Как вернуть упорядоченные уникальные данные в Excel?

В прошлом году я задал вопрос на SuperUser по аналогичной проблеме. Ответ был отличным, и формула работала так, как я ожидал. Недавно меня попросили добавить в список несколько дополнительных столбцов.

Мои входные данные теперь выглядят так:

Имя Секс Общий Событие Команда Тейлор ж 268 Событие1 Команда А Тейлор ж 252 Событие 2 Команда А Галле ж 251 Событие 1 Команда С христианин м 410 Событие 2 Команда Д абв м 216 Событие 3 Команда Е

Я пытаюсь вернуть список N лучших уникальных мужчин и женщин по общему количеству, с именем, общим количеством, событием и командой.

Функция, которая была предоставлена в прошлом году, выглядит так

=LET(
    data, $A$1:$C$15,
    criteria, "f",
    topN, 3,
    filteredData, FILTER(data, INDEX(data, , 2) = criteria),
    uniqueNames, UNIQUE(INDEX(filteredData, , 1)),
    totals, SUMIFS(INDEX(data, , 3), INDEX(data, , 1), uniqueNames, INDEX(data, , 2), criteria),
    maxTotals,MAXIFS(INDEX(data,,3),INDEX(data,,1),uniqueNames,INDEX(data,,2),criteria),
    sortedNames, INDEX(uniqueNames, MATCH(LARGE(totals, SEQUENCE(topN)), totals, 0)),
    sortedTotals, INDEX(maxTotals, MATCH(LARGE(totals, SEQUENCE(topN)), totals, 0)),
    HSTACK(SEQUENCE(topN), sortedNames, sortedTotals)
)

Теперь мне нужно также включить и событие, и команду, связанную с человеком, и их общее количество. Я пытался обновить функцию, но не смог заставить ее работать. Я не могу понять, как ссылаться на имя и общую сумму, возвращаемую в операторе LET(). Я полагаю, что мне нужно использовать либо INDEX/MATCH, либо XLOOKUP с несколькими критериями, но я не могу понять, как структурировать формулу.

Спасибо

Пожалуйста, сделайте этот вопрос отдельным, без необходимости просмотра предыдущего вопроса. Вы все еще можете сохранить ссылку, но представьте, если ссылка сломалась.

— 09.07.2024 02:40

excel excel-formula

09.07.2024 02:23

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

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

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

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

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

Ответы 2

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

Получите топ уникальных

=LET(list,A1:E21,top,H1,gender,H2,name_col,1,total_col,3,gender_col,2,
        return_cols,{1;3;4;5},include_headers,1,
    d,DROP(list,1),
    f,FILTER(d,CHOOSECOLS(d,gender_col)=gender),
    s,SORT(f,total_col,-1),
    n,CHOOSECOLS(s,name_col),
    u,CHOOSEROWS(s,XMATCH(UNIQUE(n),n)),
    t,TAKE(u,top),
    h,IF(include_headers,VSTACK(TAKE(list,1),t),t),
    r,CHOOSECOLS(h,return_cols),
    r)

Замените последнюю r любой другой переменной, чтобы увидеть, что она содержит.

РЕДАКТИРОВАТЬ

Чтобы включить столбец «Ранг», вы можете использовать:

=LET(list,A1:E21,top,H1,gender,H2,name_col,1,total_col,3,gender_col,2,
        return_cols,{1;3;4;5},include_headers,1,rank_title,"Rank",include_rank,1,
    d,DROP(list,1),
    f,FILTER(d,CHOOSECOLS(d,gender_col)=gender),
    s,SORT(f,total_col,-1),
    n,CHOOSECOLS(s,name_col),
    u,CHOOSEROWS(s,XMATCH(UNIQUE(n),n)),
    t,TAKE(u,top),
    h,IF(include_headers,VSTACK(TAKE(list,1),t),t),
    rh,CHOOSECOLS(h,return_cols),
    r,IF(include_rank,HSTACK(IF(include_headers,
        VSTACK(rank_title,SEQUENCE(top)),SEQUENCE(top)),rh),rh),
    r)

Отлично! Спасибо. На самом деле это намного проще, чем то, как это делалось раньше.

— 10.07.2024 15:58

09.07.2024 06:04

Еще один вариант, для развлечения:

=LAMBDA(data, topN_, crit_1_name, crit_1_val,
    LET(
        header, TAKE(data, 1),
        data, DROP(data, 1),
        data_cols, LAMBDA(name_,
            LET(
                col_indices, XMATCH(name_, header),
                comment, "For SUMIFS(range etc. CHOOSECOLS cannot be used",
                IF(
                    COUNT(col_indices) = 1,
                    INDEX(data, , col_indices),
                    CHOOSECOLS(data, col_indices)
                )
            )
        ),
        name, data_cols("Name"),
        name_totals, HSTACK(
            name,
            SUMIFS(
                data_cols("Total"),
                name, name,
                data_cols(crit_1_name), crit_1_val
            )
        ),
        u_name_totals, TAKE(
            SORT(
                UNIQUE(
                    FILTER(name_totals, data_cols(crit_1_name) = crit_1_val)
                ),
                2,
                -1
            ),
            topN_
        ),
        list_values_for_persons, LAMBDA(persons, col_name,
            MAP(
                persons,
                LAMBDA(person,
                    ARRAYTOTEXT(
                        FILTER(
                            data_cols(col_name),
                            (name = person) *
                                (data_cols(crit_1_name) = crit_1_val)
                        )
                    )
                )
            )
        ),
        u_names, TAKE(u_name_totals, , 1),
        HSTACK(
            u_name_totals,
            list_values_for_persons(u_names, {"Event", "Team", "Total"})
        )
    )
)(A1:E6, topN_, "Sex", "f")

09.07.2024 14:29