Я пытаюсь объединить несколько (более 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"
)))))))))))))))))))))))))))))))
Буду признателен за любую помощь, которую я могу получить. Очевидно, мне это нужно, лол!