Начал готовить курс по визуализации данных в Excel/Google Таблицах

Традиционно прикупил свежее по теме (две книги на переднем плане) и смотрю старое, тут не все, конечно, многое в электронке (тут подробнее про многие книги)

Прочитал уже первую из них «Графики, которые убеждают всех» Александра Богачева и могу рекомендовать тем, кто только входит в тему — кратко, наглядно, хорошие примеры (из настоящих отчетов/презентаций в то числе!). Есть одна опечатка (пример про цвета, а диаграмма ч/б), но это даже не капля дегтя, а молекулы.

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

Если читали что-то еще хорошее по теме, порекомендуйте, пожалуйста.

И снова в личные сообщения пришел вопрос: как удалить пробелы из выгрузки? Числа с пробелами (и поэтому это де-факто текст, который не обработать нормально), но "Найти и заменить" (Ctrl + H) не помогает. Проблема в том, что пробелы бывают разные 🤯 Короткий ответ: пробел лучше копировать, то есть брать именно тот пробел, что есть в выгрузке, который нужно удалить из чисел. А не вводить с клавиатуры.

В видео разбираемся более детально: как с помощью функции КОДСИМВ / CODE понять, что за символ вообще перед нами — в данном случае это не обычный пробел, а неразрывный, поэтому его не удалишь вводом обычного пробела с клавиатуры в окне "Найти и заменить". Его нужно либо копировать из данных и вставлять в окно "Найти и заменить", либо вводить с помощью кода — Alt+0160.

И делаем макрос, который позволит удалять ненужные символы одним нажатием кнопки или сочетанием клавиш Alt + цифра.

Код макроса можно сократить до такого:

Selection.Replace What:=" ", Replacement:=""

На месте пробела может быть другой символ, который вам нужно удалять в выделенном диапазоне.
Не забывайте: действие макросов отменить через Ctrl+Z нельзя!

Всю задачу разбираем в видео со звуком.

Типы данных

Может ли в одной ячейке быть несколько значений? Может, если это типы данных (data types).

Например, если вы введете в ячейки названия стран (на английском даже при русском интерфейсе Excel) и преобразуете их в тип данных "География" (Данные — Типы данных — География, Data — Data Types — Geography), то из таких ячеек можно будет извлекать данные формулой. Ссылаемся на ячейку с названием страны, ставим точку и видим варианты — что можно извлечь.

Либо просто нажмите на смарт-тэг(иконку), которая появляется в ячейке со страной, и выберите нужные данные, формула сформируется автоматом в соседнем столбце.

Уровней может быть несколько! Допустим, нам нужно население столиц:

=A1.[Capital/Major City].Population

А если бы мы хотели просуммировать население всех столиц стран из списка?

=СУММ(A1:A3.[Capital/Major City].Population)

Еще есть типы данных "акции", "валюты", в студенческой версии 365 еще и Wolfram (справочная система). А свои типы данных можно создавать с помощью Power Query.

Типы данных работают в 365 и Excel Online.

Магия двойных щелчков в Excel

Клац-клац 🐱Это действие много где может пригодиться, напоминает кот Лемур. В частности:

— Двойной щелчок по названию вкладки ленты инструментов скрывает и раскрывает ленту

— По кисточке "Формата по образцу". Если на нее щелкать один раз — то вы сможете применить формат выделенной ячейки один раз к другой ячейке / диапазону. А если дважды — то будете в режиме форматирования по образцу, пока не нажмете Esc. То есть сможете форматировать много отдельных ячеек и диапазонов, выделяя их 🔥.

— По названию (ярлыку) листа — можно его переименовать. По тексту в фигуре — отредактировать текст.

— По границе выделенной ячейки — перемещение в конец диапазона (например, если щелкнуть дважды на нижний край ячейки , то это будет аналог Ctrl + ↓, перемещение в конец диапазона вниз — до последней заполненной ячейки)

— В сводной таблице можно "провалиться" до исходных данных. Двойной клик по ячейке = создание отдельного листа с данными, которые сформировали то значение в сводной, по которому вы кликнули дважды.

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

— Наконец, двойной клик по самому-самому левому верхнему углу окна Excel = закрытие книги.

Друзья, залили на Kinescope наши видео. Изучайте на здоровье без регистрации, СМС, ВПН и других аббревиатур:

Автоматическая нумерация списков в Excel

Новые функции Excel: GROUPBY / ГРУПППО и PIVOTBY / СВОДПО. Агрегируем данные разными способами

Флажки в Excel: в ячейках (новые) и элементы управления (в старых версиях)

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

Интерфейс Excel: приемы и горячие клавиши для ускорения работы

Абсолютные и относительные ссылки в Excel. Стиль ссылок R1C1

Новая функция REGEXEXTRACT в Excel: извлекаем электронную почту, даты и другие фрагменты из текста

Выпадающие списки в Excel с автоматическим добавлением новых значений

Гильдия магов Excel

Еще несколько бесплатных видео вашему вниманию — уже более длинных 🤠

Потому что это вебинары "Гильдии магов Excel" в МИФе. Примерно 1 час по каждой из следующих тем:

Колдуем с текстом в Excel. Регистр, пробелы, переносы, форматы
Интерфейс и быстрый ввод данных. Лента инструментов, панель быстрого доступа
Формулы Excel для новичков. Разбираемся со ссылками, знаками и функциями.
Новые функции и инструменты 2021-2023. Что появилось в Excel 2021-2023

1 Заходим по ссылке:
https://www.mann-ivanov-ferber.ru/courses/guild-excel

2 Выбираем интересующую тему, нажимаем "Получить запись"

3 Вводим почту

4 Получаем ссылку и смотрим (без VPN)! Приятного просмотра.

Обязательно ли показывать структуру круговой диаграммой?

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

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

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

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

Ссылки на несколько листов в формулах Excel

Общий вид ссылки на несколько листов (это ссылка на листы от первого и до последнего по ярлыкам слева направо; если порядок листов в книге изменится, ссылка в формуле не поменяется):

'Первый лист:Последний лист'!Диапазон

Следующая формула суммирует числа из ячеек B2 на листах от "$ счет" и до "Счет в юанях" включительно:

=СУММ('$ счет:Счет в юанях'!B2)

В названиях листов можно использовать символ подстановки — звездочку. Если в книге много листов со словом "Расходы" в названии ("Расходы январь", "Расходы февраль", . . . ). Следующая формула позволит просуммировать ячейки A1 со всех этих листов:

=СУММ('Расходы*'!A1)

Правда, в отличие от ссылки с двоеточием, звездочка в формуле не сохранится - после ввода такой формулы ссылка на лист со звездочкой превратится в формулу с отдельными ссылками:

=СУММ('Расходы январь'!A1;'Расходы февраль'!A1;'Расходы март'!A1;...)

Получаем название листа формулой

Функция ЯЧЕЙКА / CELL может выдавать разную информацию: например, полное имя файла (книги) вместе с листом. Для этого ее первый и единственный обязательный аргумент должен быть равен "имяфайла" ("filename").

А дальше — дело техники — вытаскиваем только имя листа текстовыми функциями.
В новом Excel совсем удобно: ТЕКСТПОСЛЕ / TEXTAFTER вытащит все, что после квадратной скобки.

=ТЕКСТПОСЛЕ(ЯЧЕЙКА("имяфайла");"]")

В старых версиях Excel воспользуемся комбинацией функций:
НАЙТИ / FIND подскажет, на какой позиции находится скобка, ДЛСТР / LEN — сколько в имени вообще символов — исходя из этого поймем, какая длина названия листа — в нашем случае 5 символов — именно столько извлечем с конца текстовой строки с помощью функции ПРАВСИМВ / RIGHT.

=ПРАВСИМВ(ЯЧЕЙКА("имяфайла");ДЛСТР(...)-НАЙТИ("]";...))

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

И Excel такое дело помечает ошибкой с зеленым треугольником — "Число сохранено как текст".

Эти ошибки можно отключить. Отправляемся сюда:
Параметры Excel — Формулы — Правила проверки ошибок — отключаем "Числа, отформатированные как текст или с предшествующим апострофом".

Excel Options — Formulas — Error Checking Rules — Numbers Formatted As Text of Preceded By An Apostrophe

Если при создании сводной таблицы вы включите флажок "Добавить эти данные в модель данных" (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 среди операций🔥