Kā sapludināt cilnes programmā Excel

Excel ir neticami noderīgs rīks lielu datu kopu glabāšanai, pārvaldībai un attēlošanai. Neatkarīgi no tā, vai apstrādājat atkārtojamus zinātnisko eksperimentu rezultātus, uzņēmuma darbinieku informāciju, produktu cenu aptaujas vai citus datus, tos visus var parādīt kā izklājlapas programmā Excel.

Excel failā vai darbgrāmatā var būt vairākas cilnes. Lai gan lielākajai daļai Excel lapu ir dažādi lietojumi, dažās cilnēs var būt ietverta dublēta vai saistīta informācija. Saistīto cilņu sapludināšana vai apvienošana vienā Excel cilnē palīdzēs lasīt, analizēt un sakārtot datus turpmākai apstrādei.

Šajā rakstā tiks parādīts, kā programmā Excel apvienot divas (vai vairākas) cilnes, kā arī dažas papildu funkcijas un metodes, kuras varat izmantot.

Cilņu sapludināšana programmā Excel — tas ir vienkārši

Pirms apvienošanas pārliecinieties, vai visām cilnēm ir rezerves kopijas. Jūsu avota cilnēs būs jūsu izmantotie neapstrādātie dati, savukārt galamērķa cilnē būs gala rezultāts. Atkarībā no jūsu projekta prasībām tās var būt vai nebūt viena un tā pati cilne.

Programmas Excel noklusējuma konsolidācijas funkcija var apvienot datus pēc pozīcijas vai kategorijas (rindas vai kolonnas nosaukuma). Tomēr datiem ir jābūt tādā pašā formātā un izmērā, pretējā gadījumā tiks izveidotas jaunas rindas vai kolonnas. Piemēram, ja izmantojat pārdošanas rādītājus dažādiem birojiem, jums ir jābūt tādam pašam kategoriju skaitam, pēc kā kārtojat, un vienādam nedēļu/mēnešu skaitam, ko veicat kataloģizēšanai.

Ņemiet vērā, ka konsolidācijas funkcijas darbojas ar skaitliskiem datiem. Programma Excel var aprēķināt summas, vidējos rādītājus, novirzes un minimālās un maksimālās vērtības, kā arī citus statistikas punktus. Tomēr tas neļauj veikt niansētākas teksta datu transformācijas.

Apvienošanas darbības pēc pozīcijas vai kategorijas ir parādītas zemāk:

1. Galamērķa cilnē nosakiet sapludināto datu pozīcijas un noklikšķiniet uz atlasīto pozīciju augšējās kreisās šūnas.
   
   
2. Noklikšķiniet uz cilnes "Dati".
   
   
3. Dodieties uz "Datu rīki" un atlasiet "Konsolidēt". Tas atver uznirstošo logu.
   
   
4. Lodziņā “Funkcija” nolaižamajā sarakstā atlasiet funkciju.
   
   
5. Atlasiet apvienojamos datus:
   • Ja pēc pozīcijas, dodieties uz “Avota cilnes” un noklikšķiniet uz pogas “Pievienot”, lai pievienotu datus lodziņā “Visas atsauces” . Pievienojamos datus var ievadīt manuāli, piemēram, “ Sheet1!$B$2:$B$10” attiecas uz šūnām no B2 līdz B10 cilnē ar nosaukumu Sheet1 pašreizējā dokumentā.
     
   • Ja pēc kategorijas, lodziņā “Lietotāju iezīmes” atlasiet “Augšējā rinda” (pēc rindām) vai “Kreisā kolonna” (pēc kolonnām) vai “Izveidot saites uz avota datiem” (rakstiet saitēs).
     
     
6. Noklikšķiniet uz pogas “OK” , un dati lodziņā “Visas atsauces ” vai atlasītās rindas/kolonnas tiks sapludināti.
   
   

Jāpiemin, ka vienmēr varat izmantot kopēšanu un ielīmēšanu, lai pārsūtītu datus no vienas cilnes uz otru. Tomēr tas var būt laikietilpīgs un var izraisīt kļūdas. Ir daudz elegantāki veidi, kā panākt konsolidāciju, neatkārtojot informāciju.

Cilņu sapludināšana programmā Excel VBA

VBA apzīmē Visual Basic for Applications — vienkāršu, bet jaudīgu programmēšanas valodu, ko varat izmantot, lai paplašinātu Microsoft Office lietojumprogrammas, tostarp Excel. Galvenā problēma, izmantojot VBA, ir tā, ka jums būs jāsaprot un jāizmanto kods, lai izveidotu lietojumprogrammas vai makro.

Lai izveidotu un rediģētu VBA makro, rīkojieties šādi:

1. Rīkjoslā atlasiet “Skatīt” .
   
   
2. Noklikšķiniet uz "Makro" labajā malā. Tiek atvērts uznirstošais makro logs.
   
   
3. Ievadiet makro nosaukumu (piemēram, “test”) un noklikšķiniet uz pogas “Izveidot” labajā pusē, un tiks parādīta kodēšanas konsole ar pamata saturu, kas lasa kaut ko līdzīgu:
   
   ###
Sub test()
End Sub
###
   
   
4. Makro kodu var rediģēt konsolē. Šis ir piemērs, kurā tiek apvienotas tabulas no lapām: Makro koda paraugs pāriet uz kopējo ciļņu skaitu un izveido jaunu lapu “Apvienotā lapa”.
   
   ###
Sub Merge_Multiple_Sheets_Row_Wise()
Dim Work_Sheets() As String
ReDim Work_Sheets(Sheets.Count)
For i = 0 To Sheets.Count - 1
 Work_Sheets(i) = Sheets(i + 1).Name
Next i
Sheets.Add.Name = "Combined Sheet"
Dim Row_Index As Integer
Row_Index = Worksheets(1).UsedRange.Cells(1, 1).Row
Dim Column_Index As Integer
Column_Index = 0
For i = 0 To Sheets.Count - 2
 Set Rng = Worksheets(Work_Sheets(i)).UsedRange
Rng.Copy
 Worksheets("Combined Sheet").Cells(Row_Index, Column_Index + 1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
 Column_Index = Column_Index + Rng.Columns.Count + 1
Next i
Application.CutCopyMode = False
End Sub
###
   
   
   
   
5. Lai palaistu kodu, makrokonsoles cilnē “Palaist” noklikšķiniet uz “Run Sub/UserForm”, pēc tam tiks ģenerēta jauna cilne ar nosaukumu “Kombinētā lapa” . Varat arī modificēt Marco kodu, lai rediģētu datu diapazonu un nosaukumus.
   
   

Izklājlapu sapludināšana programmā Excel Online

Vairāki bezmaksas tiešsaistes rīki ļauj apvienot Excel lapas. Šajos rīkos jums vienkārši jāatlasa un jāaugšupielādē darbgrāmatas (vai nu Muti-Tab darbgrāmata, vai dažādas darbgrāmatas). Piemēri ir Aspose Cell Merger un DocSoSo Excel Combiner .

Ņemiet vērā, ka, sapludinot lapas, netiek veiktas manipulācijas ar datiem. Šie rīki aizņem divas vai vairākas Excel darbgrāmatas un atgriež Excel darbgrāmatu ar vienu vai vairākām lapām ar tajās iekopētajiem datiem.

Cilņu apvienošana programmā Excel, izmantojot Power Query

Power Query ir vēl viens veids, kā apvienot cilnes programmā Excel. Excel darbgrāmatai ar vairākām cilnēm veiciet tālāk norādītās darbības.

1. Dodieties uz cilni "Dati" un grupu "Iegūt un pārveidot datus" un noklikšķiniet uz pogas "Iegūt datus" .
   
   
2. Sarakstā noklikšķiniet uz “Tukšs vaicājums” opcijā “No citiem avotiem” , un jūs redzēsit jaunu Power Query redaktoru ar noklusējuma nosaukumu “Vaicājums 1”.
   
   
3. Funkciju joslā ( fx ) ierakstiet formulu “ =Excel.CurrentWorkbook() ” (ņemiet vērā, ka formula ir reģistrjutīga) un nospiediet taustiņu Enter , un tiks parādīts sākotnējās darbgrāmatas cilņu nosaukumu saraksts.
   
   
4. Atlasiet sapludināmo ciļņu nosaukumus (kolonnas) (vai atzīmējiet opciju “Atlasīt visas kolonnas”, ja visas kolonnas ir jāapvieno).
   
   
5. Noklikšķiniet uz dubultbultiņas pogas, kas atrodas blakus taustiņam “Saturs” .
   
   
6. Noņemiet atzīmi no opcijas “Izmantot sākotnējo kolonnas nosaukumu kā prefiksu”, ja vēlaties, lai sapludinātajā failā tiktu izmantoti precīzi sākotnējās darbgrāmatas nosaukumi, nevis kā prefiksi.
   
   
7. Noklikšķiniet uz “OK” , un jūs redzēsiet jaunu cilni ar visiem apvienotajiem datiem, kurā sākotnējās darbgrāmatas ciļņu nosaukumi tiks uzskaitīti galējā labajā kolonnā.
   
   

Alternatīvās metodes

Excel ir noderīgs rīks 2D masīva datu apstrādei. Tomēr, ja esat datu zinātnieks, varat piekļūt datiem tieši, izmantojot programmēšanas valodas, piemēram, Python vai R. Galvenais ir tas, ka 2D masīvu datu struktūra ir izplatīta: izmantojot atdalītājus (atstarpe, tabulēšana, komats utt.) lai sadalītu visus datu punktus. Piemēram, .csv formātā tiek izmantoti komatu atdalītāji, un to var tieši atvērt lasīšanai/rediģēšanai programmā Microsoft Excel. Varat arī atvērt Excel failus (.xlsx), izmantojot šīs programmēšanas valodas, piemēram, R pakotni “readxl”, kas var atvērt un apstrādāt datus no dažādām Excel darblapām. Šādos gadījumos apvienošanai var izmantot vienkāršas, bet ērtas funkcijas, piemēram, cbind (saistīšanas kolonnas ar vienādu izmēru). Visus datus no dažādām Excel cilnēm var apvienot datu rāmjos statistikas analīzei un vizualizācijām, neizmantojot Microsoft Excel programmatūru.

Iegūstiet vairāk no mazāk

No iepriekš apkopotajiem procesiem jūs iegūsit vienu integrētu darblapu ar datiem no vairākiem resursiem. Šī darblapa var kalpot kā arhīvs vai rezerves datu bāze. Turklāt sapludinātā cilne ļauj iegūt perspektīvas, kuras var nebūt datos no viena resursa. Mēs iesakām pārbaudīt konsolidācijas funkcijas un makro, izmantojot piemēru datus, un iepriekš dublēt tos.

Vai jums ir labāks veids, kā konsolidēt cilnes programmā Excel? Paziņojiet mums tālāk sniegtajos komentāros.