(Excel 2013)具有VLOOKUP功能的3D公式 [英] (excel 2013) 3D formula with VLOOKUP function

问题描述

前段时间，我在这里问类似的问题 具有相同单元格位置的3D公式 但是现在我面临着更加困难的问题.我有一个名为Storage的工作表，其他名为shop1，shop2的工作表.

some time ago i was asking similar question in here 3D formula with the same position of cells but now i am facing much more difficult problem. I have a sheet named Storage and other sheets called simply shop1, shop2.

在仓库中，我所有的商品都已出售，但并非在每家商店中我都出售相同的商品.因此，在某些商店中，您找不到特定的商品. 现在我想要什么.在每张纸上，我存储已售商品的数量，在存储中，我想获得所有已售商品的总和.所以我会查询每个商店是否出售了存储中的项目，如果是，它将查询编号并将其加和.

In storage i have all of my good which are sold but not in every shop i sell the same and all goods. so in some shop you dont find specific good for example. And now what i want. In every sheet i store number of sold goods and in storage i would like to have the SUM number of all sold goods. so i would lookup if an item from the storage is sold in each shop and if yes, it would lookup the number and add it to sum.

如果存储中的项目始终位于同一行，则很容易.例如，第2行上所有项目的总和就是

it is easy if the items from the storage would be always on the same row. for example the sum for all items on the row 2 would be

=SUM(Shop1:Shop4!C2)

但是会有很多白色和浪费的地方，因为商店只出售部分仓储产品.所以现在我想在其中使用VLOOKUP函数.我想我可以使用

but there would be plenty white and waste places because the shop sell only part of goods from storage. so now i would like to use VLOOKUP function into it. i thaught i can use

=SUM(VLOOKUP(A2;shop1:shop2!A:B;2;FALSE))

但是这个公式给我错误信息.那么如何将3D公式赋予VLOOKUP函数呢?

but this formula gives me error message. so how to give 3D formula into VLOOKUP function?

感谢回答

我想补充一点，存储中的商品并不总是在每个商店的同一行上，因此例如item1可以在shop1工作表的第三行中，但可以在shop2工作表的第十行以及在shop3工作表中，您甚至都找不到item1.

I want to add that an item from the storage is not always on the same row on each shop, so for example item1 can be on the third row in shop1 sheet but on the 10th row on the shop2 sheet and in the shop3 sheet you will not even find item1.

推荐答案

首先转到名称管理器"(公式"标签)并定义以下内容:

First go to Name Manager (Formulas tab) and define the following:

名称: SheetList

Name: SheetList

引用: = {"shop1"，"shop2"，"shop3"，"shop4"}

Refers to: ={"shop1","shop2","shop3","shop4"}

(或碰巧的工作表名称是什么.)

(Or whatever happen to be the sheet names in question.)

则所需公式为:

= SUMPRODUCT(SUMIF(INDIRECT('"& SheetList&'！A:A"); A2; INDIRECT('& SheetList&"'！B:B)))

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A:A");A2;INDIRECT("'"&SheetList&"'!B:B")))

致谢

这篇关于(Excel 2013)具有VLOOKUP功能的3D公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！