特定条件を満たした場合にフラグを表示させるVBA

質問

ExcelVBAのコードを教えていただきたく質問いたしました。

処理の内容としては下記の通りとなります。
1.日付がA列、対応内容がB列、部材名がC列、部材費がD列、人件費がE列に入力されている。
2.人件費のみが発生した場合の対応内容が存在したとき、F列に人件費フラグとして「1」を表示させる。
3.対応内容により、人件費が同日で複数行発生した場合、部材費が発生していなければ複数行ごとに人件費フラグに「1」を表示させる。
4.1つの対応内容で部材費と人件費が発生した場合と部材費のみが発生した場合は、人件費フラグには何も表示させない。
5.同じ日付であっても、対応内容が異なれば、別の処理として人件費のみ発生しているか、部材費も発生しているかを調べ、上記1-4と同様に処理する。
6.日付は複数年月日が入力されるため数百行になることもありえる（1,000行未満を想定）。

処理のイメージ↓
┏━━━━━┳━━━━━━━━┳━━━┳━━━┳━━━┳━━━━━━┓
┃日付 ┃対応内容 ┃部品名┃部材費┃人件費┃人件費フラグ┃
┣━━━━━╋━━━━━━━━╋━━━╋━━━╋━━━╋━━━━━━┫
┃2023/11/1 ┃電話問合せ ┃ ┃ ┃5,000 ┃1 ┃
┣━━━━━╋━━━━━━━━╋━━━╋━━━╋━━━╋━━━━━━┫
┃2023/11/1 ┃故障対応 ┃ABC ┃10,000┃ ┃ ┃
┣━━━━━╋━━━━━━━━╋━━━╋━━━╋━━━╋━━━━━━┫
┃2023/11/1 ┃ ┃DEF ┃2,000 ┃ ┃ ┃
┣━━━━━╋━━━━━━━━╋━━━╋━━━╋━━━╋━━━━━━┫
┃2023/11/2 ┃訪問（割増） ┃ ┃ ┃5,000 ┃1 ┃
┣━━━━━╋━━━━━━━━╋━━━╋━━━╋━━━╋━━━━━━┫
┃2023/11/2 ┃ ┃ ┃ ┃1,000 ┃1 ┃
┣━━━━━╋━━━━━━━━╋━━━╋━━━╋━━━╋━━━━━━┫
┃2023/11/2 ┃故障対応 ┃EFG ┃10,000┃5,000 ┃ ┃
┣━━━━━╋━━━━━━━━╋━━━╋━━━╋━━━╋━━━━━━┫
┃2023/11/3 ┃電話問合せ ┃ ┃ ┃5,000 ┃1 ┃
┣━━━━━╋━━━━━━━━╋━━━╋━━━╋━━━╋━━━━━━┫
┃2023/11/3 ┃故障対応 ┃HIJ ┃10,000┃5,000 ┃ ┃
┣━━━━━╋━━━━━━━━╋━━━╋━━━╋━━━╋━━━━━━┫
┃2023/11/4 ┃訪問 ┃ ┃ ┃5,000 ┃1 ┃
┣━━━━━╋━━━━━━━━╋━━━╋━━━╋━━━╋━━━━━━┫
┃2023/11/4 ┃部品発送 ┃KLM ┃10,000┃ ┃ ┃
┣━━━━━╋━━━━━━━━╋━━━╋━━━╋━━━╋━━━━━━┫
┃2023/11/5 ┃緊急メンテナンス┃ ┃ ┃5,000 ┃1 ┃
┗━━━━━┻━━━━━━━━┻━━━┻━━━┻━━━┻━━━━━━┛

拙い説明で申し訳ございませんが、有識者の方からお知恵を拝借いただけると幸いです。
どうぞ、よろしくお願い申し上げます。

回答No.7

kkkkkm · ベストアンサー

> 2023/11/2 故障対応 EFG 10,000 5,000
> 2023/11/2 5,000 1
> ↓
> この場合に、人件費フラグが立たないようにしたいと希望しております。

以下に変更してみてください。

'対応内容が同日で複数行発生し人件費が発生した場合
'対応内容が同日で空白の場合には上の行のフラグが入力されている場合
If DataArr(i, 1) = DataArr(i - 1, 1) _
And (DataArr(i, 2) = DataArr(i - 1, 2) Or DataArr(i, 2) = "") _
And (DataArr(i, 5) <> "") _
And (DataArr(i - 1, 4) = "") _
And (DataArr(i - 1, 6) <> "") Then
flg = True
End If

お礼

懇切丁寧にご指導くださいまして、ありがとうございます。

おかげさまで、処理させたい内容で動作することが確認できました。
相応の時間を費やして考えておりましたが、kkkkkm様からのご知見により、解決できとても嬉しく思っております。

繰り返しとなりますが、ありがとうございました。

回答No.6

kkkkkm

回答No.5の補足です。

質問にあるデータで

2023/11/2　　訪問（割増）　5,000　1
2023/11/2　　　　　　　　　1,000　1

の所は6行目の対応内容が存在しないのにフラグが「1」になっています。
なので、5行目と同じ場合(上の行と同じ)は入力しないのだなぁという考えでやってます。
で、どこまでが同じ内容として判断するのか分からないので、未入力が同じ日付で連続した場合、他の条件が一致した場合は「1」になります。

対応内容が未入力ならが必須でしたら

'対応内容が同日で複数行発生し人件費が発生した場合
If DataArr(i, 1) = DataArr(i - 1, 1) _
And DataArr(i - 1, 2) <> "" _
And (DataArr(i, 2) = DataArr(i - 1, 2)) _
And (DataArr(i, 5) <> "") Then
flg = True
End If

お礼

拙い質問の背景にご配慮いただきまして、ありがとうございます。

結論から申し上げますと修正は不要となります。というのは、対応内容が存在しない場合は、日付が同一の場合に発生した事象とみなすため、フラグが立つ先のコードで問題ございません。

とてもご丁寧にありがとうございます。

回答No.5

kkkkkm

あまり深く考えずにテストしたものです。

Sub Test2()
Dim DataArr As Variant, flgArr() As Variant
Dim i As Long
Dim LastRow As Long
Dim flg As Boolean

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(2, "F"), Cells(LastRow, "F")).ClearContents
DataArr = Range(Cells(1, "A"), Cells(LastRow, "F")).Value

For i = 2 To LastRow
flg = False
'対応内容が同日で複数行発生し人件費が発生した場合
If DataArr(i, 1) = DataArr(i - 1, 1) _
And (DataArr(i, 2) = DataArr(i - 1, 2) Or DataArr(i, 2) = "") _
And (DataArr(i, 5) <> "") Then
flg = True
End If
'上記の場合もしくは人件費が発生した場合の対応内容が存在したとき、F列に人件費フラグとして「1」を表示させる。
If (DataArr(i, 5) <> "" And DataArr(i, 2) <> "") Or flg = True Then
DataArr(i, 6) = 1
'上記でフラグがあっても部材費がある場合はフラグを無しとする
If DataArr(i, 4) <> "" Then
DataArr(i, 6) = ""
End If
End If
Next

flgArr = WorksheetFunction.Index(DataArr, 0, 6)
Range("F1").Resize(UBound(flgArr, 1), UBound(flgArr, 2)).Value = flgArr

End Sub

お礼

ご回答ありがとうございます。

コードを実行してみたところ、私の要望がかなり実装できておりました。同日で、対応内容が1つに対して部材費と人件費が複数発生する場合で、人件費のみが発生した時には、フラグを立てないようにできると幸いです。

実行後コード
日付 対応内容 部品名 部材費 人件費 人件費フラグ
2023/11/2 故障対応 EFG 10,000 5,000
2023/11/2 5,000 1
↓
この場合に、人件費フラグが立たないようにしたいと希望しております。

回答No.4

kkkkkm

回答No.3の追加です。

VBAで必要な条件とか考えてやってたのでVBAになりましたが
部材費が無ければという事だけならVBAでなくても
=IF(D2="",1,"")
でいけますね・・・。

お礼

ご回答ありがとうございます。

部材費の有無で判断するということも考えましたが、人件費のみで判断することとさほど変わらないため見送りました。同一日付で複数行が発生している場合に対応ができませんので。

回答No.3

kkkkkm

> 4.1つの対応内容で部材費と人件費が発生した場合と部材費のみが発生した場合は、人件費フラグには何も表示させない。

部材費が無ければフラグを表示するというひとつの条件だけでは駄目でしょうか。

質問のデータをみてもその条件だけでいけそうですが・・・。

その場合だとうまくいかないデータがあれば教えてください。

とりあえず、部材費が無ければという事だけで
A1からデータがあるとしてです。
行数があるという事なので、セルに毎回直接アクセスしないようにしています。

Sub Test()
Dim DataArr As Variant
Dim flgArr() As Variant
Dim i As Long, LastRow As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(2, "F"), Cells(LastRow, "F")).ClearContents
DataArr = Range(Cells(1, "A"), Cells(LastRow, "F")).Value

For i = 2 To LastRow
If DataArr(i, 4) = "" Then
DataArr(i, 6) = 1
End If
Next

flgArr = WorksheetFunction.Index(DataArr, 0, 6)
Range("F1").Resize(UBound(flgArr, 1), UBound(flgArr, 2)).Value = flgArr

End Sub

お礼

ご回答ありがとうございます。

単に人件費のみが発生している行のみフラグを立てるのであれば容易です。しかし、日付と対応内容を加味して、部材費が発生したケースはフラグを立てないとすると試行錯誤しましたが、結局、解決には至らずでした。

特に、同じ日付で対応内容が複数発生したデータや、同じ対応内容で、部材費と人件費がそれぞれ複数発生した場合に苦慮しております。いずれも日付単位で1つの対応内容で人件費のみしか発生していないときにフラグを立てることを想定しています。

以上、よろしくお願いします。

回答No.2

bardfish

私なら・・・という前提となりますがお許しを。

まず、御質問で示されている表は「最終的な成果物」として捉えた上で入力フォームを作ります。
最低入力単位を「伝票」「作業報告書」とし、1伝票1レコードとした処理前のデータテーブルに集積させます。
そのご、日単位や週単位、月単位で抽出した結果を集計し、一覧表として集計表を作ります。

Excelの最大のメリットは誰もが簡単に扱えるところではありますが「簡単な処理なら」という条件が付く。そして、処理内容によってはメリットが取り返しの付かない致命的なデメリットになる・・・と、私なんかは思っています。

だから全てをExcelでやろうとは思っていない。
そのためデータベースも勉強した。
データベースを利用したシステム開発にも携わったからExcelでもできるけど依存してはいけないことも分かっている。
いまでは、Excelは便利な電卓であり、プログラムで処理/集計したデータをExcelの使い方を多少でも知っていれば便利に「見せる」事ができる優れたツールであると割り切っています。

要は、適材適所。
Excel VBAは一見簡単そうに見えますが、実はもの凄く面倒くさい。「難しい」ではなく「面倒くさい」のです。
難しいことを比較的簡単に行いたいなら・・・今ならVisualStudioのVisual BasicとSQL Server(データベースサーバー)ですね。Accessがあればデータベースのテーブルに直接データを入力することができますが、費用が出せないというならデータ入力プログラムを自分で作れます。
入力プログラムを作るという事は、データ入力する人が複数居ても全ての人が同じルールに沿って入力するので入力されたデータが間違っているから処理ができない！なんてことは発生しません。発生したというのであれば、それはプログラム作成時の想定に「穴」があったという事。想定外の入力があったら登録前に警告を出して再入力を促すようにすれば良いだけ。
じつは、Excelではそれができないのです。できたとしても非常に面倒くさくて難しく、後々不具合に気づいても対応できる人が既に居ないこともあります。居たとしてもナニをやっているのか見ても分からない、なんて事も当たり前に起こっています笑い

少なくとも、プログラミング言語を使用して構築したものなら、作った人以外の人でも変更/修正は可能です。

ということで、Excelはなんでもできる万能ツールではありますが、やらせたいこと、使う人のことを考えると全然万能ではありません。どちらかというと、本来ならやらなくて良い仕事をしなくてはいかなくなる厄介者になる可能性をはらんでいます。

「やらなくても良い仕事」のせいで、決算前に数日徹夜作業になっても仕方ないですよね。

私が常々言っていることなんですが、いくら優秀な道具でも使う人がポンコツなら道具もポンコツに成り下がる。
語意はその都度変わりますが、本質的なところは「良い道具で良い結果を出したければ道具の本質を理解しましょう」ということです。
※Excel等の本質を理解を深めるためにプログラミングを身につけました。

結局の所は、フローチャートなどに代表される「ロジック」を学ぶことが一番の近道かもしれませんね。
プログラミングでは、コンピューターに仕事をさせるために、コンピューターに理解できる言語で「ロジック」を詳細に示しているだけなんです。
で、Excelの関数とかマクロ、VBAは本来厳格に指示しなくてはならないものなのに曖昧な指示でもそれなりに動いてしまうからダメなんです。
例)数値を入力するとき「123456789」と入力して欲しいのに、人によっては「123,456,789」とカンマまで入力してしまうため計算式のセルでエラーが発生してしまう。原因を探そうにも式が複雑すぎてなかなか原因を突き止められない・・・ということ。
プログラミング言語ならカンマ込みで数値を入力したらその時点でエラーになり場合によってはその時点でソフト/アプリが強制終了してしまう。だから入力チェックで警告を出して再入力させるなどの処置が可能となる。

って感じですので、御質問に関する私の回答としては「Excelでは無理」「Excelでそんなことをやりたくない！」「作っても良いけど作るだけでその後のサポートは断る！」となります。

以上、長々とお付き合いありがとうございました。

お礼

ご回答ありがとうございます。

確かにVSやRDBMSが使える環境が望ましいですが、業務用PCへフリーの環境をインストールできないため、汎用的なExcelで妥協した背景があります。

回答No.1

chie65536（@chie65535）

１つの対応内容が複数行になるのを無視すれば（表の1行目が見出し、データが２行目から始まるとして）

F2に
=IF(AND(ISBLANK(D2),NOT ISBLANK(E2)),"1","")
と書けば良い。

なお、１つの対応内容が、以下に示すように２行に渡っていて、１行目が「部材費＋人件費」、２行目が「人件費のみ」になっていると、２行目だけ「人件費フラグ」が付いてしまう。
┣━━━━━╋━━━━━━━━╋━━━╋━━━╋━━━╋━━━━━━┫
┃2023/11/2 ┃訪問修理（割増） ┃ABC ┃2,500 ┃5,000 ┃ ┃
┣━━━━━╋━━━━━━━━╋━━━╋━━━╋━━━╋━━━━━━┫
┃2023/11/2 ┃ ┃ ┃ ┃1,000 ┃1 ┃
┣━━━━━╋━━━━━━━━╋━━━╋━━━╋━━━╋━━━━━━┫

これを防ぐには、マクロを組んで「必要なだけ前の行に遡って調べる」という複雑な処理が必要なので、関数では解決できません。

お礼

ご回答ありがとうございます。
おっしゃる通り、関数では無理と思われたため質問した経緯があります。そのため、件名にVBAの文字列を入れております。

VBAなら何とかなるかと思いつつ、試行錯誤しても、ままならなかったため伺ってみました。ただ、少しVBAをかじった程度のレベルでは望んだ処理ができませんでした。