计算机二级必备干货——Excel基础操作篇（2）

分享兴趣，传播快乐，增长见闻，留下美好！亲爱的您，这里是LearningYard新学苑。今天小编为大家带来计算机二级必备干货——Excel基础操作篇（2）。

获取和转换数据（Power Query）

获取和转换数据是Excel 2016内置的一组强大的功能，能对数据源进行连接、组合、优化等操作，满足不同的分析需求。

Get and transform data (Power Query)

Capturing and transforming data is a powerful set of features built into Excel 2016 that can connect, combine, and optimize data sources to meet different analysis needs.

001.获取数据

Power Query可从多种渠道获取数据，例如：从表格、从工作簿、从文本、从文件夹。

【获取数据操作步骤】

【数据】选项卡→【获取和转换】组→选择获取数据渠道。即可打开Power Query编辑器。

001. Get data

Power Query retrieves data from a variety of sources, such as tables, workbooks, text, and folders.

[Procedure for obtaining data]

【 Data 】 TAB →【 Get and Transform 】 Groups → Select the access to data. Open the Power Query editor.

002.整理数据

Power Query编辑器中可对导入的数据进行整理和转换。

【行列操作步骤】

【主页】选项卡→【管理列】和【减少列】组可对行列进行调整

【拆分列操作步骤】

【主页】选项卡→【拆分列】下拉按钮打开→选择【按照从非数字到数字的转换】

【转换和修改数据格式操作步骤】

数据类型中可对数字格式进行修改，类似于Excel中设置单元格格式。若数据未添加标题行，可使用【将第一行用作标题】功能。

【查询设置】

在查询设置中可设置表名称。在Power Query中如需回到上一步操作，不能采用撤销，需在应用的步骤中删除多余步骤。

【提取文本考点】

【转换】选项卡→点击【提取】下拉箭头可以选择采用多种方式提取文本。

002. Organize data

The imported data can be collated and transformed in the Power Query editor.

[Operation steps of row and column]

【 Home 】 TAB →【 Manage Columns 】 and 【 Reduce Columns 】 groups can adjust the columns

[Disassembling and disassembling operation steps]

【 Home 】 TAB →【 Disassemble 】 Drop down button to open → Select 【 According to the conversion from non-number to number 】

Procedure for converting and modifying data formats

The format of numbers can be changed in data types, similar to the format of cells in Excel. If the data does not have a header row, use the first line as a header function.

[Query Settings]

You can set the table name in query Settings. In Power Query, if you want to go back to the previous step, undo cannot be used. You need to delete unnecessary steps in the application steps.

【 Extract text test points 】

【 Convert 】 TAB → Click the 【 Extract 】 drop - down arrow to select a variety of ways to extract text.

003.逆透视列和透视列

逆透视列和透视列可以将表格由一维于二维表之间的相互转换，逆透视列和透视列是两个相反的功能。

【表格由二维表转换成一维表操作步骤】

选中城市列→【转换】选项卡→点击【逆透视列】下拉按钮→选择【逆透视其他列】，即可将二维表转化为一维表。

【表格由一维表转化为二维表操作步骤】

选中月份这列→【转换】选项卡→选择【透视列】→值列选择【降水量】→点击【确定】，即可实现一维表转换成二维表。

003. Reverse perspective column and perspective column

Reverse pivot columns and pivot columns convert tables from one-dimensional to two-dimensional. Reverse pivot columns and pivot columns are two opposite functions.

[Table conversion from two-dimensional table to one-dimensional table operation steps]

Select the city column → [Transform] TAB → click the [Reverse Perspective Column] drop-down button → select [Reverse Perspective Other Columns] to transform the two-dimensional table into a one-dimensional table.

[Table from one-dimensional table to two-dimensional table operation steps]

Select the month column → [Conversion] TAB → Select [Perspective Column] → Value column select [precipitation] → click [OK] to convert the one-dimensional table into a two-dimensional table.

004.追加查询

追加查询是将几个结构完全相同（列数和列标题均一致）的工作表组合到一起，并且可以实现随原表自动更新的功能。

【追加查询操作步骤】

【数据】选项卡→点击【新建查询】→点击【从文件】选择【从工作簿】→找对应工作簿→勾选【选项多项】→勾选对应工作表→点击【转换数据】。

在打开的Power Query编辑器中→点击【主页】选项卡→点击【追加查询】下拉按钮→选择【将查询追加为新查询】。

选择【三个或更多表】→将三个工作表都添加到【要追加的表】中→点击【确定】。

点击【关闭并上载】，即可将查询追加至原工作簿中，最后删除多余工作表，修改工作表名称即可完成操作。

004. Add query

An append query is a combination of several worksheets that are identical in structure (the number of columns and column headings are the same) and can be automatically updated with the original table.

[Add query operation Procedure]

[Data] TAB → Click [New query] → click [From file] select [from Workbook] → find the corresponding workbook → check [options] → check the corresponding worksheet → click [Convert Data].

In the Power Query editor that opens → click the Home TAB → click the Append Query drop-down button → select AppEnd Query as a new Query.

Select three or more tables → add all three worksheets to Tables to append → click OK.

Click 'Close and upload' to append the query to the original workbook, finally delete the excess worksheet, modify the worksheet name to complete the operation.

如果您对今天的文章有独特的想法，欢迎给我们留言，让我们相约明天，祝您今天过得开心快乐！

END