Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2016)
表示形式を値に適用させる方法について
投稿日時: 19/03/28 12:22:44
投稿者: もこな2

いつもお世話になっております。
以下のようにA列のセルに数値として入力されている値を、表示形式で見た目を変えているデータがあります。
このデータの値自体を、見た目に表示されているものに置換したいのですが良い方法はないでしょうか?
 

引用:
  _A_   __A(見た目)__
 1 1     28-01-aa
 2 2     28-02-aa
 3 3     28-03-aa
 4 4     28-04-aa
 5 5  →  28-05-aa
 6 1     29-01-bb
 7 2     29-02-bb
 8 3     29-03-bb
 9 1     30-01-cc
10 2     30-02-cc

 
前提として
(1)
作業列を設けて、TEXT関数で
="28-"&TEXT(A1,"00")&"-aa"
のように数式を組んで、作業列をA列に値貼付する方法は思いついたのですが、適用している表示形式が一様ではないため、表示形式のパターンごとに数式を作る必要が出てきてしまうので今回は不採用。
 
(2)
↓コードを試してみましたが、値自体が消えちゃったのでボツ。
    Sub test01()
        With Range("A1:A10")
            .Value = .Text
        End With
    End Sub

(3)
↓これは成功だけど、引き継ぐときにマクロ分からない人だとちょっと困りそうなので次点の策
    Sub test02()
        Dim MyRNG As Range
        
        For Each MyRNG In Range("A1:A10")
            MyRNG.Value = MyRNG.Text
        Next
        
    End Sub

ということで、手間をかければできないことはないけど、マクロを使わず、該当セルにどのような表示形式が設定されているかを参照しつつ、値そのものを置換するような方法が無いかというのが質問の内容です。
よろしくお願いします。

回答
投稿日時: 19/03/28 12:43:57
投稿者: コナミ

範囲を選択→Ctrl+CC(Cを2回)→クリップボードが起動するのですべて貼り付けを押す
 
で如何でしょうか?

回答
投稿日時: 19/03/28 15:29:49
投稿者: sk

引用:
マクロを使わず、該当セルにどのような表示形式が設定されているかを参照しつつ、
値そのものを置換するような方法

引用:
="28-"&TEXT(A1,"00")&"-aa"

例えば、
 
-------------------------------------------------
 
=TEXT(A1,"""28-""00""-aa""")
 
-------------------------------------------------
 
上記のような数式セルを挿入する際において、
(TEXT 関数の第 2 引数に渡す値として)
A1 セルに設定されている書式指定文字列を取得する
ワークシート関数はないか、という意味でおっしゃっている
のであれば、そういう組み込み関数はありません。
 
-------------------------------------------------
 
=CELL("format",A1)
 
-------------------------------------------------
 
上記のような数式を記述しても、書式コード "F0" が
返されるだけです。
 
引用:
適用している表示形式が一様ではないため、表示形式のパターンごとに
数式を作る必要が出てきてしまうので今回は不採用。

引用:
引き継ぐときにマクロ分からない人だとちょっと困りそうなので次点の策

「数値しか入力されていない」上に
「 1 つの列の中でセルの表示形式が統一されていない」
という運用を是としていること自体、あまりよろしくないのでは。
 
通常は、セルによって異なる表示書式を設定するのではなく、
「 3 つのセル/列の値(と区切り記号としての "-" )を
文字列連結/整形した結果を返す数式セル」を設けるような形で
解決する問題ですよね。
 
引用:
For Each MyRNG In Range("A1:A10")
    MyRNG.Value = MyRNG.Text
Next

個々のセルに設定されている表示形式を
参照する必要がないのであれば、
 
1. Excel ワークシート上のセル範囲 A1:A10 を範囲選択して
   Ctrl + C キーをクリックする。
 
2. コピーしたテキストをメモ帳に貼り付ける。
 
3. Ctrl + A キーでテキストを全選択し、
   Ctrl + C キーをクリックする。
 
4. Excel ワークシート上のセル範囲 A1:A10 に
   コピーしたテキストを貼り付ける。
 
といった操作でも一応は可能でしょう。
(但し、このような操作手順を「引き継ぎ」の範囲に
含めるべきか否かはまた別の話です)

回答
投稿日時: 19/03/28 17:46:17
投稿者: WinArrow
投稿者のウェブサイトに移動

次のマクロは、
作業列(右隣)に、TEXT関数
作業列(右右)に、「値」
を埋め込むコードです。
 
参考になれば
 
Sub test()
Dim myCELL As Range, SHIKI As String, data
 
    With ActiveSheet
        For Each myCELL In .Range("A1:A100")
            data = Split(myCELL.Text, "-")
            SHIKI = "=TEXT(" & myCELL.Address(0, 0) & ",""" & data(0) & "-00-" & data(2) & """)"
            myCELL.Offset(, 1).Formula = SHIKI
            myCELL.Offset(, 2).Value = Application.Evaluate(SHIKI)
        Next
    End With
End Sub
 

回答
投稿日時: 19/03/28 17:59:30
投稿者: WinArrow
投稿者のウェブサイトに移動

仕組みを改造するならば、
表示形式で使っている
"28" とか "aa"
のようなデータをセルに持ち、
TEXT関数で対応したら、いかがでしょう。

投稿日時: 19/04/01 12:23:19
投稿者: もこな2

おそくなりましたが、皆さんコメントありがとうございます。
書き忘れましたが、データ自体は他の部署から貰う立場なので、ちょっと変えられないのです。
そのうえで、集計・チェックするときには、貰ったままのデータだと使いづらいので加工しているという状況です。
 

コナミ さんの引用:
範囲を選択→Ctrl+CC(Cを2回)→クリップボードが起動するのですべて貼り付けを押す
私のやり方が違ってるのかもしれないですが、Windowsクリップボード、Officeクリップボードいずれも起動しませんでした。
 
skさんのメモ帳を使うアイデア。
この発想はなかったです。
メモ帳には値が貼り付けられると思い込んでましたけど、表示形式どおりに出力されるのですね。
希望にドンピシャです。この方法で引き継ぎます。ありがとうございました。
 
WinArrowさんのアイデア。
なるほど。数式を作成するのをマクロでやっちゃうという手ですね。この発想もなかったです。
コメントありがとうござます。
(2つめのほうは、自分も考えたのですが、あんまりスマートじゃ無いので外してました。
後出しじゃんけんみたいですみません。)
 
以上解決しました。ありがとうございました。