☰

繰り返しエントリを含む複数列リストを数式を使用して部分文字列のみでフィルタリングする

tag-icon • tag-icon microsoft-excel worksheet-function
繰り返しエントリを含む複数列リストを数式を使用して部分文字列のみでフィルタリングする

部分的な文字列で配列をフィルターし、単一の数式 (オートフィルター、VBA、または追加の列なし) を使用して複数のエントリを 1 回だけリストすることは可能ですか? たとえば、次のスプレッドシートがあります。

A | B  | C        | D       | F  
-----------------------------------  
01| ID | Class    | Value   | Filter
02| 1  | A/as     | V1      |
03| 1  | B/as     | V2      |
04| 2  | A/ab     | V3      |
05| 3  | B/ab     | V4      |
06| 3  | B/as     | V5      |
07| 2  | B/ab     | V5      |
08| 1  | A/as     | V5      |
09| 3  | A/ab     | V5      |

列Bをフィルターするとクラス「A/*」ですが、その値を 1 回だけ表示する場合、結果は次のようになります。

A | B  | C        | D       | F    
-----------------------------------  
01| ID | Class    | Value   | Filter
02| 1  | A/as     | V1      | V1
03| 1  | B/as     | V2      | V3
04| 2  | A/ab     | V3      | V2
05| 3  | A/ab     | V1      | 
06| 3  | B/as     | V4      |
07| 2  | B/ab     | V5      |
08| 1  | A/as     | V2      |
09| 3  | A/ab     | V2      |

の代わりに

A | B  | C        | D       | F   
-----------------------------------  
01| ID | Class    | Value   | Filter
02| 1  | A/as     | V1      | V1
03| 1  | B/as     | V2      | V3
04| 2  | A/ab     | V3      | V1
05| 3  | A/ab     | V1      | V2
06| 3  | B/as     | V4      | V2
07| 2  | B/ab     | V5      |
08| 1  | A/as     | V2      |
09| 3  | A/ab     | V6      |

部分文字列で列をフィルタリングする（適応）それ説明を次のように変更します。

...
    IF(
                    ISNUMBER(Search("A/*"; $B$2:$B$9))  
...

結果はリスト$B$2:$B$9のインデックスを持つ配列になります。

ISNUMBER(Search("A/*"; $B$2:$B$9): {1;0;1;1;0;0;1;1}

など

IF(IS...): {1;3;4;8;9}

これまでのところ、これを「一意の名前リスト」アプローチと組み合わせる方法は見つかっていない。

`MATCH(0;INDEX(COUNTIF(`

説明どおりここ

私が持っているのはそれだけですが、うまく機能せず、かなりのCPU負荷を引き起こします。たとえば、セルC8

{=IFERROR(INDEX(
               INDEX($D$2:$D$9;
                     IF(ISNUMBER(SEARCH("A/*";$C$2:$C$9));
                        ROW($D$2:$D$9)-ROW($D$2)+1)));
               MATCH(0;
                     INDEX(COUNTIF($C$2:C7;
                                   INDEX($D$2:$D$9;
                                         IF(ISNUMBER(SEARCH("A/*";$C$2:$C$9));
                                            ROW($D$2:$D$9)-ROW($D$2)+1)));
                     0;0);0));
          "error")

答え1

私はそれを解決しました。完璧ではありませんが（3 つの列が必要です）、うまく機能します。

A | B     | C     | D      | E       | F       | G      
-------------------------------------------------------  
01| Array |Array  | Text  | search   | search  | ordered
02| Source|Source | sought| results  | results |
03|   #1  |  #2   | *a*   |    #1    |    #2   |
04| aa    | c12   |       | c12      | c12     | c02
05| ca    | c13   |       | c13      | c13     | c06    
06| ad    | c06   |       | c06      | c06     | c12
07| ee    | c11   |       | c02      | c02     | c13
08| fa    | c02   |       | c06      | c25     | c25      
09| gg    | c12   |       | c13      |         |
10| ba    | c06   |       | c06      |         |
11| aa    | c13   |       | c25      |         |
12| ad    | c06   |       | #NUM!    |         |
13| gt    | c12   |       | #NUM!    |         |
14| aa    | c25   |       | #NUM!    |         |

列E列 B の対応するセルに D3 の式が含まれている場合、列 C のすべての項目を一覧表示します。セル E4 の数式は E5-E14 にコピーされます。

{=INDEX(C:C;
        SMALL(IF(ISNUMBER(SEARCH($D$3&"/*";
                                 $B$4:$B$14));
                 ROW($B$4:$B$14));
              ROWS($E$4:E4)))}

数式を配列として入力するには、Ctrl + Shift + Enter キーを押す必要がありますが、大きなテーブルを検索する場合はかなり時間がかかる可能性があるので注意してください。セルが 1300 個あり、数式を入力するだけで 1 分以上かかりましたが、他のセルにコピーするのに遅延はありません。

これが何をするのかを説明します。

  • 索引 （引数1、引数2）項目/セルnの値を出力します（引数2）の列C（引数1）。Nは次のように計算されます。小さい。
  • 小さい（引数1、引数2）はk番目（引数2）データセット内の最小値（引数1
    この関数は、データセット内の特定の相対的な位置にある値を返します。これは、もしそして行、より深くネストされています。

  • もし （論理テスト、真の場合の値）これがトリックの主要部分です。IF条件が真となる行番号の配列を構築します（もしありません 'それ以外' 値、それはちょうど間違い条件が真でない場合

    • 論理テスト:ISNUMBER(SEARCH($D$3&"/*"; $B$4:$B$14))範囲 $B$4:$B$15 内の各セルに対して、SEARCH の結果が数値かどうかに応じて True と False の配列を返します。
      上の例の結果は次のようになります。
      1. 検索：1、2、1、#値、2、#値、2、1、1、#値、1
      2. 番号:真、真、真、偽、真、偽、真、真、真、偽、真
      3. 値が true の場合: ROW($B$4:$B$14)) は、配列 $B$4:$B$14 の行番号が入った配列を返します。
        上の例の結果は、4、5、6、7、8、9、10、11、12、13、14 です。

    もし組み合わせる論理テスト#1と真の場合の値#2. #1 のすべての値は無視されますが、#2 は配列 #2 内の同じ位置で FALSE となります。最後に、指定された範囲内で列 B のどの行にあるかを示す配列が得られます。行（...）D3の式が見つかりました。上の例の結果は4,5,6,8,10,11,12,14です。

  • 行($E$4:E4)は、増加する数字（F2で1、F3で2など）を与えるトリックです。小さいとして引数2最初のセルの結果（行(...)=1) は 4 (最小値)、2 番目は 5 などになります。最後に、後続の各セルに、D3 の式が見つかった列 B の行番号/位置が表示されます。

列F重複をフィルタリングする、これが最も難しい部分でした。列 F には、列 E にリストされているすべての項目が 1 回だけリストされます。
これが、F5 に入力された数式です。(F4 は E4 と同じです) Ctrl + Shift + Enter を使用します。

{=IFERROR(INDEX($C$2:$C$14;
                MATCH(0;
                      COUNTIF($E$4:E4;
                              $C$2:$C$14);
                      0));
          "")}

これが何をするのかを説明します。

  • COUNTIF(引数1、引数2）範囲の長さの配列が返される引数11は、のエントリの一致を示します。引数2
    上の例の結果は、1、1、0、0、0、1、0、0、0、0、0、0 です。
  • マッチ（参照値、配列、マッチタイプ）結果の配列で0が最初に現れる場所を探すCOUNTIF(...)上の例の3番目の位置は何ですか
    • 参照値:0、最初の新しい値/重複なし
    • 配列:結果として得られる配列COUNTIF(...)
    • マッチタイプ:0 = 正確に
  • 索引（引数2COUNTIF、Match(...) の最終的に、範囲の3番目の位置にある新しい/重複していない値が表示されます。引数2上の例のc06とは何ですか。

列G最後に、すべての項目がアルファベット順に並べられます。これは、Ctrl + Shift + Enter を使用して G4 に入力された数式です。

{=IFERROR(INDEX($F$4:$F$14;
                        MATCH(ROWS($G$4:$G4);
                              COUNTIF($F$4:$F$14;
                                      "<="&$F$4:$F$14);
                              0));
        "")}

これが何をするのかを説明します。

  • COUNTIF(引数1、引数2）これがトリックの主要部分です。引数2他のすべてのテキスト値は引数1そして、その相対的な順位（アルファベット順）を返します。
    上の例の結果は、
    3、4、2、1、5です。
  • 行($E$2:E2)は、増加する数字（G2では1、G3では2など）を与えるトリックです。マッチとして参照値。
  • マッチ（参照値、配列、マッチタイプ）最初の出現を探す行(...)結果の配列ではCOUNTIF(...)
    上の例の結果は、
    4、3、1、2、5 です。
    1. 細胞：行(...)=1 => 4
    2. 細胞：行(...)=2 => 3
    3. 細胞：行(...)=3 => 1
    4. 細胞：行(...)=4 => 2
    5. 細胞：行(...)=5 => 5
  • INDEX(COUNTIFのarg1、Match(...))最終的に、そのソートされたエントリが表示されます行(...)結果。上の例の結果は次のようになります。
    1. 細胞：行(...)=1 => 4インチカウント配列 => c02
    2. 細胞：行(...)=2 => 3インチカウント配列 => c06
    3. 細胞：行(...)=3 => 1インチカウント配列 => c12
    4. 細胞：行(...)=4 => 2インチカウント配列 => c13
    5. 細胞：行(...)=5 => 5インチカウント配列 => c25

ここまでは順調です。最後のステップは、すべてを1つの列にまとめることです。少なくとも、ヘルプ列 F と G を結合します (ただし、今日は実行しません)。

関連情報