2016年11月06日

ISERROR関数でエラーかどうか調べてみた / Excel / 情報関数

Excelの関数でエラーかどうか調べます。
ISERROR関数を使用します。

例えば、ある値を"0"で割ると"#DIV/0!"とエラーが表示されます。
この様なエラーが表示された時にエラーだったら"TRUE"、エラーではなかったら"FALSE"を表示するのがISERROR関数です。

このような式を入力します。
セル"C3"には"A3"を"B3"で割る式を入力しています。
セル"D3"にはISERROR関数で"C3"がエラーかどうか調べます。
20161106_01.JPG

と、この様な結果になりました。
"C3"は10を0で割っているのでエラー"#DIV/0!"が表示されました。
なので、"D3"には"TRUE"が表示されました。
20161106_02.JPG

"1"で割るとエラーではなくなるので"D3"は"FALSE"になりました。
20161106_03.JPG

他のエラーでも実際にやってみます。
こんな式を入力しています。
20161106_04.JPG

するとこんな7種類のエラーが表示されました。
20161106_05.JPG

これらのエラーについてはコチラをご覧ください。
で、最後の"#######################・・・#"はエラーでは無くて時間のシリアル値がマイナスになった時の表示なのでISERROR関数では"FALSE"が表示されます。



関連ページ
関数一覧(カテゴリ別)
関数一覧(名前別)
エラー値について(#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか)


ラベル:ISERROR エラー
posted by haku1569 at 17:59| Excel関数 | このブログの読者になる | 更新情報をチェックする

2015年06月13日

SUMPRODUCT関数で配列の掛け算の合計を求めてみた/Excel/数学関数

Excelの関数で配列の積(掛け算)の合計を求めます。
SUMPRODUCT関数を使用します。
"掛け算の合計"って分かり辛いかもしれませんが、いわゆる"重み付けの合計"のことです!?
1.掛け算の合計を求める
2.抽出したデータの掛け算の合計を求める
3.加重平均を求める


1.掛け算の合計を求める
例えばこんな商品名とその単価、販売数のテーブルがあります。
この販売金額の合計を求めてみましょう。
20150613_01.JPG

SUMPRODUCT関数を使わない方法
まず、商品毎に単価と販売数量を掛けて販売金額を求めてから、その合計をSUM関数で求めるのが一般的でしょう。。。
20150613_02.JPG

SUMPRODUCT関数を使ってみる
「数式」、「関数ライブラリ」、「数学/三角」から「SUMPRODUCT」を選択します。
20150613_03.JPG

「関数の引数」画面で、
配列1:"単価"のセル範囲を指定します。
配列2:"販売数量"のセル範囲を指定します。
20150613_04.JPG

セルの数式はこうなります。
20150613_05.JPG

すると、一発で販売金額の合計が求められました。
20150613_06.JPG

2.抽出したデータの掛け算の合計を求める
次は応用例として、商品区分で抽出した販売金額の合計をもとめてみます。
"菓子"の販売金額を求めてみましょう。
20150613_07.JPG

SUMPRODUCT関数の配列1に、
(E3:E15="菓子")*1
と入力します。
20150613_08.JPG
これは、それぞれの商品区分が"菓子"かどうかを調べています。
等しい場合は"TRUE"(真)、等しくない場合は"FALSE"(偽)が返ります。
それに1を掛けることで、数値に変換されて"TRUE"の場合は"1"、"FALSE"の場合は"0"の配列になります。
これに、配列2(単価)、配列3(販売数量)を掛けていくので、商品区分が"菓子"以外は"0"になり、"菓子"の場合だけ、合計されることになります。

セルの式はこうなります。
20150613_09.JPG

で、菓子の販売金額の合計が求められます。
20150613_10.JPG

3.加重平均を求める
さて、この商品の単価と販売数量のデータから販売単価の平均を求めてみましょう。
実は、このようなデータから求める平均のことを「加重平均」と言います。詳しくはこちらをごらんあれ!

平均単価だからと言って、単価だけを普通の平均つまり算術平均(相加平均と言います)をするとこの13品目の単価を足して13で割ってしまうわけですが、それでは間違いです。
平均単価は販売金額の合計を販売数量の合計で割らなければいけません。
ので、"SUMPRODUCT"で求めた販売金額の合計を"SUM"で求めた販売数量の合計で割らなくてはいけません。
20160501_01.JPG

式はこうなってます。
20160501_02.JPG


関連ページ
関数一覧(カテゴリ別)
関数一覧(名前別)
エラー値について(#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか)
posted by haku1569 at 19:15| Excel関数 | このブログの読者になる | 更新情報をチェックする

2015年05月16日

COLUMN(COLUMNS)関数で列番号を求めてみた〜VLOOKUPへの応用/Excel/関数

Excelの関数で列番号を求めます。
COLUMN関数を使用します。

例えば、セル"B3"にカーソルを置きます。
20150509_001.JPG

「数式」、「関数ライブラリ」、「検索/行列」から「COLUMN」を選択します。
20150509_002.JPG

参照:空白のままにします。
20150509_003.JPG

このような式が入力されます。
20150509_004.JPG

すると"2"と表示されました。
これは"列B"が"列A"から数えて"2"番目だからです。
20150509_005.JPG

次は、セル"B5"にカーソルを置いて、、
20150509_006.JPG

引数に"D5"と入力します。
20150509_007.JPG

すると、"4"と表示されました。
"列D"が4番目だからです。
20150509_008.JPG

次は引数に範囲を設定してみます。
20150509_009.JPG

すると、今度も"4"と表示されました。
引数に範囲を指定した場合は、左端の列番が表示されます。
20150509_010.JPG

COLUMNS関数で範囲の列数を求めてみた
次は"COLUMNS関数"を使ってみます。
20150509_011.JPG

引数はさっきと同じ範囲を設定します。
20150509_012.JPG

このような式が入力されました。
20150509_013.JPG

すると今度は"2"と表示されました。
範囲の列数が"2列"だからですね。
20150509_014.JPG

今度は"COLUMN"を配列変数として使用してみます。
2つのセルを選択します。(横でも縦でもかまいません)
20150509_015.JPG

その状態で"COLUMN関数"を挿入します。
引数は2列の範囲を設定します。
で、「OK」ではなく、「Ctrl」+「Shift」+「Enter」を押します。
20150509_016.JPG

すると、"4"、"5"が表示されました。
引数に指定した列番号がそれぞれ表示されました。
20150509_017.JPG

VLOOKUP関数への応用
上の国語と数学の試験の点数表から、"形野知美"さんと"仲嶋宏美"さんの国語と数学の試験の点数を"VLOOKUP関数"で求めてみます。
20150509_018.JPG

ここでの肝は、2人の点数を求める4つのセルに"COLUMN関数"を用いて1つセルの式をコピーして求めるということです。

形野知美さんの国語のセルにカーソルを置いて"VLOOKUP関数"を挿入します。
20150509_019.JPG

検索値:"形野知美"の入力されているセルを指定します。
右の数学のセルにもコピーするの"列B"は絶対参照の"$"を付けます。
(「F4」キーで参照方式を切り替えられます)
範囲:上の点数表の範囲を、これも絶対参照で指定します。
列番号:ここが肝になるところです。
国語の点数は"2"と指定すればいいわけですが、この式をコピーして数学の点数も求めるので、右のセルにコピーされた時、この列番号は"3"にならなければいけません。
そこで、"COLUMN関数"で列番号を指定します。
関数を入力しようとしているのは"列C"なので列番号は"3"になります。"VLOOKUP"の引数の国語の列番号は"2"なので、"-1"してやります。
20150509_020.JPG

このような式が入力されました。
20150509_021.JPG

で、"73"が表示されました。
20150509_022.JPG

あとは、これをこのままコピーするだけでOKです!
すると、"形野知美"さんと"仲嶋宏美"さんの国語と数学の点数が表示されました!!
20150509_023.JPG


関連ページ
VLOOKUPで列方向の検索をしてみた
ROW関数で行番号を求めてみた

関数一覧(カテゴリ別)
関数一覧(名前別)
posted by haku1569 at 23:24| Excel関数 | このブログの読者になる | 更新情報をチェックする

2015年05月10日

CHOOSE関数で指定した番号の値を求めてみた/Excel/検索関数

Excelの関数で指定した番号の引数に設定した値を求めます。
CHOOSE関数を使用します。

関数の中の引数に1〜256の値等を設定しておき、1〜256の番号(数値)によりそれらの引数の値等を取り出します。

文字列(テキスト)の表示
例えば、番号が"1"の時"Small"、"2"の時"Mediam"、"3"の時"Large"と表示させてみましょう。
20150510_01.JPG

「数式」、「関数ライブラリ」、「検索/行列」より「CHOOSE」を選択します。
20150510_02.JPG

インデックス:指定した番号が入力されているセルを指定します。
値1:インデックスが"1"の時に表示する値を入力します。"Small"を入力します。
テキストの時は""で囲みます。
値2:"Mediam"を入力します。
値3:"Large"を入力します。
20150510_03.JPG

このような、式が入力されました。
20150510_04.JPG

C5まで式をコピーするとこうなりました。
番号1〜3に応じた大きさが表示されました。
20150510_05.JPG

次はインデックスに応じてセルの番号を返します。
これを合計を求める"SUM関数"に組み込んで、合計を計算する範囲をインデックスに応じて変えてみます。
20150510_06.JPG

これで、指定した番号までの合計を自由に求めることができました。
20150510_07.JPG

次は、インデックスに応じて関数の結果を返します。
番号1〜8までの値の平均を"1"、合計を"2"で表示させます。
関数の引数に"AVERAGE関数"、"SUM関数"を入力します。
20150510_08.JPG

と、このように平均と合計を選択して表示させることができます。
20150510_09.JPG

おなじようなことは"VLOOKUP関数"でできますが、参照表を作らずに簡単に引数のリストからの検索表示をさせることができます。
"VLOOKUP関数"と上手な使い分けができれば、たいしたもんです!?


関連ページ
関数一覧(カテゴリ別)
関数一覧(名前別)
エラー値について(#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか)
ラベル:検索関数 Choose
posted by haku1569 at 15:07| Excel関数 | このブログの読者になる | 更新情報をチェックする

2015年05月09日

DATEDIF関数で開始日から終了日までの期間(年数、月数、日数)を求めてみた/Excel/関数

Excelの関数で日にちの期間を求めます。
DATEDIF関数を使用します。

例えば、開始日から終了日までの日数などの期間を求めます。引数の単位を変えることで、年数や月数等も求めることができます。
開始日"2013/3/5"と終了日"2015/5/2"を入力します。この間の期間を求めてみましょう。
20150509_01.JPG

"=datedif()"と入力します。
引数には、開始日、終了日、単位のセルを指定します。
20150509_02.JPG

すると、期間"788"日が求められました。
20150509_03.JPG

単位の種類は、、
日数:d
年:y
月:m
1年未満月数:ym
1月未満日数:md
1年未満日数:yd

が、あります。関数の引数に直接入力する場合は、"d"の様に「"」ダブルクォーテーションで囲みます。
実際に、"2013/3/5"から"2015/5/2"の期間をこれらの単位で求めるとこのようになります。
20150509_04.JPG

それから、こんな使い方も出来ます。
例えば本日付けの年齢を求めることもできます。
開始日は"誕生日"を入力して、第2引数の終了日には"NOW関数"を入力して今日の日付を求めます。
20150509_05.JPG

これで、ファイルを開くか、計算を実行した日の年齢が様々な単位で求めることができます。
20150509_06.JPG


関連ページ
経過時間を「時」、「分」で求めてみた

特集! 時間計算
関数一覧(カテゴリ別)
関数一覧(名前別)
エラー値について(#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか)


今日ホームセンターに行ったら、BOSHの18Vバッテリー工具コーナーがあってPRビデオが流れていた。共通のリチウムイオンバッテリーを様々な工具に使用できる。その中の一つがこのクリーナー。実際に現物をみたけど動かすことはできなかったので吸引力がどのくらいかは分からないが、安物のハンディークリーナーよりはかなりしっかりしるようだった。是非使ってみたい。18Vバッテリーシリーズには電動のこぎりもあるのだけど、今使っている"kio"は軽くていいんだけど、バッテリーのパワー不足で物足りない。。。18Vはかなりハイパワーのようだ(Videoを見る限り、、)ほしい。。。




それと、もうひとつすごいと思ったのがこれ!アウトドアコーナーにあった小型の電動ドライバー。なのだけど、アウトドアコーナーに合った理由は、バーベキューの炭を燃やす送風機のアダプターがあるのだ。さらに、ワインオープナーに、ペッパーミル!!!電動ドライバーのアタッチメントなのだ。。。オモロ

ラベル:DATEDIF 期間 関数
posted by haku1569 at 18:48| Excel関数 | このブログの読者になる | 更新情報をチェックする

2015年04月19日

TRANSPOSE関数で行と列を入れ替えてリンクさせてみた/Excel/検索関数

Excelの関数で行と列のデータを入れ替えてリンクさせます。
TRANSPOSE関数を使用します。

コピー、貼り付けでは「形式を選択して貼り付け」から「行列を入れ替える」で行と列を入れ替えられますが、これはあくまでもコピーしただけなので、元の表と貼り付けを行った行列を入れ替えた表は独立したものになってしまいます。
一方、この"TRANSPOSE関数"で行列を入れ替えた表は関数でリンクされた表ですから、元の表のセルの値を変えると、"TRANSPOSE関数"で行列を入れ替えた表のデータも自動的に更新されます。
20150419_00.jpg

では、さっそく。。。
こんな、テストの点数の一覧表があります。
10行、3列の表になっています。
これを、行列変換するので、3行10列のスペースを予め確保しておきます。
(ここでは分かりやすいように罫線を引いてみました)
20150419_01.JPG

まず最初に、行列を入れ替えるスペースを選択しておきます。
20150419_02.JPG

選択したまま、「数式」、「関数ライブラリ」、「検索/行列」から「TRANSPOSE」を選択します。
20150419_03.JPG

配列:元の表の範囲を選択します。
20150419_04.JPG

で、「OK」しないで、「Ctrl」と「Shift」キーを押しながら「Enter」キーを押します。
すると、一気に行列が変換されたデータが入力されます。
セルの中は、こんな式が入力されています。
式が{}でくくられていますが、これを"配列数式"と言って、複数のセルの計算を行う関数のことを言います。配列数式は「Ctrl」と「Shift」キーを押しながら「Enter」キーを押します。
20150419_05.JPG

なので、元の表の"加藤泰江"さんの数学の点数を"60"に変更すると、変換した表も同時に更新されます。
20150419_06.JPG



関連ページ
関数一覧(カテゴリ別)
関数一覧(名前別)
エラー値について(#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか)
posted by haku1569 at 19:27| Excel関数 | このブログの読者になる | 更新情報をチェックする

2015年04月18日

INDIRECT関数でセルの位置や範囲をテキストで参照させてみた/Excel/検索関数

Excelの関数でセルの位置や範囲をテキストで参照させます。
INDIRECT関数を使用します。

通常、セルの参照は式や関数の中に参照するセルやセル範囲を"A1"、"A1:A3"と入力されますが、それをINDIRECT関数によって、テキスト(文字列)で入力された"A1"、"A1:A3"等をセル参照にすることが出来ます。
20150418_000.jpg

さっそく、具体例で説明しましょう。
こんな、テストの点数の一覧表があります。
20150418_001.JPG

ここで、加藤泰江さんの点数"73"を他のセルで表示(参照)します。
ちなみに、"73"の入力されているセルは"C7"です。
20150418_002.JPG

セルC15に"=C7"と入力(または"="を入力してセルC7を選択)します。
20150418_003.JPG

すると、"C7"の値が参照されて、セルC15に"73"と表示されました。
20150418_004.JPG

同様なことを"INDIRECT関数"をつかってやってみます。
セル"B16"にテキストで"C7"と入力しておきます。
で、"C16"で、「数式」、「関数ライブラリ」、「検索/行列」から「INDIRECT」を選択します。
20150418_005.JPG

参照文字列:"C7"が入力されている"B16"を指定します。
参照形式:省略
20150418_006.JPG

すると、"C7"の"73"が参照されました。
20150418_007.JPG

次は、セルの範囲を参照してみます。
合計を"SUM関数"で求めます。参照範囲は"C4:C12"です。
20150418_008.JPG

合計"564"が求められました。
20150418_009.JPG

セル"B16"にテキストで"C4:C12"と入力しておきます。
その右にSUM関数のセル範囲の代わりに"INDIRECT"で"B16"を参照します。
20150418_010.JPG

と、合計が求められました。
20150418_011.JPG

次は、VLOOKUPでの応用編です。
VLOOKUP関数の参照範囲に範囲名を付けてその"名前"をINDIRECTで参照します。
国語と数学の点数が2つの表に集計されています。
加藤泰江さんの国語の点数をこの表から"VLOOKUP関数"で参照するとこうなります。
20150418_012.JPG

加藤泰江さんの国語の点数"73"が求められました。
20150418_013.JPG

数学の点数を参照したい時は、数学の点数の範囲をVLOOKUP関数で参照しなければなりません。
20150418_014.JPG

いちいち、範囲を設定するのは面倒なので、予め範囲に名前を付けておくことができます。
国語の表の範囲を選択して、数式バーの左隣の「名前ボックス」に"国語"と入力して「Enter」キーを押して確定します。(Enterするのを忘れないでくださいね)
同様に数学の表も"数学"と名前を付けておきます。
20150418_016.JPG

すると、"VLOOKUP関数"で、参照範囲には"国語"と入力するか、範囲を選択すると範囲名の"国語"が入力されます。
20150418_017.JPG

数学も同様に範囲名を指定します。
20150418_018.JPG

これで、加藤泰江さんの国語と数学の点数が、一覧表から参照されました。
20150418_019.JPG

ここで、INDIRECT関数で、VLOOKUP関数の参照範囲の範囲名をセル"B19"の"国語"、セル"B20"の"数学"を参照して範囲名にすることが出来ます。
20150418_020.JPG
20150418_021.JPG

これで、国語、数学の加藤泰江さんの点数が、VLOOKUP関数で面倒な参照範囲を設定しなくても自由に求めることが出来ました。
20150418_022.JPG


応用
2つのドロップダウンリストを連動させて絞り込んだデータを選択させてみた

関連ページ
VLOOKUPで一覧表から列方向の検索結果を求めてみた
INDEX関数で指定した行番号のセルのデータを求めてみた
OFFSET関数で関数の引数の範囲を設定してみた
関数一覧(カテゴリ別)
関数一覧(名前別)
エラー値について(#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか)
posted by haku1569 at 21:30| Excel関数 | このブログの読者になる | 更新情報をチェックする

2015年03月29日

PERMUT関数で順列の数を求めてみた/Excel/統計関数

Excelの関数で順列(Partial Permutation)の数を求めます。
PERMUT関数を使用します。パーミュテーションと読みます。

順列と言うのはn個からr個を取って順番に並べる総数のことで、何通りの並べ方があるかという数です。ちなみに並び順を無視した場合は"組合せ"になります。
20150329-10.jpg

では、10枚のカードから所定の枚数のカードを抜き取って並べた時の順列を求めてみましょう。
20150329_11.JPG

「数式」、「その他の関数」、「統計」から「PERMUT」を選択します。
20150329_12.JPG

標本数:ここでは"カードの数"を入力したセルを指定します。
抜取り数:"並べる枚数"を指定したセルを指定します。
20150329_13.JPG

こんな式が入力されました。
20150329_14.JPG

全てにセルにコピーするとこんなふうになりました。
10枚のカードの並べ方は何と!3,628,800通りもありますよ。
20150329_15.JPG

暇つぶしに、10枚のトランプを並べてみたら面白いかも!?

関連ページ
COMBIN関数で組合せ(抜取り)の数を求めてみた
関数一覧(カテゴリ別)
関数一覧(名前別)
エラー値について(#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか)
posted by haku1569 at 19:21| Excel関数 | このブログの読者になる | 更新情報をチェックする

2015年03月14日

COMBIN関数で組合せ(抜取り)の数を求めてみた/Excel/数学関数

Excelの関数で組合せ(抜取り)の数を求めます。
COMBIN関数を使用します。

組合せと言うのはn個からr個を取る(抜取る)場合、r個の順序は無視した時の総数のことで、何通りの方法があるかという数です。ちなみに順序を無視しない並び順の総数を"順列"と言います。
詳しくはこちらの“確率と正規分布の確率密度について”をどうぞ
20150101_03.jpg

例えば、10回のコイン投げで表の出る時の組合せを求めてみましょう。
ってか、単純にすると、10個からr個(0〜10個)を取る組合せです。
20150314_31.JPG

「数式」「数学/三角」から「COMBIN」を選択します。
20150314_32.JPG

数値:ここでは"10"です。
抜取り数:は表の出る回数を入力したセルを指定します。
20150314_33.JPG

こんな式が入力されました。
20150314_34.JPG

全てにセルにコピーするとこんなふうになりました。
コイン10回投げ表が出る組合せは5回が一番多くなります。
つまり、起きやすいということですね。
ちなみに、合計すると"1024"。つまり、コインを10回投げた時のすべてのパターンは1024通りだということです。
20150314_35.JPG

そのすべての組合せ数で、それぞれの表の出る組合せを割って確率を求めてみると。。。
5回表が出る確率は、24.5%で、、意外に小さいと思いません?
20150314_36.JPG

んで、他にどんな応用があるかというと。。。
10人のメンバーから3人のグループは何通り出来るかとか、今回のようにそれぞれの組合せの合計を求めれば、10色の絵の具で何色の色を作れるかとか、、、


関連ページ
確率と正規分布の確率密度についてまとめてみた
PERMUT関数で順列の数を求めてみた
関数一覧(カテゴリ別)
関数一覧(名前別)
エラー値について(#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか)
ラベル:組合せ COMBIN
posted by haku1569 at 22:27| Excel関数 | このブログの読者になる | 更新情報をチェックする

2015年03月08日

QUARTILE関数で四分位点を求めてみた/Excel/統計関数

Excelの関数で四分位点を求めます。
QUARTILE関数を使用します。

四分位点というのはデータを最小値から最大値まで順に並べて4分割(25%ずつ)していった
第1四分位点(Q1):25%点
第2四分位点(Q2):50%点(中央値)
第3四分位点(Q3):75%点
のことを言います。外れ値があったり、左右に歪んだ分布でも影響が少ないばらつきの代表値として利用されます。
詳しくはコチラをご覧ください。
20150308_01.jpg

8つのデータの四分位点を求めてみます。
20150308_11.JPG

「数式」「その他の関数」「統計」から「QUARTILE」を選択します。
20150308_12.JPG

関数の引数画面が開きます。
配列:データを入力したセルの範囲を指定します。
ここでは、最小値から最大値まで5のセルに式をコピーするので、範囲は"$"を付けて絶対参照にしています。
戻り値は
"0":最小値
"1":第1四分位点
"2":第2四分位点
"3":第3四分位点
"4":最大値
となります。
20150308_13.JPG

入力したセルの式はこうなります。
20150308_14.JPG

んで、結果はこうなります。
20150308_15.JPG

なぜ、四分位点の値がこうなるのかは、、、
"内分点"を求める方法を使用しているためなんですが、、、解説のページをご覧ください。


関連ページ
四分位点、四分位範囲、四分位偏差についてまとめてみた
関数一覧(カテゴリ別)
関数一覧(名前別)
エラー値について(#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか)
posted by haku1569 at 17:52| Excel関数 | このブログの読者になる | 更新情報をチェックする
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。