2.3. Копирование формул

При написании функции ЕСЛИ нужно быть очень внима­тельным. С другой стороны, набрав формулу один раз, можно уже не повторять эти монотонные действия. Копи­ровать формулы можно, применив операцию автозаполне­ния.

1. Выделите ячейку B15 и подведите указатель мыши к маркеру автозаполнения (в правом нижнем углу ячей­ки). Указатель примет вид черного крестика.

2. Нажмите левую кнопку мыши и протащите указатель к ячейке В26. Excel скопирует формулу из В15 во все выделенные ячейки.

3. Выделите D15 и выполните описанную выше операцию автозаполнения вплоть до ячейки D26.

4. Выделите ячейку В16. Excel изменил оригинальную формулу

=ECЛИ (ПPABCИMB(A15;1)=”В”;”Koнmpaкm”; "Субконтракт")

на

=ЕСЛИ (ПРАВСИМВ(А16;1)”Контракт"; "Субконтракт")

Программа изменила ссылку. Вместо А15 введена ссыл­ка А16.

По умолчанию в формулах применяются относительные ссылки. Относительная ссылка указывает положение ячей­ки относительно той, в которой содержится формула. Если копировать формулу из ячейки В15 в В16, то ссылка меняется с А15 на А16 (так как номер строки увеличился на единицу). Если бы формула копировалась из ячейки В15 в Е15 (вдоль строки через два столбца), то ссылка изменилась бы с А15 на D15.

Если вы не желаете, чтобы копирование производилось с изменением ссылок, необходимо использовать абсолют­ные ссылки. Абсолютные ссылки позволяют обращаться к фиксированным ячейкам. Для того чтобы ссылка стала абсолютной, необходимо перед номером строки и буквой столбца вставить знаки доллара ($). Так, относительная ссылка С4:С9 станет абсолютной, если набрать $С$4:$С$9. Теперь формула, содержащая эту ссылку и копируемая в любое место листа, будет обращаться все к тому же диапазо­ну ячеек С4:С9.

Кроме того, ссылки могут быть смешанными, например, $С4 означает, что ссылка на столбец абсолютная, а на строку – относительная.

2.4. Консолидация данных

Существует несколько способов консолидации данных. Определить поквартальный доход фирмы с помощью команды Кон­солидация.

1. Выберите Лист2, на котором будут консолидироваться данные, и присвойте ему новое имя. Для этого дважды щелкните на ярлычке Лист2 книги Работа 2010 и введите имя Поквартально.

2. В ячейке А1 нового листа наберите Квартал, а в В1 – Всего. Это будут заголовки столбцов. Шрифт заголов­ков сделайте 12 пт, полужирный.

А теперь начнем консолидацию.

1. Выделите ячейку А2 листа Поквартально и выберите Данные/Консолидация.

2. Щелкните на ярлычке листа Доходы 2010. Excel введет имя данного листа в поле ссылки в окне диалога.

3. Щелкните на кнопке с красной стрелкой (в поле Ссылка); диалоговое окно свернется. Выделите ячейки D15:E26. В окне диалога появятся абсолютные ссылки на эти ячейки.

4. Снова щелкните на красной стрелке. Окно развернется.

5. Если в поле Функция не появилась функция Сумма, то выберите ее из списка.

6. В области Использовать в качестве имен установите фла­жок Значения левого столбца, чтобы значения в столбце Квартал стали именами данных создаваемого листа.

7. Установите флажок Создавать связи с исходными данными, чтобы связать данные листа Доходы 2010 с листом Поквартально. Щелкните на кнопке ОК.

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

В окончательном виде таблица будет соответствовать образцу: