Cara Menggabungkan Berbilang Fail Excel: Panduan Lengkap Langkah demi Langkah

Apabila kita bekerja dengan beberapa buku dan helaian, tiba masanya kita ingin menyusun sesuatu dan mengumpulkan segala-galanya di satu tempat. Gabungkan berbilang fail Excel Ia boleh menjimatkan masa anda, mengurangkan ralat dan memudahkan pelaporan korporat, terutamanya jika anda menguruskan perbelanjaan peringkat cawangan, inventori atau jualan serantau yang kemudian anda ingin satukan secara global.

Sekarang, bukan selalu idea yang baik untuk mencampurkan segala-galanya ke dalam satu fail; Excel membolehkan anda memautkan data antara buku kerja dengan formula, yang kadangkala menjimatkan anda daripada membuka fail besar dan mempercepatkan kerja anda. Dalam panduan ini, anda akan melihat apabila anda berminat untuk menyatukan, cara menggabungkan mengikut kedudukan atau kategori, cara menggunakan Power Query untuk menambahkan atau rujukan silang data, contoh kehidupan sebenar dengan fail CSV mengikut nombor siri, makro VBA untuk menggabungkan buku kerja dalam folder dan, jika anda lebih suka, alatan web untuk menggabungkan Excel dengan cepat (dengan kebaikan dan keburukan privasinya).

Bila hendak digabungkan dan bila hendak dipautkan

Excel ialah pisau Swiss Army: ia sama-sama berguna untuk mengurus perakaunan rumah dan untuk memantau metrik perniagaan dengan beribu-ribu baris, formula, carta dan juga data luaran. Lebih banyak maklumat yang anda kumpulkan dalam satu buku, semakin besar dan semakin lama masa yang diperlukan untuk dibuka, sesuatu yang amat ketara pada komputer dengan sumber terhad.

Jika projek anda adalah projek besar dengan beberapa kawasan, mungkin berbaloi untuk meninggalkan helaian berasingan dan tarik formula yang merujuk fail lain. Dengan cara ini anda mengurangkan bilangan tab yang kelihatan dan masih mengekalkan "pusat arahan" yang bersih. Tetapi jika anda memerlukan gambaran keseluruhan umum atau untuk menyediakan penghantaran untuk pengurusan, adalah masuk akal untuk menyatukan segala-galanya ke dalam satu buku kerja (atau menyatukan menjadi satu). helaian induk).

Ketekalan juga penting: jika setiap pasukan menamakan lajur secara berbeza atau meletakkan data dalam kedudukan yang berbeza, anda perlu memutuskan sama ada untuk menyatukan mengikut kedudukan atau mengikut kategori. Ketekalan label dan struktur menandakan perbezaan antara gabungan bersih dan koleksi lajur dengan nilai nol.

Dalam persekitaran profesional, petua praktikal: apabila memindahkan halaman antara buku, ia adalah lebih bijak salin mereka dan bukannya memindahkannya untuk mengekalkan yang asli. Dan jika anda akan menyusun semula banyak tab, ingat untuk menggunakan Shift untuk memilih julat bersebelahan dan Ctrl untuk memilih helaian individu mengikut kehendak anda.

Kaedah Excel Asli untuk menggabungkan helaian dan buku kerja

Sebelum mendalami perkara ini dengan Power Query dan makro, anda perlu mengingati perkara yang ditawarkan oleh program itu sendiri. Excel membolehkan anda memindahkan helaian dari satu buku kerja ke buku kerja yang lain. dengan dua klik dan juga menyatukan data daripada berbilang helaian menjadi satu, sama ada mengikut kedudukan atau kategori, bergantung pada cara ia distrukturkan.

Alihkan atau salin helaian antara buku kerja

Ideal untuk mengumpulkan tab daripada berbilang fail ke dalam satu buku kerja. Berfungsi tanpa bergantung pada pemalam dan ia pantas untuk beberapa fail.

1. Buka buku sumber dan destinasi. Letakkan diri anda pada helaian yang anda ingin bawa.
2. Klik kanan pada tab helaian dan pilih "Alih atau Salin." Anda juga boleh pergi ke Laman Utama > Sel > Format.
3. Pilih buku destinasi (sedia ada atau "buku baharu"). Tentukan kedudukan sisipan berkenaan dengan daun sedia ada.
4. Pilih "Buat salinan" untuk mengelakkan kehilangan helaian asal, dan sahkan dengan OK. Ulang untuk semua helaian yang anda perlukan.
5. Untuk mengalihkan berbilang tab sekaligus, pilih berbilang tab dengan Shift atau Ctrl dan ulangi proses tersebut. Ia adalah jalan pintas yang menjimatkan banyak masa.

Satukan mengikut kedudukan

Gunakan kaedah ini apabila data dalam setiap helaian berkongsi reka letak yang sama (baris dan lajur yang sama dalam susunan yang sama). Adalah penting bahawa tiada baris atau lajur kosong dalam julat.

1. Buka setiap helaian sumber dan sahkan bahawa jadual berada di lokasi yang sama dan dengan bentuk yang sama. Kehomogenan adalah segala-galanya di sini.
2. Dalam helaian destinasi, pilih sel kiri atas tempat anda mahu data yang disatukan dipaparkan. Itu akan menjadi sauh.
3. Pergi ke Data > Consolidate. Kotak dengan senarai fungsi akan dibuka ada.
4. Di bawah "Fungsi", pilih cara untuk menyatukan (Jumlah, Purata, dsb.). Fikirkan sama ada anda memerlukan jumlah, purata, minimum...
5. Dalam setiap helaian sumber, pilih julat data. Laluan dan julat akan ditambah kepada “Semua rujukan”.
6. Setelah anda menambah semua rujukan, klik OK. Anda akan mendapat jadual bersatu mengikut kedudukan.

Satukan mengikut kategori

Sempurna apabila daun berkongsi label baris atau lajur yang sama, tetapi data tidak berada di lokasi yang sama. Di sinilah teks label penting: jika anda meletakkan "Purata." dalam satu helaian dan "Purata" dalam satu lagi, ia tidak akan sepadan.

1. Buka semua helaian sumber. Semak sama ada label sepadan dengan tepat dalam nama dan ejaan.
2. Dalam buku kerja destinasi, pilih sel permulaan untuk penyatuan. Letakkan kursor di tempat yang anda mahukan ringkasan.
3. Pergi ke Data > Consolidate. Pilih fungsi penyatuan (Jumlah, Purata…).
4. Tandai tempat label berada dalam "Gunakan label dalam": baris atas, lajur kiri atau kedua-duanya. Ini memberitahu Excel cara memadankan kategori.
5. Pilih julat setiap helaian termasuk label yang dipilih. Laluan akan muncul dalam "Semua rujukan".
6. Sahkan dengan Terima untuk mencipta helaian induk. Excel akan bergabung mengikut nama kategori, bukan dengan kedudukan.

Nota: Menyatukan dan mencipta jadual pangsi bukanlah perkara yang sama. Jadual pangsi membolehkan anda menyusun semula kategori sesuka hati; penyatuan membetulkan hasil mengikut label dan fungsi yang dipilih, tanpa fleksibiliti analisis dinamik.

Sertai dan Ubah dengan Power Query

Power Query ialah "enjin data" moden Excel. Ia membolehkan anda mengimport, mengubah, menambah dan menggabungkan data daripada berbilang sumber, menyegarkan semula dengan satu klik dan mengautomasikan langkah. Jika anda mengendalikan jumlah yang besar atau mahukan proses berulang, adalah sekutu terbaik anda.

Untuk menggabungkan fail dari folder ke dalam satu jadual, lakukan ini dengan cara berpandu: Muat naik folder, tapis dan gunakan "Gabungkan Fail".

1. Data > Dapatkan Data > Dari Fail > Dari Folder. Pilih folder yang mengandungi daripada fail anda.
2. Anda akan melihat senarai fail dalam folder dan subfolder. Semak bahawa yang anda perlukan muncul.
3. Klik "Transform Data" untuk membuka Power Query dengan paparan semua fail. Ia adalah "bilik enjin".
4. Tapis lajur seperti Sambungan atau Laluan Folder untuk mengekalkan jenis yang betul sahaja (contohnya, .xlsx atau .csv). Ini adalah cara anda mengelakkan mencampurkan epal dengan pear.
5. Pilih lajur Kandungan (perduaan, biasanya yang pertama) dan pergi ke Laman Utama > Gabungkan Fail. Kotak "Gabungkan Fail" akan dibuka..
6. Pilih "Fail Contoh" daripada menu lungsur jika anda tidak mahu menggunakan yang pertama. Power Query menganalisisnya untuk mengesan lajur dan penyambung.
7. Semak "Langkau fail dengan ralat" secara pilihan untuk mengecualikannya daripada hasilnya. Berguna jika terdapat fail dengan struktur yang rosak.
8. Awak terima. Pertanyaan yang menyatukan data akan dijana secara automatik setiap fail dalam satu jadual.

Hasilnya bukan sihir, tetapi hampir: Power Query mencipta pertanyaan dan langkah tambahan berdasarkan arahan yang dipilih, termasuk aplikasi transformasi biasaJika anda menambah lebih banyak fail pada folder esok, cuma Kemas kini untuk menambahkannya pada set.

• Automatización: kemas kini dengan fail baharu tanpa mengulangi proses.
• Kemudahan: Tiada pengaturcaraan diperlukan; semuanya visual.
• Prestasi: Mengendalikan volum besar lebih baik daripada salin/tampal tradisional.

Adakah kes anda menggabungkan dua CSV mengikut lajur biasa, sebagai contoh? "Nombor siri" sebagai kunciDi sini adalah penting untuk membezakan antara "Tambah" dan "Gabung":

• Lampirkan menyusun jadual satu di atas yang lain. Jika pengepala tidak sepadan 100%, anda akan melihat banyak lajur dengan nilai nol. Ini bukan perkara yang anda mahukan untuk maklumat rujukan silang mengikut kekunci.
• Gabungkan menggabungkan jadual dengan kunci, seperti dalam kesatuan pangkalan data (kiri, dalam, kanan…). Ini sesuai untuk "bergabung dengan Nombor Siri".

Langkah yang disyorkan untuk contoh anda dengan dua CSV (satu daripada Pengurus Perniagaan Apple dengan 13 lajur dan 8.743 baris dan satu daripada MDM dengan 24+ lajur dan 6.012 baris): import kedua-dua CSV ke dalam Power Query, tetapkan jenis data yang betul dan gunakan "Gabungkan Pertanyaan".

1. Data > Dapatkan Data > Daripada Fail > Daripada Teks/CSV, import ABM dan MDM. Buka setiap satu dalam editor.
2. Sahkan bahawa "Nombor Siri" adalah jenis yang sama pada kedua-duanya (Teks biasanya paling selamat). Tukar jenis jika perlu.
3. Dengan pertanyaan ABM aktif, Laman Utama > Gabungkan Pertanyaan > Gabungkan Pertanyaan sebagai Baharu. Pilih MDM sebagai jadual untuk disertai.
4. Pilih lajur "Nombor Siri" dalam kedua-dua jadual. Menetapkan jenis kesatuan:
   • Kiri luar (kiri): Menyimpan semua ABM dan membawa padanan daripada MDM.
   • Dalam (persimpangan): hanya baris dengan nombor siri terdapat dalam kedua-duanya.
   • Kanan/Penuh: varian lain, bergantung pada keperluan anda.
5. Kembangkan lajur jadual yang terhasil untuk membawa masuk hanya lajur MDM yang anda minati (tanda/nyahtanda). Anda mengelakkan bunyi bising dan lajur berlebihan.
6. Gunakan transformasi tambahan jika berkenaan (alih keluar lajur, normalkan label). Kemudian "Tutup dan Muatkan".

Jika anda mencuba Append dan mendapat banyak lajur dengan "null", sudah dijangka: anda menyusun struktur yang berbeza. Untuk menyeberang dengan kunci, gunakan Gabung dengan gabungan yang sesuai. Ini berfungsi dalam O365 pada kedua-dua macOS (termasuk Apple Silicon) dan Windows, dan anda juga akan mendapat kelebihan kerana dapat mengemas kini dalam satu klik apabila CSV baharu tiba.

Gabungkan buku kerja dengan makro VBA

Jika anda memilih untuk tidak bergantung pada antara muka dan lebih selesa dengan makro, anda boleh menyalin semua helaian secara automatik daripada semua buku kerja dalam folder ke dalam satu fail. Makro membuka setiap buku kerja dalam mod baca sahaja, menyalin helaiannya dan menutupnya, dalam satu gelung.

1. Buka buku baharu (destinasi). Tekan ALT + F11 untuk membuka Editor VBA.
2. Masukkan Modul (Sisipkan > Modul) dan tampal kod ini melaraskan laluan dan topeng fail jika berkenaan:

Sub UnirHojasDeCarpeta()
  Dim ruta As String, nombre As String
  Dim sh As Worksheet
  ruta = "C:\MisDatos\Origen\"
  nombre = Dir(ruta & "*.xlsx")
  Do While nombre <> ""
    Workbooks.Open Filename:=ruta & nombre, ReadOnly:=True
    For Each sh In ActiveWorkbook.Sheets
      sh.Copy After:=ThisWorkbook.Sheets(1)
    Next sh
    Workbooks(nombre).Close SaveChanges:=False
    nombre = Dir()
  Loop
End Sub

Jalankan dengan F5 dan tunggu sehingga ia selesai. Anda akan mendapat semua helaian dalam buku destinasi anda daripada fail .xlsx dalam folder, dimasukkan selepas tab pertama. Anda boleh menyesuaikan kad bebas kepada "*.xlsm" atau "*.xlsb," dan sudah tentu laluan. Ingat untuk mendayakan makro dan akses amanah kepada model objek VBA jika keselamatan anda tinggi.

Penyelesaian dalam talian untuk menggabungkan Excel: ASPOSE, DOCSOSO dan GROUPDOCS

Jika anda tidak mahu membuka Excel atau anda bekerja dari Mac, Linux, Android o iOS, anda boleh menggunakan perkhidmatan gabungan web. Mereka pantas dan merentas platform, tetapi ingat privasi: apabila anda memuat naik hamparan dengan data sensitif, anda menghadapi risiko mendedahkan maklumat pada pelayan pihak ketiga.

Terdapat aplikasi dalam talian yang menggabungkan berbilang fail Excel dan juga membolehkan anda menyimpannya dalam pelbagai format. Antara fungsi tipikalnya anda akan dapati:

• Gabungkan hamparan: XLSX, XLS, XLSB, XLSM, XLTX, XLT, XLTM, ODS.
• Simpan dalam format yang dikehendaki: PDF, menggabungkan Excel dengan Word, DOCX, POWERPOINT, PPTX, XLS, XLSX, XLSM, XLSB, XLT, XLTX, XLTM, ODS, OTS, FODS, NUMBERS, ET, CSV, TSV, HTML, WEBP, SVG, TIFF, XPS, ZIP, MHTML, SQL, TEXT, TXT.
• Kelebihan lain: cara cepat untuk bergabung, menggabungkan format yang berbeza dalam satu, simpan dengan mudah sebagai PDF, imej atau HTML, gabungkan fail OpenDocument, pilih susunan fail yang dicantumkan dan tentukan sama ada hasilnya masuk berbilang helaian atau dalam satu helaian.

ASPOSE menawarkan suite utiliti dokumen berasaskan web. Dalam alat Excel anda, anda boleh memuat naik fail Dari komputer anda, gunakan Dropbox atau tampal URL. Anda boleh memilih sama ada anda ingin menggabungkan berbilang helaian atau membuang semuanya ke dalam satu helaian; di bawah "Simpan Sebagai," pilih XLSX; tekan Gabung, dan dalam beberapa saat, Anda akan memuat turun hasilnya atau menghantarnya ke Dropbox. Ia juga menyokong bekerja dengan format pejabat lain (Perkataan, PDF, imej, dsb.).

DOCSOSO sangat mudah: anda log masuk, muat naik fail Excel anda dan klik "Gabung Excel." Ia mempunyai kotak untuk mengumpul segala-galanya pada satu helaian. jika ia sesuai dengan anda. Untuk tugasan mudah, ia lebih daripada mencukupi.

GROUPDOCS juga menyepadukan berbilang utiliti dokumen. Anda boleh memilih fail daripada penjelajah atau seret dan lepas pada antara muka; kemudian anda menekan butang cantum dan, apabila selesai, muat turun fail akhir. Sebelum memadamkan yang asal, buka dokumen yang terhasil dan sahkan bahawa gabungan itu adalah seperti yang anda jangkakan; jika tidak, anda boleh sentiasa fungsi Excel asli atau Power Query, yang lebih boleh dikawal.

Kaedah mudah lain dan alat "pakar".

Untuk kes tertentu, prosedur tradisional berfungsi: salin dan tampalBuka setiap buku kerja, Ctrl + A untuk memilih, Ctrl + C untuk menyalin dan tampal ke dalam helaian destinasi. ulang. Ia pantas jika anda mempunyai dua atau tiga fail; dengan berpuluh-puluh, ia menjadi membosankan.

Anda juga boleh bergantung pada pilihan Excel "Pindah atau salin" diterangkan di atas: Pilih helaian yang anda minati dan bawanya ke dalam buku kerja akhir tanpa membuka Editor Pertanyaan atau pengaturcaraan apa-apa. Untuk menyusun semula paparan dan menyimpan yang asal, pilih "Buat salinan."

Dalam bidang perisian khusus, terdapat penyelesaian yang memastikan keupayaan untuk menggabungkan beberapa Excel tanpa membukanya terlebih dahuluSesetengah menunjukkan diri mereka sebagai "satu-satunya alat di pasaran" yang mampu melakukan ini dengan penapis, pilihan lanjutan dan mod percubaan percuma supaya anda boleh melihat cara proses penggabungan berfungsi sebelum anda membuat keputusan.

1. Pasang aplikasi dan pilih pilihan "Gabungkan Excel". Ia adalah pintu masuk ke wizard gabungan.
2. Import fail atau folder individu dengan "Tambah Fail" dan "Tambah Folder." Alat ini akan memaparkan hierarki.
3. Pilih fail yang ingin anda sertakan dan klik "Seterusnya." Beginilah cara anda memperhalusi kumpulan.
4. Pilih antara "Lembaran Kerja Individu" (berbilang tab) atau "Lembaran Kerja Tunggal" dan tentukan laluan output. Anda tentukan struktur akhir.
5. Klik "Gabung" dan tunggu sehingga ia selesai. Apabila selesai, klik OK dan semak hasilnya.

Jika anda mempertimbangkan kaedah manual, ingat hadnya: Mereka tidak sesuai untuk fail gergasi, mereka memerlukan langkah yang berulang, memakan masa, memerlukan kecekapan dengan Excel/VBA dan menghadapi risiko kehilangan hierarki atau data jika anda membuat kesilapan. Untuk beban kerja yang besar, Power Query atau proses automatik (makro atau alat khusus) selalunya lebih dipercayai.

Idea utama ialah anda mempunyai pelbagai pilihan bergantung pada kes anda: disatukan mengikut kedudukan atau kategori jika struktur sejajar; memindahkan/menyalin helaian jika anda menyusun semula buku kerja; Power Query untuk menambah atau menggabungkan dengan kunci dengan kemas kini automatik; Makro VBA apabila anda ingin menelan berpuluh-puluh fail secara pukal; dan perkhidmatan web atau perisian khusus jika anda mengutamakan kelajuan dan tidak mengendalikan data sensitif. Memilih dengan baik akan menjimatkan masa dan sakit kepala anda, dan akan membolehkan anda mengekalkan laporan di bawah kawalan tanpa mengorbankan prestasi atau kualiti data.

Artikel berkaitan:

Cara Mengintegrasikan Excel ke dalam PowerPoint: Panduan Lengkap dan Semua Kaedah

Ishak

Penulis yang bersemangat tentang dunia bait dan teknologi secara umum. Saya suka berkongsi pengetahuan saya melalui penulisan, dan itulah yang akan saya lakukan dalam blog ini, menunjukkan kepada anda semua perkara yang paling menarik tentang alat, perisian, perkakasan, trend teknologi dan banyak lagi. Matlamat saya adalah untuk membantu anda mengemudi dunia digital dengan cara yang mudah dan menghiburkan.