Aさん 日付データの「曜日」を条件に集計するなど、数値や日付/時刻の一部の情報を条件にしたいですが、どう集計すれば効率的ですかね？
森田 その場合は、関数の「TEXT」を活用し、集計の条件となる列を用意してあげると良いですよ！
では、TEXTの使い方について解説していきますね。
はじめに
この記事は関数の概要を把握していることが前提です。
関数の概要については以下の記事をご参照ください。
はじめに この記事は数式の概要を把握していることが前提です。 参考記事 数式の概要の詳細は以下の記事をご参照ください。 関数とは 関数とは、数式の構成要素の1つであり、固有の計算/処理の機能がセットされた数式のことです。 …
元データの数値や日付/時刻を特定の表示形式の「文字列」へ変換したい場合は「TEXT」が有効
実務で扱う表データでは、数値や日付/時刻の列が用意されていることは一般的です。
ただし、集計する内容によっては、一工夫が必要になるケースもあります。
たとえば、日付の「曜日」だけを条件に集計したい場合、日付の列だけだと集計が難しいです。
日付/時刻はシリアル値で管理されているため、表示形式で「年」や「月」、「日」、「曜日」などの情報で表示させることはできるものの、集計時は該当のシリアル値しか集計の条件にできないためです。
シリアル値とは、Excel上の時刻/時刻を管理する数値で1900年1月1日を起点に何日目かをカウントしたもの。
よって、元データとなる数値や日付/時刻から、特定の表示形式を適用した「文字列」へ変換し、集計の条件となる列を事前に用意することが必要です。
こんな場合、関数の「TEXT」を使うことで、数値や日付/時刻を指定の表示形式の「文字列」へ自動的に変換することが可能となります。
ちなみに、TEXTは「テキスト」と呼びます。
よって、数値や日付/時刻を特定の表示形式の「文字列」へ変換したい場合はTEXTを使用していきましょう。
TEXTは他の関数と組み合わせて独自の文字列を生成するといった使い方も一般的です。
詳細の内容を知りたい場合、一例として以下の記事をご参照ください。
はじめに 本題に入る前に、この記事がおすすめな人を挙げてみます。 集計や分析を行なう機会が多い人 エクセルを扱 …
TEXTの構文
TEXTの構文は以下の通りです。
=TEXT(値,表示形式)
数値に指定した書式を設定し、文字列に変換した結果を返します。
| 引数名 | 必須 | データ型 | 説明 |
|---|---|---|---|
| 値 | ○ | すべて | 変換対象のセル（値が数値、日付/時刻）を指定します。 |
| 表示形式 | ○ | 文字列 | 「値」に適用する表示形式のコードを文字列で指定します。 |
引数「表示形式」は文字列で指定する必要があるため、必ずダブルクォーテーション（”）で囲ってください（例：”aaa”、”yyyymm”など）。
【参考】引数「表示形式」は「セルの書式設定」ダイアログと共通
TEXTの引数「表示形式」に指定するコードは、「セルの書式設定」ダイアログの「表示形式」タブで指定する内容と共通です。
標準的な表示形式であれば、任意のものを選択後、「分類」の「ユーザー定義」の「種類」ボックスで表示形式コードを確認することが可能です。
「セルの書式設定」ダイアログの表示形式で標準以外（ユーザー定義）の代表的なコードについては以下の記事をご参照ください。
前回セルの書式設定のユーザー定義のメリットについて触れました。 Exceのセル書式設定「表示形式」のユーザー定 …
前回セルの書式設定のユーザー定義の【数値】に関してのおすすめ設定について触れました。 Exceのセル書式設定「 …
前回セルの書式設定のユーザー定義の【日付】に関してのおすすめ設定について触れました。 Exceのセル書式設定「 …
【参考】TEXTは「文字列操作関数」
あくまで参考情報となりますが、TEXTはリボン「数式」タブの関数ライブラリの「文字列操作」に分類されています。
実際にTEXTを活用する際は、以下で解説しているように直接入力で挿入していきましょう。
TEXTの使用結果イメージ
TEXTで数値や日付/時刻を特定の表示形式の「文字列」へ変換したイメージは以下の通りです。
今回は「受注日」列のデータから「曜日」の文字列へ変換しました。
これでK2~K8セルのSUMIFSで問題なく曜日別の「金額」を合計できました。
なお、TEXTは原則一つの関数で一つのセルのみが変換対象です。
ベースの数式をセットしたら、他のセルへペーストしましょう。
TEXTの数式の挿入手順
上記の結果を得るための手順は以下の通りです。
- 関数を挿入するセルを選択
※今回はC2セル - 「=te」と入力
- サジェストから「TEXT」を選択し、「Tab」キーで確定
- 変換対象のセルを選択
※今回はB2セル - コンマ（,）を入力
- 任意の表示形式を指定
※今回は「aaa」を指定 - 「Enter」キーで確定
- 1行目の数式をコピーし、以降のセルへペースト
※今回はC3~C11セルへペースト
手順②の際にIMEを半角英数モードにすること。
テーブルの場合、手順⑧は不要（全レコードへ数式が自動的にコピーされる）。
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ブックを開いたら、次の手順を実施してください。（今までの解説のまとめです）
- 関数を挿入するセルを選択
※今回はC2セル - 「=te」と入力
- サジェストから「TEXT」を選択し、「Tab」キーで確定
- 変換対象のセルを選択
※今回はB2セル - コンマ（,）を入力
- 任意の表示形式を指定
※今回は「aaa」を指定 - 「Enter」キーで確定
- 1行目の数式をコピーし、以降のセルへペースト
※今回はC3~C11セルへペースト
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
TEXTは集計/分析の切り口を広げる列の追加に役立つ関数の一つです。
定期的に数値や日付/時刻を元に列の追加を行う機会があるなら、ぜひ覚えておいた方が良いですね。
なお、TEXT以外にもExcelでのデータ整形の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 TEXTはVALUEなどと真逆の機能ですが、データの不備修正というよりも、集計/分析をしやすくするための列追加の用途で使うことが多いですね。
あと、セルの値を参照した数式でメッセージやコードを作成する際にも活躍する関数なので、応用ケースは多いです。
ぜひ、この機会に覚えてください！
