Обработка данных с помощью средств MS Excel (стр. 8 из 12)

· заданная должность и зарплата меньше заданной;

· заданный отдел и стаж работы больше заданного.

6. Выполнить сортировку данных по:

· отделам;

· фамилиям;

· отделам и фамилиям;

· отделам и зарплатам;

· отделам, должностям и фамилиям;

· отделам, должностям, зарплатам, фамилиям.

7. Используя Автофильтр, отобрать данные о сотрудниках:

· фамилии которых начинаются на заданную букву;

· зарплата которых больше заданной;

· стаж работы которых находится в заданном диапазоне;

· с заданной должностью и зарплатой в заданном диапазоне.

8. Используя расширенный фильтр, отобрать данные о сотрудниках:

· с зарплатой от 1000 до 1500 руб.;

· со стажем работы меньше 7 лет или с зарплатой меньше 1000 руб.

· со стажем работы от 5 до 10 лет и с зарплатой от 700 руб. до 1500 руб.;

· зарплата которых выше средней (использовать функцию СРЗНАЧ);

· зарплата которых выше средней, а стаж работы от 5 до 15 лет;

· зарплата которых выше средней, а стаж работы — меньше среднего.

· обо всех техниках конструкторского отдела, у которых либо стаж работы больше 5 лет, либо зарплата больше 900 руб.;

9. Скопировать рабочий лист под именем «Итоги». Открыть таблицу на листе «Итоги».

10.Используя инструмент подведения итогов, разбить список на группы по отделам и подвести промежуточные и общие итоги по полям «Фамилия» (операция Количество значений), «Зарплата», «Надбавка», «Премия», «Пенсионный фонд», «Налог», «Выплатить» (операция СУММА).

11.Открыть таблицу на листе Сведения о сотрудниках.

12.Создать сводную таблицу суммарных выплат по отделам, внутри отделов — по фамилиям. Для этого в макете поместить поля «Отдел» и «Фамилия» в область «Строка», а поле «Выплатить» — в область «Данные».

13.Скопировать лист со сводной таблицей. Внести изменения в копию, чтобы данные по каждому отделу выводились на отдельной странице (для этого поле «Отдел» переместить в область «Страница»).

14.Создать еще одну копию первой сводной таблицы. Изменить копию так, чтобы подчитывалось количество сотрудников в отделах (для этого удалить из области «Данные» поле «Выплатить» и поместить туда поле «Фамилия»).

Вариант 3

Составить таблицу для ведения учета основных средств предприятия (рис. 1).

При построении таблицы следует руководствоваться следующими правилами.

В ячейку А1 (дата расчета) может быть введено любое число в формате значений типа «дата» Ехсеl.

Значения в колонках А, В, С, D вводятся произвольно вручную со следующими ограничениями:

1) значения в колонке В больше нуля;

2) значения в колонке С больше нуля и не больше 100;

3) в колонку D вводятся значения в формате даты Ехсе1.

Число строк между заголовком таблицы и строкой «Итого» может быть произвольным.

Значения в колонке G рассчитываются по следующим правилам.

Если дата ввода в эксплуатацию (D) не заполнена или она больше, чем значение в ячейке А1, то срок эксплуатации (G) равен 0. В противном случае, срок эксплуатации равен числу месяцев от даты ввода в эксплуатацию (D) до даты расчета (А1). При составлении расчетной формулы следует руководствоваться следующим правилом расчета срока эксплуатации в месяцах:

СрокЭксплуатации=12*(ГодРасчета-ГодВводаВЭксплуатацию)+МесяцРасчета-МесяцВводаВЭксплуатацию

При записи формулы рекомендуется использовать функции Ехсеl ГОД() и МЕСЯЦ().

А 06.09.98	В	С	D	Е	F	G	Н
Наименование основного средства	Стоимость	Норма амортизации (%в год)	Дата ввода в эксплуатацию	Износ за месяц	Накопленный износ	Срок эксплуатации (месяцев)	Расчетный износ
Компьютер	4800	50	20.09.97
Автомобиль ЗАЗ	12000	12	21.03.95
Москвич	24000	12	22.09.93
Грузовик	72000	30	25.09.92
Офисный стол	1800	36	23.09.97
Офисные кресла	2400	36	24.09.94
Итого	xxxxxx	xxxxxx

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

Значения в колонке Н рассчитываются в соответствии с правилом:

РасчетныйИзнос=Стоимость*НормаАмортизации*СрокЭксплуатации/1200

Колонки G и Н чисто технологические и используются для упрощения записи формул расчета колонок Е и F.

Значения износа за месяц (колонка Е) рассчитываются следующим образом. Если расчетный износ (Н) больше стоимости, то износ за месяц равен 0, в противном случае износ за месяц определяется по формуле:

ИзносЗаМесяц=Стоимость*НормаАмортизации/1200

Если расчетный износ (колонка Н) больше стоимости, то накопленный износ (колонка F) равен стоимости, в противном случае накопленный износ равен расчетному износу.

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

Вариант 4

Составить таблицу (рис. 1). При построении таблицы руководствоваться следующими правилами.

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

Рис. 1. Структура и пример заполнения таблицы калькуляции полной себестоимости продукции

В строке 4 задаются формулы для расчета итогов по соответствующим колонкам.

Значения таблицы в колонках А («Продукция»), В («Материалы»), С («Зарплата»), начиная со строки 5, задаются вручную. В колонку А («Продукция») в произвольной текстовой форме вводятся данные о видах выпускаемой продукции. В колонке В («Материалы») вводятся данные о материалах, затраченных на производство данного вида продукции, а в колонку С («Зарплата») — сведения о зарплате, выплаченной за ее производство.

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

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

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

Если номер правила равен 2, то распределение накладных расходов производится пропорционально зарплате, выплаченной за производство данного вида продукции, а если номер правила равен 3, то пропорционально прямым затратам. Расчет доли накладных расходов, относимых на конкретный вид продукции, рассчитывается аналогично правилам, используемым при распределении пропорционально затратам материалов, но с использованием иной расчетной базы.

Очевидно, что сумма долей накладных расходов, относимых на все виды продукции в соответствии с правилами их расчета должна быть равна 100%.

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

Вариант 5

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

• шапку таблицы ввести в две строки, задав для ячеек, содержащих названия граф, расположение по центру столбца;

• переопределить ширину столбцов («Номер п/п») и («Показатели»);

• названия граф («Номер п/п») и («Итого за год») ввести в две ячейки;

• название графы («Показатели») расположить в две ячейки сверху вниз, выполнив действия;

• значение ячейки («Год») отцентрировать по кварталам. Расчертить таблицу, как указано на рис. 1.

3. Автоматически пронумеровать все показатели в столбце А, задать для чисел столбца А выравнивание по центру.

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

• данные по строке «Валовая прибыль» рассчитать как разность между торговыми доходами и торговыми расходами;

• данные по строке «Общие затраты» получить как сумму трех предыдущих строк:

• данные по строке «Производственная прибыль» получить как разность между валовой прибылью и общими затратами:

• данные по строке «Удельная валовая прибыль» получить как результат деления производственной прибыли на торговые доходы;

• данные в колонке «Итого за год» получить суммированием квартальных данных.

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

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

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

8. На листе с таблицей по данным строки «Торговые расходы фирмы» за первые три квартала построить разрезанную круговую диаграмму (при выделении данных для построения диаграммы использовать и названия кварталов).