[[20210223154212]]　『ピボットテーブルを使用した連動するドロップリス』（ゆき）　ページの最後に飛ぶ

『ピボットテーブルを使用した連動するドロップリストの作成』（ゆき）

3段階で連動するドロップダウンリストを作成したくとある方のページを参考に作成しました。手順通りに作成すると成功するのですが、記載してある式が理解できずに応用を利かせることができません。式の詳細な解説をお願いできますでしょうか。
参考にさせていただいたページ
https://ameblo.jp/fururuju/entry-11195857179.html

式：
=INDEX(Pivo!$R:$R,MATCH(!A2,Pivo!$P:$P,0)+MATCH(!B2,INDEX(Pivo!$Q:$Q,MATCH(!A2,Pivo!$P:$P,0)):Pivo!$Q$2000,0)-1):INDEX(Pivo!$R:$R,MATCH(!A2,Pivo!$P:$P,0)+MATCH(!B2&" 集計",INDEX(Pivo!$Q:$Q,MATCH(!A2,Pivo!$P:$P,0)):Pivo!$Q$2000,0)-2)

特にMATCH(!A2の「！」の意味が分からないでいます。

Windows 10　Excel2016を使用しております。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

モーグで質問するとよいのではないですか。

（マナ） 2021/02/23(火) 19:02

 ＞特にMATCH(!A2の「！」の意味が分からないでいます。

 本人に聞かないと真相は分からないですが、
 この入力規則がどのシートでも設定できるように、と言う配慮じゃないですか？
 決まったシート1枚にだけ設定するなら、無くても問題ないです。

 それ以外は、どの範囲をリスト対象にするのか判定するために、
 Match関数を使ってシャカリキに調査しているだけです。

 ※求めるものは、
 　なし・みかん・りんご の行番号、
 　なし 集計・みかん 集計　・・・・などの行番号
 　20世紀 集計・21世紀 集計　・・・などの行番号
 など

 その行番号が分かれば、INDEX関数を活用して、どのセル範囲なのか特定できます。

 例：Z列の4行目から10行目なら、通常はダイレクトに Z4:Z10 ですが、
 　　動的に4行目から10行目の範囲にするには INDEX(Z:Z,4):INDEX(Z:Z,10) となる。

（半平太） 2021/02/23(火) 21:15

マナ様
アドバイスありがとうございます。mougのほうでも質問させていただきます。
もしご興味ありましたらご回答会お願いいたします。

半平太様
作成した方に直接聞くべきですが、記事の日付がだいぶ前であったため質問させていただきました。
回答ありがとうございます。

「！」の意味としてはやはりシート名の後につけるものという解釈になるのでしょうか。
直接シート名を指定すると連動していたものが連動しなくなってしまいます。

INDEX関数とMATCH関数の組み合わせで連動するリスト作成はよく見かけるのですが、ピボットテーブルをもとにした表から作成するものがこの方の情報しか見つけられずにいます。
何か良い方法等ありましたらアドバイスのほうお願いいたします。
（ゆき） 2021/02/25(木) 09:16

  ＞「！」の意味としてはやはりシート名の後につけるものという解釈になるのでしょうか。
  どのシートに設定しても、当該シートの当該セルの値を使用する、と言うことです。

  ＞直接シート名を指定すると連動していたものが連動しなくなってしまいます。
  そんなことはないです。
  連動したいシートが1枚なら、当該シート上で名前定義すればいいです。
  「！」を省略しても、強制的に「そのシート名！」が頭に付きます。

  いずれにしても当該記事の通りにやれば問題がないなら、それに従ったらどうですか？
  「！」だけにする意味は既に説明しました。

  ＞ピボットテーブルをもとにした表から作成するものが
  ＞この方の情報しか見つけられずにいます。
  ピボットテーブルで作った表であろうが、なかろうが、
  どの範囲をリストとして取って来るかと言う話なので、本質的に何も特別な事はないです。

 下図のリストがあって、みかんの「ハウスと温州」のリスト範囲を
 動的に取得するにはどうすればいいのか考えてみてください。

 <Pivo シート>
 行  _G_  _____H_____  ___I___
  3                           
  4       品物         種類   
  5       なし         20世紀 
  6                    21世紀 
  7                    22世紀 
  8       なし 集計           
  9       みかん       ハウス 
 10                    温州   
 11       みかん 集計         
 12       りんご       ジョナ 
 13                    ふじ   
 14       りんご 集計         
 15       総計                
 16

 (1)まず、H列の「みかん」の位置をMatch関数で調べますよね。
 (2)次に、H列の「みかん 集計」の位置も同じ様に調べ、こちらは、その行から1マイナスした行にしますよね。

 結局、A列でみかんが入力されたら、INDEX(I:I,(1)):INDEX(I:I,(2))　と言う範囲がリストになるようすればいい。でしょ？
 りんごだって同じことです、みかんのところをりんごにすれば同じ手順でリストが取得できます。 
 りんごとか、みかんとか言ったって、A2と言う1つのセルに入っているんですから、どこも変える必要がないです。

 それでも応用が出来ないなら、応用したい例を提示して質問してください。

（半平太） 2021/02/25(木) 10:12

半平太様

わかりやすく解説していただき、ありがとうございました。

「！」の使い方については、ご説明していただき理解することができました。名前の定義についても設定するシート上で自動でシート名がつくような設定、シート名を変えると自動で名前の定義も更新されることを初めて知りました。

＞直接シート名を指定すると連動していたものが連動しなくなってしまいます。
シート名を指定するときに誤って計算式を変更してしまったようです。自分のミスでした。
おっしゃる通り問題なく動きました。

＞ピボットテーブルで作った表であろうが、なかろうが、
＞どの範囲をリストとして取って来るかと言う話なので、本質的に何も特別な事はないです。
式の長さに戸惑ったこと、ピボットテーブルの表を特別なものと勝手にとらえていたことを反省しました。長い式でもそこに含まれているINDEX関数、MATCH関数の一つ一つの意味を分解しながら考えていけば意味合いを理解することができました。

応用についてはもう少し自分なりに勉強をして、行き詰った際には改めて例を提示して質問させていただきます。

ありがとうございます。

（ゆき） 2021/02/25(木) 17:55

コメント返信：

[ 一覧(最新更新順) ]

[[20210223154212]] 『ピボットテーブルを使用した連動するドロップリス』（ゆき） ページの最後に飛ぶ

[[20210223154212]]　『ピボットテーブルを使用した連動するドロップリス』（ゆき）　ページの最後に飛ぶ