Магия Excel

Двойной клик для копирования формул Если мы наводим курсор на правый нижний угол ячейки, и он превращается в черный крестик, можно щелкнуть дважды и формула скопируется до конца столбца (до последней строки с данными). Это сработает и не только для формул, но и для последовательности чисел или дат (допустим, у вас 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 там тоже работают.

СУММЕСЛИМН по выбранному товару (поиск строки с помощью ПОИСКПОЗ) На прошедшем в выходные тренинге по формулам обсуждали вот такую задачку: нужно просуммировать данные за выбранный год и только по штукам или деньгам. А таблица устроена так (плохо, но это жизнь), что в ней есть столбцы со штуками и деньгами попарно. Она не плоская. Как быть? Использовать СУММЕСЛИМН / 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)) (но в новых формулах столько функций, что можно и как-нибудь иначе вообще это решить ;) )

Магия таблиц: третье издание До меня доехали экземпляры третьего издания "Магии таблиц"! С выхода первого тиража книга набрала около 500 отзывов на Озоне и ВБ со средней оценкой примерно 4.9 / 5. Первый тираж — 2 500, второй — 3 000 (оба распроданы), третий — 3 000. Третье издание: — снова твердый переплет — обновления и исправления, актуальная информация на 2025 год — добавилось подробное руководство по сверхновым функциям GROUPBY и PIVOTBY — насколько я знаю, про них в книгах еще вообще больше никто не писал про них даже на английском, во всяком случае я покупаю почти все более-менее значимое про Excel на русском и английском и пока не видел; так или иначе информация — свежак дальше некуда (спасибо редакторам издательства, которые терпят бесконечные правки и дополнения — такая уж тема) — а самое главное — отзыв верховного экселье России Николая Павлова. Я учился по его книгам, статьям и тренингам. Спасибо Николаю! Вот-вот будет во всех магазинах, а в магазине издательства уже! Третье издание опознаете по собственно отзыву Николая, по отсутствию синего кругляша на обложке и по версии Excel 2024 на этой же самой обложке.

Функция ВЫБОР / CHOOSE — округляет Функция ВЫБОР устроена так: в первом аргументе число. А все последующие — это значения, которые нужно вернуть последовательно: Если это число — единица если это число — двойка и так далее =ВЫБОР(значение; что вернуть для значения = 1 ; что вернуть для значения = 2; ...) И она работает не только с целыми числами! Внимание на скриншот. Еще про применение ВЫБОРа: Другие функции как аргументы ВЫБОРа https://t.me/lemur_excel/500 ВЫБОР для получения названия месяца из даты в любом формате https://t.me/lemur_excel/305

Серая тема для черно-белой печати диаграмм и других объектов в Excel При подготовке книги к печати столкнулись с типовой проблемой: когда вы печатаете цветные диаграммы, все может сливаться, если печать не цветная. Слева сверху вы видите диаграмму, как она выглядела в Excel изначально (и как будет выглядеть этот фрагмент в электронной — цветной — книге), справа — то, что получается при ч/б печати. Как видите, совсем грустно. Гистограмма с накоплением выглядит как столбики одного цвета, словно и нет у нас двух товарных категорий. Поэтому лучше использовать специальную серую цветовую схему, в которой будут контрастные серые оттенки и будут видны отличия. Итак, если вы планируете печатать в ч/б ваш отчет: Вкладка "Разметка страницы" — группа "Темы" — Цвета — Серая Page Layout — Themes — Colors — Grayscale

У нас есть два списка, каждый из которых в отдельной ячейке. Нам нужно получить общие для обоих списков значения. С новыми функциями 365 это можно сделать формулой. Сначала разделяем каждый из списков на отдельные значения с помощью ТЕКСТРАЗД / TEXTSPLIT. Получаем два массива (для наглядности на скриншоте этот и промежуточные шаги показаны отдельно в ячейках, а вся формула видна в строке формул). Затем ищем весь первый список (каждое значение из него) во втором. Для этого подходит ПОИСКПОЗ / MATCH или ПОИСКПОЗX / XMATCH, отличаются они только тем, что у первой (старой) функции нужно задать третий аргумент = 0 для точного поиска. Она выдаст либо порядковые номера найденных значений, либо ошибки. Мы превратим с помощью ЕЧИСЛО / ISNUMBER числа в ИСТИНЫ, а ошибки в ЛОЖЬ. И по полученному массиву отфильтруем с помощью ФИЛЬТР / FILTER — получим только те значения из первого списка, для которых ИСТИНА, то есть которые были найдены ПОИСКПОЗОМ во втором списке. Останется склеить это с помощью ОБЪЕДИНИТЬ / TEXTJOIN. Функция LET позволяет задать переменные для списков и потом ссылаться на них, а не повторять вычисление с функцией ТЕКСТРАЗД. Также мы объявляем переменную для списка элементов, который получается в результате работы функции ФИЛЬТР. * Вот такие и десятки других задач будем решать в эти выходные на "живом" оффлайновом интенсиве в Москве. Конечно, в основном там будут формулы попроще (но и подобных будет немало!), и 75% будет пригодно для любых версий, а 95% — для Google Таблиц. Присодиняйтесь! https://shagabutdinov.ru/formulas-offline

Мышка или к...лавиатура?😸 Для перемещения в конец таблицы (диапазона) подойдет и то, и другое — выбирайте на ваш вкус: Ctrl + стрелка — перемещение в конец (до последней заполненной ячейки) в направлении стрелки; Двойной щелчок по границе ячейки — перемещение в соответствующем направлении (ловим курсор со стрелками во все стороны) Любое из этих действий с нажатой клавишей Shift — и получите не просто перемещение, а выделение ячеек!

Добавляем к дате день недели и выделяем выходные Допустим, мы с вами хотим видеть в каждой дате день недели — не "01.01.2025", как по умолчанию, а "01.01.2025 Ср". Для этого заходим в формат ячеек (Ctrl + 1) и добавляем к формату "ДДД" (DDD). Это краткое обозначение дня недели ("Вс"). Для полного ("Воскресенье") понадобится код "ДДДД" (DDDD). Ну а чтобы выделить цветом выходные (или другие дни) — воспользуемся условным форматированием (Conditional Formatting). Зададим правило с формулой, а в ней будем использовать функцию ДЕНЬНЕД / WEEKDAY. Она возвращает порядковый номер дня недели. Чтобы нумерация была привычной для нас с вами, добавьте второй аргумент, равный двойке: =ДЕНЬНЕД (ячейка с первой датой в диапазоне; 2) Тогда понедельнику будет соответствовать единица (иначе - воскресенью), вторнику — двойка и так далее. И остается добавить условие — день недели у нас должен быть больше 5 (то есть 6 или 7, суббота или воскресенье), чтобы ячейка заливалась цветом. Все показываем на видео!

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

Три правила структурирования данных в Excel из книги Data Modeling with Microsoft Excel: 1. В каждом столбце одно поле (например, имя, возраст, оклад или отдел) 2. Каждая строка – одна запись, одна операция — один элемент, в общем. 3. В каждой ячейки одно значение. Если там текст, то не должно быть чисел или других данных. Если вы вводите адрес, города и индексы должны быть в разных столбцах. Тогда вы сможете фильтровать и анализировать данные отдельно по городам и индексам. Короче говоря, вместо одной ячейки "Оплачено 19.08.2025 218572 руб." должно быть 2 (дата оплаты и сумма) или даже 4 (статус, дата, сумма, валюта). Но точно не все в одной 😊

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

👩‍🎓👨‍🎓Праздники — время отдыхать, конечно, но можно и заняться обучением, на которое не хватает время в обычные рабочие дни :) Что имею предложить по этому поводу: Магия новых функций 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]