1. Home »
2. エクセル関数一覧表 »
3. Excel関数の目次 »
4. XMATCH関数の使い方

XMATCH関数の使い方:Excel関数

更新:2024年12月11日;作成:2020年2月20日

XMATCH関数が利用できるExcelの種類(バージョンについて)

• XMATCH関数はMATCH関数系の新しい関数で、XLOOKUP関数と同じように新しく追加された関数です。
  スピルに関連した関数となっています。
  Excel for Microsoft 365 (サブスクリプション版) 、Excel2021,Excel2024(永続ライセンス版)でXMATCH関数が使えるようになりました。
  ちなみに、Excel2016,Excel2019(永続ライセンス版)では使用できませんので、ご注意ください。
  
  Spill(スピル) XMATCH関数
  Excel2016,Excel2019 ×ばつ ×ばつ
  Excel2021,Excel2024 ○しろまる ○しろまる
  Excel for Microsoft365 ○しろまる ○しろまる

配列またはセル範囲内の項目の相対的な位置を返す topへ
エックス マッチ
=XMATCH(検索値,検索範囲,[一致モード],[検索モード])

XMATCH関数の引数 topへ

• 引数の [ ] はオプションとなり、指定しなくてもOKです。
  XMATCH関数は完全一致で検索する設定が既定になっています。これは従来のMATCH関数と異なるところです。
  
  検索値 参照する値、セル 。
  検索範囲 検索する配列または範囲
  [一致モード] 一致の種類を指定します
  0 完全一致。(これが既定の設定です。) 見つからない場合は、#N/A が返されます。
  -1 完全一致。 見つからない場合は、次の小さなアイテムが返されます。
  1 完全一致。 見つからない場合は、次の大きなアイテムが返されます。
  2 *、?、および 〜 が特別な意味を持つワイルドカードとして使用できます。
  [検索モード] 使用する検索モードを指定します。
  1 先頭の項目から検索を実行します。 これが既定の設定です。
  -1 末尾の項目から逆方向に検索を実行します。
  2 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。
  昇順に並べ替えられていない場合、無効な結果が返されます。
  -2 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。
  降順に並べ替えられていない場合、無効な結果が返されます。

INDEX関数とXMATCH関数で検索する topへ

• ここで使用するデータをここに残しておきます。検算等にお使いください。
  
  B C D E F G H I
  2 都道府県 人口 福岡県 ↓=SORT(B3:C49,2,-1)
  3 北海道 5,381,733 9 1 東京都 13,515,271
  4 青森県 1,308,265 2 神奈川県 9,126,214
  5 岩手県 1,279,594 3 大阪府 8,839,469
  6 宮城県 2,333,899 4 愛知県 7,483,128
  7 秋田県 1,023,119 5 埼玉県 7,266,534
  8 山形県 1,123,891 6 千葉県 6,222,666
  9 福島県 1,914,039 7 兵庫県 5,534,800
  10 茨城県 2,916,976 8 北海道 5,381,733
  11 栃木県 1,974,255 9 福岡県 5,151,560
  12 群馬県 1,973,115 10 静岡県 3,700,305
  13 埼玉県 7,266,534 11 茨城県 2,916,976
  14 千葉県 6,222,666 12 広島県 2,843,990
  15 東京都 13,515,271 13 京都府 2,610,353
  16 神奈川県 9,126,214 14 宮城県 2,333,899
  17 新潟県 2,304,264 15 新潟県 2,304,264
  18 山梨県 834,930 16 長野県 2,098,804
  19 長野県 2,098,804 17 岐阜県 2,031,903
  20 岐阜県 2,031,903 18 栃木県 1,974,255
  21 静岡県 3,700,305 19 群馬県 1,973,115
  22 愛知県 7,483,128 20 岡山県 1,921,525
  23 三重県 1,815,865 21 福島県 1,914,039
  24 富山県 1,066,328 22 三重県 1,815,865
  25 石川県 1,154,008 23 熊本県 1,786,170
  26 福井県 786,740 24 鹿児島県 1,648,177
  27 滋賀県 1,412,916 25 沖縄県 1,433,566
  28 京都府 2,610,353 26 滋賀県 1,412,916
  29 大阪府 8,839,469 27 山口県 1,404,729
  30 兵庫県 5,534,800 28 愛媛県 1,385,262
  31 奈良県 1,364,316 29 長崎県 1,377,187
  32 和歌山県 963,579 30 奈良県 1,364,316
  33 鳥取県 573,441 31 青森県 1,308,265
  34 島根県 694,352 32 岩手県 1,279,594
  35 岡山県 1,921,525 33 大分県 1,166,338
  36 広島県 2,843,990 34 石川県 1,154,008
  37 山口県 1,404,729 35 山形県 1,123,891
  38 徳島県 755,733 36 宮崎県 1,104,069
  39 香川県 976,263 37 富山県 1,066,328
  40 愛媛県 1,385,262 38 秋田県 1,023,119
  41 高知県 728,276 39 香川県 976,263
  42 福岡県 5,151,560 40 和歌山県 963,579
  43 佐賀県 832,832 41 山梨県 834,930
  44 長崎県 1,377,187 42 佐賀県 832,832
  45 熊本県 1,786,170 43 福井県 786,740
  46 大分県 1,166,338 44 徳島県 755,733
  47 宮崎県 1,104,069 45 高知県 728,276
  48 鹿児島県 1,648,177 46 島根県 694,352
  49 沖縄県 1,433,566 47 鳥取県 573,441
• 入力した県の人口を検索します。
  E2セルに県名を入力し、E3セルにその県の人口を検索します。
  E3セルの数式は =INDEX(C3:C49,XMATCH(E2,B3:B49)) としました。
  XMATCH関数は一致モードを省略すると、既定で完全一致で検索されます。
  
• XLOOKUP関数で求めることもできます。
  E3セルには =XLOOKUP(E2,B3:B49,C3:C49) とXLOOKUP関数でも求めることができました。
  XLOOKUP関数に関しては XLOOKUP関数の使い方 をご覧ください。
  

何番目に大きいデータかをXMATCH関数で調べる

1. 県別の人口がリスト化されています。
   E2セルで都道府県名を選択すると、E3セルに何番目に人口が多いかを表示します。
   
2. データリストをテーブルに変換しています。テーブル名は 県別人口 としました。
   [テーブルデザイン]タブのテーブル名に「県別人口」と入力します。
   
3. E3セルに
   =XMATCH(XLOOKUP(E2,県別人口[都道府県],県別人口[人口]),SORT(県別人口[人口],,-1))
   と入力しました。
   • XLOOKUP(E2,県別人口[都道府県],県別人口[人口])
     E2セルに入力される都道府県名の人口をテーブルから取り出しています。
   • SORT(県別人口[人口],,-1)
     県別人口を降順に並べ替えた配列を作成しています。
   • XMATCH関数でその人口が SORT関数で降順に並べ替えた人口とどの位置で合致するかを調べています。
4. 福岡県は9番目となりました。
   
5. 後日・・・何番目?ということはRANK関数・・・ということで、XMATCH関数無しで解けてしまいました。
   =RANK(XLOOKUP(E2,県別人口[都道府県],県別人口[人口]),県別人口[人口],0)

XMATCH関数でワイルドカードを使う topへ

• 上の都道府県名の人口をデータとして使います。
• ワイルドカード文字には *(アスタリスク)や ?(クエスチョンマーク)があります。
  ○しろまる* 「○しろまるで始まる」
  *○しろまる 「○しろまるで終わる」
  *○しろまる* 「○しろまるを含む」
  
  ?島 「2文字の文字列で2文字目が島」
  ?島? 「3文字の文字列で2文字目が島」
  といった指定の仕方ができます。
• 「鹿」から始まる県を探します。
  XMATCH("鹿*",B3:B49,2) と引数の一致モードに「2」を指定して、ワイルドカード文字(*,?,~)が使えるようにします。
  検索条件は 「鹿」と 「*」を組み合わせて、"鹿*" とします。
  都道府県名を返すために、INDEX関数と組み合わせます。
  E3セルに =INDEX(B3:B49,XMATCH("鹿*",B3:B49,2)) と入力しました。
  
• 「島」を含むという条件の場合は
  =INDEX(B3:B49,XMATCH("*島*",B3:B49,2))
  とします。
  なお、頭から検索して最初に該当したものが表示されます。
  
• 「府」で終わるという条件の場合は
  =INDEX(B3:B49,XMATCH("*府",B3:B49,2)) とします。
  
• 3文字目が「島」という条件の場合は
  =INDEX(B3:B49,XMATCH("??島*",B3:B49,2)) としました。
  "??島" では3文字の文字列で3文字目が「島」という条件になるので、4文字目以降は何でも用という条件 * を付け加えて、"??島*" としています。
  

スポンサードリンク
(追記) (追記ここまで)

PageViewCounter
Counter
Since2006/2/27