Формула Microsoft Excel с использованием таблицы для классификации данных на строку

Question

У меня есть два листа в документе Excel, которые я пытаюсь сравнить. У меня есть список кодов городов в столбце B листа 1. В листе 2 у меня есть список всех кодов городов в столбцах с A по D с заголовками «Восточный, Центральный, Горный, Тихий» в первом ряду каждого столбца, за которым следуют по всем телефонным кодам в каждой зоне.

Я хочу классифицировать каждый код города на листе 1 в столбце C по соответствующему часовому поясу на листе 2.

Как мне это сделать?

Вот скриншоты основных таблиц, которые у меня есть.

Лист 1:

Лист 2:

Answer 1

Вот как это сделать с вашими данными, выложенными в точности так, как у вас есть сейчас. Эта формула идет в C2 на вашем Sheet1:

=IF(ISNA(MATCH(B2,Sheet2!$A$1:$A$40,0)),IF(ISNA(MATCH(B2,Sheet2!$B$1:$B$40,0)),IF(ISNA(MATCH(B2,Sheet2!$C$1:$C$40,0)),IF(ISNA(MATCH(B2,Sheet2!$D$1:$D$40,0)),"Not Found","Pacific"),"Mountain"),"Central"),"Eastern")

Это некрасиво, но это работает. Возможно, вам придется настроить четыре абсолютные ссылки на Sheet2 (например, Sheet2!$A$1:$A$40), если ваши данные на этом листе идут после строки 40.

Окончательная формула состоит из четырех использований функции MATCH, каждый из которых ищет только один столбец кодов городов.

Вот как я построил формулу.

Я использовал функцию MATCH. MATCH может искать значение только в одном столбце. Сначала я создал четыре отдельные формулы MATCH. Каждый ищет только один из столбцов часового пояса. Например, чтобы найти в "восточном" столбце значение (код города), найденное в ячейке B2:

=MATCH(B2,Sheet2!$A$1:$A$40,0)

Предположим, что B2 содержит 201, это возвращает 2 потому что 201 является вторым элементом в столбце. Но если B2 содержит 205, это возвращает #N/A Мы поймем это, обернув формулу формулой ISNA:

=IF(ISNA(MATCH(B2,Sheet2!$A$1:$A$40,0)),"no_next_formula","Eastern")

ISNA проверяет, возвращает ли формула MATCH #N/A Если yes/true, возвращается "no_next_formula". Если нет / ложно, возвращается "восточный", потому что MATCH нашел наш код города в столбце восточного кода города.

Мы можем создать четыре из этих формул, соответствующим образом изменив диапазон, указанный в Sheet2, для поиска трех других кодов области. Пример рабочего листа может выглядеть так:

Теперь, чтобы объединить их в единую формулу, показанную выше, я скопировал всю формулу в моем столбце F (за исключением знака = ) и заменил текст "no_next_formula" в формуле столбца E.

Формула в F2:

=IF(ISNA(MATCH(B2,Sheet2!$D$1:$D$40,0)),"Not Found","Pacific")

Формула в Е2 (до):

=IF(ISNA(MATCH(B2,Sheet2!$C$1:$C$40,0)),"no_next_formula","Mountain")

Формула в E2 (после вставки формулы из F2):

=IF(ISNA(MATCH(B2,Sheet2!$C$1:$C$40,0)),IF(ISNA(MATCH(B2,Sheet2!$D$1:$D$40,0)),"Not Found","Pacific"),"Mountain")

Затем я повторил эти шаги, скопировав формулу теперь в E2 чтобы заменить текст "no_next_formula" в формуле D2 . Как только это будет сделано для формулы в C2 мы получим полную формулу, показанную выше.

Answer 2

Если вы уверены, что ваши данные правильно сформированы (в частности, код города не отображается более одного раза на листе 2), вы можете использовать

=SUM(COLUMN(Sheet2!A$2:D$99)*(Sheet2!A$2:D$99=B2))

найти номер столбца (на листе 2), где появляется код города (в B2).  (Замените 99 номером последней строки на листе 2, где у вас есть коды города.)  Это формула массива ; Вы должны нажать Ctrl+Shift+Enter после того, как напечатали (или вставили) его.  Это создает виртуальный массив, в котором каждая ячейка содержит номер столбца, умноженный на значение истинности того, равно ли значение ячейки B2 , например:

1*(Sheet2!A2=Sheet1!B2)   2*(Sheet2!B2=Sheet1!B2)   3*(Sheet2!C2=Sheet1!B2)   4*(Sheet2!D2=Sheet1!B2)
1*(Sheet2!A3=Sheet1!B2)   2*(Sheet2!B3=Sheet1!B2)   3*(Sheet2!C3=Sheet1!B2)   4*(Sheet2!D3=Sheet1!B2)
1*(Sheet2!A4=Sheet1!B2)   2*(Sheet2!B4=Sheet1!B2)   3*(Sheet2!C4=Sheet1!B2)   4*(Sheet2!D4=Sheet1!B2)
            ︙                        ︙                        ︙                        ︙

Так, например, если Sheet1!B2 303, это становится

1*(201=303)               2*(205=303)               3*(208=303)               4*(206=303)
1*(202=303)               2*(210=303)               3*(303=303)               4*(209=303)
1*(203=303)               2*(214=303)               3*(307=303)               4*(213=303)
      ︙                        ︙                        ︙                        ︙

т.е.

1*FALSE                   2*FALSE                   3*FALSE                   4*FALSE
1*FALSE                   2*FALSE                   3*TRUE                    4*FALSE
1*FALSE                   2*FALSE                   3*FALSE                   4*FALSE
   ︙                        ︙                        ︙                        ︙

Так как TRUE равно 1, а FALSE равно 0, это сводится к

   0                         0                         0                         0
   0                         0                     →   3   ←                     0
   0                         0                         0                         0
︙   ︙                        ︙                        ︙                        ︙

и, конечно, SUM этого равна 3, который является номером столбца, где 303 найдено.

Если вы уверены, что каждый код города на листе 1, столбец B присутствует на листе 2, тогда вы можете получить название часового пояса, просто вставив номер столбца в функцию INDEX :

=INDEX(Sheet2!A$1:D$1, 1, SUM(COLUMN(Sheet2!A$2:D$99)*(Sheet2!A$2:D$99=B2)))

(не забудьте использовать Ctrl+Shift+Enter), но если у вас есть код города на листе 1, которого нет на листе 2, то Sheet2!whatever=B2 равно ЛОЖЬ для каждой ячейки, виртуальный массив равен нулю, SUM равна 0, и INDEX может вернуть что-то вводящее в заблуждение.  Чтобы справиться с этим, установите Z2 в

=SUM(COLUMN(Sheet2!A$2:D$99)*(Sheet2!A$2:D$99=B2))

(не забудьте использовать Ctrl+Shift+Enter) и установите C2 в

=IF(Z2=0, "Not Found", INDEX(Sheet2!A$1:D$1, 1, Z2))