Поиск подходящих столбцов в Excel

Question

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

По сути, у меня есть две таблицы, A и B, и они имеют длину в тысячи строк.

Описание моей задачи: прямо сейчас (так как я не знаю лучше), я делаю это вручную:

1. Перейти к строке I в таблице B.
2. Выберите записи в столбцах B(a, b, c) этой же строки.
3. Найдите в таблице A строку, в которой столбец A(b) соответствует строке B(a).
4. Вставьте записи столбцов B(a) строки i в конец строки, найденной на последнем шаге.
5. Повторите для ряда я + 1.

Пример: строка B(cat, dog, mouse) соответствует A(mammal, cat, Mr. Whiskers) . Поэтому я вставляю B после A и получаю A(mammal, cat, Mr. Whiskers, cat, dog, mouse) .

Примечание: я не присоединяюсь к таблицам. Я просто расширяю таблицу A, вставляя строку A(b), если строка A(b) совпадает со строкой B(a).

Кроме того, иногда записи пишутся немного по-другому. Использование подстановочных знаков для поиска кандидатов будет полезным.

---

Как следует из описания, эта задача очень утомительна и неэффективна, если я не знаю, как автоматизировать некоторые операции (есть тысячи записей).

Любые быстрые советы о том, как быть более продуктивным, - большая помощь.

Answer 1

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

Учитывая ваш точный пример, вы можете сойти с рук с помощью VLOOKUP . Но я предполагаю, что реальность ситуации немного сложнее, чем ваш пример, поэтому я даю вам то, что может справиться практически с любой ситуацией. Это комбинация функции INDEX функции MATCH .

Я создал две таблицы:

Таблица A: Ряд 1(млекопитающее, хорьк, мистер Уискерс)

Таблица B:Row1(кошка, собака, мышь)

Таблица B:Row2(хорька, игуана, черепаха)

Таблица B: Ряд 3(лошадь, корова, свинья)

В Таблицу A: Столбец D я положил следующую формулу:

=INDEX(TableB!$A$1:TableB!$C$3,MATCH($B1,TableB!$A$1:TableB!$A$3,FALSE),1)

Во-первых, давайте посмотрим только на INDEX . INDEX принимает 3 аргумента: диапазон, по которому вы хотите выполнить поиск, строку значения, которое вы хотите, и столбец значения, которое вы хотите. Диапазон, который вы хотите найти, определяется TableB!$A$1:TableB!$C$3 .

TableB! говорит перейти на другой лист с именем TableB

Знаки доллара перед столбцом и строкой говорят: «они абсолютные, не увеличиваются автоматически при заполнении формулы вниз (или вправо, влево, вверх)»

1 в конце говорит о "первом столбце диапазона", который в нашем случае является А и содержит кошку, хорька и лошадь.

Теперь, чтобы получить строку, нам нужно использовать другую функцию с именем MATCH чтобы сопоставить значение из строки, над которой мы сейчас работаем. MATCH также принимает 3 значения. Первое - это значение, которое вы ищете, второе - это один столбец для поиска, а последнее - как соответствовать (в этом случае мы получаем FALSE для "точного соответствия").

Мы хотим сопоставить значение, которое находится в TableA:ColumnB для текущей строки. Итак, первое значение - $ B1. Который говорит, что столбец B является абсолютным, не увеличивайте его. Но строка должна увеличиваться, чтобы соответствовать строке, в которой мы находимся, когда заполняем, чтобы у нее не было предшествующего знака доллара.

Столбец для поиска является столбцом A в TableB и является абсолютным, поэтому он не будет увеличиваться.

Если мы затем перейдем к TableA:ColumnE и вставим:

=INDEX(TableB!$A$1:TableB!$C$3,MATCH($B1,TableB!$A$1:TableB!$A$3,FALSE),2)

А в Таблицу A: Столбец F мы помещаем:

=INDEX(TableB!$A$1:TableB!$C$3,MATCH($B1,TableB!$A$1:TableB!$A$3,FALSE),3)

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

Если мы изменим TableA:ColumnB на "ferret", то столбец D изменится на хорька, столбец E - на игуану, а столбец F - на черепаху.

Наконец, выделите ячейки в строке 1 под D, E и F. Перетащите мышь вниз, чтобы покрыть все необходимые строки, и нажмите Ctrl+D, чтобы заполнить формулу по всем строкам, увеличивая поисковый термин в функции MATCH искать значение в столбце B каждой строки.

Дальнейшее чтение:

http://www.mrexcel.com/articles/excel-vlookup-index-match.php