设置金额大写的函数_SUBTOTAL嵌套SUMIF使用

会计小白的EXCEL学习笔记 　　随着信息化的发展，原本浑浑噩噩想着混吃等死的二胎妈妈的我，经过这段时间设计合同台账，突然觉得很丧，妥妥的强烈危机感啊~于是乎下定决心得加快提升自己了。 　　那么对于财会行业，excel是必备技能，一方面可以提升工作效率好有更多的时间去做幼小衔接教育，另一方面也是为了能在家庭和工作中活得一种平衡，工作的如鱼得水与家庭的相敬如宾在某种程度上是相得益彰的。 　　所以买了本豆瓣好评实战书，开启我的信息化学习第一篇。可能做不到每日学，坚持吧！ 　　Day1 　　1.单格如果是文本格式，即使以“=”开头，excel也不会视其为公式，一定要常规格式c 　　2.ctrl+f 查找 ctrl+h 替换 　　3.英文半角状态下“,”的作用—-多个单格区域求和。比如：sum（A1:B3）变为 sum（A1：B3,E1：E6） 　　4.只需要一个参数的函数举例：

　　5.只需要一个参数的函数举例：

　　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公式：

　　2. 空格的一个作用：交集

　　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. 运算顺序：

　　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. 快速录入常用快捷键（有几个没弄明白）

　　此截图来源于龙老师的书本 　　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） 不会统计手动隐藏的行 　　两种方式相同点：

　　昨日学习补充： 　　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表示第几个区域。

　　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可省略，省略时默认为当前工作簿

　　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）中的数组公式将同时被修改。