Категоризация данных в Excel по формуле

Я пытаюсь классифицировать каждую строку как x, y или z на основе как месяца, так и дня (определенного времени года). Мои категории:

  • X = 1 июня - 1 октября
  • Y = 15 ноября - 15 апреля
  • Z = все остальные

Я понимаю, что это может быть просто, но я не могу понять формулу правильно. Стол у меня выглядит так:

День месяц год тест
7 5 2012 Z
6 5 2013 Z
2 11 2011 Z
4 6 2013 Z
27 5 2013 Z
14 3 2013 Z
14 5 2014 Z
20 10 2013 Z
5 12 2013 Z

Моя текущая формула для "Тест":

= ЕСЛИ (И (И (В2>=6, А2>=1) и (В2<=10, А2<=1)),"Х", ЕСЛИ (ИЛИ (И (В2<=4, А2<=15), И (В2>=11, А2>=15)),"Y","Z"))

Любая помощь будет оценена!

Источник

2 ответа

Решение

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

IF(AND(B2<=4,A2<=15)

вернет false на 16 марта, даже если вы хотите, чтобы это было правдой. Это потому, что ваша формула не понимает понятия "дни и месяцы".

Формула я бы использовал

=IF(AND(DATE(C2,B2,A2)>=DATE(C2,6,1),DATE(C2,B2,A2)<DATE(C2,10,1)), "X",
    IF(OR(DATE(C2,B2,A2)>=DATE(C2,11,15),DATE(C2,B2,A2)<DATE(C2,4,15)), "Y", "Z"))

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

Обратите внимание, что моя формула вычисляет DATE(C2,B2,A2) несколько раз, и было бы хорошо иметь это как отдельный (скрытый) столбец. Приятно то, что в этом выражении используется встроенный DATE функция, которая понимает месяцы и дни - и ее относительно легко исправить в зависимости от критериев диапазона дат. С вашими датами тестирования, это дает следующее:

Day  Month  Year   Test
 7     5    2012     Z
 6     5    2013     Z
 2    11    2011     Z
 4     6    2013     X
27     5    2013     Z
14     3    2013     Y
14     5    2014     Z
20    10    2013     Z
 5    12    2013     Y

Примечание - обновляется после того, как @pnuts указал на ошибку...

Также обратите внимание... в вышеприведенном виде есть 4 условия - и я решил, какие даты включить. В основном я перевел ваши требования следующим образом:

date >= Nov 15 of this year          ==> "Y"
date < April 15 of this year         ==> "Y"
date >= June 1 AND date < Oct 1      ==> "X"
else "Z"

Возможно, это не совсем то , что вы хотели, но должно быть очевидно, как работают выражения, и вы должны иметь возможность корректировать их по мере необходимости (в частности, неясно, должно ли 15 апреля быть частью "Y" или "Z")., например.)

Я думаю, что было бы полезно определить ряд переменных (возможно, на скрытом листе)

startXmonth = 4
startXday = 15
stopXmonth = 10
stopXday = 1

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

Наконец, вы можете сделать все даты "до и включая" ​​- используя <= а также >= и определение первого и последнего дня (а не, как указано выше, первого дня для включения и первого дня для того, чтобы не включать).

Дайте мне знать, если это все имеет смысл.

Источник

Не проверено (ожидаемые результаты помогли бы), пожалуйста, попробуйте:

=IF(OR(AND(B2<5,A2<16),AND(B2=11,A2>14),B2=12),"Y",IF(OR(AND(B2>5,B2<10),AND(B2=10,A2=1)),"X","Z"))
Источник
Другие вопросы по тегам