Skip to main content

Conas tagairt cille foirmle a choinneáil seasmhach in Excel?

Sa rang teagaisc seo, taispeánfaimid conas tagairt cille i bhfoirmle a athrú go tagairt tairiseach (iomlán) chun é a chosc ó athrú nuair a chóipeáiltear an fhoirmle in áit eile.

Taispeánann an screenshot thíos dhá chineál dhifriúla tagairtí cille sna foirmlí chun costas iomlán na n-ítimí a ríomh:
  • Aistríonn an fhoirmle ar chlé tagairt an ráta lascaine síos gach ró go mícheart. Nuair a chóipeáil ó D3 chun D4 agus D5, tagraíonn sé F4 agus F5 in ionad an ráta lascaine seasta i F3, as a dtagann ríomhaireachtaí míchearta i D4 agus D5.
  • Ar thaobh na láimhe deise den scáileán, $ F $ 3 is tagairt sheasmhach (iomlán). Coinníonn sé an ráta lascaine nasctha le cill F3, is cuma cén áit a ndéantar an fhoirmle a chóipeáil laistigh den leabhar oibre. Mar sin, má dhéantar an fhoirmle a chóipeáil síos sa cholún coinnítear an ráta lascaine comhsheasmhach do gach earra.

Coinnigh tagairt cille foirmle seasmhach leis an eochair F4

Chun tagairt cille tairiseach a choinneáil i bhfoirmle, níl le déanamh ach an tsiombail $ a chur leis roimh an litir cholúin agus uimhir an ró trí bhrú an eochair F4. Seo conas é a dhéanamh.

  1. Cliceáil ar an gcill ina bhfuil an fhoirmle.
  2. Sa bharra foirmle, cuir an cúrsóir laistigh den tagairt cille is mian leat a dhéanamh tairiseach. Anseo roghnaíonn mé an tagairt F3.
  3. Brúigh an F4 eochair chun scoránaigh tríd an gcineál tagartha go dtí go sroicheann tú an tagairt iomlán, a chuireann a comhartha dollar ($) roimh an litir cholúin agus uimhir an ró. Anseo F3 athrófar go $ F $ 3. Ansin brúigh Iontráil chun an t-athrú a dhearbhú.

Anois, coinníonn sé an ráta lascaine nasctha le cill F3, is cuma cén áit a chóipeáiltear an fhoirmle laistigh den leabhar oibre.

nótaí: gach preas den F4 príomhthimthriallta trí stáit tagartha éagsúla: absalóideach ($ A $ 1), dearbhcholún measctha agus líne choibhneasta ($ A1), colún coibhneasta measctha agus lánréim (A $ 1), agus ar ais go gaol (A1).

Cúpla cad a tharlaíonn chun gach tagairt cille a dhéanamh absalóideach i raonta

Cuidíonn an modh thuas le cealla a láimhseáil ceann ar cheann. Más mian leat tagairt iomlán a dhéanamh ar ilchealla ag an am céanna, molaimid go mór an Tiontaigh Tagairtí gné de Kutools le haghaidh Excel. Leis an ngné seo, is féidir leat gach tagairt cille a dhéanamh go héasca i raon cille nó raonta iolracha cille iomlán i gceann cúpla cad a tharlaíonn.

Tar éis suiteáil Kutools le haghaidh Excel, téigh go dtí an Kutools tab, roghnaigh níos mó > Tiontaigh Tagairtí a oscailt Tiontaigh Tagairtí Foirmle bosca dialóige. Ansin ní mór duit:

  1. Roghnaigh raon nó raonta iolracha ina bhfuil foirmlí ar mhaith leat gach tagairt cille a dhéanamh tairiseach.
  2. Roghnaigh an Go hiomlán rogha agus ansin cliceáil OK chun tosú ag tiontú.

Ansin athraítear gach tagairt cille le haghaidh foirmlí sa raon roghnaithe láithreach chuig tagairtí tairiseacha.

nótaí: Chun an ghné seo a úsáid, ba chóir duit a shuiteáil Kutools le haghaidh Excel ar dtús, le do thoil cliceáil le híoslódáil agus tá triail saor in aisce 30-lá agat anois.

Taispeántas: Coinnigh tagairt cille foirmle seasmhach le Kutools le haghaidh Excel

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup  |   VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle   |  Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla   |  Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí:  12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc

Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (12)
Rated 5 out of 5 · 1 ratings
SM
SM
This comment was minimized by the moderator on the site
Thank you for this post, it was very helpful
Reply
0
0
Rated 5 out of 5
Muresan ALIN
Muresan ALIN
This comment was minimized by the moderator on the site
Not working if you have index match or vlooup in the formula
Reply
0
0
Austin314
Austin314
This comment was minimized by the moderator on the site
That Kutools "Convert Formula References" is perfect, exactly what I need. Unfortunately I can't install that, but it's nice to know someone else needed it and made an extension for it. At least it gives me some closure knowing I'll never find it in base Excel haha. Thanks.
Reply
Report
0
0
crystal
crystal     Austin314
This comment was minimized by the moderator on the site
Hi Austin,Did you get any error prompt when installing Kutools for Excel? 
Reply
Report
0
0
Austin314
Austin314     crystal
This comment was minimized by the moderator on the site
Hi Crystal! Oh, thanks for asking. I was on my Work PC so I knew I would be unable to install anything like this without going through the process of getting it approved with IT, presenting a solid reason to the team as to why we need to pay for additional software, etc. It would be a major headache, and I'm certain I would lose the argument. (Make no mistake, I'm all for it) But, unfortunately I settled for the smaller headache of doing what Kutools' "Convert Formula References" process would do, to a few hundred cells by hand. When I've made it to the big leagues, and I'm the boss, I'll ensure that my whole team is outfitted with Kutools. It's an awesome addition, for sure. 
Reply
Report
0
0
crystal
crystal     Austin314
This comment was minimized by the moderator on the site
Hi Austin314,Thank you very much for your feedback and I look forward to the day when your whole team is outfitted with Kutools.^_^
Reply
Report
0
0
Bharat
Bharat
This comment was minimized by the moderator on the site
Superb.....
Reply
0
0
Andrew
Andrew
This comment was minimized by the moderator on the site
Does not work. If I insert a row above the cell, the formula "tracks" the cell to its new location. This is not what your title implies. I need a formula that reads = B1 and if data gets inserted into row B:B then the formula reads = B1 and returns the new value. Absolute referencing does not do this.
Reply
0
0
Martin
Martin     Andrew
This comment was minimized by the moderator on the site
Andrew, that is exactly what I want too..... how can we hold the cell reference Completely constant in these circumstances?
Reply
0
0
Austin314
Austin314     Martin
This comment was minimized by the moderator on the site
Andrew, Martin! .....I came here for something else but I think I've got what you need.  You can also reference cells using the INDIRECT() function. For your case, if you go to cell C1 (or wherever) and set the equation in C1 to be =INDIRECT("B1"), then it will always return whatever is in B1, no matter what happens to B1. So then, if you insert a column of data into column B, your equation would move over to cell D1, and would continue to pull data from the brand new value in B1. The older referenced value is now in C1, with the column of data before. You can also perform mathematic operations on it as you would any other cell reference. Happy Spreadsheets :)
Reply
Report
0
0
Angelo Antonino
Angelo Antonino     Martin
This comment was minimized by the moderator on the site
Use the INDEX() function and for the Array use a range of columns to cover all your data (assuming no column inserts/deletes will happen)
Reply
0
0
Gavin Parker Rose
Gavin Parker Rose
This comment was minimized by the moderator on the site
Financial ratios are one of the most common tools of managerial decision making. A ratio is a comparison of one number to another—mathematically, a simple division problem. Financial ratios involve the comparison of various figures from the financial statements in order to gain information about a company's performance. It is the interpretation, rather than the calculation, that makes financial ratios a useful tool for business managers. Ratios may serve as indicators, clues, or red flags regarding noteworthy relationships between variables used to measure the firm's performance in terms of profitability, asset utilization, liquidity, leverage, or market valuation.
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations