Windows および Mac の Excel でデータ変換を制御する方法

Microsoft Excel の内部変換機能に長年苦労してきましたが、ユーザーは Excel の内部データ変換機能を無効にできるようになりました。

Excel は入力値のデータ型を判断しようとします。そして、ほとんどの人にとって、それは役に立ちます。 たとえば、日付を表す文字列を入力すると、Excel はそれを日付値に自動的に変換します。 日付を入力するつもりなら、問題ありません。 そうでなければ、運が悪いです。 入力すると、そのエントリは日付となり、元の文字列値を取得する方法はありません。

デフォルトでは、このような Excel 変換動作は 4 つあります。 2021 年に Microsoft はトグル オプションを公開し、ユーザーはそのうち 3 つの実装方法を選択できるようになりましたが、文字列変換は選択できませんでした。 良いニュース: Windows と Mac のバージョンには、次のことを可能にするオプションが含まれています。 Excel での文字列変換動作を無効にする。 以下に、Microsoft 365 に特有の Excel の問題と解決策の詳細を示します。

ジャンプ先:

Excelの変換機能は間違いが多い

この Excel の問題は、多くのユーザー、特に科学コミュニティにとって迷惑です。 実際、私たちはそう言われています 遺伝学者 Excel の文字列変換動作を引き続き回避するのではなく、遺伝子の短い表記法を変更しました。

例を考えてみましょう。March1 は、Membrane Associated Ring-CH-Type Finger 1 遺伝子の短縮名で、日付としては 3 月 1 日のように見えます。March1 と入力すると、Excel によって自動的に日付に変換されます。 これは、Excel が役立つように、値が実際には日付であると想定しているためです。

Ctrl + Z を押して変換を元に戻すこともできますが、機能しません。 代わりに、Excel は自動的に適用された日付形式を削除し、日付のシリアル値を残します。 この時点では、値が数値になっているため、エントリをテキストに変換することもできません。 元の文字列 MARCH1 とその意味は失われています。

Excel の変換機能は、次の 4 種類の入力値を変換します。

• Excel は、Excel が日付として解釈できるテキストエントリを日付値に変換します。
• Excel は先頭のゼロを削除し、値を数値に変換します。
• Excel では、15 桁以上の数値は科学表記に切り捨てられます。
• Excel は、文字 E の周囲の数字を科学表記法に変換します。

図A に、各タイプの入力値の例を示します。 入力された値と、それが Excel で変換された値を確認できます。 データを入力する前にセルの書式設定を強制されるため、一部のユーザーにとってこれは悪夢です。 忘れてしまった場合、回避策はなく、最初からやり直す必要があります。

図A

前述したように、Excel が日付に変換する文字列の自動変換を元に戻す簡単な修正はありません。 データを入力する前に、ターゲット セルをテキストとして書式設定する必要があります。 先頭にゼロが含まれる値や文字 E が含まれる値についても同様です。ユーザーが事後的に簡単に修正できる唯一の変換は、大きな数値を科学的表記法に変換することです。 これらの値の形式を数値形式に変更して科学表記法を削除できます。

新しい Excel オプションにより、変換機能を無効にすることができます

数年前、Excel はついに 3 つの切り替えオプションを実装し、ユーザーが 4 つの内部変換動作のうち 3 つを無効にできるようにしました。 残念ながら、これらのオプションは文字列変換動作には役に立ちませんでした。 遺伝学者の回避策を知って初めて、Microsoft は最終的にユーザーが内部文字列変換を無効にできる 4 番目のオプションを追加しました。 この最後の選択肢を理解するには、遺伝学者である必要はありません。 実際、内部文字列変換の問題は、他の 3 つの変換動作よりも多くのユーザーを悩ませました。

その最終的な修正はこの夏から公開され始めており、今では誰もがそれを入手できるはずです。 最後に、日付を表す文字列を Excel がどのように処理するかを制御できます。

Microsoft 365 でこれらのオプションを見つけるには、次の手順に従います。

1. 「ファイル」タブをクリックします。
2. [オプション]を選択するか、[その他]、[オプション]の順に選択します。
3. 左側のペインで「データ」をクリックします。 (元々、設定は[詳細設定]セクションにありました。[データ]セクションの方が分かりやすく、見つけやすいはずです。)
4. [自動データ変換]セクションには、いくつかのオプションが表示されます (図B）。

図B

すべての内部変換動作を無効にできるようになりました。 すべての動作を無効にするには、最初のオプション「Excel にテキストを入力、貼り付け、またはロードするときに、以下のすべてのデフォルト データ変換を有効にする」のチェックを外します。 または、必要に応じて、そのオプションをオンのままにし、他の 1 つ以上のオプションのチェックを外すこともできます。

最後のオプション[.CSV ファイルまたは同様のファイルを読み込むときに、自動数値変換を通知する]は、Excel が自動的に変換する可能性のあるデータをインポートするときに便利です。 これは、受信データを変換する前に警告するシンプルなオプションです。 この時点で、データを変換せずにインポートすることを選択できます。

これまでと同じデフォルトの動作をそのままにしておくことができます。 または、4 つの新しいオプションの 1 つ以上を使用して、デフォルトの動作を変更することもできます。 ただし、これはアプリケーション レベルの動作であるため、1 つのブックに設定すると、Excel はそれをすべてのブックに適用します。 おそらく将来的には、このコントロールが個々のワークブックに拡張されることになるでしょう。 さらに、マクロを使用してこれらの変換オプションを無効にする方法はありません。