У меня следующая ситуация:
Adam White
Adam Black
Adam Black
Ben White
Ben White
Clark Black
Clark Black
И я хотел бы получить следующий ответ от Excel:
Adam Both
Ben White
Clark Black
В заключение у меня есть больше разных имен в левом столбце и два значения в правом. Я хочу знать для каждого имени, которое появляется в левом столбце, если оно имеет только белый, только черный или оба цвета, связанные в правом столбце.
Я предложу другой подход, который кажется более подходящим, чем использование VLOOKUP.
Формула в E1, скопированная для списка имен:
=IF(SUMPRODUCT((A:A=D1)*(B:B="White")),IF(SUMPRODUCT((A:A=D1)*(B:B="Black")),"Both","White"),"Black")
SUMPRODUCT позволяет обрабатывать несколько столбцов, аналогично формуле массива, в поисках комбинаций условий. Нулевой результат рассматривается как False в тесте IF; любое положительное число считается True .
Обратите внимание, что это очень упрощенная логика, которая не проверяет наличие ошибок, а полагается на данные, соответствующие правилам; вложенная логика IF предполагает, что каждая запись столбца B должна быть либо Black либо White , и что каждая запись поиска столбца D является именем, которое содержится в столбце A.
Сначала вы должны проверить, присутствуют ли оба с COUNTIFS. Если это так, то верните Both используйте VLOOKUP для возврата:
=IF(AND(COUNTIFS(A:A,D1,B:B,"Black"),COUNTIFS(A:A,D1,B:B,"White")),"Both",VLOOKUP(D1,A:B,2,FALSE))
Вместо слишком сложных формул я бы порекомендовал перейти к сводной таблице, которая даст вам имя в строке, цвет в столбце и количество цветов в значениях. Затем вы можете сделать простую формулу, чтобы установить, существует ли конкретная комбинация или нет.
Преимущества:
гораздо проще и интуитивнее создавать и поддерживать вложенные IF с SUMPRODUCTs или даже формулами массива,
это легче расширить, если у вас есть несколько возможных значений.
@ Ответ Eleshar предполагает использование сводной таблицы. Если вы начинаете с нуля, это не плохой способ сделать тяжелую работу по извлечению уникального списка имен и суммированию данных (а сводная таблица облегчает эту часть). Однако, это все еще оставляет перевод результатов сводной таблицы в желаемую форму. Я буду опираться на это предложение и покажу другой подход, чтобы получить окончательный результат, который еще не был представлен.
Сводная таблица
Данные в столбцах A и B. Результат сводной таблицы находится в D1:F5. Если вы никогда не использовали сводную таблицу, выберите диапазон данных и Вставьте сводную таблицу из меню. Я использую LO Calc, поэтому пользовательский интерфейс выглядит немного иначе, но вы получаете мастер, который выглядит примерно так:
Из доступных полей перетащите Name в поле «Строки», поле « Color в столбцы» и снова « Color в поля данных (в Excel это поле помечено как « Values). В Excel для агрегации уже по умолчанию используется значение Count. Calc имеет возможность отменить выбор строк и столбцов. Мой старый Excel автоматически производит эти итоги; Вы можете щелкнуть правой кнопкой мыши на их ярлыках, и вы получите меню, которое включает их удаление. Укажите место назначения для сводной таблицы, которая не перезаписывает данные (я выбрал D1).
Таким образом, с помощью нескольких щелчков мышью и выбора, вы получите таблицу, как показано на предыдущем изображении в D:F.
Перевод на результат
Сводная таблица обеспечивает основу для результатов, показанных в столбце G, на чем я остановлюсь здесь. Формула в G3:
=CHOOSE((E3>0)+(F3>0)*2,"Black","White","Both")
CHOOSE использует значение в качестве индекса для выбора из списка; значение индекса 1 выбирает первое значение, 2 - второе и т. д. В этой формуле, если число в черном столбце больше нуля, формула выдает 1 (логическое значение для истинного результата). Если значение в столбце White больше нуля, оно выдает 2 . Так что, если есть только черные, индекс равен 1 ; только белый, индекс 2 ; и то и другое, индекс равен 3 .
Я использовал немного другой подход к решению проблемы:
Как это устроено:
H48 и черный в I48 .В ячейке H50 напишите эту формулу массива, чтобы создать список уникальных имен.
{=IFERROR(INDEX($E$50:$E$56, MATCH(0,COUNTIF($H$49:H49, $E$50:$E$56), 0)),"")}
Обратите внимание, завершите эту формулу с помощью Ctrl+Shift+Enter и заполните ее.
I50 и заполните.=IFERROR(IF(AND($E$50:$E$56=H50,SUMPRODUCT(($F$50:$F$56=$H$48)+($F$50:$F$56=$I$48))>1),"Both",INDEX(F50:F56,MATCH(H50,E50:E56,0))),"")
NB. При необходимости измените ссылки на ячейки в формуле.