Contents [show]
Jadual pangsi adalah baik untuk semua orang – ia mengira dengan cepat, dan dikonfigurasikan secara fleksibel, dan reka bentuk boleh dimasukkan ke dalamnya dengan elegan, jika diperlukan. Tetapi terdapat juga beberapa lalat dalam salap, khususnya, ketidakupayaan untuk membuat ringkasan, di mana kawasan nilai tidak harus mengandungi nombor, tetapi teks.
Mari kita cuba mengatasi had ini dan tampil dengan "beberapa tongkat" dalam situasi yang sama.
Katakan syarikat kami mengangkut produknya dalam bekas ke beberapa bandar di Negara Kita dan Kazakhstan. Bekas dihantar tidak lebih daripada sekali sebulan. Setiap bekas mempunyai nombor abjad angka. Sebagai data awal, terdapat penghantaran penyenaraian jadual standard, yang mana anda perlu membuat beberapa jenis ringkasan untuk melihat dengan jelas bilangan kontena yang dihantar ke setiap bandar dan setiap bulan:
Untuk kemudahan, mari buat jadual dengan data awal "pintar" terlebih dahulu menggunakan arahan Laman Utama – Format sebagai jadual (Laman Utama — Format sebagai Jadual) dan beri dia nama Penghantaran tab Pembina (Reka Bentuk). Pada masa hadapan, ini akan memudahkan kehidupan, kerana. nama jadual dan lajurnya boleh digunakan secara langsung dalam formula.
Kaedah 1. Yang paling mudah – gunakan Power Query
Power Query ialah alat yang sangat berkuasa untuk memuatkan dan mengubah data dalam Excel. Alat tambah ini telah dibina ke dalam Excel secara lalai sejak 2016. Jika anda mempunyai Excel 2010 atau 2013, anda boleh memuat turun dan memasangnya secara berasingan (percuma sepenuhnya).
Seluruh proses, untuk kejelasan, saya menganalisis langkah demi langkah dalam video berikut:
Jika tidak boleh menggunakan Power Query, maka anda boleh menggunakan cara lain – melalui jadual pangsi atau formula.
Kaedah 2. Ringkasan bantu
Mari tambah satu lagi lajur pada jadual asal kami, dengan menggunakan formula mudah kami mengira bilangan setiap baris dalam jadual:
Jelas sekali, -1 diperlukan, kerana kami mempunyai pengepala satu baris dalam jadual kami. Jika jadual anda bukan pada permulaan helaian, maka anda boleh menggunakan formula yang lebih kompleks tetapi universal yang mengira perbezaan dalam nombor baris semasa dan pengepala jadual:
Sekarang, secara standard, kami akan membina jadual pangsi jenis yang dikehendaki berdasarkan data kami, tetapi dalam medan nilai kami akan menggugurkan medan Nombor garisan bukannya apa yang kita mahu bekas:
Memandangkan kami tidak mempunyai beberapa bekas di bandar yang sama pada bulan yang sama, ringkasan kami, sebenarnya, tidak akan memberikan jumlahnya, tetapi nombor baris kontena yang kami perlukan.
Selain itu, anda boleh mematikan jumlah besar dan kecil pada tab Pembina – Jumlah am и Subtotal (Reka Bentuk — Jumlah Besar, Subjumlah) dan di tempat yang sama tukar ringkasan kepada susun atur meja yang lebih mudah dengan butang Laporkan mockup (Reka Letak Laporan).
Oleh itu, kita sudah separuh jalan kepada keputusan: kita mempunyai jadual di mana, di persimpangan bandar dan bulan, terdapat nombor baris dalam jadual sumber, di mana kod kontena yang kita perlukan terletak.
Sekarang mari salin ringkasan (ke helaian yang sama atau yang lain) dan tampalkannya sebagai nilai, dan kemudian masukkan formula kami ke dalam kawasan nilai, yang akan mengekstrak kod bekas dengan nombor baris yang terdapat dalam ringkasan:
fungsi IF (JIKA), dalam kes ini, semak bahawa sel seterusnya dalam ringkasan tidak kosong. Jika kosong, kemudian keluarkan rentetan teks kosong "", iaitu biarkan sel kosong. Jika tidak kosong, kemudian ekstrak dari lajur Bekas jadual sumber Penghantaran kandungan sel mengikut nombor baris menggunakan fungsi INDEKS (INDEX).
Mungkin satu-satunya perkara yang tidak begitu jelas di sini ialah kata ganda Bekas dalam formula. Bentuk penulisan yang pelik:
Bekalan[[Bekas]:[Bekas]]
… hanya diperlukan untuk merujuk lajur Bekas adalah mutlak (seperti rujukan dengan tanda $ untuk jadual "tidak pintar" biasa) dan tidak tergelincir ke lajur jiran apabila menyalin formula kami ke kanan.
Pada masa hadapan, apabila menukar data dalam jadual sumber Penghantaran, kita mesti ingat untuk mengemas kini ringkasan tambahan kami dengan nombor baris dengan mengklik kanan padanya dan memilih arahan Kemas kini & Simpan (Segarkan semula).
Kaedah 3. Formula
Kaedah ini tidak memerlukan penciptaan jadual pangsi perantaraan dan pengemaskinian manual, tetapi menggunakan "senjata berat" Excel - fungsi SUMMESLIMN (SUMIFS). Daripada mencari nombor baris dalam ringkasan, anda boleh mengiranya menggunakan formula ini:
Dengan beberapa keterbukaan luaran, sebenarnya, ini ialah kes penggunaan standard untuk fungsi penjumlahan terpilih SUMMESLIMNA yang menjumlahkan nombor baris untuk bandar dan bulan tertentu. Sekali lagi, kerana kami tidak mempunyai beberapa kontena di bandar yang sama pada bulan yang sama, fungsi kami, sebenarnya, tidak akan memberikan jumlahnya, tetapi nombor talian itu sendiri. Dan kemudian fungsi sudah biasa dari kaedah sebelumnya INDEKS Anda juga boleh mengekstrak kod bekas:
Sudah tentu, dalam kes ini, anda tidak perlu lagi berfikir tentang mengemas kini ringkasan, tetapi pada jadual besar, fungsi SUMMESLI boleh menjadi perlahan. Kemudian anda perlu mematikan pengemaskinian automatik formula, atau gunakan kaedah pertama - jadual pangsi.
Jika penampilan ringkasan tidak begitu sesuai untuk laporan anda, maka anda boleh mengekstrak nombor baris daripadanya ke dalam jadual akhir bukan secara langsung, seperti yang kami lakukan, tetapi menggunakan fungsi DAPATKAN.PIVOT.TABLE.DATA (DAPATKAN.PIVOT.DATA). Bagaimana untuk melakukan ini boleh didapati di sini.
- Cara membuat laporan menggunakan jadual pangsi
- Bagaimana untuk menyediakan pengiraan dalam jadual pangsi
- Pengiraan terpilih dengan SUMIFS, COUNTIFS, dsb.