Excelで検索機能を使用するにはどうすればよいですか?

サンプルファイル

1. アドレス機能

ADDRESS 関数は、列アドレスと行アドレスに基づいて有効なセル参照を返します。簡単に言えば、行番号と列番号を使用してセルのアドレスを作成できます。

構文

ADDRESS(行番号,列番号,abs_番号,A1,シートテキスト)

引数

  • row_num:行番号を指定する数値。
  • column_num:列番号を指定する数値。
  • [abs_num]:参照タイプ。
  • [A1]:参照スタイル。
  • [sheet_text]:シート名のテキスト値。

コメント

  • デフォルトでは、ADDRESS 関数は結果として絶対参照を返します。

例

以下の例では、さまざまな引数を使用してすべてのタイプの結果を取得しました。

Excel-アドレス関数の例-1

参照スタイル R1C1の場合:

  • 相対参照。
  • 相対行参照と絶対列参照。
  • 絶対行参照と相対列参照。
  • 絶対参照。

参照スタイル A1 の場合:

  • 相対参照。
  • 相対行参照と絶対列参照。
  • 絶対行参照と相対列参照。
  • 絶対参照。

2. ゾーン機能

AREAS 関数は、指定した参照内の範囲の数を表す数値を返します。簡単に言えば、関数内で参照したさまざまなワークシート領域を実際にカウントします。

構文

フィールド（参考）

引数

  • 参照:セルまたはセル範囲への参照。

コメント

  • 参照には、セル、セル範囲、または名前付き範囲を指定できます。
  • 複数のセル参照を参照する場合は、それらの参照すべてを複数の括弧で囲み、カンマを使用して各参照を他の参照から区切る必要があります。

例

以下の例では、zones 関数を使用して、名前付き範囲内の数値参照を取得しました。

Excel-ゾーンの例-1

ご覧のとおり、範囲には 3 つの列があり、結果として 3 が返されました。

Excel-ゾーン関数-例-2 (1)

3. 機能を選択します

SELECT 関数は、指定された位置番号に基づいて値のリストから値を返します。簡単に言うと、位置に基づいてリスト内の値を検索し、その値を結果として返します。

構文

SELECT(インデックス番号,値1,値2,…)

引数

  • Index_num:リスト内の値の位置を指定するために使用される番号。
  • value1:選択できるセル範囲または入力値。
  • [value2]:選択できるセル範囲または入力値。

コメント

  • セルを参照したり、関数に値を直接挿入したりすることもできます。

例

以下の例では、ドロップダウン リストを備えた CHOOSE 関数を使用して、4 つの異なる値 (合計、平均、最大、混合) を計算しました。そこで、次の式を使用して 4 つのことを計算しました。

=CHOOSE(VLOOKUP(K2,Q1:R4,2,FALSE),SUM(O2:O9),AVERAGE(O2:O9),MAX(O2:O9),MIN(O2:O9))

Excel-関数の選択例-1

この小さなテーブルには、必要な 4 つの計算の名前と、対応するセル内のそれぞれのシリアル番号が含まれています。

その後、4 つの計算すべてのドロップダウン リストが表示されます。この小さなテーブルから選択関数でインデックス番号を取得するには、ドロップダウン リストから選択した値に従ってシリアル番号を返す検索式を用意します。

また、値の代わりに、4 つの異なる計算に 4 つの式を使用しました。

4.COLUMN関数

COLUMN 関数は、指定されたセル参照の列番号を返します。ご存知のとおり、各セル参照は列番号と行番号で構成されます。したがって、列番号を取得して結果として返します。

構文

COLUMN([参照])

引数

  • 参照:列番号を取得するセル参照。

コメント

  • 複数の参照を参照することはできません。
  • 配列を参照している場合、column 関数は配列内の列番号も返します。
  • 複数のセルの範囲を参照している場合は、左端のセルの列番号を返します。たとえば、範囲 A1:C10 を参照すると、セル A1 の列番号が返されます。
  • 参照の指定をスキップすると、現在のセルの列番号が返されます。

例

以下の例では、COLUMN を使用してセル A1 の列番号を取得しています。

Excel 列関数の例 1

すでに述べたように、セル参照を無視すると、現在のセルの列番号が返されます。以下の例では、COLUMN を使用してシリアル番号を含むヘッダーを作成しました。

Excel 列関数の例 2

5.COLUMNS関数

COLUMNS 関数は、指定された参照で参照される列の数を返します。簡単に言うと、指定された範囲内の列の数をカウントし、その数を返します。

構文

列(テーブル)

引数

  • table:列数を取得するテーブルまたはセル範囲。

コメント

  • 名前付き範囲を使用することもできます。
  • COLUMNS 関数はセル内の値には関係せず、単に参照内の列数を返します。

例

以下の例では、COLUMN を使用して、範囲 A1:F1 内の列の数を取得しました。

Excel-列関数-例-1

6.FORMULATETEX関数

FORMULATEXT 関数は、参照されたセルの数式を返します。また、参照されたセルに数式がない場合、値または空白がある場合は、#N/A が返されます。

構文

TEXTFORMULA（参考）

引数

  • 参照:数式をテキストとして取得するセル参照。

コメント

  • 別のワークブックを参照している場合は、そのワークブックが開いている必要があります。開いていないと、数式が表示されません。
  • 複数のセルの範囲を参照している場合は、指定された範囲の左上のセルの数式が返されます。
  • 参照として使用しているセルに数式が含まれていない場合、8192 文字を超える数式がある場合、セルが保護されている場合、または外部ブックが開いていない場合は、エラー値「#N/A」が返されます。
  • 循環参照で 2 つのセルを参照すると、両方の結果が返されます。

例

以下の例では、さまざまな参照タイプの数式テキストを使用しています。数式のないセルを参照すると、エラー値「#N/A」が返されます。

Excel-式テキスト関数-例-1

7. HSEARCH機能

HLOOKUP 関数は、テーブルの先頭行の値を検索し、インデックス番号を使用して、一致した値と同じ列の値を返します。簡単に言えば、水平方向に検索します。

構文

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

引数

  • lookup_value:検索する値。
  • table_array:値を検索するデータ テーブルまたは配列。
  • row_index_num:値を取得する先頭行から下の行数を表す数値。たとえば、2 を指定し、検索値がデータ テーブルの A10 にある場合、セル B10 の値が返されます。
  • [range_lookup]:ルックアップ タイプを指定する論理値。完全一致検索を実行する場合は FALSE を使用し、非完全一致検索を実行する場合は TRUE (デフォルト) を使用します。

コメント

  • ワイルドカードを使用できます。
  • 完全一致とあいまい一致を実行できます。
  • あいまい一致を実行するときは、必ずデータを左から右の昇順に並べ替えてください。データが昇順でない場合は、不正確な結果が返されます。
  • range_lookup が true または省略された場合、非完全一致が実行されますが、検索値が検索範囲内に存在する場合は完全一致が返されます。
  • range_lookup が true または省略され、ルックアップ値がルックアップ範囲内にない場合、ルックアップ値より小さい最も近い値が返されます。
  • range_lookup が false の場合、データ範囲を並べ替える必要はありません。

例

以下の例では、HLOOKUP 関数と MATCH を使用して動的な数式を作成し、ドロップダウン リストを使用してセルの検索値を変更しました。

Excel-hlookup 関数の例-1

セル C7 のフィールド名が検索値として使用されます。範囲 B1: F5 をテーブル配列として使用し、row_index_num については match 関数を使用して行番号を取得しました。

Excel-hlookup関数-例-2

セル C9 の値を変更すると、テーブルの行番号が返されます。何度も式を変更する必要はありません。ドロップダウンで値を変更するだけで、その値が取得されます。

8. ハイパーリンク機能

HYPERLINK 関数は、ハイパーリンクが付加された文字列を返します。簡単に言えば、Excel の HYPERLINK オプションと同様に、HYPERLINK 関数はハイパーリンクの作成に役立ちます。

構文

HYPERLINK(リンクの場所,[フレンドリ名])

引数

  • Link_Location: HYPERLINK を追加する場所。さらに2つの期間に分けることができます。
    1. リンク:これは、同じワークシートまたは他のワークシートまたはワークブック内のセルまたはセル範囲のアドレスです。 Word 文書からブックマークをリンクすることもできます。
    2. location:これは、ハード ドライブへのリンク、UNC パスを使用するサーバー、またはインターネットまたはイントラネットからの任意の URL です。 (Excel Online では、HYPERLINK 関数に Web アドレスのみを使用できます)。関数へのリンクを挿入するには、関数を引用符付きのテキストとして挿入するか、リンクを含むセルをテキストとして参照します。 Web アドレスの前に必ず「HTTPS://」を使用してください。
  • [フレンドリー名]:これは、この機能のオプションの部分です。これは接続リンクの面として機能します。
    1. 任意のタイプのテキスト、数値、またはその両方を使用できます。
    2. また、friend_name を含むセルも参照しています。
    3. これを無視すると、関数はリンク アドレスを使用して表示します。
    4. Friendly_name がエラーを返した場合、関数はエラーを表示します。

コメント

  • Web アドレスに保存されたファイルをリンクする: Web アドレスに保存されたファイルを使用できます。これは、ファイルを効率的に共有するのに役立ちます。
  • ハードドライブに保存されているファイルをリンクする:オフラインで作業しているときにこの機能を使用することもできます。ハード ドライブに保存されているファイルをリンクし、単一の Excel シートからアクセスできます。各フォルダーに移動して開く必要はありません。
  • Word ドキュメント ファイルをリンク:これも HYPERLINK 機能の優れた機能です。ブックマークを使用して、Word 文書ファイルまたは Word 文書ファイル内の特定の場所をリンクできます。
  • フレンドリ名を使用せずにファイルをリンクする:ファイルまたは場所への実際のリンクをユーザーに表示したい場合。この状況では、HYPERLINK 関数のフレンドリ名の宣言を無視するだけで済みます。

9. 間接関数

INDIRECT 関数は、セル参照を表すテキスト文字列から有効な参照を返します。簡単に言うと、セルアドレスをテキスト値として使用してセル範囲を参照できます。

構文

INDIRECT(ref_text, [a1])

引数

  • ref_text:セルのアドレス、セル範囲のアドレス、名前付き範囲、またはテーブル名を表すテキスト。たとえば、A1、B10:B20、MyRange などです。
  • [a1]: ref_text で指定するセル参照タイプを表す数値またはブール値。たとえば、参照スタイル A1 を使用する場合は、TRUE または 1 を使用します。参照スタイル R1C1 を使用する場合は、参照スタイル R1C に FALSE または 0 を使用します。また、セル参照タイプを指定しないと、デフォルトで A1 スタイルが使用されます。

コメント

  • 別のワークブックを参照した場合は、そのワークブックを開く必要があります。
  • 参照した範囲に行または列を挿入した場合、INDIRECT はその参照を更新しません。
  • 関数にテキストを直接挿入する場合は、テキストを二重引用符で囲む必要があります。または、参照として使用するテキストを含むセルを参照することもできます。

例

1. 別のワークシートへの参照

INDIRECT を使用して別のワークシートを参照することもでき、そこにワークシート名を挿入する必要があります。以下の例では、indirect 関数を使用して別のワークシートを参照し、セル A2 にシート名、セル B2 にセル参照を設定しています。

Excel-間接関数-例-1

セル C2 では、次の数式を使用してテキストを結合しました。

=INDIRECT(“‘”&A2&”‘!”&B2)

この組み合わせにより、sheet1 のセル A1 を参照するために INDIRECT 関数で使用されるテキストが作成されます。最も優れた点は、ワークシート名またはセル アドレスを変更すると、参照が自動的に変更されることです。

「Sheet1」のセル A1 には値「Yes」があるため、indirect は値「Yes」を返します。

2. 別のワークブックへの参照

別のワークシートの場合と同じ方法で、別のワークブックを参照することもできます。必要なのは、参照として使用するワークブック名をテキストに追加するだけです。

Excel-間接関数-例-2

上の例では、次の数式を使用して、ブック「Book1」のセル A1 の値を取得しました。

=INDIRECT(“[“&A2&”]”&B2&”!”&C2)

セル「A2」にワークブック名、セル「B2」にワークシート名、セル「C2」にセル名があるためです。これらを組み合わせて、間接関数の入力テキストとして使用します。

注:セル参照をテキストとして結合する場合は、正しい参照構造に従っていることを確認してください。

3. 名前付き範囲の使用

はい、indirect 関数を使用して名前付き範囲を参照することもできます。それは簡単です。名前付き範囲を作成したら、その名前付き範囲を INDIRECT にテキストとして入力する必要があります。

Excel-間接関数-例-3

上の例では、セル E1 に名前付き範囲のリストを含むドロップダウン リストがあり、セル E2 でその名前を使用しています。範囲 B2:B5 には「数量」という名前が付けられ、範囲 C2:C5 には「金額」という名前が付けられます。

ドロップダウン リストから数量を選択すると、間接関数は名前付き範囲を即座に参照します。ドロップダウン メニューから金額を選択すると、セル範囲 C2:C5 の合計が表示されます。

11. 検索機能

SEARCH 関数は、行、列、またはテーブルから (探している) 値を返します。簡単に言うと、値を検索すると、その行、列、またはテーブルに値があれば、SEARCH によってその値が返されます。

構文

SEARCH(値, 検索範囲, [結果範囲])

SEARCH 関数には 2 種類があります。

  • ベクトル形状
  • 表形式

引数

  • value:列または行で検索する値。
  • lookup_range:値を検索する列または行。
  • [result_range]:値を返したい列または行。これはオプションの引数です。

コメント

  • 配列形式を使用する代わりに、VLOOKUP または HLOOKUP を使用することをお勧めします。

12.MATCH関数

MATCH 関数は、配列値のインデックス番号を返します。簡単に言えば、MATCH 関数はリスト内の値を検索し、リスト内のその値の位置番号を返します。

構文

MATCH (ルックアップ値、ルックアップ配列、[マッチタイプ])

引数

  • lookup_value : 値のリストから位置を取得する値。
  • lookup_array : セル範囲または配列には値が含まれます。
  • [match_type] : Excel が値のリスト内の値を検索する方法を指定する数値 (-1、0、および 1)。
    1. 1 を使用すると、検索値以下の最大値が返されます。リスト内の値は昇順に並べ替える必要があります。
    2. -1 を使用すると、検索値以上の最小値が返されます。リスト内の値は昇順に並べ替える必要があります。
    3. 0 を使用すると、リストに完全に一致するものが返されます。

コメント

  • ワイルドカードを使用できます。
  • リスト内に一致する値がない場合、if は #N/A を返します。
  • match 関数では大文字と小文字が区別されません。

例

以下の例では、一致タイプとして 1 を使用し、値 5 を探しています。

Excel-一致関数の例-1

すでに述べたように、一致タイプに 1 を使用すると、検索値以下の最大値が返されます。リスト全体には 5 未満の値が 3 つあり、4 が最高です。

そのため、結果では値 4 の位置である 3 が返されました。

13.シフト機能

OFFSET 関数は、セルまたはセル範囲から特定の行数と列数だけ離れた範囲への参照を返します。簡単に言うと、開始セルから始まる行と列を使用して、セルまたはセル範囲を参照できます。

構文

OFFSET(基準、行、列、[高さ]、[幅])

引数

  • Reference : オフセットして開始する基準。セルまたは隣接するセルの範囲を指定できます。
  • rows : OFFSET に参照から上または下の移動を指示する行数。下げるには正の数が必要で、上げるには負の数が必要です。
  • Cols : 列の数は、OFFSET に参照から左または右に移動するように指示します。右に行くには正の数が必要で、左に行くには負の数が必要です。
  • [高さ] : 参照に含める行を指定する数値。
  • [幅]:参照に含める列を指定する数値。

コメント

  • OFFSET は「揮発性」関数であり、スプレッドシートに変更があるたびに再計算されます。
  • #REF が表示されます!オフセットがワークシートの端の外側にある場合はエラー値。
  • 高さまたは幅を省略した場合は、基準の高さと幅が使用されます。

例

以下の例では、SUM と OFFSET を使用して、特定の製品のすべての月の値を合計するダイナミック レンジを作成しました。

Excel-オフセット関数-例-1

14. LINE機能

ROW 関数は、参照されたセルの行番号を返します。簡単に言えば、ROW 関数を使用すると、セルの行番号を取得でき、セルを参照していない場合は、セルを挿入したセルの行番号を返します。

構文

LINE([参考])

引数

  • 参照:行番号を確認するセル参照またはセル範囲。

コメント

  • これには、すべてのタイプのシート (グラフ シート、ワークシート、またはマクロ シート) が含まれます。
  • Folio は、表示されているか、非表示になっているか、非常に隠されている場合でも参照できます。
  • 関数に値を指定しない場合は、関数を適用したシートのシート番号が表示されます。
  • 無効なシート名を指定すると、#N/A が返されます。
  • 無効なシート参照を指定すると、#REF! が返されます。

例

以下の例では、row 関数を使用して、関数を使用した同じセルの行番号を確認しています。

Excel ライン関数の例 1

以下の例では、別のセルを参照して、そのセルの行番号を取得しています。

Excel ライン関数の例 2

row 関数を使用すると、スプレッドシートにシリアル番号のリストを作成できます。セルに行関数を入力し、シリアル番号を追加するセルにドラッグするだけです。

Excel-line-関数の例-3

15. LINES機能

ROWS 関数は、参照範囲内の行数を返します。 ROWS関数を簡単に言うと、参照した範囲内の行数をカウントすることができます。

構文

ライン（表）

引数

  • array:行数を確認するためのセル参照または配列。

コメント

  • 名前付き範囲を使用することもできます。
  • セル内の値には関係なく、単に参照内の行数を返します。

例

以下の例では、10 セルの垂直範囲を参照しましたが、範囲には 10 行があるため、結果として 10 が返されました。

Excelの行関数の例

16.トランスポーズ機能

TRANSPOSE 関数は、範囲の方向を変更します。簡単に言えば、この関数を使用すると、データを行から列に、また列から行に変更できます。

構文

トランスポーズ（テーブル）

引数

  • array:転置する配列または範囲。

コメント

  • Ctrl+Shift+Enter を押して、ソース範囲にあるのと同じ数のセルを使用して、TRANSPOSE を配列関数として適用する必要があります。
  • ソース範囲より小さいセルを選択した場合、データはそれらのセルに対してのみ転置されます。

例

ここでは、データを範囲 B2:D4 から範囲 G2 ～ I4 に転置する必要があります。

Excel-transpose関数-例-1

このためには、まずセル G2 に移動し、I4 までのセル範囲を選択する必要があります。

Excel-転置関数-例-2

次に、セル G2 に「(=TRANSPOSE(B2:D4))」と入力し、Ctrl+Shift+Enter を押します。

Excel-transpose関数-例-3

TRANSPOSE はデータを行から列に変換します。適用した数式は配列数式であるため、単一のセルを変更することはできません。

Excel-転置関数-例-4

コメントを追加する

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です