Name: Tremplin Numérique
Price range: $$$

Les fonctions sont la force motrice de toute feuille de calcul Microsoft Excel, générant une seule valeur ou un tableau de résultats en fonction de certains arguments que vous saisissez. Cependant, la combinaison de fonctions – ou la nidification – vous permet de profiter de plus de capacités de fonction en même temps.

Cet article couvre à la fois l'héritage et les fonctions Excel modernes, qui sont toutes disponibles dans Excel pour le Web ou Excel pour Microsoft 365. Si vous utilisez une ancienne version d'Excel, vous ne pourrez peut-être pas accéder à certaines des fonctions.

Sommaire

1

Index avec XMatch: effectuez des recherches polyvalentes

L'une des paires de fonctions les plus utilisées et les plus appréciées d'Excel a longtemps été l'index avec Match. Cependant, la combinaison d'index avec XMatch, la version moderne de son prédécesseur moins polyvalent, vous offre encore plus d'options.

En bref, la fonction d'index extrait une valeur d'un ensemble de données, la fonction XMatch servant de navigateur à la ligne et / ou à la colonne correcte. Cette combinaison peut rechercher dans n'importe quelle direction, fonctionne avec des ensembles de données verticaux et horizontaux, et peut même renvoyer des colonnes ou des lignes entières.

La syntaxe d'index est la suivante:

=INDEX(a,b,c)

où

un est la gamme,
b est le numéro de ligne, et
c est le numéro de colonne.

Donc, dans cet exemple, si je voulais trouver le score total de Nathaniel, je taperais:

=INDEX(T_Scores,5,6)

Parce que Nathaniel est dans la cinquième rangée de la table T_Scores, et son score total est dans la sixième colonne.

Cependant, en tapant le numéro de ligne (5) manuellement pour l'argument b Vaince le point d'une recherche complètement parce que, dans le processus d'identification de la rangée de Nathaniel, j'aurais pu simplement regarder son score. Au lieu de cela, je peux remplacer le numéro de ligne « 5 » dans la formule ci-dessus par une formule XMatch imbriquée, qui alimente automatiquement le numéro de ligne dans la fonction d'index.

La syntaxe de XMatch est la suivante:

=XMATCH(a,b,c,d)

où

un est l'article à rechercher,
b est la plage de recherche,
c est le type de correspondance (0 = correspondance exacte (par défaut); -1 = correspondance exacte ou le plus petit élément le plus petit; 1 = correspondance exacte ou le plus grand élément le plus grand; 2 = une correspondance générique), et
d est le mode de recherche (1 = d'abord pour durer (par défaut), -1 = dernier au premier, 2 = recherche binaire où b est dans l'ordre croissant, -2 = recherche binaire où b est en ordre décroissant).

Donc, maintenant, toute la formule se lit comme suit:

=INDEX(T_Scores,XMATCH(H2,T_Scores(Player),0,1),6)

Lorsque la formule XMatch imbriquée remplace le numéro de ligne inséré manuellement en identifiant la ligne dans la colonne Player de la table T_Score où la valeur en H2 est trouvée.

En fait, j'aurais pu omettre les deux derniers arguments de la formule XMatch, car la correspondance exacte (0) et une recherche de haut en bas (1) sont les paramètres par défaut:

=INDEX(T_Scores,XMATCH(H2,T_Scores(Player)),6)

Maintenant, lorsque je sélectionne un autre lecteur dans Cell H2, la partie XMatch de la combinaison index-xmatch recherche le nom du lecteur dans le tableau et indique à la fonction d'index sur quelle ligne ce nom est. Ensuite, la fonction d'index va à la colonne 6 et renvoie le score correspondant.

Ok, c'est génial, mais que se passe-t-il si je veux renvoyer une valeur d'une autre colonne, comme dans la capture d'écran ci-dessous?

Pour le moment, la référence à la sixième colonne est codée en dur dans la formule, donc je devrais modifier la formule pour trouver une valeur d'une colonne différente. Heureusement, Xmatch peut encore une fois à la rescousse:

=INDEX(T_Scores,XMATCH(H2,T_Scores(Player)),XMATCH(I2,T_Scores(#Headers)))

Maintenant, j'ai créé une recherche bidirectionnelle où la première formule XMatch imbriquée identifie le numéro de ligne où la valeur de la cellule H2 peut être trouvée, et la deuxième formule XMatch imbriquée identifie le numéro de colonne où la valeur de la cellule I2 peut être trouvée.

Je peux voir que cela fonctionne lorsque je sélectionne un autre joueur dans Cell H2.

2

Si avec et et: renvoie une valeur basée sur plusieurs conditions

En soi, la fonction IF Excel applique une condition, renvoyant une valeur si la condition est remplie et une autre si ce n'est pas le cas. Cependant, parfois, vous devrez peut-être saisir plus d'une condition, et c'est là que les fonctions et ou ou les fonctions peuvent aider.

La syntaxe pour IF est la suivante:

=IF(a,b,c)

où

un est le test logique (la cellule ou les cellules que vous souhaitez évaluer et la condition que vous souhaitez définir),
b est la valeur à retourner si la condition est remplie, et
c est la valeur à retourner si la condition n'est pas remplie.

Dans cet exemple, taper:

=IF((@ExpYears)>5,"Y","N")

dans la cellule C2 tests, que l'employé A ait plus de cinq ans d'expérience, retournant « y » s'ils le font, ou « n » s'ils ne le font pas. Parce que la formule est une table Excel, lorsque j'appuie entre en entrée, la même formule s'applique à chaque ligne.

En prenant ce principe un peu plus loin, dans ce deuxième exemple, tout employé avec plus de cinq ans d'expérience et La certification GOLD doit être étiquetée « senior » dans la colonne de statut, avec tous les autres étiquetés « Junior ».

Dans ce cas, l'argument de test logique de la formule If sera une formule imbriquée:

=AND(a,b)

où

un est le premier test logique, et
b est le deuxième test logique – et vous pouvez avoir jusqu'à 255 au total.

Ainsi, la formule combinée est la suivante:

=IF(AND((@ExpYears)>5,(@Cert)="Gold"),"Senior","Junior")

Tout texte en arguments logiques dans Excel doit être enfermé en doubles devis. Les seules exceptions à cette règle sont vraies et fausses, auxquelles nous arriverons sous peu.

Ainsi, c'est le résultat lorsque j'entre la formule dans la cellule D2 et appuyez sur Entrée pour l'étendre dans la colonne d'état:

Dans l'exemple ci-dessus, la fonction et évalue les cellules référencées et ne renvoie un résultat positif que si tous Les critères sont remplis. Cependant, vous pouvez également retourner un résultat positif si au moins un des critères est rempli.

Par exemple, ici, un employé a le potentiel d'être un manager s'il a eu au moins 10 ans d'expérience ou avoir la certification Gold.

Pour ce faire, j'ai besoin de nicher la fonction OR dans la formule if:

=OR(a,b)

où

un est le premier test logique, et
b est le deuxième test logique – et vous pouvez avoir jusqu'à 255 au total.

Alors, voici la formule que je vais entrer dans la cellule D2:

=IF(OR((@ExpYears)>=10,(@Cert)="Gold"),TRUE,FALSE)

Voici à quoi ressemble la table lorsque j'appuie sur Entrée:

Parce que j'ai utilisé des valeurs booléennes (true et false) dans les deux derniers arguments de la formule IF, je peux transformer les cellules dans la colonne D en cases à cocher, où True est une case à cocher à cocher, et False est une case à cocher non contrôlée.

Pour ce faire, après avoir sélectionné toutes les cellules de la colonne Manpot, je peux cliquer sur « cocher » dans l'onglet Insert du ruban.

3

Unique avec Filtre: Renvoyez un tableau raffiné sans doublons

La fonction unique d'Excel renvoie une liste de valeurs uniques dans une plage, tandis que la fonction filtrante vous permet de filtrer une plage en fonction des conditions que vous définissez.

Commençons par la fonction de filtre. Voici la syntaxe:

=FILTER(a,b,c)

où

un est la plage de filtre,
b est la plage et l'état définissant le filtre, et
c est ce qu'il faut retourner si le filtre ne renvoie aucune valeur.

Ainsi, par exemple, la saisie:

=FILTER(T_Shops(Manager),T_Shops(Type)=F1,"No result")

dans la cellule F2 recherche la colonne de type de la table T_Shops pour « DIY » (la valeur dans la cellule F1) et renvoie tous les noms correspondants de la colonne Manager de la même table. Si le filtre ne renvoie aucune valeur, la cellule F2 affiche « aucun résultat ».

Cependant, vous pouvez voir que Lucy et Vic sont dupliqués dans le résultat car ils gèrent plus d'un magasin de bricolage.

C'est là que la fonction unique fait son travail. Tout d'abord, regardons la syntaxe:

=UNIQUE(a,b,c)

où

un est la plage à partir de laquelle les valeurs uniques seront renvoyées,
b est vrai si vous souhaitez retourner des colonnes uniques ou omis si vous souhaitez retourner des lignes uniques, et
c est vrai si vous souhaitez renvoyer toutes les valeurs uniques qui se produisent exactement une fois ou omises si vous souhaitez renvoyer toutes les valeurs uniques qui se produisent un nombre de fois.

Ici, tapant:

=UNIQUE(T_Shops(Manager))

dans Cell H2 renvoie les noms de toutes les personnes dans la colonne des gestionnaires des T_Shops sans duplication de noms qui apparaissent plus d'une fois.

Ainsi, parce que la formule de filtre I entrée plus tôt a renvoyé des résultats dupliqués, je peux le combiner avec la fonction unique pour éviter que les valeurs ne soient dupliquées. En fait, je peux simplement envelopper la formule du filtre à l'intérieur de la fonction unique comme suit:

=UNIQUE(FILTER(T_Shops(Manager),T_Shops(Type)=F1))

Allez plus loin et enveloppez la combinaison de filtre unique à l'intérieur de la fonction de tri pour réorganiser le résultat.

4

Eomont avec séquence: générer une série de dates de fin de mois

Que vous cherchiez à rationaliser les rapports financiers, le budget pour l'année à venir ou que vous planifiiez un projet à venir, la combinaison de l'éomont – qui renvoie le dernier jour d'un mois avant ou après une date de début – avec une séquence – qui vous permet de créer une séquence de valeurs (ou dans ce cas, des dates) sont des dates dynamiques, récentes et récentes de temps pour créer une liste de dates de fin de mois.

Avant de commencer, assurez-vous que toutes les cellules où les dates vont contenir la mise en forme du numéro de date. Pour ce faire, sélectionnez les cellules, appuyez sur Ctrl + 1 pour lancer la boîte de dialogue Format Cellules et choisissez « Date » ou « Custom » dans le champ de catégorie. Sinon, vous verrez une séquence de numéros de série liés à la date, plutôt que des dates, dans le résultat.

La fonction Eomonth est simple, ne nécessitant que deux arguments:

=EOMONTH(a,b)

où

un est la date de début, et
b est le nombre de mois avant ou après la date de début, avec un nombre positif renvoyant une date à l'avenir, et un nombre négatif renvoyant une date dans le passé.

Ici, tapant:

=EOMONTH(C1,B2)

Prend la date dans la cellule C1 (15 août), saute le nombre de mois à l'avance ou vers l'arrière en fonction de la valeur dans la cellule B2 (trois mois en novembre) et renvoie la date de fin du mois résultant (30 novembre).

Cependant, disons que je veux créer une séquence de dates de fin de mois après une date de début donnée. C'est là que la fonction de séquence, qui a quatre arguments, est utile:

=SEQUENCE(a,b,c,d)

où

un est le nombre de lignes à retourner,
b est le nombre de colonnes à retourner,
c est le premier nombre de la séquence, et
d est l'incrément entre chaque valeur dans la séquence.

Par exemple, taper:

=SEQUENCE(10,1,1,2)

Dans la cellule A1, renvoie une séquence de nombres impairs à 10 rangées à 10 rangées à partir de 1.

La fonction de séquence produit un tableau dynamique, alors assurez-vous qu'il y a suffisamment de place dans votre feuille de calcul pour que le résultat se répand dans les cellules adjacentes pour éviter le #spill! erreur. De plus, les tableaux dynamiques ne sont pas compatibles avec les tables Excel, donc la formule que vous tapez doit être dans une cellule ordinaire.

Pour combiner ces fonctions, la fonction de séquence agit comme un argument b (le nombre de mois avant ou après) dans la fonction de l'éomont.

Ici, après avoir tapé la date de début dans la cellule A1, voici la formule que j'ai besoin pour taper dans la cellule A2 pour obtenir les dates de fin de mois restantes pour 2025:

=EOMONTH(A1,SEQUENCE(11))

où A1 est la référence cellulaire de la date de début, et 11 est le nombre de dates de fin de mois suivantes que je souhaite retourner.

Disons que je voulais plutôt retourner les dates de fin de mois pour chaque deuxième mois en 2025. Dans ce cas, la formule de la cellule A2 serait:

=EOMONTH(A1,SEQUENCE(6,,,2))

avec le premier argument de la formule de séquence disant à Excel de renvoyer six rangées de résultats, et le quatrième argument représentant un incrément de deux mois entre chaque valeur.

Dans un dernier exemple, au lieu d'avoir la séquence de date de fin de mois qui s'étend sur la colonne A, je veux qu'elles fonctionnent le long de la ligne 1. Ainsi, après avoir tapé la date de début dans la cellule A1, la formule de la cellule B2 doit être:

=EOMONTH(A1,SEQUENCE(,6,,2))

avec le deuxième argument de la formule de séquence forçant Excel à retourner six colonnes de résultats et l'argument final retournant tous les deux mois.

Ce couple de fonction est dynamique, ce qui signifie que la séquence s'ajuste en conséquence si vous modifiez la date de début. Cependant, si vous souhaitez réparer les dates une fois que vous avez utilisé l'éomont et la séquence pour créer votre séquence de fin de mois, sélectionnez toutes les cellules contenant les dates (y compris la date de début), appuyez sur Ctrl + C pour les copier et appuyez sur Ctrl + Shift + V pour coller les valeurs uniquement.

Dans ce guide, j'ai parlé de différent Fonctions qui fonctionnent bien ensemble dans Excel. Cependant, la fonction que vous nid peut être la même comme fonction principale qui entraîne une formule. Par exemple, lorsque vous utilisez la fonction xlookup pour récupérer une valeur à partir d'une certaine ligne, vous pouvez nidiquer un XOVEUP supplémentaire pour rendre votre choix de variables plus dynamique, créant une recherche bidirectionnelle puissante.