Аргументами функции ВЫБОР / 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 за последние месяцы, были отражены в новом тираже.

Убираем выбросы формулой: например, 10% самых маленьких и 10% больших заказов.

Для этого: посчитаем (для расчета числа значений используем СЧЁТЗ / COUNTA), а сколько вообще в таблице заказов и умножим на 10% или 0,1, чтобы получить число заказов, которые нужно убрать:
СЧЁТЗ(столбец из таблицы)*10%

Отсортируем (функция СОРТ / SORT) таблицу с заказами по столбцу, на основе которого убираем выбросы — например, в нашем случае по сумме заказа в третьем столбце

СОРТ(Таблица; номер столбца)

И далее дважды применяем функцию СБРОСИТЬ / DROP — она убирает строки из начала (если второй аргумент положительный) или из конца (если аргумент отрицательный) массива/таблицы/диапазона.
Нам придется два раза — первые и последние 10%.
Здесь число строк = сколько строк нужно удалить (вычислили на первом шаге):
=СБРОСИТЬ(СБРОСИТЬ(отсортированная таблица; число строк); -число строк)

Все вместе для нашей таблицы будет выглядеть так:
=СБРОСИТЬ(СБРОСИТЬ(
СОРТ(Заказы;3);
СЧЁТЗ(Заказы[Код заказа])*10%);
-СЧЁТЗ(Заказы[Код заказа])*10%)

Добавляем к дате день недели и выделяем выходные

Допустим, мы с вами хотим видеть в каждой дате день недели - не "01.01.2023", как по умолчанию, а "01.01.2023 Вс".

Для этого заходим в формат ячеек (Ctrl + 1) и добавляем к формату "ДДД" (DDD). Это краткое обозначение дня недели ("Вс"). Для полного ("Воскресенье") понадобится код "ДДДД" (DDDD).

Ну а чтобы выделить цветом выходные (или другие дни) - воспользуемся условным форматированием (Conditional Formatting).
Зададим правило с формулой, а в ней будем использовать функцию ДЕНЬНЕД / WEEKDAY.
Она возвращает порядковый номер дня недели. Чтобы нумерация была привычной для нас с вами, добавьте второй аргумент, равный двойке:

=ДЕНЬНЕД (ячейка с первой датой в диапазоне; 2)

Тогда понедельнику будет соответствовать единица (иначе - воскресенью), вторнику - двойка и так далее.

И остается добавить условие - день недели у нас должен быть больше 5 (то есть 6 или 7, суббота или воскресенье), чтобы ячейка заливалась цветом.
Все показываем на видео!

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

Нужно получить номер квартала или посчитать число пятниц в периоде?
Получить начало и конец месяца для заданной даты?

Ловите пачку полезных формул для работы с датами в Excel!

Конец месяца:

=КОНМЕСЯЦА(дата;0)

Начало месяца:

=КОНМЕСЯЦА(дата;-1)+1

Месяц:

=МЕСЯЦ(дата)

День:

=ДЕНЬ(дата)

Год:

=ГОД(дата)

День недели цифрой:

=ДЕНЬНЕД(дата;2)

День недели текстом:

=ТЕКСТ(дата;"ДДДД")

10 рабочих дней от даты:

=РАБДЕНЬ(дата;10)

Рабочих дней в месяце:

=ЧИСТРАБДНИ(КОНМЕСЯЦА(дата;-1)+1;КОНМЕСЯЦА(дата;0))

Кол-во вторников в месяце:

=ЧИСТРАБДНИ.МЕЖД(КОНМЕСЯЦА(дата;-1)+1;КОНМЕСЯЦА(дата;0);"1011111")

Квартал - вариант 1:

=ЦЕЛОЕ((МЕСЯЦ(дата)+2)/3)

Квартал - вариант 2:

=ВЫБОР(МЕСЯЦ(дата);1;1;1;2;2;2;3;3;3;4;4;4)

Номер недели (ГОСТ):
=НОМНЕДЕЛИ.ISO(дата)

Давайте продолжим тему ПРОСМОТРов. И поговорим о старой функции LOOKUP / ПРОСМОТР.

Функция по синтаксису похожа на новую XLOOKUP / ПРОСМОТРX, о которой мы писали выше. Но она как раз была давно — в Excel даже есть примечание, что функция LOOKUP / ПРОСМОТР остается для совместимости. И у нее есть минусы :она требует постоянной сортировки данных, не особо подходит для поиска текста.

Поэтому лучше использовать VLOOKUP, XLOOKUP или INDEX+MATCH. Во всяком случае, для объединения таблиц по текстовому ключу.
Но все же в некоторых экзотических случаях LOOKUP используют до сих пор. Например, для нечеткого текстового поиска, когда нужно находить в названиях какое-то слово и заменять все значения с этим слово на одно и то же типовое (исправлять разные наименования компаний или товаров, например) — такой пример есть в статье.

Группировка нескольких текстовых элементов в сводной

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

Для этого:
1 Выделяем несколько элементов (зажав клавишу Ctrl);

2 Щелкаем правой кнопкой и в контекстном меню выбираем Группировать / Group
или
2 Нажимаем на ленте на вкладке "Анализ сводной таблицы" (PivotTable Analyze) — "Группировка по выделенному" (Group Selection)

3 Щелкаем на название группы (по умолчанию будет "Группа1") и переименовываем.