現在、一般法人向けライセンスである「Microsoft 365 Business ～」シリーズを契約している場合に利用できる「Lists」アプリについて連載をしています。
前回、「Lists」内のデータを集計してみました。
しかし、全体の合計数は集計できたものの、各地名ごとに集計する方法がなかったんですね。
というわけで、今回は地名ごとなどにデータをまとめる「グループ化」と特定のデータを抽出する「フィルター」について見ていきたいと思います。
元のエクセルデータを修正する
まず、前回、「Lists」で集計したリストの合計を確認してみましょう。
上のように、リスト内の「人口・統計(2019)」列の合計を表示したわけですが、この状態だと例えば「”池尻”だけの合計」や「”太子堂”だけの合計」などが集計できないのです。
上の赤く囲んだ部分ごとに、合計を表示するにはどうしたらよいでしょうか・・・。
そのためには、地名だけで「グループ化」できるようになれば、合計数を表示できそうです。
しかし、現状のデータのままでは「池尻」、「三宿」、「太子堂」という文字だけでグループ化ができません。
「Lists」では、文字列を操作する関数などが使えないので、ここはやはり「エクセル側」でデータを整形してみたいと思います。
元データの地域名には特徴がある
「Lists」で「地域名」列に表示されているデータは、元データのエクセルでは上のように同じく「地域名」列のデータとなっています。
このエクセルデータの「地域名」列で地名だけを別の列に表示してみたいと思います。
この地域名には、特徴があり、すべて「～丁目」となっています。
したがって、まずは「丁」の位置を探すために「FIND」関数で文字の先頭から何文字目の位置にあるかを探してみましょう。
まずは、地名だけを表示するための列を「D列」に作成し、タイトルを「丁の位置」としてみました。
次に「セルD2」を選択し、関数から「FIND」を使って、引数を上のように指定します。
最後の「開始位置」は先頭から探すので、省略しても構いません。
「対象」は、「セルA2」を選択したのですが、「スピル機能」が使える最新のエクセルを使っている場合は、上のように自動的に、【各行において「地域名」列の各行を参照する】という意味の指定が自動的に入力されます。
これで、関数ウィンドウの「OK」ボタンをクリックすると、「D列」に各行の「FIND」関数の結果が表示されます。
「D列(列名：丁の位置)」に、各行における「地域名列の先頭からの”丁”の位置」が入力できました。
次に、「LEFT」関数を使って、地名だけを抜き取りましょう。
「丁」の位置が分かれば、その前には必ず「1丁目」のように数字がありますから、「丁」の位置から2を引いた文字数分を左から取れば地名となります。
例えば、「池尻1丁目」であれば、「丁」の位置は4となります。
4から2を引いた2文字分を先頭から取ると「池尻」となります。
「太子堂1丁目」であれば、「丁」の位置は5となります。
5から2を引いた3文字分を先頭から取ると「太子堂」となります。
「LEFT」関数の引数としては、以下のようになります。
文字列は「地域名」列となる。
文字数は、先頭から「丁が見つかった位置(D列)より2文字前」つまり「丁の位置－2」となる。
エクセルデータに、「地名だけ」の列が作成できました。
Listsへ修正したエクセルデータを挿入する
さて、それでは先ほど修正したエクセルデータを「Lists」へ挿入してみましょう。
エクセルからデータを「Lists」へ挿入する場合は、以下の2点に気を付けるのでしたね。
- xlsxファイルで保存する
- 対象となるデータにテーブル定義をする
元々、データの「3列・100行目」までにテーブル定義として「Lists用」という名前を付けていたわけですが、「丁の位置」と「地名だけ」という2つの列が加わっても、自動的にテーブル定義の範囲が「5列・100行目」と変更されました。
エクセルを「.xlsx」形式で保存されているのを確認して、「Lists」に「新しいリスト」として登録したいと思います。
※「Lists」の起動と新しいリストの作り方が分からない場合は、以下を参照してください
Listsのトップ画面から「新規作成」をクリックします。
インポート元から「Excel」をクリックします。
先ほど保存し直したエクセルファイルを選択すると、上のように読み込むデータが表示されます。
エクセルで作成した「丁の位置」列は、「LEFT」関数での計算用で使っただけなので、「Lists」には挿入しなくてもよいでしょう。
「Lists」では、データを取り込む列を任意に指定できるのです。
「丁の位置」列は「インポートしない」を設定します。
「次へ」をクリックして、リスト名や保存場所を設定して「作成」をクリックします。
「インポートしない」設定にした「丁の位置」列は取り込まれず、「地名だけ」列を新たに追加した表が「Lists」に新しく作成できました。
グループごとに集計する
さて、それでは各地名ごとに合計を表示してみたいと思います。
「地名だけ」列をクリックして、表示されたメニューから「地名だけでグループ化」をクリックします。
同じ地名のデータが上のように、グループ化された状態となります。
ここまで操作すると、後の合計数の表示は簡単にできます。
前回、合計を算出した時と同様、「人口・統計(2019)」をクリックして、メニューから「合計」、さらにそのメニューから「合計」をクリックします。
グループ化された地名ごとに、人口の合計数が表示されます。
最後には、全体の合計も表示されていますね。
特定の地域をフィルターする
せっかく、地域ごとに合計を表示できたので、特定の地域のデータだけを表示してみましょう。
「地域だけ」列をクリックして、「フィルター基準」をクリックします。
現在グループ化されている地域名がすべて表示されます。
例えば、「三軒茶屋」を選択してみます。
チェックボックスをクリックして、「適用」をクリックします。
「地名だけ」列に「三軒茶屋」と登録されているデータがフィルターされます。
「地名だけ」列には、フィルターが適用されているマークが付きます。
「グループ化」した際に、人口の合計を表示したままなので、フィルターを適用した後でも合計数はそのまま表示されているのが分かります。
フィルター数の確認や解除の方法
フィルターをかけた後、何件のフィルタをかけているかも簡単に確認できます。
例えば、上のようにメニューの「フィルター」の横に「6」と表示されている場合は、現在6件のデータをフィルター中であるという意味になります。
フィルターを一度にすべて解除したい場合は、「すべてクリア」でフィルターを解除できます。
最後にまとめ
エクセルではおなじみの「グループ化」や「フィルタ」は、「Lists」でも同じような感覚で利用できます。
今回の説明では、元データとなっていたエクセルにグループ化できるようなデータがなかったので、エクセル側で関数を使ったデータ整形から行いました。
最初から手入力で「Lists」にデータを打ち込む場合などは、列の構造などを考えながらデータを登録していく方がいいでしょう。
今回のような場合、エクセル側での処理とはなりますが、関数をうまく使って列の構造を修正しながら「Lists」に取り込む方法もありますので、ご参考になさってください。
次回は、「Lists」を他のユーザーと共有する方法を見ていきたいと思います。