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

Изменить вычисление можно разными способами.
1 Самый простой — правая кнопка по любому значению — Итоги по — выбрать вариант подсчета (тут не все варианты, но основные есть — среднее, максимум и минимум, количество и сумма).

2 Двойной щелчок по заголовку поля ("Сумма по полю...")

3 Щелчок по полю в редакторе сводной таблицы — "Параметры полей значений"

Видите неактивную опцию "Число разных элементов"? Это подсчет уникальных значений. Он будет доступен, если сводная будет построена на основе модели данных. Так можно сделать, даже если исходные данные — всего одна таблица. При построении сводной включите флажок "Добавить эти данные в модель данных"

Удаляем пустые строки

Выделяем диапазон, в котором нужно удалить пустые ячейки (в видео - все данные на листе с помощью Ctrl+Shift+End).

Для этого нужен инструмент "Найти и выделить" (на ленте на вкладке "Главная", Home — Go To) — выбираем там "Выделить группу ячеек" и в появившемся диалоговом окне — "Пустые ячейки" (Blanks).
Еще можно нажать F5 или Ctrl + G и в появившемся окне нажать "Выделить".

После этого остается нажать Ctrl + - (Ctrl и минус) — это удаление ячеек/строк/столбцов. И выбрать "строку".

P.S. Если у вас пустые ячейки только в одном столбце, и нужно удалить строки с такими ячейками — выделите один столбец, а не всю таблицу, а далее алгоритм такой же.

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

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

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

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

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

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

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

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

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

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

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

"Протягивание формул" (двойной щелчок и сочетания клавиш)

Объединяем умные таблицы в одну: формулы и Power Query

Сравнение списков (Видео)

Нарастающий итог: закрепляем только первую ячейку в диапазоне

Если вы хотите считать в отдельном столбце накопительный итог, никто не помешает вам закрепить только начало диапазона, но не его конец.

1. Ссылаемся на первую ячейку, эфчетырим ее (то есть нажимаем F4, чтобы сделать ссылку абсолютной, "закрепить")

2. Вводим двоеточие и ту же самую ячейку, но уже оставляем относительной. Получается диапазон с началом и концов в одной ячейке, но конец не закреплен - так что при протягивании/копировании формулы будет меняться.
=СУММ($B$2:B2)

3. Протягиваем и получаем диапазон с началом в одной и той же ячейке и концом в текущей строке.

Хотите видеть какую-то книгу Excel всегда наверху в списке последних файлов?

На стартовом экране (Backstage) наводите курсор на нужный файл — справа появится кнопка (которая выглядит как... кнопка) "Закрепить" (Pin). Нажимайте и книга будет всегда наверху.
Точно так же можно открепить обратно.

Хотите, чтобы стартовый экран не показывался при включении Excel (или другого приложения Office)?
Заходите в Параметры — Общие — отключайте "Показывать начальный экран при запуске этого приложения"

Функция ТЕКСТ / TEXT: превращаем число в текстовое значение в заданном числовом формате

Эта чудо-функция возвращает текстовую строку со значением (первый аргумент), оформленным в заданном числовом формате (второй аргумент).

Для чего нужна?
Допустим, вы хотите "склеить" в одну текстовую строку текст и число.
Чтобы получить в таблице надпись вида "По состоянию на: 30.06.23" или "Сумма продаж: 20 500". То есть текст из фиксированной части и какого-то вычисления/функции, как-то суммы чисел или текущей даты.

Проблема в том, что если сделать это "в лоб" без функции ТЕКСТ / TEXT, форматирование потеряется. Число будет без разделителей разрядов, со всеми знаками после запятой; дата будет в виде числа ("По состоянию на: 44742") — потому что вот так даты хранятся в Excel и Таблицах.

И функция ТЕКСТ позволяет это исправить — укажите нужный формат во втором аргументе, как если бы вводили его в окне "Формат ячеек" (Ctrl + 1).

Итак, для даты в нашем примере нужна будет такая формула:

="По состоянию на: " & ТЕКСТ (дата; "ДД.ММ.ГГ")

Подробнее про пользовательские числовые форматы можно посмотреть в видео — оно на основе Google Таблиц, но все работает практически идентично.

Как разделить текст по нескольким разделителям?

Например, по косой черте и дефису, как в примере.

В новой версии Excel можно воспользоваться функцией ТЕКСТРАЗД / TEXTSPLIT.
А чтобы она работала с несколькими разделителями, отправим их в массив:

{"первый разделитель"; "второй"; ... }

Если бы мы сделали так (не массив, а одна текстовая строка) — то оба символа считались бы одним разделителем:

"/-"

А в Google Таблицах есть функция SPLIT, работающая похожим образом. Но там массив указывать не надо. Задайте третий аргумент как ноль, если хотите, чтобы все символы считались одним разделителем, и единицей, если каждый должен считаться отдельным (это вариант по умолчанию, так что можно просто ограничиться двумя аргументами). Для нашей задачи:

=SPLIT(A2; "/-")

или
=SPLIT(A2; "/-"; 1)

Выводим формулой список всех рабочих дней — от заданной до сегодняшней (так в примере; но можно и наоборот — как вам нужно)

Для этого:
1 вычислим число рабочих дней в периоде (функция ЧИСТРАБДНИ / NETWORKDAYS)

2 Засунем это число в функцию ПОСЛЕД / SEQUENCE и получим последовательность чисел от 1 до числа рабочих дней в периоде

3 отправим эту последовательность в функцию РАБДЕНЬ / WORKDAY — она возвращает дату, которая наступит по прошествии N рабочих дней от заданной. В нашем случае она выдаст много дат, по одной для каждого числа полученной на прошлом шаге последовательности.

Формула такая:

=РАБДЕНЬ(начальная дата-1;ПОСЛЕД(ЧИСТРАБДНИ(начальная дата ;конечная дата)))

На скриншоте конечная дата задается функцией СЕГОДНЯ / TODAY — так что список будет обновляться каждый день (кроме выходных 😉)

P.S. G.S. В Google Sheets тоже будет работать, только не забудьте нажать Ctrl+Shift+Enter, чтобы добавилась функция ArrayFormula.

А как быть в старых версиях Excel (до 2019 включительно), где функции ПОСЛЕД / SEQUENCE нет?

Там можно воспользоваться прогрессией (Series). Ищите инструмент на вкладке "Главная" в коллекции команд "Заполнить" (стрелка вниз).
Увы, это будет статичная история, а не динамическая, как в случае с формулами... но тоже неплохо!

Тем временем большая часть тиража книги "Магия таблиц" уже распродана за 4 месяца — более 2100 экземпляров из 2500, в издательстве осталось совсем чуть-чуть, и в магазинах тоже — в Лабиринте книга и вовсе кончилась, например.

Так что если планируете сделать полезные и табличные подарки коллегам/друзьям на Новый год или по другим поводам, поторопитесь!
Книгу можно заказать тут:
На сайте издательства (там же электрическая книга)
Book24
Лабиринт (потрачено)
Озон
Wildberries
Читай-город
Буквоед

За это время прибавилось отзывов! На Озоне 50 оценок с рейтингом 4.92, на WIldberries 22 оценки — 4.8.

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

Ну и несколько других (просто копируем из магазинов как есть):
— Настоящий справочник, очень пригодился в работе
— Отличная книга!
— Отличная полезная книга
— Книга бесценна по своему содержанию.
— уровень знания excel неплохой, знания математики забыты. люблю, когда все объясняют. для меня книга очень полезная. автору, продавцу и озону спасибо ❤️
— содержание: описан полезный функционал
— Купил книгу так как до этого проходил два курса от автора. Очень доволен, пользуюсь в том числе как справочником-шпаргалкой, если что-то подзабылось. Настоятельно рекомендую.
— Книга-мечта! Отлично дополняет курс "Магия Excel"! Полностью книгу не читала, но обращаюсь, когда нужно быстро найти функцию или освежить в памяти комбинацию клавиш для определенных действий!

Новинка: флажки в Excel

Лучше поздно, чем никогда 😺 В Excel — пока только ранним пташкам, получающим обновления первыми — доступны флажки в ячейках. Как в Google Таблицах, где они появились уже давно.

Флажки — переключатели, меняющие значения с ИСТИНА / TRUE на ЛОЖЬ / FALSE и наоборот. Их можно использовать для чек-листов, списков, ссылаться на них в формулах (включили флажок — начисляем скидку в этой строке с помощью функции IF / ЕСЛИ, например) и в условном форматировании (поставили флажок — зачеркнули или покрасили цветом строку)

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

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

Вот такая диаграмма для сравнения двух показателей (план и факт, производство и продажи, ...). Как ее построить?

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

— Делаем разные значения бокового зазора у обеих гистограмм, чтобы столбики отличались по ширине. Заливку у обеих делаем прозрачной (в примере 40%). Это настраивается в панели "Формат", выделяем столбики и нажимаем Ctrl+1.

— Подписи забираем из ячеек в столбце D. Там формула (просто темп прироста, итоговый показатель — факт — делим на базисный — план — и вычитаем единицу) и пользовательский формат:

+0%* 🔥;-0%* 👎

(смайлики выберите по вкусу; чтобы зайти в окно настройки формата, нажмите Ctrl+1)

— Добавляем таблицу данных вместо основных подписей и легенды и убираем всякое ненужное (линии сетки, например).

P.S. Файл с диаграммой прикреплен в отдельном сообщении выше — забирайте!

Выбираем случайный элемент из списка

Для начала надо сгенерировать случайное число — с этим поможет функция СЛУЧМЕЖДУ / RANDBETWEEN. Она выдает случайное целое число в заданном диапазоне.

Например, если список из трех элементов:

=СЛУЧМЕЖДУ(1;3)

А чтобы выбрать элемент по сгенерированному номеру, понадобится функция ВЫБОР / CHOOSE. В первом аргументе у нее задается порядковый номер, а далее по порядку элементы, из которых нужно выбрать.
=ВЫБОР(номер; первый элемент; второй; ...)

В нашем случае номером будет случайное число, ну а все следующие аргументы ВЫБОРа — это элементы списка.

В новой версии Excel можно сгенерировать не одно значение, а сразу целый массив, ведь теперь есть функция СЛМАССИВ / RANDARRAY:

=СЛМАССИВ(число строк; число столбцов; от; до; целое)

В нашем случае 10 строк, столбцы пропускаем (1 по умолчанию), от 1 до 3, целые числа. И получим список из 10 случайных элементов списка одной формулой.

Аргументами функции ВЫБОР / CHOOSE могут и... другие функции!

Можно сделать выпадающий список, в котором будет несколько вариантов вычислений.
В нашем простом примере — в ячейке M8 список (проверка данных) из диапазона M4:M6 — там среднее, сумма и количество.

Функция ПОИСКПОЗ / MATCH подскажет нам, каким по порядку в списке идет выбранный тип вычисления. Полученное число будет первым аргументом функции ВЫБОР. А последующие аргументы — функции.

Например, если пользователь выбрал "сумму", ПОИСКПОЗ будет возвращать 2. А значит, ВЫБОР выдаст второй элемент из списка. Это функция СУММ. Получается, что мы выбираем, какую формулу вычислять на основе значения из выпадающего списка. Простой вариант добавления интерактива в отчеты!

Выделяем всю строку при наличии в столбце слова (буквы, текста)

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

Можно функцией ПОИСК / SEARCH или НАЙТИ / FIND. Первая работает без учета регистра, вторая — с.

=НАЙТИ (что ищем; где ищем)

На выходе — порядковый номер символа, позиция искомого значения в тексте для поиска.

В тех случаях, когда они не находят искомое значение, возвращается ошибка. В случае условного форматирования при ошибках в формуле правило не сработает, а это то что нам нужно.

Главное в условном форматировании — не запутаться со строками и столбцами. Формула в условном форматировании вводится один раз — при ее формировании учитывайте, что вы вводите формулу для левой верхней ячейки диапазона, а далее на все остальные ячейки она будет «протягиваться» (как если бы мы ввели формулу в B2 и далее скопировали и вставили во все остальные ячейки в трех столбцах). И ссылки на ячейки будут меняться, если они являются относительными.

Смотрим на видео!

Получаем только четные или нечетные строки

Для такой задачи нам понадобится функция ВЫБОРСТРОК / CHOOSEROWS. Она может извлечь (вернуть) строки по их номерам.

А вот как достать эти номера? Получается, нам нужны строки с первой (если нечетные) до последней в таблице.
Число строк в таблице можно узнать функцией ЧСТРОК / ROWS.

Нечетных строк в таблице половина — то есть мы поделим общее число строк пополам.
Получается, что нам нужна последовательность 1, 3, 5, ..., Число строк в таблице / 2

Иначе говоря, от единицы с шагом 2, количество — половина строк в таблице.

Засунем все эти параметры в функцию ПОСЛЕД / SEQUENCE — она и выдаст нам нужную последовательность:
ПОСЛЕД(ЧСТРОК(Таблица)/2;1;1;2)

А дальше останется использовать ее как аргумент (точнее, аргументы — ведь это много номеров строк) функции ВЫБОРСТРОК / CHOOSEROWS:
=ВЫБОРСТРОК(Таблица;ПОСЛЕД(ЧСТРОК(Таблица)/2;1;1;2))

Когда ты навертел такого в формулах, что даже Microsoft уже интересно посмотреть, что же там происходит 😺

Вот такое уведомление появилось при работе над одним мини-проектом для большой компании.

До этого не встречали :) а вы?

На горе Фернандо-По применяли мы ГРУПППО

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

Новые функции: GROUPBY / ГРУПППО и PIVOTBY / СВОДПО.

Первая позволяет группировать только в строках, вторая и в столбцах тоже.
Обязательных аргументов всего три или четыре: по какому столбцу группируем, какой агрегируем, какую функцию применяем. Необязательными аргументами можно добавить промежуточные и/или общие итоги, заголовки, сортировать и фильтровать.

В видео (11 мин, со звуком) обсуждаем, как вообще можно сводить данные и чем отличаются разные способы:
— в любой версии Excel: формулы (не очень гибко и без обновления в случае появления новых данных)
— в любой версии Excel: сводная таблица (очень гибко, но сводную надо обновлять вручную/макросом)
— в 2016 и новее (а также 2010-2013 с установкой надстройки): Power Query (обычно этой надстройкой данные предварительно обрабатывают и потом строят сводную, но в ней можно сразу группировать) (относительно гибко, обновлять вручную или по расписанию раз в N минут)
— пока только с бета-каналом обновлений в Microsoft 365, позже у всех подписчиков 365: новые функции (гибко и обновляется все автоматом)

Видео на Youtube: https://www.youtube.com/watch?v=1xN7Hly-oc0
(про новые функции с 6 минуты)