Excel笔记

Excel学习笔记

1. 数字序列自动生成1~n：在第一个格输入1，按住crtl键下拉，找到填充，输入终止数字。
2. 字母序列A~N（由于字母是文本，不能像处理数字一样处理），

 需要在第一行输入函数：=CHAR(ROW(A65)),按住crtl键下拉，找到填充(往下填充)。

1. 出现#NAME？是指单元格内出现excel无法识别的文本或者函数拼写错误。
2. 把单元格的城市或者文本要统一到Excel中，在此单元格的右边单元格输入、顿号，最后一行输入。句号，在合并的单元格中输入函数：PHONETIC(  ，再选中两列单元格作为参数，回车，就行了。

1. =函数名（参数）参数可以通过点击和选中进行自动填补.在录入函数的时候可是使用tab键自动补齐，右括号可以省略
2. 在旁边单元格输入函数之后进行选中就可以作为参数。在计算的时候都需要=
3. 加法函数：=SUM(    
4. 乘法函数=PRODUCT(
5. 幂运算 函数：=POWER( ,3)
6. 绝对值函数： =ABS(
7. 四舍五入函数：=ROUND(
8. 取实数，向下取整： =INT(
9. 求余数函数：=MOD( , )--判断报表的每一行数奇数行还是偶数行，使用=MOD(ROW(所在的行号)，2)---0是偶数行，1是奇数行。
10. 求平方根函数:=SQRT(
11. (对数据先求积，再求和)求乘积的和：=SUMPRODUCT(
12. 函数引用方式：相对引用、绝对引用、半相对半绝对引用f4键自由转换$符号。
13. 对一行和一列的数字同时求和:atl+=（使用的是相对引用）
14. 占比：计算这个的时候以为字母不能变，所以可以使用半相对半绝对引用，$在前面表示绝对引用。

小数变成百分比：快捷键ctrl+shift+5

1. 格式转换：ROUND有两个参数=ROUND(要处理的数，变成什么样)
2. 给数据添加单位的同时进行计算---自定义格式

Ctrl+1快捷键可以弹出“设置单元格格式”。

全选中:ctrl+shift+1-----------常规格式

全选中:ctrl+shift+2-----------时间格式

全选中:ctrl+shift+3-----------日期格式

全选中:ctrl+shift+4-----------货币格式---选货币类型（ctrl+1,自定义）

全选中:ctrl+shift+5-----------百分比格式

全选中:ctrl+shift+6-----------科学计数法格式

1. 英文字母的转换的函数：UPPER---大写变小写

第一行ctrl+c,然后选中下面的，右击粘贴公式的方法可以统一变大写。

LOWER---小写变大写

PROPER----将所有首字母大写变小写

1. 文本数值的转化：将文本数值改为数据---VALUE

去掉数值之间的空格的函数--NUMBERVALUE

1. 阿拉伯数字转化成中文的函数----NUMBERSTRING(选中值，1)--中文简体；（只能处理整数）

NUMBERSTRING(选中值，2)--中文繁体；

NUMBERSTRING(选中值，3)--常规中文；

1. =TEXT(I2,"[dbnum1]")--处理带小数的数字转中文
2. 文本处理：

  文本的连接：文本&文本

=CONCATENATE(H11,I11)

=PHONETIC(H11:J11),直接选中就行---不能连接数值

文本的提取：=LEFT(选中，需要几个)

=RIGHT(选中，需要几个)

=MD(选中，需要几个)

文本的查找：=FIND(选中要查找的文字，在哪里查找)

从一大串中查找---=LEFT(C17,FIND("@",C17)-1)

文本替换:   招商银行变为中国银行 =REPLACE(C23,1,2,"中国")

1. 字符/字节的计算:

字节数：=LEN(

字符数：=LENB(

一个汉字=两个字节，一个数字/字母=一个字节

1. 两列数据的核对的函数：=EXACT(选中)--结果是0就是相同或者布尔值，其他数不同

=DALTA((选中)--结果是1就是相同，0就是不同。

判断文本时，根据布尔值来判断。ctrl+ 也可以，但是我的输入法有冲突，在自己电脑无法实现。

1. 数据的清理的函数：可以清理非打印字符=CLEAR(

删除多余的空格=TRIM(---------这两个函数可以合用。

1. 数据汇总3大函数--计数、求和、平均：

求和：=SUM(   或者ctrl+=键

平均值：=AVERAGE(

汇总函数count:=COUNT(-----数字单元格，文本单元格不影响结果

=COUNTA(计算非空单元格的个数

=CUNNTBLACK(计算空白单元格

1. 描述数据的特性-描述性统计：

=MAX   =MIN  =MEDIAN(中位值)  -MODE(众数)

=LARCE(第几个最大值)   =SMALL(第几个最小值)  

1. 在进行计数后直接对旁边的直方图进行描述的话，输完函数表达式不能直接回车，要ctrl+shift+回车，才使得直方图实时变化。
2.  数据排名：

确定数据在数据组中的排名：=RANK( -----=RANK(G15,$G$14:$G$18,)

根据名次返回对应的数值：=LARGE(选中，第几名）

1. 增长率的计算：同比、环比（与上个月相比的增长率）、复合增长率

环比：=A20/A19-1-------期末值/期初值-1

同比：（与去年这个月相比）-与环比公式一样，只是数值选择不同

1. 复合增长率(CAGR)---用于投资收益率，几个年份的比较，需要先算出环比，作为期末值和期初值，(现有价值/基础价值)^(1/年数) - 1
2. 条件统计：单条件计算函数--SUMIF(计算满足条件数据的和)

COUNTIF(计算满足条件数据个数)   AVERAGEIF(计算满足条件数据的平均值)

==SUMIF(条件区域,指定的求和条件,求和的区域)

使用SUMIF函数计算C2:C12单元格区域大于60的成绩总和。

=SUMIF(C2:C12,">60")

使用COUNTIF函数计算C2:C12单元格区域大于60的个数。

=COUNTIF(C2:C12,">60")

COUNTIFS(B2:B9,"销售部",D2:D9,"6000")

这样就算出销售部拿6000元的工资人数是2人。

“<>200”表示不等于200

 

1. 多条件统计：SUMIFS(计算满足条件数据的和)

COUNTIFS(计算满足条件数据个数)   AVERAGEIFS(计算满足条件数据的平均值)

1. 时间函数：ctrl +;----快速输出当前日期。日期常规对应1~10的数字。时间常规对应0~1的数字。

ctrl +shift+;----快速输出当前时间。ctrl +shift+2--常规格式切换为时间格式。

1. 基本的日期和时间函数：=DATE(年，月，日)  =YEAR(  =MONTH(

=DAY(    =TIME(    =HOUR(时   =MINUTE(分    =SECOND(秒

=NOW(不需要选任何参数)   =TODAY()

1. =WEEKDAY(返回0~6代表星期的函数

=WEEKNUM(返回一个日期在一年中的周序数

1. 计算间隔年数：=DATEDIF(起始日期，结束日期，”y”)
2. 计算间隔月数：=DATEDIF(起始日期，结束日期，”m”)
3. 计算间隔日数：=DATEDIF(起始日期，结束日期，”d”)
4. 工作日及实际工作天数的计算：

计算工作日天数：=NETWORKDAYS(F30,F31)

以工作日计算奖金（10000）：=F32/NETWORKDAYS("2016-1-1",F31)*F35

工作日天数/一年的开始年数，结束日期）*总奖金

-根据项目规划（按天算）计算完工日期：预计完成时间=WORKDAY(项目启动日期，所需天数）。

如果有放假时间：=WORKDAY(项目启动日期，所需天数，放假总日期）

（按月算）：=EDAY(开始时间，月数)

退休时间：男女有别：

=IF(C40="男",EDATE(D40,60*12),EDATE(D40,55*12))

第二种计算：=EDATE(H37,((G37="男")*5+55)*12)

1. 计算指定日期之前或之后数月的最后一天：=EOMONTH(开始时间，月数)
2. 数据舍入：正确计算加班时间：加班时间的计算就是向下的案例，因为不满30分钟就不算加班。

 

=CEILING(---天花板     =FLOOR(----地板

1. 数据匹配：

=VLOOKUP(要查找的数据，在哪个表查（绝对引用），0)---查找

0（精确匹配）--推荐使用          1（模糊匹配）

模糊匹配是针对数值的，小于查找值的最大值

查找的是固定的值就需要绝对引用$,

把常量变量化，实现公式复制--

通过给表格添加序号。

=HLOOKUP(------水平方向的查询和匹配。

=HLOOKUP($B$54,$A$52:$I$56,COLUMN(D55),0)   查找之后的表格按行分布。

=HLOOKUP($B$54,$A$52:$I$56,ROW(D55),0)   查找之后的表格按列分布。

=HLOOKUP(   的通配符运用：

* 任意多个字符，包括0个。   ？表示任意一个字符。  ？*至少一个字符。

=VLOOKUP("*"&D66&"*",$A$65:$B$68,1,)-----在广州左右两边可以是任意字符。

 

1. 数据匹配2：

=INDEX(----返回行或列指定位置的数据。

=MATCH(----返回与指定数值匹配的元素在一行/一列中的位置，INDEX与MATCH搭配使用。

=MATCH("大连市",A65:A68,)

=CHOOSE( 函数--根据索引值返回参数清单中对应位置的数据。

=OFFSET( 函数---既能返回值，又能返回区域。

 

1. 在编辑的状态下“插入”都是不可选的。

 

要在复制之前的时候选小√，不然ctrl+f3无法弹出“名称管理器”。