Techniques de nettoyage des données
- Le formatage conditionnel dans Excel vous permet d’appliquer automatiquement des mises en forme spécifiques à des cellules en fonction de conditions que vous définissez. C’est un outil puissant pour mettre en évidence des informations importantes, repérer des tendances ou des valeurs aberrantes, et rendre vos données plus lisibles et compréhensibles. Voici comment vous pouvez appliquer un formatage conditionnel dans Excel :
1 Sélectionnez la plage de cellules à laquelle vous souhaitez appliquer le formatage conditionnel.
2Dans l’onglet “Accueil” du ruban Excel, cliquez sur le bouton “Formatage conditionnel” dans le groupe “Styles”.
3 Choisissez l’une des options de formatage conditionnel prédéfinies, telles que “Mises en surbrillance des cellules”, “Barres de données”, “Nuances de couleurs” ou “Jeux d’icônes”, en fonction de vos besoins.
4 Si vous choisissez “Mises en surbrillance des cellules”, vous pouvez sélectionner une règle prédéfinie telle que “Supérieur à”, “Inférieur à”, “Entre” ou “Texte contenant”, et spécifier les critères et le format à appliquer.
Exemple : J’ai décidé de faire un formatage conditionnel avec le terme banane en choisissant de mettre en surbrillance (Mises en surbrillance des cellules ) les cellules ou le texte contient ( texte contenant) le mot « Banane ».
5 – Si vous avez besoin de conditions plus complexes, choisissez “Nouvelle règle” dans le menu “Formatage conditionnel”. Cela vous permettra de créer des règles personnalisées basées sur des formules ou des conditions multiples.
Dans la fenêtre “Nouvelle règle de mise en forme”, sélectionnez le type de règle que vous souhaitez créer, tel que “Formater toutes les cellules d’après leur valeur”, “Formater uniquement les cellules contenant”, “Formater uniquement les valeurs du haut ou du bas”, “Formater uniquement les valeurs uniques” ou “Utiliser une formule pour déterminer les cellules à formater”.
Spécifiez les critères de votre règle et choisissez le format à appliquer, tel que la couleur de remplissage, la couleur de police, les bordures ou les styles de police.
Cliquez sur “OK” pour appliquer le formatage conditionnel à votre plage de cellules sélectionnée.
Exemple avec mon tableau de fruit :
Voici quelques exemples pratiques de formatage conditionnel dans Excel : j’ai émis une nouvelle règle où j’applique une mise en forme uniquement aux cellules qui contiennent le mot Orange en couleur Orange.
Après avoir choisi ma mis en forme, j’ai décidé d’appliquer la règle selon laquelle le texte spécifique contant le mot « Orange » sera à mettre en évidence dans mes données.
Exercez-vous ! :
- Mettez en surbrillance les valeurs supérieures à une certaine limite en utilisant une couleur de remplissage rouge.
- Utilisez des barres de données pour visualiser la contribution relative de chaque valeur à un total.
- Appliquez des nuances de couleurs pour créer une carte thermique mettant en évidence les tendances dans vos données.
- Utilisez des jeux d’icônes, tels que des flèches ou des symboles, pour indiquer si les valeurs sont supérieures, inférieures ou dans la moyenne.
- Créez une règle basée sur une formule pour mettre en évidence les cellules qui répondent à des critères spécifiques, tels que les valeurs doublons ou les cellules contenant des erreurs.
II Comment je peux faire pour utiliser la fonction “Supprimer les doublons” dans excel
La fonction “Supprimer les doublons” dans Excel est un outil pratique qui vous permet d’identifier et de supprimer rapidement les enregistrements en double dans une plage de données. Voici comment vous pouvez utiliser cette fonction :
1-Sélectionnez la plage de cellules contenant les données dans lesquelles vous souhaitez supprimer les doublons. Assurez-vous d’inclure toutes les colonnes pertinentes qui déterminent l’unicité de chaque enregistrement.
2-Dans l’onglet “Données” du ruban Excel, cliquez sur le bouton “Supprimer les doublons” dans le groupe “Outils de données”.
3-Dans la boîte de dialogue “Supprimer les doublons”, vous verrez une liste de toutes les colonnes de votre sélection. Par défaut, toutes les colonnes seront cochées, ce qui signifie qu’Excel utilisera toutes les colonnes pour déterminer les doublons.
Si vous souhaitez ne considérer que certaines colonnes pour identifier les doublons, décochez les cases des colonnes que vous voulez exclure.
Cliquez sur “OK” pour supprimer les doublons.
Excel affichera un message vous indiquant le nombre de valeurs uniques et de doublons trouvés, ainsi que le nombre d’enregistrements supprimés. Cliquez sur “OK” pour fermer le message.
Voici quelques conseils et considérations supplémentaires lors de l’utilisation de la fonction “Supprimer les doublons” :
Assurez-vous que vos données sont correctement formatées et normalisées avant d’appliquer la fonction. Par exemple, assurez-vous que les dates sont dans un format cohérent et que les valeurs textuelles sont saisies de manière cohérente (par exemple, “Oui”/”Non” au lieu de “Y”/”N” ou “Oui”/”Non”).
Si vous souhaitez conserver une copie de vos données d’origine, pensez à copier votre plage de données dans une nouvelle feuille ou un nouveau workbook avant d’appliquer la fonction “Supprimer les doublons”.
Gardez à l’esprit que la fonction “Supprimer les doublons” ne fait que supprimer les enregistrements en double et conserve la première occurrence de chaque valeur unique. Si vous avez besoin de plus de contrôle sur les enregistrements à conserver, vous pouvez utiliser des fonctions avancées telles que les tableaux croisés dynamiques ou les formules matricielles.
Si vous travaillez avec de grands ensembles de données, il peut être plus efficace de prétraiter vos données à l’aide de fonctions telles que “UNIQUE” ou “DINSTINCT” avant d’appliquer la fonction “Supprimer les doublons”, afin de réduire la taille de votre plage de données.
En utilisant judicieusement la fonction “Supprimer les doublons”, vous pouvez rapidement nettoyer vos données et vous assurer que vous travaillez avec un ensemble de données unique et cohérent, ce qui est essentiel pour une analyse précise et fiable dans Excel.
III- “ESTNA()” et “ESTNUM()” pour le nettoyage de données
Les fonctions “ESTNA()” et “ESTNUM()” dans Excel sont des outils utiles pour détecter les cellules vides et les valeurs non numériques dans vos données. Voici comment vous pouvez les utiliser, ainsi que des formules pour remplacer ces valeurs par des valeurs par défaut ou des estimations basées sur d’autres données :
1 La fonction “ESTNA()” :
Cette fonction vous indique si une cellule contient une valeur manquante ou non disponible (représentée par #N/A dans Excel).
Par exemple, si vous avez =ESTNA(A1) dans une cellule, Excel vous dira “VRAI” si la cellule A1 est vide ou contient #N/A, et “FAUX” si elle a une valeur.
Exemple pour une cellule avec une valeur
Exemple pour une cellule avec #N/A
2 La fonction “ESTNUM()” :
Cette fonction vous indique si une cellule contient un nombre ou non.
Par exemple, si vous avez =ESTNUM(A1) dans une cellule, Excel vous dira “VRAI” si la cellule A1 contient un nombre, et “FAUX” si elle contient du texte ou est vide.
Par exemple, avec une cellule contenant un nombre :
Exemple avec une cellule vide :
Maintenant, pour remplacer ces cellules vides ou non numériques par quelque chose de plus significatif, vous pouvez utiliser une formule spéciale appelée “SI()”. C’est comme donner des instructions à Excel : “Si cette condition est vraie, fais ceci, sinon, fais cela.”
3- Remplacer les cellules vides par une valeur par défaut :
Vous pouvez dire à Excel : “Si la cellule est vide, mets ‘Valeur par défaut’, sinon, garde la valeur qui est déjà là.”
En termes d’Excel, cela ressemble à : =SI(A1=””, “Valeur par défaut”, A1).
Par exemple : j’ai appliqué la formule =SI afin de connaitre mon stock de fruit et mettre une alerte si je commençais à être proche de 3 en ayant appliqué « =SI(C5>3;”oui”;”alerte”) »
Faire la formule =SI(A1=””, “Valeur par défaut”, A1) peut être compliqué pour un débutant, c’est pourquoi je vous propose en ressource complémentaire excellente video de « La minute excel ».
Vidéo : https://www.youtube.com/watch?v=5lCvmWm6wTc
4- Remplacer les valeurs non numériques par une estimation basée sur d’autres données :
Vous pouvez dire à Excel : “Si la cellule contient un nombre, garde-le, sinon, calcule la moyenne de tous les nombres de la colonne.”
En termes d’Excel, cela ressemble à : =SI(ESTNUM(A1), A1, MOYENNE(A:A)).
LES BASES :
Cas Estnum : Il mentionnera le terme FAUX si ce n’est pas un chiffre, et VRAI si c’est un chiffre.
Cas concret « =SI(ESTNUM(A1), A1, MOYENNE(A:A)). »
Avec Estnum, j’ai demandé de me mettre Vrai si c’est un chiffre ( =SI(ESTNUM ) puis de mettre la moyenne des cellule sélectionné si je n’ai pas de chiffre ( MOYENNE(C2:C13)).
En premier lieu j’ai fait cette formule sur une cellule, il affiche donc vrai pour la cellule concerné (C2)
Ensuite, lorsque j’etends la cellule aux autres cellule en dessous, celui-ci comble les cellules sans valeurs numériques en leur fournissant une moyenne ( MOYENNE(C2 :C13)).
IV. “CHERCHE()” et “SUBSTITUE()”
Les fonctions CHERCHE() et SUBSTITUE() sont des fonctions Excel utilisées pour rechercher et remplacer du texte dans une chaîne de caractères.
- =SUBSTITUE(texte; ancien_texte; nouveau_texte; [instance_num])
La fonction SUBSTITUE() est une fonction Excel puissante qui permet de remplacer du texte spécifique dans une chaîne de caractères par un autre texte. Elle est particulièrement utile lorsque vous devez modifier ou mettre à jour des données de manière cohérente et efficace.
Voici la syntaxe de la fonction SUBSTITUE() : =SUBSTITUE(texte; ancien_texte; nouveau_texte; [instance_num])
-“texte” est la chaîne de caractères dans laquelle vous voulez effectuer le remplacement. Il peut s’agir d’une référence de cellule ou d’une chaîne de texte directement entrée dans la formule.
-“ancien_texte” est le texte que vous souhaitez remplacer dans la chaîne de caractères.
-“nouveau_texte” est le texte par lequel vous voulez remplacer l’ancien texte.
-“[instance_num]” est un argument optionnel qui spécifie quelle occurrence de l’ancien texte vous voulez remplacer. Si vous omettez cet argument, toutes les occurrences seront remplacées.
Exemple simple : J’ai écrit « Banane caraïbe », mais j’apprends qu’il faut que je précise et l’ile et que ces bananes viennent de Martinique.
Alors je dois substituer le terme, je vais donc faire =SUBSTITUE(B2;”Caraibe”;”Martinique”)
(Caraïbe est le terme que je veux substituer, et Martinique le mot que je souhaite faire apparaitre à la place du mot caraïbe dans mon autre cellule), voici ce que ça donne :
La fonction SUBSTITUE() est très pratique lorsque vous devez nettoyer ou normaliser des données, mettre à jour des informations spécifiques, ou même extraire des parties spécifiques d’une chaîne de caractères en combinaison avec d’autres fonctions telles que CHERCHE().
En maîtrisant la fonction SUBSTITUE(), vous serez en mesure de manipuler et de transformer efficacement des données textuelles dans Excel, ce qui est une compétence précieuse dans de nombreux domaines professionnels.
Fonction CHERCHE()
La fonction CHERCHE() est une fonction Excel essentielle qui permet de trouver la position de départ d’un texte spécifique dans une chaîne de caractères. Elle est particulièrement utile lorsque vous devez localiser ou extraire des informations spécifiques d’une cellule.
Voici la syntaxe de la fonction CHERCHE() :
CHERCHE(texte_cherché; texte_dans_lequel_chercher; [position_départ])
“texte_cherché” est le texte que vous souhaitez rechercher dans la chaîne de caractères.
Exemple simple : Je cherche le mot Espagne dans une cellule, j’ai donc fait « =CHERCHE(“Espagne”;A7) », ma cellule m’indique 7 pour me dire qu’il se trouve en 7ème position des caractères.
Pour aller plus loin : Je recherche les fruits en provenance d’Algérie dans toutes ma liste.
- J’émets ma formule
J’étends la cellule ayant la formule afin de l’appliqué sur toutes mes données- Il se peut qu’Excel vous fournis des valeurs par défaut comme des dates ou encore des chiffres.
Si vous voulez avoir une autre information, il vous suffit de faire clique droit, de sélectionner « format de cellule », puis dans la rubrique « Nombre » vous pouvez choisir la catégorie de votre choix.
Dans mon cas, j’ai décidé de personnalisé le nombre, de sélectionner « standard » et de mettre « OUI » afin que mes cellules m’indiquent l’existence de fruit d’Algérie avec un terme qui me convient mieux via le mot OUI.
Resultat : j’ai les données dont j’ai besoin, je sais qu’elles sont les fruits qui viennent d’Algérie.
V. =MOYENNE(). =MEDIANE et =ECART.TYPE()”.
1.=MOYENNE().
Faire une moyenne est relativement simple.
1-Créer une cellule « moyenne » pour vos données, sélectionnez le groupe de cellule concerné, puis cliquez sur l’icone de mise en forme ( petit carré en bas à droite – 3) et sélectionnez « Totaux » et enfin cliquez sur la case « Moyenne ».
Et voila, vous avez votre moyenne pour qui apparait automatiquement :
Pour aller plus loin : Si vous souhaitez plutôt appliquer une moyenne pour des données spécifiques, vous pouvez appliquer indépendamment la formule =MOYENNE.
Par exemple : je voudrais la moyenne des fruits de Guadeloupe, je vais donc faire la formule et sélectionner les quantités des fruits de Guadeloupe.
J’applique donc la formule en additionnant les cellules concernées, et à la fin j’obtiens la moyenne des fruits de Guadeloupe.
- =MEDIANE
Le principe est le même que pour la moyenne, il vous suffit d’appliquer la formule « = MEDIANE » sur excel, puis de selectionner les données que vous souhaitez pour obtenir votre Mediane
2 Exemples : A gauche pour le total des fruits, à droite pour le total des fruits d’Algérie.
- =ECARTYPE
A l’instar de la moyenne et de la Mediane, il faut prendre une cellule excel vide et inscrire =ECARTYPE puis sélectionner les données concernés.
Par exemple : j’inscris =ECARTYPE, puis je le sectionne les données qui m’intéresse et j’obtiens l’écart type que je veux ( vous pouvez aussi séléctionner les données spécifique si vous le souhaitez en en appliquant plutôt une addition des cellules comme =ECARTTYPE(A2+A5+A9) )
VI – techniques de normalisation et de standardisation des données dans Excel
La normalisation et la standardisation sont deux techniques couramment utilisées pour mettre à l’échelle et transformer des données dans Excel. Elles sont particulièrement utiles lorsque vous travaillez avec des données qui ont des unités ou des échelles différentes, car elles permettent de comparer et d’analyser les données de manière plus significative.
Normalisation : La normalisation consiste à mettre à l’échelle les données dans une plage spécifique, généralement entre 0 et 1. Pour normaliser les données dans Excel, vous pouvez utiliser la formule suivante :
=(valeur – valeur_min)) / (valeur_max – valeur_min))
Les données normalisées seront : 0, 0.25, 0.5, 0.75, 1
Par exemple : j’ai appliqué la formule =(B2-MIN(B$2:B$13))/(MAX(B$2:B$13)-MIN(B$2:B$13)) pour normaliser mon résultat et j’ai obtenu 0.5.
2. Standardisation : La standardisation, également connue sous le nom de “z-score”, consiste à transformer les données de manière à ce qu’elles aient une moyenne de 0 et un écart type de 1. Pour standardiser les données dans Excel, vous pouvez utiliser la formule suivante :
=(valeur – moyenne) / écart_type
“valeur” est la valeur que vous souhaitez standardiser.
“moyenne” est la moyenne de l’ensemble de données.
“écart_type” est l’écart type de l’ensemble de données.
Technique simple : prenez directement la moyenne et l’écart type que vous avez trouvez précédemment calculé (le Z-score pour chaque valeur)
Entrez la formule suivante : =(A1-$B$1)/$B$2
A1 fait référence à la première valeur de vos données.
$B$1 fait référence à la cellule contenant la moyenne (le signe $ verrouille la référence de la cellule, de sorte qu’elle ne change pas lorsque vous copiez la formule vers le bas).
$B$2 fait référence à la cellule contenant l’écart type.
Exemple – B2 est ma première valeur, B$14 ma moyenne et B$18 mon écart type, cela donne:
Étape 4 : Copiez la formule vers le bas pour les autres valeurs.
J’étends la formule en question et j’obtiens mon Z-score.
Dans mon cas :
Pour aller plus loin, je peux vous recommander la video de Wise Cat Noro Boris qui offre des informations très complète si vous souhaitez maitriser intégralement la standarisation des données :
https://www.youtube.com/watch?v=T1rNgua_E_A
Merci d’avoir lu et appliqué les différentes formules, j’espère que ce cours vous a aidé dans le nettoyage de vos données.
Data Cleaning Techniques
Conditional formatting in Excel lets you automatically apply specific formatting to cells based on conditions you set. It’s a powerful tool for highlighting important information, spotting trends or outliers, and making your data more readable and understandable. Here’s how you can apply conditional formatting in Excel:
1 Select the range of cells to which you want to apply conditional formatting.
2 – On the “Home” tab of the Excel ribbon, click the “Conditional Formatting” button in the “Styles” group.
3 – Choose one of the predefined conditional formatting options, such as “Cell Highlights”, “Data Bars”, “Color Swatches”, or “Icon Sets”, depending on your needs.
4 – If you choose “Cell highlights”, you can select a predefined rule such as “Greater than”, “Less than”, “Between” or “Text containing”, and specify the criteria and format to be applied.
Example: I’ve decided to do conditional formatting with the term banana by choosing to highlight (Highlight Cells) cells where the text contains (text containing) the word “Banana”.
5- If you need more complex conditions, choose “New rule” from the “Conditional formatting” menu. This will enable you to create custom rules based on formulas or multiple conditions.
In the “New formatting rule” window, select the type of rule you wish to create, such as “Format all cells according to their value”, “Format only cells containing”, “Format only top or bottom values”, “Format only unique values” or “Use a formula to determine which cells to format”.
Specify the criteria for your rule and choose the formatting to apply, such as fill color, font color, borders, or font styles.
Click “OK” to apply the conditional formatting to your selected range of cells.
Example with my fruit table:
Here are some practical examples of conditional formatting in Excel: I issued a new rule where I apply formatting only to cells that contain the word Orange in the color Orange.
After choosing my formatting, I decided to apply the rule that the specific text containing the word “Orange” will be highlighted in my data.
Practice! :
Highlight values above a certain limit using a red fill color.
Use data bars to visualize the relative contribution of each value to a total.
Apply color swatches to create a heat map that highlights trends in your data.
Use icon sets, such as arrows or symbols, to indicate whether values are above, below, or average.
Create a formula-based rule to highlight cells that meet specific criteria, such as duplicate values or cells with errors.
II How I Can Use the “Remove Duplicates” Function in Excel
The “Remove Duplicates” function in Excel is a handy tool that allows you to quickly identify and remove duplicate records in a range of data. Here’s how you can use this function:
1-Select the range of cells containing the data from which you want to remove duplicates. Be sure to include all relevant columns that determine the uniqueness of each record.
2-In the “Data” tab of the Excel ribbon, click the “Remove Duplicates” button in the “Data Tools” group.
3-In the “Remove Duplicates” dialog box, you will see a list of all the columns in your selection. By default, all columns will be checked, which means that Excel will use all columns to determine duplicates.
If you want to consider only certain columns to identify duplicates, uncheck the boxes of the columns you want to exclude.
Click “OK” to remove the duplicates.
Excel will display a message telling you how many unique and duplicate values were found, as well as how many records were deleted. Click “OK” to close the message.
Here are a few additional tips and considerations when using the “Delete duplicates” function:
Make sure your data is properly formatted and normalized before applying the function. For example, ensure that dates are in a consistent format and that text values are entered consistently (e.g. “Yes”/“No” instead of “Y”/“N” or “Yes”/“No”).
If you wish to keep a copy of your original data, remember to copy your data range to a new sheet or workbook before applying the “Remove duplicates” function.
Bear in mind that the “Delete Duplicates” function only deletes duplicate records and retains the first occurrence of each unique value. If you need more control over which records to keep, you can use advanced functions such as pivot tables or matrix formulas.
If you’re working with large data sets, it may be more efficient to pre-process your data using functions such as “UNIQUE” or “DINSTINCT” before applying the “Remove Duplicates” function, in order to reduce the size of your data range.
By making judicious use of the “Remove Duplicates” function, you can quickly clean up your data and ensure that you are working with a single, consistent data set, which is essential for accurate and reliable analysis in Excel.
III- “ISNA()” and “ISNUMBER()” for data cleansing
The “ISNA()” and “ISNUMBER()” functions in Excel are useful tools for detecting empty cells and non-numerical values in your data. Here’s how you can use them, along with formulas for replacing these values with default values or estimates based on other data:
1 The “ISNA()” function:
This function tells you if a cell contains a missing or unavailable value (represented by #N/A in Excel).
For example, if you have =ISNA()(A1) in a cell, Excel will tell you “TRUE” if cell A1 is empty or contains #N/A, and “FALSE” if it has a value.
Example for a cell with a value
Example for a cell with #N/A
2 The “ISNUMBER()” function:
This function tells you whether a cell contains a number or not.
For example, if you have =ISNUMBER(A1) in a cell, Excel will tell you “TRUE” if cell A1 contains a number, and “FALSE” if it contains text or is empty.
For example, with a cell containing a number:
Example with an empty cell:
Now, to replace these empty or non-numeric cells with something more meaningful, you can use a special formula called “IF()”. It’s like telling Excel: “If this condition is true, do this, otherwise, do that.”
3- Replace empty cells with a default value:
You can tell Excel: “If the cell is empty, set it to ‘Default Value’, otherwise, keep the value that’s already there.”
In Excel terms, this looks like: =IF(A1=””, “Default value”, A1).
For example: I applied the formula =IF in order to know my stock of fruit and put an alert if I started to be close to 3 by having applied “=IF(C5>3;”yes”;”alert”)”
Making the formula =IF(A1=””, “Default value”, A1) can be complicated for a beginner, which is why I offer you as a complementary resource the excellent video “La minute excel” (In French only).
Vidéo : https://www.youtube.com/watch?v=5lCvmWm6wTc
4- Replace non-numeric values with an estimate based on other data:
You can tell Excel: “If the cell contains a number, keep it, otherwise, calculate the average of all the numbers in the column.”
In Excel terms, this looks like: =IF(ISNUMBER(A1), A1, AVERAGE(A:A)).
BASICS:
ISNUMBER Case: It will mention the term FALSE if it is not a number, and TRUE if it is a number.
Concrete case « =IF(ISNUMBER(A1), A1, AVERAGE(A:A)). »
With ISNUMBER, I asked to put True if it is a number (=IF(ISNUMBER) then to put the average of the selected cells if I do not have a number (AVERAGE(C2:C13)).
First I did this formula on a cell, so it displays true for the cell concerned (C2)
Then when I extend the cell to the other cells below it fills in the cells without numeric values by giving them an average (AVERAGE(C2:C13)).
IV. “SEARCH()” et “SUBSTITUE()”
The SEARCH() and SUBSTITUE() functions are Excel functions used to search for and replace text in a string.
- =SUBSTITUE(texte; ancien_texte; nouveau_texte; [instance_num])
(English: (=SUBSTITUTE(text, old_text, new_text, [instance_num]) )
The SUBSTITUTE() function is a powerful Excel function that allows you to replace specific text in a string with other text. It is especially useful when you need to change or update data consistently and efficiently.
Here is the syntax of the SUBSTITUE() function: =SUBSTITUE(texte; ancien_texte; nouveau_texte; [instance_num])
( english : =SUBSTITUTE(text; old_text; new_text; [instance_num]) )
-“text” is the string in which you want to replace. This can be a cell reference or a text string directly entered into the formula.
-“old_text” is the text you want to replace in the string.
-“new_text” is the text with which you want to replace the old text.
-“[instance_num]” is an optional argument that specifies which occurrence of the old text you want to replace. If you omit this argument, all occurrences will be replaced.
Simple example: I wrote “Caribbean banana”, but I learn that I need to specify both the island and that these bananas come from Martinique.
So I need to substitute the term, so I’ll do:
=SUBSTITUE(B2;”Caraibe”;”Martinique”)
(Caribbean is the term I want to substitute, and Martinique is the word I want to appear in place of the word Caribbean in my other cell), here is what it gives:
The SUBSTITUTE() function comes in handy when you need to clean or normalize data, update specific information, or even extract specific parts of a string in combination with other functions such as SEARCH().
By mastering the SUBSTITUTE() function, you will be able to efficiently manipulate and transform text data in Excel, which is a valuable skill in many professional fields.
SEARCH() Function
The SEARCH() function is an essential Excel function that can find the starting position of a specific text in a string. It is especially useful when you need to locate or extract specific information from a cell.
Here is the syntax for the SEARCH() function:
SEARCH(search_text; search_in_text; [start_position])
“search_text” is the text you want to search for in the string.
Simple example: I am looking for the word Spain in a cell, so I did “=SEARCH(“Spain”;A7)”, my cell tells me 7 to tell me that it is in the 7th position of the characters.
To go further: I search for fruits from Algeria in all my list.
1 – I issue my formula
2 – I extend the cell with the formula in order to apply it to all my data
Excel may provide you with default values such as dates or numbers.
If you want to have other information, just right-click, select “cell format”, then in the “Number” section you can choose the category of your choice.
In my case, I decided to customize the number, select “standard” and put “YES” so that my cells indicate the existence of Algerian fruit with a term that suits me better via the word YES.
Result: I have the data I need, I know which fruits come from Algeria.
V. =AVERAGE(). =MEDIAN et =STDEV()”.””.
1.=AVERAGE().
Making an average is relatively simple.
1-Create an “average” cell for your data, select the group of cells concerned, then click on the formatting icon (small square at the bottom right – 3) and select “Totals” and finally click on the “Average” box.
And there you have it, your average for which appears automatically:
Further reading: If you would rather apply an average for specific data, you can independently apply the formula =AVERAGE.
For example: I would like the average of the fruits of Guadeloupe, so I will do the formula and select the quantities of the fruits of Guadeloupe.
So I apply the formula by adding the cells concerned, and at the end I obtain the average of the fruits of Guadeloupe.
2. =MEDIAN
The principle is the same as for the average, you just have to apply the formula “=MEDIAN” on excel, then select the data you want to obtain your Median
2 Examples: On the left for the total of fruits, on the right for the total of fruits of Algeria.
3. =STDEV()”.”
Like the mean and the Median, you have to take an empty Excel cell and enter =STDEV then select the data concerned. For example: I enter =STDEV, then I section the data that interests me and I obtain the standard deviation that I want (you can also select the specific data if you wish by applying instead an addition of the cells like =STDEV(A2+A5+A9))
VI – Techniques for Data Normalization and Standardization in Excel
Normalization and standardization are two commonly used techniques for scaling and transforming data in Excel. They are particularly useful when working with data that has different units or scales, as they allow for more meaningful comparison and analysis of the data.
Normalization: Normalization involves scaling data to a specific range, typically between 0 and 1. To normalize data in Excel, you can use the following formula:
=(value – min_value) / (max_value – min_value)
The normalized data will be: 0, 0.25, 0.5, 0.75, 1
For example: I applied the formula =(B2-MIN(B$2$13))/(MAX(B$2$13)-MIN(B$2$13)) to normalize my result and obtained 0.5.
- Standardization: Standardization, also known as “z-score,” involves transforming data so that it has a mean of 0 and a standard deviation of 1. To standardize data in Excel, you can use the following formula:
=(value – mean) / standard_deviation
“value” is the value you want to standardize.
“mean” is the mean of the dataset.
“standard_deviation” is the standard deviation of the dataset.
Simple technique: directly use the mean and standard deviation you have previously calculated (the Z-score for each value).
Enter the following formula: =(A1-$B$1)/$B$2
A1 refers to the first value in your data.
$B$1 refers to the cell containing the mean (the $ sign locks the cell reference, so it does not change when you copy the formula down).
$B$2 refers to the cell containing the standard deviation.
Example – B2 is my first value, B$14 is my mean, and B$18 is my standard deviation, which gives:
Step 4: Copy the formula down for the other values.
I extend the formula in question and I get my Z-score.
In my case:
To go further, I can recommend the video by Wise Cat Noro Boris which offers very comprehensive information if you want to fully master data standardization:
https://www.youtube.com/watch?v=T1rNgua_E_A