Ընտրված հարցման էությունն այն է, որ աղբյուրի աղյուսակից ընտրվեն որոշակի չափանիշներին համապատասխանող տողեր (նման է ստանդարտի օգտագործման): Եկեք արժեքներ ընտրենք աղբյուրի աղյուսակից `օգտագործելով: Ի տարբերություն օգտագործման ( CTRL + SHIFT + Lկամ Տվյալներ / տեսակավորում և զտիչ / զտիչ) ընտրված տողերը կտեղադրվեն առանձին աղյուսակում:
Այս հոդվածում մենք կքննարկենք ամենատարածված հարցումները, օրինակ. տողերի ընտրություն, որոնց ամսաթիվը որոշակի ժամանակաշրջան; առաջադրանքներ 2 տեքստային չափանիշներով և այլն: Սկսենք պարզ հարցադրումներից:
1. Մեկ թվային չափանիշ (ընտրեք այն ապրանքները, որոնց գինը նվազագույնից բարձր է)
օրինակ ֆայլ, թերթ Չափանիշներից մեկը թիվն է ).
Անհրաժեշտ է առանձին աղյուսակում ցուցադրել Աղբյուրի աղյուսակից միայն այն գրառումները (տողերը), որոնց արժեքը 25 -ից բարձր է:
Այս և հետագա խնդիրները հեշտությամբ կարող եք լուծել օգնությամբ: Դա անելու համար ընտրեք Աղբյուրի աղյուսակի վերնագրերը և սեղմեք CTRL + SHIFT + L... Օգտագործելով բացվող ցանկը վերնագրում Գները ընտրեք Թվերի զտիչներ ..., ապա սահմանեք անհրաժեշտ զտման պայմանները և կտտացրեք OK:
Ընտրության չափանիշներին համապատասխանող գրառումները կցուցադրվեն:
Մեկ այլ մոտեցում է օգտագործել: Ի տարբերություն ընտրված տողերի, դրանք կտեղադրվեն առանձին աղյուսակում `մի տեսակ, որը, օրինակ, կարող է ձևափոխվել Աղյուսակի տարբերվող ոճով կամ կատարել այլ փոփոխություններ:
Տեղադրեք չափանիշը (նվազագույն գինը) բջիջում E6 , զտված տվյալների աղյուսակ `տիրույթում D10: E19 .
Այժմ եկեք ընտրենք շրջանակը D11: D19 (սյունակ Ապրանք) և մուտքագրեք ՝
INDEX (A11: A19;
ՓՈՔՐ (ԵԹԵ ($ E $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-ԼԱՅՆ ($ B $ 10))
Փոխարեն ՄՏՆԵԼսեղմել ստեղնաշարի դյուրանցումը CTRL + SHIFT + ENTER(զանգվածի բանաձևը կլինի):
E11: E19 (սյունակ Գինը), որտեղ մենք մուտքագրելու ենք նմանատիպ մեկը.
INDEX (B11: B19;
ՓՈՔՐ (ԵԹԵ ($ E $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-ԼԱՅՆ ($ B $ 10))
Արդյունքում մենք կստանանք նոր աղյուսակ, որը կպարունակի միայն ապրանքներ, որոնց գները ոչ պակաս են, քան նշված է բջիջում E6 .
Ստացված Նմուշի հարցման դինամիկան ցույց տալու համար մենք ներկայացնում ենք E6 արժեքը 55. Նոր աղյուսակում կհայտնվի ընդամենը 2 գրառում:
Եթե Աղբյուրի աղյուսակին ավելացնեք 80 Գնով նոր ապրանք, ապա նոր աղյուսակ ինքնաբերաբար կավելացվի նոր աղյուսակին:
Նշում... Կարող եք նաև օգտագործել և ցուցադրել զտված տվյալները: Որոշակի գործիքի ընտրությունը կախված է օգտագործողի առջև դրված առաջադրանքից:
Եթե ձեզ հարմար չէ օգտագործել զանգվածի բանաձևորը վերադարձնում է բազմաթիվ արժեքներ, կարող եք օգտագործել այլ մոտեցում, որը քննարկվում է ստորև բերված բաժիններում. 5. ա, 7, 10 և 11. Այս դեպքերում օգտագործվում են:
2. Երկու թվային չափանիշ (ընտրեք այն ապրանքները, որոնց գինը ընկնում է միջակայքում)
Թող լինի Նախնական աղյուսակ `Ապրանքների և գների ցանկով (տես. օրինակ ֆայլ, թերթԹվերի տիրույթ).
Տեղադրեք չափանիշները (գների ստորին և վերին սահմանները) միջակայքում E5: E6 .
Նրանք եթե Ապրանքի գինը ընկնում է նշված միջակայքում, ապա նման գրառումը կհայտնվի նոր teredտված տվյալների աղյուսակում:
Ի տարբերություն նախորդ առաջադրանքի, մենք կստեղծենք երկուսը ՝ ապրանքներ և գներ (առանց դրանց կարելի է, բայց դրանք հարմար են բանաձևեր գրելիս): Համապատասխան բանաձևերը պետք է փնտրվեն Անվան մենեջերում ( Բանաձևեր / Սահմանված անուններ / Անվան մենեջեր) հետևյալ կերպ (տես ստորև բերված նկարը):
Այժմ եկեք ընտրենք շրջանակը D11: D19 և մենք ներկայացնում ենք.
INDEX (ապրանքներ;
Նվազագույնը (
IF (($ E $ 5<=Цены)*($E$6>= Գները); ԳԻ (Գները); "");
Փոխարեն ՄՏՆԵԼսեղմել ստեղնաշարի դյուրանցումը CTRL + SHIFT + ENTER.
Մենք նույն մանիպուլյացիաները կկատարենք տեսականու հետ E11: E19 որտեղ մենք կներկայացնենք նմանատիպ մեկը.
INDEX (գներ;
Նվազագույնը (
IF (($ E $ 5<=Цены)*($E$6>= Գները); ԳԻ (Գները); "");
LINE (Prices) -LINE ($ B $ 10)) - LINE ($ B $ 10))
Արդյունքում, մենք կստանանք նոր աղյուսակ, որը կպարունակի միայն ապրանքներ, որոնց գները ընկնում են բջիջներում նշված միջակայքի սահմաններում E5 եւ E6 .
Ստացված զեկույցի (ընտրության հարցում) դինամիզմը ցույց տալու համար մենք մուտք ենք գործում E6 արժեքը 65. Աղյուսակի ևս մեկ գրառում կավելանա նոր աղյուսակին `բավարարելով նոր չափանիշը:
Եթե Աղբյուրի աղյուսակին ավելացնեք 25 -ից 65 -ի սահմաններում Գին ունեցող նոր ապրանք, ապա նոր աղյուսակին կավելանա նոր գրառում:
Նմուշի ֆայլը պարունակում է նաև զանգվածային բանաձևեր, որոնք լուծում են սխալները, երբ Գինը սյունակում պարունակում է սխալի արժեք, օրինակ # DIV / 0! (տես թերթիկ Սխալ մշակման ժամանակ).
Հետևյալ խնդիրները լուծվում են նույն ձևով, ուստի դրանք մանրամասն չենք քննարկի:
3. Մեկ չափանիշի ամսաթիվ (ընտրեք այն ապրանքները, որոնց համար Առաքման ամսաթիվը համապատասխանում է նշվածին)
օրինակ ֆայլ, թերթՄեկ չափանիշ `ամսաթիվ).
Տողեր ընտրելու համար օգտագործվում են զանգվածի բանաձևեր, որոնք նման են առաջադրանք 1 -ին (չափանիշի փոխարեն<= используется =):
=INDEX (A12: A20; ՓՈՔՐ (IF ($ E $ 6 = B12: B20; LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B 11 դոլար))
INDEX (B12: B20; ՓՈՔՐ (IF ($ E $ 6 = B12: B20; LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B 11 դոլար))
4. Երկու չափանիշի ամսաթիվ (ընտրեք այն ապրանքները, որոնց համար Առաքման ամսաթիվը ընկնում է միջակայքում)
Ենթադրենք, որ կա Նախնական աղյուսակ ՝ Ապրանքների և Առաքման ամսաթվերի ցանկով (տես. օրինակ ֆայլ, թերթԱմսաթվի տիրույթ).
Նկատի ունեցեք, որ Ամսաթիվ սյունակը ՉՏԵՍՎԱ Է:
Լուծում 1Դուք կարող եք օգտագործել տողերը զտելու համար:
Մտեք բջիջ D12 զանգվածի բանաձև.
INDEX ($ 12: A $ 20;
ՄԵAR (($ E $ 6<=$B$12:$B$20)*($E$7>= $ B $ 12: $ B $ 20) * (LINE ($ B $ 12: $ B $ 20) -LINE ($ B $ 11));
$ J $ 12-LINE (A12) + LINE ($ B $ 11) +1))
Նշում: Բանաձեւը մուտքագրելուց հետո, ENTER ստեղնի փոխարեն, սեղմեք CTRL + SHIFT + ENTER ստեղնաշարի համադրությունը: Այս ստեղնաշարի դյուրանցումն օգտագործվում է զանգվածների բանաձևեր մուտքագրելու համար:
Copանգվածի բանաձևը պատճենեք բջիջների ցանկալի թվին: Բանաձեւը կվերադարձնի միայն Ապրանքների այն արժեքները, որոնք առաքվել են նշված ամսաթվի սահմաններում: Մնացած բջիջները կպարունակեն #NUM! Սխալներ: Սխալներ ներսում օրինակ ֆայլ (Sheet 4 Date Range) .
Նմանատիպ բանաձև պետք է մուտքագրվի E սյունակում նշված ամսաթվերի համար:
Խցում J12 հաշվարկեց աղբյուրի աղյուսակի տողերի քանակը, որոնք համապատասխանում են չափանիշներին.
Հաշվիչ (B12: B20; "> =" & $ E $ 6; B12: B20; "<="&$E$7)
Չափանիշներին համապատասխանող աղբյուրի աղյուսակի տողերն են.
Լուծում 2: Տողեր ընտրելու համար կարող եք օգտագործել Task2- ի նման զանգվածի բանաձևեր (այսինքն).
=INDEX (A12: A20; ՓՈՔՐ (IF (($ E $ 6<=B12:B20)*($E$7>= B12: B20); LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B $ 11))
INDEX (B12: B20; ՓՈՔՐ (IF (($ E $ 6<=B12:B20)*($E$7>= B12: B20); LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B $ 11))
Առաջին բանաձևը մուտքագրելու համար ընտրեք մի շարք բջիջներ G12: G20 ... Բանաձեւը մուտքագրելուց հետո, ENTER սեղմելու փոխարեն, սեղմեք CTRL + SHIFT + ENTER:
Լուծում 3.
Նախ, դուք պետք է հաշվարկեք չափանիշներին համապատասխանող տողերի առաջին և վերջին դիրքերը: Այնուհետեւ տպեք տողերը:
Այս օրինակը մեկ անգամ ևս հստակորեն ցույց է տալիս, թե որքան հեշտ է բանաձևեր գրել:
5. Մեկ չափանիշի ամսաթիվ (ընտրեք այն ապրանքները, որոնց համար Առաքման ամսաթիվը նշվածից ոչ շուտ / ուշ է)
Ենթադրենք, որ կա Նախնական աղյուսակ ՝ Ապրանքների և Առաքման ամսաթվերի ցանկով (տես. օրինակ ֆայլ, թերթ Մեկ չափանիշ `ամսաթիվ (ոչ ուշ, քան) ).
Տողեր ընտրելու համար, որոնց ամսաթիվը ավելի վաղ չէ (ներառյալ հենց ամսաթիվը), օգտագործեք զանգվածի բանաձևը.
=INDEX (A12: A20; ՓՈՔՐ (IF ($ E $ 7)<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
Բացի այդ, օրինակ ֆայլը պարունակում է պայմանների բանաձևեր. Ոչ վաղ (ներառյալ); Ոչ ուշ (ներառյալ); Ոչ ուշ (ներառյալ):
7. Մեկ տեքստի չափանիշ (ընտրեք որոշակի տեսակի ապրանքներ)
Թող լինի Նախնական աղյուսակ `Ապրանքների և գների ցանկով (տես. օրինակ ֆայլ, թերթՄեկ չափանիշ - Տեքստ).
8. Երկու տեքստային չափանիշ (որոշակի ամսվա ընթացքում առաքված որոշակի տեսակի ապրանքներ)
Թող լինի Նախնական աղյուսակ `Ապրանքների և գների ցանկով (տես. օրինակ ֆայլ, թերթ 2 չափանիշ - տեքստ (I) ).
INDEX ($ A $ 11: $ A $ 19;
ՓՈՔՐ (IF (($ F $ 6 = $ A $ 11: $ A $ 19) * ($ F $ 7 = $ B $ 11: $ B $ 19); LINE ($ A $ 11: $ A $ 19) -LINԱՆE ($ A $ 10); 30); LINE (INDIRECT ("A1: A" & ROWS ($ A $ 11: $ A $ 19)))))
Արտահայտություն ($ F $ 6 = $ A $ 11: $ A $ 19) * ($ F $ 7 = $ B $ 11: $ B $ 19)սահմանում է երկու պայմանները (նյութ և ամիս):
Արտահայտություն ԳՈ (ՅՆ (ԱՆՈIՅՆ («A1: A» & ROWS ($ A $ 11: $ A $ 19)))ձևեր (1: 2: 3: 4: 5: 6: 7: 8: 9), այսինքն. տողերի համարները աղյուսակում:
9. Տեքստի երկու չափանիշ (ընտրեք որոշակի տեսակի ապրանքներ)
Թող լինի Նախնական աղյուսակ `Ապրանքների և գների ցանկով (տես. օրինակ ֆայլ, թերթ2 չափանիշ ՝ տեքստ (ԿԱՄ)).
Ի տարբերություն 7 -րդ խնդրի, ընտրեք 2 տիպի ապրանքներով տողեր ():
Arանգվածի բանաձևը օգտագործվում է տողեր ընտրելու համար.
INDEX ($ 11: A $ 19;
ՄԵAR ((($ E $ 6 = $ A $ 11: $ A $ 19) + ($ E $ 7 = $ A $ 11: $ A $ 19)) * (ROW ($ A $ 11: $ A $ 19 ) -LINE ($ A $ 10)); COUNTIF ($ A $ 11: $ A $ 19; $ E $ 6) + COUNTIF ($ A $ 11: $ A $ 19; $ E $ 7) -ROWS ($ $ 11: A11) +1))
Վիճակը ($ E $ 6 = $ A $ 11: $ A $ 19) + ($ E $ 7 = $ A $ 11: $ A $ 19)երաշխավորում է, որ դեղին բջիջներից կընտրվեն ապրանքների միայն նշված տեսակները (Ապրանք 2 և Ապրանք 3): Առաջադրանքի համար օգտագործվում է + (հավելում) նշանը (պետք է բավարարել առնվազն 1 չափանիշ):
Վերոնշյալ արտահայտությունը կվերադարձնի զանգված (0: 0: 0: 0: 1: 1: 1: 0: 0): Բազմապատկելով այն արտահայտությամբ LINE ($ A $ 11: $ A $ 19) -LINE ($ A $ 10), այսինքն ՝ վրա (1: 2: 3: 4: 5: 6: 7: 8: 9), մենք ստանում ենք մի շարք դիրքորոշումներ (սեղանի տողերի համարներ), որոնք համապատասխանում են չափանիշներին: Մեր դեպքում դա կլինի զանգված (0: 0: 0: 0: 5: 6: 7: 0: 0):
Որպես օրինակ, մենք ներկայացնում ենք հետևյալ խնդրի լուծումները. Ընտրեք Ապրանքներ, որոնց գինը գտնվում է որոշակի տիրույթում և կրկնվում է մի քանի անգամ կամ ավելի:
Որպես սկզբնական վերցնենք ապրանքների խմբաքանակների սեղանը:
Ենթադրենք, մեզ հետաքրքրում է, թե քանի և ինչ ապրանքներ են մատակարարվել 1000 ռուբլի գնով: մինչև 2000 ռ. (չափանիշ 1): Ավելին, պետք է լինի առնվազն 3 կուսակցություն նույն գնով (չափանիշ 2):
Լուծումը զանգվածի բանաձև է.
ՓՈՔՐ (ROW ($ A $ 14: $ A $ 27) * ($ C $ 14: $ C $ 27> = $ B $ 7) * ($ C $ 14: $ C $ 27<=$C$7)*($D$14:$D$27>= $ B $ 10); F14 + ($ G $ 8- $ G $ 9))
Այս բանաձևը վերադարձնում է երկու չափանիշներին համապատասխանող տողերի համարները:
Բանաձեւ = SUMPRODUCT (($ C $ 14: $ C $ 27> = $ B $ 7) * ($ C $ 14: $ C $ 27<=$C$7)*($D$14:$D$27>= $ B $ 10))հաշվում է չափանիշներին համապատասխանող տողերի քանակը:
11. Մենք օգտագործում ենք չափանիշի արժեքը (Anyանկացած) կամ (Բոլոր)
Վ օրինակ `« 11. Չափանիշ ցանկացած կամ (բոլորը) »թերթիկի վրա:չափանիշի այս տարբերակն իրականացվել է:
Այս դեպքում բանաձևը պետք է պարունակի IF () գործառույթը: Եթե արժեքը (Բոլորը) ընտրված է, ապա բանաձևը օգտագործվում է արժեքները ցուցադրելու համար ՝ առանց այս չափանիշը հաշվի առնելու: Եթե ընտրված է որևէ այլ արժեք, ապա չափանիշը գործում է սովորական եղանակով:
ԵԹԵ ($ C $ 8 = "(Բոլորը)";
ՓՈՔՐ ((LINE ($ B $ 13: $ B $ 26) -LINE ($ B $ 12)) * ($ D $ 13: $ D $ 26> = $ D $ 8); F13 + ($ G $ 6 - $ G $ 7));
ՓՈՔՐ ((LINE ($ B $ 13: $ B $ 26) -LINE ($ B $ 12)) * ($ D $ 13: $ D $ 26> = $ D $ 8) * ($ C $ 13: $ C $ 26 = $ C $ 8); F13 + ($ G $ 6- $ G $ 7)))
Մնացած բանաձևը նման է վերը քննարկվածներին:
Տվյալների բեռնում
Ստեղծեք հաշվետվություն 5 -րդ թերթիկի նմուշի վերաբերյալ «Որակյալ ցուցանիշ, տոկոս» սյունակում: (թերթ 8 -ից, աղյուսակ 7)
Տվյալների նմուշառման համար դուք պետք է կատարեք հետևյալ քայլերը.
Որոշեք նոր զանգվածի տարրերի քանակը ըստ տվյալ պայմանի ՝ փոփոխական մուտքագրելով InputBox օպերատորի միջոցով
Հայտարարեք և վերահռչակեք նոր զանգված
Ձևավորել նոր զանգված: Դա անելու համար հարկավոր է սահմանել u զանգվածի նոր զանգվածի առաջին տարրի համարը u = 1: Այնուհետև կատարվում է մի ցիկլ, որում ընտրության պայմանը գրվում է ըստ «Որակյալ կատարման, տոկոսների» սյունակի: Եթե ստուգման արդյունքը ճշմարիտ է, ապա վերլուծված զանգվածի տարրը դառնում է նոր զանգվածի տարր:
Elementուցադրել նոր տարր 8 -րդ թերթիկում
Sub ReportSample ()
Թերթեր («Թերթ 8»): Ընտրել
Dim A () Որպես տարբերակ
ReDim A (1 -ից n1, 1 -ից մինչև մ)
VVOD «Թերթ 5», A, n1, մ, 4
C = InputBox («Մուտքագրեք պայման»)
Թերթեր («Թերթ 8»): Բջիջներ (5.11) = C
I = 1 -ից n1- ի համար
Եթե A (i,
8) >
d = d + 1
Թերթեր («Թերթ 8»): Բջիջներ (5,10) = դ
Dim B () Որպես տարբերակ
ReDim B (1 -ից մինչև d, 1 -ից մինչև m)
I = 1 -ից n1- ի համար
Եթե A (i,
8)> Թերթեր ("Sheet8"): Բջիջներ (5.11) Հետո
J = 1 -ի համար մ
B (u, j) = A (i, j)
u = u + 1
I = 1 -ից մինչև d
J = 1 -ի համար մ
Թերթեր («Թերթ 8»): Բջիջներ (i + 4, j) = B (i, j)
Նկար 6 Աղյուսակի տվյալները վերցնելուց հետո
Ընտրության վրա ավտոմատ մակրո ստեղծելը
Մենք միացնում ենք մակրոի ձայնագրությունը: Գործիքներ> Մակրո> Սկսել ձայնագրումը> Լավ: Այնտեղ, որտեղ կանգառի ձայնագրման կոճակն է, կհայտնվի քառակուսի: Sheet5- ում (հաշվետվություն) ընտրեք աղյուսակը `առանց վերնագրերի և ընդհանուր տվյալների, պատճենեք այն Sheet10- ին (ինքնորոշում): Ընտրեք աղյուսակ առանց վերնագրերի և ընտրացանկի տարրում ընտրեք Տվյալներ> terտիչ> Ավտոմատ զտիչ> ընտրեք պայմանը> Լավ: Մենք նշում ենք սյունակը, որով մենք դասավորելու ենք: Ավարտում ենք մակրոի աշխատանքը:
Ենթ Macro2Sample ()
«Macro2Setup մակրո
Թերթեր («Թերթ 5»): Ընտրել
Ընտրություն. Պատճենել
Թերթեր («Թերթ 9»): Ընտրել
ActiveSheet. Կպցնել
Տեսականին («H5: H17»): Ընտրել
Դիմում. CutCopyMode = Սխալ
Ընտրություն. Ավտոմատ զտիչ
ActiveSheet. Տեսականի («$ H $ 5: $ H $ 17»): AutoFilter դաշտ: = 1, չափանիշներ 1: = "> 80", _
Օպերատոր ՝ = xl Եվ
Տեսականին («G22»): Ընտրել
Նկար 7: Աղյուսակի տվյալները ՝ ինքնընտրումից հետո
Առավելագույն և նվազագույն արժեքների որոշում
Որոշեք «Ընդհանուր», «Բացարձակ կատարողական, տոկոս», «Որակյալ կատարողականություն» սյունակների առավելագույն և նվազագույն արժեքները (աղյուսակ 9, թերթ 10)
Առավելագույն և նվազագույն արժեքները որոշելու համար հարկավոր է անել հետևյալը.
Սահմանեք հղման փոփոխականը, որը կլինի ընթացիկ նվազագույնը (առավելագույնը)
Բնակչության յուրաքանչյուր տարր մեկ առ մեկ համեմատվում է ներկայիս նվազագույնի (առավելագույնի) հետ, և եթե այս տարրը չի բավարարում որոնման պայմանները (նվազագույնի դեպքում այն ավելի մեծ է, իսկ առավելագույնի դեպքում ՝ ավելի քիչ) , ապա հղման արժեքը փոխարինվում է համեմատվող տարրի արժեքով
Ընթացիկ նվազագույնի (առավելագույնի) փոփոխականի բոլոր տարրերի ամբողջական սկանավորումից հետո փաստացի նվազագույնը (առավելագույնը) հայտնաբերվում է
Նվազագույնի (առավելագույնի) արժեքը ցուցադրվում է համապատասխան բջիջներում
Sub minmax ()
Dim A () Որպես տարբերակ
n1 = Թերթեր («Թերթ 4»): Բջիջներ (5.12)
m = Թերթեր («Թերթ 2»): Բջիջներ (5.12)
ReDim A (1 -ից n1, 1 -ից մինչև մ)
VVOD «Թերթ 5», A, n1, մ, 4
VIVOD «Թերթ 10», A, n1, մ, 4
VVOD «Թերթ 10», A, n1, մ, 4
J = 3 -ի համար մ
maxA = 0.00001
minA = 1.000.000
I = 1 -ից n1- ի համար
Եթե A (i, j)> maxA Հետո
maxA = A (i, j)
Եթե A (i, j)< minA Then
minA = A (i, j)
Թերթեր («Թերթ 10»): Բջիջներ (i + 4 + 2, j) = maxA
Թերթեր («Թերթ 10»): Բջիջներ (i + 4 + 3, j) = minA
Մակրոն գործողությունների հաջորդականություն է, որը գրանցվել և պահպանվել է հետագա հղումների համար: Պահված մակրոը կարող է նվագարկվել հատուկ հրամանով: Այլ կերպ ասած, դուք կարող եք մակրո մակագրել ձեր գործողությունները, պահպանել այն, այնուհետև թույլատրել այլ օգտվողներին կրկնել մակրոում պահված գործողությունները պարզ ստեղնաշարի միջոցով: Սա հատկապես օգտակար է առանցքային աղյուսակի հաշվետվություններ տարածելիս:
Ենթադրենք, դուք ցանկանում եք ձեր հաճախորդներին հնարավորություն տալ խմբավորելու PivotTable հաշվետվությունները ըստ ամիսների, եռամսյակների և տարիների: Տեխնիկապես, խմբավորման գործընթացը կարող է կատարվել ցանկացած օգտվողի կողմից, սակայն ձեր հաճախորդներից ոմանք գուցե անհրաժեշտ չեն համարում դա հասկանալ: Նման դեպքում կարող եք մեկ մակրո մակագրել ըստ ամսվա խմբավորման, մյուսը `եռամսյակի, իսկ երրորդը` տարեկան: Այնուհետեւ ստեղծեք երեք կոճակ `մեկը յուրաքանչյուր մակրոյի համար: Այնուհետև ձեր ոչ-առանցքային սեղանի հաճախորդներին անհրաժեշտ է միայն սեղմել կոճակը ՝ առանցքային աղյուսակի զեկույցը ճիշտ խմբավորելու համար:
Առանցքային աղյուսակի հաշվետվություններում մակրոներ օգտագործելու հիմնական առավելությունն այն է, որ հաճախորդները հնարավորություն ունենան արագ գործարկել ամփոփ աղյուսակներգործողություններ, որոնք նրանք սովորաբար չեն կարող կատարել: Սա զգալիորեն մեծացնում է տրամադրված տվյալների վերլուծության արդյունավետությունը:
Ներբեռնեք գրառումը ձևաչափով կամ ներբեռնեք օրինակներով (ներսում Excel ֆայլմակրոներով; մատակարարի քաղաքականությունը թույլ չի տալիս ուղղակիորեն այս ձևաչափի ֆայլը վերբեռնել կայք):
Մակրո ձայնագրություն
Նայեք Նկարում ներկայացված առանցքային աղյուսակին: 1. Կարող եք թարմացնել այս առանցքային աղյուսակը ՝ դրա ներսում աջ սեղմելով և ընտրելով Թարմացնել... Եթե առանցքային աղյուսակը թարմացնելիս գործողությունները գրանցել եք որպես մակրո, ապա դուք կամ որևէ այլ օգտվող կարող եք կրկնել այս գործողությունները և թարմացնել առանցքային աղյուսակը `մակրոը գործարկելու արդյունքում:
Բրինձ 1. Այս առանցքային աղյուսակի թարմացման ընթացքում գործողությունների գրանցումը թույլ կտա ապագայում թարմացնել տվյալները `մակրոտնտեսության գործարկման արդյունքում
Մակրո ձայնագրման առաջին քայլը երկխոսության տուփ հրավիրելն է Մակրո ձայնագրություն... Գնալ դեպի ներդիր Մշակողըժապավենը և կտտացրեք կոճակին Մակրո ձայնագրություն... (Եթե չեք կարողանում գտնել ժապավենի ներդիրը Մշակողը, ընտրեք ներդիրը Ֆայլ, և կտտացրեք կոճակին Ընտրանքներ... Երկխոսության դաշտում, որը հայտնվում է Excel- ի ընտրանքներԸնտրեք կատեգորիա Անհատականացնելով ժապավենըիսկ աջ կողմում գտնվող ցուցակում նշեք վանդակը Մշակողը... Արդյունքում, ժապավենի վրա կհայտնվի ներդիր Մշակողը.) Այլընտրանքային եղանակսկսեք մակրո ձայնագրել - կտտացրեք կոճակին (նկ. 2):
Երկխոսության վանդակում Մակրո ձայնագրությունմուտքագրեք մակրոյի վերաբերյալ հետևյալ տվյալները (Նկար 3).
Անունմակրո... Անունը պետք է բնութագրի մակրոի կատարած գործողությունները: Անունը պետք է սկսվի տառով կամ ընդգծվածով. չպետք է պարունակի բացատներ կամ այլ անօրինական նիշեր. չպետք է լինի նույնը, ինչ ներկառուցված Excel անունը կամ աշխատանքային գրքում այլ օբյեկտի անունը:
Համադրությունբանալիներ... Այս դաշտում կարող եք մուտքագրել ցանկացած տառ: Այն կդառնա ստեղնաշարի դյուրանցման մի մասը, որը կօգտագործվի մակրո խաղալու համար: Բանալիների համադրությունը պարտադիր չէ: Լռելյայն, միայն Ctrl- ն է առաջարկվում որպես համադրության սկիզբ: Եթե ցանկանում եք, որ համադրությունը ներառի նաև Shift- ը, մուտքագրեք տառը պատուհանում, մինչ պահում եք Shift ստեղնը
Պահելv... Այստեղ է, որ մակրոը պահվում է: Եթե մտադիր եք առանցքային աղյուսակի զեկույցը տարածել այլ օգտվողների, ընտրեք տարբերակը Սագիրք... Excel- ը նաև թույլ է տալիս պահպանել մակրոը դեպի Նոր գիրքկամ մեջ Անձնական մակրո գիրք.
Նկարագրություն... Այս դաշտը օգտագործվում է ստեղծված մակրոյի նկարագրությունը մուտքագրելու համար:
Բրինձ 3. Պատուհանի կարգավորում Մակրո ձայնագրություն
Քանի որ մակրոն թարմացնում է առանցքային աղյուսակը, ընտրեք անունը Տվյալների թարմացում... Կարող եք նաև մակրո նշանակել համակցությանը Ctrl ստեղներ+ Shift + Q. Հիշեք, որ մակրո ստեղծելուց հետո դուք կօգտագործեք այս ստեղնաշարի դյուրանցումը այն գործարկելու համար: Ընտրեք տարբերակը որպես մակրո պահելու վայր Այս գիրքըև կտտացրեք լավ.
Երկխոսության վրա սեղմելուց հետո Մակրո ձայնագրությունկոճակի վրա լավսկսվում է մակրո ձայնագրությունը: Այս պահին Excel- ում կատարվող բոլոր գործողությունները կգտնվեն:
Աջ սեղմեք առանցքային աղյուսակի տարածքում և ընտրեք Թարմացնել(ինչպես նկ. 1 -ում, բայց մակրո ձայնագրման ռեժիմում): Առանցքային աղյուսակը թարմացնելուց հետո կարող եք դադարեցնել մակրո ձայնագրման գործընթացը `օգտագործելով կոճակը Դադարեցնել ձայնագրությունըներդիրներ Մշակողը... Կամ նորից կտտացրեք նկ. 2
Այսպիսով, դուք պարզապես գրանցել եք ձեր առաջին մակրոը: Այժմ դուք կարող եք կատարել մակրոը `օգտագործելով ստեղնաշարի դյուրանցումը Ctrl + Shift + Q:
Մակրո անվտանգության նախազգուշացում:Պետք է նշել, որ եթե մակրոները գրանցվեն օգտվողի կողմից, դրանք կկատարվեն առանց անվտանգության ենթահամակարգի որևէ սահմանափակումների: Այնուամենայնիվ, բաշխելիս աշխատանքային տետրմակրոներ պարունակող, դուք պետք է այլ օգտվողներին հնարավորություն տաք համոզվելու, որ աշխատանքային ֆայլերի բացման վտանգ չկա, և մակրոների կատարումը չի հանգեցնի համակարգի վիրուսային վարակի: Մասնավորապես, դուք անմիջապես կնկատեք, որ այս գլխում օգտագործված նմուշի ֆայլը լիարժեք չի գործի, եթե հատուկ թույլ չտաք, որ Excel- ը դրա վրա աշխատի մակրոներ:
Ձեր մակրոները անվտանգ պահելու ամենահեշտ ձևը վստահելի տեղադրություն ստեղծելն է `թղթապանակ, որտեղ կտեղադրվեն միայն« Վստահելի »վիրուսազուրկ աշխատանքային գրքեր: Վստահելի տեղադրությունը թույլ է տալիս Ձեզ և Ձեր հաճախորդներին աշխատանքային գրքույկներում կատարել մակրոներ ՝ առանց անվտանգության որևէ սահմանափակման (այս վարքագիծը պահպանվում է այնքան ժամանակ, քանի դեռ աշխատանքային գրքերը վստահելի թղթապանակում են):
Վստահելի վայր ստեղծելու համար հետևեք այս քայլերին:
Ընտրեք ժապավենի ներդիրը Մշակողըև կտտացրեք կոճակին Մակրո անվտանգություն... Էկրանի վրա կհայտնվի երկխոսության տուփ: Վստահության կենտրոն.
Կտտացրեք կոճակին Ավելացնել նոր տեղադրություն.
Կտտացրեք կոճակին Ակնարկնշեք ձեր վստահելի աշխատանքային ֆայլերի թղթապանակը:
Երբ վստահելի վայր նշեք, կամայական մակրոները լռելյայն կաշխատեն դրանում եղած բոլոր աշխատանքային գրքերի համար:
Անվտանգության մոդելը բարելավվել է Excel 2013 -ում: Աշխատանքային գրքույկի ֆայլերը, որոնք նախկինում «վստահելի» էին, այժմ հիշվում են. բացվելուց հետո Excel աշխատանքային գրքերև կտտացրեք կոճակին Ներառել բովանդակությունը Excel- ը հիշում է այս վիճակը: Արդյունքում, այս գիրքը ընկնում է վստահվածների կատեգորիայի մեջ, և դրա հետագա բացման ընթացքում ավելորդ հարցեր չեն տրվում:
Ձևի կառավարման տարրերի միջոցով օգտագործողի միջերեսի ստեղծում
Ctrl + Shift + Q ստեղնաշարի համադրությամբ մակրո գործարկելը կօգնի, երբ առանցքային աղյուսակի զեկույցում կա միայն մեկ մակրո: (Բացի այդ, օգտվողները պետք է իմանան այս համադրությունը): Բայց ենթադրենք, որ ցանկանում եք ձեր հաճախորդներին տրամադրել բազմաթիվ մակրոներ, որոնք կատարում են տարբեր գործողություններ: Այս դեպքում դուք պետք է հաճախորդներին տրամադրեք հասկանալի և պարզ ձևովգործարկել յուրաքանչյուր մակրո առանց հիմնական կոմբինացիաները անգիր կատարելու: Կատարյալ լուծումպարզ է օգտագործողի ինտերֆեյսորպես վերահսկիչների հավաքածու, ինչպիսիք են կոճակները, ոլորման վահանակները և այլ գործիքներ, որոնք թույլ են տալիս մակրոներ կատարել մկնիկի կտտոցներով:
Excel- ն առաջարկում է ձեզ մի շարք գործիքներ, որոնք նախատեսված են անմիջականորեն աղյուսակում օգտագործողի միջերես ստեղծելու համար: Այս գործիքները կոչվում են ձևի հսկողություն: Հիմնական գաղափարն այն է, որ հնարավոր է ձևի հսկողություն տեղադրել աղյուսակև նշանակեք այն մակրո, որը գրանցվել էր ավելի վաղ: Հսկիչին նշանակվելուց հետո մակրոը կգործարկվի ՝ կտտացնելով այդ հսկողության վրա:
Ձևի կառավարման տարրերը կարելի է գտնել խմբում Ձևի վերահսկումժապավենի ներդիրներ Մշակողը... Կառավարիչների պալիտրա բացելու համար սեղմեք կոճակի այս խմբում Տեղադրեք(նկ. 4):
Բրինձ 4. Ձեւի հսկողություն Կոճակ
Խնդրում ենք նկատի ունենալ. Բացի ձևի հսկիչներից, գունապնակը նաև պարունակում է ActiveX վերահսկում... Չնայած դրանք նման են, բայց ծրագրային առումով դրանք բոլորովին այլ օբյեկտներ են: Ձևի վերահսկումիրենց հաշմանդամությամբ և պարզ կարգավորումներհատուկ նախագծված է աշխատանքային թերթերում տեղադրելու համար: Միեւնույն ժամանակ ActiveX վերահսկումօգտագործվում է հիմնականում սովորական ձևերով: Կանոն դարձրեք ձեր ձևաթղթերի վրա տեղադրել միայն ձևի կառավարման տարրեր:
Դուք պետք է ընտրեք այն վերահսկիչները, որոնք լավագույնս համապատասխանում են առաջադրանքին: Այս օրինակում հաճախորդները պետք է կարողանան թարմացնել առանցքային աղյուսակը `կտտացնելով կոճակը: Կտտացրեք հսկիչին Կոճակ, մկնիկի ցուցիչը տեղափոխեք այն աշխատաթերթի այն դիրքը, որտեղ ցանկանում եք, որ լինի կոճակը և կտտացրեք:
Կոճակը սեղանի մեջ դնելուց հետո երկխոսության տուփ կբացվի Նշանակել մակրո օբյեկտ(նկ. 5): Ընտրեք անհրաժեշտ մակրո (մեր դեպքում - Տվյալների թարմացումգրանցվել է ավելի վաղ) և կտտացրեք լավ.
Բրինձ 5. Ընտրեք կոճակին հատկացվող մակրոտն ու սեղմեք կոճակը լավ... Վ այս գործըպետք է կիրառվի մակրո Տվյալների թարմացում
Բոլոր անհրաժեշտ վերահսկումներն առանցքային աղյուսակում զետեղելուց հետո կարող եք ձևակերպել աղյուսակը `հիմնական ինտերֆեյս ստեղծելու համար: Նկ. 6 -ը ցույց է տալիս առանցքային աղյուսակի հաշվետվությունը ձևաչափելուց հետո:
Ձայնագրված մակրոյի փոփոխում
Մակրո ձայնագրման արդյունքում Excel ծրագիրստեղծում է մոդուլ, որը պահում է ձեր կատարած գործողությունները: Բոլոր գրանցված գործողությունները ներկայացված են մակրոը կազմող VBA կոդի տողերով: Ձեր առանցքային աղյուսակի հաշվետվություններին կարող եք ավելացնել տարբեր տեսակի առանցքային սեղաններ: ֆունկցիոնալությունըհարմարեցնելով VBA ծածկագիրը `ձեր ուզած արդյունքներին հասնելու համար: Որպեսզի ավելի հեշտ լինի հասկանալ, թե ինչպես է այս ամենը գործում, եկեք ստեղծենք նոր մակրո, որը ցուցադրում է հաճախորդների առաջին հինգ գրառումները: Գնալ դեպի ներդիր Մշակողըև կտտացրեք կոճակին Մակրո ձայնագրություն... Նկարում ներկայացված երկխոսության տուփը: 7. Անվանեք ստեղծված մակրոը ԱռաջինըՀաճախորդներև նշեք պահպանման վայրը Այս գիրքը... Սեղմել լավսկսել մակրոտնտեսությունը:
Ձայնագրումը սկսելուց հետո կտտացրեք վանդակի կողքին գտնվող սլաքին Հաճախորդի անուն, ընտրել Filտել ըստ արժեքիև տարբերակ Առաջին 10(Նկ. 8 ա): Բացվող երկխոսության դաշտում կազմաձևեք կարգավորումները, ինչպես ցույց է տրված Նկարում: 8 բ Այս պարամետրերը հուշում են, որ ցուցադրեք վաճառքի առումով լավագույն հինգ հաճախորդների տվյալները: Սեղմել լավ.
Բրինձ 8. Ընտրեք զտիչը (ա) և կարգավորեք պարամետրերը (բ) `վաճառքների տեսանկյունից լավագույն հինգ հաճախորդներին ցուցադրելու համար
Այն բանից հետո, երբ դուք հաջողությամբ գրանցեք բոլոր այն քայլերը, որոնք անհրաժեշտ են առևտրի թոփ 5 առաջադրանքները գտնելու համար, անցեք ներդիրին Մշակողըև կտտացրեք կոճակին Դադարեցնել ձայնագրությունը.
Այժմ դուք ունեք մակրո, որը կզտվի առանցքային աղյուսակը ՝ առևտրի լավագույն 5 հաճախորդներին առբերելու համար: Անհրաժեշտ է մակրոյին ստիպել ոլորման վահանի վիճակին, այսինքն. ոլորման սանդղակի միջոցով դուք պետք է կարողանաք մակրոին նշել այն հաճախորդների թիվը, որոնց տվյալները կցուցադրվեն առանցքային աղյուսակի հաշվետվությունում: Այսպիսով, օգտագործելով ոլորման սանդղակը, օգտվողը կկարողանա վերցնել լավագույն հնգյակը, լավագույն ութը կամ երեսուներկու լավագույն հաճախորդը, ըստ իրենց նպատակահարմարության:
Ձեր աղյուսակին ոլորման տող ավելացնելու համար անցեք ներդիրին Մշակողը, սեղմեք կոճակը Տեղադրեք, ընտրեք հսկիչ պալիտրա վրա Ոլորման գոտիև տեղադրեք այն աշխատանքային թերթիկի վրա: Աջ սեղմեք կառավարման վահանակի վրա Ոլորման գոտի Օբյեկտի ձևաչափ... Կբացվի երկխոսության տուփ Վերահսկիչ ձևաչափ(նկ. 9): Դրանում կատարեք հետևյալ փոփոխությունները պարամետրերում. Նվազագույն արժեքընշանակել արժեքը 1, պարամետր Առավելագույն արժեքը - արժեքը 200, իսկ դաշտում Բջջային հաղորդակցությունմուտքագրեք $ M $ 2 արժեքը ՝ M2 բջիջում ոլորման վահանակի արժեքը ցուցադրելու համար: Կտտացրեք կոճակին լավկիրառել նախկինում նշված կարգավորումները:
Այժմ դուք պետք է համապատասխանեք վերջերս գրանցված մակրոին ԱռաջինըՀաճախորդներվերահսկողությամբ Ոլորման գոտիաշխատանքային թերթիկի վրա: Աջ սեղմեք կառավարման վահանակի վրա Ոլորման գոտիև մեջ համատեքստային ընտրացանկընտրել թիմը Նշանակել մակրոբացել Մակրո առաջադրանքի երկխոսության տուփը: Նշված մակրոը հանձնեք ոլորման տողին FirstN հաճախորդներ... Մակրոը կգործի ամեն անգամ, երբ ոլորման սանդղակը կտտացվում է: Ստուգեք ստեղծված ոլորման տողը: Շերտի վրա սեղմելուց հետո մակրոը կաշխատի FirstN հաճախորդներիսկ M2 բջիջի համարը կփոխվի `ցույց տալով ոլորման վահանի կարգավիճակը: M2 բջիջի համարը կարևոր է, քանի որ այն օգտագործվում է մակրոը ոլորման սանդղակին կապելու համար:
Մնում է M2 բջիջի մակրո գործընթացը դարձնել այնպես, որ այն կապվի ոլորման տողի հետ: Դա անելու համար հարկավոր է գնալ մակրոի VBA կոդ: Դա անելու համար անցեք ներդիրին Մշակողըև կտտացրեք կոճակին Մակրոներ... Կբացվի երկխոսության տուփ Մակրո(նկ. 10): Դրա մեջ կարող եք գործարկել, ջնջել և խմբագրել ընտրված մակրոը: Էկրանին մակրոի VBA ծածկագիրը ցուցադրելու համար ընտրեք մակրոտն ու սեղմեք կոճակը Փոխել.
Բրինձ 10. Մակրոյի VBA կոդ մուտք գործելու համար ԱռաջինըՀաճախորդներ, ընտրեք մակրոտն ու սեղմեք կոճակը Փոխել
Էկրանի վրա կհայտնվի խմբագրի պատուհան Visual Basicմակրոի VBA- կոդով (նկ. 11): Ձեր նպատակն է փոխարինել կոշտ կոդավորված 5 թիվը, որը սահմանվում է մակրո ձայնագրման ժամանակ, M2 բջիջի արժեքով, որը կապված է ոլորման սանդղակին: Սկզբում մակրո էր գրանցվում ՝ ամենաբարձր եկամուտ ունեցող առաջին հինգ հաճախորդների ցուցադրման համար:
Կոդից հանեք 5 թիվը և փոխարենը մուտքագրեք հետևյալ արտահայտությունը.
ActiveSheet.Range ("M2"): Արժեք
Մակրոյի սկզբում ավելացրեք երկու տող ՝ ֆիլտրերը մաքրելու համար.
Տեսականի («A4»): Ընտրեք
ActiveSheet.PivotTables ("PivotTable1") .PivotFields ("Customer Name") .ClearAllFilters
Մակրո կոդը այժմ պետք է նման լինի Նկարում ներկայացվածին: 12
Փակեք Visual Basic խմբագրիչը և վերադարձեք առանցքային աղյուսակի զեկույցին: Փորձարկեք ոլորման սանդղակը ՝ սահիկը քաշելով դեպի 11. Մակրոը պետք է գործարկի և զտվի մոտ 11 գրառում լավագույն հաճախորդներըվաճառքի միջոցով:
Սինխրոնիզացրեք երկու առանցքային աղյուսակ մեկ բացվող տողով
Նկ. 13 -ը պարունակում է երկու առանցքային աղյուսակ: Նրանցից յուրաքանչյուրն ունի էջի դաշտ, որը թույլ է տալիս ընտրել վաճառքի տարածք: Խնդիրն այն է, որ ամեն անգամ, երբ ընտրում եք շուկա մեկ առանցքային աղյուսակի էջերի դաշտում, դուք պետք է ընտրեք նույն շուկան մեկ այլ առանցքային աղյուսակի էջի դաշտում: Տվյալների վերլուծության փուլում երկու սեղանների միջև զտիչների համաժամացումը մեծ խնդիր չէ, բայց հավանականություն կա, որ դուք կամ ձեր հաճախորդները դեռ կմոռանան դա անել:
Բրինձ 13. Երկու առանցքային աղյուսակները պարունակում են էջերի դաշտեր, որոնք զտում են տվյալները ըստ շուկայի: Մեկ շուկայի տվյալները վերլուծելու համար հարկավոր է համաժամանակացնել երկու առանցքային աղյուսակները
Այս առանցքային աղյուսակները համաժամանակյա պահելու եղանակը բացվող ցուցակի օգտագործումն է: Գաղափարն այն է, որ գրանցվի մակրո, որը ընտրում է ցանկալի շուկան ոլորտից Վաճառքի շուկաերկու աղյուսակներում: Այնուհետև անհրաժեշտ է ստեղծել բացվող ցուցակ և երկու առանցքային աղյուսակներից այն լրացնել վաճառքի շուկաների անուններով: Վերջապես, գրանցված մակրոտն անհրաժեշտ է փոփոխել, որպեսզի երկու առանցքային աղյուսակները զտվեն ՝ օգտագործելով բացվող ցուցակի արժեքները: Այս խնդիրը լուծելու համար հարկավոր է կատարել հետևյալ քայլերը.
1. Ստեղծեք նոր մակրո և տվեք անունը SynchMarkets... Երբ սկսվում է ձայնագրությունը, ընտրեք դաշտում Շուկա երկուսի համարվաճառքի առանցքային սեղաններ Կալիֆոռնիաև դադարեցրեք մակրո ձայնագրությունը:
2. Displayուցադրել ձևի կառավարման տարրերի ներկապնակ և բացվող ցանկ ավելացնել աշխատանքային թերթիկին:
3. Ստեղծեք կոշտ կոդավորված ցուցակ բոլոր Առանցքային աղյուսակի շուկաներից: Նկատի ունեցեք, որ ցուցակի առաջին կետը (Բոլորը) է: Դուք պետք է միացնեք այս տարրը, եթե ցանկանում եք, որ կարողանաք ընտրել բոլոր շուկաները բացվող ցուցակից:
4. Այս պահին առանցքային աղյուսակի զեկույցը պետք է նման լինի Նկարում ներկայացվածին: տասնչորս.
Բրինձ 14. Ձեր տրամադրության տակ կան բոլոր անհրաժեշտ գործիքները `մակրո, որը փոխում է դաշտը Վաճառքի շուկաերկուսն էլ առանցքային աղյուսակներ, բացվող ցուցակ և առանցքային աղյուսակում պարունակվող վաճառքի բոլոր շուկաների ցանկ
5. Աջ սեղմեք բացվող ցուցակի վրա և ընտրեք հրամանը Օբյեկտի ձևաչափվերահսկողությունը հարմարեցնելու համար:
6. Նախ, սահմանեք բացվող ցանկը լրացնելու համար օգտագործվող արժեքների սկզբնական տիրույթը, ինչպես ցույց է տրված Նկար 6-7-ում: 15. Այս դեպքում մենք խոսում ենք վաճառքի շուկաների ցանկի մասին, որը դուք ստեղծել եք 3 -րդ քայլում: Հետո նշեք բջիջը, որը ցուցադրում է ընտրված տարրի սերիական համարը (այս օրինակում սա H1 բջիջն է): Պարամետր Listանկի տողերի քանակըորոշում է, թե քանի տող կցուցադրվի միաժամանակ բացվող ցուցակում: Կտտացրեք կոճակին լավ.
Բրինձ 15. Բացվող ցուցակի կարգավորումները պետք է մատնանշեն վաճառքի շուկաների ցանկը `որպես արժեքների սկզբնական տիրույթ, իսկ H1 բջիջը` որպես խարիսխի կետ:
7. Այժմ դուք հնարավորություն ունեք բացվող ցուցակից ընտրել վաճառքի շուկա, ինչպես նաև սահմանել դրա հաջորդական համարը H1 բջիջում (նկ. 16): Հարց է առաջանում ՝ ինչո՞ւ է ինդեքսի արժեքը օգտագործվում շուկայի իրական անվան փոխարեն: Քանի որ բացվող պատուհանը վերադարձնում է ոչ թե անուն, այլ թիվ: Օրինակ, եթե ընտրեք Կալիֆորնիա բացվող ցուցակից, H1 բջիջում հայտնվում է 5 արժեքը, ինչը նշանակում է, որ Կալիֆորնիան ցուցակի հինգերորդ տարրն է:
Բրինձ 16. Բացվող ցանկն այժմ լրացված է շուկաների անուններով, իսկ ընտրված շուկայի հերթական համարը ցուցադրվում է H1 բջիջում:
8. Շուկայական անվան փոխարեն հաջորդականության համարն օգտագործելու համար պետք է այն փոխանցել INDEX ֆունկցիայի միջոցով:
9. Մուտքագրեք INDEX ֆունկցիան, որը սերիական համարը H1 բջիջից փոխակերպում է նշանակալի արժեքի:
10. INDEX ֆունկցիան վերցնում է երկու փաստարկ: Առաջին փաստարկը ներկայացնում է ցուցակի արժեքների շրջանակը: Շատ դեպքերում դուք կօգտագործեք նույն տիրույթը, որը համալրում է բացվող ընտրացանկը: Երկրորդ փաստարկը հաջորդականության թիվ է: Եթե սերիական համարը մուտքագրվում է բջիջում (օրինակ ՝ H1 բջիջում, ինչպես նկ. 17 -ում), ապա կարող եք պարզապես անդրադառնալ այս բջիջին:
Բրինձ 17. I1 բջիջում INDEX ֆունկցիան փոխակերպում է H1 բջիջում պահվող հաջորդականության թիվը արժեքի: Դուք կօգտագործեք I1 բջջի արժեքը `մակրոը փոխելու համար
11. Խմբագրել մակրոը SynchMarketsկոշտ կոդավորված արժեքի փոխարեն օգտագործելով I1 բջիջի արժեքը: Գնալ դեպի ներդիր Մշակողըև կտտացրեք կոճակին Մակրոներ... Էկրանի վրա կհայտնվի երկխոսության տուփ, ինչպես ցույց է տրված նկ. 18. Ընտրեք մակրո դրա մեջ SynchMarketsև կտտացրեք Փոխել կոճակին:
Բրինձ 18. Մակրոյի VBA կոդ մուտք գործելու համար ընտրեք մակրո SynchMarketsև կտտացրեք Փոխել
12. Մակրո ձայնագրելիս դուք երկու առանցքային աղյուսակներում դաշտից ընտրել եք Կալիֆորնիայի վաճառքի տարածքը: Վաճառքի շուկա... Ինչպես երևում է Նկ. 19 -ին, Կալիֆոռնիայի շուկան այժմ կոդավորված է VBA մակրո կոդի մեջ:
13. Փոխարինեք Կալիֆոռնիան Activesheet.Range- ով («I1»): Արժեք, որը վերաբերում է I1 բջիջի արժեքին: Այս պահին մակրո կոդը պետք է նման լինի Նկարում պատկերվածին: 20. Մակրոյի փոփոխությունից հետո փակեք Visual Basic խմբագիրը և վերադարձեք աղյուսակ:
Բրինձ 20. Փոխարինեք «California» - ն ActiveSheet.Range- ով («I1»): Գնահատեք և փակեք Visual Basic խմբագիրը
14. Մնում է միայն ապահովել մակրոյի կատարումը բացվող ցուցակում վաճառքի տարածք ընտրելիս: Աջ սեղմեք բացվող պատուհանի վրա և ընտրեք տարբերակ Նշանակել մակրո... Ընտրեք մակրո SynchMarketև կտտացրեք կոճակին լավ.
15. Թաքցրեք առանցքային աղյուսակների էջերի դաշտերով տողերն ու սյուները և ձեր ստեղծած շուկաների և ինդեքսի բանաձևերի ցանկը:
Նկ. 21 -ը ցույց է տալիս վերջնական արդյունքը: Այժմ դուք ունեք միջերես, որը թույլ է տալիս հաճախորդներին ընտրել իրենց վաճառքի տարածքը երկու առանցքային աղյուսակներում ՝ օգտագործելով մեկ բացվող ցուցակ:
Բացվող ցուցակից նոր տարր ընտրելիս սյունակների ինքնաբերաբար չափափոխվում է `դրանցում ցուցադրված բոլոր տվյալները տեղավորելու համար: Thisրագրի այս պահվածքը բավականին ձանձրալի է աշխատանքային թերթիկի ձևանմուշը ձևավորելիս: Դուք կարող եք կանխել դա ՝ առանցքային աղյուսակի աջ սեղմումով և ընտրելով Առանցքային սեղանի ընտրանքներ... Էկրանի վրա կհայտնվի համանուն երկխոսության տուփ, որում պետք է ջնջել վանդակը Թարմացման ժամանակ սյունակների չափերի ավտոմատ չափափոխում.
Նշում ՝ հիմնված lenելենի ՝ Ալեքսանդր գրքի վրա: ... Գլուխ 12:
Excel- ի գործիքների օգնությամբ դուք կարող եք որոշակի տվյալներ ընտրել մի շարքից ՝ պատահական կարգով, մեկ պայմանով կամ մի քանիսով: Նման խնդիրները լուծելու համար, որպես կանոն, օգտագործվում են զանգվածների բանաձևեր կամ մակրոներ: Եկեք նայենք մի քանի օրինակների:
Ինչպես կատարել ընտրություն Excel- ում `պայմանով
Arանգվածի բանաձևեր օգտագործելիս ընտրված տվյալները ցուցադրվում են առանձին աղյուսակում: Ո՞րն է առավելությունը այս մեթոդըսովորական ֆիլտրի համեմատ:
Աղբյուրի աղյուսակ.
Նախ, եկեք սովորենք, թե ինչպես կատարել ընտրություն `հիմնված մեկ թվային չափանիշի վրա: Խնդիրն այն է, որ սեղանից ընտրեն 200 ռուբլուց բարձր գին ունեցող ապրանքներ: Լուծումներից մեկը զտման կիրառումն է: Արդյունքում, միայն այն ապրանքները, որոնք կբավարարեն խնդրանքը, կմնան սկզբնական աղյուսակում:
Մեկ այլ լուծում է զանգվածի բանաձևի օգտագործումը: Հարցմանը համապատասխան տողերը տեղավորվում են առանձին աղյուսակի հաշվետվության մեջ:
Նախ, մենք ստեղծում ենք դատարկ աղյուսակ օրիգինալի կողքին `կրկնօրինակ վերնագրեր, տողերի և սյուների քանակ: Նոր աղյուսակը զբաղեցնում է E1: G10 տիրույթը: Այժմ ընտրեք E2: E10 (սյունակ «Ամսաթիվ») և մուտքագրեք հետևյալ բանաձևը. ( }.
Arանգվածի բանաձև ստանալու համար սեղմեք ստեղնաշարի համադրությունը Ctrl + Shift + Enter: Հաջորդ սյունակում `« Ապրանք », մենք մուտքագրում ենք նման զանգվածի բանաձև. ( ): INDEX ֆունկցիայի միայն առաջին փաստարկն է փոխվել:
«Գին» սյունակում մուտքագրեք նույն զանգվածի բանաձևը ՝ փոխելով INDEX ֆունկցիայի առաջին արգումենտը:
Արդյունքում, մենք ստանում ենք հաշվետվություն ավելի քան 200 ռուբլի գնով ապրանքների վերաբերյալ:
Նման ընտրությունը դինամիկ է. Երբ հարցումը փոխվի կամ աղբյուրի աղյուսակում հայտնվեն նոր ապրանքներ, հաշվետվությունն ինքնաբերաբար կփոխվի:
Առաջադրանք թիվ 2 - օրիգինալ սեղանից ընտրեք այն ապրանքները, որոնք վաճառքի են հանվել 2015 թվականի սեպտեմբերի 20 -ին: Այսինքն, ընտրության չափանիշը ամսաթիվն է: Հարմարության համար մենք ցանկալի ամսաթիվը մուտքագրելու ենք առանձին բջիջ ՝ I2:
Խնդիրը լուծելու համար օգտագործվում է նման զանգվածի բանաձև: Միայն չափանիշի փոխարեն):
Նմանատիպ բանաձևեր մուտքագրվում են այլ սյունակներում (տե՛ս սկզբունքը վերևում):
Այժմ մենք օգտագործում ենք տեքստի չափանիշը: I2 բջիջում նշված ամսաթվի փոխարեն մուտքագրեք «Ապրանք 1» տեքստը: Եկեք մի փոքր փոխենք զանգվածի բանաձևը: ( }.
Excel- ում նման մեծ ընտրության գործառույթ:
Excel- ում բազմաթիվ պայմանների ընտրություն
Նախ, եկեք վերցնենք երկու թվային չափանիշ.
Խնդիրն է ընտրել ապրանքներ, որոնք արժեն 400 -ից պակաս և ավելի քան 200 ռուբլի: Եկեք պայմանները համատեղենք «*» նշանի հետ: Անգվածի բանաձևն այսպիսին է. ( }.!}
Սա հաշվետվության աղյուսակի առաջին սյունակի համար է: Երկրորդի և երրորդի համար մենք փոխում ենք INDEX ֆունկցիայի առաջին փաստարկը: Արդյունք:
Մի քանի ամսաթվերով կամ թվային չափանիշներով ընտրություն կատարելու համար մենք օգտագործում ենք զանգվածի նման բանաձևեր:
Excel- ում պատահական նմուշառում
Երբ օգտագործողը աշխատում է մեծ գումարտվյալները կարող են պահանջել պատահական նմուշառում հետագա վերլուծության համար: Դուք կարող եք պատահական համար նշանակել յուրաքանչյուր տողին, այնուհետև դասավորել ընտրության մեջ:
Նախնական տվյալների հավաքածու.
Նախ, եկեք ձախ կողմում տեղադրենք երկու դատարկ սյունակ: A2 բջիջում գրեք RAND () բանաձևը: Եկեք այն բազմապատկենք ամբողջ սյունակով.
Այժմ մենք պատճենում ենք սյունակը պատահական թվերով և տեղադրում այն B. սյունակում: Սա անհրաժեշտ է, որպեսզի այս թվերը չփոխվեն, երբ փաստաթղթում նոր տվյալներ մուտքագրվեն:
Արժեքներ տեղադրելու համար, այլ ոչ թե բանաձև, աջ սեղմեք B սյունակի վրա և ընտրեք Տեղադրեք հատուկ գործիքը: Բացվող պատուհանում նշեք «Արժեքներ» կետի դիմաց.
Այժմ կարող եք B սյունակի տվյալները տեսակավորել աճման կամ նվազման կարգով: Կփոխվի նաև սկզբնական արժեքների ներկայացման կարգը: Մենք ընտրում ենք վերևից կամ ներքևից ցանկացած տող `մենք կստանանք պատահական նմուշ:
Տարբեր (39)
Excel- ի սխալներ և անսարքություններ (3)
Ինչպե՞ս կարող եմ ստանալ եզակի (ոչ կրկնօրինակ) արժեքների ցուցակ:
Պատկերացրեք տարբեր անունների մեծ ցուցակ, ամբողջական անուններ, անձնակազմի համարներ և այլն: Եվ անհրաժեշտ է այս ցուցակից թողնել բոլոր նույն անունների ցուցակը, բայց որպեսզի նրանք չկրկնվեն, այսինքն. հեռացնել այս ցուցակից բոլոր կրկնօրինակ գրառումները: Ինչպես այլ կերպ է կոչվում. Ստեղծեք եզակի տարրերի ցուցակ, չկրկնվողների ցուցակ, առանց կրկնօրինակների: Դա անելու մի քանի եղանակ կա ՝ ներկառուցված Excel գործիքներ, ներկառուցված բանաձևեր և, վերջապես, կոդի օգտագործում Visual Basic for Application (VBA) և առանցքային սեղաններ: Այս հոդվածը կանդրադառնա տարբերակներից յուրաքանչյուրին:
Excel 2007 և ավելի ներկառուցված գործառույթներով
Excel 2007 և 2010 թվականներին դա նույնքան հեշտ է, որքան տանձը գնդակոծելը հատուկ թիմ, որը կոչվում է այսպես. Այն գտնվում է էջանիշի վրա Տվյալներենթաբաժին Տվյալների գործիքներ
Ինչպես օգտագործել այս հրամանը: Առանձնացրու սյունակը (կամ մի քանի)այն տվյալների հետ, որոնցում անհրաժեշտ է ջնջել կրկնօրինակ գրառումները: Գնալ դեպի ներդիր Տվյալներ -Հեռացրեք Կրկնօրինակները.
Եթե ընտրեք մեկ սյունակ, բայց դրա կողքին կլինեն ավելի շատ սյունակներ ՝ տվյալներով (կամ առնվազն մեկ սյունակ), ապա Excel- ը կառաջարկի ընտրել. Ընդլայնել ընտրության տիրույթը այս սյունակով կամ թողնել ընտրությունը այնպիսին, ինչպիսին կա և ջնջել միայն տվյալները ընտրված տիրույթում: Կարևոր է հիշել, որ եթե չընդլայնեք տիրույթը, ապա տվյալները կփոխվեն միայն մեկ սյունակում, իսկ հարակից սյունակում եղած տվյալները կմնան անփոփոխ:
Կհայտնվի պատուհան `կրկնօրինակները հեռացնելու ընտրանքներով 
Նշեք այդ սյունակների դիմաց գտնվող տուփերը, կրկնօրինակները, որոնցից ցանկանում եք հեռացնել և կտտացրեք OK: Եթե տվյալների վերնագրերը նույնպես տեղակայված են ընտրված տիրույթում, ապա ավելի լավ է դրոշը սահմանել Իմ տվյալները պարունակում են վերնագրերաղյուսակում տվյալների պատահական ջնջումից խուսափելու համար (եթե դրանք հանկարծ ամբողջությամբ համընկնում են վերնագրի արժեքի հետ).
Մեթոդ 1: Ընդլայնված զտիչ
Excel 2003 -ի դեպքում ամեն ինչ ավելի բարդ է: Չկա այնպիսի գործիք, ինչպիսին է Հեռացրեք կրկնօրինակները... Բայց մյուս կողմից, կա այնպիսի հրաշալի գործիք, ինչպիսին է Ընդլայնված զտիչ... 2003 թվականին այս գործիքը կարելի է գտնել Տվյալներ -Զտիչ -Ընդլայնված զտիչ... Այս մեթոդի գեղեցկությունն այն է, որ դուք կարող եք օգտագործել այն այլ տիրույթում ցուցակ ստեղծելու համար, այլ ոչ թե փչացնել սկզբնական տվյալները: 2007-2010 Excel- ում այն նույնպես կա, բայց մի փոքր թաքնված: Տեղադրված է ներդիրում Տվյալներ, խումբ Տեսակավորել և զտել - Ընդլայնված
Ինչպես օգտագործել այն. Գործարկեք նշված գործիքը. Հայտնվում է երկխոսության տուփ. 
- Բուժում.Մենք ընտրում ենք Պատճենեք արդյունքը մեկ այլ վայրում.
- Listանկերի տիրույթ.Տվյալներով տիրույթի ընտրություն (մեր դեպքում դա A1: A51 է).
- Չափանիշների շրջանակ:այս դեպքում այն թողեք դատարկ:
- Պատճենել տիրույթում.մենք նշում ենք տվյալների ցուցադրման առաջին բջիջը `ցանկացած դատարկ (նկարում `E2).
- Ստուգեք վանդակը Միայն եզակի գրառումներ.
- Մենք սեղմում ենք լավ.
Նշում:եթե ցանկանում եք արդյունքը տեղադրել մեկ այլ թերթիկի վրա, ապա պարզապես չեք կարող նշել մեկ այլ թերթիկ: Դուք կկարողանաք մեկ այլ թերթիկի բջիջ նշել, բայց ... Վա andյ և ահ ... Excel- ը հաղորդագրություն կտա, որ չի կարող տվյալներ պատճենել այլ թերթերի վրա: Բայց սա նույնպես կարող է շրջանցվել, և միանգամայն պարզ: Պարզապես պետք է վազել Ընդլայնված զտիչայն թերթիկից, որի վրա ցանկանում ենք տեղադրել արդյունքը: Եվ որպես նախնական տվյալներ, մենք ընտրում ենք տվյալներ ցանկացած թերթիկից. Սա թույլատրելի է:
Կարող եք նաև արդյունքը չփոխանցել այլ բջիջների, այլ զտել տվյալները տեղում: Տվյալները որևէ կերպ չեն ազդվի. Դա կլինի տվյալների սովորական զտումը:
Դա անելու համար պարզապես անհրաժեշտ է ընտրել Վերամշակման տարրում Terտել ցանկը ՝ տեղում.
Մեթոդ 2. Բանաձևեր
Այս մեթոդը ավելի դժվար է հասկանալ անփորձ օգտվողների համար, սակայն այն ստեղծում է յուրահատուկ արժեքների ցուցակ ՝ առանց սկզբնական տվյալները փոխելու: Դե, դա նաև ավելի դինամիկ է. Եթե տվյալների աղբյուրի աղյուսակում փոխես, արդյունքը նույնպես կփոխվի: Սա երբեմն օգտակար է: Ես կփորձեմ մատներիս վրա բացատրել, թե ինչ և ինչ. Օրինակ ՝ դուք ունեք ցուցակ, որում առկա են տվյալներ սյունակում (A1: A51, որտեղ վերնագիրն է A1): Մենք կցուցադրենք ցուցակը C սյունակում ՝ սկսած C2 բջիջից: C2- ի բանաձևը կլինի հետևյալը.
(= INDEX ($ A $ 2: $ A $ 51; ՓՈՔՐ (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; ROW ($ A $ 1: $ A $ 50)); 1)))
(= INDEX ($ A $ 2: $ A $ 51; ՓՈՔՐ (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; ROW ($ A $ 1: $ A $ 50)); 1)))
Այս բանաձևի գործունեության մանրամասն վերլուծությունը տրված է հոդվածում.
Պետք է նշել, որ այս բանաձևը զանգվածային բանաձև է: Սա կարելի է ասել բրեկետներ, որում ներառված է այս բանաձևը: Եվ նման բանաձևը մուտքագրվում է բջիջ `ստեղնաշարի դյուրանցմամբ - Ctrl+Հերթափոխ+Մուտքագրեք... Այս բանաձևը C2- ում մուտքագրելուց հետո մենք պետք է պատճենենք և տեղադրենք այն մի քանի տողերում, որպեսզի բոլոր եզակի տարրերը ճշգրիտ ցուցադրվեն: Հենց ներքևի բջիջների բանաձևը վերադառնա #ԹԻՎ!- սա նշանակում է, որ բոլոր տարրերը ցուցադրվում են, և ստորև բերված բանաձևը ձգելու իմաստ չկա: Սխալից խուսափելու և բանաձևն ավելի ունիվերսալ դարձնելու համար (առանց ամեն անգամ ձգվելու մինչև սխալի հայտնվելը), կարող եք օգտագործել պարզ ստուգում.
Excel 2007 և ավելի բարձր համար.
(= IFERROR (INDEX ($ A $ 2: $ A $ 51; ՓՈՔՐ) (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; ROW ($ A $ 1: $ $ 50)); 1)); ""))
(= IFERROR (INDEX ($ A $ 2: $ A $ 51; ՓՈՔՐ) (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; ROW ($ A $ 1: $ $ 50)); 1)); ""))
Excel 2003 -ի համար.
(= IF (ISH (ՓՈՔՐ (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; ROW ($ A $ 1: $ A $ 50)); 1)); ""; INDEX ($ A $ 2: $ A $ 51; ՓՈՔՐ (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; ROW ($ A $ 1: $ A $ 50)); 1))))
(= IF (ISERR (ՓՈՔՐ (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; ROW ($ A $ 1: $ A $ 50)); 1)); ""; INDEX ($ A $ 2: $ A $ 51; ՓՈՔՐ (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; ROW ($ A $ 1: $ A $ 50)); 1))))
Հետո սխալի փոխարեն #ԹԻՎ! (#ԹԻՎ!)Դուք կարող եք ունենալ դատարկ բջիջներ(իհարկե ոչ ամբողջովին դատարկ - բանաձևերով .-)).
Մի փոքր ավելի մանրամասն ESLIOSHIBKA և IF բանաձևերի տարբերությունների և նրբությունների մասին (EOSH կարելի է կարդալ այս հոդվածում. Ինչպես սխալի փոխարեն բանաձևով բջիջում 0 ցույց տալ)
Մեթոդ 3: VBA կոդ
Այս մոտեցումը կպահանջի մակրոների լուծում և դրանց հետ աշխատելու հիմնական գիտելիքներ: Եթե վստահ չեք ձեր գիտելիքներին, խորհուրդ եմ տալիս նախ կարդալ այս հոդվածները.
- Ի՞նչ է մակրոն և որտե՞ղ կարող եմ այն գտնել: հոդվածին կցված է տեսանյութի ձեռնարկ
- Ի՞նչ է մոդուլը: Ի՞նչ մոդուլներ կան: կպահանջվի պարզել, թե որտեղ պետք է տեղադրել ստորև նշված կոդերը
Ստորև բերված երկու կոդերը պետք է տեղադրվեն ստանդարտ մոդուլ... Մակրոները պետք է թույլատրվեն:
Եկեք թողնենք նախնական տվյալները նույն կարգով. Տվյալների հետ ցուցակը գտնվում է «Ա» սյունակում (A1: A51, որտեղ A1- ը վերնագիր է)... Միայն մենք կցուցադրենք ցուցակը ոչ թե C սյունակում, այլ E սյունակում ՝ սկսած E2 բջիջից.
Sub Extract_Unique () Dim vItem, avArr, li As Long ReDim avArr (1 To Rows.Count, 1 To 1) With New Collection On Error Resume Next For Every vItem In Range ("A2", Cells (Rows.Count, 1) .Վերջ (xlUp)). Արժեք »(! LANG: Բջիջներ (Տողեր. Հաշվարկ, 1): Ավարտ (xlUp) - սահմանում է Ա սյունակի վերջին լրացված բջիջը: Ավելացնել vItem, CStr (vItem) Եթե Err = 0 Հետո li = li + 1: avArr (li, 1) = vItem Else: Err. Մաքրել ավարտը, եթե հաջորդը ավարտվի, եթե.
Այս ծածկագրով դուք կարող եք բացառիկ արժեքներ հանել ոչ միայն մեկ սյունակից, այլ սյունակների և տողերի ցանկացած տիրույթից: Եթե գծի փոխարեն
Միջակայք ("A2", Cells(Rows.Count, 1).End(xlUp)).Value !}
նշեք Selection.Value, ապա ծածկագրի արդյունքը կլինի ակտիվ թերթում ընտրված տիրույթից եզակի տարրերի ցանկը: Միայն այդ դեպքում լավ կլիներ փոխել արժեքների ելքային բջիջը `փոխարենը
տեղադրել այն, որի մեջ տվյալներ չկան:
Կարող եք նաև նշել որոշակի տիրույթ.
Միջակայք («C2», բջիջներ (տողեր. Հաշիվ, 3): Վերջ (xlUp)): Արժեք
Եզակի արժեքներ ընտրելու ունիվերսալ կոդ
Ստորև բերված ծածկագիրը կարող է կիրառվել ցանկացած տիրույթի համար: Բավական է գործարկել այն, սահմանել մի շարք, որի արժեքներն են `միայն չկրկնվողները (թույլատրվում է մեկից ավելի սյունակներ) և արդյունքը ցուցադրելու համար բջիջ: Նշված բջիջները սկանավորվելու են, որոնցից միայն եզակի արժեքներ(դատարկ բջիջները բաց են թողնվում) և արդյունքում ստացվող ցուցակը կգրվի սկսած նշված բջիջից:
| Sub Extract_Unique () Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next "խնդրեք բջիջների հասցեն` եզակի արժեքներ ընտրելու համարՍահմանել rVals = Application.InputBox ( "Նշեք մի շարք բջիջներ` եզակի արժեքներ նմուշառելու համար ", "Հարցման տվյալներ", "A2: A51", տեսակը `= 8) Եթե rVals- ը ոչինչ է, ապա «Եթե Չեղարկել կոճակը սեղմված էԵլք ենթավարտից, եթե «եթե նշված է միայն մեկ բջիջ, ապա ընտրելու իմաստ չկաԵթե rVals.Count = 1 Ապա MsgBox «Եզակի արժեքները զտելու համար հարկավոր է նշել մեկից ավելի բջիջ», vbInformation, "www.site" Exit Sub End If «կտրեք դատարկ տողերն ու սյուները աշխատանքային տիրույթից դուրսՍահմանել rVals = հատել (rVals, rVals.Parent.UsedRange) «եթե միայն աշխատանքային տիրույթից դուրս դատարկ բջիջներ են նշվածԵթե rVals- ը ոչինչ է, ապա MsgBox «Անբավարար տվյալներ արժեքներ ընտրելու համար», vbInformation, "www.site" Exit Sub End If avVals = rVals.Value «բջիջ խնդրեք արդյունքը ցուցադրելու համարՍահմանել rResultCell = Application.InputBox ( "Նշեք բջիջ` ընտրված եզակի արժեքները տեղադրելու համար ", «Հարցման տվյալներ», «E2», տեսակը ՝ = 8) Եթե rResultCell- ը ոչինչ է, ուրեմն «Եթե Չեղարկել կոճակը սեղմված էԵլք ենթավարտից, եթե "սահմանել զանգվածի առավելագույն հնարավոր չափումը արդյունքի համար ReDim avArr (1 -ից տողեր. Հաշվարկ, 1 -ից 1) «Հավաքածուի օբյեկտի օգտագործումը «ընտրել միայն եզակի գրառումներ, «քանի որ հավաքածուները չեն կարող պարունակել կրկնօրինակ արժեքներՍխալի մասին նոր հավաքածուով վերսկսել հաջորդը x x avVals- ի համար Եթե Len (CStr (x)) Ապա «բաց թողնել դատարկ բջիջները. Ավելացնել x, CStr (x) «եթե ավելացված տարրն արդեն գոյություն ունի Հավաքածուում, սխալ տեղի կունենա «Եթե սխալ չկա, այս արժեքը դեռ մուտքագրված չէ, "ավելացնել ստացված զանգվածինԵթե Err = 0 Ապա li = li + 1 avArr (li, 1) = x Այլ «Համոզվեք, որ մաքրեք Սխալի օբյեկտը Err.Clear End If End Եթե հաջորդ ավարտը հետ «գրեք արդյունքը թերթիկին ՝ սկսած նշված բջիջից If li Ապա rResultCell.Cells (1, 1): Չափափոխել (li). Գին = avArr End Sub |
Sub Extract_Unique () Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next "խնդրեք բջիջների հասցեն` ընտրելու եզակի արժեքներ Սահմանել rVals = Application.InputBox ("Նշեք տիրույթը բջիջների ՝ եզակի արժեքներ ընտրելու համար »,« Տվյալների հարցում »,« A2: A51 », տեսակը ՝ = 8) Եթե rVals- ը ոչինչ է, ապա« Եթե Չեղարկման կոճակը կտտացվի Ելք ենթավեճից, եթե », եթե նշված է միայն մեկ բջիջ , Եթե rVals.Count = 1 ընտրելու իմաստ չկա, ապա MsgBox «Եզակի արժեքներ ընտրելու համար հարկավոր է նշել մեկից ավելի բջիջ», vbInformation, «www.site" Exit Sub End If "մենք կտրում ենք դատարկ տողերն ու սյուները աշխատանքային տիրույթից դուրս Սահմանել rVals = Խաչել (rVals, rVals.Parent.UsedRange) "եթե աշխատանքային տիրույթից դուրս միայն դատարկ բջիջներն են նշված, եթե rVals- ը ոչինչ է, ապա MsgBox" Ոչ բավարար տվյալներ արժեքներ ընտրելու համար ", vbInformation," www..Value "(! LANG. Խնդրեք բջիջ` արդյունքը ցուցադրելու համար Սահմանել rResultCell = Application.InputBox ("Укажите ячейку для вставки отобранных уникальных значений", "Запрос данных", "E2", Type:=8) If rResultCell Is Nothing Then "если нажата кнопка Отмена Exit Sub End If "определяем максимально возможную размерность массива для результата ReDim avArr(1 To Rows.Count, 1 To 1) "при помощи объекта Коллекции(Collection) "отбираем только уникальные записи, "т.к. Коллекции не могут содержать повторяющиеся значения With New Collection On Error Resume Next For Each x In avVals If Len(CStr(x)) Then "пропускаем пустые ячейки.Add x, CStr(x) "если добавляемый элемент уже есть в Коллекции - возникнет ошибка "если же ошибки нет - такое значение еще не внесено, "добавляем в результирующий массив If Err = 0 Then li = li + 1 avArr(li, 1) = x Else "обязательно очищаем объект Ошибки Err.Clear End If End If Next End With "записываем результат на лист, начиная с указанной ячейки If li Then rResultCell.Cells(1, 1).Resize(li).Value = avArr End Sub!}
Մեթոդ 4. Առանցքային սեղաններ
Մի քանի ոչ ստանդարտ եղանակյուրացնելով եզակի արժեքներ:
- Ընտրեք աղյուսակի մեկ կամ մի քանի սյունակ, անցեք ներդիրին Տեղադրեք-խումբ Սեղան -Առանցքային սեղան
- Երկխոսության վանդակում Ստեղծեք առանցքային սեղանմենք ստուգում ենք տվյալների տիրույթի (կամ հավաքածուի) ընտրության ճիշտ լինելը նոր աղբյուրտվյալներ)
- նշեք առանցքային աղյուսակի գտնվելու վայրը.
- Նոր աշխատանքային թերթիկ
- Առկա աշխատանքային թերթիկ
- հաստատել ստեղծումը `սեղմելով կոճակը լավ
Որովհետեւ առանցքային աղյուսակները, տողերի կամ սյուների տարածքում տեղադրված տվյալների մշակման ժամանակ, դրանցից ընտրում են միայն եզակի արժեքներ հետագա վերլուծության համար, ապա մեզանից բացարձակապես ոչինչ չի պահանջվում, բացառությամբ առանցքային աղյուսակի ստեղծման և տվյալների տվյալները տեղադրելու: տողերի կամ սյուների տարածքում ցանկալի սյունակ:
Որպես օրինակ հոդվածին կից ֆայլը օգտագործելով ՝ ես ՝
Ո՞րն է այս դեպքում առանցքային աղյուսակների հետ աշխատելու անհարմարությունը. Եթե աղբյուրի տվյալները փոխվեն, առանցքային աղյուսակը պետք է ձեռքով թարմացվի. Ընտրեք առանցքային աղյուսակի ցանկացած բջիջ - Մկնիկի աջ կոճակը - Թարմացնելկամ ներդիր Տվյալներ -Թարմացրեք բոլորը -Թարմացնել... Եվ եթե աղբյուրի տվյալները դինամիկ և նույնիսկ ավելի վատ համալրվեն, անհրաժեշտ կլինի նորից ճշգրտել աղբյուրի տվյալների տիրույթը: Եվ ևս մեկ թերություն. Առանցքային աղյուսակի տվյալները չեն կարող փոխվել: Հետեւաբար, եթե հետագայում անհրաժեշտ կլինի աշխատել ստացված ցուցակի հետ, ապա ստեղծագործելուց հետո ցանկալի ցուցակօգտագործելով ամփոփագիրը, այն պետք է պատճենվի և տեղադրվի ցանկալի թերթիկի վրա:
Բոլոր քայլերն ավելի լավ հասկանալու և առանցքային աղյուսակներ օգտագործելու համար խստորեն խորհուրդ եմ տալիս կարդալ հոդվածը Ընդհանուր տեղեկություններ առանցքային սեղանների մասին. Դրան կցված է վիդեո ձեռնարկ, որում ես հստակ ցույց եմ տալիս հիմնականի հետ աշխատելու պարզությունն ու հարմարավետությունը: առանցքային սեղանների առանձնահատկությունները:
Կից օրինակում, բացի նկարագրված տեխնիկայից, գրված է բանաձևով և ծածկագրով եզակի տարրեր հանելու մի փոքր ավելի բարդ տատանում, այն է. եզակի տարրերի արդյունահանում ըստ չափանիշի... Ինչի մասին է խոսքը. Եթե ազգանվան մեկ սյունակում, իսկ երկրորդում (B) կան որոշ տվյալներ (ֆայլում դրանք ամիսներ են), և ցանկանում եք հանել B սյունակի եզակի արժեքները միայն ընտրված ազգանունը: Նման յուրահատուկ արդյունահանման օրինակներ գտնվում են թերթիկի վրա Քաղվածք ըստ չափանիշի.
Ներբեռնեք օրինակ.
(108.0 KiB, 14.152 ներլցումներ)
Արդյո՞ք հոդվածը օգնեց: Կիսեք հղումը ձեր ընկերների հետ: Տեսադասեր
