Excel笔记
Excel学习笔记
- 数字序列自动生成1~n：在第一个格输入1，按住crtl键下拉，找到填充，输入终止数字。
- 字母序列A~N（由于字母是文本，不能像处理数字一样处理），
需要在第一行输入函数：=CHAR(ROW(A65)),按住crtl键下拉，找到填充(往下填充)。
- 出现#NAME？是指单元格内出现excel无法识别的文本或者函数拼写错误。
- 把单元格的城市或者文本要统一到Excel中，在此单元格的右边单元格输入、顿号，最后一行输入。句号，在合并的单元格中输入函数：PHONETIC( ，再选中两列单元格作为参数，回车，就行了。
- =函数名（参数）参数可以通过点击和选中进行自动填补.在录入函数的时候可是使用tab键自动补齐，右括号可以省略
- 在旁边单元格输入函数之后进行选中就可以作为参数。在计算的时候都需要=
- 加法函数：=SUM(
- 乘法函数=PRODUCT(
- 幂运算 函数：=POWER( ,3)
- 绝对值函数： =ABS(
- 四舍五入函数：=ROUND(
- 取实数，向下取整： =INT(
- 求余数函数：=MOD( , )--判断报表的每一行数奇数行还是偶数行，使用=MOD(ROW(所在的行号)，2)---0是偶数行，1是奇数行。
- 求平方根函数:=SQRT(
- (对数据先求积，再求和)求乘积的和：=SUMPRODUCT(
- 函数引用方式：相对引用、绝对引用、半相对半绝对引用f4键自由转换$符号。
- 对一行和一列的数字同时求和:atl+=（使用的是相对引用）
- 占比：计算这个的时候以为字母不能变，所以可以使用半相对半绝对引用，$在前面表示绝对引用。
小数变成百分比：快捷键ctrl+shift+5
- 格式转换：ROUND有两个参数=ROUND(要处理的数，变成什么样)
- 给数据添加单位的同时进行计算---自定义格式
Ctrl+1快捷键可以弹出“设置单元格格式”。
全选中:ctrl+shift+1-----------常规格式
全选中:ctrl+shift+2-----------时间格式
全选中:ctrl+shift+3-----------日期格式
全选中:ctrl+shift+4-----------货币格式---选货币类型（ctrl+1,自定义）
全选中:ctrl+shift+5-----------百分比格式
全选中:ctrl+shift+6-----------科学计数法格式
- 英文字母的转换的函数：UPPER---大写变小写
第一行ctrl+c,然后选中下面的，右击粘贴公式的方法可以统一变大写。
LOWER---小写变大写
PROPER----将所有首字母大写变小写
- 文本数值的转化：将文本数值改为数据---VALUE
去掉数值之间的空格的函数--NUMBERVALUE
- 阿拉伯数字转化成中文的函数----NUMBERSTRING(选中值，1)--中文简体；（只能处理整数）
NUMBERSTRING(选中值，2)--中文繁体；
NUMBERSTRING(选中值，3)--常规中文；
- =TEXT(I2,"[dbnum1]")--处理带小数的数字转中文
- 文本处理：
文本的连接：文本&文本
=CONCATENATE(H11,I11)
=PHONETIC(H11:J11),直接选中就行---不能连接数值
文本的提取：=LEFT(选中，需要几个)
=RIGHT(选中，需要几个)
=MD(选中，需要几个)
文本的查找：=FIND(选中要查找的文字，在哪里查找)
从一大串中查找---=LEFT(C17,FIND("@",C17)-1)
文本替换: 招商银行变为中国银行 =REPLACE(C23,1,2,"中国")
- 字符/字节的计算:
字节数：=LEN(
字符数：=LENB(
一个汉字=两个字节，一个数字/字母=一个字节
- 两列数据的核对的函数：=EXACT(选中)--结果是0就是相同或者布尔值，其他数不同
=DALTA((选中)--结果是1就是相同，0就是不同。
判断文本时，根据布尔值来判断。ctrl+\ 也可以，但是我的输入法有冲突，在自己电脑无法实现。
- 数据的清理的函数：可以清理非打印字符=CLEAR(
删除多余的空格=TRIM(---------这两个函数可以合用。
- 数据汇总3大函数--计数、求和、平均：
求和：=SUM( 或者ctrl+=键
平均值：=AVERAGE(
汇总函数count:=COUNT(-----数字单元格，文本单元格不影响结果
=COUNTA(计算非空单元格的个数
=CUNNTBLACK(计算空白单元格
- 描述数据的特性-描述性统计：
=MAX =MIN =MEDIAN(中位值) -MODE(众数)
=LARCE(第几个最大值) =SMALL(第几个最小值)
- 在进行计数后直接对旁边的直方图进行描述的话，输完函数表达式不能直接回车，要ctrl+shift+回车，才使得直方图实时变化。
- 数据排名：
确定数据在数据组中的排名：=RANK( -----=RANK(G15,$G$14:$G$18,)
根据名次返回对应的数值：=LARGE(选中，第几名）
- 增长率的计算：同比、环比（与上个月相比的增长率）、复合增长率
环比：=A20/A19-1-------期末值/期初值-1
同比：（与去年这个月相比）-与环比公式一样，只是数值选择不同
- 复合增长率(CAGR)---用于投资收益率，几个年份的比较，需要先算出环比，作为期末值和期初值，(现有价值/基础价值)^(1/年数) - 1
- 条件统计：单条件计算函数--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
- 多条件统计：SUMIFS(计算满足条件数据的和)
COUNTIFS(计算满足条件数据个数) AVERAGEIFS(计算满足条件数据的平均值)
- 时间函数：ctrl +;----快速输出当前日期。日期常规对应1~10的数字。时间常规对应0~1的数字。
ctrl +shift+;----快速输出当前时间。ctrl +shift+2--常规格式切换为时间格式。
- 基本的日期和时间函数：=DATE(年，月，日) =YEAR( =MONTH(
=DAY( =TIME( =HOUR(时 =MINUTE(分 =SECOND(秒
=NOW(不需要选任何参数) =TODAY()
- =WEEKDAY(返回0~6代表星期的函数
=WEEKNUM(返回一个日期在一年中的周序数
- 计算间隔年数：=DATEDIF(起始日期，结束日期，”y”)
- 计算间隔月数：=DATEDIF(起始日期，结束日期，”m”)
- 计算间隔日数：=DATEDIF(起始日期，结束日期，”d”)
- 工作日及实际工作天数的计算：
计算工作日天数：=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)
- 计算指定日期之前或之后数月的最后一天：=EOMONTH(开始时间，月数)
- 数据舍入：正确计算加班时间：加班时间的计算就是向下的案例，因为不满30分钟就不算加班。
=CEILING(---天花板 =FLOOR(----地板
- 数据匹配：
=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,)-----在广州左右两边可以是任意字符。
- 数据匹配2：
=INDEX(----返回行或列指定位置的数据。
=MATCH(----返回与指定数值匹配的元素在一行/一列中的位置，INDEX与MATCH搭配使用。
=MATCH("大连市",A65:A68,)
=CHOOSE( 函数--根据索引值返回参数清单中对应位置的数据。
=OFFSET( 函数---既能返回值，又能返回区域。
- 在编辑的状态下“插入”都是不可选的。
要在复制之前的时候选小√，不然ctrl+f3无法弹出“名称管理器”。
