☰

Улучшение вложенных формул IF и CHOOSE

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2013
Улучшение вложенных формул IF и CHOOSE

Я получаю еженедельные статусы для наших открытых заказов на работу в виде CSV-файла. Это включает в себя 3-буквенные коды статуса, связанные с различными этапами в процессе завершения.

Мне нужно заменить коды статуса понятными определениями.

Моя электронная таблица делает это автоматически, но та электронная таблица, в которой я это делаю сейчас, кажется мне действительно неэффективной. Лист, который это делает, сам по себе весит более 75 МБ и тормозит все.

Мой текущий лист представляет собой сетку из 16 столбцов с кодами состояния из CSV в [@[SO Codes] и статическим списком кодов в строке 1. Каждая следующая строка имеет ширину 16 столбцов и:

=IF([@[SO Codes]]=Table3[[#Headers],[AA]],1,0)
=IF([@[SO Codes]]=Table3[[#Headers],[CA]],2,0)
=IF([@[SO Codes]]=Table3[[#Headers],[CAN]],3,0)
и т. д.

Таким образом, каждому коду статуса присваивается номер от 1 до 16, который отображается в сетке 16x[количество открытых заказов на работу].

Последний столбец:

=SUBTOTAL(9,E2:T2)
=SUBTOTAL(9,E3:T3)
=SUBTOTAL(9,E4:T4)
и т. д.

В конечном итоге это попадает в мой трекер в виде строк, которые выглядят следующим образом: =CHOOSE([@Status],IF([@Status]=J2,L2),IF([@Status]=J3,L3),IF([@Status]=J4,L4),IF([@Status]=J5,L5),IF([@Status]=J6,L6),IF([@Status]=J7,L7),IF([@Status]=J8,L8),IF([@Status]=J9,L9),IF([@Status]=J10,L10),IF([@Status]=J11,L11),IF([@Status]=J12,L12),IF([@Status]=J13,L13),IF([@Status]=J14,L14),IF([@Status]=J15,L15),IF([@Status]=J16,L16),IF([@Status]=J17,L17))

Где столбец L представляет собой список определений кодов открытого текста, а J3 получает промежуточные итоги из калькулятора кодов состояния.

Я не могу не согласиться с тем, что должен быть более элегантный и эффективный способ сделать это. Есть какие-нибудь идеи? Я пробовал использовать функцию =CHOOSE непосредственно на кодах из CSV, но она работает только с числовыми значениями. Я включил изображения каждого шага, потому что мне кажется, что я плохо это объяснил.

Калькулятор CSV-кода введите описание изображения здесь

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

решение1

Эту формулу выбора можно записать как

=Vlookup([@Status],$J$2:$L$17,3,False)

Не уверен, что понимаю, в чем заключается остальная часть проблемы.

решение2

Калькулятор кода:

Вместо длинной таблицы можно использовать одну MATCH():
=MATCH([@[SO CODES]],TableHelper[code list],0)
введите описание изображения здесь

Трекер

Я не уверен, что правильно понял вашу изначальную формулу.

=CHOOSE([@Status],IF([@Status]=J2,L2),IF([@Status]=J3,L3)...- Что произойдет, если [@Status]<>J2? Или это нереальный сценарий? Если да, то просто используйте простую INDEX()функцию:
=INDEX($L$2:$L$17,[@Status])

В противном случае укажите, что должна делать ваша формула.

Максимальная оптимизация:

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

Я пробовал использовать функцию =CHOOSE непосредственно для кодов из CSV, но она работает только с числовыми значениями.

На самом деле, вы можете передавать коды напрямую, просто используя VLOOKUP(), что-то вроде этого:
=VLOOKUP(<code>,$J:$K,2,false)

Где

  • <code>это код, для которого вы хотите получить описание
  • $J:$Kявляются ли столбцы кода и описания согласно вашему скриншоту

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