商品のデータ表が商品シートに分類もなされずに一覧表になっている。検索シートをつくって、分類名をダウンリストから選べば、その分類に属する商品をダウンリストから選択でき、その価格や在庫の有無などを表示できるようにしたい。
商品シートを次のようにつくる。
図1．商品シート
検索シートは次のようにする。
図2．検索シート
商品シートでは関数式を見やすくするために、次のように範囲に名前を定義する。
図3．名前定義
範囲に名前を定義したのは赤く囲まれたA~Eに設定した。A.｢分類｣、B.｢商品表｣、C.「くだもの数」、D.「野菜数」、E.「魚数」とした。
商品表の中の「分類欄」は表記のミスを防ぐために、入力規則を設定し、リスト＞元の値に「=分類」と設定する。
図4．商品表分類入力規則
商品表は追加をするかどうか、する場合どの程度の量になるかによって商品表の名前定義の範囲を設定する必要がある。品名欄は「りんご」と「リンゴ」では違うものとして扱われるので注意が必要です。
分類で分けた表示になっていないために、分類ごとに品名を表示するための作業に移ります。
作業列１はそれぞれの品がその分類の中で何番目に記載されたかをカウントする欄です。
図5．作業列１－分類ごとのカウント
「くだもの」欄の赤で囲んだセルには「=IF($A6=E$5,COUNTIF($A$6:A6,E$5),””)」と入力してあります。商品表のA列の「くだもの」の表示が何個目かをカウントしています。E5～E7の項目名は分類項目をそのまま表示することに寄って、赤で囲んだE6のセルをコピーして、「E6：G20」の範囲に「形式を選択して貼り付け」＞「数式」とすれば、作業列1（黄色の範囲）がすべて表示できる。品目が増えてもよい。
つづいて、作業列2は作業列1で番号付けした品目を上から順番に記述する部分です。表が小さいときは一つ一つやってもよいのですが、いくら増えるか分からないときにはこうしておくと楽です。
図6．作業列2ー分類ごとに品名を並べる
作業列2のくだもの欄の赤で囲んだリンゴのところには数式で「=IF(MAX(くだもの数)<ROW($A1),””,INDEX($B$6:$B$20,MATCH(ROW($A1),くだもの数,0)))」と記述しています。「くだもの数」は図3で示した通り名前を定義したものです。数式をコピーするときにこの「くだもの数」のところが分類により異なるので、とりあえず、この数式をコピーし、I6:J6に形式を選択して数式を貼り付けます。そのあと、「くだもの数」のところを野菜欄のところは「野菜数」に魚欄は「魚数」という定義した名前に変更します。そしてH6:J6をコピーして表の下まで選択し、「形式を選択して貼り付け＞数式」で図6のような結果が表示できるようになります。
ここまで出来たら、検索シートの設定を行います。
図7．検索シートの分類の入力規則
商品検索シートの分類欄の入力規則を設定します。商品シートで名前を定義した「分類」を使います。入力規則の設定で入力値の種類を「リスト」とし、元の値を「=分類」と設定してOKとします。そのセルをクリックすると三角の表示が示されそれをクリックすると、
図8．分類ダウンリスト
ダウンリストが「くだもの、野菜、魚」と表示されるので、表示させたいものを選びます。
続いて品名の欄に行くと
図9．品名入力規則
同様に入力規則を設定する。ここは分類欄に応じた品名の表示をする必要があるので、元の値を「=INDIRECT($A$3)」と設定します。これによりダウンリストが下のように表示できるようになります。
図10．品名ダウンリスト
ここでは、イワシを選択してみました。さらにそのイワシのデータを表示させるために、価格と在庫の欄に次のように数式を設定します。
図11．商品データの表示
価格欄は「=VLOOKUP(B3,商品表,2,FALSE)」、在庫欄は「=VLOOKUP(B3,商品表,3,FALSE)」。この中でB３のデータを商品シートの「商品表」の中から検索し、その2列目、3列目を表示しなさいという数式です。「FALSE」とすることにより完全一致のデータを表示することになります。
図12．項目の多い商品データの時
項目の多い商品データの時は、一項目ずつ変更するのも面倒なので、「=VLOOKUP($B$10,商品表,COLUMN(C10)-COLUMN($B$10)+1,FALSE)」のようにして、列番号を「COLUMN(C10)-COLUMN($B$10)」として品名の列から何列離れているかという形にして、価格欄の数式を、横に必要なだけコピーすれば簡単に表示させることができる。