EXCEL2007で２列の入力内容を集計するには？

質問

EXCEL2007で、次のような集計をどうするかわからず、困っています。
たとえば、各入力行に、
－－－－－
東京都　　杉並区
東京都　　千代田区
神奈川県　瀬谷区
神奈川県　横浜市
東京都　　千代田区
東京都　　武蔵野市
東京都　　杉並区
神奈川県　横浜市
東京都　　千代田区
－－－－－
．．．のように、２列にそれぞれ文字列が入っているデータにおいて、２列の語句の組み合わせで一番多いものをカウントアップし、多いもの順に表示させたいのです。
上の例でいえば、
東京都　　千代田区　3
東京都　　杉並区　2
神奈川県　横浜市　2
東京都　　武蔵野市　1
神奈川県　瀬谷区　1
のようにカウントしたものを作りたいのです。
２つの語句を結合してしまってからピボットテーブルを使えばできそう、とまではわかってきたのですが、できれば２列に分かれたまま集計したいです。
お知恵をお貸しください。

回答No.6

kagakusuki

回答No.3です。

　回答No.2様へのお礼で、

>１シート内でもできる方法があること、EXCEL2010ならピボットテーブルでできるということ、参考になりました。

と書いておられるという事は、1シート内で行う方法を望んでおられるのではないかと思いますが、私が回答No.3で別シートを用いたのは、作業列が見えていますと印刷のを行った際等に見苦しくなるため、敢えて別のシート上に作業列を移しているだけです。
　私が挙げた方法でも1シート内で行う事は簡単で、それは以下の様になります。

　今仮に、別シート上の列ではなく、同じシート上のH列とI列を作業列として使用するものとします。
　まず、I2セルに次の関数を入力して下さい。

=IF($A2&$B2="","",IF(COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2)=1,COUNTIFS($A:$A,$A2,$B:$B,$B2),""))

　次に、H2セルに次の関数を入力して下さい。

=IF(ISNUMBER($I2),COUNTIF($I:$I,">"&$I2)+COUNTIF($I$2:$I2,$I2),"")

　次に、以下の操作を行って、H2セルとI2セルを非表示にして下さい。（添付画像では、非表示にして見えなくしたのでは、作業列の状況が解り難いかと思いましたので、添付画像では、非表示にはせずに、薄い青色の文字で表示しております）
　尚、非表示にするのは見る必要のないデータが表示されて見苦しくなる事を避けるためであり、もし、見苦しくとも構わないという場合には、この操作を行う必要は御座いません。

H2～I2のセル範囲をまとめて範囲選択
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせてマウスを右クリック
　　↓
現れた選択肢の中にある[セルの書式設定]をクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[表示形式]タブをクリック
　　↓
現れた「分類」欄の中にある[ユーザー定義]をクリック
　　↓
現れた「種類」欄の中に次の様に入力

;;;

　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック

　次に、H2～I2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。
　次に、D2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT($H:$H),"",IF(INDEX($A:$A,MATCH(ROWS($2:2),$H:$H,0))="","",INDEX($A:$A,MATCH(ROWS($2:2),$H:$H,0))))

　次に、E2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT($H:$H),"",IF(INDEX($B:$B,MATCH(ROWS($2:2),$H:$H,0))="","",INDEX($B:$B,MATCH(ROWS($2:2),$H:$H,0))))

　次に、F2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT($H:$H),"",VLOOKUP(ROWS($2:2),$H:$I,2,FALSE))

　次に、D2～F2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上です。

回答No.5

tom04

No.1です！
たびたびごめんなさい。

Excel2007だというコトですので、配列数式にしなくてもよい方法にしてみました。
配置は前回アップした画像通りとし、Sheet1の作業列をC列だけにします。

C2セルに
=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,COUNTIFS(A:A,A2,B:B,B2)*10000000+ROW(),"")
としてこれ以上データはない！というくらいしっかり下へオートフィルでコピーしておきます。

次にSheet2のA2セルに
=IFERROR(INDEX(Sheet1!A:A,MATCH(LARGE(Sheet1!$C:$C,ROW(Sheet1!A1)),Sheet1!$C:$C,0)),"")
として隣りのB2セルまでコピー！

C2セルに
=IFERROR(INT(LARGE(Sheet1!C:C,ROW(Sheet1!A1))/10000000),"")
という数式を入れます。

最後にA2～C2セルを範囲指定　→　C2セルのフィルハンドルで下へコピー！

これで前回と同じ結果が表示されると思います。m（_ _）m

お礼

遅くなりましたが、ご回答ありがとうございました。
この方法でもやってみて、とりあえずは何とかできました。
図まで描いていただき、ありがとうございました。

回答No.4

kagakusuki

回答No.3の添付画像に関してですが、「作業列1」、「作業列2」では何の事やら解り難いかと思いましたので、画像を少し変更しました。(関数自体は変更しておりません)
　尚、Sheet1のA列とB列の色分けは、単に数えやすくするために手動で設定したもので、今回は色分けの自動化は特に行ってはおりません。

お礼

遅くなりましたが、ご回答ありがとうございました。
この方法でもやってみて、何とかできました。
図まで描いていただき、ありがとうございました。

回答No.3

kagakusuki

作業列を使うのでしたら、Shift＋Ctrlキー操作が必要な配列数式などを使わずとも、全自動で集計可能なワークシート関数だけで集計した方が手っ取り早い上に、パソコンの負担も軽くなります。

　今仮に、元データが入力されている２列とはSheet1のA列とB列の事であるものとし、Sheet2のA列とB列を作業列として使用して、Sheet1のD列～F列に集計結果を表示させるものとします。

　まず、Sheet2のB2セルに次の関数を入力して下さい。

=IF(Sheet1!$A2&Sheet1!$B2="","",IF(COUNTIFS(Sheet1!$A$2:$A2,Sheet1!$A2,Sheet1!$B$2:$B2,Sheet1!$B2)=1,COUNTIFS(Sheet1!$A:$A,Sheet1!$A2,Sheet1!$B:$B,Sheet1!$B2),""))

次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(ISNUMBER($B2),COUNTIF($B:$B,">"&$B2)+COUNTIF($B$2:$B2,$B2),"")

次に、Sheet2のA2～B2の範囲をコピーして、同じ列の3行目以下に(Sheet1のA列・B列の表の行数を上回るのに十分な行数となるまで)貼り付けて下さい。

　次に、Sheet1のD2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet2!$A:$A),"",IF(INDEX($A:$A,MATCH(ROWS($2:2),Sheet2!$A:$A,0))="","",INDEX($A:$A,MATCH(ROWS($2:2),Sheet2!$A:$A,0))))

　次に、Sheet1のE2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet2!$A:$A),"",IF(INDEX($B:$B,MATCH(ROWS($2:2),Sheet2!$A:$A,0))="","",INDEX($B:$B,MATCH(ROWS($2:2),Sheet2!$A:$A,0))))

　次に、Sheet1のF2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet2!$A:$A),"",VLOOKUP(ROWS($2:2),Sheet2!$A:$B,2,FALSE))

　次に、Sheet1のD2～F2の範囲をコピーして、同じ列の3行目以下に(Sheet1のA列・B列の表の行数を上回るのに十分な行数となるまで)貼り付けて下さい。

　以上です。

お礼

ご回答ありがとうございます。

回答No.2

keithin

１行目からデータを羅列してあるとして。
簡単のためＢ列Ｃ列にデータを記入
Ａ１に
=IF(COUNTIFS($B$1:B1,B1,$C$1:C1,C1)=1,COUNTIFS(B:B,B1,C:C,C1)+(9999-ROW())/10000,"")
と記入、下向けにコピー。

Ｅ１に
=IF(ROW()>COUNT(A:A),"",VLOOKUP(LARGE(A:A,ROW()),A:C,2,FALSE))
Ｆ１に
=IF(ROW()>COUNT(A:A),"",VLOOKUP(LARGE(A:A,ROW()),A:C,3,FALSE))
Ｇ１に
=IF(ROW()>COUNT(A:A),"",COUNTIFS(B:B,E1,C:C,F1))
それぞれ下向けにコピーして完成。

＃参考
エクセル2007では利用できませんが、エクセル2010以降を使いピボットテーブルレポートで集計
フィールドの設定で「アイテムのラベルを繰り返す」の機能を使って添付図のようにラベルを再掲
隣の列に参照数式でレポートの内容を再掲させ、降順に並べ替える
といった方法でも出来ます。

お礼

遅くなりましたが、ご回答ありがとうございました。
１シート内でもできる方法があること、EXCEL2010ならピボットテーブルでできるということ、参考になりました。
図まで描いていただき、ありがとうございました。

回答No.1

tom04

こんばんは！
一例です。

↓の画像で説明します。
上側が元データのSheet1で下側（Sheet2）に表示させるとします。

Sheet1に作業用の列を2列設けています。
作業列1のC2セルには
=A2&B2

作業列2のD2セルに
=IF(OR(A2="",COUNTIF(C$2:C2,C2)>1),"",COUNTIF(C:C,C2))

という数式を入れC2・D2セルを範囲指定　→　D2セルのフィルハンドルでずぃ～～～！っとしっかり下へコピーしておきます。

Sheet2のA2セルに
=IF($C2="","",INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$D$1:$D$1000=$C2,ROW(A$1:A$1000)),COUNTIF($C$2:$C2,$C2))))
これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
この画面からコピー＆ペーストする場合は上記数式を
ドラッグ＆コピー　→　Sheet2のA2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
このA2セルを隣りのB2セルまでオートフィルでコピー！

C2セル（配列数式ではありません）には
=IF(COUNT(Sheet1!D:D)<ROW(A1),"",LARGE(Sheet1!D:D,ROW(A1)))
という数式をいれます。

最後にA2～C2セルを範囲指定　→　C2セルのフィルハンドルで下へコピー！
これで画像のような感じになります。

※　極端にデータ量が多い場合は配列数式はPCにかなりの負担をかけ計算速度が落ちます。
3000行程度であれば大丈夫だと思いますが、とりあえず1000行まで対応できる数式にしてみました。

※　データ量がもっと多い場合は作業列を増やすなりして対応した方が良いと思います。m(_ _)m

お礼

ご回答ありがとうございます。