Сопоставьте и перечислите значения в динамическом списке, включающем несколько критериев столбца

0 А Б С Д Э Ф г ЧАС я Дж К л М Н 1 Продукты Магазин 2023-С 2023-М 2024-С 2024-М 2 3 Продукт А магазин3 80 2% 500 22% ColCrit1 ColCrit2 искомый_массив 4 Продукт Б магазин2 320 23% 180 60% Продукт Д магазин1 2024-е 5 Продукт Б магазин1 90 8% 300 36% Продукт Г магазин3 return_array 6 Продукт С магазин3 500 15% 657 16% Продукты 7 Продукт Д магазин1 160 17% 500 15% 8 Продукт Д магазин1 500 30% 600 8% список соответствовать 9 Продукт Д магазин1 130 4% 300 4% 830 Продукт Г 10 Продукт Е магазин2 75 10% 450 10% 600 Продукт Д 11 Продукт F магазин4 60 8% 370 4% 600 Продукт Г 12 Продукт F магазин1 500 11% 850 4% 500 Продукт Д 13 Продукт Г магазин3 350 8% 150 15% 300 Продукт Д 14 Продукт Г магазин3 60 47% 600 7% 300 Продукт Г 15 Продукт Г магазин3 90 25% 830 35% 300 Продукт Г 16 Продукт Г магазин2 390 9% 325 13% 150 Продукт Г 17 Продукт Г магазин3 170 30% 300 9% 18 Продукт Г магазин3 320 5% 300 12% 19 Продукт Н магазин2 935 27% 230 16% 20 Продукт I магазин3 134 18% 600 42%

В Range K9:K16 все значения из Column H, которые соответствуют критериям столбца в Range K4:K6 и Range L4:L6, перечислены на основе этой формулы:

=SORT(
    LET(
    a;COUNTIF(K4:K6;A1:A20)+AND(K4:K6 = "");
    b;COUNTIF(L4:L6;C1:C20)+AND(L4:L6 = "");
    FILTER(FILTER(A1:J20;(A1:J1=N4);"");a*b;""));;-1)

В Range L9:L16 я хочу:

сопоставьте значения в соответствии с результатами Range K9:K16 и
столбец введен как переменная return_array в Cell N6.

Формулы из этого вопроса уже близки к результату:

Опция 1

=CHOOSECOLS(SORT(FILTER(CHOOSECOLS(A3:I20;XMATCH(N4;A1:I1);XMATCH(N6;A1:I1));COUNTIFS(K4:K6;A3:A20;L4:L6;C3:C20));;-1);2)

Вариант 2

=LET(
     a, K9:K16,
     b, A1:I1,
     c, A3:I20,
     d, XLOOKUP(N4,b,c,""),
     MAP(a,LAMBDA(α, @DROP(TOCOL(FILTER(IFS((d=α)*
                    (1-ISNA(XMATCH(A3:A20,K4:K6)+XMATCH(C3:C20,L4:L6))),
                    IF(c=0,x,c)),N6=b,""),3),
     COUNTIF(K9:α,α)-1))))

Однако, когда, например, Range K4:K6 или Range L4:L6 пусто и список в диапазоне K9:K16 подстраивается под него, обе формулы для Range L9:L16 возвращают ошибку #CALC!?

Как мне нужно их изменить, чтобы они настраивались на основе списка в Range K4:K6?

Также лучше уточнить, как вы хотите применять ColCrit1 и ColCrit2. например вам нужны строки, где (Product = D and Shop =1) OR (Product = G and Shop =3) или где (Product = D or G) AND (shop = 1 or 3).

— 07.07.2024 15:48

COUNTIF(K4:K6;A1:A20) * COUNTIF(L4:L6;C1:C20) означает (Product = D or G) AND (shop = 1 or 3); в то время как COUNTIFS означает (Product = D and Shop =1) OR (Product = G and Shop =3). Я заметил, что все полученные вами ответы предполагают первое. Надеюсь, это то, чего вы хотите.

— 07.07.2024 15:53

excel excel-formula

07.07.2024 15:24

Преобразование HTML-таблицы в профессиональный документ Excel

Это самый простой способ создания Excel из HTML-таблицы.

Импорт excel в laravel в базу данных

Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Я думаю, вы имеете в виду, если пусто, чем перечислить все? В таком случае: =SORT(FILTER(CHOOSECOLS(A3:I20,XMATCH(N4,A1:I1),XMATCH(N6,A1:I1)),(AND(K4:K6 = "")+COUNTIF(K4:K6,A3:A20))*(AND(L4:L6 = "")+COUNTIF(L4:L6,C3:C20)),""),,-1)

Или:

=LET(x,LAMBDA(a,b,AND(a = "")+COUNTIF(a,b)),
SORT(FILTER(CHOOSECOLS(A3:I20,XMATCH(N4,A1:I1),XMATCH(N6,A1:I1)),x(K4:K6,A3:A20)*x(L4:L6,C3:C20)),,-1))

07.07.2024 15:34

Возможно, я не совсем ясно понял, если оба ColCrits пусты, то почему бы не вернуть выходные данные на основе lookup_array и return_array, одновременно сопоставляя list соответственно, как возврат всего столбца H помогает в этом сценарии, не отклоняясь от других условий ?:

=LET(
     a, K9:K16,
     b, A1:I1,
     c, A3:I20,
     d, XLOOKUP(N4,b,c,""),
     e, (1-ISNA(XMATCH(A3:A20,K4:K6)*XMATCH(C3:C20,L4:L6))),
     MAP(a,LAMBDA(α, @DROP(TOCOL(FILTER(IFS(IF(SUM(e)=0,(d=α),(d=α)*e),
                                            IF(c=0,x,c)),N6=b,""),3),
     COUNTIF(K9:α,α)-1))))

Обновлять:

=LET(
     a, K9:K16,
     b, A1:I1,
     c, A3:I20,
     d, XLOOKUP(N4,b,c,""),
     e, (1-ISNA(XMATCH(A3:A20&"|"&C3:C20,K4:K6&"|"&L4:L6))),
     IFERROR(MAP(a,LAMBDA(α, @DROP(TOCOL(FILTER(IFS(IF(SUM(e)=0,(d=α),(d=α)*e),
                                                    IF(c=0,x,c)),N6=b,""),3),
     COUNTIF(K9:α,α)-1))),""))

Решение работает, когда CoCrti1 и ColCrit2 пусты. Если хотя бы один из них пуст, список не соответствует Ragne K9:K16.

— 07.07.2024 16:00

@Мичи, видишь обновление, это то, о чем ты просишь?

— 07.07.2024 16:13

07.07.2024 15:50