こんにちは。
『自信を持って人におススメできる事！を書くブログ』をやっています、『ふみふみ』と申します。
今日はですね。仕事でも良くやることが多いんですが、Excelのマトリックス一覧表をクロスリストに集計する方法！について書こうと思います。
ここで言うマトリックス一覧とは、何種類かの情報が一覧としてずら～っと並んだ一覧の事です。↓こうゆうやつです。
↓これをこのようなクロスリストに集計します。
↓月別集計も出来ます。(最後の方で説明します)
所要時間としては、データ件数に関わらず１０～15分くらいです。方法としては、数式のみを使って集計する方法1と、ピボットテーブル機能を使用して集計する方法２が有ります。手軽さで言うと方法２の方が簡単ですが、一応両方書いていこうと思います！
このブログの説明動画です
方法１：Excelの数式のみでクロスリストを作成する！
まずは数式のみで集計する方法１です！
エクセルのマトリックス一覧に見出しセルを追加する
下記のように、マトリックス一覧に見出しセルを追加し、クロスさせたい要素を『＆』数式で合体させてください。後程集計で使用します。
入力する内容は手打ちでOKです。
↓このリストの場合の入力例(品番と日付を合体させてます)
=C4&D4
あとは、それをリストの一番下までコピー＆ペーストします。
すると集計のために必要なマトリックスデータが完成します。
日付数値が文字化けしてますが、クロス集計は問題なく出来たのでこのまま行きます。
ここまで出来たら、このSheetの名前を仮にマトリックスとしておきます。次に、集計のためのクロスリストを別のSheetに作成します。
クロスリストを作成～集計
出力したいレイアウトのクロスリストを作成します。
Sheetの名前は、わかりやすいようにクロスリストとしておきます。
次に、クロスリスト集計のための数式を各セルに入力します。
クロスリストの一番左上のセルを選択した状態で、ホームタブの右上にあるオートSUM→その他の関数と進みます。
次に数式SUMIFを選択します。SUMIFが見つからない場合は、下図のようにSUMIFと入力して検索開始をクリックすると出てくるはずです。
するとSUMIFの入力画面になります。
ここではそれぞれ、以下のように入力します。(このリストの場合は、です。)
◆範囲：先ほど作成した見出しセルの一番上から下までを絶対参照
マトリックス!$B$4:$B$107
◆検索条件：ここがミソ！後程説明します。
$B5&C$4
◆合計範囲：マトリックスの数値セルの一番上から下までを絶対参照
マトリックス!$E$4:$E$107
↓検索条件について説明
詳しい説明はここでは端折ります。詳細は記事上の説明動画を見てくださいm(__)m
品番のセルであるB5セルを列だけ絶対参照の$B5で、
日付のセルであるC4セルを行だけ絶対参照のC$4で、
でそれらを先ほども出てきた&で合体させてます。
あとはこのセルを、リスト全体にコピー&ペーストすると、クロスリストの完成です。
とても簡単にクロスリストを作成する事が出来ました！！
SUMIFで集計するので、マトリックス内に同じ日付で同じ品番の物が複数あったとしても、ちゃんと集計出来ます。
数値ゼロが表示されるのが嫌な場合は、条件付き書式で、セルの数値がゼロの場合は文字色が白くなるように設定すれば、ゼロが見えなくなります。
また、リスト全体を値だけ貼り付けし、0→ブランクに置き換えする方法も有ります。
リスト全体をコピーし、右クリックメニューの形式を選択して貼り付けから、値だけ貼り付けを選択。
その後、ホームタブ右上の検索と選択→置換を選択
検索する文字列に0を入力→セル内容が完全に同一であるものを検索するにチェック→すべて置き換えをクリック。これでゼロは消えます。
以上でクロスリストが完成します。とても簡単です！
クロスリストが完成したら必ずしたいチェック！
この手のデータ集計が『終わった～～( ｀ー´)ノ』って時に必ずしておきたいチェックをご紹介します。
このてのデータ編集が終わった後にとても効果的なチェック方法なので、覚えておいていただけると幸いです。
それは、『編集前後のデータ件数と合計数値が同じである事を確認する』って事です。
↓マトリックス
↓クロスリスト
このリストでは、マトリックスで同じ品番同じ日付のデータがクロスリストでは集計されてしまっているので、データの個数は一致しませんが、合計数はしっかり一致しています。基本、データは編集しても数値が変わるはずはないので、合計数は必ず一致する。一致していない場合は何かミスが有るという事になります。
方法２：超簡単！！ピボットテーブルを使ってクロスリストを作成する
次に、Excelのピボットテーブルという機能を使ってクロスリストを作成する方法です。(これがとても簡単です。)
マトリックスのリスト全体を選択している状態で、挿入タブのピボットグラフ→ピボットグラフとテーブルを選択します。
↓このようなメニューが出るのでOKを押します。
すると下記のような画面になります。
↓赤枠の、品番・日付・数量(それぞれの要素)にチェックを入れ、日付を『凡例(系列)』の個所に移動(ドラック&ドロップ)させます。
すると、このような状態になります。
なんとびっくりΣ(￣□￣|||)クロスリストが完成しています！！！
あとは、全体をコピー&値のみ貼り付けで別のSheetに貼り付けし、見出しや罫線、セルの色等を整えると、クロスリストが完成します！
業務上データ集計をする機会が多い方には、とてもおススメ出来る方法となります( ｀ー´)ノ
月別集計をしたい場合はmonth関数を使おう
扱っているデータが年/月/日の形なのだが、俺は日付別じゃなく月別集計がしたいんだぜっ(/・ω・)/！って時に使える方法をご紹介します。
それはとても簡単です。マトリックス一覧リストを、month関数を使い年/月/日のデータを月のデータに変えてから集計すれば良いだけです。
このリストの場合、日付の横に月という列を作り、以下数式を入力します。
=month(d4)
※月列の書式は標準か数値に設定しておきましょう。(日付だと文字化けします)
以上、クロスリスト作成の方法をご紹介させて頂きました(*’▽’)
↓これ、ポチって頂けますと、とてもうれしいです。
