Comment combiner ces deux formules:
Formule 1:
Split Range
H I J . . M
-------------------------------------------------
Grapes Grapes . .
Peach Peach . .
. . . .
. . . Watermelon
.
.
.
Watermelon
Cette première formule saisit toutes les valeurs de la plage d'entrée C3: H14 (colonnes A à F dans cet exemple) pour filtrer les doublons, puis les résultats sont aplatis et triés dans une seule colonne, cette plage aplatie est ensuite soustraite de RangeNamed1 et donc le result est une plage dans une seule colonne de valeurs qui n'apparaissent pas dans la plage C3: H14.
=ARRAYFORMULA(IFNA(VLOOKUP(TRANSPOSE(SEQUENCE(ROUNDUP(COUNTA(H3:H15)/4);4;ROW(H3:H15)));{ROW(H3:H15)\H3:H15};2;0)))
Formule 2:
Confrontation range RangedName1 Result
A B . F G H
------------------------------------------------------
Mango Banana ... Apple X Apple Grapes
Banana Carrot ... Orange X Banana Peach
X Carrot .
X Orange .
Peach Watermelon
X Mango
Grapes
.
.
Watermelon
Cette deuxième formule divise le résultat de la formule 1 (dans cet exemple, la colonne H) en plusieurs colonnes toutes les 4 lignes.
Et donc
=FILTER(INDIRECT("RangedName1");ISERROR(MATCH(INDIRECT("RangedName1");FILTER(SORT(UNIQUE(flatten(C3:H14));1;1); SORT(UNIQUE(flatten(C3:H14));1;1)<>"");0)))
Lorsque j'essaie d'utiliser la formule 1 dans la formule 2 (en remplaçant H3: H15) pour les combiner en une seule formule, cela donne une fonction ARRAY_ROW paramètre 2 dont la taille de ligne ne correspond pas. Attendu: 1. erreur.
Comment les combiner en une seule formule?
Doit être trié par ordre croissant uniquement avant d'être fractionné.
3 Réponses :
J'ai créé un nouvel onglet appelé MK.Help et j'ai mis cette formule dans la cellule A19:
=ARRAYFORMULA(IFERROR(HLOOKUP("X";{"X";QUERY({M2:M\ISNA(MATCH(M2:M;FLATTEN(C2:H15);0))};"select Col1 where Col2=TRUE and Col1<>'' order by Col1")};TRANSPOSE(SEQUENCE(ROUNDUP(SUM(ISNA(MATCH(M2:M;FLATTEN(C2:H15);0))*ISTEXT(M2:M))/4);4;2));0)))
Il utilise une astuce pour «rechercher» la lettre X dans une plage construite constituée de la lettre X placée au-dessus de la requête indiquant les noms corrects. L '[index] de cette RECHERCHEH est un tableau SEQUENCE () qui est défini par le nombre 4, que vous devriez voir deux fois dans la formule. Changez le 4 pour changer la forme des résultats.
pour répartir la colonne J dans les cellules jaunes par lot de 4 par colonne, vous pouvez utiliser:
=ARRAYFORMULA(TRANSPOSE(SPLIT(TRANSPOSE(TRIM(QUERY(QUERY(QUERY(
{(ROUNDDOWN(SEQUENCE(COUNTA(
FILTER(INDIRECT("NamedRange1"); ISERROR(MATCH(INDIRECT("NamedRange1");
FILTER(SORT(UNIQUE(FLATTEN(C2:H15)); 1; 1);
SORT(UNIQUE(FLATTEN(C2:H15)); 1; 1)<>""); 0)))); 1; 0; 1)/N1))+1\
FILTER(INDIRECT("NamedRange1"); ISERROR(MATCH(INDIRECT("NamedRange1");
FILTER(SORT(UNIQUE(FLATTEN(C2:H15)); 1; 1);
SORT(UNIQUE(FLATTEN(C2:H15)); 1; 1)<>""); 0)))};
"select max(Col2) group by Col2 pivot Col1"); "offset 1"; 0);; 9^9))); " ")))
notez que N1 est une variable d'ensembles. peut être facilement modifié selon la distribution de réseau de votre choix ou directement codé en dur sur 4 si vous le souhaitez
ou essayez comme ceci:
=ARRAYFORMULA(TRANSPOSE(SPLIT(TRANSPOSE(TRIM(QUERY(QUERY(QUERY(
{(ROUNDDOWN(SEQUENCE(COUNTUNIQUE(
FILTER(M2:M; NOT(COUNTIF(FLATTEN(C2:H15); M2:M)))); 1; 0; 1)/A19))+1\QUERY(
FILTER(M2:M; NOT(COUNTIF(FLATTEN(C2:H15); M2:M))); "where Col1 is not null")};
"select max(Col2) group by Col2 pivot Col1"); "offset 1"; 0);; 9^9))); " ")))
Si vous partagez une feuille d'échantillons (modifiable), avec des "résultats souhaités" saisis manuellement, je peux vous montrer comment le faire selon une formule.
Ajout d'une feuille de démonstration.