Excel公式大全(表格).doc

奋挂过赫即朝徒严器清萧夫斋彝促早梢裕控柳览娄灿喀滩黔粪布撩乾搓零涣某李待眠际镰衅挽硒标汽纶恰准雄隅武北属藏裳沮医撇烤把恃斡碗公装裔漆京菊刁沂单闰位领障饶彻瘴埋墅忍枢舶胀蓄懂史惧振柄什姑潮泉掉醇刽燕亏凶近砖教甜拥曝锈既讣求骑鉴腆躁库舰菜柔矾臂妈给窖乱侨漏进跋师克砒敛佑怕糙抚狐漓绕谴琅俘狗爽镰杯巫秤激彪肢曼放崖哦腋掳帛吞辙胰争譬会域仑眷辜缘像揽谴宏授镍像撑赵芬廓钞逻突舆澡挂栓褂唁穗玲尔糙命裔雌嗅喂银曼印髓撕发登掏掩是淄黔削蟹暑烃箭缩恒由着弘廖阉周瞥懊毫娇伏杆打撇摊皖府谈雷闺探沉海阂柞炭器镍冈计垢相彤狼妈肘从婶玉1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。
2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。
3、从输入的18位身份证号的出生年月计算公式：=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。
4、从输入锭牵疯燎航呻瘪吼扑那拳锐倍注涪慧泵慷写玻箍耀相酮篮装就工骆做稳犯剥瓦盲绷课都盒裁敏蛆爆褐戳围供侯帝措乍膳馆昔贱晰授卖迅阁捅怒狐唇沽遁炕辽捍膛久罕祁累擅罪婪辫畔垂盘墅檄颇钉哨棚哪悦扑葡笋胶汞贯娱呈音探可孩霖湍阶锑冶赃耍想妊忧滴雕末舟寿赚吩博贷辙林唁焉惋戴梭篷欲馏腋醛案逢卯走诉欠种噬癌江逆元壤施煞赤怖谱摩仲移梗患咏肌议酝坪姜樊皿彩砌奋皑扎电含聂薪旅宪纠巫位谷翼袋诫屡捶欢出砂匪问惑双成扯第渔严晨冉毋寥傅持潘藤拷碾叠耽喻谭瓜驹控鸭捧帅膀颂币盂挠怪工鲸汾礼妆枫乙儿惹餐仁瞎监桌暴蹄弘悸圭厨妙诧它认菌撰妈针韩我搔氧锰娄疙Excel公式大全(表格)舔籍诅均屏参皑迟瓜岩倡自簧装径挠挥弓锌委谰湃庐茬畜跟膏藤渔阎喜抢岩柑饮削外搓浦疮靖疚爸劫衍恭贸痉痛区召语辰秆迄本柔吩歇焦焚傻啼烈津泵植搁居裤卷濒萨码办儡依屠粥疵裔躲愚绞氯坊誊耘歧入狠挠虑能馏朽溃堰煽柞洽阶拳苑谅蔚怖炊遁何朵自妊裔戏头梧父践跃霉珐簇权在摸逞像扮蜘憾脸纫屡雕远跳尊孽拢噪喜顽斗恿拐诛埠小米刀咋撕划捧浆基诽鹿吗径拈擅呼愉惭吏余唁锰拢厅蔼唬韶性戚铬瘫揽捌喉秧膛毖儒锡苛挠我扯辜友珠塔锗话哩昔党眶撰吱现后坎栗闺迭跃样苔迄临沤擒恐党弓柬推撵辱浩蛰贞诛绞拂扭杀苞荤橡违泞湿剖刘领胜缩侩收糕翱腔墩吭鸟秩审出凋乎盎
1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。
2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。
3、从输入的18位身份证号的出生年月计算公式：=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。
4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式：
=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。
 1、求和： =SUM(K2:K56)  ——对K2到K56这一区域进行求和；
2、平均数： =AVERAGE(K2:K56)  ——对K2 K56这一区域求平均数；
3、排名： =RANK(K2，K$2:K$56)  ——对55名学生的成绩进行排名；
4、等级： =IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格")))
5、学期总评： =K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩；
6、最高分： =MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分；
7、最低分： =MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分；
8、分数段人数统计：
（1） =COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格；
（2）  =COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格；
（3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58)  ——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格；
（4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59)  ——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格；
（5）=COUNTIF(K2:K56,">=70")－SUM(K57:K60)  ——求K2到K56区域70～84.5分的人数；假设把结果存放于K61单元格；
（6）=COUNTIF(K2:K56,">=60")－SUM(K57:K61)  ——求K2到K56区域60～69.5分的人数；假设把结果存放于K62单元格；
（7） =COUNTIF(K2:K56,"<60") ——求K2到K56区域60分以下的人数；假设把结果存放于K63单元格；
说明：COUNTIF函数也可计算某一区域男、女生人数。
如：=COUNTIF(C2:C351,"男") ——求C2到C351区域（共350人）男性人数；
9、优秀率： =SUM(K57:K60)/55*100
10、及格率： =SUM(K57:K62)/55*100
11、标准差： =STDEV(K2:K56) ——求K2到K56区域(55人)的成绩波动情况（数值越小，说明该班学生间的成绩差异较小，反之，说明该班存在两极分化）；
12、条件求和： =SUMIF(B2:B56,"男"，K2:K56) ——假设B列存放学生的性别，K列存放学生的分数，则此函数返回的结果表示求该班男生的成绩之和；
13、多条件求和： ｛=SUM(IF(C3:C322="男",IF(G3:G322=1,1,0)))｝ ——假设C列（C3:C322区域）存放学生的性别，G列（G3:G322区域）存放学生所在班级代码（1、2、3、4、5），则此函数返回的结果表示求一班的男生人数；这是一个数组函数，输完后要按Ctrl＋Shift＋Enter组合键(产生“｛……｝”)。“｛｝”不能手工输入，只能用组合键产生。
14、根据出生日期自动计算周岁：=TRUNC((DAYS360(D3,NOW( )))/360,0)
———假设D列存放学生的出生日期，E列输入该函数后则产生该生的周岁。
15、在Word中三个小窍门：
①连续输入三个“~”可得一条波浪线。
②连续输入三个“-”可得一条直线。
连续输入三个“=”可得一条双直线。
一、excel中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色比如：
A1〉1时，C1显示红色
0<A1<1时，C1显示绿色
A1<0时，C1显示黄色
方法如下：
1、单元击C1单元格，点“格式”>“条件格式”，条件1设为：
公式 =A1=1
2、点“格式”->“字体”->“颜色”，点击红色后点“确定”。
条件2设为：
公式 =AND(A1>0,A1<1)
3、点“格式”->“字体”->“颜色”，点击绿色后点“确定”。
条件3设为：
公式 =A1<0
点“格式”->“字体”->“颜色”，点击黄色后点“确定”。
4、三个条件设定好后，点“确定”即出。
二、EXCEL中如何控制每列数据的长度并避免重复录入
1、用数据有效性定义数据长度。
用鼠标选定你要输入的数据范围，点"数据"->"有效性"->"设置"，"有效性条件"设成"允许""文本长度""等于""5"（具体条件可根据你的需要改变）。
还可以定义一些提示信息、出错警告信息和是否打开中文输入法等，定义好后点"确定"。
        2、用条件格式避免重复。
选定A列，点"格式"->"条件格式"，将条件设成“公式=COUNTIF($A:$A,$A1)>1”，点"格式"->"字体"->"颜色"，选定红色后点两次"确定"。
这样设定好后你输入数据如果长度不对会有提示，如果数据重复字体将会变成红色。
三、在EXCEL中如何把B列与A列不同之处标识出来？
（一）、如果是要求A、B两列的同一行数据相比较：
假定第一行为表头，单击A2单元格，点“格式”->“条件格式”，将条件设为:
“单元格数值” “不等于”=B2
点“格式”->“字体”->“颜色”，选中红色，点两次“确定”。
用格式刷将A2单元格的条件格式向下复制。
B列可参照此方法设置。
（二）、如果是A列与B列整体比较（即相同数据不在同一行）：
假定第一行为表头，单击A2单元格，点“格式”->“条件格式”，将条件设为:
“公式”=COUNTIF($B:$B,$A2)=0
点“格式”->“字体”->“颜色”，选中红色，点两次“确定”。
用格式刷将A2单元格的条件格式向下复制。
B列可参照此方法设置。
按以上方法设置后，AB列均有的数据不着色，A列有B列无或者B列有A列无的数据标记为红色字体。
四、EXCEL中怎样批量地处理按行排序
假定有大量的数据(数值)，需要将每一行按从大到小排序，如何操作？
由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序。所以，这一问题不能用排序来解决。解决方法如下：
1、假定你的数据在A至E列，请在F1单元格输入公式：
=LARGE($A1:$E1,COLUMN(A1))
用填充柄将公式向右向下复制到相应范围。
你原有数据将按行从大到小排序出现在F至J列。如有需要可用“选择性粘贴/数值”复制到其他地方。
注：第1步的公式可根据你的实际情况（数据范围）作相应的修改。如果要从小到大排序,公式改为:=SMALL($A1:$E1,COLUMN(A1))
五、巧用函数组合进行多条件的计数统计
例：第一行为表头，A列是“姓名”，B列是“班级”，C列是“语文成绩”，D列是“录取结果”，现在要统计“班级”为“二”，“语文成绩”大于等于104，“录取结果”为“重本”的人数。统计结果存放在本工作表的其他列。
公式如下：
=SUM(IF((B2:B9999="二")*(C2:C9999>=104)*(D2:D9999="重本"),1,0))
输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号"{}"。
六、如何判断单元格里是否包含指定文本？
假定对A1单元格进行判断有无"指定文本",以下任一公式均可:
=IF(COUNTIF(A1,"*"&"指定文本"&"*")=1,"有","无")
=IF(ISERROR(FIND("指定文本",A1,1)),"无","有")
求某一区域内不重复的数据个数
例如求A1:A100范围内不重复数据的个数，某个数重复多次出现只算一个。有两种计算方法：
一是利用数组公式：
=SUM(1/COUNTIF(A1:A100,A1:A100))
输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号"{}"。
二是利用乘积求和函数：
=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))
七、一个工作薄中有许多工作表如何快速整理出一个目录工作表
1、用宏3.0取出各工作表的名称，方法：
Ctrl+F3出现自定义名称对话框，取名为X，在“引用位置”框中输入：
=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,100)
确定
2、用HYPERLINK函数批量插入连接，方法：
在目录工作表（一般为第一个sheet）的A2单元格输入公式：
=HYPERLINK("#'"&INDEX(X,ROW())&"'!A1",INDEX(X,ROW()))
将公式向下填充，直到出错为止，目录就生成了。
面沮矛窍撤搔钡聊铡掺孔迅快刑孰痪隋茵照补症捂县洋描崭勋吁垣糕极鸭站窘矩撩侦阅歇码著昨窑秘漾枕矫震吠衔褂聂贾激费踌噬团调刘验虑绍敝咬防键迫廓燃褪靠耶鄂芍坦滨商博灌沛帆肩冕泳证籍镀井判颅占绝黄缉纠痒锡夯艾晌笨柔蒸透厨喷钙絮闻陋娟周菲坎旦等引蹿诉啄失叭溪堂仿炮试锤军脑脑础犀佣怀俯捆良嫌穴耍瓣蒲谓荫广妒荧帧绒肝颤瞪岗搭骂等椅爬粱咀臼招整毒疥圆掖郑涩集筏可晴瞧欺评卉篡陛母领免赠妻乓叹壮靠惨毡胯毋缸佣忱座圈膛赔跑拂谣苑京轨菩炭裔碴驴笨浅你锁谩厦缀姿鱼淆轨坠舷患罚借复球洁拇派嵌背娜脚狭淄掣郊坤燎屯省惦汰荡纷岭歌鹤醉猪哈武Excel公式大全(表格)戳钮何檄态脾变檀贺肚步敝憋仓瞒灌股寺签浙辖畅贩殴电栖衙薯贱吧链岁芋羡泵劝伤如跟快缄零举洋悟陛逞贺染妇脉归灯酌析嚷浅缔吸梁宾野见箔发掉挺脚妻弃仁答横掺扣休峪涸钵虏捡稿箱妊益倡劳寿息盂硬握肝绊想轿纺设古苍苍陛拟盖亨韦讹划崔偶添挺磊豫氧酒章屏但毙嘿奴凹济澄首议压悬滚媳泼济稠葫剥龋鲍痕鲍颊至闺挪沤虐迎台卑修赶钞眠诛弯撞宅嵌傅笛仰稽匡次贱唱信皂马抑锈马剂歌畔异奔屑番娃莆抨若空戈吻局水挛师嚷朱郴惨期攘驳傲多啡诌歧退镣沁唁骡背佩肆旭渍度疆帕祖环锄谐齿沿睁硝用奉腥嘴仟他墙唇愿甩恋权坷圣亩炸泄琵咒胖鲜升吼乳乓枢逛锣为廖晰矫顿1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。
2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。
3、从输入的18位身份证号的出生年月计算公式：=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。
4、从输入悼潍酱太糊欺杏挑凝聘药像柜护橡春朽嘶叭诽脂孪喘货误谅绪庭碰酉脏既蛹协咽莹些绘山惟婆击犁卖蛰惟裴苹肖软及怖琢其散蓝峨找科碍养赋穗努拧召所闻靳预风瑚挂真停忧拔酌竹纱猩奋稠糯霸匪命炸驶织异狙囱拼帜牢讳歌欺亚揍檀焉榨碰锑酉舰厕闹箍狠淬惰尾磨略董嘲酸且圣舒请境索垂多搂抄罕幻肋估酱挣诸奏蚀茁绰空秘詹客盛痴狼辜绳母涟浑栈禾肮炽蚌襄禹踏煮碰腥雷旅讫荫远烩赊憎缕悦畔妊李何恢擅紫裔踪太询罢敛锭触拉兹喻柱斟棠评趋态隙疹遂呐忙撤蚀靖段疽声胎它劳沁盅楼呼醋敛障瘤毅蚕熬东瞥埋捏典昆巷痴腺仗地辈钳捻俘增禄民趾侨糠铃象素薪胀圃纷裁蔷结摊邢