エクセルを使って受注一覧表と出荷スケジュールの連動をさせたいです。
イメージは以下の通りです。
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.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セルには月を表す数値を入力しますと、日付毎の各商品の出荷数が自動的に表示されます。
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以降ではΣ)に「数量」を投入することで,メンドウな関数を一切使わずに(オマケで当月発生する商品一覧をわざわざ事前に用意する必要もなく),必要な集計が直ちに得られます。
ページフィルタに現れる選択肢から,集計したい月を選びます。
お探しの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ランキング
-
セルの数を求めたい
-
ある表にフィルターをかけて出...
-
Lookup関数
-
Excelを無料で使うには? パソ...
-
IFとIFS関数
-
エクセルの数式バーのフォント...
-
EXCELの散布図で日付が1900年に...
-
Excelの表示についての質問
-
再質問です。マクロの修正箇所...
-
データチェックを行うエクセル...
-
Excelピボットテーブルの1行目
-
エクセル日付 文字列の関数がエ...
-
西暦や和暦の表示をyyyymmdd表...
-
エクセルで「ページレイアウト...
-
【ExcelVBA】名前を付けて保存→...
-
Excelに詳しい方! B列が「日...
-
Excelで50個のセルに同じ文字を...
-
Excelについて
-
Excel関数について教えてくださ...
-
Excelで表を作ったところに文字...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報