大学生计算机基础考试题库

《大学生计算机基础考试题库》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

⍝⚪1⌱⚗ᫀ2Excelᦪᐙ1.ᑡ!"#$ᵨ&ᑡ'(ᩭ
஻ᦻ,⌱⚗”"/0⌱⚗""1ᐭ⌱⚗"“45⌱⚗"2.᝞7◤⌕:ᦪ;<ᦻ=ᜐᳮ!᝞ᵯABṹஹEFGBHI!JᐜLᐭMN!OLᦪ;!ᔲᑣExcel:ᐸ4ᦪSTᦪ!UVW;XT஺'MᓫᥗBN”M]ᥗBN,M⌹BN`MᑖBN3.ᙠExcelc!ᢥMN"#LᐭefgᜩḄjk.Ctrl+;Ctrl+Shift+;Shift+;Alt+;4.⌕ᙠVmᓫᐗLᐭomḄᦪ!✌ᐜ⌱cqᓫᐗ!ᯠsᙠ⌱cḄgtᓫᐗcLᐭᦪ!LᐭuᡂsᢥMNwxyz{ᙠᡠᨵᓫᐗLᐭ~ᦪ஺Ctrl+EnterAlt+EnterShift+EnterEnter5.ᙠExcelc!$ᵨᐙuᡂᐙ!ᐙᓫᐗḄMN&&6.Excelc⌕⌱VoḄᓫᐗ!ᵨMNwᔠ᪗

1¢ᜧ~ᓫᐗḄᑡ¤¢ᜧ~ᓫᐗḄ¥¦$ᵨ§ᑴ'(§ᑴᦪ©ªLᐭᦪ8.ᙠ⊤c®ឋ1ᐭ3¥!Ḅ±²WMN⌱3¥!ᙠ"⊤”³ᓫc⌱´"1ᐭ¥'''(µ²yz⌱´''⊤"³ᓫcḄ"1ᐭ¥"'(¶1ᐭ··ᙠ¥¸¹!ᢥº»9.ᙠExcelc!"#LᐭÂÃᦪ!ÃWÄ!ÅÃW
ᦪ;ᦻ=Æᦪ10.ᙠLᐭᦪ;ÉᓫᐗḄᦪI!ᑣ#MNÊËᦪÌᑖÍÎÏ11.ᙠᣚ¥ÑI!"#ÒᢥMNᩭÓᑴᣚ¥alt+enteralt+shiftalt+tabctrl+enter12.᝞7⌕A2:A9áâcᒹäᦪSḄᓫᐗḄᦪ!"#"=countaMA2:A9N”,èA7ᓫᐗWêë!ᐸìᓫᐗíᨵS!ᑣ7MN786513.g᪗ðᑮᐙI!᪗ᢣ┐ôMNõᓝ;]÷ᜮëᓝ;õùú14.üýᓫᐗḄü"ÌᑖÍ"!þᦪ·ÿ2
ᵨᡝᔣᐸᐭ38ᓫ᪾ᓫᐗᑁḄᑁ

2᪾38%,ᓫᐗ38.00%᪾38,ᓫᐗ38%᪾0.38,ᓫᐗ38%᪾3800,ᓫᐗ3800.00%15.(ᵨ“*+,ᐙ''/0ᐭᦪ234ᙠA1ᐭ2,A2ᐭ4,ᯠ⌱A1:A2;<=>+,ᐙ?@A10,ᑣAlCA10;<ᑁᔜᓫᐗ,ᐙḄᦪ22,4,6,....20ᐰ0ᐰ2ᐰ416.Gᓫᐗᢣ┐JᑮAZ1000ḄᨬNᓫḄ/0OᙠPQ᪾ᐭAZ1000,Rᢥ>+T+ᩩᢥ+ᐜᵨ+XJᑮAZYIJ,=ᵨ+IJᑮ1000\17.⌕ᙠX_ᓫᐗᐭᦪ2`_ᓫᐗabOcᓫᐗdḄabefgᦪ2hi\✌ᓫᐗ18.ᙠExcelocᓫᐗᐭᦪpiᦪ23qrstuvtuwxy19.ᙠExcel᝞|ᐭ}~ᑣqrvtustuwtu20.᝞|⌕ᙠ⊤ḄDᑡEᑡᐭXᑡ⌱ᯠᓫs⌱”ᐭ”஺EᑡDᑡDᑡEᑡᑡ21.ᙠᓫᐗ4⌕ᐭᦪp1/2,ᢥ/ᐭ

301/21/2=mod(l,2)0.522.ᙠᐭ*+vtuḄO()ᦻ}ᦪp33Excelᵨ¡ᦪ2ᜐᳮ23.ᙠ¤ᨵᡈ§ᦪḄᓫᐗ¨©“#NAME?”3⊤()ᨵᦻ®¯0°±┯³ᦪ2´µᵨ¶ᑖ¸24.4A1:A5¹PXI,ᦪpᑖ±O10ஹ7ஹ9ஹ27ஹ2¼C1:C3¹PAXI,ᦪpᑖ±4ஹ18ஹ7oᑣ=4¾£À6£஺1,ÁÂ1)Ã|:10.514.522.54225.ᢥÄXᑡᑖhtÅ᜜4ÇᑡÈ\ÉÊḄËO()oᑖhÌÍÎ⌱ÏÐÎ⌱᣸Ò26.ᙠExcelḄefabÓ()Ôᜮ஺*27.ᙠExcel4ᓫᐗÖᵨxᡠᙠᓫᐗ
ØḄÙᓄÛᦋÙᑣQÝᓫᐗḄ()஺ÞtÖᵨ

4ßtÖᵨXàÖᵨáᔠÖᵨ28.ExcelᓫᐗᙢᙬḄßtÖᵨ◤⌕ᙠ\æᑡ᪗c☢éê஺$&#@29.ExcelᵨḄᓫᐗᙢᙬ⊤îoᑡ᪗é\æ\æéᑡ᪗\æᑡ᪗30.ᙠExcelïX_ᓫᐗᐹᨵtḄñὃᙶ᪗QÝ஺ᓫᐗÖᵨ
ØᓫᐗᙢᙬḄÞtÖᵨᓫᐗᙢᙬḄßtÖᵨᓫᐗ;<31.ᙠExcel2010ôḄPQõᙠö÷Ḅ᪗⚪ùêqrḄPQ஺BOOKIsB00K2-DOCXSHEET!,SHEET2-XLSX32.ᙠExcelᡠᨵᦪḄᐭ!"#ᩭ%ᡂḄ஺ᓫᐗ*+,⊤+,./033.ᙠExcel2010+,.Ḅ34᡽67"#:.XLSX.DBX.DOCX.PPTX34.ᙠExcel?D5ᓫᐗ*ᐸB?CᵨḄ⊤EFG"#$D$5D$5D5$D5

535.ᙠExcelI,᝞Kᓫᐗ*LM“#DIV/O!”ḄTUV⊤E—XYLMZ[◀ḄM]ᓫᐗ*Cᵨ^ᦔ`ᨵaᵨᦪbcKdeᓫᐗ*fghi36.᝞ikᡠELlᝄᐸᐰlᝄo500ᐗpᎷrsᡷ20ᐗuᎷrsᡷ50ᐗv+rsᡷ100ᐗ᝞Kᡷwᔠᜧz500ᐗᑣLlᝄo0ᐗ஺iᑡ}~Ḅ"#ABCDEFGH1
ᎷᎷᡷᝄ210NM100010150316NM10100010413NM1020202100512NM103ᾠ⛸0012100614NM10400010710NM105ᩰ010150815NM1060112150916NM107ᑢ!"000201017NM108#$02112003216NM130%ᵂ010350=IF(SUM(C2:E2)=O,500,IF(500-G2<=0,0,500-G2))=IF(SUM(C2:G2)=0,500,IF(500-G2<=0,0,500-G2))=IF(SUM(C2:E2)=0,500,IF(500-G2>0,0,500-G2))=IF(SUM(C2:G2)=0,500,IF(500-G2>0,0,500-G2))37.ᙠ⌱ᩩ()஺ᦪᓫ◞rᡈrᑡᙠᦪᓫḄᡈiᙠᦪᓫḄᙠᦪᓫḄ38.ᙠExcelI,¡¢£ᓫᐗ*LM"VALUE!”ḄTU¡ᐸ¦§"#ᙠXY¨ᵨ©┯«Ḅᦪ¬ᙠXYᓫᐗ*Cᵨh®ᨵᦔcKde©¯!©ᓫᐗ*°±ᓫᐗ*Ḅᦪbdᜧ39.ᙠExcel¢ᓫᐗ*LM"#N/A”,VᢣᙠµᦪᡈXY`ᨵ¶"#aᵨᦪbZ[◀Z[·¸¹º»40.᝞ikᡠE"######"┯«¼½Ḅ¾¿:

6AB11900/1/121900/1/231900/1/341900/1/451900/1/561900/1/671900/1/781900/1/891900/1/910#####»##!11UUUU12UUUU13########ᑡ°hÀXY◀ᦪo[XYᒹ¦^ᦔbᓫᐗ*Cᵨ┯«41.ᑖ¬ÃĵᦪSUBTOTALaÅÆM?⌱cKÇÈFYḄÃÄXY^SUBTOTALÊD5:D41#ÆMḄË̶ÍᦪÍᙳb·Ï42.ᙠExcel?ᦪᑖ¬ÃÄÐÑ◤⌕?Ôᑖ¬ḄÕÖᐜ—®ÃÄ஺᣸ÙÚÛ⌱ᔠÜᦪÝÞ43.Excel?ᦪḄ⌱I,ᑖoÚÛ⌱⌱iᑡßÈËÌaÅᵨÚÛ⌱ÆM¶hàÕÖá"”ᩩḄâã஺hàÕÖá"ᡈ”ᩩḄâã஺àrÕÖÇäæḄ⌱஺ç⌱cK〈ᑮ¾ᦪ⊤Å᜜ḄᙢF஺44.ÅiᐵzExcelᦪᜐᳮ᣸ÙI,ḄïG}~Ḅ¶ᙠ᣸Ù!ð?hñòó᣸ÙḄᦪaÅᐜçᐸ◚õ®᣸Ù஺Excel᣸ÙI,öÌᢥÕÖb᣸ÙhÌᢥᓫᐗ*Ḅø⁐ᡈÕúø⁐᣸Ù஺ᙠExcelöÌÆMᓫÕÖ᣸Ù஺ᙠExcel^GÆMᢥ᣸Ù஺

745.ûü⊤ýYþ3000200ḄºcKo¶TrueFalse1046.2Ḅ4ᙠExcelᵨ⊤=2"4=2**4=2&4=2#447.&◤⌕)*+ᵨᓫᐗ./012345ᵨ67ᩭ9⊤:012;<=>6745ᑭᵨᩭ@ᡂ஺67᪾DEFᙢᙬFIJ148.ᙠLᐸNOP⊤Ḅᓫᐗ.QRSᵨ35A1UVWḄ.=OP⊤6!A1=OPY6!A1=A1!OP⊤6=A1!OPY649.ᙠExcelᓫᐗ.A1[ᐭ”=average("2”,6,true)”,efAlḄgh()஺324550.klVWḄm()=SUM(A2,B3,B7)=SUMA1:A5=SUM(A10.B5:B10:28)=(SUM)A1:A551.ᐵtExcel”uv”Lw᪾kᑡyzVWḄm()஺{45|vᙠ}~᪷k45|vᙠᦻᜳ45|vᙠᦻᜳkJ|vᙠᘤḄ᪷k|vᙠᦻᜳkᦻ652.⌕LᦪᓫḄᦪQRᑖ✌ᐜQRḄPm()»

8ᙠ⌕PᑖḄᑡQR᣸ᙠᑡQR᣸ᙠ⌕ḄᑡQR᣸ᙠᨬᑡ᣸53.=SUM(B1:B4)¡¢t().=SUM(B1+B2,B3+B4)=SUM(A1:B2A3:B4))=SUM(B1,B4)=SUM(B1+B4)54.ᙠ¤¥¦⌱⌕3¨©Ḅᩩ()஺«ᙠᩩ12ḄR«ᙠᩩ12Ḅᑡ«ᙠᩩ12Ḅ¬ᑡ«ᙠᩩ12Ḅ¬R55.+ᵨ®¯ᦔḄᓫᐗ.±²Ḅ┯´µ¶m()»#REF!#DIV/O!#NULL!#NUM!56.⌕⌱¿À¬ÁḄᓫᐗ.12Âᔠ+ᵨḄᢥÅm()஺CtrlShiftAltTab57.Ì&ÍOP⊤Al:A4Ḅά45+ᵨ()=SUM(A1+A4)=SUM(A1:A4)=(A1+A2+A3+A4)=A1+A2+A3+A458.ᎷÐAlᓫᐗ.ᑁÒ“Ô6"⌕ᓫᐗ.12A2:A10=>67"Ô6"zVWḄm()஺ᐸNÕ⚗z×L⌱A2:A10ᙠOP⊤ØÙḄ67[ᐭ67“Ô6”ÚÛÜ⌱Al:A10f=>Ḅ67f᪷ᡠ⌱ᑁÒÞß⌱¿✌Rᡈ✌ᑡW=⌱A2:A10=>Ḅ67=>67[ᐭ67''Ô6"W=59.[ᐭ=SUM(Sheetl:Sheet3!Al:A10),ᑣᐵtᐸ+ᵨḄᓫᐗ.SᵨyzVWḄmO஺Sheet1ᑮSheet3äOP⊤ḄA1ᑮA10

9Sheet1ḄAlᑮA10Sheet3ḄAlᑮA10Sheet1Ḅᓫᐗ.AlÎSheet3Ḅᓫᐗ.A1060.kᑡyzVWḄmåæ஺[ᐭ”=$A5+$A6”,⊤LA5ÎA6ḄᑡᙢᙬèLSᵨ[ᐭ”=$A5+$A6”,⊤LA5ÎA6ḄRᑡᙢᙬLSᵨ[ᐭ”=$A5+$A6”,⊤LA5ÎA6ḄRᙢᙬèLSᵨ[ᐭ”=$A5+$A6”,⊤LA5ÎA6ḄRᑡᙢᙬèLSᵨ61.ExcelḄéᓫᐗ.mᢣåæ»VᙠPḄᓫᐗ.ëᐸìᓫᐗ.ḄíᓄïíᓄḄᓫᐗ.ð)ᦋ®Ḅᓫᐗ.45ëòóḄᓫᐗ.62.ᙠExcel+åæ+ᵨèLᙢᙬ஺ᓫᐗ.ᙢᙬ¬ëôõöïíᓄᓫᐗ.12ëôõöïíᓄᓫᐗ.ᙢᙬëôõöïíᓄøᜧúëôõöïíᓄ63.kᑡûl¬VWḄmåæ஺ExcelüäýOP⊤ᓫþ|vᡂᦻExcelḄÿ⊤
ᡂ⊤Ḅᦪᜐᙠ⊤Excelᐕᒹ⊤ExcelḄ⊤!"#$%64.*+ᨵ᪗⚪/Ḅᦪ0ᓫ2/᣸456ᙠ"᣸4"*8᪾:ᣵ”ᦪᒹ᪗⚪”=⌱᪾Ḅ*?@5᪗⚪/AB஺D᣸4
D᣸4EFGᙠH/EFGᙠᨬJ/65.LMNOᵨQRᓫᐗTAC3:D7,A2,F1B,_`MNᒹḄᓫᐗTGᦪaAB஺12641666.ᙠD2ᨵMN=c2+82,6fgMNhᐙᑮE2J5MNklᡂ:=B2+C2

10nA$2+B$2=$A$2+$B$2=$A2+$B267.pq⌱rᜧuḄvwxy5z{|}~ᐜᓫxyḄᓫᐗT5ᯠJᢥABᓫ*ḄᓫᐗT஺ShiftCtrlAltTab68.*⌱ḄᓫᐗT2/0◀zfAB»0◀ᓫᐗTḄᦪ0◀ᓫᐗTḄᦪᓫᐗT0◀ᓫᐗTḄTN0◀ᓫᐗT᪾69.⌱⊤ᐰᓫᐗTḄ{|~ᓫ⊤ḄAB஺/ᑡᜐḄ¡¢{ᙽᑡ᪗¤¥¦ḄᓫᐗTᙢᙬ/?70.6ª«ᐭḄᑁ®~¯°ᙠAB஺±²ᓫᐗTᓫᐗT%³᪾¤¥¦71.ᙠExcelMNᐕ´ᵨḄᦻ¶·¸~஺&$#D72.ᙠA2ᓫᐗT«ᐭ4.1,ᙠA3«ᐭ4.3,ᯠJ⌱A2A3,º²hᐙ»5ᔣ½hᐙ5ᡠ¿ᑮᦪÀ4ᑡ~஺ÁÂ4ᑡÁÃ4ᑡ᦮ᦪ4ᑡÅÆ4ᑡ73.ᙠExcel2/z5pLᓫᐗTÇÈ"#####"Ê5~ËaAB஺Ì·ÍÎÏÐÑÒQᓫᐗTÓÔᓫᐗTḄᦪÀÏᜧ

11ᙠMNᓫᐗTOᵨ
ᨵᦔᙠMNOᵨQ┯×ḄᦪØÙ74.½ᑡᓫᐗTᙢᙬ5ÚÛÜᔠOᵨḄ~ABo$B1$B$1BlC75.¤¥¦Ḅᵨß⌕~AB஺ᐸâã⚗å|æçè!éêMNஹìᐭíᦪ!îᦋᦪMN!«ᐭᦪ76.ðFA1ᓫᐗTᨵᦻ஻ãò”5B1ᓫᐗTᨵᦻ"ó▾”5⌕qᙠC1¿ᑮ஻ãòó▾”5½☢ᙠC1«ᐭḄMNçèḄ~=A1&B1=A1*B1=A1-B1=A1+B1H4øExcel⊤ùᵨ77.!½ú⊤*Ã5ûQüýzABþÿὁ
llllllllllllllI■:ಗ/⚗ᦋᙶ᪗ᦋᙶ᪗ᦪ᪗78.⊤!"#$%&'()*

12s+,ὅᢣᦪᨵ)᪷ᦟ22M2222000O00000030&BRB0cB&-02S0560709/0123456/07589:79.<=>?$@ABCᔜAEBCḄ├HIḄJK⊤"LMN⊤O"⌕Q⊤Ḅ"RST)*஺⌱W"YZ"⌱⚗ᓱḄ''"]^"_`Ḅᑡ⊤bcd⌱Wefᙠ⊤h⌱W"/K''⌱⚗ᓱḄ"”]^"_`Ḅᑡ⊤bcd⌱W⌱W”jᐭ஻⌱⚗ᓱḄ"ᱏ”]^cd⌱W80.ᙠExcelb"Q⌕tAᦪuᑡᑮ<ᨵ⊤b"RxMḄ'(yz{)*⌱b⊤"᡻d”/Kt⌱Wᦪ”}ᓫ]^"ᙠᐸ᪾bᦪuᑡḄᙢᙬᙠᐭ⊤Ḅ(⊤b⌱Q⌕Ḅᦪ"ᯠᐸefᑮᐭḄ⊤bᙠᐭ⊤Ḅ(⊤b⌱Q⌕Ḅᦪ"᡻d”jᐭt⊤”}ᓫ]^"ᦪᑮ<ᨵḄ⊤bRxM81.⊤!"#$%&'()*/0$ᦪ᪗/0$/0$758/0$⊤82.⊤!"#$%&'()*

13IMᑴᜩ=■-^1-C$⊤$$758/0$⊤3483.⊤!"#$%&'()*ᑗᣚdᑡ/0ᙶ᪗᪗⚪ᦋ⊤84.⊤!"#$%&'()*>⊤᪗A3᱐CDEF1ẑᦋ⊤/0ᙶ᪗/0⊤᪗⚪

1485.⊤!"#$%&'()*2•;’3%¡.1ᔺ£•¤ᦋXᙶ᪗¦y6ᦋYᙶ᪗¦y6ᦋᦪ᪗/0Xᙶ᪗᪗ᦪ86.⊤!"#$%&'()*⊤᪗⚪ᙶ᪗᪗⚪ᦋᙶ᪗87.ᙠExcel⊤b"©ª⊤Ḅe«ឋ"Rᵨ¯Ḅ)*⊤°ᦪ±஺²⁐´µᜧ£·¸88.⊤!"#$%&'()*758

15/034/0Yᙶ᪗᪗ᦪ/0Xᙶ᪗᪗ᦪ89.ᑡᐵºExcel⊤Ḅ»z"¼½Ḅ{)*⊤¾Rᐭᙠ¿À(⊤b"ÁÂ(ÃÄ(⊤ÅÆOzÇ(⊤bÈ
⊤⊤Âᐭᙠ¿À(⊤b"ÉÂ(ÃÄ(⊤ÅÆ⊤ÉÂᐭᙠ¿À(⊤b"Â(ÃÄ(⊤ÅÆ90.⌕ᦋÊË°ᙠ(⊤bḄ⊤"Ìᙠ)*⌱⚗ᓱÍ⌱btAÄḄ⊤஺jᐭᡈ/KYZ56Ï91.ᑤ◀(⊤b⊤ᨵÒfḄᦪÓ"⊤b)*Ô:ᑤ◀ÕÌḄᦪ±Ö×ᵨØÙᑤ◀ÕÌḄᦪ±ÚL
ÊᓄÜÝᑴ92.⊤bḄᜧßᦪ⊤⚗)*RÜ9:ᡈà᦮ᜧ£Â9:ᡈà᦮ᜧ£â:RÜ9:"ãÂà᦮ᜧ£93.⊤{)*(⊤ᦪḄ´⊤°᯿ᱏRᵨæᐹcdØÙḄ᪷(⊤ᦪᵨæᐹ1ᑴḄ94.ᙠ⊤b⌕©᪗⚪"ᙠêë⊤ḄìẠh"R)*᡻d”YZt᪗ît⊤᪗⚪"]^᡻d''jᐭt᪗⚪”]^᡻d“56tÔ:ᝅᵨ56ᓄ⊤”]^ᵨñ᪗ò"efóᐭ95.Excel⊤ḄË℉ᱯ±{(⊤bḄᦪÊᓄÓ"⊤)*ö÷ᦋÊ`MÊᓄÔᯠ+ᜫ

16
ᡂÄ⊤"Åᶇû⊤96.⊤!"#$%&'(()ᙶ᪗᪗⚪ᦋᦋᙶ᪗᪗/0⊤34þ5ÿExcelᦪ
ᑖ᪆ᦪ97.ᦪsum(30,"40")Ḅ()7040303598.B3=B1*B2,ᑣᙠB3ᓫᐗ()*+,ᐭ()product(Bl,B2)sum(Bl,B2)left(B1,B2)right(Bl,B2)99.᝞;<ᡠ>?ᑣC0UNTIF(C2:C9,100)GH()ABC130100210078345524886154560610045780100845779907312

1734100.᝞;<ᡠ>?ᑣCOUNTIFS(C2:C9,”>60”,C2:C9,"<90")GH()ABC1308522378345524886154560678457809684577980734356101.OᙠPQC2:C100)RSTU(ḄVWXᦪ?YZᵨᦪ()COUNTIF(C2:C100,"<60”)COUNTAIF(C2:C100,?z<60/z)SUMIF(C2:C100,"<609AVERAGEIF(C2:C100,"<60")102.OᙠPQC2:C100)RSTU(ḄVWᡂefᙳ?YZᵨᦪ()AVERAGEIF(C2:C100,"<60")C0UNTAIF(C2:C100,"<60")SUMIF(C2:C100,"<60")C0UNTIF(C2:C100,z/<60z/)103.᝞;<ᡠ>?ᑣCOUNTIF(B2:B9,"<>"&B3)GH()AB1302233454885456787808459805463

18104.ᦪsum(30,40)Ḅ()70403035105.᝞;<ᡠ>?ᑣCOUNTIF(B2:B9,B3)GH()AB1302233454885456787808459803214106.ᦪSUM(30,TRUE)Ḅ()31301535107.ᦪSUM(30,false)Ḅ()30311535108.ᦪROUND(533.8378,3)Ḅ()533.838533.837533533.84109.ᦪROUND(533.8378,1)Ḅ()533.8533.837533533.83

19110.ᦪROUNDk533.8378,0lḄkl534533.83533533.8111.ᦪleftk஻533.8378஻lḄkl553353533.8112.OᙠPQC2:C100)RSTU(ḄVWᡂenᑖ?YZᵨᦪklSUMIFkC2:C100,஻<60஻lCOUNTAIFkC2:C100,஻<60஻lC0UNTIFkC2:C100,?z<60?zlAVERAGEIFkC2:C100,஻<60஻l113.MidᦪopḄᦪ
qrsklturvwrᦪrxyr114.ᦪleftC533.8378஻?2lḄkl535335533.8115.ᦪleftk஻5338378஻?3lḄkl533535533.8116.ᦪRIGHTC533.8378஻,2lḄkl78878353

20117.ᦪRIGHT("533.8378஻)Ḅ()853578118.ᙠ)?⌕|ᵨ}~⊤sheet1ᑮsheet7)ᓫᐗ(A1Ḅᑏ(s()sheetl:sheet?!A1sheetl!sheet?!Alsheetl*sheet7!Alsheetl,sheet?!Al119.᝞;<ᡠ>,ᑣC0UNTIF(C2:C9,">=60")GH()ABC1308522378345524886154560678457809684577980736542120.opᦻtu)ḄᢣtuḄᦪs()midproperupperlarge121.OPQA2:A9PQB2:B9YḄ?ᑣZᵨᦪ()SUMPRODUCT(A2:A9,B2:B9)SUMIFS(A2:A9,B2:B9)SUMIF(A2:A9,B2:B9)SUM(A2:A9,B2:B9)122.ᦻtu¡ᨬ£¤¥¦ᢣtuḄᦪ()Leftupper

21largelower123.᝞;<ᡠ>?⌕ᙠF3ᓫᐗ()SW©ª?*+Zᵨ;ᑡ¬X()ABCDEF1
᫏ᫀ⊤2
HIᜧᑏLMN
OP310NM100▰Rchengxiu15452419800118001X416NM101STdouhai154627198712066921513NM102UVWfanfengxia154782198710020747612NM103XTYguohalying153330198703163960714NM104Z᫥jimei154428198503203071810NM105\]^liguoqiang155403198611080026915NM106\Yliying1546211986100938231016NM107ᴉlinhong1546261987092002271117NM108ᑢ`liubin1562031986071139671216NM109ᑢaliufeng156626198810066033=mid($E3,7,4)=left($E3,7,4)=mod($E3,7,4)=right($E3,7,4)124.ᙠExcelᓫᐗ(Al),ᐭ஻=sum(4,"5",false)",¯°Al)ḄGH()஺95410125.ᙠExcelᓫᐗ(A2),ᐭ஻=count(12,஻12஻?2016-06-01,false)",¯°A2)ḄGH()஺4312126.ᙠExcelᓫᐗ(A2),ᐭ஻=count(12,஻12஻?2016-06-01,false,#DIV/0!,஻ab஻)஻?¯°A2)ḄGH()஺4365

22127.B2=l,B3=2,B4=3,BᑡḄᐸ·ᓫᐗ(¸ᨵᑁ»?ᙠBl),ᐭ஻=count(B2:B5),¯°B1ḄGH()஺3214128.᝞;<ᡠ>?¼஻1½஻VḄnᡂe?ᑣ;ᑡ⌱⚗)ÀÁḄs()期A姓名BC11王利文ᡂc22张强8031刘艳艳3042陈好林9551刘伟7062李强5071谢东6082黄萍829100sumif(A2:A9,??l½஻?C2:C9)countif(A2:A9,z,l½”,C2:C9)sumifs(A2:A9,"1½஻?C2:C9)countifs(A2:A9,½஻,C2:C9)129.A1=LA2=2,A3=l,A4=l,Bl=3,B2=3,B3=2,B4=3,ᑣᦪSUMPRODUCT((Al:A4=D*(Bl:B4))GH()8362130.ᦪROUND(533.8378,-1)Ḅ()530533.84533533.838131.A1=LA2=2,A3=3,A4=4,Bl=2,B2=3,B3=2,B4=3,ᑣᦪSUMPRODUCT((A1:A4)*(B1:B4=3))GH()68129

23132.A1=LA2=2,A3=3,A4=4,Bl=2,B2=3,B3=2,B4=3,ᑣᦪSUMPRODUCT((A1:A4=2)*(B1:B4=3))GH()1023133.A1=LA2=2,A3=3,A4=4,Bl=2,B2=3,B3=2,B4=3,ᑣᦪSUMPRODUCT((A1:A4=3)*(B1:B4=3))GH()0123134.A1=LA2=2,A3=3,A4=4,Bl=2,B2=3,B3=2,B4=3,ᑣᦪSUMPRODUCT((Al:A4=l)*(Bl:B4=2))GH()1023135.A1=LA2=2,A3=3,A4=4,Bl=2,B2=3,B3=2,B4=3,ᑣᦪSUMPRODUCT(A1:A4,B1:B4)GH()264123136.A1=LA2=2,A3=l,A4=l,Bl=3,B2=3,B3=2,B4=3,ᑣᦪSUMPRODUCT((A1:A4=1)*(B1:B4=3))GH()2103137.A2=3.1415,ᑣᦪround(a2,2)GH()3.14313.1415138.PQARRAY)ᦪ
ḄXᦪN,ᑣᦪSMALL(ARRAY,1)Ã()ᨬÄᨬᜧÆÇXᦪ

24ᨬÈÇXᦪ139.PQARRAY)ᦪ
ḄXᦪN,ᑣᦪSMALL(ARRAY,N)ḄÃ()ᨬᜧᨬÄÆÇXᦪᨬÈÇXᦪ140.PQARRAY)ᦪ
ḄXᦪN,ᑣᦪLARGE(ARRAY,N)ḄÃ()ᨬÄᨬᜧÆÇXᦪᨬÈÇXᦪ141.ÉÊËWÌÍᧇᒹÐÑÒஹÑÔÕᱏ?×ᒹÑÒ60XஹÑÔ90XஹÕᱏ110X?×ØÍᧇᔜÚ}ÛᒹÜᑮᨬÝÞßàáᵨᦪs()LCMproductroundpercent142.ᦪRANK(D2,$D$2:$D$101)GHY()᣸äᙳᨬᜧ143,ᦪPERCENTRANK($D$2:$D$101,D2)GHY()åᑖæ᣸ä᣸äᙳ144.A1=LA2=2,A3=3,A4=4,Bl=2,B2=3,B3=2,B4=3,ᑣᦪSUMPRODUCT((A1:A4=1)*(B1:B4=3))GH()0123145.ɽçḄVWᡂenᑖᙠD3:D50PQ?᣸ä*ᵨᦪs()rank(D3,$D$3:$D$50)percent(D3,$D$3:$D$50)product(D3,$D$3:$D$50)sum(D3,$D$3:$D$50)

25146.OPQA3:A90ᓫᐗ(ḄᦪÆ2Xᨬᜧ?ᑣZᵨᦪ()LARGE(A3:A90,2)MAX(A3:A90,2)SMALL(A3:A90,2)MIN(A3:A90,2)147.OPQA3:A90ᓫᐗ(ḄᦪÆ2XᨬÄ?ᑣZᵨᦪ()SMALL(A3:A90,2)MAX(A3:A90,2)LARGE(A3:A90,2)MIN(A3:A90,2)148.᝞HPQDl:D20)×Xᓫᐗ(êᨵᦪ?ᑣᦪLARGE(D1:D2O,1)GH()ᨬᜧᨬÄÆÇXᨬÈÇX149.᝞HPQDLD20)×Xᓫᐗ(êᨵᦪ?ᑣᦪSMALL(D1:D2O,1)GH()ᨬÄᨬᜧÆÇXᨬÈÇX150.᝞HPQDl:D20)×Xᓫᐗ(êᨵᦪ?ᑣᦪSMALL(Dl:D20,20)GH()ᨬᜧᨬÄÆÇXᨬÈÇX151.Al=l,A2=2,A3=3,A4=4,Bl=2,B2=3,B3=2,B4=3,ᑣᦪSUMPRODUCT((Al:A4)*(Bl:B4=2))GH()431226152.ᦪLCM(3,8,15)GH()1203811153.ᦪROUND(AVERAGE(B2:E2),0)GHY()

26fᙳPQᙳ154.ᦪleftk"ëìV▾sëìḄᜧV”lḄklëìᜧVV155.ᦪleftk"ëìV▾sëìḄᜧV”,2lḄklëìᜧVV156.ᦪrightk"ëìV▾sëìḄᜧV"lḄklVëìᜧV157.ᦪrightk"ëìV▾sëìḄᜧV”,2lḄklᜧVëìV158.al=3,bl=4,ᑣᦪLCMkal,bllGHkl12473159.al=3,bl=4,ᑣᦪsumproductkal,bllGHk12473160.ᦪLCMk3,8lGHkl24

273811Æ6îExcelvwïᦪ161.Excel⊤ḄA1ᓫᐗ(ðñòÇXvw?;☢⊤ÜôvwõÇXᨴᨬÈÇᜩḄᦪÇḄs()Al-Day(Al)Edate(Al,-1)Date(Year(Al),Month(Al)-1,0)Al-31162.;ᑡTᙠᓫᐗ()ùᐭúûõvwḄüýs()஺ᙠᓫᐗ(),ᐭ”=DATE0”ᙠᓫᐗ(),ᐭ”=T0DAY()஻ᙠᓫᐗ(),ᐭ”=N0W()”ᢥᔠCtrl+;163.ᙠExcel⊤Al2016/3/31,!"#$ᨵ&'=1^01^^*61,10),,-Ḅ/0()2016/4/142016/4/101410164.ᙠExcel⊤A1"20150802”,789:⌱⚗Ḅ&'=7>?@A1ḄA'BᣚD!A'()=DATE(LEFT(Al,4),MID(Al,5,2),RIGHT(Al,2))=TEXT(Al,"yyyymmdd")=DATE(A1)=LEFT(A1,4)&M1D(A1,5,2)&R1GHT(A1,2)165.᝞8T#UVWXD!#⌨Z[\#78=7>?]W⌨ZD!Ḅ&'()

28ABCDEFGᕒ
eᑺOghLM⌨jPk⌨jghTYO1O11978/2/3l502028/2/3TY01021979/5/20l602039/5/20TY0201ᵱ1979/10/21no602039/10/21TY0202᝕1976/3/6l502026/3/6TY0301@8ᵱ1979/8/2l602039/8/2TY0302\]^᝕1977/12/3no552032/12/3TY0401\Y᝕1981/9/2l502031/9/2TY0402qᵱ1978/3/6no602038/3/6TY0403ᑢ`ᵱ1975/7/2l602035/7/2TY0501ᑢa᝕1982/5/7l502032/5/7TY0502ᑢ8᝕1979/12/1no552034/12/1TY0503rsᵱ1976/10/1l602036/10/1TY0601%ᵱ1983/2/3nt602043/2/3TY0602%u᝕1980/6/2l502030/6/2TY0603T-v1977/6/9no602037/6/9=DATE(YEAR(D2)+F2,MONTH(D2),DAY(D2))=D2+F2=YEAR(D2)+F2=DATE(YEAR(TODAY())+F2,MONTH(D2),DAY(D2))166.aᦪEDATE("2001/3/5”,-6),-Ḅ/0()2000/9/52001/2/271900/5/62001/9/5167.ᙠExcel⊤Al2006-8-4,aᦪDAY(DATE(YEAR(Al),MONTH(Al)+l,0)),-f()31304┯h168.aᦪDATE(19800,5,1)Ḅ,-/0()#NUM!19800-5-11980-5-1#DIV/0!169.ᙠExcel⊤Almᐭ1900/1/1,A2mᐭ1900/1/2,ᙠA3mᐭ&'=A1+A2,/0()328501

293800170.ᙠExcel⊤A2mᐭD!2005-2T,opD!qA3mᐭD!2005-5-2,/rD!#ᙠA4sᵨaᦪ=YEARFRAC(A2,A3),,-/0()0.25277780.24657530.250.5171.2015[11ᨴ1Dv!ᜩ஺y&z2015[11ᨴ3D{2015[11ᨴ4Dᵫ}&zᑁᎷᜩ#ᎷᜩḄD!ᙠᓫᐗAA5{A6஺A2ᓫᐗAḄD!2015[11ᨴ2D#B2ᓫᐗAḄD!2015[Hᨴ30D#ᑣ&'NETWORKDAYS(A2,B2,A5:A6)Ḅ,-f()19182820172.ᙠExcel⊤Almᐭ:D!2012T-3,aᦪEDATE(A1,3),-ḄD!()2012-4-32015-1-32011-10-32012-1-6173.8☢]/0ḄA'vD!ᦪḄ&'v()TODAY()+YEAR(TODAY())DATE(2016,10,5)+T0DAY0VALUE(TODAY())-15VALUEC2016/10/15O174.ᙠAlmᐭ=Date(99,1,27),ᙠA2mᐭ=Al+7A2D!A'#ᑣA2Ḅ/0()஺1999-2-31999-2-41999-2-2┯h175.ᐵ}aᦪDATE(year,month,day),8ᑡ>?Ḅv()᝞0¡ᦪyear¢}0-1899£¤#ᑣExcel¥@¦f§¨1900,©ª][«

30¡ᦪmonth¬f®¯°v±12£¤#ᔲᑣW┯¡ᦪday¬f®¯°v1-31£¤#ᔲᑣW┯¡ᦪyearḄ¬f®¯°v1900-9999,ᔲᑣW┯176.8ᑡaᦪ#³´aᦪµ◤⌕¡ᦪTODAYDAYDATETIME177.Excel2010ᦪ¸0BᣚᡂD!º»Ḅf³´1900/1/01900/1/1W┯1989/12/31178.᝞0¼ᜩv2016[3ᨴ30D#,-f30Ḅ&'()=DAY(DATE(2016,3,30))=DAY(DATE(2016-3-30))=DAY("2016,3,30")=M0NTH(“2016-3-30”)179.aᦪHOUR("22:67:23")Ḅ,-f()23242267180.aᦪMINUTE("22:67:23")Ḅ,-f()7672223181.aᦪH0UR(“35:57:23")Ḅ,-f()11352423

31182.aᦪTIME(29,20,27)@,-()5:20:2712:20:2724:20:2729:20:27183.aᦪDATE³2008,14,2´@,-³´஺2009-2-22008-12-22008-14-22007-2-2184.¾ᙠᓫᐗAmᐭᦪf1@ᓫᐗAA'¿D!A'஻yyyy-m-d஻#ᑣᓫᐗA@³´஺1900-1-11999-1-11000-1-11909-1-1185.¾ᙠᓫᐗAmᐭ0.5@ᐸA'¿஻hh-mm-ss஻#ᑣᓫᐗA³´஺12:00:0008:00:0000:00:0020:00:00186.ᙠᓫᐗAmᐭ஻=DATE³2006,2,35´஻#ᑣᓫᐗA¥³´஺2006-3-72006-2-352006-3-62006-3-5187.᝞02005[1ᨴ2D!D#ᙠᓫᐗAmᐭaᦪ஻Ã'EEKDAY³஻2005T-5஻,2´஻#ᑣᓫᐗA³´஺3214188.ᙠᓫᐗAmᐭ஻=DATE³108,1,2´஻#ᑣᓫᐗAᨵ=Å¥³´஺2008-1-2108-2-1108-1-22008-2-1189.ᙠexcelyᓫᐗAmᐭ⊤Æ'2016/1/1+2,78/0>?Ḅ⌱⚗³´

3220182016/1/32018/1/12016/3/1190.UVy⚗ÇḄÈpD!{/rD!#789:aᦪÅÉÊ]W⚗ÇËᡂᕜ!ᓰᐰ[ḄÏг´YEARFRACEDATEPERCENTNETWORKDAYS191.¾ÒÓÔÕD!2006-6-14³!Ö´#ᑣaᦪWEEKDAY³NOW஺´Ḅ,-f
O4123192.ᐵ}aᦪDays360,×ØÖ:¡ᦪMethodḄÙÚfᦔ0ÜÝḄv³´False1True-1193.ᐵ}Workdayaᦪ#àÅáâ>?Ḅv³´ᵨ}ª]y:D£Õᡈ£ä¾åᜩḄDv9ᜩᵨ}ª]D!£Õᡈ£ä¾åᜩv9ᜩᵨ}ª]:D!£¤ÜæḄDᜩᦪᵨ}ª]:D!£¤ÜæḄᜩᦪ194.ᐵ}Yearfracaᦪ#¡ᦪbasis⊤ê▭ᜩᦪḄ]ìmᐭ³´1023195.8☢aᦪsᵨ>?Ḅv³´Weeknum³Al,Bl´Date³Al,Bl´Month³Al,Bl´Networkday³Al,Bl´

33196.8☢aᦪsᵨ┯hḄv()DAY(A1,1)Eomonth(Al,1)WeekDay(Al,1)Edate(Al,1)197.ᓫᐗAAlmᐭ:D!ì¤î2016-5-3012:30,ᑣ8☢Ḅª]/0ᜧ}10000Ḅv()EOMONTH(Al,Month(A1))Minute(Al)+Month(Al)Hour(Al)+YEAR(Al)Year(Al)+Month(Al)+Day(Al)198.UVaᦪDATEVALUEḄàÅáâ:@D!fð¸ñòBᓄôᑡᦪ஺õA':DATEVALUE(date_text),aᦪTEXTḄàÅáâî᪷ᢣùḄᦪfA'@ᦪ¸Bᡂᦻû#õA'îTEXT(value,format_text),ᑨþ8☢⌱⚗ÿᑮ
ᦪḄ()=DATEVALUE("2015-5-9”)=TEXT(42651,“yyyy-m-d")=TEXT(DATE(2016,3,5),“yyyy-mm-dd")=DATEVALUE(DATE(2016,3,5))199.%☢'ᦪ()
*+,Ḅ()dayworkdayEdateEomonth200.%☢67'ᦪ89ᨵend_date<ᦪ()WorkdayDays360NetWorkdaysYearfrac201.'ᦪweeknumḄ'ᦪDEFGHIḄ()()J7+,JK8ḄLMᕜ()J7+,Jᕜ8ḄLMᜩ()J7+,PQḄMᕜ6Jᜩ()R7+,PSTUMᕜ202.%☢VᓫᐗY8Ḅ'ᦪ⊤[\]^E_ᑏHIḄ()Edate(Al,20)day(Al,20)weekday(Al,20)

34Date(Al,20)203.᪷cdefgḄhf+,ijk+,]ᵨ'ᦪ()mᕒkhfopfgḄq▭ks+ᜩᦪ]᝞%uᡠw]x%EyᑮHI
Ḅ⌱⚗()1yz
yz᱃Oygh|ghOyᕜhTSS«T2ID1501yz12015/3/232015/9/21631173ID1502yz22015/3/22015/9/182001444ID1503yz32015/4/92015/9/41481065XD15O4yz42015/4/262015/9/171441036XD1505yz52015/3/22015/9/161981427XD1506yz6KOVTIWi07708XD1507yz7119869XD1508y~2015/4/32015/971816812010XD1509yz92015/6/22015/9/101007311XD1510yz102015/8/12015/9/15453212XD1511yz112015/4/262015/9/21299213ID1512yz122015/8/32015/9/13413014ID1513rSi32015/7/142015/9/18664915ID15UyzM2015/4/52015/9/1516311616ID1515yz152015/5/162015/9/61138017XD1516yz162015/8/92015/9/17392918XD1517yz172015/6/82015/9/181027519___________________________________20⁚*g]2016/4/5I20I5/S/1I2015/6/20]|NETWORKDAYS(C2,D2,$B$20:$D$20)=W0RKDAY(C2,D2,$B$20:$D$20)=NETWORKDAYS(C2,D2)=EDATE(C2,D2,$B$20:$D$20)204.Eomonth'ᦪmḄ
^E()2017-10-312015-2-292016-2-282014-8-30205.ClᓫᐗYḄ()\Today0,ᑣ%u⁐᪾8ᦪ*Ḅ()\()OABcD+,2016/4/512ᖪgᐭ+,ᜩᦪ3XD1501¡42E5CHR2012/8/2313214XD1502¡42E7BRE2012/11/212505XD1503£¤LED42EC260JD2012/12/912136XD1504£¤LED42K326X3D2013/1/261165¦¤LED39K200JTXD15052013/3/211308XD1506TCLL46F2510E2013/1/2811639XD1507TCLL42F2510E2013/2/27113310XD1508TCLL32F1510B2013/3/30110211XD1509¨©KLV-46R476A2013/1/3011612016/4/5-C11=C1-CU

35=$c$i-cu=today()-Cl1206.AlᓫᐗY«¬®Ḅmh+,1997-5T,x%E()m®K¯ḄHI⌱⚗()=YEAR(TODAY())-YEAR(Al)=TODAY()-Al=YEAR(TODAY())-Al=NOW()-A1207.%ᑡ⌱⚗┯²Ḅ()WORKDAY'ᦪ³´ḄJ7ᜩᦪEDATE'ᦪ³´ḄJ7+,DATE'ᦪ³´ḄJ7+,NETWORKDAYS'ᦪ³´ḄJ7ᜩᦪ208.ᙠᓫᐗY8¶ᐭ67'ᦪ]ᑣᓫᐗY8*·¸w2006-3-7()=DATE(106,4,-23)=DATE(2006,3,7)=DATE(2006,2,35)=DATE(106,3,7)209.ᙠᓫᐗY8¶ᐭx%67'ᦪ]ᑣᓫᐗYᨵ^E·¸w2008-1-2()=DATE(108,1,2)=DATE(2008,2,1)=DATE("2008T-2")=DATE("108-1-2”)210.'ᦪTIMEVALUE("12:00")Ḅ³´()0.512240211.»¼½+,2006-6-14(¾,¿)]ᑣ³´4ᡠÀḄ'ᦪ]%ᑡ⌱⚗┯²Ḅ()WEEKDAY(NOW(),2)WEEKDAY(TODAY())WEEKDAY(TODAY(),1)WEEKDAY(NOW())

36212.ᓫᐗY8Ḅ\”=Date(Year(Today஺)]Month(Today())+1,0)”,³´Ḅ
()ÃᨴḄᨬQJᜩÃᨴḄLJᜩ%7ᨴḄLJᜩ%7ᨴḄᨬQJᜩ213.ᙠᓫᐗY¶ᐭ+,Æ;Re^ÈᵨḄKஹᨴஹ+S◞Ë()Ìᩨ(/)ᡈÏÐË(-)Ìᩨ(/)ᡈÑÌᩨ(\)ᙊÔ(.)ᡈÕÖ(I)ÑÌᩨ(\)ᡈÏÐË(-)214.ᓫᐗY8Ḅ\"=DATE(YEAR(NOW()),MONT1I(NOW()),2)”,¸wḄ()
()Kᨴ]2+ḄJ7+,Kᨴ]2+ḄJ7ÆS×ᨵKᨴḄJ7+,Kᨴ+ḄJ7+,215.ᓫᐗYA18ḄᑁÙ2016-03-01,B18¶ᐭ\”=DAY(A1T)”,BlḄ¸w
()29283031216.ᎷÛᓫᐗYB2ḄᑁÙ2008-4-25,ᑣ'ᦪ”=day(B2)”Ḅ()25420081979217.Excelks⊤8]VᓫᐗYᦪc+,Ý''Jß஺஺KJᨴᓝá+"]ᓫâ"ãä”åᓫ%"æ◀”⌱⚗Ḅ஻Y\஻éê]ᓫᐗYḄᑁÙ()161719161917

37218.'ᦪEdateḄ<ᦪᨵëì72134L7íExcelîï'ᦪ219.ᙠF4ᓫᐗYðᑏ”=ROW(B6)”,ᑣ³´()஺6BF4220.'ᦪADDRESS©3,3)³´()஺$C4$C$4C4C$4221.'ᦪCHOOSE஺]Al:A10,Bl:B10,Cl:C10ìḄò)
ëìᓫᐗYóôAl:A10ḄõᵨᓫᐗYóôBl:B10ḄõᵨᓫᐗYóôC1:C1OḄõᵨSVALUE!222.'ᦪCHOOSER,"U"]"8÷"]"øø+”ìḄò)
ëì8U÷øᡈø+223.CHOOSEëindexnum,valuel,[value2],…ì<ᦪindexnumḄýþÝ*ᒹ
ᦻᓫᐗᵨᦪ224.CH00SE
index_num,valuel,[value2],…&ᦪindex_num,᝞(⊤*ᦪ+,Ḅ./12


383456ᦪ4+᝞(ᓫᐗB4ᨵ9:;஻George",BᦪINDIRECT($A$4)LM()஺103456ᦪ4+BᦪINDIRECT($A$4)LM()஺#REF!10A4George3456ᦪ4,BᦪINDIRECT(஻B஻&$A$4)LM()஺402538#REF!

3938B3A3W5XᫀZ[\3456ᦪ4+BᦪINDIRECT(஻A3஻)LM()஺B338A3W5XᫀZ[\25B2A2W5XᫀZ[\231.ᙠF4ᓫᐗ^ᑏ஻=COLUMN()஻+ᑣLM/f()஺64F0232.BᦪCHOOSE(3,0,500,1000,3000,5000)Ḅhij(2()10000

40500ᡈ50003000233.ᙠF4ᓫᐗ^ᑏ஻=ROW()஻+ᑣLM/f()஺46FB234.BᦪCHOOSE(-1,0,500,1000,3000,5001)Ḅhij(2()#VALUE!05001000AB1yz⚗ᦪ2✏Ⅵ25338440235.5413456ᦪ4BᦪMATCH
40,B2:B5,-lLM
஺(#N/A)2341产品⚗ᦪ2香蓬3柑橘25苹果384梨子405236.413456ᦪ4BᦪMATCH(41,B2:B5,0)LM
஺4235

41AB⚗ᦪ237.413456ᦪ4BᦪMATCH(39,B2:B5,1)LM()»2134238.MATCH(lookupvalue,1ookuparray,[matchtype])y&ᦪ[match_type]Ḅz:2{|}()஺ᓛ+⌱+ᨵ3⌱2ᨵ⌕/ḄᓫᐗஹᦪᡈᦪḄᵨ2ᙠᦪyᓛḄ/W5XᫀZ[\஺239.MATCH(lookup_value,1ookuparray,[match_type])y&ᦪlookup_arrayḄz:2{|}()O2ᨵ⌕/ḄᓫᐗஹᦪᡈᦪḄᵨ2ᙠᦪyᓛḄ/ᓛ+⌱+ᨵ3⌱W5XᫀZ[\஺240.MATCH(lookup_value,lookuparray,[matchtype])y&ᦪlookup_valueḄz:2{|}()஺2ᙠᦪyᓛḄ/2ᨵ⌕/ḄᓫᐗஹᦪᡈᦪḄᵨᓛ+⌱+ᨵ3⌱W5XᫀZ[\஺241.BᦪADDRESS(row_num,column_num,[abs_num],[al],[sheet_text])y&ᦪ[sheet_text]Ḅz:2{|}()஺ᢣ9⌕ᵨ᜜ᵨḄ⊤Ḅ;ᢣ9⌕ᙠᓫᐗᵨyᵨḄᑡᢣ9⌕LMḄᵨᢣ9⌕ᙠᓫᐗᵨyᵨḄ242.BᦪADDRESS(row_num,column_num,[abs_num],[al],[sheet_text])y&ᦪ[abs_num]Ḅz:2{|}()஺ᢣ9⌕LMḄᵨᢣ9⌕ᙠᓫᐗᵨyᵨḄᑡ

42ᢣ9⌕ᙠᓫᐗᵨyᵨḄᢣ9⌕ᵨ᜜ᵨḄ⊤Ḅ;243.BᦪADDRESS
row_num,column_num,[abs_num],[al],[sheet_text]y&ᦪcolumn_numḄz:2{|}
஺ᢣ9⌕ᙠᓫᐗᵨyᵨḄᑡᢣ9⌕ᙠᓫᐗᵨyᵨḄᢣ9⌕LMḄᵨᢣ9⌕ᵨ᜜ᵨḄ⊤Ḅ;244.BᦪADDRESS
row_num,column_num,[absjnum],[al],[sheet_text]y&ᦪrow_numḄz:2{|}
஺ᢣ9⌕ᙠᓫᐗᵨyᵨḄᢣ9⌕ᙠᓫᐗᵨyᵨḄᑡᢣ9⌕LMḄᵨᢣ9⌕ᵨ᜜ᵨḄ⊤Ḅ;245.ADDRESS
2,3,4ḄLM/2
C2$C$2B3$B$3246.ADDRESS+,3ḄLM/2
$C$2C2$B$3B3247.BᦪADDRESS
4,3,4,FALSELM
஺R[4]C[3]R[4]C3R4C[3]R4C3248.ᙠF4ᓫᐗ^ᑏ஻^COLUMN
B6஻+ᑣLM/f
஺2B46249.BᦪVL00KUP
஻SX19஻+{஻SX19஻+600,13;஻B274஻+2600,1300,15},3,0ḄLM/f
஺13600

43260011250.BᦪINDEX
{3,4;6,5},2,1ḄLM/2
஺6543251.᝞¨©ᡠ*+Bᦪ1«£*
61ᓝ3*5:B+2,3,2ḄLM/f
ABC110203024050603708090451122336445566777889966608880252.HL00KUP(lookup_value,table_array,col_index_num,[rang_lookup])BᦪḄ&ᦪ()¯9°Bᦪ2᡻²³´2µ¶஺rang_lookuplookup_valuetable_arraycol_index_num253.BᦪINDEX(B2:D6,0,0)ḄLM/2()஺ᓫᐗB2:D6Ḅᵨ#REF!#VALUE!#N/A254.BᦪINDEX(B2:D6,0,1)ḄLM/2()஺ᓫᐗB2:B6Ḅᵨ#REF!#VALUE!#N/A255.BᦪINDEX(reference,row_num,colunm_nuni)ḄLM/2()஺/ᡈᓫᐗᵨ/

44ᓫᐗᵨ[¸³9256.BᦪINDEX(B2:D6,5,0)ḄLM/2()஺ᓫᐗB6:D6Ḅᵨ#REF!#VALUE!#N/A257.BᦪINDEX(B2:D6,4,-1)ḄLM/2()஺SVALUE!B5:D5B5D5258.BᦪINDEX((A2:C6,A8:C11),4,3)ḄLM/2ᓫᐗ()Ḅᑁº஺C5C4B5B4259.ᙠA4ஹB4ᓫᐗḄ/ᑖ¼f4,62ᩩ¾¨+BᦪINDIRECT(¿஻&A4)ḄLM/2()O624A4B4260.BᦪINDEX((A1:C6,A8:C11),3,3,2)ḄLM/2ᓫᐗ()Ḅᑁº஺C10B10C9D10261.BᦪCHOOSE(4.6,0,500,1000,3000,2016)Ḅhij(2()300020161000#VALUE!262.BᦪÀW)0Á}(஻Â஻+{'6஻+600,13;஻1?஻+2600,11;஻Å'+1300,15},3,0)ḄLM/f()O#N/ATRUE

45FALSE#REF!263.BᦪVLOOKUP(஻T஻+{஻R஻+600,13;஻S஻,2600,1300,15},3,1)ḄLM/f()o111315600264.ᙠE2ᓫᐗḄ/f75ḄÆǨ+BᦪCHOOSE(if(E2<60,1,QNT(E2-60)/10))+1),஻[Ë஻+஻Ë஻+஻y஻+஻Ì஻+஻Í஻)Ḅhij(2()ËÌyÍ265.VLOOKUP(lookup_value,table_array,col_index_num,[rang_lookup])BᦪḄ&rangÎookup2ᢣ⌕ḄÏ+°/f()ᡈḕᶍ+ᑣLM²³ᓛ/ᡈÒÓᓛ/஺TRUEFALSE#N/A0266.IILOOKUP(lookup_value,table_array,col_index_num,[rang_lookup])BᦪḄ&rangÎookup2ᢣ⌕ḄÏ+°/f()ᡈḕᶍ+ᑣLM²³ᓛ/ᡈÒÓᓛ/஺TRUEFALSE#N/A0267.HLOOKUP(lookup_value,table_array,col_index_num,[rang_lookup])BᦪḄ&ᦪ(lookup_value2ᢣ⌕Ḅ/+°/ÔÕᙠḄÖ()஺123×z268.VLOOKUP(lookup_value,table_array,col_index_num,[rang_lookup])BᦪḄ&ᦪlookup_value2ᢣ⌕Ḅ/+°/ÔÕᙠḄÖ()ØÙ1

4623×z269.ᙠVLOOKUPÚHLOOKUPBᦪy+
Bᦪ2ᢥᑡVLOOKUPHLOOKUPVLOOKUPÚHLOOKUPÜᨵ270.ᙠVLOOKUPÚHLOOKUPBᦪy+
Bᦪ2ᢥHLOOKUPVLOOKUPVLOOKUPÚHLOOKUPÜᨵ271.BᦪLOOKUP,ÝÞ2ᵨᔣàá´2ᦪá+⊤*ḄÖᡈÖᑡ
ᦪá2ᢥ᯿
᣸ᑡᓣåæåᓣåᡈæåÝ⌕ç272.BᦪLOOKUPᨵ2èéá+,ê2
ᔣàÚᦪᔣàÚᦻᦪÚᦻᦻÚ273.BᦪLOOKUP
lookup_value,lookup_vector,[result_vector]yresult_vectorÚlookup_vector&ᦪᜧìÔÕ
íîíïÜᨵ⌕ç[îḄÆÇᨵ[îḄ⌕ç274.᝞¨©ᡠ*+BᦪL00KUP
"C",Al:A5,B2:B6ḄLM/fOAB1A202B303C154D405E256F45

4740301525275.᝞¨©ᡠ*+BᦪLOOKUP(஻C஻,A1:A6,B1:B6)ḄLM/f()AB1A202B303C154D405E256F4515253040358523277.ðCHOOSE(index_num,valuel,[value2],•••)BᦪḄ⊤òWóô°BᦪḄÔÕ&ᦪᦪf()2103278.BᦪINDEX(B2:D6,4,3)ḄLM/2ᓫᐗ()Ḅᑁº஺D5C3D3C5Ö8õExcelᦻBᦪ

48279.᝞¨©ᡠ*+ö⌕D3ᓫᐗḄ/f஻ChengXiu஻+÷ᵨḄf()ABCD12
HIᜧᑏ310NM100▰Rchengxiu416NM101STdouhai513NM102UVWfanfengxia612NM103XTYguohaiying714NM104Z᫥jimei810NM105\]^liguoqiang915NM106\Yliyingøproper(C3)=left(C3)=upper(C3)=lower(C3)280.¸ùúᦻ"zhangjie஻üýþᣚ"ZhangJie஻Ḅᦪ()properupperlargelower281.ᦻ!ᡠᨵḄ$ᑏ&'ᣚᜧᑏ&Ḅᦪ*()upperproperlargelower282.ᦻ!ᡠᨵᜧᑏ&'ᣚ$ᑏ&Ḅᦪ*()lowerproperupperlarge283.,-ᦻ஻123஻'ᣚᦪ.Ḅᦪ*()valueproperupperlarge

49284.1ᵨfrequencyᦪ78ᑖ:;<=ᦪ>⌱@ABCDE>Fᢥ()HᔠJKᑮAB஺Ctrl+Shift+EnterShift+EnterCtrl+ShiftCtrl+Alt+Enter285.᝞VWᡠX>Yᔜ[ᑖᦪ:=ᦪḄ\]ᑏ^*()▲IBCDEIF1ᑖᦪ◞ᑖᦪlᦪ6560603896960-694907970-795758980-89685907988789701082771250frequency(A2:A12,C2:C5)frequency(C2:C5,A2:A12)frequency(A2:A12,E2:E6)frequency(E2:E6,A2:A12)286.᝞VWᡠX>b⌕deYfᔜ[ᑖᦪ:=ᦪghijឋlXᙠF2:F6CD>F1ᵨVᑡp[ᦪ()ABCDEIF1ᑖᦪ◞ᑖᦪlᦪ26560603896960-694907970-795|758980-89685907988789701082MJ771250frequencymodelargepercentrank

50287.g~[ᦪ.Ḅᦪ*()modefrequencylargepercentrank288,ᯠE~i[ᚖᦪHḄᦪ*()frequencymodelargepercentrank289.frequencyᦪḄᵨ*()xy⚣᳛Y᣸ᑖ᣸ᔠFᵨ290.Vᑡ^!>\]Ḅ*()ᦪleft("!=ᐳ”,4)ḄAB!=ᦪloner("abc")ḄSUMPRODUCT((C2:C7>=60)*(D2:D7ு=60)*(E2:E7>=60))=60”,D2:D7,"ு=60”,E2:E7)=countifs(C2:C7,">=60”,D2:D7,">=60”,E2:E7,">=65")=left(A2,1)

51292.Vᑡ⌱⚗!>'i",2,1)=year(date(2016,4,2))-2014=row(A2)=lcm(l,2)293.Vᑡᡈᦪ!>~.±²²Ḅ*()=left("ℳA1001”,2)=weekday(today())=value(mid(“C200”,2,3))="123"="123"294.Vᑡᡈᦪ!>~.±²µ¶.Ḅ*()="123"="123"=left("ℳA1001”,2)=weekday(today())=value(mid(“C200”,2,3))295.LENᦪḄ¸,*~ᦻ!Ḅᦪ>ᑣVᑡ⌱⚗!ᑣVᑡ⌱⚗!ᑣVᑡ⌱⚗!┯ÁḄ*()1ᵨMAXAᦪÃᓫᐗÆCDCD!Ḅµ¶.TRUEÇÈÉ0,FALSEÇÈÉ1SUMIFSᦪ,ÊyḄ¸,SL'MPRODUCTº,ÊyExcel!ÃFËᑡÌ1ḄÍÎ.*1900-1-1INDEXᦪḄ¸,*~8ᑡÏÐᜐᓫᐗÆḄÒᵨ

52299.Vᑡ^!>\]Ḅ*()1ᵨMINAᦪÃᓫᐗÆCDCD!Ḅµ¶.TRUEÇÈÉ1,FALSEÇÈÉ0SUMPRODUCTᦪ,ÊyḄ¸,SUMIFSº,ÊyExcel!ÃFËᑡÌ1ḄÍÎ.*2000-1-1INDIRECTᦪḄ¸,*~8ᑡÏÐᜐᓫᐗÆḄÒᵨ300.Vᑡ^!>\]Ḅ*()MIDᦪÓÔÕᦻḄÖ×ØÙÚÛÜÝVLOOKUPᦪḄ¸,*ᢥ8àáINDIRECTᦪḄ¸,*~8ᑡÏÐᜐᓫᐗÆḄÒᵨMATCHᦪḄ¸,*~ᢣ@8ÌᑡÌḄᓫᐗÆᙢᙬ301.Vᑡ^!>┯ÁḄ*()SUMPRODUCTᦪ,ÊyḄ¸,SUMIFSº,ÊyMIDᦪÓÔÕᦻḄÖ×ØÙÚÛÜÝVLOOKUPᦪḄ¸,*ᢥᑡàáADDRESSᦪḄ¸,*~ᢣ@8ÌᑡÌḄᓫᐗÆᙢᙬ302.Ô᝞Vᦪæ⊤è>Vᑡé[⌱⚗!~.ᦪ.²ᦪæḄ*:AB别CDEê}(ᔖì)17817012010416812030115812030616278=match("íîï">B2:B7,0)=if(D2>=160,"ðñòó">"ôõòó”)=date(2015,12,10)=mid(A2,3,2)303.Vᑡ^!>┯ÁḄ*()Excelᦪæᜐᳮ!>᣸Ë÷ø,┐Ãᑡ78᣸ËᦪCHOOSE(3,0,500,1000,3000)Ḅ~.1000ú@ûËᑡᔜ⚗üýÔþÿḄ⌹◞ᓫᐗ“"ᑣᦪEDATE(DATE(2016,4,20),5)Ḅ!"#2016/9/20304.()*+,-ᦪḄ.()஺IndirectYearFracWeekDay

53Minute305.@ᑡBCD┯FḄ.()ᦪlcm(l,2)Ḅ"#1ᦪlower("ABC")Ḅ!"#abcᦪMINUTE("10:35:23")Ḅ!"#35ᦪleft("DRSTᐳVW”,4)Ḅ"#YDRST306.@ᑡBCD┯FḄ.()Z="123"="123”Ḅ!"#1ᦪlcm(l,2)Ḅ"#2ᦪday(date(2016,2,10))Ḅ!"#10ᦪSECOND("10:23:35")Ḅ!"#35307.@ᑡBCD_`Ḅ.()ᦪcolumn(A2)Ḅ"#1MATCHᦪḄbc.def+ᑡghᜐᓫᐗḄjᵨSUMPRODUCTᦪcnoḄbcSUMIFpcnoqrstᑡᔜ⚗w-xy◞308.@ᑡBCD_`Ḅ.()ᦪ{Z|ᵨCtrl+Shift+Enter{ᔠ`ᐭᦪDATE(YEAR,MONTH,DAY)DᦪYEARḄ.1900-9999,ᔲᑣ┯ᦪROUND(number,num_digits)Dᦪnum_digits.ᜧ*Ḅ᦮ᦪᦪVLOOKUPḄbc.ᢥf309.@ᑡBCD_`Ḅ.()ᦪADDRESS(3,4,3)Ḅde$D3ᦪPROPER("goodmorning")ḄdeGoodmorningᦪHOUR("10:23:45")Ḅde23ᦪLCM(10,20)Ḅde10310.@ᑡBC_`Ḅ.()ᦪPROPER("goodmorning")ḄdeGoodMorningᦪADDRESS(3,4,3)ḄdeD$3ᦪHOUR("10:23:45")Ḅde23ᦪLCM(10,20)Ḅde10311.@ᑡBC┯FḄ.()Zvalue(mid(“C200”,2,3))ḄdeᦻᦪHOUR(“10:61:45")Ḅde11INDEXᦪḄbc.def+ᑡghᜐᓫᐗḄjᵨᦪLCM(10,20)Ḅde20

54312.@ᑡBCD┯FḄ.()ᦪlower(z/abc/z)Ḅ!"#ABCᦪlcm(l,2)Ḅ"#2Z="123"="123"Ḅde£¤MIDᦪ¥x¦ᦻḄ§¨©ª«¬®¯313.@ᑡBCD_`Ḅ.()°±²³´ᑮḄ.°±-²¶Ḅᜩᦪde¸¹º»,-Ḅᦪ.TIMEHLOOKUPᦪ.ᢥᑡFREQUENCYᦪḄbc.»½ᑖ¿᣸Á314.@ᑡBCD┯FḄ.()deº»,-ᦪ.TIMEdeº»ᦪ.TODAYUPPERᦪ.ÂᦻDᡠᨵÅᑏ®ÇÈᣚᜧᑏ®ÇᦪPERCENTRANKÊ˽ᑖ¿᣸Áᦪ315.Z"=MOD(12,5)”Ḅ!"#2,Z"=MOD(9,4)”Ḅ!"#1,ᑣᦪMODḄÌᵨ.()ÍÎᦪÍ⚣᳛ÍÑÒÍV316.@ᑡBC_`Ḅ.()ᦪCHOOSE(3,0,500,1000,3000)Ḅde1000qrstᑡᔜ⚗w-xÓÔḄᑖ◞ᓫᐗ“"ᑣᦪEDATE(DATE(2016,4,20),10)Ḅ!"#2016/4/30ExcelᦪÖᜐᳮD᣸tØÌÙc┐ÛᑡÜf᣸t