Excelで日付別の集計を取るやり方

質問

実際の内容とは違いますが例えとして、
添付の画像のように、A列に日付が並んでいます。注文が入り次第下に追記していくので日にちはランダムです。
B列に商品名がそれぞれ並びます。

ここからが質問で、
上記シートの隣に集計用シートを作りたいと思っています。
集計用シートでは、日付が1列にカレンダーのように並んでいて、そこに商品の数を拾って出したいと思っています。
縦軸にカレンダー（日付）
横軸にA,B,C,D
それぞれ日毎の数量をカウントするようにしたいです。

どのような関数、操作になりますでしょうか？
初級者のため、易しく教えていただければ幸いです。
よろしくお願いします。

回答No.1

nishi6 · ベストアンサー

データ内をクリックしておいて、挿入タブ＞ピボットテーブルを選択し、ピボットテーブルの選択ダイアログボックスでOK。
ピボットテーブルのフィールドリストで、希望納期を行ラベルにドラッグ、商品を列ラベルにドラッグ商品をΣ値にドラッグします＞データの個数になるはずです。
これで完成です。関数は気が遠くなります。
当方、win10、Excel2010です。

お礼

ピポットテーブルは聞いたことはありましたが、こういう便利なものだったのですね。
求めていたものでした。ありがとうございました。

回答No.4

imogasi

ピボットテーブル利用などを勧めるが、
COUNTIFS関数を使う回答が出ていないようなので書いてみる。
ーー
例データ
Sheet1　A1:B13
希望納期 商品名
10月10日 A
11月7日 B
10月10日 B
11月7日 C
11月8日 A
10月21日 D
11月14日 C
11月5日 C
11月5日 A
11月5日 A
11月5日 A
10月21日 B
ーーー
Sheet2
第１行目のA1:E1の各列に、それぞれに　
日付 A B C D
を入力。
A2セルに10/1 (始期の日付）を入れて、A2セルの＋ハンドルを１１月末まで引っ張る。１１月までしか受注日付がない場合を仮定。
これでA列に１0月と１１月の日付が（毎日分）出る。
ーー
関数式
Sheet2のB２セルに式
=COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$B:$B,B$1)
＄の有無と入れる場所を正確に、間違えないように。
と入れてE列までと、次に最下行まで式を複写する。
ーー
結果（一部行抜粋）
10月10日 1 1 0 0
10月21日 0 1 0 1
ーー
＞初級者のため、易しく教えていただければ幸いです。
こんなところへ質問する前に、エクセルの関数の本（千円以下の本もある、でも）買って事前に読むべきだ。
基礎的なことを勉強してから、後の疑問なら、このコーナーは（ある程度ベテランが回答するので)役立つだろうが、隣の同僚に聞いても判るような質問なら、この場を生かせない。
エクセル関数の代表的な関数の２０個ぐらいは、その名称と使う場合と引数の意味ぐらいは暗記すること。
（１）上記データで日付は日付シリアル値を使うこと。日付シリアル値とはないか分かっているか。文字列の日付は不可。
（２）他のシートのデータを参照するときのやり方。
（３）式の番地の前に着ける「＄」の理解と、使い方。付けてはダメな場合と。
（４）式の複写方法（数種ある）

回答No.3

nishi6

算式で対応してみました。
ア．A列の日付をコピーし、D列に貼り付けます。E列には入力が無いとします。
イ．D列の日付の一つをクリックして、データを昇順に並べ替えます。
ウ．そのまま、データタブ＞重複の削除を行います。重複の削除ダイアログ
　　でOKします。D列の重複が削除されます。
エ．セルE1="A"、セルF1="B"、セルG1="C"、セルH1="D"　を入力します。
オ．セルE2に、=SUMPRODUCT(($A$2:$A$13=$D2)*($B$2:$B$13=E$1))
　　と入力します。$A$13、$B$13の「13」はデータの最終行に当たります。
　　実際のデータに合わせてください。
カ．この算式をセルE2:H7にコピーします。
　　コピーの仕方は、セルE2に算式を入力した後、そのままセルE2:H7を
　　選択し、ファンクションキーF2で編集モードにし、Ctrlキー+Enterキー
　　とすれば速いでしょう。必ずこうする必要はありません。

算式の意味は、Sumproduct関数を使って、表側と表頭に一致する個数を数えています。表側と表題に一致すれば、「1」が帰ってくるので、それを加算しています。「$」を使って、コピーしても表題と表側が動かないようにしています。

関数を使いたくないのは、日付が増えた時（減ったときも）、同じ操作が必要になるからです。最終行を自動的に感知するようにできなくもないですが、算式が複雑になるのと、質問の主旨から外れると思うので省略します。（忘れたのが本音です）

前回回答のピボットも更新については、同じ様な意味合がありますが、元データを挿入タブ＞テーブルで「テーブル」として扱えば、ピボットの更新はかなり楽になります。

ご参考に。

回答No.2

kon555

ピボットは既に回答済みなのでそれ以外で。
ちょっと煩雑ですが、テンプレートを作ってしまえば何とでもなります。
A列が希望納期、B列が商品、C列が個数の列として、D列を総数、E列を判定用という2つの作業用列として使用します。

・通常操作のみ
1.並び替え
「希望納期」枠を最優先、「商品」を次優先に指定して昇順で並び替えます
これでランダムに入力された日付けが整頓され、各日付け順かつ各商品順に並びます。
https://121ware.com/qasearch/1007/app/servlet/relatedqa?QID=016326

2.if関数で総数算出
D列のセルにif関数を使って、『「希望納期」と「商品」が1つ上と同一の場合、「数量」を1つ上のセルと足す』という条件を入れます
「=IF(B2=B1,IF(A2=A1,C2+D1,C2),C2)」という数式をD2に入れて、後は一番下の行までコピーすればいいです。
これでD列には、日付けと商品名が一致した場合の合計数が順に表示されます。
https://next.rikunabi.com/journal/20170701_s02/

3.if関数で判定用
E列のセルに、さっきと同様にif関数を使って『「希望納期」と「商品」が1つ下と同一の場合は0、どちらかが違う場合は1を表示する」という関数を組みます。
「=IF(B3=B2,IF(A3=A2,0,1),1)」という数式をE2に入れて、後は一番下の行までコピーすればいいです。
これでE列では、日付けと商品が一致している最下行に「1」が、それ以外の行に「0」が表示されます。

4.フィルタ機能で判定用列をフィルタ【完了】
E列をフィルタし、「1」が入っているセルのみ表示します。
この状態で表示された列は、『日付と商品順で整列されており、かつ双方が一致している最下行』なので、D列に入っている総数がそのまま日毎の数量カウントになります。

あまり行儀のいい使い方ではないですが、日付順のデータなどでは、この「日付が同じなら数を足して行く」と「日付違ったら判定数値」の組み合わせは地味に使えます。
ピボットなり何なりで間に合うケースも多いのですが、まあ1つの方法としては覚えておいても損はないかと。
ご参考までに。