gooドクター有料プランが1ヶ月間無料!

教えて下さい。現在、商品をデータベースで管理しているのですが、システム変更をしようと思っており、新しいシステムの方式にデータを書き換えなければなりません。
excel情報
A1:商品A B1:10  C1:0
A2:商品A B2:20  C2:50 
A3:商品A B3:50  C3:0
A4:商品A B4:100 C4:38
この内容を文章にすると、商品Aは10個以下は0円、11個~20個までは50円、21個~50個までは0円、51個~100個まで38円となります。
これを下記のようにしたいのです。
A1:商品A B1:1  C1:0
A2:商品A B2:11  C2:50 
A3:商品A B3:21   C3:0
A4:商品A B4:51 C4:38
A5:商品A B5:100 C5:0
先の例とは違い、”迄”の考え方から"から"の考え方に変わっています。
文面にすると内容は同じなのですが、全くexcel情報が変わっています。
この様な複雑な変換を、そもそもexcelで出来る気がしないのですが、可能なのでしょうか?
もし、気になる方が居られましたら、回答をお願い致します。
基本的なVBAの使い方はわかりますが、書き込むレベルではありません。
もしコードを書くことができるのであれば、コピーさせて頂き作業をさせて頂けますでしょうか。
無茶な話かも知れませんが、何卒宜しくお願い致します。

質問者からの補足コメント

  • へこむわー

    誤字がありました。
    B5は101となり、商品Aは101個から0円設定となります。
    また全体量は数万行あり、B列の数量は1~数万の範囲でランダムで記載。
    法則性はなく、文章を読みとり、捉え方は変えてこれを数字に返す みたいな事しか、自分の頭では出てきません。

      補足日時:2021/09/30 15:35
  • スクショ画像の添付が、なぜかアップできずに、実際のデータ値だけを記載しました。
    今回の質問に関連の無い、得意先コード、名称、商品コードなどは省いております。
    シート名は変更できますので指定しません。

    A     B   C
    商品A    359   0
    商品A   3240   34
    商品B     47   0
    商品B   2940  65
    商品B   4980  63
    商品B   9960  61
    商品B   19980  60
    商品C   2499   0
    商品C   2999  63
    商品C   9960  61
    商品D   1999  0
    商品D   2999  33
    商品D   9840  31

    大変見にくくてすみません。

      補足日時:2021/10/01 18:55
gooドクター

A 回答 (23件中1~10件)

以下のマクロを標準モジュールに登録してください。


まず、商品並替 を実行してください。(Sheet1が商品、個数の順にソートされます。ソート範囲はA,B,C列のみです)

次に、数値変換 を実行してください。
実行結果が Sheet2に 結果が出力されます。(A,B,C列にのみ出力)
不明点があった場合、及び得意先コード、名称、商品コードなども含めて出力したい場合は、その旨補足ください。
------------------------------------------
Option Explicit

Public Sub 商品並替()
Dim sh1 As Worksheet
Dim maxrow As Long
Set sh1 = Worksheets("Sheet1")
maxrow = sh1.Cells(Rows.count, 1).End(xlUp).Row '最大行取得
Call sh1.Range("A1:C" & maxrow).Sort(Key1:=sh1.Range("A1"), Key2:=sh1.Range("B1"), Header:=xlNo)
MsgBox ("並替完了")
End Sub

Public Sub 数値変換()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim maxrow As Long
Dim row1 As Long
Dim row2 As Long
Dim shohin As String '今回商品
Dim p_shohin As String '前回商品
Dim p_count As Long '前回個数
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
sh2.Rows("1:" & Rows.count).ClearContents
maxrow = sh1.Cells(Rows.count, 1).End(xlUp).Row '最大行取得
row2 = 1
p_shohin = ""
p_count = -1
For row1 = 1 To maxrow
shohin = sh1.Cells(row1, "A").Value
If p_shohin <> shohin Then
If p_count <> -1 Then
sh2.Cells(row2, "A").Value = p_shohin
sh2.Cells(row2, "B").Value = p_count + 1
sh2.Cells(row2, "C").Value = 0
row2 = row2 + 1
End If
sh2.Cells(row2, "A").Value = shohin
sh2.Cells(row2, "B").Value = 1
sh2.Cells(row2, "C").Value = sh1.Cells(row1, "C").Value
row2 = row2 + 1
p_shohin = shohin
Else
sh2.Cells(row2, "A").Value = shohin
sh2.Cells(row2, "B").Value = p_count + 1
sh2.Cells(row2, "C").Value = sh1.Cells(row1, "C").Value
row2 = row2 + 1
End If
p_count = sh1.Cells(row1, "B").Value
Next
If p_count <> -1 Then
sh2.Cells(row2, "A").Value = p_shohin
sh2.Cells(row2, "B").Value = p_count + 1
sh2.Cells(row2, "C").Value = 0
End If
MsgBox ("変換完了")
End Sub
    • good
    • 1
この回答へのお礼

tatsumaru77様  凄すぎます!!!
驚きすぎて、思わず誰もいない会社で一人で声を上げてしまいました(笑)
sheet2が自動生成されませんでしたが、手動で作り、マクロ実行で1発変換です。
何をどうしたらこんなにできるのか、興味があるのでこの案件が終われば時間をかけてでもコードを読み解きたいです。
(出来る気がしませんが、出来る自分になりたいです)
本当に心より感謝致します。
また、機会があればどうぞ宜しくお願い致します。

お礼日時:2021/10/01 21:04

>驚きすぎて、思わず誰もいない会社で一人で声を上げてしまいました(笑)


>sheet2が自動生成されませんでしたが、手動で作り、マクロ実行で1発変
換です。

うまくいったようで何よりです。
Sheet2は予め用意してある前提でした。その旨、明記しなかったので
自動で作成されると思ったのですね。失礼しました。

尚、シート名を変えたい場合
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")
の個所を、
Set sh1 = Worksheets("○○")
Set sh2 = Worksheets("△△")
のように変えれば、OKです。
○○が元データのシート名
△△が出力用のシート名です。
(Sheet1は商品並替も変える必要があります)
    • good
    • 1
この回答へのお礼

ご丁寧にありがとうございました。
また機会がございましたら、宜しくお願い致します。

お礼日時:2021/10/02 08:53

データを商品及び個数で並べ替えるとき、得意先コード、名称、商品コードなどは並べ替えなくてもよいですか。


もし、並べ替えなくてよいなら、A、B、C列の情報のみで構いません。
並べ替えた結果がほしいなら、最大の列がいくつになるか、教えてください。その範囲を全て並べ替えます。

また、数値を変換した結果ですが、A,B,Cの3列のみで本当に良いのでしょうか。商品コードが並べ替えられ、個数が変換された場合、得意先コード、名称、商品コードをどのように対応させるかは、どうするのでしょうか?
    • good
    • 1
この回答へのお礼

何度もご返事が遅くなり申し訳ありません。
画像も返信もなぜか、エラーが出てしまいます。
ブラウザを変えて返信しました。

このままで問題ありません。
余りの膨大な量に得意先別にシートは変えておりますので、後で得意先コード、名称は挿入します。
加工工程内で、新たなシートが発生するなら、事前にシートをbook毎に変換してから、sheet1のみの状態からスタートします。
また商品コードも、後でVLOOKUPで参照しますので、問題ありません。
エラーが少ない様に、シンプルな状態で作業を進めて、確実性を上げたいからです。
本当にお手数ではありますが、何卒宜しくお願い致します。

お礼日時:2021/10/01 19:59

めぐみん_様


前回の件については、ここで議論するのは質問者のかたに失礼になるので、
もし、きちんと議論されたいのでしたら、あらたに質問をおこしていただけませんでしょうか。その質問について、納得行くまで回答したします。

シートインデックスを使用して、入力用と出力用のシートを区別する方法もありますが、その場合は、左側から1番目が○○用、2番目が●●用
と決めておく必要があり、これを質問者に告知しておく必要があります。
今回の場合、左側から1番目が入力用、2番目が出力用としておけば、
良いことになりますが、頻度が多くないので、Sheet1,Sheet2の方がシンプルと考えました。
    • good
    • 1

No14です。


補足ありがとうございました。
>1.右になり、1行の商品もあれば8行の商品もあります
>2.並び変えています

そうすると、一旦、商品名(昇順)、個数(昇順)で並び替えをしてから、
処理することになります。
並び替えはマクロで行います。
そうすると、
①Sheet1は並び替えた結果になる。したがって、並び替え前のデータを残しておきたいなら、予め、他のシート等にバックアップをしておく必要がある。
②Sheet2は、商品名が昇順に並び替えた結果で出力される。
ということになりますが、よろしいでしょうか。
    • good
    • 1

No.16様



余談ですが。

割り切れるか否かとは、確か4000だと目的とは違う回答が出るとか記憶してます。
なので割り切れたら余計な方は0を入れれば良いと書いたかな?
割り切れない(余りが1になる4001の場合)時は既に作成してたループへと進ませる。
即ち剰余が0であればループ処理の必要はないのでは?と言う意味で書いたつもりでしたがちょっと説明不足していたのかもですね。

シート名については同じ処理を予測してますが(既に取り込んで一部処理はされてる感じ?)、シート名ではなくシートインデックスで行なうと気にしなくていいかなと感じました。

余談なのでまた削除されるかもですが・・・
    • good
    • 1
この回答へのお礼

めぐみん様。
この度は、本当にご協力頂きまして、心より感謝致します。
また昨晩から返信が出来なった事に、お詫び申し上げます。
何とか、tatsumaru77様のご協力により解決する事ができたのも、めぐみん様が色々とお考え頂き投稿して頂いた結果だと思います。
何もかも、自分の至らなさがお恥ずかしい限りです。
余談でも、悩める私にとっては考えて頂いている十分に励みになりました。
また機会があれば、何卒宜しくお願い致します。

お礼日時:2021/10/01 21:09

めぐみん_様


追伸:
>特に今回はシステムが吐き出したCSVファイルをExcelで開いているので、特定のファイル名にならない可能性もあると感じますが如何でしょう?

1回きりの作業なので、マクロのあるエクセルファイルに、CSVファイルを取り込み、その取り込んだシートのシート名をSheet1に修正後、マクロを実行してもらうつもりでいます。そうすれば、特に問題ないと考えますがいかがでしょうか。
    • good
    • 1

めぐみん_様


先日の回答の件は、こちらで検証した結果、余りが出ること検証済みです。

今回の件ですが、シートが1つでたりるなら、シート名は不要かと思います。その場合は、結果をH,I,J列等に出力することになるかと思いますが。

変換後の数値をCSV形式に戻すかどうかはわかりませんが、
もし、もどすなら、変換結果は、別シートの方が良いと考えました。
そのため、入力用シートと出力用シートを指定させたほうがよいと考えた次第です。

質問者の方から、同じシート内に変換結果を出力したいという要望が出されれば、特にシート名を指定する必要はないか思います。
ただ、個人的には、シート名を指定せず、アクティブシートを直接更新するマクロは、間違って別のシートをひらいた状態で、そのマクロを実行すると、そのシートが破壊されてしまう危険があるので、なるべく避けたいと考えています。
    • good
    • 1

No.14様へ



先日は勘違いにご指南頂きありがとうございました。
でも回答した内容として
・まず割り切れるか否かの判定
・その結果による処理の分岐
を掲げていたので、1つ余るとかはあり得ないと初級レベルは思いましたが再現処理をされた結果、きっとそう出たのでしょうね。
こちらは検証なしでの回答でしたから。

今回の件につきましてもですがシート名って具体的に必要な物なのでしょうか?と度々感じます。
特に今回はシステムが吐き出したCSVファイルをExcelで開いているので、特定のファイル名にならない可能性もあると感じますが如何でしょう?
    • good
    • 1

補足要求です。



1.同じ商品は必ず1つのかたまりになっていますか。
添付図の左側のようになっていれば、問題ありませんが、右側のようになってるケースは
ありませんか。
(商品A、商品Bの黄色のセルのように、元の商品のかたまりからはずれているケース)

2.B列の個数は、必ず、昇順に並んでますか。

3.数値を変換すると必ず1行が追加されますが、
追加された1行のC列の金額は、必ず0円でよろしいでしょうか。
あなたの提示した例の、
A5:商品A B5:101 C5:0
に相当する個所です。
上記以外の行は、元のC列の金額をそのまま採用します。


4.提示された例では1行目からデータが始まっていますが、
通常は1行目は見出しで、2行目からデータが始まります。
1行からデータが始まるということで間違いないでしょうか。

5.シート名が明示されていないので、
元のデータのシート名を"Sheet1"として、変換した結果を"Sheet2"に出力しますが
それで良いでしょうか。
「excelで大量の数字の変換」の回答画像14
    • good
    • 1
この回答へのお礼

大変、返信が遅くなり申し訳ございませんでした。
片手間で出来る内容では無いのですが、片手間な物でいちいち遅れが生じてしまいます。
回答
1.右になり、1行の商品もあれば8行の商品もあります
2.並び変えています
3.0でOKです
4.修正可能ですので、1で問題ありません
5.修正可能ですので、sheet1、sheet2で問題ありません
ご協力ありがとうございます。

お礼日時:2021/10/01 17:10

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

gooドクター

このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング