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

[µo°Ý] ¨ç¼Æ¤¤ªº¦ì¸m¦p¦ó¥Î¨ä¥L¨ç¼Æ¨ú¥N

[µo°Ý] ¨ç¼Æ¤¤ªº¦ì¸m¦p¦ó¥Î¨ä¥L¨ç¼Æ¨ú¥N

¥»©«³Ì«á¥Ñ wind6424 ©ó 2019-5-2 17:56 ½s¿è

°²³] MATCH("NW",Sheet1!A:A,0)=32
½Ð±Ð­n¦p¦ó§â "MATCH("T1",Sheet2!32:32,0)" ³o¤½¦¡¤¤ªº32:32 ¼Æ¦r³¡¤À³£¥Î "MATCH("NW",Sheet1!A:A,0)" ¨ú¥N
¥t¥~¦p¤½¦¡¬O "MATCH("TX",Sheet2!A32:A3000,0)",
¨º¦¹¤½¦¡¤ºªº"A32"¤¤«á­±¼Æ¤l"32"­n¦p¦ó¥Î"MATCH("NW",Sheet1!A:A,0)" ¨ú¥N

¤U­±°ÝÃD¥ý°Ý°_¨Ó³Æ¥÷
¦pªG·Q§â"A32"¤¤ªºA¤]¥Î¤½¦¡¨ú¥N,¨Ò¦p¥Î "MATCH("BT",Sheet1!10:10,0)=9"¨ú¥N­n¦p¦ó°µ

¦^´_  wind6424

¦³ÂI³Â·Ð, ¥Î¤F´X­Ó©w¸q¦WºÙ:
­ã´£³¡ªL µoªí©ó 2020-5-15 20:42


¬ÝÀ´±zªº©w¸q¦p¦ó¤¬¬Û®M¥Î,
¯uªº«D±`·PÁ±z,
ÁÂÁÂ

TOP

¦^´_ 8# wind6424


¦³ÂI³Â·Ð, ¥Î¤F´X­Ó©w¸q¦WºÙ:
Xl0000167.rar (7.68 KB)

TOP

¦^´_  wind6424

§ì½d³ò???
=INDEX(A:A,SMALL(IF(A1:A21="Á`­p",ROW(1:21)),2)):INDIRECT(TEXT(SMALL(I ...
­ã´£³¡ªL µoªí©ó 2020-5-15 14:45


·PÁ ­ã´£³¡ªL ¤j±zªº¦^ÂÐ,§Ú¦b¬ã¨s¬Ý¬Ý
Ãö©ó§ì½d³ò
¥u­n¯à§ì¥Xªí¤G¨â­ÓÁ`­p¦ì¸m,
´N¥i¥H§Q¥Î OFFSET ³o¨ç¼Æ¨Ó®M¥Î¨ì¹Ï¤W,¥H«á¥u­n§ó·s¸ê®Æ
§Ú¬O·Q­n³z¹LÁ`­p¦ì¸m§ì¥XÂŦâ¸ò¶À¦â®Ø®Ø¼Æ¾Ú¦ì¸m,»s°µ¦¨­ì»æ¹Ï¸òª½±ø¹Ï,
ªí¤@°_©l¦ì¸m©T©w¤ñ¸û¦n¼g,ªí¤G¹J¨ì¤£ª¾¹D¦p¦ó§â¨ä¥L¨ç¼Æ¨úªº­È®M¥Î¨ì¥t¤@­Ó¨ç¼Æ¤º
2.png
2020-5-15 17:54

TOP

¦^´_ 6# wind6424

§ì½d³ò???
=INDEX(A:A,SMALL(IF(A1:A21="Á`­p",ROW(1:21)),2)):INDIRECT(TEXT(SMALL(IF(B1:G21="Á`­p",ROW(1:21)/1%+COLUMN(B:G)),2),"!r0c00"),)
²Ä¤@ªí:2 §ï 1

©Î:
=INDIRECT(TEXT(SUM(SMALL(IF(A1:G21="Á`­p",ROW(1:21)/1%+COLUMN(A:G)),{3,4})*10^{4,0}),"!r0c00!:!r00c00"),)
²Ä¤@ªí: {3,4} §ï {1,2}

TOP

B5:
=SUM(B$1:B4)-SUMIF($A$1A4,"Á`­p",B$1:B4)*2

³o¬O¥ý¥[«á´îªk, °Ñ¦Ò§Y¥i(­Y¦³¿ù»~µLªkÀˬd)
­ã´£³¡ªL µoªí©ó 2020-5-15 11:30


­ã´£³¡ªL ¤j¤j«D±`·PÁ±zªº¦^ÂÐ,
«D±`©êºp§Úªí¥Ü¤£²M·¡,¦pªG¬O©T©w¤è¦¡´M§äÁ`¼Æ¦ì¸m§Úª¾¹D¦p¦ó°µªº,

°ÝÃD¦b§Úªºªí·|¤@ª½·s¼W,§Ú·Q­n¯à¥Î¨ç¼Æ§ì¥X²Ä¤Gªíªº¾ã­Ó¦ì¸m½d³ò,§Ú´N¥i¥HÀ³¥Î¦b¹Ïªí¦Û°ÊÅã¥Ü¤W,
§Ú¬O·Q­n³]­p¨â­Ó¹Ïªí,¤@­Ó¬OÅã¥Ü·s¼Wªº³¡¥÷,²Ä¤G­Ó¬OÅã¥Ü«e¤@­Óªíªº¼Æ¾Ú,¨C¦¸¥Î©Ôªº«Ü·Ð,
©Ò¥H·Q­n¥Î¨ç¼Æ¨Ó§ì¨ú,¹Ï¥Ã»·Åã¥Ü«e¨â­Óªíªº¸ê®Æ

¥Ø«e§Ú¥i¥H§ì¨ì¦bAÄæ²Ä¤Gªíªº"¶µ¥Ø"¦ì¸m¸ò"Á`­p"¦ì¸m,
ÁÙ®t¤@­Ó²Ä7¦Cªº¥k¤è"Á`­p"¦ì¸m,
³o­Ó¦ì¸m¥Î MATCH("Á`­p",¤u§@ªí1!7:7,0) ¬O¥i¥H¨ú±o,
°ÝÃD¨C¦¸·s¼W¸ê®Æªíªº¤º®e¤j¤p³£·|¤£¤@¼Ë,¾É­P²Ä¤Gªí¥Ø«e¬Ý¨ìªº 7:7 ¦C·|¬O¯B°Êªº,
¦Ó7³o­Ó¼Æ¦r§Ú¤w¥i¥H¥Î {=INDEX(SMALL(IF(INDIRECT("'¤u§@ªí1'!$A$1:$A$500")="¶µ¥Ø",ROW(1:500)),ROW(1:500)),2)}³o­Ó¨ç¼Æ¨ú±o,
¦ý§Ú¤£ª¾¹D­n¦p¦ó§â¨úªº­È®M¥Î¦b MATCH ¨ç¼Æ¤º¨ú«Ý¿é¤Jªº "7:7"³o­Ó­È,
¥u­n¥i¥H¨ú¥N©Î¬O¨ä¥L¤è¦¡¥i¥H§ì¥X¯B°Ê¦ì¸m,´N¥i¥H®M¥Î¨ì¹Ï¤W

TOP

B5:
=SUM(B$1:B4)-SUMIF($A$1:$A4,"Á`­p",B$1:B4)*2

³o¬O¥ý¥[«á´îªk, °Ñ¦Ò§Y¥i(­Y¦³¿ù»~µLªkÀˬd)

TOP

D3:¤U©Ô
=SUM(OFFSET(D3,,-1,,1-COLUMN(D$1)))


B5/°}¦C¤½¦¡:¥k©Ô
=SUM(OFFSET(B5,-1,,MATCH(1,0/($A$1:$A4="¶µ¥Ø"))-ROW()+1))
©Î/¤@¯ë¤½¦¡
=SUM(OFFSET(B5,-1,,MMULT(1,MATCH(1,0/($A$1:$A4="¶µ¥Ø"))-ROW()+1)))
=SUM(OFFSET(B5,-1,,LOOKUP(1,0/($A$1:$A4="¶µ¥Ø"),ROW($1:4))-ROW()+1))


=============================

TOP

¹L¥hµo°Ý·í®É¥Î¤F§Oªº¤è¦¡³B²z´N§Ñ¤F³o­Ó´£°Ý,
³o¦¸¹J¨ì°ÝÃDµo²{¬O¤@¼Ëªº,­«·sµo°Ý

ºô¸ô¤W§ä¨ì¤è¦¡¥i¥H¥Î°}¦C¤è¦¡·j´M¨ìAÄæ²Ä¤G­Ó"¶µ¥Ø"¦ì¸m
{=INDEX(SMALL(IF(INDIRECT("'¤u§@ªí1'!$A$1:$A$500")="¶µ¥Ø",ROW(1:500)),ROW(1:500)),2)}
°ÝÃD¤@ : ·Q½Ð°Ý¦³¥i¥H¤£¥Î°}¦Cªº¤è¦¡¨ú¥N¤W¤èªº¤½¦¡¶Ü? ,©Î¬O¦Û­q¤½¦¡¤º­n¦p¦ó¿é¤J¤W¤è°}¦Cªº¨ç¼Æ?

°ÝÃD¤G : µM«á§Ú·Q¥Î¤W¤è¤½¦¡§ä¨ì"¶µ¥Ø"¦ì¸mªº­È¨Ó¨ú¨ì¤U¤èMATCH¤½¦¡¤ºªº "¤u§@ªí1!7:7 "³o­Ó¦r¦ê,¨ç¼Æ­n¦p¦ó®M¥Î
=MATCH("Á`­p",¤u§@ªí1!7:7,0)

test.png
2020-5-15 00:14

test.zip (7.14 KB)

TOP

½Ð´£¨ÑÀÉ®× §Ú¦bÀ°§A

TOP

        ÀR«ä¦Û¦b : ¦³´¼¼z¤~¯à¤À¿ëµ½´c¨¸¥¿¡F¦³Á¾µê¤~¯à«Ø¥ß¬üº¡¤H¥Í¡C
ªð¦^¦Cªí ¤W¤@¥DÃD