Excel TRANSPOSE-Funktion

Was macht die Transponierungsfunktion in Excel?

Die Transponierungsfunktion in Excel wird verwendet, um die Werte zu drehen oder die Werte von Zeilen zu Spalten und von Spalten zu Zeilen zu wechseln, da diese Funktion ein Array von Zellen als Argument verwendet. Um es auszuführen, müssen Sie CSE oder Strg + Umschalt + Eingabetaste drücken. Wählen Sie zuerst die genaue Größe des Bereichs aus, der transponiert werden soll, und verwenden Sie dann die Transponierungsformel.

Syntax

Unten finden Sie die TRANSPOSE-Formel in Excel.

Argumente

Array: Erforderlich. Eine Reihe von Zellen, die Sie transponieren möchten.

Es ist eine Array-Funktion .

Wie verwende ich die TRANSPOSE-Funktion in Excel? (mit Beispielen)

Sie können diese TRANSPOSE Function Excel-Vorlage hier herunterladen - TRANSPOSE Function Excel Template

Beispiel 1

Die Originaldaten, die Sie transponieren möchten, befinden sich in A3: B8.

  • Schritt 1: Wählen Sie hier den Bereich aus, in dem Sie Ihren transponierten Wert wünschen, D6: I7.

  • Schritt 2: Geben Sie nun die TRANSPOSE-Formel in die ausgewählte Region ein und drücken Sie STRG + UMSCHALT + EINGABETASTE (oder BEFEHL + UMSCHALT + EINGABETASTE in Mac).

= TRANSPOSE (A3: B7)

Sie erhalten den transponierten Ausgang in D6: I7 wie unten gezeigt.

In diesem Fall ist D3: I4 die transponierte Ausgabe durch Kopieren und Einfügen und D6: I7 ist die transponierte Ausgabe.

Beachten Sie, dass die Excel TRANSPOSE-Funktion lautet

{= TRANSPOSE (A3: B7)}

Die oben gezeigten geschweiften Klammern {} zeigen an, dass die Funktion als Array-Formel eingegeben wurde.

Wenn Sie nun einen Wert in den Originaldaten ändern müssen, z. B. B8 = SUMME (B3: B7), wird die transponierte Ausgabe, in der Sie die TRANSPOSE verwendet haben, automatisch aktualisiert, jedoch nicht in dem Fall, in dem Sie copy- verwendet haben. Einfügeoption.

Es kann auch zusammen mit anderen Funktionen verwendet werden, um die gewünschte Ausgabe zu erhalten. Lassen Sie uns einige Beispiele dafür sehen.

Beispiel 2

Angenommen, Sie haben eine Liste der Schüler und ihrer Noten, wie unten gezeigt. Einige der Studenten gaben keine Prüfungen. Daher bleibt die Markierungsspalte für diese Schüler leer.

Sie möchten diese Daten transponieren und wenn die Zelle leer ist, möchten Sie "Abwesend" in diese Zelle einfügen.

Sie können dies tun, indem Sie Transponieren zusammen mit der IF-Funktion in Excel verwenden. Sie können dazu die folgende TRANSPOSE-Formel in Excel verwenden:

= TRANSPOSE (WENN (B3: D10 = ””, ”ABSENT”, B3: D10))

'WENN (B3: D10 = "", "ABSENT" "bedeutet, dass wenn eine Zelle im Bereich B3: D10 leer ist, stattdessen" Abwesend "platziert wird. Der angegebene Bereich wird transponiert.

Angenommen, in dieser Liste wird die TRANSPOSE-Formel wie folgt geändert, wenn ein Schüler weniger als 70 Punkte erzielt hat oder nicht in der Prüfung als FAIL eingestuft wurde.

= TRANSPOSE (WENN (B3: D10 <70, "FAIL", B3: D10)

und Sie erhalten die endgültige Ausgabe wie unten gezeigt.

Beispiel 3

Manchmal müssen einige Zeichen zusammen mit der Transponierung zu den vorhandenen hinzugefügt werden. Angenommen, Sie haben eine Liste von IDs in B4: B7, wie unten gezeigt.

Sie möchten die IDs transponieren und das Präfix "ID" hinzufügen. Sie können dies mit der Formel in Excel tun:

= TRANSPOSE ("ID" & B4: B7)

Dadurch werden die Daten transponiert und jedem Zelleninhalt das Präfix hinzugefügt.

Sie können auch einen beliebigen Wert als Suffix mit der obigen TRANSPOSE-Formel hinzufügen. Dies kann hilfreich sein, wenn Sie die Transponierungsfunktion verwenden, um die in verschiedenen Zeilen angegebenen Wörter in einer einzelnen Zelle zu verketten. Dazu können Sie die folgenden Schritte ausführen:

  • Geben Sie die Syntax ein: CONCATENATE (TRANSPOSE (B4: B7 & ”,”). Drücken Sie nicht die Eingabetaste.
  • Wählen Sie die Excel-Transponierungsfunktion wie unten gezeigt.

  • Drücken Sie die Taste F9.

  • Entfernen Sie {} aus der Formel

  • Drücken Sie Enter.

Dadurch werden die Zellen in einer einzelnen Zelle verkettet.

Beispiel 4

Angenommen, Sie haben Bibliotheksdaten, in denen die verschiedenen Themen in einem Regal angeordnet sind, und es gibt verschiedene Regale, wie unten gezeigt.

Möglicherweise möchten Sie anhand dieser Daten (B4: E6) den Standort eines der Probanden (in H3: H11 angegeben) abrufen.

Sie können dies mithilfe der Syntax tun

= INDEX ($ B $ 4: $ B $ 6, MATCH (1, MMULT (- ($ C $ 4: $ E $ 6 =), TRANSPOSE (SPALTE ($ C $ 4: $ E $ 6) ^ 0)), 0))

Lassen Sie uns sehen, wie diese TRANSPOSE-Formel in Excel funktioniert.

  • - ($ C $ 4: $ E $ 6 =)

Wenn in H3 angegeben, ist es - ($ C $ 4: $ E $ 6 = H3).

Dies bildet ein Array von 1 und 0, um das Vorhandensein oder Fehlen des Werts anzuzeigen. Für Biology in H3 wird das Array wie folgt gebildet: {1,0,0; 0,0,0; 0,0,0}

  • TRANSPOSE (SPALTE ($ C $ 4: $ E $ 6) ^ 0))

Es wird ein Array mit 3 Zeilen in einer Spalte erstellt, und eine Potenz von 0 stellt sicher, dass die Zahlen in 1 konvertiert werden. Die Ausgabe dieser Funktion lautet {1,1,1}

  • MMULT (- ($ C $ 4: $ E $ 6 =), TRANSPOSE (SPALTE ($ C $ 4: $ E $ 6) ^ 0))

Das MMULT in Excel multipliziert die Ausgabe von A und B.

MMULT ({1,0,0; 0,0,0; 0,0,0}, {1,1,1}) gibt die Ausgabe {1; 0; 0} aus

  • MATCH (1, MMULT (), 0)

Es wird die Ausgabe von C mit 1 übereinstimmen. MATCH (1, {1; 0; 0}, 0) gibt die Position 1 zurück.

  • INDEX ($ B $ 4: $ B $ 6, MATCH (, 0))

Es wird der Wert der Zelle identifiziert, für die die Übereinstimmungsfunktion die Position angegeben hat. INDEX ($ B $ 4: $ B $ 6, 1) gibt A1 zurück.

Ähnlich wird es für "Geographie" als Thema sein:

  1. - ($ C $ 4: $ E $ 6 = D6) gibt {0,0,0; 0,0,0; 0,1,0} zurück
  2. TRANSPOSE (COLUMN ($ C $ 4: $ E $ 6) ^ 0)) wiederholt {1,1,1}
  3. MMULT ({0,0,0; 0,0,0; 0,1,0}, {1,1,1}) gibt {0; 0; 1} zurück
  4. MATCH (1, {0; 0; 1}, 0) gibt 3 zurück
  5. INDEX ($ B $ 4: $ B $ 6, 3) gibt A3 zurück.

Dinge, an die man sich erinnern sollte

  1. Mit der Option Kopieren und Einfügen werden Duplikate erstellt.
  2. Diese Funktion verknüpft die Daten mit der Quelle.
  3. Wenn die Anzahl der ausgewählten Zeilen und Spalten nicht den Spalten und Zeilen der Quelldaten entspricht, wird ein # VALUE-Fehler ausgegeben
  4. Nach der Eingabe kann keine einzelne Zelle, die Teil dieser Funktion ist, geändert werden.
  5. Drücken Sie STRG + UMSCHALT + EINGABETASTE, um die Formel in Excel einzugeben.