ビジネス データの分析では、多くの場合、Excel で日付値を操作して、「今日の収益はいくらですか?」または「先週の同じ日と比較してどうですか?」などの質問に答える必要があります。 Excel が値を日付として認識しない場合、これは困難になる可能性があります。
残念ながら、特に複数のユーザーがこの情報を入力したり、他のシステムからコピーして貼り付けたり、データベースからインポートしたりする場合、これは珍しいことではありません。
この記事では、テキストを日付値に変換するための 4 つの異なるシナリオと解決策について説明します。
ピリオドを含む日付
おそらく初心者が Excel に日付を入力するときに最もよくある間違いの 1 つは、日、月、年を区切るためにピリオド文字を使用してしまうことです。
Excel はこれを日付値として認識せず、そのままテキストとして保存します。ただし、検索と置換ツールを使用すると、この問題を解決できます。ピリオドをスラッシュ (/) に置き換えることで、Excel は値を日付として自動的に識別します。
検索と置換を実行する列を選択します。
[ホーム] > [検索と選択] > [置換] をクリックするか、Ctrl+H を押します。
[検索と置換] ウィンドウで、[検索する文字列] フィールドにピリオド (.) を入力し、[置換文字列] フィールドにスラッシュ (/) を入力します。次に、「すべて置換」をクリックします。
すべてのピリオドはスラッシュに変換され、Excel は新しい形式を日付として認識します。
スプレッドシートのデータが定期的に変更されており、このシナリオに対する自動化されたソリューションが必要な場合は、 SUBSTITUTE 関数を 使用できます。
=VALUE(SUBSTITUTE(A2,".","/"))
SUBSTITUTE 関数はテキスト関数であるため、単独で日付に変換することはできません。 VALUE 関数はテキスト値を数値に変換します。
結果を以下に示します。値は日付としてフォーマットする必要があります。
これは、[ホーム] タブの [数値形式] リストを使用して行うことができます。
ここでのピリオド区切り文字の例は典型的なものです。ただし、同じ手法を使用して区切り文字を置き換えたり置換したりすることができます。
yyyymmdd形式の変換
以下に示す形式で日付を受け取る場合は、別のアプローチが必要になります。
この形式は、さまざまな国が日付値を保存する方法についてのあいまいさを排除するため、テクノロジーにおいては非常に標準的なものです。ただし、Excel は最初はそれを理解できません。
手動で簡単に解決するには、 Text to Columns を使用 できます。
変換する必要がある値の範囲を選択し、[データ] > [テキストを列に変換] をクリックします。
[テキストを列に変換] ウィザードが表示されます。以下の図に示すように、最初の 2 つのステップで [次へ] をクリックしてステップ 3 に進みます。 [日付] を選択し、セルで使用されている日付形式をリストから選択します。この例では、YMD 形式を扱います。
数式解が必要な場合は、Date 関数を使用して日付を作成できます。
これは、セルの内容から日付の 3 つの部分 (日、月、年) を抽出するために、テキスト関数 Left、Mid、および Right と一緒に使用されます。
以下の式は、サンプル データを使用してこの式を示しています。
=日付(左(A2,4),中(A2,5,2),右(A2,2))
これらの手法のいずれかを使用すると、任意の 8 桁の数値を変換できます。たとえば、日付を ddmmyyyy 形式または mmddyyyy 形式で受け取る場合があります。
DATEVALUE 関数と VALUE 関数
場合によっては、問題の原因が区切り文字ではなく、単にテキストとして保存されているために不自然な日付構造になっている場合があります。
以下にさまざまな構造の日付のリストを示しますが、それらはすべて日付として認識できます。残念ながら、これらはテキストとして保存されているため、変換する必要があります。
このようなシナリオでは、さまざまな手法を使用して簡単に変換できます。
この記事では、これらのシナリオを処理するための 2 つの関数について説明したいと思いました。それは DATEVALUE と VALUE です。
DATEVALUE 関数はテキストを日付値に変換します (おそらく、それが来るのを見ていたでしょう)。一方、VALUE 関数はテキストを一般的な数値に変換します。それらの違いは最小限です。
上の画像では、値の 1 つに時間情報も含まれています。これは、関数の小さな違いを示すものになります。
以下の DATEVALUE 式は、それぞれを日付値に変換します。
=DATEVALUE(A2)
行 4 の結果から時間がどのように削除されているかに注目してください。この式は厳密に日付値のみを返します。結果は引き続き日付としてフォーマットする必要があります。
次の式では VALUE 関数を使用します。
=VALUE(A2)
この式は、時間値も維持される行 4 を除いて同じ結果を生成します。
結果は日付と時刻として、または時刻値を非表示にする (ただし削除はできない) 日付として書式設定できます。
