わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】オートフィルタモードの効果とコピー貼り付けとの関係

オートフィルタを設定すると、通常は、リストの項目名にフィルタが設定され、リストの部分だけがフィルタの対象になっているかのように見えます。そのため、リストの外側はオートフィルタと無関係であると勘違いしている人が多いようです。勘違いです

確かに、抽出されるのはリスト内のデータであり、リスト外のデータは抽出の対象ではありませんが、オートフィルタを設定したことによる効果はシート全体に及ぶということを忘れてはいけません。

 

そこで、今回は、オートフィルタモードがリスト外に及ぼす効果と、コピー貼り付けとの関係について出題します。

 

 

目次

問題文のあとに簡単な操作方法を解説していますが、静止画では、わかりにくいと思いますので、最後に動画を載せています。ぜひご覧ください。

1.オートフィルタモード

例えば、オートフィルタを設定して抽出すると、抽出されなかった行は非表示になりますが、非表示になるのは行全体です。A~D列だけにオートフィルタを設定したとしても、非表示になるデータはA~D列だけではありません。フィルタの対象ではないE列以降も非表示になります。

また、抽出をしてから、リストの下の行(フィルタの対象ではない行)を非表示にすると、抽出の条件をクリアしなければ再表示をすることができません。

f:id:waenavi:20190516064348j:plain

 

1つのシート内に複数のオートフィルタを設定することはできません。これは、オートフィルタを設定した時点で、フィルタの効果がシート全体に排他的に及ぶため、他のフィルタを適用することができないからです。この効果を「シートがオートフィルタモードAutoFilterMode)になっている」といいます。

 

2.行列の非表示とフィルタの非表示

問題

次の図で、B列のうち空白になっているセルに「A」と入力したい。Kチームのメンバーを非表示にして入力する方法を述べなさい。

f:id:waenavi:20190515194453j:plain

 

解説

(1)行の非表示

本来なら空白セルをジャンプで選択して一括入力をすれば終わりですが、ここでは、Kチームのメンバーを非表示にした状態で「A」を入力する方法について考えます。

 

まずは、(実験として)Kチームのメンバーの行を非表示にします。

f:id:waenavi:20190515194431j:plain

 

セル範囲A2:A17を選択します。このとき、非表示になっているセルも選択されていることに注意しなければなりません。

f:id:waenavi:20190515194921j:plain

 

この状態で一括で入力します。

f:id:waenavi:20190515194923j:plain

 

再表示すると、全てのメンバーがAになります。

f:id:waenavi:20190515194927j:plain

 

行や列を非表示にした場合、範囲選択をすると非表示になっているセルも含まれます。

f:id:waenavi:20190515194930j:plain

 

このことはオートフィルをした場合も同じです。初めのセルに「A」と入力して、オートフィルをします。

f:id:waenavi:20190515194933j:plain

 

再表示すると、全てのメンバーがAになります。

f:id:waenavi:20190515194936j:plain

 

行や列を非表示にした場合、初めに範囲選択をして一括入力をした場合も、オートフィルをした場合も、その範囲には非表示のセルが含まれます。したがって、非表示のセルも編集の対象であり、上書きされますので注意が必要です。

f:id:waenavi:20190515194939j:plain

 

また、文字の削除をすると非表示のセルの文字も消えますので注意をしなければなりません。

f:id:waenavi:20190515194943j:plain

f:id:waenavi:20190515194948j:plain

 

このようなことを防ぐために、Excelには「可視セル選択」(ALT+セミコロン)の機能があるのです。

 

(2)オートフィルタモード

今度はオートフィルタを使って同じ作業をしてみます。オートフィルタを設定します。

f:id:waenavi:20190515195927j:plain

 

空白だけを抽出します。A2:A17を選択します。

f:id:waenavi:20190515195930j:plain

 

一括で入力します。

f:id:waenavi:20190515195938j:plain

 

再表示すると、Kチームのメンバーはそのままです。

f:id:waenavi:20190515195933j:plain

 

オートフィルタによって非表示になった行は範囲選択をしたとしても、その範囲には含まれません。

f:id:waenavi:20190515195941j:plain

 

このことはオートフィルをした場合も同じです。初めのセルに「A」と入力して、オートフィルをします。

f:id:waenavi:20190515195944j:plain

 

再表示すると、やはり、Kチームのメンバーは上書きされません。

f:id:waenavi:20190515195948j:plain

 

初めに範囲選択をした場合も、オートフィルをした場合も、非表示のセルは選択から除外されます。

f:id:waenavi:20190515195952j:plain

 

また、範囲選択をして、文字の削除をしても非表示だった部分は残ります。

f:id:waenavi:20190515200223j:plain

f:id:waenavi:20190515200226j:plain

 

ところで、オートフィルタモードは、フィルタを設定したシート全体にその効果が及びます。

f:id:waenavi:20190515200229j:plain

 

そして、オートフィルタモードにすることによって、非表示のセルは、選択範囲から除外されます。

f:id:waenavi:20190515200232j:plain

 

シート上にオートフィルタがなく、単に非表示にした行や列は選択可能であり、上書きされます。オートフィルタが設定され、オートフィルタモードとなっている場合は、原則として、シート上の非表示のセルは選択ができなくなります(例外については後述)。

f:id:waenavi:20190515200235j:plain

 

3.フィルタが設定されていない列にも効果が及ぶ

問題

まずは、こちらの画面をご覧ください。B列とF列を非表示にします。

f:id:waenavi:20190515200545j:plain

 

この状態でオートフィルタを設定します。Aチームのメンバーを抽出します。

f:id:waenavi:20190515200548j:plain

 

2行目から17行目までをすべて青色で塗りつぶした場合、非表示になっている行や列の色はどのようになるか、述べなさい。

f:id:waenavi:20190515200551j:plain

 

解説

非表示のセルが影響を受けるかどうかは、オートフィルタを設定したかどうかで決まります。オートフィルタが設定され、オートフィルタモードとなっている場合は、シート上の非表示のセルは選択ができなくなります。これに対して、シート上にオートフィルタがなく、単に非表示にした行や列は選択するとその範囲に含まれます。

f:id:waenavi:20190515201257j:plain

 

2行目から17行目までを選択します。このときオートフィルタモードになっているため、この時点で非表示になっているセルはすべて選択範囲外です。

f:id:waenavi:20190515201303j:plain

 

すべて青色で塗りつぶします。

f:id:waenavi:20190515201300j:plain

 

フィルタを解除するとKチームは白色のままです。

f:id:waenavi:20190515201307j:plain

 

さらに、B列はもちろんのこと、フィルタと直接関係のなかったF列も白色です。

f:id:waenavi:20190515201310j:plain

 

オートフィルタモードの効果(非表示のセルには書式設定が適用されないという効果)がシート全体に及ぶからです。

f:id:waenavi:20190515201313j:plain

 

ちなみに、B列とF列を非表示にします。

f:id:waenavi:20190515201317j:plain

 

フィルタを設定せずに2~17行目に色を塗ると、非表示だった列も青色になります。オートフィルタモードでない状態で範囲選択をしたからです。

f:id:waenavi:20190515201319j:plain

 

オートフィルタモードになっている場合、非表示になっているセルに対して、書式設定をすることはできません。オートフィルタがなければ非表示のセルにも書式が設定されます。

f:id:waenavi:20190515201322j:plain

 

4.オートフィルタとオートカルク

問題

次の図で、合格者の合計点と平均点を確認するにはどうすればよいか、述べなさい。

f:id:waenavi:20190515205103j:plain

 

解説

オートフィルタを設定します。合格者だけ抽出します。B2:B17を選択します。

f:id:waenavi:20190515205325j:plain

 

画面の下に合計が表示されます。この合計は、いま表示されている合格者の合計点です。

f:id:waenavi:20190515205328j:plain

 

範囲選択をした時に、画面の下に合計が表示される機能を「オートカルク(AutoCalc」といいます。オートカルクは複数のセルを選択した時だけ表示されます。オートカルクは、フィルタがあるかないかにかかわらず、画面上に表示されているセルだけを計算します(この機能はオートフィルタモードは無関係)。

f:id:waenavi:20190515205332j:plain

 

右クリックをします。平均は95点です。これも合格者だけの平均点です。

f:id:waenavi:20190515205334j:plain

 

なお、SUM関数を使った場合は非表示のセルも含まれます。関数の中に使われるセル範囲には非表示のセルも含まれます(これもオートフィルタモードは無関係)。

f:id:waenavi:20190515205720j:plain

 

このリストを別のシートに貼り付けます。SUM関数を使うと合格者だけの合計を求めることができます。このように合格者だけのリストを作り、関数を使って合格者だけで集計する場合は、別のシートに貼り付けてから集計します(SUBTOTAL関数を使う方法もありますが、別のシートに貼り付けたほうが安全です)。

f:id:waenavi:20190515205724j:plain

 

5.オートフィルタモードとコピー貼り付け

(1)非表示のセルは、選択とコピーはできないが「貼り付け」はできる

問題

16人のメンバーの中からAチームのメンバーだけを抽出した。

f:id:waenavi:20190515201839j:plain

 

この状態で、抽出したデータを別のシートに貼り付けることが可能か、述べなさい。

f:id:waenavi:20190515201936j:plain

 

また、別のシートにあるメンバー全員分の得点データを元のリストに貼り付けることが可能か、述べなさい。

f:id:waenavi:20190515202006j:plain

 

解説

オートフィルタが設定され、オートフィルタモードとなっている場合は、シート上の非表示のセルは選択ができなくなります。ただし、コピー・貼り付けについては例外がありますので注意が必要です。

f:id:waenavi:20190515203107j:plain

 

範囲選択をします。このとき、オートフィルタモードで範囲選択をしているため、Kチームは範囲外です。

f:id:waenavi:20190515203110j:plain

 

コピーをします。非表示の行に点線が表示されます。これはコピーの対象外であることを表しています。

f:id:waenavi:20190515203114j:plain

 

別のシートに貼り付けます。Aチームだけコピーされます。

f:id:waenavi:20190515203119j:plain

 

今度は得点のデータをコピーします。

f:id:waenavi:20190515203122j:plain

 

これを、フィルタがかかっている状態のリストに貼り付けます。

f:id:waenavi:20190515203126j:plain

f:id:waenavi:20190515203129j:plain

 

フィルタを解除します。Kチームの得点も貼り付けができています。

f:id:waenavi:20190515203132j:plain

 

フィルタで抽出したリストを別の場所に貼り付けると非表示のセルはコピーされません。

f:id:waenavi:20190515203135j:plain

 

逆に、別のシートからフィルタで抽出した状態のリストに貼り付けると、非表示のセルにも貼り付けができます。

f:id:waenavi:20190515203138j:plain

 

つまり、フィルタで非表示になっているセルは、選択ができないのでコピーはできませんが、外部からの貼り付けは可能です。

f:id:waenavi:20190515203143j:plain

 

(2)連続していないセルに対する貼り付けはできない

問題

16人のメンバーの中からAチームのメンバーだけを抽出した。

f:id:waenavi:20190515201839j:plain

 

別のシートにあるAチームの評価のデータを元のリストに貼り付けることが可能か確認しなさい。

f:id:waenavi:20190515202458j:plain

 

解説

Aチームの評価をコピーします。

f:id:waenavi:20190515203722j:plain

 

このまま貼り付けるとずれることはさきほどの設問で明らかです。

f:id:waenavi:20190515203726j:plain

 

そこで、範囲選択をします。

f:id:waenavi:20190515203730j:plain

f:id:waenavi:20190515203732j:plain

 

貼り付けるとエラーになります。

f:id:waenavi:20190515203735j:plain

 

コピーをしたセルの範囲は連続しています。これに対して、フィルタを設定していると非表示のセルは選択範囲外です。つまり連続していない範囲選択をしていることになります。

f:id:waenavi:20190515203738j:plain

 

Excelでは、複数のセルを選択した状態でコピーをして、連続していない範囲に対して、貼り付けをすることはできない決まりになっています。

f:id:waenavi:20190515203740j:plain

 

したがって、エラーとなり、貼り付けは不可能です。

f:id:waenavi:20190515203758j:plain

 

(3)貼り付けと「形式を選択して貼り付け」は違う

問題

16人のメンバーの中からAチームのメンバーだけを抽出した。

f:id:waenavi:20190515201839j:plain

 

セルE2の数式をコピーして、E3:E17に貼り付けることが可能か確認しなさい。

f:id:waenavi:20190515202536j:plain

 

また、セルE1の書式をコピーして、E2:E17に貼り付けることが可能か確認しなさい。

f:id:waenavi:20190515202604j:plain

 

解説

セルE2には数式が入っています。セルE2をコピーします。

f:id:waenavi:20190515204650j:plain

 

E3:E17を選択して貼り付けます。

f:id:waenavi:20190515204654j:plain

 

このとき非表示のセルは選択の範囲外なので空白のままです。

f:id:waenavi:20190515204657j:plain

 

これはオートフィルをしても同じです。

f:id:waenavi:20190515204701j:plain

f:id:waenavi:20190515204704j:plain

 

もう一度、セルE2をコピーします。今度は「形式を選択して貼り付け」をつかって貼り付けます。

f:id:waenavi:20190515204708j:plain

f:id:waenavi:20190515204712j:plain

 

フィルタをクリアすると非表示だった行にも貼り付けができます。

f:id:waenavi:20190515204716j:plain

 

1つのセルをコピーして、通常の貼り付けをすると非表示のセルは除外になりますが、形式を選択して貼り付けをしたときは、非表示のセルにも貼り付けになります。

f:id:waenavi:20190515204720j:plain

f:id:waenavi:20190515204723j:plain

 

セルE1の書式をコピーします。

f:id:waenavi:20190515204726j:plain

 

E2:E17に貼り付けます。

f:id:waenavi:20190515204731j:plain

 

フィルタをクリアすると非表示だった行にも貼り付けができます。

f:id:waenavi:20190515204734j:plain

 

書式のコピーは、形式を選択して書式を貼り付けるのと同じなので、非表示のセルにも適用されます。

f:id:waenavi:20190515204738j:plain

 

このように、フィルタのかかったリストに貼り付けをすることによって、原則として、非表示のセルにも影響が及び、上書きとなりますので注意が必要です。(ただし、1つのセルをコピーして、範囲選択をしたセル範囲に貼り付ける場合を除きます)

f:id:waenavi:20190515204741j:plain

 

6.オートフィルタの可視セル選択

問題

下の図は、オートフィルタを用いて合格者を抽出した時の様子を示している。

f:id:waenavi:20190515210739j:plain

 

(1)A5:A20のうち合格者のみ青色で塗りつぶしなさい。

f:id:waenavi:20190515210744j:plain

 

(2)セルE5の数式をコピーして、E8:E20の合格者のみに貼り付けなさい。

f:id:waenavi:20190515210747j:plain

 

(3)セルE2の書式をコピーして、E5:E20の合格者のみに貼り付けなさい。

f:id:waenavi:20190515210750j:plain

 

解説

(1)可視セルの指定をしてもしなくても可視セル選択になる

オートフィルタを設定すると、シート全体がオートフィルタモードになり、シート上の非表示のセルは、すべて、選択の範囲外となります。

f:id:waenavi:20190515210924j:plain

 

A5:A20を選択します。オートフィルタモードの場合は可視セル選択をしなくても、可視セルのみの選択になります。青色で塗りつぶします。

f:id:waenavi:20190515210929j:plain 

 

フィルタをクリアすると合格者のみ青色になります。

f:id:waenavi:20190515210931j:plain

 

もちろん、可視セル(Alt+セミコロン;)を選択して塗りつぶしをしても構いません。

f:id:waenavi:20190515210935j:plain

f:id:waenavi:20190515210939j:plain

 

(2)可視セルに貼り付ける

E5をコピーします。

f:id:waenavi:20190515211907j:plain

 

E8:E20を選択して貼り付けます。

f:id:waenavi:20190515211909j:plain

 

このとき非表示のセルは選択の範囲外なので空白のままです。1つのセルをコピーして複数のセルに貼り付ける場合、非表示のセルは対象外となります。

f:id:waenavi:20190515211912j:plain

 

これはオートフィルをしても同じです。

f:id:waenavi:20190515211916j:plain

 

もう一度E5をコピーします。今度は「形式を選択して貼り付け」をつかって貼り付けます。

f:id:waenavi:20190515211919j:plain

 

フィルタをクリアすると非表示だった行にも貼り付けができます。通常の貼り付けをすると非表示のセルは除外になりますが、形式を選択して貼り付けをしたときは、非表示のセルにも貼り付けになります。

f:id:waenavi:20190515211924j:plain

 

これを避けるためには、オートフィルタモードであっても可視セルの選択をします。Altキーとセミコロン(;)で可視セルの選択をします。

f:id:waenavi:20190515211929j:plain

 

形式を選択して貼り付けます。

f:id:waenavi:20190515212232j:plain

 

これで、確実に可視セルだけ貼り付けができます。

f:id:waenavi:20190515211934j:plain

 

(3)可視セルを選択しておくのが無難

E2の書式をコピーします。

f:id:waenavi:20190515212150j:plain

 

E5:E20に貼り付けます。フィルタをクリアすると非表示だった行にも貼り付けられます。書式のコピーは、形式を選択して書式を貼り付けるのと同じです。したがって、書式のコピーも非表示のセルにも適用されます。

f:id:waenavi:20190515212523j:plain

 

これを避けるためには、オートフィルタモードであっても可視セルの選択をします。

f:id:waenavi:20190515212526j:plain

 

E2をコピーします。可視セルの選択をします。

f:id:waenavi:20190515212529j:plain

 

形式を選択して書式を選んで貼り付けます。

f:id:waenavi:20190515212532j:plain

 

これで可視セルだけ書式のコピーになります。

f:id:waenavi:20190515212536j:plain

 

オートフィルタモードになっているシートに可視セルだけの貼り付けをする場合は、あらかじめ可視セルの選択をするのが無難です。

f:id:waenavi:20190515212538j:plain

 


解説は以上です。


7.動画版はこちら(無料)

この記事は、わえなび実力養成講座「ファンダメンタルExcel」Program 7-14-2、7-14-3、7-14-4、7-14-5、8-5-2 の5本のYoutube動画を書き起こして、加筆修正したものです。

 

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]