Здесь вы найдете больше 400 моих статей (с файлами примеров) по самым лучшим, полезным и эффективным приемам, трюкам и способам работы в Microsoft Excel и Power BI. Пройдитесь по полному списку или сначала выберите раздел слева и - вперед!
![]()
Приемы
Что делать, если ваш рабочий файл "распух" до полусотни мегабайт и Excel жутко тормозит при открытии книги и ее пересчете. Как уменьшить размер файла и ускорить его работу?
Увеличение количества откатов в Excel 2003
Как в Excel 2003 увеличить количество отменяемых последних действий (Undo) с 16 до 100!
Подробнее...
Несколько способов разделить текст из одной ячейки (столбца) - на несколько. Например, поделить столбец с адресом на три отдельных столбца с индексом, городом, улицей в каждом по-отдельности. Или разделить слипшееся ФИО без пробелов на отдельные слова.
Подробный разбор нового инструмента в Power Query - объединения таблиц с помощью нечёткого текстового поиска.
Импорт нетабличных данных, которые обычно "не видит" Power Query, на примере парсинга цен и товаров с сайта Wildberries.
Подробный разбор функции замены текста ПОДСТАВИТЬ (SUBSTITUTE) и примеров ее использования для удаления неразрывных пробелов, подсчета количества слов, извлечения первых двух слов из текста.
Как произвести тонкую настройку вычисляемых данных в сводной таблице (поменять сумму на количество, посчитать долевые проценты, отобразить динамику и т.д.).
Как решить систему уравнений (линейных и нелинейных) в Microsoft Excel с помощью обратной матрицы Крамера и подбором в надстройке Поиск решения (Solver).
Как восстановить потерянную работу, если вы вышли из Excel без сохранения изменений? Или нужно вернуться в работе к состоянию, которое было 2 часа назад? Как научить Excel автоматически сохранять резервную копию ценного файла в заданную папку и спасать потерянные данные?
Как загрузить отдельные файлы или содержимое целой папки из облака OneDrive или SharePoint в модель данных Power BI или в Excel через Power Query.
Фильтр исходных данных в сводной таблице
Как при помощи простого макроса отфильтровать те строки в исходной таблице, которые участвовали в расчете заданной ячейки сводной таблицы. Это позволит быстро подогнать результаты сводной под нужные значения.
Подробнее...
Считаем вероятность выигрыша в лотерею, частоты выпадения шаров и моделируем игру по различным стратегиям с помощью макроса.
Как подсчитать максимальное количество идущих подряд значений, удовлетворяющих заданному условию? Например, самое большое количество рабочих дней подряд в табеле? Или самую длинную победную серию в спорте?
Как запретить другим пользователям распечатывать конфиденциальную информацию из вашей книги Excel.
Как построить секционную гистограмму, где несколько отдельных наборов столбцов (категорий, групп) наглядно форматируются разными цветами.
4 способа построить диаграмму "воронка продаж" (sales funnel) в Microsoft Excel - от инфографики SmartArt до динамической воронки на сводной таблице Power Pivot.
Как найти самые часто встречающиеся текстовые и числовые значения в большом списке разными способами (сводные таблицы с группировкой, функции ЧАСТОТА и МОДА, формулой массива и т.д.)
Как быстро подсчитать, сколько именно определенных дней недели попадает в заданный интервал дат в календаре. Например, сколько пятниц в январе 2016 года?
Несколько способов решить одну из наиболее распространенных задач при работе в Microsoft Excel - вычислить итоги (сумму, среднее, количество и т.д.) только для тех строк в таблице, которые удовлетворяют заданному условию или набору из нескольких условий.
Три способа исправить числа, случайно превратившиеся в даты в Microsoft Excel.
Как с помощью надстройки Power Query для Excel собрать данные из нескольких таблиц (разного размера), расположенных в разных книгах Excel - в одну общую таблицу. С автоматическим обновлением в будущем и "причесываниями" данных в процессе.
Как за несколько минут с помощью Excel и бесплатного сервиса Skydrive создать страницу опроса в интернет с автоматической загрузкой результатов в ваш файл Microsoft Excel.
Как при помощи Листа Прогноза за пару минут сформировать по историческим данным прогноз на будущее по методу экспоненциального сглаживания.
Почему иногда итоги в Power BI и Power Pivot могут казаться некорректными и как это исправить.
Как за несколько секунд из огромной таблицы в несколько тысяч строк сформировать компактный, наглядный и правильный отчет? Один из лучших инструментов во всем Excel!
Как вставить картинку или фотографию в примечание к ячейке - по одной или сразу несколько, чтобы получить, например, всплывающие изображения для товаров или портреты сотрудников.
Как с помощью регулярных выражений (Regular Expression = RegExp) находить и извлекать из текста фрагменты по заданным сложным шаблонам. Например, вытащить номер счета из описания платежа, город из адреса, телефон из контактов клиента и т.п.
Как в разы ускорить и упростить типовые расчёты в Power BI с помощью групп вычислений (calculation groups) и параметров полей (field parameters).
Проектная диаграмма Ганта (Gantt Chart) в Excel 2003
Как создать один из основных типов диаграмм в управлении проектами - диаграмму Ганта - в Excel 2003 и старше.
Подробнее...
Быстрая настройка форматов данных в сводной таблице
Как с помощью простого макроса быстро настроить форматы всех полей в сводной таблице, подтянув их из исходных данных.
Подробнее...
Несколько способов импортировать с финансовых сайтов в Excel с последующим автоматическим обновлением курс доллара, евро, котировки акций или любую другую динамическую информацию на заданную дату (или интервал дат).
Пошаговый разбор решения задачи план-факт анализа с использованием сводных таблиц, модели данных Power Pivot со связями "многие-ко-многим", простых мер на DAX и допиливании входных данных с помощью Power Query.
Как получить подробную информацию о ячейке и ее параметры с помощью функции ЯЧЕЙКА (CELL). Используя ее можно определять тип введенных в ячейку данных, ее формат, наличие защиты и т.п.
Как в Excel построить очень популярную в финансовых отчетах каскадную диаграмму отклонений, известную также под названиями "водопад" (waterfall) или "мост" (bridge) для наглядного отображения динамики изменений какого-либо параметра.
Обычно в области значений сводных таблиц находятся числовые данные. А что делать, если нужно создать подобие сводной, но с текстом?
Сохранение листов книги как отдельных файлов
Если вам нужно разобрать книгу на листы, т.е. сохранить каждый лист как отдельный файл - поможет простой макрос.
Подробнее...
Как соединить два списка, чтобы в итоге получить один обобщенный список, причем без повторений. Подробный разбор нескольких способов (вручную, формулами, через Power Query).
Как быстро заполнить пустые ячейки в столбце, вставив туда значения вышестоящих ячеек.
Как при помощи условного форматирования быстро и наглядно разделить линиями наборы однотипных строк по заданному параметру в большой таблице.
Как создать выпадающий список который будет иметь двустороннюю связь со справочником: при добавлении новых элементов к справочнику - они будут появляться в списке. При вводе нового элемента в выпадающий список - элемент будет добавляться к справочнику.
Как автоматически подсвечивать заданный столбец в диаграмме: по заданному условию, с выбором из выпадающего списка, выше/ниже среднего, самый большой или самый маленький и т.д. По сути, реализовать условное форматирование, но не для ячеек, а для столбцов в диаграмме.
Поиск нужных данных в диапазоне
О двух ближайших "родственниках" функции ВПР (VLOOKUP) - функциях поиска и выборки ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), позволяющих выбирать данные из списка по заданным условиям и реализовать "левый ВПР".
Подробнее...
Как построить матрицу кросс-продаж в Power BI и Microsoft Excel, чтобы определить какие товары обычно покупают вместе.
Как создать "живую" интерактивную диаграмму с прокруткой во времени, зумом отдельных фрагментов и включением-выключением нужных рядов с помощью флажков.
Как быстро проверить соответствует ли текст в ячейке заданной маске (с использованием символов *, ?, # и т.д.) Очень полезно для поиска неправильно введенных адресов, номеров автомобилей, паспортов, артикулов и т.п.
Как использовать срезы (графические кнопки) для переключения режимов вычислений (сумма, среднее, количество и т.д.) в сводной таблице по модели данных Power Pivot.
Как добавить поддержку регулярных выражений (RegExp) в Power Query для реализации поиска и извлечения фрагментов текста по гибким шаблонам и маскам.
Что такое макросы. Как создавать макросы для автоматизации повторяющихся действий и расширения функционала Microsoft Excel. Как добавить свою пользовательскую функцию (UDF) к стандартному набору функций Excel. Макросы и безопасность.
Как с помощью неочевидной новой функции в Power Query разобрать разношёрстные текстовые данные на группы (кластеры) по наибольшей похожести?
