Comment référencer toutes les cellules non vides après la ligne A6 pour une formule ?

Il existe de nombreuses façons de référencer une gamme de taille variable. Il existe également plusieurs façons de trouver le dernier élément non blanc d'une colonne.

Tout d'abord, comment trouver la ligne dont la dernière colonne est non-blanche ? A:A :

Je vais envelopper ces formules dans un formulaire IFERROR qui renverra 6 s'il y a erreur. En effet, vous indiquez que vos données commencent à la ligne 6, ce qui vous permettra, en cas d'erreur, de ne renvoyer que les données de la ligne 6. A6 au lieu d'un message d'erreur. Si vous veulent une erreur à afficher, il suffit alors de supprimer le IFERROR l'emballage.

Si vos données sont toutes des chiffres, utilisez ceci : IFERROR(MATCH(10^308,A:A),6)
Si vos données sont toutes en texte, utilisez ceci : IFERROR(MATCH(REPT(CHAR(255),255),A:A),6)
Si vos données sont des chiffres et du texte, trouvez la valeur maximale des deux : MAX(IFERROR(MATCH(10^308,A:A),6),IFERROR(MATCH(REPT(CHAR(255),255),A:A),6)

Vous pouvez également utiliser une formule de tableau, mais j'ai tendance à ne pas m'en servir car elles sont généralement plus lentes. Il s'agirait de la formule ci-dessous et elle serait entrée avec Ctrl + Shift + Enter . (Vous saurez qu'elle est correcte si des crochets { } apparaissent de part et d'autre de la formule).

MAX(ROW(A:A)*NOT(ISBLANK(A:A)))

Deuxièmement, comment référencer la gamme A6:A{last non blank row} :

Puisque vous voulez la même référence trois fois, il peut être judicieux d'ajouter une cellule d'aide. Il existe de multiples façons d'obtenir ce que nous voulons, mais je vais en montrer quelques-unes. Disons que la cellule d'aide est B1 et la formule que vous voulez est dans B2 . Je vais utiliser la formule de l'exemple "dernière ligne" pour le texte car je pense que c'est ce que sont vos données. Vous pouvez la modifier en fonction de vos besoins.

El INDEX méthode : (probablement la meilleure option)

B1 = IFERROR(MATCH(REPT(CHAR(255),255),A:A),6)
B2 = INDEX(A6:INDEX(A:A,B1),MODE(MATCH(A6:INDEX(A:A,B1),A6:INDEX(A:A,B1),0)))

El INDIRECT método:

B1 = "A6:A"&IFERROR(MATCH(REPT(CHAR(255),255),A:A),6)
B2 = INDEX(INDIRECT(B1),MODE(MATCH(INDIRECT(B1),INDIRECT(B1),0)))

El OFFSET método:

B1 = IFERROR(MATCH(REPT(CHAR(255),255),A:A),6)
B2 =INDEX(OFFSET(A6,0,0,B1-5),MODE(MATCH(OFFSET(A6,0,0,B1-5),OFFSET(A6,0,0,B1-5),0)))