引用数据

xl函数

Python 代码可以使用xl函数引用 Excel 中的值，该函数的语法格式为：

xl(data,headers)

其中，data是数据所在的Excel 对象，可以是下面几种类型中的一种：

• 单元格区域
• DataFrame
• 名称

Power Query连接

headers为可选参数，指定数据的第一行是否为标题，值为True时表示第一行是标题，值为False时表示不是。默认时headers参数的取值为False。

引用单元格(区域)的数据

对于在Excel工作表中直接输入或导入的数据，分有标题和没标题，引用单列、连续多列、不连续多列、单行、连续多行、不连续多行和单个值、多个值等多种情况，本节结合一个实例详细进行介绍。

引用给定数据

如图2-16所示，工作表单元格区域A1:C7中为给定数据，第一行为标题。单击单元格E1，首先在公式栏中输入“=PY(”进入Python模式，输入“xl(‘A1:C7’,headers=True)”，单击Ctrl+Enter键，获取数据。现在给定数据作为一个DataFrame返回。现在引用xl(‘E1’)就能获取到给定数据了。在单元格E1中进入Python模式，在公式栏输入“xl(‘E1’).describe()”，单击Ctrl+Enter键，返回一个包含给定数据统计量信息的DataFrame对象。预览该对象的内容，如图2-16所示，得到的统计量包括数据个数、均值、标准差、最小值、最大值和25%, 50%, 75%分位数。

注意，第一行作为标题时它是作为DataFrame的索引行存在的，不参与计算。

图2-16 获取给定数据并进行描述

默认时，xl函数中headers参数的值等于False。引用方式xl(‘A1:C7’)得到的DataFrame中，第一行数据不作为标题，而是作为值的一部分。如果要把“A B C”这一行去掉，使用引用方式xl(‘F2:H7’)。图2-17中单元格E1中的DataFrame是采用第一种引用方式得到的，单元格J1中的DataFrame采用第二种引用方式得到。

单元格E9和E10右侧展开了二者的字符串表示，可见，这两种引用方式都没有设置标题，所以自动设置从0开始的数字作为列的索引，前面的0, 1和2为第一列到第三列的索引。后面的0, 1, 2, …是各行数据的索引。

图2-17 没有标题的情况

有标题时引用列

当xl函数的headers参数的取值为True时，将给定数据的第一行作为DataFrame的索引行，此时，每一列的列名是指定的。

如图2-18所示，首先单元格E2在Python模式下用公式

xl(‘A1:C7’,headers=True)

返回DataFrame。单元格E4在Python模式下输入公式

xl(‘E2’)['B']

引用B列。单元格E5在Python模式下输入公式

xl(‘E2’).B

仍然是引用B列，这是另外一种引用方式。

引用单列返回一个Series类型的对象。

图2-18 引用单列

引用多列分引用不连续多列和引用连续多列两种情况。两种情况下又有不同的引用方法。

图2-19中，单元格E2在Python模式下用公式

xl(‘A1:C7’,headers=True)

返回DataFrame。单元格E4在Python模式下输入公式

xl(‘E2’)[['A','C']]

引用A列和C列。单元格E5在Python模式下输入公式

xl(‘E2’).loc[:,['A','C']]

引用A列和C列。单元格E6在Python模式下输入公式

xl(‘E2’).loc[:,'A':'C']

引用A列到C列。单元格E7在Python模式下输入公式

xl(‘E2’).loc[:,:'B']

引用B列及前面的各列，包括B列。单元格E8在Python模式下输入公式

xl(‘E5’).loc[:,'B':]

引用B列后面的各列，不包括B列。

图2-19 有标题时引用多列

无标题时引用列

当xl函数的headers参数的取值为True时，将给定数据的第一行作为DataFrame的索引行，此时，每一列的列名是指定的。

如图2-20所示，首先单元格E2在Python模式下用公式

xl(‘A2:C7’)

返回DataFrame。单元格E3在Python模式下输入公式

xl(‘E2’)[2]

引用第3列。单元格E4在Python模式下输入公式

xl(‘E2’).loc[:,2]

引用第3列。单元格E5在Python模式下输入公式

xl(‘E2’).loc[:,1:2]

引用第2列到第3列。单元格E6在Python模式下输入公式

xl(‘E2’).loc[:,[0,2]]

引用第1列和第3列。单元格E7在Python模式下输入公式

xl(‘E2’).loc[:,1:]

引用第2列及以后各列，包括第2列。单元格E8在Python模式下输入公式

xl(‘E2’).loc[:,:1]

引用第2列及以前各列，包括第2列。

图2-20 无标题时引用列

引用行

如图2-21所示，首先单元格E2在Python模式下用公式

xl(‘A1:C7’,headers=True)

返回DataFrame。单元格E3在Python模式下输入公式

xl(‘E2’).loc[2]

返回第3行数据。单元格E4在Python模式下输入公式

xl(‘E2’).loc[2,:]

返回第3行数据。单元格E5在Python模式下输入公式

xl(‘E2’)[2:6]

返回第3行到第6行的数据。单元格E6在Python模式下输入公式

xl(‘E2’).loc[[2,5],:]

返回第3行和第6行的数据。单元格E7在Python模式下输入公式

xl(‘E2’)[2:]

返回第3行到最后1行的数据，包括第3行。单元格E8在Python模式下输入公式

xl(‘E2’)[:3]

返回第3行及前面各行的数据，包括第3行。

图2-21 引用行

引用值

如图2-22所示，首先单元格E2在Python模式下用公式

xl(‘A1:C7’,headers=True)

返回DataFrame。单元格E4在Python模式下输入公式

xl(‘E2’).loc[3,'B']

返回第4行B列的数据。单元格E5在Python模式下输入公式

xl(‘E2’).loc[3:6,'B':'C']

返回第4行到第6行，B列和C列的数据。

图2-22 引用值

区分不同

如图2-23所示，首先单元格E2在Python模式下用公式

xl(‘A1:C7’,headers=True)

返回DataFrame。

单元格E4在Python模式下输入公式

xl(‘E2’)['B']

返回B列数据。单元格E5在Python模式下输入公式

xl(‘E2’)[['B']]

同样返回B列的数据。

比较两种引用方式，前者有一层索引符号，后者有两层，返回的结果都是B列数据，但是数据类型不一样。前者返回的是Series对象，而后者返回的是DataFrame对象。

图2-23 比较不同

使用DataFrame引用数据

2.3.2小节用xl(‘A1:C7’)引用数据时返回的是一个DataFrame对象，所以前面实际上是在使用DataFrame引用数据。本小节强调的不同点在于，用一个变量引用该DataFrame对象，这样使用更方便。

如图2-24所示，首先单元格E2在Python模式下用公式

df=xl(‘A1:C7’,headers=True)

将表示给定数据的DataFrame对象用变量df进行引用，这样，后面就可以直接使用df来表示这个DataFrame对象。

单元格E3在Python模式下输入公式

df.describe()

返回给定数据的一些描述统计量。单元格E4在Python模式下输入公式

df['B']

返回B列数据。单元格E5在Python模式下输入公式

df.B

返回B列数据。单元格E6在Python模式下输入公式

df.loc[2]

返回第3行数据。单元格E7在Python模式下输入公式

df[['A','C']]

返回A列和C列数据。单元格E8在Python模式下输入公式

df.loc[:,[‘A’,’C’]]

返回A列和C列数据。单元格E9在Python模式下输入公式

df.loc[:,’A’:’C’]

返回A列到C列数据。

图2-24 用DataFrame引用数据

使用名称引用数据

Excel中可以给单元格或单元格区域指定一个名称，以后可以直接用这个名称引用这个单元格或单元格区域。

图2-25 给单元格区域指定名称

图2-25中单元格区域A1:C7中为给定数据，给该单元格区域指定一个名称。首先在工作表中用鼠标选择单元格区域A1:C7，然后在Excel的“公式”功能区单击“定义名称”下拉菜单中的“定义名称”选项，打开“新建名称”对话框，如图2-26所示。

图2-26 “新建名称”对话框

在“名称”文本框中输入名称“Data”，在“引用位置”文本框中已经自动输入了选定单元格区域的范围。单击“确定”按钮，完成命名。下面就可以直接使用该名称进行数据引用了。

如图2-25所示，首先单元格E2在Python模式下用公式

df=xl(‘Data’,headers=True)

将DataFrame对象用变量df进行引用。单元格E3在Python模式下输入公式

df.describe()

返回给定数据的一些描述统计量。单元格E4在Python模式下输入公式

df[‘B’]

返回B列数据。单元格E5在Python模式下输入公式

df.B

返回B列数据。

引用Power Query导入的数据

首先按照2.1.2小节中介绍的导入Excel文件数据的方法用Power Query导入Samples目录下第4章的示例数据文件“工资表.xlsx”。加载后的数据显示在一个名为NewData的新工作表中，连接的名字也叫NewData。

图2-27 引用Power Query导入的数据

如图2-27所示，首先单元格A2在Python模式下用公式

df=xl(‘NewData’)

将DataFrame对象用变量df进行引用。单元格A3在Python模式下输入公式

xl(‘NewData[工资]’)

返回“工资”列数据。单元格A4在Python模式下输入公式

xl(‘NewData[[年龄]:[工资]]’)

返回“年龄”列到“工资”列的数据。单元格A6在Python模式下输入公式

df=xl(‘NewData’)

用变量df引用对象。单元格A7在Python模式下输入公式

df[6]

返回第7列数据。单元格A8在Python模式下输入公式

df[4:6]

返回第5行到第6行数据。单元格A9在Python模式下输入公式

df[[2,4,6]]

返回第3列、第5列和第7列数据。单元格A10在Python模式下输入公式

df.loc[2]

返回第3行数据。单元格A11在Python模式下输入公式

df.loc[2:5]

返回第3-6行数据。单元格A12在Python模式下输入公式

df.loc[[2,3,5]]

返回第3,,4,7行数据。单元格A13在Python模式下输入公式

df.loc[2:5,1:3]

返回第3-6行，第2-4列数据。

指定工作表

使用Excel内置Python时，可以在一个工作表中引用其他工作表的数据。只需要在引用单元格区域的前面添加”其他工作表名称!”即可。例如，在工作表Sheet1中使用”Sheet2!A1:C3”，表示在Sheet1中引用工作表Sheet2中A1:C3单元格区域内的数据。

图2-28所示的工作簿中有Sheet1, Sheet2和Sheet3三个工作表，Sheet1工作表的单元格A1在Python模式下输入公式

df=xl("Sheet2!A1:C7",headers=True)

引用Sheet2工作表单元格区域A1:C7中的数据。单元格A4在Python模式下输入公式

df2=xl("Sheet3!A1:C7",headers=True)

引用Sheet3工作表单元格区域A1:C7中的数据。

图2-28 引用其他工作表中的数据

前面一个小节介绍了引用Power Query导入的数据。实际上，Power Query导入的数据加载到Excel新工作表中时以超级表的形式呈现。此时可以使用前一小节介绍的方法进行引用，也可以将超级表转换为普通表后跨表引用。

将超级表转换为普通表，先选定超级表，然后在Excel“表设计”功能区单击“转换为区域”按钮即可。

特殊说明符

引用Power Query导入的数据时，可以在写公式时指定特殊说明符，指定具体要引用的数据。这里介绍引用标题、引用数据和引用全部数据的说明符。

图2-29 使用特殊说明符

首先如2.3.5小节用Power Query导入工资表数据。如图2-29所示，单元格G2在Python模式下输入公式

xl("NewData[[#标题],[工资]]")

返回“工资”列的标题。单元格G3在Python模式下输入公式

xl("NewData[[#数据],[工资]]")

返回“工资”列的数据。单元格G4在Python模式下输入公式

xl("NewData[#全部]")

返回全部标题和数据。单元格G5在Python模式下输入公式

xl("NewData[[#全部],[工资]]")

返回“工资”列的标题和数据。

列名中的转义字符

引用Power Query导入的数据时，对于一些特殊字符打头的列名，需要转义才能引用。2.3.5小节用Power Query导入了工资表数据，导入后是一个超级表，如图2-30所示。现在将列名“性别”修改为“#sex”，将列名“级别”修改为“[级别”。

图2-30 修改列名

修改后的列名用内置Python是不能直接引用的，此时给列名前面添加一个单引号进行转义即可使用。

如图2-31所示，单元格A2在Python模式下输入公式

xl("NewData['#sex]")

返回“#sex”列的数据。单元格A3在Python模式下输入公式

xl("NewData['[级别]")

返回“[级别”列的数据。

图2-31 使用转义字符