Подсчет количества уникальных значений в одном столбце, если данные во втором столбце совпадают с данными в третьем столбце
У меня есть данные, которые будут меняться ежедневно. Я просто хотел бы получить количество уникальных значений из первого столбца, если данные в той же строке второго столбца соответствуют чему-либо в третьем столбце, расположенном в другом месте. По большей части третий столбец сбоку останется прежним и будет похож на внешний белый список, а первые два столбца будут меняться ежедневно. Я хотел бы сделать это с помощью формулы, а не с помощью раскрывающихся фильтров.
Пример
Например, здесь есть 3 возможных места. Мне нужно подсчитать, в скольких местах (1, 2 или 3) находится продукт нужного цвета.
В доме есть и красный, и розовый из нужного списка. Автомобиль содержит только красный цвет из нужного списка. Несмотря на то, что для каждой локации есть 8 линий правильного цвета или несколько цветов, мне нужно знать только, сколько разных локаций содержат эти цвета. В этом случае ответ должен вернуться как 2.
Я пробовал несколько комбинаций countif/index/match/etc, но не нашел правильного способа сделать это. Также это будет использоваться в Excel 2016.
Ответы 2
Поскольку вы находитесь в 2016 году, я предлагаю вам разбить процесс на отдельные столбцы. Это должно сделать это понятным и понятным.
- Добавьте столбец в свои данные, который ищет ваш флаг продукта в списке желаемых значений. Это позволит вам быстро пометить статус соответствия для каждой строки (ниже: столбец MatchFlag).
- Добавьте столбец в таблицу желаемых флагов. Найдите все совпадения в одном и том же месте (ниже: столбец MatchData).
- Подсчитайте столбец MatchData
Я поместил ваши данные в две таблицы Excel: «Флаги» и «Данные». Это избавит вас от необходимости заполнения формулами непредсказуемого количества строк.
Данные матча:
=IFNA(IF(VLOOKUP([@Flags], Data[MatchFlag], 1, FALSE)<>"", TRUE, FALSE), "")
Флаги матча:
=IFNA(VLOOKUP([@Values], Flags[Flags], 1, FALSE), "")
Граффлагматчес:
=COUNTIF(Flags[MatchData], "=TRUE")
Очень ценю! На самом деле будут сотни и тысячи строк для местоположения и продукта, а список желаемых флагов будет намного длиннее. Но мне этот метод подойдет идеально.
Дома он работает в версии 2021 года, мне придется заставить его работать в 2016 году, если эти две функции недоступны, как заявил Черный кот. На моем рабочем месте установлена версия 2016 года, поэтому я не могу ее обновить. Некоторые компьютеры там до сих пор используют версию 2013 года.
Аргх - извини. Небрежно с моей стороны! @Blackcat совершенно прав. Я отредактировал это, чтобы заменить XLOOKUP на VLOOKUP. Чтобы считать УНИКАЛЬНЫМ, я добавил столбец в таблицу «Желаемые флаги».
В Excel 2016 вам будет интересен этот метод подсчета уникальных значений. В вашем случае вы можете использовать Countif, чтобы проверить наличие цвета в списке, а затем (поскольку местоположения являются строками) использовать match, чтобы преобразовать их в числа, как показано во втором примере в ссылке.
=SUM(--(FREQUENCY(IF(COUNTIF(Table2[Color],Table1[Products]),MATCH(Table1[Locations],Table1[Locations],0)),MATCH(Table1[Locations],Table1[Locations],0))>0))
Как уже отмечалось, это следует подтвердить с помощью CtrlShiftEnter или вы можете использовать Sumproduct вместо Sum.
Мне было интересно прочитать/перейти по ссылке, но она вела на страницу MS UNIQUE. Можете ли вы обновить? SU также предлагает метод СУММПРОИЗВ. superuser.com/questions/189762/…
Приносим извинения - ссылка обновлена.
Очень признателен вам обоим за вашу помощь. Этот метод также отлично работает в 2021 году и, надеюсь, как уже говорилось, он сработает и в 2016 году. Завтра я точно узнаю. еще раз спасибо
Попробуйте countifs(), который допускает несколько условий.