Excel。シートが何枚目かを算出するのがSHEET関数です。【SHEET】

Excel。シートが何枚目かを算出するのがSHEET関数です。

＜関数辞典：SHEET関数＞

SHEET関数

読み方： シート　　

分類： 情報 

SHEET([値])

シートが何枚目かを算出します

Excel。ファイル内のシートは何枚あるの。このシートは何枚目にあるのがわかります。【SHEETS】

Excel。ファイル内のシートは何枚あるの。このシートは何枚目にあるのがわかります。

＜SHEETS関数・SHEET+INDIRECT関数＞

ファイル内のシートが多くなると、いったい、何枚あるのか数えるのは面倒です。

そこで、SHEETS関数をつかうことで、現在何枚のシートで構成されているのかを確認することができます。

用意したのは、次のファイルです。

シート管理・7月・8月・9月・四半期集計という5枚のシートで構成されています。

B2にSHEETS関数の数式をつくります。

=SHEETS()-1

SHEETS関数は、現在のファイルのシート数を算出してくれる関数です。

シート管理シートを除きたいとしたら、「-1」とすることで、シート枚数の算出結果を増減することもできます。

これで、シート数を数えられることが確認できました。

では、そのシート名が何枚目にあるのか確認したい場合、どのようにしたらいいのか説明します。

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

=SHEET(INDIRECT(A5&"!a1"))

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

これで、それぞれ左から何枚目にあるのかがわかります。

この場合、SHEETS関数ではなく、SHEET関数をつかいます。

SHEET関数は、参照されたシートのシート番号を求めることができる関数です。

つまり、左から何枚目にあるのかを求めることができる関数というわけです。

SHEETS関数は、枚数を求める関数なので、「S」のありなしで、使い方が変わります。

では、 数式を確認してみましょう。

SHEET関数の引数に、INDIRECT関数を使用しています。

指定する文字列を直接参照することができる関数です。

なので、A5の値。

7月という文字そのものをつかうことができますので、

「A5&"!a1"」というのは、「7月!a1」という意味です。

「a1」はセル番地なので、「c1」でも構いません。

よって、7月のシートは、左から2枚にあることがわかりました。

ちなみに、シートを移動すると、算出された値も連動してかわります。

また、シート名が見つからない場合には、#REF!というエラーが表示されます。

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

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

＜Excel関数辞典：VOL.71＞

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

SECOND関数

読み方： セコンド　　

分類： 日付時刻 

SECOND(シリアル値)

時刻から""秒""を算出する　

SEQUENCE関数

読み方： シークエンス　　

分類： 数学/三角 

SEQUENCE(行,[列],[開始],[目盛り])

数列を返す　

SERIESSUM関数

読み方： シリーズサム　　

分類： 数学/三角 

SERIESSUM(x,n,m,係数)

べき級数を算出する　

SHEET関数

読み方： シート　　

分類： 情報 

SHEET([値])

シートが何枚目かを算出します　

SHEETS関数

読み方： シーツ　　

分類： 情報 

SHEETS([参照])

シートの数を算出します　

Excel。1つのシートのデータを、各シートの同じセルに転記するには【Posting】

Excel。1つのシートのデータを、各シートの同じセルに転記するには

＜INDIRECT+SHEET関数＞

簡単そうな処理でも繰り返して設定するのは、結構面倒なわけです。

そこでExcel VBAを使ってとか考えるのですが、1つのシートにあるデータを、各シートの同じセルに転記することだったら、Excel VBAでマクロを作るよりも簡単に設定することができます。

1枚目のシート「売上目標」には次の表が用意されています。

そして、新宿から川崎までの各シートには、次のような表が用意されています。

やりたいことは、例えば、シート「売上目標」のC2にある新宿店の売上目標の数値をシート「新宿」のB2に転記したいわけです。

処理的には簡単ですが、繰り返すと面倒なパターンですね。
例えば、シート「新宿」の場合だと、B2をクリックして、

=売上目標!C2
と設定するだけの、単なる『セル参照』にすぎません。

ところが、オートフィルを使って数式をコピーできるならばいいのですが、各シートの同じセルにそれぞれの店舗の売上目標を転記したいわけです。

そこで、Excel2013から登場したSHEET関数とINDIRECT関数を使うことで簡単に転記することができます。

最初に行うのは、転記したいシートを作業グループ（選択）します。

転記したいセルをクリックします。今回はB2をクリックして、数式を設定していきます。

INDIRECT関数ダイアログボックスを表示すると手入力があるので、直接数式を設定したほうが楽だと思います。

B2の数式は、
=INDIRECT("売上目標!c"&SHEET())
それでは各シートを確認してみましょう。

たった、これだけです。

Excel VBAとかでマクロをつくらないと…なんて思いがちですが、数式で簡単に算出できる方法も知っているといいですよね。

さて、数式の説明をしておきましょう。
=INDIRECT("売上目標!c"&SHEET())

INDIRECT関数はセル参照を表す文字列を間接的に参照することができる関数です。

では間接的に参照するための引数ですが、
"売上目標!c"&SHEET()
シート「売上目標」のCまでがダブルコーテーションで囲んでいます。

そして、「&」で文字結合しているのが、SHEET()。

SHEET関数は、Excel2013で登場した新しい関数です。

このSHEET関数は、ブックの左から何枚目にあるのかがわかる関数なので、左から2枚目にある、シート「新宿」は2と算出される仕組みです。

なので、シート「新宿」の場合、
"売上目標!c"&SHEET()は、売上目標!C2

ということになり、INDIRECT関数を使うことで、
=売上目標!C2
という数式を作ることができたわけです。

なので、シートの順番が非常に重要で、新宿と渋谷のシート入れ替えてしまうと、数値も入れ替わってしまうので、注意が必要です。

Excel。シート数を知りたい！この名前のシートは何枚目にあるの？【Sheets】

Excel。シート数を知りたい！この名前のシートは何枚目にあるの？

＜SHEETS関数・SHEET&INDIRECT関数＞

シートを追加したりコピーしたりということは、
ごくごく普通の処理なのですが、シートを増やしていくと、
Excelブック（ファイル）の中に何枚のシートがあるのか？

とか、

このシートからこのシートまでは何枚あるのか？

あるいは、
この名前のシートは何枚目にあるのか？

ということを知りたいことが出てきます。
そこで今回は、シート関係のテクニックについてみていきましょう。

今回使用するブック（ファイル）を確認しておきましょう。

一枚目は「シート一覧」というシート名で、
新宿・渋谷・池袋・横浜・川崎の各シートで合計6枚のシートで
構成されているブック（ファイル）です。

【ブックには何枚のシートがあるのでしょうか？】

E2に店舗数を算出しています。
これはどのようにしたのでしょうか？
今回のようにシートの枚数が少ないと自力で数えることも出来ますが、
枚数が増えると大変です。

そこで、登場するのが『SHEETS関数』です。

このSHEETS関数は、ブック（ファイル）にある
シート数を数えてくれる関数です。

また、このSHEETS関数は手入力だと簡単に作成できます。

E2をクリックして、次の数式を設定しましょう。
=SHEETS()-1
SHEETS関数でブック（ファイル）の全枚数を算出します。

店舗数だけにしたいので、
「シート一覧」の1枚分を減算しますので、-1（マイナス1）しています。

なお、シートを非表示にしても、数えてしまいますので注意が必要です。

【ここからここまでのシートの枚数を知りたい】

次は、E3に都内にある店舗数を数えたいわけですが、
先程のSHEETS関数をアレンジすれば簡単に算出することが出来ます。

E3の数式は、
=SHEETS(新宿:池袋!A1)
新宿～池袋までということで、引数=カッコの中に、
新宿:池袋!A1と入力しているだけです。

形としては、『3-D集計』に似ています。

なお、”!A1”という部分を忘れるとエラーになってしまいますので、
忘れないようにしましょう。

【このシートは左から何枚目にあるの？】

シート枚数を数えるだけではなくて、
例えば、新宿というシート名は、いったい何枚目にあるのだろうか？
ということを知りたい場合、どうしたらいいのでしょうか？

ここはSHEET関数とINDIRECT関数のネスト技で算出することが出来ます。

B3の数式は、
=SHEET(INDIRECT(A3&"!a1"))
というようになっています。

気を付けるのは、SHEET関数です。
”S”はありません。このSHEET関数は、シート番号を算出する関数です。

SHEET関数の引数（カッコの中）にINDIRECT関数を使っています。
INDIRECT関数は、その内容をそのまま使用することが出来る関数です。
ということで、A3は新宿ですね。

なので、「新宿のA1があるのは、シート番号いくつ」

という引数になっていますから、
左から2枚目に新宿というシートがありますので、
2という結果が算出されたわけです。


最後に、今回紹介した、SHEETS関数とSHEET関数は、
とても便利な関数ではありますが、
Excel2013で登場した関数なので、残念ながら、
Excel2010にはこの関数がありませんので、使うことが出来ません…。