複数ワークシートの情報一覧を作成する

#バックオフィス

#Microsoft Excel

くにきち

2012.06.13

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは。1か月捻挫を放置したためなかなか治らなくなっているクニ吉です。捻挫したままイベントで3日間立ちっぱしたのがいけなかったのか・・(´ﾛ｀||) 捻挫をしたら早めに治療してくださいね（病院に行って初めて捻挫だとわかったんですけど）

さて、今回もExcelの話。 Excelの各シート内の情報を一覧にするというものです。例えば、下図のような1Sheetに1商品の在庫管理表があるとします。

この状態ですと、各商品の在庫数を確認するにはそれぞれの商品シートを確認しなければなりません。そこで一部の情報を「在庫一覧表」というシートでまとめて見られるようにする、というのが今回のゴールです。

完成イメージはコレ↓

【前提と項目説明】

一覧表はテーブルで作成している（テーブルについては「Excelのテーブル機能」をご参照ください）
商品シートは1～3まで作成している。
No.→シートの名前。直接入力している。VBA使用でシート名一覧作成も可能
カタログ商品番号、商品型番、商品名、在庫数、平均出荷数→関数を使用して、別シートの値を表示
アラート→関数を使用して、各商品ごとに設定された発注タイミングより在庫数が下回った場合"発注してください"を表示

別シートの値を表示する

では、早速肝となる関数を紹介します。使用したのは以下4つの関数です。

INDIRECT関数
IF関数
ISBLANK関数
IFERROR関数

◎INDIRECT関数

INDIRECT（[参照文字列],[参照形式]) 文字列で指定されたセルを参照します。式の返り値で表示内容を変えたり、検索する対象範囲を変えることが出来ます。

例えば下記の式を今回情報をまとめる「在庫一覧表」シートの「商品名」列（D3~D7）に入力します。

=INDIRECT("'"&[@[No.]]&"'!C2")

この式は「No.」列の値でシート名を指定し、指定したシートのセルC2（商品名）を参照表示します。つまり「No.」の値が「1」であれば、「1」というシート名のC2セルの値を表示することが出来ます。同じ要領で、B~F列に式を入れていきます。

しかしこの式ですと「No.」に表示された値と同じ名前のシートが作成されていない6、7行目が、セルを参照出来ずにエラーとなり、シート「1」のセルC2（商品名）の値を削除した場合、シート「1」のセルC2を参照しているセルD3に「0（ゼロ）」と表示されてしまいました。

エラーを回避する

これを回避するのに登場するのが、IF関数、ISBLANK関数、IFERROR関数です。

◎IF関数

IF([条件式],[条件式がTRUEの場合に表示する値],[条件式がFALSEの場合に表示する値]）条件式で判定した結果、任意の値を表示します。とにかく使える関数で、クニ吉が大好きな関数です（●＾＾●）

【例】=IF(A1>20,"OK","NG") という式の場合

条件式：A1>20　セルA1の値が20以上であればTRUE、20以下であればFALSE A1の値が50の場合、TRUEとなり「OK」を返します A1の値が10の場合、FALSE となり「NG」を返します

◎ISBLANK関数

ISBLANK([検査する対象]) セルの内容が空白かどうかを調べます。指定したセルが空白の場合、TRUEを返します。※空白文字列の「""」は空白とは判定されません。指定したセルに値が入力されている場合、FALSEを返します。

【例】=ISBLANK(A1)という式の場合

A1が未入力の場合、TRUEを返します A1に値が入力されている場合、FALSE を返します

前述の「0（ゼロ）」表示は、IF関数の条件判定にISBLANK関数を使用して回避します。

◎IFERROR関数

IFERROR([対象セル], [エラー時に表示する値]) 式がエラーとして判定された場合に任意の値を表示することが出来ます。エラーの対象となるのは、「#N/A」,「#VALUE!」,「#REF!」,「#DIV/0!」,「#NUM!」,「#NAME?」,「#NULL!」

【例】=IFERROR(A1,"エラーだよ")という式の場合

A1に「1」という値が入力されている場合、「1」を返します A1に「#REF!」というエラー表示がされている場合、「エラーだよ」を返します

「No.」に記載された名前のシートがない場合のエラー表示を回避します。

◎関数を組み合わせてエラーを回避する

これらの関数を組み合わせ、下記のように書きました。 IF関数で条件判定をし、表示する値を指定しています。

=IF(ISBLANK(INDIRECT("'"&[@[No.]]&"'!C2"))=TRUE,"",IFERROR(INDIRECT("'"&[@[No.]]&"'!C2"),""))

【条件1】IF(ISBLANK(INDIRECT(""&[@[No.]]&"!C2"))=TRUE,"", 　指定したシートのセルC2（商品名）が空白の場合は、空白文字列「""」を表示

【条件2】IFERROR(INDIRECT("'"&[@[No.]]&"'!C2"),"") 　空白ではない（入力されている）場合は、指定したシートのセルC2（商品名）を表示、セルの結果がエラーの場合は空白文字列「""」を表示

と、このような感じです。これできれいに表示することが出来ます。

一定の条件でアラートを表示させる

ここまでくればオマケのようなものですが、アラート表示には下記の式を入れています。

=IF([@在庫数]="","",IF([@在庫数]<INDIRECT("'"&[@[No.]]&"'!F17"),"発注してください",""))

【条件1】IF([@在庫数]="","", 在庫管理表の「在庫数」列が空白の場合、空白文字列「""」を表示 ※ここでISBLANK関数を使用しないのは、「在庫数」列には見た目が空白でも前述の条件式の結果として空白文字列「""」が入っているため、ISBLANK（[@在庫数]）=TRUEとはならないからです。

【条件2-条件1】IF([@在庫数]<INDIRECT("'"&[@[No.]]&"'!F17"),"発注してください", 在庫管理表の「在庫数」列が空白ではなく、更に「在庫数」の値が指定したシートのセルF17（発注タイミング）の値より小さい場合、「発注してください」を表示ここではまたIF関数を入れてます。条件2に判定された後、更に条件で分岐させることが出来ます。文字が赤いのは、条件付き書式の設定でアラート列に「発注してください」という値が入った時に文字を赤く表示するようにしています。条件付き書式については、以前書いた「勤務表を作ってみた（1）」で触れているのでご参照ください。

【条件2-条件2】 "")) 条件1、条件2-1に判定されない場合、空白文字列「""」を表示

まとめ

INDIRECT関数（文字列で指定されたセルを参照する関数）－INDIRECT関数だけでは不完全
IF関数（条件式で判定した結果で任意の値を表示する関数）ー複数の条件設定も可能。
ISBLANK関数（セルの内容が空白かどうかを調べる関数）ー空白文字列「""」はTRUE判定されない。
IFERROR関数（エラーとして判定された場合に任意の値を表示する関数）ーエラー対象：「#N/A」,「#VALUE!」,「#REF!」,「#DIV/0!」,「#NUM!」,「#NAME?」,「#NULL!」
返り値によっては求める結果にならないため、式の返り値を意識して使用する関数を選択する。

これで一覧が完成です。前職で最初に出てきたようなエクセルの商品シートで在庫管理をしていたので、その時にこれが作れていれば！という感じ。大規模であればシステム導入を検討した方がいいですが、小規模であれば十分ですよね。今回は在庫管理をテーマにしましたが、紹介した関数を使える場面はいろいろあると思うので、ぜひ使ってみてください。

今日はこの辺で～ヾ(○ﾟ▽ﾟ○) ﾏｯﾀﾈｰﾝ♪