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

http://oshiete.goo.ne.jp/qa/5920035.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を判別するようなものにすればいいんだと思いましたが、その具体方法が分かりません。

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

宜しくお願いします。

このQ&Aに関連する最新のQ&A

A 回答 (4件)

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



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

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


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

手順:
オートフィルタを取り付ける
B列でエラー値(若しくは空白)で絞る
G列でエラー値(若しくは空白)で絞る
共にエラー値(若しくは空白)である行だけが残るので,選んで右クリックして削除で行単位で上に詰めて完成。
    • good
    • 0
この回答へのお礼

返信遅くなりました。

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

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

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

ありがとうございます。

お礼日時:2010/05/29 02:37

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



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

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


間違ったご質問:
>(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(ジャンプのセル選択)でエラーセルを拾うのも,同様に間違った対処です。



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

もしかしたらこうかな?の憶測で書いたマクロ1:
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

もしかしたらこうかな?のマクロ2:
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
    • good
    • 0
この回答へのお礼

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

紛らわしくてすみません。当初やりたかった事は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への回答を待ち、近日中に回答を締め切りたいと思います。

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

お礼日時:2010/05/29 18:20

質問の内容をやや一般化して、「そもそも論」をやってみます。

回りくどいと思うと思いますが少しお付き合いください。
Excelで、ある行を見せないようにするには
(1)そのデータ行はある(残す)が非標示にする
(2)そのデータをなくした表を新たに別のシート(た同一シートの別余白列に)に作る
の2つがあります。
(1)は
 (A)自己目視選択
 (B)条件指定で自動で
の2つがあります。
全体が少ない行で、非表示にする場合でエラーも少なく、再々の作業でなければ(A)でも済ませられます。
しかし「該当行が多いとか再々の作業がある場合にやる場合は(B)を望むでしょう。
(A)はCTRLキーを押しつつ該当行を目視で選択してクリックして、書式ー行ー非表示です。
(B)は非表示はデーターフィルタオプションの設定しかありません。選択して編集ー削除もありますが。
例えばセルの値が 2 に等しく無いものを抜き出すには<>"2"を条件にします。
ーー
(2)は
 (X)関数の抜き出し問題として考えるか
 (Y)VBAで抜き出すか
になるでしょう。
注意が必要な点は
VLOOKUP関数のエラーで、見つからない行のエラーのセルの値は、見た目「#N/A」です.
しかし文字列の「#N/A」ではなく フィルタオプションの設定の条件は <>#N/A でフィルタします。
下記例で
E1:E2

<>#N/A
です
(X)の関数で抜き出す方法は、Googleで「imogasi方式」照会すると沢山回答例が出ますが略。
その場合エラーで無い行の余白列にサインの1なりを改めてつけておくと考えやすい。
ーー
(Y)のVBAではエラーのセルの判別に知識が要ります。
データ例がA1:B4で
コード値
1a
2#N/A
3b
として
検索表はI1:J2に
1a
3b
とします。
上記例で、例えばB2の式は
=VLOOKUP(A2,$I$1:$J$2,2,FALSE)で出してます。

VBAコードは、一例(方法)として
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
のような判定になります。

ほかにフィルタオプションの設定の操作そのものをVBAで行う方法もあります。
Googleででも「VBA フィルタオプションの設定」げ照会してください。
またフィルタオプションの設定で別シートに結果を出すにはコツが必要ですがGoogleででも照会してください。
VBAで別シートに結果を出すのはSheet2.Cells(j,"A")=Sheet1.Cells(i,"A")のような書き方に
なりますが詳細は略。

参考URL:http://detail.chiebukuro.yahoo.co.jp/qa/question …
    • good
    • 0
この回答へのお礼

お礼が遅くなりました。

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

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

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

お礼日時:2010/05/29 02:45

#N/Aの表示を出さない工夫が必要でしょう。


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

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

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

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

この回答への補足

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

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

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

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

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

宜しくお願いします。

補足日時:2010/05/29 02:43
    • good
    • 0
この回答へのお礼

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

> それを解消するためには一般には次のような式にして使用します。
=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の働きを教えて頂けないでしょうか。

宜しくお願いします。

お礼日時:2010/05/26 16:42

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QエクセルのワークシートAの1列目(A列)の日付と、ワークシートBの1列

エクセルのワークシートAの1列目(A列)の日付と、ワークシートBの1列目(A列)の日付を一致させて1つのワークシートに纏める方法を探しています。

2つの時系列データがあるのですが、データA(為替データ)とデータB(株式データ)のデータ管理方法が下記のように異なっています。

データA(為替データ)・・・1週間のうち6日間(月曜から土曜まで)
データB(株式データ)・・・1週間のうち5日間(月曜から金曜まで)

おのおのワークシートのA列に日付が入っています。

データAは月曜から土曜までなので、1週間に6行要します。データBは月曜から金曜なので1週間に要するのは5行です。従って、単純にこの2つのデータを張り合わせるだけでは(データBをデータAのシートに張り合わせた時)、1週間に要する行数が違うので1行ずつずれてしまいます。

1週間に要する行数の違いだけでなく、マーケットが休みの日はデータそのものがないため日付が存在しません。(マーケットの休みもおのおの別の日でバラバラです。)

これらの理由により、ただ単に張り合わせるだけだとずれが広がるばかりです。

そこでデータAの日付とデータBの日付を認識して一致させる関数を教えて欲しいと思います。

データAの構成はA1=日付、B1=始値、C1=高値、D1=安値、E1=終値で、データBの構成も同じです。
データAのF1にデータAのA1と同じ日付のデータBの行を貼り付るように設定したいと思っています。

説明が分かり辛い場合は補足しますので、その際はご指摘下さい。

宜しくお願いします。

エクセルのワークシートAの1列目(A列)の日付と、ワークシートBの1列目(A列)の日付を一致させて1つのワークシートに纏める方法を探しています。

2つの時系列データがあるのですが、データA(為替データ)とデータB(株式データ)のデータ管理方法が下記のように異なっています。

データA(為替データ)・・・1週間のうち6日間(月曜から土曜まで)
データB(株式データ)・・・1週間のうち5日間(月曜から金曜まで)

おのおのワークシートのA列に日付が入っています。

データAは月曜から土曜までなので、1...続きを読む

Aベストアンサー

誤記がありましたね。ごめんなさい。

シートCのA2に
2010/1/1
を置いて下向けにえいやっとオートフィルドラッグ,365日の日付を並べておいて
B2:為替
=IF(COUNTIF(為替!$A:$A,$A2),VLOOKUP($A2,為替!$A:$E,COLUMN(B2)),"")
を右に下にコピー。
F2:株式
=IF(COUNTIF(株式!$A:$A,$A2),VLOOKUP($A2,株式!$A:$E,COLUMN(B2)),"")
を右に下にコピー。

これでマーケットの開いてなかった日付がエラーになることもありませんので,追加のご質問も解消でしょうか。

Q■□■読み方■□■ #N/A, #VALUE!, #REF!,DIV/0!,#NUM!, NAME?, #NULL

前に、似たような質問をされている方がおりましたが、明確な答えが出ておりませんでしたので教えていただけますでしょうか。
以下のようなエクセルのエラー項目の
読み方を教えていただけませんでしょうか。
#N/A
#VALUE!
#REF!
DIV/0!
#NUM!
NAME?
#NULL
よろしくお願いします。
なお、意味は結構です。あくまで読み方を教えていただきたいのです。
しかも、カタカナでお答えいただくと助かります。
勝手言って申し訳ないですが、
よろしくお願いします。

Aベストアンサー

<方法1>略語の由来となっている英語を言う。
※冠詞は省いています。英語の表現は他にもあります。

#N/A Not Available Value ナット・アヴェイラブル・ヴァリュー
#VALUE! Wrong Type of Value ローング・タイプ・オヴ・ヴァリュー
#REF! Invalid Cell Reference インヴァリッド・セル・リファレンス
#DIV/0! Divided by Zero ディヴァイディッド・バイ・ゼロ
#NUM! Invalid Number インヴァリッド・ナンバー
#NAME? Unrecognized Name アンリコグナイズド・ネイム
#NULL! Null Intersection ナル・インターセクション

<方法2>書いてある字をそのまま読む。

#N/A エヌエー
#VALUE! バリュー
#REF! レフ
#DIV/0! ディブゼロ
#NUM! ナム
#NAME? ネーム
#NULL! ナル(ヌル)

<方法3>適当な英語で代表させて「エラー」をつける。

#N/A ノットアベイラブルエラー
#VALUE! バリューエラー
#REF! リファレンスエラー
#DIV/0! ディバイデッドバイゼロエラー
#NUM! ナンバーエラー
#NAME? ネームエラー
#NULL! ヌルエラー

<方法4>日本語を混ぜる。

#N/A 無効エラー
#VALUE! バリューエラー
#REF! 参照エラー
#DIV/0! ゼロ割りエラー
#NUM! ナンバーエラー
#NAME? 名前エラー
#NULL! ヌルエラー

<方法1>略語の由来となっている英語を言う。
※冠詞は省いています。英語の表現は他にもあります。

#N/A Not Available Value ナット・アヴェイラブル・ヴァリュー
#VALUE! Wrong Type of Value ローング・タイプ・オヴ・ヴァリュー
#REF! Invalid Cell Reference インヴァリッド・セル・リファレンス
#DIV/0! Divided by Zero ディヴァイディッド・バイ・ゼロ
#NUM! Invalid Number インヴァリッド・ナンバー
#NAME? Unrecognized Name アンリコグナイズド・ネイム
#NULL! Null Intersection ナル...続きを読む

Qexcel vlookupで#n/aのとき別シート

excel vlookupでAシートから検索値を参照したときAシートに検索値がなく#n/aが出る状態のときBシートから検索値を探す場合のエクゼルの関数の設定の仕方を教えてください。

Aベストアンサー

ご利用のエクセルのバージョンも書かれてないご相談ですが、エクセル2007以降を使い
=IFERROR(VLOOUP(検索値,Sheet1!A:B,2,FALSE),IFERROR(VLOOKUP(検索値,Sheet2!A:B,2,FALSE),IFERROR(VLOOKUP(検索値,Sheet3!A:B,2,FALSE),"NOT FOUND")))
といった重ね方をしても出来ます。

QエクセルVBAで列名が#N/Aとなった列を削除するプログラムはどのように組めばよいのでしょうか?

エクセルVBAで列名が#N/Aとなった列を削除するプログラムはどのように組めばよいのでしょうか?
下記のような表です。
   A      B      C      D・・・・・
1 イチゴ   #N/A    リンゴ   #N/A
2  100     1      75      1
3  250     2      50      4
4  150     2      25      5

       ↓
   A      B  ・・・・・
1 イチゴ   リンゴ 
2  100     75
3  250     50
4  150     25

よろしくお願いいたします。

Aベストアンサー

こんばんは。

一例です。

Sub test()
  Dim i As Long, myLastCol As Long
  
  myLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

  Application.ScreenUpdating = False
  For i = myLastCol To 1 Step -1
    If IsError(Cells(1, i).Value) Then
      If Cells(1, i).Value = CVErr(xlErrNA) Then
        Columns(i).Delete
      End If
    End If
  Next i
  Application.ScreenUpdating = True
End Sub

QVLOOKUP関数が#N/Aで使えません

こんにちは。
エクセルで表を作っています。
VLOOKUP関数を使いたいのですが、エラー#N/Aが出て使えません。とてもシンプルな式なのに、何がだめでできないのか全く解かりません。
式は、=VLOOKUP(C3&D3,A7:D12,4,FALSE)です。
ちなみに、=VLOOKUP(C2,B7:D12,3,FALSE)にはちゃんと反応して数値を返してくれます。違いは検索値だけなのですが、数字になっているので、文字列に指定もしました。違うセルで=C3&D3は返してくれています。
何がいけなくてできないのか、教えていただけると助かります。
よろしくお願いします。

Aベストアンサー

次の点を確認して下さい。
・A7:A17は文字列でない。
 元々、数値だったのをセルの書式設定で文字列に変更しただけでは駄目です。
 この場合、A7:A17を選択→データ→区切り位置→次へ→次へ→列のデーや形式を文字列にチェック→完了をお試し下さい。
⇒(C3&D3)の文字列がA7:A17に存在しない。
 検索値、又は検索列に余分なスペース等が混入していないでしょうか。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング

おすすめ情報