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

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

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

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

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

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

А вот ссылка на видео про старые и новые формулы массива:
https://t.iss.one/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) этот оператор используется в любой версии по умолчанию, но не с диапазоном, а с названием столбца в квадратных скобках. Например, [@План] — ссылка на столбец с заголовком "План" на эту же строку (ту, где формула).

Up Up and Array!: Dynamic Array Formulas for Excel 365 and Beyond

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

Речь про функции SORT, SORTBY, FILTER, UNIQUE, SEQUENCE и про функции для работы с массивами — RANDARRAY, TOCOL, TOROW, WRAPCOLS, WRAPROWS, VSTACK, HSTACK, CHOOSECOLS, CHOOSEROWS, EXPAND, TAKE и DROP, а также про текстовые функции TEXTSPLIT, TEXTBEFORE, TEXTAFTER.
Тут максимум деталей и нюансов (для новичков) и много примеров применения новых функций, в том числе их комбинаций (это будет интересно и новичкам, и продолжающим) — насколько же проще стало решать многие задачи!
Есть файлы с примерами.

Ну а полный обзор табличных книг вместе с этой новинкой — по постоянному адресу:
https://teletype.in/@renat_shagabutdinov/excellent_books

Как заполнить всю ячейку каким-нибудь символом между двух текстовых фрагментов (например, названием главы и номером страницы, как в примере)?

Общая логика формулы такая:
Берем первый кусочек, приклеиваем (&) к нему нужный символ, который повторяем много раз с помощью функции ПОВТОР / REPT, и потом справа приклеиваем (&) второй кусочек.
Сколько раз повторять символ (в нашем случае дефис)?
Берем какое-нибудь число, которое будет больше, чем наши текстовые фрагменты, и из него вычитаем число символов (ДЛСТР / LEN) в наших склеиваемых текстовых фрагментах. Таким образом получится, что число символов-заполнителей везде будет разное, но общее число символов одинаковое.

P.S. Магия сработает только с моноширинным шрифтом — например, с Courier New.

Тем временем книга получила статус "Хит" на Озоне и "Бестселлер", "Хит продаж" на Литресе!

А вот и первые отзывы (пока на Озоне):
отличная подача материала. намного дешевле курса!

Большое количество полезных фишек для ускорения работы

Отлична книга, великолепная подача, все «разжевано». Можно читать как подряд, так и местами, которые нужны. Пока это самое понятное пособие по Excel, которое я читала

Собрали для вас с Лемуром ссылки на основные магазины, где можно купить:

На сайте издательства
Book24
Лабиринт
Озон
Литрес (электрическая)
Wildberries

Если уже прочитали, напишите отзыв тоже, будем признательны 😺

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

Формула будет выглядеть так:

=СОВПАД(первая ячейка диапазона; ПРОПНАЧ(первая ячейка диапазона))

Функция СОВПАД / EXACT проверяет совпадение с учетом регистра. А ПРОПНАЧ / PROPER меняет регистр текста — делает первые буквы каждого слова заглавными.
Таким образом, мы проверяем, совпадает ли текущее значение ячейки с ним же, но в "правильном" регистре.
И если не совпадает, то функция СОВПАД возвращает ЛОЖЬ / FALSE — и проверка данных ругается.

Если дата в ячейках записана как текстовое значение вида ДДММГГГГ, без точек/дефисов/других разделителей, можно превратить такой текст в настоящую дату формулой:

=ДАТА( ПРАВСИМВ( ячейка с датой; 4) ; ПСТР (ячейка; 3; 2) ; ЛЕВСИМВ (ячейка; 2) )

Функция ДАТА / DATE возвращает дату, заданную тремя параметрами — годом, месяцем и днем.
Ее аргументы мы получаем текстовыми функциями:
Год — извлекая первые цифры цифры с помощью ПРАВСИМВ / RIGHT (она возвращает первые N символов из текстовой строки)
Месяц — извлекая два символа, начиная с третьего, с помощью ПСТР / MID.
День — последние две цифры с помощью функции ЛЕВСИМВ / RIGHT.

Связанные выпадающие списки

Если вы хотите, чтобы список возможных значений в одном списке определялся на основе другого — можно использовать функцию ФИЛЬТР / FILTER для формирования списка — например, товаров только одной, выбранной категории.

И далее можно на эту формулу ссылаться из проверки данных (Data Validation) с помощью нового типа ссылок с решетками, о которых мы рассказывали здесь:
https://t.iss.one/lemur_excel/165

Подробно — в коротком видео со звуком. Отдельным сообщением прикрепляем файл с формулой выше.

P.S. Радость эта будет доступна, увы, только в новом Excel.

Друзья, если вы работаете не только в Excel, но и в Google Таблицах, то подписывайтесь на одноименный, дружественный и вообще лучший канал по теме. К которому в комплекте идет чат, населенный настоящими профессионалами, готовыми помочь (при понятной формулировке задачи и наличии примера 😺)
https://t.iss.one/google_sheets
@google_spreadsheets_chat

Вот на днях выложил там большую статью про функцию IMPORTRANGE, которая позволяет загружать данные из одной таблицы в другую:
Главная статья импорта

Показать сразу несколько листов

Относительно новая штука (появилась в конце 2020 года для подписчиков Microsoft 365) — можно показать сразу несколько скрытых листов.
Когда вы щелкаете правой кнопкой по любому ярлыку видимого листа и выбираете "Показать..." (Unhide...) — появляется этот список.
Теперь в нем можно выбрать несколько отдельных листов (с зажатой Ctrl) либо список идущих подряд листов (для этого зажмите Shift и щелкните на лист, до которого включительно вам нужно выделить листы в списке).

Форматирование устаревших значений

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

Для этого достаточно включить флажок "Формат устаревших значений" (Format Stale Values) на вкладке "Формулы".
Работать это будет, конечно, при ручном пересчете формул.