В тази статия ще научим как да получите средната стойност с множество критерии в Excel.
Проблем?
За Instance имаме голям списък с данни и трябва да намерим средната стойност на цената или сумата при определени критерии. Критерии могат да бъдат приложени към всяка колона от данни.
Как да решим проблема.
Сега ще направим формула от функцията. Тук ни се предоставят данните и трябваше да намерим СРЕДНАТА на числата, които имат някакви критерии
Обща формула:
= AVERAGEIFS (Среден_диапазон, диапазон1, критерии1, [диапазон2, критерии2], [диапазон3, критерии3], …)
- Среден_диапазон: диапазон от стойности, където трябва да се оцени средната стойност
- range1: първият диапазон, където се прилагат критерии1.
- критерии1: първи критерии, приложени към диапазон1.
- range2: втори диапазон, където се прилага критерий1.
- критерии2: втори критерий, приложен към диапазон2.
- range3: трети диапазон, където се прилагат критерии1.
- критерии3: трети диапазон, приложен към диапазон3.
Бележки: не предоставят дата директно към функцията. Използвайте функцията DATE или използвайте препратка към клетка за аргумент за дата в Excel, тъй като Excel чете дата само с правилния ред.
Пример:
Всичко това може да е объркващо за разбиране. Така че, нека тестваме тази формула, като я изпълним в примера, показан по -долу.
Тук сме дали данни от A1: D51. Трябва да намерим СРЕДНАТА на полученото количество по различни критерии.
Условията са следните.
- Датата трябва да бъде преди 31.01.2019 г.
- Град "Бостън"
- Продуктът се определя като „морков“
- Количеството трябва да бъде по -голямо от 50 (> 50).
Горепосочените 4 условия или критерии трябва да бъдат съобразени. Функцията AVERAGEIFS може да ни помогне да извлечем средната стойност с различни критерии.
Използвайте формулата:
= СРЕДНИ (Количество, дата на поръчка, "<=" & F4, град, "Бостън", продукт, F5, Количество, F6)
Количество: именован диапазон, използван за масив D2: D50
Order_date: именен диапазон, използван за масив A2: A50
Град: именен диапазон, използван за масива B2: B50
Продукт: именован диапазон, използван за масив C2: C50
F4: референтна клетка, използвана за критерии 1
F5: референтна клетка, използвана за критерии 2
F6: референтна клетка, използвана за критерии 3
Обяснение:
- date_date, "<=" & F4първи критерии, които предполагат дата преди 31.01.2019 г. във F4.
- град, "Бостън" втори критерии, при които градът съвпада с „Бостън“
- продукт, F5 където продуктът съвпада с морков в клетка F5
- Количество, трябва да бъде под критерии в F6 клетка т.е. над 50.
Тук A2 е даден като референтна клетка и именовани диапазони, дадени като rng (D2: D51) и дата на поръчка (A2: A51).
Както можете да видите в горната снимка, СРЕДНАТА за количеството през януари се оказва 54.00. Тъй като има само една стойност, отговаряща на всички условия.
Пример 2:
Тук сме дали данни от A1: D51. Трябва да намерим СРЕДНАТА на полученото количество по различни критерии.
Условията са следните.
- Датата трябва да бъде преди 27.02.2019 г.
- Град "Ню Йорк"
- Продуктът трябва да бъде определен като „шоколадов чипс“.
- Количеството трябва да бъде по -малко от 50 (<50).
Горепосочените 4 условия или критерии трябва да бъдат съобразени. Функцията AVERAGEIFS може да ни помогне да извлечем средната стойност с различни критерии.
Използвайте формулата:
= СРЕДНИ (Количество, дата на поръчка, "<=" & F4, град, "Ню Йорк", продукт, F5, Количество, F6)
Количество: именован диапазон, използван за масив D2: D50
Order_date: именен диапазон, използван за масив A2: A50
Град: именен диапазон, използван за масива B2: B50
Продукт: именован диапазон, използван за масив C2: C50
F4: референтна клетка, използвана за критерии 1
F5: референтна клетка, използвана за критерии 2
F6: референтна клетка, използвана за критерии 3
Обяснение:
- date_date, "<=" & F4първи критерий, който предполага дата преди 27.02.2019 г. в клетка F4.
- град, "Бостън" втори критерии, при които градът съвпада с „Бостън“
- продукт, F5 където продуктът съвпада с морков в клетка F5
- Количество, трябва да бъде под критерии в F6 клетка т.е. по -малко от 50.
Тук A2 е даден като референтна клетка и именовани диапазони, дадени като rng (D2: D51) и дата на поръчка (A2: A51).
Както можете да видите в горната снимка, СРЕДНАТА за количеството през януари се оказва 33.33… . Тъй като има само една стойност, отговаряща на всички условия.
Ето някои наблюдения относно използването на формулата по -долу.
Бележки:
- Функцията връща #VALUE! грешка, ако аргументът на функцията е нецифрен.
- Функцията връща #VALUE! грешка, ако никоя стойност не отговаря на дадените критерии.
- Формулата приема оператори като =, <, =, <= &.
- Недей предоставете аргумент за дата директно на функцията. Използвайте функцията DATE или използвайте препратка към клетка за аргумент за дата в Excel, тъй като Excel чете дата само с правилния ред.
Надявам се тази статия за това как да се осреднява с множество критерии в Excel е обяснима. Намерете още статии за функциите SUMPRODUCT тук. Моля, споделете вашето запитване по -долу в полето за коментар. Ние ще ви съдействаме.
Как да използвате функцията AVERAGEIFS в Excel
Как да използвате функцията DAVERAGE в Excel
Как да подчертаем клетки над и под средната стойност
Игнорирайте нулата в средната стойност на числата
Изчислете среднопретеглена стойност
Средна разлика между списъците
Валидиране на текстови записи
Създайте падащ списък в Excel с цвят
Популярни статии
Редактиране на падащ списък
Абсолютна справка в Excel
Ако с условно форматиране
Ако с заместващи символи
Преглед по дата
Присъединете се към собствено и фамилно име в Excel
Пребройте клетки, които съответстват или на А, или на В
Конвертирайте инчове в крака и инчове в Excel 2016
50 Преки пътища в Excel за повишаване на вашата производителност