Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1308to1312
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Zeilenabfrage

Zeilenabfrage
16.04.2013 10:37:18
Timo
Hallo zusammen,
ich habe eine Datenbank mit verschiedenen Datensätzen (Datensatz = ganze Zeile z.B. A3:CS3 mit je einem Merkmal in jeder Zelle zu diesem Datensatz) u. möchte einen Auszug aus dieser Datenbank in die Tabelle 2 in Abhängigkeit von dem Merkmal Tabelle 2 Zelle B1, welches in der Tabelle 1 in den Zellen CR2:CR1000 steht auslesen. Das Auslesen soll sich auf die Spalten A und O-AB beschränken u. alle Datensätze nacheinander aufgelistet anzeigen.
Vielen Dank im Voraus.

Die Datei https://www.herber.de/bbs/user/84891.xlsx wurde aus Datenschutzgründen gelöscht

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zeilenabfrage
16.04.2013 10:53:21
Klaus
Hi,
Formelzeile runter kopieren, falls benötigt.

Die Datei https://www.herber.de/bbs/user/84892.xlsx wurde aus Datenschutzgründen gelöscht


wird bei großen Listen irgendwann schnarchlangsam. Teste mal aus, ob es für dich reicht. Wenn nicht meld dich nochmal, dann machen wir eine rasend schnelle VBA-Lösung als Alternative.
Grüße,
Klaus M.vdT.

ohne bereich.verschieben AW: Zeilenabfrage
16.04.2013 11:50:45
Klaus
Hallo,
hier nochmal eine verbesserte Formel-Version, in der ich das langsame* BEREICH.VERSCHIEBEN gegen eine schnellere Variante mit INDEX ausgetauscht habe. Bei 6 Zeilen merkst du keinen Unterschied, bei 1000 oder mehr wirds interessant.
https://www.herber.de/bbs/user/84895.xlsx
Grüße,
Klaus M.vdT.
*genau genommen ist die volatile Funktion nicht per se langsam, sondern löst nur zu häufig Neuberechnungen aus - was im ganzen Excel langsamer macht.

Anzeige
Kommentar @ Thorsten
16.04.2013 11:14:00
Klaus
Hi Thorsten,
du hast die Überschriften nicht mitgenommen. Danach wurde zwar strenggenommen auch nicht gefragt, aber die werden bestimmt trotzdem gebraucht.
Mein VBA Code sähe so aus:
(Zeilen angepasst, Überschriften in Zeile 2 vorausgesetzt)
Sub sbAuslesen(ByVal ausleser As Variant)
Dim rTMP As Range
Set rTMP = ActiveCell
Dim liRow As Integer
Application.ScreenUpdating = False
If Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row  1 Then
Sheets(2).Rows("3:" & Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row + 1).Delete
End If
With Sheets(1)
liRow = .Cells(.Rows.Count, 1).End(xlUp).Row
If .AutoFilterMode Then .Cells.AutoFilter
.Range(.Cells(1, 96), .Cells(liRow, 96)).AutoFilter
.Range("$CR$1").AutoFilter Field:=1, Criteria1:=ausleser
.Range("A2:A" & liRow).SpecialCells(xlCellTypeVisible).Copy
Sheets(2).Range("A3").PasteSpecial xlPasteValues
.Range("O2:AB" & liRow).SpecialCells(xlCellTypeVisible).Copy
Sheets(2).Range("B3").PasteSpecial xlPasteValues
.Cells.AutoFilter
End With
rTMP.Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Durch das temporäre setzen des Autofilters spare ich mir die Schleife und die X Kopiervorgänge. Bei der gegebenen Liste ist das egal, aber ab ein paar tausend Einträgen bringt das einen guten Geschwindigkeitsvorteil.
Grüße,
Klaus M.vdT.

Anzeige
AW: Kommentar @ Thorsten
16.04.2013 11:17:04
Oberschlumpf
Hi Klaus
Ja, stimmt, die Mitnahme der Überschriften war nicht Bestandteil der Erstfrage, und deswegen hab ich sie auch nicht berücksichtigt. ;-)
Deine Autofilterlösung wird bei vielen 1000 Zeilen bestimmt die schnellere sein.
Ciao
Thorsten

noch mal Formeln
16.04.2013 11:14:10
Erich
Hi Timo,
hier noch eine Formellösung, mit etwas abgewandeltem Beispiel (wegen besserer Darstellbarkeit hier)
und noch ohne Fehlerbehandlung:
Tabelle1

 D
1Version:
2x
3xx
4xxx
5xxxx
6xxx


Tabelle2

 ABC
1Version:xxx4

Formeln der Tabelle
ZelleFormel
C1=VERGLEICH(A1;Tabelle1!A1:CS1;0)


Tabelle3

 ABCD
1300xxx
2500xxx
3#ZAHL!   
4#ZAHL!   
5#ZAHL!   

Formeln der Tabelle
ZelleFormel
A1{=INDEX(Tabelle1!$A$1:$CS$6;KKLEINSTE(WENN(INDEX(Tabelle1!$A$2:$CS$6;;mySp)=Tabelle2!$B$1;ZEILE($2:$6)); ZEILE()); SPALTE())}
A2{=INDEX(Tabelle1!$A$1:$CS$6;KKLEINSTE(WENN(INDEX(Tabelle1!$A$2:$CS$6;;mySp)=Tabelle2!$B$1;ZEILE($2:$6)); ZEILE()); SPALTE())}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen
Namen in Formeln
ZelleNameBezieht sich auf
A1mySp=Tabelle2!$C$1
A2mySp=Tabelle2!$C$1

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
Nice, Erich! aber ...AW: noch mal Formeln
16.04.2013 11:26:19
Klaus
Hallo Erich,
erstmal aus meiner "Ich-mag-komplexes-Excel" Sicht:
das ist eine tolle Lösung! Find ich super.
Jetzt aus meiner "Ich-Arbeite-Mit-DAU-Anwendern" Sicht:
meinst du wirklich, eine komplexe Matrixformel über eine Datenbank mit 1000 Einträgen zu knallen, ist eine so gute Idee? Es wird nach 15 Spalten Auswertung gefragt, im Extremfall (1000 verschiedene "Herstellabteilung" ebenso mögliche wie 1 für alle 1000 Einträge) erzeugt deine Lösung 15.000 Matrixformeln ... freut sicherlich die CPU-Hersteller, das sorgt für Absatz :-)
Davon ab glaube ich nicht, dass ein "Basiskentnisse in Excel"-User dein Beispiel abstahieren und selbstständig einbauen kann. Da täte ich mich ja schon schwer dran :-)
Oh, ich sehe gerade, dass ich selbst "Bereich.Verschieben" in meiner Formellösung benutzt habe. Ich mache es also selbst nicht besser :-)
Grüße,
Klaus M.vdT.
Nachtrag: Um Missverständnissen vorzubeugen: Das soll kein anpinkeln oder "ich-bin-besser" sein, sondern eine fachliche Diskussion auslösen.

Anzeige
@Klaus
16.04.2013 12:24:25
Erich
Hi Klaus,
so toll und super finde ich diese Matrixformel gar nicht - eher normal, wenn du bei excelformeln.de schaust...
Dass Timo in der Lage sein wird, die kleine Formel in Tab2 zu kopieren, einen Namen zu erstellen und
dann die Formel in Tab3 zu kopieren, glaube ich schon.
Und dass ich mit dem Beispiel "Version:" in eine Spalte weit links gegangen bin, sollte auch kein Problem sein.
Das mit den vielen Datensätzen hatte ich nicht gesehen.
Ansonsten sehe ich gar keinen Diskussionsbedarf - wir sind uns da doch völlig einig! :-)
Mit Rudis Code z. B. wird das Problem schnell gelöst. :-)
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: @Klaus
16.04.2013 15:29:47
Klaus
Hi,
Mit Rudis Code z. B. wird das Problem schnell gelöst. :-)
genau das sehe ich eben nicht so. Sicherlich ist Rudi's Ansatz nochmal schneller und ressourcenschonender als unsere beiden Lösungen. Aber ich behaupte mal, den allermeisten Usern ist mit einer weniger performanten Lösung, die sie aber nachvollziehen können, mehr geholfen. Hätte ich die o.g. Datei im Einsatz und es würde sich nur eine Zeile / Spalte ändern, ich währ mit Rudis Code aufgeschmissen. Und ich versteh bestimmt etwas mehr von VBA und Excel als Timo, aber halt deutlich weniger als ein Rudi M., ein Luc oder ein Nepumuk.
Andererseits .. wenn ich mir ansehe, wie viele Threads hier um blind kopierte VBA-Fragmente gehen bei denen der TE nichtmal die Kommentare gelesen hat ...
Naja, bis Timo wieder hier postet ist das eh egal. Warten wirs ab!
Grüße,
Klaus M.vdT.

Anzeige
AW: @Klaus
16.04.2013 16:10:00
Rudi
Hallo,
das seh ich anders.
Mir ist das Handling der Filter viel zu kompliziert. Da klapper ich lieber ein Array ab und bau ein neues auf, das ich als Ergebnis ausgebe.
Gruß
Rudi
btw: Mein Code funktioniert mit der Beispielmappe nicht, da BE:BF komplett leer sind und somit die dahinter liegenden Spalten nicht zu Cells(1,1).CurrentRegion gehören.

noch ein Code
16.04.2013 11:59:26
Rudi
Hallo,
Sub aaa()
Dim arrTmp(1 To 15), arrDB, oDict As Object
Dim i As Integer, j As Integer, strMatch As String
Set oDict = CreateObject("scripting.dictionary")
arrDB = Sheets(1).Cells(1, 1).CurrentRegion
arrTmp(1) = arrDB(1, 1)
strMatch = Sheets(2).Cells(1, 2)
'Überschriften
For j = 15 To 28
arrTmp(j - 13) = arrDB(1, j)
Next
oDict(1) = arrTmp
'Daten
For i = 2 To UBound(arrDB)
If arrDB(i, 96) = strMatch Then
arrTmp(1) = arrDB(i, 1)
For j = 15 To 28
arrTmp(j - 13) = arrDB(i, j)
Next
oDict(oDict.Count + 1) = arrTmp
End If
Next i
'Eintragen
If oDict.Count > 1 Then
With Sheets(2).Cells(3, 1)
.CurrentRegion.Clear
.Resize(oDict.Count, 15) = _
WorksheetFunction.Transpose(WorksheetFunction.Transpose(oDict.items))
End With
End If
End Sub

Gruß
Rudi

Anzeige
Hallo Timo, ....
18.04.2013 15:30:03
Klaus
... schade dass du dich hier nicht mehr meldest.
Wir hatten ja schon ein paar interessante Lösungsvorschläge gesammelt, und es hätte uns (die Helfer) sicher gefreut zu hören, welchen du bevorzugst.
Aber so ganz ohne Rückmeldung ist das eher frustrierend.
Grüße,
Klaus M.vdT.

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige