- •Министерство образования Российской Федерации
- •Л.С. Хрусталева
- •Введение
- •1. Подготовка Microsoft Excel 2000 к работе
- •1.1. Запуск приложения Excel
- •1.2. Окно программы Excel и его элементы
- •Строка формул
- •1.3. Завершение сеанса работы
- •Контрольные вопросы и задания
- •2. Настройка программы
- •2.1. Вкладка Вид
- •2.2. Вкладка Вычисления
- •2.3. Вкладка Правка
- •2.4. Вкладка Переход
- •2.5. Вкладка Общие
- •2.7. Вкладка Диаграмма
- •2.6. Вкладка Списки
- •2.8. Вкладка Цвет
- •Контрольные вопросы и задания
- •3. Функции Excel для работы с окнами
- •4. Создание рабочего листа
- •4.1. Основные термины Excel
- •4.2. Перемещение по рабочему листу
- •4.3. Ввод данных
- •4.4. Ввод формул
- •4.5. Выделение ячеек и диапазона ячеек
- •4.6. Выделение строк и столбцов
- •Рис. 4.5. Таблица с выделенными несмежными строками
- •Задания
- •5. Редактирование рабочего листа
- •5.1. Редактирование содержимого ячеек
- •5.2. Перемещение и копирование
- •5.3. Вставка ячеек
- •5.4. Удаление ячеек
- •Задания
- •6. Форматирование рабочего листа
- •6.1. Форматирование чисел
- •6.2. Пользовательские форматы
- •6.3. Изменение ширины столбцов и высоты строк
- •6.3.1. Изменение ширины столбца
- •6.3.2. Изменение высоты строки
- •6.3.3. Отображение столбцов и строк страницы
- •6.4. Выравнивание содержимого ячеек
- •6.5. Изменение гарнитуры, размера и начертания шрифта
- •6.6. Использование рамок
- •6.7. Цветовое оформление
- •6.8. Автоматическое форматирование
- •6.9. Использование стилей
- •6.10. Создание новых стилей
- •6.11. Защита ячеек и листов
- •Контрольные вопросы и задания
- •7. Использование формул
- •7.1. Создание формул
- •7.2. Ссылки на ячейки в формулах
- •7.2.1. Использование относительных ссылок
- •7.2.2. Использование абсолютных ссылок
- •На рис. 7.3 представлена таблица в режиме отображения формул с абсолютными ссылками.
- •Рис.7.3. Таблица в режиме отображения формул
- •7.2.3. Смешанные ссылки
- •7.2.4. Редактирование абсолютных и относительных ссылок
- •7.2.4. Ввод ссылок на другие рабочие листы
- •7.3. Использование операций в формулах
- •7.4. Автоматическое вычисление
- •7.5. Исправление ошибок в формулах
- •8. Функции в формулах
- •8.1. Использование аргументов
- •8.2. Обзор средств создания функций
- •8.3. Ввод функций
- •8.3.1. Ввод функций вручную
- •8.3.2. Использование кнопки Автосуммирование
- •8.3.3. Использование Мастера функций
- •8.4. Редактирование функций
- •8.5. Финансовые функции
- •8.6. Функции даты и времени
- •8.7. Математические функции
- •8.8. Статистические функции
- •8.9. Логические функции
- •8.10. Текстовые функции
- •9. Вывод таблицы на печать
- •9.1. Печать рабочего листа
- •9.2. Вставка разделителей конца страницы
- •9.3. Задание параметров страницы
- •Рис. 9.4. Диалоговое окно Параметры страницы
- •9.4. Задание заголовков для печати
- •9.5. Печать компонентов листа
- •Контрольные вопросы
- •10. Библиографический список
- •Оглавление
57
ссылок. Например, можно создать ссылку, состоящую из относительной ссылки на столбец и абсолютной ссылки на строку, или наоборот. Если скопировать формулу, содержащую смешанную ссылку $А1, то номер строки изменится в зависимости от нового положения формулы, а номер столбца останется прежним. С помощью клавиши [F4] можно выбрать соответствующий тип смешанной ссылки или преобразовать ссылку в абсолютную или относительную.
7.2.4. Редактирование абсолютных и относительных ссылок
Чтобы изменить тип существующей ссылки, надо выделить ячейку, содержащую формулу, и поместить точку вставки в соответствующую позицию в строке формул. Нажать клавишу [F4] несколько раз, чтобы выбрать необходимый тип ссылки, и нажать клавишу [Enter].
7.2.4. Ввод ссылок на другие рабочие листы
Можно использовать ссылки на ячейки другого листа рабочей книги. Для этого надо поместить название листа и восклицательный знак (!) перед ссылкой на ячейку. Например, ссылка на ячейку А1 листа Лист1 выглядит следующим образом: Лист1!А1. Если лист будет переименован, в соответствующих ссылках следует указать его новое имя. Если новое имя рабочего листа содержит пробелы, то в ссылках такое имя необходимо заключать в одинарные кавычки (апостроф) например: 'Бюджет 2002' !А1.
7.3.Использование операций в формулах
С помощью формул Excel можно произвести некоторые вычисления, выполнить определенные действия над текстом, сравнить два значения и произвести операции над ссылками.
Операции, которые выполняются над элементами формул, например, сложение, вычитание или умножение, задаются в формуле с помощью операторов.
58
Арифметические операторы. Выполняют основные математические операции над числами; результатом выполнения арифметического оператора являются числовые значения.
Операторы сравнения. Сравнивают два значения, результат выполнения – логические значения Истина или Ложь.
Таблица 7.1
Оператор |
Значение |
|
|
( |
Открыть скобку |
) |
Закрыть скобку |
* |
Умножить |
/ |
Разделить |
+ |
Сложить |
- |
Вычесть |
= |
Равно |
& |
Присоединение текста |
< |
Меньше |
<= |
Меньше или равно |
> |
Больше |
>= |
Больше или равно |
<> |
Не равно |
|
|
Оператор текста. Объединяет два или более текстовых значения в единое текстовое значение. Оператор текста & соединяет или сцепляет два текстовых значения, создавая одно непрерывное текстовое значение. Если одно из исходных значений является ссылкой на ячейку, в которой содержится формула, то оператор & использует значение формулы как текстовое значение.
Иногда причиной возникновения ошибок в вычислениях формул является ввод арифметических операций в неправильном порядке. В Excel предусмотрен определенный порядок выполнения операций.
59
Можно изменить порядок выполнения операций в формуле, заключив часть формулы в круглые скобки. Excel сначала выполнит все операции в скобках, а затем все остальные операции в соответствующем порядке.
В длинных формулах, содержащих круглые скобки, каждой открывающей скобке должна соответствовать закрывающаяся скобка, иначе формула не будет принята Excel.
Если в формуле используются арифметические операции одного уровня, вычисления проводятся последовательно, слева направо.
Операторы представлены в табл. 7.1 в порядке убывания их приоритета при вычислениях.
Адресные операции позволяют в формулах ссылаться на различные группы ячеек. Например, формула «=СУММ(А4:А24) возвращает сумму значений ячеек диапазона, в левом верхнем углу которого находится ячей-
ка А4, а в правом нижнем углу |
ячейка А24. |
|
В табл. 7.2 приведены адресные операции Excel. |
||
|
|
Таблица 7.2 |
|
|
|
Адресная операция |
|
Описание |
|
|
|
Диапазон (двоеточие) |
|
Ссылка на все ячейки внутри границ |
|
|
диапазона включительно |
Объединение (запятая) |
|
Ссылка на объединение ячеек диа- |
|
|
пазонов |
Пересечение (пробел) |
|
Ссылка на общие ячейки диапазонов |
|
|
|
7.4. Автоматическое вычисление
Для вычисления некоторых промежуточных значений или проверки правильности вычислений не обязательно создавать дополнительные формулы или использовать калькулятор. Достаточно выделить нужный диапазон ячеек, открыть контекстное меню функции автовычисления, выполнив щелчок правой кнопкой мыши в соответствующей области строки состояния, и указать, какую именно информацию о данных надо получить. Команды указанного контекстного меню описаны в табл. 7.3.
60
На рис. 7.4 приведен пример использования функции Автоматическо-
го вычисления.
|
|
|
|
|
Таблица 7.3 |
|
|
|
|
||
Команда |
Назначение |
|
|
||
|
|
||||
Сумма |
Определение суммы всех число- |
||||
|
вых значений из ячеек выделен- |
||||
|
ного диапазона |
|
|
||
|
|
|
|||
Максимум |
Определение |
максимального |
|||
|
значения в ячейках выделенного |
||||
|
диапазона |
|
|
|
|
|
|
||||
Минимум |
Определение минимального зна- |
||||
|
чения |
в |
ячейках |
выделенного |
|
|
диапазона |
|
|
|
|
|
|
|
|||
Количество чисел |
Подсчет |
количества числовых |
|||
|
значений в маркированном диа- |
||||
|
пазоне |
|
|
|
|
|
|
||||
Количество значений |
Подсчет количества содержащих |
||||
|
данные |
ячеек в |
маркированном |
||
|
диапазоне |
|
|
|
|
|
|
||||
Среднее |
Вычисление среднего арифмети- |
||||
|
ческого всех числовых значении, |
||||
|
расположенных |
в |
маркирован- |
||
|
ном диапазоне |
|
|
||
|
|
||||
Нет |
Отмена режима автовычисления |
||||
|
|
|
|
|
|
7.5.Исправление ошибок в формулах
Иногда при создании формулы пользователем допускаются некоторые неточности при вводе или применяются недопустимая операция. В таких случаях Excel отображает в соответствующей ячейке значение ошибки. Это позволяет определить причину возникновения ошибки и исправить
61
формулу в соответствии с указанным значением. В табл. 7.4 представлены значения ошибок и их возможные причины.
|
Таблица 7.4 |
|
|
Ошибка |
Описание |
|
|
#ДЕЛ/0 |
В формуле присутствует операция |
|
деления на ноль |
|
|
#Н/Д |
Формула ссылается на неопределен- |
|
ные данные |
|
|
#ИМЯ? |
В формуле используется неопреде- |
|
ленное имя |
|
|
#ПУСТО! |
Формула содержит ссылку на пустое |
|
пересечение ячеек |
|
|
#ЧИСЛО! |
Формула использует неверное зна- |
|
чение в качестве числового аргу- |
|
мента встроенной функции или воз- |
|
вращает результат, не попадающий |
|
в область допустимых значений |
|
|
#ССЫЛКА! |
В формуле используется недопу- |
|
стимая ссылка на ячейку |
|
|
#ЗНАЧ! |
Формула содержит недопустимый |
|
аргумент или операцию |
|
|
Чтобы легче было найти ошибку, можно установить режим отображения формул, а не результатов. Для этого следует активизировать опцию Формула на вкладке Вид диалогового окна Параметры. Ширина столбцов автоматически увеличится с целью улучшения обзора. Режим отображения результатов вычисления восстанавливается при выключении опции
Формула.
62
Рис. 7.4. Нахождение максимального значения при помощи Функции Автоматического вычисления
Контрольные вопросы и задания
1.Какими способами можно создавать формулы?
2.Приведите пример создания формулы разными способами.
3.С каких символов может начинаться ввод формулы?
4.Какой способ ввода форму наиболее удобен, в случае если данные находятся на большом расстоянии друг от друга?
5.Что такое ссылка на ячейку?
6.Перечислите типы ссылок.
7.Приведите примеры использования разных типов ссылок.
8.Каким образом будет записана ссылка на ячейку другого листа?
9.В каком случае при записи ссылки используются апострофы?
10.Перечислите виды операций, используемых в формулах.
11.Приведите примеры использования различных операций в формуле.
12.В каком случае можно использовать функцию Автоматического вычисления?
13.Перечислите типы ошибок, часто встречающиеся при записи формул.