EXCEL関数について

質問

INDEX関数とMATCH関数を組合せて値を返したいのですが、

　　　A.xls
1 2 3 4 5 6
A a 1
B 4
C 3
D
E

B.xls
1 2 3 4 5 6
A n
B m
C o
D
E

【B.xls】のn、m、o位置に【A.xls】のA3、B3、C3の値をそれぞれ返したいのですが、A1のaはA1～C1にかけてセルの結合を行っております。A3からOFFSET関数を利用してB3、C3を返そうとしてもVALUEが出ますし、何が有効なのか分かりません。説明不足でしたら申し訳ございません。宜しくお願いします。

回答No.3

guchi_yama · ベストアンサー

すいません。
先ほどのsheet2!の訂正は、私の間違いでした。
ほんと、すいません。

あと、さっきもちょっとやってみてたんですが、
A1～A3までの結合させた名前の前(B列)に、
白のフォントでB1=sheet2!A1、B2=sheet2!A1、B3=sheet2!A1
と書き込むか、B列が必要であれば、1列追加して、
上記式を書き込めば、それぞれにB.xls C1のｎ値と同様、
オートフィルするだけで求められるんじゃないですか？
しかも、offset()関数じゃないから、
再計算せずにリンクもチャントするし...。
ちょっと安易な考えですが...。
と、いうことで色々ご迷惑をお掛けしてすいませんでした。

お礼

色々相談に乗って頂き、本当にありがとうございました。
ご回答を頂いたとおりオートフィルの利用を行うことで解決いたしました！柔軟な対応方法が本当に助かりました。説明下手でご迷惑をおかけして申し訳ございませんでした。今後とも宜しくお願い致します。

回答No.2

guchi_yama

補足ありがとうございます。
最初の質問では、私でも出来そうかなぁ
なんて思ってたんですが、
補足を見た限りでは、到底出来そうにありません；；
しかし、色々と調べてみたのですが、
OFFSET関数は可変関数といって、何らかの入力がなされる度に
再計算される関数だそうです。
なので、OFFSET()を使っての別ブックへのリンクは、
リンク元のブックを開いていないと、再計算が出来ず、
エラーが出るそうです。
他にも
"NOW" "AREAS" "CELL" "COLUMNS" "INDIRECT"
"OFFSET" "ROWS" "RAND" "TODAY"
といった関数が可変関数だそうですが、
バージョンによっては、もっと少ないそうです。
私も試しにやってみたのですが、
エラーが出るものと、出ないものがありました。
(因みにOFFSET()はエラーでした。)

なので、折角補足頂いてなんですが、
私では手に負えません。
質問番号も大分後ろの方に廻ってしまったので、
補足部分をコピーして、再度書き込みされることをお勧めします。
(タイトル等、全く同じにすると、自動削除がかかるので気をつけて下さい。)
補足のような書き込みであれば、Excelに詳しい方には分かりやすくて
とてもいいと思います。
因みに、
=INDEX('[A.xls]sheet1'!$A:$A,MATCH(sheet2!'A1,[A.xls]sheet1'!$A:$A,0))
の真ん中付近
　「～MATCH(sheet2!'A1～」　→　「～MATCH(sheet1!A1～」
シート名(sheet1)の間違いと、
！の後ろのエクステンションが不要です。(以下の式も同様です)
色々書きましたが、
何のお役にも立てず申し訳ありませんでした。
しかし、私も色々と勉強になりました。
ありがとうございました。

回答No.1

guchi_yama

こんにちは。
私は、いつも教えて頂く側の立場で、
たまに、質問を閲覧していて分かりそうな問題を
勉強がてらに解いてみたりしています。
今回、hama1980さんの質問を読んでいて、
いくつか分かりにくい部分があったので、
出来ましたら、補足願えますでしょうか？
２つのブックをリンクさせて回答を得たいものと思われますが、
まず、

>A1のaはA1～C1にかけてセルの結合を行っております。

の、A1とは、aという言葉が出てくるので
辛うじて「A.xls」のブックだと分かりますが、
それ以降の

>A3からOFFSET関数を利用して…
>B3、C3を返そうとしてもVALUEが出ますし、…

の、A3、B3,C3とは、どちらのブックを指すのでしょうか？
縦横の列と行が逆さになっていたり、
(実際の答えを見たときに行と列が逆だと、頂いた回答と自分が
欲しい意味合いとが大きく違う場合があるので、
出来れば修正した方が良い。
たとえば、A3とは、実際のExcel上でのA3なのか縦横が逆の場合の
A3なのか？など。)
Web上で質問を見たときの列のズレは、(出来れば揃えた方が良いですが...。)
大体の感覚で分かるのですが、
肝心のセルを指す位置が不明確だと意味が分からず、
問題自体に取り掛かることさえ出来ません。
あと、
企業的書類の場合は情報漏えいの無い様に
数字やアルファベットに置き換えて(質問の表で使っているように)
もらって構わないのですが、
書かれているセルをもっと細かく、実際に近い形で
表示してもらえると質問に回答下さる方も増えて、
より良くより多い回答にめぐり合えると思います。
私も極力実際の言葉やセル番号に合わせて質問し、
回答をコピペするだけで良いようにしていますよ＾＾

補足

ご指摘ありがとうございます。
本当ですね・・・。例で挙げさせて頂いた表自体でさえ、かなり無茶苦茶なものになってました。申し訳ございません。

　　　　　　　　　　A.xls
　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ
１　鈴木　　　　アイス　１個
２　　　　　　　　レモン　４個
３　　　　　　　　すいか　３個
４
５　山田

　　　　　　　　　　B.xls(sheet1)
　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ
１　　　　鈴木　　ｎ
２　　　　　　　　　ｍ
３　　　　　　　　　o
４　　　　山田　　●
５　　　　　　　　　●

　　　　　　　　　　B.xls(sheet2)
　　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ
１　鈴木
２　山田
３　佐藤
４　上田

というファイルがそれぞれあり、B.xls(sheet1)のn,m,oにA.xlsのD1,D2,D3の値をそれぞれ返したいのです。但しA.xlsの鈴木は、A1,A2,A3にかけてセルの結合を行っております。何故INDEX関数とMATCH関数を利用したいかというと、B.xls(sheet2)は名簿として利用しているのですが、名前は入社により増加、退社により削除、または変更される可能性があるためです。
その為、B.xls(sheet1)のB列にある名前表示には、
=INDEX('[A.xls]sheet1'!$A:$A,MATCH(sheet2!'A1,[A.xls]sheet1'!$A:$A,0))としています。

B.xls(sheet1)のｎはA.xlsのアイスの値を返す為、
=INDEX('[A.xls]sheet1'!$D:$D,MATCH(sheet2!'A1,'[A.xls]sheet1'!$A:$A,0))

を入れています。

B.xls(sheet1)のｍ,oには、A.xlsのレモンの値を返す為、
=OFFSET(INDEX('[A.xls]sheet1'!$D:$D,MATCH(sheet2!'A1,'[A.xls]sheet1'!$A:$A,0))),1または2,0,1,1)
を使ってみました。

A.xlsとB.xlsを同時に開けばB.xls(sheet1)のm,oも値を返す事が出来るのですが、A.xlsを開かなかった場合、B.xls(sheet1)のm.oは『VALUE』になります。B.xlsを開くときに、A.xlsを開かずに値を求めたいのですが、何か改善策、もしくは最適な方法があればご教授下さいませ。説明下手な為、不足点・不明点あればおっしゃって下さい。補足させて頂きます。申し訳ございません。