C'est le problème du budget des pompiers. Les gens d'une caserne de pompiers ont mal fait leurs budgets dans Excel. Une transformation Power Query étonnante fournit la solution.
Regarder la vidéo
- Steve doit additionner les nombres qui ont été saisis dans une colonne de texte
- Il y a plusieurs lignes dans chaque cellule, séparées par alt = "" + Entrée
- Besoin de diviser ces lignes en lignes, puis d'analyser le montant en dollars du milieu de chaque cellule
- Résumer par centre de coûts
- Créer une table de consultation
- Obtenez les totaux de la table de recherche, en utilisant IFNA pour ignorer les erreurs dans la ligne vide
- Bonus: ajoutez une macro d'événement pour mettre à jour la feuille de calcul lorsqu'ils modifient une cellule.
Transcription vidéo
Apprenez Excel à partir de, Podcast Episode 2160: Données SUM qui ont été Alt + Entrées.
Hey. Bienvenue à nouveau sur le netcast. Je suis Bill Jelen. Je ne l'invente pas. J'ai une question de quelqu'un qui a des données - des données budgétaires - qui ressemble à ceci. Maintenant, j'ai mis de faux mots ici pour que nous n'ayons pas leurs informations budgétaires, mais une personne nouvelle au service de comptabilité est allée dans une entreprise, et cette société depuis des années fait ses budgets comme ça. Ce ne sont pas des comptables qui font le budget, ce sont des employés hiérarchiques, mais c'est ainsi qu'ils le font et il ne peut pas les amener à changer. Alors, voici notre objectif. Il dit que c'est aussi mauvais que de taper le budget dans Word.
Eh bien, presque, mais heureusement, grâce à Power Query, cela va sauver notre problème. Voici notre objectif. Pour chaque CENTRE DE COÛTS ici, nous voulons rapporter le total de tous ces chiffres. Donc, il y a le nom de la dépense, un -, régulièrement un -, puis un signe $, et ensuite, juste pour rendre la vie intéressante, de temps en temps, une note au hasard après; pas tout le temps, juste une partie du temps. Ligne vide entre chacun. Des tonnes et des tonnes de données.
Alors, voici ce que je vais faire. Je vais descendre tout en bas, la toute dernière cellule, je vais sélectionner tout cela, y compris les en-têtes. Je vais créer un NOM. Je vais l'appeler MyData. MyData, comme ça, d'accord? Bien. Nous allons maintenant utiliser Power Query qui est gratuit en 2010 ou 2013, intégré à 2016 et 2016 Office 365. Cela va venir d'une TABLE OU d'une RANGE. Bien. Tout d'abord, à chaque fois que nous avons ces blancs dans la COLONNE A, tous les NULLS dont nous voulons nous débarrasser. Je vais donc décocher NULL. Impressionnant. Bien. Vraiment, dans ces données, dans cette version des données, parce que je vais créer un VLOOKUP, nous n'avons pas besoin de cette colonne. Donc, je vais faire un clic droit et me débarrasser de cette colonne, donc SUPPRIMER la colonne.
Bien. Maintenant, voici où la magie effrayante va se produire. Choisissez cette colonne, SPLIT COLUMN BY A DELIMITER, et nous allons certainement passer à ADVANCED. Le délimiteur va être un caractère spécial et nous allons diviser chaque occurrence du délimiteur. Donc, ici, je pense qu'ils l'ont déjà compris parce que je l'ai développé, mais je vais vous montrer. INSÉRER UN CARACTÈRE SPÉCIAL. Je vais dire que c'est un LINE FEED, d'accord, donc, à chaque occurrence du LINE FEED, et je vais SPLIT INTO ROWS. D'accord, et ce qui va se passer ici est, 1, 2, 3, 4, 5, je vais avoir 5 lignes ou je vais dire 1001, mais, dans chaque ligne, ça va avoir un différent ligne de cette cellule. Ceci est incroyable. Il y a 1, 2, 3, 4, 5, 1001. Très bien. Il ne nous reste plus qu'à analyser ce mauvais garçon. Bien,alors, choisissez cette colonne, SPLIT COLUMN BY A DELIMITER. Cette fois, un délimiteur sera un signe $. C'est parfait, une fois, au premier signe $ que nous trouvons, juste au cas où il y aurait un signe $ dans la partie future. Nous allons diviser en colonnes. Cliquez sur OK. Bien. Donc, il y a des détails. Voici notre argent.
Maintenant, je vais partager ça au SPACE. Alors, choisissez cette colonne, SPLIT COLUMN BY A DELIMITER, et le délimiteur va être un ESPACE, oui, une fois au DELIMITER LE PLUS GAUCHE, cliquez sur OK, et je n'ai pas besoin de ces commentaires, donc ces commentaires nous ' allez à SUPPRIMER. En fait, je n'ai pas besoin de ça non plus parce que j'essaie juste d'obtenir un total de tous ces trucs, donc je vais SUPPRIMER.
Maintenant, transformez-vous. GROUPE PAR CENTRE DE COÛTS, LE NOUVEAU NOM DE COLONNE s'appellera TOTAL, l'OPÉRATION sera la SOMME, et quelle colonne allons-nous SOMME? Les DÉTAILS 2.1. Beau. Cliquez sur OK, d'accord, et nous obtenons une ligne par COST CENTER avec le TOTAL de tous ces éléments de campagne. ACCUEIL, FERMER ET CHARGER. Il va probablement insérer une nouvelle feuille de calcul. J'espère qu'il insère une nouvelle feuille de calcul, et c'est le cas, et cette feuille de calcul s'appelle MYDATA_1. MYDATA_1.
Bien. Nous allons maintenant revenir ici dans les données d'origine et effectuer ces étapes. Sur le tout premier, = VLOOKUP de 1001 dans nos résultats. C'est un peu comme mettre en place une référence circulaire, mais cela ne nous donnera pas une référence circulaire. , 2, FAUX. Je veux la correspondance exacte. D'accord, mais nous n'allons pas vouloir faire cela pour les cellules vides. Donc, je vais dire, eh bien, en fait, copions-le complètement. CONTROL + C, descendez tout en bas juste pour voir ce que nous obtenons. Peut-être que nous obtenons N / A et que je peux m'en débarrasser avec l'IFNA. Ouais, magnifique, d'accord. Alors, débarrassons-nous simplement des N / As. Si N / A, alors nous voulons juste «». Nous ne voulons rien là-dedans. CONTROL + ENTER. Bien. Maintenant, cela devrait être le TOTAL. Voyons si nous pouvons en trouver une courte et faire le calcul. = 627,37 + 7264,25 + 6066.01 + 4010,66 + 9773,94, et le TOTAL, 27742,23 est cela. Super génial. (= IFNA (RECHERCHEV (A2, MesDonnées_1,2, FALSE), «»))
Maintenant, voici l'affaire. Donc, nous avons ces gens de ligne qui sont ici en train de changer des choses, d'accord, et disons qu'ils passent et qu'ils changent le budget, 40294.48, et ils viennent ici et changent celui-ci en 6000, comme ça, et ils ajoutent un nouveau, ALT + ENTRÉE, QUELQUE CHOSE - signe $, 1000 $ vient d'être ajouté. Bien. Maintenant, bien sûr, lorsque j'appuie sur ENTRÉE, ce numéro, 40294.48, ne va pas être mis à jour, d'accord, mais ce que nous devons faire est d'aller dans l'onglet DONNÉES et nous voulons TOUT REFRESH. Donc, 40294.48. Regardez, regardez, regardez, regardez. RAFRAÎCHIR TOUT. Freaking incroyable.
J'adore la requête de puissance. La requête de puissance est la chose la plus étonnante. Ces données, qui sont essentiellement des données de mots dans une cellule, nous les avons maintenant mises à jour. Vous pourriez probablement même créer une sorte de macro qui dit que chaque fois que quelqu'un change quelque chose dans la COLONNE C, nous cliquons sur REFRESH ALL en utilisant la macro et avons simplement ces résultats constamment, constamment rafraîchissants.
Quelle horrible question envoyée. Je me sens mal pour Steve qui doit faire face à cela, mais maintenant, en utilisant Power Query dans Office 365 ou téléchargé pour 2010 ou 2013, vous avez un moyen très, très simple de résoudre ce problème.
Attendez. D'accord, un addendum: rendons les choses encore meilleures. Cette feuille s'appelle DATA et j'ai enregistré le classeur en tant que macro-activé, donc xlsm. Si vous êtes xlsx, n'ignorez pas l'enregistrement au format xlsm. ALT + F11. Trouvez le classeur appelé DATA, double-cliquez, en haut à gauche, WORKSHEET, puis CHANGE chaque fois que nous modifions la feuille de calcul, et nous allons dire ACTIVEWORKBOOK.REFRESHALL, puis fermez, d'accord, et maintenant essayons-le. Modifions quelque chose. Donc, nous allons prendre ces framboises qui sont actuellement 8 000 et nous allons le changer à 1 000, donc nous réduisons de 7 000. Quand j'appuie sur ENTRÉE, je veux que 42 000 passent à 35 000. Ah. Impressionnant.
Et bien Salut. C'est là que je vous supplie habituellement d'acheter mon livre mais, aujourd'hui, je vais vous demander d'acheter le livre de mes amis - Ken Puls et Miguel Escobar - M est pour (DATA) MONKEY. Tout ce que j'ai appris sur Power Query, je l'ai appris de ce livre. C'est un livre incroyable. Vérifiez cela.
Récapitulation de l'épisode: Steve a des nombres à additionner qui ont été entrés dans une colonne de texte; plusieurs lignes dans chaque cellule, séparées par ALT + ENTRÉE; besoin de diviser ces lignes en lignes, puis d'analyser le montant en dollars du milieu de chaque cellule; résumer par COST CENTER; construire une table de consultation; obtenir les totaux de la table de recherche, en utilisant IFNA pour ignorer les erreurs dans la ligne vide; puis, un bonus, une macro à la fin, une macro d'événement pour mettre à jour la feuille de calcul lorsqu'ils changent de cellule.
Je tiens à remercier Steve d'avoir envoyé cette question et je suis si heureux d'avoir une réponse - avant la demande de puissance, cela aurait été vraiment, vraiment difficile - et je tiens à vous remercier d'être passé. Nous vous reverrons la prochaine fois pour un autre netcast de.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2160.xlsm
