Excel知识

Excel知识

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

 

函数：

 

文本函数

  • Find(找什么值，从哪找个单元格找)， 返回要查找的值在单元格中的位置，type是数值。如果没有找到则返回#VALUE!
  • SUBSTITUTE(text, old_text, new_text)  ⚠️old_text，可以有text中没有的文本，会被忽视掉。
  • Left(取值文本,  取几位)
  • Right
  • Mid , 返回文本字符串中从指定位置开始的特定数目的字符
  • Text用于格式转化，直接在单元格转化即可。
  • Concatenate(text, text)  ⚠️也可以用&，代表合并。
  • Trim 去掉两边空格， 类似strip
  • Replace(old_text, start_num, 替换数量，替换文本) ，start_num从数值1开始。 一般直接使用替换窗口即可。
  • Len()

 ⚠️函数可以嵌套。

 

 

关联匹配函数(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 + 👇，       自动移动当前单元格到 “A10000”。 ⚠️A1列，如果中间有空格“单元格”，需要先把空格转化为0。
  3. 点击"B10000",  用cmd+shift + 👆，自动框选从"B1:B10000"
  4. 按右键，在弹出菜单>“选择性粘贴”，然后确定。

 

问：如何对数据进行描述分析？

答案：

  1. 数据清洗。去掉0数据，并筛选需要的数据。
  2. 把清洗后的数据，复制到一张新的表格。🌿重要。
    • 原因：不让后续的函数计算收到被清洗数据的影响。
    • 在原表被清洗的数据并没有从excel表中消失，只是被隐藏了
  3. 使用导航栏"数据"->“数据分析”。弹出多选框，选择“描述统计”，确定。
    • 弹出对话框，选择输入区域。确定。

 

posted @ 2020-03-16 08:13  Mr-chen  阅读(444)  评论(0)  编辑  收藏  举报