Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Quotenberechnung
#1
Hallo zusammen,

ich stoße bei einem Problem an die Grenzen meiner Excel-Kenntnisse:

Folgende Problemstellung (Beispieldatei ist angehängt):
- Leider keine Anwendung von VBA/Makros möglich! 
- die Datei soll künftig in einem bestimmten Turnus möglichst automatisiert aktualisiert werden können 

Ich habe in einer Datei zwei Tabellenblätter:
- Datengrundlage 1
- Datengrundlage 2
In beiden Tabellenblättern erhalte ich Daten aus dem System. -> es ist daher zwingend erforderlich, dass die beiden Tabellenblätter bestehen bleiben, da ich diese dann entsprechend einfach systemseitig aktualisieren kann.
Die gewünschte Berechnung kann in der Datei der Datengrundlage 1 erfolgen.  

Zu den Daten:
Wie ihr in der Datei sehen könnt, werden in beiden Datengrundlagen zu Beginn in einer Spalte verschiedene Produktnummern ausgegeben. 
In der obersten Zeile werden hingegen die verschiedenen Kalenderwochen ausgegeben. 
Problem: Die Darstellung erfolgt nicht von Kalenderwoche 1-52, da es in bestimmten Wochen zu keiner Produktion kam und aus diesen Wochen entsprechend keine Werte aus dem System gezogen werden können. -> Bedeutung: Ich springe zum Beispiel in der Datengrundlage 1 von KW 15 auf 18. In der Datengrundlage 2 hingegen gibt es in der Kalenderwoche 16 einen Wert - in Datengrundlage 1 aber nicht. Diese Sprünge werden systemseitig ausgegeben werden und können im Sinne einer Automatisierung auch nicht manuell angepasst werden.


Es soll folgendes berechnet werden: (in Beispieldatei) z.B. in der Zelle Z4:
(Info) Die Kalenderwochen der Berechnung sind mit denen der Datengrundlage 1 identisch.

Berechnung Z4: 
(Datengrundlage 1 Kalenderwoche 2 : (Datengrundlage 2) Kalenderwoche 2
Bezug auf Produkt 1111 -> Wert aus Kalenderwoche 2 des Produkts 1111 (Datengrundlage 1) dividiert durch den Wert aus Kalenderwoche 2 des Produkts 1111 (Datengrundlage 2)

In Fall der Zelle Z4 wäre das entsprechend 1/38.
Leider stößt ein S-Verweis hier bei dem Sprung der Kalenderwochen immer an seine Grenzen, da er dann beispielsweise immer den Wert aus KW18 (Datengrundlage 1) durch den Wert aus KW 16 (Datengrundlage 2) dividiert. 

Es soll also neben einer Prüfung der Produktnummer auch eine Prüfung stattfinden, dass die Division immer durch die gleichen Kalenderwochen erfolgt.
Ich möchte die Formel dann sowohl nach rechts als auch nach unten ziehen.

Könnt ihr mir hier weiterhelfen?

Danke & Grüße


Angehängte Dateien
.xlsx   Quotenberechnung.xlsx (Größe: 12,49 KB / Downloads: 10)
Antworten Top
#2
Hallo,

an dem Satz "kein VBA möglich" bin ich hängen geblieben.

Bedeutet das, dass keine xlsm - Dateien gespeichert werden können, oder - im Normalfall - VBA per Gruppenrichtlinie blockiert ist.

Einfach gefragt, gibt es eine Möglichkeit, diese Restriktion zu umgehen / auszuhebeln?

mfg
Antworten Top
#3
Vielen Dank für die Rückmeldung.
Der Grund sind leider interne Firmenrichtlinien. :/
Antworten Top
#4
Hallöchen,

hier mal ein Ansatz. Nimm INDEX und VERGLEICH. Damit kannst Du die Sprünge überspringen Smile

Arbeitsblatt mit dem Namen 'Tabelle1'
ABCDEFGHIJKL
11241234124
22341002003004005066,66667100

ZelleFormel
J2=INDEX($E$2:$H$2;1;VERGLEICH(J1;$E$1:$H$1;0))/A2
K2=INDEX($E$2:$H$2;1;VERGLEICH(K1;$E$1:$H$1;0))/B2
L2=INDEX($E$2:$H$2;1;VERGLEICH(L1;$E$1:$H$1;0))/C2
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
[-] Folgende(r) 1 Nutzer sagt Danke an schauan für diesen Beitrag:
  • FabMan07
Antworten Top
#5
(19.06.2019, 09:06)FabMan07 schrieb: - Leider keine Anwendung von VBA/Makros möglich! 
- die Datei soll künftig in einem bestimmten Turnus möglichst automatisiert aktualisiert werden können 
Eine Automatisierung ohne VBA?

(19.06.2019, 09:06)FabMan07 schrieb: In beiden Tabellenblättern erhalte ich Daten aus dem System. -> es ist daher zwingend erforderlich, dass die beiden Tabellenblätter bestehen bleiben, da ich diese dann entsprechend einfach systemseitig aktualisieren kann.
Das ist ja offenbar nur die halbe Wahrheit. Es handelt sich hierbei um eine Wertekopie einer Pivot-Tabelle. Und eine Pivot-Tabelle erhält man nur in Excel und nicht aus einem externen System.
Warum allerdings eine Wertekopie verwendet wird ist mir ein Rätsel weil damit eine einfache Lösung torpediert wird (PIVOTDATENZUORDNEN()).

(19.06.2019, 09:06)FabMan07 schrieb: In Fall der Zelle Z4 wäre das entsprechend 1/38.
Und dein Beispiel ist auch noch falsch.
Wir sehen uns!
... Detlef

Meine Beiträge können Ironie oder Sarkasmus enthalten.

Antworten Top
#6
(19.06.2019, 17:58)shift-del schrieb: Eine Automatisierung ohne VBA?

Das ist ja offenbar nur die halbe Wahrheit. Es handelt sich hierbei um eine Wertekopie einer Pivot-Tabelle. Und eine Pivot-Tabelle erhält man nur in Excel und nicht aus einem externen System.
Warum allerdings eine Wertekopie verwendet wird ist mir ein Rätsel weil damit eine einfache Lösung torpediert wird (PIVOTDATENZUORDNEN()).

Und dein Beispiel ist auch noch falsch.

Automatisierung war in diesem Fall dann falsch gewählt. 
Ich erhalte Daten aus dem System, die ich erst in einer Pivot aufbereiten muss und dann verwenden kann. 
Automatisierung bedeutet in diesem Fall also: Datenbankabfrage aktualisieren, Pivot aktualisieren, Blatt berechnen -> fertig. 
Vermeiden möchte ich, dass ich jedes mal wieder etwas an den Formeln anpassen muss und daher sollen die entsprechenden Kalenderwochen miteinander verglichen werden.


Wie du schon sagst, die Datengrundlage besteht aus zwei Pivottabellen. Entschuldige, dass ich mich hier nicht deutlich ausgedrückt habe.
& mit dem Beispiel hast du leider auch recht. Ich habe bei der Beispieldatei in KW2 tatsächlich die 1 vergessen.


Kannst du mir weiterhelfen?
Antworten Top
#7
@schaun
deine Lösung funktioniert nach einigen kleinen Anpassungen bezüglich der Zellbezüge sehr gut. Vielen Dank! 

Eine Frage habe ich allerdings noch: 
Ist es möglich, die von dir genannte Formel um eine Prüfung zu erweitern?

Ich habe die aktuelle Datei mit den Berechnungen angehängt. Zusätzlich habe ich die Datei auf ein Tabellenblatt reduziert und die Daten des zweiten einfach eingefügt. 

Nun zu meiner Frage: 
Bisher habe ich "nur" eine Prüfung bezüglich der Kalenderwochen. Wie kann ich in diese Formel auch eine Prüfung bezüglich der Produktnummern einfügen? 

Bsp. Formel aus Z7
=WENNFEHLER(INDEX($B7:$T7;1;VERGLEICH(Z$3;$B$3:$T$3;0))/INDEX($AW7:$BP7;1;VERGLEICH(Z$3;$AW$3:$BP$3;0));0)

Bisher geht er nur Zeile für Zeile durch. Was ist aber, wenn eine Produkt in einer der beiden Tabellen mal nicht angezeigt wird. In diesem Fall rechnet die Formel stur weiter und vergleicht zwei verschiedene Produkte miteinander. Ich möchte also gerne sowohl die Kalenderwochen als auch die Produktnummern vergleichen.  

Ich hoffe, du kannst meine Problematik nachvollziehen. :)


Angehängte Dateien
.xlsx   Quotenberechnung (1).xlsx (Größe: 13,1 KB / Downloads: 1)
Antworten Top
#8
Hola,

zur Info:

http://www.office-loesung.de/p/viewtopic.php?f=166&t=807195

Gruß,
steve1da
[-] Folgende(r) 1 Nutzer sagt Danke an steve1da für diesen Beitrag:
  • schauan
Antworten Top
#9
Hallöchen,

Du musst dann analog zum bisherigen Vorschlag mit den Spalten auch die Zeilen mit VERGLEICH ermitteln und natürlich auch die INDEX-Matrix auf die Zeilen ausdehnen:

=WENNFEHLER(INDEX($B4:$T9;VERGLEICH($Y4;$A$4:$A$9;0);VERGLEICH(Z$3;$B$3:$T$3;0))/INDEX($AW4:$BP9;VERGLEICH($Y4;$A$4:$A$9;0);VERGLEICH(Z$3;$AW$3:$BP$3;0));0)
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Antworten Top
#10
(21.06.2019, 06:27)FabMan07 schrieb: Kannst du mir weiterhelfen?
Du solltest eine Beispiel-Mappe mit Pivot-Tabellen zur Verfügung stellen.
Wir sehen uns!
... Detlef

Meine Beiträge können Ironie oder Sarkasmus enthalten.

Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste