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

Допустим, мы хотим посмотреть на тех клиентов, которые принесли нам миллион.
В фильтре выбираем "Фильтр по значению" — "Первые 10..." — вводим сумму, которая нас интересует — меняем "элементов списка" на "Сумма" — нажимаем ОК. Получаем фильтрацию: только самые крупные клиенты, которые суммарно формируют нужную (введенную нами) сумму.

Если бы выбрали "наименьших", а не "наибольших" в диалоговом окне фильтра, то получили бы самых маленьких по сумме выручки клиентов, которые вместе принесли нам миллион.

Короткое видео с демонстрацией без звука.

Даты и время в Excel и Google Таблицах

Всем привет! Друзья, я обновил и дополнил статью про табличные даты. Она живет по этому адресу:

https://shagabutdinov.ru/date_time

А вот что вы найдете внутри:

— значения и форматы дат
— ввод текущих дат и времени как значения (и почему не всегда работают горячие клавиши)
— функции СЕГОДНЯ / TODAY и ТДАТА / NOW
— функция РАНЗДАТ / DATEDIF
— функции и формулы для получения отдельных параметров даты: день, месяц, номер недели, день недели цифрой и текстом, квартал (4 способами)
— вычисления с рабочими днями

Тот случай, когда почти вся информация на картинке :) Итак, если мы хотим форматировать отдельные слова / фрагменты в ячейке: переходим в режим редактирования (просто нажмите F2 или дважды кликните по ячейке), выделяем слово, форматируем (либо через Ctrl+1, либо через мини-панель форматирования около курсора, либо через вкладку "Главная" на ленте, либо сочетаниями клавиш, например, Ctrl+I для курсива)

Что можно добавить: если мы применили какое-то форматирование (например, полужирное начертание) к отдельному фрагменту в рамках ячейки, а потом активировали эту ячейку (а не фрагмент) и нажали Ctrl+B, применив такое начертание к ячейке целиком, то весь текст станет полужирным (включая тот, что уже был). То есть Excel не будет разбирать, какие фрагменты уже были полужирными. Еще раз нажмете Ctrl+B — весь текст в ячейке станет обычного, не полужирного начертания.
Но если было что-то еще — как в примере, подчеркнутый или зачеркнутый текст или курсив — это форматирование сохранится.

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

Допустим, мы с вами хотим видеть в каждой дате день недели — не "01.01.2025", как по умолчанию, а "01.01.2025 Ср".

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

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

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

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

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

Мышка или к...лавиатура?😸

Для перемещения в конец таблицы (диапазона) подойдет и то, и другое — выбирайте на ваш вкус:

Ctrl + стрелка — перемещение в конец (до последней заполненной ячейки) в направлении стрелки;
Двойной щелчок по границе ячейки — перемещение в соответствующем направлении (ловим курсор со стрелками во все стороны)

Любое из этих действий с нажатой клавишей Shift — и получите не просто перемещение, а выделение ячеек!

У нас есть два списка, каждый из которых в отдельной ячейке. Нам нужно получить общие для обоих списков значения.

С новыми функциями 365 это можно сделать формулой.

Сначала разделяем каждый из списков на отдельные значения с помощью ТЕКСТРАЗД / TEXTSPLIT. Получаем два массива (для наглядности на скриншоте этот и промежуточные шаги показаны отдельно в ячейках, а вся формула видна в строке формул).

Затем ищем весь первый список (каждое значение из него) во втором. Для этого подходит ПОИСКПОЗ / MATCH или ПОИСКПОЗX / XMATCH, отличаются они только тем, что у первой (старой) функции нужно задать третий аргумент = 0 для точного поиска.

Она выдаст либо порядковые номера найденных значений, либо ошибки. Мы превратим с помощью ЕЧИСЛО / ISNUMBER числа в ИСТИНЫ, а ошибки в ЛОЖЬ.

И по полученному массиву отфильтруем с помощью ФИЛЬТР / FILTER — получим только те значения из первого списка, для которых ИСТИНА, то есть которые были найдены ПОИСКПОЗОМ во втором списке.

Останется склеить это с помощью ОБЪЕДИНИТЬ / TEXTJOIN.

Функция LET позволяет задать переменные для списков и потом ссылаться на них, а не повторять вычисление с функцией ТЕКСТРАЗД. Также мы объявляем переменную для списка элементов, который получается в результате работы функции ФИЛЬТР.

*
Вот такие и десятки других задач будем решать в эти выходные на "живом" оффлайновом интенсиве в Москве. Конечно, в основном там будут формулы попроще (но и подобных будет немало!), и 75% будет пригодно для любых версий, а 95% — для Google Таблиц. Присодиняйтесь!
https://shagabutdinov.ru/formulas-offline

Серая тема для черно-белой печати диаграмм и других объектов в Excel

При подготовке книги к печати столкнулись с типовой проблемой: когда вы печатаете цветные диаграммы, все может сливаться, если печать не цветная.
Слева сверху вы видите диаграмму, как она выглядела в Excel изначально (и как будет выглядеть этот фрагмент в электронной — цветной — книге), справа — то, что получается при ч/б печати. Как видите, совсем грустно. Гистограмма с накоплением выглядит как столбики одного цвета, словно и нет у нас двух товарных категорий.

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

Итак, если вы планируете печатать в ч/б ваш отчет:
Вкладка "Разметка страницы" — группа "Темы" — Цвета — Серая
Page Layout — Themes — Colors — Grayscale

Функция ВЫБОР / CHOOSE — округляет

Функция ВЫБОР устроена так: в первом аргументе число. А все последующие — это значения, которые нужно вернуть последовательно:
Если это число — единица
если это число — двойка
и так далее

=ВЫБОР(значение; что вернуть для значения = 1 ; что вернуть для значения = 2; ...)

И она работает не только с целыми числами! Внимание на скриншот.

Еще про применение ВЫБОРа:

Другие функции как аргументы ВЫБОРа
https://t.iss.one/lemur_excel/500

ВЫБОР для получения названия месяца из даты в любом формате
https://t.iss.one/lemur_excel/305

СУММЕСЛИМН по выбранному товару (поиск строки с помощью ПОИСКПОЗ)

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

Как быть?
Использовать СУММЕСЛИМН / SUMIFS, благо в условиях функции можно использовать символ подстановки (* = любой текст). И благо диапазоны суммирования и условия могут быть горизонтальными, а не только вертикальными.

В нашем случае переменная часть нужных заголовков — это месяцы. Они меняются. А нужный показатель (штуки или деньги) — нет. Соответственно, если нам нужны все продажи в деньгах за 2024 год:

=СУММЕСЛИМН(2:2;1:1;"Деньги*2024")

Все продажи в штуках за все время:

=СУММЕСЛИМН(2:2;1:1;"Штуки*")

Но вторая строка здесь - это конкретный товар. И коллеги на тренинге задали правильный вопрос - а как суммировать по выбранному (в выпадающем списке) товару?
Решили так:
Находим строку с помощью ПОИСКПОЗ / MATCH с выбранным товаром:

ПОИСКПОЗ(нужный товар;список товаров;0)
ПОИСКПОЗ(A11;A1:A7;0)

Делаем ссылку на строку с этим номером - то есть добавляем двоеточие и еще раз этот же номер

ПОИСКПОЗ(A11;A1:A7;0) & ":" & ПОИСКПОЗ(A11;A1:A7;0)

Такая конструкция вернет 3:3, если выбранный товар в третьей строке.
Но это текст. Превратим его в активную ссылку с помощью ДВССЫЛ / INDIRECT и засунем в СУММЕСЛИМН:

=СУММЕСЛИМН(ДВССЫЛ(ПОИСКПОЗ(A11;A1:A7;0) & ":" & ПОИСКПОЗ(A11;A1:A7;0));1:1;B10)

В новой версии можно с помощью LET один раз найти номер строки, а не вычислять его дважды:

=LET(строка;ПОИСКПОЗX(A11;A1:A7);
СУММЕСЛИМН(ДВССЫЛ(строка&":"&строка); 1:1 ;B10))

(но в новых формулах столько функций, что можно и как-нибудь иначе вообще это решить ;) )

Двойной клик для копирования формул

Если мы наводим курсор на правый нижний угол ячейки, и он превращается в черный крестик, можно щелкнуть дважды и формула скопируется до конца столбца (до последней строки с данными).

Это сработает и не только для формул, но и для последовательности чисел или дат (допустим, у вас 1 и 2 в первых двух ячейках — двойной щелчок продолжит ряд, как если бы вы использовали инструмент "Прогрессия" или тянули руками за уголок ячейки).

До Excel 2010 магия ломалась на пустых ячейках в соседнем столбце, но потом починили: если даже есть пропуски в столбце слева, формула протянется до конца.

Какие еще варианты?

Можно использовать Таблицы (Tables) — Ctrl+T или Ctrl+L и вперед — формулы в Таблицах автоматически копируются на все строки.

Еще можно использовать сочетание Ctrl + D для заполнения вниз. Но для этого придется сначала выделить все ячейки в столбце. То есть пойти в конец диапазона (Ctrl + End), потом выделить столбец до первой ячейки (Ctrl + Shift + ↑). Кстати, Ctrl + R — это заполнение вправо, тоже может пригодиться.

P.S. Google Таблицы сами предлагают протянуть формулу в таких ситуациях — просто можно нажать галочку или Ctrl + Enter. Двойной щелчок и Ctrl + D / Ctrl + R там тоже работают.

Новый урок в курсе "Магия новых функций Excel"

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

Во-первых, это демонстрация именованных функций и LAMBDA со вспомогательными функциями в Google Таблицах, а во-вторых, дополнительные примеры:
— LAMBDA и MAP: формируем расписание одной формулой
— MAP и BYCOL: два цикла — по столбцам и значениям в рамках каждого столбца
— Создаем аналог Excel-функции GROUPBY для построения «сводной» с текстом в области значений.

А скоро будут и другие обновления этого курса!

https://shagabutdinov.ru/magic-excel

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

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

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

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

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

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

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

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

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

Оглавление макросом можно найти тут:
https://t.iss.one/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 любых символа), нужно указать четыре знака вопроса в скобках:

(????)

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