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

Liebe MS-Office-Gemeinschaft

da ich mit einem Excel-Problem stecken geblieben bin, hab ich mich auf die Suche nach einem guten Forum gemacht und habe dieses hier gefunden *Smilie

Folgende Problemstellung:
Wir generieren Rechnungen in einem einfachen ERP. Nun möchte ich einem Partner Provisionen ausbezahlen. Respektive ihm die Möglichkeit geben, uns eine Rechnung zu stellen. Dazu habe ich zwei Reiter erstellt:

Rechnungen
Hier werden sämtliche Rechnungen mit Rechnungsnummer, Datum, Betreff, Betrag aufgeführt. Danach kann entschieden werden, ob eine Provision ausbezahlt wird (Nein, wenn dies nicht so sein soll) und ob die Provision schon einmmal gebucht wurde. Die Lezte Spalte zeigt dann nur an, ob die Provision in der aktuellen Provisionsabrechnung erscheinen soll.

Provision
Dieser Reiter wird dann als PDF dem Kunden geschickt. Hier sollen alle Rechnungen aufgelistet werden, welche im Reiter Rechnungen ein Ja bei Provision haben.

Lösungsversuch
Bei der Lösungssuche bin ich auf diesen Bericht gestossen. Dort habe ich die spannende Zeile gefunden:
=WENN(ZEILE(A1)>ANZAHL2(Tabelle1!B:B);"";INDEX(Tabelle1!B:B;KKLEINSTE(WENN(Tabelle1!B$1:B$1000"";ZEILE($1:$1000));ZEILE(A1))))
Danach habe ich mich so gut wie möglich in die Themen Matrix sowie Index und Verweis eingearbeitet. Ich bekomm es aber einfach nicht hin.

Über eine Hilfestellung bin ich echt meeega dankbar! =)

Zum besseren Verständnis, habe ich noch ein Excel mit der Problemstellung eingefügt.

Vielen Dank schon heute!
Gruss Mike

:)

 

Hallo Overandover,

So wie ich das verstehe kannst du das doch ganz einfach über einen Sverweis machen

 

Vergiss was ich geschrieben habe, du willst ja den gesamten markierten Bereich mit Formeln lösen...

 

Guck mal ob das so okay ist

 

Moin Mike,

bei dieser Aufgabe grängt sich eine Lösung über PIVOT auf, hast Du darüber schon mal nachgedacht?

 

Meinst du das so in etwa?



Provision

	A	B	C	D	E	F
1	Lieber Partner Bitte stellen Sie uns folgenden Betrag in Rechnung. Besten Dank
2
3	Provision:	110,092592592593
4
5	Nummer	Datum	Referenztext	Betrag	Provision
6	0
7	0
8	0
9	0
10	100127	04.07.2013	Zusatzarbeiten	100	10
11	100128	29.07.2013	Monatsrechnung	500	50
12	100129	08.08.2013	Ticket Rechnung	100	10
13	100130	12.08.2013	Ticket Rechnung	200	20
14	100131	13.08.2013	Ticket Rechnung	200,925925925926	20,0925925925926
15
16
17
18
19
20
21
22
23
24

verbundene Zellen

A1:E1

verwendete Formeln
Zelle	Formel	Bereich	R1C1 für Add In
B3	=SUMME(E6:E43)		=SUM(R[3]C[3]:R[40]C[3])
A6:A18	{=WENN(ZEILE(A2) >ANZAHL2(Rechnungen!B:B) ;"";INDEX(Rechnungen!B:B;KKLEINSTE(WENN(Rechnungen!B:B00<>"";ZEILE(:9) ) ;ZEILE(A2) ) ) *(Rechnungen!I1:I999="Ja") ) }	$A	=IF(ROW(R[-4]C) >COUNTA(Rechnungen!C[1]) ,"",INDEX(Rechnungen!C[1],SMALL(IF(Rechnungen!R1C[1]:R1000C[1]<>"",ROW(R1:R999) ) ,ROW(R[-4]C) ) ) *(Rechnungen!R[-5]C[8]:R[993]C[8]="Ja") )
B6:B18	=WENNFEHLER(SVERWEIS(A6;Rechnungen!$B:$I;2;FALSCH) ;"")		=IFERROR(VLOOKUP(RC[-1],Rechnungen!R2C2:R20C9,2,FALSE) ,"")
C6:C18	=WENNFEHLER(SVERWEIS(A6;Rechnungen!$B:$I;3;FALSCH) ;"")		=IFERROR(VLOOKUP(RC[-2],Rechnungen!R2C2:R20C9,3,FALSE) ,"")
D6:D18	=WENNFEHLER(SVERWEIS(A6;Rechnungen!$B:$I;5;FALSCH) ;"")		=IFERROR(VLOOKUP(RC[-3],Rechnungen!R2C2:R20C9,5,FALSE) ,"")
E6:E18	=WENNFEHLER(D6*0,1;"")		=IFERROR(RC[-1]*0.1,"")

{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!

 

Wahrscheinlich die Sinnvollste Lösung Dirk ..

Grüße

 

Hatte den Button vergessen, bin irgendwie etwas verwirrt heute Morgen...

 

Hallo Mike,

ich hätte da folgende Formel für Dich im Angebot, für die Spalten A bis C und in allen Zeilen (beginnend in Zeile 6):

PHP:

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


 

Hallo Lino

dein Code ist aber auch noch etwas optimierbarer.

Allein das ganze "select" verlangsamt deinen Code :-D
Aber auf jedenFall ein gangbarer Weg

Grüße

 

Bin noch recht frisch dabei.... im Moment gilt noch: Hauptsache es funktioniert *Smilie. Ich denke aber für diesen Ablauf sollte die Berechnungszeit keine signifikante Rolle spielen..

 

Moin Lino,

mal etwas zum testen, ohne Select *wink.gif*
Code:

 

Moin mücke

danke für den Hinweis mit PIVOT. Ehrlich gesagt, damit kenne ich mich noch gar nicht aus. Werde dies aber gleich mal ändern =)

Bin gespannt was ich damit anstellen kann!

Danke und Gruss
Mike

 

Hi jack

danke dir für deine (für mich) komplexe Formel. Eigentlich hab ich alles verstanden. Bis auf das Wesentliche *wink.gif* Und zwar diese FOrmel:
{=WENN(ZEILE(A2) >ANZAHL2(Rechnungen!B:B) ;"";INDEX(Rechnungen!B:B;KKLEINSTE(WENN(Rechnungen!B$1:B$1000"";ZEILE($1:$999) ) ;ZEILE(A2) ) ) *(Rechnungen!I1:I999="Ja") ) }

Ich weiss, ist bestimmt etwas viel verlangt, aber wenn du Zeit hättest, würde ich diese gerne verstehen. Könntest du mir sie evtl. erläutern? Wenn du keine Zeit hast, auch kein Problem. Dann teste ich weiter und bedanke mich herzlich!

Gruss Mike

 

Hi Lino

ob nun ganz effizient oder nicht... es funktioniert und schaut aus wie es sollte! Danke dir! ich werde noch versuchen die Anmerkung von mücke zu beherzigen und teste es mal ohne select.

aber auf jeden Fall etwas, was ich auf die "Schnelle" gut verwenden kann. thx!!!
Mike