calendar

S M T W T F S
      1
2345678
9101112131415
16171819202122
23242526272829
3031     
<< August 2020 >>

categories

archives

楽天

amazon

Excel:桁数を操作する

0
    JUGEMテーマ:コンピュータ


    Excelアイコン

     お久しぶりです。嬉しい

     最近更新がご無沙汰になってしまい、申しわけありません。

     本日は、Excel2003であることをしようとしてできず、インターネットで調べ物をしていたところ、非常にためになった情報を1つお知らせしておきたいと思います。

     内容はやや詳しい人向けなのが申し訳ないところです。ショック

     今回私がExcel2003上で悩んだのは、以下の手順です。
    ・縦棒グラフ上の
    ・データラベルの値で
    ・数値を表示させる際に
    ・XXX万円と表示させたい

     なぜ上記のようにしたいと思ったかといいますと、それは以下のとおり。
    △グラフ化の対象が1つの合計数値のため、数値ボックスが小さくなってしまう
     →数値ボックスの形を横長にしたいのにサイズ変更は任意にできない
    △グラフの参照場所は合計値のため、できれば参照セルはそのままの合計値のセルにしたい。
     →値をコピーした別セルを1000で割り INT 関数で小数点以下を切り捨て、そのセルを参照してグラフにする手順は避けたい。

     という具合です。

     文字だけでは分かりにくいので、画像を見てください。

    ◆元になるデータ
    Excelサンプルデータ

    ◆グラフ
    Excelグラフ縮小図
     <拡大図

     こういった月別、期別推移のなどはよくグラフにすると思いますが、合計値もグラフに表したい場合が難しいところです。月別、期別推移に合計のグラフを入れると、当然ですが最大値が膨大になってしまい、他のグラフとの比較に意味がなくなってしまいます。
     とはいえ、単体と連結の合計も比較をしたい場合もありますので、合計値を別にしてグラフ化します。

     実際に2つのグラフを並べてみると、やや問題が起こります。

     図を見てもらうと分かりますが、通常月データのように数値が5期→6期→7期などと横に続いていくグラフの場合、グラフ数値の [ 246,933円 ] という表示は普通に読み取れます。
     しかし、合計値のように横に続いていかないグラフの場合は、なぜか、[ 752, ] で折り返してしまい、[ 770 ] [ 円 ] と3行になり、数値のボックスを選択してもフォントサイズや色などを変更できても、左右に引っ張って読みやすくすることができません。
    ※読めないくらいフォントサイズを小さくすれば1列に表示されますが・・・。

     「合計値の概算を一目で把握したい」と思っても、2行にまたがる数値は、ぱっと見でいくらなのかが分かりにくい状態になってしまいます。
     数値ボックスの幅が広げられないのなら、75万円という風に表示がしたい、と考えましたが、意外とやっかいでした。

     それでは、これを解決していきましょう。

     [ 難易度:中 ] 嬉しい

    Excelグラフの変化


    ■桁数を制限する関数はグラフ上では使えない


     まず最初に考えたのは、「このままでは数字が読みにくいので、XXX万と表示させればいいのではないか?」という点でした。

     そうして、最初は関数で何とかできないかと考えました。
     しかし、すぐにそれが無理なことが分かりました。
     なぜなら、ここで表示を変更したいのはグラフ上の”データラベル”だからです。
     参照元のセルならまだしも、グラフ上の”データラベル”には関数は使えません。
     また参照元の数値は、先ほどのリストのようになっているため、この合計部分だけを1000で割り(〜万円にするため)そのセルに [ INT 関数 ] を使って端数を切り捨てることはできません(合計値の下1桁まで表示する必要があるから)。
    ※ex: =int(sum(D3:F3)/1000)
     別セルにINT 関数で桁数を減らした数値をコピーし、文字色 [ 白 ] にして見えなくして、そこをグラフの参照元にするというアクロバティックな方法もありますが、他の人がこのファイルを見る際に混乱させてしまいますし、あまり美しい方法ではありません。

     「書式設定」→「表示形式」でなんとかできないかと、インターネットを探したところ、非常に便利な方法がありました。
     文字で書くと分かりにくいのですが、順に説明したいと思います。


    ■表示形式で使用できる特殊文字を駆使する


     いろいろ調べましたが、一番分かりやすかったのが「教えて!goo」でした。
     希望の質問自体を見つけるための検索キーワードは「表示形式」「書式設定」「桁」「万円」の4ワード。

    エクセルの書式について
    2,000,000』を『200万』と表示させる

     こちらを読んでいただいても、このブログの最初の問題は解決できると思いますが、分かりやすく書いていきます。

     まず、上の「教えて!goo」を読んで分かったことをまとめますと以下のようになります。

    ・ 数値: 12345678 → [ #,"千円" ] =12346千円 (下3桁四捨五入)
    ・ 数値: 12345678 → [ #,,"百万円" ] =12百万円 (下6桁四捨五入)

     このように、[ , (カンマ) ] を使うことで、千円単位、百万円単位での表示制御ができるようです。さらに、

    ・ 数値: 12345678 → [ #"."#,"万円" ] =1234.6万円 (これは量販店っぽい感じ)

     しかし、どれも最初の希望を満たしていません。
     いわゆるアメリカなどの海外だと 1K=1000$ のような、1000を単位で区切る概念は分かりやすいんでしょうが(?)、日本のように1万円単位で考える場合が多いと、○○○千円なんて言われてもピンときません。※日本でも1Kと表記している人もいますが、あまりなじみはないですよね。
     だったら○○万円と表示してほしい。

     そうして試行錯誤するうちに、とても良い方法が見つかりました。
     実はこうすればいいのです。
    ・ 数値: 12345678 → [ #"万"####"円" ] =1234万5678円 (完成)

     これが、上記のグラフの数値です。
    ※1234万円(以下表記なし)という表示の仕方はできませんでした。後述。

     と、これで完成と思いきや、いくつかフォロー技を理解しておく必要があります。
     それは、以下の2点です。
    ・桁がもうひとつ増えて1億を超えた場合はどうするの?
    ・桁がもうひとつ増えて”万”の文字と数字がずれちゃったらどうするの?

     1億を超えた場合ですが、当然そのときは [ #"億"####"万"####"円" ] ですね。
     その他にも表記がフォントサイズによってずれてしまったときは・・・、そう、その時は、フォントサイズを直接指定してきれいに見えるように調整すればいいのです。
     グラフと言うのは、印刷時、もしくは、表示時にきれいに見えればいいわけですから、臨機応変に調整してあげてください。※少し強引な結論ですが、それしか思いつきません。嬉しい

     さて、画像がないと分かりにくいですが、あと一桁増えると”万”が一段下がります。それ自体は(表記上)問題ないのですが、数値の表示フォントサイズによっては、「123」「万」「456」「7円」と言ったように4つ目のブロックに数値がちょろっとくっついてしまう事があります。

     そういった場合に便利なのが、裏技として登場する「特殊文字(改行)」です。
     特に裏技と言うほどではないのですが、この「特殊文字(改行)」を表示形式に入力することができるのです。

     ここで裏技と言っているのは、Microsoftのサポートオンラインには載っていない手順だからです。

    セルの表示形式で、ユーザー定義に使用できる書式記号について

     改行を入れる手順は簡単です。
     例えば今回の場合は、以下のように入力します。
    1.表示形式の種類(T):のところに [ #"万" ] と入力する
    2.CTRL+J をキーボードから入力する。(CTRLキーを押しながら J を押す)
    3.いきなり種類(T):の欄が空欄になるが、これは改行されたためで問題ない。
    4.続けて空欄に見えるところに [ ####"円" ] と入力してOKを押す。

    Excel表示形式

     上記の手順では、改行(CTRL+J)を入力する手順が分かりやすいように”万”の次に入れていますが、4.のところで、[ #### ] の直後に再び改行記号を2.のように入れてあげれば、「円」の手前で改行されます。改行はいくつでも入れられます。
     この改行入力を使うと様々な表記に応用できるため、知っておきたい TIPS だと思いますがいかがでしょうか。
    ※ちなみに普段入力で慣れているあろう ALT+ENTER では、上記の表示形式の種類(T):に改行は入りません。


    ■やっぱり欲しい表示形式の文字制御の追加

     これで一応解決しましたが、やっぱり下の桁数を切り捨てる、簡単な表記制御方法は欲しいところです。
     たしかに日本でも桁数の区切りで普通に使うカンマ [ , ] ではありますが、万円、億円と文字を付け加える事もあります。
     もちろん前述の方法([ #"億"####"万"####"円" ])を使えば可能ですが、万円以下を切り捨てて表示しないようにしたい、という欲望は達成されていません。
     切捨てを行う ROUND() 関数もありますが、これは予め桁数が分かっていないとできませんし、繰り返しになりますが「データラベル」には関数は使えません。
    ※実際は一部ですが、大なり記号・小なり記号という"関数"が使えますけど・・・。

     分かりやすく言うと、下何桁を自由に省く、という方法が欲しいわけです。
     しかも関数ではなくて、表示形式で使える方法で、です。
     先の棒グラフのように、合計のグラフでは概算をちら見で確認したい、というときに「万円」以下を表示しないほうが、スマートな気がするからです(もちろん、その辺りは好き好きではありますが)。

     カンマ以外で、桁数切捨て(四捨五入でもいい)する記号(機能)を追加して欲しいなぁ、と今回一連の調べ物をしていて思った次第です。


     今回は少しExcelの詳しい内容に突っ込んで書いてみましたが、いかがでしたでしょうか?

     私はこれで、毎期バージョンアップしている当社のExcelデータのグラフがより見やすいものになり、大満足しています。※ちなみに今回使用したサンプルのデータは、インターネット上のホームページから手に入れた上場企業の公開されたデータの一部を加工利用していますので、当社、及び、当社に関連するいかなる企業上のデータとも関係ありません。

     このブログネタは自分と同じような悩みを抱えている人の解決の手助けになればと思いまとめたものです。
     前述の「教えて!goo」で今回の私のような疑問を質問した質問者の方、その質問に親切にお答えくださった皆様のアドバイスでこのブログが書き上げることができました。その質問者、及び、回答者の皆様方に、この場を借りて感謝を述べたいと思います。

     ありがとうございます。楽しい

    パソコン

    コメント
    コメントする








       
    この記事のトラックバックURL
    トラックバック