エクセルの関数でピボットテーブルのような集計

以下のようなテスト結果の表（DB）があります。
列：A,B,C,D,E,F,G,H,I,J,K,L,M,N,O
　　,,,,1700,小野,,,,2,,0,A,B,0
　　,,,,1200,小野,,,,4,,1,C,0,C
　　,,,,1700,柴田,,,,5,,4,A,B,0
　　,,,,1400,矢野,,,,3,,4,B,0,A
　　,,,,1200,伊藤,,,,4,,1,C,D,0,
　　,,,,1000,田村,,,,4,,3,D,C,A

Ｆ列には、名前がフルネームで入力されています。同一人物が複数あります。
Ｊ列には、数字が１,2,3,4
L列には、A,B,C,D,E
M列には、A,B,C,と数字の0
N列には、A,B,C,と数字の0
O列には、A,B,C,と数字の0
が、それぞれ入力されています。
このDBを元に
L,M,N,Oのデータの個数を名前別に以下のように集計をしたいのです。

　　　　　J , L , M ,N,O
名前,1,2,3,4,計,A,B,C,D,E,計,A,B,C,0,計,....
小野,

ピボットテーブルで考えましたが、5回行わねばならず、結合も考えると関数で何とかならないものか
と質問させていただきました。
別に関数でなくても、もっと有効な方法がありましたらそれで結構です。御教示下さい。

No.1 ベストアンサー 回答者： tsubuyuki 回答日時： 2013/07/10 14:25

２００７以降をお使いだと信じて（笑）。

図はかなり見辛いと思いますが、読み解いてください。


さて、図の通りＡ：Ｏ列におそらくお望みの通りのサンプルを用意しました。
順を追って。

まず、Ｑ列に「作業列」を作成します。
１行目から順に見ていって、名前が初めて出てきたら行番号を返す内容の
　　Ｑ２セル（式）：=IF(COUNTIF($F$2:F2,F2)=1,ROW(),"")
として、行方向にフィルしてあります。
ちなみにフィルすると
　　Ｑ３セル（式）：=IF(COUNTIF(F$2:$F3,F3)=1,ROW(),"")
という式が入ると思われます（４行目以降の行も同様です。）

で、抽出部分。
作業列を見て、数字がある行の「名前」を取ってきます。
つまり、
　　Ｓ２セル（式）：=IF(COUNT(Q:Q)<ROW(A1),"",INDEX(F:F,SMALL(Q:Q,ROW(A1))))
として、行方向にフィルしてやります。
まぁ、ややこしいですが、なんとなく
「Ｑ列に数字が入っていたら、Ｆ列から「名前」を持ってきてくれ」
くらいの認識で大丈夫です。
正確な内容を説明しだすと長くなりますので、それぞれの関数を別途お調べくださいませ。

Ｔ：Ｘ列及びＹ列はＪ列についてのカウント部分です。
ＣＯＵＮＴＩＦＳ関数を使って、カウントしてやると簡単です。
（２００７以降、実装された関数です。）
　　Ｔ２セル（式）：=IF($S2="","",COUNTIFS($F:$F,$S2,$J:$J,T$1))
としてやり、必要範囲（Ｔ：Ｘ）にフィルしてやります。
パッと見、ややこしい式に見えますが、結構簡単です。
　　＝ＣＯＵＮＴＩＦＳ（範囲１，条件１，範囲２，条件２・・・・・）
といった具合に、「範囲・条件・範囲・条件・・・」と繰り返し、
複数条件によるカウントが可能な関数です。
これ以上の詳細は別途お調べください。
（ＩＦ関数については割愛します。）
Ｙ列は単純にＳＵＭ関数で合計を求めています。
　　Ｙ２セル（式）：=IF($S2="","",SUM(T2:X2))

同様にＺ：ＡＤ列及び、ＡＥ列は、Ｍ列に関する集計です。
　　Ｚ２セル（式）：=IF($S2="","",COUNTIFS($F:$F,$S2,$N:$N,Z$1))
で、必要セルにフィルです。
ＡＥ列は単純にＳＵＭ。
　　ＡＥ２（式）：=IF($S2="","",SUM(Z2:AD2))
ですね。


２００３以前のバージョンにはこのＣＯＵＮＴＩＦＳ関数がありません。
なので、ＳＵＭＰＲＯＤＵＣＴ関数を使って、
　　Ｔ２セル（式）：=SUMPRODUCT(($F$2:$F$7=$S2)*($J$2:$J$7=T$1))
としてやります。
これも関数の仕組みを考え出すと長くなりますので、
詳細は別途お調べくださいませ。

この回答へのお礼

御回答ありがとうございます。EXCELのバージョンは2007以降だと思います。Win8についてたもので、バージョンの調べ方が分かりません。リボンも使い辛く、ピボットテーブルもどこにあるのか分からず四苦八苦状態でした。まさか、本当に関数でできるとは思っていなかったので感謝です。ありがとうございました。

お礼日時：2013/07/11 22:43

No.5 回答者： tsubuyuki 回答日時： 2013/07/16 12:57

> ピボットテーブルもどこにあるのか分からず四苦八苦状態

なるほど、そういうことでしたか。


ＷＩＮ８パソコンについてきた、と言うことは２０１３でしょう（たぶん）。
まぁ、２０１０でも変わらないのですが、
「ピボットテーブル」はリボンの「挿入」タブの一番左にありますよ。

No.4 回答者： MackyNo1 回答日時： 2013/07/10 16:39

No2の回答の補足です。

ピボットテーブルのように、重複のない名前の一覧を関数で表示するなら、名前表示セルに以下のような数式を入力して下方向にオートフィルコピーしてください。

=INDEX(F:F,SMALL(INDEX((MATCH($F$2:$F$8&"",$F$2:$F$8&"",0)<>ROW($F$2:$F$8)-1)*1000+ROW($F$2:$F$8),),ROW(A1)))&""

No.3 回答者： KURUMITO 回答日時： 2013/07/10 16:30

作業列を作って対応するのが計算に負担のかからない方法としてお勧めです。

示しのようなデータがシート1の2行目から下方に入力されているとして作業列をQからV列に用意します。
Q2セルには次の式を入力して下方にドラッグコピーします。

=IF(F2="","",IF(COUNTIF(F$2:F2,F2)=1,MAX(Q$1:Q1)+1,""))

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

=F2&J2

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

=$F2&L2

お求めの表をシート2に表示させるとして次のようにします。
A3セルには次の式を入力して下方にドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!$Q:$Q),"",INDEX(Sheet1!$F:$F,MATCH(ROW(A1),Sheet1!$Q:$Q,0)))

なめが表示されます。
1行目にはシート1での作業列の列記号を入力します。
B1セルのR、G1セルにS、M1セルにT、R1セルにU、W1セルにVと入力します。
2行目にはB2セルからE2セルまでに1，2，3，4、F2セルには計、G2セルからK2セルまでにはA・・・E、L2セルには計、M2セルからP2セルまでにはA,B,C,0、Q2セルには計、以下右横のセルかM2セルからQ2セルを繰り返してAA2セルまで入力します。
その後にB3セルには次の式を入力します。

=IF(OR($A3="",B$2=""),"",IF(B$2<>"計",COUNTIF(INDIRECT("Sheet1!"&INDEX($B$1:$AA$1,MATCH("ー",$B$1:B$1))&":"&INDEX($B$1:$AA$1,MATCH("ー",$B$1:B$1))),$A3&B$2),SUM(INDEX($B3:$AA3,MATCH("ー",$B$1:B$1)):A3)))

この式で"ー"のーは必ず全角モードのひらがな入力による‐であることが必要で、一旦式を確定した後で-をーにけるなどの操作が必要となるでしょう。
その後に式をAA3セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

この回答への補足

御回答ありがとうございます。無事作動しているのですが、なぜ「"ー"」なのか？また、どういった仕組みで抽出しているのか、再度教えていただけにでしょうか、よろしくお願い致します。

補足日時：2013/08/08 20:56

この回答へのお礼

御回答ありがとうございます。ばっちりできました。ただ、「"ー"のーは必ず全角モードのひらがな入力による‐であることが必要で、一旦式を確定した後で-をーにけるなどの操作が必要となるでしょう」の部分ですが、何も操作しないで正常に作動しています。全角の「"ー"」が正しい理由が良く分かりません。半角の「-」では#N/Aになります。EXCEL2013では、セルに単独で入れると全角と半角の違いが分かるのですが、関数内では同じに見えます。全角の「"ー"」が正しい理由を教えていただけると幸いです。関数組み合わせの内容もあまり分かっていませんが(笑)。

お礼日時：2013/07/11 22:55

No.2 回答者： MackyNo1 回答日時： 2013/07/10 14:57

ご使用のエクセルのバージョンが記載されていないので、ひとまず2007以降のバージョンでの計算方法を提示します。

また例示のデータと説明文の内容が少し違うように思いますが、各列のデータを添付画像のように集計したいなら、添付画像のＪ列のデータを集計するＧ１２セルに以下の数式を入力して右方向および下方向にオートフィルしてください。


=COUNTIFS($F$2:$F$7,$F12,$J$2:$J$7,G$11)

ちなみに、Ｌ１２セルにはＪをＭに変えた（上記の式を合計欄も無視して右方向にオートフィルした場合）、以下の関数を入力することになります。

=COUNTIFS($F$2:$F$7,$F12,$M$2:$M$7,L$11)

ちなみに、空白セルにした各合計欄はそれぞれ列方向に選択し、ホームタブのオートSUMのΣボタンをクリックします。

＃Officeソフトはバージョンによって使用できる機能や操作方法が大きく異なりますので、質問する際には必ずバージョンを明記するようにしましょう。

この回答へのお礼

御回答いただき感謝です。私の質問の仕方が悪く、御迷惑をお掛けしました。

お礼日時：2013/07/11 22:45