6/30/2024

Excel。VBA。きちんと大文字の半角で入力されているかチェックしたい【uppercase letter】

Excel。VBA。きちんと大文字の半角で入力されているかチェックしたい

<Excel VBA:UCase関数・StrConv関数>

入力されているデータが、すべて大文字で、しかも半角で入力されているのかを手早くチェックしたいので、Excel VBAでプログラム文をつくってみることにしました。

大文字の半角

A2:A6にサンプルのデータを入力してあります。

正解は、A4だけが、大文字の半角で入力された「A123」です。

B4に○が表示されるようにしたプログラムです。


Sub 半角チェック()

    Dim i As Long

    Dim lastrow As Long

    Dim ck As String

    Dim mt As String

    

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


    For i = 2 To lastrow

        ck = UCase(StrConv(Cells(i, "a"), vbNarrow))

        mt = Cells(i, "a")

        

        If mt = ck Then

            Cells(i, "b") = "○"

        End If

    Next

End Sub


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


B4だけに〇が表示されています。


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


最初は、変数宣言。

Dim i As Long For文の繰り返しでつかいます。

Dim lastrow As Long データの最大行番号を取得してFor文の繰り返しでつかいます

Dim ck As String 大文字の半角にしたデータを格納します。

Dim mt As String A列のデータを格納します。


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

A列の一番下のデータの行番号を取得します。


For i = 2 To lastrow

    ck = UCase(StrConv(Cells(i, "a"), vbNarrow))

    mt = Cells(i, "a")


    If mt = ck Then

        Cells(i, "b") = "○"

    End If

Next


For文は繰り返しです。

ckには、UCase関数をつかって大文字に変換します。

StrConv関数の2番目の引数のvbNarrowで半角に変換します。


要するに、UCase+StrConvのvbNarrowで「大文字の半角」に変換することができます。

このあたいをckに代入します。

元のA列のデータをmtに代入させます。


If文をつかって、

mtとckが同じ、つまり大文字の半角ということで合致したら、○を表示させる


ということを処理しています。

6/29/2024

Excel。2024/5/26-6/1にPI関数など紹介したFacebookページのコメントです。【comment】

Excel。2024/5/26-6/1にPI関数など紹介したFacebookページのコメントです。

<Facebookページ>

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

Facebookページ

5月26日

Excel。

PERCENTRANK.EXC関数

読み方: パーセントランク・エクスクルーシブ  

読み方: パーセントランク・エクスクルード

分類: 統計 

PERCENTRANK.EXC(配列,x,[有効桁数])

数値がどの位置に相当するのかを百分率で算出します 




5月27日

Excel。

PERCENTRANK.INC関数

読み方: パーセントランク・インクルーシブ  

読み方: パーセントランク・インクルード

分類: 統計 

PERCENTRANK.INC(配列,x,[有効桁数])

数値の位置を百分率で算出します 




5月28日

Excel。

PERMUT関数

読み方: パーミュテーション  

分類: 統計 

PERMUT(標本数,抜き取り数)

順序を区別して抜き出すときの順列を算出します 




5月29日

Excel。

PERMUTATIONA関数

読み方: パーミュテーション・エー  

分類: 統計 

PERMUTATIONA(数値,抜き取り数)

重複許可の順序を区分して抜き出すときの順列を算出します 




5月30日

Excel。

PHI関数

読み方: ファイ  

分類: 統計 

PHI(x)

標準正規分布の密度の値を算出します 




5月31日

Excel。

PHONETIC関数

読み方: フォネティック  

分類: 情報 

PHONETIC(参照)

文字列のフリガナ情報を取り出す 




6月1日

Excel。

PI関数

読み方: パイ  

分類: 数学/三角 

PI()

円周率の数値を算出します 

6/28/2024

Excel。新しいコードをつくるのに、ゼロで桁数を揃えたいけど、どうしたらいい【Alignment】

Excel。新しいコードをつくるのに、ゼロで桁数を揃えたいけど、どうしたらいい

<TEXT関数>

新しくコードを設定するときなど、桁数を揃えたいので、調整で0(ゼロ)をつかいたい。

ゼロで桁数を揃えたい

例えば、A列が現在のコードです。

それをC列の新コードに変更したいわけです。


C列の新コードは、頭文字がCから始まり、いままでのコードの桁を揃えたいので、0(ゼロ)で穴埋めをしています。


このような場合、手早く新コードをつくるには、どのようにしたらいいのでしょうか。


そこで、表示形式のTEXT関数をつかうことで、対応することができます。

C2に、TEXT関数の数式を設定します。

="C"&TEXT(A2,"0000")


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

これで、新しいコードをつくることができました。


TEXT関数は、表示形式をつかって、データを表示することができる関数です。


そこで、「0000」とすることで、文字がなければ、ゼロで穴埋めすることができます。


あとは、「&(アンパサンド)」をつかって、頭文字の「C」と文字結合すればいいというわけです。

6/27/2024

Excel。値を検索して、複数の列から対応する結果を抽出したい【Multi-column】

Excel。値を検索して、複数の列から対応する結果を抽出したい

<VLOOKUP関数+IF関数・XLOOKUP+INDIRECT関数>

会員と一般で、単価が異なる表から、会員なら会員の、一般なら一般の単価を抽出し表示したい場合、どのようにしたらいいのでしょうか。


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

複数の列から対応する結果を抽出

A2の所属が一般で、商品コードがB2のA01です。A5:D7の表から、商品コードがA01の

一般なので、D2の単価は、100と表引きされています。


A2の値を会員にすれば、D2の単価は80となるようにしたいわけです。


このような場合、VLOOKUP関数をつかうといいように思えますが、うまくいきません。


C2の商品名は、VLOOKUP関数で対応することは、できます。

C2の数式は、

=VLOOKUP(B2,A6:D7,2,FALSE)


VLOOKUP関数の最初の引数、検索値は、B2

2つ目の引数は、範囲なので、A6:D7 と設定します。

3つ目の引数は、列番号です。

2つ目の引数で設定した範囲の左から何列目のデータを表示するのかということなので、左から「2」列目なので、2と設定します。

最後の引数の、検索方法は、完全一致ですから、FALSE。


商品名は、VLOOKUP関数でもいいのですが、問題は、列番号です。


会員ならば、3。一般なら4としなければなりません。


このような場合、

=VLOOKUP(B2,A6:D7,IF(A2="会員",3,4),FALSE)

と列番号を判断させるようにIF関数をつかってもいいと思います。


ただし、所属数が会員・準会員・一般・学生のように、増えた場合、IF関数では対応するのが大変になるので、多分岐できる関数を使う必要がでてきます。


また、所属、それぞれの表をつくって、INDIRECT関数をつかって対応する方法があります。

ただ、その方法では、別表を作る必要がありますので、面倒です。


そこで、INDIRECT関数はつかうのですが、VLOOKUP関数よりもXLOOKUP関数で対応する方法があります。


事前に「名前の定義」を設定します。

C6:C7に、「会員」

D6:D7に、「一般」

と名前を設定しました。


D2の数式は、

=XLOOKUP(B2,A6:A7,INDIRECT(A2),"",0,1)

A2の所属を変更しても、会員と一般、それぞれの数値を表引きしてくれます。


もし、所属数が増えても、名前の定義を増やすだけで、数式を変更する必要はありません。


では、

=XLOOKUP(B2,A6:A7,INDIRECT(A2),"",0,1)

XLOOKUP関数の数式を確認します。


最初の引数は、検索値 なので、B2を設定します。


2つ目の引数は、検索範囲。

検索値が所属されている範囲なので、A6:A7。


3つ目の引数は、戻り範囲。

表示したい範囲です。

ここに、会員なのか、一般なのかで、戻り範囲を対応させたい。


そこで、INDIRECT関数をつかうことで、戻り範囲を変更させています。


INDIRECT関数は、値そのものをつかうことができます。

名前の定義で、会員と一般を設定していますので、切り替えることができるというわけです。


4つ目の引数は、見つからない場合。

見つからない時は「””(ダブルコーテーション×2)」で空白と設定します。


5つ目の引数は、一致モードなので、0の完全一致にしました。


6つ目の引数は、検索モード。

先頭から検索させますので、1と設定します。


このように、XLOOKUP関数とINDIRECT関数を組み合わせることで、値を検索して、複数の列から対応する結果を抽出することができます。

6/26/2024

Excel。条件で分岐して異なる計算結果を返すのがIF関数です。【IF】

Excel。条件で分岐して異なる計算結果を返すのがIF関数です。

<関数辞典:IF関数>

IF関数

読み方: イフ  

分類: 論理 

IF関数

IF(論理式,[値が真の場合],[値が偽の場合])

条件で分岐して異なる計算結果を返す

6/25/2024

Excel。合計行がある表で、並べ替えをするときは、範囲選択が重要です。【Youtube】

Excel。合計行がある表で、並べ替えをするときは、範囲選択が重要です。

<並べ替え>

昇順や降順のボタンや並べ替えのボタンをつかって、手早く並べ替えができます。

ところが、帳票のような下側に合計行などの集計行があるときに、そのままボタンだけで処理をすると、合計行などの集計行が、とんでもないことになってしまいます。


その解決方法は、範囲選択なんですね。


6/24/2024

Excel。複数の項目を組み合わせて検索するには、どうすればいいの。【Table pull】

Excel。複数の項目を組み合わせて検索するには、どうすればいいの。

<XLOOKUP関数>

検索値が1つならば、VLOOKUP関数などをつかうことで、手早く検索して抽出することができます。


では、次の表のように、2つの項目が抽出条件の場合、どのようにしたら、該当するデータを抽出することができるのでしょうか。

XLOOKUP関数

A1:D7にリストがあります。


検索したい条件は、

F1:G2にあります。


検索条件は、

店舗名は 新宿

フロアーは 2

この2つの項目に合致する販売金額を G3に抽出したいわけです。


VLOOKUP関数の場合だと、店舗名とフロアーを合体させた列を作って…など、色々考えないといけません。


そこで、XLOOKUP関数をつかえば、手早く抽出することができます。


G3にXLOOKUP関数の数式を設定します。

=XLOOKUP(G1&G2,B2:B7&C2:C7,D2:D7,"",0,1)


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


では、数式を確認します。

最初の引数は、「検索値」には、G1&G2 と設定します。

検索値が2つありますので、「&(アンパサンド)」で結合します。


2つ目の引数は、「検索範囲」。

ここも検索範囲が2つありますので、「&(アンパサンド)」で結合させます。

よって、B2:B7&C2:C7


3つ目の引数は、「戻り範囲」は、抽出結果なので、D2:D7 と設定します。


4つ目の引数は、「見つからない場合」です。

見つからない場合は「””(ダブルコーテーション×2)」の空白と設定しました。


5つ目の引数は、「一致モード」です。VLOOKUP関数でいうところに、検索方法に該当します。

完全一致で対応させたいので、「0(ゼロ)」と設定します。


最後の6つ目の引数は、「検索モード」。

先頭データから検索するかなど、検索方法を設定することができます。


大きなデータではないので、「1」の先頭から最終行へ向けて検索する方法にしました。

6/23/2024

Excel。2024/5/19-5/25にPEARSON関数など紹介したFacebookページのコメントです。【comment】

Excel。2024/5/19-5/25にPEARSON関数など紹介したFacebookページのコメントです。

<Facebookページ>

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

Facebookページ

5月19日

Excel。

OR関数

読み方: オア  

分類: 論理 

OR(論理式1,[論理式2],…)

複数の条件のいずれか1つを満たすかどうかを調べる 




5月20日

Excel。

PDURATION関数

読み方: ピーデュレーション  

分類: 財務 

PDURATION(利率,現在価値,将来価値)

目標価値になるまでの投資期間を算出します Period DURATIONの略




5月21日

Excel。

PEARSON関数

読み方: ピアソン  

分類: 統計 

PEARSON(配列1,配列2)

ピアソンの積率相関係数を算出する 




5月22日

Excel。

PERCENTILE関数

読み方: パーセンタイル  

分類: 互換性 

PERCENTILE(配列,率)

0%以上100%以下の データの百分位数を算出する 




5月23日

Excel。

PERCENTILE.EXC関数

読み方: パーセンタイル・ エクスクルーシブ  

読み方: パーセンタイル・ エクスクルード

分類: 統計 

PERCENTILE.EXC(配列,率)

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




5月24日

Excel。

PERCENTILE.INC関数

読み方: パーセンタイル・ インクルーシブ  

読み方: パーセンタイル・ インクルード

分類: 統計 

PERCENTILE.INC(配列,率)

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




5月25日

Excel。

PERCENTRANK関数

読み方: パーセントランク  

分類: 互換性 

PERCENTRANK(配列,x,[有効桁数])

数値の位置を百分率で算出します

6/22/2024

Excel。超幾何分布の確率を算出するのが、HYPGEOMDIST関数です。【HYPGEOMDIST】

Excel。超幾何分布の確率を算出するのが、HYPGEOMDIST関数です。

<関数辞典:HYPGEOMDIST関数>

Excel。

HYPGEOMDIST関数

読み方: ハイパージオムディスト  

読み方: ハイパージオメトリックディストリビューション

分類: 互換性 

HYPGEOMDIST関数

HYPGEOMDIST(標本の成功数,標本数,母集団の成功数,母集団の大きさ)

超幾何分布の確率を算出します

6/21/2024

Excel。折れ線グラフ。データにその日付はないのに、横軸に日付が表示されるのをやめたい【Horizontal axis】

Excel。折れ線グラフ。データにその日付はないのに、横軸に日付が表示されるのをやめたい

<折れ線グラフ>

連続している日付ではない表があります。

 

一応、5日置きではありますが、このデータから、マーカー付き折れ線グラフをつくってみます。


A1:B5を範囲選択します。


挿入タブのグラフにある、「折れ線/面グラフの挿入」のマーカー付き折れ線グラフをクリックします。


マーカー付き折れ線グラフが挿入されます。

折れ線グラフ

注目するのは、横軸です。

表示ない、日付が横軸に表示されていることがわかります。


たしかに、この表ならば、マーカー付き折れ線グラフではなく、集合縦棒グラフの方がマッチしているとは思います。


ですが、マーカー付き折れ線グラフにしたいけど、表示されているデータがない日付は見えないようにしたいわけです。


このトラブルの対応方法ですが、次のように処理をすれば解決します。


横(項目)軸をクリックします。

書式タブの選択対象の書式設定をクリックします。


右側に、軸の書式設定作業ウィンドウが表示されます。


軸のオプションにある軸の種類を「データを基準に自動的に選択する」から「テキスト軸」に変更するだけです。


マーカー付き折れ線グラフの横軸が変わりました。


Excelが気を使って、自動的に処理をしたというのが原因でした。


このように、日付が横軸の場合、表に無い日付を表示することもありますので、その時は、「テキスト軸」に変更することで対応できます。

6/20/2024

Excel。便利になった。チェックボックスを挿入できるようになりました。【Checkbox】

Excel。便利になった。チェックボックスを挿入できるようになりました。

<チェックボックス>

いままで、☑をセルに表示させるには、Excel VBAをつかうとか、あるいは、文字で☑を入力するという方法を使っていました。


ところが、Excel Insider版の挿入タブに、チェックボックスが登場しました。

チェックボックス

これがとても便利です。

一刻も早く、Microsoft 365のExcelにも追加してほしいぐらいです。


なんといっても、使い勝手がいい。


設定する方法は、セルをクリックして、チェックボックスをクリックする。


その後、オートフィルでコピーしてもいい。


その逆で、範囲選択をしておいて、チェックボックスをクリックすれば、範囲選択内のセルすべてにチェックボックスを挿入することができます。


そして、☑が何件あるのかも、COUNTIF関数を使うことで対応することができます。


☑ならば、TUREで、□ならば、FALSEということなので、


B6には、

=COUNTIF(C2:C4,TRUE)

という数式をつくることで、☑が何件あるのかを手早く算出することもできます。


しかも、マクロ有効ブックで保存しなくてもいいんです。

そのまま、xlsxファイルとして保存することができます。

6/19/2024

Excel・Word・PowerPoint。グラデーションの塗りつぶしの設定方法【Gradation】

Excel・Word・PowerPoint。グラデーションの塗りつぶしの設定方法

<図形>

知らないと全く設定できない。「グラデーションの設定方法」


図形やグラフの塗りつぶしを、オリジナルのグラデーションで設定する方法をご紹介しています。

6/18/2024

Excel。手早く、上位20%の平均値を算出するには、どうすればいいの。【Top 20%】

Excel。手早く、上位20%の平均値を算出するには、どうすればいいの。

<AVERAGEIF+PERCENTILE.INC関数>

データの平均値を算出するには、AVERAGE関数をつかえば、算出することができます。


次の表をつかって確認します。

上位20%

F2には、

=AVERAGE(B2:B11)

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

オートSUMボタンにある平均をつかってもいいですね。


では、上位20%の平均値を算出したい場合は、どのようにしたらいいのでしょうか。


考え方として、上位20%なのかを調べる必要があります。

20%圏内ならば、平均値の対象になります。


そこで、登場するのがPERCENTILE.INC関数です。


この関数は、データ全体の上位何%(今回は20%)のポジションの値を算出することができます。


F4に

=PERCENTILE.INC(B2:B11,80%)

と数式をつくりました。


これで、上位20%の数値が算出できました。


その結果、88.2


あとは、この結果以上の値のデータだけの平均値を算出すればいいというわけです。


ところで、

=PERCENTILE.INC(B2:B11,80%)

の2番目の引数の「率」に注意が必要です。


上位20%だから20%と設定しがちですが、上位20%というのは、80%の位置にあたりますので、100-上位率 の数値を設定します。


F2に上位20%の平均値を算出します。

F4の88.2以上が平均値算出の条件です。


条件付きの平均値ということで、使う関数は「AVERAGEIF関数」です。


=AVERAGEIF(B2:B11,">="&F4)


F2には、91と算出できました。


データも88.2以上のものは、89と93しかないので、その平均値は91ですので、合致しております。


今回は、説明の為、F4に一度、上位20%の数値を算出しましたが、一つの数式にしても、問題はありません。


=AVERAGEIF(B2:B11,">="&PERCENTILE.INC(B2:B11,80%))

6/17/2024

Excel。HYPGEOM.DIST関数で、超幾何分析の累計確率か確率密度を求められます。【HYPGEOM.DIST】

Excel。HYPGEOM.DIST関数で、超幾何分析の累計確率か確率密度を求められます。

<関数辞典:HYPGEOM.DIST関数>

HYPGEOM.DIST関数

読み方: ハイパージオム・ディスト  

読み方: ハイパージオメトリック・ディストリビューション

分類: 統計 

HYPGEOM.DIST関数


HYPGEOM.DIST(標本の成功数,標本数,母集団の成功数,母集団の大きさ,関数形式)

超幾何分析の累計確率か確率密度を算出する 

6/16/2024

Excel。2024/5/12-5/18にOFFSET関数など紹介したFacebookページのコメントです。【comment】

Excel。2024/5/12-5/18にOFFSET関数など紹介したFacebookページのコメントです。

<Facebookページ>

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

Facebookページ

5月12日

Excel。

OCT2HEX関数

読み方: オクトトゥヘックス  

読み方: オクタルトゥヘキサデジマル

分類: エンジニアリング 

OCT2HEX(数値,[桁数])

8進数を16進数に変換する 




5月13日

Excel。

ODD関数

読み方: オッド  

分類: 数学/三角 

ODD(数値)

数値を奇数に切り上げる 




5月14日

Excel。

ODDFPRICE関数

読み方: オッドエフプライス  

読み方: オッドファーストプライス

分類: 財務 

ODDFPRICE(受渡日,満期日,発行日,初回利払日,利率,利回り,償還価額,頻度,[基準])

最初の利払期間が半端な利付債の現在価格を算出します 




5月15日

Excel。

ODDFYIELD関数

読み方: オッドエフイールド  

読み方: オッドファーストイールド

分類: 財務 

ODDFYIELD(受渡日,満期日,発行日,初回利払日,利率,現在価値,償還価額,頻度,[基準])

最初の利払期間が半端な利付債の利回りを算出します 




5月16日

Excel。

ODDLPRICE関数

読み方: オッドエルプライス  

読み方: オッドラストプライス

分類: 財務 

ODDLPRICE(受渡日,満期日,最終利払日,利率,利回り,償還価額,頻度,[基準])

最後の利払期間が半端な利付債の現在価格を算出します 




5月17日

Excel。

ODDLYIELD関数

読み方: オッドエルイールド  

読み方: オッドラストイールド

分類: 財務 

ODDLYIELD(受渡日,満期日,最終利払日,利率,現在価値,償還価額,頻度,[基準])

最後の利払期間が半端な利付債の利回りを算出する 




5月18日

Excel。

OFFSET関数

読み方: オフセット  

分類: 検索/行列 

OFFSET(参照,行数,列数,[高さ],[幅])

基準のセルからの相対位置を指定する

6/15/2024

Access。ドットで区切られた日付を、日付型にするにはどうしたらいいの【DATE】

Access。ドットで区切られた日付を、日付型にするにはどうしたらいいの

<Access:CDate関数>

データを読み込んでみたところ、日付が「.(ドット)」で区切られた日付でした。


デザインビューでデータ型を確認したところ、短いテキスト型でした。


要するに、文字型だったわけです。これを日付型にしたいわけです。


単純にデータ型を変更しても、日付にはわかってくれません。


そこで、クエリをつかって、日付型として使えるようにします。


作成タブのクエリデザインをつかいます。


まず、テストという演算フィールドをつくってみました。


CDate関数をつかえば、日付型にすることができます。

「.」であったとしても、日付型に変わってくれるのではと考えたわけです。


そしてもう一つは、テスト2という演算フィールドをつくりました。


テスト2: CDate(Replace([日付],".","/"))


演算フィールドの説明は、後に回すとして、実行してみましょう。

ドットで区切られた日付を、日付型にする

演算フィールドのテストは、#エラーとなっています。


CDate関数だけでは、対応は無理のようです。

つまり「.(ドット)」のままではダメなようです。


そこで、

テスト2: CDate(Replace([日付],".","/"))

Replace関数をつかって、「.(ドット)」を「/」に置換させてから、CDate関数で日付型に変更しました。


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


Replace関数は、置換することができる関数です。


なお、Replace関数で、「.(ドット)」を「/」に置換するだけでは、文字型のままで、日付型になりません。

6/14/2024

Excel。HYPERLINK関数で、他のドキュメントへのリンクを作成します【HYPERLINK】

Excel。HYPERLINK関数で、他のドキュメントへのリンクを作成します

<関数辞典:HYPERLINK関数>

HYPERLINK関数

読み方: ハイパーリンク  

分類: 検索/行列 

HYPERLINK関数

HYPERLINK(リンク先,[別名])

他のドキュメントへのリンクを作成します


6/13/2024

Excelのショートカットキー。Shift+F7~F12を紹介【shortcut】

Excelのショートカットキー。Shift+F7~F12を紹介

<Shiftキー+Function系キー>

作業効率もUPする、知っていると便利なショートカットキー。

Excelのショートカットキー

Shift+F7

類似語辞典作業ウィンドウを表示する



Shift+F8

方向キーを使用して、隣接しないセルまたは範囲を選択範囲に追加する。



Shift+F9

作業中のワークシートのみ「再計算」する



Shift+F10

コンテキスト メニューを開きます。



Shift+F11

ワークシートを新規追加する



Shift+F12

上書き保存 Ctrl+Sと同じ

6/12/2024

Excel。最大値と最小値を除いた平均値をLET関数で算出してみる【Excluding】

Excel。最大値と最小値を除いた平均値をLET関数で算出してみる

<SUM+MAX+MIN+COUNT関数・LET関数>

最大値と最小値を除いた平均値は、AVERAGE関数では算出することができません。


AVERAGE関数をつかわないで、平均値を算出するには、範囲の総和を範囲の総和した件数で除算すれば、算出することができます。

LET関数で算出してみる

C2:C6まで、売上金額が入力されています。


この範囲の最大値と最小値を除いた平均値を算出するために、D8に数式を設定しました。


=(SUM(C2:C6)-MAX(C2:C6)-MIN(C2:C6))/(COUNT(C2:C6)-2)


長いですが、シンプルな関数しかつかっていません。

だけど、C2:C6という範囲選択が何度も登場してきます。


数式を説明すると、

SUM(C2:C6)-MAX(C2:C6)-MIN(C2:C6)

SUM関数で、合計を算出します。

その値から、MAX関数で最大値をMIN関数で最小値を減算します。


この値を

COUNT(C2:C6)-2 の算出結果で除算します。


COUNT関数で数値の件数を算出することができます。

「-2」するのは、最大値と最小値のデータを除いた件数にする必要があるからです。


この数式で算出して、何ら問題はありません。


今回は、この数式をLET関数で、算出してみます。


LET関数は、数式内に「名前の定義」というか、「変数」を設定することができる関数です。


ただ、どのように使うのか、わかりにくい関数といわれています。


そこで、比較的わかりやすい 範囲選択 を名前に設定してみます。


D9にLET関数をつかって数式をつくります。

LET関数

=LET(rg,C2:C6,(SUM(rg)-MAX(rg)-MIN(rg))/(COUNT(rg)-2))


数式の文字数としては、差はあまりありませんが、C2:C6を何度もつかわないので、可読性は改善されています。


では、この数式の説明をします。


LET関数の最初の引数は、名前。

プログラムでいうところに変数。あるいは、名前の定義です。


今回は、範囲選択なので、Excel VBAでお馴染みのRangeの「rg」としました。


2つ目の引数は、式。

式となっていますが、数式でもいいし、範囲でも指定できます。

繰り返し設定するところを設定するといいので、「C2:C6」としました。


この名前と式を複数設定することも出来ます。


3つ目は、計算式。

SUM(rg)-MAX(rg)-MIN(rg))/(COUNT(rg)-2


C2:C6だったところが、rgという名前を設定しましたので、rgにすることで、C2:C6と入力しなくてもいいわけです。


このように、LET関数をつかうと、複雑な数式の時に、可読性の改善と、数式もコンパクトにすることができるかもしれません。

6/11/2024

Excel。16進数を10進数に戻すことができるのが、HEX2DEC関数です。【base number】

Excel。16進数を10進数に戻すことができるのが、HEX2DEC関数です。

<HEX2DEC関数>

ネットワークなどでお馴染みの16進数。

 

HEX2DEC関数

16進数を10進数に変換することができるのが、HEX2DEC関数です。


B2には、

=HEX2DEC(A2)

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


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


A2がaと入力されていて、B2の結果は10と算出されていることが確認できます。


A6のFFは、255と算出されています。

このように、16進数を10進数に変換することができます。

6/10/2024

Excel。横長の表で使える。列単位で並べ替えをしたいけど、どうしたらいいの。【Youtube】

Excel。横長の表で使える。列単位で並べ替えをしたいけど、どうしたらいいの。

<並べ替え>

普通並べ替えは、行単位での並べ替えを行うようになっています。

では、列単位で並べ替えるには、どのようにしたらいいのでしょうか。

6/09/2024

Excel。VBA。オートフィルターをつかって、空白のデータだけを抽出したい【blank】

Excel。VBA。オートフィルターをつかって、空白のデータだけを抽出したい

<Excel VBA AutoFilterメソッド>

データを読み込んだ後に、オートフィルターをつかって、空白のセルを、手早く抽出したい。


単純な作業ですが、単純な作業な上、毎回毎回だと、面倒になってきます。


そこで、Excel VBAで、オートフィルターをつかって、空白のセルを抽出するプログラム文を用意しておくことにしました。


使うのは次の表です。

AutoFilterメソッド

C列の提出確認で空白のセルが、未提出なので、空白のセルを抽出して、未提出の状況を確認したいわけです。


オートフィルターを設定して、空白のセルだけを抽出する設定は、☑マークのオンオフが地味ですが、オンオフを切り替えないといけないので、面倒です。


そこで、Excel VBAで次のようなプログラム文を作ってみました。


Sub オートフィルター空白抽出()

    Range("a1").AutoFilter field:=3, Criteria1:="="

End Sub


たった一行です。

では実行してみましょう。


このように、C列の提出確認が空白のセルだけをオートフィルターをつかって、抽出することができました。


たった一行だけですが、用意しておくと便利です。


プログラム文のポイントは、

Range("a1").AutoFilter field:=3, Criteria1:="="


Criteria1:="="

Criteria1は、条件ですが、空白のセルを意味する比較演算子をつかった「”=”」です。


なお、

Criteria1:=""

でも、空白セルを抽出することができます。


ただ、空白でないセルを抽出する場合には、

Criteria1:="<>"

と、「”=”」のように、比較演算子をつかって設定する必要があります。

6/08/2024

Excel。HSTACK関数は水平方向に配列を2つの配列に積み重ね(スタック)します。【HSTACK】

Excel。HSTACK関数は水平方向に配列を2つの配列に積み重ね(スタック)します。

<関数辞典:HSTACK関数>

HSTACK関数

読み方: エイチスタック  

分類: 検索/行列 

HSTACK関数

HSTACK(array1,[array3],…)

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

6/07/2024

Excel。2024/5/5-5/11にNPER関数など紹介したFacebookページのコメントです。【comment】

Excel。2024/5/5-5/11にNPER関数など紹介したFacebookページのコメントです。

<Facebookページ>

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

Facebookページ

5月5日

Excel。

NOW関数

読み方: ナウ  

分類: 日付時刻 

NOW()

現在の日付と時刻を算出します 




5月6日

Excel。

NPER関数

読み方: エヌパー  

読み方: ナンバー・オブ・ピリオド

分類: 財務 

NPER(利率,定期支払額,現在価値,[将来価値],[支払期日])

元利均等返済における支払回数を算出します 




5月7日

Excel。

NPV関数

読み方: エヌピーヴイ  

読み方: ネット・プレゼント・バリュー

分類: 財務 

NPV(割引率,値1,[値2],…)

キャッシュフローに基づいた正味現在価値を算出します 




5月8日

Excel。

NUMBERSTRING関数

読み方: ナンバーストリング  

NUMBERSTRING(数値,書式)

数値を漢数字に変換する




5月9日

Excel。

NUMBERVALUE関数

読み方: ナンバーバリュー  

分類: 文字列操作 

NUMBERVALUE(文字列,[小数点記号],[桁区切り記号])

特定の地域に依存しない方法で文字列を数値に変換する 




5月10日

Excel。

OCT2BIN関数

読み方: オクトトゥビン  

読み方: オクタルトゥバイナリ

分類: エンジニアリング 

OCT2BIN(数値,[桁数])

8進数を2進数に変換する 




5月11日

Excel。

OCT2DEC関数

読み方: オクトトゥデック  

読み方: オクタルトゥバイナリデジマル

分類: エンジニアリング 

OCT2DEC(数値)

8進数を10進数に変換する

6/06/2024

Excel。全て入力されているか、手早くチェックするにはどうしたらいい【Entered】

Excel。全て入力されているか、手早くチェックするにはどうしたらいい

<IF+COUNT関数>

入力欄のすべてにデータが入力されているかをチェックしたい場合には、件数を算出する関数をつかいます。

IF+COUNT関数

今回のデータは、1回目から3回目の測定値が入力されている表です。


3回目まで終わっているならば、E列に○と表示させるようにしています。


まず、判断が必要になりますので、IF関数をつかいます。


そして、IF関数の論理式には、3回目、すなわち、3件データがあるかどうかを調べればいいわけです。


そこで、E2には、次の数式を設定しました。

=IF(COUNT(B2:D2)=3,"○","")

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


件数を算出するわけですが、すべて数値なので、COUNT関数をつかっています。


もし、文字列とか、数値や文字列が混在している場合にはCOUNTA関数をつかいます。

=IF(COUNTA(B2:D2)=3,"○","")


その結果が3と等しいか判断させればいいというわけです。


条件付きの件数なので、COUNTIF関数やCOUNTIFS関数をつかってもいいですが、条件がシンプルなので、IF関数とCOUNT関数、あるいは、IF関数とCOUNTA関数で対応することができます。

6/05/2024

Excel。時刻から"時"を算出するのが、HOUR関数です。【HOUR】

Excel。時刻から"時"を算出するのが、HOUR関数です。

<関数辞典:HOUR関数>

HOUR関数

読み方: アワー  

分類: 日付時刻 

HOUR関数

HOUR(シリアル値)

時刻から"時"を算出する 

6/04/2024

Excel。途中計算など文字や数値を非表示にすることができます。【Youtube】

Excel。途中計算など文字や数値を非表示にすることができます。

<表示形式>

途中計算など文字や数値を非表示にすることができます。これで、列や行を非表示にしなくても大丈夫です。


途中計算などを表示したまま印刷すると、ちょっとカッコが悪いというか、お客さん相手の資料なら、途中計算の結果を表示することはできませんね。


列や行を非表示にしてもいいのですが、Excelファイルを見せる場合は、列や行を非表示にすると、列や行番号がかけているのがわかります。


つまり、「非表示」にしたことがわかってしまいます。


そこで、表示形式をつかうことで『セル内の文字を非表示』にすることができます。

6/03/2024

Excel。個人情報保護で氏名の前後の文字を除いて伏字で表示したい【blank letters】

Excel。個人情報保護で氏名の前後の文字を除いて伏字で表示したい

<LEFT・REPT+LEN・RIGHT関数>

氏名など、個人情報保護上、表示することができない場合があります。

伏字

A列の氏名をB列のように、前後の1文字は表示して、中の文字は伏字。

今回は全角の「*(アスタリスク)」に置き換えて表示したい場合の方法をご紹介します。


B2に、数式を設定します。

=LEFT(A2,1)&REPT("*",LEN(A2)-2)&RIGHT(A2,1)


最初の文字は、LEFT関数をつかいます。

LEFT(A2,1)

これで、左から1文字を抽出することができます。


これを「&(アンパサンド)」で結合します。


伏字の部分の数式が

REPT("*",LEN(A2)-2)

REPT関数の中にあるLEN関数から説明します。


LEN関数は文字数を算出する関数です。

その値から前後1文字分ずつ、つまり「-2」することで、伏字の数がわかります。


そして、REPT関数ですが、

REPT関数は、指定した文字列を指定した回数繰り返す関数です。


最初の引数の文字列に「”*”」とします。これが、伏字の「*」です。


2つ目の引数は、繰り返し回数です。

先程説明しましたLEN関数で繰り返し回数を算出しています。


最後に、RIGHT関数をつかって、右側の1文字を抽出し、結合することで、完成します。


6/02/2024

Excel。HLOOKUP関数は、横方向の表からデータを検索して抽出できます【HLOOKUP】 

Excel。HLOOKUP関数は、横方向の表からデータを検索して抽出できます

<関数辞典:HLOOKUP関数>

HLOOKUP関数

読み方: エイチルックアップ  

分類: 検索/行列 

HLOOKUP関数

HLOOKUP(検索値,範囲,行番号,[検索方法])

横方向の表からデータを検索して抽出する

6/01/2024

Excel。2024/4/28-5/4にNOT関数など紹介したFacebookページのコメントです。【comment】

Excel。2024/4/28-5/4にNOT関数など紹介したFacebookページのコメントです。

<Facebookページ>

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

Facebookページ

4月28日

Excel。

NORMINV関数

読み方: ノーマルインバース  

分類: 互換性 

NORMINV(確率,平均,標準偏差)

累積確立から正規分布の数値を逆算する 




4月29日

Excel。

NORM.INV関数

読み方: ノーマル・インバース  

分類: 統計 

NORM.INV(確率,平均,標準偏差)

累積確立から正規分布の数値を逆算する 




4月30日

Excel。

NORMSDIST関数

読み方: ノーマルスタンダードディスト  

読み方: ノーマルスタンダードディストリビューション

分類: 互換性 

NORMSDIST(z)

標準正規分布の累積確率を算出する 




5月1日

Excel。

NORM.S.DIST関数

読み方: ノーマル・スタンダード・ディスト  

読み方: ノーマル・スタンダード・ディストリビューション

分類: 統計 

NORM.S.DIST(z,関数形式)

標準正規分布の累積確率を算出します 




5月2日

Excel。

NORMSINV関数

読み方: ノーマルスタンダードインバース  

分類: 互換性 

NORMSINV(確率)

累積確立から標準正規の数値を逆算する 




5月3日

Excel。

NORM.S.INV関数

読み方: ノーマル・スタンダード・インバース  

分類: 統計 

NORM.S.INV(確率)

累積確立から標準正規の数値を逆算する 




5月4日

Excel。

NOT関数

読み方: ノット  

分類: 論理 

NOT(論理式)

指定した条件が成立しないことを調べる