Здесь вы найдете больше сотни моих статей по самым лучшим, полезным и эффективным приемам, трюкам и способам работы в Microsoft Excel. Пройдитесь по полному списку или сначала выберите раздел слева и - вперед!
![]()
Приемы
Автоматическая вставка текущей даты в ячейку при вводе данных
Как при помощи простого макроса сделать так, чтобы при вводе новых данных в таблицу Excel в соседних ячейках автоматически фиксировалась дата и время их занесения.
Подробнее...
Три способа (обычные формулы, динамические массивы и Power Query) сравнить между собой 2-3-4 и более списков и вывести все совпадения, т.е. их общие элементы.
Как создать несколько удобных вариантов просмотра одной и той же таблицы с помощью малоизвестного инструмента Microsoft Excel - Представлений (Custom Views).
Как разблокировать макросы при появлении сообщения РИСК БЕЗОПАСНОСТИ, когда файл была скачана из интернета.
Разбираем на примерах варианты использования новой функции ИЗОБРАЖЕНИЕ (IMAGE) для вставки изображений по ссылкам на лист Excel + делаем собственный аналог на VBA для вставки изображений из локальных файлов.
Как при помощи отступов и переносов строк сделать длинные и сложные формулы с вложенными функциями гораздо более наглядными и легкими для понимания.
Как при помощи Power Query извлекать из исходных данных "плавающий" фрагмент - блок данных, размеры и положение которого в файле заранее неизвестны.
Одна из самых мощных и красивых функций в Excel - функция ВПР (VLOOKUP), позволяющая автоматически находить нужные значения в таблице. О том, как ее правильно использовать - просто и понятно.
Несколько способов разделить текст из одной ячейки (столбца) - на несколько. Например, поделить столбец с адресом на три отдельных столбца с индексом, городом, улицей в каждом по-отдельности. Или разделить слипшееся ФИО без пробелов на отдельные слова.
Как при помощи Power Query сделать в Excel "вечный" производственный календарь - автоматически обновляющийся список нерабочих дней за все годы для использования в своих расчетах.
Как с помощью всего одной формулы реализовать на листе Excel универсальный "живой" календарь на любой месяц любого года с интерактивностью.
Как подсчитать совпадения в двух диапазонах данных и вывести их отдельным списком. Разбор трех способов: формулами, макросами и с помощью слияния запросов в Power Query.
Макрос для быстрого создания на листе Excel списка всех файлов из заданной папки и ее подпапок.
Как найти самые часто встречающиеся текстовые и числовые значения в большом списке разными способами (сводные таблицы с группировкой, функции ЧАСТОТА и МОДА, формулой массива и т.д.)
Автоматическая координатная подсветка текущей строки и столбца "крестом" - крайне удобно при просмотре больших таблиц!
Пошаговый разбор всего процесса и главных принципов работы в Microsoft Power BI - от сырых файлов с исходными данными до опубликованного в облако интерактивного аналитического отчёта.
Как найти значение последней непустой ячейки в строке или столбце таблицы Excel с помощью функции ПРОСМОТР (LOOKUP).
Как за 2 минуты без формул и сложных действий создать в Excel анимированную пузырьковую диаграмму, шарики которой двигаются, отображая развитие ситуации во времени.
Как добавить на диаграмму изображения (логотипы, бренды, флаги, фотографии) в качестве подписей данных.
Как быстро подсчитать, сколько именно определенных дней недели попадает в заданный интервал дат в календаре. Например, сколько пятниц в январе 2016 года?
Что делать, если вы построили несколько сводных таблиц на основании одного источника и теперь не получается группировать их отдельно - группировка одной сводной влияет на все остальные и наоборот. Как отвязать сводную от общего кэша?
Подробный разбор функции замены текста ПОДСТАВИТЬ (SUBSTITUTE) и примеров ее использования для удаления неразрывных пробелов, подсчета количества слов, извлечения первых двух слов из текста.
Как преобразовать горизонтальный диапазон ячеек в вертикальный и обратно.
Для чего (на самом деле!) нужна функция СУММПРОИЗВ (SUMPRODUCT) и какие замечательные вещи она умеет делать (включая выборочный подсчет из ЗАКРЫТОГО файла).
Как автоматически подсвечивать заданный столбец в диаграмме: по заданному условию, с выбором из выпадающего списка, выше/ниже среднего, самый большой или самый маленький и т.д. По сути, реализовать условное форматирование, но не для ячеек, а для столбцов в диаграмме.
Как при помощи Power Query собирать данные сразу с нескольких веб-страниц, перебирая их в цикле и объединяя результаты импорта в единую таблицу Excel.
Что такое нарастающие итоги (Running Totals) и как вычислить в Microsoft Excel разными способами: формулами, в сводной таблице, в запросе Power Query.
Несколько способов решения задачи сравнения двух диапазонов данных и выделения отличий или совпадений между ними.
Сортировка формулой
Несколько способов отсортировать числовой или текстовый список не через меню Данные - Сортировка, а с помощью формул "на лету".
Подробнее...
Фильтр исходных данных в сводной таблице
Как при помощи простого макроса отфильтровать те строки в исходной таблице, которые участвовали в расчете заданной ячейки сводной таблицы. Это позволит быстро подогнать результаты сводной под нужные значения.
Подробнее...
Зачем (на самом деле!) нужны Стили в Excel и как правильно их использовать для быстрого форматирования ваших данных.
Как искать в одной таблице и подставлять в другую данные по нескольким критериям, т.е., другими словами, реализовать поиск аналогичный функции ВПР (VLOOKUP) но сразу по нескольким условиям.
Как подсвечивать разными цветами в большом списке прошлые, актуальные и будущие даты. Подойдет для наглядного отображения заканчивающихся договоров, актов, будущих поставок и т.п.
Как решить систему уравнений (линейных и нелинейных) в Microsoft Excel с помощью обратной матрицы Крамера и подбором в надстройке Поиск решения (Solver).
Что нас ждет в Excel 2016? Подробный обзор всех новых инструментов и функций: новые типы диаграмм, надстройка Power Query, изменения в работе сводных таблиц и улучшенные средства прогнозирования.
Обычно в области значений сводных таблиц находятся числовые данные. А что делать, если нужно создать подобие сводной, но с текстом?
Как настроить-изменить-восстановить цветовую палитру, которую Excel использует для форматирования ячеек?
Как быстро скрыть (а потом - отобразить обратно) временно ненужные для просмотра на листе строки или столбцы?
Как при помощи функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ просуммировать (а также вычислить среднее, минимум, максимум, количество и т.д.) только видимых ячеек оставшихся после применения фильтра, скрытия строк/столбцов, сворачивания группировкой, а также проигнорировать ячейки с ошибками при расчетах.
Как при помощи простого макроса превратить скучный и громоздкий Расширенный фильтр в мегаполезный и удобный инструмент для фильтрации списков по большому количеству сложных условий.
Несколько способов подсчитать количество уникальных (неповторяющихся) значений в заданном диапазоне ячеек.
Как и зачем строить сводную в Excel по Модели Данных (Data Model) и какие преимущества это даёт по сравнению с классической сводной таблицей?
Как разобрать данные из одной таблицы сразу на несколько листов по заданному критерию. Разбираем два способа - с обновлением (через Power Query) и без (только VBA).
Как использовать функцию ПОИСКПОЗ (MATCH) для поиска позиции нужного элемента в списке, первой или последней текстовой ячейки или ячеек с заданным значением в диапазоне.
Как построить особый вид точечной диаграммы со стрелками, чтобы наглядно визуализировать изменение ситуации в стиле "было-стало".
Все стандартные функции поиска (ВПР, ГПР, ПОИСКПОЗ и т.д.) ищут только сверху-вниз и слева-направо. Что же делать, если нужно реализовать обратный поиск совпадений, т.е. искать не первое, а последнее вхождение требуемого значения в списке?
Вставка в отфильтрованные строки
Как копировать и вставлять данные только в отобранные Фильтром строки, игнорируя скрытые.
Подробнее...
Иногда Excel начинает воспринимать числа в некоторых ячейках как текст. Из за этого не работают формулы, нарушается сортировка и фильтрация и т.д. Как же исправить ситуацию?
Что делать, если ваш рабочий файл "распух" до полусотни мегабайт и Excel жутко тормозит при открытии книги и ее пересчете. Как уменьшить размер файла и ускорить его работу?
