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, то лише перше порівняння буде правильним.

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

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


 

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

48503. УЧЕБНАЯ РАДИО- ЭЛЕКТРОМОНТАЖНАЯ ПРАКТИКА 13.68 MB
  В процессе подготовки будущие специалисты должны получить определённую квалификацию, практические навыки. Особенно важно иметь хорошую практическую подготовку для специалиста, который, овладев теоретическими знаниями, должен уметь выполнить ту или иную конкретную работу.
48504. Методика профессионального обучения как наука и учебная дисциплина 416.77 KB
  Методика профессионального обучения как наука и учебная дисциплина. обучения как научной области педагогических знаний. обучения и методической терминологии. обучения с другими науками и перспективы ее развития.
48505. МУНИЦИПАЛЬНОЕ ПРАВО РОССИИ 63.5 KB
  Понятие и признаки местного самоуправления. Аспекты анализа местного самоуправления: основа конституционного строя форма народовластия вид деятельности граждан право человека и гражданина право населения как территориального коллектива. Вопросы местного значения и государственные задачи. Полномочия органов местного самоуправления и государственные полномочия.
48506. Загальні питання методики навчання розв’язування задач 32.12 KB
  Роль і місце задач у початковому курсі математики. Функції текстових задач. Складові процесу навчання розв’язуванню задач одного виду. Диференційований підхід у навчанні. Творча робота над розв’язаною задачею
48507. ПРАВО СОЦИАЛЬНОГО ОБЕСПЕЧЕНИЯ 47.5 KB
  Понятие и организационноправовые формы социального обеспечения Возникновение государственной системы социального обеспечения в России в период развития капитализма до 1917 года. Отличительные признаки советской системы социального обеспечения. Изменение подходов к организации социального обеспечения в социальноэкономических условиях 90х годов.
48508. РИМСКОЕ ПРАВО 96.5 KB
  Понятие римского частного права. Отличие частного права от публичного. Исторические значение римского права. Значение римского права для современной юриспруденции.
48509. РОССИЙСКОЕ ПРЕДПРИНИМАТЕЛЬСКОЕ ПРАВО 1.1 MB
  Предпринимательские отношения то есть отношения возникающие в процессе осуществления предпринимательской деятельности. Легальное определение предпринимательской деятельности дано в п. В частности такие отношения складываются при осуществлении деятельности организационноимущественного характера например по созданию и прекращению коммерческих организаций деятельности ряда некоммерческих организаций учреждений объединений и др. Такая хозяйственная деятельность носит некоммерческий характер но создает основу а зачастую является...
48510. Русский язык и культура речи. Три аспекта культуры речи (нормативный, коммуникативный, этикетный) 1.74 MB
  Предмет и задачи курса Русский язык и культура речи. Три аспекта культуры речи нормативный коммуникативный этикетный Значимость речевой культуры для духовной жизни общества. Курс Русский язык и культура речи. Культура речи риторика и стилистика.
48511. Системное программное обеспечение 1.5 MB
  Расположение СПО в общей структуре ЭВМ Современная компьютерная система состоит из одного или нескольких процессоров оперативной памяти дисков клавиатуры монитора принтеров сетевого интерфейса и других устройств то есть является сложной комплексной системой. Обычно на этом уровне находятся внутренние регистры центрального процессора CPU Centrl Processing Unit и арифметико-логическое устройство. На каждом такте процессора из регистра выбирается один или два операнда которые обрабатываются в арифметико-логическом устройстве...