Excel。fxで見つからない関数。数字を漢数字にするNUMBERSTRING関数

Excel。fxで見つからない関数。数字を漢数字にする
NUMBERSTRING関数

NUMBERSTRING関数

Excelの講座で、DATEDIF関数を紹介するときに、この関数は、fxボタン。
つまり、関数挿入ボタンを押しても、見つけることは出来ないんですよ。
とお話します。
そこで、

それ以外には何かあるのですか？

と質問されることもあるので、その中から、
まぁ、使うことが無いNUMBERSTRING関数をご紹介します。

NUMBERSTRING関数

は、fx。
関数挿入ボタンから探すことができません。
一覧にいませんので、この関数を直接入力する必要があります。

下記に、NUMBERSTRING関数を使用して作成した表がありますので、ご覧下さい。

説明をしていきましょう。
このNUMBERSTRING関数は、

数字を漢数字に変換する関数です。

C3には、=NUMBERSTRING($C$2,1)
という数式が設定されています。
C2にある数字を1という形式で表示させてたというものです。
C4には、=NUMBERSTRING($C$2,2)
C5には、=NUMBERSTRING($C$2,3)
という数式です。

漢数字を使う際には、是非と言いたいところですが…
関数を使わなくても、表示形式変更すればいいわけでして…意味があまりないような。

Word。段落罫線の裏ワザ。= = =で二重罫線がひけるんです。

Word。段落罫線の裏ワザ。= = =で二重罫線がひけるんです。

段落罫線のショートカット

Wordの講義で、文字のアンダーラインではなく、見出しなど、
分かりやすいレイアウトを紹介する際に、段落罫線の下線を紹介しております。

こうするだけでも、分かりやすくなります。
この線を引こうとすると、段落を選択（行選択）して、ホームタブの段落にある。

線種とページ罫線と網掛け設定を選択します。

そうすると、線種とページ罫線と網掛けダイアログボックスが表示されます。
罫線タブの
種類は、指定。
種類は、二重線。
プレビューは、下段のボタンをクリックします。
そして、何よりも確認しないといけないのが、設定対象が”段落”になっていること。
これが、文字だと、文字に、段落だと段落に設定されます。
あとは、OKボタンをクリックして完成。

線の種類を変えれば、色々な線を引くことが出来ますが、
いちいち、線種とページ罫線と網掛けダイアログボックスを表示して、
選んで、というのは、面倒くさいわけです。

そこで、今回は、そのショートカットというか、裏ワザをご紹介しましょう。

まず、前提として、言語バーを確認して、直接入力になっていることを確認しましょう。
下線を引きたい文字の次の行にカーソルを入れて、
===　
イコールを三個連続で入力して、Enterボタンを押すと、
あら不思議、二重線の段落罫線がひけました。

他にもありますので、ご紹介しましょう。

=== 二重線 イコール
--- 直線 ハイフン
~~~ 波線 ナミ
### 三重線 シャープ
*** ドット線 アスタリスク

Excel。なんで、時給計算がうまくいかない！解決方法は24倍にします。

Excel。なんで、時給計算がうまくいかない！
解決方法は24倍にします。

表示形式

Excelの講座で、必ずといっていい程、ご紹介するのが、時給計算です。
なぜかというと、Excelは、非常に高度な計算をサクサクとこなすのですが、
時間計算及び、時給計算が苦手。
すんなり算出してくれません。
その原因は、Excelは、1日を１とするというルールを設定しております。
Excel（Windows）では1900/1/1を1として設定しております。
ですので、翌日の1990/1/2は2です。

ということは、1=24hというのを頭の中に入れておく必要があります。

説明はともかく、解決方法をご紹介していきましょう。
下記のような表があります。

D8に勤務時間の合計を算出して、すでに、ユーザー定義書式を[h]:mmにしております。
詳しくは、前回の記事をご確認ください。設定前は、21:00になっていました。
さて、分かりやすいように、時給を今回は1,000円として、D10に時給を算出してみましょう。
=D8*D9
という式が、D10に設定すればOKですね。そうすると表示が45000:00となってしまって、この時点で、「なんじゃこりゃ！」と驚く方も多くいます。

これは、合計の表示形式の[h]:mmを継承してしまったので、金額ですから、
通貨スタイルのボタンをクリックして金額に表示形式を変えてみましょう。

すると、どうなるかというと…！！
さらに、「なんじゃこりゃ！」が発生しちゃいました。
￥1,875ってどうして、1000をかけたんだから、￥45,000でしょう？

なぜ、こんなことが起こってしまったのでしょうか？
それは、最初にご説明した、1日を１としてある。1=24hというのがキーポイントなのです。

45000を24で割ってみましょう。答えは1875です。これは、1875日間を表します。
つまり、Excelが考えたのは、45時間の1000倍した時間にすぎないわけです。
あくまでも45000時間=1875日、なのです。
我々の感覚としては、金額が算出されてくるものと思っている訳ですが、
Excelはそういう訳ではないのですね。

通貨の表示形式にした際に、1=24hから、1/24倍されています。
ですので、この値に24をかけてあげる必要があるのです。

D10の数式は、
=D8*D9*24としてみましょう。

金額は、￥45,000となりましたね。これで、納得の金額になりましたね。

今回のおさらいとしては、理屈はどうあれ、

×２４

しましょう。というのを覚えておくといいですね。

しかしながら、表示形式を知らないと、24時間以上の表示にできないし、
さらに時給計算も面倒ですね。
ですので、時間計算ものを仕事で依頼されたら、安請け合いしないほうがいいでしょう。
しっかり考える時間が必要ですね。

ただ、8:00を8.0とか、18;30を18.5とすれば、苦労はしないで済むのですが…

Excel。24h以上の表示が出来ませんので表示形式を変更します。

Excel。24h以上の表示が出来ませんので
表示形式を変更します。

表示形式

Excelの表示形式。これまた、よく使うというか、知らないと困るものの中に、
24時間以上の表記が出来ないというのがあります。
深夜26:30から放送。
というこの26時というのが、Excelでは、ストレートに出来ないのです。
高度な関数や、ピボットテーブルやらと、Excel君は非常に高度な計算を、
アッという間にしてくれるのですが、時間は、全くの苦手。
ということで、24h以上の表示をするための方法をご紹介します。

下記のような表があります。

時間の項目は、退勤から出勤の時間を引きたものが、算出されています。
わかりやすいように、すべて9時間にそろえてあります。
この5日間の合計時間を算出してみましょう。
オートΣボタンで、簡単に合計時間が求められますね。

合計時間は21時間と算出されました。さて、これでOKでしょうか？
ダメですよね。9時間×5日なら45時間のハズ。しかし、21時間？？？。
これは、Excelが1日を1として、24時間が1として、設定されているのが原因なのです。
通常の表示形式ですと、24時間以上の表示が出来ないようになっているのです。
Excelからみると、45時間=24時間+21時間。
つまり、

45時間=1日と21時間。

なわけです。
ですので、表示形式は、”1日と”が消えて、21時間と表示されているのです。

では、どうやって、24時間以上の表記をするのでしょうか？
そこで、ユーザー定義書式の登場となります。
D8をクリックして、セルの書式設定ダイアログボックスを表示しましょう。

表示形式のタブで、種類のボックスをみてみると、h:mm　とあります。
このhの前後に[ ]をつけます。

[h]:mm

と種類を入力して、OKボタンをクリックしましょう。
そうすると、

無事に合計時間が45時間になりましたね。

ちょっとしたことかもしれませんが、
ちょっとしたことをしてあげないと、Excelは時間表示がうまくいきませんので、
覚えておきたいテクニックの1つですね。

Excel。表示形式で聞かれる通貨と会計の違い。

Excel。表示形式で聞かれる通貨と会計の違い。

表示形式

Excelの表示形式。非常に奥が深くて、知っていると便利なこともあるのですが、
この表示形式の質問で結構聞かれるものの中に、

通貨と会計って何が違うのですか？

というのがあります。
そこで、今回は、通貨と会計の違いをご紹介しましょう。

そもそも、表示形式を細かく設定するためには、
セルの書式設定ダイアログボックスを表示する必要がありますね。
セルの書式設定ダイアログボックスは、表示形式に限らず、
非常によく使いますので、ショートカットを覚えておくと、いいですね。

Ctrl + 1

1は、テンキーではダメです。キーボードの1を使います。
そうすると、セルの書式設定ダイアログボックスが表示されます。

表示形式の通貨と会計を設定したものを下記の表にしてみました。

こうすると、違いがよくわかりますね。まず￥マークが通貨は数字の直前にあり、
会計は、セルの左側にありますね。マイナスは通貨が赤字で表示されますが、
会計はそのまま。
ゼロについても、会計は-（ハイフン）が表示されますね。

Excel。在庫管理で使われる、主な計算式をまとめてみました。

Excel。在庫管理で使われる、主な計算式をまとめてみました。

計算式

以前紹介した、ビジネスで使う計算式が好評のようだったので、今回は、その第2弾。
在庫管理で使うことがある計算式をご紹介します。

商品回転率

商品が何回転したかを把握するものですね。
当然、売れ筋は、在庫期間が短いので商品回転率は高くなりますね。

商品回転率　＝　年間売上高　÷　商品在庫高

なのですが、この商品在庫高を求める方法も紹介しておきましょう。
なお、3タイプあります。
＜期首在庫高と期末在庫高の平均を商品在庫高とする＞

平均商品在庫高　＝（　期首商品棚卸高　＋　期末商品棚卸高　）　÷　２

＜期末在庫高を商品在庫高とする場合＞

商品在庫高　＝　期末商品棚卸高

＜毎月の商品在庫高をもとに平均在庫高を求める＞

平均商品在庫高　＝　月次商品在庫高合計　÷　１２

当然、商品回転率を日数換算にすれば、販売するために必要な期間を求めることができますね。

商品回転期間　＝　３６５　÷　商品回転率

また、商品回転率が高くても、
粗利益率が低いと売れば売るほどマイナスなんてことが発生してしまうし、
その逆も、
ちっとも売れないで、死に在庫を抱えてしまう訳ですね。
こうなると今度は、在庫費用がかさんでしまう訳です。そこで、交差比率を求める必要があります。

交差比率　＝　粗利益率　×　商品回転率

ということで、今回は、在庫管理を中心にご紹介しました。

Excel。Excelの７つのエラーと######表示をご紹介。

Excel。Excelの７つのエラーと######表示をご紹介。

エラーと列幅

Excelを仕事で使っていると、様々なエラーが表示されて、悩んでしまったり、
また、仕事でつかえるExcel講座や、他のExcel講座の際にも、
練習してもらったりしているとよく、Excelエラーが表示されて、ビックリする方。
あるいは、計算結果が#####となってビックリする方をよく見ます。
これらのエラーの意味を多少でも知っていると、ビックリすることもなく、
処理することが出来るようになります。
ということで、

今回は、Excelのエラーをご紹介。

１

#NULL!　

ヌル　正式は、Null Intersection （ナル・インターセクション）
セル指定の「:（コロン）」や「,（カンマ）」がない時や、セル範囲に共通部分がないと登場します。
また、参照するセル範囲が間違っている場合に表示されます。
ERROR TYPEの戻り値は、1　です。

２

#DIV/0!　

ディブゼロ　正式は、DIVided by 0（ディヴァイディッド・バイ・ゼロ）
0（ゼロ）または、空白セルで割り算をしちゃうと、登場しますね。
ERROR TYPEの戻り値は、2　です。

３

#VALUE!　

バリュー　正式は、Wrong Type Value （ローング・タイプ・バリュー）です。
不適切なデータが入っている時に登場しますね。
関数の引数に種類が異なる値を指定すると登場しますね。
ERROR TYPEの戻り値は、3　です。

４

#REF!　

レフ　または　リファレンス　正式はA REFerence to a cell that does not exit （ア・リファレンス・トゥ・ア・コール・ザット・ダズ・ノット・イグジット）です。
セルが参照できない時に登場します。参照しているシートやセルを削除した時に登場します。
ERROR TYPEの戻り値は、4　です。

５

#NAME?　

ネーム　正式は、Unrecognized Name （アンリコグナイズド・ネイム）です。
関数名やセル範囲名などの名前が正しくない時に登場します。
ERROR TYPEの戻り値は、5　です。

６

#NUM!　

ナム　または　ナンバー　正式は、An Invalid Number （アン・インヴァリッド・ナンバー）です。
数値が大きすぎる。または、小さすぎると登場します。
数値を指定すべき個所に数値以外のものが指定されているときに登場します。
ERROR TYPEの戻り値は、6　です。

７

#N/A　

エヌエー　正式は、No Assign （ノー・アサイン）です。
値がない時に登場します。Vlookup関数の時によく見かけますね。
検索値が空白だった時にIF+Vlookup関数というネストにしていないと、登場します。
ERROR TYPEの戻り値は、7　です。

こういうのをちょこっと知っていると、対応処理しやすくなりますね。
そして、計算結果がよく、

###########

と表示されて、エラーですか？と聞かれることがありますが、
これは、列幅が小さいので、桁あふれをしているときに表示されるものです。
ですので、この場合は、列幅を広げてあげれば、ちゃんと表示してくれます。


このように、Excelのエラーは7つありますが、
エラーの表示は、Excelのテクニックを向上させるためには、逆に好都合だと、
講座ではお話します。
その原因を数式バーをみて、解明できれば、次回から同じケースの場合は、当然エラーを回避できますし、発生ししても、原因を把握して対応することもしやすくなるかと思います。

エラー。表示された場合は、焦らずに、ゆっくりと対応していきましょう。

Excel。負の時にINT関数では、使えない時がある。

Excel。負の時にINT関数では、使えない時がある。

INT関数とTRUNC関数。絶対値のABS関数

Excel。小数点以下を切り落として、整数化するときに、
INT関数を使いますが、このINT関数は、場合によって、アンマッチになることがあります。
それは、負の数の時。
その時は、TRUNC関数を使うことをお勧めします。

では、下記のような表があります。

B2に、1.58がC3に-1.58が入力されていて、
INT関数を使うと、正数は、小数点以下がカットされるので、
1に、
ところが、負数を見てみましょう。
-1.58が-1になるとおもいきや、-2になってしまうんですね。
感覚としては、
小数点以下をカットするだけじゃないの？
なんですけどね。

原因は、INT関数は、負数は、指定した値を超えないように整数化しちゃうからなのです。

そこで、-1にするには、どうするのか？というと、
TRUNC関数を使用するわけです。
では、C5をクリックして、数式を作っていきます。

TRUNC関数のダイアログボックスを表示しましょう。
数値には、C3をクリックします。OKボタンをクリック。これで完成。

で、これだけで終わっちゃうのも、なんなので、
ついでに、絶対値を算出するABS関数もご紹介しましょう。
絶対値とは、＋・－の符号を除いた値を算出します。
B5をクリックして、ABS関数のダイアログボックスを表示しましょう。

数値には、B3をクリックします。OKボタンをクリック。これで完成

Word。線路を図形で描いてみよう。

Word。線路を図形で描いてみよう。

図形

Word。とは限りませんが、PowerPointでもExcelでも作れるのですが、
今回は、地図を描くときに知っておくと便利な、

【線路】の描き方

をご紹介します。

まずは、図形の直線を選択して、Shiftキーを押しながら、ドラッグしましょう。
Shiftキーを押すと、直線を引くことができますね。

次に、太さと色を変更します。色は黒にして、太さは、6ptにしています。もっと太くてもOKです。

このような線に変わります。

この線を垂直方向（上でも下でも）にコピーをします。ctrlキーとshiftキーを押しながらドラッグすると、垂直方向にコピーできますね。

コピーしたほうを選択してあることを確認して、色を白。破線。
そして、太さを4.5ptに変更します。
ここでは、白にすると見えなくなっちゃいますので、グレーにしていますので、ご了承ください。

そして、破線にした線を元の黒線の上に移動して重ねます。
ポイントは、↑のカーソルを連打していくといいでしょう。少しずつ戻っていきます。

あとは、グループ化して完成ですね。
どうですか。綺麗な線路が描けましたね

Excel。年末年始。美味しいものを食べちゃった方へ。BMI算出方法

Excel。年末年始。美味しいものを食べちゃった方へ。
BMI算出方法

BMIの計算方法

お仕事柄、年末年始。
関係先のみなさんから、御呼ばれして、忘年会に行ってきましたが、
みなさんもこの時期、忘年会やら新年会。
クリスマスに、打ち上げに、お正月と、美味しいものを沢山たべちゃった方も多いと思います。

まぁ、美味しいものを多く食べちゃうと…おなか周りが気になる方も多いともいます。
そこで、今回は、BMI。
そう、肥満度を表す、ボディマス指数を算出する計算式をご紹介します。

ところで、BMI（ボディマス指数）というのは、
なんでも、身長と体重の関係からはじき出される、肥満度を表す指数だそうでして、
その計算式は、

BMI（ボディマス指数）＝体重　÷　身長の二乗

で、BMIを算出する関数はありませんので、
この数式を使えば、算出することが出来るのですが、
ちょっと注意点が必要で、この身長の単位が、メートルなんですね。
つまり、だいたい日常的に、「身長は？」って聞かれたら、
「170センチ」って答えると思うんですね。

ですので、メートルに変換してあげないといけない。というのが注意点なのです。

この注意点を踏まえたうえで、下記のようなシートを作ってみましょう。

先程も書きましたが、身長はいつもセンチメートルで表現していますので、
センチメートルで入力させて、計算式上で、100で割るようにしてあげれば言い訳ですね。

それでは、説明をしますと、C2に身長をセンチメートルで入力します。
C3に体重を入力します。
C5に、BMIを算出する数式を入力します。
では、どんな数式かというと、

=C3/(C2/100)^2

という数式が入っております。
ポイントは、C2/100ですね。身長をメートルにしないといけませんから、100で割っております。

これだけでは、物足りないので、C6に、判定を表示するIF関数を作成してみました。
C6の数式は、
=IF(C5>=40,F7,IF(C5>=35,F6,IF(C5>=30,F5,IF(C5>=25,F4,IF(C5>=18.5,F3,F2)))))
う～む。長い。今回は、IFのネストで作成してみました。

この、BMI（ボディマス指数）。
日本脂肪学会さんによりますと、
数値によって、低体重～肥満4度まで区分されるそうでして、

低体重（痩せ型）	18.5未満
普通体重	18.5以上25未満
肥満（1度）	25以上30未満
肥満（2度）	30以上35未満
肥満（3度）	35以上40未満
肥満（4度）	40以上

という区分だそうです。
太りすぎもよくないけれど、痩せすぎもよくないといわれていますので、
年末年始で、体重が増しちゃいそうなこのシーズン。
ちょっと気にしてみてもいいのかもしれませんね。

けど、体重は、測定しやすいけれど、身長って、測定しにくいですよね。