Schlagwort: MTRANS

Der beste vierbeinige Freund des Menschen ist das Bett.

Merkwürdige Formeln

Guten Abend Herr Martin,

vor einiger Zeit haben Sie mir bereits bei einer meiner Excel-Problematiken geholfen. Nun hoffe ich auf Ihre erneute Unterstützung.

Ich schildere direkt mein Anliegen und freue mich, falls Sie Zeit finden, mir in diesem Fall auf die Sprünge zu helfen.

Anbei finden Sie eine Bildschirmaufnahme einer exemplarischen Darstellung der betreffenden Tabellen.

In der Tabelle 1 (B2:D17) werden den dort erfassten Namen jeweils eine Personalnummer zugeordnet. Diese ist immer konstant. Darüberhinaus werden die Personen in variable Gruppen eingeteilt. 

In Tabelle 2 soll nun Excel mit Hilfe einer mir unbekannter Formel(kette)

1) erkennen, welche Gruppen überhaupt relevant sind (A-D) und mir diese einmalig untereinander auflisten  

zudem soll Excel

2) die Personalnummern, welche der jeweiligen Gruppe zugeordnet wurden, nebeneinander in einer Zeile auflisten

–> Tabelle 3 bildet die Zieldarstellung ab.

–> Bisher wurde „Tabelle 2“ immer händisch befüllt. Ich erhoffe mir durch einen Kniff eine Automatisierung des Ganzen.

eine besondere Herausforderung stellt vielleicht die Begrenzung durch die Excel Version 2016 dar, die mir leider so vorgesetzt wird.

Falls Sie eine Idee oder einen Lösungsansatz dazu haben, würden Sie nicht nur mir sondern auch vielen Kollegen eine riesige Freude bereiten.

Vielen Dank schon mal im Voraus!

####

Hallo Herr G.,

Haben Sie Excel in Microsoft 365? Dann können Sie die Funktionen EINDEUTIG verwenden, um eine eindeutige Liste zu erhalten (die Gruppen). Und SORTIEREN hilft beim Sortieren

Und FILTERN, um die Liste der Personalnummern zu erzeugen. MTRANS transponiert diese Liste:

Hallo Her Martin,

mit meinem privaten Laptop ist Ihr Vorschlag gut umsetzbar, da ich Microsoft 365 besitze. Auf den Dienstrechnern bin ich leider durch MS OFFICE 2016 beschränkt. Dort erstellt Excel die Befehle / Funktionen nicht.

Viele Grüße

####

Hallo Herr G.,

Eine Liste der eindeutigen Funktionen erhält man mit einer Pivottabelle.

Die Funktion AGGREGAT, die in Excel 2010 eingeführt wurde, ermittelt mit dem Parameter KKleinste den ersten, zweiten, dritten, … Wert.

Hilft das?

Liebe Grüße

Rene Martin PS: wenn ich nicht weiter weiß, verwende ich Copilot oder ChatGPT

Kommentar hinterlassen , ,

Mein Schatz sagt oft „Du Hengst“ zu mir. Vorhin zum Beispiel: „du Hengst nur noch auf der Couch rum!“

VBA-Befehle

Einfach nicht aufgepasst!

Programmieren Sie VBA? Programmieren Sie Formeln in VBA? Auf einem Tabellenblatt befinden sich in den Spalten BJ und BL Daten, die vertikal angeordnet sind:

Auf einem anderen Blatt wird Bezug auf diese Daten genommen:

Während ich die erste Spalte nach unten ziehen kann, muss ich die Formeln der ersten Zeile getrennt eingeben – ich möchte weder mit MTRANS, INDIREKT noch mit BEREICH.VERSCHIEBEN arbeiten. Es handelt sich um maximal sechs Werte.

Die Formeln müssen per VBA neu geschrieben werden. Der Makrorekorder ermittelt für die Formel

=WENN(tbl_Basisdaten!BJ2="";"";tbl_Basisdaten!BJ2)

der Zelle A2 den VBA-Code:

ActiveCell.FormulaR1C1 = " =IF(tbl_Basisdaten!RC[61]="""","""",tbl_Basisdaten!RC[61])"

Dabei ist RC[61] ein relativer Bezug: R -> gleiche Zeile; C[61] -> Spalte um 61 Spalten verschoben. Wäre der Bezug absolut ($BJ$2) wäre der Code:

R2C62

also: in Zeile 2 und in Spalte 62 – egal, wo sich die Zielzelle befindet. Der Code wird in einer Schleife verwendet:

For i = 1 To 6
   ThisWorkbook.Worksheets("tbl_Risikomatrix").Range("A1").Offset(i, 0).FormulaR1C1 = _
      "=IF(tbl_Basisdaten!RC[61]="""","""",tbl_Basisdaten!RC[61])" ' -- erste Spalte A
Next i

Klappt famos! Und nun die erste Zeile. Der Makrorekorder zeichnet auf:

ActiveCell.FormulaR1C1 = _
    "=IF(tbl_Basisdaten!R[1]C[62]="""","""",tbl_Basisdaten!R[1]C[62])"

Das kann doch leicht angepasst werden:

For i = 1 To 6
    ThisWorkbook.Worksheets("tbl_Risikomatrix").Range("A1").Offset(0, i).FormulaR1C1 = _
        "=IF(tbl_Basisdaten!R[" & i & "]C[62]="""","""",tbl_Basisdaten!R[" & i & "]C[62])" ' -- erste Zeile 1
        
Next i

Padautz – das funktioniert nicht! In B1 greife ich auf Spalte 63 zu, also 62 Spalten „neben mir“. In C1 dagegen benötige ich die Spalte, die sich 61 Spalten neben mir befindet, in D1 dagegen 60 Spalten. Also noch einmal:

For i = 1 To 6
    ThisWorkbook.Worksheets("tbl_Risikomatrix").Range("A1").Offset(0, i).FormulaR1C1 = _
        "=IF(tbl_Basisdaten!R[" & i & "]C[" & (63 - i) & "]="""","""",tbl_Basisdaten!R[" & i & "]C[" & (63 - i) & "])" ' -- erste Zeile 1
Next i

Und das klappt! Man muss so aufpassen bei den Bezügen! Und beim Umdenken von =BL2 auf =R[1]C[62].

Kommentar hinterlassen , , , , ,

Jeder Topf hat einen Deckel! Aber ich bin da, glaub ich, ein Wok

Merkwürdige Formeln

Lieber Herr Martin,

Ein Studienkollege meines Sohnes hat auf seinem PC Excel als Programm nicht und benutzte Excel als Online-Version (one-drive). Er wollte dort eine Tabelle transportieren. Er findet aber dort nicht die entsprechende Funktion.

In Excel geht das ja über Einfügen > Inhalte einfügen à Transponieren oder über den rechten Mausklick. Aber in diesem Online-Excel scheint es diese Funktion nicht zu geben. Oder gibt es doch eine Möglichkeit einer Transponierung auf dieser Excel-Website? Vorausgesetzt, daß Sie diese mal benutzt haben?

Hallo Herr F.,

Ich habe nachgeschaut: Excel online hat viele Funktionen nicht – beispielsweise transponieren. Auch die Funktion MTRANS klappt nicht, weil Excel online keine Matrixfunktionen unterstützt.

Man kann die Tabelle natürlich mit Formeln transponieren. Beispielsweise mit:

=BEREICH.VERSCHIEBEN($C$1;SPALTE(A1);ZEILE(A1))

Es funktioniert auch mit INDIREKT

Kommentar hinterlassen , , , ,