Excel Programmfehler doppelte finden

[KlausiHB]

Hallo zusammen,

ich habe erschreckend feststellen müssen, dass Excel mir doppelte Werte angibt, die gar nicht doppelt sind.

Der Fehler tritt sowohl bei bedeingten Formatierungen (doppelte Werte einfärben) als auch bei ZÄHLNENWENN auf.
Z.B. werden diese Werte als doppelt vorhanden eingestuft:

00370435100094858832 und 00370435100094858757
00370435100094859303 und 00370435100094859327

Ein Direkter Vergleich (=A1=A2) ergibt allerdings ein korrektes Ergebnis: FALSCH

Die IDs, um die es geht, sind als Text in den Zellen gespeichert.
Zur Verdeutlichung habe ich eine Beispieldatei erstellt (Attachment).

Als provisorischen Workaround habe ich an alle IDs ein "A" angehängt, dann stimmen die Ergebnisse.
Da es aber über 1 Mio Zeilen sind, die mit anderen Listen abgeglichen werden müssen, und ich die Aufgabe habe, ein vorgandenes Makro zu beschleunigen, kostet es mich wertvolle Zeit, die Erweiterung mit "A" überall durchzuführen und wieder rückgängig zu machen.
Bisher läuft das Makro alle Zellen mit einer Schleife einzeln ab. Ich wollte die mehrfach vorhandenen in einem Rutsch verarbeiten.

Eine andere Idee, die ich hatte: 
Den Filter nutzen und dann mit TEILERGEBNIS (VBA: subtotal (3,...) schauen, ob es mehrere sind.
Aber auch das sind wieder mehrere Schritte.

Vielleicht hat ja jemand von Euch eine Idee, wie man das anders lösen kann.

Und eine Warnung an alle: Traut dem Zählenwenn nicht!

Vielen Dank im Voraus

KlausiHB

[neopa]

Hallo KlausiHB,

ZÄHLENWENN() hat die "Eigenschaft" als Text formatierte Zahlenwerte trotzdem als Zahlenwerte und nicht als Textwerte auszuwerten.

In Deinem konkreten Beispiel wirkt sich dies durch die "unglückliche" Kombination mit der Excel möglichen Genauigkeit von max 15 Stellen in der von Dir festgestellten Weise dann so aus.   In so einem Fall müsstest Du z.B. auf das alt bewährte SUMMENPRODUKT() zurückgreifen, also z.B. so=SUMMENPRODUKT((B$5:B$8=B5)*1).

Wenn man vorgenanntes beachtet, kann ZÄHLENWENN() schon eine sehr nützliche Funktion sein.

[KlausiHB]

Hi neopa,

das ist ja schon mal ein guter Hinweis mit dem Summenprodukt.
Ich befürchte zwar, dass das bei der Menge an Daten intern deutlich länger dauert als Zählenwenn, aber auf einen Versuch kommt es an.
Ich werde das einfach mal in eine function auslagern und erstmal benutzen. Wenn ich dann zukünftig noch was besseres finde, dann mach ich das an dieser zentralen function und messe die Verarbeitungszeit.

Vielen Dank für die prompte Antwort!

KlausiHB

[GMG-CC]

Moin,
wenn du in 2016 den Weg über Daten | Abrufen und transformieren gehst, dann geht das auch bei > 1Mio Zeilen recht gut. 
Dein Beispiel habe ich so gelöst: 

Code:

let
   Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
   #"Gruppierte Zeilen" = Table.Group(Quelle, {"Spalte1"}, {{"Anzahl", each Table.RowCount(_), type number}})
in
   #"Gruppierte Zeilen"

[KlausiHB]

Hi GMG-CC,

So ganz versteh ich Deinen Code nicht, aber da steht was von Table. Das wäre in VBA dann ein Listobject.
Tabellen  haben ja auf Wunsch eine Ergebniszeile... aber ... nee das meinste wohl nicht.
Die geschweiften Klammern sehen nach Matrixformeln aus.
Ich denke, Du hast da eine Ecke von Excel am Werk, die ich bisher nicht genutzt habe. Immer wieder erstaunlich, dass es sowas immer noch gibt  :75:
Du hast nicht zufällig ein Beispiel zum Hochladen?

Da es mir ja um Performance geht: ist das fix?

Und: Das ganze läuft zur Zeit unter Excel 2010. Geht das da auch?

LG

KlausiHB

[GMG-CC]

Moin,
(ohne Ironie:)  Schön, dass du dich mit dem aufgezeichneten Code auseinandersetzt und versuchst diesen zu verstehen. In plain Excel wird das in 2010/2013 nichts, da musst du dir von MS ein Add-In herunterladen: Power Query, -> http://www.excel-ist-sexy.de/power-query-das-add-in/. In 2016 ist die Funktionalität schon voll integriert.

So ganz versteh ich Deinen Code nicht, aber da steht was von Table. Das wäre in VBA dann ein Listobject.
Tabellen  haben ja auf Wunsch eine Ergebniszeile... aber ... nee das meinste wohl nicht.

Gut kombiniert! Nein, das meine ich nicht. Obwohl ein ListObject durch Power Query aus den Quelldaten gebildet wird und das dann die Datenbasis ist. Es handelt sich um die Sprache "M", die im Normalgebrauch für den User gar nicht sichtbar ist und im Hintergrund alle Klicks, die in einem speziellen Editor aufgezeichnet werden, aufzeichnet. Hier habe ich nur einige Klicks gebraucht (und den aufgezeichneten Code kopiert und hier im Forum eingefügt).

Ich denke, Du hast da eine Ecke von Excel am Werk, die ich bisher nicht genutzt habe. 

So ist es, Power Query wird nicht unbedingt beworben und ist auch noch nicht so verbreitet. Aber da es SQL-ähnlich ist, hast du eine enorme Leistungsfähigkeit.

Und: Das ganze läuft zur Zeit unter Excel 2010. Geht das da auch?

Früher, noch vor der Zeit der Ostfriesen-Witze gab es mal so eine Redensart, die immer wieder bei jeder passenden und unpassenden Gelegenheit angewendet wurde: "Wie heißt es doch bei Radio Eriwan: Im Prinzip ja..." - Versuche einfach, Power Query zu installieren. Wenn das klappt, läuft das alles auch bei dir. Das ist aber auch die Grundvoraussetzung zur Nutzung dieser Funktionalität.

Da es mir ja um Performance geht: ist das fix?

Es gehört zu den schnellsten Methoden. Was vielleicht noch schneller geht: Alles in ein Array (VBA) schaufeln und damit arbeiten.

Zur Beispiel-Lösung: 

• Ich habe der Übersicht wegen B5:B8 in Tabellenblatt Tabelle2 kopiert.
  
• Dann Menü Power Query und Von Tabelle
  
• Im Editor hat Power Query den Datentyp allein geändert, da ein Klick auf das X im rechten Seitenfenster (letzte Position)
  
• Menüpunkt Gruppieren nach, alle Einstellungen belassen und [OK]
  
• Ribbon Schließen und laden
  
• Freuen 
  
• ... (und eventuell die Liste an die Wunschposition verschieben).
  
• Verändern sich die Quelldaten, die Abfrage aufrufen und Ribbon Aktualisieren.
  

[KlausiHB]

Wow... da bin ich der Meinung gewesen, ich kenne mich mit Excel und VBA aus, und dann sowas..."M"... tststs...  
Ich versuche mich mal dran. Vor allem, weil es Deiner Meinung nach sehr flott arbeitet.
Ich lade mir schon mal Dein Beispiel dazu runter.
Da ich SQL kenne, wird die Umstellung ja vielleicht nicht so wild. 

KlausiB

[KlausiHB]

Ich habe mal PQ in Excel 2010 installiert. In 2016 isses integriert, wie Du sagtest.

Wenn ich das hier richtig lese, dann kann man aber anscheinend erst ab 2016 VBA zur Steuerung nutzen.
https://gallery.technet.microsoft.com/of...r-956a52d1

"...In Excel 2016 we have included Macro Recording and Object Model support. You can now write code in VBA, C#, Powershell or other languages, and automate the creation and refresh of Power Query queries..."

Und selbst wenn man die PQ in 2010 mittels Verweis zu VBA hinzufügen kann: sind die Makros dann kompatibel?

Gruß 

KlausiHB

[GMG-CC]

Ein klares "Jein"  :05:
Im Prinzip kannst du 99,5% aller Auswertungs- und Steuerungs-Aufgaben mit plain Power Query erledigen, ohne VBA mit einzubinden. Aktualisierungen bedürfen dann halt eines Klicks (was auch meist gut so ist). Übrigens: Ich in bis jetzt ohne VBA in Sachen Power Query ausgekommen. Probiers einfach einmal aus!

Also, das Ergebnis weiter oben ist ein Mal per Hand erstellt und auf Mausklick werden geänderte Quelldaten auch aktualisiert.

[Willie017]

Hallo

eine Pivottabelle täte es auch, lässt sich auch easy mit VBA programmieren...