Si certaines cellules, lignes ou colonnes ne sont pas affichées sur la feuille, vous pouvez copier toutes les cellules (ou uniquement les cellules visibles). Par défaut, Excel copie non seulement les cellules visibles, mais également les cellules masquées ou filtrées. Si vous souhaitez copier uniquement les cellules visibles, suivez les étapes ci-dessous. Par exemple, vous pouvez copier uniquement les données récapitulatives d'une feuille structurée.
Suivez les étapes ci-dessous.
Remarque: Lors de la copie, les valeurs sont insérées séquentiellement dans les lignes et les colonnes. Si la zone d'insertion contient des lignes ou des colonnes masquées, vous devrez peut-être les afficher pour voir toutes les données copiées.
Lorsque vous copiez et collez des cellules visibles dans une plage de données contenant des cellules masquées ou auxquelles un filtre est appliqué, vous remarquerez que les cellules masquées sont collées avec les cellules visibles. Malheureusement, vous ne pouvez pas modifier ce paramètre lorsque vous copiez et collez une plage de cellules dans Excel sur Internet, car Coller uniquement les cellules visibles n'est pas disponible.
Cependant, si vous formatez les données sous forme de tableau et appliquez un filtre, vous pouvez copier et coller uniquement les cellules visibles.
Si vous n'avez pas besoin de formater les données sous forme de tableau et que l'application Excel classique est installée, vous pouvez y ouvrir le classeur pour copier et coller les cellules visibles. Pour ce faire, cliquez sur Ouvrir dans Excel et suivez les étapes de l'article, copiez et collez uniquement les cellules visibles.
Information additionnelle
Vous pouvez toujours poser une question à un spécialiste de la communauté Excel Tech, demander de l'aide dans la communauté Answers, et également proposer une nouvelle fonctionnalité ou amélioration sur le site
Listes et gammes (5)
Macros (procédures VBA) (63)
Divers (39)
Bugs et pépins Excel (4)
Comment coller des cellules copiées uniquement dans des cellules visibles / filtrées
En général, le sens de l'article ressort, je pense, du titre. Encore un peu.
Ce n'est un secret pour personne qu'Excel vous permet de sélectionner uniquement les lignes visibles (par exemple, si certains d'entre eux sont masqués ou qu'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 filtré dans la plage copiée dans la plage (ou contenant des lignes cachées) - le résultat du collage ne sera pas exactement ce que vous attendiez. Les données seront insérées même dans des lignes cachées.
Copiez une seule plage de cellules et collez-la uniquement dans les cellules visibles
Pour insérer des données uniquement dans des cellules visibles, vous pouvez utiliser la macro suivante:
| Option Dim explicite rCopyRange As Range "Utilisez cette macro pour copier des données Sub My_Copy () If Selection.Count\u003e 1 Then Set rCopyRange \u003d Selection.SpecialCells (xlVisible) Sinon: Set rCopyRange \u003d ActiveCell End If End End Sub Sub "Cette macro insère des données à partir de la cellule sélectionnée Sub My_Paste () Si rCopyRange n'est rien, quittez Sub If rCopyRange.Areas.Count\u003e 1 Then MsgBox "La plage insérée ne doit pas contenir plus d'une zone!", vbCritical, Plage non valide: Exit Sub Dim rCell As Range, li As Long, le As Long, lCount As Long, iCol As Integer, iCalculation As Integer Application.ScreenUpdating \u003d False iCalculation \u003d Application.Calculation: Application.Calculation \u003d -4135 For iCol \u003d 1 To rCopyRange .Columns.Count li \u003d 0: lCount \u003d 0: le \u003d iCol - 1 pour chaque rCell dans rCopyRange.Columns (iCol) .Cells Do If ActiveCell.Offset (li, le) .EntireColumn.Hidden \u003d False And _ ActiveCell.Offset (li, le) .EntireRow.Hidden \u003d False Then rCell.Copy ActiveCell.Offset (li, le): lCount \u003d lCount + 1 End If li \u003d li + 1 Loop While lCount\u003e \u003d rCell.Row - rCopyRange.Cells (1 ) .Row Next rCell Next iCol Application.ScreenUpdating \u003d True: Application.Calculation \u003d iCalculation End Sub |
Option Explicit Dim rCopyRange As Range "Avec cette macro, copiez les données Sub My_Copy () If Selection.Count\u003e 1 Then Set rCopyRange \u003d Selection.SpecialCells (xlVisible) Else: Set rCopyRange \u003d ActiveCell End If End Sub" Avec cette macro, nous collons les données à partir de la sélection cells Sub My_Paste () If rCopyRange Is Nothing Then Exit Sub If rCopyRange.Areas.Count\u003e 1 Then MsgBox "La plage insérée ne doit pas contenir plus d'une zone!", vbCritical, "Invalid range": Exit Sub Dim rCell As Range, li As Long, le As Long, lCount As Long, iCol As Integer, iCalculation As Integer Application.ScreenUpdating \u003d False iCalculation \u003d Application.Calculation: Application.Calculation \u003d -4135 For iCol \u003d 1 To rCopyRange.Columns.Count li \u003d 0: lCount \u003d 0: le \u003d iCol - 1 pour chaque rCell In rCopyRange.Columns (iCol) .Cells Do If ActiveCell.Offset (li, le) .EntireColumn.Hidden \u003d False And _ ActiveCell.Offset (li, le) .EntireRow.Hidden \u003d False Then rCell.Copy ActiveCell.Offset (li, le): lCount \u003d lCount + 1 End If li \u003d li + 1 Loop While lCount \u003e \u003d rCell.Row - rCopyRange.Cells (1) .Row Next rCell Next iCol Application.ScreenUpdating \u003d True: Application.Calculation \u003d iCalculation End Sub
Pour compléter l'image, ces macros sont mieux attribuées aux touches de raccourci (dans les codes ci-dessous, cela se fait automatiquement lorsque vous ouvrez le livre avec le code). Pour ce faire, entrez simplement les codes ci-dessous dans le module Ce livre (ThisWorkbook) :
Option explicite "Annuler l'affectation de raccourci clavier avant de fermer Private Sub Workbook_BeforeClose (Annuler en tant que booléen) Application.OnKey" ^ q ": Application.OnKey" ^ w "End Sub" Attribuer des raccourcis clavier lors de l'ouverture de 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 collez-le dans celui filtré - Ctrl + w .
Téléchargez l'exemple
(46,5 Kio, 9 622 téléchargements)
Copiez uniquement les cellules visibles et collez uniquement les cellules visibles
À la demande des visiteurs du site, j'ai décidé de finaliser cette procédure. Il est maintenant possible de copier n'importe quelle plage: avec des lignes cachées, des colonnes cachées et de coller les cellules copiées dans n'importe quelle plage: avec des lignes cachées, des colonnes cachées. Il fonctionne exactement comme le précédent: en appuyant sur les touches Ctrl
+
q
copier la plage souhaitée (avec des lignes et colonnes cachées / filtrées ou non cachées)et insérez avec un raccourci clavier Ctrl
+
w
. L'insertion se fait également dans les lignes et colonnes cachées / filtrées ou sans celles cachées.
Si des formules sont présentes dans la plage à copier, seules les valeurs de cellule peuvent être copiées pour éviter le biais de liaison - c'est-à-dire lors de l'insertion de valeurs, non les formules seront insérées, mais le résultat de leur calcul. Ou s'il est nécessaire d'enregistrer les formats des cellules dans lesquelles se produit le collage, seules les valeurs des cellules seront copiées et collées. Pour ce faire, remplacez la ligne dans le code (dans le fichier ci-dessous):
| rCell.Copy rResCell.Offset (lr, lc) |
rCell.Copy rResCell.Offset (lr, lc)
à ce sujet:
| rResCell.Offset (lr, lc) \u003d rCell.Value |
rResCell.Offset (lr, lc) \u003d rCell.Value
Dans le fichier ci-dessous, ces deux lignes sont présentes, il vous suffit de laisser celle qui convient le mieux à vos tâches.
Exemple de téléchargement:
(54,5 Kio, 7 928 téléchargements)
Voir aussi:
[]
("Barre inférieure" :( "textstyle": "statique", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" EASOOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" gauche "," texteffectslidedction " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "facilitOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "droite", "texteffectslidedistance2": 120, "texteffecteasing2": "EASOOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500, textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolue; top: 0px; left: 0px; width: 100%; height: 100% ; couleur d'arrière-plan: # 333333; opacité: 0,6; filtre: a lpha (opacity \u003d 60); "," titlecss ":" display: block; position: relative; police: bold 14px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", sans empattement, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; police: 12px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", sans empattement, Arial; couleur: #fff; margin-top: 8px; "," buttoncss ":" display: block; position: relative; margin-top: 8px; "," texteffectresponsiveize ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ":: "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40))
Pavlov Nikolay
Dans cet article, je voudrais vous présenter les méthodes les plus efficaces de travail dans Microsoft Excel que j'ai collectées au cours des 10 dernières années de travail sur des projets et de formation pour ce merveilleux programme. Il n'y a pas de description de technologies super complexes, mais il existe des techniques pour tous les jours - simples et efficaces, décrites sans "eau" - seulement un "résidu sec". Cela ne vous prendra pas plus d'une ou deux minutes pour maîtriser la plupart de ces exemples, mais ils vous feront économiser beaucoup plus.
Saut rapide vers la feuille souhaitée
Vous arrive-t-il de travailler avec des classeurs Excel composés d'un grand nombre de feuilles? S'il y en a plus d'une douzaine, chaque transition vers la feuille souhaitée suivante devient en soi un petit problème. Une solution simple et élégante à ce problème consiste à cliquer sur les boutons de défilement des étiquettes de feuille dans le coin inférieur gauche de la fenêtre, et non avec le bouton droit de la souris - une table des matières avec une liste complète de toutes les feuilles apparaît et vous pouvez aller à la feuille souhaitée en un seul mouvement:
C'est beaucoup plus rapide que de faire défiler des étiquettes de feuille avec les mêmes boutons à la recherche de celle souhaitée.
Copier sans corruption de formatage
Combien de centaines (milliers?) De fois où j'ai vu cette image, debout derrière mes élèves pendant les formations: l'utilisateur saisit la formule dans la première cellule puis la "étend" à toute la colonne, violant la mise en forme des lignes ci-dessous, car cette méthode ne copie pas seulement la formule, mais aussi le format des cellules. En conséquence, d'autres dommages doivent être corrigés manuellement. Une seconde pour la copie, puis 30 secondes pour réparer un dessin endommagé par la copie.
À partir d'Excel 2002, il existe une solution à ce problème - simple et élégante. Immédiatement après avoir copié (glissé) la formule dans toute la colonne, vous devez utiliser la balise active - une petite icône qui apparaît temporairement dans le coin inférieur droit de la plage. Cliquez dessus pour afficher une liste des options de copie possibles, où vous pouvez sélectionner Copier uniquement les valeurs (Remplir sans formatage). Dans ce cas, les formules sont copiées, mais la mise en forme n'est pas:
Copiez uniquement les cellules visibles
Si vous travaillez dans Microsoft Excel depuis plus d'une semaine, vous devriez déjà avoir rencontré un problème similaire: dans certains cas, lors du copier-coller de cellules, elles sont collées plus qu'elles ne l'ont été à première vue. Cela peut se produire si la plage copiée comprend des lignes / colonnes cachées, des regroupements, des sous-totaux ou un filtrage. Prenons l'exemple des cas suivants:
Dans ce tableau, les sous-totaux sont calculés et les lignes sont regroupées par ville - cela est facile à comprendre par les boutons plus ou moins à gauche du tableau et par les interruptions dans la numérotation des lignes visibles. Si vous sélectionnez, copiez et collez les données de ce tableau de la manière habituelle, nous obtiendrons 24 lignes supplémentaires. Nous voulons copier et coller uniquement les résultats!
Vous pouvez résoudre le problème en sélectionnant soigneusement chaque ligne de totaux tout en maintenant la touche CTRL enfoncée - comme pour mettre en évidence des plages non contiguës. Mais s'il n'y a pas trois ou cinq lignes de ce genre, mais plusieurs centaines ou des milliers? Il existe un autre moyen, plus rapide et plus pratique:
Sélectionnez la plage à copier (dans notre exemple, c'est A1: C29)
Appuyez sur la touche F5 du clavier puis sur le bouton Spécial dans la fenêtre qui s'ouvre.
Une fenêtre apparaîtra permettant à l'utilisateur de sélectionner non pas tout dans une rangée, mais seulement 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 obtenons une copie exactement des cellules visibles et insérons au lieu des 29 inutiles uniquement les 5 lignes dont nous avons besoin.
Si vous soupçonnez que vous devrez effectuer une telle opération souvent, il est logique d'ajouter un bouton à la barre d'outils Microsoft Excel pour appeler rapidement une telle fonction. Cela peut être fait via le menu Outils\u003e Personnaliser, puis allez dans l'onglet Commandes, dans la catégorie Modifier, trouvez le bouton Sélectionner les cellules visibles et faites-le glisser vers la barre d'outils avec la souris:
Transformez les lignes en colonnes et vice versa
Une opération simple, mais si vous ne savez pas comment le faire correctement, vous pouvez passer une demi-journée à faire glisser et déposer manuellement des cellules individuelles:
En fait, tout est simple. Dans cette partie des mathématiques supérieures qui décrit les matrices, il y a le concept de transposition - une action qui permute les lignes et les colonnes d'une matrice entre elles. Dans Microsoft Excel, cela est implémenté en trois mouvements: Copier le tableau
Cliquez avec le bouton droit sur une cellule vide et sélectionnez la commande Collage spécial.
Dans la fenêtre qui s'ouvre, définissez l'indicateur de transposition et cliquez sur OK:
Ajoutez rapidement des données à un graphique
Imaginez 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 déjà ce mois-ci. La manière classique de le résoudre consiste à ouvrir la fenêtre de source de données pour le graphique, où vous pouvez ajouter une nouvelle série de données en entrant son nom et en mettant en surbrillance la plage avec les données nécessaires. Et souvent, cela est plus facile à dire qu'à faire - tout dépend de la complexité du graphique.
Une autre façon - simple, rapide et belle - de sélectionner des cellules avec de nouvelles données, de les copier (CTRL + C) et de les coller (CTRL + V) directement dans le graphique. Excel 2003, contrairement aux versions ultérieures, prend même en charge la possibilité de glisser-déposer une plage sélectionnée de cellules de données et de la déposer directement dans le graphique avec la souris!
Si vous souhaitez contrôler toutes les nuances et subtilités, vous pouvez utiliser non pas un collage régulier, mais un collage spécial en choisissant Édition\u003e Collage spécial dans le menu. Dans ce cas, Microsoft Excel affiche une boîte de dialogue qui vous permet de configurer où et comment les nouvelles données seront ajoutées:
De la même manière, vous pouvez facilement créer un graphique en utilisant les données de différents tableaux de différentes feuilles. La tâche classique prendra beaucoup plus de temps et d'efforts pour terminer la même tâche.
Remplissage des cellules vides
Après avoir téléchargé des rapports de certains programmes au format Excel ou lors de la création de tableaux croisés dynamiques, les utilisateurs obtiennent souvent des tableaux avec des cellules vides dans certaines colonnes. Ces omissions ne vous permettent pas d'appliquer des outils familiers et pratiques comme le filtrage automatique et le tri aux tableaux. Naturellement, il est nécessaire de combler les vides avec les valeurs des cellules parentes:
Bien sûr, avec une petite quantité de données, cela peut facilement être fait en copiant simplement - en étirant manuellement chaque cellule d'en-tête dans la colonne A jusqu'à des cellules vides. Mais que faire si dans le tableau il y a 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 seule formule:
Sélectionnez toutes les cellules dans la colonne vide (c'est-à-dire la plage A1: A12 dans notre cas)
Pour sélectionner uniquement les cellules vides dans la sélection, appuyez sur la touche F5 et sélectionnez le bouton dans la fenêtre de transition qui s'ouvre. Vous verrez une fenêtre vous permettant de choisir quelles cellules nous voulons sélectionner:
Réglez le commutateur sur Vide et cliquez sur OK. Maintenant, seules les cellules vides doivent rester dans la sélection:
Sans modifier la sélection, c'est-à-dire Sans toucher à la souris, nous entrons la formule dans la première cellule sélectionnée (A2). Appuyez sur le signe égal du clavier, puis sur la flèche vers le haut. Nous obtenons une formule qui fait référence à la cellule précédente:
Pour entrer la formule créée dans toutes les cellules vides sélectionnées à la fois, appuyez non pas sur la touche ENTRÉE, mais sur la combinaison CTRL + ENTRÉE. La formule remplira toutes les cellules vides:
Maintenant, il ne reste plus qu'à remplacer les formules par des valeurs pour fixer les résultats. Sélectionnez la plage A1: A12, copiez-la et collez-les dans les cellules à l'aide d'une pâte spéciale.
Liste déroulante dans une cellule
Une technique qui, sans exagération, devrait être connue de tous ceux qui travaillent dans Excel. Son utilisation peut améliorer presque n'importe quelle table, quel que soit son objectif. À toutes les formations, j'essaie de le montrer à mes élèves le premier jour.
L'idée est très simple - dans tous les cas, lorsque vous devez saisir des données d'un ensemble, au lieu de saisir manuellement la cellule à partir du clavier, sélectionnez la valeur souhaitée avec la souris dans la liste déroulante:
Sélection de marchandises dans la liste de prix, nom du client dans la base de clients, nom de l'employé dans la liste du personnel, 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 avez Excel 2003 ou une version antérieure, sélectionnez Données\u003e Validation dans le menu. Si vous avez Excel 2007/2010, accédez à 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, spécifiez les valeurs qui doivent figurer dans la liste. Il existe des options possibles:
Entrez des options de texte dans ce champ séparées par des points-virgules
Si la plage de cellules avec les valeurs d'origine est sur la feuille actuelle - sélectionnez-la simplement avec la souris.
S'il se trouve sur une autre feuille de ce livre, il faudra lui donner un nom à l'avance (sélectionnez les cellules, appuyez sur CTRL + F3, entrez un nom de plage sans espaces), puis écrivez ce nom dans le champ
Les données ont été sélectionnées dans Excel à l'aide d'un filtre ou d'un tri. Maintenant, ils doivent être imprimés ou transférés à un autre endroit. Copier vers Excel configuré pour que les cellules cachées soient également copiées.
Considérez deux façons à
comment copier des lignes filtrées dans Excel.
La première façon.
Il existe une excellente fonctionnalité dans Excel - coller la fonction spéciale dans Excel.
Nous avons donc une table.
Pour installer un filtre, consultez l'article "Filtrer dans Excel ".
Nous supprimons le filtre de la liste de tous les Ivanov. Cela s'est avéré comme ça.
Sélectionnez le tableau, cliquez sur "Copier" dans le menu contextuel. Faites un clic gauche sur la cellule A9, sélectionnez "valeurs" dans le menu contextuel.
Cliquez OK. Voila. Non seulement la valeur des lignes visibles a été copiée, mais aussi le format des cellules.
Il y a une mise en garde - n'insérez pas de 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 collons les lignes où se trouve le filtre, les données filtrées seront insérées dans les lignes cachées par le filtre. En général, il se révélera de la bouillie. 
La deuxième façon.
Le tableau est le même. Sélectionnez une table avec des données filtrées. Sur l'onglet «Accueil», dans la section «Édition», appuyez sur le bouton rechercher et sélectionner des fonctionnalités dans Excel. Cliquez ensuite sur le bouton "Aller". 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 OK. 
Maintenant sur la même table sélectionnée, faites un clic droit sur le menu contextuel. Cliquez sur la fonction "Copier". Dans un nouvel emplacement (dans notre exemple, il s'agit de la cellule A15), cliquez sur "Coller". C’est tout. Cela s'est avéré comme ça.
Comment, sans copier, tout de suite imprimer les données du filtre dans Excel, consultez l'article "Mise en page de feuille Excel" Mise en page ""
