困っています。教えて下さい。

質問

A列に値が入力されていて、間に空白のセルがランダムに存在しています。

それを、C列のように抽出したいです。

条件は下記の通りに。

(1)空白のセルに、挟まれたセルを抽出する。

(2)空白のセルに、挟まれたセルで0だけの場合は除外する。

(3)空白のセルに、挟まれたセルで0と他の数値の混合の場合は抽出する。

(4)最終的に、上記の条件で抽出した値の平均値を算出したい。

IF関数を使うのか、マクロを組まなくてはならないのか分からなくなってしまいました。

回答No.3

mt2008 · ベストアンサー

抽出の部分をマクロでやってみました。ご参考まで

Sub Sample()
　　nLast = Range("A" & Rows.Count).End(xlUp).Row
　　nRow1 = 2
　　If Range("A2") = "" Then nRow1 = Range("A2").End(xlDown).Row

　　Do While (nRow1 <= nLast)
　　　　nRow2 = nRow1
　　　　If Cells(nRow1 + 1, 1) <> "" Then
　　　　　　nRow2 = Range("A" & nRow1).End(xlDown).Row
　　　　End If
　　　　If Application.WorksheetFunction.Sum(Range("A" & nRow1 & ":A" & nRow2)) > 0 Then
　　　　　　Range("C" & nRow1 & ":C" & nRow2) = Range("A" & nRow1 & ":A" & nRow2).Value
　　　　End If
　　　　nRow1 = Range("A" & nRow2).End(xlDown).Row
　　Loop
End Sub

お礼

思い通りのモノになりました。

感動です。

回答No.5

keithin

ちなみにマクロを使った方が，簡単は簡単です。

＃ただし
Ａ列には「数式じゃなく生数字で」データが記入してあるとして。

手順：
ブックを開く
ＡＬＴ＋Ｆ１１を押す
現れた画面で挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

sub macro1()
　dim h as range
　on error resume next
　for each h in range("A:A").specialcells(xlcelltypeconstants, xlnumbers).areas
　　if application.countif(h, "<>0") > 0 then
　　　h.offset(0, 2).value = h.value
　　　h.offset(0, 3).cells(1) = application.average(h)
　　end if
　next
end sub

ファイルメニューから終了してエクセルに戻る
ＡＬＴ＋Ｆ８を押し，マクロを実行する。

お礼

関数を使う方法と、マクロと方法は色々ですね。

勉強になります。

回答No.4

pc_knight

以下のマクロをお試し下さい。

Sub cyuusyutsu()
Dim r, rs, rf, rc As Long
Dim kei As Single
For r = 2 To Range("A65536").End(xlUp).Row + 1
If r = 2 Then
rs = r
End If

If Cells(r, "A") = "" Then
kei = 0
For rc = rs To r
kei = kei + Cells(rc, "A") '空白のセルに、挟まれたセル値の合計を求める
Next rc
If kei > 0 Then
'空白のセルに、挟まれたセル値の合計＞”０”をＣ列に転記開始
For rc = rs To r
Cells(rc, "C").Value = Cells(rc, "A")
Next rc
rs = r
Else
'空白のセルに、挟まれたセル値の合計＝”０”につき転記しない
rs = r
End If
End If
Next r
MsgBox "抽出終了"
End Sub

お礼

試してみました。

凄いです。ビックリしました。

回答No.2

KURUMITO

お示しのようにＡ2セルから下方にデータがあるとしてＢ列を作業列として使用します。
Ｂ2セルには次の式を入力して下方にドラッグコピーします。

=IF(OR(AND(ROW(A2)=2,A2<>""),AND(A1="",A2<>""),AND(A2<>"",A3="")),MAX(B$1:B1)+1,"")

お求めの抽出データはＣ列に表示させるとしてＣ2セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",IF(AND(COUNT(A1)=0,B2<>"",A3<>""),IF(SUM(A2:INDEX(A:A,MATCH(B2+1,B:B,0)))>0,A2,""),IF(AND(B2<>"",A3="",A2<>0),A2,IF(C1="","",A2))))

平均値は=AVERAGE(C:C)で求めることができます。
作業列が目障りでしたらＢ列を選択して右クリックして「非表示」を選択すればよいでしょう。

お礼

なるほど！

情報をありがとうございます。

試してみます。

回答No.1

keithin

簡易版：
Ｃ２に
=IF(AND(A1="",A2<>""),AVERAGE(A2:OFFSET(A2,MATCH("",A2:A999&"",0)-1,0)),"")
と記入し、コントロールキーとシフトキーを押しながらEnterで入力、下向けにコピー。

挟まれた中にゼロしかない場合はゼロが計算されるので、ゼロになったモノは除外する。

＃ちなみにＡ２：Ａ３は空白に「挟まれていない」ので、計算対象ではありません。

＃参考
ゼロしかないのを除外するのがメンドクサイので、代わりにもっとメンドクサイ数式でも我慢できるなら。
Ｃ２に
=IF(AND(A1="",A2<>"",COUNTIF(A2:INDEX(A:A,MIN(IF(ISBLANK(A2:A9),ROW(A2:A9)))-1),"<>0")>0),AVERAGE(A2:OFFSET(A2,MATCH("",A2:A999&"",0)-1,0)),"")
と記入してCtrl+Shift+Enterで入力。以下コピー。

お礼

ありがとうございます。

ぜひ、参考にさせていただきます。