Contents [show]
Kami mempunyai dua jadual (contohnya, versi lama dan baharu senarai harga), yang perlu kami bandingkan dan cari perbezaan dengan cepat:
Ia serta-merta jelas bahawa sesuatu telah ditambahkan pada senarai harga baharu (kurma, bawang putih …), sesuatu telah hilang (beri hitam, raspberi …), harga telah berubah untuk beberapa barangan (buah tin, tembikai …). Anda perlu mencari dan memaparkan semua perubahan ini dengan cepat.
Untuk sebarang tugasan dalam Excel, hampir selalu terdapat lebih daripada satu penyelesaian (biasanya 4-5). Untuk masalah kami, pelbagai pendekatan boleh digunakan:
- fungsi VPR (TAMPILAN) — cari nama produk daripada senarai harga baharu dalam senarai lama dan paparkan harga lama bersebelahan dengan yang baharu, kemudian lihat perbezaannya
- gabungkan dua senarai menjadi satu dan kemudian bina jadual pangsi berdasarkannya, di mana perbezaannya akan dapat dilihat dengan jelas
- gunakan Power Query Add-in untuk Excel
Mari kita ambil semuanya mengikut urutan.
Kaedah 1. Membandingkan jadual dengan fungsi VLOOKUP
Jika anda benar-benar tidak biasa dengan ciri hebat ini, mula-mula lihat di sini dan baca atau tonton tutorial video mengenainya - selamatkan diri anda beberapa tahun hidup.
Biasanya, fungsi ini digunakan untuk menarik data dari satu jadual ke jadual lain dengan memadankan beberapa parameter biasa. Dalam kes ini, kami akan menggunakannya untuk menolak harga lama ke dalam harga baharu:
Produk tersebut, yang menunjukkan ralat #N/A, tiada dalam senarai lama, iaitu telah ditambah. Perubahan harga juga jelas kelihatan.
Kelebihan kaedah ini: mudah dan jelas, "genre klasik", seperti yang mereka katakan. Berfungsi dalam mana-mana versi Excel.
Kekurangan juga ada. Untuk mencari produk yang ditambahkan pada senarai harga baharu, anda perlu melakukan prosedur yang sama dalam arah yang bertentangan, iaitu menarik harga baharu kepada harga lama dengan bantuan VLOOKUP. Jika saiz jadual berubah esok, maka formula perlu dilaraskan. Nah, dan pada meja yang sangat besar (> 100 ribu baris), semua kebahagiaan ini akan perlahan.
Kaedah 2: Membandingkan jadual menggunakan pangsi
Mari salin jadual kami satu di bawah yang lain, menambah lajur dengan nama senarai harga, supaya kemudian anda boleh memahami dari senarai mana baris:
Sekarang, berdasarkan jadual yang dibuat, kami akan membuat ringkasan melalui Sisipkan – Jadual Pangsi (Sisipkan — Jadual Pangsi). Jom baling padang Produk ke kawasan garisan, medan Harga ke kawasan lajur dan medan ЦEna ke dalam julat:
Seperti yang anda lihat, jadual pangsi secara automatik akan menjana senarai umum semua produk daripada senarai harga lama dan baharu (tiada ulangan!) dan mengisih produk mengikut abjad. Anda boleh melihat dengan jelas produk yang ditambah (mereka tidak mempunyai harga lama), produk yang dialih keluar (mereka tidak mempunyai harga baharu) dan perubahan harga, jika ada.
Jumlah besar dalam jadual sedemikian tidak masuk akal, dan ia boleh dilumpuhkan pada tab Pembina – Jumlah besar – Lumpuhkan untuk baris dan lajur (Reka Bentuk — Jumlah Besar).
Jika harga berubah (tetapi bukan kuantiti barang!), maka cukup sekadar mengemas kini ringkasan yang dibuat dengan mengklik kanan padanya – Refresh.
Kelebihan: Pendekatan ini adalah susunan magnitud yang lebih cepat dengan jadual besar daripada VLOOKUP.
Kekurangan: anda perlu menyalin data secara manual di bawah satu sama lain dan menambah lajur dengan nama senarai harga. Jika saiz jadual berubah, maka anda perlu melakukan semuanya sekali lagi.
Kaedah 3: Membandingkan jadual dengan Power Query
Power Query ialah tambahan percuma untuk Microsoft Excel yang membolehkan anda memuatkan data ke dalam Excel daripada hampir mana-mana sumber dan kemudian mengubah data ini dalam sebarang cara yang diingini. Dalam Excel 2016, alat tambah ini sudah terbina dalam secara lalai pada tab Tarikh (Data), dan untuk Excel 2010-2013 anda perlu memuat turunnya secara berasingan daripada tapak web Microsoft dan memasangnya – dapatkan tab baharu Kuasa Kuasa.
Sebelum memuatkan senarai harga kami ke dalam Power Query, senarai itu mesti ditukar menjadi jadual pintar terlebih dahulu. Untuk melakukan ini, pilih julat dengan data dan tekan gabungan pada papan kekunci Ctrl+T atau pilih tab pada reben Laman Utama – Format sebagai jadual (Laman Utama — Format sebagai Jadual). Nama jadual yang dibuat boleh diperbetulkan pada tab Pembina (Saya akan meninggalkan standard Jadual 1 и Jadual 2, yang diperoleh secara lalai).
Muatkan harga lama dalam Power Query menggunakan butang Daripada Jadual/Julat (Dari Jadual/Julat) daripada tab Tarikh (Tarikh) atau daripada tab Kuasa Kuasa (bergantung pada versi Excel). Selepas memuatkan, kami akan kembali ke Excel daripada Power Query dengan arahan Tutup dan muat – Tutup dan muatkan… (Tutup & Muatkan — Tutup & Muat Ke…):
… dan dalam tetingkap yang muncul kemudian pilih Buat sambungan sahaja (Sambungan Sahaja).
Ulang perkara yang sama dengan senarai harga baharu.
Sekarang mari kita buat pertanyaan ketiga yang akan menggabungkan dan membandingkan data daripada dua sebelumnya. Untuk melakukan ini, pilih dalam Excel pada tab Data – Dapatkan Data – Gabungkan Permintaan – Gabungkan (Data — Dapatkan Data — Gabungkan Pertanyaan — Gabung) atau tekan butang Gabungkan (Gabung) tab Kuasa Kuasa.
Dalam tetingkap gabungan, pilih jadual kami dalam senarai juntai bawah, pilih lajur dengan nama barang di dalamnya, dan di bahagian bawah, tetapkan kaedah gabungan - Luaran lengkap (Luar Penuh):
Selepas mengklik OK jadual tiga lajur akan muncul, di mana dalam lajur ketiga anda perlu mengembangkan kandungan jadual bersarang menggunakan anak panah berganda dalam pengepala:
Akibatnya, kami mendapat penggabungan data daripada kedua-dua jadual:
Sudah tentu, lebih baik untuk menamakan semula nama lajur dalam pengepala dengan mengklik dua kali pada yang lebih mudah difahami:
Dan kini yang paling menarik. Pergi ke tab Tambah lajur (Tambah Lajur) dan klik pada butang Lajur bersyarat (Lajur Bersyarat). Dan kemudian dalam tetingkap yang terbuka, masukkan beberapa syarat ujian dengan nilai output yang sepadan:
Ia kekal untuk mengklik OK dan muat naik laporan yang terhasil ke Excel menggunakan butang yang sama tutup dan muat turun (Tutup & Muatkan) tab Laman Utama (Rumah):
Kecantikan.
Lebih-lebih lagi, jika apa-apa perubahan berlaku dalam senarai harga pada masa hadapan (baris ditambah atau dipadamkan, harga berubah, dsb.), maka sudah memadai untuk mengemas kini permintaan kami dengan pintasan papan kekunci Ctrl+Alt+F5 atau dengan butang Muat semula semua (Segarkan Semua) tab Tarikh (Tarikh).
Kelebihan: Mungkin cara yang paling indah dan mudah. Berfungsi dengan bijak dengan meja besar. Tidak memerlukan pengeditan manual apabila mengubah saiz jadual.
Kekurangan: Memerlukan tambahan Power Query (dalam Excel 2010-2013) atau Excel 2016 untuk dipasang. Nama lajur dalam data sumber tidak boleh ditukar, jika tidak, kita akan mendapat ralat "Lajur itu dan itu tidak ditemui!" apabila cuba mengemas kini pertanyaan.
- Cara mengumpul data daripada semua fail Excel dalam folder tertentu menggunakan Power Query
- Bagaimana untuk mencari padanan antara dua senarai dalam Excel
- Menggabungkan dua senarai tanpa pendua