Если при создании сводной таблицы вы включите флажок "Добавить эти данные в модель данных" (Add this data to Data Model), то впоследствии можно будет превратить сводную таблицу в формулы.

Что это за формулы такие? Формулы куба. Они могут напомнить вам функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA), но она получает данные из существующей сводной. А функции куба заменяют собой сводную! И проще всего получить их в готовом виде, чтобы изучить, преобразовав существующую сводную таблицу.

Строим сводную таблицу — включаем флажок "Добавить эти данные в модель данных" — переходим на вкладку "Анализ сводной таблицы" и там выбираем "Средства OLAP" — Преобразовать в формулы" (OLAP tools — Convert to Formulas).

Что изменится? Каждая ячейка сводной станет независимой формулой. Больше не будет единого объекта — сводной таблицы — будут отдельные формулы. Вы сможете переупорядочить вашу сводную как вам захочется (см пример на видео без звука).

Какие это функции?
КУБЗНАЧЕНИЕ / CUBEVALUE — это как область значений сводной, в ней задается подключение (к модели данных, первый аргумент = "ThisWorkbookDataModel") и ссылки на заголовки столбца, названия элементов (в нашем примере категория ресторана из области строк), может быть ссылка и на срез.
Заголовки в сводной (и заголовок области значений в духе "Сумма по полю ...", и названия элементов в строках/столбцах) будут заданы другой функцией — КУБЭЛЕМЕНТ / CUBEMEMBER.
Еще и другие функции куборв, например, КУБМНОЖ / CUBESET (возвращает список всех значений из столбца) и КУБПОРЭЛЕМЕНТ / CUBERANKEDMEMBER, которая из ячейки с функцией КУБМНОЖ может извлечь значение по его порядковому номеру.

Случайность в квадрате: как визуализировать вероятность

Если мы хотим наглядно показать, что что-то будет происходить примерно в 10%, 20% или N% случаев, можно поступить так:

1. Сгенерировать случайные числа в каком-то интервале, например от 1 до 100
В Excel 365 нам поможет функция СЛМАССИВ / RANDARRAY, в старых версиях СЛЧИС / RAND
(число от 0 до 1) или СЛУЧМЕЖДУ / RANDBETWEEN (целое число в заданном интервале)

Одна формула для нового Excel:

=СЛМАССИВ(число строк; число столбцов; 1; 100)

Отдельная формула, которую нужно вставить в каждую ячейку — для старых версий:

=СЛУЧМЕЖДУ(1;100)

2. Оставить в ячейках какой-нибудь знак, допустим, единицу, для тех случаев, когда случайное число меньше N, допустим, 10:

=ЕСЛИ(СЛМАССИВ(число строк; число столбцов; 1; 100)<=10; 1; "")

=ЕСЛИ(СЛУЧМЕЖДУ(1;100)<=10;1;"")

3 Сделать правило условного форматирования — для ячеек с единицей применять заливку какого-то цвета и шрифт того же цвета (чтобы скрыть единицы). Готово!
Для большей красоты можно добавить границу вокруг всего диапазона, убрать сетку на листе — на что хватит фантазии.

Весь процесс в коротком видео без звука.

Декартово произведение (все комбинации значений) формулой

С новыми функциями можно и формулой (а без них — через Power Query, о чем будет в отдельном посте).

Сначала получаем первый список без пустых значений. Функция ПОСТОЛБЦ / TOCOL вернет его без пустых значений, то есть мы можем сослаться на весь столбец, но исключить пустые вторым аргументом функции (равным 1 для такого случая), чтобы предусмотреть появление новых значений в будущем.

Второй список сделаем строкой с помощью ПОСТРОК / TOROW.

Потом склеим их амперсандом (&), добавив пробел. Получим то, что вы видите на скриншоте справа в столбцах F-I (произведение, а точнее, конкатенация в данном случае, строки на столбец дает прямоугольный диапазон).

Останется сделать его плоским списком — снова с помощью ПОСТОЛБЦ.

=ПОСТОЛБЦ(ПОСТОЛБЦ(первый список;1)&" "&ПОСТРОК(второй список;1))

Есть сотый отзыв у Магии таблиц на Wildberries 🥳

А на Озоне сотня преодолена уже давно. Итого сейчас у книги:
Wildberries 102 отзыва 4.9 / 5🌟
Ozon 146 отзывов 4.9 / 5🌟

Кот Лемур напоминает: сейчас в продаже второе издание. В твердом переплете с дополнениями. 519 страниц. Увесистый подарок вашим друзьям и коллегам — героям ячейки и формулы.

Из последних отзывов:

Большая, толстая книга 519 страниц, надеюсь будет интересно.

Побольше бы таких книг

Книга замечательная, упаковка просто лучшая

Лучше этой книги по Excel нет и быть не может)

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

Декартово произведение (все комбинации значений) в Power Query

Итак, если у вас не новая версия Excel с супергуперпупер функциями с красивыми названиями вроде ПОСТОЛБЦ, придется идти в Power Query:

1 Делаем списки таблицами (Ctrl + T) и добавляем в PQ (Данные — Из таблицы/диапазона). После добавления первого загружаем его только как подключение, чтобы список отдельно не выгружался на лист. Добавляем второй и далее уже остаемся в редакторе PQ

2 Добавляем столбец в одном из запросов (списков). Добавление столбца — Настраиваемый столбец.

В нем просто ссылаемся на другой список (=Таблица1, например)

3 Теперь напротив каждого значения из текущего списка — таблица со всеми значениями из второго списка. Раскроем ее, нажав на кнопку со стрелками в заголовке — получим для каждого значения из первого списка столько строк, сколько есть во втором.

4 Останется объединить два столбца, выбрав в качестве разделителя пробел.

5 И выгрузить это дело в виде таблицы на лист Excel. При добавлении новых значений достаточно будет обновить запрос (Alt+F5) или правой кнопкой мыши — Обновить.

Фокусировка ячейки — пустячок, а приятное новшество!

Раньше такое делали через макросы и формулы условного форматирования. Или только макросами. Или надстройкой. В любом случае без макросов решение было невозможно.

Теперь просто кнопка. Да еще и цвет можно выбирать🔥

Пока в бета-канале обновлений. Ждем, когда довезут до всех пользователей 365.

Вид — Фокусировка ячейки
View — Focus Cell

Считаем уникальные значения: новые, модные и прогрессивные функции

У нас есть список сделок. Мы хотим понять, сколько у нас вообще клиентов (уникальных значений в столбце "Клиент") и сколько товаров покупал каждый клиент (не число сделок, то есть строк; не количество штук; а количество уникальных наименований).

Как решать эту задачу? Сегодня начнем с простого. Но простое доступно, увы, только в Excel 2021 / 365.

Список уникальных значений (или строк в общем случае) можно получить функцией УНИК / UNIQUE.

А потом посчитать, сколько в списке уникальных значений — старой доброй функцией СЧЁТЗ / COUNTA.

=СЧЁТЗ(УНИК(диапазон))

А если нужно количество уникальных товаров, купленных каждым клиентом?

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

=СЧЁТЗ(УНИК(ФИЛЬТР(диапазон с товарами;диапазон с условием=условие)))

В следующих постах обсудим другие варианты решения задачи!

P.S. А в Google Таблицах все можно сделать одной функцией COUNTUNIQUEIFS, считающей уникальные значения с одним или несколькими условиями.

Считаем уникальные значения: старая школа

Как быть в старой версии Excel вплоть до 2019?

Воспользоваться формулами массива, суровыми — с фигурными скобками. Вводить такие нужно, напомним, сочетанием Ctrl+Shift+Enter (руками ввести фигурные скобки не получится).
Вот вариант формулы от Николая Павлова из его мощной книги "Мастер формул":

{=СУММ(1/СЧЁТЕСЛИ(тот же диапазон;тот же диапазон))}

Что тут происходит? Мы для каждого значения в диапазоне считаем, сколько раз оно встречается (функция СЧЁТЕСЛИ / COUNTIF). Допустим, некий клиент встречается 5 раз.
Потом делим единицу на эти числа. Для этого клиента в результате получим пять чисел 0,2. В сумме они всегда дадут единицу, то есть каждому клиенту (уникальному значению) будет соответствовать единица. И нам останется только просуммировать эти единицы, получив нужное число.

А подсчет уникальных с условием? Можно добавить вспомогательный столбец с такой формулой (по мотивам книги Майка Гирвина с очень говорящим названием Ctrl + Shift + Enter):

=СУММ(И(СЧЁТЕСЛИМН($B$2:B2;[@Товар];$A$2:A2;$H$6)=1;[@Клиент]=$H$6))

Ссылка вида $B$2:B2 означает, что мы в каждой строке ссылаемся на диапазон, начинающийся в B2 и заканчивающийся в текущей строке. То есть считаем от начала таблицы до строки, в которой находится формула.

Она будет возвращать единицу только для товаров выбранного клиента (это условие, клиент выбирается в списке в ячейке H6) — но только один раз, когда товар впервые встречается в списке. Для последующих вхождений она будет возвращать нули.
Для других клиентов нули будут априори ( не будет выполнено второе условие внутри СЧЁТЕСЛИМН / COUNTIFS. Нам останется просуммировать единицы во вспомогательном столбце.

Прикрепляем отдельно книгу Excel со всеми формулами — изучайте на здоровье!

Ух! Пожалуй, лучше использовать что-то попроще в старых версиях Excel. В следующем посте поговорим про сводные и Power Query.

Считаем уникальные значения: сводная таблица

А почему бы не вывести в сводной таблице список клиентов, отправить в область значений товары да посмотреть, сколько уникальных?
Но проблема в том, что по умолчанию сводная будет считать просто число значений. То есть это будет количество строк (=покупок), а не уникальных товаров.

Но если в вашей версии Excel есть Power Pivot, то достаточно просто поставить галочку "Добавить эти данные в модель данных" при вставке сводной (а если PP нет, то и флажка этого не будет) — и случится магия. В списке вычислений появится "Число разных элементов", это и будут уникальные. Как все это сделать — в очень коротком видео без звука.

Залезать в сам Power Pivot, связывать там данные — все это не нужно. Собственно, связывать нечего, в данном примере мы строим сводную на основе одной таблицы. Но тот факт, что она будет добавлена в модель данных этой книги Excel (в Power Pivot), активирует эту опцию.

Ну а в Google Таблицах в сводных и так есть COUNTUNIQUE среди операций🔥

Считаем уникальные значения: Power Query

Наконец, четвертый вариант — если у вас есть Power Query, а это куда больше версий, чем в случае с новыми функциями.

Загружаем данные в Power Query:
Данные — Получить данные — Из таблицы / диапазона

Удаляем все, кроме двух столбцов — по одному будем группировать (у нас это "Клиент"), по другому считать уникальные значения (у нас это "Товар")

Преобразование — Группировать по — выбираем нужные столбцы и операцию "Количество уникальных строк"

Закрыть и загрузить в — выбираем "Таблица" на новый или существующий лист. Готово!

Пробел: пересечение диапазонов

Немного экзотики. Пробел между диапазонами в Excel = пересечение этих диапазонов.

Например, такая формула:

=A1:A3 A2:C2 

Вернет значение из A2 — общей ячейки диапазонов A1:A3 и A2:C2.

Можно использовать и с именованными диапазонами — пример в видео.

=Магия_Excel_2 Март 

Кстати, чтобы быстро присвоить всем столбцам и/или строкам диапазона имена, можно использовать команду "Создать из выделенного" с вкладки "Формулы" (смотрите на видео) или нажать Ctrl + Shift + F3.

Горячие клавиши для быстрого перемещения и выделения в Excel🔥

Ctrl + PgDn/PgUp — следующий/предыдущий рабочий лист (а если открыто диалоговое окно, то перемещение между вкладками этого окна)

Ctrl + Backspace — возвращаемся к активной ячейке

Ctrl + A — выделяем всю текущую область (диапазон)

Shift + пробел — выделяем всю строку (если активна "умная таблица" — то выделяется столбец без заголовков — только данные — пределах таблицы, иначе — столбец в пределах всего листа)
Ctrl + пробел — выделяем весь столбец

Ctrl + стрелки — перемещаемся в конец диапазона (в направлении стрелки). Вместе с Shift — выделяем до конца диапазона.

Ctrl + End — перемещаемся в конец активной области на листе (в самые последние строку и столбец с данными)

P.S. Если делаете какое-то действие часто, на последнем шаге, когда кликаете на какую-то команду, остановитесь на секунду, наведите курсор на команду и посмотрите на подсказку — вполне вероятно, что там будет сочетание клавиш для нее.

Есть прекрасный сайт с производственным календарем для каждого года, начиная с 2014:
https://xmlcalendar.ru/?country=ru

Форматы там есть разные — например, XML (в этом формате у дат есть атрибут t с типом, и можно извлечь только праздничные (1) или только сокращенные рабочие дни (2) или рабочие СБ/ВС (тип 3)) или TXT, где просто список всех выходных и праздничных дней без типов.

Как получать данные? Можно разово вручную — скачать CSV и открыть в Excel или открыть ссылку с TXT-форматом, выделить все (Ctrl + A), скопировать (Ctrl + C) и вставить (Ctrl + V) в Excel.

Но так придется для каждого года вставлять данные вручную. Если вы хотите формулу, которая будет возвращать данные для текущего года, можно воспользоваться функциями ГОД / YEAR и СЕГОДНЯ / TODAY. Следующее сочетание будет возвращать номер текущего года:

ГОД(СЕГОДНЯ())

И его можно будет подставить в ссылку вместо значения конкретного года, так как ссылки меняются только на номер года:
https://xmlcalendar.ru/data/ru/2024/calendar.txt
https://xmlcalendar.ru/data/ru/2025/calendar.txt

Соответственно, ссылка на календарь текущего года в TXT-формате в формуле будет выглядеть так:

"https://xmlcalendar.ru/data/ru/" & ГОД(СЕГОДНЯ()) & "/calendar.txt"

А далее можно ее загрузить одной из функций. В Google Таблицах это IMPORTDATA:

=IMPORTDATA("https://xmlcalendar.ru/data/ru/" & YEAR(TODAY()) & "/calendar.txt")

В Excel (только на WIndows) это ВЕБСЛУЖБА / WEBSERVICE. Чтобы она не возвращала все даты "слипшимся" списком в одной ячейке, добавим функцию ТЕКСТРАЗД / TEXTSPLIT, чтобы разделить по переносу строку (символ с номером 10 — его нельзя напечатать, поэтому используем функцию СИМВОЛ / CHAR):

=ТЕКСТРАЗД(ВЕБСЛУЖБА("https://xmlcalendar.ru/data/ru/"&ГОД(СЕГОДНЯ()) &"/calendar.txt");;СИМВОЛ(10))

Но Excel не воспринимает как даты эти значения. Придется немного поколдовать: убрать непечатаемые символы через функцию ПЕЧСИМВ / CLEAN и сделать текст настоящей датой с помощью ДАТАЗНАЧ / DATEVALUE:

=ДАТАЗНАЧ(ПЕЧСИМВ(ТЕКСТРАЗД(ВЕБСЛУЖБА("https://xmlcalendar.ru/data/ru/"&ГОД(СЕГОДНЯ()) &"/calendar.txt");;СИМВОЛ(10))))

Поиск и окно "Найти и заменить" в Excel и Google Таблицах

Казалось бы, все просто — нажимай Ctrl+F да ищи, нажимай Ctrl+H да заменяй/удаляй.
Но есть приятные опции:
— Можно искать/заменять в диапазоне/на листе/на всех листах
— Можно искать/заменять с учетом регистра
— В Google Таблицах в окне "Найти и заменить" можно использовать регулярные выражения (смотрите примеры в статье: можно поменять формат дат на другие или сделать еще какую-нибудь магию), а в Excel символы подстановки
— В Google Таблицах можно искать по ссылкам (а в обоих редакторах — по формулам);
— В Excel можно менять формат ячеек — достаточно выбрать образец для поиска и образец для замены.

Отображаем все скрытые строки сразу

1 Выделяем первую строку

2 Выделяем все строки вниз: Ctrl + Shift + ↓

3 Нажимаем Ctrl + Shift + 9

Готово🔥