前回の記事では出欠表をテーマにCOUNTIF関数、COUNTA関数、データの入力規則や条件付き書式について説明しました。
今回は選手一覧をテーマにデータのフィルタリングや行と列の非表示・グループ化、名前の管理について学習していきましょう。
かなり色々な内容が盛り込まれているのでしっかり読んでください。
また、読むだけでなく自分で実際にやってみてください。
選手一覧の作成
以下のような選手一覧を作成しましょう。
こちらは集英社の人気漫画『SLAM DUNK』（作者：井上雄彦）に出てくる選手に関する情報を一覧にまとめた表です。
データ量が多いので手入力が大変な方のために下にサンプルデータを用意しました。
ダウンロードして開くと画面上部に「保護ビュー」に関する注意が記載される場合があります。
「保護ビュー」のままでは編集が出来ないので「編集を有効にする」ボタンをクリックするか、中身のデータをコピーして別のExcelのブックに貼り付けて利用してください。
参考：Microsoft 保護ビューとは
フィルタリング
この選手一覧についてデータを分析してみようと思います。
フィルタリング機能を使えばデータを特定の条件で絞り込んだり並び変えたりすることが出来ます。
まず見出しにあたる2行目の行番号をクリックして2行目全体を選択した状態にし、データタブのリボンの「並び替えとフィルター」グループの「フィルター」ボタンをクリックするとB2～H2のセルの右下にリストと同じような▼ボタンが表示されます。
ではH列のチーム名「湘北高校」でフィルタリングしてみましょう。
H2の右下の▼をクリックし、「☑（すべて選択）」のチェックマークをクリックして一度すべてのチェックを外した状態にして、「湘北高校」の☑を付けて「OK」をクリックすると「湘北高校」のみが表示されます。
なお、フィルタリング中はセルのマークが変わるのでどこの列でフィルタが掛かっているか分かります。
フィルタリングはH2のフィルタリング設定で再度「☑（すべて選択）」の状態にするか、データタブのリボンの「並び替えとフィルター」グループの「フィルター」ボタンの横の「クリア」ボタンを押すと元の状態に戻ります。
今度はフィルタリングとしてC列の身長を小さい順に並び変えてみましょう。
なお、小さい順のことを「昇順（しょうじゅん）」大きい順のことを「降順（こうじゅん）」というので覚えておきましょう。
C2の右下の▼をクリックし「昇順」を選択して「OK」ボタンをクリックします。
フィルタリングは複数の列に対して同時に行うことも出来るので、C列の身長を「昇順」にした状態のまま、H列のチーム名「湘北高校」にすると以下のようになります。
C2とH2のそれぞれのセルでフィルタリング中のマークになっているのが分かります。
なお、並び替えは先ほどの「クリア」ボタンでは元に戻りません。
並び変える前の状態に戻すには以前、成績表のExcelの基本用語で説明した「クイックアクセスツールバー」の「元に戻す」ボタンをクリックするかキーボードのショートカットキー「Ctrlキー + Zキー」で前の状態に戻す必要がありますので気を付けましょう。
文字を並び替える場合は「昇順」が五十音順、「降順」が五十音順の逆順になります。
G列のポジションを「昇順」で並び変えると以下のようになります。
なお、五十音順では漢字はカタカナより後と判定されます。
数字データに対しては数値フィルターという数値の大きさの条件でフィルタリングすることが出来ます。
たとえばD列の体重について「75以上85以下」という条件でフィルタリングしてみましょう。
D2の右下の▼をクリックし「数値フィルター」の中のを「指定の値以上」を選択します。
「カスタムオートフィルター」画面の抽出条件の指定の体重を「以上75 AND 以下85」として「OK」ボタンをクリックすると以下のようになります。
行と列の非表示・グループ化
先ほどのフィルタリングについてC列の身長を「降順」で並べた状態にしてください。
すると下3行は監督なので身長や体重などのデータが記載されておりません。
この選手一覧の表に「そもそも監督を含めるべきではない」という意見もあるでしょう。
しかし監督のデータも残しておきたいという考えもあるかもしれません。
不要な行や列を削除するのではなく表示しないようにするには「非表示」にする方法と「グループ化」によって隠す方法があります。
【行と列の非表示】
下3行（18行目～20行目）の行番号を選択した状態でその行の上で右クリックするとメニューが表示されるので「非表示」を選択します。
18行目～20行目が表示されなくなりました。
非表示になっていることは行番号に注目すると17行目の次が21行目に飛んでいるので分かります。
非表示を元に戻して再度表示するには非表示になっている行の前後の行（今回は17行目と21行目）を選択した状態で行の上で右クリックしてメニューから「再表示」を選択すると表示されます。
列を非表示にする方法も行を非表示にする方法と基本的に一緒です。
今度はE列～H列を非表示にしてみましょう。
E列～H列の列番号を選択し、列の上で右クリックしてメニューから「非表示」を選択します。
E列～H列が表示されなくなりました。
非表示になっていることは列番号に注目するとD列の次がI列に飛んでいるので分かります。
【行と列のグループ化】
行や列の非表示だとデータを見たい人にとっては、いちいち行や列を再表示しなくてはならず面倒です。
もっと簡単に見たい人だけデータを見れるようにする方法として行や列のグループ化という方法があります。
先ほどと同様に下3行（18行目～20行目）の行番号を選択した状態でデータタブを選択しリボンの「アウトライン」グループの「グループ化」ボタンをクリックすると行番号の左に列のようなものが追加されーや＋ボタンが表示されます。
このーや＋のボタンを押すことで対象の行を折りたためるので表示／非表示を切り替えられます。
なお、ーや＋ボタンではなく上の１や２のボタンでも切り替えられます。
グループ化を元に戻すにはグループ化している行を選択した状態でデータタブを選択しリボンの「アウトライン」グループの「グループ解除」ボタンをクリックすると元の状態に戻ります。
列をグループ化する方法も行をグループ化する方法と基本的に一緒です。
今度はE列～H列をグループ化してみましょう。
E列～H列の列番号を選択した状態でデータタブを選択しリボンの「アウトライン」グループの「グループ化」ボタンをクリックすると列番号の上に行のようなものが追加されーや＋ボタンが表示されます。
行と同様このーや＋のボタンを押すことで対象の列を折りたためるので表示／非表示を切り替えられます。
なお、ーや＋ボタンではなく左の１や２のボタンでも切り替えられます。
グループ化は階層構造にすることも出来ます。
先ほどのE列～H列をグループ化した状態のまま、さらにG列～H列をグループ化すると列番号の上の行みたいなものに３というボタンが追加されました。
１と２と３のボタンによって表示／非表示を階層で切り替えることが出来ます。
名前の管理
最後にそれぞれのチームの平均身長と平均体重を求めてみましょう。
平均は成績表で学習したAVERAGE関数を使って求めます。
フィルタリングによりC列の身長を降順に並び替えをした状態で、J3～K8に以下のようにそれぞれのチームの平均身長と平均体重の項目を追加しましょう。
身長の大きい順に並んでいるので各チームの選手は離れ離れに並んでいます。
AVERAGE関数は引数として対象データを指定するため、以下のように離れたセルを全て指定する必要がありますが、数式だけ見ていてもどこを指していて何の平均を求めようとしているか分かりにくいですよね。
そこで「名前の管理」という機能を使ってセルの範囲に名前を定義し、定義した名前を数式の中で使用することで式の中身を見た時に何を計算しているのか読み取りやすくします。
まず数式タブを選択しリボンの中の「定義された名前」グループの「名前の管理」ボタンをクリックします。
「名前の管理」画面の「新規作成」ボタンをクリックし「名前の編集」画面にて適宜名前を入力し、参照範囲のセル参照ボタンをクリックします。
「名前の編集-参照範囲」という小さな画面が表示されるので対象のセルを選択して最後にEnterキーを押します。（選択したセルは自動的にシート名!$列番号$行番号で表示され、それぞれのセルはカンマで区切られます）
なお離れたセルを複数指定する際は目標売上表1の作成でも学習しましたがCtrlキーを押さえたままマウスで対象のセルを順番にクリックします。
「名前の編集」画面に戻るので「OK」ボタンをクリックすると「名前の管理」画面に戻り、登録が完了します。
「名前の管理」にて定義した名前を使ってAVERAGE関数の式を入力すると何を計算しているのか対象のセルをいちいち追わなくても内容が伝わってきます。
なお、名前の定義が正しく出来ているかどうかは左上の「名前ボックス」に定義した名前を入力すると対象のセルを選択した状態で表示してくれるので確認出来ます。
同様の手順で「名前の管理」から全て名前の定義を登録し下図のように表を完成させましょう。
どのチームも平均身長と平均体重がほとんど同じという事が分かりました。
バスケットボールは体格がかなり影響するスポーツなので戦力がほぼ互角なのも納得です。
ここまでExcelの色々な機能について学習しました。
次回は今まで学習した知識の確認も兼ねて食費データ分析というテーマの演習問題に挑戦しましょう。
コメント