関数で表示された数値に桁記号（,）を付けたい。

質問

初歩的な質問かもしれませんが、教えてください。

Excelで発注の記録とかをまとめたいと思っています。

メールで来たデータをExcelに貼り付けます。(1)

(2)に関数で数値を取り出し、それをコピーして(3)に貼り付けて画像のようにしたいのです。
この時に、日付が2017/07/25から07/25もしくは7/25
金額に桁記号（,）を付けたいのですが、書式設定をしても表示されません。
数値を見て打ち込めば表示されますが・・・
そもそもコピーだと書式が反映されないのでしょうか？
貼り付けた時に日付や桁記号が表示されるようにするにはどうしたらいいのでしょうか？

回答No.4

asciiz · ベストアンサー

"注文日：2017/07/25" っていうデータは、文字列です。
"価格：￥ 1564"っていうデータも、文字列です。

そして文字列関数は、文字列操作をした結果として、「文字列型」の値を返します。
だから、"注文日：2017/07/25" というデータからRIGHT関数で右側10文字を取り出した"2017/07/25" というデータも、この時点ではまだ文字列なんです。
日付型の書式設定や、数値型の書式設定は、セルに日付型の値、数値型の値が入っているときにしか有効になりません。

なので、文字列を、日付型・あるいは数値型に変換してやる必要があります。
そのための関数が、「DATEVALUE("日付文字列")」および「VALUE("数字文字列")」です。

例えばA1に "注文日：2017/07/25" が入ってるとして、別のセルに
=DATEVALUE(RIGHT(A1,10))
という式を設定し、書式設定で「m/d」とすると、「7/25」と言う結果が得られます。

A2に"価格：￥ 1564"が入ってるとして、別のセルに
=VALUE(MID(A2,FIND("￥", A2)+1,99))
という式を設定し、書式設定で「#,##0」とすると、「1,564」と言う結果が得られます。
※MID～の部分は円記号を見つけてそこから右の部分を取り出してます

----

しかしここで、ちょっとした裏技があります。

Excelは、計算式に文字列と数値が混ざった時、「数値に見える文字列は数値として扱う」「日付に見える文字列は日付として扱う」と言う自動変換機能があります。
これはどういう事かと言うと、"2017/07/25" という文字列に、+5 とすると、5日後の「2017/7/30」と言う日付型の値を得られてしまうんです。
同様に、文字列の"1564"に数値の10を足したら、「1574」と言う数値が得られます。
※プログラマからしたら気持ち悪いことこの上ないんですが

それを今回の例に使うと、日付の方は
=RIGHT(A1,10)+0
数値の方は
=MID($A$2,FIND("￥", $A$2)+1,99)+0
このようにして、日付値と数値、それぞれ得ることができます。

日付値および数値が得られたならば、セルに設定した日付形式・数値形式の書式設定で表示されるようになります。
もし、ご自分で (2) の段階まで出来ているなら、式に「+0」と追加すると、書式反映できるでしょう。

お礼

asciiz様
お礼が遅れてしまって申し訳ありません。

丁寧な解説ありがとうございます。
とてもわかりやすいです。

エクセル関数もよく使う関数くらいならなんとか使えるのですが、ちょっと複雑になってくるとお手上げです（笑）
今回のExcelの方式も関数も初めて知りました。
勉強になります。

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

回答No.9

tsubu-yuki

初歩的というかなんというか、非常にムフフな感じです。

「普通・常識」という言葉の押し売りは好まないのですが、
おそらく普通は、項目の名前と値は別のセルに持ちます。
その上でセルを参照し、（３）に成形する、という流れです。
なので、初期のデータを作る段階でそうできれば手っ取り早い、と言えます。

さて、ご質問のケース
> メールで来たデータをExcelに貼り付けます。(1)
とのことですので、そこもまた定型なのでしょう。
なので、それをまず成形します。

貼り付けた範囲を選択し、
リボンの「データ」タブから「区切り位置」を選択。
「カンマやタブ～～」を選択し、次へ。
「その他」にチェック、インプットボックスに「：」を入力、
それ以外のチェックは（念のため）すべて外し、次へ。
何もいじらずに、完了。
すると、あら不思議。
日付は日付のように、金額は金額のように、
しかも金額には￥付きで分解されるはずです。

こうしておけば（２）のワンクッションを置かずに（３）に持って行けます。
データを分割し、「＝どこのセル番地」という参照をして
（３）まで持って行った状態が添付図です。
ワークシート関数は使っていません。

参考までにどうぞ。

回答No.8

pc_net_sp

添付画像の訂正です。
下段の「10000」がそのままになっていました。

回答No.7

pc_net_sp

ほかの回答者のコメントを読んでません。 m(_ _)m
なので、ダブっているか分かりませんが、参考画像だけUPしておきます。
多分画像の方が分かり易いと思います。

注意：
私は、今だオフィス2003を愛用しています。
（2007も持ってはいるのですが、所有PC全て分のライセンスを持っているのは2003だけなのでご容赦を・・・）
バージョン2003以降でも基本設定は同じです。

画像の赤線で囲った部分を確認すれば、「,」カンマは自動に入ります。
通貨設定しても入ります。
通貨設定の「￥」マークが邪魔な場合は、「￥」を「なし」に設定してください。

10,000の表示で、上は数値設定・下は通貨設定です。

ついでですので、数値を文字列として認識させるには、「'10,000」と入力してください。
このやり方は、計算式の表示にも使えます。
例：「'=10*1000」と入力すると、文字列として「=10*1000」っと表示されます。
ただし、他セルの計算式には文字列ｘ数値になり、「#VALUE!」っとなります。

回答No.6

imogasi

基本的に、エクセルの「セル単位」で考えて、何かデータ（複合的に項目がある場合に、それをコピーして、張り付けた場合に、項目ごとに、セル単位に散らばってくれているか、１セルに貼りついているか、認識すべきだ。
エクセルはセル単位のデータでないと、使えない場合がほとんどなどは知っているだろう。
それはＷＥＢ記事をコピー貼り付けを数回すれば気づくはず。
それを認識してないということで質問者は初歩的。
エクセルー＞エクセルや、ＷＥＢの表形式をエクセルに張り付けた場合など、
はセル単位に分かれる場合が多い。わかれるかどうかは理屈があるが今は説明略。
データー区切り位置などの応用で、できる場合も多い。
セル単位に、データがなれば、そしてセルの値が（エクセル的に）「数値」ならば、セルの書式設定で金額や日付は、どうにでも表示できる。
表示形式は「NumberFormt」と言い、元データが数値でないと使えない。
日付は、一応日付シリアル値という数値だということをご存じか？
文字列の見た目を数値の場合のように整えるのは、シコシコとプログラムを
自分で作らないとならない。関数を使うなららＭＩＤ関数で切り出し、挟む文字を＆で結合するとか。これより数値化して表示形式を適用するほうが、すっきりするだろう。また文字列は計算にはつかえない。関数の引数にも使えない場合がある。
質問者は、丸写し回答をもらうよりも、根本を理解すべきだ。

回答No.5

Chiquilin

Excelのバージョンはいくつですか？
後 数値と文字列の区別はついていますか？

> (2)に関数で数値を取り出し
この数式はどんなものですか。文字列として抜き出したのなら数値の表示
形式を設定しても 変化しないはずです。

回答No.3

Nobu-W

えぇ～っと説明下手なので画像付きで説明です
ちなみに（２）の手順勝手に省きます＞＜
A10セルにはセルの書式設定⇒日付⇒3/14 を指定
合計、価格、手数料のセルはホ～ムタグの桁区切りスタイルで ，を選び
A10セル　⇒　=DATEVALUE(RIGHT(A1,10))
B10セル　⇒　=RIGHT(A2,LEN(A2)-(FIND("：",A2)+1))
A10セル　⇒　=VALUE(RIGHT(A5,LEN(A5)*10-SUM(LEN(SUBSTITUTE(A5,{0,1,2,3,4,5,6,7,8,9},)))))
A10セル　⇒　=VALUE(RIGHT(A3,LEN(A3)*10-SUM(LEN(SUBSTITUTE(A3,{0,1,2,3,4,5,6,7,8,9},)))))
としてやると、取り出せませんか？

回答No.2

aokii

(2)の関数で数値を取り出す時に使っている数式が違っているかもしれませんので、(2)の各数式の後ろに、1を掛けて（*1）数値にしてみてください。

回答No.1

f272

(2)から(3)にコピーするときに書式も一緒にコピーされているのです。(2)でちゃんとした書式設定をしておけばOKです。