По завершении набора нажать клавишу ENTER

Щелчком мыши выделить следующую ячейку, которая нужна в формуле, т.д.

Поставить знак арифметического действия с использованием клавиатуры.

Щелчком мыши выделить ячейку, которая нужна в формуле. Если последовательно продолжать щелчки мышью по ячейкам, то в формуле будет отображена в конечном итоге ссылка на ту ячейку, по которой был выполнен последний щелчок.

Выделить ячейку для ввода формулы.

2. Ввести знак равенства (=).

При вводе в формулу ссылок на ячейки, находящиеся в далеко расположенной области рабочего листа, можно воспользоваться клавишей F5 и указать адрес нужной ячейки.

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

Использование относительных ссылок в формулах

По умолчанию для указания адресов ячеек в MS Excel применяются относительные ссылки вида D5, C7, M9 и др. Это означает, что ссылки на ячейки изменяются при копировании формулы на новое место. После копирования формул относительные ссылки будут указывать на другие ячейки так, чтобы сохранить прежние отношения в соответствии с новым местоположением.

Например. Ячейка C4 содержит формулу =E5*F7. Какой вид примет формула после копирования ее в ячейки D6, B5, A1?

Возле квадратных скобок, в которые заключены ссылки, слева запишем соотношения копируемых в формуле столбцов, а справа – соотношение копируемых в формуле строк. Получим:

, т.е. в ячейке D6 формула будет иметь вид =F7*G9;

, т.е. в ячейке B5 формула будет иметь вид =D6*E8;

, т.е. в ячейке A1 формула будет иметь вид =C2*D4.

Использование абсолютных ссылок в формулах

Иногда изменения ссылок на ячейки при копировании формул является нежелательным. Чтобы избежать изменения ссылок при копировании формулы необходимо применять абсолютные ссылки, которые обозначаются с использованием знака доллара перед заголовком столбца и перед заголовком строки: $ D$5, $C$7, $M$9. Абсолютная ссылка – это точный адрес ячейки в формуле, ссылающийся на данную ячейку независимо от положения ячейки с формулой.

Для того чтобы ссылку представить как абсолютную, следует:

1. В режиме ввода формулы указать ячейку. Появится относительная ссылка на эту ячейку. Курсор не переустанавливать на ссылку.

2. Нажимать клавишу F4 до тех пор, пока не появится правильная комбинация знаков доллара и заголовков столбцов и строк.

Можно воспользоваться клавишей F4 и для редактирования ссылок в существующей формуле.

При копировании формулы абсолютные ссылки не изменяются.

Например. Элементы столбца В умножить на значение содержащееся в ячейке D1 (рис.4.1). Формула в ячейке E1 выглядит как =B1*$D$1. Как данная формула будет выглядеть при копировании ее в ячейку Е4, G7?

Рис.3.1. Пример использования абсолютных ссылок.

Возле квадратных скобок, в которые заключены ссылки, слева запишем соотношения копируемых в формуле столбцов, а справа – соотношение копируемых в формуле строк. Не следует забывать, что абсолютные ссылки при копировании формулы не изменяются. Получим:

, т.е. в ячейке Е4 формула будет иметь вид =B4*$D$1;

, т.е. в ячейке G7 формула будет иметь вид =D7*$D$1.

Использование смешанных ссылок в формулах

В случаях, когда нужно, чтобы при копировании изменялась только строка или только столбец, используют смешанную ссылку, которая содержит как абсолютные, так и относительные ссылки. Например, ссылка D$5, а ссылка $D5 указывает на то, что при копировании формулы не изменяется столбец. Создаются смешанные ссылки тем же способом, что и абсолютные, т.е. клавиша F4 нажимается необходимое число раз.

Например. Текущий курс доллара равен 5,12 (ячейка С7). Получить числовое значение, заданное в диапазоне B2:D2, в долларах. Результат поместить в диапазон B3:D3.

Формула в ячейке B3 имеет вид: =B$2/$C$7 (рис. 4.2). Как данная формула будет выглядеть при копировании ее в ячейку C3?

Рис.3.2. Пример использования смешанных ссылок.

, т.е в ячейке С3 формула будет иметь вид =С$2/$C$7/

Ссылка на другие листы рабочей книги

В формуле можно сослаться на другие листы рабочей книги путем включения в формулу ссылки на лист. Например, чтобы сослаться на ячейку D7 листа Баланс, нужно ввести в формулу ссылку вида Баланс!D7. Если имя листа содержит пробелы, нужно заключить ссылку на лист в одиночные кавычки.

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

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

– щелкнуть по ярлычку листа, содержащего ячейку или диапазон, на который нужно сослаться;

– выделить нужную ячейку или диапазон. В строке формул появится полная ссылка, включая ссылку на лист. Если имя листа содержит пробелы, MS Excel заключит ссылку на лист в одиночные кавычки

– закончить ввод формулы и нажать Enter.

Организация связей с ячейками и диапазонами ячеек рабочей книги

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

Чтобы связать ячейку или диапазон ячеек можно воспользоваться командой Правка\Специальная вставка. Для этого:

1. открыть рабочий лист, с ячейками которого устанавливается связь;

2. выделить ячейку или диапазон ячеек;

3. выполнить команду Правка\Копировать;

4. перейти на рабочий лист, в котором устанавливается связь с данными;

5. выделить ячейку или диапазон ячеек, в которые требуется установить связь;

6. выполнить команду Правка\Специальная вставка (или команда Специальная вставка из контекстного меню);

7. в появившемся диалоговом окне установить переключатель Вставить в положение Всё; переключатель Операция установить в положение Нет; нажать кнопку Вставить связь. В выделенной ячейке или диапазоне ячеек появятся ссылки, имеющие абсолютный адрес.

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

1. активизировать рабочий лист, в котором устанавливается связь с данными;

2. выделить ячейку, в которую требуется установить связь;

3. поставить в ней знак равенства;

4. перейти на лист, с ячейками которого устанавливается связь (на лист-источник);

5. выделить связываемую ячейку. Она активизируется бегущей рамкой.

6. нажать клавишу Enter. В выделенной ячейке листа-приемника появится ссылка, имеющая относительный адрес.

Для выполнения в лабораторной работе 4 в пункте 5 первого задания следует организовать связи столбцов 2, 4, 6, 8 в строке Всего имущества с листом Баланс вручную, а затем выполнить копирование полученных связей на остальные строки таблицы тех же столбцов (2, 4, 6, 8).

Замена формул их значениями

Иногда в процессе работы возникает необходимость заморозить результаты формулы, т.е. заменить формулу ее значением. Для этого:

– выполнить двойной щелчок по ячейке, содержащей формулу;

– нажать F9 (формула в строке формул будет заменена вычисленным значением);

– нажать OK.

Чтобы вернуть формулу к первоначальному виду щелкнуть по кнопке Отменить на панели инструментов.

Использование пункта меню Правка\Специальная вставка для арифметических действий

С помощью специальной вставки можно выполнять вставку в ячейки формул, значений, форматов, примечаний, условий на значения и др., а также выполнять арифметические операции ( сложить, вычесть, умножить, разделить ). Например, для того, чтобы с помощью пункта меню Правка\Специальная вставка рассчитать абсолютное расхождение баланса (лаб. раб. 3, пункт 7), следует:

– выделить числовые значения строки Итого активов и выполнить команду Правка\Копировать;

– выделить пустые ячейки строки Расхождение и выполнить команду Правка\Вставить;

– выделить числовые значения строки Итого пассивов и выполнить команду Правка\Копировать;

– выделить ячейки строки Расхождение и выполнить команду Правка\Специальная вставка\вычесть.

Аналогичным образом можно выполнять другие арифметические действия над числовыми значениями ячеек.

Дата добавления: 2014-01-06