Apa yang akan Anda lakukan jika Anda perlu membuat tabel konsolidasi, dan data Anda pada lembar yang berbeda? Dengan Excel 2013 yang diinstal, Anda memiliki cara sederhana untuk ini. Ada teknologi yang disebut model data dan menggunakan hubungan data karena membuat database.

Dalam pelajaran ini, saya akan menunjukkan segalanya untuk membuat tabel pivot di Excel 2013 dari data pada beberapa lembar menggunakan model data.

Video

Jika Anda ingin mengikuti pelajaran menggunakan file Excel Anda sendiri, Anda dapat melakukannya. Atau unduh file zip ke pelajaran ini, yang berisi buku sampel. Pivot Consolidate.xlsx.

Pembelajaran Data

Buku ini memiliki tiga lembar kerja: informasi tentang klien, informasi pemesanan dan informasi pembayaran.

Klik pada Sheet. Info Pelanggan Dan pastikan itu berisi nomor pesanan, serta nama dan negara pelanggan.

Daftar Info Pelanggan

Klik pada Sheet. Info pesanan. Dan lihat itu berisi pesanan, serta bidang untuk bulan itu, memesan barang dan apakah produk ini organik.

Info pesanan Leaf.

Klik tab Info Pembayaran dan pastikan itu berisi pesanan, jumlah dalam dolar dari setiap penjualan, metode pembayaran dan urutan penempatan pesanan dengan klien baru atau yang sudah ada.

Info Pembayaran Leaf.

Dengan menghubungkan semua lembar ini di area tugas tugas, kita dapat memilih data dari setiap lembar. Karena nomor pesanan ada di ketiga lembar, mereka akan menjadi titik koneksi. Inilah yang disebabkan basis data kUNCI UTAMA. Harap dicatat: Tidak perlu memiliki kunci utama, tetapi mengurangi kemungkinan kesalahan.

Buat tabel bernama.

Sebelum membuat tabel pivot, mari buat tabel dari setiap lembar.

Klik kembali B. Tabel Pelanggan, lalu klik di mana saja di area data. Klik tab Memasukkan. Pada panel pita dan klik ikon Meja.

Konversi data pada lembar dengan memilih Masukkan\u003e Tabel

Kotak dialog Buat Tabel dengan benar menentukan area tabel. Kotak centang di bawah ini juga harus mengidentifikasi bahwa string pertama tabel dimaksudkan untuk header. (Jika tidak, pilih opsi ini.)

Buat dialog tabel harus menebak area data dengan benar.

Klik BAIK.Dan sekarang Anda memiliki tabel dengan stroke bolak-balik dan tombol filter. Anda dapat mengklik di dalam untuk menghapus pilihan jika Anda ingin mempertimbangkannya dengan lebih baik (hanya jangan tekan di luar tabel). Panel tape juga menampilkan tab Rancangan. Untuk sebuah meja. Di sisi kiri pita di lapangan Nama meja. Nama Sementara Table1 ditampilkan. Hapus dan beri nama. Customer_info. (Gunakan simbol penutup bukan ruang). Klik MEMASUKKAN.

Oleskan nama ke setiap tabel

Ulangi langkah-langkah ini dengan info pesanan dan info pembayaran lembar. Nama meja Order_info. dan Informasi pembayaran..

Sekarang kami siap untuk memasukkan PivotTable.

Kotak PivotTable.

Pastikan bahwa pada lembar info pembayaran, kursor ada di suatu tempat di tabel. Kembali ke tab Memasukkan. Pita dan klik ikon Tabel pivot (Ini adalah ikon pertama).

Menggunakan kursor di dalam salah satu tabel, pilih Masukkan\u003e PivotTable.

Di kotak dialog yang muncul, Anda harus mendefinisikan tabel dengan benar dan memilih yang dipindahkan PivotTable ke lembar kerja baru. Di bawah, pilih kotak centang Tambahkan data ini ke model data. Klik BAIK..

Menambahkan data ke model data adalah apa yang memungkinkan koneksi untuk bekerja

Sekarang Anda akan memiliki PivotTable pada lembar baru, bilah tugas akan berada di sisi kanan layar, dan tab Analisis akan muncul pada pita.

Taskbar menampilkan tabel dan bidang lembar aktif saja, jadi tekan Semua.Untuk melihat semua tabel yang Anda buat. Tetapi sebelum kita dapat menggunakannya, kita harus mengikat mereka satu sama lain, dan ini berarti menciptakan hubungan. tekan tombolnya Hubungan. Pada panel tape.

Mengatur hubungan meja

Menekan tombol ini menampilkan kotak dialog Kelola Hubungan. tekan tombolnya Baru Dan kotak dialog Buat Hubungan muncul. Kami akan membuat dua hubungan menggunakan bidang pemesanan # sebagai konektor.

Di daftar drop-down, pilih Informasi pembayaran. untuk meja, dan di sebelahnya memilih Pesanan # Dalam daftar drop-down kolom. Di baris kedua, pilih Customer_info. dari tabel terkait daftar drop-down, dan di sebelahnya pilih Pesanan # Dari kolom terkait daftar drop-down.

Ada tiga tabel, jadi buat dua hubungan.

Ini berarti bahwa tabel pembayaran_info dan customer_info terkait dengan kebetulan nomor pesanan.

tekan tombolnya BAIK. Dan kita akan melihat hubungan ini tercantum di jendela Kelola Hubungan.

Ulangi proses ini untuk membuat tautan antara pembayaran_info dan order_info, juga menggunakan bidang Pesanan #. Sekarang jendela Kelola Hubungan terlihat seperti ini:

Di kotak dialog Kelola Hubungan, hubungan yang Anda buat ditampilkan.

Harap dicatat bahwa tidak perlu membuat hubungan antara order_info dan tabel customer_info, karena secara otomatis terhubung melalui tabel pembayaran_info.

tekan tombolnya Menutup Di bagian bawah jendela. Sekarang kita akhirnya bisa menyeret bidang di PivotTable.

Memasukkan bidang di PivotTable

Di bagian semua pada bilah tugas, klik panah kecil ke, gunakan tiga tabel, lihat bidangnya. Seret bidang di area PivotTable sebagai berikut:

• Negara dan BULAN. dalam string.
• Produk Di kolom
• $ Penjualan. Nilainya
• Status. Dalam filter

Seret bidang masing-masing dari tiga tabel di PivotTable

Sekarang Anda dapat menggunakan dan mengubahnya, seperti PivotTable lainnya.

Kesimpulan

Menggunakan fungsi model data objek baru di Excel 2013, Anda dapat memilih bidang merah muda dari beberapa lembar untuk membuat satu pivottable. Ingatlah bahwa garis masing-masing meja harus terhubung satu sama lain. Anda memiliki lebih banyak peluang untuk sukses, ketika tabel memiliki bidang yang sama dengan nilai-nilai unik.

Jika Anda mencari cara yang baik untuk mengirimkan data Anda, Envato Market memiliki pilihan yang baik, serta untuk mengkonversi data Excel ke format web dan sebaliknya.

Dari artikel ini, Anda akan belajar cara cepat menggabungkan data dari dua tabel Excel ketika tidak ada kebetulan yang tepat dalam kolom utama. Misalnya, ketika pengidentifikasi unik dari tabel pertama adalah karakter pengidentifikasi lima pertama dari tabel kedua. Semua solusi yang ditawarkan dalam artikel ini diuji oleh saya di Excel 2013, 2010 dan 2007.

Jadi, ada dua lembar Excel yang perlu digabungkan untuk analisis data lebih lanjut. Misalkan, dalam tabel yang sama berisi harga (kolom, harga) dan deskripsi produk (kolom bir) yang Anda jual, dan pada detik mencerminkan data tentang ketersediaan barang dalam stok (kolom dalam stok). Jika Anda atau kolega Anda menyumbang kedua tabel sesuai dengan direktori, maka setidaknya satu kolom kunci dengan pengidentifikasi unik harus ada di kedua jalan. Deskripsi atau harga produk dapat bervariasi, tetapi pengidentifikasi unik selalu tetap tidak berubah.

Kesulitan dimulai ketika Anda mendapatkan beberapa tabel dari pabrikan atau dari departemen lain perusahaan. Kasus ini bahkan dapat menjadi lebih rumit jika format baru pengidentifikasi unik diperkenalkan atau nomenclatures gudang (SKU) akan berubah. Dan sebelum Anda ada tugas untuk bergabung di Excel meja baru dan lama dengan data. Salah satu atau lain cara, ada situasi di mana hanya pertandingan parsial catatan yang terjadi pada kolom kunci, misalnya, " 12345 "Dan" 12345-new_suffix.". Anda jelas bahwa ini adalah SKU yang sama, tetapi komputer tidak begitu terjaga! Kebetulan ini tidak tepat membuatnya tidak mungkin untuk menggunakan formula Excel yang biasa untuk menggabungkan data dari dua tabel.

Dan itu sangat buruk - kepatuhan mungkin sama sekali bukan, dan " Beberapa perusahaan"Dalam satu tabel dapat berubah menjadi" CJSC "beberapa perusahaan""Di meja lain, dan" Perusahaan baru (mantan beberapa perusahaan)"Dan" Perusahaan tua."Juga akan menjadi catatan dari perusahaan yang sama. Ini diketahui oleh Anda, tetapi bagaimana menjelaskan Excel?

Komentar: Solusi yang dijelaskan dalam artikel ini bersifat universal. Anda dapat menyesuaikannya untuk digunakan lebih lanjut dengan formula standar, seperti PR (VLOOKUP), Papan Pencarian (Pertandingan), GPR. (Hloookup) dan sebagainya.

Pilih contoh yang sesuai untuk segera pergi ke solusi yang diinginkan:

Kolom utama di salah satu tabel berisi karakter tambahan.

Pertimbangkan dua tabel. Kolom tabel pertama berisi nomor nomenklatur (SKU), nama bir (bir) dan harganya (harga). Tabel kedua merekam SKU dan jumlah botol di gudang (dalam stok). Alih-alih bir ada produk apa pun, dan jumlah kolom dalam kehidupan nyata bisa lebih banyak.

Dalam sebuah tabel dengan simbol tambahan, buat kolom bantu. Anda dapat menambahkannya ke ujung meja, tetapi yang terbaik adalah memasukkannya di sebelah kanan setelah kolom kunci terlihat.

Kunci dalam tabel dalam contoh kami adalah kolom SEBUAH. Dengan data SKU, dan Anda perlu menghapus 5 karakter pertama darinya. Tambahkan kolom bantu dan sebut saja SKU Helper:

Siap! Sekarang kami memiliki kolom utama dengan kebetulan nilai - kolom SKU Helper Di tabel dan kolom utama SKU. Di tabel di mana pencarian akan dilakukan.

Sekarang dengan fungsi PR (VLOOKUP) Kami akan mendapatkan hasil yang diinginkan:

Formula lain.

• Ekstrak dulu H. Simbol di sebelah kanan: Misalnya, 6 karakter di sebelah kanan entri "DSFH-164900". Formula akan terlihat seperti ini:

  Practerv (A2; 6)
  \u003d Benar (A2,6)

• Lewati dulu H. Karakter mengekstrak yang berikut ini Y. Simbol: Misalnya, Anda perlu menghapus "0123" dari rekaman "Prefix_0123_suff". Di sini kita perlu melewati 8 karakter pertama dan mengekstrak 4 karakter berikut. Formula akan terlihat seperti ini:

  PSTR (A2; 8; 4)
  \u003d Pertengahan (A2.8,4)

• Ekstrak semua karakter sebelum pemisah, panjang urutan yang dihasilkan dapat berbeda. Misalnya, Anda perlu mengekstrak "123456" dan "0123" dari catatan "123456-suffix" dan "0123-suffix", masing-masing. Formula akan terlihat seperti ini:

  Levsimv (A2; temukan ("-"; a2) -1)
  \u003d Kiri (A2, temukan ("-", a2) -1)

Singkatnya, Anda dapat menggunakan fitur Excel seperti Levsimv. (Kiri), Pratseim. (Baik) Pst. (PERTENGAHAN), MENCARI (Temukan) untuk mengekstrak bagian mana pun dari indeks komposit. Jika kesulitan muncul -, kami akan melakukan yang terbaik untuk membantu Anda.

Data dari kolom tombol di tabel pertama dibagi menjadi dua atau lebih kolom di tabel kedua.

Misalkan tabel di mana pencarian dilakukan berisi kolom dengan pengidentifikasi. Dalam sel kolom ini mengandung Xxxx-yyyy.dimana Xxxx. - Ini adalah penunjukan kode dari sekelompok barang (ponsel, televisi, camcorder, kamera), dan YYYY. - Ini adalah kode barang di dalam grup. Tabel utama terdiri dari dua kolom: dalam satu berisi kode komoditas (grup), detik-detik dicatat dalam detik (ID). Kami tidak dapat membuang kode grup produk, karena kode produk yang sama dapat diulang pada grup yang berbeda.

Di sel. C2. Kami menulis formula seperti itu:

Tangkap (A2; "-"; b2)
\u003d Compatenate (A2, "-", b2)

Sini A2. - Ini adalah alamat sel yang berisi kode grup; simbol " – "Adalah separator; B2. - Ini adalah alamat sel yang berisi kode produk. Salin formula ke dalam garis yang tersisa.

Sekarang gabungkan data dari dua tabel kami tidak akan sulit. Kami akan membandingkan kolom Id lengkap. Tabel Pertama dengan Kolom Indo Tabel kedua. Ketika kebetulan terdeteksi, perekaman dari kolom Deskripsi. dan Harga Tabel kedua akan ditambahkan ke tabel pertama.

Data dalam kolom kunci tidak bertepatan

Berikut adalah contoh: Anda adalah pemilik toko kecil, dapatkan barang dari satu atau beberapa pemasok. Masing-masing dari mereka memiliki nomenklatur mereka sendiri yang berbeda dari milik Anda. Akibatnya, ada situasi di mana catatan "case-ip4s-01" Anda cocok dengan entri "SPK-A1403" dalam file Excel yang diterima dari pemasok. Perbedaan seperti itu terjadi secara acak dan tidak ada aturan umum untuk secara otomatis mengonversi "SPK-A1403" dalam "Case-IP4S-01".

Kabar buruk: Data yang terkandung dalam dua tabel Excel ini harus ditangani secara manual sehingga di masa depan dimungkinkan untuk menggabungkannya.

Kabar baik: Itu harus dilakukan hanya sekali, dan tabel bantu yang dihasilkan dapat disimpan untuk digunakan lebih lanjut. Selanjutnya Anda dapat menggabungkan tabel ini secara otomatis dan menghemat waktu

1. Buat tabel anak perusahaan untuk pencarian.

Buat daun baru Excel dan sebut saja Konverter SKU.. Salin seluruh kolom Kami .sku. Dari lembar Toko. Untuk lembar baru, kami menghapus duplikat dan hanya menyisakan nilai-nilai unik di dalamnya.

Tambah Kolom terdekat. Sup.sku. dan secara manual mencari kesesuaian antara nilai kolom Kami .sku. dan Sup.sku. (Ini akan membantu kami deskripsi dari kolom Deskripsi.). Ini adalah pekerja yang membosankan, biarkan gagasan bahwa Anda harus memenuhinya hanya sekali :-).

Akibatnya, kami memiliki tabel seperti itu:

2. Kami memperbarui tabel utama menggunakan data dari tabel untuk mencari.

Ke tabel utama (Sheet store) masukkan kolom baru Sup.sku..

Selanjutnya dengan suatu fungsi PR (Vlookup) Bandingkan lembar Toko. dan Konverter SKU. Kami .sku., dan untuk data yang diperbarui - kolom Sup.sku..

Kolom Sup.sku. Diisi dengan kode asli dari pabrikan.

Komentar: Jika di kolom Sup.sku. Ada sel-sel kosong, maka Anda perlu mengambil semua kode SKU.sesuai dengan sel-sel kosong ini menambahkannya ke tabel Konverter SKU. dan temukan kode yang sesuai dari tabel pemasok. Setelah itu, ulangi langkah 2.

3. Transfer data dari tabel pencarian ke tabel utama

Di tabel utama kami ada kolom utama dengan kebetulan persis dengan elemen-elemen dari tabel pencarian, jadi sekarang tugas ini tidak akan menyebabkan kesulitan

Menggunakan fungsi PR (Vlookup) menggabungkan data lembaran Toko. Dengan lembar data Pemasok Grosir 1.Menggunakan kolom untuk mencari kecocokan Sup.sku..

Berikut ini adalah contoh data yang diperbarui di kolom Harga grosir:

Semuanya sederhana, bukan? Tanyakan pertanyaan Anda di komentar ke artikel, saya akan mencoba menjawab sesegera mungkin.

Tabel konsolidasi nyaman untuk menggunakan data dan membuat laporan untuk menganalisis data. Dan jika data relasional ini (I.E., yang disimpan dalam tabel individu, tetapi pada saat yang sama mereka dapat digabungkan karena nilai umum), Anda dapat membuat tabel konsolidasi hanya dalam beberapa menit:

Apa yang berbeda dari tabel pivot ini? Harap dicatat bahwa dalam daftar bidang di sebelah kanan, tidak satu tabel ditampilkan, tetapi seluruh satu set tabel. Masing-masing tabel ini berisi bidang yang dapat digabungkan menjadi satu tabel konsolidasi untuk mendapatkan bagian data yang berbeda. Tidak diperlukan pemformatan manual dan persiapan data. Segera setelah mengimpor data, Anda dapat membuat tabel ringkasan berdasarkan tabel terkait.

Untuk menggabungkan beberapa tabel dalam daftar bidang tabel pivot:

Di bawah ini adalah prosedur untuk mengimpor beberapa tabel dari database SQL Server.

Catatan: Daftar bidang berisi beberapa tabel. Ini semua tables yang dipilih oleh Anda selama impor. Setiap meja dapat dikerahkan dan runtuh untuk melihat ladangnya. Karena tabel terhubung, Anda dapat membuat tabel konsolidasi, setelah melemparkan bidang dari tabel mana saja ke area tersebut Nilai-nilai, String. atau Kolom..

Meja Impor dari Sumber Lain

Selain SQL Server, Anda dapat mengimpor tabel dari sejumlah database relasional lainnya.

Basis data relasional bukan satu-satunya sumber data yang mendukung operasi dengan beberapa tabel dalam daftar bidang tabel pivot. Anda dapat menggunakan tabel di buku Anda atau saluran data impor, dan kemudian mengintegrasikannya dengan tabel data lain dalam buku ini. Agar semua data yang tidak terkait ini berfungsi bersama, setiap tabel ditambahkan ke model data, dan kemudian membuat tautan di antara mereka menggunakan nilai bidang yang sesuai.

Menggunakan model data untuk membuat tabel pivot baru

Anda mungkin telah membuat tautan antara tabel dalam model data dan sekarang siap untuk menggunakan data ini untuk analisis. Berikut ini dijelaskan cara membuat tabel ringkasan baru atau diagram ringkasan menggunakan model data dalam buku.

Sangat sering, ketika menghasilkan laporan (analis) sejumlah besar data, Anda harus mengarahkan informasi dari beberapa lembar buku "Excel" ke dalam satu meja.
Idealnya, untuk mengumpulkan dan analitik informasi dari berbagai lembar "Excel" disarankan untuk membuat tabel ringkasan, yang akan mengambil data dari beberapa sumber (lembar buku).

Tabel seperti itu dapat dilakukan dengan dua cara berbeda.

Pertimbangkan cara pertama.

Membuat tabel pivot dari berbagai lembar menggunakan fitur dan alat standar.

Langkah pertama.

Anda harus menambahkan tombol ke alat alat ke alat alat dan grafik ke pita.

Untuk melakukan ini, klik Klik kanan pada pita (toolbars) dan pilih "pita pengaturan" dari menu drop-down

masukkan tab

"File" \u003d\u003e "parameter" \u003d\u003e "pita pengaturan".

Selanjutnya, dalam pengaturan selotip dari daftar drop-down di bawah aplikasi "Pilih perintah", pilih "Semua Perintah". Jendela di bawah ini akan menampilkan semua perintah yang mungkin yang dapat ditempatkan pada panel dalam urutan abjad.

Dari daftar, pilih "Master of Ringkasan Tabel dan Grafik"

Di jendela kanan, menggunakan tombol "Buat Grup", buat grup baru alat. Untuk grup, Anda dapat memilih nama yang nyaman untuk Anda. Misalnya, "kelompoknya sendiri". Anda dapat memilih pada grup tab apa yang akan dibuat. Dalam contoh saya, saya memilih tab "Home".

Ketika grup dibuat, sorot dengan kursor, pilih kursor "Ringkasan dan Grafik" di jendela kiri dan klik tombol Add \u003e\u003e.

Setelah klik "OK".

Sekarang pada tab utama toolbar adalah alat "Ringkasan dan Grafik".

Langkah kedua. Bangun tabel pivot dari berbagai sumber data.

Cara kedua.

Membuat tabel data target dari beberapa lembar menggunakan kueri yang terbentuk di add-on kueri daya.

Metode ini untuk menggunakan permintaan add-in kueri daya.

Membuat kueri kueri daya untuk beberapa halaman buku dalam satu tabel.

Langkah pertama.

Anda perlu membuat dua permintaan, yang masing-masing akan mengambil informasi dari tabel terpisah.

Langkah dua.

Untuk melakukan ini, di tab Kueri Daya, klik tombol "Dari Tabel" dan tentukan rentang sumber data di jendela yang muncul. Setelah itu klik "OK".

Langkah ketiga.

Ketika permintaan kedua dibuat, Anda perlu mengklik tombol "Merge Query" di tab Kueri Daya dan mengkonfigurasi tampilan tabel umum yang dihasilkan di jendela yang muncul.

Sumber data untuk tabel konsolidasi berfungsi sebagai daftar data, di mana, sebagai aturan, setiap kolom bertindak sebagai bidang tabel konsolidasi. Tetapi bagaimana jika Anda datang kepada Anda, hanya dengan melihat tabel yang dikonsolidasikan, diformat dan secara eksternal mengingatkannya, tetapi tidak mungkin menggunakan alat kerja dengan tabel konsolidasi). Dan Anda perlu mengubahnya ke dalam daftar data, I.E. Lakukan operasi terbalik. Dalam posting ini, Anda akan belajar cara mengonversi tabel konsolidasi dengan dua variabel ke daftar data.

Gambar menunjukkan prinsip yang saya jelaskan. Itu. Dalam rentang A2: E5, ada tabel ringkasan asli yang dikonversi ke daftar data (H2: J14 range). Tabel kedua mewakili kumpulan data yang sama, hanya di sudut lain. Setiap nilai tabel konsolidasi sumber terlihat seperti string yang terdiri dari titik bidang baris, bidang kolom, dan nilai yang sesuai. Tampilan data ini berguna ketika perlu untuk mengurutkan dan memanipulasi metode lain ini.

Untuk mewujudkan kemampuan untuk membuat daftar seperti itu, kami akan menggunakan alat tabel pivot. Tambahkan tombol Master of Pivot Tables Di hubbar, yang tidak tersedia bagi kami pada rekaman itu, tetapi tetap sebagai dasar dari versi Excel sebelumnya.

Ikuti tab File -\u003e Parameter. Di kotak dialog yang muncul dialog ParameterUnggul,di tab Panel Akses Cepatdi bidang kiri, cari item Master Ringkasan Tabel dan Grafikdan tambahkan ke kanan. Klik OK.

Sekarang di panel pintasan Anda memiliki ikon baru.

Klik pada tab ini untuk menjalankan Master tabel pivot.

Pada langkah pertama wizard, Anda harus memilih jenis sumber data sumber data. Instal sakelar Dalam beberapa rentang konsolidasi dan Zhmem. Lebih lanjut.

Pada langkah 2a, tentukan cara membuat bidang halaman. Tempatkan sakelar Buat bidang halaman -\u003e Selanjutnya.

Pada langkah 2b, di lapangan Jarak Pilih rentang yang berisi data dan klik Menambahkan.Dalam kasus kami, ini akan menjadi lokasi ringkasan asli Tabel A1: E4.

Pada langkah ketiga, perlu untuk menentukan di mana perlu untuk menempatkan tabel penjarahan, dan klik Siap.

Excel akan membuat tabel konsolidasi dengan data. Di sisi kiri layar Anda akan melihat area Daftar bidang tabel pivot.Hapus semua item dari bidang baris dan kolom. Lebih detail tentang saya menulis dalam artikel sebelumnya.

Anda akan memiliki tabel ringkasan kecil yang terdiri dari satu sel, yang berisi jumlah dari semua nilai tabel sumber.

Klik dua kali pada sel ini. Excel akan membuat lembar baru yang akan berisi tabel dengan daftar nilai.

Berita utama dari tabel ini adalah informasi umum, Anda mungkin ingin membuatnya lebih informatif.