お世話になります。
IFとISERRORとVLOOKUPを組み合わせて、複数範囲の検索をかけたいと思っています。
画像が小さいかもしれませんが、右の表から探したものを左の表に入れたいのです。
同じワークブックの別シートにある表です。
左の表のシート名は「6」右の表のシート名は「業者別一覧」という名前がついています。
主に入力には業者別一覧の右の表を使用します。
A列にはどちらの表も業者名&月(業者名4というぐあい)が入っていてこれを元に検索します。
業者別一覧のD列に入力した数字を左の表に反映させたくて
=IF(ISERROR(VLOOKUP(A13,業者別一覧!A:I,4,0))),IF(ISERROR(VLOOKUP(A13,業者別一覧!J:Q,4,0))),IF(ISERROR(VLOOKUP(A13,業者別一覧!A:I,4,0))),IF(ISERROR(VLOOKUP(A13,業者別一覧!J:Q,4,0)))
上記を入れたのですが、数式に問題があるというエラーが出てしまいます。
どのようにすればいいでしょうか?
よろしくお願いします。
A 回答 (6件)
- 最新から表示
- 回答順に表示
No.6
- 回答日時:
ANo4です。
>真ん中の,"",で空欄にするように指示しているわけではないのでしょうか?
""の値が採用された時は、0の表示にはならないはずです。
どのような関数式でテストしているのかわからないのと、計算している内容そのものもわからないので、推測しかできませんが…
大雑把に言えば、4か所に点在する表を順に参照して、どこかに検索値があればその値を、どこにもなければ空欄にするという意味の式を作成なさりたいのではないかと想像します。
しつこいようですが、何度もお薦めしている(無視されていますが)No2後半で示した方法をお試しください。
具体的な手順にして、以下に示しておきます。
どこかの空きセルに次の4つの式を入力します。
(わかり易くするため、空白ではなく"ERROR"が表示されるようにしてあります)
=IF(ISERROR(式A),"ERROR",式A)
=IF(ISERROR(式B),"ERROR",式B)
=IF(ISERROR(式C),"ERROR",式C)
=IF(ISERROR(式D),"ERROR",式D)
全体で一つの計算になる予定なので、検索値として参照するセルは全て同じセルになるようにしておきます。(A6ならA6に統一しておく)
この状態で、検索値のセルの値を(手動などで)いろいろ変えて結果を確認します。
各表で検索値が存在すれば、Lookupの結果の値が、存在しなければ「ERROR」が表示されるはずです。
複数の表でヒットすれば、複数個所で「ERROR」以外の値が表示されるでしょう。
これで各式が正しく動作していることをまず確認します。
どこかが違っている場合は、この段階で式を修正します。
(式が単純なので、修正も簡単なはずです。)
全てが正しく計算できていることが確認できたなら、これらを組合せます。
組合せ後の式を作成したい別のセルに、最初の式をコピーします。
コピーの際は、セルのコピーで行うと参照セルがずれてしまいますので、上部の入力バーに表示されている式(文字列)をコピーして行うようにしてください。
(このコピー操作は、以下の操作全てで共通です)
2番目の式をコピーし、最終式の入力バーに表示されている"ERROR"の部分にペーストします。
("ERROR"の代わりに、関数式を上書きでペーストする)
3、4番目の式も順に同様にコピペを行います。
これで、作成したい式が完成しているはずです。
検索値が複数の表に存在する場合は、実際に参照される値(採用される値)は式の記載順序によることになりますので、ご注意ください。
※ 計算の内容がまったくわかりませんので、多分、これ以上のアドバイスは無理と思います。
※ ANo3様が触れておられますが、同じことを行うのでも IFERROR を利用できる環境であれば、そちらを用いた方が関数式はかなり簡略化できるはずです。
No.5
- 回答日時:
No.1,3です
=IFERROR(VLOOKUP(A6,業者別一覧!A$2:I$89,4,0),
IFERROR(VLOOKUP(A6,業者別一覧!J$2:Q$89,4,0),
IFERROR(VLOOKUP(A6,業者別一覧!S$2:Z$89,4,0),
IFERROR(VLOOKUP(A6,業者別一覧!AB$2:AI$89,4,0),""))))
で見通しが良くなるでしょうか?(xl2007~)
たぶん下へオートフィルすると思うので、範囲は絶対参照(正確には複合参照)としました
ちなみに、""はすべて検索値が見つからない場合の表示になります。(文字数0の文字列)
対象範囲の4列目が空白で0が返ってほしくない場合、
文字列が返るようなら =""&数式
数値が返るようなら [Ctrl]+[1]セルの書式設定で
|表示形式| ユーザー定義で
#,##0;[赤]-#,##0;
などとします、
正;負;ゼロ
の場合の表示形式で、ゼロの場合は(存在するけど)表示しないということ
ついでに、数値が返るようなら エラー時の""ではなく0の方が良いかもです
度々のご回答、ありがとうございます。
私の使用しているExcelは2013なのですが、このファイルを共有する相手が古いものを使用しているためにIFERRORが使えないのです(泣)
けれど、書式設定は試してみようと思います。
ありがとうございます。
No.4
- 回答日時:
ANo2です。
>""で区切った前後、同じ範囲から探すようになっていると思うのですが…
エラーチェックということから想像すると、ご提示の式を構成はそのままで簡略化すると
IF(ISERROR(式A),IF(ISERROR(式B),IF(ISERROR(式C),IF(ISERROR(式D),"",式D),式C),式B),式A)
のようになるはずと思いますが、式A~式Dの対応関係が違っていませんかという意味です。
私の解釈が違っていて、意図的にそのような式になさっているのであれば別ですが、私の想定が合っているなら、ご提示の式では、式A~式Dについて前半と後半で全部(4か所とも)が同じ式にはなっていないように見受けられます。
どの式が正しいのかわかりませんので、どこが間違っているのかもわかりません。
しかしながら、同じ式でないということだけは識別できます。
No2の後半で紹介したような方法を取れば、このような間違い(なのかどうか不明ですが)はほとんど起きなくなるはずです。
度々、ありがとうございます。
やっと順序が逆がわかりました!A~DでD~Aということだったんですね~
そして参照もすることができました!
が、今度は空欄のものを参照した時に「0」が表示されてしまうのです。
これは、真ん中の,"",で空欄にするように指示しているわけではないのでしょうか?
よく理解してないのに小難しいことするなよってお思いかもしれないのですが…
今後の手間を少なくするために、今頑張りたいと思っています。
よろしくお願いします。
No.3
- 回答日時:
Excel2007以降の関数ですが、
=IFERROR(値,エラーの場合の値)
を使いたいのでしょうか?
要点だけピックアップした表(添付図)があればよいのですが、何をしたいのかよくわからないです
数式は入力できたようなのでそのセルを選択し
数式 - 数式の検証 - 検証 を何度かクリックしてどのような計算が行われているかを見てください
#N/Aエラーは対象が見つからないエラーです
ご回答ありがとうございます。
要点だけの表を添付したら小さくなってしまったので…すみません。
どうにかこうにか形にはなってきました。
ありがとうございます。
No.2
- 回答日時:
こんにちは
実際の式の意味するところは、添付図が小さすぎるのでさっぱりわかりませんが・・・
補足でご提示の式の構成を簡略化すると
IF(ISERROR(VLOOKUP(AA)),"",VLOOKUP(BB))
がネストしたものと言えます。
(実際には空白("")の部分に、さらに同様の式が入っている形式)
ISERRORを利用している目的はエラーチェックなので、通常はVLOOKUP(AA)とVLOOKUP(BB)の部分を同じ式にします。
(別の式にしてしまうと、VLOOKUP(BB)でエラーが発生するとエラー表示になってしまうので。)
一方で、補足でご提示の式の場合、この対応関係がおかしくなっていませんか?
(順序が逆になっていたりで、対応関係がおかしいように見受けられます。)
組み合わせて複雑な式を作成する際には、分解した式でそれぞれの部分を試した上で、その結果をセル参照する形で関数式を組み合わせてゆく方法が、わかり易く、間違えを起こしにくいと思います。
それで内容が確認できたなら、最後に、セル参照を実際の関数式に置き換えてゆくようにすることで、うっかりミスなども防ぐことができます。
(コピペで式に置き換えてゆけますので)
一見回りくどい方法のように感じられるかもしれませんが、式が複雑になればなるほど有効です。
ご回答ありがとうございます。添付がもう少し大きくできればいいのですが…
赤線から右の表が入力にしようする表となります。その中で網掛けされている部分に入力したものを左の表の同じ項目に入るようにしたいのです。
どちらの表も、表の一番左に業者名&月で表示してあり、それをもとに検索するようにしています。
ベースとなる入力の表を縦1列に並べてしまえば簡単なのですが、そうすると下にどんどん長くなってしまうのでそれはそれでどうかなと思いこの形になっています。
ご指摘いただいた順序が逆の部分がどの部分なのかが見つけられないでいます。
""で区切った前後、同じ範囲から探すようになっていると思うのですが…
いろいろお手数ですが、手探り状態でやっとここまできたのでなんとか完成させたいと思っています。
ご教授よろしくお願いします。
No.1
- 回答日時:
>=IF(ISERROR(VLOOKUP(A13,業者別一覧!A:I,4,0))),
> IF(ISERROR(VLOOKUP(A13,業者別一覧!J:Q,4,0))),
> IF(ISERROR(VLOOKUP(A13,業者別一覧!A:I,4,0))),
> IF(ISERROR(VLOOKUP(A13,業者別一覧!J:Q,4,0)))
上記は
=IF(論理式),IF(論理式),IF(論理式),IF(論理式)
となっているので成り立ちません
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Formulaプロパティーを使ってセルに数式を組んだのですが簡潔にしたい。 3 2022/08/21 20:51
- Excel(エクセル) 表示形式、文字列セル(列)に数式を入力するには マクロ 1 2022/09/18 10:53
- Excel(エクセル) ある数値に対して、値を返す数式についてです 2 2022/09/13 22:06
- Excel(エクセル) マクロを簡潔にしたい 6 2022/09/16 10:37
- Visual Basic(VBA) Sheet1のA列にコードB列にメアド、Sheet2のB列にコード一覧とD列にメアド一覧があり、Sh 3 2022/10/19 11:57
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Visual Basic(VBA) Changeイベントで複数セルへの貼り付けおよび値削除時に1個目のセルのみエラーになる 3 2022/12/21 09:07
- Excel(エクセル) Excelで、別シートの表のステータスに伴った動的な自動転記をしたいです。 2 2023/06/14 15:56
- Excel(エクセル) Excel_マクロ_複数のシートのVLOOKUPで表示された#N/A以外に色付けをしたいです 1 2023/02/16 22:37
- その他(Microsoft Office) エクセルについて教えてください。 2 2022/10/20 14:55
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルの関数について教えて...
-
Excelデータをコピペして、ペー...
-
Excel関数-文字列で自動作成さ...
-
スプレッドシート、Excelでの数...
-
Excelで50個のセルに同じ文字を...
-
Microsoft Officeの中古は信用...
-
スプレッドシートで使う数式を...
-
エクセルVBA、別ブックへ転記す...
-
エクセルで会社の従業員のデー...
-
エクセルで不等号記号(≠)が上に...
-
エクセルの表で1年間の曜日を...
-
A列とB列を参照してC列に連番を...
-
エクセルの空欄をつめて、次の...
-
エクセルでの特別な文字を上に...
-
エクセルでセルに標準で入力さ...
-
エクセル日付 文字列の関数がエ...
-
エクセル2013で月間勤務表から...
-
エクセルの日付を編集する
-
EXCELの質問です 119から足した...
-
【マクロ】アクティブセルにブ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報
あの後、他の所で見つけた
=IF(ISERROR(VLOOKUP(A6,業者別一覧!A2:I89,4,0)),IF(ISERROR(VLOOKUP(A6,業者別一覧!J2:Q89,4,0)),IF(ISERROR(VLOOKUP(A6,業者別一覧!S2:Z89,4,0)),IF(ISERROR(VLOOKUP(A6,業者別一覧!AB2:AI89,4,0)),"",VLOOKUP(A6,業者別一覧!A2:I89,4,0)),VLOOKUP(A6,業者別一覧!J2:Q89,4,0)),VLOOKUP(A6,業者別一覧!S2:Z89,4,0)),VLOOKUP(A6,業者別一覧!AB2:AI89,4,0))
こちらに変更してみましたが、今度は#N/Aになってしまいます。