• Главная
• Уроки:
  • Урок 1
  • Урок 2
  • Урок 3
  • Урок 4
  • Урок 5
  • Урок 6
  • Урок 7
  • Урок 8
  • Урок 9

Урок № 2 (1)
РЕШЕНИЕ ЗАДАЧИ ТАБУЛИРОВАНИЯ ФУНКЦИ

Цели работы:

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

Дальнейшее знакомство с Excel осуществим па примере задачи табулирования функции.
Постановка задачи:
Вычислить значения функции y=k*(xA2-l)/(xA2+l) для всех х па интервале [-2,2] с шагом 0,2 при к=10.
Решение должно быть получено в виде таблицы:

ЗАДАНИЕ 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:

Где ХО - начальное значение 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. Подведите итоги.

Проверьте:

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

• относительная адресация;
• абсолютная адресация;
• смешанная адресация.

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

Урок №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 и Е27 поясняющий1 текст, а в ячейки 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. Скопируйте значения из столбца А  в столбцы  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. Подведите итоги.
Проверьте,
знаете ли вы, что такое:

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