Excel Formula Assist - Поиск пропущенных номеров

Question

У меня есть таблица, экспортированная из моей базы данных, которая содержит Role и Account ID . Для идентификатора учетной записи существует 4-5 имен ролей. Мне нужна формула для отображения любых учетных записей, которые не имеют роли RBD . Кто-нибудь может помочь?

Пример:

Account ID   Account Name       Team        Role
1            123 AG SERVICE     National    MANG
1            123 SERVICE        National    CAP
1            123 AG SERVICE     National    RGL
1            123 AG SERVICE     National    CS
1            123 AG SERVICE     National    XSM
1            123 AG SERVICE     National    RBD
1            123 AG SERVICE     National    Q4
2            NORTHEAST INC      National    MANG
2            NORTHEAST INC      National    CAP
2            NORTHEAST INC      National    RGL
2            NORTHEAST INC      National    CS
2            NORTHEAST INC      National    XSM

Я хотел бы, чтобы запрос отображал 2 как номер счета, который не имеет роли RBD .

Любая помощь приветствуется!

Answer 1

SumProduct ваш друг здесь. SumProduct позволяет выполнять несколько тестов на диапазоне данных, строка за строкой.

На отдельной вкладке перечислите свои отличительные идентификаторы учетной записи в столбце A. В столбце B мы получим счетчик всех строк для каждого идентификатора учетной записи, независимо от того, с помощью формулы счетчиков ... like =countif(Sheet1!A:A, Sheet2!A1) предполагая, что Sheet1 содержит ваши данные, и мы работаем над Sheet2.

В столбце C мы развернем субпродукт. Мы хотим вернуть количество строк для каждого accountID, где роль не равна RBD. Это будет выглядеть как =SUMPRODUCT((A1=Sheet1!$A$1:$A$13)*(Sheet1!$D$1:$D$13<>"RBD")*1) . То, что это делает, проверяет каждое из условий в параграфах для истинного / ложного для каждой строки в диапазоне. Затем он складывает все строки, в которых оба условия выполнялись. Наши два условия заключаются в том, что столбец A в Sheet1 имеет интересующий нас AccountID и что столбец D в Sheet1 не содержит RBD.

В столбце D второй вкладки вычтите столбцы C из столбца B: =B1-C1 . Если это не 0, то в accountID отсутствует RBD.

Все это можно записать в одну формулу вместо того, чтобы распределять ее по столбцам B, C и D, как =if(countif(Sheet1!A:A, Sheet2!A1)-SUMPRODUCT((A1=Sheet1!$A$1:$A$13)*(Sheet1!$D$1:$D$13<>"RBD")*1) = 0, "", "Missing RBD!") если вам нравится вся эта краткость.

В конце концов, вам может быть лучше сделать это с помощью SQL в вашей базе данных. Каждый раз, когда вы используете sumproduct (или нуждаетесь в нем), вам лучше использовать SQL, так как это скорее функция типа базы данных. Что-то вроде

SELECT distinct_account_ids.Account_ID FROM (SELECT DISTINCT ACCOUNT_ID FROM <table>) as distinct_account_ids LEFT OUTER JOIN (SELECT Account_ID FROM <table> WHERE ROLES = 'RBD' GROUP BY Account_ID ) as ids_with_rbd ON distinct_account_ids.Account_id = ids_with_rbd.Account_id WHERE ids_with_rbd.Account_id is null;