Excel。並べ替えをしないでカテゴリー別の連番を、楽に設定するにはどうしたらいいの。【SORT】

Excel。並べ替えをしないでカテゴリー別の連番を、楽に設定するにはどうしたらいいの。

＜TEXT関数＋COUNTIF関数＞

データの連番だけではなくて、カテゴリー別に連番。

つまり通し番号を設定するには、どのようにしたら効率的に設定することができるのでしょうか？

次の表をつかって確認していきましょう。

B列には所属するクラス名が入力されています。

このクラス名にクラス内での連番を付属させた、新しい管理コードを作りたいわけです。

C2に設定する数式は、

=B2&"-"&COUNTIF($B$2:B2,B2)

これで、クラス（カテゴリー別）での連番をつけた管理コードをつくることができます。

COUNTIF関数を使わない場合は、クラスごとに並べ替えをおこない、別の列にオートフィルなどをつかい連続データで連番をつくります。

そのあとに、クラス名と文字結合をするというのも悪くありませんが、この方法だと面倒ですね。

そこで、COUNTIF関数を使うことで、効率よくカテゴリー別で連番を設定することができるというわけです。

COUNTIF関数の部分を確認しておくと、

最初の引数の「範囲」には、$B$2:B2と設定しておきます。

これは、始点を絶対参照にすることで、「始点留め」の範囲選択を行うことができます。

「終点」を相対参照のままにしておくことで、オートフィルで範囲選択した時に、自動的に範囲選択を拡張することができます。

よく、累計を算出するときに使う方法ですね。

2番目の引数は、B2としておきます。

こちらも相対参照のままなので、オートフィルで数式をコピーすると参照先が変動してくれます。

範囲選択が拡張されていることで、その範囲内で登場する回数を算出することができる。

つまり連番を算出することができるというわけです。

あとは、「&（アンパサンド）」をつかって、文字結合を行えば、手早くカテゴリー別で連番を設定することができます。

Excel。表示形式の中で改行するには、どうしたらいいのでしょうか【Display format】

Excel。表示形式の中で改行するには、どうしたらいいのでしょうか

＜表示形式：Ctrl+J＞

知っていると重宝する「表示形式」。

この表示形式ですが、結構奥深くて、ちょっと知っているだけで、色々な表示をすることができます。

たとえば、表示形式内で「改行」したい場合にはどのようにしたらいいのでしょうか？

B1には、2022/12/31と入力してあります。

d"日"(aaa)

表示形式を設定することで、日付と曜日という形式で表示することができます。

「d」は日を表示します。「aaa」は曜日を表示することができます。

「日にち」と「曜日」の間で改行してみます。

方法は改行したいところで、「Ctrl+J」というショートカットキーを挿入すればいいだけです。

今回の場合は、「”（ダブルコーテーション）」と「(」の間にカーソルを表示して、「Ctrl+J」と入力すれば、改行して表示してくれます。

なお、設定後に、改行されて表示されていない時は、「折り返して全体を表示する」をオンにすることで、改行されたことを確認できます。

Excel。2022/11/14-11/20にLOOKUP関数など紹介したFacebookページのコメントです。【Trivia】

Excel。2022/11/14-11/20にLOOKUP関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

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

11月14日

Excel。

LOG10関数

読み方は、ベース・テン・ログで、10を底とする数値の対数を算出します。

11月15日

Excel。

LOGEST関数

読み方は、ログイーエスティーで、複数の独立変数の回帰指数曲線の係数を算出します。

11月16日

Excel。

LOGINV関数

読み方は、ログインバースで、累積確率から対数正規分布を算出します。

11月17日

Excel。

LOGNORMDIST関数

読み方は、ログノーマルディストで、対数正規分布の累積確率を算出します。

11月18日

Excel。

LOGNORM.DIST関数

読み方は、ログノーマル・ディストで、対数正規分布の累積確率か確率密度を算出します。

11月19日

Excel。

LOGNORM.INV関数

読み方は、ログノーマル・インバースで、累積確率から対数正規分布を算出します。

11月20日

Excel。

LOOKUP関数

読み方は、ルックアップで、1行/1列のセル範囲でせるを検索し対応するセルの値を返す。

ベクトル形式　LOOKUP(検索値,検索範囲,対応範囲)

Excel。VBA。セル内に該当する文字が含まれているかを手早く判断したい。【characters】

Excel。VBA。セル内に該当する文字が含まれているかを手早く判断したい。

＜Excel VBA:InStr関数＞

データを読み込んだ後に、セル内に該当する文字列が含まれているかを判断させたいわけです。

読み込んだあとに、数式をつくって判断するのもいいのですが、頻繁に行うようならば、Excel VBAでプログラム文をつくってしまうというのもアリですね。

今回は、B列の住所に神奈川県の横浜市だったら、C列の横浜市という列に「○」を表示します。

では、早速プログラム文を書いてみましょう。

Sub 横浜市を含む()

    Dim i As Long

    Dim lastrow As Long

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

    For i = 1 To lastrow

        If InStr(Cells(i, "b"), "神奈川県横浜市") <> 0 Then

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

        End If

    Next

End Sub

これだけで、セル内に該当する文字列があるか、ないかを判断することができます。

プログラム文を確認していきます。

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

Dim i As Long

Dim lastrow As Long

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

変数のlastrowは、繰り返し回数の最大値を格納するための変数です。

For i = 1 To lastrow

    If InStr(Cells(i, "b"), "神奈川県横浜市") <> 0 Then

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

    End If

Next

For to Nextで繰り返し処理をしています。

If InStr(Cells(i, "b"), "神奈川県横浜市") <> 0 Then

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

End If

ここで、該当するセルに、「横浜市」が含まれているかを判断させています。

InStr関数は、文字列に指定した文字列を検索して、最初に見つけた文字位置を算出する関数です。

つまり含まれていなければ、「0」となるわけですね。

そのため、IF文で、「0」と等しくなければ、文字が含まれているということがわかりますので、C列に「”○”」を表示させるようにしているというわけです。

なお、InStr（インストリング）関数は、Excel VBAの関数なので、通常のExcelにはありません。

Excel。COUNT関数で数値の個数を算出することができます。【COUNT】

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

<関数辞典：COUNT関数>

COUNT関数

読み方： カウント　　

分類： 統計 

COUNT(値1,[値2],…)

数値の個数を算出する

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

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

＜Excel関数辞典：VOL.74＞

今回は、SQRT関数～STANDARDIZE関数までをご紹介しております。

SQRT関数

読み方： スクエアルート　　

分類： 数学/三角 

SQRT(数値)

平方根を求める　

SQRTPI関数

読み方： スクエアルート パイ　　

分類： 数学/三角 

SQRTPI(数値)

円周率と数値の積の平方根を算出する　

STANDARDIZE関数

読み方： スダンダーダイズ　　

分類： 統計 

STANDARDIZE(x,平均,標準偏差)

標準化得点を算出する　

Excel。範囲内に集計値がある場合、楽に最大値を算出するにはどうしたらいいの【MAX】

Excel。範囲内に集計値がある場合、楽に最大値を算出するにはどうしたらいいの

＜SUBTOTAL関数＞

四半期集計がある表の場合、最大値を算出するとなると、四半期集計を除いて範囲選択しないと算出することができません。

B11の数式は、最大値を算出したいので、MAX関数をつかっています。

=MAX(B2:B9)

ただ、B5やB9に四半期集計があるので、これら集計行を除かないと、当然集計値の方が大きいため、きちんとした最大値を算出することができません。

当然、範囲選択で集計行を除けばいいわけですが、集計行が増えれば増えるほど、面倒になります。

このような集計表を作る場合、実は、四半期集計などの途中集計や、最大値を算出するにあたり、「SUBTOTAL関数かAGGREGATE関数」をつかうことで、範囲選択が面倒にならず、手早く算出することができます。

まず、B5とB9の四半期集計をSUM関数からSUBTOTAL関数に変更します。

B5の数式は、

=SUBTOTAL(9,B2:B4)

B9の数式は、

=SUBTOTAL(9,B6:B8)

このように、数式を設定します。

なお、SUBTOTAL関数は集計をする関数です。

最初の引数は「集計方法」です。

「9」は合計を算出する番号です。

今回は、行の非表示がないので、「109」の100番台はつかわなくて大丈夫です。

B11の最大値もMAX関数ではなくて、SUBTOTAL関数で数式をつくります。

=SUBTOTAL(4,B2:B9)

計算方法「4」番は、最大値を設定する番号です。

そして、2つ目の引数は、範囲ですが、集計行を除く必要はありません。

すると、集計行を除いて、最大値を算出することができます。

SUBTOTAL関数は、範囲選択に、SUBTOTAL関数をつかった算出結果がある場合には、それを除外して、算出してくれるという、便利な機能があります。

それにより四半期集計を除いた最大値を算出することができたというわけです。

ちょっとした関数の違いですが、手早く算出できるかもしれませんので、調べてみるといいかもしれませんね。

Excel。数値の双曲線余接を算出できるのが、COTH関数です。【COTH】

Excel。数値の双曲線余接を算出できるのが、COTH関数です。

＜関数辞典：COTH関数＞

COTH関数

読み方： ハイパーポリック コタンジェント　　

分類： 数学/三角 

COTH(数値)

数値の双曲線余接を算出します　

Excel。16進数を算出するならDEC2HEX関数の出番です。【DEC2HEX】

Excel。16進数を算出するならDEC2HEX関数の出番です。

＜DEC2HEX関数＞

日常生活で使用している10進数を16進数に変換することができるのが、DEC2HEX関数です。

ちょっとした特徴がありますので、DEC2HEX関数を次の表を使って紹介していきます。

B2に設定した数式は、

=DEC2HEX(A2)

これが基本的な使い方です。

B3に設定した数式は、

=DEC2HEX(A3,10)

引数の2つ目は、桁数を設定することができる数値です。

最大10桁まで大丈夫です。

なので、B4の数式は、

=DEC2HEX(A4,11)

としたので、エラーが表示されてしまったというわけです。

なお、最高値は、549755813887です。

549755813888とするとエラーが出ることから、549755813887。

すなわち、2の39乗-1までならば、つかうことができます。

Excel。2022/11/7-11/13にLEN関数など紹介したFacebookページのコメントです。【Trivia】

Excel。2022/11/7-11/13にLEN関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

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

11月7日

Excel。

LEFT関数

読み方は、レフトで、文字列の左端から文字を取り出す

11月8日

Excel。

LEFTB関数

読み方は、レフトビーで、文字列の左端から指定バイト数の文字を返す

11月9日

Excel。

LEN関数

読み方は、レンで、文字列の文字数を返す

11月10日

Excel。

LENB関数

読み方は、レンビーで、文字列のバイト数を返す

11月11日

Excel。

LINEST関数

読み方は、ラインエスティーで、重回帰直線の各係数を算出します。

11月12日

Excel。

LN関数

読み方は、ログ・ナチュラルで、オイラー数eとする数値の対数を算出します。

11月13日

Excel。

LOG関数

読み方は、ログで、指定した数を底とする数値の対数を算出します。

Access。四捨五入のRound関数はExcelとAccessで大違いなので注意が必要です。【Round】

Access。四捨五入のRound関数はExcelとAccessで大違いなので注意が必要です。

＜Access：Round関数＞

ExcelにあるROUND関数は、四捨五入でおなじみです。

Accessの関数にも、Round関数があって、四捨五入をする関数なのですが、Excelと違った結果になるので注意が必要です。

下記の表は、AccessでRound関数をつかった結果のクエリです。

小数第1フィールドには、次の演算フィールドを設定しています。

小数第1: Round([値],1)

引数の2つ目に「1」と設定することで、小数点第2位を四捨五入して小数点第1位を求めることができます。

ただ、結果がおかしいですよね。

1.44は、1.4。

これは問題ありませんが、1.45を四捨五入したら、1.5になるはずですが、1.4と算出されています。

1.54は、「1.5」と算出されていて、1.55は「1.6」と四捨五入された結果が表示されています。

AccessのRound関数は四捨五入を行うことは行うのですが、「銀行丸め」とかJIS式と呼ばれる四捨五入で算出されているために、お馴染みの四捨五入と結果が異なってしまうのです。

Excelの四捨五入のROUND関数は、算術における四捨五入なんですね。

整数フィールドには、

整数: Round([値])

という演算フィールドを設定してあります。

2つ目の引数を省略すると整数で四捨五入してくれます。

1の位フィールドには、

1の位: Round([値],-1)

という演算フィールドが設定してあります。

しかし、結果は、エラーが表示されています。

2つ目の引数を「-1」とすれば、Excelの場合、1の位を四捨五入してくれるのですが、AccessのRound関数は、小数点にしか対応してくれません。

つまり、整数の端数には対応していないというわけです。

そのため、AccessのRound関数をつかって四捨五入をすると、Excelと異なった値になる。

または、整数値では対応してくれないということが発生しますので、注意が必要です。

Excel。COT関数をつかうと、角度の余接を算出できます。【COT】

Excel。COT関数をつかうと、角度の余接を算出できます。

＜関数辞典：COT関数＞

COT関数

読み方： コタンジェント　　

分類： 数学/三角 

COT(数値)

角度の余接を算出します　

Excel。テーブルから簡単にOR条件で抽出した別表を作れるFILTER関数【FILTER】

Excel。テーブルから簡単にOR条件で抽出した別表を作れるFILTER関数

＜FILTER関数＞

テーブルから条件で抽出して別表を手早く作ることができるFILTER関数。

オートフィルターなどでは、抽出が面倒になるOR条件も、FILTER関数をつかうことで、手早く抽出することができます。

今回は、店舗名から売上高までを対象として、売上高が1200より大きい。

または、店舗名が渋谷だったら抽出するという条件とします。

さて、このFILTER関数をつかって、OR条件をつかうときには、どのようにしたらいいのでしょうか？

テーブルにした次の表でFILTER関数をつかってみました。

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

=FILTER(売上表OR[[店舗名]:[売上高]],(売上表OR[売上高]>1200)+(売上表OR[店舗名]="渋谷"))

OR関数を使いたくなってしまいますが、「＋」をつかって条件を接続することでOR条件にすることができます。

FILTER関数は、アイディアで色々使えそうな関数ですので、色々試してみると、作業効率が改善できる場合もあるかもしれませんね。

Excel。表内で重複しないデータ数を一発で算出する簡単な方法はないのかな。【overlapping】

Excel。表内で重複しないデータ数を一発で算出する簡単な方法はないのかな。

＜SUM＋COUNTIF関数＞

帳票や表のデータが重複していない件数を算出するには、なかなか大変なケースもあります。

例えば、次の表。

B2:D4に入力されている地域名のうち、重複を除いた件数を、D6に算出したいわけです。

データタブの「重複削除」をつかって、残った件数を数えるというのもアリですが、今回のように、ひとつの列内にデータがあるわけではないので、「重複削除」をつかうことはできません。

また、別のところで、コピーして、ひとつの列をつくってから、「重複削除」を行う必要があります。

こうなると、作業は簡単でも、面倒になってきます。

また、Excel VBAでプログラム文をつくるというのも、ちょっと面倒です。

そこで、SUM＋COUNTIF関数を組み合わせた数式で、算出することができます。

D6に設定する数式は、

=SUM(1/COUNTIF(B2:D4,B2:D4))

この数式だけで、重複を除いた件数を算出することができます。

別のセルをつかって説明します。

COUNTIF関数で範囲の中に、その文字が何件あるかを算出させています。

B9に、

=COUNTIF($B$2:$D$4,B2)

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

すると、ハワイは表内で3件あることが算出されるわけです。

ただ算出しただけでは、1より大きい値のデータが重複していることがわかるだけです。

そこで、この算出した値を、「1」で除算してみましょう。

 

B9の数式は、

=1/COUNTIF($B$2:$D$4,B2)

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

この値全部を合算すれば、重複を除いた件数になります。

なぜ、「1」で除算したのかというと、例えば、2件あった場合、1÷2とすれば、「0.5」になるわけです。

2件ということは、別のセルで2と算出されたところも「0.5」となります。

0.5+0.5なので、1とすることができます。

4件あれば0.25+0.25+0.25+0.25　で、1とすることができる。

あとは、合算させればいいわけですね。

このような方法を使えば、数式だけで重複を除いた件数を算出することもできます。

Excel。通貨表示の「￥」をつけたけど、桁数で位置がバラバラなので揃えたい【currency style】

Excel。通貨表示の「￥」をつけたけど、桁数で位置がバラバラなので揃えたい

＜表示形式：通貨と会計の違い＞

数値に、「￥」をつける、通貨スタイルを設定すること自体簡単です。

ただ、数値の前に「￥」マークが付くのはいいのですが、桁数によって、「￥」マークの位置もバラバラになってしまいます。

そこで、数値の桁数に関係なく、「￥」マークを揃えたい場合には、「会計」というスタイルを設定することで対応することができます。

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

ホームタブの表示形式のボックスにある「会計」に変更することで、対応することができます。

このように、会計のスタイルをつかうことで、数値の桁数に関係なく、￥マークを揃えて表示することができます。

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

Excel。2022/10/31-11/6にLARGE関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

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

10月31日

Excel。

ISPMT関数

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

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

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

11月1日

Excel。

ISREF関数

読み方は、イズリファレンスで、対象がセル参照の場合にTRUEを返す

11月2日

Excel。

ISTEXT関数

読み方は、イズテキストで、対象が文字列の場合にTRUEを返す

11月3日

Excel。

JIS関数

読み方は、ジスで、半角文字を全角に変換する

11月4日

Excel。

KURT関数

読み方は、カートで、データセットの尖度（せんど）を算出します。

11月5日

Excel。

LARGE関数

読み方は、ラージで、指定した○番目に大きい値を算出します。

11月6日

Excel。

LCM関数

読み方は、エルシーエムで、整数の最小公倍数を算出します。

Excel。VLOOKUP関数で抽出した文字からフリガナを表示させたい【furigana】

Excel。VLOOKUP関数で抽出した文字からフリガナを表示させたい

＜VLOOKUP関数・PHONETIC+INDEX+MATCH関数＞

氏名からフリガナを抽出したい時には、PHONETIC関数をつかうことで、表示できます。

ところが、PHONETIC関数は、文字を入力した時のデータを表示するため、VLOOKUP関数やセル参照などで、間接的に表示されているデータからフリガナ情報を表示することができません。

では、どのようにしたら、次のように、フリガナを表示することができるのでしょうか。

B2には、VLOOKUP関数の数式を設定してあります。

=VLOOKUP(A2,A5:F14,2,FALSE)

C2に、PHONETIC関数をつかった数式を設定してみても、何も表示されません。

C2に、

=PHONETIC(B2)

と設定していますが、表示されないわけですね。

解決するポイントは、文字入力が入っている元のデータをつかえばいいわけです。

では、次のようにC2の数式を修正していきます。

=PHONETIC(INDEX(B5:B14,MATCH(B2,B5:B14,0)))

これで、フリガナを表示することができるようになります。

それでは、この数式を説明していきます。

最初のPHONETIC関数は、「フリガナ」情報を表示することができる関数ですね。

INDEX関数で指定した行列番号が交差するセル参照する関数ですね。

そして、INDEX関数と相性抜群のMATCH関数を組み合わせます。

MATCH(B2,B5:B14,0)

と数式を設定していますが、B2の値は、B5:B14のなかで、何番目にあるのかを算出することができます。

MATCH関数は、範囲内にある検索値の位置を算出する関数です。

このように、INDEX関数とMATCH関数を合わせることで、元にあるフリガナ情報を抽出することができます。

INDEX＋MATCH関数は、覚えておくといい、関数の組み合わせかもしれませんね。

Excel。COSH関数をつかうと数値の双曲線余弦を算出できます。【COSH】

Excel。COSH関数をつかうと数値の双曲線余弦を算出できます。

＜関数辞典：COSH関数＞

COSH関数

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

分類： 数学/三角 

COSH(数値)

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

Excel。桁数が異なっていても文字結合で「0（ゼロ）」をいれて桁を揃え番号をつくりたい【alignment】

Excel。桁数が異なっていても文字結合で「0（ゼロ）」をいれて桁を揃え番号をつくりたい

＜TEXT関数＞

CONCAT関数や「&（アンパサンド）」をつかうことで文字結合をおこなうことができます。

頭文字と数値を結合する場合、数値の桁数が異なると、当然、文字数が揃いません。

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

そこで、TEXT関数と「&（アンパサンド）」を組み合わせることで、解決することができます。

B2には次のように数式を設定しました。

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

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

TEXT関数は表示形式を設定することができる関数です。

ゼロ付数値を表示することができるのが、「”0000”」という表示形式です。

桁が1桁ならば、4桁にそろえるので「0001」と表示されます。

これに、頭文字のコードをつけることで、文字列と数値の間を「0（ゼロ）」で揃えて埋めることができるというわけです。

Excel。条件付き書式で平均値以上のデータがある行全体を塗りつぶしたい。【above average】

Excel。条件付き書式で平均値以上のデータがある行全体を塗りつぶしたい。

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

Excelの条件付き書式は便利ですね。

さらに、ちょっとしたアレンジをすることで、色々対応することができます。

例えば、次のようにTotal値が平均以上だったら行全体を塗りつぶすとしたらどうしたらいいのでしょうか。

条件付き書式で用意されているものでは、次の2つが対応できません。

1つ目は、平均以上は用意されていない

上位/下位ルールには「平均より上」はありますが、「平均以上」は無いことがわかります。

2つ目は、セルが対象になっていて、行全体を塗りつぶす設定はない

そのため、数式を使った条件を設定する必要があるわけです。

それでは、設定していきます。

設定するA2:F11を範囲選択します。

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

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

ルールの種類から「数式を使用して、書式設定するセルを決定」を選択します。

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

=$F2>=average($F$2:$F$11)

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

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

「$F2」は、列を固定した複合参照にすることで、該当データのセルが含まれる行全体を書式設定の対象にすることができます。

「>=average($F$2:$F$11)」は、平均を算出するお馴染みのAVERAGE関数ですね。

ちなみに、今回のTotalの平均値は、163.7なので、塗りつぶしされている行は、きちんと対応していることがわかります。

最終行に、集計行を表示したくない場合には、このような方法をつかうことで、平均以上の場合に行全体を塗りつぶすことができます。

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

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

＜Excel関数辞典：VOL.73＞

今回は、SLN関数～SORTBY関数までをご紹介しております。

SLN関数

読み方： エスエルエヌ

読み方： ストレートライン

分類： 財務 

SLN(取得価額,残存価額,耐用年数)

減価償却費を定額表で算出します　

Straight LiNe depreciationの略

SLOPE関数

読み方： スロープ　　

分類： 統計 

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

回帰直線の傾きを算出する　

SMALL関数

読み方： スモール　　

分類： 統計 

SMALL(配列,順位)

指定した○番目に小さい値を算出します　

SORT関数

読み方： ソート　　

分類： 検索/行列 

SORT(配列,[並べ替えインデックス],[並べ替え順序],[並べ替え基準])

範囲または配列を並べ替えます　

SORTBY関数

読み方： ソートバイ　　

分類： 検索/行列 

SORTBY(配列,基準配列1,[並べ替え順序1],…)

範囲または配列を、対応する範囲または配列の値に基づいて並べ替えます　

Excel。10進数を2進数に変換するDEC2BIN関数はありますが、どうなんだろう。【Decimal number】

Excel。10進数を2進数に変換するDEC2BIN関数はありますが、どうなんだろう。

＜DEC2BIN関数＞

ネットワーク関係の方ならお馴染みの「2進数」。

10進数を2進数に変換するという作業を行うときに、Excelでも一応関数をつかって変換することはできるのですが、ちょっと、イマイチ。

次の表で説明します。

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

=DEC2BIN(A2,10)

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

B3をみると、17を2進数に変換してくれているのですが、「オクテット」ごとに空白がはいらないので、可読性が悪いですね。

数値が大きくなったら大変です。

ところが、「数値が大きく」とはいっても、最大「511」までしか変換してくれません。

B5をみると、#NUM!というエラーが表示されています。

一応負数でもリアクションしますが、「-512～511」の範囲でしか対応しておりません。

確かに、関数を使うことで、対応することはできますが、単純に、10進数を2進数に変換するだけならば、関数電卓があるアプリをつかってしまうほうが、いいように思えます。

Excel。1列おきごとの合計を楽に算出するには、どうしたらいいの【every other row】

Excel。1列おきごとの合計を楽に算出するには、どうしたらいいの

＜SUMPRODUCT+MOD+COLUMN関数＞

Excelは基本的に上から下へ流れていくテーブル（表）でつくれば、様々なExcelの機能を使うことができます。

ただ、どうしても帳票と同じように表をつくってしまうと、簡単に算出できない場合があります。

例えば、次の表。

来店客数と売上高が一組になったデータが列方向に拡張されている表。

このような表、帳票としてはいいのですが、単純に、来店客数の合計や、売上高の合計を算出する場合、1列おきで範囲を設定する必要があります。

要するに、列が増えれば増えるほど、面倒な作業というわけです。

そこで、来店客数の合計B8には次の数式を設定することで、手早く算出することができます。

=SUMPRODUCT((MOD(COLUMN($B$3:$G$5),2)=0)*$B$3:$G$5)

また、売上高の合計C8には、次の数式を設定してあります。

=SUMPRODUCT((MOD(COLUMN($B$3:$G$5),2)=1)*$B$3:$G$5)

数式の引数を確認しましょう。

最初のSUMPRODUCT関数ですが、SUMは、和算。

PRODUCTは乗算で、乗算した結果を和算する関数です。

そして引数のMOD関数とCOLUMN関数は何をやっているのかというと、1列おきで範囲選択したいわけです。

1列おきということは、列番号をつかって、偶数か奇数なのかを判定させればいいわけです。

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

また、COLUMN関数は列番号を算出する関数です。

よって「MOD(COLUMN($B$3:$G$5),2)」で、列番号を2で除算するという数式ですから、結果「0」だったら余りが0ということで、偶数列ということがわかります。

「MOD(COLUMN($B$3:$G$5),2)=0」と「=0」とすれば、「MOD関数の結果が0と等しいか」と判断させています。

「等しい」ならば「TRUE」、「等しくない」ならば「FALSE」と判定されます。

「TRUE=1」で「FALSE=0」とExcelでは定義されていますから、「MOD(COLUMN($B$3:$G$5),2)=0」が成立しているならば、偶数列は「1」。

奇数列は「0」と算出されるわけです。

ここで、SUMPRODUCT関数の出番。

「*$B$3:$G$5」と乗算していますが、偶数の「1」を掛ければ、その値は残り、奇数の「0」を掛ければ、「0」となるわけです。

その結果を和算すれば、偶数列のみの合計値を算出できるというわけです。

1列おきとか1行おきとかで、合計を算出したい場合にはSUMPRODUCT関数をつかってみるといいかもしれませんね。

Excel。えっ！マクロが実行できない！セキュリティリスクが表示されました。【SecurityRisk】

Excel。えっ！マクロが実行できない！セキュリティリスクが表示されました。

＜Excel VBA:マクロ:セキュリティリスク対応＞

日頃、マクロを実行していたファイル。

開くと、「セキュリティリスク」が表示されてしまい、マクロをつかうことができなくなりました。

どのように対応したら、マクロを使うことができるのでしょうか。

一度、Excelを閉じて、そのファイルの上で右クリックをします。

そして、プロパティをクリックします。

ファイルのプロパティダイアログボックスが表示されます。

セキュリティの「許可をする」にチェックマークをオンとするだけで、マクロを使えるようになります。

あとは、OKボタンをクリックして、再度ファイルを開きます。

「セキュリティの警告」が表示されるので、「コンテンツの有効化」をクリックします。

通常通り、マクロを使うことができます。

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

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

＜Facebookページ＞

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

10月24日

Excel。

ISFORMULA関数

読み方は、イズフォーミュラーで、セルに数式が含まれている場合にTRUEを返す

10月25日

Excel。

ISLOGICAL関数

読み方は、イズロジカルで、対象が論理値の場合にTRUEを返す

10月26日

Excel。

ISNA関数

読み方は、イズエヌエーで、対象がエラー値の#N/Aの場合にTRUEを返す

10月27日

Excel。

ISNONTEXT関数

読み方は、イズノンテキストで、対象が文字列でない場合にTRUEを返す

10月28日

Excel。

ISNUMBER関数

読み方は、イズナンバーで、対象が数値の場合にTRUEを返す

10月29日

Excel。

ISODD関数

読み方は、イズオッドで、対象が奇数の場合にTRUEを返す

10月30日

Excel。

ISOWEEKNUM関数

読み方は、アイエスオーウィークナムで、ISO週番号を算出する

Excel。VBA。データを読み込んだら、数値に円がついてるけど合計させたい【Val】

Excel。VBA。データを読み込んだら、数値に円がついてるけど合計させたい

＜Excel VBA:Val関数＞

データを読み込んだら、次のように、数値に「円」がついていました。

SUM関数をつかって、合計値を算出したくても、文字型データの為、エラーが表示されてしまいます。

「円」を削除する。

または、削除した後に、表示形式のユーザー定義をつかって、「円」を表示すれば、数値型になるので、合計値を算出することはできます。

ただ、少し処理が面倒ですね。

そこで、Excel VBAでプログラムをつくることで、「円」がついた文字型であっても、サクッと合計値を算出することができます。

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

Sub 円付き数値()

    Dim total As Long

    Dim i As Long

    

    For i = 2 To 6

        total = total + Val(Cells(i, "b"))

    Next

    Range("b7").Value = total

    Range("b7").NumberFormat = "#,##0""円"""

End Sub

まずは実行して確認してみます。

B7には、合計値を算出してあり、数式バーを確認すると数値が入力されていることがわかります。

B7には、「円」がついていますので、表示形式のユーザー定義を設定してある状態ということもわかります。

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

最初はお馴染みの変数宣言です。

Dim total As Long

Dim i As Long

For to Next文で数値を合計していきます。

For i = 2 To 6

    total = total + Val(Cells(i, "b"))

Next

ポイントは、「Val(Cells(i, "b"))」

SUM関数では文字型だったので、算出することはできません。

しかし、Excel VBAにある「Val関数」をつかうことで、文字型であっても、そのセル内の数値と認識できる部分があれば、数値として算出することができるという、優れた関数があります。

最後の2行。

Range("b7").Value = total

Range("b7").NumberFormat = "#,##0""円"""

変数のtotalに合計値が集計されていますので、それをB7に表示させています。

あとは、算出した値に「円」を最後につけたいので、表示形式を設定してみました。

このように、Excelでは面倒なことも、Excel VBAをつかってプログラムをつくることで解決できる場合もありますので、色々試してみるといいかもしれませんね。

Excel。OR条件（または）で件数を算出するには、DCOUNTA関数が便利です。【DCOUNTA】

Excel。OR条件（または）で件数を算出するには、DCOUNTA関数が便利です。

＜DCOUNTA関数＞

複数条件で文字列の件数を算出するには、COUNTIFS関数をつかえばいいわけですが、同じフィールド内に条件がある場合。

つまり、「OR条件」だと、COUNTIFS関数では算出することができません。

D13には、次の数式を設定してますが、「0」と算出されてしまいました。

=COUNTIFS(C2:C11,"新宿",C2:C11,"渋谷")

OR条件で件数を算出したい時には、次のように、COUNTIF関数で算出した結果を和算する必要があります。

=COUNTIF(C2:C11,"新宿")+COUNTIF(C2:C11,"渋谷")

算出はできましたが、これでは、条件が増えてしまうと、数式自体の可動性が悪化することで、ミスや修正に影響が出る可能性が高くなります。

そこで、条件を別途用意する必要はありますが、DCOUNTA関数をつかうことで、楽に算出することができます。

G5の数式は、

=DCOUNTA(A1:D11,C1,F1:F3)

これだけで、算出することができます。

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

最初の引数は、データベースで、見出し行も含めた表全体になりますので「A1:D11」

2番目の引数は、フィールドで、店舗名が対象になるので、条件の見出し名を設定するわけですから「C1」。

3番目の引数は、条件なので、「F1:F3」と設定して完成ですね。

Excel。高校数学でお馴染み。コサインを算出するCOS関数。【COS】

Excel。高校数学でお馴染み。コサインを算出するCOS関数。

＜関数辞典：COS関数＞

COS関数

読み方： コサイン

分類： 数学/三角 

COS(数値)

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

Excel。区切りごとに連番を簡単に設定するにはどうしたらいいの【Serial number】

Excel。区切りごとに連番を簡単に設定するにはどうしたらいいの

＜IF+SUM関数＞

カテゴリごととか、区切りごとに連番を設定したい場合、オートフィルをつかってもいいのですが、区切りを確認しながら設定するのは、面倒です。

例えば、次の表をつかって、確認していきます。

C列のカテゴリNOは、B列のカテゴリが変わると、「1」から連番を振り直すようにしてあります。

連番は、オートフィルの連続コピーをつかうことで、設定することができます。

ただ、カテゴリがわかるなど、区切りがある場合には、単純な作業でも、面倒な作業となってしまうわけです。

そこで、C2に、次のような数式を設定することで、対応することができます。

=IF(B1=B2,SUM(C1,1),1)

設定したら、オートフィルで数式をコピーするだけです。

IF関数とSUM関数をネストしただけの数式ですが、この数式で対応することができます。

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

論理式は、「B1=B2」。

上のセルと同じかどうかを確認します。

値が真の場合は、「上のセルの内容と同じ」ということですから、上の値に+1するので、「SUM(C1,1)」と設定します。

SUM関数で「,（カンマ）」をつかった引数はあまり目にしないかもしれませんが、SUM(1,1)とすれば「2」と算出されます。

つまり「1+1」と同じ意味の数式です。

わかりにくければ、SUM(C1+1)としてもOKです。

値が偽の場合。

すなわち、カテゴリが変わった場合なので、連番を「1」に戻す必要がありますので、「1」と設定します。

このように、簡単な関数をつかった数式を用意するだけでも、作業効率を改善できるかもしれませんね。

なお、カテゴリを並べ替えても、カテゴリの区切りごとに連番を設定することができます。

 

Excel。IF関数もスピル機能によって数式が変わってしまのです。【Spill】

Excel。IF関数もスピル機能によって数式が変わってしまのです。

＜IF関数＞

IF関数は、関数を勉強する入口の関数です。

ただ、スピル機能により、今までのような数式と少々変わってしまいました。

例えば、平均以上だったら、○。そうでなければ、×と判断させる場合です。

売上高の平均値は、E12に算出してあります。

F2には、IF関数の数式を設定してありますが、スピル機能の登場で、次のような数式で算出することができるようになりました。

=IF(E2:E11>=E12,"○","×")

今までならば、

=IF(E2>=$E$12,"○","×")

という数式で、平均は絶対参照にしなければなりませんでした。

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

ところが、スピル機能の登場で、絶対参照もオートフィルで数式をコピーしなくても算出することができるようになりました。