
データの集計表で、オレンジ色の行は、下の行の合計を入れます。
1行目.オレンジ色の行(Colorindex=44)
2行目.(無色)
3行目.(無色)
4行目.オレンジ色の行
5行目.(無色)
6行目.(無色)
7行目.(無色)
8行目.(無色)
9行目.オレンジ色の行
・
・
・
といった形に、ランダムに集計行があります。
さらに、
列A,列B,列C
大分類,中分類,売上金額
集計行(オレンジ色)
肉,(空白),1000
肉,生肉,500
肉,ハム,300
肉,ソーセージ,200
魚,(空白),750
魚,たい,450
魚,いか,200
魚,えび,100
集計行(オレンジ色)
野菜,(空白),1250
野菜,キャベツ,950
野菜,トマト,300
漬物,(空白)350
集計行(オレンジ色)
・
・
・
列Bが空白の場合は、その下の中分類の集計行なので、列Bが空白の行だけの合計を、列Cのオレンジ色のセルにSUM関数で集計したいのです。
また、次のオレンジの集計行まで、全ての列Bが空白の場合もあります。
まず、オレンジ色の行から、次のオレンジ色の行範囲を確認して、無色の行だけをSUM関数で集計できるのか、さらに列Bに文字が入っていた場合、それを除外してSUM関数で集計できるのか。を教えていただけますでしょうか。
以上宜しくお願い致します。
No.4ベストアンサー
- 回答日時:
こんにちは。
Wendy02です。早速の返事ありがとうございます。
このご質問は、今まで、類のないものです。もちろん、今まで出ていた内容を組み合わせて出来ますが、この処理を一括のユーザー定義関数で処理するというのは、思うほど簡単なものではありません。
出来上がったコードを見せられれば、何だ簡単とは思うかもしれませんが、今までにはナイモノをアルモノにするというのは簡単じゃありませんでしたね。なお、現在、縦に対して計算しますが、複数列は選べません。ちょっと手を加えると、縦横、どちらでも計算できるようになります。試行錯誤の作ですから、まだ、どこかヘンなところがあるかもしれません。
Function SUMIFCOLOR(範囲1 As Range, _
条件 As Variant, _
範囲2 As Range, _
Optional 条件色 As Integer)
Dim rnum As Long, cnum As Integer, i As Long, j As Integer
Dim sum As Double, SumArray() As Double, k As Long
Application.Volatile
If 範囲1.Count = 1 Or 範囲2.Count = 1 Then
If 範囲1 = 条件 Then
SUMIFCOLOR = 範囲2: Exit Function
Else
SUMIFCOLOR = 0: Exit Function
End If
End If
rnum = UBound(範囲1.Value, 1)
cnum = UBound(範囲2.Value, 2)
If Not ((rnum > 1 And cnum = 1) Or (rnum = 1 And cnum > 1)) Then
SUMIFCOLOR = CVErr(xlErrNum)
Exit Function
End If
For j = 1 To cnum
For i = 1 To rnum
If 範囲1.Cells(i, j).Interior.ColorIndex = 条件色 _
Or i = rnum Then
ReDim Preserve SumArray(k)
SumArray(k) = sum
sum = 0
k = k + 1
ElseIf 条件 = 範囲1.Cells(i, j).Value _
And InStr(2, 範囲2.Formula(i, j), "SUMIF", 1) = 0 Then
If VarType(範囲2.Cells(i, j)) = vbDouble Then
sum = sum + 範囲2.Cells(i, j).Value
End If
End If
Next i
Next j
SUMIFCOLOR = SumArray(LBound(SumArray))
End Function
ユーザー定義関数の取り付け方はご存知かと思います。
この式の使い方は、ちょっと工夫で便利になります。
B2:
=SUMIFCOLOR(B3:$B$17,"",C3:$C$17,44)
B3:$B$17
↑
データの最終行を選択します。
↑
相対参照で、1つ下を選びます。
この式を1つコピーして、後は全てどこに貼り付けても、計算します。
'============ 補足 =======================
なお、「集計行」という行を探すのでしたら、ユーザー定義関数はいりませんね。ただし、最後の集計行だけは、「集計行」という言葉が見つからないので、エラーになります。
B2に式を入れる場合
=SUMIF(B3:INDEX($A$1:$C$18,MATCH("集計行",$A3:$A$18,0)+ROW()-1,2),"",C3:INDEX($A$1:$C$18,MATCH("集計行",$A3:$A$18,0)+ROW()-1,3))
.
ご回答ありがとうございました。
まだまだ勉強中なので、いただいた回答を理解するのに、今まで時間がかかってしまいました。
すばらしいです!!決してお世辞などではなく、感動しております。
>なお、「集計行」という行を探すのでしたら
本当は、集計行にはその大分類の上の「部門名」が入るので、いただいた補足内容では今回に関しては処理は無理ですね。ですけど、これも非常に参考になります。
また、別の機会に活用させていただきます。
本当に、ありがとうございました。
No.3
- 回答日時:
時々この手の質問があるが、今のエクセルの関数の、原理的な点から、できません。
それは色のコードを関数で捕らえる関数がないことによります。そこで、そういう関数があるとか、ないとかいう回答が過去に出ました。旧いバージョンの関数を使うためです。
しかしないとしておきます。
簡単なVBAでユーザー関数を使うと、色コードを数値化できる関数が作れますのでそれを使えばよい。しかし、「色という書式」と「関数が扱う値というものは」世界が違うことを認識してください。
http://okweb.jp/kotaeru.php3?q=1006740
や「エクセル セル 色 カウント」などで照会すれば出てきます。
ありがとうございます。
色々なパスワードで検索したつもりでしたが、不足だったようで、たどりつけませんでした。
いつもお手数をおかけして申し訳ございません。
No.2
- 回答日時:
こんばんは。
Wendy02です。質問を読んでみて、疑問に感じたのは、「次のオレンジ色の行範囲を確認して、無色の行だけをSUM関数で集計」とある場合に、下側のセルに向かってというのは、ひじょうにやりにくいことに気がつき、再度確認したほうがよいと思いました。
中分類 売上金額
(オレンジ行)(1)
1000 ----
生肉 500 ↑
ハム 300 |
ソーセージ 200 この空白行の条件合計はどこに?
750 (1) か (2)か?
たい 450 |
いか 200 ↓
えび 100 -----
(オレンジ行) (2)
1250 -----
キャベツ 950 ↑ ここは、(2)か(3)か?
トマト 300 ↓
350 -----
(オレンジ行) (3)
ユーザー定義関数を作ってみて、その条件によってコードが換わります。ただし、上の表では、(1),(2),(3)の計算式を入れる場所はずれていますが、C列です。
この回答への補足
ご回答ありがとうございます。
おっしゃる通り、集計行のSUM(SUMIF)は下側を参照しています。
別の人間が作ったフォーマットに間違いがないように関数を入れるのが目的なので、このような形になっています。
集計行数がランダムな為、現状手作業で入れていくしかないのですが、それではミスが発生してしまいますし、作業カラムに集計行であることを判別するフラグを立てて、それで範囲を確定する方法もあるのですが、出来る限り現状のフォーマットを崩したくないのです。フォーマットを作った人がワガママなので(笑)
No.1
- 回答日時:
こんにちは。
Wendy02です。>列Bが空白の行だけの合計を、列Cのオレンジ色のセルにSUM関数で集計したいのです。
例えば、
C12:
=SUMIF(B4:B11,"",C4:C11)
というような具合で良いのではありませんか?
それとも、まだ、他にあるのかな?
この回答への補足
・・・おっしゃる通りですね。気づきませんでした(^^;
後、計算範囲を、オレンジ色の行から次のオレンジ色の行までにする方法さえ分かれば問題解決なのですが。
御礼;先日ご紹介いただきました、「かんたんプログラミング EXCELVBA」3冊とも購入しました。以降、本を頼りに大分一人で解決できるようになりました。ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【マクロ】【画像あり】4つの...
-
5単位で繰り上げしたい
-
Dir関数のDo Whileステートメン...
-
【マクロ】【画像あり】ファイ...
-
【マクロ】【画像あり】4つの...
-
9月17日でサービス終了らし...
-
【マクロ】エラー【#DIV/0!】が...
-
エクセルの循環参照、?
-
エクセル ドロップダウンリスト...
-
エクセルのリストについて
-
エクセルのdatedif関数を使って...
-
オートフィルターの絞込みをし...
-
【マクロ】数式を入力したい。...
-
【関数】同じ関数なのに、エラ...
-
空白のはずがSUBTOTAL関数でカ...
-
セルにぴったし写真を挿入
-
EXCELのVBAで複数のシートを追...
-
【マクロ】【画像あり】関数が...
-
エクセルシートの見出しの文字...
-
エクセルの関数について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【マクロ】元データと同じお客...
-
エクセルの関数について
-
【画像あり】オートフィルター...
-
エクセルのVBAで集計をしたい
-
エクセルのリストについて
-
【マクロ】数式を入力したい。...
-
【マクロ】【相談】Excelブック...
-
Office2021のエクセルで米国株...
-
【マクロ】実行時エラー '424':...
-
他のシートの検索
-
エクセルの複雑なシフト表から...
-
【マクロ】【配列】3つのシー...
-
vba テキストボックスとリフト...
-
【マクロ】左のブックと右のブ...
-
【マクロ】変数に入れるコード...
-
エクセルシートの見出しの文字...
-
【マクロ】別ファイルへマクロ...
-
【関数】同じ関数なのに、エラ...
-
Amazonでマイクロソフトオフィ...
-
ページが変なふうに切れる
おすすめ情報