Магия Excel

Считаем уникальные значения: сводная таблица А почему бы не вывести в сводной таблице список клиентов, отправить в область значений товары да посмотреть, сколько уникальных? Но проблема в том, что по умолчанию сводная будет считать просто число значений. То есть это будет количество строк (=покупок), а не уникальных товаров. Но если в вашей версии Excel есть Power Pivot, то достаточно просто поставить галочку "Добавить эти данные в модель данных" при вставке сводной (а если PP нет, то и флажка этого не будет) — и случится магия. В списке вычислений появится "Число разных элементов", это и будут уникальные. Как все это сделать — в очень коротком видео без звука. Залезать в сам Power Pivot, связывать там данные — все это не нужно. Собственно, связывать нечего, в данном примере мы строим сводную на основе одной таблицы. Но тот факт, что она будет добавлена в модель данных этой книги Excel (в Power Pivot), активирует эту опцию. Ну а в Google Таблицах в сводных и так есть COUNTUNIQUE среди операций🔥

Считаем уникальные значения: старая школа Как быть в старой версии 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 2021 / 365. Список уникальных значений (или строк в общем случае) можно получить функцией УНИК / UNIQUE. А потом посчитать, сколько в списке уникальных значений — старой доброй функцией СЧЁТЗ / COUNTA. =СЧЁТЗ(УНИК(диапазон)) А если нужно количество уникальных товаров, купленных каждым клиентом? Тогда сначала фильтруем одноименной функцией товары только по нужному клиенту, а потом уже считаем, сколько в этом списке уникальных значений: =СЧЁТЗ(УНИК(ФИЛЬТР(диапазон с товарами;диапазон с условием=условие))) В следующих постах обсудим другие варианты решения задачи! P.S. А в Google Таблицах все можно сделать одной функцией COUNTUNIQUEIFS, считающей уникальные значения с одним или несколькими условиями.

Фокусировка ячейки — пустячок, а приятное новшество! Раньше такое делали через макросы и формулы условного форматирования. Или только макросами. Или надстройкой. В любом случае без макросов решение было невозможно. Теперь просто кнопка. Да еще и цвет можно выбирать🔥 Пока в бета-канале обновлений. Ждем, когда довезут до всех пользователей 365. Вид — Фокусировка ячейки View — Focus Cell

Декартово произведение (все комбинации значений) в Power Query Итак, если у вас не новая версия Excel с супергуперпупер функциями с красивыми названиями вроде ПОСТОЛБЦ, придется идти в Power Query: 1 Делаем списки таблицами (Ctrl + T) и добавляем в PQ (Данные — Из таблицы/диапазона). После добавления первого загружаем его только как подключение, чтобы список отдельно не выгружался на лист. Добавляем второй и далее уже остаемся в редакторе PQ 2 Добавляем столбец в одном из запросов (списков). Добавление столбца — Настраиваемый столбец. В нем просто ссылаемся на другой список (=Таблица1, например) 3 Теперь напротив каждого значения из текущего списка — таблица со всеми значениями из второго списка. Раскроем ее, нажав на кнопку со стрелками в заголовке — получим для каждого значения из первого списка столько строк, сколько есть во втором. 4 Останется объединить два столбца, выбрав в качестве разделителя пробел. 5 И выгрузить это дело в виде таблицы на лист Excel. При добавлении новых значений достаточно будет обновить запрос (Alt+F5) или правой кнопкой мыши — Обновить.

Есть сотый отзыв у Магии таблиц на Wildberries🥳 А на Озоне сотня преодолена уже давно. Итого сейчас у книги: Wildberries 102 отзыва 4.9 / 5🌟 Ozon 146 отзывов 4.9 / 5🌟 Кот Лемур напоминает: сейчас в продаже второе издание. В твердом переплете с дополнениями. 519 страниц. Увесистый подарок вашим друзьям и коллегам — героям ячейки и формулы. Из последних отзывов: Большая, толстая книга 519 страниц, надеюсь будет интересно. Побольше бы таких книг Книга замечательная, упаковка просто лучшая Лучше этой книги по Excel нет и быть не может) книга очень полезная даже продвинутым пользователям!

Декартово произведение (все комбинации значений) формулой С новыми функциями можно и формулой (а без них — через Power Query, о чем будет в отдельном посте). Сначала получаем первый список без пустых значений. Функция ПОСТОЛБЦ / TOCOL вернет его без пустых значений, то есть мы можем сослаться на весь столбец, но исключить пустые вторым аргументом функции (равным 1 для такого случая), чтобы предусмотреть появление новых значений в будущем. Второй список сделаем строкой с помощью ПОСТРОК / TOROW. Потом склеим их амперсандом (&), добавив пробел. Получим то, что вы видите на скриншоте справа в столбцах F-I (произведение, а точнее, конкатенация в данном случае, строки на столбец дает прямоугольный диапазон). Останется сделать его плоским списком — снова с помощью ПОСТОЛБЦ. =ПОСТОЛБЦ(ПОСТОЛБЦ(первый список;1)&" "&ПОСТРОК(второй список;1))

Случайность в квадрате: как визуализировать вероятность Если мы хотим наглядно показать, что что-то будет происходить примерно в 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 Сделать правило условного форматирования — для ячеек с единицей применять заливку какого-то цвета ишрифт того же цвета (чтобы скрыть единицы). Готово! Для большей красоты можно добавить границу вокруг всего диапазона, убрать сетку на листе — на что хватит фантазии. Весь процесс в коротком видео без звука.

Если при создании сводной таблицы вы включите флажок "Добавить эти данные в модель данных" (Add this data to Data Model), то впоследствии можно будет превратить сводную таблицу в формулы. Что это за формулы такие? Формулы куба. Они могут напомнить вам функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA), но она получает данные из существующей сводной. А функции куба заменяют собой сводную! И проще всего получить их в готовом виде, чтобы изучить, преобразовав существующую сводную таблицу. Строим сводную таблицу — включаем флажок "Добавить эти данные в модель данных" — переходим на вкладку "Анализ сводной таблицы" и там выбираем "Средства OLAP" — Преобразовать в формулы" (OLAP tools — Convert to Formulas). Что изменится? Каждая ячейка сводной станет независимой формулой. Больше не будет единого объекта — сводной таблицы — будут отдельные формулы. Вы сможете переупорядочить вашу сводную как вам захочется (см пример на видео без звука). Какие это функции? КУБЗНАЧЕНИЕ / CUBEVALUE — это как область значений сводной, в ней задается подключение (к модели данных, первый аргумент = "ThisWorkbookDataModel") и ссылки на заголовки столбца, названия элементов (в нашем примере категория ресторана из области строк), может быть ссылка и на срез. Заголовки в сводной (и заголовок области значений в духе "Сумма по полю ...", и названия элементов в строках/столбцах) будут заданы другой функцией — КУБЭЛЕМЕНТ / CUBEMEMBER. Еще и другие функции куборв, например, КУБМНОЖ / CUBESET (возвращает список всех значений из столбца) и КУБПОРЭЛЕМЕНТ / CUBERANKEDMEMBER, которая из ячейки с функцией КУБМНОЖ может извлечь значение по его порядковому номеру.

Если вам мешают жить зеленые треугольники, значит... у вас есть текст, который очень похож на числа. Такой может быть из внешнего источника или вы сами специально ввели цифры с апострофом, потому что это не число, а номер счета, например. Или другой текст, иногда начинающийся и с нуля, что невозможно для "настоящих" чисел. И Excel такое дело помечает ошибкой с зеленым треугольником — "Число сохранено как текст". Эти ошибки можно отключить. Отправляемся сюда: Параметры Excel — Формулы — Правила проверки ошибок — отключаем "Числа, отформатированные как текст или с предшествующим апострофом". Excel Options — Formulas — Error Checking Rules — Numbers Formatted As Text of Preceded By An Apostrophe

Получаем название листа формулой Функция ЯЧЕЙКА / CELL может выдавать разную информацию: например, полное имя файла (книги) вместе с листом. Для этого ее первый и единственный обязательный аргумент должен быть равен "имяфайла" ("filename"). А дальше — дело техники — вытаскиваем только имя листа текстовыми функциями. В новом Excel совсем удобно: ТЕКСТПОСЛЕ / TEXTAFTER вытащит все, что после квадратной скобки. =ТЕКСТПОСЛЕ(ЯЧЕЙКА("имяфайла");"]") В старых версиях Excel воспользуемся комбинацией функций: НАЙТИ / FIND подскажет, на какой позиции находится скобка, ДЛСТР / LEN — сколько в имени вообще символов — исходя из этого поймем, какая длина названия листа — в нашем случае 5 символов — именно столько извлечем с конца текстовой строки с помощью функции ПРАВСИМВ / RIGHT. =ПРАВСИМВ(ЯЧЕЙКА("имяфайла");ДЛСТР(...)-НАЙТИ("]";...))