Как использовать агрегатную функцию в Microsoft Excel

25 марта 2022 г.

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

В этой статье мы обсудим, что такое агрегатная функция, когда ее использовать и как ее использовать, а также приведем примеры того, как может выглядеть вычисление с использованием агрегатной функции.

Что такое агрегатная функция в Excel?

Агрегатная функция — это формула, которую можно использовать в Excel для выполнения вычислений, избегая возможных ошибок. Вы можете написать агрегатную функцию с двумя разными вариантами синтаксиса, либо в виде ссылки, либо в виде массива. Синтаксис агрегатной функции при записи в виде ссылки:
АГРЕГАТ(функция,опции,ссылка)

Синтаксис агрегатной функции при записи в виде массива:
АГРЕГАТ(функция,параметры,массив,[optional_argument]

В этих формулах переменная «функция» всегда представляет собой число от 1 до 19. При написании вашей функции число, которое вы пишете в месте «функция», определяет, какое вычисление выполняет ваша формула. Вот числа, которые вы можете использовать, и какие вычисления они заставляют выполнять формулу:

1. СРЗНАЧ: Эта функция указывает формуле усреднять ячейки, на которые вы ссылаетесь. Если вы напишете «1» в качестве переменной функции, ваша агрегатная функция вернет среднее значение всех чисел во включенных вами ячейках.

2. COUNT: Функция COUNT подсчитывает все значения или ячейки, содержащие числа.

3. СЧЕТЧИК: Функция СЧЕТЧИК выполняет те же вычисления, что и СЧЕТЧИК, но вместо подсчета только ячеек, содержащих числа, СЧЕТЧИК подсчитывает количество непустых ячеек.

4. МАКС. Функция МАКС. возвращает наибольшее значение в вашей ссылке. Это может быть полезной функцией, если вы пытаетесь найти верхний предел ваших данных.

5. MIN: функция MIN возвращает наименьшее значение в вашей ссылке. Подобно функции MAX, это может быть полезной функцией, если вы пытаетесь найти нижний предел ваших данных.

6. ПРОДУКТ: Эта функция дает вам произведение всех значений в вашей ссылке.

7. СТАНДОТКЛОН.С: эта функция оценивает стандартное отклонение ваших значений на основе выборки. Это может быть полезно, если вы пытаетесь найти среднее значение, но хотите знать диапазон чисел в своих ячейках.

8. СТАНДОТКЛОН.П: Эта функция похожа на СТАНДОТКЛОН.С, но вместо оценки стандартного отклонения на основе выборки она использует совокупность.

9. СУММ: Функция СУММ вычисляет сумму всех значений в ваших ячейках.

10. VAR.S: VAR.S дает вам дисперсию на основе выборки ваших данных.

11. VARP.P: эта функция дает вам дисперсию на основе генеральной совокупности, а не выборки.

12. МЕДИАНА: Функция МЕДИАНА возвращает значение, которое находится в середине ваших данных. Эта функция может быть полезна в сочетании с функцией СРЗНАЧ, чтобы вы могли найти не только среднее значение, но и медиану.

13. MODE.SNGL: эта функция сообщает вам, какое число чаще всего встречается в ваших данных. Это может быть полезно, если вы пытаетесь найти наиболее распространенную точку данных в своей информации.

14. НАИБОЛЬШИЙ: функция НАИБОЛЬШИЙ возвращает n-е по величине значение, например пятое или девятое.

15. МАЛЕНЬКИЙ: МАЛЕНЬКИЙ возвращает n-е наименьшее значение.

16. ПРОЦЕНТИЛЬ.ВКЛ: Эта функция дает вам n-й процентиль всех значений в диапазоне, который включает само значение.

17. КВАРТИЛЬ.ВКЛ: Функция КВАРТИЛЬ.ВКЛ возвращает квартиль на основе процентиля, включающего значение.

18. ПРОЦЕНТИЛЬ.ИСКЛ: эта функция аналогична ПРОЦЕНТИЛЬ.ВКЛ, но вместо того, чтобы включать значение, эта функция исключает его.

19. QUARTILE.EXC: QUARTILE.EXC похож на QUARTILE.INC, но возвращает квартиль на основе процентиля, который исключает значение.

При написании формулы после того, как вы выбрали, какую функцию включить, вы можете перейти к возможным параметрам, которые вы можете использовать в качестве параметров в своей формуле. Подобно тому, как работают функции, вы можете записать переменную «options» в виде числа от 1 до 7. Вот все возможные параметры, которые вы можете использовать в своей агрегатной функции в Excel, и то, что они делают:

1. Игнорировать скрытые строки, вложенные функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ

2. Игнорировать значения ошибок, вложенные функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ

3. Игнорировать скрытые строки, значения ошибок, вложенные функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ

4. Ничего не игнорировать

5. Игнорировать скрытые строки

6. Игнорировать значения ошибок

7. Игнорировать скрытые строки и значения ошибок

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

Когда использовать агрегатную функцию в Excel

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

При анализе данных

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

Когда вам нужно игнорировать пустые строки

Если у вас есть таблица данных с несколькими пустыми строками, вам может помочь агрегатная функция. Эта функция может выполнять вычисления, которые игнорируют пустые строки, чтобы вы не получали сообщение об ошибке при выполнении формулы. Если вам нужно выполнить вычисления, но вы не можете переформатировать таблицу данных, вам может пригодиться агрегатная функция.

При попытке найти диапазон ваших данных

Если у вас есть диапазон данных и вам нужно найти наибольшее и наименьшее значение, может оказаться полезной агрегатная функция. Используя функцию НАИБОЛЬШИЙ, ваша формула может сказать вам, какое наибольшее число находится в вашей таблице данных. И наоборот, функция НАИМЕНЬШИЙ может предоставить наименьшее значение. Используя эти функции вместе, вы можете легко выяснить, каков диапазон значений в вашей таблице данных.

При попытке найти среднее значение в таблице данных

Если вам нужно найти среднее значение чисел в ваших данных, вам может помочь агрегатная функция. Если вам нужно найти среднее значение, вы можете использовать функцию СРЗНАЧ, доступную как подраздел агрегатной функции. Это уравнение может суммировать все введенные вами значения и возвращать среднее значение.

Как использовать агрегатную функцию в Excel

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

1. Создайте свою таблицу данных или массив

Первый шаг при использовании агрегатной функции — убедиться, что все ваши данные присутствуют в электронной таблице. Для этого вы можете импортировать файл данных или ввести значения в ячейки вручную. После того, как вы ввели все свои данные, вы можете пометить строки и столбцы, чтобы вы и другие могли легко видеть, откуда берутся значения.

2. Решите, в какую ячейку вы хотите поместить формулу, и введите ее в строку формул.

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

АГРЕГАТ(функция,опции,ссылка)

3. Замените переменные в формуле

После написания уравнения следующим шагом будет замена переменных числами, представляющими функции и параметры. Например, если вы хотите найти медианное число в своих данных, вы должны заменить «функция» на 12. Вы должны сделать то же самое для переменных «опции» и «ссылка». При вводе формулы Excel предлагает вам раскрывающееся меню, чтобы вы могли выбирать числа вручную, вместо того, чтобы запоминать, что представляет каждое из них.

4. Проверьте свою функцию, чтобы убедиться, что она работает

После того, как вы заменили все переменные числами и информацией, которую хотите проанализировать, последний шаг — убедиться, что ваша формула работает. Это можно сделать, проверив число в ячейке, которая ссылается на строку формул. Если ваша формула возвращает ошибку, вы можете вернуться и убедиться, что ваш синтаксис верен.

Пример агрегатной функции

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

Предположим, у вас есть таблица данных, состоящая из ячеек 2–9 в столбце A и 2–9 в столбце B, но содержащая значения ошибок в ячейках в ячейках A5 и B6. Для начала введите формулу АГРЕГАТ(функция,опции,ссылка) и начните с замены переменных. Во-первых, вы можете заменить «функция» на «1», так как это число представляет функцию СРЗНАЧ. Затем замените «параметры» на «6», так как это число указывает функции игнорировать значения ошибок. Наконец, замените «ref» на «A2:A:9,B2:B9», так как это синтаксис для ссылки на несколько ячеек при использовании формулы в Excel.

В этом примере окончательное уравнение должно выглядеть так: АГРЕГАТ(1,6,A2:A9,B2:B9). Это уравнение выведет среднее значение всех чисел в строках 2–9 столбцов A и B, игнорируя любые значения ошибок, присутствующие в вашей таблице.

Обратите внимание, что ни одна из компаний, упомянутых в этой статье, не связана с компанией Indeed.