execl操作疑难问题大全Word文档下载推荐.docx
《execl操作疑难问题大全Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《execl操作疑难问题大全Word文档下载推荐.docx（15页珍藏版）》请在冰点文库上搜索。
F6区域为总分
K1={INDEX（班级,MATCH（LARGE（总分+1-ROW（总分）/100,ROW（$A1））,总分+1-ROW（总分）/100,0））}
L1={INDEX（姓名,MATCH（LARGE（总分+1-ROW（总分）/100,ROW（$A1））,总分+1-ROW（总分）/100,0））}
M1={INDEX（总分,MATCH（LARGE（总分+1-ROW（总分）/100,ROW（$A1））,总分+1-ROW（总分）/100,0））}
注：
只要向下复制，便可得到1~6名的情况了。
该公式可以无限排列。
另注：
这是数组公式，不需要输入“{}”，在输完公式后，请按ctrl+shift+enter，应将后面的100改成100000，比较好。
统计满足条件的数据并生成新的数据列
ABC
4278427/6128612/9248924/2216409/9416/62016/95516/4098/请问：
假定有如上两列数据A和B，现在想要统计满足条件B＝8的，并且在C列自动生成数据，我不懂公式怎么写，我知道筛选能够做到，但是由于数据量比较大，想做一个模板，免去一些重复劳动。
=IF（ROW（$A1）>
COUNTIF（$B$1:
$B$8,8）,"
"
INDEX（$A$1:
$A$8,MATCH（SMALL（IF（$B$1:
$B$8=8,0,1）+ROW（$B$1:
$B$8）/100000,ROW
（$A1））,IF（$B$1:
$B$8）/100000,0）））
争议：
B栏有100个选取条件，公式要修改100次，有甚么灵活性?
给字段名称，插入头两列，B1=SUBTOTAL（9,B4:
B65536）。
解决掉所有问题。
控件找自动筛选要
辩解：
不需要修改公式，直接复制就行了。
如果是100个，开始时就将范围修改好就行了。
工作表间的查找与复制
我的工作是在一个2000多个员工的纪录（包括各种资料，如身份证号，工作记录等）的excel工作表A中查找另一份200多个员工名单，把员工在A表中的各项纪录复制到另一个表上去，我听说用vba可以很容易的简化大量的复制工作，不知哪位高手可以指点指点？
？
如下表sheet1的内容
abc
姓名工号部门
张三100mm/李四101mm2/.......
表sheet2
姓名部门/李四/。
。
要在sheet2表中将sheet1表的相同人员资料复制过来可在b表单元格b2中输入
”=vlookup（a2,sheet1!
a1:
gg2000,3,false）
最好将sheet1的数据区先定义一下
比较运算符为何要用引号
（1）何解在公式内，运用"
比较运算符号"
时，要把数据连同比较运算符号用"
双引号"
括住，而其它算术符号就不需要？
例如：
=Countif（A1:
A30,"
>
=10"
）是可以接受，但不加双引号时，便遭拒绝。
（2）又请问：
如上例，要比较的数值是要参照某单元格时，公式应怎样做？
这是CountIF（）的规定。
第二个参数为字符串，必须用双引号扩起来，除非条件是等于一个数值。
比如：
=COUNTIF（A1:
=4"
）
condition"
=COUNTIF（A1:
A30,220）
用引用作参数（假设B1值为4）：
="
&
B1）
ＥＸＣＥＬ中如何修改批注的字体和颜色
视图－－批注然后在批注上单击右键选择设置批注格式
如何以进阶筛选命令筛选出“序号”为单数、双数、或者是任意间距值的records序号姓名性别年龄
1李1男30/2李2女25/3李3女22/4李4男31/：
：
/99李99男28
在“序列”与“姓名”之间插入一个空列，在其第一格输入“条件”。
在“条件”下面输入公式“=IF（MOD（A5,2）=1,"
单数"
"
双数"
）”，用“自动筛选”。
n2=sum（b2到m2）、n3=sum（b3到m3）、有没有办法让n那一列可以自动填充
就是说每增加一行，就会自动计算n那一列的值、多谢！
1：
我有个笨方法，须用VBA，不知和你意否。
PrivateSubWorksheet_Change（ByValTargetAsExcel.Range）
IfTarget.Column<
14AndCells（Target.Row,14）.Formula="
ThenCells（Target.Row,14）.Formula="
=SUM（RC[-12]:
RC[-1]）"
EndSub
2：
假设需要被SUM的数据存放在B列（第一个数据存放在单元格B2）,将以下公式COPY到你希望显示结果的单元格中:
=IF（LEN（B2）>
0,SUM（B$2:
INDEX（B:
B,ROW（）,1））,"
切记如果B列中有空单元格隔开了需要计算的数据,必须以零填充该单元格.
3：
这个问题不用想得很复杂，就是要把n2单元格输入公式后，拖曳向下复制公式。
在工作表里有连续10行数据,现在要每行间格2行,要如何做
如sheet1！
$A$1:
$D$10中有连续10行资料，在sheet2中把sheet1中的数据每行间隔2行
sheet2！
A1中公式可用
=IF（ROW（）=1,Sheet1!
A1,IF（MOD（ROW（）,3）=1,INDEX（Sheet1!
$D$10,INT（（ROW（）-1）/2）+1,COLUMN（））,"
））
然后填充公式（注意公式在SHEET2中的填充范围，超过范围会出错！
）
小修改
=IF（MOD（ROW（）,3）=1,INDEX（Sheet1!
$Z$500,INT（ROW（）/3）+1,COLUMN（））,"
当点到某单元格时单元格会出现一个下拉的列框，可以选取其中的数据
[数据]-[有效性]-[序列]
选取数据只能在本页面，有无高招选取另外页面的数据源，甚至是另外工作薄的？
先在“插入-》名称”中定义好其它页面上需要引用的数据源的名称（AA）。
然后[数据]-[有效性]-[序列]，在“数据源”栏输入“=AA”。
或：
若改其它活页薄要如何修改下列式子?
（假设活页薄名称为book1）
=Sheet1!
$A$2:
$A$14（book2）
查找与引用
有一个表格：
承兑行天数实际天数
深圳工行98101
广州交行105108
鞍山工行100100
如果“承兑行”一栏含有鞍山字样，则“实际天数”等于“天数”；
如果“承兑行”一栏不含有鞍山字样，则“实际天数”等于“天数”加上3，请问：
如何设置？
这个问题的重点在于如何判断是否含有“鞍山”字样。
如果“鞍山”在字符串中的位置是固定的，则很容易用MID函数找到并进行判断；
如果不是，则需要用FIND或Search函数来找到再判断。
如果A列中是承兑行，B列是天数，C是....
可以试用一下以下的公式：
=IF（ISERROR（FIND（"
鞍山"
A2））,B2+3,B2）
如果D2>
20那E2就显示$200、如果D2>
30那E2就显示$300依此类推
=INT（D2/10）*100，当然，你的单元格格式设置成$格式就可以了。
否则用，="
$"
&
INT（D2/10）*100
则该单元格成字符型。
当然，你也可以用IF函数，但它有7层的限制。
if（D2>
30,"
300"
IF（D2>
20,"
200"
））
在两个工作表之间查找相同的内容并复制到新表
有两张工作表，内容都是电话号码、用户名称，怎样对两张工作表进行比较？
（把第一张工作表和第二张工作表中电话号码相同的项目复制到新工作表中或加上标记）
先备份工作簿。
假设SHEET1、SHEET2两张表的结构相同，A列为用户名，数据由第二行开始。
新建SHEET3并在其A2单元格中输入=IF（ISNA（MATCH（Sheet2!
$A2,Sheet1!
$A:
$A,0））,"
DELETTHISROW"
Sheet2!
A2）并向下、向右填充。
完成后SHEET3中多了SHEET2和SHEET1共有的用户名，以及由"
DELETTHISROW"
填充的数据行。
剩下的就简单了！
统计偶数单元格合计数值
统计F4到F62的偶数单元格合计数值。
公式一｛=SUM（IF（MOD（ROW（F4:
F62）,2）=0,F4:
F62））｝
公式二（要有安装'
分析工具箱'
）｛=SUM（IF（ISEVEN（ROW（F4:
F62））,F4:
F62））｝
按表1中A列的编号提取表1中的相应数据并自动录入到表2中的对应的相关列
有两个工作表，工作表1中A列为编号，其它列为相关数据，若在工作表2中的编号列单元格中输入编号时，如何才能按表1中A列的编号提取表1中的相应数据，自动录入到表2中的对应的相关列。
利用vlookup寒暑，该函数的用处就是把一个数据区域当成数据库，并利用条件检索相关纪录。
有了这点认识就非常简便了！
在表2的单元格中输入下面的公式：
=vlookup（编号，表1！
有关数据区域，第n列，false）
解释：
1、编号——不需要输入，主要是编号的相对引用。
编号在b2,公式在c2,则编号为：
b2
2、有关数据区域——必须是绝对引用，也就是数据区的行列要用$符号修饰。
从a1到h50是数据，则应该写为：
$a$1:
$h$50
3、第n列——也就是你准备返回第几列的值。
你输入编号后，要得到姓名，而姓名在数据区域的第5列，n就是5。
4、false——此处取值有两种，一是true,一是false。
两者的区别是true为相似匹配，false为精确匹配。
用EXCEL做统计
如：
A1，B1单元格是时间类型，C1是数字类型，我要计算费用用（B1-A1）*C1得到的数据还是时间类型、怎么办？
我希望把时间类型变为整数类型，如0：
50（50分钟）*3（元/H）=2.5元
设A1为3：
30，B1为4：
30，C1为3，D1为下列输入的函数：
=HOUR（B1）*60+MINUTE（B1）-（HOUR（A1）*60+MINUTE（A1））。
D1结果等于60（分钟）
单元格是否有注释
我在用VBA写EXCEL的注释时，需要等程序判断该单元格是否有注释，如果有就读出来，如果没有就添加，但我一直没找到用什么办法来知道单元格是否有注释，请各位大虾指点！
！
Sub批注（）
Fori=1To8
OnErrorResumeNext
a=Sheets
（1）.Cells（i,1）.Comment.Text
IfErr.Number=91Then
Sheets
（1）.Cells（i,2）="
左侧单元格无批注"
Sheets
（1）.Cells（i,1）.AddCommentText:
请输入批注内容"
Else
左侧单元格批注"
a
EndIf
Nexti
根据A1的内容,决定A2的数值是来自sheet1,sheet2还是sheet3.
if（a1=1,sheet1!
a1,if（a1=2,sheet2!
a1,if（a1=3,sheet3!
a1）））
能不能做到让符合条件的单元格所在的列自动隐藏
比如说第一行的第一个单元格=1那么就自动隐藏第一行？
解答用vba。
Sub隐藏（）
fori=1toxx--行号
ifsheets
（1）.cells（i,1）=1then
Rows（i）.Select
Selection.EntireRow.Hidden=True
endif
nexti
直接输入一组数如“20020213101235”后，自动转换成日期格式
A1中输入，B1中转换。
B1=Left（A1,4）&
—"
MID（A1,5,2）&
MID（A1,7,2）&
"
MID（A1,9,2）&
:
MID（A1,11,2）&
MID（A1,13,2）
把sheet1到sheet200的a19这一格，依序贴到sheet0的a1到a200
方法一：
公式
可在A1储存格输入以下公式，再行拖曳至A200即可。
=INDIRECT（"
Sheet"
ROW（）&
!
A19"
方法二：
VBA
SubMacro1（）
'
选择工作表sheet0
Sheets（"
sheet0"
）.Select
Forr=1To200
将工作表1~200里面的D17复制到sheet0的A1~A200
Cells（r,1）=Worksheets（CStr（r））.Range（"
D17"
Nextr
A列记录几百条，如何对这列计数（重复的数值不计）我只能做到新建一列，B列，然后第一个单元格countif（$A$1:
$A$100,A1），然后拖动到全部新列。
最后在新列下面用sumif（B1:
B100,1）谁有更好地方法？
1、试试这个：
{=SUM（IF（COUNTIF（A1:
A100,A1:
A100）=1,1,0））}
2、操作：
①A1作公式栏，A2作字段名栏，如原该两栏有数插入2行。
在A1输入：
=SUBTOTAL（3,A$2:
A$5000）统计记录数或：
=SUBTOTAL（9,A$2:
A$5000）数据汇总
②选：
数据-->
筛选-->
高级筛选-->
选择不重复的记录。
③复原选：
全部显示。
3、试试这个：
{=SUM（IF（$A$1:
$A$100="
1/（COUNTIF（$A$1:
$A$100,$A$1:
$A$100））））}
4、请解释一下,因为我单独使用COUNTIF（$A$1:
$A$100）数组公式时,它仅仅计算第一个也就是A1的个数.
5、我发觉你的这办法，只对唯一的数据进行了计数，而重复的数据全部未计入（是不是应该将重复的数据也计上一个？
）打哈欠的“{=SUM（IF（COUNTIF（A1:
A100）=1,1,0））}”也是这样。
TO剑魔版主你公式中的“1/（COUNTIF（$A$1:
$A$100））”像是一个倒数，怎么理解？
6、用倒数是这个意思:
如果只出现一次，数组中的相应项统计为1，其倒数为1，Sum统计计1
如果出现N次，其倒数为1/N，出现了N次，求和就是Nx1/N，最后Sum统计就只计1。
如果有文本串"
YY0115"
，我想取第三、四的值"
01"
，应该用什么函数
1、=mid（"
3,2）&
2、如果你的A1中的数值一定包含后四位阿拉伯数字的话，你可以用这个：
=LEFT（RIGHT（A1,4）,2）=LEFT（RIGHT（A1,4）,2）=LEFT（RIGHT（A1,4）,2）
3、我的实际工作中的数据还没有如此规律，该怎么办？
A1="
YY0105"
，A2="
99065"
，A1取"
，A2取"
99"
能否用一个函数去掉A1中"
YY"
，然后都是从阿拉伯数字的第一位开始取两位数？
4、如果阿拉伯数字数量不定，但是以2个英文字母开头（或无英文字母），可以用以下公式：
（设数据在A1中）=IF（ISNUMBER（VALUE（A1））,MID（A1,1,2）,MID（A1,3,2））
5、数组公式：
{=MID（A1,MIN（IF（EXACT（LOWER（MID（A1,ROW（INDIRECT（"
A1:
A256"
））,ROW（INDIRECT（"
A2:
A257"
））））,UPPER（MID（A1,ROW（INDIRECT（"
）））））,ROW（INDIRECT（"
））,"
））,2）}
怎样将文字和数字分2列显示
1、用函数可以解决。
假如A1为中行41785015110010091252
则B1=LEFT（A1,（SEARCHB（"
?
A1,1）-1）/2）、C1=MID（A1,LEN（B1）+1,50）、随后将B1、C1的公式往下复制。
2、Sub分列（）
ForEachjkInSheet1.UsedRange.Columns
（1）.Cells
Fori=1ToLen（jk.Formula）
IfAbs（Asc（Mid（jk.Formula,i,1）））<
256Then
Sheet1.Cells（jk.Row,（jk.Column+1））.Formula="
Right（jk.Formula,（Len（jk.Formula）-i+1））
jk.Formula=Left（jk.Formula,（i-1））
ExitFor
EndIf
Nexti
Next
3、是否将Asc（Mid（jk.Formula,i,1））<
256改成47<
Asc（Mid（jk.Formula,i,1））<
58会更好，因为它只将数字抽出，如果数字前有英文字的话将抽往数字列。
4、我看帮助中instr函数不错
配合循环速度应快些
500个不连续的数之和的求法
我有一个表格，有一列数据是以6个单元格为一个单位做一次小计，我在小计中加入了公式
但是我还想求一下这个小计的总合，我想加入一个公式，但是我有SUM到了50多个时就加不进去了，用加号连加也不行，不知这个问题怎么解决
用SubTotal（）解决即可
如何使输入的英文单词第一个字母变成大写
PrivateSubWorksheet_Change（ByValTargetAsRange）
Target.Value=Application.WorksheetFunction.Proper（Target.Text）
在一个表中有两列日期型数字请问如何在第三列中得到其差（两日期间的天数）解答：
=DATEDIF（A1,B1,"
d"
用datedif发现一个问题、即当A列<
B列时,结果正确,而当A列>
B列时出错了、请问用什么办法解决
答：
=IF（A1>
B1,DATEDIF（B1,A1,"
）,DATEDIF（A1,B1,"
重要的EXCEL文件坏了（文件带密码）有没有EXCEL修复工具
Excel2000
数据>
取得外部数据>
新增数据库查询>
ExcelFile*>
找到档案>
[选项]勾选所有选项>
会找到所有未命名[区块]如Sheet1$
找到字段>
其它跟着查询精灵导引一步一步作
此中间层组件叫作MicrosoftQuery
可以用来拯救[毁损档案]
只要求简单的把数据从分表直接追加到总表
你可以用Lookup,vlookup等函数。
A1中输入11:
22:
22要求每间隔一秒刷新一次
submytime
range（"
a1"
）=now（）
Application.OnTimeNow+TimeValue（"
00:
）,"
mytime"
endsub
怎样用函数来显示某月的最后一天是几号
=DAY（DATE（年份,月份+1,1）-1）
如何用excel求解联立方程
x-x（7/y）^z=68
x-x（20/y）^z=61
x-x（30/y）^z=38
这是一个指数函数的联列方程。
步骤如下
1、令X/Y=W则有
X-（7W）^z=68
X-（20W）^Z=61
X-（30W）^Z=38
2、消去X
（20^Z-7^Z）W^Z=7
（30^Z-20^Z）W^Z=23
3
升级会员 