Здесь вы найдете больше сотни моих статей по самым лучшим, полезным и эффективным приемам, трюкам и способам работы в Microsoft Excel. Пройдитесь по полному списку или сначала выберите раздел слева и - вперед!
Оглавление книги Excel
Хотите сделать в Excel автоматическое оглавление как в Word, т.е. получить список всех листов Вашей книги в виде гиперссылок и переходить на нужный лист одним щелчком?
Вычисления по цвету ячеек
Как просуммировать или подсчитать количество или среднее арифметическое ячеек с определенным цветом заливки или шрифта.
Подробнее...
Система учета заказов на Google Calendar и Excel
Как вести учет заказов в Google-календаре и загружать потом оттуда данные в Excel с обновлением через Power Query.
Подробнее...
Динамические гиперссылки между таблицами
Как сделать удобные динамические гиперссылки, чтобы быстро переходить из одной таблицы в другую - к заданному значению в строке и столбце. Что-то типа ВПР, но возвращающая ссылку, а не значение.
Сборка таблиц с разными шапками из нескольких книг
Как при помощи Power Query собрать данные из всех файлов заданной папки, если заголовки столбцов в них отличаются.
Поиск ключевых слов в тексте
Как быстро найти в исходном тексте все ключевые слова из справочника и вывести их рядом с каждой ячейкой данных через заданный символ-разделитель. Попутно разбираемся с буферизацией запросов в Power Query с помощью функции Table.Buffer для ускорения обработки.
Разделение таблицы по листам
Как разобрать данные из одной таблицы сразу на несколько листов по заданному критерию. Разбираем два способа - с обновлением (через Power Query) и без (только VBA).
Поиск ближайшего числа
Как проверить попадание параметра в один из заданных диапазонов и, при этом, обойтись без громоздких вложенных проверок с помощью кучи функций ЕСЛИ (IF)?
Последнее слово
Как извлечь из текста последнее слово или фрагмент по заданному символу-разделителю. Разбор нескольких способов (формулы, макросы, Power Query).
Одновременная фильтрация нескольких сводных таблиц
Как фильтровать сразу несколько сводных таблиц в дашборде с помощью общего среза. Разбор 2 способов для сводных, построенных по одному и по разным источникам данных.
Поиск последнего вхождения (инвертированный ВПР)
Все стандартные функции поиска (ВПР, ГПР, ПОИСКПОЗ и т.д.) ищут только сверху-вниз и слева-направо. Что же делать, если нужно реализовать обратный поиск совпадений, т.е. искать не первое, а последнее вхождение требуемого значения в списке?
Преимущества сводной по Модели Данных
Как и зачем строить сводную в Excel по Модели Данных (Data Model) и какие преимущества это даёт по сравнению с классической сводной таблицей?
Картинки как подписи на диаграмме
Как добавить на диаграмму изображения (логотипы, бренды, флаги, фотографии) в качестве подписей данных.
Подбор слагаемых для нужной суммы
Как, имея набор из нескольких чисел, подобрать из них те, которые дадут определенную требуемую сумму?
Поиск последней непустой ячейки в строке или столбце функцией ПРОСМОТР
Как найти значение последней непустой ячейки в строке или столбце таблицы Excel с помощью функции ПРОСМОТР (LOOKUP).
Разбор функции ДВССЫЛ (INDIRECT) на примерах
Подробный разбор на примерах нюансов и особенностей функции ДВССЫЛ (INDIRECT), позволяющей реализовать косвенные текстовые ссылки.
Парсинг нетабличных данных с сайтов
Импорт нетабличных данных, которые обычно "не видит" Power Query, на примере парсинга цен и товаров с сайта Wildberries.
Подробнее...
Распределяем список по наборам
Как разделить список по наборам неодинакового размера - разбор 3 способов: вручную через сводную, запросом в Power Query и функциями динамических массивов.
Сводная по таблице с многострочной шапкой
Как построить сводную таблицу по исходным данным с многострочной шапкой, преобразовав их в нормальный вид с помощью надстройки Power Query.
Выпадающий список с показом изображений
Как сделать в Excel 2013 и новее выпадающий список, при выборе из которого рядом будет появляться изображение выбранного объекта (товара, человека и т.д.)
Обманчивая простота функции ПОСЛЕД (SEQUENCE)
Разбор на примерах возможностей новой функции ПОСЛЕД (SEQUENCE) - генератора числовых последовательностей из последнего обновления Office 365 с динамическими массивами.
Как правильно использовать стили в Excel
Зачем (на самом деле!) нужны Стили в Excel и как правильно их использовать для быстрого форматирования ваших данных.
Производственный календарь в Excel
Как при помощи Power Query сделать в Excel "вечный" производственный календарь - автоматически обновляющийся список нерабочих дней за все годы для использования в своих расчетах.
Регулярные выражения (RegExp) в Power Query
Как добавить поддержку регулярных выражений (RegExp) в Power Query для реализации поиска и извлечения фрагментов текста по гибким шаблонам и маскам.
Вафельная диаграмма в Excel
2 способа построить в Excel вафельную диаграмму (waffle-chart) для наглядной визуализации прогресса по проекту или любых других KPI.
Исправление чисел, превратившихся в даты
Три способа исправить числа, случайно превратившиеся в даты в Microsoft Excel.
Массовая замена текста в Power Query функцией List.Accumulate
Как заменить множество одних фрагментов текста на другие по справочнику в Power Query с помощью хитрой функции List.Accumulate.
Массовая замена текста формулами
Как с помощью формулы произвести массовую замену одного текста (или его фрагмента) на другой по имеющейся таблице подстановок (справочнику).
Импорт данных из OneDrive и SharePoint в Power Query / BI
Как загрузить отдельные файлы или содержимое целой папки из облака OneDrive или SharePoint в модель данных Power BI или в Excel через Power Query.
Использование функции ВПР (VLOOKUP) для подстановки значений
Одна из самых мощных и красивых функций в Excel - функция ВПР (VLOOKUP), позволяющая автоматически находить нужные значения в таблице. О том, как ее правильно использовать - просто и понятно.
ВПР и числа-как-текст
Как научить функцию ВПР (VLOOKUP) искать значения, когда в исходных данных встречаются "числа-как-текст", что приводит к ошибкам #Н/Д.
Номер недели по дате функцией НОМНЕДЕЛИ
Как определить номер рабочей недели для любой заданной даты (по ГОСТ, по ISO и т.д.) с помощью формул и функций НОМНЕДЕЛИ (WEEKNUM) и НОМНЕДЕЛИ.ISO (WEEKNUM.ISO)
Многоразовый ВПР (VLOOKUP)
Как извлечь ВСЕ данные из таблицы по заданному критерию. Функция ВПР (VLOOKUP) находит значение только по первому совпадению, а нужно вытащить все. Поможет хитрая формула массива!
Подробнее...
План-факт анализ в сводной таблице с Power Pivot и Power Query
Пошаговый разбор решения задачи план-факт анализа с использованием сводных таблиц, модели данных Power Pivot со связями "многие-ко-многим", простых мер на DAX и допиливании входных данных с помощью Power Query.
Выделение дубликатов цветом
Как быстро выделить цветом элементы, которые встречаются в списке более одного раза?
Двумерный поиск в таблице (ВПР 2D)
Как искать и выбирать нужные данные из двумерной таблицы, т.е. производить выборку не по одному параметру (как функции ВПР или ГПР), а по двум сразу.
ВПР (VLOOKUP) с учетом регистра
Как при помощи формулы массива сделать аналог функции ВПР (VLOOKUP), который при поиске будет учитывать регистр и различать строчные и прописные символы.
Транслит
Небольшой макрос для быстрого преобразования русских символов в английские, т.е. кириллицы в транслит (Пупкин в Pupkin и т.п.)
Создание резервных копий ценных файлов
Как восстановить потерянную работу, если вы вышли из Excel без сохранения изменений? Или нужно вернуться в работе к состоянию, которое было 2 часа назад? Как научить Excel автоматически сохранять резервную копию ценного файла в заданную папку и спасать потерянные данные?
Подсветка незащищенных ячеек
Как с помощью простого макроса быстро выделить цветом все незащищенные ячейки на текущем листе.
Проектная диаграмма Ганта с помощью условного форматирования
Как при помощи условного форматирования быстро создать в Excel проектную диаграмму Ганта. Удобно для визуализации этапов любого проекта, графиков отпусков, тренингов и т.д.
Подробнее...
Полосатая заливка строк таблицы "зеброй"
Несколько способов быстро сделать чересстрочную заливку строк/столбцов таблицы а-ля "зебра" или "тельняшка" для удобства восприятия.
Как не забивать гвозди микроскопом с функцией СУММПРОИЗВ
Для чего (на самом деле!) нужна функция СУММПРОИЗВ (SUMPRODUCT) и какие замечательные вещи она умеет делать (включая выборочный подсчет из ЗАКРЫТОГО файла).
Ад Условного Форматирования
Что такое "Ад Условного Форматирования", когда и почему он возникает? Как с ним бороться вручную и при помощи специального макроса?
Курс доллара для любой заданной даты
Макрос, вводящий в текущую ячейку курс доллара для любой заданной Вами даты из архива Центробанка РФ (через интернет с сайта www.cbr.ru).
Нечёткий текстовый поиск в Power Query
Подробный разбор нового инструмента в Power Query - объединения таблиц с помощью нечёткого текстового поиска.
Сравнение двух таблиц
Как сравнить между собой две таблицы (прайс-листа) и наглядно отобразить отличия между ними - добавленные и удаленные товары, изменение цены и т.д. Разбор 3 способов: с помощью функции ВПР (VLOOKUP), сводной таблицей и через надстройку Power Query.
Как построить диаграмму "водопад" (waterfall)
Как в Excel построить очень популярную в финансовых отчетах каскадную диаграмму отклонений, известную также под названиями "водопад" (waterfall) или "мост" (bridge) для наглядного отображения динамики изменений какого-либо параметра.
Прятки с формулами
Как быстро найти и выделить все ячейки, содержащие формулы, а не значения?
3D в Excel
Как при помощи простого макроса сделать необычный 3D-подарок своей половине :)

Наверх