Excel疑难问题集合

目录

引用问题.........................................................................1

如何在一列列出工作薄中的所有表（表名无规律）......................................1

18位身份证号码输入的几种解决方法...............................................1

用countif算出数值大小在50-60之间的单元格个数..................................1

几个工作表生成一个打印文件一次打印..............................................2

自动计算应收款滞纳金............................................................2

SUM函数难题....................................................................2

工作表中的小写字母都变成大写....................................................2

用COUNTIF计算整个B列中含有“F”这个字符的单元格的个数.....................2

自动记录出车情况.................................................................3

整点记录坐标值...................................................................3

请问如何把在Excel中绘制的曲线图表另存为jpg或gif格式的图片....................3

round函数.......................................................................3

工作表引用.......................................................................3

汇总.............................................................................4

在EXCEL2000中可否实现票面分解................................................4

年龄及奖学金问题.................................................................4

隔行求和问题.....................................................................5

在EXCEL里取数值的一部分用什么函数............................................5

用什么函数可以插入字符..........................................................5

将文件保存为以某一单元格中的值为文件名的宏怎么写...............................5

求余数的函数.....................................................................5

评委评分.........................................................................5

数据校对.........................................................................5

如何在一个单元格中自动输入在另外一个单元格中输入的文字........................6

在表格中查找我需要的东西并把该行所有的数据反映到另外的表格中去................6

在EXCEL表里建立唯一索引在该列输入重复的数值后自动提示输入错误..............6

发票小写金额填充.................................................................6

排列问题.........................................................................6

在条件语句中如何实现符合某个时期的条件的记录进行统计...........................7

行改列（不是转置问题）.............................................................7

如何给自动筛选中的自定义指定快捷键..............................................8

汇总问题.........................................................................8

统计包含某个字符的个数..........................................................9

如何用if函数判断，如果al单元格大于0,bl单元格为0是错误，为非0是正确...........9

我改进以下一个验证先进先出法公式................................................9

有无简洁一点的公式求如:al*bl+a2*b2+b3*b3…的和..................................9

工作中须将表格中大量的日期同时转换为中英文的星期几.............................9

我想查询表格中的某一列编号中有221这一编号时，引用其同行某列的数值............9

如何给自定义函数写上帮助信信息和参数说明.......................................9

如何在EXCEL中统计一个数在哪一行出现.........................................10

在SHEET51的A1做公式加总SHEETl:SHEET50的Al怎么做啊....................10

工作表间的数据输入..............................................................10

请问如何用公式达到我想要的结果.................................................10

一个页面在打印一次后是否能在上面的某一单元格数值上加1.................................................10

不用VBA,用EXCEL语句的IF函数，如何使用其条件关系.........................10

求和问题........................................................................11

排名问题........................................................................11

统计满足条件的数据并生成新的数据列.............................................11

工作表间的查找与复制...........................................................12

比较运算符为何要用引号.........................................................12

EXCEL中如何修改批注的字体和颜色...........................................12

如何以进阶筛选命令筛选出“序号”为单数、双数、或者是任意间距值的records.............12

n2=sum（b2到m2）、n3=sum（b3到m3）、有没有办法让n那一列可以自动填充........13

在工作表里有连续10行数据，现在要每行间格2行，要如何做........................13

当点到某单元格时单元格会出现一个下拉的列框，可以选取其中的数据...............13

查找与引用......................................................................13

如果D2>20那E2就显示$200、如果D2>30那E2就显示$300依此类推...............14

在两个工作表之间查找相同的内容并复制到新表....................................14

统计偶数单元格合计数值.........................................................14

按表1中A列的编号提取表1中的相应数据并自动录入到表2中的对应的相关列......14

用EXCEL做统计................................................................15

单元格是否有注释................................................................15

根据A1的内容，决定A2的数值是来自sheetl,sheet2还是sheet3...............................................15

能不能做到让符合条件的单元格所在的列自动隐藏..................................15

直接输入一组数如“20020213101235”后，自动转换成日期格式.....................16

把sheet1到sheet200的al9这一格，依序贴到sheet。的al到a200........................................16

A列记录几百条，如何对这列计数（重复的数值不计）................................16

如果有文本串"YY0115",我想取第三、四的值"01",应该用什么函数.................16

怎样将文字和数字分2列显示.....................................................17

500个不连续的数之和的求法......................................................17

如何使输入的英文单词第一个字母变成大写........................................17

在一个表中有两列日期型数字请问如何在第三列中得到其差（两日期间的天数）..........18

重要的EXCEL文件坏了（文件带密码）有没有EXCEL修复工具........................18

只要求简单的把数据从分表直接追加到总表........................................18

A1中输入11:22:22要求每间隔一秒刷新一次.......................................18

怎样用函数来显示某月的最后一天是几号...........................................18

如何用excel求解联立方程........................................................18

请问如何在函数中同时使用两个条件...............................................19

用TRIM把“中心是”中间的空格去掉........................................19

将EXCEL中的单元格定义成数组.................................................19

将单元格行、列高与宽单位设置成毫米.............................................19

一些电话号码如果是8结尾,号码加1;如果不是,号码加0............................................................19

当做日报表时，怎样让月累计数自动加上............................................19

单元格Al=（a2,a3,a4,a5）其中一个就返回AA,如果Al=a6,就返回BB,否则就返回CC.20

关于引用及计算的问题...........................................................20

如何统计某个列有数字的个数.....................................................20

如何统计此次自动筛选出来共有的记录条数.......................................20

怎么把等于A栏里的一个值的，再统计出B栏里不同数据的个数......................20

如何用IF函数达到我想要的结果..................................................21

关于实现“查找并替换”的宏.....................................................21

有关输入数字的提示.............................................................21

如何判断某个单元格包含某个字符.................................................22

按条件设定数值..................................................................22

小数的进位问题..................................................................23

找出A列的数字在B列中所在的位置，并在第三列显示单元格号....................23

从G9到G71,需要隔行求和.....................................................23

在单元格返回工作表名称.........................................................23

多条件计算公式..................................................................23

如何统计求每天不重复的值.......................................................23

使用vlookup函数的问题..........................................................24

20列间隔3列求和...............................................................24

如何实现把一个工作表中的数据根据另外一个表的数据分成两部分...................24

查找引用........................................................................24

需要验证因此必须在要列示出计算过程有什么办法..................................25

在制作Excel图表时，如何控制X轴的刻度显示....................................25

条件求和........................................................................25

怎样查找某列中文本长度不足X位的数据.........................................26

WORD向EXCEL格式转换时一个单词或汉字分别填入EXCEL中的一个单元格中....26

如何将名称相同的字段合并汇总，且只显示同一个名称..............................26

按工号大小排列..................................................................26

两日期间的天数..................................................................27

序列问题........................................................................27

条件乘积的求和..................................................................27

如何判断........................................................................27

用字母在单元格里面填入平方米...................................................27

在Excel中打印条形码............................................................28

求工龄..........................................................................28

把26个表格汇总求和成一个汇总表................................................28

重复数据得到唯一的排位序列.....................................................28

从身份证号码中提取性别.........................................................28

如何从文本文件中取数...........................................................28

怎样读取数字....................................................................29

怎样才能快速算出...............................................................29

如何实现对日期格式的要求条件...................................................30

截取单元格里某个字符后的字符...................................................30

用if函数判断...................................................................31

分别显示总分最高的同学的班级、姓名、总分......................................31

定有如上两列数据A和B现在想要统计满足条件B=8的并且在C列自动生成数据…•…31

排名问题........................................................................32

研究彩票，从统计入手...........................................................32

去掉XLS文件头上菜单栏的“MicrosoftEXCEL”字样.............................32

替换数据........................................................................32

复制数据再转置,不复制转置被隐藏的行或列........................................33

如何始终打开默认的工作表.......................................................33

如何分割文本....................................................................33

两列合一列......................................................................33

每次清除数据时能否作到跳过隐藏的单元格........................................34

也就是单击Sheet2时，在Sheetl的A列的最后一个记录的下一行自动填上“End”.........34

用函数将输入的月份转换为这个月所包含的天数....................................34

介绍经验：就SUM函数来讲，以下动态地址可行....................................34

在EXCEL中如何统计字数........................................................35

如何自动填充内容...............................................................35

工作表的标签的字体和大小可以更改吗............................................35

自定义格式的体会...............................................................35

再次显示出被隐藏掉了的行（第1行）............................................35

如何定义有效数字...............................................................35

sheetl工作表的Al、A2、A3单元格分别链接到sheet2、sheet3、sheet4...............................36

用SUMIF函数进行条件求和，不限于一个条件时如何设置参数......................36

如何在excel中已有的数值前加零变成六位.........................................36

如何提取工作表中的背景图片.....................................................36

绘制有三条斜线的表头...........................................................36

在A列有一组数据不是按照大小顺序排列在B列中排名.............................36

有无办法让B2所在行都呈红色字体...............................................36

现有12个工作表，是12张发票，建立一个汇总表，将发票号和金额汇总显示在一张表里……37

经验技巧........................................................................37

在一个不对称的区域中如（bl:G7）中找到A行一组数据中的某个数并自动变红.......37

不借助第三列而直接用函数或公式一步得到sum（a2/b2,a3/b3,...）的结果................37

请问要如何算出每个月有几个星期一、二、三….日.................................37

让隐藏的列或行不参预计算.......................................................37

一次删完Excel里面多出很多的空白行.............................................37

表1、表2分别有20个人的基本情况和其中10个人的名字，让表1的数据自动填充到表2

...............................................................................................................................................................38

使用vlookup函数返回#N/A符号时将此符号用0或空格来代替.......................38

通过条件格式将小计和总计的行设为不同的颜色....................................38

复制隐藏后的表格到一个新表格中使被隐藏的内容不显示............................38

如何将一个工作簿中的一个Sheet隐藏.............................................38

工具菜单与视图中的工具栏不同...................................................38

查找并填写符合条件的单元格内容.................................................38

填写空白行......................................................................39

制订下月计划并显示为中文.......................................................39

&的用法........................................................................39

有5行数据在每行上面个插入1行.................................................39

可以检查一张表里是否有漏重的数字吗............................................40

怎样将单元格中的公式转换为数值.................................................40

条件求和........................................................................40

A1单元格为出生日期，可用=DATEDIF(Al,NOW(),"y")计算其年龄...................40

这个公式是什么意思？K7=if(AND(R7>3000,Q7>0.5),P7)...................................................40

统计数据问题一例...............................................................40

关于条件求和问题！.............................................................41

请教关于条件乖积的求和问题.....................................................41

文件修复........................................................................41

显示隐藏的工作表...............................................................42

这样一列如何筛选出含201的.....................................................42

两个日期相差的月份数...........................................................42

用函数实现连续相加.............................................................43

把计算结果为负值的显示为红色取整并在数字后面加上“仟元”.....................43

比较A、B两列数据并在A列中包含B列的数据删除...............................43

怎样让我的图表随着数据透视表的更新............................................43

循环引用问题....................................................................44

如何才能有条件的引用某一区的内容...............................................44

Excel基本功....................................................................44

解除officeXP50次限制..........................................................45

小写数字转换成人民币大写.......................................................46

中国式的排名函数应用...........................................................50

当做日报表累计数自动加.........................................................50

工资条问题......................................................................51

定制单元格数字显示格式.........................................................52

关于数据引用的问题.............................................................53

如何使EXCEL应用程序锁定不让人打开...........................................53

数组的运算法则..................................................................54

替换数据........................................................................54

几个技巧........................................................................55

如何在两个工作表之间进行数据交换...............................................55

显示数值所在的单元格号.........................................................55

if超过7层如何办...............................................................55

一个单元格内格式问题...........................................................57

怎样用函数求出一定范围内的数值的偶数的个数....................................57

如何使某列的数据不重复.........................................................57

请问如何能使随机数不重复.......................................................57

日期型编号......................................................................58

如何将数字改变为字符串.........................................................58

在Excel中如何自定义数字格式...................................................58

在单元格显示负数为红字后又紧跟汉字............................................59

检索问题........................................................................60

微软到底对我们隐藏了多少.......................................................60

求一组数中第一个出现的正数.....................................................60

能否使表格不能打印.............................................................61

有无这样的函数..................................................................61

能实现两栏数据中按特定条件进行比较并做出相应记号吗............................61

如何对文本格式的数字进行筛选...................................................61

如何筛选出特定行................................................................61

Word加启动密码.................................................................61

有关大小写的转换................................................................62

在Excel中打造自己的“公式保护”菜单...........................................62

如何让EXCEL自动从头统计到当前单元格.........................................63

请问想要取当前单元格的列号，用什么函数........................................63

SUMIF函数.....................................................................63

怎么能快速的将两个单元格的内容互换............................................63

如何能到两个时间段的17:00-8:00小时数?..........................................64

如何在单元格返回工作表名称.....................................................64

如何在输入数字的加减乘除按ENTE后能在另一单元格自动出现计算数值.............64

有Al,Bl,Cl,D1四个单元格D1的值要随着A1的变化而变化...................65

对A列不重复的数值计数.........................................................65

永恒的求和......................................................................65

如何使用EXCEL进行动态跨表取数...............................................66

如何使用器寸照表..................................................................66

如何在单元格返回工作表名称.....................................................67

何在一列数据中统计限定范围的数据...............................................67

编号：

时间：2021年X月X日书山有路勤为径，学海无涯苦作舟页码：第1页共73页

引用问题

我在excel中想实现这么一个功能，如单元格D12有一个数据是4,现在我想引用A4的数据，但4是由D12

提供的，即如何实现A4=A(D12)。也就是，在Excel中，A7单元，能否实现把后面的数字用算式来代替，

如A(3+4),或者是单元格的嵌套，A(D12),恳请高手解答。

解答：①二indirect(〃a〃&dl2)

②我的想法：借一个单元格如B4用，键入二〃A〃&D12在需引用的单元格键入二INDIRECT(B4)

即可，第二个：C4是3,C5是4B4=〃A"&C4+C5，同①。

如何在一列列出工作薄中的所有表(表名无规律)

以下宏将在a列传回所有工作表名称。(很实用)

SubMacoK)

Fori=1ToSheets.Count

Cells(i,1)=Sheets(i).Name

Next

EndSub

18位身份证号码输入的几种解决方法

相信在EXCEL这个软件中如何输入15位或18位身份证号码的问题已经困挠了许多人，因为在EXCEL中，

输入超过11位数字时，会自动转为科学计数的方式，比如身份证号是：123456789012345,输入后就变成

了：1.23457E+14,要解决的方法有非常非常.....多种哦，呵呵，现在为大家说几种比较简单快速的方法：

1、在A1单元输入号码时，在前面先输入'号，就是：'123456789012345,这样单元格会默认为该单

元为文本方式，会完整显示出15个号码来，而不会显示那令人讨厌的科学计数方式来了。

2、如果已经输入了大量的号码，全部以科学计数显示的话，而又懒的按照上面的方法一个一个的重新

输入的话，也有这个好方法哦，比如已在单元Al：A100输入了号码，按以下步骤做：》选择单元Al：A100》

单击鼠标右键，设置单元格式》选择自定义，在‘类型'中输入'0'即可，轻松搞定，呵呵！！

3、还有一种用函数来解决的方法:在ALA100已经输入大量的号码时，在B1单元中输入:=trim(''al),

注意两个'之间是空格，这个公式的意思:先在A1单元15位号码前加个空格符号，然后用trim这个函数

把空格符号去掉。输完后，B1单元也是显示出完整的15位数字了。

用countif算出数值大小在50-60之间的单元格个数

解答：①你可以综合一下呀！=countif(al:a9,"〉=50")-countif(al:a9,">60")

②{=SUM((al:a9<60)*(al:a9>50))}

③为什么{=51»(缶169〈60)*6169〉50))}可以求出符合条件的个数，按理后面应再加一待求和区域如

等等，不知数组计算的内部机制是怎样的，不知谁能给个解释，谢谢！！

④这个数组公式是这样运算的：al:a9区域的数值分别与60比较。假如al:a9的数依次为

15,25,35,45,55,65,75,85,95.那么(al:a9<60)返回

{true,true,true,true,true,false,false,false,false},同理(al:a9>50)返回

{false,false,false,false,true,true,true,true,true}。然后(al:a9〈60)*(al:a9〉50)则返回

{false,false,false,false,true,false,false,false,false}«因为*表示and的意思。只有当and的对象

都为真时结果才为真。。excel里用1表示true;。表示false。有时需要将true或false转换成数值，可

第1页共73页

编号：

时间：2021年X月X日书山有路勤为径，学海无涯苦作舟页码：第2页共73页

以*1或+0

几个工作表生成一个打印文件一次打印

解答：先按Ctrl键+其它要一起打印的工作表就会成为一个群组,打印的页数可延续到下一个Sheet

自动计算应收款滞纳金

要求在给定的应收日期、滞纳金率、当前日期(自动取)的基础上自动计算出应收滞纳金。

解答：=(DATEDIF(应收日期,NOW(),“『))*滞纳金率(每天)*应收金额

SUM函数难题

AB

234230

12

1013

如何用函数计算出下面几个数据

1«A和B都有数据且A〉B的个数

2oA栏在相应的B栏有数据的情况下的数据总和，例如A1对应B1有数据，A3对应B3有数据，则求A1+A3

的和。希望都能用一个函数求出所要的结果。谢谢

解答：=SUM(($A$1:$A$1O〈〉"")*($B$1:$B$1O〈"")*($A$1:$A$1O〉$B$1:$B$1O))

记得输入公式后要按ctrl+shift+enter

工作表中的小写字母都变成大写

解答：请运行以下程序：(测试通过)

SubConvertToUpperCase()

DimRngAsRange

Worksheets(〃Shcctl〃).UscdRangc.Select

ForEachRngInSelection.Cells

IfRng.HasFormula=FalseThen

Rng.value=UCase(Rng.value)

EndIf

NextRng

EndSub

用COUNTIF计算整个B列中含有“F”这个字符的单元格的个数

cl=C0UNTIF(bl:bl30,bl29),(bl29单元格内的字符是"F")，问题是随后在向表格中添加新行131、132、

133.....之后，cl单元格中公式统计的结果还是1T29行的，怎么才能让cl中实时显示添加新数据后的

统计结果？

解答：cl=C0UNTIF(b:b,bl29)

第2页共73页

编号：

时间：2021年X月X日书山有路勤为径，学海无涯苦作舟页码：第3页共73页

自动记录出车情况

现有一表格，每天记录50辆不同车号的车辆的“出车里程”和“回库里程”，需要打印一清单，自动统计

每辆在库车辆的当前里程，请问该如何做(结果放在其它sheet中，为方便显示这里和数据放在一个sheet

中了)？

解答：可以用数组公式：

假设”此处数据不断增加”所在工作表的名字为"SHEET1”.在〃此处需要更新〃所指的单元格内输入公式

=MAX(IF(OFFSET(Sheetl!$A$2,,,COUNTA(Sheet1!$A$2:$A$65536))=A2,OFFSET(Sheet1!$B$2,,,COUNTA(Sh

eetl!$B$2:$B$65536))))”,按CTRL+SHIFT+ENTER结束，之后向下填充公式.

整点记录坐标值

我在监测一个点的电压值，每天正点记录一次，这样每天就产生了24个值，现要进行一个月，共产生了

720个值。根据这720个值，用EXCEL作一个图表，横坐标是时间，单位是一一天，纵坐标是测量值。难

点：横坐标每个刻度为一天，如设分类轴刻度线之间的分类数为24的话，横坐标每个刻度下显示为：125

49,而其单位为天，那不就成了第1天过了，就到了第25天？如设分类轴刻度线之间的分类数为1的话,

可是我每天有24个值？

解答：我试验了一下，发现还是能够解决的。横坐标为时间，每天24小时，一个月30天，共计720个数

据，纵坐标为数值，这样做出来的表格非常长，数据这样排，第一列(0小时，1小时，.....23小时，0

小时，1小时,.....23小时，)这样总共重复30天，第二列为每天24个数据，做折线图(第四种)，先按

照向导一步步做下去，删除第一个系列，就可以了。做出来的图像横坐标(分类轴)要调整，刻度调整为

标签间的分类数为1,就可以达到目的！剩下可以手工在每天的开头处加上日期(可以用文本框)，不知道

这样解决可以吗？

请问如何把在Excel中绘制的曲线图表另存为jpg或gif格式的图片

解答：选定图表部分，复制它，打开可以编辑jpg或gif格式的软件，然后粘贴保存为jpg或gif格式文

件即可。

round函数

解答：不要用工具条上的显示小数位数来控制小数位数，它只是显示这几位，实际还是按原来的数计算。

如：1.2345,你显示两位，为1.23,而实际他是按1.2345去计算的，要用round函数，round(数据,2)即

可。如果怕繁的话，可在选项里设置按显示计算，呵呵，试试看。

解答2：我是做财务工作的，这一点有较深的体会

就是无论用什么函数都要在最外面加上round。，不然的话因为那一两分钱定会被领导狠狠说一顿

再有两条经验

1、如果准备作大一些的表格，尽量不要使用“合并单元格”以免以后使用函数麻烦；

2、要分清你作的表格主要是使用来存储数据，还是用来打印输出，如果想两者兼顾，小一点的表格还可以，

大了就不好办了。

工作表引用

比如说现在Sheet2!al引用Sheetl!al,Sheet3!al引用Sheet2!al,那么有什么办法可以表示Sheet(n)!al

第3页共73页

编号：

时间：2021年X月X日书山有路勤为径，学海无涯苦作舟页码：第4页共73页

引用Sheet(n-1)!al。我是Execl方面的菜鸟，希望各位大虾能给我解决的方法或者思路也行啊。

解答1：用VBA.Sheets(n).Cells(a,1)=Sheets(n-1).Cells(a,1)

又问：ActiveCell.FormulaRlCl=，Z=SUM(RC[-2]:RC[-1],!R[-1]C)〃.在处，我应该怎么填，

才可以得到上一张表格？

答：在空格处写上：sheets(activesheet,index-1).注意如果当前工作表是第一张