æ¬ç¯æ¯å°ç¼ä»¥åä»æµ·éexcelæå·§ä¸ï¼ç²¾éåºçæè´´è¿å®ç¨çæå·§ï¼å ±25个ï¼ä»å¤©å次æ¨èï¼å¸æ对åå¦ä»¬è½æ帮å©ã
25æ¡excel常ç¨æå·§ï¼
1ã两åæ°æ®æ¥æ¾ç¸åå¼å¯¹åºçä½ç½®
=match(b1,a:aï¼0)
2ãå·²ç¥å ¬å¼å¾ç»æ
å®ä¹å称=evaluate(sheet1!c1)
å·²ç¥ç»æå¾å ¬å¼
å®ä¹å称=get.cell(6,sheet1!c1)
3ã强å¶æ¢è¡
ç¨alt+enter
4ãè¶ è¿15ä½æ°åè¾å ¥
è¿ä¸ªé®é¢é®ç人太å¤äºï¼ä¹æ¶èµ·æ¥å§ãä¸ãåå æ ¼è®¾ç½®ä¸ºææ¬;äºãå¨è¾å ¥æ°ååå è¾å ¥'
5ãå¦æéèäºbåï¼å¦æ让å®æ¾ç¤ºåºæ¥?
éä¸aå°cåï¼ç¹å»å³é®ï¼åæ¶éè
éä¸aå°cåï¼åå»éä¸ä»»ä¸å宽线ææ¹åä»»ä¸å宽
å°é¼ æ 移å°å°acåä¹é´ï¼çé¼ æ å为åç«çº¿æ¶æå¨ä¹ã
6ãexcelä¸è¡åäºæ¢
å¤å¶ï¼éæ©æ§ç²è´´ï¼éä¸è½¬ç½®ï¼ç¡®å®å³å¯
7ãexcelæ¯æä¹å å¯ç
(1)ãä¿åæ¶å¯ä»¥çå¦å为>>å³ä¸è§ç"å·¥å ·">>常è§>>设置
(2)ãå·¥å ·>>é项>>å®å ¨æ§
8ãå ³äºcountif
countifå½æ°åªè½æä¸ä¸ªæ¡ä»¶ï¼å¦å¤§äº90ï¼ä¸º=countif(a1:a10,">=90")
ä»äº80ä¸90ä¹é´éç¨åï¼ä¸º =countif(a1:a10,">80")-countif(a1:a10,">90")
9ãæ ¹æ®èº«ä»½è¯å·æååºçæ¥æ
(1)ã=if(len(a1)=18,date(mid(a1,7,4),mid(a1,11,2),mid(a1,13,2)),if (len(a1)=15,date(mid(a1,7,2),mid
(a1,9,2),mid(a1,11,2)),"é误身份è¯å·"))
(2)ã=text(mid(a2,7,6+(len(a2)=18)*2),"#-00-00")*1
10ãæ³å¨sheet2ä¸å®å ¨å¼ç¨sheet1è¾å ¥çæ°æ®
å·¥ä½ç»ï¼æä½shiftæctrlé®ï¼åæ¶éå®sheet1ãsheet2
11ãä¸åä¸ä¸è¾å ¥éå¤æ°å
[æ°æ®]--[æææ§]--[èªå®ä¹]--[å ¬å¼]
è¾å ¥=countif(a:a,a1)=1
å¦æè¦æ¥æ¾éå¤è¾å ¥çæ°å
æ¡ä»¶æ ¼å¼ãå ¬å¼ã=countif(a:a,a5)>1ãæ ¼å¼é红è²
12ãç´æ¥æå¼ä¸ä¸ªçµåè¡¨æ ¼æ件çæ¶åæä¸å¼?
“æ件夹é项”-“æ件类å”ä¸æ¾å°.xlsæ件ï¼å¹¶å¨“é«çº§”ä¸ç¡®è®¤æ¯å¦æåæ°1%,å¦æ没æï¼è¯·æå·¥å ä¸
13ãexcelä¸æèåçå®ç°
[æ°æ®]-[æææ§]-[åºå]
14ã 10åæ°æ®å计æä¸å
=sum(offset($a$1,(row()-2)*10+1,,10,1))
15ãæ¥æ¾æ°æ®å ¬å¼ä¸¤ä¸ª(åºæ¬æ¥æ¾å½æ°ä¸ºvlookup,match)
(1)ãæ ¹æ®ç¬¦åè¡å两个æ¡ä»¶æ¥æ¾å¯¹åºç»æ
=vlookup(pï¼a1ï¼e7ï¼match(i1ï¼a1ï¼e1ï¼0)ï¼false)
(2)ãæ ¹æ®ç¬¦å两åæ°æ®æ¥æ¾å¯¹åºç»æ(为æ°ç»å ¬å¼)
=index(c1ï¼c7ï¼match(p&i1,a1:a7&b1:b7,0))
16ãå¦ä½éèåå æ ¼ä¸ç0
åå æ ¼æ ¼å¼èªå®ä¹0;-0;;@ æ é项ãè§å¾ãé¶å¼å»å¾ãåµåµï¼å¦æç¨å ¬å¼å°±è¦çæ åµäºã
17ãå¤ä¸ªå·¥ä½è¡¨çåå æ ¼å并计ç®
=sheet1!d4+sheet2!d4+sheet3!d4ï¼æ´å¥½ç=sum(sheet1:sheet3!d4)
18ãè·å¾å·¥ä½è¡¨å称
(1)ãå®ä¹å称ï¼name
=get.document(88)
(2)ãå®ä¹å称ï¼path
=get.document(2)
(3)ãå¨a1ä¸è¾å ¥=cell("filename")å¾å°è·¯å¾çº§æ件å
å¨éè¦å¾å°æ件åçåå æ ¼è¾å ¥
=mid(a1,find("*",substitute(a1,"","*",len(a1)-len(substitute(a1,"",""))))+1,len(a1))
(4)ãèªå®ä¹å½æ°
public function name()
dim filename as string
filename = activeworkbook.name
name = filename
end function
19ãå¦ä½è·åä¸ä¸ªæçæ大天æ°
:"=day(date(2002,3,1)-1)"æ"=day(b1-1)",b1为"2001-03-01
æ°æ®åºå å«æä¸å符ç项çæ»å,该ç¨ä»ä¹å ¬å¼
=sumif(a:a,"*"&"æä¸å符"&"*",æ°æ®åº)
æåä¸è¡ä¸ºææ¬ï¼
=offset($b$1,match(char(65535),b:b)-1,)
æåä¸è¡ä¸ºæ°åï¼
=offset($b$1,match(9.9999e+307,b:b)-1,)
æè ï¼=lookup(2,1/(b1:b1000<>""),b1:b1000)
è¯å§æåä¸ï¼å¦ä½å»æ两个以ä¸æé«åï¼ä¸¤ä¸ªä»¥ä¸æåºåï¼æ±å©ä½äººåçå¹³åå?åæ¶æ¾ç¤ºåºè¢«å»æçåæ°ã
ççtrimmean()å½æ°å¸®å©ã
被å»æçåæ°ï¼
æ大两个ï¼=large(data,{1;2})
æå°ä¸¤ä¸ªï¼=small(data,{1;2})
ææ ·å¾ç®åçå¤ææåä¸ä½æ¯åæ¯
right(a1)*1
åºéçåæ¯
=if(isnumber(--right(a1,1)),"æ°å","åæ¯")
=if(iserr(right(a1)*1),"åæ¯","æ°å")
å¦ä½ 设置åå æ ¼ï¼ä»¤å ¶ä¸æ¥åå å«ç©ºæ ¼çå符
éå®aå
æ°æ®——ææ槗—èªå®ä¹——å ¬å¼
=iserror(find(" ",a1))
æ°æ®--æææ§--èªå®ä¹--å ¬å¼
=len(a1)=len(trim(a1))
åæ¥çå½æ°æ¯=a1+a4+a7+a10+a13+a16+a19+a22.....
ç°å¨æ¯=sum(n(offset(a1,(row(1:10)-1)*3,)))
å¨ä¸ä¸ªå·¥ä½è¡¨ä¸å¼ç¨å ¶ä»å·¥ä½è¡¨ä¸çæ°æ®,ä½æ¯è¢«å¼ç¨çå·¥ä½è¡¨ä¸æ¯åºå®ç,æ ¹æ®æè¾å ¥çå·¥ä½è¡¨åèªå¨éæ©ç¸åºçå·¥ä½
表ä¸çæ°æ®,请é®å¨å ¬å¼éææ ·å¼ç¨?
=indirect("a1"&"!"&"e1") a1为工ä½è¡¨å
å¥æ°è¡æ±å =sumproduct((a1:a1000)*mod(row(a1:a1000),2))
å¶æ°è¡æ±å =sumproduct((a1:a1000)*not(mod(row(a1:a1000),2)))
æ¥çå符串åæ°
=len(a1)
æ±é空åå æ ¼æ°é
å ¬å¼è®¡ç®åºæ¥çæ°æ®ï¼countaä¸è½ç¨ç(å¦å空å符ä¹è®¡ç®è¿å»äº)
=countif($e$3:$e$65536,"?*")
å¨ææ±åå ¬å¼,èªaåa1åå æ ¼å°å½åè¡åé¢ä¸è¡çåå æ ¼æ±å.
=sum(indirect("a1:a"&row()-1))
20ãæ¯è¾å¥½ç¨çexcelæ档修å¤å·¥å ·
excelrecovery
21ãexcelå¼æ¹è¿ç®
å°8å¼3次æ¹ï¼å¯ä»¥ç¨è¿ä¸ªå ¬å¼ï¼å¨åå æ ¼ä¸è¾å ¥=8^(1/3)
22ãåå æ ¼ä¸çæ°æ®åæ£å¯¹é½
ææ¬æ ¼å¼ãå ¨è§è¾å ¥
23ãæ¥æ¾å·¥ä½è¡¨ä¸çé¾æ¥
ctrl+~ æç¼è¾ãé¾æ¥
24ãå¦ä½è®©ç©ºåå æ ¼èªå¨å¡«ä¸º0
éä¸éæ´æ¹çåºåãæ¥æ¾ã空ãæ¿æ¢ã0
25ãæwordéçæ°å转æ¢å°excel
æ¹æ³æå¤ç§ï¼éä¸ãå¤å¶ã设置è¾å ¥åå æ ¼ä¸ºææ¬ãéæ©æ§ç²è´´ãå¼
以ä¸æ¯excelç25æ¡æå·§ãå¸æ对大家ææ帮å©ï¼ä¹å¸æ大家认çå¦ä¹ excelã