テーブルにすると関数作るの楽ね☆
一覧表はテーブルにするといろいろ楽ちんになるって話はこの記事でやりました。
もうちょっと深堀りして、関数を作るときにもめっちゃ楽って話をします。
計算式を作るセルと違うところのテーブルを参照した計算式
ほかのテーブルを元にした計算式を作るときは、そのテーブルの名前、項目名を意識するといい感じになります。
例えば、次のテーブルには「販売一覧」という名前ついています。「販売商品」と「販売価格」の2項目があります。わざとセルの列番号と行番号は書いてないですよ。
この「販売一覧」の中の「みかん」の販売金額の合計を求めてみたいと思います。販売商品項目での1条件の合計なので、SUMIF関数を使います。
まず、「=SUMIF(」まで入れます。
1つ目の「範囲」は何を探すかの範囲なので「販売商品」項目ですね。
まずはここで対象のテーブル名を手入力で入れます。
そうすると、テーブルのデータ範囲全体が点線で囲まれて色が付くんです。
ここで、半角で「[」を手入力します。
そうすると、テーブルの中の項目名だったり、何かを選べる一覧が表示されます。
今回はこの中の販売商品なので、「販売商品」をダブルクリックします。
そうすると、項目名が計算式に入ります。
ここで、「[」に対する閉じカッコ「]」を半角で手入力します。
そうすると、今度は指定したテーブルの指定した項目のデータ全部を点線で囲みます。
これで販売一覧テーブルの販売商品項目のデータを選んだことになります。
「,」を手入力して、今度は検索値です。
文字列のみかんなので、””で囲んでそのあとにも「,」を手入力します。
最後に、合計範囲です。
販売商品項目を入れたように、販売価格項目も指定します。
「)」で閉じて完成です。
計算式のあるセルと同じテーブルの中を指定する
今度は「販売一覧」テーブルの右に次のオレンジのテーブルを「集計表」という名前を付けて用意しました。
この集計表に上と同じように商品ごとの合計額を集計します。
さっきは、検索値を「みかん」と直接入力しましたが、今回は計算式のあるセルと同じテーブルの中にあるセルを検索値として使うのがポイントです。
SUMIF関数から、検索範囲として販売一覧テーブルの販売商品項目を選択し、「,」を入れるところまでは同じです。
ここで「[」を入力します。
そうすると、「この行」と2つある項目名が出てきます。
ここで選びたいのは「みかん」が入力されている計算式のセルと同じ行なので、「この行」をクリックします。項目名を選ぶとその項目のデータ範囲全部を指定してしまうので注意しましょう。
「この行」を選ぶと「@」が入ります。
今度はこの行のどの項目ですかって聞いてきます。今回は「商品」の項目ですので「商品」をダブルクリックします。
＠商品となります。
ここで「]」を入力すれば、このテーブルの同じ行の商品項目のみかんを選択したことになります。
みかんが赤枠で囲まれていますね。
あとは、さっきと同じ、販売一覧テーブルの販売価格を合計範囲として指定します。
Enterで確定すると、計算式がテーブルの中にあるので、オートフィルしなくても全部計算式が入ります。これもテーブルにするメリットですね。
もっと簡単な入力方法
これまでは、手入力でする方法を紹介しました。
例えば、テーブルがほかのシートにあったり、極端に横長の表で指定したいセルが計算式のセルから離れてスクロールしてるときって計算式を作りにくいんですけど、そういうときに手入力は役立ちます。
でもそうじゃないときは、普通のセルを計算式の参照で使うように、クリックやドラッグすると、自動でテーブルとしての範囲で入ってくれます。
指定しやすければ、普通にドラッグしたり、クリックしても構わないです。
だから、たまにあるんですけど、テーブルとしてじゃないセル範囲で指定したいときは、セル参照を手入力します。
でも、縦に長い表を上から下まで選択するのに、わざわざCtrlキーとShiftキーを押したまま下矢印キーを押して指定してるじゃないですか。元になるテーブルが計算式と別シートだとやれる方法なんですが、もっと簡単になるんですよ。
まず、計算の元になるテーブルを下にスクロールして、項目名が上に隠れるようになった状態で、そのテーブルをクリックすると、いつもはアルファベットで指定している列番号が項目名になってくれるんですよね。この状態にします。
計算式を作るシートで計算式を作っていって、テーブルの範囲を指定する直前まで作ります。
ここで、元データのテーブルのシートに切り替えて、列見出しにある指定したい項目名をクリックします。
そうすると、計算式にテーブルのその項目のデータ範囲が設定されるんです。
わざわざ長い範囲を選択せずとも、項目名をクリックしただけで指定できるので簡単なんです。
この方法は、元になるテーブルと同じシートで計算式を作ろうとするとうまくいかないことが多いんですけど、なぜかうまくいく時もあって、何を選択してるのかExcelがはっきりわかる場合はうまくいくけど、そうじゃないときはうまくいかないのかなぁって思います。
でもテーブルって1シートに1つで作られていることが多いので、この方法は覚えておくとたくさん楽ができそうです。
この方法は、Web版Excelだとできないです。
横コピーに気を付けて
テーブルを元にした計算式にしておくメリットって、一番は、絶対参照に意識しなくてもなってるってとこだと思うんですよ。そろそろ絶対参照って意識しないExcelの使い方ってしてかなきゃってさとうせんせいも言ってます。
でも、その絶対参照がおかしくなる時があるんです。
縦方向のオートフィルでは、同じテーブルの参照になります。勝手にずれたりしません。
でも横方向のオートフィルだと下の図のように隣の項目にずれるんです。
それでいいときもあるんですけど、SUMIF関数とかは検索範囲は同じ項目を検索範囲にするので、勝手にずれられると困るんですよね。
不思議なことにオートフィルだとこうなって、コピーして貼り付けだと同じ項目の範囲のまま計算式がコピーされます。ほんと不思議。っていうかなんでオートフィルだとずれるんだろ？
まとめ
テーブルのセルやセル範囲を計算式で使うとき、テーブルじゃないセルを範囲選択するより楽ですよって話をしました。
クリックでもテーブルの範囲になるんですけど、どんな状態でもそれができるわけじゃないので、全部に対応できるのは手入力です。
でも、慣れれば簡単なんですけど、慣れないうちは結構大変かもしれません。
だから、実践しながら慣れるのもいいんですけど、この手入力だけは、1つの計算式で繰り返し練習したほうがいいかもしれないです。
あと、これで作った計算式は、横方向のオートフィルに弱いってことも覚えておいてくださいね！！