Сведения о зачислении в университет

Лабораторная работа 4. Обработка числовой информации с использованием электронных таблиц

 

Часть1. Создание таблиц, построение диаграмм

Теоретическая часть

Ехсеl – это табличный процессор, то есть программа, предназначенная для автоматизации работы с большими массивами чисел, представленными в табличной форме. Программы этого класса также называют электронными таблицами.

Существует несколько разных подходов к использованию программы Exсel. Они различаются тем, какие применяются средства и какой результат достигается. Основное назначение программы состоит в автоматизации вычислений в числовых таблицах, когда изменение значения в одной ячейке автоматически приводит к изменению данных в других ячейках, связанных с ней. Такой стиль работы характерен для экономистов, бухгалтеров, работников банковской сферы и руководителей, отвечающих за развитие предприятий. Он основан на том, что в ячейках могут стоять не только числа, но и формулы. Если в ячейке находится формула, то в качестве числового значения ячейки на экране отображается результат расчета по этой формуле. Когда изменяются значения в ячейках, входящих в формулу, изменяется и результат расчета по формуле.

Кроме простейших арифметических формул в ячейках можно использовать математические, логические, текстовые и т.д. функции и даже микропрограммы, написанные на языке VBA (Visual Basic for Applications – Visual Basi c для приложений). Этот уровень использования Excel характерен для научных кругов. Excel является идеальным средством для проведения статистических расчетов и для обработки результатов экспериментов, для подготовки графиков и диаграмм.

В окне документа в программе Excel отображается только текущий Рабочий лист, с которым и ведется работа. Каждый Рабочий лист имеет название, которое отображается на ярлычке листа:

Рабочий лист состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего Рабочий лист может содержать до 256 столбцов, пронумерованных от A до IV. Строки последовательно нумеруются цифрами от 1 до 65 536.

На пересечении строк и столбцов образуются ячейки таблицы. Обозначение отдельной ячейки сочетает в себе номера столбца и строки (например, А28), на пересечении которых она расположена. Обозначение ячейки – это ее адрес.

Практическая часть.

В электронных таблицах MS Excel необходимо выполнить задание на обработку числовой информации и создать тест на тему проекта.

Задание 1. Создайте в MS Excel представленную ниже таблицу, произведите подсчет с использованием формул, отсортируйте данные в таблице, постройте диаграммы.

Страна	Длина трубопроводов, тыс. км
	Нефтепроводы	Газопроводы	Всего
США	276,0	331,0
Россия	63,0	150,0
Германия	7,5	97,6
Канада	23,5	75,0
Мексика	39,7	13,2
Франция	7,5	24,7
Италия	3,8	19,4
Китай	10,8	6,2
Аргентина	7,0	9,9
Великобритания	3,9	12,8

Для этого сначала объедините ячейки B1, C1 и D1, а ячейку А2 оставьте пустой. Далее внесите данные в таблицу в соответствии с представленным ниже образцом.

 

Произведите подсчет общей длины трубопроводов с помощью формул, для этого необходимо сложить длину нефтепровода с длиной газопровода по каждой стране:

· поставить курсор в ячейку D3;

· поставить знак “=“;

· мышью указать ячейку В3;

· поставить знак “+“;

· мышью указать ячейку С3;

· указатель мыши поставить в нижний правый угол ячейки D3

· указатель преобразуется в вид:

 

· нажать левую кнопку мыши протащить формулу на все страны.

 

1. Отсортируйте страны по длине газопровода:

 

· выделить диапазон таблицы с ячейки A2 до D12

 

· в верхнем меню найти Данные

· выбрать Сортировка …

· Сортировать по выбрать Газопроводы

· по убыванию

· ОК

 

2. Самостоятельно отсортируйте по длине нефтепровода.

 

3. Постройте диаграмму - график с маркерами «Нефтепровод стран мира»

· выделить диапазон таблицы с ячейки A2 до В12;

· на панели инструментов выбрать Мастер диаграмм;

· следуя по шагам Мастера на отдельном листе получите следующий вид диаграммы:

 

4. Постройте разрезанную кольцевую диаграмму (черно-белая, использовать штриховку) – «Длина газопроводов стран Европы», на диаграмме отразить доли, приходящиеся на конкретную страну.

· выделить ячейки таблицы с названием европейской страны и значением длины газопроводов:

§ начинать выделение с ячейки А2 (пустой)

§ для выделения несмежных ячеек удерживать CTRL

§ порядок выделения: А2 (пустая), С2 (Газопровод); Название страны, длина газопровода; …

 

· окончательный вид диаграммы следующий:

 

 

 

5. Выполните самостоятельно следующие задания:

· трехмерная гистограмма – «Газопровод и нефтепровод Канада, Мексика, Германия» с выводом значений данных;

· круговая диаграмма «Газопровод на различных континентах» с выводом долей, приходящихся на отдельный континент. Предварительно подсчитать общую длину газопровода стран, находящихся на одном континенте (Северная Америка, Южная Америка, Евразия).

· придумать и создать самостоятельно 1 диаграмму.

Задание 2. Составить калькуляцию на приобретение канцелярских товаров и расходных материалов для оргтехники для работы Вашего отдела.

Алгоритм выполнения задания:

Переименовать листы книги (правая кнопка мыши по названию листа – Переименовать): Лист1 – Итого; Лист2 – Канцтовары; Лист3 – Оргтехника

На лист Оргтехника внести информацию (В столбцы Цена и Стоимость заносятся только числа, и устанавливается денежный формат ячеек):

 

 

На лист Канцтовары внести информацию (В столбцы Цена и Стоимость заносятся только числа, и устанавливается денежный формат ячеек):

 

 

На листах Канцтовары и Оргтехника подсчитать стоимость по каждому наименованию товара.

Найти итоговые суммы на обоих листах

На листе Итого найти Остаток: из выданной суммы вычесть найденные суммы стоимости 2-х групп товаров – канцтовары и оргтехника.

Задание 3. В MS Excel заполнить таблицу:

Сведения о зачислении в университет

ФИО абитуриента	Математика	Русский язык	Инфор-матика	Общий балл	Ср. балл	Зачисление
Иванов И.И.	58	65	98
Петров П.П.	50	90	40
Буль В.В.	10	58	92
Соркин Е.П.	59	68	83
Мороз А.Л.	96	92	85
Гусев Р.И.	65	81	81
Мороз Д.Л.	72	50	56
Гвоздев С.А.	75	52	70
Козлов Н.Н.	71	82	83
Средний балл за экзамен

Вычислить суммарное количество баллов для каждого абитуриента, подсчитать средние значения по каждому абитуриенту и экзамену. В столбце Сообщение о зачислении использовать функцию ЕСЛИ для сообщения «принять», если сумма баллов больше проходного (>181), и сообщение «отказать» в остальных случаях. Определить количество абитуриентов, принятых в вуз. Построить столбиковую гистограмму по общему баллу.

Решение:

Откроем рабочую книгу MS Excel: Пуск – Все программы – Microsoft Office – Microsoft Excel.

Далее необходимо поменять название рабочего листа: щелкнем правой кнопкой мыши внизу по названию Лист1, выберем пункт Переименовать и введем с клавиатуры новое имя Пример1.

Введем в таблицу исходные данные:

1. щелкнем по ячейке А1 и введем название таблицы: Сведения о зачислении в университет

2. в ячейки А3:G3 введем шапку таблицы 1;

3. в ячейки А4: А12 введем ФИО студентов;

4. в ячейки В4:D12 введем числа, которые представляют собой полученные на экзаменах индивидуальные баллы студентов;

5. в ячейке Е4 необходимо посчитать общий балл студента Иванова, который равен сумме баллов по всем дисциплинам, т.е. B4+C4+D4. Для этого выделим ячейку Е4 (один раз щелкнув по ней левой кнопкой мыши) и введем по-английски с клавиатуры следующую формулу: =B4+C4+D4 и нажмем Enter для расчета. Это ручной способ ввода формул.

6. Освоим автоматический способ ввода формулы. Выделим ячейку Е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 предусмотрены разнообразные средства, с помощью которых вы можете по своему вкусу оформить таблицу. То есть, изменить шрифт, цвет, заливку и границы.