9160
.pdf
Лабораторная работа №2
Тема:Создание таблиц с использованием нескольких листов и работа с Мастером функций.
Цель работы: Научиться создавать таблицы на нескольких листах, проводить сложные вычисления с использованием встроенных функций, абсолютной и относительной адресации. Освоить функции СУММ, СРЗНАЧ, ЕСЛИ, ВПР,
СУММЕСЛИ, СЧЕТЕСЛИ, МАКС, МИН.
Порядок выполнения работы
1. В новой рабочей книге организуем учет реализации товаров со склада, состоящий из трех таблиц: прайс-лист, продажи и скидки. Начнем работу с создания вспомогательных таблиц: на Листе 1 и Листе 2 расположите таблицы,
приведенные на рис.2.1, 2.2, соответственно, и
заполните их данными. Переименуйте листы, дав им названия: Прайс-лист и Скидки, в
соответствии с хранящейся на них информацией.
2. На Листе 3 создайте таблицу реализации товаров со склада: введите название таблицы,
текущую дату (используйте функцию СЕГОДНЯ()) |
Рис. 2.1. |
|||||
|
||||||
и курс, сформируйте шапку |
заголовков (см. |
|
||||
рис.2.3). Заполните данными первые три столбца |
|
|||||
таблицы (для заполнения первого столбца |
|
|||||
используйте автопродолжение; |
наименования |
|
||||
товаров |
во |
втором |
столбце |
должны |
|
|
соответствовать |
списку |
|
прайс-листа). |
|
||
|
|
|
|
|
|
Рис. 2.2. |
Переименуйте Лист3 в Продажи.
3. Заполним данными столбец Цена (в у.е.). Для этого введем в ячейку D5
вычисляемое выражение, позволяющее автоматически выбирать нужную цену из созданной ранее таблицы прайс-листа в зависимости от наименования товара, а затем продолжим формулу на все ячейки столбца. В данном случае для вычислений можно использовать встроенную функцию ВПР – «вертикального просмотра», которая будет сравнивать значение ячейки В5 со значениями первого столбца «диапазона просмотра» Прайс-лист!$A$4:$B$13 и в случае совпадения вернет соответствующее значение из второго столбца указанного диапазона:
(D5)= ВПР(В5; Прайс-лист!$A$4:$B$13; 2; 0).
Замечание: последний параметр функции имеет значение 0 (Ложь), что означает поиск точного совпадения искомого значения (В5) в первом столбце диапазона просмотра.
Реализация товаров со склада
|
|
|
|
|
|
Дата: |
05.07.2006 |
|
|
|
|
|
|
Курс: |
26,7 |
№ |
Наименование |
Кол-во |
Цена в у.е. |
Цена в |
Стоимость |
Скидка |
Стоимость с |
п/п |
товара |
шт. |
|
руб. |
партии |
(в %) |
учетом |
|
|
|
|
|
|
|
скидки |
|
|
|
|
|
|
|
|
1 |
Пылесос Philips |
2 |
250 |
6 675р. |
13 350р. |
5% |
12 683р. |
|
|
|
|
|
|
|
|
2 |
Муз.центр Sony |
4 |
87 |
2 323р. |
9 292р. |
0% |
9 292р. |
|
|
|
|
|
|
|
|
… |
… |
… |
… |
|
|
|
|
|
|
|
|
|
|
|
|
5 |
Пылесос Philips |
3 |
250 |
6 675р. |
20 025р. |
10% |
18 023р. |
|
|
|
|
|
|
|
|
Рис. 2.3.
4. Заполните пятый столбец таблицы в соответствии с формулой: Цена (в руб.)=Цена (в у.е.)*Курс.
Замечание: так как для всех товаров курс валют единый (Н3), при написании формулы используйте абсолютную адресацию для ссылки на соответствующую ячейку.
5. Введите формулу расчета стоимости партии для первого товара (Стоим.партии = Кол-во*Цена в руб.) и продолжите ее на все ячейки шестого
столбца.
6. Рассчитаем величину скидки для каждой партии реализованного товара.
Скидка определяется в соответствии с таблицей на листе Скидки в зависимости
от стоимости партии: от 0 руб. до 9 999 руб. |
- 0% |
от 10 000руб. до 19 999руб. - 5%
свыше 20 000 руб. |
- 10% |
Для автоматизации вычисления величины скидки можно воспользоваться функцией ЕСЛИ: (Н3) = ЕСЛИ (G5<Скидки!$A$4; Скидки!$B$3; ЕСЛИ
(G5<Скидки!$A$5; Скидки!$В$4; Скидки!$В$5)).
Замечание: таблица на листе Скидки сформирована таким образом, что для определения величины скидки также может быть использована функция ВПР.
Напишите эту функцию самостоятельно, по аналогии с п.3, учитывая, что в данном случае значение последнего параметра равно 1 (Истина) - поиск ведется приближенно.
7. Введите формулу для расчета стоимости партии со скидкой для первого товара и продолжите ее на все ячейки столбца
(Стоим.партии со скидкой = Стоим.партии-Стоим.партии*Скидка)
8. Под таблицей вычислите общую сумму продаж (функция СУММ),
среднюю стоимость партии (функция СРЗНАЧ), минимальное и максимальное количество товаров в партии (функции МИН и МАКС, соответственно).
9. Ниже вставьте формулы для расчета количества и общей суммы покупок,
сделанных со скидкой.
10. Сохраните рабочую книгу под именем Учет товара.xls и покажите свою работу преподавателю.
Лабораторная работа №3
Тема: Создание сложных связанных таблиц
Цель работы: Научиться создавать таблицы на нескольких листах, проводить сложные вычисления с использованием встроенных функций, абсолютной и относительной адресации. Освоить условное форматирование, функции СУММ,
СРЗНАЧ, ЕСЛИ, СЧЕТЕСЛИ.
Справка:
1. Использование элемента управления формы Флажок.
Меню Вид Панель инструментов Формы. Выбрать Флажок (
) и
поместить на Лист. Правой кнопкой мыши щѐлкнуть по Флажку и из раскрывшегося контекстного меню выбрать «Формат объекта». Перейти на вкладку «Элемент управления» и установить целевую ячейку в «Связь с ячейкой».
Если Флажок установлен, то значение в целевой ячейке будет ИСТИНА, иначе -
ЛОЖЬ.
2. Использование элемента управления формы Счётчик.
Меню Вид Панель инструментов Формы. Выбрать Счётчик (
) и
поместить на Лист. Правой кнопкой мыши щѐлкнуть по Счётчику и из раскрывшегося контекстного меню выбрать «Формат объекта». Перейти на вкладку «Элемент управления» и установить целевую ячейку в «Связь с ячейкой»,
а также Текущее, Минимальное и Максимальное значения и Шаг изменения. В
целевой ячейке будет отображено Текущее значение.
Порядок выполнения работы
1. Создайте новую рабочую книгу и сохраните ее под именем Ведомость.xls. На Листе 1 таблицу Списочный состав группы и заполните ее данными 10
строк таблицы, как показано на рис.3.1.
Для заполнения столбца №п/п используйте прогрессию
(Правка Заполнить Прогрессия). Первый лист книги Лист 1 переименуйте в Список группы.
2. Переименуйте Лист 2, назвав его Математика. На этом листе создайте таблицу
Экзаменационная ведомость и оформите, как показано на рис.3.2. Заполните таблицу данными, следуя указаниям:
а) номер группы и список студентов вставьте как ссылку на лист Список группы, так чтобы при изменении данных на листе Список группы
автоматически менялись данные на листе
Математика;
б) поле Балл заполните произвольно числами от 0 до 5. Установите формат числа - один знак после запятой;
в) графа Оценка должна содержать формулу,
которая в зависимости от балла выдаѐт значение в соответствии с таблицей 3.1. (используйте функцию ЕСЛИ);
Табл. 3.1.
Балл |
Оценка |
Условное |
|
форматирование |
|||
|
|
||
от 4,5 и выше |
отл. |
- |
|
от 3,5 до 4,4 |
хор. |
- |
|
от 2,5 до 3,4 |
удовл. |
зелѐный |
|
меньше 2,5 |
неуд. |
красный курсив |
Рис. 3.1.
Рис. 3.2.
г) к столбцам Балл и Оценка примените условное форматирование
(Условие и Формат см. в таблице 3.1).
д) ниже таблицы проведите автоматический подсчѐт числа человек с оценками: «отлично», «хорошо», «удовлетворительно» и «неудовлетворительно»
(используйте функцию СЧЕТЕСЛИ).
3.Повторите п.2, создав на следующих двух листах экзаменационные ведомости ещѐ по 2 предметам. Переименуйте соответствующие листы так, чтобы их имена совпадали с названиями предметов.
4.Создайте лист Ведомость начисления стипендии и разместите на нем одноимѐнную таблицу (см. рис.3.3). Все данные на этом листе должны заполняться автоматически через формулы и ссылки:
Рис. 3.3.
а) номер группы и список студентов вставьте как ссылки на соответствующие ячейки листа Список группы, для заполнения столбца № п/п
используйте прогрессию (Правка Заполнить Прогрессия);
б) столбцы Предметы заполните ссылками на данные таблиц
Экзаменационные ведомости одноименных листов;
в) в столбце Средний балл должна получиться средняя оценка по всем предметам с точностью до второго знака после запятой;
г) в графе Соц.стип. отмечается, нуждается ли студент в социальной стипендии. Для реализации запроса используйте элемент управления формы
Флажок 
: если галочка установлена, то студенту выплачивается соц.стип.
Целевой ячейкой является ячейка из графы Соц.стип. ; д) графа Стипендии по успеваемости заполняется без учѐта
социальной стипендии. Стипендия начисляется в процентах от базовой стипендии
(БС) (см. рис.3.3). Если средний балл меньше 3,5, то стипендия равна нулю.
Формат числа – денежный; е) в графе Начисленная стипендия вычислите суммарную величину
Стипендии по успеваемости и Социальной стипендии, если она есть;
ж) размеры базовой стипендии (БС) и социальной стипендии
(Соц.стипендия) должны задаваться элементом управления формы Счётчик 
.
Целевой ячейкой являются ячейки со значениями БС и Соц.стипендии,
соответственно.
5.В конце таблицы подсчитайте суммарные затраты на выплату стипендий в группе, а так же сколько человек в группе получают отличную, хорошую и пониженную стипендии. А так же, сколько человек получают социальную стипендию (независимо от стипендии по успеваемости).
6.Сохраните рабочую книгу под именем Ведомость.xls и покажите свою работу преподавателю.
Лабораторная работа №4
Тема:Наглядное представление данных.
Цель работы:Научиться представлять табличные данные в виде диаграмм и
графиков различных типов.
Порядок выполнения работы
1. В новой рабочей книге создайте таблицу Годовой бюджет (см. рис.4.1.).
Заполните данными первую строку таблицы (Продажа фруктов) и рассчитайте
значения остальных ячеек, вставив формулы в соответствии со следующими
соотношениями:
Торговые издержки – 30% от продажи фруктов; Затраты на маркетинг – 10% от продажи фруктов; Накладные расходы – 20% от продажи фруктов.
Рассчитайте Общую сумму расходов (сумма по Торговым издержкам,
Затратам на маркетинг и Накладным расходам) и Чистую прибыль
(разностьмежду Продажей фруктов и Общей суммой расходов).
Рис. 4.1.
2. Постройте несколько диаграмм для наглядного представления
полученных данных.
а) Диаграмма № 1: поквартальные расходы по трем категориям (торговые издержки, затраты на маркетинг, накладные расходы) в виде объемной гистограммы. Выделите диапазон данных А5:Е7, вызовите Мастер диаграмм и
укажите нужный тип гистограммы (см. рис.4.2.).
Замечание: чтобы вывести названия кварталов в качестве меток оси Х
следует на втором шаге построения диаграммы перейти на вкладку Ряд и в поле Подписи по Х вести ссылку на диапазон В3:Е3.
б) Диаграмма № 2: продажа фруктов по кварталам. Постройте плоскую гистограмму по диапазону А4:Е4 (чтобы на оси Х отображались названия кварталов следует выделить диапазон А3:Е4). Оформите построенную гистограмму как показано на рис.4.3.
Замечание: изменить любой элемент диаграммы можно в диалоговом окне форматирования, вызвав его двойным щелчком мыши по заданному объекту.
в) Диаграмма № 3: измените тип диаграммы №2 на круговую. Получив круговую диаграмму, следует вставить подписи данных, указав процент продаж в каждом квартале (см. рис.4.4.).
г) Диаграмма № 4: скопируйте диаграмму №3 и преобразуйте в гистограмму. На диаграмме получить поквартальную продажу фруктов и чистую прибыль, для этого к данным диаграммы № 3 следует добавить новые данные: выделить ячейки А9:Е9, выбрать Правка Копировать; щелкнуть на одном из столбцов гистограммы, выбрать Правка Специальная вставка.
Рис. 4.2. |
Рис. 4.3. |
д) Диаграмма № 5: добавьте к диаграмме № 4 легенду (подписи рядов |
|
данных). Для этого необходимо правой кнопкой мыши щелкнуть по области
диаграммы, выбрать в контекстном меню Параметры диаграммы.
е) Диаграмма № 6: измените Диаграмму № 5 так, чтобы данные по продаже фруктов отображались в виде гистограммы, а чистая прибыль – в виде графика (нестандартный тип диаграммы). Для этого следует изменить тип диаграммы, выбрав на вкладке Нестандартные тип График|гистограмма.
Оформить полученную диаграмму как показано на рис.4.5.
Рис. 4.5. |
|
|
|
Рис. 4.4. |
|
|
2 |
2 |
, где |
7,5 x 7,5 |
x 1,5 |
. |
|
3. Постройте график поверхности z=x -y |
|
|
|
|
||
|
|
|
5 |
y 5, |
y 1 |
|
а) Для построения графика поверхности необходимо сначала создать таблицу данных. Заполните строку 1 значениями переменной x: введите в ячейку В1 число -7,5 (левую границу диапазона), выделите ячейки В1:L1 и выберите Правка Заполнить Прогрессия, указав Шаг 1.5. Аналогично заполните ячейки А2:А12 значениями переменной у - начальное значение -5, Шаг 1. Введите формулу для вычисления z в первую ячейку таблицы: (B2)=B$1^2-$A2^2, и продолжите ее на все ячейки с помощью маркера автозаполнения. До построения графика ячейку А1 следует оставить пустой.
б) Выделите диапазон А1:L12 и постройте график поверхности с помощью Мастера диаграмм. В итоге должен получиться гиперболический параболоид.