Ｅｘｃｅｌで定数の使い方を教えてください

質問

例えば「Ａ１」と「Ａ２」のセルを掛けて「Ａ３」に結果を表示させるとした場合、「Ａ１」には数字を入力しますが、その数字ではなく別の決まった数字を代入（？）したいのですが、どうすれば出来るのでしょうか？
１００と入れたら「0.25」を１０００と入れたら「0.75」をというように・・・。因みに入力する数字は２４個しかありません。
また、その２４個以外の数字以外が入力された場合、エラー表示とさせる事は出来ますか？

ヘルプを見ても解説書を見ても分かりませんでした。もし宜しければお願い致します。

回答No.2

HitomiKurose · ベストアンサー

別に1枚シートを使います。そのシートを選択して…

A1からA11に「A1」に使いたい100～3000の値を入力します(24種類でも同様に出来ます)。小さい順にして下さい。
B1からB11にそれぞれ対応する0.25,0.75等の値を入力します。
A1からA11を選択し、Ctrlを押しながらF3を押します。名前欄に「選択肢1」と入力しEnter。
A1からB11を選択、Ctrlを押しながらF3を押し、名前欄に「対応表1」と入力しEnter。
C列・D列には「A2」に使いたい値と対応値を入力し、同様に「選択肢2」・「対応表2」の名前をつけます。

これで準備終了。実際に使いたいシートを選択して下さい。

A1を選択してツールバーのデータ→入力規則→設定タブの「入力値の種類」でリストを選択。「元の値」にカーソルを当て「選択肢1」と入力。
エラーメッセージタブを選びスタイルが停止になっていなかったら停止に直しOK。
A2にも同様に入力規則（今度は「元の値」は「選択肢2」）を設定。

A3に
=VLOOKUP(A1,対応表1,2)*VLOOKUP(A2,対応表2,2)
と入力。

以上でプルダウンメニューとエラーメッセージ（プルダウンを使わずにリスト外の値を入力しようとした場合）に対応出来ています。

補足

詳しくて分かりやすい解説有難うございます。

早速試してみましたが、プルダウンをクリックしても「選択肢１」としか出てこなくて１００～３０００の数字が選択できないのです。何がいけないのでしょうか？
解説の通りに順番にやってみましたが、どうしても出来なくて困っています。
お手数ですがご教授下さいますようお願い致します。

それから後の参考までに「=VLOOKUP(A1,対応表1,2)*VLOOKUP(A2,対応表2,2) 」の意味も教えていただけると有りがたいです。具体的には式の中の”対応表１,”の後の「２」はどういった意味なのでしょうか？全くの初歩的な質問で申し訳ありませんが合わせてお願い致します。

回答No.7

comv

今見たら「,」一箇所抜かしてました・・・！
失礼しました！

=IF(COUNTA(C12,E12,F12)<3,"",VLOOKUP(C12,対応表１,2,0)*(VLOOKUP(E12,対応表２,2,0)-VLOOKUP(F12,対応表3,2,0))

回答No.6

comv

みなさん　こんにちは！

>ところで、「Ａ２」に日本語を入力したのですが
ありゃ・・・項目は数値じゃないのですか(^^;
文字列が検索ちであれば入力のチェックは
COUNT() → COUNTA()

>選択肢の下を選ぶと「＃Ｎ/Ａ」と
想像ですが、VLOOKUP()の数式で、検索型を指定せずに
検索値に数値の昇順でないものを使ったためです。
VLOOKUP(A1,対応表1,2) → VLOOKUP(A1,対応表1,2,0)
のように最後に「,0」と完全一致を指定して下さい。

結果、以下のようになります。

>　=VLOOKUP(C12,対応表１,2)*(VLOOKUP(E12,対応表２,2)-VLOOKUP(F12,対応表3,2))
以上で上記の式も対応可能です。
↓
　=IF(COUNTA(C12,E12,F12)<3,""VLOOKUP(C12,対応表１,2,0)*(VLOOKUP(E12,対応表２,2,0)-VLOOKUP(F12,対応表3,2,0))

上記の式をコピーして数式バーに貼り付けてみて下さい。

お礼

お世話様でした。
やっと２日前に完成致しました。
思っていた以上のものが出来あがり大変満足しています。

これも皆さんのアドバイスのおかげです。
本当に有難うございました。

＞ありゃ・・・項目は数値じゃないのですか(^^;
すいませんでした。作っているうちに文字の方がスッキリ作れるのがわかったので同じようにしてみたら出来るかななんて思ったものですから・・・。

回答No.5

HitomiKurose

comvさん、ありがとうございます。toshi777さん、ごめんなさい。
入力規則の元の値に「=」を付けなかったのは確かに間違いでした。
尚、元の値欄でF3を押すことで定義された名前を選択することも出来ます。

お礼

HitomiKuroseさん、有難うございました。
お蔭様で２日前に完成致しました。
予想以上の出来で大変満足しています。
丁寧で分かりやすい回答、本当に有難うございました。

補足

いえいえ、気づかない私のミスです。
Ｆ３を押しても出来るんですね。
有難うございました。

でもcomvさんのレスでも書きましたが、選択肢の下を選ぶと「＃Ｎ/Ａ」と返ってきて意味もわからず困ってしまいました。
ヘルプを読んだのですが、何の事か理解に困っています。
もしお時間があればアドバイスをお願い致します。

回答No.4

comv

みなさん　こんばんは！
sdaruさんとHitomiKuroseさんのレスを合わせたような回答ですが！

まず名前の定義はちゃんとできているとの前提で！
（HitomiKuroseさんが使った名前をお借りします）

それぞれの、入力規則　リスト　元の値の欄に
　セルＡ１の場合　=選択肢1　＜・・・イコールを付ける
　セルＡ２の場合　=選択肢2　＜・・・イコールを付ける

次にＡ３の計算式ですが、基本はHitomiKuroseさんのもの
でＯＫですが、シートを保護しないのであれば、コピペや
置換で入力規則以外のものを入れることができてしまう
ためその時にエラーを出すものを付加した方がよいので

　=IF(COUNT(A1:A2)<2,"",IF(ISERROR(MATCH(A1,選択肢1,0))+ISERROR(MATCH(A2,選択肢2,0)),"エラー",VLOOKUP(A1,対応表1,2,0)*VLOOKUP(A2,対応表2,2,0)))

（これはsdaruさんのレスに記載されていた部分ですね^^）

または
処理ルーチンは多いが、数式は多少簡単なもの

=IF(COUNT(A1:A2)<2,"",IF(COUNTIF(選択肢1,A1)+COUNTIF(選択肢2,A2)=2,VLOOKUP(A1,対応表1,2,0)*VLOOKUP(A2,対応表2,2,0),"エラー"))

シートを保護するのであれば！
=IF(COUNT(A1:A2)<2,"",VLOOKUP(A1,対応表1,2,0)*VLOOKUP(A2,対応表2,2,0))

でＯＫだと思います！

＞”対応表１,”の後の「２」はどういった意味なのでしょうか？

指定範囲の１列目で検索してヒットした行の「２列目」の
値を返すって意味です。
今回の場合 A1が100　の時　対応表1の１列目の１００が
記載されている行の隣（２列目）の0.25が値となります。

まずは、ヘルプを見ることを習慣にするよう、お勧めします！
事例も含めて結構詳しく書かれていますから！
アシスタント（カイルくん^^;）の検索欄で VLOOKUP 関数
と入力して検索で出てきます！

お礼

すいません。お礼の欄を使ってしまいましたが、補足の補足です。

「６泊７日」「１３泊１４日」が出来ないのではなく、「１３泊１４日」と「１ヶ月以内」が「＃Ｎ/Ａ」で計算結果が返ってくるのです。

訂正でした・・・。

補足

丁寧な補足有難うございました。

早速「＝」を入れてみたら出来ました！！本当に有難うございます。

＞まずは、ヘルプを見ることを習慣にするよう、お勧めします！
事例も含めて結構詳しく書かれていますから！
そうですよね。私もヘルプを見る癖はつけているのですが、私にとっては難解な言葉で書かれているため殆ど理解できないのです・・・・。情けない話ですが。
もう少し解説書なりを購入して勉強したいと思います。

ところで、「Ａ２」に日本語を入力したのですが、うまく行く所と、そうでないところがあるんです。
具体的には「３泊４日」「６泊７日」「１３泊１４日」「１ヶ月以内」を選択できる様にして、それぞれ返す値は２５・３０・４０・６５なのですが「６泊７日」「１３泊１４日」を選択すると「＃Ｎ/Ａ」が出てきてしまうのです。ヘルプを見ましたがチンプンカンプンです。
お手隙の時で結構ですので教えていただければ有りがたいです。

あと計算式で「Ａ２」から最初に数値を引くことは可能ですか？
具体的には仮に「Ｂ１」を引くとします。
選択肢は「免責無し」「免責１０００円」で返す値はそれぞれ０・０．２５です。
これもやってみたら「免責１０００円」だけが「＃Ｎ/Ａ」で返ってきてしまいました。
式はHitomiKuroseさんに作っていただいたものを使用しています。=VLOOKUP(C12,対応表１,2)*(VLOOKUP(E12,対応表２,2)-VLOOKUP(F12,対応表3,2))と言う式ですが、何処がおかしいのでしょうか？

それでは宜しくお願い致します。

回答No.3

sdaru

先に回答したsdaruです。
簡単に返事しましたがやって見ましたが結構難しいです。

どなたか先に回答がありましたら参考にして下さい。

私が間に合えばいずれ回答します。

お礼

真っ先に回答していただき本当に有難うございました。
やっと完成致しました。

また何かありましたら宜しくお願い致します。

回答No.1

sdaru

質問の意味が良く分からない部分があります。

100の場合は0.25に、１０００の時には0.75に固定するのか
２４個の数字はどのような数字なのか？

Ａ１*Ａ2の結果をＡ３に表示するときどのセルを固定するのか？

でもお尋ねの方法は可能です。

エクセルの関数を使えば幾通りかの方法が有ります。

エクセルの表示画面を（レイアウト）どのようにするかは貴方の考えに
より変わってきます。

例えば24個の数字を常に画面に表示して置くとか、固定した数字を入れたセルの
表示に保護を掛けて他の数字や文字の入力をさせないとか

ＩＦ，ＬＯＯＫＵＰ、ＭＵＣＨのような関数を使うとか等々。
エクセルには関数を使えば色々有りますが。

関数の使い方の参考書は色々有ります。私はエクスメディア社の超図解
エクセル関数変を参考にして勉強しています。

補足

早速の回答有難うございます。

２４個の数字はそれぞれ１００・２００・２５０・３００・５００・７５０・１０００・１５００・２０００・２５００・３０００で、ほんとはもう少し（あと１３個）足したいのですが別の方法でそれは対処できます。

ほんとはプルダウンで１１個の数字を選択できればいいのですが難度が高そうなので下に選択できるようにそれぞれの数字を表示しておきます。

＞100の場合は0.25に、１０００の時には0.75に固定するのか
完全に固定です。別の値は使いません。

＞Ａ１*Ａ2の結果をＡ３に表示するときどのセルを固定するのか？
どのセルも固定しません。Ａ１もＡ２も数字を入力します。（Ａ２もＡ１と同様に入力した数字とは別の値で計算出来る様にしたいのです）

私の参考書は入門書で関数は殆ど（ＳＵＭ等の基本的なもの）載っていないもので後日探してみる予定です。