Копирование формул. Ввод формулы в ячейку таблицы вручную

Ввод формулы в ячейку таблицы вручную

В режиме редактирования ячейки необходимо набрать знак равенства "=" и ввести формулу.

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

Если формула содержит ошибки, или результат невозможно вычислить для указанных исходных данных, в ячейке с формулой отображаются знаки решетки "#".

Значение в ячейке, являющееся результатом вычисления формулы, отображается на сером фоне (только на экране).

Рассмотрим пример (см. рис. 13.10). Пусть в ячейке А3 значение равно 1, в ячейке А4 – 2.

Для вычисления суммы этих двух значений, например, в ячейку С3 введем следующую формулу: =а3+а4

Для вычисления среднего арифметического, например, в ячейку С4 введем следующую формулу: =(а3+а4)/2

Можно вычислить сумму и среднее ячеек A3 и A4, используя функции Sum и Average. Также можно подсчитать количество значений с помощью функции Count.

В ячейке D3 вычислим сумму, введя формулу: =Sum(а3,а4).

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

В ячейке D4 вычислим среднее, введя формулу: =Average(а3:а4)

В ячейке D5 подсчитаем количество значений, введя формулу: =Count(a3:а4).

Примечание. Пустые ячейки, и ячейки, содержащие нечисловые значения, при работе функций Sum, Average, Count игнорируются. При использовании знаков математических действий в таких случаях будут возникать ошибки (в результате получим "#").

Результаты, полученные в ходе выполнения примера, вместе с пояснениями приведены на рисунке 13.10.

Рис. 13.10. Примеры использования формул

Если необходимо рассчитать значения в нескольких ячейках по идентичным формулам, то для сокращения времени выполнения этой работы можно воспользоваться копированием формул. Для этого можно использовать стандартный буфер обмена. Команды работы с буфером обмена упоминались в п. 9.2.4.

Для копирования формулы из одной (исходной) ячейки в другую (целевую) необходимо выделить исходную ячейку и скопировать ее содержимое в буфер обмена, нажав <Ctrl>+<C>. После этого нужно выделить целевую ячейку и вставить в нее содержимое буфера, нажав <Ctrl>+<V>. Далее можно выбирать и другие целевые ячейки.

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

Пример. Пусть в ячейке B3 введена формула: "=D3^2". При копировании данной формулы в ячейку А3 получим следующий результат: "=С3^2", а при копировании в ячейку B2 получим формулу: "=D2^2". Таким образом, при копировании формулы вверх или вниз изменяются номера строк, а при копировании формулы вправо или влево изменяются обозначения столбцов.

Во многих случаях такое автоматическое изменение адресов при копировании формул полезно, но иногда для получения правильных результатов необходимо, чтобы адрес некоторой ячейки (или ячеек) не изменялся автоматически при копировании.

Если при копировании и вставке формулы адрес некоторой ячейки изменять не требуется, для такой ячейки в исходной формуле необходимо указать абсолютный адрес. Для этого нужно добавить значок доллара "$" перед обозначением столбца и/или перед номером строки. Например, при копировании формулы "=$D3^2" не будет изменяться столбец; при копировании формулы "=D$3^2" не будет изменяться номер строки; при копировании формулы "=$D$3^2"не будут изменяться ни столбец, ни строка.

Рассмотрим пример. Имеется информация об объемах продаж пяти магазинов, входящих в состав единой торговой сети. Необходимо определить долю каждого магазина в общем объеме продаж всей сети.

Решение.

Создадим таблицу, состоящую из трех столбцов и восьми строк.

В ячейке А1 введем заголовок таблицы: "Сведения об объемах и долях продаж"; в ячейках А2, В2, С2 введем заголовки столбцов: "Магазин", "Объем продаж", "Доля продаж".

В ячейках А3:А7 введем наименования магазинов; в ячейках В3:В7 введем известные объемы продаж магазинов.

Исходные данные введены, таблица имеет вид, показанный на рисунке 13.11, а.

Долю каждого магазина в общем объеме продаж можно рассчитать по формуле:

,

где i – номер магазина (i=1,2,3,4,5);

- объем продаж i-го магазина;

- знак суммирования.

Вычислим в ячейке B8 общий объем продаж всей торговой сети по формуле:

=Sum(B3:B7)

Теперь можно вычислить долю продаж первого магазина.

Для этого в ячейку С3 вводим формулу:

=B3/B8*100

Полученные результаты приведены на рисунке 13.11, б.

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

Однако при первом же копировании формулы из ячейки С3 в ячейку С4 получим ошибку, т.к. при копировании изменятся все адреса, и в ячейке С4 получится формула:

=B4/B9*100.

Адрес В3 автоматически поменялся на В4 – это нам нужно, но адрес В8 не должен изменяться, во всех формулах должна фигурировать общая сумма – значение в ячейке В8, а при копировании адрес поменялся на В9.

Таким образом, в ячейке С3 необходимо подправить формулу для вычисления доли первого магазина и указать абсолютный адрес для ячейки В8. Правильная формула выглядит так:

=B4/B$8*100

Теперь эту формулу можно скопировать в ячейки С4:С7.

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

Окончательные результаты приведены на рисунке 13.11, в.

Рис. 13.11. Пример использования копирования формул

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