Excel精进
何庆华
十年后遇见运动、读书、旅行的自己!
展开
-
Excel第27享:基于if函数嵌套的多结果唯一性匹配
现在有E列、F列、G列三列数据,在D列中填充“最终对应编号”,匹配原则是:E列、F列、G列三列数据中,哪个有数据就填充哪个数据,如果都没有,就显示空值即可。原创 2024-03-21 15:31:54 · 342 阅读 · 0 评论 -
Excel第26享:模糊查找之Hlookup函数与通配符的嵌套
模糊查找之Hlookup函数与通配符的嵌套原创 2024-03-14 10:40:58 · 284 阅读 · 0 评论 -
Excel第25享:index函数与match函数的嵌套联合定位
index函数与match函数的嵌套联合定位原创 2023-02-13 18:20:55 · 984 阅读 · 0 评论 -
Excel第24享:替换函数之SUBSTITUTE函数与REPLACE函数
1、SUBSTITUTE函数与REPLACE函数在文本字符串中用 new_text 替代 old_text。如果需要在某一文本字符串中替换指定的文本,请使用函数 SUBSTITUTE;如果需要在某一文本字符串中替换指定位置处的任意文本,请使用函数 REPLACE。2、SUBSTITUTE函数定义及应用SUBSTITUTE(text,old_text,new_text,[instance_num])Text 不省略参数。为需要替换其中字符的文本,或对含有文本的单元格的引用。..原创 2020-11-06 17:26:18 · 1408 阅读 · 0 评论 -
Excel第23享:countif()函数之自作聪明
1、功能借助countif函数对一些特定的单元格内容进行计数时,就会出现一些识别错误的现象。2、应用之前介绍过,对身份证号这类超15位的数字的计数countif需要&"*"将其视为文本来正确计数。在下图1中,希望对单元格内容为">4"作为条件来进行计数。图1公式1: =COUNTIF(range,">4")结果1:=3说明:">4"直接作为criteria参数会被识别为条件表达式,">4"的字符串本身作为条件来统计会出错。公式2:.原创 2020-08-23 15:00:30 · 256 阅读 · 0 评论 -
第22享:countif()函数之不识数
1、功能借助countif函数对数据表中的文本、数值进行判断并统计、计数。criteria参数为数字时,countif的统计会忽略多余空格,部分情况会把range内的文本型数值统计在内,部分情况又不统计,本文一并总结并分享。2、应用在下图1中,示例数据包括数值、文本型数值、空格等数据,下面就第一列的示例数据进行统计性应用。图1按照上述说明分别用两个公式举例说明其结果差异,具体如下图2所示。图2说明:criteria参数当以"="作为比较运算符或者为纯...原创 2020-07-28 15:01:57 · 802 阅读 · 0 评论 -
Excel第21享:countif()函数之文本与数值
1、功能借助countif函数对数据表中的文本、数值进行判断并统计、计数。2、应用在下图1中,包括数值、文本型数值、文本等数据,下面就第一列的数据进行统计性应用。图1公式1:= COUNTIF(range,"<3")结果显示:2说明:忽略文本型数值。公式2:= COUNTIF(range,"<"&"3")结果显示:2说明:忽略文本型数值。公式3:= COUNTIF(range,"<=3")结果显示:3说..原创 2020-07-28 14:40:50 · 4568 阅读 · 0 评论 -
Excel第20享:countif()函数之多维引用
1、功能借助countif函数多维引用功能,实现对数据缺失的计数、英文字母计数、数据大小判断计数等函数应用。2、应用2.1判断齐码公式在下图1中,判断连续3个或3个以上的尺码有库存,则显示为齐码。图1公式:=IF(OR(COUNTIF(OFFSET(A4,,{0,1,2,3},1,3),">0")=3),"齐码","断码")结果显示:如上图中最后一列。2.2用counif区分大小写计数在下图2中,对下表中的"A"(只针对大写)进行判断并...原创 2020-07-12 16:27:05 · 1130 阅读 · 0 评论 -
Excel第19享:countif()函数之提取与统计
1、功能countif可以在提取和统计不重复数据等方面有相关应用。2、应用2.1不重复计数公式:=SUM(1/COUNTIF(range,range))结果显示:=142.2不重复提取公式:=IFERROR(OFFSET(A$283,MATCH(,COUNTIF(B$283:B283,A$284:A$301),),),"")—解法1解法1中range参数的递增,对criteria参数数组运算,利用countif在结果数据中去掉...原创 2020-07-06 18:34:30 · 784 阅读 · 0 评论 -
Excel第18享:countif()函数之文本排序
1、功能利用countif的判断大小(Excel第17享)的思路与加权法结合,可以利用函数对于文本进行自动排序。2、应用=INDEX(A:A,MOD(SMALL(COUNTIF(A$1:A$25,">"&A$1:A$25)/1%%+ROW($1:$25),ROW(A1)),10000))原创 2020-06-27 21:01:11 · 2420 阅读 · 0 评论 -
Excel第17享:countif()函数之比较大小
1、功能countif可应用于通过比较大小判断极值。2、应用利用比对最大数还大的数的计数必然为0的思路来进行极值的判断,这个思路也是排名的思路。=IF(COUNTIF(A$224:A$232,">"&A224),"","最大")...原创 2020-06-20 21:57:32 · 4185 阅读 · 0 评论 -
Excel第16享:countif()函数之序号与排名
1、功能countif函数可实现对序号的数据进行排名。2、应用2.1 序号与排名countif可以替代rank函数来进行排名统计。美式排名:=COUNTIF(data,">"&B4)+1不重复排名:=COUNTIF(data,">"&B4)+COUNTIF(B$4:B4,B4)中式排名:=SUM((data>B4)/COUNTI...原创 2020-06-14 21:40:59 · 6734 阅读 · 2 评论 -
Excel第15享:countif()函数之引用函数结果统计
1、功能countif()函数的统计功能之中,还可以对当前日期的单元格数量、大于选定单元格中大于平均值的单元格数量、通过分析日期去判断季度等结果进行统计。2、应用1)返回包含当前日期的单元格数量。=COUNTIF(range,TODAY())2)返回大于平均值的单元格数量。=COUNTIF(range,">"&AVERAGE(range))3)日期判断季度日期转季度这个问题比较经典,思路和解法很多,下文列举countif的两种解法:3....原创 2020-06-06 12:52:28 · 677 阅读 · 0 评论 -
Excel第14享:countif()函数之区间统计
1、功能countif()函数可以使用大于、大于等于、小于、小于等于等数学符号,对符合相应数据逻辑的单元格数量进行计数。2、应用1)返回包含负值的单元格数量。=COUNTIF(range,"<0")2)返回不等于0的单元格数量。=COUNTIF(range,"<>0")3)返回大于5的单元格数量(虽然文本和逻辑值大于数值,但对数值的统计结果会忽略文本逻辑值等)。=COUNTIF(range,">5")4)返回大于单元格A1中内容的单元格数量。=原创 2020-05-30 22:17:26 · 14155 阅读 · 2 评论 -
Excel第13享:countif()函数之模糊计数
1、功能countif()函数可以使用通配符对一些指定的内容进行模糊计数,如汉字、英文、字符数量等。2、应用1)返回A列单元格包含"销售部"的数量(如:A公司销售部)。=COUNTIF(range,"*销售部*")2)返回包含以单词"EP"(不分大小写)开头内容的单元格数量。=COUNTIF(range, "EP*")通配符 *是表示任意多个字符,放在字符后面表示,该字符后可以有任意多个字符。3)返回包含以单词"EP"(不分大小写)结尾内容的单元格数量。=COUNTIF(原创 2020-05-23 22:11:20 · 5148 阅读 · 0 评论 -
Excel第12享:countif()函数之精确统计
1、功能countif()函数可以对一些指定的内容进行精确统计,如文字、数值、逻辑值等。2、应用1)求单元格内容为12的数量,range内的文本型"12"与数值12均会被统计。公式:=COUNTIF(range,12)原创 2020-05-16 21:00:20 · 2605 阅读 · 0 评论 -
Excel第11享:行列互换
1、功能粘贴功能中有一个“选择性粘贴”,其中有“转置”选项,即可实现行列互换。2、应用原创 2020-05-16 19:59:46 · 221 阅读 · 0 评论 -
Excel第10享:考试成绩按姓名排序
方法一:1选中所有需排序区域,如下图所示2选择“排序和筛选”功能,下拉选项中有“自定义排序”选项卡,如下图所示。3点击“自定义排序”之后弹出相应对话框,需取消“列表包含标题”的复选框,如下图所示4在“列”的下拉选项中选择“列D”,“顺序”下拉选项中选择“A到Z”,之后点击确定,即出现排序效果。方法二:1从标题行开始选择所需排序区域,...原创 2020-04-23 19:29:21 · 3114 阅读 · 0 评论 -
Excel第8享:COUNT函数及其应用
1、函数定义COUNT函数,主要是统计数组或引用中的数字,对给定数据集合或者单元格区域中数据的个数进行计数,其语法结构为COUNT(value1,value2, ...)。COUNT函数只能对数字数据进行统计,对于空单元格、逻辑值或者文本数据将被忽略,因此可以利用该函数来判断给定的单元格区域中是否包含空单元格。 2、语法逻辑COUNT(value1,value2, ...)...原创 2020-04-03 11:19:11 · 809 阅读 · 0 评论 -
Excel第7享:AVERAGE函数及其应用
1、函数定义AVERAGE函数是EXCEL表格中的计算平均值函数,在数据库中average使用简写avg。AVERAGE是返回参数的平均值(也做算术平均值)。 2、语法逻辑AVERAGE(num1,num2,……)其中,num1为必需的,后续值是可选的,是需要计算平均值的1到255个数值参数,这些参数可以是数字或者是涉及数字的名称、数组或引用。如果数组或单元格...原创 2020-02-29 23:46:51 · 4762 阅读 · 0 评论 -
Excel第6享:Find函数及其应用
原创 2019-07-22 14:42:47 · 161 阅读 · 0 评论 -
Excel第5讲:中文字与计数值的连接
对每本书读的次数完成计数之后,如果想要转化为“第*本”的文字,该怎么做呢?如下图1。图1分析:1、计数的D列每出现一次数字1的时候,就需要加1;2、空白行仍然显示空白;解决方案:1、首先,使用countifs函数对出现1的次数进行计数,并求和计算;2、使用if函数对是否为空白进行判断;3、利用&完成文字与计数数值的连接。最终的函数为:=IF(D6="...原创 2018-08-27 23:34:36 · 682 阅读 · 0 评论 -
Excel第4讲:对同一信息出现的次数进行计数
对下图1中的F列、G列进行计数,其目的是计数同一本书读书的次数,以便有一个直观的效果。图1分析:1、F列中只有书名,需要对其进行计数,但后面有空白之处,需要特别注意;2、G列中有可以直接提取的正确数字,对于显示“无”的字段需要特别注意;解决方案:1、如果是F列,方案是:直接使用count函数进行计数,即:=IF(E5="","",COUNTIFS(E$5:E5,E5...原创 2018-08-27 23:30:28 · 2582 阅读 · 0 评论 -
Excel第3讲: 提取特定符号之间的信息
整理读书资料的时候,突然有这么一个想法:从一列中提取书名,样例数据如下图1。图1分析:1、这些数据可以分为三类,第一种是“无”,第二种是“为你读书之《****》”样式,第三种是“为你读书之《****》(*)”样式;2、所要提取的内容是“《》”之间的信息;解决方法:第一步:使用find函数确定“《”的位数;第二步:使用len函数辅助find函数确定“》”的位数;...原创 2018-08-27 23:23:44 · 7969 阅读 · 0 评论 -
Excel技能(初级) 快捷键篇:巧用Ctrl组合键的选中功能
前言不搭后语: 回首匆匆往事,深知今生唯运动、读书、旅行不可辜负,运动、旅行非我可替,现将读书之片言碎语摘录如下,共飨共勉!1、陶醉于自己的能力或微不足道的成功,骄傲自满,就得不到周围人的帮助,还会妨碍自己的成长。2、傲慢的人类,应该重新以敬畏之心应对自然,回归尊重自然的哲学。人类要向自然界学习,回归谦虚,再次感谢自然的恩惠,不是我们在自然中“活着”,而是自...原创 2018-06-18 21:16:25 · 354 阅读 · 0 评论 -
Excel技能(初级) 快捷键篇:巧用Ctrl+D的复制功能
前言不搭后语: 回首匆匆往事,深知今生唯运动、读书、旅行不可辜负,运动、旅行非我可替,现将读书之片言碎语摘录如下,共飨共勉! 1、成功不属于个人。京瓷的成功不是靠我的才能,而是靠伙伴和社会的帮助,我觉得自己很幸运。 2、我讨厌那些说话时轻飘飘的领导人。口才不好没关系,但话语中要灌注灵魂,特别是在当今严峻的经济形势中,领导人对自己所说的话必须充分负责,与人交流时要注入自己...原创 2018-06-13 16:16:38 · 4221 阅读 · 0 评论