EXCEL基础公式合集2018.2.2

1、一、Vlookup1、数值查找文本公式=VLOOKUP(F4&”,$A$2:$C$6,3,0)4T ： X z Jx= VLOO KU P( F4&. i At 2ic 16,3,0)ABCBEFG编号点货地点匕货数里根据端号查找出货数龛H001上海957 002上海64编号出货数量11003上海1031003103.ooo上海111004111005上海91005g2、文本查找数值公式=VLOOKUP(F12*1,$A$10:$C$14,3,0)=IF(ISNA(VLOOKUP(F20*1,$A$18:$C$22,3,0),二 ULO。KU P (A2&” ：穗源! 4,3B2公司名祢三川买

2、比 东南实北 国质大崇明路50号承德西路80号天府东街30号CA -S-=VLOOKUP(F12*lJ5AS10；tCS14,3,0)ABCDEFG瑞等发货地由出货数里根揖编音直携出货数里1001上海951002上海64编号出货数量1003上海F 1031003103,1004上海111004111005上海9100593、文本数值相互查找公式5、批量查找和原数据顺序一致的数据VLOOKUP+COLUMN(列计数公式)=VLOOKUP($D4,数据源!$A:$K,COLUMN()-3,0)J& -VlOOKUP(Slt.EiSila；KCClLUMNO.O：.DIFGH T5I 一户m 士用工

3、事联至手芨M展嬴,臭石I -111t而I 后-WSh正人资褥HW.实算Ln，七 A ric 1=1-I- 1#+方先生ri!物主 错售合建 后育羟不白广昆31，1天津 庠江来衙灵送训 市惠西街热2南昌北南市JL华华华小6、批量查找和原数据顺序不一致的数据使用MATCH查找表头在原数据是在第几个=VLOOKUP($A3,数据源!$A:$K,MATCH(B$2,数据源!$A$1:$K$1,0),0)Q3，虫0。如叫$2音源!祗区44TdlRK恒源!$心工耻$10),0)AE匚U邑HGH1 | 一2 客户工口一希wl I瞌亲人姓名IISn邙口吃浩天旅叮社川白广路号临江东街62号方先生 (030) 3

4、0Q765变大律KASkF.392(0571) 1T67区签深圳(0211) 5550U91 南昌780日匕32155539E2布家中二、Lookup1、个税计算公式，数组计算=MAX(F7-3500)*$C$6:$C$12-$D$6:$D$12)公式输入完成后计算需同时按 ctrl+shift+enterG7. Px7 A(=MiX(F75O0rJC$&tC$12-$D$6：tD$raABCDEFcH12343500颊应消解用蛔元税至速扣数（元）计直个脱&03%0猊前后静715001500450010%10S6500195S458450090002。%555上 00口。31202、用数组查找

5、值=LOOKUP(1,0/($B$3:$B$7=G3),$E$3:$E$7)T 上A-=LOOKU口川网启唐曰 &了二0 引 JES, E17）ABCDEFGH存二成董拄计表根盛考生姓名查找总分专陵姓名性翔米旗原姓分姓名原始分tT汪梅男本地599在梅在女制661李明朝理工林蒲男史星4a?文科朱健男本哈：JLU谣车明女本省阳43、数组精确匹配=LOOKUP(1,0/($A$2:$A$13=K6)*($B$2:B$13=L6),$D$2:$D$13)5 1，LDG).)凡;$33AQCDEFGHIJ&LH:呻区活出营口希扬王静- fl加-Il405L期工IT方T前F二科20*17】0L 2H礴售门

6、多由TOT二科L20j llj.O?f三科.1 ,2J. ! L .二“2削矶口】七L2一秣Ilk帆舵广州-H101芝L费工加二 M1 况 14M析三3201如刈1：二科115.072国二料400蚓12TLiIB三、INDEX&MATCH1、反向查找类似 VLOOKUP=INDEX（数据源旧:B,MATCH（查询!A2,数据源!A:A,0）x y 及=INDEX激振源!B：B.MATCH 值询!A2.徵据瀛!AAO)ACDE:|客户工口公司名襁CHOPS浩天旅行社浩天旅行社正人资源正人资源HUIAS英翼其翼ASTON恒源行贸易坦森行贸易t i irriTk，L右11rl冲八2、利用MATCH&

7、INDEX 查找提取图片1、自定义名称，引用位置：查找照片位置的公式，公式名称设置为：照片2、使用照相机，点击照相机，随意点击单元格，将框拖拽到需显示照片的单 元格3、点击图片框，修为改二照片四、INDIRECT1、跨表提取位置相同的数值=INDIRECT(A4&!g2)：v & =INDTRECT(A4&,!g2)ABC I IDE月份 业绩月 | 19269 闻2 月20015.07*|2、 跨表提取位置不相同的数值=VLOOKUP(张三,INDIRECT(A4&!A:G),7,0) ；A VLCi O CL= *, IN DI RECT(M& I AiG*), 7,0A B C D第一步

8、张三的业绩一vi由3q。1 m果员工姓;月份 业绩|1 月192697|19269耳 19269.7 J2 月20015.120015.13 月04271584271.5_，4月274gg527499.53、Vlookup批量查找绝对值应用=VLOOKUP(B$2,INDIRECT($A3&!$A:$G),7,0):应-VLOO KUP：BS2JNDRECT(jA3&R 15A：$G7d0)A B C D E I F月份 ,张三 李四.王五 赵六1 月19269739465.221015.923710.326973912 月2001540014.121423.940014J20015,1401

9、3 月184271.548705.74719221136.484271.548；五、SUMIFS1、在多数据中，求和一段时间的数据=SUMIFS(11月总流水!$I:$I,11月总流水!$D:$D,商户每周总流水！$C2,11月总流水!$C:$C,=2017/11/13,11 月总流水!$C:$C,=2017/11/19,11 月总流水!$V:$V,代理)* vnu i a六、高级筛选功能需要把A表的数据筛选复制到B表，需要在B表执行高级筛选的命令。七、&符号的使用1、文字或链接与单元格链接=你好，请问&B2&”在哪个城市的？”=你好r清问便在:附城市的?CD E F GT尊啥T霸屏时间葡t城

10、区云、SUMIF&SUMIFS1、查找对象数字大于15位，查找对象需加上&” *=SUMIF(A:A,F4&*,B:B)存入金额31000银行代号金额统计21000r6223888S1111222267310001131000*3223444488887777212100021000827311117777222266320OCwinrn飞双PS力船2、多行数值相同求和，最后参数为求和项的第一个目录。=SUMIF($A:$J,$L3,$B$1)a* T工|%圜，0才MiB :c口以 E奏UMI曰$.FCHvKLnI凝生琳恸外务俎同*分而用惘游*25 X浦工黑IM泅,K手tl隼E声1: :,科日

11、赵丹发生菽3M2否盲讣K. 00事中二蚌；-天 3JDC当差克i.：不匐报号二音31.330 1前宵LTI4,2I.3O土（会25E. B描埼量石 3.CE二公主黄13 S.二t 士三百：-30央三费LM，5MLE与-里马L 010.03巴美要L. m 8吉少二th匚W6%.8葬黑谡岩M ENtW理工鼻港Kl.Olt.TBjES左号叠5LSO手twm三1KCTM.00花T. ELOU.C0将蒋典。阳8机.m87m归时出：76.8上官在于1. OU.Z&“目7T期SF!JU0Q3、 SUMIF使用辅助列求和=SUMIF(A:A,H5&I5,G:G)4、SUMIFS多条件求和*BC口EFGHI.T

12、1fl日用港号款部门料日同分量上目2-SI3E高些F4123e-0023范室25 003一华阊出Ifi主窟r 0124一至芨出程生身14 M二车闫於毒杀9131工-OC工二町M导与Hg常生制5二羊闫n怖方法福E3记-4E二主Milm隹际岩g g一隼闰雷聋亲VG二车回叵梆布加29-WiJ二金运出：丁：X -M二窜闱独亍更-GUMLFiAAH53J5GsG)J5=SUMIFS($F:$F,$D:$D,$G5,$E:$E,$H5)1S.A= UM1FS序R，F.$a4D,衣S5,加& E$H5)AcDEFGHIJ1月a髯江号放部门科目划分发生收2L 29it-0023-i：ir需再季003算r 29

13、ig-0021七运聿更14 S04r31iEwsi=M10. 8部门科目M站1|sjI s29iE-MZ2二年同过幡社前宴50. gtlT虻室上161 ML Kie-0023二覆i56 00二五面独子W05、SUMIF求数值和可替代 VLOOKUP =SUMIF(A:A,M4,J:J)Ar -SUMF(AAM4.JJ1施为玲：曲他施垃工贺小:十人市相的:个A承出的r所孱骑6347 93：02AS3 ST00D&15XCT口第。Q03d0226.1a152.399E1.51|&00DSOOQ0DBOOIL李明崔掂以谴统工更安凌时1、SUMIF数据有效性=SUMIF($F:$F,$F3,$G:$G

14、)=60)A 0效学生在 李明 王小二 瑜I唐女HI州05。4D案修张大民77.0UEEl尹航左43 0“勺年酒季虹普记就士泰900 900投口56 060.06去三45.0 0.A7。 155.Q-COUN-：E2!t2)39。6Q086.0 ；2、数据超过15位数，使用&” *=COUNTIF(A2:A3,A2&*)A =COUhnF(A2:A3FA2 ST1银行卡号6223388311112222673,62238883111122222233、 =COUNTIF($G$2:$G$14,A2)=IF(COUNTIF($G$2:$G$14,A2)=1,是,否)B2： X A=COU NTF

15、SG $2：$GS 1 A2J)AB CDBFjG名洁东平涛 生雪晓李江 学俣王王李 8- 13 1J星否体检.已参加体检人员名单1 I王少楠0孟雪鹏0马艳茹。吴雪洁4、条件格式=COUNTIF($G$2:$G$14,A1)=0=COUNTIF($G$2:$G$14,A2)=0汜田浦i I史巴;是否体检透拦也口心目：后恁规如羽:EI：单亍H左营没再所有里元聃5施式十rKH当以下内官M电元格以牌噂式物国商琲W*g3E后的定giii苣中式*又门亍胃氏丁匚均亘力即宜设有电K期纯，西值其回也招近 *可耒密T?百伯比力差元用为符含叱公式的值度亘格式回：=匚用 NR$G$之*G$14M)=Q阖日走否不口是

16、否否否否wHo 1 HZ 3 4 5 k5=且ES所担单元裕的够，m R为恒堂以下内蟠M元范设詈港Tt仅凭他若靠的或靠；匕敷也灵置嘴式a仅工雷士戈工：上回细数食设置塔式学由单 吴刍活而广：再钦守蒋AeBoCic悔3c.可妞札U泛拒品：东至李平梅 晓李江谢雪晒 王王李武苏配c , n卞平京梅.丹裳 疏李江淑雪聪小琴雪晶志轮 -T王李百苛/王宇史二一5、数据有效性=COUNTIF(C:C,C1)=60),AND(A20= 女,B20=40),1000,0)C2QT ：人 =FfCRND.SO=BJOSO: AKIDtAMr B；S=41)j tMG.O十二、LEFT、MID、FIND1、时间计算为

17、分钟数公式：=LEFT（C2,FIND（小”,C2）-1）*60+MID（C2,FIND（ 时,C2）+1,FIND（ 分”,C2）-FIND（时,C2）-1）B=LEFT（C2,FIND（小”,C2）-1）分=乂曲2下山口（时,C2）+1,FIND（分,C2）-FIND（时,C2）-1）秒二乂曲3下山口（分,C3）+1,FIND（秒,C3）-FIND（分,C3）-1）b T 上IR彳P - | * *=团3中/小.#：!- 11 FO-M!D（ClflND（盯二CZl + LFTNC BfcrwX2：i-nNDfTr.c2i-ijABC口E - FGH1）K:用武立璃 遇声:WE长巧弼料fJ

18、3ffl分由5B4 3勺画“分n#；1小而12：2*J2 ）744112230（部:577 5冏5715-I ； r-x7 fr-LEFTg3.a）fir - KGFT 昭句ABCEFGH户纪标W处理产品嵋号后四早奔告节工票来奔苗乖531372007?00 |313& =MUJE T C* TT % E出等A= DATE (YEARCB6), MON T H (B 6) - C.6, D A( E 6)ABCDEFC开始日期间隔月份结束日期2013/e/L542013/10/162012f8/832012/11/814、本月天数=DAY(DATE(YEAR(B21),MONTH(B21)+1,

19、0)6、工龄，日期与日期之间相减=DATEDIF(B5,C5+1,Y)B入职日期2002/9/10CD1离职日期工龄2012/9/10Lis.1= DATE&lF(B5.C5fVn7、计算年、月、天=DATEDIF(B13,C13,y)& 年&DATEDIF(B13,C13,ym)&月&DATEDIF(B13,C13,md)& 日第几周2012/3/102002/9/102004/546离于日期 200S/7/5 2009/2/21间福,落明25天W年汩汨日！4年汨送4年明知8、计算日期是第几周=WEEKNUM(B3,2)9、计算日期是周几=WEEKDAY(B8,2)h-CATEEIFfai.

20、5C13；/i& fltDATE DI F(613.C13K 月 &DATE DiF(B13.Cl md ) 配三 =WEEKNUM(BS.2).日期凰1i冽3/二帼EKDLT广1,VEE10D国20OT/5713落物茂物仲品140220OT/r/l.B钿注物用吊108a20OTT/9/14微1宏物用品7?1ffls：冷状点懿AaBhCc格窃IF1*动态图标的制作1、开发工具-插入控件-定义控件2、定义公式名称：=IF(Sheet8!$J$11,Sheet8!$B$2:$B$7,Sheet8!$N$2:$N$7)= 各区域名称3、图表选择数据：数据二各区域名称十五、宏表函数1、63=计算单元格

21、的颜色=GET.CELL(63,A2)2、提取单元格公式3、写超链接三HYP鹿型建锥箜以便打开T存痛在耍巨.商定阻毋器型Intcmet上的女出HYPERLINK4、快速建立超链接目录1.公式-定义名称=GET.WORKBOOK(1)2 超链接=HYPERLINK(INDEX(工作名称，ROW()-1)&!a1)5、计算文本=公式-定义名称=EVALUATE(B2)”公式计算结果33+7+24504412*17*6413056E144(22-17)/61L &66676F3语文,薮学,英语总分39a 88, 9510盥我以117a 90, 90BAC弓司士置凶:-CVALUATl (EVALUA

22、TE! Ei)6、SUBSTITUTE:将字符串中的部分字符用以新字符替换1、=EVALUATE(SUBSTITUTE(A9,+)2、=EVALUATE( &B9&)把数字变成数组3、数字无符号提取某一个数字数据3=EVALUATE(&SUBSTITUTE(A16, ,;)&尸0什百IE才口电14，曜在皿前忘HE-H匿IrtWN版2;.)-EVAMjAirrr*-EU芋FVM川14rT数T作窗1S手HF户 T=INDEX(数据 3,4), IFit* Y以工医3-.：界阜谷电电四方料睛L6|1L 12 115 153 15 lr：T L2 L3 15 14 L21 16Lb M 13 13 1

23、4 15 LIL4、点击一个单元格，同列同行会变成一个，颜色设置好格式后按住F9后点击单元格1、公式-定义名称-当前单元格=REFTEXT(ACTIVE.CELL()&T(NOW()2、条件格式歹卜COLUMN(尸MID( 当前单元格，FIND(C,当前单元格)+1,100)*1 行=ROW(尸MID(当前单元格，2,FIND(C,当前单元格)-2)*1宏1、点击录制宏，输入标题，再单元格中写内容，再停止宏，宏可以帮助重复性的工作II什么是VBA,它有什么作用A实现Exel中没有提供的功能B提高运行速度C编写自定义函数D实现自动化功能F通过插入窗体做小型管理软件替换公式数据区域Replace 函数简介：用新的文本著揆原始文本中指定位宜的文本=REPLACE 8,4,”*）Substitute 函数行介：用新文本甘登指定内容的旧文