如何使用excel快速获取想要的数据
啊南 3100阅读 2020.06.18
【导语】: 现在在办公当中,为了提高工作效率,减少不必要的时间和精力,我们一般都会采用Excel工作表格来处理一些繁杂的数据。今天就来教大家如何在Excel表格中快速获取想要的数据。
返回在某一数组或数据区域中出现频率最多的数值。
语法
MODE.SNGL(number1,[number2],...)
MODE.SNGL 函数语法具有下列参数:
Number1 必需。 要计算其众数的第一个参数。
Number2, ... 可选。 要计算其众数的 2 到 254 个参数。 也可以用单一数组或对某个数组的引用来代替用逗号分隔的参数。
备注
参数可以是数字或者是包含数字的名称、数组或引用。
如果数组或引用参数包含文本、逻辑值或空白单元格,则这些值将被忽略;但包含零值的单元格将计算在内。
如果参数为错误值或为不能转换为数字的文本,将会导致错误。
如果数据集中不含有重复的数据点,则 MODE.SNGL 返回错误值 N/A。
注意: MODE.SNGL 函数用于度量集中趋势,集中趋势在统计分步中是一组数字的中心位置。 最常用的集中趋势度量方式有以下三种:
平均值 平均值是算术平均数,由一组数相加然后除以这些数的个数计算得出。 例如,2、3、3、5、7 和 10 的平均值为 30 除以 6,即 5。
中值 中值是一组数中间位置的数;即一半数的值比中值大,另一半数的值比中值小。 例如,2、3、3、5、7 和 10 的中值是 4。
众数 众数是一组数中最常出现的数。 例如,2、3、3、5、7 和 10 的众数是 3。
对于对称分布的一组数来说,这三种集中趋势的度量是相同的。 对于偏态分布的一组数来说,这三种集中趋势的度量可能不同。
示例
复制下表中的示例数据,然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果,请选中它们,按 F2,然后按 Enter。 如果需要,可调整列宽以查看所有数据。
数据 |
||
---|---|---|
5.6 |
||
4 |
||
4 |
||
3 |
||
2 |
||
4 |
||
公式 |
说明 |
结果 |
=MODE.SNGL(A2:A7) |
上面数字中的众数,即出现频率最高的数 |
4 |
本文介绍 Microsoft Excel 中 ADDRESS 函数的公式语法和用法。 请在“另请参阅”部分中查找指向有关使用邮件地址或创建邮件标签信息的链接。
说明
你可以使用ADDRESS 函数,根据指定行号和列号获得工作表中的某个单元格的地址。 例如,ADDRESS(2,3) 返回 $C$2。 再例如,ADDRESS(77,300) 返回 $KN$77。 可以使用其他函数(如 ROW 和 COLUMN 函数)为 ADDRESS 函数提供行号和列号参数。
语法
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
ADDRESS 函数语法具有以下参数:
row_num 必需。 一个数值,指定要在单元格引用中使用的行号。
column_num 必需。 一个数值,指定要在单元格引用中使用的列号。
abs_num 可选。 一个数值,指定要返回的引用类型。
abs_num |
返回的引用类型 |
---|---|
1 或省略 |
绝对值 |
2 |
绝对行号,相对列标 |
3 |
相对行号,绝对列标 |
4 |
相对值 |
注意: 要更改 Excel 使用的引用样式,请单击“文件”选项卡,单击“选项”,然后单击“公式”。 在“使用公式”下,选中或清除“R1C1 引用样式”复选框。
sheet_text 可选。 一个文本值, 指定要用作外部引用的工作表的名称。 例如, 公式=ADDRESS (1, 1,,, "Sheet2")返回Sheet2! $A $1。 如果省略了sheet_text参数, 则不使用工作表名称, 并且该函数返回的地址引用当前工作表上的单元格。
示例
复制下表中的示例数据,然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果,请选中它们,按 F2,然后按 Enter。 如果需要,可调整列宽以查看所有数据。
公式 |
说明 |
结果 |
---|---|---|
=ADDRESS(2,3) |
绝对引用 |
$C$2 |
=ADDRESS(2,3,2) |
绝对行号,相对列标 |
C$2 |
=ADDRESS(2,3,2,FALSE) |
绝对行号,R1C1 引用样式中的相对列标 |
R2C[3] |
=ADDRESS(2,3,1,FALSE,"[Book1]Sheet1") |
对另一个工作簿和工作表的绝对引用 |
'[Book1]Sheet1'!R2C3 |
=ADDRESS(2,3,1,FALSE,"EXCEL SHEET") |
对另一个工作表的绝对引用 |
'EXCEL SHEET'!R2C3 |
本文介绍 Microsoft Excel 中 KURT 函数的公式语法和用法。
说明
返回一组数据的峰值。 峰值反映与正态分布相比某一分布的相对尖锐度或平坦度。 正峰值表示相对尖锐的分布。 负峰值表示相对平坦的分布。
语法
KURT(number1, [number2], ...)
KURT 函数语法具有下列参数:
number1, number2, ... ???Number1 是必需的,后续数字是可选的。 用于计算峰值的 1 到 255 个参数。 也可以用单一数组或对某个数组的引用来代替用逗号分隔的参数。
备注
参数可以是数字或者是包含数字的名称、数组或引用。
逻辑值和直接键入到参数列表中代表数字的文本被计算在内。
如果数组或引用参数包含文本、逻辑值或空白单元格,则这些值将被忽略;但包含零值的单元格将计算在内。
如果参数为错误值或为不能转换为数字的文本,将会导致错误。
如果数据点少于四个,或者样本的标准偏差等于零,则 KURT 返回 #DIV/0! 。
峰值的公式为:
S 为样本的标准偏差。
示例
复制下表中的示例数据,然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果,请选中它们,按 F2,然后按 Enter。 如果需要,可调整列宽以查看所有数据。
数据 |
||
---|---|---|
3 |
||
4 |
||
5 |
||
2 |
||
3 |
||
4 |
||
5 |
||
6 |
||
4 |
||
7 |
||
公式 |
说明 |
结果 |
=KURT(A2:A11) |
上述数据集的峰值 |
-0.151799637 |
返回根据 known_y's 和 known_x's 中的数据点拟合的线性回归直线的斜率。
斜率为直线上任意两点的重直距离与水平距离的比值,也就是回归直线的变化率。
语法
SLOPE(known_y's,known_x's)
Known_y's 数字型因变量数据点数组或单元格区域。
Known_x's 自变量数据点集合。
说明
参数可以是数字,或是包含数字的名称、数组或引用。
若数组或引用参数包含文本、逻辑值或空白单元格,则这些值将被忽略;但包含零值的单元格将计算在内。
若 known_y's 和 known_x's 为空或其数据点个数不同,则 SLOPE 返回错误值 #N/A。
回归直线的斜率计算公式如下:
其中 x 和 y 是样本平均值 AVERAGE(known_x's) 和 AVERAGE(known_y's)。
SLOPE函数的功能是把已知自变量和因变量座位数据点,计算线性回归线的斜率。
下面给大家示范一下如何使用SLOPE函数计算线性回归线的斜率。
具体方法与步骤如下:
首先将光标放在B10处,点击插入函数-统计-SLOPE函数
此时弹出对话框,共两项参数:
“已知Y值集合”是已知Y值的区域;
“已知X值集合”是已知X值的区域。
在“已知Y值集合”输入(B3:B8),在“已知X值集合”输入(C3:C8)。
点击确定,就能得出线性回归线的斜率了。
从支持 COM 自动化的程序中检索实时数据
语法
RTD(ProgID, server, topic1, [topic2], ...)
RTD 函数语法具有下列参数:
ProgID:必需。 已安装在本地计算机上的已注册 COM 自动化加载项 ProgID 的名称。 将该名称用引号括起来。
server:必需。应运行加载项的服务器的名称。如果没有服务器,则在本地运行程序,将此参数保留为空。否则输
入引号 ("") 将服务器名称括起来。 在 Visual Basic for Applications (VBA) 中使用 RTD 时,服务器需要双引号或
VBA Nullstring 属性,即使在本地运行服务器也不例外。
Topic1, topic2, ... :Topic1 是必需的,后续主题是可选的。1 到 253 个参数,这些参数放在一起代表一个唯一的
实时数据。
说明
必须在本地计算机上创建和注册 RTD COM 自动化加载项。
如果未安装实时数据服务器,则在尝试使用 RTD 函数时,单元格中将出现错误消息。
如果服务器继续更新结果,那么与其他函数不同,RTD 公式将在 Microsoft Excel 处于自动计算模式时进行更改。
RTD函数的作用是实现从COM中引用实时数据,从而极大的扩展Excel与外部程序实现信息互通的目的。
这个函数常用来制作自动更新的股票信息表,使用这个函数需要注意的是:
必须在本地计算机上创建并且要注册RTD COM自动化加载项;如果没有安装实时数据服务器,会返回错误值。
具体方法与步骤如下:
在我们成功创建成功了一个自动化加载项后,就可以使用此函数引用数据了,接下来在这个表中向大家演示一下这
个函数的具体使用步骤:
选中U14单元格,点击插入函数,在查找框中查找并找到RTD函数,点击确定;
我们在prog ID 输入已创建的加载项信息:rtdvb6.stock;
Server不填;
Topic填入股票代码SZ002200,前后加上双引号;
点击确定,即可完成引用~
本文介绍 Microsoft Excel 中 SMALL 函数的公式语法和用法。
说明
返回数据集中的第 k 个最小值。 使用此函数以返回在数据集内特定相对位置上的值。
语法
SMALL(array,k)
SMALL 函数语法具有下列参数:
Array 必需。 需要找到第 k 个最小值的数组或数值数据区域。
K 必需。 要返回的数据在数组或数据区域里的位置(从小到大)。
备注
如果 array 为空,则小返回 #NUM! 。
如果 k ≤0或 k 超过数据点的数量,则 SMALL 返回 #NUM! 。
如果 n 为数组中的数据点个数,则 SMALL(array,1) 等于最小值,SMALL(array,n) 等于最大值。
示例
复制下表中的示例数据,然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果,请选中它们,按 F2,然后按 Enter。 如果需要,可调整列宽以查看所有数据。
数据 1 |
数据 2 |
|
---|---|---|
3 |
1 |
|
4 |
4 |
|
5 |
8 |
|
2 |
3 |
|
3 |
7 |
|
4 |
12 |
|
6 |
54 |
|
4 |
8 |
|
7 |
23 |
|
公式 |
描述(结果) |
结果 |
=SMALL(A2:A10,4) |
第一列中第四个最小值 (4) |
4 |
=SMALL(B2:B10,2) |
第二列中第二个最小值 (3) |
3 |
本文介绍 Microsoft Excel 中 CHOOSE 函数的公式语法和用法。
说明
使用 index_num 返回数值参数列表中的数值。 使用 CHOOSE 可以根据索引号从最多 254 个数值中选择一个。 例如,如果 value1 到 value7 表示一周的 7 天,那么将 1 到 7 之间的数字用作 index_num 时,CHOOSE 将返回其中的某一天。
语法
CHOOSE(index_num, value1, [value2], ...)
CHOOSE 函数语法具有以下参数:
index_num 必需。 用于指定所选定的数值参数。 index_num 必须是介于 1 到 254 之间的数字,或是包含 1 到 254 之间的数字的公式或单元格引用。
如果 index_num 为 1,则 CHOOSE 返回 value1;如果为 2,则 CHOOSE 返回 value2,以此类推。
如果 index_num 小于 1 或大于列表中最后一个值的索引号,则 CHOOSE 返回 #VALUE! 错误值。
如果 index_num 为小数,则在使用前将被截尾取整。
value1, value2, ... Value1 是必需的,后续值是可选的。 1 到 254 个数值参数,CHOOSE 将根据 index_num 从中选择一个数值或一项要执行的操作。 参数可以是数字、单元格引用、定义的名称、公式、函数或文本。
备注
如果 index_num 为一个数组,则在计算函数 CHOOSE 时,将计算每一个值。
函数 CHOOSE 的数值参数不仅可以为单个数值,也可以为区域引用。
例如,下面的公式:
=SUM(CHOOSE(2,A1:A10,B1:B10,C1:C10))
相当于:
=SUM(B1:B10)
然后基于区域 B1:B10 中的数值返回值。
先计算 CHOOSE 函数,返回引用 B1:B10。 然后使用 B1:B10(CHOOSE 函数的结果)作为其参数来计算 SUM 函数。
示例
复制下表中的示例数据,然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果,请选中它们,按 F2,然后按 Enter。 如果需要,可调整列宽以查看所有数据。
数据 |
|
|
---|---|---|
1st |
Nails |
|
2nd |
Screws |
|
3rd |
Nuts |
|
Finished |
Bolts |
|
公式 |
说明 |
结果 |
=CHOOSE(2,A2,A3,A4,A5) |
第二个列表参数的值(单元格 A3 中的值) |
2nd |
=CHOOSE(4,B2,B3,B4,B5) |
第四个列表参数的值(单元格 B5 中的值) |
Bolts |
=CHOOSE(3,"Wide",115,"world",8) |
第三个列表参数的值 |
全球 |
示例 2
数据 |
||
---|---|---|
23 |
||
45 |
||
12 |
||
10 |
||
公式 |
描述(结果) |
结果 |
=SUM(A2:CHOOSE(2,A3,A4,A5)) |
计算单元格区域 A2:A4 中所有数值的和。 CHOOSE 函数返回 A4,将其作为 SUM 函数区域的第二部分。 |
80 |
当需要在表格或区域中按行查找项目时,请使用XLOOKUP函数。 例如,按部件号查找汽车部件的价格,或根据员工 ID 查找员工姓名。 借助 XLOOKUP,你可以在一列中查找搜索词,并在同一行的另一列中返回结果,无论返回结果的列在原列的哪一侧。
注意: 此函数当前可用于每月频道中的 Microsoft 365 订阅者。 将在2020年7月开始的半年频道中 Microsoft 365 订阅者提供。
-----------------------------------------------------------------------------------------
语法
XLOOKUP 函数搜索区域或数组,并返回一个与它找到的第一个匹配项相对应的项。 如果不存在匹配项,则 XLOOKUP 可返回最接近(近似值)的匹配项。
= XLOOKUP (lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode])
参数 |
说明 |
---|---|
lookup_value 必需 |
查找值 |
lookup_array 必需 |
要搜索的数组或区域 |
return_array 必需 |
要返回的数组或区域 |
[if_not_found] 可选 |
如果找不到有效的匹配项,则返回你提供的 [if_not_found] 文本。 如果找不到有效的匹配项,并且缺少 [if_not_found],则会返回 #N/A。 |
[match_mode] 可选 |
指定匹配类型: 0 - 完全匹配。 如果未找到,则返回 #N/A。 这是默认选项。 -1 - 完全匹配。 如果没有找到,则返回下一个较小的项。 1 - 完全匹配。 如果没有找到,则返回下一个较大的项。 2 - 通配符匹配,其中 *, ? 和 ~ 有特殊含义。 |
[search_mode] 可选 |
指定要使用的搜索模式: 1 - 从第一项开始执行搜索。 这是默认选项。 -1 - 从最后一项开始执行反向搜索。 2 - 执行依赖于 lookup_array 按升序排序的二进制搜索。 如果未排序,将返回无效结果。 2 - 执行依赖于 lookup_array 按降序排序的二进制搜索。 如果未排序,将返回无效结果。 |
示例 1
此示例使用简单的 XLOOKUP 查找国家/地区名称,然后返回其电话国家/地区代码。 它仅包括 lookup_value(单元格 F2)、lookup_array(范围 B2:B11)和 return_array(范围 D2:D11)参数。 它不包括 match_mode 参数,因为 XLOOKUP 默认为完全匹配。
注意: XLOOKUP 与 VLOOKUP 的不同之处在于,它使用单独的查找并返回数组,而 VLOOKUP 使用一个表数组,后跟列索引号。 在本例中,等效的 VLOOKUP 公式是:=VLOOKUP(F2,B2:D11,3,FALSE)
示例 2
在此示例中,我们将根据员工 ID 编号查找员工信息。 与 VLOOKUP 不同,XLOOKUP 可以返回具有多个项的数组,这允许单个公式从单元格 C5: D14 返回员工姓名和部门。
示例 3
此示例将if_not_found参数添加到上面的示例。
示例 4
以下示例在列 C 中查找在单元格 E2 中输入的个人收入,并在列 B 中查找匹配的税率费率。如果未找到任何内容,则将 if-not_found 参数设置为返回0。 Match_mode 参数设置为1,这意味着该函数将查找精确匹配,如果找不到它,它将返回下一个较大的项。 最后,search_mode 参数设置为1,这意味着该函数将从第一个项搜索到最后一个项。
注意: 与 VLOOKUP 不同,lookup_array 列位于 return_array 列的右侧,而 VLOOKUP 只能从左到右查找。
示例 5
接下来,我们将使用嵌套的 XLOOKUP 函数执行垂直和水平匹配。 在这种情况下,它将首先查找 B 列中的毛利润,然后查找表格首行(范围 C5:F5)中的第一季度,并返回两者交集的值。 这类似于结合使用 INDEX 和 MATCH 函数。 你也可以使用 XLOOKUP 替换 HLOOKUP 函数。
单元格 D3:F3 中的公式是:=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))。
示例 6
此示例使用 SUM 函数,并嵌套两个 XLOOKUP 函数对两个范围之间的所有值求和。 在这种情况下,我们希望对葡萄、香蕉和 include 梨的值进行求和,这些值位于两个值之间。
单元格 E3 中的公式为: = SUM (XLOOKUP (B3,B6: B10,E6: E10): XLOOKUP (C3,B6: B10,E6: E10))
它如何工作? XLOOKUP 返回一个区域,因此当它计算时,该公式最后看起来如下所示: = SUM ($E $7: $E $9)。 可通过选择包含与此类似的 XLOOKUP 公式的单元格来查看其工作原理,然后转到公式 > 公式审核 > 公式求值,再按“求值”按钮逐步执行计算。
INDEX 函数返回表格或区域中的值或值的引用。
数组形式
说明
返回表或数组中元素的值,由行号和列号索引选择。
当函数 INDEX 的第一个参数为数组常量时,使用数组形式。
语法
INDEX(array, row_num, [column_num])
INDEX 函数的数组形式具有下列参数:
数组 必需。 单元格区域或数组常量。
如果数组只包含一行或一列,则相应的 row_num 或 column_num 参数是可选的。
如果数组具有多行和多列,并且仅使用 row_num 或 column_num,则 INDEX 返回数组中整个行或列的数组。
row_num 必需,除非存在 column_num。 选择数组中的某行,函数从该行返回数值。 如果省略 row_num,则需要 column_num。
column_num 可选。 选择数组中的某列,函数从该列返回数值。 如果省略 column_num,则需要 row_num。
备注
如果同时使用 row_num 和 column_num 参数,则 INDEX 返回位于 row_num 和 column_num 交叉处的单元格中的值。
row_num 和 column_num 必须指向数组内的单元格;否则,INDEX 将返回 #REF! 错误。
如果将 row_num 或 column_num 设置为0(零),则 INDEX 将分别返回整列或整行的值的数组。 若要使用以数组形式返回的值,请以数组公式的形式输入 INDEX 函数。
注意: 如果您有最新版本的Microsoft 365,则可以在输出区域左上角的单元格中输入公式,然后按enter以将公式确认为动态数组公式。 否则,必须先选择 "输出区域",然后在输出区域左上角的单元格中输入公式,然后按CTRL + SHIFT + ENTER确认公式,然后再将公式输入为旧数组公式。 Excel 将使用括号将公式括起来。
示例
示例 1
下面的示例使用 INDEX 函数查找某一行和某一列的交叉单元格中的值。
复制下表中的示例数据,然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果,请选中它们,按 F2,然后按 Enter。
数据 |
数据 |
|
---|---|---|
苹果 |
柠檬 |
|
香蕉 |
梨 |
|
公式 |
说明 |
结果 |
=INDEX(A2:B3,2,2) |
位于区域 A2:B3 中第二行和第二列交叉处的数值。 |
梨 |
=INDEX(A2:B3,2,1) |
位于区域 A2:B3 中第二行和第一列交叉处的数值。 |
香蕉 |
此示例在数组公式中使用 INDEX 函数查找一个 2x2 数组中指定两个单元格中的值。
注意: 如果您有最新版本的Microsoft 365,则可以在输出区域左上角的单元格中输入公式,然后按enter以将公式确认为动态数组公式。 否则,必须先选择两个空白单元格,然后在输出区域左上角的单元格中输入公式,然后按CTRL + SHIFT + ENTER确认该公式,然后才能输入为旧数组公式。 Excel 将使用括号将公式括起来。
公式 |
说明 |
结果 |
---|---|---|
=INDEX({1,2;3,4},0,2) |
数组的第一行、第二列中找到的数值。 数组包含第一行中的 1 和 2 以及第二行中的 3 和 4。 |
2 |
数组(与上面的数组相同)的第二行、第二列中找到的数值。 |
4 |
引用表单
说明
返回指定的行与列交叉处的单元格引用。 如果引用由非相邻的选项组成,则可以选择要查找的选择内容。
语法
INDEX(reference, row_num, [column_num], [area_num])
INDEX 函数的引用形式具有下列参数:
参阅 必需。 对一个或多个单元格区域的引用。
如果要为引用输入非相邻区域,请将引用括在括号中。
如果引用中的每个区域仅包含一行或一列,则 "row_num" 或 "column_num" 参数分别是可选的。 例如,对于单行的引用,可以使用函数 INDEX(reference,,column_num)。
row_num 必需。 引用中某行的行号,函数从该行返回一个引用。
column_num 可选。 引用中某列的列标,函数从该列返回一个引用。
area_num 可选。 选择一个引用区域,从该区域中返回 row_num 和 column_num 的交集。 选择或输入的第一个区域的编号为1,第二个区域为2,依此类推。 如果省略 area_num,则 INDEX 使用区域1。 此处列出的区域必须位于一个工作表上。 如果你指定的区域不在同一工作表上,它将导致 #VALUE! 错误。 如果需要使用彼此位于不同工作表上的区域,建议使用 INDEX 函数的数组形式,并使用另一个函数计算构成数组的区域。例如,可以使用 CHOOSE 函数计算将使用的范围。
例如,如果引用描述单元格(A1: B4,D1: E4,G1: H4 为),area_num 1 是区域 A1: B4,area_num 2 是区域 D1: E4,而 area_num 3 是范围 G1: H4 为。
备注
在引用和 area_num 选择特定范围后,row_num 并 column_num 选择特定单元格: row_num 1 是区域中的第一行,column_num 1 是第一列,依此类推。 INDEX 返回的引用是 row_num 和 column_num 的交集。
如果将 row_num 或 column_num 设置为0(零),则 INDEX 将分别返回整列或整行的引用。
row_num、column_num 和 area_num 必须指向引用中的单元格;否则,INDEX 将返回 #REF! 错误。 如果省略 row_num 和 column_num,则 INDEX 返回由 area_num 指定的引用中的区域。
函数 INDEX 的结果为一个引用,且在其他公式中也被解释为引用。 根据公式的需要,函数 INDEX 的返回值可以作为引用或是数值。 例如,公式 CELL("width",INDEX(A1:B2,1,2)) 等价于公式 CELL("width",B1)。 CELL 函数将函数 INDEX 的返回值作为单元格引用。 而在另一方面,公式 2*INDEX(A1:B2,1,2) 将函数 INDEX 的返回值解释为 B1 单元格中的数字。
示例
复制下表中的示例数据,然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果,请选中它们,按 F2,然后按 Enter。
水果 |
价格 |
计数 |
---|---|---|
苹果 |
¥6.90 |
40 |
香蕉 |
¥3.40 |
38 |
柠檬 |
¥5.50 |
15 |
橙子 |
¥2.50 |
二十五 |
梨 |
¥5.90 |
40 |
杏 |
¥28.00 |
10 |
腰果 |
¥35.50 |
utf-16 |
花生 |
¥12.50 |
20 |
胡桃 |
¥17.50 |
1.2 |
公式 |
说明 |
结果 |
=INDEX(A2:C6, 2, 3) |
区域 A2:C6 中第二行和第三列的交叉处,即单元格 C3 的内容。 |
38 |
=INDEX((A1:C6, A8:C11), 2, 2, 2) |
第二个区域 A8:C11 中第二行和第二列的交叉处,即单元格 B9 的内容。 |
1.25 |
=SUM(INDEX(A1:C11, 0, 3, 1)) |
对第一个区域 A1:C11 中的第三列求和,即对 C1:C11 求和。 |
216 |
=SUM(B2:INDEX(A2:C6, 5, 2)) |
返回以单元格 B2 开始到单元格区域 A2:A6 中第五行和第二列交叉处结束的单元格区域的和,即单元格区域 B2:B6 的和。 |
2.42 |
SUMPRODUCT函数返回对应的区域或数组的乘积之和。 默认运算是乘法,但加、减和除也可能。
在此示例中,我们将使用 SUMPRODUCT 返回给定项目和大小的总销售额:
SUMPRODUCT 匹配项目 Y/Size M 和 sum 的所有实例,因此,本示例21加41等于62。
语法
若要使用默认操作(乘法):
= SUMPRODUCT (array1,[array2],[array3],...)
SUMPRODUCT 函数语法具有下列参数:
参数 |
说明 |
---|---|
array1 必需 |
其相应元素需要进行相乘并求和的第一个数组参数。 |
[array2],[array3],.。。 可选 |
2 到 255 个数组参数,其相应元素需要进行相乘并求和。 |
按正常方式使用 SUMPRODUCT,但将数组参数替换为所需的算术运算符(*、/、+、-)。 执行所有操作后,结果将按常规方式进行求和。
注意: 如果使用算术运算符,请考虑将数组参数括在括号中,并使用括号对数组参数进行分组以控制算术运算的顺序。
备注
数组参数必须具有相同的维数。 否则,函数 SUMPRODUCT 将返回 #VALUE! 错误值 #REF!。 例如,= SUMPRODUCT (C2: C10,D2: D5)将返回错误,因为范围大小不同。
SUMPRODUCT 将非数字数组条目视为零。
示例 1
若要使用上面的示例列表创建公式,请键入 = SUMPRODUCT (C2: C5,D2: D5),然后按enter。 C 列中的每个单元格乘以列 D 中同一行的相应单元格,并将结果相加。 杂货的总金额为 $78.97。
若要编写提供相同结果的较长公式,请键入 = C2 * D2 + C3 * D3 + C4 * D4 + C5 * D5,然后按enter。 按 Enter 后,结果是相同的: $78.97。 单元格 C2 将乘以 D2,其结果将添加到单元格 D3 的单元格 C3 倍的结果中,依此类推。
示例 2
以下示例使用 SUMPRODUCT 返回销售代理的总净销售额,在这里,我们同时按代理的销售额和费用总额。 在这种情况下,我们使用的是excel 表,它使用结构化引用,而不是标准 Excel 区域。 在这里,你将看到 "销售"、"费用" 和 "代理" 范围按名称引用。
公式为: = SUMPRODUCT (([Sales]) + (Table1 [费用])) * (Table1 [Agent] = B8)),并返回单元格 B8 中列出的代理的所有销售和费用之和。
示例 3
在此示例中,我们希望返回由给定区域售出的特定项目的总数。 在这种情况下,East 地区销售的樱桃有多少?
此公式为: = SUMPRODUCT ((B2: B9 = B12) * (C2: C9 = C12) * D2: D9)。 它首先将东的发生次数乘以樱桃的匹配出现次数。 最后,它对 "销售额" 列中对应行的值求和。 若要查看 Excel 如何计算,请选择公式单元格,然后转到 "公式" >计算公式> 计算公式。
返回某一引用区域的左上角单元格的格式、位置或内容等信息。
语法
CELL(info_type,reference)
Info_type 一个文本值,指定所需要的单元格信息的类型。下面列出 info_type 的可能值及相应的结果。
Reference 表示要获取其有关信息的单元格。若忽略,则在 info_type 中所指定的信息将返回给最后更改的单元格。
下表描述 info_type 为“format”,以及引用为用内置数字格式设置的单元格时,函数 CELL 返回的文本值。
若 CELL 公式中的 info_type 参数为“format”,而且以后又用自定义格式设置了单元格,则必须重新计算工作表
以更新 CELL 公式。
说明
函数 CELL 用于与其他电子表格程序兼容。
如果将示例复制到空白工作表中,可能会更易于理解该示例。
CELL函数是一个超级实用的神秘函数,它的作用是可以获得指定单元格地址、值、文件路径等信息,
有强大的查找和提取信息功能。
下面给大家示范如何使用这个神秘的函数去获取当前文件路径。
具体方法与步骤如下:
打开表格,把光标放在B3处,点击插入函数-信息-CELL函数。
此时弹出对话框,共两项参数:
“信息类型”用于指定所需单元格信息类型,可参考右方参数选择所需的信息文本串;
“引用”是指需要了解信息的单元格。
我们根据右方信息参数表,在信息类型中输入"filename"。
点击确定,就能获取当前文件路径。
若要获取其他信息,我们可以参照信息参数表,只需更改“信息类型”的值。
就可以使用这个函数返回有关单元格的地址、值、文件路径等信息 。
这么神秘又强大的函数,你学会了吗~
本文介绍 Microsoft Excel 中 FIND 和 FINDB 函数的公式语法和用法。
说明
函数 FIND 和 FINDB 用于在第二个文本串中定位第一个文本串,并返回第一个文本串的起始位置的值,该值从第二个文本串的第一个字符算起。
重要:
这些函数可能并不适用于所有语言。
FIND 适用于使用单字节字符集 (SBCS) 的语言,而 FINDB 适用于使用双字节字符集 (DBCS) 的语言。 您的计算机上的默认语言设置对返回值的影响方式如下:
无论默认语言设置如何,函数 FIND 始终将每个字符(不管是单字节还是双字节)按 1 计数。
当启用支持 DBCS 的语言的编辑并将其设置为默认语言时,FINDB 会将每个双字节字符按 2 计数。 否则,FINDB 会将每个字符按 1 计数。
支持 DBCS 的语言包括日语、中文(简体)、中文(繁体)以及朝鲜语。
语法
FIND(find_text, within_text, [start_num])
FINDB(find_text, within_text, [start_num])
FIND 和 FINDB 函数语法具有下列参数:
find_text 必需。 要查找的文本。
within_text 必需。 包含要查找文本的文本。
start_num 可选。 指定开始进行查找的字符。 within_text 中的首字符是编号为 1 的字符。 如果省略 start_num,则假定其值为 1。
备注
FIND 和 FINDB 区分大小写,并且不允许使用通配符。 如果您不希望执行区分大小写的搜索或使用通配符,则可以使用 SEARCH 和 SEARCHB 函数。
如果 find_text 为空文本 (""),则 FIND 会匹配搜索字符串中的首字符(即编号为 start_num 或 1 的字符)。
Find_text 不能包含任何通配符。
如果 find_text 未显示在 within_text 中, 则 FIND 和 FINDB 返回 #VALUE! 。
如果 start_num 不大于零, 则 FIND 和 FINDB 返回 #VALUE! 。
如果 start_num 大于 within_text 的长度, 则 FIND 和 FINDB 返回 #VALUE! 。
可以使用 start_num 来跳过指定数目的字符。 以 FIND 为例,假设要处理文本字符串“AYF0093.YoungMensApparel”。 若要在文本字符串的说明部分中查找第一个“Y”的编号,请将 start_num 设置为 8,这样就不会搜索文本的序列号部分。 FIND 从第 8 个字符开始查找,在下一个字符处找到 find_text,然后返回其编号 9。 FIND 始终返回从 within_text 的起始位置计算的字符编号,如果 start_num 大于 1,则会对跳过的字符计数。
示例
复制下表中的示例数据,然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果,请选中它们,按 F2,然后按 Enter。 如果需要,可调整列宽以查看所有数据。
数据 |
||
---|---|---|
Miriam McGovern |
||
公式 |
说明 |
结果 |
=FIND("M",A2) |
单元格 A2 中第一个“M”的位置 |
1 |
=FIND("m",A2) |
单元格 A2 中第一个“M”的位置 |
6 |
=FIND("M",A2,3) |
从单元格 A2 的第三个字符开始查找第一个“M”的位置 |
8 |
数据 |
||
---|---|---|
Ceramic Insulators #124-TD45-87 |
||
Copper Coils #12-671-6772 |
||
Variable Resistors #116010 |
||
公式 |
描述(结果) |
结果 |
=MID(A2,1,FIND(" #",A2,1)-1) |
提取单元格 A2 中从第一个字符到“#”的文本 (Ceramic Insulators) |
Ceramic Insulators |
=MID(A3,1,FIND(" #",A3,1)-1) |
提取单元格 A3 中从第一个字符到“#”的文本 (Copper Coils) |
Copper Coils |
=MID(A4,1,FIND(" #",A4,1)-1) |
提取单元格 A4 中从第一个字符到“#”的文本 (Variable Resistors) |
Variable Resistors |
本文介绍 Microsoft Excel 中 MINUTE 函数的公式语法和用法。
说明
返回时间值中的分钟。 分钟是一个介于 0 到 59 之间的整数。
语法
MINUTE(serial_number)
MINUTE 函数语法具有下列参数:
Serial_number 必需。 一个时间值,其中包含要查找的分钟。 时间值有多种输入方式:带引号的文本字符串(例如 "6:45 PM")、十进制数(例如 0.78125 表示 6:45 PM)或其他公式或函数的结果(例如 TIMEVALUE("6:45 PM"))。
备注
时间值为日期值的一部分,并用十进制数表示(例如 12:00 PM 可表示为 0.5,因为此时是一天的一半)。
示例
复制下表中的示例数据,然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果,请选中它们,按 F2,然后按 Enter。 如果需要,可调整列宽以查看所有数据。
时间 |
||
---|---|---|
下午 12:45:00 |
||
公式 |
说明 |
结果 |
=MINUTE(A2) |
返回 A2 中时间值的分钟部分。 |
45 |
本文介绍 Microsoft Excel 中 CODE 函数的公式语法和用法。
说明
返回文本字符串中第一个字符的数字代码。 返回的代码对应于本机所使用的字符集。
操作环境 |
字符集 |
---|---|
Macintosh |
Macintosh 字符集 |
Windows |
ANSI |
CODE(text)
CODE 函数语法具有以下参数:
Text 必需。 要为其获取第一个字符的代码的文本。
示例
复制下表中的示例数据,然后将其粘贴进新的 Excel 工作表的 A1 单元格中。 要使公式显示结果,请选中它们,按 F2,然后按 Enter。 如果需要,可调整列宽以查看所有数据。
公式 |
说明 |
结果 |
---|---|---|
=CODE("A") |
显示 A 的数字代码 |
65 |
=CODE("!") |
显示 ! 的数字代码 |
33 |
关联标签:
导读索引
- 如何使用excel快速获取出现最多的数值,MODE.SNGL函数实用技巧
- 如何使用excel的ADDRESS函数快速获取单元格地址
- 如何使用excel KURT函数快速获取一组数据的峰值
- 利用excel怎么获取线性回归线斜率,SLOPE函数使用技巧
- excel怎么获取网页实时数据,RTD函数怎么用
- excel如何快速获取整组数据中的最小值,SMALL函数怎么用
- excel如何快速获取单元格数值,CHOOSE函数怎么用
- excel如何根据员工ID快速获取员工姓名,XLOOKUP函数使用技巧
- excel如何根据条件快速获取指定单元格内容,INDEX函数使用技巧
- excel SUMPRODUCT函数使用技巧,如何获取数组乘积之和
- excel CELL函数使用技巧,快速获取单元格,地址,值,文件路径
- 如何使用excel快速获取指定数据在单元格哪个位置
- 如何使用excel快速获取时间的分钟数
- 如何使用excel获取数据的数字代码