Что почитать про сводные таблицы?
Конечно, книги Билла Джелена. У него выходит издание по каждой версии 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://www.tg-me.com/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 (стандартное сочетание для вставки гиперссылки), выбирайте папку (Искать в) и далее нужный файл.

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

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

Еще один вопрос с недавнего вебинара:
Был столбец с пунктами типа 10.1, 10.2, 10.3...10.10, 10.11, формат текстовый... Поменял формат на числовой, после чего часть цифр превратилась в пятизначные типа 45430, 44438, часть в даты.

Как отметили наши коллеги из МИФа, для оптимиста стакан наполовину полон, а для Excel — первое февраля.
И вот тут как раз такой случай, нам нужна нумерация такого вида, которую Excel воспринимает как даты.
Чтобы ввести это как текст, ставьте апостроф в начале, который превратит значение в текстовое (в ячейке он отображаться не будет — на скриншоте это третья строка):

'10.1

А если нужно уже имеющиеся данные превратить в порядковые пункты, можно использовать такую формулу:
=ДЕНЬ(ячейка) & "." & МЕСЯЦ(ячейка)
За счет того, что мы склеиваем несколько значений в одну текстовую строку (с помощью амперсанда), результат будет текстом.

Оператор неявного пересечения @

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

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

В новых версиях ссылка на диапазон — это сразу ссылка на диапазон, а не на пересечение с ним. То есть
=A1:A10 — это сразу весь диапазон A1:A10, формула вернет результатом размером в 10 строк.
А
=@A1:A10 — это пересечение с диапазоном A1:A10, то есть одно значение из той же строки, в которой находится формула.

Следующая формула в новых версиях будет возвращать диапазон из 12 значений — разницу между парами значений в столбцах C и B:
=C2:C13-B2:B13

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

P.S. Ну а в "умных"таблицах (чтобы создать такую, нажмите Ctrl + T) этот оператор используется в любой версии по умолчанию, но не с диапазоном, а с названием столбца в квадратных скобках. Например, [@План] — ссылка на столбец с заголовком "План" на эту же строку (ту, где формула).