vlookupではできない、範囲指定のカスタマイズ関数

Function vlookups(val As Range, min As Range, max As Range, col As Range) As String
Dim cl As Range
For Each cl In min
    If val.Value >= cl.Value And val.Value <= cl.Worksheet.Cells(cl.Row, max.Column) Then
        vlookups = cl.Worksheet.Cells(cl.Row, col.Column)
        Exit For
    End If
Next
End Function

使い方： =vlookups(E4,$A$1:$A$4,$B$1:$B$4,$C$1:$C$4) 結果：

令和数式を置換してくれる自作関数

ポイントは正規表現のReplaceで、パターン化して一致されたセルの番地を更にReplaceに使ったこと。「$1」を使って一個目のマッチングを引用すること。

Option Explicit
Public Const REIWA = "IF(_CELL_>=DATE(2019,5,1),""令和""&IF(YEAR(_CELL_)-2018=1,""元"",YEAR(_CELL_)-2018)&""年""&MONTH(_CELL_)&""月""&DAY(_CELL_)&""日"",TEXT(_CELL_,""ggge年m月d日""))"
Function reiwa_switch(strFormual, strCellAddr As String) As String

Dim reg As Object
Set reg = CreateObject("VBScript.RegExp")
With reg
    .Pattern = "TEXT\((" & strCellAddr & "),""ggge年m月d日""\)"
    .IgnoreCase = False
    .Global = True
End With
reiwa_switch = reg.Replace(strFormual, Replace(REIWA, "_CELL_", "$1"))
End Function


=reiwa_switch(A1,A2)→関数


TEXT(data!FU3,"ggge年m月d日")→第一引数（A1）
data!FU3→第二引数（A2）
IF(data!FU3>=DATE(2019,5,1),"令和"&IF(YEAR(data!FU3)-2018=1,"元",YEAR(data!FU3)-2018)&"年"&MONTH(data!FU3)&"月"&DAY(data!FU3)&"日",TEXT(data!FU3,"ggge年m月d日"))→結果


PHONETIC関数が効かない時

PHONETICという漢字をカタカナに変換してくれる関数があります。しかし何故か変換せれず漢字のまま出力されてしまう時があります。
その時は、マクロのチカラを借りる。
例えば漢字の列がA列の場合：

Range("A:A").SetPhonetic

実行後、隣の列で「=PHONETIC("A1")」で数式を入れると変換されます

エクセル数式で自分自身のセルを参照する方法


=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())), 0, 1)

該当セルの右隣りのセルを返す

R1C1参照形式ならもっと簡単

=OFFSET(RC,0,1)
または
=RC[1]


R1C1参照形式に変更するには

条件書式の条件指定で他のセルの値を参照するときにとても便利
例えば累計計算時、値がなければ累計を表示したくないときは、条件書式で下記のような設定して、条件に満たす場合、文字色を白くすることで見えなくするなど。

INDEXとMATCHとOFFSETの数式で実現する一覧カード化

複数セルで構成された「名前」のなかの個別セルを参照する方法
例えば：A1:G1に名前をつけて「レコード」と呼びます。
そうすると、数式でA1を参照するには
=INDEX(レコード,0,1)で「A1」を参照できます。
同様B1は
=INDEX(レコード,0,2)
それでID列をMATCHして、行番号を返す。
※Vlookupは値しか返せない、MATCHはrow番号を返す
更にOFFSETを使えば、例えば1ページ４レコードのページ分け
ビューを数式だけで作成出来ます。ページ送り戻りもこの方法で
実現できる、工夫次第、vlookupも同じ効果で実現できると思う

=INDEX(OFFSET(レコード,MATCH($O$5,$A:$A)-1,0),0,4)

サンプルダウンロード
ここからダウンロード

INDIRECTって便利だね

書式 ：INDIRECT(参照文字列 , 参照形式)
機能 ：指定される文字列への参照を返します
解説 ：参照するセル範囲を変更します。文章で書くとわかりにくいですが、実はすごく便利な関数です

引用元：Office TANAKA