エクセル データベースと検索

質問

エクセル初心者です。

今データベースの検索の勉強のため
以下のような2枚のシートを作成しています。

上段がデータベースの元データで在庫の商品と
支店名です。
商品の種類は全部で1000個位の種類があります。

下段が商品を備蓄している支店名と支店コードです。

そこで今回したい処理なのですが、

下段の支店コードの横の列に以下のように
検索フォームをつくり、支店名（さらに言えば支店コードだけ）
を入力すれば、その支店で持っている商品名を
すべて表示したいのです。

現在、目視でしているためたいへん時間がかかります。

関数やＶＢＡで抽出することはできるのでしょうか。

よろしくお願いします。

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.4

tom04 · ベストアンサー

No.2です！
補足を読ませていただきました。

ん～～～～
データとしてはかなりおおまか過ぎますね！
変更はできないかもしれませんが・・・今後の課題として
たとえばですが、
同じ地域に複数の支店がある場合はコードの桁数を増やすなりして対処する方法をお勧めします。
仮に、701を大阪市此花区　702を大阪市此花区　703を大阪市城東区・・・
といった形にしてコードは重複なしにするのが普通だと思います。

ただ、これでは補足の回答にならないので無理やりの方法になりますが
前回の表はそのまま利用させていただき、Sheet2のF1セルに
=IF(COUNTIF($A$1:$A$1000,$E$1)<ROW(A1),"",INDEX($B$1:$B$1000,SMALL(IF($A$1:$A$1000=$E$1,ROW($A$1:$A$1000)),ROW(A1))))
これは配列数式になりますので、この画面からF1セルにコピー＆ペーストした後に
数式バー内で一度クリック　→　編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入ります。これをオートフィルで下へコピーしてみてください。
これで同じコードが複数あっても表示されると思います。

尚、G・H列は前回そのままで手を加えていませんので、
各支店ごとに表示しようとすれば根本的に表の配置を考え直した方が良いと思います。

この程度ですが、ごめんなさいね。m(__)m

回答No.3

hallo-2007

>今データベースの検索の勉強のため
>以下のような2枚のシートを作成しています。
勉強のためといえば、今回もデータベースのシートの構成は考えた直したほうが良いです。
ひとつの商品に対して在庫を持っている支店が３つ入力されていますが
それ以上多数の支店が在庫を持っている場合はどうするのでしょうか？
各支店の在庫をどうやってデータベースのシートに入力したら作業が簡単なのか
という観点からも検討が必要です。
例えば、入庫・出庫の記録からデータベース化します。
データベースシート
日付　支店コード　支店名　商品番号　商品名　入出庫数
12/1 1　　　　　世田谷　　1　　　りんご　　-100
・・・・
とひたすら縦方向へデータを入れることをお勧めします。
もちろん　フォームを作成して　支店コードと商品番号と数を入れると　支店名と商品名が入る
機能を考えます。
ピボットテーブルなど活用すれば
別のシートに商品別でも支店別でも在庫の一覧表を作成することは簡単です。
将来、アクセスとかデータベースソフトを活用するようになった場合も理解が早いと思います。

回答No.2

tom04

こんばんは！
一例です。
↓の画像のように元データSheet（Sheet1にしています）に作業用の列を設けています。
Sheet2のセル配置は適宜変更してみてください。

Sheet1の作業列J2セルに
=IF(OR(Sheet2!$E$1="",COUNTIF(D2:I2,Sheet2!$E$1)=0),"",ROW())
という数式を入れオートフィルで下へずぃ～～～！っとコピーしておきます。

Sheet2のF1セルには
=IF(E1="","",VLOOKUP(E1,A:B,2,0))
という数式を入れています。

そしてSheet2のG2セルに
=IF(COUNT(Sheet1!$J:$J)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$J:$J,ROW(A1))))
という数式を入れ、隣のH2セルまでオートフィルでコピー！
そのまま下へコピーすると画像のような感じになります。

以上、参考になれば良いのですが・・・m(__)m

補足

ありがとうございました。大変よくわかりました。

あと、1件補足なのですが

データベースの先を見てみると

支店コードが

7　大阪市此花区
7　大阪市城東区
7　大阪市港区

のように違う地域なのに、同じ支店番号が振っているものが出てきます。
（おおまかなデータベースなので…）

このような場合、ＶＬＯＯＰ関数だと、最初の大阪市しか
検索できません。（もともとそのような関数ですが…）

支店番号が同じものは、すべて抽出する
というふうにしたいのですが。

シート1も2も右の列は、いくつ使ってもかまいませんので
できる方法はあるのでしょうか。

すいませんが、よろしくお願いします。

回答No.1

keithin

シート名とか実際にデータのある/入れるセル範囲とかはテキトーですから，実際のシートに合わせて正しく直してから試してください。
マクロの中で検索すべき支店名の取り込み部分は，ご自分で修正してみてください。
シート上で支店名から支店コードを表示する関数（若しくはその逆）は，今回ご質問の核心とは無関係な簡単な検索の関数式なので，ちょっとご自分で勉強してみてください。

sub sample1()
　dim c as range
　dim firstAddress as string
　dim n as long

　'書き出し先のクリア，初期化
　worksheets("Results").range("G5:H999").clearcontents
　n = 5

　'検索の開始
　with worksheets("DATA").range("D:I")
　　set c = .find(what:="大阪市", lookin:=xlvalues, lookat:=xlwhole)
　　if not c is nothing then
　　　firstaddress = c.address

　　　do
　　　　’転記
　　　　worksheets("Results").cells(n, "G").resize(1, 2).value =　worksheets("DATA").cells(c.row, "A").resize(1, 2).value
　　　　’次を検索
　　　　n = n + 1
　　　　set c = .findnext(after:=c)
　　　loop while c.address <> firstaddress

　　end if
　end with
end sub

関数でやりたい初心者さんのご相談は沢山寄せられますが，検索結果をリストで出してくれる関数はありませんので，ここでそういうことが出来る式を教わっても（過去ログを紐解くと沢山ヒットしますが）全く勉強になりません。
マクロでセルの検索(FindとFindNext）命令を調べ，VBEのヘルプに記載のサンプルマクロを参考に，指定の支店名を順繰り検索して該当する製品を次々転記するマクロを作成してみると良いでしょう。

また他の方からも寄せられるかもしれませんが，「勉強のため」であれば，そもそも「データベース」の作り方から"間違っています"ので最初からやり直した方が良いでしょう。