【Excel】並べ替えで日付順にならない!Excelの文字列型日付をシリアル値に変換する手順

Excelでデータを並べ替える際、日付順にならないという問題に直面することがあります。これは、日付が数値ではなく文字列としてExcelに認識されている場合に発生します。文字列として扱われた日付は、本来の時系列順ではなく、五十音順やアルファベット順で並べ替えられてしまいます。この記事では、Excelで日付が正しく並べ替えられない原因と、文字列型の日付をExcelが認識できるシリアル値に変換する具体的な手順を解説します。

この変換を行うことで、Excelでの日付の並べ替えが正確に行えるようになり、データの分析や管理が格段に効率化されます。

文字列型日付が並べ替えで日付順にならない原因

Excelで日付が正しく並べ替えられない主な原因は、その日付データが文字列として入力されていることです。Excelは、日付を内部的に「シリアル値」と呼ばれる数値で管理しています。シリアル値とは、1900年1月1日を1として、それ以降の日付を日数で表した数値です。例えば、2023年10月26日は、Excelでは45224というシリアル値で認識されています。

しかし、日付が「2023/10/26」や「2023年10月26日」といった形式で入力されていても、セルの表示形式が「標準」や「文字列」になっている場合、Excelはその値を単なるテキストとして扱います。テキストとして扱われた場合、並べ替え機能はアルファベットや記号の順序に基づいて処理するため、本来の日付順になりません。これは、手入力や他のシステムからのデータインポート時に発生しやすい問題です。

文字列型日付をシリアル値に変換する手順

文字列型の日付をExcelが認識できるシリアル値に変換するには、いくつかの方法があります。ここでは、最も一般的で確実な「表示形式の変更」と「値の貼り付け」を組み合わせる方法を解説します。

1. 対象のセル範囲を選択する
   日付データが入力されているセル範囲をマウスでドラッグして選択します。複数の列にわたって日付データがある場合は、それらの列をまとめて選択しても構いません。
2. セルの表示形式を「日付」に変更する
   選択したセル範囲を右クリックし、「セルの書式設定」を選択します。表示されるダイアログボックスで、「表示形式」タブを選び、「分類」から「日付」を選択します。
3. 好みの表示形式を選択し「OK」をクリックする
   右側の「表示形式」リストから、希望する日付の表示形式(例:「2012年3月14日」や「2012/3/14」)を選び、「OK」ボタンをクリックします。この時点では、見た目が日付形式に変わるだけで、内部的なデータ型はまだ文字列のままの可能性があります。
4. 「値の貼り付け」機能でデータ型を確定させる
   Excelのリボンメニューで、「コピー」ボタン(またはCtrl+C)をクリックして、選択したセル範囲をコピーします。次に、コピーしたセル範囲のどこか(または別の場所)を右クリックし、「形式を選択して貼り付け」を選びます。
5. 「値」を選択し「OK」をクリックする
   表示される「形式を選択して貼り付け」ダイアログボックスで、「貼り付け」オプションの「値」を選択します。これにより、セルの書式設定ではなく、セルの値のみが貼り付けられます。
6. 「OK」ボタンをクリックして貼り付けを実行する
   「OK」ボタンをクリックすると、コピーしたセル範囲の値が、データ型を数値(シリアル値)として保持したまま貼り付けられます。これにより、Excelはこれらの値を日付として正しく認識できるようになります。
7. 並べ替えを実行する
   変換が完了したら、日付データが含まれる列を基準に並べ替えを実行します。Excelの「データ」タブにある「並べ替え」機能を使えば、正しく日付順に並べ替えられるはずです。

この手順により、文字列として認識されていた日付データが、Excelのシリアル値として正しく扱われるようになります。もし、上記の手順でうまくいかない場合は、元データの形式に問題がある可能性があります。

その他の変換方法と注意点

上記の手順以外にも、文字列型日付をシリアル値に変換する方法はいくつか存在します。それぞれの方法にはメリット・デメリットがあり、状況に応じて使い分けることが重要です。

TEXTAFTER関数・TEXTBEFORE関数・MID関数などを使う方法

Excelの新しいバージョン(Microsoft 365など)では、TEXTAFTER関数やTEXTBEFORE関数、MID関数などを使用して、日付の文字列を分解し、DATE関数で再構築する方法も有効です。例えば、「2023/10/26」という文字列を分解してDATE関数に渡せば、正しい日付(シリアル値)を作成できます。

具体的な数式例としては、セルA1に「2023/10/26」と文字列で入っている場合、別のセルに「=DATE(LEFT(A1,4),MID(A1,6,2),RIGHT(A1,2))」と入力することで、日付に変換できます。しかし、この方法は日付の区切り文字(/や-)や形式が一定でないと、数式を調整する必要があり、手間がかかることがあります。

「区切り位置」機能を使う方法

「データ」タブにある「区切り位置」機能も、文字列型日付の変換に利用できます。この機能は、本来はCSVファイルなどのデータを区切って列に分割するためのものですが、日付の変換にも応用できます。

1. 日付データのある列を選択する
   変換したい文字列型日付のデータが入っている列全体を選択します。
2. 「データ」タブから「区切り位置」を選択する
   Excelのリボンメニューから「データ」タブをクリックし、「区切り位置」ボタンを選択します。
3. 「元のデータの形式」で「固定長フィールド」または「カンマやタブなどの区切り文字」を選択する
   ダイアログボックスが表示されたら、データの形式に合わせていずれかを選択します。日付の形式が一定であれば、「固定長フィールド」で区切り位置を指定するか、「カンマやタブなどの区切り文字」で区切り文字(例:「/」「-」)を指定します。
4. プレビューを確認し、区切り位置を調整する
   プレビュー画面で、年・月・日などが正しく分割されるように調整します。
5. 「列のデータ形式」で「日付」を選択し、形式を選ぶ
   次の画面で、「列のデータ形式」の項目にある「日付」を選択します。右側のドロップダウンリストで、元のデータの形式に合ったもの(例:「YMD」)を選択します。
6. 「完了」ボタンをクリックする
   最後に「完了」ボタンをクリックすると、選択した列のデータが日付形式(シリアル値)に変換されます。

この方法は、大量のデータを一度に変換したい場合に便利ですが、元のデータの区切り方や形式が統一されていないと、予期せぬ結果になることがあります。

Power Queryを利用する方法

より複雑なデータ変換や、定期的なデータ更新が必要な場合は、Power Query(データの取得と変換)機能の利用が推奨されます。Power Queryを使用すると、データの取り込みから変換、読み込みまでの一連のプロセスを自動化できます。

1. Excelテーブルとしてデータを読み込む
   まず、日付データを含む範囲をExcelテーブルに変換するか、外部データソースとしてPower Queryに読み込みます。
2. 日付列のデータ型を変更する
   Power Queryエディターが開いたら、日付データが含まれる列を選択します。列ヘッダーの左側にあるアイコンをクリックしてデータ型を変更できます。ここで「日付」を選択します。
3. 必要に応じて日付形式を調整する
   Power Queryは、多くの一般的な日付形式を自動的に認識します。もし認識されない場合は、「変換」タブの「データ型」グループにある「日付/時刻」オプションから、より詳細な形式指定を行うことができます。
4. 「閉じて読み込む」を選択する
   変換が完了したら、「ホーム」タブの「閉じて読み込む」をクリックして、変換されたデータをExcelシートに読み込みます。

Power Queryは、一度設定すれば以降のデータ更新時に自動で変換が適用されるため、継続的なデータ処理において非常に強力なツールとなります。

よくある誤解とトラブルシューティング

文字列型日付の変換作業中に、いくつかの落とし穴があります。ここでは、よくある誤解や、変換後に発生する可能性のあるトラブルとその対処法を解説します。

変換後も日付順にならない場合

上記の手順で変換したにも関わらず、並べ替えが正しく行われない場合、いくつかの原因が考えられます。

• セルの表示形式が「文字列」になっている
  変換後も、セルの表示形式が意図せず「文字列」になってしまっている可能性があります。再度セルの書式設定を行い、「日付」または「標準」形式に変更してください。
• 日付の一部が正しく認識されていない
  例えば、「2023年10月」のように年と月のみの場合や、「2023/10/26 10:30」のように時刻情報が含まれる場合、Excelが日付として一意に認識できないことがあります。その場合は、DATEVALUE関数やTIMEVALUE関数を組み合わせるか、Power Queryでデータ型を慎重に設定する必要があります。
• 混在しているデータ
  同じ列に、日付データだけでなく、日付ではない文字列(例:「未定」「不明」など)が混在している場合、Excelは列全体を日付として認識できなくなります。このような場合は、まず日付ではないデータを別の列に移動させるか、空欄に置換するなどの前処理が必要です。

変換時にエラーが発生する場合

数式や「区切り位置」機能を使った変換時にエラーが発生することがあります。これは、元の文字列がExcelの期待する日付形式から大きく外れている場合に起こりやすいです。

• DATE関数でのエラー
  DATE関数に渡す年・月・日の値が数値でなかったり、範囲外(例:月が13以上)だったりするとエラーになります。数式で各要素を正しく抽出・変換できているか確認してください。
• 「区切り位置」でのエラー
  「区切り位置」機能で「日付」を選択した際にエラーが出る場合は、元のデータ形式の選択や、指定した「YMD」などの形式が、実際のデータと一致していない可能性があります。プレビュー画面でデータがどのように分割されているか、再度確認してください。

Excelのバージョンによる違い

TEXTAFTER関数やTEXTBEFORE関数といった新しい関数は、Excel for Microsoft 365などの比較的新しいバージョンでしか利用できません。Excel 2019やそれ以前のバージョンを使用している場合は、これらの関数は使えません。その際は、「表示形式の変更」と「値の貼り付け」を組み合わせる方法や、「区切り位置」機能、またはPower Queryを利用するのが現実的です。

Power QueryはExcel 2016以降のバージョンに標準搭載されていますが、それ以前のバージョンではアドインとして追加する必要がある場合があります。

まとめ

Excelで日付が並べ替えられない問題は、日付データが文字列として扱われていることが原因であることがほとんどです。この記事で解説した「表示形式の変更」と「値の貼り付け」を組み合わせる手順や、TEXTAFTER関数、区切り位置機能、Power Queryなどを活用することで、文字列型日付をExcelが認識できるシリアル値に正確に変換できます。これにより、日付データの並べ替えや分析がスムーズに行えるようになります。今後は、データのインポート時や手入力時に、セルの表示形式を適切に設定する習慣をつけ、このようなトラブルを未然に防ぐようにしましょう。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】