Результаты формулы Excel LET в #CALC! Ошибка при применении фильтра
Я пытаюсь создать формулу, которая отображает список из 20 лучших имен сотрудников на основе сгруппированного подсчета. Кроме того, я пытаюсь динамически применить фильтр к указанному списку.
Данные берутся из следующей таблицы NoCstNoAcr, которая состоит из списка пользователей, их компаний и филиалов.
Я могу успешно показать 20 лучших имен пользователей на основе количества файлов, но когда я пытаюсь применить фильтр ветвей (т. е. показать 20 лучших имен пользователей в определенной ветке, сгруппированных по количеству файлов), формула возвращает #CALC! из-за вложенных массивов.
Ниже мой код:
=LET(
branchFilter; D33;
NoCstNoAcrHeaders; NoCstNoAcr[#Headers];
branchIndex; MATCH("Branch Code"; NoCstNoAcrHeaders; 0);
operatorIndex; MATCH("Operator Full Name"; NoCstNoAcrHeaders; 0);
filteredData; IF(ISBLANK(branchFilter);
NoCstNoAcr;
FILTER(NoCstNoAcr; INDEX(NoCstNoAcr[Branch Code];;)=branchFilter));
operatorNames; UNIQUE(INDEX(filteredData;;operatorIndex));
fileCounts; MAP(operatorNames; LAMBDA(name; COUNTIF(INDEX(filteredData;;operatorIndex); name)));
sortedData; SORTBY(HSTACK(operatorNames; fileCounts); fileCounts; -1);
TAKE(sortedData; 20)
)
Фильтр ветвей находится в ячейке D33.
Может ли кто-нибудь помочь мне понять, почему возникает эта ошибка и как исправить формулу, чтобы она работала правильно при применении фильтра ветвей?
Ответы 2
Подсчет уникальных отфильтрованных
- Чтобы сделать его гибким, попробуйте следующее.
=LET(list,NoCstNoAcr[#All],unique_header,"Operator Full Name",
filter_header,"Branch Code",filter_value,D33,top,D34,
h,TAKE(list,1),
d,DROP(list,1),
ud,XLOOKUP(unique_header,h,d),
fd,XLOOKUP(filter_header,h,d),
f,IF(ISBLANK(filter_value),ud,FILTER(ud,fd=filter_value)),
u,UNIQUE(f),
c,BYROW(u,LAMBDA(r,ROWS(FILTER(f,f=r)))),
s,SORTBY(u,c,-1),
r,TAKE(s,top),
r)
- Чтобы воспользоваться преимуществами таблицы Excel, попробуйте следующее.
=LET(filter_value,D33,top,D34,
ud,NoCstNoAcr[Operator Full Name],
fd,NoCstNoAcr[Branch Code],
f,IF(ISBLANK(filter_value),ud,FILTER(ud,fd=filter_value)),
u,UNIQUE(f),
c,BYROW(u,LAMBDA(r,ROWS(FILTER(f,f=r)))),
s,SORTBY(u,c,-1),
r,TAKE(s,top),
r)
- Замените последнее появление
rлюбой другой переменной, чтобы увидеть, что она содержит. Обратите внимание, что переменнаяrв функцииBYROW— это другая переменная. - Переименуйте переменные по своему усмотрению, например:
- ч - заголовки,
- д - данные,
- уд – имена,
- фд - ветки,
- е - отфильтрованные_имена,
- u - отдельные_имена,
- в - считает,
- s - отсортированный,
- р - результат.
Вместо этого с COUNTIFS
Я думаю, что ошибка связана с первым параметром COUNTIF, поскольку он не может быть массивом (только диапазон).
Интересно, могли бы вы попробовать это, чтобы получить fileCounts:
- Используя
COUNTIFS, получите количество совпадений имен и ветвей. - Объедините с именами.
- Получите уникальные строки с именем, количеством.
- Отфильтровать строки с нулевым количеством.
(Пожалуйста, измените , на ;)
=LET(
branchFilter, D33,
fileCounts_step1, UNIQUE(
HSTACK(
NoCstNoAcr[Operator Full Name],
COUNTIFS(
NoCstNoAcr[Operator Full Name], NoCstNoAcr[Operator Full Name],
NoCstNoAcr[Branch Code], IF(
LEN(TRIM(branchFilter)) > 0,
branchFilter,
NoCstNoAcr[Branch Code]
)
)
)
),
fileCounts, FILTER(fileCounts_step1, INDEX(fileCounts_step1, , 2) > 0),
sortedData, SORT(fileCounts, 2, -1),
result, TAKE(sortedData, 20),
IFERROR(result, "No results")
)
Или
=LAMBDA(branchFilter, top,
LET(
fileCounts_step1, UNIQUE(
HSTACK(
NoCstNoAcr[Operator Full Name],
COUNTIFS(
NoCstNoAcr[Operator Full Name], NoCstNoAcr[Operator Full Name],
NoCstNoAcr[Branch Code], IF(
LEN(TRIM(branchFilter)) > 0,
branchFilter,
NoCstNoAcr[Branch Code]
)
)
)
),
fileCounts, FILTER(fileCounts_step1, INDEX(fileCounts_step1, , 2) > 0),
sortedData, SORT(fileCounts, 2, -1),
result, TAKE(sortedData, 20),
IFERROR(result, "No results")
)
)(D33, 20)
Спасибо! Теперь формула работает при наличии фильтра ветвей (D33 НЕ пуст). Однако, если D33 пуст, отображается «Нет результатов».
Ах да, я не был уверен, каким должен быть результат. Я изменил условие в ответе на IF(LEN(TRIM(branchFilter)) > 0, branchFilter, NoCstNoAcr[Branch Code]) - не могли бы вы попробовать и посмотреть, то ли это, что вы хотите?
Работает ли функция
COUNTIF()с массивом? Попробуйте это исправить:MAP(operatorNames; LAMBDA(name; SUM(N(INDEX(filteredData;;operatorIndex)=name))))также не опубликованы образцы данных.