Тем временем большая часть тиража книги "Магия таблиц" уже распродана за 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 минуты)

Теперь формулы со вспомогательными функциями LAMBDA можно записывать короче.

Допустим, вы хотите посчитать среднее значение в каждой строке. Чтобы обработать каждую строку, нужно использовать BYROW.
Задать в первом аргументе диапазон, во втором — LAMBDA, внутри которой будет обозначение для каждой строки и формула — что с ней делать:

=BYROW(диапазон;LAMBDA(строка;СРЗНАЧ(строка)))

(здесь на месте переменной "строка" может быть любое другое название — вы сами решаете, под каким именем обращаться к строке)

А теперь будет работать и следующая форма:

=BYROW(диапазон;СРЗНАЧ)

=BYROW(диапазон;AVERAGE)

Подробнее про LAMBDA и BYROW можно узнать в бесплатном мини-курсе на Stepik:
https://stepik.org/course/182713

Считаем количество ответов на форму... формулой

Вы проводите опрос и хотите быстро посмотреть статистику: сколько раз пользователи выбирали тот или иной вариант при ответе на каждый вопрос (конечно, такое не прокатит с открытыми вопросами, а только при выборе из списка вариантов)

Можно поступить так:
1. выведем список уникальных ответов (функция UNIQUE)

2. для каждого ответа посчитаем, сколько раз он встречается в столбце (COUNTIF / СЧЁТЕСЛИ)

3. Объединим в одну текстовую строку через дефис или другой разделитель:
COUNTIF(диапазон;ответ)&" - "&ответ

4. Чтобы все было одной формулой, проделаем это через MAP — массивом значений для этой функции и будет список уникальных ответов. Для каждого из них мы будем считать, сколько раз он встречается, и склеивать число с текстом ответа.
Можно добавить проверку на пустоту через ISBLANK / ЕПУСТО, чтобы не выводить пустые ответы и ссылаться на открытый диапазон (если ждете новых ответов на форму).

=MAP(UNIQUE(диапазон с ответами);LAMBDA(ответ;IF(ISBLANK(ответ);"";COUNTIF(диапазон;ответ)&" - "&ответ)))

Уникальные пары значений

Нам нужны уникальные пары значений. Например, пары "Клиент" — "Продукт" (полный список клиентов и продуктов — кто что покупал)

Если столбцы рядом — то можно просто сослаться функцией УНИК / UNIQUE на эти два столбца.

Если они не рядом — то предварительно выбрать их функцией ВЫБОРСТОЛБЦ / CHOOSECOLS. Например, если нам нужны первый и третий столбцы:
=УНИК(ВЫБОРСТОЛБЦ(Сделки;1;3))

Сортируем данные по имени, даже если это не первое слово в ячейке

Дано: хотим сортировать в режиме реального времени таблицу по именам. Но имена у нас в столбце с фамилией и именем — на втором месте, после пробела. То есть просто сортировать по этому столбцу не получится — будет сортировка по первому слову (точнее, всему тексту, Фамилия+имя).

Выход: сортируем функцией СОРТПО / SORTBY по виртуальному столбцу с именами.

Виртуальный столбец получим функцией ТЕКСТПОСЛЕ / TEXTAFTER — будем извлекать текст после пробела. Но не для отдельной ячейки, а сразу для всего столбца ФИО.

Как водится с новыми функциями, магия доступна в Microsoft 365 / Excel Online.

Как заполнить пустые ячейки (вниз, то есть значениями сверху)?

Первый вариант — формулами (любая версия, быстро и на один раз)

Закрепляем верхнюю строку, если еще не)
Идем в конец диапазона (Ctrl+End или Ctrl+стрелка вниз по заполненному столбцу)

Выделяем столбец с пустыми ячейками (можно зажать Shift с активированной последней пустой ячейкой и кликнуть на заголовок)

Нажимаем F5 и далее «Выделить» — «пустые ячейки»

Вводим формулу — нажимаем «равно» и на стрелку вверх
То есть ссылаемся на ячейку сверху (это R[-1]C в нотации R1C1 или =A2 в привычном стиле A1 — для формулы в ячейке A3)

И нажатием Ctrl+Enter вводим формулы во все выделенные ячейки (а это, вспомним, только пустые!)

То есть в каждой пустой теперь ссылаемся на ячейку сверху

Дальше можно формулы скопировать и вставить как значения (Ctrl+C и Ctrl+Shift+V / Ctrl+Alt+V)

Алгоритм в коротком видео (без звука).

Второй вариант — Power Query (2010-2013 с надстройкой, 2016+, если нужно неоднократно применять такое и, возможно, другие манипуляции с источником данных)

Добавляем данные в Power Query (Данные — Из таблицы / диапазоны).
Правой кнопкой по столбцу — Заполнить — Вниз.

Третий вариант — макросы (любая версия, если нужно часто применять в работе — работает мгновенно по нажатию кнопки/сочетанию клавиш)
Про макросы напишем в следующем посте!

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

Не забывайте, что действие макросов через Ctrl+Z отменить нельзя!

Как добавлять макросы в личную книгу:
https://t.me/lemur_excel/30

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

Здесь может быть много вариантов.
Допустим, вы хотите заполнять все пустые ячейки в активном диапазоне — это тот диапазон, который будет выделен по нажатию Ctrl+A (до пустых строк/столбцов).
К нему в коде можно обращаться так:

ActiveCell.CurrentRegion

Вся команда будет выглядеть так: выделяем пустые (как в предыдущем варианте вручную делали через F5 — Выделить) — это метод SpecialCells с параметром (типом выделяемых ячеек) xlCellTypeBlanks. И вставляем во все пустые формулу R[-1]C — то есть ссылаемся на ячейку сверху. Это один из множества примеров того, как формулы со стилем ссылок R1C1 пригождаются в макросах. Не переживайте, если у вас стиль ссылок A1, формулы на листе будут выглядеть именно так: =A2, =A3 и так далее.

ActiveCell.CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

Так как при отсутствии пустых ячеек в диапазоне будет выдаваться ошибку (ибо выделять будет нечего) — лучше добавить строку для игнорирования ошибок
On Error Resume Next

Наконец, если вы сразу хотите превратить формулы в значения, добавьте строку для этого:

ActiveCell.CurrentRegion.Value = ActiveCell.CurrentRegion.Value

———
Если вы хотите заполнять ячейки только в текущем столбце от активной ячейки вниз, то CurrentRegion не подойдет.
В таком случае лучше взять диапазон от активной ячейки (ActiveCell) до последней ячейки в активной области листа (на нее можно ссылаться так — UsedRange).

С помощью свойства Resize увеличиваем диапазон от одной активной ячейки, добавляя столько строк, сколько есть до конца активной области листа.

АктивнаяЯчейка. Увеличиваем (строк на листе — строка активной ячейки + 1)

Например, активна ячейка A10. На листе 3000 строк. Значит, мы добавляем к активной ячейке 3000-10+1 = 2991 строку и получаем диапазон A10:A3000 (в Resize указывается общее число строк (и столбцов во втором аргументе, если нужно), которое нужно включить в получаемый диапазон).

Узнать число строк в диапазоне можно с помощью свойства Rows, строку активной ячейки — Row.

Все вместе будет выглядеть так:

ActiveCell.Resize(ActiveSheet.UsedRange.Rows.Count - ActiveCell.Row + 1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

———
Оба макроса в прикрепленном файле в соседнем посте!

Коллеги порадовали хорошими новостями — первый тираж "Магии таблиц" ушел менее чем за полгода — и издательство будет делать новый, уже 3000 экземпляров (первый был 2500) 🔥

Из первого тиража книги еще остаются на маркетплейсах, а также в розничных магазинах (как Читай-город или Буквоед), если хотите сделать полезный подарок друзьям/коллегам — поспешите! Новый тираж в любом случае будет печататься пару месяцев и до НГ, естественно, книг не прибавится.

На сайте издательства (там же электрическая книга)
Озон
Wildberries

А я буду спешно собирать материал для обновления, чтобы функции, которые появились в Excel за последние месяцы, были отражены в новом тираже.