Икт 3 лаб. Лаб_03_обработ данных Excel. Анализ и обработка данных
 Скачать 285.73 Kb.
|
Лабораторная работа № 3Тема: Анализ и обработка данных. Цель работы: Получить практические навыки по обработке данных в электронной таблице Microsoft Excel. Краткие сведения из теории Программа Microsoft Excel предназначена для работы с электронными таблицами (автоматизация расчетов, построение деловой графики, создание табличных документов, ведение баз данных). Рабочее окно MS Excel представлено на рисунке 1.  Рисунок 1 - Рабочее окно MS Excel Рабочее окно содержит все стандартные элементы, присущие окну приложения Windows. Верхняя строка – заголовок окна, вторая строка – меню Excel, третья строка – панели инструментов, четвертая строка – строка формул, ниже рабочая область книги – документа в Excel. Строка формул служит для ввода и редактирования данных в ячейках. В левой части формул находится раскрывающийся список – поле имени, в котором высвечиваются адрес или имя выделенной активной ячейки или блока ячеек таблицы. Ниже рабочего поля располагается строка с ярлычками рабочих листов книги. Ниже рабочей области расположена Строка состояния, в которой высвечиваются режимы работы табличного процессора, выводится дополнительная информация. Окно документа Excel можно разделить на два или четыре подокна и одновременно работать с разными частями одной и той же таблицы (рис.2).  Рисунок 2 - Рабочее окно Excel с подокнами Разделить окно таблицы можно командой меню Вид - Разделить, либо с помощью разделителей окна «маленькие прямоугольники на концах полос прокрутки». Для снятия разделения окон набрать повторно меню Вид - Разделить. Полосы разделения можно перемещать с помощью мыши. Основные понятия MS Excel Документ в программе Excel называется рабочей книгой (Книга1, Книга 2, и т.д.). Книга состоит из рабочих листов (Лист1, Лист2 и т.д.). Рабочая книга Excel – это совокупность рабочих листов, сохраняемых на диске в одном файле с расширением *.xlsx. В каждом файле может размещаться 1 книга, а в книге – от 1 до 255 рабочих листов. По умолчанию в книге содержится 3 рабочих листа. Рабочие листы можно вставлять, удалять, переставлять. Щелкая по ярлычку листа, можно переходить от одного листа к другому в пределах книги. Электронная таблица Excel состоит из 65536 строк и 256 столбцов. Строки нумеруются числами (от 1 до 65536), а столбцы обозначаются буквами латинского алфавита A, B, C, …,Z. После столбца Z следуют столбцы AA, AB, AC, …, BA, BB, ВС, … На пересечении столбца и строки расположена – ячейка. Формат и размеры ячеек – ширину столбцов и высоту строк – можно изменить с помощью команд меню, или с помощью мыши. Текущая (активная) ячейка – ячейка, в которой в данный момент находится курсор. Она выделена на экране жирной черной рамкой. Каждая ячейка таблицы имеет свой адрес, который используется для указания на ячейку - при ссылке на нее. Например, A2. Адрес и содержимое текущей ячейки выводятся в строке формул. Ссылка – способ указания адреса ячейки. Адреса ячеек могут быть относительными, абсолютными или смешанными. Ячейки могут иметь собственные имена. Ссылки на ячейки (адреса ячеек) используются в формулах, в функциях в качестве аргументов. Типичными установками, принятыми по умолчанию на уровне всех ячеек таблицы, являются: Левое выравнивание для символьных данных; Правое выравнивание для цифровых данных. Блок (диапазон) ячеек – это группа последовательных ячеек. Блок используемых ячеек может быть указан или выделен двумя путями: Непосредственным набором с клавиатуры начального и конечного адресов ячеек(например, A1:C4), формирующих диапазон; Выделением блока с помощью мыши или клавиш управления курсором; Обозначение ячейки, составленное из номера столбца и номера строки, называется относительным адресом. При копировании формул в Excel действует правило относительной адресации ячеек, суть состоит в том, что при копировании формулы табличный процессор автоматически смещает адрес в соответствии с относительным расположением исходной ячейки и создаваемой копии. Абсолютная адресация создается из относительной ссылки путем вставки знака доллара ($) перед заголовком столбца или номером строки. Например: $A$1, $B$1 – это абсолютные адреса ячеек A1 и B1, следовательно, при их копировании не будут меняться ни номер строки, ни номер столбца. Иногда используют смешанный адрес, в котором постоянным может быть один компонент. Например: $B7 – при копировании формул не будет изменяться номер столбца; B$7 – при копировании формул не будет изменяться номер строки. Для обозначения адреса ячейки с указанием листа используется имя листа и восклицательный знак. Например: Лист 2!B5, Итоги!B5 Для обозначения адреса ячейки с указанием книги используются квадратные скобки. Например: [Книга 1]Лист2!А1 При назначении имени ячейки или диапазону следует соблюдать правила: Имя должно начинаться с буквы русского или латинского алфавита. Цифры могут быть в имени, только не в начале. В имени нельзя использовать пробелы, вместо них можно ставить подчеркивание. Длина имени ячейки не должна превышать 255 символов. Для имени листа существуют следующие ограничения: Длина имени листа не больше 31 символа; Имя листа не должно содержать квадратных скобок; Имя не должно содержать следующие символы - двоеточие, косую черту (/), обратную косую черту (\), знак вопроса, звездочку(*). Порядок выполнения лабораторной работы Изучить теоретический материал. Выполнить задания. Ответить на контрольные вопросы. Задание 1 1. Создайте таблицу, представленную на рисунке 3.  Рисунок 3 – Вид таблицы для задания 1 2. В ячейку B1 введите наименование организации. 3. В ячейку А3 введите текст «Dollar exchange rate». 4. В ячейку В3 введите число 340,5. 5. В ячейки A4, B4, C4, D4, E4, F4 введите текст как на рис. 3. 6. Начиная с ячейки А5 до ячейки А14 введите 10 чисел, используя автозаполнение. 7. Начиная с ячейки B5 до ячейки B14 введите 10 наименований товаров. 8. Начиная с ячейки С5 до ячейки С14 введите цену товара в долларах. 9. Запишите в ячейку D5 формулу для вычисления цены товара в тенге (=C5*$B$3), затем скопируйте формулу до ячейки D14. 10. Начиная с ячейки Е5 до ячейки Е14 введите количество товара по наименованиям. 11. Запишите в ячейку F5 формулу для стоимости товара с учетом количества (=D5*E5). Скопируйте формулу до ячейки F14. 12. Используя автосуммирование, вычислите общую стоимость в тенге в ячейке F15. 13. Измените курс доллара на 380. Посмотрите, что изменилось в таблице. 14. Найдите среднее, максимальное и минимальное значения цены в тенге с использованием функций СРЗНАЧ, МАКС и МИН, результаты поместите в ячейки B15, B16, B17 соответственно. 15. Постройте диаграмму, характеризующую наименование и стоимость товара. 16. Сохраните документ под именем Прайс.xlsx. Задание 2 Выполнить задание в соответствии со своим вариантом. Вариант 1. Создать таблицу по образцу.  В дополнительной графе вычислите плотность населения материков (Население/Площадь). В дополнительной графе определите материки, в которых население отсутствует. В дополнительной графе «Наличие озер», поставьте знак «+», если озеро есть, и знак «–» иначе. Определите количество материков с территорией больше 30 млн. кв. м. Выберите материки, население которых больше 20 млн. человек. Постройте диаграмму, отражающую плотность населения материков Вариант 2. Создать таблицу по образцу.  Определите количество собак, вес которых меньше 15 кг. В дополнительной графе отметьте словом «маленькая», собак, вес которых меньше 10 кг. Определите среднюю продолжительность жизни всех собак. Найдите собаку с наименьшей продолжительностью жизни. В дополнительной графе укажите собак, которые весят больше 14 кг. Постройте гистограмму, отражающую продолжительность жизни собак, данных пород. Вариант 3. Создать таблицу по образцу.  В дополнительной графе вычислите остаток приборов на конец месяца. (Остаток на начало месяца + Приход - Расход). Определите количество приборов, Мощность которых не превышает 1000(ВТ). Вычислите общее количество всех приборов оставшихся на конец месяца. Найдите самый мощный прибор. В дополнительной графе укажите приборы, мощность которых больше 1000 Вт Постройте гистограмму, отражающую мощность каждого электроприбора Вариант 4. Создать таблицу по образцу.  Определить размер заработной платы для каждого рабочего. Стоимость 1 часа равна 600 тг. Определить работников, рабочий день которых превышает 8 часов. Определить размер премии для каждого рабочего. Если Заработная плата больше 60000 тг, то Премия равна 20% от Заработной платы, иначе Премия равна 10% от “Заработной платы”. Вычислить среднюю зарплату рабочих. Найти самого молодого сотрудника. Построить диаграмму, отражающую количество отработанных дней каждым рабочим. Вариант 5. Создать таблицу по образцу.  Определить должности, имеющих зарплату меньше 100000 тенге. Определить надбавку за выслугу лет для каждого сотрудника. (Если стаж работы больше 15 лет, то Выслуга(%)=20, иначе Выслуга(%)=10). Вычислить зарплату для каждого сотрудника. Определите средний оклад всех сотрудников. Выберите сотрудников со стажем работы больше 15 лет. Постройте диаграмму, отражающую зарплату каждого сотрудника. Вариант 6. Создайте таблицу «Отдел кадров».
Определить должности, имеющих зарплату меньше 70 000 тенге. В дополнительной графе выведите фамилии сотрудников, имеющих стаж работы больше 15 лет. Выведите фамилию сотрудника, имеющего минимальный стаж работы. Выведите количество сотрудников, имеющих заработную плату больше 80 000 тенге. Определите средний возраст сотрудников. Постройте гистограмму с заголовком “Стаж работы сотрудников”, включающую данные с фамилиями сотрудников и их стажем работы. Вариант 7. Создать таблицу по образцу.
Определите количество приборов, мощность которых превышает 700 Вт. В дополнительной графе вычислите Остаток на конец месяца. (Остаток на начало месяца + Приход - Расход) В дополнительной графе «Заказ», определите количество товара, которое необходимо заказать. (Заказ=12, если остаток на конец месяца меньше 5, иначе заказ=7) Найдите самый мощный прибор. В дополнительной графе укажите приборы, мощность которых больше 800 Вт. Постройте гистограмму, отражающую мощность каждого электроприбора Вариант 8. Создать таблицу по образцу.
Определить количество сотрудников, имеющих зарплату больше 100000 тенге. Определить надбавку за выслугу лет для каждого сотрудника. (Если стаж работы больше 10 лет, то Выслуга(%)=20, иначе Выслуга(%)=10). Вычислить зарплату для каждого сотрудника. Определите средний оклад всех сотрудников. В дополнительной графе укажите сотрудников с зарплатой больше 100000 тг. Постройте диаграмму, отражающую зарплату каждого сотрудника. Вариант 9. Создайте таблицу по образцу.
Вместо вопросительных знаков введите формулы. Определите, в какие годы число городского населения составляло ниже 6 млн человек. Определите, в какие годы число сельского населения составляло выше 5 млн человек. Определить среднее значение по городскому населению. Определить максимальное значение по сельскому населению. Построить гистограмму, отражающую численность городского и сельского населения по годам (тыс. чел.) Вариант 10. Создайте таблицу по образцу.
Вместо вопросительных знаков введите формулы. Для каждого магазина вычислить средний объем товарооборота за 4 квартала в дополнительной графе «Средний объем товарооборота» В дополнительной графе указать магазины, годовой объем товарооборота которых больше 15 млн. тенге. Найти магазин с наибольшим товарооборотом за первый квартал. Найти магазин с наименьшим товарооборотом за четвертый квартал. Построить диаграмму, отражающую годовой объем товарооборота по магазинам. Вариант 11. Создайте таблицу по образцу.
Стоимость аренды сейфа рассчитывается с учетом следующих тарифов: - до 30 дней аренды - 6000 тг./сутки; - свыше 30 дней - 5000 тг./сутки Вместо вопросительных знаков введите подходящие формулы. В дополнительной графе укажите клиентов, чей срок аренды составляет до 45 дней включительно. Определите количество клиентов, чья стоимость аренды составляет 6000 тг./сутки. Найдите клиента, арендующего сейф дольше всего. Рассчитайте средний срок аренды сейфов в банке. Построить диаграмму, отображающую стоимость аренды по клиентам. Вариант 12. Создать таблицу по образцу.
Определите количество приборов, мощность которых превышает 700 Вт. В дополнительной графе вычислите Остаток на конец месяца. (Остаток на начало месяца + Приход - Расход) В дополнительной графе «Заказ», определите количество товара, которое необходимо заказать. (Заказ=12, если остаток на конец месяца меньше 5, иначе заказ=7) Найдите самый мощный прибор. В дополнительной графе укажите приборы, мощность которых больше 800 Вт. Постройте гистограмму, отражающую мощность каждого электроприбора Задание 3 Составить таблицу, которая автоматически начисляет стипендию студентам Вашей подгруппы. Считать, что начисление стипендии происходит в зависимости от оценок, полученных на 3 экзаменах зимней сессии следующим образом. Оценки на экзамене: 5, 4, 3, 2. Базовая величина стипендии — 20000 тенге. Базовую стипендию получают все сдавшие сессию - (нет "двоек"). Сдавшие без “троек” получают 1,5 базовых стипендии. Сдавшие все экзамены на “пятерку” получают 2 базовые стипендии. Не сдавшие (получившие хотя бы одну “двойку”) стипендии не получают. Требования к решению: Изменение величины базовой стипендии автоматически ведет к изменению величины стипендии. Изменение оценки за экзамен автоматически изменяет размер стипендии. Содержание отчета Титульный лист. Результаты выполненных заданий. Ответы на контрольные вопросы. Контрольные вопросы Как завершить ввод данных в ячейку? Какие бывают виды адресации? Как задать абсолютную адресацию? Как выделить два столбца в таблице, если они расположены не рядом? С какого символа начинается формула? | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||