Магия Excel

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

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

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

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

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

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

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

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

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

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

Откройте в себе внимательного аналитика и научитесь превращать обычные таблицы в мощный инструмент для анализа и автоматизации на бесплатном курсе-симуляторе «Excel: простые шаги для оптимизации работы с данными».

Курс подойдёт новичкам и тем, кто уже работает с данными: финансистам, маркетологам и продактам. Вы узнаете, как с помощью формул, сводных таблиц и прочих фишек Excel сделать свою работу проще и эффективнее.

🎁 После бесплатного обучения вы получите в подарок карьерную консультацию и скидку до 50% на курс-профессию "Аналитик данных", чтобы улучшить свои навыки под руководством экспертов.

Присоединяйтесь бесплатно в любое время и убедитесь, что Excel — это не просто таблички.

Реклама. ООО "Нетология". Erid 2VSb5xnHjVJ

Аргументами функции ВЫБОР / CHOOSE могут и... другие функции!

Можно сделать выпадающий список, в котором будет несколько вариантов вычислений.
В нашем простом примере — в ячейке M8 список (проверка данных) из диапазона M4:M6 — там среднее, сумма и количество.

Функция ПОИСКПОЗ / MATCH подскажет нам, каким по порядку в списке идет выбранный тип вычисления. Полученное число будет первым аргументом функции ВЫБОР. А последующие аргументы — функции.

Например, если пользователь выбрал "сумму", ПОИСКПОЗ будет возвращать 2. А значит, ВЫБОР выдаст второй элемент из списка. Это функция СУММ. Получается, что мы выбираем, какую формулу вычислять на основе значения из выпадающего списка. Простой вариант добавления интерактива в отчеты!

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

⏺Устали от постоянных изменений в налоговом законодательстве?

⏺Запутались в тонкостях
бухгалтерского учета?

⏺Хотите быть в курсе актуальной и проверенной информации?

Если вы бухгалтер, налоговый консультант, предприниматель или руководитель компании

🔥У нас для вас есть хорошая новость!

👉 Учебный центр "Что делать Квалификация" запустил
полезный телеграм-канал.

В канале вы найдёте:

✅Актуальную информацию о налоговых и бухгалтерских изменениях: будете в курсе важных поправок

✅Полезные рубрики с ответами на вопросы: задавайте вопросы и получайте ответы от сотрудников и лекторов Центра

✅Лайфхаки работы в 1С

✅Подробные бухгалтерские задачки с разборами

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

Теперь не нужно искать информацию по всему интернету!

👉 Подписывайтесь на канал
"Что делать Квалификация" и получайте всю необходимую информацию в одном месте
⤵️⤵️⤵️
https://t.me/+HUeahiQ3av1kZGVi

Реклама ООО "Что делать Квалификация", ИНН 7714349600, erid: 2VtzquqnUnz

Визуализируй это: курс по визуализации данных в Excel

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

Курс записан именно в Excel. В текстовом формате будут дополнения: что доступно и как работает в Google Таблицах / российском Р7-Офис. На скриншотах несколько примеров того, что внутри. На странице курса можно посмотреть один из уроков.
И там же купить:
https://www.mann-ivanov-ferber.ru/courses/visualization/

Как заполнить пустые ячейки (вниз, то есть значениями сверху)?

Закрепляем верхнюю строку, если еще не)
Идем в конец диапазона (Ctrl+End или Ctrl+стрелка вниз по заполненному столбцу)

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

Нажимаем F5 и далее «Выделить» — «пустые ячейки»

Вводим формулу — нажимаем «равно» и на стрелку вверх
То есть ссылаемся на ячейку сверху (это R[-1]C в нотации R1C1 или =A2 в привычном стиле A1 — для формулы в ячейке A3)

И нажатием Ctrl+Enter вводим формулы во все выделенные ячейки (а это, вспомним, только пустые!)

То есть в каждой пустой теперь ссылаемся на ячейку сверху.

Дальше можно формулы скопировать и вставить как значения (Ctrl+C и Ctrl+Shift+V / Ctrl+Alt+V)

Алгоритм в коротком видео (без звука).

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

Магия новых функций Excel: революция в формулах 🔥
Лемур напоминает: уже 18 ноября цена на новый курс вырастет до 2 300. Пока можно успеть купить за 890!

Внутри:
14 видео от 5 до 25 минут с качественным монтажом
Дополнительные текстовые материалы
Файлы со всеми примерами и формулами — исходные и готовые.

Покупать тут — и там же подробная программа и примеры:
https://shagabutdinov.ru/magic-excel

Контрольное значение: отслеживаем значения ячеек в любом месте

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

Лента инструментов:
Формулы — Окно контрольного значения
Formulas — Watch Window

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

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

Делаем кнопку группировки более наглядной

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

И если строка скрыта, то функция вернет 0. Тогда можно выдать текст в духе "Подробнее" или "Раскрыть". Если не 0 — значит, строка ниже видима (и в ней что-то есть), выдаем другую надпись ("Скрыть").

=ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;A3)=0; "⬇️Подробнее"; "⬆️Скрыть")

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

Точку данных в диаграмме (например, в линейчатой) можно заменить изображением

Для этого скопируйте изображение (Ctrl + C), выделите диаграмму, выделите нужный элемент (просто щелкните еще раз после выделения диаграммы на нужный элемент — вы поймете, что он выделен, когда круглые маркеры по углам останутся только у этого столбика).

И Ctrl + V — вставляем изображение.

После этого можно зайти в панель форматирования (Ctrl + 1), чтобы уменьшить боковой зазор между столбиками. Тогда они станут шире/выше.

И наконец, чтобы у вас не был один очень длинный кот или утка или что там у вас было на вставленной картинке, выберите в параметрах (Параметры ряда — Границы и заливка) вариант "размножить".

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

Отличия по строкам
Вы хотите быстро выделить цветом ячейки, в которых план отличается от факта (один столбец от другого — в общем случае)?

1 Выделяем столбцы (можно быстро быстро выделить их сочетанием Ctrl + Shift + стрелка вниз)
2 Ctrl + G —> Выделить (Special)
3 Отличия по строкам (Row differences)
4 Красим выделенные ячейки нужным цветом. Готово!

Смотрим на GIF (без звука)

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

Магия новых функций Excel: революция в формулах 🔥

Друзья, мы с Лемуром рады анонсировать новый мини-курс, посвященный новым же функциям Excel (и — по большей части — Google Таблиц).

Это функции, которые могут решать задачи, ради которых раньше пришлось бы писать макросы или создавать запросы в Power Query!

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

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

В честь запуска новой версии сайта и этого курса — зверская цена от Лемура — 890 рублей! 🔥
Никаких уловок и вечных скидок не будет: 18 ноября цена вырастет до 2300 и больше уже никогда не вернется к старой.

Внутри:
14 видео от 5 до 25 минут с качественным монтажом
Дополнительные текстовые материалы
Файлы со всеми примерами и формулами — исходные и готовые.

Покупать тут — и там же подробная программа и примеры:
https://shagabutdinov.ru/magic-excel

Еще немного 🔥 клавиш. Что умеет F11?

Просто F11 — это вставка диаграммы. Но не простой, а диаграммы на отдельном листе (на котором не будет ячеек). Обычная диаграмма (внедренная) — это Alt + F1.

Alt + F11 — редактор VBA (макросов).

Ну а Shift + F11 — вставка нового листа.