エクセルを使って受注一覧表と出荷スケジュールの連動をさせたいです。
イメージは以下の通りです。
Sheet1に注文一覧表を作ります。(現在、既にこの表はエクセルで存在します)
この表(Sheet1)では、出荷スケジュールが分かりづらいので、現在はSheet2の表を印刷し、手書きで数量を記入して管理をしています。
この手書きの作業がとても手間なので、できればSheet1からデータを読み込んで自動的にSheet2に数量が表示されるようにさせたいです。
その場合、Sheet2のB2にはどのような関数を入れれば良いのでしょうか。
Sheet1
注文番号 商品名 出荷月 出荷日 数量
1001 AA-01 10 2 100
1002 AA-02 10 2 150
1003 AA-03 10 2 200
1004 AA-04 10 4 300
1005 AA-03 10 5 100
・
・
・
Sheet2
商品名 1日 2日 3日 4日 5日 ~ 31日
AA-01
AA-02
AA-03
AA-04
AA-05
・
・
・
(商品名は400種類程度あります)
大変困っています。どなたか良い方法をご教示いただけないでしょうか。
よろしくお願いします。
No.4
- 回答日時:
タイプとしては、エクセルの「表の組み換え」です。
関数でやろうとすると、既に他回答で出ているように、複雑になります。ーー
一方VBAでやれば思ったとおりに簡単に出来ます。
仕事の絡んだようなこと(取引関係など)で、エクセルで処理するには、VBAが出来ることが必須だと思います。
VBAを勉強したことありますか。VBAを勉強するか、非常に意味も難しい関数式をコピペして、意味も判らず使うか。
好きなほうを選択するしかない。どちらも現状では、課題を丸投げで、「したい」といっているだけで、本質問に対し、基礎体力不足か。
ーー
例データ Sheet1
注文番号商品名出荷月出荷日数量
1001AA-01102100
1002AA-02102150
1003AA-03102200
1004AA-0210250
1005AA-04104300
1006AA-0410430
1007AA-03105100
1008AA-0110650
1009AA-0210670
1010AA-0310680
1011AA-0110640
1012AA-0210610
1013AA-0310720
別シートにコピーし商品名でソート Sheet2 A1:E14
注文番号商品名出荷月出荷日数量
1001AA-01102100
1008AA-0110650
1011AA-0110640
1002AA-02102150
1004AA-0210250
1009AA-0210670
1012AA-0210610
1003AA-03102200
1007AA-03105100
1010AA-0310680
1013AA-0310720
1005AA-04104300
1006AA-0410430
ーーー
コード ヒョウジュンモジュール
Sub test01()
Dim sh2, sh3
Set sh2 = Worksheets("Sheet2")
Set sh3 = Worksheets("Sheet3")
' 準備
d = sh2.Range("a65536").End(xlUp).Row 'データ最終行
m = sh2.Cells(2, "B")
md = DateSerial(2011, sh2.Cells(2, "C"), sh2.Cells(2, "D"))
MsgBox md
k = 2
sh3.Cells(2, "A") = sh2.Cells(2, "B")
'--
For i = 2 To d
dt = DateSerial(2011, sh2.Cells(i, "C"), sh2.Cells(i, "D")) '日付作成
If sh2.Cells(i, "B") = m Then '商品が変わったか
'--変わらない
c = sh3.Range("B1:Z1").Find(dt).Column ' 該当日付の列を見つける
sh3.Cells(k, c) = sh3.Cells(k, c) + sh2.Cells(i, "E") 'その列に数量を足しこむ
Else
'--変わった
k = k + 1 '次の商品のため、次行に
sh3.Cells(k, "A") = sh2.Cells(i, "B")
c = sh3.Range("B1:Z1").Find(dt).Column '該当日付の列を見つける
sh3.Cells(k, c) = sh3.Cells(k, c) + sh2.Cells(i, "E") 'その列に数量を足しこむ
'--次の行処理の準備
m = sh2.Cells(i, "B")
md = DateSerial(2011, sh2.Cells(i, "C"), sh2.Cells(i, "D"))
End If
Next i
End Sub
実行結果 Sheet3 A1:H4
下記日付は日付シリアル値を使っているから注意。それを「1日」のように表示する表示形式は、わかっているよね。
商品名2011/10/12011/10/22011/10/32011/10/42011/10/52011/10/62011/10/7 ->以下略
AA-01-100---90
AA-02-200---80
AA-03-200--1008020
AA-04---330
ーは空白セルを示す。
No.3
- 回答日時:
状況から推測すると,関数を使って作成する方法は全くお奨めできません。
(元データのリストが伸びていくと,加速度的にシートの計算が重くなっていきます。実際に対象とするリストの行数次第で,耐えられる範囲内であることが確認できれば,関数もありです)ご利用のエクセルのバージョンが不明ですが,Excel2003まではデータメニュー,2007以降では挿入タブから「ピボットテーブルレポート」の作成を開始し,ページ(2007以降ではフィルタ)に「月」,行に「日」,列に「商品」,データ(2007以降ではΣ)に「数量」を投入することで,メンドウな関数を一切使わずに(オマケで当月発生する商品一覧をわざわざ事前に用意する必要もなく),必要な集計が直ちに得られます。
ページフィルタに現れる選択肢から,集計したい月を選びます。
No.2ベストアンサー
- 回答日時:
今仮に、Sheet1で「注文番号」と入力されているセルがSheet1のA1セルであるものとします。
又、Sheet2のA2セルに「商品名」と入力し、Sheet2のA3以下に各商品名を入力し、Sheet2のB2~Sheet2のAF2にかけて、1~31の数値を入力し、Sheet2のA1セルには月を表す数値を入力するものとします。
【方法その1】作業列と関数を併用する方法
まず、適当な列(ここでは仮にSheet3のA列とします)を作業列として使用する事とし、そのA2セルに次の数式を入力して下さい。
=IF(AND(INDEX(Sheet1!$B:$B,ROW())<>"",COUNT(INDEX(Sheet1!$C:$C,ROW()),INDEX(Sheet1!$D:$D,ROW()))=2,ISNUMBER((INDEX(Sheet1!$C:$C,ROW())&"/"&INDEX(Sheet1!$D:$D,ROW()))+0)),INDEX(Sheet1!$B:$B,ROW())&":"&INDEX(Sheet1!$C:$C,ROW())&"/"&INDEX(Sheet1!$D:$D,ROW()),"")
次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼り付けて下さい。
次に、Sheet2のB2~Sheet2のAF2にかけて、1~31の数値を入力して下さい。
次に、Sheet2のB3セルに次の数式を入力して下さい。
=IF(COUNTIF(Sheet3!$A:$A,"="&$A3&":"&$A$1&"/"&B$2),SUMIF(Sheet3!$A:$A,"="&$A3&":"&$A$1&"/"&B$2,Sheet1!$E:$E),"")
次に、Sheet2のB3セルをコピーして、Sheet2のC3~AF3の範囲に貼り付けて下さい。
次に、Sheet2のB3~AF3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。
後は、Sheet2のA3以下に各商品名を入力し、Sheet2のA1セルには月を表す数値を入力しますと、日付毎の各商品の出荷数が自動的に表示されます。
【方法その2】SUMPRODUCT関数で処理する方法(作業列は不要)
※こちらの関数は、元データが500行以上でも、何行あっても、対応しますが、元データの行数が、数千行以上にもなる場合は、計算処理が重くなります。(元データの行数に合わせて、参照範囲が変化するため、行数が200~300行程度ならば、ANo.1様の関数よりは、早くなると思います)
まず、Sheet2のB2~Sheet2のAF2にかけて、1~31の数値を入力して下さい。
次に、Sheet2のB3セルに次の数式を入力して下さい。
=IF(SUMPRODUCT((Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$E:$E))=$A3)*(Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9^9,Sheet1!$E:$E))=$A$1)*(Sheet1!$D$1:INDEX(Sheet1!$D:$D,MATCH(9^9,Sheet1!$E:$E))=B$2)),SUMPRODUCT((INDEX(Sheet1!$B:$B,ROW(Sheet1!$B$1)+1):INDEX(Sheet1!$B:$B,MATCH(9^9,Sheet1!$E:$E))=$A3)*(INDEX(Sheet1!$C:$C,ROW(Sheet1!$C$1)+1):INDEX(Sheet1!$C:$C,MATCH(9^9,Sheet1!$E:$E))=$A$1)*(INDEX(Sheet1!$D:$D,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$D:$D,MATCH(9^9,Sheet1!$E:$E))=B$2)*INDEX(Sheet1!$E:$E,ROW(Sheet1!$E$1)+1):INDEX(Sheet1!$E:$E,MATCH(9^9,Sheet1!$E:$E))),"")
次に、Sheet2のB3セルをコピーして、Sheet2のC3~AF3の範囲に貼り付けて下さい。
次に、Sheet2のB3~AF3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。
後は、Sheet2のA3以下に各商品名を入力し、Sheet2のA1セルには月を表す数値を入力しますと、日付毎の各商品の出荷数が自動的に表示されます。
【方法その3】Excel2007以降のパーションでのみ使用可能な関数で処理する方法(作業列は不要で、SUMPRODUCT関数よりも高速処理が可能)
まず、Sheet2のB2~Sheet2のAF2にかけて、1~31の数値を入力して下さい。
次に、Sheet2のB3セルに次の数式を入力して下さい。
=IF(COUNTIFS(Sheet1!$B:$B,$A3,Sheet1!$C:$C,$A$1,Sheet1!$D:$D,B$2),SUMIFS(Sheet1!$E:$E,Sheet1!$B:$B,$A3,Sheet1!$C:$C,$A$1,Sheet1!$D:$D,B$2),"")
次に、Sheet2のB3セルをコピーして、Sheet2のC3~AF3の範囲に貼り付けて下さい。
次に、Sheet2のB3~AF3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。
後は、Sheet2のA3以下に各商品名を入力し、Sheet2のA1セルには月を表す数値を入力しますと、日付毎の各商品の出荷数が自動的に表示されます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルデーターの並び替え 5 2022/08/06 09:59
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- Excel(エクセル) エクセルでのVBA 2 2022/08/03 06:48
- Excel(エクセル) エクセルの表について 3 2023/04/14 18:00
- ヤフオク! 関税 詐欺でしょうか?ヤフオク 3 2023/06/25 11:22
- Excel(エクセル) エクセルについて 8 2023/02/11 07:36
- Visual Basic(VBA) マクロを教えてください。 7 2023/06/01 19:47
- Visual Basic(VBA) Sheet1の出荷日と品名が並んだ表からSheet2の品名別出荷日別の個数一覧表を作っています。 オ 3 2022/12/01 23:54
- Excel(エクセル) Excelで在庫表(クエリ、ピボット) 2 2022/04/11 17:11
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見る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 フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報