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

2 Antworten

Vom Fragesteller als hilfreich ausgezeichnet

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))

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

0
@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

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
@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
@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
@allocigar78

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

0
@DrErika

So oder so ähnlich:

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

0
@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
@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
@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
@allocigar78

Es ist natürlich nicht ganz einfach, dein spezielles Problem zu lösen, ohne die Tabelle zu sehen. Warum sollte jemand in einer Zelle den Anfang und das Ende einer Zählen-Wenn-Anweisung angeben wollen? Vielleicht weil von da bis da Herr XY mit seinen Zeiten steht und davor und danach ein anderer Arbeitnehmer? Wenn das so ist, dann mach eine Pivot-Tabelle.

0
@DrErika

Habe deinen Kommentar zur Liste oben überlesen. Entschuldige. Das geht auch mit Pivot-Tabellen. Beispiele kommen gleich.

0
@DrErika

ich weiß leider nicht was eien Piviot Tabelle ist. Meinst Du nicht das ist mit dieser Formel auch hinzubekommen? die hört sich doch gut an die Formel da ist sicher nur noch ein kleiner Fehler oder? wäre es denn eine Möglichkeit Dir die Tabelle mal per Mail zukommen zu lassen?

0
@allocigar78

Deinen Link hab ich noch nicht angesehen. Der Kommentar war irgendwie zu spät sichtbar. Schau ich mir heute abend an ob ich da was machen kann bzw. ob ich das verstehe

0
@DrErika

also mit dem Adress/Verketten Video kann ich leider gar nichts anfangen - verwirrst mich ehr mehr - ist ja auch nicths erklärt - nur ein wahnsinnig schnelles Bild das man dauernd stoppen muss und gar nicht weiß was jetzt gemacht wurde - also ich zumindest nicht.

ja das Priviot Video ist genauso hastig und schnell und ohne Ton aber das kriege ich vielleicht irgendwie hin, wenn es nicht anders geht. Allerdings dachte ich halt wir sind kurz davor mit Deiner vorgeschlagenen Formel - hast Du denn die Möglichkeit Dir meine Tabelle mal kurz anzusehen? wie ich Dich einschätze denke ich, Du erkennst auf einen Blick warum es nicht funktioniert!? - also nur wenn es nicht zuviel Stress für Dich ist - sorry

0
@allocigar78

In deinem speziellen Fall heißt die Formel

=ZÄHLENWENN(INDIREKT(VERKETTEN("Arbeitszeit!$I";MAX(INDIREKT(ADRESSE(ZEILE(Q4);SPALTE(Q4)));6);":$I";INDIREKT(ADRESSE(ZEILE(R4);SPALTE(R4)))));"Urlaub")-(WENN(INDIREKT(VERKETTEN("Arbeitszeit!$I";INDIREKT(ADRESSE(ZEILE(S4);SPALTE(S4)))))="Urlaub";0,5;0))-(WENN(INDIREKT(VERKETTEN("Arbeitszeit!$I";INDIREKT(ADRESSE(ZEILE(R4);SPALTE(R4)))))="Urlaub";0,5;0))

1
@DrErika

vielen herzlichen Dank! :-) - das hätte ich nie hinbekommen :-( - das ist ja echt ne Megaformel! aber sie funktioniert! dankeschöööööööön!!! - hab sie gerade mal versucht nachzuvollziehen - aber irgendwie..... - warum da jetzt ne "Max" Formel dabei ist und vor allem: wo kommt die Zahl "6" nach dem ersten mal Spalte (Q4))); her? - also das erschließt sich mir beim besten Willen nicht....

Suuuuuuper! Danke Dir nochmals!

0
@allocigar78

Bitte.

Die 6 ist nötig, weil da eine negative Zahl steht, dabei ist Zeile 6 die kleinstmögliche.

1
@DrErika

ah ok ist der Start weil die Tabelle bei I6 anfängt - danke :-)

0
@allocigar78

Übrigens kannst du natürlich jederzeit eine eigene "Antwort" hinzufügen. Dann gäbe es zwei und du könntest eine Antwort als "Hilfreichste Antwort" auswählen.

0

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

Ich habe zu danken. ;)

0

Was möchtest Du wissen?