Excelのテーブルでmatch関数の使い方について

Excelについて

入力した日付と項目の該当期間を検索して、その値を表示する
数式を作成しました。
これをテーブルで実現したいと思い、テーブルに変換し
数式を作ってみたのですがエラーになってしまいます。
（　・青色のテーブルは「T青」
　　・オレンジ色のテーブルは「Tオ」と定義）
添付の画像を見ていただければわかると思います。

どなたか、エラーの原因と、正しい数式を教えていただけませんでしょうか？

よろしくお願いします。

No.2 ベストアンサー 回答者： goomania 回答日時： 2024/01/24 09:06

原因はfujillinさんのご指摘のとおり、テーブルの「見出し」は「文字列」として認識されるので、テーブル内の日付データ(シリアル値)と比較しても正しい結果が得られないことにあります。

従って解決策とすれば
(1)日付データを文字列に変換してから比較する
(2)見出しをシリアル値に変換してから比較する
の2つの方法のどちらかを選択することになります。
(1)の方法はfujillinさんが回答されておられますので、(2)の方法をお示しします。

ご質問者の掲出された画像でいうと、C2セルの数式を、

=VLOOKUP([@項目],Tオ,INDEX(MATCH([@日付],--Tオ[#見出し],1),0),FALSE)･･･①

というように見出しの頭に「--」とマイナスを2つ付けてシリアル値にして、INDEX関数で囲むことで対応可能です。

なお、スピル機能の有効なバージョンでは、

=VLOOKUP([@項目],Tオ,MATCH([@日付],--Tオ[#見出し],1),FALSE)･･･②

とすればよいので、INDEX関数が不要になります。

ということは逆にいうと、スピル機能のないバージョンで②の数式を数式バーに入れ、CTRL+SHIFT+ENTERで確定して「配列数式」にしてもよいと言うことになります。

この回答へのお礼

goomaniaさま

ありがとうございます。
ご提示いただいた数式で試したところ
うまくいきました。

「--」をつけるとシリアル値に変換されるとは、
知りませんでした。。。

バージョンは３６５なので｛｝は不要でした。
重ねてお礼申し上げます。

ありがとうございます。

お礼日時：2024/01/25 09:46

No.1 回答者： fujillin 回答日時： 2024/01/23 20:43

こんばんは

構造化参照は利用したことがないので、説明を読みながら試してみただけですが・・
https://support.microsoft.com/ja-jp/office/excel …

いくつかの留意点があるようです。
1）まず、ご提示の図で、上段の（＝非テーブル）での参照式を入力しても、結果は図とは異なりますので、図の値は間違えと解釈しました。
2）テーブルの見出し欄に日付を設定した場合、シリアル値（＝数値）としては解釈されず、文字列として解釈されるようです。
そのため、シリアル値と比較しても正しい結果は得られません。
3）項目指定子の#All、#Data、#Headers・・等は使用できないようです。
　（これに関しては、ご提示の式の様子からご存じと思いますけれど・・）


以上を考慮して、ご提示の図の下段のC2セルに
=VLOOKUP([@項目],Tオ,MATCH(TEXT([@日付],"yyyy/m/d"),Tオ[#見出し],1),FALSE)
の式を入力すれば、ほぼ似た様な結果がスピルされます。

「ほぼ似た様な」としてあるのは、MATCH関数でシリアル値で検索せずに、文字列で検索しているため、数値と文字列の比較の違いが生じているからです。
同じ結果にしたい場合は、文字列の表示を「yyyy/mm/dd」とするなど、数値の場合と同じ結果が得られるように工夫するなどの必要がありそうですね。

この回答へのお礼

fujillinさま
ご回答ありがとうございます。
見出しは文字列と解釈されるとは
知りませんでした。
ありがとうございました。

お礼日時：2024/01/25 09:41