こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

エクセル2010 ユーザー定義の書式で表示したい

 新年、明けましておめでとうございます。
 
 年明け早々にお世話になります。

 "'"yy"年"mm"月"dd"日" の書式の西暦の日付に続けて、スペースなしでシート名を表示したいので、
 お分かりになる方、ご教示よろしくお願いします。

 例えば、日付が2014年1月2日(編集当日の日付ではありません)でシート名がABCDなら、
 2014/01/02 のみの入力値に対して 
 '14年1月2日ABCD
 をD6セルに表示させたいです。

 現時点で、
 =RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-FIND("]",CELL("filename",A2)))
 を特定の書式を設定していないD8セルに入力して、D8セルにシート名の表示はできていますが、
 日付を表示させようとして、ユーザー定義の書式でロックされていないD6セルに
 'yy"年"mm"月"dd"日" を設定し、2014/01/02 を入力したところ、# が表示されて、
 頓挫しています。

 本来なら、D6セルに日付とシート名を表示させたいのですが、
 D6セル、D8セルのいずれも
 ユーザー定義の書式だと期待した表示結果が得られない状態です。

 何故、日付表示さえうまく行かないのか、この点も解説頂けたら有難いです。

 年明け早々にお手数で恐縮ですが、よろしくお願いします。

投稿日時 - 2014-01-07 02:42:56

QNo.8418179

困ってます

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.5

>日付の表示が変、とかではなく、同じブック内の複数のシートで同じ位置のセルに上記書式を設定して日付を入力したところ、
>シートによって日付らしきものが表示されたり、エラー表示されたりしているという状況です。


 エラーが表示されるとの事ですが、それは何エラー(#VALUE!,REF!,#N/A,#DIV/0!,etc.)なのでしょうか?
 もしかしますと、エラーではなくセル幅いっぱいに#が連続して表示されているのではないでしょうか?
 もしも、後者であるとしますと、それは入力した値がExcelでは日付として扱う事が出来ない様な数値(負の数や、2958467以上の数、9999年12月31日よりも後の日付、等)を入力した事が原因です。
 又、前者の様に#VALUE!,REF!,#N/A,#DIV/0!,等々のエラーが表示される場合は、関数を使って表示させる場合において、その関数では扱う事が出来ない様なデータを、扱わせようとした事が原因です。
 何れにしましても、それは書式の問題ではなく、入力した値や、関数の組み方の問題となりますので、どの様なエラーなのかという事と、入力した値がどの様な値である場合においてエラーとなるのかという事、及び、入力している関数とその参照先のセルに入力さている値に関する情報が無ければ、回答のしようが御座いませんので、これらの追加情報を御教え願います。

投稿日時 - 2014-01-08 13:00:13

ANo.4

こんばんは!
横からお邪魔します。

>日付が2014年1月2日(編集当日の日付ではありません)でシート名がABCDなら、
>2014/01/02 のみの入力値に対して 
>'14年1月2日ABCD
>をD6セルに表示させたいです

D6セルにシリアル値を入力するとマクロが実行されるようにしてみました。

画面左下の操作したいSheet見出し上で右クリック → コードの表示 → VBE画面に
↓のコードをコピー&ペースト → Excel画面に戻り、D6セルにシリアル値を入力してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
If Target.Address = "$D$6" Then
With Target
If IsDate(.Value) Then
Application.EnableEvents = False
.Value = "''" & Format(.Value, "yy年m月d日") & ActiveSheet.Name
Application.EnableEvents = True
End If
End With
End If
End Sub 'この行まで

こんな感じでよいのでしょうかね?m(_ _)m

投稿日時 - 2014-01-07 21:35:46

お礼

ご回答、有難うございます。

教えて頂けるのは、どなたからのでも有難いですから、
全然、横じゃないですよ。

マクロ、有難うございます。
もっと大きな処理の時に組み込んで試してみますね。
なので、別の機会に質問させて頂くかもしれません。
その時には、またよろしくお願いします。

有難うございました。

投稿日時 - 2014-01-08 11:54:20

ANo.3

>(1月~9月と1日~9日は、月日をそれぞれ 0 を付けずに1桁で、以降の月日を2桁)
>で表示したかったのです。

>また、質問させて頂いたことなのですが、
>"'"yy"年"mm"月"dd"日"
>とセルにユーザー定義の書式を設定して
>yyyy/mm/dd
>の形式で日付入力したところ、シートによっては期待した表示結果になったりならなかったりといった現象が起きていて、原因が分からず困っています。


 「mm」という具合に「m」を2つ重ねている場合には、月が1桁の場合にも、「0」を付けて2桁で表示されます。
 月を1桁で表示させるには、「m」を重ねずに1つだけとします。
 同様に、日の部分も「d」を2つ重ねれば2桁、1つだけなら1桁になります。
 因みに、「yyyy」と「y」を4つ重ねている場合には、年を西暦年で4桁表示、「yy」と2つ重ねている場合には、西暦年の下2桁表示となります。
 従いまして、例えば、2014年1月2日の日付を入力した際に、

'14年1月2日

という表示とする場合には、セルの書式設定の表示形式を[ユーザー定義]の

"'"yy"年"m"月"d"日"

とします。


 回答No.1の関数に関しても同様に、関数中の

"'yy年mm月dd日"

という箇所を

"'yy年m月d日"

として下さい。
 同様に、回答No.2のVBAの構文の中の

r.NumberFormatLocal = "yyyy""年""m""月""d""日" & ActiveSheet.Name & """;@"

という部分を

r.NumberFormatLocal = """'""yy""年""m""月""d""日" & ActiveSheet.Name & """;@"

に変更して下さい。

投稿日時 - 2014-01-07 18:28:49

補足

>また、質問させて頂いたことなのですが、
>"'"yy"年"mm"月"dd"日"
>とセルにユーザー定義の書式を設定して
>yyyy/mm/dd
>の形式で日付入力したところ、シートによっては期待した表示結果になったりならなかったりといった現象が起きていて、原因が分からず困っています。

説明が不正確で状態が伝わらなかったようで、申し訳ありません。
日付の表示が変、とかではなく、同じブック内の複数のシートで同じ位置のセルに上記書式を設定して日付を入力したところ、
シートによって日付らしきものが表示されたり、エラー表示されたりしているという状況です。
ハードディスクはまだ半分以上空いているので、ハードの問題ではなさそうです。
ですので、原因の見当がつかず困っているのですが。

投稿日時 - 2014-01-08 11:27:58

お礼

何度もご回答下さり、有難うございます。

マクロはまだ試していませんが、教えて頂いた関数は十分に使えています。

また、ユーザー定義の書式設定で、
mm を m 、  dd を d 
にしても、10以上の月日を入力すると正確に2桁表示されることを再発見できました。
この点も、有難かったです。

関数よりもマクロの方がリソースを抑えられるので、
容量の大きな処理の時に、教えて頂いた数式をマクロに組み込ませて頂きますね。

有難うございました。  

投稿日時 - 2014-01-08 12:02:51

ANo.2

 回答No.1です。
 書式設定による方法では御座いませんが、シート名が変わるごとに、以下のVBAマクロを使用してみて下さい。
 尚、同じシート内であれば、1度このマクロを使用したセルの書式をコピーする事によって、他のセルに対しても同じ書式設定を貼り付ける事が出来ます。
 但し、同一のシートであっても、シート名が変更された場合には、再度、マクロを使用し直す必要があります。
 又、このマクロは、マクロを使用する際に開いているシートに対してのみ有効なマクロであり、別シートのセルに対して正しい書式を設定する事は出来ません。


Sub Macro()

Dim r As Range

On Error Resume Next
Set r = Application.InputBox(Title:="セルの書式設定の表示形式を変更" _
, prompt:="「年月日」+「シート名」" & Chr(10) & Chr(10) _
& "の形式で表示させたいセルをまとめて範囲選択して下さい" & Chr(10) _
& " 【注】別シートのセルは不可" _
, Default:="=" & Selection.Address(ReferenceStyle:=xlA1), Type:=8)
If Err Then Exit Sub
On Error GoTo 0
r.NumberFormatLocal = "yyyy""年""m""月""d""日" & ActiveSheet.Name & """;@"

End Sub

投稿日時 - 2014-01-07 15:10:01

お礼

2度もご回答下さり、有難うございます。

教えて頂いた関数で、日付とシート名の表示は一応できました。
マクロの方はこれから試して、結果を報告させて頂こうと思っています。

現時点で問題点が1つあります。
月日によって、それぞれ1桁ないしは2桁
(1月~9月と1日~9日は、月日をそれぞれ 0 を付けずに1桁で、以降の月日を2桁)
で表示したかったのです。
この点は、自分でも関数を調べてみますね。

また、質問させて頂いたことなのですが、
"'"yy"年"mm"月"dd"日"
とセルにユーザー定義の書式を設定して
yyyy/mm/dd
の形式で日付入力したところ、シートによっては期待した表示結果になったりならなかったりといった現象が起きていて、原因が分からず困っています。
書式を設定せずに関数を使うことでしのげますが・・・。

この経過報告で、とりあえずのお礼とさせて頂きます。

有難うございました。

投稿日時 - 2014-01-07 16:46:34

ANo.1

 書式設定のみで、自動でシート名を表示させる方法は無いと思います。
 ですから例えば次の様にされては如何でしょうか?

 今仮に、日付がD2セルに入力されているものとしますと、「'14年1月2日ABCD」等と表示させたいセルに次の関数を入力して下さい。

=IF(ISERROR(1/DAY($D2)),"",TEXT($D2,"'yy年mm月dd日")&REPLACE(CELL("filename",$D2),1,FIND("]",CELL("filename",$D2),FIND(".xl",CELL("filename",$D2))),))

 そして、上記の関数を入力したセルをコピーして、同様の値を表示させたい、その他のセルに貼り付けて下さい。

 以上です。

投稿日時 - 2014-01-07 12:22:26

あなたにオススメの質問