1.excel公式
excel中先说行,在说列 (因为列少,可以快速定位)
行是abcd 列是1234
函数和公式在输入的时候,会有提示,按table选中,而不是空格
1.公式的基本使用
- 选择单元格
- 键入等号“=”。(注意: Excel 中的公式始终以等号开头.)
- 选择一个单元格,或在所选单元格中键入其地址。
- 输入运算符。 例如,- 代表相减。
- 选择下一单元格,或在所选单元格中键入其地址。
- 按 Enter。 计算结果将显示在包含公式的单元格中。
2.查看公式
点击单元格,公式就会出现在编辑栏中
公式可以包括:函数,引用,运算符和常量
3.公式运算符
Excel中公式运算符共有4种类型,分别是算术运算符,比较运算符,引用运算符,文本运算符。
- 算术运算符主要用于数学计算,主要有加号,减号,斜杠,星号,百分号(就是百分号的意思 1 = 100%),脱字符(^ 幂次的意思 2^3 )。
- 比较运算符主要用于数值比较 (不等于为 < > ) ( 结果都是 1 , 0) 。
- 引用运算符只要用于合并单元格区域( : , 空格)。
- 文本运算符只有一个文本串连字符“&”,用于将两个或多个字符串链接起来
注意:
- 运算符有一定的优先级,按照优先级从高到低计算。
从高到低 : , 空格 -(负号) % ^ *和/ +和- & =,<,>,>=,<=,<> - 运算中有括号的话,先计算括号中的值。
4.单元格的地址引用
4.1.相对运用
=A1A3 随着拖动,就会改变位置
4.2.绝对引用 $
=$A
1
∗
1*
1∗A
3
这
样
往
下
拉
,
,
就
不
会
改
变
位
置
啦
也
可
以
=
A
1
∗
3 这样往下拉,,就不会改变位置啦 也可以=A1*
3这样往下拉,,就不会改变位置啦也可以=A1∗A$3 这样往下拉,就只有A1 会改变位置
无论你怎么拖,都不会变化
4.3.混合引用
混和应用 =A$2(1+A$1) 随着拖动不会改变位置
注意:=A$1则表明行是绝对引用,怎么拉都不会变,而列是相对引用,随着公式在行方向上的 变化而变化
=$A1,也就是在列标前面加上一个$符号,这就表示列绝对引用,行相对引用。这时下拉单 元格时行号会变动变成=$A2,但是横着拉列号不会变动,仍然是=$A1。
5.本利和
https://www.bilibili/video/BV1Fy4y1z7DH?p=58&spm_id_from=pageDriver
本利和=本金x(1 + 存期年数 X 年利率 ) 记住要锁定 存期年数 和 年利率
这里主要是要注意公司引用的是那个单元格的数据,一定要注意该锁定谁
5.单列和多列的互换–地址引用
主要使用了相对引用和填充柄
5.1单列变多列
一列有1000个数据,但是要把这个列变成 5列*20的东西
- 找到第第一列的第二十一个数据, 相对引用到第二列, 在快速填充
- 找到第二列的第二十一个数据, 相对引用到第三列,快速填充
- 找到第三列的第二十一个数据, 相对引用到第四列,快速填充
- 找到第四列的第二十一个数据, 相对引用到第五列,快速填充, 看上去就完成了
但是在复制这个表格的时候发现无法复制 - 想复制过去,就 选中要复制的地方 > 开始 > 复制性粘贴 > 数值 > 确定
5.2多列变多列
5列*20的数据
- 在第一列的第21行,相对引用(=B1) 第二列的第一个值,然后往下拉
- 第一列的第21行,往左拉一个单元格,就出现了第三列的第一个值
- 第二列的第二十一行,往左拉一个单元格, 就出现了第四列的第一个值 ,第一列继续往下拉,
- …重复第三步
- …
- 看5.1的最后一步
6.错误值利用(筛选具体值)
利用错误值能获取价值
选中目标列 > 数据 > 筛选(这里没反应的) > 去看选中列的最上面有一个小东西,点一下 > 搜索栏下面的只选中最后一个 #N/A (错误值) > 完成
7.全年级平均分
也可以叫做,跨工作表公式计算
就是在一个单元格的输入框中输入 = ( 点击每一个工作簿的具体的值1+值2+值3)/4 就可以了
8.追踪
8.1追踪公式引用
引用了谁
就是查看这个值是用那些值计算出来的 , 地址栏啊可以看到,但是也可以用箭头指示出来
公式 > 追踪引用单元格 > 点一下 就可以很明显的看到啦
还有一个追踪从属单元格,就是这个被谁引用了
8.2 追踪错误
判断单元格错误的原因,找到具体的单元格,就容易判断错误啦
选中目标 > 公式 > 错误检查 > 追踪错误
9.逐步理解函数的实现
就是看这个公式到底是怎么执行的 ,顺序和步骤以及每一步到底干了啥
选中单元格 > 公式 > 公式求值 就会显示出目标单元格的公式 > 求值 不停地点,就会显示出每一步的具体操作和结果
有的电脑可以直接按 F9 快捷键
2.函数
数据分析最重要的一个环节是数据清洗
2.1 常用函数
函数类型 | 函数名字 |
---|---|
数学函数 | int(),mod(),round(),abs(),sqrt(),rand(),randbetween() |
统计函数 | min(),sum(),count(),counta(),average(),countif(),sumif(),averageif(),countifs(),sumifs() |
averageifs(),frequency(),rank() | |
日期函数 | year(),month(),day(),today(),date(),now(),edate(),eomonth(),datedif() |
文本函数 | mid(),left(),right(),len(),text(),rept(),replace(),substitute() |
逻辑函数 | if(),and(),or(),not() |
查找与引用函数 | vlookup(),offset(),match(),index(),indirect(),row(),column(),hlookup |
2.2.公式和函数
- 相同点:公式与函数的相同点就是:都是以“=”开头的,并且都会得到一个返回值。
公式:公式是一个等式,以“=”开头,后面紧跟数据和运算符,并得到返回值。
例如下面C1单元格中的 =A1+B1 就是一个公式。
函数:函数是excel内部预定义的功能,以“=”开头,按照特定的规则进行计算,并得到返回值。
例如下面C1单元格中的 =SUM(A1:B1)就使用的SUM函数。 - 不同点
函数可以是公式里面的一部分,但公式不一定总需要包含函数。所以公式的范畴是更大的,公式包含函数。
例如下面C1单元格中的 =SUM(A1:B1)+10就是一个公式,在这个公式中包含了sum函数。
函数有唯一的函数名称,而公式没有。
2.3.函数的使用
2.3.1.if + and + or
if 结果为 ture 或者 false
=IF(C2=“YES”,1,2) 如果C2=yes ,就返回1,否则返回2
and 函数,是一个逻辑函数,用于确定测试中的所有条件均为True,参数最多为256个
一false则假 一假则假
(1=1,1=2) 1=1是ture 1=2是 FALSE,所以最后的结果是 false
or 是一个逻辑函数,用于确定测试中的所有条件均为True
一真则真
使用: 每一门课 都大于60
=AND(D2>60,E2>60,F2>60)
=OR(D4>60,E4>60,F4>60)
也可以嵌套
=IF(AND(D3>60,E3>60,F3>60),“通过”,“不通过”)
=IF(OR(D4>60,E4>60,F4>60),“至少一门通过”,“没有一门通过”)
2.3.3.COUTN IF 统计
就是if
count 统计数值类型的个数
counta 统计非空单元格的个数
countif(范围 , “查找的东西”) 查找范围内的具体的值的个数 ,只可以查找单列
countifs(范围1 , “查找的东西1,也可以是大于” ,范围2 , “查找的东西2”) 可查找多列
比如统计两天内有多少人全勤,就是用这个函数查第一天全勤的和第二天全勤的人数
2.3.4 嵌套
计算水电费
if(a,b,if(c,d,e)) —如果a,就输出b,不然就c ,输出d, ac都不是就输出e
注意$
https://www.bilibili/video/BV1Fy4y1z7DH?p=71&spm_id_from=pageDriver
计算借贷
sumif( 条件范围, “条件”, 求和范围)
如果条件范围和求和范围一致,就可以省略第三个值(求和范围)
sumifs(求和范围 , 条件范围 , “条件” , 条件范围2 , “条件”)
sum(范围3) 范围内的值3
链接中的答案为 :
金额大于10000的钱进行求和 =SUMIF(范围 ,">10000")
借贷金额总和 =SUMIF(借贷的单元格范围 ,“条件”, 金额的单元格范围)
计算贷款方,建设银行的金融总和 =SUMIFS(金额范围, 借贷范围 , “借贷范围的条件”, 银行名称的范围 , “银行名称的条件” )
https://www.bilibili/video/BV1Fy4y1z7DH?p=72
2.4 日期函数
2011/12/10 =YEAR(目标)获取年份
2013/2/15 =MONTH(目标)获取月份
2012/4/19 =DAY(目标)查看这个月多少天
2012/8/1 =TODAY() 获取当前日期
2011/10/1 =DATE(2020,11,26)获取输入的日期 , 也可以这样=DATE(“2020”,“11”,“26”)
2013/6/22 =NOW( ) 获取当前的日期和时间
2013/9/1 =EDATE(目标,-1)获取前几个或者后几个月的时间,正数就是在目标单元格的基础上加几 个月,负数就是减几个月
2012/4/19 =EOMONTH(目标,-1)获取该月上个月的最后一天,吧-1改为0,就是获取当前月的最后一 天日期
2012/4/12 =DAY(EOMONTH(目标,0)) 该月有多少天
还可以这样 day(eomonth(目标,0))
dateif ( 出生日期 , today() , “y| m | d”) 算周岁 ,y返回年,m返回月 ,d返回天
第几季度 根据月份去查看
IF(MONTH(目标)/3 <= 1 , “第一季度” ,IF(MONTH(目标)/3 <= 2 ,第二季度 ,IF(MONTH(目标)/3 <= 3 ,第三季度, IF(MONTH(目标)/3 <= 4 ,第4季度 ) ) )
最后的括号数,就是看有几个if ,就有几个括号
改完之后要去开始里面,把数据形式从日期改为常规,或者是常规改为日期
https://www.bilibili/video/BV1Fy4y1z7DH?p=73&spm_id_from=pageDriver
2.5.round系列
满五进一,
round(你输入的参数 , 保留的小数位数)
后面的参数 0是普通的四舍五入 , 1是保留一位小数 , -1就是往前保留整数(超级四舍五入,没满五就是0 , 满5就是10)
round(1.5 , 0 ) = 2
round(1.5 , 1 ) = 1.5
round(1.5 , -1 ) = 0 没满五,所以为0
round(6.5 , -1 ) = 10…满5,所以为10
roundup 往上走
roundup(1.2 , 0) = 2
roundup(1.2 , 1) = 1.2
roundup(1.2 , -1) = 10 1.2为1.往上走,就是10
rounddown 往下走
rounddown(1.5 , 0) = 1
rounddown(1.5 , 1) = 1.5
rounddown(5.5, -1) = 0
2.6.mod
MOD(number, divisor)
number 必需。 要计算余数的被除数。
divisor 必需。 除数。
符号之和第二个数有关
mod(1 , 2) = 1
mod(1 , -2) = -1
mod(-1 , 2) = 1
mod(-1 , -2) = -1
MOD(3, 2) = 1
MOD(-3, 2) = 1
MOD(3, -2) = -1
MOD(-3, -2) = -1
如果能被100整除,且能被400整除,则为闰年
如果能被4整除,且不能被100整除,则为闰年
提示 mod / and /or
IF(OR(AND(MOD(A2,100)=0,MOD(A2,400)=0),AND(MOD(A2,4)=0,MOD(A2,100)<>0)),“闰年”,“平年”)
解析: (MOD(A2,100)=0 能被100整除 和AND MOD(A2,400)=0) 能被400整除
或者 or (MOD(A2,4)=0 能被4整除 和 AND MOD(A2,100)<>0 不能被100整除
2.7.replace
隐藏手机号
第几位 是从1开始,不是从0 开始哦,这点要记住
replace(目标, 从第几位开始,隐藏几个,rept( “用什么符号隐藏” , 隐藏的位置显示几个符号))
=REPLACE(目标,5,3,REPT("*",10))
2.8.合并单元格并计算
就是一列数据,几个一算几个一算
案列 : 将三个单元格计算总和
https://www.bilibili/video/BV1Fy4y1z7DH?p=77&spm_id_from=pageDriver
2.9.生成间断序号
就是生成序号1 2 3 4 5 6 7 ,不过在空行就不生成序号,自动跳过
counta 统计非空单元格的个数
=IF(B8="","",COUNTA(B$2:B8)) 如果有一行是空,那么就输出空,而不是数字
计算这一列有多少个就可以生成序号了,
https://www.bilibili/video/BV1Fy4y1z7DH?p=78&spm_id_from=pageDriver
3.自定义的函数输入
选中文本,右键 > 设置单元格格式 > 数字下的自定义
3.1 大于100为蓝色,小于等于100为红色
[红色][<=100];[ 蓝色][>100] (去掉*,*是为了挡住xmind的注释)
3.2 TEXT(value, format_text) 函数
值 和 格式
比如:=TEXT(1234.456,“00.00”) 显示结果为 1234.46
3.3 G/通用格式.
以常规(无任何格式)的数字显示,默认值
10显示为10;10.1显示为10.1
3.4 0 显示数字
如果数字位数少于格式中的零的个数,则在数字前面补齐无意义的零
自定义中设置 00000
1234567显示为1234567;123显示为00123
100.14显示为100.140;1.1显示为01.100
3.5 #显示有效数据
只显示有效数字而不显示无意义的零
###.##
12 显示为12. 13显示为13.
12.100显示为12.1
12.1263显示为:12.13
3.6 ?添加空格,对齐小数点
数字占位符
为无意义的零在小数点两边添加空格,以便使小数点对齐。
??.?? 12.1234 显示为12.12
???.??? 12.1234显示为12.123 小数点前的部分不会有变化
3.7 , 千位分隔符
显示千位分隔符或者将数字以千倍显示
#,# 10000显示为10,000 1000000显示为10,000,000 (分割)
#, 10000显示为10 (一个, 去掉三个0)
“#, 1000000显示为“1”。 (两个 , 去掉六个0 )
3.8 . 小数点
" . " 加了双引号就是表示字符(文本) , 会直接覆盖单元格的内容…
0.# 11.23显示为11.2
. 11.23 显示为11.
3.8 % 百分比
#% 0.23显示为 23%
如果只输入 一个 % 那么这个就是错误格式,所有的文本会被替换为 % …
3.9 / 显示下一个字符
看不懂这个具体是干啥的…不会用
和“”””用途相同都是显示输入的文本,且输入后会自动转变为双引号表达。不同的“/”是显后面的文本,双引号是显示双引中间的文本。
例:代码“/ABC”或“”ABC””均显示为“ABC”
3.10 _ (下划线)
留下一个和下一个字符同等宽度的空格
(下一个字是不是不会生效,只作为一个提供文本长度的工具符号)
例:
正数后面留一个与)等宽的空格,负数不留空格,这样能保证整数和复数对齐
$#,##0.00_) 表示 1234.567 为
1234.56
(
1234.56 (
1234.56(#,##0.00) 表示 -1234.678 为 (
1234.67
)
代
码
:
1234.67) 代码:
1234.67)代码:#,##0.00_);($#,##0.00)
3.11 *
星号 ,重复下一次字符,直到充满列宽。(注意是充满列宽哦)
例:代码:“@-”。“ABC”显示为“ABC-----------------”
数字的显示可以使用#或者0或者?
例:代码:“#-”。“123”显示为“123-----------------”
3.12 “文本”:显示双引号中的文本。
例:代码:中国@ 输入 高速发展 00显示为 中国高速发展
3.13 [颜色]:用指定的颜色显示字符。
常用可选色:红色、黑色、黄色,绿色、白色、蓝色和洋红
[颜色N]:是调用调色板中颜色,N是0~56之间的整数。
例:代码:“[颜色3]”。单元格显示的颜色为调色板上第3种颜色。
3.14 [条件]:可以单元格内容判断后再设置格式
条件格式化只限于使用三个条件,其中两个条件是明确的,另一个是“所有的其他”。条件要放到方括号中。必须进行简单的比较。
显示结果是单元格数值大于零显示正数,小于零显示“负数”,其他情况显示"零",非数字类型显示"文本"。
例:代码:[>0]正数;[<0]负数;零;文本
或:代码:整数[>0];负数[<0];零;文本
还可以给正数 ,负数 加上颜色 ,只需要在边上加上[白色],[黑色]
结果为 [>0][白色]正数;[<0][黑色]负数 使用时去掉两个*
3.15 ! 显示一个双引号的一半 "
由于引号是代码常用的符号。在单元格中是无法用""“来显示出来“””。要想显示出来,须在前加入“!”
例:代码:“#!"”。“10”显示“10"”
例:代码:“#!"!"”。“10”显示“10""”
3.16 @ 文本占位符
文本的重复显示要使用@
设置: “集团”@“部” 单元格中输入 财务, 就会输出 集团财务部
设置 @@@ 输入 财务 输出 财务财务财务
3.17 时间和日期
- 日期设置
符号 | 含义 |
---|---|
m | 将月显示为不带前导零的数字。 |
mm | 根据需要将月显示为带前导零的数字 |
mmm | 将月显示为缩写形式(Jan 到 Dec) |
mmmm | 将月显示为完整名称(January 到 December)。 |
mmmmm | 将月显示为单个字母(J 到 D)。 |
d | 将日显示为不带前导零的数字。 |
dd | 根据需要将日显示为带前导零的数字。 |
ddd | 将日显示为缩写形式(Sun 到 Sat)。 |
dddd | 将日显示为完整名称(Sunday 到 Saturday)。 |
yy | 将年显示为两位数字。 |
yyyy | 将年显示为四位数字。 |
例:代码:“YYYY-MM-DD”。2005年1月10日显示为:“2005-01-10”
例:代码:“YY-M-D”。2005年10月10日显示为:“05-1-10”
-
星期设置
“AAAA”:日期显示为星期。 -
时间设置
“H”或“HH”:以一位(023)或两位(0123)显示小时
“M”或“MM”:以一位(059)或两位(0159)显示分钟
“S”或“SS”:以一位(059)或两位(0159)显示秒
[H]或[M]或[SS]:显示大于24小时的小时或显示大于60的分或秒。
例:代码:“HH:MM:SS”。“23:1:15”显示为“23:01:15”
3.18 特殊字符的显示
例:代码:[DBNum1]
“1232”显示为“一千二百三十二”
代码:[DBNum2]
“13”显示为“壹拾叁”
代码 : [DBNum3]
例:“123”显示为“1百2十3”
常用实列
正数的格式 负数的格式 零的格式 文本的格式
#,##0.00 [Red]-#,##0.00 0.00 “TEXT”@
把12345显示为1.2 -----代码:“0.,”
设置千元显示且四舍五入保留两位小数要求:把“12345”显示为:12.35-----代码:“#.00,”
在数字中任意插入字符要求:把“20050512”设置为:2005-05-12-----代码:“0000-00-00”
在文本或数字前添加字符要求:“郑州市”显示为“河南省郑州市”-----代码:““河南省”@”
1035010121显示为“手机号码1034010121-----代码:““手机号码”@”或““手机号码”#”
另外日期格式的显示方法
“bbbb”:显示四位佛历年份,即以公元前543年为纪年元年,对1900年以后的日期有效。
“mmm”:显示英文月份的简称。
“mmmm”:显示英文月份的全称。
“ddd”:显示英文星期几的简称。
“dddd”:显示英文星期几的全称。
特殊说明
因为参数的特殊性,所以自定义的参数也是有关键字的。如函数=TEXT(A1,“b0000”)就会显示错误。因为“b”就是保留的关键字,在自定义格式输入“b”系统就会自动填入“bb”。bb就是佛历年份,即以公元前543年为纪年元年,对1900年以后的日期有效。“bbbb”就是四位佛历年份。要解决=TEXT(A1,“b0000”)的错误问题,需要这样定义函数=TEXT(A1,""“b”“0000”)。在自定义格式中定义就是“"b"0000”。其它的关键字自己体会如:“d”、“e”…
更多推荐
excel公式和函数
发布评论