Расширенный фильтр: быстро фильтруем по списку значений

Расширенный (Advanced) фильтр в Excel существует уже несколько веков, а многие пользователи про него не слышали — и очень жаль! Мощный инструмент. Вот один из примеров: фильтруем по списку. Смотрим видео без звука!

Условия для него задаются в ячейках: сверху заголовки столбцов из исходных данных, на которые накладываются условия, под ними — сами условия. Одна строка = одна комбинация условий, один фильтр.
В нашем случае условие на один столбец — "Товар". Скопируем заголовок из исходных данных, вставим над списком товаров.

После выделяем любую ячейку исходных данных и вызываем расширенный фильтр. Он прячется под надписью "Дополнительно" (Advanced) справа от кнопки обычного фильтра на вкладке ленты "Данные" (Data).

В появившемся диалоговом окне нужно выделить диапазон условий и нажать ОК.

Де-факто это возможность сразу применять несколько фильтров одним движением, а также сразу вставлять выборку в другой диапазон, а не фильтровать на месте.

Кроме того, есть функции баз данных, которые работают с условиями расширенного фильтра! Они есть и в Excel, и в Google Таблицах (а сам расширенный фильтр только в Excel).

Подробнее про них и про правила записи условий тут:
https://teletype.in/@renat_shagabutdinov/4lVaI_Pj7

Окно «Найти и заменить» (Find and Replace) во многих случаях помогает решить задачи по обработке текстовых значений (и не только) без применения сложных функций и формул. Это окно позволяет исправить большое количество формул, поменять форматирование всех однотипных ячеек, удалить определенные слова или символы из диапазона или из всей книги Excel.

Его можно вызвать сочетаниями клавиш Ctrl + F (⌘ + F) или Ctrl + H (⌃ + H) — в обоих случаях откроется одно и то же диалоговое окно, но в первом случае на вкладке «Найти» (Find), а во втором — «Заменить» (Replace).

Вот несколько нюансов:
— Если вы предварительно выделили диапазон ячеек, то поиск/замена будут производиться в пределах этого диапазона. Если же нет — то на листе или в книге (изменить этот параметр можно в поле «Искать» (Within) в окне «Найти и заменить»; по умолчанию будет лист).

— Если вы хотите что-то удалять, а не заменять, просто оставьте поле «Заменить на» пустым. Заменить на ничто = удалить, не так ли?

— Можно производить изменения сразу с большим количеством формул. Например, вам нужно поменять диапазон или функцию во многих формулах. Выделите диапазон с формулами, вызовите окно «Найти и заменить» и введите в поле «Найти» тот фрагмент формул, который вы хотите изменить, а в «Заменить на» — то, на что хотите его изменить. Убедитесь, что в списке «Область поиска» (Look in) заданы «Формулы» (Formulas).

А еще в окне «Найти и заменить» (как и в случае с рядом других инструментов и функций Excel) можно использовать символы подстановки!

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

Например, если вам нужно найти/заменить/удалить любой текст в скобках (вместе с самими скобками), то в поле «Найти» нужно ввести:

(*)

А если нужно найти все скобки, в которых внутри слова строго из 4 букв (или 4 цифры или же 4 любых символа), нужно указать четыре знака вопроса в скобках:

(????)

Если вам нужно найти именно звездочки или знаки вопроса (например, чтобы удалить все звездочки в какой-то таблице), поставьте перед символом тильду (~).
~* — поиск звездочки,
~? — поиск знака вопроса,
~~ — поиск самой тильды.

Редактируем скрытую ячейку, не раскрывая строки/столбцы

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

1 Ввести адрес ячейки в поле "Имя" (слева от строки формул);

2 Ввести его в окне "Переход" (вызывается клавишей F5)

После любого из двух действий можно редактировать значение в строке формул (или просто его увидеть — иногда нужно именно это). Смотрим на видео!

P.S. А вот в Google Таблицах такая магия не сработает, увы 😿

Открытие Excel по сочетанию клавиш

Находим ярлык Excel в стартовом меню (меню "Пуск"), правая кнопка — Properties (если ее нет, то сначала открываем расположение файла, Open File Location, и уже там в проводнике снова в контекстном меню по правой кнопке находим Properties).

Ну а там идем в Shortcut key и вводим сочетание. Если ввести просто Ctrl + X, допустим, то оно будет автоматически заменено на вариант с Alt — как на скриншоте.

Захотите в будущем отказаться от сочетания клавиш — просто удалите его в этом диалоговом окне, нажав Backspace, появится надпись None.

Как вы понимаете, вероятно, это можно провернуть и не только с Excel :)

Повторное применение фильтра

Вы поставили фильтр (Ctrl + Shift + L, кстати). Поменяли что-то в данных. И вот некоторые строки, в которых вы вносили изменения, уже фильтрации не соответствуют. Если добавились новые данные в конце таблицы — они тоже не отфильтруются автоматически. Как отобразить актуальные данные?
Не нужно отключать фильтр и настраивать снова.

Просто нажимайте Ctrl + Alt + L / . Или кнопку "Повторить" (Reapply) на ленте инструментов рядом с кнопкой фильтра (на вкладке "Данные" / Data).

---
💫Магия новых функций Excel. Революция в табличных формулах: от SORT и FILTER до GROUPBY и LAMBDA

Как разрешить вводить в диапазоне только рабочие дни?

Для этого понадобится проверка данных с формулой.

Данные → Проверка данных → Тип данных: Другой
Data → Data Validation → Allow: Custom → Formula

Формула должна возвращать ИСТИНА (TRUE), то есть условие должно выполняться. Иначе проверка данных будет выдавать ошибку или предупреждение (зависит от настроек в разделе «Сообщение об ошибке», Error Alert).

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

В нашем случае в формуле будем использовать функцию ДЕНЬНЕД / WEEKDAY. Первый аргумент — дата, а второй — тип нумерации, где 2 = неделя начинается с понедельника.

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

Такая формула будет возвращать ИСТИНА / TRUE при дне недели от 1 до 5.

---
💫Магия новых функций Excel. Революция в табличных формулах: от SORT и FILTER до GROUPBY и LAMBDA

Горячие клавиши для быстрого перемещения и выделения в Excel🔥

Ctrl + PgDn/PgUp — следующий/предыдущий рабочий лист

Ctrl + Backspace — возвращаемся к активной ячейке

Ctrl + A — выделяем всю текущую область (диапазон)

Shift + пробел — выделяем всю строку (если активна "умная таблица" — то в пределах таблицы, иначе — в пределах всего листа)
Ctrl + пробел — выделяем весь столбец

Ctrl + стрелки — перемещаемся в конец диапазона (в направлении стрелки). Вместе с Shift — выделяем до конца диапазона.

Ctrl + End — перемещаемся в конец активной области на листе (в самые последние строку и столбец с данными)

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

---
💫Магия новых функций Excel. Революция в табличных формулах: от SORT и FILTER до GROUPBY и LAMBDA

Функция ISOMITTED / ПРОПУЩЕНО: добавляем к пользовательским функциям необязательные аргументы

Вашему вниманию кусочек видео из курса "Магия Excel", посвященного функции LAMBDA.
LAMBDA позволяет создавать собственные функции. Синтаксис у нее такой:

=LAMBDA([переменная]; … ; [переменная]; формула)

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

=LAMBDA(план ; факт ; факт / план - 1)

В самих ячейках LAMBDA работать напрямую не будет — ведь тут параметры, а не конкретные значения / ячейки. Вы можете ее проверить, добавив конкретные значения в скобках после функции:

=LAMBDA(план ; факт ; факт / план - 1)(B2;C2)

Но в целом все это затевается ради того, чтобы использовать новую функцию под ее именем уже без всяких лямбд. Для этого нужно сохранить ее в диспетчере имен (Ctrl + F3) под любым именем, какое вы хотите присвоить этой функции — например, “ТемпПрироста”. И дальше использовать эту функцию в пределах книги (а если хочется перенести ее в другую — можно скопировать создать пустой лист в книге и скопировать/переместить его в другую книгу — это приведет к переносу имен, а значит, и функции).

И в функциях можно даже создавать необязательные аргументы — для этого и нужна ISOMITTED / ПРОПУЩЕНО — она возвращает ИСТИНА / TRUE, когда аргумент пропущен. В видео пример создания пользовательской функции с необязательным аргументом.

---
💫Магия новых функций Excel. Революция в табличных формулах: от SORT и FILTER до GROUPBY и LAMBDA

Был столбец с пунктами типа 10.1, 10.2, 10.3...10.10, 10.11, формат текстовый... Поменял формат на числовой, после чего часть цифр превратилась в пятизначные типа 45430, 44438, часть в даты.

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

'10.1

А если нужно уже имеющиеся данные превратить в порядковые пункты, можно использовать такую формулу:
=ДЕНЬ(ячейка) & "." & МЕСЯЦ(ячейка)
За счет того, что мы склеиваем несколько значений в одну текстовую строку (с помощью амперсанда), результат будет текстом.

Новый урок в курсе "Магия новых функций Excel":
Флажки в Excel и Google Таблицах

Друзья, если вы когда-либо приобретали курс "Магия новых функций Excel", заглядывайте в личный кабинет — там вас ждет новый урок про флажки:
— "новые" флажки (checkboxes) Excel
— условное форматирование и флажки
— ссылки на флажки в формулах
— флажки и диаграммы
— "старые" флажки Excel (элементы управления)
— флажки в Google Таблицах

https://shagabutdinov.ru/magic-excel

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

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

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

Функция ОКРУГЛТ / MROUND: округляем числа и время с нужной точностью

Вот такая замечательная функция: округляет число (это первый аргумент) с нужной точностью (второй аргумент – число или ссылка на ячейку с числом). Есть, допустим, у вас цены, а вы хотите их «причесать» - с точностью 50:

=ОКРУГЛТ(число; точность)

Можно округлять и время. Это ведь тоже число в Excel и Google Таблицах – число от нуля до единицы. Например, 0,5 – половина дня, то есть 12:00:00.
Соответственно, если мы хотим округлить времена с точностью, допустим, 10 минут, можно поступить так:

=ОКРУГЛТ(A2;10/60/24)

То есть посчитать, какую долю от суток занимают 10 минут.
Или так:

=ОКРУГЛТ(A2;"00:10:00")

---
💫Магия новых функций Excel. Революция в табличных формулах: от SORT и FILTER до GROUPBY и LAMBDA

Заставляем функцию обрабатывать массив, даже если она этого не хочет 😸

Некоторые функции — например, КОНМЕСЯЦА / EOMONTH (возвращающая последнюю дату месяца, отстоящего от даты на заданное число месяцев) — не хотят выдавать результат в виде массива. А возвращают в такой ситуации ошибку — как в столбце B в примере, когда мы дали функции сразу много дат, чтобы и результат получить в виде массива дат.

Но если добавить к ссылке на диапазон знак "+" (плюс), то магия случится! 🔥

Так не работает:

=КОНМЕСЯЦА(A2:A16;1)   

А так работает:

=КОНМЕСЯЦА(+A2:A16;1)   

Почему? Из-за плюса сначала вычисляется выражение с этим самым плюсом — и диапазон A2:A16 становится массивом значений. А с этим уже функция справляется.
Это все — про Excel 2021 / 2024 / 365.
Ну а в целом про динамические массивы можно посмотреть в этом видео: https://t.iss.one/lemur_excel/95

Сводные таблицы Google Spreadsheets — новый курс

Это 20 видео с исходными и готовыми файлами с примерами, в которых разбираются все-все нюансы сводных таблиц Google Spreadsheets и дополнительные смежные темы тоже:
— Что вообще сводные могут и не могут, какие данные подходят (и как подготовить неподходящие!), как обновляются в Excel и Google (изменение старых данных и новые строки)
— Настройка отчетов: вычисления, строки и столбцы, фильтры и срезы, числовые форматы, условное форматирование и спарклайны
— Рассчитываемые поля: точно разберетесь, как ссылаться на другие данные из них, использовать условия с визуализацией выполнения и когда рассчитываемые поля не подходят
— Сводные без сводных — с помощью формул на LAMBDA. В том числе с текстом в области значений
— Сводная из нескольких источников
— Функция GETPIVOTDATA
— Визуализация: спарклайны одной формулой, в том числе только для промежуточных итогов (одного уровня), спарклайны, меняющие цвет по условию, тепловая карта без чисел, визуализация план-факта спарклайнами и другие примеры
— Отмена свертывания (unpivot)

https://shagabutdinov.ru/pivot_google

Не устаем рассказывать про одно из наших любимых сочетаний Ctrl+Backspace — это возвращение к активной ячейке. Удобно при работе с формулами, расширенным фильтром, другими окнами.

Например. Многие из вас знают одно из любимых Лемуром сочетаний клавиш Ctrl + Shift + стрелки (⌘ + ⇧ + стрелки).
Оно позволяет (если ловкости лап хватит все это нажать одновременно) выделить диапазон до последней заполненной ячейки в направлении стрелки. В том числе при вводе формулы — можно выделить диапазон в аргументе функции, как на видео.

Но в результате мы можем переместиться довольно далеко от формулы. И чтобы вернуться к текущей ячейке (в которую мы эту самую формулу вводим, не заканчивая при этом ввод формулы), пригодится сочетание — Ctrl + Backspace (⌃ + Delete).

Новое видео: Урок 18. Функция LET: упрощаем и ускоряем вычисления

Покупали когда-либо курс "Магия новых функций Excel"?
Тогда скорее в личный кабинет — там вас ждет новый, уже восемнадцатый, урок. Про функцию LET.

Совсем скоро — и другие уроки! Про все нюансы функции ПРОСМОТРX / XLOOKUP и ФИЛЬТРацию с регулярными выражениями заодно.
https://shagabutdinov.ru/magic-excel

P.S. А по этой ссылке найдете ряд бесплатных видеоуроков по Excel и Google Таблицам. И этот список тоже скоро пополнится.

Одной формулой собираем ТОП-N сделок из всех умных таблиц в списке и добавляем название таблицы к каждой строке

Ух! Вот что могут (и очень, очень многое) новые формулы.

Что тут вообще происходит?

С помощью LET создаем функцию f от трех аргументов: имя таблицы x, сколько строк берем n, по какому столбцу сортируем cl. Имена переменных роли не играют - можете придумывать любые.

Сама функция f:
1 Сортирует таблицу (так как мы получаем из ячейки имя таблицы, это текст, его нужно сделать активной ссылкой через ДВССЫЛ / INDIRECT)
2 Ищет позицию нужного заголовка через ПОИСКПОЗX / XMATCH. Ссылку на массив заголовков получаем тоже через ДВССЫЛ, добавляя к названию таблицы "[#Заголовки]"
3 СОРТирует по нему (SORT)
4 Берет первые n строк (функция ВЗЯТЬ / TAKE)
5 Добавляет справа (горизонтально, функция ГСТОЛБИК / HSTACK) расклонированное n раз название таблицы (функция MAKEARRAY) — массив из n строк, 1 столбца, внутри функция, которая ничего не делает с этими номерами, а просто возвращает имя таблицы x

Ну а потом мы отправляем в REDUCE список таблиц. И собираем последовательно массив: начальным значением будут заголовки из первой таблицы. Далее с помощью ВСТОЛБИК / VSTACK объединяем таблицы (топ-N строк), возвращаемые нашей функцией f, одну под другой.

Зачем функция ЕСНД / IFNA? У нас нет заголовка для названий таблиц, массив заголовков в самих таблицах на одно значение меньше. И там будет ошибка #Н/Д. Ее и нужно заменить на желаемое название.

Нюанс с датами: формула будет возвращать их без форматирования. Так, как их видит Excel — просто в виде целых чисел. Можно форматировать сами ячейки с запасом, можно делать это через ВЫБОРСТОЛБЦ / CHOOSECOLS и функцию ТЕКСТ / TEXT, можно через условное форматирование, как на скриншоте и в примере — искать заголовки со словом "Дата" и применять к ним формат даты.

Файл с примером по ссылке

---
Хотите писать такие же формулы? Добро пожаловать:
💫Магия новых функций Excel. Революция в табличных формулах: от SORT и FILTER до GROUPBY и LAMBDA

Видео: объединение таблиц по 2 условиям

8 минут со звуком

В видео разбираем, как объединять таблицы по двум условиям: со вспомогательным столбцов и без.

В деле функции ВПР / VLOOKUP, ПРОСМОТРX / XLOOKUP и СУММЕСЛИМН / SUMIFS.

Файл с примером по ссылке.

А другие бесплатные видеоуроки (и этот тоже) можно найти по ссылке:
https://shagabutdinov.ru/video

Функция РАЗВЕРНУТЬ / EXPAND

Что она делает? Увеличивает размеры массива. Все "дополнительные" значения (то есть дополнительные строки и/или столбцы, то, чего нет в исходном массиве, который задается в первом аргументе функции) будут ошибками #Н/Д (#N/A).

Но их можно заменить на какое-то значение — указав его в четвертом аргументе.

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

В предыдущем варианте — по ссылке — мы использовали для этого другую функцию MAKEARRAY.

В функции LET можно задавать свои функции! Это, конечно, экзотика (потому что, как правило, если нам нужно применять одно и то же вычисление много раз, мы используем LAMBDA + MAP или другую вспомогательную функцию), но тем не менее.

Если после имени переменной последует не константа / выражение, а функция LAMBDA, то это будет имя функции, которую потом можно в LET вызывать. В следующем примере у нас простая функция, умножающая число, данное ей на входе, на 2:

=LET(f; LAMBDA (a;a*2); f(10) + f(5) )

Здесь f – название функции, а – название переменной, аргумента этой функции. Затем в последнем аргументе LET мы уже ее вызываем с конкретными значениями 10 и 5.

Про базовые сценарии применения LET читайте в статье:
https://shagabutdinov.ru/tpost/47brblc2e1-novaya-funktsiya-excel-i-google-tablits

VisiCalc и первые табличные функции

Сегодня немного истории, друзья.

Excel — это не первые электронные таблицы, да-да! Первым было приложение VisiCalc, созданная в 1979 году Дэном Бриклином и Бобом Фрэнкстоном.

"Визуальный калькулятор" — идея была в том, чтобы не менять все вводные для расчетов, как это приходится делать на обычном калькуляторе. И это свойство электронных таблиц — автоматическое обновление формул и сами формулы — до сих пор ключевое.

А что насчет формул? Сейчас в Excel более 500 функций, постоянно добавляются новые.
В VisiCalc в 1979 году их было чуть меньше — 21.
Многие вы узнаете:
@SUM, @NA, @ ERROR, @MAX, @MIN, @ AVERAGE, @ COUNT, @NPV, @ LOOKUP, @ABS, @INT, @EXP, @ LOG10, @LN, @PI, @SIN, @COS, @TAN, @ ASIN, @ ACOS, @ ATAN.

И никаких тебе LAMBDA и даже СУММЕСЛИМН 🤠

Если захотите секунд на 5 заглянуть в прошлое и найти связь Excel с VisiCalc — она есть не только в названиях функций.
Введите @ (а именно так начинались формулы в VisiCalc) и вводите название функции (например, СУММ) — и увидите, что собачка работает как знак "равно", вы вводите формулу.