Расширенный фильтр с формулами
Длительность: 5 мин

В расширенном фильтре можно использовать формулы в условиях! Для чего это может пригодиться? Например, вам нужно получить выборку по большому количеству комбинаций — N городов, X продуктов и так далее. Для каждой комбинации понадобится отдельная строка с условием, если подходить к вопросу обычным образом.

Но мы сделаем формулу, которая будет определять, есть ли то или иное значение в нашем списке.

Файл с примером — по ссылке

Это один из десятков уроков курса по подписке на Sponsr. Все уроки доступны по ссылке (каждую неделю — новые):
https://sponsr.ru/excel_magic/

Гистограммы с текстом

Как реализовать такую историю?

Сначала ссылаемся на данные (числа).
Можно сослаться на первое число (=B2) и "протянуть".

А можно скопировать весь диапазон с продажами и вставить как связь (Ctrl + Alt + V — Вставить связь или правая кнопка мыши — вставить ссылку)

Далее вставляем гистограммы и убираем числа из ячеек (тут подробнее про это и вообще про гистограммы)

Затем копируем диапазон с подписями (городами), правая кнопка мыши — Специальная вставка — Связанный рисунок. И тащим вставленный рисунок поверх гистограмм.

Все будет обновляться при изменении данных / сортировке!

Скорая табличная помощь для компаний

Когда я провожу корпоративные вебинары по Excel и/или Google Таблицам, слушатели — сотрудники клиента — могут обращаться (и делают это) с вопросами не только во время и сразу после вебинаров, но и между встречами и после обучения.

Для некоторых это становится самой полезной составляющей: не у всех есть возможность прослушать все вебинары или участвовать в очном обучении. А задачу решить или сделать отчет нужно — и обычно такие вопросы удается быстро закрыть.

Поэтому я решил, что такой формат поддержки может быть и отдельно от вебинаров и длинных программ обучения: поддержка сотрудников компании, когда каждый может обратиться ко мне с вопросами и за помощью по теме:
- Оптимизация и оформление отчетов, визуализация, форматирование
- Формулы
- Сводные таблицы
- Автоматизация

Все это в нужном формате: текстовый ответ, видео-скринкаст, голосовое, с файлом/без — до победного конца (то бишь решения задачи).

Хотите попробовать в вашей компании? Тест-драйв бесплатно.
Напишите мне письмо на [email protected] или оставьте заявку на сайте! Обсудим все детали.

С днем знаний!

В этот день, как и в любой другой, кот Лемур будет благодарен, если вы купите себе или подарите кому-то книгу "Магия таблиц", а если уже покупали — напишете честный и искренний отзыв там, где покупали или тут в комментариях ❤️

У книги на данный момент:
около 360 отзывов на Озоне (суммарно у разных изданий и форматов) — средняя оценка 4.9-5.0 / 5
около 220 на ВБ, средняя оценка 5 / 5

Новый тираж — это 528 страниц в твердом переплете. Есть все самое свежее, вплоть до PIVOTBY и GROUPBY (я не встречал этих и некоторых других самых последних тем пока даже в книгах на английском, хотя покупаю и смотрю более-менее все)

Свежие отзывы:

Отличное руководство. Приобретала в твердой обложке. Книга была упакована в картонную упаковку надёжно.

Отличная книга. Изложено доступно и понятно

Эта книга-просто чудо! Очень много полезной информации в супер доступном объяснении. Мне, как новичку, было все предельно понятно. Рекомендую

Отличное пособие. Спасибо большое вам.

Отличная книга для пользователей электронных таблиц, есть ссылки на скачивание учебных файлов с интернета. Отличная полиграфия и переплёт. Упакована хорошо, надёжно, пришла в идеальном состоянии.

Хорошее качество печати, бумага плотная. Информация в книге будет полезна и как учебная, и как справочная, содержит необходимое и достаточное количество тем для тех, кто часто работает с эксель и гугл таблицами, изложение понятное и корректное

А вот отзыв Николая Павлова, автора проекта "Планета Excel" и замечательных книг:

Уникальность этой книги в том, что впервые под одной обложкой собрана коллекция наиболее эффективных и полезных приемов и функций сразу из Microsoft Excel и Google Sheets – самых мощных, на сегодняшний день, инструментов для работы с электронными таблицами. Это сравнение двух программ, показ их различий и общих возможностей позволит пользователям гибко переключаться на нужный инструмент при необходимости. А это дорогого стоит.

Купить: Озон, ВБ, Литрес (электро), МИФ (бумага и электро)

Вот подключились вы к каким-то данным в Power Query в Excel, наделали манипуляций, и потом вам нужно удалить ряд столбцов, а оставшиеся переупорядочить, сделать другой порядок, отличный от источника данных.

Зажимаем Ctrl, выделяем столбцы, которые нужно оставить, и делаем это в том порядке, в каком нужно, а затем правая кнопка мыши и «Удалить другие столбцы».

Невыделенные столбцы будут удалены, а выделенные останутся и будут сразу в том порядке, в каком вы их выделяли.

Топ-N значений в сводной таблице
Длительность: 7 мин

Вы хотите показать в сводной самые крупные филиалы/сделки/клиенты, выбрав N самых больших/малых значений.
В строке итогов будет сумма выбранных N значений, а не вся сумма по полю. А как показать всю? А можно ли выделить остальных в отдельную строку (все, кроме выбранных N самых больших)? Смотрим!

Фильтруем сводную таблицу, оставляя только самые большие N значений. А также:
— добавляем общий итог по всем значениям, включая отфильтрованные (с помощью модели данных и взлома системы🥷 путем установки обычного фильтра)
— группируем остальные значения в отдельную строку

Еще десятки бесплатных видеоуроков тут:
https://shagabutdinov.ru/video

Excel Cookbook

Вот такая новинка от издательства O'Reilly

Увы, рекомендовать ее не могу: вроде бы очень много тем и идей, от ссылок на ячейки до LAMBDA, VBA и пакета анализа. И объем приличный — почти 550 страниц.

Но вопрос в подаче: это не книга рецептов и решений, это скорее справочник по инструментам. И они подаются без реальных примеров и практически без скриншотов (!)

После прочтения всей книги не отметил для себя ничего нового, и такое бывает редко с хорошими книгами. У того же Билла Джелена все время будет что-то новенькое в любой книге.

А цена высокая (40 долларов за киндл, в России на Озоне более 3000 рублей бумажная)

Про хорошие книги писал здесь.

Извлекаем из исходной таблицы не все столбцы, а только те, что в отдельном списке.
Продолжительность: 8 минут

Варианты в видео:
— Новые формулы в Excel
— Power Query
— Новые и старые формулы в Google Таблицах

Это же видео на Youtube
Десятки других видеоуроков

Макрос: создаем по отдельному файлу для каждого продукта/города/клиента (для каждого уникального значения в столбце)

Итак, вы хотите быстро получить отдельные файлы с данными по каждому значению в том или ином столбце. Забирайте этот макрос, добавляйте его в личную книгу макросов, добавляйте кнопку на панель быстрого доступа и теперь вы можете в любом файле выбрать заголовок любой таблицы/диапазона, нажать эту кнопку и произойдет следующее:

1 В папке с вашей книгой Excel будет создана папка с заголовком ("Продукт", если у вас была активна ячейка с таким заголовком перед вызовом макроса)

2 В этой новой папке будет созданы книги для каждого значения из столбца — по одной на значение. В каждой книге будет данные только по одному этому значению (в случае с продуктом — по одной книге с данными по каждому продукту).

Как добавить макрос в личную книгу макросов, чтобы он был доступен при работе с любыми файлами Excel — читайте здесь. Сам макрос в соседнем сообщении (сохраняйте файл с макросом, заходите Alt+F11 в редактор макросов, добавляйте файл в личную книгу макросов PERSONAL.xlsb — для этого выберите Import File в контекстном меню по правой кнопке мыши)

В очень коротком видео со звуком показываю пример, как именно происходит магия.

Ссылка на макрос

Гистограммы Excel: несколько приемов
Длительность: 9 мин

— гистограммы с отрицательными и положительными значениями (план-факт)
— гистограммы с текстом (связанный рисунок)
— гистограммы в сводной таблице — отдельно от поля со значениями
— применяем гистограммы только к топ-N значений

Ссылка на файл с примерами
Видео на Kinescope (доступно в России)
Видео на Youtube
Также можно посмотреть на сайте, как и десятки других видео

Быстрая фильтрация в сводной таблице

Если вам нужно быстро исключить некоторые значения из сводной: выделите то, что нужно убрать (в строках или столбцах отчета сводной таблицы) и нажмите Ctrl + - (минус).

Данные будут отфильтрованы, те значения, что вы выделяли, будут исключены в фильтре.

Ссылки на несколько листов в формулах Excel

Общий вид ссылки на несколько листов (это ссылка на листы от первого и до последнего по ярлыкам слева направо; если порядок листов в книге изменится, ссылка в формуле не поменяется):

'Первый лист:Последний лист'!Диапазон

Следующая формула суммирует числа из ячеек B2 на листах от "$ счет" и до "Счет в юанях" включительно:

=СУММ('$ счет:Счет в юанях'!B2)

В названиях листов можно использовать символ подстановки — звездочку. Если в книге много листов со словом "Расходы" в названии ("Расходы январь", "Расходы февраль", . . . ). Следующая формула позволит просуммировать ячейки A1 со всех этих листов:

=СУММ('Расходы*'!A1)

Правда, в отличие от ссылки с двоеточием, звездочка в формуле не сохранится - после ввода такой формулы ссылка на лист со звездочкой превратится в формулу с отдельными ссылками:

=СУММ('Расходы январь'!A1;'Расходы февраль'!A1;'Расходы март'!A1;...)

Срезы: несколько советов

Видео без звука: напоминание о том, что с зажатой клавишей Alt срезы (а также другие объекты графического слоя — внедренные диаграммы, фигуры, временные шкалы) двигаются и меняют размеры по границам ячеек.

А также:
— в срезах можно убрать заголовок (часто и так понятно по вариантам, о чем идет речь)

— в срезах можно сделать несколько столбцов — получится более компактный список.

А здесь можно прочитать про подключение одного среза к нескольким сводным таблицам.

Вот вам немного ощущения власти при работе с диаграммами:

Alt + F1 — вставить стандартную диаграмму (внедренную)

F11 — вставить диаграмму на отдельном листе

Ctrl + Shift + C — скопировать форматирование выделенного элемента диаграммы (например, оси или подписи данных)

Ctrl + Shift + V — вставить форматирование. Бывает удобно, когда вы настроили сразу несколько аспектов форматирования (шрифт, начертание, цвет и т.д.) и надо их все перенести на другой элемент.

"Формат по образцу" в диаграммах тоже работает 🐁

P.S. У вас есть любимые приколы про Excel? Кидайте в комментарии 👉🏻

Границы ячеек: быстро добавляем и убираем

Два сочетания клавиш:

Ctrl + Shift + 7 — добавляем внешнюю границу у выделенного диапазона (диапазонов)

Ctrl + SHift + - — удаляем границы

У вас не работает? Скорее всего, причина в том, что перед открытием Excel была русская раскладка.
По той же причине могут не работать сочетания для вставки текущей даты/времени. Напоминаем, там картина следующая:
Если при открытии Excel у вас была русская раскладка:
Ctrl + Shift + 4 для даты
Ctrl + Shift + 6 для времени
Ctrl + Shift + 4 + пробел + Ctrl + Shift + 6 для даты и времени в одной ячейке

Если была английская:
Ctrl + ; для даты
Ctrl + Shift + ; для времени
Ctrl + ;+ пробел + Ctrl + Shift + ; для даты и времени в одной ячейке

Условное форматирование с формулами
Длительность: 13 мин
— Общие принципы условного форматирования с формулами
— Выделяем строку целиком — по значению или отдельному слову
— Выделяем заголовки столбцов, в которых есть хотя бы одна ошибка
— Выделяем всю строку при выполнении плана

Файл с примерами
Видео на Kinescope (доступно в России)
Видео на Youtube
Также можно посмотреть на сайте, как и десятки других видео

Число прописью на новых функциях

Воспринимайте это скорее как развлечение и демонстрацию новых функций, чем как реальное решение. Все-таки для нормальной суммы прописью лучше использовать пользовательские функции на макросах (такие можно найти и отдельно, и в виде надстроек — например, функция есть в составе PLEX Николая Павлова для Excel, и есть надстройка сугубо с подобной функцией NUMBERTEXT Александра Иванова для Google Таблиц)

Что же происходит в формуле на скриншоте:
— получаем число прописью на тайском с помощью старой функции БАТТЕКСТ
— переводим его на русский новой функцией ПЕРЕВОД
— удаляем слова "бат" или "бата" и точку с пробелом или без него из перевода с помощью РЕГЗАМЕНИТЬ.