Excel-ի կյանքի հաքերները նրանց համար, ովքեր ներգրավված են հաշվետվությունների և տվյալների մշակման մեջ

2024 Հեղինակ: Malcolm Clapton | [email protected]. Վերջին փոփոխված: 2024-01-13 01:54

Այս գրառման մեջ Mann, Ivanov and Ferber հրատարակչության գլխավոր տնօրենի օգնական Ռենատ Շագաբուտդինովը կիսվում է Excel-ի մի քանի հիանալի հաքերներով: Այս խորհուրդները օգտակար կլինեն բոլորի համար, ովքեր ներգրավված են տարբեր հաշվետվությունների, տվյալների մշակման և ներկայացումների ստեղծման մեջ:

Այս հոդվածը պարունակում է պարզ տեխնիկա Excel-ում ձեր աշխատանքը պարզեցնելու համար: Դրանք հատկապես օգտակար են նրանց համար, ովքեր զբաղվում են կառավարման հաշվետվություններով, պատրաստում են տարբեր վերլուծական հաշվետվություններ՝ հիմնվելով 1C-ից և այլ հաշվետվությունների ներբեռնումների վրա, կառավարման համար կազմում դրանցից ներկայացումներ և դիագրամներ: Ես բացարձակ նորություն չեմ հավակնում. այս կամ այն ձևով այս տեխնիկան, հավանաբար, քննարկվել է ֆորումներում կամ նշվել հոդվածներում:

VLOOKUP-ի և HLOOKUP-ի պարզ այլընտրանքներ, եթե ցանկալի արժեքները չկան աղյուսակի առաջին սյունակում՝ Փնտրել, INDEX + SEARCH:

VLOOKUP և HLOOKUP գործառույթներն աշխատում են միայն այն դեպքում, եթե ցանկալի արժեքները գտնվում են աղյուսակի առաջին սյունակում կամ տողում, որտեղից նախատեսում եք ստանալ տվյալներ:

Հակառակ դեպքում կա երկու տարբերակ.

1. Օգտագործեք LOOKUP ֆունկցիան:

   Այն ունի հետևյալ շարահյուսությունը՝ LOOKUP (որոնման_արժեք; փնտրման_վեկտոր; արդյունք_վեկտոր): Բայց որպեսզի այն ճիշտ աշխատի, view_vector տիրույթի արժեքները պետք է տեսակավորվեն աճման կարգով.

   

2. Օգտագործեք MATCH և INDEX ֆունկցիաների համադրություն:

   MATCH ֆունկցիան վերադարձնում է զանգվածի տարրի հերթական համարը (դրա օգնությամբ կարող եք գտնել, թե աղյուսակի որ տողում է որոնված տարրը), իսկ INDEX ֆունկցիան վերադարձնում է զանգվածի տարրը տրված թվով (որը մենք կպարզենք. օգտագործելով MATCH ֆունկցիան):

   

   Ֆունկցիայի շարահյուսություն.

   • SEARCH (search_value; search_array; match_type) - մեր դեպքի համար մեզ անհրաժեշտ է «ճշգրիտ համընկնում» տիպը, այն համապատասխանում է 0 թվին:

   • INDEX (զանգված; տողի_համար; [սյունակի_համար]): Այս դեպքում ձեզ հարկավոր չէ նշել սյունակի համարը, քանի որ զանգվածը բաղկացած է մեկ տողից:

Ինչպես արագ լրացնել ցուցակի դատարկ բջիջները

Խնդիրն է լրացնել սյունակի բջիջները վերևում գտնվող արժեքներով (այնպես, որ թեման լինի աղյուսակի յուրաքանչյուր տողում, և ոչ միայն թեմայի վերաբերյալ գրքերի բլոկի առաջին շարքում).

Ընտրեք «Թեմա» սյունակը, սեղմեք «Տուն» խմբի ժապավենի վրա, «Գտեք և ընտրեք» կոճակը → «Ընտրեք բջիջների խումբ» → «Դատարկ բջիջներ» և սկսեք մուտքագրել բանաձևը (այսինքն՝ դրեք հավասար ստորագրեք) և վերևի բջիջին վերաբերվեք՝ պարզապես սեղմելով ստեղնաշարի վերև սլաքը: Դրանից հետո սեղմեք Ctrl + Enter: Դրանից հետո ստացված տվյալները կարող եք պահպանել որպես արժեքներ, քանի որ բանաձևերը այլևս կարիք չունեն.

Ինչպես գտնել սխալները բանաձևում

Բանաձևի առանձին մասի հաշվարկ

Բարդ բանաձևը հասկանալու համար (որում այլ գործառույթներ օգտագործվում են որպես ֆունկցիայի արգումենտներ, այսինքն՝ որոշ գործառույթներ տեղադրված են մյուսների մեջ) կամ դրանում սխալների աղբյուրը գտնելու համար, հաճախ անհրաժեշտ է հաշվարկել դրա մի մասը: Երկու հեշտ ճանապարհ կա.

1. Բանաձևի մի մասը հենց բանաձևի տողում հաշվարկելու համար ընտրեք այդ մասը և սեղմեք F9:

   

   Այս օրինակում խնդիր կար SEARCH ֆունկցիայի հետ. արգումենտները փոխարինվեցին դրանում: Կարևոր է հիշել, որ եթե չչեղարկեք ֆունկցիայի մասի հաշվարկը և սեղմեք Enter, ապա հաշվարկված մասը կմնա թիվ։

2. Կտտացրեք «Հաշվարկել բանաձևը» կոճակը բանաձևերի խմբում ժապավենի վրա.

   

   Բացվող պատուհանում կարող եք քայլ առ քայլ հաշվարկել բանաձևը և որոշել, թե որ փուլում և որ գործառույթում է տեղի ունենում սխալ (եթե այդպիսիք կան).

   

Ինչպես որոշել, թե բանաձևը ինչից է կախված կամ ինչին է վերաբերում

Որոշելու համար, թե որ բջիջներից է կախված բանաձևը, ժապավենի վրա գտնվող Բանաձևեր խմբում կտտացրեք «Ազդող բջիջների» կոճակը.

Սլաքները ցույց են տալիս, թե ինչից է կախված հաշվարկի արդյունքը:

Եթե նկարում կարմիրով ընդգծված խորհրդանիշը ցուցադրվում է, ապա բանաձևը կախված է այլ թերթիկների կամ այլ գրքերի բջիջներից.

Սեղմելով դրա վրա՝ մենք կարող ենք ճշգրիտ տեսնել, թե որտեղ են գտնվում ազդող բջիջները կամ միջակայքերը.

«Ազդող բջիջների» կոճակի կողքին կա «Կախված բջիջներ» կոճակը, որն աշխատում է նույն կերպ՝ այն ցույց է տալիս սլաքները ակտիվ բջիջից բանաձևով դեպի դրանից կախված բջիջները։

«Հեռացնել սլաքները» կոճակը, որը գտնվում է նույն բլոկում, թույլ է տալիս հեռացնել սլաքները դեպի ազդող բջիջները, սլաքները դեպի կախված բջիջները կամ երկու տեսակի սլաքները միանգամից.

Ինչպես գտնել բազմաթիվ թերթերից բջիջների արժեքների գումարը (թիվը, միջինը):

Ենթադրենք, դուք ունեք միևնույն տիպի մի քանի թերթ՝ տվյալների հետ, որոնք ցանկանում եք ավելացնել, հաշվել կամ մշակել այլ կերպ.

Դա անելու համար այն բջիջում, որտեղ ցանկանում եք տեսնել արդյունքը, մուտքագրեք ստանդարտ բանաձև, օրինակ՝ SUM (SUM) և նշեք առաջին և վերջին թերթերի անվանումը այն թերթերի ցանկից, որոնք դուք պետք է մշակեք: փաստարկը՝ բաժանված երկու կետով.

Դուք կստանաք B3 հասցեով բջիջների գումարը «Տվյալներ1», «Տվյալներ2», «Տվյալներ3» թերթերից.

Այս հասցեավորումն աշխատում է տեղակայված թերթիկների համար հետեւողականորեն … Շարահյուսությունը հետևյալն է. = FUNCTION (first_list: last_list! Range reference):

Ինչպես ավտոմատ կերպով կառուցել կաղապարային արտահայտություններ

Օգտագործելով Excel-ում տեքստի հետ աշխատելու հիմնական սկզբունքները և մի քանի պարզ գործառույթներ, կարող եք պատրաստել կաղապարային արտահայտություններ զեկույցների համար: Տեքստի հետ աշխատելու մի քանի սկզբունքներ.

• Մենք միացնում ենք տեքստը՝ օգտագործելով & նշանը (կարող եք այն փոխարինել CONCATENATE ֆունկցիայով, բայց դա այնքան էլ իմաստ չունի):
• Տեքստը միշտ գրված է չակերտներով, տեքստով բջիջներին հղումները միշտ առանց:
• «Չակերտներ» ծառայության նիշը ստանալու համար օգտագործեք CHAR ֆունկցիան 32 փաստարկով:

Բանաձևերի միջոցով ձևանմուշ արտահայտություն ստեղծելու օրինակ.

Արդյունք:

Այս դեպքում, բացի CHAR ֆունկցիայից (մեջբերումներ ցուցադրելու համար), օգտագործվում է IF ֆունկցիան, որը թույլ է տալիս փոխել տեքստը՝ կախված վաճառքի դրական միտումի առկայությունից, և TEXT ֆունկցիան, որը թույլ է տալիս ցուցադրել համարը ցանկացած ձևաչափով: Դրա շարահյուսությունը նկարագրված է ստորև.

TEXT (արժեք; ձևաչափ)

Ձևաչափը նշված է չակերտների մեջ, ճիշտ այնպես, ինչպես դուք մուտքագրում եք հատուկ ձևաչափ Format Cells պատուհանում:

Ավելի բարդ տեքստերը կարող են նաև ավտոմատացվել: Իմ պրակտիկայում եղել է կառավարման հաշվետվությունների երկար, բայց սովորական մեկնաբանությունների ավտոմատացում «ՑՈՒՑԻՉԸ պլանի համեմատ նվազել է/բարձրացել է XX-ով, հիմնականում FACTOR1-ի աճի/անկման շնորհիվ XX-ով, FACTOR2-ի աճի/անկման պատճառով: YY …» գործոնների փոփոխվող ցանկով: Եթե դուք հաճախ եք գրում նման մեկնաբանություններ, և դրանք գրելու գործընթացը կարող է ալգորիթմացվել, ապա արժե մեկ անգամ տարակուսել ստեղծել բանաձև կամ մակրո, որը կփրկի ձեզ աշխատանքի գոնե մի մասը:

Ինչպես պահել տվյալները յուրաքանչյուր բջիջում միացումից հետո

Երբ միաձուլում եք բջիջները, պահպանվում է միայն մեկ արժեք: Excel-ը զգուշացնում է այս մասին, երբ փորձում են միաձուլել բջիջները.

Համապատասխանաբար, եթե յուրաքանչյուր բջիջից կախված բանաձև ունեիք, դրանք համատեղելուց հետո այն կդադարի աշխատել (# N / Սխալ օրինակի 3-4 տողերում).

Բջիջները միաձուլելու և դրանցից յուրաքանչյուրի տվյալները դեռ պահպանելու համար (գուցե դուք ունեք բանաձև, ինչպես այս վերացական օրինակում. գուցե ցանկանում եք միաձուլել բջիջները, բայց բոլոր տվյալները պահել ապագայի համար կամ դիտավորյալ թաքցնել), միացրեք թերթի ցանկացած բջիջ:, ընտրեք դրանք, այնուհետև օգտագործեք Format Painter հրամանը՝ ձևաչափումը փոխանցելու այն բջիջներին, որոնք դուք պետք է համատեղեք.

Ինչպես կառուցել առանցք մի քանի տվյալների աղբյուրներից

Եթե Ձեզ անհրաժեշտ է տվյալների մի քանի աղբյուրներից միանգամից առանցք կառուցել, ապա պետք է ժապավենին կամ արագ մուտքի վահանակին ավելացնեք «PivotTable and Chart Wizard»-ը, որն ունի նման տարբերակ:

Դուք կարող եք դա անել հետևյալ կերպ՝ «Ֆայլ» → «Ընտրանքներ» → «Արագ մուտքի գործիքագոտի» → «Բոլոր հրամանները» → «Առանցքային աղյուսակ և գծապատկերի մոգ» → «Ավելացնել».

Դրանից հետո ժապավենի վրա կհայտնվի համապատասխան պատկերակ, կտտացնելով, որի վրա կանչում է նույն հրաշագործը.

Երբ սեղմում եք դրա վրա, երկխոսության տուփ է հայտնվում.

Դրանում դուք պետք է ընտրեք «Մի քանի համախմբման միջակայքում» կետը և սեղմեք «Հաջորդ»: Հաջորդ քայլում կարող եք ընտրել «Ստեղծել մեկ էջի դաշտ» կամ «Ստեղծել էջի դաշտեր»: Եթե ցանկանում եք ինքնուրույն անուն գտնել տվյալների աղբյուրներից յուրաքանչյուրի համար, ընտրեք երկրորդ կետը.

Հաջորդ պատուհանում ավելացրեք բոլոր տիրույթները, որոնց հիման վրա կկառուցվի առանցքը և տվեք նրանց անունները.

Դրանից հետո վերջին երկխոսության վանդակում նշեք, թե որտեղ կտեղադրվի առանցքային աղյուսակի հաշվետվությունը՝ գոյություն ունեցող կամ նոր թերթի վրա.

Առանցքային աղյուսակի հաշվետվությունը պատրաստ է: «Էջ 1» ֆիլտրում անհրաժեշտության դեպքում կարող եք ընտրել տվյալների աղբյուրներից միայն մեկը.

Ինչպես հաշվարկել B տեքստում A տեքստի առաջացման քանակը («ՄՏՍ ՍուպերՄՏՍ սակագին»՝ MTS հապավումի երկու երևույթ)

Այս օրինակում A սյունակը պարունակում է մի քանի տեքստային տողեր, և մեր խնդիրն է պարզել, թե դրանցից յուրաքանչյուրը քանի անգամ է պարունակում E1 բջիջում գտնվող որոնման տեքստը.

Այս խնդիրը լուծելու համար դուք կարող եք օգտագործել բարդ բանաձև, որը բաղկացած է հետևյալ գործառույթներից.

1. DLSTR (LEN) - հաշվարկում է տեքստի երկարությունը, միակ փաստարկը տեքստն է: Օրինակ՝ DLSTR («մեքենա») = 6:
2. SUBSTITUTE - տեքստային տողի որոշակի տեքստը փոխարինում է մեկ այլով: Շարահյուսություն՝ SUBSTITUTE (տեքստ; հին_տեքստ; նոր_տեքստ): Օրինակ՝ SUBSTITUTE («մեքենա»; «auto»; «») = «շարժական»:
3. UPPER - տողի բոլոր նիշերը փոխարինում է մեծատառերով: Միակ փաստարկը տեքստն է։ Օրինակ՝ UPPER («մեքենա») = «CAR»: Մեզ անհրաժեշտ է այս ֆունկցիան՝ մեծատառերի անզգայուն որոնումներ կատարելու համար: Ի վերջո, UPPER («մեքենա») = UPPER («Մեքենա»)

Որոշակի տեքստային տողի հայտնվելը մյուսում գտնելու համար հարկավոր է ջնջել դրա բոլոր երևույթները բնօրինակում և համեմատել ստացված տողի երկարությունը բնօրինակի հետ.

DLSTR («Սակագնային MTS Super MTS») - DLSTR («Սակագնային սուպեր») = 6

Եվ այնուհետև բաժանեք այս տարբերությունը մեր փնտրած տողի երկարությամբ.

6 / DLSTR («ՄՏՍ») = 2

«ՄՏՍ» տողն ընդգրկված է հենց երկու անգամ բնօրինակի մեջ։

Մնում է գրել այս ալգորիթմը բանաձևերի լեզվով (եկեք «տեքստով» նշենք այն տեքստը, որում մենք փնտրում ենք երևույթներ, իսկ «փնտրումներով»՝ նա, ում երևույթների քանակը մեզ հետաքրքրում է).

= (DLSTR (տեքստ) -LSTR (SUBSTITUTE (UPPER (տեքստ); UPPER (որոնում), ""))) / DLSTR (որոնում)

Մեր օրինակում բանաձևը հետևյալն է.

= (DLSTR (A2) -LSTR (ՓՈԽԱՐԱԿԻՑ (ՎԵՐԻՆ (A2), UPPER ($ E $ 1), «»))) / DLSTR ($ E $ 1)