Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1876to1880
1876to1880
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Berechnung Übernachtungskosten

Forumthread: Berechnung Übernachtungskosten

Berechnung Übernachtungskosten
04.04.2022 19:09:47
Henning
Hallo zusammen,
ich benötige zur Berechnung von Übernachtungskosten für ein Ferienhaus eure Hilfe. Insbesondere die Übergänge in den einzelnen Preiskategorien macht mir zu schaffen.
Anbei einmal die Tabelle:
https://www.herber.de/bbs/user/152241.xlsx
Vielen Dank :-)
Anzeige

35
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Berechnung Übernachtungskosten
04.04.2022 19:18:45
{Boris}
Hi,
C15:
=SUMMENPRODUKT(SVERWEIS(ZEILE(INDIREKT(A15&":"&B15));A1:C6;3))
Aber das Ergebnis stimmt mit Deinem nicht ganz überein. Irgendwo hast Du imho nen Fehler drin.
VG, Boris
AW: Berechnung Übernachtungskosten
04.04.2022 22:01:14
Henning
Hallo Boris,
vielen Dank für schnelle Rückmeldung. Da hast du völlig recht! Schande auf mein Haupt, der letzte Tag in meiner Berechnung muss natürlich ebenfalls 165 € kosten.
Ich wollte eigentlich extra mehr als einen Wechsel darstellen.
Habe das einmal angepasst:
https://www.herber.de/bbs/user/152248.xlsx
Ich habe deine Formel einmal eingefügt und bekomme 150 € als Ergebnis, muss ich noch irgendwas beachten?
Vielen Dank schon mal :-)
Anzeige
Wo kommt das @-Zeichen her?
04.04.2022 22:03:17
{Boris}
Hi,
das musst Du aus der Formel entfernen - dann wird auch korrekt gerechnet.
VG, Boris
AW: Wo kommt das @-Zeichen her?
04.04.2022 22:21:59
Henning
Ich sehe kein @ in der Formel - die habe ich direkt herauskopiert und eingefügt:
=SUMMENPRODUKT(SVERWEIS(ZEILE(INDIREKT(A15&":"&B15));A1:C6;3))
So steht es in meiner Formel - fehlt denn nicht auch ein Teil zum multiplizieren in der Formel?
Anzeige
AW: Wo kommt das @-Zeichen her?
04.04.2022 22:39:22
{Boris}
Hi,
bei mir sieht das so aus:
Userbild
und ohne @ kommt dann das richtige Ergebnis:
Userbild
Wobei Du meinen letzten Hinweis noch nicht beachtet hast, 1 Tag zu subtrahieren.
Die korrekte Formel wäre also:
=SUMMENPRODUKT(SVERWEIS(ZEILE(INDIREKT(A15&":"&B15-1));A1:C6;3))
VG, Boris
Anzeige
AW: Wo kommt das @-Zeichen her?
05.04.2022 07:21:52
SF
Ich hatte das bei Office-Loesung auch vor kurzem und mir wurde das geschrieben:
Ein angeblich eingeschlichenes @ kommt daher, dass derjenige, der das bemängelt, eine neues Excel hat und der, von dem die Datei kommt, noch ein altes Excel benutzt und nicht Strg-Alt-Enter bei der Eingabe der Formel gedrückt hat.
Wenn ein Excelkenner ein altes Excel verwenden würde, so würde er statt eines eingeschmuggelten @ fehlende {...}-Klammern bemängeln.
Anzeige
So ganz kapier ich es noch nicht...
05.04.2022 13:06:47
{Boris}
Hi Steve,
abgesehen davon, dass Strg-Alt-Enter sicher Strg-Shift-Enter heißen sollte, ist das hier doch von Haus aus gar keine Arrayformel im klassischen Sinn (also mit der speziellen Formeleingabe)...?
VG, Boris
AW: im Prinzip wohl, denn umgekehrt ...
05.04.2022 13:17:08
neopa
Hallo Boris,
... werden z.B. SUMMENPRODUKT()-Formel, die in Excel 365 erstellt worden sind und deren Datei dann in älteren Excelversionen eingelesen wird, dort mit {} gekennzeichnet, obwohl die mit größter Wahrscheinlichkeit in XL3065 nicht als solche erstellt waren.
Gruß Werner
.. , - ...
Anzeige
Danke für die Erkenntnis...
05.04.2022 13:24:12
{Boris}
Hi Werner,
...das wusste ich in der Tat noch nicht. Wundert mich aber dennoch, da SUMMENPRODUKT auch in den alten Versionen ohne die {} auskommt, so lange man nicht z.B. ein (Teil-)Array mit WENN erzeugt hat.
Nun denn!
VG, Boris
AW: trifft nicht nur auf SUMMENPRODUKT() zu ...
05.04.2022 13:49:01
neopa
Hallo Boris,
... sondern auch auf andere Formeln die Funktionen einsetzen, welche für die älteren Versionen so programmiert wurden, dass man sie da ohne den spez. Matrixformelabschluss eingeben muss wie z.B. u.a. auch spez. VERWEIS() , INDEX(), AGGREGAT()- ... Formeln.
Gruß Werner
.. , - ...
Anzeige
Das heißt also...
05.04.2022 13:57:16
{Boris}
Hi Werner,
...wenn Du hier ne Datei mit diesen Funktion einstellst, dass da bei mir (bzw. halt xl365) durchweg das @ erscheint?
VG, Boris
AW: vermutlich etwas anders ...
05.04.2022 14:13:54
neopa
Hallo Boris,
... aber sieh mal. In der kleinen Beispieldatei https://www.herber.de/bbs/user/152268.xlsx stehen in Spalte E Formeln ohne {}-Abschluss und in Spalte F mit solchem. Wie stellen sich die Formeln bei Dir dar?
Gruß Werner
.. , - ...
Anzeige
Keine Auffälligkeiten...
05.04.2022 14:21:44
{Boris}
Hi Werner,
...und somit auch keine @-Zeichen.
Formeln werden so dargestellt, wie von Dir eingegeben.
Userbild
VG, Boris
AW: ... für mich jetzt teilweise schon ...
05.04.2022 15:00:22
neopa
Hallo Boris,
... vor der Pandemie, hatte ich mal bei einer ehemaligen Kollegin, welche XL365 im Einsatz hatte, diese @ Zeichen vor solchen Formeln wie ich sie in Spalte E eingesetzt hatte gesehen, als sie eine Datei mit meine Formeln eingelesen hatte.(meine mich zumindest so zu erinnern). Mittlerweile sind ja aber sicherlich schon einige Updates über XL365 gelaufen.
Ich hab mir nun mal die Datei von Henning geladen. Mit Deiner Formel kann man in XL2016 nicht das von Dir erstellte Ergebnis erzielen sondern nur eben 150. SVERWEIS() ist in XL2016 noch nicht Arrayfähig. Eine einfache Lösungsformel für das was Henning in XL2026 anstrebt ist mir jetzt jedenfalls auch nicht gleich gelungen. Ich schau es mir morgen noch einmal an.
Das bei Dir erscheinende @ -Zeichen in seiner Datei kommt natürlich in XL2016 nicht vor. Mir "erklärt" sich das ähnlich so, wie ich es mir vor zwei Jahren "zusammengereimt" hatte. Dies wird aber in der neuen XL365 Version nun genauso wenig "verstanden, wie in den älteren XLVersionen Deine in XL365 funktionierende Formel offensichtlich noch nicht interpretiert werden kann.
Gruß Werner
.. , - ...
Anzeige
Noch ne neue Erkenntnis für mich...
05.04.2022 15:20:45
{Boris}
Hi Werner,
...ist:
SVERWEIS() ist in XL2016 noch nicht Arrayfähig
Sachen gibt`s ... ;-)
VG, Boris
AW: wie meinst Du das denn? ...
05.04.2022 16:38:56
neopa
Hallo Boris,
... das verstehe ich jetzt nicht wirklich. Du beschäftigst Dich doch schon viel länger mit Excel als ich und hast doch XL365 auch noch nicht so lange im Einsatz. Du bist doch darauf sicherlich schon früher gestoßen oder sehe ich das falsch?
@Henning,
eine Lösung ohne die Hilfszeilen in 9:11 aber mit einer Hilfsspaltenformel in H1:H6 hätte momentan, wenn Dir das reichen sollte. Diese gilt dann zunächst für das Datenpaar in A15:B15. Für weitere Datenpaare ab Zeile 16 kann diese Hilfsspalte aber einfach nach rechst kopiert werden.
In C15:C## reicht dann eine einfache Summenformel.
Gruß Werner
.. , - ...
Anzeige
Manche Dinge...
05.04.2022 17:52:46
{Boris}
Hi Werner,
...vergisst man halt - und das auch recht schnell...
Bestimmt war es mir seinerzeit bewusst - aber eben jetzt nicht mehr ;-)
VG, Boris
Was ergibt denn...
05.04.2022 17:55:51
{Boris}
Hi Werner,
...so ein SVERWEIS genau für ein Ergebnis vor xl365?
Userbild
VG, Boris
Anzeige
AW: Ergebnis ist da gleich dem ohne SUMME() owT
05.04.2022 18:29:14
neopa
Gruß Werner
.. , - ...
Also 1? ...oT
05.04.2022 19:03:25
{Boris}
VG, Boris
AW: ja und wenn D2:D1 getauscht wird, dann 3 owT
05.04.2022 19:05:59
neopa
Gruß Werner
.. , - ...
Anzeige
AW: wie meinst Du das denn? ...
06.04.2022 10:43:46
Henning
Also erstmal vielen Dank für die ganzen Erklärungen. Ich denke das wird in Zukunft häufiger auffallen, wenn man im Unternehmen und privat hier verschiedene Versionen benutzt!
@neopa C/Werner
kannst du mir deinen Lösungsansatz einmal erklären? Dann könnte ich ihn vielleicht auf meine Fall umbauen.
"eine Lösung ohne die Hilfszeilen in 9:11 aber mit einer Hilfsspaltenformel in H1:H6 hätte momentan, wenn Dir das reichen sollte. Diese gilt dann zunächst für das Datenpaar in A15:B15. Für weitere Datenpaare ab Zeile 16 kann diese Hilfsspalte aber einfach nach rechst kopiert werden.
In C15:C## reicht dann eine einfache Summenformel."
Ich weiß einfach nicht, wie ich aus dem Format 31.03.2022 - 15.06.2022 die Anfangs- und Endtage bekomme, sodass ich weiß wie viele Tage in dem jeweiligen Zeitraum sind.
Schade, dass die Formel von Boris hier bei mir nicht funktioniert :-(
mfg Henning
Anzeige
AW: dazu zunächst noch nachgefragt ...
06.04.2022 11:13:50
neopa
Hallo Henning,
... willst Du immer nur ein Datenpaar (A15:B15) auswerten oder auch mehrere? Wenn letzteres, stehen diese unmittelbar darunter? Ab welcher Spalte stehen in Deiner Datei keine Daten. Kennst Du Dich mit benannten Formeln aus? Ich habe momentan eine relativ lange Hilfsspaltenformel mit einer Anzahl von wiederholenden Formelteilen, die durch benannte Formelteile ersetzt werden könnten. Dadurch würde die Hilfsspaltenformel nicht nur kürzer sondern auch verständlicher.
Gruß Werner
.. , - ...
Anzeige
Am Einfachsten wäre es...
06.04.2022 14:13:22
{Boris}
Hi,
...wenn Du auf xl365 umsteigen würdest. Kostet in der Abo-Version € 69,- pro Jahr.
Der Sprung von xl2019 auf xl365 ist - ähnlich wie von 2003 auf 2007 - ein Quantensprung.
VG, Boris
AW: ... nicht immer ...
06.04.2022 16:05:54
neopa
Hallo Boris,
...es gibt öfters mehrere Kriterien, die für oder gegen einen Umstieg entscheiden, als nur der Preis. Und dabei lass ich mal meine bisherigen persönliche Gründe ganz außen vor. U.a. ist es nämlich oftmals der AG der darüber entscheidet.
Gruß Werner
.. , - ...
Anzeige
Da haben wir halt unterschiedliche Ansichten...
06.04.2022 17:07:25
{Boris}
Hi Werner,
...wobei ich Dich nicht verstehen kann, da Du Dich in der neuen Version doch so richtig formelmäßig austoben könntest - inkl. LAMBDA und allen Nebenfunktionen.
Das müsste Dir doch eigentlich das Herz aufgehen!
VG, Boris
AW: offensichtlich, aber ...
06.04.2022 20:07:10
neopa
Hallo Boris,
... ich leiste es mir halt bewusst, hier wie auch sonst, freiwillig auf so manch Neues zu verzichten. Dies obwohl ich frei in meiner Entscheidung wäre und bin. Natürlich reizte es mich manchmal schon etwas, mich auch mit den neuen Excel-Funktionen beschäftigen zu können. Doch brauchen tu ich es nicht wirklich. Und wie ich eben wieder festgestellt habe, kommt man aber auch ohne "Turbo" zum Ziel, eben nur halt später und mit mehr Aufwand. Ich steh schon lange nicht (mehr) unter Leistungszwang.
Gruß Werner
.. , - ...
Anzeige
Noch eine ganz simple Lösung...
06.04.2022 16:12:44
{Boris}
Hi,
liste alle Datümer untereinander auf, lies den jeweiligen Zimmerpreis per SVERWEIS pro Datum aus und summiere dann die Spalte - also im Prinzip das, was meine gepostete Formel in Deiner Excelversion noch nicht kann, in Einzelschritten.
Konkret z.B. in Deiner Beispieldatei:
A18: =A15
A19: =WENN(MAX(A$18:A18)=B$15-1;"";A18+1)
A19 so weit runterkopieren, wie ein Aufenthalt maximal dauern kann.
B18: =WENNFEHLER(SVERWEIS(A18;A$1:C$6;3);"")
und entlang der Spalte A runterkopieren.
Dann aus Spalte B nur noch die SUMME bilden - und Du hast die Gesamtkosten.
VG, Boris
Anzeige
AW: Noch eine ganz simple Lösung...
06.04.2022 17:37:55
Henning
Eine andere Excel-Version ist für mich aktuell nicht erstrebenswert, da ich für mein "normales" Arbeiten gut zurrecht komme und ich den Mehrwert zu den Mehrkosten nicht sehe.
@Werner:
Also in meiner Datei stehen die Datenpaare untereinander.
Meine Datei ist auch wesentlich größer und ich habe mir auch die einzelnen Tage bereits abgegriffen. Ich weiß als, welche einzelnen Tage betroffen sind für den jeweiligen Gast. Da die Daten in Zeilen aufgebaut sind, müsste ich quasi immer eine Zusatzzeile hinzugfügen um die Logik von Boris zu verwenden. Allerdings ist das nicht sehr "schön" :-)
Mit dem Namensmanager habe ich bisher nicht gearbeitet.
Ich hatte gehofft, dass es eine Formellösung gibt, die ich in eine Zelle packen kann (und mit einer neueren Version hätte es ja auch geklappt).
Daher danke ich euch für eure Hilfe! Freut mich, dass es hilfsbereite Menschen gibt, die sich auch so zeitnahe mit anderen Probleme beschäftigen! :-)
Ich werde dann wohl eine "unschöne" Variante verwenden, die aber für diesen einfachen Zweck definitv reicht!
In dem Sinne, besten Dank und alles gute für die Zukunft!
MfG Henning
Anzeige
Hier noch eine Lösung...
06.04.2022 17:59:08
{Boris}
Hi,
...mit nur einer Formel, die auch in Deiner Excelversion laufen sollte:
=SUMMENPRODUKT((ZEILE(INDIREKT(A15&":"&B15-1))>=MTRANS(A1:A6))*(ZEILE(INDIREKT(A15&":"&B15-1)) Und für alle Kritiker von volatilen Funktionen (hier: INDIREKT), auch in dieser Variante möglich:
=SUMMENPRODUKT((ZEILE(INDEX(A:A;A15):INDEX(A:A;B15-1))>=MTRANS(A1:A6))*(ZEILE(INDEX(A:A;A15) :INDEX(A:A;B15-1)) VG, Boris
Anzeige
AW: noch nicht ganz ...
06.04.2022 19:56:56
neopa
Hallo Boris,
... aber ich hab durch Deine Formel mein Problem gelöst, welches mich zunächst zu meiner Hilfsspaltenlösung geführt hatte.
Die -1 in der Formel führt zu einem falschen Ergebnis und in XL2016 bedarf die Formel trotzdem eines Matrixformelabschluss auch mit SUMMENPRODUKT() und da kann man dann auch gleich SUMME() nutzen.
Also die Formel so:
{=SUMME((ZEILE(INDEX(A:A;A15):INDEX(A:A;B15))&gt=MTRANS(A$1:A$6))*(ZEILE(INDEX(A:A;A15) :INDEX(A:A;B15))&lt=MTRANS(B$1:B$6))*MTRANS(C$1:C$6))}
und so ist die Formel auch nach unten kopierbar für weitere Datenpaare.
Gruß Werner
.. , - ...
Anzeige
Warum führt -1 ...
06.04.2022 21:39:40
{Boris}
Hi Werner,
...zu einem falschen Ergebnis? Der Abreisetag wird nicht mitgerechnet, oder?
VG, Boris
AW: da hab ich nicht alles richtig gesehen ...
07.04.2022 08:40:10
neopa
Hallo Boris,
... wenn der Abreisetag nicht angerechnet wird (was ja normalerweise ja auch der Fall ist), dann gehört natürlich die -1 in die Formel. Entschuldige bitte, hast natürlich Recht.
Nachfolgendes nicht zu meiner Entschuldigung sondern nur zur Erklärung: Ich hatte das Ergebnis immer nur mit dem von Dir grün hinterlegten Ergebniswert in Deinem Beitrag vom 04.04.2022 22:39:22 verglichen und dabei offensichtlich übersehen, dass diese ja nicht mit der Formel mit -1 erzeugt wurde, die Du darunter geschrieben hast.
Gruß Werner
.. , - ...
Anzeige
Alles gut! :-) ...oT
07.04.2022 10:18:19
{Boris}
VG, Boris
Fehlerbehebung
04.04.2022 19:47:42
{Boris}
Hi,
die Frage ist, ab WANN Du welchen Preis berechnest.
Der Kandidat kommt am 31.3. an - da berechnest Du ihm 150,- - richtest Dich also nach dem Datum in Spalte B.
Beim letzten Tag richtest Du Dich aber nach dem Datum in Spalte A - das ist nicht konsequent. In meinen Augen kostet die letzte Übernachtung nur 165,- (und nicht 210,-) - der selben Logik wie dem Anreisetag folgend.
Grundsätzlich muss in meiner Formel noch 1 Tag (der Abreisetag) abgezogen werden:
=SUMMENPRODUKT(SVERWEIS(ZEILE(INDIREKT(A15&":"&B15-1));A1:C6;3))
Dann passt es - mit Blick auf meine Ausführungen.
VG, Boris
Anzeige

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige