どのようにExcelでN番目の発生を検索する
VLOOKUPを使用すると、常に最初の一致が得られます。 INDEXMATCH関数でも同じことが起こります。では、2番目のマッチまたは3番目またはn番目のVLOOKUPをどのように行うのでしょうか。
この記事では、範囲内の値のN番目のオカレンスを取得する方法を学習します。
一般式
{=SMALL(IF(range=value,ROW(range)-ROW(first_cell_in_range)+1),n)}
注:これは link:/ excel-array-formulas-arrays-in-excel-formula [arrayformula]
です。 CTRL + SHIFT + ENTERで入力する必要があります。範囲:値のn番目の位置を検索する範囲*。
値:範囲内でn番目の位置を探している値。 First_cell_in_range:範囲内の最初のセル。範囲がA2:A10の場合、範囲の最初のセルはA2です。*
n:値の発生数。
物事を明確にするための例を見てみましょう。
===
例:Excelで2番目の一致を見つける
だからここに私はExcelの範囲A2:A10の名前のこのリストを持っています。この範囲に名前を付けました。ここで、名前の中で2番目に出現する「Rony」の位置を取得したいと思います。
上の画像では、範囲A2:A10(名前)の7番目の位置にあることがわかります。次に、excel式を使用してその位置を取得する必要があります。
上記の一般式をC2に適用して、リスト内で2番目に出現するRonyを検索します。
{=SMALL(IF(names=“Rony” ,ROW(names)-ROW(A2)+1),2)}
CTRL + SHIFT + ENTERで入力してください。
そして、私たちは答えを持っています。正しい7を示しています。 nの値を3に変更すると、8になります。範囲内の値の出現よりも大きいnの値を変更すると、#NUMエラーが返されます。
===
どのように機能しますか?
まあ、それは非常に簡単です。それぞれの部分を一つずつ見ていきましょう。
link:/ Tips-if-condition-in-excel [IF]
(names =“ Rony”、 link:/ lookup-and-reference-excel-row-function [ROW]
(names)-` link: / lookup-and-reference-excel-row-function [ROW] `(A2)+ 1 *)
:
IFでは、names =“ Rony”はTRUEとFALSEの配列を返します。範囲名(A2:A10)のセルが「Rony」に一致する場合は常にTRUE。\ {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}。
ここで、ROW関数は、名前の各セルの行番号を返します。
\ {2; 3; 4; 5; 6; 7; 8; 9; 10}。
link:/ lookup-and-reference-excel-row-function [ROW]
(names)-` link:/ lookup-and-reference-excel-row-function [ROW] `(A2)*次に、行を減算します指定された配列の各値からのA2の数。これにより、0から始まるシリアル番号の配列が得られます。
\ {0; 1; 2; 3; 4; 5; 6; 7; 8}。
link:/ lookup-and-reference-excel-row-function [ROW]
(names)-` link:/ lookup-and-reference-excel-row-function [ROW] `(A2)+1:取得するには1から始まるシリアル番号は、この配列の各値に1を加算します。これにより、1から始まるシリアル番号が得られます。
\ {1; 2; 3; 4; 5; 6; 7; 8; 9}。
これで、IF(\ {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE}、\ {1; 2; 3; 4; 5; 6; 7; 8; 9})ができました。
これは\ {1; FALSE; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE}に解決されます。
これで、式が link:/ statistics-formulas-excel-small-function [SMALL]
(\ {1; FALSE; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE}、2)に解決されました。
これで、SMALLは範囲内で2番目に小さい値である7を返します。
どのように使用しますか?
質問が到着します:n番目の一致の生のインデックスを取得することの利点は何ですか? n番目の値から関連情報を取得できればさらに便利です。まあ、それもできます。範囲名(A2:A10)のn番目の一致の隣接セルの値から値を取得する場合。
{=INDEX(B2:B10, SMALL(IF(names=“Rony” ,ROW(names)-ROW(A2)+1),2))}
そうそう、これが範囲内でn番目の一致を取得する方法です。私は十分に説明できたと思います。この記事またはその他のExcel / VBA関連のトピックについて疑問がある場合は、以下のコメントセクションに書き込んでください。
関連記事:
link:/ information-formulas-how-to-get-sequential-row-number-in-excel [Excelで順次行番号を取得する方法]
link:/ lookup-formulas-vlookup-top-5-values-with-duplicate-values-using-index-match-in-excel [Vlookup Top 5 Values with Duplicate Values Using INDEX-MATCH in Excel]
link:/ lookup-formulas-vlookup-multiple-values [VLOOKUP Multiple Values]
link:/ lookup-formulas-use-index-and-match-to-lookup-value [INDEX andMATCHを使用して値を検索する]
link:/ lookup-formulas-lookup-value-with-multiple-criteria [Lookup Value with Multiple Criteria]
人気の記事:
link:/ forms-and-functions-introduction-of-vlookup-function [ExcelのVLOOKUP関数]
link:/ Tips-countif-in-microsoft-excel [COUNTIF in Excel 2016]
link:/ excel-formula-and-function-excel-sumif-function [ExcelでSUMIF関数を使用する方法]