エクセルで記号ごとに人数を別の表で集計する関数はないでしょうか？

質問

エクセルで記号ごとに人数を別の表で集計する関数はないでしょうか？

下記のような表があるとします。　　　　単価は全て1ｈ100円とします。

　　A　　　　　B　　　　　C　　　　　D　　　　　E　　　　　　F　　　　　G
1　記号　　4月/ｈ　　　金額　　　5月/ｈ　　金額　　　6月/ｈ　　　金額　　　
2　　ア　　　　1ｈ　　　　100円　　　2ｈ　　　200円　　　　0ｈ　　　　0円
3　　ウ　　　　0ｈ　　　　0　円　　　2ｈ　　　　200円　　　　1ｈ　　　100円　
4　　イ　　　　2ｈ　　　　200円　　　1ｈ　　　　100円　　　　0ｈ　　　0円
5　　ウ　　　　1ｈ　　　　100円　　　0ｈ　　　　0円　　　　　3ｈ　　　300円　

上表を元に、記号と月ごとの人数の集計表を下記のように作りたいです。

　　　A　　　　　B　　　　　C　　　　　D　　　　　E　　　　　　F　　　　　G
10　記号　　4月/ｈ　　　人数　　　5月/ｈ　　人数　　　6月/ｈ　　　人数　　　
11　　ア　　　　1ｈ　　　　1人　　　　2ｈ　　　　1人　　　　　0ｈ　　　　　0人
12　　イ　　　　2ｈ　　　　1人　　　　1ｈ　　　　1人　　　　0ｈ　　　　0人
13　　ウ　　　　1ｈ　　　　1人　　　　2ｈ　　　　1人　　　　　　4ｈ　　　2人　

4月、5月、6月ごとの人数を計算する関数はないでしょうか。

回答No.3

tom04 · ベストアンサー

こんばんは！
すでに回答が出ていますので、参考程度で目を通してみてください。

余計なお世話かもしれませんが、Sheet2に「記号」の重複なしで表示するようにしてみました。
↓の画像で説明すると
Sheet1に作業用の列を設けています。
作業列H2セルに
=IF(COUNTIF($A$2:A2,A2)=1,ROW(A1),"")
という数式を入れ、H2セルのフィルハンドルでダブルクリック、またはオートフィルで下へずぃ～～～！っとコピーします。

Sheet2のA2セルに
=IF(COUNT(Sheet1!$H$2:$H$1000)<ROW(A1),"",INDEX(Sheet1!$A$2:$A$1000,SMALL(Sheet1!$H$2:$H$1000,ROW(A1))))

B2セルに
=IF($A2="","",SUMIF(Sheet1!$A$2:$A1000,$A2,Sheet1!B$2:B$1000))

C2セルに
=IF($A2="","",SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*(Sheet1!B$2:B$1000<>0)))

そして、B2・C2セルを範囲指定し、C2セルのフィルハンドルで列方向（右方向）に2列ずつオートフィルでコピーします。
最後にA2～G2セルを範囲指定し、G2セルのフィルハンドルで下へコピーすると
画像のような感じになります。
（数式はSheet1の1000行目まで対応できるようにしています。）

尚、画像では記号はSheet1の出現順に表示されますので、ア・イ・ウの順序が変わっています。
もしア・イ・ウの順に表示したいのであればSheet1のA列で昇順に並び替えをすれば、Sheet2に反映されます。

以上、長々と失礼しました。m(__)m

回答No.6

Tofu-Yo

配列数式を使うやり方です。以下ではさらにtrueは数値の1、falseは数値の0と同じように扱えることを利用しました。数式自体はかなりすっきりします。ただ数式を見ても多少わかりにくくなってしまう欠点もあります。

行番号、列番号は質問内容欄にあるとおり使用します。また単位は入力値に含まれず、すべて数値であることを前提にします。

（１）B11セルに「=SUM(($A$2:$A$5=$A11)*B$2:B$5」と入力し、単にEnterではなくCtrl+Shift+Enterを押します。「{=SUM(($A$2:$A$5=$A11)*B$2:B$5}」と{}でくくられた表示されるようになるはずです。
（２）同様にC11セルに=SUM(($A$2:$A$5=$A11)*(C$2:C$5>0))」と入力し、Ctrl+Shift+Enterを押します。
（３）あとはB11:C11をコピーして他の範囲に貼り付けます。

（１）は（A?=S11なら1、そうでなければ0）×（B?）を?=2～5についてそれぞれ計算して足すという意味です。同様に、（２）は（A?=S11なら1、そうでなければ0）×（C?>0なら1、そうでなければ0）を?=2～5についてそれぞれ計算して足すという意味です。

個人的には配列数式が理解できればSUMIFもSUMPRODUCTも要らず、さらに詳細な計算ができるのでこっちの方が好きです。いかがでしょう？

回答No.5

kagakusuki

回答番号:ANo.4です。
　因みに、上の表において、「～h」や「～円」といった単位の表示を書式で行っている訳ではなく、セルに入力されているデータは文字列データであり、実際に「～h」や「～円」といった単位まで、データとして存在している場合には、他のセルにコピーする前にB11セルやC11セルに入力する数式を、次の様に変更して下さい。

B11セルに入力する数式。

=IF(COUNTIF(B$2:B$5,"><")=0,"",SUMPRODUCT(($A$2:$A$5=$A$11)*(VALUE(SUBSTITUTE(B$2:B$5,"h",))))&"h")

C11セルに入力する数式。

=IF(COUNTIF(C$2:C$5,"><")=0,"",SUMPRODUCT(($A$2:$A$5=$A$11)*(VALUE(SUBSTITUTE(C$2:C$5,"円",)))/((VALUE(SUBSTITUTE(B$2:B$5,"h",)))+(B$2:B$5="0h")+(B$2:B$5="")))/100&"人")

回答No.4

kagakusuki

上の表において、「～h」や「～円」といった単位の表示は書式で行っていて、セルに入力されているデータは数値データである場合には、次の様な方法があります。

　まず、A11セルに「ア」、A12セルに「イ」、A13セルに「ウ」と入力して下さい。（何れも「」は不要）
　次に、B11セルを選択して、セル書式設定の表示形式の分類欄を、「ユーザー定義」とした上で、種類欄に

0"h"

と入力して、[OK]ボタンをクリックして下さい。
　続いて、B11セルに次の数式を入力して下さい。

=IF(COUNT(B$2:B$5)=0,"",SUMIF($A$2:$A$5,$A$11,B$2:B$5))

　同様に、C11セルを選択して、セル書式設定の表示形式の分類欄を、「ユーザー定義」とした上で、種類欄に

0"人"

と入力して、[OK]ボタンをクリックして下さい。
　続いて、C11セルに次の数式を入力して下さい。

=IF(COUNT(C$2:C$5)=0,"",SUMPRODUCT(($A$2:$A$5=$A$11)*(C$2:C$5)/((B$2:B$5)+(B$2:B$5=0)))/100)

　そして、B11～C11の範囲を範囲コピーして、B12～C13の範囲に貼り付けて下さい。
　続いて、B11～C13の範囲を範囲コピーして、D11～Y13の範囲に貼り付けて下さい。

　以上で完成です。

回答No.2

KURUMITO

２行目から５行目までのデータの表示ですが　ｈ　や　円　の表示はセルの書式設定からの表示形式の調整で行われているのでしょうか？　そうでない場合には計算が難しいことになります。
例えばセルに１と入力して　１ｈと表示させるのでしたら「セルの書式設定」から「表示形式」の「ユーザー定義」で　0"h" のようにすればよいでしょう。
そのようにデータが入力されているとしてＢ１１セルには次の式を入力してＧ１１セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF($A11="","",IF(MOD(COLUMN(B1),2)=0,SUMIF($A$2:$A$5,$A11,B$2:B$5),SUMPRODUCT(($A$2:$A$5=$A11)*(B$2:B$5>0))))

なお、表ではｈ　人　円などの表示は先に述べたユーザー定義の表示形式で調整されているものとします。

回答No.1

noname#119360

ピボットテーブル機能を使ったらできそうに思います。

お礼

やはりピボットの方が簡単かもしれませんね。
早速の返信どうもありがとうございましたｍ（＿＿）ｍ。