Excel。日付から「日」だけを求めるなら、DAY関数をつかいます。【DAY】

Excel。日付から「日」だけを求めるなら、DAY関数をつかいます。

＜DAY関数＞

日付関係の関数は、とても簡単な関数が多い印象を受けます。

例えば、日付から、「日」だけを算出するならば、DAY関数ですね。

A1に日付を入力しました。

B2に、

=DAY(A1)

というDAY関数の数式を設定するだけで、日にちの15を算出してくれます。

なお、DAY関数の引数は「シリアル値」となっていますので、数字をダイレクトに設定することも出来ます。

Excel。年齢を年代分けするなら、Excel VBAのPartition関数をつかうのもあり。【AGE】

Excel。年齢を年代分けするなら、Excel VBAのPartition関数をつかうのもあり。

＜Excel VBA：Partition関数＞

データの年齢を10代ごとの年代別がわかるようにしたい場合、どのようにしたら、手早く・楽に算出することができるのか、考えると、なかなか、大変です。

10で除算して、一の位の値が「1」だったらとか考えますが、普通、「11-20」で一塊ですから、「20」のような場合、10で除算しても、一の位が「2」になってしまうので、どうしたものかと考えてしまいます。

そこで、Excel VBAでプログラムをつくってみるというのは、どうでしょうか。

このような年代をわかるようにしたい場合は、とても簡単なプログラム文で、設定・算出することができます。

次の表をつかってみます。

C列に年代を算出するプログラムをつくってみました。

Sub 年代()

    Dim i As Long

    Dim lastrow As Long

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

    

    For i = 2 To lastrow

        Cells(i, "c") = Partition(Cells(i, "b"), 1, 100, 10)

    Next

End Sub

処理を実行しているプログラム文は、1行だけなのですが、まずは実行してみましょう。

このように、64は60歳代なので、「61:70」と算出されています。

このあと、年代別に集計する場合には、算出した値を使うことで、簡単に集計することができます。

プログラム文も確認しておきましょう。

最初は、変数宣言です。

Dim i As Long

Dim lastrow As Long

変数のlastrowには、データの最終行数を設定します。

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

このlastrowまで繰り返せばいいわけです。

そして、プログラム文本体は、For To Next文をつかって、繰り返し処理をしています。

For i = 2 To lastrow

    Cells(i, "c") = Partition(Cells(i, "b"), 1, 100, 10)

Next

今回の年代を算出するのにつかったのは、「Partition関数」です。

このPartition関数は、通常のExcel関数にはありません。

あれば、わざわざ、VBAでプログラブ文を作らなくてもいいわけですね。

Partition関数は関数ということもあって、引数をもっています。

この引数を設定したことで、10代ごとに算出することができたというわけです。

Partition(Cells(i, "b"), 1, 100, 10)

最初の引数には、「Cells(i, "b")」と設定しています。この値を判定するわけですね。

2つ目の引数には「1」と設定しています。

3つ目の引数には「100」と設定しています。

2つ目と3つ目の引数は、1～100までと対象の範囲を指定します。

最後の引数には、「10」と設定することで、10個ずつという範囲の長さで等分することができます。

年代別を算出するならば、Excel VBAでプログラム文をつくってみてもいいかもしれませんね。

Office。ダイアログボックスを挿入タブのスクリーンショットだと太枠みたいになる【screenshot】

Office。ダイアログボックスを挿入タブのスクリーンショットだと太枠みたいになる

＜Excel・Word・PowerPoint：スクリーンショット＞

操作マニュアルをつくりたいときなど、ダイアログボックスをスクリーンショットで撮影するのですが、最近、ダイアログボックスが、影付きというか、光彩の設定がされているような表示に変わりました。

このダイアログボックスを、挿入タブの「スクリーンショット」で撮影みると、外周のボヤっとしているところが、太枠フレームのようになってしまいます。

このように太枠のようにスクリーンショットされてしまいます。

この太枠のようなフレームを削除するのにトリミングするのは、面倒です。

ところが、以前からあるショートカットキー。

Alt+PrintScreenをつかえば、太枠のようなフレームがなく、撮影することができます。

挿入タブの「スクリーンショット」にある「画面の領域」をつかってもいいのですが、ちょっと面倒なので、ショートカットキーをつかって、ダイアログボックスを撮影するほうが、手早く処理することができそうです。

Excel。COMPLEX関数をつかうと複素数を表す文字列を生成できます。【COMPLEX】

Excel。COMPLEX関数をつかうと複素数を表す文字列を生成できます。

＜関数辞典：COMPLEX関数＞

COMPLEX関数

読み方： コンプレックス　　

分類： エンジニアリング 

COMPLEX(実数,虚数,[虚数単位])

複素数を表す文字列を生成する　

Excel。上位20%までの平均値を算出するにはどうしたらいいの【Top 20%】

Excel。上位20%までの平均値を算出するにはどうしたらいいの

＜AVERAGEIF＋PERCENTILE.INC関数＞

単純に平均を算出するだけならば、オートSUMボタンの中にある、「平均」やAVERAGE関数を使えば、簡単に算出することができます。

ただ、データ量が増えたときなどに、上位20%の平均値を算出したい場合、どのようにしたら、算出することができるのでしょうか？

上位20%のデータを抽出してその平均値を算出すればいいわけですが、オートフィルターをつかったりして、抽出してから平均値を算出するというのも面倒ですね。

できれば数式一発で算出したいわけですね。

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

そこで、このデータは、データ全体の中で上位何パーセントの場所にあるのかを判断することができるのが、PERCENTILE.INC関数（パーセンタイル・インクルーシブ）です。

PERCENTILE.INC関数は、全体を100%として、「率」に相当する値を算出することができます。

なお、親戚筋というか、PERCENTILE.EXE関数（パーセンタイル・エクスクルーシブ）というのがあります。

こちらの関数は、百分位を数える時に、0%と100%を含めないで数えてしまいます。

そのため、PERCENTILE.INC関数を使用します。

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

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

これで、上位20%までの平均値を算出することができます。

AVERAGEIF関数をつかいます。

2つ目の引数である条件で、PERCENTILE.INC関数を使うわけですが、「上位20%」なので、20%にしては、ダメです。

上位20%とは、全体を100%としたとき、80%の位置を指すわけですから、80%にする必要があります。

あとは、比較演算子と数式を結合するために、比較演算子の「>=」を「”（ダブルコーテーション）」で囲み、「&（アンパサンド）」で結合する必要がありますので、数式を作るときに、注意が必要ですね。

Excel。2022/9/12-9/18にIFS関数など紹介したFacebookページのコメントです。【Trivia】

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

＜Facebookページ＞

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

9月12日

Excel。

HYPGEOM.DIST関数

読み方は、ハイパージオム・ディストで、超幾何分析の累計確率か確率密度を算出します。

9月13日

Excel。

IF関数

読み方は、イフで、条件で分岐して異なる計算結果を返す

9月14日

Excel。

IFERROR関数

読み方は、イフエラーで、対象がエラーの場合に指定した値を返す

9月15日

Excel。

IFNA関数

読み方は、イフエヌエーで、結果が#N/Aの場合は指定した値を返す

イフ・ノン・アプリカブルの略

9月16日

Excel。

IFS関数

読み方は、イフエス（イフズ）で、1つまたは複数の条件で分岐して異なる計算結果を返す

9月17日

Excel。

IMABS関数

読み方は、アイエムアブスで、複素数の絶対値を算出する

9月18日

Excel。

IMAGINARY関数

読み方は、イマジナリーで、複素数の虚数係数を取り出す

Excel。月末日の一覧表をつくりたいけど、楽につくるにはどうしたらいいの【end of the month】

Excel。月末日の一覧表をつくりたいけど、楽につくるにはどうしたらいいの

＜オートフィル＞

月末に作業することを把握するために、月末日の一覧表を作りたいとした場合、どのようにしたら、手早くつくることができるのでしょうか？

月末ということで、EOMONTH関数をつかうことで、作れるように思うわけですが、月末日の一覧表となると、なかなか簡単につくることはできません。

EOMONTH関数は、セルや引数内に、日付のデータがないと、算出することができないからです。

ところが、関数を使わないで、日々使っている、オートフィルの連続コピーをつかえば、あっさり、月末日の一覧表をつくることができます。

オートフィルは、規則性を考慮して連続コピーすることができます。

連番などはその一例で、「１」と「2」と入力しておき、その2つのセルを範囲選択してからオートフィルの連続コピーをおこなうこことで、連番を設定できるようになっています。

やり方として、2つのセル。

今回はA2に「2022/1/31」と入力しA3に「2022/2/28」と入力したら、A2:A3を範囲選択して、オートフィルの連続コピーをするだけで、月末日の一覧表をつくることができます。

シリアル値のような、日数と考えてしまうと、月末日間の日数が異なっています。

規則性が無いように見えてしまいますが、「月末日」という規則性に基づいて、連続コピーをしてくれるので、関数をつかわなくても、簡単に月末日の一覧表をつくることができるというわけです。

ちなみに、各月の10日だけの一覧表も同じ原則で、つくることができます。

日付は、年・月・日などそれぞれの規則性で連続コピーすることができます。

Excel。重複組み合わせの数を求めることができるのがCOMBINA関数です。【COMBINA】

Excel。重複組み合わせの数を求めることができるのがCOMBINA関数です。

＜関数辞典：COMBINA関数＞

COMBINA関数

読み方： コンビネーション・ エー　　

分類： 数学/三角 

COMBINA(総数,抜き取り数)

重複組み合わせの数を求める　

Excel。様々な数値の右側に揃えたい桁数の「0(ゼロ)」をつけるにはどうしたらいい。【digit】

Excel。様々な数値の右側に揃えたい桁数の「0(ゼロ)」をつけるにはどうしたらいい。

＜REPT＋LEN関数＞

「1」を「001」のように、左側に０（ゼロ）をつけて、桁を揃えるなら表示形式で対応することが多いと思います。

しかし、次の表のように、「1」を「100」のように、右側に０(ゼロ)をつけて、桁を揃えるとしたら、どのようにしたらいいのでしょうか。

表示形式では、「1」と「82」というように桁数が違う場合に揃えることができません。

今回は、6桁で添える場合で考えていきます。

ポイントになるのは、数値の桁数がバラバラなので、どのようにしたら、必要な桁数の0（ゼロ）を数値の右側というか、後ろ側に表示することができるのでしょうか。

そこで、REPT関数をつかえば、0（ゼロ）を繰り返して表示することができます。

そして、LEN関数で、現在の桁数を算出させて、その値は、6から減算させれば、0（ゼロ）を何桁分追加すればいいのかが、わかります。

では、B2につぎの数式を設定し、オートフィルで数式をコピーします。

=(A2&REPT(0,6-LEN(A2)))*1

これで完成します。

この数式を確認しておきます。

最初の「A2&」で、A2の後ろに文字結合させるわけですね。

REPT関数の最初の引数は、繰り返す文字を設定しますので「0（ゼロ）」と設定します。

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

ここで、LEN関数をつかうことで、A2の桁数を算出することができますので、6桁で揃えたいので、6からLEN関数の算出結果を減算します。

これで、ほぼほぼ完成なのですが、この時点だと、文字数値型になっているので、計算には使うことができません。

文字数値型だと、B8のようなSUM関数など数式で使いたくても、文字型になってしまっているので、算出することができません。

そこで、数式の最後の「*1」を追加するというわけです。

「*1」を追加することで、数値型に戻るので、算出したい時などには、便利になります。

このように、数値の右側（後ろ側）に「0（ゼロ）」をつけて桁を揃えたい場合には、このような方法もあります。

Excel。同じ列内の複数条件（OR条件）の平均値はDAVERAGE関数で算出します。【AVERAGE】

Excel。同じ列内の複数条件（OR条件）の平均値はDAVERAGE関数で算出します。

＜DAVERAGE関数＞

単一条件の平均値は「COUNTIF関数」をつかうと手早く算出することができますね。

そして、複数条件の平均値を算出するには「COUNTIFS関数」をつかうのですが、この複数条件というのは、AND条件であって、OR条件だとCOUNTIFS関数では算出することができません。

次の表を使って、確認していきます。

例えば、店舗名が新宿で、商品名がAセットの売上高の平均値ならば、COUNTIFS関数をつかえば算出することができます。

これは、店舗名と商品名という別々のフィールドの条件のAND条件であることがわかります。

では、店舗名が新宿と渋谷のみの平均値を算出しようとすると、COUNTIFS関数だと手早く算出することができません。

それならば、A1:F11をテーブルにして、抽出して集計行をつかって算出する方法もありますが、算出するまでの工程も多く、面倒な作業となってしまいます。

そこで、「DAVERAGE関数」をつかえば、手早くOR条件の平均値を算出することができます。

H6に設定した数式は、

=DAVERAGE(A1:F11,E1,H1:H3)

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

最初の引数「データベース」には、見出し行も含めた「A1:F11」を設定します。

2つ目の引数「フィールド」は、算出させたいフィールド名を選択しますので、E1。

最後の引数は、「条件」です。条件は、「H1:H3」と設定します。

この条件を事前に用意する必要があります。

店舗名が、新宿と渋谷というようにしたい場合には、行方向に条件を設定する必要があります。

今回は、単一フィールド内の複数条件というOR条件での平均値でしたが、複雑な条件の場合にも、DAVERAGE関数をつかうと、手早く算出することができますので、試してみると、いいかもしれませんね。

Excel。RANK.EQ関数で算出した数値を丸付き数字にしたいけど、どうしたらいい【circled numbers】

Excel。RANK.EQ関数で算出した数値を丸付き数字にしたいけど、どうしたらいい

＜CHAR＋RANK.EQ関数＞

順位を算出するのには、RANK.EQ関数をつかうことで、手早く算出することができます。

ただ、資料上、通常の数値ではなく、「①」のような丸付き数値で表示したい時には、どのようにしたら、いいのでしょうか。

次の資料を使って、丸付き数字で順位を算出してみました。

C2の数式は、

=CHAR(RANK.EQ(B2,$B$2:$B$11,0)+11552)

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

丸付き数字で、順位が算出していることが確認できます。

考え方として、順位を算出するわけですから、「RANK.EQ関数」をつかうことはわかります。

あとは、算出される数値を、丸付き数字にするには、どのようにしたらいいのかを考えるわけです。

表示形式では、数値を丸付き数字に変更することはできません。

そこで、丸付き数値の文字コードの数値にすることで、「CHAR関数」をつかって、丸付き数値に変更することができます。

丸付き数値の「①」の文字コードは、「11553」なので、RANK.EQ関数で算出して値に、マイナス1した、11552を足した数値を、CHAR関数をつかうことで、変更することができます。

なお、文字コードを知るためには、CODE関数をつかうことで、確認することができます。

また、丸付き数値ですが、20までしかありませんので、それ以上必要な場合は、対応できませんので、注意が必要です。

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

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

＜Excel関数辞典：VOL.70＞

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

SCAN関数

読み方： スキャン　　

分類： 論理 

SCAN(initial_value,array,function)

LAMBDA関数を各値に適用して配列をスキャンし、各中間値を持つ配列を返します

SEARCH関数

読み方： サーチ　　

分類： 文字列操作 

SEARCH(検索文字列,対象,[開始位置])

英字の大文字小文字の区別なく検索する文字列の位置を算出する　

SEARCHB関数

読み方： サーチビー　　

分類： 文字列操作 

SEARCHB(検索文字列,対象,[開始位置])

英字の大文字小文字の区別なく検索す文字列のバイト数を算出する　

SEC関数

読み方： セカント　　

分類： 数学/三角 

SEC(数値)

角度の正割を算出します　

SECH関数

読み方： ハイパーポリック セカント　　

分類： 数学/三角 

SECH(数値)

数値の双曲線正割を算出します　

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

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

＜Facebookページ＞

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

9月5日

Excel。

HEX2BIN関数

読み方は、ヘックストゥビンで、16進数を2進数に変換する

9月6日

Excel。

HEX2DEC関数

読み方は、ヘックストゥデックで、16進数を10進数に変換する

9月7日

Excel。

HEX2OCT関数

読み方は、ヘックストゥオクトで、16進数を8進数に変換する

9月8日

Excel。

HLOOKUP関数

読み方は、エイチルックアップで、横方向の表からデータを検索して抽出する

9月9日

Excel。

HOUR関数

読み方は、アワーで、時刻から"時"を算出する

9月10日

Excel。

HYPERLINK関数

読み方は、ハイパーリンクで、他のドキュメントへのリンクを作成します。

9月11日

Excel。

HYPGEOMDIST関数

読み方は、ハイパージオムディストで、超幾何分布の確率を算出します。

Excel。「1/2」と入力すると日付になるので、手早く「分数」にすることはできないの？【fraction】

Excel。「1/2」と入力すると日付になるので、手早く「分数」にすることはできないの？

＜分数を入力＞

Excelで分数を入力したり、計算で使ったりすることは少ないかもしれません。

ただ、この分数を入力しようとすると、少々厄介なんですね。

例えば、「1/2」とセルに入力すると、「1月2日」と表示されてしまいますし、入力した後に、セルの書式設定ダイアログボックスの表示形式を変更するというのも面倒です。

そこで、次のように入力すれば、すんなり分数として入力することができます。

「0 1/2」と入力します。

「0と1/2」って感じですね。

「0」と「1」の間には半角スペースを入力しています。

あとは、Enterキーで確定しましょう。

日付ではなく、分数で「1/2」と入力されました。

数式バーには、「0.5」と入力されていることがわかります。

ちょっとしたことですが、分数を入力したい時には、知っておくと便利な入力方法ですね。

なお、分数の計算も問題なくできます。

「2/3」と「1/2」を和算させてみました。

きちんと通分して「1 1/6」と算出してくれています。

なお、「1 1/6」ではなく「7/6」と表示したい場合には、表示形式を修正すれば、簡単に設定することができます。

セルの書式設定にある表示形式の「ユーザー定義」で今の状態を確認すると「# ?/?」となっていますので、「#」を削除して「?/?」とするだけで、対応することができます。

「7/6」と表示されていますね。

分数を使う時がありましたら、知っておくといいかもしれませんね。

Excel。上位3位まで平均を楽に算出するには、どうしたらいいの。【higher rank】

Excel。上位3位まで平均を楽に算出するには、どうしたらいいの。

＜AVERAGEIF＋LARGE関数＞

全体の平均を算出するだけならば、オートSUMボタンに含まれている、AVERAGE関数の「平均」をつかえば、すぐに算出することができます。

ただ、そのうち、上位3位までとか、限定した範囲の平均を算出する場合、どのようにしたら、楽に、手早く算出することができるのでしょうか。

次のデータを用意しました。

E1には、データ全体の平均がすでに算出されています。

E1の数式は、

=AVERAGE(B2:B11)

上位3位までの平均ということは、どうやって、上位3位までなのかを判断させる必要があります。

上位3位までのような算出をおこなうには、「LARGE関数」をつかうことで、対応できます。

そして、上位3位までだったら、平均を算出する対象ということで、条件付きの平均で、算出する必要があります。

単一条件の平均を算出するには「AVERAGEIF関数」をつかうと解決できます。

このようなことから、今回は、AVERAGEIF関数とLARGE関数のネストで対応することができそうです。

E2に数式を設定します。

=AVERAGEIF(B2:B11,">="&LARGE(B2:B11,3),B2:B11)

これで、上位3位までの平均を算出することができました。

それでは、ダイアログボックスを表示して、数式を確認しておきましょう。

最初の範囲と、平均対象範囲は、説明は不要だと思いますので、条件について確認します。

上位3位までということで、1位2位3位を算出しようと考えてしまいがちですが、1位などのランキングを算出するのは、LARGE関数ではなくて、RANK.EQ関数です。

上位3位という意味は、上位3位の数値以上なわけですね。

そのため、LARGE関数をつかって上位3位を算出した値以上という条件にすればいいわけです。

また、比較演算子は、「&（アンパサンド）」で結合する必要があります。

「">=LARGE(B2:B11,3)”」と「”（ダブルコーテーション）」で全体を囲いがちですが、これでは、文字になってしまうので、条件が合致しませんので、比較演算子を使用する時は注意する必要があります。

今回のケースは、「上位」でしたが「下位」を条件に使う場合には、「SMALL関数」をつかうことで対応することができます。

単純な平均だけではなくて、他の関数と組み合わせることで、必要とする平均を求めることができるかもしれませんので、色々試してみるといいかもしれませんね。

Excel。数字どおしの組み合わせ数を求める時は、COMBIN関数をつかいます【COMBIN】

Excel。数字どおしの組み合わせ数を求める時は、COMBIN関数をつかいます

＜関数辞典：COMBIN関数＞

COMBIN関数

読み方： コンビネーション　　

分類： 数学/三角 

COMBIN(総数,抜き取り数)

組み合わせの数を算出します　

Excel。ピボットテーブルをつかえば、データの件数と構成比は、手早く算出できます。【composition ratio】

Excel。ピボットテーブルをつかえば、データの件数と構成比は、手早く算出できます。

＜ピボットテーブルで構成比＞

集計機能はExcelに色々搭載されています。

集計作業も、集計したあとに、さらに数式をつくって、構成比を算出しているようなら、単純な数式を作成するだけでも、面倒になってきますし、思っているよりも時間がかかることがあります。

そこで、試しにピボットテーブルをつかったら、どうなるのかを確認してみるといいかもしれません。

例えば、次のデータから、都道府県の件数と、それぞれの構成比を算出する場合で、作業を確認してみます。

表内をアクティブにします。

A1をクリックして、挿入タブのピボットテーブルをクリックします。

テーブルまたは範囲からのピボットテーブルダイアログボックスが表示されます。

範囲を確認したらOKボタンをクリックします。

ピボットテーブル用の新しいシートが挿入されます。

ピボットテーブルのフィールド作業ウインドウで、行ボックスに、都道府県フィールドを1つ。

値ボックスに都道府県フィールドを2つ設定します。

構成比をつくっていきます。

構成比を表示したいフィールド。

今回は2つ目の都道府県フィールドの「個数/都道府県2」のデータをクリックします。

ピボットテーブル分析タブの「フィールドの設定」をクリックします。

値フィールドの設定ダイアログボックスが表示されます。

名前の指定を「構成比」にします。

この名前の指定は、すでにフィールドで使っている場合、重複することができません。

計算の種類タブから、計算の種類を「列集計に対する比率」を選択してOKボタンをクリックします。

なお、パーセント表示で小数点第2位までを表示されるように設定されていますので、小数点の表示桁数を変更したい場合は、表示形式ボタンをクリックして、設定を変更してからOKボタンをクリックします。

これで、都道府県の件数と構成比を算出することができました。

あとは、並べ替えなどすれば、さらにいいですね。

データ量が増えるとか、データが追加されることが多い場合は、集計機能と数式をつかった算出でもいいのですが、ピボットテーブルをつかったら、どうなるのかなと考えてみるのもいいかもしれませんね。

Excel。FILTER関数を使えば必要な列だけの抽出した表が簡単につくれます。【extracted table】

Excel。FILTER関数を使えば必要な列だけの抽出した表が簡単につくれます。

＜FILTER関数＞

テーブルから必要な列だけの別表をつくりたい。

しかも、条件に合うデータのみを抽出した表にしたいならば、FILTER関数をつかうことで、手早く作ることができます。

次の表で確認していきます。

A1:F11には、「ランチ売上表」というテーブル名を設定したテーブルがあります。

このテーブルから、「店舗名・商品名・売上高」の列だけで、さらに「売上高が1500より大きい」データのみを抽出した表を作成したい時には、FILTER関数を使えば、手早くつくることができます。

FILTER関数を使わないならば、該当する列をコピーして、オートフィルターをつかって抽出するなどの方法がありますが、簡単な作業を繰り返すことになるので、意外と面倒な作業となってしまいます。

では、H2にどのような数式をつくったのか、確認していきます。

=FILTER(ランチ売上表[[店舗名]:[売上高]],ランチ売上表[売上高]>1500)

この数式だけで完了します。

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

スピル機能のおかげで、一発で終了します。

引数を確認しておきます。

最初の引数「配列」には、「ランチ売上表[[店舗名]:[売上高]]」と設定しました。

これは、テーブルの店舗名フィールドから売上高フィールドまでという意味になります。

次の引数「含む」ですが、これが条件になります。

「ランチ売上表[売上高]>1500」とすることで、「売上高が1500より大きい」という条件として設定することができます。

FILTER関数に限らず、スピル機能と組み合わせることで、さらに便利になる関数がありますので、色々試してみると、作業効率を改善できるかもしれません。

Excel。複数シートの同じ位置にある値を、別のシートに簡単にまとめて表示したい【Display together】

Excel。複数シートの同じ位置にある値を、別のシートに簡単にまとめて表示したい

＜INDIRECT関数＞

集計シートなど、シートごとの値をまとめるシートを作るときに、シート名を含めたセル参照は簡単ですが、面倒な作業といえます。

例えば、次のようなファイルをつかって説明していきます。

集計シートは、日々の売上高のデータをまとめたものです。

B2には、11月1日のシートの値をセル参照させています。

11月1日のシートは、次のようになっています。

11月2日以降の各シートも同じレイアウトをしています。

つまり、各シートのB4に売上高の合計値があるというわけです。

これを集計シートにまとめた、一覧表を作成したいのが目的です。

では、B2に数式を作って、オートフィルで数式をコピーしてみます。

B2の数式は

='11月1日'!B4

として問題は無いのですが、これをオートフィルで数式をコピーしたら、「0（ゼロ）」と算出されてしまいました。

原因は、

B3をクリックすればすぐにわかります。

数式が、

='11月1日'!B5

となっています。

当然ですが、オートフィルで数式をコピーしても、シート名が連動して変わってくれることはありません。

セル番地のB4がB5になっただけです。

これでは、オートフィルで数式をコピーことはできないので、1シートずつ、セル参照で数式を作らないといけないわけで、3日程度ならば、パワープレイでも、いいかもしれませんが、30日分となれば、30回同じ作業を繰り返すのは、面倒でしかありません。

シート名を修正するとしても、シート数が増えれば、これまた面倒な作業でしかありません。

かといって、Excel VBAでつくるとしても、シート間を操作しなければならず、プログラム文が多くなりそうです。

そこで、「INDIRECT関数」をつかった数式をつくることで、オートフィルで数式をコピーすることができます。

そこで、INDIRECT関数をつかった数式に変更していきます。

B2には、

=INDIRECT(TEXT(A2,"m月d日")&"!b4")

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

オートフィルで数式をコピーすると、今度は、希望通りの値を表示することができました。

数式自体も、長くないこともあり、手早く、簡単に処理することができます。

使用しているINDIRECT関数は、文字列をそのまま数式などで使うことができる関数です。

INDIRECT関数の引数にTEXT関数をネストしています。

このTEXT関数をつかっている理由は、シート名が日付だからです。

INDIRECT関数で日付を参照させると、シリアル値になってしまうので、「11月1日」という文字で使用することができません。

そこで、TEXT関数で表示形式を「月日」に変更することで「11月1日」という文字としてつかえるようになります。

数式にシート名が含まれると、修正などの作業も面倒になることがありますので、アイディアが必要になることがあります。

Excel。COLUMNS関数は、セル範囲の列数を算出することができます。【COLUMNS】

Excel。COLUMNS関数は、セル範囲の列数を算出することができます。

＜関数辞典：COLUMNS関数＞

COLUMNS関数

読み方： カラムズ　　

分類： 検索/行列 

COLUMNS(配列)

セル範囲の列数を算出します　

Excel。2022/8/29-9/4にGAUSS関数など紹介したFacebookページのコメントです。【Trivia】

Excel。2022/8/29-9/4にGAUSS関数など紹介したFacebookページのコメントです。

＜Facebookページ＞

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

8月29日

Excel。

GAUSS関数

読み方は、ガウスで、指定した標準偏差の範囲になる確率を算出します。

8月30日

Excel。

GCD関数

読み方は、ジーシーディーで、整数の最大公約数を算出します。

8月31日

Excel。

GEOMEAN関数

読み方は、ジオミーン（Geometric）で、数値の相乗平均を算出します。

9月1日

Excel。

GESTEP関数

読み方は、ジーイーステップで、数値が境界値以上かを判定する

9月2日

Excel。

GETPIVOTDATA関数

読み方は、ゲットピボットデータで、ピボットテーブル内の値を抽出します。

9月3日

Excel。

GROWTH関数

読み方は、グロウスで、指数回帰分析による値を算出します。

9月4日

Excel。

HARMEAN関数

読み方は、ハーミーン（ Harmonic）で、数値の調和平均を算出します。

Excel。複数のセルをまたいで、手早く斜線を引くには図形の直線をつかいます。【diagonal line】

Excel。複数のセルをまたいで、手早く斜線を引くには図形の直線をつかいます。

＜図形の直線＞

簡単な作業だと思ったら意外と大変というものがExcelには多々あります。

例えば、次のような複数のセルをまたいだ斜線です。

なお、今回は、わかりやすくするため、斜線を赤色にして太くしています。

何が大変なのか、わかりにくいのですが、これ、罫線の斜線だと、結構面倒です。

B8:D11を範囲選択して、セルの書式設定ダイアログボックスをつかって、罫線の斜線を設定してみます。

すると、すべてのセルに斜線が設定されてしまいます。

該当するセルだけに斜線を設定しても、斜線では、ダメなことはわかります。

そこで、図形の直線で描く必要があります。

そして、直線を描くときにポイントがあります。

Altキーを押しながら、ドラッグすると、セルの枠にフィットするように描けるので、綺麗な複数のセルをまたいだ、斜線を描くことができます。

Excel。VBA。大量のデータから該当する一部を塗りつぶしてわかりやすくしたい【lots of data】

Excel。大量のデータから該当する一部を塗りつぶしてわかりやすくしたい

＜Excel VBA：Interiorプロパティ＞

該当するデータをわかりやすくするのに、「条件付き書式」をつかう方法もあります。

ただ、条件付き書式の設定自体は簡単でも、条件数が増えたり、データが増えたり、列数が増えたりすると、範囲選択をして設定するのも面倒になってきます。

そこで、Excel VBAでプログラムをつくって、次の表に塗りつぶしを設定していきます。

今回は、Listening・Writing・Readingの3列を対象とします。

塗りつぶしするセルは、70点台（70～79点）とします。

次のようにプログラムを作ってみます。

Sub 一部色分け()

    Dim i As Long

    Dim j As Long

    

    For j = 2 To 4

        For i = 2 To 11

            If Cells(i, j) >= 70 Then

                If Cells(i, j) < 80 Then

                    Cells(i, j).Interior.ColorIndex = 6

                End If

            End If

        Next

    Next

End Sub

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

このように、70点台のセルに塗りつぶされていることが確認できます。

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

最初は、変数宣言ですね。

Dim i As Long

Dim j As Long

これらの変数は、このあとのFor To Next文の繰り返しで使います。

For j = 2 To 4

    For i = 2 To 11

        If Cells(i, j) >= 70 Then

            If Cells(i, j) < 80 Then

                Cells(i, j).Interior.ColorIndex = 6

            End If

        End If

    Next

Next

最初のFor文は、列方向の繰り返し処理をするためのものです。

二番目のFor文は、行方向の繰り返し処理をしていきます。

70点台の判定は、IF文をつかって、判断させています。

IF文をシンプルに重ねて、70点以上で80点より小さいのかを判断させています。

この条件に合致したら、塗りつぶしの処理を行います。

Cells(i, j).Interior.ColorIndex = 6

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

このように、列・行方向に何回繰り返すのかをFor文を2つ使い、IF文などの判断行を含めるだけで、該当するセルのみに塗りつぶし処理をすることができます。

何度も同じ処理を繰り返すようならば、Excel VBAでプログラム文をつくってみるのもいいかもしれませんね。

Excel。2列1組のデータを簡単に2行の表に変更できるWRAPCOLS関数は画期的です。【WRAPCOLS】

Excel。2列1組のデータを簡単に2行の表に変更できるWRAPCOLS関数は画期的です。

＜WRAPCOLS関数＞

Office Insider版のExcel（Windows:2203 Build 15104以降）にしか、まだない新しい関数に「WRAPCOLS関数」というのがあります。

この新しいWRAPCOLS関数は、いままで面倒な作業を画期的に改善してくれる関数なんです。

次の表をつかって紹介します。

A1:F2には、2列1組にデータがあります。

それを、年と担当をそれぞれでまとめた表に変更したい場合、今までなら、Offset関数をどうつかったらいいのかと考える必要がありましたが、WRAPCOLS関数をつかえば、楽に表をつくることができます。

B5に設定する数式は、

=WRAPCOLS(A2:F2,2)

とするだけで、スピル機能のおかげで、オートフィルで数式をコピーする必要もありません。

最初の引数は、「vector」で、参照ですね。

今回は、A2:F2です。

次の引数の「wrap_count」は、折り返す値です。

このWRAPCOLS関数は、指定した数の値の後に列で折り返すことができる関数なんです。

機会がありましたら、つかってみると、色々使い道が見つかるかもしれませんね。

Excel関数辞典 VOL.69。ROW関数～RTD関数【dictionary】

Excel関数辞典　VOL.69。ROW関数～RTD関数

＜Excel関数＞

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

ROW関数

読み方： ロウ　　

ROW([参照])

セルの行番号を算出する　

ROWS関数

読み方： ロウズ　　

ROWS(配列)

セル範囲の行数を算出します　

RRI関数

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

読み方： レリバント・レート・オブ・インタレスト

RRI(期間,現在価値,将来価値)

将来の価値から利率を算出する　

RSQ関数

読み方： アールエスキュー　　

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

回帰直線の決定係数を算出します　

RTD関数

読み方： アールティーディー　　

読み方： リアルタイムデーターサーバー

RTD(プログラムID,サーバー,トピック1,[トピック2],…)

RTDサーバーからデータを取得する