PC・デジカメ・携帯電話

エクセル技!マトリックス一覧を10分でクロスリストに集計する方法!!

こんにちは。

『自信を持って人におススメできる事!を書くブログ』をやっています、『ふみふみ』と申します。

今日はですね。仕事でも良くやることが多いんですが、Excelのマトリックス一覧表をクロスリストに集計する方法!について書こうと思います。

ここで言うマトリックス一覧とは、何種類かの情報が一覧としてずら~っと並んだ一覧の事です。↓こうゆうやつです。

↓これをこのようなクロスリストに集計します。

↓月別集計も出来ます。(最後の方で説明します)

所要時間としては、データ件数に関わらず10~15分くらいです。方法としては、数式のみを使って集計する方法1と、ピボットテーブル機能を使用して集計する方法2が有ります。手軽さで言うと方法2の方が簡単ですが、一応両方書いていこうと思います!

このブログの説明動画です

方法1:Excelの数式のみでクロスリストを作成する!

まずは数式のみで集計する方法1です!

エクセルのマトリックス一覧に見出しセルを追加する

下記のように、マトリックス一覧に見出しセルを追加し、クロスさせたい要素を『&』数式で合体させてください。後程集計で使用します。

入力する内容は手打ちで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を入力→セル内容が完全に同一であるものを検索するにチェック→すべて置き換えをクリック。これでゼロは消えます。

以上でクロスリストが完成します。とても簡単です!

クロスリストが完成したら必ずしたいチェック!

この手のデータ集計が『終わった~~( `ー´)ノ』って時に必ずしておきたいチェックをご紹介します。

このてのデータ編集が終わった後にとても効果的なチェック方法なので、覚えておいていただけると幸いです。

それは、『編集前後のデータ件数と合計数値が同じである事を確認する』って事です。

↓マトリックス

↓クロスリスト

このリストでは、マトリックスで同じ品番同じ日付のデータがクロスリストでは集計されてしまっているので、データの個数は一致しませんが、合計数はしっかり一致しています。基本、データは編集しても数値が変わるはずはないので、合計数は必ず一致する。一致していない場合は何かミスが有るという事になります。

方法2:超簡単!!ピボットテーブルを使ってクロスリストを作成する

次に、Excelのピボットテーブルという機能を使ってクロスリストを作成する方法です。(これがとても簡単です。)

マトリックスのリスト全体を選択している状態で、挿入タブのピボットグラフ→ピボットグラフとテーブルを選択します。

↓このようなメニューが出るのでOKを押します。

すると下記のような画面になります。

↓赤枠の、品番・日付・数量(それぞれの要素)にチェックを入れ、日付を『凡例(系列)』の個所に移動(ドラック&ドロップ)させます。

すると、このような状態になります。

なんとびっくりΣ( ̄□ ̄|||)クロスリストが完成しています!!!

あとは、全体をコピー&値のみ貼り付けで別のSheetに貼り付けし、見出しや罫線、セルの色等を整えると、クロスリストが完成します!

業務上データ集計をする機会が多い方には、とてもおススメ出来る方法となります( `ー´)ノ

月別集計をしたい場合はmonth関数を使おう

扱っているデータが年/月/日の形なのだが、俺は日付別じゃなく月別集計がしたいんだぜっ(/・ω・)/!って時に使える方法をご紹介します。

それはとても簡単です。マトリックス一覧リストを、month関数を使い年/月/日のデータを月のデータに変えてから集計すれば良いだけです。

このリストの場合、日付の横に月という列を作り、以下数式を入力します。

=month(d4)

※月列の書式は標準か数値に設定しておきましょう。(日付だと文字化けします)

以上、クロスリスト作成の方法をご紹介させて頂きました(*’▽’)

↓これ、ポチって頂けますと、とてもうれしいです。

にほんブログ村 その他生活ブログ 生活術・ライフハックへ
にほんブログ村 海外生活ブログへ
にほんブログ村

PVアクセスランキング にほんブログ村





COMMENT

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です