Lorsque vous devez travailler avec une grande quantité de données dans Excel, où les lignes sont très étirées et ne tiennent pas sur un seul écran, il est difficile de comparer les valeurs d'une même ligne. Je dois constamment déplacer la barre de défilement horizontale. Le travail serait plus pratique si toute la ligne active était mise en évidence en couleur - comme un curseur sur toute la ligne. Pour mettre en évidence la ligne ou la colonne active avec de la couleur, créons notre propre fonction dans Excel à l'aide d'une macro et combinons-la avec une mise en forme conditionnelle.

Mettre en surbrillance une ligne dans Excel avec de la couleur

Pour créer vos propres cours étendus pour une ligne entière dans Excel, vous devez créer une macro et l'utiliser avec une mise en forme conditionnelle. Par exemple, créons un tableau avec de larges lignes et un grand nombre de colonnes. Nous devons mettre en évidence la ligne avec une couleur selon les critères Excel. Par exemple, si la cellule active se trouve dans la ligne 2, mettez en surbrillance toute la ligne 2 (dans le tableau ou sur la feuille entière). Par exemple, créons un tableau avec de larges lignes et un grand nombre de colonnes :

Mise en surbrillance de la ligne actuelle dans Excel avec de la couleur :

Par conséquent, la ligne est mise en surbrillance avec une mise en forme conditionnelle uniquement là où se trouve la cellule active. L'effet d'un large curseur de tableau pour une visualisation confortable des données avec une grande quantité d'informations. En rendant une cellule du tableau active, la ligne dans laquelle se trouve cette cellule est immédiatement mise en surbrillance.



Question utile ! Comment mettre en évidence la colonne active avec de la couleur ? Une méthode similaire est implémentée à l’aide d’une macro pour mettre en évidence non seulement la couleur de la ligne active, mais également la colonne. Pour ce faire :

Comment ça marche pour créer un curseur personnalisé pour un tableau

Utilisée en mise en forme conditionnelle, la fonction VBA que nous avons créée = ActiveRow() nous permet d'obtenir le numéro de la ligne actuelle dans laquelle l'une de ses cellules est devenue active. Le résultat du calcul de la fonction définie par l'utilisateur =ActiveRow() est comparé au résultat de la fonction =ROW(), qui renvoie également le numéro de la ligne actuelle à ce moment dans le calcul de mise en forme conditionnelle.

Puisque les deux fonctions sont exécutées avec une cellule active dans la même ligne, leurs valeurs sont égales et renvoient la valeur logique commune VRAI. En conséquence, la ligne est mise en évidence en couleur selon la condition. Si la valeur est VRAI, alors le format de couleur est attribué à toute la ligne active du tableau.

Chaque feuille de classeur Excel est divisée en lignes et colonnes dont le nombre dépend de la version de l'application. Les lignes et les colonnes peuvent être supprimées, ajoutées, fusionnées et déplacées. Nous avons déjà abordé la suppression conditionnelle de lignes, la suppression conditionnelle de colonnes et l'ajout de lignes vides aux classeurs Excel. Ci-dessous, nous examinerons une autre action effectuée avec des chaînes - la sélection.

Comment sélectionner une ligne ?

Il existe plusieurs manières de mettre en évidence une ligne :

1. Dans la fenêtre de la feuille ouverte, vous pouvez cliquer une fois avec le curseur fléché sur le numéro de la ligne souhaitée ;

2. également, dans la fenêtre de feuille ouverte, vous pouvez définir un marqueur de sélection dans n'importe quelle cellule de la ligne souhaitée et utiliser la combinaison de touches Maj+Espace (barre d'espace) ;

3. Et enfin, vous pouvez placer le marqueur de sélection sur la première cellule de la ligne souhaitée et utiliser le raccourci clavier Ctrl+Maj+Flèche droite. De plus, s'il y a des données dans la ligne, alors la première pression sur cette combinaison de touches sélectionnera une partie de la ligne avec des données, et la deuxième pression sélectionnera la ligne entière.

Comment sélectionner plusieurs lignes à la suite ?

1. Sélectionnez la première ligne de la plage souhaitée et, sans relâcher le bouton gauche de la souris, faites glisser le curseur fléché le long des numéros des lignes souhaitées ;

2. sélectionnez la première ligne de la plage souhaitée et, tout en appuyant sur la touche Shift, cliquez sur le numéro de la dernière ligne de la plage souhaitée ;

3. Cliquez sur le curseur de saisie de texte dans la fenêtre d'adresse de la barre de formule, saisissez une plage de lignes (par exemple, 10:20) et appuyez sur la touche Entrée.

Comment sélectionner plusieurs lignes non consécutives ?

Pour sélectionner sélectivement plusieurs lignes, vous devez cliquer sur les numéros des lignes souhaitées avec le curseur fléché tout en appuyant sur la touche Ctrl.

Comment sélectionner les lignes par condition ?

S'il est nécessaire de sélectionner sélectivement des lignes (à des fins de formatage, de copie ou à d'autres fins), par exemple contenant du texte spécifié ou, à l'inverse, ne contenant pas certains textes, vous pouvez utiliser un module complémentaire qui vous permet d'accélérer la sélection. de lignes pour diverses conditions. Vous trouverez ci-dessous une boîte de dialogue qui vous permet d'évaluer les capacités de ce complément.

Le module complémentaire vous permet de :

1. En un clic, appelez la boîte de dialogue macro directement depuis la barre d'outils Excel ;

2. Recherchez et mettez en surbrillance les lignes en fonction de la condition définie et des valeurs spécifiées,

Il est possible de saisir plusieurs valeurs de texte à la fois séparées par le signe ";" (point-virgule);

3. Sélectionnez l'une des huit conditions pour les lignes avec la valeur souhaitée :

- coïncide avec la valeur souhaitée ;

- ne correspond pas à la valeur souhaitée ;

- contient une signification locale ;

- ne contient pas la valeur souhaitée ;

- commence par la valeur souhaitée ;

- ne démarre pas avec la valeur souhaitée ;

- se termine par la valeur souhaitée ;

- ne se termine pas par la valeur souhaitée.

4. Lors de la recherche de texte, tenez compte ou ignorez la casse (distinguez les lettres majuscules et minuscules) ;

5. Lorsqu'une ligne est trouvée, il est possible de mettre en évidence :

a) toute la ligne sur la largeur de la feuille ;

b) partie de la chaîne limitée par la plage utilisée (de la première cellule remplie à la dernière cellule remplie);

c) partie de la chaîne limitée par la plage de recherche allouée (sélectionnée).

6. Sélectionnez une plage de chaînes pour rechercher les valeurs spécifiées et, si nécessaire, ajoutez des restrictions à la plage sélectionnée en définissant la chaîne de début et de fin.

Si une valeur dans une cellule répond à une condition définie par l'utilisateur, vous pouvez l'utiliser pour mettre en évidence cette cellule (par exemple, modifier son arrière-plan). Dans cet article, nous irons plus loin - nous sélectionnerons toute la ligne du tableau contenant cette cellule.

Laisser entrer la portée A6:C16 Il existe un tableau avec la liste des travaux, les délais et l'état d'avancement (voir dossier exemple).

Tâche 1 - valeurs de texte

Il est nécessaire de mettre en évidence la ligne contenant l'œuvre d'un certain statut. Par exemple, si les travaux n'ont pas commencé, alors la ligne sera surlignée en rouge, si les travaux ne sont pas encore terminés, puis en gris, et s'ils sont terminés, alors en vert. Nous sélectionnerons les lignes à l'aide de règles.

Solution1

Créons un petit tableau avec les statuts de travail dans la plage E6:E9 .

Sélectionnez une plage de cellules A7:C17 , contenant une liste d'œuvres, et installez-le via le menu Accueil/ Couleur de remplissage le fond de remplissage est rouge (nous supposons que toutes les œuvres sont initialement dans le statut Pas démarré).

Assurez-vous que la plage de cellules est sélectionnée A7:C17 (A7 devrait l'être). Appelez la commande de menu Mise en forme conditionnelle/Créer une règle/Utiliser une formule pour déterminer les cellules à formater.

• sur le terrain " Formater les valeurs pour lesquelles la formule suivante est vraie"vous devez saisir =$C7=$E$8 (dans la cellule E8 la valeur est trouvée En cours). Veuillez noter l'utilisation de ;
• appuyez sur le bouton Format;
• sélectionner l'onglet Remplir;
• choisissez la couleur grise ;
• Cliquez sur OK.

ATTENTION: Encore une fois j'attire l'attention sur la formule =$C7=$E$8 . Généralement, les utilisateurs saisissent =$C$7=$E$8 , c'est-à-dire entrez un symbole de dollar supplémentaire.

Vous devez suivre des étapes similaires pour mettre en évidence le travail dans le statut Complété. La formule dans ce cas ressemblera à =$C7=$E$9 et définira la couleur de remplissage sur vert.

En conséquence, notre tableau prendra la forme suivante.

Pour étendre rapidement les règles de mise en forme conditionnelle à une nouvelle ligne d'un tableau, sélectionnez les cellules de la nouvelle ligne ( A17:C17 ) et appuyez sur . Les règles seront copiées sur la ligne 17 tableaux.

Tâche2 – Dates

Supposons qu'un journal de participation des employés aux conférences scientifiques soit tenu (voir. fichier de feuille d'échantillon Dates).

Malheureusement, la colonne Date de visite n'est pas triée et vous devez mettre en évidence la date de la première et de la dernière visite de chaque employé. Par exemple, l'employé Kozlov s'est rendu à la conférence pour la première fois le 24 juillet 2009 et pour la dernière fois le 18 juillet 2015.

Tout d'abord, créons une formule pour la mise en forme conditionnelle dans les colonnes B et E. Si la formule renvoie VRAI, alors la ligne correspondante sera mise en surbrillance, si FAUX, alors elle ne le sera pas.

Dans la colonne D créé = MAX(($A7=$A$7 :$A$16)*$B$7 :$B$16)=$B7, qui définit la date maximale pour un employé spécifique.

Note: Si vous devez déterminer la date maximale quel que soit l'employé, alors la formule sera considérablement simplifiée =$B7=MAX($B$7:$B$16) et la formule matricielle ne sera pas nécessaire.

Sélectionnons maintenant toutes les cellules du tableau sans en-tête et créons une règle. Copions la formule dans la règle (vous n'avez pas besoin de la saisir sous forme de formule matricielle !).

Supposons maintenant que la colonne contenant les dates ait été triée et que vous souhaitiez sélectionner les lignes dont les dates de visite se situent dans une certaine plage.

Pour ce faire, utilisez la formule =AND($B23>$E$22,$B23<$E$23)

Pour les cellules E22 Et E23 avec des dates limites (surlignées en jaune) utilisées 22 $E$ et 23 $E$. Parce que leur référence ne doit pas changer dans les règles UV pour toutes les cellules du tableau.

Pour cellule B22 l'adressage mixte $B23 est utilisé, c'est-à-dire la référence à la colonne B ne doit pas changer (pour cela il y a un signe $ avant B), mais la référence à la ligne doit changer en fonction de la ligne du tableau (sinon toutes les valeurs de date seront comparées à la date de B23 ).

Ainsi, la règle UV par exemple pour une cellule A27 ressemblera à =ET($B27>$E$22,$B27<$E$23) , т.е. A27 sera mis en évidence parce que dans cette ligne la date du B27 se situe dans la plage spécifiée (pour les cellules de la colonne A, la sélection sera toujours effectuée en fonction du contenu de la colonne B de la même ligne - c'est la « magie » de l'adressage mixte $B23).

Et pour la cellule B31 la règle UV ressemblera à =AND($B31>$E$22,$B31<$E$23) , т.е. В31 не будет выделена, т.к. в этой строке дата из B31 ne se situe pas dans la plage spécifiée.

Afin de placer correctement les accents dans un document réalisé dans MS Excel, vous pouvez augmenter la taille des cellules, choisir une police ou une taille de lettre différente, ou vous pouvez peindre sur les carrés auxquels vous devez absolument faire attention.

Nous allons maintenant comprendre comment mettre en évidence les cellules avec de la couleur dans Excel ou changer la couleur de celles déjà remplies. Comment faire changer de couleur une cellule en fonction d'une condition donnée, en fonction de la valeur qui y est saisie, et comment travailler avec les règles créées.

Remplissage de bloc simple

Colorer un ou plusieurs blocs dans Excel n’est pas difficile. Tout d’abord, sélectionnez-les et dans l’onglet Accueil, cliquez sur la flèche à côté du pot de peinture pour développer la liste. Sélectionnez-y une couleur appropriée et si rien ne vous convient, cliquez sur "Autres couleurs".

D'ailleurs, vous pouvez remplir une ligne entière de cette façon, il suffit de cliquer d'abord sur son numéro pour la mettre en surbrillance. Vous pouvez lire un article séparé sur la sélection de cellules dans Excel.

Si vous travaillez avec un tableau dans lequel quelque chose est déjà ombré, vous pouvez changer la couleur des blocs ou le supprimer complètement, comme ceci. Cliquez dessus et parmi les couleurs ou sélectionnez-en une nouvelle, ou cliquez sur l'option « Non ».

En fonction des données saisies

Voyons maintenant comment faire en sorte qu'une cellule change de couleur en fonction d'une condition donnée. Pour cela, un formatage conditionnel est utilisé, sur lequel il existe un article séparé sur le site.

Texte

Prenons le tableau suivant comme exemple. Faisons en sorte que le rouge corresponde à une pomme, le jaune à une poire et l'orange à une orange.

Nous sélectionnons les données avec lesquelles nous allons travailler, dans mon cas ce sont les noms des fruits. Puis cliquez "Formatage conditionnel", que nous continuerons à utiliser en permanence. Dans la liste, cliquez sur "Créer une règle".

Cette fenêtre s'ouvre. Sélectionnez le type en haut - "Formater uniquement les cellules qui contiennent", nous le célébrerons également davantage. Ci-dessous nous indiquons les conditions : nous avons un texte qui contient certains mots. Dans le dernier champ, soit cliquez sur le bouton et sélectionnez une cellule, soit saisissez du texte.

La différence est qu'en plaçant un lien vers une cellule (=$B$4), la condition changera en fonction de ce qui y est tapé. Par exemple, au lieu d'une pomme en B4 j'indique des groseilles, la règle changera en conséquence, et des blocs avec le même texte seront remplis. Et si vous entrez pomme dans le champ, alors ce mot particulier sera recherché, et cela ne dépendra de rien.

Ici, sélectionnez une couleur de remplissage et cliquez sur OK. Pour voir toutes les options, cliquez sur le bouton «Autres».

La règle a été créée et nous la sauvegardons en cliquant sur le bouton « OK ».

En conséquence, tous les blocs contenant le texte spécifié étaient peints en rouge.

Des règles sont également créées pour d'autres fruits ; seule une option de remplissage différente est sélectionnée.

Numérique

Examinons maintenant les données numériques. Plaçons les nombres de la colonne D sur un certain fond selon la condition que nous avons fixée.

Sélectionnez la colonne, créez une règle et indiquez son type. Ensuite, nous écrivons - "Valeur" "supérieure à" "15". Vous pouvez soit saisir le dernier numéro manuellement, soit spécifier l'adresse de la cellule à partir de laquelle les données seront extraites. Nous décidons du remplissage, cliquez sur « OK ».

Les blocs contenant des nombres supérieurs à celui sélectionné sont colorés.

Spécifions plus de règles pour les cellules sélectionnées - sélectionnez "Gestion des règles".

Ici, sélectionnez tout comme je l'ai décrit ci-dessus, il vous suffit de changer la couleur et de définir la condition "inférieur ou égal à".

Lorsque tout est prêt, cliquez sur « Appliquer » et « OK ».

Tout fonctionne, les valeurs sont égales et en dessous de 15 sont peintes en bleu pâle.

Ici, la différence entre les marchandises importées et vendues serait clairement visible. Sélectionnez deux colonnes et cliquez sur "Règles de mise en évidence des cellules" – "Valeurs en double". Choisissez une couleur appropriée. Après cela, si deux cellules côte à côte sont colorées, cela signifie que les fruits livrés ont été vendus.

Utilisons la dernière colonne comme exemple pour montrer comment faire changer la couleur d'une cellule en fonction de la valeur spécifiée. Sélectionnez-les et accédez à "Gestion des règles".

Nous en créons un nouveau pour le fragment actuel ; si nécessaire, dans cette liste déroulante, vous pouvez sélectionner soit pour la feuille entière, soit pour d'autres feuilles.

Sélectionnez les éléments requis dans la fenêtre qui s'ouvre. Je remplirai en vert foncé toutes les valeurs supérieures à 90. Puisque dans le dernier champ j'ai indiqué l'adresse (=$F$15), alors si le nombre 90 dans la cellule passe, par exemple, à 110, la règle va également changer. Enregistrez les modifications en cliquant sur le bouton « OK ».

Je vais créer une autre règle, mais dans celle-ci je mettrai en surbrillance en vert clair tout ce qui est inférieur ou égal à 90. N'oubliez pas de tout sauvegarder en cliquant sur les boutons en bas à droite.

C'est ce qui s'est passé. À partir de la dernière colonne, vous pouvez déterminer visuellement rapidement le bénéfice de la vente du produit qui a été le plus élevé.

Par exemple, si le prix change, les règles seront également révisées. Si la valeur devient supérieure ou inférieure à la valeur spécifiée, la couleur de la cellule changera automatiquement.

Je pense que vous avez remarqué que la règle est créée non seulement pour le texte et les chiffres, mais aussi pour les dates, et en fonction du remplissage et de la présence d'erreurs dans les cellules. Précisez la condition, choisissez une couleur et enregistrez.

Pour voir ce que vous avez ajouté, sélectionnez la plage et dans la fenêtre "Gestion des règles" il y aura une liste complète. À l'aide des boutons en haut, vous pouvez les ajouter, les modifier ou les supprimer.

Travailler avec un projet VB (12)
Formatage conditionnel (5)
Listes et plages (5)
Macros (procédures VBA) (63)
Divers (39)
Bugs et problèmes Excel (3)

Surligner les lignes en couleur une à la fois

Il existe un tableau ressemblant à ceci :

S'il y a beaucoup de données dans le tableau, pour une visualisation plus pratique, vous pouvez sélectionner les lignes une par une - ce qu'on appelle le « zèbre ». Ceux. une ligne est vide, la suivante est vide, puis à nouveau vide, etc.
Pour ce faire, il n'est pas du tout nécessaire d'écrire des macros (et encore moins de le faire manuellement - c'est effrayant même d'y penser !).

Mise en évidence à l'aide de la mise en forme conditionnelle
La première option consiste à utiliser l'outil intégré d'Excel - Mise en forme conditionnelle :

1. sélectionnez la plage souhaitée
2. Pour Excel 2003 - Format-Formatage conditionnel; Excel 2007 - Maison-Mise en forme conditionnelle-Créer une règle (Nouvelle règle...)
3. choisir formule(dans Excel 2007 - Utilisez une formule pour déterminer les cellules à formater)
4. saisissez la condition dans le champ - =REMAT(ROW();2)
5. sélectionnez la méthode de formatage des cellules (dans l'exemple, la couleur de remplissage est le bleu)
6. Cliquez sur OK.

Télécharger l'exemple avec UV

(35,0 Ko, 5 281 téléchargements)

Utiliser des styles de tableaux intelligents
Pour les heureux propriétaires d'Excel 2007 et versions antérieures, il existe un autre moyen sans utiliser de macros. Il s'agit d'un outil Tableau intégré. Pour colorer les lignes, vous devez sélectionner le tableau (plage) souhaité - allez dans l'onglet Insérer et choisissez Tableau

La plage sélectionnée aura automatiquement le formatage de tableau par défaut appliqué. Sélectionnez n'importe quelle cellule de ce tableau et accédez à l'onglet - Designer (Design) -Styles de tableaux. Vous pouvez sélectionner l’une des options proposées et postuler. Si aucune des options ne convient, nous créons la nôtre. Développez la liste des styles et sélectionnez Créer un style de tableau


Dans la boîte de dialogue, nous définissons les paramètres nécessaires


Les principaux paramètres de coloration des lignes sont : Rayure de la première rangée Et Bande de deuxième rangée. Vous pouvez définir d'autres paramètres à votre discrétion.

Si vous cochez la case Définir comme style rapide de tableau par défaut pour ce document, lorsque vous convertirez ensuite la plage en tableau, le style de conception que vous attribuez dans cette boîte de dialogue lui sera appliqué.

Cliquez sur OK.
Maintenant sur le même onglet Travailler avec des tableaux (outils de tableau) -Designer (Design) -Styles de tableaux sélectionnez le style de tableau créé. Le formatage que nous avons créé est désormais appliqué au tableau. Nous supprimons maintenant le tableau pour supprimer toutes les fonctionnalités offertes par cet outil et, dans certains cas, les inconvénients. Après avoir supprimé un tableau, les filtres qui étaient installés sur le tableau lors de sa création seront supprimés, mais le formatage restera. Sélectionnez n'importe quelle cellule du tableau - bouton droit de la souris - Tableau -


ou sélectionnez sur le panneau Travailler avec des tableaux (outils de tableau)-groupe Outils -Convertir en plage

À première vue, c’est fastidieux et prend du temps, mais en fait c’est aussi simple que décortiquer des poires et prend très peu de temps. Et si vous tenez compte du fait que vous pouvez créer les styles nécessaires et les appliquer sur différentes feuilles à de nombreux tableaux en quelques clics seulement, vous obtenez une économie importante de temps et de ressources, ainsi que les fonctionnalités nécessaires.

L'article a-t-il aidé ? Partagez le lien avec vos amis ! Tutoriels 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; texte-align: gauche;", "textbgcss": "affichage: gauche: 0px; opacité: 0,6; ","titlecss":"affichage:bloc; 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))