Les cellules fusionnées sont le problème aujourd'hui, mais ce sont des cellules fusionnées verticales. Jetez un œil à la figure ci-dessous. S'il y a 7 lignes pour la région Midwest, le mot Midwest n'apparaît que sur la première ligne. Quelqu'un fusionne ensuite les six cellules vides restantes avec la cellule Midwest pour créer une cellule fusionnée de 7 cellules de haut.
C'est une manière horrible d'envoyer des données. Si quelqu'un maîtrise Excel, il peut souhaiter trier les données, ajouter des sous-totaux, utiliser des tableaux croisés dynamiques. Les cellules fusionnées empêchent cela.
Pour aggraver les choses, vous recevez chaque jour un nouveau fichier du siège, structuré comme ceci. HQ envoie des fichiers similaires à 500 succursales. N'étant qu'un petit avant-poste, vous n'avez pas beaucoup de chances de convaincre le siège que c'est une façon horrible d'envoyer des données.
L'article d'aujourd'hui explique comment résoudre rapidement ce problème avec Power Query. La solution Power Query sera plus facile les jours 2 à 9999 que les étapes suivantes dans Excel:
Les gourous d'Excel pourraient suggérer ces étapes tous les jours:
- Sélectionnez toutes les cellules en choisissant le rectangle au-dessus et à gauche de A1.
- Cliquez sur le lanceur de boîte de dialogue dans le coin inférieur droit du groupe Alignement de l'onglet Accueil.
- Cliquez deux fois sur la case Fusionner les cellules pour la désélectionner.
- Cliquez sur OK pour fermer la boîte de dialogue Format de cellule
- Sélectionnez de la fin de la colonne A à A1.
- Accueil, Rechercher et sélectionner, Aller à spécial, Blancs, OK
- Tapez = UpArrow. Appuyez sur Ctrl + Entrée
- Sélectionnez de la fin de la colonne A à A1.
- Ctrl + C pour copier. Cliquez avec le bouton droit et collez les valeurs
Mais il existe un moyen beaucoup plus simple. Les nouveaux outils Power Query sont intégrés aux versions Windows d'Office 365 et d'Excel 2016. Si vous disposez d'une version Windows d'Excel 2010 ou d'Excel 2013, vous pouvez télécharger Power Query gratuitement auprès de Microsoft.
Voici la stratégie avec Power Query:
- Faites un plan que vous allez enregistrer le classeur de chaque jour du siège dans le même dossier avec le même nom.
- Ouvrez un nouveau classeur vierge qui contiendra les données fixes du siège.
-
Dans le classeur vide, choisissez Données, Obtenir les données, À partir du fichier, À partir du classeur.
Démarrez le processus Power Query - Accédez au dossier et au fichier à partir de l'étape 1.
-
Lorsque la fenêtre Power Query s'ouvre, notez que les cellules fusionnées ont disparu. Vous avez Midwest à la ligne 1, puis six cellules contenant «null». Sélectionnez cette colonne en cliquant sur l'en-tête Région.
Power Query supprime automatiquement les cellules fusionnées -
Dans Power Query, sélectionnez l'onglet Transformer. Choisissez Remplir, Bas. Le mot Midwest est copié de la ligne 1 aux lignes 2 à 7. Des transformations similaires se produisent dans l'ensemble de données.
Remplissez les cellules nulles avec la valeur ci-dessus -
Accueil, fermeture et chargement. Power Query va se fermer. Dans environ 10 à 20 secondes, les données nettoyées de HQ apparaîtront dans une nouvelle feuille de calcul dans le classeur.
Renvoyer les données dans Excel - Enregistrez le classeur sous un nom tel que CleanedDataFromHQ.xlsx.
- Lorsque les données sont sélectionnées, vous devriez voir le panneau Requêtes et connexions sur le côté droit. Si vous ne voyez pas le panneau, accédez à Données, requêtes et connexions.
-
Cliquez avec le bouton droit sur la requête dans le panneau Requêtes et connexions. Choisissez Propriétés.
Ouvrez les propriétés de cette requête -
Choisissez "Actualiser les données lors de l'ouverture du fichier". Cliquez sur OK.
Définissez la requête pour qu'elle s'exécute chaque fois que vous ouvrez le classeur.
Votre flux de travail devient alors: (a) Recevoir le classeur du siège par e-mail. (b) Enregistrez la pièce jointe dans le dossier approprié avec le nom correct. (c) Ouvrez le classeur CleanedDataFromHQ.xlsx. Les nouvelles données seront chargées dans le classeur.
Regarder la vidéo
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2221: Remplissez les cellules fusionnées vers le bas.
Salut, bon retour sur le netcast. Je suis Bill Jelen, j'étais à Calumet City, Elizabeth se présente et dit qu'elle reçoit ce fichier - notre fichier, comme ce fichier - tous les jours et c'est horrible. Ici, dans la colonne A, ce n'est pas seulement qu'ils ont mis Midwest et laissé un tas de blancs, c'est une cellule fusionnée. Bien? Ils envoient le … HQ envoie ces fichiers dans des endroits du monde entier avec ces cellules fusionnées effrayantes ici. Les cellules fusionnées sont mauvaises.
Très bien, voici comment s'en débarrasser. Tout d'abord, je vais faire un clic droit, déplacer ou copier, créer une copie et la déplacer vers le nouveau livre. Alors nous prétendons que c'est comme le classeur qu'Elizabeth reçoit. Fichier, Enregistrer sous, et chaque fois qu'Elizabeth reçoit l'un de ces classeurs, je veux qu'elle le place exactement au même endroit avec exactement le même nom. Bien? Exact même nom - parfait. Alors maintenant, nous n'avons qu'un seul fichier, une seule feuille, je vais fermer le fichier. Et puis nous allons créer un tout nouveau classeur. Je vais simplement insérer une nouvelle feuille de calcul ici et cette nouvelle feuille de calcul s'appellera le rapport. Et sur ce blanc, notre rapport fera Data, Get Data, From File, From a Workbook, pointer sur le fichier dans lequel nous allons l'enregistrer - donc, avec exactement le même nom à chaque fois - cliquez sur Importer , choisissez la première feuille de calcul,puis cliquez sur Modifier. Bien. Tout d'abord, c'est vraiment génial - power query n'aura aucun de ces absurdités de cellule de fusion, ils convertissent instantanément les cellules de fusion en cellules individuelles et toutes ces cellules sont nulles. Choisissez cette colonne, elle est déjà choisie ici, puis Transformer, Fill, Fill Down, comme ça, puis Home, Close & Load. Très bien, voici mon classeur.
Maintenant, je vois que certains ont des colonnes supplémentaires ici. Permettez-moi de faire un clic droit et nous allons éditer.
Et il semble que tout ce qui se trouve à droite du coût doit disparaître. Combien de trucs supplémentaires là-bas? Faites un clic droit et supprimez ces colonnes - parfait - Fermer et charger. Très bien, maintenant, le flux de travail devient, chaque fois qu'Elizabeth reçoit un nouveau rapport du siège, elle va le sauvegarder au même endroit avec un nom fiable ou autre - peu importe - nous l'appelons, puis ouvrir ce classeur, et puis cliquez ici sur Actualiser.
Ou, si nous voulions travailler automatiquement, faites un clic droit ici, descendez dans Propriétés, juste à l'extérieur de votre écran tout en bas, puis dites: Chaque fois que j'ouvre ce fichier, actualisez les données. Ainsi, chaque fois que j'ouvrirai ce classeur, il sortira dans le nouveau classeur que nous avons obtenu du siège, il remplacera ces cellules fusionnées vides par les valeurs qui devraient y figurer, et la vie est belle comme ça.
Power Query est couvert dans mon livre, mais il est également couvert dans ce livre par Ken Puls et Miguel Escobar, M est pour (DATA) MONKEY.
D'accord, alors, Elizabeth: Comment puis-je me débarrasser des cellules de fusion verticales? Mon siège ne cesse de m'envoyer ces fichiers tous les jours. Donc, le plan: nous allons enregistrer le classeur dans un endroit fiable avec un nom fiable; créer un classeur de rapport vierge, données, obtenir des données, à partir d'un fichier, à partir d'un classeur, spécifiez la feuille, BAM! Les cellules de fusion ont disparu. Je sélectionne cette colonne et remplis, ferme et charge; puis chaque fois que vous obtenez un nouveau classeur, enregistrez ce nouveau classeur dans un emplacement fiable avec le même nom fiable; J'ouvre le classeur de rapports et actualise, ou nous définissons simplement le classeur de rapports sur Actualiser automatiquement lors de l'ouverture.
Pour télécharger le classeur à partir de la vidéo d'aujourd'hui, utilisez l'URL dans la description YouTube.
Je tiens à remercier Elizabeth d'avoir participé à mon séminaire à Calumet City, je tiens à vous remercier d'être passée. Je vous verrai la prochaine fois pour une autre diffusion sur Internet.
Télécharger le fichier Excel
Pour télécharger le fichier Excel: fill-merged-cells-down.xlsx
Power Query est un outil incroyable - il vous permet de résoudre une variété de
saeed Alimohammadi
