Komplexe Formel - ist das lösbar?

[Dingeling]

Hallo liebe Excel-Profis,

ich habe eine ziemlich komplexe Frage. Vielleicht kann mir ja jemand helfen, wir bekommen es auf jeden Fall nicht hin  

Wir möchten herausfinden, inwiefern ein Online Shop in einem bestimmten Zeitraum an Sichtbarkeit verloren hat. Dazu haben wir eine Liste mit Keywords, zu denen der Shop auffindbar ist, mit einigen Metriken. Es geht nur um Keywords mit negativer Veränderung. Das sieht so aus:

Keyword                Suchvolumen   Position (1.1.18)   Position (15.1.18)   Veränderung
Schuhe kaufen          90                15                          18                        -3
hose kaufen              50                 3                             35                      -32


In der einfachen Form rechnen wir einfach Suchvolumen*Veränderung. Somit wissen wir, welche Keywords in etwa besonders starke Auswirkungen hatten. Das ist aber leider nur die halbe Wahrheit. Denn ganz wichtig ist noch, dass ein Platz auf den vorderen Plätzen sehr sehr viel wichtiger ist als ein hinterer. Dazu haben wir die folgende Liste erstellt:


Platz      Faktor
1-3          30
4-10        10
11-20       1
21-100      0,1

Und eben dieser Faktor soll nun in die Formel - und keiner weiß wie. Wenn ein Keyword also von Platz 8 auf Platz 12 rutscht dann soll dieses Keyword den Faktor 31 bekommen. Denn: Von 8 auf 9 = 10 // von 9 auf 10 = 10 // von 10 auf 11 = 10 // von 11 auf 12 = 1   === Faktor 31

Anschließend wird 31*Suchvolumen gerechnet und wir haben das gewünschte Ergebnis.

Wie aber kann es Excel schaffen, diesen Faktor automatisch auszurechnen?

Hat jemand eine Idee? Ist das mit Excel überhaupt möglich?

Grüße und vielen vielen Dank im Voraus
Dingeling

[Glausius]

Hallo,

natürlich kann das Excel!
In Bereichen suchen geht z.B. mit SVERWEIS, aber ohne weitere Informationen wird da nicht viel kommen!
1. Wo und wie kommen die Daten in eine Tabelle?
2. Wie sieht die Tabelle aus?
3. Welche Eingaben sind zu beachten?
4. Wo und wie soll das Ergebnis verarbeitet werden?

[Dingeling]

Hallo Glausius,

danke für die fixe Antwort. Ich versuche, deine Fragen zu beantworten, allerdings verstehe ich leider nicht ganz, was du meinst.


1. Wo und wie kommen die Daten in eine Tabelle?
Welche Daten? Ich habe wie gesagt die Keyword-Tabelle, die ich bereits beispielhaft aufgezeigt habe.
2. Wie sieht die Tabelle aus?
Wie in der aufgezeigten Tabelle. Nur dass eben noch eine weitere Spalte hinzukommt mit der Formel.
3. Welche Eingaben sind zu beachten?
Ich habe versucht, den Vorgang zu gut wie es geht zu schildern. Mehr muss eigentlich nicht beachtet werden.
4. Wo und wie soll das Ergebnis verarbeitet werden?
Wie beschrieben in einer neuen Excel Spalte. 

Ein SVERWEIS würde mir m. E. hier nicht helfen - zumindest nicht ohne Weiteres.

Ein Lösungsansatz wäre vielleicht der: Wenn ein Keyword von Platz 8 auf 12 abrutscht, muss Excel irgendwie in einer neuen Tabelle notieren, dass es eben auf 9, 10, 11 und dann 12 abrutscht (ich habe allerdings nur die Daten 8 und 12). Dann könnte ich den Rest mit einem SVERWEIS machen.

Jetzt aber die große Frage: Wie kann Excel die restlichen Zahlen "auffüllen"?

Grüße

[Glausius]

  Mustermappe.xlsm (Größe: 15,1 KB / Downloads: 2)

Hallo,

da du leider keine spezifischen Angaben zu der Tabelle machst und ich die nicht nachempfinden will, habe ich einmal eine kleine Musterdatei erstellt, wo die entsprechenden Faktoren ermittelt werden können.
Allerdings ist das keine Formellösung, sondern ein VBA-Code der bei Eintragungen in die alten bzw. neuen Werte ausgelöst wird.
Sicherlich kann man das noch verfeinern und optimieren, aber es zeigt den Weg!

[BoskoBiati]

Hi,

vielleicht so:

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C	D	E	F
7	1	30		8	12	31
8	4	10		5	1	-40
9	11	1		6	17	56
10	21	0,1

Zelle	Formel
F7	=(E7-VERWEIS($E7;$A$7:$A$10))*VERWEIS(E7;$A$7:$B$10)+(VERWEIS(E7;$A$7:$A$10)-D7)*VERWEIS(D7;$A$7:$B$10)

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

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

[Dingeling]

Hi,

vielleicht so:

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C	D	E	F
7	1	30		8	12	31
8	4	10		5	1	-40
9	11	1		6	17	56
10	21	0,1

Zelle	Formel
F7	=(E7-VERWEIS($E7;$A$7:$A$10))*VERWEIS(E7;$A$7:$B$10)+(VERWEIS(E7;$A$7:$A$10)-D7)*VERWEIS(D7;$A$7:$B$10)

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

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

Hallo Opa Edgar, 

Es ist exakt das gewünschte Ergebnis und funktioniert einwandfrei. Vielen vielen Dank! Wie kann ich dir nur danken? Wir hätten das niemals hinbekommen und du hilfst mir schon zum zweiten Mal!

LG
Dingeling

[BoskoBiati]

Hi,

wenns passt ist ja alles Bestens. Dann viel Spaß damit.

[LCohen]

Ohne Hilfszellen:

A1: alter Platz 8
A2: neuer Platz 12

=
MIN(A2*{30.10.1.0,1}+{0.80.179.197,1})-
MIN(A1*{30.10.1.0,1}+{0.80.179.197,1})

[LCohen]

Für einfacheres Nachvollziehen (ohne schwierig zu ermittelnde Konstanten {0.80.179.197,1}) geht das auch so:

=SUMMENPRODUKT({-1;1}*(A1:A2>{0.4.11.21})*(A1:A2-{0.4.11.21})*({30.10.1.0,1}-{0.30.10.1}))

etwas kryptisch auch als

{=SUMME({-1;1}*WENNFEHLER(EXP(LN(A1:A2-{0.4.11.21}));)*-{-30.20.9.0,9})}

Richtig ist übrigens F8: -100 (statt Boskos -40).

[BoskoBiati]

Hi,

Du hast recht. Kann man so lösen:

Code:

=(MAX(E7;D7)-VERWEIS(MAX($E7;$D7);$A$7:$A$10))*VERWEIS(MAX(D7;E7);$A$7:$B$10)+(VERWEIS(MAX(D7;E7);$A$7:$A$10)-MIN(D7;E7))*VERWEIS(MIN(E7;D7);$A$7:$B$10)