Medienabrechnung :: Office-Hilfe.com - Wir lösen Ihre Probleme mit Microsoft Office

Einloggen, um private Nachrichten zu lesen

Downloads

[Excel 2002] Medienabrechnung

Office-Hilfe.com - Wir lösen Ihre Probleme mit Microsoft Office Foren-Übersicht » Microsoft Excel Hilfe

Autor

Nachricht

TGMer
Newbie
Newbie

Anmeldedatum: 01.08.2007
Beiträge: 9

Verfasst am: 24.01.2008, 22:14

hallo ihr fachmänner!!!

vorab möchte ich sagen, dass ich wirklich eine excel-niete bin!!! für euch ist mein problem bestimmt kein thema (hoffe ich)! Embarassed

ok....darum gehts...
ich möchte eine liste erstellen, in der ich die im objekt abgelesenen zählerstände eintragen kann.
aus den zählerständen wird dann automatisch mit dem vormonat ein verbrauch ermittelt und ausgegeben.
aus allen schon angegebenen verbräuchen des jahres wird ein mittelwert errechnet und in die spalte vorne eingetragen.
so weit...so gut! jetzt kommts!
sollte der verbrauch, den ich dann aktuell für irgend einen monat mal eintrage, 20% höher oder niedriger sein als der mittlere verbrauch aller monate, soll die zahl sich dann rot färben. das habe ich mit der bedingten formatierung realisiert.
das problem ist jetzt aber, dass excel diesen "ausreißer" auch zur mittelwertermittlung heranzieht. das soll aber nicht passieren, weil sonst ja der mittelwert verfälscht wird!
wie kann ich excel sagen, dass der mittelwert nur aus den zahlen berechnet werden soll, die in dem +/- 20% bereich liegen?????
ich hoffe, ihr könnt mir da weiter helfen! habe zum besseren verständnis mal meine angefangene tabelle angehängt.

dazu muss ich sagen, dass diese lösung nur eine idee von mir ist! wenn jemand einen besseren vorschlag für die problematik der zähleraufnahme hat, bin ich natürlich für alles offen und dankbar!!!!!
ich meine....ich kann ja nicht der erste mensch sein, der vor dieser aufgabe steht!!!
vielleicht hat ja auch jemand schon sowas in der art fertig!!

ok...ich bedanke mich schon mal vorab für eure hilfe!!!!!!! Exclamation

Medienabrechnung.xls

Beschreibung:

Download

Dateiname:

Medienabrechnung.xls

Dateigröße:

18.5 KB

Heruntergeladen:

14 mal

schatzi
Moderator

Anmeldedatum: 09.12.2006
Beiträge: 5570

Verfasst am: 24.01.2008, 22:30

Hallo!

Dein Beispiel ist etwas unglücklich gewählt, da die bisher eingetragenen Werte (F4 und I4) BEIDE außerhalb deines 20%-Korridors liegen...
Was soll hier als "bereinigter" Mittelwert denn nun ausgegeben werden?

Zum anderen: Den "normalen" Mittelwert (also MIT Ausreißern) brauchst du doch auch weiterhin als "Eich-", bzw. Referenzwert für deinen 20%-Korridor, oder verstehe ich da etwas falsch?

_________________

Viele Grüße vom Schatzi
------------------------
Dies ist meine Signatur und sie trägt eigentlich nichts zur Lösung deines Problems bei. Sollte sie es dennoch tun, dann bist du definitiv im falschen Forum...

Ein Feedback zum ursprünglichen Problem wäre trotzdem nett!
Windows XP, Office2000, Office2007

TGMer
Newbie
Newbie

Anmeldedatum: 01.08.2007
Beiträge: 9

Verfasst am: 24.01.2008, 22:56

oh das ging ja schnell!! super!
also pass auf....
den verbrauch für januar muss man per hand eintragen, da man ja keinen wert zum brechnen hat aus dem vorjahr. den hab ich willkührlich gewählt. einen einfachen wert...10 m³ um das ergebnis besser kontrollieren zu können.
also wäre ja nach der eintragung für januar der mittelwert 10 m³.
dann kommt der februar. hier hab ich mit absicht einen viel höheren wert genommen um zu gucken ob das mit der bed. formatierung klappt.

zum thema mittelwert muss ich vielleicht noch was erklären.
der eigentliche mittelwert aus allen monaten ist mir eigentlich egal!
es geht darum.....
ist ein mieter zum beispiel mal im urlaub wird er logischer weise weniger wasser verbrauchen als sonst. der wert wird aber sicherlich in der +/- 20% grenze liegen. also kann er auch zur berechnung des mittelwertes herangezogen werden.

verbraucht ein mieter aber mehr wasser als mittelwert + 20% ist irgendwas nicht in ordnung...zum beispiel rohr an einer stelle undicht und man hat es nicht mitbekommen! also ist der mittelwert für mich die kontrolle, auf die ich mich in den einzelnen monaten beziehe.
ist jetzt ein ausreißer dabei, darf der natürlich nicht in den mittelwert mit rein! sonst verfälscht er den ja und ich habe keine korrekte kontrolle mehr in den folgemonaten.
mensch...ganz schön kompliziert! ich hoffe du hast das vertsanden! Confused

TGMer
Newbie
Newbie

Anmeldedatum: 01.08.2007
Beiträge: 9

Verfasst am: 24.01.2008, 22:59

eigentlich ist ja nur interessant, ob er 20% drüber liegt....

schatzi
Moderator

Anmeldedatum: 09.12.2006
Beiträge: 5570

Verfasst am: 24.01.2008, 23:12

Hallo!

Aber woher soll Excel nun wissen, ob der Januar- oder der Febraur-Wert der Ausreißer ist?
Das ist mir noch nicht ganz klar...

Wirf doch mal einen Blick auf die Funktion GESTUTZTMITTEL:

Excel-Hilfe hat Folgendes geschrieben:

Gibt den Mittelwert einer Datengruppe zurück, ohne die Randwerte zu berücksichtigen. GESTUTZTMITTEL berechnet den Mittelwert einer Teilmenge der Datenpunkte, die darauf basiert, dass entsprechend des jeweils angegebenen Prozentsatzes die kleinsten und größten Werte der ursprünglichen Datenpunkte ausgeschlossen werden. Diese Funktion können Sie immer dann verwenden, wenn bei der Auswertung keine Daten berücksichtigt werden sollen, die als Ausreißer anzusehen sind.

Syntax

GESTUTZTMITTEL(Matrix;Prozent)

Matrix ist eine Matrix oder Gruppe von Werten, die ohne ihre Ausreißer gemittelt wird.

Prozent ist der Prozentsatz der Datenpunkte, die nicht in die Bewertung eingehen sollen. Ist beispielsweise Prozent = 0,2, wird ein Datensatz von 20 Punkten um 4 Punkte (20 x 0,2) verringert, und zwar um die zwei größten sowie die zwei kleinsten Werte des Datensatzes.

Hinweise

Ist Prozent <0> 1, gibt GESTUTZTMITTEL den Fehlerwert #ZAHL! zurück.
GESTUTZTMITTEL rundet die Anzahl der nicht berücksichtigten Datenpunkte auf das kleinste Vielfache von 2 ab. Ist Prozent = 0,1 (10 Prozent), so ergeben sich bei 30 Datenpunkten 3 auszuschließende Punkte. Aus Symmetriegründen lässt GESTUTZTMITTEL in diesem Fall den kleinsten und den größten Wert der Datenmenge (also nur 2 Werte) unberücksichtigt.

Sie rechnet zwar etwas anders als du wünscht, aber vielleicht reicht das dir ja schon (oder ist sogar besser).

Sonst müssten wir irgendwie doch auf den normalen Mittelwert als Referenz zurückgreifen und das Ganze in einer Matrixformel verpacken.
Das könnte dann so aussehen wie im Anhang:
Nur Ausreißer nach oben werden ignoriert.

Medienabrechnung-1.xls

Beschreibung:

Download

Dateiname:

Medienabrechnung-1.xls

Dateigröße:

22.5 KB

Heruntergeladen:

19 mal

Ein Feedback zum ursprünglichen Problem wäre trotzdem nett!
Windows XP, Office2000, Office2007

schatzi
Moderator

Anmeldedatum: 09.12.2006
Beiträge: 5570

Verfasst am: 24.01.2008, 23:20

Diese Formel reicht auch schon:

=MITTELWERT(WENN((REST(SPALTE(F:AM);3)=0)*(F4:AM4<1,2*D2);F4:AM4))

Achtung Matrixformel: Die Eingabe der Formel mit der Tastenkombination Strg+Shift+Enter abschließen!

Ein Feedback zum ursprünglichen Problem wäre trotzdem nett!
Windows XP, Office2000, Office2007

TGMer
Newbie
Newbie

Anmeldedatum: 01.08.2007
Beiträge: 9

Verfasst am: 24.01.2008, 23:29

hey....beide lösungen gefallen mir sehr gut!!! klasse! vielen dank!!! Wink

bei dem versuch die aufgabe heute zu lösen, bin ich an ein generelles problem bei excel gestoßen, das ich so nicht lösen konnte.
ich wollte irgendwie versuchen, excel zu sagen, dass erst der errechnete verbrauch darauf kontrolliert werden soll, ob er 20% höher oder niedriger ist, als der bisherige mittelwert. ist er innerhalb des bereiches, wird ein neuer mittelwert gebildet, mit dem neuen verbrauch. liegt der verbrauch in diesem monat außerhalb, wird kein neuer mittelwert gebildet.

TGMer
Newbie
Newbie

Anmeldedatum: 01.08.2007
Beiträge: 9

Verfasst am: 24.01.2008, 23:54

mensch schatzi!!! jetzt hast du mir so super geholfen und ich dachte das ist es nun aber.... Confused

ich habe ja mehr als einen zähler. die sollen ja untereinander aufgeführt werden. da du dich in deinen formeln aber auf D2 beziehst, geht das ja irgendwie nicht in den nächsten zeilen!
oder ich füge noch eine spalte ein mit dem tatsächlichen mittelwert für jeden einzelnen zähler. und auf die zelle kann ich dann in deiner formel immer wieder verweisen!? so müste es doch gehen oder?

schatzi
Moderator

Anmeldedatum: 09.12.2006
Beiträge: 5570

Verfasst am: 25.01.2008, 00:00

Hallo!

Ganz einfach:

=MITTELWERT(WENN((REST(SPALTE(F:AM);3)=0)*(F4:AM4<1,2*D$2);F4:AM4))

und dann runterkopieren.

Ein Feedback zum ursprünglichen Problem wäre trotzdem nett!
Windows XP, Office2000, Office2007

TGMer
Newbie
Newbie

Anmeldedatum: 01.08.2007
Beiträge: 9

Verfasst am: 25.01.2008, 00:06

na nee......eben nicht.
jeder zähler hat doch seine eigenen verbräuche und seinen eigenen mittelwert.
also benötige ich doch für jeden einzelnen zähler sozusagen eine eigene zelle "D2"!
oder hab ich da jetzt einen denkfehler??? Confused

schatzi
Moderator

Anmeldedatum: 09.12.2006
Beiträge: 5570

Verfasst am: 25.01.2008, 00:17

Ääähhhh, ja natürlich... Embarassed

Dann mach es über eine Extraspalte, in der du die ungeglätteten Mittelwerte aufführst.
Oder du baust es direkt in die Formel ein:

=MITTELWERT(WENN((REST(SPALTE(F:AM);3)=0)*(F4:AM4<1,2*MITTELWERT(F4;I4;L4;O4;R4;U4;X4;AA4;AD4;AG4;AJ4;AM4));F4:AM4))

Ein Feedback zum ursprünglichen Problem wäre trotzdem nett!
Windows XP, Office2000, Office2007

TGMer
Newbie
Newbie

Anmeldedatum: 01.08.2007
Beiträge: 9

Verfasst am: 26.01.2008, 22:44

hallo nochmal

alles klar...so gehts!
vielen, vielen dank für die schnelle hilfe!
dafür gibts ne lupenreine 1,0 mit sternchen von mir!!! Wink

Office-Hilfe.com - Wir lösen Ihre Probleme mit Microsoft Office Foren-Übersicht » Microsoft Excel Hilfe

Gehe zu:

Du kannst keine Beiträge in dieses Forum schreiben.
Du kannst auf Beiträge in diesem Forum nicht antworten.
Du kannst deine Beiträge in diesem Forum nicht bearbeiten.
Du kannst deine Beiträge in diesem Forum nicht löschen.
Du kannst an Umfragen in diesem Forum nicht mitmachen.
Du kannst Dateien in diesem Forum posten
Du kannst Dateien in diesem Forum herunterladen

Powered by phpBB © 2001, 2002 phpBB Group :: FI Theme :: Alle Zeiten sind GMT + 1 Stunde
Deutsche Übersetzung von phpBB.de