Табличный процессор Microsoft Excel имеет сотни различных функций, и даже опытные пользователи задаются вопросом, насколько полезны те или иные из них и как часто они их используют для решения задач.
Нетривиальные функции Microsoft Excel
Рассмотрим несколько простых, но нетривиальных и изящных инструментов, с помощью которых можно работать с большими объемами данных в Excel, реализовать эффективную выборку элементов из заданного массива, получать динамические итоги, объединенные данные из диапазонов, автоматически обрабатывать ошибки.
CHOOSE (ВЫБОР)
На основании индекса возвращает значение из списка аргументов. Используют функцию «CHOOSE» для выбора одного максимум из 254 значений на основании номера индекса. Сначала может показаться, что это обычная функция, ведь все, что она делает — выбирает элемент в своем списке на основе номера индекса. К тому же есть еще и функция «INDEX», которая делает то же, но если комбинировать «ВЫБОР» с другими формулами, она может быть довольно мощной.
Пример
Тривиальным примером использования данной функции является возвращение разных значений, основанных на условиях. Например, есть список фамилий и их оценки (1); нужно разделить их на основе определенных значений (2) и вывести полученные данные в поле «Результат».
Чтобы решить эту задачу, достаточно использовать функцию «CHOOSE» следующим образом:
=CHOOSE((B2>0)+(B2>=51)+(B2>=101)+(B2>=151);"Плохо";"Удовлетворительно";"Хорошо";"Прекрасно")
Функции группы LOOKUP (ПРОСМОТР)
Функции группы «LOOKUP» (VLOOKUP, HLOOKUP) по праву можно назвать одними из самых важных и полезных. Они помогают найти данные в определенном столбце, строке или таблице. «VLOOKUP (ВПР)» позволяет искать значение в столбце («V» — по вертикали) и возвращать другое значение из той же строки. «HLOOKUP (ГПР)» позволяет искать значение в строке («H» — по горизонтали) и возвращать другое значение из того же столбца. Довольно удобные и часто используемые, так как сопоставить вручную обширные диапазоны с десятками тысяч наименований проблематично.
Пример
В самом простом виде использование «ВПР» можно представить следующим образом. Пусть в некой таблице имеется список сотрудников (фамилия и идентификатор) и необходимо вытянуть информацию об имени сотрудника с фамилией Сидоров.
Для этого используем функцию «ВПР», где указываем, что искать, диапазон поиска, номер столбца и указать нужно ли искать точное вхождение.
=VLOOKUP(B3;B2:C6;2;FALSE)
IFERROR (ЕСЛИОШИБКА)
«ЕСЛИОШИБКА» в Microsoft Excel используется для обработки ситуаций, когда в результате работы формулы возникла некая ошибка. Работая с этой функцией, можно указать, какое именно значение должно возвращаться с помощью «IFERROR» вместо полученной ошибки. Если формула в ячейке не возвращает ошибку, вместо этого возвращается результат.
Пример
Простым примером использования «IFERROR» является замена ошибки в ячейке табличного процессора на значения, которые сопоставляются ошибке.
В данном случае стандартная ошибка, вызванная делением на 0 (числа первого столбца делятся на второй) заменяется на сообщение «Деление на 0» с помощью функции «ЕСЛИОШИБКА».
=IFERROR(A2/B2; "Деление на 0")
SUMPRODUCT (СУММПРОИЗВ)
«SUMPRODUCT» возвращает сумму произведений определенных массивов или диапазонов. Ее часто используют бухгалтера для расчета заработной платы и подсчетов объемов продаж.
Пример
Хорошим примером использования функции «СУММПРОИЗВ» является расчет объемов продаж определенной категории товара по заданным параметрам (категория, размер).
=SUMPRODUCT((A3:A6=A10)*(B3:B6=B10)*C3:C6)
XIRR (ЧИСТВНДОХ)
«XIRR» — финансовая функция MS Excel, используемая для вычисления внутренней ставки доходности по определенному ряду нерегулярных потоков денег (для постоянных потоков используется функция «ВСД»).
Пример
В качестве примера можно рассмотреть вычисления внутренней ставки по заданным непостоянным потокам.
=XIRR(A2:A5;B2:B5)
С помощью этих мало известных функций MS Excel можно существенно упростить работу с объемными данными и улучшить эффективность взаимодействия с таблицами. Более подробную информацию об этих функциях и их синтаксисе можно получить в самом табличном процессоре, просто кликнув по интересующему варианту.
Как вам статья?
