使用しているレポートを CSV ファイルとして保存し、データを Excel スプレッドシートにコピー/貼り付けしてみましたが、問題は解決されません。
Excel は日付データの列を mm/dd/yyyy 形式に変換しますが、データはソース データベースと CSV ファイルでは dd/mm/yyyy として保存されます。そのため、データの半分は認識されず、残りの半分は間違っています。
たとえば、生年月日が 2020 年 1 月 10 日の場合、抽出元のデータベースでは日付は 10/01/2020 と表示され、Excel はコピーされたとおりに保存しますが、形式が mm/dd/yyyy であるため、別の列に計算を入れて「日付」を使用すると、Excel は日付を 2020 年 10 月 1 日と認識します。
2020 年 1 月 24 日などの日付は、24/01/2020 として貼り付けられて保存されますが、当然 24 のような月は存在しないため、それを使用して計算することはできません。個々のセルをチェックして、使用されている形式を確認しましたが、10/01/2020 は英語アメリカ形式で表示されています。
これをオーストラリア英語に変更すると、dd/mm 値が入れ替わるため、値は依然として不正確になります。そもそも正しい日付形式として読み込まれていないため、数値に変換しても機能しません。この書式設定の問題に対する解決策はありますか。
ありがとう、ナタリー
答え1
注意: Excel は、CSV ファイルを解釈し、Excel に表示するために、コンピューターの地域設定を使用します。
日付が国際形式 dd/mm/yyyy である場合は、(Windows 10 を使用している場合) Windows タスクバー (Excel からではなく) から地域設定を検索し、次に「追加の日付、時刻、および地域の設定」を選択します。これは、従来の Windows 7 コントロール パネルで開きます。日付設定に移動し、csv ファイルと同じ形式に変更します。インポート後に元に戻す必要がある場合があるため、以前の設定をメモしておきます。
また、たとえば、csv ファイルの数値形式に応じて、小数点記号を . から に、またはその逆に変更することもできます。
複数の形式が互換性のない最悪のシナリオでは、テキストとしてインポートしてから、面倒なテキスト関数を使用して自分で日付に変換する必要があるかもしれません。
=DATE(RIGHT(A1,4), MID(A1, FIND("/", A1)+1, 2), LEFT(A1,2))
答え2
あなたは特別な人です！この問題を抱えているほとんどの人は、CSV を開いてもうまくいかないという事実にすっかりうんざりしています。他の方法では解決できないのです。あなたは CSV からコピーして貼り付けるだけでなく、わざわざテキストとしてフォーマットした列に取り込むこともいとわないのです。おめでとうございます！
つまり、テキスト列にテキストとして取り込むことができます。もう 1 つ手順を追加して、希望する形式 (mm/dd/yyyy) の日付にすることもできます。つまり、あと 2 つの手順です。
まず、列のデータの形式を変更します。数値形式タブで日付コレクションを選択し、タブの中央にある形式の例を確認します。中央の例の選択肢の下に「ロケール (場所):」というラベルの付いたドロップダウンがあります。それをスクロールして「英語 (オーストラリア)」を選択し、選択します。
上記の日付形式の例が変更され、必要なバージョンを正確に選択できます。
もちろん、テキスト表現は変更されません。これが実際のステップです。強制的に変更する必要があります。これを行うには多くの方法がありますが、適切な方法はリボン メニューの [データ] タブに移動することです。Text to Columnsそれを見つけてクリックします。
区切り文字付きか固定幅かは実際には重要ではありません。区切り文字付きを選択した場合は、何もチェックされていないことを確認して「区切り文字なし」を選択し、固定幅の場合と同じ結果になります。ステップ 3/3 では、変換後に列の形式の種類を設定できます。
「日付」ラジオ ボタンを選択し、その右側のドロップダウンで を選択しますDMY。入力の形式ではなく、出力する内容を選択します。
「完了」をクリックすると作業は完了です。
将来的には、Power Query を検討することをお勧めします。データ用の優れた CSV があるので、PQ を使用するのが最も簡単です。数式やプログラミングは不要で、ファイルからデータを引き出し、不要な列をすべて削除し、日付の列を指定した日付形式に変換するだけです。
すべてを保存し、新しいデータをインポートするたびに、スプレッドシートのテーブルを更新するか、ヘルパー スプレッドシートを使用してこれを実行させ、そのたびに結果をプロダクション シートにインポートします。プロダクション スプレッドシートにヘルパー ページがある場合 (この方法を使用すると、結果を最初に「ロード」したときに自動的に作成されます)、メイン シートにコピーして貼り付けるか、数式を使用して情報をコピーします。
マクロを使用できる/許可されている場合は、クエリを更新するためのマクロ付きのボタンを設定したり、さらにマクロを追加したりすることもできます。
他に何もなければ、変換された日付をロードして、必要なときにコピーして貼り付ける「ヘルパー スプレッドシート」があれば便利です。ただし、上記の方法は毎回非常に簡単なので、... どちらも 6 つ、もう 6 つという感じでしょうか。