Les fonctions Excel de base sont efficaces pour les calculs simples, mais elles se complexifient rapidement lors d'analyses de données complexes. Vous vous retrouvez avec des formules imbriquées difficiles à lire, de multiples colonnes d'aide qui encombrent votre feuille de calcul et des formules qui peuvent se rompre lorsque vos données changent. C'est là qu'interviennent les formules matricielles dans Excel.
Les formules matricielles vous permettent d'effectuer des calculs sur des plages entières de données dans une seule formule. Par conséquent, vous pouvez Effectuez des recherches ultra-rapides, filtrez et triez avec une seule expression puissante, au lieu d'écrire des formules distinctes pour chaque ligne ou colonne. Excel n’est pas nouveau, mais certaines personnes s’en tiennent aux anciennes méthodes de travail alors que ces fonctions peuvent rendre leur travail plus simple et plus efficace.
Liens rapides
5. XLOOKUP
Surpasse RECHERCHEV à chaque fois.
XLOOKUP est la fonction de recherche qui aurait dû exister dès le départ. Contrairement à VLOOKUP, qui vous oblige à compter les colonnes et recherche uniquement vers la droite, XLOOKUP fonctionne dans toutes les directions et utilise les références de colonnes réelles. Sa syntaxe est la suivante :
=XLOOKUP(valeur_recherchée, tableau_recherché, tableau_retourné, [si_non_trouvé], [mode_correspondance], [mode_recherche])
Voici ce que signifie chaque paramètre :
- valeur_recherche : La valeur spécifique que vous recherchez. Il peut s'agir d'un numéro de pièce, d'un code produit ou de tout autre identifiant de votre ensemble de données.
- tableau de recherche : La plage dans laquelle Excel recherche valeur de recherche Bien à vous. Il s'agit généralement d'une seule colonne ou ligne contenant vos critères de recherche.
- tableau_retour : La plage contenant les valeurs à récupérer. Il peut s'agir d'une seule colonne, de plusieurs colonnes ou même d'une section entière du tableau.
- if_not_found (facultatif) : Texte ou valeur personnalisé à afficher lorsqu'aucune correspondance n'est trouvée. Cela élimine les erreurs #N/A gênantes et vous permet d'afficher « Introuvable » ou « Vérifier le numéro de pièce ».
- match_mode (facultatif) : Contrôle le type de correspondance. Utilisez 0 pour une correspondance exacte (par défaut), -1 pour la correspondance exacte suivante ou inférieure, 1 pour la correspondance exacte suivante ou supérieure et 2 pour une correspondance générique.
- search_mode (facultatif) : Spécifie le sens de recherche. Utilisez 1 pour une recherche du premier au dernier (par défaut), -1 pour une recherche du dernier au premier et 2 pour une recherche binaire sur des données triées.
Prenons l'exemple d'une feuille de calcul d'inventaire mécanique. La formule suivante recherche la référence « BRG-002 » parmi une plage d'identifiants et renvoie les données correspondantes. Si la pièce est absente, le message « Pièce non trouvée » s'affiche au lieu d'une erreur.
=RECHERCHEX("BRG-002", A:A, A:H, "Pièce introuvable")
XLOOKUP vous permet d'extraire des données de différentes colonnes sans les calculs de colonnes fastidieux trouvés dans VLOOKUP, ce qui en fait l'un des plus importants Fonctions Excel pour trouver rapidement des données.
4. SUMPRODUCT
Centrale électrique pour calculs conditionnels
SOMMEPROD permet non seulement d'additionner des nombres, mais aussi de multiplier des matrices et d'additionner leurs résultats. Cela la rend utile pour les calculs conditionnels complexes nécessitant plusieurs colonnes auxiliaires.
Sa formule est la suivante :
=SOMMEPROD(tableau1; [tableau2]; [tableau3]; ...)
ici, tableau1 Il s’agit de la première plage de valeurs à multiplier – généralement votre colonne de données principale, comme les quantités ou les coûts. tableau2 Il s'agit d'une deuxième plage facultative pour la multiplication, qui contient souvent des critères ou une logique conditionnelle utilisant des opérateurs de comparaison.
Ils deviennent plus utiles lorsque nous utilisons des opérateurs logiques dans des tableaux. Par exemple, lorsque nous saisissons des conditions telles que (fournisseur="Siemens"), Excel convertit les résultats VRAI/FAUX en 1/0, ce qui permet d'effectuer des calculs.
Par exemple, la formule suivante calcule la valeur totale des stocks de pièces fournies uniquement par Siemens. Elle multiplie les quantités par les coûts unitaires, mais uniquement pour les lignes où le fournisseur répond aux critères.
=SUMPRODUCT(D2:D100*H2:H100*(G2:G100="Siemens"))
De même, la formule suivante permet de trouver le coût total d'un stock de roulements en bon état :
=SUMPRODUCT((C2:C100="Bearings")*(D2:D100>=15)*H2:H100)
Deux conditions s'appliquent simultanément : la catégorie doit être « Roulements » et les niveaux de stock doivent être de 15 unités ou plus, ce qui nous aide à identifier les catégories de roulements qui ont une couverture de stock suffisante.
Contrairement aux fonctions SOMME traditionnelles avec plusieurs critères, SOMMEPROD ne nécessite pas de structures imbriquées complexes car elle gère plusieurs conditions dans une seule formule lisible. Fonctions SOMME dans Excel, Comme SUMIF et SUMIF, ils sont excellents pour la sommation conditionnelle simple, mais la fonction SUMPRODUCT excelle lorsque vous devez multiplier des valeurs avant de sommer ou gérer des opérations logiques plus complexes.
3. FILTRE
Simplifie l'extraction dynamique des données
FILTER extrait des lignes de votre ensemble de données selon les conditions que vous spécifiez. Contrairement au filtrage manuel, cette fonction génère des résultats dynamiques qui se mettent à jour automatiquement lorsque les données sources changent. La syntaxe de FILTER est la suivante :
=FILTRE(tableau, inclure, [si_vide])
Voici ce que chaque entrée contrôle :
- tableau (plage) : L'ensemble des données que vous souhaitez filtrer. Cela inclut toutes les colonnes souhaitées dans vos résultats, et pas seulement la colonne des critères.
- inclure: Condition logique qui spécifie les lignes à renvoyer – utilise des opérateurs de comparaison pour créer des tableaux VRAI/FAUX pour chaque ligne.
- if_empty (facultatif) : Affiche un message personnalisé lorsqu'aucune ligne ne correspond à vos critères. Évite les erreurs #CALC! et affiche un texte explicite, tel que « Aucun résultat correspondant trouvé ».
La fonction évalue votre condition par rapport à chaque ligne de la plage. Lorsque la condition renvoie VRAI, la ligne entière apparaît dans les résultats filtrés. Voici un exemple tiré d'une feuille de calcul d'inventaire mécanique :
=FILTER(A2:H101, (C2:C101="Bearings")*(G2:G101="Timken"))
Cette formule extrait toutes les lignes dont la ressource est « Timken » et la catégorie « Roulements ». L'astérisque (*) crée une condition ET en multipliant les tableaux logiques.
Lorsque vous ajoutez de nouvelles données à votre plage source, Utilisation de la fonction FILTRE dans Excel Cette méthode est plus judicieuse que le tri manuel et les tableaux temporaires, car les résultats filtrés sont automatiquement mis à jour. Elle est donc utile pour créer des tableaux de bord et des rapports dynamiques.
2. UN GOUT
Extraire des valeurs uniques sans doublons
UNIQUE extrait les valeurs uniques de votre plage de données et évite automatiquement les doublons. Cette fonction est importante pour créer des listes déroulantes, analyser des catégories de données et créer des rapports de synthèse. La formule est :
=UNIQUE(tableau, [par_colonne], [exactement_une_fois])
Voici comment fonctionne chaque entrée :
- tableau (plage) : La plage qui contient les données dont vous souhaitez supprimer les doublons : il peut s'agir d'une seule colonne, de plusieurs colonnes ou d'une section entière du tableau.
- by_col (facultatif) : FAUX compare les lignes pour déterminer leur unicité (valeur par défaut), tandis que VRAI compare les colonnes. Cependant, la plupart des scénarios utilisent la comparaison de lignes par défaut.
- exactement_une fois (facultatif) : FALSE renvoie toutes les valeurs uniques, y compris celles qui apparaissent plusieurs fois (par défaut), et TRUE renvoie uniquement les valeurs qui apparaissent exactement une fois dans l'ensemble de données.
La fonction UNIQUE évalue chaque ligne ou valeur de votre tableau et ne renvoie que la première occurrence de chaque élément unique. L'ordre correspond à la séquence de données d'origine. Voici un exemple :
=UNIQUE(G2:G22)
Cette formule extrait tous les noms de fournisseurs uniques de la colonne Fournisseur G et crée une liste propre et dupliquée. Je l'utilise pour créer des listes déroulantes de fournisseurs ou des rapports récapitulatifs.
Vous pouvez également l'utiliser sur l'ensemble du tableau, comme indiqué ci-dessous :
=UNIQUE(A2:F100)
Renvoie des combinaisons uniques dans toutes les colonnes (A à F), affichant des enregistrements d'inventaire distincts. Si deux pièces ont des valeurs identiques dans chaque colonne, une seule apparaîtra dans les résultats.
Lorsque vous travaillez avec de grands ensembles de données, UNIQUE élimine le processus fastidieux de suppression manuelle des doublons. Les résultats dynamiques sont mis à jour à mesure que de nouvelles données arrivent, et comme UNIQUE crée des matrices de débordement, cette approche élimine le redimensionnement fastidieux des tables grâce à une mise à l'échelle automatique pour prendre en compte toutes les valeurs uniques. Je l'utilise pour maintenir des listes de référence propres et établir des plages de validation de données fiables.
1. TRIER et TRIER PAR
Organisez vos données sans compromettre l'original
Les fonctions SORT et SORTBY organisent les données de manière dynamique tout en préservant la source. SORT gère le tri de base par position de colonne, tandis que SORTBY trie en fonction des valeurs de différentes colonnes, vous offrant ainsi plus de flexibilité pour les tris complexes.
SORT utilise cette structure :
=TRIER(tableau, [index_tri], [ordre_tri], [par_colonne])
Voici ce que chaque paramètre contrôle :
- tableau: La plage de données que vous souhaitez trier comprend toutes les colonnes qui doivent apparaître dans les résultats triés.
- sort_index (facultatif) : Numéro de colonne du tableau à utiliser pour le tri. Utilisez 1 pour la première colonne, 2 pour la deuxième, et ainsi de suite (la valeur par défaut est 1).
- sort_order (facultatif) : Utilisez 1 pour l'ordre croissant (par défaut) et -1 pour l'ordre décroissant.
- by_col (facultatif) : FAUX pour trier par lignes (par défaut), VRAI pour trier par colonnes : la plupart des scénarios utilisent le tri par lignes.
La fonction TRIERPAR prend la forme suivante :
= TRIER (tableau, by_array1, [sort_order1], [by_array2], [sort_order2], ...)
Ses transactions comprennent :
- tableau: La plage de données à trier : similaire à la fonction TRIER, elle contient toutes les colonnes que vous souhaitez dans les résultats.
- par_array1: La plage contenant les valeurs qui déterminent l'ordre de tri peut être n'importe quelle colonne, même en dehors de la plage du tableau principal.
- sort_order1 (facultatif) : 1 pour l'ordre croissant (par défaut), -1 pour l'ordre décroissant.
- by_array2, sort_order2 (facultatif) : Critères de tri supplémentaires pour le tri à plusieurs niveaux.
En examinant un exemple de feuille de calcul d'inventaire mécanique, ces fonctions gèrent des scénarios de tri réels :
=TRI(A2:H22, 4, -1)
Cette formule trie l'ensemble de l'inventaire par niveau de stock, par ordre décroissant, les articles les plus en stock étant affichés en premier. La formule trie par colonne 4 (niveaux de stock) tout en préservant les relations entre les lignes.
J'utilise la fonction TRIERPAR. Au lieu de TRIER, vous pouvez l'utiliser pour mieux contrôler les critères de tri et les niveaux de tri multiples. Par exemple, la formule suivante trie d'abord par ordre alphabétique par catégorie, puis par niveau de stock du plus élevé au plus bas dans chaque catégorie.
= TRI PAR (A2:H22, C2:C22, 1, D2:D22, -1)
Des feuilles de calcul organisées, des résultats plus intelligents
Les formules matricielles éliminent l'encombrement des colonnes d'aide et des fonctions imbriquées qui compliquent la maintenance des feuilles de calcul. Vous disposez de formules uniques gérant plusieurs opérations, rendant les classeurs plus clairs et plus professionnels.
L'un des avantages notables réside dans les fonctions dynamiques, qui permettent aux résultats d'être automatiquement mis à jour lorsque les données sources changent. Cela élimine les mises à jour manuelles et les chaînes de formules erronées, rendant vos feuilles de calcul plus fiables pour les analyses continues.
La bibliothèque de fonctions matricielles d'Excel continue de s'étendre au-delà de ces outils de base. Lorsque je dois combiner des données provenant de plusieurs sources, j'utilise les fonctions VSTACK et HSTACK pour combiner des plages. Ensemble, ces fonctions créent des flux de traitement de données puissants, impossibles à réaliser avec des formules traditionnelles.
