excel常见应用.docx
《excel常见应用.docx》由会员分享，可在线阅读，更多相关《excel常见应用.docx（15页珍藏版）》请在冰豆网上搜索。
excel常见应用
（Excel）常用函数公式及操作技巧之九：
查询和查找引用
——通过知识共享树立个人品牌。
查找顺序公式
1
2
3
=LOOKUP（2,1/（A1:
A20<>0）,A1:
A20）
=MATCH（7,A1:
A20）
=VLOOKUP（7,A1:
B11,2）
怎样实现精确查询
用VLOOKUP
1
=VLOOKUP（B11,B3:
F7,4,FALSE）
用LOOKUP
1
=LOOKUP（B11,B3:
B7,E3:
E7）
用MATCH+INDEX
1
=INDEX（E3:
E7,MATCH（B11,B3:
B7,0））
用INDIRECT+MATCH
1
=INDIRECT（"E"&MATCH（B11,B3:
B7,0）+2）
用OFFSET+MATCH
1
=OFFSET（E3,MATCH（B11,B3:
B7,0）-1,0）
用INDIRECT+ADDRESS+MATCH
1
=INDIRECT（ADDRESS（MATCH（B11,B4:
B7,0）+3,5））
用数组公式
1
=INDEX（E1:
E7,MAX（IF（（B4:
B7=B11）,ROW（B4:
B7）,0）））
查找及引用
如何查找并引用B2单元格中所显示日期当日的相应代码的值。
1
B3=IF（COUNTIF（$E$3:
$E$20,A3）,VLOOKUP（$A3,$E$2:
$M$20,MATCH（B$2,$F$2:
$M$2,）+1,）,""）
查找函数的应用
我想在A5输入表的名称，B5自动跳出该表中B列的最后一个有效数值，请问B5的公式该如何设定？
1
2
3
=LOOKUP（9E+307,INDIRECT（A5&"!
"&"B:
B"））
B2=IF（A2="","",LOOKUP（9E+307,INDIRECT（A2&"!
B:
B"）））
怎么能方便的判断某个单元格中包含多少个指定的字符？
例：
A1 中是“ASAFAG”，我希望计算出A1里面有多少个“A”......
1
=LEN（A1）-LEN（SUBSTITUTE（A1,"A",""））
如何用查找函数
一、要求:
利用公式从左表中查询相应的地区，结果放在H14单元格
1
2
3
=VLOOKUP（G14,IF（{1,0},D14:
D18,C14:
C18）,2,）
h14=OFFSET（C14,MATCH（G14,D14:
D18,0）-1,,,）
H14=INDIRECT（"c"&MATCH（G14,D:
D,））
二、要求:
根据C25单元格的商品名称，查找该商品的最新单价，即该商品最后一条记录的单价（结果放在D25单元格）。
用数组公式：
1
2
3
=INDIRECT（"G"&MAX（（D14:
D22=C25）*ROW（D14:
D22）））
D25=LOOKUP（2,1/（D14:
D22=C25）,G14:
G22）
日期查找的问题
我有一个日期比如:
2007/02/12,我想知道它减去一个固定天数比如6后,最接近它的一个星期四（只能提前）是多少号
2007/02/12的答案应该是2007/02/01而不是2007/02/08
日期在A1处，B1处输入：
1
=MAX（（WEEKDAY（A1-6-{1,2,3,4,5,6,7},2）=4）*（A1-6-{1,2,3,4,5,6,7}））
A1 =2007/02/12
B1, 输入公式 :
1
=A1-6-MOD（WEEKDAY（A1-6,2）+3,7）
如何自动查找相同单元格内容
1
2
=SUMPRODUCT（（$D$2:
$D$15=A21）*（$E$2:
$E$15））
=IF（ISERROR（VLOOKUP（A6,$D$2:
$E$15,2,0））,0,VLOOKUP（A6,$D$2:
$E$15,2,0））
查找函数
1
2
3
D3=LOOKUP（2,1/（（$G$3:
$G$14=B3）*（$H$3:
$H$14=C3））,$I$3:
$I$14）
=IF（ISERROR（VLOOKUP（A14,A:
B:
D:
F,2,FALSE））,"",VLOOKUP（A14,A:
B:
D:
F,2,FALSE））
=IF（ISERROR（VLOOKUP（C2,k!
B2:
Z2189,2,FALSE））,"",VLOOKUP（C2,k!
B2:
Z2189,2,FALSE））
怎样对号入座（查找）
1
2
3
4
5
6
=VLOOKUP（D2,$A$1:
$B$5,2,FALSE）
=INDEX（$B$2:
$B$5,MATCH（D2,$A$2:
$A$5,0））
=OFFSET（$A$1,MATCH（D2,$A$2:
$A$5,0）,1）
=VLOOKUP（D2,$A$1:
$B$16,2,）
=VLOOKUP（D2,IF（{1,0},$A$1:
$A$9,$B$1:
$B$9）,2,）
=LOOKUP（2,1/（$A$1:
$A$10=D2）,$B$1:
$B$10）
一个文本查找的问题
如何在一个单元格中,统计某个字符出现的次数,例如:
单元格A1中填有:
张三/李四/王五",如何通过公式来计算此单元格中共填有几个人姓名,每个人姓名之间用"/"符号分开,烦请相告.
1
=LEN（A1）-LEN（SUBSTITUTE（A1,"/",））+1
查找一列中最后一个数值
我想用公式知道,另一个表中"A"列最下面一个数是多少,就行了.用不定值的,因为还有数据有增加,
1
2
=LOOKUP（9E+307,Sheet2!
A:
A）——最后一个数值
=LOOKUP（REPT（"座",255）,Sheet2!
A:
A）——最后一个文本
或
1
2
3
=INDEX（Sheet2!
A:
A,MATCH（9E+307,Sheet2!
A:
A））
=INDEX（Sheet2!
A:
A,MATCH（"*",Sheet2!
A:
A,-1））
＝Match（rept（"座",255）,sheet2!
A:
A）
查找重复字符
两组数值
A B
1245689 0134578
查找单元格Ａ和Ｂ里重复及不重复的字符
正确答案：
重复字符－1458
不重复字符－023679
以下公式对数字有效：
重复数字：
1
=IF（COUNT（FIND（0,A1:
B1））=2,0,""）&SUBSTITUTE（SUM（IF（ISNUMBER（FIND（ROW（$1:
$9）,A1））+ISNUMBER（FIND（ROW（$1:
$9）,B1））=2,ROW（$1:
$9）*10^（10-ROW（$1:
$9））））,0,）
不重复数字：
1
=IF（COUNT（FIND（0,A1:
B1））=1,0,""）&SUBSTITUTE（SUM（IF（ISNUMBER（FIND（ROW（$1:
$9）,A1））+ISNUMBER（FIND（ROW（$1:
$9）,B1））=1,ROW（$1:
$9）*10^（10-ROW（$1:
$9））））,0,）
都是数组公式，按Ctrl+shift+enter结束。
重复数字：
1
=IF（COUNT（FIND（0,A1:
B1））=2,0,""）&SUBSTITUTE（SUM（IF（MMULT（COUNTIF（OFFSET（A1,,{0,1},）,"*"&ROW（$1:
$9）&"*"）,{1;1}）>1,ROW（$1:
$9）*10^（9-ROW（$1:
$9））））,0,）
不重复数字：
1
=IF（COUNT（FIND（0,A1:
B1））=1,0,""）&SUBSTITUTE（SUM（IF（MMULT（COUNTIF（OFFSET（A1,,{0,1},）,"*"&ROW（$1:
$9）&"*"）,{1;1}）<2,ROW（$1:
$9）*10^（9-ROW（$1:
$9））））,0,）
请教查找替换问题
把表1中字符在4个以上的字段（含4个）查找出来,替换成表2中的人名,最好在原位置修改,或者在新的一列上生成也成,只要其他内容保持不变并按原来的顺序即可。
1
2
=IF（LEN（A2）<4,A2,OFFSET（表2!
$A$1,SUMPRODUCT（--（LEN（$A$2:
A2）>3））-1,））
=IF（LEN（A2）<4,A2,INDEX（表2!
A:
A,COUNTIF（$A$2:
A2,"="&"?
?
?
?
*"）））
IF函数替换法总结
条件说明：
小于10返回500，小于20返回800，小于30返回1100，小于40返回1400，大于40返回1700
类似于以上要求，大家最先想到IF函数，这也本属IF专长。
但用IF一般要长长的公式，且计算较慢。
现总结一下IF之替换公式，望能抛砖引玉，在我的倡导下各位提供更完善的方案。
其中部分公式通用，部分公式有局限性，请看说明。
（前18个条件公式，根据速度，排名如下）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1=SMALL（{500;800;1100;1400;1700},COUNTIF（$A$9:
$A$13,"<="&A1））
2=INDEX（{500;800;1100;1400;1700},COUNTIF（$A$9:
$A$13,"<="&A1））
3=CHOOSE（COUNTIF（$A$9:
$A$13,"<="&A1）,500,800,1100,1400,1700）
4=LOOKUP（A1,{0,10,20,30,40},{500,800,1100,1400,1700}）
5=MIN（4,INT（A1/10））*300+500
6=MATCH（A1,{0,10,20,30,40}）*300+200
7=MIN（40,FLOOR（A1,10））*30+500
8=HLOOKUP（A1,{0,10,20,30,40;500,800,1100,1400,1700},2,1）
9=200+SUM（（A1>={0;10;20;30;40}）*300）
10=FREQUENCY（{0,10,20,30,40},A1）*300+200
11=MAX（（A1>={0,10,20,30,40}）*{500,800,1100,1400,1700}）
12=INDEX（{500;800;1100;1400;1700},MATCH（A1,{0;10;20;30;40},1））
13=CHOOSE（MATCH（A1,{0;10;20;30;40},1）,500,800,1100,1400,1700）
14=500+SUM（IF（A1>={10,20,30,40},{300,300,300,300}））
15=IF（A1<10,500,IF（A1<20,800,IF（A1<30,1100,IF（A1<40,1400,1700））））
16=CHOOSE（SUM（（A1>={0;10;20;30;40}）*1）,500,800,1100,1400,1700）
17=MAX（（INT（A1/（{10;20;30;40}））>0）*（ROW（$1:
$4）*300））+500
18=CHOOSE（MIN（INT（A1/（ROW（$1:
$4）*10））+1,5）,500,800,1100,1400,1700）
新增公式：
1
2
3
4
5
6
7
19=CHOOSE（MIN（INT（A1/（ROW（$1:
$4）*10））+1,5）,500,800,1100,1400,1700）
20{=MAX（（INT（A1/（ROW（$1:
$4）*10））>0）*（ROW（$1:
$4）*300））+500}
21=500+MIN（4,MAX（0,INT（A1/10）））*300
22MAX（（A1>={0,10,20,30,40}）*{500,800,1100,1400,1700}）
23=MATCH（A1,{0,10,20,30,40}）*300+200
24=MIN（40,FLOOR（A1,10））*30+500
25=FREQUENCY（ROW（$1:
$5）*10-10,A1）*300+200
查找的函数（查找末位词组）
（数组公式：
）
1
2
=REPLACE（A2,1,MAX（IF（MID（A2,ROW（$1:
$100）,1）="",ROW（$1:
$100）））,）
=REPLACE（A2,1,LOOKUP（1,0/（MID（""&A2,ROW（$1:
$100）,1）=""）,ROW（$1:
$100））-1,）
（数组公式：
）
1
2
=RIGHT（A2,MATCH（1,FIND（"",RIGHT（""&A2,ROW（$1:
$100）））,）-1）
=TRIM（RIGHT（SUBSTITUTE（A2,"",REPT（"",50））,50）） （好）
其实这个公式的思路, 是可以变化的，改变REPT（）中的数值, 可以返回, 指定空格位置後的数据，比如：
A1 =一 二 三 四 五 六 七 八 九
10个普通公式, 分别为 :
（Excel）常用函数公式及操作技巧之九：
（Excel）常用函数公式及操作技巧之九：
怎样从原始数据中自动获取最后一个数据
原始数据
a 12
b 1221
c 12
d 33
a 33 自动获取
a 432 a 432
b 33 b 33
c 22 c 44
c 44 d 23
d 23
公式
1
=LOOKUP（1,0/（$A$1:
$A$100=C2）,$B$1:
$B$100）Sheet4!
A$2:
A$100=A5的意思是：
如果A2:
A100这个区域中的数等于A5,那么返回1，否则如果不等于A5，就返回错误
所以Sheet4!
A$2:
A$100=A5结果是一个由1和0组成的数组,比如{1,1,0，1，0.....}
同理：
Sheet4!
B$2:
B$100=B5也是得到了一个由1和0组成的数组
两个数组相乘得到了一个新数组，当然还是由1和0组成的
所以0/{1，0，1，0，1，1，0....}就得到了一个由0和错误组成的新数组，这个很好理解，
0/1=0,而0/0=错误
这样=lookup（1,{0，错误，0，错误，0，0，错误....}，Sheet4!
C$2:
C$100）
的意思就是返回Sheet4!
C$2:
C$100）这个区域中对应的{0，错误，0，错误，0，0，
错误....}这个数组最接近于1的那个数，当然就是0了，如果有很多0，LOOKUP返
回的是最后一个0对应的位置。
相信你和我一样，最难理解的部分是0/（（Sheet4!
A$2:
A$100=A5）*（Sheet4!
B$2:
B$100=
B5））,
至于LOOKUP函数的用法，XX一下就很清楚了。
两列数据查找相同值对应的位置
1
=MATCH（B1,A:
A，0）回符合特定值特定顺序的项在数组中的相对位置
查找数据公式两个（基本查找函数为VLOOKUP,MATCH）
（1）、根据符合行列两个条件查找对应结果
1
=VLOOKUP（H1，A1：
E7，MATCH（I1，A1：
E1，0），FALSE）
（2）、根据符合两列数据查找对应结果（为数组公式）
1
=INDEX（C1：
C7，MATCH（H1&I1,A1:
A7&B1:
B7,0））
2.=LOOKUP（A1&B1,Sheet1!
$A$1:
$A$10&Sheet1!
$B$1:
$B$10,Sheet1!
$C$1:
$C$10）
常用办公软件 excel技巧的整理、讲解，在这里给读者们看一看，给大家一些提示，希望在你在平时能用得上。
1、两列数据查找相同值对应的位置
=MATCH（B1，A：
A，0）
2、已知公式得结果
定义名称=EVALUATE（Sheet1!
C1）
已知结果得公式
定义名称=GET.CELL（6，Sheet1!
C1）
3、强制换行
用Alt+Enter
4、超过15位数字输入
这个问题问的人太多了，也收起来吧。
一、单元格设置为文本;二、在输入数字前先输入'
5、如果隐藏了B列，如果让它显示出来?
选中A到C列，点击右键，取消隐藏
选中A到C列，双击选中任一列宽线或改变任一列宽
将鼠标移到到AC列之间，等鼠标变为双竖线时拖动之。
6、excel中行列互换
复制，选择性粘贴，选中转置，确定即可
7、excel是怎么加密的
（1）、保存时可以的另存为>>右上角的"工具">>常规>>设置
（2）、工具>>选项>>安全性
8、关于COUNTIF
COUNTIF函数只能有一个条件，如大于90，为=COUNTIF（A1：
A10，">=90"）
介于80与90之间需用减，为=COUNTIF（A1：
A10，">80"）-COUNTIF（A1：
A10，">90"）
9、根据身份证号提取出生日期
（1）、=IF（LEN（A1）=18，DATE（MID（A1，7，4），MID（A1，11，2），MID（A1，13，2）），IF（LEN（A1）=15，DATE（MID（A1，7，2），MID（A1，9，2），MID（A1，11，2）），"错误身份证号"））
（2）、=TEXT（MID（A2，7，6+（LEN（A2）=18）*2），"#-00-00"）*1
10、想在SHEET2中完全引用SHEET1输入的数据
工作组，按住Shift或Ctrl键，同时选定Sheet1、Sheet2。
11、一列中不输入重复数字
[数据]--[有效性]--[自定义]--[公式]
输入=COUNTIF（A：
A，A1）=1
如果要查找重复输入的数字
条件格式》公式》=COUNTIF（A：
A，A5）>1》格式选红色
12、直接打开一个电子表格文件的时候打不开
“文件夹选项”-“文件类型”中找到.XLS文件，并在“高级”中确认是否有参数1%，如果没有，请手工加上
13、excel下拉菜单的实现
[数据]-[有效性]-[序列]
14、10列数据合计成一列
=SUM（OFFSET（$A，（ROW（）-2）*10+1，，10，1））
15、查找数据公式两个（基本查找函数为VLOOKUP，MATCH）
（1）、根据符合行列两个条件查找对应结果
=VLOOKUP（H1，A1：
E7，MATCH（I1，A1：
E1，0），FALSE）
（2）、根据符合两列数据查找对应结果（为数组公式）
=INDEX（C1：
C7，MATCH（H1&I1，A1：
A7&B1：
B7，0））
16、如何隐藏单元格中的0
单元格格式自定义0;-0;;@或选项》视图》零值去勾。
呵呵，如果用公式就要看情况了。
17、多个工作表的单元格合并计算
=Sheet1!
D4+Sheet2!
D4+Sheet3!
D4，更好的=SUM（Sheet1：
Sheet3!
D4）
18、获得工作表名称
（1）、定义名称：
Name
=GET.DOCUMENT（88）
（2）、定义名称：
Path
=GET.DOCUMENT
（2）
（3）、在A1中输入=CELL（"filename"）得到路径级文件名
在需要得到文件名的单元格输入
=MID（A1，FIND（"*"，SUBSTITUTE（A1，""，"*"，LEN（A1）-LEN（SUBSTITUTE（A1，""，""））））+1，LEN（A1））
（4）、自定义函数
PublicFunctionname（）
DimfilenameAsString
filename=ActiveWorkbook.name
name=filename
EndFunction
19、如何获取一个月的最大天数
：
"=DAY（DATE（2002，3，1）-1）"或"=DAY（B1-1）"，B1为"2001-03-01
数据区包含某一字符的项的总和，该用什么公式
=sumif（a：
a，"*"&"某一字符"&"*"，数据区）
最后一行为文本：
=offset（$b，MATCH（CHAR（65535），b：
b）-1，）
最后一行为数字：
=offset（$b，MATCH（9.9999E+307，b：
b）-1，）
或者：
=lookup（2，1/（b1：
b1000<>""），b1：
b1000）
评委打分中，如何去掉两个以上最高分，两个以上最底分，求剩余人员的平均分?
同时显示出被去掉的分数。
看看trimmean（）函数帮助。
被去掉的分数：
最大两个：
=large（data，）
最小两个：
=small（data，）
怎样很简单的判断最后一位是字母
right（a1）*1
出错的字母
=IF（ISNUMBER（--RIGHT（A1，1）），"数字"，"字母"）
=IF（ISERR（RIGHT（A1）*1），"字母"，"数字"）
如何设置单元格，令其不接受包含空格的字符
选定A列
数据——有效性——自定义——公式
=iserror（find（""，a1））
数据--有效性--自定义--公式
=len（a1）=len（trim（a1））
原来的函数是=a1+a4+a7+a10+a13+a16+a19+a22……
现在是=sum（n（offset（a1，（row（1：
10）-1）*3，）））
在一个工作表中引用其他工作表中的数据，但是被引用的工作表不是固定的，根据我输入的工作表名自动选择相应的工作表中的数据，请问在公式里怎样引用?
=INDIRECT（"A1"&"!
"&"E1"）A1为工作表名
奇数行求和=SUMPRODUCT（（A1：
A1000）*MOD（ROW（A1：
A1000），2））
偶数行求和=SUMPRODUCT（（A
升级会员 