タグ別アーカイブ: 表

ピボットテーブル

ピボットテーブルはとても便利です!

様々な切り口でデータを集計することができます

読者の中には「ピボットテーブルを使わないと、毎月の会議は無事に乗りきれない」という方もいらっしゃると思います

実はピボットテーブルの機能は「データの集計」だけではありません

データ集計以外の機能も有効活用すれば、集計後の業務を大幅に改善しつつ、表自体もプレゼンにそのまま使える「見やすい表」にすることができます!

1.転記・編集作業の改善

 【改善前】集計データを別表に転記し、編集作業

 【改善後】集計データから会議用の資料をそのまま作成

集計データから表作成
新ピボットテーブルデザイン

   解説記事は⇒こちらから

2.グラフ作成作業の改善

 【改善前】集計データを再度編集してグラフを作成

 【改善後】集計データからそのままグラフを作成

   解説記事は⇒こちらから

3.複数資料の作成

 【改善前】集計データから部門A、B用など同じような資料を作成

 【改善後】スライサーで集計結果をフィルタリング

   解説記事は⇒こちらから

4.更新処理

 【改善前】データ更新の度にピボットテーブルを更新

 【改善後】データ更新時にピボットテーブルを自働更新

   解説記事は⇒こちらから

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ
にほんブログ村

テーブルを作成しよう

エクセルの表で何か作業をするときには、まずはテーブルを作成しましょう

 最新のエクセルバージョンでは、エクセルのあらゆる機能がテーブル機能を前提に作られているので、テーブル化をしておくのと、そうでないのとでは作業効率がかなり違ってきます

 例えば、下のGIF画像の「今日の日付」の列であれば、テーブル化をしておくと関数を1つ入力するだけで「列のすべてのセル」に関数が反映されます

 通常のエクセルの使い方だと処理はあくまで「セル単位」ですが、テーブル化を行うと「行列単位」になるので表内のデータの扱いがとても楽になります

テーブル化

まずは作業する表のどこかにカーソルを置き、Ctrl+Tを押しましょう

そして、下のGIF画像のように開いたダイアログボックスにて、”先頭行をテーブルの見出しとして使用する”にはチェックを入れたままにしておきましょう

表がテーブル化されると、右下に下の画像のようなマークが入ります

行列の自動拡大

テーブル化を行っておくと表の行列が自動拡大するのでとても便利です

行方向も、既に入力されている関数も含めて自動拡大します

名前の定義

表をテーブル化すると、自動的に名前が付きます

この自動で付いた名前は、画面上のテーブルデザインタブで分かり易い名前に変更することができます

ファイル内でテーブル化されている表はショートカットキー:Ctrl+F3を押すと、下の画像のように一覧で確認できます

関数入力での活用

①自動反映

記事の冒頭で紹介したように、表をテーブル化しておけば「関数の1つのセルへの入力」で列全体に関数が反映されます

②関数での参照

 表をテーブル化しておくと、テーブル外でテーブル内のデータを参照する時にとても楽です

参照範囲を入力することなく、クリック一つで済むようになります

テーブル全範囲

一度、テーブル外のセルで「=テーブル名」を入力してみてください

テーブルの全範囲が反映されます

 こちらの参照の仕組みは、関数の入力時にも活用できるのですが、テーブル名を入力しなくてもクリック一つで活用できます

テーブル範囲の左上にカーソルを置くと、矢印が右下に向きます

矢印が右下に向いている状態でエンターキーを押すと、全範囲が指定できます

*全範囲には見出しがついているケースとついていないケースの2つがあります。それぞれ区別して指定できます

テーブル列範囲

テーブル外のセルでテーブル内のセルを参照してみてください

すると下の画像のように数式に@が入っています

この@を数式から消してみて下さい

すると上の画像のように列参照になります

つまり、テーブル名+角括弧:[列名]で列参照になります

 関数の中で「テーブル名+角括弧:[列名]」を記入して列を参照する方法もありますが、クリック一つで参照する方法もあります

参照したい列の見出しの上あたりにカーソルをおくと、矢印が下に向きます

その状態でエンターキーを押すと列全体が参照できます

行列の挿入・削除及び通常範囲への変換

①挿入・削除

テーブルの上で右クリックすると、挿入・削除が表示されるので、こちらで操作を行います

②通常範囲への変換

テーブルを通常の範囲に戻すには、テーブルデザインタブにて下の画像の箇所をクリックします

ピボットテーブルとの連携

テーブルはそのままピボットテーブルの元データにすることができます

ピボットテーブルの元データの範囲が変更になった際には、本来は範囲の指定し直しが必要ですが、テーブル化をしておけば、自動的に範囲が拡大するので「範囲の指定し直し」は必要ありません

<まとめ>

今回は、テーブル機能の各種操作やそのメリットについて解説させて頂きました

エクセルは元々は表計算ソフトであり、大量のデータを扱うようには作成されていませんでした

ただ、このテーブル機能をフル活用すれば大量のデータも扱いやすいので、ぜひ有効に活用していきましょう

後、Power Queryを活用する際にもテーブルの概念はとても重要になります

 Power Queryの基本的な考え方は、エクセルをセルやシート単位ではなく、テーブル単位で扱う想定で作成されています

Power Queryを学習する際には、ぜひテーブルの機能には慣れておきましょう!


にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

魅せるピボットテーブル

 多くの読者の皆さんにとって、ピボットテーブルに期待する主な役割とは、データを集計することだと思います

 実は、ピボットテーブルには日本では、まだこれまで知られていない機能が沢山あり、十分、”魅せる表”も作成できるのです

 ピボットテーブルに付随する”スライサー”機能を使うと、魅せる表を作成するだけでなく、データを”見たい切り口”に集計し直しながら、データを魅せることができます

 

 単に、スライサーを作るだけなら、とても簡単です

 ピボットテーブル上にカーソルを置いた後、挿入タブから”スライサー”を押すだけです

 スライサー自体を利用するだけなら、とても簡単なのですが、スライサー自体も魅せたい場合には、様々なテクニックが必要です

 詳細を知りたい方は、ぜひ専門コーナーの記事を参照してください

知れば知るほど”スライサー”の奥の深さに気づくと思います!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

カーソルの位置を行列双方向でハイライトする

行列双方向で大きな表を扱っていると、今、どこを操作しているのかわからなくなり、迷子になることがあります

昨日、twitterで見つけた記事↓から、この迷子問題に対する大いなる示唆を得ました

https://hamachan.info/win7/Excel/active.html

こちらの記事を元にして、作成したのが下の表です

勤務スケジュール内で、カーソルを置いた行列がハイライトされつつ、勤務者の顔が表示され、ダイナミックに動きます

kinmusukejurue.gif

!今回のポイント

・条件付き書式の使い方

ⅰ)Cell関数とColumn関数、Row関数

Cell関数はセルの情報を返す関数です

このCell関数と列番号と行番号を返すColumn関数と、Row関数を組み合わせて条件付き書式の条件にします

ⅱ)OR条件

ハイライトしたいのは、カーソルを置いた行と列の位置です

こちらを「カーソルを置いた行」もしくは「カーソルを置いた列」

と解釈し直します

ⅰ)ⅱ)を組み合わせて設定した条件付き書式の画面が次の画像です

・イベントプロシージャ―

条件付き書式を設定しただけでは、うまくハイライトされません

カーソルの位置を変えた時に、更新処理が強制的に行われるようにイベントプロシージャ―を入れます

ⅰ)コードを表示

ⅱ)更新処理の組み入れ

下の「Worksheet」⇒「SelectionChange」から更新を行うコードを入力します

Application.ScreenUpdating = True

上のコードを入力することで、カーソルの位置を変更すると強制的に画面更新が行われ、条件付き書式もうまく稼働します

・画像のダイナミック表示

更に、2つの仕掛けを付け加えて”選択したセルにある名前”と対応する画像を表示できるようにします

こちらは、名前機能とカメラ機能を3段階で活用します

セル内の名前⇒別シートにコピー⇒別シートをカメラで参照

1.別シート(計算シート)に選択したセルの内容をコピーする

条件付き書式を強制的に稼働させるために作成したイベントプロシージャ―内に以下のコードを入れます

Worksheets(“計算シート”).Range(“C3”).Value = Target.Value

すると、計算シートにカーソルを置いたセルにある内容がコピーされます

以下は、実際に設定したコードです

複数セルが選択されると、条件付き書式がうまく動かない為、制御を入れています

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
If Target.Count > 1 Then
MsgBox “複数のセルは選択できません!”, vbCritical
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End If

Worksheets(“計算シート”).Range(“C3”).Value = Target.Value

End Sub

2.名前とカメラ機能を組み合わせて画像をダイナミック表示する

①下のような表を作成し、各画像が入ったセルに「人の名前」と同じ名前(名前管理)をつける

キャプチャ0221

こちらの名前を作成する処理は、以下の画面で簡単に設定できます

「選択範囲から作成」をクリックし、下の画像の「チェック」の箇所で設定します(上の表の名前の列が、左端列になるようにします)

②名前の管理画面にて、計算シートに入力された名前を参照する名前(名前管理)を設定する

こちらはINDIRECT関数を使用します!

③カメラ機能で②の名前を参照する(数式バーにて)

カメラ機能が参照しているセルは数式バーに表示されます

数式バーを②で作成した名前を組み入れます

カメラ機能の詳細については以下を参照してください

カメラ機能

INDIRECT関数については以下を参照してください

https://youtu.be/YlGjt7vpVAI

画像変える技術については、こちらもぜひご参照ください

https://analytic-vba.com/%e4%bd%9c%e6%88%90%e6%bc%94%e7%bf%92/post-1440/

以上です。ちなみに画像は以下より拝借させていただきました

http://kara1.blog92.fc2.com/blog-category-9.html

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

にほんブログ村