VLOOKUP関数でエラーの表示された行だけを削除するにはどうすればい

質問

VLOOKUP関数でエラーの表示された行だけを削除するにはどうすればいいでしょうか。

http://okwave.jp/qa/q5920035.html
の質問にて作成したワークシートCがあります。

ワークシートCのA列はドラッグして日付を埋めたので、昇順で365日日付が並んでいます。（2005年1月1日～現在まで）

B列～K列までは、ワークシートA（為替）、ワークシートB（株式）からVLOOKUPで引っ張ってきた数値が入っています。

ドラッグして日付を埋めたので、A列にはマーケットが開いてない土日も日付が入っていますが、その結果、土日の日付を参照した行には#N/Aというエラーが表示されている状態です。
（Ex,　A3は2005/01/02という日付が入っていて、B3～K3は#N/Aというエラーが表示されている状態です。）

この#N/Aが表示されている行を削除するにはどんなマクロを組めばいいのでしょうか。

#N/Aを判別するようなものにすればいいんだと思いましたが、その具体方法が分かりません。

マクロも初心者なので、出来るだけ具体的に教えて頂けると助かります。

宜しくお願いします。

回答No.1

keithin · ベストアンサー

＞この#N/Aが表示されている行を削除するにはどんなマクロを組めばいいのでしょうか。

先のご相談では，片方のマーケットしか開いていない日もあるというお話でしたね。
その前提に立つと単純に土日だけでなく，為替列でエラーになっている行とか，株式列だけがエラーになっている行などもあるはずです。

つまりやるべきは「#N/Aが表示されている行を削除する」ではなく，「B列とG列が共に#N/Aになっている行」を抽出して削除ということになります。

＃以下のご説明は，マーケットの無い日がエラーでも""でも同じです。

手順：
オートフィルタを取り付ける
B列でエラー値(若しくは空白）で絞る
G列でエラー値(若しくは空白）で絞る
共にエラー値(若しくは空白）である行だけが残るので，選んで右クリックして削除で行単位で上に詰めて完成。

お礼

返信遅くなりました。

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

単純にオートフィルタで良かったんですね。

先の質問でもご丁寧に教えて頂いて助かりました。

ありがとうございます。

回答No.4

keithin

ん？　まだ解決してないって事は，コピーして動かせるマクロをとにかく誰かが書いてくれるまで引っ張りたいって事でしょうか？

その前に，そもそも前のご質問でエラーの解消は出来たんでしょうか。
それとも，前の回答の式は破棄してご自分の式で行くことにしたので，まだエラーが出たままなのでしょうか。その場合はもう前のご相談とは話が違うのですから，「今こういう式にしています」ときちんと情報提供してください。そういった「今こうなっている」が不明のままマクロだけ人に書かせても，的外れなままです。

エラーの消去も，こちらのご質問で先に回答しておいたように，Ｂ列を消して終わりでは間違った対処です。

間違ったご質問：
＞（Ex,　A3は2005/01/02という日付が入っていて、B3～K3は#N/Aというエラーが表示されている状態です。）

＞この#N/Aが表示されている行を削除するにはどんなマクロを組めばいいのでしょうか。

それに誘導された間違った回答：
Sub macro1
range("A:K").autofilter field:=2, criteria1:="#N/A"
activesheet.autofilter.range.offset(1).delete shift:=xlshiftup
activesheet.autofiltermode = false
end sub

Ctrl+G（ジャンプのセル選択）でエラーセルを拾うのも，同様に間違った対処です。

それと先のご相談時点と，為替・株からコピーしてきた列数が違っていますが，今結局どうなってるんでしょうか。

もしかしたらこうかな？の憶測で書いたマクロ１：
sub macro2()
range("A:K").autofilter field:=2, criteria1:="#N/A"
range("A:K").autofilter field:=7, criteria1:="#N/A"
activesheet.autofilter.range.offset(1).delete shift:=xlshiftup
activesheet.autofiltermode = false
end sub

もしかしたらこうかな？のマクロ２：
sub macro3()
range("A:K").autofilter field:=2, criteria1:="="
range("A:K").autofilter field:=7, criteria1:="="
activesheet.autofilter.range.offset(1).delete shift:=xlshiftup
activesheet.autofiltermode = false
end sub

お礼

＞　ん？　まだ解決してないって事は，コピーして動かせるマクロをとにかく誰かが書いてくれるまで引っ張りたいって事でしょうか？

紛らわしくてすみません。当初やりたかった事はkeithinさんとKURUMITOさんに教えて頂いた関数で解決出来ました。

＞　その前に，そもそも前のご質問でエラーの解消は出来たんでしょうか。
それとも，前の回答の式は破棄してご自分の式で行くことにしたので，まだエラーが出たままなのでしょうか。その場合はもう前のご相談とは話が違うのですから，「今こういう式にしています」ときちんと情報提供してください。そういった「今こうなっている」が不明のままマクロだけ人に書かせても，的外れなままです。

現在はkeithinさんに教えて頂いた

B2:為替
=IF(COUNTIF(為替!$A:$A,$A2),VLOOKUP($A2,為替!$A:$E,COLUMN(B2)),"")
を右に下にコピー。
F2:株式
=IF(COUNTIF(株式!$A:$A,$A2),VLOOKUP($A2,株式!$A:$E,COLUMN(B2)),"")
を右に下にコピー。

と、KURUMITOさんに教えて頂いた

=IF(OR(A2="",COUNTIF(為替!$A:$A,A2)=0),"",VLOOKUP(A2,為替!$A:$C,3,FALSE))

という式にてワークシートを作成し、当初の問題は解決致しました。

ですのでここから更にマクロに関する回答を望んでいたのではありません。imogasiさんへのお礼にも書きましたように、imogasiさんに教えて頂いたVBAは今の私にはちょっと難しそうなので、もう少し勉強してからトライしたいと思っています。

では何故回答をオープンにしていたかといいますと、KURUMITOさんにCOUNTIF(為替!$A:$A,A2)=0の働きについて教えて頂けないでしょうかと質問を投げかけたので、今しばらく回答を待ってみようと思ったからです。（関数がまだ良く分かっていないのでCOUNTIF(為替!$A:$A,A2)=0の「=0」がどういう役目なのかきちんと理解したいと思っていました。）

いずれにしろ、質問をオープンにしている意味をみなさんにご理解頂けないのは私の書き方が悪いからなんだと思います。申し訳ありません。

にも係わらず、更にマクロの指導を頂き感謝致します。今後のマクロの勉強に役立てたいと思います。

今しばらくCOUNTIF(為替!$A:$A,A2)=0への回答を待ち、近日中に回答を締め切りたいと思います。

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

回答No.3

imogasi

質問の内容をやや一般化して、「そもそも論」をやってみます。回りくどいと思うと思いますが少しお付き合いください。
Ｅｘｃｅｌで、ある行を見せないようにするには
（１）そのデータ行はある（残す）が非標示にする
（２）そのデータをなくした表を新たに別のシート（た同一シートの別余白列に）に作る
の２つがあります。
（１）は
　（Ａ）自己目視選択
　（Ｂ）条件指定で自動で
の２つがあります。
全体が少ない行で、非表示にする場合でエラーも少なく、再々の作業でなければ（Ａ）でも済ませられます。
しかし「該当行が多いとか再々の作業がある場合にやる場合は（Ｂ）を望むでしょう。
（Ａ）はＣＴＲＬキーを押しつつ該当行を目視で選択してクリックして、書式ー行ー非表示です。
（Ｂ）は非表示はデーターフィルタオプションの設定しかありません。選択して編集ー削除もありますが。
例えばセルの値が　2　に等しく無いものを抜き出すには<>"2"を条件にします。
ーー
（２）は
　（Ｘ）関数の抜き出し問題として考えるか
　（Ｙ）ＶＢＡで抜き出すか
になるでしょう。
注意が必要な点は
ＶＬＯＯＫＵＰ関数のエラーで、見つからない行のエラーのセルの値は、見た目「#N/A」です.
しかし文字列の「#N/A」ではなく フィルタオプションの設定の条件は　<>#N/A でフィルタします。
下記例で
Ｅ１：Ｅ２
値
<>#N/A
です
（Ｘ）の関数で抜き出す方法は、Ｇｏｏｇｌｅで「imogasi方式」照会すると沢山回答例が出ますが略。
その場合エラーで無い行の余白列にサインの１なりを改めてつけておくと考えやすい。
ーー
（Ｙ）のＶＢＡではエラーのセルの判別に知識が要ります。
データ例がＡ１：Ｂ４で
コード 値
1 a
2 #N/A
3 b
として
検索表はＩ１：Ｊ２に
1 a
3 b
とします。
上記例で、例えばＢ２の式は
=VLOOKUP(A2,$I$1:$J$2,2,FALSE)で出してます。
ー
ＶＢＡコードは、一例（方法）として
Sub test01()
j = 2
For i = 2 To 4
If IsError(Cells(i, "B")) = True Then
Select Case Cells(i, "B")
Case CVErr(xlErrNA)
Case Else
Cells(j, "G") = Cells(i, "A")
Cells(j, "H") = Cells(i, "B")
End Select
Else
Cells(j, "G") = Cells(i, "A")
Cells(j, "H") = Cells(i, "B")
j = j + 1
End If
Next i
End Sub
のような判定になります。
ー
ほかにフィルタオプションの設定の操作そのものをＶＢＡで行う方法もあります。
Ｇｏｏｇｌｅででも「ＶＢＡ　フィルタオプションの設定」げ照会してください。
またフィルタオプションの設定で別シートに結果を出すにはコツが必要ですがＧｏｏｇｌｅででも照会してください。
ＶＢＡで別シートに結果を出すのはSheet2.Cells(j,"A")=Sheet1.Cells(i,"A")のような書き方に
なりますが詳細は略。

参考URL：

http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1022022490

お礼

お礼が遅くなりました。

詳しく教えて頂いてありがとうございます。

マクロも今勉強中なのですが、このVBAは今の私にはちょっと難しそうなので、もう少し勉強してからトライしたいと思います。

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

回答No.2

KURUMITO

#N/Aの表示を出さない工夫が必要でしょう。
VLOOKUP関数を使用する場合には次のような式を使っておられることでしょう。
ワークシートＣのＡ列に日付があるとしてＢ2セルには例えば
=VLOOKUP(A2,為替!$A:$C,3,FALSE)
この式では為替シートのＡ列にその日付が無ければエラーとなります。
それを解消するためには一般には次のような式にして使用します。

=IF(OR(A2="",COUNTIF(為替!$A:$A,A2)=0),"",VLOOKUP(A2,為替!$A:$C,3,FALSE))

このように式を変形して使うことでエラー表示を解消することができますね。

どうしてエラー表示のある行を解消したいのでしたら次のようにします。
エクセル2007でしたら「ホーム」タブの「検索と選択」から「条件を選択してジャンプ」をクリックします。また、エクセル2002などでしたら「編集」メニューから「ジャンプ」を選択し、表示の画面で「セル選択」をクリックします。
表示される画面で「数式」にチェックをしたうえで「エラー値」のみにレ点が有る状態にし「ＯＫ」します。
エラー表示のあるセルがすべて選択状態になりますのでアクティブ状態にあるセルを右クリックして「削除」をクリックします。表示の画面で「行全体」にチェックをしてＯＫします。
これでエラー表示のある行をすべて削除することができます。

お礼

ご回答ありがとうございます。基本的質問で恐縮ですが、混乱しているのでお助け下さい。

＞　それを解消するためには一般には次のような式にして使用します。
=IF(OR(A2="",COUNTIF(為替!$A:$A,A2)=0),"",VLOOKUP(A2,為替!$A:$C,3,FALSE))

この式の意味ですが、もしA2が空白、もしくは為替シートA列のA2が0の場合は空白を返しなさい。さもなくばVLOOKUPで処理しなさい、ですよね。

ここでCOUNTIF(為替!$A:$A,A2)=0の働きが良く分かってないので教えて下さい。

為替シートのA列には日付が入っていて（日付が飛んでる事はありますが）、0になることはないのですが、何故COUNTIF(為替!$A:$A,A2)=0という式になっているのでしょうか。

COUNTIF(為替!$A:$A,A2)=0の働きを教えて頂けないでしょうか。

宜しくお願いします。

補足

前回補足を間違ってお礼に書いてしまったので、お礼を補足に書かせて頂きます。

色々教えて頂いてありがとうございました。

＞　また、エクセル2002などでしたら「編集」メニューから「ジャンプ」を選択し、表示の画面で「セル選択」をクリックします。

というやり方もあるんですね。知りませんでした。

もし宜しければ、お礼に書いたCOUNTIF(為替!$A:$A,A2)=0の働きについて教えて頂けないでしょうか。

宜しくお願いします。