Не показывать нули в ячейках: разные варианты

На уровне всей книги или отдельного листа: заходим в Параметры Excel — Дополнительно — выбираем лист или книгу — Показывать нули в ячейках, которые содержат нулевые ячейки
(этот вариант на скриншоте)

На уровне диапазона: выделяем диапазон — Ctrl + 1 — (все форматы) — задаем пользовательский формат, в котором третий формат (для нуля) оставляем пустым, например:

0;-0;

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

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

--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Ставили цели на этот год? Обратите внимание, что 13% года уже прошли 😈

Как это можно вычислить и визуализировать?

Используем функцию ДОЛЯГОДА / YEARFRAC. У нее два обязательных аргумента — две даты.

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

=ДОЛЯГОДА(ДАТА(ГОД(СЕГОДНЯ());1;1);СЕГОДНЯ())

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

--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Переключение дэшборда между днями и неделями — с помощью функции SEQUENCE

Итак, вы хотите создать простой дэшборд, в котором будете агрегировать данные по неделям или дням.
И при этом хотите легко переключать режим «недели / дни» (или изменение любого другого параметра), не залезая в формулы.

Статья и пример в Google Таблицах. Но в новом Excel такое тоже можно реализовать — и флажки, и функция SEQUENCE / ПОСЛЕД в наличии!

https://shagabutdinov.ru/blog/tpost/jbrryezom1-pereklyuchenie-deshborda-mezhdu-dnyami-i

--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Вывести все имена и соответствующие диапазоны на лист

Вот как можно сформировать табличку (диапазон) со списком всех или некоторых имен и их диапазонов:

Вкладка "Формулы" — Определенные имена — Использовать в формуле — Вставить имена
Formulas — Defined Names — Use in Formula — Paste Names

А зачем может пригодиться? Если вы применили имя в формуле, а потом удалили это имя (это можно сделать в диспетчере имен, Ctrl + F3), формула будет возвращать ошибку, хотя само имя в формуле останется.

То есть если у вас было =Выручка*Налог, то так оно и останется, "Налог" не будет заменен на ту ячейку, которая названа этим именем.

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

Можно ли отключить такое поведение? Чтобы при удалении имени оно исчезало из формулы, заменялось на диапазон?
Можно. Для конкретного листа:
Файл — Параметры — Дополнительно — Параметры совместимости с Lotus 1-2-3 — Преобразовывать формулы в формат Excel при вводе

--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Функция ЛИСТ / SHEET

Возвращает она порядковый номер (индекс) листа.
И этот номер может меняться. Он зависит от положения листа — они нумеруются от 1 до N, где N — количество листов в книге. Скрытые листы считаются.

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

=ЛИСТ()

С аргументом (ссылкой) будет возвращать номер листа, на который ссылка:

=ЛИСТ(Лист2!A1)

Если лист переместить, то его номер меняется. Соответственно, можно придумать формулу с проверкой. Например, такую, которая будет сигнализировать об ошибке, если лист с оглавлением передвинуть вправо (как на видео):

=ЕСЛИ(ЛИСТ()>1; "Ошибка!Переместите лист в начало книги";"Оглавление")

--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Гистограммы — простой и очень полезный инструмент для визуализации.

Вашему вниманию статья про них:
— Как работают гистограммы. Как их вставлять и что лучше с ними не делать
— Меняем минимум и максимум у гистограмм
— Задаем мин/макс формулой
— Убираем числа, показывая только гистограммы
— Применяем гистограммы в сводной, в том числе только к одному уровню

https://shagabutdinov.ru/tpost/7cfjpyxsj1-gistogrammi-v-excel-prostoi-instrument-v

Задача: посчитать стоимость (то есть перемножить цену и количество) с условием (то есть не по всем подряд строкам)

Если бы просто перемножить два столбца — цена и остатки — то все просто. Берем функцию СУММПРОИЗВ / SUMPRODUCT — она перемножает значения из нескольких массивов, а потом суммирует полученные произведения:

=СУММПРОИЗВ(Прайс[Цена];Прайс[Остатки])

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

Тогда добавим третий аргумент (массив) в функцию. С помощью функции НАЙТИ / FIND будем определять, есть ли искомый бренд в столбце "Название". Если функция выдаст ошибку (проверим это с помощью ЕОШИБКА / ISERROR), значит, бренда нет, а нам нужно, чтобы ошибки не было — так что мы будем превращать ИСТИНА (=ошибка есть, название не найдено) в ЛОЖЬ и наоборот. Таким образом, следующая конструкция выдаст ИСТИНА там, где искомое слово найдено:

НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название])))

Но это будет массив из логических значений ИСТИНА и ЛОЖЬ, и мы превратим его в единицы и нули, умножив на -1 дважды:

--НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название])))

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

=СУММПРОИЗВ(Прайс[Цена];Прайс[Остатки];--НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название]))))

---
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Код макроса

Макрос: создаем по отдельному файлу для каждого продукта/города/клиента (для каждого уникального значения в столбце)

Итак, вы хотите быстро получить отдельные файлы с данными по каждому значению в том или ином столбце. Забирайте этот макрос, добавляйте его в личную книгу макросов, добавляйте кнопку на панель быстрого доступа и теперь вы можете в любом файле выбрать заголовок любой таблицы/диапазона, нажать эту кнопку и произойдет следующее:

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

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

Как добавить макрос в личную книгу макросов, чтобы он был доступен при работе с любыми файлами Excel — читайте здесь. Сам макрос в соседнем сообщении (сохраняйте файл с макросом, заходите Alt+F11 в редактор макросов, добавляйте файл в личную книгу макросов PERSONAL.xlsb — для этого выберите Import File в контекстном меню по правой кнопке мыши)

В очень коротком видео со звуком показываю пример, как именно происходит магия.

Подробное руководство по функции FILTER / ФИЛЬТР — вашему вниманию

— Синтаксис функции. Как задаются условия в Excel и Google Spreadsheets. И-ИЛИ в условиях
— Условия на даты, текст, фрагменты текста, флажки
— Условия с функциями (например, данные только за понедельники)
— Фильтрация по списку
— ФИЛЬТРация с СОРТировкой
— Добавляем к результату фильтрации заголовки
— Фильтруем не все столбцы
— Фильтруем горизонтальные диапазоны
— FILTER в качестве аргументов других функций

🔗Google Таблица с примерами из статьи

📗Книга Excel с примерами из статьи

https://shagabutdinov.ru/blog/tpost/ko1p8i5rt1-funktsiya-filter-v-google-spreadsheets-i

_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥

Как избежать вставки ссылок в диалоговых окнах

Вот редактируете вы какую-то формулу или диапазон в окне условного форматирования или в диспетчере имен Excel.
И нажимаете стрелку влево или вправо на клавиатуре, чтобы... переместить курсор.
И в этот момент Excel вставляет ссылки на ячейки. А-а-а-а-а!

Как от этой гадости избавиться? Нажать F2.
И тогда стрелки будут перемещать курсор. При вводе формул в ячейках это тоже работает.

Опознать режим можно по надписи в левом нижнем углу (в строке состояния) — если там "Правка" (Edit), то можно смело нажимать на стрелки :)

Задача: генерируем коды вида «АБВ-00001» для переноса в Word и печати наклеек

Вашему вниманию новое видео на Sponsr.ru — оно бесплатное и открыто для всех, а не только для подписчиков. Это разбор небольшой задачки: как генерировать коды, в которых есть текстовая часть и идущие подряд числа.

Решение — и банальными и простыми формулами и новыми формулами версий 2021-2024 для задаваемого числа столбцов и строк. Попутно применяем пользовательский формат и функцию ТЕКСТ / TEXT.

Присылайте свои задачи в личные сообщения или по почте [email protected]. Без личных/коммерческих данных. Можно заменить на несколько строк со случайными. Если будет интересная задача — разберем ее в таком формате!

Сводные таблицы Excel: 10 приемов

10 сводно-табличных заклинаний под одной (виртуальной) обложкой — с видео или скриншотами:
— Удаление источника данных сводной
— Чередование строк в сводной
— Превращаем сводную в формулы
— Число уникальных элементов
— Группировка дат: анализируем сезонность
— И другое!

https://shagabutdinov.ru/tpost/mry5t8o211-svodnie-tablitsi-excel-10-priemov

Провожу серию вебинаров для крупной компании (ретейл, электроника) по Excel

И вот после последнего на данный момент вебинара прислали обратную связь с такими показателями!

А вот несколько отзывов сотрудников текстом:

Спасибо большое спикеру за интересную и доходчивую подачу материала.

Спасибо за умение доступно донести материал.

Спасибо большое за мастер-класс, было познавательно и полезно! Спасибо за понятное изложение материала

Мастер-класс шикарный и для новичков и для тех, кто подзабыл, и тех, кто уже многое знает!

Хотите организовать вебинар по Excel в своей компании — присылайте телеграмму (@r_shagabutdinov) или письмо: [email protected]!

Рассылка "Магия таблиц"

Совсем скоро подписчикам придет восьмой выпуск рассылки! Там будут новости (в частности, в Office в целом и в Excel подвезли новый режим 🌓), несколько табличных лайфхаков и всякое жизненное (про путешествия и бег).

Подписаться можно тут:
✉️https://shagabutdinov.ru/#subscription

А пока — вот предыдущие выпуски:
Первый. Новости и немного про графической слой Excel и про срезы — один из типов объектов, живущих на нем.

Второй. про ссылки на умные таблицы в Google Spreadsheets, линейчатую диаграмму для визуализации план-факта или чего-то подобного и немного личного — про путешествие на край света🥝.

Третий. Макрос для создания Word’овских документов по шаблону и лайфхаки для навигации по листам Excel. А также книжные итоги года.

Четвертый. Пара новостей об изменениях в Excel, секретный секрет про очень скрытые листы и пара слов про поездку в Оман.

Пятый. про новую функцию УРЕЗДИАПАЗОН, старую функцию ИНДЕКС (но про ее применение, которое может стать новостью для многих из вас) и про парочку нетабличных статей

Шестой. пачка лайфхаков из новой (для меня и, думаю, для вас, но не для автора 😊) книги Билла Джелена, про запуск нового формата — обучение по подписке и немного стоицизма

Седьмой. Про новые видео по табличным формулам, диаграммно-гистограмные приемы и про крутую книгу о силе оптимизма 😊

B2:ИНДЕКС(...) — ссылка на диапазон динамических размеров

Функция ИНДЕКС / INDEX весьма разносторонне развита. Умеет она в числе прочего возвращать ссылку на ячейку вместо ее содержимого. Если поставить ее после двоеточия. Например, так:

A1:ИНДЕКС(...)

В примере первая ячейка диапазона для расчета среднего — это B2 (то есть январь в каждом столбце), а последняя возвращается ИНДЕКСом — исходя из числа в ячейке A16.

=СРЗНАЧ(B2:ИНДЕКС(B2:B12;$A$16))

Теперь можно менять число в ячейке A16 и получать обновленный результат.

Почему не СМЕЩ / OFFSET, которая тоже может возвращать диапазон переменного размера? Ее тоже можно использовать в таких ситуациях. Но учитывайте, что она волатильная, в отличие от ИНДЕКСа. То есть пересчитывается при любом изменении в книге, а не при изменении ячеек, которые на нее влияют.

Ух какой отзыв на Озоне пришел на "Магию таблиц", не могу не поделиться! Сама книга почти закончилась, готовится третий тираж, но еще кое-где есть в наличии.

И на Озоне, и на WB число оценок перевалило за 200 со средней оценкой 5 и 4.9 соответственно🔥Спасибо всем читателям!

Эту книгу я бы подарил себе самому пятнадцать лет назад, если бы мог хлопнуть дверцей "Делориана" и метнуться в 2010-й на пару минут.
Работаю в EXCEL ежедневно и привык думать, что "знаю и умею" быстро и правильно.
В мою жизнь ворвались сводные таблицы, подсветив неожиданную истину: я НЕ умею работать в EXCEL.
Всегда кто-то и что-то подсказывал из-за плеча.
Так неправильно.
Пришло время вгрызться в базовые знания, поданные "от простого к сложному", системно, с иллюстрациями и примерами.
Я не "технарь", а человек "за компьютером". EXCEL и табличное мышление неизбежны.
Если вы как покупатель этой книги спросили бы моего совета по работе с книгой, я бы ответил:
Это - учебник, а не книга для послеобеденного чтения.
Закладывайте по два часа в день на эту книгу, имейте план усвоения материалов книги и открывайте эту книгу, только сидя перед компьютером с рабочим файлом EXCEL.
Сразу заводите EXCEL-конспект пройденных материалов.

Не бойтесь "нудятины". По зёрнышку. По шагу, по шажочку.
Не нужно изучать "впрок". Просто накапливайте базовые знания, лексику и EXCEL улыбнётся вам. Как и возможности быстрой работы с данными, их вариациями подачи, чтения, интерпретации.
Говорю это как стопроцентный "гуманитарий", которому жизнь и работа всё уже объяснили в деталях.
Если EXCEL неизбежен для вас, то эта книга - отличный старт.

Видеоурок: Функции для разделения текста: TEXTSPLIT и другие

Друзья, хочу поделиться с вами одним из уроков своего курса "Магия новых функций Excel" — про TEXTSPLIT и другие функции для разделения текстовых строк и извлечения фрагментов.

Полезного просмотра!

Весь курс можно найти здесь:
https://shagabutdinov.ru/magic-excel

Диаграммы в Excel: 11 приемов

— Быстрая вставка диаграммы
— 4 способа добавления новых данных
— Фильтрация данных на диаграмме в разных версиях Excel
— Изображения вместо столбиков
— Выделяем отдельные точки данных
— Выравниваем диаграммы
— Серая тема для черно-белой печати диаграмм
— Фоновое выделение периода на диаграмме
— И другие приемы!

Оффлайн-тренинг в Москве по Excel

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

Напишите в лс или на [email protected], если есть пожелания по темам? А также любые вопросы/пожелания. Я хочу рассмотреть темы форматирования (и немного визуализации), настройки интерфейса, основы и не только по сводным таблицам и формулы. Такой прожиточный максимум.
Ну а следующими постами — анонимный опрос по темам и формату в целом)