Excel。2組のデータの相関関係を算出できるのがCORREL関数です。【CORREL】

Excel。2組のデータの相関関係を算出できるのがCORREL関数です。

＜関数辞典：CORREL関数＞

CORREL関数

読み方： コーレル　　

分類： 統計 

CORREL(配列1,配列2)

2組のデータの相関関係を算出します

Access。クエリ。通し番号をゼロ付き数値にしたいけど、どうしたらいいの？【number】

Access。クエリ。通し番号をゼロ付き数値にしたいけど、どうしたらいいの？

＜Access：Format関数＞

Excelでは、「1」を「001」のように、数値をゼロ付き数値に変更したい場合は、表示形式をつかうことで、処理することができます。

では、Accessだと、どのようにしたらいいのでしょうか。

次のテーブルを用意しました。

NOフィールドに入力されている数値データを3桁のゼロ付き数値に変更していきます。

Accessも、Excel同様に、表示形式という考え方で対応しますが、元のデータを直接修正するのではなく、クエリをつかって、今回は対応してくこととします。

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

使用するテーブルを選択したら、使用するフィールドを設定していきます。

フィールドで直接入力して変更してもいいのですが、フィールド欄が狭いため、ズームをつかって変更してきます。

ズームは、フィールドの上で右クリックすると選択することができます。

次のように、Format関数をつかった演算フィールドを設定します。

OKボタンをクリックして確定すると、NOフィールドが、演算フィールドに置き換わっていることが確認できます。

番号: Format([NO],"000")

では、実行して確認してみましょう。

ゼロ付き数値に変更することができました。

ゼロ付き数値は、Excelの表示形式と同じ、揃えたい桁数分の「0」を入力することで、対応できましたが、AccessはExcelの表示形式とは少々異なりますので、Format関数を使うときには、注意する必要があります。

Excel。「0（ゼロ）」のときに「±」をつけて「±0」と表示したい【zero】

Excel。「0（ゼロ）」のときに「±」をつけて「±0」と表示したい

＜表示形式：ユーザー定義＞

三桁区切りのカンマを設定した時に、「0（ゼロ）」の時には、「±」をつけた「±０」と表示したい場合、どのようにしたらいいのでしょうか？

三桁区切りをはじめ、このように、表示に関してアレンジしたい時には、表示形式のユーザー定義をつかうことで、対応することができます。

セルの書式設定ダイアログボックスを表示して、次のように表示形式のユーザー定義を設定します。

三桁区切りの設定がありますが、次のように設定します。

#,##0;[赤]-#,##0;"±"0

これで、「0（ゼロ）」の前に、「±」をつけることができます。

なお、表示形式は、

「正数;負数;ゼロ;文字」

という順序で設定されますので、ゼロの時に、「±0」と表示するように設定したので、ゼロが入力されると「±」も合わせて表示されるというわけです。

Excel。複雑な条件で、数値の件数を算出したいときはDCOUNT関数の出番です。【complex conditions】

Excel。複雑な条件で、数値の件数を算出したいときはDCOUNT関数の出番です。

＜DCOUNT関数＞

数値の件数を算出するにはCOUNT関数をつかいますし、条件をつけて数値の件数を算出するならば、COUNTIF関数やCOUNTIFS関数をつかいます。

ただ、条件が複雑になってきた時には、DCOUNT関数をつかうと効率もよく、条件も視認しやすくなるわけです。

ただ、DCOUNT関数は別表を用意する必要があるので、作り方を確認しておきましょう。

A1:F11に表があります。H1:H2に条件の列を用意しています。

今回は、売上高が1000以上のデータが何件あるのかを、H5には、条件に合致する件数を算出します。

なお、条件の列ですが、H1の見出し名は、表の見出しを同じにする必要があります。

要するに「売上」では、合致しないので、算出してくれません。

H5には、次の数式を設定してあります。

=DCOUNT(A1:F11,E1,H1:H2)

では、引数を確認しておきましょう。

最初の引数は、データベース。「A1:F11」を範囲選択します。

2つ目の引数は、フィールドです。売上高が1000以上の件数を算出したい、つまり対象は「売上高」なので、フィールド名の「売上高」がある「E1」を設定します。

3つ目の引数は、条件です。条件を用意した、「H1:H2」を設定します。

これで、条件に合致する件数を算出することができます。

Excel。VBA。最終行の下にSUM関数で合計値を算出するには【last line】

Excel。VBA。最終行の下にSUM関数で合計値を算出するには

＜Exce VBA：WorksheetFunction＞

For文などの繰り返し処理になれてくると、自然と、プログラム文を作るときに、繰り返し処理を使いたくなってしまいます。

繰り返し処理は、意外と処理時間がかかる傾向にあります。

要するに、データの件数が増えてしまうと、処理時間がかかってしまい、Excel VBAで高速化できるはずだったのが、イマイチということも発生します。

例えば、データの最終行に合計値を算出したい場合などが、そのケースに当たります。

次の表を用意しました。

Excelだったらば、B7にSUM関数の数式を作るはずです。

Excel VBAになったからといって、繰り返し処理にする必要はないわけです。

次のようにExcel VBAでプログラム文をつくってみました。

Sub 最終行に合計()

    Range("a2").End(xlDown).Offset(1).Value = "合計"

    Range("b2").End(xlDown).Offset(1).Value = WorksheetFunction.Sum(Range("b2", Range("b2").End(xlDown)))

End Sub

実行して確認してみましょう。

このように、データの最終行の下に合計を算出することができました。

では、プログラム文を確認しておきます。

Range("a2").End(xlDown).Offset(1).Value = "合計"

A2を起点として、End(xlDown)がデータの最終行なので、その一つ下「.Offset(1)」に「合計」と入力します。

同じ仕組みで、

Range("b2").End(xlDown).Offset(1).Value = WorksheetFunction.Sum(Range("b2", Range("b2").End(xlDown)))

B2を起点としてデータの最終行の一つ下「Range("b2").End(xlDown).Offset(1).Value」に、「WorksheetFunction.Sum」で、ワークシート関数のSum関数を使います。

ワークシート関数のSum関数の引数も、普通のSUM関数と同じなので、始点から終点を設定していきます。

ただし、データ量が変わっても最終行の下に合計を算出したいので、範囲を調整できるようにしています。範囲を調整しているのが「Range("b2").End(xlDown)」の部分です。

なお、算出結果のみで数式は不要なので、B2には、Formulaプロパティをつかっていません。

ワークシート関数も色々ありますので、使ってみると、処理時間が改善されるかもしれませんので、色々試してみるといいかもしれませんね。

Office。「逗子」や「辻」の「一点しんにょう」を表示するにはどうしたらいいの【Kanji】

Office。「逗子」や「辻」の「一点しんにょう」を表示するにはどうしたらいいの

＜IMEオプション：日本語入力＞

名簿を作るときなど、できるだけ、正しい漢字で入力したいわけです。

「逗子」や「辻」のように「しんにょう・しんにゅう」の「2点」が正しいのかもしれませんが、以前あった「1点」の「逗󠄀」や「辻󠄀」がありません。

実は、消えてしまったわけではなくて、「環境依存」の文字になってしまったので、設定を変えないと漢字変換の候補にリストアップされなくなってしまったのです。

それでは、IMEの設定を変更していきます。

言語バーの上で右クリックします。

設定をクリックします。

Microsoft IME　の設定画面が表示されますので、「全般」をクリックします。

少し、下方にスクロールすると、「変換候補の一覧に含める文字セットを選択する」が見えてきますので、「IVSを除く」から「すべて」に変更します。

これで、設定変更が終了しましたので、設定は閉じます。

それでは確認してみましょう。

「1点のしんにょう」が文字選択の一覧に表示されていることが確認できました。

もし、以前あった漢字が見当たらない場合などは、この方法で、表示できるかもしれませんね。

Excel。CONVERT関数はメートルをヤードになど数値の単位を変換する関数です。【CONVERT関数】

Excel。CONVERT関数はメートルをヤードになど数値の単位を変換する関数です。

＜関数辞典：CONVERT関数＞

CONVERT関数

読み方： コンバート

　

分類： エンジニアリング 

CONVERT(数値,変換前単位,変換後単位)

数値の単位を変換する

Excel。数式に直接日付をつかって処理する時にシリアル値で考える必要があります。【serial number】

Excel。数式に直接日付をつかって処理する時にシリアル値で考える必要があります。

＜IF+DATEVALUE関数＞

条件とする日付を入力したセルを用意するのではなく、数式に直接日付を設定する場合には、ちょっとしたアイディアが必要になります。

次の表で説明します。

C列には、B列のデータが2022/11/30までに提出されていれば、「○」を表示するようにしたいわけです。

別のセルに2022/11/30という条件を入力していないので、直接数式の中に、日付を入力した数式をつくることになるわけです。

C2の数式は、

=IF(B2<DATEVALUE("2022/12/1"),"○","×")

と設定してあります。

なぜ、日付の前に、DATEVALUE関数をつかっているのか説明をしていきます。

C2に、次の数式を用意して、オートフィルで数式をコピーしてみます。

=IF(B2<"2022/12/1","○","×")

C5の判定がおかしいことがわかります。

条件の日付に「”（ダブルコーテーション）」をつかって、「"2022/12/1"」としたのが、いけないのかと考え「2022/12/1」とダブルコーテーションを消してみます。

=IF(B2<2022/12/1,"○","×")

結果は、ますます、おかしくなってしまいます。

 なぜ、このようなことになってしまうのか。

その原因は、「日付」はシリアル値だということ。

つまり、「数値」なんだということです。

最初の「”（ダブルコーテーション）」をつかってしまうと、「文字」になってしまうので、「2022/12/1」という文字よりも小さいという条件になってしまったわけです。

二つ目の「”（ダブルコーテーション）」が無い数式は、「数値は数値」なのですが「2022/12/1」という除算した結果より小さいかという条件になってしまったわけです。

数式タブの「数式の検証」をつかってみると、よくわかります。

 このようにシリアル値の問題がある場合には、日付を数値に変えて判断させることが必要になります。

そこで、DATEVALUE関数をつかうというわけです。

C2の数式をDATEVALUE関数のネストに変更します。

=IF(B2<DATEVALUE("2022/12/1"),"○","×")

オートフィルで数式をコピーすれば、完成するというわけです。

数式タブの「数式の検証」でこの数式を確認してみると、動きが理解できます。

このように、数式内に直接日付を使用した時に、結果がおかしい場合には、シリアル値のことを考えてみると、対応できるかもしれませんね。

Excel。t分布で母集団に対する信頼区間の1/2幅を算出するのがCONFIDENCE.T関数です。【CONFIDENCE.T】

Excel。t分布で母集団に対する信頼区間の1/2幅を算出するのがCONFIDENCE.T関数です。

＜関数辞典：CONFIDENCE.T関数＞

CONFIDENCE.T関数

読み方： コンフィデンス・ティー　

読み方： コンフィデンス・テール

分類： 統計 

CONFIDENCE.T(α,標準偏差,標本数)

t分布で母集団に対する信頼区間の1/2幅を算出します　

Excel。電話番号など「0（ゼロ）」から始まるようにゼロ付文字を入力するには【with zero】

Excel。電話番号など「0（ゼロ）」から始まるようにゼロ付文字を入力するには

＜表示形式の文字列＞

セルに数値を入力する時に、Excelは一桁目が「0（ゼロ）」にしたくても、表示されません。

確かに数値の場合は、01＋02のようにゼロをイチイチ表示する必要は無いのですが、電話番号などの数値でも文字列の場合、「0（ゼロ）」を表示したいわけです。

このような場合、「’（シングルコーテーション）」を入力してから数値を入力する方法があります。

ただし、入力する件数が増えると、イチイチ「’（シングルコーテーション）」を入力するのは面倒です。

また、表示形式のユーザー定義で、「0000000000」のように設定することでも、ゼロ付数値を表示することもできます。

ただ、桁数が多い場合、「0（ゼロ）」の個数を注意して設定する必要がありますので、これも面倒です。

実は、シンプルにホームタブの「数値の書式」ボックスに「文字列」というのがあるので、それを使う方が、手早く設定できます。

この「文字列」。

スクロールバーで下方に「隠れて」いるので、移動しないと見えません。

大量のデータでゼロ付数値を入力しなければいけないときなど、重宝します。

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

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

＜Excel関数辞典：VOL.72＞

今回は、SIGN関数～SKEW.P関数までをご紹介しております。

SIGN関数

読み方： サイン　　

分類： 数学/三角 

SIGN(数値)

[正]=1[零]=0[負]=-1を算出します　

SIN関数

読み方： サイン　　

分類： 数学/三角 

SIN(数値)

角度の正弦（サイン）を算出します　

SINH関数

読み方： ハイパーポリック サイン　　

分類： 数学/三角 

SINH(数値)

数値の双曲線正弦を算出します　

SKEW関数

読み方： スキュー　　

分類： 統計 

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

データセットの歪度（わいど）を算出します　

SKEW.P関数

読み方： スキュー・ピー　　

分類： 統計 

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

データセットの歪度（わいど）を算出します　一般的な方式

Excel。上位3位までのデータがわかるように、行全体を塗りつぶしたい【higher rank】

Excel。上位3位までのデータがわかるように、行全体を塗りつぶしたい

＜条件付き書式＋LARGE関数＞

データ量が多い場合など、該当するデータがどこにあるのか、わかりやすくするために、セルを塗りつぶすだけよりも、行（レコード）全体を塗りつぶすほうが、より一層わかりやすくなります。

次の表のようにするにはどうしたらいいのでしょうか。

今回は、売上高が上位3位に該当する場合、行全体を塗りつぶすようにしてあります。

このように、条件がついた塗りつぶしを行いたい時には、「条件付き書式」をつかうわけですが、ポイントになるのが、条件をどのように設定したらいいのかということですね。

該当するセルだけを塗りつぶすだけならば、ホームタブの「条件付き書式」にある「上位/下位ルール」の「上位10項目」を使用すれば設定することができます。

しかしながら、行全体を塗りつぶしたいわけです。

そのため、条件式をつくってあげる必要があります。

上位3位ということは、第3位の売上高以上ならば、該当するといえるわけです。

第3位の値を算出するには、LARGE関数を使えば算出することができますね。

それでは、条件付き書式を設定していきます。

A2:C11を範囲選択して、ホームタブの「条件付き書式」にある「新しいルール」をクリックします。

新しい書式ルールダイアログボックスが表示されます。

「数式を使用して、書式設定するセルを決定」をクリックしたら、「次の数式を満たす場合に値を書式設定」のボックスに数式を設定します。

=$C2>=LARGE($C$2:$C$11,3)

あとは、書式を設定したらOKボタンをクリックして完成です。

設定した数式ですが、

LARGE($C$2:$C$11,3)で、第3位の売上高の数値を算出できます。

それ以上ならば、1位～3位であることがわかります。

また、「$C2」と列固定の複合参照にすることで、行全体を塗りつぶすことができます。

Excel。期間日をまとめて算出するなら、DAYS関数をつかえば楽に算出できます【period】

Excel。期間日をまとめて算出するなら、DAYS関数をつかえば楽に算出できます

＜DAYS関数＞

日付で、開始日から終了日までの日数を算出するなら、単純に、終了日から開始日を減算すればいいわけです。

しかし、データ量がある場合、DAYS関数をつかえば、スピル機能も加わって、オートフィルで数式をコピーする必要もなくなり、手早く算出することができます。

次の表を用意しました。

D2には、

=DAYS(C2:C4,B2:B4)

という数式を設定するだけで、あとは、スピル機能のおかげで、オートフィルで数式をコピーする必要もありません。

手早く算出することができます。

DAYS関数の引数は、

DAYS(終了日,開始日)

となっています。

最初の引数の終了日には、C2:C4 と設定します。

次の引数の開始日には、B2:B4 と設定します。

DATEDIF関数などは、開始日,終了日という順番なので、そこだけ気を付ければ、数式を作成するのも難しくなさそうですね。

Excel。CONFIDENCE.NORM関数は正規分布で母集団に対する信頼区間の1/2幅を算出します【CONFIDENCE.NORM】

Excel。CONFIDENCE.NORM関数は正規分布で母集団に対する信頼区間の1/2幅を算出します

＜関数辞典：CONFIDENCE.NORM関数＞

CONFIDENCE.NORM関数

読み方： コンフィデンス・ノーマル　　

分類： 統計 

CONFIDENCE.NORM(α,標準偏差,標本数)

正規分布で母集団に対する信頼区間の1/2幅を算出します

Excel。4月1日～3月31日を年度として、手早く年度ごとにわけるにはどうしたらいい【fiscal year】

Excel。4月1日～3月31日を年度として、手早く年度ごとにわけるにはどうしたらいい

＜YEAR+MONTH関数＞

年を算出するには、YEAR関数をつかうことで、簡単に判別することができます。

ただ、4月1日～3月31日を「年度」として、判別するにはYEAR関数だけというわけにはいきません。

次の表を使って説明します。

B6の2023/3/8までは2022年度ですが、B7の2023/4/22は2023年度と算出したいわけです。

年度の算出で一番ネックなのは、どうやったら、1月～3月を前年の「年」と同じにしたらいいのかということですね。

そのため、YEAR関数だけでは、上手く算出することができないわけです。

IF関数で1月～3月だったら、年をマイナス1するというのでもいいのですが、もっとコンパクトな数式で算出することができます。

A2に次の数式を設定します。

=YEAR(B2)-(MONTH(B2)<4)

説明はあとに回すとして、オートフィルで数式をコピーしたら完成です。

なお、算出結果の表示形式が日付型になってしまうので、表示形式を「標準」に戻す必要があります。

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

前半部分の「YEAR(B2)」は、年を算出しています。算出結果は「2022」です。

C列に算出してみました。

後半部分の「(MONTH(B2)<4」ですが、MONTH(B2)で算出される値は「12」ですが、この数式は、「(MONTH(B2)<4」が成立するのか否かということで使っています。

D列に算出しましたが、結果は、TRUEとFALSEと算出されます。

なぜ、TRUEとFALSEを算出させたのかというと、Excelでは、TRUEを「1」。

FALSEを「0」と定義しています。

この「1」と「0」を使いたかったためです。

なお、TRUEとFALSEですが、「×1」すると、数値に置換できるので、確認のため「×1」したのが、E列です。

こうすることで、1月～3月はTRUE。

すなわち「1」を年から減算しますので、2022となり、年度を算出することができます。

これ以外にも、年度を算出する方法は色々ありますので、一例ということでご紹介させていただきました。

Excel。2022/10/3-10/9にINT関数など紹介したFacebookページのコメントです。【Trivia】

Excel。2022/10/3-10/9にINT関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

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

10月3日

Excel。

INFO関数

読み方は、インフォで、Excelの動作環境に関する情報を返す

10月4日

Excel。

INT関数

読み方は、イントで、最も近い整数に切り下げる

10月5日

Excel。

INTERCEPT関数

読み方は、インターセプトで、回帰直線の切片を算出

10月6日

Excel。

INTRATE関数

読み方は、イントレートで、満期に償還される証券の利率を算出

10月7日

Excel。

IPMT関数

読み方は、アイピーエムティー：インタレストペイメントで、元利均等返済における指定期間の利息を算出

10月8日

Excel。

IRR関数

読み方は、アイアールアールで、定期キャッシュフローに対する内部利益率を算出

10月9日

Excel。

ISBLANK関数

読み方は、イズブランクで、対象が空白セルの場合にTRUEを返す

Excel。スピル機能の登場で、数式の作り方が結構変わっています。【SPILL】

Excel。スピル機能の登場で、数式の作り方が結構変わっています。

＜スピル機能＞

Excelに少し前から加わった機能に、「スピル（SPILL）」というものがあります。

以前ならば、面倒な配列数式にしなければいけなかった数式や、複合参照を駆使した数式は、スピル機能をつかった数式にすることで、わかりやすく、そして、手早く算出することができます。

例として九九の表を作ってみます。

B2には、

=A2:A6*B1:F1

という数式を設定するだけです。

スピル機能によって、オートフィルで数式をコピーすることもありません。

可読性も高く、手早く作成することができます。

以前ならば、「=$A2*B$1」という数式を作っていました。

B1とA2を乗算した数式を、複合参照にする必要がありました。

また、オートフィルで数式をコピーして完成させることは出来ませんでした。

複合参照は、列と行のどちらを固定させるのかを考えなければなりません。

このように、スピル機能の登場によって、Excelも色々変わっています。

Access。クエリ。未入力のフィールドにデータを一括で入力するにはどうしたらいいの【Batch input】

Access。クエリ。未入力のフィールドにデータを一括で入力するにはどうしたらいいの

＜Access：更新クエリ＞

Excelだったら、空欄に同じデータを一括で入力したいときは、IF関数を使うとか、置換を使うとか様々なアイディアが浮かぶと思います。

では、Accessのテーブルでは、どのようにしたらいいのでしょうか？

次のテーブルを用意して説明していきます。

Reportフィールドの空欄に、「未提出」を入力していきます。

Accessでは、「更新クエリ」をつかうことで、手早く処理することができます。

それでは、更新クエリをつくっていきます。

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

使用するクエリはアクションクエリのひとつ。「更新クエリ」なのですが、更新クエリの怖いところは、抽出する条件を間違えたまま実行すると、取り返しがつきません。

選択クエリの状態で、抽出条件がいいのかを確認してから、更新クエリに変更することをお勧めします。

逆に慣れているならば、最初から更新クエリで作ることをお勧めします。

今回は、最初から、更新クエリで作成していきます。

クエリデザインの「クエリの種類」を「選択」から「更新」に変更します。

Reportフィールの空欄に未提出というデータを入力したいので、設定するフィールド「Report」だけ設定します。

レコードの更新には「”未提出”」と入力します。

設定したものに変更処理されます。

抽出条件ですが、空欄のデータが対象になるので、「null」と入力します。

確定すると「Is Null」と変更されます。

「null」は、データが何もないことを意味しています。

スペースキーなどでは空白という文字になってしまいます。

あとは、実行ボタンを押すだけですが、疑心暗鬼ではありませんが、不安な時には、選択クエリに戻し、対象となるデータに間違いがないかを確認してから、実行することを改めてですが、お勧めします。

では、実行ボタンをクリックします。

更新件数の確認メッセージが表示されるので、「はい」をクリックします。

このように、空欄のフィールドに「未提出」という文字を入力することができました。

Excel。FILTER関数でAND条件をつかった抽出には「*」をつかいます【Wildcard】

Excel。FILTER関数でAND条件をつかった抽出には「*」をつかいます

＜FILTER関数＞

オートフィルターで抽出するのは簡単ですが、条件が複雑になり、さらにその抽出したデータをコピーするとなると、なかなか面倒な作業になってきます。

そこで、FILTER関数をつかうと、手早く対応することができます。

そして、今回は抽出条件を「AND条件」で抽出する場合の引数を紹介します。

「売上表AND」とテーブル名を設定したテーブルを用意しました。

店舗名が新宿でかつ、売上高が1200より大きいデータを抽出します。

さらに、フィールドも店舗名・商品名・売上高だけの表にしたいとします。

H2に次の数式を設定します。

=FILTER(売上表AND[[店舗名]:[売上高]],(売上表AND[売上高]>1200)*(売上表AND[店舗名]="新宿"))

あとは、スピル機能によって、オートフィルで数式をコピーしなくても数式が拡張されます。

このように算出することができました。

FILTER関数の引数を確認しましょう。

最初の引数の「配列」には、テーブルを設定しますので「売上表AND[[店舗名]:[売上高]]」と入力します。

2番目の引数は、「含む」ですが、条件ですね。

「(売上表AND[売上高]>1200)*(売上表AND[店舗名]="新宿")」と設定します。

これで、「店舗名が新宿でかつ、売上高が1200より大きいデータ」という条件を設定することができます。

そして、「*（アスタリスク）」をつかって接続することで、AND条件にすることができます。

Excel。CONFIDENCE関数で正規分布で母集団に対する信頼区間の1/2幅を算出できます。【CONFIDENCE関数】

Excel。CONFIDENCE関数で正規分布で母集団に対する信頼区間の1/2幅を算出できます。

＜関数辞典：CONFIDENCE関数＞

CONFIDENCE関数

読み方： コンフィデンス　　

分類： 互換性 

CONFIDENCE(α,標準偏差,標本数)

正規分布で母集団に対する信頼区間の1/2幅を算出します　

Excel。VBAでセルにスピル機能の数式を設定するにはどうしたらいいの【Spill】

Excel。VBAでセルにスピル機能の数式を設定するにはどうしたらいいの

＜Excel VBA：Fomula2プロパティ＞

Excelに新しく加わった機能の【スピル】。

Excel VBAのプログラム文で、どのようにしたら使えるのでしょうか。

まずは、通常のExcelでスピルの数式を作って確認します。

九九の表をつくりたいとします。

以前のExcelならば、複合参照をつかった数式を設定して、オートフィルで数式をコピーしていました。

例えば、B2には、

=$A2*B$1

と設定していたわけですね。

複合参照になれていないと、列を固定するのか、行を固定するのかパズルのように考えないといけなかったわけです。

ところが、スピル機能の登場によって、B2には、次のような数式を設定するだけで、あとはスピル機能のおかげで、オートフィルで数式をコピーする必要もありません。

B2には、

=A2:A5*B1:D1

と配列関数のような数式ですが、これだけで九九の表が簡単につくれるわけです。

このスピル機能をつかった数式をExcel VBAのプログラム文で設定するにはどうしたらいいのか確認をしていきます。

セルに数式を設定するには、「Formulaプロパティ」をつかえばいいので、Formulaプロパティをつかって、プログラム文をつくっています。

Sub スピルの数式()

    Range("b2").Formula = "=a2:a5*b1:d1"

End Sub

実行してみます。

B2のみに算出されています。

しかも、数式が、

=@A2:A5*@B1:D1

と「@（アットマーク）」がついたセル番地だけ計算する数式に変わってしまっています。

実は、スピル機能の数式をつかうには、

Formulaプロパティではなくて、「Formula2プロパティ」を使う必要があるのです。

では、Formula2プロパティに変更してみます。

Sub スピル数式()

    Range("b2").Formula2 = "=a2:a5*b1:d1"

End Sub

実行してみましょう。

このように、スピル機能の数式をExcel VBAでもつかうことができます。

もしかしたら繰り返し処理で処理時間がかかっているようならば、改善できるかもしれませんね。

Excel。2022/9/26-10/2にINDIRECT関数など紹介したFacebookページのコメントです。【Trivia】

Excel。2022/9/26-10/2にINDIRECT関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

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

9月26日

Excel。

IMSINH関数

読み方は、アイエムサインハイパーポリックで、複素数の双曲線正弦（ハイパーポリックサイン）を算出する

9月27日

Excel。

IMSUB関数

読み方は、アイエムサブで、複素数の差を算出する

9月28日

Excel。

IMSUM関数

読み方は、アイエムサムで、複素数の和を算出する

9月29日

Excel。

IMSQRT関数

読み方は、アイエムスクエアルートで、複素数の平方根を算出する

9月30日

Excel。

IMTAN関数

読み方は、アイエムタンジェントで、複素数のタンジェントを算出する

10月1日

Excel。

INDEX関数

読み方は、インデックスで、セル範囲から縦横座標で値を抽出します。

10月2日

Excel。

INDIRECT関数

読み方は、インダイレクトで、文字列で参照されるセルの値を算出します。

Excel。以前からある、複数の文字列を統合する関数はCONCATENATE関数です。【CONCATENATE】

Excel。以前からある、複数の文字列を統合する関数はCONCATENATE関数です。

＜関数辞典：CONCATENATE関数＞

CONCATENATE関数

読み方： コンカティネイト　　

分類： 互換性 

CONCATENATE(文字列1,[文字列2],…)

複数の文字列を統合する　

CONCAT関数は、CONCATENATE関数の進化版関数です。