Excel。INDIRECT関数で文字列で参照されるセルの値を返せます【INDIRECT】

Excel。INDIRECT関数で文字列で参照されるセルの値を返せます

＜関数辞典：INDIRECT関数＞

INDIRECT関数

読み方： インダイレクト　　

分類： 検索/行列 

INDIRECT(参照文字列,[参照形式])

文字列で参照されるセルの値を算出します

Excel。値を検索して、複数の列から対応する結果を抽出したい【Multi-column】

Excel。値を検索して、複数の列から対応する結果を抽出したい

＜VLOOKUP関数＋IF関数・XLOOKUP＋INDIRECT関数＞

会員と一般で、単価が異なる表から、会員なら会員の、一般なら一般の単価を抽出し表示したい場合、どのようにしたらいいのでしょうか。

次の表をつかって説明します。

A2の所属が一般で、商品コードがB2のA01です。A5:D7の表から、商品コードがA01の

一般なので、D2の単価は、100と表引きされています。

A2の値を会員にすれば、D2の単価は80となるようにしたいわけです。

このような場合、VLOOKUP関数をつかうといいように思えますが、うまくいきません。

C2の商品名は、VLOOKUP関数で対応することは、できます。

C2の数式は、

=VLOOKUP(B2,A6:D7,2,FALSE)

VLOOKUP関数の最初の引数、検索値は、B2

2つ目の引数は、範囲なので、A6:D7 と設定します。

3つ目の引数は、列番号です。

2つ目の引数で設定した範囲の左から何列目のデータを表示するのかということなので、左から「2」列目なので、2と設定します。

最後の引数の、検索方法は、完全一致ですから、FALSE。

商品名は、VLOOKUP関数でもいいのですが、問題は、列番号です。

会員ならば、3。一般なら4としなければなりません。

このような場合、

=VLOOKUP(B2,A6:D7,IF(A2="会員",3,4),FALSE)

と列番号を判断させるようにIF関数をつかってもいいと思います。

ただし、所属数が会員・準会員・一般・学生のように、増えた場合、IF関数では対応するのが大変になるので、多分岐できる関数を使う必要がでてきます。

また、所属、それぞれの表をつくって、INDIRECT関数をつかって対応する方法があります。

ただ、その方法では、別表を作る必要がありますので、面倒です。

そこで、INDIRECT関数はつかうのですが、VLOOKUP関数よりもXLOOKUP関数で対応する方法があります。

事前に「名前の定義」を設定します。

C6:C7に、「会員」

D6:D7に、「一般」

と名前を設定しました。

D2の数式は、

=XLOOKUP(B2,A6:A7,INDIRECT(A2),"",0,1)

A2の所属を変更しても、会員と一般、それぞれの数値を表引きしてくれます。

もし、所属数が増えても、名前の定義を増やすだけで、数式を変更する必要はありません。

では、

=XLOOKUP(B2,A6:A7,INDIRECT(A2),"",0,1)

XLOOKUP関数の数式を確認します。

最初の引数は、検索値　なので、B2を設定します。

2つ目の引数は、検索範囲。

検索値が所属されている範囲なので、A6:A7。

3つ目の引数は、戻り範囲。

表示したい範囲です。

ここに、会員なのか、一般なのかで、戻り範囲を対応させたい。

そこで、INDIRECT関数をつかうことで、戻り範囲を変更させています。

INDIRECT関数は、値そのものをつかうことができます。

名前の定義で、会員と一般を設定していますので、切り替えることができるというわけです。

4つ目の引数は、見つからない場合。

見つからない時は「””（ダブルコーテーション×2）」で空白と設定します。

5つ目の引数は、一致モードなので、０の完全一致にしました。

6つ目の引数は、検索モード。

先頭から検索させますので、1と設定します。

このように、XLOOKUP関数とINDIRECT関数を組み合わせることで、値を検索して、複数の列から対応する結果を抽出することができます。

Excel。VLOOKUP関数で、手早く別々の表から抽出するには、どうしたらいい【multiple list】

Excel。VLOOKUP関数で、手早く別々の表から抽出するには、どうしたらいい

＜VLOOKUP+INDIRECT関数・名前の定義＞

リストから該当するデータを抽出することができる、VLOOKUP関数。

このリストの選択先が複数ある場合、どのようにしたら、手早く抽出することができるのでしょうか。

 

C2の数式は、

=VLOOKUP(B2,INDIRECT(A2),2,FALSE)

どのような仕組みなのか説明していきます。

A2に、「校庭」「体育館」を入力します。会場番号を入力すると、「校庭」の3番である「陸上」が抽出されるという仕組みです。

この数式を作る場合には、事前に引数の範囲に該当するところに、名前の定義を使って「名前」を設定します。

A5:B7には、「校庭」という名前を設定してあります。

D5:E7には、「体育館」という名前を設定してあります。

あとは、VLOOKUP関数を設定していきます。

最初の引数の検索値には、「B2」を設定します。

2つ目の引数の範囲ですが、ここが2か所あるわけです。

そこで、名前の定義を指定してあげれば、その範囲の検索値からデータを抽出してくれるわけですが、検索する範囲を変更するたびに、数式を変更するのは大変です。

そこで、INDIRECT関数をつかうことで、範囲に、名前の定義で設定した名前を指定することができます。

なので2つ目の引数は、「INDIRECT(A2)」と設定します。

これで、A2に、「校庭」と入力されれば、名前の定義で設定した「校庭」の範囲を選んでくれます。

3つ目の引数の列番号には、「2」。

2つ目の引数で選択した範囲の左から2列目に、抽出したい列がありますので、「2」と設定するわけですね。

最後の引数の検索方法は、「FALSE」。

完全一致で抽出しますので、「FALSE」または、「0」と設定します。

Excelでは、FALSE=0なので、「0」と設定しても大丈夫です。

これで、A2の値を校庭・体育館と切り替えれば、それぞれの範囲から該当するデータを抽出することができます。

このように、VLOOKUP関数は、アイディアを追加することで、様々なビジネスシーンでさらに使えるようになりますので、色々試してみるといいかもしれませんね。

なお、あとプラスするとしたら、A列に入力規則のリストをつかうと、「校庭」「体育館」を選択して選ぶことができますので、おすすめの機能ですね。

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。月別シートの合計値を集めた表を効率よく作りたい時はINDIRECT関数が便利です。【INDIRECT】

Excel。月別シートの合計値を集めた表を効率よく作りたい時はINDIRECT関数が便利です。

＜INDIRECT関数/INDIRECT＋SUBSTITUTE＋(LEFT＋CELL関数＞

月ごとに集計されているシートがあります。

それぞれのシートの合計値をまとめた、「年間」シートに数値を設定して年間合計を算出した集計シートをつくりたいわけです。

今回のケースは、サンプルなので、2枚のシートですんでいますが、年間となれば12枚のシートですし、もっと多くのシートからデータをコピーする必要があるとすれば、面倒な作業となってきます。

Excel VBAでプログラムをつくってもいいのですが、数式レベルで、効率よくコピーする方法はないのでしょうか？

このような場合、どのようなパターンがあるのか、ないのかを見つけるところから考えていくといいですね。

集計先のシートのB2に、セル参照の数式を設定してみましょう。

='1月'!B5

となっています。

横方向に、オートフィルで数式をコピーすれば、

='1月'!C5

='1月'!D5

と、横方向は、うまく参照結果が表示されていますが、縦方向にオートフィルで数式をコピーしても、当たり前ですが、うまくいきません。

理由は、シート名が違うからです。

シート名を変更する”だけ”かもしれませんが、とても面倒な作業です。

よくみると、シート名と、A列のデータが同じになっています。

シート名をA列に入力されている値そのものを使うことができれば、上手くいきそうです。

そこで、登場するのが、「INDIRECT関数」です。

B2にINDIRECT関数をつかって、次のような数式に変更してみましょう。

B2の数式は、

=INDIRECT($A2&"!b5")

この関数は、

='1月'!B5

という数式をどうやったらつくれるのかをイメージして作る関数です。

A2に1月という文字があるので、それをつかいたいわけです。

オートフィルで数式をコピーすることを前提としていますので、列固定の複合参照にしています。

そして、「&（アンパサンド）」で「"!b5"」という文字を結合させています。

縦方向に、オートフィルで数式をコピーすると、きちんと参照してくれたのですが、横方向にオートフィルで数式をコピーしたら、うまくいきません。

原因は、「"!b5"」。

文字になってしまっているので、オートフィルで数式をコピーしても「b」が「C」に自動的に変わってくれることはありません。

本当は、変わってくれれば、いいのですが…

そこで、どうやったら、「b」を「c」に出来るのかを考えて、B2の数式を次のように修正しました。

=INDIRECT($A2&"!"&SUBSTITUTE(LEFT(CELL("address",B1),2),"$","")&5)

なんか、長くなっちゃいましたが、この数式を、縦方向。

横方向にオートフィルで数式をコピーすると、綺麗に、参照することができました。

 

では、この長くなった数式の説明をしてきます。「b」を「c」にするための数式が、

SUBSTITUTE(LEFT(CELL("address",B1),2),"$","")

です。

内側から説明しないと、わからないので、最初は、CELL("address",B1)

CELL関数は、セルの情報を算出してくれる関数で、引数にaddressをつかうと、絶対参照がついたセル番地を文字として、算出してくれます。

B1のセル情報なので、「$B$1」という文字が算出されます。

LEFT(CELL("address",B1),2)

は、LEFT(“$B$1”,2)ということなので、LEFT関数をつかって、左から2文字分を抽出します。

これで、「$B」という文字が算出されています。

「$AA$1」だったら3文字分を抽出する必要があります。

SUBSTITUTE($B,"$","")　という状態になっていることがわかります。

SUBSTITUTE関数は、置換することが出来る関数なので、「$」を空白に置換させます。

これで、「B」だけを抽出することができるます。

ということで、シート名を参照できるセルをつくることで、INDIRECT関数をつかうことで、別シートから必要なデータを参照することができます。

Excel関数辞典 VOL.40。IMSQRT関数～INDIRECT関数【dictionary】

Excel関数辞典　VOL.40。IMSQRT関数～INDIRECT関数

＜Excel関数＞

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

IMSQRT関数

アイエムスクエアルート

複素数の平方根を算出する

IMSQRT（複素数）

IMSUB関数

アイエムサブ

複素数の差を算出する

IMSUB（複素数1,複素数2）

IMSUM関数

アイエムサム

複素数の和を算出する

IMSUM（複素数1[,複素数2]）

IMTAN関数

アイエムタンジェント

複素数のタンジェントを算出する

IMTAN（複素数）

INDEX関数

インデックス

セル範囲から縦横座標で値を抽出

INDEX（配列,行番号,[列番号]）

INDEX（参照,行番号,[列番号],[領域番号]）

INDIRECT関数

インダイレクト

文字列で参照されるセルの値を算出

INDIRECT（参照文字列,[参照形式]）

Excel。入力規則のリストでアイテム数が多すぎて、選ぶのが大変なのでどうにかしたい。【Input rule】

Excel。入力規則のリストでアイテム数が多すぎて、選ぶのが大変なのでどうにかしたい。

＜入力規則・名前の定義&INDIRECT関数＞

発注書や納品書など、入力ミスをすると致命的になりかねない書類って結構あるわけです。

そこで、VLOOKUP関数をつかったりしてミスを抑制するわけですが、さらに便利で入力ミスを抑制することができる、『入力規則のリスト』をつかうと便利ですね。

 

ただ、便利なのですが、リストに表示されるアイテム数が多いと、スクロールしなくてはならないし、探すのも大変。

結局入力したほうが早いというのでは、入力ミスを抑制する効果が減ってしまいます。

そこで、ジャンルやカテゴリーという区分けできるものがあれば、区分けするものを選択した後に、該当するアイテムだけをリストに表示できれば、入力する速度を改善でき、入力ミスも抑制することができます。

今回は、このような表を用意しました。

 

E:Fは、商品リストです。入力規則のリストを作る時には、E列の値をつかうわけですね。

そして、ポイントなのが、H列。

今回は、商品コードの頭文字で管理しているので、ジャンルとしてA・B・Dを用意しました。

A列にジャンルという列を設定しております。

入力規則のリストで、探しやすくするためで使う場合は、印刷時に外すようにするといいですね。

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

A2:A5に入力規則のリストを設定します。

A2:A5を範囲選択して、データタブの「データの入力規則」をクリックします。

 

データの入力ダイアログボックスが表示されます。

 

設定の入力値の種類を「リスト」にして、元の値にH2:H4を範囲選択します。

絶対参照が自動的に設定されますので、あとはOKボタンをクリックします。

A2をクリックすると、▼のリストが設定されていることが確認できます。

 

B列の商品コードに入力規則のリストを設定したいのですが、このままでは、頭文字がAの商品だけをリストに表示することはできません。

名前の定義をつかって、このアイテムはAというようにわかるようにしていきます。

 

頭文字がAの商品を範囲選択します。

A2:A4を範囲選択して、名前ボックスにAと入力して設定します。

これで、名前の定義が完成しました。

同じように、BとDも設定します。

ところで、なんで、「C」じゃなくて「D」にしているのかというと、名前の定義。

CとRが予約語として設定されているんで、使えないんです。

あと、セル番地のような「AA1」などの名前も使えません。

なので、人に説明する時には、AとBまでにしておくとビックリしなくてすみます。

次に、A2にダミーデータをいれておきましょう。今回は「A」をいれておきます。

B列の入力規則のリストを設定していきます。

B2だけを範囲選択して、データタブの「データの入力規則」をクリックします。

データの入力ダイアログボックスが表示されます。

 

入力値の種類を「リスト」にして、元の値には、

=INDIRECT($A2)

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

INDIRECT関数は、引数の文字自体を使うことができる関数です。

A2には、「A」が入力されていますので、Aと名前の定義した範囲を元の値としてつかうことができます。

また、引数の$A2は、このあと、フィルハンドルをつかって、セルをコピーするので、絶対参照にしてしまうと、常にA2を参照してしまうので、複合参照にしておく必要があります。

OKボタンをクリックします。

セルB2の設定をフィルハンドルでB5までコピーしたら、確認してみましょう。

 

B列の▼をクリックして、ジャンルに該当するアイテムしかリストに表示されていません。

今回のように、INDIRECT関数と名前の定義を組み合わせて入力規則のリストを設定すると、2段階式の入力規則のリストをつくることができます。

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。INDIRECT関数でシート名を見出しにした別シートの表に集計。だけど、日付だとエラーになるのでどうしたらいいの？【Aggregate】

Excel。INDIRECT関数でシート名を見出しにした別シートの表に集計。だけど、日付だとエラーになるのでどうしたらいいの？

＜SUM+INDIRECT関数&TEXT関数＞

各月ごとに店舗集計されたシートがあります。

それぞれ、シート名は、1月・2月・3月としてあります。

そして、集計するシートが、売上集計シートです。

この売上集計シートのB2には、1月の合算値を算出して表示したいわけです。

シートごとに合計値を算出して、セル参照でもいいのですが、どのみちセル参照の作業が必要ならば、セル参照も合計値を求めることも、まとめて出来れば作業効率がいいわけですね。
しかも、その式をオートフィルでコピーできれば、さらに便利ですね。

通常ならば、売上集計のB2の数式は次のように作るはずです。

=SUM('1月'!B2:B6)

シート名の「1月」が入っているのはいいのですが、この数式をオートフィルを使って数式をコピーしても、シート名は勝手に、2月・3月と変わりませんから、当然おかしな数値が計上されてしまいます。

このぐらいのデータなら、根性をいれればどうにかできないこともありませんが、たいへんです。

そこで、オートフィルを使って数式をコピーするには、どうしたらいいのか、考えると、自動的に、引数内の1月が2月と変わってくれればいいわけですね。

そこで、登場するのが、「INDIRECT関数」です。

この関数は、文字を直接使用することができるという、知っていると、とても便利な関数です。

準備としては、A2:A4にシート名と同じ文字を入力しておきます。

では、INDIRECT関数を使った数式を作って確認してみましょう。

B2の数式は、
=SUM(INDIRECT(A2&"!b2:b6"))

合計値を算出したいので、SUM関数を使います。
引数内のINDIRECT(A2&"!b2:b6")は、A2は1月という文字だとイメージします。

「1月」という文字に「!b2:b6」を結合しますので、「&」を使って結合させます。

あとは、オートフィルを使って数式をコピーすれば、完成です。

このINDIRECT関数は便利なのですが、シート名が次のようになると、工夫が必要になります。
シート名の「1月」を「2020年1月」と変更します。

A2も「2020年1月」と変更してみると、「#REF!」というエラーになります。

シート名もセルも2020年1月なのに、エラーになってしまったのでしょうか？
その原因は、INDIRECT関数は『文字』を参照します。シート名は2020年1月という文字なのですが、A2は、2020/1/1と日付でExcelは認識しています。

つまり、A2は文字ではなくて日付になってしまったので、エラーになったわけです。

【TEXT関数を使って日付を文字に】

日付を文字列に変更するといいわけですね。このような場合に登場する関数が「TEXT関数」です。
TEXT関数は、表示形式を設定できる関数です。

では、数式を次のように修正することで算出することができます。

=SUM(INDIRECT(TEXT(A2,"yyyy年m月")&"!b2:b6"))
このように数式を修正することで、シート名が日付でも対応することができます。

INDIRECT関数は便利なのですが、ちょっと注意が必要なケースもあります。

Excel。入力規則のリストのアイテム数が多いのでカテゴリー分けしたリストにするには？【Input rule】

Excel。入力規則のリストのアイテム数が多いのでカテゴリー分けしたリストにするには？

＜入力規則リスト＆名前の定義＆INDIRECT関数＞

入力を簡単にする、あるいは、入力ミスを抑制するなどよく使用する機能に、
入力規則のリストというのがあります。

VLOOKUPとペアで使うこともありますよね。

しかし、この入力規則のリストですが、件数が多くなってしまうと、
当然リストが大きくなってしまって、
使い勝手が悪くなってしまう欠点があります。

例えば次のような、都道府県を選択するような場合ですね。

47都道府県ありますから、その中から選ぶぐらいなら、
入力したほうが早いですよね。

ただ、都道府県のように、知っているものならば、
まだいいのですが、商品の型番だと、
入力すること自体が難しいことも想定されます。

そこで、カテゴリーを一度選んだら、
そのカテゴリーに所属しているアイテムだけがリストで表示してくれるようになると、
これらの問題から少しは解放されるわけですね。

作りたいのは、次のようなものです。

最初は、地域というカテゴリーを選びます。例えば「甲信越」を選びます。

次に、都道府県を選ぶと、甲信越に所属している、
都道府県のみがリストに表示されるようになっています。

カテゴリー式入力リストというか、2段式入力リストという感じでしょうか。

こうすることで、長いリストであっても、
細分化されるので、使い勝手が改善されます。

では、どのようにしているのでしょうか？

【範囲に名前の定義を設定】

甲信越を地域で選択したら、山梨・長野・新潟がリストに表示したいわけですね。

東北を地域で選択したら、
青森・秋田・岩手・山形・宮城・福島とリストに表示したいわけです。

なので、それぞれの地域（カテゴリー）に該当する都道府県に
名前を定義してあげる必要があります。

それぞれの地域に該当するものに名前を定義しておきます。

名前の定義は、一番簡単な方法で設定するには、
範囲選択して、名前ボックスに、名前を入力すれば設定することが出来ますね。

【入力規則のリストは、INDIRCT関数を使う】

そして、入力規則のリストを設定していくのですが、
A2をセル参照させても、連動することが出来ません。一応確認しておきましょう。

B2をクリックして、データタブの入力規則をクリックして、
データの入力規則ダイアログボックスを表示しましょう。

入力値の種類を「リスト」にして、元の値を、
A2に設定してOKボタンをクリックしましょう。

このように、先ほど設定している、名前の定義と連動していません。

そこで連動するために登場するのが、INDIRECT関数です。

入力規則のリスト+INDIRECT関数の組み合わせで解決することができます。

では、先ほど設定した、入力規則を、一度クリアしておきましょう。

先ほどの入力規則のリストにある、元の値には、
=INDIRECT($A$2)
と入力します。
そして、OKボタンをクリックしましょう。それでは、確認してみましょう。

このように、A2の地域名に合わせた、
都道府県のみがリストに表示されているのが確認できますね。

入力規則のリストを使いたいけど、リストに含めたい項目が多い場合は、
2段階式リストみたいにすることで、利便性を向上することができますよ。

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にはこの関数がありませんので、使うことが出来ません…。

Excel。INDIRECT。合計対象が増えても範囲選択が自動対応する方法

Excel。合計対象が増えても範囲選択が自動対応する方法

＜SUM+INDIRECT+COUNT関数＞

合計を算出する値が上にある。
そして、データが増減しても自動的に計算対象範囲が、
その増減に合わせて変更させるような計算式を作りたいんだけど、
どうやったらいいの？というご質問をいただきました。

このような場合、テーブルにしてあげると意外と簡単に、作ることができるのですが、
今回は、テーブルにしたくないということでして…。

そういうことになりますと、関数でどうにか作ってみましょうということで、
早速作っていくことにしましょう。

下記のような表があります。

B2に合計値が算出できる式を作りたいわけですが、
その範囲はデータがあるところまでを自動的に判断させたいというのが今回も目的です。

そこで、先にB2に入る数式をご紹介しておきます。

=SUM(C5:INDIRECT("C" & COUNT(C:C)+4))

このように、Excelの経験値が少ない方だと、お手上げってなってしまうかと思われます。

ですから、本来は、一つずつ作業結果のセルを作ってあげるほうがいいと思います。

それを踏まえたうえで、この数式の説明をしていきます。

まず出だしのSUM関数。これは、おなじみなので、割愛。

それで、範囲がC5から。データのあるセルの最後までということになります。

そこが、INDIRECT関数以降にあたります。

このINDIRECT関数はその結果そのものを、数値や文字として使うことができる関数です。

このINDIRECT関数の中を確認してみると、
”C”&　これは、Cという文字に次の文字を接続させるということになります。

では、接続されるものはというと、それがCOUNT関数の登場となるわけです。

COUNT関数は、数値のあるセルを数えます。
COUNT(C:C)とは、C列全体にある数値の数ということになります。

今回は、10が算出されます。

それでCと10を結合するとC10になるのですが、これですと、合計範囲が、
C5:C10となってしまいます。

そこで、
COUNT(C:C)+4とプラス4をする必要があります。

これで、C14という文字が作れました。

これで、=SUM(C5:C14)という数式が作成できたことになります。

よって、合計値は640を算出できたわけです。

INDIRECT関数は何かと重宝しますので、覚えておくといい関数の一つですね。
では、データを1件追加してみましょう。

自動的に合計値が変わりましたね。

ちなみに、現場で煩雑な数式がある場合には、【数式の検証】を行うといいかもしれませんね。
ためしにやってみましょう。

B2をクリックしておいて、数式タブの【数式の検証】をクリックしてみると、

検証ボタンをクリックすると、まず下線がある箇所。COUNT(C:C)を確認します。

次に、11+4を確認しますので、検証ボタンをクリックします。

そして、次のステップを確認していきます。

この手順でどんどん検証を進めていきましょう。

すると、最後に検証結果、すなわち算出されるものが確認できましたね。

なお、全くひっくり返してしまうのですが…
テーブルにしてしまうと、このような関数は全く不要になります。

テーブルには、計算表というテーブル名が設定されています。

テーブルにすれば、構造化参照になるため、SUM関数だけでこの問題は解決できるのです。

ということで、ネストを繰り返す関数で算出するのもいいですが、
様々なものを使ってあげるともっと、簡単に算出できることもありますので、
あまり、関数にはこだわらないというのもいいのかもしれませんね。