Решение задачи табулирования функци

Цели работы:

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

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

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

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

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

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

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

 

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

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

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

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

При обращении к ячейке можно использовать способы, опи­санные ранее, например В3_; А!: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. Аналогичным образом заполните столбец В значениями функции у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

 

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

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

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

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

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

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

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

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

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

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

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

 

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

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

 

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

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

- тип шрифта.

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

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

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

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

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

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

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

• для шапок таблиц установите шрифт ArialCyr, высота шриф­та 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.

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

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

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

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

 

 

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 Cyr с выравниванием вправо.

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

 

 

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

 

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

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

 

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

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

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

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

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

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

 

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

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

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

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

8.2. Скопируйте значения из столбца А4 в столбцы 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.4. Щелкните по кнопке <Поля>, на экране будут видны
линии, обозначающие поля.

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

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

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

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

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

 

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

Проверьте,

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

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

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

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

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

 

ПРАКТИЧЕСКАЯ РАБОТА № 3