Excel Berechnung - Bezug auf eine Zelle in der die Zeilennummer zu finden ist für den tatsächlichen

...komplette Frage anzeigen

2 Antworten

Schau dir mal INDIREKT und ADRESSE an, diese Kombi dürfte es bei dir bringen.

Der Wert im Bezug ADRESSE lässt sich durch INDIREKT holen.

=INDIREKT(ADRESSE(zeile,spalte))

Antwort bewerten Vielen Dank für Deine Bewertung
BMKaiser 09.09.2013, 14:30

DH für die Antwort, da ich diese verstehe! Doch den Sinn und den Inhalt der Frage habe ich nicht verstanden :-))

0
allocigar78 09.09.2013, 19:19
@BMKaiser

ich habe es im Kommentar auf DrErika nochmal versucht zu erklären.

Der Sinn? - ich habe mein Arbeitszeitkonto in einer Exceltabelle. In einem zweiten Tabellenblatt möchte ich ausgeben lassen, wieviele Urlaubstage ich in welchem Jahr genommen habe. Hierzu muss das Wort "Urlaub" gezählt werden. Soweit so gut. Meine Tabelle geht aber fortlaufend und endet nicht am Ende des Jahres. Also ist der 01.01. (an dem ein neues Jahr beginnt) und der 31.12. (an dem das Jahr endet) ja jedes Jahr eine andere Zeilennummer. Diese Zeilennummer lasse ich in den genannten Spalten Q14, R14 berechnen (S14 ist der 24.12. da hier, genau wie am 31.12 nur ein halber Urlaubstag abgezogen wird). Die Berechnung erfolgt nach dem Algorhytmus voriges Jahr +365; voriges Jahr +365; voriges Jahr + 365; voriges Jahr +366 (Schaltjahr).

Nur damit kann ich errechnen, in welcher Zeile der nächste 01.01. beginnt und somit ein neues Jahr für die Urlaubstageberechnung.

0
allocigar78 09.09.2013, 19:14

vielen Dank für Deine Idee, aber leider verstehe ich die Umsetzung nicht. Also folgende Formel habe ich eingesetzt:

=ZÄHLENWENN(Arbeitszeit!$I210:$I574;"Urlaub")-(WENN(Arbeitszeit!$I567="Urlaub";0,5;0))-(WENN(Arbeitszeit!$I574="Urlaub";0,5;0))

die Zeilen 210, 574 und 567 habe ich jedoch manuell eingegeben. DASS es diese Zeilen sind, steht in Q14 (hier steht als Ergebnis 210), R14 (hier steht 574) und S14 (hier steht die 567)

Ich müsste also statt den drei Zahlen 210, 574 und 567 in der obigen Formel einen Bezug auf Q14, R14 und S14 machen. Aber wie soll ich das mit ADRESSE und INDIREKT in die Formel bringen?

0
DrErika 10.09.2013, 11:43
@allocigar78

Hallo.

Die Funktion "INDIREKT" nimmt eine Zeichen kette und interpretiert sie als Bezug. Wenn also der Bezug nicht wie in meinem Beispiel aus dem selben Arbeitsblatt kommt "(zeile,spalte)", dann musst du natürlich "VERKETTEN" verwenden und deinen Tabellenblatt-Bezug hinzufügen!

=INDIREKT(VERKETTEN("TABELLENBLATT!";ADRESSE(zeile;spalte)))

In deinem Fall besteht der Bezug sogar aus einem Adress-Range, sodass du noch mehr verketten muss, nämlich zweimal "ADRESSE" mit einem Doppelpunkt getrennt.

=ZÄHLENWENN(INDIREKT(VERKETTEN("TABELLENBLATT!";ADRESSE(1;1);":";ADRESSE(6;1)));"x")

Du bist pfiffig genug, das auf deinen Fall umzustricken, da bin ich sicher.

0
allocigar78 10.09.2013, 15:12
@DrErika

vielen Dank ich weiß Du gibst Dir sehr viel Mühe aber ich verstehe leider immer noch gar nichts. Also einfach ausgedrückt lautet meine Frage ja: wie bringe ich Excel dazu dass ich die 574 in meiner Formel nicht manuell schreibe sondern er sie aus der Zelle Q14 übernimmt? mehr brauche ich ja gar nicht. vielleicht könntest Du das in meine Formel integrieren wenn es Dir nicht zuviel Mühe macht? Ansonsten trotzdem herzlichen Dank dann muss ich es halt weiter manuell machen :-(

0
DrErika 10.09.2013, 15:21
@allocigar78

Ich bin betrübt, mich nicht verständlich gemacht zu haben. :/

0
DrErika 10.09.2013, 15:27
@DrErika

So oder so ähnlich:

=ZÄHLENWENN(indirekt(verketten("Arbeitszeit!";adresse(Zeile(Q14);spalte(Q14));":";adresse(zeile(R14);spalte(R14))));"Urlaub")

0
allocigar78 10.09.2013, 18:18
@DrErika

hm, also ich hab jetzt alles probiert - die Formel dahingehend modifiziert, dass ich auch die Spalte auf dem 2. Tabellenblatt ausgeben lasse (in Q14 bzw. Q5) steht ja nur die Zeilennummer - also habe ich in U5 mal noch den Spaltenbuchstaben reingetippt ums zu probieren ob es dann funzt (weil in Deiner Formel ja auch auf die Spalte verwiesen wird) - nichts! es kommt immer ne Null raus: schau mal so hab ich so modifiziert:

=ZÄHLENWENN(INDIREKT(VERKETTEN("Arbeitszeit!";ADRESSE(ZEILE(Q5);SPALTE(U5));":";ADRESSE(ZEILE(R5);SPALTE(U5))));"Urlaub")

in Q5 steht 210, in U5 ein I in R5 die 574 - also sollte er ja die Wörter "Urlaub" auf dem Tabellenblatt 1 von Spalte I210 bis I573 zählen - tut er aber nicht - immer ne null! hast Du noch ne Idee?

noch als Hinweis: die Spalten Q5, R5 und U5 sind auf dem Tabellenblatt wo ich die Formel eingebe! (nennt sich "Urlaubstage") aber die Wörter "Urlaub" sollen auf dem Tabellenblatt "Arbeitszeit" gezählt werden - nur falls das was zur Sache tut

0
DrErika 10.09.2013, 18:52
@allocigar78

Nein nein, also Zeile(Q5) gibt die Zeile aus, auf die sich Q5 bezieht (also 5). Und ich will lediglich mit ADRESSE(zeile,spalte) aus der Zeile/Spalte etwas auslesen. Er holt sich eben nicht damit die Zeile, die in der Zelle steht!

Ich glaub, ich mach mir morgen auch mal so ein Beispiel....

Und ich glaub auch, du könntest das sofort mit einer Pivot-Tabelle erledigen, anstatt "zu Fuß".

0
allocigar78 10.09.2013, 19:11
@DrErika

also ich versteh irgendwie gar nichts - die Formel bzw. die wie ich sie jetzt angepasst hatte hat sich für mich total schlüssig angehört - aber was Du jetzt meinst die Zeil Q5 gibt die Zeile aus auf die sich Q5 bezieht - also 5 - keine Ahnung... -

also in der Zelle Q5 steht die Zahl 210 in der Zelle R5 steht die Zahl 574 und meinetwegen (wenn es nötig ist) steht in der Zelle U5 ein I

ich möchte die Wörter "Urlaub" von Zelle I210 bis I574 auf Tabellenblatt "Arbeitszeit" zählen lassen. Wo ist denn da in der Formel der Fehler?

ich weiß nicht was ne Pivot-Tabelle ist - und ich denke wir sind jetzt auch so nah dran dass ich jetzt nicht unbedingt nochmal von vorne mit was ganz anderem anfangen möchte... - also außer wir kriegen es nicht hin, aber ich denke wir sind ganz nah dran nur irgendwo ist da ein Fehler....

0

noch mal ein dickes Dankeschön an DrErika :-)

Antwort bewerten Vielen Dank für Deine Bewertung
DrErika 13.09.2013, 18:46

Ich habe zu danken. ;)

0

Was möchtest Du wissen?