Топ-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 Таблиц)

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

Выделение цветом (не)защищаемых ячеек. Условное форматирование и функция ЯЧЕЙКА / CELL
Длительность: 4 мин

Все ячейки в Excel по умолчанию являются защищаемыми (и эта защита вступает в силу, когда защищен лист).

Как найти те ячейки, у которых защиты нет (или только те, у которых она есть)? Будем использовать функцию ЯЧЕЙКА / CELL, которая в числе прочего возвращает информацию о защите. И применим ее в формуле условного форматирования, чтобы автоматически раскрашивать незащищаемые ячейки.

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

Магия табличных формул

Что там с видеоуроками на Sponsr?

Более 30 видеоуроков уже доступны — это 5 модулей:
1 Основы формул. Все нюансы, формулы в Google Таблицах, ошибки в формулах и т.д.
2 Даты и время. От текущей даты до рабочих дней и извлечения элементов даты
3 Текст. Основы, функции, регулярки.
4 Логические значения и условия. От флажков до расширенного фильтра с формулами. Формулы в проверке данных и условном форматировании.
5 Поиск данных. ВПР, ПРОСМОТРX, магия функции ИНДЕКС (ей будут посвящены несколько видеоуроков), поиск по 2 критериям, на разных листах, и многое другое

Что впереди?
— Еще видео про поиск, включая функцию СМЕЩ и полностью универсальную формулу поиска
— Формулы массива — старые, новые и гуглотабличные
— LET и LAMBDA
— Формулы кубов и Power Pivot
— Пользовательские функции
— И многое другое. Пожелания и заявки принимаются 😸

Все это с файлами-примерами.

Где подписаться?
Тут: https://sponsr.ru/excel_magic

Старине Экселю сегодня 40 🤯
Мы его с этим мощным юбилеем поздравляем.

Хотите прочувствовать масштаб изменений в табличном редакторе за это время?
Загляните сюда — тут мы писали, сколько функций было в 1979 году в VisiCalc.

Сегодня в честь праздника в Excel Blog анонсировали очередное новшество — Agent Mode. Мало им функции COPILOT, теперь в Excel будет встроен ИИ. Большинству простых людей вроде нас с вами это удовольствие пока недоступно — все впереди. Но если у нас есть хотя бы Excel 2013 — значит, у нас есть Мгновенное заполнение, а это почти ИИ 😸

Картинка оттуда же, из блога Excel.

С нашей стороны в честь ДР кот Лемур организует лютую скидку на два наших табличных курса. На самом деле, очень скоро мы прекратим полностью их продажу на сайте и они будут доступны только в издательстве МИФ — позже будут все детали (в любом случае у всех купивших есть и будет вечный доступ и все обновления)
Магия новых функций
Сводные таблицы Google Spreadsheets

Сколько?
790 рублей за любой из курсов.
Это вечный доступ, в том числе ко всем будущим обновлениям (например, в курсе по новым функциям сначала было на 5 уроков меньше, чем сейчас, ибо ... появляются новые новые функции в Excel).

Что в курсах?

Магия новых функций Excel. Массивы, регулярные выражения и многое другое
✅Все новые функции: 11 друзей LAMBDA, LET, манипуляции с массивами, регулярки, SORT и FILTER, ПРОСМОТРX, PIVOTBY и GROUPBY, флажки и многое другое
✅20 видео + текстовые материалы, исходные и готовые файлы в формате XLSX
✅Для счастливых обладателей Microsoft 365 с новыми функциями и для пользователей Google Таблиц (ибо там есть почти все функции, бесплатно… но с регистрацией аккаунта, конечно)

Сводные таблицы Google Spreadsheets. От основ и нюансов до построения сводных с помощью QUERY и LAMBDA
✅Для начинающих и продолжающих пользователей Google Таблиц и переходящих туда из Excel. Вообще все про сводные Google, от основ до нюансов вокруг сводных (от подготовки данных и рассчитываемых полей до визуализации и построения "сводных" LAMBDA-формулами)
✅20 видео, исходные и готовые файлы в формате Google Таблиц
✅Сводные — что в Excel, что в Google — зачастую могут решать до 80-90% ваших задач по анализу данных :)