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

°ÊºA¤U©Ô±ø¥ó¨Ã°Ï¶¡²Î­p¸ê®Æ.

°ÊºA¤U©Ô±ø¥ó¨Ã°Ï¶¡²Î­p¸ê®Æ.

1¡B
B1¤ÎB2Àx¦s®æ¬O§ì¨údata¤u§@ªíªºB,C,DÄæ¡A
§Æ±æ¥i¥H³]©w¬°¤U©Ô¦¡¿ï³æ¡A
¥t¥~¡AÀHµÛdata¤u§@ªí¸ê®Æ¤£Â_·s¼W¡AB1¤ÎB2Àx¦s®æ¤]¦Û°Ê·s¼W

2¡B
B2Àx¦s®æ¦³¥i¯à¬OªÅ¥Õ¡A¤]´N¬O»¡¡A¥u¦³²Î­p³æ¤ë¡A
B1³]©w¤@­Ó¤é´Á¡AB2¤£»Ý­n³]©w

3¡B
²Å¦XB1,B2Àx¦s®æ¡A
DÄæ§ì¨údata¤u§@ªíªºIÄæ
EÄæ²Î­pdata¤u§@ªíªºHÄæ
FÄæ²Î­pdata¤u§@ªíªºGÄæ
¸ê®Æ²Î­p«á¡A¦b³Ì«á¤@¦C¡AÅã¥Ü¦X­p¦r²´¨Ã¥[Á`EÄæ¤ÎFÄæª÷ÃB

Âà¦C¤ÀªR103.09.zip (57.82 KB)

joyce

¦^´_ 1# leiru
¤j¶qªº°}¦C¸ê®Æ³B²z¡A§Úªº¹q¸£§Ö¶]¤£°Ê¤F
¸Õ¸Õ¬Ýªþ¥ó
Âà¦C¤ÀªR103.09.rar (64.71 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 1# leiru

¼W¥[¤@¨Ç»²§UÄæ¥i¥H¥[³t¸ê®Æ­pºâ
°Ñ¦ÒÀÉ®×
Âà¦C¤ÀªR103.09_ML089.rar (129.8 KB)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

ÁÂÁ¨â¦ìHsieh¤ÎML089
½Ð±Ð,­Youtput¤u§@ªíªºd,e,fÄæ¸ê®Æ±a¥X¨Ó«á,¬O§_¥i¥H¦bdÄæ³Ì«á¤@¦C±a¥X"¦X­p",µM«á¦be¤ÎfÄæ,¦Û°Ê¥[Á`

·PÁ¨â¦ì.==
joyce

TOP

¦^´_ 3# ML089


    ½Ð±Ðdata¤u§@ªíªº»²§UÄæmÄæ,¬°¦ó­n¥[³o¤@Äæ,¬O§_¥i¥H¸ÑÄÀ¤½¦¡·N«ä.

ÁÂÁÂ
joyce

TOP

¦^´_ 5# leiru

dataªí®æªºMÄæ¡A¥D­n¬O¨ú¥X²Å¦X±ø¥óªºIÄ椣­«½Æ­È¦C¸¹
    =IF(L3,IF(MATCH(I3&1,I$1:I3&L$1:L3,)=ROW(),ROW(),"-"),"-")

outputªí®æDÄ椽¦¡¤¤¡A´N¥i¥H²³æ¥Î SMALL(data!M:M,ROW(A1)) ¨ú±odataªí®æªºI±o¦C¸¹
=IFERROR(INDEX(data!I:I,SMALL(data!M:M,ROW(A1))),"")


³o¨Ç»²§UÄæ­Yê²´¤]¥i¥H¥Î ¦WºÙ ¥N´À¡A¹³¶WªOªº°µªk
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 4# leiru
  1. D2 =IF(ROW(A1)<=COUNT(data!M:M),INDEX(data!I:I,SMALL(data!M:M,ROW(A1))), IF(ROW(A1)=COUNT(data!M:M)+1,"¦X­p",""))
  2. E2 =IF(D2="","",IF(D2="¦X­p",SUM(INDIRECT("R1C:R[-1]C",)),SUMIFS(data!H:H,data!L:L,1,data!I:I,D2)))
  3. F2 =IF(D2="","",IF(D2="¦X­p",SUM(INDIRECT("R1C:R[-1]C",)),SUMIFS(data!G:G,data!L:L,1,data!I:I,D2)))
½Æ»s¥N½X
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

ÁÂÁÂML089±zªº¸Ñµª.
joyce

TOP

        ÀR«ä¦Û¦b : ¡i»X½ªªº¦Û¥Ñ¡j¤H±`¦b¤°»ò³£¥i¥H¦Û¥Ñ¦Û¦bªº®É­Ô¡A«o³Q³oºØÀH¤ß©Ò±ýªº¦Û¥Ñ»X½ª¡AµêÂY®É¥ú¦Ó²@µLıª¾¡C
ªð¦^¦Cªí ¤W¤@¥DÃD