vlookup関数と入力規則(?)の続きです

質問

QNo.2539383
http://oshiete1.goo.ne.jp/qa2539383.html
で質問させていただき、思ったとおりうまくできました。
しかし、教えていただいた以下の3種類の数式
=($B1>OFFSET(先頭,MATCH($A1,名前,0)-1,1))+($B1<OFFSET(先頭,MATCH($A1,名前,0)-1,2))
=($C1>OFFSET(先頭,MATCH($A1,名前,0)-1,3))+($C1<OFFSET(先頭,MATCH($A1,名前,0)-1,4))
=OR(VLOOKUP($A3,範囲,COLUMN()*2-2)<B3,VLOOKUP($A3,範囲,COLUMN()*2-1)>B3)
が、何となくという程度にしか理解できていない為、応用をきかせることができません。
締め切る前に答えていただいた方へ質問させていただくべきだったとは思うのですが、この式が持つ意味の解説・説明をしていただけないでしょうか？
また、この質問では別のシートに入力した場合に表示させる方法をお聞きしましたが、同じシートのデータがある範囲から列方向へ大きく離れた表示されないような場所へ入力したら、もっと簡単になるのでしたらその方法が知りたいと思っています。分かり易ければ何通りかの方法を知っておきたいです。
大変申し訳ありません。よろしくお願い致します。

回答No.1

ham_kamo · ベストアンサー

こんにちは、前回の質問のNo.2で回答した者です。私が回答した上２つの式に関して回答します。

まず先に、あなたが仰るようにこれが違うシートでなく、同じシートであれば、手順としてはもっと簡単になります。OFFSET()やMATCH()関数の中では、Sheet2!A1というような別シートでの参照ができないのですが、その範囲に名前をつけて、その名前を指定すれば違うシートのセル範囲を参照できるようになるので、上記のような回答になりました。

しかし、同じシート内の場合でもやっていることはあまり変わらないので、式の複雑さはあまり変わらないでしょう。

それで、式の解説ですが、基本はOFFSET()とMATCH()の組み合わせです。
OFFSET(A,y,xY,X)
という式は、Aという番地から下にy、右にx移動した場所のセルから、下にYセル、右にXセルだけ選択したエリアを指します。Y,Xを省略した場合Aで指定した領域と同じ大きさになります。ここではAには１つのセルだけを指定し、Y、Xは省略しているので、上記の式で表されるのは１セルだけです。

まず、別のシートのA1に「鉛筆」と入力したとします。これがSheet1のＡ列（上では「名前」という名前をつけてます）の何行目にあるかを取り出します。これが、
MATCH($A1,名前,0)
という式です。「名前」の範囲（Sheet1のA列）からA1の値（鉛筆）が何行目にあるか、という意味になります。「鉛筆」が１行目にある場合、１となり、２行目にあるときは２となります。

これを上のOFFSET()関数の説明のyの値に使っています。しかし、下にy行目移動する、ということは、「鉛筆」が１行目にあるなら、y=0でないといけません。あくまで基準から何行離れているか、という数値をしているので、上記のMATCH()関数から-1した値を指定しています。

ここまでくると、あとは簡単です。上のOFFSET()のxに当たる数値を1とすると、１つ右の、2とすると２つ右のセルを指し、それはそれぞれ「鉛筆」という行のB列、C列、すなわち今年度のMAX、MINなわけです。
その値を「鉛筆」の横に入力された値と比較しています。

簡略化して書くと、
=($B1>最大値)+($B1<最小値)
ということを表しているわけです。この書き方は、
=OR($B1>最大値,$B1<最小値）
と同じことで２つの条件のどちらかが成立したら、という意味になります。

２つ目の式も全く同じで、OFFSET()のxにあたる部分に3と4を指定、つまりD列とE列を指定しています。これをC1と比較しているのです。

ここまででセルは「$B1」と列は絶対参照（$つき）で行は相対参照（$なし）で書いてます。こうしておくと、B列全体を指定して、1番上の式を入力すると、2行目では式の中のB1が自動的にB2に、3行目ではB3に変わってくれます。

ちょっと長くなってしまいましたが、おわかりいただけましたでしょうか。

お礼

ちょっと分からないところをお聞きしたいのですが…
「名前」の範囲（Sheet1のA列）からA1の値（鉛筆）が何行目にあるか、という意味になります。「鉛筆」が１行目にある場合、１となり、２行目にあるときは２となります。

これを上のOFFSET()関数の説明のyの値に使っています。しかし、下にy行目移動する、ということは、「鉛筆」が１行目にあるなら、y=0でないといけません。あくまで基準から何行離れているか、という数値をしているので、上記のMATCH()関数から-1した値を指定しています。

途中まで、A1は項目名で、それがOFFSETでの「基準」となっているのだと理解していましたが、この部分を読み返してみると、そういう意味ではないのでしょうか？

補足

再度大変丁寧で詳しい説明をありがとうございました。
ひとつずつ頭の中とエクセルの画面で整理してみたいと思います。
今度はわからない箇所があったら締め切る前に質問させていただくようにしたいと思います。
申し訳ありませんでした。
また、よろしくお願いします。

回答No.2

ham_kamo

> 途中まで、A1は項目名で、それがOFFSETでの「基準」となっているのだと理解していましたが、この部分を読み返してみると、そういう意味ではないのでしょうか？

いや、そういう意味であってます。１行目は項目名なのに、例として「鉛筆」が「１行目」にある場合、と説明したため混乱させてしまったかもしれません。

「鉛筆」が３行目にあると、番地としてはA3になるわけですが、これは基準であるA1から「２つ」離れているから、MATCH()で取得した行数から-1しないといけない、ということをご説明したつもりでした。

またわからないことがある場合は補足をお願いします。

お礼

遅くなりましたが、何とか理解することができました。
ham_kamoさま、ありがとうございました。

補足

すぐに回答下さいましてありがとうございます。
もうしばらく時間をかけて、自分のものにしようと思います。
本当に感謝致します。