excel数据处理技巧
啊南 1920阅读 2020.06.18
【导语】： 相信很多人在工作中会涉及不少数据处理、数据分析方面的内容，在这里为大家介绍一些在工作中经常会用到的操作技巧，希望能够帮助到有需要的大家。
统一获取使用技巧，如何使用excel统一获取清理数据
Excel 2016 引入了一组功能强大的工具，称为 "获取 & 转换"。 这些工具基于 Power Query 技术，使你能够轻松地连接、组合和整形来自各种源的数据。 下面的 "获取 & 转换" 教程将指导你完成基础知识。
我们很高兴地宣布在 Excel 中对数据导入和整形功能的下一次重大更新-统一的 Get & 转换体验。 使用3月、2017 Microsoft 365 更新，我们增强了 Excel 的数据导入功能并重新排列了 "数据" 选项卡上的按钮，以便充分利用 "获取 & 转换" 功能的方式比以往更轻松。
通过统一的 Get & 转换体验，你可以从 Excel 中的数据导入和整理功能的紧密集成的数据导入和整形功能中获益。 你可以轻松地连接和获取来自各种常用数据源（如文件、数据库、Azure 和 Online 服务等）的数据。 并且，你可以通过排序、更改数据类型、拆分列、聚合数据等功能，在功能强大的方式中调整数据的形状。不断增长的新连接器以及易于执行的转换是 & 在日常工作中转换宝贵且节省时间的工具的功能。
请注意，新体验取代了旧的旧版数据导入向导，这些向导在数据> "获取外部数据" 下可用。 但是，如果需要，仍然可以访问它们。
注意: 只有当你有Microsoft 365 订阅时，才可在 Excel 中使用此功能。 如果你是Microsoft 365 订阅者，请确保你拥有最新版本的 Office。
如何获取来自常用源（如 CSV 文件或 Web）的数据？
我们知道导入数据（如最受欢迎的连接线）在功能区上易于访问的用户。 在此更新中，我们尝试关注此原则并保持体验与你的使用方式一致。 现在，你可以在 "数据" 选项卡的 "获取 & 转换数据" 部分下找到以下前三个连接器：从文本/CSV、 Web和表/区域。
从文本/CSV
通过此连接线，你可以轻松地从文本（* .txt）、逗号分隔值（* .csv）或带格式的文本（* .prn）文件导入数据。 此新式连接线基于 Power Query 技术，并替换 "从文本中继承旧版" 向导。
若要开始导入带有新的连接器的数据，请单击 "从文本/CSV 数据 >"，选择源文件，然后单击"确定"。 然后，通过文本/CSV连接器的新功能和增强功能分析输入文件的结构，并自动应用相关的导入设置。 它可以按分隔符拆分列，提升要用作列标题的第一行，以及更多。
如果预览方式不符合您的需要，您可以在此处配置基本导入设置，如文件源、分隔符或数据类型检测逻辑。 根据您的选择，数据的预览将自动更新。
然后，您可以单击 "加载" 以将数据直接加载到工作表中，或单击 "加载" 旁边的箭头以将数据加载到数据模型中。 如果要在查询编辑器中应用其他数据转换，请单击 "编辑"。
从 Web
通过此连接线，您可以从 HTML 页面中 "scrape" 数据。 这是另一个新型的连接器，也是基于 Power Query 技术的另一个连接器，并替换了旧版 Web 向导。
若要开始使用它，请单击 "从 Web>数据"，指定目标页面 URL，然后单击"确定"。 "导航器" 对话框中显示了页面上所有表的列表。 你可以选择在表视图或web 视图中与网页交互。 使用 "表格" 视图，首先单击左侧的表名称，该表中的数据将显示在右侧。 使用 Web 视图，您可以从 "导航器" 列表或从 Web 视图预览窗格中单击表格。 可以在两个视图中选择多个表。
从表/区域
使用此连接线，可以创建链接到 Excel 工作表中的表或命名区域的新查询。 然后，你可以精炼你的数据，并在 "查询编辑器" 窗口中对其应用其他转换。
如何获取来自 XML 文件或 Microsoft SQL Server 等其他源的数据？
可以创建一个新查询以从单个数据源（如 Microsoft Access 数据库或 OData 源）导入数据，也可以同时从文件夹中的多个文本文件导入数据。
单击 "数据>获取数据" 时，将在下拉菜单中显示可用数据源。 源选项以类别的形式进行组织，包括 Excel 工作簿、XML 文件或整个文件夹、数据库（如 SQL Server 或 Oracle）、Azure 服务（如 HDInsight 或 Blob 存储）、SharePoint Online 列表或 Salesforce 等联机服务以及其他各种来源（如 OData 或 ODBC）。
可用连接线的列表不断增大。
通过此更新，我们还删除了以前在 "数据" 选项卡的 "获取外部数据" 部分下可用的以下旧向导：从 Access （来自 Web），从 XML 数据源（来自 SQL Server），从 XML 数据导入和数据连接向导。
但是，有两个常用且非常独特的向导，它们属于旧的 "获取外部数据" 功能集，并且将保留新体验：从 Analysis Services和Microsoft Query。 它们可以在 "数据>" 下的 "数据库和数据> 中从其他源类别中的其他源类别中找到。
如何利用 "获取 & 转换" 功能来清理和整形我的数据？
获取 & 转换使你可以将数据清理和整形为一个可靠的数据分析的基本步骤。?可以删除列、筛选行、更改列数据类型、替换值等。 通过帮助和显示所有数据转换的专用查询编辑器窗口完成此操作。
切换到功能区上的 "数据" 选项卡，单击 "获取数据"，使用其中一个可用的连接器连接到所需的数据源（如文件或数据库），并在 "导航器" 窗口中预览数据。
接下来，选择想要导入的表，然后单击 "编辑" 以启动 "查询编辑器" 窗口。
使用 "查询编辑器" 窗口内功能区中提供的大量命令集。 你可以通过排序和筛选数据、转换数据类型、拆分或合并列、聚合数据等来应用强大的转换。
将记录你对数据应用的所有转换步骤，并将其保存为查询的一部分。 录制的转换可以通过 "查询编辑器" 窗口中的 "已应用步骤" 部分进行观察和管理，并将在每次刷新查询时重新应用到数据。
如何获取来自最近使用过的源、Office 数据库连接（ODC）文件、其他工作簿连接或表格的数据？
数据导入区域中最常用的方案之一是连接和获取您最近的源中的数据。 用户通常会为导入数据建立一组用于连接的常用源。
若要从您最近的一个数据源连接并创建查询，请单击 "数据">"最近的源"。 然后，你可以浏览最近连接的源，选择一个源，然后单击 "连接" 以继续导入流。
另一种常见方案是从现有连接或工作簿中的表导入数据。 例如，您可能希望基于以前创建的工作簿连接创建新的数据透视表。 或者，你可能想要使用由组织中的人员定义的 Office 数据库连接（ODC）文件链接和导入数据源。
通过此更新，你可以将任何查询的定义导出到 ODC 文件，然后通过工作簿或同事共享它。 若要将查询定义导出到 ODC 文件中，请在 "查询 & 连接" 侧窗格中右键单击查询，然后选择 "导出连接文件"。 创建的 ODC 文件将包含具有所有依赖关系的查询定义，包括对数据源的连接指令以及对数据应用的转换步骤。 导出查询定义的另一种方法是通过 "查询属性" 对话框，可通过从查询的上下文菜单中选择 "属性" 来打开该对话框。
一旦你收到 ODC 文件，你将能够使用它，并将查询定义导入到你的工作簿中。 若要通过 ODC 机制导入数据，请单击 "数据>现有连接"。 将显示 "现有连接" 对话框，然后您可以从可用的 ODC 文件、现有工作簿连接或表格导入。
如果你需要创建将由其他应用程序（如 Visio 或 Excel 2013 及更早版本）使用的 ODC 文件，你可以使用以下连接器执行此操作：从 SQL Server 数据库、来自 microsoft Access 的数据库、来自 microsoft From Analysis ServicesQuery 的Microsoft Access 数据库、来自Microsoft Query的 microsoft Access 数据库或数据连接向导中的旧版。 请务必注意，当使用来自其他应用程序的 ODC 文件时，该应用程序将仅重复使用 ODC 的连接指令，并且不会包含原始查询可能包含的任何转换步骤。
如何管理现有工作簿查询和连接？
当我们引入了到 Excel 2016 的 & 转换时，我们引入了两种数据导入方法：第一种是新的Get & 转换体验，它可以使用查询（并且还依赖于场景背后的工作簿连接来进行刷新和加载到工作簿元素）。 第二个是使用工作簿连接的旧旧体验。 管理工作簿查询和连接也是单独执行的。 通过 "专用查询" 窗格管理工作簿查询，而通过 "工作簿连接" 对话框管理连接。 这会导致用户出现混淆。
通过此更新，我们解决了此问题。 现在，您可以从 "专用查询" & "连接" 侧窗格中访问和管理工作簿查询和连接。 若要打开侧窗格，请单击 "数据" > "查询 & 连接" 按钮。
单击窗格顶部的 "查询" 选项卡可让你浏览和管理工作簿中的可用查询。 右键单击查询以访问查询的上下文菜单，该菜单允许你执行其他操作。 例如，您可以刷新基础源中的查询数据、复制/粘贴查询、用查询编辑器编辑查询转换等。
我们还通过新的 "查询属性" 对话框简化了查询及其基础连接的管理。 今天，当您在工作簿中创建新查询时，会在后台自动创建新的基础连接实体，并与此查询相关联。 虽然查询的角色是连接到数据源，并在其顶部应用所需的数据转换，但基础连接控制数据的加载位置（例如，工作表或数据模型）以及查询数据的刷新设置（如刷新频率）。 使用新的 "查询属性" 对话框，你可以轻松地控制所有查询属性，例如其名称或转换说明，以及基础连接属性（如刷新设置）。 若要打开 "新建查询属性" 对话框，请在 "查询 & 连接" 侧窗格上单击查询上下文菜单中的 "属性" 命令。
单击侧面窗格顶部的 "连接" 选项卡，可浏览和管理工作簿中的可用旧版连接，这些连接与某个旧向导（如从文本、 Analysis Services或Microsoft Query）相关联。 右键单击连接以访问上下文菜单。 你将能够执行其他操作，例如从来源刷新连接数据、删除连接或通过熟悉的连接属性对话框管理连接属性。
如何还原旧的 "获取外部数据" 体验？
新的 Get & 转换体验在与旧向导相比的连接器产品和转换功能方面更出色。 但是，仍有可能需要使用旧的某个向导导入数据。 例如，当你希望将数据源登录凭据保存为工作簿的一部分时。 （请注意，此方法不符合新式安全标准，因此在新的 "获取 & 转换" 体验中不支持此操作。
你可以通过自定义功能区来访问旧版连接线，也可以通过 "Excel 选项" 对话框中的新增和简单的配置设置，返回并直接在 "数据 >" 获取数据"下拉菜单中显示你的旧版数据导入向导。
转到 "文件" > ">数据"选项>显示旧版数据导入向导，然后选择要使用的旧向导：
打开 "数据 >" 获取数据"下拉菜单，然后从" 专用旧向导 "类别启动以前选择的旧数据导入向导之一：
如何使用excel收集数据并将数据可视化
商业智能 (BI) 实际上是可用于收集数据，将其转换为有意义的信息，然后作出更好的决策的一组工具和过程。 在 Office 365 企业版 中，你可以在 Excel 和 SharePoint Online 中使用 BI 功能。 这些服务使您能够跨多种设备收集数据、将数据可视化、与您组织中的人员共享信息。
使用 Excel 收集数据并将数据可视化
简单几步，就可以在 Excel 中创建图表和表格。
第 1 步：获取数据
在 Excel 中，有大量选项可用来获取和组织数据：
可以从 Excel 中导入外部数据源（Power Query）中的数据，并使用该数据创建图表、表格和报表。
您可以使用 Power Query 发现和合并不同数据源中的数据，并根据需要调整数据。
您可以在 Excel 中创建数据模型，其中包含来自多种数据源的一个或多个数据表。 如果有两个或多个表格来自不同数据库，则可通过使用 Power Pivot 创建表格之间的关系。
在数据表中，可以使用快速填充将列的格式设置为以特定方式显示。
并且，如果您是高级用户，则可在 中设置计算项。Excel
第 2 步：将数据可视化
Excel 中有了数据之后，您可以轻松创建报表：
您可以使用快速分析选择数据并即时查看用于可视化这些数据的不同方式。
可以在 Office 中创建包含表格、折线图、条形图、雷达图等的可用图表类型。
您可以使用快速浏览创建数据透视表和向下钻取数据。 您也可以对报表使用字段列表来确定要显示的信息。
您可以创建记分卡，这些记分卡使用条件格式和 Power Pivot 中的关键绩效指标 (KPI) 一目了然地显示一项或多项指标的绩效是否达到目标。
可以使用Power Map在三维（3d）地球仪上分析和映射数据。
第 3 步：添加筛选器
您可以向工作表添加筛选器，如 切片器和日程表控件，以便更轻松地关注更具体的信息。
第 4 步：添加高级分析功能
当您准备就绪后，您可以向工作簿中添加更多的高级功能。 例如，您可以在 Excel 中创建计算项。 这其中包括：
用于数据透视图或数据透视表的计算度量值和成员
数据模型的计算字段
使用 SharePoint Online 共享和查看工作簿
如果您的组织使用工作组网站，则您正在使用 SharePoint Online，它提供大量选项可供您共享工作簿。 您可以指定那些可确定如何显示工作簿的浏览器视图选项。
您可以像如下所示这样在库视图中显示工作簿，在该视图中，一次有一个项目显示在屏幕上的中心位置：
您可以像如下所示这样在工作表视图中显示工作簿，在该视图中，整个工作表都显示在浏览器中：
当工作簿已上载到 SharePoint Online 中的库后，您和其他人可以轻松地在浏览器窗口中查看工作簿并与之交互。
在云中使用 Power BI 获取更多 BI 功能
Power BI 为你提供了比 Excel 和 SharePoint Online 中所获得的更多的 BI 功能。 Power BI 在云中提供了一个强大的自助服务 BI 解决方案。
注意: 由世纪互联运营的 Microsoft 365 不支持商业智能功能。
如何批量删除excel数据单位，去掉数据后面的单位要怎么做
在工作中常需要使用WPS表格进行统计，有时需要清除表格中整列数据的单位。
一个一个删除很费时间，那如何快速删除整列数据的单位呢。
具体方法与步骤如下：
以此表格为例，选中需要删除单位的区域，使用替换快捷键Ctrl+H。
弹出替换窗口，在查找内容中输入“元”，就可以快速清除表格中整列数据的单位了。
如何把excel里面的数据添加到数据模型，链接表使用技巧
链接表是一个 Excel 表，其中包含指向数据模型中的表的链接。 在 Excel 表格中创建和维护数据的优点是，您可以继续编辑 Excel 工作表中的值，同时使用数据模型中的链接表作为数据透视表的基础。、数据透视图或 Power View 报表。
添加链接表非常简单，只需选择一个区域，然后单击 "添加到数据模型" 即可。 也可以将区域设置为表格格式，然后为其指定一个正确的名称。 通过使用命名链接表，更轻松地执行计算和管理关系。
请按照以下步骤将数据链接到表：
选择要在链接表中使用的行和列的范围。
将行和列的格式设置为表格：
单击 "开始>表格格式"，然后选择表格样式。 你可以选择任何样式，但请确保始终选择"表包含标题"。 如果表没有标题，请考虑立即创建。 否则，Excel 将使用任意名称（column1、column2 等）来传达有关列内容的无意义的信息。
为该表命名。 在Excel窗口中，单击 "表格工具设计"。 在 "属性" 组中，键入表的名称。
将光标放在表中的任意单元格上。
单击 " Power Pivot >添加到数据模型" 以创建链接表。 在 " Power Pivot " 窗口中，你将看到一个表，其中包含一个链接图标，指示表链接到 Excel 中的源表：
如果模型已包含表，则仅有一个步骤。 应在刚添加的新表和模型中的其他表之间创建关系。 有关说明，请参阅在关系图视图中创建两个表之间的关系或创建关系。
如果工作簿以前不包含一个数据模型，则它现在具有数据模型。 当您在两个表之间创建关系或单击 "添加到 Power Pivot 中的数据模型" 时，将自动创建模型。
既然您已经有了链接表，您就可以编辑工作表中的任意单元格，包括添加和删除行和列。 Power Pivot 中的数据模型将立即同步。
表名称是一个异常。 如果在 Excel 中重命名表，则需要在 Power Pivot 中手动更新表。
提示: 若要浏览教程以了解有关链接表的详细信息，请参阅教程：将数据导入 Excel，并创建数据模型。
同步表和模型之间的更改
默认情况下，链接表是在包含数据值的区域或命名表之间保持的活动连接，以及用于驱动报表的数据模型。 如果添加或删除数据，或者重命名列和表，数据模型将自动更新。
在某些情况下，你可能需要控制更新行为。 使用Power Pivot 加载项切换到手动更新模式。
请按照以下步骤切换到手动更新模式：
确保在 Excel 中打开包含链接表的工作簿。
打开 Power Pivot 窗口。
在底部的选项卡中，单击链接表。 任何链接表都由表名旁边的小链接图标表示。
在顶部的功能区中，单击 "链接表"。
在 "更新模式" 中，选择 "手动" 或 "自动"。 默认值为 "自动"。 如果切换到手动，则仅当你在 " Power Pivot " 窗口的 "链接表" 功能区中使用 "更新全部" 或 "更新所选" 命令时，或者 Excel 中 Power Pivot 功能区中的 "全部更新" 命令时，更新才会发生。
excel怎样快速汇总表格，如何汇总一年的表格数据
如何将一年的数据汇总到一个表格中？
具体方法与步骤如下：
首先选中汇总表格的求和区域，输入SUM函数。
点击1月的表格，按住Shift键选中1-12月的表格。
最后点击左上角的数据，按Ctrl+回车键，搞定！
excel怎么快速对两列数据进行查重，数据重复怎么办
可以使用以下方法比较两个 Microsoft Excel 工作表列中的数据，并查找重复条目。
方法1：使用工作表公式
1.启动 Excel。
2.在新工作表中，输入以下数据作为示例（将列 B 留空）：
|
|
A |
B |
C |
|---|---|---|---|
|
1 |
1 |
|
3 |
|
2 |
2 |
|
5 |
|
3 |
3 |
|
8 |
|
4 |
4 |
|
2 |
|
5 |
5 |
|
0 |
：=IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1)
4.选择单元格 B1 到 B5。
5.在 Excel 2007 和更高版本的 Excel 中，选择 "编辑" 组中的 "填充"，然后选择 "向下"。
重复数字显示在列 B 中，如以下示例中所示：
|
|
A |
B |
C |
|---|---|---|---|
|
1 |
1 |
|
3 |
|
2 |
2 |
2 |
5 |
|
3 |
3 |
3 |
8 |
|
4 |
4 |
|
2 |
|
5 |
5 |
5 |
0 |
方法2：使用 Visual Basic 宏
警告: Microsoft 提供的编程示例仅用于说明，没有任何明示或默示的保证。 这包括但不限于对适销性或特定用途适用性的默示担保。 本文假定你熟悉所演示的编程语言，以及用于创建和调试过程的工具。 Microsoft 支持工程师可帮助解释特定过程的功能。 但是，它们不会修改这些示例以提供额外的功能或构建过程来满足你的特定要求。
若要使用 Visual Basic 宏比较两列中的数据，请使用以下示例中的步骤：
1.启动 Excel。
2.按 ALT + F11 启动 Visual Basic 编辑器。
3.在 "插入" 菜单上，选择 "模块"。
4.在模块工作表中输入以下代码：
Sub Find_Matches()
Dim CompareRange As Variant, x As Variant, y As Variant
' Set CompareRange equal to the range to which you will
' compare the selection.
Set CompareRange = Range("C1:C5")
' NOTE: If the compare range is located on another workbook
' or worksheet, use the following syntax.
' Set CompareRange = Workbooks("Book2"). _
' Worksheets("Sheet2").Range("C1:C5")
'
' Loop through each cell in the selection and compare it to
' each cell in CompareRange.
For Each x In Selection
For Each y In CompareRange
If x = y Then x.Offset(0, 1) = x
Next y
Next x
End Sub
5.按 ALT + F11 返回到 Excel。
a.输入以下数据作为示例（将列 B 留空）：
|
|
A |
B |
C |
|---|---|---|---|
|
1 |
1 |
|
3 |
|
2 |
2 |
|
5 |
|
3 |
3 |
|
8 |
|
4 |
4 |
|
2 |
|
5 |
5 |
|
0 |
7.在 Excel 2007 和更高版本的 Excel 中，选择 "开发工具" 选项卡，然后选择 "代码" 组中的 "宏"。
注意: 如果看不到 "开发工具" 选项卡，您可能需要将其打开。 若要执行此操作，请选择 "文件>选项" > "自定义功能区"，然后在右侧的 "自定义" 框中选择 "开发工具" 选项卡。
8.单击 " Find_Matches"，然后单击 "运行"。
重复数字显示在 B 列中。匹配的数字将放在第一列的旁边，如下所示：
|
|
A |
B |
C |
|---|---|---|---|
|
1 |
1 |
|
3 |
|
2 |
2 |
2 |
5 |
|
3 |
3 |
3 |
8 |
|
4 |
4 |
|
2 |
|
5 |
5 |
5 |
0 |
excel数据整理技巧有哪些，数据格式怎么设置
Microsoft Office Excel 提供各种有助于轻松管理和分析数据的功能。 若要充分利用这些功能，请务必根据以下指南在工作表中整理数据和设置数据格式。
数据整理指南
在同一列中放置类似项目 设计数据，以便所有行在同一列中具有相似的项。
将数据区域分开 在相关数据区域和工作表上的其他数据之间保留至少一个空白列和一个空白行。 这样，在排序、筛选或插入自动分类汇总时，Excel 可以更轻松地检测和选择区域。
将关键数据放在区域上方或下方 避免将关键数据放在区域的左侧或右侧，因为筛选区域时可能隐藏数据。
避免区域中出现空白行和列 避免在数据区域中放置空白行和列。 执行此操作，以确保 Excel 能够更轻松地检测和选择相关数据区域。
显示区域中的所有行和列 在对数据区域进行更改之前，请确保显示所有隐藏的行或列。 当区域中的行和列未显示时，数据可能会被意外删除。
数据格式设置指南
使用列标签标识数据 通过对数据应用不同的格式，在数据区域的第一行中创建列标签。 然后，Excel 可以使用这些标签创建报表和查找和组织数据。 对列标签使用字体、对齐方式、格式、图案、边框或大小写样式，这些标签不同于为区域中的数据指定的格式。 在键入列标签之前将单元格的格式设置为文本。
使用单元格边框区分数据 要将标签与数据分开时，请使用单元格边框（而非空白行或虚线）在标签下方插入行。
避免出现前导空格或尾随空格以避免错误 避免在单元格的开头或结尾插入空格以缩进数据。 这些额外的空格可能会影响排序、搜索以及应用于单元格的格式。 可在单元格中使用 "增加缩进量" 命令，而不是键入用于缩进数据的空格。
扩展数据格式和公式 将新的数据行添加到数据区域的末尾时，Excel 将扩展一致的格式和公式。 前面五个单元格中的三个必须使用相同格式才能进行扩展。 对于要扩展的公式，上述所有公式都必须保持一致。
使用 Excel 表格格式处理相关数据 您可以将工作表上的连续单元格区域转换为 Excel 表格。 表定义的数据可以独立于表外部的数据进行操作，并且你可以使用特定表功能来快速对表中的数据进行排序、筛选、汇总或计算。 你还可以通过在单个工作表上的多个表中组织这些数据来使用表功能来 compartmentalize 相关数据集。
excel数据怎么对齐，excel表格内容对齐怎么设置
在使用WPS表格制作表单时，有时需要插入图片，使用表格的“对齐”功能，可以设置所选对象的对齐方式，一键解决表格中的排版需求。
具体方法与步骤如下：
以此价格表单为例，我们可见表格中的图片大小不一、参差不齐。
按住Shift键，依次选中图片，点击页面布局-对齐，选择需要的对齐方式，例如水平居中。
我们也可在图片工具-对齐中，选择对齐方式，快速对齐所选对象。
这个快速对齐的小技巧，你学会了吗？
excel数据模型使用有什么限制
数据模型是一个 Excel 工作簿中的嵌入数据，用于为数据透视表（如数据透视表）和数据透视图表。 本文介绍其中包含数据模型的 Excel 工作簿的最大限制和可配置限制。
-----------------------------------------------------------------------------------------------------------------------
文件大小限制
包含数据模型和数据可视化的工作簿通常非常大，有时超出 SharePoint Online 或 Microsoft 365 中 Office 网页版 的文件大小限制。
下表说明了 Excel 中的工作簿和不同平台上的内存存储和文件大小的最大限制。 有关如何减少模型大小的提示，请参阅使用 Excel 和 Power Pivot 加载项创建内存有效的数据模型。
您还可以考虑运行工作簿大小优化程序。 它可分析您的 Excel 工作簿，并在可能时进一步压缩工作簿。
|
产品或平台 |
最大限制 |
|---|---|
|
Excel 2013 |
32 位环境限制为同一进程内运行的 Excel、工作簿和加载项最多共用 2 千兆字节 (GB) 虚拟地址空间。 数据模型的地址空间共享可能最多运行 500 - 700 MB，如果加载其他数据模型和加载项则可能会减少。 64 位环境对文件大小不作硬性限制。 工作簿大小仅受可用内存和系统资源的限制。 |
|
SharePoint Server 2013 （1） |
可上载到文档库的最大文件大小：
在 Excel Services 中呈现工作簿的最大文件大小：
|
|
Office 365 3 中的Excel 网页版 |
250 兆字节 (MB) 的总文件大小限制。 根据 SharePoint Online 中的工作簿文件大小限制决定的核心工作表内容（不在数据模型中的所有内容）的大小限制。 |
|
Power BI Pro 或免费服务 |
0-250 MB |
（2）必须由 SharePoint 管理员为每个 web 应用程序配置最大上载大小。 “最大工作簿大小”必须由服务管理员在 Excel Services 中配置。 有关管理员的详细信息，请参阅 TechNet 上的配置最大文件上载大小。
（3） Microsoft 365 中的限制是不可配置的，但可能会随着时间的推移而更改。 查看有关最新信息的企业服务说明的 Microsoft 365 。 您还可以查看SharePoint Online 限制。
-----------------------------------------------------------------------------------------------------------------------
其他限制
下表指定数据模型中定义的各种对象的最大大小和数量。
|
名称 |
最大限制 |
|---|---|
|
表格或列名中的字符数 |
100 个字符 |
|
模型中的表格数 |
2147483647 |
|
表格中的列和计算列数 |
2147483647 |
|
每个工作簿的并发请求数 |
6 |
|
连接数 |
5 |
|
列中的非重复值数 |
1,999,999,997 |
|
表格中的行数 |
1,999,999,997 |
|
字符串长度 |
536,870,912 字节 (512 MB)，等于 268,435,456 个 Unicode 字符（2.56 亿字符） |
函数异常
警告: 字符串限制的异常适用于以下函数，其中字符串限制为 2097152 Unicode 字符：
CONCATENATE 和中缀连接运算符
DATEVALUE
EXACT
FIND
FORMAT
LEFT
LEN
LOWER
MID
REPLACE
REPT（限制应用于输入参数和结果）
RIGHT
SEARCH
SUBSTITUTE（限制应用于输入参数和结果）
TIMEVALUE
TRIM
UPPER
excel数据快速处理的十大方法
拼写错误的单词、难以去除的尾随空格、不需要的前缀、不正确的大小写和非打印字符给人一种不好的第一印象。 导致数据混乱的因素还不止这些。 请准备好。 通过 Microsoft Excel 对工作表进行大扫除的时候到了。
-----------------------------------------------------------------------------------------------------------------------
清理数据的基础知识
你并不一定始终可控制从数据库、文本文件或网页等外部数据源导入的数据格式和类型。 通常需要先清理数据，才能分析数据。 幸运的是，Excel 提供许多功能，可帮助用户获取所需精确格式的数据。 有时任务非常简单，Excel 具有执行此任务的特定功能。 例如，可轻松使用拼写检查清理包含批注或说明的列中拼写错误的单词。 或者如果想要删除重复行，可使用“删除重复项”对话框快速执行此操作。
在其他情况下，可能需要使用公式将导入的值转换为新值来操作一列或多列。 例如，如果想要删除尾随空格，可创建新列来清理数据，方法是：使用公式，向下填充新列，将新列的公式转换为值，然后删除原始列。
清理数据的基本步骤如下所示：
从外部数据源导入数据。
在单独的工作簿中创建原始数据的备份副本。
确保数据采用行和列的表格格式：每一列中的数据类似、所有列和行可见并且该区域中无空白行。 为获得最佳效果，请使用 Excel 表格。
先执行不需要列操作的任务，例如拼写检查或使用“查找和替换”对话框。
接下来，执行需要列操作的任务。 列操作的常规步骤有：
在需要清理的原始列 (A) 旁插入新列 (B)。
在新列 (B) 的顶部添加将转换数据的公式。
在新列 (B) 中向下填充公式。 在 Excel 表中，会使用向下填充的值自动创建计算列。
选择新列 (B)，将其复制，然后作为值粘贴到新列 (B) 中。
删除原始列 (A)，这会将新列从 B 转换为 A。
若要定期清理相同的数据源，请考虑录制宏或编写代码，自动执行整个过程。 第三方提供商部分中列有许多第三方提供商编写的外部加载项，如果没有时间或资源独立自动执行此过程，可考虑使用这些外部加载项。
|
详细信息 |
说明 |
|---|---|
|
在工作表单元格中自动填充数据 |
介绍如何使用“填充”命令。 |
|
创建表以及设置表格式 |
介绍如何创建 Excel 表格以及添加或删除列或计算列。 |
|
创建宏 |
介绍使用宏自动执行重复任务的若干方法。 |
拼写检查
使用拼写检查不仅可查找拼写错误的单词，还可查找使用不一致的值（如产品或公司名称），只需将这些值添加到自定义词典即可。
|
详细信息 |
说明 |
|---|---|
|
检查拼写和语法 |
介绍如何更正工作表中拼写错误的单词。 |
|
使用自定义词典向拼写检查中添加单词 |
介绍如何使用自定义词典。 |
删除重复行
导入数据时，重复行是一个常见问题。 最好先筛选唯一值，确认结果是所需结果，然后再删除重复值。
|
详细信息 |
说明 |
|---|---|
|
筛选唯一值或删除重复值 |
介绍两个密切相关的过程：如何筛选唯一行以及如何删除重复行。 |
查找和替换文本
可能需要删除常见的前导字符串（例如后跟冒号和空格的标签）或后缀（例如已过时或不必要的字符串结尾处的附加说明短语）。 若要执行此操作，可查找文本的实例，然后将其替换为无文本或其他文本。
|
详细信息 |
说明 |
|---|---|
|
检查单元格是否包含文本（不区分大小写） |
介绍如何使用“查找”命令和几个函数来查找文本。 |
|
删除文本中的字符 |
介绍如何使用“替换”命令和几个函数来删除文本。 |
|
查找或替换工作表上的文本和数字 |
介绍如何使用“查找”和“替换”对话框。 |
|
FIND、FINDB |
这些是可用来执行各种字符串操作任务的函数，如查找和替换字符串内的子字符串、提取部分字符串或确定字符串的长度。 |
更改文本大小写
有时文本格式混乱，尤其是文本大小写方面。 使用三种 Case 函数中的一种或多种，可将文本转换为小写字母（如电子邮件地址）、大写字母（如产品代码）或首字母大写（如姓名或书名）。
|
详细信息 |
说明 |
|---|---|
|
更改文本大小写 |
介绍如何使用三种 Case 函数。 |
|
LOWER |
将文本字符串中的所有大写字母转换为小写字母。 |
|
PROPER |
将文本字符串的首字母以及文字中任何非字母字符之后的任何其他字母转换成大写。 将其余字母转换为小写。 |
|
UPPER |
将文本转换为大写字母。 |
删除文本中的空格和非打印字符
有时文本值包含前导空格、尾随空格或多个嵌入空格字符（Unicode 字符集值 32 和 160），或非打印字符（Unicode 字符集值 0 到 31、127、129、141、143、144 和 157）。 执行排序、筛选或搜索操作时，这些字符有时会导致意外结果。 例如，在外部数据源中，用户可能会无意添加额外的空格字符，从而导致打字错误，或者从外部源导入的文本数据可能包含嵌入在文本中的非打印字符。 由于这些字符不容易引起注意，因此意外结果可能很难理解。 若要删除这些不需要的字符，可组合使用 TRIM、CLEAN 和 SUBSTITUTE 函数。
|
详细信息 |
说明 |
|---|---|
|
介绍如何从 Unicode 字符集中删除所有空格和非打印字符。 |
|
|
CODE |
返回文本字符串中第一个字符的数字代码。 |
|
CLEAN |
从文本中删除 7 位 ASCII 代码中的前 32 个非打印字符（值 0 到 31）。 |
|
TRIM |
从文本中删除 7 位 ASCII 空格字符（值 32）。 |
|
SUBSTITUTE |
可使用 SUBSTITUTE 函数，将值较高的 Unicode 字符（值 127、129、141、143、144、157 和 160）替换为 7 位 ASCII 字符（TRIM 和 CLEAN 函数专门用于此类字符）。 |
修复数字和数字符号
主要有两个数字问题可能需要你进行数据清理：无意中将数字导入为文本，以及需要根据你组织的标准更改负号。
|
详细信息 |
说明 |
|---|---|
|
将存储为文本的数字转换成数字 |
介绍如何将单元格中设置和存储为文本格式（这会导致计算问题或排序顺序混乱）的数字转换为数字格式。 |
|
DOLLAR |
将数字转换为文本格式并应用货币符号。 |
|
TEXT |
将值转换为采用特定数字格式的文本。 |
|
FIXED |
将数字舍入到指定的小数位数，使用句点和逗号，以十进制数格式对该数进行格式设置，并以文本形式返回结果。 |
|
VALU |
将表示数字的文本字符串转换为数字。 |
修复日期和时间
由于存在许多不同的日期格式，并且这些格式可能混杂有编号部件代码或其他包含斜杠标记或连字符的字符串，因此日期和时间通常需要进行转换和重新设置格式。
|
详细信息 |
说明 |
|---|---|
|
更改日期系统、格式或两位数年份表示方式 |
介绍 Office Excel 中日期系统的工作原理。 |
|
转换时间 |
介绍如何在不同的时间单位之间进行转换。 |
|
将以文本格式存储的日期转换为日期 |
介绍如何将单元格中设置和存储为文本格式（这会导致计算问题或排序顺序混乱）的日期转换为日期格式。 |
|
DAT |
返回表示特定日期的连续序列号。 如果在输入该函数之前单元格格式为“常规”，则结果将使用日期格式。 |
|
DATEVALUE |
将由文本表示的日期转换为序列号。 |
|
TIME |
返回特定时间的十进制数字。 如果在输入该函数之前单元格格式为“常规”，则结果将使用日期格式。 |
|
TIMEVALUE |
返回由文本字符串表示的时间的十进制数字。 十进制数字是一个范围在 0（零）到 0.99999999 之间的值，表示 0:00:00 (12:00:00 AM) 到 23:59:59 (11:59:59 P.M.) 之间的时间。 |
合并和拆分列
从外部数据源导入数据后的常见任务是将两列或多列合并为一列，或将一列拆分为两列或多列。 例如，可能需要将包含全名的列拆分为名字和姓氏。 或者可能需要将包含地址字段的列拆分为单独的街道、城市、地区和邮政编码列。 反之亦可。 可能需要将名字和姓氏列合并为一个全名列，或者将单独的地址列合并为一列。 其他可能需要合并为一列或拆分为多列的常见值包括产品代码、文件路径和 Internet 协议 (IP) 地址。
|
详细信息 |
说明 |
|---|---|
|
合并名字和姓氏 |
介绍合并两列或多列中的值的典型示例。 |
|
使用“文本分列向导”将文本拆分为不同的列 |
介绍如何使用此向导基于各种常用分隔符拆分列。 |
|
使用函数将文本拆分到不同列中 |
介绍如何使用 LEFT、MID、RIGHT、SEARCH 和 LEN 函数，将名称列拆分为两列或多列。 |
|
合并或拆分单元格的内容 |
介绍如何使用 CONCATENATE 函数、&（与号）运算符和文本分列向导。 |
|
合并单元格或拆分合并的单元格 |
介绍如何使用“合并单元格”、“跨越合并”和“合并及居中”命令。 |
|
CONCATENATE |
将两个或多个文本字符串联接成一个文本字符串。 |
转换和重新排列列和行
Office Excel 中的大多数分析和格式设置功能都假设数据存在于单个平面二维表中。 有时可能需要将行转换为列、将列转换为行。 有时候，数据甚至不是表格格式结构，需要使用一种方法将数据从非表格格式转换为表格格式。
|
详细信息 |
说明 |
|---|---|
|
TRANSPOSE |
将垂直单元格区域作为水平区域返回，反之亦然。 |
通过联接或匹配协调表格数据
有时，数据库管理员会使用 Office Excel 查找并更正两个或多个表联接时的匹配错误。 这可能涉及协调不同工作表中的两个表，例如，查看两个表中的所有记录，或比较两个表并查找不匹配的行。
|
详细信息 |
说明 |
|---|---|
|
在数据列表中查找值 |
介绍使用 lookup 函数查找数据的常用方法。 |
|
LOOKUP |
从单行或单列区域或数组返回值。 LOOKUP 函数具有两种语法形式：向量形式和数组形式。 |
|
HLOOKUP |
在表格的首行或值数组中搜索值，然后返回表格或数组中指定行的所在列中的值。 |
|
VLOOKUP |
在表格数组的第一列中搜索值，并返回表格数组中另一列所在行中的值。 |
|
INDEX |
返回表格或区域中的值或值的引用。 INDEX 函数有两种形式：数组形式和引用形式。 |
|
MATCH |
返回符合特定值特定顺序的项在数组中的相对位置。 需要项目在区域中的位置而非项目本身时，请使用 MATCH 而非 LOOKUP 函数。 |
|
OFFSET |
返回对单元格或单元格区域中指定行数和列数的区域的引用。 返回的引用可以是单个单元格或单元格区域。 可以指定要返回的行数和列数。 |
excel设置数据自动添加小数点方法，如何让数据自动添加小数点
平时在购物的过程中常会看到标价为9.99元、19.99元的商品。
在录入商品价格表的时候，一个一个输入小数点很麻烦。
下面教大家如何在录入数据时自动添加小数点的小技巧。
以下面这张水果价格单为例，点击左上角“文件”菜单栏，找到“选项”。
在弹出的对话框左侧点击“编辑”，勾选“自动设置小数点”，点击“确定”。
此时在商品单价下方空白单元格输入588(即5元8毛8)，内容就自动变成5.88了，还可以给价格加上人民币符号。
选中B列数据，在“开始”菜单栏中点击“中文货币符号”按钮；
此时所有的价格都自动加上人民币符号了。
excel如何使用数据表进行多个答案的模拟运算
数据表是一个单元格区域，可在其中更改某些单元格中的值，并提出有关问题的不同答案。 数据表的一个很好的示例使用具有不同贷款金额和利率的PMT函数，以在家庭抵押贷款上计算价格低廉的金额。 通过试验不同的值来观察结果中的相应变体是数据分析中的常见任务。
-----------------------------------------------------------------------------------------------------------------------
概述在 Microsoft Excel 中，数据表是一组称为 "模拟分析工具" 的命令的一部分。 构造和分析数据表时，你正在执行模拟分析。
模拟分析是更改单元格中的值以查看这些更改对工作表上的公式结果有何影响的过程。 例如，您可以使用数据表改变贷款的利率和术语长度，以评估潜在的每月付款金额。
注意: 你可以通过数据表和 Visual Basic for Applications （VBA）执行更快的计算。 有关详细信息，请参阅Excel 模拟运算表：用 VBA 更快地进行计算。
模拟分析的类型
Excel 中有三种类型的假设分析工具：方案、数据表和目标寻道。 方案和数据表使用输入值集来计算可能的结果。 目标-寻道的差别各不相同，它使用单个结果并计算可能产生该结果的输入值。
与方案一样，数据表可帮助您探索一组可能的结果。 与方案不同，数据表在一个工作表上显示一个表中的所有结果。 通过使用数据表，可轻松查看一系列可能的功能。 因为仅关注一个或两个变量，便可轻松阅读并以表格形式共享所得结果。
数据表无法容纳两个以上的变量。 如果你想要分析两个以上的变量，则应改为使用方案。 虽然它仅限于一个或两个变量（一个用于行输入单元格，另一个用于列输入单元格），但数据表可以包含任意数量的不同变量值。 一个方案最多可以有32个不同值，但你可以根据需要创建任意数量的方案。
-----------------------------------------------------------------------------------------------------------------------
数据表基础知识
创建单变量或双变量模拟运算表，具体取决于需要测试的变量和公式的数量。
单变量模拟运算表
如果要查看一个或多个公式中一个变量的不同值如何更改这些公式的结果，请使用单变量模拟运算表。 例如，您可以使用一个单变量的数据表来查看不同利率使用PMT 函数对每月抵押支付的影响。 在一列或一行中输入变量值，结果显示在相邻的列或行中。
在下图中，单元格 D2 包含付款公式，
= PMT （B3/12，B4，-B5），它引用输入单元格 B3。
双变量模拟运算表
使用双变量模拟运算表查看一个公式中两个变量的不同值将如何更改该公式的结果。 例如，您可以使用双变量模拟运算表查看不同的利率和贷款期限的不同组合会对每月抵押支付的影响。
在下图中，单元格 C2 包含付款公式，
= PMT （B3/12，B4，-B5），它们使用两个输入单元格、B3 和 b4。
模拟运算表
每当工作表重新计算时，任何数据表也将重新计算，即使数据没有任何更改也是如此。 若要加快包含模拟运算表的工作表的计算速度，可以更改计算选项以自动重新计算工作表，而不是模拟数据表。
----------------------------------------------------------------------------------------------------------------------
创建单变量模拟运算表
单变量模拟运算表包含单个列（列方向）或行（行方向）的输入值。 单变量模拟运算表中的任何公式都只能引用一个 可变单元格 。
请按以下步骤操作：
键入要在输入单元格中替换的值的列表-在一列或一行中，将其替换为一列。 在值的任意一侧保留一些空行和空列。
执行下列操作之一：
如果数据表是列方向的（变量值位于列中），请在上面的第一行单元格中键入公式，在值列右侧键入一个单元格。 此单变量模拟运算表为列方向，公式包含在单元格 D2 中。
如果要检查其他公式的不同值的效果，请在第一个公式右侧的单元格中输入其他公式。
如果数据表是行方向的（变量值位于行中），请在第一个值左侧的单元格中键入公式，在值行下方键入一个单元格。
如果要检查其他公式的不同值的效果，请在第一个公式下方的单元格中输入其他公式。
选择包含要替换的公式和值的单元格区域。 在上图中，此范围为 C2： D5。
在 "数据" 选项卡上，单击 "模拟分析 > 数据表（在"数据工具"组或 Excel 2016 的预测组中）。
执行下列操作之一：
如果数据表是列方向的，请在 "列输入单元格" 字段中输入输入单元格的 单元格引用 。 在上图中，输入单元格为 B3。
如果数据表是行方向，请在 "输入引用行的单元格" 字段中输入输入单元格的单元格引用。
注意: 创建数据表后，您可能需要更改结果单元格的格式。 在图中，结果单元格的格式设置为货币。
-----------------------------------------------------------------------------------------------------------------------
向单变量模拟运算表添加公式
在单变量模拟运算表中使用的公式必须引用相同的输入单元格。
请按以下步骤操作
执行以下任一操作：
如果模拟运算表为列方向，请在数据表首行的现有公式右侧的空白单元格中输入新公式。
如果数据表是行方向的，请在数据表第一列中的现有公式下方的空单元格中输入新公式。
选择包含数据表和新公式的单元格区域。
在 "数据" 选项卡上，单击 "模拟分析>数据表（在"数据工具"组或 Excel 2016 的预测组中）。
执行下列任一操作：
如果数据表是列方向的，请在 "输入引用列的单元格" 框中输入输入单元格的单元格引用。
如果数据表是行方向的，请在 "输入引用行的单元格" 框中输入输入单元格的单元格引用。
-----------------------------------------------------------------------------------------------------------------------
创建双变量模拟运算表
双变量模拟运算表使用包含两个输入值列表的公式。 公式必须引用两个不同的输入单元格。
请按以下步骤操作：
在工作表上的单元格中，输入引用两个输入单元格的公式。
在以下示例中，在单元格 B3、B4 和 B5 中输入公式起始值，在单元格 C2 中键入公式= PMT （B3/12，B4，-B5） 。
在公式下方的同一列中键入一个输入值列表。
在这种情况下，请在单元格 C3、C4 和 C5 中键入不同的利率。
在公式的右侧输入同一行中的第二个列表。
在单元格 D2 和 E2 中键入贷款期限（以月为单位）。
选择包含公式（C2）的单元格区域，包括值的行和列（C3： C5 和 D2： E2），以及需要计算值（D3： E5）的单元格。
在这种情况下，选择区域 C2： E5。
在 "数据" 选项卡上的 "数据工具" 组或 "预测组" （在 Excel 2016 中）中，单击 "模拟分析" > 数据表（在 "数据工具" 组或 Excel 2016 的预测组中）。
在 "输入引用行的单元格" 字段中，输入对行中输入值的输入单元格的引用。在 "输入行单元格" 框中键入单元格 B4 。
在 "输入引用列的单元格" 字段中，输入对列中输入值的输入单元格的引用。在 "输入到列的单元格" 框中键入 " B3 "。
单击“确定”。
双变量模拟运算表的示例
双变量模拟运算表可显示不同的利率和贷款期限的不同组合对每月抵押支付的影响。 在此处的图中，单元格 C2 包含付款公式， = PMT （B3/12，B4，-B5），它使用两个输入单元格、B3 和 b4。
-----------------------------------------------------------------------------------------------------------------------
加快包含模拟运算表的工作表的计算速度
设置此计算选项时，在对整个工作簿执行重新计算时，不会执行任何数据表计算。 若要手动重新计算数据表，请选择其公式，然后按 F9。
请按照以下步骤提高计算性能：
执行下列任一操作：
在 Excel 2007 中，单击 " Microsoft Office 按钮"
，单击 " Excel 选项"，然后单击 "公式" 类别。在所有其他版本中，单击 "文件>选项" >公式"。
在 "计算选项" 部分的 "计算" 下，单击 "除模拟运算表外自动"。
提示: （可选）在 "公式" 选项卡上，单击 "计算选项" 上的箭头，然后单击 "计算" 组中的 "自动" （数据表格除外）。
-----------------------------------------------------------------------------------------------------------------------
下一步做什么？
如果有特定目标或较大的可变数据集，则可以使用其他一些 Excel 工具执行模拟分析。
单变量求解
如果您知道公式中的结果是预期的，但不知道公式需要什么输入值来获得该结果，请使用 "目标寻道" 功能。
Excel 规划求解
可以使用 Excel 规划求解加载项查找一组输入变量的最佳值。 "规划求解" 适用于计算 "目标" 和 "约束" 单元格中的公式的一组单元格（称为决策变量或简单的可变单元格）。 “规划求解”调整决策变量单元格中的值以满足约束单元格上的限制，并产生您对目标单元格期望的结果。
excel如何批量给数据添加前缀，怎么批量添加后缀
在工作中有时我们需要给一堆数据加上前缀或者后缀，一个一个添加耗时又麻烦。
只需一个小技巧即可快速批量对数据添加前后缀。
下面举例说明，例如这张表格，需要在工号前批量加上“WPS。
首先选中需要添加前缀的区域，在菜单栏中找到“文档助手”，点击“文本处理”，选择“插入文本到开头”。
输入“WPS”，点击“确定”，此时WPS前缀就添加好了。
同理，我们可以在此处选择插入文本到中间或者结尾。
关联标签：
导读索引
- 统一获取使用技巧，如何使用excel统一获取清理数据
- 如何使用excel收集数据并将数据可视化
- 如何批量删除excel数据单位，去掉数据后面的单位要怎么做
- 如何把excel里面的数据添加到数据模型，链接表使用技巧
- excel怎样快速汇总表格，如何汇总一年的表格数据
- excel怎么快速对两列数据进行查重，数据重复怎么办
- excel数据整理技巧有哪些，数据格式怎么设置
- excel数据怎么对齐，excel表格内容对齐怎么设置
- excel数据模型使用有什么限制
- excel数据快速处理的十大方法
- excel设置数据自动添加小数点方法，如何让数据自动添加小数点
- excel如何使用数据表进行多个答案的模拟运算
- excel如何批量给数据添加前缀，怎么批量添加后缀
粤公网安备 44010402000066号