4

Лабораторная работа:N3 Электронная таблица MS EXcEL

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

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

План проведения

1. Общие правила работы

1. С помощью пиктограммы на Рабочем столе или пункта "Программы" Главого меню запустить приложение Microsoft Excel.

2. Освоить систему помощи программы EXCEL.

3. Присвоить рабочему листу Лист1 (Sheet1) имя Годовой доход.

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

5. 

   Неправильные данные отредактировать, установив курсор на нужную клетку, дважды щелкнув левой кнопкой мыши и внеся необходимые исправления.

6. Рассмотреть содержимое всех клеток и определить тип содержимого.

7. Скопировать формулу из ячейки Н8 в ячейки Н9:Н12, проделать то же для ячеек D13 и E13:H13.

Например:

- выделите ячейку H8 и перетащите маркер заполнения ячейки H8 к ячейке H12;

- проанализируйте изменения в формулах.

8. Добавить в ячейку I6 заголовок нового столбца таблицы: Налог. Вставить строку перед строкой "5". В ячейку А5 ввести текст: Доход, облагаемый налогом по минимальной ставке.

9. В ячейку Н5 ввести значение 50000.

10. Ввести в столбец Налог формулы, которые вычисляют размер подоходного налога по ставке 12%, если годовой доход составил не больше значений ячейки Н5 (50 тыс.руб.), иначе налог с суммы H5 берется 12%, а с остального дохода - 20%.

Например: =ЕСЛИ(H9>$H$5;$H$5*0,12+(H9-$H$5)*0,2;H9*0,12)

Формулу можно вводить непосредственно в ячейку I9, а можно использовать команду Функция (Function) раздела главного меню Вставка (Insert) (функция ЕСЛИ (IF) находится в категории Логические (Logical) экрана Мастер функций - Function Wizard) или запустить Мастер функций .

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

11. Присвоить ячейке H5 имя ПорогЗнач, исправить формулы так, чтобы в них содержался не адрес ячейки H5, а её имя.

12. Сохранить электронную таблицу в файле с именем test1 на диске H: в каталоге CALC. Выбрать команду Сохранить как… (Save As...) в меню Файл (File).

13. Выйти из EXCEL. Выбрать команду Выход (Exit) в меню Файл (File).

2. Настройка таблицы

1. Запустить EXCEL и загрузить файл test1 (меню Файл - File, команда Открыть - Open).

2. Применить к ячейкам D9:I14 формат # ##0"руб." :

а) Выделите ячейки D9:I14;

б) В меню Формат (Format) выберите команду Ячейки (Cell...) ;

в) Щелкните на ярлычке вкладки Число (Number). На этой вкладке находятся параметры форматирования чисел;

г) В окне списка Числовые форматы (Category) выделите строку Все форматы (All);

д) В поле Тип (Code) наберите # ##0"руб." и щелкните на кнопке ОК.

3. Изменить ширину столбцов в соответствии с видом выводимой информации.

4. Найти в помощи информацию о числовых форматах и объяснить вид формата, созданного в разд.2.2. Изменить формат так, чтобы нулевые значения показывались на экране красным цветом, а вместо отрицательных выводилось слово "ошибка".

5. Отформатировать таблицу вручную, сделать для ячеек таблицы правильное обрамление, шрифт, цвет шрифта, цвет фона, узор, выравнивание и т.д. (эти операции выполняются с помощью кнопок панели инструментов <Границы>-<Borders> и операции меню Формат/Ячейки - Format/Cells...)

6. Отформатировать данные ячеек С7:I14 с помощью команды Автоформат (AutoFormat):

а) Выделите ячейку внутри блока ячеек С7:I14;

б) В меню Формат (Format) выберите команду Автоформат (AutoFormat);

в) В окне Автоформат (Table Format) выделите строку Классический 3 (Classic 3) и щелкните на кнопке ОК.

7. Защитить ячейки листа Годовой доход от изменений. Для этого одновременно выделить все не защищаемые ячейки листа (Для выделения несмежных ячеек выделите первый блок ячеек, а затем нажмите клавишу Сtrl и, не отпуская ее, выделяйте другие ячейки группы). Затем указать, что они не будут защищаться, на вкладке Формат/Ячейки…Защита (Format/Cells.../Protection) снять маркер в пункте Защищаемая ячейка (Locked). После этого защитить лист, выбрав операцию Защита/Защитить лист… (Protection) в меню Сервис (Tools). Проверить защиту, а затем снять ее.

3. Построение диаграмм

3.1. В меню Вставка (Insert) выберите команду Диаграмма (Chart - As New Sheet).

3.2. Открывается диалоговое окно Мастер диаграмм-шаг 1 из 4 (СhartWizard-Step 1 of 4), в котором необходимо выбрать тип и вид диаграммы.

3.3. Выберите тип диаграммы Круговая (Pie), среди различных видов круговых диаграмм укажите первый и щелкните на кнопке Далее> (Next>).

3.4. Находясь на вкладке "Диапазон данных", выделите диапазон ячеек, в которых хранятся данные, выводимые в виде диаграммы.

3.5. Выделите ячейки С9:С13 и Н9:Н13 (для выделения несмежных ячеек выделите первый блок ячеек, а затем нажмите клавишу Сtrl и, не отпуская ее, выделяйте другие ячейки группы). В диалоговом окне Мастер диаграмм-шаг 2 из 4 (СhartWizard-Step 2 of 4) в поле Диапазон (Range) отразятся адреса выделенных ячеек. В поле просмотра изображен предварительный вид диаграммы.

3.6. Убедитесь, что в группе Ряды в (Data Series in) данных включена опция столбцах (In Columns).

3.7. На вкладке "Ряд" выберите ряд и введите поясняющие надписи. Щелкните на кнопке Далее> (Next>).

3.10. В окнеМастер диаграмм-шаг 3 из 4 (СhartWizard-Step 3 of 4) на вкладке "Заголовки" (Chart Title) наберите название диаграммы "Годовые выплаты", на вкладке "Легенда" (Legend) укажите место размещения поясняющих обозначений (легенды), а на вкладке "Подписи данных" укажите вид подписи для секторов, щелкните на кнопке Далее> (Next>).

3.11. В окне Мастер диаграмм-шаг 4 из 4 (СhartWizard-Step 4 of 4) задайте место размещения диаграммы и нажмите кнопку Готово (Finish). При выборе места размещения "отдельно" слева от листа Годовой доход будет вставлен лист Диаграмма1 (Chart 1). Диаграмма имеет вид, представленный на рисунке.

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

Например: трехмерную гистограмму для доходов Иванова, Сергеева и Белкина только за 2 и 4 кварталы.