Как найти среднее, минимальное и максимальное значение в excel

8379 31.03.2016 Скачать пример

В 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). В появившемся окне нажмите ОК:

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

Вытаскивать данные из сводной в дальнейшие расчеты теперь можно с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GET.PIVOT.DATA), которую мы подробно разбирали ранее:

Ссылки по теме

Источник: https://www.planetaexcel.ru/techniques/2/241/

Как найти среднее в экселе. Вычисление минимального, максимального и среднего значения в Microsoft Excel

В математике среднее арифметическое значение чисел (или просто среднее) — это сумма всех чисел в данном наборе, разделенная на их количество. Это наиболее обобщенное и распространенное понятие средней величины. Как вы уже поняли, чтобы найти нужно суммировать все данные вам числа, а полученный результат разделить на количество слагаемых.

Что такое среднее арифметическое?

Давайте рассмотрим пример.

Пример 1
. Даны числа: 6, 7, 11. Нужно найти их среднее значение.

Решение.

Для начала найдем сумму всех данных чисел.

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

Следовательно, среднее значение чисел 6, 7 и 11 — это 8. Почему именно 8? Да потому, что сумма 6, 7 и 11 будет такая же, как трех восьмерок. Это отлично видно на иллюстрации.

Среднее значение чем-то напоминает «выравнивание» ряда чисел. Как видите, кучки карандашей стали одного уровня.

Рассмотрим еще один пример, чтобы закрепить полученные знания.

Пример 2.
Даны числа: 3, 7, 5, 13, 20, 23, 39, 23, 40, 23, 14, 12, 56, 23, 29. Нужно найти их среднее арифметическое значение.

• Решение.
  
• Находим сумму.
• 3 + 7 + 5 + 13 + 20 + 23 + 39 + 23 + 40 + 23 + 14 + 12 + 56 + 23 + 29 = 330
• Делим на количество слагаемых (в этом случае — 15).
• Следовательно, среднее значение данного ряда чисел равно 22.

Теперь рассмотрим отрицательные числа. Вспомним, как их суммировать. Например, у вас есть два числа 1 и -4. Найдем их сумму.

1 + (-4) = 1 — 4 = -3

Зная это, рассмотрим еще один пример.

Пример 3.
Найти среднее значение ряда чисел: 3, -7, 5, 13, -2.

1. Решение.
   
2. Находим сумму чисел.
3. 3 + (-7) + 5 + 13 + (-2) = 12
4. Так как слагаемых 5, разделим получившуюся сумму на 5.
5. Следовательно, среднее арифметическое значение чисел 3, -7, 5, 13, -2 равно 2,4.

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

Для того чтобы посчитать среднее значение ряда чисел, необходимо использовать функцию AVERAGE. Синтаксис для этой функции: = Average (argument1, argument2, … argument255)

где argument1, argument2, … argument255 — это либо числа, либо ссылки на ячейки (под ячейками подразумеваются диапазоны и массивы).

Чтобы было более понятно, опробуем полученные знания.

1. Введите числа 11, 12, 13, 14, 15, 16 в ячейки С1 — С6.
2. Выделите ячейку С7, нажав на нее. В этой ячейке у нас будет отображаться среднее значение.
3. Щелкните на вкладке «Формулы».
4. Выберите More Functions > Statistical для того, чтобы открыть
5. Выберите AVERAGE. После этого должно открыться диалоговое окно.
6. Выделите и перетащите туда ячейки С1-С6, чтобы задать диапазон в диалоговом окне.
7. Подтвердите свои действия клавишей «ОК».
8. Если вы все сделали правильно, в ячейке С7 у вас должен появиться ответ — 13,7. При нажатии на ячейку C7 функция (= Average (C1: C6)) будет отображаться в строке формул.

Очень удобно использовать эту функцию для ведения учета, накладных или когда вам просто нужно найти среднее значение из очень длинного ряда чисел. Поэтому ее часто используют в офисах и крупных компаниях. Это позволяет сохранять порядок в записях и дает возможность быстро посчитать что-либо (например, средний доход за месяц). Также с помощью Excel можно найти среднее значение функции.

Для того чтобы найти среднее значение в Excel (при том неважно числовое, текстовое, процентное или другое значение) существует много функций. И каждая из них обладает своими особенностями и преимуществами. Ведь в данной задаче могут быть поставлены определенные условия.

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

Как найти среднее арифметическое чисел?

Чтобы найти среднее арифметическое, необходимо сложить все числа в наборе и разделить сумму на количество. Например, оценки школьника по информатике: 3, 4, 3, 5, 5. Что выходит за четверть: 4. Мы нашли среднее арифметическое по формуле: =(3+4+3+5+5)/5.

Как это быстро сделать с помощью функций Excel? Возьмем для примера ряд случайных чисел в строке:

1. Ставим курсор в ячейку А2 (под набором чисел). В главном меню – инструмент «Редактирование» — кнопка «Сумма». Выбираем опцию «Среднее». После нажатия в активной ячейке появляется формула. Выделяем диапазон: A1:H1 и нажимаем ВВОД.
2. В основе второго метода тот же принцип нахождения среднего арифметического. Но функцию СРЗНАЧ мы вызовем по-другому. С помощью мастера функций (кнопка fx или комбинация клавиш SHIFT+F3).
3. Третий способ вызова функции СРЗНАЧ из панели: «Формула»-«Формула»-«Другие функции»-«Статические»-«СРЗНАЧ».

Или: сделаем активной ячейку и просто вручную впишем формулу: =СРЗНАЧ(A1:A8).

Теперь посмотрим, что еще умеет функция СРЗНАЧ.

Найдем среднее арифметическое двух первых и трех последних чисел. Формула: =СРЗНАЧ(A1:B1;F1:H1). Результат:

Среднее значение по условию

Условием для нахождения среднего арифметического может быть числовой критерий или текстовый. Будем использовать функцию: =СРЗНАЧЕСЛИ().

• Найти среднее арифметическое чисел, которые больше или равны 10.
• Функция: =СРЗНАЧЕСЛИ(A1:A8;»>=10″)
• Результат использования функции СРЗНАЧЕСЛИ по условию «>=10»:

Третий аргумент – «Диапазон усреднения» — опущен. Во-первых, он не обязателен. Во-вторых, анализируемый программой диапазон содержит ТОЛЬКО числовые значения. В ячейках, указанных в первом аргументе, и будет производиться поиск по прописанному во втором аргументе условию.

Внимание! Критерий поиска можно указать в ячейке. А в формуле сделать на нее ссылку.

Найдем среднее значение чисел по текстовому критерию. Например, средние продажи товара «столы».

В результате вычисления функции получаем следующее значение:

Внимание! Для текстового критерия (условия) диапазон усреднения указывать обязательно.

Как посчитать средневзвешенную цену в Excel?

Как мы узнали средневзвешенную цену?

Формула: =СУММПРОИЗВ(C2:C12;B2:B12)/СУММ(C2:C12).

С помощью формулы СУММПРОИЗВ мы узнаем общую выручку после реализации всего количества товара. А функция СУММ — сумирует количесвто товара. Поделив общую выручку от реализации товара на общее количество единиц товара, мы нашли средневзвешенную цену. Этот показатель учитывает «вес» каждой цены. Ее долю в общей массе значений.

Среднее квадратическое отклонение: формула в Excel

Различают среднеквадратическое отклонение по генеральной совокупности и по выборке. В первом случае это корень из генеральной дисперсии. Во втором – из выборочной дисперсии.

Для расчета этого статистического показателя составляется формула дисперсии. Из нее извлекается корень. Но в Excel существует готовая функция для нахождения среднеквадратического отклонения.

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

1. среднеквадратическое отклонение / среднее арифметическое значение
2. Формула в Excel выглядит следующим образом:
3. СТАНДОТКЛОНП (диапазон значений) / СРЗНАЧ (диапазон значений).

Коэффициент вариации считается в процентах. Поэтому в ячейке устанавливаем процентный формат.

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

Мы даже не задумываемся, какой мощный инструмент находится на наших компьютерах, а значит, и не используем его в полную силу. Многие родители думают, что компьютер – это просто дорогая игрушка.

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

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

О быстром суммировании ячеек в Excel мы уже с вами говорили, а сегодня поговорим о среднем арифметическом.

Выделим ячейку С12
и с помощью Мастера функций
запишем в неё формулу вычисления среднего арифметического. Для этого на панели инструментов Стандартная щелкнем по кнопке —Вставка функции –
fx
(на рисунке выше красная стрелка сверху). Откроется диалоговое окно Мастер Функций
.

• Выберите в поле Категории
  — Статистические
  ;
• В поле Выберите функцию
  : СРЗНАЧ
  ;
• Нажмите кнопку ОК
  .

Откроется следующее окно Аргументы и функции
.

В поле Число1
вы увидите запись С2:С11
– программа сама определила диапазон ячеек, для которых необходимо найти среднее арифметическое.

Щелкните кнопку ОК
и в ячейке С12
появится среднее арифметическое баллов.

Оказывается, вычислить среднее арифметическое в excel совсем не сложно. А я всегда боялась всяких формул. Эх, не в то время мы учились.

С уважением, Людмила

Источник: https://ironfaq.ru/orthopedics/kak-naiti-srednee-v-eksele-vychislenie-minimalnogo-maksimalnogo-i.html

Как посчитать среднее значение в Excel

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

Как посчитать среднее значение чисел в Excel

Найти среднее арифметическое чисел в Excel можно с помощью функции СРЗНАЧ.

Синтаксис СРЗНАЧ

=СРЗНАЧ(число1;[число2];…) – русская версия

Аргументы СРЗНАЧ

• число1 – первое число или диапазон чисел, для расчета среднего арифметического;
• число2 (Опционально) – второе число или диапазон чисел для расчета среднего арифметического. Максимальное количество аргументов функции – 255.

Для расчета проделайте следующие шаги:

• Выделите любую ячейку;
• Напишите в ней формулу =СРЗНАЧ(
• Выделите диапазон ячеек, для которого требуется сделать расчет;
• Нажмите клавишу “Enter” на клавиатуре

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

Как найти среднее значение с учетом текста

Если в диапазоне данных есть текст, то рекомендую использовать функцию СРЗНАЧА. Она работает как функция СРЗНАЧ. Отличие в том, что она учитывает пустые ячейки, текст и значения ЛОЖЬ/ИСТИНА.

Если в диапазоне данных есть пустые строки или текст, то функция воспринимает их как “ноль”. Если среди данных есть логические выражения ЛОЖЬ или ИСТИНА, то ЛОЖЬ функция воспринимает как “ноль”, а ИСТИНА как “1”.

Как найти среднее арифметическое по условию

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

• В ячейке A13 напишем название товара “Ручки”;
• В ячейке B13 введем формулу:

=СРЗНАЧЕСЛИ(A2:A10;A13;B2:B10)

Диапазон ячеек “А2:A10” указывает на список товаров, в котором мы будем искать слово “Ручки”. Аргумент A13 это ссылка на ячейку с текстом, который мы будем искать среди всего списка товаров. Диапазон ячеек “B2:B10” это диапазон с данными продаж товаров, среди которых функция найдет “Ручки” и вычислит среднее значение.

Источник: https://excelhack.ru/kak-poschitat-srednee-znachenie-v-excel/

Статистические функции. Вычисление минимального, максимального и среднего значений

Синтаксис функций. Функции СЧЁТ, СЧЁТ3 и СЧЁТЕСЛИ. функции

Excel поддерживает множество стандартных математических функций, которые можно вставлять в формулы. С тремя из них — СУММ (SUM), СРЗНАЧ (AVERAGE) и СЧЕТ (COUNT) — вы познакомились на предыдущем занятии. Чтобы научиться работать с другими функциями, выполните следующие шаги.

1. Щелкните на ячейке Н8 и введите текст Максимум. Нажмите клавишу Tab.
2. Щелкните на кнопке Вставка функции (Paste Function) панели инструментов Стандартная. Откроется окно диалога, показанное на рис. 10.2. Чтобы найти нужную функцию, можно кратко описать желаемую процедуру в текстовом поле Поиск функции (Search for a function) или же выбрать ее категорию в списке категорию: (Select a Category) этого окна диалога, а затем пролистать список Выберите функцию (Select a Function Name). Выделив имя одной из функций, вы увидите ее описание в нижней части окна диалога. Excel делит все функции на следующие категории:
• Финансовые (Financial) — функции для расчета амортизации имущества, стоимости основных фондов, нормы прибыли, величины выплат на основной капитал и других финансовых показателей;
• Дата и время (Date & Time) — операции прямого и обратного преобразования даты и времени в текстовые строки. Функции этой группы перечислены в табл. 10.1;

Рис. 10.2.Вставка функции

ТАБЛИЦА 10.1.Функции обработки даты и времени

Функция	Описание
ДАТА(DATE)	Возвращает дату в формате Excel
ДАТАЗНАЧ(DATEVALUE)	Преобразует дату из текстового формата в код Excel
ДЕНЬ(DAY)	День месяца заданной даты
ДНЕЙ360 (DAYS360)	Вычисляет количество дней между двумя датами на основе З60-дневного года
ЧАС (HOUR)	Час времени, заданного аргументом
.МИНУТЫ (MINUTE)	Минута времени, заданного аргументом
МЕСЯЦ (MONTH)	Номер месяца заданной даты
J-ДАТА (NOW)	Текущая дата и время в числовом формате
СЕКУНДЫ (SECOND)	Секунда времени, заданного аргументом
ВРЕМЯ (TIME)	Преобразует время дня в дробное число от 0 до 1
ВРЕМЗНАЧ (TIMEVALUE)	Преобразует время из текстового формата в дату в числовом формате
ДЕНЬ (TODAY)	Текущая дата в числовом формате
ДЕНЬНЕД (WEEKDAY)	Номер дня недели заданной даты
ГОД(YEAR)	Год заданной даты

• Математические (Math & Trig) — математические и тригонометрические функции, некоторые из них приведены в табл. 10.2;

ТАБЛИЦА 10.2.Математические и тригонометрические функции

Функция	Описание
COS, SIN, TAN,	Тригонометрические функции
ACOS, ASIN, ATAN, ATAN2	Обратные тригонометрические функции
COSH, SINH, TANH	Гиперболические функции
ACOSH, ASINH, ATANH	Обратные гиперболические функции
LN, LOG, LOG10	Натуральный логарифм, логарифмы по основанию 2 и 10
EXP	Экспонента
НЕЧЕТ (EVEN), OKPyiTI(ROUND), ОКРУГЛВВЕРХ (ROUNDUP), ОКРУГЛВНИЗ (ROUNDDOWN), 4ETH(ODD)	Функции округления
ABS	Модуль (абсолютное значение)'числа
ГРАДУСЫ (DEGREES),	Преобразование радиан в градусы
РАДИАНЫ (RADIANS)	Преобразование градусов в радианы

ЦЕЛОЕ (INT)	Целая часть числа
ОСТАТ (MOD)	Остаток от деления
ПИ(Р1)	Число пи
СТЕПЕНЬ (POWER)	Возведение в степень
ПРОИЗВЕЛ (PRODUCT)	Произведение ряда чисел
СЛЧИС (RAND)	Возвращает случайное число
РИМСКОЕ (ROMAN)	Преобразование арабского числа в римское
КОРЕНЬ (SQRT)	Квадратный корень
СУММ (SUM)	Сумма ряда чисел
СУММ КВ (SUMSQ)	Сумма квадратов ряда чисел
ОТБР (TRUNC)	Отбрасывает дробную часть

ТАБЛИЦА 10.3.Статистические функции

Функция	Описание
СРОТКЛ(AVEDEV)	Среднее абсолютное значение отклонения от среднего
СРЗНАЧ (AVERAGE), СРЗНАЧА(AVERAGEA)	Среднее арифметическое аргументов
СЧЕТ (COUNT), СЧЕТЗ (COUNTA), СЧИТАТЬ ПУСТОТЫ (COUNT-BLANK), СЧЕТЕСЛИ (COUNTIF)	Количество чисел в списке аргументов
КБЛДРОТКЛ (DEVSQ.)	Сумма квадратов отклонении от среднего но выборке
CFFEOM (GEOMEAN)	Среднее геометрическое набора положительных чисел
МАКС (МАХ), МАКСА (МАХА)	Максимальное значение списка аргументов
МИН (M1N), МИНА (MINA)	Минимальное значение списка аргументов
ДИСП (STDEV), ДИСПА (STDEVA),	Функции расчета дисперсии
ДИСПР (STDEVP), ДИСПРА (STDEVPA) СТАНДОТ-КЛОН (VAR), СТАНДОТКЛОНА (VARA), СТАНДОТКЛОНП (VARP), СТАНДОТКЛОНПА (VARPA)	Стандартное отклонение по выборке

• Статистические (Statistical) — функции для расчета среднего значения, дисперсии, статистических распределений и других вероятностных характеристик. Некоторые статистические функции перечислены в табл. 10.3;
• Ссылки и массивы (Lookup & Reference) — операции преобразования ссылки на ячейку в число, расчета ссылок на основе числовых аргументов, вычисления числа строк и столбцов диапазона и других параметров, связанных с адресацией ячеек листа Excel;
• Работа с базой данных (Database) — функции формирования выборки из базы данных и расчета статистических параметров величин, расположенных в базе данных;
• Текстовые (Text) — функции для работы с текстовыми строками. Часть этих функций перечислена в табл.-10.4.

Не нашли то, что искали? Воспользуйтесь поиском:

Источник: https://studopedia.ru/11_49496_statisticheskie-funktsii-vichislenie-minimalnogo-maksimalnogo-i-srednego-znacheniy.html

Расчет показателей вариации в Excel

Оригинал http://statanaliz.info/index.php/excel/formuly/37-raschet-pokazatelej-variatsii-v-excel

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

Проведение любого статанализа немыслимо без расчетов. И сегодня в рамках рубрики «Работаем в Excel» мы научимся рассчитывать показатели вариации. Теоретическая основа была рассмотрена ранее в ряде статей о вариации данных.

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

• Сегодняшняя публикация будет посвящена расчету в Excel следующих показателей вариации:
• — максимальное и минимальное значение
• — среднее линейное отклонение
• — дисперсия (по генеральной совокупности и по выборке)
• — среднее квадратическое отклонение (по генеральной совокупности и по выборке)
• — коэффициент вариации

Факт возможности расчета упомянутых показателей в Excel свидетельствует о практическом их использовании. И, несмотря на очевидность некоторых моментов, я постараюсь расписать все подробно.

Максимальное и минимальное значение

Начнем с формул максимума и минимума. Что такое максимальное и минимальное значение, уверен, знают почти все. Максимум – самое большое значение из анализируемого набора данных, минимум – самое маленькое (может быть и отрицательным числом).

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

Минимум и максимум – весьма информативные показатели. Давайте теперь их рассчитаем в Excel.

Как нетрудно догадаться, делается сие элементарно – как два клика об асфальт. В Мастере функций следует выбрать: МАКС – для расчета максимального значения, МИН – для расчета минимального значения. Для облегчения поиска перечень всех функций можно отфильтровать по категории «Статистические».

Выбираем нужную формулу, в следующем окошке указываем диапазон данных (в котором ищется максимальное или минимальное значение) и жмем «ОК».

Функции МАКС и МИН достаточно часто используются, поэтому разработчики Экселя предусмотрительно добавили соответствующие кнопки в ленту. Они находятся там же, где суммаи среднее значение – в разворачивающемся списке.

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

Среднее линейное отклонение

Среднее линейное отклонение, напоминаю, представляет собой среднее из абсолютных (по модулю) отклонений от средней арифметической в анализируемой совокупности данных. Математическая формула имеет вид:

1. где
2. a – среднее линейное отклонение,
3. x – анализируемый показатель, с черточкой сверху – среднее значение показателя,
4. n – количество значений в анализируемой совокупности данных.
5. В Excel эта функция называется СРОТКЛ.

После выбора функции СРОТКЛ указываем диапазон данных, по которому должен произойти расчет. Нажимаем «ОК». Наслаждаемся результатом.

Дисперсия

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

• где
• D – дисперсия,
• x – анализируемый показатель, с черточкой сверху – среднее значение показателя,
• n – количество значений в анализируемой совокупности данных.

Excel также предлагает готовую функцию для расчета генеральной дисперсии ДИСП.Г.

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

Математическая формула выборочной дисперсии имеет вид:

в Excel выборочная дисперсия рассчитывает через функцию ДИСП.В.

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

Среднее квадратическое отклонение

1. Среднеквадратическое отклонение по генеральной совокупности – это корень из генеральной дисперсии.
2. Выборочное среднеквадратическое отклонение – это корень из выборочной дисперсии.

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

   Г

— Среднеквадратическое отклонение по выборке СТАНДОТКЛОН.В.

С названием этого показателя может возникнуть путаница, т.к. часто можно встретить синоним «стандартное отклонение». Пугаться не нужно – смысл тот же.

Далее, как обычно, указываем нужный диапазон и нажимаем на «ОК». Среднее квадратическое отклонение имеет те же единицы измерения, что и анализируемый показатель, поэтому является сопоставимым с исходными данными. Об этом ниже.

Коэффициент вариации

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

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

Математическая формула такова:

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

=СТАНДОТКЛОН.Г(диапазон)/СРЗНАЧ(диапазон)

Коэффициент вариации обычно выражается в процентах, поэтому ячейку с формулой можно обрамить процентным форматом. Нужная кнопка находится на ленте на закладке «Главная»:

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

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

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

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

Легкой работы в Excel и до встречи на блоге statanaliz.info.

Оригинал и другие статьи http://statanaliz.info/index.php/excel/formuly/37-raschet-pokazatelej-variatsii-v-excel

Источник: http://vniioh.ru/raschet-pokazatelej-variacii-v-excel/

Формула Excel минимум по условию

Определение минимального или максимального значения в Excel делается очень просто с помощью соответствующих формул-функций: МИН() и МАКС(), однако иногда необходимо узнать минимум или максимум с соблюдением условия.

Есть два принципиальных варианта определения минимального или максимального значения в диапазоне на листе книги Excel с условием вида «больше-меньше» (условие «равно» и производные «больше либо равно» и «меньше либо равно», а также «в интервале» – тут не рассматриваются). Важно отметить, что проверка условия делается внутри этого же диапазона

1. Узнать минимальное число из диапазона, которое больше чем заданное число
2. Получить максимальное значение, которое меньше, чем нужное нам число

• Приведем решение формулой, которая даст нам результат при соблюдении такого условия: «определить минимальное число в диапазоне, которое больше чем 200″.
• =МИН(ЕСЛИ(B3:B14>200;B3:B14))
• Комментарий: эта формула должна быть введена как формула массива
• Чтобы было понятно на конкретном примере, приведем ряд цифр (для проверки формулы поместите эти цифры в диапазон B3:B14)
• 408
  
  -414
  290
  -482
  240
  -104
  242
  440
  
  -105
  328
• 274
• Наша формула для этого ряда чисел даст результат 240, так как это минимальное число, которое больше чем 200.
• Мы рассмотрели одно из решений для задач, когда есть потребность определить не просто максимум или минимум, а МАКС или МИН при определенном условии.
• В реальных задачах, которые возникают в процессе работы экономиста или аналитика (и не только), может потребоваться:
• — определить минимум, максимум с условием, находящимся в другом столбце. Например, максимум по региону «север»;
• — минимум/максимум в интервале – то есть задан определенный интервал, к примеру, 200-500 и внутри него нужно понять какое число минимально или же максимально;

— второе (третье и т.д.) по счету минимальное/максимальное значение в диапазоне.

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

 Если Вас интересуют такого рода формулы – обращайтесь и Вы получите профессиональную консультацию или же сразу готовое решение. Сайт excelskype.ru специализация на удаленном обучении через скайп и предоставлении консультаций по Excel. 

В закладки: постоянная ссылка.

Источник: http://excelskype.ru/formula-excel-minimum-po-usloviyu/

Создание динамической диаграммы с отображением максимального и минимального значений

В Excel построение простых, не динамических диаграмм, процедура не из сложных, достаточно определиться с правильным типом графика, выделить данные и кликнуть по пиктограмме диаграммы на вкладке “Вставка”, группы “Диаграммы”.

Смотрите также видеоверсию статьи «Создание динамической диаграммы с отображением максимального и минимального значений».

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

На самом деле, построение диаграммы, которая бы автоматически изменялась при добавлении новых данных не слишком сложно.

Построение динамической диаграммы

Итак, для того, чтобы данные автоматически “подхватывались” диаграммой их достаточно отформатировать как таблицу. (Прим. Подробная работа с данными, отформатированными таблицами, рассматривается в 10-м занятии курса Excel от новичка до профессионала).

Теперь, при добавлении новых данных, они автоматически будут отображаться и на диаграмме.

Визуальное отображение ключевых значений (максимального и минимального) на диаграмме

• Для того, чтобы визуализировать ряды с максимальным и минимальным значением необходимо добавить в таблицу с данными два новых ряда (при этом диапазон таблицы расширится и на них): в первом столбце произвести вычисление максимального значения, а во втором – минимального.
• Формула для нахождения максимального значения:
• =ЕСЛИ([@[Заработал в $]]=МАКС([Заработал в $]);[@[Заработал в $]];НД())
• Формула для нахождения минимального значения:
• =ЕСЛИ([@[Заработал в $]]=МИН([Заработал в $]);[@[Заработал в $]];НД())

Расшифровка: если в ячейке таблицы находится максимальное (минимальное) значение, тогда отобразить его, в противном случае ввести в ячейку “нет данных”. Здесь “нет данным”, которое вводится соответствующей функцией можно просто заменить на 0 (ноль).

1. Теперь на диаграмме фактически отображается три ряда данных, самое время раскрасить максимальное и минимально значение, а чтобы визуально скрыть дополнительные ряды, нужно в настройках ряда установить перекрытие рядов в 100% и, по желанию, сделать боковой зазор в процентов 80-90.
3. Готово, теперь диаграмма не только будет автоматически перестраиваться, при добавлении данных, но и автоматически подсвечивать максимально и минимальное значение.

Файл Excel с данными

Источник: https://msoffice-prowork.com/sozdanie-dinamicheskojj-diagrammy-s-otobrazheniem-maksimalnogo-i-minimalnogo-znachenijj/

34. Расчет среднего максимального и минимального значений. Определение ранга процентной нормы числа

Расчет среднего значения

СРЗНАЧ(ДИАПАЗОН1; ДИАПАЗОН2;…) — группа статистических функций.

В текущую ячейку возвращается среднее значение для чисел указанного диапазона.

Определение максимального значения

МАКС(ДИАПАЗОН1; ДИАПАЗОН2;…) — группа статистических функций.

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

Определение минимального значения

МИН(ДИАПАЗОН1; ДИАПАЗОН2;…) — группа статистических функций.

• В текущую ячейку возвращается минимальное число из данного диапазона.
• РАНГ(АДРЕС ЯЧЕЙКИ; ДИАПАЗОН) — группа статистических функций.
• В текущую ячейку возвращается величина, соответствующая положению (рангу) числа, заданного адресом ячейки, в указанном диапазоне.
• ПРОЦЕНТРАНГ(ДИАПАЗОН; АДРЕС ЯЧЕЙКИ)- группа статистических функций.
• В текущую ячейку возвращается величина, определяющая процентную долю числа, заданного адресом ячейки, от максимального значения в указанном диапазоне. Действие функции аналогично функции РАНГ, только ранг определяется в процентном отношении (максимальное число принимается за 100%, минимальное – за 0%)

35. Функции прогнозирования в табл процессоре

1. Функции прогнозирования
2. ТЕНДЕНЦИЯ(ИЗВЕСТНЫЕ ЗНАЧЕНИЯ_Х; ИЗВЕСТНЫЕ ЗНАЧЕНИЯ_У; НОВОЕ ЗНАЧЕНИЕ_У) — группа статистических функций.
3. Прогноз производится в несколько этапов:
4. 1 Этап.

   Расчёт исходных данных

5. 2 Этап. Построение функций регрессии различными методами
6. 3 Этап. Выбор функции регрессии по условию минимальной ошибки аппроксимации

4 Этап.

Расчёт доверительных интервалов и окончательный прогноз.

Автоматическое прогнозирование линейной зависимости, Автоматическое прогнозирование экспоненциальной зависимости, Ручное прогнозирование линейной или экспоненциальной зависимости, Вычисление тенденций с помощью добавления линии тренда на диаграмму,

36. Функции для работы с матрицами

МОБР(МАССИВ)- группа математических функций.

Возвращает в выделенный диапазон обратную матрицу для матрицы, хранящейся в массиве2.

Массив — это числовой массив с равным количеством строк и столбцов. Массив может быть задан как диапазон ячеек, например A1:C3 или как имя диапазона или массива. Если какая-либо из ячеек в массиве пуста или содержит текст, а также если массив имеет неравное число строк и столбцов, то функция МОБР возвращает значение ошибки #ЗНАЧ!.

МОПРЕД(МАССИВ) — группа математических функций.

Возвращает определитель матрицы (матрица хранится в массиве).

Определитель матрицы — это число, вычисляемое на основе значений элементов массива3. Массив — это числовой массив с равным количеством строк и столбцов.

Если какая-либо ячейка в массиве пуста или содержит текст, то функция МОПРЕД возвращает значение ошибки #ЗНАЧ!. МОПРЕД также возвращает значение ошибки #ЗНАЧ!, если массив имеет неравное количество строк и столбцов

• МУМНОЖ(МАССИВ1;МАССИВ2) — группа математических функций
• Возвращает произведение матриц, хранящихся в массивах 1 и 24.
• Результатом является массив с таким же числом строк, как массив-1 и с таким же числом столбцов, как массив-2.

Количество столбцов аргумента массив-1 должно быть таким же, как количество строк аргумента массив-2, и оба массива должны содержать только числа.

Массив-1 и массив-2 могут быть заданы как интервалы, массивы констант или ссылки.

Если хотя бы одна ячейка в аргументах пуста или содержит текст, или если число столбцов в аргументе массив-1 отличается от числа строк в аргументе массив-2, то функция МУМНОЖ возвращает значение ошибки #ЗНАЧ!.

Источник: https://studfile.net/preview/3601317/page:15/