Здесь вы найдете больше сотни моих статей по самым лучшим, полезным и эффективным приемам, трюкам и способам работы в Microsoft Excel. Пройдитесь по полному списку или сначала выберите раздел слева и - вперед!
![]()
Приемы
Как реализовать в Excel аналог функции ВПР (VLOOKUP), который будет выдавать значения левее поискового столбца.
Выпадающий список с данными из другого файла
Как создать в ячейке выпадающий список, содержащий элементы (например, наименования товаров), хранящиеся в другой книге Excel.
Подробнее...
Как запретить пользователю вводить значения, которые уже присутствуют в списке, т.е. обеспечить ввод строго уникальных (неповторяющихся) значений?
Как построить диаграмму временной шкалы проекта, наглядно отображающую всю историю разработки проекта по этапам на оси времени.
Как отслеживать всех пользователей, которые когда-либо открывали ваш файл с важными данными? Как фиксировать их имена и дату-время входа и выхода из книги?
Вычисления без формул
Формулы в Excel - это здорово, но иногда проще и быстрее посчитать что-то на скорую руку без них.
Подробнее...
Как реализовать поиск по трем измерениям, т.е. нахождение нужного листа в книге, а затем вывод содержимого ячейки с пересечения заданной строки и столбца.
Как быстро объединить данные из двух взаимно не пересекающихся колонок с пустыми ячейками. Без формул, макросов и т.д. - только с помощью мыши.
Как при помощи функции ВПР (VLOOKUP) и простого макроса быстро заполнять любой бланк (счет-фактура, платежка, приходно-кассовый ордер и т.д.) данными из таблицы.
Как при помощи формулы массива сделать аналог функции ВПР (VLOOKUP), который при поиске будет учитывать регистр и различать строчные и прописные символы.
Как построить сводную таблицу по исходным данным с многострочной шапкой, преобразовав их в нормальный вид с помощью надстройки Power Query.
Подробный разбор революционно нового инструмента Excel - динамических массивов (Dynamic Arrays). Логика их работы, нюансы, плюсы и минусы, совместимость со старыми версиями.
Встроенная функция ВПР (VLOOKUP) имеет один существенный недостаток - ищет только первое вхождение искомого значения в таблицу и только в крайнем левом столбце. А если нужно найти 2-е, 3-е и не в крайнем левом?
Как построить в Excel диаграмму-торнадо для наглядного сравнения двух наборов данных по нескольким параметрам.
Как создать печатную подложку в Excel, т.е. разместить на каждом печатном листе под таблицами полупрозрачное изображение (логотип) или текст ("Секретно", "Образец" и т.д.)
Как отсортировать таблицу по цвету заливки ячеек или по цвету шрифта. Синие - к синим, красные - к красным и т.д.
Как фильтровать сразу несколько сводных таблиц в дашборде с помощью общего среза. Разбор 2 способов для сводных, построенных по одному и по разным источникам данных.
Подсветка лишних пробелов
Как подсветить ячейки с лишними пробелами в полях ввода формы, намекнув пользователю, что он некорректно ввел данные.
Подробнее...
Сколько грамм в двух унциях и дюймов в пяти метрах? Сколько минут в неделе и грамм в столовой ложке? Статья о том как использовать малоизвестную функцию ПРЕОБР (CONVERT) для конвертации из одной системы мер в другую в Excel.
Цвет диаграммы из ячеек с ее данными
Небольшой макрос, назначающий столбцам (точкам, долям) на диаграмме цвета, которые были использованы для заливки ячеек с исходными данными для диаграммы.
Подробнее...
Личная Книга Макросов (Personal Macro Workbook) - что это такое, как ее создать и использовать в качестве личной библиотеки для хранения и запуска самых нужных вам в повседневной работе макросов и функций.
Как при помощи Power Query импортировать текущий курс биткойна (или любой другой криптовалюты) с биржи или обменников для оценки стоимости портфеля инвестиций.
Как с помощью Power Query быстро "размазать" бюджет по дням в списке проектов, построив упрощенный вариант диаграммы Ганта в Excel.
Правильная защита макросом
Как защитить лист так, чтобы пользователь не мог изменять данные на листе, а ваш макрос - мог. Или так, чтобы можно было использовать группировку на защищенном листе?
Подробнее...
Если вы часто вводите даты в Excel, то вам должна понравится эта подборка различных вариантов бесплатных всплывающих календарей для быстрого и удобного ввода дат в ячейки.
Как быстро перенести настройки печати и параметров страницы с одного листа на другие в книге Microsoft Excel, включая области печати и сквозные строки/столбцы.
Как в Excel построить гистограмму, где ширина столбцов будет не одинаковой, как обычно, а переменной величиной, отображая еще один параметр.
Как получить подробную информацию о ячейке и ее параметры с помощью функции ЯЧЕЙКА (CELL). Используя ее можно определять тип введенных в ячейку данных, ее формат, наличие защиты и т.п.
Как добавить к ячейкам красные и зеленые стрелки для наглядного отображения роста или падения цены, прибыли и т.д.
Как сделать в Excel 2013 и новее выпадающий список, при выборе из которого рядом будет появляться изображение выбранного объекта (товара, человека и т.д.)
Как быстро сгенерировать все возможные фразы, состоящие из заданных наборов слов в любом порядке, используя Декартово произведение множеств с помощью формул или Power Query.
Увеличение количества откатов в Excel 2003
Как в Excel 2003 увеличить количество отменяемых последних действий (Undo) с 16 до 100!
Подробнее...
Как с помощью простого макроса быстро выделить цветом все незащищенные ячейки на текущем листе.
Новые возможности сводных таблиц в Excel 2013
Рекомендованные сводные таблицы. Построение сводной по нескольким исходным таблицам. Фильтрация с помощью Временной шкалы. Новые функции вычислений полей значений. "Погружение" в данные с помощью Экспресс-просмотра.
Подробнее...
Как с помощью формулы произвести массовую замену одного текста (или его фрагмента) на другой по имеющейся таблице подстановок (справочнику).
Выборочное отображение листов пользователям
Как с помощью простых макросов определять имя пользователя и отображать им только определенные, разрешенные к просмотру листы.
Подробнее...
Создание писем с помощью функции ГИПЕРССЫЛКА
Как быстро создать уже заполненный бланк сообщения электронной почты (получатели, копия, текст) одним нажатием на ячейку.
Подробнее...
Строка состояния (статуса) висит в окне Excel постоянно, но насколько часто она вам нужна? Как использовать строку состояния максимально эффективно и отображать в ней не только сумму, количество и среднее выделенных ячеек?
Как использовать бесплатную надстройку Fuzzy Lookup для нечеткого поиска ближайших текстовых соответствий в двух таблицах на примере поиска совпадающих адресов.
Три способа реализовать горизонтальную фильтрацию столбцов, которая изначально отсутствует в Microsoft Excel: с помощью функции ФИЛЬТР, запроса Power Query + сводная таблица и макросом на VBA.
Как корректно отсортировать столбец, в котором содержатся одновременно и текстовые и числовые значения? Как правильно сортировать месяцы, дни недели и должности, чтобы они выстроились не по алфавиту, а в нужном порядке по смыслу?
Соответствие команд меню Excel 2003 и ленты Excel 2007
Удобная флеш-анимация для тех, кто недавно перешел на 2007-ю версию Excel и долго ищет привычные команды в новом интерфейсе. Выбираете нужную команду в старом Excel 2003, а вам показывают где ее же найти в Excel 2007.
Подробнее...
Большинство функций Excel не различают строчные и прописные буквы. Если же это необходимо, то сможет помочь функция СОВПАД (EXACT), умеющая сравнивать данные с учетом регистра.
Как наглядно подсветить чередующейся заливкой наборы строк с совпадающими значениями. Подробный разбор 3 способов: вспомогательный столбец, формула массива в условном форматировании и макрос.
Как быстро построить диаграмму "план-факт" для наглядного визуального сравнения запланированных и достигнутых значений или, например, бизнес-параметров прошлого года с текущим.
Как использовать функции НАИБОЛЬШИЙ (LARGE) и НАИМЕНЬШИЙ (SMALL) для поиска предельных значений, составления рейтингов и ТОПов, сортировки формулами и др.
Как добавить невидимый текстовый комментарий к формуле в ячейке с помощью функции преобразованиия Ч(N).
Как запретить другим пользователям распечатывать конфиденциальную информацию из вашей книги Excel.
