Същността на искането за подбор е да се избере низ от таблицата на източника, която отговаря на определени критерии (подобни на използването на стандарт). Ще изберем стойности от използваната таблица на източника. За разлика от употребата ( Ctrl + Shift + L или Данни / сортиране и филтриране / филтриране) Избраните линии ще бъдат поставени в отделна таблица.
В тази статия считайте най-често срещаните искания, например: изборът на редовете на таблиците, при които стойността от цифровата колона попада в определения диапазон (интервал); Изборът на линии, в който датата ще въведе определен период; Задачи с 2 текстови критерия и други. Нека започнем с прости запитвания.
1. Един цифров критерий (изберете стоките, които имат цената над минималната)
примерен файл, лист Един критерий - номер ).
Трябва да се покаже в отделна таблица само тези записи (линии) от таблицата с източника, в която цената е по-висока от 25.
Решаването на тази и последващите задачи могат лесно да бъдат с. За да направите това, изберете заглавията на таблицата с източника и кликнете върху Ctrl + Shift + L. Чрез падащия списък на заглавието изберете Цифрови филтри ..., след това задайте необходимите условия за филтриране и щракнете върху OK.
Ще бъдат показани записи, отговарящи на условията за подбор.
Друг подход е да се използва. За разлика от избраните линии ще бъдат поставени в отделна таблица - особена, която например може да бъде форматирана в стила, различна от таблицата с източника или да произвежда други модификации.
Критерий (минимална цена) ще бъде поставен в клетката E6. , таблица за филтрирани данни - в диапазона D10: E19. .
Сега изберете диапазона D11: D19. (Колонен продукт) и във въведението:
Индекс (А11: A19;
Най-малкото (ако ($ e $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
Инсулт ($ b $ 10))
Вместо Inter. Натиснете комбинацията от ключове Ctrl + Shift + Enter(Формулата на масиф).
E11: E19. (колонна цена) Къде и въведете същото:
Индекс (B11: B19;
Най-малкото (ако ($ e $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
Инсулт ($ b $ 10))
В резултат на това получаваме нова таблица, която ще съдържа само стоки, които цените не са по-малко посочени в клетката E6. .
Да покажем динамиката на получената заявка за избор, ние въвеждаме E6. стойност 55. Само 2 записа ще попаднат в новата таблица.
Ако таблицата с източника добавете нов продукт с цена 80, тогава автоматично ще бъде добавен нов запис към новата таблица.
Забележка. Също така може да се използва и за изходни филтрирани данни. Изборът на конкретен инструмент зависи от задачата, изправена пред потребителя.
Ако не сте удобни за използване massiva формулакойто връща няколко стойности, можете да използвате друг подход, който се разглежда в разделите по-долу: 5.a, 7, 10 и 11. В тези случаи се използват.
2. два цифрови критерия (изберете стоките, които цената попада в диапазона)
Нека има таблица на източника с списък на стоките и цените (виж примерен файл, листОбхват на числа).
Критериите (границите на по-ниски и най-високи цени) ще бъдат поставени в диапазона E5: E6. .
Тези. Ако цената на стоките попада в посочения интервал, такъв запис ще се появи в новата таблица на филтрираните данни.
За разлика от предишната задача, ние ще създадем две: стоки и цени (без тях можете да направите, но те са удобни при писането на формули). Съответните формули трябва да изглеждат като мениджър на име ( Формули / Специфични имена / име на имена), Както следва (виж фигурата по-долу).
Сега изберете диапазона D11: D19. И във въведението:
Индекс (стоки;
Най-малко (
Ако (($ 5 $ 5<=Цены)*($E$6>\u003d Цени); ред (цени); "");
Вместо Inter. Натиснете комбинацията от ключове Ctrl + Shift + Enter.
Същите манипулации произвеждат с диапазон E11: E19. Къде и въведете подобно:
Индекс (цени;
Най-малко (
Ако (($ 5 $ 5<=Цены)*($E$6>\u003d Цени); ред (цени); "");
Ред (Цени) -провой ($ B $ 10)) - линия ($ b $ 10))
В резултат на това получаваме нова таблица, която ще съдържа само стоки, които цените попадат в интервала, посочен в клетките E5. и E6. .
Да покаже динамиката на получения доклад (заявка за избор) E6. стойност 65. Друг запис от таблицата с източници ще бъде добавен към новата таблица, удовлетворяваща новия критерий.
Ако добавите нов продукт с цена в таблицата с източник от 25 до 65, към новата таблица ще бъде добавен нов запис.
Примерният файл съдържа и формули за обработка на грешки, когато ценовата колона съдържа стойност на грешка, например # случаи / 0! (виж листа Обработка на грешки).
Следните задачи се решават по подобен начин, така че няма да ги разгледаме подробно.
3. Един критерий (изберете тези продукти, които се определя датата на доставка)
примерен файл, листЕдин критерий - дата).
За избора на редове, формулите на масив, подобен на задачата1 (вместо критерия<= используется =):
=Индекс (A12: A20; най-малкото (ако ($ e $ 6 \u003d B12: B20; ред (B12: B20); ""); низ (B12: B20) - низ ($ b $ 11)) - линия ($ B $ 11))
Индекс (B12: B20; най-малкото (ако ($ e $ 6 \u003d B12: B20; ред (B12: B20); ""); низ (B12: B20) - низ ($ B $ 11)) - линия ($ 11)) - линия B $ 11))
4. Две критерии дата (изберете стоките, които датата на доставка попада в диапазона)
Нека има таблица на източника със списък на датите на стоки и доставка (виж примерен файл, листПериод от време).
Моля, обърнете внимание, че колоната с дати не е сортирана.
Решение1.: За избор на редове можете да използвате.
Влезте в клетката D12. формулата на масива:
Индекс ($ 12: $ 20;
Най-голямото (($ 6 $ 6<=$B$12:$B$20)*($E$7>\u003d $ B $ 12: $ B $ 20) * (линия ($ b $ 12: $ B $ 20) -прокок ($ B $ 11));
$ J $ 12-линия (A12) + линия ($ B $ 11) +1))
Забележка: След въвеждане на формулата, вместо клавиша Enter, трябва да натиснете комбинацията Ctrl + Shift + Enter. Тази клавишна комбинация се използва за въвеждане на формула за масива.
Копирайте формулата за масиф до желания брой клетки. Формулата ще върне само тези стойности на стоки, които бяха доставени в обхвата на тези дати. Останалите клетки ще съдържат грешки # номер! Грешки Б. пример за файлове (списък с дата) .
Подобна формула трябва да бъде въведена за дати в е. колона
В клетка J12. изчислява се броят на редовете на таблицата с източници, отговарящи на критериите:
Колективност (B12: B20; "\u003e \u003d" & $ 6; B12: B20;<="&$E$7)
Редове на таблицата на източника, които отговарят на критериите.
Решение2.: За да изберете редове, можете да използвате формулите за масив, подобно на задачата2 (т.е.):
=Индекс (A12: A20; най-малкият (ако (($ 6)<=B12:B20)*($E$7>\u003d B12: B20); линия (B12: B20); ""); низ (B12: B20) -стркк ($ B $ 11)) - линия ($ B $ 11))
Индекс (B12: B20; най-малък (ако (($ 6)<=B12:B20)*($E$7>\u003d B12: B20); линия (B12: B20); ""); низ (B12: B20) -стркк ($ B $ 11)) - линия ($ B $ 11))
За да въведете първата формула, маркирайте обхвата на клетките G12: G20. . След въвеждане на формулата, вместо клавиша Enter, трябва да натиснете клавиша Ctrl + Shift + Enter.
Решение3.: Ако колоната с дати е сортирана, тогава не можете да използвате формулите за масив.
Първо, е необходимо да се изчисли първата и последната позиция на линиите, които отговарят на критериите. След това изходните струни.
Този пример отново ясно показва колко по-лесни формули за писане.
5. един критерий дата (изберете тези продукти, които датата на доставка не е по-рано / не по-късно от посоченото)
Нека има таблица на източника със списък на датите на стоки и доставка (виж примерен файл, лист Един критерий - дата (не по-късно) ).
За избор на редове, датата, на която не е по-ранна (включително самата дата), се използва формулата на масината:
=Индекс (A12: A20; най-малък (ако ($ e $ 7<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
Също така в примерния файл се дават формули: не по-рано (без); Не по-късно (включително); Не по-късно (без).
7. Критерий за един текст (изберете определен вид стоки)
Нека има таблица на източника с списък на стоките и цените (виж примерен файл, листЕдин критерий - текст).
8. Два текстови критерия (изберете стоките от определен тип на определения месец)
Нека има таблица на източника с списък на стоките и цените (виж примерен файл, лист 2 Критерии - текст (и) ).
Индекс ($ 11: $ $ 19;
Най-малкият (ако (($ f $ 6 \u003d $ a $ 11: $ a $ 19) * ($ f $ 7 \u003d $ b $ 11: $ b $ 19); ред ($ $ 11: $ $ 19 ) -стрк ($ 0 $ 10); 30); ред (DVSSL ("A1: A" & ESTEXT ($ a $ 11: $ a $ 19))))))
Изразяване ($ F $ 6 \u003d $ $ 11: $ $ 19) * ($ f $ 7 \u003d $ b $ 11: $ b $ 19) Определя както условията (стоки и месеца).
Изразяване Ред (DVSSL ("A1: A" & CREKOK ($ A $ 11: $ A $ 19)))) Форми (1: 2: 3: 4: 5: 6: 7: 8: 9), т.е. Номера на реда в таблицата.
9. Два текстови критерия (изберете продукти от определени видове)
Нека има таблица на източника с списък на стоките и цените (виж примерен файл, лист2 Критерии - Текст (или)).
За разлика от задачата, 7 ще вземат струните с 2 вида ().
За избор на редове се използва формулата на масината:
Индекс ($ 11: $ 19;
Най-голямото ((($ e $ 6 \u003d $ a $ 11: $ $ 19) + ($ e $ 7 \u003d $ a $ 11: $ a $ 19)) * (линия ($ a $ 11: $ a $ 19) -Полюк ($ \u200b\u200b$ 10)); преброено ($ $ 11: $ $ 19; $ e $ 6) + броене ($ a $ 11: $ $ 19; $ e $ 7) -cons ( $ $ 11: A11) +1))
Състояние ($ E $ 6 \u003d $ $ 11: $ $ 19) + ($ e $ 7 \u003d $ $ 11: $ $ 19) Тя гарантира, че стоките от определени видове от жълти клетки (стоки2 и стоки3) ще бъдат избрани. A + (допълнение) се използва за задача (трябва да се извърши най-малко 1 критерий).
Горният израз ще върне масив (0: 0: 0: 0: 1: 1: 1: 0: 0). Умножаване на него Ред ($ $ 11: $ $ 19) -прок ($ $ 10). На (1: 2: 3: 4: 5: 6: 7: 8: 9), ние получаваме множество позиции (брой на табличните линии), които отговарят на критериите. В нашия случай ще бъде масив (0: 0: 0: 0: 5: 6: 0: 0).
Като пример, направете решение на следната задача: Изберете стоки, чиято цена се намира в определен диапазон и повтаря посочения брой пъти или повече.
Като първоначално, вземете таблицата на стоките.
Да предположим, че ние се интересуваме от това колко и кои партии стоки са доставени на цена от 1000R. до 2000 година (Критерий 1). Освен това страните с една и съща цена трябва да бъдат най-малко 3 (критерий 2).
Решението е формулата за масиф:
Най-малката (линия ($ $ 14: $ 27) * ($ c $ 14: $ c $ 27\u003e \u003d $ b $ 7) * ($ c $ 14: $ c $ 27<=$C$7)*($D$14:$D$27>\u003d $ B $ 10); F14 + ($ 8 - $ g $ 9))
Тази формула връща редове номера, които отговарят на двата критерия.
Формула \u003d Summice (($ c $ 14: $ c $ 27\u003e \u003d $ b $ 7) * ($ c $ 14: $ c $ 27<=$C$7)*($D$14:$D$27>\u003d $ B $ 10)) Изчислява броя на редовете, които отговарят на критериите.
11. Ние използваме стойността на критерия (всички) или (всички)
В примерни файлове на листа "11. Критерий за всяко или (всички)" Тази опция се изпълнява.
Формулата в този случай трябва да съдържа функция, ако (). Ако стойността е избрана (всички), формулата се използва за изходни стойности, без да се вземат предвид този критерий. Ако е избрана друга стойност, критерият работи по обичайния начин.
Ако ($ c $ 8 \u003d "(всички)";
Най-малката ((линия ($ b $ 13: $ b $ 26) -строй ($ b $ 12)) * ($ d $ 13: $ d $ 26\u003e \u003d $ d $ 8); F13 + ($ g $ 6- $ g $ 7));
Най-малката ((линия ($ b $ 13: $ 2 $ 26)-лв. ($ 12)) * ($ d $ 13: $ d $ 26\u003e \u003d $ d $ 8) * ($ c $ 13: $ C $ 26 \u003d $ c $ 8); f13 + ($ g $ 6- $ g $ 7))))
Останалата част от формулата е подобна на тези, обсъждани по-горе.
Вземане на данни за данните
Създайте образец от лист5 на колона "качествено изпълнение, процент". (от лист 8, таблица 7)
За да направите избор на данни, трябва да изпълните следните стъпки:
Определете броя на елементите на новия масив върху дадено условие, като въведете променлива, като използвате оператора за входяща кутия
Декларирайте и обновете новия масив
Да образуват нов масив. За да направите това, посочете номера на първия елемент на новия масив u \u003d 1. След това се извършва цикълът, който записва пробата състоянието на колоната "качество на качеството, процент.". Ако резултатът от проверката на истината, елементът на анализирания масив се превръща в елемент на нов масив.
Изтегли нов елемент на листа 8
Baby Baby ()
Листове ("List8"). Изберете
Dim a () като вариант
Redim a (1 до n1, 1 до m)
Vvod "лист5", а, n1, m, 4
C \u003d входната кутия ("Въведете състоянието")
Листове ("List8"). Клетки (5.11) \u003d c
За i \u003d 1 до n1
Ако a (i,
8) >
d \u003d D + 1
Листове ("List8"). Клетки (5.10) \u003d d
Dim b () като вариант
Redim b (1 до d, 1 до m)
За i \u003d 1 до n1
Ако a (i,
8)\u003e листове ("list8"). Тогава клетките (5.11)
За J \u003d 1 до m
B (u, j) \u003d a (i, j)
u \u003d u + 1
За i \u003d 1 до d
За J \u003d 1 до m
Листове ("List8"). Клетки (i + 4, j) \u003d b (i, j)
Фиг.6. Данни за таблица след вземане на проби
Създаване на автоматичен макрос
Включете макрозаписа. Сервиз\u003e Макро\u003e Старт Запис\u003e OK. Ще се появи квадрат, където бутонът за спиране. На листа5 (доклад) подчертаваме таблицата без заглавия и резултати, копирайте в списъка10 (Auto Collector). Ние подчертаваме таблицата без заглавия и в елемента от менюто, изберете Данни\u003e Филтър\u003e Автоматичен филтър\u003e Избери състояние\u003e OK. Празнуваме колоната, на която ще сортираме. Завърши работата на макроса.
SUB MACRO2 стойност ()
Макро 22 макро макрос
Листове ("list5"). Изберете
Избор. Копие.
Листове ("Списък9"). Изберете
ActiveSheet. Паста
Диапазон ("H5: H17"). Изберете
Приложение. Cutcopymode \u003d False
Избор. Автофилтър.
ActiveSheet. Обхват ("$ 5: $ 17 $ 17"). Autofilter поле: \u003d 1, критерии1: \u003d "\u003e 80", _
Оператор: \u003d Xland
Диапазон ("G22"). Изберете
Фиг.7. Данни за таблица след автоматично вземане на проби
Определяне на максималната и минималната стойност
Определят стойностите на макс и мин в колоните "общо", "абсолютна производителност, процент", "висококачествено изпълнение" (Таблица 9, лист 10)
За да определите стойностите на Max и Min, трябва да изпълните следните стъпки:
Задайте стандартната променлива, която ще бъде текущият минимум (максимум)
Ранното сравнява всеки елемент от набора с текущия минимум (максимум) и ако този елемент не отговаря на условията за търсене (в случаи от минимум, той е по-голям, и в случай на максимален - по-малко), след това стандартът стойността се заменя със стойността на сравнения елемент
След пълно разглеждане на всички елементи в променливата на текущия минимум (максимум) е валиден минимум (максимум)
Стойността на минимум (максимум) се показва в съответните клетки.
Под minmax ()
Dim a () като вариант
n1 \u003d листове ("лист4"). Клетки (5,12)
m \u003d листове ("list2"). Клетки (5,12)
Redim a (1 до n1, 1 до m)
Vvod "лист5", а, n1, m, 4
Vivod "list10", а, n1, m, 4
Vvod "list10", а, n1, m, 4
За J \u003d 3 до m
maxa \u003d 0.00001.
мина \u003d 1000000.
За i \u003d 1 до n1
Ако a (i, j)\u003e maxa
maxa \u003d a (i, j)
Ако a (i, j)< minA Then
мина \u003d a (i, j)
Листове ("list10"). Клетки (i + 4 + 2, j) \u003d maxa
Листове ("list10"). Клетки (I + 4 + 3, J) \u003d Мина
Макрос е поредица от действия, които са записани и запазени за по-нататъшна употреба. Запазеният макрос може да бъде възпроизведен от специален екип. С други думи, можете да записвате действията си в макроса, да го запазите и след това да позволите на други потребители да играят съхранени в макроса за действие, като просто натискат ключа. Това е особено удобно при разпределянето на доклади за обобщени таблици.
Да предположим, че искате да предоставите на нашите клиенти възможност да групирате доклади за консолидирани таблици по месеци, четвъртинки и години. Технически, процесът на групиране може да изпълнява всеки потребител, но някои от вашите клиенти няма да се считат за необходими, за да разберат това. В подобен случай можете да запишете един макрос на групиране в продължение на месеци, а другият - в четвърти и третата - по година. След това създайте три бутона - по един на всеки макрос. Тогава вашите клиенти, които нямат опит с консолидирани таблици, ще бъдат задължителни само за да кликнете върху бутона, за да групирате правилно обобщената таблица.
Основното предимство на използването на макроси в докладите за консолидирани таблици е да предостави на клиентите способност за бързо извършване на такива операции в консолидираните таблици, които не могат да изпълнят в обичайната ситуация. Поради това ефективността на анализа на предоставените данни е значително увеличаваща се.
Изтеглете бележка във формат или, свалете с примери (вътре в файла Excel с макроси; политиката на доставчика не позволява директно да качвате файла на този формат на сайта).
Макро запис
Обърнете внимание на консолидираната таблица, показана на фиг. 1. Можете да актуализирате тази консолидирана таблица, като я щракнете с десния бутон и изберете командата. Опресняване. Ако сте записали действия под формата на макрос по време на актуализирането на таблицата с въртене, тогава вие или някой друг потребител може да възпроизведе тези действия и да актуализира консолидираната таблица в резултат на началото на макроса.
Фиг. 1. Записване на действия при актуализиране на тази обобщена таблица ще актуализира допълнително данните в резултат на стартирането на макроса
Първият етап в макрозаписа е повикването на диалоговия прозорец Макро запис. Кликнете върху раздела Разработчик Лентите и кликнете върху бутона Макро запис. (Ако не можете да намерите раздела на лентата РазработчикИзберете раздела Файли кликнете върху бутона Параметри. В диалоговия прозорец, който се появява диалог Excel Settings. Избери категория Задаване на лента И в десния списък изберете квадратчето Разработчик. В резултат на това лентата ще се появи на лентата Разработчик,) Алтернативен начин за започване на запис на макро- кликнете върху бутона (фиг. 2).
В диалоговия прозорец Макро запис Въведете следната макросформация (фиг. 3):
Имемакро. Името трябва да описва действията, извършени от макроса. Името трябва да започне с писмо или подчертаване; не трябва да съдържа пространство и други неприемливи признаци; Не трябва да съвпада с вграденото име Excel или името на друг обект в книгата.
Комбинацияключ. В това поле можете да въведете всяко писмо. Тя ще бъде част от ключовата комбинация, която ще се използва за игра на макроса. Ключовата комбинация е по избор. По подразбиране само Ctrl се предлага като начало на комбинацията. Ако искате комбинацията да включите също, наберете буквата в прозореца, докато държите клавиша Shift
Save.в. Това показва мястото на съхранение на макроса. Ако ще разпространявате резюме на таблица на други потребители, изберете параметър Товакнига. Excel също ви позволява да спасите макроса Нова книга или в Личен макроси.
Описание. Това поле описва описанието на създадения макрос.
Фиг. 3. Настройка на прозореца Макро запис
Тъй като макрос актуализира консолидираната таблица, изберете име Ъпгрейди. Можете също да зададете Ctrl + Shift + Q Key комбинация. Не забравяйте, че след създаването на макрос, ще използвате тази клавишна комбинация, за да я стартирате. Изберете параметър като място за съхранение на макроса Тази книга и кликнете Добре.
След като кликнете в диалоговия прозорец Макро запис На бутона Добре Стартира макрос. На този етап всички действия, които изпълнявате в Excel, ще бъдат регистрирани.
Щракнете с десния бутон на мишката в консолидираната област на маса и изберете командата. Опресняване (както е на фиг. 1, но вече в режим на запис на макроса). След актуализиране на таблицата за въртене можете да спрете процеса на записване на макроса с помощта на бутона. Стоп запис Tabs. Разработчик. Или кликнете върху бутона, показан на фиг. 2.
Така че, просто записахте първия си макрос. Сега можете да изпълните макроса, като използвате комбинацията Ctrl + Shift + Q.
Предупреждение за макросферата. Трябва да се отбележи, че в случай на записване на макроси от потребителя те ще бъдат изпълнявани без никакви ограничения от страна на подсистемата за сигурност. Въпреки това, когато разпространява работна книга, съдържаща макроси, е необходимо да се предостави възможност на други потребители да се уверят, че няма риск за отваряне на работните файлове, а изпълнението на макросите няма да доведе до инфекцията на вирусната система. По-специално, веднага ще забележите, че примерният файл, използван в тази глава, няма да функционира напълно, ако не е специално позволен Excel да изпълнява макроси в него.
Най-лесният начин да се гарантира безопасността на макросите е да се създаде надеждно местоположение - папка, към която ще бъдат поставени само "надеждни" работни книги, които не съдържат вируси. Надеждно местоположение позволява на вас и вашите клиенти да извършват макроси в работни места без никакви ограничения от страна на подсистемата за сигурност (такова поведение се запаметява, докато работните книги са в доверителна папка).
За да конфигурирате надеждно местоположение, изпълнете следните стъпки.
Изберете раздела RIBBON Разработчик и кликнете върху бутона Безопасност макроси. На екрана се появява диалогов прозорец. Център за управление на сигурността.
Кликнете върху бутона Добавете ново местоположение.
Кликнете върху бутона ПрегледЗа да зададете папка за работещи файлове, на които имате доверие.
След определяне на надеждно местоположение, произволни макроси ще бъдат изпълнени по подразбиране за всички работни книги в нея.
В Excel 2013 моделът на сигурност беше подобрен. Сега файловете на работната книга, които преди това са били "доверие", бяха запомнени, т.е. След отваряне на книгата на Excel и кликнете върху бутона Разрешаване на съдържание Excel помни това състояние. В резултат на това тази книга попада в отговорност на доверените и ненужните въпроси не се определят по време на последващото му отваряне.
Създаване на потребителски интерфейс с помощта на контроли за формуляри
Стартиране на макрос, използвайки комбинацията Ctrl + Shift + Q в случай, когато консолидиран доклад за таблицата има само един макрос. (Освен това, потребителите трябва да знаят тази комбинация.) Но да предположим, че искате да предоставите на нашите клиенти няколко макроса, изпълняващи различни действия. В този случай трябва да предоставите на клиентите ясен и лесен начин за пускане на всеки макрос, без да прибягвате до запаметяване на ключови комбинации. Идеалното решение е прост потребителски интерфейс под формата на набор от контроли като бутони, ленти за превъртане и други средства, които ви позволяват да извършвате макроси с кликвания на мишката.
Excel предлага на ваше разположение набор от инструменти, предназначени да създадат потребителски интерфейс директно в електронната таблица. Тези инструменти се наричат \u200b\u200bелементи за управление. Основната идея е, че можете да поставите контрола на формуляра в електронната таблица и да му зададете макрос на него, който е записан по-рано. След назначаване макросът ще работи по този елемент.
Контролите за формуляри могат да бъдат намерени в групата Форма контролни елементи Ленти ленти Разработчик. За да отворите палитрата на контролите, кликнете върху тази група на бутона Вмъкване (Фиг. 4).
Фиг. 4. Формирайте контролен елемент Бутон
Забележка: В допълнение към контрола на формуляра, палитрата също присъства ActiveX Elements.. Въпреки че са сходни, те са програмни различни обекти. Форма контролни елементис ограничените си функции и прости настройки, тя беше специално предназначена за настаняване на работни листове. В същото време ActiveX Elements. Се прилагат главно в персонализирани формуляри. Поправете правилото за публикуване на работните листове изключително формуляри за контрол.
Трябва да изберете контроли, които са най-подходящи за решаване на задачата. В този пример клиентите трябва да предоставят възможност за актуализиране на консолидираната таблица с кликване върху бутона. Кликнете върху контролния елемент Бутон, Преместете показалеца на мишката на мястото на работния лист, където трябва да се намира бутонът и щракнете върху мишката.
След като поставите бутона в таблицата, диалоговият прозорец се отваря. Задайте макрос обект (Фиг. 5). Изберете желания макрос (в нашия случай - Ъпгрейди, записани по-рано) и кликнете върху бутона Добре.
Фиг. 5. Изберете макроса, който ще бъде присвоен на бутона и кликнете върху бутона. Добре. В този случай трябва да се приложи макрос Ъпгрейди
След като поставите всички необходими контроли в шарнирната таблица, можете да форматирате таблицата, за да създадете основен интерфейс. На фиг. 6 показва доклад за обобщена таблица след форматирането.
Промяна на записания макрос
В резултат на макрозаписите, програмата Excel създава модул, който съхранява, който сте завършили. Всички записани действия са представени от VBA-кодове, от които макрос се състои. Можете да добавите различни функции в докладите на консолидираната таблица, като конфигурирате VBA код за получаване на необходимите резултати. За да улесните да разберете как всички работи, създайте нов макрос, изтегляте петте първите записи за клиентите. Кликнете върху раздела Разработчик и кликнете върху бутона Макро запис. Диалоговият прозорец, показан на фиг. 7. Назовете създадения макрос ПървоНсаказчиков и посочете мястото на спестяване Тази книга. Кликнете ДобреДа започнете да записвате макрос.
След като започнете да записвате, кликнете върху стрелката до полето Потребителско име, Изберете Филтриране по стойност и вариант Първите 10. (Фиг. 8А). В диалоговия прозорец, който се появява, задайте настройките, както е показано на фиг. 8б. Тези настройки показват, за да показват данните от пет клиенти, най-добрите по отношение на продажбите. Кликнете Добре.
Фиг. 8. Изберете Филтър (A) и конфигурирайте параметрите (B), за да покажете петте пет клиенти за продажби.
След успешния запис на всички действия, необходими за извличане на петте клиенти на продажбите, отидете в раздела Разработчик и кликнете върху бутона Стоп запис.
Сега имате макрос, който ще филтрира консолидираната маса за извличане на петте най-добри клиенти на продажбите. Трябва да се направи така, че макросът да реагира на състоянието на лентата за превъртане, т.е. Използвайки лентата за превъртане, трябва да можете да укажете броя на клиентите, чиито данни ще бъдат показани в консолидирания доклад за таблицата. По този начин, използвайки лентата за превъртане, потребителят ще може да извлече петте най-добри, осем най-добри или тридесет и два най-добри клиенти по свое усмотрение.
За да добавите превъртане до електронната таблица в електронната таблица, отидете в раздела. РазработчикКликнете върху бутона ВмъкванеИзберете контрола върху палитрата Плъзгач И го поставете на работния лист. Кликнете с десния бутон върху управляващия елемент Плъзгач Формат на обект. Отваря се диалогов прозорец Формат за управление (Фиг. 9). В него направете следните промени в настройките: параметър Минимална стойност Забележка 1, параметър Максимална стойност - стойност 200 и в полето Комуникация с клетка Въведете стойността на $ m $ 2, така че в клетката m2 да покажете лентата за превъртане. Кликнете върху бутона ДобреДа приложите посочените по-горе настройки.
Сега трябва да сравните новозаписания макрос ПървоНсаказчиков С контролен елемент Плъзгачразположени на работния лист. Кликнете с десния бутон върху управляващия елемент Плъзгач И в контекстното меню изберете командата Задайте макросЗа да отворите диалоговия прозорец Macro назначаване. Присвояване на лента за превъртане Записан макрос ПАРИНЦАКАКАЗКИКОВ.. Macro ще се извършва всеки път, след като кликнете върху лентата за превъртане. Тествайте създадената лента за превъртане. След като кликнете върху лентата, макросът ще започне ПАРИНЦАКАКАЗКИКОВ. И номерът в клетката M2 ще се промени, за да покаже състоянието на лентата за превъртане. Номерът в клетката M2 играе важна роля, тъй като се използва за свързване на макроса към лентата за превъртане.
Единственото нещо, което трябва да направите, е да принудите макроса да обработи броя в клетката на М2, като го обвързва с лента за превъртане. За да направите това, отидете в VBA-кода на макроса. За да направите това, отидете в раздела Разработчик и кликнете върху бутона Макроси. Отваря се диалогов прозорец Макро (Фиг. 10). Можете да стартирате, изтривате и редактирате избрания макрос. За да покажете Macro кода на VBA на екрана, изберете макрос и кликнете върху бутона. Промяната.
Фиг. 10. За достъп до кода на Macro VBA ПървоНсаказчиковизберете макроса и кликнете върху бутона Промяната
Визуалния основен редактор с VBA код на макроса се появява на екрана (фиг. 11). Вашата цел е да замените номера 5 твърдо посочено в кода, инсталиран по време на запис на макроса, стойността в клетката M2, която е свързана с лентата за превъртане. Първоначално беше записан макрос, предназначен да покаже първите пет клиенти, които имат най-голям доход.
Премахнете номера 5 от кода и въведете следния израз в:
ActiveShiet.Range ("m2") .Value
Добавете два струна към макроса, за да почистите филтрите:
Диапазон ("A4").
ActiveShiet.pivottables ("pivittable1") .pivotfields ("име на клиент") .Clearallfilters
Сега макро кодът трябва да изглежда като показан на фиг. 12.
Затворете визуалния основен редактор и се върнете към консолидираната маса. Тествайте лентата за превъртане, плъзнете плъзгача до стойността 11. Макросът трябва да стартира и филтрира 11 записа за най-добрите клиенти на продажбите.
Синхронизиране на две консолидирани таблици, използващи един падащ списък
Доклада, показан на фиг. 13, съдържа две обобщени маси. Всяка от тях има поле на страница, което ви позволява да избирате търговски пазар. Проблемът е, че всеки път, когато избирате пазарен в полето на страницата на една пилонова маса, трябва да изберете същия пазар в полето на страницата на друга шарнирна маса. Синхронизирането на филтрите на двете таблици на етапа на анализ на данни не представлява голям проблем, но има вероятност вие или вашите клиенти да го забравят.
Фиг. 13. Две обобщени таблици съдържат полета на страницата, които извършват филтриране на данни на пазарите. За да анализирате данните на отделен пазар, трябва да синхронизирате двете обобщени таблици.
Един метод за синхронизиране на тези обобщени таблици е да използвате падащия списък. Идеята е да се записват макроси, които избичават желания пазар от полето Пазар на продажби В двете таблици. След това трябва да създадете падащ списък и да го попълните с имената на пазарите на продажби от две обобщени маси. Накрая, записаният макрос трябва да се променя, за да филтрира двете обобщени таблици, като се използват стойности от падащия списък. За да разрешите тази задача, трябва да изпълните следните стъпки.
1. Създайте нов макрос и задайте име на него. Синхрамници.. Когато записът започне, изберете в полето Пазарни продажби на двете Резюме Масови пазарни продажби Калифорния И да спрете макрозаписа.
2. Покажете контрола на формуляра на екрана и добавете падащия списък на екрана.
3. Създайте твърдо определен списък на всички консолидирани пазари на таблици. Обърнете внимание, че първият елемент на списъка показва стойността (всички). Трябва да активирате този елемент, ако искате да изберете всички пазари в падащия списък.
4. На този етап докладът на консолидираната маса трябва да изглежда като показан на фиг. четиринадесет.
Фиг. 14. На Ваше разположение има всички необходими средства: Macro променя полето Пазар на продажби И двете въртящи се таблици, падащ списък и списък на всички продажби, съдържащи се в консолидираната маса
5. Щракнете с десния бутон върху падащия списък и изберете командата в контекстното меню. Формат на обектДа конфигурирате контрола.
6. Първо, задайте размера на стойностите на източника, използвани за попълване на падащия списък, както е показано на фиг. 15. В този случай говорим за списъка на пазарите на продажби, създадени от вас в параграф 3. След това посочете клетката, която показва номера на последователността на избрания елемент (в този пример е клетката H1). Параметър Брой линии на устните Определя колко редове ще бъдат отразени едновременно в падащия списък. Кликнете върху бутона Добре.
Фиг. 15. Настройките на падащия списък трябва да посочат списъка на пазарите на продажби като начална гама от стойности и като точка на свързване - за определяне на клетката H1
7. Сега имате възможност да изберете продажби в падащия списък, както и да определите съответния номер на последователността в клетката H1 (Фиг. 16). Възниква въпросът: защо стойността на индекса е вместо истинското име на пазара? Тъй като падащият списък не връща име, но номерът. Например, когато изберете падащия списък на името на Калифорния в клетката H1, се появява стойност 5. Това означава, че Калифорния е петият елемент от списъка.
Фиг. 16. Падачният списък сега е изпълнен с имената на пазарите, а избраният брой на избрания пазар се показва в клетката H1.
8. За да използвате номера на последователността вместо името на пазара, трябва да го изпратите с помощта на функцията Индекс.
9. Въведете функцията Индекс, която преобразува номера на последователността от клетката H1 към ясна стойност.
10. Функцията Индекс отнема два аргумента. Първият аргумент представлява диапазона на стойностите на списъка. В повечето случаи ще използвате същия диапазон, който падащото меню е запълнено. Вторият аргумент е номер на последователност. Ако номерът на последователността се въвежда в клетката (например в клетката Н1, както на фиг. 17), можете просто да се обърнете към тази клетка.
Фиг. 17. Функционалният индекс в клетката I1 превръща номера на последователността, съхранен в клетката H1, до стойността. Ще използвате стойност в клетката I1, за да промените макроса
11. Редактиране на макрос Синхрамници.Използване на стойността в клетката I1 вместо твърда зададена стойност. Кликнете върху раздела Разработчик и кликнете върху бутона Макроси. Диалоговият прозорец се появява на екрана, показан на фиг. 18. Изберете макрос в него Синхрамници. и кликнете върху бутона Редактиране.
Фиг. 18. За достъп до кода Macro VBA, изберете Macro Синхрамници. и кликнете Промяната
12. Когато записвате макрос, сте избрали продажби на калифорния от полето в двете обобщени таблици Пазар на продажби. Както може да се види от фиг. 19, пазарът на Калифорния сега е твърдо поставен в VBA кода на макроса.
13. Сменете стойността на "Калифорния" на експресията ActiveShiet.Range ("I1") .Value, която се отнася до стойността в клетката I1. На този етап макро кодът трябва да изглежда като показан на фиг. 20. След като промените макроса, затворете визуалния основен редактор и се върнете в електронната таблица.
Фиг. 20. Заменете стойността на "Калифорния" с експресия на ActiveShiet.Range ("i1") .Value и затворете визуалния основен редактор
14. Остава само да се гарантира изпълнението на макроса при избора на търговски пазар в падащия списък. Щракнете с десния бутон върху падащия списък и изберете параметъра Задайте макрос. Изберете макрос Synchmarket. и кликнете върху бутона Добре.
15. Скриване на редове и колони с полета на страницата в консолидираните таблици, както и списъкът на пазарите и индексните формули, които сте създали.
На фиг. 21 показва крайния резултат. Получили сте потребителски интерфейс, който позволява на клиентите да избират продажния пазар в двете обобщени таблици, като използват един падащ списък.
Когато изберете новия елемент в падащия списък, размерите на колоните автоматично се променят, за да се приспособят всички данни, показани в тях. Подобно поведение на програмата е отегчено с форматирането на работния шаблон. Можете да го попречите, ако кликнете върху резюмето на десния бутон на мишката и изберете командата Настройки на таблицата. На екрана, в който искате да зададете диалогов прозорец, се появява диалогов прозорец. Автоматично променя ширината на колоните при актуализиране.
Бележката е написана на базата на книгата на Jelen, Александър. . Глава 12.
Използвайки Excel инструменти, можете да изберете определени данни от случаен обхват, едно условие или повече. За да се решат такива проблеми, се използват масив или макрос формули или макроси. Помислете за примерите.
Как да направите проба в Excel чрез състояние
Когато използвате формулата на масината, избраните данни са показани в отделна таблица. Какво е предимството на този метод в сравнение с обичайния филтър.
Таблица на източника:
Първо се научете да правите проба по един цифров критерий. Задачата е да избирате от таблицата на стоките с цената над 200 рубли. Един от начините за решаване е използването на филтриране. В резултат на това само тези продукти, които отговарят на заявката, ще останат в таблицата на източника.
Друго решение е да се използва формулата на масива. Свързаните редове за заявки ще бъдат поставени в отделна таблица за отчети.
Първо създаваме празна таблица до оригинала: дублирани заглавия, брой редове и колони. Новата таблица заема обхвата от E1: G10.Tube E2: E10 (колона "Дата") и въвежда следната формула: ( }.
За да получите формула за масиф, натиснете клавиша Ctrl + Shift + Enter. В следващата колона - "продукт" - въвеждаме подобна твърда формула: ( ). Само първият аргумент на функцията функция се е променил.
В колоната "Цена" въвеждаме същата формула на масива, като променяме първия аргумент на функцията на индекса.
В резултат на това получаваме доклад за стоки с цена повече от 200 рубли.
Тази проба е динамична: когато промените заявката или външния вид в източника на нови продукти, докладът автоматично ще се промени.
Номер на задача 2 - избирайте от таблицата с източника стоките, които са се продавали 09/20/2015. Това означава, че критерият за подбор е датата. За удобство въвеждаме желаната дата до отделна клетка, I2.
За да разрешите проблема, се използва подобна формула за масивна памет. Само вместо критерия).
Такива формули се въвеждат в други колони (cm по-горе).
Сега използваме текстов критерий. Вместо датата в клетката I2, ние въвеждаме текста "продукт 1". Леко променя формулата на масива: ( }.
Такава голяма функция за вземане на проби в Excel.
Вземане на проби за няколко състояния в Excel
Първо, вземете два цифрови критерия:
Задачата е да се изберат стоките, които струват по-малко от 400 и повече от 200 рубли. Ние комбинираме термините знак "*". Формулата на масива е както следва: ( }.!}
Това е за първата колона на таблицата на отчетите. За втората и третата, променете първия индекс на функциите на аргумента. Резултат:
За да направите проба в няколко дати или цифрови критерии, използваме подобни твърди формули.
Произволна проба в Excel
Когато потребителят работи с голямо количество данни, за по-късен анализ може да се изисква случайна проба. Всеки ред може да бъде присвоен на случаен номер и след това да се прилага сортиране за вземане на проби.
Комплект за източници:
Първо поставете две празни колони отляво. В клетката A2 ние предполагаме адхезивна формула (). Завъртете го на цялата колона:
Сега копирайте колона с случайни числа и го поставете в колона V. Това е необходимо, така че тези цифри да не се променят при извършване на нови данни в документа.
За да поставите стойностите, а не формулата, щракнете с десния бутон на мишката върху колоната и изберете инструмента "Специален вмъкване". В прозореца, който се отваря, поставяме отметка срещу елемента "Стойност":
Сега можете да сортирате данните в колоната във възходящ или низходящ. Процедурата за представяне на първоначалните стойности също ще се промени. Избираме произволен брой редове отгоре или по-ниски - получаваме произволна проба.
Различен (39)
Excel бъгове и бъгове (3)
Как да получите списък с уникални (не повтарящи се) стойности?
Представете си голям списък с различни имена, пълно име, номера на маса и др. И е необходимо да оставите списъка на всички същите имена от този списък, но те не повтарят - т.е. Изтрийте всички дублирани записи от този списък. Как иначе се нарича: Създаване на списък с уникални елементи, списък с не повтарящи се, без дубликати. За това има няколко начина: вградени инструменти на Excel, вградени формули и, накрая, с код Visual Basic за приложение (VBA) и обобщени маси. В тази статия разгледайте всяка от възможностите.
Използване на вградените функции на Excel 2007 и повече
В Excel 2007 и 2010 е по-лесно да се направи просто - има специален екип, който се нарича -. Намира се в раздела Данни (данни) подраздел Инструменти за данни (инструменти за данни)
Как да използвате тази команда. Маркирайте колоната (или по) С тези данни, в които искате да изтриете дублирани записи. Отидете в раздела Данни (данни) -Премахване на дубликати (премахване на дубликати).
Ако изберете една колона, но ще има повече колони с данни (или поне една колона), след това Excel ще предложи да избере: да разшири диапазона на извадката от тази колона или да оставите избора, както и да изтривате данни само в Специален обхват. Важно е да запомните, че ако не разширите обхвата, данните ще се променят само в една колона, а данните в съседната колона ще останат без най-малката промяна.
Ще се появи прозорец с дублиращи се параметри за отстраняване 
Поставете кърлежите срещу тези колони, дубликати, в които трябва да премахнете и кликнете върху OK. Ако заглавията на данните са разположени и в специалния диапазон, след това по-добре поставете флаг Моите данни съдържат заглавияда не изтривате данните в таблицата (ако те изведнъж напълно съвпадат с стойността на заглавието).
Метод 1: Разширен филтър
В случая с Excel 2003 всичко е по-сложно. Няма такъв инструмент Премахване на дубликат. Но има такъв прекрасен инструмент като Разширен филтър. През 2003 г. този инструмент може да бъде намерен в Данни -Филтър -Разширен филтър. Красотата на този метод е, с нея, не можете да развалите изходните данни, но за да създадете списък в друг диапазон. През 2007-2010 г. Excel, той също има, но малко втвърден. Намира се в раздела Данни (данни), Група Сортиране и филтриране (сортиране и филтър) - Напреднали)
Как да го използвате: Изпълнете посочения инструмент - Появява се диалогов прозорец: 
- Лечение: Избирам Копирайте резултата на друго място (копиране на друго място).
- Диапазон на източника: Изберете диапазон с данни (В нашия случай, това е A1: A51).
- Обхват на критериите (обхват на критериите): В този случай оставяме празни.
- Поставете резултата в диапазона (копиране до): Посочете първата клетка към изходните данни - всяко празно (на снимка - e2).
- Поставете отметка Само уникални записи (само уникални записи).
- Zhmem. Добре.
Забележка: Ако искате да поставите резултата на друг лист, тогава просто посочете друг лист няма да работи. Можете да зададете клетка на друг лист, но ... Alas и Ah ... Excel ще даде съобщение, че не може да копира данни в други листове. Но това може да бъде заобиколено и съвсем просто. Просто трябва да бягаме Разширен филтър От този лист, към който искаме да поставим резултата. И като изходни данни изберете Данни от всеки лист - е разрешено.
Можете също така да не направите резултата в други клетки, но за да филтрирате данните на място. Данните от това няма да бъдат засегнати - това ще бъде обичайното филтриране на данните.
За да направите това, просто трябва да изберете обработващия елемент Списък на филтъра на място (филтриране на списъка, на място).
Метод 2: Формули
Този метод е по-трудно в разбирането за неопитни потребители, но създава списък с уникални стойности, без да променя първоначалните данни. Е, това е по-динамично: ако промените данните в таблицата с източника, резултатът ще се промени. Понякога е полезно. Ще се опитам да обясня на пръстите какво и какво: например списък с данни е разположен в колона А (A1: A51, където A1 е заглавка). Показване на списък, с който ще бъдем в колона, започвайки с C2 клетка. Формулата в C2 ще бъде както следва:
(\u003d Индекс ($ 2: $ 51; най-малкият (ако (брой $ 1: c1; $ 2: $ $ 51) \u003d 0; ред ($ 1: $ 1: $ a $ 50)); 1)))
(\u003d Индекс ($ 2: $ 51; малък (ако (Countif ($ c $ 1: c1; $ 2: $ $ 51) \u003d 0; ред ($ 1 $ 1: $ a $ £ 50)); 1)))
В статията е даден подробен анализ на тази формула:
Трябва да се отбележи, че тази формула е масивна формула. Това може да се каже на къдравите скоби, в които е приключила тази формула. И тази формула се въвежда в клетъчната комбинация - Ctrl.+Смяна.+Inter.. След като въведохме тази формула в C2, трябва да го копираме и да поставите няколко реда, така че да покажем точно всички уникални елементи. Веднага след като формулата в долните клетки ще се върне #Number! - Това означава, че всички елементи са показани и под формулата няма смисъл. За да може грешката да избягва и да направи формулата по-универсална (без да се простира всеки път, възникне грешка), можете да използвате не-глас проверка:
За Excel 2007 и повече:
(\u003d Ако индексът (индекс ($ 2: $ 51; най-малкия (ако (брояч ($ 1: c1; $ 2: $ $ 51) \u003d 0; низ ($ a $ 51) \u003d 0; 1: $ a $ 50)); 1)); ""))
(\u003d Iferror (индекс ($ 2: $ 51; малък (ако (countif ($ c $ 1: c1; $ 2: $ $ 51) \u003d 0; ред ($ 1: $ 1: $ $ 50)); 1)); ""))
За Excel 2003:
(\u003d Ако (Eash (най-малкото (ако ($ 1: c1; $ 2: $ 51) \u003d 0; низ ($ 1: $ 1 $ 50)); 1)) ""; индекс ($ 2 $ 2: $ 51; най-малкия (ако (брой c $ 1: c1; $ 2: $ $ 51) \u003d 0; ред ($ 1 $ 1: $ $ 50)); 1))))
(\u003d IF (ISERR (ISERR (IF (COSTIF ($ c $ 1: C1; $ 2: $ $ 51) \u003d 0; ред ($ 1: $ 1 $ 50))); 1); ; индекс ($ 2: $ 51; малък (ако (countif ($ c $ 1: c1; $ 2: $ $ 51) \u003d 0; ред ($ 1 $ 1: $ $ 50 )); 1)))
Тогава вместо грешка #Number! (#Num!) Ще имате празни клетки (Не е съвсем празен, разбира се - с формули :-)).
Малко повече за различията и нюансите на формулата и ако (EOSH може да бъде прочетена в тази статия: както в клетка с формула, вместо да се показва грешка 0
Метод 3: VBA код
Този подход ще изисква разрешение на макроси и основни познания за работата с тях. Ако не сте сигурни за знанията си, ви препоръчвам да прочетете тези статии:
- Какво е макрос и къде да го търсим? Статията е прикачен видео урок
- Какво е модул? Какви са модулите? Ще бъде необходимо да се разбере къде да се вмъкне кодовете по-долу.
И двата кода по-долу трябва да бъдат поставени в стандартен модул. Трябва да бъдат разрешени макроси.
Първоначалните данни ще бъдат оставени в същия ред - списъкът с данни се намира в колоната "А" (A1: A51, където А1 - заглавие). Само изтегляме списък Няма да бъдем в колона С, но в колоната Е2, започвайки с E2 клетката:
Sub Extract_Unique () Dim Vitem, Avarr, Li като Long Redim Avarr (1 до Rows.count, 1 до 1) с нова колекция при резюме на грешка след това за всеки вит в обхвата ("A2", клетки (редове.count, 1) .End (xLUP)). Стойност "(! Lang: клетки (редове.count, 1) .end (xLUP) - определя последната напълнена клетка в addd indem, csstr (VIDEM), ако е err \u003d 0, ако err \u003d 0 + 1: Avarr (li, 1) \u003d VITEM else: err.Clear End, ако следващ край, ако Li тогава .resize (li) .value \u003d Avarr End Sub
С този код можете да извлечете уникален не само от една колона, но и от всякакъв диапазон от колони и линии. Ако вместо низ
Обхват ("A2", Cells(Rows.Count, 1).End(xlUp)).Value !}
Посочете селекция.Врай, резултатът от кода на кода ще бъде списък с уникални елементи от обхвата, разпределен на активния лист. Само тогава би било хубаво да се промени изходната клетка, за да се промени - вместо това
Поставете този, в който няма данни.
Можете също така да зададете конкретен диапазон:
Диапазон ("C2", клетки (редове.count, 3) .end (xLUP)). Стойност
Универсален код за избор на уникални стойности
Кодът по-долу може да се използва за всички диапазони. Достатъчно е да се започне, да посочите диапазона със стойностите за избор на само не повтарящ се (е разпределена повече от една колона) и клетката за извеждане на резултата. Посочените клетки ще бъдат разглеждани, от тях ще бъдат избрани само уникални стойности (празни клетки се пропускат) и полученият списък ще бъде записан, като се започне с посочената клетка.
| SUB EXCTION_UNIQUE () DIM X, AVARR, LI като дълги AVVALS DIM RVALS като диапазон, RResultCell като обхват на възрастоване на грешка След това "Заявете адреса на клетките, за да изберете уникални стойности Задаване на RVALS \u003d COMPLAY.InputBox ( "Посочете гамата от клетки за вземане на проби от уникални стойности", "Заявка за данни", "A2: A51", тип: \u003d 8) Ако RVALS не е нищо "Ако бутонът за отказ е натиснат Exit Sub Flow Ако "Ако е зададена само една клетка - няма смисъл да се избира Ако rvals.count \u003d 1 след това msgbox "За да изберете уникални стойности, трябва да посочите повече от една клетка", Vbinformation, "www.syt" излизат под края, ако "Изрежете празни струни и колони извън работния обхват Задаване на rvals \u003d пресичане (rvals, rvals.parent.usedrange) "Ако са показани само празни клетки извън работния обхват Ако rvals не е нищо, msgbox "Няма достатъчно данни, за да изберете стойности", vbinformation, "www.syt" изход под свършване, ако avvals \u003d rvals.value "Заявете клетка за извеждане на резултата Задайте RResultCell \u003d Application.Inputbox ( "Посочете клетката, за да вмъкнете избрани уникални стойности", "Заявка за данни", "E2", тип: \u003d 8), ако rresultcell не е нищо тогава "Ако бутонът за отказ е натиснат Exit Sub Flow Ако "Определете максималното възможно измерение на масива за резултата Redim Avarr (1 до Rows.count, 1 до 1) "Използване на обекта за събиране (колекция) "Изберете само уникални записи, "Тъй като колекциите не могат да съдържат многократни стойности С нова колекция при повторно резюме след това за всеки x в avvals, акоLen (cstr (x)) тогава - Прескачаме празни клетки .Add x, cstr (x) "Ако добавеният елемент вече е в колекцията - ще се появи грешка "Ако няма грешки - тази стойност все още не е направена, "Добавете към получения масив Ако err \u003d 0 тогава li \u003d li + 1 avarr (li, 1) \u003d x else - Не забравяйте да почистите обекта на грешката Err.clear End Ако приключите, ако следващия край с "Запишете резултата на листа, като започнете с посочената клетка Ако li тогава rresultcell.cells (1, 1) .resize (li) .value \u003d avarr |
SUB EXCEX_UNIQU () DIM X, AVARR, LI като дълги дим AVVALS DIM RVALS като диапазон, RResultCell като обхват на грешка Възобновяване Следващ "Заявете адреса на клетките, за да изберете уникалния комплект RVALS \u003d COMPLAY.Inputbox (" Посочете гамата от клетки) За вземане на проби от уникални стойности "," заявка за данни "," A2: A51 ", тип: \u003d 8), ако RVALS не е нищо, ако е натиснат бутонът за излизане, ако е натиснат бутонът," ако е зададена само една клетка - то само една клетка е зададена - тя Няма смисъл да се избере дали rvals.count \u003d 1 след това msgbox ", за да изберете уникални стойности, трябва да зададете повече от една клетка", vbinformation, "www.sight" изход под свършване, ако "пее празни струни и колони извън работния обхват Задаване на RVALS \u003d Intersect (RVALS, RVALS.PRENT. UULDRANGE) "Ако само празни клетки са посочени извън IF RVALS не е нищо, след това msgbox" не достатъчно данни, за да изберете стойности ", vbinformation, www..Value (! Lang: заявка a клетка към изходна настройка rresultcell \u003d comply.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: Обобщени таблици
Няколко нестандартни метода за извличане на уникални стойности.
- Изберете една или повече колони в таблицата, отидете в раздела Вмъкване (вмъкване) -Група Таблица (таблица) -Обобщена таблица (pivittable)
- В диалоговия прозорец Създаване на таблица за създаване на върхове Проверете коректността на обхвата на данните (или инсталирате нов източник на данни)
- посочете поставянето на консолидираната таблица:
- На нов лист (нов работен лист)
- На съществуващ лист (съществуващ работен лист)
- потвърдете създаването на бутон, като натиснете бутона ДОБРЕ.
Като Резюме Таблици при обработката на данни, които са поставени в областта на редовете или колоните, от тях се вземат само уникални стойности за по-късен анализ, не се изисква от нас дори сметка, освен за създаването на обобщена таблица и да се създаде данни на желаната колона в зоната на низ или колони.
При примера на файла, приложен към статията:
Какво е неудобството на работата с консолидирана в този случай: при промяна на източниците данни, консолидираната таблица ще трябва да бъде актуализирана ръчно: разпределя всяка клетка на консолидираната таблица - докато бутонът на мишката - Обновяване) или щифт Данни (данни) -Актуализиране на всички (опресняване на всички) -Обновяване). И ако данните за източника се актуализират динамично и по-лошо - ще е необходимо да се посочи рейзът на източника. И още един минус - данните в консолидираната таблица не могат да бъдат променяни. Ето защо, ако трябва да работите с получения списък в бъдеще, след като създадете желания списък, трябва да го копирате с помощта и да го поставите в желания лист.
За да разберем по-добре всички действия и да се научите как да се справяте с консолидираните таблици, силно препоръчвам да четете общата информация за обобщените таблици - видео урок е приложен към него, в който визуално демонстрирам простотата и удобството на работата с основните възможности на Консолидирани таблици.
В приложеното пример, в допълнение към описаните техники, се записва малко по-сложно изменение на добива на уникални елементи и код, а именно: премахване на уникални елементи по критерий. Какъв е въпросът: ако в една колона на фамилното име и във втория (в) някои данни (във файла е месеци) и е необходимо да извлечете уникалните стойности на колоната само за избраното фамилно име . Примери за подобни извличащи уникални са разположени на лист. Извлечение по критерий.
Пример за изтегляне:
(108.0 KIB, 14 152 Downloads)
Статията помогна? Споделете линк с приятели! Видео уроци
