Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちらをご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


	新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム

191 / 3841 ﾍﾟｰｼﾞ

←次へ　｜　前へ→


	【78633】VBAではなく、関数を教えてほしいです

エクセルの素人 - 16/12/2(金) 21:21 -


	引用なし

	パスワード

すみませんが、エクセルがほとんど使えないのですが、ある法則「ある行で初めて１が出てきたとき、その左側に１番近い数字が０のときは、１、０でないときは、０」という関数を使った式を作りたいのです。
なんとか本で調べて作ったへんてこりんな式が
以下の式なのですが、以下の式だと７列しか計算できず汎用性（２８通りしか対応できない）がないため、困っています。
どなたか、関数をつくっていただけないでしょうか？

なお、これは、他のサイトでも質問していますが、返答がなくこまっております。
よろしくお願いいたします。
IF(H3-I3=-1,1,0)
IF(G3-H3=-1,1,0)
IF(F3-G3=-1,1,0)
IF(E3-F3=-1,1,0)
IF(D3-E3=-1,1,0)
IF(C3-D3=-1,1,0)
IF(B3-C3=-1,1,0)
IF(H3=7001,IF(G3-I3=-1,1),0)
IF(H3=7001,IF(G3=7001,IF(F3-I3=-1,1)),0)
IF(H3=7001,IF(G3=7001,IF(F3=7001,IF(E3-I3=-1,1))),0)
IF(H3=7001,IF(G3=7001,IF(F3=7001,IF(E3=7001,IF(D3-I3=-1,1)))),0)
IF(H3=7001,IF(G3=7001,IF(F3=7001,IF(E3=7001,IF(D3=7001,IF(C3-I3=-1,1))))),0)
IF(H3=7001,IF(G3=7001,IF(F3=7001,IF(E3=7001,IF(D3=7001,IF(C3=7001,IF(B3-I3=-1,1)))))),0)
IF(G3=7001,IF(F3-H3=-1,1),0)
IF(G3=7001,IF(F3=7001,IF(E3-H3=-1,1)),0)
IF(G3=7001,IF(F3=7001,IF(E3=7001,IF(D3-H3=-1,1))),0)
IF(G3=7001,IF(F3=7001,IF(E3=7001,IF(D3=7001,IF(C3-H3=-1,1)))),0)
IF(G3=7001,IF(F3=7001,IF(E3=7001,IF(D3=7001,IF(C3=7001,IF(B3-H3=-1,1))))),0)
IF(F3=7001,IF(E3-G3=-1,1),0)
IF(F3=7001,IF(E3=7001,IF(D3-G3=-1,1)),0)
IF(F3=7001,IF(E3=7001,IF(D3=7001,IF(C3-G3=-1,1))),0)
IF(F3=7001,IF(E3=7001,IF(D3=7001,IF(C3=7001,IF(B3-G3=-1,1)))),0)
IF(E3=7001,IF(D3-F3=-1,1),0)
IF(E3=7001,IF(D3=7001,IF(C3-F3=-1,1)),0)
IF(E3=7001,IF(D3=7001,IF(C3=7001,IF(B3-F3=-1,1))),0)
IF(D3=7001,IF(C3-E3=-1,1),0)
IF(D3=7001,IF(C3=7001,IF(B3-E3=-1,1)),0)
IF(C3=7001,IF(B3-D3=-1,1),0)

・ツリー全体表示


	【78632】Re:特定の文字列の入っている行を削除し...

β - 16/12/2(金) 17:52 -


	引用なし

	パスワード

▼piropiro さん：

確認3 も確認4 も、どちらも 39 だったということですか？
そうであれば、なぞは謎のまま・・・

私の目論見は、確認3 は 39、でも　確認4 は 49 と出てほしかったんです。

piropiroさんの目視確認では、39行削除のはず。
でも、piropiroさんのコードや私のMATCHを使ったコードでは49行削除されてしまう！

こういうことですね。

で、私の推論は、削除行は 49 が正しい！　というものです。
つまり、NA とあきらかに記入されている行の他に、目には見えないけど、実は NA という
値を持つセルが 10行あって、それらも『正しく』削除して、49行削除。

じゃぁ、その目に見えない NA って何かということですけど、たとえば
セルの表示書式が ;;; だった場合、NA と入っているのに空白表示。
なので、人間は、見た目では気が付かない。

それを確認するために確認3 と確認4 を試してもらったんですが・・・
そうですかぁ・・どちらも 39 ですかぁ・・・

だんだん心苦しくなってくるんですが、以下を実行するとどんなメッセージがでますか？

Sub 確認5()
　　Dim a As Range
　　Dim r As Range
　　Dim c As Range
　　Dim f As Range
　　Dim x As Long

　　 '使用領域の K列から最終列までの列数
　　 x = Range("A1", ActiveSheet.UsedRange).Columns.Count - 10
　　'判定対象領域
　　 Set a = Range("I4", Range("I" & Rows.Count).End(xlUp)).Offset(, 2).Resize(, x)

　　 Set c = a.Find(What:="NA", LookAt:=xlWhole, LookIn:=xlFormulas)
　　If c Is Nothing Then
　　　　MsgBox "領域に NA はありません"
　　　　Exit Sub
　　End If

　　 Set f = c

　　 Do
　　　　If c.Value <> c.Text Then
　　　　　　If r Is Nothing Then
　　　　　　　　Set r = c
　　　　　　Else
　　　　　　　　Set r = Union(r, c)
　　　　　　End If
　　　　End If
　　　　Set c = a.FindNext(c)
　　Loop While c.Address <> f.Address

　　If Not r Is Nothing Then
　　　　MsgBox "以下のセルを確認してください" & vbLf & Replace(r.Address, ",", vbLf)
　　Else
　　　　MsgBox "NA セルの見た目と実際の値に違いはありませんでした"
　　End If

End Sub

・ツリー全体表示


	【78631】Re:特定の文字列の入っている行を削除し...

piropiro - 16/12/2(金) 14:15 -


	引用なし

	パスワード

▼β さん！！！！！！！！！

またまた早急のお返事、本当にありがとうございます。

コードは理解できてないですが（スミマセン＞＜）
確認3 と確認4 の両方とも実行しました。

結果、「そのなかの削除対象は39行です」と表示されました。
実際の削除行の39行です。

っと！っということは魔の10行解決？？？
βさんのご指摘通り、表示形式設定のせい？
ん？逆？解決してない？

スミマセン、混乱してきました。

・ツリー全体表示


	【78630】Re:特定の文字列の入っている行を削除し...

β - 16/12/2(金) 13:38 -


	引用なし

	パスワード

▼piropiro さん：

【魔の10行】ですかぁ。
不思議ですねぇ。

消されるべきなのに消されないということなら、いろいろ考えられますが
消されるべきではないのに消される・・・

可能性は極めて低いのですが、以下のケースなら、そうなります。

セルに NA という値があったとします。
で、表示書式設定で、文字列の場合に　別文字列　に設定すれば、実際のセルの値とは異なる値が
セルに表示されます。
さらに、ユーザー定義で　;;;　と設定すると、セルは空白に見えてしまいます。
なので、【ここにはNAはない！】
ところが　実態は NA ですから、セル比較（そちらのコード）やMATCH で、NA と認識します。
またFindメソッドでも LookIn を xlFormula にするとNAだと認識されます。

で、削除。見た目は NA ではないセルの行が削除されるわけです。
（コード上で LookIn を省略すると、たまたま、そのPCで、その前に行われた Findメソッド、Replaceメソッド
　ないしは　検索や置き換えの操作時のオプションが適用されます）

可能性は低いのですが、以下の確認3 と確認4 の結果は同じになりますか？
それとも10行の差異がありますか？

Sub 確認3()
　　Dim a As Range
　　Dim r As Range
　　Dim c As Range
　　Dim f As Range
　　Dim x As Long
　
　　 '使用領域の K列から最終列までの列数
　　 x = Range("A1", ActiveSheet.UsedRange).Columns.Count - 10
　　'判定対象領域
　　 Set a = Range("I4", Range("I" & Rows.Count).End(xlUp)).Offset(, 2).Resize(, x)
　
　　 Set c = a.Find(What:="NA", LookAt:=xlWhole, LookIn:=xlValues)
　　If c Is Nothing Then
　　　　MsgBox "領域に NA はありません"
　　　　Exit Sub
　　End If

　　 Set f = c

　　 Do
　　　　If r Is Nothing Then
　　　　　　Set r = c
　　　　Else
　　　　　　Set r = Union(r, c)
　　　　End If
　　　　Set c = a.FindNext(c)
　　Loop While c.Address <> f.Address

　　 MsgBox "対象領域の行数は " & a.Rows.Count & "行で、そのなかの削除対象は" & Intersect(r.EntireRow, Columns("K")).Count & "行です"

End Sub

Sub 確認4()
　　Dim a As Range
　　Dim r As Range
　　Dim c As Range
　　Dim f As Range
　　Dim x As Long
　
　　 '使用領域の K列から最終列までの列数
　　 x = Range("A1", ActiveSheet.UsedRange).Columns.Count - 10
　　'判定対象領域
　　 Set a = Range("I4", Range("I" & Rows.Count).End(xlUp)).Offset(, 2).Resize(, x)
　
　　 Set c = a.Find(What:="NA", LookAt:=xlWhole, LookIn:=xlFormulas)
　　If c Is Nothing Then
　　　　MsgBox "領域に NA はありません"
　　　　Exit Sub
　　End If

　　 Set f = c

　　 Do
　　　　If r Is Nothing Then
　　　　　　Set r = c
　　　　Else
　　　　　　Set r = Union(r, c)
　　　　End If
　　　　Set c = a.FindNext(c)
　　Loop While c.Address <> f.Address

　　 MsgBox "対象領域の行数は " & a.Rows.Count & "行で、そのなかの削除対象は" & Intersect(r.EntireRow, Columns("K")).Count & "行です"

End Sub

・ツリー全体表示


	【78629】Re:特定の文字列の入っている行を削除し...

piropiro - 16/12/2(金) 11:45 -


	引用なし

	パスワード

▼β さん：

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

試しました！
実際の削除行は39行で
βさんのコードの結果では49行削除と表示されました。。。
やはり、10行分差異があります。。。

・ツリー全体表示


	【78628】Re:特定の文字列の入っている行を削除し...

β - 16/12/2(金) 11:06 -


	引用なし

	パスワード

▼piropiro さん：

まだ原因はわかりません。
もう１つ確認してください。

>一つ一つ手動で検索→削除すると1128行のデータが残るのですが
>VBAで実行すると1118行で10行分のデータが削除されてしまっているのです。

このシートで以下のコードを走らせてください。
で、でてくるメッセージが正しい件数かどうかを確認してみてください。
（全体の行数と削除行数を表示しています。ですから引き算すれば残る行数になります）

Sub 確認2()
　　Dim a As Range
　　Dim r As Range
　　Dim c As Range
　　Dim f As Range
　　Dim x As Long
　　
　　'使用領域の K列から最終列までの列数
　　x = Range("A1", ActiveSheet.UsedRange).Columns.Count - 10
　　'判定対象領域
　　Set a = Range("I4", Range("I" & Rows.Count).End(xlUp)).Offset(, 2).Resize(, x)
　　
　　Set c = a.Find(What:="NA", LookAt:=xlWhole)
　　If c Is Nothing Then
　　　　MsgBox "領域に NA はありません"
　　　　Exit Sub
　　End If
　
　　Set f = c
　
　　Do
　　　　If r Is Nothing Then
　　　　　　Set r = c
　　　　Else
　　　　　　Set r = Union(r, c)
　　　　End If
　　　　Set c = a.FindNext(c)
　　Loop While c.Address <> f.Address
　
　　MsgBox "対象領域の行数は " & a.Rows.Count & "行で、そのなかの削除対象は" & Intersect(r.EntireRow, Columns("K")).Count & "行です"
　
End Sub

・ツリー全体表示


	【78627】Re:特定の文字列の入っている行を削除し...

piropiro - 16/12/1(木) 15:39 -


	引用なし

	パスワード

▼β さま～～～

ありがとうございます。
なんと！お優しい！！！！
全然明日でも大丈夫です。
βさまのご都合の良いときで。

よろしくお願いしますm(__)m

・ツリー全体表示


	【78626】Re:特定の文字列の入っている行を削除し...

β - 16/12/1(木) 15:23 -


	引用なし

	パスワード

▼piropiro さん：

奇々怪々ですねぇ・・・

しばらくしたら外出しますので、明日以降になると思いますが
何か原因がないのか、さぐってみます。

最大列については、7000 と指定せず自動取得ができるのですが
それは、原因がわかってからにしましょう。

・ツリー全体表示


	【78625】Re:特定の文字列の入っている行を削除し...

piropiro - 16/12/1(木) 15:15 -


	引用なし

	パスワード

▼β さ～ん：

わざわざ確認コードまで。。。
ありがとうございます！
でも、NAなかったです。。。。
う～ん、煮詰まってきました。

NAがないのに削除された行のデータをみてみましたが
他の行と特に変わったところはなく。。。
今現在はINC列までデータが入っているのですが
日によって増えたりするので7000列に指定しているのですが
これは関係ないですよね？

・ツリー全体表示


	【78624】Re:特定の文字列の入っている行を削除し...

β - 16/12/1(木) 15:01 -


	引用なし

	パスワード

▼piropiro さん：

う・・・ん・・・
どこかに（ずっと、右のほうに）NAがあると思うんですがねぇ。

以下のコードを走らせて出てくる列が、一番右側にNAがあった列です。

Sub 確認()
　　Dim c As Range
　　Dim f As Range
　　Dim col As Long
　　
　　Set c = Columns("K:JIF").Find(What:="NA", LookAt:=xlWhole)
　　If c Is Nothing Then
　　　　MsgBox "領域に NA はありません"
　　　　Exit Sub
　　End If
　　
　　Set f = c
　　
　　Do
　　　　If c.Column > col Then col = c.Column
　　　　Set c = Columns("K:JIF").FindNext(c)
　　Loop While c.Address <> f.Address
　　
　　MsgBox "一番右のNAは " & Split(Columns(col).Address(False, False), ":")(0) & " 列にあります"
　　
End Sub

・ツリー全体表示


	【78623】Re:特定の文字列の入っている行を削除し...

piropiro - 16/12/1(木) 13:46 -


	引用なし

	パスワード

▼β さん：

こんな早く教えて頂けるなんて本当にありがとうございます。

最初はFindメソッドを使用してみたのですがうまくいかず。。。
お恥ずかしいですが、今の私の知識では先程のコードが精いっぱいでして。。。

MATCHを使うとこんなに処理がスムーズなんてすばらしいです。
まだ理解できていませんが、解読してみます！

それからもうひとつ質問なのですが
一つ一つ手動で検索→削除すると1128行のデータが残るのですが
VBAで実行すると1118行で10行分のデータが削除されてしまっているのです。
（NAは含まれていないのです）

先程の私のダメダメなコードでもβさんのMATCHのコードでもです。
βさんは「NAのない行は削除されないと思います」っと書いてくださいましたが
私のPCでは削除されてしまうのです。
お心当たりがありましたら教えてください。
よろしくお願いします。

・ツリー全体表示


	【78622】Re:特定の文字列の入っている行を削除し...

β - 16/12/1(木) 13:09 -


	引用なし

	パスワード

▼piropiro さん：

シート関数MATCHを使った処理例です。
ご参考まで。

Sub Sample()
　　Dim lastrow As Long
　　Dim i As Long, j As Long
　　Dim x As Variant
　　
　　lastrow = Cells(Rows.Count, 9).End(xlUp).Row

　　For i = lastrow To 4 Step -1

　　　　x = Application.Match("NA", Range(Cells(i, 11), Cells(i, 7000)), 0)
　　　　If IsNumeric(x) Then Rows(i).Delete
　　　　　　
　　Next i
　　
End Sub

・ツリー全体表示


	【78621】Re:特定の文字列の入っている行を削除し...

β - 16/12/1(木) 13:02 -


	引用なし

	パスワード

こちらで、そのまま実行しましたが、NAのない行は削除されないと思いますけど？
ただし、このコードには問題があります。

行 i に対して、4列目から7000列目までループでチェックし（この 7000 が意味のある数字なのかどうかは？？ですけど）
どこかの列にNA があって行削除すれば、もう、その行の判定は不要ですよね。削除してしまってますから。
なのに、コードは延々と削除の結果繰り上がった空白行に対して判定を繰り返しています。
結果は、NAではないのでカラブリで、結果オーライですけど無駄ですね。
削除したら Exit For で　当該のループを強制脱出させなければいけません。

ところで、列ごとに NAかどうかのチェックをしていますが、シート関数のMATCHをVBAで利用したり
VBFのFindメソッドで、当該行の当該列領域に NA があるかないかを１行で判定して、あれば、それを削除。
こういう方法がおすすめです。

それと、コードにはインデントを付けましょう。

・ツリー全体表示


	【78620】特定の文字列の入っている行を削除したい...

piropiro - 16/12/1(木) 12:08 -


	引用なし

	パスワード

VBA初心者です。
よろしくお願いします。

以下のように書いたのですが、結果がおかしいのです。

Dim lastrow As Long
Dim i As Long, j As Long

lastrow = Cells(Rows.Count, 9).End(xlUp).Row

For i = lastrow To 4 Step -1

For j = 11 To 7000

If Cells(i, j) = "NA" Then
Rows(i).Delete

End If

Next j
Next i

範囲内にNAと入力されたセルがあったら
その行を削除したいのですが
NAがない行も削除されているのです。

どなたかアドバイスをお願いいたします！

・ツリー全体表示


	【78619】Re:特定の文字だけピックアップ

ふむふむ - 16/11/30(水) 0:57 -


	引用なし

	パスワード

▼β さん：

ありがとうございます！！！
できるのですね！
とても勉強になりました。

ほんとうにありがとうございます。

・ツリー全体表示


	【78618】Re:特定の文字だけピックアップ

β - 16/11/29(火) 16:28 -


	引用なし

	パスワード

▼ふむふむさん：

あぁ、姓だけではなく、後半の名も取得するのですね。

Sub Sample2()
　　Dim s As String
　　Dim w As Variant
　　
　　s = "田中　一郎" '間は全角スペース
　　w = Split(Replace(WorksheetFunction.Trim(s), "　", " "))
　　MsgBox w(0) & vbLf & w(1)
　　
　　s = "田中一郎" '間は全角スペース
　　w = Split(Replace(WorksheetFunction.Trim(s), "　", " "))
　　MsgBox w(0) & vbLf & w(1)
　　
　　'でも半角スペースと決まっていれば以下でもＯＫ
　　
　　w = Split(s)
　　MsgBox w(0) & vbLf & w(1)
　　
End Sub

・ツリー全体表示


	【78617】Re:特定の文字だけピックアップ

β - 16/11/29(火) 16:22 -


	引用なし

	パスワード

▼ふむふむさん：

間のスペースが全角なのか半角なのか
また、スペース１文字なのか２桁以上なのか不明ですが、
いずれであっても処理する参考コードです。

Sub Sample()
　　Dim s As String
　　
　　s = "田中　一郎" '間は全角スペース
　　
　　MsgBox Split(Replace(WorksheetFunction.Trim(s), "　", " "))(0)
　　
　　s = "田中一郎" '間は全角スペース
　　
　　MsgBox Split(Replace(WorksheetFunction.Trim(s), "　", " "))(0)
　　
　　'でも1桁の半角スペースと決まっていれば以下でもＯＫ
　　
　　MsgBox Split(s)(0)
　　
End Sub

・ツリー全体表示


	【78616】特定の文字だけピックアップ

ふむふむ - 16/11/29(火) 15:39 -


	引用なし

	パスワード

皆様

また、お知恵をお貸し下さい。

たとえば、氏名で「小林　太郎」とある場合、氏と名のﾌﾞﾗﾝｸを境として、文字をピックアップして、あるセルにコピーして張り付けたいのですが、
可能でしょうか。

left/right/midでの方法は知っておりますが、必ず2文字とは限らないので、
文字と文字の空白を検地し、判断させたいのです。

「マイク　スミス」のように、カタカナの場合もあります。
よい方法、または、可能かの有無が全く検討がつきません。

ご存知の方、どうかご教示頂けますとうれしいです。
宜しくお願いいたします。

・ツリー全体表示


	【78615】Re:重複文を避けたい　go to XXX

ふむふむ - 16/11/29(火) 1:04 -


	引用なし

	パスワード

▼β さん：

構成を変えればいいのですね。
ありがとうございます。

・ツリー全体表示


	【78614】Re:重複文を避けたい　go to XXX

β - 16/11/28(月) 18:55 -


	引用なし

	パスワード

▼ふむふむさん：

GoTo はつかうな　とまではいいませんが、できる限り（歯を食いしばって）
使わなくてもすむ記述をされるべきだと思います。
いろんな制御方法がありますが、簡単なのは

If なんとか Then
　なんとか　の場合の　固有の処理のコード

　共通Proc

　なんとか　の場合の　固有の処理のコード

Else
　なんとかじゃない場合の　固有の処理のコード

　共通Proc

　なんとかじゃない場合の　固有の処理のコード

End If

こんなコードにしておいて、このプロシジャとは別に

Private Sub 共通処理()

　両方の共通の処理コード

End Sub

こんな構成がいいのではないですか？

・ツリー全体表示


	新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム

191 / 3841 ﾍﾟｰｼﾞ

←次へ　｜　前へ→

ページ：

┃

記事番号：

(SS)C-BOARD v3.8 is Free