Excelコンサル事例ー操作方法・数値と個数をピボットテーブルで瞬時に集計ー

昨日行ったExcelコンサルで、お伝えしたことをまとめてみます。
約2時間ほど、事例をもとに解説しました。

 

Excelで身につけるべきこと

Excelで身につけるべきスキルは、大きく分けると次の3つです。
セミナーでも常々話しています。

1　操作方法

ショートカットキーを使った操作を覚えると、それだけでスピードが上がります。
一度身につければずっと使えますし、その積み重ねは大きいです。
後述します。

マウスが使いにくければ買いかえましょう。
おすすめはこれです。
大画面でも使いやすいですし、邪魔なコードもなく、マウスパッドも必要ありません。
ちょっと大きいのが難ですけどね。
PC付属のパッドが使いにくければ、無理して使わないようにしましょう。
私はMacではパッドを使いますが、VAIOではこのマウスを使っています。VAIOのパッドがあわなく、使いにくいからです。

LOGICOOL ワイヤレストラックボール M570t ロジクール 2013-08-02 売り上げランキング : 259 Amazon by カエレバ

2　整理

地味なのですが重要なのが整理。
・シート名、ファイル名をきちんとつける
・紛らわしいファイル、シートは作らない、削除する
・極力、シートやファイルをわけない。1枚で済むなら1枚で済ませる
・フォルダをつくりすぎない
・データに空白行を作らない
・右へ伸びていく表を作らない、原則は下に伸びていく表（縦長）。処理しやすくなります。

3　処理テクニック

・関数（IF、VLOOKUP、INT、SUM）
・ピボットテーブル
・グラフ
・マクロ
・オートフィルター

 

便利な操作方法

おすすめのショートカットキーは当ブログのこちらからダウンロードできます。
https://www.ex-it-blog.com/excel-shortcut/

操作方法で、昨日扱ったのは次のようなものでした。

・Ctrlを押しながらクリック→複数のセルなどを選択
・Ctrl＋「−」→削除。行を選択してからだと行削除
・Ctrl+Shift＋「;]→挿入。行を選択してからだと行挿入
・Ctrl＋N→新規のファイルを開く
・Ctrl＋W→ファイルを閉じる。
・Enter→「保存しますか？はい・いいえ・キャンセル」などが出たときに「はい」を選択できる。ESCなら「キャンセル」、Nなら「いいえ」
・Ctrl＋Shift＋L→オートフィルターの設定・解除
・セルの右下でカーソルが十字になったらダブルクリック→データの終わりまでコピー
・F2→セルを編集する
・F10→かな入力モードで、文字を入力してF10を押すと、半角英数字に変換。例）えｘｃえｌ（かなモードで「Excel」と入力）⇒F10を押す⇒excel→EXCEL→Excel→・・・・と順次変換）
・F12→名前をつけて保存
・Shiftを押しながら方向キー→セルを選択。選択するセルを広げることができる
・Ctrl＋Pageup（Pagedown）→シートの切替
・Ctrl＋Tab→Excelウィンドウ（ファイル）の切替

 

データを集計して表を作る事例＝ピボットテーブル

質問のあった事例の1つを紹介します。
こういったデータがあります。
実際には、横に60列、縦に200行ほどのデータでした。

これを商品コード別に、個数と金額を合計を合計します。

手作業でやると、コピー、貼り付けの繰り返しで非効率です。
商品コードごとに並べ替えて集計というのも手間がかかりますし、元のデータを変えてしまいます。

こういった場合には、迷わずピボットテーブルを使いましょう。

①データを選択して、[挿入]→[ピボットテーブル]を選び、

Enterを押します（OKをクリック）。

すると、新しいシートにこういったものができます。
これがピボットテーブルの枠です。
これから項目や数字を組み合わせていきます。

②元データの項目と、ピボットテーブルの項目は一致しています。
データの組み合わせを変えながら集計できるのが、ピボットテーブルです。

ここでは金額を集計するので、まずは[金額]を[値]にドラックしましょう。

すると、こう表示されます。

この6266というのは、データの[金額]合計と一致します。
データで、[金額]の列を選択し、右下の[合計]をみてみましょう。6266となっています。
（わざわざSUMを使わなくてもこのように合計を出すことはできます。）

 

③[商品コード]ごとに集計したいので、[商品コード]をマウスのドラッグで[行]へ持っていきます。

これで商品コードごとの金額が集計できます。

金額をカンマ区切り（桁区切り）にしたいなら、列を選択して、Ctrl＋Shift+1を押しましょう。

④次に個数を集計します。
[金額]を再度[値]にドラッグします。

こうなりますが、あわてずに、

右下の[合計/金額2]の▼をクリック→[値フィールドの書式設定]を選びます。

このボックスで、[データの個数]を選びます。

これで金額と個数が集計できました。

※最初に[金額]を集計するときに[個数]になる場合があります。このときも[値フィールドの書式設定]で設定してください。
※データを修正、削除したときはピボットテーブルを右クリック→[更新]をしてください。

ピボットテーブルについては次のカテゴリにまとめてあります。
EX-ITカテゴリ：ピボットテーブル

Excelの基礎はこちらです。
EX-ITカテゴリ：Excelで効率アップ

Mさん、ありがとうございました！
まずは、ショートカットキーを1日1つでも使っていきましょう。

Microsoft Office Excel 2013 [オンラインコード] [ダウンロード] (PC2台/1ライセンス) マイクロソフト 2013-02-07 売り上げランキング : 14 Amazon by カエレバ

サンプルをおいておきます
EX-ITサンプル　個数・金額集計ピボット.xlsx　

---

---

---

---

【編集後記】
今シーズンのトライアスロンをぼちぼち申込はじめています。
今のところ、新島、大島はエントリー完了。
その他、故郷の宮崎のほか、横浜（抽選）、仙台、天草、指宿、徳島あたりを考えています。
いきおいあまって徳之島もエントリーしましたが、出ると決めている宮島（厳島神社付近を泳ぐ）と重なっていることに気づき、なんとかキャンセル。
申込開始を待ちます(^^;)

【1日1新】
※詳細は→「1日1新」
・永田町・駅ナカで、Excelコンサル。
・フルーツカクテルティー　初体験
・ドトール豆乳ラテ　初体験