Извличане на данни в Microsoft Excel

Когато работите с електронни таблици в Excel, често е необходимо да ги подбирате според определен критерий или няколко условия. В програмата можете да направите това по различни начини, като използвате няколко инструмента. Нека да разберем как да направим селекция в Excel, като използваме различни опции.

Изпълнение на пример

Извадката на данните се състои в процедурата за избиране от общия масив на тези резултати, които отговарят на определените условия, и след това ги извежда на лист в отделен списък или в първоначалния диапазон.

Метод 1: Прилагане на усъвършенстван автофилтър

Най-лесният начин за избор е да използвате разширен автофилтър. Нека помислим как да направите това на конкретен пример.

1. Изберете областта на листа, между която трябва да изберете. В раздела "Начало" кликнете върху бутона "Сортиране и филтриране" . Той се поставя в блока за настройки "Редактиране" . В списъка, който се отваря след това, кликнете върху бутона "Филтриране" .

   Има възможност да направите същото по различен начин. За да направите това, след като изберете областта в листа, преминете към раздела "Данни" . Кликваме върху бутона "Филтър" , който се намира на лентата в групата "Сортиране и филтриране" .

2. След това действие иконите се появяват в заглавката на таблицата, за да започнете филтрирането под формата на малки обърнати триъгълници в десния край на клетките. Кликваме върху тази икона в заглавието на колоната, за която искаме да изберем. В менюто, което се отваря, отидете на елемента "Филтри за текст" . След това изберете елемента "Персонализиран филтър ..." .
3. Прозорецът за филтриране на потребители е активиран. В него можете да посочите ограничението, чрез което ще бъде направен изборът. В падащия списък за колоната, съдържаща клетката с цифров формат, която използваме за пример, можете да изберете един от петте вида условия:
   • е равно на;
   • не е равно;
   • повече от;
   • по-голяма или равна на;
   • по-малко.

   Нека да зададем условието като пример, така че да избираме само стойностите, с които размерът на приходите надхвърля 10 000 рубли. Задайте ключа в позиция "Още" . В дясното поле въведете стойността "10000" . За да изпълните действието, кликнете върху бутона "OK" .

4. Както можете да видите, след филтриране имаше само линии, в които размерът на приходите надхвърля 10 000 рубли.
5. Но в същата колона можем да добавим второто условие. За да направите това, се връщаме отново в прозореца за филтриране на потребителите. Както можете да видите, в долната му част има още един превключвател на състояние и съответното му поле за въвеждане. Нека сега зададем горната граница за избор до 15 000 рубли. За да направите това, поставете превключвателя в положение "Less" , а в полето в дясно влизаме стойността "15000" .

   Освен това има и превключвател на състоянието. Той има две позиции "Аз" и "ИЛИ" . По подразбиране той е настроен на първата позиция. Това означава, че в извадката ще има само линии, които удовлетворяват и двете ограничения. Ако е зададена в положение "OR" , остават стойности, които са подходящи за някое от двете условия. В нашия случай трябва да поставите превключвателя в позиция "И" , т.е. да оставите тази настройка като по подразбиране. След като въведете всички стойности, кликнете върху бутона "OK" .

6. Сега таблицата оставя само линии, в които размерът на приходите е не по-малко от 10 000 рубли, но не надвишава 15 000 рубли.
7. По същия начин можете да конфигурирате филтри в други колони. В този случай е възможно също да се съхранява и филтриране при предишните условия, които са били зададени в колоните. Така че, нека видим как се прави филтър за клетките във формат за дата. Кликнете върху иконата на филтъра в съответната графа. Постоянно кликнете върху елементите в списъка "Филтриране по дата" и "Персонализиран филтър" .
8. Прозорецът "Потребителски автоматичен филтър" се стартира отново. Ние ще направим избора на резултатите в таблицата от 4 до 6 май 2016 включително. В ключа за избор на състояние, както виждате, има още повече опции, отколкото за цифровия формат. Изберете позицията "След или равно . " В полето отдясно задайте стойността на "04/05/2016" . В долния блок поставете ключа в положение "Преди или равно" . В дясното поле въведете стойността "06.05.2016" . Превключвателят за съвместимост на състоянието остава в положение по подразбиране - "AND" . За да приложите филтрирането в действие, кликнете върху бутона "OK" .
9. Както можете да видите, нашият списък е допълнително намален. Сега оставя само линии, в които размерът на приходите варира от 10 000 до 15 000 рубли за периода от 04.05 до 06.05.2016 г., включително.
10. Можем да нулирам филтрирането в една от колоните. Да направим това за приходите. Кликнете върху иконата за автоматично филтриране в съответната графа. В падащия списък кликнете върху елемента "Изтрий филтъра" .
11. Както можете да видите, след тези действия извадката по размера на приходите ще бъде деактивирана и ще има само избор по дати (от 04/05/2016 до 06/05/2016).
12. В тази таблица има още една колона - "Име" . Той съдържа данни в текстов формат. Нека видим как да генерираме проба чрез филтриране на тези стойности.

    Кликнете върху иконата на филтъра в името на графата. Да преминем през имената на списъка "Текстови филтри" и "Потребителски филтър ..." .

13. Още веднъж се отваря прозорецът "Потребителски автоматичен филтър". Нека направим селекция върху имената "Картофи" и "Месо" . В първия блок поставете превключвателя на състояние в позиция "Равно" . В полето отдясно на него вписваме думата "картофи" . Превключвателят на долния блок също се поставя в положение "Равно" . На противоположното поле отбелязваме "Месо" . След това изпълняваме това, което не сме правили преди: задайте превключвателя за съвместимост на състоянието на позиция "OR" . Сега ще се покаже ред, съдържащ някое от посочените условия. Кликваме върху бутона "OK" .
14. Както можете да видите, в новата извадка има ограничения на датата (от 04/05/2016 до 06/05/2016) и по име (картофи и месо). Размерът на приходите не е ограничен.
15. Пълното премахване на филтъра може да бъде същият метод, който е бил използван за инсталирането му. И няма значение кой метод е бил използван. За да нулирате филтрирането, в раздела "Данни" кликнете върху бутона "Филтър" , който се намира в групата "Сортиране и филтриране" .

    Вторият вариант включва превключване към раздела Начало . Там кликваме върху лентата на бутона "Сортиране и филтриране" в блока "Редактиране" . В активирания списък кликнете върху бутона "Филтриране" .

Ако използвате някой от горните два метода, филтрирането ще бъде премахнато и резултатите от селекцията ще бъдат изтрити. Това означава, че в таблицата ще се покаже цялата гама от данни, които има.

Урокът: Функция "Автофилтър" в Excel

Метод 2: Прилагане на формула за масив

Също така можете да направите избор, като приложите сложна формула за масив. За разлика от предишната версия, този метод осигурява изхода на резултата в отделна таблица.

1. На същия лист създаваме празна таблица със същите имена на колони в заглавката като източник.
2. Изберете всички празни клетки от първата колона на новата таблица. Задайте курсора в лентата за формули. Тук ще бъде въведена формулата, като се направи проба според конкретните критерии. Избираме линиите, чиито приходи надхвърлят 15 000 рубли. В нашия конкретен пример входната формула ще изглежда така:

   =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Естествено, във всеки случай адресът на клетките и диапазоните ще бъде същият. В този пример можете да съответствате на формулата с координатите в илюстрацията и да я адаптирате според вашите нужди.

3. Тъй като това е матрична формула, за да я приложите в действие, трябва да натиснете клавиша Enter и клавишната комбинация Ctrl + Shift + Enter . Ние го правим.
4. Изберете втората колона с датите и поставете курсора в реда на формулата, въведете следния израз:

   =ИНДЕКС(B2:B29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Натиснете клавишната комбинация Ctrl + Shift + Enter .

5. По същия начин в колоната с приходи въведете следната формула:

   =ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   Отново напишете Ctrl + Shift + Enter .

   И в трите случая се променя само първата координатна стойност и в противен случай формулите са напълно идентични.

6. Както можете да видите, таблицата е пълна с данни, но нейният външен вид не е много привлекателен, освен това стойностите за дата са попълнени неправилно. Необходимо е да се коригират тези недостатъци. Неправилната дата се дължи на факта, че форматът на клетките в съответната колона е общ и трябва да зададем формата на датата. Изберете цялата колона, включително клетките с грешки, и кликнете върху селекцията с десния бутон на мишката. В списъка, който се показва, отидете на елемента "Format Cell ..." .
7. В прозореца за отворен формат отворете раздела "Брой" . В блока "Цифрови формати" изберете стойността "Дата" . В дясната част на прозореца можете да изберете желания вид показване на датата. След като зададете настройките, кликнете върху бутона "OK" .
8. Сега датата се показва правилно. Но както виждате, цялата долна част на таблицата е пълна с клетки, които съдържат грешната стойност "# NUMBER!" . Всъщност това са клетките, данните от извадката, за които не е имало достатъчно. Би било по-привлекателно, ако са били изобразени изобщо. За тези цели, нека използваме условно форматиране. Изберете всички клетки в таблицата, с изключение на капачката. В раздела "Начало" кликнете върху бутона "Условно форматиране" , който се намира в полето "Стилове" . В списъка, който се показва, изберете елемента "Създаване на правило ..." .
9. В прозореца, който се отваря, изберете типа на правилото "Форматирайте само клетките, които съдържат" . В първото поле под текста "Форматирайте само клетки, за които е изпълнено следното условие", изберете елемента "Грешки" . След това кликнете върху бутона "Форматиране ..." .
10. В прозореца за форматиране, който се отваря, отворете раздела "Font" и изберете бял цвят в съответното поле. След тези действия кликнете върху бутона "OK" .
11. На бутона с точно същото име кликнете, след като се върнете в прозореца Създаване на условия.

Сега имаме готов образец за посоченото ограничение в отделна правилно разработена таблица.

Урокът: Условно форматиране в Excel

Метод 3: Няколко условия, използващи формула

Подобно на използването на филтър, можете да използвате формула, която да вземете под внимание при няколко условия. Например, вземете същата таблица източник, както и празна таблица, където ще се показват резултатите, с вече изпълненото числено и условно форматиране. Установяваме първото ограничение на долната граница за подбор за приходи от 15 000 рубли, а второто условие е горната граница от 20 000 рубли.

1. Въвеждаме в отделната колона граничните условия за извадката.
2. Както и при предишния метод, един по един избира празните колони на новата таблица и вмъква съответните три формули в тях. В първата колона въведете следния израз:

   =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(($D$2=C2:C29);СТРОКА(C2:C29);"");СТРОКА(C2:C29)-СТРОКА($C$1))-СТРОКА($C$1))

   В следващите колони въведеме точно същите формули, като променим координатите непосредствено след името на оператора INDEX на колоните, съответстващи на колоните, от които се нуждаем, по аналогия с предишния метод.

   Всеки път след въвеждането, не забравяйте да въведете Ctrl + Shift + Enter .

3. Предимството на този метод спрямо предишната е, че ако искаме да променим границите на извадката, няма нужда да променяме самата матрична формула, която сама по себе си е доста проблематична. Достатъчно е в колоната на условията на листа да се променят граничните номера до онези, от които потребителят се нуждае. Резултатите от селекцията автоматично ще се променят.

Метод 4: случайно вземане на проби

В Excel с помощта на специалната формула RAND може да се използва и случайна селекция. Необходимо е да се създаде в някои случаи, когато се работи с голямо количество данни, когато трябва да представите обща картина без сложен анализ на всички масивни данни.

1. Отляво на масата минаваме една колона. В клетката на следващата колона, която е срещу първата клетка с данните от таблицата, въведете формулата:

   =СЛЧИС()

   Тази функция показва произволен номер. За да го активирате, натиснете бутона ENTER .

2. За да направите цяла колона с произволни числа, задайте курсора в долния десен ъгъл на клетката, която вече съдържа формулата. Появява се дръжката за пълнене. Издърпайте го с левия бутон на мишката, натиснат паралелно с таблицата с данни до края му.
3. Сега имаме набор от клетки, пълни с произволни номера. Но съдържа формулата на RAND . Също така трябва да работим с чисти ценности. За да направите това, копирайте го в празната колона отдясно. Избираме набор от клетки с произволни номера. Намирайки се в раздела "Начало" , кликнете върху иконата "Копирай" на лентата.
4. Изберете празната графа и кликнете с десния бутон на мишката, като извикате контекстното меню. В групата "Параметри за вмъкване" изберете елемента "Стойности" , представен като пиктограма с номера.
5. След това в раздела "Начало" кликнете върху познатата иконата "Сортиране и филтриране" . В падащия списък изберете "Custom Sort" .
6. Прозорецът за сортиране е активиран. Поставете отметка в квадратчето до "Моите данни съдържа заглавки" , ако има заглавка и няма отметка. В полето "Сортирай по" посочете името на колоната, в която се съдържат копираните стойности на произволните числа. В полето "Сортиране" оставаме настройките по подразбиране. В полето "Поръчка" можете да изберете опцията "Възходящ" или "Надолу" . За случайно вземане на проби това няма значение. След като направите настройките, кликнете върху бутона "OK" .
7. След това всички стойности на таблицата са подредени в ред на нарастващи или намаляващи произволни числа. Можете да вземете няколко от първите редове от таблицата (5, 10, 12, 15 и т.н.) и те могат да се считат за резултат от случайно вземане на проби.

Урокът: Сортиране и филтриране на данни в Excel

Както можете да видите, изборът в електронната таблица на Excel може да се извърши или с помощта на автоматичен филтър, или с помощта на специални формули. В първия случай резултатът ще бъде показан в таблицата с източници, а във втория - в отделна област. Възможно е да се избере едно от едно или няколко. Освен това можете да изберете произволно с помощта на функцията RANDOM .