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

«ô°U¦U¦ì°ª¤â °ª°ª¤â¡C¡C¡C

«ô°U¦U¦ì°ª¤â °ª°ª¤â¡C¡C¡C

«ô°U¦U¦ì°ª¤â °ª°ª¤â¡C¡C¡C

.¥i¥H¦bF3Äæ¦^¶Ç
A¬¡­¶Ã¯(·j´M ¥Î¤ñ¹ï¸ê®Æ¤è¦¡ ¤ñ¹ï©m¦W¡B¦~¤ë¤é¡B
²Ä¤@¦¸·j´M¨ìªº¸ê®Æ¦^¶Ç¨ì²Ä¤@®æ
²Ä¤G¦¸·j´M¨ìªº¸ê®Æ¦^¶Ç¨ì²Ä¤G®æ
²Ä¤T¦¸·j´M¨ìªº¸ê®Æ¦^¶Ç¨ì²Ä¤T®æ
²Ä¥|¦¸·j´M¨ìªº¸ê®Æ¦^¶Ç¨ì²Ä¥|®æ
²Ä¤­¦¸·j´M¨ìªº¸ê®Æ¦^¶Ç¨ì²Ä¤­®æ
²Ä¤»¦¸·j´M¨ìªº¸ê®Æ¦^¶Ç¨ì²Ä¤»®æ
²Ä¤C¦¸·j´M¨ìªº¸ê®Æ¦^¶Ç¨ì²Ä¤C®æ
²Ä¤K¦¸·j´M¨ìªº¸ê®Æ¦^¶Ç¨ì²Ä¤K®æ
²Ä¤E¦¸·j´M¨ìªº¸ê®Æ¦^¶Ç¨ì²Ä¤E®æ

§Ú¤£ª¾¹D¡C¡C¡C³o¼Ëªí¥Ü ¦U¦ì¤j¤jÀ´¤£À´
¡C¡C¡C¡C
³Â·Ð¦U¦ì¤j¤j À°À°§Ú ¥i¥H¥Î¤°»ò¤½¦¡ ¥i¥H¼g¡C
¥ÎLOOKUP ¤è¦¡¼g¥u¯à¼g¦^¶Ç©T©wªºÄæ¦ì­È ¹ï§a?!
¥ÎIF ¤S©Ç©Ç  ¹Ä®ð

«ô°U¤F.rar (36.66 KB)

³o¬OªþÀÉ

Cyssuc

¬O¤£¬O §Ú°Ýªº¤£°÷©ú¥Õ©O?!
¤j®a¤£­n¨«¡C¡C¡C¡C¡C
Cyssuc

TOP

  1. F3 =IF(COLUMN(A1)/2>SUMPRODUCT(1*($B3&$C3&$D3&$E3=A!$A$2:$A$1999&A!$B$2:$B$1999&A!$C$2:$C$1999&A!$G$2:$G$1999)),"",
  2. OFFSET(A!$D$1,MATCH($B3&$C3&$D3&$E3,A!$A$2:$A$1999&A!$B$2:$B$1999&A!$C$2:$C$1999&A!$G$2:$G$1999,)+INT((COLUMN(A1)-1)/2),MOD(COLUMN(A1)-1,2)) )
½Æ»s¥N½X
CTRL+SHIFT+ENTER »ô«ö¤è¦¡¿é¤J¤½¦¡

»~¥´¥d³¡¤À»Ý­n¤H¤u­×¥¿
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-7-27 22:36 ½s¿è

¦^´_ 2# Cyssuc
§^¤H¸ÑÃD®É¦]®É¶¡®t¥¼¬Ý¨ìML089ª©¥D¤w¸Ô²Ó«ü¾É¡A¦³½Ðª©¥D§R°£¥»¦^ÂСA³y¦¨§xÂZ¤£«K¤§³B·q½Ð¨£½Ì¡AÁÂÁ¡I

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-7-27 22:40 ½s¿è

¦^´_ 3# ML089
«ô°UML089ª©¥D²M°£4#»P5#ªº¦^ÂСA¥H§K³y¦¨¦U¦ì¾Ç²ßªº§xÂZ¡AÁÂÁ¡I

TOP

¦^´_ 5# p212


P212¤j¡A
§Ú¤£·|§RÃD¾Þ§@
¬Ý¹L§A¥H«eªº¸ÑÃD»¡©ú«Ü¸Ô²Ó¡A¤]«Ü¾A¦X¤j²³¾Ç²ß¡A½Ð¤£­n¦]¬°§Ú¦³µªÃD´N°±µ§¡A³o¼Ë·|³y¦¨¥»½×¾Â¤Î¤j²³ªº·l¥¢¡C
§Ú¥H«e·R¼gµu¦¡©Ò¥H¦¡¤l¤ñ¸û°¾®p¹ïªì¾Ç¤Ï¦Ó¤£©ö¸ÑŪ¡A¤£¦Pªº¦¡¤l¦U¦³»Ý¨D¤Î³ß·R¡A¤j®a¤¬¬ÛÆ[¼¯´N¦n¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 3# ML089


    ÁÂÁ±zªº¦^ÂÐ
¤£¹L¤p§Ì¤£§÷ ¹ê¦b¬O¬Ý¤£À´
©Ò¿×ªº¥´¥d¸ê®Æ­n¾ã²z
¬O¦bA¤u§@ªí¤¤´N­n¾ã²z
ÁÙ¬O¥i¥H¥H¤½¥Üªº¤è¦¡Âà´«¨ìBªí¦b¾ã²z©O?!

¥t¥~¤j¤jµ¹ªº¨â±ø¤½¥Ü §Ú¤S¸Ó©ñ¦b­þ¤@¦ìÀx¦s®æ¤¤©O?!

¹ê¦b¬O¤£¦n·N«ä¡C¡C¡C
Cyssuc

TOP

¦^´_ 4# p212

¤j¤j¦­¦w~~
¨C¤@¦ì°ª¤âªº¸ÑÃD ³£¤@©w·|¦³¦U¦Ûªº§Þ¥©¡C¡C¡C
­Y³£¥i¥H¿Ä·|³e³q ¬O§ÚªººÖ®ð
½Ð¨D¤j¤j
¦A¦¸±N¸ÑÃD§Þ¥©
¤£§[ªÀªº¤À¨É

Cyssuc ¦b¦¹ ¥ýÁ¹L©Ô
Cyssuc

TOP

¥»©«³Ì«á¥Ñ p212 ©ó 2014-7-28 09:30 ½s¿è

¦^´_ 8# Cyssuc
1¡B½Ð¥ý«ü©w¤u§@ªíB¤§Àx¦s®æB1¬°¡u«ü©w½s¸¹¡v¿é¤J®æ(¦p¿é¤JE0004©ÎA0010...)
2¡B©w¸q¦WºÙ
(1)¦]¤u§@ªíA¬°³æ¤@¤ë¤À¸ê®Æ(¦~¤ë©T©w¤éÅÜ°Ê)¡A¬G³]­p¨Ì·Ó¤u§@ªíB¤§Àx¦s®æB1¤º®e(½s¸¹E0004©ÎA0010...)¨ú¡u¤é¡vªº¹ïÀ³½d³ò¡A¬G©w¸q¦WºÙ¡u¤é¡v¡A¨ä°Ñ·Ó¨ì¿é¤J=OFFSET(A!$C$1,MATCH(B!$B$1,½s¸¹,0),,COUNTIF(½s¸¹,B!$B$1))
(2)©R¦W¡u½s¸¹¡v¡A°Ñ·Ó¨ì¿é¤J=OFFSET(A!$F$2,,,COUNTA(A!$F:$F)-1)
(3)©R¦W¡u¦~¡v¡A°Ñ·Ó¨ì¿é¤J=OFFSET(A!$A$2,,,COUNTA(A!$A:$A)-1)
(4)©R¦W¡u¤ë¡v¡A°Ñ·Ó¨ì¿é¤J=OFFSET(A!$B$2,,,COUNTA(A!$B:$B)-1)
(5)©R¦W¡u©m¦W¡v¡A°Ñ·Ó¨ì¿é¤J=OFFSET(A!$G$2,,,COUNTA(A!$G:$G)-1)
3¡B¤u§@ªíB¤§
(1)Àx¦s®æD3¿é¤J°}¦C¤½¦¡(¥HCtrl+Shift+Enter¿é¤J)
=IF(ISERROR(INDEX(¤é,MATCH(0,COUNTIF($D$2:D2,¤é),0))),"",INDEX(¤é,MATCH(0,COUNTIF($D$2:D2,¤é),0)))
¤W¦C¤½¦¡¥Øªº¦b©ó¨ú±o²Å¦X¤u§@ªíB¤§Àx¦s®æB1±ø¥ó¤§¡u¤é¡vªº°ß¤@­È
(2)Àx¦s®æB3¿é¤J¤@¯ë¤½¦¡=IF(D3="","",INDEX(¦~,MATCH($B$1,½s¸¹,0)))
(3)Àx¦s®æC3¿é¤J¤@¯ë¤½¦¡=IF(D3="","",INDEX(¤ë,MATCH($B$1,½s¸¹,0)))
(4)Àx¦s®æE3¿é¤J¤@¯ë¤½¦¡=IF(D3="","",INDEX(©m¦W,MATCH($B$1,½s¸¹,0)))
4¡B¦Ü©ó¤W¯ZAM¡B¤U¯ZAM¡B¤W¯ZPMµ¥Äæ¦ì¹ïÀ³¸ê®Æ¡A©ó¤u§@ªíA¥¼¦³©ú½T°Ï¤À(¹ï¦P¤@½s¸¹¡A¤£¦P¡u¤é¡v¨äµ§¼Æ¤£¾¨¬Û¦P)¡A¬G¥¼¸Ñ¡C
½Ð°Ñ¦Ò¡I

TOP

¦^´_ 7# Cyssuc

³o¬O¤@­Ó¤½¦¡¡A¤À¦¨¨â¬q¤ñ¸û®e©ö¤F¸Ñ¡A¤½¦¡©ñ¦b F3

F3 =IF(COLUMN(A1)/2>SUMPRODUCT(1*($B3&$C3&$D3&$E3=A!$A$2:$A$1999&A!$B$2:$B$1999&A!$C$2:$C$1999&A!$G$2:$G$1999)),"", OFFSET(A!$D$1,MATCH($B3&$C3&$D3&$E3,A!$A$2:$A$1999&A!$B$2:$B$1999&A!$C$2:$C$1999&A!$G$2:$G$1999,)+INT((COLUMN(A1)-1)/2),MOD(COLUMN(A1)-1,2)) )

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

TOP

        ÀR«ä¦Û¦b : ª¾ÃÑ­n¥Î¤ßÅé·|¡A¤~¯àÅܦ¨¦Û¤vªº´¼¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD