Créez des totaux ad hoc pour un ensemble de données Excel dans lequel vous filtrerez les données. Également dans cette vidéo: Utilisation de SUBTOTAL (109,) pour additionner des données qui ont été des lignes masquées manuellement. Utilisation de Ctrl + Ditto pour copier la formule exacte d'une ligne vers le bas.
Après avoir appliqué un filtre, ce serait formidable de voir le total des cellules visibles.
Sélectionnez la cellule vide sous chacune de vos colonnes numériques. Cliquez sur le bouton Somme automatique.
Au lieu d'insérer des formules SUM, Excel insère des =SUBTOTAL(9,… )formules. La formule ci-dessous vous montre le total des cellules visibles uniquement.
Insérez quelques lignes vides au-dessus de vos données. Coupez les formules sous les données et collez-les à la ligne 1 avec l'étiquette Total visible.
Désormais, lorsque vous modifiez les filtres, même si les données remplissent plus d'un plein écran de données, vous verrez les totaux en haut de votre feuille de calcul.
Merci à Sam Radakovitz de l'équipe Excel pour Filtrer par sélection - pas pour avoir suggéré Filtrer par sélection, mais pour avoir formalisé Filtrer par sélection!
Regarder la vidéo
- Créez des totaux ad hoc pour un ensemble de données Excel dans lequel vous filtrerez les données. Également dans cette vidéo: Utilisation de SUBTOTAL (109,) pour additionner des données qui ont été des lignes masquées manuellement. Utilisation de Ctrl + Ditto pour copier la formule exacte d'une ligne vers le bas.
Transcription vidéo
Le podcast est sponsorisé par "MrExcel XL", un livre avec plus de 40 astuces Excel, des dessins animés Excel, des cocktails, des tweets et des blagues.
Apprenez Excel à partir du podcast, épisode 1976 - Total des cellules visibles!
Salut, bon retour sur le netcast, je suis Bill Jelen. Bien sûr, tout le monde, la toute première formule que la plupart des gens créent, c'est la somme automatique. Vous êtes ici en dessous d'une colonne de nombres, vous obtenez le bouton Somme automatique, et il utilisera la fonction de somme pour totaliser les nombres au-dessus, jusqu'à ce qu'il rencontre une formule ou du texte. Droite? Ou Alt + = fait exactement la même chose. Ou, encore mieux, sélectionnez plusieurs cellules, puis appuyez sur la somme automatique, et il mettra toutes ces formules, sans que vous ayez à confirmer les formules.
Mais voici le truc sympa, d'accord, alors activons le filtre automatique. Nous allons donc utiliser l'astuce Filtrer par sélection, du podcast d'hier. Les données sont filtrées. Je vais aller à la toute première ligne vide sous les données, puis appuyer sur le bouton Somme automatique. Mais quelque chose de très différent se passe, voyez-vous. Au lieu de la fonction SOMME, nous obtenons = SOUS-TOTAL (9! Et ce que c'est, c'est le total des cellules visibles. Très bien, très cool que cela fonctionne.
Maintenant, voici le truc, je faisais un séminaire Power Excel en direct il y a presque dix ans, à Fort Wayne, IN, pour l'Institut des auditeurs internes. Et quelqu'un m'a montré ce truc, et j'aime vraiment le truc. Je n'avais jamais vu le truc avant, c'était cool, j'ai dit que c'était génial, je vais l'ajouter au séminaire. Et puis l'année suivante, j'étais de retour à Fort Wayne, et le manager de ce type se présente, d'accord, il n'était pas venu la première année, parce qu'il pensait tout savoir sur Excel. Mais il est revenu pour réclamer cette astuce et il a dit: "Écoutez, j'ai entendu dire que mon analyste financier vous a dit cette astuce, mais il ne vous a pas dit la meilleure partie, montez au-dessus de vos données et insérez deux lignes là-haut. Et puis une fois que vous avez le total des cellules visibles en bas, coupez, CTRL + X et collez, CTRL + V, d'accord, ce que cela fait, c'estsi vous passez à un autre client, "Très bien, je vais donc choisir quelques clients de plus ici." Et maintenant, j'ai plus de clients qu'il n'en faut à l'écran! Et je n'ai pas besoin de faire défiler vers le bas pour voir les totaux, les totaux sont toujours là-haut en haut! "
Très bien maintenant, cette astuce, en utilisant la somme automatique sous les données de filtre, fonctionne lorsque vous masquez les lignes avec le filtre. Que faire si vous masquez manuellement les lignes? Alors ici, j'ai sélectionné quelques lignes, je suis déjà allé dans Formater, Masquer et afficher, Masquer les lignes! Alors maintenant, nous avons des lignes cachées. Et je descends en bas, et j'appuie sur Alt + = ou sur la Somme automatique, et cela ne me donne pas la fonction SOUS-TOTAL, cela me donne la fonction SOMME.
Bon maintenant, à ce stade, je vais faire une chose spéciale, je vais faire CTRL + '(idem, apostrophe). Cela réduira exactement la même formule, sans changer la référence de cellule. Donc, c'est le E2: E8 changé, ou n'a pas changé, généralement, si vous copiez et collez un changement vers E3: E9. Je vais changer cela en SOUS-TOTAL (9, et vous verrez que cela ne fonctionne pas, cela nous donne toujours toutes les cellules. 1000 + 400 + 800 n'est pas 4500, cela inclut les lignes masquées. Mais voici ce que vous devez le faire, entrez CTRL + 'et changez ce 9 en 109, et vous obtiendrez: Le vrai total visible basé sur des lignes masquées manuellement!
D'accord, voilà, donc quelques astuces différentes, AutoSum, Alt + = nous donne juste la fonction SUM. Si vous êtes sous les données et que vous appuyez sur la Somme automatique, vous obtiendrez le SOUS-TOTAL (9, qui fonctionne très bien pour filtré, ou vous pouvez simplement taper manuellement le SOUS-TOTAL (109, pour obtenir le total du visible, lorsque vous avez filtré manuellement.
D'accord, voici un récapitulatif des concepts de la vidéo d'aujourd'hui: ce n'est même pas le conseil n ° 3 du livre, c'est le conseil bonus n ° 2b à droite, donc entre le conseil 2 et le conseil 3. Le livre couvre tous ces éléments articles. Eh bien, merci à tous ceux qui ont suggéré cela, les gens du chapitre de Fort Wayne Indiana de l'IIA, c'est là que je l'ai entendu pour la première fois. C'est un excellent livre, vous pouvez l'acheter en version imprimée ou électronique, cliquez sur ce "i" dans le coin supérieur droit. Vous serez redirigé vers plus d'informations sur le livre.
OK, je tiens à vous remercier d'être passé, nous vous verrons la prochaine fois pour une autre diffusion sur le net!
