戻る

今回はEXCELでデータを整理し、集計する機能について解説をします。今回の内容は、きっと実務でも利用頻度が高いテクニックだと思いますので、ぜひマスターしてください。おすすめです！

教科書を持っている場合は、第４章１３「Excelの便利な機能」P.168も合わせて参照してください。

演習するために、以下の練習問題をクリックし、ダウンロードして開いてください。

練習問題 syokyu13example.xlsx

ファイル内の設問に回答し、moodle に提出してください。

今回はEXCELでデータを整理したり集計したりするのに便利な４つの機能を紹介します。

• 並び替え データを順番に並び替えられます。
• フィルタ データの検索を行えます。
• 条件付き書式 特定のセルに自動的に色付けを行えます。
• 小計 データを項目別にまとめ、自動的に集計します。

以下、順番に解説します。

EXCELの「並び替え」機能を使えばデータを特定の順番に並び替えることができます。「並び替え」のことを「ソート」と言うこともあります。
データを分析する際に整理ができるので便利です。

練習問題の「練習１」シートにアクセスしてください。

これは架空の住所録です。しかしバラバラの順番に並んでいるのは見にくいです。そこで、氏名の50音順に並び替えてみましょう。以下のような操作で簡単に並び替えを行えます。

1. フリガナ順に並び替えると良さそうなので¹⁾、フリガナが書かれた列（C列）の中で好きなセルをどれか一つクリックします。（たとえばC10セルなど）
2. 「並べ替えとフィルタ」→「昇順」をクリックします。
   
   • なお「昇順」とは小さい順のことで、数字は小さい順（0→9）、アルファベットのA→Z、日本語の50音順という具合に並び替えられます。
   • 一方「降順」は大きい順のことで、昇順とは逆の順番で並び替えられます。

これでフリガナの50音順にデータが並び替えられます。とても簡単ですね！

これでたとえ順不同に並んでいる乱れたデータであっても、後から簡単に整理できます。大変強力です。

いろいろな欄をクリックして並び替えを自由に試してみましょう。下の動画ではフリガナ、性別、住所で次々と並び替えを行っています。

並び替えはいつでも実行できます。データも普通に入力するだけでＯＫです。

ただしスムーズに並び替えを行うためには、データの書き方にいくつかのルールがあります。並び替えを行う場合は、以下のルールを確認しておきましょう。

• 一件のデータは横一行に収めて記入してください。
  別の行に書くと異なるデータとして扱われ、並び替えを行った際に離れた位置に移動してしまう場合があります。
  どうしても２行使いたい場合は、セル内で改行（Alt+Enter）を行ってください。
  

  １件のデータは横１行に収めましょう。
• 縦列には同じ種類のデータを並べましょう。
  練習問題のように「フリガナ」欄にはフリガナばかり、「住所」欄には住所ばかり書くという具合です。
• 一番上の行は「見出し」として扱われます。
  表の最初の行には、何のデータが書かれているのかが分かりやすいように「氏名」「フリガナ」「商品名」「単価」のように項目名を書いておくと良いでしょう。
  

  一番上に書かれているこの見出しが大切です。
• データの中には空白行や空白の列を作らないようにしましょう。
  隣の行や列との間に空白行や空白列があると別の表とみなされるので、並び替えの対象から外れます。
  逆に、別の表を書きたい場合は、隣の表と縦横に１セル分以上の隙間を設け、密着しないようにしましょう。
  

  空白の列や行を境として、それぞれ別の表と見なされます。
• 入力したデータには連番を振っておくと良いでしょう。
  例えば入力順が大切な場合など、元の並び方に戻したい場合もあるかもしれません³⁾。そこで練習問題の例なら左端にある「登録No」欄のように、連続した番号を振っておくと役に立つことがあります。また、番号がつながっていることで空白行を防ぐ意味合いもあります。
  連番はオートフィルを使えば簡単に作成できるでしょう⁴⁾。

より複雑な並べ替えを行います。練習問題の「練習２」シートにアクセスしてください。

条件が複雑な場合は、「ユーザー設定の並び替え」機能を使うと便利です。

たとえば、担当者を50音順に並べ、さらに同一担当者については、売上金額の多い順に並ぶようにしてみましょう。
こういう複雑な並べ替えをしたいときに「ユーザー設定の並び替え」の出番です。

1. 表内の任意のセルをクリックします。(表内ならどこでも構いません)
2. 「並べ替えとフィルタ」→「ユーザー設定の並び替え」を選択します。
   
3. 「並び替え」パネルが開きます。「最優先されるキー」欄で「担当者」を選択し、さらに「順序」欄を「昇順」に設定します。
4. ２つ目の条件を追加するために、「＋レベルの追加」ボタンをクリックします。
5. 「２番目に優先されるキー」欄が出現するので、「売上金額」を選択し、さらに「順序」欄を「大きい順（降順）」に設定します。
6. 「ＯＫ」をクリックすれば並び替え完了です。
   

   最優先が「担当者」、
   次が「売上金額」（大きい順）

「最優先」の並び替え項目が「担当者」なので、基本は担当者順に並んでいる様子が分かります。そして、同じ担当者については、売上金額が多いものから少ないものに並んでいる様子が分かります。とっても器用ですね！

このように複雑な並び替えを行いたい場合は、「ユーザー設定の並び替え」を行えば良いというわけです。

余裕があればいろいろな並び替えを試してみましょう。
例えば「製品名」別に並び替えを行い、同一製品内では「注文日」の順番に並ぶように設定してみましょう。

つまり、最優先が製品名、次が注文日というわけですね。

このように並び替え機能を使えばデータの並びを簡単に揃えられます。

バラバラに並んでいるデータは扱いにくいです。目的の場所を探しにくいので、修正なども大変です。なのでデータをきれいに並び替えておくと、必要な情報を探しやすくなります。書類を提出する前に体裁を整えたりするのにも、並び替えをぜひ活用してください。😁

２つ目の便利機能です。

EXCELには、たくさんあるデータの中から目的のものを探せる、いわゆるデータ検索のような機能もあります。

この機能のことをEXCELでは「フィルタ」機能と呼びます。フィルタを使用すると、EXCELが大量のデータの中から、条件に合った行のみ表示してくれるようになります。

引き続き、練習問題の「練習２」シートでフィルタを試してみましょう。

フィルタを使えるようにするために、最初にフィルタの有効化をする必要があります。

1. データが書かれた表の中で、好きなセルをクリックします（たとえばI5など。てきとうに）。クリックした場所にある表が対象になります。
2. 「並べ替えとフィルタ」→「フィルタ」をクリックします。
   
3. 各列の見出しに▼ボタンが追加されれば有効化は完了です。検索する準備が整いました。
   

例えば「上生菓子詰合」に関するデータを検索してみましょう。

1. 「製品名」欄の▼ボタンをクリックし、検索欄に「上生菓子」などと入力します。
   あるいは「上生菓子詰合」だけにチェック☑が付いている状態にしてもＯＫです。
   
2. 画面上には上生菓子詰合のデータのみが表示された状態になり、検索ができました。
   

画面には「上生菓子詰合」を含むデータだけが表示された状態になります。

なお他のデータは見えなくなっているだけで、削除されたわけではないので安心してください。フィルタを解除すれば元に戻ります。

このようにフィルタが有効な状態なら、▼ボタンを押して検索条件を記入すれば、簡単に必要なデータを検索できます。

フィルタした結果をさらに並び替えることもできます。合わせ技です。😲

操作方法は同じで、例えば「担当者」欄のセルを１つクリックし、「並び替えとフィルタ」→「昇順」などを選択するだけです。

下の例は実際に「担当者」で並び替えを行った例です。フィルタで検索した結果が、さらに担当者順になるよう整理されている様子が分かります。

検索結果をきれいに整頓することができるので、ぜひ「フィルタ」＋「並び替え」の合わせ技を活用してください。

例えば「上生菓子詰合」の中で、さらに「佐々木雅子」さんが担当したものだけ検索したい場合、以下のように操作します。

• 上記の設定で上生菓子詰合が検索されている状態で、さらに「担当者」欄の▼ボタンを押して検索欄に「佐々木」と入力します。あるいは「佐々木雅子」だけにチェックが付いている状態なるよう☑を入れます。

これで画面上には「上生菓子詰合」かつ担当者が「佐々木雅子」さんのデータがのみが表示された状態になります。

絞り込み検索をやめて新たに別の検索を行いたい場合などは、以下のように操作します。

1. 「並べ替えとフィルタ」をクリックし「クリア」を選択します。
   

これで、今までの検索条件がクリアされ、検索をかけていない状態に戻ります。

検索条件のクリアを行わないと、検索操作を行うたびに条件が追加されて厳しくなっていくため、やがて１件も該当データが見つからない状態になってしまいます。

検索をやりなおす場合は、一旦クリアを行ってから別の検索を行うようにしましょう。

フィルタでは、単純なキーワード検索以外に、さまざまな種類の検索を行えます。

例えば「注文日」欄なら日付に関する検索ができ、「売上金額」欄なら数値に関する検索ができるという具合です。
書かれているデータに応じて、自動的に最適なものが提案されます。

とくに数値を検索する場合は「10,000円以上」のような範囲検索をすることが多いので、「数値フィルタ」を使う機会は多いでしょう。もし普通に検索欄に「10000」などと入力すると、10000円ピッタリのものしか見つけられないので、「数値フィルタ」を使わないと厳しいです。

余裕があれば試してみましょう。

1. 売上金額の上位10位を「トップテン」機能で検索してください。
2. 「福田敦子」さんが売った製品のうち「和菓子詰合」または「麩饅頭あらかると」を検索してください。
3. 「横浜」店で売り上げた製品のうち「売上金額」が「20,000以上」のものを検索してください。

フィルタによる検索作業を辞めて通常の状態に戻るには、表の中のクリックした状態で、再び「並べ替えとフィルタ」→「フィルタ」を選択します。
見出し部分の▼ボタンが無くなったらフィルタ機能が解除された状態です。

検索で絞り込まれて見えなくなっていたセルも全て表示される状態に戻ります。

このように「フィルタ」機能を使えば、必要なデータを素早く見つけられます。データ検索にぜひフィルタを活用してください。

フィルタは並び替えと相性が良いので、フィルタした結果は自由に並び替えできます。

３つめの便利機能です。

「条件付き書式」機能を使うと、設定した条件に合致したセルに、自動的に色が塗られます。

データの特徴が視覚的に分かりやすく表示されるので便利です⁶⁾。

例えば、「売上金額」が10万円より大きいものに、自動的に色がつくようにしてみましょう。

1. もしフィルタが設定されている場合は、解除します（「並べ替えとフィルタ」→「フィルタ」）。
2. 全ての「売上金額」の数値を選択します。
   （条件付き書式を行う場合は、色を塗りたい範囲を指定する必要があります）
3. 「条件付き書式」→「セルの強調表示ルール」→「指定の値より大きい」を選択します。
4. 入力パネルが開くので、入力欄に 100000 と入力します。
5. 「書式」欄をクリックし、好きな色、例えば「明るい赤の背景」などを選択します。

詳しい操作方法は、下の動画で確認できます。

以上で、「売上金額」欄の値が「100000」より大きいセルに自動的に明るい赤の背景色が塗られます。

注意を要するデータに自動的に色が付いてよく分かる見た目になるので、ぜひ活用してください。

値を編集するなどして売上金額の数値に変化が生じた場合は、それに合わせて色も自動的に変化します。
例えばデータを書き換えて100000以下になるようにすると、赤い背景色は自動的に消え、100000を超えると再び赤い背景色が塗られます。

色の変化で現在の状況が分かるので便利です。刻々と状況が変化するようなシーンでも、自動的に色が変化するので役に立つでしょう。

条件付き書式では、書式を変える条件をさまざまに設定できます。
「条件付き書式」ボタンをクリックして現れるメニューで条件を選べます。

メニュー内に書かれている絵を見れば、だいたい結果が予想できると思います。

その他、より詳細な条件を決めることもできます。

例えば売上金額が10万円以上のものに色を塗る場合、以下のように選択します⁷⁾。

1. 「条件付き書式」ボタン→「新しいルール」を選択します。
2. 上部の選択肢で「指定の値を含むセルだけを書式設定」を選択します。
3. 「次の値の間」と書かれている部分をクリックし「次の値以上」を選択し、右に「100000」と入力します。
4. 「書式」ボタンを押して、セルの見た目を決めます。文字の色を変えたい場合は「フォント」コーナー、背景色を変えたい場合は「塗りつぶし」コーナーで好きな色を設定します。

これで、より詳細に条件やセルの書式を変えられます。他にもさまざまな条件が用意されているので、ぜひ活用してみてください。

同じセルに複数の条件付き書式の設定を行うと、その効果は重なり合います。

例えば設定を間違えた場合、もう一度同じ範囲に新しい条件付き書式の設定を行うと、間違えた設定と、次に行った設定が重なり合うので、見た目に混乱が生じることでしょう。

条件付き書式の設定は、セル上でDeleteキーを押すなどしても残り続け、消すことができませんので、自分がどれぐらい条件付き書式の設定を行ったのか分からなくなるかもしれません。

現在の条件付き書式の状態を知りたくなった場合は、以下の操作で確認できます。

1. 「条件付き書式」ボタン→「ルールの管理」を選択します。
2. 上部にある「書式ルールの表示」欄で「このワークシート」を選択すると、現在のシート上に設定されている条件付き書式が全て列挙されます。
   
   • 不要なルールは選択して「×ルールの削除」ボタンを押せば消去できます。
   • 設定を間違えたルールは「ルールの編集」ボタンで条件を修正できます。

このようにして現在設定されているルールを確認したり、不要なルールを削除したりできます。

もし条件付き書式の設定を全て完全に消去してしまいたい場合⁸⁾は、以下の方法で行えます。

• 「条件付き書式」→「ルールのクリア」→「シート全体からルールをクリア」

条件付き書式を使えば、条件に応じてリアルタイムにセルに色を塗ったりして目立たせることができます。

IF関数を設定するより手軽で、状況が視覚的に分かりやすくなる効果がありますので、ぜひ活用してください。

本日最後の便利機能です。（あと少しなので頑張りましょう😆）

「小計」機能を使うと、データを項目ごとにグループ分けし、そのグループごとに「合計」や「平均」といった小計を求められます。

誰がどれぐらい商品を売り上げているか、どの商品がどれぐらい売れているか、といったことを調べる際に便利です。

小計を始める前には準備が必要です。

引き続き「練習２」シートで作業を行います。
たとえば「製品名」別に「売上金額」を「合計」してみましょう。

1. 「製品名」が書かれた列（F列）内の好きなセルを一箇所クリックし、「並べ替えとフィルタ」→「昇順」で並び替えておきます。
   
2. 表の中をクリックしている状態で、「データ」タブ →「小計」をクリックします。（小計ボタンは右端あたりにあります）
   
3. 集計の設定パネルが開くので、以下のように設定します。
   • 「グループの基準」欄で「製品名」を選択します。
   • 「集計の方法」欄で「合計」を選択します。
   • 「集計するフィールド」欄で「売上金額」を選択します。
     
4. 「ＯＫ」ボタンを押すと、グループごとに集計を行った結果が挿入されます。
   

これで小計が有効になり、「製品名」別に「売上金額」を「合計」することができました。

集計額を見ながら、製品別にどの程度売れているのかを比べられます。一番の売れ筋商品は何なのかなど調べるのに良いでしょう。

小計を実行すると、ウインドウの左端に「アウトライン」と呼ばれる列が作られます。
＋ボタンや－ボタンがあり、これらをクリックすると、詳細な情報を折りたたんだり展開したりできます。不要な情報を隠してすっきりした見た目にできます。

左上には１２３と書かれたボタンがありますが、これらをクリックすると、総合計だけを表示、項目ごとの合計まで表示、全ての情報を表示、といった風に表示を一括で切り替えられます。

詳しい動作の様子は下の動画を見てもらうと、よく分かるでしょう。

データの量が多い時は、たとえば左上の２のボタンを押して、小計の結果だけを表示するようにすると状況がよく分かるかもしれません。

例えば、担当者別の小計と、製品別の小計を両方調べたいかもしれません。

しかし初期設定では、小計を行うと、以前に行っていた小計の内容はかき消されて、最後に設定した小計だけが残る状態となります。

これは集計パネルで設定を行う際に「現在の小計を全て置き換える」設定が有効☑になっているからです。
このチェックをＯＦＦにすれば、複数の小計を重ね、多重の小計がとれるようになります。

もし複数の小計を重ねたい場合は、以下のように操作します。
データは最優先で担当者、次に製品名で並び替えられており、すでに担当者別で小計がとられているものとします。

1. 表の中をクリックしている状態で、「データ」タブ →「小計」をクリックします。
2. 「グループの基準」「集計の方法」「集計するフィールド」の設定を行う際に、「現在の小計を全て置き換える」のチェックをＯＦＦにした状態にします。
3. 「ＯＫ」ボタンを押すと、すでに行われている小計の上に新しい小計が重なります。

上の例が二重に小計されている状態です。担当者ごとに小計が取られていますが、一人の担当者に注目すると、さらに製品名ごとの小計が含まれる二重小計の状態になっている様子が分かります。

小計をやめたい場合は、以下の操作で小計を無効にできます。EXCELは小計を取る前の通常の状態に戻ります。

• 「データ」タブ →「小計」→「全て削除」
  

少し怖い名前のボタンですが😲、入力データが消去されたりはしないので安心してください。小計をとる前の状態に戻るだけです。

このように小計機能を使えば、グループごとのデータの合計などを簡単に計算できるので、状況を分析する際などに役に立ちます。

ただし小計を始める前に、グループごとに「並べ替え」を行っておくのを忘れないでください。

今回はデータを整理するための４つの機能を紹介しました。

1. 並び替え データを順番に並び替えられます。
   「並び替えとフィルター」ボタン→「昇順」/「降順」/「ユーザー設定並び替え」
2. フィルタ データ検索を行えます。
   「並び替えとフィルター」ボタン→「フィルター」
3. 条件付き書式 自動的にセルに色付けを行えます。
   「条件付き書式」ボタン
4. 小計 データをグループ別にまとめ、集計できます。
   「データ」タブ→「小計」ボタン

たくさんのデータを整理したり分析したりする際にはこれらの機能をぜひ活用してください。

今回紹介した４つの機能には、それぞれ相性があるので注意してください。

• 「並び替え」と「フィルタ」の相性は良いです。並び替えをしてからフィルタを使ったり、その逆の順番で操作したり、自由に行なえます。
  相性バッチリ！
• 「フィルタ」と「小計」は相性が悪いです。フィルタ結果を小計すると表示が乱れます。小計結果をフィルタすると小計が消えます。原則、同時に使わないようにしましょう。
  もしどうしてもフィルタの結果を小計したい場合は、フィルタ後のデータを、どこか別のシートなどにコピー・貼り付けしてから小計を取ると良いでしょう。
• 「並び替え」と「小計」は一方的な関係です。並び替えを行ってから小計を実行すると小計結果もきれいに並んで便利ですが、小計後に並び替えを行うとデータの並びが崩れます……。
• 「条件付き書式」は他のすべての機能と相性が良いです。他の機能と一緒に問題なく利用できます。大変使いやすいです。

各機能を併用する場合は、上記の関係に気をつけてください。奇妙な三角関係ですね。😆

以上の内容を踏まえて、EXCEL練習問題のすべてのワークシートの設問に解答して下さい。

解答が終わったら、「学生番号 氏名 集計」というファイル名で保存し、moodleに提出してください。
例: 123456 山田太郎 集計.xlsx

提出期限は、次回の授業日いっぱいとします。

学習支援システム moodle
https://cclms.kyoto-su.ac.jp/

以上で今回の作業は終了です。おつかれさまでした。

今回紹介したデータ整理に関する機能は、どれも実用性が高いものかと思います。

例えば持っている本のタイトル管理や、どこまで読んだかの管理などに使ったり、簡易的な家計簿・支出管理に使ったり、仕事でも小規模の商品・在庫管理や発注管理、やることリストなどに活用したりと、様々な用途で使えるでしょう。

ぜひ活用してください。

戻る