Umfassende Excel wenn(und) formel?

...komplette Frage anzeigen Die Erklärung - (Programm, programmieren, Microsoft)

4 Antworten

Wie es mir scheint, hast du ein Deserialisierungsproblem. Ich würde es mit einem Makro lösen und das so programmieren, dass deine Quelldaten dir immer erhalten bleiben. Die Daten kommen auf ein neues Blatt. Dadurch wirst du es öfter ausführen können.

In meinem Code ist die Zelle mit ersten T1-Wert die Ankerzelle, an der sich das Makro orientiert. Im Sheet habe ich ihr den Namen "Anker" gegeben.

Im angehängten Screenshot siehst du, wie ich die Quelldaten aufgebaut habe.

Folgender Code liegt hinter der Schaltfläche "deserialisieren". Er fügt pro Tx eine neue Zeile auf der Zieltabelle ein.

Option Explicit

Sub Deserialisierung() Dim i As Integer Dim iZielZeile As Integer Dim rPivot As Range Dim wsQuelle As Worksheet Dim wsZiel As Worksheet
Set wsQuelle = Sheets("Quelldaten") Set wsZiel = Sheets.Add(After:=Sheets(Sheets.Count)) wsZiel.Name = "Zieldaten" & Format(Sheets.Count - 1, "00") Set rPivot = wsQuelle.Range("Anker") ' Erster T1-Wert
'Zieltabelle vorbereiten wsZiel.Range("A1:D1").Value = wsQuelle.Range(rPivot.Offset(-1, -1), rPivot.Offset(-1, 2)).Value wsZiel.Range("A1:D1").Font.Bold = True iZielZeile = 2
'Deserialisierung While rPivot.Value > 0 wsZiel.Cells(iZielZeile, 1).Value = rPivot.Offset(0, -1).Value wsZiel.Cells(iZielZeile, 2).Value = rPivot.Value For i = 1 To 2 If rPivot.Offset(0, i).Value > 0 Then iZielZeile = iZielZeile + 1 wsZiel.Cells(iZielZeile, 1).Value = rPivot.Offset(0, -1).Value wsZiel.Cells(iZielZeile, 2).Value = rPivot.Offset(0, i).Value End If Next
iZielZeile = iZielZeile + 1 Set rPivot = rPivot.Offset(1, 0) Wend
Set rPivot = Nothing Set wsQuelle = Nothing Set wsZiel = Nothing End Sub
Quelldaten vor der Deserialisierung - (Programm, programmieren, Microsoft)

Im Grunde ist das nur eine Idexabfrage mit Mehrfachtreffern

das kannst du mit folgender Formel erfüllen:

=WENN(ZÄHLENWENN($A1:$A$2;$A2)>0;"";WENNFEHLER(INDEX($B$2:$B$7;KKLEINSTE(WENN($A$2:$A$7=$A2;ZEILE($1:$6));SPALTE()-1));""))

Das ganze ist eine Matrixformel muss daher mit shift + strg + enter abgeschlossen werden.

Die Bereiche musst du natürlich auch anpassen!


Zählewenn($A1:$A$2;$A2)  => A1 Stellt die Zelle über der ersten Projektnummer dar. A2 ist dementsprechend die erste Projektnummer

INDEX($B$2:$B$7 => B2:B7 ist der Bereich in dem alle T1 Nummern stehen
in diesem Fall sind es ja 6 Zeilen - Achtung diese Länge müssen die nächsten Bereiche auch haben

WENN($A$2:$A$7=$A2 => A2:A7 sind die Projektnummern - auch hier wieder 6 Zeilen!

ZEILE($1:$6) => ist eine Interne Nummerierung das $1:$ bleibt immer erhalten, die 6 musst du mit der Zahl der Spalten in deinem Fall ersetzen.

SPALTE()-1 => Definiert ob T2 oder T3 ausgegeben werden.
Wenn die Projektnummern in Spalte A (= Spalte 1) stehen, T1 und Spalte B (= 2) und T2 in Spalte C (=Spalte 3) stehen soll, dann Muss es -1 lauten.
Denn Spalte 3 -1 = 2 also wird T2 ausgegeben.

Wenn bei dir T2 erst in Spalte E (= Spalte 5) steht musst du -3 statt -1 schreiben.


Du könntest versuchen dir mit der Funktion Zählenwenn etwas zusammen zu schustern.

Allerdings fürchte ich, dass Excel für deinen Zweck das falsche Programm ist - ich würde die Werte in einer Datenbank erfassen und auswerten.

Iamiam 05.08.2016, 13:51

Datenbanken sind das richtige für viele Bank-Daten, für kleinere Datenbänke verzinst sich die Lerninvestition nicht!

0
klugshicer 05.08.2016, 14:12
@Iamiam

Würde ich jetzt nicht sagen eine Datenbank ist schnell angelegt und mit Hilfe des Formular-Assistenten kann man auch als Anfänger problemlos Formulare erstellen.

Ich verwende Access relativ häufig um Daten aufzubereiten - das einzige mit dem man sich als Anfänger wirklich befassen muss ist  die 1. Normalform und das erstellen einfacher Abfragen für Berichte

0

Ich verstehe zwar den Sinn von dem Ding nicht, ich würde erstmal ein Zählenwenn auf das Projekt machen.
http://www.excel-helfer.de/excel-vergleich-von-daten-mit-der-funktion-zaehlenwenn.html

Dann würde ich mir ein neues Blatt bauen mit verketteten Schlüsseln.

Verk. Schlüssel           Wert
Projekt1&T1         3.5
Projekt1&T2         4
Projekt2&T3         4

Jetzt kannst du mit Sverweis eine Matrix machen
               T1       T2         T3
Projekt   (x)

Beim (x) fragst du dann..

Serverweis (Projekt (links) & T1 (oben); Anderes Blatt A bis B; 2; falsch)

Iamiam 05.08.2016, 13:53

der normale SVerweis liefert halt nur die erste Findung und benötigt die Suchspalte ganz links vom Ergebnisbereich.

0
safur 05.08.2016, 15:17
@Iamiam

Falsch, deshalb hast du den verketteten Schlüssel. Er zeigt dir alle drei Ausprägungen.

Was hier allerdings nicht funktioniert wenn du sogar Dopplungen bei T1 hast.

Entweder würde ich mal mein Datenmodell überlegen oder den Sinn der Abfrage.

Also bei meiner Variante käme sowas raus
                 T1          T2         T3
Projekt
89485       3.4          5.0         1.0
89486       3.                          1.3
89488                                    2.0

Wobei hier das Projekt eindeutig sein müsste. Wie ich sehe hast du das gleiche Projekt mehrfach und dazu noch zwei unterschiedliche Werte bei T1.

0

Was möchtest Du wissen?