Dadka intooda badan waxay yaqaaniin isticmaalka tixraacyada qaraabada ee Excel^{(in Excel)} . Tani waa sababta oo ah tixraacyada unugga ee xaashida xaashida ee Excel waxay u dhigmaan habka tixraaca qaraabada.

Si kastaba ha ahaatee, waxaa jira waqtiyo marka tixraacyada qaraabada ah ay dhibsadaan. Waxay bedeli doontaa tixraacyada unugga mar kasta oo aad nuqul ka sameysato unugyada ama buuxiso tiirarka iyo safafka. Haddii aadan rabin tixraacyo inay isbeddelaan, waxaad u baahan doontaa inaad raacdo tixraacyo dhammaystiran ama tixraacyo isku dhafan (ay ku jiraan tixraacyo dhammaystiran iyo qaraabo).

Tixraac dhammaystiran, tiirarka iyo tixraacyada safafka labaduba waa “xiran yihiin,” sidaa darteed midkoodna waxba ma beddelo markaad koobiyayso ama ka buuxinayso unuggaas.

Maqaalkani wuxuu eegi doonaa sida aad u isticmaali karto tixraacyo dhammaystiran si aad Excel ugu dhaqanto sida aad rabto xogta.

Sida Tixraacyada^(References) Qaraabada ay ugu shaqeeyaan Excel

Markaad qiyamka geliso xaashida xaashida ee Excel , unug kastaa wuxuu leeyahay xaraf gaar ah iyo nambar loo qoondeeyay. Tani waxay matalaysaa tiirka iyo safka unuggaas.

Tusaale ahaan, qiimaha "1" ee xaashida hoose waxay ku taal tiirka A iyo safka 2. Markaa "tixraaca" unuggani waa A2 .

Haddii aad rabto inaad ku samayso xisaabinta unugga soo socda ee ku salaysan unuggaan, adigoo ku daraya 1, waxaad ku qori doontaa qaacidada soo socota:

=A2+1

Qaaciddadani waxay geli doontaa qiimaha A2 qaacidada, xisaabi doonta, ka dibna natiijada u soo saari doonta unugga ay qaaciidadani ku taal.

Markaad riixdo Gelida^(Enter) , waxaad arki doontaa natiijada.

Marka la tixraacayo qaraabo, maahan inaad caanahan ku qorto unug kasta oo kale. Waxa kaliya oo ay tahay inaad sameyso waa jiid geeska unugga oo leh qaacidada asalka ah hoos ilaa intaad rabto. 

Unugga xiga, tixraaca A2 wuxuu noqonayaa A3. Unugga ka hooseeya, A3 wuxuu noqonayaa A4. Si kale haddii loo dhigo, Excel wuu ogyahay inaad rabto inaad 1 ku darto unuggii hore, markaa Excel waxay cusbooneysiisaa lambarka (Tixraaca safka) si waafaqsan markaad hoos u jiideyso.

Tani waxay u shaqeysaa si la mid ah haddii aad ku jiiddo qaacidada tiirarka. Halkii la cusbooneysiin lahaa lambarka, Excel wuxuu cusbooneysiin doonaa qaybta dambe ee tixraaca (tiirka) si uu had iyo jeer u tixraaco unugga ka sarreeya.

Tiirka dhinaca midig waxa uu ka kooban yahay B2, dhanka midig waxa ku jira C2, iyo wixii la mid ah.

Kani waa tusaale fudud oo ku saabsan sida ciwaanka qaraabada ahi ugu shaqeeyo tiirka iyo tixraacyada unugga labadaba.

Sida Tixraacyada Dhabta^(References) ah ay ugu shaqeeyaan Excel

^(Absolute)Tixraacyada dhammaystiran ee Excel waxay^(Excel) kuu oggolaanayaan inaad tixraacdo isla unug, intii aad u oggolaan lahayd in Excel si toos ah u cusbooneysiiyo^{(Excel automatically update)} tixraacyada safka ama tiirka adiga. Tixraaca "isku dhafan" waa haddii aad quful kaliya safka ama tiirka, iyo "tixraaca saxda ah" waa marka aad labadaba xirto.

Bal aan eegno tusaalooyin.

Aynu nidhaahno xaashida faaruqintaadu waxay leedahay "10" oo saf sare ah, oo aad rabto in saf kasta oo ka hooseeya uu tiradaas ku dhufto lambarka gacanta bidix. 

Si tan loo sameeyo, waxaad geli doontaa qaacido u eg sidan:

=B$2*A3

Tani waxay qufulaysaa tixraaca "2" si aysan u beddeli doonin tixraaca safka haddii aad unugga ku jiid qaacidadan hoos unugyada ka hooseeya. Mar haddii A3 ay tahay "furan" labadaba tixraaca safka iyo tiirka ayaa wali si toos ah u beddeli doona oo had iyo jeer tixraacaya unugga bidixda.

Waxaad ogaan doontaa inay tani shaqaynayso oo keliya sababtoo ah waxaad hoos ugu soo jiidaysaa unugyada isla tiirka. Markaa uma baahnid inaad xidhid tiirka (B) adigoo hor dhigaya calaamad dollar ($)

Dhibaatada tan ayaa ah in haddii aad rabto inaad isticmaasho isla qaacidada midigta qaacidada asalka ah, tixraaca "B" wuu isbedeli doonaa, qaacidaduna mar dambe ma tixraaci doonto B2 sidii loogu talagalay.

Aan eegno sida loo isticmaalo tixraacyo dhammaystiran halkii tixraacyo isku dhafan si aan buuxinta labada dhinacba si sax ah u shaqeeyo.

Si sax ah u Isticmaalka Tixraaca ^(References)Dhabta^(Absolute) ah ee Excel

Si aad u isticmaasho tixraaca saxda ah ee qaaciidadan, waa inaad si sax ah uga fikirtaa waxaad isku dayayso inaad samayso.

Xaaladdan oo kale, waxaan rabnaa dabeecadaha soo socda marka la buuxinayo dhinaca midig.

• Had iyo jeer tixraac qiimaha unugga B2
• Had iyo jeer tixraac qiimaha tiirka A
• U rog tixraaca safka tiirka A una beddel safka hadda ee qaacidada

Markaad eegto dabeecadahan, waxaad hadda garanaysaa waxaad u baahan tahay inaad "quful" iyo waxaadan samaynin. Labada "B" iyo "2" waxay u baahan yihiin in la xiro (aan isbeddelin). Sidoo kale, tiirka A wuxuu u baahan yahay in la xiro.

Markaa qaacidadaada ku jirta B3 waxay u baahan tahay inay u ekaato sidan: =$B$2*$A3

Hadda marka aad isla unuggaas hoos ama kor u soo jiiddo, qaaciddada waxay u shaqeysaa sidii loogu talagalay.

Si sax ah u adeegsiga tixraaca saxda ah waxay heli kartaa khiyaano, sidaa darteed waxaa lagama maarmaan ah inaad waqti ku qaadato inaad si taxadar leh uga fiirsato sida aad u rabto Excel inuu u cusbooneysiiyo qaacidada markaad buuxineyso tiirarka ama safafka^{(columns or rows)} labada dhinac.

Baaskiilada Iyadoo loo marayo Noocyada Tixraaca^{(Reference Types)} ee Excel

Waxaad dedejin kartaa habka marka aad ku qorayso qaacidooyinka^{(typing formulas)} leh tixraacyo dhammaystiran adiga oo riixaya furaha F4 , kaas oo ka dhigi doona tixraaca unugga mid dhammaystiran.

Cursorku wuxuu ku jiri karaa labada dhinac ee tixraaca unugga (ama xitaa dhexda) markaad riixdo F4, waxayna wali u rogi doontaa tixraacaas hal tixraac oo dhammaystiran.

Haddii aadan rabin gabi ahaanba (tusaale ahaan, isku dhafan), sii wad taabo F4 ilaa tixraacu u ekaado sida aad rabto.

Haddii aad rabto in aad ku darto nooc kasta oo tixraac ah unugyo kale ee qaacidada, kaliya dhig calaamaddaada halkaas oo dib u bilaw inaad mar kale baaskiil ku dhex marto F4.

Marka aad habayso caanahaaga, kaliya taabo Gelida^(Enter) oo billow buuxinta xaashida faafinta jihada aad rabto. Haddii aad u dejiso tixraacyadaada saxda ah, wax walba waa inay u shaqeeyaan sida la filayo.

How to Use Absolute References in Excel

Mоst people are familiar with using relative refеrences in Excel. This is because cell references in Excel spreadsheets default to the relative reference method.

However, there are times when relative references get annoying. It will change cell references any time you copy cells or fill columns and rows. If you don’t want references to change, you’ll need to go with absolute references or mixed references (including relative and absolute references).

In an absolute reference, both the column and row references are “locked,” so neither of those change when you copy or fill from that cell.

This article will look at how you can use absolute references to make Excel behave the way you want with data.

How Relative References Work in Excel

When you enter values into an Excel spreadsheet, each cell has a specific letter and number assigned to it. This represents the column and row of that cell.

For example, the value of “1” in the spreadsheet below is in column A and row 2. So the “reference” to this cell is A2.

If you want to perform a calculation in the next cell based on this cell, by adding 1 to it, you will write the following formula:

=A2+1

This formula will insert the value from A2 into the formula, calculate it, and then output the result into the cell where this formula is.

When you press Enter, you’ll see the result.

With relative referencing, you don’t have to type this formula into every other cell. All you have to do is drag the corner of the cell with the original formula down as far as you’d like. 

In the next cell, the reference to A2 will become A3. In the cell below that, A3 will become A4. In other words, Excel knows that you want to add 1 to the previous cell, so Excel updates the number (the row reference) accordingly as you drag down.

This works in the same way if you drag the formula across columns. Instead of updating the number, Excel will update the latter part of the reference (the column) to always reference the cell above it.

The column to the right contains B2, to the right of that contains C2, and so on.

This is a simple example of how relative addressing works for both column and cell references.

How Absolute References Work in Excel

Absolute references in Excel let you reference the same cell, rather than letting Excel automatically update the row or column references for you. “Mixed” referencing is if you lock only the row or the column, and “absolute referencing” is when you lock both.

Let’s look at some examples.

Let’s say your spreadsheet has a “10” in a top row, and you want every row beneath it to multiply that number by the number in the cell to the left. 

To do this, you’d enter a formula that looks like this:

=B$2*A3

This locks the “2” reference so it won’t change the row reference if you drag the cell with this formula down to the cells below it. Since A3 remains “unlocked” both the row and column reference will still change automatically and always reference the cell to the left.

You will notice that this only works because you’re dragging down into cells in the same column. So you don’t have to lock column (B) by placing a dollar sign ($) in front of it.

The problem with this is that if you want to use the same formula to the right of the original formula, the “B” reference will change, and the formula will no longer reference B2 as intended.

Let’s take a look at how to use absolute references instead of mixed references to make filling in both directions work properly.

Correctly Using Absolute References in Excel

To use the correct referencing in this formula, you must consider precisely what you’re trying to do.

In this case, we want the following behaviors when filling to the right.

• Always reference the value in cell B2
• Always reference the value in column A
• Shift the reference of the row for column A to the current row of the formula

Looking at these behaviors, you now know what you need to “lock” and what you don’t. Both “B” and “2” need to be locked (unchanged). Also, column A needs to be locked.

So your formula in B3 needs to look like this: =$B$2*$A3

Now when you drag this same cell either down or up, the formula works as intended.

Correctly using absolute referencing can get tricky, so it’s essential to take the time to carefully consider how you want Excel to update the formula as you fill columns or rows in either direction.

Cycling Through Reference Types in Excel

You can speed along the process when you’re typing formulas with absolute references by pressing the F4 key, which will make the cell reference absolute.

The cursor can be on either side of the cell reference (or even in the middle of it) when you press F4, and it’ll still convert that single reference to absolute.

If you don’t want absolute (for example, mixed instead), keep tapping F4 until the reference looks the way you want.

If you want to add any kind of referencing to other cells in the formula, just place your cursor there and start cycling through F4 again.

Once you have configured your formula, just press Enter and begin filling your spreadsheet in any direction you like. If you set up your references right, everything should work just as expected.

Related posts

• Ilaali Tixraaca Unugyada marka aad nuqul ka samaynayso foomamka gudaha Excel

• Sida loo tirtiro khadadka banaan ee Excel

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• Sida loo Geliyo Xaashida Shaqada ee Excel Word Doc

• Sida loo isticmaalo Excel's What-If Analysis

• U isticmaal Magacyada Range Dynamic ee Excel si aad u Dejiso Dabcida

• Google Sheets vs Microsoft Excel - Waa maxay faraqa u dhexeeya?

• 4 siyaabood oo Excel loogu badalo Google Sheets

• Sida Loo Mideeyo Unugyada, Tiirarka & Safafka gudaha Excel

• Sida Loo Raadiyo Excel

• Sida loo raad raaco Ku-tiirsanaanta gudaha Excel

• 5 siyaabood oo loogu badalo qoraalka nambarada gudaha Excel

• 2 siyaabood oo loo Isticmaalo Shaqada Transpose ee Excel

• Sida Loo Dhaqaajiyo Columns gudaha Excel

• Sida Loo Hagaajiyo Safka gudaha Excel

• Sida Loo Geliyo CSV ama TSV Xaashida Shaqada ee Excel

• Sida looga saaro Gridlines gudaha Excel

• Sida loo Isticmaalo If iyo Nsted Haddii Odhaahyada Excel

• Ku xidhida Excel iyo MySQL

• Xarunta Xogta Xaashida Shaqada ee Excel si loo daabaco