lab12
.pdfИнформатика. Часть I |
1 |
|
|
|
|
Лабораторная работа №12
Основные понятия
Списки данных. Сортировка. Поиск по условию. Структуры данных. Функции: =ГОД(), =МЕСЯЦ(), =ДЕНЬ(), =СЕГОДНЯ(),
ДНЕЙ360().
Задание 1
Дан журнал отчетов о выручке филиалов по месяцам.
Журнал отчетов о выручке филиалов
Информатика. Часть I |
2 |
|
|
|
|
Дата |
Филиал |
Сумм |
|
|
а |
|
|
|
12.01 |
Центральный |
23000 |
12.01 |
Ж-д |
24000 |
22.01 |
Центральный |
50000 |
23.01 |
Октябрьский |
15000 |
25.01 |
Октябрьский |
12000 |
10.02 |
Центральный |
20000 |
10.02 |
Ж-д |
25000 |
22.02 |
Центральный |
52000 |
23.02 |
Октябрьский |
25000 |
25.02 |
Октябрьский |
32000 |
10.03 |
Центральный |
20000 |
10.03 |
Ж-д |
25000 |
22.03 |
Центральный |
52000 |
23.03 |
Октябрьский |
25000 |
25.03 |
Октябрьский |
32000 |
10.04 |
Ж-д |
55000 |
22.04 |
Центральный |
62000 |
23.04 |
Октябрьский |
75000 |
15.05 |
Октябрьский |
12000 |
15.05 |
Ж-д |
45000 |
23.05 |
Центральный |
52000 |
23.05 |
Октябрьский |
45000 |
15.06 |
Октябрьский |
12000 |
15.06 |
Ж-д |
45000 |
23.06 |
Центральный |
52000 |
20.07 |
Октябрьский |
45000 |
23.07 |
Ж-д |
45000 |
23.07 |
Центральный |
52000 |
15.08 |
Октябрьский |
13000 |
15.08 |
Ж-д |
45000 |
20.08 |
Центральный |
52000 |
23.08 |
Октябрьский |
42000 |
15.09 |
Октябрьский |
13000 |
15.09 |
Ж-д |
45000 |
20.09 |
Центральный |
52000 |
23.09 |
Октябрьский |
42000 |
15.10 |
Октябрьский |
15000 |
15.10 |
Ж-д |
42000 |
20.10 |
Центральный |
50000 |
23.10 |
Октябрьский |
40000 |
15.11 |
Октябрьский |
13500 |
15.11 |
Ж-д |
43000 |
20.11 |
Центральный |
55000 |
23.11 |
Октябрьский |
42500 |
15.12 |
Октябрьский |
11500 |
15.12 |
Ж-д |
35000 |
20.12 |
Центральный |
45000 |
23.12 |
Октябрьский |
22500 |
Информатика. Часть I |
3 |
Упорядочить журнал по названиям филиалов. Подвести промежуточные итоги о суммах выручки за каждый квартал,итоги по филиалу и общий итог.
Для подведения поквартальных итогов необходимо вычислить номер квартала по дате. Номер квартала можно вычислить по номеру месяца данной даты как:
Номер_квартала = ОКРУГЛВВЕРХ( Номер_месяца / 3;0)
Номер месяца можно получить используя функцию =МЕСЯЦ():
Номер_месяца = МЕСЯЦ(Номер_квартала)
Для отображения номера месяца римскими цифрами используйте функцию =РИМСКОЕ().
Добавьте колонку квартал (колонка B), для вычисления квартала по дате платежа. Исключив промежуточные переменные Номер_месяца и Номер_квартала, в столбце B получим формулу для отображения номера квартала римскими цифрами:
=РИМСКОЕ(ОКРУГЛВВЕРХ(МЕСЯЦ(A2)/3;0))
Отсортируйте таблицу по кварталам и филиалам. Установите автофильтр.Подведите итоги по кварталам и филиалам.
Информатика. Часть I |
4 |
Задание 2
Фирма в течение пяти лет реализует на рынке г. Барнаула компьютерное оборудование и программное обеспечение для дома и малого офиса. За это время накопилось достаточно информации о клиентах, характере совершаемых ими покупок т.д.,на основание которой фирма сформировала список "Клиенты", который использует как небольшую однотабличную базу данных для проведения маркетинговых исследований.
Задание
1. Сформируйте в Excelсписок из 10 записей.
2. Повторите назначение и применение функций =СЕГОДНЯ(), =ДНЕЙ360(). В поле "Сколько лет является клиентом" в ячейке I2 установите числовой формат для определения сколько полных лет клиент сотрудничает с фирмой введите формулу:
=ДНЕЙ360(Дата_покупки;СЕГОДНЯ())/360.
Здесь Дата_покупки представляет собой ссылку на ячейку с датой первой покупки для данного клиента.
3. Фирма считает клиента постоянным если он совершил первую покупку больше года назад. Графу "Статус клиента" заполните формулой с использованием функции =ЕСЛИ():
= ЕСЛИ(I2 > 1;Постоянный клиент;“”).
4.Используя механизм автозаполнения распространите формулы на все строки соответствующего столбца.
Информатика. Часть I |
5 |
По созданной таблице сформируйте несколько запросов с помощью команды Расширенный фильтр из меню Данные по образцам:
Выбрать из списка все записи, содержащие информацию о конкретном клиенте и покупках,
совершенных после заданного числа.
Для создания такого запроса нужно задать условия отбора на разных строках.
Клиент |
Дата покупки |
|
|
Петров |
|
|
>=1.05.00 |
Вывести на экран список клиентов, которые приобрели определенный виды оборудования.
Купленная техника,ПО,оказанная услуга и др.
А
Р
С
Выбрать из списка все записи, содержащие
информацию об определенном виде проданного оборудования за 10.04.00.
Для формирования такого критерия нужно задать условия на одной строке
Купленная техника,ПО, |
Дата покупки |
оказанная услуга и др. |
|
Монитор LG |
10.04.00 |
Информатика. Часть I |
6 |
Отобрать из списка строки, содержащие информацию о клиентах, которые приобрели определенные виды товаров и имеют статус
постоянного клиента.
Условие «постоянный клиент» должно быть повторено на каждой строке диапазона критериев, так как заданы два условия:по алфавиту и по сумме.
Купленная техника или ПО, |
Статус клиента |
оказанная услуга и т.д. |
|
А |
постоянный клиент |
|
|
Р |
постоянный клиент |
|
|
С |
постоянный клиент |
|
|
Задание текстовых условий.
1Найти все значения по заданному полю, которые начинаются с М. Например, для поля «Купленная техника, ПО, оказанная услуга и др.» это условие выполнится для ячеек содержащих:Монитор,Мышь, Манипулятор,и т.п.
Купленная техника или ПО,оказанная услуга и.т д.
М
2 Если поставить символ >или< перед буквой, это означает,что нужно найти все значения по алфавиту соответственно после или перед введенным текстовым значением.
2 Если ввести формулу ="=текст", то можно найти значения, которые точно совпадают со строкой символов текст.Например:
Телефон
="=22-3467 "
Информатика. Часть I |
|
7 |
Рекомендации по формированию |
критериев |
|
отбора информации из списка |
|
|
1. Для задания диапазона условий вставьте несколько новых строк выше или ниже списка.
2. Диапазон условий должен содержать, по крайней мере, 2 строки.
3. Введите один или несколько заголовков в верхнюю строку,а условия отбора – во вторую и последующие строки под заголовком. (Заголовки лучше скопировать из таблицы вашего списка). В диапазон достаточно включить заголовки тех столбцов, которые используются в условии отбора.
Выполните: Данные, Фильтр, Расширенный фильтр. В полях «Исходный диапазон» и «Диапазон критериев» укажите соответствующие блоки ячеек.
Задание 3
1. Компьютерная фирма ведет электронную ведомость учета оплаты по совершенным сделкам, в которой отражается информация:
Рекомендации к выполнению
1.В столбцах, содержащих информацию о датах и суммах, установите форматы: дата и денежный соответственно.
2.Количество просроченных платежей рассчитайте, используя функцию СЧЕТЕСЛИ(). Результат поместите в ячейку H 2.
Информатика. Часть I |
8 |
3.Столбец А дополнительно можно оформить примечанием,например,внести реквизиты фирмы и т.д.(Меню Вставка,команда Примечание)
4.В столбцах В и С заполните по 10 ячеек (заполняются все ячейки непосредственно при заключении договора).
5.Столбец D может содержать пустые ячейки, так как по условию договора возможна отсрочка оплаты по договору.
6.В ячейки столбца E введите формулу,использующую функцию ЕСЛИ(), которая должна автоматически рассчитывать сумму сделки с учетом скидки. Сумма скидки определяется по условиям:
а)если сумма превышает установленное значение Х, то скидка 3%
б) если сумма превышает установленное значение Y (Y>X),то скидка 5% .
Предельные значения Х и Y задать в ячейках J2 и J3,а в формулах для расчета скидки применить абсолютные ссылки на эти ячейки.Можно в ячейки K2 и K3 занести величины процентов скидки.
7.Дату переучета в ячейку F2 установите с помощью функции СЕГОДНЯ().При желании в эту ячейку можно вставить любую дату и автоматически получить информацию по состоянию дел на указанную дату.
8.Число просроченных дней в ячейке G 2 рассчитайте по формуле:=ЕСЛИ(D 2 = 0;$F$2 – B2;””).
Обязательно установите числовой формат для столбца G .Скопируйте полученную формулу до конца диапазона.
9.Для расчета количества просроченных платежей
свыше 20 дней в ячейку Н2 введите формулу =СЧЕТЕСЛИ(Е2:Е11;” >=20”). Эта функция вычислит количество непустых ячеек в указанном диапазоне, удовлетворяющих данному критерию. Заполните несколько пустых ячеек в столбце D , измените дату переучета или количество дней, сумма в ячейке Н11 будет меняться
Информатика. Часть I |
9 |
10.В ячейке I2 вычислите стоимость просроченных платежей,используя функцию СУММЕСЛИ(): =СУММЕСЛИ(G 2:G 11;”>0”;E2:E11).
Задание 4
1.Скопируйте файл p:\studapp\info\oshkalo.vv\inform atika\svod.xls на свой пользовательский диск,загрузите его и преобразуйте его в список.
2.Выполните:Данные->Форма… ,добавьте в список данные об АО Престиж:"24.02.02,Материалы,10000 р.,АО Престиж"
3.Используя Форму данных… ,просмотрите информацию о Казакове К.Г и измените сумму зарплаты за 6.01.02
на 2800 р.
4.Используя форму данных,просмотрите все данные списка о расходах на материалы,превышающих
12000 р.
5.Выполните:Фильтр-> Автофильтр и отобразите все
данные списка по АО АО ИНВЕСТ.
6.Выполните:Фильтр-> Автофильтр и отобразите все данные списка по накладным расходам,а затем только за январь.
7.Выполните:Данные->Итоги и по каждому расходу подведите итог по полю Сумма (предварительно отмените Автофильтр и отсортируйте данные списка по полю Расход).
8.Отобразите все данные списка по зарплате и накладных расходах за январь,результат поместите в новый диапазон рабочего листа.
9.Выполните:Данные->Расширенный фильтр и:
10.Используя вычисляемый критерий рассчитайте сумму накладных расходов за январь.
11.Для исходного списка,применив функцию
БСЧЕТ(),подсчитайте в отдельной ячейке количество выданных зарплат,с помощью функций ДМАКС()и ДМИН()определите соответственно минимальную и максимальную выплаты.
12. Сформируйте Сводную таблицу,позволяющую отобразить в один столбец сумму расходов,связанных с каждым получателем,с возможностью последующей
Информатика. Часть I |
10 |
фильтрации по полю Расход (поле Расход следует поместить в область страниц,поле Получатель – в область строк,а в область Данные Сумма по полю).
13.Используя список (Все),отобразите данные по Зарплате,по Накладным расходам.
14.Используя кнопку панели инструментов Сводные таблицы,отобразите информацию по каждому расходу.
15.Сформируйте еще одну Сводную таблицу (на этом же листе или на новом),позволяющую отобразить в один столбец сумму Расходов по каждой Дате.
16.Сгруппируйте полученные данные по месяцам, используя контекстное меню или соответствующую кнопку на панели инструментов Сводная таблица.
Домашнее задание
Постройте сводную таблицу для следующего примера.
Пусть наша фирма имеет три филиала:Центральный, Октябрьский и Южный. Эти филиалы занимаются оптовой реализацией следующей продукции: Молоко, Кефир, Творог и Сыр. Отчет о реализации товаров в филиалах нашей фирмы приведен в таблице.
Продукци |
Филиал |
Дата |
Сумма |
я |
|
реализации |
|
|
|
|
|
Молоко |
Центральный |
25.фев |
20000 |
Творог |
Октябрьский |
26.фев |
13000 |
Кефир |
Октябрьский |
26.фев |
15000 |
Сыр |
Центральный |
27.фев |
15000 |
Сыр |
Октябрьский |
01.мар |
20000 |
Кефир |
Южный |
01.мар |
1700 |
Творог |
Южный |
01.мар |
2300 |
Сыр |
Южный |
01.мар |
15000 |
Молоко |
Октябрьский |
05.мар |
23000 |
Кефир |
Октябрьский |
05.мар |
2200 |
Кефир |
Южный |
05.мар |
15000 |
Сыр |
Октябрьский |
15.мар |
24000 |
Творог |
Октябрьский |
15.мар |
2300 |
Молоко |
Октябрьский |
18.мар |
20000 |