Excel使用 绝对引用和相对引用 函数

ctrl和拖动，可以递增

直接拖动，可以复制

 

单元格的相对引用和绝对引用，默认是相对引用

绝对引用$I$2，$I固定第I列，$2固定第2行

A$1 行变列不变

$A1 列变行不变

F4切换相对引用，单元格绝对引用，行绝对引用，列绝对引用。

 

移动到某一列的右侧，出现了<-|->的时候，双击，列宽就是最合适列宽

列宽单独设置，选中需要设置的列，然后在开始选项卡的单元格样式那边，选择

 

跨列居中，选择某一行的多个单元格，然后设置单元格格式中。水平对齐，选择跨列居中。

跨列居中没有破坏表格的行列结构,选中合并其中的单元格就能看到,只是改变了显示效果,而合并居中操作破坏了表格的行列结构。
当你移动上图两种不同合并操作的表格时,就会发现合并居中单元格需要重新进行合并操作,而跨列居中操作的就不需要再次合并。
 
 
插入图表的时候需要注意：
如果两列数据作为横坐标的话，需要先选。然后再一次性选择第三列
 
 
黑色十字架拖动，也可以双击，然后会自动填充剩下的列。手动拉不靠谱，手动拉，新增一行，那一列不会有这个效果。
 
公式
=IF(C1>5,"Y","N")  后面是文本的话，需要添加英文的双引号。如果是数字的话，可以直接写
=A1=A2  这里返回true或者false
=A1=A3
 

IF 函数

IF 函数是 Excel 中最常用的函数之一，它可以对值和期待值进行逻辑比较。

因此 IF 语句可能有两个结果。 第一个结果是比较结果为 True，第二个结果是比较结果为 False。

例如，=IF(C2=”Yes”,1,2) 表示 IF(C2 = Yes, 则返回 1, 否则返回 2)。

 
 

将 IF 函数与 AND、OR 以及 NOT 函数配合使用

AND函数里面是多个条件，

=IF(AND(A2>0,B2<100),TRUE, FALSE)

如果 A2 (25) 大于 0，并且 B2 (75) 小于 100，则返回 TRUE，否则返回 FALSE。 在本例中，两个条件都为 TRUE，因此返回 TRUE。

=IF(OR(A4>0,B4<50),TRUE, FALSE)

如果 A4 (25) 大于 0，或者 B4 (75) 小于 50，则返回 TRUE，否则返回 FALSE。 在本例中，只有第一个条件为 TRUE，但由于 OR 只要求一个参数为 TRUE，因此公式返回 TRUE。

 

VLOOKUP 函数

当需要按行查找表格或区域内容时，请使用 VLOOKUP。 例如，按部件号查找汽车部件的价格，或根据员工 ID 查找员工姓名。

在这一最简单的形式中，VLOOKUP 函数表示：

=VLOOKUP (要查找的项、要查找位置、区域中包含要返回的值的列号、返回近似匹配或精确匹配 - 指示为 1/TRUE 或 0/FALSE) 。

 

 

如何开始

您需要四条信息才能构建 VLOOKUP 语法：

  1. 要查找的值，也被称为查阅值。

  2. 查阅值所在的区域。 请记住，查阅值应该始终位于所在区域的第一列，这样 VLOOKUP 才能正常工作。 例如，如果查阅值位于单元格 C2 内，那么您的区域应该以 C 开头。

  3. 区域中包含返回值的列号。 例如，如果指定 B2：D11 作为区域，则应该将 B 计为第一列，将 C 作为第二列，以此类比。

  4. （可选）如果需要返回值的近似匹配，可以指定 TRUE；如果需要返回值的精确匹配，则指定 FALSE。 如果没有指定任何内容，默认值将始终为 TRUE 或近似匹配。

现在将上述所有内容集中在一起，如下所示：

=VLOOKUP (查找值、包含查找值的范围、包含返回值的范围中的列号、近似匹配 (TRUE) 或精确匹配 (FALSE) ) 。

 

VLOOKUP function

英文版的配图和说明，能对应上

  1. The range where the lookup value is located. Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell C2 then your range should start with C.

=VLOOKUP (B3,B2:E7,2,FALSE) VLOOKUP looks for Fontana in the first column (column B) in the table_array B2:E7, and returns Olivier from the second column (column C) of the table_array. False returns an exact match.

 

业绩 奖金
0

0
1500

1000
2000

5000

 近似匹配的时候，800匹配第一列的时候，是匹配到0.而不是1500。左边是当成层次来使用的。

800超过0，但是没有达到1500。近似匹配的时候，查找的列，需要升序排列

 

HLOOKUP function

 

 

MATCH 函数

使用 MATCH 函数在 范围 单元格中搜索特定的项，然后返回该项在此区域中的相对位置。 例如，如果 A1:A3 区域中包含值 5、25 和 38，那么公式 =MATCH(25,A1:A3,0) 返回数字 2，因为 25 是该区域中的第二项。

语法

MATCH(lookup_value, lookup_array, [match_type])

MATCH 函数语法具有下列参数：

  • lookup_value    必需。 要在 lookup_array 中匹配的值。 例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。

    lookup_value 参数可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。

  • lookup_array    必需。 要搜索的单元格区域。

  • match_type    可选。 数字 -1、0 或 1。 match_type 参数指定 Excel 如何将 lookup_value 与 lookup_array 中的值匹配。 此参数的默认值为 1。

下表介绍该函数如何根据 match_type 参数的设置查找值。

Match_type

行为

1 或省略

MATCH 查找小于或等于 lookup_value 的最大值。 lookup_array 参数中的值必须以升序排序，例如：...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE。

0

MATCH 查找完全等于 lookup_value 的第一个值。 lookup_array 参数中的值可按任何顺序排列。

-1

MATCH 查找大于或等于 lookup_value 的最小值。 lookup_array 参数中的值必须按降序排列，例如：TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ... 等等。

  • MATCH 返回匹配值在 lookup_array 中的位置，而非其值本身。 例如，MATCH("b",{"a","b","c"},0)返回 2，即“b”在数组 {"a","b","c"} 中的相对位置。

  • 匹配文本值时，MATCH 函数不区分大小写字母。

  • 如果 MATCH 函数查找匹配项不成功，它会返回错误值 #N/A。

  • 如果 match_type 为 0 且 lookup_value 为文本字符串，您可在 lookup_value 参数中使用通配符 - 问号 (?) 和星号 (*) 。 问号匹配任意单个字符；星号匹配任意一串字符。 如果要查找实际的问号或星号，请在字符前键入波形符 (~)。

match也可以拼接2个单元格的value，比如A1&"-"&A2。  =MATCH(D5&"-"&C5,A5:A8,0) 这个例子，把D5和C5单元格中的value使用-进行连接，得到一个新的字符串。

 

Index函数

语法

INDEX(array, row_num, [column_num])

INDEX 函数的数组形式具有下列参数：

  • array    必需。 单元格区域或数组常量。

    • 如果数组仅包含一行或一列，则相应的row_num或column_num参数是可选的。

    • 如果数组具有多行和多列，并且row_num或 column_num，INDEX 返回数组中整个行或列的数组。

  • row_num    必需，除非column_num存在。 选择数组中的某行，函数从该行返回数值。 如果row_num，column_num参数。

  • column_num    可选。 选择数组中的某列，函数从该列返回数值。 如果column_num，row_num参数。

 

 

公式菜单，有一个名称管理器。可以给某一个区域，取名。后续可以直接引用。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 
 
 
posted @ 2022-05-07 10:15  ChuckLu  阅读(1561)  评论(0)  编辑  收藏  举报