Здесь вы найдете больше 400 моих статей (с файлами примеров) по самым лучшим, полезным и эффективным приемам, трюкам и способам работы в Microsoft Excel и Power BI. Пройдитесь по полному списку или сначала выберите раздел слева и - вперед!
![]()
Приемы
Как добавить поддержку регулярных выражений (RegExp) в Power Query для реализации поиска и извлечения фрагментов текста по гибким шаблонам и маскам.
Как создать выпадающий список который будет иметь двустороннюю связь со справочником: при добавлении новых элементов к справочнику - они будут появляться в списке. При вводе нового элемента в выпадающий список - элемент будет добавляться к справочнику.
Как найти в диапазоне чисел минимальное или максимальное значение по условию с помощью функций МИНЕСЛИ и МАКСЕСЛИ, с помощью формул массива, функции ДМИН или сводной таблицы.
Как бороться с повторами в тексте внутри ячейки: обнаруживать их, выделять цветом или удалять. С помощью формул, макросов или запросов Power Query.
Несколько способов очистить текстовые данные в Excel от ненужных символов, мешающих последующей обработке: лишних пробелов, непечатаемых символов, апострофов, латиницы и т.д.
Автоматическая координатная подсветка текущей строки и столбца "крестом" - крайне удобно при просмотре больших таблиц!
Новые возможности сводных таблиц в Excel 2013
Рекомендованные сводные таблицы. Построение сводной по нескольким исходным таблицам. Фильтрация с помощью Временной шкалы. Новые функции вычислений полей значений. "Погружение" в данные с помощью Экспресс-просмотра.
Подробнее...
Как построить матрицу кросс-продаж в Power BI и Microsoft Excel, чтобы определить какие товары обычно покупают вместе.
Три способа реализовать горизонтальную фильтрацию столбцов, которая изначально отсутствует в Microsoft Excel: с помощью функции ФИЛЬТР, запроса Power Query + сводная таблица и макросом на VBA.
Все стандартные функции поиска (ВПР, ГПР, ПОИСКПОЗ и т.д.) ищут только сверху-вниз и слева-направо. Что же делать, если нужно реализовать обратный поиск совпадений, т.е. искать не первое, а последнее вхождение требуемого значения в списке?
Как добавить к ячейкам красные и зеленые стрелки для наглядного отображения роста или падения цены, прибыли и т.д.
Извлечение уникальных элементов из диапазона
Как найти все дубликаты в большом списке и удалить их, оставив только уникальные (неповторяющиеся) значения?
Подробнее...
Вычисление простого прогноза по линейному тренду с помощью функции ПРЕДСКАЗ (FORECAST).
Поиск и подстановка с учётом периода
Как найти и подставить данные из одной таблицы в другую не только по совпадению ключевого значения, но и за определённый период (на примере подтягивания меняющихся цен).
Подробнее...
Как быстро скрыть содержимое заданных ячеек от чужого взгляда с помощью хитрого стиля и пользовательского формата, не ставя на них пароль.
Как искать и выбирать нужные данные из двумерной таблицы, т.е. производить выборку не по одному параметру (как функции ВПР или ГПР), а по двум сразу.
Как найти самые часто встречающиеся текстовые и числовые значения в большом списке разными способами (сводные таблицы с группировкой, функции ЧАСТОТА и МОДА, формулой массива и т.д.)
Копирование формул без сдвига ссылок
Несколько способов скопировать диапазон с формулами так, чтобы ссылки в них не сместились на новые ячейки, а остались точно такими же.
Подробнее...
Как при помощи Листа Прогноза за пару минут сформировать по историческим данным прогноз на будущее по методу экспоненциального сглаживания.
Как загрузить отдельные файлы или содержимое целой папки из облака OneDrive или SharePoint в модель данных Power BI или в Excel через Power Query.
Подсветка лишних пробелов
Как подсветить ячейки с лишними пробелами в полях ввода формы, намекнув пользователю, что он некорректно ввел данные.
Подробнее...
Как быстро сравнить 2 версии файла (исходную и после правок) и найти все изменения, которые туда внесли.
Автоматическая вставка текущей даты в ячейку при вводе данных
Как при помощи простого макроса сделать так, чтобы при вводе новых данных в таблицу Excel в соседних ячейках автоматически фиксировалась дата и время их занесения.
Подробнее...
Несколько способов импортировать с финансовых сайтов в Excel с последующим автоматическим обновлением курс доллара, евро, котировки акций или любую другую динамическую информацию на заданную дату (или интервал дат).
Подробный разбор революционно нового инструмента Excel - динамических массивов (Dynamic Arrays). Логика их работы, нюансы, плюсы и минусы, совместимость со старыми версиями.
Как запускать нужные вам макросы в определенное время, с определенной частотой или по расписанию? Например, автоматически обновлять тяжелый и медленный отчет каждое утро в 5:00?
Как создать маркированный или нумерованный список с автоматической перенумерацией при редактировании элементов списка как в Microsoft Word.
Как размножить некоторые строки в таблице, создав для них заданное количество дубликатов. Разбор двух способов: "в лоб" макросом и красиво - через Power Query.
Несколько способов решить одну из наиболее распространенных задач при работе в Microsoft Excel - вычислить итоги (сумму, среднее, количество и т.д.) только для тех строк в таблице, которые удовлетворяют заданному условию или набору из нескольких условий.
Несколько способов быстро сделать чересстрочную заливку строк/столбцов таблицы а-ля "зебра" или "тельняшка" для удобства восприятия.
Выпадающий список с наполнением
Как создать выпадающий список в ячейке листа Excel, который будет динамически формироваться по заданному диапазону. Т.е. при дописывании новых элементов к таблице данных они будут автоматом добавляться к выпадающему списку, а при удалении ненужных элементов из таблицы - пропадут из него.
Подробнее...
Как с помощью функций ГИПЕРССЫЛКА и ПОИСКПОЗ сделать "живую" ссылку на определенную строку в другой таблице, содержащую нужные вам данные, чтобы потом быстро переходить из одной таблицы в другую.
Использование надстройки Поиск решения (Solver) для на примере решения классической задачи в логистике - оптимизации доставки товаров со склада в магазины.
Как превратить в полноценную дату текстовые ее написания вида "8 мар 13", "2017.3.8", "09 сентябрь 2016" и т.д. с помощью функции ДАТАЗНАЧ.
Как использовать функцию ПОИСКПОЗ (MATCH) для поиска позиции нужного элемента в списке, первой или последней текстовой ячейки или ячеек с заданным значением в диапазоне.
Как поженить Power Query с нейросетями, чтобы внутри нашего запроса Power Query на любом шаге иметь возможность вызвать необходимую нейросеть, скормить ей данные и промпт и тут же получить обратно результаты для обработки на следующих шагах.
Быстрый ввод даты и времени без разделителей
Если вы часто вводите даты и время, то гораздо удобнее делать это без точек и двоеточий. Этот макрос автоматически преобразует введенные с клавиатуры шестизначные числа в дату (110512 в 11.05.2012) и четырехзначные - во время (0735 в 7:35).
Подробнее...
Если вы когда-нибудь пытались сослаться на ячейку сводной таблицы, то должны были встречать эту функцию (от которой большинство шарахается). Зачем она нужна на самом деле и в каких случаях она может здорово помочь?
Как подсвечивать разными цветами в большом списке прошлые, актуальные и будущие даты. Подойдет для наглядного отображения заканчивающихся договоров, актов, будущих поставок и т.п.
Как подсчитать максимальное количество идущих подряд значений, удовлетворяющих заданному условию? Например, самое большое количество рабочих дней подряд в табеле? Или самую длинную победную серию в спорте?
Различные способы реализации выпадающего списка на листе Excel с возможностью выбирать больше одного элемента из заданного набора вариантов и накапливать выбранное в текущей ячейке.
Красивый и удобный способ быстро и наглядно отбирать данные в сводной таблице по нескольким параметрам при помощи интерактивных графических кнопочных фильтров - срезов.
Как восстановить потерянную работу, если вы вышли из Excel без сохранения изменений? Или нужно вернуться в работе к состоянию, которое было 2 часа назад? Как научить Excel автоматически сохранять резервную копию ценного файла в заданную папку и спасать потерянные данные?
Как быстро выделить цветом элементы, которые встречаются в списке более одного раза?
В чем разница между относительными, смешанными и абсолютными ссылками на ячейки в формулах? Как сделать ссылку на ячейку, которая не будет изменяться ни при копировании исходной ячейки, ни при вставке перед ней новых строк/столбцов?
Как работает новый режим совместного редактирования в Microsoft Excel 2016. Зачем нужно Автосохранение, в чем его плюсы и минусы и как его отключить.
Сборка листов из разных книг в одну
Макрос для быстрой автоматической сборки листов из нескольких заданных файлов в текущую книгу Excel.
Подробнее...
Фильтр исходных данных в сводной таблице
Как при помощи простого макроса отфильтровать те строки в исходной таблице, которые участвовали в расчете заданной ячейки сводной таблицы. Это позволит быстро подогнать результаты сводной под нужные значения.
Подробнее...
Сохранение листов книги как отдельных файлов
Если вам нужно разобрать книгу на листы, т.е. сохранить каждый лист как отдельный файл - поможет простой макрос.
Подробнее...
