En regardant les chiffres secs des tableaux, il est difficile à première vue de saisir l'image globale qu'ils représentent. Mais, dans le programme Microsoft Excel, il existe un outil de visualisation graphique avec lequel vous pouvez visualiser les données contenues dans les tableaux. Cela vous permet d'assimiler plus facilement et plus rapidement les informations. Cet outil est appelé formatage conditionnel. Voyons comment utiliser la mise en forme conditionnelle dans Microsoft Excel.

Afin de mettre en forme une certaine zone de cellules, vous devez sélectionner cette zone (le plus souvent une colonne), et étant dans l'onglet "Accueil", cliquez sur le bouton "Mise en forme conditionnelle", qui se trouve sur le ruban dans la barre d'outils "Styles".

Après cela, le menu de mise en forme conditionnelle s'ouvre. Il existe trois principaux types de formatage:

Histogrammes;
Balances numériques;
Badges.

Pour effectuer une mise en forme conditionnelle sous forme d'histogramme, sélectionnez la colonne contenant les données et cliquez sur l'élément de menu correspondant. Comme vous pouvez le voir, il existe plusieurs types d'histogrammes avec dégradé et remplissage uni. Choisissez celui qui, selon vous, correspond le mieux au style et au contenu du tableau.

Comme vous pouvez le voir, les histogrammes sont apparus dans les cellules sélectionnées de la colonne. Plus la valeur numérique des cellules est élevée, plus l'histogramme est long. De plus, dans les versions d'Excel 2010, 2013 et 2016, il est possible d'afficher correctement les valeurs négatives dans un histogramme. Mais, la version 2007 n'a pas une telle opportunité.

Lorsque vous utilisez une échelle de couleurs au lieu d'un histogramme, il est également possible de sélectionner différentes options pour cet outil. Dans ce cas, en règle générale, plus la valeur est élevée dans la cellule, plus la couleur de l'échelle est riche.

L'outil le plus intéressant et le plus complexe parmi cet ensemble de fonctions de formatage sont les icônes. Il existe quatre groupes principaux d'icônes: directions, formes, indicateurs et estimations. Chaque option sélectionnée par l'utilisateur implique l'utilisation d'une icône différente lors de l'évaluation du contenu d'une cellule. Toute la zone sélectionnée est analysée par Excel et toutes les valeurs de cellule sont divisées en parties, selon les valeurs qui y sont spécifiées. Les icônes vertes sont appliquées aux valeurs les plus élevées, les valeurs jaunes aux valeurs moyennes et les valeurs du tiers le plus petit sont signalées par des icônes rouges.

Lorsque les flèches sont sélectionnées, la signalisation sous forme de directions est également utilisée comme icônes, en plus de la conception des couleurs. Ainsi, la flèche pointant vers le haut s'applique aux grandes valeurs, à gauche - aux valeurs moyennes, vers le bas - aux petites valeurs. Lorsque vous utilisez des formes, les valeurs les plus élevées sont marquées d'un cercle, les moyennes avec un triangle et les petites avec un losange.

Règles de sélection de cellule

Par défaut, une règle est utilisée dans laquelle toutes les cellules de la sélection sont marquées d'une certaine couleur ou icône, en fonction des valeurs qui s'y trouvent. Mais, en utilisant le menu, dont nous avons déjà parlé ci-dessus, vous pouvez appliquer d'autres règles de dénomination.

Cliquez sur l'élément de menu "Règles de sélection de cellule". Comme vous pouvez le voir, il existe sept règles de base:

Plus;
Plus petite;
Également;
Entre;
Date;
Dupliquer les valeurs.

Considérons l'application de ces actions par des exemples. Sélectionnez la plage de cellules et cliquez sur l'élément "Plus ...".

Une fenêtre s'ouvre dans laquelle vous devez définir les valeurs supérieures au nombre qui sera mis en surbrillance. Cela se fait dans le champ "Formater les cellules qui sont plus grandes". Par défaut, la valeur du milieu de la plage est automatiquement saisie ici, mais vous pouvez définir toute autre valeur ou vous pouvez spécifier l'adresse de la cellule qui contient ce nombre. Cette dernière option convient aux tableaux dynamiques dans lesquels les données changent constamment, ou pour une cellule dans laquelle une formule est appliquée. Nous avons défini la valeur à 20 000 pour l'exemple.

Dans le champ suivant, vous devez décider comment les cellules seront mises en évidence: remplissage rouge clair et couleur rouge foncé (par défaut); remplissage jaune et texte jaune foncé; texte rouge, etc. De plus, il existe un format personnalisé.

Lorsque vous accédez à cet élément, une fenêtre s'ouvre dans laquelle vous pouvez modifier la sélection, presque comme vous le souhaitez, en appliquant diverses options pour la police, le remplissage et les bordures.

Après avoir décidé des valeurs dans la fenêtre des paramètres des règles de sélection, cliquez sur le bouton "OK".

Comme vous pouvez le voir, les cellules sont sélectionnées selon la règle établie.

Selon le même principe, les valeurs sont mises en évidence lors de l'application des règles «Inférieur à», «Entre» et «Égal». Uniquement dans le premier cas, les cellules inférieures à la valeur définie par vous sont mises en surbrillance; dans le second cas, l'intervalle de nombres est défini, les cellules avec lesquelles seront mises en évidence; dans le troisième cas, un nombre spécifique est défini et seules les cellules le contenant seront mises en surbrillance.

La règle de sélection "Texte contient" s'applique principalement aux cellules au format texte. Dans la fenêtre de réglage des règles, vous devez spécifier un mot, une partie de mot ou un ensemble séquentiel de mots, une fois trouvés, les cellules correspondantes seront mises en surbrillance de la manière que vous avez définie.

La règle Date s'applique aux cellules contenant des valeurs au format date. Dans le même temps, dans les paramètres, vous pouvez définir la sélection des cellules en fonction du moment où l'événement s'est produit ou se produira: aujourd'hui, hier, demain, pour les 7 derniers jours, etc.

En appliquant la règle "Valeurs dupliquées", vous pouvez personnaliser la sélection des cellules, en fonction de la conformité des données qui y sont placées avec l'un des critères: qu'il s'agisse de données dupliquées ou uniques.

Règles de sélection pour la première et la dernière valeur

En outre, il existe un autre élément intéressant dans le menu de mise en forme conditionnelle - "Règles de sélection des première et dernière valeurs". Ici, vous pouvez définir la sélection uniquement des valeurs les plus grandes ou les plus petites de la plage de cellules. En même temps, vous pouvez utiliser la sélection, à la fois par valeurs ordinales et par pourcentage. Les critères de sélection suivants sont indiqués dans les éléments de menu correspondants:

10 premiers éléments;
Les 10 premiers%;
Les 10 derniers éléments;
Les 10 derniers%;
Au-dessus de la moyenne;
En dessous de la moyenne.

Mais, après avoir cliqué sur l'élément approprié, vous pouvez légèrement modifier les règles. Une fenêtre s'ouvre, dans laquelle vous pouvez sélectionner le type de sélection et, si vous le souhaitez, vous pouvez définir une limite de sélection différente. Par exemple, après avoir cliqué sur l'item «10 premiers éléments», dans la fenêtre qui s'ouvre, dans le champ «Formater les premières cellules», nous avons changé le nombre 10 en 7. Ainsi, après avoir cliqué sur le bouton «OK», ce ne sont pas les 10 plus grandes valeurs qui seront mises en évidence, mais seulement 7.

Créer des règles

Nous avons parlé ci-dessus des règles déjà définies dans Excel et l'utilisateur peut simplement sélectionner l'une d'entre elles. Mais, en plus, s'il le souhaite, l'utilisateur peut créer ses propres règles.

Pour ce faire, vous devez cliquer dans n'importe quelle sous-section du menu de mise en forme conditionnelle sur l'élément "Autres règles ..." situé tout en bas de la liste ". Ou cliquez sur l'élément "Créer une règle ..." situé en bas du menu principal de mise en forme conditionnelle.

Une fenêtre s'ouvre dans laquelle vous devez sélectionner l'un des six types de règles:

Formatez toutes les cellules en fonction de leurs valeurs;
Formatez uniquement les cellules qui contiennent;
Formatez uniquement les première et dernière valeurs;
Formatez uniquement les valeurs supérieures ou inférieures à la moyenne;
Formatez uniquement les valeurs uniques ou en double;
Utilisez une formule pour déterminer les cellules à mettre en forme.

Selon le type de règles sélectionné, en bas de la fenêtre, vous devez configurer le changement de la description des règles en définissant les valeurs, les intervalles et d'autres valeurs, dont nous avons déjà discuté ci-dessous. Seulement dans ce cas, le réglage de ces valeurs sera plus flexible. Il est immédiatement défini, en changeant la police, les bordures et le remplissage, à quoi ressemblera la sélection. Une fois tous les réglages terminés, vous devez cliquer sur le bouton "OK" pour enregistrer les modifications.

Gestion des règles

Dans Excel, vous pouvez appliquer plusieurs règles à la fois à la même plage de cellules, mais seule la dernière règle saisie sera affichée à l'écran. Afin de réguler l'exécution de diverses règles concernant une certaine plage de cellules, vous devez sélectionner cette plage, et dans le menu principal de mise en forme conditionnelle, accédez aux règles de gestion des éléments.

Une fenêtre s'ouvre, dans laquelle toutes les règles qui s'appliquent à la plage de cellules sélectionnée sont présentées. Les règles sont appliquées de haut en bas au fur et à mesure qu'elles sont répertoriées. Ainsi, si les règles se contredisent, alors en fait seule la plus récente d'entre elles est exécutée à l'écran.

Pour inverser les règles, il existe des boutons fléchés vers le haut et vers le bas. Pour que la règle s'affiche à l'écran, vous devez la sélectionner et cliquer sur le bouton en forme de flèche dirigée vers le bas jusqu'à ce que la règle occupe la toute dernière ligne de la liste.

Il existe également une autre option. Vous devez cocher la case dans la colonne avec le nom "Arrêter si vrai" en face de la règle dont nous avons besoin. Ainsi, en parcourant les règles de haut en bas, le programme s'arrêtera exactement à la règle près de laquelle se trouve cette marque, et ne descendra pas, ce qui signifie que cette même règle sera effectivement exécutée.

Dans la même fenêtre, il y a des boutons pour créer et modifier la règle sélectionnée. Après avoir cliqué sur ces boutons, les fenêtres de création et de modification des règles sont lancées, ce dont nous avons déjà parlé ci-dessus.

Pour supprimer une règle, vous devez la sélectionner et cliquer sur le bouton "Supprimer la règle".

De plus, vous pouvez supprimer des règles via le menu principal de mise en forme conditionnelle. Pour ce faire, cliquez sur l'élément "Supprimer les règles". Un sous-menu s'ouvre dans lequel vous pouvez sélectionner l'une des options de suppression: soit supprimer les règles uniquement sur la plage de cellules sélectionnée, soit supprimer absolument toutes les règles qui se trouvent sur la feuille Excel ouverte.

Comme vous pouvez le voir, la mise en forme conditionnelle est un outil très puissant pour visualiser les données dans un tableau. Avec son aide, vous pouvez configurer le tableau de manière à ce que les informations générales le concernant soient assimilées par l'utilisateur en un coup d'œil. De plus, la mise en forme conditionnelle ajoute beaucoup d'attrait esthétique au document.

La mise en forme conditionnelle offre une visibilité lors de l'exploration et de l'analyse des données, de la détection des problèmes critiques et de l'identification des modèles et des tendances.

La mise en forme conditionnelle facilite la mise en évidence des cellules ou des plages souhaitées, la mise en évidence des valeurs inhabituelles et la visualisation de vos données à l'aide de graphiques à barres, de barres de couleurs et de jeux d'icônes.

Lorsque vous appliquez une mise en forme conditionnelle, l'apparence des cellules change en fonction des conditions que vous spécifiez. Si les conditions sont remplies, la mise en forme est appliquée à la plage de cellules. Si les conditions ne sont pas remplies, le formatage n'est pas appliqué. Il existe de nombreuses conditions intégrées et vous pouvez également créer vos propres conditions (y compris en utilisant une formule qui donne la valeur True ou False).

Formatage conditionnel de l'enregistrement mensuel de données haute température pour différents endroits, les couleurs correspondent intuitivement aux valeurs (valeurs de température plus élevées orange et rouge, valeurs de basse température jaune et vert)
Mise en forme conditionnelle en utilisant la couleur de fond des cellules pour mettre en évidence différentes catégories de produits; l'icône à trois flèches montre les tendances des prix (à la hausse, au maintien, à la baisse), tandis que les graphiques à barres montrent la différence des augmentations de prix.

La mise en forme conditionnelle peut être appliquée à une plage de cellules (sélectionnées ou nommées), à un tableau Excel et même à un rapport de tableau croisé dynamique dans Excel pour Windows. Notez un certain nombre de facteurs supplémentaires affectant la mise en forme conditionnelle dans un rapport de tableau croisé dynamique - voir Application de la mise en forme conditionnelle à un rapport de tableau croisé dynamique ci-dessous sur l'onglet Windows.

Téléchargez un exemple de livre

Vous pouvez télécharger un exemple de livre contenant divers exemples de mise en forme conditionnelle à l'aide de règles standard telles que le haut et le bas, les données en double, les histogrammes, les jeux d'icônes et les rails de couleurs, ainsi que vos propres règles personnalisées.

Formatage des cellules avec une échelle à deux couleurs

Les barres de couleur sont des éléments visuels qui vous aident à comprendre la distribution et la dispersion des données. L'échelle à deux couleurs vous aide à comparer une plage de cellules en utilisant la gradation de deux couleurs. Le niveau de luminosité d'une couleur est élevé, moyen ou faible. Par exemple, dans l'échelle jaune-vert ci-dessous, vous pouvez spécifier que les cellules avec des valeurs élevées seront plus vertes et les cellules avec des valeurs faibles seront plus jaunes.

Conseil: IL Y A ou IFERREUR pour renvoyer une valeur autre que error.

Formatage rapide

Options de formatage

Formatage avancé

Dans l'onglet Accueil en groupe modes et sélectionnez l'élément Gestion des règles... Une boîte de dialogue s'ouvre.

Créer une règle... Une boîte de dialogue s'ouvre.
1. Réduire la boîte de dialogue sur le terrain Cela s'applique à Développer la boîte de dialogue.
  
  Changer de règle... Une boîte de dialogue s'ouvre.

Dans la section Appliquer la règle

aux cellules sélectionnéespour sélectionner des cellules par sélection;

<метка значения> pour sélectionner des cellules avec une étiquette spécifique.

à toutes les cellules contenant des valeurs<метка значения> pour<название строки> pour sélectionner des cellules avec une étiquette spécifique autre que les sous-totaux et les totaux généraux.

Dans la section Sélectionnez le type de règle sélectionnez (par défaut).

En groupe Changer la description de la règle dans la liste déroulante Style de format sélectionner un article Échelle bicolore.

Dans les champs Un type paramètres Valeur minimum et Valeur maximum sélectionnez leurs types.

Veuillez sélectionner valeur minimum et valeur maximum.

Dans ce cas, n'entrez pas minimal et maximumsens.

Sélectionner un article Nombrepuis entrez minimal et maximumsens.

Entrer minimal et maximumsens.

Formatage en centile. Sélectionner un article Centile Valeur minimum et Maximum valeur

Utilisez un centile si vous souhaitez rendre un groupe de valeurs élevées (par exemple, le 20e centile supérieur) avec une teinte et un groupe de valeurs faibles (par exemple, le 20e centile inférieur) avec une autre, car elles correspondent à des valeurs extrêmes qui peuvent déplacer la visualisation des données.

Sélectionner un article Formulepuis entrez minimal et maximum valeurs.
- Remarques:
  - Sois sûr que minimal valeur inférieure à maximum.
    
    Pour les paramètres Valeur minimum et Valeur maximum valeur minimum et type de pourcentage pour valeur maximum.

Pour sélectionner des options Valeur minimum et Valeur maximum barre de couleurs, cliquez sur un élément Couleur pour chaque valeur, puis choisissez une couleur.

Autres couleurs... L'échelle de couleurs sélectionnée apparaît dans la fenêtre aperçu.

Formatage des cellules à l'aide de l'échelle à trois couleurs

Les barres de couleur sont des éléments visuels qui vous aident à comprendre la distribution et la dispersion des données. L'échelle tricolore vous aide à comparer une plage de cellules à l'aide d'une gradation à trois couleurs. Le niveau de luminosité d'une couleur est élevé, moyen ou faible. Par exemple, pour une échelle rouge-jaune-vert, vous pouvez spécifier que les cellules avec des valeurs élevées sont vertes, les cellules avec des valeurs moyennes sont jaunes et les cellules avec des valeurs faibles sont rouges.

Formatage rapide

Vous pouvez modifier la portée des champs à partir de la zone Valeurs dans un rapport de tableau croisé dynamique à l'aide du bouton Options de formatage en regard d'un champ de tableau croisé dynamique auquel une mise en forme conditionnelle est appliquée.

Formatage avancé

Sélectionnez une ou plusieurs cellules dans une plage, un tableau ou un rapport de tableau croisé dynamique.

Dans l'onglet Accueil en groupe modes cliquez sur la flèche à côté du bouton et sélectionnez Gestion des règles... Une boîte de dialogue s'ouvre Gestionnaire de règles de mise en forme conditionnelle.

Effectuez l'une des actions suivantes:

Pour ajouter une mise en forme conditionnelle, cliquez sur Créer une règle... Une boîte de dialogue s'ouvre.

Suivez ces étapes pour modifier la mise en forme conditionnelle.
1. Assurez-vous que la zone de liste Afficher les règles de mise en forme pour le rapport de feuille, de tableau ou de tableau croisé dynamique approprié est sélectionné.
  
  Vous pouvez modifier la plage de cellules selon vos besoins. Pour ce faire, appuyez sur le bouton Réduire la boîte de dialogue sur le terrain Cela s'applique àpour masquer temporairement la boîte de dialogue. Sélectionnez ensuite la nouvelle plage de cellules sur la feuille et cliquez sur Développer la boîte de dialogue.
  
  Sélectionnez une règle puis cliquez sur Changer de règle... Une boîte de dialogue s'ouvre Modification de la règle de mise en forme.

Dans la section Appliquer la règle sélectionnez l'une des options suivantes pour modifier la sélection de champ dans la zone de valeurs du rapport de tableau croisé dynamique:

Veuillez sélectionner seulement ces cellules;

Veuillez sélectionner toutes les cellules<поле значения> avec les mêmes champs;

Veuillez sélectionner toutes les cellules<поле значения> .

En groupe Sélectionnez le type de règle sélectionner un article Mettre en forme toutes les cellules en fonction de leurs valeurs.

En groupe Changer la description de la règle dans la liste déroulante Style de format sélectionner un article Échelle à trois couleurs.

Sélectionnez le type de paramètre Valeur minimum, Signifier et Valeur maximum

Formatage des valeurs minimales et maximales. Sélectionner un article Signifier.

Dans ce cas, n'entrez pas minimal et maximum sens.

Formatage d'une valeur numérique, d'une valeur de date ou d'une valeur d'heure. Sélectionner un article Nombrepuis entrez minimal, la moyenne et maximum valeurs.

Formatage en pourcentage. Sélectionner un article Pour centpuis entrez minimal, la moyenne et maximum valeurs. Les valeurs valides sont comprises entre 0 (zéro) et 100. N'entrez pas de signe de pourcentage.

Formatage en centile. Sélectionner un article Centilepuis entrez Valeur minimum, Signifier et Valeur maximum.

Utilisez un centile lorsque vous souhaitez rendre un groupe de valeurs élevées (par exemple, les 20 centiles supérieurs) avec une teinte et un groupe de valeurs faibles (par exemple, les 20 centiles inférieurs) avec une autre, car ils correspondent à des valeurs extrêmes qui peuvent déplacer la visualisation des données.

Mise en forme du résultat d'une formule. Sélectionner un article Formulepuis entrez minimal, la moyenne et maximum valeurs.

La formule doit renvoyer un nombre, une date ou une heure. Commencez à saisir une formule avec un signe égal ( = ). Une formule non valide empêchera l'application du formatage. Vérifiez la formule pour vous assurer qu'elle ne renvoie pas de valeur d'erreur.

Remarques:
- Vous pouvez définir les valeurs minimale, moyenne et maximale pour une plage de cellules. Sois sûr que minimal valeur moins milieu, et c'est à son tour moins maximum.
  
  Pour les paramètres Valeur minimum, Signifier et Valeur maximum différents types peuvent être sélectionnés. Par exemple, vous pouvez choisir un type numérique pour valeur minimum, centile pour valeur moyenne et type de pourcentage pour valeur maximum.
  
  Dans de nombreux cas signifier la valeur par défaut de 50% est optimale, mais vous pouvez la modifier en fonction de vos besoins spécifiques.

Pour sélectionner une échelle de couleurs Valeur minimum, Signifier et Valeur maximum cliquez sur un élément Couleur pour chacun d'eux, puis choisissez une couleur.

Pour sélectionner des couleurs supplémentaires ou créer une couleur personnalisée, cliquez sur Autres couleurs.

L'échelle de couleurs sélectionnée apparaît dans la fenêtre aperçu.

Formatage des cellules avec des histogrammes

Les graphiques à barres vous aident à afficher la valeur d'une cellule par rapport à d'autres cellules. La longueur de l'histogramme correspond à la valeur de la cellule. Plus il est long, plus la valeur est élevée. Les graphiques à barres sont parfaits pour identifier les indicateurs clés, en particulier dans les grandes quantités de données, telles que les ventes de jouets maximales et minimales dans un rapport de ventes de vacances.

L'exemple présenté ici utilise des histogrammes pour mettre en évidence les valeurs extrêmes positives et négatives. Les graphiques à barres peuvent être formatés pour commencer au milieu d'une cellule et s'étirer vers la gauche jusqu'à des valeurs négatives.

Conseil: Si l'une des cellules de la plage contient une formule qui renvoie une erreur, la mise en forme conditionnelle ne lui est pas appliquée. Pour vous assurer que la mise en forme conditionnelle est appliquée à ces cellules, utilisez la fonction IL Y A ou IFERREUR

Formatage rapide

Formatage avancé

Sélectionnez une ou plusieurs cellules dans une plage, un tableau ou un rapport de tableau croisé dynamique.

Effectuez l'une des actions suivantes:

Pour ajouter une mise en forme conditionnelle, cliquez sur Créer une règle... Une boîte de dialogue s'ouvre.

Suivez ces étapes pour modifier la mise en forme conditionnelle.
1. Assurez-vous que la zone de liste Afficher les règles de mise en forme pour le rapport de feuille, de tableau ou de tableau croisé dynamique approprié est sélectionné.
  
  Vous pouvez modifier la plage de cellules selon vos besoins. Pour ce faire, appuyez sur le bouton Réduire la boîte de dialogue sur le terrain Cela s'applique àpour masquer temporairement la boîte de dialogue. Sélectionnez ensuite la nouvelle plage de cellules sur la feuille et cliquez sur Développer la boîte de dialogue.
  
  Sélectionnez une règle puis cliquez sur Changer de règle... Une boîte de dialogue s'ouvre Modification de la règle de mise en forme.

Dans la section Appliquer la règle sélectionnez l'une des options suivantes pour modifier la sélection de champ dans la zone de valeurs du rapport de tableau croisé dynamique:

pour sélectionner les champs par sélection, sélectionner seulement ces cellules;

sélectionner des champs par le champ correspondant, sélectionner toutes les cellules<поле значения> avec les mêmes champs;

pour sélectionner des champs par champ de valeur, sélectionner toutes les cellules<поле значения> .

En groupe Sélectionnez le type de règle sélectionner un article Mettre en forme toutes les cellules en fonction de leurs valeurs.

En groupe Changer la description de la règle dans la liste déroulante Style de format sélectionner un article diagramme à bandes.

Sélectionnez des valeurs Le minimum et Maximum pour type... Effectuez l'une des actions suivantes:

Formatage des valeurs minimales et maximales. Veuillez sélectionner Valeur minimum et Valeur maximum.

Dans ce cas, n'entrez pas Valeur minimum et Valeur maximum.

Formatage d'une valeur numérique, d'une valeur de date ou d'une valeur d'heure. Sélectionner un article Nombrepuis entrez minimal et maximumsens.

Formatage en pourcentage. Sélectionner un article Pour centpuis entrez minimal et maximum valeurs.

Les valeurs valides vont de 0 (zéro) à 100. N'entrez pas de signe de pourcentage.

Utilisez des pourcentages si vous souhaitez rendre toutes les valeurs proportionnellement, car la distribution des valeurs dans ce cas est proportionnelle.

Formatage en centile. Sélectionner un article Centilepuis entrez les valeurs des paramètres Valeur minimum et Valeur maximum.

Les valeurs de centile valides sont comprises entre 0 (zéro) et 100.

Utilisez des centiles lorsque vous souhaitez rendre un groupe de valeurs élevées (par exemple, le 20e centile supérieur) dans une proportion de l'histogramme et un groupe de valeurs faibles (par exemple, le 20e centile inférieur) dans une autre, car elles correspondent à des valeurs extrêmes qui peuvent déplacer la visualisation des données.

Mise en forme du résultat d'une formule. Sélectionner un article Formulepuis entrez les valeurs des paramètres Valeur minimum et Valeur maximum.
- La formule doit renvoyer un nombre, une date ou une heure.
  
  Commencez à saisir une formule avec un signe égal ( = ).
  
  Une formule non valide empêchera l'application du formatage.
  
  Vérifiez la formule pour vous assurer qu'elle ne renvoie pas de valeur d'erreur.

Remarques:

Sois sûr que minimal valeur inférieure à maximum.

Pour les paramètres Valeur minimum et Valeur maximum différents types peuvent être sélectionnés. Par exemple, vous pouvez choisir un type numérique pour valeur minimum et type de pourcentage pour valeur maximum.

Pour sélectionner des échelles de couleurs Valeur minimum et Valeur maximum sélectionner un article Couleur de la colonne.

Si vous souhaitez sélectionner des couleurs supplémentaires ou créer une couleur personnalisée, cliquez sur Autres couleurs... La couleur de colonne sélectionnée apparaîtra dans la fenêtre aperçu.

Pour afficher uniquement l'histogramme, mais pas la valeur dans la cellule, cochez la case Afficher la colonne uniquement.

Pour appliquer une bordure pleine aux barres de l'histogramme, sélectionnez Frontière option Bordure solide, puis choisissez une couleur de bordure.

Pour rendre les colonnes solides ou dégradées, sélectionnez dans la liste Remplir option Remplissage solide ou Remplissage en dégradé.

Pour mettre en forme les colonnes pour les valeurs négatives, cliquez sur Valeurs négatives et axepuis dans la boîte de dialogue Définition des valeurs négatives et de l'axe ajustez les options de couleur de remplissage et de bordure pour les colonnes correspondant aux valeurs négatives. Vous pouvez personnaliser la position et la couleur de l'axe. Après avoir sélectionné les options, cliquez sur D'accord.

La direction de l'histogramme peut être définie dans le champ Direction de la colonne... Par défaut, la valeur y est sélectionnée. par contextecependant, vous pouvez choisir la direction de gauche à droite ou de droite à gauche en fonction de la façon dont vous souhaitez présenter les données.

Formatage des cellules à l'aide du jeu d'icônes

Un ensemble d'icônes est utilisé pour annoter et classer les données en trois à cinq catégories, séparées par un seuil. Chaque icône correspond à une plage de valeurs. Par exemple, dans le jeu d'icônes 3 flèches, une flèche rouge vers le haut correspond à des valeurs élevées, une flèche jaune à la flèche latérale correspond à des valeurs moyennes et une flèche verte vers le bas correspond à des valeurs faibles.

L'exemple présenté ici utilise plusieurs ensembles d'icônes dans le formatage conditionnel.

Vous pouvez choisir d'afficher les icônes uniquement pour les cellules qui remplissent certaines conditions (par exemple, afficher une icône d'avertissement pour les cellules en dessous d'un niveau critique et aucune icône pour les cellules au-dessus de ce niveau). Pour ce faire, lors de la configuration des conditions, masquez les cellules en choisissant l'option Aucune icône de cellule dans la liste déroulante à côté de l'icône souhaitée. En outre, vous pouvez créer vos propres jeux d'icônes, tels qu'une coche verte, un feu de signalisation jaune et un drapeau rouge.

Conseil: Si l'une des cellules sélectionnées contient une formule qui renvoie une erreur, la mise en forme conditionnelle n'est pas appliquée à ces cellules. Pour vous assurer que la mise en forme conditionnelle est appliquée à ces cellules, utilisez la fonction IL Y A ou IFERREUR pour renvoyer une valeur (telle que 0 ou N / A) autre que error.

Formatage rapide

Vous pouvez modifier la portée des champs à partir de la zone Valeurs dans un rapport de tableau croisé dynamique à l'aide du bouton radio Appliquer la règle de mise en forme à.

Formatage avancé

Effectuez l'une des actions suivantes:

Pour ajouter une mise en forme conditionnelle, cliquez sur Créer une règle... Une boîte de dialogue s'ouvre.

Suivez ces étapes pour modifier la mise en forme conditionnelle.
1. Assurez-vous que la zone de liste Afficher les règles de mise en forme pour le rapport de feuille, de tableau ou de tableau croisé dynamique approprié est sélectionné.
  
  Vous pouvez modifier la plage de cellules selon vos besoins. Pour ce faire, appuyez sur le bouton Réduire la boîte de dialogue sur le terrain Cela s'applique àpour masquer temporairement la boîte de dialogue. Sélectionnez ensuite la nouvelle plage de cellules sur la feuille et cliquez sur Développer la boîte de dialogue.
  
  Sélectionnez une règle puis cliquez sur Changer de règle... Une boîte de dialogue s'ouvre Modification de la règle de mise en forme.

Dans la section Appliquer la règle sélectionnez l'une des options suivantes pour modifier la sélection de champ dans la zone de valeurs du rapport de tableau croisé dynamique:

pour sélectionner les champs par sélection, sélectionner seulement ces cellules;

sélectionner des champs par le champ correspondant, sélectionner toutes les cellules<поле значения> avec les mêmes champs;

pour sélectionner des champs par champ de valeur, sélectionner toutes les cellules<поле значения> .

En groupe Sélectionnez le type de règle sélectionner un article Mettre en forme toutes les cellules en fonction de leurs valeurs.

Dans la section Changer la description de la règle dans la liste Format de style sélectionner un article Jeu d'icônes.

Sélectionnez un ensemble d'icônes. L'ensemble par défaut est 3 feux tricolores sans encadrement... Le nombre d'icônes, les opérateurs de comparaison par défaut et les seuils pour chaque icône peuvent être différents pour différents jeux d'icônes.

Vous pouvez modifier les opérateurs de comparaison et les seuils. Les tailles de plage par défaut pour toutes les icônes sont les mêmes, mais vous pouvez les modifier en fonction de vos besoins spécifiques. Assurez-vous que les seuils sont dans une séquence logique du plus haut au plus bas, de haut en bas.

Effectuez l'une des actions suivantes:
- Formatage d'une valeur numérique, d'une valeur de date ou d'une valeur d'heure. Sélectionner un article Nombre.
  
  Formatage en pourcentage. Sélectionner un article Pour cent.
  
  Les valeurs valides vont de 0 (zéro) à 100. N'entrez pas de signe de pourcentage.
  
  Utilisez des pourcentages si vous souhaitez rendre toutes les valeurs proportionnellement, car la distribution des valeurs dans ce cas est proportionnelle.
  
  Formatage en centile. Sélectionner un article Centile... Les valeurs de centile valides sont comprises entre 0 (zéro) et 100.
  
  Utilisez des centiles si vous souhaitez visualiser un groupe de hauts (par exemple, le 20e centile supérieur) avec une icône et un groupe de bas (par exemple, le 20e centile inférieur) avec un autre, car ils correspondent à des valeurs extrêmes qui peuvent déplacer la visualisation des données.
  
  Mise en forme du résultat d'une formule. Sélectionner un article Formulepuis entrez des formules dans chaque champ Valeur.
  - La formule doit renvoyer un nombre, une date ou une heure.
    
    Commencez à saisir une formule avec un signe égal (\u003d).
    
    Une formule non valide empêchera l'application du formatage.
    
    Vérifiez la formule pour vous assurer qu'elle ne renvoie pas de valeur d'erreur.

Pour que la première icône corresponde aux valeurs inférieures et la dernière icône aux valeurs supérieures, sélectionnez l'option Ordre inverse des icônes.

Pour afficher uniquement l'icône et non la valeur dans la cellule, sélectionnez l'option Afficher uniquement l'icône.

Remarques:

Vous devrez peut-être ajuster la largeur des colonnes pour accueillir les icônes.

La taille de l'icône affichée dépend de la taille de la police utilisée dans cette cellule. L'augmentation de la taille de la police augmente proportionnellement la taille de l'icône.

Formatage de cellules contenant du texte, des valeurs numériques, une date ou une heure

Pour faciliter la recherche de cellules spécifiques, vous pouvez les mettre en forme à l'aide de l'opérateur de comparaison. Par exemple, dans une liste d'inventaire triée par catégorie, vous pouvez mettre en évidence en jaune les produits qui en ont moins de 10. Dans la liste récapitulative d'un magasin de détail, on peut distinguer tous les magasins avec plus de 10% de chiffre d'affaires, avec des ventes de moins de 100 000 $ et avec la région Sud-Est.

Les exemples fournis ici illustrent les critères de mise en forme conditionnelle intégrés tels que supérieur à et% meilleur. Les villes de plus de 2 000 000 habitants sont surlignées en vert, et les 30% des températures maximales moyennes mensuelles les plus élevées sont surlignées en orange.

Remarque: Vous ne pouvez pas appliquer la mise en forme conditionnelle aux champs de la zone Valeur d'un rapport de tableau croisé dynamique par texte ou par date (uniquement par des nombres).

Formatage rapide

Vous pouvez modifier la portée des champs à partir de la zone Valeurs dans un rapport de tableau croisé dynamique à l'aide du bouton radio Appliquer la règle de mise en forme à.

Si vous souhaitez regarder une vidéo sur ces méthodes, voir Vidéo: mise en forme conditionnelle du texte et vidéo: mise en forme conditionnelle de la date.

Formatage avancé

Sélectionnez une ou plusieurs cellules dans une plage, un tableau ou un rapport de tableau croisé dynamique.

Sous l'onglet Accueil, dans le groupe Styles, cliquez sur la flèche en regard du bouton Mise en forme conditionnelle, puis cliquez sur Gérer les règles. Une boîte de dialogue s'ouvre Gestionnaire de règles de mise en forme conditionnelle.

Effectuez l'une des actions suivantes:

Pour ajouter une mise en forme conditionnelle, cliquez sur Créer une règle... Une boîte de dialogue s'ouvre.

Suivez ces étapes pour modifier la mise en forme conditionnelle.
1. Assurez-vous que la zone de liste Afficher les règles de mise en forme pour le rapport de feuille, de tableau ou de tableau croisé dynamique approprié est sélectionné.
  
  Vous pouvez modifier la plage de cellules selon vos besoins. Pour ce faire, appuyez sur le bouton Réduire la boîte de dialogue sur le terrain Cela s'applique à Développer la boîte de dialogue.
  
  Sélectionnez une règle puis cliquez sur Changer de règle... Une boîte de dialogue s'ouvre Modification de la règle de mise en forme.

Dans la section Appliquer la règle sélectionnez l'une des options suivantes pour modifier la sélection de champ dans la zone de valeurs du rapport de tableau croisé dynamique:

pour sélectionner les champs par sélection, sélectionner seulement ces cellules;

sélectionner des champs par le champ correspondant, sélectionner toutes les cellules<поле значения> avec les mêmes champs;

pour sélectionner des champs par champ de valeur, sélectionner toutes les cellules<поле значения> .

Dans la liste Sélectionnez le type de règle sélectionner un article Mettre en forme uniquement les cellules contenant.

En groupe Changer la description de la règle dans la liste déroulante Mettre en forme uniquement les cellules qui remplissent la condition suivante Effectuez l'une des actions suivantes.

Pour définir le format, cliquez sur le bouton Format... Une boîte de dialogue s'affichera Format de cellule.

Sélectionnez le format de nombre, de police, de bordure ou de remplissage à appliquer si la valeur de la cellule répond à la condition, puis cliquez sur D'accord.

Plusieurs formats peuvent être sélectionnés. Les formats sélectionnés apparaîtront dans la fenêtre aperçu.

Formatage rapide

Vous pouvez modifier la portée des champs à partir de la zone Valeurs dans un rapport de tableau croisé dynamique à l'aide du bouton radio Appliquer la règle de mise en forme à.

Formatage avancé

Formatage rapide

Vous pouvez modifier la portée des champs à partir de la zone Valeurs dans un rapport de tableau croisé dynamique à l'aide du bouton radio Appliquer la règle de mise en forme à.

Formatage avancé

Formatage uniquement des valeurs uniques ou dupliquées

Remarque: Vous ne pouvez pas appliquer de mise en forme conditionnelle aux champs de la zone Valeurs d'un rapport de tableau croisé dynamique avec des valeurs uniques ou en double.

L'exemple ici utilise la mise en forme conditionnelle dans la colonne Enseignant pour mettre en évidence les enseignants enseignant plus d'une matière (les noms en double sont surlignés en rose). Les valeurs de note qui n'apparaissent qu'une seule fois dans la colonne Note sont surlignées en vert.

Formatage rapide

Formatage avancé

Créez vos propres règles de mise en forme conditionnelle

Si aucune des options ci-dessus ne fonctionne pour vous, vous pouvez créer votre propre règle de mise en forme conditionnelle en quelques étapes simples.

Sélection de cellules pour la mise en forme à l'aide d'une formule

Si vous ne disposez pas des options souhaitées lors de la création de votre propre règle de mise en forme conditionnelle, vous pouvez utiliser une formule booléenne pour définir les conditions de mise en forme. Par exemple, vous pouvez comparer les valeurs des cellules sélectionnées avec le résultat renvoyé par une fonction ou évaluer les données des cellules en dehors de la plage sélectionnée qui peuvent se trouver sur une autre feuille du même classeur. La formule doit renvoyer Vrai ou Faux (1 ou 0), mais vous pouvez utiliser la logique conditionnelle pour combiner un ensemble de formats conditionnels appropriés, tels que des couleurs différentes pour chacun des petits ensembles de valeurs de texte (par exemple, les noms de catégories de produits).

Remarque: Vous pouvez saisir des références de cellule dans une formule. Pour ce faire, sélectionnez des cellules directement sur la feuille ou d'autres feuilles. Lorsque vous sélectionnez des cellules dans des feuilles, des références de cellule absolues sont créées. Pour qu'Excel ajuste les références de cellule dans la plage sélectionnée, utilisez des références relatives. Pour plus d'informations, consultez Créer et modifier une référence de cellule et modifier le type de référence: relative, absolue, mixte.

Conseil: Si des cellules contiennent une formule qui renvoie une erreur, la mise en forme conditionnelle n'est pas appliquée à ces cellules. Pour résoudre ce problème, utilisez dans la formule fonction EST ou fonction IFERRORpour renvoyer une valeur que vous spécifiez (telle que 0 ou N / A) au lieu d'une valeur d'erreur.

Dans l'onglet Accueil en groupe modes cliquez sur la flèche à côté du bouton et sélectionnez Gestion des règles.

Une boîte de dialogue s'ouvre Gestionnaire de règles de mise en forme conditionnelle.

Effectuez l'une des actions suivantes:

Pour ajouter une mise en forme conditionnelle, cliquez sur Créer une règle... Une boîte de dialogue s'ouvre.

Suivez ces étapes pour modifier la mise en forme conditionnelle.
1. Assurez-vous que la zone de liste Afficher les règles de mise en forme pour le rapport de feuille, de tableau ou de tableau croisé dynamique approprié est sélectionné.
  
  Vous pouvez modifier la plage de cellules selon vos besoins. Pour ce faire, appuyez sur le bouton Réduire la boîte de dialogue sur le terrain Cela s'applique àpour masquer temporairement la boîte de dialogue, puis sélectionnez une nouvelle plage de cellules sur cette feuille ou d'autres feuilles et cliquez sur Développer la boîte de dialogue.
  
  Sélectionnez une règle puis cliquez sur Changer de règle... Une boîte de dialogue s'ouvre Modification de la règle de mise en forme.

Dans la section Appliquer la règle effectuez l'une des opérations suivantes pour modifier la sélection de champ dans la zone de valeurs d'un rapport de tableau croisé dynamique:

Pour définir une zone par une sélection , Cliquez sur Cellules sélectionnées.

Pour déterminer la zone par le champ correspondant , Cliquez sur à toutes les cellules contenant des valeurs<поле значения> .

Pour définir une zone par le champ "Valeur" , sélectionnez à toutes les cellules contenant des valeurs<поле значения> pour<строка> .

En groupe Sélectionnez le type de règle appuie sur le bouton Utilisez une formule pour définir des cellules formatées.

La vidéo suivante montre les bases de l'utilisation de formules mises en forme conditionnelles.

Copier et coller la mise en forme conditionnelle

Si vous souhaitez appliquer un style de mise en forme conditionnelle à des données nouvelles ou autres dans la feuille de calcul, vous pouvez le copier à l'aide de la commande Format d'échantillon.

Remarque: Si vous avez utilisé une formule dans une règle qui applique une mise en forme conditionnelle, vous devrez peut-être ajuster les références de cellule dans cette formule après avoir collé cette mise en forme. Pour plus d'informations, consultez l'article Basculer entre les liens relatifs, absolus et mixtes.

Recherche de cellules avec mise en forme conditionnelle

Si une feuille contient une mise en forme conditionnelle, vous pouvez trouver rapidement ces cellules pour copier, modifier ou supprimer le format conditionnel. Pour rechercher des cellules avec une mise en forme conditionnelle spécifique ou toutes les cellules avec une mise en forme conditionnelle, utilisez la commande Sélectionnez un groupe de cellules.

Recherche de toutes les cellules avec mise en forme conditionnelle

Rechercher des cellules avec la même mise en forme conditionnelle

Gestion des règles de mise en forme conditionnelle

Lorsque vous utilisez la mise en forme conditionnelle, vous définissez les règles par lesquelles Excel détermine quand appliquer la mise en forme conditionnelle. Pour gérer ces règles, vous devez comprendre l'ordre dans lequel ces règles sont vérifiées et ce qu'il faut faire en cas de plusieurs conflits. Vous devez également comprendre comment le copier-coller affecte la vérification des règles, savoir comment modifier la séquence de cette vérification et quand l'arrêter.

Gestionnaire de règles de mise en forme conditionnelle... (Pour ce faire, sur l'onglet Accueil sélectionnez une commande et sélectionnez Gestion des règles.)

Une boîte de dialogue s'ouvre Gestionnaire de règles de mise en forme conditionnelle.

Lorsque plusieurs règles de mise en forme conditionnelle sont appliquées, leur priorité de validation est déterminée par leur ordre dans cette boîte de dialogue (de haut en bas).

AUJOURD'HUI \u003d B2<СЕГОДНЯ () Cela s'applique à \u003d B2<СЕГОДНЯ()+60

Vers le haut et Vers le bas.

Parfois, plusieurs règles de mise en forme conditionnelle peuvent être appliquées à une plage de cellules. Voici des exemples d'application des règles: d'abord celles qui ne sont pas en conflit les unes avec les autres, puis celles qui sont en conflit.

Si une règle de mise en forme conditionnelle est vérifiée avec le résultat "Vrai", elle est prioritaire sur tout format défini manuellement pour les mêmes cellules sélectionnées. Cela signifie qu'en cas de conflit, la mise en forme conditionnelle est appliquée et la mise en forme manuelle n'est pas appliquée. Lorsque vous supprimez une règle de mise en forme conditionnelle, la mise en forme que vous avez appliquée manuellement à la plage de cellules est conservée.

Gestionnaire de règles de mise en forme conditionnelle

Gestion des règles cochez la case Arrêtez si c'est vrai

Arrêtez si c'est vrai
Cochez ou décochez la case Arrêtez si c'est vrai

Pour une vidéo sur la gestion des règles de mise en forme conditionnelle, voir Vidéo: Gérer la mise en forme conditionnelle.

Effacer la mise en forme conditionnelle

Effacer la mise en forme conditionnelle sur une feuille

Dans l'onglet Accueil cliquez\u003e Supprimer les règles > .

Pour effacer la mise en forme conditionnelle sur une feuille, suivez les instructions ci-dessous.

Partout feuille

Dans l'onglet Accueil cliquez\u003e Supprimer les règles > Supprimer les règles de la feuille entière.

Dans une plage de cellules

Rechercher et supprimer la même mise en forme conditionnelle dans une feuille de calcul

Cliquez sur la cellule avec la mise en forme conditionnelle que vous souhaitez supprimer de la feuille entière.

Dans l'onglet Accueil cliquez sur la flèche à côté du bouton Trouver et mettre en évidence et sélectionnez la commande Sélectionnez un groupe de cellules.

Sélectionnez une option Formats conditionnels.

Sélectionner un article le même en groupe La validation des données... Toutes les cellules avec les mêmes règles de mise en forme conditionnelle seront mises en évidence.

Dans l'onglet Accueil appuyez sur le bouton, sélectionnez l'élément Supprimer les règles, puis - .

Formatage uniquement des premières ou dernières valeurs

Vous pouvez trouver les valeurs maximale et minimale dans une plage de cellules en fonction d'un seuil spécifié. Par exemple, vous pouvez trouver les 5 produits les plus populaires dans un rapport régional, 15% des produits les moins populaires dans une enquête auprès des clients ou les 25 salaires les plus élevés dans le rapport sur le personnel du service.

Sélectionnez les cellules auxquelles vous souhaitez appliquer la mise en forme conditionnelle.

Dans l'onglet Accueil en groupe les tables sélectionner.

Sélectionnez la commande souhaitée, par exemple 10 premiers éléments ou derniers 10%.

Saisissez les valeurs que vous souhaitez utiliser, puis sélectionnez un format (couleur de remplissage, de texte ou de bordure) et cliquez sur D'accord.

Formatage uniquement des valeurs supérieures ou inférieures à la moyenne

Vous pouvez trouver des valeurs supérieures ou inférieures à la moyenne ou à l'écart type dans une plage de cellules. Par exemple, vous pouvez trouver des interprètes dont les performances sont supérieures à la moyenne dans une évaluation annuelle des performances, ou vous pouvez identifier les matériaux produits qui sont inférieurs à deux fois l'écart type.

Sélectionnez les cellules auxquelles vous souhaitez appliquer la mise en forme conditionnelle.

Dans l'onglet Accueil en groupe les tables sélectionner un article Règles de sélection pour la première et la dernière valeur.

Copier et coller le formatage conditionnel Gérer les règles de formatage conditionnel
Lorsque vous utilisez la mise en forme conditionnelle, vous définissez les règles par lesquelles Excel détermine quand appliquer la mise en forme conditionnelle. Pour gérer ces règles, il est important de comprendre l'ordre dans lequel ces règles sont vérifiées et ce qu'il faut faire si plusieurs conflits se produisent. Vous devez également comprendre comment le copier-coller affecte la vérification des règles, savoir comment modifier la séquence de cette vérification et quand l'arrêter.
Modification de l'ordre de vérification des règles de mise en forme conditionnelle

L'ordre dans lequel les règles de mise en forme conditionnelle sont vérifiées - leur priorité - dépend de leur importance relative: plus une règle est élevée dans la liste des règles de mise en forme conditionnelle, plus elle est importante. Cela signifie que si deux règles de mise en forme conditionnelle sont en conflit l'une avec l'autre, la règle la plus élevée dans la liste est appliquée et celle ci-dessous ne l'est pas.

Suppression de la mise en forme conditionnelle

Vous pouvez supprimer la mise en forme conditionnelle des cellules sélectionnées ou de la feuille entière.
Remarque: Vous ne pouvez pas utiliser la mise en forme conditionnelle avec des liens externes vers d'autres livres.

Information additionnelle

Vous pouvez toujours poser une question à la communauté technique d'Excel, demander de l'aide à la communauté Answers ou suggérer une nouvelle fonctionnalité ou une amélioration sur le site Web.

Travailler avec le projet VB (12)
Formatage conditionnel (5)
Listes et gammes (5)
Macros (procédures VBA) (63)
Divers (39)
Excel bugs et pépins (4)

Collection de formules pour la mise en forme conditionnelle

Cet article a compilé une liste de formules pouvant être utilisées dans la mise en forme conditionnelle des cellules, spécifiées à l'aide de la formule:

Excel 2003 : Format-- formule;
Excel 2007-2010 : onglet Accueil-Mise en forme conditionnelle-Créer une règle (nouvelle règle)-Utilisez une formule pour définir des cellules formatées (Utilisez une formule pour déterminer les cellules à mettre en forme)

Vous pouvez en savoir plus sur la mise en forme conditionnelle dans l'article: Concepts de mise en forme conditionnelle et comment le créer

Toutes les conditions sont pour la plage A1: A20. Cela signifie que pour que la condition soit correctement remplie, il est nécessaire de sélectionner la plage A1: A20 (il peut y avoir plus de colonnes), à partir de la cellule A1, puis d'affecter la condition.
Si vous devez sélectionner non pas à partir de la première ligne, mais, par exemple, à partir de la 4ème ligne, vous devrez sélectionner la plage A4: A20 et dans la formule de la condition, indiquer la première cellule de la plage sélectionnée comme critère - A4.

Si vous devez sélectionner avec mise en forme non seulement une cellule spécifique qui satisfait à la condition, mais la ligne entière du tableau basée sur une cellule d'une colonne, alors avant de définir la règle, vous devez sélectionner l'ensemble du tableau dont les lignes doivent être formatées et le lien vers la colonne avec le critère doit être corrigé:
\u003d $ A1 \u003d MAX ($ A $ 1: $ A $ 20)
avec la plage sélectionnée A1: F20 (la plage d'application de la mise en forme conditionnelle), la ligne A7: F7 sera mise en surbrillance s'il y a un nombre maximum dans la cellule A7.

Il peut également être appliqué non pas à une colonne spécifique, mais à une plage entière. Mais dans ce cas, vous devez connaître le principe du déplacement de lien dans les formules afin que les conditions soient appliquées exactement aux cellules requises. Par exemple, si vous définissez la condition pour la plage B1: D10 sous forme de formule: \u003d B1

VALEURS NUMÉRIQUES

Surligner les cellules avec des nombres:
\u003d ESTNOMBRE (A1)
Sélection de cellules avec des nombres, mais sans compter les zéros:
\u003d ET (ISNOMBRE (A1), A1 0)
Surligner les lignes avec une valeur supérieure à 0:
\u003d A1\u003e 0
Mise en évidence des lignes avec une valeur comprise entre 3 et 10:
\u003d ET (A1\u003e \u003d 3; A1
Sélection dans la plage $ A $ 1: $ A $ 20 cellules avec la valeur maximale:
\u003d A1 \u003d MAX ($ A $ 1: $ A $ 20)
Sélection dans la plage $ A $ 1: $ A $ 20 cellules avec la valeur minimale:
\u003d ET (ESTNOMBRE (A1); A1 \u003d MIN ($ A $ 1: $ A $ 20))
Sélection dans la plage $ A $ 1: $ A $ 20 de la cellule avec le deuxième plus grand nombre. Ceux. le nombre 6 sera choisi parmi les nombres 1,2,3,4,5,6,7:
\u003d A1 \u003d GRAND ($ A $ 1: $ A $ 20,2)

VALEURS DE TEXTE

Sélection de cellules avec n'importe quel texte:
\u003d ETEXTE (A1)
Sélectionnez des cellules avec du texte Résultat:
\u003d A1 \u003d "Total"
Sélectionnez les cellules contenant du texte Résultat:
\u003d COUNTIF (A1, "* total *")
\u003d PAS (EOSH (RECHERCHE ("total"; A1)))
Sélectionnez les cellules qui ne contiennent pas de texte Résultat:
\u003d COUNTIF (A1, "* total *") \u003d 0
\u003d EOSH (RECHERCHE ("total"; A1))
Sélectionnez les cellules dont le texte commence par un mot Résultat:
\u003d GAUCHE (A1; 4) \u003d "Total"
Sélectionnez les cellules dont le texte se termine par un mot Résultat:
\u003d DROITE (A1; 4) \u003d "Total"

DATE HEURE

Mise en évidence de la date actuelle:
\u003d A1 \u003d AUJOURD'HUI ()
Sélection d'une cellule avec une date supérieure à la date actuelle:
\u003d A1\u003e AUJOURD'HUI ()
Sélection d'une cellule avec une date qui viendra dans une semaine:
\u003d A1 \u003d AUJOURD'HUI () + 7
Surligner les cellules avec les dates du mois en cours (toute année):
\u003d MOIS (A1) \u003d MOIS (AUJOURD'HUI ())
Mettre en évidence les cellules avec les dates du mois en cours de l'année en cours:
\u003d ET (MOIS (A1) \u003d MOIS (AUJOURD'HUI ()); ANNÉE (A1) \u003d ANNÉE (AUJOURD'HUI ()))
ou
\u003d TEXTE (A1, "AAAAMM") \u003d TEXTE (AUJOURD'HUI (), "AAAAMM")
Sélection des cellules avec jours de congé:
\u003d JOUR DE SEMAINE (A1, 2)\u003e 5
Mettre en évidence les cellules avec les jours de la semaine:
\u003d JOUR DE LA SEMAINE (A1, 2)
Sélection des cellules incluses dans la période (intervalle) de dates spécifiée:
\u003d ET ($ A1\u003e DATE (2015; 9; 1); $ A1
AUTRE
Mettre en évidence les différences dans les cellules par condition:
\u003d A1<>B1 $
Sélectionnez une cellule si la cellule de la colonne suivante (B) de la même ligne est inférieure:
\u003d A1\u003e B1
Surligner les lignes en couleur une par une:
\u003d REST (RANG (), 2)
Mise en évidence des lignes si la valeur de cellule de la colonne A est présente dans la plage $ F $ 1: $ H $ 5000:
\u003d COUNTIF ($ F $ 1: $ H $ 5000; A1)
Mise en évidence des lignes si la valeur de cellule de la colonne A est manquante dans la plage $ F $ 1: $ H $ 5000:
\u003d COUNTIF ($ F $ 1: $ H $ 5000; A1) \u003d 0
Mettre en évidence une cellule si sa valeur dans la plage A1: A20 est la deuxième consécutive:
\u003d COUNTIF ($ A $ 1: $ A1; A1) \u003d 2
Sélection de cellules contenant des erreurs ( #VALEUR!; # N / A; #LIEN! etc.). En plus d'identifier simplement les cellules contenant des erreurs, vous pouvez l'utiliser lorsque vous devez masquer des valeurs erronées dans les cellules (en attribuant à la couleur de police la même couleur que la couleur de remplissage):
\u003d ISERREUR (A)
Mise en évidence des cellules non vides dans la colonne A:
\u003d $ A1 ""

L'article vous 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 ":" easyOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easyOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectlidedistance2": 120, "texteffecteasing2": "easyOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500 textcss ":" affichage: bloc; remplissage: 12px; alignement du texte: gauche; "," textbgcss ":" affichage: bloc; position: absolue; haut: 0px; gauche: 0px; largeur: 100%; hauteur: 100% ; couleur de fond: # 333333; opacité: 0,6; filtre: a lpha (opacity \u003d 60); "," titlecss ":" display: block; position: relative; police: bold 14px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", sans-serif, Arial; color: #fff; "," descriptioncss ":" display: block; position: relative; police: 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 ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40))

La mise en forme conditionnelle est l'un des outils EXCEL les plus utiles. Savoir comment l'utiliser peut faire gagner beaucoup de temps et d'efforts à l'utilisateur.

Commençons à explorer Mise en forme conditionnellede la vérification des valeurs numériques à plus / moins / égal / entre par rapport aux constantes numériques.

Ces règles sont utilisées assez souvent, donc dans EXCEL 2007, elles sont placées dans un menu séparé Règles de sélection de cellule.

Ces règles sont également disponibles via le menu Accueil / Styles / Mise en forme conditionnelle / Créer une règle, formater uniquement les cellules contenant.

Considérons plusieurs tâches:

COMPARAISON AVEC UNE VALEUR CONSTANTE (CONSTANTE)

Tache 1 A1: D1 avec le chiffre 4.

entrer dans la gamme A1: D1 sens 1, 3, 5, 7
sélectionnez cette plage;
Mise en forme conditionnelle sur la valeur Plus petite ();
dans le champ gauche de la fenêtre qui apparaît, entrez 4 - nous verrons immédiatement le résultat de l'application Mise en forme conditionnelle.
Cliquez sur OK.

Tache 1.

COMPARAISON AVEC LA VALEUR DANS LA CELLULE (RÉFÉRENCE ABSOLUE)

Compliquons un peu la tâche précédente: au lieu de saisir directement la valeur (4) comme critère, nous entrerons une référence à la cellule qui contient la valeur 4.

Tâche2... Comparer les valeurs de la plage A1: D1 avec le numéro de la cellule A2 .

entrer dans la cellule A2 Numéro 4;
sélectionnez la gamme A1: D1 ;
applicable à la gamme sélectionnée Mise en forme conditionnelle sur la valeur Plus petite (Accueil / Styles / Mise en forme conditionnelle / Règles de mise en évidence des cellules / Moins);
dans la marge gauche de la fenêtre qui apparaît, entrez la référence de cellule A2 en cliquant sur le bouton situé à droite de la fenêtre (EXCEL utilise par défaut le lien 2 $ AU ).

Cliquez sur OK.

Par conséquent, toutes les valeurs de la plage sélectionnée UNE 1: ré 1 sera comparé à une cellule 2 $ AU ... Ces valeurs de UNE 1: ré 1 qui sont plus petits UNE 2 sera mis en évidence avec le remplissage d'arrière-plan de la cellule.

Le résultat peut être vu dans le fichier d'exemple sur la feuille Tâche2.

Pour voir comment la règle de mise en forme que vous venez de créer est configurée, cliquez sur ; puis double-cliquez sur la règle ou cliquez sur le bouton Changer de règle... En conséquence, vous verrez la boîte de dialogue ci-dessous.

COMPARAISON CHAÎNE / COLONNE PAIRE (RÉFÉRENCES RELATIVES)

entrer dans les cellules de la plage UNE 2: ré 2 des valeurs numériques (vous pouvez les considérer comme des critères);
sélectionnez la gamme UNE 1: ré 1 ;
applicable à la gamme sélectionnée Mise en forme conditionnelle sur la valeur Plus petite (Accueil / Styles / Mise en forme conditionnelle / Règles de mise en évidence des cellules / Moins)
dans la marge gauche de la fenêtre qui apparaît, entrez la référence relative à la cellule UNE 2 (c'est-à-dire juste A2 ou lien mixte 2 $ AU ). Assurez-vous qu'il n'y a pas de signe $ devant la colonne A.

Maintenant, chaque valeur de la ligne 1 sera comparé à la valeur correspondante de la chaîne 2 dans la même colonne! Les valeurs 1 et 5 seront mises en évidence, car ils sont inférieurs à 2 et 6, respectivement, situés dans la rangée 2.

Le résultat peut être vu dans le fichier d'exemple sur la feuille Tâche3.

Attention!En cas d'utilisation de liens relatifs dans les règles Mise en forme conditionnelle il est nécessaire de garder une trace de quelle cellule est active au moment de l'appel de l'outil Mise en forme conditionnelle .

Note d'accompagnement: Sur l'importance de geler la cellule active lors de la création de règles de mise en forme conditionnelle avec des liens relatifs

Lors de la création de liens relatifs dans les règles Mise en forme conditionnelle, ils "se fixent" sur une cellule qui est actif au moment de l'appel de l'outil Mise en forme conditionnelle.

POINTE: Pour connaître l'adresse de la cellule active (il en est toujours une sur la feuille), vous pouvez regarder dans le champ Nom (situé à gauche de). Dans la tâche 3, après avoir sélectionné la plage A1: D1 (le bouton de la souris doit être relâché), dans, l'adresse de la cellule active y sera affichée A1 ou ré 1 ... Pourquoi 2 options sont-elles possibles et quelle est la différence pour les règles de mise en forme conditionnelle?

Examinons de près la deuxième étape de la résolution du problème précédent3 - sélectionner une plage UNE 1: ré 1 ... La plage spécifiée peut être sélectionnée de deux manières: sélectionnez une cellule A1 , puis, sans relâcher le bouton de la souris, sélectionnez toute la plage, en vous déplaçant vers la droite D1 ; ou, sélectionnez une cellule D1 , puis, sans relâcher le bouton de la souris, sélectionnez toute la plage, en vous déplaçant vers la gauche A1 ... La différence entre ces deux méthodes est fondamentale: dans le premier cas, après avoir terminé la sélection de la plage, la cellule active sera A1 , et dans le second ré 1 !

Voyons maintenant comment cela affecte la règle de mise en forme conditionnelle du lien relatif.

Si nous avons sélectionné la plage de la première manière, alors en entrant dans la règle Mise en forme conditionnelle référence de cellule relative A2 , nous avons ainsi dit à EXCEL de comparer la valeur de la cellule active A1 avec une valeur en A2 ... Car la règle s'applique à la plage UNE 1: ré 1 puis B 1 sera comparé à À 2 HEURES etc. La tâche sera résolue correctement.

Si lors de la création d'une règle Mise en forme conditionnelle la cellule était active D1 , alors sa valeur sera comparée à la valeur de la cellule A2 ... Et la valeur de UNE 1 va maintenant comparer avec la valeur de la cellule XFB2 (ne trouve pas les cellules à gauche UNE 2 , EXCEL sélectionnera la cellule la plus récente XFD pour C1 , puis l'avant-dernier pour B 1 et enfin XFB2 pour A1 ). Vous pouvez vérifier cela en regardant la règle créée:

sélectionnez une cellule A1 ;
cliquez sur Accueil / Styles / Formatage conditionnel / Gestion des règles;
maintenant vous pouvez voir que par rapport à la gamme $ A $ 1: $ D $ 1 la règle s'applique Valeur de la cellule <XFB2 (ou<XFB 2 $ ).

EXCEL affiche une règle de mise en forme ( Valeur de la cellule ) par rapport à la cellule active, i.e. à A1 ... Une règle correctement appliquée, dans notre cas, ressemble à ceci:

SÉLECTION DE LIGNE

SÉLECTION DE CELLULES AVEC NOMBRES

PRIORITÉ DES RÈGLES

Pour vérifier les règles appliquées à une plage, utilisez Gestionnaire de règles de mise en forme conditionnelle(Accueil / Styles / Formatage conditionnel / Gestion des règles).

Lorsque deux règles ou plus sont appliquées à une cellule Mise en forme conditionnelle, la priorité de traitement est déterminée par l'ordre dans lequel ils sont répertoriés dans Gestionnaire de règles de mise en forme conditionnelle... La règle de la liste ci-dessus a une priorité plus élevée que la règle de la liste ci-dessous. Les nouvelles règles sont toujours ajoutées en haut de la liste et ont donc une priorité plus élevée, mais l'ordre des règles peut être modifié dans la boîte de dialogue à l'aide des boutons fléchés Vers le haut et Vers le bas.

Par exemple, il y a un nombre 9 dans une cellule et deux règles lui sont appliquées Valeur de cellule\u003e 6 (format spécifié: fond rouge) et Valeur de cellule\u003e 7(format défini: fond vert), voir l'image ci-dessus. Car la règle Valeur de cellule\u003e 6 (format spécifié: fond rouge) est plus élevé, alors il a une priorité plus élevée, et donc la cellule avec une valeur de 9 aura un fond rouge. Sur le Case à cocher Arrêter si vrai vous pouvez l'ignorer, il est installé pour une compatibilité descendante avec les versions précédentes d'EXCEL, qui ne prennent pas en charge l'application simultanée de plusieurs règles de mise en forme conditionnelle. Bien qu'il puisse être utilisé pour remplacer une ou plusieurs règles lors de l'utilisation de plusieurs règles définies pour une plage en même temps (lorsqu'il n'y a pas de conflit entre les règles). Vous pouvez en savoir plus.

Si une règle de mise en forme s'applique à une plage de cellules, elle prévaut sur la mise en forme manuelle. Le formatage manuel peut être effectué à l'aide de la commande Format du groupe Cellules de l'onglet Accueil. La mise en forme manuelle est conservée lorsque vous supprimez une règle de mise en forme conditionnelle.

FORMAT CONDITIONNEL et FORMAT CELLULAIRE

La mise en forme conditionnelle ne modifie pas le format appliqué à cette cellule (onglet groupe Police principale, ou cliquez sur CTRL + MAJ + F). Par exemple, si la cellule a un remplissage rouge dans la cellule et que la règle de mise en forme conditionnelle est déclenchée, selon laquelle le remplissage de cette cellule doit être jaune, alors le remplissage de la mise en forme conditionnelle «gagne» - la cellule sera mise en évidence en jaune. Bien que le remplissage de mise en forme conditionnelle soit appliqué sur le remplissage de format de cellule, il ne le modifie pas (l'annule) et il n'est tout simplement pas visible.

RÈGLES DE FORMATAGE CONDITIONNELLES DE DÉBOGAGE

Pour tester si la règle de mise en forme conditionnelle est exécutée correctement, copiez la formule de la règle dans n'importe quelle cellule vide (par exemple, la cellule à droite de la cellule de mise en forme conditionnelle). Si la formule renvoie TRUE, la règle est déclenchée, si FALSE, la condition n'est pas remplie et le formatage de la cellule ne doit pas être modifié.

Revenons au problème 3 (voir la section sur les liens relatifs ci-dessus). À la ligne 4, nous écrivons la formule de la règle de mise en forme conditionnelle \u003d A1

La mise en forme conditionnelle sera appliquée aux colonnes où la formule est VRAI, mais pas FALSE.

UTILISATION DANS LES RÈGLES DES LIENS VERS D'AUTRES FEUILLES

Avant MS Excel 2010 pour les règles Mise en forme conditionnelle vous ne pouviez pas utiliser directement des liens vers d'autres feuilles ou livres. Il était possible de contourner cette limitation en utilisant. Si dans Mise en forme conditionnellevous devez créer, par exemple, une référence de cellule A2 une autre feuille, vous devez d'abord déterminer nom pour cette cellule, puis reportez-vous à ceci nom dans la règle Mise en forme conditionnelle... Comment il est implémenté Voir le fichier d'exemple sur la feuille Lien depuis une autre feuille.

RECHERCHE DE CELLULES AVEC FORMATAGE CONDITIONNEL

dans l'onglet Accueil en groupe Édition cliquez sur la flèche à côté de la commande Trouver et mettre en évidence,

sélectionner un élément dans la liste Mise en forme conditionnelle.

Toutes les cellules pour lesquelles des règles de mise en forme conditionnelle sont définies seront sélectionnées.

AUTRES RÈGLES PRÉDÉFINIES

au menu Accueil / Styles / Mise en forme conditionnelle / Règles de sélection de cellule Les développeurs EXCEL ont créé une variété de règles de formatage.

Afin de ne pas réinventer la roue, examinons de plus près certaines d'entre elles.

Le texte contient ...Donnons un exemple. Laisser la cellule contenir le mot Percer Le texte contient... Si nous écrivons comme critère ré (mettre en évidence les mots contenant une syllabe ré), puis le mot Percer sera mis en évidence.

Regardons maintenant la règle nouvellement créée via le menu Accueil / Styles / Formatage conditionnel / Gestion des règles ...

Comme vous pouvez le voir sur l'image ci-dessus, Mise en forme conditionnelle vous pouvez configurer pour sélectionner non seulement des cellules, contenant certains textes, mais aussi ne contenant pas, commençant par et se terminant en texte spécifique. Aussi, en cas de conditions contient et ne contient pas l'application est possible.

Que la cellule contienne à nouveau le mot Percer... Sélectionnez une cellule et appliquez la règle Le texte contient... Si nous écrivons comme critère r ?, ce mot Percer sera mis en évidence. Critère signifie: mettre en évidence les mots contenant des syllabes re, ra, re, etc. Vous devez comprendre que les mots avec des phrases seront également mis en évidence p2, pm, pQpuisque signe? signifie n'importe quel caractère. Si nous écrivons comme critère ?????? (sélectionnez les mots d'au moins 6 lettres), puis, respectivement, le mot Percer ne sera pas mis en évidence. Vous pouvez, bien sûr, obtenir un résultat similaire à l'aide de formules avec les fonctions MID (), LEFT (), DLSTR (), mais cette approche, vous voyez, est plus rapide.

Dupliquer les valeurs ... Cette règle vous permet de configurer rapidement Mise en forme conditionnelle pour afficher et dupliquer les valeurs. Sous une valeur unique Mise en forme conditionnelle implique une signification non répétitive, i.e. valeur qui n'apparaît qu'une seule fois dans la plage à laquelle la règle est appliquée. Pour mettre en évidence des valeurs uniques (c'est-à-dire toutes les valeurs sans les répéter), voir

Date… La figure ci-dessous montre les critères de sélection pour cette règle. Il faudra beaucoup plus de temps pour obtenir le même résultat avec des formules.

Valeur de la cellule. Cette règle est disponible via le menu. Dans la fenêtre qui apparaît, sélectionnez l'élément formatez les cellules qui contiennent.Le choix des options vous aidera à effectuer la plupart des tâches associées à la mise en évidence des valeurs numériques.

Je vous conseille également de faire attention aux règles suivantes dans le menu Accueil / Styles / Mise en forme conditionnelle / Règles de sélection des première et dernière valeurs.

10 derniers articles.

Tâche4... Soit 21 valeurs, pour plus de commodité. Appliquer la règle 10 derniers articles et mettre en surbrillance 3 valeurs (éléments). Voir exemple de fichier, feuille Tâche4.

Les mots «3 dernières valeurs» désignent les 3 valeurs les plus basses. S'il y a des répétitions dans la liste, toutes les répétitions correspondantes seront sélectionnées. Par exemple, dans notre cas, la troisième valeur la plus basse est 10 à partir du haut. il y a 10 autres répétitions dans la liste (il y en a 6 au total), elles seront également mises en évidence.

En conséquence, les règles appliquées à notre liste: "Dernière 1 valeur", "2 dernières valeurs", ... "6 dernières valeurs" conduiront au même résultat - la sélection de 6 valeurs égales à 10.

L'application de la règle "7 dernières valeurs" entraînera la sélection de toutes les valeurs supplémentaires égales à 11, depuis. La 7e valeur la plus basse est la première valeur à partir du haut, 11.

De même, vous pouvez créer une règle pour mettre en évidence le nombre de valeurs les plus élevées dont vous avez besoin en appliquant la règle 10 premiers éléments.

10% derniers

Considérez une autre règle connexe 10% derniers.

Veuillez noter que dans l'image ci-dessus, la case à cocher "% de la plage sélectionnée" n'est pas cochée. Cette case à cocher est définie manuellement ou lors de l'application de la règle 10% derniers.

Cette règle définit le pourcentage des plus petites valeurs par rapport au nombre total de valeurs de la liste. Par exemple, régler 20% de ce dernier mettra en évidence les 20% des valeurs les plus basses.

Essayons de définir 20% de la dernière dans notre liste de 21 valeurs: six valeurs seront sélectionnées 10 (voir exemple de fichier, feuille Tâche 4)... 10 est la valeur minimale de la liste, donc tous ses doublons seront de toute façon sélectionnés.

En réglant le pourcentage de 1 à 33%, la sélection ne changera pas. Pourquoi? En définissant, par exemple, 33%, nous obtenons que 6,93 valeurs doivent être sélectionnées. Car vous ne pouvez sélectionner qu'un nombre entier de valeurs. La mise en forme conditionnelle arrondit à l'entier le plus proche, en supprimant la partie fractionnaire. Mais à 34%, il faut déjà sélectionner 7,14 valeurs, c'est-à-dire 7, et en tenant compte des répétitions de la valeur 11 après la 10e, 6 + 3 \u003d 9 valeurs seront sélectionnées.

RÈGLES UTILISANT LES FORMULES

La création de règles de mise en forme basées sur des formules n'est limitée que par l'imagination de l'utilisateur. Ici nous ne considérerons qu'un seul exemple, le reste sont des exemples d'utilisation Mise en forme conditionnelle peuvent être trouvés dans ces articles :; ; ; ...

Supposons que vous ayez besoin de mettre en évidence les cellules contenant des valeurs erronées:

Sélectionnez les cellules à appliquer Mise en forme conditionnelle (que ce soit une cellule A1 ).

Appelez l'outil Mise en forme conditionnelle (Accueil / Styles / Mise en forme conditionnelle / Créer une règle)

Veuillez sélectionner Utilisez une formule pour définir des cellules formatées

Dans le champ " Mettre en forme les valeurs pour lesquelles la formule suivante est vraie»Entrez \u003d EOSH (A1) - si nous voulons que les cellules contenant des valeurs erronées soient mises en évidence, c.-à-d. sera mis en évidence #VALUE!, #LINK!, # DIV / 0!, #NUM!, #NAME? ou # VIDE! (sauf # N / A)

Sélectionnez le format souhaité, par exemple, la couleur de remplissage rouge.

Le même résultat peut être obtenu de manière différente:

Appelez l'outil Mise en forme conditionnelle (Accueil / Styles / Mise en forme conditionnelle / Créer une règle)

Mettre l'élément en surbrillance Mettre en forme uniquement les cellules contenant;

Dans la section Formatez uniquement les cellules qui remplissent la condition suivante: dans la liste déroulante la plus à gauche, sélectionnez Les erreurs.

commentaires

Tatiana (non vérifié)

Tatiana (non vérifié)

Créateur

Moi encore. (non vérifié)

Créateur

Tatiana (non vérifié)

Créateur

Alexander 555 (non vérifié)

Créateur

Chaque responsable, comptable ou même l'utilisateur le plus ordinaire a rencontré à plusieurs reprises les excellentes fonctionnalités d'Excel 2010, qui vous permettent de travailler rapidement et facilement avec des tableaux. Cette version du programme dispose de fonctionnalités avancées de mise en forme conditionnelle des tableaux croisés dynamiques, ce qui améliore l'ensemble du flux de travail.

Nous avons une fonctionnalité de visualisation qui vous permet d'utiliser des histogrammes, diverses icônes et toutes sortes d'échelles de couleurs dans les cellules. Vous pouvez désormais créer facilement une fenêtre de style console de contrôle pour faciliter la recherche d'informations. De plus, ce type de mise en forme a maintenant commencé à être appliqué avec succès dans les tableaux croisés dynamiques, plus précisément à leur structure (et pas seulement aux données). La méthode décrite ci-dessous convient même à ceux qui vont simplement acheter une nouvelle machine à laver et choisir un modèle.

Donnons un exemple simple de tableau croisé dynamique décrivant les volumes de ventes d'un produit par région:

Maintenant, sur la base de ces données, nous allons créer un rapport graphique des volumes de ventes pour chaque période afin de faciliter la perception des informations. Naturellement, vous pouvez utiliser un graphique croisé dynamique, mais la mise en forme conditionnelle fonctionne bien dans Excel 2010.

L'option la plus simple consiste à utiliser des échelles de couleurs. Pour ce faire, sélectionnez le champ "Volume des ventes", couvrant toutes les périodes. Il reste à ouvrir l'onglet "Accueil", où l'on appuie sur le bouton "Mise en forme conditionnelle" (si vous utilisez soudainement la version anglaise, alors cette fonction s'appelle "Mise en forme conditionnelle"). Déplacez le curseur sur "Histogrammes".

L'ensemble disponible de remplissages dégradés et solides ressemble essentiellement à un histogramme horizontal standard. Et si nous utilisons maintenant le filtrage des données de n'importe quel graphique dans la portée du filtre, nous remarquerons un changement dynamique dans les histogrammes.

Excel 2010 dispose déjà d'un nombre important de scripts de mise en forme prêts à l'emploi qui peuvent considérablement gagner du temps. Pour créer les histogrammes décrits ci-dessus, en passant, le programme utilise un algorithme qui détermine indépendamment les valeurs maximales et minimales de la plage de cellules sélectionnées et ne les formate qu'ensuite en fonction des informations traitées.

Donc - des scripts prêts à l'emploi qui peuvent aider dans la plupart des situations:

- les 10 premiers éléments;

- les 10 derniers éléments;

- les 10 premiers%;

- les 10 derniers%;

- plus que la moyenne;

- moins que la moyenne.

La suppression de la mise en forme conditionnelle déjà utilisée dans Excel 2010 se produit comme suit: dans le tableau croisé dynamique, allez dans l'onglet "Accueil", cliquez sur "Mise en forme conditionnelle", puis sur "Styles", et dans le menu déroulant utilisez la commande "Supprimer les règles" - "Supprimer les règles de ce tableau croisé dynamique »(en anglais -« Clear Rules »et« Clear Rules from this PivotTable »).

Cependant, vous pouvez facilement créer vos propres règles de mise en forme.

Ce tableau est une version compliquée du premier, passons donc directement à l'exemple. Suivi des ventes et des revenus par heure. Nous utiliserons la mise en forme conditionnelle d'Excel 2010 pour accélérer la recherche de correspondances et de différences. Nous sélectionnons "Volume des ventes". De plus, selon la procédure standard, nous activons le script ("Principal" - "Mise en forme conditionnelle"), mais nous choisissons non pas l'option prête à l'emploi, mais la fonction "Créer une règle" (ou "Nouvelle règle").

C'est ici que vous pouvez déterminer les cellules qui seront appliquées au formatage conditionnel dans Excel 2010, le type de règle utilisée et, en fait, les options de mise en forme. Les cellules sont définies en premier, et il y a un choix assez simple ici:

- sélectionné ("Cellules sélectionnées");

- inclus dans la colonne "Ventes" ("Toutes les cellules affichant" Sales_Amount "Values"), y compris les sous-totaux et les totaux. Cette option, en passant, est bien adaptée pour analyser les données qui nécessitent la détermination de la moyenne, du pourcentage ou d'autres valeurs, d'une manière ou d'une autre, sont différents niveaux de la même valeur;

- inclus dans la catégorie "Volume des ventes" uniquement pour "Marché des ventes" ("Toutes les cellules affichant les valeurs" Sales_Amount "pour" Marché ""). Cette option exclut complètement les totaux généraux et les sous-totaux, ce qui est pratique pour analyser certaines valeurs individuelles.

Notez que les commandes "Volume des ventes", "Marché des ventes" lors de la création des règles changent en fonction des feuilles de calcul disponibles.

Selon notre exemple, l'option la plus rentable est "3", donc l'option suivante est utilisée:

Lors du choix d'une règle (rubrique «Sélectionner une règle» ou «Sélectionner un type de règle»), nous indiquons exactement celle qui répond à nos exigences.

Cela pourrait être:

- «Formater les cellules en fonction de leurs valeurs» («Formater toutes les cellules en fonction de leurs valeurs»). Utilisé pour mettre en forme les cellules qui correspondent à la plage de valeurs utilisées. Idéal pour identifier une grande variété de variances lorsqu'il s'agit d'un vaste ensemble de données.

- "Formater les cellules contenant" ("Formater uniquement les cellules qui contiennent"). Formate les cellules qui correspondent aux conditions appropriées. Dans ce cas, la comparaison des valeurs des cellules formatées avec les cellules normales n'est pas effectuée. Utilisé pour comparer un ensemble de données commun avec une caractéristique précédemment spécifiée.

- "Formatage des première et dernière valeurs" ("Formater uniquement les valeurs classées en haut ou en bas").

- "Mettre en forme les valeurs inférieures ou supérieures à la moyenne" ("Mettre en forme uniquement les valeurs supérieures ou inférieures à la moyenne").

- "Utiliser une formule pour déterminer les cellules à formater" ("Utiliser une formule pour déterminer les cellules à formater"). Ici, les conditions de mise en forme conditionnelle sont basées sur une formule spécifiée par l'utilisateur lui-même. Si la valeur de la cellule (parmi celles substituées dans la formule) est fournie avec la valeur «true», la mise en forme est appliquée à la cellule. Dans le cas de false, aucune mise en forme n'est appliquée.

L'utilisation d'histogrammes, de jeux d'icônes et d'échelles de couleurs n'est possible que lorsque le formatage des cellules sélectionnées est basé sur les valeurs qui y sont entrées. Pour ce faire, définissez le premier bouton radio sur «Formater toutes les cellules en fonction de leurs valeurs». Un ensemble d'icônes peut être utilisé pour indiquer les zones problématiques, ce qui est également bien adapté à ce scénario.

Eh bien, il reste à déterminer les paramètres exacts de notre mise en forme. C'est là que la section «Modifier la description de Ruie» est utile. Pour ajouter des icônes aux cellules problématiques, nous utilisons le menu déroulant Style de format et sélectionnons Jeux d'icônes.

Dans la liste "Style d'icône", il reste à sélectionner la valeur "3 caractères". C'est bien si le tableau existant ne peut pas être entièrement coloré. En conséquence, nous devrions obtenir ce qui suit dans la fenêtre:

Avec cette configuration, Excel ajoutera automatiquement des icônes aux cellules, tout en suivant la fonction:

\u003e \u003d 67,\u003e \u003d 33 et<33.

Cela distribuera les icônes en 3 catégories à la fois, ce qui facilitera visuellement l'obtention d'informations lors de l'utilisation de la table.

Ajoutons que dans chaque cas les valeurs limites peuvent être facilement modifiées, et dans l'exemple les valeurs "par défaut" sont simplement utilisées.

Il ne reste plus qu'à appliquer nos règles à la table. En conséquence, cela changera comme ceci:

Il reste à appliquer la même règle à la colonne "Revenu par heure".

Mais ne vous précipitez pas pour fermer l'exemple tout de suite - il vaut mieux l'analyser. Après tout, tout gestionnaire plus ou moins expérimenté remarquera immédiatement la relation entre le revenu horaire et le revenu total. Par exemple, un vendeur à Dallas déterminera qu'il a le volume de ventes le plus bas, même si son revenu horaire est le plus élevé. Cela conduit à la conclusion que pour son marché, le revenu horaire est trop élevé.

Son collègue de New York, quant à lui, a une situation différente. Avec son revenu total élevé, le revenu horaire est minime. Et c'est déjà un problème, malgré le grand écart en termes de revenu total.

Quelle était la dernière explication? Juste pour illustrer à quel point la mise en forme conditionnelle dans Excel 2010 facilite la compréhension des différentes informations. Avec les bonnes compétences en création de filtres de formatage, vous pouvez obtenir rapidement les informations dont vous avez besoin.

Mise en forme conditionnelle dans Excel. Formation au formatage conditionnel dans Excel avec des exemples

Règles de sélection de cellule

Règles de sélection pour la première et la dernière valeur

Créer des règles

Gestion des règles

Information additionnelle

Collection de formules pour la mise en forme conditionnelle

COMPARAISON AVEC UNE VALEUR CONSTANTE (CONSTANTE)

COMPARAISON AVEC LA VALEUR DANS LA CELLULE (RÉFÉRENCE ABSOLUE)

COMPARAISON CHAÎNE / COLONNE PAIRE (RÉFÉRENCES RELATIVES)

SÉLECTION DE LIGNE

SÉLECTION DE CELLULES AVEC NOMBRES

PRIORITÉ DES RÈGLES

FORMAT CONDITIONNEL et FORMAT CELLULAIRE

RÈGLES DE FORMATAGE CONDITIONNELLES DE DÉBOGAGE

UTILISATION DANS LES RÈGLES DES LIENS VERS D'AUTRES FEUILLES

RECHERCHE DE CELLULES AVEC FORMATAGE CONDITIONNEL

AUTRES RÈGLES PRÉDÉFINIES

RÈGLES UTILISANT LES FORMULES

commentaires

Lire aussi

Tarif MTS "Tablet Mini": description

Pneus et jantes pour Nissan X-Trail, dimensions de jantes pour Nissan X-Trail

Internet mobile et communication cellulaire en Crimée - Itinérance Beeline, MTS, Megafon, Tele2

LA CLOCHE