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

¤U©Ô¦¡²M³æÃöÁp°Ñ·Ó°ÝÃD

¤U©Ô¦¡²M³æÃöÁp°Ñ·Ó°ÝÃD

¸Ôªþ¥ó, ½Ð°Ý...
1. A2 ²M³æÂI¿ïÃþ§O(ex:AX)
2. B2¡BC2¡BD2¡BE2¤U©Ô¦¡²M³æ¤º®e±a¥X»PA2Ãþ§O¬ÛÃö²M³æ
3. data¸ê®Æ®w·|§ó·s, °Ñ·Ó½d³ò¯à¦Û°Ê§ó·s
4. ¤U©Ô¦¡²M³æ°Ñ·Ódata½d³ò¯à¸õ¹L(±Æ°£)ªÅ¥Õ¸ê®Æ®æ

Book1Qx.rar (14.91 KB)

¦^´_ 1# 074063

¤U©Ô¦¡²M³æÃöÁp°Ñ·Ó°ÝÃD.rar (17.5 KB)

¦WºÙ³]©w (¨Ï¥Î©ó¸ê®ÆÅçÃÒ¤¤ªº²M³æ)
¤£¨}¦]¯À        =OFFSET(data!$F$27,,MATCH(Sheet2!$A2,data!$F$26:$J$26,)-1,COUNTA(OFFSET(data!$F$27:$F$34,,MATCH(Sheet2!$A2,data!$F$26:$J$26,)-1)))
§@·~¤H­û        =OFFSET(data!$L$3,,MATCH(Sheet2!$A2,data!$L$2:$P$2,)-1,COUNTA(OFFSET(data!$L$3:$L$12,,MATCH(Sheet2!$A2,data!$L$2:$P$2,)-1)))
²£«~        =OFFSET(data!$A$1,MATCH(Sheet2!$A2,data!$B:$B,)-1,,COUNTIF(data!$B:$B,Sheet2!$A2))
¹s¥ó        =OFFSET(data!$F$19,,MATCH(Sheet2!$A2,data!$F$18:$J$18,)-1,COUNTA(OFFSET(data!$F$19:$F$24,,MATCH(Sheet2!$A2,data!$F$18:$J$18,)-1)))
»sµ{        =OFFSET(data!$F$3,,MATCH(Sheet2!$A2,data!$F$2:$J$2,)-1,COUNTA(OFFSET(data!$F$3:$F$16,,MATCH(Sheet2!$A2,data!$F$2:$J$2,)-1)))
Ãþ§O        =OFFSET(data!$D$2,,,COUNTA(data!$D:$D)-1)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 2# ML089


    ÁÂÁÂML089¤j¤j«ü¾É :)

TOP

¦^´_ 2# ML089


        ¤j¤j¤£¦n·N«ä, ¦³¨Ç³¡¥÷ÀY¸£ÁÙÂण¹L¨Ó, ´£¨Ñ¤@¥÷ªþ¥ó¬O§_¯à½Ð¤j¤jÀ°¼g²[¼Æ

        °Ñ·Ó¸ê®Æ¦bdata¤u§@ªí (¸ê®Æ·|¦³·s¼W©Î­×§ï)
        B:G ¬°¤U©Ô¦¡²M³æ
        C:G ¤U©Ô¦¡²M³æ¤º®e»PBÄæ¦ì¬ÛÃöÁp (ex: B2ÂI¿ïAX, C:G¤U©Ô¦¡²M³æ¥u·|±a¥X»PAX¬ÛÃöªº²M³æ¤º®e¨Ñ¿ï¨ú)
        ©Ò¦³¤U©Ô¦¡²M³æ¤º®e¤£­n¦³ªÅ¥Õ­È

        ¥H¤W½Ð°Ý²[¼Æ¦p¦ó¼gªk

Book1x.rar (52.22 KB)

TOP

¦^´_ 2# ML089


    ¦WºÙ³]©w (¨Ï¥Î©ó¸ê®ÆÅçÃÒ¤¤ªº²M³æ)
¤£¨}¦]¯À        =OFFSET(data!$F$27,,MATCH(Sheet2!$A2,data!$F$26:$J$26,)-1,COUNTA(OFFSET(data!$F$27:$F$34,,MATCH(Sheet2!$A2,data!$F$26:$J$26,)-1)))
§@·~¤H­û        =OFFSET(data!$L$3,,MATCH(Sheet2!$A2,data!$L$2:$P$2,)-1,COUNTA(OFFSET(data!$L$3:$L$12,,MATCH(Sheet2!$A2,data!$L$2:$P$2,)-1)))
²£«~        =OFFSET(data!$A$1,MATCH(Sheet2!$A2,data!$B:$B,)-1,,COUNTIF(data!$B:$B,Sheet2!$A2))
¹s¥ó        =OFFSET(data!$F$19,,MATCH(Sheet2!$A2,data!$F$18:$J$18,)-1,COUNTA(OFFSET(data!$F$19:$F$24,,MATCH(Sheet2!$A2,data!$F$18:$J$18,)-1)))
»sµ{        =OFFSET(data!$F$3,,MATCH(Sheet2!$A2,data!$F$2:$J$2,)-1,COUNTA(OFFSET(data!$F$3:$F$16,,MATCH(Sheet2!$A2,data!$F$2:$J$2,)-1)))
Ãþ§O        =OFFSET(data!$D$2,,,COUNTA(data!$D:$D)-1)


°ÝÃD:
´ú¸Õ¤F¤@¤U, ­YÃþ§O¸ê®Æ¨S¦³±Æ§Ç, ²£«~¤U©Ô²M³æµLªk¥¿½T»PÃþ§O¹ïÀ³,  ÁٽФj¤j«ü¾É:'(

TOP

¦^´_ 4# 074063

¤U©Ô²M³æ(¤½¦¡«¬).rar (56.53 KB)

{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 5# 074063

ÀɮצP¼Ó¤W

{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# ML089


    2015-07-30_084350.jpg

    ­ì©l¸ê®Æ(data¤u§@ªí) ³æ¦ì(A:A)¨S¦³±Æ§Ç,  Sheet1²£«~¤U©Ô²M³æ¨ÌµM¨S¥¿½T¹ïÀ³³æ¦ì

TOP

¦^´_ 7# ML089


    ¤£¦n·N«ä, ½Ð°Ý¤j¤j¬O§_¦³ªk¸Ñ

TOP

¦^´_ 9# 074063

ÁöµM¤½¦¡³£¹ï¡A¦ý ²M³æ ¦n¹³¤£±µ¨ü¥Ñ¤½¦¡²£¥Íªº°}¦C­È¡C

»Ý­n¥[ÂI VBA ³B²z¡A¦A±N±Æ§Ç«áªº¸ê®Æ¼g¦^ªí³æ¸Ì¦A¤Þ¥Î¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¤£©È¨Æ¦h¡A¥u©È¦h¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD