Երկու աղյուսակների համեմատություն

Մենք ունենք երկու աղյուսակ (օրինակ՝ գնացուցակի հին և նոր տարբերակները), որոնք պետք է համեմատենք և արագ գտնենք տարբերությունները.

Միանգամից պարզ է դառնում, որ նոր գնացուցակում ինչ-որ բան ավելացվել է (խուրմա, սխտոր…), ինչ-որ բան անհետացել է (մոշ, ազնվամորի…), որոշ ապրանքների (թուզ, սեխ…) գները փոխվել են: Դուք պետք է արագ գտնեք և ցուցադրեք այս բոլոր փոփոխությունները:

Excel-ում ցանկացած առաջադրանքի համար գրեթե միշտ կա մեկից ավելի լուծում (սովորաբար 4-5): Մեր խնդրի համար կարող են օգտագործվել բազմաթիվ տարբեր մոտեցումներ.

• ֆունկցիա VPR- ը (VLOOKUP) — փնտրեք ապրանքների անունները նոր գնացուցակից հինում և ցուցադրեք հին գինը նորի կողքին, այնուհետև պարզեք տարբերությունները
• միաձուլեք երկու ցուցակները մեկի մեջ և այնուհետև դրա հիման վրա կառուցեք առանցքային աղյուսակ, որտեղ տարբերությունները հստակ տեսանելի կլինեն
• օգտագործեք Power Query հավելումը Excel-ի համար

Եկեք դրանք բոլորին հերթականությամբ վերցնենք։

Մեթոդ 1. Աղյուսակների համեմատում VLOOKUP ֆունկցիայի հետ

Եթե ​​դուք լիովին անծանոթ եք այս հիանալի հատկանիշին, ապա նախ նայեք այստեղ և կարդացեք կամ դիտեք դրա մասին տեսահոլովակ. փրկեք ձեզ մի քանի տարի կյանք:

Սովորաբար, այս ֆունկցիան օգտագործվում է տվյալների մի աղյուսակից մյուսը տեղափոխելու համար՝ համապատասխանեցնելով որոշ ընդհանուր պարամետր: Այս դեպքում մենք կօգտագործենք այն հին գները նոր գնի մեջ մղելու համար.

Այն ապրանքները, որոնց նկատմամբ #N/A սխալ է պարզվել, հին ցուցակում չկան, այսինքն ավելացվել են։ Գների փոփոխությունները նույնպես հստակ տեսանելի են։

Կոալիցիայում այս մեթոդը՝ պարզ և պարզ, ինչպես ասում են՝ «ժանրի դասական»: Աշխատում է Excel-ի ցանկացած տարբերակում:

Դեմ կա նաև այնտեղ։ Նոր գնացուցակում ավելացված ապրանքներ որոնելու համար դուք պետք է կատարեք նույն ընթացակարգը հակառակ ուղղությամբ, այսինքն՝ VLOOKUP-ի օգնությամբ նոր գները բարձրացրեք հին գնին: Եթե ​​վաղը աղյուսակների չափերը փոխվեն, ապա բանաձևերը պետք է ճշգրտվեն։ Դե, և իսկապես մեծ սեղանների վրա (> 100 հազար տող) այս ամբողջ երջանկությունը պարկեշտորեն կդանդաղի:

Մեթոդ 2. Աղյուսակների համեմատություն առանցքի միջոցով

Եկեք պատճենենք մեր աղյուսակները մեկը մյուսի տակ՝ ավելացնելով սյունակ՝ գնացուցակի անվանմամբ, որպեսզի հետո հասկանաք, թե որ ցուցակից որ շարքը.

Այժմ, հիմնվելով ստեղծված աղյուսակի վրա, մենք կստեղծենք ամփոփում միջոցով Տեղադրել – PivotTable (Տեղադրեք - առանցքային աղյուսակ). Եկեք դաշտ գցենք արդյունք գծերի տարածքին, դաշտ Գին սյունակի տարածքին և դաշտին Цէնա միջակայքում:

Ինչպես տեսնում եք, առանցքային աղյուսակը ավտոմատ կերպով կստեղծի բոլոր ապրանքների ընդհանուր ցուցակը հին և նոր գնացուցակներից (առանց կրկնությունների!) և կդասավորի ապրանքները այբբենական կարգով: Հստակ կարելի է տեսնել ավելացված ապրանքները (նրանք չունեն հին գինը), հանված ապրանքները (նոր գին չունեն) և գների փոփոխությունները, եթե այդպիսիք կան։

Նման աղյուսակում ընդհանուր գումարները իմաստ չունեն, և դրանք կարող են անջատվել ներդիրում Կառուցիչ – Մեծ գումարներ – Անջատել տողերի և սյունակների համար (Դիզայն - Մեծ տոտալներ).

Եթե ​​գները փոխվում են (բայց ոչ ապրանքների քանակը), ապա բավական է պարզապես թարմացնել ստեղծված ամփոփագիրը՝ աջ սեղմելով դրա վրա. Թարմացնել.

ԿոալիցիայումԱյս մոտեցումը մեծության կարգով ավելի արագ է մեծ աղյուսակներով, քան VLOOKUP-ը: 

ԴեմԴուք պետք է ձեռքով պատճենեք տվյալները միմյանց տակ և ավելացնեք սյունակ՝ գնացուցակի անունով: Եթե ​​սեղանների չափերը փոխվում են, ապա դուք պետք է ամեն ինչ նորից անեք:

Մեթոդ 3. Աղյուսակների համեմատություն Power Query-ի հետ

Power Query-ն անվճար հավելում է Microsoft Excel-ի համար, որը թույլ է տալիս բեռնել տվյալները Excel-ում գրեթե ցանկացած աղբյուրից և այնուհետև վերափոխել այս տվյալները ցանկացած ցանկալի ձևով: Excel 2016-ում այս հավելումը լռելյայն արդեն ներկառուցված է ներդիրում Ամսաթիվ (Տվյալներ), իսկ Excel 2010-2013-ի համար անհրաժեշտ է այն ներբեռնել Microsoft-ի կայքից առանձին և տեղադրել այն՝ ստանալ նոր ներդիր: Power հարցում.

Նախքան մեր գնացուցակները Power Query-ում բեռնելը, դրանք նախ պետք է վերածվեն խելացի աղյուսակների: Դա անելու համար ընտրեք տվյալների տիրույթը և սեղմեք ստեղնաշարի համակցությունը Ctrl+T կամ ընտրեք ժապավենի ներդիրը Գլխավոր – Ձևաչափել որպես աղյուսակ (Տուն — Ձևաչափել որպես աղյուսակ). Ստեղծված աղյուսակների անունները կարող են ուղղվել ներդիրում շինարար (Ես կթողնեմ ստանդարտը Աղյուսակ 1 и Աղյուսակ 2, որոնք ստացվում են լռելյայն):

Բեռնել հին գինը Power Query-ում՝ օգտագործելով կոճակը Սեղանից / միջակայքից (Սեղանից / միջակայքից) ներդիրից Ամսաթիվ (Ամսաթիվ) կամ ներդիրից Power հարցում (կախված Excel-ի տարբերակից): Բեռնումից հետո մենք կվերադառնանք Excel Power Query-ից հրամանով Փակել և բեռնել – Փակել և բեռնել… (Փակել և բեռնել — Փակել և բեռնել…):

… և երևացող պատուհանում ընտրեք Պարզապես կապ ստեղծեք (Միայն միացում).

Նույնը կրկնեք նոր գնացուցակի հետ: 

Հիմա եկեք ստեղծենք երրորդ հարցումը, որը կմիավորի և կհամեմատի նախորդ երկուսի տվյալները: Դա անելու համար ընտրեք Excel-ում ներդիրում Տվյալներ – Ստացեք տվյալներ – Միավորել հարցումները – Միավորել (Տվյալներ — Ստացեք տվյալներ — Միաձուլման հարցումներ — Միաձուլում) կամ սեղմեք կոճակը Միավորել (Միավորվել) ականջակալ Power հարցում.

Միացման պատուհանում բացվող ցուցակներում ընտրեք մեր աղյուսակները, ընտրեք սյունակները դրանցում առկա ապրանքների անուններով և ներքևում սահմանեք միացման եղանակը. Ամբողջական արտաքին (Լրիվ արտաքին):

Սեղմելուց հետո OK պետք է հայտնվի երեք սյունակներից բաղկացած աղյուսակ, որտեղ երրորդ սյունակում անհրաժեշտ է ընդլայնել ներդիր աղյուսակների բովանդակությունը՝ օգտագործելով վերնագրի կրկնակի սլաքը.

Արդյունքում մենք ստանում ենք տվյալների միաձուլում երկու աղյուսակներից.

Ավելի լավ է, իհարկե, վերանվանել սյունակների անունները վերնագրի մեջ՝ կրկնակի սեղմելով ավելի հասկանալիների վրա.

Իսկ հիմա ամենահետաքրքիրը. Գնացեք ներդիր Ավելացնել սյունակ (Ավելացնել սյունակ) և սեղմեք կոճակի վրա Պայմանական սյունակ (Պայմանական սյունակ). Եվ այնուհետև բացվող պատուհանում մուտքագրեք մի քանի փորձարկման պայմաններ իրենց համապատասխան ելքային արժեքներով.

Մնում է սեղմել OK և ստացված հաշվետվությունը վերբեռնեք Excel-ում՝ օգտագործելով նույն կոճակը փակիր և ներբեռնիր (Փակել և բեռնել) ականջակալ Գլխավոր (Տուն):

Գեղեցկություն.

Ավելին, եթե հետագայում գնացուցակներում որևէ փոփոխություն տեղի ունենա (տողեր ավելացվեն կամ ջնջվեն, գները փոխվեն և այլն), ապա բավական կլինի միայն թարմացնել մեր հարցումները ստեղնաշարի դյուրանցմամբ։ Ctrl+ալտ+F5 կամ կոճակով Թարմացնել բոլորը (Թարմացնել բոլորը) ականջակալ Ամսաթիվ (Ամսաթիվ).

ԿոալիցիայումԹերևս ամենագեղեցիկ և հարմար միջոցը: Խելացիորեն աշխատում է մեծ սեղանների հետ: Չի պահանջում ձեռքով խմբագրումներ աղյուսակների չափափոխման ժամանակ:

ԴեմՊահանջում է Power Query հավելումը (Excel 2010-2013) կամ Excel 2016-ը տեղադրելու համար: Աղբյուրի տվյալների մեջ սյունակների անունները չպետք է փոխվեն, հակառակ դեպքում մենք կստանանք «Սյունակը չի գտնվել այդպիսին» սխալը: երբ փորձում եք թարմացնել հարցումը:

• Ինչպես հավաքել տվյալներ Excel-ի բոլոր ֆայլերից տվյալ թղթապանակում՝ օգտագործելով Power Query
• Ինչպես գտնել համընկնումներ երկու ցուցակների միջև Excel-ում
• Երկու ցուցակների միավորում առանց կրկնօրինակների