EXCEL基礎公式合集

1、=VLOC KUPH2&*匚致整源出E4W一、Vlookup1、數(shù)值查種文本公式=VLOOKUP(F4&"”,$A$2:$C$6,3,0).I A=VLOQ MJ P(F4SC'" F$ A$2; tCStj 3,0)ABCDEFG編號發(fā)斐地點出斐數(shù)量根據(jù)編號查找出貨數(shù)量'1001上海953 0021誨64編禺出貨數(shù)里r1003上海1031003103000上海1110041110051海9100592、文本查找數(shù)值公式=VLOOKUP(F12*1,$A$10:$C$14,3,0)VLQQKUPCF12*L$A$：ia$C$1430)ABCD

2、EFG編母發(fā)貨施點出貨數(shù)里根據(jù)扁旨宜找出貨戴里1001上海951002上海64出甯桶乙1003上遵1031003 1LQQ4上海11WQ4111005上海g100593、文本數(shù)值相互查找公式=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、批量查找和原數(shù)據(jù)順序一致的數(shù)據(jù)VLOOKUP+COLUMN(數(shù)公式)=VLOOKUP($D數(shù)據(jù)源!$A:$K,COLUMN()-3,0)-VLOO KUM1D4 J

3、0EB3 KCC LUMhO'lO)5客戶不公司科評笆g/.一拄口展石人十舒刑期應國CJCTS浩天而i亍社方失生物主白a路314工天津華北EKKSHiF.人資源附小俎望曾經(jīng)方靜工床位52科|華南買胃仁加力二日酎小城工匕也芍轉經(jīng)醫(yī)赤恚酉街3期南昌塞aL尺 Tnn匕華宗6、批量查找和原數(shù)據(jù)順序不一致的數(shù)據(jù)使用MATC蛋找表頭在原數(shù)據(jù)是在第幾個=VLOOKUP($A數(shù)據(jù)源!$A:$K,MATCH(B$2,數(shù)據(jù)源!$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坦森行貿(mào)易黃臺北掰7S0耳 王炫的 (032U 555393Z石家莊二、Lookup1、個稅計算公式，數(shù)組計算=MAX(F7-3500)*$C$6:$C$12-$D$6:$D$12)公式輸入完成后計算需同時按ctrl+shift+enter67V丘=MAX(H-350DrSCI6:tCS124DU:lDS12)ABCDEFGH1234起犯點35005謂3內稅所薄粒(元)附速兄1數(shù)1元)計算個稅60不超過15000稅前月

5、麻715001500-450010%1056500195345004500-900020%555200003120990009OO0-3SDOO25%1005S00034S2、用數(shù)組查找值=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 考三成境猊計表卜艮居書生姓名查找總分專業(yè)突姓名士、河原始分姓名原始公_理工汪梅男本地599注悔990rr理工1醒文市地661弼理工林博男外星4C7朱健男本省31

6、0文科李明女本省闞3、數(shù)組精確匹配 =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(數(shù)據(jù)源舊:B,MATCH($詢!A2,數(shù)據(jù)源!A:A,0)X 4 親=INDEX（款據(jù)源出B.MATCH（直詢iA2.數(shù)據(jù)深1dA網(wǎng)）ABCDE客戶口公司鈿:CHOPS落天膝行社浩天旅行社ERHSH正人資源正人資源HANAE實翼頭翼ANTON坦森行貿(mào)易坦森行留易t i i rrrrk2、利用MATCH&INDEX找提取圖片1、自定義名稱，引用位置：查找照片位置的公式，公式名稱設置為：照片2、使用照相機，點擊照相機，隨意點擊單元格，將框拖拽到需顯示照片的單 元格3、點擊圖片框，修為改二照片四

8、、INDIRECT1、跨表提取位置相同的數(shù)值=INDIRECT(A4&"!g2")2、跨表提取位置不相同的數(shù)值=VLOOKUP("長三",INDIRECT(A4&"!A:G"),7,0)；點=VLCOKU P(H5£ZH. N D1RJEC7 (AJ 1A; GH) r7,0)A B C D第二步張三的業(yè)績 =¥LOCXUP張三小用血7,6 1n果員工姓月份 業(yè)績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、在多數(shù)據(jù)中，求和一段時間的數(shù)據(jù)=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網(wǎng)既搬5郵骯1狐JLUD山加仙1腑喇瞰m跳9螭撩如M產(chǎn)卜;7：柏Timifiiiihj urnAi

11、11j1 n Miu »innrwflmnnA a mi nflJ 1 fliliRrnirr六、高級篩選功能需要把A表的數(shù)據(jù)篩選復制到B表，需要在B表執(zhí)行高級篩選的命令。七、&符號的使用1、文字或鏈接與單元格鏈接="你好，請問” &B2&在哪個城市的?二.你好r清間也瞪St.在哪個域市的? C0八、SUMIF&SUMIFS1、查找對象數(shù)字大于15位，查找對象需加上&" *"=SUMIF(A:A,F4&"*",B:B)£制理二 V二=GLM：F(AAF3&e 占 EBC

12、D才1FHG存入金額31000銀行卡號金額統(tǒng)計210C0"62238888111122226731(00131000*322344448888?777212100021000 2731111777苣 222563200Cr3WC012333S2227LB393922C0C2、多行數(shù)值相同求和，最后參數(shù)為求和項的第一個目錄=SUMIF($A:$J,$L3,$B$1)己與，C* Y卻Z時底，V ；MW-a -SUMTFfJiJjLiWWBC EFGHIJKL.rJ 73發(fā)士璃科日如舟發(fā)!US科副分£±«料日劃分m(K匣g士博工三者岡車r五噌百ISr lS R

13、OrHMi窗雷；4.Pt1巧克科Mg善二國L程a制文三二二'=3音4S址2賽(目工正如毛單身弁工優(yōu)塢費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發(fā)生收軍1挈周

14、裝產(chǎn)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部門科目卻分發(fā)生強：L匚，iE-0022二不同M伊士雷整50. O0一奉向尤克費S3129iE-OT23二本間運空上工56 OO二室衍隆子身0912i記一806打存壬花于女65. 00二主怕123i25、SUMIRE數(shù)值和可替代 VLOOKUP =SUMIF(A:A,M4,J:J)-SUMIFiAAFVWJJl?EFGEIJKL宛忖工資朔他施發(fā)工費?。盒∪司疤锏奈餍【拖喔綍r品項 劣境散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)解釋：出貨數(shù)量相加小于庫存才可以填寫數(shù)值tlM居&r如產(chǎn)f鼓二*Kl用三M1UK509婚, '”1 .0住餐守亞D咨礫品工置忖<13615M金忘lEgitf事0泄玩L«OD舊也的10顯/LM。Dft£宓咄E5 HJuWRlf.r IFllL Ki- iLMF'IAl 困2、數(shù)據(jù)超過15位數(shù)，使用&” *=COUNTIF(A2:A3,A2&"*")3、=COUNTIF($G$2:$G$14,A2)=IF(CO

17、UNTIF($G$2:$G$14,A2)=1,"是","否")九、COUNT,COUNTIF,COUNTIFS1、計數(shù)數(shù)據(jù)大于某個數(shù)字的個數(shù)之和=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卜居于督之歸現(xiàn)立所目正元和七帶式匚八力士一丁丁匚丫立一昨.二亡節(jié)0新曬師格靠m舞后的都配聞豺a國?侑于由在于匚身迫卻金正且喀之1骷父制-二 之r伊尋:E確字號君當幃飾逐元拓鎘堤比九里口為特合北盤武前他循目精？t©)： CQUWTg(iG0tG$14AI)=。圄仲微；"卓尊AsEbCc格式G.I睢I學生名單吳雪潔樂呼濤S?梅桂丹敲菸.fllr專聿匚硬壬臟/ V雪晶志郭莊士fflH-HS禺蘇趙l+.lr史趙荊9.馬子D

19、數(shù)酷ar境三鎘年務3七口1_1'口網(wǎng)匚二(1)上5、數(shù)據(jù)有效性=COUNTIF(C:C,C1)<=1L . 31c.11問題工k。列涅宜為禁止榴人重復敷掘值五1問題2畤七1網(wǎng)設置為禁二輸入重復敷明,9 47 g S 10 11 1 n十、時間計算分鐘數(shù)=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、時間計算為分鐘數(shù)公式：時”,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(xiàn)；3OS-1757285715TirtiT -l I LO7tT*1'j£2k_3ia4l :a -rUtlf-TUST.-SPF E."!FHFno¥A3 EFT (A3 加w8£ ：-R1GHTCE34I AHc1_ EF .GH產(chǎn)品標號處理后.贏產(chǎn)品綜耳江型人程后盲手文京央絳號音雷手72U0丁 ； I 斤.二.舊電？ 3E F H I產(chǎn)益瓶身后四叵壇碼sh3L372OO313sh3OE19SO3兆 .sh330306S330sh2847219?B4產(chǎn)品標號處呷

23、JL=RJGHT(L£TT：電國EF |rJK產(chǎn)品標號處理sh3137200L亂3 一1100參數(shù)。對提取數(shù)據(jù)沒有影響十三、時間函數(shù)：date1、分鐘轉變?yōu)樾r2、小時轉變?yōu)榉昼?(E9-D9)*60*24-I ； I X W &=(E9-D9r60*24AB C D EF G開始附間結束時間 持續(xù)時間(分鐘)(9:0012:00 |10！3、DATES數(shù) =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、本月天數(shù)=DAY(DATE(YEAR(B21),MONTH(B21)+1,0)jitA =DA¥(DATE (YEAR(E21LMO NTHB21 4-1 rOJABcDEFG開始日期本月天數(shù)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十四、條件格式 將數(shù)量大于100的項目日期標記為紅顏色背景AU 產(chǎn)品關別Li秋里所屬因中JJV2DD7/3/21蘇州宏物月品1選祥融娛監(jiān)：200 W28彌州窟物用品4。aor/VEO蘇州嵬物用向2D2基于各3直跑所套隼元格如格式2005721外州寵物月品2DJC4 口為包W以下內吞的單三格設量格式：20D*/6/13蘇州寵相月品H2僅對E名制或靠心的裝值愎置格式soovr/iG蘇州聞麗用E00I便對而于或低于平境值用數(shù)值域置惜父socr/q/i a蘇州

28、竊物用品100I-取逸函通宜值登3格H蘇州寵物月品2C0麟使用公式標要翟田的單元格2007/11/20蘇州寵物且品4C0(200f/3/El蝴或物用品212編輯堤比說嗚Wi：200VVEal希我物用品£243為符含此公式隨菅婚格式；2OQWE0解茂物網(wǎng)用笆2200/5/31常熟寵物用品1002=C2>100圈2007/6713尚龍的同品聞22jC0T/r/ia蝴寵物月品1002CO7/9/14領寵物月品721福獸：制致成新必!吐亡亡I格式舊*動態(tài)圖標的制作1、開發(fā)工具-插入控件-定義控件 2、定義公式名稱：=IF(Sheet8!$J$11,Sheet8!$B$2:$B$7,Sh

29、eet8!$N$2:$N$7)=各區(qū)域名稱3、圖表選擇數(shù)據(jù)：數(shù)據(jù)二各區(qū)域名稱宏表函數(shù)=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 | 網(wǎng)易I市場組周環(huán)比情況! al =|=mTEPL 工鄴"IHYPERLINK或或鏈接r以更打產(chǎn)T存信在硬善、網(wǎng)空葭務罵或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、文,數(shù)學,英語息分9范 88, 951089, 9% 99111 O70,她加弓毛立置刃:-EVALUAT (EVALUATE !C2)6、SUBSTITUTE除字符串中的部分字符用以新字符替換1、=EVALUATE(SUBSTITUTE(A9,",","+")引用位置回：二 EV或LMTE 區(qū)U BSTTLmE；EVALUATEU幣2、=EVALUATE("&B9&"")把數(shù)字變成數(shù)組1(22-1 7)/611.66667語文，數(shù)學，英語總分劉瞅352飛2731典她992372£770, 30,

33、9024J240弓用謝園;=EVALUATE人吧 E9ft 丁)3、數(shù)字無符號提取某一個數(shù)字=EVALUATE(""&SUBSTITUTE(A16," ","")&""尸 數(shù)據(jù) 3 =INDEX徵據(jù) 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(前單元格，F(xiàn)IND("C",當前單元格)+1,100)*1行