Лабораторная работа № 4
Основы работы в табличном процессоре Excel:
работа с формулами (клптрование, перенос и редактирование формул), абсолютные и относительные ссылки, сортировка данных.
Копирование или перенос формул
Если функции похожи одна на другую, то, возможно, легче скопировать и отредактировать уже существующую формулу, чем набирать все сначала.
Скопировать или перенести формулу можно способом, аналогичным тому, как копируются либо переносятся любые другие данные в ячейках.
Для выполнения работы откройте файл Формулы.xls.
В ячейке D1 введите функцию «=В1+С1». Скопируйте формулу из этой ячейки в остальные ячейки диапазона D2:D5.
Что произошло? В ячейке D1 находилась формула =В1+С1. При копировании ее в ячейку D2 формула приобретает вид =B2+С2. При копировании в ячейку D3 формула приобретает вид =В3+C3 и т. д.
Теперь скопируйте формулу из ячейки D1 в ячейку Е1 и просмотрите в Строке формул, как изменилась сама формула. Теперь формула в ячейке Е1 выгдядит так: =С1+D1.
Особенность копирования формул в Excel состоит в том, что программа копирует формулы таким образом, чтобы они сохранили свой смысл и в новой копии. Редактирование ссылок на ячейки в копируемых формулах осуществляется по следующему алгоритму:
если формула копируется на N строк вниз, то Excel добавляет ко всем используемым номерам строк число N. Если при этом формула копируется на M столбцов правее, то все используемые в ней буквенные обозначения столбцов смещаются на М позиций вправо.
Теперь перенесите формулу из ячейки D1 в ячейку F1 и просмотрите саму формулу.
В отличие от копирования, при перемещении формулы сохраняют свое содержание, т.е. ссылки на ячейки не меняются!
Редактирование формул
Чтобы отредактировать существующую или скопированную формулу, нужно выделить мышкой нужную ячейку. При этом сама функция появится в Строке формул. В этой строке вы можете должным образом отредактировать формулу – изменить функцию или диапазон ячеек, или числовое значение.
Чтобы изменить диапазон ячеек в формуле, нужно выделить в Строке формул адрес всего диапазона ячеек, после этого с помощью мышки на рабочем листе выделить новый диапазон ячеек и закончить нажатием клавиши Enter.
Чтобы изменить функцию, нужно выделить ее имя, затем нажать кнопку «Вставка функций» на панели инструментов, выбрать новую функцию в списке, и завершить ввод нажатием клавиши Enter.
Измените в ячейке C28 значение диапазона в формуле с А1:В24 на B1:B3, а в ячейке F16 измените функцию COS на SIN.
Относительные и абсолютные ссылки
Когда вы копируете или переносите формулу, очень важно знать различие между относительными и абсолютными ссылками на ячейки. Относительные ссылки устанавливаются по умолчанию. Например, в ячейке D1 записана формула =B1+C1. Эта формула означает, что нужно вычислить сумму содержимого двух ячеек, расположенных слева от данной. Когда вы скопировали эту формулу в ячейку D2, она приняла вид =В2+С2, что означает то же самое – вычислить сумму содержимого двух ячеек, расположенных слева от данной.
Обычно требуется, чтобы формула изменяла свой вид при копировании. Но бывают такие случаи, когда изменение формулы нежелательно, тогда нужно использовать знак $, чтобы заменить относительную ссылку на абсолютную.
Введите в ячейку F1 формулу =$B$1+$C$1 и скопируйте эту формулу в ячейку F2.
При копировании содержимого ячейки F1 в ячейку F2 и в новой ячейке эта формула будет вычислять сумму ячеек В1 и С1.
Но могут возникнуть ситуации, когда не всегда нужно изменять адрес или столбца, или строки на абсолютный.
Введите в ячейку Н4 ссылку вида =$B1.
Значение в этой ячейке стало равным значению ячейки В1.
Теперь скопируйте содержимое ячейки Н4 в ячейку Н7.
Что произошло? Значение в этой чейке стало равным значению ячейки В4. Если просмотреть саму формулу в строке состояния, то мы увидим, что при копировании формулы в ячейку Н7 новая формула приняла вид =$B4. Столбец В остается прежним, поскольку перед буквой В стоит знак $, а при перемещении формулы на три ячейки вниз ссылка на строку изменяется с 1 на 4.
Можно использовать абсолютные ссылки либо на столбец, либо на строку, либо на то и другое одновременно.
Чтобы изменить относительную ссылку на абсолютную, можно вручную исправить формулу в строке формул, добавив знак $ в нужных местах или использовать клавишу F4. Во втором случае нужно поместить курсор в строке формул на адресе ячейки и, поочередно нажимая клавишу F4, выбрать необходимый вид ссылки.
В ячейке К1 введите абсолютную ссылку на ячейку В1, используя метод редактирования формулы, в ячейку К2 - ссылку на В2 (строка остается постоянным)а в ячейку К3 – ссылку на В4 (строка остается постоянной). Скопируйте содежржимое ячеек К1, К2, К3 в ячейки L1, L2, L4 соответственно и просмотрите получившиеся формулы? Что произошло?