EXCEL基础公式合集

1、=VLOC KUPH2&*匚致整源出E4W一、Vlookup1、数值查种文本公式=VLOOKUP(F4&"”,$A$2:$C$6,3,0).I A=VLOQ MJ P(F4SC'" F$ A$2; tCStj 3,0)ABCDEFG编号发斐地点出斐数量根据编号查找出货数量'1001上海953 0021诲64编禺出货数里r1003上海1031003103000上海1110041110051海9100592、文本查找数值公式=VLOOKUP(F12*1,$A$10:$C$14,3,0)VLQQKUPCF12*L$A$：ia$C$1430)ABCD

2、EFG编母发货施点出货数里根据扁旨宜找出货戴里1001上海951002上海64出甯桶乙1003上遵1031003 1LQQ4上海11WQ4111005上海g100593、文本数值相互查找公式=IF(ISNA(VLOOKUP(F20*1,$A$18:$C$22,3,0),ABCDE1公司名称aat2 1三“四t大崇明路50号3东南买北承德西路80号4国顶.天府东街30号B2"S'TT快 un 舁点 中口 口#!+5、批量查找和原数据顺序一致的数据VLOOKUP+COLUMN(数公式)=VLOOKUP($D数据源!$A:$K,COLUMN()-3,0)-VLOO KUM1D4 J

3、0EB3 KCC LUMhO'lO)5客户不公司科评笆g/.一拄口展石人十舒刑期应国CJCTS浩天而i亍社方失生物主白a路314工天津华北EKKSHiF.人资源附小俎望曾经方静工床位52科|华南买胃仁加力二日酎小城工匕也芍转经医赤恚酉街3期南昌塞aL尺 Tnn匕华宗6、批量查找和原数据顺序不一致的数据使用MATC蛋找表头在原数据是在第几个=VLOOKUP($A数据源!$A:$K,MATCH(B$2,数据源!$A$1:$K$1,0),0)讦浜A,附名方先生(030) 3口口7电蜡天茬2 客口工 公讥名梆3 lews 浩天旅行社382号(057L) TE75Mz除加(0211) 555W9

4、1 南昌=VLOO<U P(i.蚊毫刷 & A感 KATCHfKi 之效卷源:L 伟 Ki 1,0), 0)4 EIWEH正人资1原5 1LAJAB实翼u AMUM坦森行贸易黄台北掰7S0耳 王炫的 (032U 555393Z石家庄二、Lookup1、个税计算公式，数组计算=MAX(F7-3500)*$C$6:$C$12-$D$6:$D$12)公式输入完成后计算需同时按ctrl+shift+enter67V丘=MAX(H-350DrSCI6:tCS124DU:lDS12)ABCDEFGH1234起犯点35005谓3内税所薄粒(元)附速兄1数1元)计算个税60不超过15000税前月

5、麻715001500-450010%1056500195345004500-900020%555200003120990009OO0-3SDOO25%1005S00034S2、用数组查找值=LOOKUP(1,0/($B$3:$B$7=G3),$E$3:$E$7) | L" T "上 口0” H1t I ；fi.=LOOitLlPl .D/fl Bt 34 Bt7=G U$3： JE J7JA L BCDE| FGH 考三成境猊计表卜艮居书生姓名查找总分专业突姓名士、河原始分姓名原始公_理工汪梅男本地599注悔990rr理工1醒文市地661弼理工林博男外星4C7朱健男本省31

6、0文科李明女本省阚3、数组精确匹配 =LOOKUP(1,0/($A$2:$A$13=K6)*($B$2:B$13=L6),$D$2:$D$13)- Y,ETT LT m-*4: LOO5A12 Uli J Kb 倍时上牌 U -恳口 113 jABcDE F G H I JKLMj漏生工潮F福ie“道骸,溺SY ft4口才维-H20却口二工见广州-u2023.730.2i5他也里耳±»两=二口LS割cn配计制ZU2加工以93旧L_.皿0H.12f fl18加丸6酩2广州二 H1002 L 4235it*二林如L2L4?Dd恤 Q1C12f»T二1匹雌4-fl4皿B

7、M2TL<g三、INDEX&MATCH1、反向查找类似 VLOOKUP=INDEX(数据源旧:B,MATCH($询!A2,数据源!A:A,0)X 4 亲=INDEX（款据源出B.MATCH（直询iA2.数据深1dA网）ABCDE客户口公司钿:CHOPS落天膝行社浩天旅行社ERHSH正人资源正人资源HANAE实翼头翼ANTON坦森行贸易坦森行留易t i i rrrrk2、利用MATCH&INDEX找提取图片1、自定义名称，引用位置：查找照片位置的公式，公式名称设置为：照片2、使用照相机，点击照相机，随意点击单元格，将框拖拽到需显示照片的单 元格3、点击图片框，修为改二照片四

8、、INDIRECT1、跨表提取位置相同的数值=INDIRECT(A4&"!g2")2、跨表提取位置不相同的数值=VLOOKUP("长三",INDIRECT(A4&"!A:G"),7,0)；点=VLCOKU P(H5£ZH. N D1RJEC7 (AJ 1A; GH) r7,0)A B C D第二步张三的业绩 =¥LOCXUP张三小用血7,6 1n果员工姓月份 业绩1 月 19259.7厂历259了 1926972 月20015.120015.1'3 月84271.584271.54 月274

9、99.52749953、Vlookup批量查找绝对值应用=VLOOKUP(B$2,INDIRECT($A3&"!$A:$G"),7,0)R =MLQOKU巴B$2 IN口nlAOT!.工田(I ABODE F (月份张三李四壬五赵六1 月19269.739465.221015.923710.3|19269.713942月20015.1400*4.1 21423 g 40014.120315.140(3 月84271.548705.74719221136.484271,5481五、SUMIFS1、在多数据中，求和一段时间的数据=SUMIFS('11月总流水&#

10、39;!$I:$I,'11 月总流水'!$D:$D,商户每周总流水!$C2, '11月总流水!$C:$C,">=2017/11/13",'11 月总流水!$C:$C,"<=2017/11/19",'11月总流水!$V:$V,"代理")加tRNEH眇斯I酬三;二”品仙二出:门才菽忠二.1泞二：1刈1W®瞅1烛'硼皿?加却加哼1E1!FGI1I E瞅拜罪柒嬲H胭概20W1网既搬5邮肮1狐JLUD山加仙1腑喇瞰m跳9螭撩如M产卜;7：柏Timifiiiihj urnAi

11、11j1 n Miu »innrwflmnnA a mi nflJ 1 fliliRrnirr六、高级筛选功能需要把A表的数据筛选复制到B表，需要在B表执行高级筛选的命令。七、&符号的使用1、文字或链接与单元格链接="你好，请问” &B2&在哪个城市的?二.你好r清间也瞪St.在哪个域市的? C0八、SUMIF&SUMIFS1、查找对象数字大于15位，查找对象需加上&" *"=SUMIF(A:A,F4&"*",B:B)£制理二 V二=GLM：F(AAF3&e 占 EBC

12、D才1FHG存入金额31000银行卡号金额统计210C0"62238888111122226731(00131000*322344448888?777212100021000 2731111777苣 222563200Cr3WC012333S2227LB393922C0C2、多行数值相同求和，最后参数为求和项的第一个目录=SUMIF($A:$J,$L3,$B$1)己与，C* Y却Z时底，V ；MW-a -SUMTFfJiJjLiWWBC EFGHIJKL.rJ 73发士璃科日如舟发!US科副分£±«料日划分m(K匣g士博工三者冈车r五噌百ISr lS R

13、OrHMi窗雷；4.Pt1巧克科Mg善二国L程a制文三二二'=3音4S址2赛(目工正如毛单身弁工优坞费L.42L 播*合用品LE.»胆子身底戈.力)第封上于：盟贵7曲8二天券L ：E5.乳地00过怫之尾更口老三.二麓工3f：三二三h1.DLL 7B二一2屋:M« =R3工由-v.= 3T巴桂堇贵 .一青小C-3制学it林拘诬?fria wL海盗白舐三JrSTT *为hff电B3、SUMIF®用辅助列求和=SUMIF(A:A,H5&I5,G:G)K 彳 AGUy】(A:AH5M5,G:G)BCD£FGH11瑚咖目口气重号救#n发生收军1挈周

14、装产01r工9 K23-±r丸co一年间出留钻,良23S-K21空直一三二)14 B4二学闫/声会0-1F31记-mm二主育ED. M杷门科目同好安生罪a记-M建二名七过桥W股要处M一车间石市土二宅闰运烹行忙。1FE2正-ME 3二主笃运堂庄：56. X二王间STSJtW14、SUMIF够条件求和=SUMIFS($F:$F,$D:$D,$G5,$E:$E,$H5)JS= £LMlFb($F：$FJSD：tDJG5,i£4£lHS)ABCDEFGHIJn日免征号*an科目招抄余生做r oi也tE-OJ22一菊工百告曼5.00129比-821.一车叵土婴车费

15、14. SOF曳31i£-003i二华同前交女20. OO部门科目却分发生强：L匚，iE-0022二不同M伊士雷整50. O0一奉向尤克费S3129iE-OT23二本间运空上工56 OO二室衍隆子身0912i记一806打存壬花于女65. 00二主怕123i25、SUMIRE数值和可替代 VLOOKUP =SUMIF(A:A,M4,J:J)-SUMIFiAAFVWJJl?EFGEIJKL宛忖工资朔他施发工费小：小人景田的西小就相附时品项 劣境散6S47 9302463 510口 21s.S71300033002ZC, 10感.33 球LAI眄口0gooa0n时口，1、SUMIFS妁g有

16、效性=SUMIF($F:$F,$F3,$G:$G)<=SUMIF($A:$A,$F3,$B:$B)解释：出货数量相加小于库存才可以填写数值tlM居&r如产f鼓二*Kl用三M1UK509婚, '”1 .0住餐守亚D咨砾品工置忖<13615M金忘lEgitf事0泄玩L«OD旧也的10显/LM。Dft£宓咄E5 HJuWRlf.r IFllL Ki- iLMF'IAl 困2、数据超过15位数，使用&” *=COUNTIF(A2:A3,A2&"*")3、=COUNTIF($G$2:$G$14,A2)=IF(CO

17、UNTIF($G$2:$G$14,A2)=1,"是","否")九、COUNT,COUNTIF,COUNTIFS1、计数数据大于某个数字的个数之和=COUNT(B2:G2)=COUNTIF(B2:G2,">=60")云 COUNTIF(A2A3rA2&'银行卡号 2233888111122226706223828811112222223-COUmFCB2,G2hS-GO,卷血肉惧物-匚4住首D英修<woT忠039,0(50 066.0 1A过乞手后 王小二邨革朱大民疏Xd摩去三翌: X Y 启=(CO u N

18、T1 RS G $ 2: $G S14,A2T4、条件格式=COUNTIF($G$2:$G$14,A1)=0=COUNTIF($G$2:$G$14,A2)=0血a卜居于督之归现立所目正元和七带式匚八力士一丁丁匚丫立一昨.二亡节0新晒师格靠m舞后的都配闻豺a国?侑于由在于匚身迫却金正且喀之1骷父制-二 之r伊寻:E确字号君当帏饰逐元拓镉堤比九里口为特合北盘武前他循目精？t©)： CQUWTg(iG0tG$14AI)=。圄仲微；"卓尊AsEbCc格式G.I睢I学生名单吴雪洁乐呼涛S?梅桂丹敲菸.fllr专聿匚硬壬脏/ V雪晶志郭庄士fflH-HS禺苏赵l+.lr史赵荆9.马子D

19、数酷ar境三镉年务3七口1_1'口网匚二(1)上5、数据有效性=COUNTIF(C:C,C1)<=1L . 31c.11问题工k。列涅宜为禁止榴人重复敷掘值五1问题2畤七1网设置为禁二输入重复敷明,9 47 g S 10 11 1 n十、时间计算分钟数=HOUR(H2)*60+MINUTE(H2)+SECOND(H2)/60-、IF&IFERROR AND,OR=IF(OR(AND(A20=切”,B20>=60),AND(A20="女",B20<=40),1000,0)hf(7T 上. V:C2Q：A=JF(OR(AH吏员2aM男二m？t&

20、gt;> = 60 AN口(人眸M'1300.0A_B C D 卜H16 .1g19 桂别 华爵,侬.三仃函的黄先理清用路很重复20 里后女水瑟干60学以上的臭员工藁如岁以下的女员工给予L M口元津金E1衣的0取鼠潴口E3 惶3； IQQ24| 匿nr I十二、LEFT MID FIND1、时间计算为分钟数公式：时”,C2)+1,FIND("分时",C2)-1)分",C3)-1)=LEFT(C2,FIND("小”,C2)-1)*60+MID(C2,FIND("”,C2)-FIND("时",C2)-1)时二1&#

21、163;5丁£2下岫("小",C2)-1)分=乂曲©2下山口("时",C2)+1,FIND("分",C2)-FIND("#=MID(C3,FIND("分",C3)+1,FIND("秒",C3)-FIND("A=LE Fnc 2Ppm 口小*.C3-17HH MIDtCJ.FINDfffir". C2H l1ANDra*,?J-HWD(<!H*,<7J-DA日CDEF0H1JK罡立加 E*0£Jt甲硒1隹和氮%叮分也5&

22、;4廿卜广4g花的段1幽.骁科3 |744!仁2422-=<-建小见5法尸F；3OS-1757285715TirtiT -l I LO7tT*1'j£2k_3ia4l :a -rUtlf-TUST.-SPF E."!FHFno¥A3 EFT (A3 加w8£ ：-R1GHTCE34I AHc1_ EF .GH产品标号处理后.赢产品综耳江型人程后盲手文京央绛号音雷手72U0丁 ； I 斤.二.旧电？ 3E F H I产益瓶身后四叵坛码sh3L372OO313sh3OE19SO3兆 .sh330306S330sh2847219?B4产品标号处呷

23、JL=RJGHT(L£TT：电国EF |rJK产品标号处理sh3137200L乱3 一1100参数。对提取数据没有影响十三、时间函数：date1、分钟转变为小时2、小时转变为分钟=(E9-D9)*60*24-I ； I X W &=(E9-D9r60*24AB C D EF G开始附间结束时间 持续时间(分钟)(9:0012:00 |10！3、DATES数 =DATE(YEAR(B5),MONTH(B5)+C5,DAY(B5), G越GrilGI 就* t c* t Y z |工B电叵卜Vh彳长二 DAT E (YE AR(B6L MONTH(B6)+C6,DAY(B 6)B

24、CDE F开始日期间隔月份 结束日期2013/6/1642013/10/162012/B/852012/11/84、本月天数=DAY(DATE(YEAR(B21),MONTH(B21)+1,0)jitA =DA¥(DATE (YEAR(E21LMO NTHB21 4-1 rOJABcDEFG开始日期本月天数2013/6/10302012/8/8312008/2/11292009/2/1128祠6、工龄，日期与日期之间相减 =DATEDIF(B5,C5+1,"Y")g1X.Ji1pi= DATTEDIF(B5C5+1/Y>BCD入取日期离职日期工静2002/9

25、/102012/9/10 1107、计算年、月、天=DATEDIF(B13,C13,"y")&"年"&DATEDIF(B13,C13,"ym") &"月"&DATEDIF(B13,C13,"md")&"日"&-DATE OIKBL3,C13ryii( % '&DArE DIRB13C13/yiri"' &R" &DATE DI FfB13.C13；md')&am

26、p;' = HEC£EFGH久跑E期直取日削间隔2002/9/102003/7/5E#娟25天5三9月25 m 12004/5/16203/2/214年昨15夫年明5日8、计算日期是第几周 =WEEKNUM(B3,2)L 二 WEEKNUM 阴 aD9、计算日期是周几 =WEEKDAY(B8,2)10、=TEXT(B3,"aaaa")=TEXT(B10,"0000-00-00")*14 =TEXT(B3/aaaa')k B, C ,D E F G H日期 周几，: F -AEXT(B 10. -OOOO-OO-OOTlI2012

27、/3/10 侵源六一 B I C D E假日期 转化为日期2013061612013/6/16假日期转化为日期201306162013/6/16十四、条件格式 将数量大于100的项目日期标记为红颜色背景AU 产品关别Li秋里所属因中JJV2DD7/3/21苏州宏物月品1选祥融娱监：200 W28弥州窟物用品4。aor/VEO苏州嵬物用向2D2基于各3直跑所套隼元格如格式2005721外州宠物月品2DJC4 口为包W以下内吞的单三格设量格式：20D*/6/13苏州宠相月品H2仅对E名制或靠心的装值愎置格式soovr/iG苏州闻丽用E00I便对而于或低于平境值用数值域置惜父socr/q/i a苏州

28、窃物用品100I-取逸函通宜值登3格H苏州宠物月品2C0麟使用公式标要翟田的单元格2007/11/20苏州宠物且品4C0(200f/3/El蝴或物用品212编辑堤比说呜Wi：200VVEal希我物用品£243为符含此公式随菅婚格式；2OQWE0解茂物网用笆2200/5/31常熟宠物用品1002=C2>100圈2007/6713尚龙的同品闻22jC0T/r/ia蝴宠物月品1002CO7/9/14领宠物月品721福兽：制致成新必!吐亡亡I格式旧*动态图标的制作1、开发工具-插入控件-定义控件 2、定义公式名称：=IF(Sheet8!$J$11,Sheet8!$B$2:$B$7,Sh

29、eet8!$N$2:$N$7)=各区域名称3、图表选择数据：数据二各区域名称宏表函数=GET.CELL(63,A2)1800二 F0RMIUIE 工I菱白字巨舌卤回三士100010001、63=计算单元格的颜色B田二E后国由5虻制s博用K3期用ii> iMFriXTCMTW.FORMULATEXT*ppr於z,受gc< Jj市运律百t而50479lfi2E334.9804,其4而127174B5Q即再的42453&13.35T206372.B25164.239J6TO. 7狂.蚪12&4H72J轴士M-21.的45力:MW吃上“77B4&5131随的决5a4

30、H见西12204.1L32BfiLB2TRUE因艮士框1上FAL&L"S1FUSE口 251TRUE1mst口奴粮1TRUE132fflWE用中程1TRUEM建之植15SOJCd » c, tT打工也江* y V0曳-=trtn»ABCE1全面公式I 12000 J1飙,一32095QTuflCr4200001CW520006BQOO6SOW01CMi篡0O0LM)82000610049其效01QD010200010CM)11Z0OTQ10001320000100013200061KO14二。於D1531000L三由T七屉3、写超链接=HYPERONK(n

31、htpp:/iJ"|1"r)BCDEI目录出1I i云茸日报！r | 网易I市场组周环比情况! al =|=mTEPL 工邺"IHYPERLINK或或链接r以更打产T存信在硬善、网空葭务骂或Internet上的文出4、快速建立超链接目录1.公式-定义名称=GET.WORKBOOK(1)2 超链接=HYPERLINK(INDEX(作名称，ROW()-1)&"!a1")5、计算文本=公式-定义名称=EVALUATE(B2)ABC2公式计算结果33+7+24504412*17=*64130565144(22-17)/511.G66676?3语

32、文,数学,英语息分9范 88, 951089, 9% 99111 O70,她加弓毛立置刃:-EVALUAT (EVALUATE !C2)6、SUBSTITUTE除字符串中的部分字符用以新字符替换1、=EVALUATE(SUBSTITUTE(A9,",","+")引用位置回：二 EV或LMTE 区U BSTTLmE；EVALUATEU币2、=EVALUATE("&B9&"")把数字变成数组1(22-1 7)/611.66667语文，数学，英语总分刘瞅352飞2731典她992372£770, 30,

33、9024J240弓用谢园;=EVALUATE人吧 E9ft 丁)3、数字无符号提取某一个数字=EVALUATE(""&SUBSTITUTE(A16," ","")&""尸 数据 3 =INDEX徵据 3,4),乖- i | * 丁 k 、山宇国福工明| J itBUJeETXLU扁TEfEWJ工士|肿 n0也uj/DErniusMa.工f K£W-OALU 鼎 TtCTfiu1 «a swi2u=EVALUATtrf4r.IF&H良13 1口日Rf 1imnkTfL .二f24cs 新？W二 kawMtg HP15能振寿取用田辕判摒IS |u 12 U3 1 融 15 161JLT L2 L3 L5 14 121 16U441416 13 L2 H 15 111UJ419i?0=FVtlUJLTf | -Ca4ua-CTTTUTEi %MU*T6 阳圣“，曲甘4、点击一个单元格，同列同行会变成一个，颜色设置好格式后按住F9后点击单元格1、公式-定义名称-当前单元格=REFTEXT(ACTIVE.CELL()&T(NOW() 2、条件格式歹COLUMN(尸MID1(前单元格，FIND("C",当前单元格)+1,100)*1行