pivot in Excel 2016

[Hillary]

Hallo ihr Lieben, 
ich sehe mir nun schon seit ein paar stunden im Internet Erklärungsvideos an und komme dennoch nicht weiter.. bitte um Hilfe 

Ich habe eine Tabelle erstellt:

  A             B            C               D
Auto    Verkäufer Einkaufpreis Mietfaktor(/MONAT)
BMW   Hans        19000         570
Audi    Hans         5000          125
BMW   Peter        8000           240
Audi    Peter       10000          250


Mietfaktor für AUDI=PRODUKT(C*2,5%)
Mietfaktor für BMW=PRODUKT(C*3%)

danach habe ich eine Pivot Tabelle erstellt und wollte die Summe der Mietfaktoren für jeden einzelnen Verkäufer herausfinden... soweit so gut - das hat blendend funktioniert.. 

dann habe ich meine eigentliche Arbeitstabelle hergenommen an der ich gerade arbeite ... gleiches Prinzip nur ein paar Spalten und Zeilen mehr ....
habe wieder eine Pivot Tabelle erstellt und wollte wieder die Summe aller Mitfaktoren - ja nach Verkäufer.
Jetzt ist es aber so dass er mir als Summe nicht die Werte aus der Mietfaktoren Spalte gibt, sondern immer nur 0 ausgibt... was aber nicht sein kann, dass die Summe 0 ergibt ....
Bitte um Hilfe 

Danke im Voraus.

[Peter]

Hallo,

dann habe ich meine eigentliche Arbeitstabelle hergenommen an der ich gerade arbeite .

erkennt XL die Umsätze in dieser Arbeitstabelle auch als Zahlen?

Du könntest in deiner Datentabelle versuchsweise folgendes probieren:

- Die Spalte mit den Umsätzen markieren
- Rechte Maus
- Zellen formatieren
- Register Zahlen
- Format Standard oder Zahl vergeben (auch wenn dies schon der Fall ist)
- Ok
- Markierung belassen
- Menü Daten
- Text in Spalten
- Fertig Stellen

Anschließend aktualisierst du die PT. Hilft das?

Wenn nein, dann wäre eine Beispielsdatei (abgespeckt und anonymisiert) hilfreich damit man sich das am Original ansehen kann.

[Hillary]

Hier ist mal die Tabelle .. und CO

ich habs probiert - aber es kommt nur blödes zeug

[WillWissen]

Hallo Hillary,

wie Peter bereits vermutet hat, sind deine Zahlen keine Zahlen sondern Texte. Du solltest Peters Vorschlag ausprobieren, um die Textwerte in Zahlenwerte zu konvertieren. Ein zweite Möglichkeit ist:

- du schreibst neben deine Tabelle eine 1 und kopierst sie mit STRG-C
- du markierst anschl. deine Spalte O und gehst mit der rechten Maustaste auf
--- Inhalte einfügen
--- Multiplizieren markieren und
--- auf OK klicken

Die 1 kannst du wieder löschen.

Allerdings habe ich in deiner Tabelle neben den umgewandelten Zahlen auch Fehlermeldungen #WERT! erhalten, die auf eine fehlerhafte Formel hindeuten. Ich komme leider momentan nicht dazu, mir deine Mammutformel genauer anzusehen und ggffls. zu verkürzen.

Deine Tabelle vor der Umwandlung:

Tabelle1

	F	G	H	I	J	K	L	M	N	O	P
1	NAME	Spalte7	Spalte8	Spalte9	Spalte10	EKPREIS	Spalte11	Spalte1	FUNKTION	Mietfaktor
2	Nokia 225 Dual SIM					41,58			H	1,575882	FALSCH
3	PC Lenovo Thinkcentre M92p					545			A	14,4425	FALSCH
4	Lenovo PortReplicator3 für T510/T520/ T530					130			H	3,445	FALSCH
5	Lenovo ThinkPad Pro Dock / 90 Watt					155			H	4,10754,1075	FALSCH
6	HP Compaq LA2205wg, 22"					153			A	4,0545	FALSCH
7	HP Compaq LA2205wg, 22"					153			H	4,0545	FALSCH
8	SAMSUNG SM S24C450M, 1920x1080, boxen, höhenverstellbar, pivot					169			H	4,4785	FALSCH
9	Lenovo ThinkPad Ultra Dock / 90 Watt					155			H	4,10754,1075	FALSCH
10	SAMSUNG SM S24C450M, 1920x1080, boxen, höhenverstellbar, pivot					169			H	4,4785	FALSCH
11	SAMSUNG SM S24C450M, 1920x1080, boxen, höhenverstellbar, pivot					169			B	4,4785	FALSCH
12	HP dc8200CMT					615			H	16,2975	FALSCH
13	Lenovo ThinkPad Ultra Dock / 90 Watt					155			H	4,10754,1075	FALSCH
14	Lenovo ThinkPad Ultra Dock / 90 Watt					155			H	4,10754,1075	FALSCH

Formeln der Tabelle

Zelle Formel O2 =WENN(NICHT(ISTFEHLER(SUCHEN("apple iphone";F:F;1)));K:K *3,57%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("mac";F:F;1)));K:K *2%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("samsung galaxy";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("nokia";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("sony";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("htc";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("microsoft";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("microsoft";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("LG";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("backberry";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("huawei";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("lenovo";F:F;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("samsung sm";F:F;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("HP";F:F;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("asus";F:F;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("surface";F:F;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("microsoft";F:F;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("Notebook";F:F;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("thinkpad";F:F;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("dock";G:G;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("apple Zubehör";G:G;1)));K:K *2%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("IT Zubehör";G:G;1)));K:K *2,65%;"") P2 =ISTZAHL(O2)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8





Deine Tabelle nach der Umwandlung:

Tabelle1

	F	G	H	I	J	K	L	M	N	O	P
1	NAME	Spalte7	Spalte8	Spalte9	Spalte10	EKPREIS	Spalte11	Spalte1	FUNKTION	Mietfaktor
2	Nokia 225 Dual SIM					41,58			H	1,575882	WAHR
3	PC Lenovo Thinkcentre M92p					545			A	14,4425	WAHR
4	Lenovo PortReplicator3 für T510/T520/ T530					130			H	3,445	WAHR
5	Lenovo ThinkPad Pro Dock / 90 Watt					155			H	#WERT!	FALSCH
6	HP Compaq LA2205wg, 22"					153			A	4,0545	WAHR
7	HP Compaq LA2205wg, 22"					153			H	4,0545	WAHR
8	SAMSUNG SM S24C450M, 1920x1080, boxen, höhenverstellbar, pivot					169			H	4,4785	WAHR
9	Lenovo ThinkPad Ultra Dock / 90 Watt					155			H	#WERT!	FALSCH
10	SAMSUNG SM S24C450M, 1920x1080, boxen, höhenverstellbar, pivot					169			H	4,4785	WAHR
11	SAMSUNG SM S24C450M, 1920x1080, boxen, höhenverstellbar, pivot					169			B	4,4785	WAHR
12	HP dc8200CMT					615			H	16,2975	WAHR
13	Lenovo ThinkPad Ultra Dock / 90 Watt					155			H	#WERT!	FALSCH
14	Lenovo ThinkPad Ultra Dock / 90 Watt					155			H	#WERT!	FALSCH

Formeln der Tabelle

Zelle Formel O2 =(WENN(NICHT(ISTFEHLER(SUCHEN("apple iphone";F:F;1)));K:K *3,57%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("mac";F:F;1)));K:K *2%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("samsung galaxy";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("nokia";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("sony";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("htc";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("microsoft";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("microsoft";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("LG";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("backberry";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("huawei";F:F;1)));K:K *3,79%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("lenovo";F:F;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("samsung sm";F:F;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("HP";F:F;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("asus";F:F;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("surface";F:F;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("microsoft";F:F;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("Notebook";F:F;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("thinkpad";F:F;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("dock";G:G;1)));K:K *2,65%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("apple Zubehör";G:G;1)));K:K *2%;"") &WENN(NICHT(ISTFEHLER(SUCHEN("IT Zubehör";G:G;1)));K:K *2,65%;""))*1 P2 =ISTZAHL(O2)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[chris-ka]

HI,

du suchst nach Lenovo und du suchst nach ThinkPad
beides wird gefunden und mit deinem & in dem Konstrukt wird BEIDES ausgegeben....
das mit dem suchen hatten wir doch schon.
http://www.clever-excel-forum.de/thread-5616.html

[Rabe]

Hi,

Allerdings habe ich in deiner Tabelle neben den umgewandelten Zahlen auch Fehlermeldungen #WERT! erhalten, die auf eine fehlerhafte Formel hindeuten. Ich komme leider momentan nicht dazu, mir deine Mammutformel genauer anzusehen und ggffls. zu verkürzen.

das kommt daher, daß z.B. bei Zeile 5, 9, 13 und 14 für "Lenovo ThinkPad Pro Dock / 90 Watt" zwei Formelergebnisse als Text zusammengefügt werden:
ein Mal 2,65%*155 für Lenovo
und nochmal 2,65%*155 für thinkpad
das ergibt: 4,10754,1075  und das kann keine Zahl sein.

Und wenn Du jetzt noch nach "Pro Dock" und "90 Watt" suchst, kommt das 4 Mal hintereinander.
Schreib einfach mal "Microsoft" in die Spalte F, dann kommt die Zahl 3 Mal direkt hintereinander und das ist auch keine Zahl.

Fazit: Deine Formel ist Mist!

Lösung: Nimm SVERWEIS() oder das Beispiel, das Chris Dir gezeigt hat, das ist noch besser!

[Hillary]

Danke Leute...
ich habe nun SVERWEIS genommen, es ist tatsächlich viel einfacher !!
- und es sieht so aus ... 

bei den ersten 2-3 Werten gibt er mir genau das aus was ich brauche und ab der 6.ten Zeile schreibt er nur noch #NV 

.. was nun?

LG

[Rabe]

Hi,

bei den ersten 2-3 Werten gibt er mir genau das aus was ich brauche und ab der 6.ten Zeile schreibt er nur noch #NV 

das bedeutet, daß er den Suchbegriff nicht gefunden hat.

Hast Du den Suchbereich im SVERWEIS mit $ fixiert?

beispielhaft, da ich nicht weiß, wo der Suchbereich steht:
=SVERWEIS(F2;$Q$2:$R$20;2;0)
oder:
=SVERWEIS(F2;Tabelle2!$A:$B;2;FALSCH)*K2

Zeig mal einen Tabellenausschnitt mit den Formeln.

[Hillary]

danke 
nun die letzte frage 

=SVERWEIS([@KATEGORIE];'Mietfaktoren '!B3:C8;2;FALSCH) Diese Funktion ist gut :)
alles geht

wenn ich sie aber nach unten ziehe - also automatisch ausfüllen lassen wird sie immer wieder verändert...

=SVERWEIS([@KATEGORIE];'Mietfaktoren '!B4:C9;2;FALSCH)

usw...

kann ich irgendwie einstellen dass es sich nicht ändert wenn ich es auf die folgenden 900 Zeilen übernehmen möchte?
also dass bei der Matrix IMMER B3:C8 bleibt .. !!

Lg Hilly

[chris-ka]

Hi

=SVERWEIS([@KATEGORIE];'Mietfaktoren '!B$3:C$8;2;FALSCH)

anbei noch deine Datei mit "suchen"


  Mappe123.xlsx (Größe: 13,5 KB / Downloads: 3)