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

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

＜Facebookページ＞

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

3月10日

Excel。

LENB関数

読み方： レンビー　　

分類： 文字列操作 

LENB(文字列)

文字列のバイト数を返す　

3月11日

Excel。

LET関数

読み方： レット　　

分類： 論理 

LET(名前1,名前値1,計算または名前2,[名前値2,…)

計算結果を名前に割り当てます。　

3月12日

Excel。

LINEST関数

読み方： ラインエスティー　　

読み方： ラインエスティメーション

分類： 統計 

LINEST(既知のy,[既知のx],[定数],[補正])

重回帰直線の各係数を算出します　

3月13日

Excel。

LN関数

読み方： ログ・ナチュラル　　

分類： 数学/三角 

LN(数値)

オイラー数eとする数値の対数を算出します　

3月14日

Excel。

LOG関数

読み方： ログ　　

分類： 数学/三角 

LOG(数値,[底])

指定した数を底とする数値の対数を算出する　

3月15日

Excel。

LOG10関数

読み方： ログテン　　

読み方： ベース・テン・ログ

分類： 数学/三角 

LOG10(数値)

10を底とする数値の対数を算出する　

3月16日

Excel。

LOGEST関数

読み方： ログイーエスティー　　

読み方： ログエスティメーション

分類： 統計 

LOGEST(既知のy,[既知のx],[定数],[補正])

複数の独立変数の回帰指数曲線の係数を算出する　

Excel。集合縦棒グラフの背面を平均値以上で塗り分けるには、どうしたらいい。【plot area】

Excel。集合縦棒グラフの背面を平均値以上で塗り分けるには、どうしたらいい。

＜集合縦棒グラフ＋AVERAGE関数＞

集合縦棒グラフの背面を平均値以上かそうでないかを、視覚的にわかるように、塗り分けをしたいのです。

ただ、図形で塗りつぶすのでは、綺麗にできません。

次のようなグラフを作りたいわけです。

そこで、何か良い方法はないのでしょうか。

Excelのグラフは、作りたいグラフを表見するためのデータが必要です。

平均値と塗り分けるための数値を含めた表を作ります。

D列には、平均値を算出します。この値が、区分けの数値です。

D2の数式は、

=AVERAGE($C$2:$C$8)

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

E列には、平均値からグラフのプロットエリアの上限を塗るための数値を算出しました。

販売金額の最高値が、1175なので、グラフのプロットエリアの上限を1200としました。

E2には、

=1200-D2

という数式を設定し、オートフィルで数式をコピーしています。

これで、表の準備が完了しましたので、グラフをつくっていきます。

B1:E8を範囲選択します。

挿入のグラフにある、「おすすめグラフ」をクリックします。

グラフの挿入ダイアログボックスが表示されます。

すべてのグラフタブにします。

組み合わせをクリックします。

販売金額を「集合縦棒」

平均値と1200-平均値は、ともに、第2軸として、「積み上げ面」に設定します。

設定後OKボタンをクリックします。

妙なグラフができましたが、気にせずに、設定作業を進めます。

左側の縦軸と右側の第2軸縦軸の最大値を1200に変更します。

書式タブをクリックして、「縦（値）軸」を選択したら、選択対象の書式設定をクリックします。

画面右側に「軸の書式設定」作業ウインドウが表示されます。

境界値の最大値を1200に変更します。

同じように、右側の第2軸縦（値）軸の最大値を1200で揃えます。

プロットエリアの上限まで塗りつぶすことはできましたが、左右を塗りつぶせていません。

そのためには、「第2横軸」を表示します。

第2横軸を修正することで、左右を塗りつぶすことができます。

グラフの右上にあるグラフ要素の＋をクリックします。

軸に当てて、第2横軸にチェックマークをいれます。

グラフは、ますます、おかしくなりますが、気にせずに、作業をすすめます。

プロットエリアの上部に表示された「第2軸横軸」をクリックします。

軸の書式設定作業ウインドウの「軸のオプション」にある「軸位置」を目盛に変更します。

そして、グラフの右側に表示されている「第2軸縦軸」をクリックします。

軸のオプションにある「横軸との交点」を自動に変更します。

どの軸をアクティブにしているのかを確認しながら作業をおこなっていきましょう。

塗り分け自体は完成しましたが、第2軸　横軸や第2軸　縦軸が不要です。

不要な要素の処理をします。

第2軸　横軸を削除すると、元に戻ってしまいます。

そのため、第2軸　横軸は非表示にする処理をおこないます。

第2軸　横軸を選択します。

軸のオプションの「目盛の種類」を「なし」。

「ラベルの位置」を「なし」に設定します。

第2軸　縦軸は、DELキーで削除してもグラフに影響はありませんので、第2軸　縦軸を削除します。

あとは、フォントを変更する。塗りつぶした色を変更すれば、完成です。

Excel。GESTEP関数で、数値が境界値以上かを判定できます。【GESTEP関数】

Excel。GESTEP関数で、数値が境界値以上かを判定できます。

＜関数辞典：GESTEP関数＞

GESTEP関数

読み方： ジーイーステップ　　

分類： エンジニアリング 

GESTEP(数値,[しきい値])

数値が境界値以上かを判定する

Word。自動更新する日付をWordで設定するには、どうしたらいいの。【Automatic updating】

Word。自動更新する日付をWordで設定するには、どうしたらいいの。

＜Word＞

納品書・請求書で必須の自動更新する日付。

Excelでは、自動更新する日付を設定するには、TODAY関数をつかいます。

では、Wordでは、どのようにしたら、自動更新する日付を設定することができるのでしょうか。

Excel。平均以上のデータの行全体を手早く塗りつぶすにはどうしたらいいの【above average】

Excel。平均以上のデータの行全体を手早く塗りつぶすにはどうしたらいいの

＜条件付き書式+AVERAGE関数＞

平均値以上のデータは、どこなのかを、見てわかるようにしたいのですが、どのようにしたらいいのでしょうか。

条件により、塗りつぶしをしたいわけですから、「条件付き書式」をつかうわけです。

問題になってくるのが、条件です。

ホームタブの条件付き書式にある上位/下位ルールにある、「平均より上」をつかうと、平均値より大きい値のセルを塗りつぶすことができます。

ただし、今回は「以上」ですし、データ全体ということですから「行全体」を塗りつぶしたいわけです。

よって、条件式を設定しなければなりません。

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

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

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

「数式を使用して、書式設定するセルを決定」を選択します。

条件のボックスに、条件式を設定します。

=$C2>=AVERAGE($C$2:$C$8)

あとは、書式ボタンをクリックし、塗りつぶす色を設定したら、OKボタンをクリックすれば、平均以上のデータの行全体を塗りつぶすことができます。

行全体を対象にする場合には、「$C2」のように、列番号を固定した複合参照に設定することで対応することができます。

Excel。週間売上を手早く算出するには、どのようにしたらいいの。【weekly sales】

Excel。週間売上を手早く算出するには、どのようにしたらいいの。

＜WEEKNUM関数＞

日々の売上データで、年間売上なら、YEAR関数をつかって年を算出してから年ごとに算出します。

また、月間なら、MONTH関数をつかって算出することができます。

では、日曜日から土曜日までの週間売上はどのようにしたらいいのでしょうか。

週を算出することができる関数があります。

それが、WEEKNUM関数です。

D2に

=WEEKNUM(A2,1)

という数式を設定して、オートフィルで数式をコピーします。

すると、その日が第何週なのかを算出することができます。

また、WEEKNUM関数の2つ目の引数ですが、今回は日曜日から土曜日までを週としていますので、「1」と設定します。

週が算出できましたので、あとは週ごとに集計します。

G1にSUMIF関数をつかって週ごとの集計をすれば、週ごとの集計を算出することができます。

G1の数式は、

=SUMIF($D$2:$D$16,F1,$C$2:$C$16)

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

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

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

＜Facebookページ＞

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

3月3日

Excel。

KURT関数

読み方： カート　　

分類： 統計 

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

データセットの尖度（せんど）を算出します　

3月4日

Excel。

LAMBDA関数

読み方： ラムダ　　

分類： 論理 

LAMBDA(パラメータまたは計算,…)

カスタムの再利用可能な関数を作成し、それらを表示名で呼び出します

3月5日

Excel。

LARGE関数

読み方： ラージ　　

分類： 統計 

LARGE(配列,順位)

指定した○番目に大きい値を算出します　

3月6日

Excel。

LCM関数

読み方： エルシーエム　　

分類： 数学/三角 

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

整数の最小公倍数を算出します　

3月7日

Excel。

LEFT関数

読み方： レフト　　

分類： 文字列操作 

LEFT(文字列,[文字数])

文字列の左端から文字を取り出す　

3月8日

Excel。

LEFTB関数

読み方： レフトビー　　

分類： 文字列操作 

LEFTB(文字列,[バイト数])

文字列の左端から指定バイト数の文字を返す　

3月9日

Excel。

LEN関数

読み方： レン　　

分類： 文字列操作 

LEN(文字列)

文字列の文字数を返す　

Access。クエリ。ExcelのEOMONTH関数のように月末日を算出したい【end of month】

Access。クエリ。ExcelのEOMONTH関数のように月末日を算出したい

＜DateSerial関数＞

テーブルの納品日フィールドの日付から同月の月末日を算出するには、どうしたらいいのでしょうか。

ExcelのEOMONTH関数のようにしたいわけです。

では、作成タブのクエリデザインをつかって、クエリをつくっていきます。

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

月末支払日: DateSerial(Year([納品日]),Month([納品日])+1,0)

演算式が長いので、ズーム機能をつかってみてもいいでしょう。

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

納品日と同月の月末日を算出することができました。

設定した演算フィールドのポイントは、月を+1して、日は「0（ゼロ）」とする点です。

翌月0日にすることで、前月末日にすることができます。

Excel。GEOMEAN関数で数値の相乗平均を算出できます。【GEOMEAN】

Excel。GEOMEAN関数で数値の相乗平均を算出できます。

＜関数辞典：GEOMEAN関数＞

GEOMEAN関数

読み方： ジオミーン　　

分類： 統計 

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

数値の相乗平均を算出する　Geometricの略

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

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

＜Facebookページ＞

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

2月25日

Excel。

ISODD関数

読み方： イズオッド　　

分類： 情報 

ISODD(数値)

対象が奇数の場合にTRUEを返す　

2月26日

Excel。

ISOMITTED関数

読み方： イズオミテッド　　

分類： 情報 

ISOMITTED(argument)

LAMBDAの値がないかどうかを確認し、TRUEまたはFALSEを返す　

2月27日

Excel。

ISOWEEKNUM関数

読み方： アイエスオーウィークナム　　

読み方： アイエスオーウィークナンバー

分類： 日付時刻 

ISOWEEKNUM(日付)

ISO週番号を算出する　

2月28日

Excel。

ISPMT関数

読み方： アイエスピーエムティー　　

読み方： イズ・ペイメント

分類： 財務 

ISPMT(利率,期,期間,現在価値)

元利均等返済における指定期間の利息を算出します　Lotus1-2-3互換性維持

2月29日

Excel。

ISREF関数

読み方： イズリファレンス　　

分類： 情報 

ISREF(テストの対象)

対象がセル参照の場合にTRUEを返す　

3月1日

Excel。

ISTEXT関数

読み方： イズテキスト　　

分類： 情報 

ISTEXT(テストの対象)

対象が文字列の場合にTRUEを返す　

3月2日

Excel。

JIS関数

読み方： ジス　　

分類： 文字列操作 

JIS(文字列)

半角文字を全角に変換する

Excel。複数列を基準にして、手早く並べ替えた別の表をつくりたい。【SORT】

Excel。複数列を基準にして、手早く並べ替えた別の表をつくりたい。

＜SORTBY関数＞

複数列をつかった条件で並べ替えをしました。

その後、別の表へコピーする場合、作業的には、難しくはありませんが、面倒です。

そこで、SORTBY関数をつかうと、手早く別の表で並べ替えた状態で表示することができます。

次の表を用意しました。

処理したいことは、A1:D7の表を、クラス順の得点は降順に並べ替えた状態で別の表を作りたいわけです。

複数列の並べ替えなので、データタブの並べ替えをつかい、その後コピーすればいいわけです。

ただ、SORTBY関数をつかうと、並べ替えの作業も、コピーも、まとめて処理してくれます。

それでは、F1に次の数式を設定します。

=SORTBY(A2:D7,C2:C7,1,D2:D7,-1)

たった、この数式だけで、対応することができます。

オートフィルで数式をコピーする必要はありません。

スピル機能により、数式がコピーされます。

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

範囲なので、見出し行を除いた、「A2:D7」を設定します。

スピル機能のため、絶対参照は不要です。

2つ目の引数は、「基準配列1」です。

これは、最初の条件の並べ替え範囲のことを指しています。

クラス順にしたいので、「C2:C7」。絶対参照は不要です。

3つ目の引数は、「並べ替え順序1」です。

これは、「1」なら昇順。

「-1」なら降順の設定をおこないます。

クラス順なので、昇順ですから「1」と設定します。

2つ目と3つ目の引数を条件数分繰り返すことで、複数列で並べ替えを設定することができます。

なお、SORT関数という並べ替えをする関数もあります。

SORT関数でも複数列での並べ替えは、可能ですが、SORT関数は、一度に複数列での並べ替えができないので、SORT+SORT関数のネストにしなければ対応することができません。

Excel。整数の最大公約数は、GCD関数で算出します【GCD】

Excel。整数の最大公約数は、GCD関数で算出します　

＜関数辞典：GCD関数＞

GCD関数

読み方： ジーシーディー　　

読み方： グレーテスト・コモン・ディバイザー

分類： 数学/三角 

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

整数の最大公約数を算出します　

Excelのショートカットキー。Ctrl＋Altキーを紹介【shortcut】

Excelのショートカットキー。Ctrl＋Altキーを紹介

＜Ctrl+Shiftキー＋ジャンプ系キー＞

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

Ctrl＋Alt＋V

形式を選択して貼り付けダイアログボックスが表示されます。

Ctrl＋Alt＋=

拡大表示します。

Ctrl＋Alt＋-

縮小表示します。

Ctrl＋Alt＋F5

ブック内のすべてのデータを更新します。

Ctrl＋Alt＋F9

最終変更に関わらず開いているブックのすべてのワークシートを再計算

Excel。VBA。太い外枠と格子の罫線をたった2行で手早く設定できます。【ruled line】

Excel。VBA。太い外枠と格子の罫線をたった2行で手早く設定できます。

＜Excel VBA：BorderAroundメソッド＞

表を作成するたびに、外側は、太い外枠。

そして内側は、格子を設定するのは、単純作業ですが、面倒です。

そこで、Excel VBAをつかってプログラムをつくってみます。

Sub 罫線太い外枠と格子()

    Range("b2").CurrentRegion.Borders.LineStyle = True

    Range("b2").CurrentRegion.BorderAround Weight:=xlThick

End Sub

あとは、実行して確認します。

たった2行ですが、太い外枠と格子を設定することができました。

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

格子を設定しているのが、1行目です。

Range("b2").CurrentRegion.Borders.LineStyle = True

CurrentRegionプロパティは、B2を始点として連続するセルを表と判断するプロパティです。

これで、表全体を範囲選択することができます。

Borders.LineStyle = True

Bordersは、罫線を意味します。

LineStyle = True　は、格子を設定します。

ちなみに、LineStyle = False　とすれば、格子を削除することができます。

つまり、「枠なし」にすることができます。

2行目が、外側の太い外枠です。

Range("b2").CurrentRegion.BorderAround Weight:=xlThick

BorderAroundメソッドは、外枠の設定を行います。

Weight:=xlThick　で、線を太くする設定をしております。

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

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

＜Facebookページ＞

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

2月18日

Excel。

ISERROR関数

読み方： イズエラー　　

分類： 情報 

ISERROR(テストの対象)

対象がエラー値の場合にTRUEを返す　

2月19日

Excel。

ISEVEN関数

読み方： イズイーブン　　

分類： 情報 

ISEVEN(数値)

対象が偶数の場合にTRUEを返す　

2月20日

Excel。

ISFORMULA関数

読み方： イズフォーミュラー　　

分類： 情報 

ISFORMULA(参照)

セルに数式が含まれている場合にTRUEを返す　

2月21日

Excel。

ISLOGICAL関数

読み方： イズロジカル　　

分類： 情報 

ISLOGICAL(テストの対象)

対象が論理値の場合にTRUEを返す　

2月22日

Excel。

ISNA関数

読み方： イズエヌエー　　

分類： 情報 

ISNA(テストの対象)

対象がエラー値の#N/Aの場合にTRUEを返す　

2月23日

Excel。

ISNONTEXT関数

読み方： イズノンテキスト　　

分類： 情報 

ISNONTEXT(テストの対象)

対象が文字列でない場合にTRUEを返す　

2月24日

Excel。

ISNUMBER関数

読み方： イズナンバー　　

分類： 情報 

ISNUMBER(テストの対象)

対象が数値の場合にTRUEを返す　

Excel。写真を正円でトリミングするには、どのようにしたらいいのでしょうか。【trimming】

Excel。写真を正円でトリミングするには、どのようにしたらいいのでしょうか。

＜便利な機能＞

Excel・Word・PowerPointでつかる、便利な機能は、いろいろあります。

今回は、写真を正円でトリミングするには、どのようにしたらいいのでしょうか。

トリミングに「図形に合わせてトリミング」というのがあります。

ただ、「正円」でトリミングしようとしても、「楕円」しか「図形」に用意されていません。

では、どのようにしたら、「正円」でトリミングをすることができるのでしょうか。

「図形に合わせてトリミング」だけでは、正円にすることができないので、「縦横比1:1」を合わせてつかうことで、正円でトリミングすることができます。

Excel。データを入力したら自動で通し番号を入力するようにしたい。【serial number】

Excel。データを入力したら自動で通し番号を入力するようにしたい。

＜SEQUENCE+COUNTA関数＞

データが入力されると自動的に通し番号が入力できれば、ほんの少しかもしれませんが、入力作業も改善できるかもしれません。

では、どのようにしたら、データを入力したら、自動的に通し番号を入力できるのでしょうか。

今回は、SEQUENCE関数をつかった方法をご紹介していきます。

次の表を用意しました。

 

B列に利用者名を入力すると、A列に通し番号が自動的に入力するようにしたいわけです。

A2にSEQUENCE関数をつかった数式を設定します。

=SEQUENCE(COUNTA(B:B)-1)

あとは、スピル機能によって、ゴーストが発生しますので、オートフィルで数式をコピーする必要はありません。

B列にデータが入力されるたびに、自動的に、スピル機能によって、通し番号が自動的に入力してくれます。

数式を確認しておきましょう。

SEQUENCE関数は、「数列を返す」関数です。

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

そこで、COUNTA関数でB列のデータに入力されている件数を算出させて、見出し行分を減算しますので、「-1」を追記ます。

これで、通し番号を入力することができます。

なんで、SEQUENCE関数をつかうのでしょうか。

それは、スピル機能に対応した関数だということ。

自動的に拡張してくれます。

次のような関数でも、同じような処理をすることができます。

=IF(F2<>"",ROW()-1,"")

ただ、この数式では、数式をコピーする必要があるということです。

今回は、新しい関数であるSEQUENCE関数をつかってみました。

Excel。なぜ数式を入力しても、算出されないで数式で表示されてしまうの【string】

Excel。なぜ数式を入力しても、算出されないで数式で表示されてしまうの

＜表示形式：文字列＞

ちょっとしたことで、どうして・なんでそうなるの。ということがExcelで発生します。

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

C2に、単価×数量なので、=A2*B2と入力しても、数式が表示されてしまい、計算結果を表示してくれません。

原因は、ホームタブにある「数値の形式」。

たまたまなのか、以前そのような書式の設定をしたままだったのか、理由はわかりませんが、「文字列」になっています。

これでは、数式も文字列で表示されてしまうというわけです。

「文字列」から「標準」に戻してから、再度、数式を設定することで、対応することができます。

Excel。指定した標準偏差の範囲になる確率を算出するならGAUSS関数です。【GAUSS】

Excel。指定した標準偏差の範囲になる確率を算出するならGAUSS関数です。

＜関数辞典：GAUSS関数＞

GAUSS関数

読み方： ガウス　　

分類： 統計 

GAUSS(x)

指定した標準偏差の範囲になる確率を算出します

Excel。条件付きで空白セルを数えたい場合、どのようにしたらいいの。【blank cell】

Excel。条件付きで空白セルを数えたい場合、どのようにしたらいいの。

＜COUNTBLANK関数・COUNTIFS関数＞

計算結果が空白でも、元から空白でも、セルが空白にみえるなら、その空白セルの件数を算出するのが、COUNTBLANK関数です。

ところが、COUNTBLANK関数は条件がついてしまうと、対応することができません。

なぜかというと、COUNTBLANK関数の引数は、「範囲」だけしかないからです。

そして、この引数の「範囲」は、連続した範囲でないといけません。

つまり「,（カンマ）」で範囲を追加することもできません。

では、次のような表の場合、どのようにしたら、条件付きの空白セルの件数を算出することができるのでしょうか。

 

B列の上期下期が「上期」でC列の提出確認が「空白（空欄）」のセルの件数を算出するには、COUNTBLANK関数がつかえません。

そこで、COUNTIFS関数をつかえば、算出することができます。

E2に設定した数式は、

=COUNTIFS(B2:B9,"上期",C2:C9,"")

COUNTIFS関数は、複数条件で件数を算出することができる関数です。

なので、空白セルに条件を付けた場合は、COUNTIFS関数で対応することができます。

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

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

＜Facebookページ＞

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

2月11日

Excel。

INT関数

読み方： イント　　

読み方： インテジャー

分類： 数学/三角 

INT(数値)

最も近い整数に切り下げる　

2月12日

Excel。

INTERCEPT関数

読み方： インターセプト　　

分類： 統計 

INTERCEPT(既知のy,既知のx)

回帰直線の切片を算出します　

2月13日

Excel。

INTRATE関数

読み方： イントレート　　

分類： 財務 

INTRATE(受渡日,満期日,投資額,償還価額,[基準])

満期に償還される証券の利率を算出します　

2月14日

Excel。

IPMT関数

読み方： アイピーエムティー　　

読み方： インタレストペイメント

分類： 財務 

IPMT(利率,期,期間,現在価値,[将来価値],[支払期日)]

元利均等返済における指定期間の利息を算出します　

2月15日

Excel。

IRR関数

読み方： アイアールアール　　

分類： 財務 

IRR(範囲,[推定値])

定期キャッシュフローに対する内部利益率を算出します　

2月16日

Excel。

ISBLANK関数

読み方： イズブランク　　

分類： 情報 

ISBLANK(テストの対象)

対象が空白セルの場合にTRUEを返す　

2月17日

Excel。

ISERR関数

読み方： イズイーアールアール　　

読み方： イズエラー

分類： 情報 

ISERR(テストの対象)

対象がエラー値の#N/A以外の場合にTRUEを返す

Excel。ガンマ関数の値の自然対数はGAMMALN.PRECISE関数で算出できます。【GAMMALN.PRECISE】

Excel。ガンマ関数の値の自然対数はGAMMALN.PRECISE関数で算出できます。

＜関数辞典：GAMMALN.PRECISE関数＞

GAMMALN.PRECISE関数

読み方： ガンマログナチュラル・プリサイス　　

分類： 統計 

GAMMALN.PRECISE(x)

ガンマ関数の値の自然対数を算出します　

Excel。AND関数の欠点。ワイルドカードがつかえないので、どうしたらいいの。【trouble】

Excel。AND関数の欠点。ワイルドカードがつかえないので、どうしたらいいの。

＜AND関数・IF+COUNTIFS関数＞

AかつBという条件をつくることができる「AND関数」ですが、欠点があります。

それが、ワイルドカードをつかった条件式には対応してくれないということです。

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

判定しないのは、所属はAからはじまり、かつ、住所には横浜市が含まれるデータなら、「○」を表示して、そうでなければ、「空欄（空白）」としたい。

AND関数をつかってみたところ、思っているように算出されません。

C2に設定した数式は、

=IF(AND(A2="A*",B2="*横浜市*"),"○","")

AND関数とワイルドカードの組み合わせは、うまくいかないようです。

AND関数で、ワイルドカードをつかわない数式にすれば、算出することはできます。

ワイルドカードを使わない数式は、

=IF(AND(MID(A2,1,1)="A",MID(B2,5,3)="横浜市"),"○","")

ただ、MID関数をつかうので、可読性が下がってしまいます。

そこで、COUNTIFS関数をつかうことで、対応することができます。

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

=IF(COUNTIFS(A2,"A*",B2,"*横浜市*"),"○","")

COUNTIFS関数は、ワイルドカードをつかった条件をつかうことができます。

数式を確認しておきましょう。

IF関数の引数であるCOUNTIFS関数から説明します。

COUNTIFS関数の最初の引数が、「検索条件範囲1」です。A2を設定します。

2番目の引数は、「検索条件1」。ここに「”A*”」とワイルドカードをつかっています。

「A*」とすれば、「Aで始まる」という意味です。

3番目以降は条件範囲と、条件の繰り返しです。

B2のあたいに、「*横浜市*」なのかを確認する条件式を設定します。

「*横浜市*」はB2に「横浜市という文字が含まれる」という意味です。

この2つの条件が合致したら「TRUE」、合致しない場合は「FALSE」と判定結果がでます。

IF関数をつかってTRUEならば「”○”」と表示します。

Excel。データを「配列定数」形式の文字列で表示するにはどうしたらいいの。【array constant】

Excel。データを「配列定数」形式の文字列で表示するにはどうしたらいいの。

＜ARRAYTOTEXT関数＞

テータを指定した文字で連結するには、「TEXTJOIN関数」をつかえば対応することができます。

列の区切りを「,」（カンマ）で、行の区切りを「;」（セミコロン）、全体を「{ }」で囲む、「配列定数」にしたい場合には、TEXTJOIN関数では対応するのが大変です。

そこで、ARRAYTOTEXT関数をつかうことで、配列定数の形式にした文字列に変換できます。

A2:B3のデータを配列定数の形式の文字列に変換します。

A5に次の数式を設定しました。

=ARRAYTOTEXT(A2:B3,1)

これで、全体を{}で囲み、列の区切りは「,」で行の区切りを「;」の文字列にすることができました。

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

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

範囲なので、A2:B3と設定します。

2つ目の引数は、「書式」です。

0の簡潔を選択すると、すべてを「,」（カンマ）で区切ってくれます。

1の正確ならば、全体を{}で囲み、列の区切りは「,」で行の区切りを「;」の配列定数で区切ってくれます。

なお、このARRAYTOTEXT関数。

文字列に変換するので、日付は、シリアル値に変わってしまうので、注意が必要です。

Excel。Ctrlキーを押しながらオートフィルをドラッグする合わせ技で入力が楽になります。【Easy input】

Excel。Ctrlキーを押しながらオートフィルをドラッグする合わせ技で入力が楽になります。

＜便利な機能＞

日付や曜日を手早く入力する。

あるいは、数式をコピーするときに、つかっているオートフィル。

ただ、ドラッグしているだけではもったいない。

Ctrlキーを押しながらオートフィルをドラッグすると、連番が簡単に入力できます。

そして、日付は同じ日付を入力することができます。

Excel。「.」と「,」が逆のフランス式の数字を数値にしてつかうには、どうするの【french style】

Excel。「.」と「,」が逆のフランス式の数字を数値にしてつかうには、どうするの

＜NUMBERVALUE関数＞

日頃使っている数値。

桁区切りの記号は「,（カンマ）」で小数点は「.（ドット）」です。

これは、英語圏を中心とした場合で、フランスなど欧州などでは、逆で表示された数字を使っています。

つまり、桁区切りの記号が「.（ドット）」で小数点が「,（カンマ）」というわけです。

この条件で入力した数値は、文字型になってしまいます。

よって、そのままでは、計算で使おうとすると、文字型なので、#VALUE!というエラーが表示されてしまいます。

計算式としてつかうためには、通常の数字にしなければなりません。

そこで、NUMBERVALUE関数をつかって、文字型を数値に変更します。

B3に設定した数式は、

=NUMBERVALUE(B1,",",".")

これで、B1のフランス式の文字を数値として変換することができます。

では、設定した数式を確認しておきましょう。

最初の引数は、「文字列」。

B1を設定します。

フランス式で入力した場合数値として認識してくれません。

文字列になっています。

2つ目の引数は、「小数点記号」。

B1の文字列の中で、小数点でつかっている記号をExcelに教える必要があります。

小数点で使用しているのは「,」なので、「","」と設定します。

最後の引数は、「桁区切り記号」。

2つ目の引数と同じように、B1の文字列の中で、桁区切りでつかっている記号をExcelに教えるので「”.”」を設定します。

そもそも、NUMBERVALUE関数をつかわなければいけないのかというと、VALUE関数ではパーセントや通貨とみなせる文字列データを数値に変換できます。

ただし、「.」と「,」はVALUE関数に対応していないので、NUMBERVALUE関数を使う必要があるというわけです。

Excel。GAMMALN関数はガンマ関数の値の自然対数を算出します。【GAMMALN】

Excel。GAMMALN関数はガンマ関数の値の自然対数を算出します。

＜関数辞典：GAMMALN関数＞

GAMMALN関数

読み方： ガンマログナチュラル　　

分類： 統計 

GAMMALN(x)

ガンマ関数の値の自然対数を算出する

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

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

＜Facebookページ＞

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

2月4日

Excel。

IMSUB関数

読み方： アイエムサブ　　

読み方： イマジナリーサブトラクト

分類： エンジニアリング 

IMSUB(複素数1,複素数2)

複素数の差を算出する　

2月5日

Excel。

IMSUM関数

読み方： アイエムサム　　

読み方： イマジナリーサム

分類： エンジニアリング 

IMSUM(複素数1,[複素数2],…)

複素数の和を算出する　

2月6日

Excel。

IMTAN関数

読み方： アイエムタンジェント　　

読み方： イマジナリータンジェント

分類： エンジニアリング 

IMTAN(複素数)

複素数のタンジェントを算出する　

2月7日

Excel。

INDEX関数

読み方： インデックス　　

分類： 検索/行列 

INDEX(配列,行番号,[列番号])

セル範囲から縦横座標で値を抽出します　配列形式

2月8日

Excel。

INDEX関数

読み方： インデックス　　

分類： 検索/行列 

INDEX(参照,行番号,[列番号],[領域番号])

セル範囲から縦横座標で値を抽出します　セル範囲形式

2月9日

Excel。

INDIRECT関数

読み方： インダイレクト　　

分類： 検索/行列 

INDIRECT(参照文字列,[参照形式])

文字列で参照されるセルの値を算出します　

2月10日

Excel。

INFO関数

読み方： インフォ　　

読み方： インフォメーション

分類： 情報 

INFO(検査の種類)

Excelの動作環境に関する情報を返す　

Excel。VBA。表の見出しや小計・総計は残して、数値のデータだけを削除したい【Delete only numbers】

Excel。VBA。表の見出しや小計・総計は残して、数値のデータだけを削除したい

＜SpecialCellsメソッド＞

見出し行や集計行などは、残してデータの数値だけを削除したい。

範囲選択をして削除するだけですが、範囲選択が面倒になってきます。

B2:D4とB6:D8のデータを削除したいわけです。

そこで、Excel VBAでプログラム文をつくってみることにしました。

Sub 数値のみ削除()

Range("a1").CurrentRegion.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents

End Sub

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

B2:D4とB6:D8のデータだけを削除することができました。

数式や見出し行は残すことができました。

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

Range("a1").CurrentRegion.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents

Range("a1").CurrentRegion　で、A1を起点とした連続したデータを範囲選択します。

SpecialCellsメソッドを使用すると、指定した条件に該当するセルを取得することができます。

xlCellTypeConstantsは、定数が含まれているセルという意味です。

さらに、xlNumbersというValueの設定値は、”数値”を意味します。

つまり、『数値の入っているセル』という意味になります。

よって、A1を起点とした連続する、数値が入力されているセルを範囲選択して、削除（ClearContents）するという処理ができるというわけです。

たった一行なので、用意していてもいいかもしれませんね。

Excel。複数のセル内の文字を指定した文字で、手早く連結したい【connect letters】

Excel。複数のセル内の文字を指定した文字で、手早く連結したい

＜TEXTJOIN関数＞

クレジットカードやIPアドレスなど、例えば、4桁ごとにわかれて入力されているとします。

そのわかれているセルとセルを「-（ハイフン）」で、手早く連結するには、どのようにしたらいいのでしょうか。

次の表で説明します。

 

A1:D5に4桁の数値が入力されている表があります。

A2:D2までの文字列を「-（ハイフン）」で連結したのが、F2です。

文字結合の「&（アンパサンド）」だと、セルとセルの間に「&”-“&」を入力して連結させる必要があります。

そうなると、連結するセルが増えれば増えるほど、面倒になります。

そこで、「TEXTJOIN関数」をつかうことで、手早く連結することができます。

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

=TEXTJOIN("-",TRUE,A2:D2)

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

これだけで、「-（ハイフン）」で連結した文字列をつくることができます。

=TEXTJOIN("-",TRUE,A2:D2)　の数式を確認していきます。

最初の引数は、「区切り文字」。

連結したい文字を設定します。今回は「-（ハイフン）」で連結します。

2つ目の引数は、「空のセルは無視」。

選択したセルに文字が入力されていない場合、無視するならば、TRUE。

無視しないならば、FALSEを設定します。無視しますので、TRUEを設定しました。

3つ目の引数は、テキスト1。

連結したいセルまたは、範囲です。

A2:D2を範囲選択しますので、A2:D2　と設定します。

Excel。文字を統一する関数。全角のJIS関数と半角のASC関数を紹介【character】

Excel。文字を統一する関数。全角のJIS関数と半角のASC関数を紹介

＜JIS関数・ASC関数＞

データベースにおいて、文字の揺らぎなどが原因で、抽出などに影響がでます。

データは半角で統一されているのか、全角で統一されているのか、確認修正するために、全角のJIS関数と半角のASC関数があります。

そのJIS関数とASC関数を紹介しております。

Excel。複数条件を指定して、手早く別表に抽出したい【extraction】

Excel。複数条件を指定して、手早く別表に抽出したい

＜FILTER関数＞

オートフィルターをつかうことで、データを抽出することは、簡単にできます。

しかし、抽出したデータを、別の場所にコピーして貼り付けるとなると、単純な作業ですが、面倒です。

そこで、FILTER関数をつかうことで、抽出し、別の場所に抽出結果を表示することができます。

抽出条件をF1:G2に用意しました。

店舗名が新宿店で、商品名が消しゴムのデータを抽出して、F5を起点に結果を表示していきます。

F5に設定する数式は、

=FILTER(A2:D11,(B2:B11=G1)*(C2:C11=G2),"該当データなし")

これだけで、抽出して、さらに、別なところに貼り付けることができます。

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

つまりデータのことなので、A2:D11を設定します。

2番目の引数は、「含む」。

条件です。

FILTER関数で複数条件を作る時には、「*（アスタリスク）」で接続させていきます。

(B2:B11=G1)*(C2:C11=G2)と設定します。

また、スピル機能によって数式の範囲が広がりますので、絶対参照にする必要はありません。

3番目の引数は、「空の場合」。

これは、抽出するデータがなかった時、どうしますかということなので、「該当データなし」と表示するようにしました。

Excelのショートカットキー。Ctrl＋Shiftキーとジャンプ系キーを紹介【shortcut】

Excelのショートカットキー。Ctrl＋Shiftキーとジャンプキー系を紹介

＜Ctrl+Shiftキー＋ジャンプ系キー＞

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

Ctrl＋Shift＋Home

選択範囲をワークシートの先頭のセルまで拡張する。

Ctrl＋Shift＋End

選択範囲を最後のセル (右下隅) まで拡張する。

Ctrl＋Shift＋Space

全選択。Ctrl+Aと同じ

Ctrl＋Shift＋Space

図形などオブジェクトを選択時すべてのオブジェクトを選択する

Ctrl＋Shift＋Tab

ブックを切り替える

Ctrl＋Shift＋Page Up

現在のシートと前のシートを選択する。

Ctrl＋Shift＋Page Down

現在のシートと次のシートを選択する。

Excel。ガンマ累積分布関数の逆関数の値を算出できるのが、GAMMAINV関数です。【GAMMAINV】

Excel。ガンマ累積分布関数の逆関数の値を算出できるのが、GAMMAINV関数です。

＜関数辞典：GAMMAINV関数＞

GAMMAINV関数

読み方： ガンマインバース　　

分類： 互換性 

GAMMAINV(確率,α,β)

ガンマ累積分布関数の逆関数の値を算出する

Access。クエリ。独自の順番でデータを並べ替えるにはどうしたらいいの。【SORT】

Access。クエリ。独自の順番でデータを並べ替えるにはどうしたらいいの。

＜クエリ:並べ替え＞

次のようなテーブルがあります。

店舗名を、新宿・品川・渋谷の順番で並べ替えたいとします。

店舗名を昇順にしても、降順にしても、この順番で並べ替えることはできません。

また、Excelのユーザー設定リストのようなものは、Accessには用意されていません。

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

それでは、対応方法を紹介していきます。

最初に、Excelのユーザー設定リストのような並べ替えたい順番のテーブルを用意します。

作成タブのクエリデザインをつかって、クエリを作成していきます。

並べ替えを行いたいテーブルと並べ替えの順番となるテーブルを用意します。

両テーブル間にリレーションシップを設定します。店舗どうしで設定します。

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

今回は、並べ替えしたいテーブルのフィールドをすべて使用します。

最後尾に、並べ替えの順番になるテーブルから、「店舗NO」を追加します。

追加したフィールドは、あくまでも、並べ替えを行うためのものなので、非表示にします。

実行して確認します。

店舗名が、新宿・品川・渋谷の順番で並んでいることが確認できます。

このように、Excelのユーザー設定リストに該当するテーブルを用意することで、独自の順番で並べ替えをすることができます。

ところで、店舗NOで並べ替えを行った影響で、販売日が、降順の状態になっています。

折角なので、販売日を昇順にしておきましょう。

店舗NOに昇順の並べ替えを設定します。

並べ替え用に新たに、販売日を追加して、昇順に設定・非表示にします。

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

並べ替えを複雑にしたクエリもつくることができます。