Портал ppc.word опубликовал мнение Артёма Абдуллина по вопросу использования Excel-функций для аналитики данных.
Артём поделился рядом нестандартных приёмов и формул, которые позволяют значительно ускорить и упростить работу с данными. Эти решения особенно ценны для специалистов, регулярно работающих с финансовыми моделями, оценкой и аналитикой — ведь многие из них пока малоизвестны широкой аудитории.
В статье также освещены тонкости работы с рядом формул Excel, про которые редко упоминается в стандартных руководствах, но которые могут существенно повлиять на корректность и эффективность моделей.
Материал основан на практическом опыте команды ООО "Форпост Солюшнс", накопленном в рамках реализации проектов для крупного бизнеса, инвестиционных фондов и банков. Мы продолжаем делиться профессиональными инструментами, которые помогают делать работу быстрее, точнее и надёжнее.
Функция СУММЕСЛИМН
=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2]; [условие2]; ...)
=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2]; [условие2]; ...)
Функция суммирует ячейки, удовлетворяющие одному или нескольким условиям.
Например, функция =СУММЕСЛИМН(C1:C10; B1:B10; «>100»; A1:A10; «Яблоки») суммирует значения в C1:C10, где B1:B10 > 100 и A1:A10 = «Яблоки».
Обратите внимание, что если вы используете СУММЕСЛИМН для данных из внешнего закрытого файла Excel, функция вернет ошибку #ЗНАЧ! Это происходит, так как СУММЕСЛИМН требует прямого доступа к исходным данным в реальном времени. Можно использовать СУММПРОИЗВ — она работает, даже если файл-источник закрыт.
Обратите внимание, что если вы используете СУММЕСЛИМН для данных из внешнего закрытого файла Excel, функция вернет ошибку #ЗНАЧ! Это происходит, так как СУММЕСЛИМН требует прямого доступа к исходным данным в реальном времени. Можно использовать СУММПРОИЗВ — она работает, даже если файл-источник закрыт.
Функция СЧЁТЕСЛИМН
=СЧЁТЕСЛИМН(диапазон1; условие1; [диапазон2]; [условие2]; ...)
=СЧЁТЕСЛИМН(диапазон1; условие1; [диапазон2]; [условие2]; ...)
Функция считает ячейки, соответствующие нескольким критериям.
Функция полезна при анализе данных с комбинированными условиями (например, продажи в определенном регионе и периоде).
Функция ВПР
=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
Функция находит совпадения по строкам в таблице или диапазоне.
Функция ИНДЕКС/ПОИСКПОЗ
=ИНДЕКС(диапазон_результатов; ПОИСКПОЗ(искомое_значение; диапазон_поиска; 0))
=ИНДЕКС(диапазон_результатов; ПОИСКПОЗ(искомое_значение; диапазон_поиска; 0))
Функция — более гибкая альтернатива ВПР.
Функция полезна при поиске данных в таблицах с нестандартной структурой.
Функция СУММПРОИЗВ
=СУММПРОИЗВ(массив1; [массив2]; ...)
=СУММПРОИЗВ(массив1; [массив2]; ...)
Функция умножает элементы массивов и возвращает сумму произведений. Может использоваться для условных расчетов.
Функцию удобно применять для сложных расчетов без использования SUMIFS/COUNTIFS.
Функция СОРТ
=СОРТ(диапазон; [столбец_сортировки]; [по_возрастанию])
=СОРТ(диапазон; [столбец_сортировки]; [по_возрастанию])
Функция динамически сортирует диапазон по указанному столбцу.
Например, функция =СОРТ(A1:C10; 2; −1) сортирует данные A1:C10 по второму столбцу (B) в порядке убывания.
Функция ФИЛЬТР
=ФИЛЬТР(диапазон; условие; [значение_при_ошибке])
=ФИЛЬТР(диапазон; условие; [значение_при_ошибке])
Функция динамически фильтрует данные по заданному условию.
Функция полезна для создания автоматических отчетов без ручной фильтрации.
Функция УНИК
=УНИК(диапазон)
=УНИК(диапазон)
Функция возвращает уникальные значения из диапазона.
Функция полезна для анализа повторяющихся данных, создания выпадающих списков.
Функция ЕСЛИОШИБКА
=ЕСЛИОШИБКА(формула; «значение_при_ошибке»)
=ЕСЛИОШИБКА(формула; «значение_при_ошибке»)
Функция заменяет ошибки на заданное значение.
Например, =ЕСЛИОШИБКА(A1/B1; 0) возвращает 0, если деление на ноль.
Функция ТРАНСП
=ТРАНСП(массив)
=ТРАНСП(массив)
Функция преобразовывает вертикальные списки в горизонтальные.
Функция меняет ориентацию таблицы: строки становятся столбцами и наоборот. Например, =ТРАНСП(A1:B4) преобразует вертикальную таблицу 2×4 в горизонтальную 4×2.
Полный текст публикации https://ppc.world/articles/formuly-v-excel-10-bazovyh-16-formul-dlya-digital-specialistov/