Автоматически заполнять строки с другого листа в Excel

Question

У меня есть рабочая книга, которую я использую, чтобы отслеживать продукт, который я заказал. На данный момент у него есть 2 листа - один представляет собой инвентарный список каждого товара, организованный в строках со всеми подробностями (идентификатор продукта, минимальное / максимальное количество, описание, цена и т.д.) В каждом столбце. На втором листе я отслеживаю, когда товары были заказаны, вместе с номерами заказов на покупку. В настоящее время я копирую строку товара, который заказываю, из инвентарного листа и вставляю его на второй лист вместе с номером заказа на покупку, датой и заказанным количеством. Я сохраняю это как текущий список, который я продолжаю добавлять, чтобы я мог видеть тренды и историю заказов. Есть ли способ на втором листе ввести идентификатор продукта, нажать клавишу ввода и заполнить строку информацией о том же идентификаторе продукта из инвентарного листа? Это позволит сэкономить огромное количество времени на устранение копирования и вставки, поскольку существуют тысячи идентификаторов продуктов и их информация.

Благодарю.

Answer 1

Для такого размера коллекции, возможно, вам стоит взглянуть на использование Access или другого решения для базы данных. Тем не менее, здесь идет.

Используйте функцию VLOOKUP в Excel. В качестве примера:

Лист1 имеет этот макет

     |     A      |   B   |   C   |   D   |   E   |
1    | Product ID | Max Q | Min Q | Desc. | Price | 
2    |  ######### |   ### |    ## | xxxxx | 34.29 |
...
2359 |  ######### |   ### |    ## | xxxxx | 54.28 |

Sheet2 имеет этот макет

     |     A     |     B     |    C    |     D     |     E      |   F   |   G   |
1    |Order date | Recv Date | Order Q | P.O. Num. | Product ID | Desc. | Price |
2    |           |           |         |           |            |       |       |

В ячейке Листа E2 вы вводите Идентификатор продукта, который совпадает с некоторым идентификатором в столбце Листа 1. В ячейку Листа 2 F2 вводится формула =VLOOKUP(E2,Sheet1!$A$2:$E$2359,4,TRUE) и в G2 вы вводите формулу =VLOOKUP(A1,Sheet1!$A$1:$H$2359,5,TRUE) . Ссылки на диапазон являются абсолютными $ поэтому они не изменяются, поскольку формула копируется вниз по странице, а 3-й параметр - это столбец "внутри" диапазона, который имеет желаемое значение. Если, например, Sheet1 начинается в столбце H, то диапазон изменится на Sheet1!$H$2:$L$2 но 4 для Desc. и 5 за цену не изменится. Можно обойтись без необходимости обновлять диапазон в формулах каждый раз, когда вы добавляете продукт, используя именованный диапазон, для этого обратитесь к справке Excel. Столбец идентификатора продукта должен быть отсортирован для достижения наилучшего эффекта в соответствии с указанным размером набора данных, хотя это не требуется для VLOOKUP, и вам придется копировать формулы из строки 2 настолько, насколько вы считаете нужными (сколько бы тысяч строк не было). возможно.) Если листы имеют имена, поместите их в одинарные кавычки, например =VLOOKUP(E2,'Inventory List'!$A$2:$E$2359,4,TRUE) . В формуле 1-й аргумент E2 - это ячейка для сравнения, 2-й аргумент Sheet1!$A$1:$H$2359 - это диапазон для поиска - поиск выполняется только в первом столбце, 3-й аргумент - это столбец в диапазоне для возвращаемых данных, а 4-й аргумент TRUE точное совпадение.

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