Excel。行番号列番号からセル番地を構成できるADDRESS関数とは？【function:ADDRESS】

Excel。行番号列番号からセル番地を構成できるADDRESS関数とは？

＜ADDRESS関数＞

使うことは滅諦にない関数というのも結構ありまして、ADDRESS関数というのもその一つではないでしょうか？

ADDRESS関数は、行番号と列番号で指定したセル番地を構成することができる関数なんです。

ADDRESS関数の読み方はそのものズバリ「アドレス」です。

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

ADDRESS（行番号,列番号,[参照の種類],[参照形式],[シート名]）

関数の分類は、「検索・行列」に所属しています。

次の表を使って確認してみましょう。

ADDRESS関数も手入力するほうが楽な関数です。

というのも、入力途中で引数のフォローが入ってくれるからです。

参照の種類は、1の絶対参照から4の相対参照まで選択することができます。

1の絶対参照は、$B$4

2の行（絶対参照）/列(相対参照)は、B$4

3の行（相対参照）/列(絶対参照)は、$B4

4の相対は、B4

と設定できます。

今回は、「1」の絶対参照を採用しました。

次に設定するのが、「参照形式」

R1C1形式化、A1形式かを選択することができます。

今回は、「1」のA1形式を採用します。

シート名はこのシートとしますので、省略します。

完成した数式は、

=ADDRESS(4,2,1,1)

結果を見てみると、「$B$4」

えっ、B4の100が表示されるのでは？と思ったかもしれませんが、このADDRESS関数は、あくまでも、セル番地を構成するだけの関数なので、セルの内容を表示させるものではありません。

セルの内容を表示させるには、INDIRECT関数の力を借りる必要があります。

A1の数式を次のようにINDIRECT+ADDRESS関数のネストに変更してみます。

=INDIRECT(ADDRESS(4,2,1,1))

すると、B4のセル内容を表示することができました。

Excel VBAでつかうというのもいいですし、アイディアによって、使えそうな関数がExcelにはたくさんありますので、色々調べたり、組み合わせみると、現場で使えるものも沢山ありそうですね。

Excel。月間や四半期などの期間計算をするにはどうしたらいいの？【Period calculation】

Excel。月間や四半期などの期間計算をするにはどうしたらいいの？

＜SUMIFS関数＞

データからこの期間の合計値を算出したいということは多々あるケースではありますが、意外とトラブルを起こすというか、上手くいかないことがあるようなので、確認してみましょう。

次の表を用意しました。

このデータから7月1日～9月30日までの売上高の四半期合計をE1に算出していきます。

期間計算を行う場合のポイントは、開始と終了という2つの条件が発生するというわけです。

Accessだと、クエリなどで期間を抽出するときには、Between～And演算子という比較演算子をつかうことで簡単に算出することができますが、Excelには、Between～And演算子というのはありません。

そこで、2つの条件ということなので、複数条件に対応した合計値を算出できる関数。【SUMIFS関数】をつかうことで、算出することができます。

E1には、次の数式を設定したら、確認してみましょう。

=SUMIFS(B2:B10,A2:A10,">=2021/7/1",A2:A10,"<2021/10/1")

算出結果は、次のようになります

複数条件のうえ、合計値を算出したいわけなので、『SUMIFS関数』をつかうわけですが、引数を確認したいので、SUMIFS関数ダイアログボックスを表示します。

確認したいところは、条件1と条件2です。

条件1には、「">=2021/7/1"」

条件2には、「"<2021/10/1"」

と設定しています。

ポイントが2つあって、一つ目として、条件に「”（ダブルコーテーション）」で比較演算子も含めて囲んでいることです。

文字型ではありませんが、「”（ダブルコーテーション）」で囲まないとエラーになってしまいます。

ただ、注意点があって、今回は直接条件の日付を引数内で入力しているので、条件全体に「”（ダブルコーテーション）」で囲んでいますが、セル番地を使う場合は、「”>=”&A1」のように、比較演算子を「”（ダブルコーテーション）」で囲み、「&（アンパサンド）」でセル番地を接続させた形で設定します。

引数内直接条件なのか、セル参照なのかによって、設定の仕方が、かわるところがちょっと厄介に感じるところかもしれませんね。

もう一つのポイントですが、条件2を「期間の翌日」にしている点です。

15日とかはっきりした日ならば、終了日そのものでいいのですが、「月末」の場合、2月のうるう年問題があり、過去の場合その年が、うるう年だったのか確認しないといけないので、いちいち、28日なのか29日なのかを確認するぐらいなら、翌月の一日にしたほうが、間違いがありません。

このように、期間計算をするときには、SUMIFS関数をつかって算出することがあると思いますので、その時には、注意しておくといいかもしれませんね。

今週のFacebookページの投稿 2021/3/22-2021/3/28【one thing】

今週のFacebookページの投稿　2021/3/22-2021/3/28

＜Facebookページ＞

Facebookページで【書いてみた】ワンポイントです。

3月22日 Excel。

YIELDDISC関数。

読み方は、イールドディスクで、割引債の年利回りを算出します。

3月23日 Excel。

YIELDMAT関数。

読み方は、イールドマットで、満期利付債の利回りを算出します。

3月24日 Excel。

ZTEST関数。

読み方は、ゼットテストで、Z検定の上側確率を算出します。

3月26日 Excel。

Z.TEST関数。

読み方は、ゼット・テストで、Z検定の上側確率を算出　Excel2010以降

3月27日 Excel。

範囲選択が基本中の基本ですよね。

3月28日 Excel。

範囲選択。

複数のセルを選択するときは、Ctrlキーを押しながらクリックですね。

Excel。移動平均グラフを作りたいけど、横軸を5日おきにしたい。【moving average】

Excel。移動平均グラフを作りたいけど、横軸を5日おきにしたい。

＜移動平均グラフの横軸アレンジ＞

移動平均グラフというのがあります。

売上金額など、日々のデータの推移をみると、データが均等というわけではなくて、多い日もあれば少ない日もあるわけです。

そこで、移動平均というのをつかうのですが、数値として算出しただけでは、イメージしにくいこともあるので、「移動平均グラフ」をつくることがあります。

移動平均グラフを作ること自体は難しくはないのですが、移動平均グラフは長期間にわたるデータを使ってつくるので、横軸の表示数が多くなってしまい、見やすくするはずの移動平均グラフが、横軸が原因で見づらくなってしまうことがあります。

そこで今回は、横軸の表示にも考慮した、移動平均グラフをつくっていきます。

移動平均グラフをつくるには、まず移動平均を算出した表を作る必要があります。

C7の数式は、単純に5日間の平均値を算出しております。

=AVERAGE(B3:B7)

なお、データは、5月31日までの2ヶ月としています。

それでは、移動平均グラフをつくっていきます。

見出し行のA2:C2とA7:C63を範囲選択します。

5日平均を折れ線グラフにしますので、挿入タブの「すべてのグラフを表示」をクリックします。

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

すべてのグラフタブの「組み合わせ」を選択します。

売上高のグラフの種類を「集合縦棒」に設定します。

5日平均のグラフの種類は、「折れ線」に設定したら、OKボタンをクリックします。

移動平均グラフが挿入されました。

説明の都合があるので、グラフタイトルを削除して、大きく表示させるようにしております。

グラフが挿入された迄はいいのですが、横軸がグラフの大きさに合わせて、表示数が多くなっていきます。

それでいいのかもしれませんが、横軸の表示を10日おきに表示するように設定していきます。

横軸をクリックして、書式タブのグラフの要素が「横（項目）軸」になっていることを確認したら、「選択対象の書式設定」をクリックします。

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

軸のオプションの単位の「主」を10と入力します。

グラフの横軸は、このように変わりました。

データが多いく横軸が日付などの場合は、一定期間で横軸を設定することができます。

あとは、グラフエリアやプロットエリアの背景色を設定したり、フォントサイズを変更したりして、完成ですね。

今回は、移動平均グラフを例にしましたが、データ量が多い場合などでは、横軸の表示方法をアレンジすることができますので、状況によって使ってみるといいかもしれませんね。

Excel。グラフの復習。マイナスデータの3-D集合縦棒～五角形横棒グラフ【graph】

Excel。グラフの復習。マイナスデータの3-D集合縦棒～五角形横棒グラフ

＜グラフの復習＞

Excelのグラフは、用途に合わせて様々なグラフを作ることができます。

今回は、グラフの復習ということ、4つをピックアップ

・Excel。マイナスデータの3-D集合縦棒グラフを作ってみよう。

・Excel。作る機会が少ないバブルチャートの作り方を確認しておきましょう。

・Excel。ABC分析でお馴染みのパレート図をOffice365で作ってみよう。

・Excel。横グラフの先端を鋭角にした五角形横棒グラフを作るには？

Excel。マイナスデータの3-D集合縦棒グラフを作ってみよう。

マイナスデータがある3-D集合縦棒グラフの場合、補足ポイントを確認していくことにします。

＜続きはコチラ＞

Excel。マイナスデータの3-D集合縦棒グラフを作ってみよう。

https://infoyandssblog.blogspot.com/2019/07/excel3-dcollective-vertical-bar-chart.html

Excel。作る機会が少ないバブルチャートの作り方を確認しておきましょう。

通常グラフは2つの要素から構成させたものですが、3つの要素から構成されたグラフである、「バブルチャート」は、あまり作成する頻度が少ないので、作り方の確認をしておきましょう。

＜続きはコチラ＞

Excel。作る機会が少ないバブルチャートの作り方を確認しておきましょう。

https://infoyandssblog.blogspot.com/2019/08/excelbubble-chart.html

Excel。ABC分析でお馴染みのパレート図をOffice365で作ってみよう。

パレート図は、集合縦棒グラフと折れ線グラフの2種類が混在したグラフですので、第2軸を使用したグラフを作成します。

＜続きはコチラ＞

Excel。ABC分析でお馴染みのパレート図をOffice365で作ってみよう。

https://infoyandssblog.blogspot.com/2019/08/excelabcoffice365pareto-chart.html

Excel。横グラフの先端を鋭角にした五角形横棒グラフを作るには？

横棒グラフの先端を鋭角にした五角形横棒グラフを作るにはどのようにしたらいいのでしょうか？

＜続きはコチラ＞

Excel。横グラフの先端を鋭角にした五角形横棒グラフを作るには？

https://infoyandssblog.blogspot.com/2019/09/excelmodified-bar-chart.html

Excel。シートを大量に追加して、指定したシート名に変更するのが面倒なので楽したい。【Add sheets】

Excel。シートを大量に追加して、指定したシート名に変更するのが面倒なので楽したい。

＜Excel VBA＞

Excelの処理において、とても単純な作業。

例えば、新しいシートを追加して、そのシート名を変更するなんてことは、簡単な作業ではありますが、処理をする量が増えてしまうと、面倒以外の何物でもありません。

単純作業の繰り返しで、面倒に感じる場合には、Excel VBAでマクロを作ってExcelに処理させちゃうことができれば、作業効率も改善するし、自分自身のワーク負荷も緩和することが出来るかもしれません。

そこで、今回は、大量な新しシートを追加して、しかも追加したシートのシート名も指定して変更までするマクロを作っていきます。

意外と短いプログラム文で、出来ちゃいます。

最初に用意するシートがあります。

追加するシート用のシート名のデータを用意します。

今回は、5件分ですが、100件用意すれば100枚の新しいシートを追加することができて、そのシート名も変更することができます。

もしも五十音順にしておきたい場合には、並び替えを事前にしておくとよいかと思います。

作成するExcel VBAのプログラム文を見ていくことにしましょう。

Sub シート作成()

    Dim i As Long

    Dim sheet_name As String

    Dim lastrow As Long

        

    sheet_name = ""

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

    

    For i = 2 To lastrow

    sheet_name = Worksheets("シート名作成").Cells(i, "a")

    Sheets.Add(after:=Sheets(Sheets.Count)).Name = sheet_name

    Next

End Sub

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

このように、A列のシート名の順番通りにシートが追加されていることが確認できます。

ちょっとしたことですが、知っていると便利かと思われます。

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

まずは、お馴染みの変数宣言ですね。

    Dim i As Long

    Dim sheet_name As String

    Dim lastrow As Long

変数名「sheet_name」はシート名を代入しておく変数です。

変数名「lastrow」は、繰り返し数のための変数です。

sheet_name = ""

「sheet_name」を初期化しておきます。

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

シート名の件数を確認するためのプログラム文です。

動きとしては、シートの最終行まで一度行き、そこから、上に向かって移動すれば、データの最終行になるという考え方ですね。

For i = 2 To lastrow　～ Next 文で、繰り返し処理をします。

データが2行目からなので、2から始めて、最終行の行番号まで繰り返します。

sheet_name = Worksheets("シート名作成").Cells(i, "a")

変数「sheet_name」に、シート名を代入するプログラム文ですね。

Sheets.Add(after:=Sheets(Sheets.Count)).Name = sheet_name

Sheets.Addで、シートを追加することが出来ます。

after:=Sheets(Sheets.Count)　は、Sheets.Countで、シート数を数えます。

例えば2枚のシートがあれば、Sheets(2)となり、「after」なので、Sheets(2)の右側に挿入されます。

afterを設定しないと、左側にシートが挿入されてしまうので、移動するのが面倒なので、afterの設定をしておきました。

Name = sheet_name　で、シート名を設定することができます。

比較的、わかりやすいプログラム文ではありますが、大量のシートを挿入しないといけない時などに知っておくといいかもしれませんね。

今週のFacebookページの投稿 2021/3/15-2021/3/21【one thing】

今週のFacebookページの投稿　2021/3/15-2021/3/21

＜Facebookページ＞

Facebookページで【書いてみた】ワンポイントです。

3月15日 Excel。

XIRR関数。読み方は、エックスアイアールアールで、非定期キャッシュフローに対しる内部利益率を算出します。

3月16日 Excel。

XNPV関数。読み方は、エックスエヌピーヴイで、非定期キャッシュフローに対する正味現在価値を算出します。

3月17日 Excel。

XOR関数。読み方は、エクスクルーシブ・オアで、複数の条件で奇数の数を満たすかどうかを調べる

3月18日 Excel。

YEAR関数。読み方は、イヤーで、日付から年を算出します。

3月19日 Excel。

YEARFRAC関数。読み方は、イヤーフラクションで、2つの日付の間の期間を年数で算出

3月20日 Excel。

YEN関数。読み方は、エンで、数値を四捨五入して、円記号（￥）を付けた文字列に変換する

3月21日 Excel。

YIELD関数。読み方は、イールドで、定期利付債の利回りを算出します。