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

match ·j´M¤£¦P¤u§@ªí¥i¥H«ü©wÅܼƶÜ?

MATCH°ª¤â½Ð¶i

°}¦C ªíA ~ ªíF
¼Æ­È ªíG ~ ªíZ

VBA¨Ï¥ÎMATCH

­n«ç»òÅý"¼Æ­È"¥h¤ñ¹ï"°}¦C"...

TOP

=Application.match(lookup_value,­nmatchªº°}¦C,match_type))

Lookup_value: ¥i¥H¬O¼Æ­È©Î¤å¦r
Match_type ¬°¥i¿ï¼Æ -1¡A0¡A©Î1¡C

Match_type°Ñ¼Æ«ü©w±NExcel¤Ç°tlookup_valueªº»P­È¬d§äªº°}¦C¡C¦¹°Ñ¼ÆªºÀq»{­È¬O1¡C¤U­±´y­zMatch_type¥Îªk¡C

1©Î¬Ù²¤ match»{¬°¬O¤p©ó©Îµ¥©ó³Ì¤j­Èlookup_valueªº¡C¦b­È¬d§äªº°}¦C°Ñ¼Æ¥²¶·³Q©ñ¸m¦b¤W¤É¶¶§Ç¡A¨Ò¦p¡G...- 2¡A-1¡A0¡A1¡A2¡A...¡AAZ¡AFALSE¡ATRUE¡C
0 Match§ä¨ì²Ä¤@­Ó­È¡A¸Ó­È¥¿¦nµ¥©ólookup_valueªº¡C¦b¤¤­È¬d§äªº°}¦C°Ñ¼Æ¥i¥H¬O¥ô·N¶¶§Ç¡C
-1 Matchµo²{ªº³Ì¤p­È¤j©ó©Îµ¥©ólookup_valueªº¡C¦b­È¬d§äªº°}¦C°Ñ¼Æ¥²¶·³Q©ñ¸m¦b»¼´î¶¶§Ç¡A¨Ò¦p¡GTRUE¡AFALSE ZA¡A... 2¡A1¡A0¡A-1¡A-2¡A...¡Aµ¥µ¥¡C

TOP

¦^´_ 2# ikboy

ÁöµM¤£¬O§Ú·Q­nªºµª®×,¦ý«ÜÁÂÁ§Aªº¦^ÂÐ...

TOP

¦^´_ 3# EGBT
¬O³o¼Ë¶Ü?
  1. Option Explicit
  2. Sub EX()
  3.     Dim ¼Æ­È(), °}¦C(), e As Variant, M As Variant
  4.     ¼Æ­È = Array(0, "a" , 100 , "bb", 88)
  5.     °}¦C = Array(88, "cc", 0, "a", "bb")
  6.     For Each e In ¼Æ­È
  7.         M = Application.Match(e, °}¦C, 0)
  8.         Debug.Print e, M '«ü¥O:À˵ø->§Y®É¹Bºâµøµ¡
  9.     Next
  10. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 4# GBKEE


   §Ú¥i¥H½Ð°Ý¤@¤U¼Æ­ÈªºArray¸ò°}¦CªºArray¬O¤°»ò..?

TOP

¦^´_ 4# GBKEE


    §ÚÀ³¸Ó°Ýªº¤£°÷¦n...

À³¸Ó»¡MATCHªº¼Æ­È¸ò°}¦C

¼Æ­È»P°}¦C¤À§O¦b¤£¦PªºSheet

¦Ó¼Æ­È¸ò°}¦C³£¦³«Ü¦h­ÓSheet

MATCH¼g¤£¦n...

TOP

¦^´_ 5# EGBT


    ¦pªþÀÉ

3­Ó¼Æ­ÈªºSheet

3­Ó°}¦CªºSheet

µM«á¨C­Ó¼Æ­ÈªºSheet³£­n¶]°}¦CªºSheet

11111.zip (7.14 KB)

ÀÉ®×

TOP

Sub zz()
Dim no_ar, arr, sh_ar
sh_ar = [{"¼Æ­È1","°}¦C1";¼Æ­È2","°}¦C2";¼Æ­È3","°}¦C3"}]
For i = 1 To UBound(sh_ar)
    With Sheets(sh_ar(i, 1))
        Set no_ar = .Range("a1:b" & .Cells(Rows.Count, 1).End(3).Row)
            arr = Sheets(sh_ar(i, 2)).[a1].CurrentRegion
            For j = 1 To no_ar.Rows.Count
                no_ar(j, 2) = Application.Match(no_ar(j, 1), arr, 0)
                    Next: End With: Next
Set no_ar = Nothing
End Sub

TOP

¦^´_ 8# ikboy


    «Ü·PÁ§Aªº¦^ÂÐ~³o¼Ë§Ú¦³§ó¦hªº°ÝÃD·Q­n½Ð±Ð§A¤F..

TOP

¦^´_ 8# ikboy


    sh_ar = [{"¼Æ­È1","°}¦C1";¼Æ­È2","°}¦C2";¼Æ­È3","°}¦C3"}]

§Ú·Q½Ð°Ý¤@¤U [{}] ³o¬O¤°»ò?

TOP

        ÀR«ä¦Û¦b : ¥@¤W¦³¨â¥ó¨Æ¤£¯àµ¥¡G¤@¡B§µ¶¶ ¤G¡B¦æµ½¡C
ªð¦^¦Cªí ¤W¤@¥DÃD