Excelで計算式を作っておくと、元になる値が間違っていても、その値を書き換えるだけですべての計算結果が変わる「再計算」の機能があります。これは、Excelの最も優れている点なのですが、間違えたときに役に立つ以上に、効果的な使い方があります。
元の値が変わることで、すべての結果が書き変わるこの再計算機能をどう活用していくか、それが、効率化のカギになるのです。
再計算機能の基本
次のような計算書を作成しておきます。
D2からD9のセル範囲にはそれぞれの行の小計額として、単価×数量の計算式が入っています。
セルC10とD10には数量と小計額の合計の計算式としてその上の合計のSUM関数が入力されています。
8行目の「ゲイシャ」の単価が32ではなく、92が正しかったので、セルB8を書き換えると、書き換えて確定した瞬間、ゲイシャの小計額であるセルD8の計算の結果が変わります。それと同時にセルD10の小計額の合計値の計算の結果も変わります。
このように、元のデータが変わったとたんに自動計算してくれる機能を「再計算」と呼びます。
この機能はとても優秀で、ExcelがExcelらしく動作している計算書を作るうえでは欠かせない機能です。この再計算機能を有効に使うようにExcelを使うことが本来のExcelの使い方です。
上記のように、間違いがあっても簡単に正せる、というのはそれだけでもすごいことなのですが、Excelを活用するというのはもっと効果的に使うことを指します。
次の計算書では、販売数量を消しています。
そして12月の販売数量一覧表があったとします。この一覧の商品の並び順は、上の計算書と同じです。これが重要。
この販売数量のセル範囲をコピーし、計算書の販売数量のセル範囲に貼り付けたら、それぞれの商品の小計額が計算され、数量と金額の合計が計算された12月の計算書が、一瞬で完成します。
このような使い方が、再計算機能を活用する真骨頂です。これが真のExcelの使い方だと言えます。
簡易的な計算ボックスとして使う
例えば1か月だけ、軽作業をしてもらうアルバイトを雇う場合、次のような計算書を作成し、全体で人件費がどのくらいか試算すると思います。
セルC1からアルバイトの人数、時給、1日の労働時間、1月あたりの労働日数があります。セルC6に以上をすべて乗算するためにPRODUCT関数を使って最終的な人件費を計算しています。
試算は、条件を変えながらいくらになるか確認していきます。条件の値を変えれば、最終的な人件費もすぐにわかります。
どんな風に変わっていくかを全体的に確認したい場合は、一覧表にしてみることになるでしょう。例えば時給とアルバイト人数をある程度変えたときに、それぞれどのくらいになるかというのは、縦に時給、横にアルバイト人数を配置した次のような表を作成するとわかりやすいですね。
この表の条件、時給「1,200」、アルバイト人数「5」だとして、先ほどの一覧表に入力して、出てきた答えを、そのセルに値として貼り付けしていって、一覧表をすべて埋めればいいでしょう。
条件を変えてそれぞれの答えを求めるためには、計算式の元はセル参照にしなければいけません。
このように、計算書を作っておいて、その値を値で貼り付けていくことで、ひとつひとつ計算式を作成するよりは簡単に試算一覧表を作成できます。
この条件を変えて計算できる仕組みを、僕は「計算ボックス」と呼んでいます。
しかし、この方法でこれだけ大きな表であれば、時間もかかりそうです。
Excelにはこのような繰り返し計算を簡単にする「データテーブル」機能が「データ」タブの「What-If分析」の中にあります。便利な使い方なので紹介します。
使い方は、次の通りです。
まず、試算一覧表の変化させたい縦と横のセル範囲の交差するセルに、「=」に続けて、試算表の最終結果のセル参照を入力します。
今回の場合はセルF3に「=C6」という式を入れます。
そして選択範囲は、その計算式が入ったところを先頭として、一覧表の変化させる値の入力されている範囲です。今回はF3からJ10のセル範囲です。
ここで、「What-If分析」の中の「データテーブル」をクリックします。
「行の代入セル」には、一覧表の中の行方向で変化するものを、どんどん入れ替えたいセルを指定します。一覧表の行方向では時給が変化しますから、今回はセルC2です。
「列の代入セル」には、一覧表の中の列方向で変化するものを、どんどん入れ替えたいセルを指定します。一覧表の列方向では時給が変化しますから、今回はセルC3です。
OKボタンをクリックすると、結果がすべて一覧表に入ります。
このデータテーブルは計算ボックスの使い方をするから初めてできます。直接値を計算式に入力した計算にするのではなく、セル参照を使う計算式だからできることです。
データテーブル機能は縦横の２つしか計算できないので、アルバイトの人数、時給、1日の労働時間、1月あたりの労働日数の４つを一気に変更して計算はできません。まずは代表値の２つを選んで試算してみるのがいいと思います。
ちなみに「データテーブル」は「テーブル」とは全く違う機能です。「テーブル」は以下のような「テーブルとして書式設定」で設定するものを指します。区別しておきましょう。
元のデータを集計・分析する
データの種類とは2通りあって、元のデータと、それを見やすくしたデータです。
元データから作成した、見やすくデータのことを「レポート」と呼びます。
大きな元データの塊から、レポートにするという作業は、「連結」「抽出」「グループ化」「集計」という3つの作業でできます。
これらはExcelでは、関数や集計機能を使って実現できます。特に関数は計算ですから、再計算ができる計算書を作ればいろいろな条件でのレポートを次々と作成できます。
次の表は元データです。元データは、僕は必ずテーブルにしています。計算式が作りやすいからです。
上のデータを元に、販売方法別に、どの商品コードで、どの焙煎方法が売れているのかをレポートしたい場合は、次のような一覧表を作成し、SUMIFS関数でそれぞれの条件の数量の合計値を計算しています。セルC5の計算式は次の通りです。この計算式はスピル機能を使っているのでExcel2021または365で動作します。それ以前のExcelだと絶対参照を考える必要があります。
=SUMIFS(販売[数量],販売[販売方法],C2,販売[商品コード],C4:J4,販売[焙煎方法],B5:B8)
テーブルにしているので計算式もシンプルです。
とはいうものの計算式はちょっと複雑なので、難しければそれは置いておいてください。今は計算式を作ることによってできる再計算のメリットを理解しましょう。
この計算表すべてを、下に2つ丸ごとコピーし、それぞれの販売方法を変更するだけで、それぞれの表の計算結果が変わります。
このように、計算の仕組みを作って、それを丸ごとコピーして、一つの条件を変えるだけで、様々な条件の一覧表が作成されるという、とんでもない効率化も可能になります。
ダッシュボードとして使う
上記のレポートを究極に考えていくと、毎月資料を作成するのがとても大変な月次会議で使う資料を一瞬で作ることも可能になってきます。
次の計算書は、2023年11月の売り上げ分析のレポートで、このようにグラフや表が混在すると「ダッシュボード」と呼ぶようになります。
このダッシュボードでは、年と月だけは指定しますが、値を入力しEnterで確定した瞬間、その月の売り上げの状況が一発でわかるように、売上金額や粗利率の計算書、商品別の売上金額多い順に並んだ表が自動で計算されます。自動計算はセルの値だけではなく、グラフでもできることなので、商品ごとの売上個数と売上金額のグラフも自動で作成されます。
いつもは2日かかっていた月次報告書の作成が一瞬で出来上がります。
この仕組みを作成するには、スピル機能やその付随関数、SUMIFS関数などの集計関数、VLOOKUP関数などの行列関数、日付の関数を駆使し、計算途中をほかシートに計算することで実現します。
少し難しいかもしれませんが、Excelに慣れると、このレベルのことが1時間程度で作れるようになります。
まとめ
Excelは計算の手間を省き、効率的な業務をサポートする優れたツールです。
再計算機能を活かすことで、毎回、手動計算をする必要がなくなります。また、計算式の作成に費やす時間を大幅に削減できます。
「計算ボックス」や「データテーブル」を駆使することで、初心者でも手軽に条件の違いを比較できる一覧表を作成できます。
Excelはただの数値計算だけでなく、データの見やすいレポート作成やダッシュボード構築にも役立ちます。
Excelを使うときに大事なのは、手動計算の手間と比較してみることです。どれだけ便利になっているか実感することが、Excelを覚えることのゴールです。
Excelの柔軟性と効率性を理解し、自身の業務に積極的に応用していくことで、新たな作業スタイルにシフトしていけます。それはあなたの評価やキャリアにとって、とてもよい効果を与えることになります。
