Результаты решения задач обработки информации в excel

ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ БЮДЖЕТНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

ЛИПЕЦКИЙ ГОСУДАРСТВЕННЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ

Курсовая работа

По дисциплине: «Информатика»

На тему: Решение задач обработки информации в excel

вариант № 1

Выполнил:

студент гр. ФЗ-5-1 Антипова Е.

Принял:

Мартынова А.В.

Оценка___________ Дата_____________

Липецк

ПЛАН

1. Исходные данные

2. Выполнение работы в excel

3. Результаты решения задач обработки информации в excel

4. Графическое представление числовых данных

Библиографический список

Исходные данные

Доход

Номер	Звание	Оклад
	Профессор
	Доцент
	Доцент
	Ст. преп.
	Ст. преп.
	Ассистент
	Ассистент
	Профессор
	Ассистент
	Доцент

Анкета

ИНН	ФИО	Пол	Стаж	Количество детей
	Афонина Е.С.	Ж
	Говоров Н.Н.	М
	Грачева В.Н.	Ж
	Лукин С.П.	М
	Фетисов А.А.	М
	Сурина Н.П.	Ж
	Филина А.В.	Ж
	Шастин В.А.	М
	Попова С.С.	Ж
	Поленов Ю.И.	М

Задачи обработки информации

1. В таблице АНКЕТА подсчитайте средний стаж работы женщин.

2. В таблице АНКЕТА подсчитайте суммарное количество детей у сотрудников мужского пола.

3. Используя таблицы АНКЕТА и ДОХОД, рассчитайте квартплату, учитывая следующие условия:

• квартплата составляет 20% годового дохода;

• семья, имеющая 3 и более детей, оплачивает половину установленной квартплаты.

4. Отсортируйте в таблице АНКЕТА строки в алфавитном порядке по полю "Пол" и полю "ФИО".

5. В таблице АНКЕТА выделите записи с сотрудниками женского пола, имеющими стаж работы свыше 10 лет.

Выполнение работы в excel

Создание и заполнение таблиц «Доход », «Анкета».

ТЕХНОЛОГИЯ РАБОТЫ

Для этого запускаем программу EXCEL по команде Пуск, Программы, Microsoft Excel.Создадим рабочий лист, в который занесем информацию из таблицы ДОХОД. Для этого:

• в первой строке в ячейках Al, B1,C1, D1, E1 введем наименование заголовков столбцов: Номер, Звание, Оклад, Налог, Зарплата;

• в последующих ячейках каждого столбца введем исходные значения;

• установим курсор на имени текущего листа и вызовем контекстное меню,
нажав правую кнопку мыши;

• выберем опцию Переименовать,введем новое имя ДОХОД и нажмем Enter.

Создадим рабочий лист, в который занесем информацию из таблицы АНКЕТА. Для этого:

• перейдем на рабочий лист Лист 2;

• в первой строке в ячейках Al, Bl, Cl, Dl, El введем наименование заголовков столбцов: ИНН, ФИО, Пол, Стаж, Количество детей;

• в последующих ячейках каждого столбца введем исходные значения;

• установим курсор на имени текущего листа и вызовем контекстное меню,
нажав правую кнопку мыши;

• выберем опцию Переименовать,введем новое имя АНКЕТА и
нажмем Enter.

Сохраним рабочую книгу, выполнив команды Файл, Сохранить каки в диалоговом окне установим параметры: диск: С; имя файла: V1; и нажмем Сохранить.

ЗАДАЧА 1

В таблице АНКЕТА подсчитайте средний стаж работы женщин.

ТЕХНОЛОГИЯ РАБОТЫ

Выполним задание с помощью консолидации данных.

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

· на листе АНКЕТА установим курсор в область списка и выполним команду Данные, Фильтр, Автофильтр.В столбце «Пол» нажмем кнопку , из списка условий отбора выбрать условие «Ж».

Для подсчета среднего стажа у женщин выполним консолидацию отфильтрованных данных:

· установим курсор в ячейку D12 и выполним команду Данные, Консолидация;

· в открывшемся диалоговом окне «Консолидация» сделаем следующие установки:

Ø в строке «Функция» выберем «среднее»,

Ø нажмем кнопку в поле «Ссылка» и выделим мышью ячейку D2 с содержанием данных по стажу,

Ø нажмем кнопку в панели и перейдем в окно диалога;

Ø выполним команду Добавитьдля внесения выбранной ячейки, на которую производится ссылка, в список диапазонов;

Ø снова нажмем кнопку в поле «Ссылка», где также выделим ячейку D4, и так далее, вводя по очереди данные этой ячейки и всех последующих ячеек столбца «Стаж» с добавлением их в список диапазонов,

Ø Выполним команду «Ок».

ЗАДАЧА 2

В таблице АНКЕТА подсчитайте суммарное количество детей у сотрудников мужского пола.

ТЕХНОЛОГИЯ РАБОТЫ

Так как нам нужно подсчитать детей сотрудников мужского пола, то произведем фильтрацию данных:

· на листе АНКЕТА установим курсор в область списка и выполним команду Данные, Фильтр, Автофильтр.В столбце «Пол» нажмем кнопку , из списка условий отбора выбрать условие «М».

Устанавливаем курсор в нижную свободную ячейку столбца «Количество детей» и с помощью команды автосумма по столбцу получаем нужные данные.

Задача 3

Используя таблицы АНКЕТА и ДОХОД, рассчитайте квартплату, учитывая следующие условия:

• квартплата составляет 20% годового дохода;

• семья, имеющая 3 и более детей, оплачивает половину установленной квартплаты.

ТЕХНОЛОГИЯ РАБОТЫ

Для того чтобы рассчитать квартплату сделаем следующее:

· в таблице доход добавим столбцы – «За год»(Столбец F) и «Квартплата» (Столбец G),

· посчитаем величину налога для сотрудника №10 установив курсор в ячейку D2 столбца «Налог» по формуле (Оклад* 0,13) введя ее в строку формул

Учитывая налог посчитаем чистую зарплату:

· установить курсор в ячейке Е2 и ввести формулу ( = С2-D2);

· скопируем формулу в другие ячейки, установив курсор в правый нижний угол выделенной ячейки так, чтобы курсор принял форму крестика +и нажав левую кнопку мыши, протащим его до конца таблицы,

· подсчитаем зарплату за год введя в ячейку столбца «За год» формулу (=Е2*12),

· аналогично предыдущему скопируем значение формулы в другие ячейки данного столбца,

· подсчитаем квартплату по формуле (За год* 0,2) введя ее в строку формул

· скопируем формулу в другие ячейки данного столбца.

На листе ДОХОД выделим блок ячеек в диапазоне С1:G11, выполним команду Правка, Копировать;

· перейдем на лист 3, выделим ячейку А1 и выполнить команду Правка, Вставить;

· на листе АНКЕТА выделить блок ячеек Е1:Е11 выполнить команду Правка, Копировать;

· перейти на лист 3, выделить ячейку F1 и выполнить команду Правка, Вставить,

· введем новый столбец G – «Квартплата с учетом коэффициента» и установим курсор в ячейке G2.

Для решения данной задачи можно воспользоваться «мастером функций»:

· пользуясь «Мастером функций» выполняем команду Вставка, Функция;

· в появившемся диалоговом окне выбираем «имя» функции «ЕСЛИ», выполняем команду OK;

· во втором диалоговом окне в строчке «Логическое выражение» вводим условие (если значение ячейкиF2 > или = 3). Значение ячейки не вбиваем, а вводим в строку выделяя в таблице листа 3;

· В строке «Значение_если_истина» (т.к. если количество детей 3 и более, то сотрудники платят половину установленной квартплаты) вводим «Е2/2»;

· В строке «Значение_если ложь» - «Е2»;

· Выполняем команду ОК.

· скопируем формулу в другие ячейки, установив курсор в правый нижний угол выделенной ячейки так, чтобы курсор принял форму крестика +и нажав левую кнопку мыши, протащим его до конца таблицы.

Задача 4

Отсортируйте в таблице АНКЕТА строки в алфавитном порядке по полю "Пол" и полю "ФИО".

ТЕХНОЛОГИЯ РАБОТЫ

Установим курсор в область списка и выполним команду Данные, Сортировка.

В диалоговом окне «Сортировка диапазона» установим:

· в поле строки «Сортировать по» введем «Пол»,

· установим переключатель По возрастанию,

· выполним команду ОК.

Для того, чтобы произвести сортировку по ФИО, выполняем аналогичные команды, только в диалоговом окне «Сортировка диапазона» установим сортировку по ФИО.

Задача 5

В таблице АНКЕТА выделите записи с сотрудниками женского пола, имеющими стаж работы свыше 10 лет.

ТЕХНОЛОГИЯ РАБОТЫ

Для решения задачи нужно:

· на листе АНКЕТА установить курсор в область списка и выполнить команду Данные, Фильтр, Автофильтр.В столбце «Пол» нажать кнопку , из списка условий отбора выбрать условие «Ж»;

· в столбце «Стаж» нажать кнопку , из списка условий отбора выбрать «Условие». В появившемся окне «Пользовательский автофильтр», в верхнем левом поле окна установим «больше», а в правом - введем 10, выполним команду ОК.

Результаты решения задач обработки информации в excel

ЗАДАЧА 1

Задача 2

Задача 3

Задача 4

Задача 5