Файл: Лабораторная работа ms excel Ввод и форматирование данных Цель работы отработка базовых навыков работы в ms.doc

---

Задание для самостоятельного выполнения

1. 
   С помощью АВТОФИЛЬТРА самостоятельно:
   

• 
  найдите планеты, имеющие диаметр менее 50 тыс. км и массу менее 4*10²⁴ кг;
  
• 
  н айдите планеты, находящиеся от Солнца на расстоянии не менее 500 млн. км, имеющих массу в диапазоне от 3*10²⁴ кг до 500*10²⁴ кг, а также не более 2 спутников.
  

2. 
   С помощью СОРТИРОВКИ:
   

• 
  Отсортируйте все планеты в порядке возрастания расстояния от Солнца.
  
• 
  Отсортируйте данные в таблице в алфавитном порядке названий планет.
  
• 
  Отсортируйте данные в порядке возрастания их расстояния от Солнца.
  

Расширенный фильтр

Задание 2. С использованием Расширенного фильтра осуществить поиск планет с периодом обращения более 10 земных лет и количеством спутников не менее 2.

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

Диапазон критериев должен соответствовать следующим требованиям:

• 
  Диапазон должен включать как минимум две строки. Причем в первой строке размещаются названия полей списка. Другая строка (или строки) содержит критерии отбора записей, опре­деленные пользователем. Во избежание ошибок названия полей списка следует скопировать, а не вводить вручную.
  
• 
  Значения условий фильтрации, размещенных в одной строке, объединены логическим оператором И.
  
• 
  Если на значения поля необходимо наложить несколько усло­вий отбора, соединенных оператором И, то в области названия поля необходимо повторить имя этого поля нужное количество раз.
  
• 
  Значения условий, заданных в разных строках, связаны опера­
  тором ИЛИ.
  
• 
  Пустая строка критерия означает, что условия отбора нет.
  
• 
  Диапазон условий может находиться в любом месте рабочего листа или даже на отдельном рабочем листе. Однако при этом диапазон условий должен быть отделен от исходного списка хотя бы одной пустой строкой или столбцом.
  
• 
  Диапазон условий может содержать не все поля списка, а только те, которые используются при создании условий фильтрации.
  

---

После того как создан диапазон условий, можно использовать средства расширенного фильтра. Для этого следует последовательно выполнить операции:

Установить курсор в любую ячейку списка → Команда Данные → фильтр → Расширенный фильтр

В итоге на экране появится одноименное диалоговое окно (рис. 1), в котором следует указать необходимые данные.

Рис. 1. Диалоговое окно Расширенный фильтр

Прежде всего, следует установить переключатель обработки на одно из возможных значений, определяющих, куда поместить результат: фильтровать список на месте (действует по умолчанию) или скопи­ровать результат в другое место.

Затем в текстовом поле Исходный диапазон следует задать адрес обра­батываемой базы данных (путем выделения всех ее ячеек, включая заго­ловки полей). Этот адрес, как правило, устанавливается по умолчанию, и пользователь может его подтвердить либо изменить по своему усмотре­нию.

В текстовом поле Диапазон условий следует задать соответствующие ссылки на ячейки, содержащие условия отбора записей (диапазон кри­териев).

Если был выбран переключатель Скопировать результат в другое место, то активизируется текстовое поле Поместить результат в диапазон, в котором необходимо ввести адрес левой верхней ячейки диа­пазона результата фильтрации.

После заполнения предложенной формы ввод параметров фильтрации подтверждается кнопкой ОК. Excel отфильтрует список в соответствии с заданными условиями, не отображая записи, не удовлетворяющие кри­териям фильтра.

Примечания

1. 
   При использовании расширенного фильтра целесооб­разнее помещать диапазоны условий выше и правее исходной базы данных, если она содержит не более 1020 столбцов и не более 200250 записей. В про­тивном случае диапазон условий целесообразнее раз­мещать на новом листе.
   
2. 
   Результат выборки расширенного фильтра предпочти­тельнее помещать в новое место под диапазоном усло­вий. Если выборка размещается на том же листе, что и исходная база данных, то для облегчения сравнитель­ного анализа их следует располагать на одном уровне по горизонтали.
   
3. 
   После изменения значений диапазона условий резуль­тат выборки расширенного фильтра автоматически не изменяется. Требуется его повторное применение.
   

---

Рис. 2.

Имена полей во всех интервалах должны точно совпадать.

Для выполнения действий по фильтрации необходимо воспользоваться командами меню: ДАННЫЕ, ФИЛЬТР, РАСШИРЕННЫЙ ФИЛЬТР. В диалоговом окне необходимо указать координаты интервалов.

При использовании РАСШИРЕННОГО ФИЛЬТРА необходимо сначала определить (создать) три области (рис.2):

- исходный диапазон - это область базы данных (А2:F12);

- диапазон условий отбора (или интервал критериев) - это область, где задаются критерии фильтрации (А14:F15);

- диапазон, в который при желании пользователя Excel помещает результат выборки (интервал извлечения) - это та область, в которой будут появляться результаты фильтрации (А17:F21).

Если необходимо получать результаты фильтрации в интервале извлечения, нужно поставить [*] - СКОПИРОВАТЬ РЕЗУЛЬТАТ  В   ДРУГОЕ МЕСТО (рис. 3).

1. Создайте диапазон условий отбора в ячейках A14:F15 (см. рис. 2).

2. Запишите условия отбора в диапазон условий отбора (см. рис. 2).

3. Создайте интервал извлечения, куда будут помещены результаты фильтрации в ячейки A17:F17

4. Поместите курсор в область базы данных (A2:F12)

5. Выполните команды: ДАННЫЕ, ФИЛЬТР, РАСШИРЕННЫЙ ФИЛЬТР.

6. Проверьте правильность задания интервалов (см.рис. 3). Нажмите кнопку <ОК>.

Проверьте! Найдены планеты Юпитер, Сатурн, Уран, Нептун.

Рис. 3

7. 
   Сохраните результаты в файле Планеты солнечной системы.xls
   

Задание для самостоятельного выполнения

С использованием Расширенного фильтра самостоятельно:

1) найдите  записи о планетах, имеющих период обращения более 2 земных лет и экваториальный диаметр менее 50 тыс. км

2) осуществите поиск планет, находящихся от Солнца на расстоянии

---

более 1000 млн. км и имеющих 1 спутник

3) найдите записи о планетах Меркурий, Сатурн или Плутон

4) найдите записи о планете Меркурий или планетах, имеющие   более одного спутника

5)  найдите записи о планетах, имеющие период обращения более 1 года и массу менее 100 * 10²⁴ кг

6) найдите записи о планетах, которые расположены от Солнца на расстоянии более 1000 млн. км  с диаметром более 50 тыс. км.   или находящихся от Солнца на расстоянии менее 150 млн. км и имеющие диаметр более 10 тыс. км.

7) найдите записи о планетах у которых период обращения менее 10 лет и количество спутников не равно 0 или записи о планетах, у которых диаметр менее 10 тыс. км.

Задание 3. Применение вычисляемый критерий, применение критериев расширенного фильтра рассмотрим на базе данных магазина «Ирикон» (рис. 1), содержа­щей информацию о поступивших на ее склад.

Следует отметить, что в диапазоне критериев могут использоваться вычисляемые критерии, которые представляют собой условия, вклю­чающие более сложные операции, чем простое сравнение значения столбца с константой.

Применяя вычисляемый критерий, необходимо соблюдать следующие правила:

• 
  Заголовок над вычисляемым критерием не должен совпадать с заголовком какого-либо поля списка. Он может быть пустой ячейкой или другим текстовым полем, отличным от названия полей анализируемого списка.
  
• 
  Ссылки на ячейки внутри списка должны быть относительны­ми, а ссылки на ячейки вне списка  абсолютными.
  
• 
  В формуле, вычисляющей условие, следует использовать ссылку на целый столбец списка.
  
• 
  Количество условий, используемых для фильтрации списка, не ограничено и может применяться в сочетании с любыми дру­гими невычисляемыми условиями.
  
• 
  Вычисляющая формула представляет собой логическую фор­мулу, которая возвращает значение Истина или Ложь.
  

---