Suppression des lignes et des colonnes vides dans les données

Les lignes et les colonnes vides peuvent être pénibles dans les tableaux dans de nombreux cas. Les fonctions standards de tri, filtrage, résumé, création de tableaux croisés dynamiques, etc. perçoivent les lignes et les colonnes vides comme un saut de tableau, sans récupérer les données situées plus loin derrière. S'il existe de nombreux écarts de ce type, leur suppression manuelle peut être très coûteuse et il ne fonctionnera pas de supprimer tout en même temps "en masse" à l'aide du filtrage, car le filtre "trébuchera" également lors des pauses.

Examinons plusieurs façons de résoudre ce problème.

Méthode 1. Rechercher des cellules vides

Ce n'est peut-être pas le plus pratique, mais le moyen le plus simple mérite certainement d'être mentionné.

Supposons que nous ayons affaire à un tel tableau contenant de nombreuses lignes et colonnes vides à l'intérieur (surlignées pour plus de clarté):

Supposons que nous soyons sûrs que la première colonne de notre tableau (colonne B) contient toujours le nom d'une ville. Ensuite, les cellules vides de cette colonne seront le signe de lignes vides inutiles. Pour les supprimer rapidement, procédez comme suit :

1. Sélectionnez une plage avec des villes (B2:B26)
2. Appuyez sur la touche F5 puis appuyez sur Souligner (Aller à Spécial) ou sélectionnez dans l'onglet Accueil — Rechercher et sélectionner — Sélectionner un groupe de cellules (Accueil - Rechercher et sélectionner - Aller à spécial).
3. Dans la fenêtre qui s'ouvre, sélectionnez l'option Cellules vides (Blancs) et appuyez sur OK – toutes les cellules vides de la première colonne de notre tableau doivent être sélectionnées.
4. Sélectionnez maintenant dans l'onglet Accueil Command Supprimer - Supprimer des lignes de la feuille (Supprimer - Supprimer des lignes) ou appuyez sur le raccourci clavier Ctrl+moins – et notre tâche est résolue.

Bien sûr, vous pouvez vous débarrasser des colonnes vides exactement de la même manière, en utilisant l'en-tête du tableau comme base.

Méthode 2 : rechercher des lignes vides

Comme vous l'avez peut-être déjà compris, la méthode précédente ne fonctionnera que si nos données contiennent nécessairement des lignes et des colonnes entièrement remplies, qui peuvent être accrochées lors de la recherche de cellules vides. Mais que se passe-t-il s'il n'y a pas une telle confiance et que les données peuvent également contenir des cellules vides ?

Jetez un œil au tableau suivant, par exemple, pour un tel cas :

Ici, l'approche sera un peu plus délicate:

1. Entrez dans la cellule A2 la fonction COUNT (COMPTERA), qui calculera le nombre de cellules remplies dans la ligne de droite et copiera cette formule dans tout le tableau :
2. Sélectionnez la cellule A2 et activez le filtre avec la commande Données – Filtre (Données — Filtre) ou raccourci clavier Ctrl+Shift+L.
3. Filtrons les zéros par la colonne calculée, c'est-à-dire toutes les lignes où il n'y a pas de données.
4. Il reste à sélectionner les lignes filtrées et à les supprimer avec la commande Accueil — Supprimer -' Supprimer des lignes de la feuille (Accueil — Supprimer — Supprimer des lignes) ou raccourci clavier Ctrl+moins.
5. Nous désactivons le filtre et obtenons nos données sans lignes vides.

Malheureusement, cette astuce ne peut plus être effectuée avec des colonnes - Excel n'a pas encore appris à filtrer par colonnes.

Méthode 3. Macro pour supprimer toutes les lignes et colonnes vides d'une feuille

Vous pouvez également utiliser une macro simple pour automatiser cette tâche. Appuyez sur le raccourci clavier autre+F11 ou sélectionnez dans l'onglet promoteur - Visual Basic (Développeur — Éditeur Visual Basic). Si des onglets promoteur n'est pas visible, vous pouvez l'activer via Fichier – Options – Configuration du ruban (Fichier — Options — Personnaliser le ruban).

Dans la fenêtre de l'éditeur Visual Basic qui s'ouvre, sélectionnez la commande de menu Insertion – Module et dans le module vide qui s'affiche, copiez-collez les lignes suivantes :

   Sub DeleteEmpty() Dim r As Long, rng As Range 'удаляем пустые строки For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count If Application.CountA(Rows(r)) = 0 Then If rng Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r)) End If Next r If Not rng Is Nothing Then rng.Delete 'удаляем пустые столбцы Set rng = Nothing For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count Si Application.CountA(Columns(r)) = 0 Alors si rng n'est rien Alors Set rng = Columns(r) Sinon Set rng = Union(rng, Columns( r)) End If Next r If Not rng is Nothing Then rng.Delete End Sub  

Fermez l'éditeur et retournez dans Excel. 

Maintenant, appuyez sur la combinaison autre+F8 ou bouton Macros languette promoteur. La fenêtre qui s'ouvre répertorie toutes les macros que vous pouvez actuellement exécuter, y compris la macro que vous venez de créer. SupprimerVide. Sélectionnez-le et cliquez sur le bouton Courir (Cours) – toutes les lignes et colonnes vides de la feuille seront instantanément supprimées.

Méthode 4 : Power Query

Une autre façon de résoudre notre problème et un scénario très courant consiste à supprimer les lignes et les colonnes vides dans Power Query.

Commençons par charger notre table dans l'éditeur de requête Power Query. Vous pouvez le convertir en un "intelligent" dynamique avec le raccourci clavier Ctrl+T ou simplement sélectionner notre plage de données et lui donner un nom (par exemple Données) dans la barre de formule, en convertissant en nommé :

Maintenant, nous utilisons la commande Data – Get data – From table / range (Data – Get Data – From table / range) et chargeons tout dans Power Query :

Ensuite tout est simple :

1. Nous supprimons les lignes vides avec la commande Accueil – Réduire les lignes – Supprimer les lignes – Supprimer les lignes vides (Accueil – Supprimer les lignes – Supprimer les lignes vides).
2. Faites un clic droit sur l'en-tête de la première colonne City et sélectionnez la commande Unpivot Other Columns dans le menu contextuel. Notre table sera, comme on l'appelle techniquement correctement, normalisée – converti en trois colonnes : ville, mois et valeur de l'intersection de la ville et du mois du tableau d'origine. La particularité de cette opération dans Power Query est qu'elle ignore les cellules vides dans les données source, ce dont nous avons besoin :
3. Maintenant, nous effectuons l'opération inverse - nous transformons le tableau résultant en un tableau bidimensionnel afin de lui redonner sa forme d'origine. Sélectionnez la colonne avec les mois et sur l'onglet De La Carrosserie choisir une équipe Colonne pivot (Transformer - Pivot Colonne). Dans la fenêtre qui s'ouvre, sous forme de colonne de valeurs, sélectionnez la dernière (Valeur), et dans les options avancées - l'opération Ne pas agréger (Ne pas agréger):
4. Il reste à télécharger le résultat dans Excel avec la commande Accueil — Fermer et charger — Fermer et charger dans… (Accueil — Fermer&Charger — Fermer&Charger vers…)

• Qu'est-ce qu'une macro, comment ça marche, où copier le texte d'une macro, comment exécuter une macro ?
• Remplir toutes les cellules vides de la liste avec les valeurs des cellules mères
• Suppression de toutes les cellules vides d'une plage donnée
• Suppression de toutes les lignes vides dans une feuille de calcul avec le module complémentaire PLEX