Hallo,
ich möchte eine Tabelle machen, wo per Formel abhängig vom Inhalt der Spalten 2-4 in Spalte 1 automatisch nummeriert wird. Diese Nummerierung soll sich automatisch anpassen, wenn ich irgendwo eine Zeile hinzufüge. Ein Beispiel:
Sp. 1 | Spalte 2 | Sp. 3 | Spalte 4
1.1.1 | VW | PKW | Golf
1.1.2 | | | Up
1.1.3 | | | Sharan
1.2.1 | | Bus | T5
1.2.2 | | | T6
2.1.1. | Mercedes | PKW | B200
2.1.2 | | | A160
2.2.1.| | LKW | Sprinter
Wenn irgendwo eine neue Zeile einfüge - beispielsweise LKW unter VW - stimmen die nachfolgenden Nummerierungen nicht mehr. Deshalb würde ich sie gerne per Formel automatisieren. Meine bisherigen Experimente sind leider gescheitert - geht das überhaupt per Formel?
Viele Grüße
Frank
Sub Autonummerierung() 'Variante mit mehr als 15 Ebenen und Nummern pro Ebene 1:9^9. Test xl2000 + 2010
Workbooks.Add xlWorksheet: [A1:D1] = Split("Trigger Kapitel Titel Kombi")
[A2:A21] = WorksheetFunction.Transpose(Split("1 1 1 1 0 0 -1 -1 1 0 1 0 -2 1 0 0 -2 1 0 -1"))
[C2:C21] = WorksheetFunction.Transpose(Split("Getränke Wasser abgefüllt still medium classic " & _
"Kranberger Bier Pils Weizen Kristall Hefe Wein rot weiß rosé Essen Vorspeise Nachspeise Fazit"))
ActiveWorkbook.Names.Add Name:="XX", RefersToR1C1:="=R[-1]C"
ActiveWorkbook.Names.Add Name:="Ebenen", RefersToR1C1:="=MAX(1,LEN(XX)-LEN(SUBSTITUTE(XX,""."",))+MIN(1,RC[-1]))"
ActiveWorkbook.Names.Add Name:="Wennfehler", RefersToR1C1:="=SUBSTITUTE(LEFT(" & _
"SUBSTITUTE(XX,""."",""-"",Ebenen-1),SEARCH(""-""," & _
"SUBSTITUTE(XX,""."",""-"",Ebenen-1))),""-"",""."")&MID(SUBSTITUTE(XX&0,"".""," & _
"REPT("" "",99)),Ebenen*99-98,99)+1&""."""
ActiveWorkbook.Names.Add Name:="Nummerierung", RefersToR1C1:= _
"=IF(ISERROR(Wennfehler),MID(XX,1,SEARCH(""."",XX)-1)+1&""."",Wennfehler)"
[B2].FormulaR1C1 = "=""2017.8.17.1.""": [B3:B21].FormulaR1C1 = "=Nummerierung"
[D2:D21].FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"
[E1] = 8: [E2:E21].FormulaR1C1 = "=REPT("" "",(LEN(RC[-3])-2)/2*R1C)&RC[-2]": End Sub
Hi
____|___A__|__________B__________|____C___|_____D____|_E|_F|_G|
1| | | | | | | |
2| | | | | | | |
3| | Spalte 2 | Sp. 3 | Spalte 4 | | | |
4| 1.1.1| VW | PKW | Golf | | | |
5| 1.1.2| | | Up | | | |
6| 1.1.3| | | Sharan | | | |
7| 1.2.1| | Bus | T5 | | | |
8| 1.2.2| | | T6 | | | |
9| 2.1.1| Mercedes | PKW | B200 | | | |
10| 2.1.2| | | A160 | | | |
11| 2.2.1| | LKW | Sprinter | | | |
A4 =ANZAHL2($B$4:B4)&"."&ANZAHL2(INDEX(C:C;MAX(($B$4:B4<>"")*ZEILE($A$4:$A4))):C4)&"."&ANZAHL2(INDEX(D:D;MAX(($B$4:C4<>"")*ZEILE($A$4:$A4))):D4) 'mit strg+shift+Enter abschließen
Hallo Elex,
vielen Dank für deine Mühe. Ich habe nun in Zelle A4
=ANZAHL2($B$4:B4)&"."&ANZAHL2(INDEX(C:C;MAX(($B$4:B4<>"")*ZEILE($A$4:$A4))):C4)&"."&ANZAHL2(INDEX(D:D;MAX(($B$4:C4<>"")*ZEILE($A$4:$A4))):D4)
als Matrixformel eingefügt. Das Ergebnis ist korrekt 1.1.1. Ziehe ich diese Formel aber nun runter, kommt
2.1.1
3.1.1
4.1.1
5.1.1
6.1.1
7.1.1
8.1.1
heraus. Habe ich etwas falsch gemacht?
Viele Grüße
Frank
Schnellschuss: Markiere Spalte B, drücke ENTF und trage Einträge ein, wo sie hingehören.
Hallo LCohen,
der Schnellschuss war ein Volltreffer! Nachdem ich alles gelöscht und von Hand neu eingetragen habe, hat die Formel genau das getan, was sie sollte. Ich kenne die verwendeten Formeln - aber es ist mir rätselhaft, wie sie hier als Matrixformel zusammenwirken. Das Maxium eines nichtleeren Bereichs in B mit der Zeilnummer aus A multiplizieren und dann durch eine Zelle C4 teilen... das verstehe ich nicht, aber es funktioniert! Vielen Dank an Elex!
Dir auch vielen Dank für das Makro. Es ist mir noch wesentlich rätselhafter und ich werde mal schauen, was ich davon verstehe. Aber in diesem Fall bin ich froh über eine Formellösung!
Viele Grüße
Frank
Hallo Günter,
ich habe versucht, ein Beispiel mit Tab2Html (v2.6.0) zu erstellen. In den Anleitungen fand ich den Hinweis, dass man diese Add-Ins am vertrauenswürdigen Speicherhort C:\Program Files (x86)\Microsoft Office\Root\Office16\Library\ speichern solle. Diesen gibt es bei mir aber nicht. Mein XL 2016 möchte sie als default unter C:\Program Files\Microsoft Office\root\Office16\1031 ablegen. Das habe ich dann gemacht.
Sobald ich das Add-In aktiviere, kommt die Fehlermeldung "Kompillierungsfehler im ausgeblendetem Modul: M_Zwischenablage_API
Dieser Fehler tritt häufig auf, wenn Code nicht mit der Versin, Plattform oder Architektur dieser Anwendung kompatibel ist. .... "
An dieser Stelle bin ich leider nicht weitergekommen ;-(
Viele Grüße
Frank
Hallo Frank
Zitat:dann durch eine Zelle C4 teilen... das verstehe ich nicht
Hier wird nicht durch C4 geteilt, sondern der Index für die Anzahlfunktion zusammengesetzt.
Verwende die Formelauswertung von Excel.
http://www.pcs-campus.de/office/excel/ex...uswertung/
In der Datei findest du unter anderem auch Beispiele zur Indexfunktion.
[
attachment=25080]
Gruß Elex
Das Makro ist ein Tabellenerstellungsmakro (oder Konstruktionsmakro), welches dem Forum einen Anhang erspart. Es baut Dir einmalig eine Tabelle, verwendet ab dann kein Makro mehr.
Das Resultat entspricht dem eines hierarchischen Inhaltsverzeichnisses und geht daher an Deiner Aufgabenstellung vorbei (obwohl datentechnisch absolut zu gebrauchen). Vor allem ist das automatische Zeilen-Einfügen gewährleistet.
Der "Trigger" ist für die Ebenenansteuerung. Verstehen kann man das nach der Konstruktion.
Hallo Elex,
vielen Dank für deine Erläuterungen. Ich bin froh, dass es funktioniert und erwarte keinesfalls, dass du deine Formel noch erklärst. Nur für den Fall, dass du noch Lust hast, möchte ich mein Verständnisproblem darstellen:
Ich habe mir die Anzahl2 Auswertung angeschaut, aber es hat weiterhin nicht "Klick" gemacht. Ich habe das nun hoffentlich fehlerfrei abgeschrieben, weil ich dort nicht kopieren konnte.
1 =ANZAHL2(INDEX(C:C;MAX(($B$4:B4<>"")*ZEILE($A$4:$A4))):C4)
2 =ANZAHL2(INDEX($C:$C;MAX(("VW"<>"")*ZEILE($A$4:$A4))):C4)
eigentlich sollte hier $B$4:B4<>"") ausgewertet werden. Gleichzeitig aber verändert sich C:C zu $C:$C . Was passiert da?
3 =ANZAHL2(INDEX($C:$C;MAX((WAHR)*(4))):C4)
4 =ANZAHL2(INDEX($C:$C;MAX((4))):C4)
und hier fällt dann plötzlich das "MAX((4))):" einfach so weg.
5 =ANZAHL2(INDEX($C:$C;C4)
die Matrix ist "$C:$C" und daraus wird die Zeile "C4" ausgewählt? Das kann ja nicht sein.
6 =ANZAHL2($C$4:C4)
Und das Ergebnis der Index-Formel ist dann "$C$4:C4"? Index gibt doch als Ergebnis den Wert einer Zelle zurück. Wieso hier einen Bezug?
Aber wie gesagt - ich erwarte keine Erklärung. Ich hätte es gerne verstanden - aber vielleicht ist es einfach zuweit jenseits meines XL-Niveaus ;-(
Viele Grüße
Frank
Hallo LCohen,
mir war nicht klar, dass es ein Konstruktionsmakro ist. Nun habe ich es ausprobiert, aber bekomme es nicht hin. In Spalte B kommt nur #NAME? weil XL den Namen "=Nummerierung" nicht erkennt.
Viele Grüße
Frank