SUMIF et COUNTIF étaient excellents lorsqu'ils ont été introduits dans Excel 97. Mais ils ne pouvaient gérer qu'une seule condition. Le nouveau SUMIFS supérieur gère jusqu'à 127 conditions.
Avez-vous remarqué le «S» qui a été ajouté à la fin de SUMIF à partir d'Excel 2007? Alors que SUMIF et SUMIFS sonnent de la même manière, le nouveau SUMIFS peut faire tourner des cercles autour de son frère aîné.
Les anciens SUMIF et COUNTIF existent depuis Excel 97. Dans la figure ci-dessous, la formule indique à Excel de parcourir les noms en B2: B22. Si un nom est égal au nom en F4, additionnez la cellule correspondante de la plage commençant en D2. (Je me rends compte que la plupart des gens utiliseraient $ D $ 2: $ D $ 22 comme dernier argument, mais il suffit de spécifier la cellule du haut.)
SUMIF et COUNTIF étaient super quand vous n'aviez qu'une seule condition. Mais si vous aviez deux ou plusieurs choses à vérifier, vous deviez passer à SUMPRODUCT. (Je me rends compte que la plupart des gens remplaceraient mes signes de multiplication par des virgules et ajouteraient un double moins avant les deux premiers termes, mais le mien fonctionne aussi.)
SUMIFS permet jusqu'à 127 conditions. Étant donné que vous pouvez avoir un nombre indéterminé de conditions dans la fonction, les nombres que vous additionnez passent du troisième argument au premier argument. Dans la formule suivante, vous additionnez D2: D22, mais uniquement les lignes où la colonne B est Allen Matz et la colonne C est Widget.
Excel 2007 a également ajouté des versions plurielles de COUNTIFS et AVERAGEIFS. Toutes ces fonctions "S" sont très efficaces et rapides.
Merci à Nathi Njoko, Abshir Osman, Scott Russell et Ryan Sitoy.
Regarder la vidéo
- SUMIF et COUNTIF existent depuis Excel 97
- Ils ne géraient pas bien plusieurs conditions - vous deviez utiliser SUMPRODUCT
- À partir d'Excel 2007, l'équipe Excel a ajouté des versions plurielles: SUMIFS, COUNTIFS
- Ils ont également raccourci SUMIF () / COUNTIF en AVERAGEIF
- Et ils ont ajouté AVERAGEIFS
- Dans la version d'Office 365 de février 2016, ils ont ajouté MAXIFS, MINIFS, IFS
- Mais toujours pas de ROMANIFS
- Merci à Nathi Njoko, Abshir Osman, Scott Russell et Ryan Sitoy.
- Excel Guru Mission Patch si vous pouvez comprendre comment ROMANIFS fonctionne à la fin.
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2043 - SUMIFS!
D'accord, je baladore tous mes conseils de ce livre, cliquez sur le «i» dans le coin supérieur droit pour accéder à la liste de lecture de toutes ces vidéos!
De retour dans l'ancien temps d'Excel 97, ils ont introduit deux grandes fonctions appelées SUMIF et COUNTIF, voici SUMIF. SUMIF dit "Parcourez la plage de noms de représentants là-bas dans la colonne B," j'appuie sur F4, "voyez si c'est égal à Allan Matz, virgule, et si c'est le cas, additionnez la cellule correspondante de la colonne D!" Bon, maintenant bon, dans le passé, j'aurais peut-être remarqué que vous pouviez simplement mettre D2 là-bas, et cela rendra automatiquement la forme la même. Mais c'était un mauvais conseil, car lorsque vous faites cela, cette formule devient volatile, et c'est une mauvaise chose, vous ne voulez jamais avoir de formules volatiles, d'accord. Donc SUMIF regarde Allan Matz, a trouvé 1543 unités, et quand nous copions cela, nous pouvons voir ce que chaque personne a fait. C'est génial, ils avaient SUMIF puis COUNTIF. COUNTIF nous dirait combien de commandes il y a eu pour chacune de ces personnes,mais cela est devenu un problème énorme, lorsque nous avons eu deux ou plusieurs conditions. Vous avez dû passer à l'utilisation de SUMPRODUCT, et je suis content de ne pas avoir à expliquer SUMPRODUCT, du moins pas pour cela.
Parce que maintenant dans Excel 2007, ils nous ont donné SUMIFS, c'est là que nous voulons voir combien d'unités Allan Matz a vendu de Widgets, nous devons donc vérifier à la fois la colonne B et la colonne C. Donc, la version plurielle, = SUMIFS, et vraiment vous pouvez commencez à utiliser SUMIFS pour tout, car il est très polyvalent, il gérera une condition, ou 126-127 conditions, ou quelque chose comme ça. OK maintenant, voici la chose étrange, généralement la plage de somme était la dernière chose que nous avons spécifiée, ils inversent cela et c'est la première chose que nous spécifions. Très bien, c'est donc ce que nous allons additionner, puis la gamme de critères et les critères. Donc, la plage de critères 1 est de regarder toutes les répétitions de la colonne B, je vais appuyer sur F4 et voir si c'est = Allan Matz.
Maintenant, je dois pouvoir copier ceci vers la droite, donc je veux appuyer 3 fois sur F4, cela met un seul $ avant le F, le 4 est autorisé à changer, mais le F va être verrouillé, virgule. Condition suivante, parcourez tous les noms de produits dans la colonne C, F4, voyez si cela est égal à Widget, dans ce cas, j'appuie sur F4 2 fois pour le verrouiller à la ligne. D'accord, alors Allan Matz, 1290 widgets et 253 gadgets, double-cliquez pour abattre cela, et nous pouvons voir pour chacune de ces personnes combien de widgets, combien de gadgets, d'accord.
Donc SUMIFS, nouveau dans Excel 2007, voici l'aperçu. Excel 97, SUMIF et COUNTIF. Excel 2007 introduit SUMIFS et COUNTIFS, il a raccourci SUMIF / COUNTIF en AVERAGEIF, puis nous a donné la version plurielle de cela, AVERAGEIFS. Puis plusieurs années plus tard, Excel 2016, pas toutes les versions, vous devez être un Office 365 et la version de février 2016, ils ont ajouté MAXIFS, MINIFS et IFS. Notez ici, ils ne se sentent plus obligés de nous donner MAXIF et MINIF, et je suis d'accord avec cela, car MAXIFS fera les deux, d'accord. Excel vNext, ou peut-être que nous pouvons simplement, vous savez, regarder le, il s'agira d'Excel 2026, ils en ajouteront probablement un autre lot, et j'espère ROMANIFS et ARABICIFS!
OK, 40, les meilleurs conseils Excel de tous les temps, plus 30 autres conseils bonus pour l'anniversaire d'Excel, tous, tous ces épisodes de podcast, en revenant à quoi, le 1er août! Tout ce dont nous avons parlé depuis le 1er août dans ce livre unique, 25 $ en version imprimée, 10 $ par e-book, cliquez sur le «i» dans le coin supérieur droit, et vous pouvez y accéder. D'accord, récapitulatif de l'épisode: SUMIF et COUNTIF existent depuis Excel 97, ils ne gèrent pas bien plusieurs conditions, vous avez dû utiliser SUMPRODUCT. Mais ensuite, ils ont ajouté la version plurielle, SUMIFS et COUNTIFS, ils ont également raccourci SUMIF / COUNTIF en AVERAGEIF, super, puis nous ont donné AVERAGEIFS. Puis, tout récemment en février 2016, ils nous ont donné MAXIFS, MINIFS et la fonction IFS, il y a une vidéo sur les 3 de ceux-ci, je vais mettre un lien juste là, oui, dans ce «i» là, quand même , pas de ROMANIFS. Maintenant tu sais,J'ai demandé aux gens quels étaient leurs conseils Excel préférés, et Nathi, Abshir, Scott et Ryan ont tous défini cela comme leur conseil préféré.
Alors merci à eux, et merci à VOUS de votre visite, nous vous reverrons la prochaine fois pour une autre diffusion sur le net!
Vous êtes toujours là… D'accord, je pense que ROMAN est l'une des fonctions les plus stupides de tous les Excel, même si ce fut la naissance de ce livre, «MrExcel XL»! Bon, mon 40e livre, mais franchement, ils pourraient vraiment faire un ROMANIFS! D'accord, voici comment cela fonctionnerait, = ROMANIFS, vous additionnez ces chiffres romains ici, car c'est la version plurielle dans laquelle vous devez les mettre en premier. F4, virgule, puis regardez à travers ces régions, F4, et voyez si c'est égal à Est, et c'est là, non? Ça + ça + ça = ça! Hé, j'ai un patch de mission de gourou Excel pour la première personne sur YouTube - qui peut me dire comment j'ai fait ça? ce n'était pas VBA!
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2043.xlsm
