1. よねさんのExcelとWordの使い方 » 
2. エクセル2013基本講座：目次 » 
3. ピボットテーブル » 
4. ピボットテーブルでクラス別名簿を作成する

エクセル2013基本講座：ピボットテーブルでクラス別名簿を作成する

ピボットテーブルでクラス別名簿を作成する　　　　　Topへ

• ピボットテーブルを使って、クラス別の名簿を他のシートに書き出す方法です。
  関数を使って取り出すことも可能です。またはオートフィルターを使ってコピー&貼り付けで作成することもできます。またはVBAを使うこともあるかもしれません。
  ここでは、簡単な操作でクラス別の名簿を作成する一つの方法として、ピボットテーブルを利用してみます。
• 操作手順
  1. ピボットテーブル の「岡田」の小計が表示されているセルを選択しています。
     下図のような全体の名簿を作成します。ここでは1つの学年を想定しており、1組〜3組に分けた名簿を作成します。
     
  2. ピボットテーブルを作成します。
     [挿入]タブの[ピボットテーブル]をクリックします。
     
  3. テーブルの範囲が正しいか確認します。
     ピボットテーブルの配置する場所は「新規ワークシート」としました。
     
  4. フィールドの配置は下図のようにします。
     フィルターに「クラス」を配置します。
     行に「No」「氏名」「性別」など名簿に表示するフィールドを配置します。
     
  5. ピボットテーブル ツールの[デザイン]タブを選択します。
     [レポートのレイアウト]→[表形式で表示]を選択します。
     
  6. 小計は不要なので、[小計]→[小計を表示しない]を選択します。
     
  7. 総計も不要なので、[総計]→[行と列の集計を行わない]を選択します。
     
  8. クラスは「(すべて)」を選択します。
     
  9. ピボットテーブル ツールの[分析]タブを選択します。
     [オプション]→[レポートフィルター ページの表示]を実行します。
     
  10. 各クラスごとにクラス名のシートが作成されました。
      下図はクラス「1」の名簿です。ほかのクラスも同様にシートが作成されています。
      
      • シート「2」に作成されたクラス「2」の名簿
        
      • シート「3」に作成されたクラス「3」の名簿
        
  11. 展開/折りたたみボタンが邪魔になる場合は、ピボットテーブル ツールの[分析]タブを選択します。
      [オプション]をクリックします。
      
  12. [表示]タブを選択して、「展開/折りたたみボタンを表示する」のチェックを外します。
      
  13. 下図のような名簿が作成できました。
      

• 複数の学年の名簿を作成したい場合

  1. 下図のように学年とクラスのフィールドがあるデータを作成します。
     
  2. ピボットテーブルの各フィールドの配置は下図のようにします。
     
  3. 学年で「1」を選択します。クラスは「（すべて）」を選択します。
     ピボットテーブル ツールの[分析]タブを選択します。
     [オプション]→[レポートフィルター ページの表示]を実行します。
     
  4. 「クラス」を選択します。
     
  5. 1年1組〜3組の名簿が作成できました。
     なお、シート名はクラスの数字「1」〜「3」となりますが、上記ですでにシート名「1」〜「3」があるので、「1(2)」〜「3(2)」となっています。
     
  6. 学年「2」の名簿は学年で「2」を選択します。クラスは「（すべて）」を選択します。
     ピボットテーブル ツールの[分析]タブを選択します。
     [オプション]→[レポートフィルター ページの表示]を実行します。
     
  7. 「クラス」を選択します。
     
  8. 下図のように学年2の名簿が作成されます。
     

関数でクラス別名簿を作成する　　　　　Topへ

• 関数を使って、クラス別名簿を作成する例です。
  1. 作業列を作成します。
     作業列には　=(D2&TEXT(COUNTIF($D$2:D2,D2),"00"))+0　と入力しています。
     クラス&2桁の通しナンバーとして、数値に直しています。
     • 作業列の数式は　D2*100+COUNTIF($D$2:D2,D2)　の方が分かりやすいかもしれませんね。
     
  2. 1組の数式は　=IFERROR(INDEX(Sheet1!$A$2:$F$90,MATCH(100+ROW(A1),Sheet1!$G$2:$G$90,0),COLUMN(A1)),"")　として、右方向および下方向へ必要な行以上にコピーします。
     1組の場合は　作業列で101、102、103・・・「100+ROW(A1)」と一致する行をMATCH関数で求めます。
     取り出すデータは　Sheet1!$A$2:$F$90　なので、COLUMN(A1)で各列を指定しています。
     エラーの場合は"" とするように、IFERROR関数で処理しています。
     
  3. 2組の数式は　=IFERROR(INDEX(Sheet1!$A$2:$F$90,MATCH(200+ROW(A1),Sheet1!$G$2:$G$90,0),COLUMN(A1)),"")　
     3組の数式は　=IFERROR(INDEX(Sheet1!$A$2:$F$90,MATCH(300+ROW(A1),Sheet1!$G$2:$G$90,0),COLUMN(A1)),"")
     となります。

VBAでクラス別名簿を作成する　　　　　Topへ

• VBAを使って、クラス別名簿を作成する例です。
  • あらかじめクラス別名簿の書き出し先のシート「1組」「2組」「3組」のシートを作成しておきます。
    また各シートの1行目には列見出しを作成しているという前提です。
    
  • コードの例
    • クラスの最大人数は50としています。
      各クラス別に配列を準備して、その各配列にデータを入れてから、各シートの書き出しています。

    Sub meibo() 　Dim gakunen 　Dim class1(1 To 50, 1 To 6) 　Dim class2(1 To 50, 1 To 6) 　Dim class3(1 To 50, 1 To 6) 　Dim cn1 As Integer, cn2 As Integer, cn3 As Integer 　Dim i As Long, j As Long 　　gakunen = Worksheets("名簿").Range("A2:F90").Value 　　For i = 1 To UBound(gakunen) 　　　　Select Case gakunen(i, 4) 　　　　　　Case 1 　　　　　　　　cn1 = cn1 + 1 　　　　　　　　For j = 1 To 6 　　　　　　　　　　class1(cn1, j) = gakunen(i, j) 　　　　　　　　Next j 　　　　　　Case 2 　　　　　　　　cn2 = cn2 + 1 　　　　　　　　For j = 1 To 6 　　　　　　　　　　class2(cn2, j) = gakunen(i, j) 　　　　　　　　Next j 　　　　　　Case 3 　　　　　　　　cn3 = cn3 + 1 　　　　　　　　For j = 1 To 6 　　　　　　　　　　class3(cn3, j) = gakunen(i, j) 　　　　　　　　Next j 　　　　End Select 　　Next i 　　Worksheets("1組").Range("A2").Resize(UBound(class1), 6).Value = class1 　　Worksheets("2組").Range("A2").Resize(UBound(class2), 6).Value = class2 　　Worksheets("3組").Range("A2").Resize(UBound(class3), 6).Value = class3 End Sub

  • 実行後の例