Excel。指定桁数で切り上げるのがROUNDUP関数です。【ROUNDUP】

Excel。指定桁数で切り上げるのがROUNDUP関数です。

＜関数辞典：ROUNDUP関数＞

ROUNDUP関数

読み方： ラウンドアップ　　

分類： 数学/三角 

ROUNDUP(数値,桁数)

指定桁数で切り上げる

Excel関数辞典 VOL.68。RIGHT関数～ROUNDUP関数【dictionary】

Excel関数辞典　VOL.68。RIGHT関数～ROUNDUP関数

＜Excel関数＞

今回は、RIGHT関数～ROUNDUP関数までをご紹介しております。

RIGHT関数

読み方： ライト　　

RIGHT(文字列,[文字数])

文字列の右端から文字を取り出す　

RIGHTB関数

読み方： ライトビー　　

RIGHTB(文字列,[バイト数])

文字列の右端から指定のバイト数を返す　

ROMAN関数

読み方： ローマン　　

ROMAN(数値,[書式])

アラビア数字をローマ数字に変換します　

ROUND関数

読み方： ラウンド　　

ROUND(数値,桁数)

指定桁数で四捨五入する　

ROUNDDOWN関数

読み方： ラウンドダウン　　

ROUNDDOWN(数値,桁数)

指定桁数で切り捨てる　

ROUNDUP関数

読み方： ラウンドアップ　　

ROUNDUP(数値,桁数)

指定桁数で切り上げる　

Excel。複数列データから重複を除いたデータの一覧を作成する【Delete duplicates】

Excel。複数列データから重複を除いたデータの一覧を作成する

＜INDEX＋MOD&ROUNDUP＋ROW関数＞

現場で重複データを取り除いて作業するなんてことは、結構あるのですが、次のような複数列にわたってデータがある表があります。

この3列のデータから重複したものを除いたデータの一覧表を作る場合には、

どうしたらいいのでしょうか？

【単数列での重複削除】

一つの列内において、重複データを除きたい場合は、とても簡単に取り除くことができるのようにExcelはなっていますので、確認しておきましょう。

データタブのデータツールにある『重複の削除』を選択します。

重複の削除ダイアログボックスが表示されますので、先頭行をデータの見出しとして使用するにチェックマークがついていることを確認して、OKボタンをクリックしましょう。

これで、削除することができましたね。

なので、重複データを削除するとしたら、一列にデータをしてあげれば簡単に削除することができます。

当然といえば当然なのですが、残念ながら、複数列では対応していないわけです。

【複数列の場合は単数列にする】

複数列ならば、どうやったら、単数列。
すなわち、一列にすることができるのかというのを考えればいいわけですね。

今回使うような小さなデータでしたら、コピー＆ペーストで、一列にしてしまえばいいのですが、データが膨大の場合のことを考えると、コピー＆ペーストも大変です。

そこで、今回はマクロではなくて、関数を使っていくことにします。

該当する範囲からデータを検索するには、INDEX関数を使うと効率がいいかと思われます。

INDEX関数の引数は、INDEX（配列,行番号,列番号）という順番になっています。

配列は、データの範囲なので、B2:D6までを絶対参照で設定すればいいのですが、行番号と列番号が悩むポイントですね。

【行番号から考えていきましょう】

どうやったら、数式をオートフィルでコピーしたら、1～5までを繰り返すように算出することができるのを考えてみましょう。

そして、どうやったら、1を導くことができるのかが大切になります。

繰り返す場合は、MOD関数で除算した余りを算出すれば、繰り返し算出することができます。

そこで、次のように数式を作ってみます。
=MOD(ROW(A5),5)+1
説明しましょう。

ROW(A5)は、5が算出されますね。
これを5で除算した余りをMOD関数で算出します。

当然、5÷5なので余り0（ゼロ）。
1にする必要があるので、+1（プラス1）します。

では、オートフィルで数式をコピーしてみましょう。

1～5を繰り返し算出することができました。

データ100件の場合には、=MOD(ROW(A100),100)+1　としてあげればOKですね。
つまりデータ件数の行数をデータ件数で除算して+1してあげればいいわけです。

【列番号はROUNDUP関数】

列番号は、1列分のデータ個数分で繰り返す必要があります。

つまり、このようにしたいわけですね。

そこで、次に様な数式を作ってみましょう。

=ROUNDUP(ROW(A1)/5,0)
ROW(A1)ですから、1。1÷5なので、0.2それを1にするには、ROUNDUP関数で切り上げてしまえばいいわけですね。

そうすると、1になります。
6行目ならば、6÷5で、1.2なので、2にすることができます。

仮に、データが100件ある場合には、
=ROUNDUP(ROW(A1)/100,0)
と、データ件数で除算すればいいわけですね。

この2つの数式をINDEX関数で使ってF2に数式を作ってみましょう。
=INDEX($B$2:$D$6,MOD(ROW(A5),5)+1,ROUNDUP(ROW(A1)/5,0))

オートフィルで数式をコピーしてみます。

すると、複数列を単数列にすることができますので、あとは、データを別の列に値の貼付けを行って、最初に紹介した、重複の削除を実行すればいいわけですね。

このように、INDEX関数を使って抽出する場合には、行番号と列番号をどうやって持ってくるのかを考えることで様々な表からデータを抽出することができます。

Excel。What-If分析は便利なので使ってみましょう【What-IF analysis】

Excel。What-If分析は便利なので使ってみましょう

＜What-IF分析＞

大量のデータは無くても、日ごろ何気なく使っている計算表だけでも、
今回紹介する、データタブにある『What-If分析』の
「シナリオ」や「ゴールシーク」に「データテーブル」を知ることで、
色々と導きだしたりすることができます。

今回使用するのは、次のような表。

なお、計算式が設定されていますので、まとめて確認しておきましょう。

販売価格は、ROUNDUP関数を使って算出しています。
D3の計算式は、
=ROUNDUP(C3/$B$11,-1)
と設定しています。

なお、今回は、わかりやすいように、
原価率はB11に50%としています。売上高の半分が粗利益という表です。

【シナリオ：値をいろいろその都度変えるのは面倒】

今の原価よりも高くなってしまうことがわかったとします。

その場合、原価を高くなる予定の価格を入力したとします。

その後、今の原価に入力を戻そうとすると、
また入力しなければなりません。

仮にさらに原価が高騰した場合を想定すると、
今の原価をどこかに、保管しておく必要が発生します。

そこで、What－If分析の「シナリオ」を使うことで、
簡単に切り替えて確認することができます。

それでは、シナリオを作ってみましょう。

C3:C7を範囲選択して、データタブの「What-If分析」にある、
「シナリオの登録と管理」をクリックします。

シナリオの登録と管理ダイアログボックスが表示されますので、
追加ボタンをクリックします。

シナリオの追加ダイアログボックスが表示されます。

シナリオ名を入力して、変化させるセルを確認したら、
OKボタンをクリックします。

シナリオの値ダイアログボックスが表示されるので、セルの値を入力します。

今回はわかりやすいように+50した値にしました。

あと、今の価格も作っておくと便利ですので、
追加ボタンをクリックして、同じ要領でシナリオをもう一つ作っておきます。

そして、OKボタンをクリックすると、
シナリオの登録と管理ダイアログボックスに戻りますので、
シナリオを選択して、表示ボタンをクリックしてみましょう。

C3:C7の値が変化したのが確認できます。

それに伴い、当然のことながら、
販売価格なども連動して変わったことがわかります。

なお、このダイアログボックスをこのまま閉じてしまうと、
C3:C7の値はこのままになってしまいます。

なので、元の値をシナリオとして登録しておかないと、
今の価格はいくらだっけ？
ということになってしまいます。

「シナリオ」を知らないと、表をコピーして、数値を変えてみたり、
あるいは、シートをコピーして確認するかと思いますが、
確認したい数が増えると、コピーする量が増してしまい、
管理が複雑になってしまうので、
簡単な機能なので、覚えておくと便利かもしれませんね。

Excel。財務関数シリーズ。目標金額までの期間を求めるNPER関数

Excel。財務関数シリーズ。目標金額までの期間を求めるNPER関数

＜NPER関数とROUNDUP関数とQUOTIENT関数とMOD関数＞

今回取り上げる、財務関数シリーズは、目標金額までの期間を算出することが出来る、

NPER関数

NPERは、Number of PERiods で、ナンバー・オブ・ピリオドだそうです。

今回使う表は次のような表です。

年利は、0%ということはないのですが、今回もヘソクリということで算出してみようと思います。
年利を0%にすることによって、
算出される数値が合っている・いないがわかりやすくなりますので、最初の段階では、
年利は0%にすることをお勧めしております。

月額積立額ですが、今回は、頑張って、20,000円を積立てていくことにします。

そして、ゴールの積立目標額は、1,000,000円としました。

このような設定の場合、積立期間はどのくらいになるのか？を求めることが出来るのが、
今回ご紹介する【NPER関数】です。

さて、算出する前に財務関数の特徴として、
自分の手元から出ていく金額をマイナスにしておかないと、
最終的に算出される値もマイナスになってしまい、ピンときませんので、

B3の月額積立額を-20,000にしておきましょう。

では、B6をクリックして、NPER関数ダイアログボックスを表示しましょう。

利率には、B2/12　今回は年利ですので、月で算出する必要がありますので、
12で除算しておく必要があります。これは、PV関数やFV関数でもおなじみですね。

定期支払額は、毎月の支払額なので、B3ですね。

現在価値は、0。
この現在価値は、現時点で一括払いした時などの金額があれば入力します。

将来価値は、B4。目標金額ですね。

支払期日は、0。
支払いをいつ行うのか？ということを設定できます。0は各期の期末という意味になります。
また、1を入力しますと、各期の期首になります。

それでは、OKボタンをクリックしてみましょう。

B6には、50と算出されましたね。
1,000,000を20,000で除算すれば、当然50ですから合致していますね。
年利を0%にして算出したわけです。

B6の数式は、

=NPER(B2/12,B3,0,B4,0)

なのですが、年利を仮に0.1%にしてみましょう。

この数式のままでは、B6の積立期間が小数点表示になってしまいます。

これは、現実的にはおかしいので、繰り上げる必要が生じますので、
ROUNDUP関数を使う必要があります。

よってB6の数式を次のように修正する必要があります。

=ROUNDUP(NPER(B2/12,B3,0,B4,0),0)

このようにすれば、繰り上げりますので、現実的な期間になります。

また、このB6の数値は、「月単位」になっておりますので、年月にする場合には、
それぞれ、算出してあげる必要も生じます。

例えば、B7に年をB8に月を算出してみると、次のようになります。

B7には、除算して余りを除いた数を算出することが出来る。
【QUOTIENT関数】を使います。
数式は、

=QUOTIENT(B6,12)

最後にB8には、余りを求めますので、【MOD関数】を使います。
数式は、

=MOD(B6,12)

このように設定してあげることによって、年・月も算出することが出来るようになります。

Excel。ROUND。医療事務科の調剤で必須の五捨五超入ってどうやるの？

Excel。医療事務科の調剤で必須の五捨五超入ってどうやるの？

＜五捨五超入＞

職業訓練の講師を担当させていただいておりますと、同じ期間に、パソコンだけではなくて、
「医療事務科」の講座も開講されていることがあって、
先日、医療事務科の先生との会話の中で、調剤の報酬の講義の時に、
【五捨五超入】というのがあることをお聞きしました。

なるほど、四捨五入じゃなくて、【五捨五超入】なんてものも、
世の中にはあるんだなぁ～と感心しておりましたら、
逆に、その先生から、「Excelでは、五捨五超入はできませんよね」っていわれましたので、
今回は、五捨五超入に挑戦したいと思います。

まずは、どういう仕組みなのかを考えてみると、
この調剤の五捨五超入は、小数点第1位が0.5ちょうどまでは、切り捨てて、
わずかでも超えたら切り上げる処理を行うというのが、五捨五超入というのだそうです。

では、下記の表を使って、C列に【五捨五超入】の計算式を考えてみましょう。

再度ルールをまとめておきましょう。
0.5以下なら切り捨てで、0.5より大きければ切り上げ
これを表現する数式を作っていけばいいわけです。

判断は0.5なので、まずは、0.5を減算してあげて、
その数値を切り上げしてあげればいいわけですよね。

B2は1.51なので、そこから、-0.5すると、1.01で、
この値を一の位で切り上げをするので、2になります。

B4は、1.5なので、そこから、-0.5すると、1となって、
この値を一の位で切り上げても、1のままですよね。

ということは、切り上げるので、ROUNDUP関数を使ってあげれば算出できそうですね。

では、C2に数式を作っていきましょう。

=ROUNDUP(B2-0.5,0)

この数式でC2:C4まで、オートフィルを使って算出してみましょう。

このように算出されましたね。これで五捨五超入の結果を算出することができました。

それでは、医療事務の先生から見せてもらった調剤報酬の計算表もどきをアレンジして、
点数を算出してみます。

次の表があります。

1錠あたりの値に1回の使用量をかけて、
10で割った値を【五捨五超入】すると点数が算出される仕組みです。

では、D8をクリックして、ROUNDUP関数のダイアログボックスを表示しましょう。

数値には、B8*C8/10-0.5

桁数は一の位で切り上げますので、0

これで、求めることができます。

オートフィルを使って数式をコピーしましょう。

これで、点数が算出することができました。

が、イマイチ、ピンときませんので、計算の途中結果を見えるようにしてみましょう。

F列には、B列の値を÷10した値が算出されています。
そして、F列の値から0.5減算した数値がG列です。
このG列の値を切り上げているわけです。

このようにすれば、四捨五入だけではなくて、【五捨五超入】も算出することができますので、
調剤関係の方でなくても、ちょっとやってみると、なかなか面白いですよ。