英文:
Excel: Automatically Bring Column Data From One Table into Other Tables
问题
我有一个Excel文档里有多个表格。第一个表格叫做"Companies"，其中有一个叫做"Watchlist"的表格。该表格包含了一份有关这些公司的基本数据的公司列表。例如：
* 股票代码
-- MA
-- MSFT
-- TGT
-- 等等。
* 公司名称
-- 万事达卡
-- 微软
-- Target
-- 等等。
* 行业
-- 金融服务
-- 科技
-- 消费防御
-- 等等。
* 等等。
现在，我有另一个名为"Price"的表格，其中也有一个叫做"Price"的表格，该表格包含以下列：
* 当前价格
* 52周最低价
* 52周最高价
* 市盈率
* 等等。
我需要做的是自动将"Watchlist"表格上的股票代码列（包含完整股票代码列表）导入到"Price"表格上的"Price"表格中作为一列。
我不想做的是将列表复制并粘贴到Price表格中。除了Price表/表之外，我还有许多其他表格和表。复制并粘贴列表不仅繁琐，还意味着我必须在多个位置维护相同的列表（这不是我想要的）。
因此，我想知道将Watchlist表格中的股票代码列表合并到电子表格中的其他表格的最简单和/或最有效的方法是什么？
英文:
I have multiple sheets in a excel document. The first sheet I have is called "Companies" which has a table called "Watchlist". That table contains a list of companies with some basic data concerning those companies. So, for example:
* Ticker Symbol
-- MA
-- MSFT
-- TGT
-- etc.
* Company Name
-- Master Card
-- Microsoft
-- Target
-- Etc.
* Sector
-- Financial Services
-- Technology
-- Consumer Defensive
-- etc.
* Etc.
Now, I have another sheet called "Price" with a table that is also called "Price", which has the following columns:
* Current Price
* 52-Week Low
* 52-Week High
* P/E Ratio
* Etc.
What I need to do is to automatically import the Ticker Symbol column (with the full list of tickers) from the "Watchlist" table on the "Companies" sheet and add that as a column in the "Price" table on the "Price" sheet.
What I do NOT want to do is to copy and paste the list into the Price table. I have numerous other sheets and tables besides the Price sheet/table. Copying and pasting the list into each table is not only tedious, but would mean that I have to maintain the same list in multiple locations (something I don't want to do).
As such, I am wondering what is the easiest and/or most efficient way for me to incorporate the list of ticker symbols from the Watchlist table into the other tables in my spreadsheet?
答案1
得分: 3
对于两个数据集中的每一个，使用数据来自表/范围，没有标题，文件关闭并加载到连接中。
在Power Query中创建第三个查询，将Table1中的特定列（这里是 b）添加到Table2中。将以下示例代码粘贴到Power Query的主页...高级编辑器...
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
custom2 = Table.FromColumns(Table.ToColumns(Source) & {Table1[b]}, Table.ColumnNames(Source) & {"New"})
in custom2
文件关闭并加载为表格。然后，您需要每次刷新以确保新表格保持更新。
英文:
For each of the two data sets, use data.. from table/range... no headers .. file close and load to connections ..
Create 3rd query in powerquery that adds specific column (here b) from Table1 into Table2. Sample code to paste into powerquery's home ... advanced editor...
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
custom2 = Table.FromColumns(Table.ToColumns(Source) & {Table1[b]},Table.ColumnNames(Source)&{"New"})
in custom2
file close and load as a table. You would then have to refresh every time to make sure that the new table stays updated
答案2
得分: 0
我进行了horseyride的答案的变化。
我所做的是：
- 步骤＃1：点击Watchlist表
- 步骤＃2：点击数据选项卡 --> 从表/范围中
[此时表格将在Power Query中打开]
- 步骤＃3：移除我不想要的列
- 步骤＃4：将查询重命名为我想要的名称（例如，Prices）
- 步骤＃5：点击关闭并加载
- 步骤＃6：点击新表
- 步骤＃7：点击Ribbon Bar中的查询选项卡
- 步骤＃8：点击属性
- 步骤＃9：点击刷新频率【并设置刷新时间】
- 步骤＃10：点击确定
- 步骤＃11：向新表添加其他列和数据
英文:
I did a variation of horseyride's answer.
What I did is the following:
- Step #1: Click on the Watchlist table
- Step #2: Click on the Data Tab --> From Table/Range
[at this point the table will open up in Power Query]
- Step #3: Remove the columns I do not want
- Step #4: Rename the query to the name that I want (e.g., Prices)
- Step #5: Click on Close & Load
- Step #6: Click on the New Table
- Step #7: Click on the Query Tab in the Ribbon Bar
- Step #8: Click on Properties
- Step #9: Click on Refresh Every [and set my refresh time]
- Step #10: Click Okay
- Step #11: Add other columns and data to the new table
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论