- •1Списки данных в excel
- •2Фильтрация данных в excel
- •2.1Типы критериев
- •2.1.1Критерии на основе сравнения
- •2.1.2Критерии в виде образца-шаблона
- •2.1.3Множественные критерии на основе логических операций
- •2.1.4Вычисляемые критерии на основе логических формул
- •3Средства фильтрации
- •3.1Автофильтр
- •3.2Расширенный фильтр
- •4Встроенные функции базы данных
- •5Вопросы к защите лабораторной работы
- •6Задания для самостоятельной работы
4Встроенные функции базы данных
Excel имеет категорию функций, предназначенных для работы с базами данных (списками) .Эти функции являются аналогами обычных статистических функций, но оперируют только с элементами списка, которые удовлетворяют заданным условиям.
Синтаксис:
Функция (база данных; поле; диапазон условий), где
база данных – интервал ячеек, формирующих базу данных;
поле - имя столбца (поля) базы данных;
диапазон условий – ссылка на интервал ячеек, задающих условия для функции.
Наиболее часто используемые функции: БДСУММ, БСЧЕТ, ДМАКС, ДМИН, ДСРЗНАЧ.
Пример 8.
Определить , чему равна общая сумма в счете, рассматривая фирмы , соответствующие образцу ООО*.
Диапазон базы данных А1:Е11.
Имя поля по которому осуществляем вычисление – Е1.
Диапазон условия – G3:G4.
Формируем результат (пояснения к функции) – Общая сумма в счете -клетка G6.
В клетку G7 записываем формулу:
=БДСУММ(A1:E11;E1;G3:G4)
Рис.22. Работа функции БДСУММ
Пример 9.
Определить количество фирм, сумма в счете которых меньше 20000 рублей
Диапазон базы данных А1:Е11.
Имя поля по которому осуществляем вычисление –Е1.
Диапазон условия – G3:G4.
Формируем результат (пояснения к функции) – Количество фирм - клетка G6.
В клетку G7 записываем формулу:
=БСЧЕТ (A1:E11;E1;G3:G4)
Рис.23. Работа функции БСЧЕТ
Пример 10.
Определить минимальную сумму в счете , рассматривая фирмы , соответствующие образцу ЗАО*.
Диапазон базы данных А1:Е11.
Имя поля по которому осуществляем вычисление –Е1.
Диапазон условия – G2:G3.
Формируем результат (пояснения к функции) – Минимальная сумма - клетка G5.
В клетку G6 записываем формулу:
=ДМИН (A1:E11;E1;G2:G3)
Рис.24. Работа функции ДМИН
Пример 11.
Определить максимальную сумму в счете, рассматривая фирмы , соответствующие образцу ТОО*.
Диапазон базы данных А1:Е11.
Имя поля по которому осуществляем вычисление –Е1.
Диапазон условия – G2:G3.
Формируем результат (пояснения к функции) – Максимальная сумма - клетка G5.
В клетку G6 записываем формулу:
=ДМАКС (A1:E11;E1;G2:G3)
Рис.25. Работа функции ДМАКС (первый вариант)
Нет необходимости именовать диапазон критериев, но если это сделать, работа с расширенным фильтром упростится и сократится число возможных ошибок. Для определения имен на рабочем листе выберите на ленте вкладку Формулы. В группе Определенные имена , команду Присвоить имя.
Рис.26. .Работа функции ДМАКС (второй вариант)
5Вопросы к защите лабораторной работы
Списки данных в Excel .
Понятие фильтрации.
Типы критериев.
В каких случаях применяется критерий в виде образца-шаблона?
Правила записи вычисляемых критериев.
Команда автофильтр.
Команда расширенный фильтр. Отличие ее от автофильтра.
Правила записи диапазона критериев.
Какие существуют встроенные функции базы данных в Excel?. Охарактеризуйте их.
6Задания для самостоятельной работы
Таблица с исходными данными приведена в приложении 1
Вариант 1
С помощью расширенного фильтра составьте список автомобилей TOYOTA, которые выпущены после 2003 года.
Выходной документ должен состоять из следующих полей:
Марка автомобиля; год выпуска; тип автомобиля; пробег тыс. км.; цвет автомобиля.
С помощью функции базы данных определить, количество автомобилей черного цвета.
С помощью автофильтра составьте список автомобилей, у которых ведущий привод или передний или задний.
Вариант 2
С помощью расширенного фильтра составьте список автомобилей, у которых один хозяин в Японии и аукционная оценка равна 4,5.
Выходной документ должен состоять из следующих полей:
Марка автомобиля; год выпуска; тип автомобиля; дополнительная информация ; аукционная цена.
С помощью функции базы данных определить самый дорогой автомобиль марки NISSAN.
С помощью автофильтра составьте список автомобилей, у которых пробег между 30тыс.км. и 60тыс.км.
Вариант 3
С помощью расширенного фильтра составьте список автомобилей HONDA,у которых объем двигателя больше 2000.
Выходной документ должен состоять из следующих полей:
Марка автомобиля; год выпуска; тип автомобиля; объем двигателя ; цена автомобиля.
С помощью функции базы данных определить, количество автомобилей, у которых 6 подушек безопасности.
С помощью автофильтра составьте список автомобилей, у которых цвет белый или черный.
Вариант 4
С помощью расширенного фильтра составьте список автомобилей, у которых есть задний спойлер и год выпуска – 2004.
Выходной документ должен состоять из следующих полей:
Марка автомобиля; год выпуска; тип автомобиля; дополнительная информация; цвет автомобиля.
С помощью функции базы данных определить количество автомобилей внедорожник
С помощью автофильтра составьте список автомобилей седан или минивэн.
Вариант 5
С помощью расширенного фильтра составьте список автомобилей серебристого цвета, у которых 4 подушки безопасности.
Выходной документ должен состоять из следующих полей:
Марка автомобиля; год выпуска; подушки безопасности ; цвет автомобиля.
С помощью функции базы данных определить автомобиль марки NISSAN с самой низкой ценой.
С помощью автофильтра составьте список автомобилей, у которых есть камера заднего вида.
Вариант 6
С помощью расширенного фильтра составьте список автомобилей, у которых цена от 900 000руб до 1 000 000руб.
Выходной документ должен состоять из следующих полей:
Марка автомобиля; год выпуска; цена автомобиля.
С помощью функции базы данных определить средний пробег автомобилей TOYOTA.
С помощью автофильтра составьте список автомобилей, марка которых начинается на букву «М» и далее по алфавиту.
Вариант 7
С помощью расширенного фильтра составьте список автомобилей серебристого цвета, у которых в салоне есть DVD.
Выходной документ должен состоять из следующих полей:
Марка автомобиля; год выпуска; объем двигателя; дополнительная информация цена автомобиля.
С помощью функции базы данных определить количество автомобилей, у которых салон из кожи.
С помощью автофильтра составьте список автомобилей, у которых год выпуска или 2004г или 2005г.
Вариант 8
С помощью расширенного фильтра составьте список автомобилей, у которых аудиосистема – CD и полный ведущий привод.
Выходной документ должен состоять из следующих полей:
Марка автомобиля; ведущий привод; год выпуска; дополнительная информация .
С помощью функции базы данных определить, у какого автомобиля марки SUBARU наибольший пробег в тыс. км.
С помощью автофильтра составьте список автомобилей MITSUBISHI.
Вариант 9
С помощью расширенного фильтра составьте список автомобилей синего цвета, у которых передний привод.
Выходной документ должен состоять из следующих полей:
Марка автомобиля; год выпуска; ведущий привод ; цвет автомобиля.
С помощью функции базы данных определить среднюю цену автомобиля HONDA.
С помощью автофильтра составьте список автомобилей, марка которых начинается на букву «М» и ранее по алфавиту.
Вариант 10
С помощью расширенного фильтра составьте список автомобилей SUBARU, у которых объем двигателя от 3000 до 5000.
Выходной документ должен состоять из следующих полей:
Марка автомобиля; год выпуска; объем двигателя; аукционная цена.
С помощью функции базы данных определить, у какого автомобиля марки HONDA наименьший пробег в тыс. км.
С помощью автофильтра составьте список автомобилей TOYOTA универсал.
Вариант 11
С помощью расширенного фильтра составьте список автомобилей, у которых цена автомобиля больше средней цены автомобиля.
Выходной документ должен состоять из следующих полей:
Марка автомобиля; год выпуска; цена автомобиля.
С помощью функции базы данных определить количество автомобилей марки TOYOTA.
С помощью автофильтра составьте список автомобилей, у которых аукционная цена равна 5.
Вариант 12
С помощью расширенного фильтра составьте список автомобилей, у которых объем двигателя больше 2500 и пробег меньше 20 тыс. км.
Выходной документ должен состоять из следующих полей:
Марка автомобиля; год выпуска; объем двигателя пробег.
С помощью функции базы данных определить самый дорогой автомобиль марки TOYOTA .
С помощью автофильтра составьте список автомобилей зеленого или красного цвета.
Вариант 13
С помощью расширенного фильтра составьте список автомобилей, у которых пробег автомобиля меньше среднего пробега.
Выходной документ должен состоять из следующих полей:
Марка автомобиля; год выпуска; пробег; цвет автомобиля.
С помощью функции базы данных определить количество автомобилей серого цвета 2004 года выпуска.
С помощью автофильтра составьте список автомобилей, у которых цена больше 800 000 рублей.
Вариант 14
С помощью расширенного фильтра составьте список автомобилей , у которых есть в салоне TV.
Выходной документ должен состоять из следующих полей:
Марка автомобиля; дополнительная комплектация; цвет автомобиля.
С помощью функции базы данных определить автомобиль марки SUBARU с самой низкой ценой.
С помощью автофильтра составьте список автомобилей, у которых подушек безопасности или 6 или 4.
Вариант 15
С помощью расширенного фильтра составьте список автомобилей, у которых цена автомобиля меньше средней цены.
Выходной документ должен состоять из следующих полей:
Марка автомобиля; год выпуска; цена автомобиля; цвет автомобиля.
С помощью функции базы данных определить количество автомобилей синего цвета, у которых 2 подушки безопасности.
С помощью автофильтра составьте список автомобилей, у которых аукционная цена равна 4 или 5.
Приложение 1
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||||||
|
|
|
АУКЦИОННЫЕ АВТОМОБИЛИ ИЗ ЯПОНИИ |
|
|
|
||||||||||||||||
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|||||||||
Марка автомобиля |
Год выпуска |
Тип автомобиля |
Объем двигателя |
Руль автомобиля |
Ведущий привод |
Пробег тыс.км. |
Цвет автомобиля |
Подушки безопасности |
Аудио-система |
Дополнительная комплектация |
Дополнительная информация |
Аукционная оценка |
Цена автомобиля |
|||||||||
TOYOTA CAMRY |
2002 |
седан |
2400 |
правый |
передний |
53 |
перламутр |
2 |
CD |
задний спойлер |
парктроники |
4 |
535500 |
|||||||||
HONDA ACCORD |
2002 |
универсал |
1500 |
правый |
передний |
41 |
белый |
2 |
CD |
задний спойлер |
ксенон |
4,5 |
432000 |
|||||||||
NISSAN WINGROAD |
2004 |
универсал |
1500 |
правый |
передний |
51 |
серебро |
2 |
CD |
задний спойлер |
ксенон |
4,5 |
345000 |
|||||||||
MITSUBISHI PAJERO |
2005 |
универсал |
1800 |
правый |
полный |
11 |
серебро |
2 |
MD |
TV |
один хозяин в Японии |
4,5 |
528000 |
|||||||||
MITSUBISHI PAJERO |
2005 |
внедорожник |
3000 |
правый |
полный |
39 |
серебро |
2 |
CD |
TV |
камера заднего вида |
4,5 |
820000 |
|||||||||
MITSUBISHI AIRTREK |
2002 |
универсал |
2000 |
правый |
полный |
36 |
перламутр |
2 |
CD |
TV |
DVD |
4 |
430000 |
|||||||||
SUZUKI SWIFT |
2004 |
хэтчбэк |
1300 |
правый |
передний |
53 |
серебро |
2 |
ST |
TV |
DVD |
4 |
253000 |
|||||||||
AUDI |
2003 |
седан |
2000 |
левый |
передний |
21 |
серебро |
4 |
MD |
TV |
салон из кожи |
4,5 |
605000 |
|||||||||
SUBARU IMPREZA |
2004 |
универсал |
1500 |
правый |
передний |
25 |
голубой |
2 |
CD |
задний спойлер |
ксенон |
4,5 |
343000 |
|||||||||
LEXUS CX470 |
2003 |
внедорожник |
4700 |
левый |
полный |
60 |
темно-синий |
6 |
CD |
NAVI |
подогрев сидений |
5 |
1220000 |
|||||||||
BMW X3 |
2004 |
внедорожник |
3000 |
левый |
полный |
18 |
серый |
4 |
MD |
TV |
парктроники |
4,5 |
984000 |
|||||||||
SUBARU B9 TRIBECA |
2005 |
внедорожник |
3000 |
левый |
полный |
52 |
черный |
4 |
ST |
задний спойлер |
салон из кожи |
5 |
945000 |
|||||||||
INFINITI QXS6 |
2004 |
внедорожник |
5600 |
левый |
полный |
25 |
черный |
4 |
CD |
NAVI |
подогрев сидений |
5 |
1410000 |
|||||||||
MITSUBISHI GALANT |
2002 |
седан |
2000 |
правый |
передний |
22 |
серебро |
2 |
ST |
задний спойлер |
ксенон |
4,5 |
302000 |
|||||||||
MITSUBISHI GRANDIS |
2003 |
минивэн |
2400 |
правый |
полный |
35 |
серебро |
2 |
MD |
задний спойлер |
парктроники |
5 |
499000 |
|||||||||
MITSUBISHI LANCER |
2005 |
седан |
2000 |
правый |
передний |
15 |
голубой |
2 |
ST |
задний спойлер |
ксенон |
4 |
350000 |
|||||||||
MITSUBISHI PAJERO |
2002 |
внедорожник |
3500 |
правый |
полный |
93 |
черный |
2 |
CD |
задний спойлер |
салон из кожи |
5 |
800000 |
|||||||||
HONDA AVANCIER |
2000 |
универсал |
2300 |
правый |
полный |
30 |
серебро |
2 |
MD |
TV |
ксенон |
4,5 |
399000 |
|||||||||
HONDA ODYSSEY |
2002 |
универсал |
2300 |
правый |
полный |
72 |
перламутр |
2 |
MD |
задний спойлер |
один хозяин в Японии |
4,5 |
409000 |
|||||||||
HONDA ODYSSEY |
2002 |
универсал |
2300 |
правый |
полный |
39 |
черный |
2 |
CD |
NAVI |
ксенон |
4,5 |
434700 |
|||||||||
HONDA ELEMENT |
2005 |
внедорожник |
2400 |
левый |
полный |
21 |
оранжевый |
2 |
CD |
задний спойлер |
один хозяин в Японии |
4,5 |
721000 |
|||||||||
HONDA CR-V |
2006 |
минивэн |
2400 |
правый |
полный |
24 |
черный |
2 |
MD |
NAVI |
ксенон |
4,5 |
705000 |
|||||||||
HONDA AVANCIER |
2003 |
универсал |
1600 |
правый |
передний |
43 |
синий |
2 |
MD |
задний спойлер |
DVD |
4,5 |
445000 |
|||||||||
TOYOTA PREMIO |
2003 |
седан |
1800 |
правый |
передний |
57 |
серебро |
2 |
CD |
TV NATI |
один хозяин в Японии |
4,5 |
464000 |
|||||||||
TOYOTA VOLTZ |
2002 |
универсал |
1800 |
правый |
полный |
63 |
серебро |
2 |
CD |
TV NATI |
ксенон |
4 |
503000 |
|||||||||
TOYOTA WISH |
2003 |
универсал |
1800 |
правый |
передний |
51 |
черный |
2 |
CD |
задний спойлер |
ксенон |
4 |
414000 |
|||||||||
TOYOTA VEROSSA |
2003 |
универсал |
2000 |
правый |
задний |
65 |
перламутр |
2 |
ST |
TV NATI |
один хозяин в Японии |
4 |
432000 |
|||||||||
TOYOTA HILUX SURE |
2004 |
внедорожник |
3400 |
правый |
полный |
28 |
черный |
4 |
CD |
задний спойлер |
салон из кожи |
4,5 |
929200 |
|||||||||
TOYOTA MARK II |
2003 |
седан |
2000 |
правый |
задний |
47 |
перламутр |
2 |
CD |
NAVI |
ксенон |
4,5 |
435000 |
|||||||||
TOYOTA KLUGER |
2002 |
внедорожник |
2400 |
правый |
полный |
52 |
белый |
2 |
CD |
задний спойлер |
один хозяин в Японии |
4,5 |
654200 |
|||||||||
TOYOTA ALLEX |
2003 |
хэтчбэк |
1800 |
правый |
передний |
38 |
серебро |
2 |
CD |
NAVI |
один хозяин в Японии |
4,5 |
385700 |
|||||||||
TOYOTA IPSUM |
2002 |
минивэн |
2400 |
правый |
передний |
57 |
серебро |
2 |
MD |
TV |
камера заднего вида |
4,5 |
451400 |
|||||||||
TOYOTA COROLLA |
2003 |
седан |
1500 |
правый |
передний |
27 |
серебро |
2 |
CD |
TV |
ксенон |
4,5 |
398000 |
|||||||||
TOYOTA VITZ |
2002 |
хэтчбэк |
1300 |
правый |
передний |
22 |
зеленый |
2 |
CD |
TV |
один хозяин в Японии |
4,5 |
287000 |
|||||||||
TOYOTA RAV4 |
2004 |
универсал |
2000 |
правый |
полный |
41 |
перламутр |
2 |
CD |
задний спойлер |
ксенон |
4,5 |
590000 |
|||||||||
TOYOTA COROLLA |
2002 |
седан |
1800 |
правый |
передний |
20 |
золото |
2 |
ST |
задний спойлер |
ксенон |
4 |
385000 |
|||||||||
NISSAN SUNNY |
2003 |
седан |
1500 |
правый |
передний |
1 |
голубой |
2 |
CD |
задний спойлер |
один хозяин в Японии |
5 |
286100 |
|||||||||
NISSAN BLUEBIRD |
2004 |
седан |
1800 |
правый |
передний |
20 |
зеленый |
2 |
CD |
TV |
ксенон |
4 |
343400 |
|||||||||
NISSAN X-TRAIL |
2003 |
внедорожник |
2000 |
правый |
полный |
26 |
серебро |
2 |
CD |
NAVI |
ксенон |
4,5 |
577700 |
|||||||||
NISSAN TINO |
2002 |
минивэн |
1800 |
правый |
передний |
11 |
голубой |
2 |
ST |
NAVI |
один хозяин в Японии |
4,5 |
344400 |
|||||||||
NISSAN WINGROAD |
2004 |
универсал |
1500 |
правый |
передний |
51 |
серебро |
2 |
CD |
задний спойлер |
один хозяин в Японии |
4,5 |
348500 |
|||||||||
NISSAN TEANA |
2004 |
седан |
2500 |
правый |
полный |
74 |
черный |
2 |
CD |
TV |
один хозяин в Японии |
4 |
484800 |
|||||||||
NISSAN TEANA |
2004 |
седан |
2300 |
правый |
передний |
33 |
голубой |
2 |
CD |
TV |
ксенон |
4 |
437000 |
|||||||||
NISSAN X-TRAIL |
2004 |
внедорожник |
2000 |
правый |
полный |
41 |
белый |
2 |
CD |
NAVI |
камера заднего вида |
4 |
582000 |
|||||||||
NISSAN BLUEBIRD |
2001 |
седан |
1800 |
правый |
передний |
50 |
голубой |
2 |
CD |
TV |
сигнализация |
4 |
265000 |
|||||||||
NISSAN LAUREL |
2001 |
седан |
2000 |
правый |
задний |
74 |
серебро |
2 |
CD |
TV |
ксенон |
4 |
280000 |
|||||||||
NISSAN FORESTER |
2003 |
универсал |
2000 |
правый |
полный |
55 |
белый |
2 |
MD |
задний спойлер |
салон из кожи |
4,5 |
487000 |
|||||||||
NISSAN LEGACY |
2004 |
седан |
2000 |
правый |
полный |
18 |
серебро |
2 |
CD |
TV |
DVD |
4,5 |
499000 |
|||||||||
NISSAN B9 TRIBECA |
2005 |
внедорожник |
3000 |
левый |
полный |
52 |
черный |
4 |
CD |
NAVI |
камера заднего вида |
5 |
954000 |
|||||||||
SUBARU IMPREZA |
2004 |
универсал |
1500 |
правый |
передний |
25 |
голубой |
2 |
MD |
задний спойлер |
один хозяин в Японии |
4,5 |
346400 |
|||||||||
SUBARU OUTBACK |
2004 |
универсал |
2500 |
правый |
полный |
70 |
серебро |
2 |
CD |
задний спойлер |
один хозяин в Японии |
4,5 |
553500 |
|||||||||
SUBARU IMPREZA |
2002 |
универсал |
1500 |
правый |
передний |
11 |
серебро |
2 |
CD |
NAVI |
один хозяин в Японии |
4,5 |
313000 |
|||||||||
SUBARU FORESTER |
2005 |
универсал |
2000 |
правый |
полный |
20 |
черный |
2 |
CD |
TV |
один хозяин в Японии |
4,5 |
596900 |
|||||||||
SUBARU FORESTER |
2005 |
универсал |
2000 |
правый |
полный |
24 |
серебро |
2 |
CD |
TV |
ксенон |
4,5 |
601000 |
|||||||||
SUBARU FORESTER |
2006 |
универсал |
2000 |
правый |
полный |
55 |
белый |
2 |
ST |
задний спойлер |
один хозяин в Японии |
4,5 |
573700 |
|||||||||
SUBARU IMPREZA |
2004 |
универсал |
2000 |
правый |
полный |
41 |
красный |
2 |
CD |
задний спойлер |
ксенон |
4,0 |
400000 |
1 Окно можно вызвать, если в соответствующем подменю Текстовые фильтры, Числовые фильтры, Фильтры по дате из меню автофильтра выбрать пункт Настраиваемый фильтр.