[[20150810112011]]　『一番右端のシートを参照する関数』（たけさん）　ページの最後に飛ぶ

『一番右端のシートを参照する関数』（たけさん）

いつもありがとうございます

VlookUp関数ですが
=IFERROR(VLOOKUP(K2,Sheet1!$E$3:$F$204,2,0),"")の
『sheet1』の部分を『いちばん右端のシートを参照する』という
ように関数で参照することができますでしょうか？？

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

[[20050905144450]]　『シートの一覧表を作るには』（FAS)

 こういったものを参考にしてあれこれ組み合わせると以下のようなものができました。

 まず名前定義

 名前をshName 参照範囲を =GET.WORKBOOK(1)&T(NOW())

 で、

=IFERROR(VLOOKUP(K2,INDIRECT(MID(INDEX(shName,SHEETS()),FIND("]",INDEX(shName,SHEETS()))+1,31)&"!$E$3:$F$204"),2,0),"")

（β） 2015/08/10(月) 12:14

FASさん
早速の回答、ありがとうございます
反映してみたのですが、本来、数値が入る場所も空白になってしまします

お忙しいところ申し訳ありませんが、ご確認お願いします。
_(_^_)_
（たけさん） 2015/08/10(月) 13:23

こんにちは

上の回答は、βさんからですよ。

ユーザー定義関数ではダメですか？

範囲をセル範囲の選択で指定するのか、文字列でするのか・・・・
どちらにしても使い勝手は良くないと思います。

Function VLOOKUP_L_No( _

            検査値 As Range, _
            右からのシート番号 As Long, _
            範囲 As String, 列番号 As Long, _
            検査方法 As Long _
        ) As Variant       
    Application.Volatile
    VLOOKUP_L_No = Application.WorksheetFunction.VLookup( _
        検査値, _
        Worksheets(ThisWorkbook.Worksheets.Count - 右からのシート番号 + 1).Range(範囲), _
        列番号, _
        検査方法 _
        )
End Function

範囲は文字列で指定して、右からのシートの順番も指定するようにする。

=IFERROR(VLOOKUP_L_No(K2,1,"$E$3:$F$204",2,0),"")

（ウッシ） 2015/08/10(月) 13:51

βさん、申し訳ありませんでした

ウッシさん、ご指摘、ありがとうございました

こちらの質問は、VBAの質問掲示板ですか??
マクロ以外で関数のみで、解決できる方法はないのでしょうか。。。??
何度もスイマセン。。。

（たけさん） 2015/08/10(月) 15:02

こんにちは

βさんのレスの通り、条件が揃えば関数でも出来ます。

名前定義を作成するとか、関数の入ったブック以外にブックを開いていないとか、
アドインを使用しているいないとか、色々前提があります。

（ウッシ） 2015/08/10(月) 15:05

 SHEETS関数は2013からのような…気がします
 ってことで、βさんの回答に僭越ながら補足です

 まず名前定義

 名前をshName 参照範囲を =GET.WORKBOOK(1)&T(NOW())
 名前をshCount 参照範囲を =GET.WORKBOOK(4)

 で、

=IFERROR(VLOOKUP(K2,INDIRECT(MID(INDEX(shName,shCount),FIND("]",INDEX(shName,shCount))+1,31)&"!$E$3:$F$204"),2,0),"")

 注：MIDからはじまる右端シート名の表示までしか確認してませんので、VLOOKUP式は未確認です
（さいき） 2015/08/10(月) 15:06

 >>SHEETS関数は2013からのような…気がします

 それは気が付きませんで失礼しました。
 さいきさん、フォローありがとうございます。

（β） 2015/08/10(月) 15:14

さいきさん

[名前ボックス]で名前定義
なるほど。。。
ここから勉強します

=IFERROR(VLOOKUP(K2,INDIRECT(MID(INDEX(shName,shCount),FIND("]",INDEX(shName,shCount))+1,31)&"!$E$3:$F$204"),2,0),"")
の式で、無事にできました!!!ありがとうございました

βさん
ウッシさん
ありがとうございました!!
（たけさん） 2015/08/10(月) 16:05

 皆さんの知恵で解決されたようで何よりです。

 別の観点からコメントします。
 質問者さんからコメント頂く必要はありません。

 ユーザー定義とするなら、一番右と割り切って、こんな風に書くのも手でしょう。

 Function VLOOKUP2( _
             検査値 As Range, _
             範囲 As Range, 列番号 As Long, _
             検査方法 As Long _
          ) As Variant
     Application.Volatile
     VLOOKUP2 = Application.WorksheetFunction.VLookup( _
         検査値, _
         Worksheets(ThisWorkbook.Worksheets.Count).Range(範囲.Address), _
         列番号, _
         検査方法 _
     )
 End Function
 とすれば、

 =IFERROR(VLOOKUP2(K2,$E$3:$F$204,2,0),"")
 と書けるのではないでしょうか。(名前が似過ぎていて逆に誤認性で問題かも知れないが）

 ■感想
 知らないというユーザー定義関数を使うのも、
 > 名前をshName 参照範囲を =GET.WORKBOOK(1)&T(NOW())
 > 名前をshCount 参照範囲を =GET.WORKBOOK(4)
 のような呪文に従うのも、同じようなものじゃないでしょうか。

 しかも、
 > =IFERROR(VLOOKUP(K2,INDIRECT(MID(INDEX(shName,shCount),FIND("]",INDEX(shName,shCount))+1,31)&"!$E$3:$F$204"),2,0),"") 
 は、作業事が隠蔽されておらず、 美しくないのでは？

 質問者さんには不評のようですが（翻意を考えているのではありません。ご自由に）、
 一般関数のみで、という制限を少し取っ払ったほうが、
 自然なものになるんじゃないでしょうか。

（γ） 2015/08/10(月) 21:34

 ウッシさんのコードを使わせていただきました。
 書き漏らして、どうもすみませんでした。
 基本的にはウッシさんのお考えに賛成です。

（以下、余談のつけたし。）

 > 一般関数のみで、という制限を少し取っ払ったほうが、
 > 自然なものになるんじゃないでしょうか
 と書きました。

 よく、譬えに出されるけれど、
 鶴亀算には苦労しても、
 連立方程式を学ぶと見通しがよくなって、
 頭を捻らなくても自然に回答を得ることができるというのに、
 少し似ている面があるような気がする。

（γ） 2015/08/11(火) 21:25

コメント返信：

[ 一覧(最新更新順) ]

[[20150810112011]] 『一番右端のシートを参照する関数』（たけさん） ページの最後に飛ぶ

[[20150810112011]]　『一番右端のシートを参照する関数』（たけさん）　ページの最後に飛ぶ