Tabel ringkasan Excel: bagaimana membuat dan bekerja? Bekerja dengan Excel PivotTables

Banyak pengguna kantor sering menghadapi sejumlah masalah saat mencoba membuat dan mengedit dokumen kantor. Seringkali hal ini disebabkan oleh kenyataan bahwa perusahaan menggunakan beberapa program perkantoran, prinsip kerja yang dapat sangat bervariasi. Apalagi banyak kesulitan bisa membuat spreadsheet Excel.

Untungnya, MS Office 2010-2013 baru-baru ini muncul, yang tidak hanya mencakup sejumlah program update untuk memproses file teks, tabel, database dan presentasi, namun juga memungkinkan beberapa karyawan untuk bekerja sama dengan mereka secara bersamaan, yang tak ternilai harganya di lingkungan perusahaan.

Mengapa versi baru?

Untuk memahami masalah ini, Anda perlu membayangkan semua perubahan paling signifikan yang telah terjadi dengan perkantoran ini.

Seperti sebelumnya, Excel adalah program terpopuler kedua, yang memungkinkan Anda untuk tidak hanya membuat tabel sederhana, namun juga membuat database yang cukup kompleks. Seperti komponen lainnya, tombol "Office" ditambahkan ke dalamnya, dengan mengkliknya Anda dapat menyimpan dokumen dalam format yang dibutuhkan oleh Anda, mengubahnya, atau menentukan tindakan yang diperlukan untuk melindunginya. Modul keamanan dokumen telah diperbarui secara signifikan.

Sedangkan untuk perubahan paling signifikan di Excel, kita harus mencatat koreksi banyak kesalahan dalam formula, oleh karena itu dalam versi terakhir cukup sering ada kesalahan yang cukup bruto dalam perhitungan.

Saya harus mengatakan bahwa versi baru dari paket tidak hanya seperangkat program perkantoran, tapi juga alat yang hebat, cocok untuk menyelesaikan tugas yang sangat rumit sekalipun. Seperti yang bisa Anda pahami, hari ini kita akan mempertimbangkan spreadsheet Excel.

Apa itu dan untuk apa?

Perubahan paling signifikan pada versi baru "Office" adalah antarmuka mereka yang didesain ulang sepenuhnya, yang disebut pencipta Ribbon (tape). Di dalam kaset, 1500 tim dengan mudah dikelompokkan ke dalam kategori, jadi Anda tidak perlu mencarinya lama. Untuk sepenuhnya mematuhi parameter ini, para pengembang yang ditambahkan ke Excel juga memperbaiki tabel ringkasan.

Hari ini kita akan mempertimbangkan Excel "untuk teko". Tabel pivot adalah alat khusus dimana hasil suatu proses dikelompokkan secara visual. Sederhananya, dengan bantuan mereka Anda bisa melihat berapa banyak produk yang dijual setiap penjual untuk shift kerjanya. Selain itu, mereka digunakan dalam kasus-kasus di mana diperlukan:

• Siapkan perhitungan analitis untuk penulisan laporan akhir.
• Hitung masing-masing indikator mereka secara terpisah.
• Buat pengelompokan data menurut jenisnya.
• Lakukan filtrasi dan analisis mendalam terhadap data yang didapat.

Pada artikel ini, kita akan melihat cara sederhana untuk membuatnya. Anda akan belajar bagaimana spreadsheet Excel membantu membuat dan menganalisis deret waktu, membuat dan membongkar perkiraan yang lebih rinci.

Buat dokumen yang diinginkan

Jadi bagaimana membuat tabel pivot di Excel? Pertama, kita perlu menyusun sebuah dokumen sederhana dimana kita memasukkan data untuk analisis selanjutnya. Satu kolom harus memiliki satu parameter. Mari ambil contoh paling sederhana:

• Waktu penjualan.
• Barang terjual
• Biaya semua penjualan.

Jadi, di antara semua parameter di setiap kolom tertentu, sebuah ikatan terbentuk: misalkan sepatu kets terjual pada jam 9 pagi, dengan keuntungan menjadi n-rubel.

Memformat spreadsheet

Setelah menyiapkan semua informasi awal, letakkan kursor di sel pertama kolom pertama, buka tab "Insert", dan kemudian klik pada tombol "Summary table". Kotak dialog segera muncul , di mana Anda dapat melakukan hal berikut:

• Jika Anda langsung mengklik tombol "OK", spreadsheet Excel akan segera ditampilkan di lembar terpisah.
• Sepenuhnya menyesuaikan kesimpulan.

Dalam kasus terakhir, para pengembang memberi kita kemampuan untuk menentukan kisaran sel tempat informasi yang diinginkan ditampilkan. Setelah itu, pengguna harus menentukan dengan pasti di mana tabel baru akan dibuat: pada yang sudah ada atau pada lembar yang baru dibuat. Mengklik "OK", Anda segera melihat meja siap pakai di depan Anda. Beginilah cara pembuatan tabel pivot di Excel hampir selesai.

Di sisi kanan lembaran adalah area tempat Anda bekerja. Bidang dapat diseret ke area terpisah, setelah itu data dari mereka akan ditampilkan di tabel. Dengan demikian, tabel pivot itu sendiri akan berada di bagian kiri ruang kerja.

Memegang bidang "Barang" dengan tombol kiri mouse ditekan, kami mengirimkannya ke "Nama Jalur", dan item "Jumlah semua penjualan" dengan cara yang sama diteruskan ke "Nilai" (di sisi kiri lembaran). Begitulah cara Anda bisa mendapatkan jumlah penjualan untuk keseluruhan periode yang dianalisis.

Penciptaan dan pengelompokan deret waktu

Untuk menganalisis penjualan untuk interval waktu tertentu, Anda perlu memasukkan item yang relevan ke dalam tabel itu sendiri. Untuk melakukan ini, pergi ke lembar "Data" dan kemudian masukkan tiga bar baru segera setelah tanggal tersebut. Pilih kolom dengan nama produk, kemudian klik tombol "Tempel".

Sangat penting bahwa semua kolom yang baru dibuat ada di dalam tabel yang sudah ada dengan data asli. Dalam kasus ini, Anda tidak perlu membuat tabel pivot di Excel lagi. Anda cukup menambahkan field baru dengan data yang dibutuhkan.

Rumus yang digunakan

Misalnya, kolom yang baru dibuat dapat disebut "Tahun", "Bulan", "Bulan-Tahun". Untuk mendapatkan data yang kami minati, masing-masing harus menulis rumus terpisah untuk perhitungan:

• Dalam "Tahunan" kami memasukkan formula dalam bentuk: "= YEAR" (mengacu pada tanggalnya).
• Bulan harus dilengkapi dengan ungkapan: "= BULAN" (juga dengan referensi tanggal).
• Di kolom ketiga, kami memasukkan rumus formulir: "= KLIK" (mengacu pada ini selama satu tahun dan satu bulan).

Dengan demikian, kita mendapatkan tiga kolom dengan semua data awal. Sekarang Anda perlu masuk ke menu "Ringkasan", klik pada setiap ruang kosong di meja dengan tombol mouse sebelah kanan, dan kemudian pada menu konteks terbuka pilih "Update". Perhatikan! Dengan hati-hati tuliskan rumus di spreadsheet Excel, karena kesalahan apapun akan menyebabkan perkiraan yang tidak akurat.

Bagaimana cara menganalisa penjualan per tahun?

Sekali lagi, kita tahan item "Tahun" dengan tombol kiri mouse, lalu seret ke "Column name", setelah itu tabel ringkasan mulai menampilkan hasil penjualan jenis produk tertentu selama bertahun-tahun. Dan jika Anda juga perlu menganalisa penjualan bulanan? Demikian pula, kita menekan LMC "Month", menyeretnya di bawah kolom tahunan.

Untuk mendapatkan dinamika visual perubahan bulanan dalam penjualan menurut tahun, bidang "Bulan" harus diseret langsung ke kolom tahunan yang sebelumnya dipindahkan ke "Nama Kolom". Setelah menyelesaikan operasi ini, Anda akan melihat tabel pada tampilan berikut:

• Jumlah penjualan produk tertentu sepanjang tahun.
• Dinamika penjualan masing-masing tahun demi tahun.

Kami menghapus data dari masalah ini

Misalkan dari penerbitan kita perlu menghapus data untuk bulan Oktober, karena kita tidak memiliki statistik lengkap mengenai hal itu. Dalam tabel ringkasan kami mencari "Report Filter", lalu seret "Tahun - Bulan" ke dalamnya.

Di atas tabel muncul filter yang ingin Anda centang di sebelah "Pilih beberapa item". Setelah itu, dalam daftar yang muncul, hapus centang pada kotak dari bulan Oktober, lalu klik "OK".

Dengan demikian, Anda dapat menambahkan dan menghapus elemen dari filter, menghasilkan analisis yang benar-benar relevan dan perlu. Program ini secara otomatis menghitung indikator untuk bulan-bulan yang Anda minati, secara visual menunjukkan dinamika perubahan indikator.

Kami menghitung ramalannya

Mari kita coba prediksi hasil penjualan untuk beberapa waktu ke depan. Untuk melakukan ini, pertama-tama Anda harus menonaktifkan total sehingga mereka tidak mengenalkan ketidakakuratan ke dalam perkiraan yang dihasilkan.

Untuk melakukan ini, letakkan kursor di kolom "Total total", lalu klik tombol "Delete the grand total". Setelah itu, kita melakukan update otomatis dari spreadsheet Excel dengan cara yang dijelaskan di atas (klik pada tombol mouse sebelah kanan, pilih "Update").

Akibatnya, data ini lenyap dari tabel ringkasan kami. Untuk membuat representasi visual dari perkiraan penjualan, kami menempatkan kursor pada tahun yang akan kami tolak, lalu klik tombol "Graph Forecast Model" dengan tombol kiri mouse.

Setelah itu, jadwal yang indah dan informatif muncul, di mana hasil prediksi aktivitas pembelian disajikan dalam bentuk visual berdasarkan perkiraan yang Anda minati. Ini akan menghemat energi dan menghemat uang, yang bisa digunakan untuk membeli barang yang benar-benar dibutuhkan.

Kami menggunakan sumber data lain untuk membuat tabel ringkasan

Sudah waktunya untuk mempertimbangkan pelajaran Excel yang lebih kompleks. Tabel ringkasan dapat dibuat berdasarkan dokumen yang telah diedit oleh pengguna lain.

Fungsi "Merge" akan membantu kita dalam hal ini. Program ini memungkinkan Anda menggabungkan secara otomatis teks dokumen dan alamat, nama, atau informasi penting lainnya yang diperlukan untuk membuat prakiraan hasil personalisasi, yang dalam kasus kami diperlukan.

Fungsi penggabungan pada versi "Office" yang lebih baru tetap tidak berubah dibandingkan dengan Office 2003. Namun, permintaan ke database untuk memilih sumber data dapat dilakukan dengan menggunakan Microsoft Query. Untuk mendapatkan data dari Excel PivotTable, Anda memerlukan dua objek:

• Dokumen utama berisi informasi yang Anda butuhkan, serta kolom yang menunjukkan Excel, data apa yang harus dimasukkan ke dalam dokumen. Tabel utama adalah dokumen prosesor tabel standar, namun Anda harus menentukan di kotak dialog Mail Merge Helper yang seharusnya digunakan sebagai tabel utama.
• Sumber data berisi informasi yang diperlukan untuk menyusun perkiraan. Anda bisa membuat tabel baru seperti yang kami sebutkan di atas.

Ketika proses pembuatan dokumen ringkasan terjadi, program membuat satu salinan untuk setiap catatan yang terdapat dalam file sumber data. File-file ini dapat ditampilkan sebagai bagian terpisah dari dokumen baru atau digunakan sebagai bagian dari tabel pivot Anda.

Cukup klik di lapangan yang Anda butuhkan dengan tombol kiri mouse, dan Anda bisa melihat data sumber, bukan nama field yang muncul di dokumen utama. Bidang dapat disisipkan di manapun dalam dokumen.

Kategori yang digunakan

Juga di sini Anda dapat menggunakan kategori ASK, yang berarti "permintaan", dan FILLIN, yang berarti "isi", NEXT dan NEXTIF, ELSE. Anda juga dapat mengkonfigurasi parameter item perkiraan yang akan ditampilkan di tabel selesai. Anda dapat menemukan catatan yang diperlukan dengan menggunakan filter khusus atau dengan hanya menyortir data, seperti yang telah disebutkan di atas.

Bila Anda siap membuat dokumen master, kotak dialog Merge memberi pengguna kemampuan untuk mengidentifikasi dan memperbaiki kesalahan individu sebelum hal itu terjadi. Saat semuanya dicentang, Anda dapat dengan aman menekan tombol "Merge". Jika penggabungan dilakukan melalui e-mail, Anda akan diminta untuk menginstal program surat (jika Anda belum menginstalnya).

Jika terjadi kesalahan selama proses penggabungan, ini menunjukkan bidang yang salah dalam dokumen asing, atau data yang digunakan disajikan dalam format yang tidak didukung. Oleh karena itu, prosedur ini harus ditinjau kembali.

Secara umum, Excel (bekerja dengan tabel pivot yang sedang kita pertimbangkan) memungkinkan tidak hanya melakukan analisis daya beli yang relatif rumit, tetapi juga untuk menciptakan prakiraan berdasarkan pada mereka.