【原】带你玩转Excel函数（职场Excel函数100问）

初学Excel函数你是否总有一种无力感？工作中遇到问题时，不知道用什么函数来解决？函数的单词都认识，但一用它来写公式，要么写不出来，要么结果不对，更别说编写嵌套公式那些函数名称、函数参数的作用总是记不住、公式总是编制不对；用Excel多年，还是只会常用的那三五个函数。Excel办公小课堂老师结合职场工作中遇到的问题，整理100问用函数来帮助大家学习Excel函数的这些痛点熟练掌握Excel函数的他，总是几分钟就能完成，别人倒腾1天的工作量。

第1问：如何转换所有文本字符串为小写字母（LOWER）

目的：将表格中所有的英文全部转换成小写字母显示

说明：LOWER函数用于将一个文本字符串中的所有字母转换为小写形式

解释：=LOWER(字符串)

步骤：在C4单元格中输入公式=LOWER(B4) - 向下填充即可

第2问：如何转换所有文本字符串为大写字母（UPPER）

目的：将表格中所有的英文全部转换成大写字母显示

说明：UPPER函数用于将一个文本字符串中的所有字母转换为小写形式

解释：=UPPER(字符串)

步骤：在C4单元格中输入公式=UPPER(B4) - 向下填充即可

第3问：如何转换首字母为大写字母（PROPER）

目的：将表格中所有的英文首字母全部转换成大写字母显示

说明：PROPER函数用于英文字母中的首字母转换为大写形式

解释：=PROPER(字母)

步骤：在C4单元格中输入公式=PROPER(B4) - 向下填充即可

第4问：如何检测是否为文本

目的：将表格中的数据进行检测是否有文本

说明：T函数用于检测给定值是否为文本，如果是文本，按原样返回；否则返回为空单元格。

解释：=T(文本)

步骤：在C4单元格中输入公式=T(B4) - 向下填充即可

第5问：如何快速输入26个英文字母

目的：在表格中快速输入26个大写英文字母和26个小写英文字母

说明：CHAR函数根据字符集，返回由代码数字指定的字符

说明：ROW函数返回一个引用的行号

解释：=CHAR(任意数字) ；=ROW(单元格)

步骤：在B4单元格输入公式：=CHAR(ROW()+61) - 双击向下填充，由于CHAR(65)表示的是大写字母A，而ROW()在B4单元格引用的行号是4，所以公式中ROW()+61返回得到的结果为65，通过CHAR(65)计算得到最终结果为大写字母A

步骤：在C4单元格输入公式：=CHAR(ROW()+93) - 双击向下填充，CHAR(97)表示的是小写字母a，而ROW()在C4单元格引用的行号是4，所以公式中ROW()+93返回得到的结果为97，通过CHAR(97)计算得到最终结果为小写字母a

第6问：如何快速输入带圆圈的序号

目的：在表格中快速输入带圈的序号

说明：UNICHAR函数返回由给定数值指定的字符

说明：ROW函数返回一个引用的行号

解释：=UNICHAR(数字) ；=ROW(单元格)

步骤：输入带圆圈的序号，在B9单元格输入公式：=UNICHAR(ROW(A9312)) - 双击向下填充 ；输入带括号的序号，在C9单元格输入公式：=UNICHAR(ROW(A9332)) - 双击向下填充

第7问：如何通过快捷键ALT+=一键求和

目的：批量计算每个销售部门1-12月份的销售总额

说明：SUM函数计算单元格区域中所有数值的和

解释：=SUM(数值)

步骤：选中整张表格 - 按【ALT+=】

第8问：如何对多张表格快速汇总

目的：批量汇总第一季度至第四季度各产品销售总额，以及金额总计

说明：SUM函数计算单元格区域中所有数值的和

解释：=SUM(数值)

步骤：选中汇总表中的C8:G22单元格区域 - 输入=SUM( 单击【第一季度】工作表 - 按住【Shift】键 - 单击【第四季度】工作表 - 单击所选工作表中的【B2】单元格 - 补齐右边括号 - 按【Ctrl+Enter】

第9问：如何对合并单元格快速统计

目的：按照每个部门对每位销售员销售额进行汇总

说明：SUM函数计算单元格区域中所有数值的和

解释：=SUM(数值)

步骤：选中金额合计一列合并单元格区域 - 输入公式=SUM(E8:E22)-SUM(F11:F22) - 按【Ctrl+Enter】

第10问：如何进行累积求和汇总

目的：按照每月累积求和

说明：SUM函数计算单元格区域中所有数值的和

解释：=SUM(数值)

步骤：在D8单元格输入公式：=SUM($C$8:C8)

第11问：如何解决你的汇总结果为0的情况

目的：解决数据中数字是文本格式类型进行求和

说明：SUM函数计算单元格区域中所有数值的和

解释：=SUM(数值)

步骤：在F6单元格输入公式：=SUM(--C8:C19) ，如果单元格只是出现公式，需要将单元格设置常规格式，然后按Ctrl+Shift+Enter

第12问：如何对乘积快速汇总

目的：根据商品单价和数量计算所有商品合计额

说明：SUMPRODUCT函数返回相应的数组或区域乘积的和

解释：=SUMPRODUCT（数组区域1，数组区域2，数组区域3...）

步骤：在F8单元格输入公式：=SUMPRODUCT(C8:C20,D8:D20) 或=SUM(C8:C20*D8:D20) - 按【Enter】

第13问：如何批量汇总商品销售总额

目的：函数返回相应的数组或区域乘积的和

说明：SUMPRODUCT函数返回相应的数组或区域乘积的和

解释：=SUMPRODUCT（数组区域1，数组区域2，数组区域3...）

步骤：在H8单元格输入公式：=SUMPRODUCT(C8:G8,$K$8:$O$8)

第14问：如何对带有单位的数据进行计算

目的：对带有单位的数据进行计算

说明：Excel中计算时对于文本的字符串是无法计算的（带有单位的数据），此时需要通过借助特殊符号，然后通过替换功能得出想对应的结果

步骤：单击E8单元格 - 输入$或任意一个符号 - 然后将需要计算的公式手动输入，这里面是$=20*3.7 - 单击E9单元格 - 按【Ctrl+E】- 选中总额一列数据 - 按【Ctrl+H】- 弹出的【查找替换】对话框 - 查找内容输入$ - 单击【全部替换】

第15问：如何对带有单位的数据进行汇总

目的：根据带有单位的数据，统计每位员工加班补贴总额

说明：SUBSTITUTE函数将字符串中的部分字符串以新字符串替换 ；SUM函数计算单元格区域中所有数值的和

解释： = SUBSTITUTE(替换对象，被替换的字符串，替换的新字符串，替换第几个)

解释： =SUM(数值)

步骤：单击F9单元格输入公式：=SUM(--SUBSTITUTE(C9:C23,"元","")) - 按【Ctrl+Shift+Enter】

第16问：如何计算最高最低销售业绩

目的：根据每位销售员的销售业绩，计算最高销售业绩和最低销售业绩

说明：MAX函数返回一组数值区域中最大的值，忽略逻辑值和文本 ；MIN函数返回一组数值区域中最小的值，忽略逻辑值和文本

解释： =MAX（数值区域）

解释： =MIN（数值区域）

步骤：在F9单元格输入公式：=MAX(D9:D23) 在G9单元格输入公式：=MIN(D9:D23)

第17问：如何对数据设置上下限

目的：根据每位员工加班时长，在公司规定标准制度统计加班时长

说明：MAX函数返回一组数值区域中最大的值，忽略逻辑值和文本 ；MIN函数返回一组数值区域中最小的值，忽略逻辑值和文本

解释： =MAX（数值区域）

解释： =MIN（数值区域）

步骤：在D9单元格输入公式：=MIN(MAX(C9,1),4)

第18问：如何得出包含任何值中的最大值和最小值

目的：根据指定的数据（包含数字、日期和逻辑值），计算对用的最大值和最小值

说明：MAXA函数返回包含任何值中的最大值 ；MINA函数返回包含任何值中的最小值

解释： =MAXA（任何值）

解释： =MINA（任何值）

步骤：在F9单元格中输入公式=MAXA(B9:B14) ；在F11单元格中输入公式=MINA(B9:B14)

第19问：如何得出数值集中出现频率最高的值

目的：根据指定的数据，计算出现频率最高的值

说明：MODE函数返回数字集中出现频率最高的值，若对于出现频率相同的数值则根据数值排列顺序靠前得出结果。仅限于数字、日期、时间，对于文本及逻辑值则返回错误#N/A

解释： =MODE(值,值…)

步骤：在B18单元格中输入公式=MODE(B8:B17) - 向右填充

第20问：如何得出数字集中的中间值

目的：根据指定的数据，计算奇数数据和偶数数据的中间值

说明：MEDIAN函数返回数字集中的中间值

解释： =MEDIAN(值,值…)

步骤：在F8单元格中输入公式=MEDIAN(B8:B16)  ，在G8单元格中输入公式=MEDIAN(C8:C17) 

第21问：如何计算排名

目的：根据所有学生的总分，计算潘涛涛总分排名

说明：RANK函数返回某一个数值在某一区域内的排名

解释：=RANK(要排名的数字，比较区域，降序0升序1）

步骤：在J8单元格输入公式：=RANK(H14,H8:H22) 

第22问：如何计算排名前三销售业绩

目的：根据每位销售员业绩，计算前三名销售业绩

说明：LARGE函数计算从大开始指定位置的数值 ；ROW返回一个引用的行号

解释：=LARGE(区域，最大值位数）

解释：=ROW(单元格）

步骤：在G8单元格输入公式：=LARGE($D$9:$D$23,ROW(A1)) - 向下填充 

第23问：如何计算排名后三销售业绩

目的：根据每位销售员业绩，计算倒数三名销售业绩

说明：SMALL函数计算从小开始指定位置的数值 ；ROW返回一个引用的行号

解释：=SMALL(区域，最小值位数)

解释：=ROW(单元格）

步骤：在G8单元格输入公式：=SMALL($D$9:$D$23,ROW(A1)) - 向下填充

第24问：如何添加自动更新的序号

目的：让序号自动更新

说明：ROW返回一个引用的行号

解释：=ROW(单元格）

步骤：在B8单元格输入公式：=ROW()-7 - 向下填充

第25问：如何快速生成连续循环的序号

目的：快速生成连续循环的序号

说明：MOD函数返回两个数值相除的余数，返回结果的正负号与除数相同 ；ROW函数返回一个引用的行号

解释：=MOD(被除数，除数)

解释：=ROW(单元格）

步骤：在C9单元格输入公式：=MOD(ROW(A1)-1,3)+1 - 向下填充

第26问：如何统计已考人数

目的：统计专业课已考人数

说明：COUNT函数计算区域中包含数字的单元格的个数

解释：=COUNT(单元格区域）

步骤：在J8单元格输入公式：=COUNT(F8:F22)

第27问：如何统计缺考人数

目的：统计专业课缺考人数

说明：COUNTBLANK函数计算区域中空单元格的个

解释：=COUNTBLANK(单元格区域）

步骤：在J8单元格输入公式：=COUNTBLANK(F8:F22)

第28问：如何统计已签到人数

目的：统计已签到人数

说明：COUNTA函数计算区域中非空单元格的个数

解释：=COUNTA(单元格区域）

步骤：在E8单元格输入公式：=COUNTA(C8:C42)

第29问：如何对合并单元格填充序号

目的：对合并单元格填充序号

说明：COUNTA函数计算区域中非空单元格的个数

解释：=COUNTA(单元格区域）

步骤：选中需要填充序号的空白合并单元格区域 -输入公式：=COUNTA($C$8:C8) - 按【Ctrl+Enter】

第30问：如何快速填充中文数字序号

目的：填充中文数字序号

说明：ROW函数返回一个引用的行号 ；TEXT函数根据指定的数字格式将数值转换成本文

解释：=ROW(单元格）

解释：=TEXT(原数据，文本格式数据）

步骤：在C9单元格中输入公式="（"&TEXT(ROW(A1),"[dbnum1]")&"）" - 向下填充 ；[dbnum1]是将单元格的数值格式设置为中文小写数字，[dbnum2]是将单元格的数值格式设置为中文大写数字

内容较多就不一一展示详细内容咯，需要学习的伙伴，扫码添加微信回复指定的序号来领取视频课程吧！

第31问：如何对筛选后的数据自动更新序号

第32问：如何根据手机号前后位数统计个数

第33问：如何计算全勤奖

第34问：如何对不同部门根据不同比例分配奖金

第35问：如何根据部门分组填充序号

第36问：如何屏蔽错误值

第37问：如何解决求和区域出现错误值怎么汇总

第38问：如何解决引用单元格被函数遮住了怎么办

第39问：如何对隔列的数据求和

第40问：如何对任意两个日期之间销量进行求和汇总

第41问：如何根据分组统计部门人数

第42问：如何计算总分大于450分人数

第43问：如何快速统计出勤天数

第44问：如何统计指定月份的人数

第45问：如何查找未签到人员

第46问：如何快速查找重复的数据

第47问：如何统计不重复数

第48问：如何计算符合多重条件的人数

第49问：如何计算保留两种单位

第50问：如何进行四合五入

第51问：如何保留你想要的小数位数

第52问：如何将手机号中间四位数隐藏

第53问：如何将数值转换为货币格式

第54问：如何将数值转换为文本格式

第55问：如何快速删除多余的空格

第56问：如何比较字符串是否相同

第57问：如何区分产品型号字母大小写进行统计

第58问：如何去除首尾平均分在再求平均分

第59问：如何随机生成整数

第60问：如何随机生成小数

第61问：如何限定数据的范围

第62问：如何制作五星级评分

第63问：如何补全编号位数

第64问：如何引用另一张表中数据

第65问：如何计算业绩提成

第66问：如何计算业绩提成

第67问：如何查找重复值

第68问：如何根据行号和列号查找值

第69问：如何通过指定的值找位置

第70问：如何将文本型日期转换为数字

第71问：如何将英文月份转换为数字月份

第72问：如何对多个单元格内容进行合并

第73问：如何实现表格行列转换，数据自动更新

第74问：如何对特殊字符提取前后内容

第75问：如何提取单元格内字数不同的姓名

第76问：如何统计文本出现的次数

第77问：如何将日期类型拆分为年月日

第78问：如何将时间类型拆分为时分秒

第79问：如何对组合的日期和时间单独提取

第80问：如何根据借款周期（天）计算还款日

第81问：如何根据借款周期（月）计算还款日

第82问：如何将单独的年月日组合日期类型

第83问：如何按月求平均值

第84问：如何计算指定日期是星期几

第85问：如何计算指定日期是本年度第几周

第86问：如何快速计算转正日期

第87问：如何快速显示年月日时分秒时间格式

第88问：如何快速计算时间差

第89问：如何快速计算工龄

第90问：如何提取单元格左侧的数据

第91问：如何提取单元格中间的数据

第92问：如何提取单元格右侧的数据

第93问：如何通过身份证号码提取出生日期

第94问：如何通过身份证号码判断性别

第95问：如何通过身份证号码计算年龄

第96问：如何通过身份证号码计算生肖

第97问：如何通过身份证号码提取籍贯

第98问：如何通过身份证号码提取住址

第99问：如何制作九九乘法表

第100问：如何制作元旦倒计时

作者：Excel办公小课堂