Привет, друзья! На случай, если вы пропустили пользу из старых постов, собрали для вас самое сочное за прошлые месяцы:

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

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

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

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

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

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

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

Файл с примерами формул для суммирования чисел из видимых строк по условию

Суммируем с условием только видимые строки

Вот такой вопрос от нашей подписчицы. Просто суммировать (а также считать среднее и еще несколько базовых операций) скрытые строки - это функция SUBTOTAL / ПРОМЕЖУТОЧНЫЕ. ИТОГИ (про нее подробнее здесь).

А сумма с условием — это SUMIFS / СУММЕСЛИМН. Эта функция считает не скрытые, а все.

Так что ни одна из них "в чистом виде" тут не поможет: одна будет обрабатывать видимые строки (SUBTOTAL), другая суммировать по условию (SUMIFS). Нам надо совместить. Это можно сделать со вспомогательным столбцом и без, разными способами. Разбираем два варианта в этом посте!

У вас Microsoft 365? Тогда можете попробовать удобную опцию для навигации, которая так и называется:
Вид — Навигация (View — Navigation)

Тут будут видны все объекты на всех листах — сводные таблицы, просто таблицы ("умные"), срезы в этих таблицах и сводных, диаграммы, именованные диапазоны.

Можно щелкать по объектам и перемещаться к ним, а можно прямо здесь удалять/переименовывать (для этого щелкаем правой кнопкой 🐁 по объекту).

В срезах можно менять число столбцов и делать их "горизонтальными".

Это может пригодиться, чтобы "закрепить" срез над таблицей. Для этого можно сделать срез в несколько столбцов (на вкладке ленты "Срез" / Slicer, в которой, собственно, срез и настраивается — она появляется при активации среза).

Затем вставить несколько строк над таблицей (при этом предварительно нужно первую строку закрепить — на вкладке ленты "Вид" / View, "Закрепить области" / Freeze Panes —> "Закрепить верхнюю строку" / Freeze Top Row). Вставить строки можно с помощью контекстного меню (правый щелчок мыши по номеру строки — "Вставить").

И далее переносим срез туда. Теперь он всегда будет наверху.
Чтобы он был компактнее, можно изменить высоту кнопок — как и другие настройки среза, это делается в одноименной вкладке ленты инструментов.

Столбик в гистограмме можно заменить изображением

Для этого скопируйте изображение (Ctrl + C), выделите диаграмму, выделите нужный столбик (просто щелкните еще раз после выделения диаграммы на нужный элемент — вы поймете, что он выделен, когда круглые маркеры по углам останутся только у этого столбика).

И Ctrl + V — вставляем изображение.

После этого можно зайти в панель форматирования (Ctrl + 1), чтобы уменьшить боковой зазор между столбиками. Тогда они станут шире. В нашем случае это поможет с пропорциями!

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

Друзья, если хотите разобраться, как работают формулы массивов в Excel до 2019 включительно и какая революция произошла в 2019 году (с версии Excel 2021 и в Microsoft 365) — вашему вниманию видео по теме.

Это один из 55 уроков курса "Магия Excel" в МИФе. Приходите учиться, будем рады!

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

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

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

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

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

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

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

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

Разрешаем вводить в диапазоне только формулы

Это тоже проверка данных с использованием в правиле... формулы!

Формула будет состоять из единственной функции ЕФОРМУЛА / ISFORMULA, которая проверяет, является ли содержимое ячейки формулой (и если да, возвращает ИСТИНА / TRUE - в случае с проверкой это означает, что именно такое содержимое допускается).

Выделяем диапазон, открываем проверку данных и выбираем правило с формулой:
Данные → Проверка данных → Тип данных: Другой → Формула
Data → Data Validation → Allow: Custom → Formula

Формула будет такой:
=ЕФОРМУЛА(первая ячейка диапазона с проверкой)

Теперь в этом диапазоне при попытке ввода значений, а не формул, будет появляться сообщение об ошибке.

Импорт данных из всех Google Таблиц в списке с помощью формул

Друзья, если вы работаете и в Google Таблицах тоже, то вам может пригодиться эта статья, т.к. задача по сбору данных из списка разных таблиц - типовая. И это еще один пример того, насколько функция LAMBDA (доступная в Excel в Microsoft 365 и в Google Таблицах у всех пользователей) мощная и позволяет решать задачи с динамическим списком значений.

Дано: есть набор однотипных таблиц. Нужно загружать данные из всех таблиц в списке, при этом список может меняться – могут добавиться новые, могут уйти старые.

Решение: пробегаемся по массиву ссылок, и импортируем IMPORTRANGE данные из каждого, последовательно собирая в один массив с помощью REDUCE и LAMBDA. В статье — несколько вариантов формул.

https://teletype.in/@renat_shagabutdinov/IMPORT-LAMBDA

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

Файл с примером диаграммы!

Как сделать прогресс-бар в Excel с помощью диаграммы (ранее — через условное форматирование)

1 Выделяем две ячейки — сколько пройдено/сделано и сколько осталось.

2 Строим диаграмму (Alt+F1 или через ленту — "Вставка")

3 Выбираем/меняем тип диаграммы — нам нужна "линейчатая с накоплением" (Stacked Bar)

4 Заходим в настройки горизонтальной оси (выделяем ось, Ctrl+1) и устанавливаем максимум по этой оси = 1

5 Удаляем все границы, оси, названия и прочие элементы диаграммы. Меняем цвета, добавляем подписи данных — это по вкусу.

Как вам?

Первый раз за пределами издательства показываем (да собственно только сделали коллеги, спустя 55 писем в ветке, 10 вариантов, и, наверное, пару седых волос арт-директора, которому — и другим коллегам тоже — большая благодарность!)

Предзаказа пока нет, можно подписаться на электрическое письмо о старте продаж тут:

https://www.mann-ivanov-ferber.ru/books/magiia-tablic/

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

Первый аргумент — диапазон суммирования. А далее — попарно — диапазоны условий и условия.

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

Особенности функции:
— регистр в условиях не учитывается
— Важно, чтобы все диапазоны условий и диапазоны суммирования/усреднения были одинаковой размерности. Это могут быть и столбцы целиком (E:E), и диапазоны (E2:E40), и столбцы "умных" таблиц (Название_таблицы[Столбец]). Например, если один аргумент — это столбец целиком (D:D), то и другой должен быть в таком же формате (такого же размера — E:E, а не E2:E120, например).
— Условия можно вводить в кавычках внутри функции (как первое условие в примере) — любые текстовые значения в формулах Excel вводятся в кавычках. Либо ссылаться на ячейки, где хранится текст условия (второе условие в примере)
— В условиях можно использовать символы подстановки (* — любой текст любой длины, в том числе нулевой; ? — один любой символ). Например, "*сайт*" — это ячейка со словом "сайт" и любым другим текстом до и после, а не только ячейка со словом "сайт".
— В условиях можно использовать знаки сравнения (<, >, <=, >=, <> — "не равно"). Например, "<>Москва" — все, кроме ячеек, в которых текст "Москва". Позже напишем подробнее про условия со знаками сравнения!

Функция СУММЕСЛИМН / SUMIFS — не единственная для вычислений с условиями. В этой табличке все функции для вычисления суммы, среднего и количества: без условий, с условием и с несколькими условиями.

Функции с окончанием ЕСЛИМН / IFS появились в Excel 2007. До этого были только варианты с одним условием.

Табличка с примерами записи условий в функциях СУММЕСЛИМН / SUMIFS и других подобных функций.

Если вам нужно брать условие из ячейки и при этом добавлять к нему знаки сравнения, то приходится склеивать общее условие из двух частей:
— знаки сравнения, буду текстом, который "живет" в формуле, берутся в кавычки
— мы добавляем знак & (амперсанд), объединяющий текстовые строки в одну
— добавляем ссылку на ячейку.

Если вам нужно суммировать (усреднять, подсчитывать) данные за период, то условий будет два — на один и тот же столбец с датами. Одно — нижняя граница, второе — верхняя. Например, если в столбце B даты продаж, а нам нужны продажи за 2 квартал 2023, функция будет выглядеть так:

=СУММЕСЛИМН(диапазон суммирования; B:B; ">=01.04.2023"; B:B; "<=30.06.2023")

В функциях СУММЕСЛИМН / SUMIFS и других для вычислений с условиями диапазоны могут быть и строками, а не столбцами.

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

Условие будет в нашем примере такое:

количество*2023

У нас задано начало и окончание ячейки, а месяц между "количество" и годом может быть любой.

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

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

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

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

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

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

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

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

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

(*)

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

(????)

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

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

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

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

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

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

Если хотите научиться всем основным заклинаниям в сводных таблицах, приходите на практикум в июне, который мы с Лемуром проведем в МИФе. Будет три очень интенсивных учебных дня с домашкой!