ExcelでVLOOKUP関数で弾かれた場合、別の処理を行いたい場合

Question

先の質問の続きで申し訳ございませんがお付き合い頂けると幸いに存じます。

先程は○○予定を○時○○分に到着するために○時○○分を出力する、と言った内容の質問をしました。

別シートに固定された予定の場合の情報が登録してあったので、VLOOKUP関数を用いて出力するところまでは上手くいきました。

次にやりたいことなのですが、別シートにある固定された予定以外の情報が入力された時に、違う処理をしたい場合どうすればいいかという質問です。

例えば、固定された予定の情報に、○○予定の情報しか登録されてなかった場合、○✕予定の入力がされると#N/Aが返ってきます。

この#N/Aが返ってきた時に、別の処理を行いたいのです。

例えば、○✕予定が時間は固定されていなくても地名が固定されている場合、別シートにその地名までの所要時間を登録することは可能なので、例えば

A地点:30分
B地点:1時間
C地点:1:45分
とした場合に、

○✕予定 C地点4:45到着
と入力した場合に、出発時間を3:00と出力したいわけです。

要約すると、
決められた予定の○○予定○時○○分が入力されたときはVLOOKUP関数でそのまま登録されている出発時間を出力し、
それ以外の○✕予定○地点○時○○分が入力されたときは登録されている地点名から所要時間を逆算した時間を出発時間に出力する、といった具合に動作させたいということです。

更に言うのであれば、○✕予定に地点名こそあるものの時間が入力されていない場合は出発時間には空白を出力出来るようにしたいです。

可能でしょうか。

因みに先程言い忘れてましたが、
地名と到着予定時間は同一セル内に入力されます。
C地点○時○○分
と言った感じです。

よろしくお願いします。

Answer 1

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

最終的な解決方法としては、セルを分けることで対応しました。

固定された予定は固定された予定のセルを作り、VLOOKUPとIFERRORで検索、無い場合は0を出力

そうでない予定はそれ用のセルを4つ作り、
セル1に地名、セル2に時間とそれぞれ分離させる処理を行い、セル3でVLOOKUPにて所要時間検索を行い(当てはまらない場合同じく0を出力)、セル4で時間から所要時間を引き算する処理を行うようにしました。

メインで出力するセルにはif関数を用いて、｢固定された予定がある場合はそのまま出力し、0が帰ってきてたらそうでない予定の処理済のセル4を表示、セル3が0を返してたら空白を出力｣
という処理にて完成致しました。

別の質問でも投げましたが、
○✕予定○:○○分と同一セル内に入力されているのが厄介で、これらを分離させる処理を挟まなければならず1つのセルに入れる関数だけでは長すぎて厳しかったと思います。

Answer 2

Office365かOffice2021であればXLOOKUPが使えますが、XLOOKUPでは「見つからなかった場合の式」を指定できます。
XLOOKUPが使えない場合は、IFNAで。

Comments

[Ay8709]

IFNA関数にて時間指定が無い場合に空白を出力させることに成功しました。ありがとうございます。

当方会社のPCはOffice365ですが自前のPCは2019だったためXLOOPUPが使用できませんでした。

下の方の返信にも書いたのですが、現在では、
=IFNA(VLOOKUP(検索値,別シート!G5:I26,3,FALSE),IFNA(VLOOKUP(検索値,別シート
!P5:R7,3,FALSE),""))
とし、1度目のVLOOKUPで決められた予定の出発時間を割り出し、N/Aの場合は次に、それ以外の予定での時間が入力されている際の”移動時間”を表示し、それ以外（時間が入力されていない場合など）に空白を返す、という風に出力しています。
問題は二つ目の処理で「移動時間を表示」させることは出来ても、逆算した時間を表示させることが出来ない点です。

自分なりに考えた結果、
一度結果を別のセルに出力して、if関数等とVLOOKUPを併用して、決められた予定ないし時間の入力がない場合はそのまま出発時間のセルに出力し、それ以外の予定で時間の入力がある場合のみ、VLOOKUPで返ってきた値を再度SUM関数等で計算し、出発時間のセルに出力する、といった挙動は可能かどうか？

と考えてみましたが、これは実現可能かどうかご教授願いますでしょうか。

[otn]

よくわかりませんが、移動時間が VLOOKUP(検索値,別シート!P5:R7,3,FALSE) で求まるなら、
到着時刻からそれを引けばいいのでは？
=IFNA(VLOOKUP(検索値,別シート!G5:I26,3,FALSE),IFNA(到着時刻セル-VLOOKUP(検索値,別シート
!P5:R7,3,FALSE),""))

[Ay8709]

試してみましたが到着時刻セルに地名が同時に入力されている影響か、#VALUEエラーを吐いてしまいました。
どうにかして別けられないか、試してみます。ありがとうございます。

[otn]

計算したいのなら、到着時刻セル（文字列じゃなくて時刻数値データ）が必須ですね。

Answer 3

とりあえず、ひとつ思いつくのはIFNAかな。

=IFNA(VLOOKUP(検索値,範囲,列番号,検索方法),NAの場合の値)

---

目的地	所要時間
A	0:30
B	1:00
C	1:45
```
[出発時間]
=到着時間-VLOOKUP(目的地,所要時間表のセル範囲,所要時間の列番号,FALSE)
```
※そもそも、すべての場合をこの「まず所要時間を求めて、そこから出発時間を逆算する方法」に統一することはできないのでしょうか。

---

参考までにSUMIFSという方法も提示してみます。

目的地	行先	所要時間
A地点	0:30
B地点	1:00
C地点	固定された行先	1:45
C地点	それ以外の行先	1:30

=SUMIFS(所要時間列,目的地列,"C地点",行先列,"固定された行先")

Comments

[Ay8709]

大変参考になる回答ありがとうございます。
ご教授頂いた関数で再度トライしてみます。

予定が全部固定になっていれば全部逆算でも良かったのですが、
地名が同じでも固定された行き先とそうでない行先で場所が違うパターンがあり、その場合所要時間も変わってしまう為、このような形式を採っています。

[Ay8709]

IFNA関数にて時間指定が無い場合に空白を出力させることに成功しました。ありがとうございます。
SUMIFS関数も試してみます。ありがとうございます。

因みに現在は、IFNAとVLOOKUP関数を組み合わせて何とかできないか試行錯誤しているところでした。

現在では、
=IFNA(VLOOKUP(検索値,別シート!G5:I26,3,FALSE),IFNA(VLOOKUP(検索値,別シート
!P5:R7,3,FALSE),""))
とし、1度目のVLOOKUPで決められた予定の出発時間を割り出し、N/Aの場合は次に、それ以外の予定での時間が入力されている際の”移動時間”を表示し、それ以外（時間が入力されていない場合など）に空白を返す、という風に出力しています。
問題は二つ目の処理で「移動時間を表示」させることは出来ても、逆算した時間を表示させることが出来ない点です。

自分なりに考えた結果、
一度結果を別のセルに出力して、if関数等とVLOOKUPを併用して、決められた予定ないし時間の入力がない場合はそのまま出発時間のセルに出力し、それ以外の予定で時間の入力がある場合のみ、VLOOKUPで返ってきた値を再度SUM関数等で計算し、出発時間のセルに出力する、といった挙動は可能かどうか？

と考えてみましたが、これは実現可能でしょうか？

[jinoji]

セルを分けても勿論できると思いますし、その方が考えやすいのかもしれません。
それがうまくいったら、それをたとえば
=IFNA(VLOOKUP(検索値,別シート!G5:I26,3,FALSE),IFNA(到着時間-VLOOKUP(検索値,別シート
!P5:R7,3,FALSE),""))
のようにすることで１つにすることもできるかもしれません。