エクセルで集計表を作りたい

質問

A列…店舗名
B列…名前
C列…役職名
D列…保険加入有無

の表を加工して、店舗別・役職別、加入率の一覧表を作成したいのですが上手く行きません。どのような方法（関数やピポッド）で上記表は作成できるのでしょうか。おわかりになれば、ご教示下さい。

具体的には
◎◎店の社員は○名、アルバイトは○名。社員の加入者は○名・未加入者は○名で加入率○％、アルバイトの加入者は○名・未加入者は○名で加入率○％…ということがわかる、一覧表が作成したいです。まとまらない文章で恐縮ですが、お知恵をおかりできると助かります。

回答No.1

KURUMITO · ベストアンサー

シート1にはお示しの表が有るとします。A列からD列の1行目には項目名が有り、それぞれのデータは2行目から下方にあるとします。
なお、役職名のC列には役職名かアルバイトの文字列のどちらかが入力されているとします。アルバイト以外の文字は社員とみなします。
保険加入の有無の列では有、無のどちらかの文字が入力されているとします。
そこでシート1では作業列をE列とF列に設けます。
E2セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,MAX(E$1:E1)+1,""))

F2セルには次の式を入力して下方にドラッグコピーします。

=IF(A2="","",A2&"/"&IF(C2<>"アルバイト","社員",C2)&"/"&D2)

次にお求めの表をシート2に表示することにします。
シート2の1行目は項目名でそれぞれ次のように入力します。
A1:　店舗名
B1:　社員数
C1：　アルバイト数
D1:　社員加入数
E1:　社員未加入数
F1:　アルバイト加入数
G1:　アルバイト未加入数
H1:　社員加入率
I1:　アルバイト加入率
その後にA2セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!E:E),"",INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!E:E,0)))

B2セルには次の式を入力してI2セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF($A2="","",IF(COLUMN(A1)<=2,COUNTIF(Sheet1!$F:$F,$A2&"/"&LEFT(B$1,LEN(B$1)-1)&"*"),IF(COLUMN(A1)<=6,IF(MOD(COLUMN(A1),2)=1,COUNTIF(Sheet1!$F:$F,$A2&"/"&SUBSTITUTE(B$1,"加入者","")&"/有"),COUNTIF(Sheet1!$F:$F,$A2&"/"&SUBSTITUTE(B$1,"未加入者","")&"/無")),IF(COLUMN(A1)=7,$D2/($D2+$E2),IF(COLUMN(A1)=8,$F2/($F2+$G2),"")))))

以上で完成です。一度こちらで述べたと同じ条件で試験してみてください。

お礼

早速のご回答ありがとうございます。
数式までお付けいただき丁寧なご対応に頭が下がる思いです。
さて、実際にやってみましたが上手くいきませんでした。
具体的にはシート1のE列にはまばらに数字が出(数字が出るセルがあったりなかったりします)、シート2については、B2に数式を貼り付けてenterをすると、勝手に[値の更新:1]という別ウィンドウが立ち上がり、ファイルの選択が要求されます。キャンセルで閉じると何とかドラッグが出来たので先に進んでみたのですが、社員数と社員未加入数、アルバイト未加入数は全て0、社員加入数とアルバイト加入数、社員加入率、アルバイト加入率には#NAME ?が表示されてしまいます。
せっかく教えていただきながら、上手く表示させられず大変申し訳ないです…

補足

質問のカテゴリー違いに気がつき、同じ質問を再投稿させていただいています。
(本来であればサポートセンターに依頼すべきもの、納期が迫っており、このような対応になってしまい申し訳ありません。お詫び、また、お礼申し上げます)
再投稿後のURLです。
http://oshiete.goo.ne.jp/qa/7909229.html