Comment puis-je additionner les valeurs d'une plage nommée des valeurs qui contient des erreurs?

J'ai une feuille de calcul Excel qui importe des données provenant d'une autre source que j'ai besoin d'exécuter un calcul sur. Les données dont j'ai besoin pour travailler avec est dans une plage nommée -, il arrive à être dans la gamme C12:C36 - mais comme il est appelé "SumData".

Les valeurs dans cette gamme contiennent un certain nombre d'erreurs comprenant actuellement #NUM! et #N/A, mais pourraient éventuellement contenir d'autres erreurs. J'ai besoin de la somme de ces valeurs.

Je comprends comment faire la somme de la formule:

=SUM(SumData)

Ou

=SUM(C12:C36)

Je peux aussi utiliser IFERROR à vérifier pour les erreurs:

=IFERROR(C12:C36, 0)

Mais IFERROR n'apparaît que pour vérier la ligne correspondante à la formule je suis entrée. c'est à dire si ma formule est entrée en D12, il vérifie seulement pour l'erreur sur la ligne C12, et renvoie la valeur (ou 0 si C12 contient une erreur).

Si je combine les deux pour le faire:

=SUM(IFERROR(SumData,0))

Je n'ai pas de retour valide de valeur. Fondamentalement, ça me donne le plus proche de la ligne correspondante, par exemple:

    C      D
    -----------
12  #NUM!  
13  2      =SUM(IFERROR(SumData,0)) = 2 (I would expect this to produce 48)
14  5
15  7
16  #N/A
17  23
18  6
19  5

Ce que je fais mal?

InformationsquelleAutor BobTheBuilder | 2011-01-19
  1. 8
    =SUM(IF(ISNUMBER(SumData),SumData))

    entrée comme une formule de matrice... c'est à dire à l'aide de CTRL+Maj+Entrée et non PAS juste à l'Entrée. Si vous le faites correctement, Excel va mettre des accolades autour de la formule{}.

    • Merci, je l'ai eu, mais n'a pas frappé le CTRL+SHIFT+ENTER - je n'ai jamais trouvé aucune référence à ce!
    InformationsquelleAutor Mark Baker
  2. 3

    Essayez d'Utiliser cette Formule si vous avez tendance à ajouter une plage de cellules qui contient une erreur: 

    =SUMIFS(sumrange,criteria range1,"<>"&"#NAME?",criteria range2,"<>"&"#N/A",criteria range3,"<>"&"NUM!")

    Car vous ne savez pas lequel la cellule a l'erreur, alors il est conseillé d'utiliser les critères gammes de même que la sumrange.

    InformationsquelleAutor Celo
  3. 0

    Malheureusement, c'est la documentation de la fonctionnalité de la fonction sierreur, que l'on trouve ici: http://office.microsoft.com/en-us/excel-help/iferror-function-HA001231765.aspx.

    Essentiellement, une GAMME d'entrée ne donne pas de résultat dans une GAMME de sortie. Au contraire, vous obtenez le résultat de la position dans la fourchette indiquée par la CELLULE où la IFERROR appel arrive. C'est assez peu intuitive.

    Une solution consiste à insérer une colonne à tenir l'individu IFERROR résultats, et somme plus que, comme indiqué ci-dessous. Après avoir configuré la colonne D, il suffit de faire caché.

        C      D                      E
    --------------------------------------
12  #NUM!  =IFERROR(E12, 0) -> 0
13  2      =IFERROR(E13, 0) -> 2  =SUM(E12:E19) -> 48
14  5      =IFERROR(E14, 0) -> 5
15  7      =IFERROR(E15, 0) -> 7
16  #N/A   =IFERROR(E16, 0) -> 0
17  23     =IFERROR(E17, 0) -> 23
18  6      =IFERROR(E18, 0) -> 6
19  5      =IFERROR(E19, 0) -> 5
    • CTRL+MAJ+ENTRÉE comme par @Mark Baker exemple travaillé comme un régal. Je suppose qu'il traite de la fonction au sein de Somme(thisBit) comme un délégué au lieu d'une valeur lorsque vous utilisez les touches CTRL+MAJ+ENTRÉE. C'est un peu sucré de la fonctionnalité, je n'ai jamais trouvé documenté nulle part ailleurs!
    • "traite de la fonction au sein de Somme(thisBit) comme un délégué" - Wow, c'est incroyable une explication de la façon dont il fonctionne! J'ai vu la Marque de la réponse et de la pensée, il n'était pas intuitive, mais cette explication a vraiment aidé à solidifier dans mon esprit. Merci!
    InformationsquelleAutor Chris Ammerman
  4. 0

    Cela fonctionne pour moi:

    =IF(SUMIFS(RANGE,CRIT-RANGE,CRITCELL)=0,NA(),SUMIFS(RANGE,CRIT-RANGE,CRITCELL))

    Mais j'utilise des tables qui sont affichées; par conséquent, j'ai dû pousser un format conditionnel sur elle.

    Si la Cellule Contient une Erreur: Faire de la police {même couleur que l'arrière-plan}

    Fonctionne très bien pour moi et les parcelles de mon graphique bien sans avoir à vraiment afficher les données (jusqu'à ce que quelqu'un sélectionne la cellule et voit la valeur).

    InformationsquelleAutor Kevin Ellen