Kwatanta tebur biyu

Muna da tebur guda biyu (misali, tsofaffi da sabbin sigogin lissafin farashin), waɗanda muke buƙatar kwatanta da sauri sami bambance-bambance:

Nan da nan ya bayyana cewa an ƙara wani abu a cikin sabon jerin farashin (kwanakin, tafarnuwa ...), wani abu ya ɓace (blackberries, raspberries ...), farashin ya canza don wasu kaya (figs, melons ...). Kuna buƙatar nemo da sauri da nuna duk waɗannan canje-canje.

Ga kowane ɗawainiya a cikin Excel, kusan koyaushe akwai mafita fiye da ɗaya (yawanci 4-5). Don matsalarmu, ana iya amfani da hanyoyi daban-daban:

• aiki VPR (VLOOKUP) - Nemo sunayen samfur daga sabon lissafin farashin a tsohon kuma nuna tsohon farashin kusa da sabon, sannan kama bambance-bambancen.
• haɗa jeri biyu zuwa ɗaya sannan a gina tebur pivot bisa shi, inda za a iya ganin bambance-bambance a fili
• Yi amfani da Ƙaddamar Tambarin Ƙarfin Ƙarfafa don Excel

Bari mu dauke su duka a cikin tsari.

Hanyar 1. Kwatanta tebur tare da aikin VLOOKUP

Idan kun kasance ba ku saba da wannan fasalin mai ban sha'awa ba, sai ku fara duba nan ku karanta ko kallon koyawa ta bidiyo akan shi - ku ceci kanku na shekaru biyu na rayuwa.

Yawanci, ana amfani da wannan aikin don cire bayanai daga wannan tebur zuwa wancan ta hanyar daidaita wasu sigogi na gama gari. A wannan yanayin, za mu yi amfani da shi don tura tsoffin farashin zuwa sabon farashin:

Waɗannan samfuran, waɗanda kuskuren #N/A ya fito, ba sa cikin tsoffin jeri, watau an ƙara su. Canje-canjen farashin kuma a bayyane suke.

ribobi wannan hanya: mai sauƙi kuma bayyananne, "classic of the Genre", kamar yadda suke faɗa. Yana aiki a kowane sigar Excel.

fursunoni yana can kuma. Don nemo samfuran da aka ƙara zuwa sabon jerin farashin, dole ne ku yi hanya iri ɗaya a cikin kishiyar shugabanci, watau cire sabbin farashi zuwa tsohon farashin tare da taimakon VLOOKUP. Idan girman tebur ɗin ya canza gobe, to dole ne a daidaita tsarin. To, kuma akan babban teburi (> layuka dubu 100), duk wannan farin cikin zai ragu sosai.

Hanyar 2: Kwatanta teburi ta amfani da pivot

Mu kwafi tebur ɗin mu ɗaya ƙarƙashin ɗayan, mu ƙara ginshiƙi mai sunan jerin farashin, ta yadda daga baya za ku iya fahimtar wane jeri:

Yanzu, bisa ga tebur da aka ƙirƙira, za mu ƙirƙiri taƙaitawa ta hanyar Saka - PivotTable (Saka - Tebur Pivot). Mu jefa filin Samfur zuwa yankin layi, filin price zuwa yankin shafi da filin ЦEe a cikin kewayon:

Kamar yadda kake gani, tebur pivot zai samar da jerin samfuran gabaɗaya ta atomatik daga tsoffin jeri na farashi (babu maimaituwa!) Da kuma tsara samfuran ta haruffa. Kuna iya ganin samfuran da aka ƙara (ba su da tsohon farashi), samfuran da aka cire (ba su da sabon farashin) da canje-canjen farashin, idan akwai.

Babban jimlar a cikin irin wannan tebur ba su da ma'ana, kuma ana iya kashe su akan shafin Mai ginawa – Babban jimlar – Kashe don layuka da ginshiƙai (Zane - Grand Totals).

Idan farashin ya canza (amma ba yawan kaya ba!), To ya isa kawai sabunta taƙaitaccen bayanin da aka ƙirƙira ta danna-dama akan shi - Refresh.

ribobi: Wannan tsari tsari ne na girma da sauri tare da manyan tebur fiye da VLOOKUP. 

fursunoni: kuna buƙatar kwafin bayanan da hannu a ƙarƙashin juna kuma ku ƙara shafi tare da sunan jerin farashin. Idan girman tebur ɗin ya canza, to dole ne ku sake yin komai.

Hanyar 3: Kwatanta teburi da Query Query

Power Query shine ƙarawa kyauta don Microsoft Excel wanda ke ba ku damar loda bayanai zuwa Excel daga kusan kowane tushe sannan canza wannan bayanan ta kowace hanya da ake so. A cikin Excel 2016, an riga an gina wannan add-in ta tsohuwa akan shafin data (Data), kuma don Excel 2010-2013 kuna buƙatar zazzage shi daban daga gidan yanar gizon Microsoft kuma shigar da shi - sami sabon shafin. Tambayar .arfi.

Kafin loda lissafin farashin mu zuwa Query Query, dole ne a fara canza su zuwa tebur masu wayo. Don yin wannan, zaɓi kewayon tare da bayanai kuma danna haɗin haɗin akan maballin Ctrl+T ko zaɓi shafin akan kintinkiri Gida - Tsarin azaman tebur (Gida - Tsarin azaman Tebur). Ana iya gyara sunayen allunan da aka ƙirƙira akan shafin Constructor (Zan bar ma'auni Table 1 и Table 2, wanda aka samu ta hanyar tsoho).

Load da tsohon farashin a cikin Power Query ta amfani da maballin Daga Tebura/Range (Daga Tebur/Range) daga tab data (Kwanan wata) ko daga tab Tambayar .arfi (dangane da sigar Excel). Bayan loading, za mu koma Excel daga Power Query tare da umarni Rufe kuma ɗauka - Rufe kuma ɗauka a cikin… (Rufe & Loda - Rufe & Loda Zuwa…):

… kuma a cikin taga da ya bayyana sannan zaɓi Kawai ƙirƙirar haɗi (Haɗin kai kaɗai).

Maimaita iri ɗaya tare da sabon lissafin farashi. 

Yanzu bari mu ƙirƙiri tambaya ta uku wacce za ta haɗa tare da kwatanta bayanai daga biyun da suka gabata. Don yin wannan, zaɓi abu a cikin Excel akan shafin Bayanai - Samun Bayanai - Haɗa Buƙatun - Haɗa (Bayanai - Sami Bayanai - Tambayoyin Haɗa - Haɗa) ko kuma danna maɓallin hada (Haɗa) tab Tambayar .arfi.

A cikin taga shiga, zaɓi tebur ɗinmu a cikin jerin abubuwan da aka saukar, zaɓi ginshiƙan tare da sunayen kayayyaki a cikinsu, kuma a ƙasa, saita hanyar haɗin gwiwa - Cikakken waje (Cikakken Waje):

Bayan danna kan OK tebur na ginshiƙai uku ya kamata ya bayyana, inda a cikin ginshiƙi na uku kuna buƙatar faɗaɗa abubuwan da ke cikin tebur ɗin gida ta amfani da kibiya biyu a cikin taken:

A sakamakon haka, muna samun haɗin bayanai daga tebur biyu:

Yana da kyau, ba shakka, a sake suna sunan ginshiƙi a cikin taken ta danna sau biyu akan waɗanda za a iya fahimta:

Kuma yanzu mafi ban sha'awa. Jeka tab Sanya shafi (Ƙara Shagon) kuma danna maɓallin Sharadi na sharadi (Sharadi na Sharadi). Sannan a cikin taga da ke buɗewa, shigar da yanayin gwaji da yawa tare da ƙimar fitarwa daidai.

Ya rage don dannawa OK kuma loda sakamakon rahoton zuwa Excel ta amfani da wannan maballin rufe da saukewa (Rufe & Loda) tab Gida (Gida):

Beauty.

Bugu da ƙari, idan duk wani canje-canje ya faru a cikin jerin farashin nan gaba (ana ƙara ko share layukan, canjin farashin, da sauransu), to zai isa kawai don sabunta buƙatunmu tare da gajeriyar hanya ta keyboard. Ctrl+alt+F5 ko kuma ta hanyar maballin Wartsake duka (Refresh All) tab data (Kwanan wata).

ribobi: Wataƙila hanya mafi kyau da dacewa duka. Yana aiki da wayo tare da manyan teburi. Baya buƙatar gyare-gyaren hannu lokacin canza girman teburi.

fursunoni: Yana buƙatar ƙarawa ta Power Query (a cikin Excel 2010-2013) ko Excel 2016 don shigar. Ba dole ba ne a canza sunayen ginshiƙi a cikin bayanan tushen, in ba haka ba za mu sami kuskuren "Ba a sami irin wannan shafi ba!" lokacin ƙoƙarin sabunta tambaya.

• Yadda ake tattara bayanai daga duk fayilolin Excel a cikin babban fayil da aka bayar ta amfani da Query Query
• Yadda ake nemo matches tsakanin jeri biyu a cikin Excel
• Haɗa lissafin biyu ba tare da kwafi ba