[µo°Ý] ­pºâÀx¦s®æ©P³òªº¬Y¯S©w­È¦³¦h¤Ö­Ó

peter800725

§Ú¥Ø«e·Qºâªº¬O

¤U¸ü (33.3 KB)

2015-3-24 17:42

§Ú·Q­n­pºâ¾ï¦â³o­ÓÀx¦s®æ©P³ò5X5ªºÀx¦s®æ(ºñ¦â³¡¤À)¤¤¦³¦h¤Ö­Ó5¥X²{
­n¦p¦ó¥Î¨ç¼Æ¨Ó¼g
§Ú¬O¦³¬Ý¨ìCountif(½d³ò,"¼Æ­È") ¥i¥Hºâ
¦ý¬O¦b½d³òªº³¡¤À­n«ç»ò§âÀx¦s®æªº¬Û¹ï¦ì¸m¼g¶i¥h
¬Û¹ï¦ì¸m§Ú¦³¦³¤â¼g¤U¨Ó¦A¤U­±³o±i¹Ï¤ù

¤U¸ü (86.67 KB)

2015-3-24 17:51

¤£ª¾¹D¦³¨S¦³°ª¤â¯àÀ°§Ú¸Ñµª ·PÁÂ

hcm19522

=COUNT(1/(N(OFFSET(INDIRECT(I2),ROW(1:5)-3,COLUMN(A:E)-3))=J6))
¬Ý¤F "ML¤j" ³Ç§@ °Ñ¦Ò  I2¬O¤¤¤ßÂI  J6¬O«ü©w¥X²{¼Æ¦r

ML089

¦^´_ 3# peter800725

¤]¥i¥H¥Î N(OFFSET(....)))
=COUNT(1/(N(OFFSET(A5,{-2,-1,0,1,2},{-2;-1;0;1;2},))=5))

ML089

¦^´_ 3# peter800725

=COUNT(1/COUNTIF(OFFSET(A5,{-2,-1,0,1,2},{-2;-1;0;1;2},),5))

hcm19522

http://blog.xuite.net/hcm19522/twblog/360992856

JBY

¥t¥~¦pªG¹J¨ì³oºØ±¡§Î­n ...
peter800725 µoªí©ó 2015-3-24 22:28

1] B20, 输¤J¾ï¦âÀx¦s®æªº¦a§},  举¨Ò来说, 输¤J A5

2] B21, 输¤J¤½¦¡ :

=SUMPRODUCT(--(OFFSET(INDIRECT(B20),LOOKUP(CELL("row",INDIRECT(B20)),{1,0;2,-1;3,-2}),LOOKUP(CELL("col",INDIRECT(B20)),{1,0;2,-1;3,-2}),5,5)=5))

peter800725

¦^´_ 2# JBY

·PÁ±z³o­Ó¥i¥H¥Î

¦ý¬O¦pªG§Ú­nºâªºÀx¦s®æªø±o¹³¤U­±³o¼Ë

¤U¸ü (4.4 KB)

2015-3-24 22:26

¤U¸ü (4.25 KB)

2015-3-24 22:26

¥t¥~¦pªG¹J¨ì³oºØ±¡§Î­nºâ¨ä©P³ò5X5ªºÀx¦s®æ
¦ý¬OÃä¬É¦pªG¨S¦³¸ê®Æªº¸Ü¸Ó«ç»ò¿ì

JBY

¦^´_ 1# peter800725

¾ï¦â³o­ÓÀx¦s : E9

­pºâ©P³ò5X5ªºÀx¦s®æ(ºñ¦â³¡¤À)¤¤¦³¦h¤Ö­Ó5 :

=SUMPRODUCT(--(OFFSET(E9,-2,-2,5,5)=5))