Современная
Гуманитарная
Академия
Дистанционное образование
________________________________________________________
4083.01.01;МУ.04;1
МЕТОДИЧЕСКИЕ УКАЗАНИЯ
РАБОТА С ЭЛЕКТРОННЫМИ ТАБЛИЦАМИ MICROSOFT EXCEL 2007
СОЗДАНИЕ И ИЗМЕНЕНИЕ ФОРМУЛ
МОСКВА 2010
Разработано , сертиф. специалистом Microsoft (Microsoft office specialist)
Под ред. , канд. физ.-мат. наук
Рекомендовано Учебно-методическим
советом в качестве учебного пособия
для слушателей ДПО
МЕТОДИЧЕСКИЕ УКАЗАНИЯ
РАБОТА С ЭЛЕКТРОННЫМИ ТАБЛИЦАМИ MICROSOFT EXCEL 2007
СОЗДАНИЕ И ИЗМЕНЕНИЕ ФОРМУЛ
Настоящие методические указания предназначены как для преподавателей, так и слушателей курсов ДПО по офисным продуктам Microsoft.
Для слушателей ДПО Современной Гуманитарной Академии
_____________________________________________________________________________________
© СОВРЕМЕННАЯ ГУМАНИТАРНАЯ АКАДЕМИЯ, 2010
ОГЛАВЛЕНИЕ
Стр.
1 ПРИМЕНЕНИЕ ФОРМУЛ ДЛЯ ВЫЧИСЛЕНИЯ ТАБЛИЧНЫХ ДАННЫХ.. 4
2 Создание функций с использованием мастера функций.. 5
2.1 Простые математические вычисления с использованием функции СУММ(А) 7
2.1.1 Суммирование. 7
2.1.2 Выборочная сумма. 8
2.1.3 Создание формул с использованием кнопки Сумма. 10
2.2 О логических функциях. 11
2.3 Поиск информации в списке данных. 15
2.4 Функции даты и времени. 17
2.5 Отображение и редактирование формул. 19
3 Относительные и абсолютные ссылки.. 20
4 Исправление ошибок.. 21
4.1 Настройка исправлений общих ошибок в формулах. 22
4.2 Обозначения ошибок. 23
5 Отслеживание зависимостей в формулах.. 23
1 ПРИМЕНЕНИЕ ФОРМУЛ ДЛЯ ВЫЧИСЛЕНИЯ ТАБЛИЧНЫХ ДАННЫХ
Рабочие книги Excel 2007 представляют собой документы, позволяющие не только хранить и упорядочивать данные, но и выполнять над ними различные операции. К числу таких операций относятся, например, вычисления итоговых (суммарных) значений данных, введенных в заданный (выделенный) диапазон ячеек c помощью математических формул.
Математическая формула – символическая запись законченного логического суждения (определения величины, уравнения, неравенства или тождества). С помощью входящих в формулу операторов и операндов производятся вычисления искомых величин. Формула и результат вычис-ления формулы константами не являются.
Оператором называют знак или символ, задающий тип вычисления в формуле. Существуют математические, логические операторы, операторы сравнения и ссылок.
Операнд – это элемент вычисления (константы, функции и ссылки).
Константа – постоянное (не вычисляемое) значение. Может быть числом или текстом.
Ссылка – адрес ячейки или диапазона ячеек на рабочем листе Excel, в которых содержатся значения, используемые в формуле. Можно задавать ссылки на ячейки других листов текущей книги и на другие книги. Ссылки на ячейки других книг называются связями.
Чтобы применить формулу, прежде всего, нужно ввести ее в ячейку. Сначала в ячейку вводится знак равенства, который указывает, что последующие символы должны трактоваться как формульное выражение, а не как текст. Результат вычисления по формуле будет заноситься в ту же ячейку, в которую введена формула.
Для ввода формулы с клавиатуры:
1) выделите ячейку, в которую требуется ввести формулу;
2) введите = (знак равенства);
3) выделите мышью ячейку, в которой содержится операнд формулы;
4) введите знак оператора формулы;
5) выделите мышью ячейку, в которой содержится второй операнд формулы;
6) при необходимости продолжайте ввод знаков операторов и выделение ячеек;
7) подтвердите ввод формулы в ячейку: нажмите клавишу Enter или Tab или кнопку Ввод (галочка) в строке формул.
Например, сумма чисел, содержащихся в ячейках С2 и С3, вычисляется по формуле =С2+С3. Введенную формулу можно легко изменить, щелкнув на ячейке и отредактировав содержимое строки формул. Например, можно изменить предыдущую формулу так, чтобы новая формула вычисляла разность содержимого тех же ячеек =С2-С3.
Арифметические операторы служат для выполнения арифметических операций над числами, таких, как сложение, вычитание, умножение. Арифметические операторы Excel приведены в таб-лице 1.
Таблица 1. Арифметические операторы Excel
Оператор | Значение | Пример |
+ (знак плюс) | Сложение | A1+A2 |
– (знак минус) | Вычитание Отрицание | A1-A2 -A1 |
* (звездочка) | Умножение | A1*A2 |
/ (косая черта) | Деление | A1/A2 |
% (знак процента) | Процент | 50 % |
^ (крышка) | Возведение в степень | A1^2 |
Операторы сравнения (таблица 2) используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ.
Таблица 2. Операторы сравнения
Оператор | Значение | Пример |
= (знак равенства) | Равно | (A1=B1) |
> (знак больше) | Больше | (A1>B1) |
< (знак меньше) | Меньше | (A1<B1) |
>= (знак больше и равно) | Больше или равно | (A1>=B1) |
<= (знак меньше и равно) | Меньше или равно | (A1<=B1) |
<> (знак меньше и больше) | Не равно | (A1<>B1) |
Текстовый оператор амперсанд (&) используется для объединения нескольких текстовых значений (таблица 3).
Таблица 3. Текстовый оператор амперсанд (&)
Оператор | Значение | Пример |
& (амперсанд) | Объединение текста в одну последовательность | "Фамилия"&"Имя"&"Отчество" |
Операторы ссылок используют для описания ссылок на диапазоны ячеек (таблица 4).
Таблица 4. Операторы ссылок
Оператор | Значение | Пример |
: (двоеточие) | Ставится между ссылками на первую и последнюю ячейки диапазона | B5:B15 |
; (точка с запятой) | Оператор объединения | B5:B15;D5:D15 |
(пробел) | Оператор пересечения множеств, служит для ссылки на общие ячейки двух диапазонов | B7:D7 C6:C8 |
2 Создание функций с использованием мастера функций
Функция – это, как правило, стандартная формула, по которой выполняются вычисления в указанном порядке с заданными величинами, называемыми аргументами. Функции позволяют упростить формулы, особенно если они длинные или сложные. Функции используют не только для непосредственных вычислений, но также и для преобразования чисел, например, для округ-ления, для поиска значений, сравнения и т. д.
Структура функции: имя функции, открывающая скобка, список аргументов, разделенных точками с запятой, закрывающая скобка. Аргументом функции может быть число, текст, логическое значение, массив, значение ошибки, ссылка на ячейку. В качестве аргументов используются также константы, формулы или функции. В каждом конкретном случае необходимо использовать соответ-ствующий тип аргумента. Например, функция суммирования будет выглядеть так: =СУММ(A1:B1).
Для создания формул с функциями:
1. Выделите ячейку, в которую требуется ввести формулу.
2. В группе Библиотека функций вкладки Формулы (рисунок 1) щелкните на кнопке выбранной категории функций (Логические, Текстовые, Дата и время, Ссылки и массивы, Математические, Недавно использовались, Финансовые, Другие функции).
Рисунок 1. Вкладка Формулы, группа Библиотека функций
3. В раскрывающемся списке данной категории функций выберите нужную функцию.
4. В раскрывающемся диалоговом окне Аргументы функции (рисунок 2) в соответствующем поле (полях) введите аргументы функции.
Рисунок 2. Диалоговое окно Аргументы функции
5. В окне Аргументы функции нажмите кнопку ОК.
Ссылки на ячейки можно вводить с клавиатуры, но удобнее пользоваться выделением ячеек мышью. Для этого надо поставить курсор в соответствующее поле и на листе выделить необхо-димую ячейку или диапазон ячеек. Для удобства выделения ячеек окно Аргументы функции можно сдвинуть. Текст, числа и логические выражения в качестве аргументов функции обычно вводят с клавиатуры. В качестве подсказки в окне Аргументы функции отображается назначение функции, а в нижней части окна отображается описание аргумента, в поле которого в данный момент находится курсор. Следует иметь в виду, что некоторые функции не имеют аргументов.
В таблице 5 перечислены часто используемые табличные функции.
Таблица 5. Часто используемые табличные функции
Функция | Назначение |
СУММ (SUM) | Вычисляет сумму значений в указанных ячейках |
СРЗНАЧ (AVERAGE) | Вычисляет среднеарифметическое значение для чисел, содержащихся в указанных ячейках |
СЧЕТ (COUNT) | Определяет количество значений в указанных ячейках |
МАКС (MAX) | Находит наибольшее значение в указанных ячейках |
МИН (MIN) | Находит наименьшее значение в указанных ячейках |
Если название нужной функции неизвестно, можно попробовать найти ее с помощью Мастера функций. Для этого в группе Библиотека функции выберите команду Вставить функцию. В поле Поиск функции диалогового окна Мастер функций: шаг 1 из 2 (рисунок 3) введите назначение искомой функции и нажмите кнопку Найти. Найденные функции будут отображены в списке Выберите функцию.
Рисунок 3. Диалоговое окно Мастер функций: шаг 1 из 2. Поиск функции
2.1 Простые математические вычисления с использованием функции СУММ(А)
2.1.1 Суммирование
Синтаксис функции: СУММ(А), где A – список от 1 до 30 элементов, которые нужно просум-мировать. Элементами суммирования являются численные данные, введенные в ячейки таблицы или представленные в ячейках формулами. Ссылки на пустые ячейки, текстовые или логические значения игнорируются.
Фактически данная функция заменяет непосредственное суммирование с использованием опе-ратора сложения (+). Формула =СУММ(С2:С6), указанная в ячейке С7 (рисунок 4), тождественна формуле =С2+С3+С4+С5+С6. Однако есть и некоторые отличия. При использовании функции СУММ добавление ячеек в диапазон суммирования автоматически изменяет запись диапазона в формуле. Например, если в таблицу вставить строку, то в формуле будет указан новый диапазон суммирования. Аналогично формула будет изменяться и при уменьшении диапазона суммирования.
Рисунок 4. Формула =СУММ(С2:С6). Простое суммирование данных в ячейках
Упражнение 1
В этом упражнении Вы просуммируете цены для всех видов продуктов и услуг, необходимых для проведения праздника. Рабочие папки находятся в папке Excel_2007_Work_files.
1. Запустите программу Microsoft Excel 2007.
2. Откройте файл AutoSum, находящийся в папке PerfomingCalculations.
3. Перейдите к командной вкладке Формулы.
4. Перейдите к ячейке С8.
5. Выберите категорию Математические.
6. Щелкните по функции СУММ.
7. Проверьте диапазон, который будет выделен автоматически.
2.1.2 Выборочная сумма
Иногда необходимо суммировать не весь диапазон ячеек, а только ячейки, отвечающие опре-деленным условиям (критериям). В таком случае используют функцию СУММЕСЛИ.
Синтаксис функции: СУММЕСЛИ(А;В;С), где:
A – диапазон вычисляемых ячеек;
В – критерий в форме числа, выражения или текста, определяющего суммируемые ячейки;
С – фактические ячейки, данные которых суммируются.
В тех случаях, когда диапазон вычисляемых ячеек и диапазон фактических ячеек для сумми-рования совпадают, аргумент С можно не указывать.
В качестве примера в таблице на рисунке 5 показано суммирование только тех товаров, кото-рых было продано в количестве больше 100.
Рисунок 5. Выборочное суммирование
Упражнение 2
В этом упражнении Вы посчитаете сумму затрат на праздники, чья стоимость больше 100000 руб. Рабочие папки находятся в папке Excel_2007_Work_files.
1. Запустите программу Microsoft Office Excel 2007.
2. Откройте файл SumIf, находящийся в папке PerfomingCalculation, лист корпоративные.
3. Выделите ячейку F8.
4. Перейдите к командной вкладке Данные.
5. Выберите функцию СУММЕСЛИ из категории Математические. Диапазон ячеек F4:F7, критерий >100000.
Упражнение 3
В этом упражнении Вы суммируете доходы всех менеджеров, выручка от работы каждого составила больше 45000. Рабочие папки находятся в папке Excel_2007_Work_files.
1. Запустите программу Excel 2007.
2. Откройте файл SumIf, находящийся в папке PerfomingCalculations.
3. Перейдите к ячейке H14.
4. В наборе команд Библиотека функций выберите команду Математические, затем выберите функцию СУММЕСЛИ. Появится диалоговое окно Аргументы функции.
5. Введите значения для аргументов так, как показано на рисунке 6, и нажмите OK.
Рисунок 6. Условия упражнения
6. Сохраните книгу и закройте Excel 2007.
2.1.3 Создание формул с использованием кнопки Сумма
Для быстрого выполнения вычислений без запуска Мастера функций используют кнопку Сумма. Эта кнопка находится в группе Библиотека функций вкладки Формулы (там она называется Автосумма), а также в группе Редактирование вкладки Главная.
Для вычисления суммы чисел в ячейках, расположенных непрерывно в одном столбце или одной строке, достаточно выделить ячейку ниже или правее суммируемого диапазона и нажать кнопку Сумма. Например, для вычисления суммы значений в ячейках С4:С12 в таблице на рисунке 7 следует выделить ячейку С13 и нажать кнопку Сумма.
Рисунок 7. Суммирование с использованием кнопки Сумма
Для подтверждения ввода формулы следует нажать клавишу Enter или еще раз нажать кнопку Сумма.
Для вычисления суммы произвольно расположенных ячеек следует выделить ячейку, в кото-рой должна быть вычислена сумма, нажать на кнопку Сумма, а затем на листе выделить сумми-руемые ячейки и/или диапазоны ячеек. Для подтверждения ввода формулы следует нажать клави-шу Enter или еще раз нажать кнопку Сумма.
Кроме вычисления суммы, кнопку Сумма можно использовать для вычисления среднего зна-чения, определения количества числовых значений, нахождения максимального и минимального значений. В этом случае необходимо щелкнуть на стрелке кнопки Сумма и выбрать необходимую опцию в раскрывающемся меню:
• Среднее – вычисление среднего арифметического;
• Число – определение количества численных значений;
• Максимум – нахождение максимального значения;
• Минимум – нахождение минимального значения.
Например, для вычисления среднего значения в ячейках С4:С12 в таблице на рисунке 8 сле-дует выделить ячейку С14, затем щелкнуть на стрелке кнопки Сумма и выбрать действие Среднее.
Рисунок 8. Вычисление среднего значения с использованием кнопки Сумма
2.2 О логических функциях
Логическая функция - это функция, которая устанавливает соответствие между одним или не-сколькими высказываниями, которые являются аргументами функции, и высказыванием, которое является значением функции. Аргументами логической функции являются высказывания, логичес-кое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0). Вместо логических значений ЛОЖЬ и ИСТИНА в ячейку или в формулу можно ввести с клавиатуры соответствующие этим значениям слова.
Логические функции используют для проверки и анализа табличных данных, а также в вычис-лениях, на которые наложены определенные условия (условные вычисления).
В логических функциях в качестве аргументов используются логические выражения. В логичес-ких выражениях сравниваются числовые или текстовые значения с помощью операторов сравнения, приведенных в таблице 6.
Таблица 6. Операторы сравнения
Оператор | Значение |
= | Равно |
< | Меньше |
> | Больше |
<= | Меньше или равно |
>= | Больше или равно |
<> | Не равно |
Логическое выражение – это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 – это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА, в противном случае – ЛОЖЬ.
Одними из самых востребованных логических функций являются:
• функция ЕСЛИ - проверяет выполнение условия и присваивает одно значение функции, если логическое выражение (аргумент функции) имеет значение ИСТИНА, и другое значение, если логическое выражение имеет значение ЛОЖЬ;
• функция ИЛИ - проверяет имеет ли хотя бы один из аргументов функции (логических значе-ний) значение ИСТИНА и присваивает функции значение ИСТИНА, если хотя бы один из ее аргу-ментов имеет значение ИСТИНА, либо значение ЛОЖЬ, если все аргументы функции имеют зна-чение ЛОЖЬ;
• функция И – проверяет имеют ли все аргументы функции значение ИСТИНА и присваивает функции значение ИСТИНА, если истинны все аргументы, и значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.
Для наглядного представления результатов анализа данных с помощью логических функций рассмотрим подробней функцию ЕСЛИ.
Синтаксис функции: ЕСЛИ(А;В;С), где A – логическое выражение, правильность которого следует проверить; В – значение, если логическое выражение истинно; C – значение, если логи-ческое выражение ложно.
Рассмотрим пример. Необходимо определить, выдавать или не выдавать премию менеджерам по продажам (рисунок 9). Условие: премия выдается, если менеджер продал более 100 единиц товара.
Рисунок 9. Пример результатов работы менеджеров по продажам
Для решения этой задачи вставляем функцию ECЛИ в ячейку C2. В диалоговом окне Аргументы функции составляем логическое выражение для первого менеджера, как показано на рисунке 10. После ввода данных нажмите OK.
Рисунок 10. Диалоговое окно Аргументы функции
С помощью маркера автозаполнения продолжим ряд до ячейки С5. Формула логического выражения для ячейки В2 (см. рисунок 10) будет скопирована в ячейки, расположенные ниже с соответствующими ссылками на данные менеджеров. Таблица будет заполнена так, как показано на рисунке 11.
Рисунок 11. Пример заполнения таблицы
В таблице на рисунке 12 приведен еще один пример расчета премий менеджеров по продажам товаров с помощью логической функции ЕСЛИ. В этом примере премии зависят от суммы продан-ных товаров. Если товаров продано более чем на 10000 руб., то премия для менеджеров составит 10 % суммы проданных товаров, а если меньше этой суммы, то 5 %. Следовательно, при выполне-нии условия используется формула B:B*0,1, а при невыполнении условия – B:B*0,05. Вид функ-ции ЕСЛИ для этого примера показан в строке формул листа Excel на рисунке 12.
Рисунок 12. Расчет премий менеджеров продаж с использованием
логической функции ЕСЛИ
Упражнение 4
В этом упражнении, используя логическую функцию ЕСЛИ, Вы назначите премию двум менед-жерам. Рабочие папки находятся в папке Excel_2007_Work_files.
1. Запустите программу Excel 2007.
2. Откройте файл Logical, находящийся в папке PerfomingCalculations.
3. Перейдите к листу Продажи.
4. Перейдите к ячейке J9.
5. В наборе команд Библиотека функций выберите команду Логические, затем выберите функцию ЕСЛИ. Появится диалоговое окно Аргументы функции.
6. Введите значения для аргументов так, как показано на рисунке 13, и нажмите OK.
Рисунок 13. Аргументы функции ЕСЛИ
7. Скопируйте формулу для остальных менеджеров.
8. Сохраните книгу и закройте Excel 2007.
Упражнение 5
В этом упражнении, используя логическую функцию ИЛИ, Вы назначите премию менед-жерам, ранг которых меньше либо равен 2, либо общий доход которых составил больше 45000. Рабочие папки находятся в папке Excel_2007_Work_files.
1. Запустите программу Excel 2007.
2. Откройте документ Or, находящийся в папке PerfomingCalculations.
3. Удалите данные из диапазона ячеек J9:J13.
4. Перейдите к ячейке J9.
5. В наборе команд Библиотека функций выберите команду Логические, затем выберите функ-цию ЕСЛИ. Появится диалоговое окно Аргументы функции.
6. Введите значения для аргументов так, как показано на рисунке 14, и нажмите OK.
Рисунок 14. Заполнение аргументов функции ЕСЛИ
7. Скопируйте формулу для остальных менеджеров.
8. Сохраните книгу и закройте Excel 2007.
2.3 Поиск информации в списке данных
В MS Excel существуют широкие возможности поиска информации в диапазонах ячеек рабочей книги.
Функция ВПР ищет значение в крайнем левом столбце таблицы и сопоставляет это значение ячейке в той же строке из другого столбца таблицы. Таблица должна быть отсортирована по возрастанию.
Например, диапазон ячеек состоит из двух длинных столбцов. В первом столбце отображаются номера ISBN книг, а во втором – соответствующие названия книг. С помощью функции ВПР, можно по заданному номеру ISBN книги быстро найти ее название и ввести это название в указан-ную ячейку рабочего листа.
Синтаксис функции ВПР (рисунок 15):
ВПР(А;В;С;D),
где A – заданное значение; В – таблица (диапазон ячеек), в которой производится поиск; C – номер столбца таблицы, в котором должно быть найдено соответствующее значение; D – логическое зна-чение, которое определяет, нужно ли, чтобы функция искала точное или приближенное соответствие.
Рисунок 15. Диалоговое окно Аргументы функции для функции ВПР
Если аргумент D имеет значение ИСТИНА или отсутствует, то находится приблизительно соответствующее значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.
Чтобы воспользоваться функцией ВПР:
1) на вкладке Формулы в наборе команд Библиотека функций выберите кнопку Ссылки и массивы, затем функцию ВПР;
2) в открывшемся диалоговом окне заполните поля для ввода и нажмите OK.
На примере таблицы физических характеристик атмосферы, представленной на рисунке 16, рассмотрим, как с помощью функции ВПР можно найти вязкость атмосферы при ее плотности меньше или равной 1 и указать эту вязкость в выбранной ячейке рабочего листа. Для нашего примера функция ВПР будет выглядеть следующим образом: =ВПР(1;A2:C10;2).
Рисунок 16. Пример функции ВПР
Функция ВПР ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равно 1 (в данном случае - 0,946), выбирает соответствующее значение вязкости из столбца
В - 2,17 и вставляет в выделенную ячейку C12.
Упражнение 6
В этом упражнении Вы воспользуетесь функцией ВПР, чтобы вывести название товара по его идентификационному номеру. Рабочие папки находятся в папке Excel_2007_Work_files.
1. Запустите программу MS Excel 2007.
2. Откройте файл LoOKup, находящейся в папке DateBase.
3. Перейдите к ячейке E4.
4. Перейдите к командной вкладке Формулы.
5. В наборе команд Библиотека функций выберите команду Ссылки и массивы, затем функ-цию ВПР. (Откроется диалоговое окно Аргументы функции.)
6. Заполните аргументы так, как показано на рисунке 17, и нажмите OK. (В ячейке E4 появится слово Юбилей, которое соответствует идентификационному номеру П7.)
Рисунок 17. Аргументы функции для заполнения
7. Сохраните книгу и закройте Excel.
2.4 Функции даты и времени
Для вставки текущей автоматически обновляемой даты используется функция СЕГОДНЯ() (рисунок 18).
Рисунок 18. Вставка даты в ячейку с использованием функции СЕГОДНЯ()
Функция аргументов не имеет, но скобки в названии функции удалять нельзя. Значение в ячейке с функцией будет обновляться при открытии файла.
Функцию СЕГОДНЯ() можно использовать для вставки не только текущей, но и вообще любой обновляемой даты. Для этого после вставленной функции надо ввести со знаком плюс или минус соответствующее число дней 
.
Для вставки текущей даты и времени используют также функцию ТДАТА() (рисунок 19). Эта функция тоже не имеет аргументов, но скобки в ее написании удалять нельзя.
Рисунок 19. Вставка в ячейку текущей даты и времени
с использованием функции ТДАТА()
Значение в ячейке с функцией будет обновляться при открытии файла, а также после любых вычислений в книге или при вводе данных на любой лист. Функцией ТДАТА() удобно пользоваться, например, при подготовке и распечатке счетов.
Функция ДНЕЙ360 служит для вычисления количество дней между двумя датами на основе 360-дневного года.
Синтаксис функции: ДНЕЙ360(А;В;С), где:
A – начальная_дата;
В – конечная_дата;
C – метод.
Начальная_дата и конечная_дата – это две даты, количество дней между которыми необходи-мо вычислить. Если начальная_дата соответствует дате более поздней, чем конечная_дата, функ-ция принимает отрицательное значение.
Метод – это логическое значение, определяющее используемый в вычислениях метод: европейский (ИСТИНА) или американский (ЛОЖЬ или отсутствие значения).
Упражнение 7
1. Откройте новую книгу Excel.
2. В ячейку А1 введите =СЕГОДНЯ(). Нажмите Еnter. Отобразится текущая дата.
3. В ячейку B2 введите =ТДАТА(). Нажмите Еnter. Отобразится текущая дата и время.
Задание 1
Откройте файл SumIF из папки PerfomingCalculation, вкладку корпоративные. Рабочие папки находятся в папке Excel_2007_Work_files.
1. В ячейку E16 введите 01.01.2004.
2. В ячейку E17 с помощью функции отобразите текущую дату.
3. В ячейке E18 вычислите количество дней, прошедших с даты основания компании по настоящее время.
2.5 Отображение и редактирование формул
В содержащей формулу ячейке обычно находится не формула, а результат ее применения. Если щелкнуть на ячейке, в строке формул отобразится сама формула.
Скрытие формулы в строке формул делает невозможным редактирование ячеек, содержащих формулы.
Для того чтобы скрыть формулу:
1. Выделите диапазон ячеек, в которых нужно скрыть формулы.
2. На вкладке Главная в группе Ячейки щелкните последовательно Формат, Формат ячеек,
а затем откройте вкладку Защита (рисунок 20).
Рисунок 20. Диалоговое окно Формат ячеек вкладка Защита
3. Установите флажок Скрыть формулы.
4. Нажмите кнопку ОК.
5. На вкладке Рецензирование в группе Изменения выберите Защитить лист и введите пароль. Формулы будут скрыты.
Для отображения в ячейках ранее скрытых формул:
1. На вкладке Рецензирование в группе Изменения выберите команду Снять защиту листа и введите пароль.
2. Для отображения в выбранной ячейке формулы, а не результата формулы, выберите команду Показать формулы в группе Зависимости формул вкладки Формулы.
3 Относительные и абсолютные ссылки
По умолчанию ссылки на ячейки в формулах относительные.
Относительная ссылка – это ссылка в формуле, основанная на относительном расположении ячейки, в которой находится формула, и ячейки, на которую указывает ссылка. При этом при изменении позиции ячейки с формулой соответственно изменяется и ссылка на связанную ячейку. Так что, например, при копировании формулы вдоль столбцов или строк ссылка автоматически корректируется с учетом перемещения ячейки с формулой. Копирование формул происходит с помощью автозаполнения.
Например, при копировании формулы =B2*C2, установленной для ячейки D2 (рисунок 21) на нижерасположенные ячейки, в ячейке D3 будет формула =В3*С3, в ячейке D5 будет формула =В5*С5 и т. д.
Рисунок 21. Результат копирования формул на нижерасположенные ячейки
В некоторых случаях использование относительных ссылок недопустимо. Например, в табли-це на рисунке 22, расчет стоимости товара вычисляется по формуле D2/G2. При копировании ячейки Е2 на нижерасположенную ячейку E3 стоимость будет рассчитана по следующей формуле: D3/G3. А в нашем случае ссылка на ячейку G2 должна оставаться неизменной, так как ячейка G2 содержит курс доллара, который нам нужен для расчета стоимости каждого товара. Такая ссылка называется абсолютной.
Рисунок 22. Использование абсолютных ссылок
Абсолютная ссылка в формуле всегда указывает на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. Для использования абсолютных ссылок надо выбрать соответствующий параметр. Абсолютная ссылка указывается символом $. Например, абсолютная ссылка на ячейку $G$2 указывает на неизменность адреса ячейки G2 при копировании формулы вдоль столбца.
Установить символ абсолютной ячейки $ можно с помощью клавиши клавиатуры F4.
Абсолютными ссылками по умолчанию являются имена ячеек.
Ссылка может быть не только относительной или абсолютной, но и смешанной.
Ссылка формата A$1 является относительной по столбцу и абсолютной по строке, т. е. при копировании ячейки с формулой выше или ниже, ссылка изменяться не будет. А при копировании влево или вправо будет изменяться заголовок столбца.
Ссылка формата $A1 является относительной по строке и абсолютной по столбцу, т. е. при копировании ячейки с формулой влево или вправо, ссылка изменяться не будет. А при копирова-нии выше или ниже будет изменяться заголовок строки.
Упражнение 8
1. Откройте новый файл книги Excel.
2. Выделите диапазон ячеек А1-D1. На вкладке Главная нажмите на кнопке Объединить и поместить в центре. Введите следующий текст Адресация ячеек.
3. В ячейки С2, С3 введите числа 5 и 8, в ячейки D2,D3 введите числа 4 и 11, а в ячейку А4 введите формулу =С2*D2.
4. Выделите ячейку А4, нажмите на команду Копировать из группы Буфер обмена вкладки Главная. Перейдите в ячейку А5 и нажмите Вставить. В ячейке А5 получим следующую формулу: С3*D3. (Пример относительных ссылок.)
5. Введите в ячейку В4 формулу =С2*$D$2. При копировании формулы из ячейки В4 в ячейку В5 получим в ячейке В5 следующую формулу: =С3*$D$2. (Пример абсолютных ссылок.)
Задание 2
Рабочие файлы для задания находятся в папке Excel_2007_Work_files.
1. Откройте файл And из папки PerfomingCalculation.
2. Посчитайте стоимость каждого праздника, если стоимость – это произведение цены празд-ника на количество человек (используйте абсолютные ссылки).
4 Исправление ошибок
В Microsoft Office Excel для поиска ошибок в формулах используются определенные правила, подобные средству проверки грамматики. Они не гарантируют исправления всех ошибок в элект-ронной таблице, однако помогают во многом избежать общих ошибок в формулах. Эти правила можно независимо включать и отключать.
4.1 Настройка исправлений общих ошибок в формулах
Для осуществления проверки правильности формул необходимо использовать определенные правила. Хотя эти правила не могут полностью уберечь от ошибок на листе, они все же помогают избежать самых общих ошибок в формулах. Данные правила можно активировать или отключать. Для этого:
1. В верхнем левом углу окна программы щелкните по кнопке Office.
2. В меню типичных задач выберите пункт Параметры Excel.
3. В окне Параметры Excel на вкладке Формулы в группе Правила контроля ошибок активи-руйте следующие пункты:
• Ячейки, которые содержат формулы, вызывающие ошибки, – для пометки ячеек, в которых содержатся формулы, вычисление которых приводит к ошибке;
• Несогласованное вычисление формулы столбца в таблицах – для пометки ячеек, в которых содержатся формулы или значения, нарушающие последовательность формул в столбце или таб-лице;
• Ячейки, которые содержат годы, представленные 2 цифрами, – данный пункт позволит отмечать те ячейки, в которых при проверке формулы содержатся ячейки в текстовом формате с годом, представленным двумя цифрами;
• Числа, отформатированные как текст или с предшествующим апострофом, – данный пункт позволит отмечать те ячейки, в которых при проверке числа записаны текстом или начи-наются с апострофа;
• Формулы, не согласованные с остальными формулами в области, – данный пункт позволит отмечать те ячейки, в которых при проверке диапазона ячеек содержится формула, отличимая от остальных формул данного диапазона;
• Формулы, не охватывающие смежные ячейки, – данный пункт позволит отмечать те ячейки, в которых при проверке диапазона ячеек будут пропущены формулы;
• Незаблокированные ячейки, содержащие формулы, – данный пункт позволит отмечать незаб-локированные ячейки, которые содержат формулы;
• Формулы, которые ссылаются на пустые ячейки, – данный пункт позволит отмечать те ячейки, в которых при проверке находятся формулы, ссылающиеся на пустые ячейки. Этот пункт не всегда рекомендуется включать;
• В таблицу введены недопустимые данные – данный пункт позволит отмечать те ячейки с данными, в которых при проверке обнаружатся данные, несовместимые с типом данных этого столбца для подключенных к данным таблиц в списке SharePoint.
4. Закройте окно нажатием кнопки ОК.
Посмотреть источник ошибки в формуле можно двумя способами.
Первый способ:
1. В окне открытого листа выделите ячейку с сообщением об ошибке.
2. Раскройте меню кнопки Ошибка рядом с ячейкой, содержащей формулу. В меню отобра-зится причина ошибки.
Второй способ:
1. В окне открытого листа выделите ячейку с сообщением об ошибке и перейдите к вкладке Формулы.
2. В группе Зависимости формул раскройте меню кнопки Проверить наличие ошибок и выбе-рите пункт Источник ошибки.
4.2 Обозначения ошибок
Если формула содержит ошибку, то на рабочем листе появится специальное сообщение об ошибке. Каждая ошибка вызывается разными причинами и, соответственно, требует различных способов разрешения (таблица 7).
Таблица 7. Ошибки и способы их разрешения
Ошибка | Пояснение |
##### | Ширина столбца мала для отображения данных или дата и время являются отри-цательными числами |
#ЗНАЧ! | В формуле использован недопустимый тип аргумента. Возможно, что введен текст вместо логического значения или числа, либо вместо одного значения введен це-лый диапазон |
#ДЕЛ/0! | Результат вычисления по формуле не определен, т. к. произведена попытка деле-ния числа на нуль |
#ИМЯ? | В формуле содержится текст, который Excel не может распознать. Возможно неправильно написано имя функции, или использовано несуществующее имя, или текст не заключен в кавычки, или пропущено двоеточие при ссылке на диапазон |
#ССЫЛКА! | В формуле дана неправильная ссылка на ячейку. Может быть удалена ячейка, используемая в данной формуле |
#ЧИСЛО! | В формуле содержится некорректное числовое значение. Вероятно, в формулу некорректно введен аргумент, либо результат является слишком большим или слишком малым для Excel |
5 Отслеживание зависимостей в формулах
В Excel предусмотрены средства для отслеживания зависимостей в формулах. Зависимости используются, чтобы просматривать на табличном поле связи между ячейками с формулами и ячейками со значениями, которые были задействованы в данных формулах. Это помогает выявить ошибки при создании формул. Зависимости могут быть отображены только в пределах одной открытой книги. При создании зависимости используются понятия влияющие ячейки и зависимые ячейки.
Влияющая ячейка – это ячейка, которая ссылается на формулу в другой ячейке. Например, если в ячейке А1 находится формула =B1+C1, то ячейки B1 и С1 являются влияющими на ячейку А1.
Зависимая ячейка – это ячейка, которая содержит формулу. Например, если в ячейке А1 нахо-дится формула =B1+C1, то ячейка А1 является зависимой от ячеек B1 и C1.
Все стрелки зависимостей, отображаемые на рабочем листе, исчезнут в случае изменения формулы, на которую указывают стрелки, а также при вставке или удалении столбцов или строк, при удалении или перемещении ячеек. Для их восстановления после сделанных изменений необходимо снова использовать на этом листе команды отображения соответствующих стрелок.
Для получения возможности отслеживания зависимостей в формулах необходимо проверить настройки. Для этого:
1. В верхнем левом углу окна программы щелкните на кнопке Office.
2. В меню типичных задач выберите пункт Параметры Excel.
3. В окне Параметры Excel на вкладке Дополнительно в группе Показать параметры для следующей книги раскройте список книг и выберите нужную книгу для настройки.
4. В графе Для объектов показывать активируйте:
• Все – для отображения стрелок зависимостей;
• Скрыть объекты - выбираются для скрытия стрелок зависимостей.
5. Закройте окно кнопкой ОК.
Для отслеживания ошибки в формуле произведите следующие действия.
1. Укажите ячейку, содержащую формулу, для которой следует найти влияющие ячейки.
2. Для отображения стрелки зависимости к каждой ячейке, обеспечивающей данными активную ячейку, на вкладке Формулы в группе Зависимости формул выберите пункт Влияющие ячейки 
:
· синие стрелки показывают ячейки, не вызывающие ошибок;
· красные стрелки показывают ячейки, вызывающие ошибки;
· если на выбранную ячейку имеется ссылка из другого рабочего листа или книги, путь от выбранной ячейки к значку рабочего листа будет обозначен черной стрелкой 
. Другая книга должна быть открыта до того, как Excel будет отслеживать эти зависимости.
3. Чтобы продолжить поиск следующего уровня влияющих ячеек, снова нажмите кнопку Влияющие ячейки 
.
4. Для удаления за раз единичного уровня стрелок зависимости, начиная с влияющей ячейки, наиболее удаленной от активной ячейки, на вкладке Формулы в группе Зависимости формул щелкните стрелку рядом с командой Убрать стрелки, а затем выберите пункт Убрать стрелки к влияющим ячейкам 
. Для удаления другого уровня стрелок зависимости вновь нажмите эту кнопку.
МЕТОДИЧЕСКИЕ УКАЗАНИЯ
РАБОТА С ЭЛЕКТРОННЫМИ ТАБЛИЦАМИ MICROSOFT EXCEL 2007
СОЗДАНИЕ И ИЗМЕНЕНИЕ ФОРМУЛ
Ответственный за выпуск
Корректор
Оператор компьютерной верстки
_____________________________________________________________________________________
НАЧОУ ВПО «Современная Гуманитарная Академия»
