[[20090901083234]]　『結合セルを参照した時の集計』（みやじ）　ページの最後に飛ぶ

『結合セルを参照した時の集計』（みやじ）

sheet1

    A    B         C         D
1   5   工器   H20.07.31     500
2   6   備消   H20.10.10     800
3   6          H20.12.01   1,000
4   7   備消   H21.03.05     200
5   7          H21.06.21     400
6   8   工器   H21.08.10   1,500
             ・
　　　　　　 ・
B2:B3,B4:B5が結合セルです

sheet2

        A
1   H20.11.01
2   H21.06.30
3   _________

sheet1のような表がありまして、sheet2でA1からA2までの期間内で、B列"備消"にあてはまるD列の合計をA3に計算したいのですが…

=SUMPRODUCT((A1>='sheet1!'C1:C6)*(A2<='sheet1!'C1:C6)*('sheet1!'="備消")*(D1:D6))

と入力したのですが、B列の結合セルによってB3,B5のセルが0となっている為合計に反映されません

宜しくお願いします。

 解決策というか基本的にですが
 集計に使うデータ範囲には結合セルを使わないように心がける事が大切です。
 印刷の体裁を整えるのであれば、印刷用のシートを作る事。

 これを基本とすれば、このような問題は無くなると思いますよ
 （ｍｏｍｏ）

 コメントありがとうございます。
 結合セルはデータベースに向かないのは最近知りました。

 この表は3年以上前から作成していたもので
 結合セルを解除してデータを入れ直すとなると
 かなり大変な作業になるもので
 できれば関数などを利用して集計できないものかと思い
 質問させていただきました。

 作業列案です。
 E1=B1
 E2=IF(B2="",E1,B2)　以下フィル

 で、数式を
 =SUMPRODUCT((A1<=Sheet1!C1:C6)*(A2>=Sheet1!C1:C6)*(Sheet1!E1:E6="備消")*(Sheet1!D1:D6))
 にしてみてください。
 （数式も微妙に間違っていましたね。期間が逆ですとか）

 （ｍｏｍｏ）

 ありがとうございます
 もし作業列を使用しない方法があるとすれば
 かなり複雑な計算式になるものでしょうか？

 あと、書き忘れていたのですが
 結合セルは3段以上のものもあります。

 ３年間の表がどの様に配置されているのか分かりませんが
 結合セルを解除するのは　手間では無いと思います。

 その後、
 ｍｏｍｏさんの手順通りに作成したE列を
 B列に値貼り付け。

 今後は全ての行にデータが埋まるように
 入力なさるのが良いと思います。

 ＞この表は3年以上前から作成していたもので
 ＞結合セルを解除してデータを入れ直すとなると
 ＞かなり大変な作業になるもので
 が【建前】なら、無視して下さい。

 (HANA)

 複雑かどうかはわかりませんが、私はＥＸＣＥＬは作業列を使いこなすソフトだと認識しています。
 なので、単セル１発で答えを出す事は通常やっていないというか頭に無いので
 わかりません。というかしません。
 変に配列数式などを使うと余計に重くなったりしますし。

 私がこういうデータフォーマットに出会った時にどうするか？を答えるのであれば
 ＶＢＡで結合セルを一気に解除して普通に計算します。
 （ＶＢＡも結合を扱う処理は苦手なので集計そのものをＶＢＡでやると思いますが）

 参考に選択範囲内の結合セルを解除して値を放り込むサンプルコードです。
 （何も考えず書いたので変数の宣言とかしてません）

  Sub 選択範囲内の結合解除()
  For Each c In Selection
    If c.MergeCells = True Then
      buf = c.Value
      Set r = c.MergeArea
      c.UnMerge
      r.Value = buf
    End If
  Next c
  End Sub

 （ｍｏｍｏ）

 なるほど。
 やはり作業列を使用して計算するのがよさそうですね。

 VBAはちょっと難しいので
 先程教えていただいた作業列での方法でやってみます。
 ありがとうございます。

    A     B         C         D
1   5   工器   H20.07.31     500
2   6   備消   H20.10.10     800
3   6          H20.12.01   1,000
4   7   備消   H21.03.05     200
5   7          H21.06.21     400
6   8   工器   H21.08.10   1,500

 1.B列の結合を解きます。
 2.B列の範囲をドラッグ選択します。
 3.Ctrl+G（編集→ジャンプ）で[セル選択]をクリック
 4.[選択オプション]BOXの[空白セル]にチェックを入れて[OK]をクリック
 5.空白セルが選択されて、B3セルがアクティブセルになります。
 6.数式バー上で  =B2 と入れて Ctrl+Enter

 以上で空白セルが埋まりますから、必要あれば白文字にするのはいかがですか。。。(gon-2)

 おぉ！
 こんなに簡単に空白を埋められるとは驚きましたっ
 便利な機能があるんですね
 結合セルの解除も1つ1つやるのかと思ってました
 助かりました、ありがとうございます

コメント返信：

[ 一覧(最新更新順) ]

[[20090901083234]] 『結合セルを参照した時の集計』（みやじ） ページの最後に飛ぶ

[[20090901083234]]　『結合セルを参照した時の集計』（みやじ）　ページの最後に飛ぶ