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

Правда, на встречах слушатели этих слайдов не увидят. Вот еще — время тратить на презентации на уроках :)
Все время (3 по 2 часа) проведем в Excel (ну и малость в Google Таблицах), а слайды — это как мини-методичка для участников, чтобы потом освежить в памяти знания.

Еще будут домашки, их разбор (и подарки чемпионам), файлы-примеры до и после, ответы на вопросы.

Приходите, встречи будут 14, 20 и 23 июня (а если есть вопросы по формату обучения — спрашивайте в ЛС или комментариях):
https://www.mann-ivanov-ferber.ru/courses/practicum-excel/

Серая тема для черно-белой печати диаграмм и других объектов в Excel

При подготовке книги к печати столкнулись с типовой проблемой: когда вы печатаете цветные диаграммы, все может сливаться, если печать не цветная.
Слева сверху вы видите диаграмму, как она выглядела в Excel изначально (и как будет выглядеть этот фрагмент в электронной — цветной — книге), справа — то, что получается при ч/б печати. Как видите, совсем грустно. Гистограмма с накоплением выглядит как столбики одного цвета, словно и нет у нас двух товарных категорий.

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

Итак, если вы планируете печатать в ч/б ваш отчет:
Вкладка "Разметка страницы" — группа "Темы" — Цвета — Серая
Page Layout — Themes — Colors — Grayscale

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

="Средние продажи: "&СРЗНАЧ(C2:C13)

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

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

Выход — использовать функцию ТЕКСТ / TEXT. Она может любое значение (в том числе число, взятое из ячейки или полученное вычислением) отформатировать по заданному числовому формату и возвращает результат в виде текста.

Синтаксис:

=ТЕКСТ(число; формат).

Формат указывается как в пользовательских форматах (подробнее о них см видео).

Так что в нашем случае, если мы хотим, чтобы все средние значения были с одним знаком после запятой, понадобится следующая формула:

="Средние продажи: "&ТЕКСТ(СРЗНАЧ(B2:B13);"0,0")

У вас есть список в несколько столбцов, а вам нужно сделать его одномерным?

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

Тогда используем функцию TOCOL / ПОСТОЛБЦ (увы, недоступную в старых версиях Excel).
Чтобы сделать список без повторов (то есть если человек выступает в разное время или разные дни, мы все равно упоминаем его один раз) и отсортировать, можно добавить функции УНИК / UNIQUE (только уникальные значения) и СОРТ / SORT (сортировка).

Пользуетесь окном поиска в Excel?

Если нет, уберите его в параметрах:
Файл — Параметры — Общие — Свернуть поле Поиска

File — Options — General — Collapse the Microsoft Search Box

В любом случае справа у вас останется иконка лупы для раскрытия этого поля. Но зато оно не будет все время занимать часть панели наверху.

Схватка двух ёкодзун: сравнение Excel и Google Spreadsheets.

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

По некоторым темам и функциями есть ссылки на дополнительные материалы (видео, посты).

https://docs.google.com/spreadsheets/d/10jhUBPLhmtx-km5sdITumlmkgIn8DKqsdxyULChVaq8

Фильтр по выделенному

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

Параметры Excel — Настроить панель быстрого доступа (панель инструментов — все команды или команды не на ленте — Автофильтр — Добавить

Customize Quick Access Toolbar — Commands Not in The Ribbon — AutoFilter — Add

Теперь ставим фильтр в диапазоне (если его еще не было), выделяем ячейку, по которой нужно отфильтровать, и нажимаем на добавленную на панель быстрого доступа кнопку!
А еще кот Лемур напоминает: команды на панели можно вызывать сочетанием Alt (или косой черты или F10) и цифры (соответствующей расположению нужной команды по порядку). Так что у нас с вами теперь есть сочетание клавиш для фильтрации по выбранному значению!

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

Друзья, привет! Обновляем подборку самых сочных постов канала, посмотрите, не пропустили ли чего полезного:

Ctrl + Backspace - очень удобное сочетание клавиш для возвращения к активной ячейке

Макрос для сравнения двух файлов (книг Excel)

Удаляем строки с пустыми ячейками в одном из столбцов

Макрос: создаем оглавление в книге

Анализируем сезонность в сводной таблице

Поиск по двум критериям

План-факт через комбинированную диаграмму

Видеоурок: "старые" и новые формулы массивов

Функция СУММЕСЛИМН / SUMIFS: сумма по условиям

Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический)

Ищем данные в разных таблицах с помощью ВПР / VLOOKUP и ДВССЫЛ / INDIRECT

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

У разных подразделений разная шкала оценки — например, где-то третий разряд присваивается с 60 лет, а где-то с 50.
Как быть?
Если бы задача была с одной таблицей, то все просто решается функцией ВПР / VLOOKUP: ищем возраст сотрудника в таблице, получаем разряд из второго столбца. Последний (четвертый аргумент) ВПР не трогаем, т.к. по умолчанию у этой функции интервальный просмотр, то есть поиск ближайшего наименьшего числа, а именно это нам и нужно в данном случае.

=ВПР(возраст сотрудника; таблица с возрастами и разрядами; 2)

Но у нас таблица не одна! Во втором аргументе ВПР могут быть разные таблицы, в зависимости от должности.
Поступим так:
— превратим таблицы для каждого отдела в "умные" таблицы (Форматировать как таблицу / Format as Table или Ctrl + T или Ctrl + L)
— назовем каждую по имени отдела
— теперь можно ссылаться на таблицы по имени. Нам надо получить название отдела по сотруднику (найти должность в списке "должность-отдел" и подтянуть отдел) — это и будет название нужной таблицы. Чтобы название таблицы из текста стало ссылкой, мы засовываем всю конструкцию в ДВССЫЛ / INDIRECT — функцию, превращающую текст в ссылку.

В общем виде будет так:

=ВПР(возраст сотрудника; ДВССЫЛ(формула для определения названия нужной таблицы); 2)

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

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

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

Увы, в стандартном наборе вычислений (из 11 операций) в сводных подсчета уникальных значений нет. Но если добавить наши данные в модель данных (Power Pivot) при создании сводной, то такая возможность появится! В сам Power Pivot можно даже не заходить, и не обязательно собственно строить модель данных, добавляя туда еще какие-то таблицы. Если вам нужна только эта возможность — просто включите флажок "Добавить эти данные в модель данных" (Add this data to the Data Model) при вставке сводной. И далее в параметрах поля значений выбирайте операцию "Число разных элементов" (Distinct Count).

P.S. А в Google Таблицах функция для подсчета уникальных в сводных есть — COUNTUNIQUE (как и обычная функция рабочего листа, не в сводных, с таким именем).

Быстрая фильтрация в сводной таблице

Если вам нужно быстро исключить некоторые значения из сводной: выделите то, что нужно убрать (в строках или столбцах отчета сводной таблицы) и нажмите Ctrl + - (минус).

Данные будут отфильтрованы, те значения, что вы выделяли, будут исключены в фильтре.

P.S. Если тема сводных для вас актуальна и вы любите запрыгивать на подножку уезжающего поезда, то завтра начинается практикум по сводным таблицам. Три встречи по 2 часа, возможность выиграть призы за лучшую домашку, десятки слайдов, файлы с примерами в исходном и готовом состоянии, кот Лемур и пес Штрудель в качестве соавторов ДЗ — вот такой комплект!

Ловите промокод, с ним совокупная скидка на практикум составит 35%: Lemur_35
Записываться здесь:
https://www.mann-ivanov-ferber.ru/courses/practicum-excel/

Что почитать про сводные таблицы?
Конечно, книги Билла Джелена. У него выходит издание по каждой версии Excel.

Шикарная книга, без альтернатив для глубокого погружения в сводные таблицы. Тут и все мыслимые нюансы "классических сводных" (построенных по данным в Excel), и сводные диаграммы, и Power Query, и работа со сводными через макросы, и функция GETPIVOTDATA, и сводные по модели данных (Power Pivot), и функции кубов (превращение сводной, основанной на модели данных, в формулы).

Слева обновление по последней версии 365. То есть это и для тех, у кого "коробочная" версия Excel 2021, и для подписчиков 365, и для тех, кто использует Excel Online в браузере, где сводные наконец появились (последние два пункта будут вместе, скорее всего).

Есть пара новых техник в главе с лайфхаками, но главное — новые главы. Про сводные таблицы в Excel Online, про построение "сводных таблиц" без собственно сводных - с помощью новых формул с динамическими массивами (которые как раз появились в 365 и 2021) или внутри Power Query. Также есть глава про "анпивот" — unpivoting в Power Query.

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

В книге по 2019 версии, выходившей в другом издательстве, скриншоты и файлы на русском, команды в тексте только на русском.

Ссылка на полный обзор книг по Excel:
https://teletype.in/@renat_shagabutdinov/excellent_books

Итак, вопрос от подписчика: как разделить целое число на N неравных частей в соотношении, определённом случайным образом.

Генерируем случайные числа, потом делим каждое из них на их сумму (чтобы получить в сумме не произвольное число, а ровно единицу, 100%, это будут случайные доли, на которые мы потом умножим наше число).

Генерировать случайное число в любой версии Excel можно с помощью функции СЛЧИС / RAND, а целый массив чисел с помощью новой функции СЛМАССИВ / RANDBETWEEN.

Далее просто делим каждое на сумму всех чисел. В примере на скриншоте обратите внимание на ссылку с решеткой: это новый тип ссылок в Excel 2021 / 365. Так как теперь есть функции вроде СЛМАССИВ и других, которые, будучи в обычной ячейке, выдают массив на несколько, то есть и возможность ссылаться на них. A2# — это ссылка на массив, который возвращает формула, находящаяся в A2.
В данной задаче это не обязательно, конечно, можно решать ее через обычную СЛЧИС и ссылки на ячейки и диапазоны как раньше.

Когда вы получили «случайные» доли, остается умножить число на них.

Как проверить, есть ли в текстовом значении кириллица / латиница?

Способ первый, простой и визуальный — поменять шрифт на какой-либо, не поддерживающий оба языка. Пример на скриншоте — русские "с" и "е" выделяются визуально на фоне остальных.

Как проверить, есть ли в текстовом значении кириллица / латиница?

Способ второй, формульный.

С помощью функции КОДСИМВ / CODE определим коды каждого символа в нашем тексте. По коду можно однозначно определить, латиница это (коды 65-90 и 97-122) или кириллица (192-255).
В старых версиях Excel можно протянуть такую формулу, которая в каждом столбце (функция СТОЛБЕЦ / COLUMN дает номер столбца, в котором находится формула) будет извлекать (функция ПСТР / MID) код (функция КОДСИМВ / CODE) каждого очередного символа из ячейки. Когда символы закончатся, вместо ошибки функция ЕСЛИОШИБКА / IFERROR выдаст пустоту.
=ЕСЛИОШИБКА(КОДСИМВ(ПСТР(ячейка с текстом;СТОЛБЕЦ()-1;1));"")

В новых версиях (2021, 365) можно одной формулой извлечь все символы. Функция ПОСЛЕД / SEQUENCE выдаст последовательность символов от единицы до числа символов в тексте (определяется функцией ДЛСТР / LEN):
=КОДСИМВ(ПСТР(ячейка с текстом;ПОСЛЕД(1;ДЛСТР(ячейка с текстом));1))

Ну а далее можно проверять (например, через функции ЕСЛИ / IF и И / AND), к какому диапазону относится символ.
=ЕСЛИ(И(ячейка с символом>=192;ячейка с символом<=255);"кириллица"; "латиница/символ")

Файл с примером в отдельном сообщении выше.

Как проверить, есть ли в текстовом значении кириллица / латиница?

Способ третий, одной формулой для пользователей нового Excel с функцией LAMBDA.

Одной формулой проверяем каждый символ по порядку — и если это латиница, то извлекаем его и его порядковый номер в текстовой строке.
В общем виде:

=REDUCE(пустота как начальное значение ; формула для извлечения всех символов; LAMBDA для последовательной проверки каждого символа и склеивания всех латинских)

Формула для извлечения всех символов — это как обсуждали в посте выше, сочетание ПСТР / MID и ПОСЛЕД / SEQUENCE.

ПСТР(ячейка с текстом;ПОСЛЕД(1;ДЛСТР(ячейка с текстом));1)

На выходе будет массив из отдельных символов.

И далее мы лямбдой последовательно проверяем каждый символ из этого массива символов — ЕСЛИ буква (вторая переменная в LAMBDA, у нас так и называется — буква) попадает в диапазон латинских символов, то мы ее "забираем" в результат (первая переменная в LAMBDA, у нас называется итог), приклеивая к предыдущим собранным буквам, разделяя их переводом строки (функция СИМВОЛ / CHAR с кодом 10) или любым другим символом по вашему вкусу.
Опционально можно добавить позицию буквы в текстовой строки (ее можно вычислить функцией НАЙТИ / FIND).

Итоговая формула:

=REDUCE("";ПСТР(ячейка;ПОСЛЕД(1;ДЛСТР(ячейка));1);
LAMBDA(итог;буква;
ЕСЛИ(И(КОДСИМВ(буква)>=65; КОДСИМВ(буква)<=122); итог&СИМВОЛ(10)&буква&"(позиция "&НАЙТИ(буква;ячейка)&")";итог)))

Файл с формулой в отдельном сообщении выше.

Оберни колонки: новая (относительно) функция WRAPCOLS

Итак, нам с вами нужно превратить одномерный массив — например, столбец, в котором данные цикличные (время начала мероприятия + N строк с выступающими в нашем примере) — в двумерный, разместив каждый повторяющийся "блок" в отдельный столбец.

Засунем диапазон в WRAPCOLS, вторым аргументом укажем, сколько ячеек отправлять в каждый столбец. Необязательный третий аргумент — как возвращать пустые ячейки из исходника, если они там будут. Иначе будет выводиться ошибка #N/A (#Н/Д).

=WRAPCOLS(A1:A;N; [чем заменить пустые])

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

=WRAPCOLS(FILTER(A1:A;A1:A<>"");N)

P.S. Раз есть функция WRAPCOLS — значит — это кому-нибудь нужно? есть и WRAPROWS.
P.P.S. В Excel (365) при русскоязычном интерфейсе — СВЕРНСТОЛБЦ и СВЕРНСТРОК.