みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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のように不良の種類が1種類の事もあります。
それぞれの品物ごとに不良率を出したいので、C1からC4は空白でC5に=SUM(B1:B5)/(A1+SUM(B1:B5))の結果が出るようにしたいんです。
そして、C6は不良が無いので空白。C7は=B7/(A7+B7)の計算結果がでるようにしたいのです。
いくら考えてもなかなか うまくいく式が思い浮かびません。
知識のある方、どうぞよろしくお願いいたします。

投稿日時 - 2007-05-25 09:19:36

QNo.3028569

困ってます

質問者が選んだベストアンサー

◆こんな表示でよければ
   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)))),"")
★下にコピー

投稿日時 - 2007-05-26 06:23:24

お礼

あーっ!! 来たっ!

これですこれですっ

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

投稿日時 - 2007-05-26 08:14:13

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(6)

ANo.5

#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

投稿日時 - 2007-05-25 22:10:21

お礼

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

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

投稿日時 - 2007-05-26 08:13:06

ANo.4

#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列の0の行は、その上のものと同じ品名を入れています。)

投稿日時 - 2007-05-25 19:20:45

お礼

うわー すごいっ

ありがとうございまーす

投稿日時 - 2007-05-26 08:11:36

ANo.3

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

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列)が目障りであれば、選択した後、「右クリック」-「表示しない」で隠して下さい。

投稿日時 - 2007-05-25 18:22:31

お礼

ありがとうございます。

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

投稿日時 - 2007-05-26 08:10:20

ANo.2

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

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

投稿日時 - 2007-05-25 14:47:44

お礼

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

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

投稿日時 - 2007-05-25 15:27:32

ANo.1

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

投稿日時 - 2007-05-25 11:15:19

お礼

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

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

投稿日時 - 2007-05-25 15:25:10

あなたにオススメの質問