Магия Excel

В функциях СУММЕСЛИМН / SUMIFS и других для вычислений с условиями диапазоны могут быть и строками, а не столбцами. Например, если нам нужно суммировать не все столбцы, а только те, в которых есть слово "количество" и год 2023 (то есть продажи в штуках, а не деньгах, и за 2023 год, а не другие) — диапазоном условий будет строка с заголовками. А диапазоном суммирования — текущая строка с числовыми данными. Условие будет в нашем примере такое: количество*2023 У нас задано начало и окончание ячейки, а месяц между "количество" и годом может быть любой. Не забудьте закрепить в такой ситуации строку с заголовками, сделав ее абсолютной (F4) — потому что при протягивании формулы вниз строка для суммирования будет меняться, и это необходимо, а вот заголовки для проверки условий всегда находятся в одной и той же строке.

Табличка с примерами записи условий в функциях СУММЕСЛИМН / SUMIFS и других подобных функций. Если вам нужно брать условие из ячейки и при этом добавлять к нему знаки сравнения, то приходится склеивать общее условие из двух частей: — знаки сравнения, буду текстом, который "живет" в формуле, берутся в кавычки — мы добавляем знак & (амперсанд), объединяющий текстовые строки в одну — добавляем ссылку на ячейку. Если вам нужно суммировать (усреднять, подсчитывать) данные за период, то условий будет два — на один и тот же столбец с датами. Одно — нижняя граница, второе — верхняя. Например, если в столбце B даты продаж, а нам нужны продажи за 2 квартал 2023, функция будет выглядеть так: =СУММЕСЛИМН(диапазон суммирования; B:B; ">=01.04.2023"; B:B; "<=30.06.2023")

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

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

Как вам? Первый раз за пределами издательства показываем (да собственно только сделали коллеги, спустя 55 писем в ветке, 10 вариантов, и, наверное, пару седых волос арт-директора, которому — и другим коллегам тоже — большая благодарность!) Предзаказа пока нет, можно подписаться на электрическое письмо о старте продаж тут: https://www.mann-ivanov-ferber.ru/books/magiia-tablic/

Как сделать прогресс-бар в Excel с помощью диаграммы (ранее — через условное форматирование) 1 Выделяем две ячейки — сколько пройдено/сделано и сколько осталось. 2 Строим диаграмму (Alt+F1 или через ленту — "Вставка") 3 Выбираем/меняем тип диаграммы — нам нужна "линейчатая с накоплением" (Stacked Bar) 4 Заходим в настройки горизонтальной оси (выделяем ось, Ctrl+1) и устанавливаем максимум по этой оси = 1 5 Удаляем все границы, оси, названия и прочие элементы диаграммы. Меняем цвета, добавляем подписи данных — это по вкусу.

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

Импорт данных из всех Google Таблиц в списке с помощью формул Друзья, если вы работаете и в Google Таблицах тоже, то вам может пригодиться эта статья, т.к. задача по сбору данных из списка разных таблиц - типовая. И это еще один пример того, насколько функция LAMBDA (доступная в Excel в Microsoft 365 и в Google Таблицах у всех пользователей) мощная и позволяет решать задачи с динамическим списком значений. Дано: есть набор однотипных таблиц. Нужно загружать данные из всех таблиц в списке, при этом список может меняться – могут добавиться новые, могут уйти старые. Решение: пробегаемся по массиву ссылок, и импортируем IMPORTRANGE данные из каждого, последовательно собирая в один массив с помощью REDUCE и LAMBDA. В статье — несколько вариантов формул. https://teletype.in/@renat_shagabutdinov/IMPORT-LAMBDA Смотрите также: Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический)

Разрешаем вводить в диапазоне только формулы Это тоже проверка данных с использованием в правиле... формулы! Формула будет состоять из единственной функции ЕФОРМУЛА / ISFORMULA, которая проверяет, является ли содержимое ячейки формулой (и если да, возвращает ИСТИНА / TRUE - в случае с проверкой это означает, что именно такое содержимое допускается). Выделяем диапазон, открываем проверку данных и выбираем правило с формулой: Данные → Проверка данных → Тип данных: Другой → Формула Data → Data Validation → Allow: Custom → Formula Формула будет такой: =ЕФОРМУЛА(первая ячейка диапазона с проверкой) Теперь в этом диапазоне при попытке ввода значений, а не формул, будет появляться сообщение об ошибке.

Как разрешить вводить в диапазоне только рабочие дни? Для этого понадобится проверка данных с формулой. Данные → Проверка данных → Тип данных: Другой Data → Data Validation → Allow: Custom → Formula Формула должна возвращать ИСТИНА (TRUE), то есть условие должно выполняться. Иначе проверка данных будет выдавать ошибку или предупреждение (зависит от настроек в разделе «Сообщение об ошибке», Error Alert). В формуле мы ссылаемся на первую ячейку диапазона (представляйте, что ваша формула "протягивается", копируется, на остальные, чтобы правильно расставить абсолютные и относительные ссылки в ней). В нашем случае в формуле будем использовать функцию ДЕНЬНЕД / WEEKDAY. Первый аргумент — дата, а второй — тип нумерации, где 2 = неделя начинается с понедельника. =ДЕНЬНЕД(первая ячейка диапазона; 2) < 6 Такая формула будет возвращать ИСТИНА / TRUE при дне недели от 1 до 5.

Видеоурок: "старые" и новые формулы массивов Друзья, если хотите разобраться, как работают формулы массивов в Excel до 2019 включительно и какая революция произошла в 2019 году (с версии Excel 2021 и в Microsoft 365) — вашему вниманию видео по теме. Это один из 55 уроков курса "Магия Excel" в МИФе. Приходите учиться, будем рады!

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