Comment insérer des cellules filtrées. Collez dans les lignes visibles dans Excel. Conversion de lignes en colonnes et inversement

Pavlov Nikolaï

Dans cet article, je voudrais vous présenter les techniques les plus efficaces pour travailler dans Microsoft Excel, que j'ai collecté au cours des 10 dernières années de travail sur des projets et de formation sur ce merveilleux programme. Il n'y a pas ici de description de technologies super complexes, mais il existe des techniques pour tous les jours - simples et efficaces, décrites sans « eau » - uniquement « résidus secs ». La plupart de ces exemples ne vous prendront pas plus d'une ou deux minutes à maîtriser, mais ils vous aideront à économiser beaucoup plus.

Accédez rapidement à la feuille souhaitée

Est-ce que vous travaillez avec Classeurs Excel composé d'un grand nombre de feuilles ? S'il y en a plus d'une douzaine, alors chaque transition vers la feuille requise suivante devient un petit problème en soi. Une solution simple et élégante à ce problème consiste à cliquer dans le coin inférieur gauche de la fenêtre sur les boutons permettant de faire défiler les onglets de la feuille non pas avec le bouton gauche, mais avec le bouton droit de la souris - une table des matières du livre apparaîtra avec liste complète toutes les feuilles et vous pouvez accéder à la feuille souhaitée d'un seul mouvement :

C'est beaucoup plus rapide que de faire défiler les onglets d'une feuille en utilisant les mêmes boutons à la recherche de ce dont vous avez besoin.

Copiez sans endommager le formatage

Combien de centaines (de milliers ?) de fois ai-je vu cette image, debout derrière mes élèves lors des formations : l'utilisateur saisit une formule dans la première cellule puis « l'étire » sur toute la colonne, violant le formatage des lignes du dessous, puisque cette méthode copie non seulement la formule, mais aussi le format de la cellule. En conséquence, vous devez ensuite corriger manuellement les dégâts. Une seconde pour copier puis 30 pour réparer un dessin endommagé par la copie.

À partir d’Excel 2002, il existe une solution simple et élégante à ce problème. Immédiatement après avoir copié (faite glisser) la formule sur toute la colonne, vous devez utiliser une balise intelligente - une petite icône qui apparaît temporairement dans le coin inférieur droit de la plage. En cliquant dessus, une liste s'affichera options possibles copie, où vous pouvez sélectionner Remplir sans formatage. Dans ce cas, les formules sont copiées, mais la mise en forme ne l'est pas :

Copier uniquement les cellules visibles

Si vous travaillez dans Microsoft Excel depuis plus d'une semaine, vous devez déjà avoir rencontré un problème similaire : dans certains cas, lors du copier-coller de cellules, plus de cellules sont insérées qu'il n'y en avait, à première vue, copiées. Cela peut se produire si la plage copiée comprenait des lignes/colonnes masquées, des regroupements, des sous-totaux ou un filtrage. Prenons comme exemple un de ces cas :

Dans ce tableau, les sous-totaux sont calculés et les lignes sont regroupées par ville - ceci est facile à comprendre grâce aux boutons plus-moins à gauche du tableau et aux ruptures dans la numérotation des lignes visibles. Si nous sélectionnons, copions et collons les données de ce tableau de la manière habituelle, nous nous retrouverons avec 24 lignes supplémentaires. Nous voulons seulement copier et coller les résultats !

Vous pouvez résoudre le problème en sélectionnant minutieusement chaque ligne des totaux tout en maintenant la touche CTRL enfoncée - comme vous le feriez pour sélectionner des plages non adjacentes. Mais que se passe-t-il s’il n’existe pas trois ou cinq lignes de ce type, mais plusieurs centaines ou milliers ? Il existe un autre moyen, plus rapide et plus pratique :

Sélectionnez la plage à copier (dans notre exemple, il s'agit de A1:C29)

Appuyez sur la touche F5 de votre clavier puis sur le bouton Sélectionner dans la fenêtre qui s'ouvre.
Une fenêtre apparaîtra permettant à l'utilisateur de sélectionner non pas tout dans une ligne, mais uniquement les cellules nécessaires :

Dans cette fenêtre, sélectionnez l'option Cellules visibles uniquement et cliquez sur OK.

La sélection résultante peut maintenant être copiée et collée en toute sécurité. En conséquence, nous obtiendrons une copie exacte de cellules visibles et insérez à la place des 29 inutiles uniquement les 5 lignes dont nous avons besoin.

Si vous pensez que vous devrez souvent effectuer une telle opération, il est alors logique d'ajouter un bouton à la barre d'outils Microsoft Excel pour appeler rapidement une telle fonction. Cela peut se faire via le menu Outils > Personnaliser, puis allez dans l'onglet Commandes, dans la catégorie Edition, recherchez le bouton Sélectionner les cellules visibles et faites-le glisser vers la barre d'outils avec la souris :

Conversion de lignes en colonnes et inversement

Une opération simple, mais si vous ne savez pas comment la faire correctement, vous pouvez passer une demi-journée à faire glisser manuellement des cellules individuelles :

C'est en fait simple. Dans la partie des mathématiques supérieures qui décrit les matrices, il existe le concept de transposition - une action qui échange les lignes et les colonnes d'une matrice entre elles. Dans Microsoft Excel, cela se déroule en trois étapes : Copier le tableau

Cliquez avec le bouton droit sur une cellule vide et sélectionnez Collage spécial.

Dans la fenêtre qui s'ouvre, cochez le drapeau Transposer et cliquez sur OK :

Ajoutez rapidement des données à un graphique

Imaginons une situation simple : vous avez un rapport du mois dernier avec un diagramme visuel. La tâche consiste à ajouter de nouvelles données numériques au graphique pour ce mois. La manière classique de résoudre ce problème consiste à ouvrir la fenêtre de la source de données du graphique, dans laquelle vous ajoutez une nouvelle série de données en saisissant son nom et en mettant en surbrillance la plage contenant les données souhaitées. De plus, c'est souvent plus facile à dire qu'à faire : tout dépend de la complexité du diagramme.

Une autre méthode - simple, rapide et esthétique - consiste à sélectionner les cellules contenant de nouvelles données, à les copier (CTRL+C) et à les coller (CTRL+V) directement dans le graphique. Excel 2003, contrairement aux versions ultérieures, prend même en charge la possibilité de faire glisser une plage sélectionnée de cellules de données et de la déposer directement dans le graphique à l'aide de la souris !

Si vous souhaitez contrôler toutes les nuances et subtilités, vous pouvez utiliser non pas un collage ordinaire, mais un collage spécial en sélectionnant Édition > Collage spécial dans le menu. Dans ce cas, Microsoft Excel affichera une boîte de dialogue qui vous permettra de configurer où et comment exactement les nouvelles données seront ajoutées :

De même, vous pouvez facilement créer un graphique en utilisant les données de différents tableaux de différentes feuilles. Effectuer la même tâche de manière classique demandera beaucoup plus de temps et d’efforts.

Remplir les cellules vides

Après avoir téléchargé les rapports de certains programmes vers Format Excel ou lors de la création de tableaux croisés dynamiques, les utilisateurs se retrouvent souvent avec des tableaux avec des cellules vides dans certaines colonnes. Ces omissions ne vous permettent pas d'appliquer des outils familiers et pratiques tels que le filtre automatique et le tri aux tableaux. Naturellement, il est nécessaire de combler les vides avec des valeurs provenant de cellules de niveau supérieur :

Bien sûr, avec une petite quantité de données, cela peut facilement être fait par simple copie - en faisant glisser manuellement chaque cellule d'en-tête de la colonne A vers les cellules vides. Que se passe-t-il si le tableau comporte plusieurs centaines ou milliers de lignes et plusieurs dizaines de villes ?

Il existe un moyen de résoudre ce problème rapidement et magnifiquement en utilisant une formule :

Sélectionnez toutes les cellules d'une colonne avec des espaces vides (c'est-à-dire la plage A1: A12 dans notre cas)

Pour conserver uniquement les cellules vides dans la sélection, appuyez sur la touche F5 et dans la fenêtre de navigation qui s'ouvre, appuyez sur le bouton Sélectionner. Vous verrez une fenêtre qui vous permet de sélectionner les cellules que nous voulons sélectionner :

Réglez le commutateur sur Vide et cliquez sur OK. Désormais, seules les cellules vides doivent rester dans la sélection :

Sans changer la sélection, c'est-à-dire Sans toucher la souris, saisissez la formule dans la première cellule sélectionnée (A2). Appuyez sur le signe égal de votre clavier puis sur la flèche vers le haut. On obtient une formule qui fait référence à la cellule précédente :

Pour saisir la formule créée dans toutes les cellules vides sélectionnées à la fois, n'appuyez pas sur la touche ENTRÉE, mais sur la combinaison CTRL + ENTRÉE. La formule remplira toutes les cellules vides :

Il ne reste plus qu'à remplacer les formules par des valeurs pour enregistrer les résultats. Sélectionnez la plage A1:A12, copiez-la et collez leurs valeurs dans les cellules à l'aide de Collage spécial.

Liste déroulante dans une cellule

Une technique que, sans exagération, tous ceux qui travaillent dans Excel devraient connaître. Son utilisation peut améliorer presque n'importe quelle table, quel que soit son objectif. Lors de toutes les formations, j'essaie de le montrer à mes élèves dès le premier jour.

L'idée est très simple : dans tous les cas où vous devez saisir des données de n'importe quel ensemble, au lieu de saisir manuellement une cellule à l'aide du clavier, sélectionnez la valeur souhaitée avec la souris dans la liste déroulante :

Sélection d'un produit dans la liste de prix, du nom du client dans la base de données clients, du nom complet de l'employé dans le tableau des effectifs, etc. Il existe de nombreuses options pour utiliser cette fonction.

Pour créer une liste déroulante dans une cellule :

Sélectionnez les cellules dans lesquelles vous souhaitez créer une liste déroulante.

Si vous disposez d'Excel 2003 ou d'une version antérieure, sélectionnez Données>Validation dans le menu. Si vous disposez d'Excel 2007/2010, allez dans l'onglet Données et cliquez sur le bouton Validation des données.

Dans la fenêtre qui s'ouvre, sélectionnez l'option Liste dans la liste déroulante.

Dans le champ Source, vous devez spécifier les valeurs qui doivent figurer dans la liste. Voici les options possibles :

Saisissez les options de texte dans ce champ séparées par des points-virgules

Si la plage de cellules avec les valeurs d'origine se trouve sur la feuille actuelle, il vous suffit de la sélectionner avec la souris.

S'il se trouve sur une autre feuille de ce classeur, alors vous devrez lui donner un nom au préalable (sélectionner des cellules, appuyer sur CTRL+F3, saisir le nom de la plage sans espaces), puis écrire ce nom dans le champ

Coller uniquement dans les lignes visibles dansExceller les nombres, les formules, le texte peuvent être réalisés de plusieurs manières. Lorsque vous devez insérer des nombres, des formules, du texte dans toutes les lignes du tableau, vous pouvez utiliser un filtre. Comment installer un filtre et comment filtrer dans Excel, voir l'article « Filtrer dans Excel ». Mais pour insérer des données uniquement dans des cellules visibles, vous avez besoin de vos propres méthodes, surtout s'il y a de nombreuses lignes.
La première façon est ordinaire .
Prenons une table comme celle-ci. Le tableau sera le même pour tous les exemples.
Utilisons un filtre pour supprimer tous les chiffres 2 du tableau. Dans les cellules visibles restantes, nous mettons le nombre 600. Dans la cellule B2, nous mettons le nombre 600, puis le copions dans la colonne (tirez le coin inférieur droit de la cellule B2). Les valeurs ont été copiées uniquement dans les cellules visibles. Vous pouvez également insérer des formules de la même manière. Nous écrivons la formule suivante dans la cellule C2. =A2*10
Cela s'est passé comme ça.
Annulons le filtre. Le résultat est un tableau comme celui-ci.
La formule et les nombres ont été insérés uniquement dans les lignes filtrées.
Deuxième façon.
Nous filtrerons également les données. Dans la première cellule, nous écrivons un nombre, une formule, un texte, etc. Maintenant, s'il y a des milliers de lignes, sélectionnez les cellules comme ceci : appuyez sur les touches « Ctrl » + « Maj » + la flèche vers le bas (ou la touche haut, selon l'endroit où nous voulons sélectionner les cellules - en dessous ou au-dessus la cellule dans laquelle le numéro a été écrit) .
Maintenant, ou appuyez sur la combinaison de touches « Ctrl » + G, ou sur la touche F5. La boîte de dialogue Transition apparaîtra. Cliquez sur le bouton « Sélectionner... ». Et, dans la nouvelle boîte de dialogue « Sélectionner un groupe de cellules », cochez la case à côté des mots « Uniquement les cellules visibles ».Cliquez sur OK". Insérez ensuite comme d'habitude.

Une autre façon d'afficher la boîte de dialogue Sélectionner un groupe de cellules.Sur l'onglet « Accueil », dans la section « Édition », cliquez sur le bouton « Rechercher et sélectionner ». Dans la liste qui apparaît, cliquez sur la fonction « Sélectionner un groupe de cellules ».

À remplir les cellules visibles dans celles sélectionnées Colonnes Excel , appuyez sur la combinaison de touches « Ctrl » + D. Et toutes les colonnes sélectionnées seront remplies de données ou d'une formule, comme dans la première cellule. Dans notre exemple, nous avons écrit le nombre 800 dans la cellule D2, colonne D.

Troisième voie.
Dans une nouvelle colonne (dans notre exemple, la colonne E), sélectionnez les cellules. Appuyez sur la touche F5. La boîte de dialogue Transition apparaîtra. Cliquez sur le bouton « Sélectionner... ». Et, dans la nouvelle boîte de dialogue « Sélectionner un groupe de cellules », cochez la case à côté des mots « Cellules visibles uniquement ». Cliquez sur OK". Maintenant, sans annuler la sélection, dans la première cellule de la colonne (la nôtre est E2), saisissez une formule, un nombre, etc. Appuyez sur la combinaison de touches « Ctrl » + « Entrée ».

La sélection des données dans Excel a été effectuée à l'aide d'un filtre ou d'un tri. Ils doivent maintenant être imprimés ou déplacés vers un autre emplacement. Copier vers Excel configuré pour que les cellules masquées soient également copiées.
Considérons deux manières, À Comment copier des lignes filtrées dans Excel.
Première façon.
Il existe une fonction intéressante dans Excel - Coller la fonction spéciale dans Excel.
Nous avons donc une table.
Comment installer un filtre, voir l'article " Filtrer dans Excel".
Nous utilisons un filtre pour supprimer tous les Ivanov de la liste. Cela s'est passé comme ça.
Sélectionnez le tableau et cliquez sur « Copier » dans le menu contextuel. Cliquez avec le bouton gauche sur la cellule A9 et sélectionnez « valeurs » dans le menu contextuel.
Cliquez sur OK". Voilà. Non seulement la valeur des lignes visibles a été copiée, mais également le format des cellules.
Il y a une nuance- n'insérez pas les données filtrées dans les lignes où se trouve le filtre. Par exemple, dans notre exemple - pas dans les lignes 1 à 7, mais en dessous ou sur une autre feuille, etc. Si nous insérons dans les lignes où se trouve le filtre, alors les données filtrées seront également insérées dans les lignes masquées par le filtre. En général, cela s'avérera être un désastre. Deuxième façon.
Le tableau est le même. Sélectionnez la table avec les données filtrées. Sur l'onglet « Accueil », cliquez dans la section « Modification » Fonctions Rechercher et Mettre en surbrillance dans Excel. Ensuite, cliquez sur le bouton « Go ». Dans la boîte de dialogue qui apparaît, cliquez sur le bouton « Sélectionner... ». Dans la fenêtre « Sélectionner un groupe de cellules », cochez la case « Uniquement les cellules visibles ». Cliquez sur OK". Maintenant, sur la même table sélectionnée avec le bouton droit de la souris, nous appelons menu contextuel. Cliquez sur la fonction « Copier ». Dans un nouvel emplacement (dans notre exemple, il s'agit de la cellule A15), cliquez sur « Insérer ». Tous. Cela s'est passé comme ça.
Comment, sans copier, immédiatement imprimer les données du filtre dans Excel, voir l'article « Favoris feuille de calcul Excel"Mise en page""

Formatage conditionnel (5)
Listes et plages (5)
Macros (procédures VBA) (63)
Divers (39)
Bugs et problèmes Excel (4)

Comment coller des cellules copiées uniquement dans des cellules visibles/filtrées

En général, le sens de l'article, je pense, ressort déjà clairement du titre. Je vais juste le développer un peu.

Ce n'est un secret pour personne, Excel vous permet de sélectionner uniquement les lignes visibles (par exemple, si certains d'entre eux sont masqués ou si un filtre est appliqué).

Ainsi, si vous copiez uniquement les cellules visibles de cette manière, elles seront copiées comme prévu. Mais lorsque vous essayez de coller quelque chose copié dans une plage filtrée (ou contenant des lignes masquées), le résultat du collage ne sera pas exactement celui attendu. Les données seront insérées même dans les lignes masquées.

Copiez une seule plage de cellules et collez-la uniquement dans celles visibles
Pour insérer des données uniquement dans les cellules visibles, vous pouvez utiliser la macro suivante :

Option Explicit Dim rCopyRange As Range "Avec cette macro, nous copions les données Sub My_Copy() Si Selection.Count > 1 Then Set rCopyRange = Selection.SpecialCells(xlVisible) Else : Set rCopyRange = ActiveCell End If End Sub "Avec cette macro, nous insérons des données à partir de la cellule sélectionnée Sub My_Paste() Si rCopyRange n'est rien, quittez Sub Si rCopyRange.Areas.Count > 1, puis MsgBox "La plage collée ne doit pas contenir plus d'une région !",vbCritique, "Plage invalide": Exit Sub Dim rCell As Range, li As Long , le As Long , lCount As Long , iCol As Integer , iCalculation As Integer Application.ScreenUpdating = False iCalculation = Application.Calculation: Application.Calculation = -4135 Pour iCol = 1 To rCopyRange .Columns.Count li = 0 : lCount = 0 : le = iCol - 1 pour chaque rCell dans rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False et _ ActiveCell.Offset (li, le).EntireRow.Hidden = False Then rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 End If li = li + 1 Boucle While lCount >= rCell.Row - rCopyRange.Cells(1 ).Row Next rCell Next iCol Application.ScreenUpdating = True : Application.Calculation = iCalculation End Sub

Option Explicit Dim rCopyRange As Range "Utilisez cette macro pour copier les données Sub My_Copy() If Selection.Count > 1 Then Set rCopyRange = Selection.SpecialCells(xlVisible) Else: Set rCopyRange = ActiveCell End If End Sub "Utilisez cette macro pour coller les données à partir des cellules sélectionnées Sub My_Paste() Si rCopyRange Is Nothing Then Exit Sub If rCopyRange.Areas.Count > 1 Then MsgBox "La plage collée ne doit pas contenir plus d'une zone !", vbCritical, "Plage invalide" : Quitter Sub Dim rCell As Range, li As Long, le As Long, lCount As Long, iCol As Integer, iCalculation As Integer Application.ScreenUpdating = False iCalculation = Application.Calculation : Application.Calculation = -4135 Pour iCol = 1 To rCopyRange.Columns .Count li = 0 : lCount = 0 : le = iCol - 1 pour chaque rCell dans rCopyRange.Columns(iCol).Cells Do If ActiveCell.Offset(li, le).EntireColumn.Hidden = False Et _ ActiveCell.Offset(li , le).EntireRow.Hidden = False Then rCell.Copy ActiveCell.Offset(li, le): lCount = lCount + 1 End If li = li + 1 Boucle While lCount >= rCell.Row - rCopyRange.Cells(1). Ligne suivante rCell suivante iCol Application.ScreenUpdating = True : Application.Calculation = iCalculation End Sub

Pour compléter le tableau, il est préférable d'attribuer ces macros à des raccourcis clavier (dans les codes ci-dessous, cela se fait automatiquement lors de l'ouverture d'un livre avec le code). Pour cela, il vous suffit de copier les codes ci-dessous dans le module Ce livre (Ce cahier d'exercices) :

Option Explicite "Annuler l'attribution des raccourcis clavier avant de fermer le classeur Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey "^q": Application.OnKey "^w" End Sub "Attribuer des raccourcis clavier lors de l'ouverture du classeur Private Sub Workbook_Open() Application .OnKey "^q", "My_Copy" : Application.OnKey "^w", "My_Paste" End Sub

Vous pouvez maintenant copier la plage souhaitée en appuyant sur les touches Ctrl + q , et insérez-le dans celui filtré - Ctrl + w .

Exemple de téléchargement

(46,5 Ko, 9 622 téléchargements)

Copiez uniquement les cellules visibles et collez-les uniquement dans les cellules visibles
A la demande des visiteurs du site, j'ai décidé d'améliorer cette procédure. Il est désormais possible de copier n'importe quelle plage : avec des lignes masquées, des colonnes masquées, et de coller également des cellules copiées dans n'importe quelle plage : avec des lignes masquées, des colonnes masquées. Son fonctionnement est exactement le même que le précédent : en appuyant sur les touches Ctrl + q copier la plage souhaitée (avec lignes et colonnes masquées/filtrées ou non masquées), et collez-le avec un raccourci clavier Ctrl + w . L'insertion est également effectuée dans des lignes et des colonnes masquées/filtrées ou sans lignes masquées.
Si la plage copiée contient des formules, pour éviter le déplacement de référence, vous pouvez copier uniquement les valeurs de cellule - c'est-à-dire Lors de l'insertion de valeurs, ce ne sont pas des formules qui seront insérées, mais le résultat de leur calcul. Ou s'il est nécessaire de conserver les formats des cellules dans lesquelles l'insertion a lieu, seules les valeurs des cellules seront copiées et collées. Pour cela, vous devez remplacer la ligne dans le code (dans le fichier ci-dessous) :

rCell.Copier rResCell.Offset(lr, lc)

rCell.Copier rResCell.Offset(lr, lc)

pour ça:

rResCell.Offset(lr, lc) = rCell.Value

rResCell.Offset(lr, lc) = rCell.Value

Ces deux lignes sont présentes dans le fichier ci-dessous, il vous suffit de laisser celle qui convient le mieux à vos tâches.

Exemple de téléchargement :

(54,5 Ko, 7 928 téléchargements)

Regarde aussi:
[]

L'article a-t-il aidé ? Partagez le lien avec vos amis ! Cours vidéo

("Barre inférieure": ("textstyle": "statique", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"droite","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"affichage:bloc; remplissage:12px; text-align:gauche;","textbgcss":"affichage:bloc; position:absolue; haut:0px; gauche:0px; largeur:100%; hauteur:100% ; couleur d'arrière-plan :#333333 ; opacité :0,6 ; filtre :alpha(opacité=60);","titlecss":"display:block; position : relative ; police: gras 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"affichage:bloc; position : relative ; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; couleur :#fff ; margin-top:8px;","buttoncss":"display:block; position : relative ; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"affichage:aucun !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))