Работа со ссылками на ячейки. Использование формул для массивов. Поиск и замена данных в электронных таблицах. Функция автопересчета

Лабораторная  работа № 9.

Тема. Работа со ссылками на ячейки. Использование формул для массивов. Поиск и замена данных в электронных таблицах. Функция автопересчета.

1. Цель работы - научиться следующему:

·  ознакомиться с другими возможностями использования ссылок на ячейки;

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

·  находить и изменять текст и числовые значения;

·  изменять порядок автоматического пересчета.

2. Порядок выполнения.

АБСОЛЮТНЫЕ И ОТНОСИТЕЛЬНЫЕ ССЫЛКИ НА ЯЧЕЙКИ.

EXCEL распознает 2 вида ссылок на ячейки:

Чтобы Вам было легче понять, чем различаются абсолютные и относительные ссылки на ячейки, попробуйте выполнить следующие действия:

1.  С помощью маркера заполнения воспроизведите содержимое ячейки В20 в ячейке С20.  

2.  Щелкните по ячейке В21.

3.  Напечатайте =В20*90% и нажмите ENTER.

4.  Щелкните по ячейке В22.

5.  Напечатайте =В20-В21 и нажмите ENTER.

6.  Щелкните по ячейке С21.

7.  Напечатайте =$С$20*90% и нажмите ENTER.

8.  Щелкните по ячейке С22.

9.  Напечатайте =$C$20-$C$21и нажмите ENTER.

Когда Вы закончите, Ваш экран должен быть похож на рис 9.1, на котором показаны равные суммы в обеих ячейках - В21 и С21. (Если при выполнении операции внутри ячейки не сохраняется обозначение рублей, то Вам следует установить для ячейки Денежный формат).

Рис.9.1

Теперь воспользуйтесь маркером заполнения, чтобы воспроизвести содержимое ячеек В22 и С22 в ячейках В23 и С23. На этот раз получаются ячейки, содержащие разные значения, как показано на рис 9.2. Почему так вышло?

Чтобы ответить на этот вопрос, сделайте активной ячейку В23. Мимолетного взгляда на строкуформул достаточно, чтобы понять, что в ней теперь содержится следующая формула:

В21-В22.

Когда Вы копировали ячейку В22 (которая содержала относительные ссылки на ячейки) на один ряд ниже, ссылка на каждый ряд в ее первоначальной формуле увеличились на 1. Поэтому ее относительный указатель сообщал, что следует обратиться к двум ячейкам, находящимся над ней, в соответствии с ее нынешним положением. Согласно этим новым ссылкам, EXCEL вычел 6870 из 61830, и разность составила 54960.

Теперь щелкните по С23, чтобы сделать ее активной. Достаточно взглянуть на строку формул, чтобы увидеть, что в ней содержится следующая формула:

$C$20-$C$21.

Поскольку в ячейке С22 содержались абсолютные ссылки на ячейки, адреса этих ячеек не были скорректированы в соответствии с новым расположением этой формулы, когда она была скопирована в ячейку С23. В соответствии с первоначальными ссылками, Excel вновь вычел 61830 из 68700, остаток составил 6870.

Рис.9.2

СМЕШИВАНИЕ И СОВМЕЩЕНИЕ ССЫЛОК НА ЯЧЕЙКИ.

В формуле можно смешивать и совмещать абсолютные и относительные ссылки на ячейки. Вы можете, например, указать абсолютную ссылку на букву (номер колонки) вместе с относительной ссылкой на номер ряда. С$19- это пример смешанной ссылки, в которой номер колонки будет меняться каждый раз, когда эта формула будет копироваться; ряд будет всегда оставаться тем же самым.

И, напротив, Вы можете сделать такую ссылку на ячейку $C19. В этом случае номер колонки всегда остается неизменным, а ссылка на ряд меняется по сравнению с первоначальной.

Меню EXCEL ВСТАВКА позволяет легко менять тип ссылки на ячейку. Чтобы понять, как это делается, проделайте следующее:

1.  Щелкните по ячейке С22.

2.  В строке формул выделите мышью $C$20.

3.  Нажмите функциональную клавишу F4.

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

Опция ССЫЛКА  появляется, если Вы выделили ссылку на ячейку в формуле - еще один пример того, как EXCEL определяет, что он позволит Вам сделать дальше, исходя из того, что Вы делаете сейчас.

Чтобы посмотреть, как изменяется тип ссылки, проделайте следующее:

1.  Несколько раз нажмитеF4, Excelменяет ссылки на ячейки, содержащиеся в                      выделенной части формулы, с абсолютных на относительные и наоборот.

Не беспокойтесь о том, какого типа ссылки будет содержать эта ячейка после того, как Вы закончите выполнять это упражнение. Очень скоро мы удалим ее.

КОПИРОВАНИЕ ИМЕН.

Прежде, чем двигаться дальше, давайте проверим нынешнее содержимое