Формула динамического массива для обобщения еженедельных данных (например, сводная таблица)

Попробуйте использовать следующую формулу, используя LAMBDA() вспомогательную функцию MAKEARRAY(), она сгенерирует весь массив, как указано в вашем ОП:

• Формула, используемая в ячейке C1 из Sheet2.

=LET(
     _Data, Sheet1!A2:D15,
     _ArtistSong, CHOOSECOLS(_Data,2,3),
     _Uniq, UNIQUE(_ArtistSong),
     _Date, TAKE(_Data,,1),
     _Output, MAKEARRAY(ROWS(_Uniq),COLUMNS(DROP(C1:F1,,2)), LAMBDA(r,c,
     TOROW(FILTER(TAKE(_Data,,-1), (INDEX(_Uniq,r,1)=INDEX(_ArtistSong,,1))*
     (INDEX(_Uniq,r,2)=INDEX(_ArtistSong,,2))*(INDEX(DROP(C1:F1,,2),c)=_Date),"")))),
     HSTACK(_Uniq, _Output))

Согласно новому обновлению OP:

=LET(
     α, C2:D8,
     δ, E1:F1,
     MAKEARRAY(ROWS(α),COLUMNS(δ),LAMBDA(r,c,
     FILTER(Sheet1!D2:D15,(INDEX(α,r,1)=Sheet1!B2:B15)*
     (INDEX(α,r,2)=Sheet1!C2:C15)*(INDEX(δ,c)=Sheet1!A2:A15),""))))

Еще одно действительно ленивое решение, которое я бы посоветовал использовать с осторожностью:

=CHOOSECOLS(WRAPROWS(TOROW(SORT(A2:D15,{2,3},1,FALSE)),8),{2,3,4,8})

Сначала отсортируйте данные по песне и имени исполнителя так, чтобы строка 1 — это 03.01.1970, строка 2 — 10.01.1970…

Затем используйте WRAPROWS и TOROW, чтобы изменить форму таблицы:

Наконец, используйте CHOOSECOLS, чтобы сохранить соответствующие столбцы.

Отредактировано:

Поскольку эта формула основана на предположении, что каждый (исполнитель + песня) имеет две строки: одну для 03.01.1970, другую для 10.01.1970, если это не так, например, если один из (исполнитель + песня) имеет три даты или одна только одна дата, вся эта формула сломается.

И я увидел, что другие ответы справляются с этим правильно, поэтому решил потратить некоторое время на написание формулы, которая тоже справится с этим.

То есть, если у одного из артистов три свидания, это все равно сработает:

=LET(
    data, A2:D16,
    sorted_dates, SORT(UNIQUE(INDEX(data, , 1), FALSE, FALSE), 1, 1),
    artists, INDEX(data, , 2),
    songs, INDEX(data, , 3),
    unique_artists_and_songs, UNIQUE(HSTACK(artists, songs), FALSE, FALSE),
    rowNum, ROWS(unique_artists_and_songs),
    colNum, ROWS(sorted_dates),
    REDUCE(
        HSTACK("Artist", "Song", TOROW(sorted_dates)),
        SEQUENCE(rowNum, 1, 1, 1),
        LAMBDA(a, i,
            LET(
                artist, INDEX(unique_artists_and_songs, i, 1),
                song, INDEX(unique_artists_and_songs, i, 2),
                filtered, FILTER(data, (artists = artist) * (songs = song), ""),
                dates, INDEX(filtered, , 1),
                ranks, INDEX(filtered, , 4),
                sorted_ranks, MAP(sorted_dates, LAMBDA(d, XLOOKUP(d, dates, ranks, "", 0))),
                result, HSTACK(artist, song, TOROW(sorted_ranks)),
                VSTACK(a, result)
            )
        )
    )
)

Подведите итоги еженедельного топ-листа

• Вы можете использовать следующее, например. в Sheet2!A1:

=LET(data,Sheet1!A2:D43021,dcol,1,acol,2,scol,3,rcol,4,
        headers,{"1ST DATE";"1ST POS";"ARTIST";"SONG"},
    di,IF(data = "","",data),d,FILTER(di,CHOOSECOLS(di,dcol)<>""),
    sd,CHOOSECOLS(d,dcol),sa,CHOOSECOLS(d,acol),
    ss,CHOOSECOLS(d,scol),sr,CHOOSECOLS(d,rcol),
    artist_song,UNIQUE(HSTACK(sa,ss)),
    au,TAKE(artist_song,,1),su,DROP(artist_song,,1),
    du,TOROW(UNIQUE(sd)),
    h,HSTACK(TOROW(headers),du),
    f_date,MAP(au,su,LAMBDA(a,s,XLOOKUP(1,(sa=a)*(ss=s),sd))),
    f_pos,MAP(au,su,LAMBDA(a,s,XLOOKUP(1,(sa=a)*(ss=s)*(sr=1),sd,""))),
    f_pos_count,MAP(au,su,LAMBDA(a,s,
        IFERROR(ROWS(FILTER(sd,(sa=a)*(ss=s)*(sr=1))),))),
    ranks,MAKEARRAY(ROWS(artist_song),COLUMNS(du),LAMBDA(r,c,
        XLOOKUP(1,(sd=INDEX(du,c))*(sa=INDEX(au,r))*(ss=INDEX(su,r)),sr,""))),
    r,VSTACK(h,HSTACK(f_date,f_pos,HSTACK(artist_song,ranks))),
    r)

• Замените самую нижнюю r любой другой переменной, чтобы увидеть, что она содержит.
• Использование MAKEARRAY, вероятно, является наиболее эффективным способом решить вашу проблему, т. е. сосредоточиться на ranks.

Источник

Результат

Другой вариант:

=LET(header, A1:D1,
     data,   A2:D15,
     date,TAKE(data,,1),     artist,INDEX(data,,2),     song,INDEX(data,,3),     notation,DROP(data,,3),     unq,UNIQUE(HSTACK(artist,song)),
     unqdate,TOROW(UNIQUE(date)),     unqartist,TAKE(unq,,1),     unqsong,DROP(unq,,1),
VSTACK(HSTACK("1ST "&{"DATE","POS"},
              CHOOSECOLS(header,2,3),
              unqdate),
       HSTACK(XLOOKUP(unqartist&unqsong,artist&song,date),
              XLOOKUP(unqartist&unqsong&1,artist&song&notation,date,""),
              unq,
              WRAPCOLS(TOCOL(BYCOL(--REPT(notation,date&artist&song=TOROW(unqdate&unqartist&unqsong,,1)),LAMBDA(b,IFERROR(TOCOL(b,2),"")))),ROWS(unq),1))))

Когда вы меняете header и data в соответствии со своими потребностями, остальное адаптируется к этим диапазонам.

Я попробовал использовать самообъясняющие имена внутри LET.

По сути, он ищет первое свидание комбинации исполнителя и песни, затем он ищет первую дату обозначения номер 1, если это применимо. Затем к ним добавляются уникальные комбинации исполнителя/песни, а затем следуют их поворотные обозначения с использованием BYCOL/REPT.