Konten
Kami memiliki dua tabel (misalnya, daftar harga versi lama dan baru), yang perlu kami bandingkan dan temukan perbedaannya dengan cepat:
Segera jelas bahwa sesuatu telah ditambahkan ke daftar harga baru (kurma, bawang putih ...), sesuatu telah hilang (blackberry, raspberry ...), harga telah berubah untuk beberapa barang (ara, melon ...). Anda perlu menemukan dan menampilkan semua perubahan ini dengan cepat.
Untuk tugas apa pun di Excel, hampir selalu ada lebih dari satu solusi (biasanya 4-5). Untuk masalah kita, banyak pendekatan yang berbeda dapat digunakan:
- fungsi VPR (VLOOKUP) — cari nama produk dari daftar harga baru di yang lama dan tampilkan harga lama di sebelah yang baru, lalu lihat perbedaannya
- gabungkan dua daftar menjadi satu dan kemudian buat tabel pivot berdasarkan itu, di mana perbedaannya akan terlihat jelas
- gunakan Add-in Power Query untuk Excel
Mari kita ambil semuanya secara berurutan.
Metode 1. Membandingkan tabel dengan fungsi VLOOKUP
Jika Anda benar-benar tidak terbiasa dengan fitur luar biasa ini, pertama-tama lihat di sini dan baca atau tonton tutorial video tentangnya – selamatkan diri Anda beberapa tahun.
Biasanya, fungsi ini digunakan untuk menarik data dari satu tabel ke tabel lainnya dengan mencocokkan beberapa parameter umum. Dalam hal ini, kami akan menggunakannya untuk mendorong harga lama ke harga baru:
Produk-produk tersebut, yang menjadi penyebab kesalahan #N/A, tidak ada dalam daftar lama, yaitu ditambahkan. Perubahan harga juga terlihat jelas.
Pro metode ini: sederhana dan jelas, "genre klasik", seperti yang mereka katakan. Bekerja di versi Excel apa pun.
Kekurangan juga ada. Untuk mencari produk yang ditambahkan ke daftar harga baru, Anda harus melakukan prosedur yang sama dengan arah yang berlawanan, yaitu menaikkan harga baru ke harga lama dengan bantuan VLOOKUP. Jika ukuran tabel berubah besok, maka formula harus disesuaikan. Nah, dan di meja yang sangat besar (> 100 ribu baris), semua kebahagiaan ini akan melambat.
Metode 2: Membandingkan tabel menggunakan pivot
Mari kita salin tabel kita satu di bawah yang lain, tambahkan kolom dengan nama daftar harga, sehingga nanti Anda bisa mengerti dari daftar mana baris mana:
Sekarang, berdasarkan tabel yang dibuat, kami akan membuat ringkasan melalui Sisipkan – PivotTable (Sisipkan — Tabel Pivot). Ayo lempar lapangan Produk ke area garis, bidang Harga ke area kolom dan bidang Цena ke dalam kisaran:
Seperti yang Anda lihat, tabel pivot akan secara otomatis menghasilkan daftar umum semua produk dari daftar harga lama dan baru (tidak ada pengulangan!) dan mengurutkan produk berdasarkan abjad. Anda dapat dengan jelas melihat produk yang ditambahkan (mereka tidak memiliki harga lama), produk yang dihapus (mereka tidak memiliki harga baru) dan perubahan harga, jika ada.
Total keseluruhan dalam tabel seperti itu tidak masuk akal, dan mereka dapat dinonaktifkan di tab Konstruktor – Total keseluruhan – Nonaktifkan untuk baris dan kolom (Desain — Total Besar).
Jika harga berubah (tetapi bukan jumlah barang!), maka cukup memperbarui ringkasan yang dibuat dengan mengklik kanan di atasnya – menyegarkan.
Pro: Pendekatan ini adalah urutan besarnya lebih cepat dengan tabel besar dari VLOOKUP.
Kekurangan: Anda perlu menyalin data di bawah satu sama lain secara manual dan menambahkan kolom dengan nama daftar harga. Jika ukuran tabel berubah, maka Anda harus melakukan semuanya dari awal lagi.
Metode 3: Membandingkan tabel dengan Power Query
Power Query adalah add-in gratis untuk Microsoft Excel yang memungkinkan Anda memuat data ke Excel dari hampir semua sumber, lalu mengubah data ini dengan cara apa pun yang diinginkan. Di Excel 2016, add-in ini sudah terpasang secara default di tab Data (Data), dan untuk Excel 2010-2013 Anda perlu mengunduhnya secara terpisah dari situs web Microsoft dan menginstalnya – dapatkan tab baru Kueri Daya.
Sebelum memuat daftar harga kami ke Power Query, daftar harga tersebut harus dikonversi terlebih dahulu menjadi tabel cerdas. Untuk melakukan ini, pilih rentang dengan data dan tekan kombinasi pada keyboard Ctrl+T atau pilih tab di pita Beranda – Format sebagai tabel (Beranda — Format sebagai Tabel). Nama tabel yang dibuat dapat dikoreksi pada tab Pembina (Saya akan meninggalkan standar Tabel 1 и Tabel 2, yang diperoleh secara default).
Muat harga lama di Power Query menggunakan tombol Dari Tabel/Rentang (Dari Tabel/Rentang) dari tab Data (Tanggal) atau dari tab Kueri Daya (tergantung pada versi Excel). Setelah memuat, kami akan kembali ke Excel dari Power Query dengan perintah Tutup dan muat – Tutup dan muat di… (Tutup & Muat — Tutup & Muat Ke…):
… dan di jendela yang muncul kemudian pilih Buat saja koneksi (Koneksi Saja).
Ulangi hal yang sama dengan daftar harga baru.
Sekarang mari kita buat kueri ketiga yang akan menggabungkan dan membandingkan data dari dua kueri sebelumnya. Untuk melakukan ini, pilih di Excel pada tab Data – Dapatkan Data – Gabungkan Permintaan – Gabungkan (Data — Dapatkan Data — Gabungkan Kueri — Gabungkan) atau tekan tombol Menggabungkan (Menggabungkan) tab Kueri Daya.
Di jendela gabung, pilih tabel kami di daftar drop-down, pilih kolom dengan nama barang di dalamnya, dan di bagian bawah, atur metode gabung – Eksternal lengkap (Luar Penuh):
Setelah mengklik OK tabel tiga kolom akan muncul, di mana di kolom ketiga Anda perlu memperluas konten tabel bersarang menggunakan panah ganda di header:
Hasilnya, kami mendapatkan penggabungan data dari kedua tabel:
Lebih baik, tentu saja, untuk mengganti nama kolom di header dengan mengklik dua kali pada yang lebih mudah dipahami:
Dan sekarang yang paling menarik. Pergi ke tab Tambahkan kolom (Tambahkan Kolom) dan klik tombol kolom bersyarat (Kolom Bersyarat). Dan kemudian di jendela yang terbuka, masukkan beberapa kondisi pengujian dengan nilai output yang sesuai:
Tetap mengklik OK dan unggah laporan yang dihasilkan ke Excel menggunakan tombol yang sama tutup dan unduh (Tutup & Muat) tab Beranda (Rumah):
Kecantikan
Selain itu, jika ada perubahan dalam daftar harga di masa mendatang (baris ditambahkan atau dihapus, harga berubah, dll.), maka cukup memperbarui permintaan kami dengan pintasan keyboard Ctrl+lain+F5 atau dengan tombol Segarkan semua (Segarkan Semua) tab Data (Tanggal).
Pro: Mungkin cara yang paling indah dan nyaman dari semuanya. Bekerja cerdas dengan meja besar. Tidak memerlukan pengeditan manual saat mengubah ukuran tabel.
Kekurangan: Memerlukan add-in Power Query (di Excel 2010-2013) atau Excel 2016 untuk diinstal. Nama kolom dalam data sumber tidak boleh diubah, jika tidak kita akan mendapatkan kesalahan "Kolom ini dan itu tidak ditemukan!" saat mencoba memperbarui kueri.
- Cara mengumpulkan data dari semua file Excel di folder tertentu menggunakan Power Query
- Bagaimana menemukan kecocokan antara dua daftar di Excel
- Menggabungkan dua daftar tanpa duplikat