Основные сведения об MS EXCEL

15.09.07

15.09.07

15.09.07

15.09.2007.

ИТОГО		Доход
		Остаток

Рис.3. Поставки по районам

Ø «Доход» — общая сумма, полученная за весь продукт, проданный по розничной цене;

Ø «Отчисления» — сумма, определяемая с помощью Процента отчислений от дохода;

Ø «Налог» — часть дохода, вычисляемая Процентом налога от дохода;

Ø «Остаток» — сумма, равная разности дохода и вычетов налога и отчислений.

6. Используя «Формат по образцу» () указать денежные единицы.

7. Посчитать общий Доход и Остаток.

Задание 2

1. Используя данные рис.4 составить таблицу «Отчет по фирме».

15.09.07

Рис.4 Отчет по фирме Север за 2005 год

2. Таблица заполняется с использованием функции MS EXCEL Автозаполнение с последующей вставкой строк.

( Подробнеео функции Автозаполнение сказано в Приложении к данной лабораторной работе — раздел Основные сведения об MS EXCELпункт 3. Ввод и редактирование данных. )

3. Итоги по кварталам заполняются суммированием соответствующих ячеек по месяцам.

Рис. 5.

4. Создать 2 круговых диаграммы – по каждому товару (см.рис.5).

Задание 3

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

2. Автозаполнением заполнить столбец года.

3. Продлить таблицу до прошлого календарного года.

4. Величина капитала на первый рабочий год равна начальному капиталу.

5. Размер капитала на начало остальных периодов равен величине остатка на предыдущий отчетный год.

6. Столбцы «Доход» и «Расход» заполняются с использованием функции Прогрессия, находящейся в Правке\Заполнить. Шаг прогрессии взять равным («+» или «-») последним двум цифрам № студенческого.

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

8. Заполнить нижние строки при помощи «Мастера функций».

9. Отформатировать столбцы по образцу.

10. Построить Гистограмму, отражающую рост капитала по годам.

Рис.6 Общая таблица доходов по годам

Задание 4

Данное задание состоит из 4 упражнений на использование Логических функций (см.рис.8).

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

В Упражнении №1 (см.рис.8-а) столбцы «Доход» и «Расход» заполняются одновременно, используя функцию генерация случайных чисел. Интервал распределения — последние две цифры № студенческого10; число переменных в этом случае равно двум (см.рис.7).

Рис.7.

Столбец «Рабочие дни» заполняется при помощи автозаполнения.

Упражнение №1.

Рис.8-а

Упражнение №2.

Рис.8-б

В Упражнениях №2-№4 (см.рис.8-б-г) столбец «Цена изделия» заполняется аналогично столбцу «Количество» из Задания 1.

Упражнение №3.

Рис.8-в

В Упражнении №3 процент скидки зависит от кол-ва, покупаемых изделий: до 3 шт. — 0%, от 4 до 5 — 3%, свыше 5 шт. — 5% и вычисляется с помощью функции ЕСЛИ().

Упражнение №4.

Рис.8-г

В Упражнении №4 (см.рис.8-г) процент скидки зависит от стоимости покупаемых изделий: до 100 руб. — 0%, свыше — 5%.

Задание 5

1. Создать «Ведомость заработной платы преподавателей» (см.рис.9). Заполнить исходные данные.

2. Не забудьте вставить перед таблицей текущую дату.

Продолжение таблицы

Рис.9 Ведомость расчета заработной платы

3. «Дата Приема» — генерация случайных чисел (разумные пределы).

4. Стаж вычисляется, используя разницу между датой приема и текущей датой (данный результат MS EXCEL вычисляет в сутках). Полное число лет вычисляется функцией ЦЕЛОЕ().

5. Зарплата зависит от оплаты за час и количества отработанных часов.

6. Процент надбавки зависит от стажа:

до 3 лет — 0%; до 5 лет — 10 %; от 5 до 10 лет — 15 %; свыше 10 лет — 25 %.

7. Сумма надбавки = Зарплата * Процент надбавки.

8. Сумма налога зависит от итога: до 4300 руб. — 13%; свыше — 30%.

9. Таблицу необходимо отсортировать по алфавиту преподавателей (и/или по году приема на работу и по нагрузке). Обратить внимание на нумерацию строк в таблице — порядок должен остаться прежним.

10. Для представления преподавателю данного задания разделить окно таблицы (Окно\Разделить) на две части, выделив три первых столбца.

Задание 6

1. Таблица «Сметная ведомость» (см.рис.10) формируется из данных предыдущего задания.

2. Столбец «Ф.И.О.» копируется из Задания 5.

Рис.10 Сметная ведомость по медицинскому страхованию для сотрудников

3. Столбец «Стаж» определяется ссылкой на соответствующий столбец в предыдущем задании.

4. Столбец «Тип страхового полиса», выдаваемого сотруднику, зависит от стажа работы: стаж до 5 лет — тип полиса БТ, до 10 лет — БТС, 10 и выше — БТСС (использовать функцию ЕСЛИ()).

5. Для типов полисов БТС и БТСС доплата за услуги стоматолога 1000 руб.

6. Для типа БТСС дополнительно доплата за лечение в стационаре 2,5 тыс.руб.

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

Задание 7

Используя данные таблицы «Поставки» из Задания 1, составить сводные таблицы по образцам на рис.11. для выполнения задания использовать «Данные\Мастер сводных таблиц».

Рис.11-а Изменение розничных цен по отдельным категориям продуктов

Рис.11-б Доходы районов по отдельным видам поставок

Рис.11-в Изменение количества поставок по видам продукции

Рис.11-г Налоговые отчисления по районам

Приложение к лабораторной работе «Работа с электронными таблицами MS EXCEL»

1. Структура экрана

Окно приложения MS Excel обладает всеми элементами окон Windows: строка заголовка окна, кнопки управления размерами окна, системное меню. В главном меню Файл, Правка и т.д. функционально скомпонованы команды, необходимые для работы с электронными таблицами. Пункт меню Вид содержит список команд, определяющих внешний вид экрана. Команда Панели инструментов... позволяет вывести на экран/убрать с экрана панели инструментов: стандартную, форматирования, рисования и т.д. Вывод на экран Строки формул и Строки состояния также управляется из меню Вид. В режиме Полный экран на экране остается только строка главного меню. Команда Масштаб позволяет увеличить (напр., 150%) или уменьшить (напр., 75%) изображение на экране.

Рабочая область окна MS Excel представляет собой таблицу, состоящую из строк и столбцов, на пересечение которых находятся ячейки. Строки заголовков содержат наименования столбцов (А, В, С...) и строк (1, 2, 3...). Количество строк и столбцов в электронной таблицу ограничено только ресурсами компьютера. Для просмотра таблицы используются вертикальная и горизонтальная полосы прокрутки. Стиль адресных ссылок, наличие или отсутствие полос прокрутки, сетка и т.д. задаются командой Параметры из меню Сервис.

2. Рабочая книга

Документ, создаваемый в MS Excel — файл рабочей книги. Рабочая книга состоит из заданного по умолчанию (Сервис\Параметры\Основные) количества листов, ярлыки которых присутствуют на экране. Переход по листам осуществляется с помощью мыши. Состав рабочей книги может изменяться: листы добавляются (Вставка\Рабочий лист), удаляются (Правка\Удалить лист), переименовываются (Формат\Лист). Вы можете скопировать, переместить их в пределах данной рабочей книги или в другую книгу — Правка\Переместить или Скопировать. Команда Формат\Лист\Скрыть или Показать позволяет не выводить рабочий лист на экран или, наоборот показать его. Список этих команд приведен также в контекстном меню, которое вызывается щелчком правой кнопки мыши на ярлыке листа (см. Рис.1).

Рис.1.

3. Ввод и редактирование данных

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

Для ввода данных необходимо установить курсор экрана в соответствующую ячейку, ввести данные с клавиатуры и закончить ввод нажатием клавиши Enter. Одни и те же данные можно ввести сразу в несколько ячеек: выделив нужные ячейки, введите данные в одну из них, а затем нажмите Enter. Для ввода данных в интервал ячеек выделите интервал. Затем последовательно вводите данные в ячейки этого интервала.

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

а) б)

Рис.2.