[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『同じ名前のグループで合計額を算出する数式・関数について』（equity）
初めて質問させていただきます。
以下の図１をご確認ください。
★図１
A B C D E F
1 [name] [money] [money2] [total2] [total3] [total] 2 a 1 2 3 3 3 3 b 1 2 3 6 6 4 b 1 2 3 3 5 c 1 2 3 3 3 ・ ・ ・ 10
最終的にF列に合計額を表示するつもりで作成しました。 D列とE列に入っている値は、F列に入っている数式で利用します。
D2=SUM(B2,C2) その行の合計額を算出します。 ※B列とC列の値の合計です。
E2=SUMIF(A2:$A$10,A2:$A$10,D2:$D$10) その列から最下部まで入力されているA列の文字列で 同じ文字列をもつ行のD列の合計を算出します。 ※図１の場合はA列のbが2つあるので、D3とD4の値を合計して E3が6になります。
F2=SUMPRODUCT(MAX(($A$2:$A$10=A2)*($E$2:$E$10))) 最上部から最下部まで入力されているA列の文字列で 同じ文字列をもつ行のE列の最大値を算出します。 ※図１の場合はA列のbが2つあるので、E3とE4の最大値を算出して F3が6になります。
さらに上記の関数を以下の◆に置き換えます。 置き換えたものが図１のようになります。
F2=if(◆=E2,◆,"") A列の文字列で同じ文字列をもつ場合 同じ文字列をもつ最上部の行のみF列の値を表示させます。 ※図１の場合はF4が空白になります。
------------------------------------- ここからが本題です。 合計値に新しいケースが出てきたため、数式の改善が必要です。
F列にマイナス値が表示されるべきケースです。 以下の図２のようになることを期待しています。
★図２
A B C D E F
1 [name] [money] [money2] [total2] [total3] [total] 2 a -10 2 -8 -8 -8 3 b 1 2 3 -5 -5 4 b -10 2 -8 -8 5 c 1 2 3 3 3 ・ ・ ・ 10
図２を表現するためのF列の数式をご教授ください。 ちなみに私の構成は固執していません。 目的を達成できる もっとスッキリした構成があるならば是非とも。。！
以下は私の失敗作です。。
F2=IF(SUMPRODUCT(MAX(($A$2:$A$10=A2)*($E$2:$E$10)))=E2,SUMPRODUCT(MAX(($A$2:$A$10=A2)*($E$2:$E$10))),IF(AND(E2<0,E2>SUMPRODUCT(MIN(($A$2:$A$10=A2)*($E$2:$E$10)))),E2,"")) ※A列に同じ文字列が入力されている場合(図２のA列がbの場合)は 表現できますが、A列が一意の場合(図２のA列がaの場合)は失敗する(空白になる)。
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
（equity） 2015/06/02(火) 15:59
セルF2に
=IF($E2=MAX(IF($A$2:$A$10=$A2,$E$2:$E$10,"")),MAX(IF($A$2:$A$10=$A2,$E$2:$E$10,"")),"")
と入力して、「Ctrl」＋「Shift」＋「Enter」で確定して下方にフィルコピーとかでどうでしょうか？
（ウッシ） 2015/06/02(火) 16:44
今回の質問で、私のスキルの程度は把握できるかもしれませんが
配列数式はまだノータッチなのです。。
関数に依存せずに、これからも精進します。
これから回答いただいた数式を解析・学習します。
（equity） 2015/06/02(火) 16:58
★図３(ウッシさんの場合)
A B C D E F
1 [name] [money] [money2] [total2] [total3] [total] 2 a -10 2 -8 -8 -8 3 b 1 2 3 -2 ←ココ 4 b -10 2 -8 -5 5 b 1 2 3 3 3 ←ココ 6 c 1 2 3 3 3 ・ ・ ・ 10
★期待する結果
A B C D E F
1 [name] [money] [money2] [total2] [total3] [total] 2 a -10 2 -8 -8 -8 3 b 1 2 3 -2 -2 ←ココ 4 b -10 2 -8 -5 5 b 1 2 3 3 ←ココ 6 c 1 2 3 3 3 ・ ・ ・ 10
E列にマイナスがある場合は
0以下かつ最大値
という条件がポイントですね。
私も格闘中です。。
（equity） 2015/06/02(火) 17:12
表が崩れていて良く分からないのですが、
0以下かつ最大値
っていつ出た条件ですか？
（ウッシ） 2015/06/02(火) 17:18
表の崩れは、余計なお世話とは思いましたが、直しておきました。
(見にくくて、考えにくいので・・、・・けど仕様の解釈に確信が持てないので、思考自体停止中）
equityさんへ 表の場合、各行の左端に半角スペースを入れると整形されますので、 今後ご励行願います。
（半平太） 2015/06/02(火) 17:22
ウッシさん
「E列にマイナスがある場合は0以下かつ最大値」はF列に目的の
値を算出するためのロジックとして記入しました。
しかし、グループ内でE列にマイナスの値を含んでいても
F列にプラスの値が算出されることを期待する場合
(図３でB5が10の場合)などは、このロジックは通用しませんでした。。
F列が、グループ内でE列の最上部の値を拾ってくれれば
話は早いんですケド。。
（equity） 2015/06/03(水) 08:01
E2=SUMIF($A$2:$A$11,$A$2:$A$11,$D$2:$D$11)
F2=IF(E2<0,E2,IF(SUMPRODUCT(MAX(($A$2:$A$11=A2)*($E$2:$E$11)))=E2,SUMPRODUCT(MAX(($A$2:$A$11=A2)*($E$2:$E$11))),IF(AND(E2<0,E2>SUMPRODUCT(MIN(($A$2:$A$11=A2)*($E$2:$E$11)))),E2,"")))
あとはグループ内のF列の最上部以外を空白にするだけです。。
一応、上記の数式でモニタリング募集中です。
（equity） 2015/06/03(水) 08:35
F列の最上部以外を空白にするために
変更前のE列があったかもしれません(今、気付いた)。
（equity） 2015/06/03(水) 08:49
・A列グループのE列が0以下のみなら 0以下での最大値 ・ 〃 0より大きい数値のみなら単純に最大値
で、いいんだよね？
=IF(COUNTIF(A$2:A2,A2)=1,MAX(IF(A$2:A$10=A2,IF(COUNTIFS(A$2:A$10,A2,E$2:E$10,"<=0")=0,E$2:E$10,IF(E$2:E$10<=0,E$2:E$10)))),"")
CTRL+SHIFT+ENTER （GobGob） 2015/06/03(水) 09:56
F列が、グループ内でE列の最上部の値を拾ってくれれば 話は早いんですケド。。
あー。そういうこと。
F2 =IF(COUNTIF(A$2:A2,A2)=1,E2,"")
で、いいんでないの？
※てか、F列なしで E2 =IF(COUNTIF(A$2:A2,A2)=1,SUMIF(A2:A$10,A2,D2:D$10),"") でもいいね。 （GobGob） 2015/06/03(水) 10:10 ⇒ 10:22修正
・A列グループのE列が0以下のみなら 0以下での最大値 ・ 〃 0より大きい数値のみなら単純に最大値
申し訳ございません。
F列の値を算出するための、E列に対する私のロジックが間違っていました。。
F列の結果としては
A列グループのD列の合計を、グループ内の最上部のみに表示する
だけです。
「E列の最大値を探す」というのは
A列グループ内のF列の最上部以外を空白にするためだけの
ロジックとして考えていました。。
なのでE列を以下の数式と、GobGobさんから頂いたF列の数式を組み合わせて
図４のケースで試行すると、上記の私の期待した結果は得られませんでした。
E2=SUMIF(A2:$A$10,A2:$A$10,D2:$D$10)
★図４
A B C D E F
1 [name] [money] [money2] [total2] [total3] [total] 2 a -10 2 -8 -8 -8 3 b 1 2 3 7 12 ←ココが7になることを期待している 4 b -10 2 -8 4 5 b 10 2 12 12 ・ ・ ・ 10
ところが
E列を以下の数式と、GobGobさんから頂いたF列の数式を組み合わせて
上記と同じ図４のケースで試行すると、私の期待した結果が得られました！
E2=SUMIF($A$2:$A$10,$A$2:$A$10,$D$2:$D$10)
★図４
A B C D E F
1 [name] [money] [money2] [total2] [total3] [total] 2 a -10 2 -8 -8 -8 3 b 1 2 3 7 7 ←good! 4 b -10 2 -8 7 ←(空白も)good! 5 b 10 2 12 7 ←(空白も)good! ・ ・ ・ 10
いろいろなケースでテストしてますが、今のところ結果は良好です。
---------------------------
ここまで書き込み中
GobGobさんの
>※てか、F列なしで E2 =IF(COUNTIF(A$2:A2,A2)=1,SUMIF(A2:A$10,A2,D2:D$10),"") > でもいいね。
を確認しました。
コレです、コレ！。。ありがとうございました(笑)
（equity） 2015/06/03(水) 10:57
一応。。。
E列もなしに
D2 =IF(COUNTIF(A$2:A2,A2)=1,SUMIF(A:A,A2,B:B)+SUMIF(A:A,A2,C:C),"") でもOKのような。。。。 （GobGob） 2015/06/03(水) 11:13
>D2 =IF(COUNTIF(A$2:A2,A2)=1,SUMIF(A:A,A2,B:B)+SUMIF(A:A,A2,C:C),"") >でもOKのような。。。。
みるみる簡略化されてゆく。。(笑)
COUNTIFって便利ですね〜
ただ本番データはB列とC列だけではなく
約6個、列があります。
さらに基本的に2行を結合して1行のデータとして扱っており
結合されていない上段と下段のセルも混在している中で
合計を算出しているので
本番データの場合、F列なし止まりが個人的に良いかもです。
手動入力列が少ないor複雑ではない場合は
E列なしの構成が最高ですね！
（equity） 2015/06/03(水) 11:48 ⇒ 12:03修正
結合している片方のセルにデータは無いんで問題ないのでは？
=IF(COUNTIF(A$2:A2,A2)=1,SUMPRODUCT((A$2:A$10=A2)*B$2:G$10),"") （GobGob） 2015/06/03(水) 13:00
あと合計を算出するための複数の列は
連続していないんですよね。。
以下のように。。
B C D E F H
連続していない場合は、数式が長くなってしまうと
認識しています。
だったらF列なし止まりがキレイかな、と。
（equity） 2015/06/03(水) 13:32
データ列で途切れてる列が空白または文字列なら
=IF(COUNTIF(A$2:A2,A2)=1,SUMPRODUCT((A$2:A$10=A2)*1,B$2:H$10),"")
で、問題ないだろけど。どーなんだろね。
（GobGob） 2015/06/03(水) 13:39
★図5
A B C D E F G H I
1 [name] [money] [money2] [money3] [money4] [money5] [char] [money6] [total]
2 1 1
a 1 1 1 1 moji #VALUE! ←「8」を期待する！
3 1 1
-----------------------------
4 1 1
b 1 1 1 1 moji #VALUE! ←「16」を期待する！
5 1 1
-----------------------------
6 1 1
b 1 1 1 1 moji
7 1 1
-----------------------------
頂いた数式の
B$2:H$10
の部分に無理があるのではないかと認識しています。
（equity） 2015/06/03(水) 14:44
複数列だめっぽいね・・・（検証不足）
I2 =IF(COUNTIF(A$2:A2,A2)=1,SUMPRODUCT((A$2:A$11=A2)*(TEXT(B$2:H$11,"標準;-標準;0;!0")+TEXT(B$3:H$12,"標準;-標準;0;!0"))),"") （GobGob） 2015/06/03(水) 14:59
しかし図５のB〜H列のいずれかに
見た目のみ日付として表示させたい以下の
'06/04
を入力すると
I列で上記の日付を数値に変換し、
合計した値となってしまいます。
(B〜H列を選択して、ステータスバーに
表示されている合計値と異なっていることを確認しました)
頂いた数式の以下の部分が原因だと認識しています。
"標準;-標準;0;!0"
上記の「!0」の部分が文字列の場合の
扱い・表示方法と認識しています。。
(また「!」の意味が不明でした)
（equity） 2015/06/04(木) 09:04
日付（シリアル値）のみなら
I2 =IF(COUNTIF(A$2:A2,A2)=1,SUMPRODUCT((A$2:A$11=A2)*(B$1:H$1<>G$1)*(B$2:H$11+B$3:H$12)),"")
※シリアル値と文字列 いろいろあるならTEXT併用。 （GobGob） 2015/06/04(木) 09:19 ⇒ 9:29 間違い訂正
ちなみにTEXTの第２引数は関係ないよ。
mojiが文字列でなくシリアル値なんで計算されてしまってるだけ。 （GobGob） 2015/06/04(木) 09:31
また、計算に使用する数値以外の
数値、日付、文字列などに
「'」を付与する必要なく
正しい合計値が表示されています。
ただ、E列なしの構成にしてから
処理速度が落ちました。
具体的には。。
・手動入力箇所(図５の場合、A〜H列、2行目以下)のセルに
入力を行い、Enterあるいは他のセルをアクティブにする。
・手動入力箇所のセルの値をDeleteやBackspaceで削除する。
上記のいずれかを行うとステータスバーに
再計算：(プロセッサ ▲ 個)：■%
と表示されます。
本番データの行数は1000行、
2行を結合しているので500件のデータがあります。
しかし現在、入力されているのは6.5割ほどです。
また、印刷用に他のシートに50件ずつ(計10シート)以下の
数式で引っ張って表示させてます。
=INDIRECT(ADDRESS(ROW(),COLUMN(◆参照セル◆),4,,"◆参照シート◆"))
理由は50件ずつの合計値を算出したいからです。
メモリは8GBです。
以下のURL に記載されている方法で回避できましたが
データ入力者は初心者なので、手動で再計算させる手間を
できるだけ省きたいです。。
計算式が複雑でExcelが重い - インストラクターのネタ帳 http://www.relief.jp/itnote/archives/000046.php
そもそもE列なしの構成で使用する数式が原因かも分かりませんが。。
（equity） 2015/06/04(木) 13:23
まぁ、重くなるかもね。配列数式やから。 最初の質問から脱線してもーたし（レイアウト違いとか列数とか・・・）
COUNTIF使って 作業列＋SUMIF列でいいと思う。
あと、一応
＞ =INDIRECT(ADDRESS(ROW(),COLUMN(◆参照セル◆),4,,"◆参照シート◆"))
=INDEX(◆参照シート!$1:$1048576,ROW(),COLUMN())
でよさそうだけど。 （GobGob） 2015/06/04(木) 13:39
後継者にGobGobさん並のスキルの人が現れ、
簡略化・最適化してくれることを願うばかりです(笑)
以上、ありがとうございました。拍手！
（equity） 2015/06/04(木) 13:55
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.