ついに夏本番!さぁ、家族でキャンプに行くぞ! >>

エクセルでの和暦から西暦への変換について



お願いします。
列に、昭和と平成が入り混じって入力してあります。
昭和50年8月30日生まれであれば、500830。
平成2年12月4日生まれであれば、021204。

この、6桁の数字を西暦0000/00/00と変えたいのですが、どのような方法があるのか教えて頂きたいです。

又、その西暦を使用して、違う列に年齢が出るようにしたいのです。

よろしくお願いいたします。

例 550828→1980/8/28→38歳

質問者からの補足コメント

  • すみません。自分が早生まれなので年齢間違いです。

    ありがとうございます!
    明日再チャレンジしてみたいと思います!

      補足日時:2017/09/16 22:31

A 回答 (4件)

裏技を使いました。

数字を分解する必要はありません。

A1:500830
=DATEDIF(DATEVALUE(IF(LEFT(TEXT(A1,"000000"),2)*1>29,"S","H")&TEXT(A1*1,"00!/00!/00")),NOW()+1,"Y")

ポイント:*1>29,
平成30の生年月日は存在しないので、昭和にしました。

うまくない場合は、
DATEDIF(DATEVALUE("S" & TEXT(A1*1,"00!/00!/00")),NOW()+1,"Y")
としても可能です。マニュアルですが、S-昭和, H-平成, M-明治を入れます。

問題はここ:NOW()+1
法律的には誕生日の前日午後12時に1歳年齢が加算されるというそうで、+1 にするそうです。つまり、4月1日生まれは早生まれになるわけです。

ひとつ条件があるのは、数字は、数値にしていることだけです。書式文字列(@)にしてしまうと、うまく行きません。逆に、文字列接頭辞(')を使っていたら、最初のLEFT(TEXT(A1,...),2)が不要です。
    • good
    • 0
この回答へのお礼

助かりました!


ありがとうございました!

お礼日時:2017/09/17 11:22

》 平成2年12月4日生まれであれば、021204


「021204」は数値でなく、文字列で入力されているのですか?
昭和生まれの「500830」も文字列ですか?
昭和2年12月4日生まれは存在しないのですか?
    • good
    • 0

また面倒な…


文字列を分解して計算するしかないですよ。
文字を左から2文字抜き取ってそれを「年」として置き換えます。
ただし、昭和17年生まれの人や昭和5年生まれの人をどう判断するかという問題があります。
 「いやいや、そんなお年寄りいないから」
というなら単純にIF関数で割り振ればいいでしょう。

50歳以上の人がいないなら昭和47を基準にして、47よりも小さければ平成、47以上なら昭和として扱えばよいということなので、
あとは文字列をシリアル値に直して日付にして表示形式を指定してしまえばよい。

 =TEXT(IF(VALUE(MID(A1,1,2))<47,DATEVALUE("平成"&MID(A1,1,2)&"年"&MID(A1,3,2)&"月"&MID(A1,5,2)&"日"),DATEVALUE("昭和"&MID(A1,1,2)&"年"&MID(A1,3,2)&"月"&MID(A1,5,2)&"日")),"yyyy/mm/dd")

こんな感じ。

・・・
長くて何しているか分からないだろ。
数式に使用している関数それぞれについて、ちゃんと調べて理解したうえで使いましょう。
 TEXT
 IF
 VALUE
 MID
 DATEVALUE
使用しているのはたったこれだけです。
    • good
    • 0

関数を使って…



[A1]=500830
[B1]=DATEVALUE(LEFT(A1,2)+IF(LEFT(A1,2)*1>29,1925,1988)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2))
[C1]=DATEDIF(B1,TODAY(),"Y")&"歳"

頭2桁が29までなら、平成 → +1988したら西暦
頭2桁が30以上なら、昭和 → +1925したら西暦

と扱っています。
以降、1行目のセルをコピーで。

> 例 550828→1980/8/28→38歳

これ、37歳ではなく38歳にしたい??
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aと関連する良く見られている質問

Qエクセルで 異なるデータを含む2つのデータを 1つの表にまとめる方法を教えてください

初めて質問をします。
あまりエクセルが詳しくなく わからないので 教えて下さい。

2つのデータがあります。
Aには仕入れ数、Bには売り上げ数があります。
双方の商品名はすべて一致するわけではなく、「AにあってBにない」「BにあってAがない」ものもあります。


    データA 【仕入れ】     データB 【売上】

管理No 商品  仕入れ数   管理No 商品   売上数
   1  りんご  3        2  みかん  8
   2  みかん  8       3    バナナ    3
   3  バナナ    4      1  りんご    2
   4  いちご    1      7   もも     3
   5  オレンジ  3      10    キウイ     2
   6  レモン    3     11    ぶどう     2
   7  もも     3     13    大根      1
   8  なし     2
   9  カキ    5
  10  キウイ  2
  11  ぶどう    1
  12   マンゴー   1


上記の2つのデータを以下のようにまとめたいのですが 可能でしょうか?
(ABすべての商品名を出して それぞれの数を表示する)

管理No   商品    仕入れ数      売上数
   1   りんご      3        2
   2   みかん      8      8
    3   ばなな      4        3
   4   いちご      1        0
   5    オレンジ  3        0
   6   レモン      3     0
   7   もも       3     3
   8   なし       2     0
   9   カキ      5     0
   10   キウイ      2      2
   11   ぶどう      1       2
   12   マンゴー    1      0
   13   大根      0      1
      


数字がずれていてすみません。
どうぞよろしくお願いします。

初めて質問をします。
あまりエクセルが詳しくなく わからないので 教えて下さい。

2つのデータがあります。
Aには仕入れ数、Bには売り上げ数があります。
双方の商品名はすべて一致するわけではなく、「AにあってBにない」「BにあってAがない」ものもあります。


    データA 【仕入れ】     データB 【売上】

管理No 商品  仕入れ数   管理No 商品   売上数
   1  りんご  3        2  みかん  8
   2  みかん  8       ...続きを読む

Aベストアンサー

こんにちは!

>(ABすべての商品名を出して それぞれの数を表示する)
というコトですので、手っ取り早くVBAでの一例です。

↓の画像のようにそれぞれのシート名は「仕入れ」・「売上」となっていて、Sheet3に表示するとします。
尚、「商品」の「管理No」はシリアルナンバーのように決まっているものとします。
そして、Sheet3の1行目項目行は入力済みという前提です。

Alt+F11キー → メニュー → 挿入 → 標準モジュール → VBE画面のカーソルが点滅しているところに
↓のコードをコピー&ペースト → Excel画面に戻り(VBE画面を閉じて)マクロを実行してみてください。
(Alt+F8キー → マクロ → マクロ実行です)

Sub Sample1() '//この行から//
Dim i As Long, lastRow1 As Long, lastRow2 As Long, lastRow3 As Long
Dim c As Range, r As Range
Dim wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("仕入れ")
Set wS2 = Worksheets("売上")
Application.ScreenUpdating = False
With Worksheets("Sheet3") '//←「Sheet3」はまとめるシート名に!"//
lastRow3 = .Cells(Rows.Count, "A").End(xlUp).Row
If lastRow3 > 1 Then
Range(.Cells(2, "A"), .Cells(lastRow3, "D")).ClearContents
End If
.Range("E:F").Insert
lastRow1 = wS1.Cells(Rows.Count, "A").End(xlUp).Row
Range(wS1.Cells(1, "A"), wS1.Cells(lastRow1, "A")).Copy .Range("E1")
lastRow2 = wS2.Cells(Rows.Count, "A").End(xlUp).Row
Range(wS2.Cells(2, "A"), wS2.Cells(lastRow2, "A")).Copy .Cells(Rows.Count, "E").End(xlUp).Offset(1)
.Range("E:E").AdvancedFilter Action:=xlFilterCopy, copytorange:=.Range("F1"), unique:=True
.Range("F:F").Sort key1:=.Range("F1"), order1:=xlAscending, Header:=xlYes
lastRow3 = .Cells(Rows.Count, "F").End(xlUp).Row
Range(.Cells(2, "F"), .Cells(lastRow3, "F")).Copy .Range("A2")
For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
Set c = wS1.Range("A:A").Find(what:=.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
.Cells(i, "B") = c.Offset(, 1)
.Cells(i, "C") = c.Offset(, 2)
End If
Set r = wS2.Range("A:A").Find(what:=.Cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
If Not r Is Nothing Then
If .Cells(i, "B") = "" Then
.Cells(i, "B") = r.Offset(, 1)
End If
.Cells(i, "D") = r.Offset(, 2)
End If
Next i
.Range("E:F").Delete
.Columns.AutoFit
End With
Application.ScreenUpdating = True
MsgBox "完了"
End Sub '//この行まで//

※ 関数でないので、データ変更があるたびにマクロを実行する必要があります。

※ コード内の「Sheet3」の部分は実際のシート名に変更してください。m(_ _)m

こんにちは!

>(ABすべての商品名を出して それぞれの数を表示する)
というコトですので、手っ取り早くVBAでの一例です。

↓の画像のようにそれぞれのシート名は「仕入れ」・「売上」となっていて、Sheet3に表示するとします。
尚、「商品」の「管理No」はシリアルナンバーのように決まっているものとします。
そして、Sheet3の1行目項目行は入力済みという前提です。

Alt+F11キー → メニュー → 挿入 → 標準モジュール → VBE画面のカーソルが点滅しているところに
↓のコードをコピー&ペースト → ...続きを読む

Qエクセルで見えないが、何かがまだは言っているのを取りのぞくにはどうすればいい。

教えてください。

エクセルのA1に「〇○錠 1.5錠」、A2に「〇○錠 3錠」としてある場合。
「〇○錠     」とだけするために、1.5錠を「””」、3錠を「""」として変換して
一応消されはするのですが、ピポットテーブルにのせると同じものと認識しないが、
これにはなにか残っているようなのですが・・。これを消して同じものとして
認識するためにはどうすればいいか。クリーン関数などというのもあるようだが、使い方が
わからない。

Aベストアンサー

>1.5錠を「””」、3錠を「""」として変換して

これ、どうやったんですか?数式?
置換機能?
◯◯錠の後ろにスペースが残っているのでは?

=CLEAN(TRIM(A1))

とかで、一度にやってしまったら如何でしょうか?

QMSの“小さな親切、余計なお世話”

Excel 2013 を使用しています。
添付図上段において、セル F1 に(赤矢印の先に)何やら表示されているアイコン、セル内に入力された文字列を隠しています。其処にマウスポインタを乗せると[挿入オプション]と表示され、当該アイコンの右端に現れたナビスコマークをクリックすると、添付図下段に示すメニューが。

この邪魔臭いアイコンを隠す(永久に表示されなくする)ための設定手順を教えてください。
ちなみに、このアイコンはどういう場合に表示されるようになっているのでしょうか?

Aベストアンサー

2016のオプション画像で申し訳ない。

「コンテンツを貼り付けるときに[貼り付けオプション]ボタンを表示する」
このチェックを外す。

QExcel関数で、文字を数字に変換させたいです。 if関数で、数字を文字で表示させることは出来ますが

Excel関数で、文字を数字に変換させたいです。
if関数で、数字を文字で表示させることは出来ますが、その逆はできるのでしょうか?
また、その列を数字の合計で出すことはできますか?

Aベストアンサー

>>例えば、非を1、定を0として表示させることはできますか?

=IF(A1="非",1,IF(A1="定",0,""))

Qエクセルで文字を入力すると罫線が消えてしまいます。対処法を教えて下さい。

エクセルで文字を入力すると罫線が消えてしまいます。対処法を教えて下さい。

Aベストアンサー

普通はそんなことにはなりません。

条件付き書式で空白の時に罫線をつける設定になってませんか?

Qエクセルである行が急に消えてしまいました。なぜなのかよくわかりません。

2010エクセルで情報管理しています。
60行ー40列くらいの情報量を適宜、変更・追記・消去・順番入れ替え(テーブル使用し、昇順にて)を行っています。またその情報を別シート(3シート)にそれぞれ必要な情報が転送されるようにしていました。
約半年ほど使用していますが、最近2回ほど、ある行が消えている現象がありました。
転送した別シートには情報が残った状態になっていました。
非表示になっているのかと思い、再表示しましたが反応がありません。
これはどういった状態なのでしょうか?
バグを起こしているのでしょうか?

Aベストアンサー

操作ミスの場合、行の高さを0(もしくは0に近い値)にしてしまい消えたように見えるのが定番です。

>再表示しましたが反応がありません。

消えた行の前後を選択してから再表示しましたか?
例えば5行目が消えているなら4行目から6行目をマウスでなぞって選択してから行の高さを変えてみてください。
再表示ではなく行の高さを変えて試すことをお勧めします。
行の高さが0.75など0ではないが小さい場合、見た目は消えていますがシステム的には表示されているので再表示で反応しません。

Qエクセルでの行挿入に関して

教えていただきたいことがあります。ご教授ください。

エクセルの行を途中で挿入した場合、通常ですとその行の空白の行が一行はいるのですが、そのシートの見出しを除いたエクセルの行すべてに同じ列は同じ列で数式が入っているものとします。挿入した空白は空白でもそれぞれの数式が入っている挿入をしたいのですが・・・。
例えば、見かけ上は空白でも数式が入っている基礎となる一行が行の2番に入っているものとします。

Aベストアンサー

行をコピーして貼り付ければいい。

Qエクセル セル内の除去について

エクセルのセル一つについて 「〇○○  ×××  」と文字○があった場合、〇○○の右の空白のスペースからすべて右側を×××を含めて除去する方法はありませんか。〇○○、×××の文字、文字数はセルごとに違うものとします。

Aベストアンサー

例えば、
A1に、

> 「〇○○  ×××  」

が入力されているとして、

B1に、
=FIND(" ",A1)
で最初に空白文字が現れる文字の場所を取得。

C1に、
=LEFT(A1,B1-1)
で最初に空白が現れるより左の文字列を取得。

とか。

Qエクセルで、西暦 1900年以前の年月日も含めて、年月日で並べ替えしたいのですが、いい方法は

エクセルで、西暦 1900年以降は年月日で並べ替えができるのですが、1900年以前の年月日も含めると、文字になっているので、並べ替えができなくなります。いい方法はありませんか。
宜しくお願い致します。

Aベストアンサー

>「セルの書式設定F]- 「表示形式」 - 「文字列」

この操作ではデータ自体は文字列にはなりません。
大体、日付データがシリアル値になると思うんですけどね。

作業列を作ってTEXT関数で日付データは文字列になるようにしておけば並べ替えできますよ。

Q【至急】勤務計算のために空白をカウントしたいです。

Aさん場合、M3のセルにC3からL4までの勤務時間を表示させたいです。
午前・午後共に「空白」の場合、8時間
午前又は午後に「半」の場合、4時間
午前又は午後に「休」の場合、カウントしない。または0時間
となるようにしたいです。

お力添えをどうかよろしくお願いします。

Aベストアンサー

こんにちは

読んでいないのか、そのままの回答でないので無視していらっしゃるのかはわかりませんが、No3様の回答がわかり易いのではないかと思います。
後出しで条件が増えたり変わったりするのは、回答者達に嫌われる質問の典型ですが、No3様の考え方ならそのまま応用することが可能ではないでしょうか??

例えば、そのままで記述するなら
=COLUMNS(C3:L4)*8-COUNTIF(C3:L4,"休")*8-COUNTIF(C3:L4,"半")*4-COUNTIF(C3:L4,"早")*3-COUNTIF(C3:L4,"遅")*2
とか。

しかしながら、この方法は単純にセルの数を数えているだけですので、想定していない入力があると結果は必ずしも正しくならない可能性があります。
例えば、同じ日の午前午後に「休」と記入してある場合 などなど…

このような可能性をチェックして解釈しなおす関数式も作成は可能ですが、それぞれの記入されているセルのカウントをSUMPRODUCTや配列数式などを利用することになると思いますので、全体の式は相当に長くなります。
時間があれば、「SUMPRODUCT、 配列数式」といったあたりを研究してみてください。

こんにちは

読んでいないのか、そのままの回答でないので無視していらっしゃるのかはわかりませんが、No3様の回答がわかり易いのではないかと思います。
後出しで条件が増えたり変わったりするのは、回答者達に嫌われる質問の典型ですが、No3様の考え方ならそのまま応用することが可能ではないでしょうか??

例えば、そのままで記述するなら
=COLUMNS(C3:L4)*8-COUNTIF(C3:L4,"休")*8-COUNTIF(C3:L4,"半")*4-COUNTIF(C3:L4,"早")*3-COUNTIF(C3:L4,"遅")*2
とか。

しかしながら、この方法は単純にセルの数を数...続きを読む


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング