Тому нужно усреднить серию несмежных ячеек, исключая любые ячейки, которые могут содержать нулевые значения. В частности, ему нужно усреднить ячейки A1, C1, E1, G1 и J1, но подсчитать только те ячейки, которые не содержат нуля.

Прежде чем перейти к тому, что работает, давайте посмотрим, что не работает.

Во-первых, просто сложить ячейки и разделить на 5 не получится; при этом не учитываются нулевые значения. Во-вторых, нельзя использовать СЧЁТЕСЛИ в знаменателе вашей формулы, как показано здесь:

=(A1+C1+E1+G1+J1) / COUNTIF(A1:J1,"<>0")

Это не работает, потому что он исследует и подсчитывает ячейки в пределах всего диапазона A1: J1, а не только пяти ячеек, которые вы хотите рассматривать в среднем. Вы также можете подумать, что можете выбрать пять несмежных ячеек, дать им имя, а затем использовать это имя в формуле. Хотя Excel позволяет вам создать имя, следующее выдает ошибку:

=SUM(MyCells) / COUNTIF(MyCells,"<>0")

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

=AVERAGEIF(MyCells, "<>0")

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

=(A1+C1+E1+G1+J1) / ((A1<>0)+(C1<>0)+(E1<>0)+(G1<>0)+(J1<>0))

Оценка, выполненная для каждой ячейки в знаменателе, возвращает либо 1 (для True), либо 0 (для False) в зависимости от того, содержит ли ячейка ненулевое значение или нет. Эта серия значений складывается вместе, обеспечивая необходимое количество ненулевых ячеек для знаменателя.

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

Единственное место, где они имеют значение, — это знаменатель, из-за чего вычислить это среднее значение так сложно.

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

Этот совет (7845) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Averaging_a_Non-Contiguous_Range [Усреднение несмежного диапазона].