Здесь вы найдете больше сотни моих статей по самым лучшим, полезным и эффективным приемам, трюкам и способам работы в Microsoft Excel. Пройдитесь по полному списку или сначала выберите раздел слева и - вперед!
![]()
Приемы
7 способов проверить условия в Excel
Несколько способов проверить одиночные и множественные условия в Microsoft Excel: функция ЕСЛИ, ЕСЛИМН, ПЕРЕКЛЮЧ, ВЫБОР, ВПР + экзотика в виде прямого умножения на условия и формулы массива с И-ИЛИ.
Подробнее...
6 способов преобразовать формулы в ячейках листа Excel в значения. Как для выделенного диапазона, так и сразу для всего листа или даже целой книги.
Многоразовый ВПР (VLOOKUP)
Как извлечь ВСЕ данные из таблицы по заданному критерию. Функция ВПР (VLOOKUP) находит значение только по первому совпадению, а нужно вытащить все. Поможет хитрая формула массива!
Подробнее...
Как искать и выбирать нужные данные из двумерной таблицы, т.е. производить выборку не по одному параметру (как функции ВПР или ГПР), а по двум сразу.
Одна из самых мощных и красивых функций в Excel - функция ВПР (VLOOKUP), позволяющая автоматически находить нужные значения в таблице. О том, как ее правильно использовать - просто и понятно.
Как проверить попадание параметра в один из заданных диапазонов и, при этом, обойтись без громоздких вложенных проверок с помощью кучи функций ЕСЛИ (IF)?
Несколько способов решить одну из наиболее распространенных задач при работе в Microsoft Excel - вычислить итоги (сумму, среднее, количество и т.д.) только для тех строк в таблице, которые удовлетворяют заданному условию или набору из нескольких условий.
Если вы когда-нибудь пытались сослаться на ячейку сводной таблицы, то должны были встречать эту функцию (от которой большинство шарахается). Зачем она нужна на самом деле и в каких случаях она может здорово помочь?
Как найти в диапазоне чисел минимальное или максимальное значение по условию с помощью функций МИНЕСЛИ и МАКСЕСЛИ, с помощью формул массива, функции ДМИН или сводной таблицы.
Что если при расчете сроков нужная вам дата выпадет на выходные? Как найти ближайший рабочий день к заданной дате, учитывая выходные и праздники?
Подробный разбор всех пяти вариантов применения мегамощной функции ИНДЕКС (INDEX): от простого поиска данных в столбце, до двумерного поиска в нескольких таблицах и создания авторастягивающихся диапазонов.
Как реализовать в Excel аналог функции ВПР (VLOOKUP), который будет выдавать значения левее поискового столбца.
Для чего (на самом деле!) нужна функция СУММПРОИЗВ (SUMPRODUCT) и какие замечательные вещи она умеет делать (включая выборочный подсчет из ЗАКРЫТОГО файла).
Как использовать одну из малоизвестных, но крайне полезных функций БДСУММ (DSUM) для выборочного суммирования по одному или нескольким сложным условиям (еще и связанным между собой И-ИЛИ).
Подробный разбор на примерах нюансов и особенностей функции ДВССЫЛ (INDIRECT), позволяющей реализовать косвенные текстовые ссылки.
Разбор на живых примерах трех главных функций динамических массивов в новом вычислительном движке Excel: СОРТ, ФИЛЬТР и УНИК + их сочетаний для решения практических задач.
Подробный разбор новой функции ПРОСМОТРX (XLOOKUP), которая приходит на смену классической ВПР (VLOOKUP).
Тест скорости 7 разных вариантов реализации поиска и подстановки данных из одной таблицы в другую: ВПР, ИНДЕКС+ПОИСКПОЗ, ПРОСМОТРХ, СУММЕСЛИ и т.д. Кто будет самым быстрым?
Как научить функцию ВПР (VLOOKUP) искать значения, когда в исходных данных встречаются "числа-как-текст", что приводит к ошибкам #Н/Д.
Разбор на примерах возможностей новой функции ПОСЛЕД (SEQUENCE) - генератора числовых последовательностей из последнего обновления Office 365 с динамическими массивами.
Все стандартные функции поиска (ВПР, ГПР, ПОИСКПОЗ и т.д.) ищут только сверху-вниз и слева-направо. Что же делать, если нужно реализовать обратный поиск совпадений, т.е. искать не первое, а последнее вхождение требуемого значения в списке?
Как склеить (друг под другом или друг рядом с другом) несколько таблиц (в том числе с нескольких листов) с помощью новых функций Office 365 - ВСТОЛБИК (VSTACK) и ГСТОЛБИК (HSTACK).
Подробный разбор с примерами новой функции LAMBDA, позволяющей создавать свои собственные функции в Microsoft Excel (включая реализацию даже рекурсивных вычислений).
Считаем вероятность выигрыша в лотерею, частоты выпадения шаров и моделируем игру по различным стратегиям с помощью макроса.
Продвинутые техники использования ПРОСМОТРX (XLOOKUP)
Разбираем продвинутые случаи и нюансы использования функции ПРОСМОТРХ (XLOOKUP), пришедшей на смену "классической" ВПР (VLOOKUP): работу с массивами, множественные условия, двумерный поиск и поиск по нескольким листам.
Подробнее...
Ещё один наглядный вариант визуализации выполнения плана с помощью столбчатой гистограммы с накоплением и "усов" погрешностей в Microsoft Excel.
Пошаговый разбор всего процесса и главных принципов работы в Microsoft Power BI - от сырых файлов с исходными данными до опубликованного в облако интерактивного аналитического отчёта.
Примеры использования искусственного интеллекта Open AI ChatGPT для решения повседневных задач в Microsoft Excel: ввод формул, создание макросов и М-функций в Power Query.
Разбираем нюансы и хитрости сортировки данных в таблицах и диаграммах Microsoft Power BI: простые и многоуровневые сортировки, неявную сортировку, сортировку месяцев и пользовательскую сортировку в нашей собственной логической последовательности.
3 способа сравнить и наглядно вывести год-к-году в сводной таблице: формулами, дополнительным полем или с помощью мер на DAX в Power Pivot.
Как при помощи Power Query собирать данные сразу с нескольких веб-страниц, перебирая их в цикле и объединяя результаты импорта в единую таблицу Excel.
Как сравнить абсолютно любые произвольно выбранные объекты (годы, месяцы, товары и т.п.) в отчёте Microsoft Power BI.
Создаём календарный дашборд в Excel с помощью Power Pivot для наглядного отображения любых событий (например, заказов) в календаре.
Как настроить правильный моноширинный шрифт в строке формул Microsoft Excel для более удобной работы с длинными, сложными, вложенными формулами и функциями.
3 способа разложить многоуровневый список по отдельным столбцам
Как быстро сравнить 2 версии файла (исходную и после правок) и найти все изменения, которые туда внесли.
Разбираем на примерах варианты использования новой функции ИЗОБРАЖЕНИЕ (IMAGE) для вставки изображений по ссылкам на лист Excel + делаем собственный аналог на VBA для вставки изображений из локальных файлов.
Три способа реализовать горизонтальную фильтрацию столбцов, которая изначально отсутствует в Microsoft Excel: с помощью функции ФИЛЬТР, запроса Power Query + сводная таблица и макросом на VBA.
Как мгновенно фильтровать большой список по частичному текстовому совпадению в одном или нескольких столбцах с помощью текстового поля ActiveX.
Как реализовать правильное хранение пользовательских примечаний к результатам запроса Power Query - так, чтобы при обновлении запроса эти примечания оставались напротив тех ячеек, куда их вводили.
Как построить матрицу кросс-продаж в Power BI и Microsoft Excel, чтобы определить какие товары обычно покупают вместе.
4 способа построить диаграмму "воронка продаж" (sales funnel) в Microsoft Excel - от инфографики SmartArt до динамической воронки на сводной таблице Power Pivot.
Три способа (обычные формулы, динамические массивы и Power Query) сравнить между собой 2-3-4 и более списков и вывести все совпадения, т.е. их общие элементы.
3 способа посчитать скользящее среднее (Simple Moving Average = SMA) в Microsoft Excel и Power BI
Как имитировать на листе Excel флажки-галочки (мультивыбор) или жирную точку (выбор один-из), чтобы, например, отбирать из списка нужные элементы?
Как создать в ячейке выпадающий список с автоматическим удалением - если один из элементов списка уже был выбран ранее, то он исчезает из списка в следующий раз.
Несколько способов подсчитать количество уникальных (неповторяющихся) значений в заданном диапазоне ячеек.
Как быстро удалить все пустые строки и/или столбцы в большой таблице или на всём листе в Microsoft Excel. 4 способа: вручную, формулами, макросом, запросом Power Query.
5 способов построить диаграмму Парето (принцип 20/80) для наглядного отображения ключевых факторов, влияющих на ваши бизнес-процессы.
Импорт нетабличных данных, которые обычно "не видит" Power Query, на примере парсинга цен и товаров с сайта Wildberries.
