1. Home » 
2. エクセル練習問題：目次 » 
3. 該当するデータを別シートに書き出す

エクセル練習問題：該当するデータを別シートに書き出す

更新:2024/5/14;作成:2014/11/29

問題1の解答例　　　　topへ

FILTER関数を使って求める 　　　　topへ

• Excel for Microsoft365、Excel2021ではFILTER関数が使えます。
  FILTER関数の詳細な使い方は　FILTER関数でデータを抽出する をご覧ください。
• TEXTAFTER関数はExcel for Microsoft365で使える新しい関数です。
  TEXTBEFORE関数・TEXTAFTER関数の使い方をご覧ください。

TEXTAFTER関数が使える場合

• =CELL("filename",A1)　でファイルの置き場所とファイル名、シート名が表示されます。
  こちらの環境では、
  C:\Users\user1\Documents\HP用データファイル\HP-MS365\[HP-365_練習問題_別シートの書き出す.xlsx]Sheet1
  といった感じです。
  シート名を取り出すにはTEXTAFTER関数で、ファイル名の ]の後ろの文字列を取り出せるようになりました。
  =TEXTAFTER(CELL("filename",A1),"]")

1. データを抽出するシート　「朝」〜「晩」を選択します。(作業グループにします)
   なお、シート「朝」〜「晩」を作成したら、一旦ファイルを保存しておいてください。
   A2セルに
   =FILTER(Sheet1!A1:B12,
   Sheet1!C1:C12=TEXTAFTER(CELL("filename",A1),"]"))
   と入力します。
   「朝」のデータがシート「朝」に抽出できました。
   
2. 一旦、Sheet1のシート見出しをクリックして、作業グループを解除します。
   シート「昼」を選択すると、A2セルに数式が入力され、データが抽出されています。
   
3. シート「晩」を選択すると、A2セルに数式が入力され、データが抽出されています。
   

FILTER関数が使える場合

• TEXTAFTER関数が使えない場合は、シート名を
  =RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
  のような数式で求める必要があります。
• ちょっと面倒なので、シートごとに数式中のシート名に直接入力する方法を書いてみます。

1. データを抽出するシート　「朝」〜「晩」を選択します。(作業グループにします)
   A2セルに　=FILTER(Sheet1!A2:C13,Sheet1!C2:C13="朝")　と入力しました。
   「朝」のデータが抽出できました。
   
2. 一旦、シート見出しの「Sheet1」を選択して、作業グループを解除します。
   シート「昼」を選択します。
   =FILTER(Sheet1!A2:C13,Sheet1!C2:C13="昼")　と修正します。
   「昼」のデータが抽出できました。
3. シート「晩」を選択します。
   =FILTER(Sheet1!A2:C13,Sheet1!C2:C13="晩")　と修正します。
   「晩」のデータが抽出できました。

数式を使って書き出す方法

• ここでは、作業列を使って書き出すことにします。
  D2セルに　=C2&"_"&COUNTIF($C$2:C2,C2)　として、下方向へコピーします。
  
• 書き出すシートには　朝のデータの場合は
  =IFERROR(INDEX(Sheet1!$A$2:$C$13,
  MATCH("朝_"&ROW(A1),
  Sheet1!$D$2:$D$13,0),COLUMN(A1)),"")
  と入力しています。
  横方向と下方向へデータがある分以上コピーします。
  
• 書き出すシートには　昼のデータの場合は
  =IFERROR(INDEX(Sheet1!$A$2:$C$13,
  MATCH("昼_"&ROW(A1),
  Sheet1!$D$2:$D$13,0),COLUMN(A1)),"")
  と入力しています。
  
• 書き出すシートには　晩のデータの場合は
  =IFERROR(INDEX(Sheet1!$A$2:$C$13,
  MATCH("晩_"&ROW(A1),
  Sheet1!$D$2:$D$13,0),COLUMN(A1)),"")
  と入力しています。
  

作業列を使わず、配列数式で求めることもできます。

• 朝の例です。昼、晩は赤字の2カ所を書き換えます。
  =IF(COUNTIF(Sheet1!$C$2:$C$20,"朝")<ROW(A1),"",INDEX(Sheet1!$A$2:$C$20,SMALL(IF(Sheet1!$C$2:$C$20="朝",ROW($A$1:$A$19)),ROW(A1)),COLUMN(A1)))
  と入力して、[Shift]+[Ctrl]+[Enter]で配列数式として確定します。
  

ピボットテーブルを利用する方法

1. データリスト内のセルを選択します。
   [挿入]タブの[ピボットテーブル]をクリックします。
   
2. テーブル/範囲　が正しいことを確認します。
   「新規ワークシート」にチェックが入っているのを確認して、[OK]ボタンをクリックします。
   
3. 列ラベルに「希望講座」、行ラベルに「氏名」、Σ値に「希望講座」を配置しました。
   総計のセルをダブルクリックすることで、該当するデータが別シートに書き出されます。(ドリルダウンという機能を利用します。)
   
4. 下図のようにデータが別シートに書き出されました。
   • 朝のコース
     
   • 昼のコース
     
   • 晩のコース
     

VBA(マクロ)を使って処理する方法

• 詳細は　Excel(エクセル) VBA入門：データを振り分ける をご覧ください。
• ここでは、上記ページのtest1を利用することにしました。
  ここの例ではC列の値を基にデータを振り分けるので、コードの一部を書き換えます。
  

  Sub test1() 　Dim i As Long 　Dim lastRow As Long 　Dim mySh As Worksheet 　Dim myFlg As Boolean 　Dim myRow As Long 　Dim myKey As String 　　lastRow = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row 　　For i = 2 To lastRow 　　　　'----振り分け先のシートが存在するか否かをチェック 　　　　For Each mySh In Worksheets 　　　　　　myFlg = False 　　　　　　myKey = Worksheets("Sheet1").Range("C" & i).Value 　　　　　　If mySh.Name = myKey Then 　　　　　　　　myFlg = True 　　　　　　　　mySh.Cells.Delete 　　　　　　　　Exit For 　　　　　　End If 　　　　Next mySh 　　　　'----振り分け先のシートがなかったらシートを追加する 　　　　If myFlg = False Then 　　　　　　ActiveWorkbook.Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = myKey 　　　　End If 　　　　'----列見出しをコピー&貼り付け 　　　　Worksheets("Sheet1").Range("A1:C1").Copy Worksheets(myKey).Range("A1") 　　Next i 　　　　'----データを転記する 　　　　For i = 2 To lastRow 　　　　　　myKey = Worksheets("Sheet1").Range("C" & i).Value 　　　　　　If myKey <> "" Then 　　　　　　　　myRow = Worksheets(myKey).Range("A" & Rows.Count).End(xlUp).Row + 1 　　　　　　　　Worksheets("Sheet1").Range("A" & i & ":C" & i).Copy _ 　　　　　　　　Worksheets(myKey).Range("A" & myRow & ":C" & myRow) 　　　　　　End If 　　　　Next i End Sub

• 実行後は下図のようになりました。
  
• 
• 

スポンサードリンク

PageViewCounter

Since2006/2/27