Excel2000 でこんな集計は可能ですか・・・？

質問

Excel2000で、書式が設定されているセル(例えば数字が赤字になっているセル)
だけを集計、または抽出することはできないでしょうか？

ぜひぜひ ご教授いただきたく よろしくお願いします。<m(__)m>

回答No.7

kazuhiko5681 · ベストアンサー

こんばんわ。マクロの記述ミスをしてしまいましたので、こちらのマクロを使って下さい。

・データの入っているブックを立ち上げ、ＡＬＴ＋Ｆ１１キーを押してＶＢＥ画面を表示させ、画面左上のVBAProjectと書かれている上にマウスポインターを合わせて右クリック後、挿入→クラスモジュールを順にクリックし、表示された右側の白い部分に下記のコードをコピー＆ペーストする。

Public WithEvents App As Application

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim myRow As Integer
Dim myRange1 As Range
Dim myRange2 As Range
Dim myCell1 As String
Dim myCell2 As String

If Target.End(xlToLeft).Value <> "納入済合計金額" _
And Target.End(xlToLeft).Value <> "未納合計金額" Then Exit Sub
myRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set myRange1 = ActiveSheet.Range("A3:A" & myRow)
myCell1 = ActiveSheet.Cells(3, Target.Column).Address
myCell2 = ActiveSheet.Cells(myRow, Target.Column).Address
Set myRange2 = ActiveSheet.Range(myCell1 & ":" & myCell2)
If Target.End(xlToLeft).Value = "納入済合計金額" Then
Select Case Target.Column

Case 2
ActiveSheet.Range("H2").Value = Application.SumIf(myRange1, "納入済合計金額", myRange2)
Case 3
ActiveSheet.Range("H3").Value = Application.SumIf(myRange1, "納入済合計金額", myRange2)
Case 4
ActiveSheet.Range("H4").Value = Application.SumIf(myRange1, "納入済合計金額", myRange2)
Case 5
ActiveSheet.Range("H5").Value = Application.SumIf(myRange1, "納入済合計金額", myRange2)
Case 6
ActiveSheet.Range("H6").Value = Application.SumIf(myRange1, "納入済合計金額", myRange2)

End Select
Else
Select Case Target.Column

Case 2
ActiveSheet.Range("I2").Value = Application.SumIf(myRange1, "未納合計金額", myRange2)
Case 3
ActiveSheet.Range("I3").Value = Application.SumIf(myRange1, "未納合計金額", myRange2)
Case 4
ActiveSheet.Range("I4").Value = Application.SumIf(myRange1, "未納合計金額", myRange2)
Case 5
ActiveSheet.Range("I5").Value = Application.SumIf(myRange1, "未納合計金額", myRange2)
Case 6
ActiveSheet.Range("I6").Value = Application.SumIf(myRange1, "未納合計金額", myRange2)

End Select
End If

End Sub

・次に画面左上のVBAProjectと書かれている下のThisWorkbookをダブルクリックし、表示された右側の白い部分に下記のコードをコピー＆ペーストする。

Dim myClass As New Class1

Private Sub Workbook_Open()

Set myClass.App = Application

End Sub

ここまでの作業が終了したら、１度ブックを保存して終了し、再度ブックを立ち上げます。

データを入力してみて下さい。貴方様の思い通りの動作が確認できると思います。

なお、各シートのＨ２に第１週目の全商品の納入済金額の合計・Ｉ２に第１週目の全商品の未納金額の合計・Ｈ３に第２週目の全商品の納入済金額の合計・Ｉ２に第２週目の全商品の未納金額の合計・・・・というように表示されます。

ご不明な点・不具合等がありましたら、ご遠慮なくお知らせ下さい。私のわかる範囲でご一緒に考えていきたいと思います。

お礼

kazuhiko5681さん、こんにちは。

素晴らしいマクロをどうもありがとうございます！！(感涙)
とっても嬉しいです～。(^。^)
早速、登録させて頂きます。
お手数おかけ致しまして、申し訳ございませんでした。
kazuhiko5681さんの才能に 心からお礼申し上げます。
本当にありがとうございました。<m(__)m>

回答No.8

taisuke555

＃５で回答したものです。

よく見ると未納が黒字で、納入済みが赤字ですね。
納入済みを－で入力よりは、

書式設定を　[赤]#,##0;#,##0
にしてもらえば、未納を－入力すると黒字で表示し、納入済みは普通に入力すると
赤字になります。

お礼

taisuke555さん、こんにちは。
ご回答、ありがとうございました。

実はこのファイル、手入力・リンク貼り付け・vlookup、などが混在しているファイルなんです…。(汗)
というのも、このファイルは複数の人間がupdateしているファイルで、
その人によって数字の入れ方がまちまちの様です。
（私自身、自分で最初から作ったファイルではないので、よく分かっていないんですけど(苦笑)）
で、納入された物を赤字に変えるのだけが私の役目でして…。

今回の質問とは別のファイルで、また色を分けて集計しているファイルがありまして、
そちらのファイルでtaisuke555さんに教えて頂いた方法を使わせて頂こうと
思っております。

丁寧な回答を頂きまして、どうもありがとうございました。

回答No.6

kazuhiko5681

こんにちは。遅くなって申し訳ございません。サンプルマクロが出来上がりました。次のように操作します。

・データの入っているブックを立ち上げ、ＡＬＴ＋Ｆ１１キーを押してＶＢＥ画面を表示させ、画面左上のVBAProjectと書かれている上にマウスポインターを合わせて右クリック後、挿入→クラスモジュールを順にクリックし、表示された右側の白い部分に下記のコードをコピー＆ペーストする。

Public WithEvents App As Application

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim myRow As Integer
Dim myRange1 As Range
Dim myRange2 As Range
Dim myCell1 As String
Dim myCell2 As String

If Target.End(xlToLeft).Value <> "納入済合計金額" _
And Target.End(xlToLeft).Value <> "未納合計金額" Then Exit Sub
myRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Set myRange1 = ActiveSheet.Range("A3:A" & myRow)
myCell1 = ActiveSheet.Cells(3, Target.Column).Address
myCell2 = ActiveSheet.Cells(myRow, Target.Column).Address
Set myRange2 = ActiveSheet.Range(myCell1 & ":" & myCell2)
If Target.End(xlToLeft).Value = "納入済合計金額" Then
Select Case Target.Column

Case 2
ActiveSheet.Range("H2").Value = Application.SumIf(myRange1, "納入済合計金額", myRange2)
Case 3
ActiveSheet.Range("H3").Value = Application.SumIf(myRange1, "納入済合計金額", myRange2)
Case 4
ActiveSheet.Range("H4").Value = Application.SumIf(myRange1, "納入済合計金額", myRange2)
Case 5
ActiveSheet.Range("H5").Value = Application.SumIf(myRange1, "納入済合計金額", myRange2)
Case 6
ActiveSheet.Range("H6").Value = Application.SumIf(myRange1, "納入済合計金額", myRange2)

End Select
Else
Select Case Target.Column

Case 2
ActiveSheet.Range("I2").Value = Application.SumIf(myRange1, "未納合計金額", myRange2)
Case 3
ActiveSheet.Range("I3").Value = Application.SumIf(myRange1, "未納合計金額", myRange2)
Case 4
ActiveSheet.Range("I4").Value = Application.SumIf(myRange1, "未納合計金額", myRange2)
Case 5
ActiveSheet.Range("I5").Value = Application.SumIf(myRange1, "未納合計金額", myRange2)
Case 6
ActiveSheet.Range("I6").Value = Application.SumIf(myRange1, "未納合計金額", myRange2)

End Select
End If

End Sub

・次に画面左上のVBAProjectと書かれている下のThisWorkbookをダブルクリックし、表示された右側の白い部分に下記のコードをコピー＆ペーストする。

Private Sub Workbook_Open()

Set myClass.App = Application

End Sub

ここまでの作業が終了したら、１度ブックを保存して終了し、再度ブックを立ち上げます。

データを入力してみて下さい。貴方様の思い通りの動作が確認できると思います。

なお、各シートのＨ２に第１週目の全商品の納入済金額の合計・Ｉ２に第１週目の全商品の未納金額の合計・Ｈ３に第２週目の全商品の納入済金額の合計・Ｉ２に第２週目の全商品の未納金額の合計・・・・というように表示されます。

ご不明な点・不具合等がありましたら、ご遠慮なくお知らせ下さい。私のわかる範囲でご一緒に考えていきたいと思います。

回答No.5

taisuke555

＃３の補足を見て思ったのですが・・・

現在の発注No.1～発注No.3までは、赤字が入るということですよね？
どのように入力するのですか？

手入力しているのであれば、こんな方法はいかがでしょうか

（１）発注No.1～発注No.3の入力セル（この例ならB5～E7)を
　　　セルの書式設定→表示形式→ユーザー定義「#,##0;[赤]#,##0」と設定する

　　　●未納数は「-700」のように入力すると赤字で「700」と表示されます。

（２）納入済数量合計のセル（B8セル）
　　　=SUMIF(B5:B7,">0")
　　　と入力すると＋の合計（納品済みの合計）が表示されます

　　　C8～E8へコピー貼り付けする

（３）未納数量合計のセル（B9セル）
　　　=ABS(SUMIF(B5:B7,"<0"))
　　　と入力すると－の合計（未納合計）の絶対値が表示されます

　　　C9～E9へコピー貼り付けする

（４）納入済合計金額のセル（B10セル）
　　　=$B$4*B8
　　　と入力すると単価×納品済数量が表示される

　　　C10～E10へコピー貼り付けする

（５）未納合計金額のセル（B11～E11)

　　　B10をコピー貼り付けする
　　　（B11セルは「=$B$4:B9」になる）

で要望の事は出来ると思います。

このような「－」入力がだめなら仕方ありませんが、
入力が楽のような気がしますので試してみてください。

回答No.4

kazuhiko5681

こんにちは。補足有難うございました。この内容であれば、マクロを使わなくてもSumif関数を使えば計算できると思います。

まず、Ｂ列の最終行に、納入済合計金額の合計を出してみましょう。

・Ｂ列の最終行に
=sumif($A$4:$A$最終行番号,"納入済合計金額",B4:Ｂ列の最終行番号)
と入力します。
この式を横に必要な列数分コピーします。

未納合計金額の合計を出す時には、上の式の"納入済合計金額"を"未納金額"と変更して同じ作業をすれば、それぞれの週の合計金額を求めることができます。お試しになってみて下さい。
マクロにつきましては、ただいま作成中です。でき次第送らせていただきます。

お礼

なるほど、集計するときはSUMIF関数を使えばいいんですね…。
そこまで頭が回っていませんでした。(^^ゞ
さっそく試してみます。
どうもありがとうございました。

回答No.3

kazuhiko5681

初めまして。もしご興味があるようでしたら、サンプルマクロを組んでみたいと思います。ご希望の節は、貴方様のおやりになりたいことを具体的に詳しく教えて下さい。

お手数をおかけいたしますが、よろしくお願いいたします。

補足

はじめまして、お返事ありがとうございます。
サンプルマクロを組んで頂けるのですか？！
ありがとうございます～。m(__)m
ぜひぜひお願いしたいので、下記に現在使用しているリストの概略をご説明いたします。

　　　　A列　　　　B列　　　C列　　　D列　　　E列
１　　　　　　　　第○週　　第●週　　第△週　第▲週（→1行目は見出しです）
２　　　【空白行】
３　　　--- 商品A ---
４　　　単価　　　200　　　204　　　207　　　209 （→単価は週によって変動します）
５　　　発注No.1　1000　　　　　　　　　　（→5～7行目は各発注No.の数量）
６　　　発注No.2　　　　　3500　　　900
７　　　発注No.3　800　　　　　　　　　　　　700
８　　　納入済数量合計
９　　　未納数量合計
10　　　納入済合計金額
11　　　未納合計金額
12　　　【空白行】
13　　　--- 商品B ---
14　　　～以下、商品Aと同様仕様(単価のみ異なる)～

・既に納入された数量：赤・太字、単価：黒・太字、合計数量＆金額：黒・太字
　その他の見出しには何らかの色がついています。
・何の書式も設定されていないのは、未納数量のみです。
・商品の種類は約20種類で、1枚のシートに各商品ごとに空白行をはさんで上記のようなリストがあります。
・単価は商品ごと・週ごとに異なります。
・最終的には、各週ごとに全商品の未納金額と納入済金額の合計を求めたいです。

ちなみにこのリストで一番知りたいのは(箇条書きの一番下に書きましたが)、
各週ごと／全商品の未納金額と納入済金額の合計です。
それさえ分かれば、リストはどんな形式になっても結構です。

以上、こんな説明でお分かり頂けますでしょうか…？(汗)
分かりにくいと思いますので、何かございましたらご指摘頂ければと思います。
何卒、よろしくお願いいたします。<m(__)m>

回答No.2

uratan

抽出もできると思います。
ただ、抽出したデータをどのように使いますか？
どちらにせよ、マクロを使用します。

マクロについては説明が長くなるので、理解しているという仮定で書かせてもらいます。
マクロ中で、条件分岐を使います。
たとえば、指定された行（仮にA1～A30）の中の赤文字
（書式設定で文字色赤を指定してある）セル内容を抽出する場合の条件式は。

If Range("A1:A30").Font.Color = RGB(255, 0, 0) Then

でそのセルが赤文字かどうか判断できます。
この条件にあてはまったのものを、シート上に抽出するか、ファイルに出力するかすればいいと思います。

説明が悪くてすみません。

お礼

説明が悪いだなんて、とんでもないです。
どうもありがとうございます。
抽出したデータは、ファイルに出力してメールに添付して送信する、又はプリントアウトして配る予定でした。
いずれにせよ、マクロになるわけですね…。
分かりました、勉強します！
ご回答、ありがとうございました。m(__)m

回答No.1

Hageoyadi

集計だけなら、参考URLでその方法が説明されてます。

参考URL：

http://excel.internet.ne.jp/e1k/e1k.html

お礼

早速のご回答、ありがとうございました。m(__)m
URL、参考にさせて頂きます。
ありがとうございました。