並べ替えの超応用技！！単純作業ほど奥深い　CSVとVBAの連携技で差をつけろ！！

今日は、実は奥深い並べ替えについてのお話です。

「並べ替え」という機能は、Excelを使う上でのとっても重要な機能の一つです。

どうして重要なのか考えてみましょう。

例えば、1,000件分の表があって「氏名」・「郵便番号」・「住所」・「商品カテゴリー」・「商品名」・「数量」・「単価」・「販売価格」なんていう項目があったとしましょう。

全てのデータの数は、単純計算で8項目×1,000件=8,000個のデータになります。

表自体はWordでも作れますが、こういうものは将来的に分析に使えるのでWordの選択肢は無くなります。

そうすると、Excelで作ることになりますが、ここで重要なのが将来的に分析出来る点です。

分析とは、色々な角度からデータを見て次回の戦略に活かすといった重要な作業です。

色々な角度からデータを簡単に見るためには、色々な条件でパッと並べ替えられたら良いですよね。

単純なようで奥深い並べ替えの機能をしっかり抑えて、バッチリExcelを使いこなしちゃいましょう！！

並べ替えを実際にやってみよう

まず、サンプルデータを用意します。

サンプル用のデータは、この完成版をダウンロードしましょう

↓　↓　↓　↓　↓

サンプルデータ .xlsm

もしくは、ここから擬似個人データをダウンロードしてみよう

↓　↓　↓　↓　↓

なんちゃって個人情報

今回は、こちらのサイトでデータを用意しました。

私が作った「サンプル設定」を参考にご自身で用意してみるのも良いですよ。

使い方は、各項目を設定して【なんちゃって生成】ボタンをクリックすれば、データをダウンロードできます。

※ダウンロードファイルの種類に「csv」を指定しても【dummy.cgi】という名前のファイルがダウンロードされます。
CSVファイルに直す場合は、「.cgi」→「.csv」に書き換えれば、問題なく開くことが出来ます。

「テーブルとして書式設定」を利用して

名前列を【昇順】で並べ替えをします。

並べ替えたが結果がこれです。

どこか変だと思いませんか？どこがおかしいんでしょう？？

名字が「ひらがな」なのは、あとで漢字直すとして(自動生成機能が上手く機能しなかったようです。)

本来は名前は昇順なので、「あ→お」の順番で並べ替えられないといけないんです。

これだと、資料としてきちんと用意したときに「見づらい～」って苦情殺到になりかねません。

アメリカだったら「ブ～！！」の嵐です。

『たかが並べ替え・されど並べ替え』です。

原因と対策を含め基本からもう一度見直してみましょう。

と、その前に「テーブルとして書式設定」の活用法や使い方は、こっちをチェックしましょう！！

↓　↓　↓　↓　↓

仕事にめっちゃ役立つ「表」の作り方

並べ替えの基本をしっかり理解しよう

並べ替えのルールを整理しましょう。

昇順(しょうじゅん)と降順(こうじゅん)について知っておきましょう。

昇順は、【1～10・あ～お】といったように数字の少ない方からまたは、五十音順で並べ替えます。

降順は、【10～1・お～あ】といったように昇順とは逆の順番で並べ替えます。

分かりやすく図にしてみましょう。

イメージで覚えてしまえば、こっちのものです。

お堅い言葉ですが、単純なので覚えておきましょう

正しい並べ替えのルールと仕組みを覚えよう

話は戻って、どうして名前で並べ替えた結果は、きちんと並べ替えが行われなかったのでしょうか？

真実は一つです。

それは、【名前のフリガナが設定されていない】からです。

フリガナ(ルビ)がきちんと設定されていないと、エクセルは独自のルールで並べ変えをしてしまうんです。

コレを防止するためにはきちんとフリガナを設定することが大切です。

ただ、ここが大事なのですが、実はCSVファイルにはフリガナの情報が含まれていないんです。

そもそも「CSVファイル」と言うのは、凄くシンプルな情報の塊なんです。

CSVとは、Comma Separated Values(カンマ・セパレーテッド・バリュース)の略で

「コンマ区切りの値」という意味のファイルです。

CSVファイルを開こうとすると、Excelで自動的に開かれるケースが多いのでExcelで扱うファイルだと思っている方も多いんです。

実は、CSVファイルの正体は昔から使われているシンプルなデータの集まりなんです。

「百聞は一見にしかず」では、実際に正体を見てみましょう。

確認方法は簡単です。

「CSVファイルを右クリック」　→　「プログラムから開く」　→　「メモ帳」を選びましょう。

コレが、CSVファイルの正体です。

文字が集まっていてちょっと気持ち悪ですかね。でもよく見てみると

こんな感じになっています。

カンマで区切られている文字(値)ですね。

これがCSVファイルの正体です。

そうすると、フリガナの情報は含まれていないんですね。

「名前は漢字そのもの」で「都道府県名も漢字そのもの」です。

だから、Excelにはその漢字がどう読むかまでは分からないんです。

だから、この漢字はこう読むんじゃない？って勝手に判断して並べ替えるので、CSVファイルを並べ替えるときは注意が必要です。

たとえ人間でも、「人の名前や住所や地域名」の正しい読み方って分からないですよね。

だから、適当に読んでそれにそって並べ替える作業をする。それと同じようなことをExcelもするんです。

漢字にフリガナを設定して正しく並べ替えてみよう

そこで、今回は漢字にフリガナを設定する方法を説明しましょう。

方法は大きく分けて2種類です。

1. Excelで1つずつフリガナを設定する
2. VBAで一気にフリガナを設定する

どっちが良いですか？

今回は500件のデータを用意しました。

①の方法が良い人は、チャレンジしてみて下さい。私はやりません。

ごめんなさい。ホント嫌なので今回は②でチャレンジします。

VBAについては詳しくは、この記事を読んでみましょう。

↓　↓　↓　↓　↓

仕事がめっちゃ早くなるVBAのメリット・デメリット

VBAと言うと難しそうですが、そんなことは無いので一緒に操作してみましょう。

はじめにダウンロードしてもらったファイルで操作できます。

ここからは、手順を追って一緒にやりましょう。

操作の前に1点注意です。VBA(マクロ)を実行するためには、Excelで『開発』タブを表示させておく必要があるので、その手順から説明します。

Excel2016での解説になります。

①ファイル→オプションボタンをクリックして、下の項目をクリックしましょう。

②リボンに開発タブが表示されれば、VBAを使う準備は出来ました。

Excelは一度閉じておいてもらっても大丈夫です。

③ダウンロードしておいた

『サンプルデータ.xlsm』ファイルを開きましょう。

拡張子が、「xlsm」のファイルは「マクロ有効ブック形式」と言って、VBA(マクロ)を使うためのファイル形式です。

※初めてファイルを開くと上の図のように「セキュリティの警告」という黄色い帯が表示されることがあるので、必ず「コンテンツの有効化ボタン」を押して下さい。押さないとマクロの実行が出来ません。

④開発タブのマクロをクリックします

⑤フリガナの設定を実行します

⑥すべての名前に「フリガナ」が設定されます。

今回はわかりやすいようにマクロの実行前の名前のフリガナを表示しています。

下の図の上下を見比べると、フリガナが順番に設定されていることが分かりますよね。

↓　↓　↓　↓　↓　↓

⑦名前で並べ替えてみましょう

どうですか？今度はちゃんと昇順で並べ変わりましたよね。

ちゃんと正しい「フリガナ」が設定されていることができました。

マクロの解説をしちゃうよ

どうですか？マクロめっちゃ便利ですよね。

一瞬で500件のフリガナの設定が完了しました。

コレを人力でやったとしたら、果たして何時間かかるんでしょうか？？

ん～。考えただけでゾッとします。

では、マクロの解説と「あなたでもできる改造方法」を教えちゃいます。

まずは、コード(ソース)の解説をします。

Option Explicit

Sub フリガナの設定()

Const 開始列 As Long = 2
Const 名前列 As Long = 1
Const ふりがな列 As Long = 2

Dim 最終行 As Long
Dim 最終列 As Long

'フリガナ設定用のカウンター
Dim i As Long

最終行 = Cells(Rows.Count, 1).End(xlUp).Row
最終列 = Cells(1, Columns.Count).End(xlToLeft).Column

'名前の列にフリガナを設定する。
For i = 開始列 To 最終行
Cells(i, 名前列).Characters(1, Len(Cells(i, 名前列))).PhoneticCharacters = Cells(i, ふりがな列).Value
Next i

End Sub

ソースをなるべく分かりやすくしますね。

===========================================

Option Explicit　◀変数の宣言を強制します。特に気にしなくて大丈夫です。

Sub フリガナの設定()　◀VBAのタイトルです。　

Const 開始列 As Long = 2
Const 名前列 As Long = 1
Const ふりがな列 As Long = 2　◀各列数を設定しています。ここの数字を変更するとこで改造出来ます。

Dim 最終行 As Long
Dim 最終列 As Long　◀最終行数と最終列数を取得するための変数の宣言をしています。

‘フリガナ設定用のカウンター
Dim i As Long　◀繰り返し処理の繰り返す回数をセットするための変数の宣言をしています。

最終行 = Cells(Rows.Count, 1).End(xlUp).Row　　　　　◀1列目(名前の列)の行数が何行あるか数えます。
最終列 = Cells(1, Columns.Count).End(xlToLeft).Column　◀1行目の列数(タイトルの行)が何列あるか数えます。

‘名前の列にフリガナを設定する。
For i = 開始列 To 最終行
Cells(i, 名前列).Characters(1, Len(Cells(i, 名前列))).PhoneticCharacters = Cells(i, ふりがな列).Value
Next i

▲フリガナを設定する処理です。
①B2のセルの「ふりがな」をA2の名前の漢字にフリガナとして設定します。
②①の処理をB501まで(500回)繰り返します。

End Sub　◀処理終了です。

===========================================

今回は、なるべく分かりやすいように「定数と変数」に日本語名を使いました。

定数と変数の意味は分かる人用と割り切って下さい。

改造できる部分は、※の部分です。

Const 開始列 As Long = 2
※　Const 名前列 As Long = 1
※　Const ふりがな列 As Long = 2

名前の通り、名前の列数とふりがなの列数を設定しています。

改造して使いたい時は、

名前の列がB列だったら、「1→2」に変更するだけです。

同じようにフリガナを設定してる列がE列だったら、「2→5」に変更するだけです。

つまり、Aから順にアルファベットの数を数えて設定するだけです。

コレで改造終了です。分かりやすいでしょ。

※元々のソースを使いたい場合は、解説上部のソースをコピーして使って下さい。

まとめ

どうでしたでしょうか？

『並べ替え』単純なようで奥深いということを分かってもらえたでしょうか？

今回は、CSVやマクロが出てきましたが、超実践的な内容となっていますのでちょっと難しかったですね。

『たかが並べ替え、されど並べ替え』

並べ替えのことをきちんと理解して、実践でバリバリ使いこなせるようになってくださいね。

最後までお読みいただきありがとうございました。

この記事が、少しでもお役に立てたら幸いです。 ご意見もいただけると励みになります。

それではまた、お会いしましょう。　グッバイビー