При выполнении последующих заданий размещайте диапазоны условий справа от списка.
Работа 4. Обработка списков.
1. Формирование списка (базы данных)
Задание 1 .
§ откройте новую рабочую книгу,
| B | C | D | E | F | G | |
| 2 | Номер | Дата | Наименование | Цена | Объем | Сумма |
| 3 | 1 | 25.03.07 | СистБлок-4 | 9 170 | 4 | 36 680 |
| 4 | 2 | 15.02.07 | СистБлок -3 | 8 570 | 3 | 25 710 |
| 5 | 3 | 23.05.07 | Принтер-HP | 4 242 | 2 | 8 484 |
| 6 | 4 | 05.11.07 | Привод CD-R | 540 | 5 | 2 700 |
| 7 | 5 | 05.11.07 | FDD-3.5 | 170 | 5 | 850 |
| 8 | 6 | 03.04.07 | Принтер-Xerox | 2596 | 1 | 2 596 |
| 9 | 7 | 23.06.07 | Монитор | 7 976 | 3 | 23 928 |
| 10 | 8 | 12.07.07 | Привод CD-RW | 730 | 3 | 2190 |
| 11 | 9 | 02.09.07 | Привод DVD | 942 | 2 | 1884 |
| 12 | 10 | 05.11.07 | HDD-250 | 1550 | 2 | 3 100 |
| Рис. 1 |
§ задайте имя первого листа: Исходная таблица,
§ создайте список, приведенный рис. 1.
2. Сортировка списка.
Задание 2. Упорядочение сформированного списка по значениям столбца Дата.
§ скопируйте таблицу на новый лист (Сортировка),
§ установите маркер в список и выберите в меню: Данные Сортировка. . .,
§ задайте имя столбца (Дата), по значениям которого выполняется упорядочение строк,
§ задайте принцип сортировки (например, по возрастанию).
Строки списка, у которых значение поля Дата одинаковы, не могут быть упорядочены. Если и их требуется отсортировать, можно задать дополнительный показатель, который повлияет только на их взаимное расположение.
|
|
|
Задание 3. Добавить второй критерий (Цена) и выполнить сортировку по убыванию.
§ установите маркер в список,
§ выберите в меню элемент Данные Сортировка . . .,
§ добавьте в поле Затем критерий Цена и выполните сортировку по убыванию,
Записи с одинаковой датой переупорядочиваются в соответствии с убыванием цены.
3. Поиск записей с помощью Автофильтра.
Поиск по заданному значению
Пример. Найти в списке записи по условию: Наименование = Монитор.
§ скопируйте исходный список на новый лист (Задание 4) и установите маркер в список,
§ выберите в меню элемент Данные Фильтр Автофильтр,
§ раскройте список в столбце Наименование, выберите значение: Монитор и нажмите Ок.
Видимыми в списке останутся только записи с заданным Наименованием.
§ отмените фильтрацию, используя меню Данные: снимите флажок Автофильтр и выполните Задание 4
Задание 4 . Найти в списке записи по условию: Номер = 4.
Поиск по диапазону значений
Пример. Найти в списке записи по критерию: 3 <= Номер <= 6 .
§ скопируйте исходную таблицу на новый лист (Задание 5) и установите маркер в список,
|
|
|
§ выберите в меню элемент Автофильтр,
§ раскройте список в столбце Номер, выберите элемент Условие,
§ введите в поле Показать первое условие из критерия: больше или равно 3,
§ установите переключатель: ¤ И ,
§ введите в поле Показать второе условие из критерия: меньше или равно 6,
§ нажмите Ок.
Видимыми в списке останутся только запись с заданными Номерами.
§ отмените фильтрацию и выполните Задание 5.
Задание 5. Найти записи по критерию: 1.05.07 <= Дата <= 31.07.07.
Поиск по части символьного значения
Если требуется выполнить фильтрацию по части наименования, можно использовать шаблон подстановки:
* - означающий произвольный набор допустимых символов. Например, при фильтрации по сочетанию дис* будут найдены наименования: дисплей, диск, диспетчер и т.д., то есть слова, начинающиеся сочетанием символов дис,
? - означающий произвольный одиночный символ. Например, при фильтрации по сочетанию диск?будут найдены наименования: диск1, дискА, диск5 и т.д., то есть слова, начинающиеся сочетанием символов диски имеющие произвольный последний символ.
Пример. Найти в списке записи по критерию:
Наименование начинается сочетанием символов СистБлок и Цена >= 9000.
|
|
|
§ скопируйте исходную таблицу на новый лист (Задание6) и установите маркер в список,
§ выберите в меню элемент Автофильтр, раскройте список в столбце Наименование, выберите элемент Условие и введите условие:
равно СистБлок*
§ нажмите Ок, раскройте список в столбце Цена и введите условие по цене:
больше или равно 9000
§ нажмите Ок.
§ отмените фильтрацию и выполните Задание 6.
Задание 6. Найти в списке и отобразить записи по критерию:
Сумма <= 5 000 и Наименование начинается сочетанием символов Принтер
Поиск по критерию, включающему диапазоны значений по двум условиям
Задание 7. Найти в списке и отобразить записи по критерию::
20 000 <= Сумма <= 30000.
Дата: второй квартал 2007 года,
§ скопируйте исходную таблицу на новый лист (Задание7) и установите маркер в список,
§ выберите в меню элемент Автофильтр, раскройте список в столбце Сумма, выберите элемент Условие и введите условия:
больше или равно 20 000
и
меньше или равно 30 000
|
|
|
§ нажмите Ок, раскройте список в столбце Дата и аналогично введите условие по дате,
§ нажмите Ок.
4. Расширенный фильтр
Расширенный фильтр позволяет использовать более сложные критерии отбора и поместить результат фильтрации в отдельной таблице, оставляя исходный список неизменным. При использовании Расширенного фильтра составляется Диапазон условий, представляющий собой отдельную таблицу, в которой формируются условия фильтрации.
Примеры
Пример 1 Найти записи о реализации товаров по критерию
Наименованием = Привод CD-R или FDD-3.5 или HDD-250.
§ скопируйте исходную таблицу на новый лист (Примеры-РасшФильтр), сохраняя указанные на рис. 1 адреса,
В данной задаче критерий включает условия поиска только по столбцу Наименование, причем отдельные условия объединяются логической операцией ИЛИ. Следовательно, Диапазон условийимеет вид таблицы, содержащей один столбец и три строки (по числу условий, объединенных операцией ИЛИ), рис. 2:
| I | ||
| 2 | Наименование | |
| 3 | Привод CD-R | |
| 4 | FDD-3.5 | |
| 5 | HDD-250 | Рис. 2 |
· справа от списка сформировать указанный Диапазон условий (например, блок I2:I5),
· установить курсор в список,
· выбрать меню Данные Фильтр Расширенный фильтр, в окне фильтра задать блоки ячеек, содержащие
- исходный диапазон, блок B2:G12 (обычно система сама правильно определяет список),
- диапазон условий, блок I2:I5 (вспомогательная таблица, сформированная справа от списка),
- включить переключатель Скопировать результат в другое место и задать блок для размещения найденных записей (ниже исходного списка, например, начиная с ячейки B14), Ок.
Пример 2 Найти записи о продаже товаров по критерию
3 <= Номер <= 7 и Объем > 3 .
В данной задаче условия поиска относятся к столбцам Номер и Объем, причем для реализации двойного неравенства по Номеру этот столбец включается в диапазон условий дважды. Так как все элементарные условия объединены операцией И, они должны записываться в одной строке. Тогда Диапазон условийпредставляет собой таблицу, состоящую из трех столбцов, рис. 3:
| I | J | K | ||
| 7 | Номер | Номер | Объем | |
| 8 | >=3 | <=7 | >3 | Рис. 3 |
§ в окне фильтра задать параметры и выполнить фильтрацию, разместив Диапазон условий справа от списка (блок I7 : K8), а результат ниже предыдущего результата (например, начиная с ячейки B20).
Пример 3 Найти записи о продаже товаров по критерию
Объем > 3 или Сумма > 20 000 .
В данной задаче условия поиска относятся к столбцам Объем и Сумма. Так как элементарные условия, входящие в критерий, объединены операцией, они должны записываться в разных строках Диапазона условий, который будет иметь вид таблицы, рис. 4.
| I | J | ||
| 10 | Объем | Сумма | |
| 11 | >3 | ||
| 12 | >20 000 | Рис. 4 |
§ в окне фильтра задать параметры и выполнить фильтрацию.
Пример 4 Найти записи о продаже товаров по критерию
Сумма > среднего значения Суммы по всему списку.
Критерий включает одно условие по столбцу Сумма. Сформируем диапазон условий (блок J14 : J15):
§ в ячейку J15 введем формулу: =G3>СРЗНАЧ($G$3:$G$12),
где:
G3 – адрес первого значения столбца Сумма, по которому задан критерий фильтрации,
$G$3:$G$12 - блок, по которому вычисляется значение функции СРЗНАЧ (задается всегда абсолютными адресами),
| J | |
| 14 | |
| 15 | ИСТИНА |
| Рис. 5 |
§ в отличие от предыдущих вариантов формирования диапазона условий, здесь не указывается имя столбца, по которому задано условие (в этом примере имя Сумма),
§ в окне Расширенного фильтра задать параметры и выполнить фильтрацию.
Самостоятельные задания
§ скопируйте исходную таблицу на новый лист (Задания-РасшФильтр).
Задание 8. Найти записи о продаже товаров по критерию
Дата: май-июль 2007 г и Сумма > 8 000.
Учесть, что элементарные условия, объединенные операцией "И" записываются в одной строке диапазона условий, а двойное неравенство разделяется по двум столбцам с одинаковыми наименованиями.
Задание 9. Найти записи о продаже товаров по критерию
Наименование начинается символами Привод или 2000 < Сумма <3000
Здесь при формировании поискового условия можно использовать символы шаблона, где
* означает произвольный набор символов, ? означает произвольный одиночный символ.
Задание 10. Найти записи о продаже товаров по критерию:
Объем < среднего значения Объем по всему списку.
5. Функции баз данных
Функции баз данных позволяют производить обработку значений заданного столбца (поля) списка для строк (записей), удовлетворяющих заданному условию. Например, для исходной таблицы (рис.1) можно потребовать найти общий сумму продаж по столбцу Сумма, нотолько для записей, соответствующих ноябрю 2007 года (критерий отбора строк, которые подвергаются суммированию).
Каждая из функций баз данных имеет собственное имя и использует три аргумента
Имя_функции (База данных; Поле; Условие).
База данных - это диапазон ячеек, содержащий исходный список, включая имена столбцов.
Поле - столбец, значения которого используются функцией для обработки данных. Аргумент «поле» может быть задан названием столбца в двойных кавычках, например "Цена", "Сумма", или как номер столбца в списке: 1 - для первого столбца, 2 - для второго и так далее.
| B | C | D | E | F | G | |
| 13 | Количество продаж с 1.11.07 | |||||
| 14 | Средняя цена Приводов различных типов | |||||
| 15 | На какую сумму проданы мониторы 23.06.07 | |||||
| 16 | Максимальная суммапродажи во II полугодии 2007 г. | |||||
| Рис. 6 | ||||||
Условие (критерий) - это ссылка на интервал ячеек, задающих условия отбора строк, подлежащих обработке. Диапазон критериев формируется так же как в Расширенном фильтре.
Задание 1д.
· скопируйте список на новый лист рабочей книги (Функции БД) сохраняя указанные на рис. 1 адреса,
· ниже списка сформируйте заготовку для результатов последующих примеров и заданий (рис. 6)
Пример. В списке определить общее количество продаж товаров, начиная с 1 ноября 1993 года.
· справа от списка создайте диапазон условий:
| I | |
| 2 | Дата |
| 3 | > =1.11.93 |
· в ячейку G13 введите формулу, подсчитывающую количество записей, удовлетворяющую сформированному диапазону условий
БСЧЁТ(В2:G12; ; I2:I3)
Замечание: параметр "Поле" в данной функции не является обязательным.
При выполнении последующих заданий размещайте диапазоны условий справа от списка.
Задание 2д. Рассчитать среднюю цену (функция ДСРЗНАЧ) проданных Приводов различных типов (любых моделей).
Замечание: использовать шаблоны подстановки
Задание 3д. Извлечь (функция БИЗВЛЕЧЬ) из списка значение Суммы продажи мониторов 23.06.07 г.
Задание 4д. Рассчитать максимальную (функция ДМАКС) сумму продаж во втором полугодии 2007 г.
Замечание: так как условие по дате представляет собой двойное неравенство, в диапазоне условий формируются два столбца с именем Дата, в которые вводятся простые неравенства.
6. Задания для самостоятельной работы (выполняются по указанию преподавателя)
6.1 В списке (рис.1) найти записи по критерию: 3 <= Номер <= 5 или 8 000 <= Цена <=10 000
6.2 В списке (рис.1) найти записи по критерию: Наименование = Привод или 2 < Номер < 5
6.3 В списке (рис.1) найти записи по критерию: Дата: 3 квартал 2007 г. или Сумма >=2 000
6.4 В списке (рис.1) найти записи по критерию: Наименование = Привод или Наименование = СистБлок
6.5 В списке (рис.1) найти записи по критерию:
Цена > среднего значения Цена по всему списку на 30 %
7. Вопросы для самопроверки
Дата добавления: 2018-05-02; просмотров: 316; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!