空白セルの集計方法

質問

1 A　　　　　　　　B　　　　　　C　　　　　　　D　　　　　　　E　　　　　　　F
2 名前　　　　　時間帯　　　射撃数　　　　　　　　　　　　　　　　　　合計射撃数
3　山田　　　　　0時　　　　　20　　　　　　　　　　　　　　山田　　　　　
4　　　　　　　　　1時　　　　　30　　　　　　　　　　　　　　高橋
5　　　　　　　　　2時　　　　　15　　　　　　　　　　　　　　鈴木
6高橋　　　　　　4時　　　　　 3
7　　　　　　　　　5時　　　　　 5
8　　　　　　　　 13時　　　　　12
9　　　　　　　　 23時　　　　　45
10　　　　　　　 21時　　　　　12
11　　　　　　　 22時　　　　　33

F3～F5に山田さん～鈴木さんの時間帯を合計した射撃数を関数で入力したいと思います。
２通りの考え方があると思うのですが、教えていただけないでしょうか。

(1)A列の空白の箇所に関数を使いD列に名前を反映させれば、F3～F5にSUMIFの関数が使えると
考えてます。ただ表示させる関数がわからないので教えてください。

(2)OFFSET関数をF3～F5に他の関数を組み合わせてつかえば何とか反映できるのでは？
と考えてます。※OFFSET関数でなくてもいいです。

上記は一部抜粋したものでデータ数がかなりあるので関数を教えていただけると大変助かります。

質問の内容がわかりづらいかもしれませんが、
どちらの方法でもいいので宜しくお願い致します。

回答No.9

Musaffah

てゆっか、単純にこれでよくない？
VBA使うと起動時にいちいち確認ポップアップ出てくるの面倒くさいし。

まず、F3セルに
“=IF(E3="", "", SUMIF(A:A,E3,C:C))”
と書きます。

あとはF4セルから気が済む場所までコピペすればできますよ。

E列に名前を追加したらその場で計算してくれるし。

回答No.8

tom04

こんばんは！
すでに回答は出ていますので、参考程度で・・・

一例です。↓の画像のようにD列を作業用の列としています。
作業列D2セルに
=IF(B2="","",IF(A2="",D1,A2))
という数式を入れ、オートフィルでずぃ～～～！っと下へコピーしておきます。

E2セルに
=IF(COUNTA($A$2:$A$1000)<ROW(A1),"",INDEX($A$2:$A$1000,SMALL(IF($A$2:$A$1000<>"",ROW($A$1:$A$999)),ROW(A1))))
これは配列数式になってしまいますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

F2セルに
=IF(E2="","",SUMIF(D:D,E2,C:C))
（これは配列数式ではありません）
として、E2・F2セルを範囲指定しF2セルのフィルハンドルで下へコピーすると画像のような感じになります。

尚、余計なお世話かもしれませんがVBAでの方法として・・・
画面左下にある操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i, j, k As Long
Dim vl As Variant
k = Cells(Rows.Count, 5).End(xlUp).Row
If k > 1 Then
Range(Cells(2, 5), Cells(k, 6)).Delete (xlUp)
End If
Columns(1).Insert
For i = 2 To Cells(Rows.Count, 3).End(xlUp).Row
If Cells(i, 2) <> "" Then
Cells(i, 1) = Cells(i, 2)
Cells(Rows.Count, 6).End(xlUp).Offset(1) = Cells(i, 2)
Else
Cells(i, 1) = Cells(i - 1, 1)
End If
Next i
For j = 2 To Cells(Rows.Count, 6).End(xlUp).Row
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 1) = Cells(j, 6) Then
vl = vl + Cells(i, 4)
End If
Next i
Cells(j, 7) = vl
vl = 0
Next j
Columns(1).Delete
End Sub 'この行まで

長々と失礼しました。m(__)m

回答No.7

kagakusuki

万が一、A列に名前を書き忘れたり、書いてある名前を誤って消してしまったりした場合、集計結果がめちゃくちゃになり、修復も困難になる恐れがありますから、多少面倒ではあっても、ANo.3様の添付画像の様に、全ての行に名前を入力した方が良いと思います。
　但し、その場合の関数は、SUMPRODUCT関数だと処理計算の負荷が大きくなりますから、SUMIF関数を使用して、

=IF($E2="","",SUMIF($A:$A,$E2,$C:$C))

とした方が良いと思います。（F2セルの場合）

回答No.6

KURUMITO

同じ人が違った時間帯に再び射撃を始めたとしたらお示しの表のように同じ人物は固まった行で並べることはないでしょう。その場合でも対応でき、しかも名前を自動で表示できるようにするには作業列を作って対応するのがよいでしょう。
例えばお示しの表が1行目は項目名で2行目から下方にデータがあるとして、D2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(AND(A2<>"",COUNTIF(A$2:A2,A2)=1),MAX(D$1:D1)+1,IF(AND(A2<>"",COUNTIF(A$2:A2,A2)>1),INDEX(D$1:D1,MATCH(A2,A:A,0)),IF(AND(A2="",B2<>""),D1,"")))

お求めの表はEおよびF列に表示させるとしてE2セルには次の式を入力してF2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>MAX($D:$D),"",IF(COLUMN(A1)=1,INDEX($A:$A,MATCH(ROW(A1),$D:$D,0)),IF(COLUMN(A1)=2,SUMIF($D:$D,ROW(A1),$C:$C),"")))

同じ人が全く異なった時間帯で射撃を行ったとして全く離れた行にデータが入力されようが名前が追加されようが自動的に結果が表示されるようになります。

回答No.5

mshr1962

(1)の場合
D3=IF(A3="",D2,A3)
F3=IF(E3="","",SUMIF(D:D,E3,C:C)

(2)の場合
質問の条件（同じ名前）の範囲をOFFSETで指定することは無理です。
OFFSET関数は同じ規則の間隔でセル幅を変更する場合には有効ですけどね。
上記がC3を起点に3行ずつの合計というパターンであれば
F3=SUM(OFFSET($C$3,(ROW()-3)*3,0,3,1))

回答No.4

imogasi

したいことの説明が不十分では。
下記の（１）（２）の条件が説明されて無い。質問標題が聞きたいことを十分表してなくて判りにくい
（１）A列は山田さんなら山田さんの行が固まって存在する
（２）A列は同じ山田さんなら第２行目以降は（同じという意味で）空白となっている。
こういう入れ方をするのはエクセルをよく知らない人のすること。あと何かをする時の、式が大変。
それなら
例データ　A2:E１２　－は空白の代わりに示したもの　E列は関数の結果
名前 時間帯 射撃数 合計射撃数
山田 0時 20 山田 65
- 1時 30 高橋 20
- 2時 15 鈴木 90
高橋 4時 3 以上
- 5時 5
- 13時 12
鈴木 23時 45
- 21時 12
- 22時 33
以上
ーーー
A列データの最後に「以上」を入れる。「以上」ではなく、誰かの出てこない名前でも良い。
それをD列の名前の最後に入れておく。
D列はデーターフィルターフィルタオプションの設定ー重複するレコードは無視するで出せる。
E3の式は
=SUM(OFFSET($C$1,MATCH(D3,$A$1:$A$100,0)-1,0,MATCH(D4,$A$1:$A$100,0)-MATCH(D3,$A$1:$A$100,0),1))
下方向に式を複写する。E3で複写を止める
結果
上記E列の通り

回答No.3

noname#204879

１．範囲 A2:A10 を選択
２．[編集]→[ジャンプ]→[セル選択]で“空白セル”に目玉を
　　入れて[OK]をクリック
３．空白セルの最上(A3)がアクティブになっていることを確認
　　して、キーボードから =a2 と入力したままで Ctrl+Enter
　　をパシーッ
　　添付図のＡ列にその結果を示している。
４．セル F2 に式 =SUMPRODUCT((A$2:A$10=E2)*(C$2:C$10))
　　を入力して、此れを下方にズズーッとドラッグ＆ペースト

回答No.2

keithin

＞D列に名前を反映

方法１
D3に
=IF(C3="","",IF(A3="",D2,A3))
と入れて下向けにずらっとコピーして入れておきます。
F列には
=SUMIF(D:D,E3,C:C)
のように式を入れます

ただし，実際のエクセルの画面はあなたがご質問に書いたサンプルと１行ずれていて，ABCの行が１行目ではないので，式を入れる場所と入れる数式を勘違いしないよう，（こうしなさいといわれたからそうしましたじゃなく）式をちゃんと理解してから作業してください。

方法２
A列のセル範囲を山田以下まとめて選択
Ctrl+Gを押す
現れたダイアログでセル選択のボタンをクリック
現れたダイアログで空白セルにマークしてOK
すると山田や高橋を除いた空白のセルが飛び飛びに選択され，
山田の一つ下のセル（A4またはA3)がアクティブセルになっているので，
数式バーに「一つ上のセルを参照する式」を記入し，コントロールキーを押しながらEnterします
つまりアクティブセルがA4なら
=A3
を入れるということです

さらにA2セルからリスト下端までのセル範囲を選択し，
ご利用のエクセルのバージョンが不明ですが条件付き書式(2003までなら書式メニュー，2007以降ではホームタブ）を開始して，2003までならセルの値が→数式がに変更して，2007以降なら数式を使用して
=A2=A1
の時に書式ボタンでフォントの色をセルの塗り色を同じ（白）に設定しておきます
F列には
=SUMIF(A:A,E3,C:C)
のように式を入れます。

回答No.1

noname#187541

こんにちは。

Ｄ列に名前を反させる方法です。D3に　=IF(A3<>"",A3,OFFSET(D3,-1,))　と入れて、必要なところまでコピーしてください。

お礼

シンプルでわかりやすかったです。ありがとうございます。