11/29/2014

Excel。AVERAGE関数。仕事でよく使うExcel関数 第5位タイ 平均を求める。


Excel。AVERAGE関数。
仕事でよく使うExcel関数 第5位タイ 平均を求める。

AVERAGE関数

MOS(マイクロソフトオフィススペシャリスト)を主宰しているオデッセイさんが、
「仕事でよく使うExcel関数は? つぶやき大募集」という企画した結果の
「Excel関数」をランキングが発表されまして、


  1. SUM関数
  2. VLOOKUP関数
  3. IF関数
  4. COUNTIF関数
  5. AVERAGE関数
  6. SUMIF関数
  7. SUMIFS関数
  8. IFERROR関数
  9. COUNTA関数
  10. ROUND関数

だそうです。
ランキング外ですが、
このほか「DATEDIF関数」「RATE関数」「COLUMN関数」などもはいっていたそうです。

ということで、今回は第5位タイのAVERAGE関数を改めてご紹介しましょう。

私の講座の場合、職業訓練でも初心者さん対象の講座でもマンツーマンでも企業研修でも、
SUM関数の次に紹介することになるのが、今回のAVERAGE関数。
オートSUMボタンの▼の中でも合計に次いで、よく使われていると思いますが、
5位ですか…意外ですね。

さて、使い方はいたって簡単ですので、確認していきましょう。
なお、今回はオートSUMボタンの▼の中のAVERAGE関数で紹介していきます。
下記のような表があります。

B9に合計が算出されていて、B10に平均値を求めるという表ですね。
では、B10をクリックしておきましょう。
関数挿入ダイアログボックスを使ってもいいですか、頻繁につかうので、
ボタンになっていますので、オートSUMボタンの▼をクリックして、その中から、
平均を選択しましょう。

そうすると、AVERAGE関数が出てきて、平均を算出する範囲を自動的に選択します。

さて、このExcelが設定してきた範囲のままでOKでしょうか?残念ながら、
これでは、ダメですよね。
合計が加わってしまっています。
ですので、だいたい、AVERAGE関数を使う表計算の場合、
合計が【邪魔】をしているケースがあると思われますので、
こういう場合は、自分で範囲選択をやり直す必要がありますので、
B3:B8を範囲選択し直しましょう。

B10には55と算出されましたね。数式は、
=AVERAGE(B3:B8)
このようにして、平均値を算出することが出来ます。

ただ、平均値というのは、曲者でして、今回の場合も該当しますが、
内藤さんから上野さんまでのデータを平均値で算出していいのでしょうか?

というのも、内藤さんと上野さんのお二人で、全体の数字を押し上げてしまっていますよね。
見た感じ平均値55というのは、なんかしっくりきませんよね。

そこで、紹介しておきたいのが、中央値という算出方法です。
使う関数は、
MEDIAN関数。

これが中央値を算出する関数です。

B11をクリックして、数値1に、B3:B8を入力してOKボタンを押しましょう。
すると、中央値は36と算出されましたね。

こっちのほうが、今回のデータだとしっくりきますね。
AVERAGE関数だけではもったいないので、MEDIAN関数も覚えておくといいですよね。

11/26/2014

Excel。SUMIF関数。仕事でよく使うExcel関数 第5位タイ 条件付での合計を求める。


Excel。SUMIF関数。仕事でよく使うExcel関数 第5位タイ 
条件付での合計を求める。

SUMIF関数

MOS(マイクロソフトオフィススペシャリスト)を主宰しているオデッセイさんが、「仕事でよく使うExcel関数は? つぶやき大募集」という企画した結果の「Excel関数」をランキングが発表されまして、


  1. SUM関数
  2. VLOOKUP関数
  3. IF関数
  4. COUNTIF関数
  5. AVERAGE関数
  6. SUMIF関数
  7. SUMIFS関数
  8. IFERROR関数
  9. COUNTA関数
  10. ROUND関数

だそうです。
ランキング外ですが、
このほか「DATEDIF関数」「RATE関数」「COLUMN関数」などもはいっていたそうです。

ということで、今回は第5位タイのSUMIF関数を改めてご紹介しましょう。

前回ご紹介した第7位のSUMIFS関数は、複数条件での合計値を算出する関数でしたが、
今回のSUMIF関数は、シングル条件の合計値を算出する関数ですね。

これの関数は、初心者さんの講座はもちろんのこと、
職業訓練・企業研修にマンツーマン講座でも必ずご紹介する関数です。

また、ランキングも5位タイということで、
現場レベルでもよく使われている関数という事は間違いないと思いますので、
是非覚えておきましょう。

さて、このSUMIF関数は、SUMIFS関数よりも作るときに、【?】しやすい関数なので、
以前ご紹介しておりますが、ここで改めてご紹介していきたいと思います。

では、下記の表があります。
今回は、H3に支店が東京の合計金額を算出させてみましょう。

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

まず、範囲ですが、これが、鬼門というか、厄介なんですよね。
漠然と”範囲”なんていわれてもね。

どこを範囲選択すればいいんですか?とよく質問があります。
この範囲は、その次で設定する検索条件が入っているデータ範囲、列ということになります。
今回の検索条件は、支店が東京ということですから、範囲は支店の列が該当します。

ということで、
範囲には、
B3:B21
と入力しましょう。前回もご紹介しましたが、データが大きな場合には、

ctrl+shift+↓

で範囲選択できるショートカットですので、覚えておくと便利ですね。

次は、検索条件ですね。先ほど書いちゃいましたが、
ここには、検索したいキーワードになりますので、今回は東京の合計金額なので、

検索条件には
“東京”と入力します。

そして、最後の合計範囲ですが、何を合計したいのか?ということになりますので、
売上金額の列ということになりますね。

ですので、合計範囲には、
F3:F21
を入力しましょう。

あとは、OKボタンをクリックして完成ですね。
H3の数式は、
=SUMIF(B3:B21,"東京",F3:F21)
となっていますね。
SUMIF関数は、関数を勉強する中でも序盤に登場しますので、苦手な方は、
確認するとお仕事で役に立つと思いますよ。


11/23/2014

Excel。SUMIFS関数。仕事でよく使うExcel関数 第7位 複数条件での合計を求める。


Excel。SUMIFS関数。仕事でよく使うExcel関数 第7位 
複数条件での合計を求める。

SUMIFS関数

MOS(マイクロソフトオフィススペシャリスト)を主宰しているオデッセイさんが、
「仕事でよく使うExcel関数は? つぶやき大募集」という企画した結果の
「Excel関数」をランキングが発表されまして、

  1. SUM関数
  2. VLOOKUP関数
  3. IF関数
  4. COUNTIF関数
  5. AVERAGE関数
  6. SUMIF関数
  7. SUMIFS関数
  8. IFERROR関数
  9. COUNTA関数
  10. ROUND関数

だそうです。
ランキング外ですが、このほか「DATEDIF関数」「RATE関数」「COLUMN関数」なども
はいっていたそうです。

ということで、今回は第7位のSUMIFS関数を改めてご紹介しましょう。

このSUMIFS関数も、企業研修や職業訓練。そしてマンツーマンなど、
様々な所でもお話している関数の1つですね。確かにこれもよく現場レベルでは使うでしょうね。

SUMIFS関数の特長は、
単発条件での合計値を算出するSUMIF関数の単発条件が複数条件での合計値を
算出できるようになっている点ですね。

そして、SUMIF関数よりもSUMIFS関数の方が数式を作る際に、
関数が苦手な方や、初心者の方には、比較的わかりやすいかもしれませんね。

それでは、早速実際に作っていくことにしましょう。
下記の表があります。

H3に、この表のデータを使って、【東京のDVDの合計金額】を算出しみたいと思います。
それでは、H3をクリックして、SUMIFS関数のダイアログボックスを表示しましょう。

合計対象範囲。これは、合計したい列の範囲を選択しますので、
今回は合計金額を算出したいので、

F3:F21

と入力しましょう。

なお、SUMIF関数などデータが大きい場合に、
いちいち範囲選択をしていると大変ということもあると思いますので、その時には、

ctrl+shift+↓のショートカットキーをすれば、簡単に範囲選択をしてくれますね。

合計対象範囲を設定すると、
条件範囲1のあとに、条件1というのがダイアログボックス内に表示されてきます。

続いて、条件範囲1です。

この条件範囲1には、この後の条件1が含まれている列の範囲という意味になりますから、

B3:B21

を入力しましょう。

次は、条件1ですね。

これは、先程設定しました、条件範囲1の中での条件ということになりますので、
今回は【東京】という文字ですので、ここには、【東京】という文字を入力しましょう。

よく、セル番地にデータが入っている場合があって、それを使うこともありますが、
何かの拍子に変わってしまうと、算出結果も変わってしまうので、
入力するほうがいいかと思います。

そして、次の条件を設定していきますので、
条件範囲2には、商品名の列。すなわち、
C3:C21
を入力しましょう。
最後に、DVDを条件範囲2の中から検索したいので、
DVD
と入力しましょう。

あとは、OKボタンをクリックしましょう。これで完成ですね。
数式は、
=SUMIFS(F3:F21,B3:B21,"東京",C3:C21,"DVD")

なぜ、SUMIFS関数がSUMIF関数よりも作りやすいのかは、
SUMIF関数の時に改めてご紹介することにしましょう。



11/20/2014

Excel。IFERROR関数。仕事でよく使うExcel関数 第8位 エラーだったらどうする。


Excel。IFERROR関数。仕事でよく使うExcel関数 第8位 
エラーだったらどうする。

IFERROR関数

MOS(マイクロソフトオフィススペシャリスト)を主宰しているオデッセイさんが、「仕事でよく使うExcel関数は? つぶやき大募集」という企画した結果の「Excel関数」をランキングが発表されまして、


  1. SUM関数
  2. VLOOKUP関数
  3. IF関数
  4. COUNTIF関数
  5. AVERAGE関数
  6. SUMIF関数
  7. SUMIFS関数
  8. IFERROR関数
  9. COUNTA関数
  10. ROUND関数

だそうです。
ランキング外ですが、
このほか「DATEDIF関数」「RATE関数」「COLUMN関数」などもはいっていたそうです。

ということで、今回は第8位のIFERROR関数を改めてご紹介しましょう。
IFERROR関数は、Excel2003ではなかった関数なのですが、
このIFERROR関数は結構使い勝手がいいというか、エラーが出たら、どうする?
という関数なので、比較的わかりやすい関数ですね。

それでは、下記の表をご覧ください。

3行目のD3には、#DIV/0!というエラーが表示されていて、
4行目のD4にはエラーは表示されていませんよね。
実はD4には、すでに今回紹介する、IFERROR関数の数式を作ってあるのです。

結果からみると一目瞭然ですが、エラーを表示させないですむ。というのが、
今回紹介するIFERROR関数なのです。

ところで、D3の#DIV/0!というエラーは何を意味しているのか?というと、
これは、ゼロで割っている、ゼロで除算しているよというエラーですね。
ちなみに読み方はディバイドバイゼロといいます。

つまり、C3が空っぽ、すなわちゼロなので、エラーが表示されている訳です。
念のため、C3もC4もゼロにしたらどうなるのか?を確認してみましょう。

やはり、D3には#DIV/0!というエラーが表示されていますが、
D4には、エラーが表示されませんね。

では、早速IFERROR関数の作り方を見ていくことにしましょう。D4をクリックして、
IFERROR関数のダイアログボックスを表示しましょう。

値には、金額÷購入者数なので、B4/C4を入力しましょう。
つづいて、
エラーの場合の値には、今回は空白にしたいので、空白を意味する””を設定しましょう。
“”の間には、スペースは入りませんのでご注意ください。
あとは、OKボタンをクリックして完成ですね。

数式は、
=IFERROR(B4/C4,"")
これで、エラーを表示しないで済むわけですね。
本来ならIF関数を使ってエラーを表示しない、IF関数の方程式を使うことが多いのですが、
IF関数で作るよりも、初心者の方には、IFERROR関数のほうが簡単に感じられるようですね。

ですので、企業研修でもマンツーマンでも、職業訓練でも、
このIFERROR関数は紹介することが非常に多いですね。

なお、以前このBLOGでも紹介しておりますが、IFERROR関数は万能ではありませんので、
IF関数でも作れるようにしておくといいでしょう。

Excel。IFERROR関数じゃダメな時ってどんな時?後編
http://infoyandssblog.blogspot.jp/2014/08/exceliferror_29.html

11/17/2014

Excel。COUNTA関数。仕事でよく使うExcel関数 第9位 データの個数を数えます。


Excel。COUNTA関数。仕事でよく使うExcel関数 第9位 
データの個数を数えます。

COUNTA関数

MOS(マイクロソフトオフィススペシャリスト)を主宰しているオデッセイさんが、「仕事でよく使うExcel関数は? つぶやき大募集」という企画した結果の「Excel関数」をランキングが発表されまして、

1.SUM関数
2.VLOOKUP関数
3.IF関数
4.COUNTIF関数
5.AVERAGE関数
5.SUMIF関数
7.SUMIFS関数
8.IFERROR関数
9.COUNTA関数
10.ROUND関数
だそうです。
ランキング外ですが、
このほか「DATEDIF関数」「RATE関数」「COLUMN関数」などもはいっていたそうです。

ということで、今回は第9位のCOUNTA関数を改めてご紹介しましょう。
確かに今回のCOUNTA関数。そうそう、この関数の読み方大丈夫ですか?
「カウントエー関数」ですよ。

間違っても「カウンタ関数」なんて読みませんように。

ちなみにこのCOUNTA関数。
企業研修でも、マンツーマンでも、職業訓練でも必ずご紹介する関数でもあります。
初心者さん向きの関数ですが、
このCOUNTA関数も、お仕事で確かによく使っているだろうな~と思います。

さて、オートSUMボタンにある、数値の個数の関数である、COUNT関数がランキングしないで、COUNTA関数がランキングに入ってきたのは、おおよそ推理はできますよね。

それでは、改めて、COUNT関数とCOUNTA関数の違いを下記の表を使って確認してみましょう。

このような店舗一覧がありまして、F2に店舗数を算出したいわけですね。
見てすぐに、10店舗とは言わないでいただきたいところですが…

それでは、F2をクリックして、
オートSUMボタンの中の数値の個数を使って算出してみたいと思います。

普通この手の場合は、C列の店舗名を範囲選択することが多いと思いますので、
範囲は、C3:C12にしてみましょう。

すると、F2の結果は…ゼロ?!

なんでこうなったのかといいますと、このCOUNT関数。数値の個数というように、
数値だけしか数えてくれないんですね。
今回は文字のデータがC3:C12まではいっていたので算出した結果が
ゼロになってしまったんですね。

では、範囲をB3:B12に変えてみましょう。
F2の数式は、=COUNT(B3:B12)。
するとF2の結果は、10と算出されましたね。B列は数値が入っているからです。

では、今度はF2にCOUNTA関数を設定してみましょう。
COUNTA関数のダイアログボックスを表示しましょう。

値1にはC3:C12を設定すると、10と算出されましたね。
F2の数式は、=COUNTA(C3:C12) となっています。
今回のCOUNTA関数は、セルにデータがあれば数えてくれるので、
数値でも文字でも数える対象となるわけです。

日頃の業務で数値がないデータを数えることは多いと思われますので、
COUNT関数だけでなくCOUNTA関数も覚えておくといいですよね。

なお、COUNTA関数のダイアログボックスを表示して作るよりも、
COUNT関数をオートSUMボタンで作ってから、
COUNTのTのあとにAと入力したほうが簡単だと思います。

11/14/2014

Excel。ROUND関数。仕事でよく使うExcel関数 第10位 四捨五入で活躍します。


Excel。ROUND関数。仕事でよく使うExcel関数 第10位 
四捨五入で活躍します。

ROUND関数


MOS(マイクロソフトオフィススペシャリスト)を主宰しているオデッセイさんが、

「仕事でよく使うExcel関数は? つぶやき大募集」

という企画した結果の「Excel関数」をランキングが発表されまして、


  1. SUM関数
  2. VLOOKUP関数
  3. IF関数
  4. COUNTIF関数
  5. AVERAGE関数
  6. SUMIF関数
  7. SUMIFS関数
  8. IFERROR関数
  9. COUNTA関数
  10. ROUND関数

だそうです。
ランキング外ですが、
このほか「DATEDIF関数」「RATE関数」「COLUMN関数」などもはいっていたそうです。

ということで、今回は第10位のROUND関数を改めてご紹介しましょう。
確かに、ROUND関数も、仕事でよく使う関数の1つですよね。
このROUND関数は、
企業研修でも、マンツーマンでも、職業訓練でも必ずご紹介する関数でもあります。

さて、このROUND関数はどんな動きをするのかというと、【四捨五入】ですね。
仲間に、切り上げのROUNDUP関数。切り捨てのROUNDDOWN関数がありますね。

では、このROUND関数の作り方を見ていくことにしましょう。
下記の表があります。

B3に30。C3に11と入力として、D3には、B3÷C3の数式である =B3/C3が設定されています。
よってD3には、2.727272727という結果が算出されました。

このD3の値を、小数点第2位まで表示するようにして、四捨五入をしたいとした場合に、
登場するのが今回のROUND関数になります。

では、E3にROUND関数のダイアログボックスを表示しましょう。

まず数値ですが、これは、D3ですね。
そして、問題になるのが、この桁数。
この桁数は、切り上げのROUNDUP関数。切り捨てのROUNDDOWN関数でも、
同じように考えちゃうところですね。

この桁数は、小数点第何位まで表示させますか?と考えるとわかりやすいかもしれませんね。
今回は小数点第2位まで表示したいわけです。

ですから、小数点第3位を四捨五入するわけですね。

よって、小数点第3位は、2.727の7ですから、繰り上がって、
2.73という結果が算出されるわけですね。

では、今度は一の位を四捨五入したい場合はどうしたらいいでしょうか?
下記の表があります。

結果のE3をご覧ください。2730と一の位が繰り上がって、2727が2730になっていますよね。

このような場合の桁数はというと、

桁数には-1が入力されていますね。一の位や十の位などを四捨五入する場合には、
このようになります。

よって、E3には、=ROUND(D3,-1) という計算式が設定されています。

さて、この桁数を、どうだっけかなぁ~と悩むぐらいならば、取りあえず、
数値を入れて結果を見て修正をしたほうが、絶対に速いですね。

Excelは、悩むぐらいなら、手を動かしてみようとよく、お話します。
今回の桁数は、その例のひとつですね。

なお、数値には直接、計算式を設定してもOKですね。

数値には B4/C4と入力すれば、そのまま算出することもできますね。
数式は、=ROUND(B4/C4,-1) となっていますね。

11/06/2014

Excel。SUM関数はやっぱり便利、小計だけを足したい時にも大活躍で初心者に感激されます。


Excel。SUM関数はやっぱり便利、
小計だけを足したい時にも大活躍で初心者に感激されます。

SUM関数

職業訓練でも新人研修でも個別レッスンであっても、必ず序盤にご紹介する関数の中に、
SUM関数があります。

そう。オートSUMボタンでおなじみですね。

このSUM関数。結構賢くて便利なのは皆さんご存知の通りだと思います。

以前説明したことのある、【累計】などもSUM関数のひとつのアレンジですが、
今回ご紹介するのは、途中に小計があって、
最終行に合計がある場合のSUM関数をご紹介していこうと思います。

今回の内容は、Excelのベテランさんには、【このどこが?】とお思いになるかもしれませんが、
初心者の方が多い会場の時には、【おおっー】と歓声が上がることが多い内容なんですよ。

さて下記の表があります。

5行目、9行目、14行目の小計をそれぞれ、オートSUMボタンを使って算出していきましょう。
まずは、B5をクリックして、オートSUMボタンをクリックしましょう。

範囲選択を確認してからEnterキーを押すと、
4月の内藤さん、大久保さん、馬場さんの合計が算出されましたね。

B5には244と算出されましたね。数式は、=SUM(B2:B4)ですね。
さて、つづいて、大塚さん、田端さん、谷さんの小計をB9に算出していきましょう。
B9をクリックして、オートSUMボタンをクリックすると、なんと、賢いことに合計する範囲が、
B6:B8になっていますよね。

なんとなく、B2:B8まで範囲選択されてしまって、
こっちで改めて範囲選択しないといけないようなイメージがありますが、
オートSUMボタンを使いますと、自動的に判断して小計は含めない、
そして、その小計の上も範囲として選択しない設定になっております。

Excelも慣れてきますと、オートSUMボタンではなく、
数式バーに直接関数を入力する方も多くなってきますが、
用途によって、どちらでもできるようにしておくといいでしょうね。

引き続き、上野さん~右京さんまでの小計を同じように算出してみましょう。数式は、=SUM(B10:B13)。

で315と算出されました。

さて、次はいよいよ、合計を算出しようと思います。
3か所の小計を合算してあげればいいわけですね。

そうすると、=B5+B9+B14という数式を作りがちですが、

ここもオートSUMボタンの合計を使ってみると、

オートSUMボタンって賢いなぉ~と実感できると思います。

それでは、早速やってみましょう。
B15をクリックして、オートSUMボタンを押してみると…

なんと、小計のところだけを範囲選択してくれましたね。
ですので、=B5+B9+B14ではなくて、数式は、=SUM(B14,B9,B5)となります。

これは、SUM関数の範囲をctrlキーを使って選択したのと同じ処理ですね。
ということで、今回は初心者の方が驚いてくれるネタでした。



11/02/2014

Excel。CONVERT関数。ヤードをメートルに変換できちゃう。


Excel。CONVERT関数。ヤードをメートルに変換できちゃう。

CONVERT関数

職業訓練や企業研修に限らず、個人レッスンでも、講義のちょっとしたインターバルに、
ちょっとした、関数を紹介しているのですが、
今回紹介しようと思います、
CONVERT関数
もそのうちの一つでして。
紹介しますと、「へぇ~」と感嘆の声を上げてくれることもある、このCONVERT関数。

簡単なので、ちょっと覚えておくといい関数なので、早速、ご紹介いたしましょう。

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

まずは、ヤードをメートルに変換してみたいと思います。
よくゴルフで400ヤードなど、ゴルフではヤードはおなじみなのですが、
日常生活において、イマイチ、ヤードというと、ピンとこないわけでして、
400ヤードは何メートルなのか?あと、よく450ヤード。パー4とかいったりするように、
ゴルフでは、おなじみの単位ですね。

余談ですが、ギネスブックによりますと、515ヤードがゴルフトーナメントでの記録だそうです。
いったい何メートルなんでしょうね。

また、アメリカンフットボールは10ヤードをかけて勝負が進んでいくスポーツですし、
そういえば、アメリカンフットボールのグラウンドには、1ヤードごとに線が引いてありますよね。

これをCONVERT関数を使うと瞬時にわかるわけなんですね。
では、E3をクリックして、CONVERT関数のダイアログボックスを表示してみましょう。

数値には、400がはいっている、B3をクリックしましょう。
つづいて、変換前単位には、ydを入力します。このydがヤードを意味するものです。
このydは、mとかと同じように正式なヤードの単位なんですよ。
そして、変換後単位には、mを入力します。このmはメートルを意味するものです。

これで、OKボタンをクリックしましょう。
すると、E3には、

365.76が表示されましたね。本当にあっているのか?疑問に思う方もいると思いますので、
ここは、Googleで100ヤードが何メートルなのかを検索してみましょう。

すると、

100ヤードは、91.44メートル。つまりこの4倍ですので、91.44×4ですので、間違っていませんね。
アメリカンフットボールだと、
10ヤードをどう進めていくのか?ということを争う訳なので、
9メートルの攻防ということなんでしょうね。

このように、ヤードをメートルに簡単に変換することが出来ましたね。

そうそう、ゴルフのトーナメントにおける、最長記録である、515ヤードって何メートルなんでしょうね。
せっかくなので、確認してみましょう。

なんと、515ヤードは、470.916メートル!
本当にこんな距離をあのゴルフボールは飛んでいくものなのでしょうかね?すごいなぁ~。