エクセルを使って顧客データを作っています。
伝票データ(BOOK1)に
・婚約指輪のお客様データ(SHEET1)
・結婚指輪のお客様データ(SHEET2)
があり、それぞれこんな感じ↓のデータが並んでいます。
店舗 伝票NO お客様名 売上合計 品番・・・
-------------------------------------------------
A店 1-2-2 東京一郎 ¥120000 SDD223
B店 3-5-5 神戸次郎 ¥953330 LP002-30
このデータを、顧客データ(BOOK2)として自動的に反映される様にしたいです。
(1)反映したい列を選びたい。
(2)同じ名前の項目(例:店舗、お客様名など)はまとめたい。
婚約指輪のお客様(Book1sheet1)と結婚指輪のお客様(Book1sheet2)、両方の名前の名前をBook2の「お客様名」列に反映させたい。
上記の作業を出来るだけ簡単に組みたいです。
エクセルは初級者なのでマクロなど難しい事は分かりません。。。
でも、今日中になんとかしたいです!
よろしくお願いします!!!
A 回答 (5件)
- 最新から表示
- 回答順に表示
No.5
- 回答日時:
マクロを試していただきありがとうございます
使えないということが分かったのは良かったです。
以下、操作の詳細、新規ブックにて。
データ - 外部データの取り込み - 新しいデータベースクエリ
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
- 回答日時:
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
'↑ここまで
この回答への補足
間違えました!エラーが出たのは上のマクロです。
SQLを調べるととても使えそうだったので↓を試そうとしたんですが、そもそものサーバー名で「応答していません」エラーが出ました。
会社のパソコンなので複数のパソコンがつながっているためでしょうか・・・?
No.3
- 回答日時:
No1です。
試しにSQL文でつくってみた(クエリウィザードで注意メッセージが出るけど続行する)
SELECT `Sheet1$`.店舗, `Sheet1$`.お客様名
FROM `D:\BOOK1`.`Sheet1$` `Sheet1$`
union
SELECT `Sheet2$`.店舗, `Sheet2$`.お客様名
FROM `D:\BOOK1`.`Sheet2$` `Sheet2$`
No.2
- 回答日時:
例えばブック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すればよいでしょう。
この回答への補足
セルの入力した所、エラーが出てしまいました。
BOOK1に複数枚のシートがあるからでしょうか?
あと、作業列挿入なんですが、データが膨大なため作業列として店舗+お客様名がでるセルを作ると、行数がえらいことになります。。
なにか案がありましたら引き続きお助けください。orz
No.1
- 回答日時:
質問があまりにも漠然としていて、、、
※バージョン不明、当方XL2003
案1 SQL
外部データの取り込みで 新しいデータベース Excel Files
でSheet1の欲しい列データを取り込み、SQLを編集して union でSheet2を追加すれば重複はしないと思います。
案2. マクロの自動記録で
コピー&ペースト や フィルタオプションの設定 で重複を削除する操作を自動化する
顧客データは常に更新させるのか、その時点のものを保存しておくのかも不明
案1は常に更新(1分単位) 一工夫でその時点のものを保存も可能、案2はその時点のもので保存できる
案2のほうが分かりよいと思いますが、
>エクセルは初級者なのでマクロなど難しい事は分かりません。。。
となると難しいです
この回答への補足
失礼しました!
バージョンは2003です。
顧客データは伝票データに追記するたびに更新したいです。
顧客データのBOOKはデータ保存用にしたいので、
伝票データBookのsheet1+sheet2=顧客データBook
になるようにしたいと思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 【エクセル」 特定のセルで条件抽出した列を、別シートに上から詰めて表示したい。 8 2022/04/08 16:00
- Visual Basic(VBA) 指定月分の顧客データファイルを統合して並べ替え、所定の場所に貼り付ける (再質問) 4 2022/09/14 22:51
- Visual Basic(VBA) 指定月分の顧客データファイルを統合して並べ替え、所定の場所に貼り付ける 3 2022/09/10 07:55
- Excel(エクセル) 【関数】【マクロ】データの転記の方法について 2 2023/07/26 15:22
- Access(アクセス) 対象月の2桁表示について 1 2023/01/07 05:08
- Visual Basic(VBA) エクセルのマクロについて教えてください。 1 2023/08/03 11:27
- その他(Microsoft Office) パワークエリの複数ファイルのデータ統合について 3 2022/07/14 17:06
- Excel(エクセル) メモ帳からエクセルにセル区切りで表示させたいんです 7 2023/02/25 22:04
- Excel(エクセル) 【Excel】住所に郵便番号を付記する方法 3 2022/05/07 17:15
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
【関数】先頭だけにある、半角...
-
エクセル 白黒印刷で白線を印刷...
-
Excelのチェックボックスの使い...
-
【関数】適切な文字数の数字を...
-
Excelのpivotについて質問です
-
Excel ピボットテーブルで日付...
-
LOOKUP関数を使えばいいのでし...
-
エクセル関数を教えてください
-
エクセルのセルに同じ大きさの...
-
UNIQUE関数が使えないバージョ...
-
excelの不要な行の削除ができな...
-
エクセルで「-0.0」と表示さ...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの関数について教えて...
-
Aというブックの1というシート...
-
【マクロ】シート名を取得する...
-
VBA Private Sub Worksheet_Cha...
-
VBA、Excelのworkbook.open に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報