Jump to content

Documentation/Calc 関数/MATCH

From The Document Foundation Wiki
This page is a translated version of the page Documentation/Calc Functions/MATCH and the translation is 3% complete.


Function name:

MATCH

Category:

Spreadsheet

Summary:

Identifies the relative position of a search item in a single-row or single-column cell range, depending on its arguments.

The matching process can also utilize either wildcards or regular expressions. By default, the matching is not case-sensitive, but a case-sensitive match can be carried out when using a regular expression.

Syntax:

MATCH(Search criterion; Lookup array[; Type])

Returns:

Returns an integer ranging from 1 to the size of the specified Lookup array, giving the position of the searched-for item in the given range of cells.

Arguments:

Search criterion is either a text string (in quotation marks), a number, a Boolean, or a reference to a cell containing one of those types. Search criterion is matched against the contents of cells in Lookup array.

Lookup array is the single-row or single-column range of cells to be searched. It may also be specified using the name of a named range or a database range, or may be passed as an inline array.

Type is an integer, or a reference to a cell containing an integer, that indicates in which sorting order the data in Lookup array will be searched-for. The intended values for Type are as follows:

  • Type = 1 (default). Lookup array is assumed to be sorted in ascending order (see the #Additional details: section below for a description of the sorting order). MATCH returns the position of the largest value in Lookup array that is less than or equal to Search criterion.
  • Type = 0. Lookup array is not assumed to be sorted in any particular way. MATCH returns the position of the first exact match. Search criterion can optionally utilize either regular expressions or wildcards, if these are enabled.
  • Type = -1. Lookup array is assumed to be sorted in descending order. MATCH returns the position of the smallest value in Lookup array that is greater than or equal to Search criterion.

The following conditions may arise:

  • If Lookup array is a multi-dimensional cell range, then MATCH reports a parameter list error (エラー:504).
  • If Type is non-numeric, then MATCH reports a #VALUE! error.
  • If Type is any positive number, then MATCH assumes that Lookup array is sorted in ascending order.
  • If Type is any negative number, then MATCH assumes that Lookup array is sorted in descending order.
  • If Type is non-zero, but the cells in Lookup array are not ordered as expected, then the result returned by MATCH is unpredictable and unreliable. In such cases, it is possible that no error will be reported and it may not be obvious that the returned result is meaningless.
  • If Type is non-zero and Search criterion utilizes either a regular expression or a wildcard expression, MATCH does not routinely report an error. However, it may not be obvious when the returned result is meaningless.
  • If Type is zero and no exact match to Search criterion can be found in Lookup array, then MATCH reports the #N/A error.
  • If Type is non-zero and Search criterion precedes the first cell in Lookup array for the specified sorting order, then MATCH reports the #N/A error.

Additional details:

Details specific to MATCH function

  • Boolean values FALSE and TRUE are treated as numbers 0 and 1 respectively.
  • If Type is zero and Lookup array contains multiple cells with the same content, MATCH will only match one of the cells sharing common content. This could be a cause of confusion.
  • If Type is greater than zero, Lookup array should be sorted with numbers in ascending order appearing before text values in alphabetical order. MATCH calculates where in the Lookup array the value of Search criterion would appear in the sorted order.
  • If Type is less than zero, Lookup array should be sorted with text values in descending alphabetical order appearing before numbers in descending order. MATCH calculates where in the Lookup array the value of Search criterion would appear in the sorted order.
  • If regular expressions are enabled in the Formulas Wildcards area of the Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate (or LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate on macOS) dialog, then MATCH will find exact matches treating Search criterion as a regular expression.
  • If wildcards are enabled in the Formulas Wildcards area of the Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate (or LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate on macOS) dialog, then MATCH will find exact matches treating Search criterion as a wildcard expression.
  • If the "Search criteria = and <> must apply to whole cells" option is ticked in the General Calculations area of the Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate (or LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate on macOS) dialog, then Search criterion must match the whole text in a cell. Otherwise, it can match just part of the text.
  • The Case sensitive option in the General Calculations area of the Tools ▸ Options ▸ LibreOffice Calc ▸ Calculate (or LibreOffice ▸ Preferences ▸ LibreOffice Calc ▸ Calculate on macOS) dialog, has no effect on the operation of MATCH.


Calc の正規表現に関する一般事項

Note:
便宜上、このサブセクションの情報は、正規表現を操作する関数を説明するすべてのページで表示されます。

  • 正規表現とは、一致させるテキストのパターンを定義する文字列です。より詳細で一般的な背景情報は、Regular expression ページにあります。
  • 正規表現は多くのドメインで広く使用されており、様々な正規表現プロセッサが利用可能です。Calc では、International Components for Unicode (ICU) のオープンソース正規表現パッケージ を採用しています。ICU 正規表現の構文の完全な定義を含む詳細については、Regular Expressions のドキュメントを参照してください。
  • さらに、LibreOffice のヘルプシステムは、高水準の 正規表現リスト を提供します。
  • Calc の正規表現エンジンは、番号付きキャプチャ グループ をサポートしています。これにより、一致するサブ範囲を識別し、置換テキスト内で使用できます。括弧は、正規表現のコンポーネントをグループ化し、番号付きキャプチャ・グループを作成するために使用されます。キャプチャ グループを置換テキストに挿入するには、"$n" 形式を使用します。ここで、"n" はキャプチャ グループの番号です。

Calc のワイルドカードに関する一般事項

ワイルドカードは、一部の Calc 関数に引数として渡される検索文字列で使用できる特殊文字です。また、検索と置換 ダイアログで検索条件を定義するためにも使用できます。ワイルドカードを使用すると、単一の検索文字列でより高度な検索パラメータを定義できます。

Calc は、現在のアプリケーションの設定に応じて、引数としてワイルドカードまたは正規表現をサポートします。既定では、正規表現ではなくワイルドカードがサポートされます。

ワイルドカードがサポートされていることを確認するには、 ツール ▸ オプション ▸ LibreOffice Calc ▸ 計算式 に移動し、オプション 数式でワイルドカードを使用する が選択されているかどうかを確認します。このダイアログを使用して正規表現に切り替えるには、数式で正規表現を使用する を選択するか、ワイルドカードも正規表現もサポートしないように選択します。

次の表に、Calc でサポートされているワイルドカードを示します。

Calc wildcards
ワイルドカード 説明
?
(クエスチョン マーク)
任意の 1 文字に一致します。たとえば、検索文字列 "b?g" は "bag" および "beg" に一致しますが、"boog" または "mug" には一致しません。

"?"正確に 1 文字に一致する必要があるため、"bg" にも一致しないことに注意してください。"?" ワイルドカードは、0 文字の一致には対応しません。
*
(アスタリスク)
空の文字列を含む任意の文字列と一致します。たとえば、検索文字列 "*cast" は、"cast"、"forecast" および“outcast”と一致しますが、デフォルトの Calc 設定を使用する場合 "forecaster" とは一致しません。

ツール ▸ オプション ▸ LibreOffice Calc ▸ 計算式 でオプション 検索条件 = と <> はセル全体に適用 が無効になっている場合、"forecaster" は "*cast" の検索文字列を使用し一致させることができます。
~
(チルダ)
チルダ文字の直後に続く疑問符、アスタリスク、または チルダ文字の特別な意味をエスケープします。

例えば、検索文字列 "why~?""why?" に一致しますが、 "whys""why~s" には一致しません。

ワイルドカードの比較では大文字と小文字が区別されないため、 "A?" は "A1" と "a1" の両方に一致します。

これらのワイルドカードは、Calc と Microsoft Excel の両方でサポートされています。したがって、2 つのアプリケーション間の相互運用性が必要な場合は、正規表現ではなくワイルドカードを使用することを選択します。逆に、相互運用性が必要でない場合は、より強力な検索機能を利用できる正規表現の使用を検討してください。

Examples:

In the first set of examples, it is assumed that cells A1:M1 contain the following numbers and strings:
7, "King", 3, 2, 8, 5, "Ace", 6, "Jack", 10, 4, 9, "Queen" (representing the 13 cards in one suit of a pack of playing cards, shuffled into a random order). The strings are entered into their cells without quotation marks.

Formula Description Returns

=MATCH(2; lookup; 0) where lookup is a named range (or database range) covering the cell range A1:M1.

Search exactly for the value 2 in the specified cell range, located in the fourth position. 4
=MATCH(3.6; A1:M1; 0) There is no exact match for the value 3.6. #N/A

=MATCH(A3; A1:M1; A4) where cells A3 and A4 contain the string "Jack" (without quotation marks) and number 0 respectively.

Search exactly for "Jack" (without quotation marks, not case-sensitive) in the specified cell range, located in the ninth position.

9

=MATCH(A3; A1:M1; A4) where cells A3 and A4 contain the string "jack" (without quotation marks) and number 0 respectively.

Search exactly for "jack" (without quotation marks, not case-sensitive) in the specified cell range.

9

=MATCH("K*"; A1:M1; 0) with wildcards enabled.

Search for a value matching exactly the expression in the specified cell range, finding the string "King" in the second entry.

2

=MATCH("^Q.*"; A1:M1; 0) with regular expressions enabled.

Search for a value matching exactly the regular expression "^Q.*" in the specified cell range, finding the string "Queen" in the thirteenth entry.

13

=MATCH("(?-i)^[Q]{1}[a-z]*$"; A1:M1; 0) with regular expressions enabled.

Search for a value matching exactly the regular expression "(?-i)^[Q]{1}[a-z]*$" in the specified cell range, requiring case-sensitivity in this case, finding the string "Queen" in the thirteenth entry.

13

=MATCH("(?-i)^[q]{1}[a-z]*$"; A1:M1; 0) with regular expressions enabled.

Search for a value matching exactly the regular expression "(?-i)^[q]{1}[a-z]*$" in the specified cell range, requiring case-sensitivity in this case, not finding a match.

#N/A

In the second set of examples, it is assumed that cells A1:A13 contain the following numbers and strings:
2, 3, 4, 5, 6, 7, 8, 9, 10, "Ace", "Jack", "King", "Queen" (representing the 13 cards in one suit of a pack of playing cards, placed in the ascending order required by the MATCH function in the following examples). The strings are entered into their cells without quotation marks. Compare the results with the third set of examples.

A
1 2
2 3
3 4
4 5
5 6
6 7
7 8
8 9
9 10
10 Ace
11 Jack
12 King
13 Queen
Formula Description Returns
=MATCH(1; A1:A13)

As the Type argument is omitted, the default value of 1 is assumed. The Search criterion argument is less than the initial value in the list.

#N/A
=MATCH(2.9; A1:A13; 1)

Search for the position corresponding to the largest value in Lookup array that is less than or equal to 2.9.

1
=MATCH(23.5; A1:A13; 1)

The position corresponding to the largest value in Lookup array that is less than or equal to 23.5.

9
=MATCH("Aardvark"; A1:A13; 1) The Search criterion argument is alphabetically before the first string in the list. #N/A
=MATCH("King"; A1:A13; 1) Search for the position of "King" in Lookup array. 12
=MATCH("Zulu"; A1:A13; 1)

"Zulu" is not found in Lookup array. Search for the position of the value in Lookup array that resides immediately before where "Zulu" would appear.

13

In the third set of examples, it is assumed that cells A1:A13 contain the following numbers and strings:
"Queen", "King", "Jack", "Ace", 10, 9, 8, 7, 6, 5, 4, 3, 2 (representing the 13 cards in one suit of a pack of playing cards, placed in the descending order required by the MATCH function in the following examples). The strings are entered into their cells without quotation marks. Compare the results with the previous set of examples.

A
1 Queen
2 King
3 Jack
4 Ace
5 10
6 9
7 8
8 7
9 6
10 5
11 4
12 3
13 2
Formula Description Returns
=MATCH("Zulu"; A1:A13; -1) The Search criterion argument resides before the initial value in the list. #N/A
=MATCH("King"; A1:A13; -1) Search for the position of "King" in Lookup array. 2
=MATCH("Aardvark"; A1:A13; -1)

"Aardvark" is not found in Lookup array. Search for the position of the value in Lookup array that resides immediately before where "Aardvark" would appear.

4
=MATCH(23.5; A1:A13; -1) The Search criterion argument is greater than the initial value in the list. #N/A
=MATCH(2.9; A1:A13; -1) The position of the smallest value in Lookup array that is greater than or equal to 2.9. 12
=MATCH(1; A1:A13; -1) The position of the smallest value in Lookup array that is greater than or equal to 1 13

Related LibreOffice functions:

HLOOKUP

INDEX

LOOKUP

OFFSET

VLOOKUP

ODF standard:

Section 6.14.9, part 2

Related (or similar) Excel functions:

MATCH