Обработка данных средствами электронных таблиц MS Excel 2007

1.2 Цель:

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

ЧАСТЬ Л/Р №1

1.3 Общие сведения:

Создание электронных таблиц предполагает заполнение и редактирование документа. Базовые команды формирования таблиц можно разбить на две группы:

- команды, изменяющие содержимое ячеек (ввод и редактирование
данных, очистка, копирование);

- команды, изменяющие структуру таблицы (удаление, вставка,
перемещение фрагментов таблицы).

Выполнение этих команд требует определенных навыков, связанных с выделением блоков ячеек, строк и столбцов.

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

Чтобы выделить два диапазона ячеек, нужно выделить сначала первый диапазон, нажать на клавишу Ctrl и, удерживая ее нажатой, выделить второй диапазон.

Чтобы выделить отдельный столбец таблицы, нужно установить курсор на букве, обозначающей столбец, в верхней части рабочего листа и щелкнуть левой клавишей мыши. Аналогично выделяется строка таблицы. Если нажать левую клавишу мыши и, не отпуская ее, " протащить" по номерам строк, то эти строки будут выделены.

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

1) указатель мыши устанавливается в правый нижний угол копируемой ячейки,
нажимается левая клавиша мыши и, не отпуская клавишу, протаскивается на
нужное место;

2) указатель мыши устанавливается на копируемую ячейку, вызывается
контекстное меню правой клавишей мыши и выбирается команда
Копировать, затем выделяется нужный диапазон ячеек и нажимается
клавиша ввода команды;

3) в основном меню выбирается команда Правка, затем в раскрывшемся меню
команда Копировать.

Обычно в ячейке находятся числовые значения, а формула не видна. Увидеть формулу в отдельной ячейке можно, если перевести эту ячейку в режим редактирования клавишей F2. Можно сделать видимыми все формулы таблицы одновременно, если перейти в режим отображения формул. Обычно в исходном состоянии (по умолчанию) таблица находится в режиме отображения значений, вычисленных по формулам.

Для заполнения таблицы с использованием абсолютной адресации существуют два способа:

1) «Замораживание» адреса ячейки - использование символа $. Можно
«заморозить» только номер строки (например, F$5) или только имя столбца
($F5). Чтобы сделать абсолютным адрес ячейки, нужно значок $ писать
дважды ($F$5).

2) Использование имен ячеек.

Ячейке назначается имя, которое используется затем в формулах. Имя должно начинаться с буквы русского или латинского алфавита и не должно быть похоже на адрес ячейки, оно не может содержать пробела. Для этого выбирается команда Вставка из основного меню, затем пункт Имя/Определит ь.

Для оформления документов используется форматирование таблицы. Можно использовать различные шрифты, добавлять новые столбцы, выравнивание, автозаполнение, перемещение ячеек, изменять Формат ячеек с помощью контекстного меню, автоформатирование.

1.4 Постановка задачи:

На данной практической работе вам необходимо составить штатное расписание хозрасчетной больницы.

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

Пусть известно, что для нормальной работы больницы нужно 5-7 санитарок, 8-10 медсестер, 10-12 врачей, 1 заведующий аптекой, 3 заведующих отделениями, 1 главный врач, 1 заведующий хозяйством и 1 заведующий больницей.

За основу берется оклад санитарки, а все остальные вычисляются исходя из него. Каждый оклад является линейной функцией от оклада санитарки: А*С+В, где С - оклад санитарки, А и В - коэффициенты.

Например, медсестра должна получать в 1, 5 раза больше санитарки (А=1, 5, В=0);

- врач - в 3 раза больше санитарки (В=0, А=3);

- зав. от делением - на 30$ больше, чем врач (А=3, В=30);

- зав.аптекой - в 2 раза больше санитарки (А=2, В=0);

- зав.хозяйством - на 40$ больше медсестры (А=1, 5, В=40);

- гл.врач - в 4 раза больше санитарки (А=4, В=0);

- зав.больницей - на 20$ больше гл. врача (А=4, В=20).

Задав количество человек на каждой должности, можно составить уравнение: N1(A1*C+B1)+N2(A2*C+B2)+...+N8(A8*C+B8)=(Сумма фонда з/п), где N1-количество санитарок, N2-количество медсестер и т.д.

В этом уравнении известны А1...А8 и В1...В8, а неизвестны С и N1...N8. Решить такое уравнение известными методами не удастся, его можно решить путем подбора.

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

1.5 Ход работы:

Начало

1.Заполните таблицу.

Таблица 1.1

А	В	С	D	Е	F	G
коэфф. А	коэфф. В	должность	З/П сотрудн.	количество сотрудн.	суммарная зарплата	зарплата санитарки
		санитарка
		медсестра
		врач
		зав. отделением
		зав. аптекой
		завхоз
		главврач
		зав. больницей
		Суммарный месячный фонд зарплаты

2. В столбце D вычислите заработную плату для каждой должности.

Заработная плата вычисляется по формуле: A*C+B. В таблице коэффициенты А и В находятся в столбцах А и В, а С - зарплата санитарки указана в ячейке Н3.

Обратите внимание! Столбец D должен заполняться формулами с использованием абсолютной ссылки на ячейку Н3.То есть Вы должны ячейке Н3 присвоить имя С, в ячейку D3 занести формулу =A3*C+B3 и скопировать эту формулу из ячейки D3 в D4: D10.

3. В столбце F вычислите заработную плату всех сотрудников данной
должности, т.е. в ячейку F3 занесите формулу = D3*E3 (< зарплата> *< кол-во
сотрудников> ) и скопируйте ее в F4: F10, установив в столбцах D и F формат
0, 00.

4. Определите суммарный месячный фонд заработной платы, т.е.
просуммируйте столбец F c помощью кнопки суммирования и переместите
значение суммы в ячейку F12.

5. Составьте штатное расписание. Вносите изменения в зарплату санитарки
или меняйте количество сотрудников в ячейках Е3: Е5 до тех пор, пока

полученный суммарный месячный фонд зарплаты не будет равен заданной сумме.

6. Составьте штатное расписание с использованием функции автоматизации расчетов.

Подбор параметра

Порядок действий:

а) выбрать меню Сервис/Подбор параметра;

б) в списке Установить в ячейке указать адрес ячейки реального суммарного
фонда ($F$12);

в) в списке Значение - сумму фонда з/п, к которой нужно приблизиться;

г) в списке Изменить ячейку — указать ячейку, в которой указана базовая з/п
($H$3).

7. Составьте несколько вариантов штатного расписания с использованием функции Подбор параметра и оформите их в виде таблицы, изменяя количество сотрудников на должностях санитарки, медсестры и врача и подобрав зарплату санитарки в новых условиях.

Варианты штатного расписания

Таблица 1.2

Варианты	Должность	Количество сотрудников	Базовая зарплата (санитарки)

вариант 1	санитарка
	медсестра
	врач
....

8. Проанализируйте полученные варианты и выберите и оформите один из них в виде таблицы (таблица 1.3).

В таблице оставить столбцы C, D, E, F, а столбцы A, B, H выделить и выбрать пункт меню Формат/Столбец/Скрыть.

⇐ Предыдущая 1 2 3 456 7 8 9 10 Следующая ⇒