Декартово произведение (все комбинации значений) в 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:
http://xmlcalendar.ru/?country=ru

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

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

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

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

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

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

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

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

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

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

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

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

=ДАТАЗНАЧ(ПЕЧСИМВ(ТЕКСТРАЗД(ВЕБСЛУЖБА("http://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

Готово🔥

Еще немного 🔥 клавиш. Что умеет F11?

Просто F11 — это вставка диаграммы. Но не простой, а диаграммы на отдельном листе (на котором не будет ячеек). Обычная диаграмма (внедренная) — это Alt + F1.

Alt + F11 — редактор VBA (макросов).

Ну а Shift + F11 — вставка нового листа.

Магия новых функций Excel: революция в формулах 🔥

Друзья, мы с Лемуром рады анонсировать новый мини-курс, посвященный новым же функциям Excel (и — по большей части — Google Таблиц).

Это функции, которые могут решать задачи, ради которых раньше пришлось бы писать макросы или создавать запросы в Power Query!

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

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

В честь запуска новой версии сайта и этого курса — зверская цена от Лемура — 890 рублей! 🔥
Никаких уловок и вечных скидок не будет: 18 ноября цена вырастет до 2300 и больше уже никогда не вернется к старой.

Внутри:
14 видео от 5 до 25 минут с качественным монтажом
Дополнительные текстовые материалы
Файлы со всеми примерами и формулами — исходные и готовые.

Покупать тут — и там же подробная программа и примеры:
https://shagabutdinov.ru/magic-excel

Отличия по строкам
Вы хотите быстро выделить цветом ячейки, в которых план отличается от факта (один столбец от другого — в общем случае)?

1 Выделяем столбцы (можно быстро быстро выделить их сочетанием Ctrl + Shift + стрелка вниз)
2 Ctrl + G —> Выделить (Special)
3 Отличия по строкам (Row differences)
4 Красим выделенные ячейки нужным цветом. Готово!

Смотрим на GIF (без звука)

Мини-курс "Магия новых функций Excel" 🔥