Магия Excel

В срезах можно менять число столбцов и делать их "горизонтальными". Это может пригодиться, чтобы "закрепить" срез над таблицей. Для этого можно сделать срез в несколько столбцов (на вкладке ленты "Срез" / Slicer, в которой, собственно, срез и настраивается — она появляется при активации среза). Затем вставить несколько строк над таблицей (при этом предварительно нужно первую строку закрепить — на вкладке ленты "Вид" / View, "Закрепить области" / Freeze Panes —> "Закрепить верхнюю строку" / Freeze Top Row). Вставить строки можно с помощью контекстного меню (правый щелчок мыши по номеру строки — "Вставить"). И далее переносим срез туда. Теперь он всегда будет наверху. Чтобы он был компактнее, можно изменить высоту кнопок — как и другие настройки среза, это делается в одноименной вкладке ленты инструментов.

У вас Microsoft 365? Тогда можете попробовать удобную опцию для навигации, которая так и называется: Вид — Навигация (View — Navigation) Тут будут видны все объекты на всех листах — сводные таблицы, просто таблицы ("умные"), срезы в этих таблицах и сводных, диаграммы, именованные диапазоны. Можно щелкать по объектам и перемещаться к ним, а можно прямо здесь удалять/переименовывать (для этого щелкаем правой кнопкой 🐁 по объекту).

Суммируем с условием только видимые строки Вот такой вопрос от нашей подписчицы. Просто суммировать (а также считать среднее и еще несколько базовых операций) скрытые строки - это функция SUBTOTAL / ПРОМЕЖУТОЧНЫЕ. ИТОГИ (про нее подробнее здесь). А сумма с условием — это SUMIFS / СУММЕСЛИМН. Эта функция считает не скрытые, а все. Так что ни одна из них "в чистом виде" тут не поможет: одна будет обрабатывать видимые строки (SUBTOTAL), другая суммировать по условию (SUMIFS). Нам надо совместить. Это можно сделать со вспомогательным столбцом и без, разными способами. Разбираем два варианта в этом посте!

Файл с примерами формул для суммирования чисел из видимых строк по условию

Привет, друзья! На случай, если вы пропустили пользу из старых постов, собрали для вас самое сочное за прошлые месяцы: Ctrl + Backspace - очень удобное сочетание клавиш для возвращения к активной ячейке Макрос для сравнения двух файлов (книг Excel) Удаляем строки с пустыми ячейками в одном из столбцов Макрос: создаем оглавление в книге Анализируем сезонность в сводной таблице Поиск по двум критериям Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический)

Повторное применение фильтра Вы поставили фильтр (Ctrl + Shift + L, кстати). Поменяли что-то в данных. И вот некоторые строки, в которых вы вносили изменения, уже фильтрации не соответствуют. Если добавились новые данные в конце таблицы — они тоже не отфильтруются автоматически. Как отобразить актуальные данные? Не нужно отключать фильтр и настраивать снова. Просто нажимайте Ctrl + Alt + L / . Или кнопку "Повторить" (Reapply) на ленте инструментов рядом с кнопкой фильтра (на вкладке "Данные" / Data).

Как выбрать половину значений из списка случайно? Можно такой формулой: =СЛЧИС()<0,5 Функция СЛЧИС / RAND возвращает случайное число от нуля до единицы. Так что в половине случаев оно будет меньше 0,5 - значит, такая формула примерно в половине случаев будет возвращать ИСТИНА / TRUE (так как сгенерированное число будет меньше 0,5), а в половине ЛОЖЬ / FALSE, и по этому признаку можно выбрать данные. Формулы можно превратить в значения - если у вас Microsoft 365, то с помощью Ctrl+Shift+V, а во всех версиях - специальной вставкой (Ctrl+Alt+V).

Автозамена (AutoCorrect). Если вам часто нужно вводить какое-то словосочетание, адрес, имейл и так далее — придумайте для него короткое обозначение и добавьте в список автозамены в Параметрах. Параметры → Правописание → Параметры автозамены Options → Proofing → AutoCorrect Options Обратите внимание, что список автозамены один на все приложения Microsoft Office! Так что введенные вами варианты будут заменяться и в Outlook, Word и т.д.

Совместная работа в Excel (в Microsoft 365) Можно ли колдовать с данными вместе в режиме онлайн в Excel, как в Таблицах? Можно, но нужна подписка Microsoft 365 🤯 И тогда доступны представления (sheet views, своего рода индивидуальные фильтры, как filter views в Таблицах), ветки комментариев, работа над книгой в браузере в Excel Online (но там функционал ограниченный, хоть и обновляется регулярно). Чтобы работать над документом одновременно с другими пользователями, рабочую книгу Excel нужно хранить онлайн. Это предполагает облачное хранилище, а именно OneDrive, OneDrive for Business или SharePoint Online (в перспективе к списку может добавиться Dropbox). Подробности - в этом фрагменте из книги "Магия таблиц" - ну а ее выход ожидается через несколько месяцев, а пока можно подписаться о уведомление о выходе тут: https://www.mann-ivanov-ferber.ru/books/magiia-tablic/ PS А еще мы активно занимаемся обновлением нашей книги про Google Таблицы. Внутри обязательно будет материал и про новые формулы и про Google скрипты. Напишем вам, когда она будет доступна.

Если мы установим фильтр, выберем определенные строки и после этого применим "Автосумму" (Alt + = или на ленте на вкладке "Формулы") — будет введена не функция СУММ / SUM, а ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL. Она же вводится в Таблицах (Создать таблицу - Ctrl + T) в строке итогов (Ctrl+Shift+T). Эта функция позволяет производить вычисление только с видимыми строками. У нее такой синтаксис: =SUBTOTAL(номер функции ; диапазон ; [еще диапазон]; ...) Номер функции определяет, какая операция будет производиться. Функций всего 11 - стандартный набор, который, например, есть и в вычислениях сводных таблиц Excel. Каждая функция бывает в двух вариантах — коротком (9 или 11, например) и длинном из трех цифр (109 или 111). Короткий вариант — подсчет всех видимых строк (отфильтрованных) и скрытых вручную (через скрытие или группировку) строк. Длинный вариант — подсчет только отфильтрованных строк, без скрытых вручную. Если внутри диапазона уже есть другие функции SUBTOTAL, такие вложенные подытоги не будут учитываться. То есть задвоения в таком случае не будет.

План-факт через комбинированную диаграмму Вот такая диаграмма для сравнения двух показателей (план и факт, производство и продажи, ...). Как ее построить? - Тип диаграммы в целом - комбинированная, тип каждого ряда данных - гистограмма. Один из рядов данных - на вспомогательную ось, сама ось удалена (так как она не отличается по значениям от основной) - это все можно настроить, нажав "Изменить тип диаграммы" на ленте или в контекстном меню. - Делаем разные значения бокового зазора у обеих гистограмм, чтобы столбики отличались по ширине. Заливку у обеих делаем прозрачной (в примере 40%). Это настраивается в панели "Формат", выделяем столбики и нажимаем Ctrl+1. - Подписи забираем из ячеек в столбце D. Там формула (просто темп прироста, факт делим на план и вычитаем единицу) и пользовательский формат: +0%* 🔥;-0%* 👎 (смайлики выберите по вкусу) - Добавляем таблицу данных вместо основных подписей и легенды и убираем всякое ненужное (линии сетки, например). P.S. Файл с диаграммой прикреплен в отдельном сообщении выше - забирайте! P.P.S. Мы тут с Лемуром наконец книгу "Магия таблиц" дописали! Получилось почти 600 страниц в гугл-доке (скорее всего, в книге будет поменьше) и почти столько же скриншотов, а также 50+ файлов с примерами. На уведомление о выходе можно подписаться тут: https://www.mann-ivanov-ferber.ru/books/magiia-tablic/