Операции над документами

Лабораторная  работа 1: «ОПЕРАЦИИ НАД ДОКУМЕНТАМИ»

 

1.    Ввести  таблицу следующего вида

Фамилия	Оклад	Аванс	Пенсионный фонд	Подоходный налог	К выдаче
Иванов	4000
Антонов	1500
Борисов	3800
Петров	2200
Лазарев	2000
Николаев	5000
ИТОГО:

2. Выделить ячейки шапки таблицы, щелкнуть по выделенным ячейкам правой кнопкой мыши и в появившемся контекстном меню выбрать опцию Формат ячеек. Перейти на вкладку выравнивание и задать для текста ячеек шапки таблицы формат Полужирный, Перенос, по словам, Выравнивание по центру (по горизонтали и по вертикали).
3. В ячейку А12 ввести текст «Минимальный размер оплаты труда», а в ячейку Е12 ввести действующий норматив минимального размера оплаты труда (2000 рублей).
4. Произвести расчет незаполненных граф следующим образом:

• аванс составляет 40% от оклада;

• отчисления в Пенсионный фонд равны 1% от оклада;
• подоходный налог - 13%;
• последняя графа равна разнице между окладом и всеми удержаниями.

5. Выполнить расчет итоговой строки, воспользовавшись кнопкой Автосумма на панели инструментов.

6. Выделить ячейки, содержащие числа, щелкнуть по выделенным ячейкам правой кнопкой мыши и в появившемся контекстном меню выбрать опцию Формат ячеек. Задать для ячеек формат Денежный. В случае необходимости увеличить ширину столбцов.
7. Вставить новый столбец перед столбцом с фамилиями, для этого щелкнуть правой кнопкой мыши по заголовку столбца, содержащего фамилии и в контекстном меню выбрать Добавить ячейки. В шапке таблицы ввести «Номер п/п». Используя Автозаполнение, автоматически пронумеровать фамилии. Для этого ввести первых два номера, затем выделить ячейки с этими номерами, протащить маркер заполнения (небольшой черный квадрат в правом нижнем углу выделенного блока, при наведении на который указатель принимает вид черного креста) по ячейкам, которые требуется заполнить, и отпустить кнопку мыши.
8. Расчертить таблицу, как показано на рисунке, для этого выполнить команду Формат→Ячейки, перейти на вкладку Граница и нажать кнопки Внешние и Внутренние.
9. Откорректировать ячейку с фамилией «Иванов». Заменить эту фамилию фамилией «Иванова».
10. Изменить фамилию «Борисов» на «Васильев».
11. Изменить числовое значение оклада Ивановой.
12. При включенном режиме вычислений вручную изменить значения окладов Борисова, Петрова, Лазарева, Николаева. Произвести перерасчет остальных граф.
13. В начало листа вставить две пустых строки. Для этого щелкнуть правой кнопкой мыши по заголовку строки, в контекстном меню выбрать Добавить ячейки. Повторить операцию два раза. В первую строку ввести заголовок таблицы «Расчетная ведомость» и расположить по центру всей таблицы.
14. Подготовить документ к печати: ввести в верхний колонтитул свою фамилию, а в нижний — текущую дату. Выполнить команду Вид→ Колонтитулы. Сначала нажать кнопку Создать  верхний колонтитул, создать его, нажать Ок, затем нажать кнопку Создать  нижний колонтитул, создать его.
15. Проверить полученный результат в режиме предварительного просмотра.

 

Лабораторная работа 2: «РАБОТА  С БОЛЬШИМИ ТАБЛИЦАМИ»

1.    Создать  новую книгу и ввести таблицу:

Структура доходов и расходов фирмы

Номер п/п	Показатели	Год				Итого за год
		1 кв.	2кв.	3 кв.	4кв.
	Продано единиц	3592	4390	3192	4789
	Торговые доходы	143660	175587	127700	191540
	Торговые расходы	89789	109742	79812	119712
	Валовая прибыль
	Расходы на зарплату	8000	8000	9000	9000
	Расходы на рекламу	10000	10000	10000	10000
	Накладные расходы фирмы	21549	26338	19155	28732
	Общие затраты
	Производственная прибыль
	Удельная  валовая прибыль

• шапку таблицы ввести в две строки, задав для ячеек, содержащих названия граф, расположение по центру столбца;
• переопределить ширину столбцов («Номер п/п») и («Показатели»);
• названия граф («Номер п/п») и («Итого за год») ввести в две ячейки;
• название графы («Показатели») расположить в две ячейки сверху вниз, выполнив действия;
• значение ячейки («Год») расположить по центру относительно по кварталов;
• автоматически пронумеровать все показатели в столбце А, задать для чисел столбца А выравнивание по центру.

2. Выполнить необходимые расчеты:

• данные по строке «Валовая прибыль» рассчитать как разность между торговыми доходами и торговыми расходами;
• данные по строке «Общие затраты» получить как сумму трех предыдущих строк;
• данные по строке «Производственная прибыль» получить как разность между валовой прибылью и общими затратами;
• данные по строке «Удельная валовая прибыль» получить как результат деления производственной прибыли на торговые доходы;
• данные в колонке «Итого за год» получить суммированием квартальных данных.

3. Задать для строки «Удельная валовая прибыль» Процентный формат, а для всех остальных строк — Формат числовой с разделителями групп разрядов.

4. Перед первой строкой вставить строку, ввести в нее название таблицы, задав для него более крупный жирный шрифт и расположение по центру всей таблицы.

5. Зафиксировать титулы таблицы (шапку и первый столбец). Для этого выделите ячейку, расположенную слева и выше того места, где требуется разбить лист. В нашем случае ячейку, содержащую текст «Продано единиц». Введите команду Окно→ Закрепить области. Перемещением по таблице отобразить на экране данные только двух столбцов: «Номер п/п», «Итого за год». Снять закрепление областей.

6. По данным строк «Торговые расходы» и «Валовая прибыль» построить гистограммы. Оформить заголовки и легенды диаграмм, расположить диаграммы рядом под таблицей. Для построения гистограммы «Торговые расходы» установить курсор в пустую ячейку. Щелкнуть левой кнопкой мыши по кнопке Мастер диаграмм , расположенной на панели инструментов, после чего появится диалоговая панель   Мастер диаграмм. В  списке Тип:  выбрать вариант Гистограмма. Щелкнуть по кнопке Далее, в появившемся окне ввести диапазон содержащий ячейку Торговые расходы и их значения за 1, 2, 3, 4 кварталы. Далее перейти на вкладку Ряд и в поле Подписи по оси X указать ячейки содержащие номера кварталов. Щелкнуть по кнопке Далее. В поле название диаграммы указать Торговые расходы. Щелкнуть по кнопке Далее. Обратить внимание, что переключатель уже установлен в такое положение когда диаграмма помещается на имеющемся листе. Щелкнуть по кнопке Готово. Аналогично строится диаграмма «Валовая прибыль».
7. На отдельном листе построить гистограмму со столбцами в виде цилиндров по данным всей таблицы (исключая графу «Итого за год»).

 

 

 Лабораторная работа 3: «ИСПОЛЬЗОВАНИЕ ВСТРОЕННЫХ ФУНКЦИЙ EXCEL»

1.    Создать  новую книгу и ввести таблицу

Отчет о деятельности страховых агентов

 

№ п/п	Фамилия	Объем страховых сделок				Комиссионное вознаграждение
		за III квартал	июль	август	сентябрь
	Иванов		400	120	390
	Федоров		250	200	270
	Антонов			370	410
	Орлов		290		360
	Смирнов		350	210	280
	Владимиров		410	380
	Егоров		360	290	340
	Громов			450	420
	Антонов		420	75	470
	Борисов		360		420
	Всего:
	Средний объем сделок
	Максимальный объем
	Минимальный объем
	Количество неотчитавшихся

• шапку таблицы ввести в две строки, задав для ячеек, содержащих названия граф, расположение по центру столбца (чтобы объединить ячейки и расположить текст в них по центру необходимо выделить их и нажать кнопку на панели инструментов);
• текст «Объем страховых сделок» расположить по центру четырех столбцов;
• названия граф «№ п/п», «Комиссионное вознаграждение» расположить в 2 ячейки по вертикали, объединив эти ячейки;
• для граф «№ п/п», «за III квартал», «Комиссионное вознаграждение» — задать перенос по словам (чтобы задать для ячеек перенос по словам необходимо эти ячейки выделить щелкнуть по выделенным ячейкам правой кнопкой мыши и в появившемся контекстном меню выбрать опцию Формат ячеек, перейти на вкладку выравнивание и установить флажок переносить по словам).

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

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

4. Ввести числовые данные в столбцы D, Е и F.

5. Автоматически пронумеровать все фамилии в столбце A. Для этого ввести первых два номера, затем выделить ячейки с этими номерами, протащить маркер заполнения (небольшой черный квадрат в правом нижнем углу выделенного блока, при наведении на который указатель принимает вид черного креста) по ячейкам, которые требуется заполнить, и отпустить кнопку мыши.
6. Сделать сортировку всей таблицы по фамилиям (без столбца А). Для этого выделить диапазон ячеек, содержащий фамилии, объем страховых сделок и комиссионное вознаграждение. Далее нажать кнопку Сортировка по возрастанию на панели инструментов .
7. Вычислить для каждого страхового агента объем сделок за III квартал как сумму сделок за июль, август и сентябрь. Для этого щелкнуть левой кнопкой мыши по кнопке вставка функции , после чего появится диалоговая панель   Мастер функций. В  списке Категория:  выбрать вариант Математические, а в списке Функция выбрать вариант СУММ. Щелкнуть по кнопке Ок, появится  диалоговая панель Аргументы функции. Указать диапазон суммирования, выделив его с помощью мыши. Щелкнуть по кнопке Ок.
8. Для вычисления значений строки Всего выделить диапазон суммирования и ячейку куда будет помещен результат, нажать кнопку Автосумма на панели инструментов.
9. Для вычисления значений строки Средний объем сделок щелкнуть по кнопке Вставка функции, после чего появится диалоговая панель   Мастер функций. В  списке Категория:  выбрать вариант Статистические, а в списке Функция выбрать вариант СРЗНАЧ. Щелкнуть по кнопке Ок, появится  диалоговая панель Аргументы функции. Указать диапазон, в котором будем искать среднее значение. Щелкнуть по кнопке Ок.
10. Для вычисления значений строки Максимальный объем щелкнуть по кнопке Вставка функции, после чего появится диалоговая панель   Мастер функций. В  списке Категория:  выбрать вариант Статистические, а в списке Функция выбрать вариант МАКС. Щелкнуть по кнопке Ок, появится  диалоговая панель Аргументы функции. Указать диапазон, в котором будем искать максимальное значение. Щелкнуть по кнопке Ок.
11. Для вычисления значений строки Минимальный объем щелкнуть по кнопке Вставка функции, после чего появится диалоговая панель   Мастер функций. В  списке Категория:  выбрать вариант Статистические, а в списке Функция выбрать вариант МИН. Щелкнуть по кнопке Ок, появится  диалоговая панель Аргументы функции. Указать диапазон, в котором будем искать минимальное значение. Щелкнуть по кнопке Ок.
12. Для вычисления значений строки Количество неотчитавшихся щелкнуть по кнопке Вставка функции, после чего появится диалоговая панель   Мастер функций. В  списке Категория:  выбрать вариант Статистические, а в списке Функция выбрать вариант СЧИТАТЬ ПУСТОТЫ. Щелкнуть по кнопке Ок, появится  диалоговая панель Аргументы функции. Указать диапазон, в котором будем производить подсчет. Щелкнуть по кнопке Ок.

200	8%
300	10%
400	12%
500	15%
600	18%
800	20%
1000	25%

Ввести тарификационную  таблицу, задав для диапазона столбца со значениями процентов процентный формат. Для этого выделить нужный диапазон, щелкнуть по выделенным ячейкам правой кнопкой мыши и в появившемся контекстном меню выбрать опцию Формат ячеек. Задать для ячеек формат Процентный. Установить число десятичных знаков 0. Щелкнуть по кнопке Ок.

 

 

 

 

 

 

 

14. Присвоить имя «тариф» диапазону яче<span class="dash041e_0431_044b_0447_043d_044b_0439__Char" style=" font-s