Excel。CUBEKPIMEMBER関数は主要業績評価指標(KPI)を返す関数です【CUBEKPIMEMBER】

Excel。CUBEKPIMEMBER関数は主要業績評価指標(KPI)を返す関数です

＜関数辞典：CUBEKPIMEMBER関数＞

CUBEKPIMEMBER関数

読み方： キューブケーピーアイメンバー　　

分類： キューブ 

CUBEKPIMEMBER(接続,KPI名,KPIのプロパティ,[キャプション])

主要業績評価指標(KPI)を返す　

Excel。2023/1/8-1/14にODD関数など紹介したFacebookページのコメントです。【Trivia】

Excel。2023/1/8-1/14にODD関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

Facebookページに書いた、Excelの豆知識（Trivia）です。

1月8日

Excel。

OCT2DEC関数

読み方は、オクトトゥデックで、8進数を10進数に変換する

1月9日

Excel。

OCT2HEX関数

読み方は、オクトトゥヘックスで、8進数を16進数に変換する

1月10日

Excel。

ODD関数

読み方は、オッドで、数値を奇数に切り上げる

1月11日

Excel。

ODDFPRICE関数

読み方は、オッドエフプライスで、最初の利払期間が半端な利付債の現在価格を算出します。

1月12日

Excel。

ODDFYIELD関数

読み方は、オッドエフフィールドで、最初の利払期間が半端な利付債の利回りを算出

1月13日

Excel。

ODDLPRICE関数

読み方は、オッドエルプライスで、最後の利払期間が半端な利付債の現在価格を算出

1月14日

Excel。

ODDLYIELD関数

読み方は、オッドエルフィールドで、最後の利払期間が半端な利付債の利回りを算出

Access。クエリ。期間外のデータを手早く抽出するにはどうしたらいいの。【extract】

Access。クエリ。期間外のデータを手早く抽出するにはどうしたらいいの。

＜Not Between And演算子＞

1月～3月のデータから2月以外のデータを抽出したい場合、どのようにしたら手早く抽出することができるのでしょうか。

期間を抽出するときにつかう演算子に、Between And演算子というのがあります。

期間外を抽出するときには、このBetween And演算子に「～でない」という演算子の「Not」を追加した「Not Between And演算子」をつかえば、期間外のデータを対象にすることができます。

作成タブのクエリデザインをクリックします。

必要なフィールドを設定します。

抽出条件に、2月以外のデータを抽出する条件を設定します。

Not Between #2022/02/01# And #2022/02/28#

これで実行すれば、2月以外のデータを抽出することができます。

条件を増やしたりして、抽出条件を煩雑にするよりかは、Not Between And演算子をつかうほうが、手早く抽出することができますね。

Excel。数値の双曲線余割を算出するのがCSCH関数です。【CSCH】

Excel。数値の双曲線余割を算出するのがCSCH関数です。

＜関数辞典：CSCH関数＞

CSCH関数

読み方： ハイパーポリック コセカント　　

分類： 数学/三角 

CSCH(数値)

数値の双曲線余割を算出します

Excel。条件が複雑になった最小値の抽出は、DMIN関数をつかってみるといいかも。【minimum】

Excel。条件が複雑になった最小値の抽出は、DMIN関数をつかってみるといいかも。

＜DMIN関数＞

データから最小値を抽出するには、MIN関数。

条件がつくようならば、MINIFS関数がExcelには用意されています。

同じ列内で条件となる場合、つまりOR条件は、MINIFS関数でも、ちょっと抽出するのが、面倒になります。

そこで、DMIN関数をつかってみるといいかもしれませんね。

クラスが、AまたはCのポイント最低点は何点かを算出する場合です。

C11に設定した数式は、

=DMIN(A1:C8,C1,A10:A12)

これで、抽出することができました。

データベース関数は、条件を事前に用意しておく必要があります。

A10:A12が条件ですね。

DMIN関数の引数を確認しておきましょう。

DMIN(データベース,フィールド,条件)

データベースは、表のことなので、A1:C8を設定します。

抽出条件の列は、ポイントなので、見出しのC1を設定します。

条件は、A10:A12です。

Excel。条件の数式で、時刻で使うと正しい結果を表示しないのでどうしたらいい。【Times of Day】

Excel。条件の数式で、時刻で使うと正しい結果を表示しないのでどうしたらいい。

＜TIMEVALUE関数＞

時刻や時間がある帳票で、判断などするために、例えばIF関数を使おうとした場合、TIMEVALUE関数をつかわないと、効率的に算出することができません。

C列の時間が、1時間以内ならば、「○」。

そうでなければ「×」と算出したいので、IF関数をつかいます。

D2に設定した数式は、

=IF(C2<=TIMEVALUE("1:00"),"○","×")

C2の時間が、1時間以内なのかを判断させるために、「TIMEVALUE("1:00")」とTIMEVALUE関数をつかっています。

TIMEVALUE関数は、文字で表示された時刻をシリアル値（日付時刻型）に変えることができる関数です。

なぜ、TIMEVALUE関数をつかうのかというと、「1:00」と設定すると、文字列として判断されてしまうので、思っているように判断してくれません。

例えば、

D2の数式を、

IF(C2<=1:00,"○","×")

というように、変更したらどうなるかというと、エラーが表示されて算出できません。

また、D2の数式を、

=IF(C2<="1:00","○","×")

と「”（ダブルコーテーション）」で囲ってみると、算出はされますが、不正解が算出されてしまいます。

C2が1時間をオーバーしているにも関わらず、判定結果が「○」ということで、1時間以内の判定になっています。

このように、IF関数などで、時刻や時間をつかった、数式を設定する場合には、「型」ということも含めて、考えないと上手くいかないかもしれません。

Excel。CSC関数で角度の余割を算出できます。【CSC】

Excel。CSC関数で角度の余割を算出できます。

＜関数辞典：CSC関数＞

CSC関数

読み方： コセカント　　

分類： 数学/三角 

CSC(数値)

角度の余割を算出する

Excel。OR条件の最大値はMAXIFS関数ではなくてDMAX関数をつかいます。【max at OR】

Excel。OR条件の最大値はMAXIFS関数ではなくてDMAX関数をつかいます。

＜DMAX関数＞

条件付きの最大値を算出するには、MAXIFS関数をつかうと手早く算出することができます。

ただ、次のような場合は、MAXIFS関数では対応できません。

クラスがAまたはCの最高ポイントを算出したいわけです。

このような「または」という条件なので、「OR条件」となるわけですが、OR条件をつかってMAXIFS関数を使うことはできません。

そこで、DMAX関数をつかうことで、手早く算出することができます。

C11には、次の数式を設定しています。

=DMAX(A1:C8,C1,A10:A12)

これで、AまたはCの最大値を算出することができました。

DMAX関数の引数を確認しておきましょう。

最初の引数は、データベース。要するに表ですね。

なので、A1:C8を設定します。なお、見出し行も含める必要があります。

2つ目の引数は、フィールドですね。抽出したい見出し名になりますので、C1のポイントです。

最後の引数は、条件です。A10:A12を設定します。

なお、DMAX関数をはじめとする、D系のデータベース系関数は、条件を別のところに用意する必要があります。

Excel。VLOOKUP関数で先頭の複数文字と合致するデータを抽出したい【first character】

Excel。VLOOKUP関数で先頭の複数文字と合致するデータを抽出したい

＜VLOOKUP＋LEFT+FIND関数＞

別表から合致するデータを検索し抽出することができる「VLOOKUP関数」。

例えば、商品コードと合致する商品名や単価などを検索し抽出することはできますが、次のような、先頭からハイフンまでの複数文字を別表から抽出するにはどのようにしたらいいのでしょうか。

VLOOKUP関数は、4番目の引数の「検索方法」を近似値か完全一致でなければなりません。

講座番号のハイフンまでの左の文字列と合致するものという場合には、VLOOKUP関数だけでは対応することができません。

そこで、B2の数式は、ちょっと長くなりますが、次のように設定することで、対応することができます。

=VLOOKUP(LEFT(A2,FIND("-",A2)-1),$A$8:$B$10,2,FALSE)

あとは、オートフィルで数式をコピーします。

完全一致は完全一致ではあるのですが、ハイフンまでというのを、どう表現すればいいのかを考えていきます。

VLOOKUP関数の最初の引数「検索値」をハイフンより前の文字列としてあげればいいわけです。

そこで、検索値には、LEFT関数とFIND関数をつかうことで、ハイフンよりも前の文字列を抽出することができます。

「LEFT(A2,FIND("-",A2)-1)」

ハイフンの位置が文字列の何文字目にあるのかを、算出すればいいわけなので、FIND関数をつかって、ハイフンの文字位置を確認します。

「EX-001」の場合は、「3」と算出されます。

ただし、この「3」のままLEFT関数で使ってしまうと、左から3文字目までを抽出ということになり「EX-」となってしまうので、検索することができません。

そこで、「-1」しているというわけです。

これで、「EX」というハイフンより前の文字列を抽出することができましたので、あとは、VLOOKUP関数を設定するだけで完成します。

なお、今回は、ハイフンで区切られていたので、ハイフンより左側の文字列を抽出するというルールがありましたが、区切られているルールがない場合は、この数式をつかうことはできません。

Excel。累計二項分布が基準値以上になる最小値には、CRITBINOM関数【CRITBINOM】

Excel。累計二項分布が基準値以上になる最小値には、CRITBINOM関数

＜関数辞典：CRITBINOM関数＞

CRITBINOM関数

読み方： クリテリアバイノム　　

読み方： クライテリアバイノミアル

分類： 互換性 

CRITBINOM(試行回数,成功率,α)

累計二項分布が基準値以上になる最小値を算出します

Excelの様々な関数の読み方や引数などを紹介。今回は、STDEV関数～STDEVPA関数です。【dictionary】

Excelの様々な関数の読み方や引数などを紹介。今回は、STDEV関数～STDEVPA関数です。

＜Excel関数辞典：VOL.75＞

今回は、STDEV関数～STDEVPA関数までをご紹介しております。

STDEV関数

読み方： スタンダードディブ　　

読み方： スタンダードディビエーション

分類： 互換性 

STDEV(数値1,[数値2],…)

数値群を標本データとみなした標準偏差を算出する　

STDEV.P関数

読み方： スタンダードディブ・ピー　　

読み方： スタンダードディビエーション・ピー

分類： 統計 

STDEV.P(数値1,[数値2],…)

数値群を母集団全体とみなした標準偏差を算出する

Pはポピュレーション

STDEV.S関数

読み方： スタンダードディブ・エス　　
読み方： スタンダードディビエーション・エス

分類： 統計 

STDEV.S(数値1,[数値2],…)

数値群を標本データとみなした標準偏差を算出する

Sはサンプル

STDEVA関数

読み方： スタンダードディブエー　

読み方： スタンダードディビエーションエー

分類： 統計 

STDEVA([値1,[値2],…)

全データを標本データとみなした標準偏差を算出する　

STDEVP関数

読み方： スタンダードディブピー　

読み方： スタンダードディビエーションピー

分類： 互換性 

STDEVP(数値1,[数値2],…)

数値群を母集団全体とみなした標準偏差を算出する　

STDEVPA関数

読み方：スタンダードディブピーエー

読み方：スタンダードディビエーションピーエー

分類： 統計 

STDEVPA([値1,[値2],…)

全データを母集団全体とみなした標準偏差を算出する

Excel。新しく追加されたIMAGE関数はネット上の画像をセルに表示します【new function】

Excel。新しく追加されたIMAGE関数はネット上の画像をセルに表示します

＜IMAGE関数＞

新しく追加されたIMAGE関数。

珍しく画像関係の関数が追加されました。

ネット上にある画像をセル内に表示することができる関数です。

簡単に使い方を紹介します。

このBLOG内にある、画像を使ってみることにします。

ちなみに、URLは、

https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiIVibHv5OS-S3TyEn6IB-RGqSsVE-AFrA362bSI6TaNSu6dhIPLtJD9sWZzhAkJM2YFgPCSi3h63otJX4Qs_EMAXWdGJ2cP0UKDlrIyx0v7mjnrn6uN1_C3jAi_RAG8IuctBiikW5bfyNC37nkzbE8mq9Voz1_-ZFXBQtuQAMkO95dNpYSbnlLaQzJ/s756/001.jpg

ExcelにURLと代替テキストを用意します。

B1に画像のURLを貼り付けています。

B2には、代替テキストを入力しています。

画像が大きいので、別シートのA1を大きくした状態で、IMAGE関数を設定します。

A1に設定した数式は、

=IMAGE(IMAGE!B1,IMAGE!B2,0)

※シート名：IMAGE

3つ目の引数のサイズは、０～3まで設定することで、用途に合わせてセル内に表示することができます。

今回は、セル内に収めて縦横比をキープしたかったので、「0」を選びました。

なお、1は、アスペクト比を無視して画像でセルを塗りつぶすことができます。

これで、ネット上にある画像をセル内に表示することができました。

今までだったら、この画像をピクチャとかのフォルダに保存して、挿入から画像を選択して挿入するという作業でしたが、IMAGE関数をつかうことで、ネット上の画像は簡単に表示することができるようになりました。

ただ、セル内なので、自由には、移動させることができません。

それと、ネット上の画像でないといけません。
PC上のデータが出来ると、もっと嬉しいんですけどね。

最後に、IMAGE関数の引数などの情報も載せておきます。

IMAGE関数

読み方： イメージ

分類： Web 

IMAGE(ソース,[代替テキスト],[サイズ],[高さ],[幅])

ソースの場所からセルに画像を代替テキストとともに挿入できる。

ソースはhttpプロトコルを使用なので、ネット上にあるデータでないと表示することができません。

またセル内に表示することになります。

Excel。2組のデータの共分散を算出するのがCOVARIANCE.S関数です。【COVARIANCE.S】

Excel。2組のデータの共分散を算出するのがCOVARIANCE.S関数です。

＜関数辞典：COVARIANCE.S関数＞

COVARIANCE.S関数

読み方： コバリアンス・エス　　

分類： 統計 

COVARIANCE.S(配列1,配列2)

2組のデータの共分散を算出します

Excel。読み込んだデータの△10を-10に変更するには、置換で十分です。【replacement】

Excel。読み込んだデータの△10を-10に変更するには、置換で十分です。

＜置換＞

Excelには様々な機能が用意されていることもあり、色々難しく考えがちになることがあります。

例えば次のようなケース。

データを読み込んでみたら、B2は、「-（マイナス）」表記ではなく、「△」をつかったマイナス表記だったわけですね。

これだと、C2に単純な加算の数式「=A2+B2」を用意していると、「#VALUE!」というエラーが表示されてしまいます。

要するに、B2は数値ではなく「文字」という認識になってしまっているわけです。

このような場合、SUBSTITUTE関数やREPLACE関数で対応しようか、あるいは、フラッシュフィルとか使えるのかなど、色々考えてしまいます。

このような場合、単純に「置換」すれば、文字を数値にすることができます。

ホームタブの「検索と選択」にある「置換」をクリックして、検索と置換ダイアログボックスを表示して、「△」を「-（マイナス）」にするように設定すればいいだけです。

Excel。文字型になっている日付を日付型として使いたいけど、どうしたらいい【DATE】

Excel。文字型になっている日付を日付型として使いたいけど、どうしたらいい

＜LEFT＋LEN関数・表示形式＞

データを読み込んでみたら、日付がおかしい。

どうおかしいのかというと、文字（文字型）になっているわけです。

これでは、Excelの様々な機能を有効につかうことができません。

A列の日付は、日付はシリアル値なので数値なので、日付は右揃えになっているはずですが、左揃えになっています。

また、「=A2+1」と数式をつくってみたところ、「#VALUE!」というエラーが表示されました。

つまり、「日付」ではなくて「文字」として認識されているわけです。

このデータの原因は、「曜日」です。

曜日が付いてなければ、日付として認識されます。

まずは、対応方法をご紹介します。

色々な方法がありますが、比較的シンプルな方法をご紹介します。

C2に次の数式を設定し、オートフィルで数式をコピーします。

=LEFT(A2,LEN(A2)-3)*1

そのあと、C2:C3を範囲選択して、表示形式をホームタブの数値の書式にある「短い日付形式」に変更します。

これで、日付型にすることができました。

テキストファイルでもCSVでも曜日をつけたいなら、日付と分けてほしいところです。

C2に設定した数式の説明をしておきます。

=LEFT(A2,LEN(A2)-3)*1

なぜ、DATE関数ではなくて、LEFT関数をつかっているのか。

日付にするには、DATE関数をつかうというのがスタンダートなのですが、年と月と日をどうやったら抽出することができるのかを考えると、月と日をどのように抽出するのかというのが、問題になります。

例えば、2月の場合MID関数で、6文字目から1文字とすれば、抽出できますが、11月の場合だと、2文字抽出するように数式を設定しなければいけません。

同じように、日も、月によっては、何文字目から抽出したらいいのかも、異なるので、複雑な数式を用意する必要があります。

そもそも、曜日が邪魔なだけなので、曜日をカットした状態をつくればいいわけです。

そこで、LEFT関数をつかうわけです。

LEN関数をつかって、セル内の文字数を算出させて、曜日の「（曜日）」の3文字分の減算した値を左から抽出することで、文字数値型になります。

文字型を数値型にするVALUE関数をつかってもいいのですが、ネストだらけになってしまうので、VALUE関数をつかわずに、文字数値型に「×1」することで、数値型にすることができます。

また、算出結果に、TEXT関数で、年月日の短い日付形式をネストすると、また文字数値型に戻ってしまうので、表示形式をつかいました。

簡単に思われる処理も色々面倒だったりしますので、Excelにインポートする前に解決できるものは解決しておきたいところですね。

Excel。COVARIANCE.P関数なら2組のデータの母共分散を算出できます。【COVARIANCE.P】

Excel。COVARIANCE.P関数なら2組のデータの母共分散を算出できます。

＜関数辞典：COVARIANCE.P関数＞

COVARIANCE.P関数

読み方： コバリアンス・ピー　　

分類： 統計 

COVARIANCE.P(配列1,配列2)

2組のデータの母共分散を算出する

Excel。2023/1/1-1/7にNOW関数など紹介したFacebookページのコメントです。【Trivia】

Excel。2023/1/1-1/7にNOW関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

Facebookページに書いた、Excelの豆知識（Trivia）です。

1月1日

Excel。

NOT関数

読み方は、ノットで、指定した条件が成立しないことを調べる

1月2日

Excel。

NOW関数

読み方は、ナウで、現在の日付と時刻を算出します。

1月3日

Excel。

NPER関数

読み方は、エヌパー：ナンバー・オブ・ピリオドで、元利均等返済における支払回数を算出します。

1月4日

Excel。

NPV関数

読み方は、エヌピーヴイ： ネット・プレズント・バリューで、キャッシュフローに基づいた正味現在価値を算出します。

1月5日

Excel。

NUMBERSTRING関数

読み方は、ナンバーストリングで、数値を漢数字に変換する。

1月6日

Excel。

NUMBERVALUE関数

読み方は、ナンバーバリューで、特定の地域に依存しない方法で文字列を数値に変換する

1月7日

Excel。

OCT2BIN関数

読み方は、オクトトゥビンで、8進数を2進数に変換する

Access。クエリ。10代ごとの度数分布表を手早く作成するにはどうしたらいいの【frequency】

Access。クエリ。10代ごとの度数分布表を手早く作成するにはどうしたらいいの

＜Access：Partition関数＞

AccessのテーブルやクエリをExcelにエクスポートして、Excelで度数分布表を作成してもいいのですが、Accessで度数分布表がつくれるなら、Excelにエクスポートしないで済むので手早く作成することができます。

では、どのようにしたらいいのでしょうか？

次のテーブルをつかって、10歳ごとの年代別度数分布表をクエリで作成していきます。

作成タブのクエリデザインをクリックします。

演算フィールドを設定してきます。

10歳代ごとに、分けたいので、

年代別: Partition([年齢],10,90,10)

という演算フィールドを設定します。

Partition関数は、区分けするにはもってこいの、とても便利な関数です。

最初の引数はフィールド名で、今回は年齢ごとにわけるので、「年齢」を設定します。

2つ目の引数は、最初の値。10歳から対象とするので、「10」としました。

3つ目の引数は、最後の値です。最高齢は90歳かなぁ～ということで、「90」としました。

最後の引数は、区分。つまり間隔です。10歳ごとに件数を知りたいので、「10」とします。

たったこれだけです。Excelにもほしい関数ですね。

そして、集計行を表示します。

クエリデザインの「集計」をクリックすることで、集計行を表示することができますので、件数を算出したいフィールドの集計に「カウント」と設定します。

これで完成です。

Excelで、度数分布表をつくるよりも、AccessのPartition関数をつかったほうが手早く作成することができますので、機会があれば、Partition関数をつかってみると、色々代用することができるかもしれませんね。

Excel。DGET関数は単一条件で、該当件数が1件だけなら抽出できる関数です。【single condition】

Excel。DGET関数は単一条件で、該当件数が1件だけなら抽出できる関数です。

＜DGET関数＞

DSUM関数など「D」から始まる、データベース系関数。

そのグループにDGET関数というのがあって、表から該当するデータを抽出することができるのですが、イマイチというか。

データベース系関数の共通点として、条件の表を用意する必要があります。

A10:A11が条件の表に当たります。

順位が3のデータの氏名が誰なのか抽出するために、DGET関数を使います。

B11の数式は、

=DGET(A1:C8,A1,A10:A11)

関数の構造を説明します。

最初の引数は、「データベース」。

つまり表なので、見出し行を含めて範囲選択をします。「A1:C8」と設定します。

2つ目の引数は、「フィールド」です。

最初の引数で選択した範囲である表から抽出したいデータの行タイトルであるフィールドを設定します。

氏名を抽出したいので、「A1」ですね。

最後の引数は、「条件」です。順位が3ということですから、「A10:A11」と設定します。

わかりやすい関数ではあるのですが、使い勝手がイマイチ。

まず、最後の引数の条件が、クラスがAで順位が3のような複数条件だと算出できません。

また、合致するデータが複数あると「#NUM!」というエラーが表示されてしまいます。

Excel。2組のデータの母共分散を算出するならCOVAR関数【COVAR】

Excel。2組のデータの母共分散を算出するならCOVAR関数

＜関数辞典：COVAR関数＞

COVAR関数

読み方： コバリアンス　　

分類： 互換性 

COVAR(配列1,配列2)

2組のデータの母共分散を算出します

Excel。項目別集計なら「統合」をつかえば手早く集計できます。【integration】

Excel。項目別集計なら「統合」をつかえば手早く集計できます。

＜統合＞

項目別で集計をするとしたら、SUMIF関数や「小計」。

あるいは「ピボットテーブル」というのが定石ですが、実は連続しているフィールドであれば、「統合」をつかうと手早く算出することができます。

まずは、どのようにしたらいいのか、手順を説明しますが、集計方法は、「統合」をすればいいだけです。

算出したい、E1をクリックして、データタブの「統合」をクリックします。

統合の設定ダイアログボックスが表示されますので、「集計の方法」が合計になっているのを確認したら、統合元範囲に、項目別集計をしたい範囲を設定します。

B1:C8を設定したら、追加ボタンをクリックします。

統合の基準は、「上端行」「左端列」の両方ともがオンになっているのを確認したら、OKボタンをクリックして完成です。

あとは、見出し行を整えてあげます。

「統合」をつかうことで、簡単に項目別集計をおこなうことができます。

そもそも「統合」は、3-D集計を行いたくても、レイアウトが異なっているので、出来ない時に「統合」という流れになっているのですが、別に、複数シートでなくても、統合を行うことができるわけですね。

この方法を知っていると、「小計」。

あるいは、「ピボットテーブル」で処理をすることもなく、簡単に項目別集計を算出することができます。

ただし、隣接したフィールドならば、統合できますが、離れたフィールドではできません。

今まで機会がなかった機能をアレンジしてみると、意外な発見があるかもしれませんね。

Excel。COUPPCD関数をつかえば購入日より前の直近の利払日がわかります。【COUPPCD関数】

Excel。COUPPCD関数をつかえば購入日より前の直近の利払日がわかります。

＜関数辞典：COUPPCD関数＞

COUPPCD関数

読み方： クーポンピーシーディー

分類： 財務 

COUPPCD(受渡日,満期日,頻度,[基準])

購入日より前の直近の利払日を算出します

COUPon, Previous Coupon Dateの略

Excel。2022/12/25-12/31にNORM.DIST関数など紹介したFacebookページのコメントです。【Trivia】

Excel。2022/12/25-12/31にNORM.DIST関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

Facebookページに書いた、Excelの豆知識（Trivia）です。

12月25日

Excel。

NORM.DIST関数

読み方は、ノーマル・ディストで、平均と標準偏差に対する正規分布の確率を算出します。

12月26日

Excel。

NORMINV関数

読み方は、ノーマルインヴァースで、累積確立から正規分布の数値を逆算する

12月27日

Excel。

NORM.INV関数

読み方は、ノーマル・インヴァースで、累積確立から正規分布の数値を逆算する

12月28日

Excel。

NORMSDIST関数

読み方は、ノーマルスタンダードディストで、標準正規分布の累積確率を算出します。

12月29日

Excel。

NORM.S.DIST関数

読み方は、ノーマル・スタンダード・ディストで、標準正規分布の累積確率を算出します。

12月30日

Excel。

NORMSINV関数

読み方は、ノーマルスタンダードインヴァースで、累積確立から標準正規の数値を逆算する

12月31日

Excel。

NORM.S.INV関数

読み方は、ノーマル・スタンダード・インヴァースで、累積確立から標準正規の数値を逆算する。

Excel。VBA。読み込んだデータのふりがなを設定し列に表示したい【Furigana】

Excel。VBA。読み込んだデータのふりがなを設定し列に表示したい

＜Excel VBA: vbHiragana＞

データを読み込んだら、ふりがなの列がないので、作ることしました。

Phonetic関数を使ってふりがなを表示してもいいのですが、データを読み込むたびに行うのは、ちょっと面倒です。

また、Phonetic関数という数式で表示してしまうと、ふりがなの修正が必要になると、これも、面倒な作業が発生します。

そこで、Excel VBAでプログラム文を作成して対応することにします。

Sub ふりがな()

    Dim i As Integer

    Dim lastrow As Long

    

    lastrow = Cells(Rows.Count, "a").End(xlUp).Row

    

    Range("b1").CurrentRegion.Offset(1, 0).SetPhonetic

   

    For i = 2 To lastrow

        Range("c" & i) = Range("b" & i).Phonetic.Text

        Range("c" & i).Value = StrConv(Range("c" & i).Value, vbHiragana)

    Next

End Sub

これで、C列にB列のデータのふりがなを表示することができます。

それでは、プログラム文を確認します。

お馴染みの変数宣言です。

Dim i As Integer

Dim lastrow As Long

lastrow = Cells(Rows.Count, "a").End(xlUp).Row

変数のlastrowは繰り返し作業のために何回繰り返し作業をすればいいのかを、算出して設定しています。

Range("b1").CurrentRegion.Offset(1, 0).SetPhonetic

B1から連なるデータにフリガナを設定しています。

Excelのふりがな情報は、Excelに直接入力しないと入力時文字情報は記録されません。

要するに、Excel以外からデータをインポートや、コピーだと、ふりがな情報は無いというわけです。

そのため、ふりがな情報を強制的に設定する必要があるわけです。

For i = 2 To lastrow

    Range("c" & i) = Range("b" & i).Phonetic.Text

    Range("c" & i).Value = StrConv(Range("c" & i).Value, vbHiragana)

Next

For To Nextで繰り返し処理をしております。

Range("c" & i) = Range("b" & i).Phonetic.Text

この行で、C列にフリガナ情報を表示します。

ただ、表示すると「カタカナ」になっています。

今回は、見出し行が「ひらがな」になっていますので、次の行で、カタカナをひらがなに変換しています。

Range("c" & i).Value = StrConv(Range("c" & i).Value, vbHiragana)

Excel。購入日後の利払回数を算出できるのがCOUPNUM関数です。【COUPNUM】

Excel。購入日後の利払回数を算出できるのがCOUPNUM関数です。

＜関数辞典：COUPNUM関数＞

COUPNUM関数

読み方： クーポンナンバー

分類： 財務 

COUPNUM(受渡日,満期日,頻度,[基準])

購入日後の利払回数を算出します　

Excel。時刻が24時以降の表示ができないので、どうしたらいいの【After 24:00】

Excel。時刻が24時以降の表示ができないので、どうしたらいいの

＜表示形式＞

時間計算をすると、不思議なことが発生して困ることがあります。

例えば、時給計算。

D5は、3日分の勤務時間の合計値をSUM関数で、算出していますが、「3:00」と表示されています。

9時間×3日なので、「27:00」と表示されるはずです。

対応するには、表示形式をつかって24時以上の表示ができるようにします。

「h:mm」の「h」に大カッコをつけて、「[h]:mm」とすることで、24時以上の表示にすることができます。

なぜ、表示形式を変更しないといけないのかというと、24時を越えると翌日になってしまうからです。

27時間は24時間+3時間というわけです。

Excel。オートフィルターをつかわずに、該当するデータを別のセルに表示したい【Extract】

Excel。オートフィルターをつかわずに、該当するデータを別のセルに表示したい

＜IF＋ROW関数　IFERROR＋INDEX＋SMALL＋ROW＋COLUMN関数＞

データベースや表から、該当する条件のデータを抽出して、別のセルに表示したい場合、オートフィルターでデータを抽出して、コピーをする。

この作業でもいいのですが、抽出条件が変わるとなると、オートフィルターで抽出してコピーするという作業が面倒になってきます。

そこで、数式だけで、対応することもできます。

A1:C8にデータがあって、G1のクラス名に合致するデータを、F4を起点として抽出表示したいわけです。

G1を「B」にしたら、クラスBのメンバーが抽出され表示するというものです。

最初は準備として、D2に次の数式を設定し、D8までオートフィルで数式をコピーします。

=IF(B2=$G$1,ROW()-1,"")

B列のデータと、G1のデータが合致したら、行番号-1を算出するという数式ですね。

ROW関数は行番号を算出します。

「-1」するのは、あとでINDEX関数をつかうのですが、座標がわかりやすくするために、「1」からの値にしたいからです。

今回は、データの1件目が2行目にありますので、「-1」すれば、「1」から表示することができます。

F4にメインとなる数式を設定します。

設定したら、オートフィルで数式をコピーします。

=IFERROR(INDEX($A$2:$C$8,SMALL($D$2:$D$8,ROW(A1)),COLUMN(A1)),"")

長い数式ですが、これだけで、該当するデータを抽出し表示することができます。

F4:H8まで数式を設定してあります。

それでは、数式を確認していきます。

最初のIFERROR関数ですが、データの件数が少ない場合、「#NUM!」というエラーが表示されてしまうので、その防止のためにIFERROR関数をつかっています。

INDEX関数は、設定した範囲の中から、指定した行番号と列番号のデータを抽出するという関数です。

最初の引数は、「配列」です。

「$A$2:$C$8」と設定します。

オートフィルで数式をコピーしますので、絶対参照を忘れないようにしましょう。

2番目の引数は、「行番号」です。

SMALL($D$2:$D$8,ROW(A1))

先程、IF関数で算出した結果を、SMALL関数を使い、1番小さい値を抽出させます。

1番小さい値、次に2番目の小さい値というようにしたいので、SMALL関数をつかい、さらに、ROW関数でA1を指定します。

オートフィルで数式をコピーすると、A1がA2とかわり、2番目・3番目に小さい値のある場所を見つけてくれるという仕組みです。

最後の引数は、「列番号」です。

COLUMN(A1)

こちらも、先程と同じ仕組みで、オートフィルで数式をコピーするときに、座標がズレるようにしています。

このように、INDEX関数をつかうことで、データを抽出することもできます。

Excel。偏差平方和（変動）を算出するなら、DEVSQ関数で手早く算出できます。【Fluctuation】

Excel。偏差平方和（変動）を算出するなら、DEVSQ関数で手早く算出できます。

＜DEVSQ関数＞

データの平均値に対するズレの平均値である。

「変動」。

つまり偏差平方和を算出するならば、DEVSQ関数をつかえば、手早く算出することができます。

B8には、つぎの数式を設定してあります。

=DEVSQ(B2:B6)

DEVSQ関数（読み方： ディブスクウェアまたは、ディビエーションスクエア）をつかうと、平均からの差を2乗した値をデータ分合算する必要もなく、範囲選択するだけで算出することができました。

なんで2乗するのかというと、「＋」「－」という符号の影響をなくし、ばらつき具合をわかるようにするためですね。

算出された値は、データのばらつき具合である分散を考えるときに活用されます。

重さ1と重さ2の平均値は同じですが、変動は、重さ1が5.2。

重さ2が103.2と数値が異なっています。

よく見ると、重さ2のほうが、データにばらつきがあることがわかりますね。

Excel。COUPNCD関数をつかうと購入日後の最初の利払日を算出できます【COUPNCD】

Excel。COUPNCD関数をつかうと購入日後の最初の利払日を算出できます

＜関数辞典：COUPNCD関数＞

COUPNCD関数

読み方： クーポンエヌシーディー　　

分類： 財務 

COUPNCD(受渡日,満期日,頻度,[基準])

購入日後の最初の利払日を算出します

COUPon, Next Coupon Dateの略

Excel。IF関数もつかわずに、手早く締め日から支払日を算出したい。【Closing date】

Excel。IF関数もつかわずに、手早く締め日から支払日を算出したい。

＜DATE・YEAR・MONTH・DAY関数＞

締め日と支払日。なかなかアレコレ考えて算出する必要があるので面倒だったりします。

締め日と支払日の日にちだけを入力するだけで、支払日を算出できるようにしていきます。

今回は、10日締めの翌20日払いのケースで、紹介していきますが、土日祝日だったら、それをよけるようにするというのは、除いています。

A列には、購入た日が入力されています。

E2には、10日締めなので、「10」と入力しています。

E3には、翌月20日払いなので、「20」と入力しています。

B列の支払日に数式を設定します。

=DATE(YEAR(A2),MONTH(A2)+1+(DAY(A2)>$E$2),$E$3)

締め日が10日より前だから、あとだからといったことを判断させるためのIF関数は使用していません。

20日締めの翌25日払いとかでもE2とE3を変更すれば対応できる数式です。

この数式を説明していきます。

DATE関数は、日付を算出する関数ですね。引数は、年・月・日を設定する必要があります。

引数の年は、YEAR関数を使用して算出します。

引数の月は、MONTH関数を使用することで「月」の数値を算出します。

A2の場合は「7」と算出されます。

そしてこの数式のポイントになるが、「+1+(DAY(A2)>$E$2」。

翌月の払いなので「+1」するのはイメージしやすいと思いますが、7月29日は8月10日に締めて、翌月である「9月」に支払が発生しますので、さらに「+1」する必要があります。

そこで「+(DAY(A2)>$E$2」で判断させています。

「(DAY(A2)>$E$2」のDAY関数は日付を抽出する関数なのでA2の日にちですから「29」と算出されます。

その値がE2である「20」より大きいという条件が成立しているのか、成立していないのかを判断させています。

「29>20」なので、成立しているので「TRUE」という結果になります。

Excelでは、「TRUE」が「1」で「FALSE」が「0」と設定されていますので、さらに「+1」されて、「9」という数値が算出されます。

これにより、月を「9月」とすることができました。

最後の引数の日ですが、20日なので、「E3」を設定します。

TRUEが「1」でFALSEが「0」というのを今まで使用していた数式に組み合わせてみると意外な発見があるかもしれませんね。

Excel。購入日より次の利払日までの日数を算出するのがCOUPDAYSNC関数【COUPDAYSNC】

Excel。購入日より次の利払日までの日数を算出するのがCOUPDAYSNC関数

＜関数辞典：COUPDAYSNC関数＞

COUPDAYSNC関数

読み方： クーポンデイエスエヌシー　

分類： 財務 

COUPDAYSNC(受渡日,満期日,頻度,[基準])

購入日より次の利払日までの日数を算出します　

COUPon DAYs Settlement to Next Couponの略