Telegram Group Search
This media is not supported in your browser
VIEW IN TELEGRAM
Фильтр в сводной таблице по сумме

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

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

Короткое видео с демонстрацией без звука.
👩‍🎓👨‍🎓Праздники — время отдыхать, конечно, но можно и заняться обучением, на которое не хватает время в обычные рабочие дни :)

Что имею предложить по этому поводу:

Магия новых функций Excel. Массивы, регулярные выражения и многое другое
15 видео + текстовые материалы, исходные и готовые файлы в формате XLSX
Для счастливых обладателей Microsoft 365 с новыми функциями и для пользователей Google Таблиц (ибо там есть почти все функции, бесплатно и без но с регистрацией аккаунта, конечно)
🔗https://shagabutdinov.ru/magic-excel

Сводные таблицы Google Spreadsheets. От основ и нюансов до построения сводных с помощью QUERY и LAMBDA
20 видео, исходные и готовые файлы в формате Google Таблиц
Для начинающих и продолжающих пользователей Google Таблиц и переходящих туда из Excel. Все про сводные Google, от основ до нюансов вокруг сводных (от подготовки данных до визуализации и построения "сводных" формулами)
Сводные — что в Excel, что в Google — зачастую могут решать до 80-90% ваших задач по анализу данных :)

🔗https://shagabutdinov.ru/pivot_google

Есть вопросы? [email protected]
Please open Telegram to view this post
VIEW IN TELEGRAM
Даты и время в Excel и Google Таблицах

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

https://shagabutdinov.ru/date_time

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

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

1. В каждом столбце одно поле (например, имя, возраст, оклад или отдел)
2. Каждая строка – одна запись, одна операция — один элемент, в общем.
3. В каждой ячейки одно значение. Если там текст, то не должно быть чисел или других данных. Если вы вводите адрес, города и индексы должны быть в разных столбцах. Тогда вы сможете фильтровать и анализировать данные отдельно по городам и индексам.

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

Что можно добавить: если мы применили какое-то форматирование (например, полужирное начертание) к отдельному фрагменту в рамках ячейки, а потом активировали эту ячейку (а не фрагмент) и нажали Ctrl+B, применив такое начертание к ячейке целиком, то весь текст станет полужирным (включая тот, что уже был). То есть Excel не будет разбирать, какие фрагменты уже были полужирными. Еще раз нажмете Ctrl+B — весь текст в ячейке станет обычного, не полужирного начертания.
Но если было что-то еще — как в примере, подчеркнутый или зачеркнутый текст или курсив — это форматирование сохранится.
This media is not supported in your browser
VIEW IN TELEGRAM
Добавляем к дате день недели и выделяем выходные

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

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

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

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

И остается добавить условие — день недели у нас должен быть больше 5 (то есть 6 или 7, суббота или воскресенье), чтобы ячейка заливалась цветом.
Все показываем на видео!
This media is not supported in your browser
VIEW IN TELEGRAM
Мышка или к...лавиатура?😸

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

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
Please open Telegram to view this post
VIEW IN TELEGRAM
Магия таблиц: третье издание

До меня доехали экземпляры третьего издания "Магии таблиц"! С выхода первого тиража книга набрала около 500 отзывов на Озоне и ВБ со средней оценкой примерно 4.9 / 5.

Первый тираж — 2 500, второй — 3 000 (оба распроданы), третий — 3 000.

Третье издание:
— снова твердый переплет
— обновления и исправления, актуальная информация на 2025 год
— добавилось подробное руководство по сверхновым функциям GROUPBY и PIVOTBY — насколько я знаю, про них в книгах еще вообще больше никто не писал про них даже на английском, во всяком случае я покупаю почти все более-менее значимое про Excel на русском и английском и пока не видел; так или иначе информация — свежак дальше некуда (спасибо редакторам издательства, которые терпят бесконечные правки и дополнения — такая уж тема)
— а самое главное — отзыв верховного экселье России Николая Павлова. Я учился по его книгам, статьям и тренингам. Спасибо Николаю!

Вот-вот будет во всех магазинах, а в магазине издательства уже!
Третье издание опознаете по собственно отзыву Николая, по отсутствию синего кругляша на обложке и по версии Excel 2024 на этой же самой обложке.
Please open Telegram to view this post
VIEW IN TELEGRAM
Двойной клик для копирования формул

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

Это сработает и не только для формул, но и для последовательности чисел или дат (допустим, у вас 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
Please open Telegram to view this post
VIEW IN TELEGRAM
Розыгрыши в 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
Издательство
This media is not supported in your browser
VIEW IN TELEGRAM
Расширенный фильтр: быстро фильтруем по списку значений

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

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

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

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

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

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

Подробнее про них и про правила записи условий тут:
https://teletype.in/@renat_shagabutdinov/4lVaI_Pj7
2025/06/11 09:12:24
Back to Top
HTML Embed Code: