エクセル ○行ごとに縦横変換。

質問

エクセル　○行ごとに縦横変換。

エクセルで以下のようなデータがあります。

氏名　担当　数
－－　－－　－
鈴木　東京　５
鈴木　大阪　０
鈴木　千葉　５
鈴木　京都　３
木村　東京　２
木村　大阪　２
木村　千葉　０
木村　京都　６
…続く

↑を↓のように入れ替えたいのです。
氏名　東京　大阪　千葉　京都
鈴木　　５　　　０　　　５　　　３
木村　　２　　　２　　　０　　　６
…続く

条件としましては、氏名は増えていきますが、担当は上記4箇所で順番を含めて固定です。
また、数が０件の時も表示します。
ですので、４行ごとに担当者名が変わって行くデータになっております。

以下のようなデータから前述までに集計したのですが、
鈴木　東京
鈴木　東京
鈴木　千葉
木村　東京
鈴木　東京
木村　大阪
…　続く
そこからが進まず質問させていただきました。
よろしくお願いいたします。

回答No.4

MackyNo1 · ベストアンサー

No3の回答の訂正と補足です。

　誤：D2セル以下に名前が入力されている場合、

　正：E2セル以下に名前が入力されている場合、

今回は必要ないと思いますが、もし重複のない担当者の名前を自動的にE2セル以下に表示させたいなら以下のような関数で表示できます。

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

＞以下のようなデータから前述までに集計したのですが、
鈴木　東京
鈴木　東京
鈴木　千葉

おそらく、私の回答した2つ目のSUMPRODUCT関数（またはピボットテーブル）で集計する方法を用いれば、元データから一発で表示できるような気がします。

お礼

たくさんのお返事じをいただきました。
ありがとうございます。
皆様の教えを全て試させていただき、無事に全て動作できました。
たくさんのお返事を頂戴してしまったので、評価は順番でつけさせて頂きます。

明日、早速仕事場で運用してみます。
本当にありがとうございました。

回答No.6

esupuresso

添付した表から・・・
1行目に見出し行とE列に名前を入力しておきまして・・・
【F3】=SUMPRODUCT(($A$2:$A$1000=$E3)*($B$2:$B$1000=F$1)*$C$2:$C$1000)の式を入れます
【F3】～【I3】まで式のコピーをして、そのまま【I5】セルまで【F3】～【I3】の式をコピーします
セル【A2】～【A1000】を範囲とした式になってますのでデータの範囲に合わせて数値を変更してください

宜しければお使い下さい　<(_ _)>

お礼

たくさんのお返事じをいただきました。
ありがとうございます。
皆様の教えを全て試させていただき、無事に全て動作できました。
たくさんのお返事を頂戴してしまったので、評価は順番でつけさせて頂きます。

明日、早速仕事場で運用してみます。
本当にありがとうございました。

回答No.5

KURUMITO

Ａ列からＣ列で1行目には項目名があり、2行目から下方にお示しのデータがあるとしたら、
例えばＥ1セルに氏名、Ｆ1セルに東京、Ｇ1セルに大阪、Ｈ1セルに千葉、Ｉ1セルに京都と入力してＥ2セルには次の式を入力し、Ｉ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(INDEX($A:$A,ROW(A1)*4-2)="","",IF(COLUMN(A1)=1,INDEX($A:$A,ROW(A1)*4-2),IF(COLUMN(A1)<=5,INDEX($C:$C,ROW(A1)*4-4+COLUMN(A1)),"")))

お礼

たくさんのお返事じをいただきました。
ありがとうございます。
皆様の教えを全て試させていただき、無事に全て動作できました。
たくさんのお返事を頂戴してしまったので、評価は順番でつけさせて頂きます。

明日、早速仕事場で運用してみます。
本当にありがとうございました。

回答No.3

MackyNo1

関数で表を組み替えるなら、たとえば以下のような数式で表示できます。

たとえばF1セルから右に東京、大阪、・・・、D2セル以下に名前が入力されている場合、F2セルに以下の式を入力して右方向および下方向にオートフィルコピーします。

=INDEX($C:$C,MATCH($E2,$A:$A,0)+COLUMN(A1)-1)

同じ名前と地域が２つ以上出現してそれを集計したい場合は（もちろん1回しか出てこない場合もOKです）以下のような式になります。

=SUMPRODUCT(($A$2:$A$100=$E2)*($B$2:$B$100=F$1)*$C$2:$C$100)

お礼

たくさんのお返事じをいただきました。
ありがとうございます。
皆様の教えを全て試させていただき、無事に全て動作できました。
たくさんのお返事を頂戴してしまったので、評価は順番でつけさせて頂きます。

明日、早速仕事場で運用してみます。
本当にありがとうございました。

回答No.2

tom04

こんにちは！
一例です。

↓の画像で左側がSheet1でそれをSheet2に表示させています。

Sheet1に作業用の列を設けています。
作業列D2セルに
=IF(COUNTIF($A$2:A2,A2)=1,ROW(A1),"")
という数式を入れオートフィルで下へコピーします。

そして、Sheet2の「担当地域」はあらかじめ入っているものとします。

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

B2セルに
=IF($A2="","",INDEX(Sheet1!$C$2:$C$1000,SUMPRODUCT((Sheet1!$A$2:$A$1000=$A2)*(Sheet1!$B$2:$B$1000=B$1)*(ROW($A$1:$A$999)))))
として、B2セルを右へE2セルまでコピーします。

最後にA2～E2セルを範囲指定し、E2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

以上、参考になれば良いのですが・・・m(__)m

お礼

たくさんのお返事じをいただきました。
ありがとうございます。
皆様の教えを全て試させていただき、無事に全て動作できました。
たくさんのお返事を頂戴してしまったので、評価は順番でつけさせて頂きます。

明日、早速仕事場で運用してみます。
本当にありがとうございました。

回答No.1

Cupper

ピボットテーブルと言う機能を使ってみてはいかがでしょう。

データをいじったり、関数やマクロを使わなくてもできますよ。
ピボットテーブルは使わなければマジで使わない機能ですけど、Excelで表計算を行う上では基本的な操作です。
簡単な解説本にも記述がありますから、自力で設定ができないのでしたら解説本を手元に置いておくなどして
いつでも参照できるようにしておくと良いでしょう。
（Webにも解説サイトが多数あるので、一通り探してみてみると良いと思います。
　http://www11.plala.or.jp/koma_Excel/pivot_menu.html
　など）

お礼

敷居の高かったピボットテーブルを触ってみました。
とても不思議な感じでした。
いろいろと触れば劇的なことが出来そうでした。
お返事ありがとうございました。