Excel TRANSPOSE-funktion

Vad gör transponeringsfunktion i Excel?

Transponeringsfunktion i excel används för att rotera värdena eller byta värden från rader till kolumner och kolumner till rader, eftersom den här funktionen tar en rad celler som ett argument så för att utföra det behöver vi trycka på CSE eller Control + Shift + Enter, välj först den exakta storleken på det intervall som behöver transponeras och använd sedan transponeringsformeln.

Syntax

Nedan är TRANSPOSE-formeln i Excel.

Argument

array: Obligatoriskt. Ett antal celler som du vill transponera.

Det är en matrisfunktion .

Hur använder jag TRANSPOSE-funktionen i Excel? (med exempel)

Du kan ladda ner denna Excel-mall för TRANSPOSE här - TRANSPOSE-funktion Excel-mall

Exempel nr 1

De ursprungliga uppgifterna du vill transponera finns i A3: B8.

  • Steg 1: Välj det område där du vill ha ditt transponerade värde, här, D6: I7.

  • Steg 2: Skriv nu TRANSPOSE-formeln i den valda regionen och tryck på CTRL + SKIFT + RETUR (eller KOMMANDO + SKIFT + RETUR i Mac).

= TRANSPONERA (A3: B7)

Du får den transponerade utgången i D6: I7 som visas nedan.

I detta är D3: I4 den kopieringspasta-transponerade utgången och D6: I7 är genom transponeringsutmatningen.

Lägg märke till att Excel TRANSPOSE-funktionen är

{= TRANSPOSERA (A3: B7)}

De lockiga hakparenteserna {} som visas ovan indikerar att funktionen har matats in som en Array Formula.

Om du nu måste ändra något värde i originaldata, säg B8 = SUM (B3: B7) kommer den transponerade utgången, där du har använt TRANSPOSE, att uppdateras automatiskt, men inte i det fall du har använt kopia- klistra in alternativet.

Den kan också användas tillsammans med andra funktioner för att få önskad effekt. Låt oss se några exempel på detta.

Exempel 2

Antag att du har en lista över studenter och deras betyg enligt nedan. Några av studenterna gav inte tentor. Därför lämnas markeringskolumnen tom för de studenterna.

Du vill transponera dessa data, och när cellen är tom, vill du sätta "Frånvarande" i den cellen.

Du kan göra det genom att använda Transpose tillsammans med IF-funktionen i Excel. Du kan använda följande TRANSPOSE-formel i excel för att göra det:

= TRANSPOSERA (OM (B3: D10 = ””, ”AVSNITT”, B3: D10))

'IF (B3: D10 = ”“, “ABSENT” ”betyder att om någon cell i intervallet B3: D10 är tom, kommer den att placeras” Frånvarande ”istället. Den kommer att transponera det givna området.

Antag att i denna lista, om någon student har fått mindre än 70 poäng eller inte har dykt upp i tentan anses vara FAIL, kan du ändra TRANSPOSE-formeln som:

= TRANSPONERA (OM (B3: D10 <70, ”FAIL”, B3: D10)

och du får den slutliga utdata som visas nedan.

Exempel # 3

Ibland finns det ett behov av att lägga till några tecken till de befintliga tillsammans med transponera. Antag att du har en lista med ID i B4: B7 enligt nedan.

Du vill transponera id: n och lägga till prefixet "ID" till det. Du kan göra det med Formeln i Excel:

= TRANSPOSERA (“ID” & B4: B7)

Detta kommer att transponera data och lägga till prefixet till vart och ett av cellinnehållet.

Du kan också lägga till valfritt värde som suffix med ovanstående TRANSPOSE-formel. Detta kan vara till hjälp när du använder transponeringsfunktionen för att sammanfoga orden i olika rader till en enda cell. För att göra det kan du följa följande steg:

  • Skriv syntaxen: CONCATENATE (TRANSPOSE (B4: B7 & ”,”). Tryck inte på Enter.
  • Välj excel transponera-funktionen enligt nedan.

  • Tryck på F9-tangenten.

  • Ta bort {} från formeln

  • Tryck enter.

Det sammanfogar cellerna i en enda cell.

Exempel # 4

Antag att du har biblioteksdata där de flera ämnena är ordnade på en hylla, och det finns olika hyllor som visas nedan.

Du kanske vill hämta platsen för något av ämnena (anges i H3: H11) med hjälp av dessa data (B4: E6).

Du kan göra det med hjälp av syntaxen

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

Låt oss se hur denna TRANSPOSE-formel i Excel fungerar.

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

När det ges i H3 blir det - ($ C $ 4: $ E $ 6 = H3).

Detta bildar en matris av 1 och 0 för att indikera närvaron eller frånvaron av värdet. För biologi i H3 kommer den att bilda matrisen som: {1,0,0; 0,0,0; 0,0,0}

  • TRANSPONERA (KOLONN ($ C $ 4: $ E $ 6) ^ 0))

Det skapar en matris med 3 rader i en kolumn och en effekt på 0 säkerställer att siffrorna konverteras till 1. Utgången för denna funktion är {1,1,1}

  • MMULT (- ($ C $ 4: $ E $ 6 =), TRANSPONERA (KOLONN ($ C $ 4: $ E $ 6) ^ 0))

MMULT i excel multiplicerar produktionen av A och B.

MMULT ({1,0,0; 0,0,0; 0,0,0}, {1,1,1}) ger utdata {1; 0; 0}

  • MATCH (1, MMULT (), 0)

Det matchar utgången från C med 1. MATCH (1, {1; 0; 0}, 0) returnerar position 1.

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

Det identifierar värdet på cellen för vilken matchningsfunktionen angav positionen. INDEX ($ B $ 4: $ B $ 6, 1) returnerar A1.

På samma sätt för "Geografi" som ämne kommer det att vara:

  1. - ($ C $ 4: $ E $ 6 = D6) returnerar {0,0,0; 0,0,0; 0,1,0}
  2. TRANSPOSERA (KOLONN ($ C $ 4: $ E $ 6) ^ 0)) omgångar {1,1,1}
  3. MMULT ({0,0,0; 0,0,0; 0,1,0}, {1,1,1}) returnerar {0; 0; 1}
  4. MATCH (1, {0; 0; 1}, 0) returnerar 3
  5. INDEX ($ B $ 4: $ B $ 6, 3) returnerar A3.

Saker att komma ihåg

  1. Alternativet kopiera och klistra in skapar dubbletter.
  2. Denna funktion länkar data till källan.
  3. Om antalet valda rader och kolumner inte är lika med källdataens kolumner och rader, kommer det att ge ett #VALUE-fel
  4. När den har angetts kan ingen individuell cell som ingår i denna funktion ändras.
  5. Tryck på CTRL + SKIFT + ENTER för att ange formeln i Excel.