excel2010の条件によるセルの参照方法

質問

excel2010の特定の条件下のセルの参照の式についてご質問させて頂きます。
例えば、A1のセルに、以下のようにするように式を書くにはどのようにしたらよろしいでしょうか？

E1, E2, E3, E5, E6 に大小の数値が入っていたとします。
それらのセルの中で、最大値のセルを探し、
E1が最大値の時はX1、E2が最大値の時はX2、E3が最大値の時はX3・・・以下同。
のセルを参照する。

例えば、E1からE6のなかで、E5が最大値だったばあいは、X5のセルをA1のセルに参照されるようにしたいと考えています。

申し訳ございませんが、ご教示のほどよろしくお願いいたします。

回答No.10

kagakusuki · ベストアンサー

> =IF(COUNT('Sheet(2)'!Q8:Q12),INDEX('Sheet(2)'!H8:H12,MATCH(MAX('Sheet(2)'!Q8:Q12),'Sheet(2)'!Q8:Q12,0)),"")

　それですと、検索対象のセル範囲も、参照先のセル範囲も、どちらも飛び地にはなっておりませんし、第一、最大値を求める対象としているセル範囲と検索対象のセル範囲と参照先のセル範囲という3つのセル範囲が全く同じセル範囲となっているのですから、結局の処、単に'Sheet(2)'!H8:H12のセル範囲内における最大値を求めているだけの事になってしまいます。
　ですから、もし本当に

=IF(COUNT('Sheet(2)'!Q8:Q12),INDEX('Sheet(2)'!H8:H12,MATCH(MAX('Sheet(2)'!Q8:Q12),'Sheet(2)'!Q8:Q12,0)),"")

という関数でも、質問者様が望んでおられる結果が得られたという事であれば、

=IF(COUNT('Sheet(2)'!Q8:Q12),MAX('Sheet(2)'!Q8:Q12),"")

という、単に'Sheet(2)'!H8:H12のセル範囲内における最大値を求めるだけの関数としても良いのではないでしょうか？

>今は、シート２を挿むかたちで作表しましたが、このシート２を挿まずに、シート3から直接、シート1に結果が出るようにするにはどのようにしたら良いのでしょうか？

>シート3：比べる数値のセルの場所がばらばら。例えば、C5, F2, V9 の中で比べて、c5が最大値の時はJ6を返して、f2が最大値の時はN8を返して、V9が最大値の時はB11を返す、のような場合です。

　その御説明では、C5セル、F2セル、V9セルの3つのセルと、J6セル、N8セル、B11セルの3つのセルの全てがSheet3のセルである事になってしまい、Sheet1のセルの値は参照しないという事になりますが、それで宜しいのでしょうか？
　それと、C5セル、F2セル、V9セルの中に最大値と同じ値が入っているセルが複数存在している場合には、J6セル、N8セル、B11セルの内のどのセルの値を優先して表示させれば良いのでしょうか？
　ここでは取り敢えずの話として、C5セル、F2セル、V9セル、J6セル、N8セル、B11セルの何れのセルもSheet3のセルであるものとし、最大値と同じ値が入っているセルが複数存在している場合には、J6セル、N8セル、B11セルの順に優先して表示させるものとして、話を進める事に致します。

　その様な場合には、基本的にはIF関数を入れ子にした次の様な関数を使う事になります。

=IF(COUNT(Sheet3!$C$5,Sheet3!$F$2,Sheet3!$V$9),IF(AND(Sheet3!$C$5=MAX(Sheet3!$C$5,Sheet3!$F$2,Sheet3!$V$9),ISNUMBER(Sheet3!$C$5)),Sheet3!$J$6,IF(AND(Sheet3!$F$2=MAX(Sheet3!$C$5,Sheet3!$F$2,Sheet3!$V$9),ISNUMBER(Sheet3!$F$2)),Sheet3!$N$8,Sheet3!$B$11)),"")

　或いは、CHOOSE関数を応用した次の様な関数を使う事でも、同様の処理を行う事が出来ます。

=IF(COUNT(Sheet3!$C$5,Sheet3!$F$2,Sheet3!$V$9),CHOOSE(MAX((Sheet3!$V$9=MAX(Sheet3!$C$5,Sheet3!$F$2,Sheet3!$V$9))*ISNUMBER(Sheet3!$V$9)*1+(Sheet3!$F$2=MAX(Sheet3!$C$5,Sheet3!$F$2,Sheet3!$V$9))*ISNUMBER(Sheet3!$F$2)*2+(Sheet3!$C$5=MAX(Sheet3!$C$5,Sheet3!$F$2,Sheet3!$V$9))*ISNUMBER(Sheet3!$C$5)*3),Sheet3!$B$11,Sheet3!$N$8,Sheet3!$J$6),"")

　但し、最大値を求めるMAX関数は、SUMPRODUCT関数や配列式と比べれば、計算処理に要するコンピューターの負荷は断然軽いものの、VLOOKUP関数やMACH関数、INDEX関数等と比べると、比較的重くなりがちな関数ですので、同じ最大値を求めるだけであるのに、MAX関数を複数回使用するのは効率が悪い方法となります。
　又、MACH関数やVLOOKUP関数の様な検索を行う関数が使えないため、対象としているセルの個数が多くなればなる程、関数を長くしていかねばならなくなります。

　ですから、その様な場合には、Sheet2などの未使用の別シートのA1セルに

=IF(Sheet3!C5="","",Sheet3!C5)

A2セルに

=IF(Sheet3!F2="","",Sheet3!F2)

A3セルに

=IF(Sheet3!V9="","",Sheet3!V9)

B1セルに

=IF(Sheet3!J6="","",Sheet3!J6)

B2セルに

=IF(Sheet3!N8="","",Sheet3!N8)

B3セルに

=IF(Sheet3!B11="","",Sheet3!B11)

という関数をそれぞれ入力しておく事で、A1～A3のセル範囲にC5セル、F2セル、V9セルの値を表示させ、Sheet2のB1～B3のセル範囲にJ6セル、N8セル、B11セルの値を表示させる様にしておいた上で、

==IF(COUNT(Sheet2!A1:A3),IF(VLOOKUP(MAX(Sheet2!A1:A3),Sheet2!A1:B3,2,FALSE)="","",VLOOKUP(MAX(Sheet2!A1:A3),Sheet2!A1:B3,2,FALSE)),"")

等としておく方が良策だと思います。

お礼

私のつたないせつめいで、質問の趣旨をくみとってご回答いただきありがとうございました。

今回のご回答で以下のようにすることにしました。。
１．シート３から、直接シート１に結果を表示させるのはやめて、シート２をワンクッション置いてシート３の値を引用、整列させる。
…入れ子関数で長く煩雑なり、仕事上、他人が行う可能性のあるメンテナンスのことも考えると好ましくないため。

２．今回ご教示いただいたしきで、より簡易に予定の数字が表れました。
=IF(COUNT('Sheet(2)'!Q8:Q12),MAX('Sheet(2)'!H8:H12),"")
=IF(COUNT('Sheet(2)'!Q8:Q12),MIN('Sheet(2)'!K8:K12),"")
などなど…

なお、他の方にも少々説明しました状況についてですが、例えば、
シート１は今回作る一番新しい表で、シート２とは異なる情報、作表が必要となり、シート１を作ることとなりました。
シート２は、もともと使用していた表で、シート３から数値等を引用して、作表しており、シート３からの引用セルを、ある程度、列行ならべてシート２で整地しています。
シート３は、これは元々情報をインプットするための表ですが、表の関係上、比較、抽出対象のセル位置は、綺麗にそろっておりませんでした。

またお世話になるかもしれませんが、取り急ぎベストアンサーとさせていただきます。ありがとうございました。

回答No.11

keithin

具体的なアナタのエクセルの配置について、相変わらず説明が出来ないご様子なので、次の通りに説明します。

ヤリタイ事：
シート３のＥ１、Ｅ２、Ｅ３の一塊と、Ｅ５、Ｅ６の別の一塊を合わせた中から最大値を探して、対応するＸ１：Ｘ３、Ｘ５：Ｘ６の値を取り出したい

手順：
シート３のＥ１：Ｅ３をコピーして、シート４のＡ１にリンク貼り付ける
シート４のＡ１に
=Sheet3!E1
のような数式を作成するという意味なので、間違えないこと

同様にシート３のＥ５：Ｅ６をコピーして、シート４のＡ４にリンク貼り付ける
シート４のＡ１からＡ５までの連続した範囲に、シート３のそれぞれ所定のセルの値を参照させるという意味なので間違えないこと

シート４のＢ１からＢ３に、シート３のＸ１：Ｘ３を参照する式を
=Sheet3!X1
のような数式で並べる
シート４のＢ４、Ｂ５には
=Sheet3!X4
のような数式で並べる

シート１には
=VLOOKUP(MAX(Sheet4!A1:A5),Sheet4!A1:B5,2,FALSE)
のようにして、最大値に対応する値を計算する。

まず、実際にエクセルを動かして回答の通りに「やってみる」ことから始めて下さい。お返事を記入するのは「そのあと」です。やってもないのに返事だけ書かれても、それこそ本末転倒です。

いきなり実地にやってみるのがムズカシければ、シート４をまず回答の通りに作成してちゃんと出来ることを確認してから、本番のシートにやりかえるような練習をしてください。

＞不都合はと申しますと、シート１とシート２は別々の表でして、それぞれを変形させたり、メンテナンスするときに分かりにくくなるためです。
＞従って、シート１、シート２それぞれに、おおもとの引用部(シート３)から数字を引用したいと思いました。

いいえ。それは説明のための説明で、「事実とは違う事」をさもあるかのように騙り始めています。

シート３は既にカタチの決まったシートなので、飛び地になっているのは事実です。
でもシート２は、飛び地になっているシート３から計算をしやすいように新しく１クッション置くために作ったシートなので、自由な「飛び地になっていない姿で」新しく作成します。
今何か既にあるシート２とごっちゃにしてて、まだ飛び地が解消できないと勘違いしているのでしたら、まったく新しいシート４をもう一枚作り、そちらを使って計算するんだと理解してください。

お礼

ご回答、ありがとうございます。

しかしながら貴重なお時間をお使いいただきご回答いただいておきながら忍びないのですが、今回の質問のご回答の中で、無用な決めつけが多いように感じられました。

それらを一つ一つ挙げることはいたしませんが、ご質問させていただく際には、必ずしも完全にすべての情報を書きだした場合は、より理解が煩雑になる場合もございますので、今回のご質問も要点を抽出したものとなったことご容赦ください。

貴重なお時間、お知恵をいただきましてありがとうございました。

回答No.9

keithin

＞その表から、別の表(シート１、シート２)を作る

何も面倒なことはありません。

シート２のＡ１、Ａ２、Ａ３にシート３の飛び地１を参照する式を並べて置き、
続けてシート２のＡ４、Ａ５にシート３の飛び地２を参照する式を並べておく、
ただそれだけの事です。

また、「飛び地は飛ばさなきゃならない」というアタマが抜けてないからでしょうか、各回答者から「飛ばしてない数式」を回答されて「それで具体的にどういう目に見える不都合があるのか」も、補足されていないままです。

どうやらご相談の「例えば」で教わった数式を、ご自分の実際に当てはめることが出来なくて悉く失敗してるのが原因の様子ですが、それなら
１）今のご相談は捨てて丁寧にご自分のエクセルの本当の姿をキチンと正しく詳しく情報提供して、そのまま使える正しい数式を今度はしっかり教えてもらう
２）ご自分がサンプルで提示した通りにまずご自分でも一回作成してみて、それでどういう具合に不都合があるのか無いのかちゃんとやってみる

とかしてみてはいかがでしょう。

お礼

ご回答ありがとうございます。
私自身の知識やその他もろもろの理解力が足りないため、申し訳ございません。

>各回答者から「飛ばしてない数式」を回答されて「それで具体的にどういう目に見える不都合があるのか」も、補足されていないままです。

こちらについても申し訳ございません。
本来、飛び飛びの位置の比較、参照を考えておりまして、その見地からご質問をさせていただいた予定であったため（私の書き方が悪く混乱させてしまいました）、その説明まで頭が回りませんでした。

不都合はと申しますと、シート１とシート２は別々の表でして、それぞれを変形させたり、メンテナンスするときに分かりにくくなるためです。
従って、シート１、シート２それぞれに、おおもとの引用部(シート３)から数字を引用したいと思いました。
そのシート３は、すでに出来上がった表ですので、レイアウトを崩したくなく、引用するべきセルの位置が、綺麗にそろってはいない状態です。

回答No.8

tom04

No.5です。

補足の数式を拝見すると
Sheet2のH8～H12セル内で、Sheet2のQ8～Q12セル内最大値と一致する行を返したい！
というコトのようですので・・・
敢えてSUMPRODUCT関数は使わずに

=INDIRECT("Sheet2!H"&MATCH(MAX(Sheet2!Q:Q),Sheet2!Q:Q,0))
または
=INDEX(Sheet2!H8:H12,MATCH(MAX(Sheet2!Q8:Q12),Sheet2!Q8:Q12,0))

としてみてはどうなりますか？m(_ _)m

お礼

ご回答、ありがとうございます。

私の実際のセルにあてはめて、下記のようにしましたが、
#VALUE!と表示されてしまいました…

=INDIRECT("'Sheet(2)'!H8:H12"&MATCH(MAX('Sheet(2)'!Q8:Q12),'Sheet(2)'!Q8:Q12,0))

関数の理解が不足していてもうしわけございません…

回答No.7

kagakusuki

>この中の参照する、E列や、X列は、飛び地のセルなのです。(E4をあえて抜かしたのはそのためでした)

　失礼しました、E4が書かれていないのは、書き忘れられただけかと思いこんでおりましたが、敢えて抜かしておられたのですね。
　そうしますと確認したい事がもう一点出て来るのですが、抜かされているE4セルに入っている値が、E1, E2, E3, E5, E6における最大値と偶然にでも一致する可能性はあるのでしょうか？
　もし、その様な事は全くあり得ないという事でしたら、回答No.3の関数を少し修正した次の様な関数となります。

=IF(COUNT(E1:E3,E5,E6),INDEX(X1:X6,MATCH(MAX(E1:E6),E1:E6,0)),"")

　こちらの関数であれば、

>E1が最大値の時はX1、E2が最大値の時はX2、E3が最大値の時はX3・・・以下同。

だけではなく、例えば
「E1が最大値の時はX101、E2が最大値の時はX102、E3が最大値の時はX103・・・以下同」
等の様に、E列に対応するX列のセル範囲で、行が異なっている場合にも対応出来ます。

お礼

ご回答、ありがとうございます。
お教えいただいた式を、以下のように自分のシートにあてはめたところ、予定通りの数字が引用できました。

=IF(COUNT('Sheet(2)'!Q8:Q12),INDEX('Sheet(2)'!H8:H12,MATCH(MAX('Sheet(2)'!Q8:Q12),'Sheet(2)'!Q8:Q12,0)),"")

ベストアンサーにさせていただきますが、後学のために、もう一点ご教示いただけないでしょうか？
今は、シート２を挿むかたちで作表しましたが、このシート２を挿まずに、シート3から直接、シート1に結果が出るようにするにはどのようにしたら良いのでしょうか？

シート3：比べる数値のセルの場所がばらばら。例えば、C5, F2, V9 の中で比べて、c5が最大値の時はJ6を返して、f2が最大値の時はN8を返して、V9が最大値の時はB11を返す、のような場合です。
他力本願になってしまい申し訳ありませんが、お教えいただけないでしょうか。よろしくお願いいたします。

回答No.6

keithin

シート２にワンクッション置くなら、
シート２のＡ１：Ａ５に「飛ばさずに」検索値、シート２のＢ１：Ｂ５に欲しい結果をそれぞれシート３から参照式で並べておいて
=VLOOKUP(MAX(Sheet2!A1:A5),Sheet2!A1:B5,2,FALSE)
のようにしてしまうだけです。ワザワザややこしいことをする必要は何もありません。

「飛び地」であることに格別のコダワリのご相談ですが、具体的な配置も内容も不明のままなので、既出回答の全てでアドバイスが寄せられているように飛び地を飛ばさずに計算して何がダメなのかも、アドバイスできません。

お礼

ご回答ありがとうございます。
飛び地のセルであるのは、もともとの参照するセルが、表になっているためです。
その表から、別の表(シート１、シート２)を作るため、話が少々面倒になっている次第です。

回答No.5

tom04

こんばんは！
すでに色々回答が出ていますが、別案として

=IFERROR(INDIRECT("X"&MATCH(MAX(E:E),E:E,0)),"")

でも大丈夫だと思います。m(_ _)m

お礼

ありがとうございます。

補足

皆様、困っているときにレスポンスよくお答えくださりありがとうございました。

分かりにくく質問をした私が悪いのですが、実は、

>E1, E2, E3, E5, E6 に大小の数値が入っていたとします。
>それらのセルの中で、最大値のセルを探し、
>E1が最大値の時はX1、E2が最大値の時はX2、E3が最大値の時はX3・・・以下同。
>のセルを参照する。

この中の参照する、E列や、X列は、飛び地のセルなのです。(E4をあえて抜かしたのはそのためでした)
そこで、飛び地の引用対象セルを、別シートに配置しなおし('Sheet(2)'に)、教えていただいた式に引用式をあてはめ、以下のように、関数をSheet(1)に書いてみました。

=INDEX('Sheet(2)'!H8:H12,SUMPRODUCT((MAX('Sheet(2)'!Q8:Q12)='Sheet(2)'!Q8:Q12)*ROW('Sheet(2)'!Q8:Q12)))
※シートの状況説明
Sheet(1)：結果を表示したい　A1　セルがあり、ここに上記式を記入。
Sheet(2)：シート3の比較対象、引用対象セルが飛び地のため、このシート2のH8～H12, Q8～Q12に、シート3の数字を引用('sheet(3)'**のように)。
Sheet(3)：ここに、本来の比較対象数字や、引用対象数字がありますが、対象は飛び地のセルです。

しかし、結果表示のセルには、#REF!　が表示されてしまいました…

もうしわけありませんが、説明不足の点のご指摘を含めまして、改善方法をご教示いただけないでしょうか？(シート2を挿むとうまくいかないのであれば、比較・引用対象が飛び地セルを直接指定する方法なども)
誠にもうしわけございませんが、よろしくお願いいたします。

回答No.4

MackyNo1

質問をよく見たら「×1」ではなくX1セルのことだったのですね。

その場合は、以下のような式になります。

=INDEX(X1:X6,SUMPRODUCT((MAX(E1:E6)=E1:E6)*ROW(E1:E6)))

ただし、最大値が2つ以上ある場合は、どちらのデータを優先するかで式が異なりますが、別の式を使用する必要があります。

お礼

ありがとうございます。

回答No.3

kagakusuki

=IF(COUNT(E1:E6),VLOOKUP(MAX(E1:E6),E1:X6,20,FALSE),"")

或いは、

=IFERROR(VLOOKUP(MAX(E1:E6),E1:X6,20,FALSE),"")

=IF(COUNT(E1:E6),INDEX(X1:X6,MATCH(MAX(E1:E6),E1:E6,0)),"")

=IFERROR(INDEX(X1:X6,MATCH(MAX(E1:E6),E1:E6,0)),"")

等の様な関数になります。

お礼

ありがとうございます。

回答No.2

keithin

=INDEX(X1:X6,MATCH(MAX(E1:E6),E1:E6,0))
=VLOOKUP(MAX(E1:E6),E1:X6,20,FALSE)
などのように。

お礼

ありがとうございます。

回答No.1

MackyNo1

＞例えば、E1からE6のなかで、E5が最大値だったばあいは、X5のセルをA1のセルに参照されるようにしたいと考えています

「X5のセルをA1のセルに参照されるようにしたい」とはE5セルが最大値の時は、E5セルの値×５の値をA1に表示したいということでしょうか？

その場合は、以下の数式で求めることができます。

=SUMPRODUCT((MAX(E1:E6)=E1:E6)*ROW(E1:E6)*E1:E6)

お礼

ありがとうございます。