Пры працы з табліцамі Excel даволі часта даводзіцца праводзіць адбор у іх па вызначаным крытэру ці па некалькіх умовам. У праграме зрабіць гэта можна рознымі спосабамі пры дапамозе шэрагу інструментаў. Давайце высветлім, як вырабіць выбарку ў Эксэля, выкарыстоўваючы разнастайныя варыянты.
выкананне выбаркі
Выбарка дадзеных складаецца ў працэдуры адбору з агульнага масіву тых вынікаў, якія задавальняюць зададзеным умовам, з наступным вывадам іх на лісце асобным спісам або ў зыходным дыяпазоне.
Спосаб 1: прымяненне пашыранага автофильтра
Найбольш простым спосабам вырабіць адбор з'яўляецца прымяненне пашыранага автофильтра. Разгледзім, як гэта зрабіць на канкрэтным прыкладзе.
- Вылучаем вобласць на лісце, сярод дадзеных якой трэба вырабіць выбарку. ва ўкладцы "Галоўная" пстрыкаем па кнопцы "Сартаванне і фільтр". Яна размяшчаецца ў блоку налад "Рэдагаванне". У якое адкрылася пасля гэтага спісу выконваем пстрычка па кнопцы "Фільтр".
Ёсць магчымасць паступіць і па-іншаму. Для гэтага пасля вылучэння вобласці на лісце перамяшчаемся ва ўкладку "Дадзеныя". Пстрыкаем па кнопцы "Фільтр", Якая размешчана на стужцы ў групе "Сартаванне і фільтр".
- Пасля гэтага дзеяння ў шапцы табліцы з'яўляюцца піктаграмы для запуску фільтравання ў выглядзе перавернутых вастрыём уніз невялікіх трыкутнікаў на правым краі вочак. Клікаем па дадзеным значку ў загалоўку таго слупка, па якім жадаем вырабіць выбарку. У запусцім меню пераходзім па пункце "Тэкставыя фільтры". Далей выбіраем пазіцыю "Наладжвальны фільтр ...".
- Актывуецца акно карыстацкай фільтрацыі. У ім можна задаць абмежаванне, па якім будзе вырабляцца адбор. У выпадальным спісе для слупка які змяшчае ячэйкі лікавага фармату, які мы выкарыстоўваем для прыкладу, можна выбраць адно з пяці відаў умоў:
- роўна;
- ня роўнае;
- больш;
- больш або роўна;
- менш.
Давайце ў якасці прыкладу задамо ўмова так, каб адабраць толькі значэння, па якіх сума выручкі перавышае 10000 рублёў. Ўсталёўваем перамыкач у пазіцыю "Больш". У правае поле ўпісваем значэнне "10000". Каб вырабіць выкананне дзеяння, пстрыкаем па кнопцы "OK".
- Як бачым, пасля фільтравання засталіся толькі радкі, у якіх сума выручкі перавышае 10000 рублёў.
- Але ў гэтым жа слупку мы можам дадаць і другая ўмова. Для гэтага зноў вяртаемся ў акно карыстацкай фільтрацыі. Як бачым, у яго ніжняй частцы ёсць яшчэ адзін перамыкач ўмовы і адпаведнае яму поле для ўводу. Давайце ўсталюем цяпер верхнюю мяжу адбору ў 15000 рублёў. Для гэтага выстаўляем перамыкач у пазіцыю "Менш", А ў поле справа ўпісваем значэнне "15000".
Акрамя таго, існуе яшчэ перамыкач умоў. У яго два становішча "І" і "АБО". Па змаўчанні ён усталяваны ў першым становішчы. Гэта азначае, што ў выбарцы застануцца толькі радкі, якія задавальняюць абодвум абмежаванням. Калі ён будзе выстаўлены ў становішча "АБО", То тады застануцца значэння, якія падыходзяць пад любое з двух умоў. У нашым выпадку трэба выставіць перамыкач у становішча "І", Гэта значыць, пакінуць дадзеную настройку па змаўчанні. Пасля таго, як усе значэння ўведзеныя, пстрыкаем па кнопцы "OK".
- Цяпер у табліцы засталіся толькі радкі, у якіх сума выручкі не менш 10000 рублёў, але не перавышае 15000 рублёў.
- Аналагічна можна наладжваць фільтры і ў іншых слупках. Пры гэтым маецца магчымасць захоўваць таксама фільтраванне і па папярэднім умовам, якія былі зададзены ў калонках. Такім чынам, паглядзім, як вырабляецца адбор з дапамогай фільтра для вочак у фармаце даты. Клікаем па значку фільтрацыі ў адпаведным Стоўбцах. Паслядоўна клікаем па пунктах спісу "Фільтр па даце" і "Наладжвальны фільтр".
- Зноў запускаецца акно карыстацкага автофильтра. Выканаем адбор вынікаў у табліцы з 4 па 6 мая 2016 года ўключна. У перамыкачы выбару умоў, як бачым, яшчэ больш варыянтаў, чым для лікавага фармату. выбіраем пазіцыю "Пасля або роўна". У полі справа усталёўваем значэнне "04.05.2016". У ніжнім блоку ўсталёўваем перамыкач у пазіцыю "Да або роўна". У правым поле ўпісваем значэнне "06.05.2016". Перамыкач сумяшчальнасці умоў пакідаем у становішчы па змаўчанні - "І". Для таго, каб прымяніць фільтрацыю у дзеянні, ціснем на кнопку "OK".
- Як бачым, наш спіс яшчэ больш скараціўся. Цяпер у ім пакінутыя толькі радкі, у якіх сума выручкі вар'іруецца ад 10000 да 15000 рублёў за перыяд з 04.05 па 2016/05/06 ўключна.
- Мы можам скінуць фільтраванне ў адным з слупкоў. Зробім гэта для значэнняў выручкі. Клікаем па значку автофильтра ў адпаведным Стоўбцах. У выпадальным спісе пстрыкаем па пункце "Сапраўды выдаліць фільтр".
- Як бачым, пасля гэтых дзеянняў, выбарка па суме выручкі будзе адключаная, а застанецца толькі адбор па дат (з 2016/05/04 па 2016/05/06).
- У дадзенай табліцы маецца яшчэ адна калонка - "Найменне". У ёй утрымліваюцца дадзеныя ў тэкставым фармаце. Паглядзім, як сфармаваць выбарку з дапамогай фільтрацыі па гэтых значэнняў.
Клікаем па значку фільтра ў назве слупка. Паслядоўна пераходзім па назвах спісу "Тэкставыя фільтры" і "Наладжвальны фільтр ...".
- Зноў адкрываецца акно карыстацкага автофильтра. Давайце зробім выбарку па назвах "Бульба" і "Мяса". У першым блоку перамыкач умоў ўсталёўваем у пазіцыю "Роўна". У полі справа ад яго ўпісваем слова "Бульба". Перамыкач ніжняга блока гэтак жа ставім у пазіцыю "Роўна". У полі насупраць яго робім запіс - "Мяса". І вось далей мы выконваем тое, чаго раней не рабілі: ўсталёўваем перамыкач сумяшчальнасці умоў у пазіцыю "АБО". Цяпер радок, якая змяшчае любое з названых умоў, будзе выводзіцца на экран. Пстрыкаем па кнопцы "OK".
- Як бачым, у новай выбарцы існуюць абмежаванні па даце (з 2016/05/04 па 2016/05/06) і па найменні (бульба і мяса). Па суме выручкі абмежаванняў няма.
- Цалкам выдаліць фільтр можна тымі ж спосабамі, якія выкарыстоўваліся для яго ўстаноўкі. Прычым усё роўна, які менавіта спосаб ўжываўся. Для скіду фільтрацыі, знаходзячыся ва ўкладцы "Дадзеныя" пстрыкаем па кнопцы "Фільтр", Якая размешчана ў групе "Сартаванне і фільтр".
Другі варыянт прадугледжвае пераход ва ўкладку "Галоўная". Там выконваем пстрычка на стужцы па кнопцы "Сартаванне і фільтр" ў блоку "Рэдагаванне". У Актываваць спісе націскаем на кнопку "Фільтр".
Пры выкарыстанні любога з двух вышэйзгаданых метадаў фільтраванне будзе выдаленая, а вынікі выбаркі - ачышчаны. Гэта значыць, у табліцы будзе паказаны ўвесь масіў дадзеных, якімі яна размяшчае.
Урок: Функцыя автофильтр ў Excel
Спосаб 2: прымяненне формулы масіва
Зрабіць адбор можна таксама ужыўшы складаную формулу масіва. У адрозненне ад папярэдняга варыянту, дадзены метад прадугледжвае вывад выніку ў асобную табліцу.
- На тым жа лісце ствараем пустую табліцу з такімі ж назвамі слупкоў у шапцы, што і ў зыходніка.
- Вылучаем усе пустыя вочкі першай калонкі новай табліцы. Усталёўваем курсор у радок формул. Якраз сюды будзе заносіцца формула, якая вырабляе выбарку па ўказаных крытэрам. Адбярэм радкі, сума выручкі ў якіх перавышае 15000 рублёў. У нашым канкрэтным прыкладзе, ўводзімая формула будзе выглядаць наступным чынам:
= Індэкс (A2: A29; найменшымі (КАЛІ (15000 <= C2: C29; СТРОКА (C2: C29); ""); СТРОКА () - СТРОКА ($ C $ 1)) - СТРОКА ($ C $ 1))Натуральна, у кожным канкрэтным выпадку адрас вочак і дыяпазонаў будзе свой. На дадзеным прыкладзе можна супаставіць формулу з каардынатамі на ілюстрацыі і прыстасаваць яе для сваіх патрэб.
- Бо гэта формула масіва, то для таго, каб прымяніць яе ў дзеянні, трэба націскаць ня кнопку Enter, А спалучэнне клавіш Ctrl + Shift + Enter. Робім гэта.
- Вылучыўшы другі слупок з датамі і усталяваўшы курсор у радок формул, уводны наступнае выраз:
= Індэкс (B2: B29; найменшымі (КАЛІ (15000 <= C2: C29; СТРОКА (C2: C29); ""); СТРОКА () - СТРОКА ($ C $ 1)) - СТРОКА ($ C $ 1))Ціснем спалучэнне клавіш Ctrl + Shift + Enter.
- Аналагічным чынам у слупок з выручкай ўпісваем формулу наступнага зместу:
= Індэкс (C2: C29; найменшымі (КАЛІ (15000 <= C2: C29; СТРОКА (C2: C29); ""); СТРОКА () - СТРОКА ($ C $ 1)) - СТРОКА ($ C $ 1))Зноў набіраем спалучэнне клавіш Ctrl + Shift + Enter.
Ва ўсіх трох выпадках змяняецца толькі першае значэнне каардынатаў, а ў астатнім формулы цалкам ідэнтычныя.
- Як бачым, табліца запоўненая дадзенымі, але знешні выгляд яе не зусім прывабны, да таго ж, значэння даты запоўненыя ў ёй некарэктна. Трэба выправіць гэтыя недахопы. Некарэктнасць даты звязана з тым, што фармат вочак адпаведнага слупка агульны, а нам трэба ўсталяваць фармат даты. Вылучаем ўвесь слупок, уключаючы вочкі з памылкамі, і клікаем па вылучэнню правай кнопкай мышы. У якое з'явілася спісе пераходзім па пункце "Фармат ячэйкі ...".
- У якое адкрылася акне фарматавання адкрываем ўкладку "Лічба". У блоку "Лікавыя фарматы" вылучаем значэнне "Дата". У правай частцы акна можна выбраць жаданы тып адлюстравання даты. Пасля таго, як налады выстаўлены, ціснем на кнопку "OK".
- Цяпер дата адлюстроўваецца карэктна. Але, як бачым, уся ніжняя частка табліцы запоўненая вочкамі, якія ўтрымліваюць памылковае значэнне "# ЛІК!". Па сутнасці, гэта тыя вочкі, дадзеных з выбаркі для якіх не хапіла. Больш прывабна было б, калі б яны адлюстроўваліся наогул пустымі. Для гэтых мэтаў скарыстаемся умоўнай фарматаваннем. Вылучаем усе вочкі табліцы, акрамя шапкі. Знаходзячыся ва ўкладцы "Галоўная" клікаем па кнопцы "Умоўнае фарматаванне", Якая знаходзіцца ў блоку інструментаў "Стылі". У якое з'явілася спісе выбіраем пункт "Стварыць правіла ...".
- У якое адкрылася акне выбіраем тып правілы "Фарматаваць толькі вочкі, якія ўтрымліваюць". У першым полі пад надпісам "Фарматаваць толькі вочкі, для якіх выконваецца наступнае ўмова" выбіраем пазіцыю "Памылкі". Далей ціснем па кнопцы "Фармат ...".
- У запусцім акне фарматавання пераходзім ва ўкладку "Шрыфт" і ў адпаведным полі выбіраем белы колер. Пасля гэтых дзеянняў пстрыкаем па кнопцы "OK".
- На кнопку з сапраўды такім жа назвай ціснем пасля вяртання ў акно стварэння умоў.
Цяпер у нас ёсць гатовая выбарка па паказаным абмежавання ў асобнай належным чынам аформленай табліцы.
Урок: Ўмоўнае фарматаванне ў Excel
Спосаб 3: выбарка па некалькіх умоў з дапамогай формулы
Гэтак жа, як і пры выкарыстанні фільтра, з дапамогай формулы можна ажыццяўляць выбарку па некалькіх умовам. Для прыкладу возьмем усю тую ж зыходную табліцу, а таксама пустую табліцу, дзе будуць выводзіцца вынікі, з ужо выкананым лічбавым і умоўнай фарматаваннем. Ўсталюем першым абмежаваннем ніжнюю мяжу адбору па выручцы ў 15000 рублёў, а другім умовай верхнюю мяжу ў 20000 рублёў.
- Ўпісваем ў асобным слупку межавыя ўмовы для выбаркі.
- Як і ў папярэднім спосабе, па чарзе вылучаем пустыя слупкі новай табліцы і ўпісваем ў іх адпаведныя тры формулы. У першы слупок ўносім наступнае выраз:
= Індэкс (A2: A29; найменшымі (КАЛІ (($ D $ 2 = C2: C29); СТРОКА (C2: C29); ""); СТРОКА (C2: C29) -СТРОКА ($ C $ 1)) - СТРОКА ($ C $ 1))У наступныя калонкі ўпісваем дакладна такія ж формулы, толькі змяніўшы каардынаты адразу пасля назвы аператара індэкс на адпаведныя патрэбным нам слупках, па аналогіі з папярэднім спосабам.
Кожны раз пасля ўводу, не забываемся набіраць спалучэнне клавіш Ctrl + Shift + Enter.
- Перавага дадзенага спосабу перад папярэднім заключаецца ў тым, што калі мы захочам памяняць мяжы выбаркі, то зусім не трэба будзе мяняць саму формулу масіва, што само па сабе даволі праблематычна. Дастаткова ў калонцы умоў на лісце памяняць межавыя колькасці на тыя, якія патрэбныя карыстачу. Вынікі адбору тут жа аўтаматычна зменяцца.
Спосаб 4: выпадковая выбарка
У Эксэля з дапамогай адмысловай формулы СЛЧИС можна таксама ўжываць выпадковы адбор. Яго патрабуецца вырабляць у некаторых выпадках пры працы з вялікім аб'ёмам дадзеных, калі трэба прадставіць агульную карціну без комплекснага аналізу ўсіх дадзеных масіва.
- Злева ад табліцы прапускаем адзін слупок. У вочку наступнага слупка, якая знаходзіцца насупраць першай ячэйкі з дадзенымі табліцы, ўпісваем формулу:
= СЛЧИС ()Гэтая функцыя выводзіць на экран выпадковы лік. Для таго, каб яе актываваць, ціснем на кнопку ENTER.
- Для таго, каб зрабіць цэлы слупок выпадковых лікаў, усталёўваем курсор у ніжні правы кут ячэйкі, якая ўжо ўтрымлівае формулу. З'яўляецца маркер запаўнення. Працягваем яго ўніз з заціснутай левай кнопкай мышы паралельна табліцы з дадзенымі да яе канца.
- Цяпер у нас маецца дыяпазон вочак, запоўнены выпадковымі лікамі. Але, ён утрымлівае ў сабе формулу СЛЧИС. Нам жа трэба працаваць з чыстымі значэннямі. Для гэтага варта выканаць капіраванне ў пусты слупок справа. Вылучаем дыяпазон вочак са выпадковымі лікамі. Размясціўшыся ва ўкладцы "Галоўная", Пстрыкаем па абразку "Капіяваць" на стужцы.
- Вылучаем пусты слупок і клікаем правай кнопкай мышы, выклікаючы кантэкстнае меню. У групе інструментаў "Параметры ўстаўкі" выбіраем пункт "Значэнні", Паказаны ў вобразе піктаграмы з лічбамі.
- Пасля гэтага, знаходзячыся ва ўкладцы "Галоўная", Клікаем па ўжо знаёмаму нам значку "Сартаванне і фільтр". У выпадальным спісе спыняем выбар на пункце "Наладжвальная сартаванне".
- Актывуецца акно налады сартавання. Абавязкова усталёўваем галачку насупраць параметру "Мае дадзеныя ўтрымліваюць загалоўкі", Калі шапка маецца, а галачкі няма. У полі "Сартаваць па" паказваем найменне таго слупка, у якім змяшчаюцца скапіраваныя значэння выпадковых лікаў. У полі "Сартаванне" пакідаем налады па змаўчанні. У полі "Парадак" можна выбраць параметр як "Па ўзрастанні", так і "Па змяншэнні". Для выпадковай выбаркі гэта значэння не мае. Пасля таго, як налады зроблены, ціснем на кнопку "OK".
- Пасля гэтага ўсе значэння табліцы выстройваюцца ў парадку ўзрастання або змяншэння выпадковых лікаў. Можна ўзяць любую колькасць першых радкоў з табліцы (5, 10, 12, 15 і да т.п.) і іх можна будзе лічыць вынікам выпадковай выбаркі.
Урок: Сартаванне і фільтраванне дадзеных у Excel
Як бачым, выбарку ў табліцы Excel можна вырабіць, як з дапамогай автофильтра, так і ужыўшы спецыяльныя формулы. У першым выпадку вынік будзе выводзіцца ў зыходную табліцу, а ў другім - у асобную вобласць. Маецца магчымасць вырабляць адбор, як па адным умове, так і па некалькіх. Акрамя таго, можна ажыццяўляць выпадковую выбарку, выкарыстаўшы функцыю СЛЧИС.
