EXCEL基础攻略与实战技巧.docx
《EXCEL基础攻略与实战技巧.docx》由会员分享，可在线阅读，更多相关《EXCEL基础攻略与实战技巧.docx（80页珍藏版）》请在冰豆网上搜索。
EXCEL基础攻略与实战技巧
引用问题
我在excel中想实现这么一个功能，如单元格D12有一个数据是4，现在我想引用A4的数据，但4是由D12提供的，即如何实现A4=A（D12）。
也就是，在Excel中，A7单元，能否实现把后面的数字用算式来代替，如A（3+4），或者是单元格的嵌套，A（D12），恳请高手解答。
解答：
①=indirect（"a"&d12）
②我的想法：
借一个单元格如B4用，键入="A"&D12在需引用的单元格键入=INDIRECT（B4）
即可，第二个：
C4是3，C5是4B4="A“&C4+C5,同①。
18位身份证号码输入的几种解决方法
相信在EXCEL这个软件中如何输入15位或18位身份证号码的问题已经困挠了许多人，因为在EXCEL中，输入超过11位数字时，会自动转为科学计数的方式，比如身份证号是：
123456789012345，输入后就变成了：
1.23457E+14,要解决的方法有非常非常......多种哦，呵呵，现在为大家说几种比较简单快速的方法：
1、在A1单元输入号码时，在前面先输入’号，就是：
’123456789012345,这样单元格会默认为该单元为文本方式，会完整显示出15个号码来，而不会显示那令人讨厌的科学计数方式来了。
2、如果已经输入了大量的号码，全部以科学计数显示的话，而又懒的按照上面的方法一个一个的重新输入的话，也有这个好方法哦，比如已在单元A1：
A100输入了号码，按以下步骤做：
》选择单元A1：
A100》单击鼠标右键，设置单元格式》选择自定义，在‘类型’中输入‘0’即可，轻松搞定，呵呵！
！
3、还有一种用函数来解决的方法：
在A1：
A100已经输入大量的号码时，在B1单元中输入：
=trim（''a1）,注意两个’之间是空格，这个公式的意思:
先在A1单元15位号码前加个空格符号，然后用trim这个函数把空格符号去掉。
输完后，B1单元也是显示出完整的15位数字了。
如何在一列列出工作薄中的所有表（表名无规律）
以下宏将在a列传回所有工作表名称。
（很实用）
SubMaco1（）
Fori=1ToSheets.Count
Cells（i,1）=Sheets（i）.Name
Next
EndSub
用countif算出数值大小在50~60之间的单元格个数
解答：
①你可以综合一下呀!
=countif（a1:
a9,">=50"）-countif（a1:
a9,">60"）
②{=SUM（（a1:
a9<60）*（a1:
a9>50））}
③为什么{=SUM（（a1:
a9<60）*（a1:
a9>50））}可以求出符合条件的个数，按理后面应再加一待求和区域如"B1:
B9"等等，不知数组计算的内部机制是怎样的，不知谁能给个解释,谢谢！
！
④这个数组公式是这样运算的：
a1:
a9区域的数值分别与60比较。
假如a1:
a9的数依次为15,25,35,45,55,65,75,85,95.那么（a1:
a9<60）返回{true,true,true,true,true,false,false,false,false}，同理（a1:
a9>50）返回｛false,false,false,false,true,true,true,true,true}。
然后（a1:
a9<60）*（a1:
a9>50）则返回{false,false,false,false,true,false,false,false,false}。
因为*表示and的意思。
只有当and的对象都为真时结果才为真。
。
excel里用1表示true;0表示false。
有时需要将true或false转换成数值，可以*1或+0
几个工作表生成一个打印文件一次打印
解答：
先按Ctrl键+其它要一起打印的工作表就会成为一个群组,打印的页数可延续到下一个Sheet
自动计算应收款滞纳金
要求在给定的应收日期、滞纳金率、当前日期（自动取）的基础上自动计算出应收滞纳金。
解答：
=（DATEDIF（应收日期,NOW（）,"d"））*滞纳金率（每天）*应收金额
SUM函数难题
A B
234 230
12
10 13
如何用函数计算出下面几个数据
1。
A和B都有数据且A>B的个数
2。
A栏在相应的B栏有数据的情况下的数据总和，例如A1对应B1有数据，A3对应B3有数据，则求A1+A3的和。
希望都能用一个函数求出所要的结果。
谢谢
解答：
=SUM（（$A$1:
$A$10<>""）*（$B$1:
$B$10<>""）*（$A$1:
$A$10>$B$1:
$B$10））
=SUM（（$A$1:
$A$10<>""）*（$B$1:
$B$10<>""）*（$A$1:
$A$10>$B$1:
$B$10）*（$A$1:
$A$10+$B$1:
$B$10））
记得输入公式后要按ctrl+shift+enter
工作表中的小写字母都变成大写
解答：
请运行以下程序：
（测试通过）
SubConvertToUpperCase（）
DimRngAsRange
Worksheets（"Sheet1"）.UsedRange.Select
ForEachRngInSelection.Cells
IfRng.HasFormula=FalseThen
Rng.value=UCase（Rng.value）
EndIf
NextRng
EndSub
用COUNTIF计算整个B列中含有“F”这个字符的单元格的个数
c1=COUNTIF（b1:
b130,b129），（b129单元格内的字符是“F”），问题是随后在向表格中添加新行131、132、133.....之后，c1单元格中公式统计的结果还是1-129行的，怎么才能让c1中实时显示添加新数据后的统计结果?
解答：
c1=COUNTIF（b:
b,b129）
自动记录出车情况
现有一表格，每天记录50辆不同车号的车辆的“出车里程”和“回库里程”，需要打印一清单，自动统计每辆在库车辆的当前里程，请问该如何做（结果放在其它sheet中，为方便显示这里和数据放在一个sheet中了 ）？
解答：
可以用数组公式:
假设"此处数据不断增加"所在工作表的名字为"SHEET1".在"此处需要更新"所指的单元格内输入公式=MAX（IF（OFFSET（Sheet1!
$A$2,,,COUNTA（Sheet1!
$A$2:
$A$65536））=A2,OFFSET（Sheet1!
$B$2,,,COUNTA（Sheet1!
$B$2:
$B$65536））））",按CTRL+SHIFT+ENTER结束,之后向下填充公式.
整点记录坐标值
我在监测一个点的电压值，每天正点记录一次，这样每天就产生了24个值，现要进行一个月，共产生了720个值。
根据这720个值，用EXCEL作一个图表，横坐标是时间，单位是----天，纵坐标是测量值。
难点：
横坐标每个刻度为一天，如设分类轴刻度线之间的分类数为24的话，横坐标每个刻度下显示为：
12549，而其单位为天，那不就成了第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!
a1引用Sheet1!
a1,Sheet3!
a1引用Sheet2!
a1，那么有什么办法可以表示Sheet（n）!
a1引用Sheet（n-1）!
a1。
我是Execl方面的菜鸟，希望各位大虾能给我解决的方法或者思路也行啊。
解答1：
用VBA.Sheets（n）.Cells（a,1）=Sheets（n-1）.Cells（a,1）
又问：
ActiveCell.FormulaR1C1="=SUM（RC[-2]:
RC[-1],_______!
R[-1]C）".在_____处，我应该怎么填，才可以得到上一张表格？
答：
在空格处写上：
sheets（activesheet.index-1）.注意如果当前工作表是第一张（即activesheet.index-1＝0）时会出错。
或：
=INDIRECT（"sheet"&A1&"!
$A$1"）
汇总
有这样一个问题，学校里共有24个科目（每个科目都有其代码，每门科目都有它相应的教材费），但学生最多只能选其中４门科目，那么我在一个表里每个记录中存储学生所选的４门科目的代码，并要在该表里汇总４门科目的总教材费，怎么汇总。
（各门科目代码及对应的教材费存在另一个表里）。
解答：
你可以这样：
1，表2：
序号科目教材费/1语文120/2数学100/3英语150/4政治70/5历史110/6地理90/7物理250/8化学180
2，表1：
姓名科目1科目2科目3科目4教材费合计/赵1234440*/钱2345430/孙3456420/李4567520/张5678630.其中教材费单元格（打*号处，即F2）公式如下：
=VLOOKUP（B2,Sheet2!
A:
C,3）+VLOOKUP（C2,Sheet2!
A:
C,3）+VLOOKUP（D2,Sheet2!
A:
C,3）+VLOOKUP（E2,Sheet2!
A:
C,3）。
其下单元格复制公式。
在EXCEL2000中可否实现票面分解
解答：
我在excel中试验了一下，发现票面分解还是比较简单的，下面是我的做法：
ABCD
1票面金额655389.26/2面值张数余额/31006553（公式为：
int（b1/a3）89.26（公式为：
b1-c3*a3）/4501（公式为：
int（d3/a4）39.26（公式为：
D3-A4*C4）/520119.26/61019.26/7514.26/8220.26/9100.26/100.500.26/110.210.06/120.100.06/0.0510.01/0.0200.01/0.0110.00/第五行以下的数据就不需要一一输入了，选中C列和D列（C4：
D15），按ctrl+D,结果就出来了。
年龄及奖学金问题
打开Excel工作表，建立如下表格所示的表格：
ABCDEFGHIJKLM
姓名性别生日年龄年级语文数学外语政治平均工资奖励总额
王明男 289989287
1设置生日为11-28-1998的形式，并根据生日计算出学生的年龄。
/2对平均分>=90分，80<=平均分<90，60<=平均分<80的奖励金额数放在单元格N5..N7中，请利用公式计算出每位同学赢得的奖金。
/31年级学生工资为80元，2年级工资为100元，3年级工资为110元，请计算出每位同学的工资数，并根据工资数和奖励数计算每位同学的工资总额。
解答：
1.计算年龄:
=INT（（（YEAR（NOW（））-YEAR（C2））*12+MONTH（NOW（））-MONTH（C2））/12）（注意学生生日在C列）
2.计算工资:
=IF（LEN（E2）>0,CHOOSE（E2,80,100,110）,""）（注意学生年级在E列）
3.奖励:
=IF（J2>=90,$N$5,IF（J2>=80,$N$6,IF（J2>=60,$N$7,""）））（平均分在J列）
隔行求和问题
若有20列（只有一行），需没间隔3列求和，该公式如何做？
前面行跳跃求和的公式不管用。
解答：
假设a1至t1为数据（共有20列），在任意单元格中输入公式：
=SUM（IF（MOD（TRANSPOSE（ROW（1:
20））,3）=0,（a1:
t1））/按ctrl＋shift＋enter结束即可求出每隔三行之和。
在EXCEL里取数值的一部分用什么函数
打比方说有一个数字123456，我只想取它的后三位，也就是说只需要456，应该用什么函数。
解答：
假设A1的值为123456,用=RIGHT（a1,3）可以取其后三位./ 你如果想取其中的几位，不妨试试mid函数。
用什么函数可以插入字符
00020304T02390400020304T0239/00020404T02110400020404T0211/00020604T01990400020604T0199/00020704T02160400020704T0216/00021304T02410400021304T0241/00021404T02220400021404T0222/00021504T02220400021504T0222/00021704T01390400021704T0139/就像上表一样，怎样用第一列的数据形成第二列的数据，即在特定位置加上几个相同字符串？
解答：
b1="04"&a1
问：
哪如果倒过来呢？
答：
用公式A1=MID（B1,3,13）或A1=SUBSTITUTE（B1,"04","",1）
将文件保存为以某一单元格中的值为文件名的宏怎么写
解答：
假设你要以Sheet1的A1单元格中的值为文件名保存，则应用命令：
ActiveWorkbook.SaveCopyAsStr（Range（"Sheet1!
A1"））+".xls"
A1是文本串的话，使用这条命令：
ActiveWorkbook.SaveAsRange（"A1"）&".xls"。
当然，如果你的A1是路径，F1是文件名的话，可以这样写：
ActiveWorkbook.SaveAsRange（"A1"）&Range（"F1"）&".xls"
求余数的函数
比如：
A1=28，A2=（A1÷6）的余数=4，请问这个公式怎么写？
解答：
=MOD（28,6）
评委评分
解答：
用两个函数可解决：
rank（排高低）average（求平均）。
也可以用：
{=（SUM（$B$1:
$B$10）-SUM（LARGE（$B$1:
$B$10,ROW（$A$1:
$A$2）））-SUM（SMALL（$B$1:
$B$10,ROW（$A$1:
$A$2））））/（COUNT（$B$1:
$B$10）-4）}（可以解决有多个最高和最低分的问题）。
数据校对
已知:
A2=SUM（A3:
A20）,B2=SUN（B3:
B20）,C2=SUM（C3:
C20）,D2=SUM（d3:
d20）,E2=SUM（E3:
E20）、又知A2=B2+C2=D2+E2。
需要解决的问题:
当B2+C2或者D2+E2不等于A2时,系统自动提示错误,请各位高手给予指点,是否一定要通过宏才可以解决,有没有更简单的办法?
解答：
=IF（AND（B2+C2=SUM（A3:
A20）,D2+E2=SUM（A3:
A20））,SUM（A3:
A20）,":
（"）
如何在一个单元格中自动输入在另外一个单元格中输入的文字
解答：
a2中输入a1的文字,则a2=indirect（"a1"）
在表格中查找我需要的东西并把该行所有的数据反映到另外的表格中去
解答：
比如找表A中B列数值等于5的数。
在表B中的A1单元输入：
=VLOOKUP（5,A!
B1:
B4,1）
在EXCEL表里建立唯一索引在该列输入重复的数值后自动提示输入错误
解答：
1、选定你要限制输入的列（假设是A2:
A20），选菜单的“数据”－“有效性”；
2、在许可条件中选定“自定义”，在公式一拦中，输入你要求的限制，例如：
“=COUNTIF（$A$2:
$A$20,A2）=1”。
3、你还可以在“输入信息”和“出错信息”输入一些提示信息。
不过，你一定要注意！
这个功能只能在你从键盘上键入数据时有效！
拷贝和粘贴数据的操作是无效的。
发票小写金额填充
我输入123456.52它自动给拆成￥12345652的形式并且随我输入的长度改变而改变？
解答：
我所知函数不多，我是这样做的，如有更方便的方法，请指点
例如：
在A1输入小写金额，则：
千万：
B1=IF（A1>=10000000,MID（RIGHTB（A1*100,10）,1,1）,IF（A1>=1000000,"￥",0））
百万：
C1=IF（A1>=1000000,MID（RIGHTB（A1*100,9）,1,1）,IF（A1>=100000,"￥",0））
十万：
D1=IF（A1>=100000,MID（RIGHTB（A1*100,8）,1,1）,IF（A1>=10000,"￥",0））
万：
E1=IF（A1>=10000,MID（RIGHTB（A1*100,7）,1,1）,IF（A1>=1000,"￥",0））
千：
F1=IF（A1>=1000,MID（RIGHTB（A1*100,6）,1,1）,IF（A1>=100,"￥",0））
百：
G1=IF（A1>=100,MID（RIGHTB（A1*100,5）,1,1）,IF（A1>=10,"￥",0））
十：
H1=IF（A1>=10,MID（RIGHTB（A1*100,4）,1,1）,IF（A1>=1,"￥",0））
元：
I1=IF（A1>=1,MID（RIGHTB（A1*100,3）,1,1）,IF（A1>=0.1,"￥",0））
角：
J1=IF（A1>=0.1,MID（RIGHTB（A1*100,2）,1,1）,IF（A1>=0.01,"￥",0））
分：
K1=IF（A1>=0.01,RIGHTB（A1*100,1）,0）
（编者注：
公式中最后一个0应改为""）
排列问题
已知1，2，3，4共4个数字符号构成的4位数的全排列有256个，如：
1234，2341，3245等等，现在我需要将这256个数字全部列出，如果用手写笔算的话既麻烦又容易出错，不知可否用Excel来解决这个问题?
解答：
在单元格输入公式“=1111+1000*INT（（ROW（）-1）/64）+100*MOD（INT（（ROW（）-1）/16）,4）+10*MOD（INT（（ROW（）-1）/4）,4）+MOD（ROW（）-1,4）”拖放到A256。
在条件语句中如何实现符合某个时期的条件的记录进行统计
比如有1-12月份的记录单,需要实现对每个月里些数据的统计汇总/（格式如何?
）
解答：
以下公式,A列为日期列,B列为数据,要求计算1月份的累计:
=SUM（IF（MONTH（A:
A）=1,B:
B,0））
此为数组公式,在输完公式后,不要ENTER,而要CTRL+SHIFT+ENTER.
行改列（不是转置问题）
1.原内容为行（每行5列内容共几千行）：
列1列2列3列4列5
行112345/行212345/行312345/以下类同…/…
2.改为行排列（每12行5列内容共12组，排为一行）
列1列2列3列4列5列6列7列8列9列10……（60列）
原1-12行1234512345……（60列）/原13-24行1234512345……（60列）/原25-36行1234512345……（60列）/以下类推…
本人不会编写宏，让您见笑曾费劲手工移动录制了一个取2400行（每行5列内容）改列的宏。
因原有工作表行不断增加，经常需要行改列作其它用途，录制的宏不能满足需要。
烦请大家帮助写一自动循环取12行5列内容排列为一行的宏。
规律：
1.1-12行1-5列内容排在第1行（第一行1-5列、第二行1-5列、类推，完成为60列）
2.13-24行1-5列内容排在第2行（第13行1-5列、第14行1-5列、类推）
3.25-36行1-5列内容排在第3行（第25行1-5列、第26行1-5列、类推）
4.以下类推…
解答：
Sub转换（）
DimnumcolAsInteger
DimnumrowAsLong
DimiAsLong
DimxAsInteger
DimnumperrowAsInteger
numperrow=InputBox（"请输入每行要填的数据行的数目:
"）
Range（"数据"）.Select
numrow=Selection.Rows.Count'数据区的行数
numcol=Selection.Columns.Count'数据区的列数
x=numperrow*numcol
Range（"a1"）.Select
Fori=1Tonumrow'以数据的每一行为单位进行剪切
Range（"数据"）.Rows（i）.Cut
ActiveSheet.Paste
Selection.Offset（,numcol）.Select
If（iModnumperrow）Then'判断是否要换行
Else:
Selection.Offset（1,-x）.Select
EndIf
Nexti
EndSub
'本程序需要把要变换的数据命名为"数据"（数据区的列数可以任意,不一定是5）方法:
选中该区域,在名称框内输入"数据"即可,然后按程序要求输入每行要填的数据行的数目（如本例中的12）
如何给自动筛选中的自定义指定快捷键
解答：
Sub自动筛选（）
OnErrorGoToxx
Selection.AutoFilter=True
xx:
EndSub
汇总问题
本人有以下一个问题需要解决，请诸位大虾指点迷津。
如下表：
A B C
1 50 采购 （采购汇总）/ 2 60 工程 （工程汇总）/ 3 80 工资 （工资汇总）/ 4 100 税 （税汇总）/ 5 70 采购 / 6 50
升级会员 