基礎的 IT セミナー 業務に役立つ表計算ソフトの関数の活用 Ⅲ 2021 年 1 月 9 日 23 日 30 日 株式会社フロムサーティ

基礎的

IT セミナー

業務に役立つ

表計算ソフトの関数の活用

Ⅲ

2021 年

1

月 9 日・23 日・30 日

－2－

ピボットテーブル

ピボットテーブル機能を使うと、表の項目を自由に配置し項目別の集計表

を簡単に作成、分析することができます。

１．ピボットテーブルの作成

（品名ごとの販売員別数量および値引後金額一覧表を作成）

手順：(1) ブック「3 週間売上表」を開き、シート「販売」を選択します。 (2) セル B12 から H52 を選択します。 (3)「挿入」リボンの「テーブル」グループから「 」ボタンをクリックします。 (4)「ピボットテーブルの作成」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽから以下の設定を行い「ＯＫ」ボタンを クリックします。 ﾃｰﾌﾞﾙまたは範囲を選択［テーブル/範囲］ ： 販売!$B$12：$H$52 ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙﾚﾎﾟｰﾄを配置する場所を選択してください。： 新規ワークシート

－3－

(5) 新しいシート「Sheet1」が作成されます。 (6) 以下のとおりフィールド名をドラッグし、 各項目を指定します。 行 ： 品名 列 ： 販売員 値 ： 数量 値引後金額 品名

－4－

(6) ［列ラベル］領域の「Σ値」を ［行ラベル］に移動します。

(6) 「合計/数量」と「合計/値引後金額」が行ラベルエリアに表示されます。

－5－

２．フィールドの編集（値の表示形式の変更）

手順：(1) 値ボックスの「合計/値引後金額」フィールド名の「▼」をクリックし、 「値フィールドの設定」を選択します。

－6－

(3)「セルの書式設定」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで以下の設定をし、「ＯＫ」ボタンをクリックします。 分類 ： 数値 桁区切り( , )を使用する ： ☑ (4)「値フィールドの設定」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで「ＯＫ」ボタンをクリックし、桁区切りスタイ ルになっていることを確認します。

－7－

スライサーを使ったデータの抽出

スライサーはピボットテーブルのデータを抽出するためのツールです。ボ

タンをクリックするだけで、簡単にそのデータのみを表示、集計すること

ができます。

１．データの絞り込み（販売員「今井」のデータのみを表示）

手順：(1) ピボットテーブル内の任意のセルをクリックします。 (2)「ピボットテーブルツール」－「分析」リボンの「フィルター」グループから 「 」ボタンをクリックします。 (3)「スライサーの挿入」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽの 「販売員」にチェックを入れ「ＯＫ」ボタンを クリックします。 (4) 指定した項目のスライサーが表示されます。

－8－

(5)「販売員」スライサーの「今井」をクリックし、選択した項目だけの値になっている ことを確認します。

－9－



参考

条件の削除

抽出した条件を解除するには、「 」をクリック します。

スライサーの削除

スライサーを選択し、「Delete」キーを押します。または、右クリックし 「”販売員”の削除」を選択します。 ＊フィルターのクリアを行わずにスライサーを削除すると、データが抽出された 状態のままになります。

フィルターボタンを使ったデータの抽出

抽出したいフィールド名の 右側「▼」をクリックし、 表示したいリストに☑を 入れ「ＯＫ」ボタンを クリックします。 (“スライサーの名前”)

－10－

ピボットテーブルのレイアウト

１．総計行の表示・非表示（行列の総計を非表示にする）

手順：(1)「販売員」スライサーの （フィルターのクリア）をクリックし、すべてのデータ が表示されるようにしましょう。 (2) ピボットテーブル内の任意のセル（A3～F21）をクリックします。 (3)「ピボットテーブルツール」－「デザイン」リボンの「レイアウト」グループから 「総計」ボタンをクリックし、「行と列の集計を行わない」を選択します。 (4) F 列と 20 行目から 21 行目の総計が非表示になったことを確認します。

－11－

２．空白行の挿入（分類間に空白行を挿入）

手順：(1) ピボットテーブル内の任意のセルをクリックします。 (2)「ピボットテーブルツール」－「デザイン」リボンの「レイアウト」グループから 「空白行」ボタンをクリックし、「アイテムの後ろに空行を入れる」を選択します。 (3)「品名」の間（8・12・16・20 行目）に空白行が挿入されたことを確認します。

－12－

３．レイアウトの変更（アウトライン形式に変更）

手順：(1) ピボットテーブル内の任意のセルをクリックします。 (2)「ピボットテーブルツール」－「デザイン」 リボンの「レイアウト」グループから 「レポートのレイアウト」ボタンをクリックし 「アウトライン形式で表示」を選択します。 (3) レイアウトが「コンパクト形式」から 「アウトライン形式」に変更されたことを 確認します。

－13－



参考

－14－

練習問題 7

ブック「練習⑦」を開き、「売上状況」表を作成しデータを分析しましょう。 ＜処理内容＞ １．シート「1-3 月」の表に必要な項目を追加しましょう。「得意先 CD」の右に「得意先名」「担 当者 CD」「担当者名」、「商品 CD」の右に「商品名」「単価」、「数量」の右に「金額」の項目を それぞれ追加します。 ①「得意先名」「担当者CD」の欄には、シート「得意先一覧」を参照したデータを表示するこ と。 ②「担当者名」の欄には、シート「担当者一覧」を参照したデータを表示すること。 ③「商品名」「単価」の欄には、シート「商品一覧」を参照したデータを表示すること。 ④「金額」の欄には、単価×数量を計算すること。 ２．入力したすべてのデータが表示できるようセル幅を調整し、追加したセルに罫線を引きまし ょう。 ３．「単価」および「金額」欄には、桁区切りスタイルを設定しましょう。 ４．シート「1-3 月」の表（セル A1～J72）をもとに新規ワークシートにピボットテーブルを 使用し、月ごとの担当者別売上集計表を作成しましょう。 行ラベル：担当者名 列ラベル：売上日 値：金額 ５．「合計/金額」の値フィールドの表示形式を、桁区切り(，)を使用した数値に設定しましょう。 ６．ピボットテーブルのレイアウトを「アウトライン形式」に変更しましょう。 ７．「練習⑦完成」というファイル名で保存しましょう。 ＜完成見本＞ ＊日付が自動的にグループ化され、月単位で表示されます。

－15－



参考

グループ化の解除

グループ化を解除したいセルを選択し、「ピボットテーブルツール」－「分析」 リボンの「グループ」グループから「 」をクリックします。

自動グループ化の無効

「ファイル」－「オプション」－「Excel のオプション」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽから 「データ」－「データオプション」の設定から 「ピボットテーブルで日付/時刻列の自動グループ化を無効にする」に「☑」を 付ける。 ＊フィルターのクリアを行わずにスライサーを削除すると、データが抽出された状態の ままになります。

フィルターボタンを使ったデータの抽出

抽出したいフィールド名の 右側「▼」をクリックし、 表示したいリストに☑を 入れ「ＯＫ」ボタンを クリックします。

－16－

ゴールシーク機能

目標の値を得るために逆算して代入すべき値を求めることができます。

予算内にパーティー費用を収めるために、コース料理の金額を調整します。

(1) パーティーの招待客数

30 人

(2) コース料理

6,000 円

(3) 会場のレンタル料

50,000 円

(4) 消費税

10％

(5) 予算上限額

200,000 円

１．目標値の算出（予算内にすべての経費を収めるために、

コース料金をいくらにすればよいか）

手順：(1) ブック「4 パーティー予算」を開き、シート「予算調整」を選択します。 (2)［データ］リボン－「データツール」グループから「 」ボタンを クリックし、「ゴールシーク」を選択します。

－17－

(3)「ゴールシーク」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽで以下の設定を行い「ＯＫ」ボタンをクリックします。 数式入力セル ： $C$12 目標値 ： 200000 変化させるセル ： $C$12 (4)「ゴールシーク」ﾀﾞｲｱﾛｸﾞﾎﾞｯｸｽの「解答が見つかりました。」で「ＯＫ」ボタンを クリックします。 (5)「コース料理」の単価が「4,394 円」と算出されました。 (6) ファイル名「4 パーティー予算完成」というファイル名で保存しましょう。 セルC12 の計算式 =C5*C6+C7+C9

－18－

練習問題 8

ブック「練習⑧」を開き、ゴールシーク機能を使用し、分析を行いましょう。 ＜処理内容＞ １．公民館の利用料金の値上げを検討しています。現在設定しているすべての利用料金を一定 金額値上げすることにしました。「新収入の増加率」(G11)を 10.0%にするには、「値上金額」 (D4)をいくらにすればよいか、ゴールシーク機能を使用して解を求めましょう。 なお、「新利用料金」(D7～D9)、「現収入」(F7～F9)、「新収入」(G7～G9)、「計」(F10～G10)、 「新収入の増加率」(G11)には、すでに計算式が設定されています。 ２．「練習⑧完成」というファイル名で保存しましょう。 ＜完成見本＞

－19－

練習問題 9

ブック「練習⑨」を開き、ゴールシーク機能を使用し、分析を行いましょう。 ＜処理内容＞ １．各施設の修繕費の予算配分を「予算配分表」で試算することにしました。各施設の「修繕費 見積り」額が300 万円以下ならば、全額を配分し、そうでない場合は、各「修繕費見積り」額 を「配分率」に応じて配分します。 「予算配分」の「合計」（H8）を 30,000,000 円にするには、「配分率」（C4）を何％にすればよ いか、ゴールシーク機能を使用して解を求めましょう。 なお、「予算配分」(C8～G8)、「修繕費見積り」の「合計」（H7）、「予算配分」の「合計」（H8） には、すでに計算式が設定されています。 ２．「練習⑨完成」というファイル名で保存しましょう。 ＜完成見本＞

－20－

総合練習問題 1

ブック「総合練習Ⅰ」を開き、「支払区分別売上表」を完成させましょう。 ＜処理内容＞ １．「支払別」ワークシート上に＜図１＞を参照し、以下の通り、表のタイトルを作成しましょ う。 ワードアート 文字列 支払区分別売上表 文字書式 サイズ 20pt ワードアートの書式 図形の塗りつぶし 色：オレンジ、アクセント2、白+ 基本色80% 図形の効果 光彩：光彩：5pt；青、アクセント カラー1 ＜図１＞を参考に適切な位置に配置する。 ＜図１＞ ２．「単価区分」（C7）、「送料区分」（H7）、「支払区分」（J7）の項目名が、セル内で 2 行で表示 されるように、「区分」の前で改行しなさい。 ３．以下のとおり、「単価区分」（C8～C37）に、入力規則を設定しましょう。 (1) 1 から 3 の数字が入力できるように設定する。 (2) 空白を無視する。 (3) 入力時メッセージのタイトルはなし、入力時メッセージに「1、2、3 のいずれかを入力」 を入力する。 (4) エラーメッセージのスタイルは「停止」、タイトルはなし、エラーメッセージに「1、2、 3 以外が入力されました」を入力する。

－21－

４．以下の通り、「金額」（E8～E37）を設定しましょう。 (1)「金額」は、「単価」×「数量」で計算する。 (2) 1.の計算式で使用する「単価」は、CHOOSE 関数を使用して、「単価区分」（C8～C37） が「1」ならば「2000」、「2」ならば「2800」、「3」ならば「3500」とするように設定する。 ５．「税額」（F8～F37）に、関数を使用して、「金額」×0.08 の結果を 10 円未満の端数を切り捨 てて表示するように設定しましょう。 ６．以下の通り、「送料」（G8～G37）を設定しましょう。 (1) IF 関数を使用して、「送料区分」（H8～H37）が「0」ならば「0」を表示し、「0」でない 場合は、「金額」（E8～E37）が「10,000」以下ならば「600」、それ以外は「0」を表示す るように設定する。 ７．「合計」（I8～I37）に、「金額」から「送料」の合計を、関数を使用して設定しましょう。 ８．「合計」（I8～I37）に、「セルの値が 10000 から 30000 の間ならば、セルの塗りつぶしを「青、 アクセント1」（1 行 5 列目）にする」という条件付き書式を設定しましょう。 ９．「数量」から「送料」（D8～G37）および「合計」（I8～I37）の数値データに、桁区切りスタ イルを設定しましょう。 10．以下の通り、「支払区分別売上表」を分析するために、ピボットテーブルを作成しましょう。 (1)「支払区分別売上表」（B7～J37）を、分析するデータの範囲に設定する。 (2) 新しいワークシートに作成する。 (3) 新しく作成したワークシートのシート名を「ピボット」に変更する。 (4)「単価区分」を行ラベルに設定する。 (5)「支払区分」を列ラベルに設定する。 (6)「数量」および「金額」の合計を、それぞれ値に設定する。また、「金額」の合計の表示 形式を、桁区切り（，）を使用した数値に設定する。 (7) 列ラベルの「値」を行ラベルに移動する。 (8) アウトライン形式で表示する。 11．「支払別」ワークシートの「支払区分別売上表」（B7～J37）をコピーし、「持帰り」ワーク シートのセルB3～J33 に元の列幅を保持して貼り付けましょう。 12．「現金・お持帰り分表」（B3～J33）を、「商品 No.」の昇順に並べ替えましょう。

－22－

13．＜図２＞を参照し、以下の通り、データを抽出するマクロを作成しましょう。 (1) マクロ名は「抽出」とする。 (2) マクロの保存先は「作業中のブック」とする。 (3) マクロの操作内容は以下の通りとする。 ①「現金・お持帰り分表」（B3～J33）にフィルターを設定し、「送料区分」が「0」で、「支 払区分」が「1」または「2」の行を表示する。 ②「単価区分」（列C）、「送料」（列 G）、「送料区分」（列 H）、「支払区分」（列 J）の列を 非表示にする。 ③ セルA1 をアクティブセルにする。 14．以下の通り、抽出を解除するマクロを作成しましょう。 (1) マクロ名は「全表示」とする。 (2) マクロの保存先は「作業中のブック」とする。 (3) マクロの操作内容は以下の通りとする。 ①「単価区分」（列C）、「送料」（列 G）、「送料区分」（列 H）、「支払区分」（列 J）の列を 再表示にする。 ②「現金・お持帰り分表」（B3～J33）のフィルターを解除する。 ③ セルA1 をアクティブセルにする。 15．＜図２＞を参照し、以下の通り、図形を２つ作成し、それぞれにマクロを登録しましょう。 左側の図形 右側の図形 図形 基本図形：六角形 文字列 抽出 全表示 文字の配置 垂直方向：上下中央揃え、水平方向：中央揃え 図形のスタイル 標準スタイル：塗りつぶし(グ ラデーション)-青、アクセント 1、アウトラインなし 標準スタイル：塗りつぶし(グ ラデーション)-オレンジ、アク セント2、アウトラインなし サイズ 高さ：1cm、幅：2.3cm マクロの登録 抽出 全表示 ＜図２＞を参考に適切な位置に配置する。 ＜図２＞

－23－

16．カード優待セールを開催するにあたり、「カード優待セール売上金額目標表」でカードの 売上の割引率を定めることにしました。「セール」ワークシート上で、以下の通り、設定しま しょう。 セール期間中の現金・商品券とカードの売上の割合を４：６とし、現金・商品券では 3％割 引、 「総売上金額」は800,000 円を目標としています。割引後の「売上金額」の「合計」(E6)を 720,000 円とするには、カードの「割引率」(D5)を何％にすればよいか、ゴールシーク機能を 使用して解を求めましょう。 なお、「売上金額」の「合計」(E6)には、すでに計算式が設定されています。 17．「総合練習Ⅰ完成」のファイル名でマクロ有効ブックとして保存しましょう。 ＜完成見本＞

－24－

総合練習問題 2

ブック「総合練習Ⅱ」を開き、「支出一覧」表を完成させましょう。 ＜処理内容＞ １．「支出一覧」ワークシート上に＜図１＞を参照し、以下の通り、表のタイトルを作成しまし ょう。 ワードアート 文字列 各項目の支出平均（年代別） 文字書式 フォント HG 創英角ゴシック UB サイズ 24pt 文字の配置 垂直方向：上下中央揃え ワードアートの書式 図形のスタイル テーマスタイル：枠線-淡色 1、塗 りつぶし-緑、アクセント 6 図形の枠線 太さ：3pt 図形の効果 影：オフセット：下 サイズ 高さ：1.8cm、幅：12cm ＜図１＞を参考に適切な位置に配置する。 ＜図１＞ ２．「全年代平均」（I8～I36）に、「30 代未満」から「60 代」の平均を、関数を使用して設定し ましょう。 ３．「通帳」ワークシート上で、以下の通り、セルB2 を設定しましょう。 文字書式 フォント HG 創英角ゴシック UB サイズ 20pt フォントの色 オレンジ、アクセント2 文字の配置 セルB2～I2 の範囲でセルを結合し、文字の配置の横位置 を均等割り付け（インデント）、インデントを「5」に設定 する

－25－

４．以下のとおり、「明細表」の「日付」（B6～B22）に、入力規則を設定しましょう。 (1) 1 から 31 までの整数が入力できるように設定する。 (2) 空白を無視する。 (3) 入力時メッセージのタイトルはなし、入力時メッセージに「日付を入力してください」 を入力する。 (4) エラーメッセージのスタイルは「停止」、タイトルはなし、エラーメッセージに「日付が 正しくないようです」を入力する。 ５．以下の通り、VLOOKUP 関数を使用して、「区分」（D6～D22）および「分類」（E6～E22） を設定しましょう。 (1)「分類表」（C27～E41）から、「摘要」（C6～C22）に該当する「区分」および「分類むを それぞれ表示する。 (2)「検索方法」は「検索値」と完全に一致する値のみを検索するように設定する。 ６．以下の通り、「引落」（G6～G22）および「引出」（H6～H22）に条件付き書式を設定しまし ょう。 (1)「引落」（G6～G22）に、「データバー」の「塗りつぶし（グラデーション）」の「赤の データバー」を設定する。 (2)「引出」（H6～H22）に、「アイコンセット」の「図形」の「赤と黒の丸」を設定する。 ７．以下の通り、「残高」（I6～I22）、「当月残高」（I23）、「当月余剰」（I24）を設定しましょう。 (1) 「残高」（I6）に、以下の計算式を設定する。 「残高」＝「前月残高」＋「入金」－「引落」－「引出」 (2) 「残高」（I7～I22）に、以下の計算式を設定する。 「残高」＝直前の「前月残高」＋「入金」－「引落」－「引出」 (3) 「当月残高」（I23）に、直前の「残高」へのセル参照を設定する。 (4) 「当月余剰」（I24）に、以下の計算式を設定する。 「当月余剰」＝「当月残高」－「前月残高」 ８．以下の通り、「集計表」の「引落」（H28）および「引出」（I28）を設定しましょう。 (1) DSUM 関数を使用して、「明細表」の一部（B5～I22）から、検索条件に該当する「引落」 および「引出」の合計を、それぞれ表示する。 ①「明細表」の一部（B5～I22）をデータベース範囲に設定する。 ② セルG27～G28 を検索条件に設定する。

－26－

９．以下の通り、「集計表」の条件を変更するマクロを作成しましょう。 (1) マクロ名は「固定費」とする。 (2) マクロの保存先は「作業中のブック」とする。 (3) マクロの操作内容は以下の通りとする。 ① セル G28 に文字列「固定」を入力する。 ② セルA1 をアクティブセルにする。 10．以下の通り、「集計表」の条件を変更するマクロを作成しましょう。 (1) マクロ名は「変動費」とする。 (2) マクロの保存先は「作業中のブック」とする。 (3) マクロの操作内容は以下の通りとする。 ① セル G28 に文字列「変動」を入力する。 ② セルA1 をアクティブセルにする。 11．＜図２＞を参照し、以下の通り、図形を２つ作成し、それぞれにマクロを登録しましょう。 左側の図形 右側の図形 図形 基本図形：四角形：角度付き 文字列 固定費 変動費 文字の配置 垂直方向：上下中央揃え、水平方向：中央揃え 図形のスタイル テーマスタイル：パステル-緑、 アクセント6 テーマスタイル：パステル- ゴールド、アクセント4 サイズ 高さ：1cm、幅：2cm マクロの登録 固定費 変動費 ＜図２＞を参考に適切な位置に配置する。 ＜図２＞

－27－

12．以下の通り、シート「支出一覧」の「各項目の支出平均（年代別）」表を分析するために、 ピボットテーブルを作成しましょう。 (1)「各項目の支出平均（年代別）」表（B7～I36）を、分析するデータの範囲に設定する。 (2) 新しいワークシートに作成する。 (3) 新しく作成したワークシートのシート名を「ピボット」に変更する。 (4)「区分」を行ラベルに設定する。 (5)「30 代」、「60 代」、「全年代平均」のそれぞれの合計を値に設定し、表示形式を、桁区切 り（，）を使用した数値に設定する。 (6) 表形式で表示し、行と列の集計を行わない。 (7)「区分」のスライサーを挿入し、アイテム「健康」と「保障」を表示する。 (8) スライサー「区分」の左上端をセル F2 の枠線に合わせて配置する。 13．Ａさんの収入の増加が1 年に 1 回の定期昇給 5,000 円だけとし、支出（固定）が年 5％ずつ 上昇すると想定して、10 年後に収支が 0 になるようにするには、支出（変動）の節約を何％に すればよいかを「10 年後の想定表」で算定することにしました。「節約」ワークシート上で、 以下の通り、設定しましょう。 「収支」の「10 年後」(D11)を 0 にするには、「節約」(D5)を何%にすればよいか、ゴール シーク機能を使用して解を求めましょう。 なお、表にはすでに計算式が設定されています。 17．「総合練習Ⅱ完成」のファイル名でマクロ有効ブックとして保存しましょう。 ＜完成見本＞

－28－

基礎的

IT セミナー 業務に役立つ表計算ソフトの関数の活用 Ⅲ

2021 年 1 月 9 日 第 1 版 発行者・・・・・・・・株式会社フロムサーティ 〒904-2143 沖縄県沖縄市知花3-4-1-102 TEL:098-929-4090 https:// from30.okinawa/ 企画・製作・・・・・・株式会社フロムサーティ ※本書の全部または一部につき、無断で転載、複写、その他の方法で記録されると、著作権等の権利侵害となります。