Werte aus Liste Filtern nach Suchkriterien

[shift-del]

Die Formel von shift-del (Spalte H) sucht die größten Wert aus der Spalte F (Zählerstände) in Abhängigkeit von Spalte A (Liegenschaft) raus und zieht von Ihr die nächst kleineren Zählerstand ab.

Genau das was du wolltest:

In Spalte G möchte ich nun abhängig von der Liegenschaft den größten Wert vom nächst kleinerem Wert abziehen.

Aber vermutlich möchtest du etwas anderes.
Schon mal den Ansatz von Dieter63 ausprobiert?

[willi356]

Genau das was du wolltest:
Aber vermutlich möchtest du etwas anderes.
Schon mal den Ansatz von Dieter63 ausprobiert?

Genau das was du wolltest:
Ja stimmt, eingeschränkt. Diese Berechnung sollte aber nicht nur für die letzten Werte (größten Werte) durchgeführt werden, sondern für jede Zeile. Zur Erklärung:
Eine Nebenkostenabrechnung wird für den Zeitraum eines Jahres gemacht. Das muss nicht immer vom 1.1 bis 31.12. sein, aber umschließt immer ein Jahr. Jetzt kommt es vor, dass man von verschiedenen Nebenkosten Rechnungen bekommt die nicht genau den Abrechnungszeitraum treffen. (Kann man in der letzten hochgeladenen Datei sehen). Die dort vermerkten Datums in den Spalten B und C ist der Zeitraum in dem die Wassermenge (Spalte G - Verbrauch) verbraucht wurde. Dies ist meistens nicht Deckungsgleich mit dem Abrechnungszeitraum. Also errechne ich Tagesgenau den Anteil der Rechnung der in der Abrechnungszeitraum gehört. Und das bei jeder eingetragenen Rechnung. Somit können also mehrere der hier gelisteten (Zeilen) Daten in den Abrechnungszeitraum fallen. Dies ermittle ich aber in einer anderen Spalte. In einer weiteren Spalte wird dann der Anteil der Rechnung ermittelt, der in den Abrechnungszeitraum gehört.
Deswegen brauche ich nicht nur die Berechnung des größten Wertes minus des nächstkleineren, sondern auch den Wert vom nächstkleineren minus dem nächstnächstkleineren, usw. usw.
Oder: Wenn es für die Liegenschaft 1  8 Zählerstände gibt, muss errechnet werden:
Zählerstand 8 minus Zählerstand 7
Zählerstand 7 minus Zählerstand 6
Zählerstand 6 minus Zählerstand 5

usw. usw

Und am besten mit der Berücksichtigung das die Zählerstände nicht aufsteigend oder absteigend pro Liegenschaft auf einander folgen.

Ich hoffe das war jetzt nachvollziehbar.


Hab den Ansatz von Dieter durchgespielt.
Zur Info: Die komplette Datei, (dies hier ist nur ein kleiner Ausschnitt), berechnet eine komplette Heiz- und/oder Nebenkostenabrechnung für Liegenschaften. Auch wenn ich Sie nicht kommerziell vertreiben will, werden mit dieser Datei Leute arbeiten, die von Excel wenig Ahnung haben. Oder anders formuliert: Das Ding muss Bedienerfreundlich sein. Selbst das Sortieren von Daten kann schon ins Auge gehen.
Die einzelnen Blätter werden am Ende Passwort geschützt und die Formeln ausgeblendet.
Und auch wenn ich beim Schützen des Blattes angeben kann, dass das Sortieren erlaubt ist, weis ich nicht wieviel Support ich geben muss.

Mir wäre es lieber wenn die Reihenfolge, in der die Datensätze stehen egal wären, dann kann der Benutzer die Daten einfach eintippen, und die Berechnung würde automatisch erfolgen.
Also wenn alles andere nicht klappt, dann wäre dies wohl die einzige Möglichkeit.

[WillWissen]

Hi Willi,

mir fallen immer wieder Sätze wie diesen

Auch wenn ich Sie nicht kommerziell vertreiben will, werden mit dieser Datei Leute arbeiten, die von Excel wenig Ahnung haben. Oder anders formuliert: Das Ding muss Bedienerfreundlich sein. Selbst das Sortieren von Daten kann schon ins Auge gehen.

auf. Und stoßen mir auch auf, denn es wird von Unfähigkeit der MA ausgegangen. Es wäre doch der einfachste und auch notwendige Weg, den MA die Datei zu erklären. Selbst XL-Kundigen kann ich nicht eine x-beliebige Datei ohne Einweisung vor die Nase setzen.

[willi356]

Hi Willi,

mir fallen immer wieder Sätze wie diesen

auf. Und stoßen mir auch auf, denn es wird von Unfähigkeit der MA ausgegangen. Es wäre doch der einfachste und auch notwendige Weg, den MA die Datei zu erklären. Selbst XL-Kundigen kann ich nicht eine x-beliebige Datei ohne Einweisung vor die Nase setzen.

Also da stimme ich dir ja vollkommen zu. 
Und glaub mir, die bekommen auch eine gründliche Einweisung von mir, keine Frage.
Aber, es ist immer besser, wenn möglich, die Programmierung so zu gestalten, dass der User immer die gleiche Arbeitsweise in den einzelnen Masken vorfindet. Die Datei besteht aus 28 Arbeitsblättern. Wenn jetzt in jedem dieser Arbeitsblätter eine Ausnahme exestiert, der Art:

Hier musst du erst sortieren, in Blatt 23 muss du erst gucken ob in Spalte A diese Bedingung stimmt, in Blatt 15 erstmal alles grün machen, usw.

dann muss zu viel erklärt werden, und der Nutzer muss sich zu viel merken. Besser is, solche Dinge durch Programmierung vor weg zu nehmen.

Ist zumindest meine Meinung und meine Erfahrung.

[shift-del]

dann muss zu viel erklärt werden, und der Nutzer muss sich zu viel merken. Besser is, solche Dinge durch Programmierung vor weg zu nehmen.

Dann programmiere mal das Sortieren. Entweder mit VBA oder PQ.
Aber vermutlich kommt PQ auch nicht in Frage.

[willi356]

Dann programmiere mal das Sortieren. Entweder mit VBA oder PQ.
Aber vermutlich kommt PQ auch nicht in Frage.

Darf ich jetzt daraus schließen dass Du raus bist aus der Nummer? Denn eigentlich wollte ich nicht sortieren.

[shift-del]

Du darfst daraus schließen dass das Sortieren unerlässlich ist.
Und bei VBA bin ich raus.

[willi356]

Du darfst daraus schließen dass das Sortieren unerlässlich ist.

Also, ich das getestet: Sobald ich das Blatt schütze ist nix mehr mit sortieren. Denn leider hat Microsoft es nicht geschafft beim Schützen die intelligente Tabellen als Ausnahme zu programmieren. Also wenn Blattschutz -> nix mehr mit sortieren.

[schauan]

Hallöchen,

so?

Arbeitsblatt mit dem Namen 'Testseite'
	A	B	C	D	E	F	G	H	I	J	K
4	Liegenschaft	von	bis	Tage	Betrag	Haupt zähler	Verbrauch nach Rechnung	shift-del	Jörg I	Jörg IV	schauan
5	1	10.03.2003	10.03.2003	1		0 m³		398		276	0
6	1	11.03.2003	19.08.2004	528	596,71 €	276 m³	276	398		46	276
7	1	20.08.2004	04.11.2004	77	97,73 €	322 m³	46	398		74	46
8	1	04.11.2004	31.03.2005	148	165,32 €	396 m³	74	398		98	74
9	1	01.04.2005	03.11.2005	217		494 m³	98	398		200	98
10	1	04.11.2005	31.10.2006	362	434,85 €	694 m³	200	398		25	200
11	1	01.11.2006	31.12.2006	61	59,31 €	719 m³	25	398		171	25
12	1	01.01.2007	30.10.2007	303	369,75 €	890 m³	171	398		37	171
13	1	31.10.2007	31.12.2007	62	78,88 €	927 m³	37	398		183	37
14	1	01.01.2008	30.10.2008	304	388,69 €	1.110 m³	183	398		190	183
15	1	31.10.2008	30.07.2009	273	455,15 €	1.300 m³	190	398		58	190
16	1	01.08.2009	03.11.2009	95	59,25 €	1.358 m³	58	398		232	58
17	1	04.11.2009	28.10.2010	359	485,27 €	1.590 m³	232	398		234	232
18	1	29.10.2010	31.10.2011	368	491,30 €	1.824 m³	234	398		258	234
19	1	01.11.2011	29.10.2012	364	528,25 €	2.082 m³	258	398		249	258
20	1	30.10.2012	31.10.2013	367	517,29 €	2.331 m³	249	398		241	249
21	1	01.11.2013	31.10.2014	365	526,81 €	2.572 m³	241	398		141	241
22	1	01.11.2014	26.10.2015	360	546,59 €	2.713 m³	141	398		247	141
23	1	27.10.2015	25.10.2016	365	587,74 €	2.960 m³	247	398		219	247
24	1	26.10.2016	25.10.2017	365	543,37 €	3.179 m³	219	398		398	219
25	1	26.10.2017	06.06.2018	224	395,01 €	3.577 m³	398	398	398	0	398
26	2	26.10.2017	06.06.2018	224	411,00 €	22 m³	22	27		0	22
27	3	19.05.2017	15.05.2018	362	511,00 €	15 m³	15	19		0	15
28	2	07.06.2018	31.10.2018	147	125,00 €	51 m³	29	27		27	29
29	2	01.11.2018	31.12.2018	61	78,00 €	78 m³	27	27	27	0	27
30	3	16.05.2018	30.09.2018	138	111,00 €	65 m³	50	19		19	50
31	3	01.10.2018	15.02.2019	138	117,00 €	84 m³	19	19	19	0	19

Zelle	Formel
H5	=MMULT({1.-1};AGGREGAT(14;6;$F$5:$F$31/($A$5:$A$31=$A5);{1;2}))
I5	{=WENN(ZÄHLENWENN(A5:$A$31;A5)=1;MMULT({1.-1};AGGREGAT(14;6;$F$5:$F$31/($A$5:$A$31=$A5);{1;2}));"")}
J5	=WENN(A5=A6;F6-F5;0)
K5	=F5-WENNFEHLER(VERWEIS(2;1/($A$4:A4=A5);$F$4:$F$1000);0)

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[willi356]

Das passt ja so was von .....

Klasse ich danke dir.