Использование VLOOKUP и нескольких листов в Excel

Question

Вот проблема:

Я строю систему инвентаризации запасов. Он имеет 3 листа: Транзакции (где вводятся совершенные транзакции), Получено (там, где вводятся новые полученные запасы) и Запас (где отображаются затраты и количество на складе).

Страницы «Транзакции» и «Получено» используют раскрывающиеся списки для выбора элементов из ассортимента продуктов (которые хранятся на листе Backend).

На странице запаса, скажем, в ячейке "Бисквиты" Количество на складе, я хотел бы просмотреть любые вхождения "Бисквиты" на листе транзакций с сегодняшнего дня или раньше, получая количества каждого из них. Затем мне нужно сделать то же самое с полученным листом и минус два числа, чтобы получить фактическое количество на складе.

Я рассмотрел многочисленные руководства по использованию функции VLOOKUP с несколькими листами, однако все они, похоже, возвращают значение на том же листе, что и значение поиска, а не на другом листе. Это приводит к сообщению # N/A каждый раз.

Лист можно найти здесь

https://www.dropbox.com/s/pf1wzsa7ruhjzc1/stockSystem.xlsx?dl=0

чтобы вы могли видеть проблему более четко.

Если бы кто-то мог указать мне правильное направление, это было бы оценено.

Спасибо Люк

Answer 1

Ответ Рохита хороший. Особенно второй вариант, так как он намного легче и все еще возвращает желаемый результат.

Другой вариант - использовать INDEX и MATCH. Таким образом, вы можете исключить требование к значению поиска в первом столбце VLOOKUP -

=INDEX(Received!$E$4:$E$10, MATCH(A6, Received!$D$4:$D$10, 0))

Answer 2

Так как ваш лист сохраняется как .xlsx, я полагаю, что вы используете Excel 2007 или выше. Хотя в вашей функции VLOOKUP есть ошибка, в принципе вы можете не получить желаемых результатов, если в вашем случае вы используете VLOOKUP или INDEX MATCH.

То, что вы пытаетесь сделать, - это поиск Предмета в «Транзакциях» и «Полученных» листах с двумя условиями, согласно вашим встроенным комментариям в них.

1 - название предмета должно совпадать

2 - Дата должна быть меньше или равна текущей дате

При соблюдении этих двух условий необходимо сложить количество.

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

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

Смотрите скриншоты ниже.

В ячейке B6 листа с именем Stock поместите следующую формулу и перетащите ее вниз. В этом примере я взял небольшой диапазон ячеек, скажем от A4 до A14, однако его можно изменить согласно вашему листу.

=SUMIFS(Received!$E$4:$E$14,Received!$A$4:$A$14,"<="&NOW(),Received!$D$4:$D$14,A6)-SUMIFS(Transactions!$E$4:$E$15,Transactions!$A$4:$A$15,"<="&NOW(),Transactions!$D$4:$D$15,A6)

Answer 3

Ваша формула VLOOKUP = VLOOKUP(A6, Получено!A4:F27,5, FALSE). Тем не менее, он ищет «Печенье» в столбце A, но этот элемент фактически находится в столбце D. VLOOKUP Ищет значение в первом столбце таблицы.

Поменяйте местами столбцы A & D на листе «Получено», если с вами это работает, и VLOOKUP должен работать нормально.

Или измените формулу на = VLOOKUP(A6, Получено!D4:F27,2, FALSE) в случае, если первые 3 столбца не имеют значения, и поиск будет выполнен правильно.