動的串刺し計算のコツ

動的串刺し計算のコツ

動的串刺し計算のコツ

動的串刺し計算のコツ

動的串刺し計算のコツ

MENU　>　動的串刺し計算のコツ 　　複数シートにまたがるリスト形式データの動的串刺し計算を、基本操作で考えてみました。 　　「項目による統合」機能を利用します。 項目 項目 項目 項目 動的串刺し計算のイメージ 動的の意味は。？ 標準の「項目による統合」の結果と問題点 項目による統合のコツ 動的串刺し計算のコツ 集計用の新たなキー列を作成する 単価の変動を考慮する I 単価の変動を考慮する II ワイルドカード文字を利用する 何故、統合機能を知らない人が多いのか。？

動的串刺し計算のイメージ

動的串刺し計算のイメージ

動的串刺し計算のイメージ

動的串刺し計算のイメージ

　複数シートに分散されているリスト形式データを集計して、一枚のシートにまとめます。 　以下が、データ処理のイメージです。 　データは、並べ替えられているわけでもなく、また、すべてのデータがすべてのシートに 存在しているわけでもありません。

Sheet1 Sheet2 Sheet3

A

B

C

D

E

1 コード 品名

数量 単価 合計

2 0001 みかん

1 120 120

3 0003 なし

2 330 660

4 0004 いちご

4 120 480

5 0005 もも

1 400 400

A

B

C

D

E

1 コード 品名

数量 単価 合計

2 0002 りんご

2 150 300

3 0004 いちご

4 120 480

4 0003 なし

2 330 660

5 0005 もも

1 400 400

A

B

C

D

E

1 コード 品名

数量 単価 合計

2 0001 みかん

1 120 120

3 0002 りんご

2 150 300

4 0004 いちご

4 120 480

↓ ↓ ↓ ↓ ↓ 総計シート ↓ → → → → → →

_A

_B

_C

_D

_E

1 コード 品名

数量 単価 合計

2 0001 みかん

2 120 240

3 0002 りんご

4 150 600

4 0003 なし

4 330 1320

5 0004 いちご

12 120 1440

6 0005 もも

2 400 800

← ← ← ← ← ←

動的の意味は。？

動的の意味は。？

動的の意味は。？

動的の意味は。？

　普通、串刺し計算は、全く同じシート フォーマットで行います。 　これを静的串刺し計算とします。 　しかし、リスト形式データが複数シートにまたがっている場合、行位置が異なるので、 静的串刺し計算では解決できません。 　そこで、左端列と上端行にある「項目名」をキーに、クロス集計することで、

行位置の異なる場合でも、串刺し計算することができます。 　この行位置が動くので「動的」と名づけました。 　上記の例では、左端列がA列で、キーはコードの「0001～0005」です。 　また、上端行は、1行目で、キーは項目名の「品名,数量,単価,合計」です。 　これらの、「0001～0005」と「品名,数量,単価,合計」のキーの名称で、クロス集計します。 　行や列の位置に左右されません。 　エクセルでの正式名称は、「項目による統合」という機能です。 　しかし、イメージがつかみにくいので、「動的串刺し計算」と、私が勝手に命名してみました。

標準の「項目による統合」の結果と問題点

標準の「項目による統合」の結果と問題点

標準の「項目による統合」の結果と問題点

標準の「項目による統合」の結果と問題点

　新規シートを作成し、セルA1を選択します。 　次に、[データ]-[統合]-[集計の方法]-[合計]を選択します。 　そして、[データ]-[統合]-[統合元範囲]に、以下3シートのデータ範囲を設定し、 　　[追加]ボタンで[統合元]にセットします。 　　　Sheet1!$A$1:$E$5 　　　Sheet2!$A$1:$E$5 　　　Sheet3!$A$1:$E$4 　最後に[データ]-[統合]-[統合の基準]の[上端行]と[左端列]にチェックを入れます。 　以下のように新規シートに統合されたデータが作成されます。 新規シート 　　　　　　　 目指す結果、総計シート

A

B

C

D

E

1

品名 数量 単価 合計

2 1

2 240 240

3 3

4 660 1320

4 2

4 300 600

5 4

12 360 1440

6 5

2 800 800

A

B

C

D

E

1 コード 品名

数量 単価 合計

2 0001 みかん

2 120 240

3 0002 りんご

4 150 600

4 0003 なし

4 330 1320

5 0004 いちご

12 120 1440

6 0005 もも

2 400 800

問題点は以下です。 　1)B列の品名が表示されていない。 　2)D列の単価が合計されてしまっている。 　3)セルA1にコード文字が入っていない。 　4)A列のコードの頭に0が付いていない。(元データが、セルの表示形式0000を設定している場合) 　5)A列の昇順で、データが並んでいない。 　C列数量とE列合計は、期待した結果となっています。 　上記3,4,5は、基本操作とセルの表示形式で解決できます。 　次々項では、1と2の問題を解決する方法を考えます。 　　(コードの一覧表があれば、VLOOKUP関数で1と2も解決できますが、 　　同一コードの場合でも、時系列で単価が異なる場合があるので、 　　今回は最終的に単価変更まで想定します。 　　単価が変動しない場合は、単価＝合計/数量で解決できるので、上記2 　　はクリアできます。)

項目による統合のコツ

項目による統合のコツ

項目による統合のコツ

項目による統合のコツ

　もう一つ、重要な操作方法を提示しておきます。

　上記で、新規シートを作成したときに、あらかじめ項目を入力し、その範囲を選択しておくことで、 必要な項目だけ集計することができます。 　下記水色の部分を選択後、統合を実行してください。 新規シート 新規シート 　

A

B

C

1 コード 数量 合計

2

3

4

5

6

　統合を実行 →

A

B

C

1 コード 数量 合計

2

1

2 240

3

3

4 1320

4

2

4 600

5

4

12 1440

6

5

2 800

上端行の項目名、コード、数量、合計 の3つを入力後、セル範囲A1:C1を選択 します。 それから、統合を実行します。 実は、セルA1は空白で結構です。 便宜上、コードと入力しています。 　 新規シート 新規シート

A

B

C

1 コード 数量 合計

2

1

3

2

4

5

6

　統合を実行 →

A

B

C

1 コード 数量 合計

2

1

2 240

3

2

4 600

4

5

6

上記に追加して、セルA2とA3に、左端列 のキー(項目名)を入力後、セル範囲A1:C3 を選択します。 該当キーのみのデータを合計できます。 左記の場合は、コード1と2でみかんと りんごの数量と合計のみを合計しています。 　コードの一覧表が別にある場合、後は品名と単価の列を挿入して、 VLOOKUP関数で品名と単価を引いてくる方法もあります。 　以下は、VLOOKUP関数を利用せず、単価が変動した場合でも、 統合機能を利用して解決する方法を考えます。

動的串刺し計算のコツ

動的串刺し計算のコツ

動的串刺し計算のコツ

動的串刺し計算のコツ

　コツは二つあります。 　一つは、集計用の新たなキー列を関数で作成することです。 　もう一つは、その新たなキーに、集計したくない値(品名や単価)を渡しておくことです。 　統合後、文字列操作関数や[データ]-[区切り位置]を利用して、品名や単価を取り出します。 　これで、単価変動した場合でも対応できます。

集計用の新たなキー列を作成する

集計用の新たなキー列を作成する

集計用の新たなキー列を作成する

集計用の新たなキー列を作成する

　まず、Sheet1を選択後、Sheet1～3を同時に選択します。 　シート名を[Ctrl]+左クリックでシートを同時に選択できます。 　連続したシートは、[Shift]+左クリックです。(解除は別の適当なシート名をクリックです。 　そして、A列の左に新規の列を挿入します。A列を選択後、[右クリック]-[挿入]です。 　そして、セルA1にコードと品名と単価をつなげる数式を入力して、 必要なだけ下方にコピーします。 　カンマで文字列をつなげる場合は、　=B1&","&C1&","&E1　です。 　列全体に数式を入力すると、処理が重くなり、またブックが大きくなるので、 注意が必要です。 　以下のような表ができると思います。

Sheet1～3同時選択状態

A

B

C

D

E

F

1 コード,品名,単価 コード 品名

数量 単価 合計

2 1,みかん,120

0001 みかん

1 120 120

3 3,なし,330

0003 なし

2 330 660

4 4,いちご,120

0004 いちご

4 120 480

5 5,もも,400

0005 もも

1 400 400

　新規シートを作成し、B1に数量、C1に合計と入力後、 　セル範囲A1：C1を選択し、[データ]-[統合]を実行します。 　統合の条件は以下です。 　[集計の方法]-[合計] 　[統合元範囲][統合元]　※F列まで広げます。 　　　Sheet1!$A$1:$F$5 　　　Sheet2!$A$1:$F$5 　　　Sheet3!$A$1:$F$4 　[統合の基準]の[上端行]と[左端列]にチェック。 　以下のように新規シートに統合されたデータが作成されます。 新規シート

A

B

C

1

数量 合計

2 1,みかん,120

2 240

3 3,なし,330

4 1320

4 2,りんご,150

4 600

5 4,いちご,120

12 1440

6 5,もも,400

2 800

　次に、A列とB列の間に2列追加します。 　BC列を選択後、[右クリック]-[挿入]です。 　 　そして、セル範囲A1:A6を選択後、[データ]-[区切り位置]を実行します。 　[データ]-[区切り位置]で[カンマやタブ...]をチェックし[次へ]ボタン、 　[カンマ]をチェックし、[次へ]ボタン、 　[列のデータ形式]をすべて[文字列]にして完了です。 新規シート

A B

C

D

E

1

数量 合計

2 1 みかん 120

2 240

3 3 なし

330

4 1320

4 2 りんご 150

4 600

5 4 いちご 120

12 1440

6 5 もも

400

2 800

　後は、項目名を入力しC列とD列を入れ替え、A列で並べ替え、A列に表示形式で0000を設定すれば、 　理想的な結果が得られると思います。 　新たなキー列(作業セル)を作成し、合計したくない値を、そのキーに文字列連結し、 後で取り出すことで、統合ではできない品名や単価のような値の表示も可能です。 　難しい関数やVBAを利用しなくても、基本操作だけでかなり複雑な処理もできますね。

　しかし、作業列を入れる場合、特に複数シートを扱う場合は、データを消さないように、 十分注意が必要です。 　ブックごとバックアップを事前にとっておく必要があると思います。 　また、この作業列での解決方法は、統合機能だけでなく、複数シートを利用したピボット テーブルでも応用可能です。

単価の変動を考慮する I

単価の変動を考慮する I

単価の変動を考慮する I

単価の変動を考慮する I

　単価が変動した場合の事例を考えて見ました。 　上記まででも解決できるのですが、少し関数で工夫してみます。 　上記の事例の中で、一箇所だけデータを書き換えます。 　Sheet3のセルD2の単価を120円から130円に変更します。

Sheet1 Sheet2 Sheet3

A

B

C

D

E

1 コード 品名

数量 単価 合計

2 0001 みかん

1 120 120

3 0003 なし

2 330 660

4 0004 いちご

4 120 480

5 0005 もも

1 400 400

A

B

C

D

E

1 コード 品名

数量 単価 合計

2 0002 りんご

2 150 300

3 0004 いちご

4 120 480

4 0003 なし

2 330 660

5 0005 もも

1 400 400

A

B

C

D

E

1 コード 品名

数量 単価 合計

2 0001 みかん

1 130 130

3 0002 りんご

2 150 300

4 0004 いちご

4 120 480

　まず、Sheet1を選択後、Sheet1～3を同時に選択します。 　そして、A列の左に新規の列を挿入します。 　そして、セルA1にコードと品名と単価をつなげる数式を入力して、 　必要なだけ下方にコピーします。 　ここで数式を少し工夫します。 　コード、単価、品名の順で、文字列連結します。 　=TEXT(B1,"0000")&TEXT(E1,"0000")&C1 　コード4桁、単価4桁と固定することで、後でデータを分割しやすくします。 　以下のような表ができると思います。 Sheet1～3同時選択状態

A

B

C

D

E

F

1 コード単価品名 コード 品名

数量 単価 合計

2 00010120みかん 0001 みかん

1 120 120

3 00030330なし

0003 なし

2 330 660

4 00040120いちご

0004 いちご

4 120 480

5 00050400もも

0005 もも

1 400 400

　新規シートを作成し、セルA1を選択後、そのまま[データ]-[統合]を実行します。 　今回は、先に項目名は入力しません。 　統合の条件は以下です。 　[集計の方法]-[合計] 　[統合元範囲][統合元]　※F列まで広げます。 　　　Sheet1!$A$1:$F$5 　　　Sheet2!$A$1:$F$5 　　　Sheet3!$A$1:$F$4 　[統合の基準]の[上端行]と[左端列]にチェック。

　以下のように新規シートに統合されたデータが作成されます。 　単価120円と130円の、コード0001、品名みかんが、別々に集計されています。 新規シート

A

B

C

D

E

F

1

コード 品名 数量 単価 合計

2 00010120みかん 0001

1 120 120

3 00030330なし

0006

4 660 1320

4 00010130みかん 0001

1 130 130

5 00020150りんご

0004

4 300 600

6 00040120いちご

0012

12 360 1440

7 00050400もも

0010

2 800 800

　セルB2、C2、E2にそれぞれ以下の数式を入力後、 必要なだけ下方にコピーします。 　=LEFT(A2,4) 　=MID(A2,9,3) 　=TEXT(MID(A2,5,4),"0")*1　　　※1を乗算しているのは、文字数値を数値化するためです。 新規シート

A

B

C

D

E

F

1

コード 品名

数量 単価 合計

2 00010120みかん 0001 みかん

1 120 120

3 00030330なし

0003 なし

4 330 1320

4 00010130みかん 0001 みかん

1 130 130

5 00020150りんご

0002 りんご

4 150 600

6 00040120いちご

0004 いちご

12 120 1440

7 00050400もも

0005 もも

2 400 800

次に全体をコピーし、形式を選択して貼り付けで値貼り付けして、データを定着させます。 そして、Ａ列を削除後、Ａ列昇順で並べ替えれば、以下のようになります。 新規シート

A

B

C

D

E

1 コード 品名

数量 単価 合計

2 0001 みかん

1 120 120

3 0001 みかん

1 130 130

4 0002 りんご

4 150 600

5 0003 なし

4 330 1320

6 0004 いちご

12 120 1440

7 0005 もも

2 400 800

単価の変動を考慮する II

単価の変動を考慮する II

単価の変動を考慮する II

単価の変動を考慮する II

　作業列を作らなくても、単価を個別に確認する方法があります。 　上記データで、[データ]-[統合]-[統合元データとリンクする]にチェックして、 統合を実行します。 　以下のように、左側にアウトラインが出てきます。 1 2 A B C D E F 1 品名 数量 単価 合計

+ ++ + 4 1 2 250 250 + ++ + 7 3 4 660 1320 + ++ + 10 2 4 300 600 + ++ + 14 4 12 360 1440 + ++ + 17 5 2 800 800 　アウトラインのボタン2(列名Aの二つ左側)をクリックすると、内容が展開されて、 下記のように、単価を確認することができます。 1 2222 A B C D E F 1 品名 数量 単価 合計 ｜ ｜ ｜ ｜ ・ 2 串刺し計算 1 120 120 ｜ ｜ ｜ ｜ ・ 3 串刺し計算 1 130 130 - --- 4 1 2 250 250 ｜ ｜ ｜ ｜ ・ 5 串刺し計算 2 330 660 ｜ ｜ ｜ ｜ ・ 6 串刺し計算 2 330 660 - --- 7 3 4 660 1320 ｜ ｜ ｜ ｜ ・ 8 串刺し計算 2 150 300 ｜ ｜ ｜ ｜ ・ 9 串刺し計算 2 150 300 - --- 10 2 4 300 600 ｜ ｜ ｜ ｜ ・ 11 串刺し計算 4 120 480 ｜ ｜ ｜ ｜ ・ 12 串刺し計算 4 120 480 ｜ ｜ ｜ ｜ ・ 13 串刺し計算 4 120 480 - --- 14 4 12 360 1440 ｜ ｜ ｜ ｜ ・ 15 串刺し計算 1 400 400 ｜ ｜ ｜ ｜ ・ 16 串刺し計算 1 400 400 - --- 17 5 2 800 800 　品名を抽出することを考えると、作業列を利用した方がいいかもしれません。 　[集計の方法]に列単位で「値」が欲しいところですね。

ワイルドカード文字を利用する

ワイルドカード文字を利用する

ワイルドカード文字を利用する

ワイルドカード文字を利用する

　　静的串刺し計算(3D参照)では、シート名にワイルドカード文字が利用できます。 　　参考事例のURLは、以下です。 [MSON] 第19号 ワンポイントテクニック 第 15 回 　　　　　[Excel] ワークシートの串刺し集計の方法 　　　　　http://www.microsoft.com/japan/office/news/BackNumber/MSON019-990129.txt 　　しかし、[データ]-[統合]では、うまくワイルドカード文字が利用できませんでした。 　それで、以下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

　　　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でアプリケーションを組む場合も、場合によりピボットテーブルを使うよりも、 この統合機能を利用した方がいい場合もあると思います。 　項目による統合を理解するためのポイントは、統合を実行する前に項目名を指定し、 それを事前に選択しておくことで、必要なデータのみ取り出すことができることですね。 　また、今回は、作業列を作成し、その新規キーに値を加えることで、後で値を取り出す

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