Note: The other languages of the website are Google-translated. Back to English

Excelの列範囲から一意の値のリストを動的に抽出するにはどうすればよいですか?

値が定期的に変化する列範囲の場合、どのように変化したかに関係なく、常に範囲からすべての一意の値を取得する必要があります。 一意の値の動的リストを作成するにはどうすればよいですか? この記事では、その対処方法を説明します。

式を使用して、列範囲から一意の値のリストを動的に抽出します
VBAコードを使用して、列範囲から一意の値のリストを動的に抽出します


式を使用して、列範囲から一意の値のリストを動的に抽出します

以下のスクリーンショットに示すように、範囲B2:B9から一意の値のリストを動的に抽出する必要があります。 次の配列式を試してください。

1. D2などの空白のセルを選択し、次の数式を入力して、を押します。 Ctrlキー + シフト + 入力します 同時にキー。 (B2:B9は一意の値を抽出する列データであり、D1は数式が配置されている上記のセルです)

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

2.セルD2を選択し続け、フィルハンドルを下にドラッグして、指定した範囲からすべての一意の値を取得します。

これで、列範囲B2:B9のすべての一意の値が抽出されます。 この範囲の値が変更されると、一意の値リストはすぐに動的に変更されます。

Excelの範囲内のすべての一意の値を簡単に選択して強調表示します。

世界 重複セルと一意セルを選択します の有用性 Kutools for Excel 以下のスクリーンショットに示すように、すべての一意の値(最初の重複を含む)またはXNUMX回だけ表示される一意の値、および必要に応じて重複する値を簡単に選択して強調表示するのに役立ちます。
今すぐKutoolsforExcelをダウンロードしてください! ((30-日フリートレイル)


VBAコードを使用して、列範囲から一意の値のリストを動的に抽出します

次のVBAコードを使用して、列範囲から一意の値のリストを動的に抽出することもできます。

1。 押す 他の + F11 キーを同時に開いて アプリケーション向け Microsoft Visual Basic 窓。

2。 の中に アプリケーション向け Microsoft Visual Basic ウィンドウ、クリック インセット > モジュール。 次に、以下のVBAコードをコピーしてに貼り付けます モジュール 窓。

VBAコード:範囲から一意の値のリストを抽出します

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
For I = 1 To xLastRow2
  If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
     ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
  End If
Next
End Sub

注意:コードでは、D2は一意の値リストを見つけるセルです。 必要に応じて変更できます。

3.ワークシートに戻り、をクリックします インセット > 形状 > 長方形。 スクリーンショットを参照してください:

4.ワークシートに長方形を描き、表示する必要のある単語をいくつか入力します。 次に、それを右クリックして選択します マクロの割り当て 右クリックメニューから。 の中に マクロの割り当て ダイアログボックスで CreateUniqueList マクロ名 ボックスをクリックし、 OK ボタン。 スクリーンショットを参照してください:

5.次に、長方形のボタンをクリックします。 Kutools for Excel ダイアログボックスが表示されます。抽出する必要のある一意の値が含まれている範囲を選択して、[ OK ボタン。

これ以降、上記の手順5を繰り返して、一意の値のリストを自動的に更新できます。


関連記事:


最高のオフィス生産性ツール

Kutools for Excelはほとんどの問題を解決し、生産性を80%向上させます

  • 再利用: すばやく挿入 複雑な数式、チャート および以前に使用したものすべて。 セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
  • スーパーフォーミュラバー (複数行のテキストと数式を簡単に編集できます); 読書レイアウト (多数のセルを簡単に読み取って編集する); フィルター範囲に貼り付け...
  • セル/行/列をマージする データを失うことなく; 分割セルコンテンツ; 重複する行/列を組み合わせる...重複セルを防止します。 範囲を比較する...
  • [複製]または[一意]を選択します 行; 空白行を選択 (すべてのセルは空です); スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
  • 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
  • スーパーフィルター (フィルタースキームを保存して他のシートに適用します); 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
  • ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
  • 300 以上の強力な機能. Office / Excel 2007-2021 および 365 をサポートします。すべての言語をサポートします。 企業や組織に簡単に導入できます。 フル機能の 30 日間無料トライアル。 60日間の返金保証。
kteタブ201905

Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
officetab下部
コメントを並べ替える
コメント (35)
まだ評価はありません。 最初に評価してください!
このコメントは、サイトのモデレーターによって最小化されました
チュートリアルありがとうございます。 数式メソッドを使用して、カテゴリ修飾子を追加する場合、数式をどのように変更しますか? 列Cで、アイテムが果物か野菜かを区別するとします。 ユニークな果物のみを並べ替えて野菜を除外するようにコードをどのように変更しますか? (LIST RANGE、 "CATEGORY")のXNUMX番目のcountif基準を使用して、COUNTIFをCOUNTIFSに置き換えようとしましたが、空白が返されます。 アレイを拡張してVLOOKUPを組み込む必要がありますか?
このコメントは、サイトのモデレーターによって最小化されました
私は優れていますが、上記の式がどのように、そしてなぜ機能するのかについて頭を悩ませようとしています(これは私が使用している目的で機能しますが、理由を理解する必要があります)。 配列を使うと少し混乱することがあるので、ばかげた言葉での説明は非常に役に立ちますよろしく
このコメントは、サイトのモデレーターによって最小化されました
この式は古く、機能しません。 私は文字通り、この正確なExcelシートを設定して、この数式が機能するかどうかを確認しましたが、機能しません。
このコメントは、サイトのモデレーターによって最小化されました
ヘイ、あんた、
どのOfficeバージョンを使用していますか?
このコメントは、サイトのモデレーターによって最小化されました
{= INDEX($ Q $ 3:$ Q $ 263、MATCH(0、COUNTIF(V $ 2:V2、$ Q $ 3:$ Q $ 263)、0))}-これは別のサイトで機能することがわかりました...

Ctrl + Shift + Enterを使用して、配列関数(中括弧)を取得します。 #NAが表示されるまで、数式をコピーして貼り付けます。 私のデータセットはColumn-Qにあり、この同じ列に沿って継続的に伸びるColumn-Vの一意のリストに存在するかどうかを比較しました。
このコメントは、サイトのモデレーターによって最小化されました
良い一日。
列Qのすべての一意の値をabobv式でリストしてから、彼の式= IF(D2 = V1、 "Match"、 "No match")を使用して、同じ行の列Vと比較する列Qの一意の値を比較してください。 。
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、そしてあなたの助けに感謝します。

まさにこの機能が必要ですが、「一意の値」のリストは行ではなく列にまたがる必要があるため、行を下に展開するリストは機能しません。

列間でドラッグするときに「一意の値」リストを展開するために、この数式を変更するにはどうすればよいですか?

オフセット()?
転置()?
行ではなく列への参照と連結された絶対参照の文字列を使用したIndirect()?


おかげで再び!
このコメントは、サイトのモデレーターによって最小化されました
親愛なるライアン、
この式=IFERROR(INDEX($ B $ 2:$ B $ 9、MATCH(0、COUNTIF($ D $ 2:D2、$ B $ 2:$ B $ 9)、0))、 "")+ Ctrl + Shift + Enter canあなたが問題を解決するのを手伝ってください。
以下のスクリーンショットを参照してください。
このコメントは、サイトのモデレーターによって最小化されました
また、何らかの理由で、元の式は次のように提供されます。
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

「循環参照」警告を返し、計算しません。
このコメントは、サイトのモデレーターによって最小化されました
親愛なるライアン、
どのOfficeバージョンを使用していますか? この数式は、私のOffice2016と2013でうまく機能します。
このコメントは、サイトのモデレーターによって最小化されました
私は以前にこれを経験しました-私の修正は、セルD1(使用していたワークシートに相当)に数式を入力することでした。 $ D:$ 1が対応するセルは、下のセル(D2)に入力する必要があります。 それがエラーを受け取った理由ではない場合はお詫びします
このコメントは、サイトのモデレーターによって最小化されました
VBAオプションをExcel2016for macOSで動作させるためのヒントはありますか? 手順に従いました。 ただし、マクロを実行しても何も起こりません。 ありがとう!
このコメントは、サイトのモデレーターによって最小化されました
Daer Jones、
以下のVBAコードを試して、それが機能するかどうかをお知らせください。 ありがとうございました!

サブCreateUniqueList()
Dim xRng As Range
xLastRowを暗くする
薄暗いxLastRow2AsLong
Dim I As Integer
'エラー時に次を再開
Set xRng = Application.InputBox( "範囲を選択してください:"、 "Kutools for Excel"、Selection.Address 、、、、、 8)
xRngが何もない場合は、Subを終了します
エラーで次の再開
xRng.Copy Range( "D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range( "D2:D"&xLastRow).RemoveDuplicates Columns:= 1、Header:= xlNo
xLastRow2 = Cells(Rows.Count、 "B")。End(xlUp).Row
I=1の場合xLastRow2へ
If ActiveSheet.Range( "D2:D"&xLastRow2).Cells(I).Value = "" Then
ActiveSheet.Range( "D2:D"&xLastRow2).Cells(I).Delete
終了する場合
次へ
End Subの
このコメントは、サイトのモデレーターによって最小化されました
こんにちはクリスタル、
一意の値のリストのVBバージョンを使用しようとしていますが、問題が発生しています。
一意の値の列を作成する範囲は、さまざまなタブを参照するすべての数式です。
数式の代わりに転送する値を取得するにはどうすればよいですか?
このコメントは、サイトのモデレーターによって最小化されました
親愛なるマイク、
数式参照を絶対に変換してから、VBスクリプトを適用してください。
このコメントは、サイトのモデレーターによって最小化されました
数式が列名を参照し、絶対値に変換できないことを除いて、同じ問題があります。
数式ではなく値を貼り付けるようにvbaを変更するにはどうすればよいですか?
このコメントは、サイトのモデレーターによって最小化されました
日付が9/12の場合にのみ動的リストに追加したい場合など、複数の基準をどのように追加しますか?

MATCH数式で「&」を試していますが、機能しません。

たとえば、あなたの例に基づいて:
= IFERROR(INDEX($ B $ 2:$ B $ 9、MATCH(0&B4、COUNTIF($ D $ 1:D1、$ B $ 2:$ B $ 9)&$ A $ 2:$ A $ 9、0))、 "" )。
これにより、エラーがスローされるか、重複が作成されます。

または、「+」が機能する可能性があることを読みましたが、機能させることはできません。 またはSMALLを使用します。

アイデアですか?
このコメントは、サイトのモデレーターによって最小化されました
親愛なるザック、
申し訳ありませんが、これについてはサポートできません。フォーラムに質問を投稿できます。 https://www.extendoffice.com/forum.html 私たちの専門家からより多くのExcelサポートを取得します。
このコメントは、サイトのモデレーターによって最小化されました
3番目の変数をどのように追加しますか? たとえば、ある列のすべての一意の要素が、別の列でも同様の値を共有するようにします。 あなたの例では、「Department」というタイトルのXNUMX番目の列を想像してください。この列には、製品、肉などの値があります。これらはすべて農産物であると思いますが、うまくいけば、私の主張を理解できます。 CountIF式をCOUNTIFSに変更しますか、それとも別の方法で変更しますか?
このコメントは、サイトのモデレーターによって最小化されました
こんにちはマット
この式を試してください=IF(ISNA(VLOOKUP(A2、$ C $ 2:$ C $ 13,1、FALSE))、 "Yes"、 "")。
XNUMXつの比較リストが列Aと列Cであるとすると、一意の値が列Aにのみ残り、列Cにはない場合、列Bに「はい」と表示されます。 一方、列Bに何も返さない場合は、対応する値が列Aと列Cの両方にとどまっていることを意味します。
このコメントは、サイトのモデレーターによって最小化されました
返信ありがとうございます。ただし、YESと表示された場合は、その一意の値を引き出すのが大変です。別の列で一意の値を引き出す式を教えてください。
このコメントは、サイトのモデレーターによって最小化されました
Macの最新バージョンのExcelでXNUMX行のExcelシートに対してこれを行うと、返されません。 最初の行は機能しますが、複製すると、ExcelはXNUMX時間以上値を返さない計算モードになります。

2または50の一意の値を返す大きなリスト(最大60k行)に対してこれを行う方法について何か考えはありますか?

私はこれを「Numbers」アプリでモックアップしましたが、計算に数分しかかからず、そこで完全に機能しています。 Excelでは時間がかかりすぎて、完成するのではないかと思います。 何が起こるかを見るために、一晩「実行」させる予定です。
このコメントは、サイトのモデレーターによって最小化されました
計算オプションを確認してください。 自動に設定する必要があります。 ファイル>オプション>数式>計算オプション>ワークブック計算(自動選択)
このコメントは、サイトのモデレーターによって最小化されました
さまざまなサイズのデータ​​セットを入力でき、何も調整する必要がないように、数式を実際のデータを超えてドラッグしようとしています。 ただし、実際のデータが終了した後の最後の行は常に「0」を返します。 隣接する列の他の何かに一意の値を使用していますが、0を指定すると、最後の値が繰り返されます(0を削除すると、値は繰り返されなくなります)。 これを修正する方法はありますか? また、Office365Businessを使用しています
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、あなたの助けに感謝します。
では、値をアルファベット順に並べ替えるにはどうすればよいですか? (マスターテーブルでフィルターを使用したくない)
COUNTIFの代わりにCOUNTIFSを使用する必要がありますか?
助けてください
このコメントは、サイトのモデレーターによって最小化されました
こんにちはアレクシス、
申し訳ありませんが、数式と同時に抽出された値をアルファベット順に並べ替えることはできません。 コメントありがとうございます。
このコメントは、サイトのモデレーターによって最小化されました
私はその=IFERROR(INDEX($ B $ 2:$ B $ 9、MATCH(0、COUNTIF($ D $ 1:D1、$ B $ 2:$ B $ 9)、0))、 "")式を使用しています。 4つの列に対してですが、私のデータは列と行の範囲に分散しています。 数式を編集して、領域全体を含めることはできますか? 私のデータはAC60からARXNUMXまで生きています...
このコメントは、サイトのモデレーターによって最小化されました
VBAコードと数式を試してみます。 コードVBAは非常にうまく機能していますが、マクロを含むファイルを保持できません。 しかし、問題は、数式を機能させることができないことです。 誰かアイデアがありましたか? ありがとうございました
このコメントは、サイトのモデレーターによって最小化されました
こんにちはシャー​​ロット、
コメントありがとうございます。 ブックをExcelマクロ対応ブックとして保存することにより、将来使用するためにファイルをマクロとともに保持できます。
数式の問題について、データのスクリーンショットを提供していただけますか? コメントありがとうございます。
このコメントは、サイトのモデレーターによって最小化されました
どうもありがとうございました
このコメントは、サイトのモデレーターによって最小化されました
別の数式が使用された範囲でvbaコードを機能させる方法は?列BIには、列DとEを参照する数式があります。
コードを列Lに適用する場合(たとえば、コード内のセルを適切に変更する場合)、マクロは列MとNに適用される数式を返します...それでは機能しますが、希望どおりではありません。列Bの値を保持する方法?ありがとう
このコメントは、サイトのモデレーターによって最小化されました
Ik heb gemerkt dat de'formule'-methode erg traag werktbijgroteデータセット。 Een goed alternatiefは、het gebruik vaneendraaitabelです。 Kies dan alleen de rijlabels、je krijgt daneenlijstjeはuniekewaardenに会いました。 Het kan zijn dat je wat extra's krijgt "(leeg)"bijvoorbeeld。 Je kunt deze ervervolgensuitfilteren。 Helaas kun je maarop1クリテリウムフィルター。 Ook daar zijn wel weer oplossingen voor、maardatはワットコンプレックスです。
このコメントは、サイトのモデレーターによって最小化されました
2つの別々の列範囲(B9:B2)と(D9:DXNUMX)を使用することを除いて、これとまったく同じことを実行できるようにしたいと思います。これは可能ですか?
このコメントは、サイトのモデレーターによって最小化されました
こんにちはアンソニー、
結果を元のデータと同じ列に配置できます。 この場合の列Bなど。
ただし、次のように、数式の結果セルの一番上のセルを参照する必要があります。
= IFERROR(INDEX($ B $ 2:$ B $ 9、MATCH(0、COUNTIF($ B $ 11:B11、$ B $ 2:$ B $ 9)、0))、 "")+ Ctrl + Shift + Enter
このコメントは、サイトのモデレーターによって最小化されました
con este procedimiento de filtro se hace de forma muy rapida

1.EN ESTE EJEMPLO los datos a remover los duplicados estan en la col A de la fila 59 a la 239
2. se define un criterio de filtrado en este caso en la fila d56 el mismo titulo de la lista a remover duplicados y la d57 la dejo en blanco
3. una vez ejecutado se muestran los datos en la fila destino, que en mi caso fue la d59

Range("A59:A239").AdvancedFilter アクション:=xlFilterCopy, CriteriaRange:=Range _
("D56:D57")、CopyToRange:=Range("D59")、ユニーク:=True
ここにはまだコメントが投稿されていません
あなたのコメントを残す
ゲストとして投稿
×
この投稿を評価:
0   文字
推奨される場所

フォローする

著作権©の2009 - WWW。extendoffice.com。 | | 全著作権所有。 搭載 ExtendOffice。 | サイトマップ
MicrosoftおよびOfficeのロゴは、米国MicrosoftCorporationの米国およびその他の国における商標または登録商標です。
SectigoSSLで保護