データの集計表で、オレンジ色の行は、下の行の合計を入れます。
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で質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 列を自動で追加したい 3 2022/07/11 12:58
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- Excel(エクセル) 特定文字(数字)で行挿入、挿入された行で合計したい 2 2023/03/13 14:30
- Excel(エクセル) エクセルで最初に値が入っているセルを見つける方法はありますか? 2 2023/07/18 14:58
- Visual Basic(VBA) 最終列の右へSUM関数を作成するため下記コードを実行しましたが、最終列「10月28日」が上書きされて 3 2022/12/05 20:32
- Excel(エクセル) 複数シートのデータ統合とレイアウトの維持についてです 2 2022/10/18 21:11
- Excel(エクセル) SUMIFのIF分岐について 4 2023/04/15 12:57
- Excel(エクセル) 別シートに毎回異なるデータをコピーする 7 2022/06/24 09:02
- Excel(エクセル) エクセルの関数について 5 2023/04/30 17:24
- Visual Basic(VBA) 特定の文字を条件に指定範囲のデータを貼り付けるVBA 3 2023/01/15 06:14
このQ&Aを見た人はこんなQ&Aも見ています
-
外出時に「待たせる妻」vs イライラする「待つ夫」は日本だけ?見習いたい海外事情
夫の家事参加に積極的なイメージのある海外でも、同様の事例はあるのか。結婚カウンセラーの佐竹悦子さんに伺ってみた。
-
excelのデータで色つき行の抽出方について教えてください
Excel(エクセル)
-
エクセル 背景色のついたセル位置を参考にして固定の数値を取得する
会計ソフト・業務用ソフト
-
Excel VBA で色付きのセルの値を取得する
Excel(エクセル)
-
-
4
Excelで色付けしたセルに対し対応するセル値で返す方法
Excel(エクセル)
-
5
VBマクロ 色の付いたセルを含む行をコピーしたい。
Visual Basic(VBA)
-
6
背景色の有無を判断をして別のセルに「1」と入力
Excel(エクセル)
-
7
【Excel】 セルの色での判断はできますか?
Excel(エクセル)
-
8
EXCEL VBA で指定した範囲に入力があるかどうか?
Visual Basic(VBA)
-
9
エクセルでエラーが出て困っています。
Excel(エクセル)
-
10
Excel 書式を関数で判断。
Excel(エクセル)
-
11
【Excel VBA】ネットワーク上の共有フォルダにあるExcelを開く
Excel(エクセル)
-
12
Excel マクロ:変数を複数使う場合
Excel(エクセル)
-
13
EXCEL VBA セルに既に入力されている文字に文字を追加する
Excel(エクセル)
-
14
Excelのセルの色指定をVBAから配列を用いて効率的に行う方法はあり
Visual Basic(VBA)
-
15
EXCELにて複数列を同条件(色)で一括フィルタリングする
Excel(エクセル)
-
16
3桁または4桁の数値を時刻に変換したい
Visual Basic(VBA)
-
17
Excelで一行おきに2行の空白行を挿入したい
その他(Microsoft Office)
-
18
VBAでファイルを開くときにファイル名でワイルドカードを使用したいです
その他(プログラミング・Web制作)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
別シートからの文字を変更
-
エクセルの行の抽出について質...
-
Excel 2019 のピボットテーブル...
-
Excelのセルを飛ばして入力する
-
【マクロ】エクセルにかいてあ...
-
Excelのオートフィル
-
Excel初心者です。 詳しい方、...
-
スプレッドシート クエリ関数 1...
-
MOS365 Excel Expert / Excel R...
-
西暦や和暦の表示をyyyymmdd表...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセルの数式で教えてください。
-
スプレッドシートの関数VLOOKUP...
-
エクセルでセルに「氏名を入力...
-
エクセルで指定した日付、店舗...
-
【Excel】セル内の時間帯が特定...
-
Excelのグラフ軸について
-
Excel 2019 は、SPILL機能があ...
-
関数を教えて下さい。
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報