Пересчет формул на рабочем листе

 

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

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

Циклическая ссылка - это формула, которая в явном или косвенном виде содержит ссылку на саму себя. Если в рабочем листе встретилась циклическая ссылка, Excel иногда может разрешить ее с помощью итераций, когда последовательные вычисления приводят к единственному решению. Excel при этом просто многократно производит пересчет рабочего листа. Если вы создаете циклическую ссылку, выберете Сервис/Параметры, щелкните на вкладке Вычисления и установите флажок Итерации.

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

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

Рис.8. Сообщение о циклической ссылке

 

Работа с функциями

 

Чтобы облегчить работу с формулами, Excel имеет возможность работать с функциями. Функции - это встроенные формулы.

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

	А	В
	Иванов
	Петров
	Сидоров
	Козлова
	Дроздова
	Титов

 

Предположим, что вам нужно вычислить средний возраст и поместить результат в ячейку В7. Для этого можно построить формулу следующего вида: =(В1+В2+В3+В4+В5+В6)/6 и поместить ее в рабочий лист. Однако гораздо проще использовать функцию, которая может вычислить среднее для набора значение из диапазона В1:В6. Для этого введите в рабочий лист следующую формулу: =СРЗНАЧ (В1:В6).

Очевидно, что работа с функцией значительна проще.

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

1. Ввести знак равенства =.

2. Ввести имя функции, а затем открывающуюся скобку (.

3. Ввести аргументы функции, а затем закрывающуюся скобку).

В примере с функцией СРЗНАЧ использовался только один аргумент - диапазон В1:В6. Часто для функций требуется несколько аргументов, тогда их следует разделить точками с запятой. Например, можно переписать показанную выше функцию СРЗНАЧ таким образом:

=СРЗНАЧ (В1;В2;В3;В4;В5;В6).

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

В качестве аргументов функции можно подставлять реальные числа:

=СРЗНАЧ(24;31;43;21;28;24). Аргументами могут быть результаты, полученные от других функций.

 

Вставка функций

 

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

Предположим, что вы покупаете квартиру в рассрочку и хотите рассчитать свои ежемесячные выплаты. Достаточно сложно составить формулу, которая производит такой расчет. Вместо этого лучше использовать функцию ППЛАТ (в версии Office 2002 ивыше изменено имя этой функции, она называется ПЛТ). Для вызова функции необходимо проделать следующую последовательность шагов:

1. Щелкните в ячейке, где должна появиться функция.

2. Выберите Вставка/Функция или щелкните на кнопке Вставка функции. Excel выведет на экран окно диалога, показанное ниже.

Рис.9. Выбор функции

3. Если на экране Помощник не появился, щелкните на кнопке запуска Помощника в окне диалога Мастер функций.

4. Чтобы получить справку по выделенной функции, щелкните по строчке Справка по этой теме (рис.9). Помощник выведет на экран раздел

Рис.10. Описание функции

справки по выделенной функции, как показано ниже на рисунке 10. Этот процесс, возможно, придется повторить несколько раз.

5. После того как вы найдете нужную функцию, выделите ее и щелкните на кнопке ОК в окне диалога Мастер функций. Excel выведет на экран окно ввода аргументов функции (рис.11).

6. Укажите аргументы в соответствующих полях. Когда вы щелкнете в поле аргумента, Excel выведет его краткое описание. После того, как вы введете последний из обязательных аргументов, Excel произведет расчет и отобразит результат внизу окна аргументов.

 

Рис.10. Описание функции

 

Рис.11

 

7. Щелкните на ОК, когда закончите ввод аргументов. Excel закроет окно палитры формул и поместит функцию в ячейку (если вы щелкнули на кнопке Вставка функции на стандартной панели инструментов при выделенной ячейке) или в точку курсора (если вы щелкали на кнопке Вставка функции при выделенной строке формул). На рис.12 показан рабочий лист, в котором вычисляется ежемесячные выплаты для покупки новой квартиры. Он показывает, сколько нужно платить ежемесячно за квартиру стоимостью в 100 000 у.е., купленную в рассрочку под 4% годовых, со сроком выплат в течение 4 лет. При вызове функций ПЛТ или ППЛАТ в строке «Норма» (рис. 11) следует указать процентную ставку за 1 период выплат (за 1 месяц), т.е. 4%/12. Параметр НЗ вводится со знаком минус, если кредитор дает деньги клиенту.

 

Рис.12. Рабочий лист, составленный с помощью Мастера функций и Помощника

 

13. Копирование, перемещение и заполнение диапазонов

 

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

Диапазон - это прямоугольная область рабочего листа. Наименьший диапазон - это ячейка.

 

14. Выделение диапазонов

 

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

 

Рис.13. Выделение диапазона

 

Диапазон можно описать, указав адреса его крайних ячеек (левый верхний и правый нижний), разделенные двоеточием. Например, диапазон на рисунке 13 описывается как В2:D4.

С помощью мыши можно выделить несколько несмежных диапазонов, как показано на следующем рисунке (рис.14). Чтобы выделить несмежные диапазоны, удерживайте нажатой клавишу CTRL во время выделения.

 

 

 

Рис.14. Выделение несмежных диапазонов

 

Диапазоны также можно выделять с помощью клавиатуры. Сначала выделите угловую ячейку диапазона. Затем нужно увеличить размер выделения с помощью клавиш перемещения курсора, удерживая нажатую клавишу SHIFT.

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

Чтобы выделить строку или столбец, просто щелкните на заголовке столбца или номере строки. Excel выделит целую строку или столбец. (Чтобы выделить строку или столбец с помощью клавиатуры, сначала выделите ячейку в нужной строке или столбце, а затем нажмите CTRL+ПРОБЕЛ, чтобы выделить столбец, или SHIFT+ПРОБЕЛ, чтобы выделить строку.)

 

15. Копирование диапазонов

 

Вы можете скопировать содержимое ячеек одного диапазона в другой. Например, если вы составляете проект бюджета, как показано на рисунке, может понадобиться скопировать содержимое диапазона В1:В8 в диапазон С1:С8. Для этого необходима следующая последовательность действий:

1. Выделите диапазон, содержащий подписи, значения и формулы, которые вы хотите копировать (в нашем примере В1:В8).

 

 

Рис.15. Копирование диапазонов

 

2. Щелкните на кнопке Копировать на стандартной панели инструментов.

3. Щелкните в верхней ячейке диапазона, в которой будет производиться вставка скопированной информации (в нашем примере в С1).

4. Щелкните на кнопке Вставить на стандартной панели инструментов. Excel вставит содержимое буфера в новый диапазон.

 

Рис.16. Копирование диапазонов

 

5. Если нужно, измените значение ячеек. Например, в ячейке С1 следует заменить январь на февраль.

Копирование формул работает аналогично. Когда вы копируете формулу, Excel изменяет ссылки в ней, чтобы она правильно работала на новом месте. Например, формула в ячейке В8 (=В2-В4-В5-В6-В7) при вставке в ячейку С8 изменится (=С2-С4-С5-С6-С7).

 

16. Перемещение диапазонов

 

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

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

2. Щелкните в левой верхней ячейке диапазона, в которой будет производиться вставка вырезанной информации, и щелкните на кнопке Вставить на стандартной панели инструментов. Excel переместит вырезанное содержимое ячеек в новый диапазон.

3. Чтобы переместить выделенный диапазон с помощью мыши, просто щелкните на границе диапазона и перетащите его на новое место. Для копирования удерживайте нажатой клавишу CTRL во время перетаскивания.

 

Дополнение строк и столбцов

 

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

1. Выделите строку, над которой должна появиться новая строка.

2. Выберите Вставка/Строки.

Чтобы вставить столбец, выполните следующие действия:

1. Выделите столбец, слева от которого должен появиться новый столбец.

2. Выберите Вставка/Столбцы.

Чтобы вставить несколько строк или столбцов одновременно, выделите соответствующее количество строк или столбцов перед использование команд Вставка/Строки или Вставка/Столбцы.

 

18. Форматирование рабочих листов

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

Таблица 3

Основные средства форматирования в Excel

Кнопка	Название	Описание
	Размер шрифта	Изменение размера шрифта выделенного текста
	Полужирный	Применение и отмена оформления выделенного текста полужирным шрифтом
	Курсив	Применение и отмена оформления выделенного текста курсивом
	Подчеркнутый	Применение и отмена оформления выделенного текста подчеркиванием
	По левому краю	Выравнивание содержимого выделенных ячеек по левому краю
	По центру	Выравнивание содержимого выделенных ячеек по центру
	По правому краю	Выравнивание содержимого выделенных ячеек по правому краю
	Объединить и поместить в центре	Объединение нескольких выделенных ячеек в одну. Содержимое будет расположено по центру объединенной ячейки
	Денежный формат	Форматирование выделенных ячеек в денежном формате. (Чтобы изменить тип денежной единицы, используйте вкладку Число в окне диалога Формат ячеек.)
	Процентный формат	Установка процентного формата для выделенных ячеек. Обратите внимание, что Excel превратит 10 в 1000%, а 0,1 - в 10%.
	Формат с разделителями	Установка формата с разделителями для выделенных ячеек. (Excel отобразит длинное число с пробелами между группами разрядов)
	Увеличить разрядность	Увеличение числа дробных знаков для выделенных ячеек.
	Уменьшить разрядность	Уменьшение числа дробных знаков для выделенных ячеек.
	Уменьшить отступ	Уменьшение отступа для выделенной ячейки
	Увеличить отступ	Увеличение отступа для выделенной ячейки
	Границы	Добавление границ для выделенной ячейки или диапазона. Для задания другого типа границ следует нажать стрелку рядом с кнопкой и выбрать тип границ из палитры
	Цвет заливки	Добавление, изменение или удаление цвета заливки для выделенного объекта
	Цвет шрифта	Форматирование выделенного текста заданным цветом

 

19. Печатание, сохранение и открытие книг

 

Сохранять, открывать, выводить на печать книги Excel следует таким же образом, как и документы Word (см. часть I лабораторного практикума).