Ｅｘｃｅｌでの置換

質問

Excelで家計簿を作ってますが、来年度の出費予定を本年の内容を参考に作ってます。つまり、本年のワークシートをコピーして最低限の削除・挿入で作ってます。問題は、その際日付の「平成12年ｘ月ｘ日」→「平成13年ｘ月ｘ日」を一括して変換できないかと思って「検索・置換」でTryしてみましたが、一部上手く置換してくれましたが、ほとんどは置換してくれませんでした。原因として、思
い当たるのは、日付部分を所々コピー＆ペーストを使用している事かと思います。
とりあえず、該当するワークシート１枚分の置換が出来ればよいのですが・・・？
ご教示の程宜しくお願いします。

回答No.3

10n · ベストアンサー

私も原因は「全角・半角」か「表示形式」のどちらかだと思います。

nanalyさんの解決策を施してもまだ変換されずにいるセルがありましたら、そのセルをひとつ選択してみてください。仮にそのセルが「平成12年5月5日」と表示していたとして、もし画面上部の数式バーに「2001.5.5」という値が表示されている場合は、そのセルに入力されている値は文字列ではなく数値です。数値を入力されているセルが、表示形式によって「平成12年5月5日」と表示しているということです。

Excelの検索はあくまでも入力されている値に対して行われるものですから「平成12年」という文字列が入力されていないセルを置換することはできません。

ではどうすれば「平成12年5月5日」は「平成13年5月5日」になるのでしょか。
つまり「平成12年5月5日」に相当する数値をどうすれば「平成13年5月5日」相当の数値に変換できるかということです。

365を足せばOKです。

<1>「平成12年5月5日」と入力されているセルを任意のセルにコピーしてください。(コピー元を仮にB4、コピー先をC4とします)
<2>C4に「=B4+365」という数式を入力してください。
以上の作業によりC4には「平成13年5月5日」と表示されるはずです。

MiJunさんは家計簿を加工されているとのことですが、きっと「日付」という一つの列に上から下まで並んでいる日付を一括変換したいということだと思います。先ほどの作業を応用する場合は
<1>日付が入力されている列をコピーしてください。(この列を仮にB列とします)
<2>C列を選択し「コピーしたセルの挿入」を行ってください。
<3>C1に「=B1+365」という数式を入力してください。
<4>C1をコピーしてください。
<5>C列を選択し「貼り付け」てください。
以上の作業によりB列に表示されている日付の一年後の日付がC列に表示されているはずです。
しかしこのままの状態でB列を削除するとC列全体が「#REF!」となってしまいます。したがって
<6>C列をコピーしてください。
<7>そのままC列に「形式を選択して貼り付け→値」を行ってください。

以上の作業により、お求めの結果は得られるはずかと思いますがいかがでしょうか。

補足

大変丁寧な説明ありがとうございます。
ほぼ解決しました。
ただ、行によっては「明治33年」との表示が出てきました。この原因は「形式を選択して貼り付け→値」にチェックを入れなければなる場合と、入れてもなる場合があります。特に、後者の場合の原因・解決策をご教示下さい。
（もちろん、コピー＆ペーストで一応解決はしてますが・・・）

回答No.8

10n

そうですよね。列全体に「貼り付け」る必要はないですね。「必要範囲の先頭から末尾を選択」→「貼り付け」でしたね。

そうそう「0」が「明治33年」か「明治37年」かは私も気になっていたところです。

ウィンドウズ版では「ツール→オプション」で現れるダイアログの
マッキントッシュ版では「ツール→初期設定」で現れるダイアログの
「計算方法」の「ブックオプション」の欄の「1904年から計算する」を

チェックすると「0」が「明治37年」(1904年)、
チェックしないと「0」が「明治33年」(1900年)となります。

なぜたった4年のシフトのためにこのチェックボックスは用意されたのでしょう。
ご存じのかたがいらっしゃいましたら
教えて!

あと日付に関連して本編から外れた駄談をしてしまって恐縮ですが
2001年になってから日付入力のキーストロークが増えてしまいましたね。

2000年の頃は
入力年の日付は「5/5」→「2000/5/5」。一年前は「99/5/5」→「1999/5/5」

2001年になってから
入力年の日付は「5/5」→「2001/5/5」。一年前は「2000/5/5」→「2000/5/5」となってしまいました。

「00/5/5」→「2000/5/5」となるようなパッチはないのですかねぇ(単なるボヤキです)。

お礼

お礼が大変遅くなり申し訳ありません。
何度も回答して頂いてありがとうございました。

今後ともよろしくお願いします。

回答No.7

10n

表示形式の設定により日付として表示されている数値を日付の文字列に変換する場合は「TEXT」関数を用いると便利です。

仮にB列に日付が上から下まで入力されていたとします。
<1>C列を選択します。
<2>「列の挿入」を行います。
<3>「C1」に「=TEXT(B1,"ggge年m月d日")」を入力します。
<4>C列全体を選択する。
<5>選択範囲(C列全体)に「貼り付け」を行う。
<6>C列全体を選択し「コピー」する。
<7>そのまま選択範囲(C列全体)に「形式を選択して貼り付け→値」を行う。
以上の作業により日付が文字列として入力されます。

補足

10nさん、いろいろと勉強になり、ありがとうございます。
前回の補足同様に、
「<5>選択範囲(C列全体)に「貼り付け」を行う。」では、
B列の選択範囲を「貼り付け」した方が良いのでしょうか？

いずれにしても、お恥ずかしい限りですが「文字列」・「数式」等の基本的な事のおさえが甘かったようです。

回答No.6

noname#420

日付の表示形式ですと今後の置換も大変ですので、
一度現状態を文字列に置き換えたほうが良いと思います。
そのVBAを下記に示します。

Sub Macro()
Dim c As Range
'現選択中シート内のセル指定範囲において
For Each c In ActiveSheet.Range("A1:A368")
'セル書式設定が日付型「平成　年　月　日」ならば、
If c.NumberFormatLocal = "ggge""年""m""月""d""日""" Then
'A_単なる文字列へ変換 '←「ココ」
c.Value = Format(c.Value, "ggge""年""m""月""d""日""")'←「ココ」
End If
Next
End Sub

Range("A1:A368")ではA1からA368のセル範囲を指定して日付を文字列へ変換しています。ご自身でここは書き換えて使用してください。
現状を文字列に置換後、標準機能の文字列置換で平成13年にすればよいと思いますが、その際、平成12年2月29日にご注意ください。

ちなみに1年加算して文字列へ置き換えるのは上記 [←「ココ」]行を下記Bの行と置き換えます。(365を足しても良いですが、その場合閏年の判定等わずらわしくなります。）
'B　１年加算して文字列へ
c.Value = Format(DateAdd("yyyy", 1, c.Value), "ggge""年""m""月""d""日""")

また、1年加算して日付のままにしておくのは下記Cの行と置き換えます。
'C　１年加算して日付へ
c.Value = DateAdd("yyyy", 1, c.Value)

マクロの実行方法等はヘルプ等をみて勉強なさって下さい

お礼

お礼が遅くなり申し訳ありません。
VBAの丁寧な解説有難うございました。
もう少し勉強してからあらためて質問致しますので、今後ともよろしくお願いします。

回答No.5

10n

---1つめのケース---
「（連続した）複数行の修正が必要なので、例えばＣ１をＣ２からＣ５に各々コピーし、Ｃ１からＣ５を一括コピーしてＣ６にペーストする。これを繰り返していく・・・」

この作業の必然性が分かりません。
<1>C1を選択し「コピー」を行う。
<2>C列全体を選択する。
<3>選択範囲(C列全体)に「貼り付け」を行う。
という方法ではまずいのでしょうか。

まぁそれはさておき
このケースにおいてはC列のセルにはすべて「=B?+365」という数式が入力されているはずです。これらのセルが「明治」を表示してしまうケースは1つだけ考えられます。

それは、参照元の「B?」のセルに何も値が入力されていない。つまり空欄のセルであった場合です。

このケースにおいて現れた「明治」セル(仮にC21)には「=B21+365」という数式が格納されているはずです。その「B21」のセルに何も値が入力されていない、つまり空欄のセルであった場合は「=B21+365」という数式が入力されているセル「C21」は「明治」の日付を表示します。

ここでExcelの「日付」について説明します。
例えば「平成12年5月5日」と表示されているセルにはどのような値が格納されているのでしょうか。じつはこのセルには「35189」という数値が格納されています。

ではこのセルに「0」を入力するとどうなるでしょうか。「明治37年1月1日」と表示されます。
つまり「0」を「明治37年1月1日」として「1」が「明治37年1月2日」、「366」が「明治38年1月1日」、「3132」が「明治45年7月29日」、「3133」が「大正1年7月30日」となっています。

つまり「B21」が「0」もしくは空欄であった場合は「=B21+365」は実質的には「365」となるため「明治37年12月31日」の日付を表示してしまうわけです。
==================================================
---2つめのケース---
このケースにおいても、「明治」セルにはどのような数値が格納されたのかという視点から考えてみます。
この「形式を選択して貼り付け」により「明治」セルが現れてしまうケース、つまりセルに「3132」以下の数値が格納されるケースは1つだけ考えられます。

それは、「形式を選択して貼り付け」るときに「演算」の欄で「しない」以外の項目にチェックを入れてしまった場合です。

「形式を選択して貼り付け」ダイアログの中ほどには「演算」という欄があります。
この欄の「減算」という項目をチェックした場合、「貼り付け」前にそのセルに格納されていた値を「コピー」されている値で減算した値が「貼り付け」られます。
同様に「演算」の欄に「除算」という項目があります。これも「貼り付け」前の値を「コピー」されている値で割った値を「貼り付け」ます。

つまり「平成12年5月5日」と表示している「B4」を参照する「=B4+365」という数式が「C4」に入力されていた場合、「C4」は「平成13年5月5日」と表示します。このとき「C4」の値は実質的には「35554」です。
この「C4」をコピーして、「C4」に「形式を選択して貼り付け→値・減算」を行った場合は
(C4の値)-(C4の値)ということで結果、値は「0」となり「明治37年1月1日」となるはずです。
同様に「形式を選択して貼り付け→値・除算」を行った場合は
(C4の値)/(C4の値)ということで結果、値は「1」となり「明治37年1月2日」となるはずです。
==================================================
---まとめ---
「明治」セルが現れてしまったときには
<1>そのセルの参照元が「0」もしくは空欄ではないか。
<2>「形式を選択して貼り付け」るときに「演算」の欄が「しない」であったかどうか。
を確認すればよいと思いますがいかがでしょうか。

補足

10nさん、何度もありがとうございます。
「この作業の必然性が分かりません。
<1>C1を選択し「コピー」を行う。
<2>C列全体を選択する。
<3>選択範囲(C列全体)に「貼り付け」を行う。
という方法ではまずいのでしょうか。」

まず大変申し訳ないのですが、当方は「Lotus1-2,3,2000」と「Excel2000」の両方をインストールしてます。それで、この質問は本来こちらのミスで「Lotus1-2-3
」とすべきところを「Excel」としてしました。失礼しました。自信はありませんが、恐らく「Lotus」では上記のような方法でないと・・・（？）
----------------------------
「つまり「0」を「明治37年1月1日」」に関しては説明頂き、良く理解できました。

その後、「Excel2000」で確認しました。
２点確認したい事があります。
１．前回の回答で、「＜5＞C列を選択し、貼り付け・・・。」に関して、
C列全体を選択すると、余分な部分（範囲外）もコピーされ
てしましますね？
→これを回避するにはB列の必要範囲をコピーする・・・？
２．細かい点ですが。「明治33年」ではないでしょうか（1900年は）？

宜しくお願いします。

回答No.4

10n

「明治」の日付を表示するセルが出現してしまうことの原因としては
「=??-365」の参照元のセルが空欄であることが考えられます。

「明治」セルは作業のどの段階で現れるのでしょうか。

補足

２つのケースがあるようです？？
１．<3>C1に「=B1+365」という数式を入力してください。
<4>C1をコピーしてください。
-------------------------
（連続した）複数行の修正が必要なので、例えばＣ１をＣ２からＣ５に各々コピーし、Ｃ１からＣ５を一括コピーしてＣ６にペーストする。これを繰り返していく最中にある行で「明治」となる。
============================
２．
<7>そのままC列に「形式を選択して貼り付け→値」を行ってください。
-----------------------------
この貼り付けを行ったある行で「明治」となる。

以上の２つのケースがあるようです。

ご教示の程宜しくお願いします。

回答No.2

noname#420

原因として考えられるのは、２つあると思います。
１つめは、先に回答された方のもの（全角・半角混在）と
２つめはセルの書式設定が[日付]の表示形式となっているものです。
１つめは先に回答された方のいうとおり解決できるとおもいますが、２つめのものについては１セルづつ修正していくかVBAを使用して一括修正するしかないと思います。

補足

ありがとうございます。
原因は２のようです。
「VBA」に関しては以前より興味を持ってましたが、なかなか勉強する機会がありませんでした。少し、この件に関して説明して頂ければ幸いです。

回答No.1

nanaly

「平成12年ｘ月ｘ日」の文字列に半角や全角が混在しているのではないでしょうか？

「置換」ダイアログボックスで「全角と半角を区別する」がチェックされていたならば、そのチェックを外してから「すべて置換」ボタンをクリックすれば一括置換できると思います。

お礼

ありがとうございます。
「全角・半角」は質問前に気がつきまして、一部は上手くいきましたがこれだけでは解決しませんでした。