Ищем данные в разных таблицах с помощью ВПР / 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) при русскоязычном интерфейсе — СВЕРНСТОЛБЦ и СВЕРНСТРОК.

Удаление источника данных сводной

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

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

А если вдруг захочется посмотреть на исходные данные, можно просто щелкнуть дважды на общий итог. Ведь двойной щелчок = создание листа со всеми строками, которые "стоят" за тем числом, на которое вы щелкнули в сводной. А общий итог складывается из всех строк исходных данных.
Так что если вы рассматривали вариант удаления источника, чтобы скрыть его от других пользователей и оставить им только сводную, увы, это не сработает 😺

Мышка или к...лавиатура?

Для перемещения в конец таблицы (диапазона) подойдет и то, и другое — выбирайте на ваш вкус:

Ctrl + стрелка — перемещение в конец (до последней заполненной ячейки) в направлении стрелки;
Двойной щелчок по границе ячейки — перемещение в соответствующем направлении (ловим курсор со стрелками во все стороны)

Любое из этих действий с нажатой клавишей Shift — и получите не просто перемещение, а выделение ячеек 😺

Добавляем гистограммы в сводной таблице отдельным столбцом

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

В двух словах: мы добавляем еще один столбец с теми же суммами, применяем к нему условное форматирование (это могут быть не только гистограммы, но и значки / цветовая шкала) и потом в настройках правила условного форматирования включаем опцию "Показывать только столбец" (Show Bar Only).

Ссылки с решеткой на динамические массивы

Вашему вниманию три минуты видео про новые ссылки с решеткой.
Так как в Excel 2021 / 365 появились динамические массивы (то есть формулу можно ввести в одну ячейку, а результат она выкатит размером более одной ячейки, да еще и размер этого результата может меняться в будущем) и соответствующие функции (например, УНИК / UNIQUE, СОРТ / SORT), то появился и новый тип ссылки — на ячейку с такой формулой.

Ведь если функция УНИК выдает уникальные значения, то откуда мы знаем, какого размера результат будет в будущем? Ведь уникальных значений в источнике может стать меньше или больше.
Поэтому мы не можем сослаться на результат стандартно, например как на A1:A10. Вдруг потом в 11 строке тоже будет результат выдачи?
Поэтому на такие формулы нужно ссылаться с решеткой A1#.
A1# — такая ссылка будет возвращать массив значений, который вычисляется формулой, находящейся в ячейке A1.

А вот ссылка на видео про старые и новые формулы массива:
https://t.me/lemur_excel/95

Чередование строк в сводной: пользовательский стиль

В сводных, как и в "обычных" (умных) таблицах можно включать чередование строк.
Но не всегда стандартный вариант вам подойдет.
Если нужен свой вариант (1 строка белая + 3 голубых, например) — создаем свой стиль сводной таблицы и там настраиваем размер и формат строк.

Вкладка "Конструктор" на ленте (контекстная — появится только при активации сводной) —> Стили сводной таблицы —> Создать стиль сводной таблицы
Design —> PivotTable Style Options —> New PivotTable Style

И далее "Первая полоса строк", "Вторая полоса строк" (First Row Stripe, Second Row Stripe).

Как найти все формулы в диапазоне?

Вариант первый, "на один раз":
"Найти и выделить" —> Формулы
Find and Select —> Formulas

Все ячейки с формулами в активном диапазона будут выделены.
А если активна одна ячейка — то все формулы на листе.

Как найти все формулы в диапазоне?

Вариант второй, динамический — через условное форматирование и функцию ЕФОРМУЛА / ISFORMULA, которая выдает ИСТИНА / TRUE, если в ячейке есть формула.
Условное форматирование — Создать правило — Использовать формулу...
Conditional Formatting — New Rule — Use a formula...

И вводим формулу:

=ЕФОРМУЛА(первая ячейка форматируемого диапазона)

И далее выбираем форматирование: как мы хотим оформлять ячейки, содержащие формулы.

Проводили недавно вебинар с Лемуром, и один из вопросов слушателей был такой: как фильтровать данные в сводной (или просто в диапазоне / таблице) по последним N дням месяца/квартала/года? Например, по последним трем?
Временная шкала в сводной таблице такого не умеет.

Здесь можно добавить к данным расчетный столбец, где формула будет выдавать одно из двух - последние три дня или нет. И потом фильтровать на основе этого столбца. Справа здесь скриншот с примером, а вот один из вариантов такой формулы:
=ЕСЛИ(И(A1>=КОНМЕСЯЦА(A1;0)-2;A1<=КОНМЕСЯЦА(A1;0));"Посл три дня месяца";"Другие дни")

Функция КОНМЕСЯЦА / EOMONTH выдает последнюю дату месяца. Первый аргумент — дата, а второй — отступ от нее по месяцам. То есть КОНМЕСЯЦА(A1; -1) вернет последнюю дату предыдущего месяца относительно даты в A1.
А с нулем это будет последняя дата этого месяца, того, к которому относится дата в первом аргументе — без сдвига.
И в формуле мы проверяем с помощью функции И / AND (проверяет одновременное выполнение всех условий), попадает ли наша дата в промежуток между датой за три дня до конца месяца и собственно концом месяца.

Друзья, наша с Лемуром книга тем временем доехала до всех основных книжных магазинов!

Внутри — все от основ и интерфейса до новых функций, появившихся в 2021-2023 годах (в том числе LAMBDA и ее вспомогательных функций) и визуализации данных. Все функции и команды на двух языках, есть информация о том, какие вещи доступны в Google Таблицах.

Если вам нужны полкило скриншотов из Excel и Google Таблиц (и только одна фотография кота) и 45 миллионов байт с примерами, заказывайте:
На сайте издательства (там же электрическая книга)
Book24
Лабиринт
Озон
Литрес (электрическая)

Хотите добавить в ячейку ссылку на файл (не обязательно книгу Excel, можно и на Word, и на другие файлы)?

Нажимайте Ctrl + K (стандартное сочетание для вставки гиперссылки), выбирайте папку (Искать в) и далее нужный файл.

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

Нажимаем ОК и готово! В ячейке появится ссылка, при щелчке на которую будет открываться этот файл.