Spalten index der ersten nicht leeren zelle in einer zeile herausfinden
Ich hänge schon seit Stunden an einem Excel-Problem(oder Open Office Calc)
Um das zu lösen muss ich den Spaltenindex der ersten nicht leeren Zelle herausfinden.
Kann mir da jemand eine Formel zu geben?
3 Antworten
Hier ein kleines Makro zum Suchen der ersten freien Zelle in Spalte A mit Ausgeabe in einer MsgBox. Musst du für deine Sache anpassen.
Gruß Monkee
Sub ErsteFreieZelleInSpalteA()
Dim s As String
Dim i As Long
With ActiveSheet
i = 0
Do
i = i + 1
s = Cells(i, "A")
If Len(s) = 0 Then
Cells(i, "A").Activate
MsgBox ActiveCell.Offset.Address
Exit Do
End If
Loop While i < 65535
End With
End Sub
kommen ab der ersten gefüllten nur noch gefüllte und bis wohin? (ich nehme mal Zeile L = Spaltenindex12 als maximal mögliche Spalte für den ersten Eintrag an, bis dahin müssten aber ab der ersten gefüllten alle weiteren gefüllt sein).
Wenn Du das bestätigen kannst, gibt es eine Formel, ansonsten musst du auf ein Makro zurückgreifen. (oder extrem hoch verschachtelte Abfragen durchführen). Angenommen Du suchst in Zeile 5, dann die Formel:
=ADRESSE(5;12+1-ZÄHLENWENN(A5:L5;"<>"))
Der Spaltenindex alleine ist 13-ZÄHLENWENN(A5:L5;"<>")
lies: Adresse(Zeile5;Spalte 13-Zählenwenn(Bereich;"ungleich leer"))
Der Inhalt dieser ersten Zelle ist
=indirekt(ADRESSE(5;12+1-ZÄHLENWENN(A5:L5;"<>")))
es gäbe noch weitere (viel kompliziertere Möglichkeiten, deshalb schau erst mal, ob Du damit zurechtkommst (oO oder xl?) xl-VBA-Makros laufen in oO angeblich immer noch nicht, DER Grund, warum ich immer noch in xl rumfuhrwerke.)
Eine der Möglichkeiten liefe über
=Zelle("Adresse";Spaltenziffer→Buchstabe&":5")
Aber wirklich nur, wenn Adresse(...) in oO nicht tutet!
verschachtelte wenn-Abfrage:
=WENN(A5<>"";1;WENN(B5<>"";2;WENN(C5<>"";3;WENN(D5<>"";4;WENN(E5<>"";5;WENN(F5<>"";6;"..."))))))
muss ergänzt werden bis zur maximal möglichen Spalte. Ist zwar Arbeit, aber lösbar.
Bis xl2003 ist da aber nach 8 Klammerebenen Schluss, in 2007 geht mehr (k.A., wieweit)
Die Adresse bekommst Du wieder mit
=Adresse(5;WENN(A5<>"";1;WENN(B5<>"";2;WENN(C5<>"";3;WENN(D5<>"";4;WENN(E5<>"";5;WENN(F5<>"";6;"...")))))))
und den Inhalt mit
=indirekt(Adresse(...;...))
Für Zelle("Adresse",A1) musst Du den Spaltenindex erst noch in einen Buchstaben umwandeln:
=Zeichen(WENN(A5<>"";1;WENN(B5<>"";2;WENN(C5<>"";3;WENN(D5<>"";4;WENN(E5<>"";5;WENN(F5<>"";6;"..."))))))+64)
oder zB direkt den Wert holen mit
=indirekt(Zeichen(...)&5)
Das sind die Formellösungen, wie sie mir einfallen, wahrscheinlich kann man das mit einer Matrixformel oder Summenprodukt(Logik-Ausdruck)-Formel kürzer darstellen, aber dafür hab ich jetzt nicht die Muße.
Leider verstehe ich Dein angepeiltes System nicht ganz, zB fehlt mir die Verknüpfung vom Mitarbeiter mit der Abteilung bzw dem Meister.
Die Abfrage <>"" geht so wirklich nicht, aber versuchs mal mit dem Faktor:
=zählenwenn(Bereich, evtl Einzelzelle;"<>")*irgendwas
(da liefert jede leere Zelle 1 bzw nichtleere 0) oder
=zählenwenn(Bereich, evtl Einzelzelle;"*") , das zählt jeden Text als 1 (beachte die andere Setzung der AnfZch!). Vielleicht kannst Du damit was anfangen.
Das Problem ist eben immer, dass man bei abstrakten Beschreibungen in der Vorstellung total daneben liegen kann!
Anstatt des SVerweis würde ich Dir Folgendes empfehlen
Formelkombination index/Vergleich
=index(C2:C99;Vergleich ("XXX";A2:A99;0)
zum Verständnis:
Vergleich(Was, Wo;Typ) sucht im zB Bereich A2:A99 nach zB XXX und meldet die n-te Zelle als ersten Fund.
Index geht nun in der ersten (hier einzigen) Spalte des Bereichs zB C2:C99 zu dieser nten Zelle und meldet den dort gefundenen Wert zurück.
ist viel flexibler als der SVerweis!:
1.muss die Leitspalte nicht links stehen,
2.Kopiert sich der Versatz relativ mit (zu index gehörige Spalte), die Spalte in Vergleich(was,wo;Typ) kann man $ schreiben ($A2:$A99) und
3.kann man auch relativ zu dem durch index gefundenen Wert versetzt was suchen: entweder in der gleichen Spalte mit ;Vergleich()±n; oder durch Versatz der Index-Spalte. Drei m.E. unschätzbare Vorteile!
Aber nach diesem Exkurs: Du kannst mir ja (per Freudschaftsantrag oder Kompliment) eine E-Mail-Adresse zukommen lassen, ich schreib Dir dann dahin zurück und Du hängst mir eine BeispielsDatei an (jeweils 3-5 Zeilen sollten reichen), ich bearbeite das dann im xl direkt. Bitte als xls, nicht als xlsx! (bin häufiger an alten Rechnern).
für eine einzelne Zeile geht es so: {=VERGLEICH(WAHR;1:1<>"";0)}
siehe http://www.excelformeln.de unter Tabellennavigation
Hmm, entwerder versteh ich das nicht oder es ist noch nicht des Rätsels Lösung.
Um einmal etwas konkreter zu werden: Es geht um eine zusammenfassende Liste zur Planung von Mitarbeitereinsätzen. Bisher wurde eine Liste geführt, die sehr unübersichtlich ist, wenn man von mehr als 100 Mitarbeiter ausgeht.
Aufbau:
VornameMA NachnameMA KW1 KW2 KW3 usw.
und dann Einträge mit jeweils einem Kürzel des Abteilung in der KW, in der der Mitarbeiter in der Abteilung ist.
Dazu gibt es dann ein Tabellenblatt, in dem die Kürzel der Abteilungen den Meistern zugewiesen werden.
Dieses System nervt mich, weil es totawl unübersichtlich ist.
Dies möchte ich jetzt in eine "filterbare" Liste übertragen: Meister/Abteilung KW1 KW2 KW3 usw.
Dann soll in der Spalte A der Name des Meisters und in der KW, in der ein Mitarbeiter in seiner Abteilung ist, soll der Name des MA erscheinen.
Bisher habe ich mit einer "Hilstabelle" gearbeitet in der ich eine Wenn-Funktion mit SVerweis verknüpft habe und dann dieses Ergebnis übertragen habe. Leider funktioniert das nicht, da man beim Spaltenindex nicht das Argument "nicht leer" also <>"" eingeben kann.
Leider habe ich keinerlei Erfahrung mit Makros und VBA.
Vielleicht hat ja aber jemand eine Lösung. Ich wäre sehr dankbar, ehrlich.