名前付きのセル範囲から、条件に合致するデータだけを参照したい

ユーザー定義関数を作れば、それを使った配列数式で該当データを参照できます Excelでは、独自の処理や計算を行うために、自分自身で関数を定義し、再利用することができます。このユーザー定義関数は、自分のニーズや特定の問題に合わせて、自由にカスタマイズすることができます。ユーザー定義関数の作成で必要なものは、次の4つです。 ①関数の名前：関数に適切な名前を付けます。他の組み込み関数と区別するために、一意な名前にします。 ②入力パラメータの定義：関数が受け取る引数やパラメータを定義します。関数内で使用するデータや処理のための情報を、引数で指定できるようにします。 ③処理内容：実行したい計算や操作のロジックを実装します。 ④返り値を定義：関数の実行結果として返す値を定義します。　以下のような関数をユーザー定義すると、名前付きのセル範囲から、指定列の数値と一致もしくは範囲内にある、対象列のデータをすべて取得できます。配列数式としてセル範囲にまとめて設定すると、条件に合致する指定列の文字列が、複数のセルに配列として表示されます。
Function ExtractStringsByCondition(rangeName As String, conditionColumn As Long, extractColumn As Long, condition As Variant) As Variant() 　Dim rng As Range ' 名前付きセル範囲を格納する変数　Dim result() As Variant ' 抽出された文字列を格納する配列　Dim count As Long ' 抽出された文字列の数をカウントする変数　Dim i As Long ' ループ変数　' 名前付きセル範囲を取得　Set rng = Range(rangeName) 　' 結果の配列を初期化　ReDim result(1 To rng.Rows.Count, 1 To 1) 　count = 0 　' 条件に一致する場合、指定の列の文字列を抽出　On Error Resume Next 　For i = 1 To rng.Rows.Count 　　If IsNumeric(condition) Then 　　　' 条件が数値の場合　　　If rng.Cells(i, conditionColumn).Value = condition Then 　　　　count = count + 1 　　　　result(count, 1) = rng.Cells(i, extractColumn).Value 　　　End If 　　ElseIf IsArray(condition) Then 　　　' 条件が数値の範囲の場合　　　If rng.Cells(i, conditionColumn).Value >= condition(LBound(condition)) And rng.Cells(i, conditionColumn).Value <= condition(UBound(condition)) Then 　　　　count = count + 1 　　　　result(count, 1) = rng.Cells(i, extractColumn).Value 　　　End If 　　End If 　Next i 　' エラーチェック　If Err.Number <> 0 Then 　　ExtractStringsByCondition = CVErr(xlErrValue) ' #VALUE! エラーを返す　　Exit Function 　End If 　' 抽出された文字列のみの配列をリサイズ　ReDim Preserve result(1 To count, 1 To 1) 　ExtractStringsByCondition = result ' 関数の戻り値として配列を返す End Function

①Functionプロシージャを使って、関数名と引数を定義します Functionプロシージャは、次のような構文になります。 Function 関数名([引数1 As データ型], [引数2 As データ型], ...) As 戻り値のデータ型　' 関数の処理を記述します　' ... 　' 処理結果を返す　関数名 = 結果の値 End Function 作例では、ExtractStringsByConditionという関数名と、rangeName、conditionColumn、extractColumn、conditionの4つの引数を定義しています。それぞれ、 rangeName：セル範囲名 conditionColumn：条件列番号 extractColumn：取得列番号 condition：条件値、または条件範囲（以上、以下）となっています。戻り値は配列になるので、データ型に（）を付けて定義します。
Function ExtractStringsByCondition(rangeName As String, conditionColumn As Long, extractColumn As Long, condition As Variant) As Variant()
Dim rng As Range ' 名前付きセル範囲を格納する変数 Dim result() As Variant ' 抽出された文字列を格納する配列 Dim count As Long ' 抽出された文字列の数をカウントする変数 Dim i As Long ' ループ変数	②処理で用いる変数を定義します
' 名前付きセル範囲を取得 Set rng = Range(rangeName)	③rangeName引数で指定された名前付きセル範囲を変数rngに設定します
' 結果の配列を初期化 ReDim result(1 To rng.Rows.Count, 1 To 1)	④result()配列を初期化し、抽出された文字列を格納するための準備をします配列のサイズは、rngの行数（Rows.Count）に合わせて設定されます。
count = 0 ' 条件に一致する場合、指定の列の文字列を抽出 On Error Resume Next ' エラーハンドリングを有効にし、エラーが発生しても処理を継続する	⑤count変数を初期化します count変数は抽出された文字列の数をカウントするために使用されます。 On Error Resume Nextステートメントを使用して、エラーハンドリングを有効にします。これにより、エラーが発生しても処理が中断されず、続行されます。
For i = 1 To rng.Rows.Count	⑥Forループを使用して、rngの各行を順番に処理します
If IsNumeric(condition) Then 　' 条件が数値の場合　If rng.Cells(i, conditionColumn).Value = condition Then 　　count = count + 1 　　result(count, 1) = rng.Cells(i, extractColumn).Value 　End If	⑦条件が数値の場合、rngの指定された条件列(conditionColumn)の値が条件(condition)と一致するかどうかをチェックします一致する場合は、抽出列(extractColumn)の値をresult()配列に追加し、countの値を1増やします。
⑧条件が数値の範囲の場合、rngの指定された条件列の値が条件の範囲内にあるかどうかをチェックします条件の範囲は、condition配列の最小値と最大値です。条件に合致する場合は、抽出列の値をresult()配列に追加し、countの値を1増やします。
ElseIf IsArray(condition) Then 　　' 条件が数値の範囲の場合　　If rng.Cells(i, conditionColumn).Value >= condition(LBound(condition)) And rng.Cells(i, conditionColumn).Value <= condition(UBound(condition)) Then 　　　count = count + 1 　　　result(count, 1) = rng.Cells(i, extractColumn).Value 　　End If 　End If Next i
' エラーチェック If Err.Number <> 0 Then 　ExtractStringsByCondition = CVErr(xlErrValue) ' #VALUE! エラーを返す　Exit Function End If	⑨エラーチェックを行いますもしエラーが発生している場合、CVErr(xlErrValue)を返して関数を終了します。これにより、エラーが表示されることになります。
' 抽出された文字列のみの配列をリサイズ ReDim Preserve result(1 To count, 1 To 1)	⑩抽出された文字列のみの配列の大きさを調整し、不要な要素を削除します ReDim Preserveステートメントを使用して、result()配列のサイズをcountに合わせて変更します。
ExtractStringsByCondition = result ' 関数の戻り値として配列を返す End Function	⑪最後に、result()配列を関数の戻り値として返します
	⑫以上のような関数を、標準モジュールで定義します作例のように、PERSONAL.XLSB（個人用マクロブック）の標準モジュールで定義すると、どのブックからでもユーザー定義関数を利用できるようになります。
	⑬本ページ冒頭の関数コードを貼り付けて使うこともできます
	⑭ユーザー定義関数は、「関数の挿入」から呼び出せます結果は配列で取得されるので、あらかじめ取得数以上のセル範囲を選択しておきます。
	⑮定義した関数名を選んで、「OK」をクリックします
	⑯それぞれの引数を指定します rangeNameには、セル範囲名を””で囲んで指定します。 conditionColumnには、条件値の入力されている列の番号を指定します。 extractColumnには、取得したい値の入力されている列の番号を指定します。 conditionには、条件値を指定します。 ※作例の関数では、条件値は数値のみとなります。他の条件を使いたい場合には、引数conditionのデータ型や、その取得・処理の方法などを変更する必要があります。
	⑰Ctrl＋Shift＋Enterキーで、配列数式として確定します配列外にあたるセルにはエラーや0が表示されますが、条件付き書式や表示形式で非表示にできます。
	⑱作例のユーザー定義関数では、条件を範囲で指定することもできますその場合には、condition引数を｛以上，以下｝の形で指定します。

	●セル範囲に付けた名前を使って、データを参照する方法です

	関連する他のページ
	修正内容が自動的に反映される、もうひとつの表を作りたいリンク貼り付けを使えば、自動更新される表を簡単に作れます他のブックのデータを転記して使いたい「リンク貼り付け」を使えば、セル範囲のセル参照式を簡単に設定できます指定期間の祝日の数を知りたい配列数式を使って、期間初日以降に該当し、同時に期間末尾以前に該当する祝日を数えます土日の労働時間を集計したい WEEKDAY関数を使った配列数式で行えます住所から都道府県名を取り出して表示したいあらかじめ都道府県の一覧を用意しておけば、それを使った配列数式で、住所から都道府県名を取り出して表示できます隔列のセル値を合計したい配列数式を使って合計する方法があります数字の組み合わせ出現回数を一覧表で見たい配列数式で集計することができます数式で、一意の値を抽出したい配列数式で抽出することもできます時間帯ごとの予約数を集計したい配列数式を使う方法があります直近の購入日時を知りたい日付の実体は数値（シリアル値）なので、 MAX関数を使った配列数式で調べられます日時データを使って、月別で売上を集計したい配列数式を使えば、日時データから月を照合して、該当する売上のみを合計できます複数回答のアンケートを集計したい FIND関数を使った配列数式で集計することができます重複しない個数を求めたい COUNTIF関数を使った配列数式にします年度ごとの月別売上を集計したい複数条件による配列数式を使う方法があります配列数式による評価計算結果をABC評価に変えたい CHOOSE関数を使うと、計算で得られた数値を、任意の文字に変えることができます実績を一定の目標値でまとめて評価したい配列数式を使うと、範囲内の実績セルをそれぞれ指定値と比較して、その結果をSUM関数で合計することができます源泉徴収税額表を使って、所得税を求めたい配列数式を使えば、空白の含まれる表からでも、該当値を取得することができます会員番号ごとに最新売上日時を求めたい MAX関数を使った配列数式にすれば、売上日時の中から最新のものを表示できます学級や男女別の順位を求めたい条件付きの順位を求めるときには配列数式を用います直近3回の来店日を知りたい LARGE関数を使った配列数式で求められます直近の来店日を知りたい MAX関数を使った配列数式で求められます『月』列を追加しないで、各月の売上を集計したい配列数式なら、数式による条件式を使うことができますマス計算の解答を作りたい配列数式を使うと、まとめて答を得られます素数を探し出したい配列数式を使えば、一つの数式で、素数か否かを判断できます配列数式を使って、年齢層ごとの来店数を求めたい配列数式では、条件式を乗算することによって、複数の条件を指定します重複値を無視して、伝票番号の個数が知りたい COUNTIF関数を使った配列数式で求めることができます数式を使って年代別の集計を行いたい配列数式を使うと、年齢などの数値データをグループ化して集計できます仕入伝票と売上伝票を使って、月ごとで在庫管理したい② 配列数式にすると、「月」列なしに入出庫数を求めることができます複数の検索データに合致するセル数を調べたい」配列数式を使えば、COUNTIF関数で調べられます階級ごとの度数を数式で求めたい FREQUENCY関数を使えば、配列数式で簡単に度数を求められますさらに条件を加えて、順位を求めたい配列数式での条件の追加は、追加する条件式の乗算で行えます COUNTIFS関数の使えない2007より前のExcelでも利用できる表を作りたい COUNTIFS関数と同等の、配列数式を利用する方法があります一つおきのセルの値を合計したい配列数式を使えば、特殊な条件に合致するセルの値だけを合計することができます複数回答のアンケートを集計したい FIND関数を使った配列数式で集計することができますエラー値 #N/A を表示したくない IFERROR関数を使えば、#N/Aなどの数式エラーをトラップできます一意の項目に該当する、同一行内のデータを取り出したい INDEXとMATCHの関数を組み合わせると、検索列の左側であっても、該当するデータを取り出すことができますすべての月で目標を達成した者だけを強調表示する PRODUCTとINDEXの関数を組み合わせることで、シンプルなAND条件式にできますすべての月で目標を達成した者だけにメッセージを表示する PRODUCTとINDEXの関数を組み合わせることで、シンプルなAND条件式にできます『名前』に右隣セルの『ふりがな』を登録したい簡単なマクロを作って、ふりがな登録する方法がありますブックを開いたときの新規シート自動挿入を、1日1枚に制限したい新規シートの名前に日付を加え、マクロ実行時にその存在の有無を確認するようにしますブックを開いたときに、新規シートを先頭へ挿入したい ThisWorkbookのOpenプロシージャに該当コードを記述します指定したページだけを、とびとびで印刷したいマクロを使えば、指定したページだけを連続して印刷することは可能ですどのブックからでも利用できるマクロを作りたい「個人用マクロブック」に登録すれば、どのブックからでもそのマクロを実行できるようになりますマクロ実行の対象範囲を固定したいセルやセル範囲は、 Rangeで指定できます選択中のセル範囲に含まれる空セルを、マクロ処理の対象から除外したい空セルでないことを条件に、マクロを実行するよう、 If文を追加します選択中のセル範囲に対してのマクロ実行結果を、別のセルに表示したい Offsetを使うと、対象のセルの位置を行列単位でずらすことができます選択中のセル範囲に対するマクロを作りたい For Each～Nextで、選択中のセル範囲（Selection）に対する処理を記述します出退の時刻を簡単に記録したい簡単なマクロを作れば、「出社」や「退社」をクリック（タップ）するだけで現在日時を入力できるタイムカードを用意できます『名前』に右隣セルの『ふりがな』を登録したい簡単なマクロを作って、ふりがな登録する方法がありますブックを開いたときの新規シート自動挿入を、1日1枚に制限したい新規シートの名前に日付を加え、マクロ実行時にその存在の有無を確認するようにしますブックを開いたときに、新規シートを先頭へ挿入したい ThisWorkbookのOpenプロシージャに該当コードを記述しますマイナス値をゼロに置換したい④ If...ThenステートメントによるVBAマクロを使えば、マイナス値をまとめてゼロに置換できます “縮小して表全体を表示する”のマクロを、クイックアクセスツールバーから実行したいクイックアクセスツールバーにマクロを登録すれば、マウスからでも、キーボードからでも、簡単に実行できるようになります “縮小して表全体を表示する”のマクロを使って、表の選択から一気に処理したい Range.CurrentRegion プロパティを使えば、アクティブセル領域となっている表全体をマクロで選択できます “縮小して表全体を表示する”を、ショートカットキーですばやく実行したい実行手順をマクロとして記録すれば、ショートカットキーでその一連の手順をすばやく実行できるようになります “セキュリティの警告”が表示されるんだけど・・・マクロを含むブックを開くときには、注意を促すべく、「セキュリティの警告」が表示されますマクロ有効ファイルって何？マクロを含むブックは、マクロ有効ファイル（マクロ有効ブック）として保存しなければ、マクロが失われてしまいます 100行程度の表で、5行おきに、新しい行を挿入したいあまり大きくない表でのパターン化された処理なら、一定の手順をマクロ記録すると便利ですオートシェイプをクリックするたびに、枠線の表示と非表示を切り替えたい枠線の表示と非表示の切り替えマクロを、オートシェイプに登録すれば、オートシェイプをクリックするだけでそのマクロが実行されるようになりますオートシェイプの枠線の表示と非表示を、一つのマクロで切り替えたい If～Then～Elseの構文を使えば、オートシェイプの状態に応じて、表示と非表示を一つのマクロで切り替えられますオートシェイプの枠線の表示の有無を、マクロで切り替えたい Shape.LineのVisibleプロパティの設定を変えることで、枠線を表示したり非表示にしたりすることができますマクロを右クリックで実行したい Worksheet_BeforeRightClickイベントのプロシージャにマクロを記述すると、指定シートを右クリックしたときにそのマクロが実行されますブックを閉じるときに、マクロを自動的に実行したいそのブックのWorkbook.BeforeClose イベントに、マクロを実行するコードを記述します２つのフォルダへの保存マクロを、汎用的なものにしたいファイル名の部分を、編集中のブックの名前を表すActiveWorkbook.Nameにすると、編集中のブックならどれでもマルチ保存が可能になりますマクロ実行時に表示されるダイアログボックスを無視したい Application.DisplayAlerts プロパティをFalseにすると、マクロの実行中に警告やメッセージが表示されなくなります編集中のブックを、現在のフォルダとは別のフォルダにも、すばやく保存したい手間のかかる操作は、マクロ化すれば、簡単に処理できるようになりますマクロのコードを、見た目にわかりやすく、簡潔に記述したい With ステートメントを使えば、作業の対象となるオブジェクトの記述が１回で済みます B2セルに入力してあるデータを、そのシートの名前にしたいシートの名前は、Worksheet.Name プロパティで変えることができますリスト内の各データを、必要に応じて個別で表示したいマクロを使えば、ダブルクリックしたデータを、専用のユーザーフォームで表示することができます

名前付きのセル範囲から、 条件に合致するデータだけを参照したい

ユーザー定義関数を作れば、 それを使った配列数式で該当データを参照できます

Function ExtractStringsByCondition(rangeName As String, conditionColumn As Long, extractColumn As Long, condition As Variant) As Variant()

Dim rng As Range ' 名前付きセル範囲を格納する変数 Dim result() As Variant ' 抽出された文字列を格納する配列 Dim count As Long ' 抽出された文字列の数をカウントする変数 Dim i As Long ' ループ変数

' 名前付きセル範囲を取得 Set rng = Range(rangeName)

' 結果の配列を初期化 ReDim result(1 To rng.Rows.Count, 1 To 1) count = 0

' エラーチェック If Err.Number <> 0 Then ExtractStringsByCondition = CVErr(xlErrValue) ' #VALUE! エラーを返す Exit Function End If

' 抽出された文字列のみの配列をリサイズ ReDim Preserve result(1 To count, 1 To 1)

ExtractStringsByCondition = result ' 関数の戻り値として配列を返す

End Function

①Functionプロシージャを使って、関数名と引数を定義します

Function 関数名([引数1 As データ型], [引数2 As データ型], ...) As 戻り値のデータ型

' 関数の処理を記述します ' ...

' 処理結果を返す

関数名 = 結果の値

End Function

②処理で用いる変数を定義します

③rangeName引数で指定された名前付きセル範囲を変数rngに設定します

④result()配列を初期化し、抽出された文字列を格納するための準備をします

⑤count変数を初期化します

⑥Forループを使用して、rngの各行を順番に処理します

⑦条件が数値の場合、rngの指定された条件列(conditionColumn)の値が条件(condition)と一致するかどうかをチェックします

⑧条件が数値の範囲の場合、rngの指定された条件列の値が条件の範囲内にあるかどうかをチェックします

⑨エラーチェックを行います

⑩抽出された文字列のみの配列の大きさを調整し、不要な要素を削除します

⑪最後に、result()配列を関数の戻り値として返します

⑫以上のような関数を、標準モジュールで定義します

⑬本ページ冒頭の関数コードを貼り付けて使うこともできます

⑭ユーザー定義関数は、「関数の挿入」から呼び出せます

⑮定義した関数名を選んで、「OK」をクリックします

⑯それぞれの引数を指定します

⑰Ctrl＋Shift＋Enterキーで、配列数式として確定します

⑱作例のユーザー定義関数では、条件を範囲で指定することもできます

●セル範囲に付けた名前を使って、データを参照する方法です

関連する他のページ

名前付きのセル範囲から、条件に合致するデータだけを参照したい

ユーザー定義関数を作れば、それを使った配列数式で該当データを参照できます

　Dim rng As Range ' 名前付きセル範囲を格納する変数
　Dim result() As Variant ' 抽出された文字列を格納する配列
　Dim count As Long ' 抽出された文字列の数をカウントする変数
　Dim i As Long ' ループ変数

　' 名前付きセル範囲を取得
　Set rng = Range(rangeName)

　' 結果の配列を初期化
　ReDim result(1 To rng.Rows.Count, 1 To 1)
　count = 0

　' エラーチェック
　If Err.Number <> 0 Then
　　ExtractStringsByCondition = CVErr(xlErrValue) ' #VALUE! エラーを返す
　　Exit Function
　End If

　' 抽出された文字列のみの配列をリサイズ
　ReDim Preserve result(1 To count, 1 To 1)

　ExtractStringsByCondition = result ' 関数の戻り値として配列を返す

　' 関数の処理を記述します
　' ...

　' 処理結果を返す

　関数名 = 結果の値