Frage von Marcopietro, 92

In den Zellen a1 bis a10 stehen zB 1,2,3 15,19, 22,31,32,33. Ich möchte nun wissen in wievielen Zahlenblöcken Zahlen hintereinander vorkommen Hier also 2?

Expertenantwort
von Oubyi, Community-Experte für Excel, 67

Welches Programm?
In Excel ließe sich das mit einer Hilfsspalte lösen:
B1: =SUMME(B2:B10) [Hier wird das Ergebnis angezeigt]
B2: =WENN(UND(A2-A1=1;A3-A2<>1);1;0)
und runterkopieren.

Klappt es? Teste mal genau.

P.S.: Ich füge einfach mal Excel zu Deinen Themen hinzu, kann ja nicht schaden, denke ich.

Kommentar von Marcopietro ,

Hallo Oubyi, danke für deine Lösung funktioniert genau so wie ich es möchte. Schade, dass es eine Hilfspalte braucht, Verwende es in einem alten Makro4. Danke Markus

Kommentar von Oubyi ,

Zum weiter grübeln hatte ich gestern Nacht keine Lust mehr.
Und jetzt hat mir  Suboptimierer (DH!) in der Antwort von lamiam die "Arbeit" abgenommen.

Expertenantwort
von Iamiam, Community-Experte für Excel, 54

ebenfalls xl, vermutlich auch openOffice & Consorten:

=SUMMENPRODUKT((A2:A10-A1:A9=1)*1)

funktioniert ohne Hilfsspalte

Musst nur drauf achten, dass die beiden Bereiche gleiche Zellzahlen aufweisen.


Kommentar von Marcopietro ,

Hallo Lamiam, danke für Deine Lösung. Sie wäre genial ohne Hilfsspalte. Sie zeigt mir aber an wieviele Zahlen hintereinender vorkommen. Ich bräuchte aber als Lösung wieviele Zahlenblöcke eine Reihenfolge hat. Also bei 1,2,3,5,10,11,12,13,45,49,51,52. Zählenblöcke sind 1,2,3 und 10,11,12,13 und 51,52. Das Resultat sollte also hier 3 ergeben. Lösung wird für Ferienwochenbezüge verwendet. Gruss Markus

Kommentar von Suboptimierer ,

Du hast Die Anzahl der Elemente der Blöcke -1 pro Block.

Du musst davon noch die Mittelelemente pro Block abziehen. Es bleibt pro Block nur noch ein Element:

=SUMMENPRODUKT((A2:A12-A1:A11=1)*1)-SUMMENPRODUKT((A2:A12-A1:A11=1)*(A2:A12-A3:A13=-1)*1)
Kommentar von Iamiam ,

in EINEM Summenprodukt mit einer Bedingung mehr:

=SUMMENPRODUKT((A2:A10-A1:A9=1)*(A3:A11-A1:A9<>2))

Formel darf natürlich nicht in A11 stehen!

Kommentar von Marcopietro ,

Hallo Lamiam, vielen Dank für Deine Formel. Es funktioniert genau so wie ich es möchte.....Leider habe ich vergessen zu sagen, dass es eigentlich doppelt so viele Spalten sind, ZB a1 : a14. In der für die Zahl vorgelegten Spalte kann auch ein Buchstabe stehen oder leer sein. Also Buchstaben in a1;a3;a5;a7;a9;a11;a13 Zahlen in a2,a4,a6,a8,a10;a12;a14 Diese Felder können teilweise auch leer sein. Sobald ich in der Zelle a3 als Beispiel einen Buchstaben eingebe wird ein Fehler gemeldet. Kann man da was machen. Oder ev ein Makro??? Danke Markus

Kommentar von Iamiam ,

grrrr...

Bevor ich nochmals von falschen Voraussetzungen ausgehe, einige Klärungen:

  • zwei Spalten a1:a14??? Du meinst Doppelzeilen bzw 2-Zeilen-Abstände der Zahlen?
  • Und "kann": mal so, mal so?
  • Und sollen -wenn immer nur die geraden gefüllt sind- diese Zahlen dann immer den Abstand 1 haben oder vllt 2, weil es ja 2 zeilig ist?

Schreibe mal 4 oder 5 typische Folgen für A1:A14 auf (Texte nur als "tx", die Länge ist -nur für die Darstellung- wichtiger als der Text-Inhalt), vllt kann ich dann klarer sehen, was machbar ist bzw welcher Aufwand zur Lösung nötig ist. Vermutlich gehts dann doch nicht ohne Hilfsspalte, aber mal sehen.

Kannst Du denn nicht die Texte in eine Spalte daneben schreiben? das wäre die einfachste Lösung! Oder als Kommentare? Das müsste ich nämlich sonst über eine komplizierte Hilfsspaltenformel bewirken, wobei deren Rückbezug fast unmöglich ist (mit Makro schon, aber was mit Formeln geht, sollte man mit Formeln machen: Ich selbst möchte gerne zum kostenlosen Libre Office wechseln, geht aber wegen meiner vielen Makros nicht, Formeln werden dagegen anstandslos umgesetzt!)

Kommentar von Marcopietro ,

Ja schade, kann ich dir hier nicht einen Ausschnitt der Tabelle zeigen. Das ganze läuft mit Makro4 von früher darum muss ich bei Excel bleiben. Es ist eine Ferienliste in welcher für 7 Ferienwochen Zellen bestehen und davor jeweils eine Zelle in der man ein x eingeben kann und damit anzeigt, dass man das entsprechende Wochenende ebenfalls als Ferien braucht. (Die Ferien beginnen sonst am Montag) Also insgesamt 14 Zellen. Bei Kollegen die weniger als 7 Ferienwochen beziehen können, werden die gesperrten Wochen mit 3X als gesperrt markiert. Also im Zahlenfeld sind Zahlen drin XXX oder leer. Die Eintragungen der Wochenzahlen erfolgen aufsteigend. Im der Wochenendzelle x oder leer. Die Einträge sind in den Spalten von N bis AA. Im Prinzip suche ich die Anzahl Zahlenblöcke hintereinander in allen 14 Zellen ohne die Buchstaben zu berücksichtigen. Das ist die Krux. Gruss und Dank Markus

Kommentar von Ninombre ,

Wenn man nach diversen Versuchen mit den Formeln nicht weiterkommt, kann es sinnvoll sein, den Aufbau der Tabelle zu hinterfragen. Wäre es für die Auswertung nicht einfacher, wenn nur die 7 Ferienwochen als Spalten verwendet werden und im Inhalt der Zellen zwischen Urlaub ohne Wochenede und Urlaub mit Wochenende unterschieden wird? Also U und UW meinetwegen?

Vielleicht wird wird die Problemstellung auch transparenter, wenn Du beschreibst, was das Ergebnis der zusammenhängenden Zahlenblöcken aussagt. Die obigen Beispiele wie 1,2,3,5,10,11,12,13,45,49,51,52. passen nicht zur Beschreibung mit 14 Spalten zw. N und AA.

Kommentar von Iamiam ,

ich konnte nicht erkennen, dass Du auf meinem ●1 und ●3 meiner Fragen eingegangen wärst.

Aber versuchs mal mit folgender Formel, sie wäre tolerant gegen Leerzellen und Texteinträge:

{=SUMMENPRODUKT((WENNFEHLER(R5:R12-R4:R11;0)=1)*(WENNFEHLER(R6:R13-R4:R11;0)<>2))}

Achtung: Dies ist eine Matrixformel! Die geschweiften {Klammern} NICHT miteingeben, sondern die Formel anstatt mit einfachem Enter mit der Kombination (gleichzeitig) Strg+Shift+Enter abschließen (nach jeder Neubearbeitung wieder!). Die {Klammern} entstehen dann automatisch.
Schreibtechnisch nimmst Du am besten die rechte Strg- und Umschalttaste, dann gehts mit einer Hand und lässt sich auch leichter merken!  - - -
Noch ein Tipp: Wenn Du in der Bearbeitungszeile einen selbständigen Formelteil markierst und F9 drückst, erhältst Du ein(e) Ergebnis{liste}. Allerdings dann die Zelle mit Esc verlassen (oder rückgängig machen), Enter würde das gezeigte Ergebnis festschreiben!

Kommentar von Iamiam ,

hab vergessen, auf Spalte A umzuschreiben:

{=SUMMENPRODUKT((WENNFEHLER(A5:A12-A4:A11;0)=1)*(WENNFEHLER(A6:A13-A4:A11;0)<>2))}

Kommentar von Iamiam ,

ach ja, die Zeilen differieren auch noch, aber jetzt mag ich nicht mehr, kriegst Du sicher selber hin! Aber nochmal: auch bei Korrekturen die Neueingabe mit Strg+Alt+Entf vornehmen!

Kommentar von Iamiam ,

ist regelmäßig jede 2.Zeile auszuwerten, geht das auch noch ohne Riesenaufwand:

{=SUMMENPRODUKT((WENNFEHLER(A4:A12-A2:A10;0)=1)*(WENNFEHLER(A6:A14-A2:A10;0)<>2))}

ist auch eine Matrixfunktion. (6 Zahlen in A2:A12)

Sehr kompliziert würde es, wenn die Zahlen-und Text-/Leerzellen unregelmäßig kommen.

Positive Ausnahme: geradzahlige Leer/Textzellenpaare zwischendrin.

Kommentar von Suboptimierer ,

Na, nu veralbere den Fragesteller doch nicht ;)

Pssst: Er meint Strg + Shift + Enter.

Kommentar von Iamiam ,

Ja, der Affengriff hat sich offenbar in tiefere Schichten des Gehirns eingefressen als der Abschluss von xl-Matrixformeln...

Ob das ein Beweis für unsere Abstammung ist? Bin sicher, dass das schon irgendjemand für schlüssig findet!

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten