メインコンテンツへスキップ
見出し画像

[Excel]データ集計をどう行うか? 実際の自治体データをもとに考える③ 下準備

    実際の自治体データを使って集計してみるシリース、その③です。

    【まとめ】集計に入る前にやる下準備

    【説明】
    集計すべきデータが目の前にあったら、すぐに集計したくなるのは人情です。
    でも、そこを堪えて、まずは下準備をしましょう。
    下準備をしておくことで、次回以降(あるいは後任)が楽になります。

    サンプルデータ
    茨城県ホームページ 「旅館業」 のページの下の方の 「7.旅館業法に基づく許可施設一覧」にある 旅館業法に基づく許可施設一覧(令和5年11月15日現在)(エクセル:350KB) 

    1 ファイル情報のシートを作る

    「ファイル情報」シートを作り、そこにファイルの情報を記載します
    これが、今後(自分や後任)の助けになります。
    具体的な操作例は以下の通り(シートの作り方は他の方法もあります。)。
     
    ①シートを追加する(「+」マークをクリック)。

    画像
    「+」キーでシートが増えます。

    ②シートの名前を変える(タブをダブルクリック → backキーでシート名を削る → ファイル情報 と入れる)

    画像
    Sheet2という名前のシートが出来ます。
    ダブルクリックすると、上の表にシート名が選択されるので、
    backキーでこのシート名を消します。
    画像
    シート名が「ファイル情報」になります。

    ③必要な情報を入力する。

    画像
    ま

    どんな情報を載せるかのルールはありませんが、マイルール(組織ルール)を決めておくと効率的です(ただし、あんまり複雑にはしない。)。

    本例では以下としています。
    ・出典:データが掲載されていたURLやサーバの保管場所等
    集計の根拠になるので重要です。
    それが分からないと、後任が更新するとき大変です。
    *URLは Ctrl + K で「ハイパーリンクの編集」が開くので「アドレス」に入れます。サーバのアドレスでも構いません。ワンクリックで開けます。

    画像
    Ctrl + K で上のウィンドウが開きます。
    URLやサーバーのディレクトリは、下の「アドレス」」に入れます。

    ・作成日、作成者、連絡先等
    これがないと「名無しエクセル」になります。
    個人名を入れるのは、役人は嫌がる傾向にありますが、内容に疑義や誤りがある場合、連絡をとれないと困ります。

    ・更新履歴
    集計内容にもよりますが、長く使う集計の場合、あった方が便利です。

    これらは、それぞれ別シートにしてもいいのですが、シートが増えすぎるのも面倒なので、1シートにまとめておいた方が楽でしょう。
    *私の場合は、「ファイルの保存場所」や「ファイル名(いずれも自動抽出)」、「フォント名」なども入れています。

    2 元データのシート名を「元データ」にする

    集計する前のデータは、加工せず、そのまま残しておきます。
    もし間違えても、「スタート」から始められます。
    元のデータの一部を誤って削除や修正してしまうと、元の状態に戻すのは大変です。「0スタート」どころか「マイナススタート」になってしまいます。
    シート名は「元データ」でなくとも構いません。「元」でも「オリジナル」でも「原票」でも、同僚や後任が見て分かれば何でも構いません。
    各ファイルで統一しておくと混乱を防げます。

    画像

    3 元データをコピーして、集計用データを作る

    集計用に弄るデータを作ります。
    集計用のデータの作り方は、2つあります。

    ①「元データ」シートそのものをコピーする。
    シートのコピーは、Ctrlを押しながら、タブを左クリックしたま、右へドラッグすれば「元データ(2)」というシートが出来ます。

    画像
    Ctrlキーを押しながら右へドラッグでシートがコピーできます。

    このシートを「DATA」という名前にして(名前は何でもいい)、集計していきます。

    画像
    シートを「DATA」に変えます。

    この場合、元データの「悪いところ」もコピーされます(例:セルの結合等)。
    それを避けるには、②の方法でやります。

    ②新しいシートを作って、「元データ」し0とのデータだけを全部コピーし、新しいシートに「値のみ」張り付ける。
    *全部コピーは Ctrl +A の後に Ctrl + C です。
    値のみ貼り付けは、
    Alt +Ctrl + V
    又は
    Alt → E→ S
    で「形式を選択して貼り付け」が開く → V → OK
    です。(私は後者を多用しています。3キー同時押しの前者は苦手です。)

    この場合、計算式は消えることに注意が必要です。
    しかし、このデータ内を弄る(数値を変更する等)予定はないので、むしろ計算式がない方が、データが変わらないという点でメリットがあります。
    計算式や書式を活かしたいなら①にします。

    ②の場合、列幅の修正やフォントの変更などの手間が生じます。
    私はなるべく「素」の状態から始めたいので②を多用していますが、今回は①の方法でコピーします。

    4 表頭(ヘッダー)を固定する。

    下にスクロールしても表頭(ヘッダー)が消えないように固定します。
    右にスクロースしてもNOだけは消えないように、B4セルを選択したらAlt → W(表示) → F(ウィンドウ枠の固定)→ F(ウィンドウ枠固定)の順で押します。(解除するときも同じ順番で押す)

    画像

    本当は、右スクロールしても E列の「営業施設の名称」は常に表示させておきたいっところですが、そうすると、次のF列以降の表示範囲が狭くなってしまうので、今回は避けました。
    データの並び具合やディスプレイの大きさ、拡大割合に応じて、どこで固定するか決めればいいでしょう(固定も解除も3秒で出来るので、とりあえず、どこかで固定しておけばいいでしょう。)

    不要な行は消す

    もう一つ。
    1行目の表の名前、2行目の「令和5年12月31日現在」(一番右にある)という情報は、集計には不要です。
    表頭の上に空欄セルがあると、集計できなかったり、ミスの元となりますので、この2行は消しておきます。
    *今回はそうではありませんが、表の名前は、セルの結合が使われている場合が多く、集計の際の邪魔になります。

    画像
    1、2行目を選択します。
    マウスで行番号を選択します。
    Shift+Spaceで行選択、という説明も多いですが、
    日本語入力モードでは効きません。
    画像
    右クリック→D で選択行が削除されます
    画像
    上の2行が削除され、表頭(ヘッダー)が1行目になりました

    これで、概ねの下準備は終了です。
    ただ、これを毎回やるのは、ちょっと面倒かもしれません。
    個人的には、「集計用ひな形」ファイルと作り、そこに元データシートを追加することで、上記の作業を効率化しています(やり忘れを避けられます。)。

    以上、長くなりましたが、参考になれば幸いです。

    次の記事はこちらです。
    [Excel]データ集計をどう行うか? 実際の自治体データをもとに考える④ 「営業の種類」別の数を、計算式を使わないで、どう出す?


    あなたへのおすすめ