エクセルでシートの文字データを別ブックに反映・・・

質問

エクセルを使って顧客データを作っています。

伝票データ（BOOK1）に
・婚約指輪のお客様データ(SHEET1)
・結婚指輪のお客様データ(SHEET2)

があり、それぞれこんな感じ↓のデータが並んでいます。

　店舗　　伝票NO　　お客様名　　売上合計　　品番・・・
-------------------------------------------------
　Ａ店　　　1-2-2　　 東京一郎　　￥120000　　ＳＤＤ223
　Ｂ店　　　3-5-5　　 神戸次郎　　￥953330　　ＬＰ002-30

このデータを、顧客データ（ＢＯＯＫ２）として自動的に反映される様にしたいです。

(1)反映したい列を選びたい。
(2)同じ名前の項目（例：店舗、お客様名など）はまとめたい。
婚約指輪のお客様（Book1sheet1)と結婚指輪のお客様(Book1sheet2)、両方の名前の名前をＢｏｏｋ２の「お客様名」列に反映させたい。

上記の作業を出来るだけ簡単に組みたいです。
エクセルは初級者なのでマクロなど難しい事は分かりません。。。

でも、今日中になんとかしたいです！
よろしくお願いします!!!

回答No.5

某HN クロメート(Chromate)（@CoalTar）

マクロを試していただきありがとうございます
使えないということが分かったのは良かったです。
以下、操作の詳細、新規ブックにて。

データ - 外部データの取り込み - 新しいデータベースクエリ
Excel Files* [OK]
ドライブ D:
BOOK1.xls を選んで[OK]
Sheet1$ の 店舗 [>]
Sheet1$ の お客様名 [>]
[次へ][次へ][次へ]
●Microsoft Queryでデータの表示またはクエリの編集を行う
[完了]

[SQL]コマンドボタン
下記を
SELECT `Sheet1$`.店舗, `Sheet1$`.お客様名
FROM `D:\BOOK1`.`Sheet1$` `Sheet1$`

下記にする
SELECT `Sheet1$`.店舗, `Sheet1$`.お客様名
FROM `D:\BOOK1`.`Sheet1$` `Sheet1$`
union
SELECT `Sheet2$`.店舗, `Sheet2$`.お客様名
FROM `D:\BOOK1`.`Sheet2$` `Sheet2$`

[OK] すると注意メッセージ 無視して[OK]
[データを返す]コマンドボタン で抜ける

データを返す先を支持して 終了です

回答No.4

某HN クロメート(Chromate)（@CoalTar）

SQLの作成をマクロの自動記録で記録してみた
ただし、意味不明の部分もあるので動くかどうかはわからない。
ちなみに、前回の回答＋「お客様名」で降順にしてみた。
不要ならOrder By～を削除、昇順なら DESC を消す

ファイルの場所 D:\BOOK1.xls とする

新しいブックを作成し、
1. [Alt]+[F11]VBE
2. 挿入 - 標準モジュール
3. 下記貼り付け
4. [×]閉じる
5. [Alt]+[F8]macro1を実行してみる
6. 1回行えば不要なので VBEのmodule1を右クリック 削除
7. 編集したい場合は、「クエリの編集」ボタン クリックして MSQueryを起動させ「SQL」ボタンをクリック
8. 自動更新したい場合は「データ範囲のプロパティ」を使ってください

'↓ここから
Sub Macro2()
With ActiveSheet.QueryTables.Add(Connection:= _
　 　 　"ODBC;DSN=Excel Files;DBQ=D:\BOOK1.xls;DefaultDir=D:;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
　 　 　, Destination:=Range("A1"))
　 　 　.CommandText = Array( _
　 　 　"SELECT `Sheet1$`.店舗, `Sheet1$`.お客様名" & Chr(13) & "" & Chr(10) & _
　 　 　"FROM `D:\BOOK1`.`Sheet1$` `Sheet1$`" & Chr(13) & "" & Chr(10) & _
　 　 　"union" & Chr(13) & "" & Chr(10) & _
　 　 　"SELECT `Sheet2$`.店舗, `Sheet2$`.お客様名" & Chr(13) & "" & Chr(10) & _
　 　 　"FROM `D:\BOOK1`.`Sheet2$` `Sheet2$`" & Chr(13) & "" & Chr(10) & _
　 　 　"ORDER BY `Sheet1$`.お客様名 DESC")
　 　 　.Name = "Excel Files からのクエリ"
　 　 　.FieldNames = True
　 　 　.RowNumbers = False
　 　 　.FillAdjacentFormulas = False
　 　 　.PreserveFormatting = True
　 　 　.RefreshOnFileOpen = False
　 　 　.BackgroundQuery = True
　 　 　.RefreshStyle = xlInsertDeleteCells
　 　 　.SavePassword = False
　 　 　.SaveData = True
　 　 　.AdjustColumnWidth = True
　 　 　.RefreshPeriod = 0
　 　 　.PreserveColumnInfo = True
　 　 　.Refresh BackgroundQuery:=False
End With
End Sub
'↑ここまで

補足

間違えました！エラーが出たのは上のマクロです。
ＳＱＬを調べるととても使えそうだったので↓を試そうとしたんですが、そもそものサーバー名で「応答していません」エラーが出ました。
会社のパソコンなので複数のパソコンがつながっているためでしょうか・・・？

回答No.3

某HN クロメート(Chromate)（@CoalTar）

No1です。試しにSQL文でつくってみた
(クエリウィザードで注意メッセージが出るけど続行する)
SELECT `Sheet1$`.店舗, `Sheet1$`.お客様名
FROM `D:\BOOK1`.`Sheet1$` `Sheet1$`
union
SELECT `Sheet2$`.店舗, `Sheet2$`.お客様名
FROM `D:\BOOK1`.`Sheet2$` `Sheet2$`

補足

エラーが出て動きませんでした。。。涙

回答No.2

KURUMITO

例えばブック1のシート1にはA1セルに店舗、B1セルに伝票No、C1セルにお客様名、・・・などの項目名が並んでいるとして2行目からデータがそれぞれ入力されているとします。
シート2でもシート1と同じになっているとします。
そこでシート2には作業列を作って、シート1とシート2の店舗名とお客名を並べそれをもとに作業をすることにします。
シート2のG2セルには次の式を入力してH2セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)<=COUNTIF(Sheet1!$A:$A,"<>")-1,IF(COLUMN(A1)=1,INDEX(Sheet1!$A:$A,ROW(A1)+1),IF(COLUMN(A1)=2,INDEX(Sheet1!$C:$C,ROW(A1)+1),"")),IF(ROW(A1)>COUNTIF(Sheet1!$A:$A,"<>")+COUNTIF(Sheet2!$A:$A,"<>")-2,"",IF(COLUMN(A1)=1,INDEX(Sheet2!$A:$A,ROW(A1)-COUNTIF(Sheet1!$A:$A,"<>")+2),IF(COLUMN(A1)=2,INDEX(Sheet2!$C:$C,ROW(A1)-COUNTIF(Sheet1!$A:$A,"<>")+2),""))))

I2セルには次の式を入力して下方にドラッグコピーします。

=G2&H2

J2セルには次の式を入力して下方にドラッグコピーします。

=IF(G2="","",IF(COUNTIF(G$2:G2,G2)=1,ROUNDDOWN(MAX(J$1:J1),-4)+10000,IF(COUNTIF(I$1:I1,I2)>0,"",IF(COUNTIF(I$1:I1,I2)=0,IF(ROUNDDOWN(INDEX(J$1:J1,MATCH(G2,G$1:G1)),-4)=ROUNDDOWN(MAX(J$1:J1),-4),MAX(J$1:J1)+1,SMALL(J$1:J1,RANK(ROUNDDOWN(INDEX(J$1:J1,MATCH(G2,G$1:G1)),-4)+10000,J$1:J1,1)-1)+1)))))

これでブック1での作業は終わります。
お求めのブック2での作業ですが例えばシート1のA1セルに店舗名、B1セルにお客名と文字を入力します。
A2セルには次の式を入力してB2セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>COUNT([Book1]Sheet2!$J:$J),"",INDEX([Book1]Sheet2!G:G,MATCH(SMALL([Book1]Sheet2!$J:$J,ROW(A1)),[Book1]Sheet2!$J:$J,0)))

これで店舗ごとにお客名が表示されます。

お客を優先しての表にするためにはA,B列を選択してコピーし、その後に例えばD1セルを選択してから「形式を選択して貼り付け」で「値」にチェックをして貼り付けます。
その後にD及びE列を選択してから「並び替えとフィルター」から「ユーザー設定の並び替え」で「先頭行をデータの見出しとして使用する」にチェックをし、最優先されるキーをお客名としてOKすればよいでしょう。

補足

セルの入力した所、エラーが出てしまいました。
ＢＯＯＫ１に複数枚のシートがあるからでしょうか？
あと、作業列挿入なんですが、データが膨大なため作業列として店舗+お客様名がでるセルを作ると、行数がえらいことになります。。

なにか案がありましたら引き続きお助けください。orz

回答No.1

某HN クロメート(Chromate)（@CoalTar）

質問があまりにも漠然としていて、、、

※バージョン不明、当方XL2003

案1 SQL
外部データの取り込みで 新しいデータベース Excel Files
でSheet1の欲しい列データを取り込み、SQLを編集して union でSheet2を追加すれば重複はしないと思います。

案2. マクロの自動記録で
コピー&ペースト や フィルタオプションの設定 で重複を削除する操作を自動化する

顧客データは常に更新させるのか、その時点のものを保存しておくのかも不明
案1は常に更新(1分単位) 一工夫でその時点のものを保存も可能、案2はその時点のもので保存できる

案2のほうが分かりよいと思いますが、
>エクセルは初級者なのでマクロなど難しい事は分かりません。。。
となると難しいです

補足

失礼しました！
バージョンは２００３です。
顧客データは伝票データに追記するたびに更新したいです。
顧客データのＢＯＯＫはデータ保存用にしたいので、

伝票データBookのsheet1+sheet2=顧客データBook

になるようにしたいと思います。