EXCEL初级入门--（第四章 函数进阶学习）-中

文章目录

• • (十四) MatchVlookup应用对比
  • • Match
  • (十五) IndexMatch多条件应用案例
  • • Index
  • (十六) IndexMatch数组嵌套
  • • Index Match
  • (十七) 唯一Subtotal唯一的筛选函数
  • • Subtotal
  • (十八) Sumproduct函数应用
  • • Sumproduct
  • (十九) 条件求和函数
  • • 1、sum
    • 2、sumif
    • 3、sumifs
  • (二十) 条件计数函数应用案例
  • • 1、counta
    • 2、count
    • 3、countif
    • 4、countifs
    • 5、countblank
  • (二十一) 活用表格定义名称的引用
  • • 表格的应用
  • (二十二) 替换函数应用案例
  • • 1、replace
    • 2、substitute
  • (二十三) 数值位数处理函数应用
  • • 1、round
    • 2、 roundup
    • 3、rounddown
    • 4、int
    • 5、floor
    • 6、 ceiling
  • (二十四) OffsetCounta函数应用案例
  • • offset
  • (二十五) 数据有效条件格式保护锁定
  • • 保护单元格步骤
  • (二十六) VlookupLookup正反向模糊匹配
  • • 1、正向模糊匹配查找
    • 2、逆向模糊匹配

(十四) MatchVlookup应用对比

Match

返回特定位置的值
语法结构：'=MATCH(lookup_value,lookup_array,match_type)

(十五) IndexMatch多条件应用案例

Index

语法结构：'=INDEX(reference,row_num,column_num,area_num)
referenceL:给定的区域
row_num:确定的行
column_num:确定的列
area_num:第几个区域

(十六) IndexMatch数组嵌套

Index Match

作用：弥补Vlookup在查询匹配中的一些缺陷
语法结构：'=INDEX(reference,row_num,column_num,area_num)
Match结果是两个条件连接到一起，所以结果必须按：Ctrl+Shift+回车

(十七) 唯一Subtotal唯一的筛选函数

Subtotal

筛选函数，本质属于分类汇总
语法结构：'=SUBTOTAL(function_num,ref1,...)
function_num：类型
ref1：区域

(十八) Sumproduct函数应用

Sumproduct

语法结构：'=sumproduct(array1,array2,array3,...)
'array:区域
常用语法：'=sum(条件1*条件2*条件3)

(十九) 条件求和函数

1、sum

简单求和
语法结构：'=sum(number1,number2,...)
'number1,代表一个个数值或区域，文本代表0

2、sumif

sumif指定条件求和
语法结构：'=sumif(range,criteria,sum_range)
range：条件所在的区域
criteria：条件
sum-range：求和区域

3、sumifs

sumifs指定多条件求和
语法结构：'=SUMIFS(sum_range,criteria_range,criteria,...)
sum-range：求和区域
criteria-range：条件区域
criteria：条件

(二十) 条件计数函数应用案例

1、counta

=COUNTA(value1,value2,...):非空单元格的个数
value1:单元格或单元格区域

2、count

=count(value1,value2,...):数值的个数
value1:单元格或单元格区域

3、countif

=countif(range,criteria):条件计数
range:所选的单元格区域
criteria:条件

4、countifs

=COUNTIFS(criteria_range,criteria):多条件计数

5、countblank

=COUNTBLANK(range):计算空单元格的数量

(二十一) 活用表格定义名称的引用

表格的应用

选中区域-插入-表格-确定-进行修改替换格式

(二十二) 替换函数应用案例

1、replace

替换函数，把指定位置的值替换掉
语法结构：=REPLACE(old_text,start_num,num_chars,new_text)
old-text：查找的单元格
start-num：从第几位开始
num-char：替换几位
new-text

2、substitute

替换函数，把指定值的替换掉。
语法结构：'=substitute(text,old_text,new_text,instance_num)
text:所查找的单元格或值
old-text:要替换的旧字符
new-text:替换后的新字符
instance-num:替换第几次出现的旧字符。省略，表示全部替换

(二十三) 数值位数处理函数应用

1、round

按指定位数四舍五入
语法结构：'=ROUND(number,num_digits)
如果 num_digits 大于 0（零），则将数字四舍五入到指定的小数位
如果 num_digits 等于 0，则将数字四舍五入到最接近的整数
如果 num_digits 小于 0，则在小数点左侧前几位进行四舍五入

2、 roundup

向上舍入
语法结构：'=ROUNDUP(number,num_digits)

3、rounddown

向下舍去
语法结构：'=ROUNDDOWN(number,num_digits)

4、int

向下取整
语法结构：'=INT(number)

5、floor

向下舍入最接近基数的倍数
语法结构：'=FLOOR(number,significance)

6、 ceiling

向上舍入最接近基数的倍数
'=CEILING(number,significance)

(二十四) OffsetCounta函数应用案例

offset

=offset(reference,rows,cols,height,width)
reference：单元格或区域
rows：偏移行，可以正负
cols：偏移列，可以正负
height：返回的行数
width：返回的列数

(二十五) 数据有效条件格式保护锁定

保护单元格步骤

全选整个工作表
单元格格式设置锁定，去勾
选定要锁定的区域单元格格式加勾
审阅，保护工作表

(二十六) VlookupLookup正反向模糊匹配

1、正向模糊匹配查找

语法结构：'=VLOOKUP(查找对象,查找区域,列数,查找类别)

2、逆向模糊匹配

语法结构：'=LOOKUP(1,0/(find(条件区域，条件),计算区域))