数据分析之EXCEL函数【精华篇】

数据分析之EXCEL函数【精华篇】

这篇文章也很好，查阅方便，实用性更强。

将函数分为

• 清洗处理类
• 关联匹配类
• 逻辑运算类
• 计算统计类
• 时间序列类

本文仅是自己观看视频记录的笔记。视频来源 /?p=23

1.公式与引用

引用有

绝对引用（添加美元符号$，

=$A1，仅对单元格进行绝对引用，

=$A$1，对数值进行绝对引用，F4）

相对引用（拖动时，引用的数据会伴随拖拽发生变化）

 

2.函数与sum 函数

将表格转化为普通区域（右击—表格—转为区域）

Sum（） 具有忽略文本的特性，文本和为0

Average（） tab键选择确认函数，不是回车

 

3.条件求和 Sumif & Sumifs

= sumif（C2：C8，”>20”）

               求和区域  条件

= sumif（B2：B8，”电脑”，C2：C8）

               条件区域  条件  求和区域

对一组指定条件的单元格进行求和

= sumifs （C2：C8，A2：A8，E2，B2：B8，F2）

               求和区域，条件1区域，条件1，条件2区域，条件2

 

表达式：SUMIF(range, criteria, [sum_range])

中文表达式：SUMIF(根据条件进行计算的范围, 求和条件, [实际求和范围])

 

表达式：SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

中文表达式：SUMIFS(求和区域，条件区域1，条件1，[条件区域2，条件2]，...)

 

2019/5/15   输入日期，2019-5-15，回车

设置单元格格式快捷键 Ctrl+1

 

4.DSUM

5.DSUM

=Dsum(数据区域，求和的列数,条件区域)

= DSUM（A:C，3，E1:F2）

       数据源，必须包含标题；返回哪一列的数据；条件区域，也必须包含标题

= DSUM（A:C，3，$E$1:F2）

                 将条件标题绝对引用，保证了条件区域包含标题。但是此时向下拖拽得到的数值是求和的值，要减去除了结果那一行的所有值的和

=DSUM(A:C,3,$E$1:F3)-SUM($F$1:G2)

 

6.SUM与通配符

（1）公式的跨表计算

单元格跨表引用基本结构   = ‘2月’！C2    [表！单元格]

跨表求和某一列  = sum（‘2月’！C：C）   SUM（表！求和区域）

（2）利用shift 和 sum进行多表汇总

对一月到六月，共六张表的C列进行汇总

= sum（‘1月：6月’！C：C）

       点选1月，shift，点选6月

（3）使用通配符进行多表汇总

= sum（‘*月’！C：C）

      所有以月结尾的表，除当前所在的表

= sum （‘*’！C：C）

 

7.Indirect函数进行多表汇总

= indirect 转化文本字符串为引用，间接的方式进行引用

首先要构造符合引用规则的文本字符串，然后直接转化为引用

= a1 , 输出 a1   直接引用

= indirect （a1），输出 月份   间接引用

输入连字符&，可以将字符连接

构造字符串  C = A2&”!C:C”

对符合规则的字符串求值  D= SUM（indirect（C2））

直接嵌套求值为 =