EXCELで不良率を出そうと思ってるんですが・・・

質問

まずは下の表を見てください。

│ A │ B │ C │
──────────
1 │100│ 1 │ │　　
2 │ 0│ 2 │ │
3 │ 0│ 1 │ │
4 │ 0│ 3 │ │
5 │ 0│ 2 │ │
6 │ 30│ 0 │ │
7 │ 25│ 1 │ │

Aの列は良品の数ですBの列は不良品の数です Cの列に不良率の結果を出そうと思います。
A2,A3,A4,A5が0なのはA1からA5は同じ品物ですが不良の種類が違うので別の行に書かれています。
A1の品物のように不良の種類が5つになることもあるし、A6のように不良が無い事もあれば、A7のように不良の種類が１種類の事もあります。
それぞれの品物ごとに不良率を出したいので、C1からC4は空白でC5に=SUM(B1:B5)/(A1+SUM(B1:B5))の結果が出るようにしたいんです。
そして、C6は不良が無いので空白。C7は=B7/(A7+B7)の計算結果がでるようにしたいのです。
いくら考えてもなかなか うまくいく式が思い浮かびません。
知識のある方、どうぞよろしくお願いいたします。

回答No.6

maron--5 · ベストアンサー

◆こんな表示でよければ
　　　A　　　　B　　　　C
1　　100　　　1　　　0.082568807
2　　　0　　　2
3　　　0　　　1
4　　　0　　　3
5　　　0　　　2
4　　 30　　　0　　　0
5　　 25　　　1　　　0.038461538

C1=IF(A1<>0,SUM(OFFSET(B1,,,MATCH(1,INDEX(1/((A2:A10<>0)+(B2:B10="")),),0)))/(A1+SUM(OFFSET(B1,,,MATCH(1,INDEX(1/((A2:A10<>0)+(B2:B10="")),),0)))),"")
★下にコピー

お礼

あーっ！！　来たっ！

これですこれですっ

もー大助かりです　ありがとうございましたm(__)m

回答No.5

merlionXX

#1-4のmerlionXXです。
コードに抜けがありました。
差し替えてください。

Sub test02()

Dim c As Range

Rows("1").Insert Shift:=xlDown
Range("A1") = "良品"
Range("B1") = "不良品"
Range("C1") = "ratio"
Columns("A").Insert Shift:=xlToRight
Range("A1") = "品名"
i = 1
Range("A2") = "品名" & i

Set c = Range("A2").Offset(1, 0)

Do Until c.Offset(0, 1) = ""
If c.Offset(0, 1) <> 0 Then
i = i + 1
End If
c.Value = "品名" & i
Set c = c.Offset(1, 0)
Loop

Range("A1").CurrentRegion.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Set c = Range("A1").Offset(1, 0)

Do Until c.Value = ""
If Right(c.Value, 1) = "計" Then
c.Offset(0, 3).FormulaR1C1 = "=RC[-1]/(RC[-2]+RC[-1])"
c.Offset(0, 3).NumberFormatLocal = "0.00%"
End If
Set c = c.Offset(1, 0)
Loop

Set c = Nothing

End Sub

お礼

なんどもなんども懲りずに回答いただき
謝謝(-人)謝謝です。

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

回答No.4

merlionXX

#1のmerlionXXです。

> 表の作り方が間違っているのはわかってるんですが、
> すでに出来上がってしまっている表から不良率だけ出さないといけないので・・・

では、表を直しましょう。
かならずオリジナルのコピーを取ってから、以下の手順でやってみてください。

1.Altキー+F11キーでVisualBasicEditorを呼び出します。

2.メニューから挿入、標準モジュールで出てきたコードウィンド（右側の白い広い部分）に以下のコードをコピペします。

Sub test01()

Columns("A").Insert Shift:=xlToRight
Range("A1") = "品名"
i = 1
Range("A2") = "品名" & i

Set c = Range("A2").Offset(1, 0)

Do Until c.Offset(0, 1) = ""
If c.Offset(0, 1).Value <> 0 Then
i = i + 1
End If
c.Value = "品名" & i
Set c = c.Offset(1, 0)
Loop

Range("A1").CurrentRegion.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

Set c = Range("A1").Offset(1, 0)

Do Until c.Value = ""
If Right(c.Value, 1) = "計" Then
c.Offset(0, 3).FormulaR1C1 = "=RC[-1]/(RC[-2]+RC[-1])"
c.Offset(0, 3).NumberFormatLocal = "0.00%"
End If
Set c = c.Offset(1, 0)
Loop

End Sub

3.Alt+F11キーでワークシートへもどります.

4.メニューから、ツール、マクロ、マクロで出てきたマクロ名（test01）を選択して実行します。

これで集計行が入り、集計行で不良率を計算しています。
ただし、上記コードはあなたがお書きになったとおりにデータがA1からAとB列に切れ目無く入っていることが条件です。（普通、このような表には左側に品名があると思うのですが、提示された例ではないので、新たにれつを挿入し、A列の０の行は、その上のものと同じ品名を入れています。）

お礼

うわー　すごいっ

ありがとうございまーす

回答No.3

coffeebar

補助列を使わないと難しいように思います。

100が入っているセルをA2と仮定します。（質問文ではA1）
C2に
=IF(A2=0,C1,A2)

D2に
=IF(A2=0,D1+B2,B2)

E2に
=IF(OR(A3>0,A3=""),D2/C2,"")

を入力して、C列からE列を下まで、コピー。

補助列（C列とD列）が目障りであれば、選択した後、「右クリック」－「表示しない」で隠して下さい。

お礼

ありがとうございます。

そうかー　こんな方法でやるんですね。

回答No.2

DIooggooID

あまりスマートではありませんが、下記のようなマクロにすれば、
とりあえず実現可能かと思います。

Sub Macro1()
'
' Macro1 Macro
' マクロ記録日 : 2007/5/25 ユーザー名 : X
'
Range("A1").Select
Av = ActiveCell.Value
Bv = 0
Do Until ActiveCell.Value = ""
If (ActiveCell.Offset(0, 1).Value = 0) Then
ActiveCell.Offset(0, 2).Value = "■"
Av = 0
Bv = 0
Else
Bv = Bv + ActiveCell.Offset(0, 1).Value
If (ActiveCell.Offset(1, 0).Value <> 0 And _
ActiveCell.Offset(1, 0).Value <> "") Then
ActiveCell.Offset(0, 2).Value = Bv / (Av + Bv)
Bv = 0
End If
End If
ActiveCell.Offset(1, 0).Select
If (ActiveCell.Value <> 0) Then
Av = ActiveCell.Value
End If
Loop
If (ActiveCell.Offset(-1, 1).Value <> 0) Then
ActiveCell.Offset(-1, 2).Value = Bv / (Av + Bv)
End If
'
End Sub

お礼

早速のご返事ありがとうございます。

非常にありがたいんですが、マクロの事がさっぱりわからないんで
できましたら、式がいいんですが・・・
難しいですかね～

回答No.1

merlionXX

表の作り方が間違っているのでは？
途中に集計行をもうければいいのではないでしょうか。

お礼

早速のご返事ありがとうございます。

表の作り方が間違っているのはわかってるんですが、
すでに出来上がってしまっている表から不良率だけ出さないといけないので・・・