[Excel/Word]常用函数与技巧

1 Excel

case1 同时多列筛选

同时筛选多列: 选中首行(属性行)>筛选>(筛选目标的N列)

case2 IF/OR/AND/COUNTIF语句

=IF(condition,condition=true的值,condition=false的值)

=IF(OR(A1=5, A1=6),"土", IF(OR(A1=7,A1=8),"金", IF(OR(A1=9,A1=0),"水","其它值")))

=IF( B15="",  "尚未填值",  
    IF( AND(B15>=0, B15<4),"低", 
        IF( AND(B15>=4,B15<7),"中", 
            IF( AND(B15>=7,B15<=10),"高",
                "Unknown Value"
            )
        )
    )
)

=IF(A1="OK",A3,"")

=COUNTIF(A6:A100,"<>") 【统计选中(行/列)区域内，不为空的表格数】

case3 正则表达式提取文本内容

(目前没有实测成功，据其他博文的说法是，需要安装'excelapi'一类的依赖工具)

=RegexString(B13,Rule, index)

=RegexString(A9,"([\dA-Z]{5,20})",1)

case4 每一行批量拼接字符串 & 拼接双引号/单引号

参考文献

  • 拼接双引号/单引号的方法

假如有2个单元格(a2，b2)，其内容分别是 你好 和 世界
现在我需要用一个公式在c2中显示'你好，世界~'
方法是 ="'"&a2&"，"&b2&"'"

总结:要让其显示双引号，就连续写上4个英文双引号，要显示1个单引号，只要在两个双引号之间真接加一个单引号就可以了。

case5 日期转换与处理： 时:分:秒 => 秒(总数)

  • 方式1及其要点
    • VALUE(TEXT(E5,"[s]")): 将E5格子的时间文本(h:mm:ss，Eg: '00:04:45')转换为秒数（s）
      • TEXT【字符串文本类型转换函数】将数值转换为按指定数字格式表示的文
      • Value【数值型转换函数】 为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用
  • 方式2及其要点
    • HOUR(B75) 获取字符串的小时数 / MINUTE(B75) 获取字符串的分钟数 / SECOND(B75) 获取字符串的秒针数
    • =HOUR(B75)*3600+MINUTE(B75)*60+SECOND(B75)

case6 excel如何利用if函数判断【非空值】/【非空值】/【#NA】/【#VALUE】

参考文献

运算 Excel的实现 Excel的示例
等于 = 等于空 A2=""
不等于 <> 不为空 A2<>""
字符串拼接 & 字符串拼接 A2&B2 
#VALUE! : 值错误
  情况1：当公式需要数字或逻辑值（例如TRUE/FALSE）时，却输入了文本。（解决方法：确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值）
  情况2：输入或编辑了数组公式后按了Enter。（解决方法：选定包含数组公式时应通过F2编辑公式后按Ctrl+Shift+Enter）
  情况3：将单元格引用、公式或函数作为数组常量输入。（解决方法：确认数组常量不是单元格引用、公式或函数）
  情况4：为需要单个值（而不是区域）的运算符或函数提供了区域（解决方法：将区域更改为单个值）
  情况5：在某个矩阵工作表函数中使用了无效的矩阵（解决方法：修改无效矩阵，确认矩阵的维数对矩阵参数是正确的）


#REF! : 引用错误

#NULL! : 交叉运算符（空格）是为了防止区域求和时发生重复计算而引入的 ==> ISNULL(A1) : 判空


#NAME? : 名称错误
  情况1. 名称公式拼写错误
  情况2. 公式中的文本子未添加双引号
  情况3. 区域引用缺少冒号
  情况4. 引用未定义名称
  情况5. 引用定义名称出现拼写错误

#N/A : 错误值出错 ==> ISNA(A1)
  1. 查找区域不存在查找值
  2. 数据类型不匹配
  3. 查找数据源引用错误
  4. 引用了返回值为#N/A的函数或公式

延申:

  • ISERR(A1) / ISERROR(A1) /
  • MATCH(555,$H\(8:\)H$21,)

case7 格式化日期字符串 : TEXT(, "yyyyMMddHHmmss")

TEXT(<dataItem>, "yyyyMMddHHmmss")

case8 插入/拼接特殊符号(英文双引等)

  • 场景1：插入双引号
  • 方法：转义法
="这是" &CHAR(34) & "完美Excel" & CHAR(34) & "公众号"

特殊字符对应的反转义数字编号

依据: ASCII码表
双引号" = 34 / 反引号 = 96

case9 MATCH函数：基于匹配，判断某个元素值在另一列表中是否存在？

  • 参考文献
  • 函数语法

MATCH(lookup_value, lookup_array, [match_type])

  • 第1个参数：必选参数，需要查找的单元格；如：A2
  • 第2个参数：必选参数，要搜索的单元格区域；如：C2:C8
  • 第3个参数：可选参数，精确匹配(0)或模糊匹配(1)；如：0
  • 案例：【案例1】查出完全匹配的成绩，对应的名次：第7名；
=MATCH(E2,C2:C8,0)

  • 案例：【案例2】查出指定元素x是否在列表List中是否存在？
=IF( 
    ISNUMBER( MATCH(A2, F2:F500, 0) )
    , "存在", "不存在"
)

注：A2、F2:F500 可替换为 $A$2、$F$2:$F$500或F:F(即 F 列)

case10 保持多变量函数中第N个参数随行而变，其他参数值保持不变

  • 样例问题
按照如下文本规律，将A4一直递增至A10：
"=MATCH(match_result!A4, bigdata_ck_latest!A2:A100000, 0)
=MATCH(match_result!A5, bigdata_ck_latest!A2:A100000, 0)
=MATCH(match_result!A6, bigdata_ck_latest!A2:A100000, 0)"
  • 解决方法
  • 第1个参数随当前行而变（利用：INDIRECT("{Column}" & ROW() ) 函数
  • 第2、3个参数不动（利用: $Column$y）

sample 1

sample 2

=INDIRECT("bigdata_ck_latest!M" & "805")
=IF(INDIRECT("H" & ROW()) = "存在", INDIRECT("bigdata_ck_latest!M" & "805") , "--")
=IF(INDIRECT("H" & ROW()) = "存在", INDIRECT("bigdata_ck_latest!M" & "804" + 1) , "--")
=IF(INDIRECT("H" & ROW()) = "存在", INDIRECT("bigdata_ck_latest!M" & G6 + 1) , "--")
=IF(INDIRECT("H" & ROW()) = "存在", INDIRECT("bigdata_ck_latest!M" & INDIRECT("G" & ROW()) + 1) , "--")
=IF(INDIRECT("H" & ROW()) = "存在", INDIRECT("bigdata_ck_latest!M" & INDIRECT("G" & ROW()) + 1 ), "--")

2 Word

case1 Word每次保存时异常缓慢

关掉COM加载项，问题就解决了。

文件-->选项-->加载项-->COM加载项-->转到 ： 取消所有勾选

X 参考文献

Ctrl + H 
  > 特殊格式: 图 
    > 格式: 段落-居中 
      > 全部替换
---------------------- 
[延申] 通配符大法
posted @ 2020-10-22 15:54  千千寰宇  阅读(741)  评论(0)  编辑  收藏  举报