エクセルで他シートのデータを検索し表示する

商品のデータ表が商品シートに分類もなされずに一覧表になっている。検索シートをつくって、分類名をダウンリストから選べば、その分類に属する商品をダウンリストから選択でき、その価格や在庫の有無などを表示できるようにしたい。

商品シートを次のようにつくる。

図1.商品シート

図1.商品シート

検索シートは次のようにする。

図2.検索シート

図2.検索シート

商品シートでは関数式を見やすくするために、次のように範囲に名前を定義する。

図3.名前定義

図3.名前定義

範囲に名前を定義したのは赤く囲まれたA~Eに設定した。A.「分類」、B.「商品表」、C.「くだもの数」、D.「野菜数」、E.「魚数」とした。

商品表の中の「分類欄」は表記のミスを防ぐために、入力規則を設定し、リスト>元の値に「=分類」と設定する。

図4.商品表分類入力規則

図4.商品表分類入力規則

商品表は追加をするかどうか、する場合どの程度の量になるかによって商品表の名前定義の範囲を設定する必要がある。品名欄は「りんご」と「リンゴ」では違うものとして扱われるので注意が必要です。

分類で分けた表示になっていないために、分類ごとに品名を表示するための作業に移ります。

作業列1はそれぞれの品がその分類の中で何番目に記載されたかをカウントする欄です。

図5.作業列1-分類ごとのカウント

図5.作業列1-分類ごとのカウント

「くだもの」欄の赤で囲んだセルには「=IF($A6=E$5,COUNTIF($A$6:A6,E$5),””)」と入力してあります。商品表のA列の「くだもの」の表示が何個目かをカウントしています。E5~E7の項目名は分類項目をそのまま表示することに寄って、赤で囲んだE6のセルをコピーして、「E6:G20」の範囲に「形式を選択して貼り付け」>「数式」とすれば、作業列1(黄色の範囲)がすべて表示できる。品目が増えてもよい。

つづいて、作業列2は作業列1で番号付けした品目を上から順番に記述する部分です。表が小さいときは一つ一つやってもよいのですが、いくら増えるか分からないときにはこうしておくと楽です。

図6.作業列2ー分類ごとに品名を並べる

図6.作業列2ー分類ごとに品名を並べる

作業列2のくだもの欄の赤で囲んだリンゴのところには数式で「=IF(MAX(くだもの数)<ROW($A1),””,INDEX($B$6:$B$20,MATCH(ROW($A1),くだもの数,0)))」と記述しています。「くだもの数」は図3で示した通り名前を定義したものです。数式をコピーするときにこの「くだもの数」のところが分類により異なるので、とりあえず、この数式をコピーし、I6:J6に形式を選択して数式を貼り付けます。そのあと、「くだもの数」のところを野菜欄のところは「野菜数」に魚欄は「魚数」という定義した名前に変更します。そしてH6:J6をコピーして表の下まで選択し、「形式を選択して貼り付け>数式」で図6のような結果が表示できるようになります。

ここまで出来たら、検索シートの設定を行います。

図7.検索シートの分類の入力規則

図7.検索シートの分類の入力規則

商品検索シートの分類欄の入力規則を設定します。商品シートで名前を定義した「分類」を使います。入力規則の設定で入力値の種類を「リスト」とし、元の値を「=分類」と設定してOKとします。そのセルをクリックすると三角の表示が示されそれをクリックすると、

図8.分類ダウンリスト

図8.分類ダウンリスト

ダウンリストが「くだもの、野菜、魚」と表示されるので、表示させたいものを選びます。

続いて品名の欄に行くと

図9.品名入力規則

図9.品名入力規則

同様に入力規則を設定する。ここは分類欄に応じた品名の表示をする必要があるので、元の値を「=INDIRECT($A$3)」と設定します。これによりダウンリストが下のように表示できるようになります。

図10.品名ダウンリスト

図10.品名ダウンリスト

ここでは、イワシを選択してみました。さらにそのイワシのデータを表示させるために、価格と在庫の欄に次のように数式を設定します。

図11.商品データの表示

図11.商品データの表示

価格欄は「=VLOOKUP(B3,商品表,2,FALSE)」、在庫欄は「=VLOOKUP(B3,商品表,3,FALSE)」。この中でB3のデータを商品シートの「商品表」の中から検索し、その2列目、3列目を表示しなさいという数式です。「FALSE」とすることにより完全一致のデータを表示することになります。

図11.項目の多い商品データの時

図12.項目の多い商品データの時

項目の多い商品データの時は、一項目ずつ変更するのも面倒なので、「=VLOOKUP($B$10,商品表,COLUMN(C10)-COLUMN($B$10)+1,FALSE)」のようにして、列番号を「COLUMN(C10)-COLUMN($B$10)」として品名の列から何列離れているかという形にして、価格欄の数式を、横に必要なだけコピーすれば簡単に表示させることができる。

 

エクセルで分類と品名を名前定義を利用し連動させてドロップダウン表示させる

エクセルで分類欄と品名欄、それぞれの品に多くの特別のデータがあり、それを操作するシートに検索して表示させたいことが生じた。そこでここでは簡単な例をつくり覚えのために記録する。

名前の定義a

 

このような分類と品名、それぞれのデータのシートがある。枠で囲ったa~dの範囲をそれぞれ選択しメニューから(あるいは右クリックで)「範囲に名前をつける」をクリックし、a:「分類」、b:「くだもの」、c:「野菜」、d:「魚」と定義する。

検索シートの分類の表示欄に

分類入力規則

 

と名前を定義した「分類」を元の値の欄に記入し入力規則を設定する。続いて品名入力規則を

品名の入力規則

として元の値の欄に「=INDIRECT($A$2)」と入力し、A2の欄の分類名に応じて品名が表示できるようにする。こうすることによって

品名検索

 

上のように分類に応じた品名だけが表示できる。

続いて価格や在庫の様子について表示するために、商品シートに作業欄を設ける。

作業欄の設定

 

色で表示している部分が作業欄である。分類や品名は、検索シートのデータを参照する。そして価格や在庫の欄は上の俵の中からVLOOKUP関数を用いて検索し値を表示させている。列の表示は2とか3とかと入力してもよいのだが、たくさんのデータがあることを想定して

=VLOOKUP($B18,$B$6:$D$14,COLUMN()-COLUMN($B18)+1,FALSE)

として、COLUMN()-COLUMN($B18)+1 で基準列からの列番号を取得する。これによって右にデータを「コピー」>「数式の貼り付け」によって簡単に同様の作業をできるようにする。

この作業欄をつくることによって、検索シートの品名を選択したらこれらのデータ表示が楽になる。

品名検索

検索シートの価格、在庫などの欄は、商品シートの価格、在庫欄のデータを表示するように「=商品!C18」などと入力するだけで終わる。横にどれだけの欄があっても楽である。

今回は、非常に単純な商品シートを使い、分類や品名の名前定義が簡単にできた。分類が入り組み品が次々に増えていく場合には、分類名だけを集約する欄、それに応じる品名を集約する欄を作成しておく必要がある。

エクセルの複数シートでのセルの値を集計するINDIRECT(複数シートではcountifができない)

エクセルの複数シートでのセル集計にINDIRECTを利用する。
シート間をまたがってcountifができないので
集計をするシートのあいているところに集計に必要な数値を入れる
シート名:セルG1:K1
集計をするセルは各シートの同一セル。
集計セルの行列地番を列番号:F4
同         行番号:F5~
集計セルがF5からF20まで続くため行番号をF5:F20まで
=(INDIRECT(ADDRESS($F5,$F$4,,,G$1))
 
いくつかのサイトで記載されていることをURLとその表記を掲載する。
下記のサイトによる
=INDIRECT(G1&"!E5")
では、値がとれなかった。この時シート名を各シートのA1セルに入力している名前をシート名にしていた(マクロ使用)。このA1のセルに数字を入れると、
=INDIRECT(G1&"!E5")
で値を取ることができた。
 
には、集計作業をしているシートの空いている列などを作業領域にし、
INDIRECT()を使って一旦各表の該当セルへの参照を集めておけば、お望みのCOUNTIFが可能です。
例えば10枚あるシートのA3セルを集計したいとします。
ここでは作業領域をHとIにしてみます。
H1~H10にシート名を入れておき、Iには =INDIRECT(H1&"!A3")として、オートフィルでI1~I10「=INDIRECT(H10&"!A3")」までを埋めます。
これで、I列に各シートのA3の参照がならびましたので、同じシートの任意のセルで=COUNTIF(I1:I10,"○")を掛けてみてください。
 
INDIRECT関数は、引数に指定されたセルに入力されているデータを使ってセルを参照してくれる関数です。
A2セルに
「4月」と入力されているときに
「=INDIRECT(A2&"!B33")」は
「='4月'!B33」と同じ意味になります。
上記の数式でINDIRECT関数の引数のうち「A2」はセルの相対参照ですから、下方向にオートフィルすれば
「=INDIRECT(A3&"!B33")」
「=INDIRECT(A4&"!B33")」
と変化しA3・A4セルにシート名を入力しておけば、それぞれのシートのB33セルの値を表示するようになります。
 
セルA1に入力されたシート名の、セルB3を参照する場合
=INDIRECT(A1& "!B3")
また上記ではセルB3が固定になってますが、
セルA1 に参照したい【シート名】をいれ
セルA2 に参照したい【セル番地】まで入れておくと
なお、使い勝手がよくなるかも知れませんね。
=INDIRECT(A1& "!" & A2)
使用する関数:ADDRESS と INDIRECT
例:
Sheet1のA1にSheet2という文字列が入っているとします。
Sheet2のA2に「参照する値」とい文字列が入っているとします。
式:
=INDIRECT(ADDRESS(2,1,,,A1))
これで、=Sheet2!A2 と同じ結果になります。
解説:
=ADDRESS(2,1,,,A1) のみですと、Sheet2!$A$2という文字列になります。その後、INDIRECTを使うことにより、値を取得します。

エクセルの複数シートでのセル集計にINDIRECTを利用(countifができない)
シート間をまたがってcountifができないので集計をするシートのあいているところに集計に必要な数値を入れるシート名:セルG1:K1集計をするセルは各シートの同一セル。集計セルの行列地番を列番号:F4同         行番号:F5~集計セルがF5からF20まで続くため行番号をF5:F20まで(INDIRECT(ADDRESS($F5,$F$4,,,G$1))
いくつかのサイトで記載されていることをURLとその表記を掲載する。下記のサイトによる=INDIRECT(G1&"!E5")では、値がとれなかった。この時シート名を各シートのA1セルに入力している名前をシート名にしていた(マクロ使用)。このA1のセルに数字を入れると、=INDIRECT(G1&"!E5")で値を取ることができた。
http://q.hatena.ne.jp/1147068497

には、集計作業をしているシートの空いている列などを作業領域にし、INDIRECT()を使って一旦各表の該当セルへの参照を集めておけば、お望みのCOUNTIFが可能です。例えば10枚あるシートのA3セルを集計したいとします。ここでは作業領域をHとIにしてみます。H1~H10にシート名を入れておき、Iには =INDIRECT(H1&"!A3")として、オートフィルでI1~I10「=INDIRECT(H10&"!A3")」までを埋めます。これで、I列に各シートのA3の参照がならびましたので、同じシートの任意のセルで=COUNTIF(I1:I10,"○")を掛けてみてください。
http://www.relief.jp/itnote/archives/001697.php

にはINDIRECT関数は、引数に指定されたセルに入力されているデータを使ってセルを参照してくれる関数です。A2セルに「4月」と入力されているときに「=INDIRECT(A2&"!B33")」は「='4月'!B33」と同じ意味になります。上記の数式でINDIRECT関数の引数のうち「A2」はセルの相対参照ですから、下方向にオートフィルすれば「=INDIRECT(A3&"!B33")」「=INDIRECT(A4&"!B33")」と変化しA3・A4セルにシート名を入力しておけば、それぞれのシートのB33セルの値を表示するようになります。
http://oshiete.goo.ne.jp/qa/3547667.html

セルA1に入力されたシート名の、セルB3を参照する場合   =INDIRECT(A1& "!B3")また上記ではセルB3が固定になってますが、セルA1 に参照したい【シート名】をいれセルA2 に参照したい【セル番地】まで入れておくとなお、使い勝手がよくなるかも知れませんね。   =INDIRECT(A1& "!" & A2)
http://q.hatena.ne.jp/1166863251使用する関数:ADDRESS と INDIRECT
例: Sheet1のA1にSheet2という文字列が入っているとします。 Sheet2のA2に「参照する値」とい文字列が入っているとします。式: =INDIRECT(ADDRESS(2,1,,,A1)) これで、=Sheet2!A2 と同じ結果になります。解説: =ADDRESS(2,1,,,A1) のみですと、Sheet2!$A$2という文字列になります。その後、INDIRECTを使うことにより、値を取得します。