ExcelでA列に30種類の中からランダムに商品名が(重複しています)B列~D列にその商品の売上日、伝票番号、請求額が入力されています。
やりたいことは商品名ごとに売上日、伝票番号、請求額を別の請求書フォーマットに内訳として転記したいのです。
(別シートに作成してあります)
現在はソートを利用して、あとは手作業でコピーして貼り付けをしているのですがそれを自動でできる方法はあるでしょうか。
ソートする商品名は月毎でばらばらなので簡単なマクロだとどうしてもソートする部分が手動になってしまって困っています。
ピポットだと集計してしまうので内訳すべての転記ができません。
本来ならAccess等を使用するべきだとは思うのですがExcelでなんとかできないものかと皆様のお知恵を貸して下さい。
よろしくお願いします。
No.1
- 回答日時:
1枚の伝票(1個の伝票番号)に同一商品が複数なければ、
=商品&伝票番号 とくっつけて、
(商品、例えば鉛筆をいつ、どこに売ったのか選別するため)
VLOOKUPで請求書に引っ張ってきたら如何でしょう?
VLOOKUPで出たエラーは、ISERROR関数とIF式で、""(空白)にします。
多分これだけでは完全な回答ではないと思いますので、補足ください。
この回答への補足
お忙しいところ回答ありがとうございます。
補足を要求されましたので、もう少し記述しますと、
商品は30種類の中から月毎にランダムで重複入力されていて伝票番号はユニークで重複はありません。例えば「鉛筆」という商品は今月伝票番号1,4,10で売上が上がっていたとすると「鉛筆」で集計した場合その商品に関する明細は3項目になります。
それを別のシートに転記したいのですが・・・。
VLOOKUPを使用すると1つしか抽出されないですし、毎月の検索条件も変わりますので上手くいかないのですが・・。
手動でソートをして、コピーすればいいのですが・・・。
No.2
- 回答日時:
補足恐れ入ります。
なるほど、
伝票番号がユニークであるならば、
抽出条件は=商品&伝票番号でよいとして…
転記するシートには、
1:単一の商品(及びその行にぶら下がってる明細)だけ反 映させるのか
2:例えば、ある特定の取引先に売った商品全てを、
月次で反映させるのか、
(転記するシートに複数の商品が何らかの形で
ソートされて反映されるのか)
これによって大分やり方は代わってくると思いますが、
まず簡単な1:の話でしたら、
商品名のリストを作って、コンボボックスで、
反映される商品を選択式にして、VLOOKUPで引っ張ってくればいいですよね。
=VLOOKUP(【コンボボックスで選んだ商品】,A列:B列,2,0)
(売上日の場合)
複数の商品(鉛筆&ボールペン&消しゴム)
であるなら、2位以下のソート順は、何か約束があるのでしょうか?
考えておきますので、不明な点等あれば補足ください。
No.3
- 回答日時:
複数の商品を転記するのであれば、
商品名を何らかの形で数値化して(商品コード等とは別に、ソート順を決定させるものとして)
ソートに必要な2つめの要素(伝票番号順や日付順)を&でくっつけたものをVALUEで数値化します。
月次での変動要因が、商品のソート順ですので、
商品の数値データは、一番上にしたい商品が一番大きい数値になるようにコンボボックスとVLOOKUPで引当ます。
もし、販売件数が多い商品順であれば、COUNTIFで、順番つけられますよね。
そうして作成したソート用の数値をRANKで返せば、数値が大きい順にランキングできますので、
その順にVLOOKUPで引っ張ってくれば、よろしいのでは。
月ごとのソート順にまったく法則性がなければ…、
コンボボックスを29個つくるしかないのかなぁ。
マクロを使えばもっと簡単にできそうですが、
申し訳ありませんが、マクロは詳しくないもので。
あまりお役にたてませんでしたね。
私の回答で、不明な点があれば、補足でお問い合わせくださいませ。
アドバイスありがとうございました。
確かにVLOOKUPでデータを引っ張ってくることはできるのですが
月毎のソートや商品名に法則性がまったくないために
その都度検索したい商品名をあてなければならないので
(まさしくコンボボックスを29個作成するはめに・・・・)
誰でも使用できるExcelファイルにならなくて困っていました。
ある程度Excelができる人ならこの方法でも可能なのですが・・。
でも、今後の参考にさせていただきます。
ありがとうございました。
No.4
- 回答日時:
不特定のデータ数ですから、関数は使う気になりませんね。
下記VBAを書いてみました。●データが入力されているシート="Sheet1"。請求書フォーマットのあるシート="Sheet2" とします。
Sheet1のE列に連番を振ります。ソートしたデータを元に戻すためです。
Sheet1のデータを商品名、売上日、伝票番号の優先キー順で並べます。(商品名は最優先です。後は自由)
Sheet2がどのような形か分かりませんので勝手に想定しました。
商品名
AAA04
売上日 伝票番号 請求額
2001/7/ 9 1021 61,600
2001/7/12 1020 46,700
2001/7/14 1027 12,200 のように想定し、
商品名の下のセルに範囲名:trsSyohin(上図のAAA04の場所)
売上日の下のセルに範囲名:trsData(上図の2001/7/ 9の場所)
Sheet1のA1(商品名という表題が入っている?)に範囲名:DataTop を付けます。
●Sheet2にコントロールツールボックスからボタンを貼り付けます。
(表示→ツールバー→Visual Basic)
●ツール→マクロ→Visual Basic Editor でVBE画面に移り、Sheet2のコードウインドウに貼り付けます。
Private Sub CommandButton1_Click()
DataTensou
End Sub
●次は標準モジュールを挿入し(挿入→標準モジュール)貼り付け
Public dataNum As Long 'データ個数
Public rgDt As Range 'データ基準セル
Public rgTsyo, rgTdat As Range '商品転記セル、データ転記基準セル
Public cot As Long 'カウンタ
Public newSyohin As String '読み込んだ商品名
Public TensoNum As Long '転記した個数
Public endFlg As Boolean 'データ終了フラグ
Public Sub DataTensou()
Set rgDt = Range("DataTop") 'データ基準セル
Set rgTsyo = Range("trsSyohin") '商品転記セル
Set rgTdat = Range("trsData") 'データ転記基準セル
'
With rgTdat '書き込み済みデータを消去
If TensoNum > 0 Then
Range(.Cells(1, 1), .Cells(TensoNum, 3)).ClearContents
End If
End With
'
dataNum = Worksheets("Sheet1").UsedRange.Rows.Count - 1
With rgDt
TensoNum = 0 '転記した個数をクリア
newSyohin = .Offset(cot + 1, 0) '今度転記する商品名
rgTsyo.Offset(0, 0) = newSyohin
While newSyohin = .Offset(cot + 1, 0) And cot <= dataNum
rgTdat.Offset(TensoNum, 0) = .Offset(cot + 1, 1)
rgTdat.Offset(TensoNum, 1) = .Offset(cot + 1, 2)
rgTdat.Offset(TensoNum, 2) = .Offset(cot + 1, 3)
TensoNum = TensoNum + 1
cot = cot + 1
Wend
End With
'
If cot > dataNum Then
MsgBox "終了しました"
cot = 0: TensoNum = 0: endFlg = True
Else
'どちらかの「'」をはずすとプレビューか印刷を実行
'ActiveSheet.PrintPreview 'プレビュー(印刷範囲を設定すること)
'ActiveSheet.PrintOut '印刷(印刷範囲を設定すること)
End If
End Sub
Public Sub PrintALL() '全印刷
While endFlg = False
DataTensou
Wend
endFlg = False
End Sub
ボタンを押す毎に商品名単位で転記します。終われば、最初から繰り返せます。
自動印刷やボタン1発で全印刷とか・・・いろいろできそうです。(参考に書きました)
いつも長くなってしまう・・・デハ。
この回答への補足
nishi6さんお忙しい中回答ありがとうございます。
つたない説明文章からよくぞここまで意図をくんでいただけて
本当にありがとうございました。まさしく、自動ですべての商品に関しての
ソート、転記、印刷をしたかったのでした。(参考のコードが役にたちます。)
しかし・・。
元のデータが入力されているSheet1は
商品名 売上日 伝票番号 請求金額 連番
AAA 2001/6/1 0001 1000 1
BBB 2001/6/2 0002 2000 2
CCC 2001/6/3 0003 3000 3
AAA 2001/6/4 0004 1000 4
AAA 2001/6/5 0005 1000 5
CCC 2001/6/6 0006 3000 6
にしましたが、上手くソートができなくて(?)Sheet2には
商品名
AAA
売上日 伝票番号 請求額
2001/6/ 1 0001 1000
としかでてきません。
連続している場合には
商品名
AAA
売上日 伝票番号 請求額
2001/6/ 4 0004 1000
2001/6/ 5 0005 1000
とされるのですが何か原因はあるのでしょうか。
VBAに関しては初心者もいいところなので、記述を見てもなんとなくしかわかりません。
それとも、作成の仕方が悪いのでしょうか。
丸投げ状態で大変申し訳ございませんがもしよろしければアドバイスをよろしくお願い致します。
補足を書いた後、じっくりコードを眺めていましたら、
初めにデータをソート(並び替え)しておけばいいんですよね?
と、気が付きました。
nishi6さんも「ソートしたデータを元に戻すためです。」と
書かれているのに、慌てていて申し訳ございませんでした。
自分でもがんばって少しやってみます。
もし、意図されている事が違っていたり、いい案があるようでしたら
また、アドバイス下さい。よろしくお願い致します。
No.6ベストアンサー
- 回答日時:
Sheet1のデータを(人が手で)ソートした後、マクロを実行する想定でした。
今の結果は商品名でソートされていない状態での結果のようです。下記に自動的にソートし、全商品の出力が終ればデータ並びを元に戻すようにしました。
まず、3箇所挿入します。前後の行を見て、「*** 追加 ***」の行を挿入して下さい。
=挿入1=
Public endFlg As Boolean 'データ終了フラグ
Public sortFlg As Boolean 'ソート済みフラグ *** 追加 ***
Public Sub DataTensou()
=挿入2=
End With
'
DataSort 'データソート *** 追加 ***
dataNum = Worksheets("Sheet1").UsedRange.Rows.Count - 1
=挿入3=
If cot > dataNum Then
FukkiSort '元の順に戻す *** 追加 ***
MsgBox "終了しました"
次に標準モジュールに貼り付けます。(追加)
Public Sub DataSort() 'データソート
If sortFlg = True Then Exit Sub
Application.ScreenUpdating = False
With Worksheets("Sheet1")
.Select: .Range("A2").Select
Selection.Sort Key1:=Range("A2"), Order1:=1, Key2:=Range("B2"), Order2:=1, Key3:=Range("C2"), Order3:=1, Header:=0
End With
Worksheets("Sheet2").Select
sortFlg = True
Application.ScreenUpdating = True
End Sub
Public Sub FukkiSort() '元の順に戻す
If sortFlg = False Then Exit Sub
Application.ScreenUpdating = False
With Worksheets("Sheet1")
.Select: .Range("A2").Select
Selection.Sort Key1:=Range("E2"), Order1:=1, Header:=0
End With
Worksheets("Sheet2").Select
sortFlg = False
Application.ScreenUpdating = True
End Sub
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ファイルの漢数字の順番につい...
-
エクセル、並び替え正しくソー...
-
コンボボックスのソートについて
-
数字と漢字が混じった日付デー...
-
OracleとAccessの出力順の違い
-
Selectした時のレコードの取得順
-
SQLのORDERBY句について質問です。
-
SQLで曜日のソートを月火水木金...
-
SQLデータベースの処理
-
ハイフンの入ったデータの並べ替え
-
レコードの登録順がおかしい
-
タイトル行を固定してソートしたい
-
@以下をソートしたい
-
検索結果を指定件数ずつ抽出表...
-
ソートについて
-
1の行を固定した上でVBAを用い...
-
月末日のみソートしたい
-
基本的な質問です。フロッピー...
-
SQLローダーCSV取込で、囲み文...
-
【PL/SQL】FROM区に変数を使う方法
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイルの漢数字の順番につい...
-
並べ替えについて
-
レコードの登録順がおかしい
-
1の行を固定した上でVBAを用い...
-
テーブルレーコードをソートし...
-
リストボックス内を昇順並べる方法
-
エクセル、並び替え正しくソー...
-
コンボボックスのソートについて
-
月末日のみソートしたい
-
ハイフンの入ったデータの並べ替え
-
accessでDISTINCT 句と矛盾
-
アクセスに関して。クエリの並...
-
都道府県順、北からソートした...
-
SQLで曜日のソートを月火水木金...
-
数字と漢字が混じった日付デー...
-
Excelのオートフィルタでソート...
-
Selectした時のレコードの取得順
-
ファイルメーカーでソート後の...
-
excel:一番上の行がソートでき...
-
OracleとAccessの出力順の違い
おすすめ情報