---

Hallo Gemeinde,

wie üblich: Forumsuche->Treffer: 0 ... Google->Treffer: jeweils nur für die gesamte Spalte (oder Zeile  )

Daher mein Hilferuf in Eure Richtung.

Hintergrund:
ich möchte in einer Zelle eine Zahl darstellen lassen, die um 1 größer ist als die unterste Zahl in der gesamten darüber liegenden Spalte oder - was mir noch schwieriger erscheint - in einem bestimmten Bereich der darüber liegenden Spalte.

Problem:
die Formel muss so aufgebaut sein, dass sie flexibel an jede beliebige Stelle dieser Spalte kopiert werden kann und dort immer das korrekte Ergebnis liefert.

momentane (unbefriedigende) Lösung:
steht atm in Zelle C13 (oder tiefer), in Zelle oberhalb der Formel steht die Spaltenüberschrift (Positions-Nr.) und die Zeile direkt oberhalb der jeweiligen Teil-Tabelle soll zur sauberen Darstellung immer leer sein:

Code:

---

=WENN(D13="";"";1+SUMME(C10)*(SUMME(C11)=0)+SUMME(C9)*(SUMME(C10:C11)=0)+SUMME(C8)*(SUMME(C9:C11)=0)+SUMME(C7)*(SUMME(C8:C11)=0)+SUMME(C6)*(SUMME(C7:C11)=0)+SUMME(C5)*(SUMME(C6:C11)=0)+SUMME(C4)*(SUMME(C5:C11)=0)+SUMME(C3)*(SUMME(C4:C11)=0)+SUMME(C2)*(SUMME(C3:C11)=0)+SUMME(C1)*(SUMME(C2:C11)=0))

---

überwacht den Bereich von C1 bis C10, ignoriert Texte und Abbildungen und erfasst die unterste Zahl

Leider reicht das an manchen Stellen nicht aus, da durch VBA-Kopier-Aktionen mit Abbildungen auch deutlich größere "Lücken" als die momentan erfassten 10 Zeilen entstehen können - theoretisch bis zu ca. 50 Zeilen, die ich zum einen formelmäßig nicht sehr gern abdecken müssen möchte  und zum anderen bei der obersten Verwendung frühestens eine Zeile unterhalb des überwachten Zeilenbereichs ohne Fehlermeldung (BEZUG!) funktionieren würde  .

Mein (einziger) derzeitiger Ansatz geht dahin, die Höhe der Abbildungen per Benutzereingabe auszuwerten und direkt über der Ergebniszelle (in diesem Fall C13) per VBA entsprechend viele neue Zellen einzufügen - sie sozusagen "runterzudrücken" ... allerdings empfinde ich weder die Formel noch diese Methode als elegant.

Hat jemand eine bessere Idee ?

Danke für Euer Gehirnschmalz 

Gernot

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Servus,

was soll die unterste Zahl (min?) sein, eventuell hilft eine Beispiel XLS!

=min(A1:A10)+1

------------------
<----- Bitte Systeminfo eintragen, warum siehst du hier. Schöne Grüsse aus der Steiermark  Bernd P.

[Diese Nachricht wurde von Bernd P am 15. Feb. 2018 editiert.]

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Moin Bernd,

nöö, ähem, hättest Du meinen Code-Schnipsel gelesen ... oder auch nur angefangen  ... es geht nicht um die kleinste Zahl, sondern um diejenige, die oberhalb der Zelle mit der Formel als unterste steht.

Beispiel:
von C3 bis C5 stehen die Positionsnummern 1 bis 3 und in C13 soll nun automatisch die 4 erscheinen.

Gruß 

Gernot

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

hmmm, obwohl mir da jetzt die Idee kommt, das Ganze mit max zu probieren ... "kann" diese Funktion automatisch Texte und Abbildungen "aussortieren", wie es Summe macht ... und wie kriege ich die Überwachung von C1 bis zur Zelle oberhalb der jeweiligen Formelzelle umgesetzt ? für den bisher abgedeckten Bereich der 10 Zeilen würde die max-Formel - so sie denn funktioniert - auf jeden Fall eleganter sein  ... o.k. mit max(C$1:C10) sollte zumindest das gelöst sein ... mal schauen

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Moin Gernot,

der Einleitungssatz ist niedlich, aber netter wäre eine Auflistung der Suchbegriffe und evtl. sogar Ergebnistreffer, die NICHT zum Ziel führten, Fleißbienchen bekämst Du dann noch für das WARUM nicht. Das macht es für die geneigten Helferlein einfacher und wirkt nicht so Copy&Paste 
Aber das nur am Rande.

Hast Du es mit BEREICH.VERSCHIEBEN und INDIREKT versucht?

Code:

---

=(BEREICH.VERSCHIEBEN(INDIREKT((ADRESSE(ZEILE();SPALTE())));-1;0)+1)

---

Deine nachgeschobene Erläuterung hat aber nichts mit dem Ursprung der Frage bzgl. Blockbereiche mehr zu tun, oder?
Ohne Beispielmappe nur der Schuß ins Blaue...

------------------
Gruß,
runkelruebe          Herr Kann-ich-nich wohnt in der Will-ich-nich-Straße...

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

ok, mit der max-Formel funktioniert's für diese meine konkrete Anwendung  (zufällig), weil die Positions-Nummern halt nach unten immer größer werden und so die unterste Zahl oberhalb der Formelzelle immer auch die größte Zahl in diesem Bereich ist ... jetzt muss ich nur noch dafür sorgen, dass in der Spalte C niemals nie nicht andere, eventuell größere Zahlen auftauchen 

sollte aber jemandem eine Lösung für die ursprüngliche (offensichtlich zu ungenau formulierte) Fragestellung einfallen - nur her damit ... wer weiß, wofür man's vielleicht mal brauchen könnte 

Grüße 

Gernot ... fertig (?)

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Moin Runkelrübe,

Zitat:

---

Original erstellt von runkelruebe:
Auflistung der Suchbegriffe und evtl. sogar Ergebnistreffer, die NICHT zum Ziel führten, Fleißbienchen

---

leider weiß ich die Suchbegriffe hier im Forum nicht mehr genau ... "unterste Zahl in Spalte" oder so etwas

in Google: "Excel 2013 unterste Zahl in Bereich finden" ... weil die Seite noch offen war    ...

die von mir geöffneten Treffer befassten sich aber leider immer nur mit der jeweils gesamten Spalte oder Zeile und nicht mit Teilabschnitten davon: z.B. http://www.excelformeln.de/formeln.html?welcher=50

der einzige (gelesene) Treffer, der tatsächlich wirklich hilfreich schien, beinhaltete Matrix-Formeln, mit denen ich mich in Excel überhaupt nicht auskenne und da die bisher existierende Tabelle mit VBA-Zeuchs und Grafiken schon bei gut 15 MB liegt, will ich keine mir unbekannten Experimente wagen    ... https://www.excel-inside.de/funktionen-a-tipps/927-ermittlung-des-letzten-wertes-einer-zeile-oder-spalte

oooooh, hier gibt's Fleißbienchen ... warum hat mir das keiner gesagt 

Grüße 

Gernot

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

ach so, das funktionierende Code-Schnipselchen in der Zelle C13 (funzt aber schon ab C2 und ist kopierbar in jede tiefer gelegene Zelle):

Code:

---

=WENN(D424="";"";1+MAX(C$12:C423))

---

hm, die Test-Tabelle stand gerade in Zeile 424  und die gesamte Tabelle beginnt - nur wegen der 10-Zeilen-Formel aus dem Start-Post - in Zeile 12 ... wenn jetzt noch jemand eine Lösung für http://forum.cad.de/foren/ubb/Forum226/HTML/002047.shtml hätte, wäre die Welt rosarot mit Zuckerwatte-Wölkchen 

Grüße 

Gernot

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Zitat:

---

Original erstellt von runkelruebe:
der Einleitungssatz ist niedlich, aber netter wäre eine Auflistung der Suchbegriffe und evtl. sogar Ergebnistreffer, die NICHT zum Ziel führten, Fleißbienchen bekämst Du dann noch für das WARUM nicht. Das macht es für die geneigten Helferlein einfacher und wirkt nicht so Copy&Paste  

---

und dabei habe ich mir diesen - durchaus jedesmal eigenhändig wieder neu (und meist etwas anders) in die Tastatur gehämmerten - Satz nur angewöhnt, weil oftmals als erstes solche wenig hilfreichen Antworten wie "durchsuch' doch endlich mal die Forenhilfe" oder solche Links wie "Ein Freund hat Sie auf diese Seite Google-Suche geschickt" kamen, die ich damit abzuwehren gedachte ... jetzt reicht nicht mal das mehr aus 

vielleicht beim nächsten Mal etwas detailierter ... obwohl ?? ... na mal schauen ...

Grüße 

Gernot

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Folgende Formel gibt Dir immer den letzten Wert aus.
Hier z.B. in Spalte D von Zeile 1 bis Zeile 1000

={INDEX(D1:1004;MAX((D1:1000<>"")*ZEILE(1:1000)))}

Achtung:
Umrandende { } nicht mit eingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!

------------------
Gruß Pille

[Diese Nachricht wurde von pi-design am 15. Feb. 2018 editiert.]

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

sollte : D sein

------------------
Gruß Pille

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Hallo Pille,

Zitat:

---

Original erstellt von pi-design:
Folgende Formel gibt Dir immer den letzten Wert aus.
Hier z.B. in Spalte D von Zeile 1 bis Zeile 1000

={INDEX(D1:D1004;MAX((D1:D1000<>"")*ZEILE(1:1000)))}

Achtung:
Umrandende { } nicht mit eingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!

---

das ist doch so eine oben erwähnte Matrix-Formel, oder ?!

Müsste ich die - für die freie Kopierbarkeit - für z.B. "meine" Zelle C13 und die Überwachung ab Zeile 12 so umschreiben :

Code:

---

={INDEX(C$12:C12;MAX((C$12:C12<>"")*ZEILE($12:12)))}

---

oder würde sie dann nicht mehr funktionieren ? ... wie gesagt, Matrix-Formeln in Excel verstehe ich nicht wirklich.

Gruß (ohne :wink: )

Gernot

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Zitat:

---

Original erstellt von ArCADe-Spieler:
nöö, ähem, hättest Du meinen Code-Schnipsel gelesen ... oder auch nur angefangen  

---

Was soll das bringen eine Formel zu lesen die Augenscheinlich nicht zum Erfolg führt?
Und weil wir grad beim hättest gelesen sind, was ist mit eventuell hilft eine Beispiel XLS! und Ohne Beispielmappe nur der Schuß ins Blaue...

btw.
der Code entspricht aber nicht deinen Anforderungen von die um 1 größer ist als die unterste Zahl in der gesamten darüber liegenden Spalte
sie gibt nur die max. Zahl+1 aus

Code:

---

=WENN(D424="";"";1+MAX(C$12:C423))

---

das wärs in VBA, sucht in der aktiven Spalte nach der letzten Zeile, nimmt den Wert + 1 und trägt den Wert in die aktive Zelle

Code:

---

Sub letztezahl()
lastnumber = ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Value
ActiveCell = lastnumber + 1
End Sub

---

------------------
<----- Bitte Systeminfo eintragen, warum siehst du hier. Schöne Grüsse aus der Steiermark  Bernd P.

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

test.xlsx.txt

Zitat:

---

Original erstellt von ArCADe-Spieler:
Hallo Pille,

das ist doch so eine oben erwähnte Matrix-Formel, oder ?!

Müsste ich die - für die freie Kopierbarkeit - für z.B. "meine" Zelle C13 und die Überwachung ab Zeile 12 so umschreiben :

Code:

---

={INDEX(C$12:C12;MAX((C$12:C12<>"")*ZEILE($12:12)))}

---

oder würde sie dann nicht mehr funktionieren ? ... wie gesagt, Matrix-Formeln in Excel verstehe ich nicht wirklich.

Gruß (ohne  )

Gernot

---

anbei ein Bsp. (.txt entfernen)

------------------
Gruß Pille

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Beispielalt.xls.txt Beispielneu.xls.txt

Hallo Bernd,

ich bin mir nicht ganz sicher, warum ich mich über Deinen Post ärgere, was der einzige Grund ist, warum ich überhaupt darauf antworte.

Zitat:

---

Original erstellt von Bernd P:

Was soll das bringen eine Formel zu lesen die Augenscheinlich nicht zum Erfolg führt?

---

habe ich nicht geschrieben ... ich fand meine (Start-Post-)Formel nur unelegant und die Methode zum Berücksichtigen größerer als der 10-Zeilen-Lücken unbefriedigend aufwändig

Zitat:

---

Original erstellt von Bernd P:

Und weil wir grad beim [i]hättest gelesen sind, was ist mit eventuell hilft eine Beispiel XLS! und Ohne Beispielmappe nur der Schuß ins Blaue...[/i]

---

wie geschrieben: die Datei ist 15 MB groß und ich glaubte, mein Anliegen ausreichend genau beschrieben zu haben, ohne die Datei von allem Inhalt befreien zu müssen, den ich nicht posten darf, um dann eine Datei hinzufrickeln, die alle Funktionen und Erläuterungen für ihre Bedienung enthält ... trotzdem im Anhang zwei Dateien mit Mikro-Beispielen zur Verdeutlichung ... hellgrüner Hintergrund bedeutet "Benutzereingabe erforderlich" ... aus dem Dateinamen die Endung ".txt" löschen

Zitat:

---

Original erstellt von Bernd P:

BTW.
der Code entspricht aber nicht deinen Anforderungen von [i]die um 1 größer ist als die unterste Zahl in der gesamten darüber liegenden Spalte
sie gibt nur die max. Zahl+1 aus

Code:

---

=WENN(D424="";"";1+MAX(C$12:C423))

---

[/i]

---

hm, meinen Anforderungen in diesem speziellen Fall entspricht er ganz genau - wie weiter oben auch geschrieben, da in meinem Fall die unterste Zahl immer zugleich auch die größte ist ... ich hatte bereits "zugegeben", dass meine ursprüngliche Thread-Benennung und Anforderungs-Beschreibung die tatsächlichen Ansprüche in diesem konkreten Fall nicht 100%-ig getroffen hatte

Zitat:

---

Original erstellt von Bernd P:

das wärs in VBA, sucht in der aktiven Spalte nach der letzten Zeile, nimmt den Wert + 1 und trägt den Wert in die aktive Zelle

Code:

---

Sub letztezahl()
lastnumber = ActiveSheet.Cells(Rows.Count, ActiveCell.Column).End(xlUp).Value
ActiveCell = lastnumber + 1
End Sub

---

---

für den Moment nicht wirklich hilfreich, da ich es mit normalem Excel-Zellen-Formel-Inhalt machen will, aber als Anregung, falls mal eine solche Situation vorkommt echt interessant

Gruß 

Gernot

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Hallo Pille,

hab' jetzt mal in Deiner Beispiel-Datei rumprobiert und dabei etwas erstaunt festgestellt, dass sich Deine Zelle C20

Code:

---

=INDEX(C1:C19;MAX((C1:C19<>"")*ZEILE(1:19)))

---

nach dem Kopieren in z.B. Zelle C25 mit dem "#BEZUG!"-Fehler zurückmeldet.

ändere ich jedoch den dortigen Inhalt

Code:

---

=INDEX(C6:C24;MAX((C6:C24<>"")*ZEILE(6:24)))

---

dann auf

Code:

---

=INDEX(C$1:C24;MAX((C$1:C24<>"")*ZEILE($1:24)))

---

und bestätige einmalig mit Strg+Shift+Enter, kann ich diese Zeile beliebig hoch und runter kopieren und sie funktioniert ohne Fehlermeldung und ohne Strg+Shift+Enter und schreibe ich zwischen diese Kopien weitere Zahlen, werden diese in den darunter stehenden Kopien korrekt angegeben. Allerdings werden auch Texte (wie z.B. die Spaltenüberschriften "Position") mit berücksichtigt, sodass ich die Formel unten um eine Zeile "gekürzt" habe (24->23), dann wieder einmalig mit Strg+Shift+Enter bestätigen musste und danach diese Zelle beliebig hoch und runter kopieren kann.

Versuche ich das gleiche jedoch mit z.B. 4 oder 12 anstelle von 1 an den 3 "Startstellen" in der Formel, bekomme ich immer "0" 

Kann man die Formel auch von den Startwerten her verändern ? Und ausschließlich auf Zahlen abzielen lassen ?

Gruß 

Gernot

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

ach so, eigentlich ändere ich ja sogar auf

Code:

---

{=INDEX(C$1:C23;MAX((C$1:C23<>"")*ZEILE($1:23)))+1}

---

weil ich ja immer 1 mehr als die unterste Zahl haben will, aber dann bringen Texte den "#WERT!"-Fehler 

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

hmmm,

schon etwas länger her.

basierend auf folgende Suchworte (Hint: meist reichen 3 Stichworte, sie müssen halt sinnvoll sein :-))

excel letzten wert aus spalte ausgeben

aka Zelle A14

Code:

---

={INDEX(A:A;MAX(ISTZAHL(A$1:A13)*ZEILE(A$1:A13)))+1}

---

geschweifte Klammer ist ja schon bekannt
Sollte beliebig kopiertbar sein.

Soll er erst ab Zeile 10 auswerten A$1 in A$10 ändern.

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Der letzte Wert in einer Spalte + 1:

={INDEX(C1:C1004;MAX((C1:C1004<>"")*ZEILE(1:1004)))+1}

------------------
Gruß Pille

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Falls du automatische Nummerierung gemeint hast:
Bereich markieren - Als Tabelle formatieren mit Überschriften - unter Entwurf Tabellennamen vergeben. Spaltenname Nr
=ZEILE()-ZEILE(tabNummer#Kopfzeilen];[Nr) in 1.Zeile einfügen.

tabNummer ist der Tabellenname.

Zeile löschen - Nummerierung wird angepasst. Zeile einfügen ebenfalls

------------------
- Ingo -

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Da ist was abhanden gekommen:

"=ZEILE()-ZEILE(tabNummer#Kopfzeilen];[Nr)"

------------------
- Ingo -

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Nummerierung.xlsx.txt

Die eckigen Klammern werden beim Kopieren unterschlagen. Ich hänge eine Datei an.

------------------
- Ingo -

[Diese Nachricht wurde von infiel_thomas am 19. Apr. 2018 editiert.]

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP