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

Offset & Mod ¨ç¼Æ

Offset & Mod ¨ç¼Æ

offset&mod¨ç¼Æ.zip (6.57 KB)

¦U¦ì¦n,
·Q½Ð±Ð¥H¤U¨ç¼Æ¬O«ç»ò¨Óªº?
1.  ROW(1:1)     «á­±¬°¤°»ò­n´î1©O?
2.  §Úª¾¹D20 ¬O20­Ó¸ê®ÆÄ檺·N«ä, ¦ý¬°¤°»ò­n¥Î "/"  ?
3.  MOD(ROW(1:1)-1,20)   ³o­Ó´N§¹¥þ¤£©ú¥Õ¤F

OFFSET(Sheet1!$A$1,(ROW(1:1)-1)/20,MOD(ROW(1:1)-1,20))

¦³¤£©ú¥Õªº¦a¤èµ½¥Î½s¿è¦C¤¤¤Ï¥Õ¤½¦¡¤¤ªº¬Y¤@¤p¬q¦A«öF9¥i¬Ý¥X¦b¤½¦¡¤¤ªº¹Bºâµ²ªG.¨Ó¤F¸Ñ¤½¦¡ªº¹B§@
ROW(1:1)©¹¤Ucopy ¦b²Ä¤@¦Cµ¥©ó1¦b²Ä¤G¦Cµ¥©ó2......20¦Cµ¥©ó20
(ROW(1:1)-1)/20¦b²Ä¤@¦C·|±o¨ì0,¦b²Ä¤G¦C·|±o¨ì0.......²Ä21¦C±o¨ì1
¬°¤F­n«e­±20¦C³£±o¨ì0©Ò¥H­n´î1,§_«h²Ä20¦C´N·|±o¨ì1¤F
MOD(ROW(1:1)-1,20)
¦P¼Ëªº¹D²zMOD(ROW(1:1)-1,20)¥u¬O¥Øªº¤£¦P,¦b²Ä¤@¦C­n±o¨ì0²Ä¤G¦C­n±o¨ì1....²Ä20¦C­n±o¨ì19,
¦ý²Ä21¦C­n±o¨ì0²Ä22¦C­n±o¨ì1......

TOP

¦^´_ 1# yc1031 [/b)
OFFSET(Sheet1!$A$1,(ROW(1:1)-1)/20,MOD(ROW(1:1)-1,20))

°Ý1.  ROW(1:1)     «á­±¬°¤°»ò­n´î1©O?
µª1. ROW(1:1) ¬O¤½¦¡¤U©Ô­p¼Æ¾¹¡A¥Ñ1¡B2¡B3 ...
       OFFSET(Sheet1!$A$1, ¦C¦ì¸m¬O¥Ñ 0¡B1¡B2 ... ©Ò¥H ROW(1:1)-1 ¤~¯à¥Ñ0°_­p¼Æ

°Ý2.  §Úª¾¹D20 ¬O20­Ó¸ê®ÆÄ檺·N«ä, ¦ý¬°¤°»ò­n¥Î "/"  ?
µª2. ROW(1:1)-1)/20 ¬O²¤Æ¼gªk¡AÀ³¸Ó¬O INT(ROW(1:1)-1)/20)¡A¥H¨C20¦C¼W¥[1ªº¤è¦¡¥X²{¼Æ¦r¡A0,0,0,(20­Ó 0)....1,1,1...(20­Ó 1)....

°Ý3.  MOD(ROW(1:1)-1,20)   ³o­Ó´N§¹¥þ¤£©ú¥Õ¤F
µª3. ROW(1:1) ¬O¤½¦¡¤U©Ô­p¼Æ¾¹¡A¥Ñ1¡B2¡B3 ...
        ¥~¥[MOD(....,20) ªí¥Ü¨C20­Ó°µ¤@¦¸´`Àô°O¼Æ 0,1,2....,19,  0,1,2.....,19,  0,1,2...

¨ç¼Æ»¡©ú¦b¨ç¼Æ¤W«öF1´N¥i¥H±o¨ì»¡©ú
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¥ÌÄ@°µ¡BÅw³ß¨ü¡C
ªð¦^¦Cªí ¤W¤@¥DÃD