エクセルの関数について

質問

エクセルの関数について
売り上げの集計で、毎日・毎週・毎月の売り上げを個々の製品ごとに集計したいのですが、
どのような関数になるのか、教えて頂けませんか？

例　チョコレートとおせんべいの二つの商品がありまして、それぞれの毎日・毎週・毎月の売り上げを
　　表に集計したいです。

回答No.6

kagakusuki

回答番号:ANo.4、5です。
　月毎の集計はSheet4(2)で行います。

　まず、Sheet4のコピーシートを作成して下さい。（シート名はSheet4(2)になる筈です）
　次に、Sheet4(2)のA1セルの内容を、「週集計」から「月集計」に変更して下さい。
　次に、Sheet4(2)のE1セルを選択して、Sheet1のB3セルと同様のやり方で、ドロップダウンリストの設定を行い、「元の値」欄の内容を、

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,末日

に変更して下さい。
　次に、Sheet4(2)のA4セルに入力されている数式を、次の数式に変更して下さい。

=IF($C4="","",IF(DATE(YEAR($C4),MONTH($C4)-(DAY($C4)<IF($E$1="末日",1,$E$1+1)),IF($E$1="末日",1,$E$1+1))<MIN(Sheet1!$A:$A),MIN(Sheet1!$A:$A),DATE(YEAR($C4),MONTH($C4)-(DAY($C4)<IF($E$1="末日",1,$E$1+1)),IF($E$1="末日",1,$E$1+1))))

　次に、Sheet4(2)のA4セルをコピーして、A5以下に貼り付けて下さい。

　最後に、Sheet4(2)のE1セルに月毎の〆日を、ドロップダウンリストから選択して入力して下さい。

　後は、Sheet1の
A列に商品が売れた日付を、1行毎に、年月日が揃った形式
（例えば

2010/1/1
2010年01月01日
H22.1.1
平成22年1月1日

等々）
で入力し、
B列に売れた商品の名称
C列に売れた数量
を入力して行くだけで、
自動的に、日毎、週毎、月毎の集計結果が、Sheet3、Sheet4、Sheet4(2)に表示されます。
　尚、日付の順序が上から下へ向かって、古い日付から新しい日付の順に並んでいないと、正しく集計されない事がありますので、御注意願います。

回答No.5

kagakusuki

回答番号:ANo.4です。
　週毎の集計はSheet4で行います。
　まず、Sheet4の
A1セルに「週集計」
C3セルに「小計」
と入力して下さい。
　次に、Sheet4のE1セルを選択して、Sheet1のB3セルと同様のやり方で、ドロップダウンリストの設定を行い、「元の値」欄には、

月,火,水,木,金,土,日

と入力して下さい。
　次に、Sheet3のG1～H1の範囲をコピーして、Sheet4のG1～H1の範囲に貼り付けて下さい。
　次に、Sheet3のB3セルをコピーして、Sheet4のD3セルに貼り付けて下さい。
　次に、Sheet4のD2セルに次の数式を入力して下さい。（Sheet3のB2セルの数式を基にすると入力が楽です）

=IF(INDEX(Sheet2!$A:$A,COLUMNS($A:C))="","",INDEX(Sheet2!$A:$A,COLUMNS($A:C)))

　次に、Sheet4のA4セルに次の数式を入力して下さい。

=IF($C4="","",IF($C4-WEEKDAY($C4)+LOOKUP($E$1,{"火","金","月","水","土","日","木";4,7,3,5,8,2,6})-7*(WEEKDAY($C4)<=LOOKUP($E$1,{"火","金","月","水","土","日","木";3,6,2,4,7,1,5}))<MIN(Sheet1!$A:$A),MIN(Sheet1!$A:$A),$C4-WEEKDAY($C4)+LOOKUP($E$1,{"火","金","月","水","土","日","木";4,7,3,5,8,2,6})-7*(WEEKDAY($C4)<=LOOKUP($E$1,{"火","金","月","水","土","日","木";3,6,2,4,7,1,5}))))

　次に、Sheet4のB4セルに次の数式を入力して下さい。

=IF($C4="","","～")

　次に、Sheet4のC4セルに次の数式を入力して下さい。

=IF(COUNT(Sheet1!$A:$A)=0,"",MAX(Sheet1!$A:$A))

　次に、Sheet4のD4セルに次の数式を入力して下さい。

=IF(OR(C$4="",D$2=""),"",SUMPRODUCT((INDIRECT("Sheet1!A"&MATCH($A4,Sheet1!$A:$A,0)&":A"&MATCH($C4,Sheet1!$A:$A))>=$A4)*(INDIRECT("Sheet1!A"&MATCH($A4,Sheet1!$A:$A,0)&":A"&MATCH($C4,Sheet1!$A:$A))<=$C4)*(INDIRECT("Sheet1!B"&MATCH($A4,Sheet1!$A:$A,0)&":B"&MATCH($C4,Sheet1!$A:$A))=D$2)*VALUE("0"&INDIRECT("Sheet1!E"&MATCH($A4,Sheet1!$A:$A,0)&":E"&MATCH($C4,Sheet1!$A:$A)))))

　次に、Sheet4のA5セルに次の数式を入力して下さい。

=IF($C5="","",IF(A4-7<MIN(Sheet1!$A:$A),MIN(Sheet1!$A:$A),A4-7))

　次に、Sheet4のC5セルに次の数式を入力して下さい。

=IF(OR($A4="",$A4=MIN(Sheet1!$A:$A)),"",$A4-1)

　次に、Sheet4のB4セルをコピーして、B5セルに貼り付けて下さい。
　次に、Sheet4のD4セルをコピーして、D5セルに貼り付けて下さい。
　次に、Sheet4のD3～D5の範囲をコピーして、同じ行のE列から右方向に向かって貼り付けて下さい。
　次に、Sheet4の5行目全体をコピーして、6行目以下に貼り付けて下さい。

　最後に、Sheet4のE1セルに週毎の〆曜日を、ドロップダウンリストから選択して入力して下さい。

　これで週毎の集計が可能になります。
　月毎の集計に関しては、後で回答させて頂きます。

回答No.4

kagakusuki

まず、Sheet2の
A1セルに「単価リスト」
A2セルに「商品名」
B2セルに「単価」
と入力して下さい。
　次に、Sheet2のA列の4行目から下に向かって、「チョコレート」や「おせんべい」等の商品名を入力して行って下さい。
　次に、Sheet2のB列の4行目から下に向かって、A列に記した商品の単価を入力して下さい。

　次に、Sheet1の
A1セルに「販売記録」
A2セルに「日付」
B2セルに「商品名」
C2セルに「数量」
D2セルに「単価」
E2セルに「売上」
と入力して下さい。
　次に、Sheet1のD3セルに次の数式を入力して下さい。

=IF(COUNTIF(Sheet2!$A:$A,$B3)>0,VLOOKUP($B3,Sheet2!$A:$B,2,0),"")

　次に、Sheet1のE3セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER($C3),ISNUMBER($D3)),$C3*$D3,"")

　次に、Sheet1のB3セルをクリックしてから、以下の操作を行って、ドロップダウンリストを設定して下さい。

メニューの［データ］ボタンをクリック
　　↓
現れた選択肢の中にある［入力規則］をクリック
　　↓
現れた「データの入力規則」ウィンドウの［設定］タグをクリック
　　↓
「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある「リスト」をクリック
　　↓
「元の値」欄に次の数式を入力

=INDIRECT("Sheet2!A3"&MATCH(9^9,INDIRECT("Sheet2!B:B")))

　　↓
「データの入力規則」ウィンドウの［OK］ボタンをクリック

　次に、Sheet1のB3～E3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

　次に、Sheet3の
A1セルに「日集計」
G1セルに「総計」
A3セルに「小計」
と入力して下さい。
　次に、Sheet3のH1セルに次の数式を入力して下さい。

=SUM($3:$3)

　次に、Sheet3のA2セルに次の数式を入力して下さい。

=IF(INDEX(Sheet2!$A:$A,COLUMNS($A:B))="","",INDEX(Sheet2!$A:$A,COLUMNS($A:B)))

　そして、Sheet3のA2セルをコピーして、同じ行のB列から右方向に向かって貼り付けて下さい。
　次に、Sheet3のB3セルに次の数式を入力して下さい。

=IF(B$2="","",SUM(B$4:B$65536))

　次に、Sheet3のA4セルとA5セルに、一旦、「2000/1/1」等の適当な日付を入力して下さい。
　次に、再度Sheet3のA4セルを選択して、次の数式を入力して下さい。

=IF(COUNT(Sheet1!$A:$A)=0,"",MIN(Sheet1!$A:$A))

　次に、再度Sheet3のA5セルを選択して、次の数式を入力して下さい。

=IF(OR(A$4="",MAX(A$4:A4)=MAX(Sheet1!$A:$A)),"",Sheet3!A4+1)

　次に、Sheet3のB4セルに次の数式を入力して下さい。

=IF(OR(A$4="",B$2=""),"",IF(COUNTIF(Sheet1!$A:$A,$A4)=0,0,SUMPRODUCT((INDIRECT("Sheet1!A"&MATCH($A4,Sheet1!$A:$A,0)&":A"&MATCH($A4,Sheet1!$A:$A))=$A4)*(INDIRECT("Sheet1!B"&MATCH($A4,Sheet1!$A:$A,0)&":B"&MATCH($A4,Sheet1!$A:$A))=B$2)*VALUE("0"&INDIRECT("Sheet1!E"&MATCH($A4,Sheet1!$A:$A,0)&":E"&MATCH($A4,Sheet1!$A:$A))))))

　次に、Sheet3のB4セルをコピーして、B5セルに貼り付けて下さい。
　次に、Sheet3のB3～B5の範囲をコピーして、同じ行のC列から右方向に向かって貼り付けて下さい。
　次に、5行目全体をコピーして、6行目以下に貼り付けて下さい。

　これで日毎の集計が可能になります。
　週毎や月毎の集計に関しては、後で回答させて頂きます。

回答No.3

KURUMITO

例えば次の表のようにデータが入力されているとします。
Ａ2セルに日付の項目名を入力し、Ａ3セルから下方には10/1のように入力します。当年以外になる場合には2011/1/5のように年を付けて入力します。
Ｂ2セルにはチョコレート、Ｃ2セルにはおせんべいと入力し、下方には売上数などを入力します。
Ｄ列とＥ列は作業列としてＤ3セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A3="","",YEAR(A3)&WEEKNUM(A3))

この列は毎週の売り上げを集計するために使用する作業列です。12月と1月にまたがる週については計算が複雑となりますのでそれぞれの年で違った週として集計をすることになりますが、その他の日付などの場合には問題無く対応できます。

Ｅ3セルには次の式を入力して下方いオートフィルドラッグします。

=IF(A3="","",YEAR(A3)&MONTH(A3))

この列は月毎の集計に必要な作業列です。

Ｆ1セルとＧ1セルは結合セルとして毎日と入力します。また、Ｆ2セルにはチョコレート、Ｇ2セルにはおせんべいと入力します。
Ｆ3セルには次の式を入力してＧ3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(A3="","",IF(COUNTIF($A$3:$A3,$A3)=1,SUMIF($A:$A,$A3,B:B),""))

Ｈ1セルとＩ1セルは結合セルとして毎週と入力します。Ｈ2セルにはチョコレート、Ｉ2セルにはおせんべいと入力します。
Ｈ３セルには次の式を入力してＩ3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF($A3="","",IF(COUNTIF($D$3:$D3,$D3)=1,SUMIF($D:$D,$D3,B:B),""))

同様にＪ，Ｋ列については毎月、でＪ2セルにはチョコレート、Ｋ2セルにはせんべいと入力します。
Ｊ3セルには次の式を入力してＫ3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF($A3="","",IF(COUNTIF($E$3:$E3,$E3)=1,SUMIF($E:$E,$E3,B:B),""))

これらの操作で毎日、毎週、毎月の売り上げが表示されることになります。

回答No.2

edomin7777

> どのような関数になるのか、教えて頂けませんか？

どのような関数になるかは、シートにどのようにデータが入っているかが判らないと判りません。

SUMIF
IF

で、出来ると思います。

回答No.1

yama1718

そういうのはピボットテーブルを使って集計できます。
ネットの検索で "Excel ピボットテーブル" で検索すると
色々と説明されたホームページが見つかります。