Формулы. Абсолютная и относительная адресации ячеек и блоков.

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

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

Относительная адресация. В формулах Excel запоминает расположение ячеек относительно текущей ячейки. Так, например, когда вы вводите формулу =B1+D1 в ячейку F1,

то Excel интерпретирует формулу, как «прибавить содержимое ячейки, расположенной четырьмя столбцами впереди, к содержимому ячейки, расположенной двумя столбцами впереди».

Если вы скопировали формулу =B1+D1 из ячейки F1 в F4. Excel также интерпретирует формулу как «прибавить содержимое ячейки, расположенной четырьмя столбцами впереди, к содержимому ячейки, расположенной двумя столбцами впереди». Таким образом, формула в ячейке F4 примет вид =B4+D4.

Абсолютная адресация. Если при копировании вы пожелаете сохранить ссылку на конкретную ячейку или область, то вам необходимо воспользоваться абсолютной адресацией. Для её задания необходимо перед именем столбца и перед номером строки ввести символ $. Например $B$4; $C$2:$F$8 и т. д.

Если вы скопировали формулу =$B$1+D1 из ячейки F1 в F4. Excel также интерпретирует формулу, как «прибавить содержимое ячейки B1, к содержимому ячейки, расположенной двумя столбцами впереди». Таким образом, формула в ячейке F4 примет вид =$B$1+D4.

1

2

3

4

A B C D E F

Практическая работа № 1.

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

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

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

№

x

k

y1=x^2-1

y2=x^2+1

y=k*(y1/y2)

Задание №1. Заполните шапку основной таблицы начиная с ячейки А1.

• 1.1. В ячейку В2 занесите -2; в В3 -1,8. Затем выделите ячейки B2:B3 и выполните заполнение до ячейки В22.

При этом столбец заполнится значениями х от -2 до 2 с шагом 0,2.

1.2. Аналогично заполните столбец А до ячейки А22.

1.3. В ячейку С2 занесите 10 и выполните заполнение до ячейки С22.

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

1.4. заполните столбец D значениями функции y1=x^2-1

• в ячейку D2 занесите =B2^2-1 или =B2*B2-1

• скопируйте формулы из ячейки D2 в ячейки D3:D22

Начальное и конечное значения равны 3.

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

Начальное и конечное значения равны 5.

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

• в ячейку F2 занесите =C2*(D2/E2)

• скопируйте формулы из ячейки F2 в ячейки F3:F22

Начальное и конечное значения равны 6.

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

1

2H I J

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

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

это значит, что в ячейку В2 заносится значение из ячейки Н2

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

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

• скопируйте формулу из ячейки В3 в ячейки В4:В22.

• При этом столбец заполнится значениями х от -2 до 2 с шагом 0,2.

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

• В ячейку С2 занесите =$J$2;

• В ячейку С3 занесите =С2;

• скопируйте формулу из ячейки С3 в ячейки C4:C22.

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

2.3. заполните столбец D значениями функции y1=x^2-1

• в ячейку D2 занесите =B2^2-1 или =B2*B2-1

• скопируйте формулы из ячейки D2 в ячейки D3:D22

Начальное и конечное значения равны 3.

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

Начальное и конечное значения равны 5.

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

• в ячейку F2 занесите =C2*(D2/E2)

• скопируйте формулы из ячейки F2 в ячейки F3:F22

Начальное и конечное значения равны 6.

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

3.1. Измените во вспомогательной таблице начальное значение х. В ячейку H2 занесите -5.

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

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

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

3.4. Прежде, чем продолжить работу, верните прежние начальные значения во вспомогательной таблице: x0=-2, step=0,2, k=10.

Занятие №3.

Практическая работа № 1.

Цель работы: научиться пользоваться функциями в Excel.

Задание №1. Откройте файл work2_1

Задание №2. Использование встроенных функций Excel.

Для вставки функции в Excel можно воспользоваться кнопкой «Вставка функции» на панели Форматирование.

В ячейку Е25 запишите «Сумма у=». В ячейку Е26 запишите «Ср.арифм.у=». В ячейку Е27 запишите «Min у=». В ячейку Е28 запишите «Max у=». установите курсор на F25 - щелкните по кнопке «Вставка функции» на панели Форматирование, полный алфавитный перечень, СУММ, число1 - F4:F24; F26 - СРЗНАЧ, число1 - F4:F24; F27 - МИН, число1 - F4:F24; F28 - МАКС, число1 - F4:F24.

A B C D E F

№

x

k

y1=x^2-1

y2=x^2+1

y=k*(y1/y2)

1

-2

10

3

5

6

2

-1,8

10

2,24

4,24

5,28301887

3

-1,6

10

1,56

3,56

4,38202247

4

-1,4

10

0,96

2,96

3,24324324

5

-1,2

10

0,44

2,44

1,80327869

6

-1

10

0

2

0

7

-0,8

10

-0,36

1,64

-2,195122

8

-0,6

10

-0,64

1,36

-4,7058824

9

-0,4

10

-0,84

1,16

-7,2413793

10

-0,2

10

-0,96

1,04

-9,2307692

11

0

10

-1

1

-10

12

0,2

10

-0,96

1,04

-9,2307692

13

0,4

10

-0,84

1,16

-7,2413793

14

0,6

10

-0,64

1,36

-4,7058824

15

0,8

10

-0,36

1,64

-2,195122

16

1

10

0

2

0

17

1,2

10

0,44

2,44

1,80327869

18

1,4

10

0,96

2,96

3,24324324

19

1,6

10

1,56

3,56

4,38202247

20

1,8

10

2,24

4,24

5,28301887

21

2

10

3

5

6

Сумма у=

-15,323179

Ср. арифм.у=

-0,7296752

мин y=

-10

макс y=

6

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

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

1. Выделите значения у, затем на выделенном блоке нажмите правой кнопке мыши и выберите пункт копировать.

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

Столбце K задайте: Формат, Ячейки, Число, Числовой, Число десятичных знаков - 4;

Столбце L задайте: Формат, Ячейки, Число, Экспоненциальный, Число десятичных знаков - 2;

Столбце M задайте: Формат, Ячейки, Число, Процентный, Число десятичных знаков - 2;

Столбце N задайте: Формат, Ячейки, Число, Денежный, Число десятичных знаков - 2, Обозначения - $ английский (США).

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

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

• Установите курсор на строку номер 1:

Вставка Строки (2 раза)

4.2. Введите заголовки таблиц.

• В ячейку А1 «Таблицы»

• В ячейку А2 «Основная»

• В ячейку Н2 «Вспомогательная»

4.3. Объедините ячейки A1:J1 и разместите заголовок по центру:

• Выделите блок A1:J1;

• Используйте кнопку Объединить и поместить в центре панели инструментов Форматирование.

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

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

Шрифтовое оформление текста.

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

• Для заголовка «Таблицы» задайте шрифт Courier New, размер шрифта 14, полужирный.

• Для заголовков «основная» и «вспомогательная» задайте шрифт Courier New, размер шрифта 12, полужирный.

• Для шапок таблиц установите шрифт Courier New, размер шрифта 12, полужирный, курсив.

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

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

4.8. Задайте рамки для основной и вспомогательной таблиц, используя Меню.

• Выделите шапки таблиц, затем: Формат/ Ячейки/ Граница/ Внешние - ОК.

• Выделите всю таблицу, затем: Формат/ Ячейки/ Граница/ Внешние и вертикальную черточку, проходящую по центру/ - ОК.

4.8а. Задайте рамки для основной и вспомогательной таблиц, используя кнопку «Границы» на панели Форматирование.

4.9. задайте фон заполнения, внутри таблиц - желтый, фон заполнения шапок таблиц - розовый.

Задание 5. Сохраните результаты работ на диске в личном каталоге в файле work2_1