Office Hack

エクセルで特定の文字を含む行を抽出し別シートに貼る

  • Release

エクセルで、表やデータベース内で特定の文字が含まれる行を別のシートに抽出する方法をご紹介します。

この方法を使うと、例えば全店舗の売上表から特定の店舗のみのデータを別シートに抽出したい場合などに役立てることができます。

以下では、フィルター機能を使う方法と関数を使う方法の2通りをご紹介します。

フィルター機能を使って抽出する方法

フィルター機能を使って、Sheet1のデータから条件に該当するデータが含まれた行をSheet2に抽出する方法をご紹介します。

作業時間:2分

データを準備する

別シートにデータを抽出する

今回は、上の画像の表を使って店舗名が「福岡支店」であるデータを行ごとSheet2に抽出する方法をご紹介します。

Sheet2に条件を入力する

条件を入力する

任意のセル(例:A1セルとA2セル)を選択し、項目名(例:店舗名)と条件(例:福岡支店)を入力します。

フィルターの詳細設定を選択する

フィルターの詳細設定を選択する

Sheet2のデータの抽出先のセル(例:A4セル)、②データタブ、③詳細設定の順に選択します。

データの範囲を選択する

セル番地を入力する

フィルター オプションの設定ダイアログボックスが表示されます。①「抽出先」で「指定した範囲」を選択します。②「リスト範囲」にSheet1のデータ範囲(例:Sheet1!$A$1:$D$16)、③「検索条件範囲」にSheet2の条件を入力したいセル(例:Sheet2!$A$1:$A$2)、④「抽出範囲」にSheet2内の抽出したデータを入力したいセル(例:Sheet2!$A$4)の順に入力します。⑤OKボタンを押します。

データが抽出される

データが抽出される

これで、条件にあうデータのみがSheet1からSheet2に抽出できました。

関数を使って抽出する方法

以下の関数を組み合わせることで、関数を使ってSheet1のデータから条件に該当するデータが含まれる行をSheet2に抽出することができます。

  • IF関数
  • MAX関数
  • ROW関数
  • INDEX関数
  • MATCH関数
  • COLUMN関数
別シートにデータを抽出する

今回は、上の画像の表を使って店舗名が「福岡支店」であるデータを行ごとSheet2に抽出する方法をご紹介します。

以下の方法を使えば、条件を再入力する度にその条件に該当するデータを自動的に抽出するように設定できます。

別シートに項目名を入力する

①今回は表から「福岡支店」のデータのみを抽出するので、上の画像のようにSheet2に条件名や項目名を任意のセルに入力しておきましょう。

Sheet1を選択します。

数式を入力する

Sheet1のデータベース以外の列(例:F列)に数式を入力します。

任意のセル(例:F2セル)を選択し、以下の数式を入力してEnterを押します。

=IF(B2=Sheet2!A$2,MAX(F$1:F1)+1,"")

「=IF(B2=Sheet2!A$2,MAX(F$1:F1)+1,"")」は、「Sheet2のA2セルに入力した条件(店舗名)がB2セルと同じだったら、同じ列で対象のセルよりも上に表示されている数字に1を足した数値を表示する」という意味の数式です。

IF関数の書式や使い方については以下の記事をご確認ください。

ExcelのIF関数の使い方|論理式の条件(IF文)によって処理を分岐

MAX関数の書式や使い方、条件付き書式と組み合わせる方法等については以下の記事をご確認ください。

エクセルのMAX関数の使い方|最大値を求める

オートフィルでコピーする

数式をオートフィルでコピーします。

数式を入力したセル(例:F2セル)を選択し、セルの右下角にある■(フィルハンドル)を下に向かってドラッグします。

別シートに移動する

「福岡支店」が含まれる行にのみ、数字が表示されました。

Sheet2を選択します。

数式を入力する

Sheet2で抽出したデータを表示したいセル(例:A5セル)を選択し、以下の数式を入力します。


=IF(ROW(A1)>MAX(Sheet1!$F:$F),"",INDEX(Sheet1!$A:$D,MATCH(ROW(A1),Sheet1!$F:$F,0),COLUMN(A1)))

上記の数式で、Sheet1でF列に数値が表示された行のみをSheet2に抽出することができます。

ROW関数については以下の記事をご参照ください。

ExcelのROW関数の使い方|セルの行番号を求める

INDEX関数の引数の詳細、基本的な使い方、使用例などについては以下の記事をご確認ください。

ExcelのINDEX関数の使い方|行と列が交差する位置にあるセルを返す

MATCH関数の使い方や、あいまいな条件で検索を行う方法については以下の記事をご確認ください。

ExcelのMATCH関数の使い方|検索値の範囲内での相対的な位置を返す

COLUMN関数の引数や基本的な使い方については以下の記事をご確認ください。

ExcelのCOLUMN関数の使い方|指定したセルの列番号を求める

あらかじめ入力した項目名に従ってコピーする

数式を必要な分だけ行方向にコピーします。

数式を入力したセル(例:A5セル)を選択し、セルの右下角にある■(フィルハンドル)を右に向かってドラッグします。

再度列方向にコピーする

次に、そのまま列方向にもコピーします。

セルの右下角にある■(フィルハンドル)を下に向かってドラッグします。

抽出が完了する

これで、関数を使ってSheet1のデータをSheet2に抽出することができました。

条件を変える

試しに、条件を変更してみましょう。

Sheet2の条件を入力したセル(例:A2セル)に、「大阪支店」と入力し、Enterを押します。

条件にあったデータが抽出される

Sheet2の抽出したデータが、「大阪支店」を含む行のデータに変わりました。

これで、関数を使って別シートにデータを抽出する設定は終了です。