IF＆VLOOKUP関数とテキストの併用は可能？？

質問

いつも大変お世話になり助かっております。
どなたかご教授頂けると非常に助かります、また無茶な質問でしたらご容赦下さい。

エクセルを使い始めたばかりなのですが、見積フォームを作成中です。
図のような「関数を用いたリストからの選択」もしくは、「フリーでテキストを打ち込む」といった
ことは可能なのでしょうか？ご回答宜しくお願い致します。

回答No.2

tom04 · ベストアンサー

こんにちは！
No.1さんが回答されていらっしゃる通り「手入力」を行いたい場合は
入っている数式も消えてしまいます。

そこでB列に入力された時点でC・E列にVLOOKUP関数を適用するのはどうでしょうか？
VBAになってしまいますが、一例です。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてB列に「品番」を入力してみてください。
VLOOKUP関数の範囲に入力されたデータがない場合のみ、何も表示されないようにしています。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim c As Range
If Application.Intersect(Target, Range("B:B")) Is Nothing Or Target.Count <> 1 Then Exit Sub
With Target
Set c = Range("H:H").Find(what:=.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
.Offset(, 1) = WorksheetFunction.VLookup(.Value, Range("H:J"), 2, False)
.Offset(, 3) = WorksheetFunction.VLookup(.Value, Range("H:J"), 3, False)
End If
End With
End Sub 'この行まで

※　実際の表のレイアウトがアップされている配置とは異なっていると思います。
その場合コードをレイアウトに合わせて変更する必要があります。m（_ _）m

お礼

ご回答ありがとうございます。
No.1さんのおっしゃる問題点をクリアできるご回答の様に思います。
VBA、便利ですねー。ただ自分で導き出せと言われたら....不可能ですが。
仰せの通りやってみましたが、現段階ではこの式を適用させて頂くのが
一番理想的と感じました。感謝の気持ちでいっぱいです、本当にありがとうございます。そしてこの場を借りて、ご回答頂いた皆様に改めて感謝いたします。

回答No.5

MarcoRossiItaly

既に皆さんが回答されているとおり、同一のセルにおいて、「数式による計算結果の表示」と「上書きせずに手入力を受け付けること」を同時に達成することはできません。

No.1 さんのおっしゃるように、（数式ではなくメニューの）リストによる入力制限は、いちいちオン／オフを切り換えることもできますが、それには手間がかかるし、そのブックを他者と共有する場合は、初級者向きでもありません。

★
任意の文字列を入力できるようにしたいということは、添付された図の「品名」の種類数がいくつになるのか、不定ということでしょうかね。であるならば、数式の使用はやめて、手入力のみにすることをお勧めします。

既存の文字列・数値を繰り返し入力したいときは、コピペすればいいだけです。Ctrl+セルのドラッグという操作でもコピペできますね。Alt+↓というショートカットキーもあります。オートフィルでもいいです。また、「入力の候補データをリストで用意しておくが、リストにない文字列の入力も同時に可能にする」という設定も可能です。いずれにしても、数式は用いません。

なお No.4 さんが指摘されている「手入力によりどこかのデータが上書きされるリスク」が気になるという場合は、シートの保護を使ってください。同一シート上で、保護された状態でも上書きできるセルとできないセルを、指定できます。

どうしても「品名」に数式も併用したいということなら、それ用の列を別に用意するといいでしょう。後で、適当な処理を施せば、その列と手入力用の列のデータをドッキングさせることもできます。なお列を分けていない見積書も必要なら、別シートに見積書を用意し、入力用シートから見積書シートにデータをリンクさせて表示すればいいだけです。

回答No.4

Nouble

手入力と既存の関数表示を混在させたい
と、云う事ですか？

現在フォームを作成中なのですよね？
関数式が　解らない
と、いう事ではなさそうですので
上記内容を気にされている事と思います。

関数式を敷き詰めた「使い回すシート」にとって「手入力」は天敵です。
いっぺんで壊れて　次ぎ使うのが怖すぎです。

この事はもうお気付きですね。

では同じ内容のシートを量産できるようにし、
一度使ったシートを「使い回さない」ようにすれば
どうなるでしょう？

作ったシートに何を書き込もうと、いっそ捨ててしまおうと、
何ら困らなくなりますよね？

と、云う事で、
これを叶えるには簡単な事、
テンプレートとして保存し、テンプレート用ホルダー以外の
何処か解り易い所に、置いておいて、
それを開くようにすれば良いのですよ。

また
マスターテンプレートとして
運用用とは別に、
どこかに保管用を置いておけば
完璧でしょうね。
もう何もほぼ怖くないでしょうね。

所で
テンプレートホルダーでもまあ良いのですが、
メンテナンスとか　使い勝手が悪いですね。

さて、
テンプレートにするのは至極簡単。
保存形式を選ぶだけです。

これで
新規作成したファイルに何をしようが、
いっそ燃やそうが…　行きすぎかな？　汗
根幹のテンプレートファイルさえ残っていれば
へっちゃらです。

但し1つ留意点が、
テンプレートファイルを使って
新規作成したファイルに
変更したい箇所が見つかり、「変更したい」と、
そのファイルに書いてはいけません。

ちゃんと
テンプレートファイルを編集できる状態で開いて
書き込む癖を付けないと

「あれ？この変更適応されていないぞ！！」
なんて、商務無い事になってしまいます。

如何でしょうか？
お役に立てていたならば幸いです。

お礼

ご回答ありがとうございます、関数で管理されたものと手入力というのは、
確かに混在するとカタチが崩れやすくなりそうですね。
そのことも踏まえてテンプレの作成を検討してみますm(_ _)m

回答No.3

KURUMITO

お示しの表でしたら関数を用いたリストからの選択ということはできませんが、例えば品名についてＩ列に記載のデータをＣ8セル以降のセルでリストから選択できるようにするには次のようにします。
初めにＣ8セルから下方のセルを範囲として選択します。その後に「データ」タブの「データの入力規則」から「データの入力規則」を選択します。表示の画面の「入力値の種類」で「リスト」を選択し、「元の値」の窓には　=I$7:I$100 と入力しＯＫします。出来ればＨ７からＪ７セルは空のままのセルにしておきます。そのことでリストの際の表示が先頭の品名からの表示になるのです。
これでＣ8セルから下方のセルを選択すれば▼が表示され、それをクリックすることでＩ列の品名が表示されますのでそこから選択することができます。
Ｂ列でリスト表示をしてもよいのですが番号から直接に品名を連想するのが容易ではないと思ったので品名でリスト選択ができる方法を示しました。
その場合にはＢ列の番号についてはＢ8セルに次の式を入力して下方にドラッグコピーします。

=IF(C8="","",INDEX(H:H,MATCH(C8,I:I,0)))

E列の単価についてはＥ8セルに次の式を入力して下方にドラッグコピーします。

=IF(C8="","",INDEX(J:J,MATCH(C8,I:I,0)))

番号をリストから選択してから品名や単価を表示させるのでしたらＶＬＯＯＫＵＰ関数を使うことができますが、ここでは品名をリストから選択する方法について述べました。
Ｉ列の品名を追加することで新しい品名についても対応することができます。

お礼

ご回答ありがとうございます。
早速お教え頂いた関数を試してみました。
品名から他を参照する。こんなやり方もあるんですねー。
関数は基礎の「き」しか解らないので色々とこれから使えそうです。
ありがとうございます、また一つ勉強になりました。

回答No.1

akiomyau

結論から言えば可能です。
入力規則で設定タブでリストを選んで設定を行った後、
エラーメッセージタブで停止以外のもの
注意や情報を選べば、リストもしくはフリー入力が可能になります。
Vlookup関数の部分に関しては
リストにない場合エラーになってしまうので
IF関数とISERROR関数を使って、”手入力してください”など表示をさせてはどうでしょうか。
但し、一つだけ問題点としては手入力した場合式が消えてしまうので変更する場合不便ですが。

お礼

ご回答ありがとうございます。
入力規則を使う....なるほどそういう使い方ができるんですね。
知らなかったのでまた他でも役に立ちそうです。
おっしゃるように式が消えてしまうのは、テンプレとしては難点ですね。
そのことも含めて検討してみます、たいへん参考になりました。