Tabel pivot bagus untuk semua orang – mereka menghitung dengan cepat, dan dikonfigurasi secara fleksibel, dan desainnya dapat dililitkan ke dalamnya dengan elegan, jika diperlukan. Tetapi ada juga beberapa lalat di salep, khususnya, ketidakmampuan untuk membuat ringkasan, di mana area nilai tidak boleh berisi angka, tetapi teks.
Mari kita coba untuk mengatasi batasan ini dan menghasilkan "beberapa kruk" dalam situasi yang sama.
Misalkan perusahaan kami mengangkut produknya dalam wadah ke beberapa kota di Negara Kami dan Kazakhstan. Kontainer dikirim tidak lebih dari sebulan sekali. Setiap wadah memiliki nomor alfanumerik. Sebagai data awal, ada tabel daftar pengiriman standar, dari mana Anda perlu membuat semacam ringkasan untuk melihat dengan jelas jumlah kontainer yang dikirim ke setiap kota dan setiap bulan:
Untuk memudahkan, mari kita buat tabel dengan data awal “pintar” terlebih dahulu menggunakan perintah Beranda – Format sebagai tabel (Beranda — Format sebagai Tabel) dan beri dia nama Pengiriman tab Pembina (Rancangan). Di masa depan, ini akan menyederhanakan hidup, karena. akan memungkinkan untuk menggunakan nama tabel dan kolomnya secara langsung dalam rumus.
Metode 1. Yang paling mudah – gunakan Power Query
Power Query adalah alat yang sangat kuat untuk memuat dan mengubah data di Excel. Add-in ini telah dibangun ke dalam Excel secara default sejak 2016. Jika Anda memiliki Excel 2010 atau 2013, Anda dapat mengunduh dan menginstalnya secara terpisah (sepenuhnya gratis).
Seluruh proses, untuk kejelasan, saya menganalisis langkah demi langkah dalam video berikut:
Jika tidak mungkin menggunakan Power Query, maka Anda bisa menggunakan cara lain – melalui tabel pivot atau rumus.
Metode 2. Ringkasan tambahan
Mari tambahkan satu kolom lagi ke tabel asli kita, di mana menggunakan rumus sederhana kita menghitung jumlah setiap baris dalam tabel:
Jelas, -1 diperlukan, karena kami memiliki header satu baris di tabel kami. Jika tabel Anda tidak berada di awal lembar, maka Anda dapat menggunakan rumus yang sedikit lebih kompleks, tetapi universal yang menghitung perbedaan jumlah baris saat ini dan header tabel:
Sekarang, dengan cara standar, kami akan membuat tabel pivot dari jenis yang diinginkan berdasarkan data kami, tetapi di bidang nilai kami akan menghapus bidang Nomor baris alih-alih apa yang kita inginkan wadah:
Karena kami tidak memiliki beberapa kontainer di kota yang sama pada bulan yang sama, ringkasan kami sebenarnya tidak akan memberikan jumlah, tetapi nomor baris kontainer yang kami butuhkan.
Selain itu, Anda dapat mematikan grand dan subtotal pada tab Konstruktor – Total umum и Subtotal (Desain — Total Besar, Subtotal) dan di tempat yang sama, alihkan ringkasan ke tata letak tabel yang lebih nyaman dengan tombol Laporan mockup (Tata Letak Laporan).
Jadi, kita sudah setengah jalan menuju hasil: kita memiliki tabel di mana, di persimpangan kota dan bulan, ada nomor baris di tabel sumber, di mana kode wadah yang kita butuhkan terletak.
Sekarang mari salin ringkasan (ke lembar yang sama atau lainnya) dan tempel sebagai nilai, lalu masukkan rumus kita ke area nilai, yang akan mengekstrak kode wadah dengan nomor baris yang ditemukan di ringkasan:
fungsi IF (JIKA), dalam hal ini, memeriksa apakah sel berikutnya dalam ringkasan tidak kosong. Jika kosong, maka keluarkan string teks kosong “”, yaitu biarkan sel kosong. Jika tidak kosong, maka ekstrak dari kolom Wadah tabel sumber Pengiriman konten sel dengan nomor baris menggunakan fungsi INDEKS (INDEKS).
Mungkin satu-satunya hal yang tidak terlalu jelas di sini adalah kata ganda Wadah dalam rumus. Bentuk tulisan yang aneh:
Persediaan[[Wadah]:[Wadah]]
… hanya diperlukan untuk referensi kolom Wadah adalah mutlak (seperti referensi dengan tanda $ untuk tabel "non-pintar" biasa) dan tidak tergelincir ke kolom tetangga saat menyalin rumus kita ke kanan.
Di masa depan, saat mengubah data di tabel sumber Pengiriman, kita harus ingat untuk memperbarui ringkasan tambahan kita dengan nomor baris dengan mengklik kanan padanya dan memilih perintah Perbarui & Simpan (Menyegarkan).
Metode 3. Rumus
Metode ini tidak memerlukan pembuatan tabel pivot perantara dan pembaruan manual, tetapi menggunakan "senjata berat" Excel – fungsinya RINGKASAN (SUMIF). Alih-alih mencari nomor baris dalam ringkasan, Anda dapat menghitungnya menggunakan rumus ini:
Dengan beberapa bulkiness eksternal, pada kenyataannya, ini adalah kasus penggunaan standar untuk fungsi penjumlahan selektif RINGKASANA yang menjumlahkan nomor baris untuk kota dan bulan tertentu. Sekali lagi, karena kami tidak memiliki beberapa kontainer di kota yang sama pada bulan yang sama, fungsi kami sebenarnya tidak akan memberikan jumlah, tetapi nomor baris itu sendiri. Dan kemudian fungsinya sudah familiar dari metode sebelumnya INDEKS Anda juga dapat mengekstrak kode wadah:
Tentu saja, dalam hal ini, Anda tidak perlu lagi memikirkan untuk memperbarui ringkasan, tetapi pada tabel besar, fungsinya SUMMELI bisa terasa lambat. Kemudian Anda harus mematikan pembaruan formula secara otomatis, atau menggunakan metode pertama – tabel pivot.
Jika tampilan ringkasan tidak terlalu cocok untuk laporan Anda, maka Anda dapat mengekstrak nomor baris darinya ke tabel akhir tidak secara langsung, seperti yang kami lakukan, tetapi menggunakan fungsi DAPATKAN.PIVOT.TABLE.DATA (DAPATKAN.PIVOT.DATA). Cara melakukannya dapat ditemukan di sini.
- Cara membuat laporan menggunakan tabel pivot
- Cara mengatur perhitungan di tabel pivot
- Penghitungan selektif dengan SUMIFS, COUNTIFS, dll.