В Excel, как заменить все экземпляры текста в ячейке, если какой-либо текст соответствует значениям из диапазона ячеек)

Question

Чтобы упростить задачу пользователям этой книги, я настроил столбец J с проверкой данных (извлечение значений из Sheet2), в которой можно выбрать несколько типов событий (Академический календарь, Художественные выставки и т.д.).

Мне пришлось использовать какой-то код, чтобы это осуществить, но я нашел видео на YouTube, которое помогло мне пройти через него, поэтому отсутствие опыта кодирования в Excel не остановило меня.

Чтобы импортировать этот лист в другую программу, у меня не может быть "Академический календарь" в качестве типа события, мне нужен соответствующий код, «et-acacal». Соответствующий код находится в следующем столбце на листе, содержащем мои типы событий.

По сути, мне нужно заменить каждый экземпляр "Академического календаря" на «et-acacal» и "Художественные выставки" на «et-artgal», а также различные комбинации типов событий, таких как «Академический календарь, художественные выставки» на «et- акакал, эт-артгал "

Я нашел действительно дурацкий способ осуществить это, используя несколько функций SUBSTITUTE (см. Ниже), но это было утомительно, и я должен верить, что есть более простой способ сделать это. У меня было только 30 разных типов событий, что будет, если у меня будет 100 или 200?

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(J2,Sheet2!$A$30,Sheet2!$B$30),Sheet2!$A$29,Sheet2!$B$29),Sheet2!$A$28,Sheet2!$B$28),Sheet2!$A$27,Sheet2!$B$27),Sheet2!$A$26,Sheet2!$B$26),Sheet2!$A$25,Sheet2!$B$25),Sheet2!$A$24,Sheet2!$B$24),Sheet2!$A$23,Sheet2!$B$23),Sheet2!$A$22,Sheet2!$B$22),Sheet2!$A$21,Sheet2!$B$21),Sheet2!$A$20,Sheet2!$B$20),Sheet2!$A$19,Sheet2!$B$19),Sheet2!$A$18,Sheet2!$B$18),Sheet2!$A$17,Sheet2!$B$17),Sheet2!$A$16,Sheet2!$B$16),Sheet2!$A$15,Sheet2!$B$15),Sheet2!$A$14,Sheet2!$B$14),Sheet2!$A$13,Sheet2!$B$13),Sheet2!$A$12,Sheet2!$B$12),Sheet2!$A$11,Sheet2!$B$11),Sheet2!$A$10,Sheet2!$B$10),Sheet2!$A$9,Sheet2!$B$9),Sheet2!$A$8,Sheet2!$B$8),Sheet2!$A$7,Sheet2!$B$7),Sheet2!$A$6,Sheet2!$B$6),Sheet2!$A$5,Sheet2!$B$5),Sheet2!$A$4,Sheet2!$B$4),Sheet2!$A$3,Sheet2!$B$3),Sheet2!$A$2,Sheet2!$B$2)

Answer 1

Лучший способ справиться с этим - использовать функцию VLOOKUP . Будет выполнен поиск соответствующего кода события для имени события в ячейке J2.

=VLOOKUP(J2,Sheet2!$A$1:$B$999,2,0)

Тем не менее, у вас есть сложность нескольких критериев в одной ячейке. Чтобы получить VLOOKUP по нескольким критериям в одной и той же ячейке, вам потребуется заменить одну чрезмерно сложную вложенную функцию SUBSTITUTE другой - учитывая то, что вы спрашиваете, я не буду вдаваться в то, как это работает (много громоздких IF , MID и SEARCH ).

Лучше всего попытаться реорганизовать таблицу, чтобы не пытаться объединить все свои критерии в одной ячейке. Excel на самом деле не создан для этого - ячейка должна быть самой основной единицей (отсюда и название). Попробуйте использовать несколько столбцов и выполните VLOOKUP для каждого.