「知っていると得するかもしれないExcelの癖」シリーズ第六回、テーマは「元表を参照する数式」になります。
Excel 2016バージョン1902を使用して確認しています。
別表シートを作る目的はいろいろあることと存じますが、今回はコードの名称をコードの隣の率に表示するというサンプルを使ってご説明いたします。
必要となるシートの種類
必要となるシートは次の3種類です。
| シートの種類 | シートの中身 |
| コード表 | コードとコード名称が並んだシートになります。コードは昇順に並んでいることが推奨されます。 |
| 元表 | 表に名称が含まれていないので、例えば商品の売り上げデータのようなトランザクションデータを 想定致します。 |
| 参照表 | 数式を設定して、元表データを参照するためのシートです。 本テーマで説明するのはこのシートがメインになります。 |
なお元表の項目は、「日付、コード、数量、売上(金額)」の4項目といたします。
参照表をセル参照で設定した場合
一般的には、シートをまたぐ参照設定の場合、参照もとのセルで「=」を入力した後で、参照先シートの該当セルをクリックして設定することと存じます。
またコードからコード名を取得するところはVLOOKUP関数で設定します。
ここまで1行分の数式を設定したら、後は設定したセル範囲をコピーして、後続行に数式貼り付けをすることで参照表の設定は完了します。
なお元表にどれだけのデータがあるか?決まっていない場合は、参照表では多めに数式を設定しておくケースがあるかと思います。
この場合、元表の空白行を参照することになるため、何も考慮しないと参照表に日付型のセルであれば「1900/1/0」とか数値型であれば「0」が表示されてしまいます。
そこでIF関数を使用して上記が表示されないように設定することにします。
実際にセル参照を使って、参照表のA2:D2のセル範囲に設定した数式は下記になります。
| 項目 | 表示形式 | 数式 |
| 日付 | 日付型 | =IF(ISBLANK(元表!A2),””,元表!A2) |
| コード | 文字型 | =IF(ISBLANK(元表!B2),””,元表!B2) |
| コード名 | 文字型 | =IF(B2=””,””,VLOOKUP(B2,コード表!$A$2:$B$5,2,FALSE)) |
| 数量 | 数値型 | =IF(ISBLANK(元表!C2),””,元表!C2) |
| 売上 | 数値型 | =IF(ISBLANK(元表!D2),””,元表!D2) |
元表に修正が発生した時
皆様も一度はご経験があるかと存じますが、折角このようにして参照表を設定したにも関わらず、元表に変更すると困ったことが起こります。
例えば、下左図のように元表の2行目と3行目の間に1行挿入すると、もとの5行目を削除すると参照表は下右図のようになります。
元表での挿入は、参照表では見た目上は反映されていませんが、参照表3行目の数式は元表の4行目をセル参照しています。
元表で削除された5行目は参照表では参照先のセルが見つからない「#REF!」のエラーになります。
セル参照を使用しない数式に変更する
現在の元表のセルの場所の情報を、参照表でも同じセルの場所に表示され続けるようにする、例えば元表でA3のセルの情報は、参照表のA3セルの場所に表示され続けるようにするためには、セル参照を使用しない数式に変更するしか方法はありません。
そこで、変更した数式は下記になります。
| 項目 | 表示形式 | 数式 |
| 日付 | 日付型 | =IF(ISBLANK(INDIRECT(“元表!”&ADDRESS(ROW(),COLUMN()))),””,INDIRECT(“元表!”&ADDRESS(ROW(),COLUMN()))) |
| コード | 文字型 | 同上 |
| コード名 | 文字型 | =IF(B2=””,””,VLOOKUP(B2,コード表!$A$2:$B$5,2,FALSE)) |
| 数量 | 数値型 | =IF(ISBLANK(INDIRECT(“元表!”&ADDRESS(ROW(),COLUMN()-1))),””,INDIRECT(“元表!”&ADDRESS(ROW(),COLUMN()-1))) |
| 売上 | 数値型 | 同上 |
セル参照ではなく、見慣れない関数が並んでいて、かつ数式も長くなるので、一般的には「これを使います」という反応にはならないことと存じます。
なおコード名の数式はもとのままで変更はありません。
この数式に変更した後の結果は下図になります。
元表で挿入された3行目には、空行が表示され、削除した行は「#REF!」にならずに、現在の元表の状態をそのまま表示しています。
数式の説明
アウェー感を感じつつも説明を続けさせていただくと、セル参照では同じ列で数式をコピーすることはできましたが、個々のセルに設定される数式はセル参照先がそれぞれ異なるために、ひとつとして全く同じ数式になるものはありませんでした。
それに対してこちらの数式は、コード名の列を境にして、左の列のグループはすべて同じ、右の列のグループもすべて同じとなり左右の2パターンになります。
しかも右の列のグループの数式は、左の数式に「-1」を追加しただけです。
従って1つの数式の意味が理解できれば、すべての数式を理解できたことになります。
セル参照の数式と、今回の数式で異なるのはつぎのところです。
元表!A2
INDIRECT("元表!"&ADDRESS(ROW(),COLUMN()))
- ROW関数とCOLUMN関数は引数が無い場合は関数がセットされているセルの行番号と列番号を返してくれます。
- ADDRESS関数は、引数に指定された、行番号と列番号のセルのセル参照文字列(セル番地)を返してくれます。
- 例えばADDRESS(2,3)であれば、2行、3列になるので「C2」という文字列を返してくれます。
- 同様にADDRESS(5,4)であれば、5行、4列になるので「D5」になります。
- INDIRECT関数は引数に指定されたセル参照文字列(セル番地)から、実際のセル参照値を返してくれます。
- 「”元表!”&」の意味ですが、「&」は文字列を結合させる式で、”元表!”は文字列です。そして”元表!”はシート名「元表」に含まれていることを表しています。
- 上図の例でINDIRECT(“元表!C2”)であれば、元表シートのC2セルの値「109商品」が返ってきます。
コード名より右側の列の数式に「-1」が付く理由は、元表の列と参照表1の列で、コード名が入っている分、列がずれています。
従いまして、そのずれを補正するために「-1」が必要なのですが、なぜマイナスなのか?というと、参照表1から見ると元表の数量と売上の列はひとつ前の列になるためです。
最終的なまとめ
知らないことを沢山並べられると拒否反応が起こるのは自然なことと存じます。
ただ、やっていることはセル参照と同じ結果が得られるような数式にしているだけです。
如何でしょうか?
言葉だけでは伝わり難いと思いますので、一度実際に試してみていただけば幸いです。
なお最後に1点注意事項がございます。
先ほどの数式ではシート名が固定文字列として組み込まれています。
「”元表!”」の部分です。
セル参照の場合は、シート名が変更されれば、元のシート名が変わった事を反映してくれますが、固定文字列となると、反映はされません。
つまり元表のシート名を変更した場合は、数式に含まれるすべての固定文字列を変更する必要があります。
ひとつひとつではなく数式に含まれる固定文字列をまとめて置換するためには、一度すべての数式を文字列に変換し、置換をした後に、再度文字列から数式に変換するか、さもなくば、変更後のシート名に変えた数式ですべての数式の入ったセルにコピペするかになりますが、どちらにしも手間暇がかかります。
そこで推奨としては、シート名を特定のセルに格納し、数式に含まれる固定文字列のところを格納したセルへの絶対参照に変更することです。
例えばセルG2にシート名を格納した場合は、さきほどの数式はつぎのようになります。
=IF(ISBLANK(INDIRECT($G$2&"!"&ADDRESS(ROW(),COLUMN()))),"",INDIRECT($G$2&"!"&ADDRESS(ROW(),COLUMN())))
なおシート名の末尾に「!」を付けることをお許しいただけるのであれば、上記数式から「&”!”」を取り除くことができます。
以上、最後までご一読いただきありがとうございました。
