Наука о данных
Как безотказное программирование может ускорить работу Excel
Пример логики без ответвлений с использованием Excel для классификации
В этой статье в качестве примера используется набор данных цветов IRIS и классификатор среднего геометрического, чтобы познакомить с применением логики без ответвлений в Excel. (см. видео ниже, чтобы узнать больше об этом)
Обзор варианта использования
Этот пример проведет вас через пример преобразования вложенного варианта использования оператора if в более быстрое вычисление без каких-либо операторов if. Это улучшает производительность в больших книгах Excel, а также делает код намного короче.
Представьте, что вы хотите классифицировать три цветка из набора данных IRIS на основе их диапазона высоких и низких значений, показанного ниже. Самый простой способ запрограммировать эту логику - использовать стандартный оператор IF, чтобы проверить, попадает ли среднее геометрическое значение экземпляра тестовых данных в диапазон от минимума до максимума. Однако по мере того, как набор тестовых данных становится все больше и больше, вложенные операторы if снижают скорость работы книги.
Как написать классификационную задачу с помощью оператора if
Во-первых, давайте посмотрим, как записать это с помощью оператора If в сочетании с оператором AND. Это формат оператора if в excel:
=if(condition, output if the condition is true, output if the condition is false)
А вот как выглядит оператор «И»:
=AND(condition 1, condition 2,…condition n). An AND statement returns TRUE in a cell if the condition is met.
Например:
=AND(1=1,2=2) will return TRUE// =AND(1=1,2=1) will return FALSE because 2 is NOT equal to 1.
Чтобы написать оператор IF (AND), вы должны использовать следующий синтаксис:
=If(AND(condition 1, condition 2… , condition n), output if ALL conditions return TRUE, output if all conditions return FALSE)
Теперь давайте посмотрим, как мы могли бы написать это для Versicolor из приведенного выше оператора для экземпляра тестового набора данных.
Представьте, что ваши тестовые данные имеют среднее геометрическое 2,75, это означает, что вы классифицируете этот экземпляр как Versicolor, так как это кодируется?
Итак, вот два условия, которым вы должны соответствовать:
Не превышает ли среднее геометрическое значение нижнего предела диапазона Versicolor из справочной таблицы выше?
Среднее геометрическое меньше, чем верхний предел диапазона Versicolor из справочной таблицы выше?
Этот код вернет ИСТИНА, если диапазон соблюден, и ЛОЖЬ, если диапазон НЕ соблюден:
=AND(geomean>=low of versicolor, geomean<=high of versicolor)
Теперь все, что нам нужно сделать, это заключить это в оператор if:
=If(AND(geomean>=low of versicolor, geomean<=high of versicolor),”versicolor”, “not versicolor”)
Однако это по-прежнему НЕ дает вам всех трех цветов. Для этого вам нужно будет вложить оператор IF:
=If(AND(geomean>=low of versicolor, geomean<=high of versicolor),”versicolor”, If(AND(geomean>=low of setosa, geomean<=high of setosa),”setosa”, If(AND(geomean>=low of virginica, geomean<=high of virginica),”virginica”……)
Это длинный оператор IF, который должен снова и снова вычислять вложенное if, чтобы добраться до нужного класса цветов.
Как этого избежать? Есть ли способ значительно сократить этот код, а также превратить его в простую арифметику? Вот и здесь мы можем внедрить внеотраслевое программирование.
Безотраслевое программирование в Excel
Давайте для начала взглянем на оператор AND, который мы написали ранее для Versicolor.
AND(geomean>=low of Versicolor, geomean<=high of Versicolor)
Обратите внимание, что в логическом операторе ИСТИНА приравнивается к 1, а ложь равняется нулю.
Теперь давайте попробуем понять, как использовать это для замены логики IF. По сути, когда условие оценивается как ИСТИНА или 1, мы хотим повторить имя цветка (текст) 1 раз, а когда оно ложно или 0, мы хотим повторить его 0 раз, то есть показывать пустое поле.
Запишем это:
Ячейки: названия видов находятся в I4, I5 и I6. Нижняя часть - столбец L, а верхняя - столбец M. Вот как формула будет искать цветок из тестового набора данных, который вы хотите классифицировать:
=REPT(I4,AND(geomeangiven>=L4, geomeangiven <=M4)) → versicolor =REPT(I5,AND(geomeangiven >=L5, geomeangiven <=M5)) → setosa =REPT(I6,AND(geomeangiven >=L6, geomeangiven <=M6)) → virginica
Комбинирование этого дало бы альтернативу вложенному оператору if, приведенному выше:
= REPT(I4,AND(geomeangiven>=L4, geomeangiven <=M4))&REPT(I5,AND(geomeangiven >=L5, geomeangiven <=M5))&REPT(I6,AND(geomeangiven >=L6, geomeangiven <=M6))
По сути, вместо медленно ... оценивать 3 оператора if друг в друге, вы просто объединяете три различных простых арифметических формулы, две из которых будут оцениваться как пустые, а одна будет именем цветка.
Использованная литература:
- Внеофисное программирование: почему Если - это Sloowww ... и что мы можем с этим сделать! - https://www.youtube.com/watch?v=bVJ-mWWL7cE
