VBA 最上行にMaxの値、それ以下の行には０表示

質問

VBAの勉強を始めて間がなく知識も乏しいため、なんとか（とりあえずでも）エクセル関数を使って出来ないものかと考えていましたが、難しそうです。

F列にあいうえお…わをん等のランダム文字列が入っています。
H列には1か0が入っています。
（少ないときは5000行、多い時で10000行のデータ量です）

F列が例えば、あ、か、さ、た、な、は、ま、や、ら、わ、の時
最上行のH列にMAXの値（1か0しかないですが）を表示し、それ以下の行にはすべて0と表示させたいです。（H列の値を書き換えたい）
F列が、あ、か、さ、た、な、は、ま、や、ら、わ、以外の文字列のときは元の値のままを表示です。

どうか、よろしくお願いします。

Ｆ　Ｇ　Ｈ
---------------
あ　　　０
あ　　　１
あ　　　０
あ　　　０
あ　　　０
---------------
か　　　０
か　　　０
---------------
さ　　　１
さ　　　１
さ　　　０
さ　　　１

↓

Ｆ　Ｇ　Ｈ
---------------
あ　　　１
あ　　　０
あ　　　０
あ　　　０
あ　　　０
---------------
か　　　０
か　　　０
---------------
さ　　　１
さ　　　０
さ　　　０
さ　　　０

回答No.2

tom04 · ベストアンサー

こんにちは！
一例です。

＞F列が例えば、あ、か、さ、た、な、は、ま、や、ら、わ、の時・・・

とありますので、Sheet2のA列に条件データ（あ・か・さ・・・）を入力しておきます。

元データはSheet1の2行目以降にあるとして、

Sheet1のSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
Application.ScreenUpdating = False
For i = 2 To Cells(Rows.Count, 6).End(xlUp).Row
If WorksheetFunction.CountIf(ws.Columns(1), Cells(i, 6)) Then
If WorksheetFunction.SumIf(Columns(6), Cells(i, 6), Columns(8)) > 0 And _
WorksheetFunction.CountIf(Range(Cells(2, 6), Cells(i, 6)), Cells(i, 6)) = 1 Then
Cells(i, 8) = 1
Else
Cells(i, 8) = 0
End If
End If
Next i
Application.ScreenUpdating = True
End Sub 'この行まで

※　一旦マクロを実行すると元に戻せませんので、別Sheetでマクロを試してみてください。

こんなんで参考になりますかね？m(_ _)m

お礼

ありがとうございます！！
簡単に出来ました。

これならもしデータの量が今後増えても問題ないですね。
条件が変わったらsheet2のデータを変えるだけでいいので、今後も助かりそうです。

内容の一部、実はぼんやりとしか理解出来ていないのですが（参考書が手元にないので）
ひとつ一つ自分で理解し、今後に生かしたいと思います！！

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

回答No.5

KURUMITO

回答Ｎｏ4です。
Ｊ2セルへの入力の式は次のように訂正してください。

=IF(F2="","",IF(AND(CODE(F2)<9250,CODE(F2)>9327),H2,IF(COUNTIF(F$2:F2,F2)=1,MAX(INDEX(H$2:H$10000,ROW(A1)):INDEX(H$2:H$10000,ROW(A1)+COUNTIF(F$2:F$10000,F2)-1)),0)))

お礼

ありがとうございます。

まず、すみません。あかさたなの条件は例なのでcodeがつかえません。。
or関数に変えて条件を入力すればつかえそうな感じですね。

今回色々な式の勉強になりました。ありがとうございました。

回答No.4

KURUMITO

データがＦ，Ｇ，Ｈ列の2行目から下方に入力されているとします。
例えばＪ列を作業列としてＪ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(F2="","",IF(AND(CODE(F2)>=9250,CODE(F2)<=9327,COUNTIF(F$2:F2,F2)=1),MAX(INDEX(H$2:H$10000,ROW(A1)):INDEX(H$2:H$10000,ROW(A1)+COUNTIF(F$2:F$10000,F2)-1)),0))

Ｊ列を選択して右クリックして「コピー」、その後にＨ1セルをアクティブセルにしてから右クリックして「形式を選択して貼り付け」から「値」にチェックをして貼り付けます。
その後にＪ列を削除します。

回答No.3

keithin

○あ，か，さ，た，な…の時に発動すること，それ以外はＨ列の値のまま
○同じ文字の２番目以降は必ずゼロになること
○同じ文字の先頭１つは，同じ文字のグループに１が１つ以上あれば１に，無ければゼロに

Ｉ１に
=IF(OR(F1={"あ","か","さ","た","な"}),IF(AND(SUMIF(F:F,F1,H:H)>0,MATCH(F1,F:F,0)=ROW(F1)),1,0),H1)
と記入して下端までコピー，コピーして型式を選んで値のみ貼り付けて値化します。

数式作成時に誤記しないようによく注意して作成してください。
マクロで行いたいなら，数式の計算ロジックをそのままマクロに応用してください。

Ｆ列が必ず並べ替えてあればもうちょっと高速になります
マクロを使い，
１．現在の姿に通し番号を振ってから
２．Ｆ列で並べ替え
３．各対象先頭行および続きの行についてそれぞれ上述の計算を行い
４．通し番号で並べ戻す
といった手口も研究してみては如何でしょう。

お礼

ありがとうございます！！

関数でも出来るんですね。
条件が実は40項目くらい（文字数も５～８文字程度）あるので、ものすごく長い式になってしまいましたが…。
関数の組み合わせが思いつかず行き詰っていましたが大変勉強になりました。

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

回答No.1

LHS07

「さ」は1つだけ1になっていますが、これおかしくないですか？

全部、書き換えてしまっていいのですか？
入力した値と変換値が参照できるようにＪ、Ｌに表示させたほうがいいのではないですか？
または、別のシートに表示させたほうがいいのではないですか？

補足

LHS07さん、ありがとうございます。

全部書き換えたいのです。現在、目視で判断して手入力で書き換えています。
ほかの列に表示でも大丈夫です。（後でH列を削除すればいいだけですので）

「さ」のH列に1が1つ入っていようと、100入っていようと、「さ」の最上行には1で、それ以下の行は0にしたいのです。

例が分かりにくくてすみません。。
よろしくお願いします。