エクセルのセル内の１部の参照方法

質問

エクセルの書類作成で
あるセル内の１部に文字が入力されている消費税額が自動で、入るように設定したいと考えています。

"（内消費税額￥" & H47 & "-）"

参照元のH47セルの値は

3050

で　3,050 になるように書式設定されています。

上記のように設定すると、消費税額にカンマが入らないのですが、

うまくカンマが入るようにする方法はありますか？

よろしくおねがいします。

回答No.3

kagakusuki · ベストアンサー

書式設定のみで「（内消費税額￥3,050‐）」という具合に「￥3,050」と「‐）」の間に半角スペースが入らない形式で表示させるのでしたら、セルの書式設定を[ユーザー定義]の

"（内消費税額￥"#,##0"‐）";"（内消費税額￥"-#,##0"‐）"

にされればOKです。

　只、御質問の件はおそらく、元の金額が例えば20000円だった場合に1つのセルに

￥21,600（内消費税額￥1,600‐）

と表示させるために必要なExcelのテクニックの一部分を知りたいという事なのではないかと思われます。
　この様に異なる数値を1つのセル内に同時に表示させるという場合には、セルの表示形式だけでは良い方法はありません。(金額の上限に制限がある場合には方策が無い訳でもありませんが)

　因みに、御質問文では参照元のH47セルに入力されている数値は消費税額になっている様ですが、もし消費税額ではなく税抜き金額が入力されているものとした場合には、次の様な関数となります。

=IF(ISNUMBER(H47),"￥"&TEXT(ROUND(H47*1.08,0),"#,##0")&"‐（内消費税額￥"&TEXT(ROUND(H47*0.08,0),"#,##0")&"‐）","")

　尚、消費税を含めると1円未満の端数が生じる事が良くありますが、その端数を丸める際に四捨五入を使うのか、切り捨てを使うのか、切り上げを使うのかは、事業者ごとに異なっています。
　そのため、上記の関数では取り敢えず仮の話としてROUND関数を用いて四捨五入で端数を丸めておりますが、必要に応じて質問者様の方で、ROUNDUP関数やROUNDDOWN関数を用いる様に修正して下さい。
　それから、もしH47セルに入力されている数値が税抜き額ではなく総額である場合には、次の様な関数となります。

=IF(ISNUMBER(H47),"￥"&TEXT(H47,"#,##0")&"‐（内消費税額￥"&TEXT(ROUND(H47*(1-1/1.08),0),"#,##0")&"‐）","")

お礼

回答いただきどうもありがとうございます。
関数で設定することができるのですね。
小数点も処理する方法を教えていただき参考になりました。
ありがとうございました
とても助かりました

回答No.2

noname#212313

既に優れた解決策示されていますので、別の方法もあるかもというご紹介まで。

　書式設定を使う方法があります。H47に数値があるとして、ご希望の書式でA1に表示させるとします（H47に対して使うこともできる）。要は「カンマありの数値の書式にしておいてから、ユーザー設定の書式で好みにいじる」ということです。

１．セルA1を右クリックして「セルの書式設定」を選ぶ。
２．「表示形式」で、いったん「数値」を選び、「桁区切り」のチェックボックスにチェックを入れます。
３．「負の数の表示形式」はお好みですが、「-1.234」形式を選んだとします。
４．そのまま、「ユーザー定義」を選びます。入力欄は「#,##0_ 」になっています。
５．入力欄の「#,##0_ 」の前後に「"（内消費税額￥"#,##0_ "-）"」と入れます。
６．「OK」をクリックして書式設定を終えます。
７．セルA1に「=H47」と入力します。

　この方法ですと、どうしても「（内消費税額￥3,050 -）」と「￥3,050」と「-）」の間に半角スペースが入ってしまいます。そこが難点なのですが、半角スペースを取り除くうまい方法がありません。

P.S.

　上記では書式に数値を使いましたが、通貨にして「￥」をエクセルに表示させることもできそうなのですが、数字の後ろの「-）」を表示させる方法が分かりませんでした。

お礼

回答いただきどうもありがとうございます。
書式設定で設定するという方法もあるのですね。

とても参考になりました

回答No.1

f272

="（内消費税額￥" & TEXT(H47,"#,##0") & "-）"

お礼

回答いただきどうもありがとうございます。

うまくいきました。
ありがとうございます
とても助かりました