haku1569　Excel でらくらく データ分析！

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

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

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


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


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


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


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


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



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

Excelの関数で配列の積（掛け算）の合計を求めます。
SUMPRODUCT関数を使用します。
"掛け算の合計"って分かり辛いかもしれませんが、いわゆる"重み付けの合計"のことです！？

1．掛け算の合計を求める
2．抽出したデータの掛け算の合計を求める
3．加重平均を求める

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


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


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


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


セルの数式はこうなります。


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


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


SUMPRODUCT関数の配列1に、
(E3:E15="菓子")*1
と入力します。

これは、それぞれの商品区分が"菓子"かどうかを調べています。
等しい場合は"TRUE"（真）、等しくない場合は"FALSE"（偽）が返ります。
それに1を掛けることで、数値に変換されて"TRUE"の場合は"1"、"FALSE"の場合は"0"の配列になります。
これに、配列2（単価）、配列3（販売数量）を掛けていくので、商品区分が"菓子"以外は"0"になり、"菓子"の場合だけ、合計されることになります。

セルの式はこうなります。


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


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

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


式はこうなってます。



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

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

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


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


参照：空白のままにします。


このような式が入力されます。


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


次は、セル"B5"にカーソルを置いて、、


引数に"D5"と入力します。


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


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


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


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


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


このような式が入力されました。


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


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


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


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


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


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

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


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


このような式が入力されました。


で、"73"が表示されました。


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



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

関数一覧（カテゴリ別）
関数一覧（名前別）

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

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

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


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


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


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


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


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


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


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


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


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


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

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

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


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


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


単位の種類は、、

日数：d
年：y
月：m
1年未満月数：ym
1月未満日数：md
1年未満日数：yd

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


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


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



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

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

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

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


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


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


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


配列：元の表の範囲を選択します。


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


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




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

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

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


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


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


セルC15に"=C7"と入力（または"="を入力してセルC7を選択）します。


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


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


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


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


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


合計"564"が求められました。


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


と、合計が求められました。


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


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


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


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


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


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


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


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



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



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

関連ページ
VLOOKUPで一覧表から列方向の検索結果を求めてみた
INDEX関数で指定した行番号のセルのデータを求めてみた
OFFSET関数で関数の引数の範囲を設定してみた
関数一覧（カテゴリ別）
関数一覧（名前別）
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）

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

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


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


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


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


こんな式が入力されました。


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


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

関連ページ
COMBIN関数で組合せ（抜取り）の数を求めてみた
関数一覧（カテゴリ別）
関数一覧（名前別）
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）

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

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


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


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


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


こんな式が入力されました。


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


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


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


関連ページ
確率と正規分布の確率密度についてまとめてみた
PERMUT関数で順列の数を求めてみた
関数一覧（カテゴリ別）
関数一覧（名前別）
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）

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

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


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


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


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


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


んで、結果はこうなります。


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


関連ページ
四分位点、四分位範囲、四分位偏差についてまとめてみた
関数一覧（カテゴリ別）
関数一覧（名前別）
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）