Ngabandingkeun dua tabel

Simkuring gaduh dua tabel (contona, versi heubeul jeung anyar tina daptar harga), nu urang kudu ngabandingkeun sarta gancang manggihan bédana:

Langsung écés yén aya anu parantos ditambah kana daptar harga énggal (tanggal, bawang bodas ...), aya anu ngaleungit (blackberries, raspberries ...), harga parantos robih pikeun sababaraha barang (buah ara, melon ...). Anjeun kudu gancang manggihan tur mintonkeun sadaya parobahan ieu.

Pikeun tugas naon waé dina Excel, ampir sok aya langkung ti hiji solusi (biasana 4-5). Pikeun masalah urang, loba pendekatan béda bisa dipaké:

• fungsi VPR (VLOOKUP) - néangan ngaran produk tina daptar harga anyar dina nu heubeul tur mintonkeun harga heubeul gigireun nu anyar, lajeng nyekel bédana
• ngagabungkeun dua daptar kana hiji teras ngawangun tabel pangsi dumasar kana éta, dimana bédana bakal katingali jelas
• ngagunakeun Power Query Add-in pikeun Excel

Hayu urang nyandak aranjeunna sadayana dina urutan.

Métode 1. Ngabandingkeun tabel kalawan fungsi VLOOKUP

Upami anjeun teu biasa sareng fitur anu saé ieu, teras tingali heula di dieu sareng baca atanapi nonton tutorial pidéo ngeunaan éta - nyalametkeun diri anjeun sababaraha taun hirup.

Ilaharna, pungsi ieu dipaké pikeun narik data tina hiji méja ka nu sejen ku cocog sababaraha parameter umum. Dina hal ieu, urang bakal nganggo éta pikeun nyorong harga lami kana harga énggal:

Éta produk, ngalawan nu #N / ​​A kasalahan tétéla, teu aya dina daptar heubeul, nyaéta ditambahkeun. Parobahan harga ogé jelas katingali.

Naros metoda ieu: basajan tur jelas, "klasik tina genre", sabab nyebutkeun. Gawéna dina sagala vérsi Excel.

kontra oge aya. Pikeun milarian produk anu ditambahkeun kana daptar harga anyar, anjeun kedah ngalakukeun prosedur anu sami dina arah anu sabalikna, nyaéta narik harga énggal ka harga anu lami kalayan bantosan VLOOKUP. Upami ukuran tabel énjing robih, rumusna kedah disaluyukeun. Nya, sareng dina tabel anu ageung (> 100 rébu jajar), sadayana kabagjaan ieu bakal ngalambatkeun.

Métode 2: Ngabandingkeun tabél nganggo pangsi

Hayu urang nyalin tabel urang hiji di handapeun sejen, nambahkeun kolom kalawan ngaran daptar harga, ku kituna engké anjeun bisa ngarti tina daptar nu baris:

Ayeuna, dumasar kana tabel dijieun, urang bakal nyieun kasimpulan ngaliwatan Selapkeun - PivotTable (Selapkeun - Pivot Table). Hayu urang ngalungkeun sawah barang ka wewengkon garis, sawah harga ka aréa kolom jeung widang Цena kana rentang:

Sakumaha anjeun tiasa tingali, tabel pangsi bakal otomatis ngahasilkeun daptar umum sadaya produk tina daptar harga heubeul jeung anyar (euweuh pengulangan!) Jeung nyortir produk alphabetically. Anjeun tiasa jelas ningali produk ditambahkeun (aranjeunna teu boga harga heubeul), produk dihapus (aranjeunna teu boga harga anyar) jeung parobahan harga, lamun aya.

Grand totalna dina tabel misalna teu make akal pikiran, sarta aranjeunna bisa ditumpurkeun dina tab Konstruktor - Jumlahna ageung - Nonaktipkeun pikeun baris sareng kolom (Desain - Grand Total).

Upami hargana robih (tapi sanés kuantitas barang!), maka cukup ngan ukur ngapdet kasimpulan anu diciptakeun ku ngaklik katuhu - nyegerkeun.

Naros: Pendekatan ieu mangrupa urutan gedena gancang kalayan tabel badag ti VLOOKUP. 

kontra: anjeun kedah nyalin data sacara manual dina silih sareng nambihan kolom kalayan nami daptar harga. Upami ukuran tabel robih, maka anjeun kedah ngalakukeun sadayana deui.

Métode 3: Ngabandingkeun tabel sareng Power Query

Power Query mangrupikeun tambihan gratis pikeun Microsoft Excel anu ngamungkinkeun anjeun ngamuat data kana Excel tina ampir sadaya sumber teras ngarobih data ieu ku cara anu dipikahoyong. Dina Excel 2016, tambihan ieu parantos diwangun sacara standar dina tab data (Data), sareng pikeun Excel 2010-2013 anjeun kedah unduh nyalira tina situs wéb Microsoft sareng pasang - kéngingkeun tab énggal Patarosan Kakuatan.

Sateuacan ngamuat daptar harga kami kana Power Query, aranjeunna kedah dirobih janten tabel pinter. Jang ngalampahkeun ieu, pilih rentang sareng data sareng pencét kombinasi dina kibor Ctrl+T atawa pilih tab dina pita Imah - Format salaku méja (Imah - Format salaku Méja). Ngaran-ngaran tabel anu didamel tiasa dilereskeun dina tab Constructor (Kuring bakal ninggalkeun standar table 1 и table 2, anu diala sacara standar).

Muatkeun harga lami dina Power Query nganggo tombol Ti Table / Rentang (Tina Méja/Rasaran) ti tab data (Kaping) atawa tina tab Patarosan Kakuatan (gumantung kana versi Excel). Saatos ngamuat, urang bakal uih deui ka Excel tina Power Query kalayan paréntah Tutup sareng muat - Tutup sareng muatkeun… (Tutup & Muat - Tutup & Muat Ka…):

… sareng dina jandela nu nembongan teras pilih Ngan nyieun sambungan (Sambungan wungkul).

Ngulang sarua jeung daptar harga anyar. 

Ayeuna hayu urang nyieun query katilu anu bakal ngagabungkeun jeung ngabandingkeun data ti dua saméméhna. Jang ngalampahkeun ieu, pilih dina Excel dina tab Data - Kéngingkeun Data - Gabungkeun Paménta - Gabungkeun (Data — Kéngingkeun Data — Gabungkeun Paménta — Gabung) atanapi pencét tombol ngombinasikeun (Gabung) tab Patarosan Kakuatan.

Dina jandéla gabung, pilih tabel kami dina daptar turun-handap, pilih kolom kalayan nami barang di jerona, sareng di handap, setel metode gabung - Eksternal lengkep (Lengkap Luar):

Sanggeus ngaklik OK méja tilu kolom kedah muncul, dimana dina kolom katilu anjeun kedah dilegakeun eusi tabel nested nganggo panah ganda dina header:

Hasilna, urang meunang merging data tina duanana tabel:

Éta langkung saé, tangtosna, pikeun ngagentos nami kolom dina lulugu ku ngaklik dua kali dina anu langkung kaharti:

Tur ayeuna paling metot. Pindah ka tab Tambihkeun kolom (Tambahkeun Kolom) tur klik dina tombol Kolom kondisional (Kolom Kondisional). Teras dina jandela anu muka, lebetkeun sababaraha kaayaan tés sareng nilai kaluaran anu saluyu:

Tetep klik dina OK sareng unggah laporan anu hasilna ka Excel nganggo tombol anu sami nutup tur ngundeur (Tutup & Muat) tab imah (Bumi):

Beauty.

Sumawona, upami aya parobihan dina daptar harga di hareup (garis ditambah atanapi dihapus, harga robih, jsb.), maka éta bakal cekap ngan ukur ngapdet pamundut kami nganggo potong kompas keyboard. Ctrl+Alt+F5 atanapi ku tombol Refresh sadayana (Refresh Sadaya) tab data (Kaping).

Naros: Panginten jalan anu paling indah sareng paling merenah. Gawéna pinter jeung tabel badag. Teu merlukeun éditan manual nalika ngarobah ukuran tabel.

kontra: Merlukeun tambihan Power Query (dina Excel 2010-2013) atanapi Excel 2016 kanggo dipasang. Ngaran kolom dina data sumber teu kedah dirobih, upami henteu, urang bakal nampi kasalahan "Kolom sapertos kitu henteu kapendak!" nalika nyobian ngapdet pamundut.

• Kumaha cara ngumpulkeun data tina sadaya file Excel dina polder anu dipasihkeun nganggo Power Query
• Kumaha milarian patandingan antara dua daptar dina Excel
• Ngahijikeun dua daptar tanpa duplikat