☰

Формулы массива и логика, проверка по нескольким критериям

tag-icon • tag-icon microsoft-excel worksheet-function array
Формулы массива и логика, проверка по нескольким критериям

Я сейчас просто устраняю неполадки в отчетной таблице, которую я написал. Логика, которую я написал, не работает, поэтому я подумал, что посмотрю, смогу ли я попросить здесь о помощи.

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

Пример данных:

Product   Company Number    Start of Contract        End of contract     Contract ID     Include in this month's report
Fax            1234             10.09.2013              10.09.2014           1                      No
Fax            1234             10.09.2014              10.09.2015           2                      No     
Box            5678             11.01.2014              30.04.2015           3                      No
Box            5678             01.05.2015              11.01.2016           4                     Yes
Fax            5678             01.05.2015              01.05.2016           5                     Yes
Cup            9876             03.05.2015              03.05.2016           6                     Yes

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

Логика включения контракта в отчет за этот месяц проста — просто проверьте, Start dateуказан ли этот месяц в другом месте отчета.

В приведенном выше примере

  • Идентификаторы контрактов 1,3 и 6 являются новыми для бизнеса
  • ID 2 и 4 — это продления
  • Идентификатор 5 является новым для продукта, с существующим бизнесом с компанией

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

Мне нужно найти способ проверить, есть ли предыдущий контракт с нами, который был с указанным продуктом, контракты с совершенно новыми клиентами и контракты, где есть предыдущий контракт с нами, который НЕ был с тем же продуктом. По сути, как ниже, но автоматически вычисляется из приведенной выше таблицы.

Полученные результаты:

Contract ID     Renewal?        New to Product?         New to Business?
    1              No                  No                     Yes
    2             Yes                  No                     No
    3              No                  No                     Yes
    4             Yes                  No                     No
    5              No                 Yes                     No
    6              No                  No                     Yes

У меня пока есть несколько вспомогательных столбцов, например, «завершено за последние 12 месяцев» и «количество соглашений». Но я все время путаюсь, когда дело доходит до логики трех конечных результатов!

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

решение1

Я думаю, что именно так вы хотите, чтобы выглядели ваши колонки:

введите описание изображения здесь

Вот используемые формулы:

  • G2 — это=IF(AND(B2=B1,A2=A1),"Yes","No")
  • H2 — это=IF(AND(B1=B2,A2<>A1),"Yes","No")
  • I2 — это=IF(B2<>B1,"Yes","No")

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