Вопрос или проблема
Для контекста, у меня есть excel Data Frame (назовем его SurveyData), содержащий оценки качества, предоставленные людям двумя способами: оценками AI и оценками, созданными пользователями (См. пример ниже). Эти оценки приходят нерегулярно, и оценки, созданные пользователями, как правило, ниже по количеству (обычно соотношение 1:20 по сравнению с оценками, созданными AI). Оценки, созданные пользователями, отображаются как пустые ячейки в столбце метода опроса, они могут начать опрос, но не завершить его (показывая статус “Покинуто” в столбце статуса и не предоставляя никаких оценок, а оценки, созданные AI, не генерируют статус, только оценку. На одну и ту же неделю может быть несколько опросов для каждого человека.
Data Frame SurveyData
| Человек | Метод опроса | Оценка | Статус | Неделя, заканчивающаяся | Ключ доступа |
|---|---|---|---|---|---|
| A | AI | 3 | 27/10/2024 | ID | |
| B | Покинуто | 27/10/2024 | ID | ||
| A | AI | 4 | 27/10/2024 | ID | |
| A | 2 | Завершено | 27/10/2024 | ID | |
| B | AI | 4 | 27/10/2024 | ID | |
| A | 3 | Завершено | 27/10/2024 | ID | |
| C | AI | 4 | 27/10/2024 | ID | |
| C | Покинуто | 27/10/2024 | ID | ||
| A | AI | 2 | 27/10/2024 | ID | |
| B | 4 | Завершено | 27/10/2024 | ID |
У меня есть сводная таблица, которая создается из другого Data Frame (назовем его PersonData), где я хочу добавить усредненную оценку опроса для каждого человека каждую неделю, используя информацию из SurveyData.
Сводная таблица, создаваемая из таблицы AgentData
| Неделя, заканчивающаяся | Человек | Средняя оценка AI | Средняя U. Gen оценка |
|---|---|---|---|
| 27/10/2024 | A | ||
| B | |||
| C |
Поскольку оба Data Frame могут быть пересечены с использованием поля, называемого Ключ доступа, который уникален для каждой недели и каждого человека, я попытался использовать эту формулу, чтобы получить среднюю оценку, созданную пользователями, для каждого человека, используя все оценки за эту неделю. Идея заключалась в том, чтобы использовать только оценки строк, которые имеют оценки, созданные пользователями (пустые ячейки в методе опроса) и отображаются как завершенные, чтобы избежать добавления пустых оценок в уравнение, используя следующую формулу:
=IF(XLOOKUP(AgentData[@[Access Key]],SurveyData[AccessKey],SurveyData[Survey Method],"", 0,1) = "", AVERAGE(FILTER(SurveyData[Score],AND(SurveyData[AccessKey] = AgentData[@[Access Key]], SurveyData[Status] = "Completed"))),
"")
Эта формула показывает ошибку в функции FILTER, потому что условие AND всегда ложно, даже если условия верны. Я проверил и стандартизировал данные, чтобы избежать опечаток, но проблема сохранилась. При использовании одного условия формула работает, но я хочу разделить оба типа оценок, потому что у них разные веса из-за количества опросов, которые независимы для каждого типа. Может кто-то помочь мне понять, есть ли способ сделать это альтернативно? Объединенные сводные таблицы могут быть использованы, но мне действительно хотелось бы понять концепцию того, почему эта формула может не работать при добавлении двух условий. Заранее спасибо.
Я думаю, что это то, что вы пытаетесь достичь:
=IFERROR(
AVERAGE(
FILTER($C$2:$C$11,($A$2:$A$11=I4)*($D$2:$D$11="Completed"))),"N/A")
Общая формула для FILTER с несколькими критериями выглядит так:
=FILTER(range,(criteria1)*(criteria2),"Сообщение об ошибке")
Ответ или решение
Как получить усредненные значения при фильтрации по нескольким условиям в Excel
Введение
В данной статье мы рассмотрим, как получить усреднённые значения из одной колонки в Excel, используя несколько критериев фильтрации. Это может быть очень полезно, если вы работаете с данными, как, например, в случае с оценками качества, предоставленными различными методами.
Исходные данные
У вас есть таблица данных, названная SurveyData, содержащая информацию о пользователях и их оценках, полученных через разные методы (AI и пользовательские). Также у вас есть сводная таблица (Pivot Table) из другой таблицы (назовем её PersonData). Ваша задача заключается в том, чтобы добавить усреднённые оценки для каждой персоны и недели.
Проблема с формулой
Вы пробовали использовать формулу, в которой стремились применить условия фильтрации для расчета средней оценки только для строк с пользовательскими оценками, но столкнулись с ошибкой.
Вот ваша исходная формула:
=IF(XLOOKUP(AgentData[@[Access Key]],SurveyData[AccessKey],SurveyData[Survey Method],"", 0,1) = "", AVERAGE(FILTER(SurveyData[Score],AND(SurveyData[AccessKey] = AgentData[@[Access Key]], SurveyData[Status] = "Completed"))), "")Ошибка возникает из-за неправильного использования функции AND внутри FILTER. Вместо этого вам следует использовать логическое умножение (оператор *), чтобы совместить условия.
Решение
Мы можем использовать более простую и эффективную формулу. Давайте рассмотрим, как правильно ее настроить:
- Использование функции
FILTERс несколькими условиями:=IFERROR( AVERAGE( FILTER(SurveyData[Score], (SurveyData[AccessKey] = AgentData[@[Access Key]]) * (SurveyData[Survey Method] = "") * (SurveyData[Status] = "Completed"))), "N/A")
Пояснение формулы
- IFERROR – эта функция позволяет избежать ошибок, возвращая заданное значение ("N/A") в случае их возникновения.
- AVERAGE – функция, которая рассчитывает среднее значение от отфильтрованных значений.
-
FILTER – основная функция, которая выбирает значения, соответствующие указанным критериям.
(SurveyData[AccessKey] = AgentData[@[Access Key]])– проверяет, что Access Key соответствует ключу из сводной таблицы.(SurveyData[Survey Method] = "")– фильтрует только те оценки, которые были получены через пользовательский метод (пустое значение в столбце «Метод опроса»).(SurveyData[Status] = "Completed")– учитываем только те записи, которые имеют статус "Завершено".
Заключение
Используя приведённую формулу, вы сможете получить усреднённые значения пользовательских оценок для каждого человека и недели, сохраняя правильность рассчёта. Это простое, но эффективное решение позволит вам избежать ошибок, связанных с неправильным объединением условий и даст точные данные для вашей сводной таблицы.
Обязательно протестируйте формулу на вашем наборе данных и убедитесь, что она соответствует ожиданиям. Если у вас есть дополнительные вопросы или проблемы, не стесняйтесь обращаться за помощью.