Tabel pivot di Excel adalah alat praktis untuk menganalisis dan menyajikan data. Tetapi bagaimana jika data berada di sumber yang berbeda? Mari cari tahu cara membuat tabel pivot dari beberapa lembar di Excel.
Bahkan jika ada sistem ERP dan BI, CFO tidak dapat melakukannya tanpa Excel. Semua jenis perhitungan tabel pivot, grafik yang nyaman - di Excel Anda dapat melakukan hampir semua hal. Tetapi Anda perlu tahu bagaimana melakukannya.
Tabel pivot di Excel menggunakan Power Query
Untuk memulainya, perlu diperhatikan bahwa Excel dapat bekerja dengan tabel sumber dengan berbagai ukuran. Namun judul dan judul tabel ini harus sama. Ini diperlukan agar program menginterpretasikan data yang digunakan dengan benar. Jika tidak, kesalahan dapat terjadi.
Misalkan perlu, data yang harus diambil dari lembaran yang berbeda. Situasi ini dapat muncul jika perusahaan memiliki beberapa titik penjualan yang didistribusikan, gudang, pelanggan yang berbeda dari kelompok barang yang sama. Dalam hal ini, laporan akan diberikan oleh cabang yang berbeda. Dan untuk menganalisisnya secara memadai, masuk akal untuk membuat tabel data yang umum untuk seluruh perusahaan, yang menjadi dasar pembuatan tabel pivot di masa mendatang.
Dalam hal ini, Anda perlu membuat lembar kerja kosong baru di Excel.
Di lembar ini, buka tab "Data".
Selanjutnya, klik "Buat Permintaan", di daftar drop-down pilih "Dari File" dan kemudian - "Dari Buku". Mari buat tabel pivot dari beberapa lembar menggunakan contoh - dua toko mengirim laporan tentang keberadaan kotak dengan warna berbeda di rak mereka. Setiap data toko disimpan dalam satu lembar. Dari lembaran-lembaran ini, "Buku 1" terbentuk, yang kami kerjakan.
Di jendela yang muncul, Anda perlu menentukan buku dari mana program harus mengambil data dan klik tombol "Impor".
Sebuah jendela bernama "Navigator" akan muncul. Di dalamnya, Anda perlu memilih lembar dari mana data akan diambil. Anda dapat menentukan lembar apa pun.
Program akan menampilkan data di jendela pratinjau, yang akan diambil dari lembar yang ditentukan.
Dalam contoh kita, terlihat bahwa sheet yang ditentukan berisi banyak sel dengan data "null". Ini tidak benar, karena program juga akan memproses sel-sel ini. Untuk mengurangi area nilai yang diproses dan menghapus sel nol tersebut, Anda perlu memperbaikinya berkas asli. Untuk melakukan ini, buka tabel asli dan tekan "Ctrl + End". Sel tabel aktif terakhir akan disorot. Anda perlu menghapus semua sel di kanan dan di bawah tabel, memastikan bahwa saat Anda menekan "Ctrl + End", sel kanan bawah tabel menjadi aktif.
Setelah itu, sumber data tidak akan berisi informasi yang tidak perlu.
Anda dapat menghapus baris "Navigasi" dan "Jenis Berubah". Dan mulailah mengedit data di bagian "Sumber". Jendela editor utama akan menampilkan daftar semua lembar buku yang ditentukan. Dalam kasus kami, "Sheet1" dan "Sheet2".
Kemudian, di baris "Data", klik ikon dengan dua panah, seperti yang ditunjukkan pada gambar.
Di jendela yang muncul, hapus centang pada kotak "Gunakan nama kolom asli sebagai awalan". Dan klik "OK". Sebuah tabel akan muncul berisi semua data.
Meja akan dibangun kembali. Baris duplikat dengan "tajuk" dapat dihapus. Untuk melakukan ini, di filter kolom "Gudang", hapus centang pada kotak "Gudang" dan klik "OK". Kemudian, di filter yang sama, klik "Hapus Kosong". Baris yang sesuai akan dihapus.
Di jendela "Unduh ke" yang muncul, alihkan sakelar ke posisi "Hanya buat koneksi" dan klik tombol "Unduh". Sebuah kueri akan muncul, yang menjadi dasar pembuatan tabel pivot.
Di jendela yang muncul, setel sakelar ke posisi "Gunakan sumber data eksternal" dan klik tombol "Pilih koneksi".
Di jendela yang muncul, pilih nama permintaan yang dibuat, dalam kasus kami - "ringkasan" dan klik tombol "Buka".
Desainer PivotTable muncul. Data dapat ditambahkan dan dipindahkan seperti pada tabel pivot biasa.
PivotTable dalam versi Excel sebelum 2016
DI DALAM Program Excel, diterbitkan hingga tahun 2003 inklusif, tabel pivot dari berbagai sumber dibuat menggunakan opsi "bangun pivot untuk beberapa rentang konsolidasi". Namun, konstruksi tabel seperti itu tidak memungkinkan untuk menganalisis sepenuhnya seluruh jumlah informasi yang diterima. Tabel yang dibangun dengan cara ini hanya dapat digunakan untuk analisis data paling dasar.
Dalam versi sejak 2007, perlu menambahkan "Wizard Tabel Pivot" khusus, di mana rentang data baru ditentukan satu per satu melalui kotak dialog. Tabel pivot yang dihasilkan juga memiliki fungsi yang agak terbatas.
Apa itu Tabel Pivot Excel? Mungkin, orang yang belum tahu akan menganggap yang paling jelas - ini adalah tabel di mana data dikumpulkan (diringkas) dari beberapa sumber berbeda lainnya yang memenuhi beberapa permintaan. Nyatanya, kesimpulan ini tidak sepenuhnya benar.
Excel PivotTables adalah alat yang mengumpulkan, menurut algoritme tertentu, informasi paling sering dari satu tabel database! (Kemungkinan menggunakan beberapa rentang konsolidasi sebagai sumber ada, tetapi tidak akan dibahas dalam artikel ini.)
Dengan bantuan tabel pivot dari kumpulan baris database yang kacau, Anda bisa dalam hitungan detik ekstrak yang diperlukan, potong informasi yang tidak perlu di dalamnya, tambahkan informasi yang hilang dan sajikan kepada pengguna dalam bentuk yang indah dan mudah dibaca.
Jika Anda terus-menerus bekerja di MS Excel dengan banyak informasi dan tidak tahu apa-apa tentang tabel pivot, pertimbangkan bahwa Anda "memalu paku" dengan kalkulator tanpa mengetahui tujuan sebenarnya!
Kapan tabel pivot harus digunakan?
Pertama, saat Anda bekerja dengan sejumlah besar data statistik, yang sangat melelahkan untuk dianalisis dengan bantuan penyortiran dan filter.
Kedua, bila perlu mengulang (menghitung ulang) laporan pada interval tertentu karena perubahan sumber - tabel database.
Ketiga, saat menganalisis perubahan data database dalam konteks berbagai periode waktu atau kriteria lainnya.
Ini adalah situasi utama di mana kekuatan sihir sebenarnya dari alat PivotTable Excel terungkap.
Membuat tabel pivot dan bekerja dengannya tidak memengaruhi konten sumber - database! Tabel pivot unggulmemungkinkan Anda untuk melihat data ini dari sudut yang berbeda tanpa mengubah apa pun di database!!!
Ini adalah artikel terakhir dan terpenting dari seri ulasan tentang menyimpan informasi dan mengelola volume data tabular yang besar.
Di artikel siklus sebelumnya, kami membuat, mempelajari, menerapkan, dan, masuk.
Buat template tabel pivot.
Kami akan membuat tabel pivot di buku kerja yang samaunggul, di mana sumber informasi berada - tabel database, tetapi pada lembar yang berbeda.
Kami terus bekerja dengan mini-base DB2 pendidikan "Produksi struktur logam berdasarkan bagian No. 2", yang kami kerjakan di semua artikel siklus ini.
1. Buka file di MS Excel.
2. Kami mengaktifkan ("klik dengan mouse") setiap sel di dalam tabel dasar.
3. Kami menjalankan perintah dari menu utama program "Data" - "Tabel pivot ...". Perintah ini meluncurkan layanan mini PivotTable Wizard.
4. Tanpa berpikir lama tentang opsi untuk memilih posisi sakelar di jendela tarik-turun "Penyihir ...", kami mengonfigurasinya (lebih tepatnya, jangan menyentuhnya) seperti yang ditunjukkan pada tangkapan layar di bawah, bergerak di antara jendela menggunakan tombol "Berikutnya".
Pada langkah kedua, "Wizard ..." akan memilih rentang itu sendiri jika Anda telah menyiapkan database dengan benar dan menyelesaikan langkah 2 dari bagian artikel ini.
5.
Pada langkah ketiga dari "Penyihir ...", klik tombol "Selesai". Template tabel pivot dihasilkan dan ditempatkan pada lembar baru dari file yang sama di mana database berada!
Kecuali untuk saat ini templat kosong pada lembar yang baru dibuat berkas Excel Anda melihat panel "Tabel Pivot" dengan alatnya dan jendela "Daftar Bidang Tabel Pivot" yang muncul saat sel apa pun di dalam tabel pivot diaktifkan. Mereka muncul secara otomatis, saya hanya menempatkannya di sebelah template dan menyesuaikan ukurannya. Jika Anda tidak melihat panel "Tabel Pivot", panggil melalui menu utama dengan perintah "Lihat" - "Bilah Alat" - "Tabel Pivot"
Di MS Excel 2007 dan versi yang lebih baru, "Penyihir ..." dihapuskan karena 99% pengguna tidak pernah mengubah pengaturan sakelar yang diusulkan dan melalui tiga langkah ini, hanya menyetujui opsi yang diusulkan (kami juga melakukan ini).
Di MS Excel 2007, dengan menggunakan perintah "Tabel Pivot", kotak dialog "Buat Tabel Pivot" muncul, di mana cukup menentukan sumber data untuk analisis dan lokasi tabel pivot yang akan dibuat.
Pembuatan tabel pivot Excel yang berfungsi.
Ada empat zona dalam template yang dibuat, di mana untuk membuat tabel pivot Anda harus menempatkan nama kolom-kolom tabel database dengan menyeretnya dengan tombol kiri mouse dari jendela "Daftar Bidang Tabel Pivot". Saya mengingatkan Anda bahwa dalam database, bidang adalah kolom dengan nama.
Perhatian!!!
Elemen jendela "Daftar Bidang PivotTable" adalah judul bidang basis data!
1. Jika Anda menempatkan salah satu elemen "Daftar Bidang Tabel Pivot" paling banyak zona atas template di mana itu akan menjadi "Bidang halaman", maka kita mendapatkan kenyamanan menyaring catatan bidang ini.
2. Jika Anda menyeret satu elemen (atau dua, lebih jarang tiga atau lebih) dari Daftar Bidang PivotTable ke zona kiri template, di mana itu akan menjadi "Bidang string" header baris tabel pivot.
3. Jika Anda menyeret satu elemen (atau dua, lebih jarang - tiga atau lebih) dari "Daftar ..." ke zona atas templat, di mana itu akan menjadi "Bidang kolom", maka kita akan mendapatkan dari catatan bidang tabel database ini judul kolom tabel pivot.
4. Jika Anda menyeret satu elemen (atau dua, lebih jarang - tiga atau lebih) dari "Daftar ..." ke area tengah templat, di mana itu akan menjadi "elemen data", maka kita akan mendapatkan record dari field database ini nilai-nilai tabel pivot.
Nilai - elemen data - akan ditempatkan sesuai ketat dengan aturan tabel dua dimensi, yaitu di persimpangan tajuk baris dan tajuk kolom yang sesuai!
Tidak terlalu jelas? Mari beralih ke contoh praktis - semuanya akan menjadi jelas!
Tugas nomor 9:
Berapa ton struktur logam yang diproduksi untuk setiap pesanan?
Ayo lakukan dua hal untuk menjawab pertanyaan ini! Skema tindakan ini ditunjukkan pada gambar sebelumnya dengan panah biru.
1. Seret elemen "Pesan" dari jendela "Daftar Bidang Tabel Pivot" ke area "Bidang Baris" dari templat kosong.
2.
Seret elemen "Massa total, t" dari jendela "Daftar bidang tabel pivot" ke zona "Elemen data" dari templat. Hasilnya ada di tangkapan layar di sebelah kiri. Saya pikir tidak ada penjelasan yang diperlukan.
Judul "Elemen Data" "Jumlah di atas bidang Massa total, t" berada lebih tinggi tajuk "Bidang baris" dan di sebelah kiri tempat tajuk "Bidang kolom" dapat ditemukan. Perhatikan ini!
Tugas nomor 10:
Berapa ton struktur baja yang diproduksi untuk setiap pesanan berdasarkan tanggal?
Kami terus bekerja.
3. Untuk menjawab pertanyaan tugas No. 10, cukup menambahkan elemen "Tanggal" ke tabel pivot kami dari jendela "Daftar Bidang Tabel Pivot" ke zona "Bidang Kolom" pada templat.
4.
Catatan dapat dikelompokkan, misalnya berdasarkan bulan. Untuk melakukannya, pada panel "Tabel Pivot", klik tab "Tabel Pivot" dan pilih "Grup dan Struktur" - "Grup ...". Di jendela Pengelompokan, lakukan pengaturan sesuai dengan tangkapan layar di sebelah kiri.
Karena semua catatan dalam database kami dibuat pada bulan April, setelah pengelompokan, kami hanya melihat dua kolom - "Apr" dan "Total Keseluruhan". Saat rekaman bertanggal Mei dan Juni muncul di database sumber, dua kolom terkait ditambahkan ke tabel pivot. 
Tugas nomor 11:
Kapan dan berapa ton dan potongan prangko yang diproduksi secara total berdasarkan pesanan No.3?
5. Untuk memulai, seret bidang "Urutan" dari jendela "Daftar Bidang Tabel Pivot" atau langsung dari Tabel Pivot itu sendiri ke bagian paling atas lembar ke area "Bidang Halaman" pada templat.
7. Tambahkan bidang "Kuantitas, pcs" ke area "Elemen data".
Konfigurasi tabel pivot Excel yang diperlukan untuk menjawab pertanyaan tugas telah dibentuk.
8. Klik tombol panah bawah di sel B1 dan di daftar drop-down, alih-alih entri "(Semua)", pilih "3", sesuai dengan pesanan No. 3 yang menarik bagi kami dan tutup daftar dengan mengklik " tombol OK”.
Jawaban atas pertanyaan tugas ada di tangkapan layar di bawah teks ini.
Pemformatan tabel pivot.
Jika tabel pivot perlu dicetak dan diperlihatkan kepada seseorang, atau hanya untuk sedikit menyenangkan diri Anda sendiri, tabel pivot perlu diformat - diberi tampilan yang layak dan dirumuskan ulang beberapa tajuk yang "tidak memadai".
Judul "Sum for the field Total weight, t" dan "Sum for the field Quantity, pcs" terdengar, semacam, dan dapat dimengerti, tetapi entah bagaimana tidak dalam bahasa Rusia. Mari kita ganti namanya menjadi "Total massa produk, t" dan "Jumlah produk, buah" yang lebih merdu.
1. Mari lanjutkan dengan tabel yang dibuat. Aktifkan sel B5 dengan mouse.
2. Pada bilah alat PivotTable, klik tombol Opsi Bidang (disorot di sebelah kanan pada gambar di bawah).
3. Di jendela tarik-turun "Perhitungan bidang tabel pivot", ubah nama dan klik tombol "OK".
4.
Menurut algoritme serupa, kami mengganti nama tajuk bidang kedua, setelah pertama "memasukkan" mouse ke sel B6.
5. Pada panel "Tabel Pivot", klik tombol "Format Laporan" (disorot di sebelah kiri pada gambar di atas - di atas item 3).
6. Di jendela "Format Otomatis" yang muncul, pilih dari opsi pemformatan yang diusulkan, misalnya, "Laporan 6" dan klik "OK".
Penampilan tabel pivot kami telah berubah secara signifikan.
Kesimpulan.
Saya menarik perhatian Anda ke beberapa poin yang sangat penting!
Saat mengubah sumber (misalnya, menambahkan catatan lain ke database), perubahan tidak akan terjadi secara otomatis di tabel pivot itu sendiri !!!
Agar perubahan dalam database tercermin dalam tabel pivot, Anda perlu "secara manual" menekan tombol "Perbarui data" setiap kali (ikon tombolnya adalah " ! ) di panel PivotTable!!!
Tempatkan di bidang "Page Margins" area yang berisi informasi teks, yang mungkin menjadi menuju untuk tabel pivot yang dihasilkan.
Di area Bidang Baris dan Bidang Kolom, tempatkan bidang yang berisi informasi teks, yang mungkin menjadi judul untuk baris dan kolom tabel pivot yang dibuat.
Di zona "Elemen Data", tempatkan terutama informasi numerik.
Lanjutkan dengan menempatkan bidang seperti yang akan Anda lakukan di selembar kertas! Jangan takut melakukan kesalahan. Semuanya mudah diperbaiki.
Tabel pivot yang dibuat dengan dua atau tiga gerakan mouse di salah satu bagian sebelumnya dari artikel ini menjawab pertanyaan yang sangat sulit dengan sangat cepat! Jika pesanan dilakukan dalam beberapa bulan, dan jumlah nama merek melebihi beberapa ribu, berapa lama waktu yang Anda perlukan untuk menyelesaikan masalah yang dibahas di atas? Jam? Hari? PivotTable Excel melakukannya secara instan, berulang kali, dan tanpa kesalahan!!!
Banyak buku tebal dan tipis yang bagus telah ditulis tentang PivotTable Excel. Pertama-tama, ini adalah buku karya Bill Jelen ("Mr. Excel") dan Michael Alexander. Bagi mereka yang tertarik dengan topik ini, saya sarankan untuk membacanya.
Tentu saja, sangat sulit, hampir tidak mungkin, untuk membicarakan alat luar biasa ini secara detail dalam sebuah artikel pendek. Di satu sisi, saya ingin menyentuh aspek yang lebih penting secara praktis, tetapi di sisi lain, di artikel pengantar saya tidak ingin "masuk ke hutan", menakuti pengguna dengan volume dan kerumitan informasi. Jika “alat ajaib” MS Excel ini membangkitkan minat pembaca blog, maka ke depan topik ini akan dilanjutkan dengan peluncuran brosur kecil dengan rekomendasi praktis yang penting dan berharga.
Pastikan untuk menggunakan PivotTable Excel! Nilai Anda sebagai spesialis akan meningkat berkali-kali lipat!
Pada rangkaian enam artikel ini, yang merupakan gambaran umum dari topik tersebut memproses sejumlah besar informasi di Excellengkap.
aku memohon menghormati karya penulis langganan untuk pengumuman artikel di jendela yang terletak di akhir setiap artikel atau di jendela di bagian atas halaman!
Pembaca yang budiman, tinggalkan pertanyaan, umpan balik, dan komentar di komentar di bagian bawah halaman.
Sumber data untuk PivotTable adalah daftar data, dengan aturan, setiap kolom berfungsi sebagai bidang di PivotTable. Tetapi bagaimana jika sebuah tabel datang kepada Anda yang hanya terlihat seperti tabel pivot (diformat dan terlihat seperti itu, tetapi tidak mungkin menggunakan alat untuk bekerja dengan tabel pivot). Dan Anda perlu mengubahnya menjadi daftar data, mis. menjalankan operasi terbalik. Dalam postingan ini, Anda akan mempelajari cara mengonversi tabel pivot dengan dua variabel menjadi daftar data.
Gambar tersebut menunjukkan prinsip yang saya jelaskan. Itu. dalam rentang A2:E5 adalah tabel pivot asli, yang diubah menjadi daftar data (kisaran H2:J14). Tabel kedua mewakili kumpulan data yang sama, hanya dari perspektif yang berbeda. Setiap nilai tabel pivot asli terlihat seperti string, terdiri dari item bidang baris, bidang kolom, dan nilainya yang sesuai. Tampilan data ini berguna saat Anda perlu mengurutkan dan memanipulasi data dengan cara lain.
Untuk mewujudkan kemungkinan membuat daftar seperti itu, kami akan menggunakan alat tabel pivot. Mari tambahkan tombol Panduan PivotTable ke panel akses cepat, yang tidak tersedia untuk kami di pita, tetapi tetap sebagai sisa dari versi Excel sebelumnya.
Arahkan ke tab Berkas -> Opsi. Pada kotak dialog yang muncul Pilihanunggul, tab Panel akses cepat di bidang kiri temukan itemnya Panduan PivotTable dan PivotChart dan tambahkan ke yang benar. Klik Oke.
Anda sekarang memiliki ikon baru di Bilah Alat Akses Cepat.
Klik pada tab ini untuk meluncurkan Panduan PivotTable.
Langkah pertama panduan ini adalah memilih tipe sumber data PivotTable. Memasang sakelar Dalam berbagai rentang konsolidasi dan klik Lebih jauh.
Pada langkah 2a, tentukan bagaimana bidang halaman harus dibuat. Tempatkan sakelar Buat Bidang Halaman -> Selanjutnya.
Pada langkah 2b, di lapangan Jangkauan pilih rentang yang berisi data dan klik Menambahkan. Dalam kasus kita, ini akan menjadi lokasi tabel pivot asli A1:E4.
Pada langkah ketiga, Anda perlu memutuskan di mana Anda ingin meletakkan tabel pivot, dan klik tombolnya Siap.
Excel akan membuat PivotTable dengan data. Di sisi kiri layar Anda akan melihat sebuah area Daftar bidang tabel pivot. Hapus semua item dari bidang baris dan kolom. Saya menulis lebih banyak tentang itu di artikel sebelumnya.
Anda akan mendapatkan tabel pivot kecil yang terdiri dari satu sel yang berisi jumlah semua nilai dalam tabel asli.
Klik dua kali pada sel ini. Excel akan membuat lembar baru yang berisi tabel dengan daftar nilai.
Judul dari tabel ini mewakili informasi Umum Anda mungkin ingin membuatnya lebih informatif.
Tabel pivot digunakan untuk menganalisis sejumlah besar data dengan cepat. Ini memungkinkan Anda untuk menggabungkan informasi dari berbagai tabel dan lembar, menghitung hasil keseluruhan. Alat analitik universal ini secara signifikan memperluas kemampuan program Excel.
Anda dapat menghasilkan total baru berdasarkan parameter asli dengan menukar baris dan kolom. Anda dapat memfilter data dengan menampilkan elemen yang berbeda. Dan juga secara visual merinci area tersebut.
Tabel Pivot di Excel
Misalnya, kami menggunakan tabel penjualan barang di berbagai cabang perdagangan.
Dari piring Anda bisa melihat di bagian mana, apa, kapan dan berapa jumlah yang dijual. Untuk mengetahui jumlah penjualan untuk setiap departemen, Anda harus menghitung secara manual di kalkulator. Atau buat spreadsheet Excel lain, di mana menggunakan rumus untuk menunjukkan totalnya. Menganalisis informasi dengan cara ini tidak produktif. Tidak cukup lama untuk salah.
Solusi paling rasional adalah membuat tabel pivot di Excel:
Sederhana, cepat dan berkualitas tinggi.
Nuansa penting:
- Baris pertama dari rentang yang ditentukan untuk rollup data harus diisi.
- Di pelat dasar, setiap kolom harus memiliki tajuknya sendiri - lebih mudah untuk menyiapkan laporan ringkasan.
- Di Excel, Anda dapat menggunakannya sebagai sumber informasi Tabel akses, Server SQL dan sebagainya.
Cara membuat tabel pivot dari beberapa tabel
Seringkali Anda ingin membuat laporan ringkasan dari beberapa tabel. Ada beberapa papan informasi. Kita perlu menggabungkannya menjadi satu kesamaan. Untuk sains, mari kita cari sisa makanan di gudang di dua toko.
Prosedur untuk membuat tabel pivot dari beberapa sheet adalah sama.
Mari buat laporan menggunakan PivotTable Wizard:
Seperti yang Anda lihat, hanya dengan beberapa klik, Anda dapat membuat laporan kompleks dari beberapa lembar atau tabel dengan jumlah informasi yang berbeda.
Cara bekerja dengan tabel pivot di Excel
Mari kita mulai dengan yang paling sederhana: menambah dan menghapus kolom. Misalnya, pertimbangkan tabel ringkasan penjualan untuk berbagai departemen (lihat di atas).
Di sebelah kanan tabel pivot, kami memiliki area tugas tempat kami memilih kolom di daftar bidang. Jika sudah hilang tinggal klik tabletnya.
Mari tambahkan bidang lain ke tabel pivot untuk laporan. Untuk melakukan ini, centang kotak di seberang "Tanggal" (atau di seberang "Produk"). Laporan segera berubah - dinamika penjualan per hari di setiap departemen muncul.
Pengelompokan data dalam laporan berdasarkan bulan. Untuk melakukannya, klik klik kanan arahkan mouse ke bidang "Tanggal". Klik "Grup". Pilih "berdasarkan bulan". Ini menghasilkan tabel pivot seperti ini:
Ke ubah opsi di tabel pivot, cukup hapus centang pada kotak di sebelah bidang baris yang ada dan atur untuk bidang lain. Mari kita buat laporan berdasarkan nama produk, bukan per departemen.
Dan inilah yang terjadi jika kita menghapus "tanggal" dan menambahkan "departemen":
Tetapi laporan seperti itu dapat dibuat jika seret bidang di antara area yang berbeda:
Untuk mengubah judul baris menjadi judul kolom, pilih judul ini, klik menu pop-up. Klik "pindah ke judul kolom". Dengan cara ini, kami telah memindahkan tanggal ke dalam kolom.
Kami meletakkan bidang "Departemen" di depan nama barang. Menggunakan bagian menu "pindah ke awal".
Tampilkan detail produk tertentu. Pada contoh tabel pivot kedua yang menampilkan saldo di gudang. Pilih sel. Klik tombol kanan mouse - "perluas".
Di menu yang terbuka, pilih bidang dengan data yang ingin Anda tampilkan.
Saat kami mengklik tabel pivot, tab dengan opsi laporan tersedia. Dengan bantuannya, Anda dapat mengubah judul, sumber data, informasi grup.
Verifikasi kebenaran tagihan utilitas yang dikeluarkan
Dengan bantuan tabel pivot Excel, mudah untuk memeriksa seberapa benar organisasi jasa membebankan biaya sewa. Poin positif lainnya adalah penghematan. Jika kita mengontrol setiap bulan berapa banyak listrik dan gas yang dikonsumsi, kita dapat menemukan cadangan untuk menghemat uang untuk membayar apartemen.
Untuk memulainya, kami mengusulkan untuk menyusun tabel ringkasan tarif untuk semua tagihan listrik. Untuk kota yang berbeda, datanya akan berbeda.
Misalnya, kami membuat tabel ringkasan tarif untuk Moskow:
Untuk tujuan pendidikan, ambil satu keluarga dengan 4 orang yang tinggal di apartemen seluas 60 meter persegi. m. Untuk mengontrol tagihan listrik, Anda perlu membuat tabel untuk perhitungan setiap bulan.
Kolom pertama = kolom pertama dari tabel pivot. Yang kedua adalah rumus untuk menghitung tampilan:
Tarif * jumlah orang / pembacaan meter / area
Rumus kami mengacu pada lembar tempat tabel ringkasan dengan tarif berada.
Jika tunjangan diterapkan saat menghitung tagihan utilitas, tunjangan juga dapat dimasukkan ke dalam rumus. Minta informasi tentang akrual di departemen akuntansi organisasi layanan Anda. Saat tarif berubah, ubah saja data di sel.
PivotTable adalah alat yang membantu Anda menganalisis data perusahaan. Anda dapat membuat tabel seperti itu di berbagai editor teks.
Untuk membuat, Anda harus memilih judul untuk baris dan kolom dari laporan yang dihasilkan. Cara paling mudah untuk membuat laporan adalah di Excel. Anda dapat menerapkan filter ke seluruh laporan atau ke bagian tertentu darinya.
cara membuat tabel pivot di excel
Mari kita lihat versi 2007 terlebih dahulu. Beberapa pengguna masih memiliki editor versi lama. Untuk memulai yang Anda butuhkan buat tabel untuk membangun sebuah laporan. Seharusnya tidak berisi sel dan garis kosong. Mari kita analisis proses pembuatan laporan menggunakan contoh paling sederhana:
- Mulai Excel 2007. Pilih tab "Sisipkan" di bilah atas.
- Pilih "Buat PivotTable yang Direkomendasikan".
- Sebuah jendela akan muncul menampilkan opsi bingkai. Anda dapat mempratinjau tampilan laporan Anda dengan mengeklik tata letak. Setelah itu, Anda dapat memilih yang utama. Klik OK dan laporan akan muncul di lembar. Selain itu, Excel sendiri yang akan menentukan urutan barisnya.
- Anda dapat memfilter dan mengurutkan nilai dalam program. Ada ikon khusus di panel untuk ini.
Ini adalah contoh paling sederhana bagaimana Anda bisa membuat tabel pivot versi lama unggul.
Tabel pivot excel 2010 dan 2013 - contoh pengerjaan excel
Membuat laporan di Excel 2010 dan 2013 hampir sama. Proses dalam versi ini sedikit berbeda dengan pembuatan di versi 2007. Tetapi, beberapa saat serupa: sebelum Anda mulai, Anda perlu membuat tabel sederhana tempat Anda menentukan semua data yang Anda butuhkan. Baris teratas harus berisi nama kolom.
Sekarang Anda bisa membuat laporan di Excel:
Apa yang bisa dilakukan dengan lembar:
- Anda akan memiliki akses ke bilah alat untuk mengelola nilai. Semua bidang dan judulnya akan dicantumkan di tab "Pilih bidang untuk ditambahkan ke laporan". Untuk menambahkan item yang diperlukan, Anda harus memilih bidang yang diperlukan dan daftar akan dibuat.
- Dimungkinkan untuk mengontrol lokasi bidang. Anda dapat menarik dan melepas kolom ke jendela yang Anda inginkan, serta mengurutkan sesuai urutan yang Anda inginkan. Anda juga dapat memfilter item. Untuk melakukan ini, seret item ke jendela "Filter". Jika Anda perlu menghitung untuk bidang terpisah, seret ke jendela "Nilai".
- Anda dapat gunakan opsi tambahan. Anda dapat menemukannya dengan prinsip berikut: "Parameter" "Bekerja dengan Tabel Pivot". Ada banyak alat yang memiliki banyak pengaturan dan fitur tambahan.
Cara Membuat PivotTable Menggunakan Wisaya PivotTable
Alat Panduan PivotTable tidak muncul di panel utama. Makanya harus diaktifkan dulu. Untuk melakukan ini, buka "File" "Options" "Quick Access Toolbar". di depanmu daftar akan terbuka"pilih perintah dari", centang "Perintah di pita". Sekarang pilih PivotTable Wizard dan tambahkan. Setelah semua tindakan dilakukan, ikon fungsi yang dipilih akan muncul di panel utama.
Sekarang Anda dapat melanjutkan ke proses pembuatan laporan ringkasan menggunakan wizard:
Jika laporan sederhana dipilih sebagai sumber, yang memiliki semua data yang diperlukan untuk laporan tersebut, Anda tinggal membuatnya. Atur rentangnya, yaitu memilih data meja sederhana. Pilih tempat untuk ditempatkan dan klik tombol "Selesai".
Jika Anda memilih sumber yang datanya perlu dikonsolidasikan dari sumber lain, maka harus dicentang berlawanan dengan item ini. Sekarang Anda perlu menentukan bagaimana Anda akan membuat bidang halaman. Anda juga perlu menentukan rentang. Untuk melakukan ini, pertama-tama pilih satu rentang dan klik tombol "tambah". Nilai selanjutnya juga ditambahkan.
Agar tidak bingung dalam rentangnya, perlu diberi nama. Untuk melakukan ini, pilih salah satu rentang daftar dan tentukan jumlah bidang untuk itu, setelah itu Anda memberi nama setiap bidang. Begitu juga dengan semua rentang.
tabel pivot excel 2010 dengan banyak lembar
Jika informasi dalam laporan memiliki jenis yang sama, paling mudah menggunakan wizard tabel pivot. Jika laporan memiliki struktur yang rumit, Anda perlu bertindak berbeda. Misalnya, ada dua meja, satu berisi tanda terima barang, dan yang lainnya - jumlah barang yang dijual di toko yang berbeda. Hal utama adalah memiliki tajuk yang sama, lebih sedikit waktu akan mentransfernya ke dalam satu laporan. Dalam hal ini, laporan dibuat sebagai berikut:
Sekarang kita perlu membuat laporan ringkasan:
- Menyisipkan.
- Laporan konsolidasi.
- Masukkan jangkauan dan lokasi.
Namun bagaimana jika Anda perlu membuat laporan dari beberapa tabel. Sulit untuk bekerja dengan tangan. Untuk ini Anda dapat gunakan EXCEL add-in khusus. Itu diunduh dari situs web pengembang.
Jadi kami menemukan caranya membuat tabel pivot di excel. Anda perlu menunjukkan sedikit ketekunan dan perhatian, dan hasilnya tidak akan membuat Anda menunggu.
Memuat...