subtotal函数怎么用_excel统计

subtotal函数怎么用_excel统计会计小白的EXCEL学习笔记随着信息化的发展,原本浑浑噩噩想着混吃等死的二胎妈妈的我,经过这段时间设计合同台账,突然觉得很丧,妥妥的强烈危机感啊~于是乎下定决心得加快提升自己了。那么对于财会行业,excel是必备技能,一方面可以提升工作效率好有更多的时间去做幼小衔接教育,另一方面也是为了能在家

会计小白的EXCEL学习笔记   随着信息化的发展,原本浑浑噩噩想着混吃等死的二胎妈妈的我,经过这段时间设计合同台账,突然觉得很丧,妥妥的强烈危机感啊~于是乎下定决心得加快提升自己了。   那么对于财会行业,excel是必备技能,一方面可以提升工作效率好有更多的时间去做幼小衔接教育,另一方面也是为了能在家庭和工作中活得一种平衡,工作的如鱼得水与家庭的相敬如宾在某种程度上是相得益彰的。   所以买了本豆瓣好评实战书,开启我的信息化学习第一篇。可能做不到每日学,坚持吧!   Day1   1.单格如果是文本格式,即使以“=”开头,excel也不会视其为公式,一定要常规格式c   2.ctrl+f 查找 ctrl+h 替换   3.英文半角状态下“,”的作用—-多个单格区域求和。比如:sum(A1:B3)变为 sum(A1:B3,E1:E6)   4.只需要一个参数的函数举例:
subtotal函数怎么用_excel统计
subtotal函数怎么用_excel统计   5.只需要一个参数的函数举例:
subtotal函数怎么用_excel统计
subtotal函数怎么用_excel统计   6.日期和时间本质上是数字,可以进行加减法,并且直接通过公式会默认为一个数,可以通过text(A1,“yyyy-mm-dd”)函数转化成文本进行转换;一个汉字两个字节(常识)。   7.后期功课:(1)什么是数组公式,哪些可用于日常?(2)归纳总结常用函数。   Day2   1. Left(文本,要提取的字符数) :有两种文本字符串用双引号括起来,单格不能用双引号括起来。比如left(“”,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公式:
subtotal函数怎么用_excel统计
subtotal函数怎么用_excel统计   2. 空格的一个作用:交集
subtotal函数怎么用_excel统计
subtotal函数怎么用_excel统计   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. 运算顺序:
subtotal函数怎么用_excel统计
subtotal函数怎么用_excel统计   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. 快速录入常用快捷键(有几个没弄明白)
subtotal函数怎么用_excel统计
subtotal函数怎么用_excel统计   此截图来源于龙老师的书本   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) 不会统计手动隐藏的行   两种方式相同点:
subtotal函数怎么用_excel统计
subtotal函数怎么用_excel统计   昨日学习补充:   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表示第几个区域。
subtotal函数怎么用_excel统计
subtotal函数怎么用_excel统计   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可省略,省略时默认为当前工作簿
subtotal函数怎么用_excel统计
subtotal函数怎么用_excel统计   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)中的数组公式将同时被修改。

2024最新激活全家桶教程,稳定运行到2099年,请移步至置顶文章:https://sigusoft.com/99576.html

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请联系我们举报,一经查实,本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处:https://sigusoft.com/62474.html

(0)
上一篇 2024年 8月 28日 下午4:39
下一篇 2024年 8月 28日

相关推荐

关注微信