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を判別するようなものにすればいいんだと思いましたが、その具体方法が分かりません。
マクロも初心者なので、出来るだけ具体的に教えて頂けると助かります。
宜しくお願いします。
No.1ベストアンサー
- 回答日時:
>この#N/Aが表示されている行を削除するにはどんなマクロを組めばいいのでしょうか。
先のご相談では,片方のマーケットしか開いていない日もあるというお話でしたね。
その前提に立つと単純に土日だけでなく,為替列でエラーになっている行とか,株式列だけがエラーになっている行などもあるはずです。
つまりやるべきは「#N/Aが表示されている行を削除する」ではなく,「B列とG列が共に#N/Aになっている行」を抽出して削除ということになります。
#以下のご説明は,マーケットの無い日がエラーでも""でも同じです。
手順:
オートフィルタを取り付ける
B列でエラー値(若しくは空白)で絞る
G列でエラー値(若しくは空白)で絞る
共にエラー値(若しくは空白)である行だけが残るので,選んで右クリックして削除で行単位で上に詰めて完成。
返信遅くなりました。
ご回答ありがとうございます。
単純にオートフィルタで良かったんですね。
先の質問でもご丁寧に教えて頂いて助かりました。
ありがとうございます。
No.4
- 回答日時:
ん? まだ解決してないって事は,コピーして動かせるマクロをとにかく誰かが書いてくれるまで引っ張りたいって事でしょうか?
その前に,そもそも前のご質問でエラーの解消は出来たんでしょうか。
それとも,前の回答の式は破棄してご自分の式で行くことにしたので,まだエラーが出たままなのでしょうか。その場合はもう前のご相談とは話が違うのですから,「今こういう式にしています」ときちんと情報提供してください。そういった「今こうなっている」が不明のままマクロだけ人に書かせても,的外れなままです。
エラーの消去も,こちらのご質問で先に回答しておいたように,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
> ん? まだ解決してないって事は,コピーして動かせるマクロをとにかく誰かが書いてくれるまで引っ張りたいって事でしょうか?
紛らわしくてすみません。当初やりたかった事は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
- 回答日時:
質問の内容をやや一般化して、「そもそも論」をやってみます。
回りくどいと思うと思いますが少しお付き合いください。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 …
お礼が遅くなりました。
詳しく教えて頂いてありがとうございます。
マクロも今勉強中なのですが、このVBAは今の私にはちょっと難しそうなので、もう少し勉強してからトライしたいと思います。
色々とありがとうございました。
No.2
- 回答日時:
#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の働きについて教えて頂けないでしょうか。
宜しくお願いします。
ご回答ありがとうございます。基本的質問で恐縮ですが、混乱しているのでお助け下さい。
> それを解消するためには一般には次のような式にして使用します。
=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の働きを教えて頂けないでしょうか。
宜しくお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) Changeイベントで複数セルへの貼り付けおよび値削除時に1個目のセルのみエラーになる 3 2022/12/21 09:07
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Excel(エクセル) ExcelにおけるVLOOKUPでの祝日表示について 5 2022/09/18 22:23
- Excel(エクセル) VBA でvlookup エラーなどは削除したい 8 2022/12/30 04:03
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Excel(エクセル) excelで検索した商品の画像(ネットワーク上の)を表示させたい。 3 2023/06/28 00:32
- Excel(エクセル) 製品番号での整列と、検索に関して 3 2023/06/28 19:20
- その他(Microsoft Office) Excel2019と365、2021 2 2023/07/08 06:22
- Excel(エクセル) Excel_マクロ_複数のシートのVLOOKUPで表示された#N/A以外に色付けをしたいです 1 2023/02/16 22:37
- Excel(エクセル) 【エクセル】参照セルに何も入力が無い場合の空白表示方法 1 2022/05/26 10:01
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教える店舗&オフィスのセキュリティ対策術
中・小規模の店舗やオフィスのセキュリティセキュリティ対策について、プロにどう対策すべきか 何を注意すべきかを教えていただきました!
-
マクロで#N/A"のエラー行を削除したい"
Visual Basic(VBA)
-
IF関数などを使って条件に合った「行」を削除するような機能はありますでしょうか?
Excel(エクセル)
-
エクセル Vlookup関数の入ったセルで関数を削除して値だけを残す方
その他(Microsoft Office)
-
-
4
エクセル マクロ エラーのある行を削除
Excel(エクセル)
-
5
#N/Aの文字を削除するには
Visual Basic(VBA)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【マクロ】エクセルにかいてあ...
-
エクセルの行の抽出について質...
-
Excel初心者です。 詳しい方、...
-
スプレッドシート クエリ関数 1...
-
別シートからの文字を変更
-
Excel初心者です。 詳しい方、...
-
Excel 2019 のピボットテーブル...
-
MOS365 Excel Expert / Excel R...
-
エクセルの数式で教えてください。
-
数学 Tan(θ)-1/Cos(θ)について...
-
4つのパターンを表示するEXACT...
-
各ページの1番上の表示について
-
INDIRECTを使わず excelで複数...
-
関数を教えて下さい。
-
Excelのグラフ軸について
-
Excel:一部のフォントでセルの...
-
エクセルで指定した日付、店舗...
-
Excel ウインドウ枠の固定をす...
-
EXACT関数とIF関数の組み合わせ...
-
写真のコピー
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報