Объединяем умные таблицы в одну: формулы и Power Query

В видео разбираем такую задачу: собрать данные из нескольких умных таблиц.

Если у вас Microsoft 365, то можно наслаждаться новыми формулами и использовать функцию ВСТОЛБИК / VSTACK. Так же в видео разбираем, как с ее помощью в сочетании с функцией ФИЛЬТР / FILTER фильтровать данные "в режиме реального времени" и добавить к результату фильтрации заголовки.

Если версии 2010 и новее, то можно с помощью Power Query объединить таблицы в один запрос и далее анализировать данные вместе с помощью сводной таблицы или просто выгрузить на лист как одну таблицу.

Добавляем гистограммы в сводной таблице отдельным столбцом

Друзья, вашему вниманию видео со звуком на пару минут — разбираем, как в сводной добавить отдельный столбец с гистограммами.
Если вы хотите, чтобы визуализация была не "поверх ячеек" с данными, а отдельным столбиком, и чтобы это было частью сводной (то есть отражала актуальные данные в случае обновления исходника и соответственно сводной) — это способ для вас.

В двух словах: мы добавляем еще один столбец с теми же суммами, применяем к нему условное форматирование (это могут быть не только гистограммы, но и значки / цветовая шкала) и потом в настройках правила условного форматирования включаем опцию "Показывать только столбец" (Show Bar Only).

Хотите скрыть все объекты на графическом слое Excel? Нажмите Ctrl + 6.

Это слой, по которому плавают диаграммы, срезы (умных таблиц и сводных) и временные шкалы (сводных таблиц), фигуры, изображения (но только те, что вставлены "над ячейками", а не "в ячейках").

Чтобы все это дело временно убрать, нажмите Ctrl + 6. А еще одно нажатие этого сочетания вернет все на место.

Спасибо каналу "Финансовый Гиппогриф" за этот лайфхак 😺

И еще один совет по работе с объектами, которые находятся "над ячейками" (на графическом слое).

Если вы будете их перемещать или менять их размеры с нажатой клавишей Alt, то они будут выравниваться по границам ячеек, а не произвольно.
Этот прием очень помогает аккуратно выравнивать диаграммы 😸

Автосумма: одним движением суммы по всем столбцам/месяцам.

Сочетание клавиш Alt + = позволяет получить сумму быстро, не вводя руками функцию СУММ / SUM.
Если выделить ячейку под столбцом с числами и нажать Alt + =, то получим сумму по этому столбцу (одну функцию СУММ).
Уточняем: речь про "просто Alt", то есть левый Alt. Правый Alt заменяет сочетание Ctrl+Alt и в сочетании с плюсом-минусом будет менять масштаб листа.

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

Нужно напечатать много строк, которые точно не влезут на одну страницу? Закрепляем строки, чтобы заголовки были на каждой странице при печати!

Разметка страницы — Печатать заголовки — Сквозные строки
Page Layout — Print Titles — Rows to repeat at top

И вводим / выделяем строки, которые нужно выводить на каждой странице при печати. Со столбцами, разумеется, работает аналогично 😺

Повторное применение фильтра

Вы поставили фильтр (Ctrl + Shift + L, кстати). Поменяли что-то в данных. И вот некоторые строки, в которых вы вносили изменения, уже фильтрации не соответствуют. Если добавились новые данные в конце таблицы — они тоже не отфильтруются автоматически. Как отобразить актуальные данные?
Не нужно отключать фильтр и настраивать снова.

Просто нажимайте Ctrl + Alt + L / . Или кнопку "Повторить" (Reapply) на ленте инструментов рядом с кнопкой фильтра (на вкладке "Данные" / Data).

Добавляем к дате день недели и выделяем выходные

Допустим, мы с вами хотим видеть в каждой дате день недели — не "01.01.2024", как по умолчанию, а "01.01.2024 Пн".

Для этого заходим в формат ячеек (Ctrl + 1) и добавляем к формату "ДДД" (DDD). Это краткое обозначение дня недели ("Пн"). Для полного ("Понедельник") понадобится код "ДДДД" (DDDD).

Ну а чтобы выделить цветом выходные (или другие дни) — воспользуемся условным форматированием (Conditional Formatting).
Зададим правило с формулой, а в ней будем использовать функцию ДЕНЬНЕД / WEEKDAY.
Она возвращает порядковый номер дня недели. Чтобы нумерация была привычной для нас с вами, добавьте второй аргумент, равный двойке:

=ДЕНЬНЕД (ячейка с первой датой в диапазоне; 2)

Тогда понедельнику будет соответствовать единица (иначе неделя будет начинаться с воскресенья, если пропустить второй аргумент функции), вторнику — двойка и так далее.

И остается добавить условие — день недели у нас должен быть больше 5 (то есть 6 или 7, суббота или воскресенье), чтобы ячейка заливалась цветом.

Режим перехода в конец (End mode)

Нажмите клавишу End — и в строке состояния появится надпись "Режим перехода в конец" (End mode)

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

Заполняем промежуточные шаги с помощью прогрессии

Допустим, вы знаете первое значение и то, к которому нужно прийти. Выделите весь диапазон от первого значения до последнего и вызовите инструмент "Прогрессия" (Series):
Главная — Заполнить (кнопка со стрелкой вниз в правой части ленты) — Прогрессия

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

Средневзвешенная цена

Если у нас есть данные по продажам товаров с разными ценами, мы не можем считать среднюю цену просто функцией СРЗНАЧ / AVERAGE. Ведь мы не будем учитывать число проданных товаров, и, как в примере, средняя цена будет некорректная.

= (650 + 1400 + 2200) / 3 = 1417.

Но мы продали совсем мало товаров за 2200 и за 1400! Фактическая средняя цена должна быть ближе к 650.

Поэтому правильно вычислить сумму продаж в деньгах и потом поделить на все штуки.

Сумму продаж в деньгах можно вычислить одной функций СУММПРОИЗВ / SUMPRODUCT — она перемножает числа из нескольких диапазонов и потом суммирует результаты. Останется только разделить результат на сумму проданных штук. И получится 752 — это уже похоже на правду!

Дано: есть данные за несколько лет с выручкой (или чем-то еще) по дням.

Задача: посмотреть на сезонность, какой месяц "лучше", какой "хуже". На сезонность — то есть на январь за все годы, на февраль за все годы, и так далее.

Для этой задачи извлечем из чемоданчика всемогущий мультитул — сводную таблицу. По умолчанию в сводной даты группируются по годам-кварталам-месяцам, то есть мы смотрим на данные в рамках каждого года. А нам нужно убрать этот верхний уровень, смотреть только на уровень месяцев (или кварталов, если вам нужно сезонность на этом уровне). Для этого группируем данные сами — только по месяцам.
Это можно сделать на ленте: Анализ сводной таблицы — Группировка по полю
или в контекстном меню — щелкаем по полю с датами в сводной правой кнопкой мыши и нажимаем "Группировать" (или нажимаем Г на клавиатуре)

После группировки можно посмотреть на сумму показателя по месяцам, а можно на среднее значение. Еще раз уточняем: теперь это данные за все январи в периоде , то есть мы не смотрим на динамику во времени, а смотрим на сезонность! Если нам нужна динамика от месяца к месяцу, то нужна группировка и по годам, и по месяцам, как было изначально при построении сводной (в большинстве версий Excel поле с датами само группируется в таком формате при его переносе в область строк)

Смотрим на видео!

Нужно выделить все формулы?

Нажимаем Ctrl+G

В открывшемся окне "Переход" нажимаем "Выделить" (Special)

Далее — "Формулы" (Formulas)

Готово. Можно теперь покрасить ячейки с формулами, если хочется.

Хотите, чтобы в фигуре отображался какой-нибудь текст, сформированный формулой?

Например, текущее время с какой-нибудь надписью (Текущее время: 12:00") или что-то другое ("Выручка на дату 01.06: 1,2 млн")?

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

В примере используется функция ТДАТА / NOW — это текущие дата и время. И функция ТЕКСТ / TEXT — напоминаем, при объединении в текст числовых (а дата и время = число) значений они теряют форматирование. Если вам нужно время в заданном формате, например, ЧЧ:ММ, используйте функцию ТЕКСТ, которая превращает число в текст, но в нужном формате.

Суммируем с условием только видимые строки

Просто суммировать (а также считать среднее и еще несколько базовых операций) скрытые строки — это функция SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
А сумма с условием — это SUMIFS / СУММЕСЛИМН. Эта функция считает не скрытые, а все строки.

Как совместить и считать по видимым строкам с условием?

Во всех версиях — со вспомогательным столбцом, в Microsoft 365 можно и формулой, оба варианта здесь:
https://teletype.in/@renat_shagabutdinov/subtotal_sumifs

Друзья, мы с Лемуром рады сообщить, что вышло второе (дополненное и обновленное) издание книги "Магия таблиц"!

✅Теперь в твердом переплете — в отзывах жаловались, что брали книгу в подарок, а она приезжала не в идеальном состоянии. Теперь это увесистый кирпичик в переплете, который должен доехать до вас в сохранности и будет достойным подарком любому герою таблиц и формул

✅Добавилось 30 страниц материала — больше про модель данных (Power Pivot), про текстовые функции и другое.

✅Добавился справочник функций: все 130 с лишним функций, упоминаемых в книге — на каких страницах их найти, где они есть (Excel/Google Таблицы/везде). На русском и английском языках, как и все в книге.

✅Новое издание узнаете по твердому переплету и кругляшу на обложке. Купить можно, например, тут:
Wildberries
Book24
Издательство
Электро — Литрес

Первый тираж был продан менее чем за полгода, на Озоне было более 120 отзывов (4.8 / 5), на WIldberries более 60 (4.9 / 5). Вот некоторые отзывы:

книга очень полезная даже продвинутым пользователям!

Просто сказка, такой справочник подсказок по Google Sheets вместо душного официального справочник от Гугла, давно искала.

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

Выделяем цветом формулы по какому-то признаку

Вы хотите выделить визуально "старые формулы массива" (из версий до 2019 включительно), или формулы, ссылающиеся на какой-то лист, или формулы с определенными функциями.

Получить текст формулы можно с помощью функции Ф.ТЕКСТ / FORMULATEXT. Искать в этом тексте какой-то признак можно с помощью функции НАЙТИ / FIND.
И если все это засунуть в условное форматирование, то мы получим возможность выделять визуально формулы, содержащие что-нибудь!

Например, старые формулы массива можно выделить по наличию фигурной скобки:

=НАЙТИ("{";Ф.ТЕКСТ(первая ячейка форматируемого диапазона))

Ссылки на лист с названием — по этому самому названию

=НАЙТИ("название листа";Ф.ТЕКСТ(первая ячейка ...))

Определенные функции — по их названию. Например, ПРОСМОТРX, которой нет в старых версиях:

=НАЙТИ("ПРОСМОТРX";Ф.ТЕКСТ(ячейка))

А вот выделить формулы со старой функцией ПРОСМОТР можно, добавив к "запросу" скобку — иначе будут выделяться формулы, где есть и ПРОСМОТР, и ПРОСМОТРX.
=НАЙТИ("ПРОСМОТР(";Ф.ТЕКСТ(ячейка))

Мгновенное заполнение (Flash Fill)

— когда вы нажимаете Ctrl+E (или включаете мгновенное заполнение с ленты инструментов, Главная — Заполнить), Excel анализирует всю строку, а не один столбец. Так что можно собирать данные из нескольких столбцов
— Можно исправлять регистр текста
— Можно добавлять символы, например, точки

Все это — в примере с ФИО, где мы добавляем точки, делая инициалы вместо полных ИО, собираем данные из трех столбцов, исправляем регистр. Все это без формул и быстро :) Но только с версии Excel 2013 включительно.

Часто создаете "умные" таблицы?

Хорошая практика — их переименовывать (чтобы в формулах ссылаться не на "Таблица1", "Таблица2", а на "Прайс" или "Остатки")

Если приходится переименовывать их часто, поле "Имя таблицы" можно добавить на панель быстрого доступа! И оно всегда будет наверху во всех книгах Excel при любой активной вкладке ленты инструментов.

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

Чтобы добавить инструмент на панель, просто щелкните по нему правой кнопкой мыши и выберите соответствующую команду в контекстном меню.