SVERWEIS statt Wert Bezug ausgeben

[Dietmar Wien]

  Beispieldatei.xlsx (Größe: 13,71 KB / Downloads: 5)
Hallo liebes "Clever-Excel-Forum",

ich hänge leider völlig, es sollen Umsatzzahlen von diesem Jahr mit dem Tagesgegenwert von letztem Jahr verglichen werden. Nun gibt es natürlich das Problem, das der Arbeitstag des einen Jahres nicht unbedingt auch ein Arbeitstag im letzten Jahr war. Um dem notwendigen #NV vorzubeugen, habe ich mir gedacht in dem Fall einfach den Mittelwert von drei Tagen vor und nach dem Tag auszugeben und auch da evtl. #NV's gekonnt zu igonieren. Das klappt leider nicht, aber bekanntlich sagen ja Formeln mehr als tausend Worte, daher hier mal die Formel:

=WENN(ISTNV(SVERWEIS(DATUM(JAHR(C6)-1;MONAT(C6);TAG(C6));'Tabelle2'!$A$2:$B$46;2;FALSCH));MITTELWERTWENN (SVERWEIS(DATUM(JAHR(C6)-1;MONAT(C6);TAG(C6)-3);'Tabelle2'!$A$2:$B$46;2;FALSCH):SVERWEIS(DATUM(JAHR(C6)-1;MONAT(C6);TAG(C6)+3);'Tabelle2'!$A$2:$B$46;2;FALSCH);“<>#NV“); SVERWEIS(DATUM(JAHR(C6)-1;MONAT(C6);TAG(C6));'Tabelle'!$A$2:$B$46;2;FALSCH))


Die Formel ist recht sperrig, das tut mir leid, aber die generelle Idee sollte klar werden. Auch eine Kombination von ADRESSE und VERGLEICH habe ich probiert, da kam aber leider auch nichts bei rum...
Ich persönlich glaube, dass das Problem der Bereich der beiden SVERWEISE +/- 3 ist da Excel da vermutlich die Werte ausgibt, was als Bereich natürlich keinen Sinn macht. Eine Lösung für dieses Problem finde ich leider nicht, aber generell mag ich mich auch täuschen.

Hat jmd. schoneinmal ein ähnliches Problem gehabt und kann evtl. aushelfen?

Vielen Dank,

euer Dietmar

[Excellerner]

Hi da habe ich ja sogar während des Wartens sogar ne einfach Antwort für dich.
Formatiere die Jahre nicht mit, sondern mach nur Tag und Monat,..
dann kannst du die Zeilen aus den Tabellen direkt vergleichen mit Sverweis:
=SVERWEIS(A2;$Tabelle1.$A2:$B5000;2;0)
Einfach in der zweiten Tabellte hinten dran in Spalte 3 setzen,..
dann hast die Werte genau nebeneinander :=)
Diferenzen brechnen summen und so weiter wie du wilst :=)
LG Basti

---

verzeih gerade mein Deutsch :=)

[WillWissen]

Hi,

ich würde es mit zwei Hilfsspalten (jeweils eine in den beiden Tabellen), in denen du Tag und Monat berechnest, lösen:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
1	Datum	HS	Wert	SOLL
2	01.10.2019	01.10.1900	69	29
3	02.10.2019	02.10.1900	29	43
4	03.10.2019	03.10.1900	38	57
5	04.10.2019	04.10.1900	69	70
6	07.10.2019	07.10.1900	70	51
7	08.10.2019	08.10.1900	77	32
8	09.10.2019	09.10.1900	25	41
9	10.10.2019	10.10.1900	28	38
10	11.10.2019	11.10.1900	50	38
11	12.10.2019	12.10.1900	21	78
12	14.10.2019	14.10.1900	68	65
13	15.10.2019	15.10.1900	71	51
14	16.10.2019	16.10.1900	70	72
15	17.10.2019	17.10.1900	75	61
16	18.10.2019	18.10.1900	58	29
17	21.10.2019	21.10.1900	29	45
18	22.10.2019	22.10.1900	38	60
19	23.10.2019	23.10.1900	25	20
20	24.10.2019	24.10.1900	35	58
21	25.10.2019	25.10.1900	48	29
22	26.10.2019	26.10.1900	56	51
23	28.10.2019	28.10.1900	55	41
24	29.10.2019	29.10.1900	78	30
25	30.10.2019	30.10.1900	77	40
26	31.10.2019	31.10.1900	29	33

Zelle	Formel
B2	=DATUM(;MONAT(A2);TAG(A2))
D2	=RUNDEN(WENNFEHLER(SVERWEIS(B2;Tabelle2!$B$1:$C$46;2;0);MITTELWERT(D1;D3));0)

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

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

[Dietmar Wien]

Hallo Günter,

von der Grundidee sehr gut, da habe ich fürchte ich eine signifikante Information weggelassen, mein Fehler sry!
Und zwar sind die Daten aus dem letzten Jahr natürlich als vollständiger Datensatz vorhanden, von dem diesjährigen leider nicht,
da wird immer nur der aktuelle Monat angezeigt. Daher funktioniert eine einfache Fläche in der Mittelwertfunktion leider nicht.

@Basti, deine Idee scheitert daran, die #NV's in betracht zu ziehen, da es kein perfektes Matching der Arbeitstage gibt.

LG Dietmar

[WillWissen]

Tja Dietmar,

deshalb sollte eine Beispieltabelle immer wie das Original aufgebaut sein.

[BoskoBiati]

Hallo,

wenn Du beim SVERWEISmit 1 als 4.Parameter arbeitest, dann brauchst Du Dir keine Gedanken machen, ob der Tag ein Arbeitstag war, weil dann der nächst kleinere Tag genommen wird!

[Excellerner]

Hi Diemtar,
schau bevor ich mich hier verkünstel bin auch Laie und bastel nun gerne,..
Mein Ansatz breite alle Daten sauber auf und nimm nenn Kalender,.. kannst nebeneinander und untereinander machen,...
Ich habe jetzt mal bei Dir nur den Oktober genommen und nebeneinander gestellt,..
Mittelwert würde ich als Geschäftsmann persönlich gesagt den ganzen Monat nehmen, denn dies trifft es besser rein rechnerisch :=)
Jetzt hast die Daten genau nebeneinander! Ich habe dafür nur 5 Minuten gebraucht :=)

was würde dir jetzt fehlen,... wie genau willst du es vergleichen?


LG Basti

[Dietmar Wien]

  Beispieldatei.xlsx (Größe: 80,7 KB / Downloads: 4)
Hallo Günter,

Lektion gelernt! Also hier die etwas aufwendigere neue Beispielliste!


VG Dietmar

[BoskoBiati]

Hi,

wenn Du die Zellen mit Zufallszahlen füllst, dann wandle die in feste Zahlen um, bevor Du die Tabelle zeigst, sonst wird man wahnsinnig beim Testen, da diese sich ständig neu berechnen!!!!

Code:

=WENN($A4="";SVERWEIS(EDATUM($C4;-12);'2019'!$B:$D;SPALTE(B$1);1);"")

[Dietmar Wien]

Hallo miteinander,

entschuldigt die späte Antwort, ich war eine Woche nicht im Büro.
Ich habe jetzt die Formel oben genutzt was auch gut funktioniert, das mit dem Mittelwert bilden scheint ja leider nicht wirklich zu klappen.

Vielen Dank für all die guten Vorschläge,

euer Dietmar