Функция промежуточного итога в Excel вставляет функцию SUBTOTAL в базу данных или список связанных данных и извлекает конкретную информацию. Функция не ограничивается нахождением суммы или итога для выбранных строк данных. Он также находит средние значения для каждого столбца или поля данных в вашей базе данных.
Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; Excel для Office 365, Excel Online, Excel для Mac, Excel для iPad, Excel для iPhone и Excel для Android.
Введите данные в Excel
Первым шагом к использованию функции SUBTOTAL в Excel является ввод ваших данных в таблицу. При этом помните следующее:
- Введите данные правильно. Ошибки, вызванные неправильным вводом данных, являются источником многих проблем, связанных с управлением данными.
- Не оставляйте пустых строк или столбцов при вводе данных. Это включает в себя не оставлять пустую строку между заголовками столбцов и первой строкой данных.
Чтобы следовать этому руководству, введите данные, показанные на рисунке ниже, в ячейки от A1 до D12 .
Сортировать данные Excel
Перед применением промежуточных итогов ваши данные должны быть сгруппированы по столбцу данных, который вы хотите извлечь. Эта группировка выполняется с помощью функции сортировки Excel.
Из этого туториала вы узнаете среднее количество заказов на регион продаж. Вот как отсортировать данные по заголовку столбца Регион.
-
Перетащите выделенные ячейки A2 на D12 , чтобы выделить их.
-
Выберите Данные .
-
Выберите Сортировка , чтобы открыть диалоговое окно Сортировка.
-
Поставьте галочку рядом с Мои данные имеют заголовки .
-
В раскрывающемся списке «Сортировать по» выберите Регион .
-
Выберите ОК .
Данные в ячейках с A3 по D12 сортируются в алфавитном порядке по второму столбцу Region. Сначала перечисляются данные для трех торговых представителей из восточного региона, затем Север, затем Юг и Запад.
Найти средние по региону
После правильной сортировки данных найдите средние продажи по каждому региону, используя функцию SUBTOTAL. Синтаксис и его аргументы для функции:
= ИТОГО ( Function_Num , Ref1 )
- Function_num : функция SUBTOTAL выполняет ряд различных функций с числовыми данными. Это поле обозначает функцию, которая должна быть выполнена. Для функции СРЕДНЕГО используйте номер 1.
- Ref1 : этот аргумент отмечает диапазон чисел, которые используются в вычислениях SUBTOTAL-функции.
Чтобы использовать СРЕДНЮЮ функцию SUBTOTAL:
-
Создайте группу ячеек, в которых будут отображаться региональные данные о продажах. Для этого урока введите данные, показанные в ячейках с F2 по F5.
-
Чтобы усреднить данные о продажах на востоке, выберите ячейку G2 . Это где результаты появятся.
-
Выберите Формулы .
-
Выберите Math & Trig > SUBTOTAL , чтобы открыть диалоговое окно «Аргументы функций». В Excel для Mac откроется построитель формул.
-
Поместите курсор в текстовое поле Function_num и введите число 1 .
-
Поместите курсор в текстовое поле Ref1 и выделите ячейки от D3 до D5 . Эти ячейки будут объединены для расчета среднего объема продаж в восточном регионе.
-
Выберите ОК . За исключением Excel для Mac, где вы выбираете Готово . Средние данные о продажах отображаются в ячейке G2.
-
Повторите вышеуказанные шаги для продаж на север, продаж на юг и продаж на запад. Выберите соответствующий диапазон Ref1 для каждого сценария.
