Как объединить несколько операторов If в Excel

Question

Я пытаюсь объединить несколько (более 64) операторов If в одну ячейку. Я не очень хорошо владею Excel, поэтому могу даже ошибиться.

То, что я пытаюсь сделать, это:

Если [(Cell 1 = X) AND (Cell 2 = Y) then Cell 3 = Z]
или же
Если [(Cell 1 = X) AND (Cell 2 = B) the Cell 3 = C]

У меня есть 100 возможностей для Cell 1 и 2 возможности для Cell 2. Сочетание ячеек 1 и 2 даст конкретный ответ в ячейке 3.

Я надеюсь, что это имеет смысл.

До сих пор я пробовал следующее утверждение If, которое работает, но слишком долго (длиннее 64).

=IF(AND(B6="AF001.1",C6="US"),"5.5",
IF(AND(B6="AF010",C6="US"),"17.2",
IF(AND(B6="Z284.1",C6="US"),"8.5",
IF(AND(B6="Z284.2",C6="US"),"17.8",  
IF(AND(B6="AF016.1",C6="US"),"7.5",
IF(AND(B6="AF016.2",C6="US"),"7.5",
IF(AND(B6="AF016.3",C6="US"),"7.5",
IF(AND(B6="AF016.4",C6="US"),"7.5",
IF(AND(B6="AF016.5",C6="US"),"7.5",
IF(AND(B6="AF016.6",C6="US"),"7.5",
IF(AND(B6="AF016.7",C6="US"),"7.5",
IF(AND(B6="AF016.8",C6="US"),"7.5",
IF(AND(B6="AF016.9",C6="US"),"7.5",
IF(AND(B6="AF016.10",C6="US"),"7.5",
IF(AND(B6="AF016.11",C6="US"),"7.5",
IF(AND(B6="AF016.12",C6="US"),"7.5",
IF(AND(B6="AF016.13",C6="US"),"7.5",
IF(AND(B6="ST001",C6="US"),"6",
IF(AND(B6="ST002.2",C6="ALL"),"36.5",
IF(AND(B6="ST002.3",C6="ALL"),"36.5",
IF(AND(B6="ST002.4",C6="ALL"),"36.5",
IF(AND(B6="ST002.5",C6="ALL"),"36.5",
IF(AND(B6="ST002.6",C6="ALL"),"36.5",
IF(AND(B6="ST002.7",C6="ALL"),"36.5",
IF(AND(B6="ST002.8",C6="ALL"),"36.5",
IF(AND(B6="ST002.9",C6="ALL"),"36.5",
IF(AND(B6="ST002.10",C6="ALL"),"36.5",
IF(AND(B6="ST002.11",C6="ALL"),"36.5",
IF(AND(B6="ST002.12",C6="ALL"),"36.5",
IF(AND(B6="ST003.1",C6="ALL"),"36.5",
IF(AND(B6="ST003.2",C6="ALL"),"36.5",
IF(AND(B6="ST003.3",C6="ALL"),"36.5",
IF(AND(B6="ST003.4",C6="ALL"),"36.5",
IF(AND(B6="ST003.5",C6="ALL"),"36.5",
IF(AND(B6="ST003.6",C6="ALL"),"36.5",
IF(AND(B6="ST003.7",C6="ALL"),"36.5",
IF(AND(B6="ST003.8",C6="ALL"),"36.5",
IF(AND(B6="ST003.9",C6="ALL"),"36.5",
IF(AND(B6="ST003.10",C6="ALL"),"36.5",
IF(AND(B6="ST003.11",C6="ALL"),"36.5",
IF(AND(B6="ST003.12",C6="ALL"),"36.5",
IF(AND(B6="ST004.1",C6="US"),"11.5",
IF(AND(B6="ST004.2",C6="US"),"11.5",
IF(AND(B6="ST004.3",C6="US"),"11.5",
IF(AND(B6="ST004.4",C6="US"),"11.5",
IF(AND(B6="ST005",C6="ALL"),"21.5",
IF(AND(B6="ST006.1",C6="ALL"),"80",
IF(AND(B6="ST006.2",C6="ALL"),"80",
IF(AND(B6="ST006.3",C6="ALL"),"80",
IF(AND(B6="ST006.4",C6="ALL"),"80",
IF(AND(B6="ST006.5",C6="ALL"),"80",
IF(AND(B6="ST006.6",C6="ALL"),"80",
IF(AND(B6="ST006.7",C6="ALL"),"80",
IF(AND(B6="ST006.8",C6="ALL"),"80",
IF(AND(B6="ST006.9",C6="ALL"),"80",
IF(AND(B6="ST006.10",C6="ALL"),"80",
IF(AND(B6="ST006.11",C6="ALL"),"80",
IF(AND(B6="ST006.12",C6="ALL"),"80",
IF(AND(B6="ST006.13",C6="ALL"),"80",
IF(AND(B6="ST006.14",C6="ALL"),"80",
IF(AND(B6="ST006.15",C6="ALL"),"80",
IF(AND(B6="ST006.16",C6="ALL"),"80",
IF(AND(B6="ST006.17",C6="ALL"),"80",
IF(AND(B6="ST006.18",C6="ALL"),"80",
IF(AND(B6="ST007",C6="ALL"),"19",
IF(AND(B6="ST008.1",C6="US"),"12.5",
IF(AND(B6="ST008.2",C6="US"),"12.5",
IF(AND(B6="ST008.3",C6="US"),"12.5",
IF(AND(B6="ST008.4",C6="US"),"12.5",
IF(AND(B6="ST009.1",C6="ALL"),"108",
IF(AND(B6="ST009.2",C6="ALL"),"108",
IF(AND(B6="ST009.3",C6="ALL"),"108",
IF(AND(B6="ST009.4",C6="ALL"),"108",
IF(AND(B6="ST009.5",C6="ALL"),"108",
IF(AND(B6="ST009.6",C6="ALL"),"108",
IF(AND(B6="ST009.7",C6="ALL"),"108",
IF(AND(B6="ST009.8",C6="ALL"),"108",
IF(AND(B6="ST009.9",C6="ALL"),"108",
IF(AND(B6="ST009.10",C6="ALL"),"108",
IF(AND(B6="ST009.11",C6="ALL"),"108",
IF(AND(B6="ST009.12",C6="ALL"),"108",
IF(AND(B6="ST009.13",C6="ALL"),"108",
IF(AND(B6="ST009.14",C6="ALL"),"108",
IF(AND(B6="ST009.15",C6="ALL"),"108",
IF(AND(B6="ST009.16",C6="ALL"),"108",
IF(AND(B6="ST009.17",C6="ALL"),"108",
IF(AND(B6="ST009.18",C6="ALL"),"108",
IF(AND(B6="ST009.19",C6="ALL"),"108",
IF(AND(B6="ST009.20",C6="ALL"),"108",
IF(AND(B6="ST009.21",C6="ALL"),"108",
IF(AND(B6="ST009.22",C6="ALL"),"108",
IF(AND(B6="ST009.23",C6="ALL"),"108",
IF(AND(B6="ST009.24",C6="ALL"),"108",
IF(AND(B6="ST009.25",C6="ALL"),"108",
IF(AND(B6="ST009.26",C6="ALL"),"108",
IF(AND(B6="ST009.27",C6="ALL"),"108",
IF(AND(B6="ST009.28",C6="ALL"),"108",
IF(AND(B6="ST010",C6="US"),"5.3",
IF(AND(B6="ST010",C6="ALL"),"6.8",
IF(AND(B6="ST008.1",C6="ALL"),"14",
IF(AND(B6="ST008.2",C6="ALL"),"14",
IF(AND(B6="ST008.3",C6="ALL"),"14",
IF(AND(B6="ST008.4",C6="ALL"),"14",
IF(AND(B6="ST004.1",C6="ALL"),"13",
IF(AND(B6="ST004.2",C6="ALL"),"13",
IF(AND(B6="ST004.3",C6="ALL"),"13",
IF(AND(B6="ST004.4",C6="ALL"),"13",
IF(AND(B6="ST001",C6="ALL"),"7.5",
IF(AND(B6="AF016.1",C6="ALL"),"9",
IF(AND(B6="AF016.2",C6="ALL"),"9",
IF(AND(B6="AF016.3",C6="ALL"),"9",
IF(AND(B6="AF016.4",C6="ALL"),"9",
IF(AND(B6="AF016.5",C6="ALL"),"9",
IF(AND(B6="AF016.5",C6="ALL"),"9",
IF(AND(B6="AF016.7",C6="ALL"),"9",
IF(AND(B6="AF016.8",C6="ALL"),"9",
IF(AND(B6="AF016.9",C6="ALL"),"9",
IF(AND(B6="AF016.10",C6="ALL"),"9",
IF(AND(B6="AF016.11",C6="ALL"),"9",
IF(AND(B6="AF016.12",C6="ALL"),"9",
IF(AND(B6="AF016.13",C6="ALL"),"9",
IF(AND(B6="Z284.1",C6="ALL"),"9.5",
IF(AND(B6="AF001.1",C6="ALL"),"7"
)))))))))))))))))))))))))))))))

Буду признателен за любую помощь, которую я могу получить. Очевидно, мне это нужно, лол!

Answer 1

Вам было бы гораздо лучше создать таблицу подстановок с 3 столбцами:

1. The 100 possibilities for Cell 1.
2. The 100 answers for Cell 1 and possibility 1 of Cell 2.
3. The 100 answers for Cell 1 and possibility 2 of Cell 2.

Затем используйте VLOOKUP() для поиска в ячейке 1 и возврата ответа из столбца 2 или столбца 3 таблицы поиска, в зависимости от значения ячейки 2.

Вот пример с 10 строками:

Функция VLOOKUP() находит значение ячейки 1 в столбце 1 таблицы поиска и возвращает соответствующее значение из столбца 2 или столбца 3, используя предложение IF(), которое проверяет значение ячейки 2, чтобы определить столбец.

Формула в B6:

=VLOOKUP(A2,D2:F11,IF(B2="Value 1",2,3),FALSE)

При необходимости вы можете скрыть таблицу поиска или переместить ее на другой лист.

Вот больше информации о VLOOKUP() и IF(). Вам также следует проверить справку Excel.

Answer 2

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

Теперь вам нужно удалить свою бизнес-логику из оператора if и вместо этого поместить ее в удобное для обслуживания место, а затем использовать (относительно) простую функцию поиска для получения правильного значения. Серьезно, что вы будете делать, если значения для ST008.6 изменятся?

Что вы должны сделать, это:

На новом листе настройте таблицу. Поместите ваши значения B вниз (в столбце A, начиная со строки 2), а значения C через верх в строке 1. Заполните целевые значения в точках пересечения. Сортировать по столбцу А.

         US    CA    ALL
AF001.1  5.5         7
AF010   17.2
...etc...
Z284.1   8.5         9.5

Настройте вторую таблицу, которая преобразует ваши метки верхней строки в номера столбцов:

US      2
CA      3
ALL     4

Обратите внимание, что номера деталей, или как они там, являются столбцом 1; нам не нужно определение для этого в этой таблице поиска.

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

• выделите первую таблицу (исключая верхнюю строку)
• щелкните правой кнопкой мыши и выберите «Определить имя ...»
• дать ему разумное имя (в этом примере я назову его XREF)
• сделать то же самое для второй таблицы (я назову это CLKP)

Наконец, на вашем основном листе замените это ужасное утверждение на vlookup или два:

=VLOOKUP(B6,XREF,VLOOKUP(C6,CLKP,2,FALSE),FALSE)

Это находит значение в ячейке B6, где бы оно ни находилось в первом столбце нашего диапазона XREF, и затем преобразует значение в C6 в номер нужного нам столбца, а затем выбирает значение для этого элемента в этом столбце, чтобы перейти в текущий клетка.