Skip to main content

Conas an fhoirmle a nuashonrú agus sraitheanna á gcur isteach in Excel? 

Údar: Xiaoyang Athraithe Deiridh: 2020-12-11
foirmle nuashonraithe doc ag cur sraitheanna 1 isteach

Mar shampla, tá foirmle agam = suim (D2: D7) i gcill D8, anois, nuair a chuirim as a chéile ag an dara ró agus a iontráil uimhir nua, athrófar an fhoirmle go = suim (D3: D8) go huathoibríoch nach n-áirítear an chill D2 mar a thaispeántar ar an scáileán a leanas. Sa chás seo, ní mór dom an tagairt cille san fhoirmle a athrú gach uair a chuireann mé sraitheanna isteach. Conas a d’fhéadfainn suim a thabhairt i gcónaí go dtosaíonn na huimhreacha ó chill D2 agus sraitheanna á gcur isteach in Excel?

Déan an fhoirmle a nuashonrú agus sraitheanna á gcur isteach go huathoibríoch leis an bhfoirmle

mboilgeog cheart gorm saighead Déan an fhoirmle a nuashonrú agus sraitheanna á gcur isteach go huathoibríoch leis an bhfoirmle

Is féidir leis an bhfoirmle shimplí seo a leanas cabhrú leat an fhoirmle a nuashonrú go huathoibríoch gan an tagairt cille a athrú de láimh agus sraitheanna nua á gcur isteach, déan mar seo le do thoil:

1. Iontráil an fhoirmle seo: = SUM (INDIRECT ("D2: D" & ROW () - 1)) (D2 an chéad chill ar an liosta is mian leat a achoimriú) ag deireadh na gcealla ar mhaith leat an liosta uimhreacha a shuimiú, agus brúigh Iontráil eochair.

2. Agus anois, nuair a chuireann tú sraitheanna isteach áit ar bith idir an liosta uimhreacha, déanfar an fhoirmle a nuashonrú go huathoibríoch, féach an scáileán:

foirmle nuashonraithe doc ag cur sraitheanna 2 isteach

Leideanna: Ní oibríonn an fhoirmle i gceart ach nuair a chuireann tú í ag deireadh an liosta sonraí.

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 (9)
No ratings yet. Be the first to rate!
Ali
Ali
This comment was minimized by the moderator on the site
Thank you very much.
Reply
0
0
MandyZhou
MandyZhou     Ali
This comment was minimized by the moderator on the site
Hello,
You are welcome. Glad it helps. Any questions, please feel free to contact us. Have a great day.
Sincerely,
Mandy
Reply
Report
0
0
Idorenyin
Idorenyin
This comment was minimized by the moderator on the site
Please help me with Excel formula that update automatically without changing the cell reference manually when inserting new row at the bottom only. But not when inserting new row at the top. Thanks
Reply
0
0
Katie
Katie
This comment was minimized by the moderator on the site
This is really helpful ... except it does not update the row/column when copying and pasting this formula, so if I have a bunch of them, I have to update the column/row manually (like in the example, if I copy this to the adjacent column E, it still has D ...)

Unless someone knows a workaround to that problem, too?
Reply
0
0
Beatrice
Beatrice     Katie
This comment was minimized by the moderator on the site
you can use a different formula SUM(D$2:D12) as you insert a new row it continues to sum it up
Reply
0
0
Rachael
Rachael     Beatrice
This comment was minimized by the moderator on the site
I found that this only works if there is more than 1 row to total at the start.  Any ideas how you would do this for totalling 1 row to start and then adding in rows as you work on the spreadsheet?

Reply
0
0
Michael Scott
Michael Scott     Beatrice
This comment was minimized by the moderator on the site
You are the best! It works!
Reply
0
0
Rayan
Rayan
This comment was minimized by the moderator on the site
I have a simple cell reference in one of my cells down a column e.g.:"=H6", “=H7” etc. When I insert a row in Excel and copy formulas from row above, the cell reference in 'Opening Stock' does not update automatically in the row after the new blank row (should be equal to previous row balance, which is 60 now). But it updates correctly in all other rows below. Is there a way to force Excel to update cell reference to the cell in blank row above? Have searched the web, but did not get any satisfactory answer. I do not want to convert the data to a table, since it messes up the filters and formatting. Macros don't solve the problem either - they don't copy to the row following the blank one. Can anyone please help?
Reply
0
0
alainachkar@gmail.com
    Rayan
This comment was minimized by the moderator on the site
Hi Rayan!

Please if you see this let me know! You are the only one I found online who brought this issue up. However, my data is table not normal range. The automatic filling of the formula really helps and copy/pasting is a hassle. Anyway, when insert a row, the formulas update for all columns except one! In the row and the cell, instead of the formula shifting from =if(C24 = C25) to =if(C25 = C26), it goes to =if(C24 = C26) although C24 is not fixed!!! I've really tried all possible solutions...................
Reply
Report
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