Pavlov Nicholas.

Dans cet article, j'aimerais vous présenter les techniques les plus efficaces de travail chez Microsoft Excel, collectées par moi au cours des 10 dernières années de travail sur des projets et une formation sur ce programme merveilleux. Il n'y a pas de description des technologies superstrates, mais il existe des techniques pour chaque jour - simple et efficace, décrite sans "eau" - seul le "résidu sec". Vous n'aurez pas plus d'une ou deux minutes pour développer la plupart de ces exemples, mais ils vous sauveront beaucoup plus.

Transition rapide vers la feuille souhaitée

Travaillez-vous avec des livres Excel composés d'un grand nombre de feuilles? S'il y a plus d'une douzaine, chaque transition vers le prochain besoin de peur peut-elle devenir un petit problème. Une solution simple et élégante d'une telle tâche est de cliquer dans le coin inférieur gauche de la fenêtre sur les boutons de défilement des feuilles de feuilles à gauche et le bouton droit de la souris - la table des matières apparaîtra avec la liste complète de tous. Les feuilles et sur la feuille désirée peuvent être transférées à un mouvement:

Il est beaucoup plus rapide que le défilement des feuilles de feuilles par ces mêmes boutons à la recherche de la personne souhaitée.

Copier sans formater des dommages causés

Combien de centaines de centaines (des milliers de personnes?) Une fois que j'ai vu cette image, debout derrière mes auditeurs lors de formations: l'utilisateur entre dans la formule de la première cellule puis et "s'étend" sur toute la colonne, perturbant la mise en forme des lignes suivantes, car ceci La méthode copie non seulement la formule, mais également le format de la cellule. En conséquence, alors vous devez corriger manuellement les dégâts. Deuxièmement, la copie puis 30 - pour réparer la conception endommagée par la copie.

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

Copier uniquement des cellules visibles

Si vous travaillez à Microsoft Excel Plus d'une semaine, vous auriez dû déjà été confrontés à un problème similaire: dans certains cas, lors de la copie des cellules, elles sont insérées plus que celles-ci, à première vue, copiées. Cela peut se produire si la gamme de copies comprenait des chaînes / colonnes cachées, des groupes, des résultats intermédiaires ou du filtrage. Considérez comme exemple l'un de ces cas:

Les résultats intermédiaires sont comptés dans ce tableau et un regroupement de chaînes dans les villes est facile - il est facile de comprendre les boutons "plus moins" à gauche de la table et sur les lacunes de la numérotation des lignes visibles. Si vous sélectionnez, copiez et collez des données de ce tableau de la manière habituelle, nous recevrons 24 lignes inutiles. Nous voulons aussi copier et insérer uniquement les résultats!

Vous pouvez résoudre le problème, soulignant minutieusement chaque ligne du résultat et maintenir la clé CTRL - comme pour la sélection des gammes non négatives. Mais s'il n'y a pas trois cinq lignes de ce type, mais quelques centaines ou des milliers de personnes? Il y a une autre manière, plus rapide et commode:

Mettre en surbrillance la plage copiée (dans notre exemple est A1: C29)

Appuyez sur la touche du clavier F5, puis sélectionnez le bouton (spécial) de la fenêtre qui s'ouvre.
Une fenêtre apparaîtra, permettant à l'utilisateur de n'allouer pas tout dans une rangée, mais seulement les cellules nécessaires:

Dans cette fenêtre, sélectionnez uniquement des cellules visibles uniquement (cellules visibles uniquement) et cliquez sur OK.

La sélection obtenue peut désormais être copiée et insérée en toute sécurité. En conséquence, nous recevrons une copie des cellules visibles et d'insérer au lieu d'une 29 inutile de 29 lignes dont vous avez besoin.

S'il y a une suspicion qu'une telle opération que vous devez faire 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 Configuration (Outils\u003e Personnaliser), puis Allez dans l'onglet Commandes (Commandes), dans la catégorie Modifier (modifier) \u200b\u200bRecherchez le bouton Sélectionner des cellules visibles et transférez-le dans la barre d'outils:

Transformation des rangées dans les colonnes et le dos

Opération simple, mais si vous ne savez pas comment faire raison - vous pouvez passer une demi-journée à faire glisser manuellement les cellules individuelles:

En fait, tout est simple. Dans cette partie des mathématiques les plus élevées, décrivant les matrices, c'est le concept de transposition - actions qui change les chaînes et les colonnes de la matrice dans certains endroits les uns avec les autres. Dans Microsoft Excel, il est mis en œuvre en trois mouvements: copier la table

Cliquez avec le bouton droit sur une cellule vide et choisissez une commande d'insertion spéciale (pâte spéciale)

Dans la fenêtre qui s'ouvre, réglez le drapeau pour transposer (transpose) et cliquez sur OK:

Ajoutez rapidement des données au graphique

Imaginez une situation simple: vous avez un rapport pour le mois dernier avec un diagramme visuel. Tâche - Ajoutez de nouvelles données numériques au tableau pour ce mois. Le trajet classique consiste à ouvrir la fenêtre source de données pour le diagramme où vous ajoutez une nouvelle série de données en entrant son nom et en mettant en surbrillance la plage avec les données souhaitées. De plus, il est souvent plus facile de dire que de faire - tout dépend de la complexité du graphique.

Une autre solution est simple, rapide et belle - de sélectionner des cellules avec de nouvelles données, copiez-les (CTRL + C) et Coller (Ctrl + V) directement dans le graphique. Excel 2003, contrairement aux versions ultérieures, même la possibilité de faire glisser la gamme dédiée de cellules avec des données et de le lancer directement sur le graphique avec la souris!

Si vous souhaitez contrôler toutes les nuances et toutes les subtilités, vous pouvez utiliser non une insertion ordinaire et une insertion spéciale en sélectionnant sur l'édition\u003e Coller Spécial (Edition\u003e Coller Spécial). Dans ce cas, Microsoft Excel affichera une boîte de dialogue qui vous permettra de configurer où et comment les nouvelles données seront ajoutées:

De même, vous pouvez facilement créer un diagramme à l'aide de données de différentes tables à partir de différentes feuilles. Pour effectuer la même tâche, cela prendra beaucoup plus de temps et de force.

Remplir des cellules vides

Après le déchargement des rapports de certains programmes au format Excel ou lors de la création de tables consolidées, les utilisateurs reçoivent souvent des tables avec des cellules vides dans certaines colonnes. Ces sauts ne nous permettent pas de nous appliquer aux tables d'outils familiers et pratiques tels que Autofilter et tri. Naturellement, il est nécessaire de remplir les valeurs du vide des cellules supérieures:

Bien entendu, avec une petite quantité de données, il peut être facilement effectué par une copie simple - étirement manuellement chaque cellule de titre dans la colonne et vers le bas pour vider les cellules. Et s'il y a plusieurs centaines ou des milliers de lignes et plusieurs dizaines de villes de la table?

Il existe un moyen de résoudre cette tâche rapidement et belle avec l'aide d'une formule:

Mettez en surbrillance toutes les cellules dans une colonne avec des vides (c'est-à-dire la plage A1: A12 dans notre cas)

Pour sélectionner uniquement les cellules vides, appuyez sur la touche F5 et dans la fenêtre de transition qui s'ouvre, le bouton Allocate. Voir une fenêtre qui vous permet de choisir - quelles cellules nous souhaitons allouer:

Réglez le commutateur sur la position vide (vide) et cliquez sur OK. Maintenant, seules les cellules vides doivent rester dans la sélection:

Sans changer d'allocations, c'est-à-dire Pas une souris tactile, nous introduisons la formule dans la première cellule dédiée (A2). Cliquez sur le clavier sur le signe "égal", 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 immédiatement dans toutes les cellules vides sélectionnées, appuyez sur la touche Entrée, mais la combinaison Ctrl + Enter. La formule remplira toutes les cellules vides:

Il reste maintenant que de remplacer les formules sur les valeurs de fixation des résultats. Mettez en surbrillance la plage A1: A12, copiez-la et insérez-les sur les cellules à l'aide d'un insert spécial.

Décharge

La réception, qui, sans exagération, devrait connaître quiconque travaille dans Excel. Son utilisation est capable d'améliorer, presque toute table, quel que soit son but. Dans toutes les formations, j'essaie de le montrer à mes auditeurs le premier jour.

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

Le choix des biens de la liste de prix, le nom du client de la clientèle de la clientèle, le FIO de l'employé de la dotation en personnel et ainsi de suite. Options d'application pour cette fonction définie.

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

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

Si vous avez un Excel 2003 ou plus, sélectionnez Données\u003e Validation dans le menu Données\u003e Validation. Si vous avez Excel 2007/2010, passez à l'onglet Données et cliquez sur Validation des données.

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

Dans le champ source (source), vous devez spécifier les valeurs qui doivent être répertoriées. Ici, les options sont possibles:

Entrez dans cette option de texte de champ via un point de virgule

Si la plage de cellules avec des valeurs de source est sur la feuille de courant - il suffit de la sélectionner simplement avec la souris.

Si c'est sur une autre feuille de ce livre, il devra donner un nom à l'avance (pour mettre en surbrillance les cellules, appuyez sur Ctrl + F3, entrez le nom de la plage sans espaces), puis enregistrez ce nom dans le champ.

Si certaines cellules, cordes ou colonnes sur la feuille ne sont pas affichées, vous pouvez copier toutes les cellules (ou uniquement les cellules visibles). Par défaut, Excel copies non seulement visibles, mais également des cellules cachées ou filtrées. Si vous souhaitez copier uniquement des cellules visibles, suivez les étapes décrites ci-dessous. Par exemple, vous pouvez copier uniquement des données de résumé de la feuille structurée.

Suivez les étapes ci-dessous.

Noter: Lors de la copie, les valeurs sont systématiquement insérées dans les cordes et les colonnes. Si la zone d'insertion contient des chaînes ou des colonnes cachées, vous devrez peut-être les afficher pour voir toutes les données copiées.

Lors de la copie et de l'insertion de cellules visibles dans la plage de données contenant des cellules cachées ou à laquelle le filtre est appliqué, on peut noter que les cellules cachées sont insérées avec visibles. Malheureusement, vous ne pouvez pas modifier ce paramètre lorsque vous copiez et insérez la plage de cellules dans Excel sur Internet, car l'insertion de cellules visibles uniquement n'est pas disponible.

Toutefois, si vous formatez les données comme une table et appliquez le filtre, vous pouvez copier et coller uniquement des cellules visibles.

Si vous n'avez pas besoin de formater les données sous forme de tableau et une application Excel classique est installée, vous pouvez ouvrir un livre dans celui-ci pour copier et coller des cellules visibles. Pour ce faire, cliquez sur Ouvert dans Excel Et suivez les étapes décrites dans l'article de copie et insérez uniquement des cellules visibles.

Informations Complémentaires

Vous pouvez toujours poser une question par Excel Tech Community, demander de l'aide dans la communauté communautaire des réponses, ainsi que d'offrir une nouvelle fonctionnalité ou une nouvelle amélioration sur le site Web.

Formatage conditionnel (5)
Listes et gammes (5)
Macros (procédures VBA) (63)
Divers (39)
Excel bugs et glitches (4)

Comment insérer des cellules copiées uniquement dans des cellules visibles / filtrées

En général, le sens de l'article déjà, je pense, est clair du nom. Juste légèrement étendre.

Ce n'est pas un secret que Excel vous permet de ne mettre en évidence que des lignes visibles. (Par exemple, si certains d'entre eux sont cachés ou filtrent).

Donc, si vous ne copiez que des cellules visibles, ils sont copiés car il devrait être copié. Mais lorsque vous essayez d'insérer copié dans la plage, les lignes filtrées (ou contenant des lignes cachées) sont le résultat de l'insertion ne seront pas exactement comme prévu. Les données seront insérées même dans des lignes cachées.

Copier une seule gamme de cellules et insérez uniquement pour visible
Pour que les données ne soient insérées que dans des cellules visibles, une telle macro peut être appliquée:

Option explicite dim rcopyrange comme plage "Ces données de copie macro Sub my_copy () si sélection.compte\u003e 1 puis définissez rcopyrange \u003d selection.specialcells (xlvisible) (xlvisible) d'autre: SET RCOPYRANGE \u003d ActiveCell End si fin "Cette macro insère les données à partir de la cellule sélectionnée SUB MY_PASTE () Si rcopyrange n'est rien, puis quittez SUM si rcopyrange.areas.count\u003e 1 puis msgbox "La plage insérée ne doit pas contenir plus d'une zone!", vbcrritical, "Gamme non valide": Quittez SUB DIM RCELL AS la portée, Li There, LE TERME, LE TERME, LCOUVE AS long, ICOL comme entier, icalculation comme application entier.Screenupdature \u003d False iCalculation \u003d application.calculation: application.Calculation \u003d -4135 pour ICOL \u003d 1 à rcopyrange .Columns.Count Li \u003d 0: Lcount \u003d 0: Le \u003d icol - 1 pour chaque rcell dans rcopyrange.columns (ICOL) .Cells do Si ActiveCell.Offset (Li, LE) .Tentirecolumn.hiden \u003d Faux et _ ActiveCell.Offset (Li, LE). CentreRow.Hidden \u003d FALSELL.COPY ACTIVECELL.OFFECTRECSETRE (LI, LE): LCOUNT \u003d LCOUNT + 1 extrémité Si LI \u003d LI + 1 boucle tandis que LCount\u003e \u003d rcell.row - rcopyrange.cells (1 ) .ROW Suivant RCell Next ICOL Application.Screenupdating \u003d TRUE: Application.Calculation \u003d IALCULATION END SUB

Option explicite DIM RCOPYRANGANGANGE AS LA GAMME "CETTE MACRO COPY DONNATS SUB MY_COPY () Si Sélection.Count\u003e 1 Ensemble RCOPYRANGE \u003d SELECTION.SPECIALCELLS (XLVISIBLE) Sinon: SET RCOPYRANGE \u003d ACTIVECELL END Si FIND SUB" Cette macro Insérez les données commençant par le Cellules sélectionnées SUB MY_PASTE () Si RCOPYRANGE N'EST RIEN DE SORTIE SUPPORT SUB SUB SUB IF SI RCOPYRANGE.AREAS.COUNT\u003e 1 ALORS MSGBOX "La plage insérée ne doit pas contenir plus d'une zone!", VBCritical, "plage non valide": sortie SUB DIM RCELL , Li aussi longtemps, le aussi longtemps, icol comme entier, icalculation comme application entier.Screenupdating \u003d fausse icalculation \u003d application.calculation: application.Calculation \u003d -4135 pour icol \u003d 1 à rcopyrange.columns.Count Li \u003d 0 : lcount \u003d 0: Le \u003d icol - 1 pour chaque rcell dans rcopyrange.columns (ICOL) .Cells Do Si ActiveCell.Offset (Li, LE). InticRecolumn.Chogen \u003d Faux et _ ActiveCell.Offset (Li, Le). CentreRow .Chidden \u003d faux alors rcell.copy ActiveCell.Offset (Li, LE): Lcount \u003d LCount + 1 extrémité si li \u003d li + 1 boucle tandis que lcount\u003e \u003d rcell.row - rcopyrange.cells (1) .row Suivant rcell Next iCol Application.Screenupdating \u003d true: application.calculation \u003d icalculation fin

Pour l'exhaustivité de l'image, les données de macros sont préférables à attribuer aux raccourcies (dans les codes ci-dessous, ceci est effectué automatiquement lors de l'ouverture d'un livre avec code). Pour cela, les codes ci-dessous doivent simplement être copiés sur le module. Ce livre Ce charbon) :

Option explicite "Nous annulons l'affectation de touches de raccourci avant de fermer le livre privé Sub Workbook_Beforeclose (Annuler sous forme booléenne) Application.Onkey" ^ Q ": Application.Onkey" ^ w "End Sub" Nous affectons des touches chaudes lors de l'ouverture d'un livre privé Workbook_open () application.onkey "^ q", "my_copy": application.onkey "^ w", "my_paste" end sous

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

Exemple de téléchargement

(46.5 KIB, 9 622 téléchargements)

Copier uniquement des cellules visibles et insérer uniquement dans Visible
À la demande des visiteurs du site décida de finaliser cette procédure. Il est maintenant possible de copier toutes les gammes: avec des lignes cachées, des colonnes cachées et une insertion de cellules copiées également dans toutes les gammes: avec des rangées cachées, des colonnes cachées. Fonctionne complètement comme la précédente: appuyer sur les touches Ctrl + q. Copier la plage souhaitée (avec des lignes et des colonnes cachées / filtrées ou non cachées)et insérez la combinaison de touches Ctrl + w. . L'insert est effectué également dans des lignes et des colonnes cachées / filtrées ou sans cachée.
Si des formules sont présentes dans la plage de copies, afin d'éviter le décalage de la liaison, seules les valeurs de cellule peuvent être copiées - c'est-à-dire Lors de l'insertion de valeurs, les formules ne seront pas insérées, mais le résultat de leur calcul. Ou si vous devez enregistrer les formats de cellules dans lesquels l'insertion se produit - seules les valeurs de cellule seront copiées et insérées. Pour ce faire, remplacez la ligne dans le code (dans le fichier ci-dessous):

Rcell.copy rrsesell.offset (lr, lc)

rcell.copy rrsesell.offset (lr, lc)

de telle:

Rrsesell.Offset (LR, LC) \u003d RCellue

rrsesell.Offset (LR, LC) \u003d RCellue

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

Exemple de téléchargement:

(54.5 KIB, 7 928 téléchargements)

Tu verras:
[]

L'article a aidé? Partagez un lien avec des amis! Cours vidéo

("Barre inférieure" :( "TextStyle": "statique", "TextPositionStatic": "bas", "TextAutohide": vrai, "TextpositionMarginStatique": 0, "Textpositiondynamic": "Bottomleft", "TextpositionMarginFt": 24, " TextPositionMarginRight ": 24," TextpositionMargintop ": 24," TextpositionMarginbottom ": 24," TextEffect ":" TextEffect "," TextessEcecting ":" TextOutCubic "," TextEffectDuration ": 600," TextesEffectsLidé ":" GAUCHE "," TEXTEFECTECTSLIDEDISTANCE " : 30, "texteseffectDelay": 500, "texteseffectstéparate": false, "textesEffect1": "diapositive", "texteseffectslidirection1": "droite", "texteseffectslidistance1": 120, "texteeffecing1": "texteeffechuration1": 600 "Texteseffectdelay1": 1000, "texteseffect2": "diapositive", "texteseffectslidé2": "droite", "texteseffectslidedistance2": 120, "textesEffectuer2": "textessculic", "texteseffectDuration2": 600, "textefectDelay2": 1500, " TextCSS ":" Affichage: bloc; Rembourrage: 12px; text-alignement: gauche; "," TextBGCSS ":" Affichage: Bloc; Position: absolu; haut: 0px; gauche: 0px; Largeur: 100%; Hauteur: 100% ; couleur de fond: # 333333; opacité: 0.6; filtre: a LPHA (opacité \u003d 60); "," Titlecss ":" Affichage: bloc; Position: relatif; Font: Bold 14px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", Sans Serif, Arial; Couleur: #fff; "," DescriptionCSS ":" Affichage: Bloc; Position: relatif; Font: 12px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", Sans Serif, Arial; Couleur: #fff; Marge-top: 8px; "," BoutonCSS ":" Affichage: bloc; Position: relatif; Margin-top: 8px; "," TextEffectResponssif ": vrai," TextEffectResponsige ": 640," TiTlecssresponsive ":" Taille de police: 12px; "," DescriptionCssive ":" Affichage: "", "BoutonCssResponsive": "", "addgooglefonts": faux, "Googlefont": "", "TextleftrightPercenForStatic": 40))

Sélection de données dans Excel dépensé à l'aide d'un filtre ou de tri. Maintenant, ils doivent être imprimés ou transférer vers un autre endroit. Copier pour exceller Il est configuré pour que les cellules cachées soient copiées.
Considérer deux manières à copie AK Copie des lignes filtrées dans Excel.
La première façon.
Il y a une excellente fonctionnalité dans Excel - fonction "Insert spécial" dans Excel.
Nous avons donc une table.
Comment installer un filtre, regarder dans l'article "Filtrer dans Excel ".
Nous supprimons le filtre de la liste de tous Ivanov. Il s'est avéré donc.
Sélectionnez la table, cliquez dans le menu contextuel "Copier". Appuyez sur la souris gauche dans la cellule A9, sélectionnez les "valeurs" dans le menu contextuel.
Cliquez sur OK". Voila. Non seulement la valeur des lignes visibles, mais également le format des cellules.
Il y a une nuance - Insérez les données filtrées ne sont pas dans la chaîne où le filtre est. Par exemple, dans notre exemple - pas en ligne 1-7, mais ci-dessous ou sur une autre feuille, etc. Si vous insérez dans la chaîne où le filtre vaut, les données filtrées et les lignes cachées par le filtre sont insérées. En général, il s'avère de la bouillie. La deuxième façon.
La table est la même. Sélectionnez une table avec des données filtrées. Sur l'onglet Home, cliquez dans le bouton Modifier trouver et mettre en surbrillance des fonctions dans Excel. Ensuite, appuyez sur le bouton "Go". Dans la boîte de dialogue qui apparaît, appuyez sur le bouton "Sélectionner ...". Dans la fenêtre "Sélection du groupe de groupe", nous mettons une tique sur la chaîne "uniquement des cellules visibles". Cliquez sur OK". Maintenant, sur la même table en surbrillance, le menu contextuel s'appelle la main droite. Appuyez sur la fonction "Copier". Dans un nouvel endroit (dans notre exemple, il s'agit d'une cellule A15) Cliquez sur "Coller". Tout. Il s'est avéré donc.
Comment, pas copier, immédiatement imprimer les données de filtrage dans Excel, Regarde dans l'article "Bookmark Leaf Excel" page marquage ""