3/15/2015

Excel。ROUND関数を使わなくても表示形式のユーザー定義でどうにかなる


Excel。ROUND関数を使わなくても
表示形式のユーザー定義でどうにかなる

ROUND関数&ユーザー定義


縦横のクロス集計などで、各月の売上金額が一の位まできちんと出てなくてもいい場合、
例えば、12,345を12,000と表示してもいい場合に、
だいたいROUND関数を使っていると思いますが、ROUND関数ですと、
元のデータを用意しておいてそのデータから、
ROUND関数を使って別のセルにデータを作ってクロス集計をする方法が多いと思います。

実は表示形式のユーザー定義を使うと、桁区切り(,)での四捨五入になりますが、
同じようにすることが出来るので、今回は、それを紹介したいと思います。

A2:E7までが通常の桁区切りをした数値でこれが一の位まで表示されていますが、
G2:K7は、A2:E7の数値を基にROUND関数を使って、百の位を四捨五入しています。

そして、A11:E16。これは見た目は、ROUND関数を使っているようにみえますが、
表示形式のユーザー定義で表示しております。この方法の利点は、
表示形式を変えているので元データを用意する必要もなく、
正しい数値もセルに入力されたままです。

では、G2:K7のROUND関数からやってみましょう。
下記のデータがあります。

H3をクリックして、ROUND関数のダイアログボックスを表示しましょう。

数値には、B3を入力します。
桁数ですが、何処の桁から表示したいのかということになるので千の位から表示したいので、
前三桁は表示したくないので-3を入力します。

ただ、この桁数ですが、わかりにくいので、ネットとかで調べても結構なのですが、
取りあえず、数値を入れてみて、どのような結果になるのかをみて、
数値を修正したほうが早いと思います。

あとは、OKボタンをクリックしましょう。

その後は、オートフィルハンドルを使って数式をコピーしましょう。

最後に、K列と7行目をオートΣボタンを使って合計を算出しましょう。

これで、ROUND関数は完成ですね。
ROUND関数自体を作るには簡単なのですが、元のデータが必要になることと、
桁数のところで、悩む可能性があるのが弱点ですね。

そして、表示形式のユーザー定義を使ってみましょう。

A11:E16は、B2:E7の表を、コピーしたものです。今回は比較できるようにコピーをしましたが、
本来は不要です。

なお、E列と7行目には、オートΣボタンで合計を算出しております。
では、A11:E16を範囲選択して、セルの書式設定ダイアログボックスを表示しましょう。

表示形式の分類をユーザー定義にして、種類に、
#,##0,",000"
と入力します。あとは、OKボタンをクリックしてみます。

これで、完成です。ROUND関数と同じようになりましたね。
桁区切りのところでないと出来ないという欠点はありますが、表示形式を変えただけなので、
元の数値はそのままなというメリットと、
関数を使わないでいいという点がお勧めかと思いますので、
このようなテクニックを覚えておくと、ちょっとした時に役に立つと思いますよ。