PHP  エクセル10倍活用術              操作編

 ・詳細な条件指定(詳細設定)をして抽出する

 


操作の概要

 ここでは、フィルタの詳細設定を利用した詳細な抽出条件指定による抽出方法を説明します。
 ここでは以下6つの場合に分けて、代表的な例で具体的な操作方法を説明します。

 それぞれの操作説明では全て新規にそれぞれの抽出条件を設定するものとして説明します。
  但し詳細説明は@で行います(A以降、同じ操作は、少し簡略化します)

   @同じ列に複数の抽出条件を指定する(地区が大阪府と京都府のみを抽出)
   A複数列に1つの抽出条件を指定する
                   (スーパーの調味料類の金額が10万より大きいものを抽出)
   B列ごとに抽出条件を指定する(ドライパン粉か食材類かで、数量が1000以上を抽出)
   C複数の列に2セット以上の抽出条件を指定する
               (大阪府か京都府か兵庫県のスーパーの金額が50万以上を抽出)
   D1つの列に2セット以上の抽出条件を指定する
           ( 大阪府のスーパーの売上額が50万以上100万以下と1万より下を抽出)
   E数式の計算結果を抽出条件に指定する(金額が平均より大きいデータを抽出
   



  • 「フィルタの詳細設定」を利用した抽出 では、『データを抽出する元の表』とは別に『抽出条件範囲』を指定します。
     

  • その『抽出条件範囲』を利用して、元の表よりデータを抽出します。
     

  • 『抽出条件範囲』は、基本的に『元の表』の上に作成します。
    また、それらの間には、最低1行以上の空白行が必要です。

    同じブックの別シートでも、オープンしてあれば別ブックのシートにでも、抽出条件範囲は作成し、利用出来ます。
    ここでは、両方が見える、同じシート内の表の上に抽出条件範囲を作成し説明します。

  • 『抽出条件範囲』には、元の表と同じ列(項目)見出しを付ける必要があります。
     

  • 「フィルタ の詳細設定」を利用した抽出では、1度で複雑な抽出条件設定が出来ます。
    上記@〜Eの基本的な考え方と操作の組み合わせで、いろいろな複雑な条件が指定出来ます。

 

操作の流れ@−地区名が大阪府と京都府のみを抽出

『抽出条件範囲』を用意します−1

  • ここでは下図のような表があります (333件のデータがあります)。
    地区名に2つの条件をつけたいので、見出し行(1行)+条件行(2行)+空白行(1行)の最低4行が『抽出条件範囲』として必要です。
  1. ここでは、この表の上に5行挿入して、『抽出条件範囲』行として用意します。

『抽出条件範囲』を用意します−2

  1. 6行目の項目名を1行目にコピーします。

    『抽出条件範囲』には、条件となる項目名のみのコピーでも操作は可能ですが、ここでは全体の抽出条件設定状況が良くわかるように、全ての項目名をコピーして準備します。

抽出条件を指定します

 ここで指定したい抽出条件 は「大阪府」と「京都府」です。

 このように、「いずれかに一致するデータを抽出したい場合」には、同じ列の別の行に、抽出条件を設定します。
 
  1. 『抽出条件範囲』の「地区名」の列の別の行に、下図のように「大阪府」と「京都府」と入力します。

  2. その上で、元の表内の1ヶ所をクリックしておきます。

    これで条件指定は終了です。
    元の表内の1ヶ所をクリックしておくのは、下記ワンポイントで説明しているように自動的に範囲指定してくれるからです 。

(フィルタの)詳細設定を指定します

  1. 【データ】タブ→[並べ替えとフィルタ−]の【詳細設定】とクリックします。

詳細設定をします− 1

  1. 「フィルタオプションの設定」ダイアログが表示されます。

 「フィルタ −オプションの設定」ダイアログについて説明します。

1.【抽出先】
   ・規定値は「選択範囲内」です。
    この意味は、「抽出結果を「基の表」の上に置き換えて表示す
    る」という事です。
   ・「指定した範囲」を選択した場合
    今灰色になっている「抽出範囲」の欄で、抽出結果の表示場所
    を指定出来ます。
    別シートにでも、別ブック(オープンしておく必要があります)に
    も、同じシートの元の表などの重複しない場所を指定出来ます。
    指定したセルが抽出結果表の左上のセルとなります。

2.【リスト範囲】
   ・これは抽出される、元の表の範囲の意味です。
    操作4で、元の表の表内の1つのセルをクリックしてあれば、自
    動的に範囲を感知して、ここに表示くれます。
    その場合には、ここは指定する必要がありません。

3.【抽出条件範囲】
   ・ここはその名の通り、抽出条件を設定した範囲を指定します。

4.【重複するレコードは無視する】
   ・ここにチェックをつけると、元の表から重複するデータがある
    場合には、無視して抽出しません。
 

 

詳細設定をします−2

  • ここでは、抽出先は既定値のまま、元の表上に表示させる事とします。 また重複するレコードは無視しない事とします。
    ですから、ここでは、「検索条件範囲」のみ指定します。
  1. 「検索条件範囲」の欄の横の  をクリックします。

  2. この欄のみが浮動状態で小さく表示されます。

  3. 検索条件範囲のA1〜H3の範囲をドラッグで指定します。
    (または、ここではB1〜B3でも結構です)

  4. 条件範囲が表示されたら【Enter】キーで決定します。
    (または、再度  をクリックします )

詳細設定をします−3

  1. 「フィルタ−オプションの設定」ダイアログに戻ります。

  2. 指定は完了なので、【OK】をクリックします。

抽出結果が表示されました

  • 今条件とした「大阪府」と「京都府」のデータ229件が表示されました。

−−−−−−

抽出結果を解除して、元の表にもどす方法

  1. 今の【データ】タブ→[並べ替えとフィルタ]の【クリア】とクリックします。
  2. 抽出条件が解除されて、元の表に戻ります。

 


 

操作の流れA−スーパーの調味料類の金額が10万より大きい

『抽出条件範囲』を用意します

  • ここでは下図のような表があります (333件のデータがあります)。
    ここでは、見出し行(1行)+条件行(1行)+空白行(1行)の最低3行が『抽出条件範囲』として必要です。
  1. ここでは、この表の上に5行挿入して、『抽出条件範囲』行として用意します。

  2. そして、6行目を1行目にコピーして、項目名を作成します。

抽出条件を指定します

 ここで指定したい抽出条件 は「スーパー」の「調味料類」の金額が「10万より大きい」です。

 このように、「すべてに一致するデータを抽出したい場合」には、同じ行に、抽出条件を設定します。
 
  1. 『抽出条件範囲』の「業種名」のC列の2行目に、「スーパー」と入力します。

  2. 「商品群名」のE列の2行目(操作3と同じ行)に、「調味料類」と入力します。

  3. 「金額」のH列の2行目(操作3と同じ行)に、「>100000」と入力します。

  4. その上で、元の表内の1ヶ所をクリックしておきます。

    これで条件指定は終了です。

詳細設定を指定します

  1. 【データ】タブ→[並べ替えとフィルタ−]の【詳細設定】とクリックします。

詳細設定します−1

  • ここでは、抽出先は既定値のまま、元の表上に表示させる事とします。 また重複するレコードは無視しない事とします。
    ですから、ここでは、「検索条件範囲」のみ指定します。
  1. 「フィルタ−オプションの設定」ダイアログが表示されます。

  2. 「検索条件範囲」の欄の横の  をクリックします。

  3. この欄のみが浮動状態で小さく表示されます。

  4. 検索条件範囲のA1〜H2の範囲をドラッグで指定します。
    (または、ここではC1〜H2でも結構です)

  5. 条件範囲が表示されたら【Enter】キーで決定します。
    (または、再度  をクリックします )

詳細設定します−2

  1. 「フィルタ−オプションの設定」ダイアログに戻ります。

  2. 指定は完了なので、【OK】をクリックします。

抽出結果が表示されました

  • 今条件とした「スーパーの調味料類の10万より大きい」データが5件が表示されました。

−−−−−−

抽出結果を解除して、元の表にもどす方法

  1. 今の【データ】タブ→[並べ替えとフィルタ−]の【クリア】とクリックします。
  2. 抽出条件が解除されて、元の表に戻ります。


 

 

操作の流れB− ドライパン粉か食材類かで、数量が1000以上を抽出

『抽出条件範囲』を用意します

  • ここでは下図のような表があります (333件のデータがあります)。
    ここでは、見出し行(1行)+条件行(3行)+空白行(1行)の最低5行が『抽出条件範囲』として必要です。
  1. ここでは、この表の上に5行挿入して、『抽出条件範囲』行として用意します。

  2. そして、6行目を1行目にコピーして、項目名を作成します。

抽出条件を指定します

ここで指定したい抽出条件 は「ドライパン粉」または「食材類」または数量が「1000以上」の、いずれかのデータです。

・このように、「いずれかに一致するデータを全てを抽出したい場合」には、別々のに、抽出条件を設定します。
  1. 『抽出条件範囲』の「商品名」のD列の2行目に、「ドライパン粉」と入力します。

  2. 「商品群名」のE列の3行目(操作3と違う行)に、「食材類」と入力します。

  3. 「数量」のG列の4行目(操作3とも操作4とも違う行)に、「>=1000」と入力します。

  4. その上で、元の表内の1ヶ所をクリックしておきます。

    これで条件指定は終了です。

詳細設定を指定します

  1. 【データ】タブ→[並べ替えとフィルタ−]の【詳細設定】とクリックします。

詳細設定します−1

  • ここでは、抽出先は既定値のまま、元の表上に表示させる事とします。 また重複するレコードは無視しない事とします。
    ですから、ここでは、「検索条件範囲」のみ指定します。
  1. 「フィルタ−オプションの設定」ダイアログが表示されます。

  2. 「検索条件範囲」の欄の横の  をクリックします。

  3. この欄のみが浮動状態で小さく表示されます。

  4. 検索条件範囲のD1〜G4の範囲をドラッグで指定します。
    (または、ここではA1〜G4でも結構です)

  5. 条件範囲が表示されたら【Enter】キーで決定します。
    (または、再度  をクリックします )

詳細設定します−2

  1. 「フィルタ−オプションの設定」ダイアログに戻ります。

  2. 指定は完了なので、【OK】をクリックします。

抽出結果が表示されました

  • 今条件とした「ドライパン粉か食材類か数量が1000以上」のいずれかに該当するデータが40件が表示されました。

−−−−−−

抽出結果を解除して、元の表にもどす方法

  1. 今の【データ】タブ→[並べ替えとフィルタ−]の【クリア】とクリックします。
  2. 抽出条件が解除されて、元の表に戻ります。

 



操作の流れC−大阪府か京都府か兵庫県のスーパーの金額が50万以上を抽出

『抽出条件範囲』を用意します

  • ここでは下図のような表があります (333件のデータがあります)。
    ここでは、見出し行(1行)+条件行(3行)+空白行(1行)の最低5行が『抽出条件範囲』として必要です。
  1. ここでは、この表の上に5行挿入して、『抽出条件範囲』行として用意します。

  2. そして、6行目を1行目にコピーして、項目名を作成します。

抽出条件を指定します

 ここで指定したい抽出条件 は丁寧に言えば「 大阪府のスーパーの金額が50万以上」と「京都府のスーパーの金額が50万以上」と「兵庫県のスーパーの金額が50万以上」の 、すべてのデータを抽出するという事です。

 ここでは、たまたま同じ業種の同じ金額にしていますが、別業種の別金額でも勿論OKです。 また同じ行の別の列にさらに条件設定も出来ます。例えば、大阪府の条件の2行目に、更に(E2に)粉類のという条件を付加しても、同じ考え方で同じ操作で条件指定が出来ます。

−−−−

 ここでお判りのように、同じ行に指定する条件は、AND条件です
               別の行に指定する条件は、OR条件です。

 AND条件とOR条件について図示して説明します。

  • 「AND」は、『なおかつ』の意味です。
    上図では「青色でなおかつ赤色の部分=重なっている紫色の部分」の意味です。
  • 「OR」は、『または』の意味です。
    上図では、青色又は、紫色又は、赤色の部分の意味です。

−−−−

 このように、「抽出条件セット(同じ行に指定した条件群)のいずれかに一致するデータを全てを抽出したい場合」には、別々の行 に、それぞれその抽出条件セットを指定します。
 

  1. 『抽出条件範囲』の2行目に、「地区名」のB2に「大阪府」 、「業種名」のC2に「スーパー」、金額のH2に「>=500000」と入力します。

  2. 『抽出条件範囲』の3行目に、「地区名」のB3に「京都府」 、「業種名」のC3に「スーパー」、金額のH3に「>=500000」と入力します。

  3. 『抽出条件範囲』の4行目に、「地区名」のB4に「兵庫県」 、「業種名」のC4に「スーパー」、金額のH4に「>=500000」と入力します。

  4. その上で、元の表内の1ヶ所をクリックしておきます。

    これで条件指定は終了です。

詳細設定を指定します

  1. 【データ】タブ→[並べ替えとフィルタ−]の【詳細設定】とクリックします。

詳細設定します−1

  • ここでは、抽出先は既定値のまま、元の表上に表示させる事とします。 また重複するレコードは無視しない事とします。
    ですから、ここでは、「検索条件範囲」のみ指定します。
  1. 「フィルタオプションの設定」ダイアログが表示されます。

  2. 「検索条件範囲」の欄の横の  をクリックします。

  3. この欄のみが浮動状態で小さく表示されます。

  4. 検索条件範囲のB1〜H4の範囲をドラッグで指定します。
    (または、ここではA1〜H4でも結構です)

  5. 条件範囲が表示されたら【Enter】キーで決定します。
    (または、再度  をクリックします )

詳細設定します−2

  1. 「フィルタ−オプションの設定」ダイアログに戻ります。

  2. 指定は完了なので、【OK】をクリックします。

抽出結果が表示されました

  • 今条件とした「大阪府のスーパーの金額が50万以上」または「京都府のスーパーの金額が50万以上」 または「兵庫県のスーパーの金額が50万以上」のいずれかに該当するデータが10件が表示されました。

−−−−−−

抽出結果を解除して、元の表にもどす方法

  1. 今の【データ】タブ→[並べ替えとフィルタ−]の【クリア】とクリックします。
  2. 抽出条件が解除されて、元の表に戻ります。

 




操作の流れD−大阪府のスーパーの売上額が50万以上100万以下と1万より下を抽出

『抽出条件範囲』を用意します

  • ここでは下図のような表があります (333件のデータがあります)。
    ここでは、見出し行(1行)+条件行(2行)+空白行(1行)の最低4行が『抽出条件範囲』として必要です。
  1. ここでは、この表の上に5行挿入して、『抽出条件範囲』行として用意します。

  2. そして、6行目を1行目にコピーして、項目名を作成します。

  3. ここでは、金額の項目を「○○以上と、○○以下」という2つの条件指定をしたいので、I列にもう1つ「金額」の条件項目をコピーして
    おきます。

大阪府のスーパーの金額が50万以上100万以下と1万以下を抽出条件とします

  1. 2行目の、B列 に「大阪府」、C列に「スーパー」、H列に「>=500000」、I列に「<=1000000」をそれぞれ入力します。

  2. 3行目の、B列に「大阪府」、C列に「スーパー」、H列に「<10000」と入力します。

  3. その上で、元の表内の1ヶ所をクリックしておきます。

    これで、「大阪府のスーパーの金額が50万以上100万以下または1万より下」のデータの抽出条件となります。
     

     抽出条件の「AND」と「OR」について下図を利用して 、再度説明します。

    • 同じ行に指定する条件は、AND条件です。
      別の行に指定する条件は、OR条件です。

       
    • 「AND」は、『なおかつ』の意味です。
      上図では「青色でなおかつ赤色の部分=重なっている紫色の部分」の意味です。
    • 「OR」は、『または』の意味です。
      上図では、青色又は、紫色又は、赤色の部分の意味です。

詳細設定を指定します

  1. 【データ】タブ→[並べ替えとフィルタ−]の【詳細設定】とクリックします。

詳細設定します−1

  • ここでは、抽出先は既定値のまま、元の表上に表示させる事とします。 また重複するレコードは無視しない事とします。
    ですから、ここでは、「検索条件範囲」のみ指定します。
  1. 「検索条件範囲」の欄の横の  をクリックします。

  2. この欄のみが浮動状態で小さく表示されます。

  3. 検索条件範囲のA1〜I3の範囲をドラッグで指定します。
    (または、ここではB1〜I3でも結構です)

  4. 条件範囲が表示されたら【Enter】キーで決定します。
    (または、再度  をクリックします )

詳細設定します−2

  1. 「フィルタオプション−の設定」ダイアログに戻ります。

  2. 指定は完了なので、【OK】をクリックします。

抽出結果が表示されました

  • 今条件とした「大阪府のスーパーの金額が50万以上で100万以下と1万より下」に該当するデータが39件が表示されました。

−−−−−−

抽出結果を解除して、元の表にもどす方法

  1. 今の【データ】タブ→[並べ替えとフィルタ−]の【クリア】とクリックします。
  2. 抽出条件が解除されて、元の表に戻ります。

 


操作の流れE−金額が平均より大きいデータを抽出

『抽出条件範囲』を用意します

  • ここでは下図のような表があります (333件のデータがあります)。
    ここでは、見出し行(1行)+条件行(1行)+空白行(1行)の最低3行が『抽出条件範囲』として必要です。
  1. ここでは、この表の上に5行挿入して、『抽出条件範囲』行として用意します。

  2. そして、6行目を1行目にコピーして、項目名を作成します。
    (この操作は本当は必要ないのですが、別項目を作る事を説明する為に、分かり易いように操作しました)

  3. 数式を設定する為の新規項目(ここでは「抽出条件」と名付けました)を I1に作成します。
     

     ○数式を抽出条件 にする場合には、列見出しを抽出条件のラベルとして指定する事は出来ません。

     その為、元の表の項目名とは別の名前をつけた項目名が必要です

     この数式のみの抽出をする場合には、上記操作2の操作は必要ありません。
     今つけた新規項目名の「抽出条件」の項目名をA1に作成して、そこからの操作でOKです。

     ここで、あえて操作1〜2を行っているのは、数式やその他の項目の条件を複合的に利用する場合に便利な為です。
     (このような、元の表の項目名+新規項目名を用意しておくと、すぐに抽出条件をいろいろと変更しての抽出が出来るからです)

     ・ここでは、このまま説明を続けます。
     

金額が平均以上という数式を抽出条件とします

  1. 2行目の、I列に「=H7>AVERAGE($H$7:$H$338)」と入力します。
    (下図では、入力状態を示す為に、あえてI2をクリックしています)

  2. その上で、元の表内の任意のセルを1つクリックしておきます。

この条件式について説明します。
  1. 「H7」の意味
    H列を指定したいので、代表として先頭のセルを指定しているだけです。
    「H10」でも良く、H列の中のデータがある任意のセルでOKです。
     
  2. 平均の範囲($H$7:$H$338)について
    今平均を求めたい、H列のH7〜H338を絶対参照としたいので、「$」をそれぞれにつけています。

 これで、「H列の平均値以上のデータを抽出する」という条件になります。

◎数式を入力すると、下図のようにI2のセルには、ここでは「FALSE」が表示されます。
 他に数式により、いろいろなエラー値が表示されるますが、問題なく抽出出来ます。
 

 

詳細設定を指定します

  1. 【データ】タブ→[並べ替えとフィルタ−]の【詳細設定】とクリックします。

詳細設定します−1

  • ここでは、抽出先は既定値のまま、元の表上に表示させる事とします。 また重複するレコードは無視しない事とします。
    ですから、ここでは、「検索条件範囲」のみ指定します。
  1. 「検索条件範囲」の欄の横の  をクリックします。

  2. この欄のみが浮動状態で小さく表示されます。

  3. 検索条件範囲のA1〜I2の範囲をドラッグで指定します。
    (または、ここではI1〜I2でも結構です)

  4. 条件範囲が表示されたら【Enter】キーで決定します。
    (または、再度  をクリックします)

詳細設定します−2

  1. 「フィルタ−オプションの設定」ダイアログに戻ります。

  2. 指定は完了なので、【OK】をクリックします。

 

抽出結果が表示されました

  • 今条件とした「金額が平均より大きい」に該当するデータが48件が表示されました。

−−−−−−

抽出結果を解除して、元の表にもどす方法

  1. 今の【データ】タブ→[並べ替えとフィルタ−]の【クリア】とクリックします。
  2. 抽出条件が解除されて、元の表に戻ります。