SUMIF et COUNTIF sont pratiques pour de simples SUM et COUNT sur seulement certaines des valeurs d'une plage, mais existe-t-il une version plus générale qui me permettrait d'insérer n'importe quelle autre fonction ? Par exemple, je pourrais vouloir obtenir l'écart-type des valeurs correspondant à certains critères, mais il n'existe pas de fonction de ce type. STDDEVIF . Sinon, quel est le meilleur moyen de le faire, sans copier-coller les données, évidemment ? J'utilise Excel 2007.
Réponses
Trop de publicités?
divideandconquer.se
Points
3529
Convertissez votre plage de données en tableau et ajoutez une ligne de totaux (clic droit -> Tableaux -> Ligne de totaux). Par défaut, cette ligne affichera le SUM mais vous pouvez facilement le changer pour une série d'autres fonctions, notamment STDDEV . Ensuite, appliquez un filtre numérique à la colonne du tableau.
Edit : (Répondre au commentaire)
- Assurez-vous que vos colonnes de données ont toutes un nom dans la première ligne.
- Sélectionnez la plage, y compris la première ligne contenant les noms.
- Pulse Ctrl + L (Windows) et assurez-vous que vous avez sélectionné "Mon tableau a des en-têtes". Cliquez sur OK.
- Vous devriez maintenant voir qu'Excel a appliqué un nouveau formatage à vos données et que vous avez des filtres déroulants sur chaque en-tête de colonne - vous avez maintenant un fichier Excel. tableau .
- Vous pouvez ajouter une ligne de total en cliquant avec le bouton droit de la souris n'importe où sur la colonne pour laquelle vous voulez un total. Sélectionnez maintenant Tables -> Totaux dans le menu contextuel. Par défaut, vous obtiendrez le total (
SUM) des données de la colonne. - Sélectionnez la cellule contenant le total et une flèche déroulante apparaîtra à droite de celle-ci. Vous pouvez cliquer sur celle-ci pour changer la fonction de
SUMà la fonction souhaitée, par exempleStdDev. - La valeur est calculée à partir de toutes les valeurs visible dans la colonne. Vous pouvez appliquer des filtres à une ou plusieurs colonnes en cliquant sur les listes déroulantes qui apparaissent dans les lignes d'en-tête et en utilisant des filtres de nombres ou d'étiquettes. La valeur calculée sera modifiée en conséquence.
Vous devriez examiner les fonctions de la base de données. Elles sont beaucoup plus puissantes que Sumif dans la mesure où les critères sont illimités. Ils peuvent être aussi complexes ou simples que vous le souhaitez. Il existe également des fonctions d'écart type dans la catégorie des bases de données. Elles ne vous permettent pas d'insérer N'IMPORTE QUELLE fonction, mais cela résoudrait votre problème d'écart type.
user42817
Points
21
Pas vraiment une réponse, mais quelque chose à considérer : Excel n'a pas de fonctions xxxxIF() plus complexes parce qu'il n'y a pas de communauté susceptible de les utiliser. Vos exemples, SUMIF() et STDEVIF(), seraient utilisés par des communautés très différentes. Il est très facile d'imaginer un comptable souhaitant extraire toutes les transactions d'un client spécifique d'un registre global (SUMIF). Les statisticiens, en revanche, doivent justifier très soigneusement la suppression de toute valeur d'un ensemble de données. Les filtres automatiques sont donc peu probables ; les données seraient ajustées à la main avant l'analyse (STDEVIF). Par conséquent, cela va nécessiter un certain degré de manipulation manuelle, ou des formules de cellule très complexes.
Y a-t-il une raison pour laquelle vous ne voulez pas ajouter une table temporaire pour l'espace scratch ? Je me contenterais d'insérer une feuille de calcul et de sélectionner manuellement les données à copier, ou d'écrire des formules pour extraire des données de la feuille de calcul d'origine. Une fois que vous vous êtes occupé de la partie "IF", analysez. Vous pouvez supprimer ou masquer la feuille de calcul initiale lorsque vous avez terminé.
John C
Points
451
Une autre approche que vous pouvez envisager est l'utilisation d'une colonne "Helper" pour signaler les données filtrées :
Vous pouvez ajouter une colonne de données et utiliser cette colonne pour déterminer si une ligne est masquée (filtrée) ou non. Les valeurs de la colonne A étant nominalement remplies de données, vous pouvez utiliser, par exemple, la colonne Z (nouvelle colonne Filtre) avec la formule "=sous-total(3,A2)" (à partir de la ligne 2) et demander à Excel de remplir automatiquement la ligne jusqu'à la fin avec la poignée Cntrl+. Une fois configuré, vous pouvez éventuellement masquer votre nouvelle colonne de données Filtre.
Une fois que vous avez configuré votre nouveau Filter col Z, vous pouvez alors l'utiliser avec COUNTIFS ou simplement avec des conditionnels IF normaux. Avec COUNTIFS vous pouvez utiliser de manière similaire à COUNTIFS(Data_Rng,Data_Val,Filter_Rng,1) qui filtrera le comptage de Data_Rng pour les valeurs égales à Data_Val uniquement dans les lignes non cachées déterminées par Filter_Rng égal à 1. Veillez à ce que vos plages aient la même longueur, sinon Excel générera une erreur.
Si COUNTIFS ne vous donne pas ce dont vous avez besoin, vous pouvez également configurer des valeurs conditionnelles dérivées basées sur la colonne Z du filtre et utiliser le remplissage automatique d'Excel pour remplir ces valeurs IF(Z2=1,compute-new-derived value,error).
