Aide-mémoire sur les formules Excel : Guide avancé

Vous avez donc parcouru les bases, abordé les éléments intermédiaires et vous êtes maintenant prêt à vous battre avec les gros canons - les formules avancées d'Excel ! Excel possède de nombreuses fonctions et formules avancées pour des calculs sophistiqués, il est donc utile d'avoir un guide qui identifie celles dont vous avez besoin.

Cette feuille de triche sur les formules Excel couvre les formules de prévision avancées, l'analyse statistique, les fonctions de manipulation de données, la gestion des erreurs, etc.

Cette référence vous fournira les connaissances nécessaires pour utiliser ces fonctions avancées. Chaque formule est accompagnée d'explications claires, de syntaxe et d'exemples pratiques pour aider les utilisateurs intermédiaires d'Excel à devenir des utilisateurs expérimentés.

Veuillez télécharger et imprimer la feuille de triche et la garder à portée de main.

Ok, commençons.

Excel-formules-cheat-sheet-avancé-1 Télécharger

Tout d'abord, entrons dans les formules Array.

Table des matières

• Formules matricielles
  • 1. Fonction UNIQUE
  • 2. Fonction TRIER
  • 3. Fonction FILTRE
• Randomisation des fonctions Excel
  • 1. Fonction RANDBETWEEN
  • 2. Fonction RANDARRAY
• Formules de prévision avancées dans Microsoft Excel
  • 1. Fonction PREVISION.ETS
  • 2. Fonction TENDANCE
• Formules statistiques avancées
  • 1. Fonction CENTILE
  • 2. Fonction QUARTILE
• Formules avancées d'analyse et de manipulation de données
  • 1. Fonction TRANSPOSER
  • 2. Fonction FRÉQUENCE
  • 3. Fonction GETPIVOTDATA
• Gestion avancée des erreurs
• Formules de recherche avancées
  • 1. Fonction RECHERCHEX
  • 2. Fonction XMATCH
• Dernières pensées

Formules matricielles

Notre aide-mémoire pour débutant vous montre comment trier et filtrer vos données manuellement. Les utilisateurs avancés le font par programmation avec des formules matricielles.

Les formules matricielles vous permettent d'effectuer des calculs sur plusieurs cellules simultanément. Ce sont trois fonctions clés :

1. UNIQUE

2. TRIER

3. FILTRE

Certaines de ces fonctions ne sont disponibles que dans les versions les plus récentes de Microsoft Excel.

1. Fonction UNIQUE

La fonction UNIQUE accepte une plage et renvoie une liste de valeurs uniques.

Supposons que vous disposiez de données sur les ventes d'articles vestimentaires. Pour trouver les éléments uniques dans la colonne A, utilisez cette formule :

=UNIQUE(A2:A6)

2. Fonction TRIER

La fonction SORT trie le contenu d'une plage. La syntaxe est :

TRIER(tableau, [sort_index], [sort_order], [by_col])

• array : la plage de valeurs à trier.

• sort_index : la colonne à trier (1 par défaut)

• sort_order : 1 pour croissant (par défaut) ou 2 pour décroissant).

• by_col : TRUE pour trier par colonne (par défaut) ou FALSE pour trier par ligne.

Les trois derniers arguments sont facultatifs et les valeurs par défaut correspondent généralement à ce que vous souhaitez.

Pour trier les exemples de données par la première colonne, utilisez cette formule :

=TRI(A2:C6)

3. Fonction FILTRE

La fonction FILTER vous permet de filtrer une plage sur une condition spécifique. C'est la syntaxe :

=FILTRE(tableau, inclure, [if_empty])

• array : la plage à filtrer.

• include : la condition qui détermine les valeurs à filtrer.

• if_empty : spécifie ce qu'il faut renvoyer si aucune valeur ne répond aux critères de filtrage (la valeur par défaut est "").

Supposons que vous souhaitiez filtrer les lignes dans les exemples de données pour n'afficher que les endroits où la valeur des ventes est supérieure à 15 $. Utilisez cette formule :

=FILTRE(A2:C6, C2:C6>15)

Randomisation des fonctions Excel

Notre triche intermédiaire montre comment utiliser la fonction RAND qui produit un nombre aléatoire entre 0 et 1.

Les utilisateurs avancés d'Excel savent utiliser les fonctions de randomisation pour générer rapidement des exemples de données.

1. Fonction RANDBETWEEN

La fonction RANDBETWEEN est plus flexible que RAND car vous pouvez spécifier les nombres inférieur et supérieur autrement que 0 et 1.

Pour générer des données avec des nombres entre 1 et 100, entrez cette formule dans la cellule A1 :

=RANDENTENTE(1, 100)

Ensuite, copiez la cellule dans autant de lignes et de colonnes que vous le souhaitez. Il faut quelques secondes pour produire une grille de nombres aléatoires :

2. Fonction RANDARRAY

Vous pensez peut-être qu'il serait bien d'éviter la copie manuelle de la fonction RANDBETWEEN. Pour devenir super avancé, vous pouvez utiliser la nouvelle fonction RANDARRAY dans la dernière version de Microsoft Excel.

La syntaxe est :

RANDARRAY([lignes], [colonnes], [min], [max], [nombre entier])

• lignes : nombre de lignes

• colonnes : nombre de colonnes

• min : nombre le plus bas

• max : nombre le plus élevé

• nombre entier : par défaut à TRUE, sinon utilise des nombres décimaux.

Pour générer des nombres aléatoires entre 1 et 100 sur six lignes et neuf colonnes, utilisez cette formule :

=RANDARRAY(6, 9, 1, 100, VRAI)

Formules de prévision avancées dans Microsoft Excel

Les fonctions de prévision d'Excel sont utilisées pour prévoir les valeurs futures en fonction des tendances des données existantes. Ces fonctions aident à identifier des modèles et à projeter des tendances en fonction de vos données.

1. Fonction PREVISION.ETS

L'ancienne fonction PREVISION a été remplacée par un ensemble de fonctions plus récentes dans Excel 2016.

Vous choisissez la fonction en fonction du modèle de prévision spécifique que vous souhaitez. Par exemple, la fonction FORECAST.ETS utilise l' algorithme de lissage exponentiel .

La syntaxe est :

FORECAST.ETS(date_cible, valeurs, chronologie)

• target_date : la date pour laquelle vous voulez une valeur calculée.

• valeurs : les données historiques.

• chronologie : une plage de dates

Supposons que vous ayez des dates du 1er au 5 janvier dans la colonne A et les montants des ventes dans la colonne B. Cette formule prédira le prochain montant des ventes :

=PRÉVISION.ETS(“6 janvier 2023”, B2:B6, A2:A6)

2. Fonction TENDANCE

La fonction TENDANCE projette un ensemble de valeurs basées sur la méthode des moindres carrés. Il renvoie un tableau. La syntaxe est :

TENDANCE(y_connu, [x_connu], [nouveau_x], [const])

• y_connu : plage de valeurs y

• x_connu : plage de valeurs x

• new_x : plage de valeurs calculées

Souvent, les y_connus sont les points de données, tandis que les x_connus sont les dates.

En utilisant les mêmes données que dans l'exemple précédent, vous pouvez entrer la formule ci-dessous dans la cellule C2. Un ensemble de valeurs sera généré.

=TENDANCE(B2:B6, A2:A6)

Formules statistiques avancées

Les fonctions statistiques avancées incluent le calcul des centiles et des quartiles. Certaines fonctions mathématiques sont disponibles pour la rétrocompatibilité, mais il est recommandé d'utiliser les versions les plus récentes.

1. Fonction CENTILE

Cette fonction calcule le pourcentage de points de données qui tombent en dessous d'une valeur particulière. La syntaxe est :

CENTILE.INC(tableau, k)

• array : la plage de cellules

• k : le centile de 0 à 1

Supposons que vous souhaitiez calculer le 70e centile des données de la colonne B. Utilisez cette formule :

=PERCENTILE.INC(B2:B6, 0.7)

2. Fonction QUARTILE

Cette fonction est une variante de la fonction centile mais utilise des trimestres pour diviser les données. C'est la syntaxe :

QUARTILE.INC(tableau, quart)

• array : la plage de données

• quart : 1 pour le 25e centile, 2 pour le 50e, 3 pour le 75e et 4 pour le maximum.

La formule ci-dessous calculera le premier quartile de données dans la colonne A.

=QUARTILE.INC(A2:A5, 1)

Formules avancées d'analyse et de manipulation de données

Plusieurs fonctions avancées vous permettent de changer le format des données, d'analyser les distributions de fréquence et d'extraire les données des tableaux croisés dynamiques.

1. TRANSPOSER

2. FRÉQUENCE

3. GETPIVOTDATA

1. Fonction TRANSPOSER

Parfois, vous souhaitez déplacer les données de vos lignes vers des colonnes et vice versa. Vous pouvez le faire manuellement ou utiliser la fonction TRANSPOSE à la place.

Supposons que vous ayez les éléments « T-shirt », « Sweat à capuche » et « Jeans » dans les cellules A2, A3 et A4. Vous voulez en faire des en-têtes de colonne. Cette fonction renvoie les valeurs dans une seule ligne :

=TRANSPOSER(A2:A4)

2. Fonction FRÉQUENCE

Cette fonction calcule la distribution de fréquence des valeurs dans un jeu de données. C'est la syntaxe :

FRÉQUENCE(data_array, bins_array)

• data_array : plage de valeurs.

• bins_array : les intervalles à utiliser.

Supposons que vous ayez des données de ventes dans la colonne B et que vous souhaitiez analyser la distribution de fréquence des valeurs en fonction du nombre de montants :

• en dessous de 20.

• de 20 à 80.

• au-dessus de 80.

Cela représente trois bacs et peut être calculé avec cette formule :

=FREQUENCE(A2:A6, {20,80})

Pour en savoir plus sur les distributions de fréquences dans Excel, regardez cette vidéo :

3. Fonction GETPIVOTDATA

Cette fonction vous permet d'extraire des informations résumées à partir de tableaux croisés dynamiques. C'est la syntaxe :

GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], …)

• data_field : le champ de données ou la valeur que vous souhaitez récupérer à partir du tableau croisé dynamique.

• pivot_table : une référence au tableau croisé dynamique.

• champ1, élément1, etc. : les paires champ/élément à filtrer.

Supposons que vous disposiez d'un tableau croisé dynamique basé sur la couleur des articles vendus. Pour extraire les ventes des articles rouges, utilisez cette formule :

=GETPIVOTDATA("Ventes", A1, "Couleur", "Rouge")

Gestion avancée des erreurs

Même les formules Excel les plus élémentaires peuvent produire des erreurs. Les utilisateurs intermédiaires doivent savoir comment utiliser ISERROR pour gérer les erreurs. Les utilisateurs avancés doivent également être familiarisés avec la fonction ERROR.TYPE pour l'identification des erreurs.

La fonction ERROR.TYPE aide à identifier le type spécifique d'erreur dans une cellule ou une formule.

Il renvoie une valeur numérique correspondant à différents types d'erreurs, tels que #N/A, #VALUE!, #REF!, etc.

Supposons que vous ayez une erreur dans la cellule A1 et que vous souhaitiez identifier son type d'erreur. La formule suivante renverra le nombre qui correspond à l'erreur spécifique :

=TYPE.ERREUR(A1)

Vous pouvez combiner cela avec plusieurs fonctions pour répondre différemment selon le type d'erreur. Voici les erreurs les plus courantes et leurs valeurs :

1. #NUL! (aucune cellule commune trouvée dans une plage)

2. #DIV/0 ! ( division par zéro ou cellule vide)

3. #VALEUR! (type de données ou argument inapproprié dans une formule)

4. #REF! (une cellule référencée a été supprimée ou il y a une référence circulaire )

5. #NOM? (Excel ne reconnaît pas la fonction ou la plage)

6. #NUM ! (valeur numérique invalide)

7. #N/A (valeur introuvable)

Supposons que vous souhaitiez gérer trois types d'erreurs spécifiques. Utilisez cette formule pour afficher un message d'erreur spécifique en fonction du type :

=IF(ISERROR(C2), IF(ERROR.TYPE(C2)=2, "Diviser par zéro erreur", IF(ERROR.TYPE(C2)=3, "Erreur de valeur invalide", IF(ERROR.TYPE(C2 )=7, "Erreur de valeur introuvable", "Autre erreur"))), C2)

Formules de recherche avancées

Nos aide-mémoire pour débutants et intermédiaires couvraient une sélection de fonctions de recherche . Voici quelques options avancées :

1. XRECHERCHE

2. XMATCH

1. Fonction RECHERCHEX

Cette fonction de recherche vous permet de rechercher une valeur dans une plage et de renvoyer une valeur correspondante à partir d'une autre colonne ou plage.

Il offre plus de polyvalence que des fonctions de recherche plus simples comme VLOOKUP . C'est la syntaxe :

XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode], [if_not_found])

• lookup_value : la valeur que vous souhaitez rechercher.

• lookup_array : la plage pour la recherche.

• return_array : la plage qui affichera la valeur correspondante.

• match_mode : correspondance exacte (0), prochaine plus petite (1), prochaine plus grande (-1) ou correspondance générique (2).

• search_mode : -1 pour le haut vers le bas, 1 pour le bas vers le haut ou 2 pour la recherche binaire.

• if_not_found : définit la valeur à renvoyer si aucune correspondance n'est trouvée.

Supposons que vous souhaitiez rechercher dans une plage de données la première occurrence d'un vêtement et renvoyer les montants des ventes. Cette formule recherchera le texte "Hoodie" et renverra la valeur dans la cellule adjacente si elle est trouvée :

=XRECHERCHE("Sweat à capuche", A2:A6, B2:B6)

2. Fonction XMATCH

Cette fonction vous permet de trouver la position d'une valeur spécifiée dans une plage ou un tableau. C'est la syntaxe :

XMATCH(lookup_value, lookup_array, [match_type], [search_mode])

• lookup_value : la valeur que vous voulez trouver.

• lookup_array : La plage que vous souhaitez rechercher.

• match_type : correspondance exacte (0), plus petit suivant (-1), plus grand suivant (1).

• search_mode : recherche binaire (1) ou recherche linéaire (2).

Supposons que vous souhaitiez rechercher la première occurrence d'un élément jaune dans une plage de la colonne B. Utilisez cette formule :

=XMATCH("Jaune", B2:B6, 0)

Dernières pensées

Cette feuille de triche a couvert un large éventail de fonctions, de l'analyse statistique, des formules de recherche, des techniques de manipulation de données et des stratégies de gestion des erreurs.

Les exemples et explications fournis aident à démystifier ces formules avancées, les rendant accessibles même à ceux qui ont une expérience limitée.

Au fur et à mesure que vous commencez à les incorporer dans vos tâches Excel, vous êtes sur la bonne voie pour augmenter vos compétences Excel à un niveau avancé.

Mais rappelez-vous, cette feuille de triche n'est que la pointe de l'iceberg. Les choses vraiment étonnantes se produisent lorsque vous faites preuve de créativité, mélangez et associez ces formules et adaptez-les pour résoudre vos défis uniques. Excel est comme une toile, et ces formules sont votre palette - alors allez-y, peignez votre chef-d'œuvre !