Як зрабіць выбарку ў Excel: 4 рабочыя спосабу

Пры працы з табліцамі Excel даволі часта даводзіцца праводзіць адбор у іх па вызначаным крытэру ці па некалькіх умовам. У праграме зрабіць гэта можна рознымі спосабамі пры дапамозе шэрагу інструментаў. Давайце высветлім, як вырабіць выбарку ў Эксэля, выкарыстоўваючы разнастайныя варыянты.

выкананне выбаркі

Выбарка дадзеных складаецца ў працэдуры адбору з агульнага масіву тых вынікаў, якія задавальняюць зададзеным умовам, з наступным вывадам іх на лісце асобным спісам або ў зыходным дыяпазоне.

Спосаб 1: прымяненне пашыранага автофильтра

Найбольш простым спосабам вырабіць адбор з'яўляецца прымяненне пашыранага автофильтра. Разгледзім, як гэта зрабіць на канкрэтным прыкладзе.

1. Вылучаем вобласць на лісце, сярод дадзеных якой трэба вырабіць выбарку. Ва ўкладцы «Галоўная» пстрыкаем па кнопцы «Сартаванне і фільтр». Яна размяшчаецца ў блоку налад «Рэдагаванне». У якое адкрылася пасля гэтага спісу выконваем пстрычка па кнопцы «Фільтр».

   

   Ёсць магчымасць паступіць і па-іншаму. Для гэтага пасля вылучэння вобласці на лісце перамяшчаемся ва ўкладку «Дадзеныя». Пстрыкаем па кнопцы «Фільтр», якая размешчана на стужцы ў групе «Сартаванне і фільтр».



2. Пасля гэтага дзеяння ў шапцы табліцы з'яўляюцца піктаграмы для запуску фільтравання ў выглядзе перавернутых вастрыём уніз невялікіх трыкутнікаў на правым краі вочак. Клікаем па дадзеным значку ў загалоўку таго слупка, па якім жадаем вырабіць выбарку. У запусцім меню пераходзім па пункце «Тэкставыя фільтры». Далей выбіраем пазіцыю «Наладжвальны фільтр ...».



3. Актывуецца акно карыстацкай фільтрацыі. У ім можна задаць абмежаванне, па якім будзе вырабляцца адбор. У выпадальным спісе для слупка які змяшчае ячэйкі лікавага фармату, які мы выкарыстоўваем для прыкладу, можна выбраць адно з пяці відаў умоў:
   • роўна;
   • ня роўнае;
   • больш;
   • больш або роўна;
   • менш.

   Давайце ў якасці прыкладу задамо ўмова так, каб адабраць толькі значэння, па якіх сума выручкі перавышае 10000 рублёў. Ўсталёўваем перамыкач у пазіцыю «Больш». У правае поле ўпісваем значэнне «10000». Каб вырабіць выкананне дзеяння, пстрыкаем па кнопцы «OK».



4. Як бачым, пасля фільтравання засталіся толькі радкі, у якіх сума выручкі перавышае 10000 рублёў.



5. Але ў гэтым жа слупку мы можам дадаць і другая ўмова. Для гэтага зноў вяртаемся ў акно карыстацкай фільтрацыі. Як бачым, у яго ніжняй частцы ёсць яшчэ адзін перамыкач ўмовы і адпаведнае яму поле для ўводу. Давайце ўсталюем цяпер верхнюю мяжу адбору ў 15000 рублёў. Для гэтага выстаўляем перамыкач у пазіцыю «Менш», а ў поле справа ўпісваем значэнне «15000».

   Акрамя таго, існуе яшчэ перамыкач умоў. У яго два становішча «І» і «АБО». Па змаўчанні ён усталяваны ў першым становішчы. Гэта азначае, што ў выбарцы застануцца толькі радкі, якія задавальняюць абодвум абмежаванням. Калі ён будзе выстаўлены ў становішча «АБО», то тады застануцца значэння, якія падыходзяць пад любое з двух умоў. У нашым выпадку трэба выставіць перамыкач у становішча «І», гэта значыць, пакінуць дадзеную настройку па змаўчанні. Пасля таго, як усе значэння ўведзеныя, пстрыкаем па кнопцы «OK».



6. Цяпер у табліцы засталіся толькі радкі, у якіх сума выручкі не менш 10000 рублёў, але не перавышае 15000 рублёў.



7. Аналагічна можна наладжваць фільтры і ў іншых слупках. Пры гэтым маецца магчымасць захоўваць таксама фільтраванне і па папярэднім умовам, якія былі зададзены ў калонках. Такім чынам, паглядзім, як вырабляецца адбор з дапамогай фільтра для вочак у фармаце даты. Клікаем па значку фільтрацыі ў адпаведным Стоўбцах. Паслядоўна клікаем па пунктах спісу «Фільтр па даце» і «Наладжвальны фільтр».



8. Зноў запускаецца акно карыстацкага автофильтра. Выканаем адбор вынікаў у табліцы з 4 па 6 мая 2016 года ўключна. У перамыкачы выбару умоў, як бачым, яшчэ больш варыянтаў, чым для лікавага фармату. Выбіраем пазіцыю «Пасля або роўна». У полі справа усталёўваем значэнне «2016/05/04». У ніжнім блоку ўсталёўваем перамыкач у пазіцыю «Да або роўна». У правым поле ўпісваем значэнне «2016/05/06». Перамыкач сумяшчальнасці умоў пакідаем у становішчы па змаўчанні - «І». Для таго, каб прымяніць фільтрацыю у дзеянні, ціснем на кнопку «OK».



9. Як бачым, наш спіс яшчэ больш скараціўся. Цяпер у ім пакінутыя толькі радкі, у якіх сума выручкі вар'іруецца ад 10000 да 15000 рублёў за перыяд з 04.05 па 2016/05/06 ўключна.



10. Мы можам скінуць фільтраванне ў адным з слупкоў. Зробім гэта для значэнняў выручкі. Клікаем па значку автофильтра ў адпаведным Стоўбцах. У выпадальным спісе пстрыкаем па пункце «Выдаліць фільтр».



11. Як бачым, пасля гэтых дзеянняў, выбарка па суме выручкі будзе адключаная, а застанецца толькі адбор па дат (з 2016/05/04 па 2016/05/06).



12. У дадзенай табліцы маецца яшчэ адна калонка - "Назва". У ёй утрымліваюцца дадзеныя ў тэкставым фармаце. Паглядзім, як сфармаваць выбарку з дапамогай фільтрацыі па гэтых значэнняў.

    Клікаем па значку фільтра ў назве слупка. Паслядоўна пераходзім па назвах спісу «Тэкставыя фільтры» і «Наладжвальны фільтр ...».



13. Зноў адкрываецца акно карыстацкага автофильтра. Давайце зробім выбарку па назвах «Бульба» і «Мяса». У першым блоку перамыкач умоў ўсталёўваем у пазіцыю «Роўна». У полі справа ад яго ўпісваем слова «Бульба». Перамыкач ніжняга блока гэтак жа ставім у пазіцыю «Роўна». У полі насупраць яго робім запіс - «Мяса». І вось далей мы выконваем тое, чаго раней не рабілі: ўсталёўваем перамыкач сумяшчальнасці умоў у пазіцыю «АБО». Цяпер радок, якая змяшчае любое з названых умоў, будзе выводзіцца на экран. Пстрыкаем па кнопцы «OK».



14. Як бачым, у новай выбарцы існуюць абмежаванні па даце (з 2016/05/04 па 2016/05/06) і па найменні (бульба і мяса). Па суме выручкі абмежаванняў няма.



15. Цалкам выдаліць фільтр можна тымі ж спосабамі, якія выкарыстоўваліся для яго ўстаноўкі. Прычым усё роўна, які менавіта спосаб ўжываўся. Для скіду фільтрацыі, знаходзячыся ва ўкладцы «Дадзеныя» пстрыкаем па кнопцы «Фільтр», якая размешчана ў групе «Сартаванне і фільтр».

    

    Другі варыянт прадугледжвае пераход ва ўкладку «Галоўная». Там выконваем пстрычка на стужцы па кнопцы «Сартаванне і фільтр» ў блоку «Рэдагаванне». У Актываваць спісе націскаем на кнопку «Фільтр».

Пры выкарыстанні любога з двух вышэйзгаданых метадаў фільтраванне будзе выдаленая, а вынікі выбаркі - ачышчаны. Гэта значыць, у табліцы будзе паказаны ўвесь масіў дадзеных, якімі яна размяшчае.

Урок: Функцыя автофильтр ў Excel

Спосаб 2: прымяненне формулы масіва

Зрабіць адбор можна таксама ужыўшы складаную формулу масіва. У адрозненне ад папярэдняга варыянту, дадзены метад прадугледжвае вывад выніку ў асобную табліцу.

1. На тым жа лісце ствараем пустую табліцу з такімі ж назвамі слупкоў у шапцы, што і ў зыходніка.



2. Вылучаем усе пустыя вочкі першай калонкі новай табліцы. Усталёўваем курсор у радок формул. Якраз сюды будзе заносіцца формула, якая вырабляе выбарку па ўказаных крытэрам. Адбярэм радкі, сума выручкі ў якіх перавышае 15000 рублёў. У нашым канкрэтным прыкладзе, ўводзімая формула будзе выглядаць наступным чынам:

   = Індэкс (A2: A29; найменшымі (КАЛІ (15000

   Натуральна, у кожным канкрэтным выпадку адрас вочак і дыяпазонаў будзе свой. На дадзеным прыкладзе можна супаставіць формулу з каардынатамі на ілюстрацыі і прыстасаваць яе для сваіх патрэб.



3. Бо гэта формула масіва, то для таго, каб прымяніць яе ў дзеянні, трэба націскаць ня кнопку Enter, а спалучэнне клавіш Ctrl + Shift + Enter. Робім гэта.



4. Вылучыўшы другі слупок з датамі і усталяваўшы курсор у радок формул, уводны наступнае выраз:

   = Індэкс (B2: B29; найменшымі (КАЛІ (15000

   Ціснем спалучэнне клавіш Ctrl + Shift + Enter.



5. Аналагічным чынам у слупок з выручкай ўпісваем формулу наступнага зместу:

   = Індэкс (C2: C29; найменшымі (КАЛІ (15000

   Зноў набіраем спалучэнне клавіш Ctrl + Shift + Enter.

   Ва ўсіх трох выпадках змяняецца толькі першае значэнне каардынатаў, а ў астатнім формулы цалкам ідэнтычныя.



6. Як бачым, табліца запоўненая дадзенымі, але знешні выгляд яе не зусім прывабны, да таго ж, значэння даты запоўненыя ў ёй некарэктна. Трэба выправіць гэтыя недахопы. Некарэктнасць даты звязана з тым, што фармат вочак адпаведнага слупка агульны, а нам трэба ўсталяваць фармат даты. Вылучаем ўвесь слупок, уключаючы вочкі з памылкамі, і клікаем па вылучэнню правай кнопкай мышы. У якое з'явілася спісе пераходзім па пункце «Фармат ячэйкі ...».



7. У якое адкрылася акне фарматавання адкрываем ўкладку «Колькасць». У блоку «Лікавыя фарматы» вылучаем значэнне «Дата». У правай частцы акна можна выбраць жаданы тып адлюстравання даты. Пасля таго, як налады выстаўлены, ціснем на кнопку «OK».



8. Цяпер дата адлюстроўваецца карэктна. Але, як бачым, уся ніжняя частка табліцы запоўненая вочкамі, якія ўтрымліваюць памылковае значэнне «# ЛІК!». Па сутнасці, гэта тыя вочкі, дадзеных з выбаркі для якіх не хапіла. Больш прывабна было б, калі б яны адлюстроўваліся наогул пустымі. Для гэтых мэтаў скарыстаемся умоўнай фарматаваннем. Вылучаем усе вочкі табліцы, акрамя шапкі. Знаходзячыся ва ўкладцы «Галоўная» клікаем па кнопцы «Умоўнае фарматаванне», якая знаходзіцца ў блоку інструментаў «Стылі». У якое з'явілася спісе выбіраем пункт «Стварыць правіла ...».



9. У якое адкрылася акне выбіраем тып правілы «Фарматаваць толькі вочкі, якія змяшчаюць». У першым полі пад надпісам «Фарматаваць толькі вочкі, для якіх выконваецца наступнае ўмова» выбіраем пазіцыю «Памылкі». Далей ціснем па кнопцы «Фармат ...».



10. У запусцім акне фарматавання пераходзім ва ўкладку «Шрыфт» і ў адпаведным полі выбіраем белы колер. Пасля гэтых дзеянняў пстрыкаем па кнопцы «OK».



11. На кнопку з сапраўды такім жа назвай ціснем пасля вяртання ў акно стварэння умоў.

Цяпер у нас ёсць гатовая выбарка па паказаным абмежавання ў асобнай належным чынам аформленай табліцы.

Урок: Ўмоўнае фарматаванне ў Excel

Спосаб 3: выбарка па некалькіх умоў з дапамогай формулы

Гэтак жа, як і пры выкарыстанні фільтра, з дапамогай формулы можна ажыццяўляць выбарку па некалькіх умовам. Для прыкладу возьмем усю тую ж зыходную табліцу, а таксама пустую табліцу, дзе будуць выводзіцца вынікі, з ужо выкананым лічбавым і умоўнай фарматаваннем. Ўсталюем першым абмежаваннем ніжнюю мяжу адбору па выручцы ў 15000 рублёў, а другім умовай верхнюю мяжу ў 20000 рублёў.

1. Ўпісваем ў асобным слупку межавыя ўмовы для выбаркі.



2. Як і ў папярэднім спосабе, па чарзе вылучаем пустыя слупкі новай табліцы і ўпісваем ў іх адпаведныя тры формулы. У першы слупок ўносім наступнае выраз:

   = Індэкс (A2: A29; найменшымі (КАЛІ (($ D $ 2 = C2: C29); СТРОКА (C2: C29); ""); СТРОКА (C2: C29) -СТРОКА ($ C $ 1)) - СТРОКА ($ C $ 1))

   У наступныя калонкі ўпісваем дакладна такія ж формулы, толькі змяніўшы каардынаты адразу пасля назвы аператара індэкс на адпаведныя патрэбным нам слупках, па аналогіі з папярэднім спосабам.

   Кожны раз пасля ўводу, не забываемся набіраць спалучэнне клавіш Ctrl + Shift + Enter.



3. Перавага дадзенага спосабу перад папярэднім заключаецца ў тым, што калі мы захочам памяняць мяжы выбаркі, то зусім не трэба будзе мяняць саму формулу масіва, што само па сабе даволі праблематычна. Дастаткова ў калонцы умоў на лісце памяняць межавыя колькасці на тыя, якія патрэбныя карыстачу. Вынікі адбору тут жа аўтаматычна зменяцца.

Спосаб 4: выпадковая выбарка

У Эксэля з дапамогай адмысловай формулы СЛЧИС можна таксама ўжываць выпадковы адбор. Яго патрабуецца вырабляць у некаторых выпадках пры працы з вялікім аб'ёмам дадзеных, калі трэба прадставіць агульную карціну без комплекснага аналізу ўсіх дадзеных масіва.

1. Злева ад табліцы прапускаем адзін слупок. У вочку наступнага слупка, якая знаходзіцца насупраць першай ячэйкі з дадзенымі табліцы, ўпісваем формулу:

   = СЛЧИС ()

   Гэтая функцыя выводзіць на экран выпадковы лік. Для таго, каб яе актываваць, ціснем на кнопку ENTER.



2. Для таго, каб зрабіць цэлы слупок выпадковых лікаў, усталёўваем курсор у ніжні правы кут ячэйкі, якая ўжо ўтрымлівае формулу. З'яўляецца маркер запаўнення. Працягваем яго ўніз з заціснутай левай кнопкай мышы паралельна табліцы з дадзенымі да яе канца.



3. Цяпер у нас маецца дыяпазон вочак, запоўнены выпадковымі лікамі. Але, ён утрымлівае ў сабе формулу СЛЧИС. Нам жа трэба працаваць з чыстымі значэннямі. Для гэтага варта выканаць капіраванне ў пусты слупок справа. Вылучаем дыяпазон вочак са выпадковымі лікамі. Размясціўшыся ва ўкладцы «Галоўная», пстрыкаем па абразку «Капіяваць» на стужцы.



4. Вылучаем пусты слупок і клікаем правай кнопкай мышы, выклікаючы кантэкстнае меню. У групе інструментаў «Параметры ўстаўкі» выбіраем пункт «Значэнні», паказаны ў вобразе піктаграмы з лічбамі.



5. Пасля гэтага, знаходзячыся ва ўкладцы «Галоўная», клікаем па ўжо знаёмаму нам значку «Сартаванне і фільтр». У выпадальным спісе спыняем выбар на пункце «Наладжвальная сартаванне».



6. Актывуецца акно налады сартавання. Абавязкова усталёўваем галачку насупраць параметра «Мае дадзеныя ўтрымліваюць загалоўкі», калі шапка маецца, а галачкі няма. У полі «Сартаваць па» паказваем найменне таго слупка, у якім змяшчаюцца скапіраваныя значэння выпадковых лікаў. У полі «Сартаванне» пакідаем налады па змаўчанні. У полі «Парадак» можна выбраць параметр як «Па ўзрастанні», так і «Па змяншэнні». Для выпадковай выбаркі гэта значэння не мае. Пасля таго, як налады зроблены, ціснем на кнопку «OK».



7. Пасля гэтага ўсе значэння табліцы выстройваюцца ў парадку ўзрастання або змяншэння выпадковых лікаў. Можна ўзяць любую колькасць першых радкоў з табліцы (5, 10, 12, 15 і да т.п.) і іх можна будзе лічыць вынікам выпадковай выбаркі.

Урок: Сартаванне і фільтраванне дадзеных у Excel

Як бачым, выбарку ў табліцы Excel можна вырабіць, як з дапамогай автофильтра, так і ужыўшы спецыяльныя формулы. У першым выпадку вынік будзе выводзіцца ў зыходную табліцу, а ў другім - у асобную вобласць. Маецца магчымасць вырабляць адбор, як па адным умове, так і па некалькіх. Акрамя таго, можна ажыццяўляць выпадковую выбарку, выкарыстаўшы функцыю СЛЧИС.