☰

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

tag-icon • tag-icon microsoft-excel worksheet-function
Автоматически заполнять строки с другого листа в Excel

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

Спасибо.

решение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 |

Лист2 имеет такой макет

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

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

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

Связанный контент