Excel。NEGBINOMDIST関数は負の二項分布の確率を算出します
<関数辞典:NEGBINOMDIST関数>
NEGBINOMDIST関数
読み方: ネガバイノムディスト
読み方: ネガティブバイノミアルディストリビューション
分類: 互換性
NEGBINOMDIST(失敗数,成功数,成功率)
負の二項分布の確率を算出します
Excel・Word・PowerPoint・Accessのテクニックや研修ネタ・テキストを紹介しております。主に、講義先や現場レベルで質問があったものを中心に書いております。
NEGBINOMDIST関数
読み方: ネガバイノムディスト
読み方: ネガティブバイノミアルディストリビューション
分類: 互換性
NEGBINOMDIST(失敗数,成功数,成功率)
負の二項分布の確率を算出します
Facebookページに書いた、Excelの豆知識(Trivia)です。
6月1日
Excel
範囲選択が基本中の基本ですよね。
6月2日
Excel。
範囲選択。複数のセルを選択するときは、Ctrlキーを押しながらクリックですね。
6月3日
Excel。
範囲選択で、広い範囲を選択するときは、Shiftキーを押しながらクリック技が便利ですね。
6月4日
Excel。
範囲選択の表現は、A1:B5と書きますが、これは、A1~B5までを範囲選択という意味ですね。
6月5日
Excel。
範囲選択で、行を選択する場合は、行番号をクリックすると行選択できますね。
6月6日
Excel。
範囲選択で、列を選択する場合は、列番号をクリックすると列選択できますね。
6月7日
Excel。
範囲選択で、隣接しているセル・行・列は、Ctrlキーを押すのではなく、ドラッグ操作ですね。
Excelは、単純に塗りつぶしたセル数を求められる関数は用意されていませんので、一発で求めることはできません。
どうしても数えたい場合には、オートフィルターをつかって抽出した件数を求めることはできますが、少し面倒です。
そこで、Excel VBAをつかって、指定した色で塗りつぶされているセルだけを求めるプログラム文をつくることにしました。
今回用意した表です。
B5とC4は数えたくないわけです。
そのため、単純に、セルが塗りつぶされているかどうかで、判断するわけにはいきません。
指定されている色を把握する必要があります。
では、Excel VBAのプログラム文を確認してみます。
Sub 指定した色を数える()
Dim kinmuhani As Range
Dim yasumi As Range
Dim Cell As Range
Dim i As Integer
Set kinmuhani = Range("B2:D5")
Set yasumi = Range("A7")
i = 0
For Each Cell In kinmuhani
If Cell.Interior.Color = yasumi.Interior.Color Then
i = i + 1
End If
Next Cell
Range("B7").Value = i
End Sub
まずは、実行してみましょう。
では、プログラム文を確認しておきましょう。
まずは、変数宣言です。
Dim kinmuhani As Range 範囲選択を代入させます
Dim yasumi As Range A7の塗りつぶしの色情報を代入します。
Dim Cell As Range セルをチェックする変数です。
Dim i As Integer 一致するセルの数を格納する変数です。
用意した変数に、指定した範囲選択を代入します。
Set kinmuhani = Range("B2:D5")
Set yasumi = Range("A7")
For Each文で範囲選択内を繰り返して、チェックします。
For Each Cell In kinmuhani
If Cell.Interior.Color = yasumi.Interior.Color Then
i = i + 1
End If
Next Cell
範囲選択内のセルの塗りつぶした色と、A7の塗りつぶした色が同じならば、変数iに1を加算します。
このようなプログラムを用意しておくと、単純な塗りつぶしされているセルを数えるには重宝するかと思います。
NEGBINOM.DIST関数
読み方: ネガバイノム・ディスト
読み方: ネガティブバイノミアル・ディストリビューション
分類: 統計
NEGBINOM.DIST(失敗数,成功数,成功率,関数形式)
負の二項分布の累積確率か確率密度を算出します
データを読み込んでみたところ
アルファベットの元号の列
ドットで区切られた数値の列
この2つの列から、「日付」にするには、どのようにしたら、いいのでしょうか。
色々な方法がありますが、
今回は、「DATEVALUE関数」という関数をつかってみました。
氏名が入力されている3つの表があります。
コピペするのも悪くはありません。
ただし、データ量が増えれば増えるほど、面倒です。
データを1列にまとめるならば、VSTACK関数がおすすめです。
G2に次の数式を設定します。
VSTACK関数は、列結合する関数なので、引数には、結合したい元の列を範囲選択するだけです。
このVSTACK関数は、スピル機能対応の関数なので、オートフィルで数式をコピーは不要です。
簡単に結合することができましたので、重複したデータを除きたいと思います。
重複データを除くには、データタブにある「重複の削除」をつかいたいところですが、スピル機能によって、ゴーストとして表示されています。
データそのものがあるわけではありません。
そのため、データタブの「重複の削除」はつかえません。
ここは、UNIQUE関数をつかうことで、重複データを除くことができます。
数式にUNIQUE関数を追加してみます。
=UNIQUE(VSTACK(A2:A5,C2:C5,E2:E5),FALSE,FALSE)
これで、重複データを除くことができました。
UNIQUE+VSTACK関数のネストです。
修正した数式を確認します。
最初の引数は、配列。範囲なので、VSTACK関数を設定します。
2つ目の引数は、列の比較。FALSEと設定します。
「一意の行」を返すことができます。
3つ目の引数は、回数指定。
FALSEと設定します。
個別のアイテムをすべて返すことができます。
最後に、カタカナなので、五十音順。
つまり昇順にしたいのですが、スピル機能のゴーストである以上、並べ替えをつかうことはできません。
並べ替えの関数であるSORT関数をつかうことで、対応することができます。
数式をさらに修正します。
G2の数式は、SORT+UNIQUE+VSTACK関数のネストになりました。
=SORT(UNIQUE(VSTACK(A2:A5,C2:C5,E2:E5),FALSE,FALSE),1,1,FALSE)
これで、並べ替えを行うことができました。
追加したSORT関数についても確認しておきましょう。
最初の引数は、配列。
範囲選択なので、UNIQUE関数の数式を設定します。
2つ目の引数は、並べ替えインデックス。
何番目の列を基準にするのかということです。
今回は、1列しかありませんので、1と設定します。
3つ目の引数は、並べ替え順序。
1なら昇順で2なら降順です。
五十音順なので1と設定します。
4つ目の引数は、並べ替え基準。
TRUEならば、列で並べ替えすることができますが、行での並べ替えなので、FALSEと設定します。
このように、最近追加された、関数を組み合わせてスピル機能と合わせてつかうことで、並べ替えや重複対応をした表をつくることができるようになりました。
作業効率もUPする、知っていると便利なショートカットキー。
Ctrl+L
テーブルの作成ダイアログ ボックスを表示
Ctrl+N
新規ブックを作成する
Ctrl+O
ブックを開く
Ctrl+P
印刷プレビューを表示
Ctrl+Q
クイック分析 オプションを表示
住所録があるのですが、都道府県順にしたいわけです。
今回は、東京都・神奈川県・埼玉県・千葉県・群馬県・栃木県・茨城県という順番にしたい。
まずは、クエリを使って、都道府県順にしてみます。
作成タブのクエリデザインをつかいます。
クエリは、テーブルの全てのフィールドをつかうことにします。
何かいい方法はないのでしょうか。
そこで、都道府県順のテーブルをつくります。
改めて、クエリをデザインビューで表示します。
確認が終わりましたので、あとは、T関東順番フィールドを非表示にして完成です。
Facebookページに書いた、Excelの豆知識(Trivia)です。
5月25日
Excel。
REGEXEXTRACT関数
読み方: レゲックスエクストラクト
分類: 文字列操作
REGEXEXTRACT(text,pattern,[return_mode],[case_sensitivity])
指定したパターンに一致するテキストの部分文字列を抽出します
5月26日
Excel。
REGEXREPLACE関数
読み方: レゲックスリプレイス
分類: 文字列操作
REGEXREPLACE(text,pattern,replacement,[occurrence],[case_sensitivity])
指定したパターンに一致するテキストの部分文字列を置換文字列に置換します
5月27日
Excel。
REGEXTEST関数
読み方: レゲックステスト
分類: 文字列操作
REGEXTEST(text,pattern,[case_sensitivity]
指定したテキストのいずれかの部分と一致するかどうかをチェックします
5月28日
Excel。
DETECTLANGUAGE関数
読み方: ディテクトランゲージ
分類: 文字列操作
DETECTLANGUAGE(text)
指定したテキストの言語を検出し、言語コードを返します DETECT LANGUAGE の略
5月29日
Excel。
TRANSLATE関数
読み方: トランスレイト
分類: 文字列操作
TRANSLATE(text,[source_language],[target_language])
ユーザーが入力したテキストを取得し、1つの言語から別の言語に翻訳する
5月30日
Excel。
TRIMRANGE関数
読み方: トリムレンジ
分類: 検索/行列
TRIMRANGE(Range,[Row_trim_mode],[Col_trim_mode])
範囲または配列の外側のエッジからすべての空の行と列を除外します。
5月31日
Excel。
Ctrl+Shift+V
値の貼り付け:セルの場合
次の表があります。
A4:D8に店舗別の販売表があります。
この表から、A2に店舗名を入力したら、B2にB1の見出しの売上データを表示したいわけです。
5月売上と固定していれば、次のような数式をB2に設定します。
確かに、5月売上の中野店の販売金額を表引きすることができます。
ただ、VLOOKUP関数の3番目の引数の列番号を「3」と固定してしまうと、4月売上や6月売上の列から表引きすることができません。
B1の見出しを変更したら、それに合わせて、列番号を変更するのは面倒です。]
どうにか、表引きすることはできないものでしょうか。
そこで、列番号にIF関数などの条件分岐する関数をつかってみたらどうなるのでしょうか。
今回は、4月売上・5月売上・6月売上を3列ありますから、IFS関数をつかって、VLOOKUP関数を修正してみます。
では、B1の見出しを6月売上で、A2の店舗名を渋谷に変更してみます。
では、設定したVLOOKUP関数を確認しておきましょう。
=VLOOKUP(A2,A5:D8,IFS(B1="4月売上",2,B1="5月売上",3,TRUE,4),FALSE)
最初の引数は、検索値。
店舗名で表引きをしますので、A2です。
2番目の引数は、範囲。
表なので、A5:D8。見出し行は不要です。
そして、3番目の引数、列番号。
ここは2番目の引数で設定した範囲の左から何列目のデータを表引きするのかという数値を設定します。
4月売上から6月売上まで自由に選択したいわけなので、列番号は2~4となるわけです。
ここで条件分岐ということで、IFS関数をつかってみました。
IFS(B1="4月売上",2,B1="5月売上",3,TRUE,4)
4月売上ならば、2。
5月売上ならば、3
それ以外は、4
と条件分岐するようにしました。
VLOOKUP関数の最後の引数は、完全一致なので、FALSEです。
これで、複数の見出し列に対応した自由度の高い表引きをすることができました。
MUNIT関数
読み方: エムユニット
読み方: マトリック ユニット
分類: 数学/三角
MUNIT(次元)
指定した次元の単位行列を算出する
2行1組の連続した日付を入力したい場合、オートフィルだけでは、入力することができません。
では、どのようにしたら、手早く、2行1組の連続した日付を入力することができるのでしょうか。
名簿のファイルがあります。
ただ、1列何人のデータで移動したらいいのかを求める。
求めた後の移動もデータ量があれば、あるほど面倒になります。
何かいい方法は無いのでしょうか。
そこで、WRAPROWS関数をつかうことで、手早く対応することができます。
では、C2をクリックして、WRAPROWS関数をつかった数式を設定してみましょう。
=WRAPROWS(A2:A13,3,"---")
スピル機能に対応している関数なので、オートフィルで数式をコピーする必要はありません。
これで、1列の表を3列編成にすることができました。
引数を確認しておきましょう。
最初の引数は、vector。
ベクトルです。データの範囲なので、A2:A13と設定します。
2番目の引数は、wrap_count。
折り返し数です。
これは、何列で折り返すのかを設定します。
今回は3列での折り返しなので、「3」と設定します。
最後の引数は、pad_with。
代替値。
なかった場合、どのようにするのかということです。
ない場合は「---」と表示する様にしました。
ちょっとしたことですが、意外と便利な関数ですね。
MULTINOMIAL関数
読み方: マルチノミアル
分類: 数学/三角
MULTINOMIAL(数値1,[数値2],…)
多項係数を算出します 数値の和の階乗と数値の階乗の積との比
Facebookページに書いた、Excelの豆知識(Trivia)です。
5月18日
Excel。
F8
拡張選択モードを有効する
5月19日
Excel。
F9
ブックのすべてのワークシートを再計算する
5月20日
Excel。
F10
リボンにキーボード操作できるキーを表示
Altキーと同じ
5月21日
Excel。
F11
グラフを作成
5月22日
Excel。
F12
名前を付けて保存ダイアログボックスが表示されます
5月23日
Excel。
GROUPBY関数
読み方: グループバイ
分類: 検索/行列
GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array])
行の項目ごとに集計をします。
5月24日
Excel。
PERCENTOF関数
読み方: パーセントオブ
読み方: パーセントオフ
分類: 文字列操作
PERCENTOF(data_subset,data_all)
与えられたデータセットの割合を算出します。
次のような経過時間の表をつくってみたら、結果がおかしなことになっています。
B2には、3.5時間経過した時間が入力されています。
C2には、開始時間に経過時間を足した結果を表示したいので、
=A2+B2
という数式を設定したのですが、結果がおかしなことになっています。
またオートフィルで数式をコピーしたところ、C3も、おかしい結果になっています。
原因は、シリアル値という考え方が抜けていることです。
Excelでは、日付や時間をシリアル値という数値で管理しています。
C2をクリックして、数式タブにある、数式の検証をつかうとよくわかります。
Excelが1日を1としたシリアル値で管理しています。
10時というのは、1/24で1時間なので、1/24*10ということになります。
この値に、単純に3.5を足しても、13.5にはならないことがわかりました。
時間計算は、常にシリアル値を考えないといけないわけです。
つまり、経過時間を次のようにすれば、いいことがわかりました。
確かに、結果は求めることができました。
もし、当初のように、3.5としたままで、結果を求めるには、どのようにしたらいいのでしょうか。
TIME関数をつかって、時間に変更することで対応できます。
=A2+TIME(INT(B2),(B2-INT(B2))*60,0)
B2を数値から時間に変換するので、TIME関数をつかいます。
TIME関数は、時・分・秒という引数が用意されています。
時は、3.5の3なので、小数点を除いた値をつくりたい。
そこで、INT関数で整数化します。
分は、時と逆で、小数点を求めたいので、B2から整数の値を減算すれば、小数点以下を求めたいので、先程の時で求めた値であるINT(B2)をB2から減算すれば、小数点だけが残ります。
残った小数点に60を掛けます。
60かけるのは、分だからです。
最後の引数の秒は不要ですが、無いとエラーになるので、「0(ゼロ)」を設定します。
これで、経過時間を足した終了時間を求めることができます。
MROUND関数
読み方: エムラウンド
分類: 数学/三角
MROUND(数値,倍数)
指定した数値の倍数で四捨五入します
ドーナツグラフは、円グラフと同じように、手早く作ることができます。
では、二重ドーナツグラフは、どのようにしたらいいのでしょうか。
ポイントは、グラフを作るための表。
作り方を間違えると、ドーナツグラフの内円と外円が、思っているようにつくることができません。
そのポイントの説明。
そして、二重ドーナツグラフをつくっていきます。
遠投結果をAさんBさんの二人で、平均値を求めました。
あとから、B5とD5が実は「パス」していたので、「パス」と入力してみました。
先ほどまで、結果は同じだったのですが、トラブルが発生してしまいました。
平均は平均でも、選択した関数が異なっていたのが原因です。
それでは、数式を確認してみましょう。
B9の数式は、
=AVERAGEA(B3:B7)
よくみると、AVERAGE関数ではなくて、AVERAGEA関数です。
このAVERAGEA関数は、範囲内の文字を除くのではなく、「0(ゼロ)」として含めてしまった、平均を算出します。
D9の数式は、
=AVERAGE(D3:D7)
こちらは、お馴染みのAVERAGE関数です。
AVERAGE関数は、範囲選択内の文字は除外して、平均値を求める関数です。
「A」のあるなしで、結果が大きく異なってしまいました。
文字を含めた平均値を算出したいときには、AVERAGEA関数をつかいますが、通常の平均値は、AVERAGE関数でもとめます。
Facebookページに書いた、Excelの豆知識(Trivia)です。
5月11日
Excel。
F2
アクティブなセルを編集する
5月12日
Excel。
F3
[名前の 貼り付け ] ダイアログ ボックスを表示
5月13日
Excel。
F4
直前の動作を繰り返す
5月14日
Excel。
F4
数式作成時
絶対参照・複合参照を設定
5月15日
Excel。
F5
ジャンプダイアログボックスが表示されます
5月16日
Excel。
F6
シート見出しをアクティブにする
5月17日
Excel。
F7
スペルをチェックする。
年間売上がわかるように、集合縦棒グラフをつくってみたものの、わかりにくいので、四半期ごと背景であるプロットエリアを塗り分けたいと考えました。
そこで、グラフのもとになる表を修正します。
1500 という数値は、B列の販売金額の最大値より、ちょっと大きめの数値ということで1500を設定しました。
A1:C10を範囲選択して、ホームタブの「おすすめグラフ」をクリックします。
グラフの挿入ダイアログボックスが表示されます。
すべてのグラフタブにして、組み合わせを選択します。
そのため第2軸をつかいます。
第1軸のままを希望される場合は、グラフの元表のB列とC列をいれかえれば、大丈夫です。
そして、色分けを集合縦棒とします。
OKボタンをクリックします。
縦軸と第2縦軸の目盛を最小値0 最大値1500にします。
縦軸をクリックしたら、書式タブの選択対象の書式設定をクリックして、作業ウィンドウを表示します。
先程表示してあった作業ウィンドウを閉じてしまった場合には、再度選択対象の書式設定をクリックして、作業ウィンドウを表示します。
そうすれば、色分けできるという仕組みです。
凡例の「色分け」だけを選択して削除しておきましょう。
軸の書式設定のラベルにあるラベルの位置を「なし」に変更します。
MODE.SNGL関数
読み方: モード・シングル
分類: 統計
MODE.SNGL(数値1,[数値2],…)
最頻値を算出します