ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

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

§Ú·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¹Ï¤ù

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

¦^´_ 1# peter800725

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

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

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

TOP

¦^´_ 2# JBY

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

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

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

TOP

¥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))

TOP

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

TOP

¦^´_ 3# peter800725

=COUNT(1/COUNTIF(OFFSET(A5,{-2,-1,0,1,2},{-2;-1;0;1;2},),5))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 3# peter800725

¤]¥i¥H¥Î N(OFFSET(....)))
=COUNT(1/(N(OFFSET(A5,{-2,-1,0,1,2},{-2;-1;0;1;2},))=5))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

=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

TOP

        ÀR«ä¦Û¦b : ¯u¥¿ªº·R¤ß¡A¬O·ÓÅU¦n¦Û¤vªº³oÁû¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD