No.2
- 回答日時:
こんにちは!
昨日投稿した内容より結構ややこしくなっていますね!
とりあえずやってみました。
今回はオートフィルで!というよりそれぞれの表に数式を入れていく必要があると思います。
↓の画像で元データ(左側がSheet1)をSheet2に表示するようにしてみました。
画像通りの配置とします。
Sheet2のC4セルに
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A$1)*(ISNUMBER(FIND(Sheet1!$B$1:$B$100,$C$2)))*(Sheet1!$C$1:$C$100>=$A4)*(Sheet1!$C$1:$C$100<$B4))
D4セルに
=C4/COUNTIF(Sheet1!A:A,A$1)
E4セルに
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A$1)*(ISNUMBER(FIND(Sheet1!$B$1:$B$100,$E$2)))*(Sheet1!$C$1:$C$100>=$A4)*(Sheet1!$C$1:$C$100<$B4))
F4セルに
=E4/COUNTIF(Sheet1!A:A,$A$1)
とそれぞれ入力し、C4~F4セルを範囲指定 → F4セルのフィルハンドルで下へコピー!
D1セルは
=SUMPRODUCT((Sheet1!A1:A100=A1)*(ISNUMBER(FIND(Sheet1!B1:B100,C2))),Sheet1!C1:C100)/SUM(C4:C8)
F1セルは
=SUMPRODUCT((Sheet1!A1:A100=A1)*(ISNUMBER(FIND(Sheet1!B1:B100,E2))),Sheet1!C1:C100)/SUM(E4:E8)
という数式を入れると、「営業」の表は完成です。
次に「開発」の表の数式だけを載せておきます。
C13セルに
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A$10)*(ISNUMBER(FIND(Sheet1!$B$1:$B$100,$C$11)))*(Sheet1!$C$1:$C$100>=$A13)*(Sheet1!$C$1:$C$100<$B13))
D13セルに
=C13/COUNTIF(Sheet1!A:A,A$10)
E13セルに
=SUMPRODUCT((Sheet1!$A$1:$A$100=$A$10)*(ISNUMBER(FIND(Sheet1!$B$1:$B$100,$E$11)))*(Sheet1!$C$1:$C$100>=$A13)*(Sheet1!$C$1:$C$100<$B13))
F13セルに
=E13/COUNTIF(Sheet1!A:A,$A$10)
D10セルに
=SUMPRODUCT((Sheet1!A1:A100=A10)*(ISNUMBER(FIND(Sheet1!B1:B100,C11))),Sheet1!C1:C100)/SUM(C13:C17)
F10セルに
=SUMPRODUCT((Sheet1!A1:A100=A10)*(ISNUMBER(FIND(Sheet1!B1:B100,E11))),Sheet1!C1:C100)/SUM(E13:E17)
といった感じです。
この操作を各部署ごとに行う必要があります。
長々と書きましたが、参考になりますかね?m(_ _)m
No.3ベストアンサー
- 回答日時:
今仮に、「担当部署」と入力されているセルがSheet1のA1セルで、Sheet2において「営業」と入力されているのがA1セルであるものとします。
又、「未満」欄の最上段に10と入力されていますが、10未満としたのでは、点数が10.0の場合はカウントされない事になります。
ですから、質問欄の添付画像中では10と入力されているセルを空欄とした上で、「未満」欄が空欄の場合には、その左隣りの「~以上の」条件に合致するものは全てカウントする事が出来る様に、工夫するものとします。
まず、各部署別の表ごとに、表中の最下段の行のA列のセル(A8セル、A16セル、A24セル、A32セル)に、「計」或いは「合計」等の様に「計」の字を含んでいる文字列を、必ず入力して下さい。
次に、各部署別の表ごとに、表の枠で囲まれた部分の内の最上段の行のA列のセル(A2セル、A10セル、A18セル、A26セル)に、「以上」等の何らかの文字列を、必ず入力して下さい。
次に、Sheet2のC3セル(「営業」の表中において「A-,A02,A03,A04」と入力されている箇所の直下のセル)に次の関数を入力して下さい。
=IF(COUNT($A3,$B3),SUMPRODUCT(COUNTIFS(Sheet1!$A:$A,INDEX($A:$A,MATCH("*?",$A$1:$A3,-1)-1),Sheet1!$B:$B,REPLACE(LEFT(","&INDEX(C:C,MATCH("*?",$A$1:$A3,-1)),FIND(CHAR(1),SUBSTITUTE(INDEX(C:C,MATCH("*?",$A$1:$A3,-1))&",",",",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(INDEX(C:C,MATCH("*?",$A$1:$A3,-1)))-LEN(SUBSTITUTE(INDEX(C:C,MATCH("*?",$A$1:$A3,-1)),",",))+1))))),1,FIND(CHAR(1),SUBSTITUTE(","&INDEX(C:C,MATCH("*?",$A$1:$A3,-1)),",",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(INDEX(C:C,MATCH("*?",$A$1:$A3,-1)))-LEN(SUBSTITUTE(INDEX(C:C,MATCH("*?",$A$1:$A3,-1)),",",))+1)))),),Sheet1!$C:$C,IF(ISNUMBER($A3),">="&$A3,"<>"),Sheet1!$C:$C,IF(ISNUMBER($B3),"<"&$B3,"<>"))*1),"")
次に、Sheet2のD3セルに次の関数を入力して下さい。
=IF(ISNUMBER(C3),IF(C3=0,0,C3/INDEX(C:C,MATCH("*計*",INDEX($A:$A,ROW()):INDEX($A:$A,MATCH("*?",$A:$A,-1)),0)+ROW()-1)),"")
C8=SUM(INDEX(C:C,MATCH("*?",$A$1:INDEX($A:$A,ROW()-1),-1)+1):INDEX(C:C,ROW()-1))
D8=IF(C8=0,"-",SUM(INDEX(D:D,MATCH("*?",$A$1:INDEX($A:$A,ROW()-1),-1)+1):INDEX(D:D,ROW()-1)))
D1=IF(ISNUMBER(1/INDEX(C:C,MATCH("*計*",INDEX($A:$A,ROW()):INDEX($A:$A,MATCH("*?",$A:$A,-1)),0)+ROW()-1)),SUMPRODUCT(SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,INDEX($A:$A,MATCH("*?",$A$1:$A3,-1)-1),Sheet1!$B:$B,REPLACE(LEFT(","&C2,FIND(CHAR(1),SUBSTITUTE(C2&",",",",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(C2)-LEN(SUBSTITUTE(C2,",",))+1))))),1,FIND(CHAR(1),SUBSTITUTE(","&C2,",",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(C2)-LEN(SUBSTITUTE(C2,",",))+1)))),))*1)/INDEX(C:C,MATCH("*計*",INDEX($A:$A,ROW()):INDEX($A:$A,MATCH("*?",$A:$A,-1)),0)+ROW()-1),"-")
次に、Sheet2のD3セルの書式設定の表示形式の分類を[パーセンテージ]とし、小数点以下の桁数を[2]にして下さい。
次に、Sheet2のC3~D3の範囲をコピーして、Sheet2のC4~D7の範囲に貼り付けて下さい。
次に、Sheet2のC8セルに次の関数を入力して下さい。
=SUM(INDEX(C:C,MATCH("*?",$A$1:INDEX($A:$A,ROW()-1),-1)+1):INDEX(C:C,ROW()-1))
次に、Sheet2のD8セルに次の関数を入力して下さい。
=IF(C8=0,"-",SUM(INDEX(D:D,MATCH("*?",$A$1:INDEX($A:$A,ROW()-1),-1)+1):INDEX(D:D,ROW()-1)))
次に、Sheet2のD1セルに次の関数を入力して下さい。
=IF(ISNUMBER(1/INDEX(C:C,MATCH("*計*",INDEX($A:$A,ROW()):INDEX($A:$A,MATCH("*?",$A:$A,-1)),0)+ROW()-1)),SUMPRODUCT(SUMIFS(Sheet1!$C:$C,Sheet1!$A:$A,INDEX($A:$A,MATCH("*?",$A$1:$A3,-1)-1),Sheet1!$B:$B,REPLACE(LEFT(","&C2,FIND(CHAR(1),SUBSTITUTE(C2&",",",",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(C2)-LEN(SUBSTITUTE(C2,",",))+1))))),1,FIND(CHAR(1),SUBSTITUTE(","&C2,",",CHAR(1),ROW(INDIRECT("Z1:Z"&LEN(C2)-LEN(SUBSTITUTE(C2,",",))+1)))),))*1)/INDEX(C:C,MATCH("*計*",INDEX($A:$A,ROW()):INDEX($A:$A,MATCH("*?",$A:$A,-1)),0)+ROW()-1),"-")
次に、Sheet2のD1セルの書式設定の表示形式の分類を[数値]とし、小数点以下の桁数を[3]にして下さい。
次に、Sheet2のC1~D8の範囲をコピーして、Sheet2のE1~F8の範囲に貼り付けて下さい。
次に、Sheet2のC2セルとE2セルに、それぞれ集計対象となるレベルの名称を、「,」(半角のカンマ)で区切って入力して下さい。
次に、Sheet2のA3~B7の各セルに、条件となる点数の範囲の上限値と下限値を入力して下さい。
但し、「未満」欄の部署内で最上段の行にあるセルだけは、数値を入力しないで、空欄のままとして下さい。
又、「以上」欄の部署内で最下段の行にあるセルには、必ずしも数値を入れなくとも構いません。
次に、Sheet2のA1~F8の範囲をコピーして、その下に、順次貼り付けて下さい。(各票の間隔は、8行毎でなくとも構いません)
そして最後に、各表の「以上」と入力されているセルの1つ上のセルに、各々の部署名を入力して下さい。
以上です。
因みに、集計対象となるレベルの名称や、条件となる点数の範囲の上限値と下限値は、それらの値が入力されている各セルの値を入力しなおす事で、各部署ごとに個別に設定を変更する事も出来ますし、条件範囲の行数や、集計対象となるレベルの個数も、個別に変更する事が出来ます。
この回答へのお礼
お礼日時:2012/12/13 08:24
kagakusuki様、
ご回答いただきありがとうございました。
実際のファイルはデータの項目も量も多かったのでいただいたFomulaにもかかわらず作成に手間取り、その後出張などで返答が遅くなてしまい誠に申し訳ございませんでした。
肝心のTaskに関しては、アドバイスを基に無事に完成いたしました。
改めましてありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excelの数式についての質問 1 2022/10/31 15:50
- Excel(エクセル) エクセル 条件に合う日付に入力された時間数の合計したい 4 2022/06/17 22:18
- 統計学 統計量および正規分布と分散の加法性の演習問題です。 5 2023/07/29 10:46
- Visual Basic(VBA) ExcelのVBAを使い、複数シートの同一箇所を、同一条件にて一括でソルバーを回す方法について 1 2022/04/23 11:49
- Excel(エクセル) Excelでの複数条件のカウントについて 1 2022/09/25 07:40
- Visual Basic(VBA) VBA初心者です。 VBAで行単位で条件付き書式の色をカウントしたいです。 大量のデータがあるExc 3 2022/06/08 10:02
- Visual Basic(VBA) VBA初心者です。 VBAで行単位で条件付き書式の色をカウントしたいです。 大量のデータがあるExc 3 2022/06/08 10:00
- Excel(エクセル) Excelで全クラスのランキング表を作成したい 4 2022/05/24 15:28
- Excel(エクセル) エクセルで当番表を作成したいです。 1 2023/08/09 19:53
- Access(アクセス) Accessフォームで複数条件でフィールドの値を表示する方法 4 2022/07/21 10:47
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
MicrosoftOfficeの1ユーザー2...
-
MicrosoftOffice2019なんですが、
-
Microsoft 365のディフェンダー...
-
英数字のみ全角から半角に変換
-
Excel 日付を比較したら、同じ...
-
Microsoft Officeを2台目のPCに...
-
Microsoft365の「お支払いを更...
-
会社PCのメールが更新されない
-
エクセル関数について
-
エクセルのシフト表を簡単にGoo...
-
ウィンドウィズ メモ帳で日付だ...
-
会社のTeamsのことで相談です。...
-
バソコンが二台とも壊れ後換装...
-
Microsoft Formsの「個人情報や...
-
複数の写真を1枚に印刷
-
Formsにて、匿名にて回答する方...
-
パソコン画面の中の小さい画面...
-
マイクロソフト 一時使用コード...
-
MicrosoftOfficeについて質問で...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Office 2021 Professional Plus...
-
エクセルで例えば、関数を使っ...
-
Microsoft Formsの「個人情報や...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルで例えば、A1に㈱ベ...
-
理由を教えてください。
-
エクセルでXLOOKUP関数...
-
マイクロソフト オフィスについて
-
VLOOKUP関数について
-
teams設定教えて下さい。 ①ビデ...
-
Googleのスプレッドシートでシ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft365で写真をアルバム...
-
Outlook で宛先が複数の場合の人数
-
Excel テーブル内の空白行の削除
おすすめ情報