Comment peut-on additionner uniquement les lignes dans Excel non filtrées?

Question

J'utilise la formule SUM (B1..B20) pour additionner une colonne, mais lorsque je filtre les données en fonction d'une colonne adjacente, la somme ne se met pas à jour pour refléter uniquement les lignes non filtrées. Comment est-que quelqu'un peut faire ça?

Answer 1

Vous devez utiliser la sous-totalde la fonction. Le sous-total de la fonction ignore les lignes qui ont été exclus par un filtre.

La formule pourrait ressembler à ceci:

=SUBTOTAL(9,B1:B20)

Le numéro de fonction 9, dit-il à utiliser la SOMME de la fonction sur l'intervalle de données B1:B20.

Si vous êtes"filtrage"par le masquage de lignes, le numéro de fonction doit être mis à jour à 109.

=SUBTOTAL(109,B1:B20)

Le numéro de fonction 109 est la SOMME de la fonction en tant que bien, mais les lignes cachées sont ignorés.

Answer 2

Si vous n'utilisez pas de filtre automatique (c'est-à-dire que vous avez des lignes masquées manuellement), vous devrez utiliser la fonction AGGREGATE au lieu de SUBTOTAL .

Answer 3

Lorsque vous utilisez le filtre automatique pour filtrer les résultats, Excel n'est même pas la peine de les cacher: il définit la hauteur de la ligne de zéro (jusqu'à 2003, au moins, pas de certitude sur 2007).

Si la fonction personnalisée suivante devrait vous donner un démarreur à faire ce que vous voulez (testé avec des entiers, n'ont pas joué avec n'importe quoi d'autre):

Function SumVis(r As Range)
    Dim cell As Excel.Range
    Dim total As Variant

    For Each cell In r.Cells
        If cell.Height <> 0 Then
            total = total + cell.Value
        End If
    Next

    SumVis = total
End Function

Edit:

Vous aurez besoin de créer un module du classeur pour mettre la fonction, alors vous pouvez simplement appeler votre feuille comme toute autre fonction (=SumVis(A1:A14)). Si vous avez besoin d'aide pour configurer le module, faites le moi savoir.

Answer 4

Un filtre ne cache que les lignes de vue. Cela signifie que SUM(B1:B20) sera toujours la somme de toutes les lignes dans la gamme, même si certaines lignes sont cachés entre les deux. Je ne pense pas qu'il n'y a même une fonction qui prend le "visible" état de cellules en compte.

Une alternative serait d'utiliser SUMIF() et la rendre dépendante de la colonne adjacente.

Un quick-and-dirty solution serait de placer le filtre, et de recréer l' SUM() la fonction sur la plage filtrée. Excel puis de créer quelque chose comme ceci: SUM(B1:B3;B17;B19:B20), qui ne comprennent actuellement visibles sous-gammes. Si vous remplacez le filtre, ce serait encore rester les mêmes, bien sûr.