取引記録を扱う場合、未払い分と支払済みの金額を追跡する方法が必要になるでしょう。特定の顧客の未払い額、すべての顧客の未払い額、さらには支払いを受け取った回数などを、いつでも把握したい場合があります。取引記録を分析する方法は数多くありますが、この記事では、関数、小計機能、ピボットテーブルを使用した3つの取引照合方法をご紹介します。
取引は必ずしも金銭的なものである必要はありません。レンタル機器の追跡も同様に簡単です。しかし、取引が発生するたびに、最終的にはその取引の両端、つまり出金と入金を照合する必要があります。この記事では、専門的な売掛金管理プログラムのような内容は何もないため、 「照合」と「照合」という用語をあいまいに使用して、その内容を説明します。むしろ、組み込みツールを使用して取引記録をグループ化および分析し、一時的にその場で役立つ情報を提供する方法を紹介します。
Windows 10 64ビットシステムでExcel 2016を使用しています。この記事は以前のバージョンにも適用できますが、手順が異なります。関数とピボットテーブルはブラウザで簡単に使用できます。ブラウザでは既存のシートの小計が表示されますが、機能を適用することはできません。ご自身のデータを使用することも、デモ用の.xlsxファイルと.xlsファイルをダウンロードすることもできます。
参照: Excel の What-if ツールを使用してビジネス シナリオを分析する方法 (無料 PDF) (TechRepublic)
機能
図Aのデータは、データベースのテーブルで見られるものと非常によく似ていますが、典型的なスプレッドシートとは全く異なります。このレコード型構造には長所と短所があります。保存と分析には優れていますが、レポート作成にはそれほど適していません。
図A
このデータ セットは、従来のスプレッドシートではなく、データベース レコードに似ています。
現在、請求書の合計額と支払額の一覧表があり、未払い額と支払い額の記録も残っています。これだけです。現状の構造では、どの顧客が未払いなのかを簡単に特定することはできません。まず最初に思いつくのは、図Bに示すように、シンプルなSUMIF()関数を使って簡単なマトリックスを作成することでしょう。
=SUMIF($B$3:$B$15,"="&H$2,$D$3:$D$15)
残念ながら、データセットに行を追加しても、SUMIF() 関数は更新されません。簡単な解決策としては、データセットを Table オブジェクトに変換すると、新しいレコードを追加するたびにマトリックスが更新されます。
図B
単純な SUMIF() は各企業の合計を返します。
2つ目の問題は、SUMIF() の結果を日付で制限できないことです。この種類のデータでは日付による制限が当然期待されますが、マトリックス内で SUMIFS() を使用して日付を参照することもできますが、すぐに扱いにくくなってしまいます。
図Cに示すように、データセットに隣接する関数を簡単に追加することもできます。以下のいずれかの関数が機能します。
=SUMIFS($D$3:D$15,B$3:B$15,$B3)
=SUMIF($B$3:$B$15,"="&B3,$D$3:$D$15)
図C
データ セットに隣接する各企業の未払い金額を返します。
このルートは顧客（列B）ごとに合計しているため、結果が重複しています。それでも違いがはっきりしない場合は、もう1つ関数を試すことができます。
=ROUND(SUMIF($B$3:$B$15,"="&B3,$D$3:$D$15),2)=0
この関数は、図 Dに示すように、TRUE と FALSE を返します。支払い金額が同じ会社の請求額と一致する場合、関数は TRUE を返します。会社に未払い残高がある場合、関数は FALSE を返します。この関数は実際よりも複雑に見えます。合計が 0 の場合、未払い残高はないので、関数は TRUE を返します。合計が 0 でない場合は、未払い残高があり、関数は FALSE を返します。ROUND() 関数はこれを可能にします。この場合、ほぼエラー関数として機能します。残高が $0 の顧客は会社 D のみであることが簡単にわかります (この ROUND() 関数のトリックを提供してくれた Chandoo に感謝します)。ただし、TRUE/FALSE 値に基づいて条件付き書式を追加することで、さらに一歩進むことができます。
図D
顧客の未払い残高に基づいて TRUE と FALSE を返すことができます。
ROUND() 関数に基づく単純な条件付き書式ルールを実装するには、次の手順を実行します。
- データ セット B13:D15 を選択します。
- [ホーム] タブの [スタイル] グループで [条件付き書式] をクリックし、[新しいルール] を選択します。
- 上部のペインで、「数式を使用して、書式設定するセルを決定」を選択します。
- 下のパネルに数式を入力します
=$F3=TRUE - フォーマットボタンをクリックします。
- 必要に応じて「塗りつぶし」タブをクリックし、色を選択して「OK」を 2 回クリックして結果を確認します (図 D )。
小計
Excelの小計機能は、合計を求める際に便利な機能です。この機能を使用する場合、まずデータの並べ替えを行う必要があります。今回の場合は顧客ごとに合計を出したいので、まず顧客フィールドで並べ替えます。次に、データセット内の任意の場所をクリックして、以下の操作を続けます。
- [データ] タブをクリックし、[アウトライン] グループの [小計] をクリックします。
- 表示されるダイアログで、[各変更時] ドロップダウンから [顧客] を選択します。
- [関数の使用] ドロップダウンから [合計] を選択します。
- 「小計を追加」リストで注文合計を確認します (図 E )。
- [OK] をクリックすると、図 Fに示す結果が表示されます。
図E
小計設定を選択します。
図F
小計は SUMIF() マトリックスと同じ結果を返します。
小計機能はマトリックスと同じ結果を返しますが、小計とともに関連する詳細を表示します。ただし、動的ではありません。顧客データと日付データの小計を簡単に返すことも、少なくとも現状のままではできません。両方に対応するには初期設定が必要であり、それでも動的ではありません。小計は切り替え機能であり、データを追加するたびにオン/オフを切り替えることで結果を更新できます。
ピボットテーブル
Excelのピボットテーブル機能はおそらく最適な選択肢でしょう。データセットを最初に並べ替える必要はなく、日付でフィルタリングできます。また、ピボットテーブルをテーブルオブジェクトに基づいて作成することで、データセットを更新した後にピボットテーブルも更新されます。まず、データセットを次のようにテーブルに変換してみましょう。
- データ セット内の任意の場所をクリックし、[挿入] タブをクリックして、[表] グループの [表] をクリックします。
- 表示されるダイアログで、データ範囲を確認します（正しいはずです）。
- [テーブルにヘッダーを含める] オプションをオンにします。
- [OK]をクリックします。
これで、次のようにピボットテーブルを作成する準備が整いました。
- テーブル内の任意の場所をクリックします。
- [挿入] タブをクリックし、[テーブル] グループの [ピボットテーブル] をクリックします。
- 必要に応じて「新しいワークシート」をクリックし、「OK」をクリックします。
- ピボットテーブル フィールド ウィンドウを使用して、[顧客] を [行] リストにドラッグし、[注文合計] を [値] リストにドラッグします。
- 次に、「日付」を「行」リストにドラッグします。すると、Excelが自動的に「月」フィールドを追加します。「月」を「行」リストから「フィルター」リストにドラッグします（図G）。
図G
ピボットテーブルには詳細と小計が表示され、月ごとにフィルタリングできます。
この時点で、会社ごとにグループ化された詳細レコードと小計が作成されます。支払いと請求書を照合するわけではありませんが、未払い残高のある会社が一目でわかります。小計を月ごとに絞り込むには、フィルターを使用します。図Hに示すように、6月の詳細と合計には主に支払いが表示されています。「行ラベル」ドロップダウンからオプションを選択して、会社ごとに絞り込むこともできます。（データセットに借方と貸方の列が別々にある場合は、「行」リストに追加してください。）
図H
フィルターを使用して、月ごとにレコードを表示します。
ピボットテーブルは、テーブルへの変更、さらには新しいレコードも反映します。テーブルを更新したら、ピボットテーブル内をクリックし、「コンテキスト分析」タブをクリックして、「データ」グループの「更新」をクリックします。
Officeに関するご質問をお送りください
読者の質問には可能な限りお答えしますが、必ずお答えできるとは限りません。リクエストがない限り、ファイルは送信しないでください。添付ファイル付きの初回サポートリクエストは未読のまま削除されます。ご質問を明確にするために、データのスクリーンショットを送信していただくことも可能です。お問い合わせの際は、できるだけ具体的にご記入ください。例えば、「ワークブックのトラブルシューティングを行い、問題点を修正してください」といった質問では返答がないかもしれませんが、「この数式が期待どおりの結果を返さない理由を教えていただけますか？」といった質問であれば、回答が得られるかもしれません。ご使用のアプリとバージョンを明記してください。読者サポートにあたり、TechRepublicから時間や専門知識の報酬を受け取ることはありません。また、サポートした読者から料金を請求することもありません。お問い合わせは[email protected]までお願いいたします。
以下も参照:
- Word 2016で複数レベルの番号付き見出しを作成する方法（TechRepublic）
- Office Q&A: 受信した Outlook メッセージを色で識別する方法 (TechRepublic)
- 予算値を使ってExcelで意味のある情報を表示する3つの方法（TechRepublic）
- Excelで空白行を削除する5つの方法（TechRepublic）
- Excel Power Pivot を使用して複数のデータセットのデータを結合して分析する方法 (TechRepublic)
