"８桁の数値で入力された日付" を「日付（シリアル値）」に変換する（その３）

"８桁の数値で入力された日付" は「日付（シリアル値）」として使えません

Excelでは「日付」を "シリアル値で管理" します

　Excelは「日付」を「１９００年１月１日を起点」とした、"シリアル値" で管理しています。

　「年・月・日」を「 ／（スラッシュ）」で区切って「２０２０/１/１」のように入力すれば、Excelは自動的に「日付」と判断して "シリアル値" に変換されます。

８桁の数値「20200101」は、「日付」と認識されない

　８桁の数値は、セルの書式設定の分類を「日付」にしても "日付形式" にはなりません。

　　　

「年・月・日」を「 ／ 」で区切ると、「日付」と認識される

　「年」と「月」と「日」の間に「 ／ 」を入れることで、Excelは「日付」と認識するので、セルの書式設定の分類を「日付」にすることで、日付形式にすることができます。

　　　

表示は「2020年1月1日」でも、実際にはシリアル値に変換されています

　シリアル値はセルの設定を変更することで「日付形式」として表示することができます。

　セルの設定で分類を「標準」にすればシリアル値が表示されます。

　　　

Excelで「日付」を "８桁の数値" で入力する場合があります

　会計システムでデータをExcelで使いたい場合や、年賀状ソフトの移行時などに内部データを出力（書き出し）する場合、出力された「日付データ」が "８桁の数値" になっている場合があります。

• システム（ソフト）でデータを出力（書き出し）した場合
• 入力作業の効率化

"８桁の数値" で入力されたデータは「計算」に使えない

　「日付」データが "シリアル値" であれば、「日数計算」や「時間計算」をすることができます。

　　　

「８桁の数値」を「日付形式（シリアル値）」に変換する方法は複数あります。

１．"TEXT関数" を使う

２．"DATE関数" と "MID関数" を使う

３．"取得と変換" の機能を使う

 　１番目（その１）と、２番目（その２）の変換方法は、数式（関数）を使う方法でしたが、今回（その３）では外部プログラム（アドイン）のPoweQueryを使って変換する方法を説明します。 

"取得と変換"を使って、８桁の数値を「シリアル値」に変換する

　今回はExcel2016から追加された新機能「取得と変換」を使って、８桁の数値で入力された「日付」を日付形式（シリアル値）に変換します。

「取得と変換」で、PoweQueryを起動する

　新しく追加された「取得と変換」の機能は、従来アドイン（外部プログラム）として起動していた "PoweQuery" を簡単に使えるようにしたものです。

　取得と変換の機能を選ぶと、自動的に"PoweQuery" が立ち上がります。

　一度 PoweQueryが立ち上がると「終了（閉じる）」までは、Excelの画面には戻ることができないので注意してください。

1. 変換する「表」のどこか１カ所のセルを選択状態にする
2. 「データ」タブを選択
3. 「テーブルまたは範囲から」をクリック
4. 変換する「表」が正しく選択されているのを確認（※ 自動で選択される）
5. 「先頭行をテーブルの見出しとして･･･」にチェックを入れる
6. 「OK」ボタンをクリック　

　　　

「例からの列」で、「日付」の列を作成

　PoweQuery の画面で、"列の追加" タブにある「例からの列」の機能で「日付」の列を作成します。

　"列の作成ボックス" で「２０２０」と入力すると、"変換例（候補）が表示される" ので、その中から「８桁の数値からの日付」を選択します。

1. PoweQueryの画面で、「列の追加」タブを選択
2. 「例からの列」をクリック
3. 画面の右に「列の作成ボックス?」が表示されるので、そのまま何もせず「２０２０」と入力すると、"変換例" が表示される
4. "変換例" の中から「2020/01/01  ８桁の数値からの日付」を選択して、"Enterキー" を２回押す
5. 続いて、"CTRL＋Enter" を押す（※ CTRLキーと、Enterキーを同時に押す）

　　　

「閉じて読み込む」で、PoweQueryを終了して新しいシートを作成

　「日付」に変換された、"新しい列" が作成される。

　「閉じて読み込む」ことで、変換内容が反映した「新しいシート」が作成されます。

1. 「ホーム」タブを選択
2. 「閉じて読み込む」をクリック

　　

「新しいシート」が作成される

　PoweQueryが終了して、「元の表」に"日付に変換された列" が追加された新しいシートが追加されました。

　追加されたシートは"クエリと連携" しており、変換作業の内容は「クエリ」として保存されています。

　この方法では、「元の表」には手を加えていないので何度でも変更・修正することができます。

　　　

「元の表」に追加すると、「変換したシート」に反映される

　「取得と変換」の機能で「追加されたシート」は、クエリと連携しているので "更新" することで追加（修正）した内容が反映されます。

1. 「元の表」にデータを「２行」追加
2. 「追加したシート」の「表」の部分のどこか１カ所を選択
3. 「クエリ」タブを選択（※ 「表」にカーソルがないと表示されない）
4. 「更新」をクリック

　　　　　　

"クエリ" を変更する

　「取得と変換」で作成した新しい「表（シート）」は、"クエリ" と関連（連携）づけられています。

"クエリ" を呼び出して、作業内容を変更する

　"クエリ" は作業内容を「ステップ」として管理しています。

　"クエリ" に「ステップ（作業）」を追加することで、保存されている作業内容を変更することができます。
　「取得と変換」を使う方法は、「数式（関数）」を使う方法と異なり「元の表」に手を加えないので何度でもやり直したり、変更したりすることができます。

変換元の「列」を削除する

　今回は"８桁の数値" 形式の「日付」をExcelが「日付」と認識する形式に変換しました。

　新しく作成した「表（シート）」には、「８桁の数値」と「日付形式」の両方のフィールド（列）がありますが、「８桁の数値」の方は不要なので、クエリを修正して不要な「列」を削除するようにします。

　　　

"クエリ" を呼び出す

　「取得と変換（PoweQuery）」で作成したシートにある「表」は、"クエリ" と連携（連動）しています。

　「表」を選択することで表示される「クエリタブ」から、「編集」を選択することで、PoweQuery を呼び出して編集することができます。

1. 「表」のどこか１カ所を選択状態にする
2. 「クエリ」タブを選択（※ 「表」にカーソルがないと表示されない）
3. 「編集」をクリック

　　　

「列の削除」コマンドで、不要な「列」を削除

1. "削除" したい「列」の "項目名" の部分をクリックして "列を選択" する（※ 列全体が「緑色」になる）
2. 「列の削除」をクリック

　　　

不要な「列」が削除され、クエリのステップが追加された

　クエリの "ステップ" に「削除された列」が追加されました。

　この作業内容は保存されるので、元のデータに追加すれば「日付の列」のみが作成されるようになります。

1. 「閉じて読み込む」をクリック　　

　　　

「日付」の列だけになりました

　　　

　「取得と変換」は多少手順が複雑ですが、作業内容が "保存" されるのが特徴です。

　「元の表」に手を加えないので、何度もやり直したり、色々な作業を試したりすることができます。

　数式（関数）を使う方法は、手順は簡単ですが「元のデータ」を変更・修正した場合、その都度数式を手直しする必要があります。

　数式を修正する時に「参照先」を間違えたり、「数式」の一部分に「データ」を入力してしまい、そこの「値」が固定されたりすることがあり神経を使う必要があります。

　「取得と変換（PoweQuery）」を使う方法は、一度 "クエリ" を作成すれば、それからは自動的に変換されるようになるので、会計システムから「書き出したデータ」を毎月変換する必要がある場合などにはとても有効な方法です。

k-ohmori9616.hatenablog.com