動的串刺し計算のコツ

Transcription

1 動 的 串 刺 し 計 算 のコツ MENU > 動 的 串 刺 し 計 算 のコツ 複 数 シートにまたがるリスト 形 式 データの 動 的 串 刺 し 計 算 を 基 本 操 作 で 考 えてみました 項 目 による 統 合 機 能 を 利 用 します 項 目 動 的 串 刺 し 計 算 のイメージ 動 的 の 意 味 は? 標 準 の 項 目 による 統 合 の 結 果 と 問 題 点 項 目 による 統 合 のコツ 動 的 串 刺 し 計 算 のコツ 集 計 用 の 新 たなキー 列 を 作 成 する 単 価 の 変 動 を 考 慮 する I 単 価 の 変 動 を 考 慮 する II ワイルドカード 文 字 を 利 用 する 何 故 統 合 機 能 を 知 らない 人 が 多 いのか? 動 的 串 刺 し 計 算 のイメージ 複 数 シートに 分 散 されているリスト 形 式 データを 集 計 して 一 枚 のシートにまとめます 以 下 が データ 処 理 のイメージです データは 並 べ 替 えられているわけでもなく また すべてのデータがすべてのシートに 存 在 しているわけでもありません Sheet1 Sheet2 Sheet みかん なし いちご もも りんご いちご なし もも みかん りんご いちご 総 計 シート みかん りんご なし いちご もも 動 的 の 意 味 は? 普 通 串 刺 し 計 算 は 全 く 同 じシート フォーマットで 行 います これを 静 的 串 刺 し 計 算 とします しかし リスト 形 式 データが 複 数 シートにまたがっている 場 合 行 位 置 が 異 なるので 静 的 串 刺 し 計 算 では 解 決 できません そこで 左 端 列 と 上 端 行 にある 項 目 名 をキーに クロス 集 計 することで

2 行 位 置 の 異 なる 場 合 でも 串 刺 し 計 算 することができます この 行 位 置 が 動 くので 動 的 と 名 づけました 上 記 の 例 では 左 端 列 がA 列 で キーはコードの 0001~0005 です また 上 端 行 は 1 行 目 で キーは 項 目 名 の 品 名, 数 量, 単 価, 合 計 です これらの 0001~0005 と 品 名, 数 量, 単 価, 合 計 のキーの 名 称 で クロス 集 計 します 行 や 列 の 位 置 に 左 右 されません エクセルでの 正 式 名 称 は 項 目 による 統 合 という 機 能 です しかし イメージがつかみにくいので 動 的 串 刺 し 計 算 と 私 が 勝 手 に 命 名 してみました 標 準 の 項 目 による 統 合 の 結 果 と 問 題 点 を 作 成 し セルA1を 選 択 します 次 に [データ]-[ 統 合 ]-[ 集 計 の 方 法 ]-[ 合 計 ]を 選 択 します そして [データ]-[ 統 合 ]-[ 統 合 元 範 囲 ]に 以 下 3シートのデータ 範 囲 を 設 定 し [ 追 加 ]ボタンで[ 統 合 元 ]にセットします Sheet1!$A$1:$E$5 Sheet2!$A$1:$E$5 Sheet3!$A$1:$E$4 最 後 に[データ]-[ 統 合 ]-[ 統 合 の 基 準 ]の[ 上 端 行 ]と[ 左 端 列 ]にチェックを 入 れます 以 下 のように に 統 合 されたデータが 作 成 されます 目 指 す 結 果 総 計 シート 1 品 名 数 量 単 価 合 計 みかん りんご なし いちご もも 問 題 点 は 以 下 です 1)B 列 の 品 名 が 表 示 されていない 2)D 列 の 単 価 が 合 計 されてしまっている 3)セルA1にコード 文 字 が 入 っていない 4)A 列 のコードの 頭 に0が 付 いていない ( 元 データが セルの 表 示 形 式 0000を 設 定 している 場 合 ) 5)A 列 の 昇 順 で データが 並 んでいない C 列 数 量 とE 列 合 計 は 期 待 した 結 果 となっています 上 記 3,4,5は 基 本 操 作 とセルの 表 示 形 式 で 解 決 できます 次 々 項 では 1と2の 問 題 を 解 決 する 方 法 を 考 えます (コードの 一 覧 表 があれば VLOOKUP 関 数 で1と2も 解 決 できますが 同 一 コードの 場 合 でも 時 系 列 で 単 価 が 異 なる 場 合 があるので 今 回 は 最 終 的 に 単 価 変 更 まで 想 定 します 単 価 が 変 動 しない 場 合 は 単 価 = 合 計 / 数 量 で 解 決 できるので 上 記 2 はクリアできます ) 項 目 による 統 合 のコツ もう 一 つ 重 要 な 操 作 方 法 を 提 示 しておきます

3 上 記 で を 作 成 したときに あらかじめ 項 目 を 入 力 し その 範 囲 を 選 択 しておくことで 必 要 な 項 目 だけ 集 計 することができます 下 記 水 色 の 部 分 を 選 択 後 統 合 を 実 行 してください 1 コード 数 量 合 計 2 3 統 合 を 実 行 コード 数 量 合 計 上 端 行 の 項 目 名 コード 数 量 合 計 の3つを 入 力 後 セル 範 囲 A1:C1を 選 択 します それから 統 合 を 実 行 します 実 は セルA1は 空 白 で 結 構 です 便 宜 上 コードと 入 力 しています 1 コード 数 量 合 計 統 合 を 実 行 1 コード 数 量 合 計 上 記 に 追 加 して セルA2とA3に 左 端 列 のキー( 項 目 名 )を 入 力 後 セル 範 囲 A1:C3 を 選 択 します 該 当 キーのみのデータを 合 計 できます 左 記 の 場 合 は コード1と2でみかんと りんごの 数 量 と 合 計 のみを 合 計 しています 6 6 コードの 一 覧 表 が 別 にある 場 合 後 は 品 名 と 単 価 の 列 を 挿 入 して VLOOKUP 関 数 で 品 名 と 単 価 を 引 いてくる 方 法 もあります 以 下 は VLOOKUP 関 数 を 利 用 せず 単 価 が 変 動 した 場 合 でも 統 合 機 能 を 利 用 して 解 決 する 方 法 を 考 えます 動 的 串 刺 し 計 算 のコツ コツは 二 つあります 一 つは 集 計 用 の 新 たなキー 列 を 関 数 で 作 成 することです もう 一 つは その 新 たなキーに 集 計 したくない 値 ( 品 名 や 単 価 )を 渡 しておくことです 統 合 後 文 字 列 操 作 関 数 や[データ]-[ 区 切 り 位 置 ]を 利 用 して 品 名 や 単 価 を 取 り 出 します これで 単 価 変 動 した 場 合 でも 対 応 できます 集 計 用 の 新 たなキー 列 を 作 成 する まず Sheet1を 選 択 後 Sheet1~3を 同 時 に 選 択 します シート 名 を[Ctrl]+ 左 クリックでシートを 同 時 に 選 択 できます 連 続 したシートは [Shift]+ 左 クリックです ( 解 除 は 別 の 適 当 なシート 名 をクリックです そして A 列 の 左 に 新 規 の 列 を 挿 入 します A 列 を 選 択 後 [ 右 クリック]-[ 挿 入 ]です そして セルA1にコードと 品 名 と 単 価 をつなげる 数 式 を 入 力 して 必 要 なだけ 下 方 にコピーします カンマで 文 字 列 をつなげる 場 合 は =B1&","&C1&","&E1 です 列 全 体 に 数 式 を 入 力 すると 処 理 が 重 くなり またブックが 大 きくなるので 注 意 が 必 要 です 以 下 のような 表 ができると 思 います

4 Sheet1~3 同 時 選 択 状 態 F 1 コード, 品 名, 単 価 コード 品 名 数 量 単 価 合 計 2 1,みかん, みかん ,なし, なし ,いちご, いちご ,もも, もも を 作 成 し B1に 数 量 C1に 合 計 と 入 力 後 セル 範 囲 A1:C1を 選 択 し [データ]-[ 統 合 ]を 実 行 します 統 合 の 条 件 は 以 下 です [ 集 計 の 方 法 ]-[ 合 計 ] [ 統 合 元 範 囲 ][ 統 合 元 ] F 列 まで 広 げます Sheet1!$A$1:$F$5 Sheet2!$A$1:$F$5 Sheet3!$A$1:$F$4 [ 統 合 の 基 準 ]の[ 上 端 行 ]と[ 左 端 列 ]にチェック 以 下 のように に 統 合 されたデータが 作 成 されます 1 数 量 合 計 2 1,みかん, ,なし, ,りんご, ,いちご, ,もも, 次 に A 列 とB 列 の 間 に2 列 追 加 します BC 列 を 選 択 後 [ 右 クリック]-[ 挿 入 ]です そして セル 範 囲 A1:A6を 選 択 後 [データ]-[ 区 切 り 位 置 ]を 実 行 します [データ]-[ 区 切 り 位 置 ]で[カンマやタブ...]をチェックし[ 次 へ]ボタン [カンマ]をチェックし [ 次 へ]ボタン [ 列 のデータ 形 式 ]をすべて[ 文 字 列 ]にして 完 了 です 1 数 量 合 計 2 1 みかん なし りんご いちご もも 後 は 項 目 名 を 入 力 しC 列 とD 列 を 入 れ 替 え A 列 で 並 べ 替 え A 列 に 表 示 形 式 で0000を 設 定 すれば 理 想 的 な 結 果 が 得 られると 思 います 新 たなキー 列 ( 作 業 セル)を 作 成 し 合 計 したくない 値 を そのキーに 文 字 列 連 結 し 後 で 取 り 出 すことで 統 合 ではできない 品 名 や 単 価 のような 値 の 表 示 も 可 能 です 難 しい 関 数 やVBAを 利 用 しなくても 基 本 操 作 だけでかなり 複 雑 な 処 理 もできますね

5 しかし 作 業 列 を 入 れる 場 合 特 に 複 数 シートを 扱 う 場 合 は データを 消 さないように 十 分 注 意 が 必 要 です ブックごとバックアップを 事 前 にとっておく 必 要 があると 思 います また この 作 業 列 での 解 決 方 法 は 統 合 機 能 だけでなく 複 数 シートを 利 用 したピボット テーブルでも 応 用 可 能 です 単 価 の 変 動 を 考 慮 する I 単 価 が 変 動 した 場 合 の 事 例 を 考 えて 見 ました 上 記 まででも 解 決 できるのですが 少 し 関 数 で 工 夫 してみます 上 記 の 事 例 の 中 で 一 箇 所 だけデータを 書 き 換 えます Sheet3のセルD2の 単 価 を120 円 から130 円 に 変 更 します Sheet1 Sheet2 Sheet みかん なし いちご もも りんご いちご なし もも みかん りんご いちご まず Sheet1を 選 択 後 Sheet1~3を 同 時 に 選 択 します そして A 列 の 左 に 新 規 の 列 を 挿 入 します そして セルA1にコードと 品 名 と 単 価 をつなげる 数 式 を 入 力 して 必 要 なだけ 下 方 にコピーします ここで 数 式 を 少 し 工 夫 します コード 単 価 品 名 の 順 で 文 字 列 連 結 します =TEXT(B1,"0000")&TEXT(E1,"0000")&C1 コード4 桁 単 価 4 桁 と 固 定 することで 後 でデータを 分 割 しやすくします 以 下 のような 表 ができると 思 います Sheet1~3 同 時 選 択 状 態 F 1 コード 単 価 品 名 コード 品 名 数 量 単 価 合 計 みかん 0001 みかん なし 0003 なし いちご 0004 いちご もも 0005 もも を 作 成 し セルA1を 選 択 後 そのまま[データ]-[ 統 合 ]を 実 行 します 今 回 は 先 に 項 目 名 は 入 力 しません 統 合 の 条 件 は 以 下 です [ 集 計 の 方 法 ]-[ 合 計 ] [ 統 合 元 範 囲 ][ 統 合 元 ] F 列 まで 広 げます Sheet1!$A$1:$F$5 Sheet2!$A$1:$F$5 Sheet3!$A$1:$F$4 [ 統 合 の 基 準 ]の[ 上 端 行 ]と[ 左 端 列 ]にチェック

6 以 下 のように に 統 合 されたデータが 作 成 されます 単 価 120 円 と130 円 の コード0001 品 名 みかんが 別 々に 集 計 されています F みかん なし みかん りんご いちご もも セルB2 C2 E2にそれぞれ 以 下 の 数 式 を 入 力 後 必 要 なだけ 下 方 にコピーします =LEFT(A2,4) =MID(A2,9,3) =TEXT(MID(A2,5,4),"0")*1 1を 乗 算 しているのは 文 字 数 値 を 数 値 化 するためです F みかん 0001 みかん なし 0003 なし みかん 0001 みかん りんご 0002 りんご いちご 0004 いちご もも 0005 もも 次 に 全 体 をコピーし 形 式 を 選 択 して 貼 り 付 けで 値 貼 り 付 けして データを 定 着 させます そして A 列 を 削 除 後 A 列 昇 順 で 並 べ 替 えれば 以 下 のようになります みかん みかん りんご なし いちご もも 単 価 の 変 動 を 考 慮 する II 作 業 列 を 作 らなくても 単 価 を 個 別 に 確 認 する 方 法 があります 上 記 データで [データ]-[ 統 合 ]-[ 統 合 元 データとリンクする]にチェックして 統 合 を 実 行 します 以 下 のように 左 側 にアウトラインが 出 てきます 1 2 F 1 品 名 数 量 単 価 合 計

7 アウトラインのボタン2( 列 名 Aの 二 つ 左 側 )をクリックすると 内 容 が 展 開 されて 下 記 のように 単 価 を 確 認 することができます 1 2 F 1 品 名 数 量 単 価 合 計 2 串 刺 し 計 算 串 刺 し 計 算 串 刺 し 計 算 串 刺 し 計 算 串 刺 し 計 算 串 刺 し 計 算 串 刺 し 計 算 串 刺 し 計 算 串 刺 し 計 算 串 刺 し 計 算 串 刺 し 計 算 品 名 を 抽 出 することを 考 えると 作 業 列 を 利 用 した 方 がいいかもしれません [ 集 計 の 方 法 ]に 列 単 位 で 値 が 欲 しいところですね ワイルドカード 文 字 を 利 用 する 静 的 串 刺 し 計 算 (3D 参 照 )では シート 名 にワイルドカード 文 字 が 利 用 できます 参 考 事 例 のURLは 以 下 です [MSON] 第 19 号 ワンポイントテクニック 第 15 回 [Excel] ワークシートの 串 刺 し 集 計 の 方 法 しかし [データ]-[ 統 合 ]では うまくワイルドカード 文 字 が 利 用 できませんでした それで 以 下 VBAで 考 えてみました これで 簡 単 に 複 数 シートの 動 的 串 刺 し 計 算 ( 項 目 による 統 合 )ができます データを 上 書 きするので 実 行 には 十 分 注 意 してください Sub S_DynaSum() 'シート 名 をワイルドカード 文 字 で 指 定 Like 演 算 子 のヘルプ 参 照 Const mywsname As String = "Sheet#" 'セル 範 囲 をA1 形 式 で 指 定 Const myrc As String = "A1:F5" 'R1C1 形 式 への 変 換 を 確 認 'MsgBox Range(myRC).Address(,, xlr1c1) Dim mysources() As String Dim i As Long, n As Long, m As Long

8 If MsgBox("データを 上 書 きするので データのないシートの 適 当 " & vbcrlf _ & "なセルを 選 択 後 実 行 してください " & vbcrlf & vbcrlf & _ "シート 名 " & mywsname & " セル 範 囲 ( " & myrc & _ " )で 統 合 を 実 行 します ", vbokcancel + _ vbexclamation + vbdefaultbutton2) = vbcancel Then Exit Sub 'ワークシート 数 をセット n = Worksheets.Count ReDim mysources(n - 1) For i = 0 To n - 1 If Worksheets(i + 1).Name Like mywsname Then mysources(p) = Worksheets(i + 1).Name & "!" & _ Worksheets(i + 1).Range(myRC).Address(,, xlr1c1) p = p + 1 End If Next ReDim Preserve mysources(p - 1) 'For i = 0 To p - 1 ' Debug.Print mysources(i) ' Debug.Print i 'Next 'Debug.Print "---" 'Selection.Consolidate Sources:=Array(mySources()) Selection.Consolidate Sources:=mySources _, Function:=xlSum, TopRow:=True, LeftColumn:=True, _ CreateLinks:=False End Sub 何 故 統 合 機 能 を 知 らない 人 が 多 いのか? 関 数 やVBAでデータ 処 理 してるので 必 要 性 がないから そもそも 統 合 という 機 能 があることを 知 らなかったから ピボットテーブルの 方 が 機 能 が 高 いから 項 目 による 統 合 で 値 表 示 が 難 しいから データは1シートのリスト 形 式 にまとめて 運 用 した 方 がいいから と 多 くの 理 由 があると 思 います Excel 歴 約 10 年 の 私 ですが 今 まで 統 合 機 能 は 一 度 も 使 ったことがありませんでした それは ピボットテーブルを 習 得 してしまった 為 必 要 性 がなかったからです また 統 合 という 言 葉 が 串 刺 し 計 算 と 結 びつかなかったこともあります 今 回 動 的 串 刺 し 計 算 という 名 称 を 作 ったのは イメージが 簡 単 につかめるように 考 えたからです ただ 複 雑 な 数 式 やVBAを 覚 えず また 表 形 式 からリスト 形 式 に 変 換 しなくても 複 数 シートの 高 度 な 集 計 ができるというのは ある 意 味 利 用 価 値 は 高 いと 思 います また VBAでアプリケーションを 組 む 場 合 も 場 合 によりピボットテーブルを 使 うよりも この 統 合 機 能 を 利 用 した 方 がいい 場 合 もあると 思 います 項 目 による 統 合 を 理 解 するためのポイントは 統 合 を 実 行 する 前 に 項 目 名 を 指 定 し それを 事 前 に 選 択 しておくことで 必 要 なデータのみ 取 り 出 すことができることですね また 今 回 は 作 業 列 を 作 成 し その 新 規 キーに 値 を 加 えることで 後 で 値 を 取 り 出 す

9 手 法 を 新 たに 考 えました これは ピボットテーブルの 複 数 シート 処 理 の 場 合 も 応 用 可 能 と 思 います ただ Excelユーザーでも 統 合 機 能 は 使 っていない 人 が 多 いので 引 継 ぎをする 場 合 を 考 えると 利 用 はしない 方 がいいかもしれません 検 算 用 に この 機 能 はいいかもしれませんが VBAユーザーがこの 統 合 機 能 を 利 用 する 場 合 特 に 月 次 年 次 処 理 の 集 計 には 非 常 に 有 効 かもしれません 今 回 は 行 方 向 のみ 扱 いましたが 列 方 向 にも 動 的 に 串 刺 しができるからです [ページトップへ] Copyright(C) HI-TAN All Rights Reserved.