69236

Робота з базами даних (списками) в Excel. Використання розширеного фільтра

Лекция

Информатика, кибернетика и программирование

Расширенный фильтр має додаткові можливості порівняно з Автофильтр а саме: задавати для кількох стовпців умови з’єднані логічним оператором ИЛИ.; задавати три або більше умов для конкретного стовпця з використанням хоча б одного логічного оператора ИЛИ.

Украинкский

2014-10-02

62.99 KB

4 чел.

Робота з базами даних (списками) в  Excel. Використання розширеного фільтра.

Розширений фільтр

 Расширенный фильтр має додаткові можливості порівняно з  Автофильтр, а саме:

задавати для кількох стовпців умови, з’єднані логічним оператором ИЛИ. Наприклад, із загального списку співробітників можна вибрати осіб віком старше 50 років або таких, річна заробітна плата яких перевищує 10000 грн.;

задавати три або більше умов для конкретного стовпця з використанням хоча б одного логічного оператора ИЛИ. Наприклад, можна вивести на екран список співробітників, чиї прізвища починаються з «А», «Г» чи «Н»;

задавати обчислювані умови. Наприклад, можна вивести на екран списки тільки тих співробітників, заробітна плата яких на 25 % перевищує середню.

Крім того,  Расширенный фильтр використовують для відбору рядків зі списку за деякою умовою та копіювання відібраних рядків в іншу частину робочого аркуша.

 Расширенный фильтр вимагає визначення умов відбору рядків в окремому діапазоні комірок робочого аркуша. Діапазон умов рекомендується задавати в порожніх рядках над списком.

Для використання розширеного фільтра до створеного засобами Excel списку необхідно виконати такі дії.

Вставити кілька порожніх рядків у верхній частині робочого аркуша над списком (щоб вивільнити місце для діапазону умов).

Задати діапазон умов відбору рядків, який повинен містити щонайменше два рядки. У верхній рядок вводять один або кілька заголовків стовпців, у другому і наступних задають умови відбору.

Між значенням умов і списком повинен бути щонайменше один порожній рядок. За винятком обчислюваних умов, заголовки в діапазоні умов повинні точно співпадати із заголовками стовпців у списку. У діапазоні умов можна ввести будь-яку кількість умов. Програма Excel сприймає їх за таким правилом:

  1.  умови одного рядка вважаються з’єднаними логічним оператором И (тобто всі умови повинні виконуватись одночасно);
  2.  умови у двох рядках вважаються з’єднаними логічним оператором ИЛИ (тобто достатньо виконання хоча б однієї умови).

Для того щоб знайти дані, які відповідають одній умові в одному стовпці або іншій умові в іншому стовпці, слід ввести умови відбору в різні рядки діапазону умов відбору. Наприклад, наведений далі діапазон умов відображає всі рядки, що містять значення «Артикул 1» у стовпці «Товар» або «Іванов» у стовпці «Менеджер», або обсяг продажів на суму понад 1000 грн. у стовпці «Обсяг продажів».

За наявності для одного стовпця двох і більше умов відбору треба ввести їх безпосередньо одна під одною в суміжні рядки. Наприклад, наведений діапазон умов відображає рядки, що містять у стовпці «Менеджер» список співробітників, чиї прізвища починаються з «А», «Г» та «Н».

Для того щоб знайти дані, які відповідають одному з кількох наборів умов, кожний з яких містить умови більше як для одного стовпця, необхідно ввести ці умови відбору в окремі рядки. Наприклад, наведений діапазон умов відображає рядки, що містять у стовпці «Менеджер» співробітників, прізвища яких починаються з «І» і які мають обсяг продажів на суму понад 5000 грн., або прізвища яких починаються з «Г», а обсяг продажів на суму менше 6000 грн.

Для того щоб показати рядки, в яких на елементи одного стовпця одночасно накладається кілька умов, необхідно включити в діапазон умов кілька стовпців з однаковими заголовками. Наприклад, наведений діапазон умов відображає рядки, які містять у стовпці «Обсяг продажів» обсяг продажів від 5000 до 8000 грн.

Задаючи текстові умови, слід дотримуватися таких правил:

  1.  одна літера означає, що пошуку підлягають усі значення, що починаються із зазначеної літери;
  2.  символ «>« або «<« означає, що пошуку підлягають значення, що за абеткою стоять після введеного текстового значення або перед ним;
  3.  формула -текст означає, що пошуку підлягають значення, які точно співпадають з введеним рядком символів текст.

Після введення діапазону умов відбору слід виконати команду Данные / Фильтр и сортировка/Дополнительно.

У вікні цієї команди треба ввести в поле Исходный Диапазон діапазон комірок таблиці, до якого буде застосовано розширений фільтр, а в поле Диапазон условий – діапазон комірок робочого аркуша, де задаються умови відбору рядків, що містять заголовки стовпців.

Перемикач Обработка слід встановити в положення Фильтровать список на месте і натиснути кнопку ОК.

У результаті виконання команди Расширенный фильтр приховуються всі рядки, що не задовольняють введені умови. Номери відібраних рядків виділяються синім кольором.

Умовою відбору може бути обчислене значення, що є результатом виконання певної формули. При використанні обчислюваної умови слід дотримуватися таких правил:

  1.  заголовок над обчислюваною умовою повинен відрізнятися від будь-якого іншого заголовка стовпця у списку. Заголовок умови може бути порожній або містити довільний текст;
  2.  посилання на комірки, що перебувають за межами списку, повинні бути абсолютними;
  3.  посилання на комірки списку повинні бути відносними (за одним винятком, який розглянемо далі). Наприклад, для відображення рядків, що містять список працівників, у яких обсяг продажів перевищує середній, необхідно:
  4.  у комірку, що перебуває за межами списку (наприклад, D2), ввести формулу =CP3HA4(F8:F30), за допомогою якої обчислюється середнє значення за стовпцем «Обсяг продажів» з діапазону F8:F30;
  5.  вважаючи, що діапазоном умов є А1:А2, ввести в комірку А2 обчислювану умову =F8>$D$2;
  6.  формула умови порівнює комірку F8 з коміркою D2. Комірка F8 є першою у стовпці «Обсяг продажів» невідфільтрованого списку і згідно з формулою обчислюваної умови порівнюється з коміркою D2. У процесі фільтрації по черзі підставляється кожний елемент списку замість цього першого значення, тобто D2 порівнюється з F8, потім з F9 і так до кінця списку;
  7.  посилання на комірку D2 повинно бути абсолютним. Якщо б у комірку А2 діапазону умов було введено формулу =F8>D2, то спочатку F8 порівнювалося б з D2, потім F9 з D3 і т. д. І всі ці порівняння, за винятком першого, були б неправильними;
  8.  значення «ЛОЖЬ», що повертає формула умови, саме по собі не потрібне. У розглядуваному прикладі воно вказує лише на те, що перший працівник у списку має обсяг продажів менший від середнього.

При визначенні обчислюваної умови можна ввести формулу =F8> >CP3HAЧ($F$8:$F$30), яка прямо посилається на стовпець «Обсяг продажів», а не на комірку, що перебуває за межами списку. Згідно з наведеними правилами посилання на комірки у списку повинні бути відносними, проте формула містить абсолютні адреси діапазону комірок від F8 до F30. Це зроблено для того, щоб на кожному кроці фільтрації використовувався один і той самий діапазон комірок. Іншими словами, треба порівнюватиТ8 із середнім для F8:F30, потім F9 із середнім для F8:F30 і т. д. Якщо використати відносне посилання на F8:F30, то лише перше порівняння буде правильним.

Копіювання відфільтрованих рядків в іншу частину робочого аркуша. За допомогою вікна діалогу  Расширенный фильтр можна встановити режим копіювання відібраних рядків в іншу частину аркуша замість виведення відфільтрованого списку. Щоб скопіювати рядки, треба перемикач Обработка встановити в положення Скопировать результат в другое место, а в полі Поместить результат в Диапазон зазначити адресу діапазону, куди потрібно скопіювати інформацію.

Режим Только уникальные записи у вікні  Расширенный фильтр додає додатковий фільтр до заданих умов відбору. Він приховує рядки, що повторюються.


 

А также другие работы, которые могут Вас заинтересовать

37799. Побудова корпоративної комп’ютерної мережі з доступом до ресурсів Internet 57 KB
  Мета роботи: Вивчити основні принципи побудови корпоративних комп’ютерних мереж на основі комутаторів Fst Ethernet маршрутизуючого комутатора 3го рівня Fst Ethernet програмного маршрутизатора на базі ПК з операційною системою FreeBSD 8.1 принципи організації доступу корпоративної комп’ютерної мережі до ресурсів Internet через апаратний маршрутизатор Fst Ethernet отримати практичні навики по налаштуванню та діагностуванню роботи корпоративної комп’ютерної мережі створенню та використанню спільних ресурсів. Завдання: Дослідити...
37800. Робота з базами даних в мережі 88.5 KB
  Робота з базами даних в мережі. Вивчення архітектури мережевих баз даних. Архітектура серверних баз даних. Оскільки настільні СУБД такі як dBse Prdox FoxPro ccess не містять спеціальних додатків і сервісів для роботи в мережі щоб керувати даними а використовують для цієї цілі файлові сервіси операційної системи вся реальна обробка даних в таких СУБД здійснюється клієнтськими додатками і будьякі бібліотеки доступу до даних в цьому випадку також знаходяться в адресному просторі клієнтського додатку.
37801. Амплитудные детекторы радиосигналов 374 KB
  Приводятся теоретические сведения о принципах детектирования амплитудно модулированных сигналов процессах происходящих при детектировании АМ сигналов основные соотношения и рекомендации по выбору параметров элементов детекторов. В работе изучается влияние элементов принципиальных схем детекторов на характеристики детектирования и на выходные сигналы.1 Определение детектора и процесса детектирования. Процесс детектирования радиосигналов определяется как обратный процессу получения модулированных колебаний радиосигналов.
37802. ЗНЯТТЯ РЕГУЛЯТОРНОЇ І ШВІДКИСНОЇ ХАРАКТЕРИСТИКИ КАРБЮРАТОРНОГО ДВИГУНА 71 KB
  На підставі цього аналізу оцінити економічність ефективність режимів роботи двигуна і динамічні якості. При роботі двигуна з відкритою дросельною заслінкою в дифузорі створюється розрідження і паливо з розпилювача поступає в дифузор розпилюється там і перемішується з повітрям. Регулювальні характеристики Регулювальні характеристики є залежностями основних показників двигуна від значення одного або декількох з регулювальних параметрів при постійній частоті обертання...
37803. Ознайомлення з особливостями застосування на мові Асемблера системи команд керування програмою та процесором, вивчення команд умовного розгалуження 81 KB
  Вивчити основні команди керування програмою та процесором, отримати навички та вміння щодо застосування команд умовного розгалуження.
37804. Робота з дисками папками та файлами в середовищі Windows 669.5 KB
  Вивчив призначення програм основного меню при натисканні на клавішу пуск. запустив програму блокнот зафіксував у звіт призначення меню файли. За допомогою правої правої клавіші миші обо через меню Программы війти в програму проводник.
37805. Програмування лінійних та розгалужених алгоритмів 62.52 KB
  Ознайомитися з операторами вводу, виводу і присвоєння, навчитися записувати лінійні алгоритми на мові програмування Pascal; закріпити теоретичні відомості про розгалужені алгоритми, оператори передачі управління, навчитися програмувати розгалуження.
37806. Види модуляцій в сучасних інформаційних системах 8.77 MB
  Мета роботи: Дослідження і вивчення особливості видів модуляції які застосовуються в цифровій техніці ознайомитися з елементами модуляторів і демодуляторів а також із принципами їх роботи. Порядок виконання роботи Ознайомитися з принципами математичного моделювання модуляції які використовуються лабораторною програмою.
37807. Вказівники 2.14 MB
  Мета: навчитися програмувати з використанням вказівників та динамічних змінних, створювати та опрацьовувати черги та стеки.