SVERWEIS erzeugt übergroße Datei

Forumthread: SVERWEIS erzeugt übergroße Datei

Hans

Hallo Zusammen,
meine Excel 10 Datei bläht sich über SVERWEIS auf eine große Arbeitsmappe ungeheuer auf.
Mit der Formel
=WENN(A9"";SVERWEIS(A9;'[Datei1.xlsm]Positionen_2011'!$C:$AJ;34;0);"")
übernehme ich in 48 Zeilen dieser Tabelle Daten aus einer Datei "Datei1", die ca. 100.000 Zeilen und 44 Spalten hat.
Meine Exceldatei hat (inkl. einiger anderer Blätter) dann eine Größe von ca. 33 MB.
Ich ändere obige Formel in
=WENN(A9"";SVERWEIS(A9;'[Datei2.xlsx]BLP'!$A:$D;4;0);"")
und übernehme Daten aus einer Tabelle mit 11.000 Zeilen und 4 Spalten einer anderen Datei.
Wenn ich dann meine Arbeitsmappe abspeichere, ist sie "nur noch" 12 MB groß.
Anscheinend wird irgendwie ein größeres Datenvolumen nur über den SVERWEIS abgespeichert, wenn ich auf eine große, als auf eine kleine Datei referiere.
Da ich auf mehreren Tabellenblätter auf größere andere Dateien zugreife, kommen dann auch diese immer noch zu großen 12 MB zustande.
Kann ich das durch Änderung einer Konfiguration ändern?
Mit freundlichen Grüßen
Hans

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

AW: SVERWEIS erzeugt übergroße Datei

16.04.2012 11:35:20

Sheldon

Hallo Hans,
ist mir nicht bekannt. Ich löse das Problem, indem ich die Formel nur in der ersten Zeile stehen lasse, den Rest kopiere ich und füge ihn als Wert ein. So werden nur die Ergebnisse gespeichert, nicht aber der Bezug. Zum Aktualisieren öffne ich die Quelldatei jeweils und kopiere die Formel wieder in alle Zellen, um danach wiederum die Ergebnisse zu kopieren und als Werte einzufügen.
Gruß
Sheldon

SVerweis speichert ganze Fremdspalte

16.04.2012 12:57:48

Reinhard

Hallo Hans,
Frage noch offen da ich keine direkte Lösung habe aber etwas gebastelt was das Phänomen
zumindest teilweise erklärt.
Nachfolgender Code füllt eine neu erstellte Mappe in Blatt1 mit 26 Zeilen und 6 Spalten als
Datenquelle.
Das sieht in der Mappe so aus:

Tabellenblatt: K:\[kwsverweistest.xls]!Tabelle1
│ A │  B │  C │  D │  E │  F │
--┼---┼----┼----┼----┼----┼----┼
1 │ A │ B1 │ C1 │ D1 │ E1 │ F1 │
--┼---┼----┼----┼----┼----┼----┼
2 │ B │ B2 │ C2 │ D2 │ E2 │ F2 │
--┼---┼----┼----┼----┼----┼----┼
3 │ C │ B3 │ C3 │ D3 │ E3 │ F3 │
--┴---┴----┴----┴----┴----┴----┴
A1:F3
haben das Zahlenformat: Standard

Spalte A geht runter bis zum zeichen Z, Werte in B4:F26 enstprechend wie in zeilen 1-3
In der Mappe die den Code beinhaltet wird hinten ein neues Blatt angehängt, dort wird in
A1 ein F eingetragen als Suchkriterium für die Sverweisformeln in B1:F1 die in der neu erstellten Mappe den Suchbereich haben.
Nach Formeleintragung wird die neue Mappe gespeichert, geschlossen und dann gelöscht.
Gibt man nun in A1 einen anderen Suchbuchstaben ein so wird der immer noch durch SVerweis gefunden.
Ergo muß sich durch Svwerweis die ganzen Spalten der inzwischen gelöschten Mappe gemerkt worden sein.
Gruß
Reinhard


Option Explicit
Sub sverweistest()
Dim Zei As Long, Spa As Long, wks As Worksheet
Const Pfad As String = "K:\"                  'Anpassen, hinten \ nicht vergessen
Const Datei As String = "kwsverweistest.xls"  'Anpassen
Worksheets.Add after:=Worksheets(Worksheets.Count)
Set wks = ActiveSheet
Workbooks.Add
With ActiveWorkbook
With .Worksheets("Tabelle1")
.Range("A1:A26").Formula = "=CHAR(ROW(A65))"
.Range("B1:F26").Formula = "=CHAR(COLUMN()+64)&ROW()"
.Range("A1:F26").Value = .Range("A1:F26").Value
End With
.SaveAs Pfad & Datei
With wks
.Range("A1").Value = "F"
.Range("B1:F1").FormulaR1C1 = _
"=VLOOKUP(R1C1,[" & Datei & "]Tabelle1!R1C1:R26C6,Column(),0)"
End With
.Close
End With
Kill Pfad & Datei
End Sub

AW: SVERWEIS erzeugt übergroße Datei

16.04.2012 13:11:26

Rudi

Hallo,
aus der Hilfe:
Externe Verknüpfungswerte speichern Speichert Kopien der Werte, die in externen Dokumenten, die mit einem Excel-Arbeitsblatt verknüpft sind, enthalten sind. Nimmt ein Arbeitsblatt mit Verknüpfungen zu umfangreichen Bereichen in einem externen Dokument ungewöhnlich viel Speicherplatz in Anspruch oder dauert es sehr lange, bis es geöffnet wird, kann durch Deaktivieren des Kontrollkästchens Externe Verknüpfungswerte speichern der belegte Speicherplatz sowie die Zeit bis zum Öffnen des Arbeitsblatts reduziert werden.
Excel-Optionen-Erweitert, Beim Berechnen dieser Arbeitsmappe
Gruß
Rudi

AW: SVERWEIS erzeugt übergroße Datei

16.04.2012 13:35:05

Hans

Hallo Rudi,
danke sehr, aus meiner 12,7 MB großen *.xls-Datei ist nun eine 205 kB -Datei entstanden :-)
Hans

Infobox / Tutorial

SVERWEIS: Dateigröße reduzieren und effizient nutzen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Öffne deine Excel-Datei.
Identifiziere die SVERWEIS-Formeln, die auf große Datenmengen in einer anderen Datei zugreifen.
Optimiere die Formel:
- Verwende, wenn möglich, kleinere Datenbereiche oder Dateien. Beispiel:
```
=WENN(A9<>"";SVERWEIS(A9;'[Datei2.xlsx]BLP'!$A:$D;4;0);"")
```
Speichere die Datei und überprüfe die Dateigröße.
Aktiviere die Option, um externe Verknüpfungswerte nicht zu speichern:
- Gehe zu Datei > Optionen > Erweitert und deaktiviere das Kontrollkästchen für „Externe Verknüpfungswerte speichern“.
Speichere deine Datei erneut und schaue, ob die Größe reduziert wurde.

Häufige Fehler und Lösungen

Problem: Excel stürzt ab beim Öffnen großer Dateien.
- Lösung: Reduziere die Anzahl der SVERWEIS-Formeln, indem du die Ergebnisse in Werte umwandelst. Kopiere die Zellen und wähle „Inhalte einfügen > Werte“.
Problem: SVERWEIS speichert unnötige Daten aus einer anderen Datei.
- Lösung: Überprüfe die Formeln und verwende spezifischere Bereiche, um die Datenmenge zu minimieren.

Alternative Methoden

XVERWEIS nutzen: Wenn Du Excel 365 oder Excel 2021 verwendest, probiere XVERWEIS anstelle von SVERWEIS. Es hat den Vorteil, dass es flexibler ist und weniger Speicherplatz benötigt.
VBA zur Automatisierung: Du kannst VBA-Makros verwenden, um die Daten aus einer anderen Datei zu importieren und als Werte zu speichern. Ein Beispielcode könnte so aussehen:
```
Sub ImportAsValues()
  ' Dein Code hier
End Sub
```

Praktische Beispiele

SVERWEIS aus einer anderen Datei:
```
=SVERWEIS(A1;'[Daten.xlsx]Tabelle1'!$A:$C;2;FALSCH)
```
Diese Formel fragt die Daten aus der Datei „Daten.xlsx“ ab und gibt den Wert der zweiten Spalte zurück.
Daten in Werte umwandeln:
- Markiere die Zellen mit der SVERWEIS-Formel.
- Drücke Strg + C, dann Rechtsklick > Inhalte einfügen > Werte.

Tipps für Profis

Verwende benannte Bereiche: Das verbessert die Lesbarkeit der Formeln und kann dazu beitragen, die Dateigröße zu reduzieren.
Vermeide komplexe Verknüpfungen: Reduziere die Anzahl der Dateien, auf die verwiesen wird, um die Dateigröße zu minimieren.
Regelmäßige Bereinigung: Überprüfe deine Arbeitsmappen regelmäßig auf unnötige Verknüpfungen und Daten.

FAQ: Häufige Fragen

1. Warum wird meine Excel-Datei so groß, wenn ich SVERWEIS nutze?
SVERWEIS speichert oft Daten aus den referenzierten Dateien, was die Dateigröße erhöht.

2. Gibt es eine Möglichkeit, diese Größe zu reduzieren?
Ja, indem Du die Option „Externe Verknüpfungswerte speichern“ deaktivierst und die Formeln in Werte umwandelst.

3. Was ist der Unterschied zwischen SVERWEIS und XVERWEIS?
XVERWEIS ist flexibler und bietet erweiterte Funktionen. Es ist in neueren Excel-Versionen verfügbar und benötigt weniger Speicherplatz.

Beliebteste Forumthreads (12 Monate)

SVERWEIS erzeugt übergroße Datei

Forumthread: SVERWEIS erzeugt übergroße Datei

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

SVERWEIS: Dateigröße reduzieren und effizient nutzen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

SVERWEIS erzeugt übergroße Datei

Forumthread: SVERWEIS erzeugt übergroße Datei

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

SVERWEIS: Dateigröße reduzieren und effizient nutzen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread