Excel 解析中高级函数2.docx
《Excel 解析中高级函数2.docx》由会员分享，可在线阅读，更多相关《Excel 解析中高级函数2.docx（38页珍藏版）》请在冰点文库上搜索。
Excel解析中高级函数2
如何实现对日期格式的要求条件
Q：
在条件语句中如何实现符合某个时期的条件的记录进行统计,比如有1-12月份的记录单,需要实现对每个月里些数据的统计汇总/（格式如何?
）
A：
dongmu
以下公式,A列为日期列,B列为数据,要求计算1月份的累计:
=SUM（IF（MONTH（A:
A）=1,B:
B,0））
此为数组公式,在输完公式后,不要ENTER,而要CTRL+SHIFT+ENTER.（好象不足呀!
程香宙）
截取单元格里某个字符后的字符
A：
tof：
使用RIGET（）或LEFT（）函数就可以了,详细可以参考EXCEL的帮助
liberty：
比如这样：
a1内容为,g2er.g
我需要提取出，号以后的字符，g2er.g
午餐：
先使用SERACH或FIND函数找出“，”的位置，再用MID来取数，如A1＝123，456，我想取出“，”以右的数，可以＝MID（SEARCH（，），8）。
可能我用的函数不行，不过思路一定行。
你多看看帮助文件，我在网吧，此机无EXCEL无法试，不过我以前用过，用MID加SEARCH一定行的通，多试试吧。
实在不行再给我邮箱发信了，愿意帮忙！
祝你好运。
我认为能用函数解决的最好不用VBA，你说呢？
markxg：
假设A1中有,g2er.g
=RIGHT（A1,LEN（A1）-SEARCH（",",A1,1））
missle：
我今天也试着用公式解决了你的问题，思路与MARK的一样，只是他用的是SEARCH，我用的是FIND。
但不是很清楚你的问题，是否要把“，”号一起给截取下来，如果是的话：
公式应该是=RIGHT（A1,LEN（A1）-SEARCH（",",A1,1）+1），否则“，”号是无法截取到的。
把问题给纵深一下，如果单元格内有多个“，”的话，如A1中是：
aa,bb,cc,dd我想截取cc,dd该如何作呢？
或者想知道这个单元格中含有多少个“，”又应该如何呢？
（VBA的解决方法除外）
午餐：
Missle，你看仔细了，在Search函数中是有参数的，它允许你指定在第几个出现显出位置，如“，”你可以通过指定search参数选定第几次出现时的位置，对于截取我更认为MID比LEFT和RIGHT好
用if函数判断
请问:
如何用if函数判断,如果a1单元格大于0,b1单元格为0是错误,为非0是正确?
A：
dongmu
if（and（a<=0,b=0）,"错误","正确"）
葡萄：
=if（a1>0,if（b1=0,"错误","正确"）,"条件一不满足"）
分别显示总分最高的同学的班级、姓名、总分
Q:
问题1是这样的:
1、在A1:
A30单元格区域中输入500至600之间的数值；
2、在B1单元格中输入500；
3、在C1单元格中插入公式：
COUNTIF（A1:
A30,">=INDEX（B1,1）*0.9"）
回车后C1单元格中显示的结果是0，为何不能把A1:
A30中输入的数大于或等于500*0.9的单元格个数统计出来？
错在哪里，该如何更正？
问题2有下面一个表格：
班级姓名政治语文数学总分
1小东909090270/2明明959290277/3小英968991276/要在K1:
K3的单元格中分别显示总分最高的同学的班级、姓名、总分.该如何设置公式？
答案1：
建议你好好看看COUNTIF的帮助，真是搞不明白，为什么在B1里输入500而不是450呢？
COUNTIF不支持一个以上的条件，你应该用DCOUNT。
答案2：
K1=INDEX（A2:
F4,MATCH（K3,F2:
F4,0）,1）
K2=INDEX（A2:
F4,MATCH（K3,F2:
F4,0）,2）
K3=MAX（F2:
F4）
解答3：
问题一：
公式改为：
=COUNTIF（A1:
A30,">="&INDEX（B1,1）*0.9）
问题二：
在K1:
K3中分别输入如下公式（假设A1为"班级"）：
=LOOKUP（$K$3,$F$2:
$F$4,A2:
A4）
=LOOKUP（$K$3,$F$2:
$F$4,B2:
B4）
=LARGE（F2:
F4,1）
解答4
第一题:
=COUNTIF（A1:
A30,">="&B1*0.9）
第二题:
K1==INDIRECT（"A"&MATCH（MAX（F1:
F4）,F1:
F4））
K2=INDIRECT（"B"&MATCH（MAX（F1:
F4）,F1:
F4））
K3=MAX（F2:
F4）
定有如上两列数据A和B 现在想要统计满足条件B＝8的 并且在C列自动生成数据
Q:
ABC
4278427/6128612/9248924/2216409/9416/62016/95516/4098
请问:
假定有如上两列数据A和B,现在想要统计满足条件B＝8的,并且在C列自动生成数据,我不懂公式怎么写?
我知道筛选能够做到,但是由于数据量比较大,想做一个模板，免去一些重复劳动.
A:
dongmu
=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=8,0,1）+ROW（$B$1:
$B$8）/100000,0）））
老夏L:
B栏有100个选取条件,公式要修改100次,有甚么灵活性?
给字段名称,插入头两列.B1=SUBTOTAL（9,B4:
B65536）.解决掉所有问题,控件找自动筛选.
排名问题
在A1:
F6区域有下面一个表格：
班级姓名政治语文数学总分
1小东909090270/2明明959290277/3小英968991276/4小刘959092277/5小红959192278/要在K1:
K3的单元格中分别显示总分最高的同学的班级、姓名、总分/在L1:
L3的单元格中分别显示总分第二的同学的班级、姓名、总分/在M1:
M3的单元格中分别显示总分第三的同学的班级、姓名、总分/注意期中277分的有两人,不要出现第二名与第三名都是"明明"的结果.
A:
dongmu
定义A2:
A6区域为班级；定义B2:
B6区域为姓名；定义F2:
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名的情况了。
该公式可以无限排列。
研究彩票，从统计入手
Q：
我有一个VBA编程的问题向你请教。
麻烦你帮助编一个。
我一定厚谢。
有一个数组列在EXCEL中如：
01020304050607/和01041219252632/02081516182428/01020709121522/09151720222932/比较，如果有相同的数就在第八位记一个数。
如:
010412192526322/020815161824281/010207091215222/091517202229320.这个数列有几千组，只要求比较出有几位相同就行。
解答：
把“01020304050607”放在表格的第一行，“010412192526322”放第二行。
把以下公式贴到第二行第八个单元格“A9”中，按F2,再按CTRL+SHIFT+ENTER.
=COUNT（MATCH（A2:
G2,$A$1:
$G$1,0））
去掉XLS文件头上菜单栏的“MIcrosoftEXCEL”字样
A:
PrivateSubWorkbook_Open（）
Application.Caption="程香宙专用表格"
EndSub
替换数据
Q：
请教各位如何用将一组数据，
如：
6550894，9852547，2656032，7461136，0505867，5564892，7235580，0421077，我需要把数据中的数字1，3，5换为符号A表示，2，4，6换为符号B表示，依此类推将数据中的阿拉伯数字0~9分为几类用其它符号替换。
A：
leaf
用VBA处理比较方便。
只用EXCEL函数，感觉代价太大。
假设：
B13值为9550894在B14中输入=IF（ISERROR（FIND（MID（$B$13,1,1）,"135"））,IF（ISERROR（FIND（MID（$B$13,1,1）,"246"））,IF（ISERROR（FIND（MID（$B$13,1,1）,"79"））,IF（ISERROR（FIND（MID（$B$13,1,1）,"80"））,"","D"）,"C"）,"B"）,"A"），C14中MID（）第二个参数为2，以此类推...最后在目标单元格中输入：
=CONCATENATE（B14,C14,D14,E14,F14,G14,H14）
dongmu：
表一:
AB...
11234567890、2ABABABCCCD、=SUBSTITUTE（A4,A$1,A$2）
说明:
先列一个替换表,如表一,在A4处填如数据,在B4处填如上述公式=SUBSTITUTE（A4,A$1,A$2）,并向右拖动9个同样的公式,最后一个便是结果.在将该10个相同的公式向下拖,便得到其它的结果.好处:
可以修改表一,产生变化.
ACCESS：
你可以把全部数据拷贝到WORD中，再用替换命令，想怎么换就怎么换，然后在拷贝回来。
复制数据再转置,不复制转置被隐藏的行或列
解答：
选择需要转置的单元区域，按下F5－－＞定位条件－－＞可见单元格－－＞复制－－＞选择性粘贴－－＞转置。
如何始终打开默认的工作表
可不可以作到每次保存工作表时，无论保存时是在哪一个
SHEET，但是当下次再打开时，还是原来默认的那张工作表。
比如SHEET1。
谢谢！
解答：
PrivateSubWorkbook_Open（）
Worksheets（"sheet1"）.Activate
EndSub
如何分割文本
有一列数据，全部是邮箱的，现在想将@前面的账号与@后面的域名分割开，分为两列，如何做？
解答：
采用函数分割：
例如：
A1:
name@
B1:
=LEFT（A1,FIND（"@",A1）-1）-->name
C1:
=RIGHT（A1,LEN（A1）-FIND（"@",A1））-->
或：
数据-分列-分列-分隔符号-@就可以了
两列合一列
现有两列数据A列与B列，我想把B列的数据合并到A列但必须是B1单元格的数放到A1的下面，B2放到A2的下面依此类推，有什么办法呢？
解答：
=INDIRECT（"r"&INT（（ROW（）+1）/2）&"c"&MOD（ROW（）+1,2）+1,0）
解释:
（一）EXCEL表中的列、行样式有两种：
一种标记样式为:
列（字段）以A,B,C,D......
行（记录）以数值1,2,3,4,5......
第一列第一行的单元格为A1
另一种标记样式为（取ROW和COLUMN的首位字母）:
列（字段）以R1,R2,R3,R4,R5......
行（记录）以C1,C2,C3,C4,C5......
第一列第一行的单元格为R1C1
（二）请参阅INDIRECT函数的帮助说明!
!
!
公式:
=INDIRECT（"r"&INT（（ROW（）+1）/2）&"c"&MOD（ROW（）+1,2）+1,0）
等同于:
=INDIRECT（"r"&INT（（ROW（）+1）/2）&"c"&MOD（ROW（）+1,2）+1,FALSE）
（三）工具-->选项-->常规-->设置,还可选取R1C1引用样式
每次清除数据时能否作到跳过隐藏的单元格
解答：
F5----定位条件----常量----确定----Del
或：
F5－＞定位条件->可见单元格->确定->DEL
也就是单击Sheet2时，在Sheet1的A列的最后一个记录的下一行自动填上“End”
在sheet2:
PrivateSubWorksheet_Activate（）
dimiasinteger
i=Sheets（"Sheet1"）.Cells（1,1）.CurrentRegion.Rows.Count
Sheets（"Sheet1"）.Cells（i+1,1）="End"
EndSub
用函数将输入的月份转换为这个月所包含的天数
假设A1单元格为月份：
＝TEXT（（DATE（YEAR（NOW（））,A1+1,1）-1）,"d"）
或：
=DAY（DATE（YEAR（NOW（））,A1+1,0））
介绍经验：
就SUM函数来讲,以下动态地址可行
1.SUM（$A$1:
A2）,SUM（A$1:
A2）
2.B2="A9",
SUM（INDIRECT（"a1:
"&B2））
3.B1="A1",B2="A9"
SUM（INDIRECT（B1&":
"&B2））
4.B1="A1:
A9"
SUM（INDIRECT（B1））
5.SUM（INDIRECT（"A1:
"&"A"&ROW（）-1））
6.SUM（INDIRECT（"A1:
"&ADDRESS（ROW（）-1,COLUMN（））））
在EXCEL中如何统计字数
用{=SUM（LEN（范围））}试试
如何自动填充内容
A1：
A20是编号，B1：
B20是姓名，C1：
C20是性别，当我在A21单元格输入A1：
A20范围内的任意一个编号时，B21出现对应的姓名，C21出现对应的性别。
该如何做，请帮忙。
解答：
B21单元格公式“=IF（A21=0,"",VLOOKUP（A21,A1:
C20,2,FALSE））”；C21单元格公式“=IF（A21=0,"",VLOOKUP（A21,A1:
C20,3,FALSE））”这个公式也适用于A列编号不排序的情况，如果升序的话会更简单一点。
问：
以上公式中的'false'有什么用？
能否省略？
答：
false参数主要是用它以后在A列中的数据可以不是升序排列。
不然如果A列不是升序排列，公式会出错的。
工作表的标签的字体和大小可以更改吗
答：
在桌面上点右键─内容─外观，相关的设定都在此更改。
自定义格式的体会
在formatcell的时候，选了custom后在格子里输入你想要的位数，不变的部分就照着打进去，会变得部分打0就好了，（用0占位）。
例如：
你要打的数字是00715834123456，后6位是不定的，那你要打在格子里面00715834000000。
这样如果你输入最后3位是012，那么会显示出00715834000012；如果你输入54321，那么会显示出00715834054321。
如果你会变得部分是在数字的中间，比如我的item#会是9690000001-0000002，后面的-0000002是不变的，那我就可以设置自定义格式为9690000000"-0000002",这样当我键入502的时候就会显示9690000502-0000002。
再次显示出被隐藏掉了的行（第1行）
1：
选中隐藏的上、下行，右击鼠标，选“取消隐藏”（作者注：
此法可行）
2：
Ctrl+A-----格式-----行-----取消隐藏（可以，能够一次显示所有隐藏的行或列）
3：
另一法（工作表处于未保护状态）:
假如A1被隐藏了在名称框中键入A1,回车按Ctrl+Shift+0或Ctrl+Shift+9（只显示选定的隐藏列或行）
4：
光标移到行号4上部变成上下箭头状,按住了,拖也要把它拖出来!
（慢，不好操作）
5：
选择整个工作表（点击左上角），然后再选择菜单中的行，选择最适合的行高，然后就OK!
同样可以把隐藏的列显示出来。
（这个办法最好，能够一次显示所有隐藏的行或列）
如何定义有效数字
例：
取两位有效数是从第一个不是零的数字起，取两位。
0.0023666取两位有效数是0.0023。
0.2366取两位有效数是0.23。
解答：
用函数可如下:
=FLOOR（A1,SIGN（A1）*10^（INT（LOG（ABS（A1）））-1））,+/-小数有效,0无效.
其它形式的数据,自行扩展.
sheet1工作表的A1、A2、A3单元格分别链接到sheet2、sheet3、sheet4
解答：
1、=indirect（"sheet"&row（）+1&"!
a1"）《程香宙的解释：
indirect是把文本变为单元格引用的函数row（）是取当前行号。
例如在a1输入该公式，则row（）=1，公式里的值变为indirect（"sheet2!
a1"）,跟＝sheet2!
a1同效，在a2输入该公式，则row（）=2，公式里的值变为indirect（"sheet3!
a1"）》
2、使用插入----超级链接----书签----（选择）----确定
用SUMIF函数进行条件求和，不限于一个条件时如何设置参数
例如：
有一个表格登记面粉、米粉、糯米、梗米、绿豆、早米……等等的进出流水帐，如果对满足单一条件的如面粉、糯米、绿豆等分别求和是没有问题的，但如果要将同一类的求和，例如将糯米、梗米、早米的数值加在一起，应该怎么办？
解答：
提供以下公式供参考，设A列为名称、B列为数量：
=SUMIF（A:
A,"糯米",B:
B）+SUMIF（A:
A,"梗米",B:
B）+SUMIF（A:
A,"早米",B:
B）
如B1:
D1为求和条件项，即B1="糯米",C1="梗米",D1="早米",
上述公式还可改为:
=SUMIF（A:
A,B1,B:
B）+SUMIF（A:
A,C1,B:
B）+SUMIF（A:
A,D1,B:
B）
如何在excel中已有的数值前加零变成六位
比如说25、369、1569等，操作后变成000025、000369、00156
解答：
如果直接输入的话，可以在数值前面加“'”，如“'002020”；
如果处理现成的数据，或者从别处（比如从A1单元格）链接来的数据，可以用公式：
＝RIGHT（"00000"&A1,6）
如何提取工作表中的背景图片
解答：
找个干净的地方,去掉网纹等不需要的东西,PrintScreen再编辑
绘制有三条斜线的表头
解答：
1、用绘图工具画出斜线>>画方框>>内添加文字>>去边框
2、引用WORD中的，然后再复制过来就可以！
在A列有一组数据不是按照大小顺序排列在B列中排名
解答：
方法1、将A列COPY到B列，再排序。
2、rank函数（=RANK（A2:
A11,$A$2:
$A$11,0）（假设数据在A2：
A11单元格，下同）
3、使用contif函数进行排列“=countif（a$2:
a$11,">"&a2）+1"
有无办法让B2所在行都呈红色字体
解答：
假设你有一个B列和一个A1的值，你的目的是，如果B2＝A1的话，整个B列都为红色显示！
设置如下：
先选定整个b列，也就是在B列列标处单击（废话～^_^），选择格式－条件格式
出现条件格式对话框，单击左边的下拉列表，里面只有两项，单元格数值和公式，选中公式，右边就可以输入任何可以返回逻辑值的公式了。
输入这个公式=（$B$2=$A$1）。
千万注意要用绝对引用，因为如果是相对的，excel又自作主张的一个一个判断了，就没有作用了。
（绝对正确并且好用）
现有12个工作表,是12张发票,建立一个汇总表,将发票号和金额汇总显示在一张表里
（发票号和金额在每张表的相同位置）.
解答：
在A1输入 =INDIRECT（"sheet"&ROW（）&"!
d3"）
在B1输入 =INDIRECT（"sheet"&ROW（）&"!
d10"）
再选择A1：
B1往下复制到第12行。
经验技巧
按“Ctrl+~”可以一次显示所有公式（而不是计算结果）。
再按一次回到计算结果。
（程香宙）
在一个不对称的区域中如（b1:
G7）中找到A行一组数据中的某个数并自动变红
解答：
其实也很简单,你只要选定你的b1:
g7，设置它的条件格式为=（COUNTIF（$A$1:
$A$7,b1））
注意,b1为相对引用,这里输入所选区域的第一个取值，那样你的所选区域会自动填充.达到你要的效果。
（好）
不借助第三列而直接用函数或公式一步得到sum（a2/b2,a3/b3,…）的结果
解答：
输入=sum（a1:
a100/b1:
b100），按ctrl+shift+Enter。
请问要如何算出每个月有几个星期一、二、三….日
解答：
为简单起见,表格需作一下调整,将"星期日"移到C1,其后依次,这也符合规则（请参阅函数:
WEEKDAY（））.。
在C2键入数组公式:
{=SUM（IF（WEEKDAY（DATE（$A2,$B2,ROW（INDIRECT（"$A$1:
$A$"&DAY（DATE（$A2,$B2+1,1）-1）））））=COLUMN（）-2,1））}，向右复制、向下复制。
公式解释一点:
ROW（INDIRECT（"$A$1:
$A$"&DAY（DATE（$A2,$B2+1,1）-1）））实际上是从1号测试到本月的最后一天.如需要,公式可再作精简。
让隐藏的列或行不参预计算
解答：
使用subtotal函数,详细用法参见帮助。
一次删完Excel里面多出很多的空白行
解答：
1、用分面预览看看
2、用自动筛选然后删除
3、用自动筛选，选择一列用非空白，空白行就看不到了，打印也不会打出来。
但是实际上还是在的，不算删除。
或者用自动筛选选择空白将空白行全显出来一次删完也可以。
4、先插入一列，在这一列中输入自然数序列，然后以任一列排序，排序完后删除数据后面的空行，再以刚才输入的一列排序，排序后删除刚才插入的一列。
表1、表2分别有20个人的基本情况和其中10个人的名字，让表1的数据自动填充到表2
答：
1、用lookup函数即可。
要保证20人不重名；
2、假设表1的D列对应表2的E列。
E2的公式：
=VLOOKUP（B2,Sheet1!
B:
D,3,FALSE）
使用vlookup函数返回#N/A符号时将此符号用0或空格来代替
答：
这样处理：
=IF（ISNA（VLOOKUP（C13,A1:
B10,2））,0,VLOOKUP（C13,A1:
B10,2））
或：
IF（ISERROR（vlookup（a1,e1:
g10,2,0））,0,vlookup（a1,e1:
g10,2,0））。
通过条件格式将小计和总计的行设为不同的颜色
答：
输入=RIGHT（RC,1）="计"；设定字体、边框、图案；确定。
复制隐藏后的表格到一个新表格中使被隐藏的内容不显示
答：
crtl+g－选可见单位格－复制－粘贴。
如何将一个工作簿中的一个Sheet隐藏
答：
1、选“格式”---“工作表”
升级会员 