Ստեղծեք տվյալների բազա Excel-ում

Տվյալների շտեմարանները (DB) նշելիս առաջին բանը, որ գալիս է մտքում, իհարկե, բոլոր տեսակի խոսակցական բառերն են, ինչպիսիք են SQL, Oracle, 1C կամ առնվազն Access: Իհարկե, դրանք շատ հզոր (և մեծ մասամբ թանկ) ծրագրեր են, որոնք կարող են ավտոմատացնել մեծ և բարդ ընկերության աշխատանքը, որը շատ տվյալներ ունի: Դժբախտությունն այն է, որ երբեմն նման ուժ պարզապես պետք չէ։ Ձեր բիզնեսը կարող է փոքր լինել և համեմատաբար պարզ բիզնես գործընթացներով, բայց դուք նաև ցանկանում եք այն ավտոմատացնել: Եվ փոքր ընկերությունների համար դա հաճախ գոյատևման խնդիր է:

Սկսենք, եկեք ձևակերպենք TOR-ը: Շատ դեպքերում, հաշվապահական հաշվառման տվյալների բազան, օրինակ, դասական վաճառքները պետք է կարողանան.

• պահել աղյուսակներում տեղեկատվություն ապրանքների (գների), կատարված գործարքների և հաճախորդների մասին և կապել այս աղյուսակները միմյանց հետ
• ունենալ հարմարավետ մուտքագրման ձևեր տվյալներ (բացվող ցուցակներով և այլն)
• ավտոմատ կերպով լրացրեք որոշ տվյալներ տպագիր ձևեր (վճարումներ, հաշիվներ և այլն)
  
• թողարկել անհրաժեշտը Ռեպորտաժ վերահսկել ողջ բիզնես գործընթացը մենեջերի տեսանկյունից

Microsoft Excel-ը կարող է հաղթահարել այս ամենը մի փոքր ջանք գործադրելով: Փորձենք իրականացնել սա։

Քայլ 1. Սկզբնական տվյալները աղյուսակների տեսքով

Մենք ապրանքների, վաճառքի և հաճախորդների մասին տեղեկությունները կպահենք երեք աղյուսակներում (նույն թերթիկի կամ տարբեր թերթիկների վրա՝ կարևոր չէ): Սկզբունքորեն կարևոր է դրանք վերածել «խելացի սեղանների»՝ ավտոմատ չափսերով, որպեսզի հետագայում չմտածեն դրա մասին։ Դա արվում է հրամանով Ձևաչափեք որպես աղյուսակ ականջակալ Գլխավոր (Տուն — Ձևաչափել որպես աղյուսակ). Այնուհետև հայտնվող ներդիրում շինարար (Դիզայն) դաշտում տալ աղյուսակների նկարագրական անուններ Աղյուսակի անվանումը հետագա օգտագործման համար.

Ընդհանուր առմամբ, մենք պետք է ստանանք երեք «խելացի սեղան».

Խնդրում ենք նկատի ունենալ, որ աղյուսակները կարող են պարունակել լրացուցիչ պարզաբանող տվյալներ: Այսպիսով, օրինակ, մեր Գինպարունակում է լրացուցիչ տեղեկություններ յուրաքանչյուր ապրանքի կատեգորիայի (ապրանքի խումբ, փաթեթավորում, քաշ և այլն) և աղյուսակի մասին հաճախորդ — նրանցից յուրաքանչյուրի քաղաքը և շրջանը (հասցե, TIN, բանկային տվյալներ և այլն):

Սեղան Sales այն հետագայում կօգտագործվի մեր կողմից՝ դրանում կատարված գործարքներ մուտքագրելու համար:

Քայլ 2. Ստեղծեք տվյալների մուտքագրման ձև

Իհարկե, դուք կարող եք մուտքագրել վաճառքի տվյալները անմիջապես կանաչ աղյուսակում Sales, բայց դա միշտ չէ, որ հարմար է և հանգեցնում է «մարդկային գործոնի» պատճառով սխալների և տառասխալների ի հայտ գալուն։ Հետևաբար, ավելի լավ կլինի հատուկ ձևաթղթեր պատրաստել՝ նման բանի առանձին թերթիկի վրա տվյալներ մուտքագրելու համար.

B3 բջիջում թարմացված ընթացիկ ամսաթիվ-ժամը ստանալու համար օգտագործեք գործառույթը TDATA (ՀԻՄԱ). Եթե ​​ժամանակ պետք չէ, ապա դրա փոխարեն TDATA գործառույթը կարող է կիրառվել ԱՅՍՕՐ (ԱՅՍՕՐ).

B11 բջիջում գտեք ընտրված ապրանքի գինը խելացի աղյուսակի երրորդ սյունակում Գին օգտագործելով գործառույթը VPR- ը (VLOOKUP). Եթե ​​նախկինում չեք հանդիպել, ապա նախ կարդացեք և դիտեք տեսանյութը այստեղ։

B7 բջիջում մեզ անհրաժեշտ է բացվող ցուցակ՝ գնացուցակից ապրանքներով: Դրա համար կարող եք օգտագործել հրամանը Տվյալներ – Տվյալների վավերացում (Տվյալներ — Վավերացում), նշեք որպես սահմանափակում Ցուցակ (Ցուցակ) իսկ հետո մտիր դաշտ Աղբյուր (Աղբյուր) հղում սյունակին Անուն մեր խելացի սեղանից Գին:

Նմանապես, ստեղծվում է հաճախորդների հետ բացվող ցուցակ, բայց աղբյուրը կլինի ավելի նեղ.

=INDIRECT («Հաճախորդներ[Հաճախորդ]»)

ֆունկցիա Անկախ (Անուղղակի) անհրաժեշտ է, այս դեպքում, քանի որ Excel-ը, ցավոք, չի հասկանում աղբյուրի դաշտում խելացի աղյուսակների ուղիղ հղումները: Բայց նույն հղումը «փաթաթված» ֆունկցիայի մեջ Անկախ միևնույն ժամանակ, այն աշխատում է պայթյունով (այս մասին ավելին եղել է բովանդակությամբ բացվող ցուցակներ ստեղծելու մասին հոդվածում):

Քայլ 3. Վաճառքի մուտքի մակրո ավելացում

Ձևը լրացնելուց հետո անհրաժեշտ է դրանում մուտքագրված տվյալները ավելացնել աղյուսակի վերջում Sales. Օգտագործելով պարզ հղումներ, մենք կկազմենք տող, որը կավելացվի հենց ձևի տակ.

Նրանք. A20 բջիջը կունենա հղում դեպի =B3, B20 բջիջը կունենա հղում դեպի =B7 և այլն:

Հիմա եկեք ավելացնենք 2 տող տարրական մակրո, որը պատճենում է ստեղծված տողը և ավելացնում այն ​​Sales աղյուսակում: Դա անելու համար սեղմեք համադրությունը Alt + F11 կամ կոճակ Visual Basic ականջակալ երեվակիչ (մշակող). Եթե ​​այս ներդիրը տեսանելի չէ, ապա նախ միացրեք այն կարգավորումներում Ֆայլ – Ընտրանքներ – Ժապավենի տեղադրում (Ֆայլ — Ընտրանքներ — Անհատականացնել ժապավենը). Visual Basic-ի խմբագրիչի պատուհանում, որը բացվում է, ցանկի միջոցով տեղադրեք նոր դատարկ մոդուլ Ներդիր – մոդուլ և այնտեղ մուտքագրեք մեր մակրո կոդը.

Sub Add_Sell() Worksheets("Input Form").Range("A20:E20").Copy 'Copy the data line from form n = Worksheets("Sales").Range("A100000").End(xlUp) . Տող «որոշեք աղյուսակի վերջին տողի թիվը: Վաճառքի աշխատաթերթեր («Վաճառք»). Բջիջներ (n + 1, 1). PasteSpecial Paste:=xlPasteValues ​​'տեղադրել հաջորդ դատարկ տողում Worksheets («Input Form»). Range («B5,B7,B9»): ClearContents «մաքրել վերջի ենթաբաժինը  

Այժմ մենք կարող ենք կոճակ ավելացնել մեր ձևին՝ ստեղծված մակրո գործարկելու համար՝ օգտագործելով բացվող ցուցակը Տեղադրել ականջակալ երեվակիչ (Մշակող — Տեղադրել — կոճակ):

Այն նկարելուց հետո, մկնիկի ձախ կոճակը սեղմած պահելով, Excel-ը կհարցնի ձեզ, թե որ մակրո պետք է նշանակեք դրան. ընտրեք մեր մակրոն: Add_Sell. Դուք կարող եք փոխել տեքստը կոճակի վրա՝ աջ սեղմելով դրա վրա և ընտրելով հրամանը Փոխել տեքստը.

Այժմ ձևը լրացնելուց հետո կարող եք պարզապես սեղմել մեր կոճակը, և մուտքագրված տվյալները ավտոմատ կերպով կավելացվեն աղյուսակում: Sales, և այնուհետև ձևը մաքրվում է նոր գործարք մտնելու համար:

Քայլ 4 Աղյուսակների միացում

Նախքան հաշվետվությունը կազմելը, եկեք կապենք մեր աղյուսակները, որպեսզի հետագայում կարողանանք արագ հաշվարկել վաճառքներն ըստ տարածաշրջանի, հաճախորդի կամ կատեգորիայի: Excel-ի հին տարբերակներում դա կպահանջի մի քանի գործառույթների օգտագործում: VPR- ը (VLOOKUP) սեղանին գները, կատեգորիաները, հաճախորդները, քաղաքները և այլն փոխարինելու համար Sales. Սա մեզանից ժամանակ և ջանք է պահանջում, ինչպես նաև «ուտում» է Excel-ի շատ ռեսուրսներ: Սկսած Excel 2013-ից, ամեն ինչ կարելի է իրականացնել շատ ավելի պարզ՝ աղյուսակների միջև հարաբերություններ ստեղծելով:

Դա անելու համար ներդիրում Ամսաթիվ (Ամսաթիվ) սեղմել Հարաբերություններ (հարաբերություններ). Պատուհանում, որը հայտնվում է, սեղմեք կոճակը Ստեղծել (նոր) և բացվող ցուցակներից ընտրեք աղյուսակները և սյունակների անունները, որոնցով դրանք պետք է կապված լինեն.

Կարևոր կետ. աղյուսակները պետք է նշված լինեն այս հերթականությամբ, այսինքն՝ կապակցված աղյուսակը (Գին) չպետք է պարունակի հիմնական սյունակում (Անուն) կրկնօրինակ ապրանքներ, ինչպես դա տեղի է ունենում աղյուսակում Sales. Այլ կերպ ասած, կապված աղյուսակը պետք է լինի այնպիսին, որտեղ դուք կարող եք փնտրել տվյալներ՝ օգտագործելով VPR- ըեթե այն օգտագործվեր.

Իհարկե, աղյուսակը միացված է նմանատիպ ձևով Sales սեղանի հետ հաճախորդ ընդհանուր սյունակով Հաճախորդ:

Հղումները տեղադրելուց հետո հղումների կառավարման պատուհանը կարող է փակվել. դուք պետք չէ կրկնել այս ընթացակարգը.

Քայլ 5. Մենք կառուցում ենք հաշվետվություններ՝ օգտագործելով ամփոփագիրը

Այժմ, վաճառքը վերլուծելու և գործընթացի դինամիկան հետևելու համար, եկեք ստեղծենք, օրինակ, ինչ-որ հաշվետվություն՝ օգտագործելով առանցքային աղյուսակը: Ակտիվ բջիջը դնել աղյուսակին Sales և ընտրեք ժապավենի ներդիրը Տեղադրել – PivotTable (Տեղադրեք - առանցքային աղյուսակ). Բացվող պատուհանում Excel-ը մեզ կհարցնի տվյալների աղբյուրի (այսինքն՝ աղյուսակ Sales) և զեկույցը վերբեռնելու տեղ (ցանկալի է նոր թերթի վրա).

Կարևորն այն է, որ անհրաժեշտ է միացնել վանդակը Այս տվյալները ավելացրեք տվյալների մոդելին (Տվյալների ավելացում տվյալների մոդելում) պատուհանի ներքևում, որպեսզի Excel-ը հասկանա, որ մենք ցանկանում ենք հաշվետվություն կազմել ոչ միայն ընթացիկ աղյուսակի վրա, այլև օգտագործել բոլոր հարաբերությունները:

Սեղմելուց հետո OK պատուհանի աջ կեսում կհայտնվի վահանակ Պտտեք աղյուսակի դաշտերըորտեղ սեղմել հղումը բոլորըտեսնել ոչ միայն ներկայիս, այլև գրքում գտնվող բոլոր «խելացի աղյուսակները»։ Եվ այնուհետև, ինչպես դասական առանցքային աղյուսակում, դուք կարող եք պարզապես քաշել մեզ անհրաժեշտ դաշտերը ցանկացած հարակից աղյուսակներից դեպի տարածք ֆիլտր, Rows, Ստոլբցով or Արժեքներն – և Excel-ը ակնթարթորեն կստեղծի մեզ անհրաժեշտ ցանկացած զեկույց թերթիկի վրա.

Մի մոռացեք, որ առանցքային աղյուսակը պետք է պարբերաբար թարմացվի (երբ աղբյուրի տվյալները փոխվում են)՝ աջ սեղմելով դրա վրա և ընտրելով հրամանը։ Թարմացնել և պահպանել (Թարմացնել), քանի որ այն չի կարող դա անել ավտոմատ կերպով:

Նաև ամփոփման մեջ ընտրելով ցանկացած բջիջ և սեղմելով կոճակը Առանցքային աղյուսակ (Առանցքային գծապատկեր) ականջակալ վերլուծություն (Վերլուծություն) or Պարամետրեր (Ընտրանքներ) դուք կարող եք արագ պատկերացնել դրա մեջ հաշվարկված արդյունքները:

Քայլ 6. Լրացրե՛ք տպագրվող նյութերը

Ցանկացած տվյալների բազայի մեկ այլ բնորոշ խնդիր է տարբեր տպագիր ձևաթղթերի և ձևաթղթերի ավտոմատ լրացումը (հաշիվներ, հաշիվ-ապրանքագրեր, ակտեր և այլն): Ես արդեն գրել եմ դա անելու եղանակներից մեկի մասին։ Այստեղ մենք իրականացնում ենք, օրինակ, լրացնել ձևը հաշվի համարով.

Ենթադրվում է, որ C2 բջիջում օգտվողը մուտքագրելու է թիվ (տողի համարը աղյուսակում Sales, փաստորեն), և այնուհետև մեզ անհրաժեշտ տվյալները վեր են հանվում՝ օգտագործելով արդեն ծանոթ գործառույթը VPR- ը (VLOOKUP) եւ առանձնահատկություններ INDEX (INDEX).

• Ինչպես օգտագործել VLOOKUP ֆունկցիան արժեքներ փնտրելու և որոնելու համար
• Ինչպես փոխարինել VLOOKUP-ը INDEX և MATCH ֆունկցիաներով
• Ձևաթղթերի և ձևաթղթերի ավտոմատ լրացում աղյուսակից ստացված տվյալներով
• Զեկույցների ստեղծում PivotTables-ով