Лабораторная работа 1 Основы работы в Excel Цель работы научить студента открывать существующие книги, сохранять книги на новом месте и под новыми именами, работать с листами книги,
 Скачать 1.3 Mb.
|
|
A1:K16; L8:O16; P10:R16; A17:A18. Замечание несмежные диапазоны выделяют с нажатой клавишей Ctrl , b) температуры от –40 0 до –30 0 отобразить темно-синим фоном температуры от –30 0 до –18 0 выделить светло-синим фоном, температуры от – 18 0 до 0 0 – голубым фоном c) выделить положительные температуры – диапазоны A19:A29; B17:K29; L17:O22; P17:R20, d) температуры от до 7 0 отобразить розовым фоном температуры от до 14 0 выделить светло-красным фоном, температуры от до 20 0 – красным фоном. 39 Условное форматирование с параметром "формула" Задание 3 Форматирование таблицы осадков 3.1. На листе Таблица отметить желтыми буквами на зеленом фоне ячейки с названиями месяцев и количеством осадков в 2000 году от 50 до 60 мм. Задание 4 Форматирование баллов студентов 4.1. Открыть файл из предыдущей лабораторной работы, лист Баллы. 4.2. Скопировать таблицу на новый лист текущей книги. 4.3. Используя условное форматирование, установить фон красного цвета для ячеек с оценками по контрольным точкам, рейтингом, оценкой в пяти- бальной системе и отметкой о зачете, соответствующим неудовлетворительным оценкам. При этом форматирование выполняется за два шага a) форматируются столбцы с исходными оценками по значению, b) форматируются три последних столбца по формуле, здесь нужно анализировать значения в столбце "Оценка. 4.4. Отформатировать синим жирным курсивом фамилию и средний балл студента, имеющего максимальный средний балл. Задание 5 Форматирование расчета корней 5.1. Перейти на лист Корни. 5.2. Выделить сообщение и корни фоном зеленого цвета, если уравнение имеет два действительных корня, фоном красного цвета, если нет действительных корней, и фоном синего цвета, если уравнение имеет один корень. Задание 6 Расчет и форматирование отчета продаж 6.1. Составить таблицу отчета продаж за текущий день. Формирование таблицы выполнять в следующем порядке a) ячейкам, содержащим курс доллара, скидку и налог, дать имена. Эти имена затем использовать в формулах, b) дату вводить как текущую с использованием Мастера функций и правильно форматировать, c) заполнить первые пять колонок, d) в шестую колонку необходимо занести формулы для расчета стоимости проданного оборудования в рублях, e) в седьмой колонке необходимо скорректировать стоимость с использованием вложенной функции ЕСЛИ в зависимости от вида оплаты если оплата выполняется по пластиковым картам Сбербанка РФ, то выполняется скидка, а если наличными – то налог, в остальных случаях оплачивается полная стоимость, f) отформатировать таблицу и ее шапку. 6.2. Условным форматированием выделить a) строки таблицы красным цветом, где производилась оплата по пластиковым картами синим цветом, где производилась оплата за наличные, b) полужирным курсивом строку с максимальной суммой покупки 40 Курс$ 27 Скидка при оплате по пластиковым картам 7% Налог при оплате наличными деньгами 5% Заказчик Вид оплаты Наименование Кол-во Цена,$ Стоимость, руб Оплачено АлтГТУ DIMM 32 MB 4 62.2 6717.60 6717.60 Иванов Наличные Клавиатура 4.8 129.60 ООО "Лимит" Карта DJ 300 Series 1 25.25 681.75 Школа № 27 HP чернила 6.6 178.20 178.20 Петров Карта ViewSonic 15" 1 213 5751.00 Школа № 22 Genius Mouse 5 2.85 384.75 Отчет продаж за Март, Задание 7 Создание и форматирование сводной ведомости 7.1. Составить фрагмент сводной экзаменационной ведомости, используемой в деканате для начисления стипендии. Составление таблицы выполнять в следующем порядке a) заполнить первые четыре колонки и шапку таблицы, b) в отдельную ячейку занести размер обычной стипендии - 450 руб, c) заполнить последнюю колонку с использованием вложенной функции ЕСЛИ и логических функций И, ИЛИ последующему правилу. - если студент не имеет двоек, то ему начисляется обычная стипендия, - если студент имеет отличные и хорошие оценки, то ему начисляется стипендия в двойном размере, - если в сессию имеются двойки, то стипендия не начисляется и равна 0. 7.2. Правильно форматировать таблицу. 7.3. Используя условное форматирование, выделить фамилию и размер стипендии a) зеленым цветом - студентов с двойной стипендией, b) синим цветом – студентов с обычной стипендией. Размер стипендии 450 Фамилия Математика Химия Начертательная геометрия Стипендия Иванов 5 Петров Сидоров 3 4 Васечкин 0 Павлов Сводная экзаменационная ведомость гр. ПГС-01 за семестр Контрольные вопросы 1. Отличие условного форматирования от прямого. 41 2. Порядок выполнения условного форматирования. 3. Задание условия. В чем разница при форматировании по значению от по формуле 4. Форматирование по значению. Пример. 5. Форматирование по формуле. Для какой ячейки интервала записывается условие Правила формирования типа адресации для ячеек, указанных в формуле. 6. Как формируется результат, если в выделенном диапазоне одна или несколько ячеек удовлетворяют нескольким условиям 7. Редактирование условного формата. 8. Удаление условных форматов. 9. Поиск ячеек, имеющих условный формат. Образец контрольной работы по разветвляющимся процессами условному форматированию Цель работы проверить навыки создания разветвляющихся вычислительных процессов и условного форматирования. Оборудование, технические средства, инструмент персональный компьютер, Microsoft Excel Создать ведомость оплаты товаров. Скидка 10% Йогурт Сырок Майонез 1 Москва 10 шт шт шт руб руб. 2 Новосибирск 3 шт шт шт руб руб. 3 Барнаул 20 шт шт шт руб руб руб руб руб. Итого: 1 877,70 руб 737,40 руб. Цена товара: Ведомость оплаты товара Товар № Город Всего К оплате Молочные изделия Порядок выполнения 1. Заполнить шапку таблицы. 2. Заполнить таблицу исходными данными (столбцы №, Город, Товар. 3. Вычислить столбец Всего , используя данные о количестве товаров и их ценах. 4. Вычислить столбец К оплате, учитывая скидку, если стоимость покупки не менее 500 руб.(использовать функцию ЕСЛИ. 5. Вычислить строку Итого используя автосуммирование. 6. Выделить зеленым фоном максимальное количество молочных товаров, а желтым фоном – меньшее среднего значения по значению. 42 7. Выделить жирными зелеными символами значения в столбцах Город, Всего, К оплате, в которых выполнялась скидка (стоимость не менее 500 руб) (формула. Лабораторная работа № 9 Графическое представление данных с использованием диаграмм Цель работы научить студентов строить диаграммы различных типов, включая графики функций, гистограммы, круговые диаграммы, поверхности и сечения поверхностей. Оборудование, технические средства, инструмент персональный компьютер, Microsoft Excel. Порядок выполнения Задание 1 Построение диаграммы наблюдений метеостанции 1.1. Скопировать таблицу осадков из л/р 8 на лист 1 текущей книги. Переименовать лист в Осадки. 1.2. Добавить столбец, в котором для каждого месяца вычислить среднее затри года. 1.3. Построить диаграмму наблюдений количества осадков затри года как гистограмму на этом же листе справа от таблицы. Подписать название диаграммы и ее осей как на образце. 1.4. Скопировать диаграмму, располагая ее внизу под исходными данными. Проследить, чтобы в дальнейшей работе обязательно была панель Мастера диаграмм. 1.5. Форматировать скопированную диаграмму 43 a) расположить ряды по строкам (что является при этом осью категорий, что является рядами, b) добавить таблицу под диаграмму, c) изменить размеры таблицы и высоту шрифта, чтобы таблица поместилась полностью, 1.6. Показать измененную диаграмму преподавателю. Сохранить файл под именем "фамилия. 1.7. Форматировать исходную диаграмму, расположенную справа от таблицы (при форматировании на панели Мастера диаграмм правильно выбрать из списка объект или указать его мышью на диаграмме, а затем, используя кнопку или контекстное меню, установить нужные свойства 44 a) выбрать в качестве объекта Ось значений, для оси значений установить свойства максимальное значение - 200, цена основного деления –50, цена промежуточного деления – 10, промежуточные метки делений пересекают ось наружу b) выбрать в качестве объекта Ось категорий, для оси категорий установить свойства основные деления пересекают ось c) выбрать в качестве объекта Область построения диаграммы, в свойствах изменить цвет заливки области на голубой d) выбрать в качестве объекта Основные линии сетки оси значений, изменить цвет линий сетки на красный e) выбрать в качестве объекта Ряд 2003 г, в свойствах изменить заливку этого ряда на зеленый цвет. 1.8. Для трех столбцов гистограммы (максимального количество осадков в каждом году) указать над столбцами численные значения, выполнив следующие действия a) выделить как объект любой Ряд г. - при этом выделится каждый столбец ряда b) щелкнуть мышью по выбранному столбцу - выделится только столбец и отобразится информация о ряде, категории и значении c) щелкнуть по кнопке и установить в группе Подписи значений переключатель значение - над столбиком диаграммы появится численное значение d) повторить эти действия для двух остальных столбцов 1.9. Добавить надпись Максимальное количество осадков, расположить в правом верхнем углу и стрелкой связать надпись и столбец, выполнив следующие действия a) выделить как объект Область диаграммы, b) в строке формул написать текст "максимальное количество осадков, c) появившееся текстовое окно с набранной надписью перенести вправо на свободную область и изменить в размерах так, чтобы оно занимало 3 строки, обеспечить высоту шрифта не более 10 пунктов, d) через меню Вид отобразить панель рисования, e) выбрать на панели рисования кнопку , f) подвести курсор клевому краю надписи и с нажатой левой кнопкой мыши провести стрелку от надписи к одному из столбцов, g) провести стрелки к двум другим столбцам. Результат изображен на рисунке. 45 1.10. Изменить тип диаграммы на линейчатую. 1.11. Построить новую диаграмму – круговую - на отдельном листе диаграмм, отображая среднее количество осадков по месяцам. Для построения выделить несмежные столбцы таблицы, затем выбрать вид диаграммы объемный вариант разрезанной круговой диаграммы. Диаграмму подписать и вывести значения секторов. Если надписи секторов перекрывают друг друга, то их следует разнести. 2001- 2003 гг Задание 2 Создание смешанной диаграммы 2.1. На диаграмму – гистограмму добавить ряд данных, содержащих среднее значение по месяцам столбец Среднее выделить столбец и мышкой перетащить его на диаграмму. 2.2. У ряда Среднее изменить тип диаграммы на График. 2.3. Отформатировать ряд Среднее a) цвет линии – синий, b) увеличить толщину. 46 Задание 3 Анализ работы фирмы Имеются обобщенные данные о работе фирмы за несколько лет. Построить диаграмму - гистограмму прихода и расхода в зависимости от года. 3.1. Перейти на лист 2 и переименовать его в Анализ работы фирмы. 3.2. Создать таблицу значений. 3.3. Установить курсор внутри таблицы и вызвать Мастер диаграмм. Шаг 1. Выбрать тип Гистограмма, вид - Обычная. Нажать кнопку Предварительный просмотр. По оси категорий отображены порядковые номера 1, 2, 3, 4,… и каждому номеру соответствуют 3 столбика, то есть Excel рассматривает Год как ряд данных, а не как ряд категорий. Нажать кнопку Далее>. Шаг 2. Изменить исходные данные для получения требуемой гистограммы a) перейти на вкладку Ряд b) в списке Ряд выделить Год и щелкнуть по кнопке Удалить Столбики, соответствующие ряду Год исчезают, c) поместить курсор в поле ввода Подписи оси Хи выделить диапазон ячеек, содержащих значение годов. Теперь вместо порядковых номеров по оси категорий отображены года. Шаг 3. Дать диаграмме название Итоги работы, оси Х – заголовок Годы. Убрать линии сетки. Шаг 4. Поместить диаграмму на отдельном листе. Замечание Если в таблице значений очистить ячейку со значением Год, то требуемая диаграмма будет получена автоматически без изменения исходных данных на шаге 2. Задание 4 Построение таблиц и графиков функций от одного аргумента 4.1. Перейти на лист 3 и переименовать его в Графики функций. 4.2. Построить таблицу графиков функций и f2(x)=arccos(x/2) для x [-2,2] c шагом 0.4, выполнив действия a) впервой строке записать заголовки столбцов x, f1(x),f2(x) и центрировать их b) в первом столбце занести с помощью прогрессии значения аргументах) сформировать значения двух следующих столбцов - функций f1(x), f2(x), отображать в столбцах два знака после запятой и центрировать их 47 4.3. Построить график функции f1(x) a) как точечную диаграмму с маркерами, b) на втором шаге Мастера открыть вкладку "Ряди удостовериться в том, что на графике будет изображен один ряд - f1(x) - , проверить его имя, значения и подписи оси Хна третьем шаге Мастера выполнить подпись диаграммы "Графики функций" и обеспечить вертикальные и горизонтальные линии сетки, d) диаграмму расположить рядом с таблицей. 4.4. Форматировать график a) ось значений отобразить как утолщенную линию синего цвета, значения оси изменяются от -4 до 6, нанести деления с шагом 2, в значениях делений убрать дробную часть (отображать как целые числа, b) ось категорий отобразить утолщенной линией синего цвета, метки пересекают ось, c) обеспечить фон области построения графика белого цвета, а также пересечения осей значений и категорий в точке (0, 0), d) отобразить график линией зеленого цвета без маркеров. 4.5. Показать график преподавателю. На этот же график добавить еще один ряд, соответствующий функции х. 4.6. Форматировать этот график как маркеры – " " без соединения красного цвета (обратить внимание, что у маркеров цвет границы и фон должны быть одинакового красного цвета. 4.7. Изменить значения подписей в легенде на выражения функций использовать меню Диаграмма – Исходные данные – вкладка Рядили контекстное меню) и переместить легенду подзаголовок. Задание 5 Добавление данных на диаграмму 5.1. Отобразить на первом графике из предыдущего задания точку с абсциссой 0.6, выполнив действия a) в ячейку A13 занести значение 0,6 – аргумент функции. В ячейки B13, C13 скопировать формулы функций. b) выделить диаграмму правой кнопкой мыши, 48 c) меню Исходные данные – вкладка Ряд, d) нажать кнопку Добавить, в строку Имя ввести текст Точка 1, в строку Значения Х ввести адрес A13, содержащий абсциссу точки, в строку Значения Y занести адрес B13, содержащий ординату точки. Точка появится на графике, как новый ряд с именем Точка 1, 5.2. Отметить точку символом "", выполнив действия a) выделить точку, выбрав имя ряда из списка элементов на панели Диаграммы, b) нажать кнопку на панели Диаграммы, c) на вкладке Вид задать параметры маркера цвет и фон синий, размер 10 пт. 5.3. Аналогично добавить на второй график точку стой же абсциссой, отметить точку символом "". В результате получить диаграмму, представленную на рисунке. Задание 6 Построение поверхностей Построить поверхность гиперболического параболоида 9 y 4 x z 2 2 , где х, 4], y [-3, 3] с шагом 0.4 в следующем порядке 6.1. Вставить новый лист и переименовать его в Поверхность. 6.2. Построить таблицу функции z(x,y) от двух аргументов a) в ячейки А2:А22 занести значениях и выделить их заливкой, b) в ячейки В занести значения y и выделить их заливкой, c) в ячейку В занести формулу вычисления поверхности и правильно поставить адресацию, d) заполнить ячейки таблицы формулой. 6.3. Построить Мастером диаграмм поверхность, располагая ряды в столбцах и задавая заголовок и подписи осей. 49 6.4. Выполнить форматирование диаграммы a) активизировать диаграмму и выполнить щелчок на стенке объемной диаграммы, при этом в диаграмме появятся черные квадраты, обозначающие некоторые из углов параллелепипеда, описанного вокруг диаграммы, b) поместить курсор мыши на один из квадратиков (указатель примет вид крестика) и перемещать при нажатой левой кнопке мыши. На экране будет представлен контур параллелепипеда без вписанной диаграммы. Изменяя форму и положение контура в пространстве, можно добиться наилучшего представления диаграммы. c) отпустить левую кнопку мыши и просмотреть полученный результат. 6.5. Построить сечения поверхности гиперболоида при х = -2.4, х = 0, х = 2. Для этого a) выделить несмежные диапазоны аргумента y как Аи рядов-строк A6:Q6 (для x=-2.4), A12:Q12 (для x=0), A17:Q17 (для x=2), b) Мастером диаграмм построить точечные графики, на втором шаге проверить диапазоны рядов и подписать имена рядов, на третьем шаге подписать диаграмму и обеспечить построение вертикальных и горизонтальных основных линий сетки. Cечение плоскостями, параллельными ZOY -2 -1 0 1 2 -4 -3 -2 -1 0 1 2 3 4 x=-2,4 x=0 x=2 6.6. Форматировать диаграмму a) в рядах убрать маркеры, отображение выполнять утолщенной линией, b) форматировать ось значений и категорий, правильно указывая максимальные и минимальные значения, цену основных делений. 6.7. Построить сечения поверхности гиперболоида плоскостями, параллельными ZOX при y = -2.2, y = 0.6, y = 3. Для этого 50 a) выделить несмежные диапазоны аргументах как A1:A22 и рядов- столбцов D1:D22 (для y=-2.2), K1:K22 (для y=0.6), Q1:Q22 (для y=3), b) Мастером диаграмм построить точечные графики, на втором шаге проверить диапазоны рядов и подписать имена рядов, на третьем шаге подписать диаграмму и обеспечить построение вертикальных и горизонтальных основных линий сетки. 6.8. Форматировать диаграмму аналогично предыдущей. Задание 7 Форматирование объемных диаграмм с использованием диалогового окна Формат трехмерной поверхности 7.1. Перейти на лист Поверхность. 7.2. Скопировать диаграмму поверхности два раза. 7.3. Отформатировать первую диаграмму так, чтобы она отображалась со стороны плоскости |