Лабораторная работа №2 Проектирование расчётов на рабочем листе в среде


Скачать 197.35 Kb.
НазваниеЛабораторная работа №2 Проектирование расчётов на рабочем листе в среде
Дата публикации06.04.2013
Размер197.35 Kb.
ТипЛабораторная работа
userdocs.ru > Математика > Лабораторная работа

Лабораторная работа №2

Проектирование расчётов на рабочем листе в среде

табличного процессора Excel.

Краткие теоретические сведения.


Типы входных данных. Автоматизация ввода.

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

Символьные (текстовые) данные могут включать в себя алфавитные, числовые и специальные символы.

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

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

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

Ввести функцию можно несколькими способами:

  • с помощью ^ Мастера функций;

  • непосредственно введя в ячейку нужную формулу с клавиатуры.

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

ДД – МММ – ГГ (04-Янв-01);

МММ – ДД – ГГ (Янв-04-01).

Ввод данных осуществляется непосредственно в текущую ячейку или в строку формул. Содержимое строки формул всегда соответствует содержимому текущей ячейки, так что оба метода ввода равноправны. Так как таблицы часто содержат повторяющиеся или однотипные данные, программа Excel предоставляет средства для автоматизации ввода. К числу предоставляемых средств относятся:

  • автозавершение;

  • автозаполнение числами;

  • автозаполнение формулами.

Автозавершение применяют при вводе в ячейки одного столбца рабочего листа повторяющихся текстовых строк. Чтобы использовать возможности средства автозавершения, заполненные ячейки должны идти подряд, без промежутков между ними. В ходе ввода текста в очередную ячейку программа проверяет соответствие введённых символов строкам, имеющимся в этом столбце выше. Если обнаружено однозначное совпадение, введённый текст автоматически дополняется. Нажатие клавиши Enter подтверждает операцию автозавершения, в противном случае ввод можно продолжать, не обращая внимания на предлагаемый вариант.

Можно прервать работу средства автозавершения, оставив в столбце пустую ячейку.

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

Автозаполнение формулами. Эта операция выполняется так же, как автозаполнение числами. Ее особенность заключа-ется в необходимости копи-рования ссылок на другие ячейки. В ходе автозаполнения во внима-ние принимается характер ссылок в формуле; относи-тельные ссылки изменяют- ся в соответствии с относительным расположением копии и оригинала, а абсолютные ссылки остаются без изменений.

Абсолютная и относительная адресация.

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

Таким образом, относительная ссылка – это изменяющийся при копировании и перемещении формулы адрес ячейки, содержащий исходное данное.

При абсолютной адресации адреса ссылок при копировании формулы не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как постоянная (не табличная).

Таким образом, абсолютная ссылка - это не изменяющийся при копировании и перемещении формулы адрес ячейки, содержащий исходное данное.

Для указания абсолютной адресации вводится символ $. Различают два типа абсолютной ссылки: полная и частичная.

Полная абсолютная ссылка указывается, если при копировании или перемещении адрес клетки, содержащий исходное данное, не меняется. Для этого символ $ ставится перед наименованием столбца и номером строки, например, $В$5, $АА$220 – полные абсолютные ссылки.

Частичная абсолютная ссылка указывается, если при копировании и перемещении не меняется номер строки или наименование столбца. При этом символ $ в первом случае ставится перед номером строки, а во втором – перед наименованием столбца, например, В$5, АА$220 – частичная абсолютная ссылка, не меняется номер строки; $В5, $АА220 – частичная абсолютная ссылка, не меняется наименование столбца.

Для изменения способа адресации при редактировании формулы надо выделить ссылку на ячейку и нажать клавишу F4.

Форматирование данных. Условное форматирование.

Форматирование данных – выбор формы представления числовых или символьных данных в ячейке: устанавливаются размер, стиль шрифта, выравнивание данных.

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

Рассмотрим наиболее распространенные форматы представления числовых данных.

        1. Общий (основной) формат используется по умолчанию, обеспечивая запись числовых данных в ячейках в том же виде, как они вводятся, или вычисляются.

        2. Числовой формат (формат) с фиксированным количеством десятичных знаков обеспечивает представление числа в ячейках с заданной точностью, определяемой установленным количеством десятичных знаков после запятой. Например, если установлен режим форматирования, включающий два десятичных знака, то вводимое в ячейку число 123 будет записано как 123.00, а число 0,123 – как 0.12.

        3. Процентный формат обеспечивает представление введенных данных в форме процентов со знаком % в соответствии с установленным количеством десятичных знаков. Например, если установлена точность в один десятичный знак, то при вводе числа 0,123 на экране появится 12.3%

        4. Денежный формат обеспечивает такое представление чисел, где каждые три разряда разделены запятой. При этом пользователем может быть установлена определенная точность представления: с округлением до целого числа или в два десятичных знака. Например, введенное число 12345 будет записано в ячейке как 12,345 (с округлением до целого числа) или 12,345.00 (с точностью до двух десятичных знаков).

        5. Экспоненциальный формат, используемый для представления очень больших или очень маленьких чисел, обеспечивает представление вводимых чисел в виде двух компонент: мантиссы и порядка числа. Например, введенное число 12345 будет записано в ячейке как 1.2345Е+04 (если установленная точность составляет четыре разряда) или как 1.23Е+04 (при точности в два разряда); число 0,0000012 в научном формате будет иметь вид 1.2Е - 06 (при точности в один разряд).

Чтобы произвести форматирование чисел в ячейках, необходимо их выделить, а затем выбрать команду меню Формат – Ячейки, а в появившемся диалоговом окне – вкладку Число.

Если ширина вводимого числа превышает ширину ячейки (колонки), ячейка заполняется символами # # # #, сигнализирующими о том, что ширина ячейки недостаточна для отображения данных.

При условном форматировании оформление ячеек зависит от их содержания. Например, рабочий лист содержит сводную экзаменационную ведомость студенческих оценок по итогам сессии. Если экзамены сданы без троек, соответствующая строка таблицы должна подсвечиваться одним цветом (например, зеленым), если у студента остались задолженности – другим (например, красным). Для этого используется команда меню Условное форматирование (Главная – Стили).

Графические возможности табличного процессора.

Представление данных в графической форме повышает наглядность полученных результатов и показывает соотношение различных значений и динамику их изменения. Построение графического изображения производится на основе ряда данных. Диаграмма сохраняет связь с данными, на основе которых она построена, и при обновлении этих данных автоматически меняет свой вид.

Чтобы создать в Excel базовую диаграмму, которую впоследствии можно изменять и форматировать, сначала надо ввести на лист данные для этой диаграммы. Затем просто выделить эти данные и выбрать нужный тип диаграммы на ленте интерфейса (вкладка Вставка, группа Диаграммы).

Основные этапы построения диаграммы:

  • выбор типа диаграммы;

  • выбор данных;

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

  • размещение диаграммы;

  • редактирование диаграммы.

Например, диаграмма может иметь такой вид:
Также можно проверить, какая связь существует между исходной таблицей данных и диаграммой. Для этого в исходной таблице заполните данные на нового сотрудника. Теперь перейдите на лист "Диаграмма" и проверьте, как новые данные отразились на диаграмме - новый сотрудник сразу же внесен в диаграмму.
^

Практическое задание


  1. Имеются четыре отдела: ОНК, АПС, ОТД, ТКБ. В сокращённых названиях отделов не следует искать какого-либо смысла и пытаться их расшифровать.

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

В таблицу ввести 20 записей (по пять записей на каждый отдел).


A

B

C

D

E

F

G

H

I

J

Фамилия

Имя

Отчество

Таб. номер

Пол

Дата

рож.

Отдел

Оклад

Дети

Адрес

Муравьёв

Сергей

Николаевич

855

М

07.06.56

ОНК




0

ул. Энгельса 39 кв.79

































  1. Рассчитайте величину оклада для каждого сотрудника по формуле:

Оклад = мин. зар. плата * разр. коэф.

2.1 Вставьте две пустые строки перед таблицей (Главная – Ячейки - Вставить – Вставить строки на лист).

2.2 В ячейку В1 поместите выражение мин. зар. плата, в ячейку D1 поместите значение мин. зар. платы: 800, а в ячейку Е1: руб.

2.3 Перед колонкой Оклад вставьте два пустых столбца, дайте им заголовки Разряд и Разрядн. коэф.;

      1. поставьте курсор на колонку Оклад;

      2. выполните команду Главная – Ячейки - Вставить – Вставить столбцы на лист дважды.

    1. Заполните столбец Разряд, присвоив каждому сотруднику разряд в диапазоне от 8 до 18.

    2. Создайте справочную таблицу (рис.1) на Листе 2.

    3. Ввнесённые данные выровняйте по центру.

    4. Выделите блок А1:В12 (Ctrl + *).

    5. В строке формул в поле имени (слева от поля ввода) ввести имя koef.

    6. Лист 2 переименуйте в koef.

    7. Перейдите к Листу1.

    8. Заполните столбец Разрядн.коэф., используя функцию ВПР.

Для этого из главного меню выберите пункт Формулы, на появившейся ленте – команду Вставить функции. Запускается Мастер функций.

Синтаксис функции ВПР имеет вид:

ВПР (искомое значение, таблица, номер столбца, интервальный просмотр)

В нашем случае искомое значение – это разряд (из основной таблицы). Это значение функция ищет в таблице (в нашем случае koef).

^ Номер столбца – это номер столбца таблицы (koef), из которого берутся значения коэффициентов, соответствующие определённому разряду (в нашем случае 2).

Интервальный просмотр – необязательный параметр. Если он равен ЛОЖЬ (вместо ЛОЖЬ можно ввести 0), то первый столбец таблицы (koef) может быть неупорядоченным, а функция ВПР ищет точное соответствие для искомого значения.

Итак, вводим в ячейку для первого сотрудника в поле ^ Разрядн .коэф. формулу =ВПР(H4;koef;2;0)


Разряд

Разрядн. коэф.

8

3,12

9

3,53

10

3,99

11

4,51

12

5,10

13

5,76

14

6,51

15

7,36

16

8,17

17

9,07

18

10,07





рис.1 Справочная таблица


    1. Скопируйте формулу для других сотрудников.

    2. Прочитайте о функции ВПР в Справке. Там приведены полезные примеры и много дополнительной информации.

    3. В ячейку поля Оклад для первого работника (J4) запишите формулу: =$D$1*I4

2.15. Скопируйте формулу на весь диапазон.

  1. Начислите каждому работнику премию в размере 10% оклада, при этом оставьте возможность изменения размера премии.

    1. вставьте пустую строку перед таблицей;

    2. в ячейку В2 поместите выражение премия(%);

    3. в ячейку D2 поместите величину премии: 10%;

    4. для ячейки D2 установите формат чисел Процентный (Главная – Вставить – Формат ячеек);

3.5 после колонки Оклад добавьте два пустых столбца, дайте им заголовки Премия и Всего;

3.6 произведите вычисления по формулам:

Премия = Оклад * величину премии;

Всего = Оклад + Премия

3.6.1. в ячейку поля Премия для первого работника (K5) запишите формулу =J5*$D$2 (например);

3.6.2. в ячейку поля Всего для первого работника запишите формулу = J5+ K5 (например);

3.6.3. выделите ячейки K5 и L5 и скопируйте формулы, записанные в них, на весь диапазон.

4. Закрепите «шапку» и «боковину» таблицы

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

    1. Установите курсор в ячейку С5.

    2. Выберите команду меню Вид – Окно - Закрепить области - Закрепить области

4.3 Чтобы убрать закрепление, выполните команду Вид – Окно - Закрепить области - Снять закрепление областей

  1. Отформатируйте таблицу.

    1. Создайте обрамление таблицы

      1. Выделите таблицу.

      2. Щёлкните по ней правой кнопкой мыши, в открывшемся меню выбрать пункт Формат ячеек - Границы либо выполните команду Главная – Шрифт -Границы

    2. Содержимое полей Таб. номер, Пол, Отдел, Разряд, Разрядн.коэф., Оклад, Премия, Дети выровняйте по центру.

    3. Для величин, хранящихся в полях Таб. номер, Разряд, Дети, задайте число знаков после запятой равным 0; для полей Разрядн. коэф., Оклад, Премия, Всего – два знака после запятой.

    4. Вычисляемые данные выделите красным цветом.

    5. Для «шапки» таблицы установите следующие параметры шрифта: начертание – полужирный, размер шрифта 11, выравнивание – по центру; для остальной таблицы: начертание – обычный, размер шрифта 12.

  1. Осуществите защиту листа.

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

6.1 Выделите ячейки, содержащие исходные данные, то есть «не защищаемые» ячейки.

6.2 Вызовите контекстно-зависимое меню, щёлкнув правой кнопкой мыши по выделенным ячейкам.

6.3 Выберите команду Формат ячеек – Защита, снимите флажок Защищаемая ячейка

6.4 Выберите в меню команду Рецензирование, из появившегося подменю – Защитиь лист (группа Изменения)

6.5 Снимите защиту: Рецензирование Изменения – Снять защиту листа

  1. Произведите имитацию печати (предварительный просмотр).

7.1 Нажмите кнопку Office, выберите пункт Печать – Предварительный просмотр.

7.2 Изучите назначение кнопок в окне предварительного просмотра.

7.3 Расположите таблицу на одном листе.

7.4 Нажмите кнопку Закрыть.

  1. Присвойте каждому сотруднику порядковый номер

    1. Вставьте пустой столбец перед списком.

    2. Например, в ячейку А4 поместите №, в ячейку А5 – число 1, а в ячейку А6 – число 2.

    3. Выделите ячейки А5 и А6 и мышкой, схватив за правый нижний угол выделенного прямоугольника, автоматически заполните требуемый диапазон до числа 20

  1. Рассчитайте итоговые значения по графам Оклад, Премия, Всего.

  2. Осуществите условное форматирование.

10.1 Выделите работников, у которых количество детей равно 0; больше 3.

10.1.1 Выделите значения поля Дети.

10.1.2 Выберите из главного меню команду Главная, из группы Стили команду Условное форматирование – Правила выделения ячеек.

10.2 Выделите работников, у которых Разряд больше 13.

  1. Постройте график зависимости оклада от разряда работника, графики распределения всего начислено, премия, оклад между работниками фирмы.

  2. Переименуйте Лист1 текущей рабочей книги в kadr.

  3. Сохраните рабочую книгу под именем kadr2 в своей папке в папке «Мои документы».

  4. Завершите работу с Excel.


^

Контрольные задания.


Необходимо:

  1. рассчитать таблицу;

  2. защитить от изменения исходные данные;

  3. построить диаграммы по данным таблицы.

Задание 1.

Таблица 1

Размер и структура продаж продовольственных продуктов

№ п/п

Наименование

Количество

(кг)

Цена

(руб/кг)

Сумма

(руб)

Структура продаж, %

1

Сахар

180

14

?

?

2

Крупа

330

10

?

?

3

Пшено

150

10

?

?

4

Макароны

87

12,5

?

?

5

Гречка

54

16,5

?

?

ИТОГО

?




?

100

1. Построить столбиковую диаграмму продаж по видам продуктов.

2. Построить круговые диаграммы структуры объемов продукции и суммы продаж.


Задание 2.

Таблица 2

Оборотная ведомость малоценных предметов в эксплуатации (тыс.руб)


Шифр группы предметов

Наименование

Остаток на начало месяца

Поступило

Выбыло

Остаток на конец месяца

91

Инструменты

240

18

25

?

93

Спецодежда

124

23

45

?

95

Хоз.инвентарь

125

25

54

?

97

Штампы общего назначения

34

12

23

?

99

Штампы спец. назначения

23

13

26

?

ИТОГО

?

?

?

?




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

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

Задание 3.

Таблица 3

Размер и структура валовых сборов культур


№ п/п

Культура

Площадь, га

Урожайность, ц/га

Валовый сбор, т

Структура валового сбора, %

1

Озимая рожь

150

30,2

?

?

2

Озимая пшеница

300

25,5

?

?

3

Ячмень яровой

120

28,7

?

?

4

Овес

65

18,4

?

?

5

Зернобобовые

26

19,6

?

?

ИТОГО

?

?

?

100




  1. Построить круговую диаграмму структур площадей.

  2. Построить столбиковые диаграммы валового сбора и урожайности культур.

Задание 4.

Таблица 4

Структура производственных затрат фирмы



№ п/п

Виды затрат

Сумма, тыс. руб.

Структура затрат,

%

1

Сырье

69750

?

2

Вспомогательные и упаковочные материалы

2718

?

3

Запчасти

2829

?

4

Строительные материалы для текущего ремонта

4583

?

5

Стоимость аренды помещений

2500

?

6

Плата за электроэнергию, топливо и воду

4195

?

7

Оплата труда

14750

?

8

Естественная убыль продукта

836

?

ИТОГО




100




  1. Построить круговую диаграмму структуры производственных затрат фирмы.

  2. Построить столбиковую диаграмму суммы производственных затрат фирмы.



Задание 5.

Таблица 5

Структура себестоимости 1 ц помидоров в фирмах


№ п/п

Статьи затрат

«Янка»

«Ирадо»

«Арсенал»

тыс.руб

%

тыс.руб

%

тыс.руб

%

1

Семена

3,5

?

2,1

?

2,3

?

2

ГСМ

5,4

?

4,2

?

5,7

?

3

Удобрения

2,1

?

1,4

?

1,5

?

4

Ядохимикаты

3,6

?

3,0

?

2,9

?

5

Затраты на основные фонды

2,4

?

1,8

?

1,1

?

6

Прочие затраты

0,7

?

0,5

?

0,6

?

ИТОГО




100




100




100


1. Построить столбиковую диаграмму общей величины себестоимости по фирмам.

2. Построить круговую диаграмму структуры себестоимости для каждой фирмы.

  1. Построить столбиковые диаграммы величины себестоимости по статьям затрат.



^

Порядок оформления отчёта по лабораторной работе.





  1. Дайте ответы на контрольные вопросы.

  • Дайте понятие электронной таблицы и табличного процессора.

  • Каковы основные возможности электронной таблицы?

  • Какова обобщённая технология работы с электронной таблицей?

  • Назовите и дайте краткую характеристику средствам для автоматизации ввода.

  • Назовите возможные виды адресации.

  • Дайте понятие форматирования данных.

  • Назовите наиболее распространённые форматы представления числовых данных. Как изменить формат данных?

  • Что такое условное форматирование? Как его использовать?

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

Похожие:

Лабораторная работа №2 Проектирование расчётов на рабочем листе в среде iconЛабораторная работа №1
Работа в интегрированной среде borland pascal на примере программ линейной структуры
Лабораторная работа №2 Проектирование расчётов на рабочем листе в среде iconЛабораторная работа № Работа с массивами и записями
Получить представление о том, что такое массив и научиться разрабатывать алгоритмы решения задач с использованием массивов в среде...
Лабораторная работа №2 Проектирование расчётов на рабочем листе в среде iconЛабораторная работа №1
Цель работы: Изучение порядка и методики выбора двигателя в процессе проектирования на стадии технического предложения при разработке...
Лабораторная работа №2 Проектирование расчётов на рабочем листе в среде iconЛабораторная работа Технология создания трехмерных графических объектов...
...
Лабораторная работа №2 Проектирование расчётов на рабочем листе в среде iconЛабораторная работа №8 Тема : Turbo Pascal. Линейная структура
Цель занятия: научиться решать простейшие задачи линейной структуры в среде Turbo Pascal
Лабораторная работа №2 Проектирование расчётов на рабочем листе в среде iconРатманова Ирина Дмитриевна, Игнатьева Елена Сергеевна
Методички: «Проектирование баз данных и разработка приложений в среде ms sql server», «Проектирование баз данных и разработка приложений...
Лабораторная работа №2 Проектирование расчётов на рабочем листе в среде iconРатманова Ирина Дмитриевна, Игнатьева Елена Сергеевна
Методички: «Проектирование баз данных и разработка приложений в среде ms sql server», «Проектирование баз данных и разработка приложений...
Лабораторная работа №2 Проектирование расчётов на рабочем листе в среде iconЛабораторная работа №6 Тема : Текстовый процессор Word
Цель работы : Изучить основные технологические операции и процессы в среде текстового редактора Word для создания разнообразных текстовых...
Лабораторная работа №2 Проектирование расчётов на рабочем листе в среде iconЛабораторная работа 1
Б 35 Основы программирования на языке Object Pascal в среде delphi: Лаб практикум по курсам «Программирование» и «Основы алгоритмизации...
Лабораторная работа №2 Проектирование расчётов на рабочем листе в среде iconЛабораторная работа №1 «Проектирование базы данных «Успеваемость студентов»
Получить практические навыки проектирования реляционных баз данных, закрепить теоретические знания о типах данных, научиться связывать...
Вы можете разместить ссылку на наш сайт:
Школьные материалы


При копировании материала укажите ссылку © 2015
контакты
userdocs.ru
Главная страница