質問: エクセルにて受注一覧と出荷スケジュールを連動 エクセルを使って受注一覧表と出荷スケジュールの連動をさせたいです。 イメージは以下の通りです。 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種類程度あります) 大変困っています。どなたか良い方法をご教示いただけないでしょうか。 よろしくお願いします。
回答: 今仮に、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セルには月を表す数値を入力しますと、日付毎の各商品の出荷数が自動的に表示されます。