Рассмотрено на заседании
предметно–цикловой комиссии математических, естественно–научных и
общепрофессиональных дисциплин.
Протокол №2 от 8.10.2014 г.
Данные
методические указания предназначены для студентов всех специальностей БОУ СПО
ВО «Вологодский строительный колледж» при выполнении практических работ по теме
«Табличный процессор Microsoft Excel 2013» при изучении дисциплин:
«Информатика», «Информационные технологии в профессиональной деятельности»,
«Компьютерное сопровождение профессиональной деятельности».
Методические указания содержат: введение, теоретические
сведения, задания для закрепления, включающие в себя подробный порядок
выполнения, задания для отработки, приобретенных знаний и практических навыков
работы с электронными таблицами, задания профессиональной направленности.
Методические указания могут быть
рекомендованы к использованию студентами и преподавателями БОУ СПО ВО
«Вологодский строительный колледж».
Авторы:
преподаватель информатики и ИКТ БОУ СПО ВО
"Вологодский строительный колледж Т.
А. Габриэлян
Рецензент: заместитель
директора по учебно-методической работе и качеству образования Санкт-Петербургского
государственного экономического университета, филиал в г. Вологде, преподаватель
информатики высшей категории И. А. Евграшина
Оглавление
Введение
|
3
|
Диаграммы и графики
|
4
|
Обработка данных
|
21
|
Практические задания
|
36
|
Список литературы и информационных источников
|
48
|
Приложения
|
50
|
Требования
работодателей к современному специалисту, а также федеральный государственный
образовательный стандарт СПО ориентированы, прежде всего, на умения
самостоятельной деятельности и творческий подход к специальности. Профессиональный
рост специалиста, его социальная востребованность, как никогда зависят от
умений проявить инициативу, решить нестандартную задачу, от способности к
планированию и прогнозированию самостоятельных действий. Стратегическим
направлением повышения качества образования в этих условиях является
оптимизация системы управления учебной работой обучаемых, в том числе и их
самостоятельной работой.
В
современный период востребованы высокий уровень знаний, академическая и
социальная мобильность, профессионализм специалистов, готовность к
самообразованию и самосовершенствованию.
Табличный
процессор Microsoft Office Excel — удобное и мощное средство для создания
таблиц и графиков, анализа данных, проведения расчетов. В виде файлов формата
Excel создаются финансовые отчеты, распространяются прайс-листы, оформляются
расчеты в различных сферах деятельности, в том числе и в строительстве. Excel
2013 — на данный момент самая последняя версия популярного табличного
процессора.
В
методических указаниях рассмотрен интерфейс последней версии Microsoft Excel
2013, освещены вопросы организации рабочих книг Excel, форматирования листов,
использования разнообразных формул для вычислений, поиска необходимой
информации. Теоретический материал сопровождается практическими примерами и
заданиями для самостоятельного освоения материала.
Целью
методических указаний является подробное освещение последней версии Microsoft
Excel, решение профессиональных задач средствами Microsoft Excel.
Начало работы с Microsoft Excel 2013
1. Диаграммы
и графики
Построение
диаграмм
Диаграммы
создаются по данным, введенным в таблицы на листах.
Инструменты
для работы с диаграммами собраны в группе Диаграммы,
расположенной на вкладке Вставка
(щелкните мышью на названии вкладки Вставка,
чтобы перейти на нее).
В
этой группе приведены различные виды диаграмм. Если щелкать мышью на кнопках с
названиями типов диаграмм, будут раскрываться меню, где можно выбрать
конкретную модификацию диаграммы выбранного типа.
Например,
создадим объемную гистограмму для данных таблицы, представленной на рис. 1.
Рис.
1 Исходные данные для построения диаграммы
Предварительно
нужно выделить данные, которые будут участвовать в построении гистограммы,
вместе с их заголовками. На рис. 1 выделены восемь ячеек с числовыми данными и
заголовки.
Как
показано на рис. 1, в группе Диаграммы
щелкаем мышью на стрелке в правой части кнопки Гистограмма
и
в открывшемся при этом подменю выбираем один из вариантов раздела Объемная
гистограмма.
В
результате на листе появится прямоугольная область с помещенной в нее
диаграммой. Перетаскивая ее мышью, установите прямоугольную область там, где по
вашей задумке должна располагаться диаграмма на листе (рис. 2).
Рис.
2 Размещение диаграммы
Размеры
области с диаграммой можно менять. По сторонам и углам рамки вокруг области
диаграммы есть маркеры изменения размера. При наведении на них указателя мыши
он принимает вид двунаправленной стрелки. Если в этот момент нажать левую
кнопку мыши и, не отпуская ее, перемещать указатель мыши, то размеры области с
диаграммой изменятся.
В
области диаграммы, кроме самой диаграммы, присутствуют надписи, поясняющие,
какие именно данные обозначены тем или иным цветом или узором. Эти надписи
называют легендой. Для отображения легенды тоже можно задавать различные
параметры. Для этого щелкните правой кнопкой мыши на легенде в области
диаграммы и в появившемся контекстном меню выберите пункт Формат
легенды
(рис. 3).
В
результате в правой части окна Excel 2013 становятся доступными параметры
легенды. Под словами Формат легенды
находится раскрывающийся список категорий. Все варианты настройки выбранной
категории доступны в правой части окна. Например, на рис. 4 показаны варианты
настройки для категории Параметры
легенды.
С их помощью можно задать положение легенды относительно диаграммы.
Если
вы щелкнете мышью на стрелке вниз справа от слов Параметры
легенды,
вы увидите весь список категорий.
Для
завершения работы с окном настройки легенды щелкните мышью на кнопке закрытия
(в виде крестика) справа от слов Формат
легенды.
Рис.
3 Настройка формата легенды
Для
некоторых видов диаграмм, например для гистограмм, можно настроить особенности
отображения и размерность координатных осей. Для этого щелкните правой кнопкой
мыши на изображении любой оси диаграммы и в появившемся контекстном меню
выберите пункт Формат оси.
В правой части окна Excel 2013 появится панель Формат
оси,
где собраны различные элементы управления для настройки по категориям.
Рис.
4 Параметры легенды
Если
вы решили, что выбрали неподходящий тип диаграммы, всегда можно изменить его.
Для этого, если область диаграммы не выделена, щелкните мышью на диаграмме.
После этого нужно щелкнуть мышью на кнопке Изменить
тип
диаграммы
на вкладке Конструктор.
При этом откроется окно, где представлены на ваш выбор различные типы диаграмм.
Графики
В
Excel 2013 есть еще одна группа инструментов для наглядного представления
изменений в данных, которые называются спарклайны (в предыдущей версии
Excel они назывались инфокривые). Они очень похожи на гистограммы,
однако рассчитаны на размещение внутри одной ячейки. Это очень удобно, чтобы
показать, например, изменение какого-то одного показателя во времени.
Все
эти инструменты собраны в группе Спарклайны
на вкладке Вставка.
Для
вставки, например, спарклайн-графика нужно выделить ячейку для вставки и
щелкнуть мышью на кнопке График
в группе Спарклайны
на вкладке Вставка.
Появится окно, где нужно указать исходные данные для построения графика, то
есть диапазон ячеек (рис. 5). Можно ввести адреса ячеек в поле Диапазон
данных
вручную или выделить нужные ячейки мышью (с нажатой левой кнопкой мыши выделить
те ячейки, по значениям которых нужно строить график).
Рис.
5 Вставка графика
Щелкните
мышью в этом окне на кнопке OK.
График будет помещен в выбранную вами ячейку (рис. 6).
Рис.
6 Построенный график
При
выделении ячейки с графиком становится доступной вкладка Конструктор
(в верхней части окна Excel 2013). С помощью инструментов этой панели можно
менять внешний вид графика. На рис. 7 показан режим вывода на графике
максимального и минимального значений.
Рис.
7 Изменение параметров парклайна
При
изменении высоты или ширины ячейки, где расположен график, меняется и его
масштаб.
Для
удаления графика из ячейки щелкните правой кнопкой мыши на этой ячейке, в
появившемся контекстном меню раскройте подменю Спарклайны
и выберите пункт Очистить выбранные
спарклайны,
как показано на рис. 8.
Рис.
8 Удаление графика
Порядок
выполнения:
1. Выделить
диапазон A1:E2;
2. Вставка→Гистограмма→Объемная
гистограмма (а);
3. Конструктор→Строка/столбец
(поменять ряды местами);
4. Конструктор→Название
диаграммы → Над диаграммой;
5. Конструктор
→ Подписи данных → Показать (Дополнительные параметры подписей данных);
Задание 2 (Лист 2)
Создайте
таблицу, заполните данными, постройте график, отображающий динамику изменения
стоимости квадратного метра жилья.
1. Выделить
все ячейки, включая заголовки;
2. Вставка
→Диаграммы →График→ График с маркерами
По шкале Х будет
отложена дата, по шкале Y — денежные единицы.
3. У
получившегося графика есть один существенный недостаток — слишком большой
диапазон значений по вертикальной оси, вследствие чего изгиб кривой виден
нечетко, а графики оказались прижаты друг к другу. Чтобы улучшить вид графика,
необходимо изменить промежуток значений, отображаемых на вертикальной шкале.
Поскольку
даже самая низкая цена в начале полугодового интервала превышала 1000, а самая
высокая не превысила отметку 2000, имеет смысл ограничить вертикальную ось
этими значениями. Выполните правый щелчок на области оси Y и задействуйте
команду Формат оси
4. Далее
оформим диаграмму
a) Конструктор
→ Стили диаграмм → Стиль 4;
b) Легенда → Добавить
легенду снизу;
c) Выделить
область построения диаграммы →Формат → Заливка фигуры →Градиентная → Выбрать
цвет;
d) Макет → Название
диаграммы
Задание3
(Лист 3)
В
электронной таблице Microsoft Excel создайте таблицу, заполните данными и
вычислите с помощью автосуммирования S – сумму по
столбцам, с помощью функции СУММ – сумму по строкам. Постройте
диаграмму по диапазонам: В1:Е1 и В9:Е9; для выделения не смежных диапазонов,
используйте клавишу Ctrl
Задание4
(Лист 4)
Создайте таблицу,
выполните вычисления.
При оформлении
таблицы установить:
1. Выравнивание
данных по центру столбца;
2. В столбце
«Плотность населения» записать формулы для вычислений, задать формат числа с
одним знаком после запятой;
3. По данным
таблицы построить диаграммы:
a) Круговую –
по столбцу «Всего земель»
b) Цилиндрическую –
по столбцу «Плотность населения»;
При оформлении диаграмм убрать линии
сетки, записать заголовок диаграммы, установить подписи данных и легенду.
Задание 5 (Лист 5)
В
электронной таблице Microsoft Excel создайте таблицу, заполните данными,
постройте по 2 и 3 столбцам диаграмму
год
|
приход
|
расход
|
1992
|
200
|
150
|
1993
|
350
|
230
|
1994
|
410
|
250
|
1995
|
200
|
180
|
Задание 6 (Лист 6)
Составить электронную таблицу «Справочник
расценок», содержащую поля: Код работы, Наименование работы,
Расценка (руб./час), Объем работы (час), Стоимость заказа. Стоимость
заказа должна рассчитываться. Построить гистограмму распределения стоимости
заказа по кодам работ.
Код работы
|
Наименование
|
Расценка
|
Объём работы
|
Стоимость заказа
|
1
|
Установка ОС
|
1000
|
12
|
12000
|
2
|
Установка ПО
|
300
|
11
|
3300
|
3
|
Восстановление информации с носителя
|
500
|
1
|
500
|
4
|
Диагностика ПК
|
500
|
3
|
1500
|
5
|
Удаление вредоносных программ
|
500
|
1
|
500
|
Задание 7 (Лист 7)
Постройте график
функции , для построения графика
заполните таблицу.
1.
В ячейку B1
ввести начальное значение -5, в ячейку С2 ввести формулу: =B1+0,5.
Скопировать формулу в остальные ячейки строки.
2.
В ячейку B2
ввести формулу:. =СТЕПЕНЬ(СТЕПЕНЬ(B1;2)*(B1+3);1/3)
3.
Скопировать формулу в остальные ячейки
строки.
4.
Установить границы таблицы.
5.
Выделить всю таблицу (диапазон A1:V2);
6.
Вставка →Диаграммы →Точечная → Точечная с
гладкими кривыми;
7.
Макет → Название диаграммы, Измените
название, вставив объект формулу;
Задание 8 (Лист 8)
Создайте и
заполните таблицу. Рассчитайте с помощью функций:
· СУММ,
столбец «Итого»;
· СРЗНАЧ,
среднее значение премии по месяцам;
· МИН, МАКС,
минимальную и максимальную премии
Постройте
диаграммы.
Премиальные выплаты сотрудникам ООО
"Карат"
|
ФИО
|
Сентябрь
|
Октябрь
|
Ноябрь
|
Декабрь
|
Итого
|
Борсовкин А.
Л.
|
2 000,00р.
|
2 300,00р.
|
1 000,00р.
|
3 300,00р.
|
8 600,00р.
|
Веселов П. Р.
|
2 500,00р.
|
2 875,00р.
|
1 200,00р.
|
3 500,00р.
|
10 075,00р.
|
Гордеев Н. Г.
|
4 000,00р.
|
4 600,00р.
|
1 500,00р.
|
5 000,00р.
|
15 100,00р.
|
Ершов А. Ф.
|
1 200,00р.
|
1 380,00р.
|
1 700,00р.
|
2 300,00р.
|
6 580,00р.
|
Корнеев Г. Л.
|
800,00р.
|
920,00р.
|
900,00р.
|
1 500,00р.
|
4 120,00р.
|
Малышев Ф. Д.
|
4 300,00р.
|
4 945,00р.
|
2 100,00р.
|
3 800,00р.
|
15 145,00р.
|
Порозов Е. О.
|
5 200,00р.
|
5 980,00р.
|
3 150,00р.
|
6 000,00р.
|
20 330,00р.
|
|
|
|
|
|
|
Ср.знач.премии
|
2 857,14р.
|
3 285,71р.
|
1 650,00р.
|
3 628,57р.
|
|
Мин.премия
|
800,00р.
|
920,00р.
|
900,00р.
|
1 500,00р.
|
|
Макс.премия
|
5 200,00р.
|
5 980,00р.
|
3 150,00р.
|
6 000,00р.
|
|
Построение
графиков
Задание9 (Лист 9)
Построить
графики функций y=x2 и y=0,5x+2.
Порядок
выполнения:
1. Создадим
таблицу для первой функции.
a) В ячейку
В3 введем число -6, в ячейку С3 формулу: =В3+0,5 и скопируем формулу в ячейки D3:Z3;
b) В ячейку В4 введем
формулу: =СТЕПЕНЬ(B3;2), скопируем в ячейки С4:Z4;
2. Создадим
таблицу для второй функции.
3. Выделить
диапазон В3:Z4
4. Вставка → Точечная
→ С гладкими
кривыми
5. График
будет иметь вид:
6. Конструктор
→ Выбрать данные →Элементы легенды (ряды) →Изменить →
7. Изменяем
параметры горизонтальной оси: Макет → Оси →Основная горизонтальная ось →
Дополнительные параметры основной горизонтальной оси → Меняем переключатель с
«авто» на «фиксированное», задаем значение.
Устанавливаем переключатель: Значение оси
0,0
8. Изменяем
параметры вертикальной оси аналогично.
9. Добавляем
вторую функцию на график
Конструктор
→ Выбрать данные →Элементы легенды (ряды) →Добавить →
а) б)
в)
10.
Название:
Конструктор → Название диаграммы → Над диаграммой;
11.
Устанавливаем
линии сетки: Конструктор → Добавить элемент диаграммы → Сетка → Вертикальные
линии сетки → Основные линии сетки
Задание 10 (Лист 10)
Построить
графики функций y1= x 2 и y2=
x 3 на интервале [- 3 ; 3] с шагом 0,5.
Порядок
выполнения:
1. Заполнить
таблицу значений;
2. Выделить
таблицу и указать тип диаграммы Точечная.
3. Выбрать
формат точечной диаграммы с гладкими кривыми.
Задание 11
Построить
графики функций и на интервале [- 3 ; 3] с
шагом 0,5.
Задание 12
Построить
графики функций и на интервале [-
0.5 ; 9] с шагом 0,5.
Задание 13
Построить
графики функций и на интервале [-
5 ; -0.5] с шагом 0,5.
Задание 14
Построить
графики функций и на интервале [-
0.5 ; 5] с шагом 0,5.
Задание 15
Построить
графики функций , , на интервале [-3
;3] с шагом 0,3.
Задание 16
Теоретические
сведения:
I.
Сортировка
списков. Виды сортировки. Технология сортировки
Под списками в EXCEL понимаются таблицы, содержащие
однородную информацию. Чтобы превратить таблицу EXCEL в список, следует
присвоить столбцам имена, каждый из которых находится в одной клетке. В списке,
который иногда называют базой данных EXCEL, строки таблицы называются записями,
а столбцы – полями. При создании списка на рабочем листе EXCEL необходимо
соблюдать следующие основные правила:
1) на одном рабочем листе не следует помещать более
одного списка;
2) следует отделять список от других данных рабочего
листа хотя бы одним свободным столбцом или одной свободной строкой;
3) имена полей списка должны располагаться в первой
строке таблицы, EXCEL использует эти имена при создании отчётов, в поиске и
сортировке данных.
4) каждый столбец списка должен содержать во всех
строках однотипные данные.
При работе со списками часто возникает необходимость
сортировки его строк в заданном порядке. Это можно выполнить с помощью команды
Сортировка, которая входит в меню Данные, предварительно выделив весь список с
заголовками столбцов, кроме итоговых строк таблицы, если они присутствуют.
Включение заголовков столбцов в область выделения облегчает настройку
сортировки, поскольку эти имена можно использовать в качестве ключей
сортировки. В противном случае ключами сортировки будут стандартные имена
столбцов таблицы EXCEL. Команда Сортировка открывает одноименное диалоговое
окно, где задаются параметры сортировки.
Вместо простого порядка сортировки по возрастанию или
по убыванию можно задать пользовательский порядок сортировки по заданному
ключу. В этом случае не применяются обычные правила сортировки в алфавитном или
числовом порядке. Например, с помощью пользовательского порядка сортировки
можно отсортировать список по месяцам календаря: январь, февраль, март, апрель
и т.д.
Пример: Имеется таблица
«Премиальные выплаты сотрудникам ООО «Карат»». Произвести сортировку в столбце B выплат по
возрастанию, а в столбце А – фамилий в обратном алфавитном
порядке.
Порядок
выполнения:
1. Выделяем
ячейки B2:B9
2. Главная ®®
3. В
появившемся окне выбираем ®
Результат:
4. Выполним
сортировку в обратном алфавитном порядке в столбце А, выделим
столбец «ФИО» (A2:A9)
5. Главная
®®
®
Результат:
II.
Фильтрация
данных
Фильтрация
– это скрытие лишних данных в таблице, при этом на экране остаются только те
записи, которые удовлетворяют заданным критериям.
Суть ее в
том, чтобы отобрать из списка все строки (записи), удовлетворяющие определенным
условиям. Условий может быть много, они могут быть простыми и сложными,
связанными друг с другом или независимыми. Существует несколько способов
фильтрации списков в Excel.
Способ 1.
Автофильтр
Отфильтровать список автофильтром - значит скрыть все
строки за исключением тех, которых удовлетворяют заданным условиям отбора. Для
выполнения такой операции необходимо выделить любую ячейку списка и, выбрать в
меню
В первой строке, содержащей заголовки столбцов,
появятся кнопки со стрелками - кнопки автофильтра .
Для выборки записей необходимо щелкнуть по кнопке
автофильтра в заголовке нужного столбца и выбрать из выпадающего списка то, что
необходимо отфильтровать.
После фильтрации обратите внимание на номера
выведенных строк - Excel скрыл все строки, не удовлетворяющие заданному
условию, а номера отфильтрованных отобразил синим цветом, чтобы напомнить
пользователю, что в данный момент он видит неполный список.
Из исходной
таблицы получим следующую таблицу:
Способ 2.
Пользовательский автофильтр
Можно использовать
автофильтр для выборки записей по более сложным условиям. Допустим, необходимо
выбрать из списка строки, где зарплата сотрудника лежит в диапазоне от 10000 до
15000 или меньше/больше определенной величины.
1. Выделить
диапазон F2:F9
2.
3. ®®
Из исходной
таблицы получим следующую таблицу:
Способ 3.
Расширенный фильтр
В
отличие от Автофильтра и Пользовательского автофильтра - Расширенный
фильтр практически не имеет ограничений на количество условий, налагаемых
на список. Но требует некоторых подготовительных операций. А именно:
- В
любую свободную область (лучше всего вставить над списком несколько пустых
строк) нужно скопировать из "шапки" списка заголовки столбцов,
по которым будут вводиться условия.
- Затем,
ниже скопированных заголовков в пустые ячейки вводятся условия для
фильтрации. Условия, введенные в ячейки одной строки Excel, будет
связывать логическим "И", а в ячейки разных строк - логическим
"ИЛИ".
Задания
профессиональной направленности
Задание 1 (дисциплина
«Строительные конструкции», специальность 270802 (СиЭЗиС))
Подобрать
сечение стропильной ноги из бревна, если пролет l=4,2
м, шаг стропил a=1,2 м, расчетная
нагрузка на 1 м2-qм2=1991
Н/м, нормальная нагрузка 1 м2-qnм2=1436,
уклон кровли -a=25°,
обрешетка из досок сечением b×h=30×150
мм, коэффициент надежности по назначению gn=0,95,
коэффициент gf=1,1,
p=500,
E=107.Диаметр
стропильной ноги = 16 см.
Создадим
таблицу:
Для
вычисления функции COS,
аргумент функции необходимо перевести в радианы, т. е. угол (град.) умножить на
коэффициент
Формулы:
1.
Полная расчетная нагрузка на 1 пог.м
горизонтальной проекции стропильной ноги q;
Полная нормативная нагрузка на 1 пог.м
горизонтальной проекции стропильной ноги qn
;
С20:=ОКРУГЛ(((C7*C6+C14*(ПИ()*СТЕПЕНЬ(C16/100;2))/(4*COS(C9*ПИ()/180))*C13*10)*C12)/1000;1)
C21:
=ОКРУГЛ((C8*C6+C14*(ПИ()*СТЕПЕНЬ(C16/100;2))/(4*COS(C9*ПИ()/180))*10)/1000;1)
2.
Максимальный изгибающий момент
С22:
=ОКРУГЛ((C20*СТЕПЕНЬ(C5;2))/8;1)
3.
Требуемый момент сопротивления из условий
прочности на изгиб
С23:
=ОКРУГЛ(C22/C18*1000;0)
4.
Требуемый диаметр бревна
С24:
=ОКРУГЛ(СТЕПЕНЬ(10*C23;1/3);1)
5.
Расчётный диаметр в середине пролёта
С25:
=ОКРУГЛ(C17+0,8*(C5/(2*COS(C9*ПИ()/180)));2)
6.
Геометрические характеристики отёсанного
бревна
С26:
=ОКРУГЛ(0,096*СТЕПЕНЬ(C25;3);0)
С27:
=ОКРУГЛ(0,0476*СТЕПЕНЬ(C25;4);0)
7.
Нормальное напряжение
С28:
=ОКРУГЛ(C22/C26*1000;1)
8.
Сравниваем
С29:
=ЕСЛИ(C28<=C18;"Прочность по нормальному сечению обеспечена, сечение
выбрано верно"; "Прочность по нормальному сечению не обеспечена,
сечение выбрано неверно")
9.
Максимальный прогиб в середине пролёта
С30:
=ОКРУГЛ((5*C21*СТЕПЕНЬ(C5;4))/(384*C15*C27/10000000*COS(C9*ПИ()/180))*1000;1)
10.
Предельно допустимый прогиб
С31:
=(C5-3)/3*50
С32:
=ОКРУГЛ(C5/(150+C31)*100;1)
11.
Проверяем
С33:
=ЕСЛИ(C30<C32;"Жёсткость обеспечена" ;"Жёсткость не
обеспечена, необходимо увеличить диаметр бревна (d)")
В
этом случае необходимо взять больший диаметр d0.
Задание
1.2
Составить
расчётную таблицу для решения задачи № 1 «Проверить сечение обрешетки под
кровлю из металлочерепицы», с. 6, [5]
Задание
1.3
Составить
расчётную таблицу для решения задачи № 3 «Подобрать сечение двухпролётной
стропильной ноги», с. 14, [5]
Задание
1.4
Составить
расчётную таблицу для решения задачи № 4 «Проверить сечение стропильной ноги на
прочность и жёсткость», с. 16, [5]
Дополнительные
задания (область применения: профессиональная деятельность)
Задание 2 Составить
таблицы следующего вида на разных листах:
Справочник
автомашин (лист 1)
Государственный
номер
|
Марка
автомобиля
|
Средний
месячный пробег (K)
|
|
|
|
Справочник стоимости (лист 2)
Марка
автомобиля
|
Начальная
стоимость (S)
|
|
|
Заполнить первые две таблицы (марка автомобиля может
повторяться). С помощью справочных таблиц должна автоматически заполняться
результирующая таблица.
Расчет
стоимости автомобиля с учетом амортизации (лист 3)
Государственный
номер
|
Сумма
амортизационных отчислений в месяц (А)
|
Стоимость
с учетом амортизации (B)
|
|
|
|
Сумма амортизационных отчислений за месяц
рассчитывается по формуле:
A = S*K /
10000*10/100
Стоимость с учетом амортизации рассчитывается по
формуле:
B = S – А
Построить диаграмму распределения стоимости с учетом
амортизации по маркам автомобилей, автоматически корректируемую при изменении
данных в исходной таблице.
Вывести государственный номер машины, имеющий
максимальную стоимость, минимальную стоимость.
Порядок
выполнения:
1. Для
автоматизации заполнения необходимо при заполнении 2 таблицы делать ссылку на
лист1: А2:=Лист1!В2, далее скопировать формулу в остальные
ячейки столбца;
2. При
заполнении 3 таблицы делать ссылку на лист 1 для заполнения столбца
«Государственный номер»:
А2:=Лист1!А2 и ссылки
на листы 1 и 2 для использования исходных данных в вычислениях;
3. Переименовать
листы: ПЩ по ярлыку листа→Переименовать→Ввести новое название листа
Лист 1 →
«Справочник автомашин»
Лист 2 →
«Справочник стоимости»
Лист 3 →
«Расчет стоимости автомобиля»
4. В ячейку
В2 (Лист 3) ввести формулу: =
'Справочник стоимости'!B2*'Справочник автомашин'!D2/10000*10/100. Скопировать
формулу в остальные ячейки (В3:В6);
5. В ячейку
С2 (Лист 3) ввести формулу: = 'Справочник стоимости'!B2-'Расчет стоимости автомобиля'!B2.
Скопировать формулу в остальные ячейки (С3:С6);
6. В ячейку
С8 ввести формулу для определения максимальной стоимости а/м;
7. В ячейку
С9 ввести формулу для определения номера а/м с максимальной стоимостью:
=ИНДЕКС(A2:C6;ПОИСКПОЗ(МАКС(C2:C6);C2:C6;0);1);
8. Диаграмма:
1) Выделить диапазон
С1:С6 (столбец);
2) Вставка →Диаграмма
→ Все типы диаграмм → Линейчатая → Горизонтальная цилиндрическая с
группировкой;
3) Удалить легенду
(выделить → Delete);
4) Редактировать
название: выделить название, редактировать;
5) Изменить подписи
оси: Конструктор → Выбрать данные → Подписи горизонтальной оси (категории) →
Изменить → Выделить данные столбца без заголовка «Марка а/м» на листе2 (А2:А6);
6) Изменить стиль
диаграммы: Конструктор → Стиль 35
7) Подписать точки данных:
Макет → Подписи данных → Показать
Образец выполнения:
Лист
1 «Справочник автомашин»
Лист
2 «Справочник стоимости»
Лист
3 «Расчет стоимости автомобиля»
Диаграмма
Задание 3
Составить
таблицы следующего вида для 5 а/м:
Справочник
грузоперевозки
Код
марки автомобиля
|
Марка
автомобиля
|
Масса
груза M
|
Расстояние
L
|
|
|
|
|
Справочник
автомашин
Марка
автомобиля
|
Стоимость
1т.км (N)
|
|
|
Заполнить первые две таблицы (марка автомобиля может
повторяться). С помощью справочных таблиц должна автоматически заполняться
результирующая таблица.
Стоимость
перевозок
Код
марки автомобиля
|
Стоимость
перевозки S
|
|
|
Стоимость перевозки рассчитывается по формуле: S= N*
M* L
Вывести марку автомобиля, имеющую максимальную
стоимость перевозки, минимальную стоимость перевозки.
Задание 4
Составить
таблицы следующего вида для 5 наименований:
Сроки проведения
работ
Наименование
работы
|
Дата
начала
|
Дата
окончания
|
|
|
|
Справочник
характеристик работ
Наименование
работы
|
Группа
|
Бригада
|
Нормативный
срок
|
|
|
|
|
Заполнить
первые две таблицы. При заполнении второй таблицы следует учесть, что группа
товара и бригада могут повторяться. С помощью справочных таблиц
должна автоматически заполняться результирующая таблица.
Расчетные
данные по опережению и отставанию в проведении работ
Наименование
работы
|
Фактическая
продолжительность, дн.
|
Отставание,
дн.
|
Опережение,
дн.
|
|
|
|
|
Построить
объемную столбиковую диаграмму отставания или опережения по бригадам, которая
должна автоматически корректироваться при изменении данных в исходной таблице.
Вывести наименования групп работ (столярные, сантехнические и т.п.) с
максимальным отставанием.
Задание 5
Составить
таблицы следующего вида:
Справочник
расценок
Код
работы
|
Наименование
работы
|
Расценка
(руб/ед)
|
|
|
|
Справочник бригад
Табельный
номер
|
Фамилия
|
Код
бригады
|
|
|
|
Справочник,
выполнения работ
Табельный
номер
|
Код
бригады
|
Код
работы
|
Выполненный
объем
|
КТУ
|
|
|
|
|
|
Заполнить первые три таблицы (код бригады и код работ
могут повторяться). КТУ (коэффициент трудового участия) может иметь значения от
0 до 1. С помощью справочных таблиц должна автоматически заполняться
результирующая таблица.
Ведомость
начислений
Табельный
номер
|
Начислено
сдельно
|
Начислено
КТУ
|
Всего
|
|
|
|
|
Расчет
«Начислено КТУ» выполнить по формуле:
Начислено
КТУ= Премия на бригаду * КТУ рабочего/S
КТУ бригады
Вывести
фамилии рабочих, имеющих минимальный КТУ.
Список литературы и информационных источников:
1.
ГОСТ 7.32-2001 «Отчет о
научно-исследовательской работе. Структура и правила оформления»
2.
ГОСТ 7.1-2003 «Библиографическая запись.
Библиографическое описание. Общие требования и правила составления»
3.
ГОСТ 7.80-2000 «Библиографическая запись.
Заголовок. Общие требования и правила составления»
4.
ГОСТ 7.82—2001 «Библиографическая запись.
Библиографическое описание электронных ресурсов»
5.
Лазарев Д. Презентация: Лучше один раз
увидеть!– М.: Альпина Бизнес Букс, 2009
6.
Единая коллекция цифровых образовательных
ресурсов – http://schoolcollection.edu.ru
7.
Единое окно доступа к образовательным
ресурсам – http://window.edu.ru
8.
Информационная система «Единое окно
доступа к образовательным ресурсам» (Информационно-методическое пособие для
учреждений общего образования) – http://catalog.iot.ru/pdf/window_edu_ru.pdf
9.
Каталог образовательных ресурсов «Школьный
мир» – http://www.myschools.ru
10.
Каталог электронных образовательных
ресурсов – http://fcior.edu.ru
11.
Видеоуроки «ИнтернетУрок» – http://interneturok.ru
12.
Информатика и информационные технологии в
образовании – http://www.rusedu.info
13.
Информатика и информационные технологии:
cайт лаборатории информатики МИОО – http://iit.metodist.ru
14.
Информатика и информация: сайт для
учителей информатики и учеников – http://www.phis.org.ru/informatika
15.
Материалы к урокам информатики (О.А.
Тузова, С.-Петербург, школа № 550) – http://school.ort.spb.ru/library.html
16.
Электронные учебники по HTML, Word, Excel,
VBA – http://www.on-lineteaching.com
17.
Официальный сайт профессионального
IT-тренера, разработчика и эксперта по программам пакета Microsoft Office
Николая Павлова http://www.planetaexcel.ru/
Оставьте свой комментарий
Авторизуйтесь, чтобы задавать вопросы.