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

[µo°Ý] excel VBA ¨ç¼Æ-««ª½·j´M¬Û¹ïÀ³¦Cªº­È

[µo°Ý] excel VBA ¨ç¼Æ-««ª½·j´M¬Û¹ïÀ³¦Cªº­È

¦U¦ì«e½ú¤j®a¦n
¤p§Ì³Ìªñ¦b¨Ï¥ÎEXCEL VBA°µ¸ê®Æ¾ã²z

¤p§Ì§Æ±æ¦bÀx¦s®æ¦C¤¤««ª½·j´M¥X¬Û¹ïÀ³¦Cªº­È(¨Ò¦p:¦b«ü©w·j´M½d³ò¤º, ·j´M²Äm¦Cªº­È, ·í²Äm¦C¬Y¦æªº­Èµ¥©ó"±ý·j´Mªº­È"®É¡A«h¦^¶Ç²Än¦C¦P¦æªº­È)
¥Ø«e¦bºô¸ô¤W¥u§ä¨ìVLOOKUP©MLOOKUP³o¨â­Ó¨ç¼Æ¥i¥H¦³Ãþ¦üªº¥\¯à
¦ýVLOOKUP ¨ç¼Æ ­­¨î"±ý·j´Mªº­È"¥u¯à¦b«ü©w·j´M½d³òªº²Ä¤@¦C
¦ÓLOOKUP  ¨ç¼Æ "±ý·j´Mªº­È"¤S­­¨î¥u¯à¥H»¼¼Wªº¶¶§Ç±Æ¦C

·Q½Ð°Ý¦U¦ì«e½ú°£¤F³o¨â­Ó¨ç¼Æ¤§¥~
¦³¨S¦³¨ä¥L¦n¥ÎªºVBA¨ç¼Æ¥i¥H¦³Ãþ¦üªº¥\¯à
¦ý¤£·|¦³¥H¤W­­¨îªº

³Â·Ð¦U¦ì«e½ú«ü¾É¤F~~ÁÂÁÂ~~!

«D±`ÁÂÁÂstillfish00«e½ú¶W¯Å¸Ô²Óªº¸ÑÄÀ~³o¼Ë¤p§Ì´N§¹¥þ¤F¸Ñ~~!

¤]ÁÂÁÂGBKEEª©¥Dªº¹ªÀy, ¤p§Ì·|Ä~Äò¥[ªoªº~~!!

TOP

¦^´_ 10# junkwei
(B$1:B$16=H2)
B1~B16¨C­Ó¤¸¯À³£©MH2¤ñ¡A¬Û¦P¬°True¡B¤£¦P¬°FALSE
°²¦p¥u¦³B2¤ÎB3©MH2¬Û¦P¡A´Nµ¥©ó¬°¦^¶Ç :  
False,True,True,False,False... ³o¼Ëªº°}¦C

(C$1:C$16=I2)¤]¬O¦p¦¹¡A°²³]¨Ò¦p¦^¶Ç:
False,False,True,False,False...

(B$1:B$16=H2)*(C$1:C$16=I2)
¬Û·í©ó¤W­z¦^¶Çªº°}¦C­Ó¤¸¯À¬Û­¼¡A¬Û­¼®ÉTrue·|¦Û°ÊÂà´«¬°1¡BFalse¬°0
©Ò¥H¤]¬O§A»¡ªº"¥B"ªº°Ê§@¡A¥H¤W­±Á|ªº¨Ò¤l´N¬O¦^¶Ç:
0,0,1,0,0...

¦Ó¥Î1°£¥H(B$1:B$16=H2)*(C$1:C$16=I2)¡A¥H¤W­±Á|ªº¨Ò¤l´N¬O¦^¶Ç:
#DIV/0!,#DIV/0!,1,#DIV/0!,#DIV/0!...
#DIV/0!¬°°£¹s³y¦¨ªº¿ù»~

LOOKUP´N¦Û¤v¬d¬Ý»¡©ú¤F
¦pªG LOOKUP ¨ç¼ÆµLªk§ä¨ì lookup_value¡A«K·|±Ä¥Î¦b lookup_vector ¤¤µ¥©ó©Î¶È¦¸©ó lookup_value ªº³Ì¤j­È¡C

Á`¤§¬O­n¤ñ¹ïªºÄæ©ñ¤À¥À¡A¦hÄæ(2Äæ¡B3Äæ...³£¥i¥H)³£­n²Å¦X´N¥Î*¡A¨ú¦^µ²ªGªºÄæ´N©ñ²Ä¤T°Ñ¼Æ¡C
¤½¦¡¨ìEXCEL¤@¯ë°ÏÀ³¸Ó¤ñ¸û¦h°Q½×¡C
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 9# junkwei
µ{¦¡½X¬O¨Ì 5# ªº¹Ï¥Ü¼ÒÀÀªº,§A­×§ï¦¨¥\.¨ºVBA¥i«Ü§Ö¤W¤âªº.
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_  junkwei
¨Ì5¼Ó¹Ïªº°ÝÃD¡A³Ì²³æªº¤èªk¬O
¦bJ2¶ñ¤W =LOOKUP(2,1/((B$1:B$16=H2)*(C$1:C$16=I2)),A$ ...
stillfish00 µoªí©ó 2014-12-15 16:40


·PÁÂstillfish00«e½úªº¤èªk~
Åý§Ú¾Ç¨ì¤£¦P¥B¹ê¥Îªº¤èªk~ÁÂÁ±z~~!
¤£ª¾¹D«e½ú¬O§_¤è«K»¡©ú¤@¤U±zªº¤½¦¡¤¤lookup¨ç¼Æªº²Ä¤GÄæ
¥H«K¤p§Ì¤é«áÀ³¥Î

½Ð°Ý
1/(½d³ò=±ý·j´M­È)  ¬O¤@ºØ©T©wªº»yªk¶Ü?
¥H¤Î
·í¤¤ªº * ²Å¸¹  ¦b³o¸Ì¥Nªíªº·N¸q¬O"¥B"ªº·N«ä¶Ü?

¤p§Ì¦bºô¸ô¤W¦n¹³¤£®e©ö§ä¨ì¬ÛÃöªº»¡©ú...@@
¤£¦n·N«ä¦A³Â·Ð«e½ú«ü¾É¤F!
ÁÂÁÂ~~!

TOP

¦^´_  junkwei
¸Õ¸Õ¬Ý
GBKEE µoªí©ó 2014-12-15 08:12


ÁÂÁÂGBKEEª©¥DªºÀ°§U!!¤SÅý¤p§Ì¦A¦¸¨£ÃѤF¤@¦¸vbaªº«Â¤O!
¤£¹L±z­ì¥»ªºcode¦n¹³Äæ¦ì¦³¨Çµ§»~
¦]¦¹¤p§Ì¦³¤p¤p­×¥¿¤F¤@¤U, ¦p¤U©Ò¥Ü
¨Ñ¨ä¥L¦³»Ý­nªºª©¤Í°Ñ¦Ò~~
³Ì«á¦A¦¸·PÁ±zªºÀ°¦£~!
  1. Sub CAL2()
  2.     Dim Rng, Ar, R As Range, i As Integer, ii As Integer
  3.     Ar = Range("A1").CurrentRegion.Value    '¸ê®Æ®w
  4.     i = [MATCH(J1,A1:D1,0)]
  5.     '©Îª½±µµ¹ i=1  '¼Æ¾Ç¦¨ÁZ¦b¸ê®Æ®wªºÄæ¦ì
  6.     Set Rng = Range("H1", Range("H1").End(xlDown)).Resize(, 2)   
  7.    
  8.     For Each R In Rng.Rows  
  9.         For ii = 1 To UBound(Ar, 1)
  10.             If R.Cells(1, 1) & R.Cells(1, 2) = Ar(ii, 2) & Ar(ii, 3) Then
  11.                 R.Cells(1, 3) = Ar(ii, 1)
  12.                 Exit For   
  13.             End If
  14.         Next
  15.     Next
  16.    
  17. End Sub
½Æ»s¥N½X

TOP

¦^´_ 5# junkwei
¨Ì5¼Ó¹Ïªº°ÝÃD¡A³Ì²³æªº¤èªk¬O
¦bJ2¶ñ¤W =LOOKUP(2,1/((B$1:B$16=H2)*(C$1:C$16=I2)),A$1:A$16)
µM«á©¹¤U¶ñº¡¤½¦¡¡A
³o¤½¦¡ºâ¬O¤ñ¸û¶i¶¥¤SÁÙÆZ±`¹J¨ìªº¤@­Ó¤p§Þ¥©¡C

­Y­n¥ÎVBA´N°Ñ¦ÒGª©ªº§a~
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 5# junkwei
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Rng, Ar, R As Range, i As Integer, ii As Integer
  4.     Ar = Range("A1").CurrentRegion.Value    '¸ê®Æ®w
  5.     i = [MATCH(J1,A1:D1,0)]
  6.     '©Îª½±µµ¹ I=3  '¼Æ¾Ç¦¨ÁZ¦b¸ê®Æ®wªºÄæ¦ì
  7.     Set Rng = Range("H1", Range("H1").End(xlDown)).Resize(, 2)
  8.     For Each R In Rng.Rows
  9.         For ii = 1 To UBound(Ar, 1)
  10.             If R.Cells(1, 1) & R.Cells(1, 2) = Ar(ii, 1) & Ar(ii, 2) Then
  11.                 R.Cells(1, 3) = Ar(ii, 3)
  12.                 Exit For
  13.             End If
  14.         Next
  15.     Next
  16. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 3# stillfish00

stillfish00«e½ú±z¦n:
¤£¦n·N«ä¤p§Ìªº°ÝÃD±Ô­z¦ü¥G¯uªº¤£¤Ó²M·¡
¦]¬°§Ú­ì¥»ªº¥Øªº¬O¥´ºâ¥u°Ý¨º­Ó¨ç¼Æªº¦WºÙ
¦A¦Û¤v¥h§ä¨ç¼Æªº¬ÛÃö¸ê°T¥hÀ³¥Î
¤£®Æ¸ê½è¾q¶w....0rz
¦n¹³°ÝÃD±Ô­zªº¤£§¹¥þ,
«e½ú­Ì©Ò¼gªºµ{¦¡¤]¤£¨£±o¥i¥H§¹¥þÀ³¥Î
¤U¦¸¤p§Ìµo¤å®É·|¯S§Oª`·Nªº~!
¤]·PÁ«e½úªº¤£§[«ü±Ð~~!

TOP

¦^´_ 2# GBKEE

GBKEE ª©¥D±z¦n:
¤£¦n·N«ä¤p§Ìªì¾ÇVBA
±zªºcode ¤p§ÌÁÙ¬O¦³¨ÇÀ³¥Î¤Wªº§xÃø..
¦]¦¹¨Ìstillfish00«e½úªº«Øij
±N§Úªº°ÝÃD§¹¾ã§e²{
¦A³Â·Ðª©¥D¼·ªÅ«ü¾É¤F...
«D±`·PÁÂ...!!

««ª½·j´M.png (21.51 KB)

««ª½·j´M.png

TOP

        ÀR«ä¦Û¦b : ¤@¥y·Å·xªº¸Ü¡A´N¹³©¹§O¤H¨­¤WÅx­»¤ô¡A¦Û¤v·|ªg¨ì¨â¤Tºw¡C
ªð¦^¦Cªí ¤W¤@¥DÃD