☰

Формула не работает, не могу найти ошибку в формуле

tag-icon • tag-icon microsoft-excel worksheet-function
Формула не работает, не могу найти ошибку в формуле

Я уверен, что это имеет невероятно простой ответ, но я часами смотрел на эту формулу и не могу понять ошибку. По сути, я хочу, чтобы формула делала следующее: если F3 = "Freshman", то проверяла, пусто ли U3, проходила через N3 и вводила значение, если U3 не пусто, проходила через U3. Затем, если F3 != "Freshman" и V3 пусто, проходила через S3, если V3 не пусто, использовала V3. Может быть, есть менее сложный способ сделать это, но я не уверен.

=IF(ISBLANK($U3)=TRUE,IF($F3="Freshman",IF(N3>=Lists!$H$7,3,IF(N3>=Lists!$H$6,2.5,IF(N3>=Lists!$H$5,2,IF(N3>=Lists!$H$4,1.5,IF(N3>=Lists!$H$3,1,IF(N3>=Lists!$H$2,0.5,IF(N3>Lists!$H$8,0,""))))))),IF(U3>=Lists!$I$7,3,IF(U3>=Lists!$I$6,2.5,IF(U3>=Lists!$I$5,2,IF(U3>=Lists!$I$4,1.5,IF(U3>=Lists!$I$3,1,IF(U3>=Lists!$I$2,0.5,IF(U3>=Lists!$I$8,"")))))))),IF(ISBLANK($V3)=TRUE,IF(S3>=Lists!$H$7,3,IF((S3>=Lists!$H$6,2.5,IF(S3>=Lists!$H$4,1.5,IF(S3>=Lists!$H$3,1,IF(S3>=Lists!$H$2,0.5,IF(S3>Lists!$H$8,0,"")))))),IF(V3>=Lists!$I$7,3,IF(V3>=Lists!$I$6,2.5,IF(V3>=Lists!$I$5,2,IF(V3>=Lists!$I$4,1.5,IF(V3>=Lists!$I$3,1,IF(V3>=Lists!$I$2,0.5,IF(V3>=Lists!$I$8,0,"")))))))))

Я знаю, что есть много вложенных if, которые, вероятно, и сбивают меня с толку. Если кто-то может помочь, это было бы очень ценно.

решение1

Я ввел вашу формулу в блокнот. Затем для каждого условия я начал новую строку и использовал табуляцию, чтобы отсортировать различные функции ЕСЛИ. Это упрощает отладку вашей проблемы. К сожалению, я не знаю, как загрузить файл. Я обнаружил, что последний ")" слишком многочисленный, и в формуле есть некоторые несоответствия.

Например, во втором листинге последний оператор заканчивается на S3>=lists$I$8,""), тогда как в других листингах он заканчивается на S3>=lists$I$8,0,"").

В третьем листинге отсутствует IF(U3>=Lists!$H$4,2.

Если хотите, я могу вам отправить файл, чтобы вы сами убедились.

решение2

Вот это в кодированной записи

=IF(ISBLANK($U2)=TRUE,          'if condition #1
 IF($F3="Freshman",             '#1 true condition
  IF(N3>=Lists!$H$7,3,
   IF(N3>=Lists!$H$6,2.5,
    IF(N3>=Lists!$H$5,2,
     IF(N3>=Lists!$H$4,1.5,
      IF(N3>=Lists!$H$3,1,
       IF(N3>=Lists!$H$2,0.5,
        IF(N3>Lists!$H$8,0,"")
       )
      )
     )
    )
   )
  ),
   IF(S3>=Lists!$I$7,3,            '#1 false condition
    IF(S3>=Lists!$I$6,2.5,
     IF(S3>=Lists!$I$5,2,
      IF(S3>=Lists!$I$4,1.5,
       IF(S3>=Lists!$I$3,1,
        IF(S3>=Lists!$I$2,0.5,
         IF(S3>=Lists!$I$8,"")
        )
       )
      )
     )
    )
   )
  ),                              'everything before the comma is one function
   IF(ISBLANK($V2)=TRUE,          'new #2 if condition
    IF(U3>=Lists!$H$7,3,          '#2 true condition
     IF((U3>=Lists!$H$6,2.5,
      IF(U3>=Lists!$H$4,1.5,
       IF(U3>=Lists!$H$3,1,
        IF(U3>=Lists!$H$2,0.5,
         IF(Lists!$H$8,0,"")      'here is an if without a test condition
        )
       )
      )
     )
    ),
     IF(V3>=Lists!$I$7,3,         '#2 false condition
      IF(V3>=Lists!$I$6,2.5,
       IF(V3>=Lists!$I$5,2,
        IF(V3>=Lists!$I$4,1.5,
         IF(V3>=Lists!$I$3,1,
          IF(V3>=Lists!$I$2,0.5,
           IF(V3>=Lists!$I$8,0,"")
          )
         )
        )
       )
      )
     )
    )
   )
  )

решение3

Вот альтернатива: уменьшите вложенность.

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

[N]: {=IFERROR(CHOOSE(LARGE(ROW($H$2:$H$7)*(N3>=$H$2:$H$7),1)-1,0.5,1,1.5,2,2.5,3),IF(N3>=$H$8,0,""))}
[S]: {=IFERROR(CHOOSE(LARGE(ROW($I$2:$I$7)*(S3>=$I$2:$I$7),1)-1,0.5,1,1.5,2,2.5,3),IF(S3>=$I$8,0,""))}
[U]: {=IFERROR(CHOOSE(LARGE(ROW($H$2:$H$7)*(U3>=$H$2:$H$7),1)-1,0.5,1,1.5,2,2.5,3),IF(U3>=$H$8,0,""))}
[V]: {=IFERROR(CHOOSE(LARGE(ROW($I$2:$I$7)*(V3>=$I$2:$I$7),1)-1,0.5,1,1.5,2,2.5,3),IF(V3>=$I$8,0,""))}

=IF(ISBLANK($U3),IF($F3="Freshman",[N],[U],IF(ISBLANK($V3),[S],[V])))

В качестве альтернативы, если у вас есть место в ваших данных, я бы рекомендовал добавить значения 0.5 to 3 step 0.5в диапазоне J2:J7. Это может облегчить поддержку, поскольку вы можете изменить значения - если когда-либо возникнет необходимость - не трогая формулы. Если вы это сделали, формулы могут быть такими:

[N]: {=IFERROR(INDEX($J$2:$J$8,LARGE(ROW($H$2:$H$7)*(N3>=$H$2:$H$7),1)-1),IF(N3>=$H$8,0,""))}
[S]: {=IFERROR(INDEX($J$2:$J$8,LARGE(ROW($I$2:$I$7)*(S3>=$I$2:$I$7),1)-1),IF(S3>=$I$8,0,""))}
[U]: {=IFERROR(INDEX($J$2:$J$8,LARGE(ROW($H$2:$H$7)*(U3>=$H$2:$H$7),1)-1),IF(U3>=$H$8,0,""))}
[V]: {=IFERROR(INDEX($J$2:$J$8,LARGE(ROW($I$2:$I$7)*(V3>=$I$2:$I$7),1)-1),IF(V3>=$I$8,0,""))}

=IF(ISBLANK($U3),IF($F3="Freshman",[N],[U],IF(ISBLANK($V3),[S],[V])))

По всей вероятности, их можно было бы улучшить еще больше. Надеюсь, я хотя бы дал вам представление.

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