Skip to main content

Conas sonraí ó bhileoga oibre/leabhair oibre a achoimriú i mbileog oibre amháin?

Údar: Xiaoyang Athraithe Deiridh: 2024-07-23

Imagine you have a workbook with multiple worksheets, all formatted identically. Now, you need to consolidate these worksheets and compute the final results into a master worksheet. How do you effectively summarize data from various worksheets or even different workbooks into one central location? This guide will introduce several efficient techniques to help you achieve this seamlessly.

Summarize data from multiple worksheets/workbooks into one worksheet with the Consolidate function

Déan achoimre ar shonraí ó iliomad bileoga oibre / leabhair oibre i mbileog oibre amháin le Kutools for Excel


mboilgeog cheart gorm saighead Summarize data from multiple worksheets/workbooks into one worksheet with the Consolidate function

seo Chomhdhlúthú feature combines values and does some calculations from multiple worksheets or workbooks into one worksheet. In the following example, I want to summarize the total score of four terms for each student. See screenshots:

The score of the first term:

doc-achoimre-il-bhileoga oibre1

The score of the second term:

doc-achoimre-il-bhileoga oibre2

The score of the third term:

doc-achoimre-il-bhileoga oibre3

The score of the fourth term:

doc-achoimre-il-bhileoga oibre4

Anois is féidir liom an Chomhdhlúthú function to summarize the data from multiple worksheets into a single worksheet, please do as follows:

1. Create a new workbook in which you want to put the consolidated data, then click Dáta > Chomhdhlúthú, féach ar an scáileán:

doc-achoimre-il-bhileoga oibre5

2. Sa an Chomhdhlúthú dialóg, roghnaigh Suim (as we want to show the sum of values for consolidation ) from the Function option, then click the  cnaipe1 cnaipe ó Tagairt to select the range that you want to summarize, and then click the Cuir cnaipe chun an tagairt raon a chur leis an Gach tagairt bosca liosta. Féach an pictiúr:

doc-achoimre-il-bhileoga oibre6

3. Use the same way to add other ranges to the Gach tagairt list box. If your data contains labels for the top row and left column, please check the Sraith barr agus Colún ar chlé faoi an Úsáid lipéid i section. You can also enable the links to source data option, for linking the source data(this option is applied to the new workbook ), see screenshot:

doc-achoimre-il-bhileoga oibre7

4. Click OK to finish this operation. All the data from the worksheets has been added to a new worksheet. At the same time, it creates links for the consolidated data, which updates automatically when the source data changes.

doc-achoimre-il-bhileoga oibre8


mboilgeog cheart gorm saighead Déan achoimre ar shonraí ó iliomad bileoga oibre / leabhair oibre i mbileog oibre amháin le Kutools for Excel

If you are not familiar with the Consolidate function in Excel, here I will recommend an easy and multi-functional tool-Kutools le haghaidh Excel. Leis an Chomhcheangail gné, is féidir leat na sonraí ó iliomad bileoga oibre nó leabhair oibre a achoimriú go tapa i máistir-bhileog oibre amháin.

Nóta: Chun é seo a chur i bhfeidhm Chomhcheangail gné, ar dtús, ba chóir duit a íoslódáil agus a shuiteáil Kutools le haghaidh Excel.

Tar éis a shuiteáil Kutools le haghaidh Excel, Cliceáil le do thoil Kutools Plus > Chomhcheangail.

1. In the Combine Worksheets wizard, select Consolidate and calculate values across multiple workbooks into one worksheet. See screenshot:

doc-achoimre-il-bhileoga oibre10

2. On step 2 of the wizard, all the opened workbooks and worksheets are listed in the list boxes, you can click the Cuir button to add other files or folders that you want to consolidate. Click the cnaipe1 cnaipe chun an raon a theastaíonn uait a achoimriú ceann ar cheann a roghnú. (De réir réamhshocraithe, roghnaítear an raon úsáide do gach bileog oibre) Féach an pictiúr:

doc-achoimre-il-bhileoga oibre11

3. After selecting the ranges, then continue to click the Next button, on step3 of the wizard, and set the following settings:

  • (1.) Select the calculation type from the Function, in this example, I choose Sum.
  • (2.) Seiceáil an ró Barr, Colún Clé má tá na lipéid chomhfhreagracha i do shonraí foinse.
  • (3.) Check the Create links to source data option if you want the consolidation to be updated automatically when the source data are changed. See screenshot:

doc-achoimre-il-bhileoga oibre12

4. And then click the Finish button, and you will be asked if you want to save this scenario. If you want to save this scenario, please click Yes, and enter the name of the scenario, (see the following screenshots), if you don’t want to save this scenario, please click No. When you want to use this scenario, you just only need to click the Cás button to choose the name of the scenario you need on the step2 wizard.

doc-achoimre-il-bhileoga oibre13 -2 doc-achoimre-il-bhileoga oibre14

And then all the data from the worksheets has been summarized into one worksheet of a new workbook. And the consolidated data is added to the links as well. See screenshot:

doc-achoimre-il-bhileoga oibre15

Nótaí:

1. With the above two methods, you can also do other calculations, such as Average, Count, Max, Min, and so on.

2. Is féidir an dá ghné a chur i bhfeidhm freisin ar iliomad leabhar oibre lena gcomhdhlúthú.

Más mian leat tuilleadh a fháil amach faoin ngné seo, cliceáil le do thoil Comhdhlúthaigh agus ríomh luachanna thar iliomad leabhar oibre i mbileog oibre amháin.


Earraí gaolmhara:

Conas iliomad leabhar oibre a chomhcheangal le leabhar oibre amháin in Excel?

Conas bileoga oibre / leabhair oibre a chumasc i mbileog oibre amháin?

Conas bileoga oibre den ainm céanna a chur le chéile i mbileog oibre amháin?

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 (3)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
What are the advantages of using the Kutools method over a standard Consolidate function in Excel?
This comment was minimized by the moderator on the site
i need this excel sheets, multiple consalidated and dashboards, pivot tables, slicers etc
This comment was minimized by the moderator on the site
i need this excel sheets, multiple consalidated and dashboards, pivot tables, slicers etc
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations