練習問題1/複数の表をまとめる

【練習問題1 問題】

下の表は「2月度書籍売上表」と「商品コード別書籍名一覧」です。
2月度書籍売上表には日付毎に販売した書籍の商品コードと売上冊数が記載済みです。
1.「商品コード別書籍名一覧」を参考に、商品コードから〝書籍名〟と〝単価〟を自動的に求める関数を黄色セルに入力して下さい。
2.売上金額を求める数式を青セルに入力してください。

▶練習問題のExcelデータをダウンロード

sponsored link
Hints!

使用する関数が分からない、もしくは関数を知らない場合でも、自分が使いたい関数を調べることが出来れば、利用上問題ありません。ですので、分からない時に関数を調査する検索キーワードを学ぶことはとても重要です。本サイトに加え、Google、Yahoo!などを利用し検索してみましょう。この問題の場合、たとえば以下のようなキーワードを利用して検索すると目的の関数を見つけやすいので、検索キーワードがわからない場合は参考にして、検索をしてみてください。

【検索キーワード】
表をまとめる関数がわからない場合は・・・Excel 表 結合 関数
本サイトの検索ボックス
※上記以外にも検索できるキーワードがあります。

【練習問題1 解答】

解答は以下の通りです。

各セルには以下のような関数や数式を入れます。

1.「書籍名」列/1行目の例

【セル】
D4
:
:
【関数式】
=VLOOKUP(C4,$I$4:$K$13,2,FALSE)

【値】
よくわかる英検(準2級)

2.「単価」列/1行目の例

【セル】
G4
:
:
【関数式】
=E4*F4

【値】
1,100

【練習問題1 解説】

複数の表をまとめる流れは以下のようになります。

該当のセルに当てはまる値を、他の表のデータから検索して表示させるには「VLOOKUP関数」を利用します。

=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式は先述したように以下の通りです。

=VLOOKUP(検索値, 範囲, 列番号, [検索方法])

「2月度書籍売上表」の1レコード目について、上記に当てはめてみると、“検索値”には2つの表の紐づけのキーとなる〝商品コード〟「C4」が入ります。次に、“範囲”は、結合元の表である「書籍コード別書籍一覧」のデータ範囲である「$I$4:$K$13」が入ります。(各列行の前に「$」をつける理由が不明な場合、【練習問題1 まとめ】を確認してください。)この時、紐づけのキーと対応する〝商品コード〟が、結合元の“範囲”の一番左側の列にあるように指定します。そして、列番号については、表示したい〝書籍名〟〝単価〟が、結合元の“範囲”の左から何列目のデータかを記入します。〝書籍名〟は「2」列目、〝単価〟は「3」列目です。最後に、「検索方法」は、キーとなる〝商品コード〟が完全に一致するデータを紐づける必要があるので、「FALSE」となります。

【練習問題1 まとめ】

セルのひとつひとつにいちいちVLOOKUP関数を作っていくのは効率的とはいえません。
D4やE4のセルに関数を作成してから、同じものを下の行にもコピーして使う方が効率が良くなります。
そのため、範囲を「絶対参照」にしています。

もし絶対参照の指定をしていなかったらどうなるのか見てみましょう。
該当セル(ここではC4)の右下にカーソルをあてると十字形のカーソルに変わります(オートフィル機能)。
これを左クリックしたまま下方向にドラッグするとコピーができるのですが、下記のように、コピーしたセル内の表示がうまくいかないことがあります。

ここで気をつけなければならないのは「相対参照」になっていることです。
そんなときにはまず、関数の内容をひとつひとつ確認してみましょう。
例えば、D4のすぐ下のD5のセル内は、「=VLOOKUP(C5,I5:K14,2,FALSE)」になっています。検索する範囲に注目してみると、I5からK14になっていますが、本来の範囲はI4からK13なので、セル範囲が相対参照コピーでずれていることがわかりますね。

このようなことを防ぐためにも、検索範囲の値を絶対参照にしましょう。

今回使用した関数は、以下の記事に詳しい使い方の説明が掲載されています。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。
データをカウントする
複数の表を結合する/VLOOKUP関数の使い方について解説します。

練習問題2/別シートの表を連結・結合する

【練習問題2 問題】

とあるフラワーショップの4月の売上表です。
別のシートにある「商品リスト」を参照して、商品名と単価を表示する関数を黄色セルに入力してください。

フラワーショップ4月売上表

商品リスト

▶練習問題のExcelデータをダウンロード

Hints!

使用する関数が分からない、もしくは関数を知らない場合でも、自分が使いたい関数を調べることが出来れば、利用上問題ありません。ですので、分からない時に関数を調査する検索キーワードを学ぶことはとても重要です。本サイトに加え、Google、Yahoo!などを利用し検索してみましょう。この問題の場合、たとえば以下のようなキーワードを利用して検索すると目的の関数を見つけやすいので、検索キーワードがわからない場合は参考にして、検索をしてみてください。

【検索キーワード】
別シートの表から検索して表示する関数がわからない場合は・・・Excel 別シート 検索 結合 関数
本サイトの検索ボックス
※上記以外にも検索できるキーワードがあります。

【練習問題2 解答】

解答は以下の通りです。

関数は、以下のようになります。(1行目)

【商品名セル】
D4
:
:
【関数式】
=VLOOKUP(C4,商品リスト!$B$4:$D$20,2,FALSE)

【値】
ひまわり
【単価セル】
E4
:
:
【関数式】
=VLOOKUP(C4,商品リスト!$B$4:$D$20,3,FALSE)

【値】
250

▶解答のExcelデータをダウンロード

【練習問題2 解説】

別シートにある表と結合するには、

VLOOKUP関数
=VLOOKUP(検索値, 範囲, 列番号, [検索方法])

2番目の〝範囲〟の部分に
シート名+!(エクスクラメーション)+その範囲を指定することで別シートにある表をまとめることができます。
今回の結合するシートは「商品リスト」、範囲はB4:D20なので、
商品リスト!$B$4:$D$20をVLOOKUP関数の〝範囲〟に指定します。

【練習問題2 まとめ】

結合する予定のあるシートには、ミスを防ぐ意味でもわかりやすく短い名前を付けると良いでしょう。

今回使用した関数は、以下の記事に詳しい使い方の説明が掲載されています。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。

データをカウントする
結合前の2つの表が別々のシート内にある場合の結合方法について解説します。

練習問題3/近似値の一致/部分一致の検索

【練習問題3 問題】

下記の表は顧客名簿です。
実際の年齢を参考に、年代でまとめたいので「年代別一覧表」と結合して「年代」列のセルにそれぞれ表示されるよう関数を入力してください。

▶練習問題のExcelデータをダウンロード

Hints!

使用する関数が分からない、もしくは関数を知らない場合でも、自分が使いたい関数を調べることが出来れば、利用上問題ありません。ですので、分からない時に関数を調査する検索キーワードを学ぶことはとても重要です。本サイトに加え、Google、Yahoo!などを利用し検索してみましょう。この問題の場合、たとえば以下のようなキーワードを利用して検索すると目的の関数を見つけやすいので、検索キーワードがわからない場合は参考にして、検索をしてみてください。

【検索キーワード】
データの個数をカウントする関数がわからない場合は・・・Excel 表の結合 部分一致 関数
本サイトの検索ボックス
※上記以外にも検索できるキーワードがあります。

【練習問題3 解答】

解答は、以下の通りになります。

セルに入る関数は以下の通りです。(1行目)

【年代セル】
E4
:
:
【関数式】
=VLOOKUP(C4,$G$4:$H$9,2,TRUE)

【値】
50代

▶解答のExcelデータをダウンロード

【練習問題3 解説】

VLOOKUP関数
=VLOOKUP(検索値, 範囲, 列番号, [検索方法])
この中にある[検索方法]には2通りあり、完全一致は「FALSE」、部分一致は「TRUE」と入力します。(省略も可能ですが、その場合はTRUEとなります。)

部分一致(TRUE)について
数値で[検索値]が指定されている場合に限り、[検索値]以下の数字でかつ、最も近い数値(=近似値)を[範囲]内から検索できるようになります。

今回の問題では、「年齢」が検索値で、「年代別一覧表」の「年齢列」の数値から近似値を検索しています。

年齢が58歳の遠藤さんは、〝年代〟の列に「50代」が表示される必要があります。しかし、表結合しようにも、キーとなる〝年齢〟は、2表で一致する数字がありません。こういう時に利用するのが先述されていたVLOOKUPの[検索方法]で指定する「部分一致」です。

=VLOOKUP(検索値, 範囲, 列番号, [検索方法])

上記の公式の「[検索方法]」は、練習問題1、2で利用した完全一致「FALSE」だと一致するデータがなく「#N/A」となってしまいますが、部分一致「TURE」を利用すると、「58」歳以下で最も近い数値である〝年齢〟データを「年代別一覧表」から検索することが出来ます。本問題ですと、「年代別一覧表」から「50」のデータを得ることができます。ですので、解答式は、検索値である〝年齢〟キーの「C4」、「年代別一覧表」の範囲「$G$4:$H$9」、表示したい〝年代〟は範囲中の「2」列目、[検索方法]は部分一致の「TRUE」となります。

=VLOOKUP(C4,$G$4:$H$9,2,TRUE)

【練習問題3 まとめ】

VLOOKUP関数で、検索先の範囲内から検索値と〝全く同じ〟データを探すなら「FALSE」、数値に限り近い値を探すなら「TRUE」と覚えておくと良いでしょう。

今回使用した関数は、以下の記事に詳しい使い方の説明が掲載されています。
今回の問題が難しいと感じた方は、ぜひ確認してみてください。

検索値の数値に近い数字を検索する/VLOOKUP関数について解説します。