Paragraf
Kita duwe rong tabel (contone, dhaptar rega versi lawas lan anyar), sing kudu dibandhingake lan cepet nemokake bedane:
Cetha yen ana sing ditambahake menyang dhaptar rega anyar (tanggal, bawang putih ...), ana sing ilang (blackberry, raspberries ...), rega wis diganti kanggo sawetara barang (anjir, melon ...). Sampeyan kudu cepet nemokake lan nampilake kabeh owah-owahan kasebut.
Kanggo tugas apa wae ing Excel, meh ana luwih saka siji solusi (biasane 4-5). Kanggo masalah kita, macem-macem pendekatan bisa digunakake:
- fungsi VPR (VLOOKUP) - goleki jeneng produk saka dhaptar rega anyar ing sing lawas lan nampilake rega lawas ing jejere sing anyar, banjur goleki bedane
- gabungke rong dhaptar dadi siji banjur gawe tabel pivot adhedhasar kasebut, sing bedane bakal katon kanthi jelas.
- nggunakake Power Query Add-in kanggo Excel
Ayo padha njupuk kabeh ing urutan.
Cara 1. Mbandhingake tabel karo fungsi VLOOKUP
Yen sampeyan ora ngerti babagan fitur sing apik iki, mula katon ing kene lan waca utawa nonton tutorial video babagan iki - nylametake awake dhewe sawetara taun.
Biasane, fungsi iki digunakake kanggo narik data saka siji tabel menyang liyane kanthi cocog sawetara parameter umum. Ing kasus iki, kita bakal nggunakake kanggo push prices lawas menyang rega anyar:
Produk kasebut, sing ana kesalahan #N / A, ora ana ing dhaptar lawas, yaiku ditambahake. Owah-owahan rega uga katon kanthi jelas.
Pros cara iki: prasaja lan cetha, "klasik saka genre", lagi ngomong. Dianggo ing sembarang versi Excel.
cons uga ana. Kanggo nggoleki produk sing ditambahake menyang dhaptar rega anyar, sampeyan kudu nindakake prosedur sing padha ing arah sing ngelawan, yaiku narik rega anyar menyang rega lawas kanthi bantuan VLOOKUP. Yen ukuran tabel sesuk diganti, rumus kasebut kudu diatur. Uga, lan ing tabel sing gedhe banget (> 100 ewu larik), kabeh rasa seneng iki bakal alon-alon.
Cara 2: Mbandhingake tabel nggunakake pivot
Ayo nyalin tabel siji ing sangisore liyane, nambah kolom kanthi jeneng dhaptar rega, supaya mengko sampeyan bisa ngerti saka dhaptar sing endi:
Saiki, adhedhasar tabel digawe, kita bakal nggawe ringkesan liwat Sisipake - PivotTable (Sisipake - Tabel Pivot). Ayo mbuwang sawah Product menyang area garis, lapangan price menyang area kolom lan lapangan Цena menyang kisaran:
Kaya sing sampeyan ngerteni, tabel pivot kanthi otomatis bakal ngasilake dhaptar umum kabeh produk saka dhaptar rega lawas lan anyar (ora ana pengulangan!) Lan ngurutake produk miturut abjad. Sampeyan bisa ndeleng kanthi jelas produk sing ditambahake (ora duwe rega lawas), produk sing dibusak (ora duwe rega anyar) lan owah-owahan rega, yen ana.
total Grand ing meja kuwi ora nggawe raos, lan padha bisa dipatèni ing tab Konstruktor - Total total - Pateni baris lan kolom (Desain - Total Total).
Yen rega ganti (nanging dudu jumlah barang!), mula cukup nganyari ringkesan sing digawe kanthi ngeklik tengen - Refresh.
Pros: Pendekatan iki minangka urutan gedhene luwih cepet kanthi tabel gedhe tinimbang VLOOKUP.
cons: sampeyan kudu nyalin data kanthi manual ing saben liyane lan nambah kolom kanthi jeneng dhaptar rega. Yen ukuran tabel diganti, sampeyan kudu nindakake kabeh maneh.
Cara 3: Mbandhingake tabel karo Power Query
Power Query minangka tambahan gratis kanggo Microsoft Excel sing ngidini sampeyan mbukak data menyang Excel saka meh wae sumber lan banjur ngowahi data iki kanthi cara sing dikarepake. Ing Excel 2016, tambahan iki wis dibangun kanthi gawan ing tab Data (Data), lan kanggo Excel 2010-2013 sampeyan kudu ngundhuh kanthi kapisah saka situs web Microsoft lan nginstal - entuk tab anyar Pitakonan Daya.
Sadurunge ngemot dhaptar rega menyang Power Query, mula kudu diowahi dadi tabel cerdas. Kanggo nindakake iki, pilih kisaran karo data banjur pencet kombinasi ing keyboard Ctrl+T utawa pilih tab ing pita Ngarep - Format minangka tabel (Ngarep - Format minangka Tabel). Jeneng tabel sing digawe bisa didandani ing tab Constructor (Aku bakal ninggalake standar Tabel 1 и Tabel 2, sing dipikolehi kanthi gawan).
Muat rega lawas ing Power Query nggunakake tombol Saka Tabel / Range (Saka Tabel/Rentang) saka tab Data (Tanggal) utawa saka tab Pitakonan Daya (gumantung saka versi Excel). Sawise dimuat, kita bakal bali menyang Excel saka Power Query kanthi printah kasebut Nutup lan muat - Nutup lan muat ing… (Tutup & Muat - Tutup & Muat Kanggo…):
... lan ing jendhela sing katon banjur pilih Mung nggawe sambungan (Sambungan Mung).
Baleni padha karo dhaptar rega anyar.
Saiki ayo nggawe pitakon katelu sing bakal nggabungake lan mbandhingake data saka rong sadurunge. Kanggo nindakake iki, pilih ing Excel ing tab Data - Entuk Data - Gabungan Panjaluk - Gabung (Data — Entuk Data — Gabung pitakon — Gabung) utawa pencet tombol Gabung (Gabung) tab Pitakonan Daya.
Ing jendhela gabung, pilih tabel kita ing dhaptar gulung mudhun, pilih kolom kanthi jeneng barang kasebut, lan ing sisih ngisor, atur cara gabung - Eksternal lengkap (Outer Lengkap):
Sawise ngeklik OK Tabel telung kolom kudu katon, ing kolom katelu sampeyan kudu nggedhekake isi tabel nested nggunakake panah pindho ing header:
Akibaté, kita entuk gabungan data saka loro tabel:
Mesthi, luwih becik ngganti jeneng kolom ing header kanthi ngeklik kaping pindho ing sing luwih dingerteni:
Lan saiki sing paling menarik. Pindhah menyang tab Tambah kolom (Tambah Kolom) lan klik ing tombol Kolom kondisional (Kolom Bersyarat). Banjur ing jendhela sing mbukak, ketik sawetara kahanan tes kanthi nilai output sing cocog:
Iku tetep kanggo klik ing OK lan upload laporan asil menyang Excel nggunakake tombol padha nutup lan download (Tutup & Muat) tab Home (Ngarep):
Beauty.
Kajaba iku, yen ana owah-owahan ing dhaptar rega ing mangsa ngarep (baris ditambahake utawa dibusak, owah-owahan rega, lan sapiturute), banjur bakal cukup kanggo nganyari panjalukan kita kanthi trabasan keyboard. Ctrl+Alt+F5 utawa kanthi tombol Refresh kabeh (Refresh Kabeh) tab Data (Tanggal).
Pros: Mbok menawa cara sing paling apik lan trep. Dianggo pinter karo meja gedhe. Ora mbutuhake suntingan manual nalika ngowahi ukuran tabel.
cons: Mbutuhake tambahan Power Query (ing Excel 2010-2013) utawa Excel 2016 kanggo diinstal. Jeneng kolom ing data sumber kudu ora diganti, yen ora, kita bakal entuk kesalahan "Kolom kasebut ora ditemokake!" nalika nyoba nganyari pitakon.
- Cara ngumpulake data saka kabeh file Excel ing folder tartamtu nggunakake Power Query
- Cara golek sing cocog antarane rong dhaptar ing Excel
- Nggabungake rong dhaptar tanpa duplikat