| 「StiLL」 デザイン情報276 StiLLコマンド-- ボタン１つで小計・合計を出力する究極のテクニック | ||||||||||||||||||||||||||||||||||||
| 【テーマ】 過去にシート１つで完結する小計・合計の作成方法をご紹介いたしましたが、今回はさらなる簡略化を行い、 ボタン１つで小計・合計を出力するテクニックをご紹介いたします。構成もシンプルになりました。 |
【方法】 UNIQUE関数でグループ化したリストを利用します。 また集計する数式を作成しておき、StiLLボタンの「セルデータコピー (BtCellCopy)」ボタンで数式セルを展開（コピー）することで小計・合計を出力します。 |
【参考】 明細の範囲名 (店舗列／売上列) の定義は「Excelﾃﾞｰﾀ取得 (BtMerge)」ボタンなどで設定できます。 本例では店舗明細の後に小計行をまとめて表示する並びとしています。 |
||||||||||||||||||||||||||||||||||
| ■ 今回の内容 | ||||||||||||||||||||||||||||||||||||
| ボタン１つで小計・合計を作成する方法をご紹介いたします。 | ||||||||||||||||||||||||||||||||||||
| （元のデータ） （完成イメージ） | ||||||||||||||||||||||||||||||||||||
|
|
|||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||
| 本例ではシステムテンプレート「WORKT」を使用しています。 | ||||||||||||||||||||||||||||||||||||
| 同じシート上で小計・合計を出力します ⇒ | ||||||||||||||||||||||||||||||||||||
| ・ポイント | ||||||||||||||||||||||||||||||||||||
| 1. UNIQUE関数を使い（店舗）をグループ化したリストをもとに集計を行います。 | ||||||||||||||||||||||||||||||||||||
| ※UNIQUE関数はOffice365、2021で使用できます。 ⇒左記の環境以外については下段（ご参考・・ 3.）を参照 | ||||||||||||||||||||||||||||||||||||
| 2. 「セルデータコピー (BtCellCopy)」ボタンで小計・合計の数式を入力したセルを明細行の下にコピーします。 | ||||||||||||||||||||||||||||||||||||
| ■ ボタンの作成と設定 | ||||||||||||||||||||||||||||||||||||
| 今回使用するStiLLボタンを示します。（下図赤枠のボタンを使用します） | ||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||
| ボタン配置と関連図（実行後イメージ） | ||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||
| （初期値の説明） | ||||||||||||||||||||||||||||||||||||
| セルC11：店舗の合計として表示する表題 (文字列)を入力します。 ・・・本例では"計"を入力 | ||||||||||||||||||||||||||||||||||||
| セルC12：全体の合計として表示する表題 (文字列)を入力します。 ・・・本例では"支店計"を入力 | ||||||||||||||||||||||||||||||||||||
| （数式の説明） | ||||||||||||||||||||||||||||||||||||
| セルC14：=ROWS(店舗列)+15 定義名から明細最終行の次の行を求めます。（15⇒ データ出力行[14]+[1]） | ||||||||||||||||||||||||||||||||||||
| セルC15：="WORKT!F"&C14&":I"&C14+(MATCH("",E:E,-1)-14) ← FからI列の範囲に数式を展開 | ||||||||||||||||||||||||||||||||||||
| ↑E列のグループ数[4行]に合計の[1行]を加えた[5行]が出力セル位置となります。 | ||||||||||||||||||||||||||||||||||||
| ・店舗のグループ化（店舗リスト） | ||||||||||||||||||||||||||||||||||||
| セルE15：=SORT(UNIQUE(店舗列))で店舗列からグループ化します。 ここではSORT関数と合わせて使用しています。 | ||||||||||||||||||||||||||||||||||||
| ・集計式 | ||||||||||||||||||||||||||||||||||||
| セルF12：=INDEX(E:E,ROW()-$C$14+15) | ||||||||||||||||||||||||||||||||||||
| グループ化された（E列）から数式が展開（コピー）される順番で（F列）に店舗名を取り出します。 | ||||||||||||||||||||||||||||||||||||
| ※本例では、F列が 0 は合計行と判定します。 | ||||||||||||||||||||||||||||||||||||
| セルG12：=IF(F12=0,$C$12,F12&" "&$C$11) ← 数式を入力する行 [12行目] を基準に作成 | ||||||||||||||||||||||||||||||||||||
| F列の状態から小計の表題を生成、または合計の表題を出力します。 | ||||||||||||||||||||||||||||||||||||
| セルI12：=SUMIF(店舗列,IF(F12=0,"*",F12),売上列) | ||||||||||||||||||||||||||||||||||||
| F列が 0 の場合は売上列すべて "*" を加算します。 ・・・・・・・・・・・ （合計） | ||||||||||||||||||||||||||||||||||||
| 0 以外の場合はF列の店舗名と一致する売上列を集計します。 ・・・ （小計） | ||||||||||||||||||||||||||||||||||||
| 小計・合計の出力「セルデータコピー (BtCellCopy)」ボタンの設定 | ||||||||||||||||||||||||||||||||||||
| 以下に設定状態を示します。 | ||||||||||||||||||||||||||||||||||||
| ここでの出力セル位置はセルの値 (WORKT!C15) を参照しています。 | ||||||||||||||||||||||||||||||||||||
| 貼り付け種類で複数選択に☑を入れ（3.数式）と（11.出力先を値のみコピー）をチェックします。 | ||||||||||||||||||||||||||||||||||||
| ■ ボタンの実行 | ||||||||||||||||||||||||||||||||||||
| 1．（小計・合計の出力）ボタンをクリックすることで冒頭の（完成イメージ）となります。 | ||||||||||||||||||||||||||||||||||||
| ■ ご参考までに | ||||||||||||||||||||||||||||||||||||
| 1. 明細の範囲名 (店舗列／売上列) の定義は 「Excelﾃﾞｰﾀ取得 (BtMerge)」 ボタンなどで設定できます。 | ||||||||||||||||||||||||||||||||||||
| 下図の設定で元データの取得と同時に明細行数分の範囲名が定義されます。 | ||||||||||||||||||||||||||||||||||||
| 出力範囲名の設定例（本例レイアウトの場合） | ||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||
| ←「Excelﾃﾞｰﾀ抽出」ボタンでも同様 | ||||||||||||||||||||||||||||||||||||
| 2. 本例では店舗明細の後に小計行をまとめて表示する並びとしています。 | ||||||||||||||||||||||||||||||||||||
| 下図のように店舗ごとに小計を挟む場合は、集計後に「並べ替え（BtSort）」ボタンで順番を入れ替えてください。 | ||||||||||||||||||||||||||||||||||||
| （店舗ごとに小計行を挿入したイメージ） | ||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||
| ・店舗名でソートした場合思い通りに並ばないケースもあります。 | ||||||||||||||||||||||||||||||||||||
| その場合は下図のように順番、コードなどを店舗に添えるとよいでしょう。 | ||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||
| 3. UNIQUE関数が使用できない環境の場合は、店舗をグループ化するボタンを別途作成することで対応できます。 | ||||||||||||||||||||||||||||||||||||
| 店舗をグループ化するボタン「Excelﾃﾞｰﾀ抽出（BtRecordQuery）」の設定例（本例レイアウトの場合） | ||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||
| （出力イメージ） | ||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||
| 右図のようにUNIQUE関数の結果と同じ店舗リストがE列に出力されます。 ⇒ | ||||||||||||||||||||||||||||||||||||
| 店舗リストを出力したのち（小計・合計の出力）ボタンを実行します。 | ||||||||||||||||||||||||||||||||||||
| （E列の項目名は変わっても処理に影響はありません） | ||||||||||||||||||||||||||||||||||||
| ・バックナンバーは下記をご覧ください。 | ||||||||||||||||||||||||||||||||||||
| 【バックナンバー 274】 シート１枚で完結する小計・合計の作成方法 | ||||||||||||||||||||||||||||||||||||
| （各ボタンの設定内容の詳細はStiLLヘルプをご確認ください） | ||||||||||||||||||||||||||||||||||||
| Copyright(C) アイエルアイ総合研究所 無断転載を禁じます | ||||||||||||||||||||||||||||||||||||
