☰

特定の行で値の 2 番目または 3 番目の出現を見つける - Excel

tag-icon • tag-icon microsoft-excel worksheet-function
特定の行で値の 2 番目または 3 番目の出現を見つける - Excel

連続して複数回発生する日付の列の位置を取得しようとしています。

サンプルデータ:
データ例.

次の数式はほとんどの列の位置を正しく識別しますが、重複が存在する場合は常に最初の出現の位置が返されます。

式 ：
式

よろしくお願いします。

答え1

次の数式は、n-th一致するセルと一致しないセルのセットから一致するものを選択します。これは、さまざまなソース データ レイアウトに適応できます (詳細は後述)。これは、幅広いIF(FL2<>0用途を想定しているため、エラー チェックをまったく提供しないという点で基本的なものですが、状況に合わせてエラー チェックを簡単に追加できます。

=FILTERXML("<Outer><Inner>"&SUBSTITUTE(TEXTJOIN("¢",FALSE,FILTER(SUBSTITUTE(ADDRESS(ROW(),COLUMN(AW2:CW2),4),ROW(),""),AW2:CW2=B2),ROW(),""),"¢","</Inner><Inner>")&"</Inner></Outer>","/Outer/Inner["&C2&"]")

これは、列 B の値と列 C の値が一致する行の列 A に入ることを前提として設定されています。n-thもちろん、これらを調整することもできます。

LET()すべての変数を 1 つの編集しやすい場所に配置するためにを使用するバージョンは次のとおりです。

=LET(
 RangeToExamine, AW2:CW2,  Delimiter, "¢",  ItemToMatch, B2,  InstanceToMatch,  C2,
 FILTERXML("<Outer><Inner>"
 &SUBSTITUTE(TEXTJOIN(Delimiter,FALSE,
 FILTER(SUBSTITUTE(ADDRESS(ROW(),COLUMN(RangeToExamine),4),ROW(),""),
 RangeToExamine=ItemToMatch),
 ROW(),""),  Delimiter,"</Inner><Inner>") & "</Inner></Outer>",
 "/Outer/Inner["&InstanceToMatch&"]"))

LET()数式をより簡単に編集できるようにするためです。

では、これが何をするのか。まず、レイアウトは 1 行かもしれませんが、確信が持てなかったので、どの行でも機能するようにレイアウトしたままにしました。これは、関数ROW()内で関数を使用しADDRESS()、その後、SUBSTITUTE()行番号を削除する関数を使用することで処理されます (したがって、列のみになり、これが目的の結果です)。必要に応じて、両方の場所に数字の「1」を配置して、行単位のアクティビティが発生しないようにすることができます。LET()あちこちで変更できるようにしたい場合は、に句を追加することもできます。ここでは行が 2 行にロールされ、面倒になるため、ここでは追加しませんでしたLET()。ちなみに、これは列の NUMBER を列の LETTER に変換する方法であり、MOD()(およびそれよりも悪い) 奇妙な数式を使用する必要はありません。UDF は必要ありません。

そこで、機能ADDRESS()を使用してSpill、検査対象範囲のすべてのセル アドレスの配列を作成します。FILTER()次に、一致の対象範囲を調べ、対応するセル アドレスをリストします。(繰り返しますが、エラーが発生するようには設定されていません。)SUBSTITUTE()次に、行番号 (または変更した場合は定数) を削除します。これで、Excel には一致があった列の列文字ラベルだけの配列が含まれるようになります。

ここで使用した区切り文字は、他の場合には必ずしも適切なものではないことに注意する必要があります。ただし、この区切り文字は基になるデータではなく、アドレス データに関連付けられているため、返されるアドレスで使用されない文字であるだけでよく、ADDRESS()ここでは失敗しません。ただし、他の場所で「めったに使用されない文字」アプローチを使用する場合は、文字マップで見つかった奇妙な文字を選択する必要がある場合があります。

次に、TEXTJOIN()アドレスの配列を文字列に変換し、SUBSTITUTE()その区切り</Inner><Inner>文字を文字列に置き換え、その前後に文字列を追加して、これまでの結果を許容可能な XML に変換します。XML の構造化には、少なくとも 1 つの上位レベルで要素をラップし、少なくとも 1 つの下位レベルで文字列の各部分をラップする限り、ほとんど何でも使用できます。私は、すべてをラップする上位レベルと作業レベルに をTEXTJOIN()使用するのが好きです。<Outer><Inner>

私はこれをどこかで初めて見たのですが、説明が下手なものでした。チャンドゥー彼の説明は（ほぼ）いつも非常に明確で役立つので、彼に何かヒントがあるかどうか確認しました。とても便利なので、感謝の意を表さなければなりません。

最後に、FILTERXML()最後の 1 つの処理を行います。これにより、返したい一致のインスタンスを選択できます。Chandoo のヒントでは、このように文を分解していますが、ご覧のとおり、列データを含め、取得したものや文字列に変換したものはすべて分解できます。この場合、3 番目のインスタンスが必要な場合は、[3]何かを追加します。

しかし、Excel はここでさらに優れています。まず、3 番目のインスタンスには [3] などの数値インスタンスを使用できますが、[last()]基数も検索せずに最後のインスタンスを検索するためにも使用できます。しかし、この関数が本当に優れているのは、驚くべきことに (この種のことを目的としているようには思えないため)、その句を「構築」できることです。言い換えると、変数データを含む文字列を一部に含めて構築すると、数式がレスポンシブになり、数式内で必要に応じて入力して変更する必要がなくなります。つまり、ユーザーは数式を編集するのではなく、セルに入力するだけでその機能を利用できます。または、必要に応じて他の数式の結果を反映させることもできます。

最後の部分は、フォーマットされた数式の最後の行にあります。「XML パス」(この場合は「/Outer/Inner」) の直後に、インスタンス番号または式を囲んだ角括弧 ("[ ]") を指定します。

一般的な使用では、次の点を確認するためにエラー チェックを行うことをお勧めします。

  1. 一致するアイテムは検査対象の範囲外です
  2. 必要なインスタンス番号は、一致の合計数よりも大きいです。必要なのは #6 ですが、存在するのは 4 つだけです...
  3. 同様に、最後のインスタンスから n 番目のインスタンスを検索する場合、そのインスタンスも存在することを確認します。「last()」から 6 番目のインスタンスを検索する場合、少なくとも 6 つのインスタンスが存在することを確認します...

データが文字や数字以外のものである場合、選択する区切り文字に注意してください。

列だけではなくセル アドレスが必要な場合は、SUBSTITUTE()完全なアドレスから行を削除する を削除します。

まあ、ある程度は申し訳ありませんが、これはあなたの投稿に完全に適合するものではなく、一般的な解決策です。しかし、あなたが正確にどのような用途で使用しているかはわかりませんでしたので、それも役立つかもしれませんし、編集内容も簡単に確認できます。ここでの経験から、投稿には役立つ情報が頻繁に省略されており、投稿者は必要な回答を得るために少なくとももう 1 つ質問する必要があります。そのプロセスを経る必要性を回避しようとしています。

答え2

COUNTIF 式をお探しですか? =IF(A2<>0,COUNTIF($A$2:A2,A2),0)

ここに画像の説明を入力してください

関連情報