Абсолютные и относительные ссылки
По умолчанию, ссылки наячейки в формулах рассматриваются как относительные. Это означает, что при копировании формулы, адреса в ссылках автоматически изменяются в соответствии с относительным расположением исходной ячейки и создаваемой копии. Например, пусть в ячейках А1 и А2 находятся данные (рис. 2), сумма этих чисел равна 11 и записана в ячейке В3. Теперь перекопируем эту формулу (выделена жирным шрифтом) в ячейку В4. Воспользуемся правилом относительной адресации, первый компонент суммы находился на две ячейки выше и одну правее результата, следовательно, после копирования роль первого компонента в формуле будет играть ячейка А2, а второго – А3. Перекопируем формулу-источник в ячейку С5, компоненты примят значения ячеек В3 (на две ячейки выше и одну правее результата) и В4 (на одну ячейку выше и одну правее результата).
| A | B | C | D | A | B | C | D | |||
| =A1*$B$8 | ||||||||||
| =B2*$B$8 | ||||||||||
| =A1+A2 | =$A3*$B$8 | |||||||||
| =A2+A3 | =$A4*$B$8 | |||||||||
| =B3+B4 | =A5*$B$8 | |||||||||
| =$A6*$B$8 | ||||||||||
| множитель |
Рис. 2
При абсолютной адресации адреса ссылок при копировании не изменяются, так что ячейка, на которую указывает ссылка, рассматривается как нетабличная. Для изменения способа адресации при редактировании формулы надо выделить ссылку на ячейку и нажать клавишу F4. Элементы номера ячейки, использующие абсолютную адресацию, предваряются символом $. Например, при последовательных нажатиях клавиши F4 номер ячейки А1 будет записываться как А1, $А$1, А$1 и $А1. В двух последних случаях один из компонентов номера ячейки рассматривается как абсолютный, а другой — как относительный.
Пусть дан столбец чисел и масштабный множитель (рис. 2), требуется получить значения ряда, увеличенных на масштабный множитель, записывая результаты в шахматном порядке. Записанную в ячейке В1 формулу скопируем в ячейки С2 и В5. Так как на ячейку А1 была использована относительная ссылка, то при копировании в ячейку С2 формула не даст нужного ответа, а в ячейке В5 будет верный результат. Так как при копировании формулы в ячейку В5, верного результата мы не получили, то воспользуемся частичной абсолютной ссылкой – «зафиксируем» столбец знаком $ так, что при копировании он не будет изменятся (формула в ячейке А3). Скопируем эту формулу в ячейки С4 и С6, результат в ячейках будет соответствовать заданию.
Правила копирования формул.
Для эффективной работы с электронными таблицами можно придерживаться следующих правил:
1. Для ускорения заполнения электронной таблицы в ней надо определить ячейки, в которых будут находиться однотипные формулы.
2. Для ввода однотипных формул достаточно ввести только одну формулу-оригинал, в которой с помощью относительной или абсолютной адресации указать полностью или частично изменяемые ссылки. Такая исходная формула представляет собой образец (шаблон), указывающий местоположение в таблице входных данных (ссылок) — операндов этой формулы относительно любого текущего местоположения ячейки, в которой хранится формула (первоначально введенная или затем скопированная).
3. После ввода исходной формулы-оригинала для заполнения других ячеек однотипными формулами необходимо скопировать ее в эти ячейки. При копировании формул действует правило относительной ориентации ячеек, благодаря которому обеспечивается автоматическая настройка адресов ссылок во всех формулах-копиях. Для запрета автоматического изменения (настройки) адресов ссылок в формулах-копиях используют абсолютные ссылки в формулах-оригиналах.
Автоматизация ввода.
Так как таблицы часто содержат повторяющиеся или однотипные данные, программа Excel содержит средства автоматизации ввода. К числу предоставляемых средств относятся: автозавершение, автозаполнение числами и автозаполнение формулами.
Автозавершение. Для автоматизации ввода текстовых данных используетсяметод автозавершения. Его применяют при вводе в ячейки одного столбца рабочего листа текстовых строк, среди которых есть повторяющиеся. В ходе ввода текстовых данных в очередную ячейку программа Excel проверяет соответствие введенных символов строкам, имеющемся в этом столбце выше. Если обнаружено однозначное совпадение, введенный текст автоматически дополняется. Нажатие клавиши ENTER подтверждает операцию автозавершения, в противном случае ввод можно продолжать, не обращая внимания на предлагаемый вариант.
Можно прервать работу средства автозавершения, оставив в столбце пустую ячейку. И наоборот, чтобы использовать возможности средства автозавершения, заполненные ячейки должны идти подряд, без промежутков между ними.
Автозаполнение числами. При работе с числами используется метод автозаполнения. В правом нижнем углу рамки текущей ячейки имеется черный квадратик – маркер заполнения. При наведении на него указатель мыши (он обычно имеет вид толстого белого креста) приобретает форму тонкого черного крестика. Перетаскивание маркера заполнения рассматривается как операция «размножения» содержимого ячейки в горизонтальном или вертикальном направлении.
Если ячейка содержит число (в том числе дату, денежную сумму), то при перетаскивании маркера происходит копирование ячеек или их заполнение арифметической прогрессией. Для выбора способа автозаполнения следует производить специальное перетаскивание с использованием правой кнопки мыши.
Пусть, например, ячейка А1 содержит число 1. Наведите указатель мыши на маркер заполнения, нажмите правую кнопку мыши, и перетащите маркер заполнения так, чтобы рамка охватила ячейки А1, В1 и С1, и отпустите кнопку мыши. Если теперь выбрать в открывшемся меню пункт Копировать ячейки, все ячейки будут содержать число 1. Если же выбрать пункт Заполнить, то в ячейках окажутся числа 1,2 и так далее.
Чтобы точно сформулировать условия заполнения ячеек, следует дать команду Правка > Заполнить > Прогрессия. В открывшемся диалоговом окне Прогрессия выбирается тип прогрессии, величина шага и предельное значение. После щелчка кнопке ОК программа Excel автоматически заполняет ячейки в соответствии с заданными правилами.
Автозаполнение формулами. Эта операция выполняется так же, как автозаполнение числами. Ее особенность заключается в необходимости копирования ссылок на другие ячейки. В ходе автозаполнения во внимание принимается характер ссылок в формуле: относительные ссылки изменяются в соответствии с относительным расположением копии и оригинала, абсолютные остаются без изменений.
Для примера предположим, что значения в третьем столбце рабочего листа (столбце С) вычисляются как суммы значений в соответствующих ячейках столбцов А и В. Введем в ячейку С1 формулу =А1+В1. Теперь скопируем эту формулу методом автозаполненяя во все ячейки третьего столбца таблицы. Благодаря относительной адресации формула будет правильной для всех ячеек данного столбца.
Технология работы.
Задание 1.
Цель: Научиться вводить текстовые и числовые данные в электронные таблицы Excel. Изучить, как производиться ввод и вычисления формул. Выяснить, как осуществляется копирование формул методом автозаполнения, и определить, в каких случаях следует использовать относительные и абсолютные ссылки.
1. Запустите программу Excel (Пуск>Программы>Microsoft Excel).
2. Создайте новую рабочую книгу (кнопка Создать на стандартной панели инструментов).
3. Дважды щёлкните на ярлыке текущего рабочего листа и дайте этому рабочему листу имя Данные.
4. Дайте команду Файл > Сохранить как… и сохраните рабочую книгу под именем book.xls.
5. Сделайте текущей ячейку А1 и введите заголовок Результаты измерений.
6. Введите произвольные числа в последовательные ячейки столбца А, начиная с ячейки А2.
7. Введите в ячейку В1 строку Удвоенное значение.
8. Введите в ячейку С1 строку Квадрат значения.
9. Введите в ячейку D1 строку Квадрат следующего числа.
10. Введите в ячейку В2 формулу = 2*А2.
11. Введите в ячейку С2 формулу = А2*А2.
12. Введите в ячейку D2 формулу = В2 + С2 + 1.
13. Выделите протягиванием ячейки В2, С2 и D2.
14. Наведите указатель мыши на маркер заполнения в правом нижнем углу рамки, охватывающей выделенный диапазон. Нажмите левую кнопку мыши и перетащите маркер, чтобы рамка охватила столько строк в столбцах В, С и D, сколько имеется чисел в столбце А.
15. Убедитесь, что формулы автоматически модифицируются так, чтобы работать со значениями ячейки в столбце А текущей строки.
16. Измените одно и значений в столбце А и убедитесь, что соответствующее значение в столбцах В, С и D в этой же строке были автоматически пересчитаны.
17. Введите в ячейку E1 строку Масштабный множитель.
18. Введите в ячейку Е2 число 5.
19. Введите в ячейку F1 строку Масштабирование.
20. Введите в ячейку F2 формулу = А2*Е2.
21. Используйте метод автозаполнения, чтобы скопировать эту формулу в ячейки столбца F, соответствующие заполненным ячейкам столбца А.
22. Убедитесь, что результат масштабирования оказался неверным. Это связано с тем, что адрес Е2 в формуле задан относительной ссылкой.
23. Щёлкните на ячейки F2, затем в строке формул. Установите текстовый курсор на ссылку Е2 и нажмите клавишу F4. Убедитесь, что формула теперь выглядит как = А2*$E$2, и нажмите клавишу ENTER.
24. Повторите заполнение столбца F формулой из ячейки F2.
25. Убедитесь, что благодаря использованию абсолютной адресации значения ячеек столбца F теперь вычисляются правильно. Сохраните рабочую книгу book.xls.
Задание 2.
Вычислить значения функции 
для всех x на интервале [-2; 2 ] с шагом 0,2 при k = 10. Значения функции должны автоматически пересчитываться при изменениях во вспомогательной таблице. Решение должно быть получено в виде таблицы на рис. 3.Работу выполните в созданной вами книге на новом листе " функция".
Ход работы.
1. Откройте созданную вами книгу book.xls. Переименуйте лист, дав ему имя «функция».
| A | B | C | D | E | F | G | H | I | J | |
| n | x | k | Y1=x^2-1 | Y2=x^2+1 | Y=k* (Y1/Y2) | x0 | step | K | ||
| -2 | -2 | 0,2 | ||||||||
Рис. 3.
2. Заполните основную (ячейки А1, …,F1) и вспомогательную (ячейки H1, H2, I1, I2, J1, J2) таблицы. Установите ширину столбцов такой, чтобы надписи были видны полностью.
3. Используя функцию автозаполнения, заполните столбец А числами от 1 до 21, начиная с ячейки А2 и заканчивая ячейкой А22.
4. Заполните столбец В значениями х от - 2 до 2 с шагом 0,2, для этого занесите в ячейку В1 формулу =Н2. Затем самостоятельно напишите формулу нахождения следующего значения х незабывая, что оно должно зависеть от величины шага, значение которого находится в ячейке I2.
5. Заполните столбец С значениями коэффициента k, то есть скопируйте значение k из ячейки J2.
6. Заполните столбец D значениями функции у1=х^2-1.
7. Аналогичным образом заполните столбец E значениями функции у2=х^2+1.
Проверьте! Все значения положительные; начальное и конечное значения равны 5.
8. Заполните столбец F значениями функции: y = k*(x^2-1)/(x^2+1).
Проверьте! Значения функции как положительные, так и отрицательные; начальное и конечное значения равны 6.
9. Пронаблюдайте за изменениями в основной таблице при изменении данных во вспомогательной таблице.
10. Измените во вспомогательной таблице начальное значение х = -5.
11. Измените значение шага: step = 2.
12. Измените значение коэффициента: k = 1.
Внимание! При всех изменениях данных во вспомогательной таблице в основной таблице пересчет производится автоматически.
13. Верните прежние начальные значения во вспомогательной таблице: х0 = -2, step = 0.2, k =10.
14. Сохраните изменения и закройте книгу.
Мы познакомились с основными способами работы с электронными таблицами, научились применять формулы для проведения подсчетов.
Наиболее часто используемые приёмы работы с объектами Exsel вынесены в отдельную таблицу Объекты табличного процессора и действия над ними и таблицу Перемещение по рабочему листу.