英文:
Reference Sharepoint List with Excel Lookup
问题
我想使用类似Excel查找函数的东西来引用SharePoint列表中特定记录的特定信息。有点像新的IMAGE函数从网站地址引用一样。是否可以从Excel内部引用特定记录的信息？
我之前已经使用查询链接了整个SharePoint列表，但出于数据安全原因，我不希望在Excel文件中有这么大量的数据转储。
英文:
I'd like to use something similar to an excel lookup function to reference specific information in specific records of a sharepoint list. Sort of like the new IMAGE function references from a website address. Is there a way to reference specific record information from within excel?
I have previously linked the entirety of the sharepoint list with a query, but I'd prefer not to have this large of a data dump in the excel file for data security reasons.
答案1
得分: 1
- 在Excel的功能区下的“数据”选项中，选择“获取数据” > 从其他来源 > 从SharePoint列表
- 复制并粘贴SharePoint网址到“站点网址”输入框（注意：这必须是站点的根网址，不包括子文件夹）
这将作为可刷新的数据表格导入，您可以在电子表格的其他位置引用它。
编辑：您可以使用Power Query窗口添加筛选器来修剪数据集，使其不包含全部数据。在功能区中尝试不同选项，将创建查询中的新步骤以编辑输出。可以是重新排序，也可以是对数据的结构性更改。
英文:
- Under the "Data" in the ribbon in Excel, select "Get Data" > From Other Sources > From SharePoint List
- Copy & Paste the SharePoint URL to the input box "Site URL" (Note: This must be the root URL for the site, not including subfolders)
This should pull the data in as a refreshable data table that you can reference elsewhere in the spreadsheet.
edit: you can use the power query window to add filters to trim the dataset so it isn't the entirety of it. Play around in the ribbon and you'll see several options which will create a new step in the query to edit the output. It can be as small as resorting or as big as structural changes to the data formulaically.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论