|
1、excel使用函数前须知： F1键：获取帮助；F4键：切换相对引用和绝对引用；锁定行：=A$1；锁定列：=$A1；锁定行和列：=$A$1；拼接：&
2、excel基本聚合函数：count、sum、average、max、min
3、条件if、拼接&、左截取left、右截取right
4、首字母大写proper、查找元素位置find
5、Excel常用日期时间周期处理函数公式字符串日期转化为标准格式：=--TEXT(A2,''0-00-00'') 根据时间提取年月：=YEAR(A1)&"年"&MONTH(A1)&"月" 根据时间算出星期几（中文）：=TEXT(A1,"aaaa") ， ="星期"&WEEKDAY(A1,2) 根据时间算出星期几（英文）：=TEXT(A1,"dddd") 根据时间返回一年的周数：=WEEKNUM(A1) 根据时间返回一年的季度值：=INT((MONTH(A2)+2)/3) ， =IF(MONTH(A2)<=3,1,IF(MONTH(A2)<=6,2,IF(MONTH(A2)<=9,3,4)))
6、排名rank、截取mid、分组排名sumproduct=RANK(D2,$D$2 ：$D$6,0) #排名 mid(截取的字符串,截取的起始位置,截取长度) SUMPRODUCT(($F$2 ：$F$6=F2)*($D$2 ：$D$6>D2))+1 #分组排名
7、累计求和、累计数量、分组累计求和、分组累计数量、排名、分组排名累计：要点就是把起始位置锁死，其他位置随着拖拽位置变化而变化。 下图案例： 累计求和=SUM($D$2：D2)，下拉填充公式即可 累计次数=COUNT($A$2：A2)，下拉填充公式即可 分组--累计--求和=SUMIFS($D$2：D2,$B$2：B2,B2)，下拉填充公式即可 分组--累计--计数=COUNTIFS($B$2：B2,B2)，下拉填充公式即可
8、条件聚合：countifs、sumifs 、averageifs所有单条件聚合的函数···if()都可以用多条件聚合函数···ifs()代替。 COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…) COUNTIFS 函数语法具有以下参数： criteria_range1 必需。在其中计算关联条件的第一个区域。 criteria1 必需。条件的形式为数字、表达式、单元格引用或文本，它定义了要计数的单元格范围。例如，条件可以表示为 32、">32"、B4、"apples"或 "32"。 criteria_range2, criteria2, ... 可选。附加的区域及其关联条件。最多允许 127 个区域/条件对。
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
9、多表关联匹配基本方法vlookup=VLOOKUP（要查找的值、要在其中查找值的区域、区域中包含返回值的列号、精确匹配或近似匹配 – 指定为 0/FALSE 或 1/TRUE）
10、INDEX+MATCH实现多表关联匹配=INDEX($E$1 ：$F$7,MATCH(A2,$E$1 ：$E$7,0),2) 解释：INDEX(要返回值的单元格区域或数组, 返回值所在的行号, 返回值所在的列号) 其中返回值所在的行号用：match(目标值, 查找区域, 0/1/-1匹配方法)。 如：在第2个表中匹配第1个表中的语文成绩，INDEX(要返回值的单元格区域或数组为：A1:B6, 行号：MATCH(目标值：A10,查找区域A1:A6,匹配方式：0), 列号: 2)
文章源于公众号：Python数据分析运营
| 
