[Excel] 簡単にSUMさせたいのですが…。

質問

こんにちは。

添付の画面をご覧ください。

やりたいことは、サイズごとに個数と重量を合計したいだけなのですが、ちょっと変な表なのでDSUMとかSUMIFとか使いにくいんです。

単純に、=SUM(B2:B3,D2:D3,F2:F3) でも良いのですが、この表実際にはもう少し大きくて、サイズの行もデータによっては増減があります。

このような場合、H3やI3に入れる数式はどんなものが良いでしょうか？

よろしくお願いいたします。

回答No.7

Wendy02 · ベストアンサー

こんにちは。

#5の回答者です。

IF を付けるのは、あくまでも、１行置きに表示するという理由からです。

=SUMPRODUCT(MOD(COLUMN(A$2:F$3),2)*$B2:$G3)

この場合は、B2;G3で、２行分を計算するということです。もし、３行に変わるとかいうなら、B2:G4の３行分になります。

なるべく、統一した数式で、オートフィルで下にドラッグコピーで一度に出ないかと考え、前回のような数式にしました。同じフォーム（行数が同じ)なら、統一した数式で可能ですが、行数がそれぞれ違うのなら、それぞれの数式の内容は修正しないといけなくなります。

お礼

こんにちは。

>統一した数式で、オートフィルで下にドラッグコピーで一度に出ないかと考え、

意味がわかりました。そこまで考えてくれていたんですね。

実はExcel実はあまり得意ではありません。でも、なんか好きになれそうです。

ありがとうございました。

回答No.9

_Kyle

#8です。

合計する列を配列定数で指定するのであれば
SUMPRODUCTでなく普通のSUMで通りますね^^;;
訂正します。
-------------------------------------
>空欄埋めは必須ですよね。

作業列を使えば別に必須ではありませんよ。
　H2セル　：　=IF($J2=$J3,"",SUM(SUMIF($J:$J,$J2,OFFSET(A:A,,{1,3,5}))))
　I2セル　：　=IF($J2=$J3,"",SUM(SUMIF($J:$J,$J2,OFFSET(B:B,,{1,3,5}))))
　J2セル　：　=IF(A2="",J1,A2)
としてそれぞれ下方にフィル。

以上ご参考まで。

お礼

こんにちは。
やり方っていろいろあるんですね。
今回、とても勉強になりました。
幾つもの回答ありがとうございました。

回答No.8

_Kyle

ん？　
ご質問の趣旨を取り違えていたら申し訳ないのですが…。

列の構成が２×３で６列というのは
(実際は多少多いかもしれませんが)【固定】なんですよね？

一方、
＞サイズの行もデータによっては増減があります。
とありますから、
・合計を表示すべき行が何行目なのか。
・あるサイズの行が何行目から何行目までなのか
は「１行おき」「２行ずつ」とは限らず【不定】なんですよね？

であれば、
「どの列を合計するか」ということよりも
「どの行からどの行までを合計して、どの行に表示するか」
が問題なのかと思ったのですが…。
-------------------------------------------
１．
サイズ欄の空白を埋める

２．
(必要であれば)サイズ欄について[条件付書式]を設定して、
直上のセルと同じ値であればフォント色が白になるようにする。

３．
H2:I8セルを選択し、

　=IF($A2=$A3,"",SUMPRODUCT(SUMIF($A:$A,$A2,OFFSET(A:A,,{1,3,5}))))

を入力して[Ctrl]+[Enter]
※[Ctrl]+[Enter]とするのは単なる一括入力です。
　配列数式として確定する必要はないので[Shift]は不要。
　H2にまず入力して右方・下方にフィルしてもOK。

とすれば、
H列とI列で数式を分ける必要はありませんし、
数式を入れる行を選んだり、
サイズごとに数式を変える必要もありません。
-------------------------------------------
なお、実データの列の構成が２×３程度であれば、
OFFSETを使わず素朴に
　=IF($A2=$A3,"",SUMIF($A:$A,$A2,B:B)+SUMIF($A:$A,$A2,D:D)+SUMIF($A:$A,$A2,F:F))
とした方が可読性・保守性が高いかもしれません。

以上ご参考まで。長乱文陳謝。

お礼

こんにちは。

SUMIFを使う場合、この空欄埋めは必須ですよね。
透明？にしてしまえば良いと思いますが、運用が少し面倒かもしれません。
良い方法を考えてみます。
ありがとうございました。

回答No.6

某HN クロメート(Chromate)（@CoalTar）

＞このような場合、H3やI3に入れる数式はどんなものが良いでしょうか？
参考までですが

H3セルに =SUM((B:B,D:D,F:F) 2:3)
「=SUM((」まで入力後、B列を選択、[Ctrl]キーを押しつつD、F列を[クリックして括弧を閉じ、 [スペース]キー、2～3行目を選択し、「)」、数式を完成させます。

右へ一つオートフィル

H2:I3セル範囲を選択して下へオートフィル

飛び飛びの列を選択が面倒ではあります(^^;

お礼

こんにちは。

これはわかりやすいですね。
B,D,F列に「個数」とセル名を付けて、「=SUM((個数) 2:3)」としてしまうと、もっとわかりやすいかも。

いろんなやり方があるんですね、なんかわくわくします（笑。
ありがとうございました。

回答No.5

Wendy02

こんばんは。

式の中の参照位置が少しややこしいです。
図を見ながら、以下の数式、H3 から貼り付けてください。
範囲を広げる場合は、解説をみて直してみてください。

H3 （統一式)　同じ数式、ドラッグコピーしても良いですが、書式もコピーされますので、数式だけにしてください。
----------------------------------

=IF(MOD(ROW(A1),2),SUMPRODUCT(MOD(COLUMN(A$2:F$3),2)*$B2:$G3),"")

-----------------------------------
式の解説

MOD(ROW(A1),2)　は、一行置きになっているので、1と0、つまり、TRUE, FALSE にします。

TRUE 側、
SUMPRODUCT(MOD(COLUMN(A$2:F$3),2)*$B2:$G3)

MOD(COLUMN(A$2:F$3),2)

最初と同じく、列数にが、一列置きになっていますので、
1 と0、つまり TRUE, FALSE にします。
データ範囲を左に一列ずらすのは、TRUE,FALSE が逆になっているからです。

A$2:F$3

計算のデータ範囲
$B2:$G3

お礼

こんにちは。

理解するのにかなり手間取っています。
で、H3の数式ですけど
=SUMPRODUCT(MOD(COLUMN(A$2:F$3),2)*$B2:$G3)
ではダメなのでしょうか？値は、同じ感じになります。
そして頭のIF文の意味がいまいちわかっていません。
また、20cmの行は1行だったり、3行だったり可変するようです。

だいぶ進みました。ありがとうございました。
もしフォローいただけるのでしたら、お願いします。

回答No.4

mt2008

配列式を使えば可能ですが判りにくいかも知れません。

H3に↓
=SUM((MOD(COLUMN(B2:G2),2)=0)*B2:G2)+SUM((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

I3に↓
=SUM((MOD(COLUMN(B2:G2),2)=1)*B2:G2)+SUM((MOD(COLUMN(B3:G3),2)=1)*B3:G3)

それぞれ、Ctrl+Shift+Enterで配列式として確定します。
1列おきの合計を上の行、下の行で出して足しています。

お礼

こんにちは。

配列式としての確定なんてのがあるんですね。
勉強になります。

今いろいろ検証しています。おもしろいです。

ありがとうございました。

回答No.3

shinkun0114

※どうしてもこの表の形を保持したい場合
●隠し行で計算させる
　各サイズの下に2行追加します。4行目に個数の集計行を、5行目に重量の集計行を作ります。
　B4セルに=B2+B3 、C4セルは空白にし、B4:C4をコピーして一気に右側にコピーします。
　同様にB5セルは空白、C5セルは=C2+C3として、やはり右側にコピーします。
　4行は個数、5行は重量だけになりますから、SUMで簡単に集計できます。

　これを各行に作った後、非表示とすれば、見栄えも狂いません。

お礼

こんにちは。

この方法はシンプルでよいですよね。
参考にさせていただきます。
ありがとうございました。

回答No.2

sky-plane

個数計の列と重量計の列は必要ありません。集計という機能を使えば問題ありません。
その前に上記のようなグラフの形式ではまずいので変える必要があります。
個数の列と重量の列を一つにしてください。入力する範囲が足りなくなったら行の追加をしましょう。そのあとで次のようにしてください。

合計を求めたい表の範囲を選択します。（ここで、個数計と重量計の列は含みません。）

次にデータタブ→集計を選択します。

出てきたダイアログボックスに次のように設定します。

グループの基準：サイズ
集計の方法：合計
集計するフィールド：個数と重量にチェック
OKをクリック

そうすれば自動的に集計されて合計が表示されます。

集計が邪魔になる場合は集計している範囲を選択して同じように集計ダイアログボックスを開いてすべて削除をクリックすれば大丈夫です。

分からないところがありましたらご報告お願いします。

お礼

こんにちは。

個数と重量、行増やしで対応したいですよね。私もそう思います。
ただ、サイズが多いんです。で、しかも全部を1ページで出したいとかで、ギュウギュウ詰めの表になってしまう勢い。

なんとかこの形を維持したまま計をとりたい。そんな、無理な話なんです。
とにかくH3,I3に入る数式を見つけたいだけです。

ありがとうございました。もしよろしければフォロー願います。

回答No.1

shinkun0114

そもそも、なぜこのような表なのでしょうか？
2行ごとにサイズを分けて、それを集計しようとすれば、難しくなるのは当然だと思うのですが…。
たとえば、B・C列を20cm、D・E列を21cmとすれば、単純に縦計で算定できます。

このような形で出力したいのであれば、別シートに出力用のシートを作ってはどうでしょう。

お礼

こんにちは。

そうなんです。この表、ちょっと変なんですよね。
おそらく、何カ所からのデータをサイズごとに入力して、何とか合計を出そうとしているんだと思うんですけど。

ただ、これが良いみたいなので出来ればこのまま行きたいんです。
と言うか、この状態で解決させたいという変な野望もあったりしますし…。
ありがとうございました。さらなるフォローをお願いします。