Для создания простой формулы (например, =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) Как используются имена в формулах?
12) Как создать имя на ячейку, группу ячеек?
13) Как использовать существующие заголовки строк и столбцов в качестве имен в формулах?
14) Как создать, удалить формулу?
15) Как переместить формулу?
16) Как скопировать формулу?
