Создание и удаление формулы

Для создания простой формулы (например, =128+345), содержащей операторы и константы, необходимо выполнить следующие действия:

1) щелкните ячейку, в которую требуется ввести формулу;

2) введите = (знак равенства);

3) введите формулу;

4) нажмите клавишу Enter.

Для создания формулы, содержащей ссылки или имена (например, =A1+23), необходимо выполнить следующие действия:

1) щелкните ячейку, в которую требуется ввести формулу;

2) в строку формул введите = (знак равенства);

3) выполните одно из следующих действий.

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

- чтобы создать ссылку на именованный диапазон, нажмите клавишу F3, выберите имя в поле Вставка имени и нажмите кнопку ОК;

4) нажмите клавишу Enter.

Приведенные в табл. 1.12 формулы содержат относительные ссылки на другие ячейки и их имена. Ячейка, содержащая формулу, называется зависимой ячейкой, если ее значение зависит от значений в других ячейках. Например, ячейка B2 является зависимой, если она содержит формулу =C2.

Таблица 1.12 – Примеры формул с относительными ссылками

Пример формулы	Описание
=C2	Использует значение в ячейке C2
=Лист2!B2	Использует значение в ячейке B2 на Лист2
=Ответственность-Актив	Вычитает ячейку Ответственность из ячейки Актив

Для удаления формулы:

1) выделите ячейку, содержащую формулу;

2) нажмите клавишу Del.

Перемещение и копирование формулы

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

1) Выделите ячейку, содержащую формулу.

2) Убедитесь, что ссылки на ячейки, используемые в формуле, дают требуемый результат. Выберите требуемый тип ссылки. Чтобы переместить формулу, используйте абсолютную ссылку.

3) выберите команду меню Правка Þ Копировать;

4) выделите ячейку, в которую требуется ее скопировать;

5) чтобы скопировать формулу и форматирование, выберите команду меню Правка Þ Вставить;

6) чтобы скопировать только формулу, выберите команду меню Правка Þ Специальная вставка, а затем выберите вариант формулы.

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

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

Для выделения ячеек, содержащих формулы:

1) выполните одно из следующих действий:

- выделите диапазон, включающий ячейки, которые нужно выбрать;

- выделите одну ячейку, чтобы выделить все ячейки того же типа на активном листе;

2) выберите команду меню Правка Þ Перейти;

3) нажмите кнопку Выделить;

4) выберите вариант формулы и установите флажки, соответствующие типам данных, которые нужно выбрать.

ХОД ВЫПОЛНЕНИЯ РАБОТЫ

2.1 Открыть книгу с таблицей, созданной в лабораторной работе №2 и сохранить ее под именем «ваша фамилия»_excel3.

2.2 Заполнить таблицу данными.

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

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

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

2.6 Проверить результаты вычисления в таблице на втором листе при изменении данных в таблице на первом.

2.7 Выполнить дополнительное задание (см. индивидуальное задание).

2.8 Сохранить все изменения в книге.

ИНДИВИДУАЛЬНЫЕ ЗАДАНИЯ

Номер варианта соответствует 2-й цифре по журналу академической группы (цифра 0 – вариант№10).

Вариант 1. Сравнительная таблица розничных цен на продовольственные товары по городам Украины.

Товар	Киев	Одесса	Харьков	Ужгород	Сумы	Средняя цена
Говядина
Свинина
Птица
Рыба

В ячейки столбца «Средняя цена» ввести формулы для вычисления среднего значения содержимого ячеек соответствующей строки.

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

Вариант 2. Данные о продаже автомобилей в 200__г.

Марка	1 квартал	%	2 квартал	%	3 квартал	%	4 квартал	%
БМВ
Форд
Ауди
Рено
Тойота
Всего

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

Дополнительное задание. Добавить столбец «Сумма за год» с формулами вычисления объема продаж за год для каждой марки автомобиля.

Вариант 3. Температура воздуха в городах мира с 1 по 7 января 200__г.

Дата	Москва	Рим	Париж	Лондон	Мадрид
01 янв 2000
02.01.2000
03.1.2000
04.01.2000
5 янв
6.01
07.01.2000 13:30
Средн. температура

В ячейке строки «Средняя температура» ввести формулу для вычисления среднего значения температуры в столбце.

Дополнительное задание. Добавить столбец «Максимальная температура», в который ввести формулы вычисления максимальной температуры за каждое число.

Вариант 4. Выручка от продажи книжной продукции в 200_ г.

Название книги	1 кв.	2 кв.	3кв.	4 кв.	Процент за год
1.
2.
3.
4.
5.
6.
Всего за год

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

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

Вариант 5. Итоговые экспертные оценки (от 0 до 10 баллов) кандидатов на должность генерального директора фирмы «Аква».

Номер кандидата	Эксперты	Сумма баллов	Процент

№1
№2
№3
№4
№5
Всего

В столбце «Процент» вычисляется процентное отношение ячеек столбца «Сумма баллов» от значения ячейки строки «Всего».

Дополнительное задание. Добавить столбец для еще 1-го эксперта и строку с еще 1-м кандидатом с соответствующей коррекцией формул в таблице.

Вариант 6. Прайс-лист фирмы «Трианон» на 22 декабря 200_ г.

Наименование товара	Розничная цена, руб.	От 10 шт.	От 100 шт.	Свыше 1000 шт.	Дилеры
1.
2.
3.
4.
5.
6.

Формулы в ячейках столбцов «От 10 шт. ... Диллеры» должны учитывать, что цена уменьшается на 1 процент по сравнению с предыдущей колонкой.

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

Вариант 7. Перечень доходов и расходов за 1-е полугодие.

Статья \ Месяц	Янв.	Февр.	Март	Апр.	Май	Июнь
Доходы
Оклад
Премия
Совместительство
Всего доходов
Расходы
Жилье
Кредит
Питание
Налоги
Другие расходы
Всего расходов
Остаток
Остаток за полугодие

В ячейки строки «Всего доходов» и «Всего расходов» ввести формулы, вычисляющие сумму значений ячеек соответствующего столбца. В ячейках строки «Остаток» должны быть формулы, вычисляющие разность содержимого ячеек строк «Всего доходов» и «Всего расходов», в строке «Остаток за полугодие» – сумма содержимого ячеек строки «Остаток».

Дополнительное задание. Добавить столбцы для ввода данных и вычисления доходов и расходов за весь год.

Вариант 8. Результаты зимней сессии 200_ г. студентов группы КИТ-__.

Ф.И.О.	Математика	Программирование	Иностранный язык	Физика	Сумма	Средний балл
1.
2.
3.
4.

В столбцах «Сумма» и «Средний балл» должны быть введены соответствующие формулы.

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

Вариант 9. Анализ цен на товары в конкурирующих фирмах.

Наименование товара	Название фирмы
ВЛАДОС	ОК	Контраст
1.
2.
3.
4.
5.
Сумма

В ячейках строки «Сумма» ввести соответствующие формулы для вычисления суммы по столбцам.

Дополнительное задание. Добавить столбец «Минимальная цена», в который ввести формулы вычисления минимальной цены на каждый товар по фирмам.

Вариант 10. Изменение курса валют за период 1 по 5 марта 200_г.

Дата	Доллар	Марка	Доллар/Марка
1 мар 2000
2 мар 2000
3 мар 2000
4 мар 2000
5 мар 2000
Средний за период
Процент роста

В столбце «Доллар/Марка» должна быть формула для вычисления отношения курсов доллара и марки в соответствующей строке. В строку «Средний за период» вводятся формулы для вычисления среднего значения в столбце, в строку «Процент роста» – формулы для определения процентного прироста курса на 5 марта относительно курса на 1 марта 200_г.

Дополнительное задание. Добавить столбец «Гривна» и столбцы «Гривна/Доллар» и «Гривна/Марка», в которые ввести формулы вычисления отношения курсов гривны и доллара, гривны и марки в соответствующей строке.

КОНТРОЛЬНЫЕ ВОПРОСЫ

1) Какие элементы может включать в себя формула?

2) Что такое константа?

3) Что такое оператор?

4) Какие виды операторов поддерживает Excel?

5) Что такое ссылки?

6) Какие типы ссылок используются в Excel?

7) Что такое функции?

8) Какие категории функций используются в Excel?

9) Каков порядок выполнения операций, если формула содержит несколько операторов?

10) Как изменить порядок выполнения операций?

11) Как используются имена в формулах?