Создавайте специальные итоги для набора данных Excel, в котором вы будете фильтровать данные. Также в этом видео: Использование SUBTOTAL (109,) для суммирования данных, которые были скрыты вручную. Использование Ctrl + Ditto для копирования точной формулы вниз на одну строку.
После того, как вы применили фильтр, было бы здорово увидеть общее количество видимых ячеек.
Выберите пустую ячейку под каждым из ваших числовых столбцов. Щелкните кнопку Автосумма.
Вместо того, чтобы вставлять формулы СУММ, Excel вставляет =SUBTOTAL(9,… )формулы. Приведенная ниже формула показывает сумму только видимых ячеек.
Вставьте несколько пустых строк над вашими данными. Вырежьте формулы из-под данных и вставьте в строку 1 с меткой Total Visible.
Теперь, когда вы меняете фильтры, даже если данные заполняют более одного полного экрана данных, вы увидите итоги вверху своего рабочего листа.
Спасибо Сэму Радаковицу из команды Excel за фильтр по выделению - не за предложение «Фильтр по выделению», а за формализацию фильтра по выделению!
Смотреть видео
- Создавайте специальные итоги для набора данных Excel, в котором вы будете фильтровать данные. Также в этом видео: Использование SUBTOTAL (109,) для суммирования данных, которые были скрыты вручную. Использование Ctrl + Ditto для копирования точной формулы вниз на одну строку.
Стенограмма видео
Подкаст спонсируется "MrExcel XL", книгой с более чем 40 советами по Excel, мультфильмами Excel, коктейлями, твитами и шутками.
Изучите Excel из подкаста, эпизод 1976 года - Всего видимых ячеек!
Привет, добро пожаловать обратно в сеть, я Билл Джелен. Конечно, у всех, самая первая формула, которую создает большинство людей, - это автосумма. Вы находитесь внизу под столбцом чисел, вы получаете кнопку AutoSum, и он будет использовать функцию суммы для суммирования чисел над ним, пока не встретит формулу или текст. Правильно? Или Alt + = делает то же самое. Или, что еще лучше, выберите несколько ячеек, а затем нажмите Автосумма, и все эти формулы будут помещены без необходимости подтверждать формулы.
Но вот крутой трюк, давайте включим автофильтр. Итак, мы собираемся использовать трюк с фильтром по выделению из вчерашнего подкаста. Данные фильтруются. Я собираюсь перейти к самой первой пустой строке под данными, а затем нажать кнопку Автосумма. Но, видите ли, происходит нечто совсем другое. Вместо функции СУММ мы получаем = ПРОМЕЖУТОЧНЫЙ ИТОГО (9! И что это такое, это общее количество видимых ячеек. Хорошо, очень здорово, что это работает.
Вот в чем дело, я проводил семинар по Excel почти десять лет назад в Форт-Уэйне, штат Индиана, для Института внутренних аудиторов. Кто-то показал мне этот трюк, и мне он очень нравится. Я никогда раньше не видел трюка, он был классный, я сказал, что это здорово, я собираюсь добавить это на семинар. А потом в следующем году я вернулся в Форт-Уэйн, и там появился менеджер того парня, да, он приехал не первый год, потому что думал, что знает об Excel все. Но он вернулся, чтобы заявить об этой уловке, и говорит: «Послушайте, я слышал, что мой финансовый аналитик рассказал вам об этой уловке, но он не сказал вам лучшего, поднимитесь над вашими данными и вставьте туда две строки. И затем, когда у вас будет общее количество видимых ячеек внизу, вырежьте, CTRL + X, и вставьте, CTRL + V, хорошо, что это делает,если вы переключитесь на другого клиента: «Хорошо, я выберу здесь еще несколько клиентов». И теперь у меня больше клиентов, чем поместится на экране! И мне не нужно прокручивать вниз, чтобы увидеть итоги, они всегда вверху! "
Хорошо, теперь этот трюк с использованием автосуммы под данными фильтра работает, когда вы скрываете строки с помощью фильтра. Что делать, если вы вручную скрываете строки? Итак, здесь я выбрал несколько строк, уже перешел в форматирование, скрытие и отображение, скрытие строк! Итак, теперь у нас есть скрытые строки. Я опускаюсь вниз, нажимаю Alt + = или Автосумма, и это не дает мне функции ПРОМЕЖУТОЧНЫЙ ИТОГ, а дает мне функцию СУММ.
Хорошо, сейчас я собираюсь сделать особую вещь, я собираюсь сделать CTRL + '(то же самое, апостроф). Это приведет к снижению той же формулы без изменения ссылки на ячейку. Итак, E2: E8 изменился или не изменился, как правило, если вы копируете и вставляете изменения в E3: E9. Я собираюсь изменить это на SUBTOTAL (9, и вы увидите, что это не работает, он по-прежнему дает нам все ячейки. 1000 + 400 + 800 - это не 4500, это включая скрытые строки. Но вот что вам нужно сделать это, нажмите CTRL + 'и измените это 9 на 109, и вы получите: Истинное общее количество видимых на основе скрытых вручную строк!
Хорошо, вот и пара различных трюков, AutoSum, Alt + = просто дает нам функцию SUM. Если вы находитесь ниже данных и нажмете автосумму, вы получите ПРОМЕЖУТОЧНЫЙ ИТОГ (9, который отлично подходит для фильтрации), или вы можете просто вручную ввести ПРОМЕЖУТОЧНЫЙ ИТОГ (109, чтобы получить сумму видимого, когда вы отфильтровали вручную.
Хорошо, вот краткое изложение концепций в сегодняшнем видео: это, кстати, даже не совет №3 в книге, это бонусный совет №2б справа, так что между советом 2 и советом 3. Книга охватывает все эти вопросы. Предметы. Что ж, спасибо всем, кто предлагал это, людям в отделении IIA в Форт-Уэйне, штат Индиана, именно здесь я впервые услышал об этом. Это отличная книга, вы можете купить ее в печатном виде или в электронном виде, щелкнув букву «i» в правом верхнем углу. Вы перейдете к более подробной информации о книге.
Хорошо, я хочу поблагодарить вас за то, что заглянули, увидимся в следующий раз на другом сетевом трансляции от!
