This article is a Private article. Only a writer and users who know the URL can access it.
Please change open range to public in publish setting if you want to share this article with other users.

More than 1 year has passed since last update.

Excel学習【eラーニング】

Posted at 2023-09-17

Excel学習【eラーニング】

参考サイト： eラーニングシステム L-TEMP
ピボットテーブル・データベース・マクロなど、
今まで何となく避けてきた分野をきちんと学ぶことにした。

1. 基本知識

ExcelではファイルのことをBookと呼ぶ。
一番左上にあるのはクイックアクセスツールバーと呼ばれている。これは、よく使う操作のショートカットを登録することができる。
選択されているセルをアクティブセルと呼ぶ。
四則演算式で用いるは、+ - * /は演算と呼ぶ。

前月比を求める数式

前月比とは、前月に対する当月数値の伸びの割合のこと。
計算式は、当月の数値 / 前月の数値となる。
数式の再計算機能 = これはセル数値を修正すると、その値で再計算されますよ。ただそれだけ。
オートフィル機能 = これもわかるセルの右下にポインタを合わせると、+マークが出るので、これをダブルクリックまたはドラッグ&ドロップすれば、数式がコピーされる。
そのセルをダブルクリックすると、数式で指定されたセルが色分けされて表示される。

基礎関数

sum関数は分かるよ。さすがに。
関数名(引数1, 引数2...)
SUM(A1, B1)
基礎関数には5つくらいある。SUM COUNT AVERAGE MAX MIN
Σアイコンから使える
=SUM(A1:A8)連続したセルを指定する場合はコロンで区切る。
=SUM(A1,A4,A9)離れたセルを指定する場合、カンマで区切る。

相対参照と絶対参照

数式を入力する時のセルの参照方法には、相対参照と絶対参照と複合参照がある。
相対参照は、参照セル位置がコピー先のセル番地合わせて相対的に変更される参照方法である。
相対参照が一般的に使われるし、大抵の表計算ソフトでは、オートフィル機能を使う際に相対参照が適用され、行や列に合わせたセル番地の値が参照され、計算が行われるはずである。
対して、絶対参照は、参照するセル位置を固定して参照する方法である。
=B2/$B5$みたいに、$$でセルを囲むことで、絶対参照にできる。

参照形式の変更にはF4キーを使う。
F4キーの押す回数によって絶対参照の固定を列・行にする、とかを切り替えることができる。

売上構成比を求める

売上構成比とは、全ての売上額を100とした場合の商品個別の売上額が全体に占める割合のこと。
東京支店の売上額が全店売上合計に占める割合を求める場合、、、、
構成比のセルに、、=B2/B7と二つのセルを指定→次にF4キーを1回押すとすることで、=B2/$B$7となり、合計売上のセルを、列・行ともに固定した絶対参照の形で、計算を行うことができる。
これでオートフィル機能を使って、数式を下にコピーした場合でも、=B3/$B$7や=B4/$B$7や=B5/$B$7のように、全店売上合計額が固定された数式がコピーされるのだ！
数式はセルをダブルクリックすれば、上のfxバーで確認できる。
Escボタンを押せば、この数式確認表示は解除できる。

上記のように、絶対参照するべき計算を相対参照でおこなってしまうと、
参照されるべきセルがずれてしまい、計算結果が狂ってしまうか、エラーになってしまう。
ちなみに、よく見かけるエラー#DIV/0!は、0で割り算したことによる数式のエラー表示のこと。
このエラーの時、脇に出る❗️ボタンを押すと、エラーの内容や対処方法を確認することができます。

行や列の操作

列幅の単位は半角文字数幅、初期設定は8.38文字分である。
行の高さは初期設定が13.5ポイントである。
行の高さはデータのフォントサイズに応じて自動調整される。
いずれも、自分でドラッグで幅調整できる。
文字数の一番長い幅に応じた幅に自動調整される。ダブルクリックで。
複数の列（縦）の幅を一度に調整したい場合は、列のA~のところを範囲選択し、そのいずれかの幅の境界線にマウスポインターを合わせてドラッグする。
行の高さもおんなじ感じ。

行や列の挿入・削除

行番号を選択し、上メニューの挿入アイコンから、挿入できる。
または、右クリックで列を削除や行の削除を選択しても同様のはず。

セルの書式設定1

表示設定と文字の書式設定について学ぶ
表示形式を変更するとデータをわかりやすく表示切る。
表示形式を変更してもセル中身のデータは削除されないので心配しなくてよい。
ホームタブ（上のメニュー表示）から、桁区切りカンマとか、パーセント表示の小数点以下の表示形式などのアイコンがあるので、そこで形式を変更できる。

文字の書式設定

Excelのデフォルトフォントは「游ゴシック」「11ポイント」である。
フォントの変更は、まあ、わかるので、ここでは割愛。
上のメニューから変更できる。

セルの書式設定2（罫線・セルの塗りつぶし）

罫線の設定
格子とか枠なしとか、まぁこの辺はわかる。。
一度に複数箇所に罫線を弾きたい場合、
上メニューの罫線アイコンのタブメニューからその他の罫線を選択し、セルの書式設定ダイアログボックスを開く。
これで、罫線の種類などをあらかじめ設定した上で、表全体とかを設定して罫線が作成できる。
塗りつぶしについて
これも大体わかるので、割愛。
表の列内の文字を中央に配置することについて。
これも大体わかるので、割愛。
セルの結合
任意の範囲のセルを選択
上メニューからセルを結合して中央揃えを選択
設定を解除するには、同じボタンをクリックすると、解除される。

印刷について

完成したデータの印刷について学習します。
初期設定は、用紙サイズA4、印刷の向きは縦のページ設定になっている。
変更するには、ページレイアウトタブからページ設定グループボタンから実行します。

印刷の向きを横にする。

画面上のタブページレイアウトから印刷の向きタブを選択し、横向きを選択する。
画面右下のツールバーのレンジ表示で表示倍率を変更することができるので、少し小さくして全体のレイアウト感を確認したりすることができる。
すると、印刷の向きが横になったことが確認できる。

印刷位置を水平方向に、ページ中央に変更する。

同じく画面上のタブページレイアウトから
水平方向や垂直方向の中央に配置して印刷できる。
ページレイアウトから余白を設定できる。
余白メニューの一番した、ユーザー設定の余白(A)をクリック。
すると、余白の四隅をカスタマイズできる。
また、ページ中央の水平にチェックを入れてOKすると、、、水平に配置される。

画面のイメージ確認から印刷の実行

印刷メニューをクリック
印刷プレビューが右側に表示される。
問題なければ印刷を実行する。
部数などをここで、指定できる。
プリンター接続設定がされているかは確認しましょう。
上書き保存をしてファイルBookを閉じれば終了。

2. 関数

関数1と関数2があるが、関数2からでいいかな。
関数1は、基本的なSUMとかAVERAGEになるので、飛ばしてもいいかも。
いや、SUMIF関数とCOUNTIF関数やSUMIFS関数はよく知らないのでやっておこう。
次回は、関数1のSUMIF関数からやっていく❗️❗️

条件集計とは？

男女別の来店者数を集計
特定のエリアだけの売上合計
平均売上を集計する
といった、条件をつけて、多角的な視点で集計を行うために、Excelの関数を使用する。

SUMIF関数

SUMIF関数は一つの条件を指定して、条件に一致するデータの合計を求めることができる。
書式は、=SUMIF(範囲,検索条件,[合計範囲])
引数の指定は、
- 範囲：条件を検索するセル範囲を指定します。空白と文字列は無視される。
- 検索条件：検索条件は直接文字列、式や数値で指定するか、条件が入力されたセルを指定します。
- 合計範囲：合計したい値が入力されたセル範囲を指定します。
曜日が月曜日という条件を指定し、条件に一致する売上金額の合計を求めるとするなら、、、、、
- 出力したいセルをクリック
- 上メニュータブから数式メニューを選択
- 数式の一覧からSUMIFを選択
- 引数を指定する。
  - 範囲には、曜日の列のセルを全て選択（例 B4:B17）
  - 検索条件には、月曜日と直接入力するか、出力したいセル月曜日カラムを選択（つまりSQLのWHEREK句みたいなもの。）（例 F5（"月曜日"））
  - 合計範囲には、SUMしたい対象、今回であれば売上金額の列のセルを範囲していする。（例 C4:C17）
これで出力したいセルに月曜日の売上金額の合計が出力できる。
数式は=SUMIF(B4:B17,F5,C4:C17)みたいな感じになる。
ここで、検索条件のセル(例題ではF5セル)のテキストを月曜日から火曜日に変更すると、自動的に数式の検索条件が変更され、F7セルには、火曜日の売上金額合計が出力されるようになる。
これ、めちゃ便利やな❗️

AVERAGEIF関数

これも、SUMIF関数と同じような容量で出来る。
最初の数式でAVERAGEIFを選択すればOK。

COUNTIF関数

=COUNTIF(範囲, 検索条件)
曜日が月曜日に該当する営業日数を求める。
数式メニューからその他の関数を選択
範囲は上記と同じ要領。（曜日カラムのセル）
検索条件も上記と同じ容量。（月曜日のセル）

SUMIFS関数

SUMIFSは、2つ以上の条件を指定して、その値の合計を求める関数
SQLコマンドで言うなら、SELECT *, FROM BOOKS, WHERE(), WHERE();みたいに、複数のAND検索みたいな感じかな。
SUMIFSの書式

条件範囲は最大１２７組指定可能である。
【例題】　2つの条件に合致する売上合計金額を求める。
- 条件1：日付= 4月4日以降（以降という条件は比較演算子を使用する）
- 条件2：商品名= 紳士スーツ
比較演算子の種類と適用例は以下の通り。

関数実装の手順は以下の通り
- 出力したいセルを選択（F8）
- 上メニューから数式タブを選択
- 数学・三角ボタンからSUMIFSを選択
- 合計対象範囲を選択（求めたいのは合計売上金額なので、金額カラムの全セルを選択）（C4:C25）
- 条件範囲1を選択（日付カラムのすべてのセルを選択）（A4:25）
- 条件1を選択（比較演算子を入力）（>=2023/4/4と直接入力するか、別のセルに比較演算子を用いた条件を入力したセルを予め用意し、そのセルをクリックするのと同じ。=>こっちの方が親切かもね。）
- 条件範囲2を選択（商品名カラムのすべてのセルを選択）（B4:B25）
- 条件2を選択（紳士スーツと直接入力するか、予め用意した条件を入力したセルを選択）

AVERAGEIFS関数

上記、SUMIFS関数と同じ要領で出来る。
最初の使用する関数でAVERAGEIFS関数を選択して、あとは大体同じ感じでやればOK。

COUNTIFS関数

COUNTIFSは2つ以上の条件を指定して、そのAND検索に合致した合計を求める関数
4月4日以降の紳士スーツの売れた数の合計みたいな感じで、求めたい時に使うのが良いだろう。
手順や流れはSUMIFS関数と大体同じ。

DSUM関数、DAVERAGE関数、DCOUNT関数

DSUM関数とは、データベース関数の一種であるのがDSUM関数である。
副業条件ん合致する条件について学習する。
DSUM関数は、予め別の領域に条件を入力し、その条件に一致する結果を出力する。
書式は以下の通り。

フィールドとは、DBでいうところのカラム＿列のことである。（フィールド名 = カラム名）
条件の指定方法は以下の画像の通り。

デーベースと同じフィール名の行を、どっか他の領域にコピーするのがポイント。
他の領域にコピーしたフィールドを条件に指定に利用する。

上記画像のように、AND条件を入れる際は、きちんと対応するフィールド（カラム）に条件となるワードを入力する。
金額カラムのレコードに紳士スーツと入力しても、おそらくエラーとなってしまうだろう。
AND条件の場合同じ行に条件のワードを入れるのがポイント！

２行目に紳士スーツ、３行目に20代と入力した場合は、OR検索となる❗️（これもポイント）

また、上記画像のように、AND条件とOR条件の組み合わせ検索もできる。

DBテーブル 売上金額一覧表（仮）
フィールド（求めたいもの）複合した条件に合致する売上金額合計
条件1:紳士スーツ AND 20代, OR 条件2:紳士スーツ AND 30代

もし、SQLで検索するのなら、こんな感じかなぁ？？？

select "売上金額"
from "売上金額一覧表（仮）"
where 商品名 = "紳士スーツ", 顧客年代 = "20代" or
where 商品名 = "紳士スーツ", 顧客年代 = "30代";

同様に、DAVERAGE関数やDCOUNT関数なども使える。
書式の設定方法は上記DSUM関数のやり方と要領は同じになる。
以上。

RANK.EQ関数

RANK.EQ関数は、数値の順位を求める関数である。
ランクイコール関数と読むらしい。
指定の範囲内での順位を求めることができる。
書式の設定は以下の通り。

手順は次の通り。
今回はテーブル得意先別、売上金額合計表を用いる。
各得意先ごとに自社の売上が何番なのかを出力する。

順位をつけたい対象のセルを選択
上のメニューから数式タブを選択
その他の関数を選択
統計を選択
RANK.EQを選択
数値に、昭和物産（仮）の順位カラムのセルを選択
参照に、売上金額カラムの全列を範囲選択する。
この参照に指定した範囲は、この後、数式を下にコピーする際も、同じ指定範囲条件で出力したいので、絶対参照にする。（F4キーで絶対参照にできる）
例】 B4:B10 →F4キー押下→ $B$4:$B$10みたいな感じになればOK。絶対参照に設定される。
順序には0と入力する（降順）数字の大きい順から小さい順に並ぶ。

3. ピボットテーブル

DB形式の表データを元に、行と列を自由に開店して、さまざまな角度からクロス集計表を作成できる。
集計結果を元にグラフを生成できる

ピボットテーブルの作成

挿入タブ→テーブルグループ→ピボットテーブルの挿入→実行します。
テーブル内の任意のセルをクリック→挿入タブ→ピボットテーブルの挿入→作成のダイアログボックスが表示される
テーブル範囲に「book」のテーブル名が自動的に選択されている。
新規ワークシートが選択されていることを確認
挿入
白紙のピボットテーブルが生成される。
フィールドが生成される。
ここに、以下のように列や行をカスタマイズして新しいテーブルを生成することができる。
- 行ラベル：4月・5月・6月
- 列ラベル：各支店名
- 値フィールド：売上金額
上記のようにカスタムして、売上月ごとの支店別売上表が生成された。
なお、データは、、、
数字は、昇順
漢字は、文字コード順
その他は、５十音巡

フィールドの解除

エリアセクションから、各ラベルの値をドラッグして取り除く。
分析〜アクション〜すべてクリア =　すべての値を解除して、ピボットテーブルの中身を白紙に戻せる。

フィールドのグループ化

行や列のフィールドを特定のグループ単位でまとめること。
フィールドは、四半期、男女、などのグループ化ができる。
売上を週単位でグループ化したいなら、、、
分析タブ〜グループ〜グループの選択〜月・日の選択を解除〜日数7を選択〜OK
売上が週単位でグループ化できた。

グループの解除

任意のセルを選択した状態〜グループ〜グループ化の解除 = 解除できる。

年齢を年代ごとにグループ化してみる

アンケート調査シートのテーブルを元に、
アンケート回答者の年齢別・住所別のアンケートが集計あれている。
年齢データをグループ化して、年代別のグループを作ってみる。
ピボットテーブルタ〜分析〜グループ化〜先頭の値を10（10代）にする。
単位は10歳間隔になるので、10のまま、OKをする。
他にも、エリア別にグループ化したりと、自由自在である！

ピボットテーブルの書式設定

フィールドの書式と、ピボットテーブル全体の書式がある。
フィールド書式= 表示形式の設定などが行える。
ピボットテーブルの書式= スタイルの設定が行えます。

売上の金額に3桁のカンマをつける。
上のメニューのフィールドの設定から書式設定ができる。
ピボットテーブルの書式
任意のセルを選択
デザインタブ〜その他〜ピボットスタイルの好みのスタイルを選択
これで、綺麗なデザインの表ができる。

データの更新

作成元のテーブルデータを変更、追加した場合。
ピボットテーブル側で、データの更新を実行をしないと、値が更新されない。
これ、面倒臭いな。
これをVBAでマクロを組んで、定期的に実行するようなcronを実装したりできそうな気がする、、、（知らんけど、、、2023/09/12時点）
まずは集計元のテーブルデータを変更する。
売上管理テーブルを選択、
任意のセルの値を変更。更新。
次に、ピボットテーブルのシートに切り替えると、、、
このままでは、元データを更新したのに、反映されていない。
なので、、、
分析タブ〜更新ボタン〜これだけでOk
これで、ピボットテーブルの情報が集計元の新しいデータを元に更新される。

ピボットグラフと条件付き書式

ピボットグラフと条件付き書式を使ったデータ分析について学びます。
ピボットテーブルの集計結果を元に作成されるグラフを、ピボットグラフと呼ぶ。
ピボットテーブルを変更すると、ピボットグラフも即座に反映される。
分析作業をスムーズに行えて便利である。
月別支店別売上表を例としてピボットグラフを作成していきましょう。
任意のセル〜ピボットテーブルツールの分析タブ〜ピボットグラフボタンをクリック〜ダイアログボックスが開く〜デザインはデフォルトの集合縦棒グラフになっていることを確認してOK〜これで完成
割と簡単にグラフ化できる。

ピボットグラフの行と列フィールドの入れ替え

行と列を入れ替えることで、視点を変えたグラフが生成できる。
ピボットグラフを選択〜ピボットグラフツールメニューのデザインタブを選択〜行列の切り替えボタンをクリック〜
これで簡単に視点を変えたグラフが生成できる。
なお、ピボットテーブルの右側のフィールドメニューのカラム名を選択してドラッグして移動させることでも、ピボットテーブルと合わせてピボットグラフも切り替えることができる。

条件付き書式の利用方法

ピボットグラフの書式と同様に集計地のデータを視覚化できる。
また、条件付きの書式にして、特定のデータを強調させる視覚化のカスタマイズも可能。
データバーを設定しましょう。
対象セルを選択〜ホームタブメニューから「条件付き書式」をクリック〜データバーを選択〜緑のデータバーを選択〜

カラースケールという条件付き書式では、数値の代償を比較して、セルを色分けできる。
例えば、「上位・下位ルール」という書式では、、上位１０項目、下位20%、平均より上、といった条件によってセルの色分けをやってくれる。

条件付き書式を解除するには、
「条件付き書式」〜「ルールのクリア」〜「すべての書式をクリア」などを選択すると、解除できる。

詳細データの表示

集計表から特徴的なデータを掘り下げて分析する方法をドリルダウン分析と呼ぶ。
東京支店の詳細データを表示してみましょう。
売上の多い、東京支店を分析して、その要因を分析してみましょう。
東京支店のセルをダブルクリック〜詳細データのダイアログボックスが開く〜商品名を選択しOK〜
すると東京支店の商品別の売上が表示され、階層状になる。
ここで、フィールドの展開と折り畳みを学ぶ
支店名の左側にある「＋」「-」ボタンをクリックすると、展開したり、折りたたんだりできる。
全部の階層を展開したい場合は、分析タブからフィールドの展開ができる。

集計値の明細行の抽出

特徴的なデータは、詳細データを別シートに表示させることができる。
東京支店の紳士スーツのみの6月どの売上を別シート表させてみよう。
対象のセルをダブルクリックする。これだけ。

レイアウトの変更

コンパクト形式、アウトライン形式、表形式、3つのレイアウトが用意されている。
ピボットテーブル　〜デザインタブ〜レポートのレイアウト〜表形式で表示をクリック
これでレイアウトが変更できる。

データの絞り込み

さまざまな切り口でデータを集計して分析する方法
レポートフィルターを使う
エリアフィールドを追加する
右側のメニューから、レポートフィルターエリアに、エリアをドラッグして配置する。

この生成したフィルターを使うと、カテゴリーだけを絞ったテーブルに変えることができる。
レポートフィルターの項目ごとにシートを作ることもできる。
西日本の売上sheetと東日本の売上sheetみたいにレポートを分類してテーブルを作成し直すことができる。（もちろん、元のシートは残された上で、追加されると言うことである。）

フィルター機能の利用

フィルター機能による絞り込みができる。
試しに、合計売上金額が「10,000,000」以上の支店のデータだけに絞り込んで売上集計を行ってみましょう。
ピボットテーブル、ピボットグラフ共に、フィールドやカラムに応じたフィルターが設定できる。いつでもフィルターは解除可能。
ピボットテーブルで使用できるフィルターには「値」「日付」「ラベル」といった種類でのフィルタリングが可能であり、さまざまな角度からデータを抽出し、分析が可能である。
例えば、、、