Excel関数

Excel関数

表1 お菓子類販売数
　A列　 B列　　　C列　 D列　　E列・・・・Q列
1 メーカー名　商品名　部門　 1月　2月・・・・合計
2 つばめ すっきり ジュース 350 250・・・・4500
3 つばめ まろやか ガム 214 205・・・・2840
4 つばめ げんき ガム 247 198・・・・3120
5 つばめ いちご ジュース 360 380・・・・5240
6 すずめ みかん ジュース 320 215・・・・4162
7 すずめ あまあま ジュース 284 178・・・・3968
8 すずめ しゃき ガム 183 216・・・・2963
9 すずめ グリーン ガム 169 209・・・・2834
10 はと れもん ジュース 217 301・・・・4295
11 はと かるい ガム 238 240・・・・3657
12 はと すうすう ガム 199 236・・・・3102

上記の表がシート1にあります。
そして、シート2に下記の2つの表を作成しなければなりません。
表2 部門別販売数No.1
A列 B列　 C列 D列
1部門名 メーカー名 商品名 合計
2ジュース
3ガム

表3 メーカー別販売数ベスト3
A列 B列　 C列 D列
1メーカー名　1　　2 3
2つばめ
3すずめ
4はと

表2はジュース・ガムの部門別のトップ販売数の商品名とメーカー名、合計（表1のQ列）を表示させます。
表3は部門に関係なくメーカー別に販売数ベスト3商品名を表示させます。

どのように関数式を作ったらいいのでしょう？
両方ともINDEX、MATCH、LARGE関数を使うのかなあと漠然と考えるのですが・・・
表3はメーカーの検索範囲をどのように指定するのかが特にわかりません。
表2はうまく考えがまとまりません・・・

どなたか教えて下さい！よろしくお願いします。

No.5 ベストアンサー 回答者： mike_g 回答日時： 2010/06/17 17:56

D15: =SUMPRODUCT(MAX((C$2:C$12=A15)*(Q$2:Q$12)))

B15: =INDEX(A$2:A$12,MATCH($D15,$Q$2:$Q$12,0))
B19: =INDEX($B$2:$B$12,MATCH(SUMPRODUCT(LARGE(($A$2:$A$12=$A19)*($Q$2:$Q$12),B$18)),$Q$2:$Q$12,0))

この回答へのお礼

ありがとうございました。
非常に参考になりました。

お礼日時：2010/06/19 15:11

No.8 回答者： HRI-Sankou 回答日時： 2010/06/18 16:56

すみません　先ほどの計算式では、合計が同じ場合の事を

考慮していませんでした。
先ほどの式を入力後修正してみてください。
Sheet1 のＳ列より右側に修正を行います。
つばめ列の右に３列　列を追加する
すずめ列の右に３列　列を追加する
はと　列の右に３列　列を追加する
Ｔ２セル　=IF(S$17=ROWS(T$2:T2),0,S2)
先ほど追加した列全ての２行目から１２行目に
（Ｔ２～Ｖ１２ ，Ｘ２～Ｚ１２ ，ＡＢ２～ＡＤ１２ ）
コピーする。
１６行から１７行を消去する。
Ｓ１５セルの式を Ｔ１５～ＡＤ１５ にコピーする。
Ｓ１７セルの式を Ｔ１７～ＡＤ１７ にコピーする。

Sheet2
Ｃ９セルの　$S$17 部分を　S17　に修正する。
Ｃ９セルの式を、Ｄ９ ，Ｅ９　にコピーする。
Ｃ１０セルの　$W$17 部分を　W17　に修正する。
Ｃ１０セルの式を、Ｄ１０ ，Ｅ１０　にコピーする。
Ｃ１１セルの　$AA$17 部分を　AA17　に修正する。
Ｃ１１セルの式を、Ｄ１１ ，Ｅ１１　にコピーする。

もし、３位が、２商品存在していた場合にも
対応する必要が有るならば、

Sheet1
Ｖ１８セル　=IF(U15=V15,1,0)
Ｖ１８セルの式を、Ｚ１８ ，ＡＤ１８　にコピーする。

Sheet2
Ｆ８セル　=IF(SUM(Sheet1!$S$18:$AE$18)>=1,E8,"")
Ｆ９セル　=IF(Sheet1!V18>=1,INDEX(Sheet1!$B$2:$Q$12,Sheet1!V17,2,1),"")
Ｆ１０セル　=IF(Sheet1!Z18>=1,INDEX(Sheet1!$B$2:$Q$12,Sheet1!Z17,2,1),"")
Ｆ１１セル　=IF(Sheet1!AD18>=1,INDEX(Sheet1!$B$2:$Q$12,Sheet1!AD17,2,1),"")

同じ様な事を、部門別でも必要ならば、同じ考え方で対応出来ると思います。
（注意：変更部分のチェックが一部分のみですので
　ファイルをコピーしてから行ってみて下さい。）
考えかた
デターをメーカー　及び　部門　毎に分割
それぞれのデーターを、上位から下位に選択
（１位のデーターを ０ にして２位のデーターを選択）
（２位のデーターを ０ にして３位のデーターを選択）
（３位のデーターを ０ にして４位のデーターを選択）
（これにより同位が複数あっても対応する）
選択データーの列位置を呼び出す
列位置から必要行のデーターを表示させる
追加部分
３位のデーターが、２データーあった場合
そのデーターも表示させる

この回答へのお礼

ありがとうございました。
ただ、作業列を使用できないので、今回は他の方の回答を参考にさせていただきました。
今後の参考にさせていただきます。

お礼日時：2010/06/19 15:13

No.7 回答者： HRI-Sankou 回答日時： 2010/06/18 13:29

ちょっと面倒くさい方法ですが、

全体としては、単純だと思いますので参考にしてみて下さい。
Sheet1 の B2 が　つばめ　～　Q12 が　3102　のデーターが入力されている条件です。

Sheet1 に以下の式を入力する。
Ｓ１セル　つばめ
Ｔ１セル　すずめ
Ｕ１セル　はと
Ｗ１セル　ジュース
Ｘ１セル　ガム
Ｓ２セル　=IF(S$1=$B2,$Q2,0)
Ｓ２の式を、Ｓ２ から Ｕ１２　にコピーする。
Ｗ２セル　=IF(W$1=$D2,$Q2,0)
Ｗ２の式を、Ｗ２ から Ｘ１２　にコピーする。
Ｓ１５セル　=LARGE(S$2:S$12,1)
Ｓ１５の式を、Ｓ１５ から Ｕ１７　と　Ｗ１５ ，Ｘ１５　にコピーする。
Ｓ１６セル　=LARGE(S$2:S$12,2)
Ｓ１６の式を、Ｔ１６ ，Ｕ１６　にコピーする。
Ｓ１７セル　=LARGE(S$2:S$12,3)
Ｓ１７の式を、Ｔ１７ ，Ｕ１７　にコピーする。
Ｓ１９セル　=MATCH(S15,S$2:S$12,0)
Ｓ１９の式を、Ｓ１９ から Ｕ２１ と　Ｗ１９ ，Ｘ１９　にコピーする。

Sheet2 の Ｃ２ に メーカー名　が入力されているとする。
Sheet2 に以下の式を入力する。
Ｂ３セル　ジュース
Ｂ４セル　ガム
Ｃ３セル　=INDEX(Sheet1!$B$2:$Q$12,Sheet1!$W$19,1,1)
Ｄ３セル　=INDEX(Sheet1!$B$2:$Q$12,Sheet1!$W$19,2,1)
Ｅ３セル　=INDEX(Sheet1!$B$2:$Q$12,Sheet1!$W$19,16,1)
Ｃ４セル　=INDEX(Sheet1!$B$2:$Q$12,Sheet1!$X$19,1,1)
Ｄ４セル　=INDEX(Sheet1!$B$2:$Q$12,Sheet1!$X$19,2,1)
Ｅ４セル　=INDEX(Sheet1!$B$2:$Q$12,Sheet1!$X$19,16,1)

Ｃ８セル　１位
Ｄ８セル　２位
Ｅ８セル　３位
Ｂ９セル　つばめ
Ｂ１０セル　すずめ
Ｂ１１セル　はと
Ｃ９セル　=INDEX(Sheet1!$B$2:$Q$12,Sheet1!$S$19,2,1)
Ｄ９セル　=INDEX(Sheet1!$B$2:$Q$12,Sheet1!$S$20,2,1)
Ｅ９セル　=INDEX(Sheet1!$B$2:$Q$12,Sheet1!$S$21,2,1)
Ｃ１０セル　=INDEX(Sheet1!$B$2:$Q$12,Sheet1!$T$19,2,1)
Ｄ１０セル　=INDEX(Sheet1!$B$2:$Q$12,Sheet1!$T$20,2,1)
Ｅ１０セル　=INDEX(Sheet1!$B$2:$Q$12,Sheet1!$T$21,2,1)
Ｃ１１セル　=INDEX(Sheet1!$B$2:$Q$12,Sheet1!$U$19,2,1)
Ｄ１１セル　=INDEX(Sheet1!$B$2:$Q$12,Sheet1!$U$20,2,1)
Ｅ１１セル　=INDEX(Sheet1!$B$2:$Q$12,Sheet1!$U$21,2,1)

データー数が多い場合には、列、行 を挿入してみて変化する部分を
確認して変更を行ってみて下さい。

No.6 回答者： imogasi 回答日時： 2010/06/17 22:30

配列数式でも出来るのでやってみた。

例データ
A列B列C列D列E列・・・・Q列
1メーカー名商品名部門1月2月合計
2すずめグリーンガム169?2092834
4すずめしゃき??ガム183?2162963
8すずめあまあまジュース284?1783968
9すずめみかん??ジュース320?2154162
3つばめまろやかガム214?2052840
6つばめげんき??ガム247?1983120
11つばめすっきりジュース350?2504500
12つばめいちご??ジュース360?3805240
5はと??すうすうガム199?2363102
7はと??かるい??ガム238?2403657
10はと??れもん??ジュース217?3014295
D16:E２４に下記を用意する
ガム3657
ジュース5240

すずめ14162
すずめ23968
すずめ32963
つばめ15240
つばめ24500
つばめ33120
F16に　
=MAX(IF($D$3:$D$14="ガム",$I$3:$I$14,""))と入れてSHIFT+CTRL+ENTERの３つのキーを同時押し。
F17へは式の複写.

F19に
=LARGE(IF($B$3:$B$13=D19,$I$3:$I$13,0),E19)と入れてSHIFT+CTRL+ENTERの３つのキーを同時押し。
F２４まで式複写。
知る紋では順位を横に流しているが、順位をColumn()－１のようにすれば返られる。
ーー
文字は完全位置で無いと結果がおかしくなる。
こちらでは、WEB本質問のデータをコピーし区切り位置で分離すると、「つばめ」のあとにスペースが付いて、旨く結果が出ず手間取った。注意。
ー
もうそろそろエクセル関数では限界で、アクセスのSQLを使うとか、VBAでも使うとかする時期になっているかと思いますよ。

この回答へのお礼

ありがとうございました。
今回はNO.5さんの回答を参考にさせていただきました。
今後の参考にさせていただきます。

お礼日時：2010/06/19 15:15

No.4 回答者： MackyNo1 回答日時： 2010/06/17 16:56

Sheet2には以下の式を入力します。

D2セル

=MAX(INDEX((Sheet1!$C$2:$C$20=A2)*(Sheet1!$Q$2:$Q$20),))

B2セルに以下の式を入力し右方向に１つ、下方向にオートフィルします。

=INDEX(Sheet1!A$2:A$20,MATCH($D2,Sheet1!$Q$2:$Q$20,0))

Sheet3のB2セルに以下の式を入力し、右方向に３つ、下方向にオートフィルします。

=INDEX(Sheet1!$B$2:$B$20,MATCH(LARGE(INDEX((Sheet1!$A$2:$A$20=$A2)*Sheet1!$Q$2:$Q$20,),COLUMN(A1)),Sheet1!$Q$2:$Q$20,))

この回答へのお礼

ありがとうございました。
今回は、ANo.5さんの回答をさんこうにさせていただきました。
今後の参考にさせていただきます。

お礼日時：2010/06/19 15:17

No.3 回答者： junichihirobe 回答日時： 2010/06/17 16:46

別に関数でやらないでも

ピボットテーブルで処理すれば簡単だと思いますが・・。

この回答へのお礼

そうですよね・・・
ただ、関数を使用する指示が出てたもので・・・
ありがとうございました。

お礼日時：2010/06/19 15:18

No.2 回答者： KURUMITO 回答日時： 2010/06/17 15:47

かなり面倒な作業になりますが次のようにすればよいでしょう。

表1がシート1に表2がシート2に表3をシート3に作るとしてシート1では1行目を項目名などにしてデータは2行目から下方に有るとします。
シート1ではいくつかの作業列を設けます。
Ｒ1セルには部門別順位とでも入力してからＲ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",SUMPRODUCT((($C$2:$C$1000=C2)*$Q$2:$Q$1000>Q2)*1)+1)

Ｓ1セルには部門区分とでも入力してからＳ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(R2="","",IF(R2=1,MAX(S$1:S1)+1,""))

Ｔ1セルにはメーカー別順位とでも入力してからＴ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",SUMPRODUCT((($A$2:$A$1000=A2)*$Q$2:$Q$1000>Q2)*1)+1)

Ｕ1セルにはメーカー区分とでも入力してからＵ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",IF(A1<>A2,MAX(U$1:U1)+1,IF(A1=A2,U1,"")))

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

=IF(U2="","",U2&"/"&T2)

以上でシート1での作業は終了して、シート2ではＡ1セルに部門別販売数Ｎｏ1とでも入力します。
Ａ2セルに部門名、Ｂ2セルにメーカー名、Ｃ2セルに商品名、Ｄ2セルに合計とでも入力します。

Ａ3セルには次の式を入力してＤ3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNTIF(Sheet1!$S:$S,ROW(A1))=0,"",IF(COLUMN(A1)=1,INDEX(Sheet1!$C:$C,MATCH(ROW(A1),Sheet1!$S:$S,0)),IF(COLUMN(A1)=2,INDEX(Sheet1!$A:$A,MATCH(ROW(A1),Sheet1!$S:$S,0)),IF(COLUMN(A1)=3,INDEX(Sheet1!$B:$B,MATCH(ROW(A1),Sheet1!$S:$S,0)),IF(COLUMN(A1)=4,INDEX(Sheet1!$Q:$Q,MATCH(ROW(A1),Sheet1!$S:$S,0)),"")))))

シート3ではＡ1セルにメーカー別販売数ベスト3とでも入力します。
Ａ2セルにはメーカー名、Ｂ2セルには１、Ｃ2セルには２、Ｄ2セルには3と入力します。
Ａ3セルには次の式を入力したのちにＤ3セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNTIF(Sheet1!$U:$U,ROW(A1))=0,"",IF(COLUMN(A1)=1,INDEX(Sheet1!$A:$A,MATCH(ROW(A1),Sheet1!$U:$U,0)),IF(AND(COLUMN(A1)>=2,COLUMN(A1)<=4),INDEX(Sheet1!$B:$B,MATCH(ROW(A1)&"/"&COLUMN(A1)-1,Sheet1!$V:$V,0)),"")))

この回答へのお礼

ありがとうございました。
今回は作業列を使用できないので、ANo5さんの回答を参考にさせていただきました。
今後の参考にさせていただきます。

お礼日時：2010/06/19 15:20

No.1 回答者： CoalTar 回答日時： 2010/06/17 15:44

↓合計に重複があったら、使えません。

B15:D15セル範囲を選択して
=INDEX(A:Q,MIN(IF(MAX(IF($C$2:$C$12=$A15,$Q$2:$Q$12))=$Q$2:$Q$12,ROW($Q$2:$Q$12))),{1,2,17})
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式です({}で囲まれる)

B19セルに
=INDEX($B:$B,MIN(IF(LARGE(IF($A$2:$A$12=$A19,$Q$2:$Q$12),B$18)=($A$2:$A$12=$A19)*$Q$2:$Q$12,ROW($Q$2:$Q$12))))
[Ctrl]+[Shft] ＋[Enter] で確定)

エラー処理もしていません。
重複があった場合はどのように処理するのでしょうか？
作業列を使っても良いでしょうか？

この回答へのお礼

ありがとうございました。
今回は作業列を使用できなかったので、ANo.5さんの回答を参考にさせていただきました。
今後の参考にさせていただきます。

お礼日時：2010/06/19 15:21