excel宝典文档格式.docx
《excel宝典文档格式.docx》由会员分享，可在线阅读，更多相关《excel宝典文档格式.docx（100页珍藏版）》请在冰点文库上搜索。
输入数组公式：
=SUM（（$A$1:
$A$10<
"
）*（$B$1:
$B$10<
）*（$A$1:
$A$10>
$B$1:
$B$10））
$A$10+$B$1:
工作表中的小写字母都变成大写
请运行以下程序：
（测试通过）
SubConvertToUpperCase（）
DimRngAsRange
Worksheets（"
Sheet1"
）.UsedRange.Select
ForEachRngInSelection.Cells
IfRng.HasFormula=FalseThen
Rng.value=UCase（Rng.value）
EndIf
NextRng
用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）即可。
如果怕繁的话，可在选项里设置按显示计算，呵呵，试试看。
工作表引用
比如说现在Sheet2!
A1引用Sheet1!
A1,Sheet3!
A1引用Sheet2!
A1，那么有什么办法可以表示Sheet（n）!
A1引用Sheet（n-1）!
A1。
用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!
C,3）+VLOOKUP（D2,Sheet2!
C,3）+VLOOKUP（E2,Sheet2!
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工作表，建立如下表格所示的表格：
A
B
C
D
E
F
G
H
I
J
K
L
M
姓名
性别
生日
年龄
年级
语文
数学
外语
政治
平均
工资
奖励
总额
王明
男
1、设置生日为11-28-1998的形式，并根据生日计算出学生的年龄。
2、对平均分>
=90分，80<
=平均分<
90，60<
80的奖励金额数放在单元格N5..N7中，请利用公式计算出每位同学赢得的奖金。
3、1年级学生工资为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函数。
将文件保存为以某一单元格中的值为文件名的宏怎么写?
假设你要以Sheet1的A1单元格中的值为文件名保存，则应用命令：
ActiveWorkbook.SaveCopyAsStr（Range（"
Sheet1!
A1"
））+"
.xls"
，A1是文本串的话，使用这条命令：
ActiveWorkbook.SaveAsRange（"
）&
"
。
当然，如果你的A1是路径，F1是文件名的话，可以这样写：
Range（"
F1"
求余数的函数
比如：
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:
$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）,"
f"
如何在一个单元格中自动输入在另外一个单元格中输入的文字
A2中输入A1的文字,则A2=indirect（"
在表格中查找我需要的东西并把该行所有的数据反映到另外的表格中去
比如找表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,"
￥"
"
））
百万：
C1=IF（A1>
=1000000,MID（RIGHTB（A1*100,9）,1,1）,IF（A1>
=100000,"
十万：
D1=IF（A1>
=100000,MID（RIGHTB（A1*100,8）,1,1）,IF（A1>
=10000,"
万：
E1=IF（A1>
=10000,MID（RIGHTB（A1*100,7）,1,1）,IF（A1>
=1000,"
千：
F1=IF（A1>
=1000,MID（RIGHTB（A1*100,6）,1,1）,IF（A1>
=100,"
百：
G1=IF（A1>
=100,MID（RIGHTB（A1*100,5）,1,1）,IF（A1>
=10,"
十：
H1=IF（A1>
=10,MID（RIGHTB（A1*100,4）,1,1）,IF（A1>
=1,"
元：
I1=IF（A1>
=1,MID（RIGHTB（A1*100,3）,1,1）,IF（A1>
=0.1,"
角：
J1=IF（A1>
=0.1,MID（RIGHTB（A1*100,2）,1,1）,IF（A1>
=0.01,"
分：
K1=IF（A1>
=0.01,RIGHTB（A1*100,1）,"
排列问题
已知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））,此为数组公式。
行改列（不是转置问题）
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
a1"
Fori=1Tonumrow'
以数据的每一行为单位进行剪切
）.Rows（i）.Cut
ActiveSheet.Paste
Selection.Offset（,numcol）.Select
If（iModnumperrow）Then'
判断是否要换行
Else:
Selection.Offset（1,-x）.Select
Nexti
'
本程序需要把要变换的数据命名为"
（数据区的列数可以任意,不一定是5）方法:
选中该区域,在名称框内输入"
即可,然后按程序要求输入每行要填的数据行的数目（如本例中的12）
如何给自动筛选中的自定义指定快捷键?
Sub自动筛选（）
OnErrorGoToxx
Selection.AutoFilter=True
xx:
汇总问题
本人有以下一个问题需要解决，请诸位大虾指点迷津。
如下表：
A B C
1 50 采购 （采购汇总）/ 2 60 工程 （工程汇总）/ 3 80 工资 （工资汇总）/ 4 100 税 （税汇总）/ 5 70 采购 / 6 50 工资 / 7 60 工程/.../请问：
C1、C2、C3、C4中的公式如何设置？
方法1：
1 50 采购 =countif（a1:
a7,"
采购"
）/ 2 60 工程 =countif（a1:
工程"
）/ 3 80 工资 =countif（a1:
工资"
）/ 4 100 税 =countif（a1:
税"
）/ 5 70 采购 / 6 50 工资 / 7 60 工程/ ...
方法2：
用如下方法才对：
1 50 采购 =SUMIF（B1:
B7,B1,A1:
A7）
2 60 工程 =SUMIF（B1:
B7,B2,A1:
A7）/ 3 80 工资 =SUMIF（B1:
B7,B3,A1:
A7）/ 4 100 税 =SUMIF（B1:
B7,B4,A1:
A7）/ 5 70 采购 / 6 50 工资 / 7 60 工程
方法3：
由于Ｂ栏属于数据表的一部分，可能会随时改变，故此最好把采购等项目名称抄到另外一个地方（Ｃ栏）。
A B C D
1 50 采购 采购 =SUMIF（B$1:
B$7,C1,A$1:
A$7）/ 2 60 工程 工程 =SUMIF（B$1:
B$7,C2,A$1:
A$7）/ 3 80 工资 工资 =SUMIF（B$1:
B$7,C3,A$1:
A$7）/ 4 100 税 税 =SUMIF（B$1:
B$7,C4,A$1:
A$7）/ 5 70 采购 / 6 50 工资 / 7 60 工程/还有，记着要适当地使用绝对参照地址（Absolutereferencing）。
方法4：
A B C D
1 50 采购 采购 =SUMIF（B:
B,C1,A:
A）/ 2 60 工程 工程 =SUMIF（B:
B,C2,A:
A）/ 3 80 工资 工资 =SUMIF（B:
B,C3,A:
A）/ 4 100 税 税 =SUMIF（B:
B,C4,A1:
A）/ 5 70 采购 / 6 50 工资 / 7 60 工
统计包含某个字符的个数
我想编的公式是：
a/[84-（b×
4）]。
其中a是一个数值，小于或等于84；
b是包含字符C的单元格的个数；
C是一个符号。
这个公式的关键是要统计出包含字符C的单元格的个数。
=a/（84-countif（b,"
=c"
）*4）
问题：
我试了一下，不能运行，我想是因为没有指定出现“c”的单元格的范围。
比如说“c”在D2—D30中随机出现，在上述公式中要先统计出出现“c”的单元格的个数。
这个公式如何做？
=a/（84-COUNTIF（D3:
D30,"
c"
如何用if函数判断,如果A1单元格大于0,B1单元格为0是错误,为非0是正确
1：
=if（and（A1<
=0,B1=0）,"
错误"
正确"
2：
=if（A1>
0,if（B1=0,"
）,"
条件一不满足"
我改进以下一个验证先进先出法公式
=IF（B3<
C1,IF（SUM（B3:
B4）<
B5）<
B6）<
B1,IF（SUM（B3:
B7）<
C1,"
outofrange"
（B3*C3+B4*C4+B5*C5+B6*C6+（C1-SUM（B3:
B6））*C7）/C1）,（B3*C3+B4*C4+B5*C5+（C1-SUM（B3:
B5））*C6）/C1）,（B3*C3+B4*C4+（C1-SUM（B3:
B4））*C5）/C1）,（B3*C3+（C1-B3）*C4）/C1）,C4）
B3至B7为输入数量,C3至C7为输入单价,C1为输入结余数量,E1为输出平均单价,即上面公式公式太复杂，而且受限制。
把A列利用起来，a3=SUM（B3:
$B$7），拖至7行（若为X行，则=SUM（B3:
$B$X，下拖），A列数据为先进先出的累计数量（待Match的结存数量），=MATCH（C1,A3:
A7,-1）找到结存批次。
然后计算结存平均单价及发出平均单价。
有无简结一点的公式求如:
a1*b1+a2*b2+b3*b3...的和
在B4中输入公式"
=SUM（A1:
A3*B1:
B3）"
按CTRL+SHIFT+ENTER结束.
工作中须将表格中大量的日期同时转换为中英文的星期几
升级会员 