У Сьюзи есть рабочий лист, в котором довольно много данных. Для нее нет ничего необычного в том, чтобы скрыть столбцы на листе. Ей нужна формула, которая будет возвращать среднее значение диапазона ячеек в одной строке, игнорируя любые скрытые ячейки. Например, если Сьюзи выбирает B7: G7, ей нужно усреднить только видимые ячейки в этом диапазоне (столбцы D и E в данном случае скрыты).

В Excel нет встроенной функции для вычисления такого среднего значения. Если вы пытаетесь найти среднее значение диапазона ячеек в столбце, вы можете использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ следующим образом:

=SUBTOTAL(101,A7:A12)

Однако функция ПРОМЕЖУТОЧНЫЙ ИТОГ не возвращает точное значение, если указанный диапазон находится в пределах строки. При желании вы можете использовать несколько вспомогательных ячеек для вычислений. Просто введите в ячейку B8 следующее:

=(CELL("width",B7)>0)+0

Скопируйте ячейку B8 в диапазон C8: G8. В результате каждая ячейка в диапазоне B8: G8 будет содержать либо 0, либо 1 в зависимости от того, является ли столбец скрытым или нет. Затем вы можете использовать следующую формулу для определения среднего значения:

=SUMIFS(B7:G7,B8:G8,">0")/SUM(B8:G8)

Он вычисляет среднее значение только для тех столбцов, в которых ячейки в B8: G8 содержат значение 1 (они не скрыты).

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

Function AverageVisible(rng As Range)

Dim rCell As Range     Dim iCount As Integer     Dim dTtl As Double

iCount = 0     dTtl = 0     For Each rCell In rng         If rCell.ColumnWidth > 0 _           And rCell.RowHeight > 0 _           And Not IsEmpty(rCell) _           And IsNumeric(rCell.Value) Then               dTtl = dTtl + rCell               iCount = iCount + 1         End If     Next     If iCount > 0 Then         AverageVisible = dTtl / iCount     Else         AverageVisible = 0     End If End Function

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

=AverageVisible(B7:G7)

Функция проверяет каждую ячейку в диапазоне (что означает, что вы можете использовать ее для строк, столбцов или вообще любого диапазона), чтобы убедиться, что она не скрыта и не пуста. Если ячейка содержит числовое значение, оно используется для вычисления среднего значения. Если указанный диапазон не содержит значений, которые можно усреднить, функция возвращает значение 0.

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

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

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

Этот совет (13262) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.