随着信息化的发展,原本浑浑噩噩想着混吃等死的二胎妈妈的我,经过这段时间设计合同台账,突然觉得很丧,妥妥的强烈危机感啊~于是乎下定决心得加快提升自己了。
那么对于财会行业,excel是必备技能,一方面可以提升工作效率好有更多的时间去做幼小衔接教育,另一方面也是为了能在家庭和工作中活得一种平衡,工作的如鱼得水与家庭的相敬如宾在某种程度上是相得益彰的。
所以买了本豆瓣好评实战书,开启我的信息化学习第一篇。可能做不到每日学,坚持吧!
Day1
1.单元格如果是文本格式,即使以“=”开头,excel也不会视其为公式,一定要常规格式c
2.ctrl+f 查找 ctrl+h 替换
3.英文半角状态下“,”的作用----多个单元格区域求和。比如:sum(A1:B3)变为 sum(A1:B3,E1:E6)
4.只需要一个参数的函数举例:
![178f18bbdee8c45d47ed242f26b6b505.png](https://i-blog.csdnimg.cn/blog_migrate/72a19ccf9a2378385292ecc3edda9965.jpeg)
5.只需要一个参数的函数举例:
![364f1e396ee12a8f1d3a711b6f3187c8.png](https://i-blog.csdnimg.cn/blog_migrate/6f795befd3f891ff77e0684cb8bfa0ae.jpeg)
6.日期和时间本质上是数字,可以进行加减法,并且直接通过公式会默认为一个数,可以通过text(A1,“yyyy-mm-dd”)函数转化成文本进行转换;一个汉字两个字节(常识)。
7.后期功课:(1)什么是数组公式,哪些可用于日常?(2)归纳总结常用函数。
Day2
1. Left(文本,要提取的字符数) :有两种文本字符串用双引号括起来,单元格不能用双引号括起来。比如left(“20170304”,3),则返回值1,left(B2,3)
2. mid(文本,起始位置,要提取的字符数)
3. Vlookup(要查找的值,查找区域,取查找区域第几列的值,精确查找还是模糊查找)
注意:很实用的公式,注意查找区域是否绝对引用,第一参数是第二参数的第一列
4. ctrl+# 讲单元格格式设置为日期格式
5. Criteria 条件的意思
6. Countif(单元格区域,条件),公式中的条件表达式要用英文双引号括起来,如:
Countif(B2:B10,98)统计区域里是98的个数
Countif(B2:B10,”>70”)统计区域里大于70的数字个数
7. sumif(条件判断区域,条件,求和区域) 举例:sumif(B3:B13,”>=”&D1,B3:B13)
第一参数与第三参数一样时,第三参数可省略,即sumif(B3:B13,”>=”&D1)
大于等于>= 小于等于<= 不等于<>,且加双引号
8. averageif(条件判断区域,条件,求平均值区域)
9. &连接 “”文本引用 *多个任意字符 ?单个任意字符 ~转义符(如果字符中本身含有?*,”~*”可以去掉*的通配属性)
10. Countifs(条件区域,条件1,条件区域,条件2……)
Sumifs(求和区域,条件区域1,条件1,条件区域2,条件2,……)
Averageifs(求平均值的区域,条件区域1,条件1,条件区域2,条件2,……)
11. If() and() or () not() ——小逻辑函数相互嵌套,编制复杂的多条件函数
比如:if(and(c3>80,c3<90),”良好”,”其他”)
12.一个作业:了解vlookup函数模糊查找
Day3
1. 文本转数值:负负得正的--,如--”2017/2/1”,将本文转为日期,还可以通过加减0或者乘1的方式转换。
如下列是计算判断大于或小于某日期的求和公式,目前只能看懂sumif公式:
![ff80902042800c7fa2ea9038b3b3d024.png](https://i-blog.csdnimg.cn/blog_migrate/8df859f0eef63b6ffbd5d3ae5297a551.jpeg)
2. 空格的一个作用:交集
![f36f3c374b76708f89efe9d54f6e0278.png](https://i-blog.csdnimg.cn/blog_migrate/1376739d44d9cdb7045e363e7c6a7c68.png)
3. 引用文本的四种形式
引用本工作表,只显示单元格 sum(c5:c7)
引用本工作簿的其他工作表的单元格 sum(sheet1!c5:c7)
引用本工作簿的其他多个工作表的单元格 sum(sheet1:sheet3!c5:c7)
引用处于其他打开状态的工作簿的单元格 sum([统计表.xlsx]sheet1!c5:c7)
引用处于关闭状态的其他工作簿的单元格,较复杂,建议打开状态下再编辑公式,也可直接鼠标选择系统自行会进行显示,公式如下:sum(‘D:我的桌面[统计表.xlsx]sheet1’!$c$5:$c$7)
4. 运算顺序:
![8f09c959d8472e989265b3290ac23826.png](https://i-blog.csdnimg.cn/blog_migrate/9849de57846c1122e675e11dd3c3f9c3.png)
5. 绝对引用$,选择公式中的相应单元格,如选定A2字符,重复按F4可以在四种引用类型间切换($A$2 $A2 A$2 A2)
6. [表格]功能
定义名称、能自动扩展新增数据行、方便结构化引用的单元格区域
步骤:先“插入”“[表格]”(选定区域 插入 表格 包含标题与筛选)、修改表格名称(表2) 、进行公式编辑,这样公式会使用结构化引用样式,如A列是销售数量列,在K1输入sum(a2:a12),在K2的单元格公式就变为sum(表2[销售数量])
7三种常见结构化引用样式:
表格名[列标题] 引用单列 如 表2[销售数量]
表格名[[列标题1]:[列标题2]] 引用相邻的多列 如 表2[销售员]:[产品名称]
表格名[@列标题] 引用某列中本行的单元格 如 表2[@销售数量](是某一单元格)
8.indirect()函数的应用 P32
(1)作用:文本字符串单元格地址变为真正的引用
(2)Indirect(单元格引用,引用样式)举例:可以将公式拖动填充相应的其他同类型单元格
A1样式:INDIRECT(A5&"!B9")
R1C1样式:INDIRECT($A$2&"!R"&ROW()&"C"&COLUMN(),0)
注意两点:1. A1引用样式和R1C1引用样式 R表示行 C表示列A5单元格即为R5C1,C4单元格即为R4C3;2. 第二参数用于表示使用A1引用样式还是R1C1样式,如果第二参数为true或省略,则表示使用A1引用样式。
Day4
1. 冻结窗口,选取的单元格比较关键,若选B2,则可冻结第一行A列,若选C3,则可同时冻结第一、二行与A至B列。
2. Vlookup与lookup
(1)Lookup(查找值,查找区域,结果区域)
特点有三:比vlookup更牛逼的公式;参数2查找区域必须是按升序排序;第二参数与第三参数可以不在同一行甚至不在同一个表中,适用范围广。
(2)P43好好复习!可以查找指定日期的余额,注意运用的准确性
vlookup()模糊查找,即第四参数为1而不是0,要求数据按升序排列,此条件下如果有多条件符合记录的值,会返回符合条件的最后一条记录;如果没有找到刚好相等的值,会返回小于查找值的最大值所对应的记录,因而用上面的公式正好可以返回指定日最后一笔收支记录的余额。而这时,跟lookup()公式有异曲同工之妙。
3. 使用数据验证功能来规范数据录入。选中单元格区域,“数据”—“数据验证,在数据验证对话框中进行设置,“条件”是允许,“来源”可输入相应的名称。P48
4. 保护工作表以防修改。
(1)Ctrl+1 选中单元格或单元格区域,点击“保护”—“锁定”前的勾去掉——“确定”,可以设置为该单元格允许修改
(2)“审阅”—“保护工作表”……可进行工作表保护,只有输入密码才可更改
5. sumifs(求和区域,条件区域1,条件1,条件区域2,条件2……)公式回忆复习,注意文本格式用””引号,条件1与2是并且的意思,不是或。
6. 快速录入常用快捷键(有几个没弄明白)
![4b661d33210d854cb49ff3b339dd0680.png](https://i-blog.csdnimg.cn/blog_migrate/964c2ea8deb41fbe947738292cb29514.png)
此截图来源于龙老师的书本
7. 除此之外,Tab与enter键的结合使用P52
8. 练习单元格格式数字自定义,可以使“从:2020/3/2”变为数字而不是文本,可以直接引用进行公式操作。P42
9. 公式中相应的内容可以用替换完成,选定相应的单元格,直接按CTRL+H,自动会替换公式中的相应内容。
10. &将条件与条件值连接在一起组成完整的条件,如:”>=”&E3
Day5
1. 快捷键:ctrl+1设置单元格 ctrl+shift+1 将数量单元格区域设置为无小数且有千位分隔符的数字
2. F5—“定位”—“定位条件”—双击“引用单元格选项”,可批量选定公式所引用的那些单元格,进行单元格格式批量设置
3. “数据”—“创建组” 可进行缩放,方便查看不同层次数据,“视图”—“自定义视图”也有类似应用功能,可以多尝试操作看看
4. 定位功能:简单了解即可。快捷键:ctrl+[,或者直接双击公式单元格,还可选中公式单元格后点击编辑栏,用以检查公式范围是否正确,检查完按esc推出即可。
5. 追踪引用单元格功能:选中单元格,点击“公式”—“追踪引用单元格”,检查完点击“移去箭头”即可
6. Sum() 输入“=sum(”,按住ctrl,鼠标一次点击要加总的单元格,回车即可进行无规律单元格的加和计算。
7. 继续复习sumif()、column()公式,公式要多用才能转换成所谓的“知识就是力量”!
8. Mod(除数,被除数) 返回两数相除的余数
9. 一个复杂的公式:sumproduct(),求乘积之和。
两种用法:常规方法是将几个数组对应元素相乘,并返回乘积之和,语法格式为=sumproduct(数组1,数组2,…);另外一种用法,跟sumifs()差不多的功能,了解即可,见P94-95。但对于日常工作,只要运用常规作用应该也可以了
10.一个应用:隔列求和P88 sumproduct((mod(column(求和区域),2)=A)*(求和区域))
Day7
1. 数据有效性:“数据”—“数据有效性”(直接有“插入下拉列表”选项)—“序列”与“来源”,可以实现输入一个关键字跳出相应提示字段或者下拉框选择,非常nice
2. 数据透视表:有空学习练习
3. Month(需要提取月份的日期) year(需要提取年份的日期)
4. [表格]创建以及公式引用的运用,贯穿全书,如“固定资产清单[使用部门]”,表示固定资产清单表的使用部门一列。
5. 字符@表示引用指定列中与公式所在单元格位于同一行的单元格,eg:F2单元格输入公式中含“[@入账日期]”,表示入账日期列(假设A列)与F2对应的第二行的交叉单元格位置,即A2
6. 定义名称功能的使用:点击“公式”—“定义名称”—在“名称”中输入所取的名字(如叫“折旧日期”)——在“引用位置”输入相应的公式,点击确定,这样这个公式就可以定义为“折旧日期”,接下来的公式中只要输入“折旧日期”四个字即可,不需要特别的符号。注意:但是是相对引用还是其他,后续练习自行体会。书上讲:是一个代指符,可以代表其他单元格,至于代表哪个单元格取决于其引用的类型以及和活动单元格的关系。
7. Max/min(数值1,数值2,数值3……)取最大最小值; round(数值, 保留位数)四舍五入,第2格参数是指定保留的小数点后的位数。
8. 逻辑函数返回true or false,在做乘法运算时,“true”相当于1,“false”相当于0
9. 本节最重点的是合并单元格的合计数公式设计思路P61
10. 复制单元格可以批量:选定E4单元格,按ctrl+c,选定F4:J4单元格,按ctrl+v,即可将E4单元格的公式粘贴到这些单元格。
11. 一般按照复制粘贴我们都是填充结构的引用公式,但通过“选择性粘贴—公式”可以实现复制结构化引用的公式
好几天没学习了,因为全力以赴做了一份小小的研究,期待好的结果~现在学习继续!坚持!
Day8
1.ctrl+F/G/H 查找/定位/替换;alt+=,实现批量求和(公式自动输入,从上往下)
2.Sum(区域1,区域2,…) 可以多区域求和
3.通过选定单元格区域,按ctrl+g可以定位,选择“空值”,可以找到该区域的空白单元格,进行复制并批量粘贴入空白单元格内
4.公式的思路与设计很重要,要活,而不是仅仅背出各种函数功能
5.思路学习:
用sumif()批量向下求和公式模型(有小计数,先小计下面是构成明细的表格P67):
=本列下一行单元格到最末行单元格之和-2*下面的行各类别“小计”之和
=sum(本列下一行单元格到最末行单元格之和)-2*sumif(条件列下一行单元格到最末行单元格,”*小计”,本列下一行单元格到最末行单元格)
6. 第5点也可以用强大的subtotal()公式模型
=2*本列下面所有单元格剔除小计行的金额之和-本列下面所有单元格的金额之和
=2*subtotal(9,本列下一行单元格到最末行单元格)-sum(本列下一行单元格到最末行单元格)
7. 函数subtotal(统计方式,统计区域)——P73
(1)11种统计类型,其中9就是求和统计方式,其他有求平均值、最大最小值、统计个数等功能,用时查下就好
(2)最大的特点是:该函数自动忽略求和区域中嵌套的subtotal分类汇总,以避免重复计算
(3)
两种统计方式与比较
方式一:(1,2,3,……11) 会统计手动隐藏的行
方式二:(101,102,……111) 不会统计手动隐藏的行
两种方式相同点:
![aaf6dabe6cfd953914ba3def3d766c3e.png](https://i-blog.csdnimg.cn/blog_migrate/10f4c60819d39661cbc6f10f3468fd97.png)
昨日学习补充:
8.函数aggregate(),这个函数与subtotal()相似,功能更全一点,剔除因素可以更多,可以百度学习,单个人觉得功能太多太复杂,了解下即可。P69
9.(1)分级显示功能:选定相应行或列,点击“数据”—“分组显示”—“组合”,分级显示更易于清晰查看数据;
(2)将EXCEL界面设置为自己的目标界面,然后点击“视图”—“自定义视图”—“添加”,可进行相应设置,这样随时通过视图—自定义视图进行目标窗口切换。
10.捷键:CTRL+9 隐藏 但wps没有该功能;
11.好的EXCEL:针对目标设计表格布局、进行公式思考设计(函数运用、相关知识点配合)、界面友好性设计
Day9
1.函数SUBSTITUTE(text,old_text,new_text,[instance_num])
Text是需要替换其中字符的文本,或是含有文本的单元格引用;Old_text是需要替换的旧文本;New_text用于替换old_text 的文本;Instance_num 为一数值,用来指定以new_text 替换第几次出现的old_text;如果指定了instance_num,则只有满足要求的old_text 被替换;如果缺省则将用 new_text 替换 TEXT 中出现的所有 old_text。如:如果A1=学习的革命、A2=电脑,则公式=SUBSTITUTE(A1,"的革命",A2,1),返回“学习电脑”
2.查找函数MATCH(lookup_value, lookup_array, [match_type])返回指定数值在指定数组区域中的位置
match_type可选参数,数字 -1、0 或 1。match_type 参数指定 Excel 如何在 lookup_array 中查找 lookup_value 的值。默认值为 1。下表介绍该函数如何根据 match_type 参数的设置查找值:
(1)Match_type:1 或省略,表示MATCH 函数会查找小于或等于 lookup_value 的最大值。lookup_array 参数中的值必须按升序排列,例如:...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE。
(2)Match_type:0,表示MATCH 函数会查找等于 lookup_value 的第一个值。lookup_array 参数中的值可以按任何顺序排列。
(3)Match_type:-1,表示MATCH 函数会查找大于或等于 lookup_value 的最小值。lookup_array 参数中的值必须按降序排列,例如:TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ... 等等。
3.新的函数:offset(起点单元格,偏移几行,偏移几列,取几行,取几列),是根据指定行和列的偏移量来引用相应的单元格区域。
4.函数index()
连续区域情况index(array,row_num,column_num) ,其中array表示我们要引用的区域,row_num表示要引用的行数,column_num表示要引用的列数,最终的结果就是引用出区域内行列交叉处的内容。
非连续区域中index函数的公式格式是:=index((array_1,array_2,array_3....array_n),row_num,column_num,array_num) ,其中array表示我们要引用的非连续区域,其必须用小括号括起来,row_num表示要引用的行数,column_num表示要引用的列数,array_num表示第几个区域。
![545946273d8ca62ba2de3549fe07143b.png](https://i-blog.csdnimg.cn/blog_migrate/72fac88dfe40b39458b672aa19450131.png)
5. subtotal(统计方式,统计区域)与aggregate(统计方式,剔除方式,统计区域)在不相邻月份累加中的运用,尤其是aggregate函数,号称是万能型公式,明天集中学习。
6. 有关常用函数sumif的第三参数的一个知识点:
sumif(条件判断区域,条件,求和区域)函数的第三参数,真正起作用的是该求和区域左上角的那个单元格(该单元格定位定点),什么是定位定点?从左上角的第一个单元格为起点,相对于条件值在条件判断区域的位置(如在A列的第6行,即A6),那么对求和区域相应的那个位置的值进行加总(假如求和区域为B2:B10,那么在B7),详见P83,P85
7. 在排版设计技巧上,单元格边框与底色(浅色为宜),字体大小是否加粗,最好有个常用的规则,自己在摸索实践中不断总结
8. 分级显示功能的运用复习
9. 为方便输入添加下拉选项框:按“数据选项卡”—“数据工具组”—“数据验证”,在弹出的对话框中选择“设置”—“允许”—“序列”,在“来源”设置输入相关区域后确定。
Day10
1. 今天开始学习查找索引功能,总体而言是以下几个函数的配合嵌套应用——交叉查询
2. 函数offset(起点单元格,偏移几行,偏移几列,取几行,取几列),当要取的行数、列数为1时,可省略。返回的可做引用(单元格区域)可取数(单一单元格就是显示值)。Offset函数第一参数既可以是单元格,也可以时单元格区域。函数偏移的行数、列数,取几行取几列可以是正数也可以是负数。如果第二、三参数为负数,则表示往上数、往左数;第四第五参数为负,则表示往上往左取若干行或列。
3. Index(单元格区域,第几行,第几列),返回第几行第几列交叉处单元格的值。如果第二参数或第三参数为零,那么就返回整列、整行。另外,它的计算结果不只是值,还可作为单元格引用。
4. Match(查找值,数据列表,匹配模式),返回查找值在该序列中的排行。第三参数的匹配模式有三种,为0时,表示精确匹配;为1时,模糊匹配,查找小于或等于查找值的最大值,且第二参数此时须按升序排列;为-1时,模糊匹配,查找大于或等于查找值的最小值,且第二参数此时须按降序排列。
5. Vlookup与Hlookup时双胞胎,前者纵向垂直查找,后者是横向水平查找
6. Vlookup函数:
(1)第四参数为0或false时,表示精确查找;
(2)第四参数为true或1或省略时,表示模糊查找。此时,如果找不到精确匹配值,则函数返回小于查找值的最大值,且要求查找区域必须按升序排列第一列的值。
(3)如果有多个符合条件的值,精确查找时,返回第一个值所对应的值;模糊查找时,会返回最后一个值所对应的值。
Day11
1.函数find(要查找的字符,查找单元格,指定开始进行查找的字符数),返回找到的第一个匹配字符的位置(不管参数3是几都是从起始处返回字符编号),参数1不能含通配符,参数3省略时默认值1
2.函数right(指定要提取子串的字符串,从右边起要选取的字符数),返回从字符串右端取指定个数字符
3.上述函数常与Len(text)(得到字符串的长度)一起用
4.函数get.workbook(信息类型,工作簿名称),只有excel有此功能,wps无,可以提取各工作表名称。参数1有四种,如下表;参数2可省略,省略时默认为当前工作簿
![429716dbad8de969c5d84ba361d85fd7.png](https://i-blog.csdnimg.cn/blog_migrate/b0cda93fb375c8d9e5fb80b24c37348a.png)
5.复习indirect()函数以及第二参数两种样式
6.新的函数IFNA(公式1,值2),表示公式1计算结果不是#N/A,就取此公式1计算结果,否在就取“值2”
7.函数iferror():IFERROR(value, value_if_error),表示判断value的正确性,如果value正确则返回正确结果,否则返回value_if_error。
8.通过“公式”—“定义名称”,自己取好名称(如“工作表列表”),在引用位置栏输入相应公式,可以作为一个泛指的公式通用。在单元格输入“=工作表列表”即可 P112-113有视频讲解
9.函数Subtotal(功能代码,数值区域),返回一个数据列表或数据库的分类汇总。参数1中“1-11”与“101-111”的区别:1-11:包含手动隐藏的值101-111:不包含手动隐藏的值,暨以可见单元格为统计对象。
10.插入—表格—选区域—改表名,设立【表格】,然后点击快捷功能“汇总行”,这样表格的最末行后会添加一个“汇总”行
11.超链接的应用——函数hyperlink(连接地址,要显示的文字),跟添加超链接功能一样。有几个注意点:
一是两个参数多要加注英文状态下引号””;二是参数1一定要写清楚路径,即使是本工作簿的其他工作表,如E:工作预算预算下达.xlsx]福利基金基础数据!A2
Day12
1. lookup(查找值,查找区域,结果区域)
(1)相当于vlookup的模糊查找功能,查找区域必须按升序排列,返回与查找值对应的查找区域的结果区域的值,即返回的是参数2结果区域的值;会忽略掉查找列表中的错误值,当再查找区域找不到参数1查找值时,返回最后一个非查找值对应的值(N/A错误自动忽略)。
(2)多条件:=lookup(1,0/((条件1)*(条件2)*……(条件N)),结果区域),当条件中有一个为false,相当于分母为0,返回错误#DIV/0;如果全为true,则相当于1,look返回最后一个0对应的结果区域的值。
2. countif(区域,条件):返回满足参数2条件的参数1区域中的个数。如果用countif()=0,则返回true或者false,前者运算时表示1,后者表示0;一般参数2条件是一个单元格,也可以是单元格区域,单元格区域的话就是一个数组,依次往参数1区域里去匹配
3. F4的快捷功能:可以在公式单元格区域任一位置,按F4,可直接赋予绝对引用功能
4. Frequency(数据区域,间隔值),返回按参数2的区域段在参数1数据区域的数量。
(1)这里涉及一个数组公式的应用,编辑好公式后不要按enter回车键,按ctrl+shift+enter,会出现{}的数组公式,返回一组答案,但是至于是不是自动返回,书本在P127扩展阅读部分,至于提取唯一值列表(顺序)不是特别能理解透彻,似懂懵懂??P125
(2)当第二参数区间分割点有重复值时,后面重复的分割点计算结果为0。所以一般区间是这么分割的:X<=a a<X<=b X>b 注意等号=的位置
(3)编辑数组公式操作流程:选择包含数组公式的单元格——编辑数组公式内容(进入单元格编辑或编辑框时,{}不显示,在确定后再自动加上)——按 Ctrl+Shift+Enter确定,就自动垂直排列显示结果
例如,在单元格区域(A1:D4)中键入了数组公式,此时只需要选择单元格区域(A1:D4)中的任一单元格,编辑并按 Ctrl+Shift+Enter 确定后,单元格区域(A1:D4)中的数组公式将同时被修改。