Здесь вы найдете больше сотни моих статей по самым лучшим, полезным и эффективным приемам, трюкам и способам работы в Microsoft Excel. Пройдитесь по полному списку или сначала выберите раздел слева и - вперед!
Приемы
Скользящее среднее в Power BI
3 способа посчитать скользящее среднее (Simple Moving Average = SMA) в Microsoft Excel и Power BI
Подробнее...
Воронка продаж в Microsoft Excel
4 способа построить диаграмму "воронка продаж" (sales funnel) в Microsoft Excel - от инфографики SmartArt до динамической воронки на сводной таблице Power Pivot.
Разделение многоуровневного списка по столбцам
3 способа разложить многоуровневый список по отдельным столбцам
Правильный шрифт в строке формул
Как настроить правильный моноширинный шрифт в строке формул Microsoft Excel для более удобной работы с длинными, сложными, вложенными формулами и функциями.
Календарный дашборд с Power Pivot
Создаём календарный дашборд в Excel с помощью Power Pivot для наглядного отображения любых событий (например, заказов) в календаре.
Сравнение любых произвольных дат и/или товаров в Power BI
Как сравнить абсолютно любые произвольно выбранные объекты (годы, месяцы, товары и т.п.) в отчёте Microsoft Power BI.
Мультистраничный веб-запрос в Power Query
Как при помощи Power Query собирать данные сразу с нескольких веб-страниц, перебирая их в цикле и объединяя результаты импорта в единую таблицу Excel.
Год-к-году в сводной таблице
3 способа сравнить и наглядно вывести год-к-году в сводной таблице: формулами, дополнительным полем или с помощью мер на DAX в Power Pivot.
Хитрости сортировки в Power BI
Разбираем нюансы и хитрости сортировки данных в таблицах и диаграммах Microsoft Power BI: простые и многоуровневые сортировки, неявную сортировку, сортировку месяцев и пользовательскую сортировку в нашей собственной логической последовательности.
5 примеров использования новой функции IMAGE в Excel
Разбираем на примерах варианты использования новой функции ИЗОБРАЖЕНИЕ (IMAGE) для вставки изображений по ссылкам на лист Excel + делаем собственный аналог на VBA для вставки изображений из локальных файлов.
ChatGPT и Excel
Примеры использования искусственного интеллекта Open AI ChatGPT для решения повседневных задач в Microsoft Excel: ввод формул, создание макросов и М-функций в Power Query.
Поиск отличий в 2 текстовых строках
Как сравнить 2 текстовых строки и найти отличия (даже если визуально они, вроде бы, совпадают).
Основы работы в Microsoft Power BI
Пошаговый разбор всего процесса и главных принципов работы в Microsoft Power BI - от сырых файлов с исходными данными до опубликованного в облако интерактивного аналитического отчёта.
Диаграмма выполнения плана
Ещё один наглядный вариант визуализации выполнения плана с помощью столбчатой гистограммы с накоплением и "усов" погрешностей в Microsoft Excel.
Решение системы уравнений в Excel
Как решить систему уравнений (линейных и нелинейных) в Microsoft Excel с помощью обратной матрицы Крамера и подбором в надстройке Поиск решения (Solver).
Регистро НЕ чувствительность в Power Query (и Power BI)
Как победить регистрочувствительность в Power Query при фильтрации, удалении дубликатов и объединении таблиц с помощью функции Comparer.OrdinalIgnoreCase
Склейка диапазонов новыми функциями VSTACK и HSTACK
Как склеить (друг под другом или друг рядом с другом) несколько таблиц (в том числе с нескольких листов) с помощью новых функций Office 365 - ВСТОЛБИК (VSTACK) и ГСТОЛБИК (HSTACK).
Динамический "Топ-N + Другие" в сводной таблице
Как при помощи запроса Power Query реализовать в сводной таблице вывод динамического "Топ-N + Другие".
Быстрое прогнозирование в Microsoft Excel
Как при помощи Листа Прогноза за пару минут сформировать по историческим данным прогноз на будущее по методу экспоненциального сглаживания.
Путь к текущей книге и новая функция LET
Как с помощью формул получить в ячейке листа обновляемый путь к текущей книге.
Подробнее...
Риск безопасности и блокировка макросов
Как разблокировать макросы при появлении сообщения РИСК БЕЗОПАСНОСТИ, когда файл была скачана из интернета.
Слушаем интернет-радио в Excel
Как за пару минут реализовать в Excel прослушивание интернет-радиостанций с помощью внедренного компонента Windows Media Player и макроса в две строки.
Горизонтальная фильтрация столбцов в Excel
Три способа реализовать горизонтальную фильтрацию столбцов, которая изначально отсутствует в Microsoft Excel: с помощью функции ФИЛЬТР, запроса Power Query + сводная таблица и макросом на VBA.
Переключение вычислений в сводной таблице срезами
Как использовать срезы (графические кнопки) для переключения режимов вычислений (сумма, среднее, количество и т.д.) в сводной таблице по модели данных Power Pivot.
Копировать сумму выделенных ячеек в Буфер обмена
Как быстро скопировать в Буфер сумму выделенных на листе ячеек. А также не только сумму (среднее, количество и т.д.) и не только выделенных (только видимых, удовлетворяющих заданным условиям и т.д.)
Как правильно разворачивать вложенные таблицы в Power Query
Как избежать проблем при разворачивании вложенных таблиц в Power Query, если в исходных данных добавляются или удаляются столбцы.
Моделирование лотереи в Excel
Считаем вероятность выигрыша в лотерею, частоты выпадения шаров и моделируем игру по различным стратегиям с помощью макроса.
Столбец из примеров - искуственный интеллект в Power Query
Подробный разбор инструмента Столбец из примеров (Column from Examples), автоматически подбирающего формулу для преобразования ваших данных по заданному образцу.
Диаграмма по выделенной ячейке
Как построить диаграмму, где будут отображаться данные из той строки, где сейчас стоит активная ячейка.
Сборка разноформатных таблиц с одного листа в Power Query
Как собрать несколько разноформатных простых (не умных!) таблиц с одного листа Excel, а затем развернуть их в плоские (нормализовать) и соединить в единое целое с помощью Power Query.
Подробнее...
Нарастающий итог в Excel
Что такое нарастающие итоги (Running Totals) и как вычислить в Microsoft Excel разными способами: формулами, в сводной таблице, в запросе Power Query.
LAMBDA - новая суперфункция Excel
Подробный разбор с примерами новой функции LAMBDA, позволяющей создавать свои собственные функции в Microsoft Excel (включая реализацию даже рекурсивных вычислений).
Сохранение истории обновлений запросов Power Query
Как при обновлении запросов Power Query сохранять старые значения для истории, а не заменять их на новые.
Деление слипшегося текста функцией ФИЛЬТР.XML
Быстрый способ разделить слипшиеся данные на несколько ячеек с помощью функции ФИЛЬТР.XML, превратив исходный текст в XML-код.
Обновляемый курс валют в Excel
Как использовать функции ВЕБСЛУЖБА (WEBSERVICE) и ФИЛЬТР.XML (FILTERXML) для загрузки мгновенно обновляемого курса любой валюты на любой интервал дат.
Система учета заказов на Google Calendar и Excel
Как вести учет заказов в Google-календаре и загружать потом оттуда данные в Excel с обновлением через Power Query.
Динамические гиперссылки между таблицами
Как сделать удобные динамические гиперссылки, чтобы быстро переходить из одной таблицы в другую - к заданному значению в строке и столбце. Что-то типа ВПР, но возвращающая ссылку, а не значение.
Поиск ключевых слов в тексте
Как быстро найти в исходном тексте все ключевые слова из справочника и вывести их рядом с каждой ячейкой данных через заданный символ-разделитель. Попутно разбираемся с буферизацией запросов в Power Query с помощью функции Table.Buffer для ускорения обработки.
Сборка таблиц с разными шапками из нескольких книг
Как при помощи Power Query собрать данные из всех файлов заданной папки, если заголовки столбцов в них отличаются.
Разделение таблицы по листам
Как разобрать данные из одной таблицы сразу на несколько листов по заданному критерию. Разбираем два способа - с обновлением (через Power Query) и без (только VBA).
Одновременная фильтрация нескольких сводных таблиц
Как фильтровать сразу несколько сводных таблиц в дашборде с помощью общего среза. Разбор 2 способов для сводных, построенных по одному и по разным источникам данных.
Картинки как подписи на диаграмме
Как добавить на диаграмму изображения (логотипы, бренды, флаги, фотографии) в качестве подписей данных.
Преимущества сводной по Модели Данных
Как и зачем строить сводную в Excel по Модели Данных (Data Model) и какие преимущества это даёт по сравнению с классической сводной таблицей?
Поиск последнего вхождения (инвертированный ВПР)
Все стандартные функции поиска (ВПР, ГПР, ПОИСКПОЗ и т.д.) ищут только сверху-вниз и слева-направо. Что же делать, если нужно реализовать обратный поиск совпадений, т.е. искать не первое, а последнее вхождение требуемого значения в списке?
Парсинг нетабличных данных с сайтов
Импорт нетабличных данных, которые обычно "не видит" Power Query, на примере парсинга цен и товаров с сайта Wildberries.
Распределяем список по наборам
Как разделить список по наборам неодинакового размера - разбор 3 способов: вручную через сводную, запросом в Power Query и функциями динамических массивов.
Сводная по таблице с многострочной шапкой
Как построить сводную таблицу по исходным данным с многострочной шапкой, преобразовав их в нормальный вид с помощью надстройки Power Query.
Выпадающий список с показом изображений
Как сделать в Excel 2013 и новее выпадающий список, при выборе из которого рядом будет появляться изображение выбранного объекта (товара, человека и т.д.)
Как правильно использовать стили в Excel
Зачем (на самом деле!) нужны Стили в Excel и как правильно их использовать для быстрого форматирования ваших данных.
Исправление чисел, превратившихся в даты
Три способа исправить числа, случайно превратившиеся в даты в Microsoft Excel.
Вафельная диаграмма в Excel
2 способа построить в Excel вафельную диаграмму (waffle-chart) для наглядной визуализации прогресса по проекту или любых других KPI.
Массовая замена текста в Power Query функцией List.Accumulate
Как заменить множество одних фрагментов текста на другие по справочнику в Power Query с помощью хитрой функции List.Accumulate.
Массовая замена текста формулами
Как с помощью формулы произвести массовую замену одного текста (или его фрагмента) на другой по имеющейся таблице подстановок (справочнику).
Регулярные выражения (RegExp) в Power Query
Как добавить поддержку регулярных выражений (RegExp) в Power Query для реализации поиска и извлечения фрагментов текста по гибким шаблонам и маскам.
Производственный календарь в Excel
Как при помощи Power Query сделать в Excel "вечный" производственный календарь - автоматически обновляющийся список нерабочих дней за все годы для использования в своих расчетах.
Обманчивая простота функции ПОСЛЕД (SEQUENCE)
Разбор на примерах возможностей новой функции ПОСЛЕД (SEQUENCE) - генератора числовых последовательностей из последнего обновления Office 365 с динамическими массивами.
Импорт данных из OneDrive и SharePoint в Power Query / BI
Как загрузить отдельные файлы или содержимое целой папки из облака OneDrive или SharePoint в модель данных Power BI или в Excel через Power Query.
ВПР и числа-как-текст
Как научить функцию ВПР (VLOOKUP) искать значения, когда в исходных данных встречаются "числа-как-текст", что приводит к ошибкам #Н/Д.
План-факт анализ в сводной таблице с Power Pivot и Power Query
Пошаговый разбор решения задачи план-факт анализа с использованием сводных таблиц, модели данных Power Pivot со связями "многие-ко-многим", простых мер на DAX и допиливании входных данных с помощью Power Query.
Ад Условного Форматирования
Что такое "Ад Условного Форматирования", когда и почему он возникает? Как с ним бороться вручную и при помощи специального макроса?
Нечёткий текстовый поиск в Power Query
Подробный разбор нового инструмента в Power Query - объединения таблиц с помощью нечёткого текстового поиска.
Самый быстрый ВПР
Тест скорости 7 разных вариантов реализации поиска и подстановки данных из одной таблицы в другую: ВПР, ИНДЕКС+ПОИСКПОЗ, ПРОСМОТРХ, СУММЕСЛИ и т.д. Кто будет самым быстрым?
Функция ПРОСМОТРX - наследник ВПР
Подробный разбор новой функции ПРОСМОТРX (XLOOKUP), которая приходит на смену классической ВПР (VLOOKUP).
Пишем игру "Жизнь" (Life) на VBA в Excel
Пишем за 15 минут "с нуля" игру "Жизнь" (Life) Джона Конвея на VBA в Microsoft Excel.
Выпадающий список с быстрым поиском
Как создать выпадающий список, где при вводе нескольких первых символов автоматически будет фильтроваться содержимое, сужая круг поиска и отбирая только те элементы, которые содержат введённый фрагмент.
Функции динамических массивов: СОРТ, ФИЛЬТР и УНИК
Разбор на живых примерах трех главных функций динамических массивов в новом вычислительном движке Excel: СОРТ, ФИЛЬТР и УНИК + их сочетаний для решения практических задач.
Динамические массивы в Excel
Подробный разбор революционно нового инструмента Excel - динамических массивов (Dynamic Arrays). Логика их работы, нюансы, плюсы и минусы, совместимость со старыми версиями.
Как открыть новый Excel в отдельном окне
5 способов запустить новый независимый экземпляр Microsoft Excel, чтобы не ждать, пока в предыдущем выполнится пересчет формул, выполнение долгого макроса и т.д.
Дубликаты внутри ячейки
Как бороться с повторами в тексте внутри ячейки: обнаруживать их, выделять цветом или удалять. С помощью формул, макросов или запросов Power Query.
Суперсила Мгновенного заполнения (Flash Fill)
Подробный разбор вариантов применения одного из самых удивительных, но малоизвестных инструментов - Мгновенного заполнения (Flash Fill). Поможет для обработки текста: нарезки, склейки, исправления регистра, добавления или удаления слов, извлечения чисел из текста и многого другого.
Тонкости работы с переносами строк в Excel
Как работать с переносами строк (Alt+Enter) в Excel: удалять их, заменять на пробелы и другие символы, делить по ним данные на столбцы или строки.
Сборка данных со всех листов книги в одну таблицу
Как быстро собрать данные со всех (или только с нужных) листов книги в одну большую таблицу - готовым макросом или с помощью Power Query.
Генератор фраз из заданных фрагментов
Как быстро сгенерировать все возможные фразы, состоящие из заданных наборов слов в любом порядке, используя Декартово произведение множеств с помощью формул или Power Query.
Параметризация путей к данным в Power Query
При пересылке файлов с запросами Power Query они очень часто перестают работать, т.к. путь к исходным данным на другом ПК меняется. Как решить эту проблему и превратить путь в параметр (переменную), чтобы наш запрос работал на любом компьютере?
Запуск макроса по времени
Как запускать нужные вам макросы в определенное время, с определенной частотой или по расписанию? Например, автоматически обновлять тяжелый и медленный отчет каждое утро в 5:00?
Точечная диаграмма со стрелками "было-стало"
Как построить особый вид точечной диаграммы со стрелками, чтобы наглядно визуализировать изменение ситуации в стиле "было-стало".
Новые типы данных в Excel 2016
Подробный разбор особенностей использования двух новых типов данных География (Geography) и Акции (Stocks), появившихся в Microsoft Excel 2016.
Как создать свою надстройку для Microsoft Excel
Подробный пошаговый разбор создания своей собственной надстройки для Microsoft Excel.
Что такое Power Query / Pivot / Map / View / BI и зачем они пользователю Excel
Большая обзорная статья о том, что такое Power Query, Power Pivot, Power Map, Power View и комплекс бизнес-аналитики Power BI. Что делает каждая из этих программ и как они взаимодействуют? Чем могут помочь пользователю Excel?
Импорт "плавающего" фрагмента в Power Query
Как при помощи Power Query извлекать из исходных данных "плавающий" фрагмент - блок данных, размеры и положение которого в файле заранее неизвестны.
Нечеткий текстовый поиск с Fuzzy Lookup в Excel
Как использовать бесплатную надстройку Fuzzy Lookup для нечеткого поиска ближайших текстовых соответствий в двух таблицах на примере поиска совпадающих адресов.
Импорт данных из PDF в Excel через Power Query
Как перенести данные из таблицы PDF-документа на лист Microsoft Excel, используя только Word и Power Query.
Анализ детализации мобильной связи (Билайн)
Как при помощи нескольких формул и сводных таблиц проанализировать вашу детализацию разговоров, чтобы выбрать оптимальный тариф и оценить свои расходы на роуминг, интернет-траффик и т.д.
Подробнее...
Последнее слово
Как извлечь из текста последнее слово или фрагмент по заданному символу-разделителю. Разбор нескольких способов (формулы, макросы, Power Query).
Как использовать Личную Книгу Макросов
Личная Книга Макросов (Personal Macro Workbook) - что это такое, как ее создать и использовать в качестве личной библиотеки для хранения и запуска самых нужных вам в повседневной работе макросов и функций.
Диаграмма Ганта в Power Query
Как с помощью Power Query быстро "размазать" бюджет по дням в списке проектов, построив упрощенный вариант диаграммы Ганта в Excel.
Анализ текста регулярными выражениями (RegExp) в Excel
Как с помощью регулярных выражений (Regular Expression = RegExp) находить и извлекать из текста фрагменты по заданным сложным шаблонам. Например, вытащить номер счета из описания платежа, город из адреса, телефон из контактов клиента и т.п.
Расчет средневзвешенного значения в Excel
Что такое средневзвешенное значение и чем оно отличается от обычного среднего арифметического. Как вычислить средневзвешенное в Excel с помощью формул и в сводной таблице (в том числе с помощью DAX и Power Pivot).
Импорт курса биткойна в Excel через Power Query
Как при помощи Power Query импортировать текущий курс биткойна (или любой другой криптовалюты) с биржи или обменников для оценки стоимости портфеля инвестиций.
Наглядность в сложных формулах
Как при помощи отступов и переносов строк сделать длинные и сложные формулы с вложенными функциями гораздо более наглядными и легкими для понимания.
Сравнение двух таблиц
Как сравнить между собой две таблицы (прайс-листа) и наглядно отобразить отличия между ними - добавленные и удаленные товары, изменение цены и т.д. Разбор 3 способов: с помощью функции ВПР (VLOOKUP), сводной таблицей и через надстройку Power Query.
Трансформация столбца в таблицу
Три способа (формулы, Power Query и макросы) для трансформирования длинного столбца с чередующимися данными в полноценную двумерную таблицу, с которой можно работать.
Самая длинная победная серия
Как подсчитать максимальное количество идущих подряд значений, удовлетворяющих заданному условию? Например, самое большое количество рабочих дней подряд в табеле? Или самую длинную победную серию в спорте?
Совместное редактирование и Автосохранение в Excel 2016
Как работает новый режим совместного редактирования в Microsoft Excel 2016. Зачем нужно Автосохранение, в чем его плюсы и минусы и как его отключить.
Подсветка заданных столбцов на диаграмме
Как автоматически подсвечивать заданный столбец в диаграмме: по заданному условию, с выбором из выпадающего списка, выше/ниже среднего, самый большой или самый маленький и т.д. По сути, реализовать условное форматирование, но не для ячеек, а для столбцов в диаграмме.
Генерация дубликатов строк
Как размножить некоторые строки в таблице, создав для них заданное количество дубликатов. Разбор двух способов: "в лоб" макросом и красиво - через Power Query.
Секционные диаграммы
Как построить секционную гистограмму, где несколько отдельных наборов столбцов (категорий, групп) наглядно форматируются разными цветами.
Поиск совпадений в двух списках
Как подсчитать совпадения в двух диапазонах данных и вывести их отдельным списком. Разбор трех способов: формулами, макросами и с помощью слияния запросов в Power Query.
Слияние двух списков без дубликатов
Как соединить два списка, чтобы в итоге получить один обобщенный список, причем без повторений. Подробный разбор нескольких способов (вручную, формулами, через Power Query).
Визуализация движения по маршруту
Как наглядно отобразить движение объекта (транспорта, службы доставки...) по заданному маршруту на географической карте с привязкой ко времени?
Быстрый прогноз функцией ПРЕДСКАЗ (FORECAST)
Вычисление простого прогноза по линейному тренду с помощью функции ПРЕДСКАЗ (FORECAST).
Превращение текста в число функцией Ч (N)
Как добавить невидимый текстовый комментарий к формуле в ячейке с помощью функции преобразованиия Ч(N).
Разбор функции ДВССЫЛ (INDIRECT) на примерах
Подробный разбор на примерах нюансов и особенностей функции ДВССЫЛ (INDIRECT), позволяющей реализовать косвенные текстовые ссылки.
Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)
Подробный разбор функции замены текста ПОДСТАВИТЬ (SUBSTITUTE) и примеров ее использования для удаления неразрывных пробелов, подсчета количества слов, извлечения первых двух слов из текста.
Суммирование по множеству условий функцией БДСУММ (DSUM)
Как использовать одну из малоизвестных, но крайне полезных функций БДСУММ (DSUM) для выборочного суммирования по одному или нескольким сложным условиям (еще и связанным между собой И-ИЛИ).
Удаление лишних пробелов функцией СЖПРОБЕЛЫ (TRIM) и формулами
Как удалить из текста лишние пробелы в начале, в конце или между словами с помощью функции СЖПРОБЕЛЫ (TRIM) или формул.
Анализ топовых значений функциями НАИБОЛЬШИЙ и НАИМЕНЬШИЙ
Как использовать функции НАИБОЛЬШИЙ (LARGE) и НАИМЕНЬШИЙ (SMALL) для поиска предельных значений, составления рейтингов и ТОПов, сортировки формулами и др.
Частотный анализ по интервалам функцией ЧАСТОТА (FREQUENCY)
Как быстро подсчитать количество значений, попадающих в заданные интервалы "от-до" с помощью функции ЧАСТОТА (FREQUENCY) и формул массива.
Создание внутренних и внешних ссылок функцией ГИПЕРССЫЛКА
Максимально полное описание всех возможностей функции ГИПЕРССЫЛКА, умеющей создавать ссылки на ячейки внутри книги, внешние файлы, веб-страницы и т.д.
Получение элемента из набора по номеру функцией ВЫБОР (CHOOSE)
Как с помощью функции ВЫБОР (CHOOSE) реализовать выборку элементов из набора, работу с массивами, динамические итоги и склейку диапазонов.
Как не забивать гвозди микроскопом с функцией СУММПРОИЗВ
Для чего (на самом деле!) нужна функция СУММПРОИЗВ (SUMPRODUCT) и какие замечательные вещи она умеет делать (включая выборочный подсчет из ЗАКРЫТОГО файла).
Поиск точных совпадений с учетом регистра функцией СОВПАД (EXACT)
Большинство функций Excel не различают строчные и прописные буквы. Если же это необходимо, то сможет помочь функция СОВПАД (EXACT), умеющая сравнивать данные с учетом регистра.
Извлечение информации о ячейке функцией ЯЧЕЙКА (CELL)
Как получить подробную информацию о ячейке и ее параметры с помощью функции ЯЧЕЙКА (CELL). Используя ее можно определять тип введенных в ячейку данных, ее формат, наличие защиты и т.п.
Визуализация значками с функцией СИМВОЛ (CHAR)
Как выводить нестандартные символы и пиктограммы с помощью функции СИМВОЛ (CHAR) для визуализации количества проданного товара, динамики роста-падения и т.п.
Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)
Как перехватывать любые, возникающие не по вашей вине, ошибки в формулах, и заменять их на что-то более полезное и удобное с помощью функции ЕСЛИОШИБКА (IFERROR).
Превращение текстовой даты в полноценную функцией ДАТАЗНАЧ (DATEVALUE)
Как превратить в полноценную дату текстовые ее написания вида "8 мар 13", "2017.3.8", "09 сентябрь 2016" и т.д. с помощью функции ДАТАЗНАЧ.
Суммирование по "окну" на листе функцией СМЕЩ (OFFSET)
Как использовать функцию СМЕЩ (OFFSET), чтобы суммировать из динамического диапазона-"окна" на листе с заранее неизвестными размерами и положением.
Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)
Как использовать функцию ПОИСКПОЗ (MATCH) для поиска позиции нужного элемента в списке, первой или последней текстовой ячейки или ячеек с заданным значением в диапазоне.
Подсветка наборов строк цветом
Как наглядно подсветить чередующейся заливкой наборы строк с совпадающими значениями. Подробный разбор 3 способов: вспомогательный столбец, формула массива в условном форматировании и макрос.
Независимая группировка сводных таблиц
Что делать, если вы построили несколько сводных таблиц на основании одного источника и теперь не получается группировать их отдельно - группировка одной сводной влияет на все остальные и наоборот. Как отвязать сводную от общего кэша?
Сборка таблиц из разных файлов Excel с помощью Power Query
Как с помощью надстройки Power Query для Excel собрать данные из нескольких таблиц (разного размера), расположенных в разных книгах Excel - в одну общую таблицу. С автоматическим обновлением в будущем и "причесываниями" данных в процессе.
Левый ВПР
Как реализовать в Excel аналог функции ВПР (VLOOKUP), который будет выдавать значения левее поискового столбца.
5 вариантов использования функции ИНДЕКС (INDEX)
Подробный разбор всех пяти вариантов применения мегамощной функции ИНДЕКС (INDEX): от простого поиска данных в столбце, до двумерного поиска в нескольких таблицах и создания авторастягивающихся диапазонов.
Сводная таблица с текстом в значениях
Обычно в области значений сводных таблиц находятся числовые данные. А что делать, если нужно создать подобие сводной, но с текстом?
Анимированная пузырьковая диаграмма
Как за 2 минуты без формул и сложных действий создать в Excel анимированную пузырьковую диаграмму, шарики которой двигаются, отображая развитие ситуации во времени.
Создание базы данных в Excel
Пошаговый пример создания в Excel небольшой, но полноценной базы данных на примере учета продаж: с формой ввода, связанными таблицами, отчетами и печатными формами.
Преобразование чисел-как-текст в нормальные числа
Иногда Excel начинает воспринимать числа в некоторых ячейках как текст. Из за этого не работают формулы, нарушается сортировка и фильтрация и т.д. Как же исправить ситуацию?
Поиск и подсчет самых частых значений
Как найти самые часто встречающиеся текстовые и числовые значения в большом списке разными способами (сводные таблицы с группировкой, функции ЧАСТОТА и МОДА, формулой массива и т.д.)
Многоликие Представления
Как создать несколько удобных вариантов просмотра одной и той же таблицы с помощью малоизвестного инструмента Microsoft Excel - Представлений (Custom Views).
Поиск последней непустой ячейки в строке или столбце функцией ПРОСМОТР
Как найти значение последней непустой ячейки в строке или столбце таблицы Excel с помощью функции ПРОСМОТР (LOOKUP).
Поиск ближайшего рабочего дня функцией РАБДЕНЬ (WORKDAY)
Что если при расчете сроков нужная вам дата выпадет на выходные? Как найти ближайший рабочий день к заданной дате, учитывая выходные и праздники?
Поиск минимального или максимального значения по условию
Как найти в диапазоне чисел минимальное или максимальное значение по условию с помощью функций МИНЕСЛИ и МАКСЕСЛИ, с помощью формул массива, функции ДМИН или сводной таблицы.
Гистограмма с переменной шириной столбцов
Как в Excel построить гистограмму, где ширина столбцов будет не одинаковой, как обычно, а переменной величиной, отображая еще один параметр.
Календарь этапов проекта
Как с помощью условного форматирования быстро сделать годовой календарь проекта, наглядно отобразив временные интервалы этапов, их пересечения и выходные дни.
Обратный порядок элементов в списке
Как расположить элементы списка в обратном порядке - от последнего до первого. Разбор нескольких способов - от простой сортировки до формулы массива и макросов.
Сколько пятниц в январе 2016 года
Как быстро подсчитать, сколько именно определенных дней недели попадает в заданный интервал дат в календаре. Например, сколько пятниц в январе 2016 года?
Диаграмма-торнадо в Microsoft Excel
Как построить в Excel диаграмму-торнадо для наглядного сравнения двух наборов данных по нескольким параметрам.
Пересечение интервалов дат
Как быстро определить - пересекаются ли имеющиеся интервалы дат? А если пересекаются, то на сколько именно дней? Пригодится всем, кто составляет графики отпусков, дежурств, тренингов и т.п.
Запрет на печать книги Excel
Как запретить другим пользователям распечатывать конфиденциальную информацию из вашей книги Excel.
Сортировка дней рождений
Как отсортировать даты рождения людей не по принципу "старый-молодой", а чтобы определить у кого из них в каком месяце день рождения.
Трехмерный поиск по нескольким листам (ВПР 3D)
Как реализовать поиск по трем измерениям, т.е. нахождение нужного листа в книге, а затем вывод содержимого ячейки с пересечения заданной строки и столбца.
Зачем нужна функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Если вы когда-нибудь пытались сослаться на ячейку сводной таблицы, то должны были встречать эту функцию (от которой большинство шарахается). Зачем она нужна на самом деле и в каких случаях она может здорово помочь?
Полезности для VBA программиста
Обзор полезных программ, утилит и надстроек для программиста на Visual Basic. Если вы хотя бы иногда создаете макросы на VBA в Excel, то что-то из этого списка вам точно пригодится.
Новые возможности Excel 2016
Что нас ждет в Excel 2016? Подробный обзор всех новых инструментов и функций: новые типы диаграмм, надстройка Power Query, изменения в работе сводных таблиц и улучшенные средства прогнозирования.
Умное автозаполнение вниз и вправо
Двойной щелчок по черному крестику в правом нижнем углу ячейки протянет формулу вниз. А если нужно это сделать не до первой пустой ячейки, а до конца таблицы? Или не вниз, а вправо?
Визуализация геоданных на карте
Обзор различных способов для отображения числовых данных из Excel на карте: надстройки Microsoft Power View, Microsoft Power Map, Bing Maps, пузырьковая диаграмма и др.
Поиск и подстановка по нескольким условиям
Как искать в одной таблице и подставлять в другую данные по нескольким критериям, т.е., другими словами, реализовать поиск аналогичный функции ВПР (VLOOKUP) но сразу по нескольким условиям.
Полезная информация в строке состояния
Строка состояния (статуса) висит в окне Excel постоянно, но насколько часто она вам нужна? Как использовать строку состояния максимально эффективно и отображать в ней не только сумму, количество и среднее выделенных ячеек?
Суммирование только видимых ячеек
Как при помощи функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ просуммировать (а также вычислить среднее, минимум, максимум, количество и т.д.) только видимых ячеек оставшихся после применения фильтра, скрытия строк/столбцов, сворачивания группировкой, а также проигнорировать ячейки с ошибками при расчетах.
Подстановка "из ниоткуда"
Как использовать массивы констант и имена, чтобы встроить поисковые таблицы прямо внутрь функций ВПР, ИНДЕКС, ПОИСКПОЗ и им подобных, чтобы подставлять данные на лету и без использования отдельных таблиц.
Динамический диапазон с автоподстройкой размеров
Как в Excel создать динамический диапазон, автоматически подстраивающийся в размерах под реальное количество данных, т.е. раздвигающийся при дописывании новых строк или сжимающийся при их удалении. Такой диапазон потом удобно использовать в сводных таблицах, диаграммах, выпадающих списках или любых формулах.
Копирование настроек печати с листа на лист
Как быстро перенести настройки печати и параметров страницы с одного листа на другие в книге Microsoft Excel, включая области печати и сквозные строки/столбцы.
Стрелки в ячейках
Как добавить к ячейкам красные и зеленые стрелки для наглядного отображения роста или падения цены, прибыли и т.д.
Преобразование формул в значения
6 способов преобразовать формулы в ячейках листа Excel в значения. Как для выделенного диапазона, так и сразу для всего листа или даже целой книги.
Диаграмма "План-Факт"
Как быстро построить диаграмму "план-факт" для наглядного визуального сравнения запланированных и достигнутых значений или, например, бизнес-параметров прошлого года с текущим.
Защита ячеек шифром Виженера
Как надежно зашифровать содержимое ячеек листа Excel шифром Виженера - одним из простых, но очень надежных шифров, который невозможно взломать простыми способами криптоанализа и подбором паролей.
Слишком много различных форматов ячеек
Как победить ошибку "Слишком много различных форматов ячеек" в Excel и очистить файл от избыточного форматирования, лишних стилей и т.п.
Разделительная линия между наборами строк
Как при помощи условного форматирования быстро и наглядно разделить линиями наборы однотипных строк по заданному параметру в большой таблице.
Линии проекции в диаграмме
Как опустить линии проекции от каждой точки данных на оси X и Y на диаграмме в Microsoft Excel.
История изменения ячейки в примечаниях
Простой макрос для записи в примечание к ячейке всех ее прошлых значений с датой-временем и именем пользователя. Очень удобно для хранения истории изменения важных ячеек в ваших отчетах Excel.
Суммирование каждой 2-й, 3-й... N-й ячейки
Несколько способов произвести вычисления (просуммировать, выбрать максимум/минимум, посчитать среднее арифметическое) с каждой 2-ой, 3-ей... n-ой ячейкой в заданном диапазоне, т.е. например сложить каждую четную ячейку или ячейки через одну.
Скрытие содержимого ячейки
Как быстро скрыть содержимое заданных ячеек от чужого взгляда с помощью хитрого стиля и пользовательского формата, не ставя на них пароль.
Склеивание текста по условию
Как при помощи формул, макро-функций или надстройки Power Query склеить (сцепить) текст из нескольких ячеек, но только при выполнении заданного условия. Что-то похожее на функцию СУММЕСЛИ (SUMIF), но - для текста. Причем условия могут быть как точными, так и приблизительными или их может быть несколько.
Диаграмма-шкала (bullet chart) для отображения KPI
Как в Excel построить диаграмму-шкалу или диаграмму-термометр (bullet chart) для наглядного отображения достигнутых значений по сравнению с запланированными. Будет полезно для визуализации ключевых параметров (KPI) в бизнес-отчетах и презентациях.
Расчет кредита в Excel
Как при помощи пары формул и функций создать в Excel универсальный кредитный калькулятор, т.е. рассчитать выплаты и переплату по кредиту при известных начальных параметрах кредита (процентной ставке, сроку и сумме). Как сделать расчет досрочных выплат по кредиту по сценариям сокращения срока или размера выплаты. Как смоделировать кредит с неравномерными и непериодическими выплатами.
Парная подсветка дубликатов
Как быстро выделить совпадающие значения в большом списке соответствующими цветами?
Расширенный фильтр и немного магии
Как при помощи простого макроса превратить скучный и громоздкий Расширенный фильтр в мегаполезный и удобный инструмент для фильтрации списков по большому количеству сложных условий.
Отслеживание входа пользователей в книгу Excel
Как отслеживать всех пользователей, которые когда-либо открывали ваш файл с важными данными? Как фиксировать их имена и дату-время входа и выхода из книги?
Обзор и тестирование Excel для iPad
Подробный обзор и тестирование возможностей нового Excel для iPad (с видео). Какие функции поддерживаются, что может и чего не может мобильное приложение по сравнению с десктопной версией.
Подсветка дат и сроков
Как подсвечивать разными цветами в большом списке прошлые, актуальные и будущие даты. Подойдет для наглядного отображения заканчивающихся договоров, актов, будущих поставок и т.п.
Подсветка лишних пробелов
Как подсветить ячейки с лишними пробелами в полях ввода формы, намекнув пользователю, что он некорректно ввел данные.
Подробнее...
Печатная подложка
Как создать печатную подложку в Excel, т.е. разместить на каждом печатном листе под таблицами полупрозрачное изображение (логотип) или текст ("Секретно", "Образец" и т.д.)
Зачистка текста
Несколько способов очистить текстовые данные в Excel от ненужных символов, мешающих последующей обработке: лишних пробелов, непечатаемых символов, апострофов, латиницы и т.д.
Копирование формул без сдвига ссылок
Несколько способов скопировать диапазон с формулами так, чтобы ссылки в них не сместились на новые ячейки, а остались точно такими же.
Подробнее...
Универсальный календарь формулой
Как с помощью всего одной формулы реализовать на листе Excel универсальный "живой" календарь на любой месяц любого года с интерактивностью.
Вычисления без формул
Формулы в Excel - это здорово, но иногда проще и быстрее посчитать что-то на скорую руку без них.
Подробнее...
Подсчет количества уникальных значений
Несколько способов подсчитать количество уникальных (неповторяющихся) значений в заданном диапазоне ячеек.
Цвет диаграммы из ячеек с ее данными
Небольшой макрос, назначающий столбцам (точкам, долям) на диаграмме цвета, которые были использованы для заливки ячеек с исходными данными для диаграммы.
Подробнее...
Условное форматирование в Excel 2007-2013
Как залить ячейки на основе их значений? Как использовать гистограммы и значки в ячейках для визуализации числовых данных? Видеоурок по основам использования условного форматирования в Excel 2007-2013 для наглядного представления числовой информации.
Хитрости сортировки
Как корректно отсортировать столбец, в котором содержатся одновременно и текстовые и числовые значения? Как правильно сортировать месяцы, дни недели и должности, чтобы они выстроились не по алфавиту, а в нужном порядке по смыслу?
Диаграмма Парето
5 способов построить диаграмму Парето (принцип 20/80) для наглядного отображения ключевых факторов, влияющих на ваши бизнес-процессы.
Выпадающий список с мультивыбором
Различные способы реализации выпадающего списка на листе Excel с возможностью выбирать больше одного элемента из заданного набора вариантов и накапливать выбранное в текущей ячейке.
Подбор слагаемых для нужной суммы
Как, имея набор из нескольких чисел, подобрать из них те, которые дадут определенную требуемую сумму?
Выборочное отображение листов пользователям
Как с помощью простых макросов определять имя пользователя и отображать им только определенные, разрешенные к просмотру листы.
Подробнее...
Временная шкала проекта (Project Timeline)
Как построить диаграмму временной шкалы проекта, наглядно отображающую всю историю разработки проекта по этапам на оси времени.
Вставка в отфильтрованные строки
Как копировать и вставлять данные только в отобранные Фильтром строки, игнорируя скрытые.
Подробнее...
Веб-опрос с помощью Excel и OneDrive
Как за несколько минут с помощью Excel и бесплатного сервиса Skydrive создать страницу опроса в интернет с автоматической загрузкой результатов в ваш файл Microsoft Excel.
Обзор надстроек и приложений для Excel 2013
Обзор встроенных в Microsoft Excel 2013 надстроек - дополнительных модулей, позволяющих заметно увеличить набор возможностей и полезных функций этой программы.
Подробнее...
Заставка при открытии книги Excel
Как создать окно-заставку, которая будет автоматически отображаться заданное время (3-5 сек) при открытии книги Excel.
Выборочные вычисления по одному или нескольким критериям
Несколько способов решить одну из наиболее распространенных задач при работе в Microsoft Excel - вычислить итоги (сумму, среднее, количество и т.д.) только для тех строк в таблице, которые удовлетворяют заданному условию или набору из нескольких условий.
Выпадающий список с наполнением
Как создать выпадающий список в ячейке листа Excel, который будет динамически формироваться по заданному диапазону. Т.е. при дописывании новых элементов к таблице данных они будут автоматом добавляться к выпадающему списку, а при удалении ненужных элементов из таблицы - пропадут из него.
Подробнее...
Фильтр исходных данных в сводной таблице
Как при помощи простого макроса отфильтровать те строки в исходной таблице, которые участвовали в расчете заданной ячейки сводной таблицы. Это позволит быстро подогнать результаты сводной под нужные значения.
Подробнее...
Как построить диаграмму "водопад" (waterfall)
Как в Excel построить очень популярную в финансовых отчетах каскадную диаграмму отклонений, известную также под названиями "водопад" (waterfall) или "мост" (bridge) для наглядного отображения динамики изменений какого-либо параметра.
Вычисления по цвету ячеек
Как просуммировать или подсчитать количество или среднее арифметическое ячеек с определенным цветом заливки или шрифта.
Почтовая рассылка
Как с помощью связки Excel-Word-Outlook быстро организовать почтовую рассылку типовых писем по списку адресатов. Без макросов и программирования, только штатными средствами.
Сохранение листов книги как отдельных файлов
Если вам нужно разобрать книгу на листы, т.е. сохранить каждый лист как отдельный файл - поможет простой макрос.
Подробнее...
Новые возможности диаграмм в Excel 2013
Что нового появилось в построении диаграмм в Microsoft Excel 2013. Новый мастер диаграмм и рекомендуемые диаграммы. Настройка параметров и форматирования диаграмм, фильтрация данных в диаграмме. Подписи данных из ячеек. Анимация диаграмм.
Подробнее...
Новые возможности сводных таблиц в Excel 2013
Рекомендованные сводные таблицы. Построение сводной по нескольким исходным таблицам. Фильтрация с помощью Временной шкалы. Новые функции вычислений полей значений. "Погружение" в данные с помощью Экспресс-просмотра.
Подробнее...
Консолидация (объединение) данных из нескольких таблиц в одну
Как, имея несколько разных таблиц на разных листах или в разных файлах, свести их в один итоговый отчет?
Суперфильтр на VBA
Быстрая и удобная альтернатива стандартному Автофильтру с помощью небольшого макроса. Умеет искать по маске и одному/двум сложным условиям (с И и ИЛИ). Без мыши. В реальном времени.
Умные таблицы в Excel
Как превратить обычный диапазон с данными в супер удобную для работы таблицу с автоматически подстраивающимися размерами, приятным дизайном и кучей других бонусов.
Фильтрация сводных таблиц Срезами и Шкалой
Красивый и удобный способ быстро и наглядно отбирать данные в сводной таблице по нескольким параметрам при помощи интерактивных графических кнопочных фильтров - срезов.
Быстрая настройка форматов данных в сводной таблице
Как с помощью простого макроса быстро настроить форматы всех полей в сводной таблице, подтянув их из исходных данных.
Подробнее...
Сводная таблица по нескольким диапазонам данных
3 способа построить полноценную сводную таблицу не по одному, а по нескольким диапазонам данных, находящихся на разных листах.
Настройка вычислений в сводных таблицах
Как произвести тонкую настройку вычисляемых данных в сводной таблице (поменять сумму на количество, посчитать долевые проценты, отобразить динамику и т.д.).
Группировка в сводных таблицах
Как в сводной таблице сгруппировать даты с нужным шагом (день-месяц-квартал...), числа в заданные диапазоны (от-до) или наименования (товары по категориям).
Создание отчетов при помощи сводных таблиц
Как за несколько секунд из огромной таблицы в несколько тысяч строк сформировать компактный, наглядный и правильный отчет? Один из лучших инструментов во всем Excel!
Импорт курса валют из интернета
Несколько способов импортировать с финансовых сайтов в Excel с последующим автоматическим обновлением курс доллара, евро, котировки акций или любую другую динамическую информацию на заданную дату (или интервал дат).
Картинка в примечании к ячейке
Как вставить картинку или фотографию в примечание к ячейке - по одной или сразу несколько, чтобы получить, например, всплывающие изображения для товаров или портреты сотрудников.
Горячие клавиши в Excel
Удобная таблица с горячими клавишами Excel на русском и английском.
Подробнее...
Изменение рабочей палитры цветов
Как настроить-изменить-восстановить цветовую палитру, которую Excel использует для форматирования ячеек?
Сохранение пользовательских настроек панелей инструментов
Как сохранить или перенести на другой компьютер пользовательские настройки панелей инструментов в Excel (расположение панелей, добавленные нестандартные кнопки, свои вкладки и содержимое панели быстрого доступа и т.д.)
Подробнее...
Увеличение количества откатов в Excel 2003
Как в Excel 2003 увеличить количество отменяемых последних действий (Undo) с 16 до 100!
Подробнее...
Оптимизация доставки
Использование надстройки Поиск решения (Solver) для на примере решения классической задачи в логистике - оптимизации доставки товаров со склада в магазины.
Скрытие/отображение ненужных строк и столбцов
Как быстро скрыть (а потом - отобразить обратно) временно ненужные для просмотра на листе строки или столбцы?
Соответствие команд меню Excel 2003 и ленты Excel 2007
Удобная флеш-анимация для тех, кто недавно перешел на 2007-ю версию Excel и долго ищет привычные команды в новом интерфейсе. Выбираете нужную команду в старом Excel 2003, а вам показывают где ее же найти в Excel 2007.
Подробнее...
Как уменьшить размер файла и ускорить его
Что делать, если ваш рабочий файл "распух" до полусотни мегабайт и Excel жутко тормозит при открытии книги и ее пересчете. Как уменьшить размер файла и ускорить его работу?
Зачем нужен стиль ссылок R1C1
Если у вас в заголовках столбцов листа Excel вместо букв (А,В,С...) цифры и все формулы превратились в непонятную кашу из букв R,C и квадратных скобок - значит вы переключились в режим R1C1. Что это, зачем он нужен и как с ним бороться?
Подробнее...
Объединение двух столбцов с данными
Как быстро объединить данные из двух взаимно не пересекающихся колонок с пустыми ячейками. Без формул, макросов и т.д. - только с помощью мыши.
Удобный просмотр формул и результатов
Простой, но удобный трюк, позволяющий одновременно видеть и формулы и их результаты в ячейках листа. Незаменимая штука при отладке расчетов.
Подробнее...
Поиск ближайшего числа
Как проверить попадание параметра в один из заданных диапазонов и, при этом, обойтись без громоздких вложенных проверок с помощью кучи функций ЕСЛИ (IF)?
Проектная диаграмма Ганта (Gantt Chart) в Excel 2007/2010
Обучающий видеоролик о том, как создать проектную диаграмму Ганта в новых версиях Excel 2007-2010. Можно использовать для создания графиков отпусков, дежурств, тренингов и т.п. в Excel.
Проектная диаграмма Ганта (Gantt Chart) в Excel 2003
Как создать один из основных типов диаграмм в управлении проектами - диаграмму Ганта - в Excel 2003 и старше.
Подробнее...
Использование функции ВПР (VLOOKUP) для подстановки значений
Одна из самых мощных и красивых функций в Excel - функция ВПР (VLOOKUP), позволяющая автоматически находить нужные значения в таблице. О том, как ее правильно использовать - просто и понятно.
Вычисление возраста или стажа функцией РАЗНДАТ (DATEDIF)
Как точно вычислить возраст/стаж человека (или любой другой интервал времени между началом и концом) в полных годах, месяцах или днях с помощью недокументированной функции Excel РАЗНДАТ (DATEDIFF).
Маркированный и нумерованный список в Excel как в Word
Как создать маркированный или нумерованный список с автоматической перенумерацией при редактировании элементов списка как в Microsoft Word.
Извлечение уникальных элементов из диапазона
Как найти все дубликаты в большом списке и удалить их, оставив только уникальные (неповторяющиеся) значения?
Подробнее...
Сортировка диапазона по цвету
Как отсортировать таблицу по цвету заливки ячеек или по цвету шрифта. Синие - к синим, красные - к красным и т.д.
Делим слипшийся текст на части
Несколько способов разделить текст из одной ячейки (столбца) - на несколько. Например, поделить столбец с адресом на три отдельных столбца с индексом, городом, улицей в каждом по-отдельности. Или разделить слипшееся ФИО без пробелов на отдельные слова.
Улучшаем функцию ВПР (VLOOKUP)
Встроенная функция ВПР (VLOOKUP) имеет один существенный недостаток - ищет только первое вхождение искомого значения в таблицу и только в крайнем левом столбце. А если нужно найти 2-е, 3-е и не в крайнем левом?
Поиск отличий в двух списках
Несколько способов решения задачи сравнения двух диапазонов данных и выделения отличий или совпадений между ними.
Поиск символов латиницы в русском тексте
Как быстро найти в символы латиницы, ошибочно набранные вместо русских букв (английская "С" вместо русской "С") в большом списке?
Подробнее...
Проверка текста по маске
Как быстро проверить соответствует ли текст в ячейке заданной маске (с использованием символов *, ?, # и т.д.) Очень полезно для поиска неправильно введенных адресов, номеров автомобилей, паспортов, артикулов и т.п.
Заполнение пустых ячеек значениями из соседних ячеек
Как быстро заполнить пустые ячейки в столбце, вставив туда значения вышестоящих ячеек.
Случайная выборка
Как случайным образом выбрать из большого массива данных заданное количество элементов?
Заполнение бланков данными из таблицы
Как при помощи функции ВПР (VLOOKUP) и простого макроса быстро заполнять любой бланк (счет-фактура, платежка, приходно-кассовый ордер и т.д.) данными из таблицы.
Поиск нужных данных в диапазоне
О двух ближайших "родственниках" функции ВПР (VLOOKUP) - функциях поиска и выборки ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), позволяющих выбирать данные из списка по заданным условиям и реализовать "левый ВПР".
Подробнее...
Формулы массива в Excel
Что такое формула массива? Как ее вводить, редактировать и удалять? Как формулы массива могут упростить и облегчить жизнь пользователя в больших таблицах?
Удаление пустых ячеек из диапазона
Несколько способов удалить из заданного диапазона пустые ячейки, оставив только ячейки с данными.
3 способа склеить текст из нескольких ячеек
Как собрать текст из нескольких ячеек в одну с помощью функций СЦЕП, СЦЕПИТЬ, ОБЪЕДИНИТЬ и объединять ячейки без потери текста всех ячеек кроме верхней левой специальным макросом.
Основные принципы работы с датами и временем в Excel
Как Excel хранит и обрабатывает ячейки с датами и временем. Какие операции возможны над такими ячейками. Типовые ситуации: вычисление разницы между датами в днях, в рабочих днях, вычисление возраста в полных годах, суммирование временных интервалов для вычисления количества рабочих часов и др.
Превращение строк в столбцы и обратно
Как преобразовать горизонтальный диапазон ячеек в вертикальный и обратно.
Номер недели по дате функцией НОМНЕДЕЛИ
Как определить номер рабочей недели для любой заданной даты (по ГОСТ, по ISO и т.д.) с помощью формул и функций НОМНЕДЕЛИ (WEEKNUM) и НОМНЕДЕЛИ.ISO (WEEKNUM.ISO)
Поиск ближайшего похожего текста
Пользовательская функция на VBA для поиска наиболее похожего текста в списке (по максимальному количеству совпадений символов).
Типы ссылок на ячейки в формулах Excel
В чем разница между относительными, смешанными и абсолютными ссылками на ячейки в формулах? Как сделать ссылку на ячейку, которая не будет изменяться ни при копировании исходной ячейки, ни при вставке перед ней новых строк/столбцов?
Создание писем с помощью функции ГИПЕРССЫЛКА
Как быстро создать уже заполненный бланк сообщения электронной почты (получатели, копия, текст) одним нажатием на ячейку.
Подробнее...
Многоразовый ВПР (VLOOKUP)
Как извлечь ВСЕ данные из таблицы по заданному критерию. Функция ВПР (VLOOKUP) находит значение только по первому совпадению, а нужно вытащить все. Поможет хитрая формула массива!
Подробнее...
Сортировка формулой
Несколько способов отсортировать числовой или текстовый список не через меню Данные - Сортировка, а с помощью формул "на лету".
Подробнее...
Конвертирование величин функцией ПРЕОБР (CONVERT)
Сколько грамм в двух унциях и дюймов в пяти метрах? Сколько минут в неделе и грамм в столовой ложке? Статья о том как использовать малоизвестную функцию ПРЕОБР (CONVERT) для конвертации из одной системы мер в другую в Excel.
ВПР (VLOOKUP) с учетом регистра
Как при помощи формулы массива сделать аналог функции ВПР (VLOOKUP), который при поиске будет учитывать регистр и различать строчные и прописные символы.
Двумерный поиск в таблице (ВПР 2D)
Как искать и выбирать нужные данные из двумерной таблицы, т.е. производить выборку не по одному параметру (как функции ВПР или ГПР), а по двум сразу.
Количество слов в ячейке
Простая, но красивая формула для быстрого подсчета количества слов в ячейке.
Подробнее...
Вычисление нужной даты
Как формулой вычислить дату нужного дня недели для заданного месяца и года (например, второй среды мая 2011 г.)
Подробнее...
Проектная диаграмма Ганта с помощью условного форматирования
Как при помощи условного форматирования быстро создать в Excel проектную диаграмму Ганта. Удобно для визуализации этапов любого проекта, графиков отпусков, тренингов и т.д.
Подробнее...
Пользовательские форматы в Excel
Как добавить в Excel нестандартные форматы ячеек, например "кг", "шт", "чел", "тыс.руб" и т.п.
Подробнее...
Полосатая заливка строк таблицы "зеброй"
Несколько способов быстро сделать чересстрочную заливку строк/столбцов таблицы а-ля "зебра" или "тельняшка" для удобства восприятия.
Условное форматирование в Excel 2003
Как сделать так, чтобы ячейки автоматически выделялись цветом при выполнении определенных условий.
Подробнее...
Выделение дубликатов цветом
Как быстро выделить цветом элементы, которые встречаются в списке более одного раза?
Запрет ввода повторяющихся значений
Как запретить пользователю вводить значения, которые уже присутствуют в списке, т.е. обеспечить ввод строго уникальных (неповторяющихся) значений?
Защита данных в Microsoft Excel
Как запретить пользователю менять данные в определенных ячейках листа? Удалять листы, копировать их или менять имя листа? Как зашифровать файл, чтобы прочитать его могли только знающие пароль?
Правильная защита макросом
Как защитить лист так, чтобы пользователь не мог изменять данные на листе, а ваш макрос - мог. Или так, чтобы можно было использовать группировку на защищенном листе?
Подробнее...
Подсветка незащищенных ячеек
Как с помощью простого макроса быстро выделить цветом все незащищенные ячейки на текущем листе.
Удаление пустых строк и столбцов в данных
Как быстро удалить все пустые строки и/или столбцы в большой таблице или на всём листе в Microsoft Excel. 4 способа: вручную, формулами, макросом, запросом Power Query.
Пометка элементов списка
Как имитировать на листе Excel флажки-галочки (мультивыбор) или жирную точку (выбор один-из), чтобы, например, отбирать из списка нужные элементы?
Оглавление книги Excel
Хотите сделать в Excel автоматическое оглавление как в Word, т.е. получить список всех листов Вашей книги в виде гиперссылок и переходить на нужный лист одним щелчком?
Создание макросов и пользовательских функций на VBA
Что такое макросы. Как создавать макросы для автоматизации повторяющихся действий и расширения функционала Microsoft Excel. Как добавить свою пользовательскую функцию (UDF) к стандартному набору функций Excel. Макросы и безопасность.
Координатное выделение
Автоматическая координатная подсветка текущей строки и столбца "крестом" - крайне удобно при просмотре больших таблиц!
Создание резервных копий ценных файлов
Как восстановить потерянную работу, если вы вышли из Excel без сохранения изменений? Или нужно вернуться в работе к состоянию, которое было 2 часа назад? Как научить Excel автоматически сохранять резервную копию ценного файла в заданную папку и спасать потерянные данные?
Макрос-переводчик
Если Вам приходится делать многоязычные отчеты, то Вам пригодится этот макрос, умеющий переводить таблицы Excel с одного языка на другой. Всем работающим в иностранных компаниях посвящается...
Редизайнер таблиц
Специальный макрос, позволяющий переконструировать Вашу "красивую" таблицу в тот вид, который понимает и с которым может работать Excel.
Курс доллара для любой заданной даты
Макрос, вводящий в текущую ячейку курс доллара для любой заданной Вами даты из архива Центробанка РФ (через интернет с сайта www.cbr.ru).
Ячейка с накоплением (нарастающим итогом)
Как сделать ячейку-коллектор, при вводе новых значений в которую они будут суммироваться с уже находящимися там числами?
Подробнее...
Быстрый ввод даты и времени без разделителей
Если вы часто вводите даты и время, то гораздо удобнее делать это без точек и двоеточий. Этот макрос автоматически преобразует введенные с клавиатуры шестизначные числа в дату (110512 в 11.05.2012) и четырехзначные - во время (0735 в 7:35).
Подробнее...
Всплывающий календарь
Если вы часто вводите даты в Excel, то вам должна понравится эта подборка различных вариантов бесплатных всплывающих календарей для быстрого и удобного ввода дат в ячейки.
Сборка листов из разных книг в одну
Макрос для быстрой автоматической сборки листов из нескольких заданных файлов в текущую книгу Excel.
Подробнее...
Отправка книги или листа по электронной почте
Несколько способов отправить лист или всю книгу Excel по электронной почте заданному адресату.
Многоуровневая группировка строк
Как быстро сгруппировать строки в сложной многоуровневой таблице, чтобы потом быстро и удобно сворачивать-разворачивать группы-подгруппы с помощью одного щелчка.
Сумма прописью
Готовая пользовательская функция на VBA для перевода чисел в текст, т.е. в сумму прописью (123 = сто двадцать три).
Список файлов в папке
Макрос для быстрого создания на листе Excel списка всех файлов из заданной папки и ее подпапок.
Автоматическая вставка текущей даты в ячейку при вводе данных
Как при помощи простого макроса сделать так, чтобы при вводе новых данных в таблицу Excel в соседних ячейках автоматически фиксировалась дата и время их занесения.
Подробнее...
Минидиаграммы в ячейках листа
Как наглядно отобразить множество числовых данных в виде миниатюрных графиков разного типа прямо внутри ячеек.
Интерактивная диаграмма
Как создать "живую" интерактивную диаграмму с прокруткой во времени, зумом отдельных фрагментов и включением-выключением нужных рядов с помощью флажков.
Пузырьковая диаграмма (Bubble Chart)
Что такое пузырьковая диаграмма (Bubble chart) и с чем ее едят. Как ее "читать", как должны быть подготовлены исходные данные для ее построения. Как ее создать, настроить и как сделать подписи к пузырькам.
Выпадающий список в ячейке листа
4 способа создать выпадающий (раскрывающийся) список в ячейках листа Excel,чтобы не вводить повторяющиеся значения с клавиатуры, а быстро и безопасно выбирать нужный пункт мышью. Очень удобно, если вам приходится часто вводить одни и те же значения.
Подробнее...
Выбор фото из выпадающего списка
Хотите выбирать наименование товара из выпадающего списка и получать в соседней ячейке фотографию выбранного продукта? Или выбирать человека из списка и получить рядом его фотографию? Причем без всякого программирования - только с помощью формул?
Связанные (зависимые) выпадающие списки
Несколько способов решения весьма распространенной задачи: представьте себе два выпадающих списка в двух ячейках, причем от того, что выбрано в первом списке - зависит содержимое второго. Например, в первом - страны, а во втором - города выбранной страны. Или в первом - категории товаров, а во втором тогда - товары из выбранной категории.
Выпадающий список с данными из другого файла
Как создать в ячейке выпадающий список, содержащий элементы (например, наименования товаров), хранящиеся в другой книге Excel.
Подробнее...
Выпадающий список с удалением использованных элементов
Как создать в ячейке выпадающий список с автоматическим удалением - если один из элементов списка уже был выбран ранее, то он исчезает из списка в следующий раз.
Выпадающий список с добавлением новых элементов
Как создать выпадающий список который будет иметь двустороннюю связь со справочником: при добавлении новых элементов к справочнику - они будут появляться в списке. При вводе нового элемента в выпадающий список - элемент будет добавляться к справочнику.
Быстрое добавление новых данных в диаграмму
Как быстро добавить к имеющейся диаграмме новые данные или настроить это добавление полностью автоматически.
Динамическая гиперссылка
Как с помощью функций ГИПЕРССЫЛКА и ПОИСКПОЗ сделать "живую" ссылку на определенную строку в другой таблице, содержащую нужные вам данные, чтобы потом быстро переходить из одной таблицы в другую.
Транслит
Небольшой макрос для быстрого преобразования русских символов в английские, т.е. кириллицы в транслит (Пупкин в Pupkin и т.п.)