Список листов формулой

Для этой цели достанем из закромов Excel древнейшую и недоступную на рабочем листе макрофункцию ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ (GET.WORKBOOK)

Если дать ей аргумент 1 — она возвращает массив с названиями всех листов.
Второй аргумент — имя книги, но его можно пропустить, и тогда формула будет возвращать данные по активной книге.

Так как она не работает в ячейках, придется создать имя — Ctrl+F3 — придумайте имя (у нас будет "Оглавление") — и введите:

=ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ (1)

Теперь остается перевернуть (транспонировать) список с помощью ТРАНСП / TRANSPOSE:

=ТРАНСП(Оглавление)

и избавиться от имени книги, идущей в квадратных скобках:

=ТЕКСТПОСЛЕ(ТРАНСП(Оглавление);"]")

Оглавление макросом можно найти тут:
https://hottg.com/lemur_excel/29

Розыгрыши в Excel: немного табличного хулиганства (и пользы тоже)

При написании статьи ни один офисный сотрудник не пострадал, все тестировалось только на себе и коте Лемуре.

До первого апреля еще далеко... Но будем готовиться заранее. Да и если у вас есть на работе кто-то, кого вы очень любите...
Впрочем, мы скажем, что все это исключительно в развлекательных целях. К тому же по большинству инструментов мы обсуждаем пользу, а не только возможность подшутить на кем-то в Excel!

Итак:
— Прячем объекты
— Прячем сетку
— Прячем ярлыки листов
— Меняем пользовательские списки
— Автозамена функций/текста на что-то другое
— Автоматическое проговаривание ячеек

https://shagabutdinov.ru/tpost/uj8ggrn4i1-rozigrishi-v-excel-nemnogo-tablichnogo-h

Книга "Магия таблиц" — третье издание в продаже!

— Более 100 функций с индексом (два языка, где работает — Excel/Google, на каких страницах встречается)
— 1 фотография кота
— Сотни скриншотов
— 50 файлов с примерами
— Издание №3
— Уже более 500 отзывов на разных площадках со средними оценками 4.8-5.0
— 528 страниц

Третье издание уже в продаже:
Озон
WB
Издательство

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

Расширенный (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://hottg.com/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