Нумеруем только видимые строки

Если мы хотим, чтобы при скрытии строк номера пунктов обновлялись автоматически (то есть нумеровались видимые в моменте строки, а не все, что есть на листе) — нужно использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL.

Ибо она умеет обрабатывать только видимые (не скрытые) ячейки.

В нашем случае мы считаем значения, а не суммируем или что-то еще, то есть нужна функция СЧЁТЗ / COUNTA. Внутри SUBTOTAL ее код — 103 (у SUBTOTAL тип вычисления указывается в первом аргументе).

А что считаем? Для каждой строки мы считаем, сколько значений есть в видимых строках с начала таблицы (у нас это B2) до текущей строки.

Чтобы сформировать ссылку на диапазон от начала до текущей строки, сделаем так:

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;$B$2:B2)

$B$2:B2 — начало всегда в B2, а конец диапазона в строке с формулой. Такую ссылку не задать через F4, придется попотеть и ввести доллары вручную.

_ _ _
Ну а заодно напоминание про 🔥 клавиши:
Ctrl + 9 — скрыть строку
Ctrl + Shift + 9 — отобразить скрытые строки

Магия табличных формул: видеоуроки по подписке

Друзья, на Sponsr уже более 40 видеоуроков (10-30 минут) с исходными и готовыми файлами!

Здесь полный список всего, что уже есть:

Формулы
Вступительное видео. Книги по табличным формулам
1.1 Формулы, ссылки и имена
1.2. Ссылки на таблицы, листы и книги
1.3 Копирование и перенос формул. Скрытие формул
1.4 Операторы в формулах. Приоритет операторов
1.5 Функции и их аргументы. Автосумма. Волатильные функции
1.6 Ошибки в формулах
Задача: генерируем коды вида «АБВ-00001» для переноса в Word и печати наклеек
1.7 Формулы в Google Таблицах

Даты
2.1 Дата и время: основы. Текущие дата и время: вручную и функциями
2.2 Функция ДАТА / DATE. Формируем дату из элементов, создаем последовательность дат, «собираем» дату из текста
2.3 Извлекаем элементы из даты: месяц и год, номер недели, день недели номером и текстом, квартал
2.4 Функции для вычислений с рабочими днями. Производственный календарь
2.5 Время. Текущее время, извлечение часов, форматирование времени и продолжительности

Тексты
3.1 Объединение текста. Функция ТЕКСТ. Мгновенное заполнение
3.2 Замена текста: окно «Найти и заменить» и функции ПОДСТАВИТЬ, ЗАМЕНИТЬ
3.3 Извлекаем фрагменты из текста. Мгновенное заполнение, текст по столбцам и «старые» формулы
3.4 Разделяем текст формулами. Функции ТЕКСТРАЗД / TEXTSPLIT и SPLIT
3.5 Регулярные выражения

Логика и условия
4.1 Логические значения и флажки
4.2 Функции И (AND), ИЛИ (OR). Проектная диаграмма
4.3 Функции ЕСЛИМН / IFS, ПЕРЕКЛЮЧ / SWITCH, ВЫБОР / CHOOSE
4.4 Условное форматирование с формулами
4.5 Проверка данных с формулами
4.6 СУММЕСЛИМН / SUMIFS и компания
4.7 Элементы управления и СУММЕСЛИМН. Флажок и полоса прокрутки
4.8 Функция СУММПРОИЗВ / SUMPRODUCT

А впереди формулы массива, LAMBDA, Google Таблицы, пользовательские функции и многое другое.

Есть еще только 4 места со скидкой за 390 рублей в месяц. При подписке на год еще + скидка. А еще на Sponsr можно приходить в "личку" с табличными вопросами. Подписываться можно по ссылке:
https://sponsr.ru/excel_magic/

Автосумма: одним движением суммы по всем столбцам/месяцам.

Сочетание клавиш Alt + = позволяет получить сумму быстро, не вводя руками функцию СУММ / SUM.
Если выделить ячейку под столбцом с числами и нажать Alt + =, то получим сумму по этому столбцу (одну функцию СУММ).
Уточняем: речь про "просто Alt", то есть левый Alt. Правый Alt заменяет сочетание Ctrl+Alt и в сочетании с плюсом-минусом будет менять масштаб листа.

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

А это чужие онлайн-курсы по Excel / Таблицам, которые я рекомендую

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

Планета Excel (Николай Павлов)
Бюджетно, очень качественно (как всегда у Николая), курсы по базовым темам. И видео, и тексты.

Xelplus
Курсы по всем темам от основ до Google Таблиц и Power Query, Python, Power Pivot.
Очень хороший профессиональный монтаж уроков, много дополнительных материалов. Недешево, но понятно, за что платишь.

Язык M — Михаил Музыкин (по подписке на Sponsr)
Высший полет мастерства и погружение в самые глубины языка M. Для тех, кто хочет освоить всю магию Power Query. Смотря иногда чисто для удовольствия, потому что не все может сразу пригодиться на практике (если у вас нет разнообразных и сложных задач в PQ), но настолько все интересно и с нюансами.
Даже на английском, думаю, непросто будет найти аналоги такого уровня.
Есть книга Power Query и язык M, хорошая, объемная и подробная, но местами напоминающая справку и не всегда там есть мостики с практикой.

Бен Коллинс — курсы по Google Таблицам
Есть бесплатные, в том числе по формулам и основам скриптов! Все курсы хороши, со всеми гуглотаблицами-примерами.

Ну а все хорошие книги по таблицам тут. Думаю, что учиться можно и нужно и на практике, и по книгам, и по курсам, кому-то больше подходит одно, кому-то другое, здорово, если получается комбинировать.

Вычисления с рабочими днями
Длительность: 5 мин

Функции РАБДЕНЬ, ЧИСТРАБДНИ, ЧИСТРАБДНИ.МЕЖД: считаем, какая дата наступит через N рабочих дней, в том числе с учетом праздников.

А еще считаем число рабочих дней (в том числе с нестандартной рабочей неделей) или определенных дней (допустим, сред) в периоде.

Этот и пара десятков других видеоуроков доступны по ссылке:
https://shagabutdinov.ru/video

Расширенный фильтр с формулами
Длительность: 5 мин

В расширенном фильтре можно использовать формулы в условиях! Для чего это может пригодиться? Например, вам нужно получить выборку по большому количеству комбинаций — N городов, X продуктов и так далее. Для каждой комбинации понадобится отдельная строка с условием, если подходить к вопросу обычным образом.

Но мы сделаем формулу, которая будет определять, есть ли то или иное значение в нашем списке.

Файл с примером — по ссылке

Это один из десятков уроков курса по подписке на Sponsr. Все уроки доступны по ссылке (каждую неделю — новые):
https://sponsr.ru/excel_magic/

Гистограммы с текстом

Как реализовать такую историю?

Сначала ссылаемся на данные (числа).
Можно сослаться на первое число (=B2) и "протянуть".

А можно скопировать весь диапазон с продажами и вставить как связь (Ctrl + Alt + V — Вставить связь или правая кнопка мыши — вставить ссылку)

Далее вставляем гистограммы и убираем числа из ячеек (тут подробнее про это и вообще про гистограммы)

Затем копируем диапазон с подписями (городами), правая кнопка мыши — Специальная вставка — Связанный рисунок. И тащим вставленный рисунок поверх гистограмм.

Все будет обновляться при изменении данных / сортировке!

Скорая табличная помощь для компаний

Когда я провожу корпоративные вебинары по Excel и/или Google Таблицам, слушатели — сотрудники клиента — могут обращаться (и делают это) с вопросами не только во время и сразу после вебинаров, но и между встречами и после обучения.

Для некоторых это становится самой полезной составляющей: не у всех есть возможность прослушать все вебинары или участвовать в очном обучении. А задачу решить или сделать отчет нужно — и обычно такие вопросы удается быстро закрыть.

Поэтому я решил, что такой формат поддержки может быть и отдельно от вебинаров и длинных программ обучения: поддержка сотрудников компании, когда каждый может обратиться ко мне с вопросами и за помощью по теме:
- Оптимизация и оформление отчетов, визуализация, форматирование
- Формулы
- Сводные таблицы
- Автоматизация

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

Хотите попробовать в вашей компании? Тест-драйв бесплатно.
Напишите мне письмо на [email protected] или оставьте заявку на сайте! Обсудим все детали.

С днем знаний!

В этот день, как и в любой другой, кот Лемур будет благодарен, если вы купите себе или подарите кому-то книгу "Магия таблиц", а если уже покупали — напишете честный и искренний отзыв там, где покупали или тут в комментариях ❤️

У книги на данный момент:
около 360 отзывов на Озоне (суммарно у разных изданий и форматов) — средняя оценка 4.9-5.0 / 5
около 220 на ВБ, средняя оценка 5 / 5

Новый тираж — это 528 страниц в твердом переплете. Есть все самое свежее, вплоть до PIVOTBY и GROUPBY (я не встречал этих и некоторых других самых последних тем пока даже в книгах на английском, хотя покупаю и смотрю более-менее все)

Свежие отзывы:

Отличное руководство. Приобретала в твердой обложке. Книга была упакована в картонную упаковку надёжно.

Отличная книга. Изложено доступно и понятно

Эта книга-просто чудо! Очень много полезной информации в супер доступном объяснении. Мне, как новичку, было все предельно понятно. Рекомендую

Отличное пособие. Спасибо большое вам.

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

Хорошее качество печати, бумага плотная. Информация в книге будет полезна и как учебная, и как справочная, содержит необходимое и достаточное количество тем для тех, кто часто работает с эксель и гугл таблицами, изложение понятное и корректное

А вот отзыв Николая Павлова, автора проекта "Планета Excel" и замечательных книг:

Уникальность этой книги в том, что впервые под одной обложкой собрана коллекция наиболее эффективных и полезных приемов и функций сразу из Microsoft Excel и Google Sheets – самых мощных, на сегодняшний день, инструментов для работы с электронными таблицами. Это сравнение двух программ, показ их различий и общих возможностей позволит пользователям гибко переключаться на нужный инструмент при необходимости. А это дорогого стоит.

Купить: Озон, ВБ, Литрес (электро), МИФ (бумага и электро)

Вот подключились вы к каким-то данным в Power Query в Excel, наделали манипуляций, и потом вам нужно удалить ряд столбцов, а оставшиеся переупорядочить, сделать другой порядок, отличный от источника данных.

Зажимаем Ctrl, выделяем столбцы, которые нужно оставить, и делаем это в том порядке, в каком нужно, а затем правая кнопка мыши и «Удалить другие столбцы».

Невыделенные столбцы будут удалены, а выделенные останутся и будут сразу в том порядке, в каком вы их выделяли.

Топ-N значений в сводной таблице
Длительность: 7 мин

Вы хотите показать в сводной самые крупные филиалы/сделки/клиенты, выбрав N самых больших/малых значений.
В строке итогов будет сумма выбранных N значений, а не вся сумма по полю. А как показать всю? А можно ли выделить остальных в отдельную строку (все, кроме выбранных N самых больших)? Смотрим!

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

Еще десятки бесплатных видеоуроков тут:
https://shagabutdinov.ru/video

Excel Cookbook

Вот такая новинка от издательства O'Reilly

Увы, рекомендовать ее не могу: вроде бы очень много тем и идей, от ссылок на ячейки до LAMBDA, VBA и пакета анализа. И объем приличный — почти 550 страниц.

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

После прочтения всей книги не отметил для себя ничего нового, и такое бывает редко с хорошими книгами. У того же Билла Джелена все время будет что-то новенькое в любой книге.

А цена высокая (40 долларов за киндл, в России на Озоне более 3000 рублей бумажная)

Про хорошие книги писал здесь.

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

Варианты в видео:
— Новые формулы в Excel
— Power Query
— Новые и старые формулы в Google Таблицах

Это же видео на Youtube
Десятки других видеоуроков