Office: (Office 2010) Formel zum überspringen von Zeilen?

Hi Superingo2 =)

Wie geil! Sieht echt sehr gut aus und scheint zu funktionieren. Wenn ich hinten aber mehrere "Ja" bei "Gebucht" entferne, erscheint plötzlich der Fehler #ZAHL!. Wenn ich mehrere "Ja" hinzufüge, werden nicht mehr alle Rechnungen angezeigt.

Leider bin ich noch nicht so fit, dass ich deine Formel bis ins Detail verstehe. Daher finde ich den Fehler auch nicht.

By the way: kennst du ein Tutorial oder eine andere Möglichkeit, wie ich mir dieses Wissen aneigen könnte?

Danke nochmals.
Gruss Mike

 

Hallo Overand Out

Imho gibt es kein sinnvolles tutorial welches dir derart komplexe formeln wie die von Superingo erklärt

Daher rein didaktisch

Schaue dir zb auf http://www.excelformeln.de/formeln.html
"Alle" Formeln und erklärungen an. Macht erstmal nur begrenzt Sinn für dich (hat aber den Mehrwert, dass man alles schonmal gesehen hat)

im zweiten Schritt versuchst du zur Übung einfache Formeln miteinander zu kombinieren

im dritten SChritt versuchst du deine Fragestellung in (mit einfachen Formeln lösbare ) Teilschritte zu zerlegen

im vierten Schritt fügst du die zusammen (Immer von Aussen nach innen Denken)

Bsp "meine Formel"
Besteht aus
1. Wenn(Prüfung;Dann;Sonst) -> einfache logische Formel die Kriterien Prüft und anhand dessen Rückgabewerte besteht (Werte können auch Formeln sein)

2. Zeile(Bezug) -> gibt einen Zahlenwert für die Zeile zurück auf die referiert wird [A1 =1; A4 = 4] wenn zwischen den Klammern nichts steht ist es immer die aktuelle Zeile (in der du schreibst )

3. Anzahl2(Bezug) ->gibt die Anzahl nicht leerer Zellen des in Klammern angegebenen Bereichs bzw der angegebenen Zellen zurück

4. Index(Bereich; Zeile;Spalte) ist eine Matrix bzw. Array -Formel

Bsp (A1:C4;2;3) gibt den Wert der 2. Zeile und der 3.Spalte aus dem Bereich zurück Ergebnis ist der Wert der Zelle C2 [Wichtig ist hierbei er zählt immer von der ersten Zelle des Angegebenen Bereichs wieder NICHT von A1, es sei denn, A1 ist die erste Zelle des Bereichs]

5. kkleinste(Matrix, k) gibt den k kleinsten wert der Matrix zurück (wenn k =2 ist dann wird der Zweitkleinste Wert zurückgegeben)

So und jetzt bringst du das in die richtige Reihenfolge und dann passt es
oder aber
Deduktiv vorgehen



Provision

	A	B	C
18			=WENN(ZEILE(A2) >ANZAHL2(Rechnungen!B:B) ;"";INDEX(Rechnungen!B:B;KKLEINSTE(WENN(Rechnungen!B:B00<>"";ZEILE(:9) ) ;ZEILE(A2) ) ) *(Rechnungen!I1:I999="Ja") )
19	Formel Zerlegen
20		Rahmenformel	=wenn(Prüfung1; "";Schau mal wo anders")
21		Prüfung 1	wenn zeile(A2) größer als die Anzahl der leeren Zellen in Rechnungen B:B ist (Wobei A2 hier variabel ist, da die Formel gezogen wird .. Die Aktuelle Zeile muss immer kleiner sein die Anzahl der Leerzellen)
22		Zeile A2	Rückgabe : 2 (Wichtig hier, wenn du die Formel runter ziehst erhöht sich der Wert um 1) ..logisch denn A3 gibt 3 zurück A4 -4 usw.
23		Index	=INDEX(Matrix;Zeile;Spalte)
24		Matrix (der Indexformel)	ist Rechnungen B:B (Da hier nur eine Spalte die Matrix bildet, kann "Spalte" bei Index () frei bleiben
25		Zeile (der Indexformel)	=KKLEINSTE(WENN(Rechnungen!B:B00<>"";ZEILE(:9) ) ;ZEILE(A2) ) ) *(Rechnungen!I1:I999="Ja")
26		kkleinste (Matrix;k)	gibt den "ZEILE(A2) " kleinsten wert von (WENN(Rechnungen!B:B00<>"";ZEILE(:9) ) ) zurück
27		Zeile	oben erklärt (Entspricht "k") und verändert sich beim Ziehen
28		Matrix (der kKleinsteformel)	(WENN(Rechnungen!B:B00<>"";ZEILE(:9) ) ) -> Wenn die Zellen nicht Leer sind, sind sie bestandteil der Matrix
29		Anhang	Der Anhang " *(Rechnungen!I1:I999="Ja") " Prüft lediglich ob in Spalte I "Ja Steht" und gibt dann Wahr oder Falsch Zurück Wenn Falsch steht wird der Term vorher "genullt" Wenn Wahr steht wird er mit 1 multipliziert

 

Hallo Mike,

wofür bastele ich Dir eine geniale Formel, wenn Du in keiner Weise darauf eingehst?

Meine Formel löst ja sogar auch das Problem von jack_D, der die nicht relevanten Provisionszeilen nicht ausgeblendet bekommen hat.

Du musst nur noch den Durchsuchungs-Bereich von 2 bis 9 Zeilen auf die gewünschte Länge aufbohren, aber ansonsten ist das genau das, was Du gewünscht hast!!!!

Ein etwas enttäuschter Helfer

oh sorry....ich lese gerade Deinen Beitrag......und nehme alles zurück!!!!!!!!!!!!!!!!!!!!!!

 

Hallo Mike,

wenn Du meine lange Formel (die mit "wenn") benutzt, dürftest Du keine Fehler "#Zahl" erhalten.
Wenn Du den Fehlerwert "#Wert" in Spalte E entfernen willst, ändere Deine Formel in E6 so ab:

PHP:

         =WENN(D6="";0;D6*0,1)


 

Moin Mike,

da gibt es div. Möglichkeiten *Smilie
Ein muss für alle Formelfreaks sind folgeden Seiten

_ Excelformel.de die ultimative Formelseite

_ online-excel.de Tipps und Tricks rund um Excel

und natürlich dieses Forum hier *Smilie

 

Hallo Ingo

es freut mich, dass du meine Nachricht noch gelesen hast =) ich versuche wirklich alle Varianten zu testen, nachzuvollziehen und auch jedem eine Antwort zu geben!

habe deine Formel nochmals getestet. es ging mir nicht darum, dass bei provision ZAHL steht. sondern unten dran. habe dir das excel nochmals hochgeladen.

So wie es jetzt ist werden keine Werte ausgegeben. Obwohl dies eigent so sein müsste. Die Rechnung vom 12.08.13 müsste ausgegeben werden.

Wenn du im Reiter Rechnungen alle Ja bei GEBUCHT löschst, siehst du auch den Fehler mit ZAHL!.

woran könnte das liegen?

Gruss und herzlichen Dank nochmals!
mike

 

hi jack_D

danke für deine Ausführung. bin gerade im büro, da klingelt oft das telefon. werde es mir in aller ruhe mal an einem abend anschauen! =) und mich auch durch deinen link klicken.

danke danke danke! =)

auch für den tipp nicht alles in eine formel packen zu wollen. werde es jetzt erst mal auseinander nehmen und alles separat anschauen.

gruss und ein schönes wochenende
mike

 

Hallo Mike,

sorry, da ist mir ein Fehler unterlaufen. Hier die korrigierte Formel:

PHP:

         {=WENN(ZEILE()-5-SUMME((Rechnungen!$H$2:$H$999="Ja")*1)>0;"";
INDIREKT("Rechnungen!Z"&
KGRÖSSTE((Rechnungen!$H$2:$H$999="Ja")*(ZEILE(Rechnungen!$H$2:$H$999));SUMME((Rechnungen!$H$2:$H$999="Ja")*1)-ZEILE()+6)
&"S"&SPALTE();0))}


 

Moin Mike,

oder evtl. so.
Die Formel kann nach rechts bzw. nach unten kopiert werden.
Provision

	A	B	C	D
5	Nummer	Datum	Referenztext	Betrag
6	100125	29.05.2013	Monatsrechnung	540,00
7	100126	28.06.2013	Monatsrechnung	540,00
8	100130	12.08.2013	Ticket Rechnung	216,00

Formeln der Tabelle

Zelle Formel A6 {=WENNFEHLER(INDEX(Rechnungen!A:A;KKLEINSTE(WENN((Rechnungen!$H:$H="Ja");ZEILE(:));ZEILE(A1)));"")} Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen! Excel Jeanie HTML 4   OfficeUser, 30. August 2013 #24 SUPERINGO2 Superingo2 Hallo Dirk, Respekt für Deine Formel! Es macht immer wieder Spaß noch seinen Meister zu treffen. Meine Formel funktioniert zwar genauso gut wie Deine, aber Du hast das Problem mit den gefundenen Nullwerten deutlich besser gelöst. Meine Hochachtung! Die Idee mit "Wennfehler", konnte ich leider nicht haben, da ich Excel 2003 benutze. Aber auch Du mußt Deine Formel in Spalte D um eine Spalte anpassen, da hier die Ausgabespalte E gewünscht wird. LG Ingo   Superingo2, 30. August 2013 #25 MüCKE mücke Moin Ingo, ... aber ... diese Formel ist nicht von mir, sondern von Excelformel.de *wink.gif* @mike Die Anpassung der Formel in Spalte D kriegst Du bestimmt alleine hin *Smilie Schöne Wochenende *mrcool   mücke, 30. August 2013 #26 OVERANDOVER overandover Ich bin überwältigt! Danke danke danke euch allen für die super Hilfe! Bin so saumässig positiv überrascht, dass ich es kaum in Worte fassen kann! *Smilie Jetzt brauche ich erstmal etwas Zeit um alles einzufügen, zu testen und hoffentlich zu verstehen! *wink.gif* Ein klasse Forum mit klasse Members! Gruss Mike   overandover, 30. August 2013 #27 OVERANDOVER overandover Formel zum überspringen von Zeilen? \@Ingo und Dirk. Habe beides getestet und es läuft super! Danke euch herzlich! Jetzt werde ich dann mal ein paar Stunden aufwenden, dies auch zu verstehen!! =) Will schliesslich weiterkommen. @Ingo: Habe auch deine Formel für die Spalte E noch übernommen und minimal angepasst. So funktionierts für meine Bedürüfnisse perfekt: Code:   overandover, 30. August 2013 #28 (Du musst angemeldet oder registriert sein, um eine Antwort erstellen zu können.) Ignorierte Inhalte anzeigen Seite 2 von 2 Thema: Formel zum überspringen von Zeilen? Die Seite wird geladen... Formel zum überspringen von Zeilen? - Similar Threads - Formel überspringen Zeilen Ergebnis aus INDEX und Vergleich Formel weiter Verwenden in Microsoft Excel Hilfe Ergebnis aus INDEX und Vergleich Formel weiter Verwenden: Hallo :-) Ich möchte ein Ergebnis aus einer Formel (Index/Vergleich) mit einer Wenn Funktion auswerten. Wenn sieht aber nicht das Ergebnis in dem Kästchen sondern nur die Formel!... dynamische Bezug auf Feiertagskalender in Formel "Nettoarbeitstage" in Microsoft Excel Hilfe dynamische Bezug auf Feiertagskalender in Formel "Nettoarbeitstage": Hallo Zusammen, ich habe leider ein paar Schwierigkeiten die Nettoarbeitstage von Mitarbeitern an verschiedenen Standorten in verschiedenen Bundesländern zu ermitteln. Irgendwie stehe ich gerade... Formel funktioniert nur bis 19.30 Uhr in Microsoft Excel Hilfe Formel funktioniert nur bis 19.30 Uhr: Hallo zusammen, Ich würde gerne die Anzahl der ein- und ausfahrenden Fahrzeuge einer Parkgarage über mehrere Tage im Viertelstundentakt auswerten. Ich verwende dafür die Funktion ZÄHLENWENNS. Das... Formel sucht nur unvollstaendig in Microsoft Excel Hilfe Formel sucht nur unvollstaendig: Guten Morgen, woran kann es liegen, dass wenn ich diese Formel eingebe, erhalte ich beispielsweise nur 4 ergebnisse.. wenn ich aber die formel in der formelleiste noch einmal bestaetige, erhalte... Keine Zeilen überspringen in Microsoft Excel Hilfe Keine Zeilen überspringen: Hallo, ich habe ein Problem, und da ich nach langem probieren und googeln immer noch keine Lösung habe wollte ich mal hier nachfragen ob Ihr mir weiterhelfen könnt. Ich habe eine Datei mit... formel zeilen und sparten überspringend kopieren in Microsoft Excel Hilfe formel zeilen und sparten überspringend kopieren: Hallo zusammen, ich sitzte bereits seit einigen stunden an dieser tabelle und schafe das letzte stück nicht. farbliche hinterlegung: ab wert 400/500/600 und 700. geschaft max wert: fett kursiv... Zelle in Formel überspringen in Microsoft Excel Hilfe Zelle in Formel überspringen: Guten Tag ! Ich möchte bestimmte Werte einer Zelle aus einem Tabellenblatt in einer Übersichtstabelle in einem anderen Tabellenblatt anzeigen lassen. Das ist ja recht einfach ='Name... Benutzername oder E-Mail-Adresse: Besitzt du schon ein Benutzerkonto? Nein, erstelle jetzt ein Benutzerkonto. Ja, mein Passwort ist: Hast du dein Passwort vergessen? Angemeldet bleiben Anmelden oder registrieren Suche Nur die Titel durchsuchen Erstellt von: Trenne Benutzernamen durch Kommata. Neuer als: Nur dieses Thema durchsuchen Nur dieses Forum durchsuchen Die Ergebnisse als Themen anzeigen Nützliche Suchen Themen mit aktuellen Beiträgen Mehr Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten. Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies. Akzeptieren Weitere Informationen... Information ausblenden