Лабораторная работа 1: Работа с электронной таблицей как с базой данных.
Сортировка и фильтрация данных.
Цель работы:
· познакомиться с использованием электронной таблицы как базы данных;
· научиться осуществлять поиск информации в базе данных по различным критериям;
· научиться производить сортировку информации.
Задание 1. Заполните таблицу.
Основные понятия баз данных
БД предназначены для хранения больших объёмов структурированной информации.
БД в Excel – это таблица, организованная определённым образом. Область таблицы А2:Н17 можно рассматривать как базу данных. Столбцы А, В, С, D, E, F, G, H этой таблицы называются полями, а строки со 2 по 17 – записями. Область А2:Н2 содержит имена полей.
Существуют ограничения, накладываемые на структуру базы данных:
· первый ряд базы данных должен содержать неповторяющиеся имена полей;
· остальные ряды данных должны содержать записи, которые не являются пустыми рядами;
· информация по полям (столбцам) должна быть однородной, т. е. только цифры или только текст.
Весь инструмент работы с БД в Excel сосредоточен на вкладке Данные.
Фильтрация данных
Excel даёт возможность работать с данными, удовлетворяющими определённым условиям. Фильтрация таблицы оставляет на экране только те строки, которые отвечают заданным критериям.
При использовании фильтра необходимо переместить курсор в область, содержащую базу данных, или выделить её. Затем нужно перейти на вкладку Данные, щелкнуть по кнопке Фильтр.
. На именах полей появятся кнопки с изображением стрелок вниз. Нажимая на кнопки, можно задавать критерии фильтрации.
Задание 1. Оставить в базе данных только трехкомнатные квартиры.
1. Установите курсор в области базы данных.
2. На вкладке Данные щелкните по кнопке Фильтр. На полях появились кнопки.
3. Нажмите на кнопку в поле «Кол-во комнат».
4. Снимите флажок «Выделить все».
5. Установите флажок «3».
6. Нажмите «ОК».
Проверьте: в базе данных остались 6 квартир (№ 2, 8, 9, 10, 12, 15).
7. Щелкните по кнопке «Фильтр», чтобы убрать кнопки с полей.
Задание 2. С использованием фильтра осуществить поиск двухкомнатных квартир Октябрьского или Центрального района, расположенных не выше 5 этажа с телефоном.
1. Установите курсор в области базы данных.
2. На вкладке Данные щелкните по кнопке Фильтр. На полях появились кнопки.
3. Нажмите на кнопку в поле «Район». Выберите «Текстовые фильтры» - «Настраиваемый фильтр». Появится окно «Пользовательский автофильтр».
4. В окне «Пользовательский автофильтр» задайте критерии согласно рис. 1. Примечание: Символ «*» заменяет любое количество любых символов.
Рис. 1
Н ажмите кнопку Ok.
Проверьте! В базе данных остались шесть квартир с № п/п 3, 5, 7, 10, 13, 14.
5. Нажмите на кнопку на поле «Кол-во комнат». Снимите флажок «Выделить все», установите флажок «2».
Проверьте! В базе данных остались три квартиры с № п/п 5, 7, 14.
6. Нажмите на кнопку на поле «Этажность квартиры». Выберите пункт «Числовые фильтры» - «меньше или равно».
7. В диалоговом окне задайте критерий (Рис. 2).
Рис. 2
Проверьте! В базе данных остались две квартиры с № п/п 7, 14.
8. Нажмите на кнопку на поле «Телефон». Снимите флажок «Выделить все», затем установите флажок «есть».
Проверьте! Осталась одна квартира № 7.
2.8. На вкладке Данные щелкните по кнопке Фильтр, чтобы убрать кнопки с полей.
Задание 3. С использованием фильтра самостоятельно:
· осуществите поиск трёхкомнатных квартир в кирпичных домах со стоимостью не выше 1 миллиона руб. (ответ: одна квартира с № 15)
· найдите однокомнатные квартиры в 5-этажных домах (ответ: № 3, 13).
Расширенный фильтр (на примере задания № 4, стр. 5)
При использовании Расширенного фильтра необходимо сначала определить (создать) три области (см. рис. 2):
· Интервал списка – область базы данных (А2:Н17), эта область у вас уже есть.
· Интервал критериев – область, где задаются критерии фильтрации (А19:Н21). Для ее создания скопируйте заголовки таблицы в строку 19, в строки 20 и 21 заносятся данные из задачи № 4.
· Интервал извлечения – область, в которой будут появляться результаты фильтрации (А23:Н28). Для ее создания скопируйте заголовки таблицы в строку 23. Имена полей во всех интервалах должны точно совпадать. В строки 24-26 будут занесены результаты фильтрации.
Рис. 2
Для выполнения действий по фильтрации переходим на вкладку Данные и щелкаем по кнопке Дополнительно 
. Появится окно Расширенный фильтр. В этом окне надо указать координаты интервалов (Рис. 3) и установить флажок «Скопировать результат в другое место».
Рис. 3
Нажимаем ОК.
Задание 4. С использованием Расширенного фильтра осуществите поиск трёхкомнатных квартир стоимостью не более 900 тыс. руб. и однокомнатных со стоимостью менее 600 тыс. руб.
4.1. Создайте интервал критериев и интервал извлечения.
4.2. Запишите критерии поиска в интервал критериев.
4.3. Поместите курсор в область базы данных.
4.4. Вкладка Данные – кнопка Дополнительно
4.5. Установите флажок Скопировать результат в другое место.
4.6. Проверьте правильность задания интервалов. Нажмите кнопку Ok.
Проверьте! Должны остаться квартиры с № 1, 9, 13.
Задание 5. С использованием Расширенного фильтра самостоятельно:
Найдите двухкомнатные квартиры Октябрьского района, расположенные не выше 5 этажа и однокомнатные квартиры Железнодорожного района (ответ: № 6, 14).
Найдите все однокомнатные квартиры в панельных домах стоимостью менее 600 тыс. руб. и двухкомнатные квартиры с телефоном в кирпичных домах стоимостью менее 700 тыс. руб
(ответ: №1, 7, 13).
Сортировка данных
Для выполнения сортировки необходимо выделить область базы данных или поместить в неё курсор, а затем на вкладке Данные щелкнуть по кнопке Сортировка 
При этом появится диалоговое окно, в котором выбрать название поля, по которому нужно производить сортировку и указать порядок сортировки (Рис. 4).
Для добавления строки «Затем по» была нажата кнопка «Добавить уровень».
Рис. 4
Задание 6.
· Отсортируйте квартиры по районам города, а в районе – по количеству комнат.
· Отсортировать квартиры по материалу, затем – по количеству комнат, в последнюю очередь – по стоимости.
Лабораторная работа 2 «Отчётная ведомость о результатах работы сети магазинов»
создание и форматирование электронных таблиц;
работа с Мастером функций;
работа с ЭТ как с базой данных;
построение диаграмм.
Спроектируем форму ЭТ и введём в неё исходные данные:
Задание 1. Вычислить:
суммарную выручку по каждому магазину;
итоговые значения выручки по всем магазинам за каждый месяц;
общую сумму выручки по всем магазинам за все месяцы;
среднее значение выручки по каждому магазину;
место магазина по объёму продаж;
процент выручки для каждого магазина в общем объёме выручки.
Задание 2. Определить:
у каких магазинов суммарная выручка минимальная;
у каких магазинов выручка за июнь больше 400млн. руб.;
у каких магазинов выручка за июнь превышает выручку за июль;
у каких магазинов выручка за август составляет от 300 до 400 млн. руб.
Задание 3. Определить:
сколько магазинов имеют среднюю выручку более 400 млн. рублей;
чему равна выручка магазинов 1 и 5 за июль.
Задание 4.
построить круговую диаграмму, характеризующую суммарную выручку каждого магазина;
построить гистограмму распределения выручки для каждого магазина за июнь, июль и август.
Ввод формул
Задание 1
v Все суммы определяются с помощью функции СУММ (кнопка автосумма 
на панели Стандартная).
v Среднее значение выручки по каждому магазину рассчитывается по формуле:
Среднее значение выручки определяется с помощью функции СРЗНАЧ.
v место магазина по объёму продаж представляет собой ранг числа в списке чисел.
Ранг числа – это его величина относительно других значений в списке: его порядковый номер относительно других чисел в списке.
В Excel ранг числа определяется с помощью функции РАНГ.
v процент суммарной выручки магазина в общем объёме выручки определяется по формуле:
процент выручки i-го магазина в общем объёме выручки | = |
общая сумма выручки по всем магазинам за все месяцы |
сумма выручки i-го магазина за все месяцыЭлектронная таблица с формулами
Задание 2.
Для формирования выборки по условию в Excel используется Расширенный фильтр.
Для этого в свободной области ЭТ создаётся таблица критериев (диапазон условий) и выходной документ.
В таблице критериев указываются имена полей из ЭТ, по которым осуществляется поиск, и условие поиска.
В выходном документа указываются имена полей из ЭТ, для которых осуществляется поиск.
Выходной документ, как и вся электронная таблица может иметь название.
Создадим общую таблицу критериев для всех примеров из задания 2 и спроектируем выходные документы.
Для создания каждого из выходных документов используется команда меню Данные – Фильтр – Расширенный фильтр.
В окне диалога указывается:
исходный диапазон А3:Н9
диапазон условий для примера 1 А14:А15
диапазон условий для примера 2 В14:В15
диапазон условий для примера 3 С14:С15
диапазон условий для примера 4 D14:Е15
диапазон выходной таблицы для примера 1 А19:В19
диапазон выходной таблицы для примера 2 А23:В23
диапазон выходной таблицы для примера 3 А28:С28
диапазон выходной таблицы для примера 4 А34:В34
Количество строк выходного документа Excel определяет сам.
Задание 3
Для определения количественных соотношений в Excel используются функции категории РАБОТА С БАЗОЙ ДАННЫХ.
Функции Баз Данных (БД) имеют структуру:
Функция (база_данных;поле;критерий), где
база_данных – это интервал ячеек, формирующих список или базу данных.
поле определяет столбец, используемый функцией. Поля данных в списке должны содержать идентифицирующее имя в первой строке.
критерий – это ссылка на интервал ячеек, задающих условия для функции. Функция возвращает данные из списка, которые удовлетворяют условиям, определённым диапазоном критериев. Диапазон критериев включает копию названия столбца, для которого выполняется подведение итогов.
Сформируем таблицы критериев и выходные документы.
В ячейку А45 запишем формулу подсчёта количества магазинов
=БСЧЁТ(А3:Н9;G3;A41:A42)
В ячейку А54 запишем формулу определения суммы выручки
=БДСУММ(А3:Н9;Е3;А49:А51)
Построение диаграммы
В Excel диаграммы строятся с помощью Мастера диаграмм (кнопка 
на панели инструментов Стандартная).
а) Построить круговую диаграмму, характеризующую суммарную выручку каждого магазина.
Для построения круговой диаграммы необходимо вызвать Мастер диаграмм и в пошаговом режиме указать:
1 шаг
тип диаграммы – круговая
вариант круговой диаграммы – объёмная
2 шаг
диапазон – Е4:Е9
3 шаг
название диаграммы – Выручка магазинов
легенда – добавить легенду
подписи данных – имена категорий и доли
4 шаг
Поместить диаграмму на отдельном листе
Вид готовой диаграммы
В Excel существует возможность сравнительного анализа информации, представленной в графическом виде. Примером такого анализа является гистограмма.
б) Построить гистограмму распределения выручки для каждого магазина за июнь, июль и август.
Вызовите Мастер Диаграмм и в пошаговом режиме введите:
1 шаг
тип диаграммы – гистограмма
вид диаграммы – обычная
2 шаг
диапазон данных – В4 : D9
ряды находятся в столбцах
ряд со значением В4 : В9 имеет имя июнь (В3)
ряд со значением С4 : С9 имеет имя июль (С3)
ряд со значением D4 : D9 имеет имя август (D3)
3 шаг
название диаграммы Выручка магазинов в млн. руб.
ось X (категорий) Номер магазина
ось (Y) (значений) Объём выручки
4 шаг
Диаграмму поместить на отдельном листе
Вид готовой диаграммы
· Сохранить работу в файле Выручка магазинов
Самостоятельная работа.
Номер варианта должен совпадать со 2-й цифрой в списке студентов в журнале. Например, если номер ФИО в списке – 12, тогда номер варианта контрольной работы = 2. Номер ФИО в списке – 30, тогда номер варианта КР = 10.
Вариант № 1
1. Проанализировать динамику поступления товаров от поставщиков.
2. Выдать список поставщиков, у которых удельный вес и в 2001, и в 2002 годах не превышал 50.
3. Подсчитать число поставщиков, у которых превышение не больше 0,5 млн. руб.
4. Построить круговую диаграмму поступления товаров в % к 2001 году и гистограмму динамики удельного веса поступления товаров в 2001 и 2002 годах по поставщикам.
Вариант № 2
1. Рассчитать доход от реализации колбасных изделий АОЗТ «Мясная лавка». Результаты округлить до 2-х знаков после запятой, используя функцию ОКРУГ.
Наименование | Объём | Цена за | Торгово- | Цена со | Сумма с учё- |
Колбаса пермская, | 6 | 76 | 8 | ||
Колбаса одесская, | 12 | 80 | 8 | ||
Колбаса краков- | 4 | 90 | 7,8 | ||
Колбаски охотни- | 2 | 105 | 8,5 | ||
Колбаса сервелат | 3 | 110 | 8,5 | ||
Итого: |
2. Вывести список наименований изделий, объём производства которых больше 5, но меньше 10 т.
3. Подсчитать сумму от реализации колбасных изделий, у которых торгово-сбытовая скидка больше или равна 8%.
4. Построить круговую диаграмму доходов от реализации колбасных изделий и гистограмму изменения цены по изделиям.
Вариант № 3
Рассчитать стоимость продукции с учётом скидки. Результаты округлить до 2-х знаков после запятой, используя функцию ОКРУГ.
Номенкл. | Наименование | Коли- | Цена | Стои- | % | Сумма | Стоимость с |
202 | Монитор | 5 | 12 | 7 | |||
201 | Клавиатура | 25 | 0,25 | 5 | |||
213 | Дискета | 100 | 0,02 | 10 | |||
335 | Принтер | 2 | 10 | 20 | |||
204 | Сканер | 1 | 8 | 15 | |||
Итого: |
2. Выдать список наименований продукции с номенклатурными номерами, стоимость с учётом скидки которых находится в пределах от 5 до 10 тыс. руб.
|
Из за большого объема этот материал размещен на нескольких страницах:
1 2 3 |
