☰

Excel — найдите несколько фрагментов данных в нескольких разных столбцах одновременно

tag-icon • tag-icon microsoft-excel worksheet-function
Excel — найдите несколько фрагментов данных в нескольких разных столбцах одновременно

В настоящее время у меня на ходу электронная таблица, в которой перечислены контракты. Мне нужно знать, является ли контракт продлением, новым для продукта или новым для компании. В настоящее время я борюсь с логикой, стоящей за этим, и буду признателен за любую помощь!

Поскольку программное обеспечение для составления отчетов не запрашивает информацию о различных типах контрактов, у меня остается меньше информации, чем хотелось бы.

Помимо дат «От» и «По», каждый клиент имеет уникальный идентификатор, каждый продукт имеет указанное название, а каждый контракт имеет уникальный номер, а также текущий «статус». Для контрактов с несколькими различными версиями я также добавил столбец «последняя версия».

Образец:

CompanyNumber   Product     Contract Number     Version     Status   Latest version?   Valid From      Valid To
12345              A            9876A-BA           1        Active        Yes             1/1/14       31/12/15

Мне нужно создать дополнительные столбцы «Да/Нет» для New Customer, New to product, и Renewal.

Это должно позволить мне гораздо легче создавать ежемесячные отчеты по этим трем столбцам, просто фильтруя их в сводных таблицах.

Я уже создал несколько дополнительных столбцов по другим причинам, как указано ниже.

Start date in this month? - End Date In This Month - Expires in next three months?  - More than one version? - Existing Client > 365 days - Latest version?

Логика, которая у меня есть на данный момент для продлений, заключается в том, чтобы сначала проверить, имеет ли другая запись с тем же номером компании дату окончания в течение прошлого года, а затем проверить, начинается ли последняя версия контракта в этом месяце и является ли она последней версией, например так:

If
   unique company number = no, find if any duplicate result ended in the last year
                              AND
   Contract Starts this month AND this entry is the latest version 
= True, "YES"
  False = "NO"

Но я не знаю, как это сделать! Любая помощь будет высоко оценена.

Большое спасибо

РЕДАКТИРОВАТЬ.

После нескольких дней попыток я зашел так далеко. Я думаю, что это работает, но я не на 100%

Я начал пробовать это как функцию if с вложенными функциями and, countif (уникальный номер компании) >1, countif(product)>1, текущей и последней версии, например: =IF(AND(COUNTIF(B:B,B10)>1,COUNTIF(C:C,C10)>1,(S10="Да"),AA10=1),1,0)

Ответы приходят в виде 1 или 0, поэтому я могу легко использовать результаты в сводной таблице.

Если кто-то придумает лучший ответ, дайте мне знать :) -

ВТОРОЕ ИЗДАНИЕ

Это не сработало, поэтому я провел дополнительные исследования и нашел эту формулу, которая тоже не совсем работает.

Я решил создать «вспомогательный» столбец, чтобы мы могли подсчитать количество экземпляров каждого продукта по номеру компании, но при этом будут учитываться КАЖДЫЕ экземпляры номера компании, а не только те, которые ссылаются на продукт.

=SUM(IF(C:C=C28,IF(B:B=B28,1,0),0))

Где C:Cнаходится столбец, в котором я храню номера продуктов, и Bуникальный номер компании?

решение1

Часто я использую concatenate для объединения элементов данных, а затем использую сводную таблицу для подсчета количества раз, когда встречается объединенный элемент данных, или вы можете использовать count if. Так, например, если вы пытаетесь выяснить, является ли компания (номер компании) новой для продукта, то вы можете создать вспомогательный столбец с формулой, =Concatenate(Company Number, Product)которая в вашем примере даст вам значение 12345A. Затем вы можете использовать countifили сводную таблицу, чтобы увидеть 1) является ли компания новой для этой линейки продуктов 2) дату начала (действительно с) и т. д.

Связанный контент