WorksheetFunction.Match 方法 (Excel)WorksheetFunction.Match method (Excel)

傳回項目在陣列中的相對位置,其符合指定順序中的指定值。Returns the relative position of an item in an array that matches a specified value in a specified order. 當您需要而不是項目本身的範圍中之項目的位置,請使用相符項目而不是下列其中一個Lookup函式。Use Match instead of one of the Lookup functions when you need the position of an item in a range instead of the item itself.


運算式比對(Arg1Arg2Arg3)expression.Match (Arg1, Arg2, Arg3)

_運算式_代表**WorksheetFunction** 物件的變數。expression A variable that represents a WorksheetFunction object.


名稱Name 必要/選用Required/Optional 資料類型Data type 描述Description
Arg1Arg1 必要Required VariantVariant Lookup_value: 您使用表格中尋找您想要的值的值。Lookup_value: the value that you use to find the value that you want in a table.
Arg2Arg2 必要Required VariantVariant Lookup_array:包含可能查閱值的連續儲存格範圍。Lookup_array: a contiguous range of cells containing possible lookup values. Lookup_array 必須是陣列或陣列參照。Lookup_array must be an array or an array reference.
Arg3Arg3 選用Optional VariantVariant Match_type:數字 -1、0 或 1。Match_type: the number -1, 0, or 1. Match_type 會指定 Microsoft Excel 如何比對 lookup_value 與 lookup_array 中的值。Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

傳回值Return value



Lookup_value 是您要在 lookup_array 中尋找比對的值。Lookup_value is the value that you want to match in lookup_array. 比方說,當您查閱電話活頁簿中的數字時,使用該人員的名稱作為查閱值,但的電話號碼是您想要的值。For example, when you look up a number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value that you want.

Lookup_value 可以是值 (數字、文字、邏輯值),或是數字、文字、邏輯值的儲存格參照。Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

如果 match_type 是 1,相符項目會找到小於或等於 lookup_value 的最大值。If match_type is 1, Match finds the largest value that is less than or equal to lookup_value. Lookup_array 必須以遞增次序排列:...,-2,-1,0,1,2,...A-Z,FALSE,TRUE。Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

如果 match_type 是 0,相符項目會尋找第一個完全等於 lookup_value 的值。If match_type is 0, Match finds the first value that is exactly equal to lookup_value. Lookup_array 可以依任意次序排列。Lookup_array can be in any order. 請注意, Match不區分大小寫。Note that Match is case-insensitive.

如果 match_type 是-1,相符項目會找到大於或等於 lookup_value 的最小值。If match_type is -1, Match finds the smallest value that is greater than or equal to lookup_value. Lookup_array 必須以遞減順序排列:TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., 等。Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

如果省略 match_type,則會假設其值為 1。If match_type is omitted, it is assumed to be 1.

Match會傳回相符值在 lookup_array,而非值本身內的位置。Match returns the position of the matched value within lookup_array, not the value itself. 例如,MATCH("b",{"a","b","c"},0)會傳回 2,"b"陣列中的相對位置{"a","b","c"}For example, MATCH("b",{"a","b","c"},0) returns 2, the relative position of "b" within the array {"a","b","c"}.

比對不會區分字母大小寫比對文字值時。Match does not distinguish between uppercase and lowercase letters when matching text values.

如果符合中尋找相符項目不成功,則會傳回 # n/A 錯誤值。If Match is unsuccessful in finding a match, it returns the #N/A error value.

如果 match_type 是 0 而 lookup_value 是文字,您可以在 lookup_value 中使用萬用字元、問號 (?) 和星號 (*)。If match_type is 0 and lookup_value is text, you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. 問號可比對任何單一字元;星號可比對任何序列字元。A question mark matches any single character; an asterisk matches any sequence of characters. 如果想要尋找實際的問號及星號,請在字元前面輸入波狀符號 (~)。If you want to find an actual question mark or asterisk, type a tilde (~) before the character.


對於第一份工作表之第一個資料行中的每個值,此範例會搜尋整個活頁簿來找出相符的值。For each value in the first column of the first worksheet, this example searches through the entire workbook for a matching value. 如果巨集發現相符的值,它會將第一份工作表中的原始值設定為粗體。If the macro finds a matching value, it sets the original value on the first worksheet to be bold.

Sub HighlightMatches()
    Application.ScreenUpdating = False
    'Declare variables
    Dim var As Variant, iSheet As Integer, iRow As Long, iRowL As Long, bln As Boolean
       'Set up the count as the number of filled rows in the first column of Sheet1.
       iRowL = Cells(Rows.Count, 1).End(xlUp).Row
       'Cycle through all the cells in that column:
       For iRow = 1 To iRowL
          'For every cell that is not empty, search through the first column in each worksheet in the
          'workbook for a value that matches that cell value.

          If Not IsEmpty(Cells(iRow, 1)) Then
             For iSheet = ActiveSheet.Index + 1 To Worksheets.Count
                bln = False
                var = Application.Match(Cells(iRow, 1).Value, Worksheets(iSheet).Columns(1), 0)
                'If you find a matching value, indicate success by setting bln to true and exit the loop;
                'otherwise, continue searching until you reach the end of the workbook.
                If Not IsError(var) Then
                   bln = True
                   Exit For
                End If
             Next iSheet
          End If
          'If you do not find a matching value, do not bold the value in the original list;
          'if you do find a value, bold it.
          If bln = False Then
             Cells(iRow, 1).Font.Bold = False
             Cells(iRow, 1).Font.Bold = True
          End If
       Next iRow
    Application.ScreenUpdating = True
End Sub

支援和意見反應Support and feedback

有關於 Office VBA 或這份文件的問題或意見反應嗎?Have questions or feedback about Office VBA or this documentation? 如需取得支援服務並提供意見反應的相關指導,請參閱 Office VBA 支援與意見反應Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.