Excel Formel oder Funktion?

[Catweasel]

Hallo liebes Team vom Clever-Excel-Forum,
Ich bitte euch um Hilfestellung/Lösung für folgendes Problem:
Ich habe 3 Spalten (A - C  = Laden, Artikel und Preis) und viele Zeilen und möchte gerne den Preis (Spalte C) entsprechend der Aufteilung in den Spalten E – G (Discounter, Kaufhaus und Online) ausgeben. Beispiel ist beigefügt.
Für eure Mithilfe danke ich vielmals.
  Formel oder Funktion.xlsx (Größe: 10,82 KB / Downloads: 22)

[echo]

Hallo,
das geht mit "Bordmittel" ganz ohne Formeln:

1. deine Liste markieren und mit [STRG]+[T] eine intelligente Tabelle erzeugen
2. Ist eine Zelle der Tabelle markiert, im Menü "Tabellentwurf" die Ergebniszeile einblenden
3. Im Pulldown der Überschrift von Spalte A nach der gewünschten Zellfarbe filtern

Gruß Holger

[Ralf A]

Ich habe 3 Spalten (A - C  = Laden, Artikel und Preis) und viele Zeilen und möchte gerne den Preis (Spalte C) entsprechend der Aufteilung in den Spalten E – G (Discounter, Kaufhaus und Online) ausgeben. Beispiel ist beigefügt.

Moin,

Formatierte Tabelle ist schon der richtige Weg. Wenn Du jetzt der Tabelle noch eine Spalte "Zuordnung" spendierst und den Läden zuordnest, ob sie Online, Kaufhaus oder Discounter sind, kannst Du eine beliebige Zelle der Datentabelle markieren und über das Menü Daten --> Aus Tabelle/Bereich folgendenCode im Erweiterten Editor eingeben:

Code:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Pivotierte Spalte" = Table.Pivot(Quelle, List.Distinct(Quelle[Zuordnung]), "Zuordnung", "Preis"),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Pivotierte Spalte",{"Laden", "Artikel"}),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Entfernte Spalten",{{"Discounter", Currency.Type}, {"Kaufhaus", Currency.Type}, {"Online", Currency.Type}}),
    #"Sortierte Zeilen" = Table.Sort(#"Geänderter Typ",{{"Discounter", Order.Ascending}, {"Kaufhaus", Order.Ascending}})
in
    #"Sortierte Zeilen"

Wobei mir nicht klar ist, was Du mit Listen nicht zuzuordnender Preise anfangen willst. Wolltest Du nicht eher eine Zusammenfassung aller Preise/Zuordnung? Evtl. sogar pro Zuordnung und Artikel?

[Andreas Killer]

Ich habe 3 Spalten (A - C  = Laden, Artikel und Preis) und viele Zeilen und möchte gerne den Preis (Spalte C) entsprechend der Aufteilung in den Spalten E – G (Discounter, Kaufhaus und Online) ausgeben. Beispiel ist beigefügt.

Ich gehe da noch mal einen Schritt weiter als Ralf, wahrscheinlich wirst Du ja viele gleiche Artikel pro Laden haben. Daher wäre es sinnvoll die Zuordnung welcher Laden nun Online, Kaufhaus oder Discounter ist in einer 2ten Tabelle zu machen. Das reduziert die Datenmenge und macht es einfacher da Du Deine Daten ohne Nacharbeit verwenden kannst.

Beide Tabellen laden wir in das Datenmodell (Power Pivot) und erstellen eine Beziehung. Nun können wir sehr einfach Dein gewünschtes Layout erzeugen oder auch mal eine Auswertung machen wo es einen Artikel am günstigsten gibt.

  Formel oder Funktion.xlsx (Größe: 199,91 KB / Downloads: 13)

Andreas.

[snb]

Pivottable.

[schauan]

... mit PowerQuery geht das auch.

Auch hier, beide Tabellen laden und mit diesem Code verbinden:

PHP-Code:

let
Source = Excel.CurrentWorkbook(){[Name="Daten"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Laden", type text}, {"Artikel", type text}, {"Preis", type number}}),
#"Zusammengeführte Abfragen" = Table.NestedJoin(#"Changed Type", {"Laden"}, Läden, {"Laden"}, "Läden", JoinKind.LeftOuter),
#"Erweiterte Läden" = Table.ExpandTableColumn(#"Zusammengeführte Abfragen", "Läden", {"Typ"}, {"Läden.Typ"}),
#"Entfernte Spalten" = Table.RemoveColumns(#"Erweiterte Läden",{"Artikel", "Laden"}),
#"Gruppierte Zeilen" = Table.Group(#"Entfernte Spalten", {"Läden.Typ"}, {{"Summe nach Typ", each List.Sum([Preis]), type nullable number}})
in
#"Gruppierte Zeilen"


Anschließend ggf. das Ergebnis anzeigen lassen ...

Anbei die Datei von Andreas, entsprechend erweitert.

[Egon12]

Hallo,
 
ich fürchte da hat einer aufgegeben. Ich denke mal, dass der TO eine UDF erhofft hatte um anhand der Farbvorgaben eine passende Ausgabe zu erhalten. 
Mit Farben ist das natürlich so eine Sache. Schon die kleinste Abweichung führt zu Fehlern in der Ausgabe.
Aber falls dies nun unbedingt so sein soll hier die Datei mit der UDF.

  Formel oder Funktion.xlsm (Größe: 17,23 KB / Downloads: 0)
 
Gruß Uwe

[schauan]

@Uwe,

das ist wie Farben zählen

Ansonsten 1)
könnte der TE eine bedingte Formatierung für die Farben verwenden, dann gibt es auch keine Abweichungen. Problem: Dazu braucht er auch wie vorgeschlagen eine Zusatztabelle.

Ansonsten 2)
bin ich da bei echo - vielleicht als Ergänzung für den TE, die Ergebniszeile in einer iT bringt die Summe der sichtbaren Zeilen - wenn also nach einer Farbe gefiltert wird hat man den entsprechenden Wert . Die Formel für dieses "TEILERGBNIS" kann man aber auch ausserhalb der iT nutzen.

[slowboarder]

Hi
ich würde das so machen:

Schritt 1:
ersetze die Farbliche Zuordnung durch eine Textliche Zuordnung
dh in die Spalte D schreibst du in jede Zeile, ob es ein Discounter, Kaufhaus oder Online ist
um deine Farben trotzdem zu verwenden, aktiviere den Autofilter, klicke auf den DropDown und wähle "nach Farbe filtern". dann kannst du orange auswählen und in alle Zellen "Discounter" eintragen. Excel füllt nur die sichtbaren Zellen.
das machst du dann mit allen drei Farben.
Schritt 2:
verwende in den Spalten E:G die Formel (Formel für E3): =Wenn(E$2=$D3;$C3)


wenn du ohne Hilfsspalte auskommen willst und die Farben direkt nutzen möchtest, dann geht das prinzipiell auch, aber es hat einige Einschränkungen.
Die erste wäre, dass man Farben mit normalen Excelformeln nicht auswerten kann, man muss hierzu entweder was mit VBA programmieren oder das alte Zelle.Zuordnen verwenden, welches aber nur in einem Excelnamen verwendet werden kann:
Schritt1:
Selektiere also die Zelle E3 und erstelle folgende beide Namen:
FarbeZ mit dieser Formel: =Zelle.Zuordnen(63;Indirekt("ZS1";0))
FarbeS mit dieser Formel: =Zelle.Zuordnen(63;Indirekt("Z2S";0))
Schritt 2: 
in die Zelle E3 kommt diese Formel, welche du dann nach unten und rechts Ziehen kannst:
=Wenn(FarbeZ=FarbeS;$C3;"")

Der Nachteil dieser Lösung liegt darin, dass es eine alte Excelfunktion ist, die nur das alte Farb-Index-System beherrscht, welches nur 56 verschiedene Farben kennt. Wenn du daher sehr ähnliche Farben verwendest  (verschiedene Grüntöne), kann es also passieren, dass diese als die selbe Farbe interpretiert werden
Ein weitere Nachteil liegt darin, dass Excel die Änderung einer Farbe nicht veranlasst, die Zellwerte neu zu berechnen, dh nach einer nachträglichen Änderung der Farbe wird die Formel zunächst noch den alten Wert anzeigen.

Gruß Daniel

[schauan]

... da hatte die Tabelle mit den Einzelwerten gefehlt. Wie gesagt, per PQ.
Bei den Summen der Code ist komplett per Klick im PQ-Editor / GUI entstanden.
Für de Einzelwerte wurden berechnete Spalten hinzugefügt, also auch bisschen was geschrieben ... Kann man sich ja auch im Erweiterten Editor anschauen


  Formel oder Funktion + PQ.xlsx (Größe: 176,86 KB / Downloads: 2)