「関数の挿入」ダイアログボックスを表示させる

エクセル関数練習問題。VLOOKUPの2回目です。

利用したエクセルファイルをこちらに添付しました。
必要に応じてダウンロードしてご利用ください。
エクセル関数練習問題エクセルファイル

問題
ヒント
解答例

問題

前回と同じ表を利用して「請求書」を作成します。
請求書の書式に受注番号を入力し、VLOOKUPで受注内容を持ってくる、という内容です。

こちらが元データとなる「取引情報」シートです。

請求書シートのセルC４に受注番号を入力すると、青色のセルに取引先名、受注内容を持ってくるようにします。

①請求書右上の日付のセルは今日の日付が出るようにします。（VLOOKUPは使いません）
②セルB５には取引先名が出るようにします
③13行目より下の請求明細には取引情報シートから受注内容を出力します。

ヒント

①Today関数を使います

②取引明細シートから取引先名を持ってきます。キーは受注番号にします。

③ちょっとひねり問題です。
キーは受注番号と受注明細番号を使用します。

解答例

①の解答例

①まったくひねりも何もなく、TODAY関数を入力します。

=TODAY()

とセルG４に入力します。
()内にはなにも書きません。
今日の日付が表示されます。
なお、別の日にこのエクセルを開くと、TODAY関数を入力した日ではなく、エクセルを開いたその日の日付が表示されます。あたりまえですが。

リボン「数式」の「日付・時刻」からTODAYを選択して入力する方法でもいいですね。

②の解答例

②はVLOOKUPを使って、取引情報のシートから受注番号に対応する顧客名を持ってきます。

セルB5にVLOOKUP関数を入力します。
検索値は「請求書」シートC1の『受注番号』
絶対参照にしてもしなくてもよいのですが、なんとなく絶対参照にしています。
範囲は「取引情報」シートのセルB4～L１１を指定しています。
検索値となる『受注番号』が範囲の左端になるようにします。検索値（キー）が範囲の左端でないとVLOOKUPできません。
L列の住所まで範囲としていますが、持ってきたい情報のあるJ列まででも大丈夫です。
絶対参照にしていますが、絶対参照でなくても大丈夫です。
列番号は範囲の左端から数えて9列目の取引先名を持ってきたいので9を指定します。
検索方法は完全一致のFalse（または0）です。

ところで、数式は直接セルに手入力してもよいのですが、VLOOKUPは慣れるまでは関数の挿入から行いダイアログボックスを表示させて入力するほうが分かりやすくてよいと思います。

関数の挿入方法①

リボン「数式」の「検索/行列」からVLOOKUPを選択します。アルファベット順なのでVLOOKUPは下の方にありますね。

関数の挿入方法②

関数の挿入ボタンから、関数の挿入ダイアログボックスを表示させて、そこの「検索/行列」からVLOOKUPを選択しても同様です。一度使用した関数は「最近使った関数」からも選択できるので、それが一番らくちんですね。

「関数の挿入」ダイアログボックスを表示させる方法

ちなみに「関数の挿入」ダイアログボックスを表示させるには、
（１）ショートカットキー　Shift＋F3
（２）リボン「数式」の「関数の挿入」ボタン

（３）数式バーの関数の挿入ボタンクリック

（４）リボン「ホーム」の「ΣオートSUM」ボタンから「その他の関数」

等、各種ありますが（１）（３）がらくちんでお勧めです。

③解答例

③受注明細の解答例です。

検索値は受注番号だけではだめ（明細が1行だったら大丈夫なのですが）。
受注番号と明細番号をくっつけたものを検索値（キー）とします。
適当な余白に検索値（キー）の列を設けて検索値を入力しておきます。
I13セルに以下の式を入力して、コピーし、明細の最下行まで貼り付けします。

=$C$1&”-“&B13

下の行にコピーする必要があるため「受注番号」のC1は行列の絶対参照にします。明細番号B13はコピーしたときに行番号がずれてほしいので絶対参照にはしません。
ハイフンでつないでいますが、とくにハイフンを入れなくても大丈夫です。重複しないキー値が作成できれば大丈夫です。

受注番号や明細番号が1桁を超える場合は、
受注番号1、明細番号11　＝＞　ハイフンなしだと　111
受注番号11、明細番号1　＝＞　ハイフンなしだと　111

と、キー値が重複してしまうためハイフンなどを入れて区別がつくようにしないといけません。
ハイフンありだと以下のようになり区別がつきます。

受注番号1、明細番号11　＝＞　1-11
受注番号11、明細番号1　＝＞　11-1

さて、VLOOKUPを入れよう！と、取引情報のシートをみると、そういえばこちらにも受注番号と明細番号をつなげた検索値キー）がないといけませんね！

参照範囲の方は検索値（キー）が範囲の一番左である必要があります。ちょうどA列がなにも値がないので、ここに入れていきます。

参照範囲の取引情報シートのA列に検索値（キー）を入力していきます。
請求書シートと同じように「受注番号」と「明細番号」をハイフンで繋げます。
=B4&”-“&C4

今回たまたまA列が開いていたので範囲の一番左にキー値を入れることができましたが「左に列がない！」というときは。。。列を挿入するなどして参照範囲の一番左がキーになるようにしなければいけません。

さて！いよいよ請求書シートにVLOOKUPを入れていきます。

まずは日付のセルC13にVLOOKUPを入力しましょう。

=VLOOKUP($I13,取引情報!$A$4:$L$11,4,0)

検索値：作成した検索値、セルI13を指定します。式をコピーするので列の絶対参照にします。行はコピーしたときにずれてほしいので行は絶対参照にしません。

範囲：取引情報シート、セルA4～L11を指定します。式をコピーしても範囲は固定されるように、行列の絶対参照にします。作成した検索値のある列から指定するのを忘れずに！H列まででもよいのですが、今回はなんとなくL列まで指定しています。

列番号：日付の列は作成したキー値のある列からかぞえて４列目なので「４」を指定します。

検索方法：完全一致False（または0）を指定します。

VLOOKUPとは関係なのですが、日付のセルは書式を日付形式にしておきましょう。そうしないと44805とか訳の分からない(シリアル値ですが)数値になってしまいます。

さて、セルＣ１３に入力した式をコピーしましょう。
まずは横方向にコピーしてみます。
セルＣ１３をコピーし、セルD13～D13に貼り付けします。
※日付のセルがコピー元なので、日付の書式までコピー適用されないように「数式のみ貼り付け」とします。
列番号の部分を手入力で修正していきます。
商品は範囲の左から数えて5番目なので「5」、同様に数量は6番目なので「6」、単価は7番目なので「7」、金額は8番目なので「8」。