Возможно, вы уже знакомы с функцией СРЗНАЧ в Excel, которая вычисляет среднее значение набора чисел. Однако бывают случаи, когда нужно учитывать только те значения, которые соответствуют определенным критериям. Здесь на помощь приходит функция Excel СРЗНАЧЕСЛИ, которая позволяет рассчитывать среднее значение только для значений, соответствующих заданным условиям. В этом руководстве мы подробно рассмотрим, как использовать и оптимизировать функцию СРЗНАЧЕСЛИ в Excel, чтобы повысить эффективность вашей работы.
Что такое функция СРЗНАЧЕСЛИ в Excel и как ее использовать?
Функция СРЗНАЧЕСЛИ в Excel облегчает пользователям вычисление среднего значения указанного диапазона ячеек, соответствующих определенному условию. синтаксис для реализации Функция Excel СРЗНАЧЕСЛИ состоит из 3 аргумента и заключается в следующем:
Синтаксис:
СРЗНАЧЕСЛИ (диапазон, критерий, [средний диапазон])
Аргументы:
Диапазон: Это диапазон ячеек, которые вы хотите проверить на соответствие определенным критериям.
Критерии: Условие или критерий, которому должны соответствовать ячейки в диапазоне, чтобы их значения были учтены в расчете среднего.
Средний Диапазон (необязательно): Это диапазон ячеек, для которых вы хотите вычислить среднее значение. Если этот аргумент не указан, функция будет использовать диапазон, заданный в первом аргументе.
В вышеупомянутом аргументе первые два являются обязательными, в отличие от третьего.
Для более перспективного понимания Функция Excel СРЗНАЧЕСЛИ, давайте продемонстрируем пример. Предположим, у вас есть набор данных о продажах конкретной модели автомобиля за несколько лет в столбце A и соответствующее количество проданных единиц в столбце B.
Воспользуемся этими данными, чтобы определить среднее количество автомобилей, проданных в год. В то время Функция среднего значения Excel может предоставить нам базовое среднее значение, что, если мы хотим учитывать только те годы, когда количество проданных единиц превысило 10? Вот когда Функция Excel СРЗНАЧЕСЛИ вступает в игру. К применение условий к нашим средним расчетам, мы можем получить более глубокое понимание наших данных.
Шаг 1: Нажмите на пустая ячейка где мы хотим рассчитать среднее значение.
Шаг 2: Далее мы введем Формула Excel ЕСЛИ в пустой ячейке Б10.Обязательно начинайте формулу с Знак равенства.
Шаг 3: С помощью курсора выберите диапазон для СРЕДНЕЕСЛИ функция; то есть.Б2:Б9.
Шаг 4: Мы хотим, чтобы наше среднее значение включало только те годы, когда количество проданных единиц превышало 10, поэтому критерии для нашей функции будет«>10».
Шаг 5: Закрой круглые скобки и нажмите «Входить», чтобы позволить Excel вычислить Среднее количество единиц, продаваемых каждый год.
Дополнительные советы, которые следует знать при использовании функции СРЗНАЧЕСЛИ
При использовании Функция СРЗНАЧЕСЛИ, есть некоторые аспекты, о которых вам следует знать. Вот краткий обзор этих соображений:
Значения ИСТИНА и ЛОЖЬ: Функция учитывает для расчета только числовые значения, поэтому она игнорирует любые логические значения (ИСТИНА или ЛОЖЬ) в диапазоне.
Пустые ячейки: Функция не включает пустые ячейки в диапазон при вычислении среднего значения, поэтому они не влияют на ваш средний результат.
#ДЕЛ/0! ошибка: Функция вернет #DIV/0! ошибка, если все ячейки, выбранные в качестве диапазона, не соответствуют критериям, то есть нет значений для усреднения.
Конкретный диапазон: Функция требует в качестве аргумента определенный диапазон ячеек, вместо него нельзя подставить массив.
Расчет среднего диапазона: Функция позволяет выбрать другой диапазон ячеек для расчета среднего, который может быть больше основного диапазона, используемого для критериев. Вычисление начинается с верхней левой ячейки диапазона Average Range, и усредняются только совпадающие ячейки (на основе критериев) из обоих диапазонов. Несовпадающие ячейки игнорируются при вычислении среднего значения.
Подстановочные знаки и чувствительность к регистру: Функция поддерживает использование подстановочных знаков (* и ?) для гибкого сопоставления и одинаково обрабатывает прописные и строчные буквы при оценке критериев.
Примеры использования функции Averageif в Excel
Понимание того, как использовать логические операторы (>, <,>, =) и подстановочные знаки (*, ?) в критериях, может помочь выполнять более сложные и гибкие операции условного усреднения.
Вот таблица, иллюстрирующая критерии использования функции СРЗНАЧЕСЛИ:
Цель | Критерии |
|---|---|
Ячейки равны ста | 100 или «100» |
Клетки больше 75 | ">75" |
Ячейки, которые не пусты | "<>" |
Ячейки, которые пусты | (пустые кавычки) |
Ячейки, не равные «красному» | "<>красный" |
Ячейки, равные «красному» | "красный" |
Ячейки меньше или равны 100 | "<=100"<> |
Ячейки, начинающиеся с «X» | "Икс*" |
Ячейки меньше значения в ячейке A1 | "<"&a1<> |
Ячейки меньше сегодняшней даты | "<"&сегодня()<> |
Таблица критериев функции СРЗНАЧЕСЛИ
Давайте рассмотрим несколько примеров для полного понимания.
(1) Использование текстовых критериев AVERAGEIF
Вот простой пример использования Excel СРЗНАЧЕСЛИ функция с текстовыми критериями.
Допустим, у вас есть список студентов и соответствующие им баллы за тест:
Теперь, если мы хотим вычислить средний балл всех студентов, набравших более 80 баллов, мы будем использовать Excel СРЗНАЧЕСЛИ функция с «>80» в качестве нашего текстового критерия.
Шаг 1: Нажмите на пустую ячейку, например B7, и введите формулу СРЗНАЧЕСЛИ.
Шаг 2: Мы будем выбирать результат теста из 5 студентов как наш диапазон.
Шаг 3: В качестве наших критериев мы введем >80 как мы хотим Функция СРЗНАЧЕСЛИ чтобы включить результат теста выше 80. Убедитесь, что критерии введены внутри кавычки.
Шаг 4: Нажать "Входить» после закрытия круглые скобки. Excel отобразит Средний балл студентов без учета баллов ниже 80; Результат Мэри, равный 78, будет исключен из расчетов.
(2) Использование СРЗНАЧЕСЛИ с датами
Вот простой пример использования функции СРЗНАЧЕСЛИ с датами.
Допустим, у вас есть список данных о продажах с датами и соответствующими суммами продаж:
Мы можем использовать Excel СРЗНАЧЕСЛИ функция для расчета среднего объема продаж с февраля по текущую дату. Наши критерии будут «>01.02.2023».
Шаг 1: Выберите пустую ячейку, чтобы выполнить расчет среднего значения. Введите формулу СРЗНАЧЕСЛИ.
Шаг 2: Мы выберем столбец даты как наш диапазон для Функция СРЗНАЧЕСЛИ. Поскольку наш критерии будет основано на дата, столбец даты становится нашим Целевой диапазон для расчета.
Шаг 3: Для нашего критерии аргумент, мы вступим«>01.02.2023». Убедитесь, что Формат даты соблюдается правильно;ММ/ДД/ГГГГ
Шаг 4: Наш последний аргумент:средний диапазон, мы выберем "Объем продаж" столбец. Это позволяет нам рассчитать средний на основе значений в столбце «Продажи».
Шаг 5: Нажать "Входить», чтобы отобразить результаты.
Будут отображены средние продажи с февраля по текущую дату.
(3) Использование СРЗНАЧЕСЛИ с критериями «больше 0»
Давайте рассмотрим набор данных со следующими показателями продаж различных продуктов, чтобы понять, как Функция Excel СРЗНАЧЕСЛИ может использоваться с критериями «больше 0»:
Мы хотим найти средний объем продаж для продуктов, продажи которых больше 0. Для этого мы можем использовать функцию СРЗНАЧЕСЛИ с критерием «больше 0».
Шаг 1:Выберите пустая ячейка где вы хотите провести средний расчет.
Шаг 2: Введите Excel СРЗНАЧЕСЛИ Функция. Обязательно запустите функцию с помощью Знак равенства.
Шаг 3: Дляаргумент диапазона, выберитедиапазон ячеек которые содержат данные о продажах. В примере набора данных, т.е. B2:B6.
Шаг 4: Для аргумент критерия, входить">0",с кавычками. Это указывает на то, что мы хотим вычислить среднее значение объемов продаж, превышающее 0.
Шаг 5: Чтобы отобразить результаты, нажмите «Входить"
Функция Excel СРЗНАЧЕСЛИ рассчитает среднее значение объемов продаж для продуктов, у которых есть продажи больше 0.
Если хотите узнать подробнее об этих способах, посмотрите это видео:
Преимущества использования WPS Office
Однако методы приложения СРЗНАЧЕСЛИ применимы как к Microsoft Excel, так и к электронным таблицам WPS Office (Spreadsheet). Офис WPS предоставляет его бесплатно. Не только это, но и WPS Office предлагает множество других причин, почему он лучше Excel.
Цена: Если у вас ограниченный бюджет или вы ищете недорогой вариант,WPS Office предоставляет бесплатную версию с мощными функциями.
Совместимость: WPS Office гарантирует совместимость с несколькими типами форматов файлов, включая Excel.
Функции: Благодаря набору множества инструментов он обеспечивает оптимальный опыт для своих пользователей.
Удобный интерфейс: Это интерфейс удобен для новичков так что новые пользователи не сталкиваются с большими хлопотами.
Облачная интеграция: Офис WPS обеспечивает интеграцию облачного хранилища, что позволяет пользователям удобно сохранять файлы и получать к ним доступ из облачных хранилищ, таких как Google Drive и OneDrive.
Мультиплатформенная поддержка: WPS Office предлагает поддержку нескольких платформ, что делает его доступен в различных операционных системах, таких как Windows, macOS, Linux, Android и iOS.
Как использовать функцию СРЗНАЧЕСЛИ в WPS
Офис WPS, мощный пакет повышения производительности, предлагает функции, аналогичные MS Excel, но со значительным преимуществом: это бесплатно! Как и в Excel, вы можете легко использовать Функция СРЗНАЧЕСЛИ в Офис WPS для расчета средних значений в зависимости от конкретных условий.
Вот простой пример использования функции СРЗНАЧЕСЛИ в WPS Office. Допустим, у вас есть список сотрудников и соответствующие им ежемесячные продажи:
Шаг 1: Выберите пустая ячейка где вы хотите отобразить средние результаты.
Шаг 2: Далее введите СРЕДНЕЕСЛИ функция.
Шаг 3: Наш диапазон будет столбец ежемесячных продаж, поскольку мы хотим рассчитать среднемесячные продажи.
Шаг 4: По нашим критериям мы будем вводить«>1000» поскольку мы хотим использовать Функция СРЗНАЧЕСЛИ в WPS Office для расчета среднего объема продаж сотрудников, добившихся продаж больше 1000.
Шаг 5: Нажать Входить рассчитать среднемесячный объем продаж.Офис WPS отобразит средний объем продаж для выбранных сотрудников которые соответствуют критериям.
Часто задаваемые вопросы о Excel СРЗНАЧЕСЛИ
Вопрос 1. Могу ли я использовать СРЗНАЧЕСЛИ со ссылкой на ячейку и как?
Да, вы можете использовать Функция СРЗНАЧЕСЛИ со ссылками на ячейки как в Microsoft Excel, так и в электронной таблице WPS Office. Пользователи могут напрямую использовать ссылку на ячейку для определенного значения, например СРЗНАЧЕСЛИ (диапазон, ссылка на ячейку), или использовать логические операторы в сочетании со ссылками на ячейки, например СРЗНАЧЕСЛИ (диапазон, «>» и ссылка на ячейку), чтобы найти средние значения на основе определенных условий. .
В2. Как усреднить ячейки на основе частичного совпадения?
Чтобы усреднить ячейки на основе частичного совпадения, вы можете использовать функцию СРЗНАЧЕСЛИ вместе с подстановочными знаками (* и ?) в электронной таблице Microsoft Excel или WPS Office. Подстановочные знаки позволяют сопоставлять текстовые шаблоны, а не точные совпадения, что позволяет выполнять частичное сопоставление для анализа данных.
Вопрос 3. Могу ли я использовать другие полезные формулы как в WPS Office, так и в Microsoft Excel?
Да, и WPS Office, и Microsoft Excel используют широкий спектр формул, помимо функции СРЗНАЧЕСЛИ. К ним относятся СУММА, СЧЕТ, ЕСЛИ, ВПР, ГПР, ЕСЛИОШИБКА и многие другие.
Откройте для себя потенциал функции СРЗНАЧЕСЛИ
В этой статье мы подробно углубились в функциональность Функция СРЗНАЧЕСЛИ в таблицах Excel и WPS. Хотя оба приложения предлагают одинаковую реализацию этой функции, WPS Spreadsheets лидирует благодаря своему превосходному удобству и интуитивно понятному интерфейсу. Скачать Таблица WPS сегодня, чтобы испытать беспрепятственное манипулирование и анализ данных.
