Работа с формулами
ЛЕКЦИЯ 3. РАБОТА С ФОРМУЛАМИ. СВЯЗЫВАНИЕ ЯЧЕЕК.
СПЕЦИАЛЬНАЯ ВСТАВКА.
Ввод и копирование формул
Формулы в MS Excel всегда начинаются со знака равенства (=) и могут включать числовые и буквенные величины (константы), знаки арифметических операций, операций сравнения, операций с текстом, скобки, данные ячеек и имена, а также встроенные формулы, называемые функциями. Например, чтобы получить результат перемножения содержимого ячеек D4 и N16, следует ввести: = D4*N16.
После ввода формулы в ячейку, на рабочем листе сразу появляется результат. Чтобы просмотреть саму формулу, или отредактировать ее, необходимо выделить ячейку с формулой и формула появится в строке формул. Чтобы получить возможность просмотра и редактирования формул в самой ячейке, следует выполнить двойной щелчок по этой ячейке.
Можно показать на рабочем листе формулы, а не их результаты, если выполнить команду СервисПараметры вкладка Вид флажок Формулы и нажать OK (или выполнить комбинацию клавиш Ctrl+` (где второй знак – это клавиша с изображением ~ «тильды» и ` «тупого ударения», вверху слева)).
Использование знаков операций в формулах представлено в приложении к лекции 1.
Копирование формул осуществляется:
Рекомендуемые материалы
– с применением кнопок Копировать - Вставить на стандартной панели инструментов;
– с использованием комбинации клавиш <Ctrl+C> и <Ctrl+V>;
– с помощью команд контекстного и верхнего меню;
– с использованием маркера заполнения (выделить ячейку, содержащую формулу, перетащить маркер заполнения, находящийся в нижнем правом углу ячейки, на примыкающий диапазон);
– с использованием команд ПравкаКопировать и ПравкаСпециальная вставкаформулы.
Ввод ссылок на ячейки и диапазоны
Ссылка на ячейку всегда содержит заголовок столбца и строки (например, А5). Ссылка на диапазон выглядит как B4:F10, где B4 и F10 – противоположные углы диапазона. Ссылки позволяют использовать в формулах содержимое других ячеек и одна и та же ссылка может участвовать в любом количестве формул.
Чтобы ввести ссылку на ячейку путем указания, следует:
1. Выделить ячейку для ввода формулы.
2. Ввести знак равенства (=).
3. Щелчком мыши выделить ячейку, которая нужна в формуле. Если последовательно продолжать щелчки мышью по ячейкам, то в формуле будет отображена в конечном итоге ссылка на ту ячейку, по которой был выполнен последний щелчок.
4. Поставить знак арифметического действия с использованием клавиатуры.
5. Щелчком мыши выделить следующую ячейку, которая нужна в формуле, т.д.
6. По завершении набора нажать клавишу ENTER.
При вводе в формулу ссылок на ячейки, находящиеся в далеко расположенной области рабочего листа, можно воспользоваться клавишей 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 и для редактирования ссылок в существующей формуле.
При копировании формулы абсолютные ссылки не изменяются.
Например. Элементы столбца В умножить на значение содержащееся в ячейке Е1 (рис.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), следует:
– выделить числовые значения строки Итого активов и выполнить команду ПравкаКопировать;
– выделить пустые ячейки строки Расхождение и выполнить команду ПравкаВставить;
– выделить числовые значения строки Итого пассивов и выполнить команду ПравкаКопировать;
– выделить ячейки строки Расхождение и выполнить команду ПравкаСпециальная вставкавычесть.
Аналогичным образом можно выполнять другие арифметические действия над числовыми значениями ячеек.