Может ли Excel применить фильтр к данным с промежуточным итогом? Эта статья покажет вам, как это сделать.
Смотреть видео
- РА спрашивает? Можете ли вы отфильтровать данные с промежуточным итогом?
- Как сделать так, чтобы промежуточные итоги отображались в верхней части каждой группы?
- При создании промежуточных итогов снимите флажок «Сводка под данными».
- Можете ли вы использовать фильтр в наборе данных с промежуточным итогом?
- Да, но вы также должны включить пустые ячейки
- Как можно заставить вычисление процентов работать в строках промежуточных итогов?
- Прямо сейчас процент суммирует другие строки, а не выполняет расчет
- После добавления промежуточных итогов скопируйте расчет процентов из строки без промежуточных итогов во все другие строки.
Стенограмма видео
Изучите Excel из подкаста, эпизод 2174: Фильтр промежуточных данных.
Хорошо, сегодня вопрос от РА. Он говорит: «Кто-то прислал мне набор данных, в котором есть промежуточные итоги в верхней части каждой группы. Как они это делают? А затем они создали субтитры, которые вы можете фильтровать. Как вы можете это сделать? А потом, позже, как можем ли мы добиться правильного расчета процентного соотношения в строке промежуточных итогов? "
Прекрасная особенность функции промежуточных итогов - = ПРОМЕЖУТОЧНЫЙ ИТОГ 9 - заключается в том, что она игнорирует другие промежуточные итоги в наборе. Но он также всегда исключает отфильтрованные ячейки, если вы используете фильтр. Теперь, если кто-то вручную скрывал строки, вам пришлось бы использовать 109 вместо 9. Но в этом случае мы говорим просто о фильтрах, поэтому это должно быть довольно просто.
Итак, начнем. У нас действительно есть три дела сегодня. Мы должны добавить промежуточные итоги вверху группы. Итак, мы перейдем на вкладку Data, здесь я собираюсь добавить субтитры по клиентам, поэтому первое, что я делаю, это Сортирую по клиенту, эти данные уже отсортированы, а затем нажимаю команду Промежуточный итог при каждом изменении. в клиенте. Мы собираемся использовать функцию СУММ - я собираюсь добавить ее ко всем пяти из них, даже если этот процент валовой прибыли не будет работать - а затем, если я хочу, чтобы итоги отображались в верхней части для каждого набора данных я снимаю флажок "Сводка под данными", нажимаю "ОК". И я получаю - вот магазины ABC и итоговые суммы для магазинов ABC появляются поверх этого, хорошо? Итак, вот как получить промежуточные итоги наверху.
Далее мы хотим иметь возможность фильтровать эти данные. Итак, я собираюсь фильтровать на основе продукта, поэтому я выбираю одну ячейку, щелкаю значок фильтра и хочу просто увидеть итоги для ABC. Хорошо. Итак, сначала я захожу сюда и выбираю только ABC, вот так жму ОК. И я понял, что смотрю записи ABC, но теперь не вижу итогов. Итак, уловка здесь в том, что если я хочу увидеть все записи ABC и итоги для продукта ABC, я собираюсь выбрать как ABC, так и (пустые), потому что итоги будут отображаться там, где находится продукт. пусто, хорошо?
Итак, теперь я вижу, что у AT&T были две разные записи для ABC, и давайте просто проведем здесь небольшой тест. Выберите эти две ячейки, итого 23 978, и, конечно же, работает - 23 978. Если бы я очистил этот фильтр, общая сумма AT&T составила 498 000 долларов. Но 23000 продуктов ABC. Хорошо, давайте попробуем еще раз для другой записи. Выберите DEF, выберите (Пробелы), нажмите ОК. И мы видим, что AT&T - все записи DEF, которые они покупают много, - это 237000. Итак, конечно же, команда «Фильтр» работает с промежуточным итогом. Хорошо, это здорово, что это работает.
Но что здесь не работает, так это процент валовой прибыли. Хорошо, у нас есть эти четыре рекорда для магазинов ABC, и они суммируются - 225%… И было бы даже неправильно изменять это значение на среднее. Было бы в среднем эти 4, но если бы у вас были более крупные заказы и более мелкие заказы, это неправильный ответ. И просто чтобы доказать это - здесь среднее значение 56,3. Теперь, что мы собираемся сделать, мы собираемся взять одну из формул, которая работает - итак, G4 делится на E4 - мы собираемся скопировать эту формулу и вставить ее повсюду. вся колонка, хорошо? Включая итоги - так что вставлю. И мы видим, что средний процент валовой прибыли, используя эти цифры здесь - прибыль и доход, это не 56,3, а на самом деле 56%.Это одно из тех правил, по которым нельзя просто брать среднее значение по строке Итого, определенно нельзя брать сумму. Но если скопировать ту же процентную формулу, которую вы используете для строк подробностей, в строки промежуточных итогов, она будет работать.
Хорошо. В моей книге Power Excels with, выпуск 2017 г. много тем, посвященных промежуточным итогам. Если вы поклонник Subtotal, вам понравится эта книга. Щелкните эту букву «I» в правом верхнем углу.
Хорошо. Сегодня РА: Можете ли вы отфильтровать данные с промежуточным итогом? Да, вообще-то можешь. Это будет работать, если вы также включите пустую ячейку. Как сделать так, чтобы промежуточные итоги отображались вверху каждой группы? При создании промежуточных итогов снимите флажок «Сводка под данными». А затем: «Эй, а почему процентные вычисления не работают в строках промежуточных итогов?» Ну, потому что это один из тех вычислений, которые вам нужно снова проделать в строке Промежуточный итог. Поэтому просто выберите один из процентных расчетов из подробной строки и скопируйте его в другие строки.
Что ж, я хочу поблагодарить RA за то, что прислал этот вопрос, и я хочу поблагодарить вас за то, что заглянули. Увидимся в следующий раз для другого сетевого вещания от.
Скачать файл
Загрузите образец файла здесь: Podcast2174.xlsm
