[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『○○以上○○未満』(マッキー)
以下の表@を参考に表Aの集計表を作っているんですが
関数がわからずとても困っています。
表@
A B C D E
| 4月 | 5月 | 6月 | 7月 −−−−−−−−−−−−−−−−−−−−− aさん | 50 | 20 | 80 | 10 −−−−−−−−−−−−−−−−−−−−− bさん | 30 | 40 | 20 | 10 −−−−−−−−−−−−−−−−−−−−− cさん | 10 | 30 | 60 | 40 −−−−−−−−−−−−−−−−−−−−− aさん | 30 | 20 | 40 | 50 −−−−−−−−−−−−−−−−−−−−− bさん | 20 | 10 | 70 | 60 −−−−−−−−−−−−−−−−−−−−− aさん | 70 | 30 | 20 | 40 ・ ・ ・
表A
A B C D E
| 0〜20 | 21〜40 | 41〜60 | 61〜100 −−−−−−−−−−−−−−−−−−−−−−− aさん | 4 | 4 | 2 | 2 −−−−−−−−−−−−−−−−−−−−−−− bさん | | | | −−−−−−−−−−−−−−−−−−−−−−− cさん | | | |
表@に数値を入力して自動的に表Aに反映されるようにしたいのですが…。
4月〜12月トータルで“0〜20”がいくつ、“21〜40”がいくつ、というような感じです。
いろいろ試したんですがまったくダメでした。
どのような関数を入れたらいいんでしょうか。。
どなたかご教授よろしくお願いいたします。
こんばんは〜♪
回答が付かない様ですので〜。。。
こんな表の場合です。。。
┌─┬────┬────┬────┬────┬────┐ │ │ A │ B │ C │ D │ E │ ├─┼────┼────┼────┼────┼────┤ │ 1│ │4月 │5月 │6月 │7月 │ ├─┼────┼────┼────┼────┼────┤ │ 2│aさん │ 50│ 20│ 80│ 10│ ├─┼────┼────┼────┼────┼────┤ │ 3│bさん │ 30│ 40│ 20│ 10│ ├─┼────┼────┼────┼────┼────┤ │ 4│cさん │ 10│ 30│ 60│ 40│ ├─┼────┼────┼────┼────┼────┤ │ 5│aさん │ 30│ 20│ 40│ 50│ ├─┼────┼────┼────┼────┼────┤ │ 6│bさん │ 20│ 10│ 70│ 60│ ├─┼────┼────┼────┼────┼────┤ │ 7│aさん │ 70│ 30│ 20│ 40│ ├─┼────┼────┼────┼────┼────┤ │ 8│ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┤ │ 9│ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┤ │10│ │ │ │ │ │ ├─┼────┼────┼────┼────┼────┤ │11│ │0〜20 │ 21〜40 │ 41〜60 │ 61〜100│ ├─┼────┼────┼────┼────┼────┤ │12│aさん │ 4│ 4│ 2│ 2│ ├─┼────┼────┼────┼────┼────┤ │13│bさん │ 4│ 2│ 1│ 1│ ├─┼────┼────┼────┼────┼────┤ │14│cさん │ 1│ 2│ 1│ 0│ └─┴────┴────┴────┴────┴────┘
★条件その1 1行目の月は、文字列で。 4月から3月までのM列までです。。。
条件その2 データの数値は、すべて入力済みでマイナスは無いこと。。。
その上で。。。式は。。。
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〜♪♪
単純に 0〜20なら =COUNTIF($B2:$J2,">=0")-COUNTIF($B2:$J2,">20") 21〜40なら =COUNTIF($B2:$J2,">=21")-COUNTIF($B2:$J2,">40") 以下、パラメータのみ変更 でも出来そうですね。
(momo)
こんにちは。 FREQUENCY関数を使ってみました。
【Sheet1】シート R/C A B C D E 1 担当者 4月 5月 6月 7月 2 aさん 50 20 80 10 3 bさん 30 40 20 10 4 cさん 10 30 60 40 5 aさん 30 20 40 50 6 bさん 20 10 70 60 7 aさん 70 30 20 40
【Sheet2】シート R/C A B C D E 1 担当者 0〜20 21〜40 41〜60 61〜100 2 aさん 4 4 2 2 3 bさん 4 2 1 1 4 cさん 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.