这是网友提出的一个问题：对一个数据表进行排序，今后每次增加新的数据时，刷新数据表就能自动纳入新增行重新排序，这个怎么做到？听上去很智能，很多人首先想到是不是要用 VBA。从我个人角度，是非常鼓励并且喜欢用 VBA 来解决问题的，但对于没有编程基础的同学来说，学习 VBA 就有点困难。那么，今天就教大家一种非常亲民、一学就会的解决方法，不用编程，不用函数公式，点击几下鼠标，满打满算 30 秒内保准完成。案例：将下图 1 按分数从高到低排序；如果在表格下方增加新的数据行，刷新就能自动纳入新行重新排序。效果如下图 2 所示。解决方案：1. 选中数据表的任意区域 --> 选择菜单栏的“数据”-->“从表格”2. 弹出的对话中会自动选择整个数据表区域作为来源，默认勾选了“表包含标题”--> 点击“确定”然后就会出现一个新的窗口，界面如下图这样。看到这里，大家应该都明白了，咱们今天用的是 Power Query。3. 选中“分数”列 --> 选择菜单中“排序”区域的“降序排列”现在可以看到“分数”单元格右边出现了一个向下箭头，分数已经按降序排列好了。4. 选择菜单栏的“关闭并上载”现在就关闭了 Power Query，重新回到了 Excel，我们发现工作簿中多出来一个新的工作表，其中的内容就是刚才用 Power Query 排序的表格。5. 现在回到 Sheet1，在原来的数据表下面增加一行新的数据6. 回到 Sheet2 的 Power Query 表格，选中表格任意区域 --> 右键单击 --> 在弹出的菜单中选择“刷新”，表格就纳入新增行重新按分数降序排列好了。Excel学习世界转发、在看也是爱！

当Excel表格中存在不同大小的合并单元格时，进行排序可能会出现一些难题。因为合并单元格会影响数据的布局，导致排序结果不符合预期。下面我将详细介绍如何在包含不同大小合并单元格的Excel表格中进行排序，希望对您有所帮助。1. 确定排序规则在进行排序之前，首先需要明确排序的规则。根据您的需求，确定按照哪一列或者多列进行排序，以及是升序还是降序排列。这将有助于我们后续进行步骤。2. 取消合并单元格由于合并单元格会影响排序结果，我们可以首先将所有的合并单元格取消，然后进行排序操作。请按照以下步骤执行：选中包含合并单元格的区域。在Excel菜单栏中选择“开始”选项卡。在“对齐”组中点击“取消合并单元格”按钮。这样，所有的合并单元格将被取消，数据将回到原始的单元格布局。3. 复制排序列在进行排序之前，我们可以通过复制排序列的方式来保留原始数据。这样，在排序后我们还可以参照原始数据进行比对，确保排序结果的准确性。选中需要排序的列。右键点击选中的列，选择“复制”。右键点击目标位置，选择“粘贴”。这样，我们就在目标位置创建了排序列的副本。4. 执行排序操作现在，我们可以进行实际的排序操作了。请按照以下步骤执行：选中需要排序的区域（包括排序列的副本）。在Excel菜单栏中选择“数据”选项卡。在“排序和筛选”组中点击“排序”按钮。弹出一个排序对话框，在这里您可以设置排序规则。请注意以下几点：确认排序区域范围是否正确。选择要排序的列，根据您的需求设置排序顺序（升序或降序）。如果有多个排序条件，可以点击“添加级别”按钮进行设置。点击“确定”按钮，完成排序操作。5. 比对排序结果与原始数据由于我们在第3步中复制了排序列的副本，我们可以将排序结果与原始数据进行比对，确保排序的准确性。如果排序结果符合您的预期，则表示排序成功。6. 重新合并单元格如果您需要恢复原来的合并单元格布局，可以按照以下步骤重新合并单元格：选中需要重新合并的区域。在Excel菜单栏中选择“开始”选项卡。在“对齐”组中点击“合并单元格”按钮。这样，选中的区域将重新合并为单个单元格。7. 保存并备份数据最后，不要忘记保存您的工作，并进行数据备份。这样，即使出现意外情况，您也可以恢复到之前的版本。总结起来，对于包含不同大小合并单元格的Excel表格进行排序，您需要按照确定排序规则、取消合并单元格、复制排序列、执行排序操作、比对排序结果与原始数据、重新合并单元格以及保存备份数据的步骤进行操作。这样，您就可以在保持数据完整性的同时，成功地将表格进行排序。

第二十一讲EXCEL你从没注意的行号ROW和列号COLUMN，犹如神助攻原创2021-12-22 办公神操作在日常工作中，我们经常用EXCEL函数处理表册，函数的运用，大幅度提升了我们的工作效率。笔者集合工作实际，把最常用的EXCEL函数进行逐一详细讲解，课程中有大量案例，为便于朋友们更加深入了解各个函数的用法，将于每天上午7:00同步发布视频教程和图文教程（包含公式）。只要不懈努力和不断实践，通过30天的系统学习，你也能成为EXCEL函数高手，从此告别加班，让同事和朋友刮目相看。今天，我将和大家一起分享行号ROW和列号COLUMN函数的基本知识和案例，在EXCEL中你觉得不起眼的函数，往往能发挥出你意想不到的效用。一、ROW函数基础知识（一）ROW函数定义ROW函数是返回公式所在行的行数。语法：ROW(参数)参数：是指需要得到其行号的单元格或单元格区域，不能引用多个区域。如果省略，则是对函数ROW所在单元格的引用；如果该参数为一个单元格区域，并且函数ROW作为垂直数组输入，则行号以垂直数组的形式返回，如：ROW(A2:A5)，返回的是2。ROW函数一般不单独使用，而是以其他函数的参数出现，特别是在查找和引用函数中，ROW函数发挥着重要的定位作用。（二）ROW函数案例实践明白了ROW函数的用法和基本知识，下面，我们进行几个案例分析。1.为职工信息表填充序号某单位在登记职工信息表时，记录的行数比较多，现需要自动生成序号，我们可以用ROW函数来完成。在A3单元格输入公式：=ROW()-2，按回车键并往下复制到需要自动编排序号的单元格，即可自动填充序号，并且删除行也会自动按顺序编排。见下图：本例中，A3单元格ROW()中的参数省略了，默认返回当前所在行的行号3，减去2表示当前行上面还有两行不需要编排序号。也可以直接用ROW(A1)来填充序号，表示从1开始往下编号。有时候，在新增职工的信息时，中间难免会出现空行，为了使序号连续，需要用IF和COUNTA函数嵌套的方式，统计非空单元格并进行判断，公式可写成：=IF(B3<>"",COUNTA($B$3:B3),"")，这样就可以跳过空行连续填充序号。见下图：2.用ROW函数对人员进行3人一组分组某单位安排职工外出开展志愿服务活动，将随机排序的职工每3人分成一组，进行相同人数分配，可利用MOD和ROW函数创建循环序列，每隔3人循环一次，这样，系统会将所有职工按照3人平均分成一个组，从而实现快速分组的目的。在E3单元格输入公式：=MOD(ROW(),3)+1，拖动到需要的位置，即可进行快速分组。见下图：本例中，ROW()的意思是取行号，括号中没有内容就是取本行号，如果在第一行中输入，就返回1，第二行返回2，以此类推，然后用MOD取余数。如果公式在第一行：=MOD(ROW(),3)=MOD(1,3)=1，就是1除以3的余数等于1；如果公式在第二行：=MOD(ROW(),3)=MOD(2,3)=2，就是2除以3的余数等于2；如果公式在第三行：=MOD(ROW(),3)=MOD(3,3)=0，就是3除以3的余数等于0；下拉时，这一段将产生0,1,2的循环，再加上1，就是1,2,3的循环。二、COLUMN函数基础知识（一）COLUMN函数定义COLUMN函数是返回公式所在列的列数。语法：COLUMN(参数)参数：是指需要得到其列号的单元格或单元格区域，不能引用多个区域。如果省略，则是对函数COLUMN所在单元格的引用；如果该参数为一个单元格区域，并且函数COLUMN作为水平数组输入，则列号以水平数组的形式返回，如：COLUMN(A2:D5)，返回的是2。COLUMN函数一般不单独使用，而是以其他函数的参数出现，特别是在查找和引用函数中，COLUMN函数发挥着重要的定位作用。COLUMN和ROW函数的关系：COLUMN和ROW函数在动态引用列号和行号方面非常有用，它们俩的关系和VLOOKUP及HLOOKUP的关系有点相似，COLUMN函数用于返回指定引用的列号，ROW函数用于返回指定引用的行号，COLUMN和ROW函数的参数可以省略，当参数被省略时，函数返回公式单元格所在的列号和行号。（二）COLUMN函数案例实践：为职工周考核填充周次某单位对职工的考核实行每周考核，每周要填报一次考核情况，需要对周次进行填充。在A3单元格输入公式：=TEXT(COLUMN(A1),"第0周")，按回车键并往右复制到需要填充周次的单元格即可。见下图：本例中，首先用COLUMN(A1)取得当前开始列的列号1，然后用TEXT函数为列号1添加文字“第0周”，往右拖动复制公式会变成COLUMN(B1)，以此类推。以上就是行号ROW和列号COLUMN函数的几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。个人建议：在学习EXCEL函数时，首先要熟悉函数的功能和语法，尽量多写，通过反复书写和实践，对照文中的案例，举一反三，我相信，你的EXCEL技巧会得到快速提升，日积月累，必成大器！第二十二讲EXCEL黄金组合函数INDEX和MATCH，刷新了很多不可能原创2021-12-23 办公神操作今天，我将和大家一起分享EXCEL黄金组合函数INDEX和MATCH的基本知识和案例，在EXCEL中，单个函数或许解决不了多少问题，但是用好函数组合，能发挥出极大的效用，解决很多工作上的难题。一、INDEX函数基础知识（一）INDEX函数定义INDEX函数是返回表或区域中的值或值的引用，INDEX函数有两种形式：数组形式和引用形式。数组形式通常返回数值或数值数组，引用形式通常返回引用。1.数组形式语法：INDEX(区域,行,列)返回表格或数组中的元素值，此元素由行号和列号的索引值给定。当函数INDEX的第一个参数为数组常量时，使用数组形式。区域：必需参数，是指单元格区域或数组常量，如果数组只包含一行或一列，则相对应的参数“行”或“列”为可选参数。如果数组有多行和多列，但只使用参数“行”或“列”，函数INDEX返回数组中的整行或整列，且返回值也为数组。行：必需参数，是指选择数组中的某行，函数从该行返回数值，如果省略，则必须有“列”参数。列：可选参数，是指选择数组中的某列，函数从该列返回数值，如果省略，则必须有“行”参数。注意事项：—如果同时使用了“行”和“列”参数，INDEX将返回“行”和“列”交叉处单元格中的值。—如果将“行”或“列”设置为0，INDEX将分别返回整列或整行的值数组。要将返回的值用作数组，请在行的水平单元格区域和列的垂直单元格区域以数组公式（数组公式：数组公式对一组或多组值执行多重计算，并返回一个或多个结果。—“行”和“列”必须指向数组中的某个单元格，否则INDEX将返回#REF!错误值。2.引用形式语法：INDEX(引用区域,引用行,引用列,选择区域)返回指定的行与列交叉处的单元格引用，如果引用由不连续的选定区域组成，可以选择某一选定区域。引用区域：必需参数，是指对一个或多个单元格区域的引用，如果引用是一个不连续的区域，必须将其用括号括起来。如果引用中的每个区域只包含一行或一列，则相应的参数“引用行”或“引用列”分别为可选项。例如：对于单行的引用，可以使用函数INDEX(引用区域,,引用列)。引用行：必需参数，是指引用某行的行号，函数从该行返回一个引用。引用列：可选参数，是指引用中某列的列标，函数从该列返回一个引用。选择区域：可选参数，是指选择引用中的一个区域，以从中返回“引用行”和“引用列”的交叉区域。选中或输入的第一个区域序号为1，第二个为2，依此类推。如果省略该参数，则函数INDEX使用区域1。例如：如果引用的单元格为(A1:C5,D1:F5,G1:H5)，则区域1为A1:C5，区域2为D1:F5，区域3为G1:H5。（二）INDEX函数案例实践明白了INDEX函数的用法和基本知识，下面，我们进行几个案例分析。1.查找最后一条不为空的记录某单位在登记职工信息表时，记录的行数比较多，现需要查询最后一个职工的信息，可以使用MATCH函数在数据列中查找通配符，得到最后一个数据所在的位置，再使用INDEX函数提取该数据。在G4单元格输入公式：=INDEX(A:A,MATCH("*",A:A,-1))，按回车键并往右复制到最后一列数据所在的单元格，即可查找到最后一个职工的所有信息。见下图方法一：本例中，也可使用OFFSET函数替代INDEX函数，公式可写成：OFFSET(A1,MATCH("*",A:A,-1)-1,)。知识拓展：OFFSET函数是以指定的引用为参照系，通过给定偏移量得到新的引用，语法为：OFFSET(参照区域,偏移行数,偏移列数,引用区域行数,引用区域列数)，后续课程中我们会继续详细讲解。本例中，在使用MATCH函数进行匹配时，可以不使用通配符，而使用一个按照拼音排序极为靠后的汉字进行匹配，比如使用新华字典中排列靠后的汉字“坐”，公式可写成：=INDEX(A:A,MATCH("坐",A:A))。使用LOOKUP函数也可以得到最后一条记录，公式可写成：=LOOKUP("坐",A:A)。2.在重复数据的列中提取不重复的信息某电器商在对一月内各种电器销售量进行汇总时，电器销售一单就统计一单，会出现很多重复的数据，现在需要把同一种电器提取出来计算总销售量，多数人会应用删除重复项的功能进行处理，其实用INDEX函数进行引用查找即可。在F3单元格输入公式：=INDEX($B$3:$B$32,MATCH(0,COUNTIF(F$2:F2,$B$3:$B$32),0)&"")，按【Ctrl+Shift+Enter】三键组合，拖动到需要的位置，直到出现空白内容为止，即可过滤重复值，只显示不重复的数据，然后用SUMIF函数计算各种电器的销售量。见下图：本例中，COUNTIF(F$2:F2,$B$3:$B$32)用于返回的值与上一个单元格F2的值进行比较，以判断是否出现过，然后用MATCH函数返回要提取内容所在的行号，再用INDEX函数提取$B$3:$B$32区域的内容。二、MATCH函数基础知识（一）MATCH函数定义MATCH函数是返回指定数值在指定数组区域中的位置。语法：MATCH(查找值,查找区域,匹配值)查找值：必需参数，是指需要在“查找区域”中查找的值。例如，通过身份证号码查找某人的姓名，则应该将身份证号码作为查找值，但实际上需要的是姓名。该参数可以为数字、文本、逻辑值或对数字、文本、逻辑值的单元格引用。查找区域：必需参数，是指要搜索的单元格区域。匹配值：可选参数，数字-1、0或1，该参数指定如何在“查找区域”中查找第1个参数的内容，默认值为1。MATCH匹配值设置：设置为1或省略：表示MATCH函数会查找小于或等于“查找值”的最大值，“查找区域”参数中的值必须按升序排列，例如：-2,-1,0,1,2,...,A-Z,FALSE,TRUE。设置为0：表示MATCH函数会查找等于“查找值”的第一个值，“查找区域”参数中的值可以按任何顺序排列。设置为-1：表示MATCH函数会查找大于或等于“查找值”的最小值，“查找区域”参数中的值必须按降序排列，例如：TRUE,FALSE,Z-A,...2,1,0,-1,-2,...等。（二）MATCH函数案例实践：从全班成绩表里查找总分最低的学生某班级需要查找成绩最低分的学生姓名，以便有针对性的辅导，本例可以运用INDEX、MATCH和MIN函数组合进行查找。例子中的姓名、成绩等信息均为随机生成，只为讲解函数的用法，别无他意。在G3单元格输入公式：=INDEX(A3:A32,MATCH(MIN(E3:E32),E3:E32,))，按回车键即可查找到成绩最低分的学生姓名；在H3单元格输入公式：=VLOOKUP(G3,A3:E32,5,FALSE)即可根据查找出的姓名查到最低分。见下图：本例中，首先用MIN(E3:E32)查找总分最低分，作为MATCH的第1个参数“查找值”，然后E3:E32为MATCH函数的第2参数“查找区域”，MATCH函数的第3参数省略，查找小于或等于“查找值”的最大值，得到最低分的位置4，最后用INDEX函数的数组形式INDEX(A3:A32,4)返回A列第4行单元格的值。以上就是INDEX和MATCH函数的几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。第二十三讲EXCEL极品引用函数OFFSET，复杂数据处理怎能无它？原创2021-12-24 办公神操作今天，我将和大家一起分享EXCEL中极品函数OFFSET的基本知识和案例，说起OFFSET函数，真的是像雾像雨又像风，从复杂的数据汇总、到数据透视表再到高级动态图表，都离不开OFFSET函数的默默付出。一、OFFSET函数基础知识OFFSET函数定义：OFFSET函数是以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域，并可以指定返回的行数或列数。语法：OFFSET(参照区域,偏移行数,偏移列数,引用区域行数,引用区域列数)参照区域：是指作为偏移量参照系的引用区域，必须是对单元格或相连单元格区域的引用，否则将返回错误值#VALUE!。偏移行数：是指相对于“参照区域”的左上角单元格上（下）偏移的行数，如果使用3作为参数，则说明目标引用区域的左上角单元格比“参照区域”低3行，当行数为正数时，表示在起始引用的下方，当行数为负数，表示在起始引用的上方。偏移列数：是指相对于“参照区域”的左上角单元格，左（右）偏移的列数，如果使用3作为参数，则说明目标引用区域的左上角的单元格比“参照区域”靠右3列，当列数为正数时，表示在起始引用的右边，当列数为负数，表示在起始引用的左边。引用区域行数：是指要返回的引用区域的行数，可以为负数，表示当前行向上的N行。引用区域列数：是指要返回的引用区域的列数，可以为负数，表示当前列向左的N行。注意事项：—如果“偏移行数”和“偏移列数”超出工作表边缘，函数OFFSET返回错误值#REF!。—如果省略“引用区域行数”或“引用区域行数”，则其值与“参照区域”相同。—OFFSET函数实际上并不移动任何单元格或更改选定区域内容，它只是返回一个引用，可用于任何需要将引用作为参数的函数。例如，公式SUM(OFFSET(A8,2,1,4,3))，将计算比单元格A8靠下2行并靠右1列的4行3列的区域的总和，即B10:D13单元格区域。二、OFFSET函数案例实践明白了OFFSET函数的用法和基本知识，下面，我们进行几个案例分析。（一）计算最近N天销售总收益某电器商把上个月每天的销售数据按时间顺序进行了统计，现在需要对指定天数的销售收益进行求和，可以使用OFFSET函数在收益列进行指定天数的数据匹配和查找，然后用SUM函数计算总收益。在H3单元格输入公式：=SUM(OFFSET(E2,COUNT(E:E),,-G3,))，按回车键，即可计算出最近10天的总收益（本例中指11月21日—11月30日）。见下图：本例中，OFFSET(E2,COUNT(E:E),,-G3,)是指以E2单元格为参照，用表达式COUNT(E:E)统计E列中包含数据的单元格总数，省略了列偏移量，表示与E2同在一列，-G3表示从E列的最后一个数据向上查找指定数量的数据共10行，省略了引用列数量，表示与E2同在一列，然后用OFFSET函数返回符合匹配的数据组，最后用SUM函数进行求和。如果要改变公式的计算方式，比如计算平均值，直接将SUM函数改成AVERAGE函数即可。（二）对同一列多个合并单元格按顺序编号某年级统计了学生的考试成绩，需要对相邻同一班级的学生编排同一个序号，并按顺序编排所有学生的序号。对于同一列合并单元格编排连续的序号，可以使用OFFSET函数在序号列中进行单元格区域的引用，然后使用COUNT函数统计该单元格区域中已有的序号个数，在此基础上加1，即可得到一个连续的序号。选中所有需要编排序号的合并单元格，输入公式：=COUNT(OFFSET($A$2,,,ROW()-2,))+1，按【Ctrl+Enter】组合键即可按顺序填充所有合并单元格序号。见下图：本例中，OFFSET($A$2,,,ROW()-2,)表示以A2单元格为参照，行和列均省略不偏移，ROW()-2表示当前单元格位置为第3行，减去2即从1开始编排。前面讲过，合并单元格中的内容存储在第1个单元格，其余单元格为空，利用合并单元格的这个特点，本例可以COUNTA函数来进行，公式可写成：=COUNTA($B$3:B3)，这是最简单的用法，虽然使用其他函数也可实现按顺序编号，但是公式较为繁琐，并且不能自动忽略错误值，不推荐使用。提示：在多个不规则的合并单元格中输入公式时，需要先选择所有合并的单元格，然后再输入公式，而不是在第1个合并单元格中输入公式后在采用复制公式的方式，多个不规则合并单元格会提示单元格大小不相同。（三）根据学号提取各科成绩某年级统计了学生的考试成绩，需要根据学号提取学生的姓名和所有考试成绩，可以使用OFFSET和MATCH函数来实现。选中所有需要提取数据的单元格区域，本例即I3:M3单元格区域，输入公式：=OFFSET($A$2,MATCH($H$3,$A$3:$A$32,0),MATCH(I$2,$B$2:$F$2,0))，按【Ctrl+Enter】组合键即可根据学号提取学生的姓名和所有考试成绩。见下图：本例中，以A2单元格为参照，用MATCH($H$3,$A$3:$A$32,0)查找H3单元格的学号在A列中的行号，作为OFFSET函数的第2个参数，即偏移行数；用MATCH(I$2,$B$2:$F$2,0)查找I2单元格（即姓名）在第二行中的列标，作为OFFSET函数的第3个参数，即偏移列数；省略引用行数和列数，本例得到OFFSET(A2,5,2)，即得到学号对应的信息。以上就是极品引用函数OFFSET的几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。第二十四讲EXCEL引用单元格地址函数INDIRECT和ADDRESS经典用法原创2021-12-25 办公神操作今天，我将和大家一起分享单元格地址引用函数INDIRECT和ADDRESS的基本知识和案例，用好INDIRECT和ADDRESS函数，能解决很多单元格引用的问题。一、INDIRECT函数基础知识（一）INDIRECT函数定义INDIRECT函数是指返回由文本字符串指定的引用，如果需要更改公式中对单元格的引用，而不更改公式本身，则使用该函数，INDIRECT函数为间接引用。语法：INDIRECT(引用单元格,引用类型)引用单元格：是指对单元格的引用，此单元格可以包含如A1-样式的引用、R1C1-样式（R后面的数字表示行，C后面的数字表示列）的引用、定义为引用的名称或对文本字符串单元格的引用。如果该参数不是合法单元格的引用，函数INDIRECT返回错误值#REF!或#NAME?。如果该参数是对另一个工作簿的引用（外部引用），工作簿必须被打开，如果源工作簿没有打开，函数INDIRECT返回错误值#REF!。引用类型：为逻辑值，指明包含在“引用单元格”中的引用类型。如果值为TRUE或省略，“引用单元格”被解释为A1-样式的引用；如果值为FALSE，“引用单元格”被解释为R1C1-样式的引用。注意事项：要使用这个函数，主要记住以下两点就可以轻松掌握：—函数的参数：单元格名称或符合单元格地址的格式，如A1、$A$1、“EXCEL”等的字符串。—函数的返回值：参数所指定的单元格的值。当在创建公式时，对某个特定单元格进行了引用，如果使用“剪切”命令或插入、删除行或列使该单元格发生了移动，则单元格引用将被更新。如果需要使得无论单元格上方的行是否被删除或是单元格是否移动，都在公式上保持相同的单元格引用，请使用语法如=INDIRECT("A9")进行引用。（二）INDIRECT函数案例实践明白了INDIRECT函数的用法和基本知识，下面，我们进行几个案例分析。1.拆分合并单元格并填充数据有的朋友喜欢在表格中使用合并单元格，将一些具有相同属性的记录合并在一起，但是合并单元格的使用，常常会给公式的使用带来不便，此时就需要对合并单元格进行拆分，拆分后的单元格中均应该包含原合并单元格中的内容。本例中需要将某学校各班级的合并单元格进行拆分，拆分后只有第一个单元格中含有数据，其余单元格均为空白，这时就需要使用公式对空白单元格进行填充。选中所有需要拆分填充数据的单元格，输入公式：=IF(A3<>"",A3,INDIRECT("B"&ROW()-1))，按【Ctrl+Enter】组合键，即可根据合并单元格对拆分后的单元格进行数据填充。见下图：我们知道，合并单元格的地址等于其左上角单元格的地址，合并单元格中的其他单元格值为空白或0。本例中，首先使用IF(A3<>"",A3判断合并单元格左上角的A3单元格是否有数据，作为IF函数的第1个参数“真值”，然后用INDIRECT("B"&ROW()-1)判断B列中当前单元格的行号，作为IF函数的第2个参数“假值”，如果当前合并单元格没有数据，说明不是左上角的单元格，所有要用当前行ROW()减去1，得到上一个单元格的值。合并单元格的拆分，使用引用函数OFFSET同样可以解决，公式可写成：=IF(A3<>"",A3,OFFSET(B2,0,))。本例也可以用最简单的IF函数解决，公式可写成：=IF(A3<>"",A3,B2)，三个公式结果完全相同。2.多区域统计总分在200分及以上的学生人数某班级的学生成绩表记录了全班学生的考试成绩，需要统计的数据位于多个不连续的单元格区域中，初步考虑用COUNTIF函数进行统计，但是COUNTIF函数的第1个参数只支持一个单元格区域，不支持常量数组或者联合区域，本例可以使用INDIRECT函数实现COUNTIF函数中使用多个区域作为参数，最后用SUM对符合条件的个数进行求和。F14单元格输入公式：=SUM(COUNTIF(INDIRECT({"E3:E12","J3:J12","O3:O12"}),">=200"))，按回车键即可计算出总分在200分及以上的学生人数。见下图：在EXCEL中，有一些函数仅仅支持1个单元格区域作为参数，但在实际工作中，常常需要使用多个不连续的区域作为参数，这时就可以通过INDIRECT函数实现以多个单元格作为参数的目的。二、ADDRESS函数基础知识（一）ADDRESS函数定义ADDRESS函数是指按照给定的行号和列标，建立文本类型的单元格地址。语法：ADDRESS(行号,列号,引用类型,引用样式,外部工作表名称)行号：必需参数，指定要在单元格引用中使用的行号，为数值。列号：必需参数，指定要在单元格引用中使用的列号，为数值。引用类型：可选参数，指定要返回的引用类型，为数值。—1或省略：引用类型为绝对值，如$A$1、R1C2；—2，引用类型为绝对行号，相对列标，如A$1、R1C[2]；—3，引用类型为相对行号，绝对列标，如$A1、R[1]C2；—4，引用类型为相对值，如A1、R[1]C[2]。引用样式：可选参数，是一个逻辑值，指定A1-或R1C1-引用样式。在A1-样式中，列和行将分别按字母和数字顺序添加标签。在R1C1-引用样式中，列和行均按数字顺序添加标签。如果参数A1-为TRUE或被省略，则ADDRESS函数返回A1-样式引用；如果为FALSE，则ADDRESS函数返回R1C1-样式引用。外部工作表名称：可选参数，为文本值，指定要用作外部引用的工作表名称。例如：公式=ADDRESS(1,1,,,"工作表")返回“工作表！$A$1”。如果省略该参数，则不使用工作表名称，函数返回的地址引用当前工作表上的单元格。当在创建公式时，对某个特定单元格进行了引用，如果使用“剪切”命令或插入、删除行或列使该单元格发生了移动，则单元格引用将被更新。如果需要使得无论单元格上方的行是否被删除或是单元格是否移动，都在公式保持相同的单元格引用，请使用语法如=ADDRESS("A9")进行引用。（二）ADDRESS函数案例实践：计算一班的总分最高分某年级把各班考试成绩记录在了多个表，在计算某班级总分最高分时，首先需要确定引用的数据所在的工作表，然后对该工作表中的数据求最大值。本例采用ADDRESS函数来设置引用的单元格地址，然后使用INDIRECT函数将地址转换为单元格引用，最后使用MAX函数计算最大值。在B3单元格输入公式：=MAX(INDIRECT(ADDRESS(3,5,1,,A3)&":"&ADDRESS(12,5,,1)))，按回车键，即可计算一班的总分最高分，如果需要计算平均分，只需要把最外层的MAX函数修改为AVERAGE就可以了。见下图：ADDRESS函数返回的结果为文本型数据，可以直接当作文本处理，本例将两个ADDRESS函数返回的单元格地址使用文本连接符&组成跨表引用的单元格地址。本例也可以使用INDIRECT函数得到单元格区域的引用，公式可写成：=MAX(INDIRECT(A3&"!E3:E12"))，两个公式结果完全相同。在引用的单元格比较简单时，直接使用INDIRECT函数比较简单，当引用的单元格需要经过复杂的运算才能得到时，使用ADDRESS函数作为INDIRECT函数的参数则会使计算过程变得更为简单。以上就是单元格地址引用函数INDIRECT和ADDRESS的几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。第二十五讲EXCEL转换函数CHAR、VALUE、T、REPT，可以这么用原创2021-12-26 办公神操作今天，我将和大家一起分享几个字符串转换函数CHAR、VALUE、T、REPT，平时工作中虽然用得不多，但这几个函数用好了能解决一些你意想不到的问题。一、CHAR函数基础知识（一）CHAR函数定义CHAR函数返回对应数字代码的字符，使用CHAR可将从其他类型计算机上的文件中获取的代码数字转换为字符。语法：CHAR(数字)数字：必需参数，指定所需的字符，介于1到255之间的数字，使用的是当前计算机字符集中的字符。从上图可以看出，大写字母A—Z相对应的编码是65—90，小写字母a—z相对应的编码是97—122；代码41689—41698可以返回数字1—10带圈字，代码41713—41724可以返回序号Ⅰ—Ⅻ。（二）CHAR函数案例实践：用CHAR(10)在公式中实现换行某单位对考核结果不及格的人员需要提示进行补考，首先需要用IF函数判断，为了让显示结果更加清晰，可以将考核结果和是否需要补考的信息进行换行，在EXCEL公式中，如果要在计算过程中强制在某个位置换行显示，可以使用CHAR(10)函数来实现。在C3单元格中输入公式：=IF(B3>=60,"考核结果：及格"&CHAR(10)&"不需要补考","考核结果：不及格"&CHAR(10)&"需要补考")，按回车键即可将考核结果和是否需要补考的信息进行换行。见下图：CHAR函数的参数是10的时候，产生的字符是换行符，但换行符需要设置单元格为自动换行才会起作用，如果不设置单元格为自动换行，就算插入了换行符，输出结果也不会换行显示。本例中，使用了IF函数进行判断，IF函数的相关用法可参考我发布的系列教程。二、VALUE函数基础知识VALUE函数定义：VALUE函数的作用是将代表数字的文本字符串转换成数字，如果是数字，则返回数字本身。语法：VALUE(文本)文本：必需参数，表示需要转换成数值格式的文本字符串，或者包含要转换文本的单元格引用。该参数可以是EXCEL中可识别的任意数值常数、日期或时间格式，如果不是这些格式，则函数VALUE返回错误值#VALUE!。通常不需要在公式中使用函数VALUE，EXCEL可以自动在需要时将文本转换为数字，实际使用中，往往在函数前加“--”代替VALUE函数。据官方说明，提供此函数是为了与其他电子表格程序兼容。三、T函数基础知识（一）T函数定义T函数属于EXCEL函数中的文本类函数，该函数的作用是返回值引用的文字。语法：T(参数)参数：必需参数，当源数据为文本时，返回值还是文本，若源数据为数值或逻辑值，则返回值为空。EXCEL会根据需要自动转换值，因此通常不需要在公式中使用T函数，提供此函数是为了与其他电子表格程序兼容。（二）T函数案例实践：为公式添加说明在使用EXCEL时，使用公式处理各种数据，会使得数据的处理更加便捷和轻松。由于每个人的逻辑思维不同，在用公式处理数据时使用的公式可能会有所不同，会给其他人员阅读公式带来不便，这时可以考虑在公式后面添加公式说明。为公式添加说明，首先需要考虑的是不能影响公式的功能，不会让公式因添加了说明而发生变化。在E3单元格中输入公式：=IF(D3>=60,"及格","不及格")&T(N("公式说明：大于等于60分为及格"))，按回车键即可为公式添加说明且不影响公式结果。见下图：T函数只能为结果是文本型的公式添加说明，如果为结果是数值型的公式添加说明则不能使用T函数，否则会将数值型结果转换为文本型，不利于公式结果参与其他运算，可以考虑在公式后面添加这个说明：+N("公式说明：说明内容")。三、REPT函数基础知识（一）REPT函数定义REPT函数可以按照指定的次数重复显示文本，相当于复制文本。语法：REPT(文本,重复次数)文本：必需参数，是指需要重复显示的文本。重复次数：必需参数，是指用于指定文本重复次数的整数。使用REPT函数注意事项：—如果第2个参数“重复次数”为0，则REPT返回空文本。—如果“重复次数”不是整数，将被截尾取整。—REPT函数结果的长度不能超过32,767个字符，否则将返回错误值#VALUE!。（二）REPT函数案例实践1.隐藏手机号码中间的4位数日常工作中，需要统计手机号码，为了信息不泄露，会对手机号码进行加密处理，一般会用*号代替手机号码中间4位数，用LEFT、REPT和RIGHT函数就可以实现。在C3单元格中输入公式：=LEFT(B3,4)&REPT("*",4)&RIGHT(B3,3)，按回车键即可隐藏手机号码中间4位数。见下图：本例中，先用LEFT(B3,4)提取手机号码前4位，用REPT("*",4)重复4次*号，用RIGHT(B3,3)提取手机号码后3位，最后用文本连接符&连接起来。2.制作五星评价简易图在考核评价中，有时候需要一目了然表达考核情况，可以考虑使用简易图表的方式来表示，本例中评价分为1～5分，1分用一颗实心五角星★表示，5分以下的其他分值用一颗空心五角星☆表示。在C3单元格中输入公式：=REPT("★",B3)&REPT("☆",(5-B3))，回车后即可得到五星评价图。见下图：本例中，首先使用REPT("★",B3)为★用B3单元格的数字指定重复次数，再用REPT("☆",(5-B3))为☆指定重复次数，因为最高分为5分，所以用5减去已计算重复的★次数，即5减去B3单元格的次数，最后用文本连接符&连接起来。以上就是字符串转换函数CHAR、VALUE、T、REPT的基础知识和几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。第二十六讲EXCEL用FIND和SEARCH函数查找定位字符，就是这么牛原创2021-12-27 办公神操作今天，我将和大家一起分享查找定位字符函数FIND和SEARCH。很多时候，我们拿到的原始数据往往杂乱无章，尤其是当我们面对错综复杂且数据量庞大的表册时，这时候就需要利用这两个函数，根据文本中的指定字符串进行查找和定位，从而帮助我们快速找到所需内容，所以千万不要小瞧它们的作用。一、FIND函数基础知识（一）FIND函数定义FIND函数用来对原始数据中某个字符串进行定位，以确定其位置。使用FIND函数进行定位时，总是从指定位置开始，返回找到的第一个匹配字符串的位置，不管其后是否还有相匹配的字符串。语法：FIND(查找值,查找单元格,查找字符数)查找值：必需参数，是指要查找的字符串。如果是文本，必须添加英语输入法下的双引号，否则函数无法计算。查找单元格：必需参数，是指包含要查找关键字的单元格，也就是说要在这个单元格内查找关键字。查找字符数：可选参数，指定开始进行查找的字符数，如果值为1，则从单元格内第一个字符开始查找关键字，如果忽略该参数，则默认值为1。使用FIND函数注意事项：—使用FIND函数查找时，要区分大小写、全角和半角字符。查找结果的字符位置不分全角和半角，作为一个字符来计算。—使用第3个参数“查找字符数”可跳过指定数目的字符。例如，使用文本字符串“函数学习：如何学好函数”，如果要查找文本字符串中第二个“学”字，可将“查找字符数”的值设置为大于第一个“学”字所在的位置3，比如我们设置为5，这样就会跳过第一个“学”字，从第5个字符开始查找，下一个“学”字的位置在第8位，于是返回8。—如果第1个参数“查找值”是空文本，则FIND会返回数值1。“查找值”中不能包含通配符。—如果第2个参数“查找单元格”中不包含“查找值”，第3个参数“查找字符数”小于0，“查找字符数”大于“查找单元格”的长度，则FIND都将返回错误值#VALUE!。（二）FIND函数案例实践：提取长宽高统计在一个单元格的数据计算体积某公司工程部在统计农户水池建设时，把建设规格长宽高统计在一个单元格里，每个数据只有十位数并保留两位小数，中间用*号隔开，现在需要计算水池体积并根据单价计算总价。在C3单元格中输入公式：=PRODUCT(LEFT(B3,FIND("*",B3)-1),MID(B3,FIND("*",B3,5)+1,4),RIGHT(B3,4))，按回车键即可分别提取长宽高并进行相乘计算出体积；在E3单元格输入公式：=PRODUCT(C3:D3)，即可计算出水池建设总价。见下图：本例中，用LEFT、MID和RIGHT函数提取的数据作为PRODUCE的参数进行计算。LEFT(B3,FIND("*",B3)-1)中，使用FIND("*",B3)-1查找出*号在B3单元格中的位置数字，减去1表示提取*号前面的数据；MID(B3,FIND("*",B3,5)+1,4)中，第一个*号在第4位，跳过*号+1表示提取*号后面一位，然后提取第二个*号前的4个字符；用RIGHT(B3,4)提取B3单元格后面4位，最后用PRODUCT函数进行乘积计算。对于本例记录数字规律的单元格，也可以跳过FIND函数，直接用LEFT、MID和RIGHT函数分别提取后相乘，公式可写成：=PRODUCT(LEFT(B3,4),MID(B3,6,4),RIGHT(B3,4))，两个公式结果完全相同。二、SEARCH函数基础知识（一）SEARCH函数定义SEARCH函数可在第二个文本字符串中查找第一个文本字符串，并返回第一个文本字符串的起始位置的编号，该编号从第二个文本字符串的第一个字符算起。语法：SEARCH(查找值,查找区域,查找字符数)查找值：必需参数，是指要查找的文本字符串。查找区域：必需参数，是指要在哪个区域查找。查找字符数：可选参数，是指从第2个参数“查找区域”的第几个字符开始查找，当从第一个字符开始查找时可省略，当省略时默认值为1。使用SEARCH函数注意事项：—在第1个参数“查找值”中，可以使用通配符，例如：问号“?”和星号“*”。其中问号“?”代表任何一个字符，星号“*”代表任何字符串。如果要查找的字符串就是问号或星号，必须在这两个符号前加上“～”符号。—如果找不到第1个参数“查找值”的值，则返回错误值#VALUE!。—如果第3个参数“查找字符数”小于0或大于第2个参数“查找区域”的长度，则返回错误值#VALUE!。（二）SEARCH函数案例实践：统计各班级学生人数某年级把所有学生的基础信息汇总在一个表格，需要统计各班级的学生人数，本例中，可以用COUNT和SEARCH函数进行统计。在F3单元格中输入公式：=COUNT(SEARCH(E3,$B$3:$B$20))，按【Ctrl+Shift+Enter】三键组合，即可统计出各班级学生人数。见下图：本例中，首先用SEARCH(E3,$B$3:$B$20)查找出E3单元格班级在$B$3:$B$20区域中的位置数字，如果能查找到需要的文本，则返回其位置，如果找不到则返回错误值。最后用COUNT统计这些数字的个数，因为一对多查找属于数组，所以要用【Ctrl+Shift+Enter】三键组合得到计算结果。$B$3:$B$20要用绝对引用的方法，否则下拉复制时单元格区域会发生变化，导致统计错误。在实际运用中，本例属于单条件计数，一般不会运用以上方法，通常会用COUNTIF函数来统计，公式可写成：=COUNTIF($B$3:$B$20,E3)，两个公式的统计结果完全相同。以上就是查找定位字符函数FIND和SEARCH的基础知识和几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。第二十七讲EXCEL中的易容术，文本替换用REPLACE和SUBSTITUTE原创2021-12-28 办公神操作今天，我将和大家一起分享字符替换函数REPLACE和SUBSTITUTE。这两个函数是EXCEL中的字符替换函数，可以将指定位置的字符替换为新的内容，是EXCEL中的易容术，配合其他函数使用将能发挥很大作用。一、REPLACE函数基础知识（一）REPLACE函数定义REPLACE函数是指根据指定的字符数，将特定文本字符串替换为不同的文本字符串。语法：REPLACE(要替换的字符串,开始位置,替换个数,新的文本)要替换的字符串：必需参数，是指要替换其部分字符的文本或单元格文本引用。开始位置：必需参数，是指要用第4个参数“新的文本”替换的字符位置。替换个数：必需参数，是指使用第4个参数“新的文本”替换第1个参数“要替换的字符串”中字符的个数。新的文本：可选参数，是指即将替换第1个参数“要替换的字符串”中字符的文本。如果忽略该参数，则默认值为空。（二）REPLACE函数案例实践：将11位手机号码中间4位数替换为*号以往的课程我们讲过，用REPT函数将手机号码中间的4位数替换为*号，涉及到LEFT、MID、RIGHT等多个函数嵌套，其实用REPLACE函数就可以轻松替换。在C3单元格中输入公式：=REPLACE(B3,5,4,"****")，按回车键即可隐藏手机号码中间4位数。见下图：二、SUBSTITUTE函数基础知识（一）SUBSTITUTE函数定义SUBSTITUTE函数是指对指定的字符串进行替换。语法：SUBSTITUTE(需替换的文本,旧文本,新文本,第N个旧文本)需替换的文本：必需参数，是指需要替换其中字符的文本，或对含有文本的单元格的引用。旧文本：必需参数，是指需要替换的旧文本。新文本：必需参数，是指用于替换第2个参数“旧文本”的文本。如果省略该参数，则默认值为空。第N个旧文本：可选参数，是指用来指定以第3个参数“新文本”替换第几次出现的“旧文本”。如果指定该参数的数字，则只有满足要求的“旧文本”被替换；如果省略该参数，将用“新文本”替换文本中出现的所有“旧文本”。（二）SUBSTITUTE函数案例实践1.隐藏手机号码中间的数字以往的课程我们讲过，用REPT函数和REPLACE函数可以轻松隐藏手机号码指定的位置数字，用SUBSTITUTE和MID函数也可以实现。在EXCEL中，同一个计算结果可能有很多种方法能实现，我们一般会选择使用最简便的那一种方法。在C3单元格中输入公式：=SUBSTITUTE(B3,MID(B3,4,4),"****",1)，按回车键即可隐藏手机号码中间4位数。见下图：本例中，用MID(B3,4,4)截取手机号码中间4位数作为SUBSTITUTE函数的第2个参数“旧文本”，用"****"作为第3个参数“新文本”，“1”是指替换出现1次的内容，可以省略该参数。2.根据姓名统计参加活动的人数某单位每天安排职工参加志愿服务活动，人数不固定，统计时把每天的人员统计在一个单元格里，中间用顿号分隔，现在需要根据人员姓名统计参加活动的人数，可以用LEN和SUBSTITUTE函数进行统计。在C3单元格中输入公式：=LEN(B3)-LEN(SUBSTITUTE(B3,"、",""))+1，按回车键即可根据姓名统计出参加活动的人数。见下图：本例中，先用SUBSTITUTE(B3,"、","")将参加活动人员之间的顿号替换为空，再用LEN函数计算删掉逗号之后的字符串长度。然后用LEN(B3)计算出包括顿号的字符串总长度，减去删除顿号的字符串长度再加上1，即可计算出参加活动的人数。知识拓展：LEN函数的功能是返回文本字符串中的字符数，语法格式为：LEN(字符串)，其参数为必需参数，表示要查找其长度的字符串或单元格引用，空格将作为字符进行计数。3.对带单位的数据进行求和某超市统计每天各类商品销售总额时，把单位也录入了单元格，现在需要汇总当日所有商品销售合计金额，需把单位去掉后才能进行求和，可以用SUBSTITUTE函数将单位替换为空，然后再进行计算。在C3单元格中输入公式：=SUMPRODUCT(SUBSTITUTE(C3:C9,"元","")*1)&"元"，按回车键即可计算出销售总合计。见下图：本例中，使用SUBSTITUTE(C3:C9,"元","")将C3:C9区域的“元”全部替换为空值，乘以1将文本转换为数值，再利用SUMPRODUCT函数求和，最后用连接符&加上“元”，使计算结果带上单位。本例也可以将SUMPRODUCT替换为SUM，然后按【Ctrl+Shift+Enter】三键组合，利用数组的方式进行求和。以上就是字符替换函数REPLACE和SUBSTITUTE的基础知识和经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。第二十八讲EXCEL中的时间函数DATE和DATEDIF，管理好你的时间原创2021-12-29 办公神操作今天，我将和大家一起分享时间函数DATE和DATEDIF，广泛运用在计算每月天数、计算工龄、计算周岁等领域。一、DATE函数基础知识（一）DATE函数定义DATE函数是指返回代表特定日期的序列号，如果在输入函数前，单元格格式为“常规”，则结果为日期格式。语法：DATE(年,月,日)年（YEAR）：为1～4位的数字，并且要大于或者等于1900，EXCEL根据计算机使用的日期系统解释该参数。默认情况下，EXCEL使用1900日期系统，这意味着第一个日期是1900年1月1日。如果该参数位于0～1899（包含这两个数）之间，则EXCEL会将该值加上1900，再计算年份。例如：DATE(121,1,1)返回2021年1月1日（1900+121）。如果该参数位于1900到9999（包含这两个数）之间，EXCEL将使用该数值作为年份。例如：DATE(2021,1,1)将返回2021年1月1日。如果该参数小于0或大于等于10000，EXCEL将返回错误值#NUM!。月（MONTH）：1～12之间的数字。如果大于12，将从下年度的一月份开始往上累加计算。例如：DATE(2021,14,1)返回代表2022年2月1日的序列号。如果小于0，将从指定年份前一年的12月份开始往下减去相应的月份数。例如：DATE(2021,-2,1)返回代表2020年10月1日的序列号。日（DAY）：1～31天之间的数字。如果大于该月份的最大天数，将从指定月份下个月的第一天开始往上累加。例如：DATE(2021,1,35)返回代表2021年2月4日的序列号。（二）DATE函数案例实践1.通过身份证号码提取出生日期并转换为标准日期某单位在制作职工基本信息表的时候，已经输入了身份证号码，还需要填写出生日期，为了实现快速输入，需要一次性从身份证号码中提取出生日期并转换为标准日期格式，前几节课讲过用TEXT函数进行转换，本例还可以使用DATE函数进行转换。在C3单元格中输入公式：=DATE(MID(B3,7,4),MID(B3,11,2),MID(B3,13,2))，按回车键即可通过身份证号码提取出生日期并转换为标准日期。见下图：DATE函数可以将代表年月日的数字转换为日期序列号，如果输入公式前单元格格式是常规格式，该函数会默认以标准日期格式显示计算结果。本例也可以使用文本连接符&将提取的年月日连接起来，虽然这样连接起来的日期中的月份和天数都是两位数，但是EXCEL依然会将其视为日期，其公式可写成：=MID(B3,7,4)&"-"&MID(B3,11,2)&"-"&MID(B3,13,2)。2.计算指定月份的天数某单位每月都要对周末和节假日排班，因为每个月的天数略有不同，现在需要用EXCEL制作一个公式，要求该公式能够返回当月的天数，并且能够随着时间的变化而变化。需要知道某月有多少天，一般的方法是先判断该月是大月份还是小月份，如果是大月，该月就是31天；如果为非2月份的小月，则为30天；如果是2月份，还要判断该年是闰年还是平年，闰年则2月份有29天，平年则有28天。如果在EXCEL中用该方法计算当月的天数，写成的公式就会显得很复杂。但是因为每个月的最后一天就是该月的天数，所以只要知道了该月的最后一天，就可以知道该月的天数，可以通过EXCEL中日期函数的自动进位功能来实现。在B3单元格中输入公式：=DAY(DATE(YEAR(A3),MONTH(A3)+1,0))，按回车键即可得到A3单元格指定的月份天数。见下图：日期函数之所以具有自动进位的功能，这和EXCEL使用日期序列号记录日期有着很大的关系，当DATE函数第三个参数设置为0的时候，相当于该月第1天的前一天，即上个月的最后一天。本例中，YEAR(A3)是指A3单元格的年度，MONTH(A3)是指A3单元格的月份，因为第三个参数0指的是上个月的最后一天，所以要+1将月份指定为当前月份，然后将YEAR(A3)、MONTH(A3)+1和0作为DATE的三个参数，最后用DAY函数得到天数。二、DATEDIF函数基础知识（一）DATEDIF函数定义DATEDIF函数是指返回两个日期之间的年\月\日间隔数，常使用DATEDIF函数计算两日期之差，时间单位包含D,M,Y,YD,YM,MD。语法：DATEDIF(起始日期,结束日期,时间单位)起始日期：必需参数，表示给定期间的第一个或开始的日期。日期值有多种输入方式：包括带引号的文本字符串、序列号或其他函数的结果等。结束日期：必需参数，代表时间段内的最后一个日期或结束日期。时间单位：必需参数，是指为“起始日期”和“结束日期”返回时间单位的代码。"Y"表示两个日期以“年”为单位的时间间隔。"M"表示两个日期以“月”为单位的时间间隔。"D"表示两个日期以“日”为单位的时间间隔。"MD"忽略“年”和“月”后，两个日期以“日”为单位的时间间隔。"YD"忽略“年”后，两个日期以“日”为单位的时间间隔。"YM"忽略“年”，两个日期以“月”为单位的时间间隔。（二）DATEDIF函数案例实践1.根据身份证号码计算周岁在工作中，我们经常会利用身份证号码提取出生日期，同时会计算年龄，通常用当前年数减去出生年进行计算，但是这样计算出来的结果并不是真正的年龄，比如相差一个月按一年计算。本例中用DATEDIF函数，通过身份证号码计算周岁。在C3单元格中输入公式：=DATEDIF(TEXT(MID(B3,7,8),"0000-00-00"),TODAY(),"Y")，按回车键即可根据身份证号码计算周岁。见下图：本例中，用MID(B3,7,8)从身份证号码中提取出生日期，然后用TEXT将出生日期转换为标准日期格式，作为DATEDIF函数的第1个参数“起始日期”，然后用TODAY()得到当前日期作为第2个参数，用"Y"作为第3个参数即可得到周岁。2.根据工龄计算职工公休的天数某单位每年安排职工公休，规定工龄不足5年享受5天公休假；工龄20年以下享受10天公休假；工龄20年以上享受15天公休假，现在需要计算每位职工每年的公休假天数。在C3单元格中输入公式：=DATEDIF(B3,TODAY(),"Y")，计算出工龄，在D3单元格中输入公式：=IF(C3<10,5,IF(C3<20,10,15))，按回车键即可根据工龄计算出每年的公休假天数。见下图：本例中，利用辅助列C列计算出工龄，然后用IF函数进行判断，这样写成的公式容易理解。如果不用辅助列，公式可写成：=IF(DATEDIF(B3,TODAY(),"Y")<10,5,IF(DATEDIF(B3,TODAY(),"Y")<20,10,15))，公式相对要长一些，新手不容易理解，建议使用辅助列辅助计算。3.计算精确到月份的职工工龄计算职工的工龄，其实就是计算职工参加工作的时间和当前时间之间的日期间隔，使用DATEDIF函数就可以解决这样的问题。在D3单元格中输入公式：=TEXT(DATE(0,DATEDIF(C3,TODAY(),"M"),1),"y年m月")，按回车键即可计算出精确到月份的职工工龄。见下图：本例中，使用DATEDIF(C3,TODAY(),"M")计算出工龄的总月数，然后用DATE将代表年的0、DATEDIF计算的月份数和代表每月1日的1转换为日期，最后用TEXT函数将显示格式转换为年和月。以上就是日期函数DATE和DATEDIF的基础知识和经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。第二十九讲EXCEL函数公式常见的计算错误值和处理方法原创2021-12-30 办公神操作今天，我将和大家一起盘点在使用EXCEL公式时常见的错误值和处理技巧，有效防止错误的再次发生和连续使用。一、常见的公式计算错误值（一）“#####”错误和解决方法出现错误的原因：有时对表格的格式进行调整，并没有对表格中的数据进行编辑，调整格式后却发现有些单元格的数据不见了，取而代之的是“#####”形式的数据。解决办法：1.如果整个单元格都是#号填充，通常表示该单元格中的数字、日期和时间超过了第一个的宽度，无法完整显示数据，增加列宽即可解决。2.当单元格的数据类型和单元格格式不符时，也可能显示“#####”错误，此时可以改变单元格格式，直到正常显示数据。3.当单元格包含的公式返回无效的日期和时间时，比如产生负值，也会显示“#####”错误，因此，需要确保日期和时间公式的准确性。（二）“#VALUE!”错误和解决方法出现错误的原因：当使用的参数或数值类型错误，原因是数值型和非数值型数据进行了四则运算，或者当公式自动更正功能不能更正公式时，也或者是数组公式没有按【Ctrl+Shift+Enter】三键组合结束，都会产生“#VALUE!”错误。解决办法：确认公式和函数使用的参数、计算对象、类型是否正确，公式引用的单元格中是否包含有效的数值。（三）“#N/A”错误和解决方法出现错误的原因：在手动输入公式时，常常会出现“#N/A”错误值，主要是因为公式中没有可用数值，常见的有以下几种情况：1.源数据缺失：小数组在复制到大区域中时，尺寸不匹配的部分会返回“#N/A”错误值。2.目标数据缺失：在引用或涉及目标数据的运算时，如果目标数据缺失，就会产生“#N/A”错误值。3.参数数据缺失：如公式“INDEX(,,)”，由于没有参数，就会返回“#N/A”错误值。4.数组运算不匹配：当两个对应的数组进行运算时，由于矩阵数目不匹配，也会产生“#N/A”错误值。解决办法：检查目标数据、源数据、参数等是否填写完整，相互运算的数组尺寸是否相同。（四）“#NULL!”错误和解决方法出现错误的原因：公式使用了不相交的两个区域交集，需要注意的是不相交，而不是相交为空。例如：公式“=A1:D1 A3:D3”，两行不相交。解决办法：预先检查计算区域，避免空值的产生。若要在引用不相交的两个区域，一定要使用联合运算符，即半角逗号“,”。（五）“#REF!”错误和解决方法出现错误的原因：1.引用地址失效：当删除了其他公式所引用的单元格、将已移动的单元格粘贴到其他公式所引用的单元格中或使用了拖动填充控制柄的方法复制公式，但公式中的相对引用成分变成了无效引用。2.返回无效的单元格：如公式“=OFFSET(B2,-ROW(A2),)”，返回的是单元格B2向上移动2行的单元格的值，指定的是B0单元格，但是该单元格地址不存在。解决办法：检查被引用的单元格或区域返回参数的值是否存在或有效，对公式进行修改，在删除或粘贴单元格之后恢复工作表中的单元格。（六）“#NUM!”错误和解决方法出现错误的原因：在需要数字参数的函数中使用了无法接受的参数。解决办法：确保函数中使用的参数为正确的数值范围和数值类型。如“=10^400”超出了EXCEL数值大小的限制，属于范围出错，因此，我们在进行计算时，要保持数值的规范，及时需要输入的值是“$800”，也应在公式中输入“800”。（七）“#DIV/0!”错误和解决方法出现错误的原因：在EXCEL中，当公式的除数是0时，将会产生错误值“#DIV/0!”。如果参数是一个空白单元格，EXCEL会认定为0，也会产生错误值“#DIV/0!”。解决办法：将除数更改为非零值，修改单元格引用，或在用作除数的单元格中输入不是零的数值，确保公式中的除数不为零或空。（八）“#NAME?”错误和解决方法出现错误的原因：在公式中使用了EXCEL不能识别的文本，产生该错误值的原因较多，具体如下：1.函数名称拼写错误；2.某些函数未加载宏，如DATEDIF函数；3.名称拼写错误；4.公式中输入文本时没有使用双引号，或者在中文输入法状态下输入了引号“”；5.单元格地址书写错误，如输入了错误公式“=AVERAGE(A:B6)”；6.在低版本EXCEL中使用了高版本的公式。解决办法：确保公式拼写正确，要加载宏，定义正确的名称，删除不受支持的函数，或者替换为受支持的函数。要确认公式中使用的名称是否存在，可以在“名称管理器”中查看所需的名称有没有被定义。如果公式中引用了其他工作表或工作簿中的值或单元格，且工作簿或工作表的名称中包含非字母字符或空格时，需要将该字符放在单引号“'”中。二、利用IFERROR和ISERROR函数彻底解决公式错误（一）IFERROR函数定义IFERROR函数可以捕获和处理公式中的错误值，如果公式的计算结果为错误，则返回指定的值，否则将返回公式结果。语法：IFERROR(被检查参数,错误时返回的值)被检查参数：必需参数，检查是否存在错误的参数，主要检查以下错误类型：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?和#NULL!。错误时返回的值：必需参数，是指公式计算结果为错误时返回的值。注意事项：如果参数为空单元格，则IFERROR会视为空值。如果值是数组公式，则IFERROR返回值中指定区域内每个单元格的结果数组。（二）ISERROR函数定义ISERROR函数用于测试公式返回的数值是否有错误。如果有错误，该函数返回TRUE，反之返回FALSE，通常与IF函数配套使用。语法：ISERROR(表达式)表达式：可以是任何有效的表达式。ISERROR和IFERROR的区别：两个函数都可以起到容错的作用，同样的效果，使用IFERROR函数可以使公式更简短。=IF(ISERROR(A1),0,A1)和=IFERROR(A1,)两个公式的结果完全相同，因此，实际中建议多用IFERROR函数解决同类问题。以上就是我们在EXCEL公式计算时常见的一些错误和解决办法。明天的课程是“EXCEL函数高手成长记”30天系列课程的最后一节课，我将和大家一起总结学习EXCEL函数遇到的一些困惑及技巧，希望大家学有所成，大幅度提高工作效率。第三十讲30天成为EXCEL函数高手：如何学好EXCEL函数原创2021-12-31 办公神操作和大家分享了日常工作中常用的EXCEL函数，用29节课对EXCEL函数的基本知识、各个常用函数的基本用法和一些经典案例作了讲解，制作了一系列视频和图文教程，如果下定决心坚持学习，我想，应该学有所获。学习函数容易，成为函数高手不容易，而成为数组函数高手更是一条艰辛的道路。对于大多数人而言，学习的过程中，如果不能经常收获成就，会很容易放弃。所以，在学习EXCEL函数时，我建议大家以自己的实际工作出发，用EXCEL函数把自己工作上的问题一点点进行处理，在不断收获成就感的过程中一点一点掌握。今天，是“EXCEL函数高手成长记”30天系列课程的最后一节课，我将和大家一起总结学习EXCEL函数遇到的一些困惑及技巧。一、扎实的基本功是学好EXCEL函数的杀手锏高手都是练基本功的，不同行业、不同领域、不同学科都会有不同的基本功，这些基本功都是围绕人来进行的，所以，基本功的建立点都是以人为根本出发点。比如：键盘打字会涉及到手、脑、心配合，书法的基本功会有眼睛的运用、手部关节的运用、身体姿势的运用……还有很多很多领域的基本功都是建立在人本身的功能运用上。所以，我们在提到基本功的时候，一定是在人自身能力上去寻找。EXCEL函数的基本功就是要清楚什么是函数，什么是公式，要知道公式总是以“=”开始，以成对的半角括号()封闭，以半角逗号“,”未分隔符，然后将各种计算数据使用不同的运算符连接起来，从而有目的地完成某种数据结果的计算。EXCEL函数的基本功就是要掌握运算符的基本概念，运算符是公式的基本要素，利用它可对公式中的参数进行特定类型的运算，它是影响数据计算结果的重要因素之一。学好EXCEL函数，要清楚数据的引用方法。在EXCEL中，数据的引用方式有三种，分别是相对引用、绝对引用和混合引用，不同的数据引用方法得到的结果不同。学好EXCEL函数，要知道逻辑值和逻辑函数。在EXCEL中，逻辑值有两个，分别是TRUE和FALSE，主要作用是对某条件判定是否成立，成立则为真（TRUE），不成立则为假（FALSE）。逻辑函数有OR（“或者”）、AND（“与”或“并且”）、NOT（“非”或“取反”）等。学好EXCEL函数，要知道空单元格和空文本的区别。在EXCEL中，默认状态下，单元格中不显示任何内容，或将有内容的单元格清空后，此时的单元格即为空单元格。而空文本则是使用一对半角双引号（""）来表示，二者在公式运用中有明显的区别。在EXCEL函数的实际运用中，经常会出现错误值，如：“#VALUE!”“#NULL!”“#DIV/0!”等，所以我们要掌握处理错误数据的方法，要学会利用IFERROR和ISERROR函数解决公式错误。二、学习EXCEL函数要结合工作实际，把握重点EXCEL作为职场办公中不可缺少的一款软件，它可以让我们从繁琐的手工制表中解脱出来，并且可以自动完成相应的计算，其中的函数部分更是功能强大，当你面对一大堆数据一筹莫展时，当你为做着重复计算工作烦恼时，利用好EXCEL的函数功能可以摆脱这些让人头痛的工作。说到函数，很多小伙伴们认为是高大上的东西，无从学起，其实掌握了工作中常用的函数用法就可以了，毕竟EXCEL仅内置函数就有400多个，完全掌握那简直是天方夜谭。我在讲解VLOOKUP函数的时候说过，EXCEL函数中最受欢迎的有三大家族，一个是以SUM函数为首的求和家族，一个是以VLOOKUP函数为首的查找引用家族，另一个是以IF函数为首的逻辑函数家族，学好这三大家族的函数，就能完成80%的日常工作。三、要学会利用资源除了少部分EXCEL发烧友以外，别怀疑，这种人的确存在，有的人学习EXCEL就是为了玩儿，大部分人学习EXCEL的目的是为了解决工作中的问题和提升工作效率。遇到问题的时候，如果知道应该使用哪个函数，但是对这个函数不太会用，此时最好的办法是资源搜索，集中精力学习这个需要掌握的函数，这一招在学习上百个EXCEL函数的时候特别适用。如果对所遇问题不知从何下手，甚至不能确定EXCEL函数能否提供解决方法，可以求助于他人。此时，如果身边有一位EXCEL高手，或者能马上联系到一位高手，那将是件非常幸运的事情。如果没有这样的受助机会，也不用担心，还可以上网搜索解决方法，或者到某些EXCEL网站上去寻求帮助。当利用各种资源解决了自己的问题时，一定很有成就感，此时千万不要停止探索的脚步，争取把解决方法理解得更透彻，能做到举一反三。EXCEL实在是博大精深，在学习的过程中如果遇到某些知识点暂时用不着，不必深究，但一定要了解，而不是简单的忽略。说不定哪天就需要用到某个函数，EXCEL里面明明有，可是你却不知道，以至于影响到寻找答案的速度。比如，作为一名财会工作者，可能没有必要花很多精力去学习EXCEL的工程函数，而只需要了解，当有一天需要用到它们时，可以在函数列表里面查找适合的函数，并配合查看帮助文件来快速掌握需要的函数。四、多读多写多实践在学习EXCEL函数时，多阅读EXCEL技巧或案例方面的文章和书籍，在互联网上，介绍EXCEL的文章很多，而且可以免费阅读，这些都是非常好的学习资源。在图书市场上也有许多EXCEL书，所以多花点时间阅读对自己有帮助的书，可以买回家去仔细研读，能够拓宽你的视野，并从中学到许多对自己有帮助的知识。我们经常遇到这样的问题：“学习EXCEL，什么书比较好”，这是个比较难回答的问题，因为不同的人，需求是不一样的，适合一个人的书，不见得适合另一个人，选书之前，最好还是自己翻阅一下，选择书中你最感兴趣的一本。在学习EXCEL函数时，软件提供了很多录入方式，输入“=”再输入函数，当输入第一个字母时，软件会自动列出以该字母开头的所有函数和解释，也可以在插入函数中插入函数和参数，建议在学习EXCEL过程中，多动手写，这样才能尽快掌握常用函数的名称和用法。学习EXCEL函数，必须理论结合实践，阅读过的文章，只有亲自在电脑上实践几次，才能把别人的知识真正转化为自己的知识。通过实践，还能够举一反三，围绕一个知识点，做出多个案例，以验证自己的理解是否正确和完整。我所见过的很多高手，实践的时间远远大于阅读的时间，因为EXCEL的基本功是有限的，不需要太多文字去介绍，而真正的成长来源于如何把这些有限的功能不断通过实践转化为成果。以上是对在EXCEL函数时的一些拙见，说得不对的地方，请批评指正，本套课程结束。在下一套课程中，将分享EXCEL的操作技巧，相比于函数学习课程要简单得多，同时也能激发学习兴趣，更容易掌握。