2015.02.18Excelで関数を使う時には、別シートのセルを指定することもよくあります。シート内のセルの時と少し勝手が違うので、注意すべき点をおさえておきましょう。「A1」「B5」など、セルの場所を指定する「セル番地」があります。「指定されたセルのデータを使いなさい」という指示を出していることになります。このような表があるとします。赤で囲ったセルに、SUM関数で得票数の合計を表示することを考えます。「D3セルからD14セルまでのデータを使いなさい」という指示になって、正しく合計してくれます。実は、普段、当たり前のように使っている、「D3」や「D14」のような、セル番地だけを入力する方法には、ある情報が省略されていることを覚えておく必要があります。Excel上でセル番地を指定するためには、セル以外に2つの情報が必要です。1つは、ブックもう1つが、シートです。(ブックとは、この「ファイル」のことを指します。)なぜ、セル以外の情報として、上の2つが必要になるかというと、Excelは、その重要な仕組みとして「階層構造」を持っているからです。大きな単位から順番に指定しなければいけない、というルールのこと。セルの順番で指定します。「セルを指定する」場合には、その前に「ブック((例)book1、book2など)」「シート((例)sheet1、sheet2など)」を指定するのが、本来のルールなのです。自動的に指定されているからです。・ブック、シートの指定がなければ、「今開いているブック&シート」が自動的に指定されるこの2点を理解しておけば、セルの指定がスムーズになります。これを踏まえて、関数の中で、別シートのセルを指定する方法を考えます。先ほどの表を、別シートで集計することを考えます。「201503」以下のシートには、このような表が作成され(形は全て同じ。数字だけ異なります)、Summaryシートで、SUM関数を使い、「201503」、「201502」、「201501」シートの得票数の合計をもとめます。ポイントはシートの指定の仕方です。「201503」シートを指定します。SUM関数が入力されているセルを見て下さい。’」(クォーテーションマーク)で、「シート名」を囲み「!」(エクスクラメーション)で、セル番地とつなぐ、ことで、シートを指定できることが分かりました。次に説明する、より簡単な方法で、別シートのセルを指定する時に役立ちます。INDIRECT関数を使って、簡単に別シートを指定する別シートのセルを指定する際の、シートの指定方法を見てきました。指定するシートのセルに移動してドラッグするのも、シート数が増えると、やはり、面倒です。そこで、シート数が増えた場合にでも、簡単にシートを指定できる方法を考えます。INDIRECT関数を使う方法です。最初に、INDIRECT関数を使って入力する際のポイントを押さえておきます。・INDIRECT関数でシートとセルのデータをまとめて指定する。”」で囲んで文字データにして、シートのデータと「&」でつなぐ=SUM(INDIRECT(D2&”!$D$3:$D$14“))この2点です。INDIRECT関数はセルに入力された「シート名」や「セル番号」をExcel上の場所を示す、シート名やセル番号に変換してくれる関数です。C2セルに「B2」と入力したとします。「C2セルのデータを使う」ように指定すると、C2セルに入力された「B2」を表示します。「B2セルのデータを使いなさい」という指示に変わります。その結果、B2に入力されている「ヴィヴィくん」が表示されます。関数で指定するシート名として使ってしまいましょう。シート名が入力されているセルをINDIRECT関数で指定するところからスタートします。元の式が、=SUM(‘201502’!$D$3:$D$14)ですから、赤字の部分ですね。ここを、INDIRECT関数で指定します。「$D$3:$D$14」は単独で入力されていることになってしまいます。シートは「201502」が指定されているのに、セルは、「今開いているシート(=Summaryシート)」のセルを指定することになってしまうので、正しくセルが指定できていないことになるのです。そこで、修正を加えます。「シート」だけでなく、「セル」まで含めた、ひとまとまりを、INDIRECT関数で指定します。=SUM(INDIRECT(D2!$D$3:$D$14))こうなりました。これで上手くいきそうですが、もうひとつ。ひとまとまりにしたのは良いのですが、今度はINDIRECT関数の中が整理できていません。「シート」!「セル」のつなぎ方では、INDIRECT関数の方が正しく認識できないのです。「シート」と「セル」を指定したデータであることを、INDIRECT関数に認識させます。=SUM(INDIRECT(D2&”!$D$3:$D$14′′))こうなりました。・INDIRECT関数でシートとセルのデータをまとめて指定する。”」で囲んで文字データにして、シートのデータと「&」でつなぐ=SUM(INDIRECT(D2&”!$D$3:$D$14“))これでOKです。数式をコピーするだけでシート名が、上にあるセルのデータ(201501)に変わるので、いちいちシート名を入力する必要がなくなります。シートが、何10枚増えても、コピーするだけで、シート名の入力ができるのです。まとめExcel上の場所を指示する時の基本的なルール、「階層構造」をおさえておきましょう。その上で、INDIRECT関数を使えば、別シートのセルの指定がスムーズになります。<おまけ>今回触れた、Excelの「階層構造」は、マクロでも重要な考え方ですので、覚えておいて損はありません。どの列に何のデータが入力されているかが分からなくなって不便です。VLOOKUP関数で複数の表からデータを抽出する方法ですExcelで作成された表の中から、条件に合うデータを抽出するのに便利なのが、VLOOKUP関数。複数の表からデータを抽出する方法を考えます。仕事でExcelを使っていると、だんだんそのような気持ちが強くなってきます。(前編)「最強の経理実務Excel教本」が発売になりました。僕にとって初めての出版だったのですが、完成までにはかなり時間がかかってしまいました。今回の経験を通じて感じたことや反省すべき点を、出版までの過程を振り返りながら書いてみます。【ピボットテーブル】数値の集計には関数よりもピボットテーブルを優先して使おう実務でExcelを使う際に、最もよく行う作業が数値の集計です。関数と機能も組み合わせて幅広く使いましょうExcelファイルは1人で作業する際にも使いますが、一つのファイルをシェアしながら使うこともあります。Excel上で複数の人にデータ入力してもらうのに備えておくと便利なのがドロップダウンリストです。東上野で税理士事務所タカジムを経営しています。公認会計士として監査法人に勤務し、上場企業の監査を担当。側面から会社の「利益」と「お金」を守るためのサポートしています。おっと、それから、監査、経理の現場でExcelを上手く使えず悔しい思いをした経験から(今でも当時の感情が蘇るくらい悔しかったみたいですよ)、”実務で使えるExcel”を伝えることにも力を入れています。東上野で税理士事務所タカジムを経営しています。公認会計士として監査法人に勤務し、上場企業の監査を担当。側面から会社の「利益」と「お金」を守るためのサポートしています。おっと、それから、監査、経理の現場でExcelを上手く使えず悔しい思いをした経験から(今でも当時の感情が蘇るくらい悔しかったみたいですよ)、”実務で使えるExcel”を伝えることにも力を入れています。lp=this.data.ListPrice,la=Number(lp.Amount),os=this.data.OfferSummary||{},sp=os.LowestNewPrice||{},sa=Number(sp.Amount);return(!isNaN(sa)&&saタイトルとURLをコピーしました。