いきなり答える備忘録

Google Workspace・Microsoft 365・LibreOfficeなどに関するメモ

最終更新日

（Excel）重複するデータを削除し最後だけ残す方法

Excel

　Excelで、値が重複するデータがある場合に最後のものだけ残す方法についてです。
　「最後」の意味が複数考えられるので、どのような抽出が適当かを確認する必要があります。

データとやりたいことの確認

　次の画像中のデータの中には、同じ担当者を記した行がそれぞれ複数あります。

　ここから「担当者の重複を除き最後のものだけ残す」わけですが、次の2つの意味が考えられるので以下ではそれぞれの方法を試してみます。日付が新しいデータが必ずしも下にあるわけではないことが注意点です。

  • 担当者が重複する行を削除し日付の値が最新であるものを残す
  • 担当者が重複する行のうち最も下にあるものを残す

    日付が最も新しいものを残す方法

    　次の画像ではFILTER関数を使い、各担当者について日付が最も新しいデータを残しています。
    　なおG列には日付の表示形式を適用しています（以下同様）。

    　F3セル

    =FILTER(B3:D9,C3:C9=MAXIFS(C3:C9,B3:B9,B3:B9))

    　意外に簡潔に求められます。
    　各行のC列の日付が「担当者を条件とする日付の条件付き最大値」と一致するかをチェックし、該当する行を抽出しています。
    　FILTER関数の第2引数（抽出条件）は各行において次のように解釈され、該当するかどうかの判定結果（TRUE/FALSE）を返します。

    C3=MAXIFS(C3:C9,B3:B9,B3)
C4=MAXIFS(C3:C9,B3:B9,B4)
・・・
C9=MAXIFS(C9:C9,B3:B9,B9)

    　なお「各担当者の最新の日付」にあたるデータがが複数ある場合はいずれの行も残ります。
    　また、MAXIFSをMINIFSに代えれば「日付が最も古い」データが残ります。

    一番下にあるものを残す方法

    UNIQUE関数等を使い2段階で抽出する方法

    　まずはUNIQUE関数を使って担当者名の重複を除いた結果を得ます。

    　F3セル

    =UNIQUE(B3:B9)

    　


    　続いて隣のセルに次の式を入力し、右方にフィルコピーすれば完成です。

    　G3セル（H3にフィルコピー）

    =XLOOKUP($F3#,$B3:$B9,C3:C9,,,-1)

    　XLOOKUPを使い、各担当者名を下から検索して値を抽出しています。スピル範囲演算子（#）を使っているのがポイントで、データの変更によりUNIQUE関数で抽出される行数が増減しても、それに対応して出力される行数が変わります。
    　XLOOKUP関数の仕様により検索値が複数ある場合に1列しか返さないのが残念なところで、「#」のほか絶対参照の「$」も必要となり、簡単とはいいがたい式になります。
    　なお第6引数（-1）を省略すると「最も上にあるものを残す」になります。

    1つの式で済ませる方法

    　気合でワンライナーする例ですが最新の関数が使えればそれほど難しくないです。

    　F3セル

    =CHOOSEROWS(B3:D9,XMATCH(UNIQUE(B3:B9),B3:B9,,-1))

    　XMATCH関数で各担当者名を下から検索して位置を取得、さらにCHOOSEROWS関数でその位置にある行を取得しています。
    　INDEXでこれができればいいんですがやはり1列しか返さないのでCHOOSEROWSを使っています。



    　次はCHOOSEROWSを使わずFILTER/XMATCH等でやる例です。Excel2021でも動くかもしれません。

    　F3セル

    =FILTER(B3:D9,SEQUENCE(ROWS(B3:B9))=XMATCH(B3:B9,B3:B9,,-1))

    　考え方は次のとおりです。

    1. SEQUENCE関数でデータの7つの行に対応する1から7の整数を生成する
    2. XMATCH関数で各行の担当者名をB3:B9の範囲から（ただし下から）検索し、上から何行目（1～7）にあるか検索する
    3. 1.と2.で生成された各7つの値を比較し、一致するならそれに対応するB3:D9中の行を抽出する

    備考

    　複数条件の設定や他の関数の使用例については、長くなるので個別の記事で掘り下げたいと思います。

    • accs2014