Microsoft Excel

Herbers Excel/VBA-Archiv

gibt es identische Zahlen in meiner Tabelle

Betrifft: gibt es identische Zahlen in meiner Tabelle von: Jenny
Geschrieben am: 09.08.2014 12:49:11

Hallo an alle,

hat jemand von euch eine Idee, wie ob sich folgendes herausfinden lässt.

Habe eine große Tabelle2, es geht um die Zellen B2:LS331.
Ich würde gerne wissen, ob wenn ich die Zahlen in diesem Bereich auf 10 Nachkommastellen runde, ob es dann Zahlen gibt, die zu den 5000 kleinsten Zahlen gehören und mehrfach in der Tabelle vorkommen. Es reicht mir als Ergebnis einer möglichen Formel ja oder nein. Welcher Wert es ist würde mich erst interessieren wenn ja, aber da ich die Chance als extrem gering ansehe, wird denke ich ja oder nein als Antwort erstmal reichen.

Hat da jemand eine Idee, die auch berücksichtigt, dass die Mappe so riesig ist, das nur noch ca. 300 Formeln fehlen, damit die Maximalanzahl der Formeln pro Mappe erreicht ist?

Danke für eure Ratschläge
Jenny

  

Betrifft: AW: gibt es identische Zahlen in meiner Tabelle von: Christian
Geschrieben am: 09.08.2014 13:51:18

Hallo Jenny,

Ideen gibts, denke ich mal, genug!

Aber:
Die Frage ist, ob diese Dich weiterbringen.
Warum hat Deine Mappe so "extrem viele" Formeln?
Könnte man nicht die Formeln durch VBA-Routinen ersetzen?

Eine verschachtelte ZÄHLENWENN() mit KKLEINSTE()-Formel könnte Dir weiterhelfen, aber unter den Umständen, wie Du sie beschreibst, weiss ich nicht genau ob Dich dass weiterbringt.

MfG Christian


  

Betrifft: AW: gibt es identische Zahlen in meiner Tabelle von: Jenny
Geschrieben am: 09.08.2014 13:58:40

Hallo Christian,

die Mappe ist eine Entfernungstabelle zwischen 330 Städten, berechnet als Luftlinie zwischen Koordinaten. das sind dann schonmal 108.900 Formeln für die Entfernungen, dazu 30.000 Formeln für Berechnungen daraus und 990 Formeln für Umrechnungen der Koordinaten.

Das eine Grenze erreicht ist schließe ich einfach daraus, dass eine komplette Hilfsspalte mit 5000 Formeln nicht mehr berechnet wird, überprüft ob eine Grenze erreicht ist, habe ich nicht.

Das blöde ist halt, ich kann daran nichts mehr ändern, sobald ich die Werte statt den Formeln einfüge.

Gruß
Jenny


  

Betrifft: AW: gibt es identische Zahlen in meiner Tabelle von: Christian
Geschrieben am: 09.08.2014 14:13:57

Hallo Jenny,

dann würde sich eine VBA-Lösung anbieten, was aber ohne eine Bsp.-Mappe schwer zu erstellen sein wird.

MfG Christian


  

Betrifft: AW: gibt es identische Zahlen in meiner Tabelle von: Jenny
Geschrieben am: 09.08.2014 14:19:45

Hallo Christian,

ich hab den Aufbau in einem anderen Thread skizziert. Ich weiß im Moment aber nicht, was ich an der Mappe von 3,2 MB weglassen kann, damit sie die Größe von 300 KB zum Hochladen hier nicht überschreitet.
Hoffe die Auflistung der Formeln reicht da schon aus. Das einzige was es außer diesen Formeln noch in der Tabelle gibt ist Tabelle 1 Spalte A, Tabelle2, Spalte A und Tabelle 2 Zeile 1 mit Städtenamen

und Tabelle 1 Spalte B und C mit den Koordinaten in Dezimalform. Der Rest sind alles Formeln.

Gruß
Jenny


  

Betrifft: AW: gibt es identische Zahlen in meiner Tabelle von: Adis
Geschrieben am: 10.08.2014 19:12:13

Hallo

vielleicht reicht ja eine Beispiel Datei mit den Stadtnamen und Koordinaten um in Ruhe über eine VBA Lösung nachzudenken.
Das könnte mit 300 KB hinkommen. Oder 2 Beispieldatein machen, eine mit Staedtenamen und eine mit den Koordinaten.

Gruss Adis


  

Betrifft: AW: gibt es identische Zahlen in meiner Tabelle von: Jenny
Geschrieben am: 10.08.2014 20:11:32

Habs mal probiert, eine kleine Tabelle mit Erklärungen, wie die komplette Tabelle aussieht.

Hoffe das reicht.
https://www.herber.de/bbs/user/92003.xlsx
Jenny


  

Betrifft: AW: gibt es identische Zahlen in meiner Tabelle von: Jenny
Geschrieben am: 09.08.2014 14:13:42

Hallo Christian

ich meine, wenn du dich in der Lage siehst, die Formeln durch VBA Routinen zu ersetzen, dann gerne, ich bin da überfordert.

In der Tabelle gibt es folgende Formeln, ich nenne die Formel die in der ersten Zeile des genannten Bereiches steht und die hab ich dann in den gesamten Bereich kopiert.

Tabelle1!D1:D330 : =B1*PI()/180
Tabelle1!E1:E330 : =C1*PI()/180
Tabelle1!G1:G330 : =B1+C1

Tabelle2:B2:LS331 :

=WENN(SPALTE()>ZEILE();ARCCOS(SIN(SVERWEIS($A2;Tabelle1!$A:$E;4;0))*SIN(SVERWEIS(B$1;Tabelle1!$A:$E; 4;0))+COS(SVERWEIS($A2;Tabelle1!$A:$E;4;0))*COS(SVERWEIS(B$1;Tabelle1!$A:$E;4;0))*COS(SVERWEIS(B$1; Tabelle1!$A:$E;5;0)-(SVERWEIS($A2;Tabelle1!$A:$E;5;0))))*6378,137;"") 
Tabelle3!A1:A5000 : =KKLEINSTE(Tabelle2!$B$2:$LS$331;ZEILE(A1))
Tabelle3!B1:B5000 : {=INDEX(Tabelle2!A:A;MAX((A1=Tabelle2!B$2:LS$331)*ZEILE($2:$331)))}
Tabelle3!C1:C5000 : {=INDEX(Tabelle2!$1:$1;MAX((A1=Tabelle2!B$2:LS$331)*SPALTE(B:LS)))}
Tabelle3!D1:D5000 : =ZÄHLENWENN(B$1:C1;B1)
Tabelle3!E1:E5000 : =ZÄHLENWENN(B$1:C1;C1)
Tabelle3!F1:F5000 : =ODER(UND(D1=$G$1;E1>=$G$1);UND(UND(D1>=$G$1;E1=$G$1)))

Ziel des Ganzen ist, herauszufinden, ob die Formeln in Spalte C und D in Tabelle 3 auch funktionieren, wenn ich die Entfernungen auf 10 Nachkommastellen runde, oder ob es Entfernungen gibt, die sich erst ab der 11. Nachkommastelle unterscheiden.

Gruß
Jenny


  

Betrifft: AW: gibt es identische Zahlen in meiner Tabelle von: Ewald
Geschrieben am: 09.08.2014 15:39:15

Hallo,

wenn du sehen willst ob es doppelte Werte gibt,kannst du auch die bedingte Formatierung benutzen,da brauchst du keine Formel.

Gruß Ewald


  

Betrifft: AW: gibt es identische Zahlen in meiner Tabelle von: Jenny
Geschrieben am: 09.08.2014 15:50:24

Hallo Ewald, und wie mache ich das? Auch in Bezug auf die Rundung?

Liebe Grüße
Jenny


  

Betrifft: AW: gibt es identische Zahlen in meiner Tabelle von: Ewald
Geschrieben am: 09.08.2014 17:03:17

Hallo Jenny,

Den Bereich B2 - LS331 markieren

Bedingte Formatierung/Regeln zum Hervorheben von Zellen/Doppelte Werte (kann in deiner Version vielleicht etwas anders heißen)

eventuell andere Farbe wählen und OK.

Alles andere macht jetzt Excel,es ist egal wieviel Stellen die Zahl hat.

Ob es eventuell durch das Runden Probleme gibt, mußt du ausprobieren.

Die doppelten Werte werden jetzt in der eingestellten Farbe hervorgehoben.

Gruß EWald


  

Betrifft: nachgefragt ... von: neopa C (paneo)
Geschrieben am: 11.08.2014 08:36:43

Hallo Jenny,

... so wie Du es beschreibst, sind die Ergebnisse in den Tabellen 1 und 2 nach Berechnung fix. Somit könntest Du für Deine Vergleichsberechnung die Formeln durch ihre Ergebniswerte ersetzen (jeweils eine könntest Du ja für später zu erfassende neue / andere Städte stehen lassen) und schon dürfte da ganz kein Problem mal darstellen.

Oder?

Gruß Werner
.. , - ...


  

Betrifft: AW: nachgefragt ... von: Jenny
Geschrieben am: 12.08.2014 18:39:16

Hallo Werner,

da hast du nicht unrecht, jedoch beantwortet das immer noch nicht meine ursprüngliche Frage, wegen der diese Diskussion überhaupt entstanden ist.

"Ziel des Ganzen ist, herauszufinden, ob die Formeln in Spalte C und D in Tabelle 3 auch funktionieren, wenn ich die Entfernungen auf 10 Nachkommastellen runde, oder ob es Entfernungen gibt, die sich erst ab der 11. Nachkommastelle unterscheiden."

Gruß
Jenny


  

Betrifft: AW: gibt es identische Zahlen in meiner Tabelle von: Daniel
Geschrieben am: 12.08.2014 18:59:34

HI
ich würde die Städte nicht als Kreuztabelle anordnen, sondern als Linear-Tabelle:

Aachen - Aalen
Aachen - Abbeville
Aachen - Aalborg
...
Aachen - Zeebrügee
Aalen - Abbeville
Aalen - Aalborg
...
Aalen - Zeebrügge
Abbeville - Aalborg
...
Abbeville - Zeebrügge
...
...
Ypern - Zeebrüge

und hier dann die KM berechnen (nach der Berechnung: Formeln durch Werte ersetzen)
dann kannst du die ganze Tabelle nach den KM-Abständen sortieren.
um dann in der Sortierten Liste herauszufinden, ob KM-abstände gleich sind, brauchst du nur eine Zeile mit ihren Nachbarn vergleichen und nicht mehr mit allen anderen Zeilen.
Da kannst du dann auch problemlos eine Zweite Zeile hinzufügen, in der du den Vergleich mit den gerundeten Werten machst.

Gruß Daniel


  

Betrifft: AW: gibt es identische Zahlen in meiner Tabelle von: Jenny
Geschrieben am: 12.08.2014 22:05:21

Hallo Daniel,

danke für den Vorschlag, nur wie bekomme ich diese Lineartabelle hin? Du siehst an der Kreuztabelle, dass das untere Dreieck in den Berechnungen fehlt, sowie alle Verbindungen bei denen beide Städte identisch sind, also B2, C3, E4 usw, d.h. das müsste bei deiner Lineartabelle genauso sein. Mir fallen da keine Formeln ein, wie man das hinbekommt mit 330 Städten.

Gruß
Jenny


  

Betrifft: AW: gibt es identische Zahlen in meiner Tabelle von: Daniel
Geschrieben am: 12.08.2014 22:43:04

Hi
anbei das Formelwerk zum erstellen der Lineartabelle
die Formeln müssten für beliebige Datenmengen funktionieren (Anzahl der Städte in Tabelle1), du musst nur die Formeln entsprechend weit nach unten ziehen (die letzte Zeile für Tabelle2 steht in Tabelle1 Spalte A in der letzten Zeile).

wenn du die Tabelle erstellt hast, kannst du in der Tabelle2 die Formeln durch Werte ersetzen und dann die Tabelle so sortieren, dass du deine Auswertungen mit geringstmöglichem Rechenaufwand machen kannst (im Sortieren ist Excel auch bei grossen Datenmengen sehr schnell, das sollte man ausnutzen)
https://www.herber.de/bbs/user/92060.xlsx
Makros wurden nicht verwendet.
Gruß Daniel


 

Beiträge aus den Excel-Beispielen zum Thema "gibt es identische Zahlen in meiner Tabelle"