エクセルを使って顧客データを作っています。
伝票データ(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.1
- 回答日時:
質問があまりにも漠然としていて、、、
※バージョン不明、当方XL2003
案1 SQL
外部データの取り込みで 新しいデータベース Excel Files
でSheet1の欲しい列データを取り込み、SQLを編集して union でSheet2を追加すれば重複はしないと思います。
案2. マクロの自動記録で
コピー&ペースト や フィルタオプションの設定 で重複を削除する操作を自動化する
顧客データは常に更新させるのか、その時点のものを保存しておくのかも不明
案1は常に更新(1分単位) 一工夫でその時点のものを保存も可能、案2はその時点のもので保存できる
案2のほうが分かりよいと思いますが、
>エクセルは初級者なのでマクロなど難しい事は分かりません。。。
となると難しいです
この回答への補足
失礼しました!
バージョンは2003です。
顧客データは伝票データに追記するたびに更新したいです。
顧客データのBOOKはデータ保存用にしたいので、
伝票データBookのsheet1+sheet2=顧客データBook
になるようにしたいと思います。
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.3
- 回答日時:
No1です。
試しにSQL文でつくってみた(クエリウィザードで注意メッセージが出るけど続行する)
SELECT `Sheet1$`.店舗, `Sheet1$`.お客様名
FROM `D:\BOOK1`.`Sheet1$` `Sheet1$`
union
SELECT `Sheet2$`.店舗, `Sheet2$`.お客様名
FROM `D:\BOOK1`.`Sheet2$` `Sheet2$`
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.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]
[データを返す]コマンドボタン で抜ける
データを返す先を支持して 終了です
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
タイムスタンプとテキストから...
-
エクセルの文字が途中から消える
-
エクセルの質問です。 F列からL...
-
ワークシートに出現したこの画...
-
Excelの警告について
-
EXCELの散布図で日付が1900年に...
-
エクセルでファイルの最終更新...
-
Excelでの文字色
-
マクロの処理が遅くなった
-
シートの情報を別のシートへま...
-
OFFSET関数を使用したいのです...
-
エクセルの数式バーのフォント...
-
エクセルデーターから必要な項...
-
Excelについて教えてください。...
-
SUBTOTALは、参照された数字で...
-
エクセルの「条件付き書式」を...
-
Excelの関数について このよう...
-
エクセル。金額から「円」を除...
-
Excelの数字の前に入っている空...
-
エクセルの問題です。絶対値の...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報