オートフィルタを設定すると、通常は、リストの項目名にフィルタが設定され、リストの部分だけがフィルタの対象になっているかのように見えます。そのため、リストの外側はオートフィルタと無関係であると勘違いしている人が多いようです。勘違いです。
確かに、抽出されるのはリスト内のデータであり、リスト外のデータは抽出の対象ではありませんが、オートフィルタを設定したことによる効果はシート全体に及ぶということを忘れてはいけません。
そこで、今回は、オートフィルタモードがリスト外に及ぼす効果と、コピー貼り付けとの関係について出題します。
- オートフィルタの設定と解除、条件にあう行を抽出する方法の総復習
- オートフィルタをさらに深く理解するための応用事例演習
- オートフィルタモードの効果とコピー貼り付けとの関係
- 並べ替えは並べ替えるだけ、オートフィルタは行数を減らすだけです
目次
問題文のあとに簡単な操作方法を解説していますが、静止画では、わかりにくいと思いますので、最後に動画を載せています。ぜひご覧ください。
- 1.オートフィルタモード
- 2.行列の非表示とフィルタの非表示
- 3.フィルタが設定されていない列にも効果が及ぶ
- 4.オートフィルタとオートカルク
- 5.オートフィルタモードとコピー貼り付け
- 6.オートフィルタの可視セル選択
- 7.動画版はこちら(無料)
1.オートフィルタモード
例えば、オートフィルタを設定して抽出すると、抽出されなかった行は非表示になりますが、非表示になるのは行全体です。A~D列だけにオートフィルタを設定したとしても、非表示になるデータはA~D列だけではありません。フィルタの対象ではないE列以降も非表示になります(参考:【Excel】オートフィルタの設定と解除、条件にあう行を抽出する方法の総復習)。
また、抽出をしてから、リストの下の行(フィルタの対象ではない行)を非表示にすると、抽出の条件をクリアしなければ再表示をすることができません。
1つのシート内に複数のオートフィルタを設定することはできません。これは、オートフィルタを設定した時点で、フィルタの効果がシート全体に排他的に及ぶため、他のフィルタを適用することができないからです。この効果を「シートがオートフィルタモード(AutoFilterMode)になっている」といいます。
2.行列の非表示とフィルタの非表示
問題
次の図で、B列のうち空白になっているセルに「A」と入力したい。Kチームのメンバーを非表示にして入力する方法を述べなさい。
解説
(1)行の非表示
本来なら空白セルをジャンプで選択して一括入力をすれば終わりですが、ここでは、Kチームのメンバーを非表示にした状態で「A」を入力する方法について考えます。
まずは、(実験として)Kチームのメンバーの行を非表示にします。
セル範囲A2:A17を選択します。このとき、非表示になっているセルも選択されていることに注意しなければなりません。
この状態で一括で入力します(参考:【Excel】データ入力の特訓、同じ文字の一括入力と削除のトレーニング(初心者特訓Part2))。
再表示すると、全てのメンバーがAになります。
行や列を非表示にした場合、範囲選択をすると非表示になっているセルも含まれます。
このことはオートフィルをした場合も同じです。初めのセルに「A」と入力して、オートフィルをします。
再表示すると、全てのメンバーがAになります。
行や列を非表示にした場合、初めに範囲選択をして一括入力をした場合も、オートフィルをした場合も、その範囲には非表示のセルが含まれます。したがって、非表示のセルも編集の対象であり、上書きされますので注意が必要です。
また、文字の削除をすると非表示のセルの文字も消えますので注意をしなければなりません。
このようなことを防ぐために、Excelには「可視セル選択」(ALT+セミコロン)の機能があるのです。
(2)オートフィルタモード
今度はオートフィルタを使って同じ作業をしてみます。オートフィルタを設定します。
空白だけを抽出します。A2:A17を選択します。
一括で入力します。
再表示すると、Kチームのメンバーはそのままです。
オートフィルタによって非表示になった行は範囲選択をしたとしても、その範囲には含まれません。
このことはオートフィルをした場合も同じです。初めのセルに「A」と入力して、オートフィルをします。
再表示すると、やはり、Kチームのメンバーは上書きされません。
初めに範囲選択をした場合も、オートフィルをした場合も、非表示のセルは選択から除外されます。
また、範囲選択をして、文字の削除をしても非表示だった部分は残ります。
ところで、オートフィルタモードは、フィルタを設定したシート全体にその効果が及びます。
そして、オートフィルタモードにすることによって、非表示のセルは、選択範囲から除外されます。
シート上にオートフィルタがなく、単に非表示にした行や列は選択可能であり、上書きされます。オートフィルタが設定され、オートフィルタモードとなっている場合は、原則として、シート上の非表示のセルは選択ができなくなります(例外については後述)。
3.フィルタが設定されていない列にも効果が及ぶ
問題
まずは、こちらの画面をご覧ください。B列とF列を非表示にします。
この状態でオートフィルタを設定します。Aチームのメンバーを抽出します。
2行目から17行目までをすべて青色で塗りつぶした場合、非表示になっている行や列の色はどのようになるか、述べなさい。
解説
非表示のセルが影響を受けるかどうかは、オートフィルタを設定したかどうかで決まります。オートフィルタが設定され、オートフィルタモードとなっている場合は、シート上の非表示のセルは選択ができなくなります。これに対して、シート上にオートフィルタがなく、単に非表示にした行や列は選択するとその範囲に含まれます。
2行目から17行目までを選択します。このときオートフィルタモードになっているため、この時点で非表示になっているセルはすべて選択範囲外です。
すべて青色で塗りつぶします。
フィルタを解除するとKチームは白色のままです。
さらに、B列はもちろんのこと、フィルタと直接関係のなかったF列も白色です。
オートフィルタモードの効果(非表示のセルには書式設定が適用されないという効果)がシート全体に及ぶからです。
ちなみに、B列とF列を非表示にします。
フィルタを設定せずに2~17行目に色を塗ると、非表示だった列も青色になります。オートフィルタモードでない状態で範囲選択をしたからです。
オートフィルタモードになっている場合、非表示になっているセルに対して、書式設定をすることはできません。オートフィルタがなければ非表示のセルにも書式が設定されます。
4.オートフィルタとオートカルク
問題
次の図で、合格者の合計点と平均点を確認するにはどうすればよいか、述べなさい。
解説
オートフィルタを設定します。合格者だけ抽出します。B2:B17を選択します。
画面の下に合計が表示されます。この合計は、いま表示されている合格者の合計点です。
範囲選択をした時に、画面の下に合計が表示される機能を「オートカルク(AutoCalc)」といいます。オートカルクは複数のセルを選択した時だけ表示されます。オートカルクは、フィルタがあるかないかにかかわらず、画面上に表示されているセルだけを計算します(この機能はオートフィルタモードは無関係)。
右クリックをします。平均は95点です。これも合格者だけの平均点です。
なお、SUM関数を使った場合は非表示のセルも含まれます。関数の中に使われるセル範囲には非表示のセルも含まれます(これもオートフィルタモードは無関係)。
このリストを別のシートに貼り付けます。SUM関数を使うと合格者だけの合計を求めることができます。このように合格者だけのリストを作り、関数を使って合格者だけで集計する場合は、別のシートに貼り付けてから集計します(SUBTOTAL関数を使う方法もありますが、別のシートに貼り付けたほうが安全です)。
5.オートフィルタモードとコピー貼り付け
(1)非表示のセルは、選択とコピーはできないが「貼り付け」はできる
問題
16人のメンバーの中からAチームのメンバーだけを抽出した。
この状態で、抽出したデータを別のシートに貼り付けることが可能か、述べなさい。
また、別のシートにあるメンバー全員分の得点データを元のリストに貼り付けることが可能か、述べなさい。
解説
オートフィルタが設定され、オートフィルタモードとなっている場合は、シート上の非表示のセルは選択ができなくなります。ただし、コピー・貼り付けについては例外がありますので注意が必要です。
範囲選択をします。このとき、オートフィルタモードで範囲選択をしているため、Kチームは範囲外です。
コピーをします。非表示の行に点線が表示されます。これはコピーの対象外であることを表しています。
別のシートに貼り付けます。Aチームだけコピーされます。
今度は得点のデータをコピーします。
これを、フィルタがかかっている状態のリストに貼り付けます。
フィルタを解除します。Kチームの得点も貼り付けができています。
フィルタで抽出したリストを別の場所に貼り付けると非表示のセルはコピーされません。
逆に、別のシートからフィルタで抽出した状態のリストに貼り付けると、非表示のセルにも貼り付けができます。
つまり、フィルタで非表示になっているセルは、選択ができないのでコピーはできませんが、外部からの貼り付けは可能です。
(2)連続していないセルに対する貼り付けはできない
問題
16人のメンバーの中からAチームのメンバーだけを抽出した。
別のシートにあるAチームの評価のデータを元のリストに貼り付けることが可能か確認しなさい。
解説
Aチームの評価をコピーします。
このまま貼り付けるとずれることはさきほどの設問で明らかです。
そこで、範囲選択をします。
貼り付けるとエラーになります。
コピーをしたセルの範囲は連続しています。これに対して、フィルタを設定していると非表示のセルは選択範囲外です。つまり連続していない範囲選択をしていることになります。
Excelでは、複数のセルを選択した状態でコピーをして、連続していない範囲に対して、貼り付けをすることはできない決まりになっています。
したがって、エラーとなり、貼り付けは不可能です。
(3)貼り付けと「形式を選択して貼り付け」は違う
問題
16人のメンバーの中からAチームのメンバーだけを抽出した。
セルE2の数式をコピーして、E3:E17に貼り付けることが可能か確認しなさい。
また、セルE1の書式をコピーして、E2:E17に貼り付けることが可能か確認しなさい。
解説
セルE2には数式が入っています。セルE2をコピーします。
E3:E17を選択して貼り付けます。
このとき非表示のセルは選択の範囲外なので空白のままです。
これはオートフィルをしても同じです。
もう一度、セルE2をコピーします。今度は「形式を選択して貼り付け」をつかって貼り付けます。
フィルタをクリアすると非表示だった行にも貼り付けができます。
1つのセルをコピーして、通常の貼り付けをすると非表示のセルは除外になりますが、形式を選択して貼り付けをしたときは、非表示のセルにも貼り付けになります。
セルE1の書式をコピーします。
E2:E17に貼り付けます。
フィルタをクリアすると非表示だった行にも貼り付けができます。
書式のコピーは、形式を選択して書式を貼り付けるのと同じなので、非表示のセルにも適用されます。
このように、フィルタのかかったリストに貼り付けをすることによって、原則として、非表示のセルにも影響が及び、上書きとなりますので注意が必要です。(ただし、1つのセルをコピーして、範囲選択をしたセル範囲に貼り付ける場合を除きます)
6.オートフィルタの可視セル選択
(1)可視セルの指定をしてもしなくても可視セル選択になる
問題
下の図は、オートフィルタを用いて合格者を抽出した時の様子を示している。
A5:A20のうち合格者のみ青色で塗りつぶしなさい。
解説
オートフィルタを設定すると、シート全体がオートフィルタモードになり、シート上の非表示のセルは、すべて、選択の範囲外となります。
A5:A20を選択します。オートフィルタモードの場合は可視セル選択をしなくても、可視セルのみの選択になります。青色で塗りつぶします。
フィルタをクリアすると合格者のみ青色になります。
もちろん、可視セル(Alt+セミコロン;)を選択して塗りつぶしをしても構いません。
(2)可視セルに貼り付ける
問題
セルE5の数式をコピーして、E8:E20の合格者のみに貼り付けなさい。
解説
E5をコピーします。
E8:E20を選択して貼り付けます。
このとき非表示のセルは選択の範囲外なので空白のままです。1つのセルをコピーして複数のセルに貼り付ける場合、非表示のセルは対象外となります。
これはオートフィルをしても同じです。
もう一度E5をコピーします。今度は「形式を選択して貼り付け」をつかって貼り付けます。
フィルタをクリアすると非表示だった行にも貼り付けができます。通常の貼り付けをすると非表示のセルは除外になりますが、形式を選択して貼り付けをしたときは、非表示のセルにも貼り付けになります。
これを避けるためには、オートフィルタモードであっても可視セルの選択をします。Altキーとセミコロン(;)で可視セルの選択をします。
形式を選択して貼り付けます。
これで、確実に可視セルだけ貼り付けができます。
(3)可視セルを選択しておくのが無難
問題
セルE2の書式をコピーして、E5:E20の合格者のみに貼り付けなさい。
解説
E2の書式をコピーします。
E5:E20に貼り付けます。フィルタをクリアすると非表示だった行にも貼り付けられます。書式のコピーは、形式を選択して書式を貼り付けるのと同じです。したがって、書式のコピーも非表示のセルにも適用されます。
これを避けるためには、オートフィルタモードであっても可視セルの選択をします。
E2をコピーします。可視セルの選択をします。
形式を選択して書式を選んで貼り付けます。
これで可視セルだけ書式のコピーになります。
オートフィルタモードになっているシートに可視セルだけの貼り付けをする場合は、あらかじめ可視セルの選択をするのが無難です。
解説は以上です。
7.動画版はこちら(無料)
この記事は、わえなび実力養成講座「ファンダメンタルExcel」Program 7-14-2、7-14-3、7-14-4、7-14-5、8-5-2 の5本のYoutube動画を書き起こして、加筆修正したものです。
- ファンダメンタルExcel 7-14-2 補講 オートフィルタモードの効果1(非表示セル)【わえなび】 (ファンダメンタルExcel Program7 データベース) - YouTube
- ファンダメンタルExcel 7-14-3 補講 オートフィルタモードの効果2(書式設定)【わえなび】 (ファンダメンタルExcel Program7 データベース) - YouTube
- ファンダメンタルExcel 7-14-4 補講 オートフィルタモードの効果3(コピー)【わえなび】 (ファンダメンタルExcel Program7 データベース) - YouTube
- ファンダメンタルExcel 7-14-5 補講 オートフィルタとオートカルク【わえなび】 (ファンダメンタルExcel Program7 データベース) - YouTube
- ファンダメンタルExcel 8-5-2 補講 オートフィルタの可視セル【わえなび】(ファンダメンタルExcel Program8 ジャンプと置換) - YouTube
- オートフィルタの設定と解除、条件にあう行を抽出する方法の総復習
- オートフィルタをさらに深く理解するための応用事例演習
- オートフィルタモードの効果とコピー貼り付けとの関係
- 並べ替えは並べ替えるだけ、オートフィルタは行数を減らすだけです