2019.03.27この記事は約7分で読めます。「知っていると得するかもしれないExcelの癖」シリーズ第六回、テーマは「元表を参照する数式」になります。2016バージョン1902を使用して確認しています。別表シートを作る目的はいろいろあることと存じますが、今回はコードの名称をコードの隣の率に表示するというサンプルを使ってご説明いたします。必要となるシートは次の3種類です。コードとコード名称が並んだシートになります。コードは昇順に並んでいることが推奨されます。表に名称が含まれていないので、例えば商品の売り上げデータのようなトランザクションデータを想定致します。数式を設定して、元表データを参照するためのシートです。本テーマで説明するのはこのシートがメインになります。元表の項目は、「日付、コード、数量、売上(金額)」の4項目といたします。一般的には、シートをまたぐ参照設定の場合、参照もとのセルで「=」を入力した後で、参照先シートの該当セルをクリックして設定することと存じます。コードからコード名を取得するところはVLOOKUP関数で設定します。ここまで1行分の数式を設定したら、後は設定したセル範囲をコピーして、後続行に数式貼り付けをすることで参照表の設定は完了します。元表にどれだけのデータがあるか?決まっていない場合は、参照表では多めに数式を設定しておくケースがあるかと思います。この場合、元表の空白行を参照することになるため、何も考慮しないと参照表に日付型のセルであれば「1900/1/0」とか数値型であれば「0」が表示されてしまいます。そこでIF関数を使用して上記が表示されないように設定することにします。実際にセル参照を使って、参照表のA2:D2のセル範囲に設定した数式は下記になります。皆様も一度はご経験があるかと存じますが、折角このようにして参照表を設定したにも関わらず、元表に変更すると困ったことが起こります。下左図のように元表の2行目と3行目の間に1行挿入すると、もとの5行目を削除すると参照表は下右図のようになります。元表での挿入は、参照表では見た目上は反映されていませんが、参照表3行目の数式は元表の4行目をセル参照しています。元表で削除された5行目は参照表では参照先のセルが見つからない「#REF!」のエラーになります。セルに設定されたセル参照は、「設定された時点の参照先のセル」の状態を把握していて、もしも参照先のセルに変更があった場合は、「変更前の参照先のセルが変更後どうなっているか?」にもとづき結果を反映させます。変更前A5だったセルが削除されていれば、変更後のA5ではなく、変更前のA5が削除されたことを反映セル参照を使用しない数式に変更する現在の元表のセルの場所の情報を、参照表でも同じセルの場所に表示され続けるようにする、例えば元表でA3のセルの情報は、参照表のA3セルの場所に表示され続けるようにするためには、セル参照を使用しない数式に変更するしか方法はありません。そこで、変更した数式は下記になります。同上セル参照ではなく、見慣れない関数が並んでいて、かつ数式も長くなるので、一般的には「これを使います」という反応にはならないことと存じます。コード名の数式はもとのままで変更はありません。この数式に変更した後の結果は下図になります。元表で挿入された3行目には、空行が表示され、削除した行は「#REF!」にならずに、現在の元表の状態をそのまま表示しています。数式の説明アウェー感を感じつつも説明を続けさせていただくと、セル参照では同じ列で数式をコピーすることはできましたが、個々のセルに設定される数式はセル参照先がそれぞれ異なるために、ひとつとして全く同じ数式になるものはありませんでした。それに対してこちらの数式は、コード名の列を境にして、左の列のグループはすべて同じ、右の列のグループもすべて同じとなり左右の2パターンになります。右の列のグループの数式は、左の数式に「-1」を追加しただけです。従って1つの数式の意味が理解できれば、すべての数式を理解できたことになります。セル参照の数式と、今回の数式で異なるのはつぎのところです。元表!A2INDIRECT("元表!"&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())))なおシート名の末尾に「!」を付けることをお許しいただけるのであれば、上記数式から「&”!”」を取り除くことができます。以上、最後までご一読いただきありがとうございました。知っ得かもしれないExcelの癖-その12行で1データの表で縦計算をする時にセル結合を効果的に使う「知っていると得するかもしれないExcelの癖」シリーズ第七回、テーマは「1行おきの縦計算」になります。「知っていると得するかもしれないExcelの癖」シリーズ第二回、テーマは「文字列結合について」になります。2016バージョン1902を使用して確...知っ得かもしれないExcelの癖-その1Excelで行を追加削除してもメンテナンスがほぼ要らない行番号を付ける「知っていると得するかもしれないExcelの癖」シリーズ第三回、テーマは「行番号について」になります。「知っていると得するかもしれないExcelの癖」シリーズ第九回、テーマは「テーブルでの数式利用」になります。「知っていると得するかもしれないExcelの癖」シリーズ第四回、テーマは「名前の管理について」になります。「知っていると得するかもしれないExcelの癖」シリーズ第十二回、テーマは「オートコレクト機能の例外」になります。「知っていると得するかもしれないExcelの癖」シリーズ第九回、テーマは「テーブルでの数式利用」になります。2016バージョン1902を使用して確認しています。...2019.03.302019.04.25セル左上隅のエラー表示(エラーインジケーター)を有効に使う「知っていると得するかもしれないExcelの癖」シリーズ第八回、テーマは「セル左上のエラー表示」になります。2016バージョン1902を使用して確認しています。「知っていると得するかもしれないExcelの癖」シリーズ第十回、テーマは「1行おきの行番号」になります。2016バージョン1902を使用して確認しています。...2019.04.012019.04.24人気記事(全カテゴリー全期間)Excel条件付き書式の数式における相対参照と絶対参照の使い分け「知っていると得するかもしれないExcelの癖」シリーズ第十七回、テーマは「条件付き書式の参照設定」になります。2016バージョン1903を使用して確認しています。...2019.04.142019.12.25Cocoonでスニペット付き新着記事と人気記事一覧の作成ver1.9.1Cocoonではver1.8.9.4からver1.9.1にかけて、新着記事ウィジェット・人気記事ウィジェットに加えて新たにナビカードウィジェットの機能が追加されました。この新機能...2019.02.122019.12.25Excelで動的にシート内ハイパーリンクを設定する「知っていると得するかもしれないExcelの癖」シリーズ第十五回、テーマは「ハイパーリンクの設定」になります。2016バージョン1902を使用して確認しています。...2019.04.102019.12.25Cocoonヘッダー(グローバル)ナビのサブメニュー名を2行表示にするサイト開始から1ヵ月強が過ぎ、全体的にこれまで書いた記事の見直しを進めています。AdSenseの認定で「質の低いサイト」と言...2019.03.082019.11.19Cocoonでカテゴリー別にサイトマップを表示するプログラム無料のワードプレス(以下WP)のテーマCocoonで作成者の「わいひら」様が下記のページでショートコードを公開されています。...2019.02.202019.05.17Cocoonアピールエリアの表示スタイルをいじる最後と言いながら1点、標準のアピールエリアのプロクラムを修正せていただきました。これは全くの好みというか、見た目だけの修正なので本来は控えるべき話ではありますが、トップページの部...2019.02.222019.04.18Cocoonでカテゴリーページに一般的なパンくずリストを表示するワードプレス(以下WP)の無料のテーマCocoonでは、設定だけではカテゴリーページ(カテゴリーをクリックした時に表示される当該カテゴリーの一覧のページ)に一般的なパンくずリスト...2019.02.112019.04.18Excelピボットテーブルの自動更新でセルとは別にデータを保存する「知っていると得するかもしれないExcelの癖」シリーズ第二十回、テーマは「ピボットテーブルへのデータ保存」になります。2016バージョン1903を使用して確認して...2019.04.252019.04.27数独向けの条件付き書式とは何ですか?9×9の数独をご存知でしょうか?「数独」もしくは「ナンプレ」という名称で呼ばれていますが以下「数独」と表記いたします。「数独向けの条件付き書式とは何ですか?」の記事を書いてから、2ヵ月半が過ぎてしまいました。「知っていると得するかもしれないExcelの癖」シリーズ第九回、テーマは「テーブルでの数式利用」になります。2016バージョン1902を使用して確認しています。...2019.03.302019.04.25セル左上隅のエラー表示(エラーインジケーター)を有効に使う「知っていると得するかもしれないExcelの癖」シリーズ第八回、テーマは「セル左上のエラー表示」になります。2016バージョン1902を使用して確認しています。「知っていると得するかもしれないExcelの癖」シリーズ第十回、テーマは「1行おきの行番号」になります。2016バージョン1902を使用して確認しています。...2019.04.012019.04.24人気記事(全カテゴリー全期間)Excel条件付き書式の数式における相対参照と絶対参照の使い分け「知っていると得するかもしれないExcelの癖」シリーズ第十七回、テーマは「条件付き書式の参照設定」になります。2016バージョン1903を使用して確認しています。...2019.04.142019.12.25Cocoonでスニペット付き新着記事と人気記事一覧の作成ver1.9.1Cocoonではver1.8.9.4からver1.9.1にかけて、新着記事ウィジェット・人気記事ウィジェットに加えて新たにナビカードウィジェットの機能が追加されました。この新機能...2019.02.122019.12.25Excelで動的にシート内ハイパーリンクを設定する「知っていると得するかもしれないExcelの癖」シリーズ第十五回、テーマは「ハイパーリンクの設定」になります。2016バージョン1902を使用して確認しています。...2019.04.102019.12.25Cocoonヘッダー(グローバル)ナビのサブメニュー名を2行表示にするサイト開始から1ヵ月強が過ぎ、全体的にこれまで書いた記事の見直しを進めています。AdSenseの認定で「質の低いサイト」と言...2019.03.082019.11.19Cocoonでカテゴリー別にサイトマップを表示するプログラム無料のワードプレス(以下WP)のテーマCocoonで作成者の「わいひら」様が下記のページでショートコードを公開されています。...2019.02.202019.05.17Cocoonアピールエリアの表示スタイルをいじる最後と言いながら1点、標準のアピールエリアのプロクラムを修正せていただきました。これは全くの好みというか、見た目だけの修正なので本来は控えるべき話ではありますが、トップページの部...2019.02.222019.04.18Cocoonでカテゴリーページに一般的なパンくずリストを表示するワードプレス(以下WP)の無料のテーマCocoonでは、設定だけではカテゴリーページ(カテゴリーをクリックした時に表示される当該カテゴリーの一覧のページ)に一般的なパンくずリスト...2019.02.112019.04.18Excelピボットテーブルの自動更新でセルとは別にデータを保存する「知っていると得するかもしれないExcelの癖」シリーズ第二十回、テーマは「ピボットテーブルへのデータ保存」になります。2016バージョン1903を使用して確認して...2019.04.252019.04.27数独向けの条件付き書式とは何ですか?9×9の数独をご存知でしょうか?「数独」もしくは「ナンプレ」という名称で呼ばれていますが以下「数独」と表記いたします。「数独向けの条件付き書式とは何ですか?」の記事を書いてから、2ヵ月半が過ぎてしまいました。BIのTips1サイト構築に関する事柄1会社設立に関する事柄1サイト全般22ワードプレスのテーマCocoonについて12ワードプレスでの基本ルールを考える7会社全般4会社紹介1書庫(アーカイブ)2スポンサーリンク。