コードから名称を表示

「社員コード」を入力すると「氏名」が自動表示されるという機能です。   先に「社員マスタ」のようなシートを用意しておいて「社員コード」「氏名」の一覧を作成しておきます。 「社員コード」を入力して「氏名」を表示させる方のシートの「氏名」のセルに下記のような計算式を入れるだけでこのような機能が実現します。
この時の「社員コード」が「数値」なのか「文字列」なのか統一されていないとうまく行かないこともありますが、その説明も加えてあります。


マスタテーブル(セル範囲)からコードに相当する名称を見つけて表示する
一般的なコンピュータ処理では、売上明細など個々の明細データには﹁名称﹂の項目はなく、﹁コード﹂から該当のマスタを参照して﹁名称﹂を取り出します。
  ここで説明するのが、そういったマスタから名称やそれ以外の補足情報を参照する方法です。
  このサンプルは、社員番号から氏名を表示させています。
  VLOOKUP関数サンプル (画像をクリックすると、このサンプルがダウンロードできます)
  ﹁VLOOKUP関数﹂を使います。﹁氏名﹂セルの数式は、

 =VLOOKUP($B$2,マスタ!$A$1:$B$7,2,FALSE)
このようになります。
関数概略説明
 VLOOKUP関数  テーブルまたはセル範囲の内容を行ごとに検索し、見つかった行の指定列番号の値を返します。
 検索はテーブルの左端列で行なわれます。引数は以下の通りです。
 ① 検索値
 ② テーブルとなるセル範囲
 ③ 戻り値となる列番号(セル範囲の左端列を｢1｣とする相対番号)
 ④ 検索方法(完全一致はFALSEとする)
 
※通常は、このように﹁縦方向﹂に向かって参照しますが、例えば﹁コード﹂が横に並んでいるような場合は、﹁HLOOKUP関数﹂を同じように使います。
  
テーブルに存在しない﹁コード﹂を入力した時はこのサンプルでは﹁#N/A﹂というエラーになりますが、エラーの表現を変更したいような場合は﹁エラーの回避策﹂をご覧下さい。
  

マスタテーブル(セル範囲)からコードに相当する相対位置を見つける
﹁VLOOKUP関数﹂では、テーブル位置の﹁他の列﹂の内容を表示しますが、場合によってはその内容ではなく、﹁何番目﹂にあるかが取り出したい場合があります。  ﹁コード﹂を見つけた同じ行から複数の列の値を参照したい場合や、﹁コード﹂の列が参照したい内容の列より右にある場合などが一般的なケースでしょう。
  このような場合は、﹁MATCH関数﹂を使います。
  MATCH関数サンプル (画像をクリックすると、このサンプルがダウンロードできます)
  この例の程度であれば、同じように﹁VLOOKUP関数﹂を﹁氏名﹂﹁部署﹂﹁内線﹂それぞれに置けば良いのですが、一度の検索で﹁位置﹂を掴んでしまえば同じ配列位置からそれぞれの参照値を取り出せるということも多いと思います。
  このような場合は、一旦欄外のセルに参照位置の﹁番号﹂を1回だけ﹁MATCH関数﹂で参照しておき、﹁氏名﹂﹁部署﹂﹁内線﹂の項目は、﹁INDEX関数﹂でその﹁番号﹂を使って直接マスタシートのテーブルを参照しています。 欄外のセル(F2)の数式は、

 =MATCH($B$2,マスタ!$A$2:$A$8,0)
このようになります。 一方、「氏名」「部署」「内線」セルの数式は、

[氏名(B4)]
 =INDEX(マスタ!$A$2:$D$8,$F$2,2)
[部署(B5)]
 =INDEX(マスタ!$A$2:$D$8,$F$2,3)
[内線(B6)]
 =INDEX(マスタ!$A$2:$D$8,$F$2,4)
このようになります。
関数概略説明
 MATCH関数  検索値で検索セル範囲を検索し、見つかった位置の検索セル範囲内での相対位置を返します。
 引数は以下の通りです。
 ① 検索値
 ② 検索するセル範囲
 ③ 照合の型(省略可：省略時は1)
   ⇒1:検索値以下の最大値、0:検索値と一致する値、-1:検索値以上の最小値
     ※0以外の場合は検索するセル範囲が値の昇順(1)、または降順(-1)に並んでいる必要があります。
 INDEX関数  行番号、列番号により、テーブルにある値、またはセル範囲あるいはその値のセル参照を返します。
 引数は以下の通りです。
 ① 配列またはセル範囲
 ② 行番号(相対値)
 ③ 列番号(相対値)
 

XLOOKUP関数を使ってみましょう。
﹁XLOOKUP関数﹂は﹁Office 365(現在はMicrosoft 365)﹂と﹁Office 2021以降﹂のExcelにしか実装されていない関数です。
  2019年頃から﹁スピル﹂が話題になっていますが、この関数でもこの﹁スピル﹂を利用します。
  XLOOKUP関数サンプル (画像をクリックすると、このサンプルがダウンロードできます)
  計算式は、

 =XLOOKUP($B$2,マスタ!$A$2:$A$8,マスタ!$B$2:$D$8)
です。
XLOOKUP関数サンプル
この計算式はC2セルだけに投入しており、D2セル、E2セルは同じ計算式が数式バーに表示されますがグレー表示となっています。
関数概略説明
 XLOOKUP関数  範囲または配列を検索し、最初に見つかった一致に対応する項目を返します。 一致するものがない場合、最も近い(近似)一致を返します。
 引数は以下の通りです。(④以降はオプション)
 ① 検索値
 ② 検索範囲(検索する配列またはセル範囲)
 ③ 戻り配列(返す配列またはセル範囲)
 ④ 見つからない場合(初期値は#N/A)
 ⑤ 一致モード(初期値は0)
値 モードの説明
0  完全一致 見つからない場合は#N/Aが返される
-1  完全一致 見つからない場合は次の小さなアイテムが返される
1  完全一致 見つからない場合は次の大きなアイテムが返される
2  ワイルドカード一致(*、?等)
 ⑥ 検索モード(初期値は1)
値 モードの説明
1  先頭の項目から検索を実行
-1  末尾の項目から逆方向に検索を実行
2  昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行
-2  降順で並べ替えられた検索範囲を使用してバイナリ検索を実行
 
さて、﹁スピル﹂であふれた分の結果はそのままだと上の画像のように右側セルに表示されます。
  これを、前項の﹁MATCH関数﹂のサンプルのように縦に並べたい時はこのようにします。
 XLOOKUP関数サンプル
 計算式は、

 =TRANSPOSE(XLOOKUP($B$2,マスタ!$A$2:$A$8,マスタ!$B$2:$D$8))
です。
関数概略説明
 TRANSPOSE関数  引数で指定されたセル範囲の縦横変換した内容を、数式投入したセルを起点にスピル展開します。
 
つまり、﹁XLOOKUP関数﹂の処理結果に対して、﹁TRANSPOSE関数﹂で行列を入れ替えているだけですが、これにより﹁スピル﹂のあふれる方向が﹁縦方向﹂に替わります。
  
 念のため、XLOOKUP関数に対応していないバージョンのExcelで開いてみました。
  該当PCは単なるOffice2019(売り切り版)です。サブスクリプション対応ではありません。
 XLOOKUP関数サンプル
 このようになりました。
  計算式は、

 ={_xlfn.XLOOKUP($B$2,マスタ!$A$1:$A$7,マスタ!$B$1:$D$7)}
 です。
  ﹁スピル﹂なので配列数式に変換されていますが、それより、  ﹁XLOOKUP関数﹂の前にある﹁_xlfn.﹂です。
  この﹁_xlfn.﹂が付くのは、開いているバージョンで対応されていない関数が使われていることを意味しています。  再計算させてもエラーにはならずにセル表示値も前回保存時の値のままになります。
  
﹁_xlfn.﹂が表示されるのはこのワークブックを作成した側のExcelのバージョンが、  現在開いているExcelのバージョンより高く、さらに高いバージョン側でしか対応していない関数を使用していることを意味しています。
  

﹁コード﹂は数字並びの﹁文字列﹂ですが．．．

VLOOKUP関数の参照式は合っているはずなのに﹁＃N/A﹂になるのはなぜ？


参照シートの方が﹁数値﹂、マスタリストの方が﹁文字列﹂の場合
一般に﹁社員コード﹂﹁商品コード﹂というものは数字並びであっても加減乗除の計算をするための﹁数値﹂ではなく、桁ごとに意味があることもある数字並びの﹁文字列﹂であるはずです。  これを﹁数値﹂として扱ってしまうとゼロ始まりの場合が問題となってしまいます。Excelではセルの書式設定を﹁標準﹂のままにしておくと、数字並びの入力は基本的に﹁数値﹂で扱われます。
  ﹁VLOOKUP関数﹂の参照式でリストにヒットするはずの値があって参照範囲も正しいのに﹁＃N/A﹂になってしまうというような場合は、探索している﹁コード﹂の属性をチェックしてみて下さい。
  MATCH関数サンプル２ (画像をクリックすると、このサンプルがダウンロードできます)
  例えばこのサンプルでは、社員№は﹁002﹂と表示されていますが、これはセルの書式設定で前ゼロが付くようになっているからで、数式バーの表示は﹁2﹂なのです。
  MATCH関数サンプル２ 一方、﹁マスタ﹂シートの方の社員№がこのように文字列︵数式バーにも﹁002﹂と表示される︶の場合、数値の﹁2﹂と文字列の﹁002﹂では同じ値だと見なされず﹁＃N/A﹂となってしまうのです。
  
このような場合は、参照シートの方で﹁型変換﹂させます。
もちろん、参照シートの方とマスタリストの方で同じ形式にできれば問題ないのですが、作成者が違う別ブックであったりで変更できないこともあります。
  MATCH関数サンプル２ そのような場合は、﹁VLOOKUP関数﹂の中で参照シート側の値を﹁型変換﹂させてしまいます。

=VLOOKUP(TEXT($B$2,"000"),マスタ!$A$2:$B$10,2,FALSE)
このように、「TEXT関数」を併用して数値の「2」を文字列の「002」に変換してから「VLOOKUP関数」に掛けるようにすればヒットするようになります。
逆のケースも同様に対処できます。
逆のケース（参照シートの方が「文字列」、マスタリストの方が「数値」の場合）はこのように対処します。
MATCH関数サンプル２
このケースでは参照シートの方の「文字列」を「数値」にするので、「VALUE関数」を使用して「型変換」します。

=VLOOKUP(VALUE($B$2),マスタ!$A$2:$B$10,2,FALSE)





関数概略説明
 TEXT関数  表示形式コードを使用して数値に書式設定を適用することで、数値の表示方法を変更することができます。
 引数は以下の通りです。
 ① 値(数値)
 ② 表示形式コード
 VALUE関数  「数値を表す文字列」を「数値」に変換します。引数は「数値を表す文字列」です。
 セルの左上に表示される緑の三角マークは何？    この上のサンプル画像の﹁社員№﹂セルやさらに2つ手前の画像︵﹁マスタ﹂シート︶の﹁コード﹂列のようにセルの左上に緑の三角マークが表示されることがあります。  これは﹁エラーチェック﹂で設定された警告なのですが、この場合は﹁文字列として保存されている数値﹂という意味を持っています。先に説明した通り﹁コード﹂は一般的には﹁文字列﹂なのですからこの警告はおかしいとも言えます。ですがExcelは加減乗除のような計算を行なう数値を多く扱うので﹁文字列﹂で格納されている数値に警告を表示するような機能を持っているのでしょう。  ですが、これを利用して参照元とマスタリストの書式の不一致が発見できるので利用してみるのも良いと思います。