Foreversoft.ru

IT Справочник
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Average в excel

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

В этой статье описаны синтаксис формулы и использование функции СРЗНАЧ в Microsoft Excel.

Описание

Возвращает среднее значение (среднее арифметическое) аргументов. Например, если диапазон a1: A20 содержат числа, формула = СРЗНАЧ ( a1: A20) Возвращает среднее арифметическое этих чисел.

Синтаксис

Аргументы функции СРЗНАЧ описаны ниже.

Число1. Обязательный аргумент. Первое число, ссылка на ячейку или диапазон, для которого требуется вычислить среднее значение.

Число2. Необязательный. Дополнительные номера, ссылки на ячейки или диапазоны, для которых вычисляется среднее значение максимум в 255.

Замечания

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

Логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов, не учитываются.

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

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

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

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

Примечание: Функция СРЗНАЧ вычисляет среднее значение, то есть центр набора чисел в статистическом распределении. Существует три наиболее распространенных способа определения среднего значения, описанных ниже.

Среднее значение — это среднее арифметическое, которое вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.

Медиана — это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.

Мода — это число, наиболее часто встречающееся в данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.

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

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

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

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

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Примеры функций СРЗНАЧ и СРЗНАЧА для среднего значения в Excel

Функции СРЗНАЧ и СРЗНАЧА служат для того, чтобы вычислять среднее арифметическое значение интересующих аргументов в Excel. Среднее число вычисляется классическим путем – суммирования всех чисел и делением суммы на количество этих же чисел. Главное отличие этих функций заключается в том, что они по-разному обрабатывают не числовые типы значений, которые находятся в ячейках Excel. Далее обо всем поподробнее.

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

Функция СРЗНАЧ относится к группе «Статистические». Поэтому для вызова данной функции в Excel необходимо выбрать инструмент: «Формулы»-«Библиотека функций»-«Другие функции»-«Статистические»-«СРЗНАЧ». Или вызвать диалоговое окно «Вставка функции» (SHIFT+F3) и выбрать из выпадающего списка «Категория:» опцию «Статистические». После чего в поле «Выберите функцию:» будет доступен список категории со статистическими функциями где и находится СРЗНАЧ, а также СРЗНАЧА.

Если есть диапазон ячеек B2:B8 с числами, то формула =СРЗНАЧ(B2:B8) будет возвращать среднее значение заданных чисел в данном диапазоне:

Синтаксис использования следующий: =СРЗНАЧ(число1; [число2]; …), где первое число обязательный аргумент, а все последующие аргументы (вплоть до числа 255) необязательны для заполнена. То есть количество выбранных исходных диапазонов не может превышать больше чем 255:

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

Аргументы функции СРЗНАЧ могут быть представлены не только числами, но и именами или ссылками на конкретный диапазон (ячейку), содержащий число. Учитывается логическое значение и текстовое представление числа, которое непосредственно введено в список аргументов.

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

Результат выполнения функции в примере на картинке ниже – это число 4, т.к. логические и текстовые объекты игнорируются. Поэтому:

(5 + 7 + 0 + 4) / 4 = 4

При вычислениях средних значений необходимо учитывать разницу между пустой ячейкой и ячейкой, содержащей нулевое значение, особенно если в диалоговом окне Excel снят флажок «Показывать нули в ячейках, которые содержат нулевые значения». Когда флажок установлен, пустые ячейки игнорируются, а нулевые значения нет. Чтобы снять или установить этот флаг необходимо открыть вкладку «Файл», далее нажать на «Параметры» и выбрать в категории «Дополнительно» группу «Показать параметры для следующего листа», где возможно установить флажок:

Читать еще:  Vba excel массивы

Результаты еще 4-х задач сведены в таблицу ниже:

Как видно на примере, в ячейке A9 функция СРЗНАЧ имеет 2 аргумента: 1 — диапазон ячеек, 2 – дополнительное число 5. Так же могут быть указаны в аргументах и дополнительные диапазоны ячеек с числами. Например, как в ячейке A11.

Формулы с примерами использования функции СРЗНАЧА

Функция СРЗНАЧА отличается от СРЗНАЧ тем, что истинное логическое значение «ИСТИНА» в диапазоне приравнивается к 1, а ложное логическое значение «ЛОЖЬ» или текстовое значение в ячейках приравнивается к нулю. Поэтому результат вычисления функции СРЗНАЧА отличается:

Результат выполнения функции возвращает число в примере 2,833333, так как текстовые и логические значения приняты за нуль, а логическое ИСТИНА приравнено к единице. Следовательно:

(5 + 7 + 0 + 0 + 4 + 1) / 6 = 2,83

Аргументы функции СРЗНАЧА подчинены следующим свойствам:

  1. «Значение1» является обязательным, а «значение2» и все значения, которые следуют за ним необязательными. Общее количество диапазонов ячеек или их значений может быть от 1 до 255 ячеек.
  2. Аргумент может быть числом, именем, массивом или ссылкой, содержащей число, а также текстовым представлением числа или логическим значением, например, «истина» или «ложь».
  3. Логическое значение и текстовое представление числа, введенного в список аргументов, учитывается.
  4. Аргумент, содержащий значение «истина», интерпретируется как 1. Аргумент, содержащий значение «ложь», интерпретируется как 0 (ноль).
  5. Текст, содержащийся в массивах и ссылках, интерпретируется как 0 (ноль). Пустой текст («») интерпретирован тоже как 0 (ноль).
  6. Если аргумент массив или ссылка, то используются только значения, входящие в этот массив или ссылку. Пустые ячейки и текст в массиве и ссылке — игнорируется.
  7. Аргументы, которые являются значениями ошибки или текстом, не преобразуемым в числа, вызывают ошибки.

Результаты особенности функции СРЗНАЧА сведены в таблицу ниже:

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

Average

The AVERAGE function in Excel calculates the average (arithmetic mean) of a group of numbers. The AVERAGE function ignores logical values, empty cells and cells that contain text.

Average

Instead of the AVERAGE function, use the SUM and the COUNT function.

1. For example, the AVERAGE function below calculates the average of the numbers in cells A1 through A3.

2. The formula below produces the exact same result.

3. The following AVERAGE function calculates the average of the numbers in cells A1 through A3 and the number 8.

4. The AVERAGE function ignores logical values (TRUE or FALSE), empty cells and cells that contain text.

AverageA

The AVERAGEA function also returns the average (arithmetic mean) of a group of numbers. However, the logical value FALSE and cells that contain text evaluate to 0 and the logical value TRUE evaluates to 1. The AVERAGEA function also ignores empty cells.

1. For example, take a look at the AVERAGEA function below.

2. You can use the normal AVERAGE function to check this result.

Average Top 3

Use the AVERAGE and the LARGE function in Excel to calculate the average of the top 3 numbers in a data set.

1. First, the AVERAGE function below calculates the average of the numbers in cells A1 through A6.

2. For example, to find the third largest number, use the following LARGE function.

3. The formula below calculates the average of the top 3 numbers.

Explanation: the LARGE function returns the array constant <20,15,10>. This array constant is used as an argument for the AVERAGE function, giving a result of 15.

AverageIf

To calculate the average of cells that meet one criteria, use the AVERAGEIF function in Excel.

Weighted Average

To calculate a weighted average in Excel, use the SUMPRODUCT and the SUM function.

Moving Average

Use the Analysis Toolpak to calculate the moving average of a time series in Excel.

Note: a moving average is used to smooth out irregularities (peaks and valleys) to easily recognize trends.

Excel Average Functions

Examples show how to average Excel cells with numbers, text, blanks, or cells based on specific criteria. Use the AVERAGE, AVERAGEA, AVERAGEIF or AVERAGEIFS functions, or TRIMMEAN, for an average that excludes outliers.

Average Cells with Numbers — AVERAGE

The AVERAGE function will average cells that contain numbers. Its syntax is:
=AVERAGE(value1, value2. value30).
The arguments (e.g. value1) can be cell references, or values typed into the AVERAGE formula.

The following AVERAGE function example uses one argument — a reference to cells A1:A5.

  1. Enter the sample data on your worksheet
  2. In cell A7, enter an AVERAGE formula, to average the numbers in column A: =AVERAGE(A1:A5)

  • Press the Enter key, to complete the formula.
  • The result will be 21.83, the average of the cells that contain numbers.
    Cell A1 isn’t included in the average, because it contains text, and the empty cell C3, is ignored.
  • Note: Since dates are stored as numbers, the AVERAGE function will include any cells that contain dates.

    Average Cells with Data — AVERAGEA

    The AVERAGEA function will average cells that are not empty. Its syntax is:
    =AVERAGEA(value1, value2. value30).
    The arguments (e.g. value1) can be cell references, or values typed into the formula. The following example uses one argument — a reference to cells A1:A5.

    1. Enter the sample data on your worksheet
    2. In cell A7, enter a AVERAGEA formula, to average the numbers in column A: =AVERAGEA(A1:A5)

  • Press the Enter key, to complete the formula.
  • The result will be 16.38, the average of the cells that contain data. Cell A1 contains text, which is treated as a zero, and cell A3 is not included in the average, because it is blank.
  • Note: AVERAGEA will include cells with formulas — including those that look empty, because they evaluate to «», e.g. =IF(B2=»»,»»,B2).

    Average cells that match criteria — AVERAGEIF

    Match criterion exactly

    In Excel, average cells that meet a specific criterion. In this example only the quantities for the Pen orders will be averaged.

    1. Select the cell in which you want to see the average (cell A12 in this example)
    2. Type an equal sign (=) to start the formula
    3. Type: AVERAGEIF(
    4. Select the cells that contain the values to check for the criterion. In this example, cells A1:A10 will be checked
    5. Type a comma, to separate the arguments
    6. Type the criterion. In this example, you’re checking for text, so type the word in double quotes: «Pen»
      Note: upper and lower case are treated equally
    7. Type a comma, to separate the arguments
    8. Select the cells that contain the values to average. In this example, cells B1:B10 contain the values
    9. Type a closing bracket
      The completed formula is: =AVERAGEIF(A1:A10,»Pen»,B1:B10)

  • Press the Enter key to complete the entry
  • The result will be 9.25, the average quantity for rows that contain «Pen»
  • Match criterion in a string

    In Excel, average cells in rows that contain a criterion as part of the cell’s contents. In this example all Pen, Gel Pen, and Pencil orders will be averaged, because they contain the string «pen».

    1. Select the cell in which you want to see the average (cell A12 in this example)
    2. Type an equal sign (=) to start the formula
    3. Type: AVERAGEIF(
    4. Select the cells that contain the values to check for the criterion. In this example, cells A1:A10 will be checked
    5. Type a comma, to separate the arguments
    6. Type the criterion. In this example, you’re checking for text, so type the word in double quotes, with one or more asterisk (*) wildcard characters: «*Pen*»
      Note: upper and lower case are treated equally
    7. Type a comma, to separate the arguments
    8. Select the cells that contain the values to average. In this example, cells B1:B10 contain the values
    9. Type a closing bracket
      The completed formula is: =AVERAGEIF(A1:A10,»*Pen*»,B1:B10)

  • Press the Enter key to complete the entry
  • The result will be 8.83, the average of rows that contain the string, «Pen», in column A
  • Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 7 above could be changed to:
    =AVERAGEIF(A1:A10,»*» & B12 & «*»,B1:B10)
    if cell B12 contained the text — pen.

    Criterion and operator

    You can use an operator with a criterion. In this example only the rows where the quantity is greater than or equal to ten will be averaged.

    1. Select the cell in which you want to see the average (cell A12 in this example)
    2. Type an equal sign (=) to start the formula
    3. Type: AVERAGEIF(
    4. Select the cells that contain the values to check for the criterion. In this example, cells B1:B10 will be checked
    5. Type a comma, to separate the arguments
    6. Type the criterion. In this example, you’re checking for rows where the quantity is greater than or equal to 10. The >= operator is used before the number, and the entire criterion is enclosed in double quotes: «>=10»
      Note: Even though this is a numerical criterion, it must enclosed in double quote marks.
    7. Type a closing bracket
    8. The completed formula is:
      =AVERAGEIF(B1:B10,»>=10″)
    9. Press the Enter key to complete the entry

    Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 8 above could be changed to:
    =AVERAGEIF(B1:B10,»>=» & B12)
    if cell B12 contained the number — 10.

    Average cells for multiple criteria — AVERAGEIFS

    Match multiple criteria

    In Excel 2007 and later versions, you can use the AVERAGEIFS function to average rows that meet two or more criteria. In this example only the rows where the item is «Pen» and the quantity is greater than or equal to ten will be averaged.

    The criteria have been entered in cell D3 and E3. We’ll refer to those cells in the formula, instead of typing the values in the formula.

    1. Select the cell in which you want to see the total
    2. Type an equal sign (=) to start the formula
    3. Type: AVERAGEIFS(
    4. Select the cells that contain the values to average. In this example, cells B2:B10 will be averaged
    5. To start the next argument, type a comma
    6. Select the cells that contain the values to check for the first criterion. In this example, cells A2:A10 will be checked
    7. Type a comma, and click on cell D3, which contains the first criterion, «Pen»
    8. To start the next set of criteria, type a comma
    9. Select the cells that contain the values to check for the second criterion. In this example, cells B2:B10 will be checked
    10. Type a comma, and the operator for the second criterion: «>=»
      Note: The operator is enclosed in double quote marks.
    11. Type an ampersand (&), and click on cell E3, which contains the minimum number for the averaged cells — 10.
    12. To start the next argument, type a comma
    13. Select the cells that contain the values to check for the second criterion. In this example, cells B2:B10 will be checked
    14. Finish with a closing bracket: )
    15. The completed formula is shown below.
    16. Press the Enter key to complete the entry

    The completed formula is:

    Note: Instead of refering to cells for the criteria, you can type them into the formula. Here is the same formula, with typed criteria:

    Average Rows in a Filtered List — SUBTOTAL

    After you filter the rows in a list, you can use the SUBTOTAL function to average the visible rows.

    1. Apply an AutoFilter to the table. There are instructions here — AutoFilter Basics
    2. Filter at least one of the columns in the table. In this example, the first column has been filtered for Binders.
    3. Select the cell immediately below the column you want to sum.
    4. Click the AutoSum button on the Ribbon’s Home tab or Formulas tab.
      • If you want the SUBTOTAL function in a cell other than the one directly below the filtered list, you can type the formula, instead of using the AutoSum button.
    5. A SUBTOTAL formula will be automatically inserted, totalling the visible cells in the column
      • The first argument in the SUBTOTAL function is a function number, that specifies how the numbers should be calculated. The default is 9, which tells Excel to SUM the numbers.
      • Other function numbers can be used, such as 1 for AVERAGE, or 2 for COUNT.
    6. To Average all the non-empty cells in column D, select 1 — AVERAGE from the drop down list of functions, as the first argument:
      =SUBTOTAL(1,D2:D10)

  • Check the range of cells to be averaged, and correct the range address, if necessary.
  • Press the Enter key to complete the formula entry.

    Video: Trimmed Mean With TRIMMEAN Function

    To exclude a specific percentage of outlying data from an average, you can use Excel’s TRIMMEAN function. Watch this video to see how to set up the formula, and the written instructions are below the video.

    Your browser can’t show this frame. Here is a link to the page

    Trimmed Mean With TRIMMEAN Function

    To exclude outlying data from an average, you can use the TRIMMEAN function. This function excludes a specific percentage of data points from the top and bottom of the data set, then returns the average (mean) of the remaining data points.

    NOTE: In this example, the quantities are sorted in ascending order, so it is easier to see the top and bottom numbers. Numbers do NOT need to be sorted, for the TRIMMEAN function to calculate correctly.

    TRIMMEAN Function

    The TRIMMEAN function requires 2 arguments: TRIMMEAN(array, percent)

    In this example,

    • the values for the array are in cells B2:B21
    • the trim percent is entered in cell E3, as 25% (or 0.25)

    To calculate the trimmed mean, enter this formula in cell E4::

    The TRIMMEAN result (53.06) is different from the AVERAGE (51.95), which is shown in cell E5.

    How TRIMMEAN Works

    In this example, there are 20 values, and the trim percent is 25%.

    To calculate how many number to trim,

    • the values are counted, then multiplied by the trim percentage (e.g. 20 * .25 = 5
    • That number is div >So, in this excample, the top 2 (86,97) and bottom 2 (3,4) data points will not be included in the average that TRIMMEAN calculates.

    The AVERAGE function, used on cells B4:B19 in this example, returns the same result as the TRIMMEAN function, with a trim percentage of 25%.

    Download Free Workbook

    To see the formulas and test data used in this example, you can download the Average functions sample file. The file is in xlsx format (Excel 2007 and later versions).

    Excel Function Tutorials

    Don’t Miss Our Excel Tips

    Don’t miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.

  • Ссылка на основную публикацию
    Adblock
    detector