Excel извлекает значения из нескольких тестов
В конечном итоге я пытаюсь найти среднее значение трех верхних значений для каждого теста в последний день для «Человека 1». Я могу найти последний день, используя формулу Large(If()), и могу подключить ее к функции Unique(Filter()), чтобы найти соответствующие «тестовые» числа за день. Моя проблема возникает, когда я пытаюсь извлечь фактические результаты теста. Мои данные:
Person Date Test Rep Result
Person 1 10/9/2023 1 5 1.06459372
Person 1 10/9/2023 1 4 1.11329722
Person 1 10/9/2023 1 3 0.91809
Person 1 10/9/2023 1 2 0.92332983
Person 1 10/9/2023 1 1 0.81854742
Person 1 10/9/2023 2 5 0.79415372
Person 1 10/9/2023 2 4 0.78722627
Person 1 10/9/2023 2 3 0.77623751
Person 1 10/9/2023 2 2 0.75960889
Person 1 10/9/2023 2 1 0.55552335
Person 1 10/9/2023 3 5 1.25761919
Person 1 10/9/2023 3 4 1.38660111
Person 1 10/9/2023 3 3 1.28825923
Person 1 10/9/2023 3 2 1.11500258
Person 1 10/9/2023 3 1 0.93898195
Person 1 10/9/2023 4 5 1.01453846
Person 1 10/9/2023 4 4 1.06929
Person 1 10/9/2023 4 3 0.93578771
Person 1 10/9/2023 4 2 0.94945872
Person 1 10/9/2023 4 1 0.84496289
Person 1 10/23/2023 1 5 1.58905785
Person 1 10/23/2023 1 4 1.49243315
Person 1 10/23/2023 1 3 1.4587432
Person 1 10/23/2023 1 2 1.58905785
Person 1 10/23/2023 1 1 1.47988413
Person 1 10/23/2023 2 5 0.368215
Person 1 10/23/2023 2 4 1.66144122
Person 1 10/23/2023 2 3 1.3734
Person 1 10/23/2023 2 2 1.75722655
Person 1 10/23/2023 2 1 1.24049032
Person 2 4/29/2024 1 5 1.89406839
Person 2 4/29/2024 1 4 1.90691308
Person 2 4/29/2024 1 3 1.81291382
Person 2 4/29/2024 1 2 1.58922
Person 2 4/29/2024 1 1 1.40970617
Person 2 4/29/2024 2 5 1.70049909
Person 2 4/29/2024 2 4 1.92244355
Person 2 4/29/2024 2 3 1.92599629
Person 2 4/29/2024 2 2 1.63100333
Person 2 4/29/2024 2 1 1.67577882Я использую формулу =FILTER(E:E,UNIQUE(FILTER(E:E,IF((A:A=H3)*(B:B=LARGE(IF(A:A=H3,B:B),1)),C:C)))), где столбец E — это результаты теста, а H3 — раскрывающийся список имен. Эта формула дает мне результат «#VALUE». Я также попробовал включить логику, где =...,K:K=Unique(Filter())...
Для контекста я также буду искать интеграцию Макса, чтобы найти максимальное среднее значение за день, если это имеет какое-либо влияние на формулу. Но моя отправная точка — попытаться найти 3 лучших повторения в тесте в последний день (23.10.23 для человека 1).
Возможно, отфильтровать по человеку, затем отсортировать по дате (по убыванию) и результату (по убыванию) и взять три верхних значения, но слишком много данных для ввода и проверки.
Не могли бы вы подтвердить, что в вашей установке доступна поддержка динамических массивов и FILTER? Они доступны в Excel 2021 по следующему адресу: support.microsoft.com/en-us/office/…
Ответы 2
=LET(f,FILTER(C2:E41,(A2:A41 = "Person 1")*(B2:B41=MAXIFS(B2:B41,A2:A41,"Person 1"))),MAP(UNIQUE(TAKE(f,,1)),LAMBDA(m,AVERAGE(LARGE(FILTER(TAKE(f,,-1),TAKE(f,,1)=m),{1;2;3})))))
Сначала создается фильтр для максимальной даты для человека 1 (f).
Далее уникальные тесты обозначаются как m. Затем усредняются 3 наибольших значения за тест.
Насколько я знаю, это будет работать в Excel 2021:
=LET(x,"Person 1",f,FILTER(Table1[[Date]:[Result]],(Table1[[Person]:[Person]]=x)*(Table1[[Date]:[Date]]=MAXIFS(Table1[[Date]:[Date]],Table1[[Person]:[Person]],x))),u,UNIQUE(INDEX(f,,2)),t,SORT(FILTER(INDEX(f,SEQUENCE(ROWS(f)),{2,4}),INDEX(f,,2)=INDEX(u,ROW(A1),)),2,-1),IFERROR(INDEX(CHOOSE({1,2},INDEX(t,1,1),AVERAGE(INDEX(t,{1,2,3},2))),,COLUMN(A1)),""))
Оно не способно пролиться. но вы можете перетащить его вниз и вправо, чтобы отобразить следующее среднее значение теста, равное 3.
При перетаскивании он показывает имя/идентификатор теста в первом столбце, в который вы его вставляете, и среднее значение трех верхних значений этого теста в столбце справа от него.
Спасибо, я уверен, что это сработает. К сожалению, в моей офисной подписке нет функций LAMBDA или MAP. Я работаю с Office профессиональный плюс 2021. Быстрый поиск показывает, что эта функция не была перенесена в мою версию.
Я проверю сегодня позже. Но, может быть, вы можете использовать его онлайн: Excel.new?
@Cra538 Cra538 проверьте редактирование. Я работал со ссылками на таблицы, дайте мне знать, что вам подходит.
С СРЕДНИМИ, с разливом
Вот вариант со вспомогательным столбцом и AVERAGEIFS; Вспомогательный столбец упрощает формулу.
- Добавьте вспомогательный столбец для ранжирования результатов.
- Поскольку это динамическая формула, ее не нужно заполнять.
Столбец ранга:
=LET(
data, $A$2:$E$5000,
persons, INDEX(data, , 1),
dates, INDEX(data, , 2),
tests, INDEX(data, , 3),
results, INDEX(data, , 5),
COUNTIFS(
persons, persons,
dates, dates,
tests, tests,
results, ">" & results
) + 1
)
Столбец результатов:
=LET(
for_persons, H3:H4,
top_n, 3,
data, $A$2:$F$5000,
persons, INDEX(data, , 1),
dates, INDEX(data, , 2),
tests, INDEX(data, , 3),
results, INDEX(data, , 5),
ranks, INDEX(data, , 6),
latest_dates, MAXIFS(dates, persons, for_persons),
AVERAGEIFS(
results,
persons, for_persons,
dates, latest_dates,
ranks, "< = " & top_n
)
)
Обновлено для Excel 2021 без LAMBDA.
При наличии FILTER
=LET(
person, H3,
latest_date, MAXIFS(
Table2[Date],
Table2[Person], person
),
rank_for_result, COUNTIFS(
Table2[Person], person,
Table2[Date], Table2[Date],
Table2[Test], Table2[Test],
Table2[Result], ">" & Table2[Result]
) + 1,
latest_results, FILTER(
Table2[Result],
(rank_for_result <= 3) *
(Table2[Date] = latest_date)
),
AVERAGE(latest_results)
)
Результат:
Скриншот формулы:
Исправлено 7 августа 2024 г.
Внес исправление, любезно указанное П.б.
Не такой компактный, как у P.b, но с использованием таблицы. Пожалуйста, дайте мне знать, если преобразование в таблицу невозможно.
=REDUCE(
{"Person", "Lastest Date", "Avg for Top 3"},
UNIQUE(Table1[Person]),
LAMBDA(acc, person,
LET(
latest_date, MAXIFS(Table1[Date], Table1[Person], person),
test_and_results_for_latest_date, FILTER(
HSTACK(Table1[Test], Table1[Result]),
(Table1[Date] = latest_date) * (Table1[Person] = person)
),
tests, INDEX(test_and_results_for_latest_date, , 1),
results, INDEX(test_and_results_for_latest_date, , 2),
top_3_per_test, DROP(
REDUCE(
"top_3_per_test",
UNIQUE(tests),
LAMBDA(acc, test,
VSTACK(acc, TAKE(SORT(FILTER(results, tests = test), , -1), 3))
)
),
1
),
avg_for_top_3s, AVERAGE(top_3_per_test),
VSTACK(acc, HSTACK(person, latest_date, avg_for_top_3s))
)
)
)
Топ-3 значения для каждого теста для максимальной даты для человека (1)
Ах, да, спасибо, П.б! В ближайшее время исправим. Еще одна пара глаз определенно поможет!
@P.b, не мог бы ты заглянуть сюда, когда будет время? Еще раз спасибо!
=REDUCE({"Person","LastestDate","AvgforTop3"},UNIQUE(Table1[Person]),LAMBDA(acc,person,LET(latest_date,MAXIFS(Table1[Date], Таблица1[Человек],person),test_and_results_for_latest_date,FILTER(HSTACK(Таблица1[Тест],Таблица1[Результат]),(Таблица1[Дата]=latest_date)*(Таблица1[Пер son]=person)),tests,TAKE(test_and_results_for_latest_date,,1),results,TAKE(test_and_results_for_latest_date,,-1),top_3_per_test,DROP(REDUCE("top_3_per_test ",UNIQUE(тесты),LAMBDA(acc,test,VSTACK(acc,AVERAGE(TAKE(SORT(FILTER(+results,tests=test),,-1),3) )))),1),avg_for_top_3s,(top_3_per_test),
IFNA(VSTACK(acc,HSTACK(person,latest_date,avg_for_top_3s)),""))))
Я не смог уместить это в 1 комментарий, поэтому вам нужно вставить оба комментария друг за другом. Я не сильно изменил ваш код. Основное изменение, которое необходимо исправить: СРЗНАЧ перенесен с одного слоя на предыдущий (вокруг TAKE(SORT(FILTER(),,-1),3).
=REDUCE({"Человек","LastestDate","Test#","AvgforTop3"},UNIQUE(Table1[Person]),LAMBDA(acc,p,LET(latest_date,MAXIFS(Table1[) Дата],Table1[Person],p),res_latest,FILTER(HSTACK(Table1[Test],Table1[Результат]),(Table1[Date]=latest_date)*(Table1[ Person]=p)),tests,TAKE(res_latest,,1),results,INDEX(res_latest,,2),top_3,DROP(REDUCE("top_3_per_test",UNIQUE (тесты),LAMBDA(acc,test,LET(top3_test_res,TAKE(SORT(FILTER(HSTACK(tests,results),tests=test),2,-1),3), VSTACK(acc,HSTACK(INDEX(top3_test_res,1,1),AVERAGE(INDEX(top3_test_res,,2))))))),1),VSTACK(acc,IFNA(HSTACK( p,latest_date,top_3),HSTACK(p,latest_date))))))
Я использовал более короткие имена, чтобы можно было вставлять. Эта версия включает тестовую колонку
Я потенциально мог бы превратить это в таблицу, если это поможет, однако в моей офисной подписке нет функций LAMBDA или MAP. Я работаю с Office профессиональный плюс 2021. Быстрый поиск показывает, что эта функция не была перенесена в мою версию.
Спасибо большое П.Б.! Я внесу эти изменения. @Cra538 Cra538, пока вы проверите решение Pb, я попробую собрать настольную и нетабличную версии позже сегодня.
@P.b, пожалуйста, просмотри еще раз, когда у тебя будет время - спасибо!
Да, в моей версии есть ФИЛЬТР. @nkalvi, версия с наличием ФИЛЬТРА заработала. Спасибо!
@nkalvi Обновление по-прежнему показывает среднее значение топ3 на человека, но не на тест на человека. Взгляните на результаты предложенных мной комментариев к версии 5 выше этой. Он включает в себя идентификатор теста (который, я думаю, может быть неупомянутым требованием, потому что в противном случае вы знаете 2 средних значения, но понятия не имеете, к чему они относятся.
Вы правы, я не исправил «первую» версию, но включил сравнение идентификаторов тестов в последние. Первую обновлю позже. Я не могу оценить вашу помощь достаточно!
У ваших
IFотсутствует значение, если оно ложное.