Connaissez-vous l'astuce de créer une référence à un en-tête de colonne dans la dernière cellule non vide d'une ligne ? Le meilleur : vous n'avez pas besoin de lignes ou de colonnes auxiliaires. C'est si facile:
Combinez les quatre fonctions SIERREUR (), INDEX (), MAX () et SI ()
Le directeur général des ventes vous enverra une liste des contrats conclus par mois pour des produits en retrait . Vous devez utiliser une formule dans la colonne N pour spécifier le dernier mois de vente pour chaque produit - sans aucune ligne ou colonne auxiliaire. Si aucun autre contrat n'a été conclu, saisissez une cellule vide dans la colonne N.
Cet exemple, banal à première vue, s'avère être un écrou difficile à casser sans l'utilisation de lignes ou de colonnes auxiliaires. comme Excel pour la pratique-Les lecteurs craquent la noix ! Nous avons la formule matricielle suivante dans la cellule pour la résolution de problèmes N2 créé :
{= IFERREUR (INDEX ($ B $ 1 : $ M $ 1; MAX (SI (B2: M2 ""; COLONNE (B2: M2) -1; -1))); "")}
Jetez un œil à son fonctionnement étape par étape
Commencez par la condition IF () intégrée dans la formule matricielle : {= IFERREUR (INDICE ($ B $ 1 : $ M $ 1; MAX (SI (B2 : M2 "", COLONNE (B2 : M2) -1, -1)));"")}
La condition IF () crée une ligne auxiliaire fictive dans la formule matricielle et vérifie les cellules B2 jusqu'à M2qu'ils soient vides ou non. Si une cellule est vide, la valeur -1 est sinon renvoyée via la fonction COLUMN (), le numéro de colonne respectif moins la valeur 1.
La soustraction de 1 est requise dans la formule car la première colonne du tableau ne contient pas le nom du mois, mais le nom du produit. Ci-dessous, vous apprendrez à utiliser la fonction INDEX () pour afficher le nom du mois correspondant, qui - si vous n'avez pas soustrait le 1 - à cause de la colonne supplémentaire utilisée UNE. serait incorrect d'exactement une colonne.
Si toutes les cellules de la plage B2 : M2 sont vides, la valeur -1 (aucune vente de produit) crée une erreur que nous utilisons pour représenter une cellule vide. La ligne auxiliaire active est visible sur la figure ③ à la ligne 3.
Dans l'étape suivante, vous lisez la plus grande valeur avec la fonction MAX (), dans laquelle la condition IF () est imbriquée. Il s'agit de la valeur 12 de la ligne 3 (colonne 13 moins 1; voir la ligne auxiliaire de la figure ③) :
{= IFERREUR (INDICE ($ G$ 1 : $ M $ 1 ;MAX (SI (B2 : M2 "", COLONNE (B2 : M2) -1, -1)));"")}
Vous passez cette valeur MAX à la fonction INDEX(). Le mois calendaire correspondant est ensuite lu à la ligne 1. La zone de données de la fonction INDEX() est la zone $ G $ 1 : $ M $ 1. La valeur MAX transmise - dans l'exemple 12 - signifie que la douzième valeur de la liste, c'est-à-dire déc pour le mois de décembre :
{= SIERREUR(INDICE ($ G$ 1 : $ M$ 1; MAX (SI (B2: M2 ""; COLONNE (B2: M2) -1; -1)));"")}
Si toutes les cellules de la plage de mois du tableau sont vides, la plus grande valeur est -1 (voir la condition IF () au début). Si la valeur -1 est passée à la fonction INDEX (), cela conduit inévitablement à une valeur d'erreur, car la zone de liste de la fonction INDEX () ne contient que douze entrées et ne peut donc pas trouver l'entrée -1. Vous interceptez cette valeur d'erreur avec la fonction SIERREUR () et renvoyez à la place une chaîne vide. Dans l'exemple, c'est le cas pour le produit C de la ligne 4, car plus aucun contrat n'a pu être conclu pour ce produit :
{=SIERREUR(INDICE ($ G$ 1 : $ M$ 1; MAX (SI (B2: M2 "", COLONNE (B2: M2) -1, -1)));"")}
Puisqu'il s'agit d'une formule matricielle, complétez la saisie de la formule avec la combinaison de touches Ctrl + Maj + Entrée.
