EXCL
鍋氭帓鍒椾笁鏁版嵁缁忓吀鍏紡
1
姹傚拰
鍏紡
=A+B+C
鎴栬�?/p>
=SUM(A:B)
2
姹傚拰
鍙栧熬
=RIGHT(SUM(A:B))
3
姹傝法鍏紡
=MAX(A:C)-MIN(A:C)
4
浣?/p>
鏁?/p>
鍏?/p>
寮?/p>
=MOD
锛?/p>
A,4)
琛?/p>
绀?/p>
闄?/p>
4
浣?/p>
鍑?/p>
5
鍚?/p>
骞?/p>
鍗?/p>
鍏?/p>
鏍?/p>
=A&B&C&D&E
鎴栬�?/p>
=CONCATENATE(A1,B1,C1,D1,E1
锛?/p>
6
鍙?/p>
鏁?/p>
瀛?/p>
鍏?/p>
寮?/p>
=LEFT(A,3)
琛?/p>
绀?/p>
鍙?/p>
鍓?/p>
3
浣?/p>
=MID(A,3,2)
琛ㄧず浠庢暟瀛楃殑涓棿鐨勭
3
浣嶆暟寮�濮?/p>
鍙?/p>
2
浣嶅彿锛?/p>
=RIGHT(A
锛夎〃绀哄彇鏈綅
7
姹傜粷瀵瑰樊
=ABS(A-B)
琛ㄧず缁撴灉涓嶅惈璐熸暟
8
鎸崱鍏紡
=ABS(A1-A2)
9
浠庡皬鍒板ぇ鎺掑垪
=MIN(A:C)&MEDIAN(A:C)&MAX(A:C
锛?/p>
10
濂?/p>
鍋?/p>
鍏?/p>
寮?/p>
=IF(ISODD(A1),"
濂?/p>
","
鍋?/p>
")&IF(ISODD(B1),"
濂?/p>
","
鍋?/p>
")&IF(ISODD(C1),"
濂?/p>
","
鍋?/p>
")
绛夊悓
MOD(A1,2)=1 ,"
濂?/p>
","
鍋?/p>
"
11
澶?/p>
灏?/p>
鍏?/p>
寮?/p>
=IF(OR(B2>=5),"
澶?/p>
","
灏?/p>
")&IF(OR(C2>=5),"
澶?/p>
","
灏?/p>
")&IF(OR(D2>=5),"
澶?/p>
","
灏?/p>
")
12
璐ㄥ悎鍏紡
=IF(OR(B2={1,2,3,5,7}),"
璐?/p>
","
鍚?/p>
")&IF(OR(C2={1,2,3,5,7}),"
璐?/p>
","
鍚?/p>
")&IF(OR(D2={1,2,3,5,7}),"
璐?/p>
","
鍚?/p>
")
13
灏忎腑澶у叕寮?/p>
=IF(B2<=2,"
灏?/p>
",IF(B2>=7,"
澶?/p>
","
涓?/p>
"))&IF(C2<=2,"
灏?/p>
",IF(C2>=7,"
澶?/p>
","
涓?/p>
"))&IF(D2<=2,"
灏?/p>
",IF(D2>=7,"
澶?/p>
","
涓?/p>
"))
14
鐩?/p>
閫?/p>
璺?/p>
鍏?/p>
寮?/p>
=MOD(B2,3)&MOD(C2,3)&MOD(D2,3)
2
澶?/p>
2
鍚?/p>
=IF(AND(COUNTIF(B2:D2,">=5")=2,SUM(COUNTIF(B2:D2,{0,4,6,8,9}))=2),"2
澶?/p>
2
鍚?/p>
","")
15
闅旇鍏紡
=IF(MOD(ROW(),11)=5,A1,"")
琛ㄧず浠庣
5
鏍煎紑濮嬪彇闂撮殧
11
琛岀殑鏁?/p>
=4
琛?/p>
绀轰粠绗?/p>
4
鏍艰捣銆?/p>
16
鏂囨湰鍏紡
=IF(A2="
濂囧濂?/p>
",A2
锛?/p>
"")
琛ㄧず鑻ユ湁
"
濂囧濂?/p>
"
灏卞～
"
濂囧濂?/p>
"
鍙嶄箣涓虹┖銆?/p>
16
鑷姩搴忓垪濉厖鍏紡
=IF(A2="
澶уぇ灏?/p>
",A2,IF(A1="
澶уぇ灏?/p>
",1
锛?/p>
B1+1))
娉ㄦ剰
A2
涓?/p>
A1
鐨勫尯
鍒?/p>
,B2
鏄紪鍏紡鍖猴紙
A1,B1
锛変负绌虹櫧鏍?/p>
17
瀛?/p>
浼?/p>
閭?/p>
鍏?/p>
寮?/p>
=IF(OR
锛?/p>
B3=B2,B3=C2,B3=D2),"
浼?/p>
",IF(OR(ABS(B3-B2)=1,ABS(B3-C2)=1,ABS(B3-D2)=1,ABS(B3-B2)=9,ABS(B3-C2)=9,
ABS(B3-D2)=9),"
閭?/p>
","
瀛?/p>
"))&
澶嶅埗鍓嶉潰锛?/p>
B3
鏀?/p>
C3)&
澶嶅埗鍓嶉潰锛?/p>
B3
鏀?/p>
D3)
鍗?/p>
鍙?/p>
18
澶嶉殧涓?/p>
=if(or(B3=B2,B3=C2,B3=D2),"
澶?/p>
",if(or(B3=B1,B3=C1,B3=D1),"
闅?/p>
","
涓?/p>
"))&
澶嶅埗
鍓嶉潰
IF(COUNTIF(B5:D5,B6)>=1,"
澶?/p>
",IF(COUNTIF(B4:D5,B6)=0,"
涓?/p>
","
闅?/p>
")
A
琛屾敼鎴?/p>
8
鍒?/p>
=OFFSET($A$1,(COLUMN(A1)-1)*8+ROW(A1)-1,0)
8
琛ㄧず
8
鍒?/p>
19
鍐风儹娓╁叕寮?/p>
=IF(COUNTIF(A2:C8,A9)<=1
锛?/p>
"
鍐?/p>
",IF(COUNTIF(A2:C8,A9)=2,"
娓?/p>
","
鐑?/p>
"))&
澶嶅埗鍓嶉潰锛?/p>
A9
鏀?/p>
B9)&
澶嶅埗鍓嶉潰锛?/p>
B9
鏀?/p>
C9)
鍗冲彲
20
涓?/p>
鐮?/p>
搴?/p>
鍒?/p>
鍏?/p>
寮?/p>
=IF(AND(COUNTIF(B3:D3,0)>=1,COUNTIF(B3:D3,1)>=1),"01",IF(AND(COUNTIF(B2:D2,0
)>=1,COUNTIF(B2:D2,1)>=1),1,E2+1))
21
鍜屽�艰嚜鍔ㄥ簭鍒楀叕寮?/p>
=IF(E3=12,E3,IF(E2=12,1,D2+1))
璇存槑
E2-D2
涓虹┖鐧芥牸锛?/p>
E3
鍜屽�?/p>
鍖猴紝
D3
缂栧叕寮忓尯
22
璧板娍鏌ヨ鍏紡
=IF(AND(A1=1,A2=3
锛?/p>
锛?/p>
A3,"")
璇存槑
1-3
鍚庨潰
A3
鏈�鏄撳紑浠�涔堢殑鍏紡
23
璧?/p>
鍔?/p>
鍏?/p>
寮?/p>
=IF(B1=0,B1,""
锛?/p>
璇?/p>
鏄?/p>
褰?/p>
B1=0
鏃?/p>
杩?/p>
鍥?/p>
鍘?/p>
鍊?/p>
,
鍚?/p>
鍒?/p>
涓?/p>
绌?/p>
.
绛?/p>
鍚?/p>
=IF(B1="0","0",""
锛?/p>
鎴?/p>
=IF(COUNTIF(B1:C1,"0")>=1,"0","")
24
浣?/p>
鍜?/p>
鍏?/p>
寮?/p>
=RIGHT((B2+C2+D2)*2
锛?/p>
浣?/p>
宸?/p>
鍏?/p>
寮?/p>