Магия Excel

Чередование строк в сводной: пользовательский стиль В сводных, как и в "обычных" (умных) таблицах можно включать чередование строк. Но не всегда стандартный вариант вам подойдет. Если нужен свой вариант (1 строка белая + 3 голубых, например) — создаем свой стиль сводной таблицы и там настраиваем размер и формат строк. Вкладка "Конструктор" на ленте (контекстная — появится только при активации сводной) —> Стили сводной таблицы —> Создать стиль сводной таблицы Design —> PivotTable Style Options —> New PivotTable Style И далее "Первая полоса строк", "Вторая полоса строк" (First Row Stripe, Second Row Stripe).

Ссылки с решеткой на динамические массивы Вашему вниманию три минуты видео про новые ссылки с решеткой. Так как в Excel 2021 / 365 появились динамические массивы (то есть формулу можно ввести в одну ячейку, а результат она выкатит размером более одной ячейки, да еще и размер этого результата может меняться в будущем) и соответствующие функции (например, УНИК / UNIQUE, СОРТ / SORT), то появился и новый тип ссылки — на ячейку с такой формулой. Ведь если функция УНИК выдает уникальные значения, то откуда мы знаем, какого размера результат будет в будущем? Ведь уникальных значений в источнике может стать меньше или больше. Поэтому мы не можем сослаться на результат стандартно, например как на A1:A10. Вдруг потом в 11 строке тоже будет результат выдачи? Поэтому на такие формулы нужно ссылаться с решеткой A1#. A1# — такая ссылка будет возвращать массив значений, который вычисляется формулой, находящейся в ячейке A1. А вот ссылка на видео про старые и новые формулы массива: https://t.me/lemur_excel/95

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

Мышка или к...лавиатура? Для перемещения в конец таблицы (диапазона) подойдет и то, и другое — выбирайте на ваш вкус: Ctrl + стрелка — перемещение в конец (до последней заполненной ячейки) в направлении стрелки; Двойной щелчок по границе ячейки — перемещение в соответствующем направлении (ловим курсор со стрелками во все стороны) Любое из этих действий с нажатой клавишей Shift — и получите не просто перемещение, а выделение ячеек 😺

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

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

Как проверить, есть ли в текстовом значении кириллица / латиница? Способ третий, одной формулой для пользователей нового Excel с функцией LAMBDA. Одной формулой проверяем каждый символ по порядку — и если это латиница, то извлекаем его и его порядковый номер в текстовой строке. В общем виде: =REDUCE(пустота как начальное значение ; формула для извлечения всех символов; LAMBDA для последовательной проверки каждого символа и склеивания всех латинских) Формула для извлечения всех символов — это как обсуждали в посте выше, сочетание ПСТР / MID и ПОСЛЕД / SEQUENCE. ПСТР(ячейка с текстом;ПОСЛЕД(1;ДЛСТР(ячейка с текстом));1) На выходе будет массив из отдельных символов. И далее мы лямбдой последовательно проверяем каждый символ из этого массива символов — ЕСЛИ буква (вторая переменная в LAMBDA, у нас так и называется — буква) попадает в диапазон латинских символов, то мы ее "забираем" в результат (первая переменная в LAMBDA, у нас называется итог), приклеивая к предыдущим собранным буквам, разделяя их переводом строки (функция СИМВОЛ / CHAR с кодом 10) или любым другим символом по вашему вкусу. Опционально можно добавить позицию буквы в текстовой строки (ее можно вычислить функцией НАЙТИ / FIND). Итоговая формула: =REDUCE("";ПСТР(ячейка;ПОСЛЕД(1;ДЛСТР(ячейка));1); LAMBDA(итог;буква; ЕСЛИ(И(КОДСИМВ(буква)>=65; КОДСИМВ(буква)<=122); итог&СИМВОЛ(10)&буква&"(позиция "&НАЙТИ(буква;ячейка)&")";итог))) Файл с формулой в отдельном сообщении выше.

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

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

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