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

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

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


 

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

44782. Обучающие работы по созданию и ведению баз данных 2.61 MB
  Система управления базами данных предоставляет значительные возможности по работе с хранящимися данными, их обработке и совместному использованию. Можно выбирать любые поля, форматы полей, сортировать данные, вычислять итоговые значения. Можно отбирать интересующие данные по какому-либо признаку, менять их, удалять, копировать в другие таблицы
44783. Создание базы данных, состоящей из двух таблиц 6 MB
  Воспользуемся новым способом изготовления таблиц. Таблицы будем создавать в режиме Таблицы. В таблице Список будет 7 полей (код, фамилия, имя, отчество, год рождения, курс, название группы в колледже, номер группы в компьютерной школе). Номера групп и фамилии преподавателей школы будут храниться в отдельной таблице Группы в виде двух столбцов
44784. Создание базы данных, состоящей из трех таблиц 2.07 MB
  В данном случае таблицы Группы и Список объединены связью «один-ко-многим», таблицы Список и Личные данные — связью «один-к-одному». Таблицы Группы и Личные данные прямо не связаны
44786. Эрозия и деградация земель. Техногенные пустоши. Рекультивация и мелиорация земель 18.13 KB
  Рекультивация и мелиорация земель Деградация пастбищных земель: включает в себя множество различных проявлений от низкой продуктивности кормов до ухудшения растительного состава разрушения растительного покрова транспортными средствами. Рекультивация комплекс работ по экологическому и экономическому восстановлению земель и водоёмов плодородие которых в результате человеческой деятельности существенно снизилось. Целью проведения рекультивации является улучшение условий окружающей среды восстановление продуктивности нарушенных земель и...
44787. Команды для работы с файлами и каталогами 24.62 KB
  После имени команды надо ввести пробел и имя пользователя например jim: [root] userdd jim После этого система будет знать о существовании пользователя jim говорят будет открыт счет для пользователя jim . После того как вы завершите ввод нажатием клавиши Enter система попросит ввести его повторно: Retype new UNIX pssword: Если вы не ошиблись при вводе пароль приходится вводить вслепую поскольку он не отображается на экране появится сообщение: psswd: ll uthentiction tokens updted successfully [root] mn psswd В ответ вы получите...
44788. Русский язык как предмет изучения и обучения. Место РЯ среди других учебных дисциплин 14.62 KB
  Основу русского языка как школьного учебного предмета составляет наука о русском языке. В разные периоды развития отечественной школы состав учебного предмета Русский язык менялся в зависимости от целей изучения русского языка от уровня развития науки о русском языке и наук психологопедагогического цикла. Изучение языка усвоение сведений добытых учёнымилингвистами в области фонетики лексики словообразования грамматики стилистики. Обучение речи развитие навыков употребления языка для общения мышления.
44790. Предпринимательство, формы и методы организации предпринимательства 14.59 KB
  По характеру деятельности предпринимательство включает несколько форм: коммерческое предпринимательство. Предприниматель выступает в роли коммерсанта торговца который продает готовые товары купленные им у других лиц потребителю покупателю; финансовое предпринимательство. Это форма коммерческого предпринимательства в котором в качестве предмета куплипродажи выступают деньги или ценные бумаги; некоммерческое предпринимательство.