Расчет среднего значения в таблице Excel

При работе с числовыми данными в Excel пользователи часто сталкиваются с задачей вычисления среднего значения. В математических терминах это делается путем деления суммы всех чисел на их количество. Но как это сделать в Excel? Давайте узнаем.

Оглавление

1. Информация из строки состояния
2. Вычисление среднего значения
   • • Использование арифметического выражения
     • Инструменты на ленте
     • Использование функции SMARTNAME
     • Инструменты на вкладке Формулы
     • Ручной ввод функции в ячейку
     • Нахождение средних значений по штатам
3. Приложение

Информация в строке состояния

См. также "Как найти обратную величину матрицы в Excel".

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

 

 

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

 

 

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

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

Использование арифметического выражения

Как известно, среднее значение - это сумма чисел, деленная на их количество. Эту формулу также можно использовать в Excel.

1. Вы переходите к соответствующей ячейке, ставите в ней знак "равно" и записываете арифметическое выражение, используя следующее правило:
   =(Число1+Число2+Число3...)/Число_компонентов.
   Примечание: в качестве числа можно указать как конкретное числовое значение, так и ссылку на ячейку. В нашем случае попробуем вычислить среднее значение чисел в ячейках B2,C2,D2 и E2.
   Окончательная формула выглядит следующим образом: =(B2+E2+D2+E2)/4.

    

   

    

2. Когда вы закончите, нажмите Enter, чтобы получить результат.

    

   

    

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

Инструменты на ленте

Этот метод предполагает использование специального инструмента на ленте программы. Вот как это работает:

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

    

   

    

2. Перейдите на вкладку "Главная" (если вы не находитесь на ней). В разделе инструментов "Редактирование" найдите значок "Автосумма" и нажмите на маленькую стрелку вниз рядом с ним. В открывшемся списке выберите опцию "Среднее".

    

   

    

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

    

   

    

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

    

   

    

Примечание: Если вместо вертикального выделения (всего столбца или его части) сделано горизонтальное выделение, результат будет отображаться не под выделением, а справа от него.

 

 

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

Чтобы объяснить это, рассмотрим следующую ситуацию. Предположим, у нас есть две строки, заполненные данными. Мы хотим усреднить две строки одновременно, поэтому выделим их и применим инструмент.

 

 

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

 

 

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

Альтернативный способ использования опции Average на ленте:

1. Перейдите к первой свободной ячейке после столбца или строки (в зависимости от структуры данных) и нажмите кнопку вычисления среднего значения.

    

   

    

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

    

   

    

3. Когда вы будете готовы, нажмите клавишу Enter и получите результат в заданной ячейке.

    

   

    

Использование функции СРЗНАЧ

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

1. Перейдите к ячейке, в которой вы хотите отобразить результат. Нажмите "Вставить функцию" (fx) в левой части панели формул.

    

   

    

2. В окне "Мастер функций" нажмите "Статистические", а затем в списке выберите "CROSS".

    

   

    

3.  Появится окно с аргументами функции (их максимальное количество - 255). В качестве аргумента "Number1" введите координаты требуемого диапазона. Это можно сделать вручную, введя адреса ячеек с клавиатуры. В качестве альтернативы можно сначала щелкнуть внутри поля ввода, а затем с помощью левой кнопки мыши выделить нужный диапазон в таблице. При необходимости (если вы хотите выбрать ячейки и диапазоны ячеек в других местах таблицы) заполните аргумент 'Number2' и так далее. Когда вы будете готовы, нажмите OK.

    

   

    

4. Вы получите результат в выбранной ячейке.

    

   

    

5. Среднее значение не всегда может быть "красивым" из-за большого количества знаков после запятой. Если вам не нужна такая детализация, вы всегда можете настроить ее. Для этого щелкните правой кнопкой мыши на ячейке результата. В появившемся контекстном меню выберите "Форматировать ячейки".

    

   

    

6. На вкладке "Число" выберите формат "Числовой" и укажите количество десятичных знаков в правой части окна. В большинстве случаев двух цифр более чем достаточно. Кроме того, при работе с большими числами можно установить флажок "Разделитель групп цифр". После внесения изменений нажмите кнопку OK.

    

   

    

7. Теперь вы готовы. Теперь результат выглядит гораздо привлекательнее.

    

   

    

Инструменты во вкладке “Формулы”

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

1. Перейдите к ячейке, в которой необходимо выполнить расчет. Перейдите на вкладку "Формулы". В разделе инструментов "Библиотека функций" нажмите на значок "Другие функции" и в открывшемся списке выберите группу "Статистические", а затем группу "Стрелка".

    

   

    

2. Откроется знакомое окно аргументов выбранной функции. Заполните данные и нажмите OK.

Ввод функции в ячейку вручную

Как и все другие функции, формулу СРСНАС с необходимыми аргументами можно ввести непосредственно в нужную ячейку.

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

=RESPOND(number1;number2;...).

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

=СРЗНАЧ(3;5;22;31;75)

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

=СРЗНАЧ(B2;C2;D2;E2;F2;G2;H2;B3;C3;D3)

 

 

Когда формула полностью готова, мы нажимаем Enter и получаем результат.

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

Определение среднего значения по условию

См. также "Выбор параметра в Excel: где найти, как сделать".

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

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

1. Перейдите в ячейку результата и нажмите кнопку "Вставить функцию" (fx) слева от строки формул.

    

   

    

2. В Мастере функций выберите категорию "Статистика", выберите оператор "CROWN EXCISOR" и нажмите OK.

    

   

    

3. На экране появятся аргументы функции, после чего необходимо нажать кнопку OK:
   • Укажите (вручную или выделив левой кнопкой мыши в самой таблице) необходимую область ячеек в значении аргумента "Диапазон";
   • Значение аргумента "Условие", соответственно, задает наше условие для того, чтобы ячейки в выбранном диапазоне были включены в общий расчет. В нашем случае это выражение ">0". Вместо конкретного числа при необходимости можно указать адрес ячейки, содержащей числовое значение в условии.
   • Поле аргумента "Диапазон_усреднения диапазона" можно оставить пустым, так как его обязательное заполнение требуется только при работе с текстовыми данными.

      

     

      

4. Среднее значение с учетом заданного нами условия выбора ячеек отображается в выбранной ячейке.

    

   

    

Заключение

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