excel函数公式公式如何写 a＝5 b＝15...
点击联系发帖人
时间：2011-10-04 08:24
后使用快捷导航没有帐号？
查看: 42067|回复: 20
Excel电子表格计算公式
主题帖子积分
快乐博彩 赢在乐彩
精华8注册时间
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$1,(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))
Excel电子表格计算公式使用方法25条公式技巧总结(2)时间: 11:39来源:网络 作者:不详点击:4794次
16、如何隐藏单元格中的0 单元格格式自定义0;-0;;@ 或 选项》视图》零值去勾。呵呵，如果用公式就要看情况了。 17、多个工作表的单元格合并计算 =Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4) 18
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）、自定义函数
Public Function name()
Dim filename As String
filename = ActiveWorkbook.name
name = filename
End Function
19、如何获取一个月的最大天数
:&=DAY(DATE()-1)&或&=DAY(B1-1)&,B1为&
数据区包含某一字符的项的总和,该用什么公式
=sumif(a:a,&*&&&某一字符&&&*&,数据区)
最后一行为文本：
=offset($b$1,MATCH(CHAR(65535),b:b)-1,)
最后一行为数字：
=offset($b$1,MATCH(9.,b:b)-1,)
或者：=lookup(2,1/(b1:b1000&&&&),b1:b1000)
评委打分中，如何去掉两个以上最高分，两个以上最底分，求剩余人员的平均分?同时显示出被去掉的分数。
看看trimmean()函数帮助。
被去掉的分数：
最大两个：=large(data,{1;2})
最小两个：=small(data,{1;2})
怎样很简单的判断最后一位是字母
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((A1:A1000)*NOT(MOD(ROW(A1:A1000),2)))
查看字符串字数
=LEN(A1)
求非空单元格数量
公式计算出来的数据，COUNTA不能用的(否则空字符也计算进去了)
=COUNTIF($E$3:$E$65536,&?*&)
动态求和公式,自A列A1单元格到当前行前面一行的单元格求和.
=SUM(INDIRECT(&A1:A&&ROW()-1))
20、比较好用的EXCEL文档修复工具
ExcelRecovery
21、EXCEL开方运算
将8开3次方，可以用这个公式，在单元格中输入 =8^(1/3)
22、单元格中的数据分散对齐
文本格式》全角输入
23、查找工作表中的链接
Ctrl+~ 或编辑》链接
24、如何让空单元格自动填为0
选中需更改的区域》查找》空》替换》0
25、把里的数字转换到Excel
方法有多种，选中》复制》设置输入单元格为文本》选择性粘贴》值
选中》表格转换为文本》粘贴》分列》对分列选项设置为文本
另存为文本文件》EXCEL中打开文本文件》对导入文本对话框进行对应设置
主题帖子积分
快乐博彩 赢在乐彩
精华8注册时间
公式输入方法
1.公式选项板
以成绩等级计算公式“=IF(C2&=85,″A″,IF(C2&=70,″B″,IF(C2&=60,″C″,IF(C2&60,″D″)))”为例，使用“公式选项板”输入公式的步骤是：
(1)选中用来存放计算结果的单元格，本例中为“D2”。
(2)单击公式选项板中的“编辑公式”按钮（即“=”按钮），则表示公式的“=”自动被输入编辑栏。
(3)单击“函数”下拉列表框右端的下拉箭头，从列表中选择“IF”。如果你需要的函数没有出现在列表中，可单击“其它函数”，打开“粘贴函数”对话框寻找。
(4)在打开的“IF”公式选项板（见图1）中输入参数。对本例来说，第一个参数“Logical—test”是一个逻辑判断，须用键盘在其后的输入框内敲入“C2&=85”。第二个参数“Value—if—true”是“C2&=85”为“真”时的计算（判断）结果，也需要键盘在其后的输入框内填入“A”。第三个参数“Value—if—false”是逻辑判断“C2&=85”为“假”时的计算（判断）结果，本例是继续执行下一个IF函数。所以应让光标停留在“Value—if—false”框内，然后单击“函数”框中的“IF”，则一个新的“IF”公式选项板打开，按上面介绍的方法继续输入直至结束。在公式输入过程中，已输入的内容可以在Excel编辑栏中看到。另外，公式选项板可以用鼠标拖动，如果它影响观察，可单击输入框右边的按钮将它折叠起来。
（5）公式输入结束后，单击公式选项板中的“确定”按钮完成计算。
公式选项板是一种向导式输入，用户可以详细了解函数的各个参数、功能和当前的计算结果。当光标停留在参数框中时，还可以看到对参数的描述。其最大优点是输入时不必过多考虑分隔符、单元格引用等细节，用它建立和调试公式不易出错。
2.手工输入
手工输入主要用于套用现成的公式，或输入一些比较简单的公式。它的输入方法是：
（1）选中存放计算结果的单元格。
（2）用鼠标在Excel的编辑栏内单击，然后按照公式的组成（见上例）顺序输入。
（3）公式输入结束后，单击编辑栏左端“输入”(“√”)按钮进行计算。
对于一些非常简单的公式，如两个单元格的数值相减，也可以直接在存放结果的单元格内输入公式（如“=A1－B1”），然后回车计算出结果。
公式使用技巧
1.复制
复制是将公式应用于其它单元格的操作，最常用的有以下几种方法：
(1)拖动复制
操作方法是：选中存放公式的单元格，移动空心十字光标至单元格右下角。待光标变成小实心十字时，按住鼠标左键沿列（对行计算时）或行（对列计算时）拖动，至数据结尾完成公式的复制和计算。公式复制的快慢可由小实心十字光标距虚框的远近来调节：小实心十字光标距虚框越远，复制越快；反之，复制越慢。
(2)输入复制
此法是在公式输入结束后立即完成公式的复制。操作方法是：选中需要使用该公式的所有单元格，用上面介绍的方法输入公式，完成后按住Ctrl键并按回车键，该公式就被复制到已选中的所有单元格。
(3)选择性粘贴
操作方法是：选中存放公式的单元格，单击Excel工具栏中的“复制”按钮。然后选中需要使用该公式的单元格，在选中区域内单击鼠标右键，选择快捷选单中的“选择性粘贴”命令。打开“选择性粘贴”对话框后选中“粘贴”命令，单击“确定”，公式就被复制到已选中的单元格。
2.编辑
(1)打开公式选项板
如果公式是由几个Excel函数组合而成，而你又要用公式选项板修改某个函数的参数，则可用鼠标单击函数名称或括号内的参数，再单击编辑栏左端的“=”按钮，即可打开这个函数的公式选项板。修改完毕后单击“确定”将其关闭。
(2)公式与结果切换
单元格中的公式执行后显示的是计算结果，按Ctrl＋′键（位于键盘左上角），可使公式在显示公式内容与显示公式结果之间切换，方便了公式编辑和计算结果查看。
(3)单元格引用
单元格引用的目的在于确定计算范围。鼠标拖动引用单元格既快捷又不易出错。以SUM函数为例，如果你使用了公式选项板，可让光标停在“Number”框内，然后用鼠标拖过需要计算的单元格。若这些单元格是不连续的，可以按住Ctrl键分别拖过它们。如果你是用手工输入的公式，可用鼠标在SUM字样后的括号内单击，然后按相同方法拖过需要计算的单元格区域，则被引用的单元格区域及其分隔符会自动出现在公式中。
主题帖子积分
快乐博彩 赢在乐彩
精华8注册时间
excel彩票公式技巧,慢慢积A
不连续单元格统计
=SUM(COUNTIF(INDIRECT({&a1&,&c1&,&f1&,&g1&}),3))&&复制时用=SUM(COUNTIF(OFFSET($A1,,{0,2,5,6}),12))｝
不为0的最小数LARGE(D12:F12,COUNTIF(D12:F12,&&0&))
查找数字并引用
=INDEX($A$4A$1477,SMALL(IF(--$E$3E$1477=$N$2,ROW($A$4A$1477),&&),ROW()-4)-3)
& && & 引用区域& && && && && && & 条件
多条件统计
=sum(if(c2:c10=&男&，if(g2:g10=&是&,b2:b10)))数组公式
多条件求和
=SUM((条件1)*(条件2)*IF(ISNUMBER(D110),D110,))
百=INT(B3/100)&&十=INT(MOD(B3,100)/10)& & 个=MOD(B3,10)
大小=if(c3&4,&大&,&小&)
单双=IF(MOD(c3,2),&单&,&双&)
合质=if(or(c3=1,c3=2,c3=3,c3=5,c3=7),&质&,&合&)
复隔中=if(or(c4=c3,c4=d3,c4=e3),&复&,if(or(c4=c2,c4=d2,c4=e2),&隔&,&中&))
邻孤传=if(or(c4=c3,c4=d3,c4=e3),&邻&,if(or(abs(c4-c3)=1,abs(c4-d3)=1,abs(c4-e3)=1),&传&,&孤&))
跨度=max(c3:e3)-min(c3:e3)
类型=if(and(c3=d3,c3=e3,d3=e3),&豹子&,if(and(c3&&d3,c3&&e3,d3&&e3),&组六&,&组三&))
连续出现次数最多的字符
{=INDEX(A:A,MATCH(MAX(FREQUENCY(ROW(1:19),IF(A1:A18&&A2:A19,ROW(1:18),))),FREQUENCY(ROW(1:19),IF(A1:A18&&A2:A19,ROW(1:18),)),))}
=INDEX(A:A,MATCH(MAX(FREQUENCY(ROW(1:19),(A1:A18&&A2:A19)*ROW(1:18))),FREQUENCY(ROW(1:19),(A1:A18&&A$2:A$19)*ROW(1:18)),))
清除重复数字 一=IF(ISERROR(FIND(0,A1)),&&,0)&IF(ISERROR(FIND(1,A1)),&&,1)&IF(ISERROR(FIND(2,A1)),&&,2)&IF(ISERROR(FIND(3,A1)),&&,3)&IF(ISERROR(FIND(4,A1)),&&,4)&IF(ISERROR(FIND(5,A1)),&&,5)&IF(ISERROR(FIND(6,A1)),&&,6)&IF(ISERROR(FIND(7,A1)),&&,7)&IF(ISERROR(FIND(8,A1)),&&,8)&IF(ISERROR(FIND(9,A1)),&&,9)
二=(IF(COUNT(FIND(0,A1)),0,)&SUBSTITUTE(SUM(IF(ISNUMBER(FIND(ROW($19),A1)),ROW($19))*10^(9-ROW($19))),&0&,))
三=(IF(COUNT(FIND(0,A1)),0,))&SUBSTITUTE(SUM((MID(A1,FIND(ROW($19),A1&5^19),1)&0)*10^(9-ROW($19))),0,)
四=(IF(COUNT(FIND(0,A1)),0,)&SUBSTITUTE(SUM((1-ISERR(FIND(ROW($19),A1)))*ROW($19)*10^(9-ROW($19))),0,))
五(不变顺序)=RIGHT(TEXT(SUM(MID(A1,SMALL(--TEXT(FIND(ROW($110)-1,A1&&&),&[&=&&LEN(A1)&&]#;1&),ROW($110)),1)*10^(10-ROW($110))),REPT(&0&,10)),COUNT(FIND(ROW($110)-1,A1)))
说明:5^19=25 其中包含了0～9这10个数字。find(row(1:9),A1&5^19),如果A1中不包含某个数字，find的结果也不会返回错误值，而是返回一个大于len(A1)的数字。
去重复内容按列输出
=IF(ROW(1:1)&SUM(1/COUNTIF($A$1A$18,$A$1A$18)),&&,INDEX($A$1A$18,SMALL(IF(MATCH($A$1A$18,$A$1A$18,0)=ROW(INDIRECT(&1:&&ROWS($A$1A$18))),MATCH($A$1A$18,$A$1A$18,)),ROW(1:1))))
=IF(LEN(SMALL(IF(IF(MATCH(C5:K5&&&,C5:K5&&&,)&=COUNT(C5:K5),MATCH(C5:K5&&&,C5:K5&&&,))=COLUMN(A5:I5),C5:K5,4^8),COLUMN(A5:I5)))=5,&&,SMALL(IF(IF(MATCH(C5:K5&&&,C5:K5&&&,)&=COUNT(C5:K5),MATCH(C5:K5&&&,C5:K5&&&,))=COLUMN(A5:I5),C5:K5),COLUMN(A5:I5)))
=IF(COLUMN(A1)&COUNT(1/FREQUENCY($C5K5,$C5K5)),&&,SMALL(IF(IF(MATCH($C5K5&&&,$C5K5&&&,)&=COUNT($C5K5),MATCH($C5K5&&&,$C5K5&&&,))=COLUMN($A5I5),$C5K5),COLUMN(A5)))
columns(&a:z&).delete& &&&或Range(&e3:e65536&).ClearContents
统计不连结单元的含某数的个数
SUM(COUNTIF(OFFSET($D11,{0,1,2,3,4},{0,1,2,3,4}),2))
统计两组数的相同数个数
=SUM(IF(COUNTIF(A2:F2,H2:T2)&0,1/COUNTIF(H2:T2,H2:T2)))& &或=SUM(--(COUNTIF(H2:T2,A2:F2)&0))
有效数据行数[c65536].End(xlUp).Row
=IF(a1=&&,&&,IF(COUNTIF(b1:d1,目标值)=0,SUM(e1,1),&●&))
余码=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(&&,MID(A1,1,1),),MID(A1,2,1),),MID(A1,3,1),)复制代码=IF(ISNUMBER(FIND(0,F1)),0,&&)&SUBSTITUTE(SUMPRODUCT(ISNUMBER(FIND({1;2;3;4;5;6;7;8;9},F1))*{1;2;3;4;5;6;7;8;9}*10^(9-ROW($1:$9))),0,)复制代码
最大连出=MAX(FREQUENCY(IF(个数,ROW(个数)),IF(个数=0,ROW(个数))))
最大连空=MAX(FREQUENCY(IF(个数=0,ROW(个数)),IF(个数,ROW(个数))))
=MAX(FREQUENCY(IF($F$4F$103=&●&,ROW($F$4F$103)),IF($F$4F$103&&&●&,ROW($F$4F$103))))
最大连出=MAX(FREQUENCY(IF((N$3:N$20=N$2)*($H$3H$20=N$2),ROW(N$3:N$20)),IF(N$3:N$20&&N$2,ROW(N$3:N$20))))& & 数组公式
倒序排列
=IF(ISERROR(FIND(&9&,A1)),&&,&9&)&IF(ISERROR(FIND(&8&,A1)),&&,&8&)&IF(ISERROR(FIND(&7&,A1)),&&,&7&)&IF(ISERROR(FIND(&6&,A1)),&&,&6&)&IF(ISERROR(FIND(&5&,A1)),&&,&5&)&IF(ISERROR(FIND(&4&,A1)),&&,&4&)&IF(ISERROR(FIND(&3&,A1)),&&,&3&)&IF(ISERROR(FIND(&2&,A1)),&&,&2&)&IF(ISERROR(FIND(&1&,A1)),&&,&1&)&IF(ISERROR(FIND(&0&,A1)),&&,&0&)复制代码组六组三豹子：
=IF(B2=&&,&&,IF(AND(B2&&C2,B2&&D2,C2&&D2),&组六&,IF(AND(B2=C2,B2=D2,C2=D2),&豹子&,&组三& )))复制代码
主题帖子积分
快乐博彩 赢在乐彩
精华8注册时间
excel数据引用分为四种，我们给大家简单介绍下：
1、excel数据相对引用。这种最为简单，通常大家都知道，就是普通的公式计算。简单介绍几个公式，相信大家都很清楚。
1）、在d2单元格中需要计算：数据一和数据二的平均数与数据三的差。就可以输入
=(A2+B2)/2-C2
=sum(A2:B2)/2-C2
其中，括号必须是英文状态或者是半角中文状态，其中的列号ABC输入大小写没有关系，都会自动变成大写。如果求和的数据过多则不要用加号了，用sum求和函数较为方便。（求括号内英文冒号两侧的单元格之间的数据之和）sum函数不但可以求横向的行之和、竖向的列之和，还可以求对角线所辖区域的矩形区域之和。
2）、计算：数据一、数据二、数据三的最大值与最小值的差，可以输入：
=max(a2:d2)-min(a2:d2)
其中，max是求最大值函数，min是求最小值函数。和sum一样，这两个函数都支持对角线所辖区域矩形的数据运算。
3）、计算：如果数据一大于1500，则综合数据是数据一、数据二、数据三求和，若数据一介于之间则计算数据一和数据二之和，若数据一低于1000，则只记录数据一的结果。（这个例子在公司计算工资的时候很常用，但是要非常小心边界的判断，需要的时候可以用&=表示大于等于，用&=来表示小于等于）关于这个if判断语句的写法要非常小心括号的运用，我们可以在有图早期的一篇
中看到多次嵌套的运用。
=IF(A2&1500,SUM(A2:C2),IF((A2&1000),A2,SUM(A2:B2)))
这三个例子主要是介绍大家常见的三种模式：直接运算，用excel的函数进行运算，用程序语句进行运算。（这种提法是我自己随便想的，不一定有真正的三中模式之说。）
2、excel数据的绝对引用。参加了办公自动化培训班的朋友和自学excel的朋友应该对他不陌生。举例说明一下，还以上面的那些数据为例：
计算数据一中每个数据与最大值的差。（呵呵，也很常用的，就是计算平级的人或者部门之间的差距）
我们一般会在d2单元格输入
=MAX(A2:A6)-A2
可是我们无法通过拖动，生成但d3、d4、d5等下面单元格的数据，当我们拖动的时候，在d3单元格变成了
=MAX(A3:A7)-A3
这显然不是我们要的，我们在d3单元格想要的是
=MAX(A2:A6)-A3
所以，我们可以使用$来固定那些需要不变的数据，如上面a2单元格的公式就可以写成
=MAX(A$2:A$6)-A2
随你怎样拖动，max计算的区域就不会变化了。如果你想固定的更牢靠一点，可以在列标号前加$。
3、同文件内excel表间引用。同文件内不同表之间的数据引用和计算通常是使用英文状态的引号表名结合而来的，例如本例中：
引用表1当中的a列数据，可以在a2单元格输入
其中英文单引号中为表的名字，表和单元格名称间用英文的!分割开来。
4、不同文件间的excel引用。使用英文的中括号 [ ]来引用文件，如我们将正在操作的文件保存到d盘，默认名字为book1.xls 新建一个excel文件，引用book1.xls文件中第一列的数据：
=[book1.xls]1!a2
此例和上面的例子中英文的单引号 '&&可要可不要，不过excel默认都会给加上，另外本例中，当我们输入完公式，打回车键之后，excel会自动加上文件地址，如本例中可能会变为：
='d:\[book1.xls]1'!a2
主题帖子积分
快乐博彩 赢在乐彩
精华8注册时间
主题帖子积分
精华29注册时间
本帖最后由 轉裑ｄē距蘺 于
20:33 编辑
假设你在B2输入数据，C2为报警格 ，输入
=if(b2=&&,&&,if(b2&=50,&不足&,if(b2&=100,&库存过多&,&&)))
=IF($J187=&&,&&,LOOKUP($J187,{0,1,2,3,4,5,6,7,8,9},{&0159&,&0126&,&1237&,&2348&,&3459&,&0159&,&0126&,&1237&,&2348&,&3459&}))
=IF($A3=&&,&&,3-SUMPRODUCT(--ISERROR(FIND($R3:T3,AJ3))))
=IF(ISNUMBER(FIND(0,E16)),0,&&)&SUBSTITUTE(SUMPRODUCT(ISNUMBER(FIND({1;2;3;4;5;6;7;8;9},E16))*{1;2;3;4;5;6;7;8;9}*10^(9-ROW($1:$9))),0,)
=LEFT(TEXT(B108,&00000&),3)
=RIGHT(TEXT(B113,&00000&),3)
主题帖子积分
快乐博彩 赢在乐彩
精华8注册时间
excel 统计有数据单元格的数量:
=COUNTA(A1:A20)
=ROWS(A1:A20)-COUNTBLANK(A1:A20)
主题帖子积分
六段, 积分 6307, 距离下一级还需 3293 积分
六段, 积分 6307, 距离下一级还需 3293 积分
精华3注册时间
轉裑ｄē距蘺
管理员:你好!
你在这里写了同文件不同表间的单元格的引用问题,你是这样写的:
&3、同文件内excel表间引用。同文件内不同表之间的数据引用和计算通常是使用英文状态的引号表名结合而来的，例如本例中：
引用表1当中的a列数据，可以在a2单元格输入
其中英文单引号中为表的名字，表和单元格名称间用英文的!分割开来。&
& &&&这个我能明白,我的问题是:同文件不同表间的跨行单元格的引用时有附加条件时怎样表达?比如:
1.& &B和C表的A列都是表示开奖期号,B表是记录连续号开奖号表,C表是记录星期日的开奖号码表,因此,C表每行数据都与B表隔2行!但为了一致,其期号都统一用连续号的期号,这时候应怎样写引用公式呢?
2.& &特别是当引用的列不相同时又怎样表达呢?最近为这个问题我在贵网上专门发贴请教,但到目前为止尚无应答!
请你解答,谢谢!!
主题帖子积分
六段, 积分 6307, 距离下一级还需 3293 积分
六段, 积分 6307, 距离下一级还需 3293 积分
精华3注册时间
轉裑ｄē距蘺
& & 轉裑ｄē距蘺 管理员:
& && && &妳好!首先谢谢妳的关注!更感谢推背图应承帮我修改表格公式!
我在问题求助栏发表了专题的,也上传了文件和截图的,下面是链接:
请教:Excel同表不同表单间的跨行跨列数据引用计算公式(8楼文件最好!)
在上传过程中,有网管说有病毒,经我查杀,无病毒,请放心下载,解压!
在下载,解压时请关闭360实时监控,它容易将所有含宏的office文件当病毒文件处理!!
经我试验,下载时最好不要间断,流畅下载对文件解压最好!
主题帖子积分
五段, 积分 3085, 距离下一级还需 1715 积分
五段, 积分 3085, 距离下一级还需 1715 积分
精华2注册时间
本帖最后由 爱彩必中 于
10:55 编辑
如图，篮色部份的数字我想让其自动显示，怎么做？请教！
五一劳动勋章
国庆66周年纪念勋章
五一劳动勋章
五一劳动奖章
乐彩荣誉勋章
乐彩荣誉勋章
社区活动先锋
社区活动先锋
乐彩荣誉勋章
乐彩荣誉勋章
添加安全提问领取安全达人勋章
十年纪念勋章
验证邮箱获得十年纪念勋章
服务我国公益事业，将乐彩进行到底
&&&本社区所有言论，纯属发表者个人意见，与乐彩网立场无关Excel常用函数公式及技巧（5）
Excel常用函数公式及技巧（5）
Excel常用函数公式及技巧（5）
28.如何分班统计男女人数
男=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1))
女=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=G$1))
男｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$F$1))｝
女｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$G$1))｝
男｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$G$1)*$D$2:$D$446)｝
女｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$H$1)*$D$2:$D$446)｝
增加d列，输入公式：=B2&C2，合并数据后再利用countif公式对D列统计。
=COUNTIF($B$2:$B$446,E2)
29.在几百几千个数据中发现重复项
我的意思不是查找功能,那个我会用,比如有几百个人的名字输入单元格中,但我面对那么多名字真无法短时间内看出谁重复了,该如何办?
假设判断区域为A1:D10,格式/条件格式，选公式(不是数值)，输入：
=COUNTIF($A$1:$D$10,A1)&1
然后在格式中设置一个字体或图案颜色,确定,这样重复数据就变成了有色单元格。
30.统计互不相同的数据个数
例如,在 3 * 3 的区域中统计互不相同的数据个数,
结果应为 4 (4 个互不相同的数据)
数组公式=sum(1/countif(a1:c3,a1:c3))
还可以公式:
=COUNT(IF(FREQUENCY(A1:C3,A1:C3),1))
31.多个工作表的单元格合并计算
=Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4)
32.单个单元格中字符统计
假设 A1单元格中有数据"sdfsfjksfhweofiefondsfljsdfisdofjei"如何用公式统计出A1单元格中有多个不重复的字符?
=SUMPRODUCT(--(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(97:122)),""))=1))
数组公式=SUM(IF(ISERROR(FIND(CHAR(ROW(97:122)),A1)),,1))
这个公式只适用单元中的字符为小写字母,给个通用点的
=SUM(--(MATCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),)=ROW(INDIRECT("1:"&LEN(A2)))))
=SUM(IF(ISERROR(FIND(CHAR(ROW(97:122)),LOWER(A1))),,1))
33.数据区包含某一字符的项的总和,该用什么公式
=sumif(a:a,"*"&"某一字符"&"*",数据区)
34.函数如何实现分组编码
对数值进行分组编码
=A2&TEXT(COUNTIF($A$2:A2,A2),"00")
十三、【数值取整及进位】
1.取整数函数
907.5；1034.2；1500要改变为908；1035；1500公式为：
=CEILING(A1,1)
907；1034；1500要改变为910；1040；1500公式为：
=CEILING(A1,10)
如果要保留到百位数，即改变为1000；1100；1500公式为：
=CEILING(A1,100)
2.数值取整
在单元格中要取整数（只取整数不用考虑四舍五入）用什么函数呀？例如：10/4只要显示2就可以了！要考虑负数的因数呢？例如：(-10/4)要显示－2而不是－3？怎么办？
=TRUNC(A1,0)
=ROUNDDOWN(A1,0)
3.求余数的函数
比如：A1=28，A2=（A1÷6）的余数=4，请问这个公式怎么写？&
解答：=MOD(28,6)
4.四舍五入公式
=ROUND($B$1*A1,2)
=ROUND(B1*A1,2)
=round(a1,0)
=round(a1,0)*0.95
5.对数字进行四舍五入
对于数字进行四舍五入,可以使用INT（取整函数），但由于这个函数的定义是返回实数舍入后的整数值。因此，用INT函数进行四舍五入还是需要一些技巧的，也就是要加上0.5，才能达到取整的目的。公式应写成：
=INT(B2*100+0.5)/100
6.如何实现“见分进元”
在我们的工资中，有一项“合同补贴”，只要计算结果出现“分”值就在整数“元”进一位，也就是说3.01元进到4.00元，3.00元不变，整数“元”不变。
=IF((A3-INT(A3))&=0.3,IF((A3-INT(A3))&=0.8,1,0.5),0)+INT(A3)
＝IF(RIGHT(FIXED(A1,2),2)&B1,TRUNC(A2)+1,A2)
说明一下：A1即是要转换的目标；B2输入00（文本格式，必须是00这两个数） 。
＝IF(INT(A1)&&A1,INT(A1)+1,A1)
=ROUNDUP(A1,0)
=CEILING(A9,1)
=INT(A9+1)
7.四舍五入
如何将Excel 中的数据，希望把千位以下的数进行四舍五入，例如：3245& 希望变成3000；3690 希望成为400
=ROUND(C6*D6,2)
=ROUND(A2*0.001,)*1000
=ROUND(A2,-3)
=--FIXED(A2,-3)
=ROUND(A2/00
8.如何四舍五入取两位小数
如何四舍五入取两位小数,如2.145为2.15,0.1449为0.14.=ROUND(A1,2)
9.根据给定的位数，四舍五入指定的数值
对整数无效。四舍五入B234的数值，变成小数点后一位。
&&&& 12512.3
=ROUND(B23,1)
10.四舍六入
=IF(MOD(INT(A1),2)=0,IF(MOD(A1,1)=0.5,INT(A1),INT(A1+0.5)),INT(A1+0.5))
=IF(AND(RIGHT(A1*100,1)="0",RIGHT(A1*10,1)="5")=TRUE,IF(INT(A1)/2=INT(INT(A1)/2),INT(A1),ROUND(A1,0)),ROUND(A1,0))
AND(RIGHT(A1*100,1)="0",RIGHT(A1*10,1)="5")=TRUE 判断是否为一位小数,且是0.5，如果不符合上术要条件,按普通四舍五入法则处理,否则判断整数部分的奇偶。
=IF(RIGHT(A1,1)*1&5,INT(A1),IF(RIGHT(A1,1)*1&5,INT(A1)+1,IF(MOD(ROUND(A1,),2)=0,ROUND(A1,),ROUNDDOWN(A1,))))
=IF(ROUNDUP(A1*2,)=A1*2,IF(MOD(ROUND(A1,),2)=1,ROUNDDOWN(A1,),ROUNDUP(A1,)),ROUND(A1,))
11.如何实现2舍3入
做工资时，常遇到：3.2元要舍去0.2元变为3.00元,而3.3元要把0.3元入为0.5元变为3.5元.请教,该如何实现?
=ROUND(A1*2,0)/2
=CEILING(A1,0.5)
=IF((A1-INT(A1))&=0.2,INT(A1),IF((A1-INT(A1))&=0.5,INT(A1)+0.5,IF((A1-INT(A1))&=0.7,INT(A1),INT(A1)+1)))
=CEILING(A1-0.2,0.5)
=FLOOR(A1+0.2,0.5)
12.怎么设置单元格以千元四舍五入
比如输入123456，显示出来123，000=CEILING(ROUND(A1/)*1000
=round(a1,-3)
=mround(A1,1000)
13.ROUND函数的四舍五入不进位的解决方法？
计算一：A2=1345.3& B2=1232.4& C3=A2-B2=112.9&& D=0.05& E=ROUND(B2*D2,2)=5.64 &（计算结果为5.645，此运算没有进位）。
计算二：A2=1225.4& B2=1112.5& C3=A2-B2=112.9&& D=0.05& E=ROUND(B2*D2,2)=5.65（计算结果为5.645，此运算进位）。
以上两式中C3结果都为112.9，而为什么应用ROUND函数后结果却不一样。
请教高手有什么函数能保证四舍五入不会出错。
可将C列先变成文本性数据，再进行后面的运算，以达到计算的目的。
如：C列可改成C1=TRIM(A1-B1)，以此类推，只要是更改成文本性数据就行。
14.保留一位小数
我需要保留一位小数,不管后面是什么数字,超过5或不超过5,都向前进一位.
例如:329.99--&330.00
329.84-----&329.90
329.86-----&329.90
=roundup(*,2)或=round(a1+0.04,1)
15.如何三舍四入
=round(原数值+0.001,2)
16.另类四舍五入
我用Excle给别人算帐，由于要对上百家收费，找零却是个问题。于是我提出四舍五入，收整元。但是领导不同意，要求收取0.5元。例如：某户为123.41元，就收123.50元;如果是58.72元，就收58.5元。这可难坏了我。经过研究，我发现，可以在设置单元格中，设成分数，以2为分母，可以解决问题。但是打印出来的却是分数不好看，而且求和也不对。请各位高手给予指点。是这样的，如果是57.01元，则省去，即收57.00元；如果是57.31元，则进为57.50元；如果是57.70元，也收57.50元；要是57.80元，则收58.00元。
假设数据在A1
=INT(A1)+IF((A1-INT(A1)&=0.3),0,IF((A1-INT(A1)&0.7),1,0.5))
简化一下：
=INT(A1)+0.5*((A1-INT(A1)&0.3)+(A1-INT(A1)&0.7))
int函数取整数部分，A1-int(A1)取小数部分，根据你的意思：&=0.3按0算，0.3~0.7(含)按0.5算，0.7~0.99……按+1算
则：第一个公式不难理解了
简化公式中：“*((A1-INT(A1)&0.3)+(A1-INT(A1)&0.7))”即(小数部分&0.3)+(小数部分&0.7)
我们知道这是省略if的判断语句，条件为真返回true（也就是1）否在为false（0），那么如果小数&=0.3，则两个条件都为0，即整数部分+0.5*0=整数部分，介于0.3~0.7，则为整数部分+0.5*（1+0），大于0.7肯定也大于0.3啦，则为整数部分+0.5*（1+1）。
请问，如果是由几个分表汇总的总表想如此处理，该如何做。
例：e112位置=SUM(一库入库!G112,二库入库!G112,四库入库!G112,保健酒基地入库!G112,下陆仓库入库!G112)
汇总的结果为100.24，而我要求如果小数为24的话自动视为1累加，否则不便。
就是小数为0.24才加1，否则都舍掉？
若是：=if（sum公式-int（sum公式）=0.24，int（sum公式）+1，sum公式）
17.想把小数点和后面的数字都去掉，不要四舍五入
比如：&&&&&&&&
12.30&&& 变成&&&&& &&&12.00
45.32&&&&&&&&&&&&&&&& 45.00
25.38&&&&&&&&&&&&&&&& 25.00
6.54&&&&&&&&&&&&&&&&&& 6.00
13.02&&&&&&&&&&&&&&&& 13.00
59.68&&&&&&&&&&&&&&&& 59.00
23.62&&&&&&&&&&&&&&&& 23.00
=Rounddown(A1,0)
你要把A1换成你要转换的那个单元格啊，然后拖动就可以了！
我那里用的那个A1只是告诉你一个例子而已，你要根据你的实际情况来修改一下才能用的。
=INT(A1)=TRUNC(A1,0)
18.求真正的四舍五入后的数
请教如何在Excel中，求“金额合计”（小数点后二位数）时，所取的数值应是所求单元格中写的数字（四舍五入后的数字），而不是（四舍五入前）的数字。因为只有这样行和列及关联的工作表才能对得上，例如：表上的数值分别是：(1.802/2=0.901)0.90(A1); (1.604/2=0.802)0.80(A2);&&(1.406/2=0.703)0.70(A3);（因取小数点后二位）。合计数(A4)表中自己计算和显示是：(0.901+0.802+0.703=2.406)2.41（四舍五入后的数值）。但照表中的数值人工计算却是：(0.9+0.8+0.7=)2.4,有矛盾，还有许多例子，故请教各高手，如何设置公式，使得人工计算结果同表中一致。请指教。十分感谢！
工具》选项》重新计算》以显示精度为准& &前打钩
也可以用函数 ROUND() 使结果四舍五入 。如ROUND（算式，2）代表保留两位小数，如ROUND（算式，1）代表保留一位小数。
19.小数点进位
小数点进位如何把1.4进成2或1.3进成2=Ceiling(A1,1)
=Roundup(A1,0)
=INT(A1+0.9)
&=int(a1)+1
如何把1.4进成2，而1.2不进位
=ROUND(A1+0.1,0)
20.个位数归0或者归5
A*B后想得到C的结果值，用什么函数比较好
A&&&&&&&&& B&&&&&&&& C(想得到的数值)
320&&&&& &&1.1&&&&&&&&&&&&& &355
1140&& &&&&1.2&&&&&&&&&&&&& &1370
50&&&&&&& &1.3&&&&&&&&&&&&&& 65
16&&&&&&& &1.4&&&&&&&&&&&&&& 25=FLOOR(A1*B1+5*(MOD(A1*B1,5)&&0),5)
=CEILING(A1*B1,5)
十四、【大小值或中间值】
1.求平均值
如在列中有一组数字：10、7、9、27、2
=AVERAGE(A2:A6) 上面数字的平均值为11
行公式=AVERAGE(B2:D2)
2.如何实现求平均值时只对不等于零的数求均值？
＝AVERAGE (IF(A1:A5&0,A1:A5))
3.平均分的问题
假设一个班有60人，要统计出各个学科排名前50的学生的平均分，用公式应该如何写？如果用排序再来算的话很麻烦，能不能直接用公式找出前50名进行计算？
{=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:50"))))}
4.怎样求最大值（最小值或中间值）=IF(A2="","",MAX(OFFSET(C2,,,MIN(IF(A3:$A$15&&"",ROW(3:$15),15))-MAX(($A$2:A2&&"")*ROW($2:2)))))
=IF(A2="","",MAX((LOOKUP(ROW($A$2:$A$14),IF($A$2:$A$14&&"",ROW($A$2:$A$14)),$A$2:$A$14)=A2)*$C$2:$C$14))
=IF(A2="","",LOOKUP(2,1/FIND(A2,$B$2:$B$1000),$C$2:$C$1000))
=IF(A2="","",MAX(IF(ISNUMBER(FIND(A2,$B$2:$B$1000)),$C$2:$C$1000)))
5.平均数怎么弄
如在列中有一组数字：10、7、9、27、2
=AVERAGE(A2:A6) 上面数字的平均值为11
=AVERAGE(A2:A6,&5)&上面数字与&5&的平均值为10
6.去掉其中两个最大值和两个最小值的公式
我要将一行数据进行处理。要去掉其中两个最大值和两个最小值，不知道怎样运用公式，应该是：
=SUM(A1:A50)-MAX(A1:A50)-LARGE(A1:A50,2)-MIN(A1:A50)-SMALL(A1:A50,2)&
这个只能减去1个最大和1个最小值，不符合题意。可用下面的公式。
=SUM(A1:A20)-SUM(LARGE(A1:A20,{1,2}))-SUM(SMALL(A1:A20,{1,2}))
7.去一行最高分最低分求平均值
去一行中一个最高分和一个最低分求平均值
公式为：=(SUM(A5:E5)-MAX(A5:E5)-MIN(A5:E5))/(COUNTIF(A5:E5,"&0")-2)
但另用TRIMMEAN ()函数较好。=TRIMMEAN($A$5:$E$5,2/COUNT($A$5:$E$5))
为需要进行整理并求平均值的数组或数值区域。TRIMMEAN(array,percent)
为计算时所要除去的数据点的比例，例如，如果 percent = 0.2，在 20 个数据点的集合中，就要除去 4 个数据点 (20 x 0.2)：头部除去 2 个，尾部除去 2 个。
用活了TRIMMEAN函数，这个问题易如反掌。
8.在9个数值中去掉最高与最低然后求平均值
假设9个数值所在的区域为A1：A9
=(SUM(A1:A9)-MAX(A1:A9)-MIN(A1:A9))/7
=TRIMMEAN(A1:A9,2/COUNTA(A1:A9))
=TRIMMEAN(A1:A9,2/9)
｛=AVERAGE(SMALL(A1:A9,ROW(2:8)))｝
=ROUND((SUM(A1:A9)-MAX(A1:A9)-MIN(A1:A9))/(COUNT(A1:A9)-2),3)
=TRIMMEAN(A1:A9,0.286)
9.求最大值（n列）
{=MAX(($A$2:$A$16=$D$2)*($B$2:$B$16))}｛=LARGE(IF(FREQUENCY(N3:AT3,N3:AT3),TRANSPOSE(N3:AT3)),ROW(A1))｝
｛=LARGE(IF(FREQUENCY(TRANSPOSE(N3:AT3),TRANSPOSE(N3:AT3)),(N3:AT3)),ROW(A1))｝
10.如何实现求平均值时只对不等于零的数求均值？
= TRIMMEAN (IF(A1:A5&0,A1:A5))
11.得到单元格编号组中最大的数或最小的数
对字符格式的数字不起作用。
=MAX(B16:B25)
=MIN(B16:B25)&& （得到最小的数的公式）
12.标记出3个最大最小值
=RANK(B4,$B4:$Q4)+COUNTIF($B4:B4,B4)&=4
=RANK(B4,$B4:$Q4,2)+COUNTIF(B4:$Q4,B4)&=4
=(COUNTIF($B3:$Q3,"&"&B3)+COUNTIF($B3:B3,B3))&=3
=(COUNTIF($B3:$Q3,"&"&B3)+COUNTIF(B3:$B3,B3))&COUNT($B3:$Q3)-3
=SMALL(rongjun!$C4:$R4+COLUMN(rongjun!$C4:$R4)/1,3})
=LARGE(rongjun!$C4:$R4+COLUMN(rongjun!$C4:$R4)/1,3})
=RANK(B8,$B8:$Q8)+COUNTIF($B8:B8,B8)-1&=3
=RANK(B8,$B8:$Q8)+COUNTIF($B8:B8,B8)-1&COUNT($B8:$Q8)-3
＝C4+COLUMN(C4)/10000&LARGE(rongjun!$C4:$R4+COLUMN(rongjun!$C4:$R4)/10000,4)
13.取前五名，后五名的方法
{=LARGE(IF(ISERROR($D$2:$D$57),0,$D$2:$D$57),ROW())}
{=SMALL(IF(ISERROR($D$2:$D$57),0,$D$2:$D$57),ROW())}
{=LARGE(IF(ISERROR(D$2:D$57),"",D$2:D$57),ROW(1:5))}
{=SMALL(IF(ISERROR(D$2:D$57),"",D$2:D$57),ROW(1:5))}
=LARGE(B$2:B$57,ROW(A1))
=SMALL(B$2:B$57,ROW(A1)+COUNTIF(B$2:B$57,0))
=LARGE(D$2:D$57,ROW(A1))
=SMALL($D$2:$D$57,5-MOD(ROW(A5),5))
14.如何用公式求出最大值所在的行？
如A1：A10中有10个数，怎么求出最大的数在哪个单元格？
=MATCH(LARGE(A1:A10,1),A1:A10,0)
=ADDRESS(MATCH(SMALL(A1:A10,COUNTA(A1:A10)),A1:A10,0),1)
=ADDRESS(MATCH(MAX(A1:A10,1),A1:A10,0),1)
{=ADDRESS(MATCH(MAX(LEN(A1:A10)),LEN(A1:A10),FALSE),1)}
{=ADDRESS(SUM(($A$1:$A$10=MAX($A$1:$A$10))*(ROW($A$1:$A$10))),SUM(($A$1:$A$10=MAX($A$1:$A$10))*(COLUMN($A$1:$A$10))))}
15.如有多个最大值的话呢？如何一一显示其所在的单元格？
{=IF(ROW(1:1)&=COUNTIF($A$1:$A$100,MAX($A$1:$A$100)),ADDRESS(LARGE(IF($A$1:$A$100=MAX($A$1:$A$100),ROW($A$1:$A$100)),ROW(1:1)),1),"")}
16.求多个最高分
语文成绩有多个最高分,如何用公式的方法把他们抽出来(动态)?B15=INDEX(A:A,SMALL(IF(B$2:B$10=MAX(B$2:B$10),ROW($2:$10),65536),ROW(1:1)))&""
数组公式，按下Ctrl+Shift+Enter结束。
如果增加一个条件,就是在姓名前加一个类别,例如前5个人是A类的,后4个是B类的,请分类找出A类和B类的对应姓名的最高分
=INDEX(B:B,SMALL(IF(C$2:C$10=MAX(IF($A$2:$A$10="A",$C$2:C$10)),ROW($2:$10),IF(C$2:C$10=MAX(IF($A$2:$A$10="B",$C$2:$C$10)),ROW($2:$10),65536)),ROW(1:1)))&""
17.如何求多条件的平均值
应如何求下表中1月份400g重量的平均值
月份& &规格& & 重量
1& && & 400g& &&&401
1& && & 400g& &&&403
2& && & 400g& &&&402
2& && & 400g& &&&404
1& && & 200g& &&&201
1& && & 200g& &&&203
2& && & 200g& &&&202
試試這個行不行=SUMPRODUCT(($A$4:$A$10=1)*($B$4:$B$10="400g"),($C$4:$C$10))/SUMPRODUCT(($A$4:$A$10=1)*($B$4:$B$10="400g"))
比较土的办法
{=SUM(IF(($A$1:$A$7=1)*($B$1:$B$7="400g"),C1:C7,0))/SUM(IF(($A$1:$A$7=1)*($B$1:$B$7="400g"),1,0))}
数组公式：{=AVERAGE(IF(B2:B8="400g")*(A2:A8=1),(C2:C8),""))}
另一个数组公式试试：=Average(if((a1:a10=1)*(b1:b10="400g"),c1:c10))
=SUMIF(B1:B7,B1,C1:C7)/COUNTIF(B1:B7,B1)&&& 这个也可以
18.想求出第三大之数值
如A1：A4分别为1,2,2,3.&
想求出第三大之数值"1",应如何设公式。 &&
=large(if(frequency(a1:a4,a1:a4),a1:a4),3)
数组公式的解法
=LARGE((MATCH(A1:A10,A1:A10,)=ROW(1:10))*A1:A10,3)
&十五、【查询和查找引用】
1.查找顺序公式
=LOOKUP(2,1/(A1:A20&&0),A1:A20)
=MATCH(7,A1:A20)
=VLOOKUP(7,A1:B11,2)
2.怎样实现精确查询
=VLOOKUP(B11,B3:F7,4,FALSE)
=LOOKUP(B11,B3:B7,E3:E7)
用MATCH+INDEX
=INDEX(E3:E7,MATCH(B11,B3:B7,0))
用INDIRECT+MATCH
=INDIRECT("E"&MATCH(B11,B3:B7,0)+2)
用OFFSET+MATCH
=OFFSET(E3,MATCH(B11,B3:B7,0)-1,0)
用INDIRECT+ADDRESS+MATCH
=INDIRECT(ADDRESS(MATCH(B11,B4:B7,0)+3,5))
用数组公式
=INDEX(E1:E7,MAX(IF((B4:B7=B11),ROW(B4:B7),0)))
3.查找及引用
如何查找并引用B2单元格中所显示日期当日的相应代码的值。
B3=IF(COUNTIF($E$3:$E$20,A3),VLOOKUP($A3,$E$2:$M$20,MATCH(B$2,$F$2:$M$2,)+1,),"")
4.查找函数的应用
我想在A5输入表的名称，B5自动跳出该表中B列的最后一个有效数值，请问B5的公式该如何设定？ =LOOKUP(9E+307,INDIRECT(A5&"!"&"B:B"))
B2 =IF(A2="","",LOOKUP(9E+307,INDIRECT(A2&"!B:B")))
5.怎么能方便的判断某个单元格中包含多少个指定的字符？
例：A1 中是“ASAFAG”，我希望计算出A1里面有多少个“A”......
=LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))
6.如何用查找函数
一、要求: 利用公式从左表中查询相应的地区，结果放在H14单元格
=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单元格）。用数组公式：
=INDIRECT("G"&MAX((D14:D22=C25)*ROW(D14:D22)))
D25 =LOOKUP(2,1/(D14:D22=C25),G14:G22)
7.日期查找的问题
我有一个日期比如:,我想知道它减去一个固定天数比如6后,最接近它的一个星期四(只能提前)是多少号
的答案应该是而不是
日期在A1处，B1处输入：=MAX((WEEKDAY(A1-6-{1,2,3,4,5,6,7},2)=4)*(A1-6-{1,2,3,4,5,6,7}))
B1, 输入公式 :=A1-6-MOD(WEEKDAY(A1-6,2)+3,7)
8.如何自动查找相同单元格内容
=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))
9.查找函数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))
10.怎样对号入座（查找）
=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)
11.一个文本查找的问题
如何在一个单元格中,统计某个字符出现的次数,例如:单元格A1中填有:张三/李四/王五",如何通过公式来计算此单元格中共填有几个人姓名,每个人姓名之间用"/"符号分开,烦请相告.=LEN(A1)-LEN(SUBSTITUTE(A1,"/",))+1
12.查找一列中最后一个数值
我想用公式知道,另一个表中"A"列最下面一个数是多少,就行了.用不定值的,因为还有数据有增加,
=LOOKUP(9E+307,Sheet2!A:A)——最后一个数值
=LOOKUP(REPT("座",255),Sheet2!A:A)——最后一个文本
=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)
13.查找重复字符
&&&& A&&&&&&&&&&&&&&&& &&&&&&&&&& B
1245689&&&&&&& &&&&&&&&0134578
查找单元格Ａ和Ｂ里重复及不重复的字符
正确答案：重复字符－1458
&&& 不重复字符－023679
以下公式对数字有效：
重复数字：
=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,)
不重复数字：
=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结束。
重复数字：
=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,)
不重复数字：
=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,)
14.请教查找替换问题
把表1中字符在4个以上的字段(含4个)查找出来,替换成表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,"="&"????*")))
15.IF函数替换法总结
条件说明：小于10返回500，小于20返回800，小于30返回1100，小于40返回1400，大于40返回1700
类似于以上要求，大家最先想到IF函数，这也本属IF专长。但用IF一般要长长的公式，且计算较慢。现总结一下IF之替换公式，望能抛砖引玉，在我的倡导下各位提供更完善的方案。其中部分公式通用，部分公式有局限性，请看说明。(前18个条件公式，根据速度，排名如下)
1=SMALL({500;800;00},COUNTIF($A$9:$A$13,"&="&A1))
2=INDEX({500;800;00},COUNTIF($A$9:$A$13,"&="&A1))
3=CHOOSE(COUNTIF($A$9:$A$13,"&="&A1),500,800,00)
4=LOOKUP(A1,{0,10,20,30,40},{500,800,00})
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,00},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,00})
12=INDEX({500;800;00},MATCH(A1,{0;10;20;30;40},1))
13=CHOOSE(MATCH(A1,{0;10;20;30;40},1),500,800,00)
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,))))
16=CHOOSE(SUM((A1&={0;10;20;30;40})*1),500,800,00)
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,00)
新增公式：
19=CHOOSE(MIN(INT(A1/(ROW($1:$4)*10))+1,5),500,800,00)
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,00})
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
16.查找的函数（查找末位词组）
（数组公式：）=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,)
（数组公式：）=RIGHT(A2,MATCH(1,FIND(" ",RIGHT(" "&A2,ROW($1:$100))),)-1)
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50))&& （好）
其实这个公式的思路, 是可以变化的，改变REPT( )中的数值, 可以返回, 指定空格位置後的数据，比如：
A1&&=一 二三 四 五 六 七 八 九
10个普通公式, 分别为 :
1=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT("",100)),100)) 返回第0空格位置後的数据&一 二 三 四 五 六 七 八 九
2=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),100)) 返回第8 空格位置後的数据&九3=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",40)),100)) 返回第7 空格位置後的数据&八 九
4=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),100)) 返回第6 空格位置後的数据&七 八九
5=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",23)),100)) 返回第5空格位置後的数据&六 七八 九
6=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",18)),100)) 返回第4 空格位置後的数据&五 六 七 八 九
7=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",14)),100)) 返回第3 空格位置後的数据&四 五 六 七 八 九
8=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",12)),100)) 返回第2 空格位置後的数据&三 四 五 六 七 八 九
9=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",11)),100)) 返回第1 空格位置後的数据&二 三 四 五 六 七 八 九
10=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",9)),100)) 返回第0空格位置後的数据&一 二三 四 五 六 七 八 九
17.怎样从原始数据中自动获取最后一个数据
公式=LOOKUP(1,0/($A$1:$A$100=C2),$B$1:$B$100)
18.两列数据查找相同值对应的位置
=MATCH(B1,A:A，0)
19.查找数据公式两个(基本查找函数为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))
&十六、【输入数据的技巧】
谈谈Excel输入的技巧
在Excel工作表的单元格中，可以使用两种最基本的数据格式：常数和公式。常数是指文字、数字、日期和时间等数据，还可以包括逻辑值和错误值，每种数据都有它特定的格式和输入方法，为了使用户对输入数据有一个明确的认识，有必要来介绍一下在Excel中输入各种类型数据的方法和技巧。
【1】输入文本
Excel单元格中的文本包括任何中西文文字或字母以及数字、空格和非数字字符的组合，每个单元格中最多可容纳32000个字符数。虽然在Excel中输入文本和在其它应用程序中没有什么本质区别，但是还是有一些差异，比如我们在Word、PowerPoint的表格中，当在单元格中输入文本后，按回车键表示一个段落的结束，光标会自动移到本单元格中下一段落的开头，在Excel的单元格中输入文本时，按一下回车键却表示结束当前单元格的输入，光标会自动移到当前单元格的下一个单元格，出现这种情况时，如果你是想在单元格中分行，则必须在单元格中输入硬回车，即按住Alt键的同时按回车键。
【2】输入分数
几乎在所有的文档中，分数格式通常用一道斜杠来分界分子与分母，其格式为“分子/分母”，在Excel中日期的输入方法也是用斜杠来区分年月日的，比如在单元格中输入“1/2”，按回车键则显示“1月2日”，为了避免将输入的分数与日期混淆，我们在单元格中输入分数时，要在分数前输入“0”（零）以示区别，并且在“0”和分子之间要有一个空格隔开，比如我们在输入1/2时，则应该输入“0 1/2”。如果在单元格中输入“8 1/2”，则在单元格中显示“8 1/2”，而在编辑栏中显示“8.5”。
&& &【3】输入负数
在单元格中输入负数时，可在负数前输入“-”作标识，也可将数字置在（）括号内来标识，比如在单元格中输入“（88）”，按一下回车键，则会自动显示为“-88”。
【4】输入小数
在输入小数时，用户可以向平常一样使用小数点，还可以利用逗号分隔千位、百万位等，当输入带有逗号的数字时，在编辑栏并不显示出来，而只在单元格中显示。当你需要输入大量带有固定小数位的数字或带有固定位数的以“0”字符串结尾的数字时，可以采用下面的方法：选择“工具”、“选项”命令，打开“选项”对话框，单击“编辑”标签，选中“自动设置小数点”复选框，并在“位数”微调框中输入或选择要显示在小数点右面的位数，如果要在输入比较大的数字后自动添零，可指定一个负数值作为要添加的零的个数，比如要在单元格中输入“88”后自动添加3个零，变成“88 000”，就在“位数”微调框中输入“-3”，相反，如果要在输入“88”后自动添加3位小数，变成“0.088”，则要在“位数”微调框中输入“3”。另外，在完成输入带有小数位或结尾零字符串的数字后，应清除对“自动设置小数点”符选框的选定，以免影响后边的输入；如果只是要暂时取消在“自动设置小数点”中设置的选项，可以在输入数据时自带小数点。
【5】输入货币值
Excel几乎支持所有的货币值，如人民币（￥）、英镑（￡）等。欧元出台以后，Excel2000完全支持显示、输入和打印欧元货币符号。用户可以很方便地在单元格中输入各种货币值，Excel会自动套用货币格式，在单元格中显示出来，如果用要输入人民币符号，可以按住Alt键，然后再数字小键盘上按“0165”即可。快速输入欧元符号 先按下Alt键，然后利用右面的数字键盘(俗称小键盘)键入0128这4个数字，松开Alt键，就可以输入欧元符号。
【6】输入日期
Excel是将日期和时间视为数字处理的，它能够识别出大部分用普通表示方法输入的日期和时间格式。用户可以用多种格式来输入一个日期，可以用斜杠“/”或者“-”来分隔日期中的年、月、日部分。比如要输入“日”，可以在单元各种输入“”或者“”。如果要在单元格中插入当前日期，可以按键盘上的Ctrl+;组合键。
&&& 【7】输入时间
在Excel中输入时间时，用户可以按24小时制输入，也可以按12小时制输入，这两种输入的表示方法是不同的，比如要输入下午2时30分38秒，用24小时制输入格式为：2:30:38，而用12小时制输入时间格式为：2:30:38 p，注意字母“p”和时间之间有一个空格。如果要在单元格中插入当前时间，则按Ctrl+Shift+;键。
&&& 【8】输入比值
&&& 如何在excel中输入比值(1:3)，单元格式设置为文本即可。先设成文本格式，再输入。
【9】输入0开头
在Excel单元格中，输入一个以“0”开头的数据后，往往在显示时会自动把“0”消除掉。要保留数字开头的“0”，其实是非常简单的。只要在输入数据前先输入一个“‘ ”（单引号），这样跟在后面的以“0”开头的数字的“0”就不会被系统自动消除。还有更好的办法，就是设置单元格格式为自定义“000000#“，0的个数依编码长度定，这样可以进行数值运算。如果这带0开头的字串本身是文本，或者是不定长的，那干脆先设该部分单元格格式为文本好了。另外还可用英语逗号开头再输就可以了。
【10】输入百分数
在单元格中输入一个百分数（如60％），按下回车键后显示的却是0.6。出现这种情况的原因是因为所输入单元格的数据被强制定义成数值类型了，只要更改其类型为“常规”或“百分数”即可。操作如下：选择该单元格，然后单击“格式”菜单中的“单元格”命令，在弹出的对话框中选择“数字”选项卡，再在“分类”栏中把其类型改为上述类型中的一种即可。如果我要求为负值的百分数自动显示成红色，可以再利用条件格式进行设置，格式-条件格式-单元格数值-小于-0(格式-图案-红色)，选中要设置的单元格-----ctrl+1---分类---自定义---输入&& 0.00%;[红色]-0.00%
【11】勾怎么输入
1、按住ALT键输入41420后放开ALT键√
2、首先选择要插入“√”的单元格，在字体下拉列表中选择“Marlett”字体，输入a或b，即在单元格中插入了“√”。
【12】输入无序数据
??在Excel数据表中，我们经常要输入大批量的数据，如学生的学籍号、身份证号等。这些数值一般都无规则，不能用“填充序列”的方法来完成。通过观察后我们发现，这些数据至少前几位是相同的，只有后面的几位数值不同。通过下面的设置，我们只要输入后面几位不同的数据，前面相同的部分由系统自动添加，这样就大大减少了输入量。例如以学籍号为例，假设由8位数值组成，前4位相同，均为0301，后4位为不规则数字，如学籍号为011369等。操作步骤如下：选中学籍号字段所在的列，单击“格式”菜单中的“单元格”命令，在“分类”中选择“自定义”，在“类型”文本框中输入“”。不同的4位数字全部用“0”来表示，有几位不同就加入几个“0”，[确定]退出后，输入“56”按回车键，便得到了“”，输入“1369”按回车便得到了“”。身份证号的输入与此类似。
【13】快速输入拼音
选中已输入汉字的单元格，然后单击“格式→拼音信息→显示或隐藏”命令，选中的单元格会自动变高，再单击“格式→拼音信息→编辑”命令，即可在汉字上方输入拼音。单击“格式→拼音信息→设置”命令，可以修改汉字与拼音的对齐关系。
【14】快速输入自定义短语
使用该功能可以把经常使用的文字定义为一条短语，当输入该条短语时，“自动更正”便会将它更换成所定义的文字。定义“自动更正”项目的方法如下:单击“工具→自动更正选项”命令，在弹出的“自动更正”对话框中的“替换”框中键入短语，如“电脑报”，在“替换为”框中键入要替换的内容，如“电脑报编辑部”，单击“添加”按钮，将该项目添加到项目列表中，单击“确定”退出。以后只要输入“电脑报”，则“电脑报编辑部”这个短语就会输到表格中。具体步骤：
1.执行“工具→自动更正”命令，打开“自动更正”对话框。
2.在“替换”下面的方框中输入“pcw”（也可以是其他字符，“pcw”用小写），在“替换为”下面的方框中输入“《电脑报》”，再单击“添加”和“确定”按钮。
3.以后如果需要输入上述文本时，只要输入“pcw”字符?此时可以不考虑“pcw”的大小写?，然后确认一下就成了。
【15】填充条纹
如果想在工作簿中加入漂亮的横条纹，可以利用对齐方式中的填充功能。先在一单元格内填入“*”或“~”等符号，然后单击此单元格，向右拖动鼠标，选中横向若干单元格，单击“格式”菜单，选中“单元格”命令，在弹出的“单元格格式”菜单中，选择“对齐”选项卡，在水平对齐下拉列表中选择“填充”，单击“确定”按钮。
【16】上下标的输入
在单元格内输入如103类的带上标(下标)的字符的步骤:
(1)按文本方式输入数字(包括上下标)，如103键入\'103；
(2)用鼠标在编辑栏中选定将设为上标(下标)的字符，上例中应选定3；
(3)选中格式菜单单元格命令，产生[单元格格式]对话框；
(4)在[字体]标签中选中上标(下标)复选框，再确定。
【17】文本类型的数字输入
证件号码、电话号码、数字标硕等需要将数字当成文本输入。常用两种方法:一是在输入第一个字符前，键入单引号"\'"；二是先键入等号"="，并在数字前后加上双引号"""。请参考以下例子:
键入\'027，单元格中显示027；
键入="001"，单元格申显示001；
键入="""3501"""，单元格中显示"3501"。(前后加上三个双撇号是为了在单元格中显示一对双引号)；
键入="9\'30""，单元格中显示9\'30"；
【18】多张工作表中输入相同的内容
几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1、Sheet2......来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其它工作表之中。输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系，否则在一张表单中输入的数据会接着出现在选中的其它工作表内。
【19】不连续单元格填充同一数据
选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。在编辑区中输入数据，然后按住Ctrl键，同时敲一下回车，在所有选中的单元格中都出现了这一数据。
【20】利用Ctrl＋*选取文本
如果一个工作表中有很多数据表格时，可以通过选定表格中某个单元格，然后按下Ctrl＋*键可选定整个表格。Ctrl＋*选定的区域为：根据选定单元格向四周辐射所涉及到的有数据单元格的最大区域。这样我们可以方便准确地选取数据表格，并能有效避免使用拖动鼠标方法选取较大单元格区域时屏幕的乱滚现象。
【21】快速清除单元格的内容
如果要删除内容的单元格中的内容和它的格式和批注，就不能简单地应用选定该单元格，然后按Delete键的方法了。要彻底清除单元格,可用以下方法：选定想要清除的单元格或单元格范围；单击“编辑”菜单中“清除”项中的“全部”命令，这些单元格就恢复了本来面目。
【22】在Excel中插入斜箭头
&&&& 经常使用Excel的朋友会遇到这样一个问题：在Excel中想插入斜箭头，但Excel本身没有这样的功能，是不是就没有其他办法了呢？答案是否定的。我们要想在Excel中插入斜箭头，首先我们在要插入斜箭头的单元格里调整好大小（为了方便插入斜箭头），然后打开Word，插入一个表格（一个框即可），调整好表格大小，在这个框里插入一个斜箭头，然后把这个框复制到Excel要插入斜箭头的单元格中，再调整大小，便大功告成。我们在调整斜箭头的时候，可以先把复制过来的斜箭头打散，方法是：选中斜箭头，按右键，“取消组合”，注意调整好大小后，调整斜线使之适合单元格，方法是：点击右键，选择“编辑顶点”，这时线条两端会变成两个小黑点，我们可以自由编辑线条了。至于文字，选中文本框，移动位置，直至适合位置即可。我们赶快试试吧。
&&& 【23】其它输入补充
※在同一单元格内连续输入多个测试值 一般情况下，当我们在单元格内输入内容后按回车键，鼠标就会自动移到下一单元格，如果我们需要在某个单元格内连续输入多个测试值以查看引用此单元格的其他单元格的动态效果时，就需要进行以下操作:单击“工具→选项→编辑”，取消选中“按Enter键后移动”选项（），从而实现在同一单元格内输人多个测试值。
※输入数字、文字、日期或时间 单击需要输入数据的单元格，键入数据并按Enter或Tab键即可。如果是时间，用斜杠或减号分隔日期的年、月、日部分，例如，可以键入 9/5/96 或 Jun-96。如果按12小时制输入时间，请在时间数字后空一格，并键入字母 a(上午) 或 p(下午)，例如，9:00 p。否则，如果只输入时间数字，Excel将按 AM(上午)处理。
※将单元格区域从公式转换成数值 有时，你可能需要将某个单元格区域中的公式转换成数值，常规方法是使用“选择性粘贴”中的“数值”选项来转换数据。其实，有更简便的方法:首先选取包含公式的单元格区域，按住鼠标右键将此区域沿任何方向拖动一小段距离(不松开鼠标)，然后再把它拖回去，在原来单元格区域的位置松开鼠标 (此时，单元格区域边框变花了)，从出现的快捷菜单中选择“仅复制数值”。
※快速输入有序文本 如果你经常需要输入一些有规律的序列文本，如数字（1、2……）、日期（1日、2日……）等，可以利用下面的方法来实现其快速输入:先在需要输入序列文本的第1、第2两个单元格中输入该文本的前两个元素（如“甲、乙”）。同时选中上述两个单元格，将鼠标移至第2个单元格的右下角成细十字线状时（我们通常称其为“填充柄”），按住鼠标左键向后（或向下）拖拉至需要填入该序列的最后一个单元格后，松开左键，则该序列的后续元素（如“丙、丁、戊……”）依序自动填入相应的单元格中。
※输入有规律数字 有时需要输入一些不是成自然递增的数值（如等比序列:2、4、8……），我们可以用右键拖拉的方法来完成:先在第1、第2两个单元格中输入该序列的前两个数值（2、4）。同时选中上述两个单元格，将鼠标移至第2个单元格的右下角成细十字线状时，按住右键向后（或向下）拖拉至该序列的最后一个单元格，松开右键，此时会弹出一个菜单（），选“等比序列”选项，则该序列（2、4、8、16……）及其“单元格格式”分别输入相应的单元格中（如果选“等差序列”，则输入2、4、6、8……）。
※巧妙输入常用数据 有时我们需要输入一些数据，如单位职工名单，有的职工姓名中生僻的字输入极为困难，如果我们一次性定义好“职工姓名序列”，以后输入就快多了。具体方法如下:将职工姓名输入连续的单元格中，并选中它们，单击“工具→选项”命令打开“选项”对话框，选“自定义序列”标签（），先后按“导入”、“确定”按钮。以后在任一单元格中输入某一职工姓名（不一定非得是第一位职工的姓名），用“填充柄”即可将该职工后面的职工姓名快速填入后续的单元格中。
※快速输入特殊符号 有时候我们在一张工作表中要多次输入同一个文本，特别是要多次输入一些特殊符号（如※），非常麻烦，对录入速度有较大的影响。这时我们可以用一次性替换的方法来克服这一缺陷。先在需要输入这些符号的单元格中输入一个代替的字母（如X，注意:不能是表格中需要的字母），等表格制作完成后，单击“编辑→替换”命令，打开“替换”对话框（），在“查找内容”下面的方框中输入代替的字母“X”，在“替换为”下面的方框中输入“※”，将“单元格匹配”前面的钩去掉（否则会无法替换），然后按“替换”按钮一个一个替换，也可以按“全部替换”按钮，一次性全部替换完毕。
※快速输入相同文本 有时后面需要输入的文本前面已经输入过了，可以采取快速复制（不是通常的“Ctrl+C”、“Ctrl+X”、“Ctrl+V”）的方法来完成输入: 1.如果需要在一些连续的单元格中输入同一文本（如“有限公司”），我们先在第一个单元格中输入该文本，然后用“填充柄”将其复制到后续的单元格中。 2.如果需要输入的文本在同一列中前面已经输入过，当你输入该文本前面几个字符时，系统会提示你，你只要直接按下Enter键就可以把后续文本输入。 3.如果需要输入的文本和上一个单元格的文本相同，直接按下“Ctrl＋D（或R）”键就可以完成输入，其中“Ctrl+D”是向下填充，“Ctrl+R”是向右填充。 4.如果多个单元格需要输入同样的文本，我们可以在按住Ctrl键的同时，用鼠标点击需要输入同样文本的所有单元格，然后输入该文本，再按下“Ctrl+Enter”键即可。
※快速给数字加上单位 有时我们需要给输入的数值加上单位（如“立方米”等），少量的我们可以直接输入，而大量的如果一个一个地输入就显得太慢了。我们用下面的方法来实现单位的自动输入:先将数值输入相应的单元格中（注意:仅限于数值），然后在按住Ctrl键的同时，选取需要加同一单位的单元格，单击“格式→单元格”命令，打开“单元格格式”对话框（），在“数字”标签中，选中“分类”下面的“自定义”选项，再在“类型”下面的方框中输入“#”“立”“方”“米”，按下确定键后，单位（立方米）即一次性加到相应数值的后面。
※巧妙输入位数较多的数字 大家知道，如果向Excel中输入位数比较多的数值（如身份证号码），则系统会将其转为科学计数的格式，与我们的输入原意不相符，解决的方法是将该单元格中的数值设置成“文本”格式。如果用命令的方法直接去设置，也可以实现，但操作很慢。其实我们在输入这些数值时，只要在数值的前面加上一个小“'”就可以了（注意:'必须是在英文状态下输入）。
※快速在多个单元格中输入相同公式 先选定一个区域，再键入公式，然后按“Ctrl+Enter”组合键，可以在区域内的所有单元格中输入同一公式。
※同时在多个单元格中输入相同内容 选定需要输入数据的单元格，单元格可以是相邻的，也可以是不相邻的，然后键入相应数据，按“Ctrl+Enter”键即可。
※快速输入日期和时间 当前日期 选取一个单元格，并按“Ctrl+;” 当前时间 选取一个单元格，并按“Ctrl+Shift+;” 当前日期和时间 选取一个单元格，并按“Ctrl+;”，然后按空格键，最后按“Ctrl+Shift+;” 注意:当你使用这个技巧插入日期和时间时，所插入的信息是静态的。要想自动更新信息，你必须使用TODAY和NOW函数。
※快速输入无序数据
在Excel数据表中，我们经常要输入大批量的数据，如学生的学籍号、身份证号等。这些数值一般都无规则，不能用“填充序列”的方法来完成。通过观察后我们发现，这些数据至少前几位是相同的，只有后面的几位数值不同。通过下面的设置，我们只要输入后面几位不同的数据，前面相同的部分由系统自动添加，这样就大大减少了输入量。例如以学籍号为例，假设由8位数值组成，前4位相同，均为0301，后4位为不规则数字，如学籍号为011369等。操作步骤如下：选中学籍号字段所在的列，单击“格式”菜单中的“单元格”命令，在“分类”中选择“自定义”，在“类型”文本框中输入“”（如图2）。不同的4位数字全部用“0”来表示，有几位不同就加入几个“0”，[确定]退出后，输入“56”按回车键，便得到了“”，输入“1369”按回车便得到了“”。身份证号的输入与此类似。
※输入公式
单击将要在其中输入公式的单元格，然后键入＝(等号)，若单击了“编辑公式”按钮或“粘贴函数”按钮，Excel将插入一个等号，接着输入公式内容，按Enter键。
※输入人名时使用“分散对齐”
在Excel表格中输入人名时为了美观，我们一般要在两个字的人名中间空出一个字的间距。按空格键是一个办法，但是我们这里有更好的方法。我们以一列为例，将名单输入后，选中该列，点击“格式→单元格→对齐”，在“水平对齐”中选择“分散对齐”，最后将列宽调整到最合适的宽度，整齐美观的名单就做好了。
※如何在excel单元格中输入01
這個函數很管用...值得一試哦!例:&&=TEXT(A1,"00000")
把单元格设置为文本格式再输入数据，或输入'(撇号)再输入数据，或根据要显示的数字位数自定义单元格格式：如要显示5位，不足5位的前面用0填足，自定义单元格格式：00000
输入123显示00123，输入1显示00001，输入12345，显示12345
※在EXCEL中增加自动填充序列
在Excel中提供了自动填充功能，我们在使用时，可以通过拖动“填充柄”来完成数据的自动填充。例如要输入甲、乙、丙、丁……，可以先在指定单元格输入甲，然后将鼠标移至单元格的右下角的小方块处，直至出现“＋”字，按住鼠标左键，向下（右）拖动至目的单元格，然后松开即完成了自动填充。可是有时我们会发现有一些数据序列不能自动填充，例如车间一、车间二、车间三等，填充方法有两种：
第一种：单击“菜单”栏上的“工具”，选“选项”→“自定义序列”，这时就可以在“输入序列”栏输入要定义的序列。需要注意的是每输入完成一项就要回车一次，表示一项已经输入完毕，全部输入完成以后单击“添加”→“确定”，这样我们自定义的序列就可以使用了。
第二种：首先把你要添加的序列输入到一片相临的单元格内，例如要定义一个序列：车间一、车间二、车间三，把这三项分别输入到单元H1：H3，单击“工具”→“选项”→“自定义序列”→“导入”，在“导入序列所在的单元格”所指的对话框中输入H1：H3，单击“导入”→“添加”→“确定”，这样新序列就产生了。
定义的序列如果不再使用，还可删除，方法是：单击“工具”→“选项”→“自定义序列”，在“自定义序列”框中，单击要删除的序列，再单击“删除”→“确定”。
※如何输入假分数
1又2分之1怎么输入
单元格格式设成”分数“，单元格中输入1.5，先輸入1，再按空白鍵；再輸入1/2，
输入后是这样&&“1&&1/2 ”&&，不是内行人看不懂的。
二分之一,四分之一, 四分之三可用ALT+189(188,190)获得。
先输入0，空格，再输入3/2。
※录入准考证号码有妙招
最近在学校参加招生报名工作，每位新生来校报到时，我们先请他们填写一张信息表，例如姓名、性别、准考证号码、联系电话、邮编等内容，然后在Excel中进行填写，这样无论是数据统计还是分班都方便多了。
准考证号码是类似于“”的8位数字，如果直接输入的话，Excel会自作聪明地去除最前面的0，常规的做法是在录入数字时手工输入一个半角的单引号作为前导引号，但由于需要录入的数据量太大，因此便将这一列设置成“文本”格式。
很快，我便发觉本地所有考生的准考证号码中前4位数字都是相同的，是否可以想一个办法让Excel自动录入最前面的“0436”呢？
选定“准考证号码”列，打开“格式→单元格格式→数字”对话框，如图所示，在“分类”下拉列表框中选择“自定义”项，在右侧的“类型”栏中输入“"0436"@”，这里的“0436”是准考证号码最前面的4位数字，录入时注意不要忘记前后的半角双引号，最后点击“确定”按钮退出。
现在只需要录入准考证号码后面的4位数字，Excel会自动添加前面的“0436”，这样效率明显提高。
编辑提示：如果需要录入的准考证号码位数非常长，这样可能会出现其他的显示错误，因为Excel的缺省设置是单元格中输入的数字被限制在11位，一旦超过将会以科学记数格式显示所输入的数字，例如“0301”将被显示为“3．65202E＋14”；当输入的数字超过15位时，第15位以后的数字将显示为0。其实，除了将该列设置为“文本”格式外，此时我们还可以采取上述同样的方法简化录入操作，毕竟最前面的几位数字总是相同的。
※向上填充的快捷键
我只会向下填充的快捷键，向上-向左-向右的都是什么呢？
解答：向上-Alt+E,I,U。向左-Alt+E,I,L。向右-CTRL+R
一列中不输入重复数字
[数据]--[有效性]--[自定义]--[公式]
输入=COUNTIF(A:A,A1)=1
如果要查找重复输入的数字
条件格式》公式》=COUNTIF(A:A,A5)&1》格式选红色
单元格输入
我想在A1单元格内输入1而A1自动会乘1000。格式寫為: #"000"
工具—选项—编辑—自动设置小数点：-3
大量0值输入超级技巧
在单元格中输入“=450**3”会等于450000
单元格 =45**N 时出现 45000
任一数字**N ，数字后面的**N 表示加 N 个零
如何在C列中输入工号在D列显示姓名
比如在A、B列中建立了工号对应的姓名，如何在C列中输入工号在D列显示姓名。
假设你的数据区域在A1：B100，A列为工号，B列为姓名，C列为要输入的工号，D列输入以下公式：d1=vlookup(C1,$a$1:$b$100,2,false)
输入提示如何做
输入提示是怎么做出来的，好像不是附注吧!
用数据有效性中的输入信息功能就可实现自动跟踪。
“数据&有效性&输入信息”。
在信息输入前就给予提示
在单元格输入信息时，希望系统能自动的给予一些必要的提示，这样不但可以减少信息输入的错误，还可以减少修改所花费的时间。请问该如何实现？
答：可以按如下操作：首先选择需要给予输入提示信息的所有单元格。然后执行“数据”菜单中的“有效性”命令，在弹出的对话框中选择“输入信息”选项卡。接着在“标题”和“输入信息”文本框中输入提示信息的标题和内容即可。
提示显示在屏幕的右上角，离左边的单元格太远，一般人注意不到，达不到提示的目的。如何设置让提示跟单元格走？
数据有效性
只能输入以"杨"开头的字符串,或者是含有"龙"的字符串& &
=OR(LEFT(D35,1)="杨",NOT(ISERROR(FIND("龙",D35))))
简化=(Left(a1)="杨")+Countif(a1,"*龙*")
=(LEFT(A:A)="a")+COUNTIF(A:A,"*b*")
发表评论：
馆藏&10408
TA的推荐TA的最新馆藏[转]&[转]&[转]&[转]&[转]&[转]&[转]&[转]&}
查看: 42067|回复: 20
Excel电子表格计算公式
主题帖子积分
快乐博彩 赢在乐彩
精华8注册时间
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$1,(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))
Excel电子表格计算公式使用方法25条公式技巧总结(2)时间: 11:39来源:网络 作者:不详点击:4794次
16、如何隐藏单元格中的0 单元格格式自定义0;-0;;@ 或 选项》视图》零值去勾。呵呵，如果用公式就要看情况了。 17、多个工作表的单元格合并计算 =Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4) 18
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）、自定义函数
Public Function name()
Dim filename As String
filename = ActiveWorkbook.name
name = filename
End Function
19、如何获取一个月的最大天数
:&=DAY(DATE()-1)&或&=DAY(B1-1)&,B1为&
数据区包含某一字符的项的总和,该用什么公式
=sumif(a:a,&*&&&某一字符&&&*&,数据区)
最后一行为文本：
=offset($b$1,MATCH(CHAR(65535),b:b)-1,)
最后一行为数字：
=offset($b$1,MATCH(9.,b:b)-1,)
或者：=lookup(2,1/(b1:b1000&&&&),b1:b1000)
评委打分中，如何去掉两个以上最高分，两个以上最底分，求剩余人员的平均分?同时显示出被去掉的分数。
看看trimmean()函数帮助。
被去掉的分数：
最大两个：=large(data,{1;2})
最小两个：=small(data,{1;2})
怎样很简单的判断最后一位是字母
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((A1:A1000)*NOT(MOD(ROW(A1:A1000),2)))
查看字符串字数
=LEN(A1)
求非空单元格数量
公式计算出来的数据，COUNTA不能用的(否则空字符也计算进去了)
=COUNTIF($E$3:$E$65536,&?*&)
动态求和公式,自A列A1单元格到当前行前面一行的单元格求和.
=SUM(INDIRECT(&A1:A&&ROW()-1))
20、比较好用的EXCEL文档修复工具
ExcelRecovery
21、EXCEL开方运算
将8开3次方，可以用这个公式，在单元格中输入 =8^(1/3)
22、单元格中的数据分散对齐
文本格式》全角输入
23、查找工作表中的链接
Ctrl+~ 或编辑》链接
24、如何让空单元格自动填为0
选中需更改的区域》查找》空》替换》0
25、把里的数字转换到Excel
方法有多种，选中》复制》设置输入单元格为文本》选择性粘贴》值
选中》表格转换为文本》粘贴》分列》对分列选项设置为文本
另存为文本文件》EXCEL中打开文本文件》对导入文本对话框进行对应设置
主题帖子积分
快乐博彩 赢在乐彩
精华8注册时间
公式输入方法
1.公式选项板
以成绩等级计算公式“=IF(C2&=85,″A″,IF(C2&=70,″B″,IF(C2&=60,″C″,IF(C2&60,″D″)))”为例，使用“公式选项板”输入公式的步骤是：
(1)选中用来存放计算结果的单元格，本例中为“D2”。
(2)单击公式选项板中的“编辑公式”按钮（即“=”按钮），则表示公式的“=”自动被输入编辑栏。
(3)单击“函数”下拉列表框右端的下拉箭头，从列表中选择“IF”。如果你需要的函数没有出现在列表中，可单击“其它函数”，打开“粘贴函数”对话框寻找。
(4)在打开的“IF”公式选项板（见图1）中输入参数。对本例来说，第一个参数“Logical—test”是一个逻辑判断，须用键盘在其后的输入框内敲入“C2&=85”。第二个参数“Value—if—true”是“C2&=85”为“真”时的计算（判断）结果，也需要键盘在其后的输入框内填入“A”。第三个参数“Value—if—false”是逻辑判断“C2&=85”为“假”时的计算（判断）结果，本例是继续执行下一个IF函数。所以应让光标停留在“Value—if—false”框内，然后单击“函数”框中的“IF”，则一个新的“IF”公式选项板打开，按上面介绍的方法继续输入直至结束。在公式输入过程中，已输入的内容可以在Excel编辑栏中看到。另外，公式选项板可以用鼠标拖动，如果它影响观察，可单击输入框右边的按钮将它折叠起来。
（5）公式输入结束后，单击公式选项板中的“确定”按钮完成计算。
公式选项板是一种向导式输入，用户可以详细了解函数的各个参数、功能和当前的计算结果。当光标停留在参数框中时，还可以看到对参数的描述。其最大优点是输入时不必过多考虑分隔符、单元格引用等细节，用它建立和调试公式不易出错。
2.手工输入
手工输入主要用于套用现成的公式，或输入一些比较简单的公式。它的输入方法是：
（1）选中存放计算结果的单元格。
（2）用鼠标在Excel的编辑栏内单击，然后按照公式的组成（见上例）顺序输入。
（3）公式输入结束后，单击编辑栏左端“输入”(“√”)按钮进行计算。
对于一些非常简单的公式，如两个单元格的数值相减，也可以直接在存放结果的单元格内输入公式（如“=A1－B1”），然后回车计算出结果。
公式使用技巧
1.复制
复制是将公式应用于其它单元格的操作，最常用的有以下几种方法：
(1)拖动复制
操作方法是：选中存放公式的单元格，移动空心十字光标至单元格右下角。待光标变成小实心十字时，按住鼠标左键沿列（对行计算时）或行（对列计算时）拖动，至数据结尾完成公式的复制和计算。公式复制的快慢可由小实心十字光标距虚框的远近来调节：小实心十字光标距虚框越远，复制越快；反之，复制越慢。
(2)输入复制
此法是在公式输入结束后立即完成公式的复制。操作方法是：选中需要使用该公式的所有单元格，用上面介绍的方法输入公式，完成后按住Ctrl键并按回车键，该公式就被复制到已选中的所有单元格。
(3)选择性粘贴
操作方法是：选中存放公式的单元格，单击Excel工具栏中的“复制”按钮。然后选中需要使用该公式的单元格，在选中区域内单击鼠标右键，选择快捷选单中的“选择性粘贴”命令。打开“选择性粘贴”对话框后选中“粘贴”命令，单击“确定”，公式就被复制到已选中的单元格。
2.编辑
(1)打开公式选项板
如果公式是由几个Excel函数组合而成，而你又要用公式选项板修改某个函数的参数，则可用鼠标单击函数名称或括号内的参数，再单击编辑栏左端的“=”按钮，即可打开这个函数的公式选项板。修改完毕后单击“确定”将其关闭。
(2)公式与结果切换
单元格中的公式执行后显示的是计算结果，按Ctrl＋′键（位于键盘左上角），可使公式在显示公式内容与显示公式结果之间切换，方便了公式编辑和计算结果查看。
(3)单元格引用
单元格引用的目的在于确定计算范围。鼠标拖动引用单元格既快捷又不易出错。以SUM函数为例，如果你使用了公式选项板，可让光标停在“Number”框内，然后用鼠标拖过需要计算的单元格。若这些单元格是不连续的，可以按住Ctrl键分别拖过它们。如果你是用手工输入的公式，可用鼠标在SUM字样后的括号内单击，然后按相同方法拖过需要计算的单元格区域，则被引用的单元格区域及其分隔符会自动出现在公式中。
主题帖子积分
快乐博彩 赢在乐彩
精华8注册时间
excel彩票公式技巧,慢慢积A
不连续单元格统计
=SUM(COUNTIF(INDIRECT({&a1&,&c1&,&f1&,&g1&}),3))&&复制时用=SUM(COUNTIF(OFFSET($A1,,{0,2,5,6}),12))｝
不为0的最小数LARGE(D12:F12,COUNTIF(D12:F12,&&0&))
查找数字并引用
=INDEX($A$4A$1477,SMALL(IF(--$E$3E$1477=$N$2,ROW($A$4A$1477),&&),ROW()-4)-3)
& && & 引用区域& && && && && && & 条件
多条件统计
=sum(if(c2:c10=&男&，if(g2:g10=&是&,b2:b10)))数组公式
多条件求和
=SUM((条件1)*(条件2)*IF(ISNUMBER(D110),D110,))
百=INT(B3/100)&&十=INT(MOD(B3,100)/10)& & 个=MOD(B3,10)
大小=if(c3&4,&大&,&小&)
单双=IF(MOD(c3,2),&单&,&双&)
合质=if(or(c3=1,c3=2,c3=3,c3=5,c3=7),&质&,&合&)
复隔中=if(or(c4=c3,c4=d3,c4=e3),&复&,if(or(c4=c2,c4=d2,c4=e2),&隔&,&中&))
邻孤传=if(or(c4=c3,c4=d3,c4=e3),&邻&,if(or(abs(c4-c3)=1,abs(c4-d3)=1,abs(c4-e3)=1),&传&,&孤&))
跨度=max(c3:e3)-min(c3:e3)
类型=if(and(c3=d3,c3=e3,d3=e3),&豹子&,if(and(c3&&d3,c3&&e3,d3&&e3),&组六&,&组三&))
连续出现次数最多的字符
{=INDEX(A:A,MATCH(MAX(FREQUENCY(ROW(1:19),IF(A1:A18&&A2:A19,ROW(1:18),))),FREQUENCY(ROW(1:19),IF(A1:A18&&A2:A19,ROW(1:18),)),))}
=INDEX(A:A,MATCH(MAX(FREQUENCY(ROW(1:19),(A1:A18&&A2:A19)*ROW(1:18))),FREQUENCY(ROW(1:19),(A1:A18&&A$2:A$19)*ROW(1:18)),))
清除重复数字 一=IF(ISERROR(FIND(0,A1)),&&,0)&IF(ISERROR(FIND(1,A1)),&&,1)&IF(ISERROR(FIND(2,A1)),&&,2)&IF(ISERROR(FIND(3,A1)),&&,3)&IF(ISERROR(FIND(4,A1)),&&,4)&IF(ISERROR(FIND(5,A1)),&&,5)&IF(ISERROR(FIND(6,A1)),&&,6)&IF(ISERROR(FIND(7,A1)),&&,7)&IF(ISERROR(FIND(8,A1)),&&,8)&IF(ISERROR(FIND(9,A1)),&&,9)
二=(IF(COUNT(FIND(0,A1)),0,)&SUBSTITUTE(SUM(IF(ISNUMBER(FIND(ROW($19),A1)),ROW($19))*10^(9-ROW($19))),&0&,))
三=(IF(COUNT(FIND(0,A1)),0,))&SUBSTITUTE(SUM((MID(A1,FIND(ROW($19),A1&5^19),1)&0)*10^(9-ROW($19))),0,)
四=(IF(COUNT(FIND(0,A1)),0,)&SUBSTITUTE(SUM((1-ISERR(FIND(ROW($19),A1)))*ROW($19)*10^(9-ROW($19))),0,))
五(不变顺序)=RIGHT(TEXT(SUM(MID(A1,SMALL(--TEXT(FIND(ROW($110)-1,A1&&&),&[&=&&LEN(A1)&&]#;1&),ROW($110)),1)*10^(10-ROW($110))),REPT(&0&,10)),COUNT(FIND(ROW($110)-1,A1)))
说明:5^19=25 其中包含了0～9这10个数字。find(row(1:9),A1&5^19),如果A1中不包含某个数字，find的结果也不会返回错误值，而是返回一个大于len(A1)的数字。
去重复内容按列输出
=IF(ROW(1:1)&SUM(1/COUNTIF($A$1A$18,$A$1A$18)),&&,INDEX($A$1A$18,SMALL(IF(MATCH($A$1A$18,$A$1A$18,0)=ROW(INDIRECT(&1:&&ROWS($A$1A$18))),MATCH($A$1A$18,$A$1A$18,)),ROW(1:1))))
=IF(LEN(SMALL(IF(IF(MATCH(C5:K5&&&,C5:K5&&&,)&=COUNT(C5:K5),MATCH(C5:K5&&&,C5:K5&&&,))=COLUMN(A5:I5),C5:K5,4^8),COLUMN(A5:I5)))=5,&&,SMALL(IF(IF(MATCH(C5:K5&&&,C5:K5&&&,)&=COUNT(C5:K5),MATCH(C5:K5&&&,C5:K5&&&,))=COLUMN(A5:I5),C5:K5),COLUMN(A5:I5)))
=IF(COLUMN(A1)&COUNT(1/FREQUENCY($C5K5,$C5K5)),&&,SMALL(IF(IF(MATCH($C5K5&&&,$C5K5&&&,)&=COUNT($C5K5),MATCH($C5K5&&&,$C5K5&&&,))=COLUMN($A5I5),$C5K5),COLUMN(A5)))
columns(&a:z&).delete& &&&或Range(&e3:e65536&).ClearContents
统计不连结单元的含某数的个数
SUM(COUNTIF(OFFSET($D11,{0,1,2,3,4},{0,1,2,3,4}),2))
统计两组数的相同数个数
=SUM(IF(COUNTIF(A2:F2,H2:T2)&0,1/COUNTIF(H2:T2,H2:T2)))& &或=SUM(--(COUNTIF(H2:T2,A2:F2)&0))
有效数据行数[c65536].End(xlUp).Row
=IF(a1=&&,&&,IF(COUNTIF(b1:d1,目标值)=0,SUM(e1,1),&●&))
余码=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(&&,MID(A1,1,1),),MID(A1,2,1),),MID(A1,3,1),)复制代码=IF(ISNUMBER(FIND(0,F1)),0,&&)&SUBSTITUTE(SUMPRODUCT(ISNUMBER(FIND({1;2;3;4;5;6;7;8;9},F1))*{1;2;3;4;5;6;7;8;9}*10^(9-ROW($1:$9))),0,)复制代码
最大连出=MAX(FREQUENCY(IF(个数,ROW(个数)),IF(个数=0,ROW(个数))))
最大连空=MAX(FREQUENCY(IF(个数=0,ROW(个数)),IF(个数,ROW(个数))))
=MAX(FREQUENCY(IF($F$4F$103=&●&,ROW($F$4F$103)),IF($F$4F$103&&&●&,ROW($F$4F$103))))
最大连出=MAX(FREQUENCY(IF((N$3:N$20=N$2)*($H$3H$20=N$2),ROW(N$3:N$20)),IF(N$3:N$20&&N$2,ROW(N$3:N$20))))& & 数组公式
倒序排列
=IF(ISERROR(FIND(&9&,A1)),&&,&9&)&IF(ISERROR(FIND(&8&,A1)),&&,&8&)&IF(ISERROR(FIND(&7&,A1)),&&,&7&)&IF(ISERROR(FIND(&6&,A1)),&&,&6&)&IF(ISERROR(FIND(&5&,A1)),&&,&5&)&IF(ISERROR(FIND(&4&,A1)),&&,&4&)&IF(ISERROR(FIND(&3&,A1)),&&,&3&)&IF(ISERROR(FIND(&2&,A1)),&&,&2&)&IF(ISERROR(FIND(&1&,A1)),&&,&1&)&IF(ISERROR(FIND(&0&,A1)),&&,&0&)复制代码组六组三豹子：
=IF(B2=&&,&&,IF(AND(B2&&C2,B2&&D2,C2&&D2),&组六&,IF(AND(B2=C2,B2=D2,C2=D2),&豹子&,&组三& )))复制代码
主题帖子积分
快乐博彩 赢在乐彩
精华8注册时间
excel数据引用分为四种，我们给大家简单介绍下：
1、excel数据相对引用。这种最为简单，通常大家都知道，就是普通的公式计算。简单介绍几个公式，相信大家都很清楚。
1）、在d2单元格中需要计算：数据一和数据二的平均数与数据三的差。就可以输入
=(A2+B2)/2-C2
=sum(A2:B2)/2-C2
其中，括号必须是英文状态或者是半角中文状态，其中的列号ABC输入大小写没有关系，都会自动变成大写。如果求和的数据过多则不要用加号了，用sum求和函数较为方便。（求括号内英文冒号两侧的单元格之间的数据之和）sum函数不但可以求横向的行之和、竖向的列之和，还可以求对角线所辖区域的矩形区域之和。
2）、计算：数据一、数据二、数据三的最大值与最小值的差，可以输入：
=max(a2:d2)-min(a2:d2)
其中，max是求最大值函数，min是求最小值函数。和sum一样，这两个函数都支持对角线所辖区域矩形的数据运算。
3）、计算：如果数据一大于1500，则综合数据是数据一、数据二、数据三求和，若数据一介于之间则计算数据一和数据二之和，若数据一低于1000，则只记录数据一的结果。（这个例子在公司计算工资的时候很常用，但是要非常小心边界的判断，需要的时候可以用&=表示大于等于，用&=来表示小于等于）关于这个if判断语句的写法要非常小心括号的运用，我们可以在有图早期的一篇
中看到多次嵌套的运用。
=IF(A2&1500,SUM(A2:C2),IF((A2&1000),A2,SUM(A2:B2)))
这三个例子主要是介绍大家常见的三种模式：直接运算，用excel的函数进行运算，用程序语句进行运算。（这种提法是我自己随便想的，不一定有真正的三中模式之说。）
2、excel数据的绝对引用。参加了办公自动化培训班的朋友和自学excel的朋友应该对他不陌生。举例说明一下，还以上面的那些数据为例：
计算数据一中每个数据与最大值的差。（呵呵，也很常用的，就是计算平级的人或者部门之间的差距）
我们一般会在d2单元格输入
=MAX(A2:A6)-A2
可是我们无法通过拖动，生成但d3、d4、d5等下面单元格的数据，当我们拖动的时候，在d3单元格变成了
=MAX(A3:A7)-A3
这显然不是我们要的，我们在d3单元格想要的是
=MAX(A2:A6)-A3
所以，我们可以使用$来固定那些需要不变的数据，如上面a2单元格的公式就可以写成
=MAX(A$2:A$6)-A2
随你怎样拖动，max计算的区域就不会变化了。如果你想固定的更牢靠一点，可以在列标号前加$。
3、同文件内excel表间引用。同文件内不同表之间的数据引用和计算通常是使用英文状态的引号表名结合而来的，例如本例中：
引用表1当中的a列数据，可以在a2单元格输入
其中英文单引号中为表的名字，表和单元格名称间用英文的!分割开来。
4、不同文件间的excel引用。使用英文的中括号 [ ]来引用文件，如我们将正在操作的文件保存到d盘，默认名字为book1.xls 新建一个excel文件，引用book1.xls文件中第一列的数据：
=[book1.xls]1!a2
此例和上面的例子中英文的单引号 '&&可要可不要，不过excel默认都会给加上，另外本例中，当我们输入完公式，打回车键之后，excel会自动加上文件地址，如本例中可能会变为：
='d:\[book1.xls]1'!a2
主题帖子积分
快乐博彩 赢在乐彩
精华8注册时间
主题帖子积分
精华29注册时间
本帖最后由 轉裑ｄē距蘺 于
20:33 编辑
假设你在B2输入数据，C2为报警格 ，输入
=if(b2=&&,&&,if(b2&=50,&不足&,if(b2&=100,&库存过多&,&&)))
=IF($J187=&&,&&,LOOKUP($J187,{0,1,2,3,4,5,6,7,8,9},{&0159&,&0126&,&1237&,&2348&,&3459&,&0159&,&0126&,&1237&,&2348&,&3459&}))
=IF($A3=&&,&&,3-SUMPRODUCT(--ISERROR(FIND($R3:T3,AJ3))))
=IF(ISNUMBER(FIND(0,E16)),0,&&)&SUBSTITUTE(SUMPRODUCT(ISNUMBER(FIND({1;2;3;4;5;6;7;8;9},E16))*{1;2;3;4;5;6;7;8;9}*10^(9-ROW($1:$9))),0,)
=LEFT(TEXT(B108,&00000&),3)
=RIGHT(TEXT(B113,&00000&),3)
主题帖子积分
快乐博彩 赢在乐彩
精华8注册时间
excel 统计有数据单元格的数量:
=COUNTA(A1:A20)
=ROWS(A1:A20)-COUNTBLANK(A1:A20)
主题帖子积分
六段, 积分 6307, 距离下一级还需 3293 积分
六段, 积分 6307, 距离下一级还需 3293 积分
精华3注册时间
轉裑ｄē距蘺
管理员:你好!
你在这里写了同文件不同表间的单元格的引用问题,你是这样写的:
&3、同文件内excel表间引用。同文件内不同表之间的数据引用和计算通常是使用英文状态的引号表名结合而来的，例如本例中：
引用表1当中的a列数据，可以在a2单元格输入
其中英文单引号中为表的名字，表和单元格名称间用英文的!分割开来。&
& &&&这个我能明白,我的问题是:同文件不同表间的跨行单元格的引用时有附加条件时怎样表达?比如:
1.& &B和C表的A列都是表示开奖期号,B表是记录连续号开奖号表,C表是记录星期日的开奖号码表,因此,C表每行数据都与B表隔2行!但为了一致,其期号都统一用连续号的期号,这时候应怎样写引用公式呢?
2.& &特别是当引用的列不相同时又怎样表达呢?最近为这个问题我在贵网上专门发贴请教,但到目前为止尚无应答!
请你解答,谢谢!!
主题帖子积分
六段, 积分 6307, 距离下一级还需 3293 积分
六段, 积分 6307, 距离下一级还需 3293 积分
精华3注册时间
轉裑ｄē距蘺
& & 轉裑ｄē距蘺 管理员:
& && && &妳好!首先谢谢妳的关注!更感谢推背图应承帮我修改表格公式!
我在问题求助栏发表了专题的,也上传了文件和截图的,下面是链接:
请教:Excel同表不同表单间的跨行跨列数据引用计算公式(8楼文件最好!)
在上传过程中,有网管说有病毒,经我查杀,无病毒,请放心下载,解压!
在下载,解压时请关闭360实时监控,它容易将所有含宏的office文件当病毒文件处理!!
经我试验,下载时最好不要间断,流畅下载对文件解压最好!
主题帖子积分
五段, 积分 3085, 距离下一级还需 1715 积分
五段, 积分 3085, 距离下一级还需 1715 积分
精华2注册时间
本帖最后由 爱彩必中 于
10:55 编辑
如图，篮色部份的数字我想让其自动显示，怎么做？请教！
五一劳动勋章
国庆66周年纪念勋章
五一劳动勋章
五一劳动奖章
乐彩荣誉勋章
乐彩荣誉勋章
社区活动先锋
社区活动先锋
乐彩荣誉勋章
乐彩荣誉勋章
添加安全提问领取安全达人勋章
十年纪念勋章
验证邮箱获得十年纪念勋章
服务我国公益事业，将乐彩进行到底
&&&本社区所有言论，纯属发表者个人意见，与乐彩网立场无关Excel常用函数公式及技巧（5）
Excel常用函数公式及技巧（5）
Excel常用函数公式及技巧（5）
28.如何分班统计男女人数
男=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=F$1))
女=SUMPRODUCT(($B$2:$B$446=$E2)*($C$2:$C$446=G$1))
男｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$F$1))｝
女｛=SUM(($B$2:$B$446=$E2)*($C$2:$C$446=$G$1))｝
男｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$G$1)*$D$2:$D$446)｝
女｛=SUM(($B$2:$B$446=F2)*($C$2:$C$446=$H$1)*$D$2:$D$446)｝
增加d列，输入公式：=B2&C2，合并数据后再利用countif公式对D列统计。
=COUNTIF($B$2:$B$446,E2)
29.在几百几千个数据中发现重复项
我的意思不是查找功能,那个我会用,比如有几百个人的名字输入单元格中,但我面对那么多名字真无法短时间内看出谁重复了,该如何办?
假设判断区域为A1:D10,格式/条件格式，选公式(不是数值)，输入：
=COUNTIF($A$1:$D$10,A1)&1
然后在格式中设置一个字体或图案颜色,确定,这样重复数据就变成了有色单元格。
30.统计互不相同的数据个数
例如,在 3 * 3 的区域中统计互不相同的数据个数,
结果应为 4 (4 个互不相同的数据)
数组公式=sum(1/countif(a1:c3,a1:c3))
还可以公式:
=COUNT(IF(FREQUENCY(A1:C3,A1:C3),1))
31.多个工作表的单元格合并计算
=Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4)
32.单个单元格中字符统计
假设 A1单元格中有数据"sdfsfjksfhweofiefondsfljsdfisdofjei"如何用公式统计出A1单元格中有多个不重复的字符?
=SUMPRODUCT(--(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(97:122)),""))=1))
数组公式=SUM(IF(ISERROR(FIND(CHAR(ROW(97:122)),A1)),,1))
这个公式只适用单元中的字符为小写字母,给个通用点的
=SUM(--(MATCH(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),)=ROW(INDIRECT("1:"&LEN(A2)))))
=SUM(IF(ISERROR(FIND(CHAR(ROW(97:122)),LOWER(A1))),,1))
33.数据区包含某一字符的项的总和,该用什么公式
=sumif(a:a,"*"&"某一字符"&"*",数据区)
34.函数如何实现分组编码
对数值进行分组编码
=A2&TEXT(COUNTIF($A$2:A2,A2),"00")
十三、【数值取整及进位】
1.取整数函数
907.5；1034.2；1500要改变为908；1035；1500公式为：
=CEILING(A1,1)
907；1034；1500要改变为910；1040；1500公式为：
=CEILING(A1,10)
如果要保留到百位数，即改变为1000；1100；1500公式为：
=CEILING(A1,100)
2.数值取整
在单元格中要取整数（只取整数不用考虑四舍五入）用什么函数呀？例如：10/4只要显示2就可以了！要考虑负数的因数呢？例如：(-10/4)要显示－2而不是－3？怎么办？
=TRUNC(A1,0)
=ROUNDDOWN(A1,0)
3.求余数的函数
比如：A1=28，A2=（A1÷6）的余数=4，请问这个公式怎么写？&
解答：=MOD(28,6)
4.四舍五入公式
=ROUND($B$1*A1,2)
=ROUND(B1*A1,2)
=round(a1,0)
=round(a1,0)*0.95
5.对数字进行四舍五入
对于数字进行四舍五入,可以使用INT（取整函数），但由于这个函数的定义是返回实数舍入后的整数值。因此，用INT函数进行四舍五入还是需要一些技巧的，也就是要加上0.5，才能达到取整的目的。公式应写成：
=INT(B2*100+0.5)/100
6.如何实现“见分进元”
在我们的工资中，有一项“合同补贴”，只要计算结果出现“分”值就在整数“元”进一位，也就是说3.01元进到4.00元，3.00元不变，整数“元”不变。
=IF((A3-INT(A3))&=0.3,IF((A3-INT(A3))&=0.8,1,0.5),0)+INT(A3)
＝IF(RIGHT(FIXED(A1,2),2)&B1,TRUNC(A2)+1,A2)
说明一下：A1即是要转换的目标；B2输入00（文本格式，必须是00这两个数） 。
＝IF(INT(A1)&&A1,INT(A1)+1,A1)
=ROUNDUP(A1,0)
=CEILING(A9,1)
=INT(A9+1)
7.四舍五入
如何将Excel 中的数据，希望把千位以下的数进行四舍五入，例如：3245& 希望变成3000；3690 希望成为400
=ROUND(C6*D6,2)
=ROUND(A2*0.001,)*1000
=ROUND(A2,-3)
=--FIXED(A2,-3)
=ROUND(A2/00
8.如何四舍五入取两位小数
如何四舍五入取两位小数,如2.145为2.15,0.1449为0.14.=ROUND(A1,2)
9.根据给定的位数，四舍五入指定的数值
对整数无效。四舍五入B234的数值，变成小数点后一位。
&&&& 12512.3
=ROUND(B23,1)
10.四舍六入
=IF(MOD(INT(A1),2)=0,IF(MOD(A1,1)=0.5,INT(A1),INT(A1+0.5)),INT(A1+0.5))
=IF(AND(RIGHT(A1*100,1)="0",RIGHT(A1*10,1)="5")=TRUE,IF(INT(A1)/2=INT(INT(A1)/2),INT(A1),ROUND(A1,0)),ROUND(A1,0))
AND(RIGHT(A1*100,1)="0",RIGHT(A1*10,1)="5")=TRUE 判断是否为一位小数,且是0.5，如果不符合上术要条件,按普通四舍五入法则处理,否则判断整数部分的奇偶。
=IF(RIGHT(A1,1)*1&5,INT(A1),IF(RIGHT(A1,1)*1&5,INT(A1)+1,IF(MOD(ROUND(A1,),2)=0,ROUND(A1,),ROUNDDOWN(A1,))))
=IF(ROUNDUP(A1*2,)=A1*2,IF(MOD(ROUND(A1,),2)=1,ROUNDDOWN(A1,),ROUNDUP(A1,)),ROUND(A1,))
11.如何实现2舍3入
做工资时，常遇到：3.2元要舍去0.2元变为3.00元,而3.3元要把0.3元入为0.5元变为3.5元.请教,该如何实现?
=ROUND(A1*2,0)/2
=CEILING(A1,0.5)
=IF((A1-INT(A1))&=0.2,INT(A1),IF((A1-INT(A1))&=0.5,INT(A1)+0.5,IF((A1-INT(A1))&=0.7,INT(A1),INT(A1)+1)))
=CEILING(A1-0.2,0.5)
=FLOOR(A1+0.2,0.5)
12.怎么设置单元格以千元四舍五入
比如输入123456，显示出来123，000=CEILING(ROUND(A1/)*1000
=round(a1,-3)
=mround(A1,1000)
13.ROUND函数的四舍五入不进位的解决方法？
计算一：A2=1345.3& B2=1232.4& C3=A2-B2=112.9&& D=0.05& E=ROUND(B2*D2,2)=5.64 &（计算结果为5.645，此运算没有进位）。
计算二：A2=1225.4& B2=1112.5& C3=A2-B2=112.9&& D=0.05& E=ROUND(B2*D2,2)=5.65（计算结果为5.645，此运算进位）。
以上两式中C3结果都为112.9，而为什么应用ROUND函数后结果却不一样。
请教高手有什么函数能保证四舍五入不会出错。
可将C列先变成文本性数据，再进行后面的运算，以达到计算的目的。
如：C列可改成C1=TRIM(A1-B1)，以此类推，只要是更改成文本性数据就行。
14.保留一位小数
我需要保留一位小数,不管后面是什么数字,超过5或不超过5,都向前进一位.
例如:329.99--&330.00
329.84-----&329.90
329.86-----&329.90
=roundup(*,2)或=round(a1+0.04,1)
15.如何三舍四入
=round(原数值+0.001,2)
16.另类四舍五入
我用Excle给别人算帐，由于要对上百家收费，找零却是个问题。于是我提出四舍五入，收整元。但是领导不同意，要求收取0.5元。例如：某户为123.41元，就收123.50元;如果是58.72元，就收58.5元。这可难坏了我。经过研究，我发现，可以在设置单元格中，设成分数，以2为分母，可以解决问题。但是打印出来的却是分数不好看，而且求和也不对。请各位高手给予指点。是这样的，如果是57.01元，则省去，即收57.00元；如果是57.31元，则进为57.50元；如果是57.70元，也收57.50元；要是57.80元，则收58.00元。
假设数据在A1
=INT(A1)+IF((A1-INT(A1)&=0.3),0,IF((A1-INT(A1)&0.7),1,0.5))
简化一下：
=INT(A1)+0.5*((A1-INT(A1)&0.3)+(A1-INT(A1)&0.7))
int函数取整数部分，A1-int(A1)取小数部分，根据你的意思：&=0.3按0算，0.3~0.7(含)按0.5算，0.7~0.99……按+1算
则：第一个公式不难理解了
简化公式中：“*((A1-INT(A1)&0.3)+(A1-INT(A1)&0.7))”即(小数部分&0.3)+(小数部分&0.7)
我们知道这是省略if的判断语句，条件为真返回true（也就是1）否在为false（0），那么如果小数&=0.3，则两个条件都为0，即整数部分+0.5*0=整数部分，介于0.3~0.7，则为整数部分+0.5*（1+0），大于0.7肯定也大于0.3啦，则为整数部分+0.5*（1+1）。
请问，如果是由几个分表汇总的总表想如此处理，该如何做。
例：e112位置=SUM(一库入库!G112,二库入库!G112,四库入库!G112,保健酒基地入库!G112,下陆仓库入库!G112)
汇总的结果为100.24，而我要求如果小数为24的话自动视为1累加，否则不便。
就是小数为0.24才加1，否则都舍掉？
若是：=if（sum公式-int（sum公式）=0.24，int（sum公式）+1，sum公式）
17.想把小数点和后面的数字都去掉，不要四舍五入
比如：&&&&&&&&
12.30&&& 变成&&&&& &&&12.00
45.32&&&&&&&&&&&&&&&& 45.00
25.38&&&&&&&&&&&&&&&& 25.00
6.54&&&&&&&&&&&&&&&&&& 6.00
13.02&&&&&&&&&&&&&&&& 13.00
59.68&&&&&&&&&&&&&&&& 59.00
23.62&&&&&&&&&&&&&&&& 23.00
=Rounddown(A1,0)
你要把A1换成你要转换的那个单元格啊，然后拖动就可以了！
我那里用的那个A1只是告诉你一个例子而已，你要根据你的实际情况来修改一下才能用的。
=INT(A1)=TRUNC(A1,0)
18.求真正的四舍五入后的数
请教如何在Excel中，求“金额合计”（小数点后二位数）时，所取的数值应是所求单元格中写的数字（四舍五入后的数字），而不是（四舍五入前）的数字。因为只有这样行和列及关联的工作表才能对得上，例如：表上的数值分别是：(1.802/2=0.901)0.90(A1); (1.604/2=0.802)0.80(A2);&&(1.406/2=0.703)0.70(A3);（因取小数点后二位）。合计数(A4)表中自己计算和显示是：(0.901+0.802+0.703=2.406)2.41（四舍五入后的数值）。但照表中的数值人工计算却是：(0.9+0.8+0.7=)2.4,有矛盾，还有许多例子，故请教各高手，如何设置公式，使得人工计算结果同表中一致。请指教。十分感谢！
工具》选项》重新计算》以显示精度为准& &前打钩
也可以用函数 ROUND() 使结果四舍五入 。如ROUND（算式，2）代表保留两位小数，如ROUND（算式，1）代表保留一位小数。
19.小数点进位
小数点进位如何把1.4进成2或1.3进成2=Ceiling(A1,1)
=Roundup(A1,0)
=INT(A1+0.9)
&=int(a1)+1
如何把1.4进成2，而1.2不进位
=ROUND(A1+0.1,0)
20.个位数归0或者归5
A*B后想得到C的结果值，用什么函数比较好
A&&&&&&&&& B&&&&&&&& C(想得到的数值)
320&&&&& &&1.1&&&&&&&&&&&&& &355
1140&& &&&&1.2&&&&&&&&&&&&& &1370
50&&&&&&& &1.3&&&&&&&&&&&&&& 65
16&&&&&&& &1.4&&&&&&&&&&&&&& 25=FLOOR(A1*B1+5*(MOD(A1*B1,5)&&0),5)
=CEILING(A1*B1,5)
十四、【大小值或中间值】
1.求平均值
如在列中有一组数字：10、7、9、27、2
=AVERAGE(A2:A6) 上面数字的平均值为11
行公式=AVERAGE(B2:D2)
2.如何实现求平均值时只对不等于零的数求均值？
＝AVERAGE (IF(A1:A5&0,A1:A5))
3.平均分的问题
假设一个班有60人，要统计出各个学科排名前50的学生的平均分，用公式应该如何写？如果用排序再来算的话很麻烦，能不能直接用公式找出前50名进行计算？
{=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:50"))))}
4.怎样求最大值（最小值或中间值）=IF(A2="","",MAX(OFFSET(C2,,,MIN(IF(A3:$A$15&&"",ROW(3:$15),15))-MAX(($A$2:A2&&"")*ROW($2:2)))))
=IF(A2="","",MAX((LOOKUP(ROW($A$2:$A$14),IF($A$2:$A$14&&"",ROW($A$2:$A$14)),$A$2:$A$14)=A2)*$C$2:$C$14))
=IF(A2="","",LOOKUP(2,1/FIND(A2,$B$2:$B$1000),$C$2:$C$1000))
=IF(A2="","",MAX(IF(ISNUMBER(FIND(A2,$B$2:$B$1000)),$C$2:$C$1000)))
5.平均数怎么弄
如在列中有一组数字：10、7、9、27、2
=AVERAGE(A2:A6) 上面数字的平均值为11
=AVERAGE(A2:A6,&5)&上面数字与&5&的平均值为10
6.去掉其中两个最大值和两个最小值的公式
我要将一行数据进行处理。要去掉其中两个最大值和两个最小值，不知道怎样运用公式，应该是：
=SUM(A1:A50)-MAX(A1:A50)-LARGE(A1:A50,2)-MIN(A1:A50)-SMALL(A1:A50,2)&
这个只能减去1个最大和1个最小值，不符合题意。可用下面的公式。
=SUM(A1:A20)-SUM(LARGE(A1:A20,{1,2}))-SUM(SMALL(A1:A20,{1,2}))
7.去一行最高分最低分求平均值
去一行中一个最高分和一个最低分求平均值
公式为：=(SUM(A5:E5)-MAX(A5:E5)-MIN(A5:E5))/(COUNTIF(A5:E5,"&0")-2)
但另用TRIMMEAN ()函数较好。=TRIMMEAN($A$5:$E$5,2/COUNT($A$5:$E$5))
为需要进行整理并求平均值的数组或数值区域。TRIMMEAN(array,percent)
为计算时所要除去的数据点的比例，例如，如果 percent = 0.2，在 20 个数据点的集合中，就要除去 4 个数据点 (20 x 0.2)：头部除去 2 个，尾部除去 2 个。
用活了TRIMMEAN函数，这个问题易如反掌。
8.在9个数值中去掉最高与最低然后求平均值
假设9个数值所在的区域为A1：A9
=(SUM(A1:A9)-MAX(A1:A9)-MIN(A1:A9))/7
=TRIMMEAN(A1:A9,2/COUNTA(A1:A9))
=TRIMMEAN(A1:A9,2/9)
｛=AVERAGE(SMALL(A1:A9,ROW(2:8)))｝
=ROUND((SUM(A1:A9)-MAX(A1:A9)-MIN(A1:A9))/(COUNT(A1:A9)-2),3)
=TRIMMEAN(A1:A9,0.286)
9.求最大值（n列）
{=MAX(($A$2:$A$16=$D$2)*($B$2:$B$16))}｛=LARGE(IF(FREQUENCY(N3:AT3,N3:AT3),TRANSPOSE(N3:AT3)),ROW(A1))｝
｛=LARGE(IF(FREQUENCY(TRANSPOSE(N3:AT3),TRANSPOSE(N3:AT3)),(N3:AT3)),ROW(A1))｝
10.如何实现求平均值时只对不等于零的数求均值？
= TRIMMEAN (IF(A1:A5&0,A1:A5))
11.得到单元格编号组中最大的数或最小的数
对字符格式的数字不起作用。
=MAX(B16:B25)
=MIN(B16:B25)&& （得到最小的数的公式）
12.标记出3个最大最小值
=RANK(B4,$B4:$Q4)+COUNTIF($B4:B4,B4)&=4
=RANK(B4,$B4:$Q4,2)+COUNTIF(B4:$Q4,B4)&=4
=(COUNTIF($B3:$Q3,"&"&B3)+COUNTIF($B3:B3,B3))&=3
=(COUNTIF($B3:$Q3,"&"&B3)+COUNTIF(B3:$B3,B3))&COUNT($B3:$Q3)-3
=SMALL(rongjun!$C4:$R4+COLUMN(rongjun!$C4:$R4)/1,3})
=LARGE(rongjun!$C4:$R4+COLUMN(rongjun!$C4:$R4)/1,3})
=RANK(B8,$B8:$Q8)+COUNTIF($B8:B8,B8)-1&=3
=RANK(B8,$B8:$Q8)+COUNTIF($B8:B8,B8)-1&COUNT($B8:$Q8)-3
＝C4+COLUMN(C4)/10000&LARGE(rongjun!$C4:$R4+COLUMN(rongjun!$C4:$R4)/10000,4)
13.取前五名，后五名的方法
{=LARGE(IF(ISERROR($D$2:$D$57),0,$D$2:$D$57),ROW())}
{=SMALL(IF(ISERROR($D$2:$D$57),0,$D$2:$D$57),ROW())}
{=LARGE(IF(ISERROR(D$2:D$57),"",D$2:D$57),ROW(1:5))}
{=SMALL(IF(ISERROR(D$2:D$57),"",D$2:D$57),ROW(1:5))}
=LARGE(B$2:B$57,ROW(A1))
=SMALL(B$2:B$57,ROW(A1)+COUNTIF(B$2:B$57,0))
=LARGE(D$2:D$57,ROW(A1))
=SMALL($D$2:$D$57,5-MOD(ROW(A5),5))
14.如何用公式求出最大值所在的行？
如A1：A10中有10个数，怎么求出最大的数在哪个单元格？
=MATCH(LARGE(A1:A10,1),A1:A10,0)
=ADDRESS(MATCH(SMALL(A1:A10,COUNTA(A1:A10)),A1:A10,0),1)
=ADDRESS(MATCH(MAX(A1:A10,1),A1:A10,0),1)
{=ADDRESS(MATCH(MAX(LEN(A1:A10)),LEN(A1:A10),FALSE),1)}
{=ADDRESS(SUM(($A$1:$A$10=MAX($A$1:$A$10))*(ROW($A$1:$A$10))),SUM(($A$1:$A$10=MAX($A$1:$A$10))*(COLUMN($A$1:$A$10))))}
15.如有多个最大值的话呢？如何一一显示其所在的单元格？
{=IF(ROW(1:1)&=COUNTIF($A$1:$A$100,MAX($A$1:$A$100)),ADDRESS(LARGE(IF($A$1:$A$100=MAX($A$1:$A$100),ROW($A$1:$A$100)),ROW(1:1)),1),"")}
16.求多个最高分
语文成绩有多个最高分,如何用公式的方法把他们抽出来(动态)?B15=INDEX(A:A,SMALL(IF(B$2:B$10=MAX(B$2:B$10),ROW($2:$10),65536),ROW(1:1)))&""
数组公式，按下Ctrl+Shift+Enter结束。
如果增加一个条件,就是在姓名前加一个类别,例如前5个人是A类的,后4个是B类的,请分类找出A类和B类的对应姓名的最高分
=INDEX(B:B,SMALL(IF(C$2:C$10=MAX(IF($A$2:$A$10="A",$C$2:C$10)),ROW($2:$10),IF(C$2:C$10=MAX(IF($A$2:$A$10="B",$C$2:$C$10)),ROW($2:$10),65536)),ROW(1:1)))&""
17.如何求多条件的平均值
应如何求下表中1月份400g重量的平均值
月份& &规格& & 重量
1& && & 400g& &&&401
1& && & 400g& &&&403
2& && & 400g& &&&402
2& && & 400g& &&&404
1& && & 200g& &&&201
1& && & 200g& &&&203
2& && & 200g& &&&202
試試這個行不行=SUMPRODUCT(($A$4:$A$10=1)*($B$4:$B$10="400g"),($C$4:$C$10))/SUMPRODUCT(($A$4:$A$10=1)*($B$4:$B$10="400g"))
比较土的办法
{=SUM(IF(($A$1:$A$7=1)*($B$1:$B$7="400g"),C1:C7,0))/SUM(IF(($A$1:$A$7=1)*($B$1:$B$7="400g"),1,0))}
数组公式：{=AVERAGE(IF(B2:B8="400g")*(A2:A8=1),(C2:C8),""))}
另一个数组公式试试：=Average(if((a1:a10=1)*(b1:b10="400g"),c1:c10))
=SUMIF(B1:B7,B1,C1:C7)/COUNTIF(B1:B7,B1)&&& 这个也可以
18.想求出第三大之数值
如A1：A4分别为1,2,2,3.&
想求出第三大之数值"1",应如何设公式。 &&
=large(if(frequency(a1:a4,a1:a4),a1:a4),3)
数组公式的解法
=LARGE((MATCH(A1:A10,A1:A10,)=ROW(1:10))*A1:A10,3)
&十五、【查询和查找引用】
1.查找顺序公式
=LOOKUP(2,1/(A1:A20&&0),A1:A20)
=MATCH(7,A1:A20)
=VLOOKUP(7,A1:B11,2)
2.怎样实现精确查询
=VLOOKUP(B11,B3:F7,4,FALSE)
=LOOKUP(B11,B3:B7,E3:E7)
用MATCH+INDEX
=INDEX(E3:E7,MATCH(B11,B3:B7,0))
用INDIRECT+MATCH
=INDIRECT("E"&MATCH(B11,B3:B7,0)+2)
用OFFSET+MATCH
=OFFSET(E3,MATCH(B11,B3:B7,0)-1,0)
用INDIRECT+ADDRESS+MATCH
=INDIRECT(ADDRESS(MATCH(B11,B4:B7,0)+3,5))
用数组公式
=INDEX(E1:E7,MAX(IF((B4:B7=B11),ROW(B4:B7),0)))
3.查找及引用
如何查找并引用B2单元格中所显示日期当日的相应代码的值。
B3=IF(COUNTIF($E$3:$E$20,A3),VLOOKUP($A3,$E$2:$M$20,MATCH(B$2,$F$2:$M$2,)+1,),"")
4.查找函数的应用
我想在A5输入表的名称，B5自动跳出该表中B列的最后一个有效数值，请问B5的公式该如何设定？ =LOOKUP(9E+307,INDIRECT(A5&"!"&"B:B"))
B2 =IF(A2="","",LOOKUP(9E+307,INDIRECT(A2&"!B:B")))
5.怎么能方便的判断某个单元格中包含多少个指定的字符？
例：A1 中是“ASAFAG”，我希望计算出A1里面有多少个“A”......
=LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))
6.如何用查找函数
一、要求: 利用公式从左表中查询相应的地区，结果放在H14单元格
=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单元格）。用数组公式：
=INDIRECT("G"&MAX((D14:D22=C25)*ROW(D14:D22)))
D25 =LOOKUP(2,1/(D14:D22=C25),G14:G22)
7.日期查找的问题
我有一个日期比如:,我想知道它减去一个固定天数比如6后,最接近它的一个星期四(只能提前)是多少号
的答案应该是而不是
日期在A1处，B1处输入：=MAX((WEEKDAY(A1-6-{1,2,3,4,5,6,7},2)=4)*(A1-6-{1,2,3,4,5,6,7}))
B1, 输入公式 :=A1-6-MOD(WEEKDAY(A1-6,2)+3,7)
8.如何自动查找相同单元格内容
=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))
9.查找函数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))
10.怎样对号入座（查找）
=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)
11.一个文本查找的问题
如何在一个单元格中,统计某个字符出现的次数,例如:单元格A1中填有:张三/李四/王五",如何通过公式来计算此单元格中共填有几个人姓名,每个人姓名之间用"/"符号分开,烦请相告.=LEN(A1)-LEN(SUBSTITUTE(A1,"/",))+1
12.查找一列中最后一个数值
我想用公式知道,另一个表中"A"列最下面一个数是多少,就行了.用不定值的,因为还有数据有增加,
=LOOKUP(9E+307,Sheet2!A:A)——最后一个数值
=LOOKUP(REPT("座",255),Sheet2!A:A)——最后一个文本
=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)
13.查找重复字符
&&&& A&&&&&&&&&&&&&&&& &&&&&&&&&& B
1245689&&&&&&& &&&&&&&&0134578
查找单元格Ａ和Ｂ里重复及不重复的字符
正确答案：重复字符－1458
&&& 不重复字符－023679
以下公式对数字有效：
重复数字：
=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,)
不重复数字：
=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结束。
重复数字：
=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,)
不重复数字：
=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,)
14.请教查找替换问题
把表1中字符在4个以上的字段(含4个)查找出来,替换成表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,"="&"????*")))
15.IF函数替换法总结
条件说明：小于10返回500，小于20返回800，小于30返回1100，小于40返回1400，大于40返回1700
类似于以上要求，大家最先想到IF函数，这也本属IF专长。但用IF一般要长长的公式，且计算较慢。现总结一下IF之替换公式，望能抛砖引玉，在我的倡导下各位提供更完善的方案。其中部分公式通用，部分公式有局限性，请看说明。(前18个条件公式，根据速度，排名如下)
1=SMALL({500;800;00},COUNTIF($A$9:$A$13,"&="&A1))
2=INDEX({500;800;00},COUNTIF($A$9:$A$13,"&="&A1))
3=CHOOSE(COUNTIF($A$9:$A$13,"&="&A1),500,800,00)
4=LOOKUP(A1,{0,10,20,30,40},{500,800,00})
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,00},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,00})
12=INDEX({500;800;00},MATCH(A1,{0;10;20;30;40},1))
13=CHOOSE(MATCH(A1,{0;10;20;30;40},1),500,800,00)
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,))))
16=CHOOSE(SUM((A1&={0;10;20;30;40})*1),500,800,00)
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,00)
新增公式：
19=CHOOSE(MIN(INT(A1/(ROW($1:$4)*10))+1,5),500,800,00)
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,00})
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
16.查找的函数（查找末位词组）
（数组公式：）=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,)
（数组公式：）=RIGHT(A2,MATCH(1,FIND(" ",RIGHT(" "&A2,ROW($1:$100))),)-1)
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50))&& （好）
其实这个公式的思路, 是可以变化的，改变REPT( )中的数值, 可以返回, 指定空格位置後的数据，比如：
A1&&=一 二三 四 五 六 七 八 九
10个普通公式, 分别为 :
1=TRIM(RIGHT(SUBSTITUTE(A1,"",REPT("",100)),100)) 返回第0空格位置後的数据&一 二 三 四 五 六 七 八 九
2=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),100)) 返回第8 空格位置後的数据&九3=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",40)),100)) 返回第7 空格位置後的数据&八 九
4=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",30)),100)) 返回第6 空格位置後的数据&七 八九
5=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",23)),100)) 返回第5空格位置後的数据&六 七八 九
6=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",18)),100)) 返回第4 空格位置後的数据&五 六 七 八 九
7=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",14)),100)) 返回第3 空格位置後的数据&四 五 六 七 八 九
8=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",12)),100)) 返回第2 空格位置後的数据&三 四 五 六 七 八 九
9=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",11)),100)) 返回第1 空格位置後的数据&二 三 四 五 六 七 八 九
10=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",9)),100)) 返回第0空格位置後的数据&一 二三 四 五 六 七 八 九
17.怎样从原始数据中自动获取最后一个数据
公式=LOOKUP(1,0/($A$1:$A$100=C2),$B$1:$B$100)
18.两列数据查找相同值对应的位置
=MATCH(B1,A:A，0)
19.查找数据公式两个(基本查找函数为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))
&十六、【输入数据的技巧】
谈谈Excel输入的技巧
在Excel工作表的单元格中，可以使用两种最基本的数据格式：常数和公式。常数是指文字、数字、日期和时间等数据，还可以包括逻辑值和错误值，每种数据都有它特定的格式和输入方法，为了使用户对输入数据有一个明确的认识，有必要来介绍一下在Excel中输入各种类型数据的方法和技巧。
【1】输入文本
Excel单元格中的文本包括任何中西文文字或字母以及数字、空格和非数字字符的组合，每个单元格中最多可容纳32000个字符数。虽然在Excel中输入文本和在其它应用程序中没有什么本质区别，但是还是有一些差异，比如我们在Word、PowerPoint的表格中，当在单元格中输入文本后，按回车键表示一个段落的结束，光标会自动移到本单元格中下一段落的开头，在Excel的单元格中输入文本时，按一下回车键却表示结束当前单元格的输入，光标会自动移到当前单元格的下一个单元格，出现这种情况时，如果你是想在单元格中分行，则必须在单元格中输入硬回车，即按住Alt键的同时按回车键。
【2】输入分数
几乎在所有的文档中，分数格式通常用一道斜杠来分界分子与分母，其格式为“分子/分母”，在Excel中日期的输入方法也是用斜杠来区分年月日的，比如在单元格中输入“1/2”，按回车键则显示“1月2日”，为了避免将输入的分数与日期混淆，我们在单元格中输入分数时，要在分数前输入“0”（零）以示区别，并且在“0”和分子之间要有一个空格隔开，比如我们在输入1/2时，则应该输入“0 1/2”。如果在单元格中输入“8 1/2”，则在单元格中显示“8 1/2”，而在编辑栏中显示“8.5”。
&& &【3】输入负数
在单元格中输入负数时，可在负数前输入“-”作标识，也可将数字置在（）括号内来标识，比如在单元格中输入“（88）”，按一下回车键，则会自动显示为“-88”。
【4】输入小数
在输入小数时，用户可以向平常一样使用小数点，还可以利用逗号分隔千位、百万位等，当输入带有逗号的数字时，在编辑栏并不显示出来，而只在单元格中显示。当你需要输入大量带有固定小数位的数字或带有固定位数的以“0”字符串结尾的数字时，可以采用下面的方法：选择“工具”、“选项”命令，打开“选项”对话框，单击“编辑”标签，选中“自动设置小数点”复选框，并在“位数”微调框中输入或选择要显示在小数点右面的位数，如果要在输入比较大的数字后自动添零，可指定一个负数值作为要添加的零的个数，比如要在单元格中输入“88”后自动添加3个零，变成“88 000”，就在“位数”微调框中输入“-3”，相反，如果要在输入“88”后自动添加3位小数，变成“0.088”，则要在“位数”微调框中输入“3”。另外，在完成输入带有小数位或结尾零字符串的数字后，应清除对“自动设置小数点”符选框的选定，以免影响后边的输入；如果只是要暂时取消在“自动设置小数点”中设置的选项，可以在输入数据时自带小数点。
【5】输入货币值
Excel几乎支持所有的货币值，如人民币（￥）、英镑（￡）等。欧元出台以后，Excel2000完全支持显示、输入和打印欧元货币符号。用户可以很方便地在单元格中输入各种货币值，Excel会自动套用货币格式，在单元格中显示出来，如果用要输入人民币符号，可以按住Alt键，然后再数字小键盘上按“0165”即可。快速输入欧元符号 先按下Alt键，然后利用右面的数字键盘(俗称小键盘)键入0128这4个数字，松开Alt键，就可以输入欧元符号。
【6】输入日期
Excel是将日期和时间视为数字处理的，它能够识别出大部分用普通表示方法输入的日期和时间格式。用户可以用多种格式来输入一个日期，可以用斜杠“/”或者“-”来分隔日期中的年、月、日部分。比如要输入“日”，可以在单元各种输入“”或者“”。如果要在单元格中插入当前日期，可以按键盘上的Ctrl+;组合键。
&&& 【7】输入时间
在Excel中输入时间时，用户可以按24小时制输入，也可以按12小时制输入，这两种输入的表示方法是不同的，比如要输入下午2时30分38秒，用24小时制输入格式为：2:30:38，而用12小时制输入时间格式为：2:30:38 p，注意字母“p”和时间之间有一个空格。如果要在单元格中插入当前时间，则按Ctrl+Shift+;键。
&&& 【8】输入比值
&&& 如何在excel中输入比值(1:3)，单元格式设置为文本即可。先设成文本格式，再输入。
【9】输入0开头
在Excel单元格中，输入一个以“0”开头的数据后，往往在显示时会自动把“0”消除掉。要保留数字开头的“0”，其实是非常简单的。只要在输入数据前先输入一个“‘ ”（单引号），这样跟在后面的以“0”开头的数字的“0”就不会被系统自动消除。还有更好的办法，就是设置单元格格式为自定义“000000#“，0的个数依编码长度定，这样可以进行数值运算。如果这带0开头的字串本身是文本，或者是不定长的，那干脆先设该部分单元格格式为文本好了。另外还可用英语逗号开头再输就可以了。
【10】输入百分数
在单元格中输入一个百分数（如60％），按下回车键后显示的却是0.6。出现这种情况的原因是因为所输入单元格的数据被强制定义成数值类型了，只要更改其类型为“常规”或“百分数”即可。操作如下：选择该单元格，然后单击“格式”菜单中的“单元格”命令，在弹出的对话框中选择“数字”选项卡，再在“分类”栏中把其类型改为上述类型中的一种即可。如果我要求为负值的百分数自动显示成红色，可以再利用条件格式进行设置，格式-条件格式-单元格数值-小于-0(格式-图案-红色)，选中要设置的单元格-----ctrl+1---分类---自定义---输入&& 0.00%;[红色]-0.00%
【11】勾怎么输入
1、按住ALT键输入41420后放开ALT键√
2、首先选择要插入“√”的单元格，在字体下拉列表中选择“Marlett”字体，输入a或b，即在单元格中插入了“√”。
【12】输入无序数据
??在Excel数据表中，我们经常要输入大批量的数据，如学生的学籍号、身份证号等。这些数值一般都无规则，不能用“填充序列”的方法来完成。通过观察后我们发现，这些数据至少前几位是相同的，只有后面的几位数值不同。通过下面的设置，我们只要输入后面几位不同的数据，前面相同的部分由系统自动添加，这样就大大减少了输入量。例如以学籍号为例，假设由8位数值组成，前4位相同，均为0301，后4位为不规则数字，如学籍号为011369等。操作步骤如下：选中学籍号字段所在的列，单击“格式”菜单中的“单元格”命令，在“分类”中选择“自定义”，在“类型”文本框中输入“”。不同的4位数字全部用“0”来表示，有几位不同就加入几个“0”，[确定]退出后，输入“56”按回车键，便得到了“”，输入“1369”按回车便得到了“”。身份证号的输入与此类似。
【13】快速输入拼音
选中已输入汉字的单元格，然后单击“格式→拼音信息→显示或隐藏”命令，选中的单元格会自动变高，再单击“格式→拼音信息→编辑”命令，即可在汉字上方输入拼音。单击“格式→拼音信息→设置”命令，可以修改汉字与拼音的对齐关系。
【14】快速输入自定义短语
使用该功能可以把经常使用的文字定义为一条短语，当输入该条短语时，“自动更正”便会将它更换成所定义的文字。定义“自动更正”项目的方法如下:单击“工具→自动更正选项”命令，在弹出的“自动更正”对话框中的“替换”框中键入短语，如“电脑报”，在“替换为”框中键入要替换的内容，如“电脑报编辑部”，单击“添加”按钮，将该项目添加到项目列表中，单击“确定”退出。以后只要输入“电脑报”，则“电脑报编辑部”这个短语就会输到表格中。具体步骤：
1.执行“工具→自动更正”命令，打开“自动更正”对话框。
2.在“替换”下面的方框中输入“pcw”（也可以是其他字符，“pcw”用小写），在“替换为”下面的方框中输入“《电脑报》”，再单击“添加”和“确定”按钮。
3.以后如果需要输入上述文本时，只要输入“pcw”字符?此时可以不考虑“pcw”的大小写?，然后确认一下就成了。
【15】填充条纹
如果想在工作簿中加入漂亮的横条纹，可以利用对齐方式中的填充功能。先在一单元格内填入“*”或“~”等符号，然后单击此单元格，向右拖动鼠标，选中横向若干单元格，单击“格式”菜单，选中“单元格”命令，在弹出的“单元格格式”菜单中，选择“对齐”选项卡，在水平对齐下拉列表中选择“填充”，单击“确定”按钮。
【16】上下标的输入
在单元格内输入如103类的带上标(下标)的字符的步骤:
(1)按文本方式输入数字(包括上下标)，如103键入\'103；
(2)用鼠标在编辑栏中选定将设为上标(下标)的字符，上例中应选定3；
(3)选中格式菜单单元格命令，产生[单元格格式]对话框；
(4)在[字体]标签中选中上标(下标)复选框，再确定。
【17】文本类型的数字输入
证件号码、电话号码、数字标硕等需要将数字当成文本输入。常用两种方法:一是在输入第一个字符前，键入单引号"\'"；二是先键入等号"="，并在数字前后加上双引号"""。请参考以下例子:
键入\'027，单元格中显示027；
键入="001"，单元格申显示001；
键入="""3501"""，单元格中显示"3501"。(前后加上三个双撇号是为了在单元格中显示一对双引号)；
键入="9\'30""，单元格中显示9\'30"；
【18】多张工作表中输入相同的内容
几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1、Sheet2......来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入这些相同的数据，此时这些数据会自动出现在选中的其它工作表之中。输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系，否则在一张表单中输入的数据会接着出现在选中的其它工作表内。
【19】不连续单元格填充同一数据
选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。在编辑区中输入数据，然后按住Ctrl键，同时敲一下回车，在所有选中的单元格中都出现了这一数据。
【20】利用Ctrl＋*选取文本
如果一个工作表中有很多数据表格时，可以通过选定表格中某个单元格，然后按下Ctrl＋*键可选定整个表格。Ctrl＋*选定的区域为：根据选定单元格向四周辐射所涉及到的有数据单元格的最大区域。这样我们可以方便准确地选取数据表格，并能有效避免使用拖动鼠标方法选取较大单元格区域时屏幕的乱滚现象。
【21】快速清除单元格的内容
如果要删除内容的单元格中的内容和它的格式和批注，就不能简单地应用选定该单元格，然后按Delete键的方法了。要彻底清除单元格,可用以下方法：选定想要清除的单元格或单元格范围；单击“编辑”菜单中“清除”项中的“全部”命令，这些单元格就恢复了本来面目。
【22】在Excel中插入斜箭头
&&&& 经常使用Excel的朋友会遇到这样一个问题：在Excel中想插入斜箭头，但Excel本身没有这样的功能，是不是就没有其他办法了呢？答案是否定的。我们要想在Excel中插入斜箭头，首先我们在要插入斜箭头的单元格里调整好大小（为了方便插入斜箭头），然后打开Word，插入一个表格（一个框即可），调整好表格大小，在这个框里插入一个斜箭头，然后把这个框复制到Excel要插入斜箭头的单元格中，再调整大小，便大功告成。我们在调整斜箭头的时候，可以先把复制过来的斜箭头打散，方法是：选中斜箭头，按右键，“取消组合”，注意调整好大小后，调整斜线使之适合单元格，方法是：点击右键，选择“编辑顶点”，这时线条两端会变成两个小黑点，我们可以自由编辑线条了。至于文字，选中文本框，移动位置，直至适合位置即可。我们赶快试试吧。
&&& 【23】其它输入补充
※在同一单元格内连续输入多个测试值 一般情况下，当我们在单元格内输入内容后按回车键，鼠标就会自动移到下一单元格，如果我们需要在某个单元格内连续输入多个测试值以查看引用此单元格的其他单元格的动态效果时，就需要进行以下操作:单击“工具→选项→编辑”，取消选中“按Enter键后移动”选项（），从而实现在同一单元格内输人多个测试值。
※输入数字、文字、日期或时间 单击需要输入数据的单元格，键入数据并按Enter或Tab键即可。如果是时间，用斜杠或减号分隔日期的年、月、日部分，例如，可以键入 9/5/96 或 Jun-96。如果按12小时制输入时间，请在时间数字后空一格，并键入字母 a(上午) 或 p(下午)，例如，9:00 p。否则，如果只输入时间数字，Excel将按 AM(上午)处理。
※将单元格区域从公式转换成数值 有时，你可能需要将某个单元格区域中的公式转换成数值，常规方法是使用“选择性粘贴”中的“数值”选项来转换数据。其实，有更简便的方法:首先选取包含公式的单元格区域，按住鼠标右键将此区域沿任何方向拖动一小段距离(不松开鼠标)，然后再把它拖回去，在原来单元格区域的位置松开鼠标 (此时，单元格区域边框变花了)，从出现的快捷菜单中选择“仅复制数值”。
※快速输入有序文本 如果你经常需要输入一些有规律的序列文本，如数字（1、2……）、日期（1日、2日……）等，可以利用下面的方法来实现其快速输入:先在需要输入序列文本的第1、第2两个单元格中输入该文本的前两个元素（如“甲、乙”）。同时选中上述两个单元格，将鼠标移至第2个单元格的右下角成细十字线状时（我们通常称其为“填充柄”），按住鼠标左键向后（或向下）拖拉至需要填入该序列的最后一个单元格后，松开左键，则该序列的后续元素（如“丙、丁、戊……”）依序自动填入相应的单元格中。
※输入有规律数字 有时需要输入一些不是成自然递增的数值（如等比序列:2、4、8……），我们可以用右键拖拉的方法来完成:先在第1、第2两个单元格中输入该序列的前两个数值（2、4）。同时选中上述两个单元格，将鼠标移至第2个单元格的右下角成细十字线状时，按住右键向后（或向下）拖拉至该序列的最后一个单元格，松开右键，此时会弹出一个菜单（），选“等比序列”选项，则该序列（2、4、8、16……）及其“单元格格式”分别输入相应的单元格中（如果选“等差序列”，则输入2、4、6、8……）。
※巧妙输入常用数据 有时我们需要输入一些数据，如单位职工名单，有的职工姓名中生僻的字输入极为困难，如果我们一次性定义好“职工姓名序列”，以后输入就快多了。具体方法如下:将职工姓名输入连续的单元格中，并选中它们，单击“工具→选项”命令打开“选项”对话框，选“自定义序列”标签（），先后按“导入”、“确定”按钮。以后在任一单元格中输入某一职工姓名（不一定非得是第一位职工的姓名），用“填充柄”即可将该职工后面的职工姓名快速填入后续的单元格中。
※快速输入特殊符号 有时候我们在一张工作表中要多次输入同一个文本，特别是要多次输入一些特殊符号（如※），非常麻烦，对录入速度有较大的影响。这时我们可以用一次性替换的方法来克服这一缺陷。先在需要输入这些符号的单元格中输入一个代替的字母（如X，注意:不能是表格中需要的字母），等表格制作完成后，单击“编辑→替换”命令，打开“替换”对话框（），在“查找内容”下面的方框中输入代替的字母“X”，在“替换为”下面的方框中输入“※”，将“单元格匹配”前面的钩去掉（否则会无法替换），然后按“替换”按钮一个一个替换，也可以按“全部替换”按钮，一次性全部替换完毕。
※快速输入相同文本 有时后面需要输入的文本前面已经输入过了，可以采取快速复制（不是通常的“Ctrl+C”、“Ctrl+X”、“Ctrl+V”）的方法来完成输入: 1.如果需要在一些连续的单元格中输入同一文本（如“有限公司”），我们先在第一个单元格中输入该文本，然后用“填充柄”将其复制到后续的单元格中。 2.如果需要输入的文本在同一列中前面已经输入过，当你输入该文本前面几个字符时，系统会提示你，你只要直接按下Enter键就可以把后续文本输入。 3.如果需要输入的文本和上一个单元格的文本相同，直接按下“Ctrl＋D（或R）”键就可以完成输入，其中“Ctrl+D”是向下填充，“Ctrl+R”是向右填充。 4.如果多个单元格需要输入同样的文本，我们可以在按住Ctrl键的同时，用鼠标点击需要输入同样文本的所有单元格，然后输入该文本，再按下“Ctrl+Enter”键即可。
※快速给数字加上单位 有时我们需要给输入的数值加上单位（如“立方米”等），少量的我们可以直接输入，而大量的如果一个一个地输入就显得太慢了。我们用下面的方法来实现单位的自动输入:先将数值输入相应的单元格中（注意:仅限于数值），然后在按住Ctrl键的同时，选取需要加同一单位的单元格，单击“格式→单元格”命令，打开“单元格格式”对话框（），在“数字”标签中，选中“分类”下面的“自定义”选项，再在“类型”下面的方框中输入“#”“立”“方”“米”，按下确定键后，单位（立方米）即一次性加到相应数值的后面。
※巧妙输入位数较多的数字 大家知道，如果向Excel中输入位数比较多的数值（如身份证号码），则系统会将其转为科学计数的格式，与我们的输入原意不相符，解决的方法是将该单元格中的数值设置成“文本”格式。如果用命令的方法直接去设置，也可以实现，但操作很慢。其实我们在输入这些数值时，只要在数值的前面加上一个小“'”就可以了（注意:'必须是在英文状态下输入）。
※快速在多个单元格中输入相同公式 先选定一个区域，再键入公式，然后按“Ctrl+Enter”组合键，可以在区域内的所有单元格中输入同一公式。
※同时在多个单元格中输入相同内容 选定需要输入数据的单元格，单元格可以是相邻的，也可以是不相邻的，然后键入相应数据，按“Ctrl+Enter”键即可。
※快速输入日期和时间 当前日期 选取一个单元格，并按“Ctrl+;” 当前时间 选取一个单元格，并按“Ctrl+Shift+;” 当前日期和时间 选取一个单元格，并按“Ctrl+;”，然后按空格键，最后按“Ctrl+Shift+;” 注意:当你使用这个技巧插入日期和时间时，所插入的信息是静态的。要想自动更新信息，你必须使用TODAY和NOW函数。
※快速输入无序数据
在Excel数据表中，我们经常要输入大批量的数据，如学生的学籍号、身份证号等。这些数值一般都无规则，不能用“填充序列”的方法来完成。通过观察后我们发现，这些数据至少前几位是相同的，只有后面的几位数值不同。通过下面的设置，我们只要输入后面几位不同的数据，前面相同的部分由系统自动添加，这样就大大减少了输入量。例如以学籍号为例，假设由8位数值组成，前4位相同，均为0301，后4位为不规则数字，如学籍号为011369等。操作步骤如下：选中学籍号字段所在的列，单击“格式”菜单中的“单元格”命令，在“分类”中选择“自定义”，在“类型”文本框中输入“”（如图2）。不同的4位数字全部用“0”来表示，有几位不同就加入几个“0”，[确定]退出后，输入“56”按回车键，便得到了“”，输入“1369”按回车便得到了“”。身份证号的输入与此类似。
※输入公式
单击将要在其中输入公式的单元格，然后键入＝(等号)，若单击了“编辑公式”按钮或“粘贴函数”按钮，Excel将插入一个等号，接着输入公式内容，按Enter键。
※输入人名时使用“分散对齐”
在Excel表格中输入人名时为了美观，我们一般要在两个字的人名中间空出一个字的间距。按空格键是一个办法，但是我们这里有更好的方法。我们以一列为例，将名单输入后，选中该列，点击“格式→单元格→对齐”，在“水平对齐”中选择“分散对齐”，最后将列宽调整到最合适的宽度，整齐美观的名单就做好了。
※如何在excel单元格中输入01
這個函數很管用...值得一試哦!例:&&=TEXT(A1,"00000")
把单元格设置为文本格式再输入数据，或输入'(撇号)再输入数据，或根据要显示的数字位数自定义单元格格式：如要显示5位，不足5位的前面用0填足，自定义单元格格式：00000
输入123显示00123，输入1显示00001，输入12345，显示12345
※在EXCEL中增加自动填充序列
在Excel中提供了自动填充功能，我们在使用时，可以通过拖动“填充柄”来完成数据的自动填充。例如要输入甲、乙、丙、丁……，可以先在指定单元格输入甲，然后将鼠标移至单元格的右下角的小方块处，直至出现“＋”字，按住鼠标左键，向下（右）拖动至目的单元格，然后松开即完成了自动填充。可是有时我们会发现有一些数据序列不能自动填充，例如车间一、车间二、车间三等，填充方法有两种：
第一种：单击“菜单”栏上的“工具”，选“选项”→“自定义序列”，这时就可以在“输入序列”栏输入要定义的序列。需要注意的是每输入完成一项就要回车一次，表示一项已经输入完毕，全部输入完成以后单击“添加”→“确定”，这样我们自定义的序列就可以使用了。
第二种：首先把你要添加的序列输入到一片相临的单元格内，例如要定义一个序列：车间一、车间二、车间三，把这三项分别输入到单元H1：H3，单击“工具”→“选项”→“自定义序列”→“导入”，在“导入序列所在的单元格”所指的对话框中输入H1：H3，单击“导入”→“添加”→“确定”，这样新序列就产生了。
定义的序列如果不再使用，还可删除，方法是：单击“工具”→“选项”→“自定义序列”，在“自定义序列”框中，单击要删除的序列，再单击“删除”→“确定”。
※如何输入假分数
1又2分之1怎么输入
单元格格式设成”分数“，单元格中输入1.5，先輸入1，再按空白鍵；再輸入1/2，
输入后是这样&&“1&&1/2 ”&&，不是内行人看不懂的。
二分之一,四分之一, 四分之三可用ALT+189(188,190)获得。
先输入0，空格，再输入3/2。
※录入准考证号码有妙招
最近在学校参加招生报名工作，每位新生来校报到时，我们先请他们填写一张信息表，例如姓名、性别、准考证号码、联系电话、邮编等内容，然后在Excel中进行填写，这样无论是数据统计还是分班都方便多了。
准考证号码是类似于“”的8位数字，如果直接输入的话，Excel会自作聪明地去除最前面的0，常规的做法是在录入数字时手工输入一个半角的单引号作为前导引号，但由于需要录入的数据量太大，因此便将这一列设置成“文本”格式。
很快，我便发觉本地所有考生的准考证号码中前4位数字都是相同的，是否可以想一个办法让Excel自动录入最前面的“0436”呢？
选定“准考证号码”列，打开“格式→单元格格式→数字”对话框，如图所示，在“分类”下拉列表框中选择“自定义”项，在右侧的“类型”栏中输入“"0436"@”，这里的“0436”是准考证号码最前面的4位数字，录入时注意不要忘记前后的半角双引号，最后点击“确定”按钮退出。
现在只需要录入准考证号码后面的4位数字，Excel会自动添加前面的“0436”，这样效率明显提高。
编辑提示：如果需要录入的准考证号码位数非常长，这样可能会出现其他的显示错误，因为Excel的缺省设置是单元格中输入的数字被限制在11位，一旦超过将会以科学记数格式显示所输入的数字，例如“0301”将被显示为“3．65202E＋14”；当输入的数字超过15位时，第15位以后的数字将显示为0。其实，除了将该列设置为“文本”格式外，此时我们还可以采取上述同样的方法简化录入操作，毕竟最前面的几位数字总是相同的。
※向上填充的快捷键
我只会向下填充的快捷键，向上-向左-向右的都是什么呢？
解答：向上-Alt+E,I,U。向左-Alt+E,I,L。向右-CTRL+R
一列中不输入重复数字
[数据]--[有效性]--[自定义]--[公式]
输入=COUNTIF(A:A,A1)=1
如果要查找重复输入的数字
条件格式》公式》=COUNTIF(A:A,A5)&1》格式选红色
单元格输入
我想在A1单元格内输入1而A1自动会乘1000。格式寫為: #"000"
工具—选项—编辑—自动设置小数点：-3
大量0值输入超级技巧
在单元格中输入“=450**3”会等于450000
单元格 =45**N 时出现 45000
任一数字**N ，数字后面的**N 表示加 N 个零
如何在C列中输入工号在D列显示姓名
比如在A、B列中建立了工号对应的姓名，如何在C列中输入工号在D列显示姓名。
假设你的数据区域在A1：B100，A列为工号，B列为姓名，C列为要输入的工号，D列输入以下公式：d1=vlookup(C1,$a$1:$b$100,2,false)
输入提示如何做
输入提示是怎么做出来的，好像不是附注吧!
用数据有效性中的输入信息功能就可实现自动跟踪。
“数据&有效性&输入信息”。
在信息输入前就给予提示
在单元格输入信息时，希望系统能自动的给予一些必要的提示，这样不但可以减少信息输入的错误，还可以减少修改所花费的时间。请问该如何实现？
答：可以按如下操作：首先选择需要给予输入提示信息的所有单元格。然后执行“数据”菜单中的“有效性”命令，在弹出的对话框中选择“输入信息”选项卡。接着在“标题”和“输入信息”文本框中输入提示信息的标题和内容即可。
提示显示在屏幕的右上角，离左边的单元格太远，一般人注意不到，达不到提示的目的。如何设置让提示跟单元格走？
数据有效性
只能输入以"杨"开头的字符串,或者是含有"龙"的字符串& &
=OR(LEFT(D35,1)="杨",NOT(ISERROR(FIND("龙",D35))))
简化=(Left(a1)="杨")+Countif(a1,"*龙*")
=(LEFT(A:A)="a")+COUNTIF(A:A,"*b*")
发表评论：
馆藏&10408
TA的推荐TA的最新馆藏[转]&[转]&[转]&[转]&[转]&[转]&[转]&[转]&}
我要回帖
更多关于 excel公式 的文章
- ·excel函数公式公式如何写 a＝5 b＝15...
- ·在excel公式中用这个公式=DATE(RIGHT(F2,2),LEFT(F2,2),MID(F2,4,2))可否将08/01/2011 转换成了2011年8月1日
- ·请大家给我以下几个excel公式中时间例子加减法的公式好吗？？谢谢！
- ·excel公式中这个公式是什么意思?
- ·（高分悬赏）怎么用excel公式 的公式列举表内各元素的相对关系
- ·Excel如图，绿色部分应该怎么编辑excel公式大全？
- ·excel减法公式 公式填充问题 求大神帮忙
- ·请教excel函数公式大全公式
- ·excel怎么输入公式公式，0.3x+0.7y=z，已知z，怎样求x，y的随机数
- ·EXCEL有哪些公式公式求助。我想求出两个年份间隔了几年，但是如果开始的年份不足一年则不算。
- ·excel公式函数公式为什么返回错误
- ·excel求和公式excel能否操作出这种效果
- ·请教个excel公式大全
- ·求Excel公式对比公式，谢谢
- ·求助excel基础入门教程公式
- ·Excel公式中怎么用公式计算出导购是AAA的去重客户数？
- ·excel求和公式excel在是否VIP的不同情况下赠品金额是进货金额的不同点子
- ·请excel公式高手看看这个公式怎么写？
- ·求大神帮忙看下这个Excel求和公式excel怎么改
- ·求解excel公式 函数公式
- ·excel公式表如何设置公式达成下列要求
- ·需要一个EXCEL公式的公式，求助求助，几百份做不完了
- ·Excel求和公式excel的问题 求大神教一下
- ·excel公式 B2的公式 B2=IFERROR((E2*L2)+(H2*L2)+F2+I2+J2,"")，但是在N列中，没有564的编号
- ·请教个条件excelexcel函数公式if条件
- ·Excel公式 VLookup公式哪里出现了问题
- ·要求 excel公式内用同一公式进行多个数据搜索
- ·excel公式 怎么理解这个公式呢
- ·execl 汇总数据如何用execl函数与公式运用批量减去等于某项对应的单元格数值？
- ·求这个规则的excel公式评标公式
- ·EXCEL公式里用什么公式对A1-A45中不同的数据在B列中进行排序。
- ·excel公式统计当天有工资的数据excel合并函数？
- ·求助，Excel公式2003公式问题，万分的感谢！
- ·excel公式懂函数公式的来
- ·Excel公式如何在统一修改公式，修改后仍不改原数值，只在改变后来数值
- ·关于Excel公式，请教下面两公式的具体用意。
- ·已知公式y=4x+m，在Excel公式中怎么做拟合曲线求出m值
- ·excel计算公式公式中=年报!AL20是啥意思 求大神解答!
更多推荐
- ·dirk的互对你的现在不打扰是什么歌这首歌歌词刚唱完紧接着的大声的怼的音乐节奏没有唱的那几声原歌曲是叫什么歌名？
- ·晋城博润微创外晋城肛门专科医院哪家最好上厕所便秘会流血肛门裂开般痛苦难忍，怎么办？
- ·国美买的东西是正品吗淘物中国 这个是不是真的网址，求网友辨别
- ·刚刚在抖音上看了几个视频，对方取消关注你后还可以刷到你吗了几个人，但是明明已经对方取消关注你后还可以刷到你吗上了，返回去看又没对方取消关注你后还可以刷到你吗上？
- ·虎扑和毒的鉴定准确吗的评分准吗
- ·日语n2总分从零基础到N2最短需要多久？
- ·高中数学该怎么学好数学？
- ·zenm思密达是什么意思是什么
- ·你对《耐磨的驱动人生怎么用》有什么感触?用几句话...
- ·2011全国理综北京理综31题 第6小题 最后...
- ·中国第一辆汽车车6小时行了全程的4分之3，行完...
- ·澳大利亚是什么洲最有名的建筑物是什么？最有名的...
- ·相同温度下。HCL和KNO3的hcl水中溶解度度哪...
- ·SU KAbie ruk na kaI [少] 不是一类...
- ·addictedtofeet to the Int...
- ·世界上最毒的蛇大的蛇有多毒
- ·understanding in motion前应用什么介...
- ·常州培训网日语培训
- ·加入电子IT阅览室的入党申请书怎么写怎么写？
- ·已知函数y 4的x次方f(x)=x的平方+bx+c的...
- ·excel函数公式公式如何写 a＝5 b＝15...
- ·什么和什么分别是dna和rna的根本区别组成成...
- ·求secret base 下载～君がくれたも...
- ·微弱端详的近义词词
- ·澳大利亚驻上海上海领事馆贴签
- ·已知函数fx x2(x)=x的平方+bx+c的...
- ·转折 材料作文怎么写写
- ·鬼泣41.2汉化补丁×8.1+11×9.25+5....
- ·《曹刿论战ppt》就是（ ） 编著的《》中的...
- ·这张图片的那个斜框子教程怎么弄的，为什么我...
- ·北京会计考试网 会计继续教育后需要考试吗
- ·nationa hoilday申论大作文怎么写...
- ·市场营销有哪些课程专业有多少门课程？
- ·二次函数x值表达式y=1-x^2（-1<x≤2）的值...
- ·北师大三年级语文 三年级可能性课件下载
