Microsoft Excel- ի բազմաթիվ հնարավորություններից մեկը տվյալների երկու ցուցակների համեմատման, ցուցակների միջև համընկնումների բացահայտման և միայն մեկ ցուցակում հայտնաբերված տարրերի բացահայտման հնարավորությունն է: Սա օգտակար է ֆինանսական գրառումները համեմատելիս կամ ստուգելիս ՝ արդյոք որոշակի անուն տվյալների շտեմարանում կա՞: Դուք կարող եք օգտագործել MATCH գործառույթը `համապատասխանող կամ չհամապատասխանող գրառումները նույնականացնելու և նշելու համար, կամ կարող եք օգտագործել պայմանական ձևաչափում COUNTIF գործառույթով: Հետևյալ քայլերը հուշում են, թե ինչպես օգտագործել յուրաքանչյուրը ՝ ձեր տվյալներին համապատասխանեցնելու համար:
Քայլեր
Մեթոդ 1 -ից 2 -ը. Գրառումների նույնականացում MATCH գործառույթով
Քայլ 1. Պատճենեք տվյալների ցուցակները մեկ աշխատանքային թերթիկի վրա:
Excel- ը կարող է աշխատել մի քանի աշխատանքային թերթերի հետ մեկ աշխատանքային գրքում կամ մի քանի աշխատանքային գրքույկներով, սակայն ցուցակները համեմատելը ավելի հեշտ կլինի, եթե դրանց տվյալները պատճենեք մեկ աշխատանքային թերթի վրա:
Քայլ 2. listանկի յուրաքանչյուր տարրին տվեք եզակի նույնացուցիչ:
Եթե ձեր երկու ցուցակները չեն կիսում դրանք նույնականացնելու ընդհանուր եղանակը, գուցե անհրաժեշտ լինի լրացուցիչ սյունակ ավելացնել յուրաքանչյուր տվյալների ցուցակին, որը նույնականացնում է այդ տարրը Excel- ին, որպեսզի տեսնի, թե արդյոք տվյալ ցուցակի տարրը որևէ առարկայի հետ կապ ունի մյուս ցուցակում: Այս նույնացուցիչի բնույթը կախված կլինի այն տվյալների տեսակից, որոնց հետ փորձում եք համընկնել: Յուրաքանչյուր սյունակի ցուցակի համար ձեզ անհրաժեշտ կլինի նույնացուցիչ:
- Տվյալ ժամանակաշրջանի հետ կապված ֆինանսական տվյալների համար, ինչպիսիք են հարկային հաշվառումները, սա կարող է լինել ակտիվի նկարագրությունը, ակտիվի ձեռքբերման ամսաթիվը կամ երկուսն էլ: Որոշ դեպքերում գրառումը կարող է նույնականացվել կոդի համարով. սակայն, եթե նույն համակարգը չի օգտագործվում երկու ցուցակների համար, այս նույնացուցիչը կարող է ստեղծել համընկնումներ, որտեղ դրանք չկան կամ անտեսել համապատասխանությունները, որոնք պետք է կատարվեն:
- Որոշ դեպքերում կարող եք մեկ ցուցակից ապրանքներ վերցնել և դրանք միավորել մեկ այլ ցուցակի տարրերի հետ ՝ նույնացուցիչ ստեղծելու համար, օրինակ ՝ ֆիզիկական ակտիվների նկարագրությունը և գնման տարին: Նման նույնացուցիչ ստեղծելու համար դուք համակցում եք (ավելացնում, համատեղում) երկու կամ ավելի բջիջների տվյալները ՝ օգտագործելով ամպերսանդը (&): F3 բջիջի տարրերի նկարագրությունը G3 բջիջի ամսաթվին և բացատով առանձնացնելու համար համատեղելու համար մուտքագրեք '= F3 & "& G3' բանաձևը այդ շարքի մեկ այլ բջիջում, օրինակ ՝ E3- ում: Եթե ցանկանում էիք նույնացուցիչի մեջ ներառել միայն տարին (քանի որ մի ցուցակում օգտագործվում են ամբողջական ամսաթվերը, իսկ մյուսը `միայն տարիները), ապա YEAR գործառույթը կներառեք ՝ փոխարենը մուտքագրելով '= F3 &" "& YEAR (G3)' E3 բջիջում: (Մի ներառեք միայնակ մեջբերումները. Դրանք այնտեղ են միայն օրինակ նշելու համար):
- Բանաձևը ստեղծելուց հետո այն կարող եք պատճենել նույնացուցիչ սյունակի բոլոր մյուս բջիջներում ՝ բանաձևով բջիջը ընտրելով և լրացման բռնակը քաշելով սյունակի մյուս բջիջների վրայով, որտեղ ցանկանում եք պատճենել բանաձևը: Երբ բացում եք մկնիկի կոճակը, յուրաքանչյուր բջիջ, որի վրա քաշել եք, կլրացվի բանաձևով, իսկ բջիջների հղումները ճշգրտված կլինեն նույն տողի համապատասխան բջիջներին:
Քայլ 3. Հնարավորության դեպքում ստանդարտացրեք տվյալները:
Մինչ միտքը ճանաչում է, որ «Inc.» և «Ներկառուցված» նշանակում են նույնը, Excel- ը դա չի անում, եթե այն չվերափոխեք այս կամ այն բառը: Նմանապես, դուք կարող եք համարել այնպիսի արժեքներ, ինչպիսիք են $ 11, 950 և $ 11, 999.95, այնքան մոտ, որ համապատասխանեն, բայց Excel- ը դա չի անի, եթե դա չասեք:
- Կարող եք գործ ունենալ որոշ հապավումների հետ, օրինակ ՝ «Co» «Ընկերության» համար և «Inc» ՝ «Incorporated» ՝ օգտագործելով LEFT լարային գործառույթը ՝ լրացուցիչ նիշերը կրճատելու համար: Այլ հապավումներ, ինչպիսիք են «Assn» - ը «Association» - ի համար, լավագույն դեպքում կարող են լինել լուծվել է տվյալների մուտքագրման ոճի ուղեցույց ստեղծելով, այնուհետև գրել ծրագիր `ոչ պատշաճ ձևաչափեր փնտրելու և ուղղելու համար:
- Թվերի տողերի համար, ինչպիսիք են ZIP կոդերը, որտեղ որոշ գրառումներ ներառում են ZIP +4 ածանցը, իսկ մյուսները `ոչ, կարող եք կրկին օգտագործել LEFT լարային գործառույթը` ճանաչելու և համապատասխանեցնելու միայն առաջնային ZIP կոդերը: Որպեսզի Excel- ը ճանաչի թվային արժեքներ, որոնք մոտ են, բայց ոչ նույնը, կարող եք օգտագործել ROUND գործառույթը `փակելու արժեքները նույն թվին և համապատասխանեցնելու դրանք:
- Լրացուցիչ բացատները, օրինակ ՝ բառերի միջև երկու տարածություն մուտքագրելը մեկի փոխարեն, կարող են հեռացվել ՝ օգտագործելով TRIM գործառույթը:
Քայլ 4. Համեմատության բանաձևի համար ստեղծեք սյունակներ:
Asիշտ այնպես, ինչպես դուք պետք է ստեղծեիք սյունակներ ցուցակի նույնացուցիչների համար, դուք նույնպես պետք է սյունակներ ստեղծեք այն բանաձևի համար, որը համեմատում է ձեզ համար: Յուրաքանչյուր ցուցակի համար ձեզ հարկավոր է մեկ սյունակ:
Դուք կցանկանաք այս սյունակներին պիտակավորել «Բացակա՞»:
Քայլ 5. Մուտքագրեք համեմատության բանաձևը յուրաքանչյուր բջիջում:
Համեմատության բանաձևի համար դուք կօգտագործեք MATCH գործառույթը, որը տեղադրված է մեկ այլ ֆունկցիայի ՝ ISNA- ի ներսում:
- Բանաձևն ընդունում է «= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))» ձևը, որտեղ առաջին ցուցակի նույնացուցիչ սյունակի բջիջը համեմատվում է երկրորդ ցուցակի նույնացուցիչներից յուրաքանչյուրի հետ: տեսեք, արդյոք այն կհամապատասխանի դրանցից մեկին: Եթե այն չի համընկնում, գրառում է բացակայում, և այդ վանդակում կցուցադրվի «TRUE» բառը: Եթե այն համընկնում է, գրառումը ներկա է, և կցուցադրվի «FALSE» բառը: (Բանաձևը մուտքագրելիս մի՛ ներառեք մեջբերումները):
- Դուք կարող եք բանաձևը պատճենել սյունակի մնացած բջիջներում այնպես, ինչպես պատճենել եք բջիջի նույնացուցիչի բանաձևը: Այս դեպքում փոխվում է միայն նույնացուցիչ բջիջի բջիջի հղումը, քանի որ երկրորդ բջիջի նույնացուցիչների ցուցակի առաջին և վերջին բջիջների տողի և սյունակների հղումների դիմաց դոլարի նշանները դնելը դրանք դարձնում է բացարձակ հղումներ:
- Դուք կարող եք պատճենել առաջին ցուցակի համեմատության բանաձևը երկրորդ ցուցակի սյունակի առաջին բջիջում: Այնուհետև դուք պետք է խմբագրեք բջիջի հղումները, որպեսզի «G3» - ը փոխարինվի երկրորդ ցուցակի առաջին նույնացուցիչի բջիջի հղումով և «$ L $ 3: $ L $ 14» փոխարինվի առաջին և վերջին նույնացուցիչ բջիջով: երկրորդ ցուցակ: (Մենակ թողեք դոլարի նշանները և երկու կետերը): Այնուհետև կարող եք պատճենել այս խմբագրված բանաձևը երկրորդ ցուցակի համեմատության տողում մնացած բջիջներում:
Քայլ 6. Տեսակավորեք ցուցակները `անհրաժեշտության դեպքում անհամապատասխան արժեքներն ավելի հեշտ տեսնելու համար:
Եթե ձեր ցուցակները մեծ են, գուցե անհրաժեշտ լինի դրանք տեսակավորել ՝ բոլոր անհամապատասխան արժեքները միասին դնելու համար: Ստորև բերված ենթածրագրերի ցուցումները կփոխակերպեն բանաձևերը արժեքների `վերահաշվարկի սխալներից խուսափելու համար, և եթե ձեր ցուցակները մեծ են, կխուսափեն երկար վերահաշվարկի ժամանակից:
- Քաշեք ձեր մկնիկը ցանկի բոլոր բջիջների վրա `այն ընտրելու համար:
- Excel 2003 -ում Խմբագրել ընտրացանկից կամ Excel 2007 կամ 2010 -ի Գլխավոր տախտակի Clipboard խմբից ընտրեք Պատճենել:
- Excel 2003 -ում Խմբագրել ընտրացանկից կամ Excel 2007 կամ 2010s Home Clipboard խմբի «Կպցնել» բացվող կոճակից ընտրեք Տեղադրեք հատուկ:
- Ընտրեք «Արժեքներ» Paste As ցուցակից ՝ Paste Special երկխոսության դաշտում: Կտտացրեք OK ՝ երկխոսությունը փակելու համար:
- Excel 2003 -ի Տվյալների ընտրացանկից կամ Excel 2007 կամ 2010 -ի Տվյալների ժապավենի Տեսակավորում և զտիչ խմբից ընտրեք Տեսակավորել:
- Տեսակավորել ըստ երկխոսության «Իմ տվյալների տիրույթն ունի» ցուցակից ընտրեք «Վերնագրի տող», ընտրեք «Բացակա՞ն»: (կամ այն անունը, որն իրականում տվել եք համեմատության սյունակի վերնագիր) և կտտացրեք OK:
- Կրկնեք այս քայլերը մյուս ցուցակի համար:
Քայլ 7. Տեսողականորեն համեմատեք չհամընկնող տարրերը `տեսնելու, թե ինչու դրանք չեն համընկնում:
Ինչպես նշվեց նախկինում, Excel- ը նախագծված է տվյալների ճշգրիտ համընկնումներ փնտրելու համար, եթե այն չեք սահմանել մոտավոր տվյալները որոնելու համար: Ձեր անհամապատասխանությունը կարող է լինել այնքան պարզ, որքան տառերի կամ թվանշանների պատահական փոխադրումը: Դա կարող է լինել նաև այն, ինչը պահանջում է անկախ ստուգում, օրինակ ՝ ստուգել ՝ արդյոք ցուցակված ակտիվներն առաջին հերթին պետք է զեկուցվե՞ն:
Մեթոդ 2 2 -ից. Պայմանական ձևաչափում COUNTIF- ով
Քայլ 1. Պատճենեք տվյալների ցուցակները մեկ աշխատանքային թերթիկի վրա:
Քայլ 2. Որոշեք, թե որ ցուցակում եք ցանկանում ընդգծել համապատասխանող կամ չհամապատասխանող գրառումները:
Եթե ցանկանում եք ընդգծել գրառումները միայն մեկ ցուցակում, հավանաբար կցանկանաք առանձնացնել այդ ցուցակին հատուկ գրառումները. այսինքն ՝ գրառումներ, որոնք չեն համընկնում մյուս ցուցակի գրառումների հետ: Եթե ցանկանում եք երկու ցուցակներում նշել գրառումները, կցանկանաք ընդգծել միմյանց համապատասխանող գրառումները: Այս օրինակի նպատակների համար մենք ենթադրենք, որ առաջին ցուցակը զբաղեցնում է G3- ից G14 բջիջները, իսկ երկրորդը `L3- ից L14 բջիջները:
Քայլ 3. Ընտրեք ցանկի այն կետերը, որոնցում ցանկանում եք ընդգծել եզակի կամ համապատասխան տարրեր:
Եթե ցանկանում եք երկու ցուցակներում ընդգծել համապատասխան տարրերը, դուք պետք է ընտրեք ցուցակները մեկ առ մեկ և յուրաքանչյուր ցուցակի վրա կիրառեք համեմատության բանաձևը (նկարագրված է հաջորդ քայլում):
Քայլ 4. Կիրառեք համապատասխան համեմատության բանաձևը:
Դա անելու համար դուք պետք է մուտք գործեք պայմանական ձևաչափման երկխոսություն Excel- ի ձեր տարբերակում: Excel 2003 -ում դա անում եք ՝ Ձևաչափի ընտրացանկից ընտրելով Պայմանական ձևաչափում, մինչդեռ Excel 2007 և 2010 թվականներին կտտացնում եք Պայմանական ձևաչափման կոճակը ՝ Գլխավոր ժապավենի Styles խմբում: Ընտրեք կանոնի տեսակը որպես «Բանաձև» և մուտքագրեք ձեր բանաձևը Խմբագրել կանոնների նկարագրությունը դաշտում:
- Եթե ցանկանում եք ընդգծել առաջին ցուցակին հատուկ գրառումները, բանաձևը կլինի «= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)», իսկ երկրորդ ցուցակի բջիջների շրջանակը ՝ որպես բացարձակ արժեքներ և հղում առաջին ցուցակի առաջին բջիջին `որպես հարաբերական արժեք: (Մի՛ մտեք փակման մեջբերումներ):
- Եթե ցանկանում եք ընդգծել երկրորդ ցուցակին հատուկ գրառումները, բանաձևը կլինի «= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)», իսկ առաջին ցուցակի բջիջների շրջանակը ՝ որպես բացարձակ արժեքներ և հղում երկրորդ ցուցակի առաջին բջիջին `որպես հարաբերական արժեք: (Մի՛ մտեք փակման մեջբերումներ):
- Եթե ցանկանում եք ընդգծել յուրաքանչյուր ցուցակի գրառումները, որոնք գտնվում են մյուս ցուցակում, ձեզ հարկավոր է երկու բանաձև ՝ մեկը առաջին ցուցակի և երկրորդի համար: Առաջին ցուցակի բանաձևը "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", իսկ երկրորդ ցուցակի բանաձևը `COUNTIF ($ G $ 3: $ G $ 14, L3> 0)": Ինչպես Նախկինում նշված, դուք ընտրում եք առաջին ցուցակը `դրա բանաձևը կիրառելու համար, այնուհետև ընտրում եք երկրորդ ցուցակը` դրա բանաձևը կիրառելու համար:
- Կիրառեք ցանկացած ձևաչափ, որը ցանկանում եք ընդգծել նշվող նշվող գրառումները: Կտտացրեք OK ՝ երկխոսությունը փակելու համար:
Տեսանյութ - Այս ծառայությունից օգտվելով ՝ որոշ տեղեկություններ կարող են կիսվել YouTube- ի հետ:
Խորհուրդներ
- COUNTIF պայմանական ձևաչափման եղանակով բջջային հղում օգտագործելու փոխարեն կարող եք մուտքագրել որոնվող արժեքը և այդ արժեքի օրինակների համար նշել մեկ կամ մի քանի ցուցակներ:
- Համեմատության ձևերը պարզեցնելու համար կարող եք անուններ ստեղծել ձեր ցուցակի համար, օրինակ ՝ «List1» և «List2»: Այնուհետև, բանաձևերը գրելիս, այս ցուցակների անունները կարող են փոխարինել վերը նշված օրինակներում օգտագործված բջիջների բացարձակ տիրույթներին:
