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

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

Excelで身につけるべきこと

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

1 操作方法

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

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

2 整理

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

3 処理テクニック

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

便利な操作方法

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

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

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


■スポンサードリンク
------※この記事は、投稿日現在の状況、心境、法律に基づいて書いています。---------

■スポンサードリンク


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

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

これを商品コード別に、個数と金額を合計を合計します。
スクリーンショット 2014 01 18 9 31 38

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

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

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

スクリーンショット 2014 01 18 9 35 22

Enterを押します(OKをクリック)。
スクリーンショット 2014 01 18 9 36 07

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

スクリーンショット 2014 01 18 9 36 56

②元データの項目と、ピボットテーブルの項目は一致しています。
データの組み合わせを変えながら集計できるのが、ピボットテーブルです。
スクリーンショット 2014 01 18 9 40 15

■スポンサードリンク

ここでは金額を集計するので、まずは[金額]を[値]にドラックしましょう。
スクリーンショット 2014 01 18 9 42 06

すると、こう表示されます。
スクリーンショット 2014 01 18 9 42 33

この6266というのは、データの[金額]合計と一致します。
データで、[金額]の列を選択し、右下の[合計]をみてみましょう。6266となっています。
(わざわざSUMを使わなくてもこのように合計を出すことはできます。)
スクリーンショット 2014 01 18 9 43 29

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

スクリーンショット 2014 01 18 9 48 17

これで商品コードごとの金額が集計できます。
スクリーンショット 2014 01 18 9 48 59

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

スクリーンショット 2014 01 18 9 50 00

④次に個数を集計します。
[金額]を再度[値]にドラッグします。
スクリーンショット 2014 01 18 9 51 17

こうなりますが、あわてずに、
スクリーンショット 2014 01 18 9 52 07

右下の[合計/金額2]の▼をクリック→[値フィールドの書式設定]を選びます。
スクリーンショット 2014 01 18 9 51 49

このボックスで、[データの個数]を選びます。
スクリーンショット 2014 01 18 9 54 26

これで金額と個数が集計できました。
スクリーンショット 2014 01 18 9 55 17

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

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

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

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

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





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

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




■スポンサードリンク


■ブログEX-ITの購読 →feedlyを使って無料で読む
→Twtterで読む
Facebookで読む

1日1新 Instagram
井ノ上陽一のVALU
■著書
ひとり税理士のIT仕事術―ITに強くなれば、ひとり税理士の真価を発揮できる!!
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』