Лабораторная работа. Лабораторная работа №4. Обработка числовой информации с использ. Создание таблиц, построение диаграмм Теоретическая часть
 Скачать 495.5 Kb.
|
1 2 Лабораторная работа 4. Обработка числовой информации с использованием электронных таблицЧасть1. Создание таблиц, построение диаграммТеоретическая частьЕхсеl– это табличный процессор, то есть программа, предназначенная для автоматизации работы с большими массивами чисел, представленными в табличной форме. Программы этого класса также называют электронными таблицами. Существует несколько разных подходов к использованию программы Exсel. Они различаются тем, какие применяются средства и какой результат достигается. Основное назначение программы состоит в автоматизации вычислений в числовых таблицах, когда изменение значения в одной ячейке автоматически приводит к изменению данных в других ячейках, связанных с ней. Такой стиль работы характерен для экономистов, бухгалтеров, работников банковской сферы и руководителей, отвечающих за развитие предприятий. Он основан на том, что в ячейках могут стоять не только числа, но и формулы. Если в ячейке находится формула, то в качестве числового значения ячейки на экране отображается результат расчета по этой формуле. Когда изменяются значения в ячейках, входящих в формулу, изменяется и результат расчета по формуле. Кроме простейших арифметических формул в ячейках можно использовать математические, логические, текстовые и т.д. функции и даже микропрограммы, написанные на языке VBA (Visual Basic for Applications – Visual Basic для приложений). Этот уровень использования Excel характерен для научных кругов. Excel является идеальным средством для проведения статистических расчетов и для обработки результатов экспериментов, для подготовки графиков и диаграмм. В окне документа в программе Excel отображается только текущий Рабочий лист, с которым и ведется работа. Каждый Рабочий лист имеет название, которое отображается на ярлычке листа:  Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего Рабочий лист может содержать до 256 столбцов, пронумерованных от A до IV. Строки последовательно нумеруются цифрами от 1 до 65 536. На пересечении строк и столбцов образуются ячейки таблицы. Обозначение отдельной ячейки сочетает в себе номера столбца и строки (например, А28), на пересечении которых она расположена. Обозначение ячейки – это ее адрес. Практическая часть.В электронных таблицах MS Excel необходимо выполнить задание на обработку числовой информации и создать тест на тему проекта. Задание 1. Создайте в MS Excel представленную ниже таблицу, произведите подсчет с использованием формул, отсортируйте данные в таблице, постройте диаграммы.
Для этого сначала объедините ячейки B1, C1 и D1, а ячейку А2 оставьте пустой. Далее внесите данные в таблицу в соответствии с представленным ниже образцом.  Произведите подсчет общей длины трубопроводов с помощью формул, для этого необходимо сложить длину нефтепровода с длиной газопровода по каждой стране: поставить курсор в ячейку D3; поставить знак “=“ ; м  ышью указать ячейку В3;поставить знак “+“ ; мышью указать ячейку С3; указатель мыши поставить в нижний правый угол ячейки D3 у  казатель преобразуется в вид:нажать левую кнопку мыши протащить формулу на все страны. Отсортируйте страны по длине газопровода: выделить диапазон таблицы с ячейки A2 до D12  в верхнем меню найти Данные выбрать Сортировка … Сортировать по выбрать Газопроводы  по убыванию ОК Самостоятельно отсортируйте по длине нефтепровода. Постройте диаграмму - график с маркерами «Нефтепровод стран мира» выделить диапазон таблицы с ячейки A2 до В12; на панели инструментов выбрать Мастер диаграмм;  следуя по шагам Мастера на отдельном листе получите следующий вид диаграммы:  Постройте разрезанную кольцевую диаграмму (черно-белая, использовать штриховку) – «Длина газопроводов стран Европы», на диаграмме отразить доли, приходящиеся на конкретную страну. выделить ячейки таблицы с названием европейской страны и значением длины газопроводов: начинать выделение с ячейки А2 (пустой) для выделения несмежных ячеек удерживать CTRL порядок выделения: А2 (пустая), С2 (Газопровод); Название страны, длина газопровода; …  окончательный вид диаграммы следующий:  Выполните самостоятельно следующие задания: трехмерная гистограмма – «Газопровод и нефтепровод Канада, Мексика, Германия» с выводом значений данных; круговая диаграмма «Газопровод на различных континентах» с выводом долей, приходящихся на отдельный континент. Предварительно подсчитать общую длину газопровода стран, находящихся на одном континенте (Северная Америка, Южная Америка, Евразия). придумать и создать самостоятельно 1 диаграмму. Задание 2. Составить калькуляцию на приобретение канцелярских товаров и расходных материалов для оргтехники для работы Вашего отдела. Алгоритм выполнения задания: Переименовать листы книги (правая кнопка мыши по названию листа – Переименовать): Лист1 – Итого; Лист2 – Канцтовары; Лист3 – Оргтехника На лист Оргтехника внести информацию (В столбцы Цена и Стоимость заносятся только числа, и устанавливается денежный формат ячеек):  На лист Канцтовары внести информацию (В столбцы Цена и Стоимость заносятся только числа, и устанавливается денежный формат ячеек):  На листах Канцтовары и Оргтехника подсчитать стоимость по каждому наименованию товара. Найти итоговые суммы на обоих листах На листе Итого найти Остаток: из выданной суммы вычесть найденные суммы стоимости 2-х групп товаров – канцтовары и оргтехника. Задание 3. В MS Excel заполнить таблицу: Сведения о зачислении в университет
Вычислить суммарное количество баллов для каждого абитуриента, подсчитать средние значения по каждому абитуриенту и экзамену. В столбце Сообщение о зачислении использовать функцию ЕСЛИ для сообщения «принять», если сумма баллов больше проходного (>181), и сообщение «отказать» в остальных случаях. Определить количество абитуриентов, принятых в вуз. Построить столбиковую гистограмму по общему баллу. Решение: Откроем рабочую книгу MS Excel: Пуск – Все программы – Microsoft Office – Microsoft Excel. Далее необходимо поменять название рабочего листа: щелкнем правой кнопкой мыши внизу по названию Лист1, выберем пункт Переименовать и введем с клавиатуры новое имя Пример1. Введем в таблицу исходные данные: щелкнем по ячейке А1 и введем название таблицы: Сведения о зачислении в университет в ячейки А3 :G3 введем шапку таблицы 1; в ячейки А4 : А12 введем ФИО студентов; в ячейки В4:D12 введем числа, которые представляют собой полученные на экзаменах индивидуальные баллы студентов; в ячейке Е4 необходимо посчитать общий балл студента Иванова, который равен сумме баллов по всем дисциплинам, т.е. B4+C4+D4. Для этого выделим ячейку Е4 (один раз щелкнув по ней левой кнопкой мыши) и введем по-английски с клавиатуры следующую формулу: =B4+C4+D4 и нажмем Enter для расчета. Это ручной способ ввода формул. Освоим автоматический способ ввода формулы. Выделим ячейку Е5, щелкнув на панели инструментов на значке Автосумма  , получим следующую формулу =СУММ(B5:D5). Для ввода формулы нажмите Enter.Далее таким же способом необходимо посчитать весь столбец общих баллов (Е6:Е12); Рассмотрим метод расчета среднего балла по предметам. Для расчета среднего балла воспользуемся стандартной функцией MS Excel СРЗНАЧ (число1; число2; ...), которая возвращает среднее (арифметическое) своих аргументов. Число1, число2, ... – это от 1 до 30 аргументов, для которых вычисляется среднее. Аргументы должны быть либо числами, либо именами, массивами или ссылками, содержащими числа. 1. выделим ячейку F4. В меню Вставка – Функция выберем категорию Статистические и функцию СРЗНАЧ. 2. В поле Число1 введем с клавиатуры или выделим мышью диапазон ячеек (B4:D4). В поле Аргументы функции сразу появится результат, нажмем Enter или Ок. 3. Аналогично посчитаем весь столбец Ср. балл. для всех абитуриентов и средний балл по предмету.  Рассмотрим метод выдачи результата о зачислении абитуриента. Если сумма баллов превышает 181, то необходимо выдать сообщение «принять», в противном случае – «отказать». Воспользуемся логической функцией ЕСЛИ, которая возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. Синтаксис функции: ЕСЛИ(лог_выражение;значение_если_истина ;значение_если_ложь) Лог_выражение – это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 – это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае – ЛОЖЬ. Этот аргумент может быть использован в любом операторе сравнения. Значение_если_истина – это значение, которое возвращается, если лог_выражение равно ИСТИНА. Значение_если_ложь – это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. Значение_если_ложь может быть формулой. Для нашего примера в ячейку G4 необходимо либо ввести с клавиатуры, либо через меню Вставка-Функция следующую формулу: =ЕСЛИ(E4>180;"принять";"отказать") Microsoft Excel предлагает дополнительные функции, которые можно применять для анализа данных с использованием условий. Например, для вычисления числа появлений текстовой строки или числа в диапазоне ячеек используйте функцию СЧЁТЕСЛИ. Для вычисления суммы значений, попадающих в интервал, заданный текстовой строкой или числами, используйте функцию СУММЕСЛИ. Для расчета количества абитуриентов, принятых в ВУЗ воспользуемся функцией СЧЁТЕСЛИ, которая подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию. СЧЁТЕСЛИ(диапазон;критерий) Диапазон – диапазон, в котором нужно подсчитать ячейки. Критерий – критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки". Выделим ячейку G13 и введем с клавиатуры или через меню Вставка-Функция следующую статистическую функцию: =СЧЁТЕСЛИ(G4:G12;"принять") Для расчета рейтинга (ранга) каждого абитуриента по общему баллу необходимо применить функцию РАНГ, которая возвращает ранг (рейтинг) каждого абитуриента в списке чисел. Ранг числа – это его величина относительно других значений в списке. В MS Excel для вычисления ранга используется функция РАНГ (число; ссылка; порядок), где Число – адрес на ячейку, для которой определяется ранг (E4). Ссылка - ссылка на массив общих баллов (Е4:Е12). Порядок – число, определяющее способ упорядочения. Если порядок равен 0 (нулю), или опущен, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке убывания. Если порядок – любое ненулевое число, то Excel определяет ранг числа так, как если бы ссылка была списком, отсортированным в порядке возрастания. На следующем этапе необходимо оформить таблицу, так, чтобы при распечатке она красиво выглядела на бумаге. В программе MS Excel предусмотрены разнообразные средства, с помощью которых вы можете по своему вкусу оформить таблицу. То есть, изменить шрифт, цвет, заливку и границы. Создание гистограммы с помощью мастера диаграмм В рассматриваемом примере в виде диаграммы представим столбиковую гистограмму по общему баллу. Для создания такой диаграммы нужно: выделить столбцы А4:А12 и E4:E12 нашей таблицы (для выделения ячеек в разных местах таблицы нужно удерживать нажатой клавишу Ctrl); затем щелкнуть на кнопке Мастер диаграмм  на стандартной панели инструментов; на первом шаге из 4 выберите Тип диаграммы Гистограмма, а затем щелкните на кнопке Далее; на шаге 2 в окне Источник данных диаграммы должна быть нажата кнопка Столбцах, на вкладке Ряд введите имя Общий балл, а затем Далее; третье окно мастера диаграмм состоит из нескольких разделов (вкладок), предназначенных для задания различных параметров диаграммы. В окне Параметры диаграммы на вкладке Заголовки введите название диаграммы Общий балл, отмените легенду на вкладке Легенда, а затем Далее; в последнем окне Размещение диаграммы выберите на имеющемсящелкните на кнопке Готово. После этого на экране появится следующая гистограмма к примеру:  Редактирование любых диаграмм и графиков осуществляется при помощи выделения «мышью» любых объектов и нажатия правой ее кнопки. При этом в появившемся контекстном меню необходимо выбрать Формат объекта. Задание 4. Составим таблицу для подсчета баллов студентов за семестр. Итоговый лист должен выглядеть следующим образом, обведенные жирными квадратами ячейки содержат формулы и должны в результате заполниться автоматически, поэтому НЕ ЗАПОЛНЯЙТЕ данными ячейки, взятые в жирные рамки:  Создание таблиы Заголовок – выделим первую строку: левая кнопка мыши по номеру строки 1, размер шрифта 20. В ячейку А1 вносим сведения о факультете, курсе, группе, дисциплине и т.д. Шапка таблицы. Установить курсор между нумерацией строк 1 и 2 до появления знака:  нажать левую кнопку мыши и растянуть строку. Правая кнопка мыши (п.к.м) по номеру строки 2 → Формат ячеек …→ Выравнивание: по горизонтали – по центру; по вертикали – по центру; Отображение: переносить по словам. В ячейки вносим: А2 – № п/п (ячейку сужаем); B2 Фамилия Имя (расширяем); T2 – зачет; U2 – оценка. Выделяем ячейки от С2 до S2: (п.к.м) по выделенному → Формат ячеек …→ Выравнивание: Ориентация 90º. Уменьшим ширину ячеек до 30 пикселей, используя команду Копировать формат  , выделим ячейки с D2 до S2. В ячейку С2 вносим текст семинар 1, ставим курсор мыши в нижний правый угол с квадратиком, до появления черного тонкого креста ┼, нажмем левую кнопку и протащим мышь до семинар 12 – это называется автозаполнение.Заполнение таблицы. Столбец А заполняем номерами по порядку – в А3: 1, в А4: 2, выделим номера и автозаполнением протягиваем до 12. Вносим фамилии. Строка Баллы максимум – в ячейку O16 внесем формулу суммы: команда автосумма  , и выделить диапазон ячеек (C16:N16) и в S16 – аналогично формулу =СУММ(O16:R16). Подбираем баллы таким образом, чтобы в ячейке S16стало 100. Копируем формулу из ячейки O16 в ячейку O3 и автозаполнением копируем сумму для всех студентов. Поступаем аналогично с формулой в ячейке S16.Перевод набранных баллов в оценку. «Зачтено» ставится при условии, что студенты набрали не менее 40 баллов. Для выставления оценки можно использовать формулу: ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) лог_выражение – это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ; значение_если_истина – это значение, которое возвращается, если лог_выражение равно ИСТИНА; значение_если_ложь – это значение, которое возвращается, если лог_выражение равно ЛОЖЬ; значение_если_ложь может быть формулой или функцией. В ячейку Т3 запишем формулу: =ЕСЛИ(S3>40;"зачтено";"не зачтено"), ячейку S3 можно указать мышкой. Необходимо учесть случай, когда студенты по каким-либо причинам не явились на зачет, для этого редактируем функцию: Установить курсор мыши строку формул –  Отредактировать формулу: =ЕСЛИ(Q3="";"н/я";ЕСЛИ(S3>40;"зачтено";"не зачтено")) Автозаполнением копируем формулу для всех студентов В ячейку U3 запишем формулу на случай дифференцированного зачета: ЕСЛИ(S3>79;"5(отл)";ЕСЛИ(S3>59;"4(хор)";ЕСЛИ(S3>39;"3(удов)";"2 (неудов)"))); автозаполнением копируем формулу для всех студентов. Обработка результатов группы. Подсчитаем количество студентов получивших «зачтено», «не зачтено» и не явившихся на зачет. Используем формулу СЧЁТЕСЛИ(диапазон;критерий): Диапазон – диапазон, в котором нужно подсчитать ячейки. Критерий – критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Установить курсор в ячейку T19 Вставка → Функция … → Категория: Статистические; Выберите функцию: СЧЁТЕСЛИ. ОК Диапазон: T3:T14 с отметками о зачете, диапазон не должен меняться при подсчетах остальных отметок, поэтому ссылка должна быть абсолютной, необходимо нажать клавишу F4, в результате получиться: $T$3:$T$14; Критерий: ячейка R19, в которой должно быть внесено «зачтено». ОК Автозаполнением копируем формулу на все виды отметок. Аналогично поступая, заполняем подсчет оценок в ячейках U23 … U26. Рейтинг студентов. Переименуем Лист1 в «список»: кликните правой кнопкой мыши по названию листа Лист1 в меню выбрать переименовать и дать новое имя: список. Скопируем полученный лист «список»: правая кнопка мыши по названию листа Список → Переместить/скопировать→Создать копию→(Перед листом: Лист 2) →Оk; Переименовать лист список(2) в лист «рейтинг». На листе «рейтинг» выделим ячейки с фамилиями студентов и их результатами: В3:U14. Отсортируем список по общему набранному количеству баллов: Данные → Сортировка … → Сортировать по: Итого за семестр; по убыванию; Затем по: Фамилия Имя; по возрастанию → ОК Построение диаграмм: выделим столбцы Т19:Т21; щелкнуть по образу Мастер диаграмм  на стандартной панели инструментов; на первом шаге из 4 выберите Тип диаграммы Круговая, Объемная, кнопка Далее; на шаге 2 в окне Источник данных диаграммы должно быть выбрано Ряды в: столбцах, на вкладке Ряд – Подписи категорий: выделите на листе список значения отметок о зачете, в результате получиться =список!$R$19:$S$21, кнопка Далее; третье окно мастера диаграмм состоит из нескольких разделов (вкладок), предназначенных для задания различных параметров диаграммы. На вкладке Легенда, размещение: вверху, Подписи данных: доли; кнопка Далее; в окне Размещение диаграммы Поместить диаграмму на листе: отдельном щелкните на кнопке Готово; для печати залить диаграмму, используя только черно-белую гамму цветов. Выделить долю – щелчок левой и еще раз щелчок (с расстановкой), затем щелчок правой кнопкой мыши, выбрать: Формат точки данных …→ кнопка Способы заливки → вкладка Узор → Штриховка: черная, Фон: белый; выберем тип узора. Аналогично, остальные доли. Шрифт легенды и подписей не менее 24 пт. (Рис. 1) Самостоятельно постройте диаграмму по оценкам (Рис.2).
Задания для самостоятельного выполнения Задание 5. Используя MS Excel, построить таблицы по приведенным ниже формам. Определите средний балл экзаменационной сессии по курсам и факультету. Результаты округлить до двух знаков после запятой. Определите рейтинг каждого курса. Впишите полученные данные в таблицу. По данным таблицы 2 постройте график. Таблица 1
Таблица 2
Задание 6 В MS Excel заполнить следующую таблицу со сведениями о выполнении тестов группой учащихся:
Вычислить суммарное количество баллов для каждого студента и проставить ему оценку в соответствии со шкалой: менее 20 баллов – «2» от 20 до 42 – «3» от 43 до 60 – «4» больше 60 – «5». Заполнить таблицу данными. Заполнить пустые столбцы и строки таблицы. Вычислить средний балл по каждому тесту. Подсчитать количество удовлетворительных оценок. 1 2 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
