英文:
Excel: View Column A from Multiple Worksheets
问题
我有许多Excel表格，其中包括以下类别：
汽车工作表1 - 列A包含汽车零件（发动机、轮胎、挡风玻璃等），
家具工作表2 - 列包含家具（沙发、灯、桌子），
厨房工作表3 - 列A包含厨房用品（水槽、微波炉、冰箱）。
我想创建一个"Everything"工作表，其中包含所有工作表的列A的内容（无需复制和粘贴）。
它将按顺序列出所有工作表的内容：发动机、轮胎、挡风玻璃、沙发、灯、桌子、水槽、微波炉、冰箱。
如何实现这一目标？我在“数据”->“合并”中没有看到相关功能。
我更倾向于使用Excel公式而不是数据透视表。但是，如果数据透视表/查询是唯一的选项，我将使用它作为答案。
英文:
I have many excel sheets with Categories
Car Worksheet1 - Column A has car parts (Engine, tire, windshield, etc),
Furniture Worksheet2 - Column has a furniture (Couch, lamp, table)
Kitchen Worksheet3 - Column A has kitchen parts (sink, microwave, refrigerator),
I want to create a Everything worksheet that contains All the worksheets Column A (without copying and pasting).
It would list everything in Worksheet sequentially: Engine, tire, windshield, couch, lamp, table, sink, microwave, refrigerator
How can I accomplish this? I didn't see the function in Data-> Consolidate
Prefer to use excel equations vs pivot table. However, if pivot table/query is the only option, I will use as answer then.
答案1
得分: 4
=TOCOL(
VSTACK(
Car:Kitchen!A2:A1000
),
1
)
=TOCOL(Car:Kitchen!A:A,1)
英文:
Assuming no Excel Constraints as per your tags. Then the following formula will work for your requirements.
• Formula used in cell A2
=TOCOL(
VSTACK(
Car:Kitchen!A2:A1000
),
1
)
Or, just using TOCOL() as mentioned below by JvdV Sir
• Formula used in cell A2
=TOCOL(Car:Kitchen!A:A,1)
Note: TOCOL(), with parameter 1 ignores the blanks.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论