ПРАКТИЧЕСКАЯ РАБОТА № 2 (1)

РЕШЕНИЕ ЗАДАЧИ ТАБУЛИРОВАНИЯ ФУНКЦИ

Цели работы:

· закрепить навыки по заполнению и редактированию таблицы;

· познакомиться со способами адресации;

· освоить некоторые приемы оформления таблиц.

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

Постановка задачи:

Вычислить значения функции y=k*(x^2-1)*(x^2+1) для всех х на интервале [-2,2] с шагом 0,2 при к=10.

Решение должно быть получено в виде таблицы:

 

№	Х	К	Y1=X^2-1	Y2=X^2+1	Y=K*(Y1/ Y2)

ЗАДАНИЕ 1. Прежде чем перейти к выполнению задачи, позна­комьтесь со способами адресации в Excel.

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

Ячеек и блоков

При обращении к ячейке можно использовать способы, опи­санные ранее, например В3_, А1:G9 и т.д. Такая адресация называ­ется относительной. При использовании подобной адресации в формулах Excel запоминает расположение относительно текущей ячейки.. Так, например, когда вы вводите формулу =В1+В2 в ячейку В4, то Excel интерпретирует формулу как «прибавить содержимое ячейки расположенной тремя рядами выше, к содержимому ячейки двумя рядами выше».

Если вы скопировали формулу =В1+В2 из ячейки В4 в С4, Excel также интерпретирует формулу как "прибавить содер­жимое ячейки, расположенной тремя рядами выше, к содержи­мому ячейки двумя рядами выше". Таким образом, формула в ячей­ке С4 изменит свой вид на =С1+С2.

Абсолютная адресация. Иногда при копировании формул вы пожелаете сохранить ссылку на конкретную ячейку или область. Тогда вам необходимо воспользоваться абсолютной ад­ресацией. Для ее задании необходимо перед буквой колонки и перед номером ряда напечатать символ $. Например: $В$4 или $C$2:$F$48 и т.д.

Смешанная адресация. Символ $ ставится только там, где он необходим, например: В$4 или $С2. Тогда при копировании один параметр адреса изменяется, а другой остается прежним.

 

ЗАДАНИЕ 2. Заполните основную и вспомогательную таблицы.

2.1.Заполните шапку основной таблицы, начиная с ячейки A1:

Шапка - это верхняя строка таблицы с названием столбцов (см. постановку задачи):

в ячейку A1 занесите № (название первого столбца таблицы);

в ячейку В1 занесите X (название второго столбца таб­лицы);

• в ячейку С1 занесите К (название третьего столбца таб­лицы)
• установите ширину столбцов такой, чтобы надписи были
  видны полностью.

2.2.Заполните вспомогательную таблицу начальными исход­ными данными, начиная с ячейки Н1:

 

 

Х0	step	К
-2	0,2

 

Где ХО - начальное значение X; step - шаг изменения X; к - коэффициент (const).

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

2.3.Используя функцию автозаполнения, заполните столбец А числами от 1 до 21, начиная с ячейки А2 и заканчивая ячейкой А22 аналогично тому, как заполнили блок ячеек годами в ра­боте №1.

2.4.Заполните столбец В значениями X:

· в ячейку В2 занесите =$Н$2

Это означает, что в ячейку В2 заносится значение из ячейки Н2 (начальное значение X), знак $ указывает на абсолютную ад­ресацию.

• в ячейку ВЗ занесите =B2+$I$2

Это означает, что начальное значение X будет увеличено на величину шага, которая берется из ячейки I2.

• заполните этой формулой ячейки В4;В22

Столбец заполнится значениями X от -2 до 2 с шагом 0,2.

2.5. Заполните столбец С значениями коэффициента К:

· в ячейку С2 занесите =$J$2

· в ячейку СЗ занесите =С2

Посмотрите на введенные формулы. Почему они так запи­саны?

· заполните этой формулой ячейки С4:С22.

Весь столбец заполнился значением 10.

2.6.Заполните столбец D значениями функции у1=х^2-1:

• в ячейку D2 занесите = В2*В2-1,
• заполните этой формулой ячейки D3:D22.

Столбец заполнился как положительными, так и отрицатель­ными значениями функции y1. Начальное значение 3 и конечное значение 3.

2.7.Аналогичным образом заполните столбец E значениями функции у2=х^2+1.

Проверьте! Все значения положительные: начальное 5, конеч­ное 5.

2.8.Заполните столбец F значениями функции у = к*(X^2-1)/(X^2+1)

· в ячейку F2 занесите =С2*(D2/Е2),

· заполните этой формулой ячейки F2: F22.

Проверьте! Значения функции как положительные, так и от­рицательные. Начальное значение 6. Конечное значение 6.

 

ЗАДАНИЕ 3. Понаблюдайте за изменениями в основной таблице при смене данных во вспомогательной.

3.1. Смените во вспомогательной таблице начальное значе­ние х, в ячейку Н2 занесите -5.

3.2.Смените значение шага, в ячейку I2 занесите 2.

3.3. Смените значение коэффициента, в ячейку J2 зане­сите 1.

Внимание! При всех изменениях данных во вспомогатель­ной таблице в основной таблице пересчет производится автоматически.

3.4.Прежде чем продолжить работу, верните прежние началь­ные значения во вспомогательной таблице:

step=0,2

к=10

x0=-2

 

ЗАДАНИЕ 4. Оформите основную и вспомогательную таблицы.

4.1.Вставьте 2 пустые строки сверху для оформления заго­ловков:

· установите курсор на 1 строку;

· выполните команды меню Вставка - Строки (2 раза).

4.2. Занесите заголовки:

· в ячейку А 1 Таблицы,

· в ячейку А2 Основная,

· в ячейку Н2 Вспомогательная.

4.3.Объедините ячейки А1:J1 и центрируйте заголовок «Таблицы»:

· выделите блок А1:J1

· используйте кнопку центрировать по столбцам панели инструментов Форматирование.

 

· аналогичным образом центрируйте заголовки: «основ­ная» и «вспомогательная».

4.4.Оформите заголовки определенными шрифтами:

 

Символы (шрифты)

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

- тип шрифта.

- размер шрифта

- начертание (жирный, курсив, подчеркнутый)

Можно воспользоваться командой Главная – Ячейки – Формат ячеек или щелкнуть правой кнопкой мыши и выбрать эту команду из кон­текстного меню. На экране появится диалоговое окно Формат яче­ек. В нем необходимо раскрыть вкладку Шрифт.

· Заголовку "Таблицы" установите шрифт Arial, высота
шрифта 14, жирный.

Используйте кнопки панели инструментов Форматирование;

• Заголовкам "Основная" и "Вспомогательная" установите
  шрифт Arial, высота шрифта 12, жирный.

Используйте контекстное меню Формат Ячеек - Шрифт;

• для шапок таблиц установите шрифт Arial, высота шриф­та 12, курсив.

Используйте контекстное меню.

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

4.7.Произведите выравнивание надписей шапок по центру.

 

Выравнивание

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

Для задания необходимой ориентации используются кноп­ки на панели инструментов Форматирование:

(Выровнять влево, Выровнять по центру, Выровнять вправо).

Можно воспользоваться командой Главная – Ячейки – Формат ячеек или щелкнуть правой кнопкой мыши и выбрать эту команду из кон­текстного меню. На экране появится диалоговое окно Формат яче­ек. В нем необходимо раскрыть вкладку Выравнивание.

4.8. Обрамите основную и вспомогательную таблицы.

Обрамление

Для задания обрамления используется кнопка на панели Форматирование.

Можно воспользоваться командой Главная – Ячейки – Формат ячеек или аналогичную команду из кон­текстного меню.

 

4.9.Задайте фон заполнения внутри таблиц - желтый, фон заполнения шапок таблиц — малиновый. Содержимое любой ячейки или блока может иметь необходи­мый фон (тип штриховки, цвет штриховки, цвет фона). Для задания фона используется кнопка панели Форматирование.

Можно воспользоваться командой Главная – Ячейки – Формат ячеек, вкладка Вид, или аналогичную команду контекстного меню.

Экран после выполнения работы должен иметь следующий вид:

 

 

 

ЗАДАНИЕ 5. Сохраните результаты своей работы на дискете, в личном каталоге. Имя файла work2_1.xls

 

ЗАДАНИЕ 6. Завершите работу с Excel.

 

ЗАДАНИЕ 7. Подведите итоги.

 

Проверьте:

умеете ли вы: · использовать, различные способы адресации; · оформлять символы; · производить выравнива­ние и обрамление; · изменять фон.

знаете ли вы, что такое:

· относительная адресация;

· абсолютная адресация;

· смешанная адресация.

 

 

Если нет, то еще раз внимательно перечитайте соответст­вующие разделы работы.

 

Предъявите преподавателю:

• краткий конспект;
• файл work2_1.xls на экране и на рабочем диске в личном каталоге.

 

 

ПРАКТИЧЕСКАЯ РАБОТА №2 (2)(Продолжение)

Цели работы:

• Познакомиться с использованием функций в Excel;
• Познакомиться с форматом чисел
• Научиться осуществлять защиту информации в таблице
• Научиться распечатывать таблицу;

 

ЗАДАНИЕ 1: Откройте файл work2_1.xls, созданный на прошлом уроке.

ЗАДАНИЕ 2: Защитите в таблице информацию, которая не изменяется (заголовки, полностью основная таблица, шапка вспомогательной таблицы).

Защита ячеек

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

Установка (снятие) защиты выполняется за два действия:

• отключают блокировку (защиту) с ячеек, подлежащих пос­ледующей корректировке;
• включают защиту листа или книги.

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

Разблокировка (блокировка) ячеек. Выделите необходимый блок. Выполните команду Формат Ячеек, Защита, а затем в диалоговом окне выключите (включи­те) параметр Защищаемая ячейка.

Включение (снятие) защиты с листа или книги. Выполните команду Файл – Сведения – Защитить книгу – Защитить текущий лист (для отключения – повторить операцию).

2.1. Выделите блок H4:J4 и снимите блокировку. Меню: Формат Ячеек – Защита – убрать знак в окне «Защищаемая ячейка»

2.2. Защитите лист. Файл – Сведения – Защитить книгу – Защитить текущий лист – Ок. В результате действий заблокируется вся основная таблица и шапка вспомогательной.

2.3. Попробуйте изменить значения в ячейках: В ячейке А4 с 1 на 10. Это невозможно. Значение шага во вспомогательной таблице с 0.2 на 0.5.Это возможно. В основной таблице произошел пересчет. Измените текст «step» в ячейке I3 на «шаг» Каков результат? Почему? Верните значение шага 0,2

ЗАДАНИЕ 3. Сохраните файл под старым именем. Воспользуйтесь кнопкой сохранить на панели инструментов Стандартная.

ЗАДАНИЕ 4.Снимите защиту листа. Файл – Сведения – Защитить книгу – Защитить текущий лист

ЗАДАНИЕ 5. Познакомьтесь с функциями пакета Excel

Функции

Функции предназначены для упрощения расчетов и имеют следующую форму: Y=f(X), где у - результат вычисления функ­ции, Х-аргумент, f-функция.

Пример содержимого ячейки с функцией: =A5+sin(C7), где А5 - адрес ячейки; sin() - имя функции, в круглых скобках указывается аргумент;

С7 – аргумент (число, текст и т.д.) в данном случае ссылка на ячейку, содержащую число.

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

МАКС(список) - возвращает из списка максимальное число.

МИН(список)- возвращает минимальное значение из списка аргументов.

СРЗНАЧ(список) - возвращает среднеарифметическое своих аргументов.

ЦЕЛОЕ(Х) - округляет аргумент X до ближайшего меньшего целого

ABS(X) - возвращает модуль (абсолютную величину ) числа X.

ЕХР(Х) - возвращает экспоненту заданного числа X

LN(X) - возвращает натуральный логарифм заданного числа X.
LOGIO(X) - возвращает десятичный логарифм заданного числа X.
LOG(X) -возвращает натуральный логарифм числа X.
SIN(X) - вычисляет синус угла X, измеренного в радианах.

СУММ(список) - возвращает сумму указанного списка.

Например, СУММ(А1:А300) => подсчитает сумму чисел в трехстах ячейках диапазона А1:А300.

Так как функция суммирования часто используется, то за ней закреплена кнопка на вкладке Главная.

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

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

 

5.1. Нажмите кнопку . Выберите категорию Полный алфавитный перечень. Посмотрите с помощью линейки просмотра, каким огромным арсеналом функций располагает Excel.

5.2. Подсчитайте сумму вычисленных значений Н и запишите ее в ячейку F25.Сделайте текущей ячейку F25.Щелкните по кнопке . Вычисление суммы – часто используемая операция. Она закреплена за кнопкой на вкладке Формулы. В ячейку У25 запишите поясняющий текст Сумма y=

5.3.Оформите нахождение среднего арифметического вычисленных значений Y. Сделайте текущей ячейку F26;Щелкните по кнопке ; В диалоговом окне Мастер функций выберите категорию Полный алфавитный перечень и имя функции СРЗНАЧ; Щелкните кнопкой ОК; В появившемся диалоговом окне укажите диапазон, в котором производится вычисление среднего значения (F4; F24).

Это можно сделать двумя способами

• набрав диапазон вручную в окне число 1;
• выделив соответствующий диапазон таблицы (при этом если окно Мастера функций закрывает нужный блок таблицы, то его можно отодвинуть, уцепившись мышью за заголовок окна).
• щелкните по кнопке Закончить;
• занесите в ячейку Е26 поясняющий текст, а в F26 — среднее значение.

5.4.Оформите нахождение минимального и максимального значений, занеся в ячейки Е27 и Е28 поясняющий текст, а в ячейки F27 и F28 минимальное и максимальное значение.

ЗАДАНИЕ 6.Оформите блок ячеек Е25:F28

6.1.Обрамите ячейки Е25:F28

6.2.Заполните этот блок фоном шапки таблицы.

6.3.Поясняющие надписи в ячейках Е25:Е28 оформите жирным шрифтом типа Arial с выравниванием вправо. Экран после выполнения данной части работы должен иметь следующий вид:

ЗАДАНИЕ 7.Сохраните файл под новым именем work2_2.xls.

ЗАДАНИЕ 8.Познакомьтесь с форматами чисел в Excel.

Форматы числа

 

Число в ячейке можно представить в различных форматах.

Например, число 100 будет выглядеть как:

100,00 р - в денежном формате;

10000% - в процентном выражении;

1,00Е+2 - в научной форме, что означает 1,00*10^2.

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

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

При изменении формата числа ячейки изменяется только спо­соб представления данных в ячейке, но не сами данные.

Если ячейка отображается в виде ##### символов, то это озна­чает, что столбец недостаточно широк для отображения числа целиком в установленном формате.

8.1.Установите масштаб 75%

8.2.Скопируйте значения из столбца А в столбцы K,L,M,N.

Столбец F заполнен формулами, а скопировать требуется значения.

· Выделите столбец F;

· Подведите курсор к границе выделенного блока (он должен принять Форму белой стрелки);

· Нажмите правую кнопку мыши;

• Буксируйте блок с помощью мыши до столбца К;
• Отпустите мышь;
• В открывшемся контекстно-зависимом меню выберите команду «Копировать только значения»;
• При этом столбец К заполнится теми же значениями, что и столбец F, но он не будет содержать формул.
• Заполните значениями столбца К столбцы L,M,N.

8.3. В столбце К задайте формат, в котором отражаются две значащие цифры после запятой 0,00.

8.4. В столбце L задайте научный (экспоненциальный) формат;

8.5. В столбце М задайте формат процент;

8.6. В столбце N установите собственный формат, четыре знака после запятой.

8.7. Оформите блок K2:N24 в стиле основной и вспомогательной таблиц (заголовок, обрамление, шрифт, заполнение).

 

ЗАДАНИЕ 9.Предъявите результат работы преподавателю. Экран должен иметь следующий вид:

 

 

ЗАДАНИЕ 10. Сохраните файл под старым именем work2_2.xls

 

ЗАДАНИЕ 11. Распечатайте таблицу на принтере, предварительно

просмотрев ее вид на экране.

 

Предварительный просмотр и печать таблицы на принтере.

 

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

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

• изменить параметры страницы;
• изменить установленные поля и разбивку на страницы;
• начать печать.

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

11.1.Задайте режим предварительного просмотра.

11.2. Щелкните по вкладке Разметка страницы

11.3.В группе Параметры страницы нажмите кнопку.

• Проверьте, что установлен размер листа 210 х 297 (формат А4).

11.4. Откроите вкладку Колонтитулы.

• Щелкните по кнопке <Создать Верхний колонтитул>. Откроется новое диалоговое окно.

• В окне Центр наберите свою Фамилию и Имя.
• Щелкните по кнопке <ОК>.
• Щелкните по кнопке <Создать Нижний колонтитул>
• В окне Центр установите текущие дату и время, используя соответственно кнопки
• Щелкните по кнопке <Ок>.
• Раскроите вкладку Лист и проверьте, что флажок (*). Печатать Сетку - <Не выбран>.
• Щелкните по кнопке <Ок>.

Диалоговое окно Параметры страницы закроется.

11.5.Щелкните по кнопке <Поля>, на экране будут видны
линии, обозначающие поля.

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

• Измените ширину полей таблицы таким образом, чтобы все
  данные в них были видны и таблица помещалась на странице
  полностью.

11.5.Убедитесь, что принтер подключен к вашему компьютеру и работоспособен.

11.6.Нажмите на кнопку <Печать>.

ЗАДАНИЕ 12. Завершите работу с EXCEL.

 

ЗАДАНИЕ 13. Подведите итоги.

Проверьте,

знаете ли вы, что такое: умеете ли вы:

функции Excel форматы чисел; режим предварительного просмотра; колонтитулы

защищать информацию в таблице; использовать функции; изменять форматы представления чисел; распечатывать таблицу

Если нет, то еще раз внимательно перечитайте соответствую­щие разделы работы.

Предъявите преподавателю:

• краткий конспект;
• файл work2_2.xls на экране и на рабочем диске в личном
  каталоге;
• распечатку таблицы work2_2.xls .