Excel。BYCOL関数は、ラムダ関数を各列に適用し結果の配列を返します【BYCOL】

Excel。BYCOL関数は、ラムダ関数を各列に適用し結果の配列を返します

＜関数辞典：BYCOL関数＞

BYCOL関数

読み方： バイカラム

読み方： ビーワイカラム

分類： 論理 

BYCOL(array,[function])

ラムダ関数を各列に適用し、結果の配列を返す

Excel。VBA。条件と合致した行全体を手早く塗りつぶしたい。【Interior】

Excel。VBA。条件と合致した行全体を手早く塗りつぶしたい。

＜Excel VBA：Interior＞

データを読み込んだ後に、毎回同じ設定をするのは、意外と面倒です。

やりたい作業は、販売数が80以上のデータがある行全体を塗りつぶしたいわけです。

条件付き書式をつかって、数式で「=$D2>=80」として塗りつぶしを設定すればいいだけなのですが、データを読み込むたびに設定するのは、大変です。

そこで、Excel VBAでプログラムをつくって対応することにしましょう。

次のようにプログラムを書いてみました。

Sub 行塗りつぶし()

    Dim i As Long

    Dim lastrow As Long

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

    

    For i = 2 To lastrow

        If Cells(i, "d") >= 80 Then

            With Range(Cells(i, "a"), Cells(i, "f")).Interior

                .ThemeColor = xlThemeColorAccent1

                .TintAndShade = 0.8

            End With

        End If

    Next

End Sub

とりあえず、実行して確認してみましょう。

このように、販売数が、80以上のデータがある行全体を塗りつぶすことができました。

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

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

Dim i As Long

Dim lastrow As Long

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

変数名のlastrowは、データ件数を入れるための変数です。

データ件数繰り返したいので、用意しました。

For i = 2 To lastrow

    If Cells(i, "d") >= 80 Then

        With Range(Cells(i, "a"), Cells(i, "f")).Interior

            .ThemeColor = xlThemeColorAccent1

            .TintAndShade = 0.8

        End With

    End If

Next

For To Nextはデータ件数分繰り返し処理をしています。

If～End Ifは、販売数が80以上だったらという条件で、処理を振り分けています。

With～End Withは、80以上のデータだった場合、行全体を塗りつぶす処理をしています。

「Range(Cells(i, "a"), Cells(i, "f")).Interior」を繰り返し入力するのは、これまた面倒なので、With文をつかって、繰り返し入力する作業を減らしています。

Interiorプロパティをつかうことで、塗りつぶしすることができます。

「ThemeColor = xlThemeColorAccent1」と「TintAndShade = 0.8」ですが、これはテーマの色を設定しています。

この2つは何を意味しているのかというと、カラーパレットを見れば、一目瞭然です。

テーマカラーは、Accent1で、「0.8」すなわち、80%なので、「青、アクセント1 白+基本色80%」で、セルを塗りつぶすように指示しているわけです。

条件付き書式を設定するのもいいですが、何度も同じことをするようでしたら、Excel VBAでプログラムをつくってしまうというのも、作業効率の面から見ても、いいかもしれませんね。

Excel。今週のFacebookページの投稿 2022/5/23-2022/5/29【Trivia】

Excel。今週のFacebookページの投稿　2022/5/23-2022/5/29

＜Facebookページ＞

Facebookページで【書いてみた】Excelの豆知識（Trivia）です。

5月23日

Excel。

COVARIANCE.P関数

読み方は、コバリアンス・ピーで、2組のデータの母共分散を算出します

5月24日

Excel。

COVARIANCE.S関数

読み方は、コバリアンス・エスで、2組のデータの共分散を算出します

5月25日

Excel。

CRITBINOM関数

読み方は、クリテリアバイノムで、累計二項分布が基準値以上になる最小値を算出します

5月26日

Excel。

CSC関数

読み方は、コセカントで、角度の余割を算出します

5月27日

Excel。

CUBEKPIMEMBER関数

読み方は、キューブケーピーアイメンバーで、主要業績評価指標(KPI)を返します

5月28日

Excel。

CUBEMEMBER関数

読み方は、キューブメンバーで、キューブからメンバーまたは組を返します

5月29日

Excel。

CUBEMEMBERPROPERTY関数

読み方は、キューブメンバープロパティで、キューブからメンバーのプロパティの値を返します

Excel。BITXOR関数は、排他的論理和を算出します。【BITXOR】

Excel。BITXOR関数は、排他的論理和を算出します。

＜関数辞典：BITXOR関数＞

BITXOR関数

読み方： ビットエックスオア

読み方： ビットエクスクルーシブオア

分類： エンジニアリング 

BITXOR(数値1,数値2)

排他的論理和を算出する　

Access。クエリ。データの上位25%の降順リストを手早くつくりたい【Top 25%】

Access。クエリ。データの上位25%の降順リストを手早くつくりたい

＜トップ値＞

Excel以上にAccessは大きな大量のデータをつかって、集計や抽出を行うことが多いようです。

そこで、TOP10とかではなくて、全体のうち、上位25%のデータを降順で手早く、抽出したい場合、どのようにしたらいいでしょうか。

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

このテーブルから、商品別の売上高合計金額が、上位25%に該当する降順のクエリをつくっていきます。

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

 

デザインビューにかわりますので、必要なフィールドを設定します。

売上高の並べ替え設定を「降順」にしておきます。

初期のままだと、商品名ごとと、「グループ化」することができませんので、クエリデザインタブにある「集計」をクリックして、集計行を追加します。

グループ化したい、商品名フィールドの集計は、「グループ化」と設定します。

合算値を算出したい売上高フィールドの集計には「合計」と設定します。

ここまでの状況を確認しますので、データシートビューで確認します。

次に「売上高の合計」の上位25%に抽出する作業をしていきますので、デザインビューに移動します。

クエリデザインタブの「プロパティシート」をクリックして、プロパティシート作業ウィンドウを表示します。

「トップ値」に「25%」と設定します。

あとは、クエリ名をつけて、完成です。

それでは、データシートビューで確認します。

このように、上位25%のデータのみにすることができました。

プロパティシートの中にあるということで、なかなか使う機会はないかもしれませんが、トップ値をつかうことで、色々なデータを抽出することができますよ。

Excel。EXPAND関数をつかえば、表からのセル参照を手早く設定できます。【EXPAND】

Excel。EXPAND関数をつかえば、表からのセル参照を手早く設定できます。

＜EXPAND関数＞

2022年3月に追加された、「EXPAND関数」。

新しく加わった14個の関数の中で、イマイチという感じもしますが、大きな表を基にしたセル参照などでは、使い勝手がいい関数といえます。

EXPAND関数の基本情報から確認しておきます。

EXPAND関数

読み方： エクスパンド

分類： 検索/行列

引数は、

EXPAND(array,rows,[columns],[pad_with])

配列を指定した行数列数に展開または埋め込むことができます。

1番目の引数「array」は、配列。範囲のことですね。

2番目の引数「rows」は、行

3番目の引数「columns」は、列

4番目の引数「pad_with」は、埋め込みに使用する値

説明用に次の表を用意しました。

A9には、EXPAND関数をつかった数式を設定しました。

=EXPAND(A2:C5,,)

この関数も「スピル機能」が動くので、自動的に数式が拡張されます。

設定すると、A2:C5までの値を参照して表示してくれます。

C2:C5にデータを入力してみます。

「=セル番地」のセル参照のように、数値が表示されました。

さて、ここからがEXPAND関数の本領発揮。

D2:E5までも同じように、セル参照をするとしたら、「=C2」のようなセル参照の数式をオートフィルでコピーするはずです。

ただ、表が大きな場合、オートフィルで数式をコピーする作業は単純ではありますが、面倒です。

そこで、A9に設定した、EXPAND関数を次のように修正してみます。

=EXPAND(A2:E5,,)

C5をE5に修正しただけで、自動的にセル参照が拡張することができました。

もっと便利な使い方がありそうなので、色々探ってみると面白い関数なのかもしれませんね。

Excel。BITRSHIFT関数は、ビットを右シフトする関数です。【BITRSHIFT】

Excel。BITRSHIFT関数は、ビットを右シフトする関数です。

＜関数辞典：BITRSHIFT関数＞

BITRSHIFT関数

読み方： ビットライトシフト　　

分類： エンジニアリング 

BITRSHIFT(数値,移動数)

ビットを右シフトする　

Excel。検索データと合致する行全体を手早く塗りつぶしたい【Paint the cell】

Excel。検索データと合致する行全体を手早く塗りつぶしたい

＜条件付き書式＞

検索したいデータが、表内のどこにあるのか、目視でわかるように、行全体を塗りつぶしたい場合、どのようにしたら手早く処理することができるのでしょうか。

次の表を用意しました。

B1に入力したデータを、B列内で検索して合致したら、行全体を塗りつぶすという設定をしたいわけですね。

オートフィルターをつかって該当データを抽出して、セルを塗りつぶすという方法もありますが、ここは、シンプルに、「条件付き書式」をつかうことで、手早く設定することができます。

A4:B13を範囲選択します。

ホームタブの条件付き書式にある「新しいルール」をクリックします。

行全体を塗りつぶしたいなど、該当のセルに留まらないで行全体・列全体のような場合には、新しいルールで数式をつかって、設定する必要があります。

新しいルールダイアログボックスが表示されたら、ルールの種類から「数式を使用して、書式設定するセルを決定」をクリックします。

次の数式を満たす場合のボックスには、次の数式を設定します。

=$B$1=$B4

書式ボタンをクリックして、塗りつぶしの色を設定したあとに、OKボタンをクリックして設定完了です。

設定した数式のポイントは、複合参照の「$B4」。

列と行を固定する絶対参照だと、そのデータしか対象になりません。

そこで、列側だけ固定した「$B4」とすることで、行全体を塗りつぶしすることができます。

列や行だけを固定する方法を、「複合参照」といいます。

行を塗りつぶしたい場合には、例えば「B$4」といったように、行側を固定することで対応できます。

条件付き書式は、条件に数式をうまくつかうことで、様々なリクエストに対応することができますので、色々試してみるといいかもしれませんね。

Excel。今週のFacebookページの投稿 2022/5/16-2022/5/22【Trivia】

Excel。今週のFacebookページの投稿　2022/5/16-2022/5/22

＜Facebookページ＞

Facebookページで【書いてみた】Excelの豆知識（Trivia）です。

5月16日

Excel。

COUPDAYBS関数

読み方は、クーポンデイビーズで、購入日より前の利払日までの日数を算出します

5月17日

Excel。

COUPDAYS関数

読み方は、クーポンデイズで、購入日を含む利払日と利払日の間の日数を算出します

5月18日

Excel。

COUPDAYSNC関数

読み方は、クーポンデイエスエヌシーで、購入日より次の利払日までの日数を算出します

5月19日

Excel。

COUPNCD関数

読み方は、クーポンエヌシーディーで、購入日後の最初の利払日を算出します

5月20日

Excel。

COUPNUM関数

読み方は、クーポンナンバーで、購入日後の利払回数を算出します

5月21日

Excel。

COUPPCD関数

読み方は、クーポンピーシーディーで、購入日より前の直近の利払日を算出します

5月22日

Excel。

COVAR関数

読み方は、コバリアンスで、2組のデータの母共分散を算出します

Excel。BITOR関数は、論理和を算出する関数です。【BITOR】

Excel。BITOR関数は、論理和を算出する関数です。

＜関数辞典：BITOR関数＞

BITOR関数

読み方： ビットオア　　

分類： エンジニアリング 

BITOR(数値1,数値2)

論理和を算出します。

Excel。データから手早く複数の予測値を算出するにはどうしたらいいの【Predicted value】

Excel。データから手早く複数の予測値を算出するにはどうしたらいいの

＜TREND関数＞

次のようなデータがあるので、予測値を算出する。

できれば、様々なケースの予測を同時に算出したい場合、Excelの関数をつかうことで、手早く算出することができます。

今回予測したいのは、来店客数です。

B列には、売上高。C列には、来店客数のデータがあります。

せっかくデータがあるので、売上高を1100にするには、どのぐらいの来店客数があればいいのかを算出したいわけですね。

B列とC列のデータを目視から、「このぐらいじゃない」というのは、マズいわけですね。

来店客数の予測を算出すること自体は、関数で手早く算出できるのですが、前提条件として、売上高と来店客数に、関係性があるのか、ないのか、ということがあげられます。

そこで、F1に、相関係数を算出することから始めます。

F1に設定している数式は、

=CORREL(B2:B12,C2:C12)

CORREL関数は、相関係数を算出できる関数です。

この値が、「1」に近いので、因果関係まではわかりませんが、何らかの関係性は強いと判断できます。

逆に「0」に近ければ、「関係性はない」ということがわかります。

来店客数が増えれば、売上高があがることがわかったわけです。

これを踏まえたうえで、売上高に必要な来店客数を算出していきます。

F4に

=INT(TREND(C2:C12,B2:B12,E4:E12))

という数式を設定します。

予測値を算出するだけならば、FORECAST関数を使うことで算出できますが、複数の予測値を算出するには、TREND関数をつかいます。

予測値を算出するにあたり、回帰直線のy=ax+bに基づき引数を設定していきます。

既知のyには、来客数の予測値を算出したいので、C2:C12を設定します。

既知のxには、売上高がxにあたりますので、B2:B12を設定します。

新しいxには、売上高の予定値を設定しますので、E4:E12を設定します。

今回は、定数は省略しています。

また、算出結果に小数点が表示されます。

来店客数なので、INT関数をつかって、整数化しています。

以前は、配列関数にしなければいけませんでしたが、今は、スピル機能が追加されましたので、F12まで予測値を算出してくれます。

このように、ちょっとしたデータでも、複数の予測値を素早く算出することができますので、今までの資料にも、このような関数を使いすることで、違った角度から資料を見ることができるかもしれませんね。

Excel。DROP関数は手早く指定した行列を除外した範囲を抽出できます。【DROP】

Excel。DROP関数は手早く指定した行列を除外した範囲を抽出できます。

＜DROP関数＞

2022年3月に追加された、「DROP関数」。

なんで今までいなかったの？といえるほど、便利な関数が追加されました。

次の表で確認してみましょう。

 

DROP関数は、大きなデータになればなるほど、使いたくなる関数だと思います。

A1:E6の表があります。

この表から、1行目の見出し行と、E列のチェックを除いた範囲をコピーしたい。

いままでは、SHIFTキーをつかった範囲選択など、単純作業だけど、データ量が多くなればなるほど、面倒な作業を、DROP関数だけで、処理してくれます。

A9にDROP関数をつかった数式を作ります。

=DROP(A1:E6,1,-1)

たった、これだけで、A9:D13に抽出することができました。

数式は、スピル機能があるので、自動的に拡張されます。

そのため、オートフィルで数式をコピーする必要もありません。

いままで、見出し行を除いた範囲選択や、逆に最終行にある集計行を除いて範囲選択を行うことも、新しく登場したDROP関数によって、手早く処理することができるようになりました。

最後に、DROP関数の引数なども確認しておきましょう。

DROP関数

読み方： ドロップ

分類： 検索/行列 

DROP(array,rows,[columns])

配列から連続する指定された数の連続する行または列を除外する　

引数の

Arrayは列のことで、範囲ですね。

Rowsは、除きたい行の数です。1行目だけを削除したいならば「1」。

最終行を除きたいなら「-1」という感じです。

Columnsは、除きたい列の数です。すべての列を残すようならば、省略することができます。

なお、この関数は、現在、Office InsiderのMicorsoft365。

バージョン2203ベータチャネル版にしか、追加されていません。

おいおい、Micorsoft365のExcelにも追加されること思われます。

Excel。BITLSHIFT関数は、ビットを左シフトする関数です。【BITLSHIFT】

Excel。BITLSHIFT関数は、ビットを左シフトする関数です。

＜関数辞典：BITLSHIFT関数＞

BITLSHIFT関数

読み方： ビットレフトシフト　　

分類： エンジニアリング 

BITLSHIFT(数値,移動数)

ビットを左シフトする　

Excel。手早く5行おきにセルを塗り分けたいけど、どうしたらいいの

＜MOD・QUOTIENT・ROW関数＋条件付き書式＞

データを見やすくしたいので、5行おきに色分けしたいけど、どのようにしたら、手早く設定することができるのでしょうか。

セルを塗りつぶすこと自体は、とても簡単な作業ですが、データの量が多いと面倒です。

面倒だからといって、わざわざ、Excel VBAをつかって、プログラムをつくるというのも、逆に面倒になってしまいます。

そこで、セルを塗りつぶしたいので、「条件付き書式」で考えてみることにします。

問題は、どのような条件式を設定すればいいのかということです。

有名な、一行おきならば、MOD関数とROW関数を組わせた条件を設定すればいいわけですが、やりたいことは、5行おきです。

単純に5で除算すればいいというわけにはいきません。

次のように、設定する数式を考えてみました。

C2に設定した数式は、

=MOD(QUOTIENT(ROW()-2,5),2)

オートフィルで数式をコピーしています。

算出結果をみると、5行おきに「0」と「1」にわけることができました。

あとは、この条件式を使って、条件付き書式を設定すれば、5行おきに塗り分けることができそうです。

先に、この数式を確認しておきましょう。

引数の内側からみていきます。

QUOTIENT(ROW()-2,5)

このパーツは、行番号から2を減算した値を、5で除算した商を算出しています。

ROW関数は、行番号を算出する関数。

ROW関数をつかって算出した行番号から2を減算しています。

1～5・6～10という組み合わせにしたいので、5で除算してみます。

1～4の商は、「0」となりますが、5は「1」となってしまうので、1～5の組み合わせにすることができません。

そこで、「-2」することで、0～４・5～9というように除算した結果の商を調整するために「-2」しています。

QUOTIENT関数は、除算した結果の商を算出する関数です。

ROW()-2で算出された値を、5で除算しています。

こうすることで、5行ごとにまとめることができます。

最後に、QUOTIENT関数で算出した値を、2で除算した余りを算出しています。

MOD関数は、除算した余りを算出する関数です。

偶数か奇数かを判断する方法ですね。

こうすることで、「0」と「1」で分別することができました。

最後に条件付き書式にこの数式を設定していきます。

A2:B12を範囲選択します。

ホームタブの「条件付き書式」にある「新しいルール」をクリックします。

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

 

「数式を使用して、書式設定するセルを決定」のボックスに、先程の数式を設定します。

=MOD(QUOTIENT(ROW()-2,5),2)

あとは、書式ボタンをクリックして、塗りつぶす色を設定したら、OKボタンをクリックします。

これで、5行おきに塗り分けることができました。

 ところで、先程設定した数式ですが、

=MOD(QUOTIENT(ROW()-2,5),2)

と、「=0」とか「=1」をつけていません。

これは、Excelでは「0」がFalseで「1」がTrueとなっています。

この数式の結果が0だったらFalseなので、成立していないということで、塗りつぶされません。

このようなことから、省略した数式でも、問題なく、セルを塗り分けることができたというわけです。

Excel。今週のFacebookページの投稿 2022/5/9-2022/5/15【Trivia】

Excel。今週のFacebookページの投稿　2022/5/9-2022/5/15

＜Facebookページ＞

Facebookページで【書いてみた】Excelの豆知識（Trivia）です。

5月9日

Excel。

TOCOL関数

読み方： トゥカル　　

読み方： トゥカラム

分類： 検索/行列 

TOCOL(array,[ignore],[scan_by_column])

配列を1つの列として返します　

5月10日

Excel。

TOROW関数

読み方： トゥロウ　　

分類： 検索/行列 

TOROW(array,[ignore],[scan_by_column])

配列を1つの行として返します　

5月11日

Excel。

VSTACK関数

読み方： ブイスタック　　

分類： 検索/行列 

VSTACK(array1,[array2],…)

垂直方向に配列を1つの配列に積み重ね（スタック）する　

5月12日

Excel。

WRAPCOLS関数

読み方： ラップコルズ

読み方： ラップカラムズ

分類： 検索/行列 

WRAPCOLS(vector,wrap_count,[pad_with])

指定した数の値の後に列で折り返（ラップ）します　

5月13日

Excel。

WRAPROWS関数

読み方： ラップロウズ　　

分類： 検索/行列 

WRAPROWS(vector,wrap_count,[pad_with])

指定した数の値の後に行で折り返（ラップ）します　

5月14日

Excel。

COUNTIF関数。

読み方は、カウントイフで、1つの条件を満たす件数を算出します

5月15日

Excel。

COUNTIFS関数。

読み方は、カウントイフズで、複数の条件を満たす件数を算出します

Excel。BITAND関数は、論理積を算出します。【BITAND】

Excel。BITAND関数は、論理積を算出します。

＜関数辞典：BITAND関数＞

BITAND関数

読み方： ビットアンド　　

分類： エンジニアリング 

BITAND(数値1,数値2)

論理積を算出する　

Excel。手早く20日締め翌月末払いの日程表をつくりたい【payment】

Excel。手早く20日締め翌月末払いの日程表をつくりたい

＜DATE・EDATE・DAY・YEAE・MONTH関数＞

Excelの日付計算は、頭の中で考えるように、スムーズにいかないところがあります。

たとえば、次のような、20日締め翌月末払いの日程表をつくるとします。

A列に仕入日を入力すると、締め日と支払日が表示されるように数式を設定するわけですが、手早く作りたいわけです。

B列の締め日は、20日締めなので、20日より前ならば、同月20日ですが、21日以降ならば、翌月20日で表示したいわけです。

なお、今回は、土日祝日は考慮しておりません。

さて、20日より前とか後となれば、IF関数をつかって算出させればいいように思います。

B4にIF関数をつかった数式を設定するとすれば、

=IF(DAY(A4)<=20,DATE(YEAR(A4),MONTH(A4),20),DATE(YEAR(A4),MONTH(A4)+1,20))

確かに間違いではないのですが、当然長くなります。

わかりやすい反面、可読性が悪化します。

そこで、B4に次のような数式でも算出することができます。

=DATE(YEAR(A4),MONTH(EDATE(A4-20,1)),20)

数式がコンパクトになったことで、可読性が向上していますね。

それに、IF関数をつかわないで、算出しています。

締め日の着目点は、「20日前なのか後なのか」ということです。

そこで、MONTH関数の引数を確認します。

MONTH(EDATE(A4-20,1))

MONTH関数は、月を算出する関数です。

EDATEは、月ちがいの同日を算出する関数です。

仕入日から20減算した日付をつかい、EDATE関数で算出された月に「+1」した数値を月として、算出しています。

ケースとして、2022/4/21ならば、20日減算すると2022/4/1なので、月は、4+1で5と算出されます。

2022/5/20が締め日となり、2022/4/20ならば、20日減算すると、2022/3/31なので、月は、3+1で4と算出されます。

よって、2022/4/20が締め日と算出することができるというわけです。

このようなことから、IF関数をつかわないで、算出することができるというわけです。

支払日の数式は、IF関数で算出しても、締め日と同じ方法で算出しても、可動性に変わりは、あまりありません。

C4の数式は、

IF関数ならば、

=IF(DAY(A4)<=20,EOMONTH(A4,1),EOMONTH(A4,2))

EDATE関数ならば、

=DATE(YEAR(A4),MONTH(EDATE(A4-20,3)),1)-1

関数の説明をします。

=IF(DAY(A4)<=20,EOMONTH(A4,1),EOMONTH(A4,2))

この数式で使用している、EOMONTH関数は、月末日を算出する関数です。

20日以前ならば、一か月後の月末を算出し、それ以外は、二か月後の月末を算出することができます。

=DATE(YEAR(A4),MONTH(EDATE(A4-20,3)),1)-1

MONTH関数は、先程と同じで、20日減算した日の、同日の3か月後の月を算出しています。

2022/4/21のケースだと、20日減算するので、2022/4/1で、この同日の3か月後ですから、MONTH関数で算出された月は、「7」。

DATE関数で算出されるのが、2022/7/1。

この日から「-1」することで、前月末日。

すなわち、翌月末日を算出することができるので、2022/6/30と算出することができるわけです。

このように、Excelには、様々な日付の関数が用意されいるので、色々組み合わせてみると数式を改善することができるかもしれませんね。

Excel関数辞典 VOL.63。PRODUCT関数～QUARTILE.EXC関数【dictionary】

Excel関数辞典　VOL.63。PRODUCT関数～QUARTILE.EXC関数

＜Excel関数＞

今回は、PRODUCT関数～QUARTILE.EXC関数までをご紹介しております。

PRODUCT関数

読み方： プロダクト　　

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

複数の数値の積を算出します　

PROPER関数

読み方： プロパー　　

PROPER(文字列)

英単語の先頭文字を大文字にしてそれ以降を小文字に変換する　

PV関数

読み方： ピーヴィ　　

読み方： プレゼントバリュー

PV(利率,期間,定期支払額,[将来価値],[支払期日])

現在の価値を算出します　

QUARTILE関数

読み方： クォータイル　　

QUARTILE(配列,戻り値)

0%以上100%以下の データの四分位数を算出します　

QUARTILE.EXC関数

読み方： クォータイル・ エクスクルーシブ

読み方： クォータイル・ エクスクルード

QUARTILE.EXC(配列,戻り値)

0%より大きくて100%未満のデータの四分位数を算出します　

Excel。BINOMDIST関数は、二項分布の確率を算出する関数です。【BINOMDIST】

Excel。BINOMDIST関数は、二項分布の確率を算出する関数です。

＜関数辞典：BINOMDIST関数＞

BINOMDIST関数

読み方： バイノムディスト

読み方： バイノミアルディストリビューション

分類： 互換性 

BINOMDIST(成功数,試行回数,成功率,関数形式)

二項分布の確率を算出する　

Excel。列を非表示にしても、手早くレコードの合計値が変わるようにしたい【Column total】

Excel。列を非表示にしても、手早くレコードの合計値が変わるようにしたい

＜CELL＋SUMIF関数＞

簡単そうに思えるのですが、次のような表があって、

店舗を非表示にしても、F列の合計値の値を連動して算出したいのですが、これが、簡単にできません。

F2の数式は、

=SUM(B3:E2)

です。

B列を非表示にしてみましょう。

F列の合計値は、変わっていません。

つまり、列の非表示に対応していないわけですね。

SUBTOTAL関数やAGGREGATE関数は、行の非表示には対応していますが、列の非表示には対応していないので、つかえません。

では、どのようにしたら、手早く、列の非表示に連動した合計値を算出できるのでしょうか？

着目点を変えてみましょう。

そもそも、非表示ということは、列幅がゼロということです。

列幅が0だったら計算範囲から除外することができればいいわけですね。

列幅を算出するならば、CELL関数をつかえば、算出することができます。

B6にCELL関数をつかった数式を設定します。

=@CELL("width",B1)

オートフィルで数式をコピーしています。

CELL関数の前に「＠（アットマーク）」をつけないと、スピル機能が使いされたことで、セルごとに算出できません。

あとは、F列の数式をSUMIF関数に変更します。

F2の数式を、

=SUMIF($B$6:$E$6,">0",B2:E2)

と設定します。

範囲には、$B$6:$E$6

列幅を算出した範囲です。オートフィルで数式をコピーしますので、絶対参照を忘れないようにします。

検索条件は、「">0"」。

比較演算子と数値を組み褪せて使うときには、「”（ダブルコーテーション）」で囲む必要があります。

合計範囲は、B2:E2

列を非表示にして確認してみます。

しかし、F列の数値が変わっていません。

ダメじゃんというわけではなくて、CELL関数は、自動的に再計算されないので、「F9」キーを押すか、数式タブの「再計算実行」をクリックします。

これで、再計算されましたので、F列の合計値が変わったことが確認できました。

このように、列を非表示にしても、合計値を連動して算出するには、簡単に算出することができないようですので、ちょっとアイディアが必要なようです。

Excel。今週のFacebookページの投稿 2022/5/2-2022/5/8【Trivia】

Excel。今週のFacebookページの投稿　2022/5/2-2022/5/8

＜Facebookページ＞

Facebookページで【書いてみた】Excelの豆知識（Trivia）です。

5月2日

Excel。

DROP関数

読み方： ドロップ　　

分類： 検索/行列 

DROP(array,rows,[columns])

配列から連続する指定された数の連続する行または列を除外する　

5月3日

Excel。

EXPAND関数

読み方： エクスパンド　　

分類： 検索/行列 

EXPAND(array,rows,[columns],[pad_with])

配列を指定した行数列数だけ拡大します　

5月4日

Excel。

HSTACK関数

読み方： エイチスタック　　

分類： 検索/行列 

HSTACK(array1,[array3],…)

水平方向に配列を2つの配列に積み重ね（スタック）する　

5月5日

Excel。

TAKE関数

読み方： テイク　　

分類： 検索/行列 

TAKE(array,rows,[columns])

配列から連続する指定された数の連続する行または列を返す

5月6日

Excel。

TEXTAFTER関数

読み方： テキストアフター　　

分類： 文字列操作 

TEXTAFTER(text,delimiter,[instance_num],[ignore_case])

文字を区切った後のテキストを返す

5月7日

Excel。

TEXTBEFORE関数

読み方： テキストビフォー　　

分類： 文字列操作 

TEXTBEFORE(text,delimiter,[instance_num],[ignore_case])

文字を区切る前のテキストを返す

5月8日

Excel。

TEXTSPLIT関数

読み方： テキストスピリット　　

分類： 文字列操作 

TEXTSPLIT(text,col_delimiter,row_delimiter,ignore_empty,pat_with)

区切り記号を使用してテキストを行または列に分割する　

Excel。BINOM.INV関数は、累計二項分布が基準値以上になる最小値を算出します【BINOM.INV】

Excel。BINOM.INV関数は、累計二項分布が基準値以上になる最小値を算出します

＜関数辞典：BINOM.INV関数＞

BINOM.INV関数

読み方： バイノム・インバース　　バイノミアル・インバース

読み方： バイノミアル・インバース

分類： 統計 

BINOM.INV(試行回数,成功率,α)

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

Excel。セル内の「*」だけを手早く削除したいけど、どうしたらいいの【Asterisk】

Excel。セル内の「*」だけを手早く削除したいけど、どうしたらいいの

＜置換：「~*」, REPLACE+FIND関数＞

意外と簡単な処理と思っていても、実際やってみると、上手くいかないとか、面倒な処理だったというケースは多々あります。

例えば、セル内の「*（アスタリスク）」だけを取り除きたい、消したい場合もそのケースの一つだといえます。

次の表を用意しました。

B列のセルには、「*（アスタリスク）」が含まれています。

この「*（アスタリスク）」だけを取り除きたいわけですね。

関数をつかって対応する方法もあります。

C2の数式は、

=REPLACE(B2,FIND("*",B2,1),1,"")

と設定してあります。

オートフィルを使って数式をコピーすることで、セル内から「*（アスタリスク）」を取り除くことができました。

REPLACE関数は指定の場所を置換することができる関数なので、指定の場所はFIND関数をつかいます。

「*（アスタリスク）」がセル内の左から何文字目にあるのかを算出することで、「*（アスタリスク）」を取り除くことができたわけです。

このように、関数をつかった方法でもいいのですが、もっと楽にセル内の「*（アスタリスク）」を取り除くことができます。

それは「置換」です。

置換をつかうことで、新たに列を設ける必要もありませんし、試行錯誤しなら数式を設定する必要もありません。

ただし、「置換」で処理するにあたり、注意するポイントがありますので、確認しておきましょう。

対象範囲のB2:B5を範囲選択します。

ホームタブの「検索と選択」にある「置換」をクリックします。

検索と置換ダイアログボックスが表示されます。

オプションは特に設定しません。

検索する文字列に「~*」（チルダ＋アスタリスク）と入力したら、設定完了です。

「すべて置換」をクリックします。

これで、「*（アスタリスク）」を手早く取り除くことができました。

ポイントは「~*」（チルダ＋アスタリスク）だということ。

単純に「*（アスタリスク）」を「検索する文字列」に入力して、実行すると、すべての文字が空白に置換されてしまいます。

今回のように、一見簡単そうに思える作業でも、実際処理してみると、スムーズに処理できないこともあるようですね。

また、関数を使えば、セルの途中だけなど、細かく設定することも可能なので、色々な対応方法を増やしていくといいかもしれませんね。

2022年4月の閲覧ランキングTOP10をご紹介【APRIL 2022 ranking】

2022年4月の閲覧ランキングTOP10をご紹介

＜TOP10＞

皆様に閲覧していただいた項目の2022年4月TOP10をご紹介

1位

Excel。セル内でハイフンがないものだけを手早く追加入力したい

https://infoyandssblog.blogspot.com/2022/04/excelno-hyphen.html

2位

Excel。カレンダー。曜日の欄に祝日名を手早く表示するにはどうしたらいい

https://infoyandssblog.blogspot.com/2022/04/excelholiday.html

3位

Excel。わかりやすいように、文字が入力されている行全体を塗りつぶしたい

https://infoyandssblog.blogspot.com/2022/04/excelpaint-line.html

4位

Excel。折れ線グラフに外れ値がわかるように最高・最低の2本の線を描きたい

https://infoyandssblog.blogspot.com/2022/04/excel2line-graph.html

5位

Excel。2つの数値に関連性があるのかないのか、手早く知りたい

https://infoyandssblog.blogspot.com/2022/04/excel2relevance.html

6位

Excel。新機能のスピルが邪魔なので、スピルにならない数式をつくりたい

https://infoyandssblog.blogspot.com/2022/04/excelspill.html

7位

Excel。CHOOSECOLS関数は好きな列データを抽出できるので便利です。

https://infoyandssblog.blogspot.com/2022/04/excelchoosecolschoosecols.html

8位

Excel。範囲内の数値を除いた文字だけのデータ件数を手早く算出したい

https://infoyandssblog.blogspot.com/2022/04/excelonly-letters.html

9位

Excel。一日のタイムスケジュールを管理する24時間横棒グラフを作ってみる

https://infoyandssblog.blogspot.com/2016/03/excel24hour-schedule24.html

10位

Excel。VBA。テーブル機能で抽出データをコピーする時に注意することがあります。

https://infoyandssblog.blogspot.com/2022/04/excelvbatable-copy.html