[[20260319124345]]　『MATCHの計算結果について』（初心者）　ページの最後に飛ぶ

『MATCHの計算結果について』（初心者）

エクセルのシートのセルの以下の範囲に 1 を入力します。

 A01:BH1
 AE2:AN2,AY2:BH2
  U3:AD3,AY3:BH3
  K4: T4,AY4:BH4
  A5: J5,AY5:BH5

次に、それぞれのセルに計算式をいれます。

 CD1=MATCH(1,A1:CB1,1)
 CD2=MATCH(1,A2:CB2,1)
 CD3=MATCH(1,A3:CB3,1)
 CD4=MATCH(1,A4:CB4,1)
 CD5=MATCH(1,A5:CB5,1)

このとき、CD2 の値だけ40になります。
これはこういう仕様なのでしょうか？
それともバグなのでしょうか？
仕様だとすれば、なぜこのようになるのでしょうか？

＜使用 Excel：Microsoft365、使用 OS：Windows11 ＞

 バグでありません。仕様です。
 MATCH関数で照合の型に1を指定する場合、検索範囲は昇順で配置しなければなりません
 検索範囲が昇順になっていない場合、結果は保証されません。 二分探索するので。

 二分探索については調べてみてください
（´・ω・`） 2026/03/19(木) 13:25:36

1が表示されている左端の位置と右端の位置を得るには
どうすればいいのでしょうか？

 1行目最小値=41,最大値=60
 2行目最小値=31,最大値=60
 3行目最小値=21,最大値=60
 4行目最小値=11,最大値=60
 5行目最小値=1,最大値=60
（初心者） 2026/03/19(木) 13:54:44

 左端 =XMATCH(1,A2:CB2,0,1)
 右端 =XMATCH(1,A2:CB2,0,-1)
（´・ω・`） 2026/03/19(木) 14:03:13

（´・ω・`）さんありがとうございます。
２文探索わかりました。
と、思います。

 検索範囲が昇順ではなくて、検索範囲のデータが昇順になっていなければ
 ならないということですよね。

 今回は範囲が８０なので４０から検索をはじめて
 ２行目の４０番に１があり、４１番目が1ではないのでそれ以降に1がないと判断し
 それ以外の行では４０番に１がなく空なので（空<1）右側に検索し、
 最初に見つけた１の位置から１が続く最終の８０番目を
 答えとしているというわけですよね。
（初心者） 2026/03/19(木) 14:08:40

 Excel内部でどのように実装されているかはわかりませんので、なんともいえませんが、
 >２行目の４０番に１があり、４１番目が1ではないので
 二分探索なので、「４０番」のあとに「４１番」をチェックすることはないでしょう
（´・ω・`） 2026/03/19(木) 15:36:58

 MATCHの第3引数？、falseか0以外ほとんど使った記憶がない
 小値、最大値なら、maxやminを突くかな、必要ならLARGE、countifなどを使を組み合わせたり・・・
 結局自分の思った通りにならないとかでしょうか？
 知ってる関数でせこく工夫して色々やってます
（ジェッター） 2026/03/20(金) 15:21:44

 ＞ 二分探索なので、「４０番」のあとに「４１番」をチェックすることはないでしょう

 私もそう思っていたのですが、実験してみるとそうとも云えない気がして来ました。
 つまり、分岐点の値が検索値と違っている限りは「二分探索」するが、
 分岐点の値が検索値と合致した途端、同値の隣接が途絶えるまで「順探索」している様な気がします。

 ＜実験1＞二分探索とは思えない挙動

 行 _A_ _B_ _C_ _D_ _E_ _F_ _G_ _H_ _I_ _J_ _K_
  1 　  3rd 2nd 3rd 1st 3rd 2nd 3rd                                
  2   2   2   2   2   2       2   2   2       5 =MATCH(2,$A2:$I2,1) ←二分探索なら9ではないか？
  3   2   2   2   2   2   1   1   2   2       5 =MATCH(2,$A3:$I3,1) ←二分探索なら9ではないか？
  4   2   2   2   2   2   1   2   2   2       5 =MATCH(2,$A4:$I4,1) ←二分探索なら9ではないか？

 ＜実験2＞分岐点の値が検索値と合致した後、同値が大量に隣接すると順探索に変わった様な処理時間低下となる

 下記マクロでテストすると以下の処理時間が計測された（結果1例）

 (1)A列全て2(最終も2)、検索値2、検索モード1 Time 0:0.07 =MATCH(2,A:A,1) 1048576
 (2)A列全て2(最終は3)、検索値2、検索モード1 Time 0:0.06 =MATCH(2,A:A,1) 1048575
 (3)A列全て2(最終は3)、検索値3、検索モード1 Time 0:0.00 =MATCH(3,A:A,1) 1048576
 (4)A列全て2(最終は3)、検索値3、検索モード0 Time 0:0.12 =MATCH(3,A:A,0) 1048576

 (１)や(２)が、順探索(４)の半分くらいの処理時間になる。
 これは、2をヒット後、同値の隣接が途絶えるまで順探索モードになったのではないか？
 一方、(３)は期待通り瞬時に処理が終わっている。

 Sub test用()
     Dim testKind(1 To 4)
     Dim tm As Double
     Dim n As Long, ts As Long
     Dim valToSearch As Long
     Dim SearchMode As Long
     Dim fml As String

     testKind(1) = "A列全て2(最終も2)、検索値2、検索モード1"
     testKind(2) = "A列全て2(最終は3)、検索値2、検索モード1"
     testKind(3) = "A列全て2(最終は3)、検索値3、検索モード1"
     testKind(4) = "A列全て2(最終は3)、検索値3、検索モード0"

     For ts = 1 To 4

         Range("B1").Clear

         Columns(1) = 2
         Range("A1048576") = Mid(testKind(ts), InStr(testKind(ts), "最終") + 3, 1)
         valToSearch = Mid(testKind(ts), InStr(testKind(ts), "検索値") + 3, 1)
         SearchMode = Mid(testKind(ts), InStr(testKind(ts), "検索モード") + 5, 1)
         fml = "=MATCH(" & valToSearch & ",A:A," & SearchMode & ")"

         Application.Wait Now() + TimeSerial(0, 0, 1)
         tm = [now()]   '計測開始

         For n = 1 To 10 'Match関数を10回入力して負荷をかける
             Range("B1").Formula = fml
         Next n

         Debug.Print "(" & ts&; ")" & testKind(ts) & " Time "; _
             Application.Text([now()] - tm, "m:s.00　"); fml; " " & Range("B1").Value
     Next ts
 End Sub

（半平太） 2026/03/21(土) 10:05:20

コメント返信：

[ 一覧(最新更新順) ]

[[20260319124345]] 『MATCHの計算結果について』（初心者） ページの最後に飛ぶ

[[20260319124345]]　『MATCHの計算結果について』（初心者）　ページの最後に飛ぶ