エクセル/数式で表示された文字の一部のサイズ変更

質問

Sheet1のA1セルの文字列（顧客名）に　御中　をつけて別のシートに表示したいのです。
=Sheet1!A1&" 御 中"
で表示できるのは存じておりますが、文字のサイズを顧客名と御中で変えたいのです。御中を若干ちいさめにしたいのです。
文字でセルに直接　ABCD株式会社 御 中　と打ち込んだものならフォントサイズは文字ごとに変えられますが、数式で表示された文字の一部のフォントサイズを変える方法が判りません。たぶん書式をいじる関数はないのだと思います。
御中を別のセルにできればよいのですが、顧客名の長さが不明なので数式で　&"　御中　としています。
良い方法があったらご教示ください。

回答No.4

masnoske · ベストアンサー

関数ではできません．
VBAのCharactersでも =Sheet1!A1&" 御 中" のような数式の結果に対してはできないようです（Excel2013ではできません）．

なので，顧客名を入力すると自動的に" 御 中" を付加して文字サイズを調整するマクロを作ってみました．
仮に顧客名を入力するセルを$A$1としています．

シートモジュールに以下のコードをコピペしてご利用ください．

Private Const ONCHU = " 御 中"
Private Const KOKYAKU_ADDRESS = "$A$1"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> KOKYAKU_ADDRESS Then Exit Sub

Dim lngLen As Long

Application.EnableEvents = False
With Target
.Value = .Value & ONCHU
lngLen = Len(.Value)
.Characters(1, lngLen - Len(ONCHU)).Font.Size = 14
.Characters(lngLen - Len(ONCHU) + 1, Len(ONCHU)).Font.Size = 10
End With
Application.EnableEvents = True
End Sub

お礼

ありがとうございます。
やはりVBAでいくしかないようですね。すばらしいコードをありがとうございました。

回答No.7

HohoPapa

No6 HohoPapa です。
="御中" & REPT("　",28-ROUNDUP(LENB(A1)*8/11,0))
とすれば顧客名に半角文字が含まれてもダイジョブです。

お礼

ありがとうございます。

回答No.6

HohoPapa

VBAを使うのが順当な解と思います。
が、
今回は興味が湧き、VBAを使わない解を考えてみました。
顧客名、御中に使うフォント、フォントサイズが提示されておらず
セル幅などレイアウトが未詳なので以下を前提にしています。

・各列幅はデフォルトのまま
・顧客名のフォントサイズは１８
・御中のフォントサイズは１１

添付画像のように

Ａ列のフォント：ＭＳ 明朝　サイズ１８
かつ、左詰め
Ｆ列のフォント：ＭＳ ゴシック　サイズ１１
（またはＭＳ 明朝）
かつ、右詰め

F1="御中" & REPT("　",28-ROUNDUP(LEN(A1)*16/11,0))

なお、
顧客名に半角文字が含まれないことが前提です。
更に、ＭＳ Ｐ明朝などプロポーショナルフォントには耐えられません。

フォント、フォントサイズと文字幅の関係は複雑ですので
厳密な制御が求められる場合は、
顧客名の文字数と
「28-ROUNDUP(LEN(A1)*16/11,0)」この部分で求めている文字数
（つまり、"御中"末尾に埋める全角スペースの数）
の関係をテーブルにまとめ、
VLOOKUPで求める必要がありましょう。

お礼

ありがとうございます。
でもやはり関数では無理がありますね。VBAで行くしかないようです。

回答No.5

kkkkkm

面倒ですが
セルに=Sheet1!A1として、テキストボックスをセルに重ねて配置して(塗りつぶし無しの線無し)で御中と記載して右寄せでセットしておきます。
顧客名の長さに合わせてテキストボックスのプロパティでサイズの幅をスピンボタンで調整して合わせます。
合わせ忘れたら悲惨ですが…。

お礼

ありがとうございます。

補足

ありがとうございます。

回答No.3

msMike

ちょっと試みた限りでは、テキストなら可能だけど、実体がが数式の儘では無理みたいですね。

お礼

ありがとうございます。
やはりVBAでいくしかないようですね。

回答No.2

imogasi

通常のエクセル関数で書式を左右することはできません。基本原則。
ーー
VBAはやっていないかもしれず、参考程度に。
しかし、VBAならできます。Charactersというプロパティがあるから、文字列の一部を捉えられるからです。
標準モジュールに
Sub test01()
For i = 1 To 3
x = Cells(i, "G")　'G列にデータがあるとして、
MsgBox x
p = InStr(x, "御中")
MsgBox p
Range("G" & i).Characters(Start:=1, Length:=(p - 1)).Font.Size = 23
Next i
End Sub
データはA列に
田中株式会社
田中伸株式会社
田中建設工業伸株式会社
とあるとして
関数で　=A1&"御中"
で御中をくっつける。
これをG列に「値のみ複写」しておく。
上記のVBAを実行。
御中以外の部分が、字が大きくなる。

お礼

ありがとうございます。やはりVBAでいくしかないですか。

回答No.1

masnoske

> 御中を別のセルにできればよいのですが、顧客名の長さが不明なので数式で　> &"　御中　としています。

顧客名の長さが不明なのは当たり前の話で，普通は別のセルにします．
顧客名は右詰，御中は左詰で御中にはインデントをつけるか，御中の前にスペースを入れておく．．．何が問題なのかもわかりません．

お礼

早速ありがとうございます。
顧客名は右詰？どのセルにでしょう？
通常は、横書きの場合、宛名は左側に来ますよね。たとえばC列に右詰で持ってくると、A、B、Cの3列ありますからたいていは収まりますが、収まらない場合は頭が切れてしまいます。そして怖いのは頭の切れたことに気づかないことです。もし自分の会社名の頭の切れた見積書をもらった顧客はどう思いますか？
ABC3列を結合して、1つのセルにし、縮小して全体を表示にしたら、差出人名のフォントより小さくなる可能性があります。これも問題です。
では、一番長い名前を想定してE列あたりに持ってくる？
すると通常の長さの宛名は、本来来てほしい位置に表示されなくなります。
これが別のセルにできない理由です。