Excelでデータベース的な使い方

質問

今、ある商店の帳簿をDB化しようとしています。
そこで、少し煩雑なので教えてください。

まず、Book1で、商品コードと商品名の一覧表を作りました。
で、Book2で商品コードを入れた時に、自動的に商品名の欄にBook1で対応している商品名が入るようにするためにはどうしたら（どのような関数を使ったら？）良いでしょうか。
また、Book2に、Book1にはなかった商品コードと商品名を入れた時、Book1の方で「それは自分は持っていない新しい情報である」と認識して自動的にBook1にも新しい項目（＝商品コードと商品名）が増えるようにするにはどうしたらいいでしょうか。

Excelのヘルプを見ても該当するようなものが探せなくて困っています。
お手数ですが、力を貸していただけないでしょうか。
よろしくお願いいたします。

回答No.2

popesyu · ベストアンサー

とりあえず質問１「商品コードから商品名を判別する」
は１番さんの回答通りLOOKUP関数の仕事です。
質問２「商品コード・商品名を追加する」は
マクロというよりは、完全にVBAの作業になりますね。
自分でコードを書かなければなりませんが
そんな複雑な操作は必要ないので
マクロの積み重ねで何とかなるでしょう。
##一からVBAをやっていくには格好のレベルの題材だと
##思いますので、もし時間的に余裕があるのであれば
##勉強されてみては如何でしょうか。
あるいは根本的に設計を変更、
つまりは追加する部分は完全に手作業でする前提で
作るとか。

一応注意点としては、LOOKUP関数は対象をソート
（数字順に並べなおし）しておく必要がありますので、
そこだけクリアすれば、そんなに知識がない人でも
データベースの操作は可能だと思います。

お礼

詳細なご説明、ありがとうございます。
私は、Excelは簡単な関数やグラフ、表なんかは一通りできるものの、マクロはほとんどさっぱりなので、ちょうど勉強になると思います。
勉強してできるレベルであるということが分かっていれば、やる気にもなります(^_^)
「ある商店」とは酒屋さんなので、毎月のように出るビールやチューハイの新製品のコード管理にそういうことができたらいいとリクエストされたのでした。
ひとまず、LOOKUP関数とVBA、やってみます。
ありがとうございました。

回答No.3

nishi6

＞Book2で商品コードを入れた時に、自動的に・・・・・・・・
　VlookUpでできますが、事前に算式を登録したり、入力後、算式をコピーする必要があります。
　また、多数入力すると操作性（速度）が悪くなってくるでしょう。新しいコードを入力する時には算式を壊すことにもなります。次の質問と相反しますね。

＞Book2に、Book1にはなかった商品コードと商品名を入れた時・・・・
　これを実現するためには、最初の質問との関係でVlookUpは使えないように思います。使った場合、使い手がかなり操作（Excel）に慣れていないと無理でしょう。

参考にVBAだけでコードを書いてみました。商品コードと商品名の重複に制限をかけています。VlookUpを使っていないので、数が増えても問題はないと思っています。

ExcelでDB化というのも少し大変かと思いますが・・・　ご参考に。

Book1はA、B列に商品コードと商品名を入力。Book2はD、E列に商品コードと商品名を入力する前提です。
実際のシートに合わせて変更して下さい。

ツーツ→マクロ→Visual Basic Editor でVBE画面に移り、Book2のシート1のコードウインドウに貼り付けます。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
　Dim wb As Workbook 'コードがあるBook
　Dim ws As Worksheet 'コードがあるSheet
　Dim cl As Integer 'コードがある列
　Dim rw As Long '新しいコードを追加する行
　　Set wb = Workbooks("Book1.xls")
　　Set ws = wb.Worksheets("Sheet1")
　　cl = Asc("A") - 64 '*** コードのある列をセット(今はA列) ***

　Dim rg As Range '変更したセル範囲の単一セル
　Dim schCode As Range '検索する商品コード
　Dim schName As Range '検索した商品コードの商品名
　Dim inp_cl As Integer 'コードを入力する列
　　inp_cl = Asc("D") - 64 '*** 入力するコードの列をセット(今はD列) ***

　Application.EnableEvents = False
　For Each rg In Target '複数セルの変更に対応する
　　Select Case rg.Column
　　　Case inp_cl 'コードを入力

　　　　'コードを検索
　　　　Set schCode = ws.Columns(cl).Find(What:=rg.Value, LookIn:=xlValue, LookAt:=xlWhole)
　　　　If Not schCode Is Nothing Then
　　　　　'見つかった！
　　　　　rg.Offset(0, 1) = schCode.Offset(0, 1).Value '商品名を入力コードの横に表示
　　　　Else
　　　　　'見つからないので商品コード表に追加
　　　　　If rg.Offset(0, 1) <> "" Then
　　　　　　rw = ws.Range(Chr(cl + 64) & "65536").End(xlUp).Row + 1
　　　　　　ws.Cells(rw, cl) = rg.Value
　　　　　　ws.Cells(rw, cl + 1) = rg.Offset(0, 1).Value
　　　　　End If
　　　　End If

　　　Case inp_cl + 1 '商品名を入力
　　　　If rg.Offset(0, -1) <> "" Then
　　　　　Set schCode = ws.Columns(cl).Find(What:=rg.Offset(0, -1).Value, LookIn:=xlValue, LookAt:=xlWhole)
　　　　　Set schName = ws.Columns(cl + 1).Find(What:=rg.Value, LookIn:=xlValue, LookAt:=xlWhole)

　　　　　If schCode Is Nothing Then
　　　　　　If schName Is Nothing Then
　　　　　　　'コードが無く、商品名も見つからないのでコード表に追加
　　　　　　　rw = ws.Range(Chr(cl + 64) & "65536").End(xlUp).Row + 1
　　　　　　　ws.Cells(rw, cl) = rg.Offset(0, -1).Value '商品コード
　　　　　　　ws.Cells(rw, cl + 1) = rg.Value '商品名
　　　　　　Else
　　　　　　　'既に商品名がある場合
　　　　　　　MsgBox "商品名が重複しています。", vbOKOnly, "商品名重複"
　　　　　　End If
　　　　　Else
　　　　　　'既に商品コードがある場合
　　　　　　MsgBox "入力コードが重複しています。", vbOKOnly, "データ重複"
　　　　　End If
　　　　End If

　　End Select
　Next
　Application.EnableEvents = True
End Sub

お礼

すごい！すっかり作ってくださってありがとうございます。
とはいえ、このまま丸写しでは自分の勉強にもならないし、おかしくなったときに直せないので、
ご提示頂いたものを手がかりに勉強してみたいと思います。
ありがとうございました。

回答No.1

peso

＞Book2で商品コードを入れた時に、自動的に・・・
これは　VLOOKUP　関数を使う事で実現できます。
この関数は・・・ちょっとここで説明するのが難しいので、ヘルプ等や
関数ウィザードを参照してください。

＞Book2に、Book1にはなかった商品コードと・・・
これは関数などでは出来ないでしょう。
マクロを使えば一応は可能だと思います。

お礼

どうもありがとうございます！
Excelの関数は数が多いので、調べきれずにいました。
どの関数で実現できるかわかっただけでも、これからの作業が進みます。
関数の使い方はヘルプを見て勉強してみます。
すでにコードと商品名の一覧表は作り始めているので、これができないと八方塞になってしまうところだったので....
本当にありがとうございました。