エクセル シート 自動反映 並び替え

質問

シート1
順位　　会社名　　住所　　　売上(1)　　売上(2)
1　　　　A社　　　　品川区　　100　　　　200　　
2　　　　B社　　　　港区　　　　90　　　　190
3　　　　C社　　　　品川区　　80　　　　　180

シート2（品川区）
順位　　会社名　　　　売上(1)　　売上(2)
1　　　　A社　　　　　　100　　　　200　　
3　　　　C社　　　　　　80　　　　　180

シート3（港区）　
2　　　　B社　　　　　　　90　　　　190

上記のようにシート1に入力した情報を、シート2（品川区）・シート3（港区）のように区ごとに各社の情報を自動反映（並び替え）させるにはどうしたらいいでしょうか。　

区ごとに並び替えたのち、順位が高い順に並び替えたいのですが・・・

質問が下手で申し訳ないのですが、ご教授願います。

※エクセルは2003を使用しています。

回答No.3

kagakusuki

関数と作業列を使用して、全自動で反映させる方法です。

　今仮に、Sheet1の順位と入力されているセルがA1セルであるものとし、
Sheet2のB1セルに品川区等の住所を入力すると、
Sheet4のA列～E列を作業列として使用して、
Sheet2のA列～D列の3行目以下に抽出結果を表示するものとします。

　まず、Sheet4のA1セルに次の数式を入力して下さい。

=IF(COUNT(OFFSET(Sheet1!$D$1:$E$1,ROW()-1,))=0,"",SUM(OFFSET(Sheet1!$D$1:$E$1,ROW()-1,)))

　次に、Sheet4のB1セルに次の数式を入力して下さい。

=IF($A1="","",$A1+COUNTIF($A$1:$A1,$A1)/COUNTIF($A:$A,$A1))

　次に、Sheet4のD1セルに次の数式を入力して下さい。

=IF(ROW()>COUNT($B:$B),"",LARGE($B:$B,ROW()))

　次に、Sheet4のE1セルに次の数式を入力して下さい。

=IF($D1="","",INDEX(Sheet1!$C:$C,MATCH($D1,$B:$B,0)))

　次に、Sheet4のC1セルに次の数式を入力して下さい。

=IF($E1="","",$E1&COUNTIF($E$1:$E1,$E1))

　次に、Sheet4のA1～E1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　次に、Sheet2の
A1セルに　　住所、
A2セルに　　順位、
B2セルに　　会社名、
C2セルに　　売上1、
D2セルに　　売上2、
と入力して下さい。
　次に、Sheet2のA3セルに次の数式を入力して下さい。

=IF(ROWS($3:3)>COUNTIF(Sheet1!$C:$C,$B$1),"",OFFSET(Sheet1!$A$1,MATCH(VLOOKUP($B$1&ROWS($3:3),Sheet4!$C:$D,2,0),Sheet4!$B:$B,0)-1,MATCH(A$2,Sheet4!$1:$1,0)-1))

　次に、Sheet2のA3セルをコピーして、Sheet2のB1～D3の範囲に貼り付けて下さい。
　次に、Sheet2のA3～D3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

　後は、Sheet2のB2セル（「住所」と入力されているセルの右隣のセル）に

品川区

等の住所を入力すると、A列～D列の3行目以下に、抽出結果が売上の合計が多い順に表示されます。

　尚、Sheet1の売上の種類がもっと多数あり、しかも数が増え続けていて、売上の合計を計算する際に、何列目までを計算に含めるのか未定の場合には、
Sheet4のA1セルに入力する数式を次の様に変えて下さい。

=IF(COUNT(OFFSET(Sheet1!$D$1,ROW()-1,,1,MATCH("゛",Sheet1!$1:$1,-1)-COLUMN(Sheet1!$C$1)))=0,"",SUM(OFFSET(Sheet1!$D$1,ROW()-1,,1,MATCH("゛",Sheet1!$1:$1,-1)-COLUMN(Sheet1!$C$1))))

　因みに、もしSheet1の元データが、最初から売上順に並んでいる事が保証されている場合には、作業列を使用する事なく、関数のみで抽出させる事も、一応は可能ですが、数式が更に複雑になるたまめ、今回は割愛致します。

回答No.2

tom04

こんばんは！
Sheet1にすでにデータ入力後の操作としています。
VBAでの一例です。

Alt＋F11キーを押す　→　VBE画面がでますので、標準モジュールに↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test1() 'この行から
Dim i, j, k As Long
Dim ws1 As Worksheet
Set ws1 = Worksheets("sheet1") '←Sheet1のSheet名は適宜変更してください。
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(Range(ws1.Cells(2, 3), ws1.Cells(i, 3)), ws1.Cells(i, 3)) = 1 Then
Worksheets.Add after:=ActiveSheet
ActiveSheet.Name = ws1.Cells(i, 3)
End If
Next i
For j = 2 To Worksheets.Count
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
For k = 1 To 5
If ws1.Cells(i, 3) = Worksheets(j).Name Then
Worksheets(j).Cells(1, k) = ws1.Cells(1, k)
Worksheets(j).Cells(Rows.Count, k).End(xlUp).Offset(1) = ws1.Cells(i, k)
End If
Next k
Next i
Next j
For j = 2 To Worksheets.Count
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
If ws1.Cells(i, 3) = Worksheets(j).Name Then
If WorksheetFunction.CountIf(Worksheets(j).Columns(1), ws1.Cells(i, 1)) = 0 Then
Worksheets(j).Cells(Rows.Count, k).End(xlUp).Offset(1) = ws1.Cells(i, k)
End If
End If
Next i
Next j
For j = 2 To Worksheets.Count
k = Worksheets(j).Cells(Rows.Count, 1).End(xlUp).Row
Range(Worksheets(j).Cells(2, 1), Worksheets(j).Cells(k, 5)).Sort key1:= _
Worksheets(j).Cells(2, 1), order1:=xlAscending
Next j
End Sub 'この行まで

もしSheet1のデータが今後も増えて、各Sheetに振り分けしたい場合は別方法を考える必要があります。

この程度ですが参考にならなかったらごめんなさいね。m(__)m

回答No.1

web2525

ピポットテーブルで集計する

http://www11.plala.or.jp/koma_Excel/pivot_menu.html

慣れない内は項目の配置など分かり辛く感じるかもしれませんが、表の集計には便利です