データは物語を語ります。データを読むことで物語を自分で発見することもできますが、意味が明確であればより早く理解できます。予算が良い例です。実際の経費項目が適切に管理されているかどうかを確認するために、頭の中で数値を足したり引いたりする時間はありません。むしろ、項目が予算を上回っているか下回っているかを明確に示す色や説明的な文字列を見たいものです。Excelには多くの選択肢があります。この記事では、予算データを使って有意義な情報を共有する3つの簡単な方法をご紹介します。
Windows 10 64ビットシステムでExcel 2016を使用していますが、これらの3つのソリューションは以前のバージョンでも使用できます。ご参考までに、デモ用の.xlsファイルと.xlsxファイルをダウンロードできます。ブラウザ版では条件付き書式を使用してルールを追加することはできませんが、既存の書式はブラウザで使用できます。また、ブラウザで小計機能を適用することはできませんが、小計はブラウザに表示されます。
参照: Excel の What-if ツールを使用してビジネス シナリオを分析する方法 (無料 PDF) (TechRepublic)
1. 表現
予算から実際の支出を差し引く簡単な式を追加するのが良い出発点です。値がプラスかマイナスかは一目見ただけで分かります。図Aに示す結果の値は、項目が予算を上回っているか下回っているか、そしてその差額がいくらかを示します。簡単な式を入力してください。
=C3-D3
セル E3 に入力し、残りのデータ セットにコピーします。
このようなシンプルなデータセットでは、この式で十分でしょう。しかし、より複雑なシートでは、より詳細な説明が必要になるかもしれません。
図A
シンプルな表現で十分です!
2. 条件付き書式
条件付き書式は簡単に適用でき、視覚的なインパクトも抜群です。通常の範囲（今回のように）やTableオブジェクトで使用できます。今回のルールは#1で追加した式をベースに構築しますが、この列がない場合でも同じロジックを使用できます。ただし、別の式が必要になります。次に、予算超過の項目を表示する条件付き書式を追加するには、以下の手順に従います。
- データ範囲（B3:D6）を選択します。（これを分散値や合計値まで拡張できます。）
- [ホーム] タブの [スタイル] グループで [条件付き書式] をクリックします。
- 表示されるドロップダウンから、[新しいルール] を選択します。
- 表示されるダイアログで、上部のペインの「数式を使用して、書式設定するセルを決定」をクリックします。
- ルールの説明を編集するセクションで、次の数式を入力します。
=$E3<0 - 「書式」ボタンをクリックし、必要に応じて「塗りつぶし」タブをクリックします。色を選択して「OK」をクリックします。図Bは、罫線と書式設定を示しています。
- 「OK」をクリックしてシートに戻ります。図Cは新しいルールが適用された状態を示しています。一目見ただけで、事務用品と広告費の予算が超過していることが分かります。
図B
この単純なルールにより、予算を超過している予算項目が強調表示されます。
図C
赤い項目は予算を超えています。
予算を下回る項目に関する2つ目のルールは必要ありません。ハイライト表示されていない項目は、予算を下回っているか、予算とほぼ同額であると想定されるためです。ただし、式 =E3>=0 を使用することで、このルールを追加できます。
差異値がなくても、2 つの予算項目をそれぞれのルールで比較することで同じ結果を得ることができます。
- =$D3>$C2 は予算を超過している項目を強調表示します。
- =$D3<=$C2 は、予算を下回っているか予算どおりの項目を強調表示します。
参照：Windows 10 April 2018 Update：インサイダーズガイド（無料PDF）（TechRepublic）
インプレースフィルタリング
条件付き書式では塗りつぶしの色が適用されるため、フィルターを使用できます。データ範囲内の任意の場所をクリックし、「データ」タブの「フィルター」をクリックします。強調表示された範囲内の任意の列（この場合は「Variance」列を除く）のフィルタードロップダウンをクリックします。ドロップダウンから「色でフィルター」を選択し、図Dに示すように「赤」（この例では唯一の色）を選択します。これにより、図Eに示すように、赤以外の行（予算項目）がフィルターされます。これで完了です（または完了していない可能性があります）。
図D
カラーフィルターを適用します。
図E
フィルタリングされた結果。
3. 数える
予算超過項目を明らかにするだけでも十分かもしれませんが、もう一つの可能性を考えてみましょう。#2 までの結果から、予算超過項目の数をどのように数えるでしょうか？図 F に示すように、Variance 列の値が 0 未満であるものをカウントする COUNT() 関数を挿入することができます。関数は以下のとおりです。
H3: =COUNTIF(E3:E6,"<" &0)
I3: =COUNTIF(E3:E6,">=" &0)
図F
簡単なカウント式を使用します。
単純なカウントで十分かもしれませんが、データセット自体のカウントを確認したい場合もあるでしょう。この場合、小計機能を検討することもできますが、サブグループを作成するための共通の値がありません。そのためには補助列が必要です。セルF3に次の式を入力し、残りのデータセットにコピーします（図Gを参照）。
=IFS(E3<0,"Over",E3>=0,"Okay")
図G
単純なカウントを返すには、IFS() を使用します。
この単純な式は、それぞれ説明的な文字列値「Over」と「Okay」を返します。共通の値が得られたので、次のようにSubtotalを使ってカウントできます。
- まず、チェック列で並べ替えます。小計では並べ替えが必要です。
- データ セット内の任意の場所をクリックします。
- [データ] タブをクリックし、[アウトライン] グループの [小計] をクリックします。
- 表示されるダイアログで、次の設定を確認します。[各変更時] にチェックを入れ、[関数の使用] に Count、[小計の追加先] にチェックを入れます (図 H )。
- [OK]をクリックすると、図 Iの結果が表示されます。
図H
小計の設定。
図I
小計にはデータ セット内のカウントが表示されます。
最後に…
最後の方法は、特にシートが込み入った場合は少し複雑ですが、試してみる価値はあります。しかし、3つの方法はすべてシンプルで、専門知識は必要ありません。この例は予算項目に焦点を当てていますが、シンプルな表現と書式設定は、全体像を理解するために少しの知識や明確な説明が必要なほぼすべてのシナリオに適用できます。
Officeに関するご質問をお送りください
読者の質問には可能な限りお答えしますが、必ずお答えできるとは限りません。リクエストがない限り、ファイルは送信しないでください。添付ファイル付きの初回サポートリクエストは未読のまま削除されます。ご質問を明確にするために、データのスクリーンショットを送信していただくことも可能です。お問い合わせの際は、できるだけ具体的にご記入ください。例えば、「ワークブックのトラブルシューティングを行い、問題点を修正してください」といった質問では返答がないかもしれませんが、「この数式が期待どおりの結果を返さない理由を教えていただけますか？」といった質問であれば、回答が得られるかもしれません。ご使用のアプリとバージョンを明記してください。読者サポートにあたり、TechRepublicから時間や専門知識の報酬を受け取ることはありません。また、サポートした読者から料金を請求することもありません。お問い合わせは[email protected]までお願いいたします。
以下も参照:
- Excelで空白行を削除する5つの方法（TechRepublic）
- Excel Power Pivot を使用して複数のデータセットのデータを結合して分析する方法 (TechRepublic)
- Word 2016 文書で見出しに番号を付ける方法 (TechRepublic)
- Office Q&A: Excel の貼り付けタスクで貼り付け先のセルの書式が上書きされないようにする方法 (TechRepublic)
- Office ドキュメントにチェックマークを挿入する 5 つの方法 (TechRepublic)
- Microsoft 365とは？Microsoftの最も重要なサブスクリプションバンドルについて解説（ZDNet）
