Excel。移動年計のZチャートを作ってみよう。

Excel。移動年計のZチャートを作ってみよう。

Zチャート

先日、Zチャートを教えてほしいと、お話がありましたので、
今回はそれを題材に書いてみましょう。
移動年計は、季節変動が大きい場合は、特に大切ですね。
まず、移動年計を算出してみましょう。
下記のようなデータがあります。さらに、移動年計用の表も作ります。

まずは、単月を入力します。

次に累計を算出しましょう。
F2をクリックして、SUM関数を作っていきます。
今回は手入力したほうが楽なので、数式バーに直接、

=SUM($E$2:E2)

と入力していき、オートフィルを使ってコピーしていきます。
始点を絶対参照にして、その始点までという範囲で設定するだけで、累計ができます。

次に、移動年計を算出します。
移動年計は、過去1年間の数値を合計した数字を時系列で比較した数値です。
なので、2011/4は、2010/5～2011/4までの合計ですね。
2011/4は、オートSUMボタンを使って計算しましょう。
=SUM(B3:B14)
オートフィルでコピーします。

これで、移動年計は完成しました。

さて、Zチャートグラフを作っていきます。範囲は、この表全体ですね。
そして、折れ線グラフを挿入します。

これで完成ですね。移動年計が出来れば、グラフは簡単ですね。

Excel。ビジネスや経理で使われる、主な計算式をまとめてみました。

Excel。
ビジネスや経理で使われる、主な計算式をまとめてみました。

計算式

ビジネス実践スキルの講座や、ビジネスマンのExcel実践講座で、
よくExcelの四則演算はわかるけど、式がイマイチとか、学生の頃数学が苦手で…
という声をよく聞きます。
先日もまとめて、説明をしたら好評でしたので、
今回は、ビジネスシーン。経営分析などで使用する、
主な計算式をまとめてみましょう。

その前に、

【率】【比】という文字が付いたら、割り算です。

売上金額

単価　×　数量
意外かもしれませんが、結構聞かれることがあるんですよ。ナメたらアカン。

割引後金額

元の金額　×（1-割合）
20%OFFならば、元の金額×(1-0.2)ですね。1は100%という意味ですね。

本体価格（税引後価格：消費税5%として）

税込価格　÷（1+5％）

税込金額（消費税5%として）

元の金額　×（1+5%）

平均

合計　÷　個数ですが、これはオートΣボタンで算出できますね。

構成比

その項目の合計　÷　全体
絶対参照は必須ですね。算出後、％ボタンをクリックしましょう。

前年度比

今年度合計　÷　前年度合計
算出後、％ボタンをクリックしましょう。

達成率

実際の数値　÷　目標の数値
算出後、％ボタンをクリックしましょう。
どっちから、どっちを割るのか、分からない場合は、まず割っちゃいましょう。
で100%を越えるか、越えないかを目で確認すれば、
おのずと、判断出来ますね。
頭で考えるよりも、実行しちゃうのがポイントですね。

伸び率

（比較する数値-元の数値）÷元の数値
算出後、％ボタンをクリックしましょう。

売上高

売上の合計ですね。これはオートΣボタンで算出できますね。

粗利率

粗利益　÷　売上高　
または、
売上総利益　÷　売上高　

営業利益率

営業利益　÷　売上高

当期純利益率

当期純利益　÷　売上高

総資産利益率

当期純利益　÷　総資産
投資効率を表す指標で高いほど効率よく利益をあげていることになりますね。

流動比率

流動資産　÷　流動負債
短期的な財務安全性を示します。

自己資本比率

自己資本　÷　総資本
財務安全性を示す数字ですね。

ギアリング（負債比率）

負債合計　÷　総資本
ギアリングは、財務の安全性を示します。

固定資産長期適合比率

固定資産　÷　自己資本
財務安全性を示し、固定資産のうち、自己資本がどれだけまかなっているかがわかります。

債務償還年数

返済財源（営業利益＋減価償却費）　÷　借入金
借入金など債務を何年で返却できるのか？

インタレスト・カバレッジ・レシオ

（営業利益＋受取利息＋受取配当金）　÷　支払利息
利息の支払能力を示します。

キャッシュ・フロー額

営業利益　＋　減価償却

平均従業員

従業員の延べ人数（各月の従業員数の合計）　÷　12

人件費

役員報酬　＋　給与手当　＋法定福利費

売上高人件費率

人件費　÷　売上高

労働分配率

人件費　÷　粗利益
粗利をどれだけ人件費に振り分けているのか

まだまだありますが、一応この辺で。

Excel。一行空きのデータを合計するテクニック SUM関数とISODD+ROW関数

Excel。一行空きのデータを合計するテクニック

SUM関数とISODD+ROW関数

仕事場で一行空きのデータを合計するのが、
面倒でどうにかならないかな？と、
お話がありまして、
確かにSUM関数で、飛び地をCTRLキーでチマチマ、クリックするのも確かに面倒ですね。

ということで、今回は、一列作業列を追加しますが、簡単に合計する方法をご紹介。
下記のようなデータをご覧ください。

金額を合計したいわけですね。本来ならば、こういう表の作り方はよくないわけです。
個数と金額を別々の列にすれば、何も困らないはずですね。
ただ、愚痴をいってもしかたありませんね。
現場レベルではよくあることです。

アイディアとして、
奇数行なのか偶数行なのか判断して、計算対象でないようにすればいいわけです。
なお、この行はROW関数を使うので、行番号です。
また、計算対象外にするということは、ゼロにすればいいわけですね。
で、ここで、IF関数を考えちゃうのですが、
長くなってしまうので、
今回は

ISODD関数

を使用します。
このISODD関数は、イズオッド関数と読みます。
この関数は、奇数の時はTRUEを偶数のときはFALSEを返す関数です。
そして、ここでポイントとなるのが、
Excelは、TRUEは0。FALSEは1を返します。
すなわち、奇数行か偶数行かを判別して、
その結果に数字をかければ、偶数行はゼロになるわけです。

それでは、さっそく作ってみましょう。
まず、D列に計算行を作っていきます。
D4をクリックして、ISODD関数のダイアログボックスを表示します。

数値には、手入力で、ROW(C4)と入力して、OKボタンをクリックします。
数式バーには、

=ISODD(ROW(C4))

が完成していますが、数式的には未完成ですね。これに、C4を掛ける必要があります。
すなわち、数式バーは

=ISODD(ROW(C4))*C4

この式をオートフィルで8月5日までCOPYします。

ここまで、算出できれば、あとは、オートSUMボタンで、簡単に合計を算出することができますね。
結果は、

Excel。Excel2010のピボットテーブルをExcel2003タイプに変える技

Excel。
Excel2010のピボットテーブルをExcel2003タイプに変える技

ピボットテーブル

先日ご質問がありまして、
Excel2010を導入したら、ピボットテーブルが変わってやりにくいので、
どうにかなりませんかね？とのこと。
確かに、Excel2003で使っていたのをExcel2010に変更すると、
すぐには慣れないものですよね。

では、慣れるまで我慢してください。というのもなんなので、
実は、Excel2003のピボットテーブルに変更することが、簡単にできるのです。
ということで、ご紹介しましょう。

まず、
挿入タブ→ピボットテーブルで新規シートにピボットテーブルを作成しましょう。

あとは、右側のピボットテーブルのフィールドリストを使って、
列ラベルや行ラベルなどに移動するわけですね。

Excel2003は、フィールド名を直接、シート上にドラッグ アンド ドロップしたのですが、ここがExcel2010では、変わったわけですね。
ピボットテーブルツールのオプションタブ→ピボットテーブルオプションをクリックします。
ピボットテーブル上で右クリックでもOKですので、
ピボットテーブルオプションのダイアログボックスを表示しましょう。タブは、表示に移動します。

従来のピボットテーブルレイアウトを使用するにチェックマークを付けてOKをクリックしましょう。

Excel2003のピボットテーブルレイアウトに変更されましたので、今までと同じように処理することが可能になりましたね。

Excel。罫線の斜め線をクイックアクセスツールバーで簡単に書く方法 クイックアクセスツールバーをカスタム

Excel。
罫線の斜め線をクイックアクセスツールバーで簡単に書く方法

クイックアクセスツールバーをカスタム

先日、罫線の斜め線を引くのは、どうしたらいいの？
とご質問がありましたので、
どうやって描いたらいいのかをご紹介。

下記のような表があって、色がついている所に斜め線を書きたい訳です。
まずは、一般的な方法。

B3をクリックして、Ctrl+1か、ホームタブの罫線ボタンのその他の罫線をクリックします。

そうすると、セルの書式設定ダイアログボックスが表示されてきます。罫線タブに移動します。

線の種類・色を選択後、
罫線ブロックの右斜め下にある、
斜め線のボタンをクリックします。
このボタンは、ONとOFFができます。
これで、斜め線が描くことができました。

ただ、頻繁に斜め線を書くようでしたら、
その都度に、セルの書式設定ダイアログボックスを表示しないといけないわけで、
効率が悪いですね。

罫線ボタンをクリックして、斜め線があればいいのですが、残念ながらない。

そこで、知っておくと便利なのが、
リボンに表示されていないもので、
意外と用意されているボタンがあるのです。
それを、

クイックアクセスツールバーに登録したら便利になる。

という訳です。

では、やり方をご紹介。

クイックアクセスツールバーの▼をクリックして、その他のコマンドをクリックします。
※ファイルタブ→Excelのオプションと同じです。
 
クイックアクセスツールバーが選択されている状態で、左側がコマンドの選択が、基本的なコマンドになっています。ここの▼をクリックして、リボンにないコマンドを選択します。

これをクリックすると、リボンにない操作が表示されてきます。この中から、【斜め罫線（右下さがり）】を選択して、追加ボタンをクリックすると、右側のクイックアクセスツールバーのユーザー設定に追加されます。

OKボタンをクリックして確定します。

クイックアクセスツールバーに、ボタンが追加されています。では早速クリックしてみましょう。

これで、ボタン一発で、斜め罫線を描くことができましたね。
クイックアクセスツールバー。ちょっとカスタマイズすると便利ですね。

Excel。Excel2010でExcel2003の印刷プレビューを表示する方法

Excel。
Excel2010でExcel2003の印刷プレビューを表示する方法

クイックアクセスツールバーをカスタム

最近、企業研修で、Excel2003を使用していたけどExcel2010になったので…
あれは、これは、どこにいったという質問が多いですね。
そこで、今回は、その中でも多い、

Excel2003の印刷プレビューを使う方法

をご紹介。

Excel2010では、ファイルタブ→印刷をクリックすると、

バックステージビュー

に変わります。

これだと、右側のプレビューは拡大して確認することはできません。
また、表示タブのページレイアウトや改ページプレビューとも、ちょっと違う。

そこで、知っておくと便利なのが、
リボンに表示されていないもので、
便利なもの旧バージョンでお馴染みのものが結構あるのです。
それを、クイックアクセスツールバーに登録しちゃおうという訳です。
では、やり方をご紹介。

クイックアクセスツールバーの▼をクリックして、その他のコマンドをクリックします。
※ファイルタブ→Excelのオプションと同じです。

 クイックアクセスツールバーが選択されている状態で、
左側がコマンドの選択が、基本的なコマンドになっています。
ここの▼をクリックして、

リボンにないコマンド

を選択します。

これをクリックすると、リボンにない操作が表示されてきます。
この中から、【印刷プレビュー（全画面表示）】を選択して、
追加ボタンをクリックすると、
右側のクイックアクセスツールバーのユーザー設定に追加されます。

OKボタンをクリックして確定します。

クイックアクセスツールバーに、ボタンが追加されています。
では早速クリックしてみましょう。

ありゃま、懐かしい。

印刷プレビュー

ですね。
これならば、ズームで確認も出来ますね。
どうしても、バックステージビューになじめない方は、
この方法はいかがでしょうか？

Excel。空白の場合エラーを返さない方法 IF+VLOOKUP関数

Excel。空白の場合エラーを返さない方法

IF+VLOOKUP関数

今回は、空白の場合エラーが出てしまうのを回避する【公式】のような定番関数。

IF関数によるエラー対処方法

をご紹介。

セミナーでも、講義でも、定番中の定番のネタですが、早速やってみましょう。
下記のようなデータがあったとして。

シート名：納品書

シート名：商品一覧

納品書のC13（グレーのところ）にVLOOKUP関数を使って、
商品一覧シートの商品一覧から抽出するとして、
B13の商品IDを空白にしたままVLOOKUP関数を作ると、
下記のような#N/Aというエラーが表示されてしまいます。

数式は、

=VLOOKUP(B13,商品一覧!$A$4:$C$14,2,FALSE)

これは、商品IDが空白なので、エラーになってしまう訳ですね。
そこで、商品IDが空白でもエラーが出ないようにIF関数を使って、
修正してみましょう。
今回は、わかりやすいように、C13の数式を削除して、
何もない状態から作ってみます。
それでは、まず、IF関数のダイアログボックスを表示します。

やりたいことは、商品ID（B13）は空白ですか？
空白だったら、空白。そうでなかったらVLOOKUP。
というのをやりたい訳です。これをボックスに当てはめていきます。

論理式は、B13=””
真の場合は、””
偽の場合は、Vlookup関数を挿入していきます。

これで、エラー表示を回避することが出来ます。
数式は、

=IF(B13="","",VLOOKUP(B13,商品一覧!$A$4:$C$14,2,FALSE))

【IF関数によるエラー対処方法の公式】