Ich arbeite in Excel mit einer Liste von BKP-Nummern (Baukostenplan Schweiz), die wie…
- User
Ich arbeite in Excel mit einer Liste von BKP-Nummern (Baukostenplan Schweiz), die wie folgt strukturiert sind: • Hauptgruppen sind z. B. "23" (wenn vorhanden) oder "230" (wenn keine 23 vorkommt). • Untergruppen sind detaillierter, z. B.: ◦ "230" → "230.0", "230.1", "230.2" usw. ◦ "230.0" → "230.01", "230.02" usw. ◦ "230.01" → "230.01.0", "230.01.1" usw. Datenstruktur in Excel: • BKP-Nummern stehen als Text in den Zellen B4:B500 (wegen enthaltenen Punkten). • In Spalte F stehen die Kosten als Zahl, korrekt formatiert (ohne Text, keine Fehler, CHF). • Ich möchte in Spalte G (z. B. G4 bis G500) eine Formel ohne Hilfsspalten verwenden, die abhängig vom jeweiligen Eintrag in Spalte B (z. B. B4) folgendes tut: Ziel der Formel: 1 Wenn B4 eine Hauptgruppe ist (z. B. "23" oder "230"): → Summiere alle Werte aus Spalte F, deren BKP-Nummer in Spalte B mit diesem Wert beginnt, z. B. "230", "230.1", "230.01.1", "239.99.99" usw. 2 Wenn B4 eine Untergruppe ist (z. B. "230.0", "231.0", "232.1"): → Summiere alle Werte aus Spalte F, deren BKP-Nummer in Spalte B mit diesem Wert + einem weiteren Punkt und Zeichen weitergeht, z. B. "231.01", "231.01.0", "231.99.99" usw. 3 Fehlerfrei bleiben: → Die Formel soll keine Fehler anzeigen (z. B. #WERT!, Datentypfehler), auch wenn Spalte B leer ist oder Spalte F ungültige Werte enthält. 4 Wenn Zelle B leer ist: → Kein Ergebnis anzeigen, also leer bleiben. Weitere Bedingungen: • Die Formel muss ohne Hilfsspalten funktionieren. • Keine Konvertierungen wie B4*1 oder WERT() verwenden, da BKP-Nummern wie "231.1.1" sonst zu Fehlern führen. • Die Formel darf mit Textfunktionen wie LINKS() oder SUCHEN() arbeiten. • Sie muss in jeder Zeile funktionieren und sich immer auf die entsprechende Zelle in Spalte B (z. B. B4, B5...) beziehen. Beispiel zur Verdeutlichung: Wenn in B4 der Wert "230" steht, soll die Formel alle Kosten aus Spalte F summieren, deren zugehörige BKP-Nummer in Spalte B mit "230" beginnt, z. B. "230.1", "230.01.0", "230.99.99" etc. Wenn in B4 der Wert "230.0" steht, soll die Formel alle Werte in F summieren, deren BKP in Spalte B mit "230.0." beginnt, z. B. "230.01", "230.01.0", "230.02.1". Bitte gib mir eine funktionierende Excel-Formel, die dieses Verhalten exakt umsetzt. Die Formel muss auch funktionieren, wenn sie z. B. in G4 steht und sich auf B4 bezieht. Vielen Dank!
Assistant
Hier ist eine Excel-Formel, die Ihre Anforderungen erfüllt:
=WENN(B4="";"";SUMMEWENNS(F:F;B:B;">"&B4&".";B:B;"<"&B4&"/")+SUMMEWENNS(F:F;B:B;B4&".*"))Erklärung der Formel:
Die Formel arbeitet mit zwei SUMMEWENNS-Funktionen, die addiert werden:
-
Erster Teil
SUMMEWENNS(F:F;B:B;">"&B4&".";B:B;"<"&B4&"/"):- Findet alle Einträge, die größer als "B4." und kleiner als "B4/" sind
- Dies erfasst alle Unterebenen wie "230.1", "230.01", "230.99.99" etc.
-
Zweiter Teil
SUMMEWENNS(F:F;B:B;B4&".*"):- Findet alle Einträge, die exakt mit dem Muster "B4.*" übereinstimmen
- Das Sternchen (*) ist ein Platzhalter für beliebige Zeichen
-
WENN-Bedingung: Prüft, ob B4 leer ist und gibt dann einen leeren Wert zurück
Funktionsweise bei Ihren Beispielen:
- B4 = "230": Summiert alle Werte zu "230.1", "230.01.0", "230.99.99" etc.
- B4 = "230.0": Summiert alle Werte zu "230.01", "230.01.0", "230.02.1" etc.
- B4 = leer: Zeigt nichts an
Alternative Formel (falls die erste nicht funktioniert):
=WENN(B4="";"";SUMMENPRODUKT((LINKS(B:B;LÄNGE(B4&"."))=B4&".")*(ISNUMBER(F:F))*F:F)+SUMMENPRODUKT((B:B=B4)*F:F))Kopieren Sie eine der Formeln in G4 und ziehen Sie sie nach unten bis G500. Die Formel passt sich automatisch an die jeweilige Zeile an.
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.