12-Excel技巧备忘录

1. Excel表格比较两列数据，找出重复项目写在当前单元格

=IF(ISERROR(MATCH(E1,$G$1:$G$1000,0)),"",E1)

1. MATCH函数：MATCH函数用于在指定范围内查找某个值并返回其相对于范围起始位置的位置。在这个公式中，MATCH(E1,$G1:1:G1000,0)的作用是在G列的范围1000,0)的作用是在G列的范围G1:1:G$1000中查找与单元格E1相匹配的值。第三个参数0表示精确匹配。

2. ISERROR函数：ISERROR函数用于检查其参数是否返回错误值。如果MATCH函数找不到与E1单元格匹配的值，它会返回一个错误值。因此，ISERROR(MATCH(E1,$G1:1:G$1000,0))这部分的作用是检查MATCH函数是否返回了错误值。

3. IF函数：IF函数根据指定条件返回不同的值。在这个公式中，IF(ISERROR(MATCH(E1,$G1:1:G$1000,0)),"",E1)的意思是如果MATCH函数返回错误值（即未找到匹配值），则返回空字符串""，否则返回E1单元格的值。

如果您想要在整个C列中查找匹配A1单元格的值，并返回C列中相应的值，可以使用以下公式：

=IFERROR(INDEX($C:$C, MATCH(A1, $C:$C, 0)), "")

1. MATCH(A1, C:C:C, 0)：MATCH函数用于在指定区域（这里是整个C列）中查找与A1单元格相匹配的数值，并返回该数值在区域中的位置。第三个参数0表示精确匹配。

2. INDEX(C:C:C, MATCH(A1, C:C:C, 0))：INDEX函数根据给定的行号或列号返回数组中的值。在这里，它使用MATCH函数返回的位置来从C列中提取相应的数值。

2. Excel表格比较两列数据，将第二列数据中与第一列数据重复的数值标黄

• 选中G列中的数据，点击Excel菜单中的“开始”（或“主页”），找到“条件格式设置”（或“条件格式规则管理”）。

• 在条件格式对话框中，选择“新建规则”。

• 在弹出的“新建格式规则”对话框中，选择“使用公式确定要设置格式的单元格”。

• 在“格式值”框中输入以下公式：

• =COUNTIF($E:$E, G1) > 0
  

  这个公式的含义是：如果G列当前单元格的数值在E列中存在重复，则应用该格式。

• 点击“设置格式”，选择想要的背景颜色，比如黄色，然后点击“确定”。

• 确认设置后，点击“确定”应用条件格式。

#规则：要设置的条件    格式：代表要体现的要求    应用于：代表规则应用的位置

=E1=1

表示：当E列的数值为1时，对应的D列单元格将变成设置的红色；当E列的数值为2时，对应的D列单元格将变成设置的黄色。其他情况下，D列单元格保持原样。

其中E1的1表示在第几行开始执行，当为1时，单元格前后存在对应关系，当为2时，规则的地方变成了H2行到H列，并且，当H2行为2时，D1行会变成黄色，当H3行为2时，D2行会变成黄色，如果H4行不为2，D3行无变化，以此类推。（个人实践得出，无相关理论支撑）

3. Excel表格有一列数据，计算出重复数据的个数

=COUNTIF(A:A, A2) #计算A列中A2格的

=COUNTIF(ok!H:H,"质量")  #作用于"ok"表格中的H列

=COUNTIF('sheet表'!F:F,A2)  #计算"sheet表"工作表的F列中等于当前sheet表A2单元格的数量

=COUNTIF(Sheet1!G:G,Sheet1!A2)  #计算在Sheet1工作表的G列中等于sheet1表中A2单元格的数量

=COUNTIFS(B:B,B2,E:E,E2) #统计同时满足B列和E列条件的数量

• 统计在A列中与单元格A2中的内容完全相同的单元格的数量。COUNTIF函数会扫描A列中的每个单元格，然后检查该单元格的内容是否与A2单元格相匹配。如果匹配，则计数器会递增1。最后，这个公式会给出与A2单元格内容相同的单元格数量

        #或者直接用excel自带的筛选功能可以计算出单列重复个数

4. Excel表格有两列存在对应关系的数据，有一列是前面中一列的乱序，需要自动填充对应关系

=VLOOKUP(D1, A:B, 2, FALSE)

• VLOOKUP：表示使用垂直查找来在指定范围内查找数值。
• D1：表示要查找的数值，即当前单元格中的数值，这里是d列当前行的车号数据。
• A:B：表示要在哪个范围内进行查找，这里是A列和B列，即车号和对应的重量数据。
• 2：表示如果在A列中找到与D1匹配的数值，就返回该行对应位置的第2列数据，即B列的重量数据。
• FALSE：表示精确匹配，只有在A列中找到与D1完全一样的数值时才返回结果

 

5. Excel表格将文字转换成固定格式的链接

=IF(ISBLANK(Sheet2!A1),"",HYPERLINK("https://jira.gs-robot.com/"&Sheet2!A1, Sheet2!A1))

• IF 函数是Excel中的条件函数，用于根据给定条件返回不同的值。语法为 IF(条件, 值为真时的结果, 值为假时的结果)。
• ISBLANK 函数用于检查某个单元格是否为空。如果被引用的单元格为空，则 ISBLANK 返回 TRUE，否则返回 FALSE。
• Sheet2!A1 表示对Sheet2工作表中的A1单元格的引用，即要判断和引用的单元格。
• HYPERLINK 函数用于创建超链接。在这个公式中，超链接的地址是"正在加载" 加上 Sheet2中A1单元格的内容，显示文本也为Sheet2中A1单元格的内容。
• 如果 ISBLANK(Sheet2!A1) 返回 TRUE，即A1单元格为空，那么公式会返回空字符串 ""，表示不进行任何操作。
• 如果 ISBLANK(Sheet2!A1) 返回 FALSE，即A1单元格不为空，那么公式会生成相应的超链接，跳转网址为"正在加载" 加上A1单元格的内容，并显示相同的内容作为链接文本。

6.  Excel表格计算数据各个区间个数

1. 低于10的数据个数：

=COUNTIF(Sheet2!A:A, "<10")

举例：=COUNTIF('Sheet20240416-20240422'!G:G,"<10")

2. 10到50之间的数据个数

=COUNTIF(Sheet2!A:A, ">=10") - COUNTIF(Sheet2!A:A, ">50")

举例：10 ≤ Num < 50
=COUNTIF('Sheet20240416-20240422'!G:G,">=10")-COUNTIF('Sheet20240416-20240422'!G:G,">49")
等于
=COUNTIF('Sheet20240416-20240422'!I:I,">9")-COUNTIF('Sheet20240416-20240422'!I:I,">19")

• COUNTIF('机器告警个数'!G:G, ">=10")：这部分是用来统计在"机器告警个数"工作表的G列中数值大于等于10的个数。COUNTIF函数的第一个参数是要统计的数据范围，这里是G列。第二个参数是设定的条件：“>=10”表示大于或等于10。

• COUNTIF('机器告警个数'!G:G, ">50")：这部分是用来统计在"机器告警个数"工作表的G列中数值大于50的个数。COUNTIF函数的第一个参数仍然是数据范围，第二个参数是新的条件设定：“>50”。

• 两个 COUNTIF 的结果相减：将第一个条件计数的结果减去第二个条件计数的结果，即可得到符合条件“10≤次数＜50”的数据量。

3. 大于50的数据个数

=COUNTIF(Sheet2!A:A, ">50")

举例：=COUNTIF('Sheet20240416-20240422'!G:G,">100")

7. 日期+时间转换成日期

2024/4/3 16:50:08   转换成   2024/4/3   

=INT(A1)

• 函数INT用于向下取整，将指定的数值向下取整为最接近的整数。当应用于日期或时间数值时，INT函数将会保留该日期或时间的整数部分（即日期部分），而忽略小数部分（即时间部分）。

如需做日期差值计算，只需要减法就行

=D2-E2

做空值校验

=IF(OR(H1="",G1=""),"",H1-G1)

对不同表格之间数据做计算

=IF(OR(Sheet1!H1="",Sheet1!G1=""),"",Sheet1!H1-Sheet1!G1)

• Sheet1!H1 和 Sheet1!G1 分别代表 Sheet1 工作表中的 H 列和 G 列的单元格。

• OR(Sheet1!H1="",Sheet1!G1="")：检查 Sheet1 工作表的 H1 和 G1 单元格是否有一个为空。如果其中一个单元格为空，则条件成立，返回空值("")。

• Sheet1!H1 - Sheet1!G1：如果 H1 和 G1 都包含日期值，那么这部分计算两个日期之间的天数差值。由于日期在Excel中以数字表示，相减得到的是天数的差值。

        综上所述，这个公式的作用是：如果 Sheet1 工作表中的 H1 和 G1 都包含日期值，则计算两

        个日期间的天数差；如果其中一个单元格为空，则结果为空值。