Susie a une feuille de calcul qui contient pas mal de données. Il n’est pas inhabituel pour elle de masquer des colonnes dans la feuille de calcul. Elle a besoin d’une formule qui renverra la moyenne d’une plage de cellules, toutes dans la même ligne, tout en ignorant les cellules masquées. Par exemple, si Susie sélectionne B7: G7, elle souhaiterait une moyenne des seules cellules de cette plage qui sont visibles (les colonnes D et E, dans ce cas, sont masquées).

Il n’y a pas de fonction intrinsèque dans Excel pour calculer une telle moyenne. Si vous essayez de trouver la moyenne d’une plage de cellules dans une colonne, vous pouvez utiliser la fonction SOUS-TOTAL, de cette manière:

=SUBTOTAL(101,A7:A12)

Cependant, la fonction SUBTOTAL ne renvoie pas de valeur précise lorsque la plage fournie se trouve dans une ligne. Vous pouvez, si vous le souhaitez, utiliser des cellules d’aide pour effectuer le calcul. Mettez simplement ce qui suit dans la cellule B8:

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

Copiez la cellule B8 dans la plage C8: G8. Le résultat est que chaque cellule de la plage B8: G8 contiendra un 0 ou un 1 selon que la colonne est masquée ou non. Vous pouvez ensuite utiliser la formule suivante pour déterminer la moyenne:

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

Il calcule la moyenne uniquement pour les colonnes dans lesquelles les cellules de B8: G8 contiennent une valeur de 1 (elles ne sont pas masquées).

Bien sûr, vous ne pourrez peut-être pas utiliser les cellules d’assistance et vous trouverez peut-être plus avantageux de créer une fonction définie par l’utilisateur pour calculer la moyenne. Ce qui suit fonctionne très rapidement:

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

Pour utiliser la fonction, mettez simplement cette formule dans la cellule que vous souhaitez contenir la moyenne:

=AverageVisible(B7:G7)

La fonction vérifie chaque cellule de la plage (ce qui signifie que vous pouvez l’utiliser sur des lignes, des colonnes ou, en fait, sur n’importe quelle plage) pour vous assurer qu’elle n’est ni masquée ni vide. Si la cellule contient une valeur numérique, elle est utilisée pour calculer la moyenne. Si la plage que vous spécifiez ne contient aucune valeur pouvant être moyennée, la fonction renvoie la valeur 0.

La fonction s’exécute automatiquement chaque fois que la feuille de calcul est recalculée. Si vous modifiez les colonnes masquées, Excel ne recalcule pas automatiquement. Ainsi, vous devrez forcer le recalcul après avoir masqué ou affiché des colonnes.

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (13262) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365.