Файл: Лабораторная работа ms excel Ввод и форматирование данных Цель работы отработка базовых навыков работы в ms.doc

Фон

Содержимое любой ячейки или блока может иметь необходимый фон (тип штриховки, цвет штриховки, цвет фона) Для задания фона используется кнопка панели  Цвет заливки

М ожно использовать команду меню ФОРМАТ, ЯЧЕЙКИ, вкладка ВИД, или аналогичную команду контекстного меню.

9. Задайте фон заполнения внутри таблиц - желтый, фон заполнения шапок таблиц - малиновый.

10. В результате получаем следующую таблицу.

11. Переименуйте лист Табулирование функции.
Задание 5. MS Excel: Использование абсолютной адресации

В этом задании Вы научитесь использовать абсолютную адресацию с использованием символа $.

1. 
   О ткройте новую книгу. Сохраните её в свою папку под именем Абсолютная адресация. Первый лист этой книги назовите Прайс-лист ($). На нем оформите таблицу, как показано на рисунке.
   
2. 
   В ячейку С2 вставьте функцию СЕГОДНЯ, которая показывает текущую дату. Эта функция относится к категории Дата и время.
   
3. 
   Для расчета цены в рублях попробуйте сначала использовать обычную формулу. Т.е. в ячейку С7 введите =В7*С4 и попробуйте автозаполнением распространить её на нижние ячейки. Посмотрите, что получилось.
   

4. 
   Объяснение: при копировании вниз автоматически увеличивается номер строки, т.е. в ячейке С8 будет формула =В8*С5 . Но т.к. ячейка С5 пуста, Excel не смог правильно вычислить формулу. Удалите скопированные формулы в ячейках С8:С14.
   
5. 
   В начальной формуле =В7*С4 используем абсолютную адресацию для ячейки С4, поскольку при копировании этот адрес должен оставаться неизменным. Исправьте формулу в ячейке С7 на следующую =В7*С$4. Знак доллара можете набрать на клавиатуре, а можете использовать клавишу F4. Для этого щелкните мышью в ячейке С7 в формуле так, чтобы курсор стоял в ссылке С4 и нажимайте клавишу F4 пока не получите нужную адресацию.
   
6. 
   Скопируйте эту формулу вниз Автозаполнением. Убедитесь, что расчет произведен правильно.
   

Задание 6. MS Excel: Использование имени ячейки

В

---

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

1. 
   В книге Абсолютная адресация скопируйте лист Прайс-лист ($). Его копию назовите Присвоение имени. На нем удалите содержимое ячеек С7:С14.
   
2. 
   Присвоим ячейке С4 имя. Выделите ячейку С4, выполните команду Вставка-* Имя—> Присвоить..., впишите имя ячейки kurs. Нажмите ОК.
   
3. 
   Теперь посмотрите, что в поле имени этой ячейки указывается не С4, a kurs. Теперь к этой ячейке можно обращаться по новому имени, которое не будет изменяться при копировании и автозаполнении, т.е. будет являться абсолютным именем ячейки.
   
4. 
   В ячейку С7 введите формулу =B7*kurs.
   
5. 
   Полученную формулу скопируйте вниз для всех товаров. Проверьте правильность вычислений.
   

Задание для самостоятельной работы

1. Создайте таблицу умножения, используя относительную и абсолютную адресацию:

	A	B	C	D	E	F	G	H	I	J
1		1	2	3	4	5	6	7	8	9
2	1
3	2
4	3
5	4
6	5
7	6
8	7
9	8
10	9

---

Начальные данные введите с помощью Автозаполнения.

В ячейку B2 введите формулу по смыслу (надо заполнить таблицу умножения). Автозаполнением протяните формулу вниз и, не снимая выделения, вправо. Если вы ввели формулу, верно, то должна получиться таблица умножения.

Переименуйте лист Таблица умножения.

2. Создайте следующую таблицу:

	A	B	C	D		E		F
1	НАРЯД-ЗАДАНИЕ НА ДОСТАВКУ ГРУЗА
2	Номер заявки	Наименование груза	Стоимостьгрузар		Расстояние км		этажа	Доставка р
3
4	1	Диван	4300		3		1
5	2	Книжный шкаф	3750		6		4
6	3	Холодильник	8500		10		5
7	4	Мягкая мебель	13200		5		8
8	5	Стол	1500		5		8
9	ВСЕГО
10	Наценка (%) зависит от стоимости груза				5
11	Наценка (р./км) зависит от расстояния				3
12	Наценка (р./этаж) зависит от N этажа				2,5

---

Формула в ячейке F4 вводится с учетом того, что стоимость доставки груза складывается из следующих трех величин (наценок):

• 
  5% от стоимости груза,
  
• 
  от расстояния(3 р./км),
  
• 
  номера этажа (2,5 р./этаж).
  

Заполните с помощью Автозаполнения оставшиеся ячейки.

Переименуйте лист Доставка груза.
Задание 7. Ссылки на ячейки других листов

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

1. Откройте новую книгу, сохраните её под именем Отдел кадров. Первый лист назовите Карточка. На нем оформите следующую таблицу. Не забудьте объединить ячейки и отформатировать их.



2. Предположим, что на предприятии работают пять сотрудников. Скопируйте содержимое листа Карточка 5 раз. Каждую из его копий назовите фамилией сотрудника. У вас должно получиться 5 новых листов, со следующими названиями: Маркова, Козлов, Матвеев, Паршуков, Малкова.



3. На всех пяти листах внесите сведения о сотрудниках: их фамилии' (такие же, как в названии листа), имена, отчества и другие данные придумайте самостоятельно.

4. Перейдите на новый лист. Переименуйте лист и назовите его Общие сведения. На нем создайте и оформите следующую таблицу:



5 . Рассчитаем искомые величины.

6. В ячейку ВЗ при помощи
Мастера вставьте функцию
СРЗНАЧ. В появившемся
окне аргументов функции
поставьте курсор в поле
ЧИСЛО1, затем перейдите
на лист Маркова и выделите ячейку В6. Затем поставьте курсор в поле ЧИСЛО2, перейдите на лист Козлов и выделите ячейку В6. И так для всех сотрудников. Посмотрите, как вписываются адреса ячеек с других листов. Затем нажмите ОК.

7. В результате в ячейке ВЗ должна получиться следующая формула:

=СРЗНАЧ(Маркова!В6,Козлов!В6,Матвеев!В6,Паршуков!В6,Малкова!В6)

8. В ячейке В4 нужно рассчитать средний стаж работы всех сотрудников, но учитывая что значение стажа работы на всех листах со сведениями о сотрудниках находится на строчку ниже, чем значение возраста, то в данном случае можно воспользоваться Автозаполнением. При помощи маркера Автозаполнения скопируйте формулу из ячейки

---

ВЗ в ячейку В4.

9. Аналогичным образов рассчитайте общий стаж работы сотрудников в ячейке В5, используя Автосумму на панели инструментов. В случае, если аргументы функции не появляются, тогда в самом начале строки формул нажмите кнопку мастера функций .

10. Затем с помощью Автозаполнения скопируйте полученную формулу в ячейку В6, чтобы посчитать общее количество дететй у сотрудников предприятия.

Задание 8. Ссылки на ячейки других листов и абсолютная адресация

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

1. Откройте книгу Расчеты.xls. На листе Статус семьи у нас имеется список сотрудников и количество их детей. Добавьте в эту книгу новый лист Вставка  Лист. Переименуйте его в Детские- компенсации. На нем оформите таблицу (часть данных скопируйте с листа Статус семьи).

2 . В ячейку С6 напишем формулу для расчета суммы детских компенсаций с учетом того, что размер компенсации на 1 ребенка находится в ячейке С3 а количество детей данного на другом листе. Для этого:

• 
  В ячейке С6 введите знак « = ».
  
• 
  Щелкните по ячейке С3.
  
• 
  Нажмите клавишу F4 на клавиатуре 2 раза, чтобы ссылка превратилась в С$3.
  
• 
  Введите с клавиатуры знак « * » .
  
• 
  Щелкните по ярлычку листа Статус семьи и выделите на нем ячейку С4 (с количеством детей данного сотрудника).
  
• 
  Нажмите Enter на клавиатуре.
  

У вас должна получиться следующая формула: =С$3*'Статус семьи'!С4

3. Автозаполнением скопируйте полученную формулу для всех сотрудников.

Задание для самостоятельной работы

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

Создайте таблицу по приведенному образцу на Листе 1:

---