Wie kann ich eine Formel erstellen, die die Anzahl der Duplikate zählt, den Wert in einer Zelle meiner Wahl zurückgibt und dann die Duplikate löscht?

Question 1

Mein Vorschlag betrifft nur den ersten Teil (sortierte Produktliste):

Sie können Ihre Liste beispielsweise in eine Excel-Tabelle einfügen Table1und diese Liste als bezeichnen Table1[List].
In Spalten Cerhalten DSie eine Liste von Unikaten mit ihren Anzahlen.
Formel in C2: =UNIQUE(Table1[List])
Formel in D2: =COUNTIF(Table1[List],C2#)
Als nächsten Schritt können Sie diese beiden Spalten wie in Spalten Fund gezeigt sortieren G.
Formel in F2:=SORT(HSTACK(C2#,D2#),2,-1)

Answer

Mein Vorschlag betrifft nur den ersten Teil (sortierte Produktliste):

Sie können Ihre Liste beispielsweise in eine Excel-Tabelle einfügen Table1und diese Liste als bezeichnen Table1[List].
In Spalten Cerhalten DSie eine Liste von Unikaten mit ihren Anzahlen.
Formel in C2: =UNIQUE(Table1[List])
Formel in D2: =COUNTIF(Table1[List],C2#)
Als nächsten Schritt können Sie diese beiden Spalten wie in Spalten Fund gezeigt sortieren G.
Formel in F2:=SORT(HSTACK(C2#,D2#),2,-1)

Question 2

Wenn Sie über die folgenden Daten verfügen und MS365diese verwenden, können Sie die folgenden Methoden anwenden:

Faser	Kalzium	Eisen	Magnesium	Zink	Kupfer	Mangan	Jod	Selen	Fluorid
weiße Bohnen	Milch	angereicherte Cerealien	Kürbiskerne	Rindfleisch	Krabbe	Weizenkeime	Ofenkartoffel	Paranuss	schwarzer Tee
Linsen	Joghurt	Weizenkleie-Flocken	Mandeln	Kürbiskerne	Hummer	Süßkartoffeln	Milch	Sonnenblumenkerne	Garnele
Pintobohnen	Parmesan	Grütze	Spinat	Sonnenblumenkerne	Shiitake-Pilze	Blattkohl	Garnele	Thunfisch	Rosinen
Kichererbsen	schweizerisch	Granola	schwarze Bohnen	Linsen	weiße Knöpfe	Erbsen	Truthahn	Lachs	Himbeeren
Limabohnen	Hüttenkäse	Weizenschrot	Lachs	Truthahn	tragbare	Pinienkerne	weiße Bohnen	Hummer	gekochte Haferflocken
Avocado	Spinat	Reiscreme	Joghurt	Eier	cremefarben	Haselnüsse	Eier	Garnele	Bier
Kürbiskerne	Blattkohl	Rindfleisch	Milch	Shiitake-Pilze	Süßkartoffeln	Pecannüsse		Schweinefilet	Rotwein
Sesamsamen	Rübengrün	Hackfleisch	Avocado		Rübengrün	Hafer		Rindfleisch	Apfelwein
Mandeln	Brokkoli	Rinderfilet	Bananen		Ofenkartoffel	Limabohnen		Lamm	Grütze
Pistazien	Schwarzäugige Erbsen	Büffelfilet			Spinat	Kichererbsen		Huhn	Schwarze Bohnensuppe
Pecannüsse	weiße Bohnen	gemahlener Bison			Sonnenblumenkerne	weiße Bohnen		Truthahn	Ofenkartoffel
Sonnenblumenkerne	Lachs	Porterhouse-Burger			Kürbiskerne	Linsen		weiße Bohnen	Möhren
Eichelkürbis	Eichelkürbis	rindfleisch eintopf fleisch			Paranuss	Spinat		Pintobohnen	Spinat
Hubbard-Kürbis	Hummer	Schweinefilet			Walnüsse	Ananas		Limabohnen	Kantalupe

• Die Verwendungsfunktion ist nur GROUPBY()auf MS365--> anwendbar Office Insiders.

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     GROUPBY(_SingleCol,_SingleCol,ROWS,,0,-2))

Oder, Verwenden von BYROW()oderMMULT()

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     _Uniq, UNIQUE(_SingleCol),
     _Counts, MMULT(N(_Uniq=TOROW(_SingleCol)),SEQUENCE(ROWS(_SingleCol))^0),
     HSTACK(_Uniq,_Counts))

=LET(
     _Data, A3:J16,
     _SingleCol, TOCOL(_Data,1),
     _Uniq, UNIQUE(_SingleCol),
     _Counts, BYROW(_Uniq,LAMBDA(x,SUM(--(x=_SingleCol)))),
     HSTACK(_Uniq,_Counts))

Alle oben gezeigten Formeln ergeben die Anzahl der Zählungen für jeden einzelnen Datensatz.
Verwenden Sie TOCOL()die Funktion, um die Daten mehrerer Bereiche in ein einspaltiges Array umzuwandeln, wobei die Duplikate ausgeschlossen werden.
Methode 1--> Bei der ersten Methode wird GROUPBY()eine Funktion verwendet, die so konzipiert ist group, aggregatedass sortsie den von uns angegebenen Feldern entspricht. Die besagte Funktion ist jedoch in, daher funktioniert sie nicht und wird als Fehler Office-Insidersangezeigt, solange Sie die Option nicht aktiviert haben .#NAME!
Methode 2--> Beginnt auch mit der LET()Funktion, was das Lesen erleichtert und die Verwendung redundanter Bereiche/Formeln reduziert.
Wie bei der ersten Methode wird zuerst die TOCOL()Funktion angewendet, dann wird UNIQUE()die Funktion verwendet, um eindeutige Werte zurückzugeben, und zuletzt wird MMULT()die Funktion verwendet, um die Zählungen zurückzugeben, die das Matrixprodukt der beiden gegebenen Arrays sind.
Kombinieren Sie abschließend HSTACK()die beiden zurückgegebenen Arrays mit UNIQUE()und MMULT()der Funktion, um die gewünschte Ausgabe zurückzugeben.
Methode 3--> Beginnt mit den gleichen Schritten wie oben, außer dass eine BYROW()Funktion verwendet wird, um die übereinstimmenden Anzahlen der einzelnen Elemente zu erhalten.

Wenn Sie diese Methoden jedoch zu kompliziert finden, können Sie auch die einfachste Methode verwenden, die unten gezeigt wird:

Verwenden Sie zunächst die Funktion TOCOL()& UNIQUE(), um die verschiedenen Bereiche in einem Array zurückzugeben, wobei die leeren Bereiche und Duplikate ausgeschlossen werden. Und platzieren Sie sie in einer Zelle, sagen wir inL2

=UNIQUE(TOCOL(A3:J16,1))

Geben Sie abschließend die folgende Funktion ein, um die Zählungen abzurufen. Denken Sie daran, dass ich den Datenbereich übernommen habe, den A3:A16Sie möglicherweise nach Ihren Wünschen ändern müssen. Das Folgende muss nicht ausgefüllt werden, da es sonst überlaufen würde. Wo L2wird die Formel von oben zurückgegeben, indem TOCOL()undUNIQUE()

=COUNTIFS(A3:J16,L2#)

Wenn Sie immer noch zögern, den obigen Anweisungen zu folgen, können Sie POWER QUERYFolgendes verwenden: ist ab verfügbar Excel 2010+für , verfügbar in Windows Excel 2010+undExcel 365 (Windows or Mac)

Um Power Query zu verwenden, befolgen Sie die Schritte:

Konvertieren Sie zunächst die Quellbereiche in eine Tabelle und benennen Sie diese entsprechend. Für dieses Beispiel habe ich sie wie folgt benannt:Table1

Öffnen Sie als Nächstes eine leere Abfrage über Datadie Tabulatortaste --> Get & Transform Data--> Get Data--> From Other Sources-->Blank Query

Das obige lässt das Power QueryFenster öffnet sich, jetzt von HomeTab --> Advanced Editor--> Und fügen Sie das Folgende ein, M-Codeindem Sie alles entfernen, was Sie sehen, und drücken SieDone

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Value"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Zum Schluss importieren Sie es wieder nach Excel--> Klicken Sie auf Close & Loadoder Close & Load To-->. Der erste Klick erstellt ein New Sheetmit der erforderlichen Ausgabe, während der letzte Klick ein Fenster öffnet, in dem Sie gefragt werden, wo das Ergebnis abgelegt werden soll.

^{Hinweis: Die Formel kann keine Duplikate löschen, sondern extrahiert Werte, wenn sie unter bestimmten Bedingungen in eine Zelle eingefügt werden. Wie ich oben gezeigt habe. Sie benötigen im Grunde die Funktion UNIQUE(), TOCOL()& COUNTIF()oder COUNTIFS(), um die gewünschte Ausgabe zu erhalten}

Platzieren Sie Rough Listdiese Formel einfach in einer beliebigen leeren Zelle, wobei Listder Name der Zelle steht Table:

=LET(
     _Uniq, UNIQUE(List),
     _Output, SORT(HSTACK(_Uniq, COUNTIF(List,_Uniq)),2,-1),
     VSTACK({"Nutrients","Counts"},_Output))

Einfacher geht es GROUPBY()ggf. mit Funktion:

=GROUPBY(List,List,ROWS,,0,-2)

Answer