Mararka qaarkood, waxaa laga yaabaa inaad rabto inaad xogta ku jirta hal tiir u beddesho xogta habaysan ee Excel . Tani waa gaar ahaan kiiska marka aad koobiyayso xogta aan habaysanayn ee tifaftiraha kelmad xaashida Excel . Xaaladdan oo kale, dhammaan xogta waxaa lagu ururiyay hal saf.

U beddelo tiirarka ^{(Convert Columns)}safafka^(Rows) ku jira Excel oo wata foomamka^(Formulas)

Iyadoo aad si fudud ugu beddeli karto buug-shaqeedka habaysan ama qayb ka mid ah safafka ilaa tiirarka adoo isticmaalaya shaqada Transpose , taasi maahan kiiska marka xogta aan la habeynin oo ay ku faafto hal tiir.

Haddii xogta aan la habeynin midkoodna aysan lahayn qaab, waxaa laga yaabaa inaad marka hore u baahato inaad habayso qaab. Si kastaba ha ahaatee, sida caadiga ah, waxaa lagu soo bandhigaa xog aan habaysanayn oo qaabaysan.

Tusaale ahaan. Aynu ka soo qaadno kiis aad haysato liis xog ah oo lagu habeeyay hal tiir oo isku xiga Magaca^(Name) , DOB , iyo Fasalka^(Grade) .

Karan

01/06/1987

A

Justin

09/08/1964

D

Bob

04/05/1996

B

Jason

08/09/1984

C

Xaaladdan oo kale, waxaa laga yaabaa inaad rabto inaad u habayso xogta sida isku xigta ee Magaca^(Name) , DOB , iyo Fasalka^(Grade) ee safafka iyo tiirarka ee ma aha oo kaliya hal tiir. Tan waxa lagu samayn karaa iyadoo la isticmaalayo habka OFFSET . Erayga habka OFFSET waxa uu noqonayaa:

OFFSET($A$1,(ROW()-f_row)*rows_in_set+INT((COLUMN()-f_col)/col_in_set), MOD(COLUMN()-f_col,col_in_set))

Sida lagu sharaxay tusaalaha kor ku xusan, haddii macluumaadka lagu faafiyo tiirka A ee bilawga A1, qaacidada waxay noqon doontaa:

=OFFSET($A$1,(ROW()-2)*3+INT((COLUMN()-3)),MOD(COLUMN()-3,1))

Iyadoo qaaciidadani u qaybinayso macluumaadka 3da saf, tirada tiirarkuna ma go'an. Tirada tiirarka waxay kuxirantahay dhererka xogta. Xaalad kasta oo tiir kasta oo madhan ee xogta oo dhan ah, waxa lagu beddelaa 0.

Ka soo qaad^(Suppose) inaad u baahan tahay xogtan habaysan laga bilaabo C2 oo aad ka warqabto in xogta lagu habeeyay 3 saf, geli qaacidadan unugga C2. Ka dib qaacidada u soo jiid 3 saf oo hoos u dhig tiirarka ilaa aad ka bilaabayso helitaanka 0 sida gelitaanka.

Marka xogta la habeeyo, waxaad ku xusi kartaa cinwaan-hoosaadka sare oo ku dheji meesha saxda ah.

Rajo inay ku caawiso!

How to convert Columns to Rows in Excel with Formulas

At times, you may want to convert data in one column to organized data іn Excel. This is especially thе case when you copy-paѕtе unorganized data from a word editor to an Excel sheеt. In such a caѕe, all the data is comрiled across a single row.

Convert Columns to Rows in Excel with Formulas

While you can easily convert an arranged workbook or part of it from rows to columns using the Transpose function, that isn’t the case when the data is unorganized and spread across a single column.

If the unorganized data doesn’t have a pattern either, you might first need to sort out a pattern. However, usually, you are presented with unorganized data in a pattern.

Eg. Let us assume a case where you have a list of data organized across a single column in the sequence Name, DOB, and Grade.

Karan

01/06/1987

A

Justin

09/08/1964

D

Bob

04/05/1996

B

Jason

08/09/1984

C

In this case, you may want to organize the data in the sequence of Name, DOB, and Grade across rows and columns and not just a single column. This could be done using the OFFSET formula. The syntax for the OFFSET formula would become:

OFFSET($A$1,(ROW()-f_row)*rows_in_set+INT((COLUMN()-f_col)/col_in_set), MOD(COLUMN()-f_col,col_in_set))

As explained in the example mentioned above, if the information is spread across column A starting A1, the formula would become:

=OFFSET($A$1,(ROW()-2)*3+INT((COLUMN()-3)),MOD(COLUMN()-3,1))

While this formula divides the information across the 3 rows, the number of columns isn’t fixed. The number of columns depends on the length of the data. In the case of any blank column across the data, it is replaced by a 0.

Suppose you need this data arranged starting C2 and are aware that the data is to be organized across 3 rows, enter this formula in cell C2. Then pull the formula across 3 rows and down across the columns till you start getting 0 as entries.

Once the data is organized, you can mention the subheadings on the top and cut-paste it at the right location.

Hope it helps!

Related posts

• Sida loo codsado Midabka safafka ama Saafka kale ee Excel

• Sida loo beddelo safafka iyo tiirarka gudaha Excel

• Sida Looga Badalo Saafka ama Saafka Excel [Tallaabo Talaabo]

• Sida loo qariyo xaashiyaha, unugyada, tiirarka, iyo qaaciidooyinka gudaha Excel

• Sida loo qaboojiyo safafka iyo tiirarka gudaha Excel

• Sida loo qaybiyo hal tiir oo loo kala qaybiyo tiirar badan oo Excel ah

• Sida qoraalka loogu qaybiyo tiirarka ku jira Excel iyo Google Sheets

• Sida loo tirtiro safafka badan ee Microsoft Excel hal mar

• Excel, Word ama PowerPoint ma bilaabi karaan markii ugu dambeysay

• Sida loo daabaco unugyada la doortay ee Excel ama Google Sheets hal bog

• Sida loo isticmaalo shaqada ISODD ee Excel

• Sida loo tiriyo lambarka Haa ama Maya ee Excel

• U beddelo CSV una beddelo Excel (XLS ama XLSX) adoo isticmaalaya khadka taliska ee Windows

• Sida loogu isticmaalo shaqada MID iyo MIDB gudaha Microsoft Excel

• Sida loo isticmaalo Percentile.Exc function gudaha Excel

• Sida loo isticmaalo shaqada DISC gudaha Excel

• Sida loo dayactiro buug-shaqeedka Excel ee xumaaday

• Sida loo geliyo shaxda firfircoon ee xaashida Excel

• Sida loo isticmaalo shaqada COOSE ee Excel

• Microsoft Excel waxay isku dayaysaa inay soo ceshato macluumaadkaaga