[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『○○以上○○未満』（マッキー）
以下の表①を参考に表②の集計表を作っているんですが
関数がわからずとても困っています。
表①
Ａ Ｂ Ｃ Ｄ Ｅ
| 4月 | 5月 | 6月 | 7月
－－－－－－－－－－－－－－－－－－－－－
ａさん | 50 | 20 | 80 | 10
－－－－－－－－－－－－－－－－－－－－－
ｂさん | 30 | 40 | 20 | 10
－－－－－－－－－－－－－－－－－－－－－
ｃさん | 10 | 30 | 60 | 40
－－－－－－－－－－－－－－－－－－－－－
ａさん | 30 | 20 | 40 | 50
－－－－－－－－－－－－－－－－－－－－－
ｂさん | 20 | 10 | 70 | 60
－－－－－－－－－－－－－－－－－－－－－
ａさん | 70 | 30 | 20 | 40
・
・
・
表②
Ａ Ｂ Ｃ Ｄ Ｅ
| 0～20 ｜ 21～40 | 41～60 | 61～100
－－－－－－－－－－－－－－－－－－－－－－－
ａさん | 4 ｜ 4 ｜ 2 ｜ 2
－－－－－－－－－－－－－－－－－－－－－－－
ｂさん ｜ | | |
－－－－－－－－－－－－－－－－－－－－－－－
ｃさん ｜ ｜ ｜ ｜
表①に数値を入力して自動的に表②に反映されるようにしたいのですが…。
4月～12月トータルで“0～20”がいくつ、“21～40”がいくつ、というような感じです。
いろいろ試したんですがまったくダメでした。
どのような関数を入れたらいいんでしょうか。。
どなたかご教授よろしくお願いいたします。
こんばんは～♪
回答が付かない様ですので～。。。
こんな表の場合です。。。
┌─┬────┬────┬────┬────┬────┐ │ │ A │ B │ C │ D │ E │ ├─┼────┼────┼────┼────┼────┤ │ 1│ │4月 │5月 │6月 │7月 │ ├─┼────┼────┼────┼────┼────┤ │ 2│ａさん │ 50│ 20│ 80│ 10│ ├─┼────┼────┼────┼────┼────┤ │ 3│ｂさん │ 30│ 40│ 20│ 10│ ├─┼────┼────┼────┼────┼────┤ │ 4│ｃさん │ 10│ 30│ 60│ 40│ ├─┼────┼────┼────┼────┼────┤ │ 5│ａさん │ 30│ 20│ 40│ 50│ ├─┼────┼────┼────┼────┼────┤ │ 6│ｂさん │ 20│ 10│ 70│ 60│ ├─┼────┼────┼────┼────┼────┤ │ 7│ａさん │ 70│ 30│ 20│ 40│ ├─┼────┼────┼────┼────┼────┤ │ 8│ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┤ │ 9│ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┤ │10│ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┤ │11│ │0～20 │ 21～40 │ 41～60 │ 61～100│ ├─┼────┼────┼────┼────┼────┤ │12│ａさん │ 4│ 4│ 2│ 2│ ├─┼────┼────┼────┼────┼────┤ │13│ｂさん │ 4│ 2│ 1│ 1│ ├─┼────┼────┼────┼────┼────┤ │14│ｃさん │ 1│ 2│ 1│ 0│ └─┴────┴────┴────┴────┴────┘
★条件その１ 1行目の月は、文字列で。 4月から３月までのM列までです。。。
条件その２ データの数値は、すべて入力済みでマイナスは無いこと。。。
その上で。。。式は。。。
B12セルへ
=SUMPRODUCT(($A$2:$A$7=$A12)*((INDEX($A$1:$M$7,2,MATCH("4月",$1:$1,0)):
INDEX($A$1:$M$7,7,MATCH("12月",$1:$1,0)))<=LOOKUP(100,RIGHT(B$11,ROW($1:$6))*1)))
下へコピー。。。
C12セルへ
=SUMPRODUCT(($A$2:$A$7=$A12)*((INDEX($A$1:$M$7,2,MATCH("4月",$1:$1,0)):
INDEX($A$1:$M$7,7,MATCH("12月",$1:$1,0)))<=LOOKUP(100,RIGHT(C$11,ROW($1:$6))*1)))-SUM($B12:B12)
右と下へコピー。。。
★MATCHの検索値の 4月 12月はどこかのセルへ入力しておいて セル参照してもいいですね。。。
★OFFSETを使いたくないので、INDEXを使いました。。
ご参考にどうぞ。。。
。。。Ms.Rin～♪♪
単純に ０～２０なら =COUNTIF($B2:$J2,">=0")-COUNTIF($B2:$J2,">20") ２１～４０なら =COUNTIF($B2:$J2,">=21")-COUNTIF($B2:$J2,">40") 以下、パラメータのみ変更 でも出来そうですね。
（ｍｏｍｏ）
こんにちは。 FREQUENCY関数を使ってみました。
【Sheet1】シート R/C A B C D E 1 担当者 4月 5月 6月 7月 2 ａさん 50 20 80 10 3 ｂさん 30 40 20 10 4 ｃさん 10 30 60 40 5 ａさん 30 20 40 50 6 ｂさん 20 10 70 60 7 ａさん 70 30 20 40
【Sheet2】シート R/C A B C D E 1 担当者 0～20 21～40 41～60 61～100 2 ａさん 4 4 2 2 3 ｂさん 4 2 1 1 4 ｃさん 1 2 1 0 5 区間 20 40 60
B2:E2 =TRANSPOSE(FREQUENCY(IF(Sheet1!A$2:A$10=A2,Sheet1!B$2:E$10,""),B$5:D$5)) ※B2:E2を配列数式として入力（B2:E2を選択→B2に数式入力→Ctrl+Shift+Enterを同時押下） →B2:E2をE4までフィルコピー
＜補足＞ ・TRANSPOSEを使っているのは、FREQUENCYの戻り値が、縦（列）方向の配列だからです。 ・集計対象が担当者ごとなので、FREQUENCYのデータ範囲として、IF文で担当者以外のデータを""にしています。
(コタ)
みなさんありがとうございました。
とても参考になりました。
普段SUMとかIFくらいしか使わないので本当に勉強になりました。
SUMPRODUCTとかTRANSPOSEとかFREQUENCYとか初耳でした。。。
またお世話になるかもしれませんのでその時はよろしくお願いします。
>集計対象が担当者ごとなので、FREQUENCYのデータ範囲として
4月～12月という指定もあるよ。
(たまたま通りすがり）
＞ 4月～12月という指定もあるよ。 ご指摘ありがとうございます。 たしかに列方向はあまり気にしてませんでした。
Sheet1!B$2:E$10 → Sheet1!B$2:J$10 に修正してください。 (コタ)
ただ、サンプルデータですと、8～12月が記載されていないため、0として カウントされます。 (コタ)追記
>Sheet1!B$2:E$10 → Sheet1!B$2:J$10
そういう事じゃなくて、4月～12月の指定もあれば 他の期間の指定もあるんじゃないかなという事。 Rinさんは、そこをふまえて回答してると思うけど。
(たまたま通りすがり）
ご指摘ありがとうございました。
コタさん
すいません、私の質問が判りづらかったですね。。。
丁寧に回答して頂いたのでとても助かりました。
ありがとうございました。
(マッキー）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.