新生活を充実させるための「こだわり」を取材!!

こんにちは。
皆様お助け下さい!

画像をご覧ください。
E2セルに以下の式を入力しています。
=INDEX(A1:A7,MATCH(C2:C5,A1:A7,0),0)

以前のバージョンののExcelでは、A列とマッチするセルがあれば抽出してこられたのですが(画像の例ですと「H」がE2セルに返ります)、現在のExcelのバージョンでは、画像のように下方へ自動的に式が入力されて、E2に回答が表示されなくなりました。

スピル機能だと思うのですが、これを解除する方法を調べると、先頭に@を入れる方法があったので試してみたのですが、結果エラーになるだけでした。
スピル機能を解除する方法はないと書かれているページもありましが、本当にないものでしょうか?

また以前と同じようにE2セルに回答を出すにはどうしたらよいでしょうか?

詳しい方どうぞ回答よろしくお願い致します!

「ExcelのINDEXとMATCH関数で」の質問画像

質問者からの補足コメント

  • 関数に詳しい方、スピル機能をご存じの方回答をお願い致します。

      補足日時:2020/11/11 15:33
教えて!goo グレード

A 回答 (8件)

こんにちは



>以前のバージョンののExcelでは、A列とマッチするセルがあれば抽出してこられたのですが
信じられません!!

スピル機能のないエクセルを用いていますが、そのようなことはできないですね。
通常の計算で、
 =MATCH(C2:C5,A1:A7,0)
とした場合、第一引数はC2:C5の代表値としてC2が採用されて計算されるので、#N/Aが返されます。

仮に、これを配列化するために
 =INDEX(MATCH(C2:C5,A1:A7,0),)
としても、計算結果は {#N/A, #N/A, 7, #N/A} となるので、この配列から有効な最小値を取得しない限り、結果は#N/Aとなります。
配列から直接「有効な最小値」を抽出しようとしても、MINやSMALL関数では、範囲にエラー値を含むとエラーを返してしまうのでうまくいかないはずです。

配列数式を用いて
 SMALL(IF(COUNTIF(C2:C5,A1:A7)>0,ROW(A1:A7),10),1)
のような方法で計算することでindex値の7を得る手法は、旧いバージョンでよくみられた方法です。
(ですので、ご質問文の方法で抽出できるたとはとても思えません)
上記の他にも、近年のエクセルなら、AGGREGATE関数を用いることで、配列数式化しなくても配列計算をさせることは可能です。
(SUMPRODUCT関数にも似たような効果を期待できます)

新しいバージョンをお使いなのであれば、FILTER関数が使えると思いますので(私の環境では使えませんが)、そちらを利用して抽出する方法をとれば、はるかに簡単に抽出できるものと思います。
https://support.microsoft.com/ja-jp/office/filte …
    • good
    • 2
この回答へのお礼

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

確かにそうですね。。。
以前の式と何か勘違いしてる気がしてきました。

もう一度見直してみます。
AGGREGATE関数は知らなかったので調べてみます!

ありがとうございました!

お礼日時:2020/11/11 17:12

No5の方へ


>e2には、何が出て欲しいんですか?
「H」を表示したいと質問に書かれています。

質問者様へ
こちらでexcel2007で確認しても、同様にE2には#N/Aが表示されます。
スピル機能はexcel2007ではサポートしていないので、スピル機能が原因とは思えません。
うまくいっていたときのexcelのバージョンはいくつでしょうか。
    • good
    • 1
この回答へのお礼

ありがとうございました!

お礼日時:2020/11/11 17:14

エラー処理


=IFERROR(・・・
を入れるのはだめですか?

画像を見る限りA1:A7に存在しない文字Aや空白を検出しています。
で、
=IFERROR(INDEX(A1:A7,MATCH(C2:C5,A1:A7,0),0),"")
というようなものでは解決にならないですか?
    • good
    • 0
この回答へのお礼

ありがとうございました!

お礼日時:2020/11/11 17:17

スピル機能ってのが新しく出来たんですね。


知らなかった。

で、e2には、何が出て欲しいんですか?
    • good
    • 0

>E4にはちゃんと答えが返っているので、これがE2に返ればいいだけなんですけどね。

。。
C2のAはA列にないと思いますが、いかがでしょうか。
私が解決案を回答できるわけではありませんが
excelのうまくいってるバージョンとうまくいってないバージョンを提示されると良い回答が得られるかもしれません。
    • good
    • 0
この回答へのお礼

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

C2のAは、A列になくて良いのです。
A列とC列を比較して、合致するものを抽出するというものなので。

お礼日時:2020/11/11 15:24

やっぱり、matchの検査値は、配列じゃなくセル単位だと思うんだけど。


c2にしてみたら?
    • good
    • 0
この回答へのお礼

ありがとうございます!

INDEXで配列になるのでmatchはこれで正常に動作します。

c2のセルだけを選択した場合は求めるものが出ません。
そのため配列にしています。

お礼日時:2020/11/11 15:26

match関数の方はちゃんと取れてる?

    • good
    • 0
この回答へのお礼

はい問題ありません。
INDEXと組み合わせて配列になってますので、これでちゃんと値は返ってました。

お礼日時:2020/11/11 14:46

第三引数をゼロから1にしてもダメかな?

    • good
    • 0
この回答へのお礼

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

はい、同じなんです。。。

E4にはちゃんと答えが返っているので、これがE2に返ればいいだけなんですけどね。。。

お礼日時:2020/11/11 14:31

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

教えて!goo グレード

人気Q&Aランキング