Kaundan
Adunay kami duha ka mga lamesa (pananglitan, ang daan ug bag-ong mga bersyon sa lista sa presyo), nga kinahanglan namon itandi ug dali nga makit-an ang mga kalainan:
Klaro dayon nga adunay gidugang sa bag-ong lista sa presyo (mga petsa, ahos ...), adunay nawala (mga blackberry, raspberry ...), nabag-o ang mga presyo sa pipila ka mga butang (mga igos, melon ...). Kinahanglan nimo nga dali nga makit-an ug ipakita kining tanan nga mga pagbag-o.
Alang sa bisan unsang buluhaton sa Excel, hapit kanunay adunay labaw sa usa ka solusyon (kasagaran 4-5). Alang sa among problema, daghang lainlaing mga pamaagi ang magamit:
- function VPR (VLOOKUP) — pangitaa ang mga ngalan sa produkto gikan sa bag-ong lista sa presyo sa daan ug ipakita ang daan nga presyo sunod sa bag-o, ug dayon kuhaa ang mga kalainan
- iusa ang duha ka lista sa usa ug dayon paghimo ug pivot table base niini, diin ang mga kalainan klaro nga makita
- gamita ang Power Query Add-in para sa Excel
Atong husayon silang tanan.
Pamaagi 1. Pagkumpara sa mga lamesa sa VLOOKUP function
Kung dili ka pamilyar niining talagsaon nga bahin, unya tan-awa una dinhi ug basaha o tan-awa ang usa ka video tutorial niini - luwasa ang imong kaugalingon sa usa ka magtiayon nga mga tuig sa kinabuhi.
Kasagaran, kini nga function gigamit sa pagbitad sa datos gikan sa usa ka lamesa ngadto sa lain pinaagi sa pagpares sa pipila ka sagad nga parameter. Sa kini nga kaso, among gamiton kini aron iduso ang daan nga mga presyo sa bag-ong presyo:
Kadtong mga produkto, nga batok diin ang #N/A error nahimo, wala sa daan nga lista, ie gidugang. Ang mga pagbag-o sa presyo klaro usab nga makita.
pros kini nga pamaagi: yano ug tin-aw, "classic sa genre", ingon sa ilang giingon. Nagtrabaho sa bisan unsang bersyon sa Excel.
disbentaha anaa usab. Aron makapangita sa mga produkto nga nadugang sa bag-ong lista sa presyo, kinahanglan nimong buhaton ang parehas nga pamaagi sa atbang nga direksyon, ie pagbitad sa bag-ong mga presyo sa daan nga presyo sa tabang sa VLOOKUP. Kung ang mga gidak-on sa mga lamesa mausab ugma, nan ang mga pormula kinahanglan nga usbon. Aw, ug sa dako kaayo nga mga lamesa (> 100 ka libo nga mga laray), kining tanan nga kalipay hinayhinay nga mohinay.
Pamaagi 2: Pagkumpara sa mga lamesa gamit ang pivot
Atong kopyahon ang among mga lamesa sa usa sa ilawom sa lain, pagdugang usa ka kolum nga adunay ngalan sa lista sa presyo, aron sa ulahi masabtan nimo kung unsang lista ang linya:
Karon, base sa gibuhat nga lamesa, maghimo kami usa ka summary pinaagi sa Isulod – PivotTable (Sal-ot — Pivot Table). Maglabay ta ug uma Product sa lugar sa mga linya, uma Presyo ngadto sa column area ug field Цena sa range:
Sama sa imong makita, ang pivot table awtomatik nga makamugna og usa ka kinatibuk-ang listahan sa tanang mga produkto gikan sa daan ug bag-ong mga listahan sa presyo (walay pagbalik-balik!) ug paghan-ay sa mga produkto sa alpabeto. Imong klarong makita ang gidugang nga mga produkto (wala silay daan nga presyo), ang gitangtang nga mga produkto (wala silay bag-ong presyo) ug mga kausaban sa presyo, kon aduna man.
Ang mga dagkong total sa ingon nga lamesa dili makatarunganon, ug mahimo silang ma-disable sa tab Constructor - Dako nga total - I-disable ang mga laray ug kolum (Disenyo — Dakong Kabug-usan).
Kung ang mga presyo mausab (apan dili ang gidaghanon sa mga butang!), nan igo na nga i-update ang gihimo nga summary pinaagi sa pag-right-click niini - refresh.
pros: Kini nga pamaagi usa ka han-ay sa magnitude nga mas paspas nga adunay dagkong mga lamesa kaysa VLOOKUP.
disbentaha: kinahanglan nimo nga mano-mano nga kopyahon ang datos sa ilawom sa usag usa ug pagdugang usa ka kolum nga adunay ngalan sa lista sa presyo. Kung ang mga gidak-on sa mga lamesa mausab, nan kinahanglan nimo nga buhaton pag-usab ang tanan.
Pamaagi 3: Pagkumpara sa mga lamesa sa Power Query
Ang Power Query usa ka libre nga add-in alang sa Microsoft Excel nga nagtugot kanimo sa pag-load sa datos ngadto sa Excel gikan sa halos bisan unsang tinubdan ug dayon pagbag-o niini nga datos sa bisan unsang gusto nga paagi. Sa Excel 2016, kini nga add-in gitukod na pinaagi sa default sa tab Data (Data), ug alang sa Excel 2010-2013 kinahanglan nimo nga i-download kini nga gilain gikan sa website sa Microsoft ug i-install kini - pagkuha usa ka bag-ong tab Kusog nga Pagpangutana.
Sa dili pa i-load ang among mga lista sa presyo sa Power Query, kinahanglan una nga i-convert kini sa mga smart table. Aron mahimo kini, pilia ang range nga adunay data ug ipadayon ang kombinasyon sa keyboard Ctrl+T o pilia ang tab sa ribbon Panimalay - Pag-format ingon usa ka lamesa (Balay — Format isip Talaan). Ang mga ngalan sa gibuhat nga mga lamesa mahimong matul-id sa tab Magtutukod (Mobiya ko sa sumbanan Table 1 и Table 2, nga nakuha pinaagi sa default).
I-load ang daan nga presyo sa Power Query gamit ang buton Gikan sa Table/Range (Gikan sa Talaan/Sakop) gikan sa tab Data (Petsa) o gikan sa tab Kusog nga Pagpangutana (depende sa bersyon sa Excel). Pagkahuman sa pagkarga, mobalik kami sa Excel gikan sa Power Query uban ang mando Pagsira ug pagkarga - Pagsira ug pagkarga sa… (Close & Load — Close & Load To…):
… ug sa bintana nga makita unya pilia Paghimo lang og koneksyon (Koneksyon Lamang).
Balika ang parehas sa bag-ong lista sa presyo.
Karon maghimo kita ug ikatulo nga pangutana nga maghiusa ug itandi ang datos gikan sa miaging duha. Aron mahimo kini, pagpili sa Excel sa tab Data - Pagkuha og Data - Paghiusa sa mga Gihangyo - Paghiusa (Data — Pagkuhag Data — Paghiusa sa mga Pangutana — Paghiusa) o pindota ang buton Pagsagol (Paghiusa) tab Kusog nga Pagpangutana.
Sa window sa pag-apil, pilia ang among mga lamesa sa mga lista sa drop-down, pilia ang mga kolum nga adunay mga ngalan sa mga butang nga naa niini, ug sa ilawom, itakda ang pamaagi sa pag-apil - Kompleto sa gawas (Tibuok gawas):
Human sa pag-klik OK Ang usa ka lamesa sa tulo ka mga kolum kinahanglan nga makita, diin sa ikatulo nga kolum kinahanglan nimo nga palapdan ang sulud sa mga nested nga mga lamesa gamit ang doble nga arrow sa ulohan:
Ingon usa ka sangputanan, makuha namon ang paghiusa sa datos gikan sa duha nga mga lamesa:
Mas maayo, siyempre, ang pag-usab sa ngalan sa mga ngalan sa kolum sa ulohan pinaagi sa pag-double click sa mas masabtan:
Ug karon ang labing makapaikag. Adto sa tab Pagdugang kolum (Idugang ang Column) ug i-klik ang buton Kondisyonal nga kolum (Kondisyonal nga Kolum). Ug unya sa bintana nga nagbukas, pagsulod sa daghang mga kondisyon sa pagsulay nga adunay katugbang nga mga kantidad sa output:
Kini nagpabilin sa pag-klik sa OK ug i-upload ang resulta nga report sa Excel gamit ang samang buton pagsira ug pag-download (Close & Load) tab Home (Balay):
Katahum.
Dugang pa, kung adunay mga pagbag-o nga mahitabo sa mga lista sa presyo sa umaabot (mga linya gidugang o gitangtang, mga pagbag-o sa mga presyo, ug uban pa), nan kini igo na nga i-update ang among mga hangyo gamit ang usa ka shortcut sa keyboard. Ctrl+alt+F5 o pinaagi sa buton Refresh tanan (Refresh Tanan) tab Data (Petsa).
pros: Tingali ang labing nindot ug sayon nga paagi sa tanan. Maayo nga nagtrabaho sa dagkong mga lamesa. Wala magkinahanglan og manwal nga mga pag-edit sa pag-resize sa mga lamesa.
disbentaha: Nagkinahanglan sa Power Query add-in (sa Excel 2010-2013) o Excel 2016 aron ma-install. Ang mga ngalan sa kolum sa gigikanan nga datos kinahanglan dili usbon, kung dili, makuha namon ang sayup nga "Ang kolum nga ingon ug ingon wala makit-an!" sa pagsulay sa pag-update sa pangutana.
- Giunsa pagkolekta ang datos gikan sa tanan nga mga file sa Excel sa usa ka gihatag nga folder gamit ang Power Query
- Giunsa pagpangita ang mga posporo tali sa duha ka lista sa Excel
- Paghiusa sa duha ka lista nga walay mga duplicate