Excel知识

 小数据量，3万左右，都可以用excel。

函数：

文本函数

 ⚠️函数可以嵌套。

关联匹配函数(ecel叫查找和引用)

• lookup
• vlookup
• column() , 和row()都是返回当前行号/列号，如果指定参数，则返回参数指向的行列号。
• index
  • 1. INDEX(reference, row_num, [column_num], [area_number])  ,
       • 框出一个二维行列矩阵（也可以一维），然后根据行/列号返回对应的单元格的值⚠️从1计算。
       • 如果reference是2个矩阵，使用元祖作为第一个参数：
         • 首先，在一个单元格输入=index(()) ⚠️这里是双括号，因为第一个参数是元组，包括多个矩阵。
         • 然后，在表格上框出一个矩阵，然后输入逗号，再次框出一个矩阵。
         • 最后，输入后面的3个参数。area_number是指定第一个参数中的哪个矩阵。
    2. index(array, row_num, [column_num])
    • • array, 用法和第一种类似，array是二维的，行/列号，是这个数值的行列号，也是从1开始。
• offset
• match(查询值, 查询范围, 匹配方式)。输入0，完全匹配。 返回行号。
• hyperlink

逻辑运算

 TRUE, FALSE.

• =A2=”水果”， 如果A2单元格的值是”水果”，返回TRUE。

 if(条件，真则返回这里的值，假则返回这里的值)， 用法和python类似。⚠️可以嵌套，但不要写太复杂的逻辑。

is系列函数：

• =is，然后出现下拉框，选择需要的即可。

计算统计函数

⚠️（很多函数都有衍生函数）

sum, max,min,

count(区域) ，计算区域中有多少单元格的值包含数字。

countif， 用于条件计数。

• =COUNTIF(B4:B6, “>10”)
• ⚠️还有诸如sumif等函数。条件+运算

rank: 计算排名。

rand(), 返回0～1之间的随机数。

averagea, 

• ⚠️多了一个a, 代表all，全部。
• 返回所有参数的算术平均值。字符串和 FALSE 相当于 0；TRUE 相当于 1。参数可以是数值、名称、数组或引用

int , 取整数。

round, 四舍五入

stdev， 就是计算样本的标准差（也叫标准偏差）

quartile， 计算四分位数。

sumproduct()  :

• 函数返回对应的区域或数组的乘积之和。 默认运算是乘法，但加、减和除也可能。
•  = SUMPRODUCT （C2： C5，D2： D5）
• 解释：C 列中的每个单元格乘以列 D 中同一行的相应单元格的值，并将4个值相加。
• =SUMPRODUCT((A2:A6=4.14)* C2:C6)
  • A列的每个值和4.14比较，相等的返回1，不等的返回0，然后这个数组[1, 0,1,0]再和C列对应的值相乘，最后将结果相加。

技巧1-快捷键：

2.Ctrl+Option(alt)+Return：一个单元格中开始新的一行

3. ctrl(cmd) + shift + 方向键，

1. 第一种：可以快速框选（一列/行）从当前单元格开始，直到最后一个有数据的单元格。
2. 第二种：如果当前单元格（方向键方向）后面的单元格是空的，那么直接框选到表格的底部。

4.对角线框选：按住Shift键再选择另一个单元格

5.按住cmd⌘键可以实现不连续选择

6. cmd + 方向箭，光标快速移动。 ⚠️用法类似cmd+shift+方向键。

⚠️mac上一般用cmd代替ctrl。

技巧2-操作：

1 分列：

选择一列>导航栏上的“数据”>“分列”, 然后就可以根据自定义的符号把列拆分。

1.1删除重复值

选择一列>导航栏上的“数据”>“删除重复值”

用途：遇到公式id, 用户id，可以用这个功能进行简单的快速检查。

2 “数据条”和“色阶”：

选择一列>菜单上的“开始”>“条件格式”/“色阶”

“数据条”可以为类型是数值的列，提供可视化支持。比如数据条：根据数值大小：产生类似横bar。

3 选择性粘贴：可以粘贴公式，值。

如果a列是通过公式得到的计算值，现在只想保留值，以便后续计算。

1. 复制列

2. 右击出现下拉菜单，选择“选择性粘贴”，选择值，确定。

4.数据透视表+切片器

导航条“插入”->”数据透视表” 。 把数据分组。

index， columns都可以分成1级，2级，多级。和pandas的privot_table用法类似。(点击看文档)

> table = pd.pivot_table(df, values=\'D\', index=[\'A\', \'B\'],
                    columns=[\'C\'], aggfunc=np.sum)

• 还可以加上更多的切片器。
• 可以加上图表，可以做动态分析报表，但更推举使用PI做报表，或者使用pandas/pyecharts做动态化分析。 
• 插入中的“应用商店”有大量的第三方软件可以利用。

5. 定义名称 （导航栏“公式”）

选定一块数据区域，给这块数据区域一个别名。类似编程中定义一个变量名，并分配数据。别名可以应用到函数中。还可以制作下拉菜单。

选择一个区域->excel2016导航栏“数据”->“数据验证”->弹出对话框：

6. 迷你图

用报表较多，用迷你图更方便。

7. 数据筛选 

为表格的表头列增加下拉框，对值进行筛选。

导航栏“开始”>“排序与筛选”->“筛选” 

8. $符号的作用 

在单元格上使用$表示 绝对值引用：

• 　　1、列的绝对引用
• 　　2、行的绝对引用
• 　　3、行列的绝对引用

　　比如，C1单元格的公式为：=$A1+$B1；那么，单元格的引用为列的绝对引用；

　　比如，C1单元格的公式为：=A$1+B$1；那么，单元格的引用为行的绝对引用；

　　比如，C1单元格的公式为：=$A$1+$B$1；那么，单元格的引用为行列的绝对引用，即使用A1单元格的数据+B1单元格的数据。

　  ⚠️关于单元格地址的行列的认识，比如B3单元格，B是指列，3指的是行号，因此，一个单元格的地址，是列名＋行号。

9⚠️

排序前，去掉公式，筛选，改成数值。可以把表复制到一张新表。

技巧3-问题： 

问：一张表有1万行，A列是数值，希望在B列对A列的数值进行数学运算，当在B1写好公式后，如果选择下拉的方式，1万行要拖动鼠标很久。

是否有更方便的操作。让B列的公式对应A列的1万个单元格的值？

答案：

1. 在B1设置好公式后，复制。
2. 点击”A1″,          用cmd +