Среднее значение в excel

Поиск минимального или максимального значения по условию

В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF) , СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)?

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

Таким образом, условием будет наименование товара (бумага, карандаши, ручки), а диапазоном для выборки – столбец с ценами.

Для будущего удобства, конвертируем исходный диапазон с ценами в “умную таблицу”. Для этого выделите его и выберите на вкладке Главная – Форматировать как таблицу (Home – Format as Table) или нажмите Ctrl+T. Наша “поумневшая” таблица автоматически получит имя Таблица1, а к столбцам можно будет, соответственно, обращаться по их именам, используя выражения типа Таблица1 или Таблица1. При желании, стандартное имя Таблица1 можно подкорректировать на вкладке Конструктор (Design) , которая появляется, если щелкнуть в любую ячейку нашей “умной” таблицы. Подробнее о таких таблицах и их скрытых возможностях можно почитать здесь.

Способ 1. Функции МИНЕСЛИ и МАКСЕСЛИ в Excel 2016

Начиная с версии Excel 2016 в наборе функции Microsoft Excel наконец появились функции, которые легко решают нашу задачу – это функции МИНЕСЛИ (MINIFS) и МАКСЕСЛИ (MAXIFS) . Синтаксис этих функции очень похож на СУММЕСЛИМН (SUMIFS) :

=МИНЕСЛИ( Диапазон_чисел ; Диапазон_проверки1 ; Условие1 ; Диапазон_проверки2 ; Условие2 . )

• Диапазон_чисел – диапазон с числами, из которых выбирается минимальное или максимальное
• Диапазон_проверки – диапазон, который проверяется на выполнение условия
• Условие – критерий отбора

Например, в нашем случае:

Просто, красиво, изящно. Одна проблема – функции МИНЕСЛИ и МАКСЕСЛИ появились только начиная с 2016 версии Excel. Если у вас (или тех, кто будет потом работать с вашим файлом) более старые версии, то придется шаманить другими способами.

Способ 2. Формула массива

В английской версии это будет, соответственно =MIN(IF(Table1=F4;Table1))

Не забудьте после ввода этой формулы в первую зеленую ячейку G4 нажать не Enter , а Ctrl + Shift + Enter , чтобы ввести ее как формулу массива. Затем формулу можно скопировать на остальные товары в ячейки G5:G6.

Давайте разберем логику работы этой формулы поподробнее. Функция ЕСЛИ проверяет каждую ячейку массива из столбца Товар на предмет равенства текущему товару (Бумага). Если это так, то выдается соответствующее ему значение из столбца Цена. В противном случае – логическое значение ЛОЖЬ (FALSE) .

Таким образом внешняя функция МИН (MIN) выбирает минимальное не из всех значений цен, а только из тех, где товар был Бумага, т.к. ЛОЖЬ функцией МИН игнорируется. При желании, можно выделить мышью всю функцию ЕСЛИ(…) в строке формул

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

Способ 3. Функция баз данных ДМИН

Этот вариант использует малоизвестную (и многими, к сожалению, недооцененную) функцию ДМИН (DMIN) из категории Работа с базой данных (Database) и требует небольшого изменения результирующей таблицы:

Как видите, зеленые ячейки с результатами транспонированы из столбца в строку и над ними добавлена мини-таблица (F4:H5) с условиями. Логика работы этой функции следующая:

• База_данных – вся наша таблица вместе с заголовками.
• Поле – название столбца из шапки таблицы, из которого выбирается минимальное значение.
• Критерий – таблица с условиями отбора, состоящая (минимально) из двух ячеек: названия столбца, по которому идет проверка (Товар) и критерия (Бумага, Карандаши, Ручки).

Это обычная формула (не формула массива), т.е. можно вводить и использовать ее привычным образом. Кроме того, в той же категории можно найти функции БДСУММ (DSUM) , ДМАКС (DMAX) , БСЧЁТ (DCOUNT) , которые используются совершенно аналогично, но умеют находить не только минимум, но и сумму, максимум и количество значений по условию.

Способ 4. Сводная таблица

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

Установите активную ячейку в любое место нашей умной таблицы и выберите на вкладке Вставка – Сводная таблица (Insert – Pivot Table) . В появившемся окне нажмите ОК:

В конструкторе сводной таблицы перетащите поле Товар в область строк, а Цену в область значений. Чтобы заставить сводную вычислять не сумму (или количество), а минимум щелкните правой кнопкой мыши по любому числу и выберите в контекстном меню команду Итоги по – Минимум:

СРЗНАЧЕСЛИ (функция СРЗНАЧЕСЛИ)

​ и 10 будет​ текста, которое определяет​ ячейку. После того,​​ расположенную справа от​​ Excel​

Синтаксис

​ Чтобы быстро найти функцию,​

​ начните вводить ее​ Чтобы выделить несмежные ячейки,​

• ​Нажмите клавиши​​, чтобы открыть панель​ с текстом «Средняя​750​ более 250 000. Этому​ 5, которое является​ ячейки, используемые при​

• ​ как открылось окно​​ поля ввода данных.​Самый простой и известный​ красным цветом содержатся​ поиск по прописанному​груша груши​ качестве критерия функции​ начните вводить ее​ имя в поле​ щелкните их, удерживая​+C.​

• ​Построитель формул​​ цена изделия».​9​ условию удовлетворяют два​ результатом деления их​ вычислении среднего. Например,​

Замечания

​ аргументов функции, нужно​После этого, окно аргументов​ способ найти среднее​

​ численные значения оценок​ во втором аргументе​Использован подстановочный знак ?​

​ СРЗНАЧЕСЛИ() будем использовать​ имя в поле​Поиск функции​ нажатой клавишу​Выделите на листе ячейку​

​.​На вкладке​30​ значения, сумма которых​

​ суммы, равной 30,​ условие может быть​ ввести её параметры.​ функции свернется, а​ арифметическое набора чисел​

​ по предметам. В​ условию.​Для того чтобы найти​ слово «яблоки».​Поиск функции​

Например, начните вводить​.​ A1, а затем​В списке​Формулы​200​ составляет 49 000.​ на их количество,​

​ выражено следующим образом:​ В поле «Диапазон»​ вы сможете выделить​ — это воспользоваться​ столбце «Средний балл»​Внимание! Критерий поиска можно​ среднее значение в​=СРЗНАЧЕСЛИ($A$6:$A$16;»яблоки»;$B$6:$B$16)​. Например, начните вводить​СУММПРОИЗВ​В строке состояния щелкните​ нажмите клавиши​построителя формул​

​нажмите кнопку​	​10​	​24500​
​ равное 6.​	​ 32, «32», «>32»,​	​ вводим диапазон ячеек,​
​ ту группу ячеек​	​ специальной кнопкой на​	​ требуется подсчитать их​
​ указать в ячейке.​	​ Excel (при том​	​При расчете среднего, функция​
​СРЗНАЧЕСЛИ​	​.​	​ стрелку всплывающего меню​

​+V.​​дважды щелкните функцию​Вставить функцию​40​Регион​Медиана​ «яблоки» или B4.​ значения которых будут​

​ на листе, которую​​ ленте Microsoft Excel.​ среднее значение.​ А в формуле​ неважно числовое, текстовое,​ СРЗНАЧЕСЛИ() учтет только​.​В разделе​и выберите пункт​Рассчитаем среднее качество изделий​СРЗНАЧЕСЛИ​, чтобы открыть панель​300​Доходы (в тысячах)​    — это число, которое​Диапазон_усреднения.​

​ участвовать в определении​​ берете для расчета.​ Выделяем диапазон чисел,​Проблема вот в​ сделать на нее​ процентное или другое​ значения 2; 4;​В разделе​Аргументы​Среднее​ двумя разными способами.​.​Построитель формул​8​

​Восточный​​ является серединой множества​    Необязательный. Фактическое множество ячеек​ среднего арифметического числа.​ Затем, опять нажимаете​ расположенных в столбце​ чем: всего предметов​ ссылку.​ значение) существует много​. ​ 5; 6; 8;​Аргументы​щелкните поле рядом​.​ Первый способ позволяет​Совет:​.​

​ 5; 6; 8;​Аргументы​щелкните поле рядом​.​ Первый способ позволяет​Совет:​.​

Примеры

​27​45678​ чисел, то есть​ для вычисления среднего.​ Делаем это тем​ на кнопку слева​ или в строке​ 60-70 и часть​Найдем среднее значение чисел​ функций. И каждая​ 10; 11, т.е.​щелкните поле рядом​ с надписью​

​В приведенном примере результат​	​ быстро узнать среднее​
​ Чтобы быстро найти функцию,​	​В списке​
​400​	​Западный​
​ половина чисел имеют​	​ Если этот параметр​
​ же способом, как​	​ от поля ввода​
​ документа. Находясь во​	​ из них на​	​ по текстовому критерию.​
​ из них обладает​	​ значения в строках​ с надписью​массив1​ будет равен 8.​ значение, не вводя​ начните вводить ее​	​построителя формул​
​5​	​23789​ значения большие, чем​ не указан, используется​ и с функцией​ данных, чтобы вернуться​ вкладке «Главная», жмем​	​ другом листе.​
​ Например, средние продажи​	​ своими особенностями и​ 6, 9-14. В​диапазон​и выделите на​Примечание:​ формулу. Второй способ​ имя в поле​дважды щелкните функцию​20​Северный​	​ медиана, а половина​
​ диапазон.​	​ «СРЗНАЧ».​ в окно аргументов​ на кнопку «Автосумма»,​Я смотрела в​ товара «столы».​ преимуществами. Ведь в​	​ этих строках в​

Пример 2

​и выделите на​	​ листе ячейки с​
​ Если строка состояния не​	​ предполагает использование функции​
​Поиск функции​	​СУММПРОИЗВ​
​200​	​-4789​
​ чисел имеют значения​	​Ячейки в диапазоне, которые​
​А вот, в поле​	​ функции.​
​ которая расположена на​	​ другом документе уже​	​Функция будет выглядеть так:​
​ данной задаче могут​	​ столбце​ листе ячейки с​ B2 по B7​	​ отображается, в меню​
​ «Автосумма» для расчета​	​. Например, начните вводить​.​Среднее качество изделий​Южная (новое представительство)​	​ меньшие, чем медиана.​

support.office.com>

Расчет средней арифметической в Excel

Оно-то, конечно, так, Excel считает по формуле, но вид формулы и результат сильно зависят от исходных данных. А исходные данные бывают очень разные, в том числе и динамические, то есть изменчивые.

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

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

Есть и стандартный для всех функций способ вызова. Нужно нажать на кнопку fx в начале строки, где прописываются функции (формулы) и тем самым вызвать Мастер функций. Снова жмем на «Ввод» или «Ок» Результат расчета отразиться в ячейке с формулой.

Коэффициент вариации: формула и расчет в Excel и интерпретация результатов

​.​ значением. В связи​В предварительно выделенной ячейке​ генеральной совокупности происходит​ в общей массе​Условием для нахождения среднего​ «Z» можно сократить.​ вариации, который характеризует​ продажах за год​ отклонения, стоит задуматься:​

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

Как рассчитать коэффициент вариации в Excel

​ что не так.​ для предприятия В​ Позволяет оценить отношение​ содержащую числовой ряд,​ для занесения их​ приложении Excel имеются​

​ выбираем пункт​

• ​ Экселе можно с​
• ​ значение и показать​ поменять формат ячейки​
• ​ выбранного вида стандартного​ выборке, на два​

​Различают среднеквадратическое отклонение по​ числовой критерий или​ по этим наименованиям​ вокруг средней величины.​ Необходимо ранжировать ассортимент​Условия для применения ABC-анализа:​ – 50%, для​ между среднеквадратическим отклонением​ на листе. Затем​ координат в окно​ функции, позволяющие автоматизировать​«Статистические»​ помощью двух специальных​ результат на экране​

​ на соответствующий. Это​ отклонения.​ отдельных варианта:​ генеральной совокупности и​ текстовый. Будем использовать​ на предварительный заказ.​Коэффициент вариации – относительный​ по доходу (какие​анализируемые объекты имеют числовую​

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

​ можно сделать после​Урок:​СТАНДОТКЛОН.Г​

​ по выборке. В​ функцию: =СРЗНАЧЕСЛИ().​

​Для того чтобы найти​ показатель, не имеющий​ товары дают больше​ характеристику;​

​ 33%. Риск инвестирования​ в относительном выражении.​«OK»​«Число2»​ алгоритм работы с​ делаем выбор между​

​СТАНДОТКЛОН.В​ кнопке​ её выделения, находясь​Формула среднего квадратичного отклонения​и​ первом случае это​

Интерпретация результатов

​ среднее значение в​ конкретных единиц измерения.​ прибыли).​список для анализа состоит​ в ценные бумаги​ Соответственно, сопоставить полученные​.​

​,​ этими инструментами.​

​ значениями​(по выборочной совокупности)​Enter​

​ во вкладке​ в Excel​СТАНДОТКЛОН.В​ корень из генеральной​ которые больше или​ Excel (при том​ Достаточно информативный. Даже​Отсортируем данные в таблице.​ из однородных позиций​ фирмы В выше​ результаты.​Результат вычисления будет выведен​«Число3»​Скачать последнюю версию​

​СТАНДОТКЛОН.В​ и​.​«Главная»​Среднее арифметическое является отношением​.​ дисперсии

Во втором​ равны 10.​ неважно числовое, текстовое,​ сам по себе.​ Выделяем весь диапазон​ (нельзя сопоставлять стиральные​ в 1,54 раза​При принятии инвестиционного решения​ в отдельную ячейку.​и т.д. После​ Excel​или​

​СТАНДОТКЛОН.Г​Существует условное разграничение. Считается,​. Кликаем по полю​ общей суммы всех​Синтаксис данных функций выглядит​ – из выборочной​Функция: =СРЗНАЧЕСЛИ(A1:A8;»>=10″)​ процентное или другое​ НО! Тенденция, сезонность​ (кроме шапки) и​

exceltable.com>

Как вычислять среднее значение ряда чисел

​ формуле. В этом​«Описательная статистика»​ Для решения нашего​. Чтобы перейти к​ них как раз​ формулой. Величина стандартной​«Другие функции…»​ данному наименованию. В​ 255 аргументов этого​ рассмотрим каждый из​ имейте в виду,​

​ первых и последнего​ больше, чем Медиана,​ Мы стараемся как можно​: для определения среднего​ такие функции вроде​ согласно с логическим​ случае результат расчета​

​установлены, щелкаем по​​ вопроса это не​ ней, щелкаем по​ и является нахождение​ ошибки выведена в​.​ обратном случае жмите​ типа

Обязательным является​ этих вариантов.​ что в этом​ чисел списка (7,5)​

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

​ по пункту​​ только первый аргумент.​Прежде всего, давайте составим​ примере чтобы проще​=СРЗНАЧ(A2:A7;»0″)​ имеют значения, которые​ актуальными справочными материалами​. ​ ячейку столбиком пишите​ $, а вот​ критерии отбора

При​ ячейке листа.​«OK»​Далее переходим к блоку​«Данные»​Но чтобы воспользоваться данной​

​ ячейку столбиком пишите​ $, а вот​ критерии отбора. При​ ячейке листа.​«OK»​Далее переходим к блоку​«Данные»​Но чтобы воспользоваться данной​

Вычисление среднего значения ячеек, расположенных вразброс

​ общий вид которой​производим перемещение в​​.​​ поле​ конкретном примере по​ его на пустой​

​ чисел списка, кроме​	​ Например медиана 2,​
​ Эта страница переведена​	​ сложить с другим​ написать не знаю.​
​ найдется ни одной​	​Для расчета среднего значения​ части.​«Параметры вывода»​
​После перехода жмем на​	​ активировать​ в нашем случае​ группу​Снова происходит запуск​«Число1»​

Вычисление среднего взвешенного значения

​ расчету ошибки средней​ лист.​​ нулевых, таких как​​ 3, 3, 5,​​ автоматически, поэтому ее​​ числом. Ставите автосумму​Дмитрий медведев​ ячейки удовлетворяющей условие​ по условию в​После этого инструмент​. Здесь следует указать,​ кнопку​«Пакет анализа»​ следующий:​

​«Статистические»​Мастера функций​. Далее, обязательно произведя​

​ арифметической, используя для​ ​Формула​ ​ в ячейке A6​ ​ 7 и 10​

​ текст может содержать​ ​ и делите на​ ​: 1 вопрос. Вызвать​ ​ выборки, тогда функция​ ​ Excel используется функция​ ​«Описательная статистика»​ ​ куда именно будет​ ​«Анализ данных»​ ​, так как по​ ​=СТАНДОТКЛОН.В(B2:B13)/КОРЕНЬ(СЧЁТ(B2:B13))​ ​. Там выделяем наименование​ ​. На этот раз​ ​ зажим левой кнопки​ ​ этих целей комбинацию​Описание (результат)​ (11,4)​ — 4.​

Вычисление среднего значения чисел, без учета нулевых значений

​ неточности и грамматические​ то число, сколько​​ функцию СРЗНАЧ ()​​ СРЗНАЧЕСЛИ возвращает ошибку​​ СРЗНАЧЕСЛИ. Кроме суммирования​​выводит результаты обработки​ выводиться результат расчета​в блоке инструментов​ умолчанию в Экселе​Результат вычисления ошибки средней​«СЧЁТ»​ нам следует посетить​ мыши, выделяем курсором​

​ функций. Для выполнения​	​=СРЗНАЧЕСЛИ(A2:A7;»<>0″)​
​Для выполнения этой задачи​	​Режим​ ошибки. Для нас​ столбиков с цифрами,​ — в скобках​ #ДЕЛ/0!​

Расчет среднего квадратичного отклонения в Microsoft Excel

​ их содержат. Все​«OK»​Сразу определим, что же​Выделяем предварительно отформатированную под​«OK»​ содержаться, как конкретные​ отдельно функции для​ этой цели подойдут​ тот же принцип​ Формула расчета изменчивости​ 95% — В.​

Определение среднего квадратичного отклонения

​Ожидаемая доходность ценных бумаг​ другого – низкий​.​«Полный алфавитный перечень»​ расчеты выполняет сама​.​ представляет собой среднеквадратичное​ процентный формат ячейку,​.​ числа, так и​ вычисления этого показателя,​ функции СУММПРОИЗВ и​ нахождения среднего арифметического.​ объема продаж: =СТАНДОТКЛОНП(B3:H3)/СРЗНАЧ(B3:H3).​

​ Остальное – С.​Найти значение в перечне,​ группы.​ составит:​ уровень риска.​В категории​выполняем поиск аргумента​ программа. Намного сложнее​

Расчет в Excel

​Результат расчета будет выведен​ отклонение и как​ в которой будет​Результат вычисления среднего арифметического​​ ссылки на ячейки​​ но имеются формулы​ СУММ. Таблица для​​ Но функцию СРЗНАЧ​​Классифицируем значения – определим​Чтобы было удобно пользоваться​ в котором доля​Значения в перечне после​Среднеквадратическое отклонение доходности для​Коэффициент вариации представляет собой​«Полный алфавитный перечень»​

Способ 1: мастер функций

1. ​ с наименованием​ осознать, что же​ в ту ячейку,​ выглядит его формула.​​ выведен результат. Прописываем​​ выводится в ту​ или диапазоны. Ставим​

2. ​ для расчета стандартного​ примера:​​ мы вызовем по-другому.​​ товары в группы​​ результатами анализа, проставляем​​ нарастающим итогом близко​ применения метода ABC​​ активов компании А​​ отношение среднеквадратического отклонения​или​«ДИСП.Г»​ собой представляет рассчитываемый​ которая была выделена​ Эта величина является​​ в ней формулу​​ ячейку, которая была​

3. ​ курсор в поле​ отклонения и среднего​Как мы узнали средневзвешенную​ С помощью мастера​ «X», «Y» или​ напротив каждой позиции​ к 95% (+15%).​ распределяются в три​ и В составляет:​ к среднему арифметическому.​«Статистические»​. После того, как​ показатель и как​ в самом начале​​ корнем квадратным из​​ по типу:​

4. ​ выделена перед открытием​«Число1»​ арифметического ряда чисел,​ цену?​ функций (кнопка fx​ «Z». Воспользуемся встроенной​

Способ 2: вкладка «Формулы»

​ соответствующие буквы.​ Это нижняя граница​ группы:​​Ценные бумаги компании В​​ Для расчета в​

1. ​ищем наименование​ нашли, выделяем его​ результаты расчета можно​​ процедуры поиска среднего​​ среднего арифметического числа​

2. ​= СТАНДОТКЛОН.В(диапазон_значений)/СРЗНАЧ(диапазон_значений)​​Мастера функций​​. Мышью выделяем на​​ а именно они​​Формула: =СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).​ или комбинация клавиш​​ функцией «ЕСЛИ»: =ЕСЛИ(I3​​Вот мы и закончили​ группы В.​А – наиболее важные​​ имеют более высокую​​ статистике используется следующая​​«ДИСП.В»​​ и щелкаем по​ применить на практике.​ квадратичного отклонения.​ квадратов разности всех​

3. ​Вместо наименования​.​ листе тот диапазон​ используются для нахождения​С помощью формулы СУММПРОИЗВ​ SHIFT+F3).​

Способ 3: ручной ввод формулы

​В группу «Х» попали​ АВС-анализ с помощью​Для С – все,​ для итога (20%​ ожидаемую доходность. Они​ формула:​

1. ​. После того, как​ кнопке​ Но постижение этого​Также рассчитать значение среднеквадратичного​ величин ряда и​«Диапазон значений»​

   ​ значений, который нужно​

   ​ мы узнаем общую​Третий способ вызова функции​ товары, которые имеют​

2. ​ средств Excel. Дальнейшие​ что ниже.​ дает 80% результата​​ превышают ожидаемую доходность​​CV = σ / ǩ,​

​ формула найдена, выделяем​​«OK»​ уже относится больше​

​ отклонения можно через​ их среднего арифметического.​вставляем реальные координаты​Как посчитать среднее значение​ обработать. Если таких​Стандартное отклонение, или, как​ выручку после реализации​ СРЗНАЧ из панели:​ самый устойчивый спрос.​ действия пользователя –​Посчитать число значений для​ (выручки, к примеру)).​ компании А в​CV – коэффициент вариации;​ её и делаем​.​ к сфере статистики,​ вкладку​ Существует тождественное наименование​ области, в которой​ в Excel​

​ областей несколько и​

lumpics.ru>

Как сделать выборку средних значений по условию в Excel

Примеры использования функции СРЗНАЧЕСЛИМН в Excel.

Пример 1. Студенты, изучавшие некоторый предмет, на протяжении семестра зарабатывали баллы (до 100 баллов). При этом те, кто набрал 49 и менее баллов считается не сдавшим предмет. Определить средний балл для студентов, которые набрали 50 и более баллов (сдали).

Вид таблицы данных:

Для определения искомого значения используем формулу:

=СРЗНАЧЕСЛИМН(B3:B17;C3:C17;»сдал»)

Описание аргументов:

• B3:B17 – диапазон усреднения (оценки всех студентов);
• C3:C17 – диапазон, на основе данных которого будет проверяться условие;
• «сдал» — условие (проверка каждой ячейки на соответствие хранящихся в ней данных заданной текстовой строке).

В результате получим:

В результате была сделана выборка средних значений только для студентов, сдавших семестр.

Статистические функции Excel, которые необходимо знать

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

​ на 6, 5;​ СРЗНАЧ (диапазон значений).​ сделать на нее​ собственную формулу. Рассмотрим​ формулой =СМЕЩ(Лист1!$A$5;;;СЧЁТЗ(Лист1!$A$5:$A$19))​ нечетное количество значений,​ арифметическое значений, которые​СРЗНАЧЕСЛИ​ листе ячейки с​ заменится ссылками на​ значение, не вводя​+C.​Щелкните поле рядом с​

​ надписью​На вкладке​ среднего или среднего,​ за единицу.​Медиана​​Коэффициент вариации считается в​​ ссылку.​​ различные варианты.​​Соответственно, среднее последних 5​ то функция возвращает​

СРЗНАЧ()

​ удовлетворяют определенному условию,​​для расчета среднего​​ C2 по C7​ выделенные ячейки. В​

​ формулу. Второй способ​Выделите на листе ячейку​ надписью​массив2​Формулы​ исключающего определенные значения.​

​Скопируйте в приведенной ниже​     Средний номер ряда​ процентах. Поэтому в​Найдем среднее значение чисел​Чтобы найти среднее арифметическое,​ значений можно вычислить​ то, что находится​​ то можно воспользоваться​

​ без нулевых значений,​ (значения в столбце​ приведенном примере результат​ предполагает использование функции​​ A1, а затем​​диапазон​и выделите на​щелкните стрелку рядом​Более новые версии​​ таблице на пустой​​ чисел. Половина чисел​

​ ячейке устанавливаем процентный​​ по текстовому критерию.​​ необходимо сложить все​ так:​ ровно по середине.​ статистической функцией​

СРЗНАЧЕСЛИ()

​ введите выражение​ «Количество изделий»).​ будет равен 8.​ СРЗНАЧ для расчета​ нажмите клавиши​​и выделите на​​ листе ячейки с​ с кнопкой​ Office 2011 г.​

​ лист.​ имеют значения, которые​ формат.​ Например, средние продажи​ числа в наборе​=СРЗНАЧ(СМЕЩ($A$5;СЧЁТЗ(Список)-5;0;5))​ Если же количество​СРЗНАЧЕСЛИ​​»<>0″​​Теперь в строке формул​В приведенном ниже примере​ среднего значения и​+V.​ листе ячейки с​ C2 по C7​Автосумма​

​ ​1​ больше, чем Медиана,​olpana​ товара «столы».​ и разделить сумму​Если в столбец добавить​ значений четное, то​. Следующая формула вычисляет​в поле​

​ должна содержаться следующая​ рассчитывается средняя цена​ позволяет вывести его​Рассчитаем среднее качество изделий​​ A2 по A7​​ (значения в столбце​, выберите пункт​Чтобы научиться вычислять средние​2​

​ а половина чисел​​: добрый день,​​Функция будет выглядеть так:​ на количество. Например,​

​ еще значения, то​​ функция возвращает среднее​​ среднее чисел, которые​условие​

НАИБОЛЬШИЙ()

​ формула: =СУММПРОИЗВ(B2:B7;C2:C7)/СУММ(C2:C7).​ за изделие по​ на листе.​ двумя разными способами.​ (значения в столбце​ «Количество заказанных изделий»).​Среднее​

​ значения, используйте предоставленные​3​ имеют значения, которые​

НАИМЕНЬШИЙ()

​подскажите, пожалуйста, как​ =СРЗНАЧЕСЛИ($A$2:$A$12;A7;$B$2:$B$12). Диапазон –​ оценки школьника по​ функция СМЕЩ() автоматический​ для двух чисел.​ больше нуля:​

​.​Нажмите клавишу RETURN.​ всем заказам, каждый​Быстрый расчет среднего​

МЕДИАНА()

​ Первый способ позволяет​​ «Цена за единицу»).​​В строке формул установите​и нажмите RETURN.​ образцы данных и​4​ меньше, чем Медиана.​ написать формулу, чтобы​ столбец с наименованиями​ информатике: 3, 4,​ вернет ссылку на​Например, на рисунке ниже​В данном примере для​Функции категории​В этой формуле общая​ из которых содержит​

​Выделите ячейки, для которых​ быстро узнать среднее​Щелкните поле рядом с​ курсор справа от​

​Щелкните ячейку, которая содержит​ описанные ниже процедуры.​A​ Например медиана 2,​

МОДА()

​ найти среднее значения​ товаров. Критерий поиска​ 3, 5, 5.​

​ диапазон, содержащий 5​ формула возвращает медиану​ подсчета среднего и​Статистические​

​ стоимость всех заказов​​ различное количество изделий​​ вы хотите найти​ значение, не вводя​ надписью​ закрывающей скобки формулы​ только что найденное​​Чтобы лучше понять описываемые​​B​​ 3, 3, 5,​​ из списка с​ – ссылка на​

​ Что выходит за​​ последних значений, с​​ для списка, состоящего​ проверки условия используется​предназначены в первую​ делится на общее​ по разной цене.​ среднее значение. Например,​ формулу. Второй способ​условие​ и введите​ среднее значение (ячейка​ действия, скопируйте пример​Цена единицы товара​ 7 и 10​ выполнением двух обязательных​ ячейку со словом​ четверть: 4. Мы​ учетом только что​ из 14 чисел.​ один и тот​ очередь для анализа​ количество заказанных изделий,​

​Выделите ячейку A9, расположенную​

office-guru.ru>

Задачи

Предположим, что в диапазоне A 5:D11 имеется таблица продаж ( База_данных ), содержащая Поля (столбцы) Товар , Продавец, Продажи и Дата (см. файл примера и рисунок выше).

Сформулируем задачи в виде вопросов.

Вопрос 1 (Определить максимальные продажи Белова ). Т.е. найдем строки, в которой в столбце Продавец содержится значение Белов . Если такие строки есть в таблице, то для них выведем максимальное значение из столбца Продажи .

• Создадим в диапазоне G5:G6 табличку критериев (желательно над исходной таблицей или сбоку, чтобы она не мешала добавлению новых данных в таблицу), состоящую из заголовка (совпадает с названием заголовка столбца исходной таблицы, к которому применяется критерий) и собственно критерия (условия отбора);
• Условия отбора должны быть записаны в специальном формате: =”=Белов” (будет производиться поиск в столбце Продавец только значений точно совпадающих со словом Белов (или белов , БЕЛОВ , т.е. без учета регистра ). Если написать условие не в виде =”=Белов”, а просто ввести в ячейку значение Белов , то условию будут удовлетворять текстовые строки, которые содержат слово Белов , например, « Белов Иван», «Иван Белов» и пр.)

Предполагаем, что База_данных (исходная таблица) находится в A 5:D11 . С5 – это ссылка на заголовок столбца, в котором ищется максимальное значение, если выполняется условие. G5:G6 – ссылка на табличку критериев (см. рисунок выше).

Итоговая формула выглядит так =ДМАКС(B5:C11;C5;G5:G6)

Как видно из исходной таблицы, условиям удовлетворяет 3 строки, поэтому результатом будет максимальное значение из массива (450; 6544; 1245), т.е. 6544.

Альтернативное решение – формула =НАИБОЛЬШИЙ(ЕСЛИ(B6:B11=G6;C6:C11;””);1) , которая вводится как формула массива ( подробнее см. здесь ). В случае, когда ни одна строка не удовлетворяет условиям, то формула вернет ошибку #ЧИСЛО! в отличие от ДМАКС() , которая вернет 0 (это может ввести в заблуждение, т.к. не понятно есть ли в исходной таблице строки, удоблетворяющие критерию или нет. См. раздел Недостаток функции ).

Вопрос 2 (Последняя продажа Белова). В какой день Белов продал последнюю партию товара?

Решение аналогично предыдущей задаче, только максимальное значение ищется не в столбце С ( Продажи ), а в столбце D ( Дата ).

Вопрос 3 (Максимальные продажи Мяса продавцом Белов)

В данном случае таблица критериев представляет собой 2 столбца Товар и Продавец .

Вопрос 4 (Максимальные продажи среди продаж Белова или Батурина)

В данном случае таблица критериев представляет собой 1 столбец Продавец с 2-мя строками (с фамилиями продацов Белов и Батурин ).

В файле примера приведено решение и других задач.

Расчет среднего чека для выбранного товара по условию в Excel

Пример 2. Перед продавцами магазина стоят следующие задачи по продажам:

• количество товаров в чеке – не менее 3 единиц;
• средняя цена в чеке – 40 у. е.;
• обязательная продажа продукции фирмы Adidas.

Определить, соответствуют ли данные по продажам за день указанным условиям для каждого продавца.

Вид таблицы данных:

Для расчетов используем следующую формулу:

Описание аргументов:

• B3:B13 – диапазон ячеек с ценами в чеке;
• A3:A12 – с номерами продавцов;
• C15 – ячейка с номером продавца как критерий выбора;
• C3:C12 – с количеством единиц товаров в чеке;
• «>=»&C17 – критерий выбора (не менее числа, указанного в ячейка C17, в данном примере – не менее 3 единиц);
• D3:D12 – с названиями брендов;
• «*»&C18&»*» – критерий выбора по названию бренда (так как наименования перечислены через запятую, для нахождения подстроки в строке используем символы «*» для указания любого числа любых символов в начале и в конце исследуемой строки).

Результат вычислений для первого продавца:

Для второго продавца:

Как видно, оба продавца справились с поставленной задачей.

MS Excel: Вычисление среднего значения одной формулой

​На панели инструментов в​​ в которых находятся​ которой будет выводиться​ ячеек.​​ констант, тогда формула​​ нравится​ есть посчитав все​ данных, рассчитывается коэффициент​ в первом аргументе,​​ преимуществами. Ведь в​​ обычного среднеарифметического, каждое​ функцией​​ таблицах, таких как​​ можно использовать критерии​ (например, вычислим среднее​​ на синтаксис формулы,​​ специального инструмента. Во​​ категории «Редактирование» нажмите​​ числа большие или​ результат. После этого,​Для случаев, когда нужно​​ не будет нуждатся​​Я получаю от​(А-Б+С) = Х​ вариации:​ и будет производиться​​ данной задаче могут​​ значение учитывается только​СУММПРОИЗВ​​ Google Sheets.​​ с подстановочным знаком​​ для значений,соответствующих названию​ выглядит он следующим​

​ равные 15000. При​​ в группе инструментов​ подсчитать среднюю арифметическую​ в массивном вводе:​ них эстетическое удовольствие​

​и их среднее​​среднеквадратическое отклонение / среднее​​ быть поставлены определенные​​ один раз, то​​(SUMPRODUCT) на сумму​​Если Вы собираетесь вычислять​

​ * (звездочка). Для​​ фрукта «яблоки»);​​ образом:​​ по кнопке, расположенной​ однако жать необходимо​ необходимости, вместо конкретного​ «Библиотека функций» на​ массива ячеек, или​

​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СРЗНАЧ(C3-{0:1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29}*B3+СУММЕСЛИ(D3:D6;»​​Я абсолютно серьёзно​​ значение. Файл «​ арифметическое значение​

​ во втором аргументе​​ условия.​

​ весов. Позже мы​​ среднее взвешенное, Вам​

​Найдем среднее только тех​​=СРЗНАЧ(адрес_ячеек(число); адрес_ячеек(число))​ в правой части​​ на стрелочку рядом​

​ деле она считает​

• Отклонение от среднего значения excel
• Посчитать в эксель количество значений в
• Как в excel вычислить среднее значение
• Эксель если значение то значение
• Выделить в эксель повторяющиеся значения в
• Среднее значение в excel на английском
• Как в эксель убрать формулы оставив значения
• Среднее значение в эксель формула
• Эксель посчитать количество ячеек с определенным значением
• Значение в эксель
• Excel среднее значение
• Как в эксель убрать формулу и оставить значение