小数点以下の桁数を入力データに応じて設定する方法

2003エクセルで計算表を作成しています。

途中の計算結果の小数点以下の桁数が非常に大きくなり、「縮小して全体を表示する」にしておくと見えないくらいに小さい数字になってしまいます。

表示に必要な桁数は入力データに依存するので一定ではなく、入力データに応じて変化します。
処理（四捨五入とか切り捨てなど）をせず、単に表示だけを少なくしたいのですが、関数でできるのでしょうか？　

できるのであれば教えてください。

No.6 ベストアンサー 回答者： ママチャリ 回答日時： 2016/06/24 21:26

当方、Excel2010です。

2003でどこまで出来るか分からないのですが、次のことを試してみて頂けないでしょうか。ちなみに２０１０では動作確認済みです。

まず、該当シートのWorksheet_Changeイベントプロシジャに、次のマクロを張り付けます。
入力データはA3セルとしました。A1、A2セルにはA3セルを参照した数式が入っているものとします。
このマクロは、A3セルの値が変更された時に、その小数点以下の桁数を判定して、A1、A2セルの書式を動的に変更します。シートは保護されていると云うことなので、処理の前後に解除／保護を行っています。

それから、1点、確認なのですが、「入力データ：2.00」を入力すると「2」となってしまいます。この場合、小数点以下2桁とは見做せません。これって、文字扱いで入力すると云うことでしょうか？

Private Sub Worksheet_Change(ByVal Target As Range)
Dim L As Long
If Intersect(Target, Range("A3")) Is Nothing Then Exit Sub
L = InStrRev(Range("A3").Text, ".")
If L <> 0 Then L = Len(Range("A3").Text) - L
Me.Unprotect
Range("A1").NumberFormatLocal = "0." & String(L + 1, "0") & "_ "
Range("A2").NumberFormatLocal = "0." & String(L + 2, "0") & "_ "
Me.Protect
End Sub

この回答へのお礼

ママチャリ　様

回答ありがとうございます。

私にはやや高度なテクニックなので、検証に手間取りました。

１．2003でも実施可能
　早速2010、2003で試してみましたが、どちらもうまく動きました。
　適応範囲をセルではなく範囲に設定しても動きますので、私の要望にぴったりでした。

　実際のケースでは入力データを離れた場所にあることが多いので、それも試しました。
　まずA3に入力して確認した後、A10に変更したところ、2003ではうまく切り替わりま
　したが、2010ではA3とA10の両方に反応したかと思うと、A10には反応しないなど、
　あれっと思うことがありました。

２．入力データが2.00ではなく2になる問題
　実際のケースではあまりないことですが、入力データが整数の場合があるので、この時
　は2.00として扱ってほしいのに、2として扱われるのが唯一の難点かと思います。
　これが起こりうるのは、例えば１０個の入力データのうち、その一つを代表としてA3に
　コピーして示す場合を考えます。他の９個は2桁なのに、たまたまそのサンプルセルには、
　2.00と打ち込まれても2と表示され、整数として処理されてしまいます。

３．欲を言えばこんなことができないでしょうか？
　入力データが１０個ある場合、１０個すべてが同じ桁数であることをチェックし、違う場合は
　警告を発する。その上で、そこで取得した小数点以下桁数を用いて他のセルの表示を設定する。
　こうするとさらに実用性が高まるし、２の難点を避けることも可能になります。
　小数点以下の桁数は次式で検出できます。=LEN(A1)-FIND(".",A1)

大変実用性のある技をご紹介いただきまして誠にありがとうございました。助かりました。

お礼日時：2016/06/25 15:33

No.7 回答者： ママチャリ 回答日時： 2016/06/25 16:23

No.4 ママチャリです。

「欲を言えば…」の件ですが、こんな感じですかね。
入力データのセルは、A3およびB1~B9の10ヶ所としました。その中で小数点以下の桁数が一番多いものに合わせて表示するようにしています。警告は出していません。
前回の回答を検証されているようなので、細かい説明は省略させていただきます。
ちなみに、「=LEN(A1)-FIND(".",A1)」って、小数点が無い場合、ゼロになりますか？

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRng As Range
Dim r As Range
Dim maxLen As Long
Dim L As Long
Set myRng = Range("A3,B1:B9")
If Intersect(Target, myRng) Is Nothing Then Exit Sub
For Each r In myRng
L = InStrRev(CStr(r.Value), ".")
If L <> 0 Then L = Len(CStr(r.Value)) - L
If L > maxLen Then maxLen = L
Next
Me.Unprotect
myRng.NumberFormatLocal = "0." & String(maxLen, "0") & "_ "
Range("A1").NumberFormatLocal = "0." & String(maxLen + 1, "0") & "_ "
Range("A2").NumberFormatLocal = "0." & String(maxLen + 2, "0") & "_ "
Me.Protect
End Sub

この回答へのお礼

ママチャリ 様

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

「=LEN(A1)-FIND(".",A1)」はマクロの中ではなく、セル計算の中で入力時に警告を発するように
設定して見ようと考えております。小数点のない場合は表示されませんので、その中で工夫してみます。

これで完璧になりました。

お礼日時：2016/06/25 17:49

No.5 回答者： moto_koukousei 回答日時： 2016/06/24 19:30

> 計算表は保護されているため、使用者は各種の設定を変更することができません。

> ですので、入力データに応じて自動的に変化してほしいのです。

入力データに応じて自動的に変化するようにするには、設定を変えるしかないです。
関数を使うのでも、シートのセルにその関数を組み込む、マクロを使う等しか思いつきません。

abcdefgh.ijklmnopqr　のようなもともと長い桁数表示が必要な場合で、それを、abcd efhg .ijkl mnop rq　と入力したセルとは別の複数のセルにも分割表示させることは、関数だけでも可能です。　しかし、関数をあらかじめ組み込むには、シートをいじるしかないです。

前に回答した表示変更も、該当セルにあらかじめ書式を指数に指定するだけですが、使用者にそうしたことが許されず、ベースとなるシートを配布している者も設定変更が出来なければ、手の打ちようがないです。

既に計算表は保護されていて、許容されたセルへのデータ入力しかできないのであれば、ちょっと、入力データに応じて自動的に変化するようにする方法は考えつきません。

なおセル幅(列幅)を一時的に広げてデータ内容を目視確認出来ればいいのであれば、「縮小して全体を表示する」になっていても、目的は達成出来そうです。
幅を一時的に広げて確認し、その後に戻すのもダメなんですか。

この回答へのお礼

moto_koukousei　様

重ね重ねありがとうございます。　やはりだめでしょうか？

この件を投稿する前に、ネット検索をかなりしたのですが良いサイトが見つかりませんでした。

割合と単純でかつ多くの人に必要とされる要求だと思うのですが、それに対応できる策がないのですね。

皆様には大変なご足労をおかけして申し訳なく思っております。でもまだあきらめた訳ではないのですが・・・。

お礼日時：2016/06/24 20:09

No.4 回答者： tyusonic 回答日時： 2016/06/24 18:44

こんばんは

一応、関数だけでご希望のことはできると思います。

①入力値の2と2.00を区別するためには、入力セルの表示形式を文字列にします。

次に表示セルですが、入力セルの文字数に合わせて表示すれば良いので、Left関数を使うことを考えました。

Left(計算結果 , Len(入力セル)+1)

なお、これだと小数点(.)も文字数としてカウントされるので、2と2.00のあいだでうまく行きませんでした。

小数点を含むときと含まないときで分岐します。

②If(Iserror(Find( "." , 入力セル , 1 )) , Left(計算結果,Len(入力セル)+2) , Left(計算結果,Len(入力セル)+1))

↓　入力セルをA列、計算式をA1*6/7でやってみた結果です。
※なお、これだと切り捨てられた数値が使用できなくなるので、引き続きこの数値を使用する場合には、隠しセルを利用するなど工夫してください。

この回答へのお礼

tyusonic 様

回答ありがとうございます。

種々工夫していただいて恐縮です。

実はこの計算はまだまだ延々と続きますので、四捨五入や切り捨てなどをすると誤差がたまる結果となり、好ましくありません。

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

お礼日時：2016/06/24 20:03

No.3 回答者： moto_koukousei 回答日時： 2016/06/24 17:19

例えば、1234.567890123のような14個の文字列になるときは、全体縮小表示だと、セル幅によっては、小さくなりすぎにはなると思います。

四捨五入や切り捨てなどの丸め処理をせずに、｢表示だけを少なくする｣とはどのようなイメージでしょうか。

エクセルの数値の場合、｢1234.567890123｣は、｢1234.56789｣と表示するのが標準です。
小数点以下の表示を二桁に指定すれば、｢1234.57｣と表示されます。
小数点以下の表示を11桁に指定すれば、｢1234.56789012300｣と表示されます。
表示形式を指数指定し、小数点以下の表示を2桁に指定すれば、｢1.23E+03｣と表示されます。
これらは"表示"なので、セルの中にある計算結果や直接入力した数値の値を変更したことにはなりません。
エクセルの該当セルにあらかじめ表示形式の指定をしてから、入力を依頼した場合には、そのセルの表示だけは短くも出来ます。
http://www.slownet.ne.jp/sns/area/pc/reading/pc_ …

この回答へのお礼

moto_koukousei　様

回答ありがとうございます。

No.2の返礼欄に書きましたように、入力データの桁数は様々です。
No.1の返礼欄に書きましたように、計算表は保護されているため、使用者は各種の設定を変更することができません。

ですので、入力データに応じて自動的に変化してほしいのです。

よろしくお願いします。

お礼日時：2016/06/24 17:46

No.2 回答者： chonami 回答日時： 2016/06/24 16:27

2003ですから条件付き書式は使えませんね…

1つのセルでデータによって表示を変えたいんですよね？
入力セルと表示セルを分けるとかくらいしかできないのではないでしょうか。
表示セルにはTEXT関数でデータの種類ごとに表示形式を分岐して入れるようにして。
後はVBAでしょうかね。

例えばどんな場合があるのでしょうか？
データの内容によってはデータは整数でも1.00などのように必ず小数第２位まで表示しなければいけないとかですか？

この回答へのお礼

chonami 様

回答ありがとうございます。

例を書きます。

例1
入力データ：2.32
計算結果セルA1：3.24368978 ⇒ 望ましい表示は 3.243　 （入力データより1桁多い）
計算結果セルA2：1.56432534 ⇒ 望ましい表示は 1.5643 （入力データより2桁多い）

例2
入力データ：2
計算結果セルA1：2.43678452 ⇒ 望ましい表示は 2.4 （入力データより1桁多い）
計算結果セルA2：1.24389463 ⇒ 望ましい表示は 1.24 （入力データより2桁多い）

例3
入力データ：2.00
計算結果セルA1：2.43678452 ⇒ 望ましい表示は 2.436 （入力データより1桁多い）
計算結果セルA2：1.24389463 ⇒ 望ましい表示は 1.2438 （入力データより2桁多い）

こういう表示が関数を使って可能であれば、教えてください。

よろしくお願いします。

お礼日時：2016/06/24 17:39

No.1 回答者： moon-and-star 回答日時： 2016/06/24 12:57

セルの表示設定で数値に設定すれば、表示桁数が設定できます。

この回答へのお礼

moon-and-star　様

早速のお返事ありがとうございます。

説明不足で申し訳ありません。本計算表は関係者に配布するためシートは保護されており、使用者は入力欄以外は変更することができません。

ですので、入力値に応じて、計算表の小数点以下桁数が自動的に変更されるようにしておきたいのです。

よろしくお願いします。

お礼日時：2016/06/24 14:16