Nov 15, 2014

2623

Excel Tabelle

Hallo
Ich habe ein Problem mit einer Tabelle
In der ersten Tabebelle hab ich Namen aufgelistet und 1 Zelle zum markieren eingefügt.
Die zweite Tabelle soll nun Namen der ersten Tabelle übernehmen wenn in der ersten Tabelle ein x in der Markierung steht.
Dies muss fortlaufend in der zweiten automatisch eingetragen werden.
Kann mir einer einen Tipp gegen wie ich das umsetzen kann.

Please also mark the comments that contributed to the solution of the article

Content-Key: 254954

Url: https://administrator.de/contentid/254954

Printed on: April 24, 2024 at 22:04 o'clock

17 Comments

Latest comment

1. Tabelle mit den Namen im folgenden Quelldaten
2. Tabelle mit der Auswahl im folgenden Zieldaten

3. Namen aus der Quell in die Zieltabelle übernehmen
Bsp. "=WENN(Quelldaten!A1=0;"";Quelldaten!A1)" in Zeile 1 schreiben
Spalte komplett markieren dann Str+U um die Formel nach unten zu kopieren

4. gleiches für die Spalte mir den Markierungen

5. Funktion Sortieren und Filtern und und in der ersten zeile den nun erschienen harken klicken um dort den haken links neben leere zu entfernen

6. optional die spalte rechts von den Datenspalten markieren (komplett)
7. Strg+Umsch+Pfeil Rechts um alle rechts davon zu markieren
8. Strg+8 um alle markierten spalten auszublenden

Moin samsamsam,

willkommen im Forum.

Angenommen, deine erste Tabelle hiesse "Quelltab", die Namen stehen in Spalte A ( zB Quelltab!A$1:A$100) und die x-Markierungen in Quelltab!B$1:B$100...

Dann suche dir in der Zieltabelle eine freie Zelle, trage da die Formel ein:

=ZÄHLENWENN(Quelltab!$B$1:$B$100;"x")

...und nenne diese Zelle "Anzx" (für Anzahl der mit "x" markierten Zeilen).
oder definiere diese "AnzX" direkt als Variable, falls du keine Zelle in deiner Zieltabelle opfern willst.

In der Wunschspalte und der ersten Wunschzeile deiner zweiten Tabelle gibst du ein:

=WENN(Anzx<=ZEILE(A1);"";INDEX(Quelltab!A$1:A$100;KGRÖSSTE((Quelltab!$B$1:$B$100="x")*(ZEILE($B$1:$B$100));Anzx-ZEILE(A1));1))

und schliesst diese Formel mit Ctrl-Shift-Enter ab.
Dann kopierst du das Gesummse nach unten soweit du willst.

P.S.
Ein wenig lesbarer wird die Formel, wenn du deinen Bereich mit den Namen (also im Beispiel Quelltab!A$1:A$100) "Namen" nennst und den (gleichlangen) Bereich mit den x-Werten "xe".
Dann lautet die Formel:

=WENN(Anzx<=ZEILE(A1);"";INDEX(namen;KGRÖSSTE((xe="x")*(ZEILE(xe));Anzx-ZEILE(A1));1))

Dann lässt es sich wenigstens auf einen Blick lesen und verstehen.

Grüße
Biber

Hallo zusammen!

Eigentlich sollte es ja reichen, in der Spalte mit den x'en einen Filter zu setzen und anschließend die ganze Spalte mit den angezeigten Namen zu kopieren, falls das dann überhaupt noch nötig ist?

Grüße Dieter

Hallo,

also für alle die genau so unerfahren sind wie ich xD:

Hier Informationen zur Index Funktion:
http://office.microsoft.com/de-de/excel-help/index-funktion-HP010069831 ...

Selbiges zur kgrößte Funktion:
http://office.microsoft.com/de-de/excel-help/kgrosste-HP005209151.aspx

Genau dafür bekomme ich aber einen Fehler: #Zahl!

=KGRÖSSTE((xe="x")*(ZEILE(xe));Anzx-ZEILE(A1))

Laut Anleitung wenn k über die Matrix hinaus geht.

xe="x" ergibt jeweils 0 oder 1
ZEILE(xe) jedoch immer 1
Damit ist die Matrix für KGrößte höchtens 1

k geht aber über 1 hinaus.

Ich gehe davon aus das der erste Parameter das letzte auftreten der x Markierung anzeigen soll.
Verstehe aber nicht ganz wie das über Zeile(Spaltenamarkierung) Funktionieren kann.

Gruß aus Bayern
SweetOne

Hallo SweetOne!

Hast Du die Biber's Array-Formeln auch mit <Ctrl-Shift-Enter> eingegeben?

Grüße Dieter

Moin SweetOne,

der Wert #ZAHL kann eigentlich nur auftreten, wenn die Formel "nur" mit Enter statt mit Ctrl-Shift-Enter eingegeben wurde, wie auch Eintachsfliege schon geschrieben hat.

Wenn die Formel "richtig", also als Arrayformel eingegeben wird, dann wird sie in der Formelansicht dargestellt mit geschweiften Klammern drumherum, also:

 
{=WENN(AnzX<=ZEILE(A1);"";INDEX(namen;KGRÖSSTE((xe="x")*(ZEILE(xe));AnzX-ZEILE(A1));1))}

Die geschweiften Klammern erscheinen automatisch und dürfen nicht mit eingegeben werden.

Außerdem - wie auch oben eher beiläufig erwähnt - die Vektoren "namen" und "xe" müssen gleich lang sein, also zB von A1:A100 und von B1.B100 definiert sein.

Zum Thema "ZEILE(xe) jedoch immer 1"... Zeile(xe) ist gleich Zeile(Quelltab!B1:B100), also nicht immer gleich 1, sondern 1..100.

Die Matrix "(xe="x")*(ZEILE(xe)" kannst du dir zum Verständnis wie folgt visualisieren:
Gib in einer freien Spalte deiner Zieltabelle in die erste Wunsch-Zeile ein

=(Quelltab!B1="x")*ZEILE()

... und kopier die Formel nach unten.

Dann siehst du die Matrix, die in meiner Matrixformel durchgeflöht wird.

Grüße
Biber

Hallo Ihr beiden,

habe mich gerade noch mal nen Stündchen damit beschäftigt deine Funktion nachzuvollziehen.
Dabei weis ich ein paar Wochen überhaupt, dass Excel Matrizen Rechnen kann.. und dann gleich sowas xD.
Aber man lernt ja gerne dazu.

Nachteil: Leider beschäftigt Excel diese Funktion doch sehr...
...zwingt einen ja fast dazu die automatische Berechnung auszuschalten.

Wesentlich mehr jedenfalls, als die Funktion zum Filtern wenn die Datenmenge größer ist.

Gruß und schönen Sonntag noch
SweetOne

Moin SweetOne,

ja, Matrixformeln beinhalten ja immer ein paar mehr Rechenoperationen und kosten entsprechend Ressourcen.

Mit drei Grundregeln lässt sich allerdings etwas gegensteuern

"konstante" Teilergebnisse nur 1x berechnen und auslagern, so wie ich es mit der "Anzahl x-Werte"/AnzX getan habe

den Bereich mit Arrayformeln so klein halten wie möglich/nötig.

In dem obigen Beispiel "Einige der Namen sind mit einem x markiert" heisst das doch: Wenn ich 100 Namen habe, dann rechne ich damit, dass 20 oder 30 Namen markiert sind, mehr nicht. Also brauche ich die Arrayformel in der Zieltabelle auch nur in 20 oder 30 Zellen runterkopieren, aber nicht 100x.

Arrayformeln gar nicht kopieren, sondern nur 1x in der Tabelle halten (das aber erst, wenn du mit Array-Formeln sicher umgehen kannst).

Beispiel; Markiere in der Zieltabelle einen Spaltenbereich, zB Spalte D2:D21.
Schreibe als Formel

=WENN(anzX<=ZEILE(xe);"";INDEX(namen;KGRÖSSTE((xe="x")*(ZEILE(xe));anzX-ZEILE(xe));1))  

und schicke es mit Ctrl-Shift-Enter ab.

Dann wird nur eine einzige Formel berechnet statt vorher 20 oder 30

Grüße
Biber

Ich war am Wochenende weg und kann mich erst jetzt damit beschäftigen und wollte mich schon mal für eure Bemühungen bedanken.

Moin samsamsam,

war es das jetzt oder kommt noch ein Feedback?

kann doch nicht sein, dass du Samstag früh eine Frage stellst, dann ins Wochenende fährst und bis Mittwoch keine Zeit hattest, die Lösungsvorschläge von drei Kommentierern mal zu testen.

Grüße
Biber

Danke für eure Arbeit

Leider komme ich so nicht weiter, da ich wohl ungeschickt gefragt habe.

Mein Problem

ich besitze 5 Tabellen in der Namen, die in der ersten Spalte eingetragen sind, automatisch in vierer Gruppen gelost werden. (Tennis Herren Einzel, Herren Doppel
Damen Einzel, Damen Doppel und Mix).

In der zweiten Tabelle sind alle 50 Namen in der ersten Spalte fest eingetragen. Hinter den Namen gibt es 5 Spalten, die mit einem X für mitmachen (siehe Gruppen Tennis) gekennzeichnet werden können.
Die Namen sollen dann automatisch den 5 Tabellen zugeordnet werden.

PS
Ich habe mich sehr wohl mit den Lösungen beschäftigt. Da ich aber nicht unbedingt Meister in Excel bin, versuche ich natürlich erst einmal selber weiter zu kommen bevor ich wegen jeder Kleinigkeit nachfrage.
Das kann schon mal ein paar Tage dauern.

Ich freue mich sehr über eure Hilfe mit der ich so schnell gar nicht gerechnet habe.

Danke Danke

Tja, samsamsam,

ich kann da leider nur die Lösung für ein Tabellenblatt liefern.
Nimm die für das Tabellenblatt "Mix" aus Gender-Gründen.

Vielleicht kommen ja noch vier andere Lösungen für Herren-Einzel bis Damen-Doppel.

Viel Glück
Biber

P.S. Ich bin raus.
P.P.S. Ist "Herren-Doppel" wirklich eine Sportart?? Ich dachte immer... ach egal.

Hallo samsamsam!

Deine Beschreibung habe ich irgendwie nicht so ganz verstanden, aber hier mal ein VBA-Code, der aus der Namens-Tabelle (Bild 1) die Tabellen 1-5 (Bild 2) mit den x-Namen befüllt:

Option Explicit

Private Const SheetHE = "Tabelle1"      'Tabelle Herren-Einzel  
Private Const SheetHD = "Tabelle2"      'Tabelle Herren-Doppel  
Private Const SheetDE = "Tabelle3"      'Tabelle Damen-Einzel  
Private Const SheetDD = "Tabelle4"      'Tabelle Damen-Doppel  
Private Const SheetMX = "Tabelle5"      'Tabelle Mix  
Private Const SheetNL = "Tabelle6"      'Tabelle Namensliste  

Private Const ColNL = 1                 'Spalte(A) Namensliste  
Private Const ColHE = 2                 'Spalte(B) Herren-Einzel  
Private Const ColHD = 3                 'Spalte(C) Herren-Doppel  
Private Const ColDE = 4                 'Spalte(D) Damen-Einzel  
Private Const ColDD = 5                 'Spalte(E) Damen-Doppel  
Private Const ColMX = 6                 'Spalte(F) Mix  

Private Const RowStart = 2              'Daten ab Zeile 2 (Überschrift in Zeile 1)  

Sub CopyNamesList()
    Dim aWks As Variant, sWks As Variant, aCol As Variant, i As Integer

    aWks = Array(SheetHE, SheetHD, SheetDE, SheetDD, SheetMX)
    aCol = Array(ColHE, ColHD, ColDE, ColDD, ColMX)
    
    For Each sWks In aWks  'Inhalte löschen  
        Sheets(sWks).Rows(RowStart).Resize(Sheets(sWks).UsedRange.Rows.Count).ClearContents
    Next

    With Sheets(SheetNL)
         For i = 0 To UBound(aWks)
            .AutoFilterMode = False
            .Columns(aCol(i)).AutoFilter Field:=1, Criteria1:="x"  
            .Columns(ColNL).Copy Sheets(aWks(i)).Range("A1")  
         Next
        .AutoFilterMode = False
    End With
End Sub

Bild 1:

Bild 2:

Ansonsten bin ich auch raus

Grüße Dieter

Hallo Dieter

Vielen Dank für deine Hilfe.

das ist genau das was ich brauche.

Hallo Dieter
Ich habe noch ein Problem mit deinem VBA Code.

folgender Fehler wird gemeldet:

Laufzeitfehler 9
Index außerhalb des gültigen Bereichs.

woran kann das liegen.
Ich habe an deinem Code nichts verändert.

könntest du mir einen Tipp geben.

Hallo samsamsam!

Kann es sein, dass Deine Tabellenblätter nicht Tabelle1-6 heißen und Du die Tabellennamen im VBA-Code nicht angepasst hast?

Falls Nein, an welcher Stelle bleibt der Code hängen (gelbe Markierungszeile)?

Grüße Dieter

Hallo

Ich habe das Problem gefunden
Die Namenstabelle war nicht Tabelle6

Danke noch mal für alles

German Question Microsoft Office Microsoft

Hotly discussed

Check of ZFW Firewallgleixnerd - 5 Comments

Wireguard VPN on UDM Pro behind Fritzbox - Handshake did not completejstricker - 3 Comments