Поиск подходящих столбцов в Excel
Я никогда не использовал Excel раньше, поэтому мне нужно самое простое из доступных решений, и на этой неделе это рабочее задание, поэтому у меня не было времени, чтобы прочитать большую часть документации.
По сути, у меня есть две таблицы, A и B, и они имеют длину в тысячи строк.
Описание моей задачи: прямо сейчас (так как я не знаю лучше), я делаю это вручную:
- Перейти к строке I в таблице B.
- Выберите записи в столбцах B(a, b, c) этой же строки.
- Найдите в таблице A строку, в которой столбец A (b) соответствует строкеB(a).
- Вставьте записи столбцов B(a) строки i в конец строки, найденной на последнем шаге.
- Повторите для ряда я + 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).
Кроме того, иногда записи пишутся немного по-другому. Использование подстановочных знаков для поиска кандидатов будет полезным.
Как следует из описания, эта задача очень утомительна и неэффективна, если я не знаю, как автоматизировать некоторые операции (есть тысячи записей).
Любые быстрые советы о том, как быть более продуктивным, - большая помощь.
1 ответ
Ответ, пожалуй, одна из самых мощных функций, которые вы можете создать в Excel. Если вы новичок, чтобы преуспеть, это будет много, чтобы проглотить.
Учитывая ваш точный пример, вы можете избежать использования VLOOKUP, Но я предполагаю, что реальность ситуации немного сложнее, чем ваш пример, поэтому я даю вам то, что может справиться практически с любой ситуацией. Это сочетание INDEX функция и MATCH функция.
Я создал две таблицы:
Таблица A: Ряд 1(млекопитающее, хорьк, мистер Уискерс)
Таблица B:Row1(кошка, собака, мышь)
Таблица B:Row2(хорька, игуана, черепаха)
Таблица B:Row3(лошадь, корова, свинья)
В Таблицу 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