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

[µo°Ý] [µo°Ý]EXCEL ¸ê®Æ¬d´M¦^¶Ç°ÝÃD

[µo°Ý] [µo°Ý]EXCEL ¸ê®Æ¬d´M¦^¶Ç°ÝÃD

¹Ï¤@

¹Ï¤G

Q:AÄ椤¯»¬õ¦r¬O­«½Æªº°Ó«~,¦ý¨C­Ó°Ó«~¦³¤£¦Pªº/BÄæ:¥Í²£¤é´Á©M/CÄæ:¦³®Ä¤é´Á
¦p¦ó¤À§O¶×¦¨¹Ï¤GÄæ¦ì¤¤,EX:¹Ï¤@ªº ¦P¤@½s¸¹AÄæ:7605,¤À§OÅã¥Ü¦b¹Ï¤G:/AGÄæÅã¥Ü¹Ï¤@ªºB2,AHÄæÅã¥Ü¹Ï¤@ªºC2/AIÄæÅã¥Ü¹Ï¤@ªºB3,AJÅã¥Ü¹Ï¤@ªºC3/AKÄæÅã¥Ü¹Ï¤@ªºB4,ALÄæÅã¥Ü¹Ï¤@ªºC4?

PS¹Ï¤@©M¹Ï¤G«e«áÄæ³£¦³¨ä¥L¸ê®Æ,©È¾Þ§@¤H­û¤£²ßºD,§Æ±æ¤£·s¼WÄæ¦ìªº¤è¦¡³B²z.   VLOOKUPªº¤è¦¡¦^¶Ç­È¬O¤é´Á¤@ª½µLªk¸Ñ¶},¦A³Â·Ð¤j¤j¯àÀ°§Ú¸£¤O¿EÀú¤@¤U,«D±`·PÁÂ

¦^´_ 1# hanachau


1. AG2 (¥Í²£¤é´Á2), ¿é¤J °}¦C¤½¦¡ (¤@»ô«ö Ctrl + Alt + Enter 3Áä ), ¦V¤U½Æ»s¦ÜAG3 :

=IFERROR(INDEX($B$2:$B$7,SMALL(IF($A$2:$A$7=$AF2,ROW($1:$6)),INT((COLUMN(A:A)-1)/2)+1)),"")

2. AH2 (¦³®Ä¤é´Á2), ¿é¤J °}¦C¤½¦¡ (¤@»ô«ö Ctrl + Alt + Enter 3Áä ), ¦V¤U½Æ»s¦ÜAH3 :

=IFERROR(INDEX($C$2:$C$7,SMALL(IF($A$2:$A$7=$AF2,ROW($1:$6)),INT((COLUMN(A:A)-1)/2)+1)),"")

3. ¿ï AG2:AH3 ¦V¥k½Æ»s¦Ü AI2:AJ3 ¦A¦¸ ¦V¥k½Æ»s¦Ü AK2:AL3

TOP

¦^´_ 2# JBY
·PÁ¤j¤jªº¦^ÂÐ,¦ý®z®zªº§Ú,ÁÙ¬O¦³¥H¤Uªº°ÝÃD,¦A³Â·Ð¤j¤j±Ð¾Ç¤@¤U:
1.¬°¤F©ñ«K»¡©ú°ÝÃD,©Ò¥H­ì©lÀɧڽs¿è¹L,¹Ï¤@©M¹Ï¤G,¬O¤G­Ó¤£¦Pªº¤å¥ó,¤ÀÄݤ£¦P³¡ªù,¸ê®Æ¤£¯à¦X¨Ö,¦ý¤j¤jªº¤½¦¡«Ü±j,ÂI¿ï没°ÝÃD,ªþ¥ó¹Ï¤@­ì©lÀÉÀY
2.   INT((COLUMN(A:A)-1)/2)+1)  ³o¤@¬q,§Ú...¾\Ū¤£¯à...,§Ú¸Õ°µ¤F¤@¤U,¬O§ÚÄæ¦ì­pºâªº°ÝÃD¶Ü?¹Ï¤@ªº½s¸¹¦Ü¥Í²£¤é´Á¤¤¶¡¦³8橺,©Ò¥H¸Ó¦p¦ó­×§ï©O?
3.§Ú¦³¸Õ©M¤j¤jÂI¦¨¦P¤@¤u§@ªí®æ¦¡¤À¤½¦¡,¦ý¦^¶Çªº­È¤é´Á¬O¼Æ¦r®æ¦¡,³o³¡¥÷¸Ó¦p¦ó­×¥¿?

TOP

¦^´_ 3# hanachau

1. ªþ¥ó¹ÏÄæ¦ì©M¹Ï¤@&¹Ï¤GÄæ¦ì¤£¤@¼Ë, ¬O¤@­ÓÁÙ¬O¤G­Ó¤£¦Pªº¤å¥ó ? ªþ¹Ï³¯­z²M·¡Äæ¦ì.

2. ³o¬O¼Ó¥Dªº¹jÄæ­n¨D, AG2 ¿é¤J =INT((COLUMN(A:A)-1)/2)+1 ¤½¦¡«á, ¥k½Æ»s¦Ü AI2 ¥H¤Î AK2 À³¸Ó¦^¶Ç 1,2 ©M 3.

3. ¦^¶Çªº­È¤é´Á¬O¼Æ¦r®æ¦¡, §â B Äæ¤é´Á®æ¦¡½Æ»s¹L¥h.

TOP

¦^´_ 4# JBY


ÁÂÁ¤j¤jªº¦^ÂÐ,³o´X¤Ñºô¸ô¤£Ã­,¦^ÂÐ¥[¹Ï¤ù³£¥¢±Ñ,²{¦b¤~¦^ÂЦ¨¥\,¦A³Â·Ð¤j¤jÀ°§Ú¬Ý¬Ý¸Ó¦p¦ó³B²z,ÁÂÁÂ
§ÚÁÙ¬O¸Ñ¤£¶},ºâÄæ¦ìªº¤èªk=INT((COLUMN(A:A)-1)/2)+1
¦A³Â·Ð¤j¤j»¡©ú¤F,ÁÂÁÂ

TOP

¦^´_ 5# hanachau
¥Ø«e
AG:AH½Æ»sAI:AJ,¤]¤£¦æ,¦^¶Çªº¸ê®Æ¬O¤@¼Ë?
AG2:AH3 ¦V¥k½Æ»s¦Ü AI2:AJ3 ¦A¦¸ ¦V¥k½Æ»s¦Ü AK2:AL3 , ³o¤]¤£¦æ,«öCTRL+SHIFT+SNTER 没¦³¤ÏÀ³,¬O§_¦]¬°AG3:AH3Àx¦s®æ¥X²{Åå¹Ä¸¹,¤½¦¡¿ù»~,©Ò¥HµLªk½Æ»s©O?

TOP

¦^´_ 4# JBY

¤j¤j,§Ú¸Õ¤F¦n¤[,譞¬O¸Ñ¤£¶},¥i¥H³Â·Ð§A,¦A±Ð±Ðªº¶Ü?·PÁ±z
AG:AH½Æ»sAI:AJ,¤]¤£¦æ,¦^¶Çªº¸ê®Æ¬O¤@¼Ë?
AG2:AH3 ¦V¥k½Æ»s¦Ü AI2:AJ3 ¦A¦¸ ¦V¥k½Æ»s¦Ü AK2:AL3 , ³o¤]¤£¦æ,«öCTRL+SHIFT+SNTER 没¦³¤ÏÀ³,¬O§_¦]¬°AG3:AH3Àx¦s®æ¥X²{Åå¹Ä¸¹,¤½¦¡¿ù»~,©Ò¥HµLªk½Æ»s©O?

TOP

http://blog.xuite.net/hcm19522/twblog/353167879

TOP

        ÀR«ä¦Û¦b : ·O´d¨S¦³¼Ä¤H¡A´¼¼z¤£°_·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD