Contents
練習問題１／複数の表をまとめる
【練習問題１ 問題】
下の表は「2月度書籍売上表」と「商品コード別書籍名一覧」です。
2月度書籍売上表には日付毎に販売した書籍の商品コードと売上冊数が記載済みです。
１．「商品コード別書籍名一覧」を参考に、商品コードから〝書籍名〟と〝単価〟を自動的に求める関数を黄色セルに入力して下さい。
２．売上金額を求める数式を青セルに入力してください。
▶練習問題のExcelデータをダウンロード
使用する関数が分からない、もしくは関数を知らない場合でも、自分が使いたい関数を調べることが出来れば、利用上問題ありません。ですので、分からない時に関数を調査する検索キーワードを学ぶことはとても重要です。本サイトに加え、Google、Yahoo！などを利用し検索してみましょう。この問題の場合、たとえば以下のようなキーワードを利用して検索すると目的の関数を見つけやすいので、検索キーワードがわからない場合は参考にして、検索をしてみてください。
【練習問題１ 解答】
解答は以下の通りです。
各セルには以下のような関数や数式を入れます。
1．「書籍名」列／１行目の例
2．「単価」列／１行目の例
▶解答のExcelデータをダウンロード
【練習問題１ 解説】
複数の表をまとめる流れは以下のようになります。
該当のセルに当てはまる値を、他の表のデータから検索して表示させるには「VLOOKUP関数」を利用します。
[検索値] = 検索する文字、又は数値を指定します。
[範囲] = 検索する表の範囲を指定します。
[列番号] = [範囲]の内、実際に表示させたい情報が何列目（左端から1列目として）に有るかを指定します。
[検索方法] = 省略可。[検査値]に一致するデータ（完全一致）を探す場合には「FALSE」、検索値に近いデータ（部分一致）を探す場合には「TRUE」を指定します。省略すると「TRUE」となります。
下の図のように、例えばセルD4に入る関数は=VLOOKUP(C4,$I$4:$K$13,2,FALSE）となりますが、
隣のセルの「単価」セルE4には列番号3を指定した=VLOOKUP(C4,$I$4:$K$13,3,FALSE）が入ります。
今回の場合、「2月度書籍売上表」に「書籍コード別書籍一覧」のデータを結合する必要があります。結合のキーとなるのは〝商品コード〟で、「書籍コード別書籍一覧」から〝商品コード〟が同一のデータの〝書籍名〟〝単価〟を表示します。データを紐づけるVLOOKUP式は先述したように以下の通りです。
「2月度書籍売上表」の１レコード目について、上記に当てはめてみると、“検索値”には２つの表の紐づけのキーとなる〝商品コード〟「C4」が入ります。次に、“範囲”は、結合元の表である「書籍コード別書籍一覧」のデータ範囲である「$I$4:$K$13」が入ります。（各列行の前に「$」をつける理由が不明な場合、【練習問題１ まとめ】を確認してください。）この時、紐づけのキーと対応する〝商品コード〟が、結合元の“範囲”の一番左側の列にあるように指定します。そして、列番号については、表示したい〝書籍名〟〝単価〟が、結合元の“範囲”の左から何列目のデータかを記入します。〝書籍名〟は「2」列目、〝単価〟は「3」列目です。最後に、「検索方法」は、キーとなる〝商品コード〟が完全に一致するデータを紐づける必要があるので、「FALSE」となります。
【練習問題１ まとめ】
セルのひとつひとつにいちいちVLOOKUP関数を作っていくのは効率的とはいえません。
D4やE4のセルに関数を作成してから、同じものを下の行にもコピーして使う方が効率が良くなります。
そのため、範囲を「絶対参照」にしています。
もし絶対参照の指定をしていなかったらどうなるのか見てみましょう。
該当セル（ここではC4）の右下にカーソルをあてると十字形のカーソルに変わります（オートフィル機能）。
これを左クリックしたまま下方向にドラッグするとコピーができるのですが、下記のように、コピーしたセル内の表示がうまくいかないことがあります。
ここで気をつけなければならないのは「相対参照」になっていることです。
そんなときにはまず、関数の内容をひとつひとつ確認してみましょう。
例えば、D4のすぐ下のD5のセル内は、「=VLOOKUP(C5,I5:K14,2,FALSE)」になっています。検索する範囲に注目してみると、I5からK14になっていますが、本来の範囲はI4からK13なので、セル範囲が相対参照コピーでずれていることがわかりますね。
このようなことを防ぐためにも、検索範囲の値を絶対参照にしましょう。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。
練習問題２／別シートの表を連結・結合する
【練習問題２ 問題】
とあるフラワーショップの4月の売上表です。
別のシートにある「商品リスト」を参照して、商品名と単価を表示する関数を黄色セルに入力してください。
フラワーショップ4月売上表
商品リスト
▶練習問題のExcelデータをダウンロード
使用する関数が分からない、もしくは関数を知らない場合でも、自分が使いたい関数を調べることが出来れば、利用上問題ありません。ですので、分からない時に関数を調査する検索キーワードを学ぶことはとても重要です。本サイトに加え、Google、Yahoo！などを利用し検索してみましょう。この問題の場合、たとえば以下のようなキーワードを利用して検索すると目的の関数を見つけやすいので、検索キーワードがわからない場合は参考にして、検索をしてみてください。
【練習問題２ 解答】
解答は以下の通りです。
関数は、以下のようになります。（１行目）
▶解答のExcelデータをダウンロード
【練習問題２ 解説】
別シートにある表と結合するには、
VLOOKUP関数
=VLOOKUP(検索値, 範囲, 列番号, [検索方法])
2番目の〝範囲〟の部分に
シート名＋!（エクスクラメーション）＋その範囲を指定することで別シートにある表をまとめることができます。
今回の結合するシートは「商品リスト」、範囲はB4:D20なので、
商品リスト!$B$4:$D$20をVLOOKUP関数の〝範囲〟に指定します。
【練習問題２ まとめ】
結合する予定のあるシートには、ミスを防ぐ意味でもわかりやすく短い名前を付けると良いでしょう。
今回使用した関数は、以下の記事に詳しい使い方の説明が掲載されています。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。
練習問題３／近似値の一致／部分一致の検索
【練習問題３ 問題】
下記の表は顧客名簿です。
実際の年齢を参考に、年代でまとめたいので「年代別一覧表」と結合して「年代」列のセルにそれぞれ表示されるよう関数を入力してください。
▶練習問題のExcelデータをダウンロード
使用する関数が分からない、もしくは関数を知らない場合でも、自分が使いたい関数を調べることが出来れば、利用上問題ありません。ですので、分からない時に関数を調査する検索キーワードを学ぶことはとても重要です。本サイトに加え、Google、Yahoo！などを利用し検索してみましょう。この問題の場合、たとえば以下のようなキーワードを利用して検索すると目的の関数を見つけやすいので、検索キーワードがわからない場合は参考にして、検索をしてみてください。
【練習問題３ 解答】
解答は、以下の通りになります。
セルに入る関数は以下の通りです。（１行目）
▶解答のExcelデータをダウンロード
【練習問題３ 解説】
VLOOKUP関数
=VLOOKUP(検索値, 範囲, 列番号, [検索方法])
この中にある[検索方法]には2通りあり、完全一致は「FALSE」、部分一致は「TRUE」と入力します。（省略も可能ですが、その場合はTRUEとなります。）
数値で[検索値]が指定されている場合に限り、[検索値]以下の数字でかつ、最も近い数値（=近似値）を[範囲]内から検索できるようになります。
今回の問題では、「年齢」が検索値で、「年代別一覧表」の「年齢列」の数値から近似値を検索しています。
年齢が58歳の遠藤さんは、〝年代〟の列に「50代」が表示される必要があります。しかし、表結合しようにも、キーとなる〝年齢〟は、２表で一致する数字がありません。こういう時に利用するのが先述されていたVLOOKUPの[検索方法]で指定する「部分一致」です。
上記の公式の「[検索方法]」は、練習問題１、２で利用した完全一致「FALSE」だと一致するデータがなく「#N/A」となってしまいますが、部分一致「TURE」を利用すると、「58」歳以下で最も近い数値である〝年齢〟データを「年代別一覧表」から検索することが出来ます。本問題ですと、「年代別一覧表」から「50」のデータを得ることができます。ですので、解答式は、検索値である〝年齢〟キーの「C4」、「年代別一覧表」の範囲「$G$4:$H$9」、表示したい〝年代〟は範囲中の「2」列目、[検索方法]は部分一致の「TRUE」となります。
=VLOOKUP(C4,$G$4:$H$9,2,TRUE)
【練習問題３ まとめ】
VLOOKUP関数で、検索先の範囲内から検索値と〝全く同じ〟データを探すなら「FALSE」、数値に限り近い値を探すなら「TRUE」と覚えておくと良いでしょう。
今回使用した関数は、以下の記事に詳しい使い方の説明が掲載されています。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。
