4/30/2024

Excel。集計の新しい関数。GROUPBY関数が便利すぎ!Insider版だけではもったいない【GROUPBY】

Excel。集計の新しい関数。GROUPBY関数が便利すぎ!Insider版だけではもったいない

<GROUPBY関数>

2023年11月にExcel 365インサイダー版に新しく追加されたGROUPBY関数が、便利すぎなので、早速紹介します。


次の表を用意しました。

GROUPBY関数

A1:D11に表があります。


店舗ごとの金額合計を求めます。

そして、金額の総合計も算出し、さらに、金額合計は降順にしたいとします。


いままででしたら、どうやって、集計するのかを考えていました。

ところが、GROUPBY関数だけで、処理できるのです。


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

=GROUPBY(B2:B11,D2:D11,SUM,,1,1)

たったこれだけです。


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

驚きの新関数。GROUPBY関数です。


引数は、英語なので、日本語訳はどのようになるのかは、いまのところわかりませんが、次のようになっています。


GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array])


最初の、「row_fields」は、集計行のようです。

今回は店舗別の集計をしたいので、B2:B11 と設定します。


2つ目の引数は、「values」なので、合計したい範囲なので、,D2:D11 と設定します。


3つ目の引数は、「function」なので、関数。

つまり、集計方法ですね。

直接関数名を入力する必要がありそうです。

合計を算出したいので、SUM と設定します。


この3つまでが、最低限必要な引数です。


4番目の引数は、「[field_headers]」で、見出し行についてです。


5番目の引数は、「[total_depth]」で、集計行を表示するかどうかの設定ができます。「1」を設定すると、総計を表示することができます。


6番目の引数は、「[sort_order]」で、並べ替えの設定です。

「-1」が昇順で「1」が降順です。


7番目の引数は、「[filter_array])」です。


色々な関数と組み合わせたり、GROUPBY関数単体でも可能性を秘めている関数のように思えますので、試してみたい関数ですね。

4/29/2024

Excel。横棒グラフの縦軸を反転して表を同じ順番にする【horizontal bar graph】

Excel。横棒グラフの縦軸を反転して表を同じ順番にする

<横棒グラフ>

Excelでは、簡単に横棒グラフを作成することができます。


ただ、縦軸の並び順が、表と逆になっています。


縦軸を、どうやったら、表と同じ順番にすることができるのかという対応方法を紹介しています。


なお、お恥ずかしい限りですが、「軸の反転」を「軸の回転」といってしまっておりますので、ご了承いただけますと幸いです。

4/28/2024

Excel。通貨スタイルの「¥」は、桁数によって位置がバラバラなので揃えたい。【currency style】

Excel。通貨スタイルの「¥」は、桁数によって位置がバラバラなので揃えたい。

<表示形式>

お馴染みの「¥」マークですが、数値の先頭に表示されるため、桁数によって、一がバラバラで表示されます。


できたら、位置を揃えたいと思ったら、どのようにしたらいいのでしょうか。


そこで、表示形式を、「会計」にしてみましょう。


セルの書式設定ダイアログボックスを表示するのもいいですが、ホームタブに用意されていますので、そちらの方法をご紹介します。


C2:C5を範囲選択します。


ホームタブの数値の書式ボックスにある▼をクリックして、展開します。


その中にある「会計」をクリックします。


すると、会計の表示形式が設定されました。


「¥」マークは数値の桁数に関係なく、揃っていることが確認できます。

ただし、「通貨スタイル」と「会計」で違う点は、数値が「負数」の時です。

通貨スタイル

通貨スタイルは、文字の色が赤色に変わりますが、会計はそのままで、赤色には変わりません。

4/27/2024

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

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

<Facebookページ>

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

Facebookページ

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],[定数],[補正])

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

4/26/2024

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軸 縦軸を削除します。

集合縦棒グラフの背面を平均値以上で塗り分ける

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

4/25/2024

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

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

<関数辞典:GESTEP関数>

GESTEP関数

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

分類: エンジニアリング 

GESTEP関数

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

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

4/24/2024

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

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

<Word>

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

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


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

4/23/2024

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

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

<条件付き書式+AVERAGE関数>

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

条件付き書式+AVERAGE関数

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


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


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


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


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


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

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


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

条件付き書式+AVERAGE関数

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


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

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


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


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

4/22/2024

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

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

<WEEKNUM関数>

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

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


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

WEEKNUM関数

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

それが、WEEKNUM関数です。


D2に

=WEEKNUM(A2,1)

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


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


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



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


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


G1の数式は、

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

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

4/21/2024

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

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

<Facebookページ>

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

Facebookページ

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(文字列)

文字列の文字数を返す 

4/20/2024

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

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

<DateSerial関数>

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


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

DateSerial関数

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

DateSerial関数

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


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


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


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

DateSerial関数

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


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


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

4/19/2024

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

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

<関数辞典:GEOMEAN関数>

GEOMEAN関数

読み方: ジオミーン  

分類: 統計 

GEOMEAN関数

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

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

4/18/2024

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

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

<Facebookページ>

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

Facebookページ

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(文字列)

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

4/17/2024

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

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

<SORTBY関数>

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

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


そこで、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関数のネストにしなければ対応することができません。

4/16/2024

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

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

<関数辞典:GCD関数>

GCD関数

読み方: ジーシーディー  

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

分類: 数学/三角 

GCD関数

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

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

4/15/2024

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

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

<Ctrl+Shiftキー+ジャンプ系キー>

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

Excelショートカットキー

Ctrl+Alt+V

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



Ctrl+Alt+=

拡大表示します。



Ctrl+Alt+-

縮小表示します。



Ctrl+Alt+F5

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



Ctrl+Alt+F9

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

4/14/2024

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 で、線を太くする設定をしております。

4/13/2024

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

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

<Facebookページ>

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

Facebookページ

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を返す 

4/12/2024

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

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

<便利な機能>

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


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


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

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


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


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

4/11/2024

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

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

<SEQUENCE+COUNTA関数>

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


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


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

次の表を用意しました。

SEQUENCE+COUNTA関数

 

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


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

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


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


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


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


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


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

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


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


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

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

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


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

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

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


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

4/10/2024

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

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

<表示形式:文字列>

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


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

文字列

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


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


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


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


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

4/09/2024

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

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

<関数辞典:GAUSS関数>

GAUSS関数

読み方: ガウス  

分類: 統計 

GAUSS関数

GAUSS(x)

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

4/08/2024

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

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

<COUNTBLANK関数・COUNTIFS関数>

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


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


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


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

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


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

空白セルの件数

 

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


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


E2に設定した数式は、

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


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


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

4/07/2024

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を返す

4/06/2024

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

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

<関数辞典:GAMMALN.PRECISE関数>

GAMMALN.PRECISE関数

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

分類: 統計 

GAMMALN.PRECISE関数

GAMMALN.PRECISE(x)

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

4/05/2024

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ならば「”○”」と表示します。

4/04/2024

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

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

<ARRAYTOTEXT関数>

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


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


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

ARRAYTOTEXT関数

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


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


=ARRAYTOTEXT(A2:B3,1)


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


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


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

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


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

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

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


なお、このARRAYTOTEXT関数。

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

4/03/2024

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

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

<便利な機能>

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

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


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


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


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

4/02/2024

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

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

<NUMBERVALUE関数>

日頃使っている数値。

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


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


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

フランス式の数字

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


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


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

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


B3に設定した数式は、

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

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


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


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

B1を設定します。

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

文字列になっています。


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

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

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


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

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


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


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

4/01/2024

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

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

<関数辞典:GAMMALN関数>

GAMMALN関数

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

分類: 統計 

GAMMALN関数

GAMMALN(x)

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