更にその隣で「YMD」が選択されていることを確認します。 これで、Excelさんも「日付」と理解してくれるデータになったので、Excelの日付特有の機能も使うことができます！, 「#」がズラ〜っと並んだセルがある場合は、列幅が足りないだけなので、列幅を広げれば、, Officeの使いこなしテクニックをメールでお届け！メールマガジンポータルサイト「まぐまぐ」の殿堂入りメルマガです！ 分割前と分割後でデータは同じだけど、分割後にお好みのデータ形式にしてしまえる設定を使って、Excelさんに理解してもらえる「日付」の状態にしてしまおうというわけです！, まずは、変換したい日付を表す8桁の数字、または日付の数字に見える「文字」を範囲選択します。, 今回は、データを分割するわけではないので、分割に関する設定を変更する必要はありません。 規定では、区切り位置を使うときに最初に範囲選択した、元データがある範囲の、先頭のセルが指定されます。 エクセルでデータ処理をする場合、日付をシリアル値ではなく、文字列のデータとして管理したい場合があります。 要は「パターン1」から「パターン2」に変換をしたいという場合です。 全角やカンマ区切りの数字などは自動で変換されます。「VALUE 関数」で変換もできます。, エクセルでは数値と判断できる文字列を数式に使用したとき、自動で数値に変換されます。, これらの文字列を数値に変換するには、[セル] をクリックしてエラーチェックの機能から [数値に変換する] をクリックします。変換したいセルを範囲選択してから行うと一括で変換できます。, このような自動で変換できる文字列は「VALUE 関数」を使用しても数値に変換できます。, また、変換したい [セル] をコピーして [形式を選択して貼り付け] から [値] をクリックしても数値に一括変換できます。, このようなときは、変換できない文字を数値と一緒に入力しないで表示形式として入力します。, セルの書式設定で0"円"と入力すると数値の 100 が100円のように数値のまま文字列を表示できます。これで計算してもエラーになりません。, 数字が 3 桁であるような規則性があるときは「LEFT 関数」、「MID 関数」、「RIGHT 関数」を使用して数字の部分だけを抽出します。, 結果が左揃えや右揃えになることがあります。これはセルの書式設定が標準か文字列かの違いです。セルをクリックしてから入力すると標準になり、セルをダブルクリックしてから入力すると文字列となります。どちらの場合でもセルの書式設定を変更すればどちらかに統一できます。, 文字に規則性があるときは「SUBSTITUTE 関数」を使用して文字列を削除します。, これも同様に必ず数値として表示したいときは「VALUE 関数」で数式の結果を囲みます。, 数字が区切り文字などで分割されているときは「区切り位置ウィザード」から数値に分割できます。, [カンマやタブなどの区切り文字によって・・・] をクリックして [次へ] をクリックします。, [その他] をチェックし、[区切り文字] を入力して [完了] をクリックします。 ここでは区切り文字に-を入力します。.
TEXT関数と言えば、数値の表示形式を変える関数なので、日付や時刻とは何の関係もないように見えます。しかし、Excelにおいて、日付や時刻の実体は数値（シリアル値）であり、しかも日付の表示形式（書式記号）もあるため、日付や時刻の変換は原則としてTEXT関数を使うべきなのです。, しかし、そのことを全く理解していないのか、日付を曜日に変換するだけなのにわざわざWEEKDAY関数やCHOOSE関数を使うといった意味不明な解説をしているサイトが散見されます。勉強不足と言っても過言ではありません。, そこで、今回は、TEXT関数を用いて、日付や時刻のシリアル値を数値に変換する方法、数値を日付や時刻のシリアル値に変換する方法、元号や曜日を取り出す方法などについて出題します。, TEXT関数の基本と、数値を文字列に変換する方法についてはこちらの記事をご覧ください。, セルA1に「2023/4/5」と入力した。TEXT関数を用いて年・月・日の部分を取り出しなさい。また、「2023年4月5日」にしなさい。, 年・月・日は通常、YEAR、MONTH、DAY関数を使って求めますが、TEXT関数を使って求めることもできます。4桁の西暦年、月、日の書式記号はそれぞれ「yyyy」「m」「d」です（参考：【Excel】セルの表示形式「ユーザー定義」書式記号完全総まとめ）。, 「=TEXT(A1,"yyyy")」「=TEXT(A1,"m")」「=TEXT(A1,"d")」と入力します。それぞれ2023、4、5となります。ただし、TEXT関数で取り出したものは文字列です。, これらをくっつけて「2023年4月5日」とするには「=TEXT(A1,"yyyy年m月d日")」とします。, 年月日はそれぞれYEAR、MONTH、DAY関数を使って求められるにもかかわらず、なぜTEXT関数を使うのかというと、表示形式の書式記号を別のセルに入力して、それを参照することができるからです。, 日付から曜日を求めるには表示形式を使います。WEEKDAYではありません（参考：【Excel】日付から曜日を自動で表示するのにWEEKDAY関数を使ってはいけない）。Excel関数の場合はTEXT関数です。, 曜日の書式記号は「aaa」です。「=TEXT(A1,"aaa")」と入力します。水曜日となります。ただし、TEXT関数を用いた時点でシリアル値ではなくなるので、日付としての計算はできなくなります。, セルA1に「2023/4/5」と入力した。TEXT関数を用いて8桁の数「20230405」にしなさい。, 年月日はyyyymmddです。「=TEXT(A1,"yyyymmdd")」と入力します。これで8桁の数になります。ただし、これは文字列です。, 数値化する場合は1を掛けます（参考：【Excel】数値と文字列の違い、数値と文字列の変換方法の総まとめ）。, １をかけたときに、日付の表示形式が適用されて＃＃＃＃＃＃＃＃となることがあります。, セルB1に「12:34」と入力した。TEXT関数を用いて4桁の数「1234」にしなさい。, 時間・分はhhmmです。「=TEXT(B1,"hhmm")*1」と入力します。表示形式を「標準」にすれば4桁の数になります。, セルA1に8桁の数「20230405」と入力した。TEXT関数を用いて文字列「2023年04月05日」にしなさい。また、シリアル値に変換して「2023/4/5」と表示しなさい。, ＊補足＊セルA1の表示形式は「標準」ですが、数値や文字列であっても問題ありません。また、先頭にシングルクォーテーションがあっても問題ありません。, 「2023年04月05日」とするには「=TEXT(A1,"#年00月00日")」とします。, スラッシュで区切ります。「=TEXT(A1,"#!/00!/00")」と入力します。スラッシュで区切られましたが、これは文字列であってシリアル値（整数値）ではありません。なお、半角のスラッシュは直前に！が必要です。, そこで1を掛けて数値化します。「45021」となります。スラッシュで区切られた文字列に1を掛けると数値化され、日付のシリアル値になります（参考：【Excel】日付を表す文字列に「１」を掛けるだけでシリアル値に変換できる）。, 日付のシリアル値を求めるのであればスラッシュではなくハイフンでもよいです。「=TEXT(A1,"#-00-00")」と入力します。, セルA1に4桁の数「1234」と入力した。TEXT関数を用いてシリアル値に変換して「12:34」と表示しなさい。, 「=TEXT(A1,"#! Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ, 【Excel】TEXT関数で数値を日付や時刻のシリアル値に変換する、日付の変換方法まとめ, https://www.waenavi.com/entry/20191029/1572352019, 【Excel】日付から曜日を自動で表示するのにWEEKDAY関数を使ってはいけない, 【Excel】LEFT関数、RIGHT関数、MID関数で文字列を抽出する方法の基本例題, 【令和れいわ】Excelを業務で使用している場合の新元号対応と注意点まとめ（Office2010以降）, https://www.waenavi.com/entry/20190401/1554087637, 【基本情報】2019秋期基本情報技術者試験の表計算をExcel＆VBAで完全再現してみた！, 【書評】Excel関数を独学で勉強するための関数解説本おすすめランキングBEST9で打線を組む, 【書評】WordやExcel文書のデザイン改善に役立つ本おすすめランキングBEST9, 【VBA】Excelマクロ資格「VBAエキスパート試験」独学するなら公式テキストで勉強しよう. 数値を文字列に変換するには「数値を文字列に変換する」をご覧ください。 Webサイトのアクセス解析に使うGoogleアナリティクスで、エクスポートしたデータなんかがそうです。, このままでは、Excelさんは「このデータは「数値」だぞ」、「このデータは「文字列」だぞ」と考えているので、日付の計算のような、Excelの日付特有の機能を使うことができません。 エクセルの日付に関する基本情報をまとめました。表示形式とは？シリアル値とは？そして、文字列の日付を関数で日付の表示形式に変更する方法をご説明しております。最後に日付に関する様々な計算方法をリストアップしております。 Be Cool Usersマガジン--Word,Excel,PowerPointの技, 解説記事内の画像はExcel 2016のものですが、操作方法は下記のバージョンで同じです。, Be Cool Usersマガジン--Word,Excel,PowerPointの技. 元のデータに上書きしてよければ、そのままでOK。, 元データは残し、変換後のデータを別のセルに表示させたいときには、［表示先］の右端にあるボタンをクリックすると、, セルの選択の邪魔にならないよう、ダイアログボックスが小さくなります。 というわけで、［区切り位置指定ウィザード 1/3］では、最初から選択されている［カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ］のまま、［次へ］ボタンをクリックします。, データを分割するわけではないので、［区切り位置指定ウィザード 2/3］でも、分割に関する設定を変更する必要はありません。 エクセルの文字列を数値に変換する方法を紹介します。 全角やカンマ区切りの数字などは自動で変換されます。「VALUE 関数」で変換もできます。 円やハイフンなどの文字列が含まれているときの対処法も紹介します。 1. TEXT関数と言えば、数値の表示形式を変える関数なので、日付や時刻とは何の関係もないように見えます。しかし、Excelにおいて、日付や時刻の実体は数値（シリアル値）であり、しかも日付の表示形式（書式記号）もあるため、日付や時刻の変換は原則としてTEXT関数を使うべきなのです。 「19720110」のような日付を表す8桁の数字を、Excelさんにも日付として理解してもらえる「日付データ」に変換したい！それなら、この方法を使えば一発です！Excel・エクセルの技！ シリアル値を文字列データに変換したい. :00")」と入力します。日付はハイフンでつなぎ、時刻はコロンでつなぎます。日付と時刻の間には半角のスペースが必要です。, 表示形式を「yyyy/m/d h:mm:ss」にすると「2023/4/5 12:34:56」となります。, セルA1に「2023/4/5」と入力した。TEXT関数を用いて文字列「0405」にしなさい。また、数値に変換して「405」と表示しなさい。, 日付から月日だけ抽出して4桁で表示するには「=TEXT(A1,"mmdd")」とします。, セルA1に「H300303」と入力した。TEXT関数を用いて、2018/3/3（H30.3.3）のシリアル値にしなさい。, 「H300303」は文字列なのでこのままではTEXT関数を使うことができません。先頭の文字と数字に分けて考えます。右の数字は「=RIGHT(A1,6)」です（参考：【Excel】LEFT関数、RIGHT関数、MID関数で文字列を抽出する方法の基本例題）。, ところで、TEXT関数の第1引数は数値でなければなりませんが、RIGHT関数の答えは文字列です。しかし、数値化できる文字列は1を掛けなくてもTEXT関数の第1引数として使用することができ、自動的に数値化されます。したがって、1を掛ける必要はありません。, TEXT関数で「#-00-00」の形式に変換して、1を掛けることでシリアル値にすることができます。この他、「平成30年03月03日」のような日本語も１を掛けることでシリアル値にすることができます。, セルA1に「20180303」と入力した。TEXT関数を用いて「H300303」にしなさい。, 7桁の和暦の表示形式は「geemmdd」です。TEXT関数の第1引数に入れることによってシリアル値になり、geemmddによって7桁の和暦の形式になります。, ＊補足＊日付の形式で1をかけるとシリアル値になる文字列は、1をかけなくてもTEXT関数の第1引数にすることができます。, 「=TEXT(A1,"g")」と入力します。「g」は元号の書式記号です。昭和はS、平成はH、令和はRになります。, これを利用してIF関数で元号の判定をすることができます。平成の日付だけ〇になります。, A列の日付を「平成e年m月d日」の表示形式にしなさい。ただし1年の場合は元年とする。, 「=TEXT(A1,"ggge年m月d日")」と入力します。元年が1年になります。表示形式だけで元年の表示にすることはできません。, Youtube わえなびチャンネルhttps://www.youtube.com/c/waenavijp, Copyright(C)2018-2020 waenavi, All rights reserved. 特に何もせず［次へ］ボタンをクリックします。, ［区切り位置指定ウィザード 3/3］では、分割後の設定を行います。
:00")」と入力します。「12:34」となりますが、これは文字列であってシリアル値（小数値）ではありません。, そこで1を掛けて数値化します。「0.523611」となります。コロンで区切られた文字列に1を掛けると数値化され、時刻のシリアル値になります。, セルA1に14桁の数「20230405123456」と入力した。TEXT関数を用いてシリアル値に変換して「2023/4/5 12:34:56」と表示しなさい。, 「=TEXT(A23,"#-00-00 00!:00! 変換後のデータを表示させたい範囲の、先頭のセルをクリックすると、表示先を指定することができます。, 「日付」に変換することができました！ 日付を表す8桁の数字。 ［列のデータ形式］で［日付］を選択します。 これは現在の数字データが、「Year・Month・Day」の順番で入力されていますよ、とExcelさんにお伝えするものです。, ［表示先］には、変換後のデータの表示先を指定します。 [www.waenavi.com 定礎 平成三十年八月]
人間はこれが日付を表すものだと分かるのですが、Excelさんには「日付」ではなく、普通の「数値」とみなされてしまいます。, 「日付の数字に見える「文字」」という、「数値」ではなく「文字列」とみなされるデータもあったりします。 変換後は「日付」の形式にしてくださいね、という意味です。 エクセルについて 例えば表の中にあるセルの値が ”1”だったら ”東京 ” ”2”だったら ”千葉 ” と変換するにはどうしたらよいでしょうか？ if関数を使うのでしょうか？… / 2件程度ならif関数、それ以上ならvlookup関数が便利ですよ。 (c1に数値が入って…
偏差値とは何なのか、偏差値を求める方法をExcelを使ってわかりやすく紹介しています。模試の結果などで偏差値が書いてありますが、偏差値の意味やどのように計算するかご存知でしょうか？Excelを使えば簡単に偏差値を計算できます。 Excelで日付特有の機能を使うためには、こういったパッと見、日付に見える「数値」や「文字列」を、Excelさんにも「日付」だと理解してもらえる「日付データ」に変換します。, 今回の変換で使うのは、区切り位置の機能。 本来、区切り位置は、カンマやスペースなどの区切り文字を境にデータを分割し、更に分割後のデータをお好みのデータ形式にしてしまえる機能です。, でも今回は、データにカンマやスペースなどの区切り文字は入っていないので、データが分割されるわけではありません。