エクセルで、できますか？

質問

1 佐藤 みかん 300
2 佐藤 りんご 300
3 山田 みかん 300
4 石井 みかん 300
5 石井 りんご 300
6 足立 みかん 300
7 山田 みかん 300
8 甲斐 りんご 300
9 甲斐 みかん 300

以上のデータがあります。
みかんだけ買った人の人数、金額
りんごだけ買った人の人数、金額
両方とも買った人の人数、金額
を、わりだす方法を教えて下さい。

回答No.2

zap35 · ベストアンサー

このように複数の行にまたがる条件判定を１つの関数で行うのは難しいと思います。そこで一旦ピボットテーブルでクロス集計を行って、その集計表に対して関数を用いるのが良いと思います。

ピボットテーブルで以下のような集計表ができます（１行目はタイトル、２行目からデータとする）
Ａ列　　　Ｂ列　　　Ｃ列　　Ｄ列　　
氏名　　みかん　りんご　　総計
甲斐　　300　　　300　　　　600
佐藤　　300　　　300　　　　600
山田　　600　　　　　　　　　600
石井　　300　　　300　　　　600
足立　　300　　　　　　　　　300
総計 1800　　　900　　　2700

みかんだけ買った人数
　=SUMPRODUCT(($B$2:$B$6<>"")*($C$2:$C$6=""))
みかんだけ買った人の金額
　=SUMPRODUCT(($B$2:$B$6<>"")*($C$2:$C$6="")*($D$2:$D$6))
りんごだけ買った人数
　=SUMPRODUCT(($B$2:$B$6="")*($C$2:$C$6<>""))
りんごだけ買った人の金額
　=SUMPRODUCT(($B$2:$B$6="")*($C$2:$C$6<>"")*($D$2:$D$6))
両方買った人数
　=SUMPRODUCT(($B$2:$B$6<>"")*($C$2:$C$6<>""))
両方買った人の金額
　=SUMPRODUCT(($B$2:$B$6<>"")*($C$2:$C$6<>"")*($D$2:$D$6))
となります。

もし実際のデータは商品数がもっと多いのであれば、もう一捻りいるかもしれませんが…

お礼

詳しくありがとうございました。
思ってたことができそうです

回答No.6

zap35

まずピボットテーブルを利用するには１行目にタイトルを入れなければなりません。Ａ～Ｃ列の１行目に行を挿入して氏名、商品、金額とタイトルを入れます。

集計表を作成したい適当なセルを選択（クリック）して、「データ」→「ピボットテーブルとピボットグラフレポート」→ウィザード１／３が表示される。ここは何も指定せず「次へ」

ウィザード２／３で使用するデータ範囲（タイトル行と氏名、商品、金額のデータ全て）を選択して「次へ」をクリック

ウィザード３／３で「レイアウト」ボタンをクリック
すると右側に「氏名」「商品」「金額」のボタンが並んでいるので、「氏名」を左側の「行」のエリアにドラッグしてはなす。「商品」は「列」のエリアにドラッグ。「金額」は「データ」のエリアに同様にドラッグ。そして「ＯＫ」

ウィザード３／３に戻り「完了」で集計表が作成されます。

なお使ったことがなくてもヘルプを見て少し試してから「ここが分からない」と補足していただく方が、気持ちが良いです…

回答No.5

telescope

ピボットテーブルで簡単に出来ます。

まず、元の表にタイトル行を挿入します。
1 名前　品名　金額
2 佐藤 みかん 300
3 佐藤 りんご 300
4 山田 みかん 300
5 石井 みかん 300

メニューバーの「データ」－「ピボットテーブル～」を選択します。
「ピボットテーブルウィザード」が立ち上がります。
特に設定するところもありませんので、「完了」ボタンを押します。

「行のフィールド～」に「品名」をドラッグします。
「データアイテム～」に「金額」をドラッグします。
もう一度、「金額」を「データアイテム～」にドラッグします。

品名 データ 計
みかん 合計 : 金額 1800
合計 : 金額2 1800
りんご 合計 : 金額 900
合計 : 金額2 900
全体の 合計 : 金額 2700
全体の 合計 : 金額2 2700

こんな表が出来上がります。
多分データのところが両方とも「合計：金額」になっていると思います。
「合計：金額2」を右クリックして、「フィールドの設定」で「集計の方法」を「データの個数」にします。
フィールド名を変更したければ、「名前」の欄を「人数」に直します。
「合計：金額」の方の名前を変更すると以下のような表になります。

品名 データ 計
みかん 金額 1800
人数 6
りんご 金額 900
人数 3
全体の 金額 2700
全体の 人数 　　　　　　　9

両方とも「データの個数：金額」になっているかも知れませんが、臨機応変に。

元の表のセルを一つだけ選択してから、「データ」－「ピボットテーブル～」を選択すると、ピボットテーブルウィザードが自動で範囲を検知します。
「次へ」ボタンを押した「ピボットテーブルウィザード2/3」に表示されます。
データが増える予定があるなら、この範囲を広げておきます。
（$A$1:$C$10　を　$A$1:$C$50　のように）
ピボットテーブルには「みかん」、「りんご」のほかに「（空白）」というデータが追加されます。
表示したくなければ、（空白）のところを右クリックで「表示しない」を選びます。

元の表に追加・訂正があったら、「ピボットテーブル」ツールバーの赤い「！」をクリックすると更新されます。

回答No.4

NCU

#1です。
ピボットから数字を計算する際に使った式を書いておきます。
（セル座標は #2さんのテーブルの例と同じで、結果をB9:D10に表示する場合を想定。）

みかんだけ買った人数 （セルB9）
　=SUMPRODUCT((B2:B6>0)*(C2:C6=0))
みかんだけ買った人の金額 （セルB10）
　=SUMPRODUCT(B2:B6*(C2:C6=0))

りんごだけ買った人数 （セルC9）
　=SUMPRODUCT((C2:C6>0)*(B2:B6=0))
りんごだけ買った人の金額 （セルC10）
　=SUMPRODUCT(C2:C6*(B2:B6=0))

両方買った人数 （セルD9）
　=COUNT(D2:D6)-SUM(B9:C9)
両方買った人の金額 （セルD10）
　=SUM(D2:D6)-SUM(B10:C10)

回答No.3

dec02

違う方法だと、
どこかのセルに
ＳＵＭＩＦ(＄Ｃ＄１＄Ｃ＄３０,Ｈ１,＄Ｄ＄１:＄Ｄ＄３０）として
Ｈ１に「みかん」を入力

Ｃ列は果物の種類
Ｄ列は金額
３０データある場合です。

みかんを買った人の人数は
【ＣＯＵＮＴＩＦ（＄Ｃ＄１:＄Ｃ＄３０，Ｈ１】

りんごも同様です。

リストが必要なら
まず、全部のセル列を指定
データ　→　フィルタ　→　オートフィルタ
それぞれの列に▼がつきますね。

果物名のところで「みかん」をセレクトします。

両方の場合は「空白以外のセル」を選びます。

回答No.1

NCU

ピボットで一旦金額データを展開すれば以下は簡単に計算できますが。

みかんだけ: 2名 900円
りんごだけ: 0名 　0円
両　　　方: 3名 1800円

補足

早速にありがとうございます。
ピボットテーブルを利用したことがないんで、
もう少し詳しく教えていただけませんか？