Вероника знает, как использовать функцию СРЕДНЕЕ, чтобы определить среднее значение диапазона значений. Однако она хотела бы, чтобы среднее значение определялось на основе ненулевых значений в диапазоне.

Есть несколько способов решить эту проблему. Во-первых, нужно помнить, как рассчитывается среднее значение. Он определяется как сумма диапазона значений, деленная на количество элементов в диапазоне. Таким образом, вы можете рассчитать исключительное среднее, просто убедившись, что знаменатель (число, на которое вы делите) не включает никаких нулевых значений. Например:

=SUM(A1:A50)/COUNTIF(A1:A50,"<>0")

Этот подход использует функцию СЧЁТЕСЛИ, чтобы определить количество ячеек в диапазоне (A1: A50), которые не содержат нуля. Если этот диапазон содержит не только нули, но и пустые ячейки, и вы не хотите, чтобы пустые ячейки фигурировали в результате, вам нужно использовать более сложную формулу:

=SUM(A1:A50)/(COUNTIF(A1:A50,"<>0")-COUNTBLANK(A1:A50)- (COUNTA(A1:A50)-COUNT(A1:A50)))

Функция СЧЁТЕСЛИ подсчитывает ячейки, которые явно не оцениваются как 0, но подсчитывает пустые и текстовые ячейки. Термин COUNTBLANK корректирует пустые ячейки, а разница между COUNTA и COUNT регулирует общее количество ячеек, содержащих текст.

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

=AVERAGE(IF(A1:A50<>0,A1:A50))

Помните, что формулы массива нужно вводить с помощью комбинации Ctrl + Shift + Enter. Эта формула массива также исключает пробелы или ячейки, содержащие текст.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (7727) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

link: / excelribbon-An_Average_that_Excludes_Zero_Values ​​[Среднее значение, исключающее нулевые значения].