When you have some data to analyze, do you gather your data into a spreadsheet, format the sheet, and then stare for hours at the numbers wondering what they mean? No! You create a chart that represents the data. While a chart can help you see the overall results of the data, how do you examine how individual data-points fall into the overall scheme of things? Conditional formatting can help you focus in on the data-points making up your data set. LibreOffice provides us with many different conditional formatting options, allowing us to find a setup that will work for many different types of data. We discussed one of the conditional methods back in part 43 under statistical functions (Full Circle 91). Today, I want to add three more conditional formatting options to your data analysis arsenal, Color Scales, Data Bars, and Icon Sets.
Quand vous avez des chiffres à analyser, est-ce que vous chargez ces données dans un tableur, formatez la feuille, puis passez des heures à fixer les nombres pour comprendre ce qu'ils signifient ? Non ! Vous créez un graphique qui représente les données. Alors qu'un graphique peut vous aider à voir le résultat global de ces données, comment examiner la façon dont des valeurs individuelles rentrent dans le schéma général ? Le formatage conditionnel peut vous aider à vous concentrer sur les valeurs qui constituent l'ensemble de données.
LibreOffice nous fournit de nombreuses options différentes de formatage conditionnel, nous permettant de trouver un réglage qui fonctionnera pour beaucoup de types de données différents. L'une des méthodes conditionnelles a été présentée dans la partie 43 sur les fonctions statistiques (le FCM n° 91). Aujourd'hui, je veux ajouter trois options supplémentaires de formatage conditionnel à votre arsenal d'analyses de données : l'Échelle de couleurs, la Barre de données et le Jeu d'icônes.
Methods of Calculation In all three methods, you create a range or condition for your data. The range or condition is controlled by 6 different methods for calculating values. Let's look at each of the methods. • Minimum – The smallest value in the data set. • Maximum – The largest value in the data set. • Percentile – The percentile ranking of each value within the minimum to maximum range of the data points. • Value – The value is based off a fixed number you enter. • Percent – The part each value is of the total sum of the data set. • Formula – The value is set by a cell reference or a formula. While percentile and percent may seem like the same thing, mathematically they are different. A percentile breaks the difference between minimum and maximum into 100 pieces and reports where the values fall in those 100 pieces. A percent shows how much the value is of the total sum of all the numbers in the set.
Méthodes de calcul
Dans les trois méthodes, vous créez une plage ou condition pour vos données. La plage ou condition est contrôlée par 6 différentes méthodes de calcul des valeurs. Regardons chacune d'elles : • Minimum - La valeur la plus petite du jeu des données. • Maximum - La valeur la plus grande du jeu des données. • Centile - Le rang en centièmes de chaque valeur dans la plage du minimum au maximum des points de données. • Valeur - La valeur est basée sur un nombre fixe que vous saisissez. • Pourcentage - La part qu'a chaque valeur dans la somme globale du jeu des données. • Formule - La valeur est fixée en référence à une cellule ou une formule.
Bien que le centile et le pourcentage puissent sembler identiques, ils sont mathématiquement différents. Le centile divise la différence entre maximum et minimum en cent parties et indique où sont placées les valeurs dans ces 100 parties. Un pourcentage indique l'importance d'une valeur dans la somme de toutes les valeurs du jeu.
Color Scale Color Scale is a conditional format that highlights values in a gradient of chosen colors depending on their value. This method gives you a quick visual clue about where a value lies within the overall range. You can use 2 or 3 colors to create your scale. I will demonstrate both. We will set up a range of values to demonstrate how the scale works. The range we are creating is linear so you can see the way the colors graduate from one to the other. Start by entering the value 1 in the cell A1. Select cell range A1:A10. Edit > Fill > Series. In the dialog, select Down for the direction, Linear for the Series Type, and 10 for the Increment value. Click OK. The values will fill in. Now, select cell range A1:J10. Edit > Fill > Series. Select Right for the direction, Linear for the Series Type, and 1 for the Increment value. Click OK. If you did everything correctly, you will have the values from 1 to 100 in 10 rows.
Échelle de couleurs
L'Échelle de couleurs est un formatage conditionnel qui surligne les valeurs dans un dégradé de couleurs sélectionnées en fonction de leur valeur. Cette méthode vous donne un indice visuel rapide sur la position de la valeur dans l'ensemble. Vous pouvez utiliser 2 ou 3 couleurs pour créer votre échelle. Je montrerai les deux.
Nous allons préparer une plage de valeurs pour montrer comment l'échelle fonctionne. La plage que nous allons créer est linéaire, de sorte que vous pourrez voir la façon dont les couleurs varient d'une valeur à l'autre. Commencez par entrer la valeur 1 dans la cellule A1. Sélectionnez la plage de cellules A1:A10. Édition > Remplir > Série. Dans la boîte de dialogue, sélectionnez Bas pour la direction, Arithmétique pour le type de série et 10 pour l'incrément. Cliquez sur OK. Les valeurs sont placées dans les cellules. Maintenant, sélectionnez la plage A1:J10. Édition > Remplir > Série. Sélectionnez Droite pour la direction, Arithmétique pour le type de série et 1 pour l'incrément. Cliquez sur OK. Si vous avez tout fait correctement, vous aurez des valeurs de 1 à 100 dans les 10 lignes.
Now, let's set up our Color Scale. If not still selected, select cell range A1:J10. Format > Conditional Formatting > Color Scale. Color Scale is always set at All Cells. Set the top center dropdown box to Color Scale (2 Entries). Leave the rest of the settings at their default values. Click OK. Notice the range of numbers are highlighted by a gradient of colors starting with the default red and blending more with the blue until it gets to the center, then the red starts to fade out as it graduates back to blue at the end. So, you can see here how the scale works. If your number is highlighted in a purplish color, you know that it is a mid-range value. The more red, the closer to the beginning; the more blue, the closer to the end. We can add a third color to the scale, increasing the visual effect. Format > Conditional Formatting > Manage. Select the condition in the list and click Edit. Switch the top center dropdown to Color Scale (3 Entries). The three center entries relate to the third color. Select Percentile for the type, 50 for the value, and Green 3 for the color. Click OK. Click OK again to close the Manage Conditional Formatting dialog. You now have two color gradients. Red to green, green to blue. The more green, the closer to the middle. It might help to format the cells font color to white so they stand out against the colors better.
Maintenant, réglons notre Échelle de couleurs. Si ce n'est pas déjà fait, sélectionnez la plage A1:J10. Format > Formatage conditionnel > Échelle de couleurs. La boîte Échelle de couleurs est toujours préréglée à Toutes les cellules. Dans la liste déroulante du champ en haut au centre, choisissez Échelle de couleurs (2 entrées). Laissez les autres champs à leurs valeurs par défaut. Cliquez sur OK. Notez que la plage de nombres est surlignée avec un dégradé de couleurs, commençant sur un rouge standard, se mélangeant de plus en plus à du bleu jusqu'au milieu, puis perdant peu à peu du rouge pour obtenir le bleu à la fin. Ainsi, vous pouvez voir ici comment fonctionne l'échelle de couleurs. Si votre nombre est surligné avec une couleur violacée, vous savez que c'est une valeur du milieu. Plus c'est rouge, plus la valeur est proche du début ; et plus c'est bleu, plus elle est près de la fin.
Nous pouvons ajouter une troisième couleur à l'échelle, ce qui augmente l'effet visuel. Format > Formatage conditionnel > Gérer. Sélectionnez la condition dans la liste et cliquez sur Éditer. Modifiez le champ en haut au centre pour Échelle de couleurs (3 entrées). Les trois champs du milieu sont liés à la troisième couleur. Sélectionnez Centile pour le type, 50 pour la valeur et Vert 3 pour la couleur. Cliquez sur OK. Cliquez à nouveau sur OK pour fermer la boîte de dialogue Gérer le formatage conditionnel. Vous avez maintenant deux dégradés de couleurs. Du rouge au vert, du vert au bleu. Plus vous avez de vert, plus vous êtes proches du centre. Le formatage en blanc des caractères des cellules pourrait aider à mieux les distinguer sur le fond coloré.
Data Bars Data Bars are like a quick-and-dirty bar chart right in your spreadsheet, creating a data mixed with chart effect. To make data bars look good, it helps to make the columns a little wider. We will place our data bars data at the end of our previous table so you can do just that. To set up our data for the data bars, select the cell range L1:L11. Edit > Fill > Series. In the Fill dialog, select down for the Direction, Linear for the Fill Type, -50 for the start value, and 10 for the Increment. Click OK. The data will fill in from -50 to 50 in increments of 10. To create the data bars, select the cell range L1:L10. Format > Conditional Formatting > Data Bars. Just like the color scale, the setting must remain on all cells. You can use any of the calculation methods to create your upper and lower range, but leaving it on automatic shows the best results for our data. Click OK. You will see that the cells are split in half. The negative numbers grow to the left in red, and the positive numbers grow to the right in blue. Notice the gradient scale on both colors. But that's not the end of this story. Did you notice the More Options button? Maybe you even clicked on it. You're the type who likes to get ahead, aren't you? Well, let's go back and take a look at those options. Let's edit our data bar range, Format > Conditional Formatting > Manage. Select the Data Bars range (L1:L11) and click the Edit button. Finally, click on the More Options button.
Les Barres de données
Les Barres de données sont comme un graphique vite fait dans la feuille, créant un mélange de données et de graphique. L'élargissement des colonnes aide pour que les barres de données aient un bel aspect. Nous placerons nos données pour les Barres de données à la suite de notre table précédente, pour que vous puissiez le faire.
Pour préparer les données pour les Barres de données, sélectionnez la plage de cellules L1:L11. Édition > Remplir > Série. Dans la boîte de dialogue Remplir, sélectionnez Bas pour la direction, Arithmétique pour le type de remplissage, -50 pour la valeur de début et 10 pour l'incrément. Cliquez sur OK. Les données vont remplir les cellules de -50 à 50 par incréments de 10.
Pour créer les Barres de données, sélectionnez la plage L1:L11. Format > Formatage conditionnel > Barre de données. Comme pour l'Échelle de couleurs, ne modifiez pas les valeurs de tous les champs. Vous pouvez utiliser n'importe quelle méthode de calcul pour créer votre plage basse et votre plage haute, mais en laissant en automatique, vous afficherez les meilleurs résultats pour vos données. Cliquez sur OK. Vous verrez que les données sont séparées en deux. Les nombres négatifs croissent vers la gauche en rouge et les nombres positifs croissent vers la droite en bleu. Notez l'échelle en dégradé pour les deux couleurs.
Mais ce n'est pas la fin de notre histoire. Avez-vous remarqué le bouton Plus d'options… ? Peut-être même que vous l'avez cliqué. Vous êtes le gars qui aime aller de l'avant, n'est-ce pas ? Bien, revenons en arrière et jetons un regard à ces options. Éditons notre plage de Barres de données, Format > Formatage conditionnel > Gérer. Sélectionnez la plage des barres de données (L1;L11) et cliquez sur le bouton Éditer. Enfin, cliquez sur le bouton Plus d'options…
Here you can adjust the minimum and the maximum just like in the dialog. The Bar Colors options allow you change the colors for positive and negative numbers. The Fill lets you change from Gradient to Color (solid). Position of the Vertical Axis changes the position of the center or zero line. Automatic will adjust its position depending on the type of numbers you have. For positive numbers, it automatically left-justifies the bars, but for negative numbers, it automatically right-justifies the bars. For a mix of positive and negative numbers, Automatic centers the 0-axis line; Middle forces the vertical axis to the center of the cells; None forces the bar to left-justify, even the negative numbers. With None, the only way to know positive from negative is by the color. Using Middle or None for the vertical axis activates the Bar Lengths. Here you can adjust the minimum and maximum lengths as a percentage. If you want all values (except zero) to show a bar, you can set the minimum to 1. Or, if you don't want the bars to go all the way to the left or right, you can set the maximum to something other than 100 (say 80). Play around with these options to see what kind of results you get. The Display Bar Only check-box causes the cells to just show the data bars and hides the number values.
Ici, vous pouvez ajuster le minimum et le maximum, comme dans la boîte de dialogue. Les options Couleurs de barre vous permettent de changer les couleurs des nombres positifs et négatifs. Le champ Remplir [Ndt : dans la version 5] vous permet de changer de Dégradé à Couleur (pleine). Le choix de Axe vertical modifie la position de la ligne, au centre ou à zéro (gauche). Automatique ajustera sa position en fonction du type de nombres que vous avez. Pour les nombres positifs, il justifie automatiquement les barres à gauche ; pour les nombres négatifs, il les cale à droite. Pour un mélange de nombres négatifs et positifs, Automatique centre l'axe zéro, Milieu force l'axe vertical au centre des cellules, Aucun force un calage des barres à gauche, même pour les nombres négatifs. Avec Aucun, la couleur est la seule façon de distinguer les nombres négatifs des positifs. L'utilisation de Milieu ou Aucun pour l'axe vertical active le champ Longueur des barres [version 5]. Ici, vous pouvez ajuster les longueurs minimum et maximum, en pourcentage. Si vous voulez qu'il y ait une barre pour chaque valeur (sauf pour zéro), mettez 1 en minimum. Ou, si vous ne voulez pas que les barres aillent jusqu'au bout à droite ou à gauche, vous pouvez mettre le maximum à valeur différente de 100 (disons 80). Amusez-vous avec ces options pour voir les différents résultats. La case à cocher Afficher seulement la barre entraîne la visualisation des barres seules, sans les valeurs.
Icon Sets Icon sets are used to show whether a value is below, equal to, or above a defined value. The icon sets come in several varieties, and in sets of 3, 4, or 5 icons. Use them according to the style and number of icons you need. We will set up a group of random numbers, average them, and then use three arrow icons to determine whether the values are above or below the average. For the setup, select cell range A13:A24. Edit > Fill > Random Number. Set Distribution to Uniform Integer, Minimum to 50, and Maximum to 50,000. Click OK. In cell C13, enter the formula =AVERAGE(A13:A24). To set up our icons, select cell range A13:A24. Format > Conditional Formatting > Icon Set. Change both of the dropdown boxes to the Formula method. In the first text-box with the yellow right-pointing arrow, enter the formula as =C13. If the value of the cell equals the value in C13, the yellow arrow will show in the cell. If the value of the cell is less than the value of C13, the red down-arrow will display in the cell. In the second text-box, enter the formula =CEILING(C13, 1, 0). The CEILING function takes the number provided, in this case the value in cell C13, and rounds it up according to the value in the second argument. Since we have 1 as the second argument, the function will round the number up to the next whole number. The third value, set at 0 in our case, controls whether negative numbers are rounded according to their actual value or absolute value. In order to round based on the absolute value, you would set the third argument to something other than 0 (zero). Click on the OK button, and the arrows will show in the cells based on their relationship to the value in C13. Conditional Formatting gives us the ability to give visual clues about the values in a spreadsheet without having to create a chart or graph. Color Scale sets the background color on a cell – based on its value within a defined range. Data Bars creates a mini bar-chart directly in the cells. Icon Set marks the cells with icons based on their conditional relationship to some value. Each method has its own function, giving you a different style of results. Just like when creating a chart or graph, you have to make the decision about which one works best for your application.
Jeux d'icônes
Les jeux d'icônes sont utilisés pour montrer si une valeur est en dessous de, égale à ou au-dessus d'une valeur définie. Les jeux d'icônes existent en plusieurs variétés et en jeux de 3, 4 ou 5 icônes. Utilisez-les suivant le style et le nombre d'icônes dont vous avez besoin. Nous allons mettre en place des nombres aléatoires, en faire la moyenne, et ensuite, utiliser trois icônes flèches pour déterminer si ces valeurs sont en dessous ou au-dessous de la moyenne.
En préparation, sélectionnez la plage de cellules A13:A24. Édition > Remplir > Nombre aléatoire. Prenez Entier uniforme pour Distribution, 50 pour Minimum, et 50 000 pour Maximum. Cliquez sur OK. Dans la cellule C13, entrez la formule =MOYENNE(A13:A24).
Pour régler les icônes, sélectionnez la plage A13:A24. Format > Formatage conditionnel > Jeu d'icônes. Changez les deux champs à liste déroulante pour la méthode Formule. Dans le premier champ de saisie, face à la flèche jaune pointant à droite, entrez la formule =C13. Si la valeur de la cellule est égale à la valeur en C13, la flèche jaune sera affichée dans la cellule. Si la valeur de la cellule est plus petite que la valeur de C13, la cellule comportera la flèche rouge descendante. Dans le second champ de saisie, entrez la formule =PLAFOND(C13;1;0). La fonction PLAFOND (CEILING en anglais) prend le nombre fourni, dans notre cas la valeur dans la cellule C13, et l'arrondit par valeur supérieure en fonction de la valeur du second argument. Comme nous avons 1 comme second argument, la fonction fera l'arrondi au prochain nombre entier supérieur. Le troisième argument, mis à 0 dans notre cas, contrôle si les nombres négatifs sont arrondis suivant leur valeur réelle ou leur valeur absolue. Pour arrondir à partir de la valeur absolue, vous auriez mis le troisième argument à quelque chose d'autre que zéro. Cliquez sur le bouton OK et les flèches seront affichées dans les cellules en fonction de leur position respective par rapport à la valeur en C13.
Le formatage conditionnel nous donne la possibilité d'obtenir des indices visuels sur les valeurs présentes dans la feuille de calcul, sans avoir à créer un diagramme ou un graphique. L'échelle de couleurs met une couleur de fond dans une cellule, suivant sa valeur dans une plage définie. Les barres de données créent un mini barre-graphe directement dans les cellules. Le jeu d'icônes marque les cellules avec des icônes sur la base de leur relation conditionnelle à une valeur. Chaque méthode a sa fonction propre, qui vous donne un style de résultat différent. Comme en créant un diagramme ou un graphique, vous devez décider celui qui fonctionne le mieux pour votre utilisation.