Прикладное программное обеспечение
ПРИМЕНЕНИЕ ФОРМУЛ ДЛЯ ВЫЧИСЛЕНИЯ ТАБЛИЧНЫХ ДАННЫХ
Рабочие книги Excel 2007 представляют собой документы, позволяющие не только хранить и упорядочивать данные, но и выполнять над ними различные операции. К числу таких операций относятся, например, вычисления итоговых (суммарных) значений данных, введенных в заданный (выделенный) диапазон ячеек c помощью математических формул.
Математическая формула– символическая запись законченного логического суждения (определения величины, уравнения, неравенства или тождества). С помощью входящих в формулу операторов и операндов производятся вычисления искомых величин. Формула и результат вычис-ления формулы константами не являются.
Операторомназывают знак или символ, задающий тип вычисления в формуле. Существуют математические, логические операторы, операторы сравнения и ссылок.
Операнд– это элемент вычисления (константы, функции и ссылки).
Константа– постоянное (не вычисляемое) значение. Может быть числом или текстом.
Ссылка– адрес ячейки или диапазона ячеек на рабочем листе Excel, в которых содержатся значения, используемые в формуле. Можно задавать ссылки на ячейки других листов текущей книги и на другие книги. Ссылки на ячейки других книг называются связями.
Чтобы применить формулу, прежде всего, нужно ввести ее в ячейку. Сначала в ячейку вводится знак равенства, который указывает, что последующие символы должны трактоваться как формульное выражение, а не как текст. Результат вычисления по формуле будет заноситься в ту же ячейку, в которую введена формула.
Для ввода формулы с клавиатуры:
1) выделите ячейку, в которую требуется ввести формулу;
2) введите = (знак равенства);
3) выделите мышью ячейку, в которой содержится операнд формулы;
4) введите знак оператора формулы;
5) выделите мышью ячейку, в которой содержится второй операнд формулы;
6) при необходимости продолжайте ввод знаков операторов и выделение ячеек;
7) подтвердите ввод формулы в ячейку: нажмите клавишу Enter или Tab или кнопкуВвод (галочка)в строке формул.
Например, сумма чисел, содержащихся в ячейках С2 и С3, вычисляется по формуле =С2+С3. Введенную формулу можно легко изменить, щелкнув на ячейке и отредактировав содержимое строки формул. Например, можно изменить предыдущую формулу так, чтобы новая формула вычисляла разность содержимого тех же ячеек =С2-С3.
Арифметические операторыслужат для выполнения арифметических операций над числами, таких, как сложение, вычитание, умножение. Арифметические операторы Excel приведены в таб-лице 1.
Таблица 1. Арифметические операторы Excel
Операторы сравнения(таблица 2) используются для сравнения двух значений. Результатом сравнения является логическое значение: либо ИСТИНА, либо ЛОЖЬ.
Таблица 2. Операторы сравнения
Текстовый операторамперсанд (&)используется для объединения нескольких текстовых значений (таблица 3).
Таблица 3. Текстовый оператор амперсанд (&)
Операторы ссылокиспользуют для описания ссылок на диапазоны ячеек (таблица 4).
Таблица 4. Операторы ссылок
2 Создание функций с использованием мастера функций
Функция – это, как правило, стандартная формула, по которой выполняются вычисления в указанном порядке с заданными величинами, называемыми аргументами. Функции позволяют упростить формулы, особенно если они длинные или сложные. Функции используют не только для непосредственных вычислений, но также и для преобразования чисел, например, для округ-ления, для поиска значений, сравнения и т. д.
Структура функции: имя функции, открывающая скобка, список аргументов, разделенных точками с запятой, закрывающая скобка. Аргументом функции может быть число, текст, логическое значение, массив, значение ошибки, ссылка на ячейку. В качестве аргументов используются также константы, формулы или функции. В каждом конкретном случае необходимо использовать соответ-ствующий тип аргумента. Например, функция суммирования будет выглядеть так:=СУММ(A1:B1).
Для создания формул с функциями:
Выделите ячейку, в которую требуется ввести формулу.
В группеБиблиотека функцийвкладкиФормулы(рисунок 1) щелкните на кнопке выбранной категории функций(Логические, Текстовые, Дата и время, Ссылки и массивы, Математические, Недавно использовались, Финансовые, Другие функции).
Рисунок 1. ВкладкаФормулы, группаБиблиотека функций
В раскрывающемся списке данной категории функций выберите нужную функцию.
В раскрывающемся диалоговом окнеАргументы функции(рисунок 2) в соответствующем поле (полях) введите аргументы функции.
Рисунок 2. Диалоговое окноАргументы функции
- В окнеАргументы функциинажмите кнопкуОК.
Ссылки на ячейки можно вводить с клавиатуры, но удобнее пользоваться выделением ячеек мышью. Для этого надо поставить курсор в соответствующее поле и на листе выделить необхо-димую ячейку или диапазон ячеек. Для удобства выделения ячеек окноАргументы функцииможно сдвинуть. Текст, числа и логические выражения в качестве аргументов функции обычно вводят с клавиатуры. В качестве подсказки в окнеАргументы функцииотображается назначение функции, а в нижней части окна отображается описание аргумента, в поле которого в данный момент находится курсор. Следует иметь в виду, что некоторые функции не имеют аргументов.
В таблице 5 перечислены часто используемые табличные функции.
Таблица 5. Часто используемые табличные функции
Если название нужной функции неизвестно, можно попробовать найти ее с помощьюМастерафункций. Для этого в группеБиблиотека функциивыберите командуВставить функцию. В полеПоиск функциидиалогового окнаМастер функций: шаг 1 из 2(рисунок 3) введите назначение искомой функции и нажмите кнопкуНайти. Найденные функции будут отображены в спискеВыберите функцию.
Рисунок 3. Диалоговое окноМастер функций: шаг 1 из 2. Поиск функции
2.1 Простые математические вычисления с использованием функцииСУММ(А)
2.1.1 Суммирование
Рисунок 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.
Запустите программу Excel 2007.
Откройте файлLogical, находящийся в папкеPerfomingCalculations.
Перейдите к листуПродажи.
Перейдите к ячейкеJ9.
В наборе командБиблиотека функцийвыберите командуЛогические, затем выберите функциюЕСЛИ. Появится диалоговое окноАргументы функции.
Введите значения для аргументов так, как показано на рисунке 13, и нажмитеOK.
Рисунок 13. Аргументы функцииЕСЛИ
Скопируйте формулу для остальных менеджеров.
Сохраните книгу и закройте Excel 2007.
Упражнение 5
В этом упражнении, используя логическую функциюИЛИ, Вы назначите премию менед-жерам, ранг которых меньше либо равен 2, либообщий доходкоторых составил больше 45000. Рабочие папки находятся в папкеExcel_2007_Work_files.
Запустите программу Excel 2007.
Откройте документOr, находящийся в папкеPerfomingCalculations.
Удалите данные из диапазона ячеекJ9:J13.
Перейдите к ячейкеJ9.
В наборе командБиблиотека функцийвыберите командуЛогические, затем выберите функ-циюЕСЛИ. Появится диалоговое окноАргументы функции.
Введите значения для аргументов так, как показано на рисунке 14, и нажмитеOK.
Рисунок 14. Заполнение аргументов функцииЕСЛИ
Скопируйте формулу для остальных менеджеров.
Сохраните книгу и закройте 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.
Запустите программу MS Excel 2007.
Откройте файлLoOKup, находящейся в папкеDateBase.
Перейдите к ячейкеE4.
Перейдите к командной вкладкеФормулы.
В наборе командБиблиотека функцийвыберите командуСсылки и массивы, затем функ-циюВПР. (Откроется диалоговое окноАргументы функции.)
Заполните аргументы так, как показано на рисунке 17, и нажмитеOK. (В ячейкеE4появится словоЮбилей, которое соответствует идентификационному номеруП7.)
Рисунок 17. Аргументы функции для заполнения
- Сохраните книгу и закройте Excel.
2.4 Функции даты и времени
Для вставки текущей автоматически обновляемой даты используется функцияСЕГОДНЯ()(рисунок 18).
Рисунок 18. Вставка даты в ячейку с использованием функцииСЕГОДНЯ()
Функция аргументов не имеет, но скобки в названии функции удалять нельзя. Значение в ячейке с функцией будет обновляться при открытии файла.
ФункциюСЕГОДНЯ()можно использовать для вставки не только текущей, но и вообще любой обновляемой даты. Для этого после вставленной функции надо ввести со знаком плюс или минус соответствующее число дней
.
Для вставки текущей даты и времени используют также функциюТДАТА()(рисунок 19). Эта функция тоже не имеет аргументов, но скобки в ее написании удалять нельзя.
Рисунок 19. Вставка в ячейку текущей даты и времени
с использованием функцииТДАТА()
Значение в ячейке с функцией будет обновляться при открытии файла, а также после любых вычислений в книге или при вводе данных на любой лист. ФункциейТДАТА()удобно пользоваться, например, при подготовке и распечатке счетов.
ФункцияДНЕЙ360служит для вычисления количество дней между двумя датами на основе 360-дневного года.
Синтаксис функции:ДНЕЙ360(А;В;С), где:
A– начальная_дата;
В– конечная_дата;
C– метод.
Начальная_дата и конечная_дата – это две даты, количество дней между которыми необходи-мо вычислить. Если начальная_дата соответствует дате более поздней, чем конечная_дата, функ-ция принимает отрицательное значение.
Метод– это логическое значение, определяющее используемый в вычислениях метод: европейский (ИСТИНА) или американский (ЛОЖЬ или отсутствие значения).
Упражнение 7
Откройте новую книгу Excel.
В ячейкуА1введите=СЕГОДНЯ(). Нажмите Еnter. Отобразится текущая дата.
В ячейкуB2введите =ТДАТА(). Нажмите Еnter. Отобразится текущая дата и время.
Задание 1
Откройте файлSumIFиз папкиPerfomingCalculation, вкладку корпоративные. Рабочие папки находятся в папкеExcel_2007_Work_files.
В ячейкуE16введите01.01.2004.
В ячейкуE17с помощью функции отобразите текущую дату.
В ячейкеE18вычислите количество дней, прошедших с даты основания компании по настоящее время.
2.5 Отображение и редактирование формул
В содержащей формулу ячейке обычно находится не формула, а результат ее применения. Если щелкнуть на ячейке, в строке формул отобразится сама формула.
Скрытие формулы в строке формул делает невозможным редактирование ячеек, содержащих формулы.
Для того чтобы скрыть формулу:
Выделите диапазон ячеек, в которых нужно скрыть формулы.
На вкладкеГлавнаяв группеЯчейкищелкните последовательноФормат,Формат ячеек,
а затем откройте вкладкуЗащита(рисунок 20).
Рисунок 20. Диалоговое окноФормат ячееквкладкаЗащита
Установите флажокСкрыть формулы.
Нажмите кнопкуОК.
На вкладкеРецензированиев группеИзменениявыберитеЗащитить листи введите пароль. Формулы будут скрыты.
Для отображения в ячейках ранее скрытых формул:
На вкладкеРецензированиев группеИзменениявыберите командуСнять защиту листаи введите пароль.
Для отображения в выбранной ячейке формулы, а не результата формулы, выберите командуПоказать формулыв группеЗависимости формулвкладкиФормулы.
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
Откройте новый файл книги Excel.
Выделите диапазон ячеекА1-D1. На вкладкеГлавнаянажмите на кнопкеОбъединить и поместить в центре. Введите следующий текстАдресация ячеек.
Выделите ячейкуА4, нажмите на командуКопироватьиз группыБуфер обменавкладкиГлавная.Перейдите в ячейкуА5и нажмитеВставить. В ячейкеА5получим следующую формулу:С3*D3.(Пример относительных ссылок.)
Введите в ячейкуВ4формулу =С2*$D$2. При копировании формулы из ячейкиВ4в ячейкуВ5получим в ячейкеВ5следующую формулу: =С3*$D$2. (Пример абсолютных ссылок.)
Задание 2
Рабочие файлы для задания находятся в папкеExcel_2007_Work_files.
Откройте файлAndиз папкиPerfomingCalculation.
Посчитайте стоимость каждого праздника, если стоимость – это произведение цены празд-ника на количество человек (используйте абсолютные ссылки).
4 Исправление ошибок
В Microsoft Office Excel для поиска ошибок в формулах используются определенные правила, подобные средству проверки грамматики. Они не гарантируют исправления всех ошибок в элект-ронной таблице, однако помогают во многом избежать общих ошибок в формулах. Эти правила можно независимо включать и отключать.
4.1 Настройка исправлений общих ошибок в формулах
Для осуществления проверки правильности формул необходимо использовать определенные правила. Хотя эти правила не могут полностью уберечь от ошибок на листе, они все же помогают избежать самых общих ошибок в формулах. Данные правила можно активировать или отключать. Для этого:
В верхнем левом углу окна программы щелкните по кнопкеOffice.
В меню типичных задач выберите пунктПараметры Excel.
В окнеПараметры Excelна вкладкеФормулыв группеПравила контроля ошибокактиви-руйте следующие пункты:
•Ячейки, которые содержат формулы, вызывающие ошибки, – для пометки ячеек, в которых содержатся формулы, вычисление которых приводит к ошибке;
•Несогласованное вычисление формулы столбца в таблицах– для пометки ячеек, в которых содержатся формулы или значения, нарушающие последовательность формул в столбце или таб-лице;
•Ячейки, которые содержат годы, представленные 2 цифрами, – данный пункт позволит отмечать те ячейки, в которых при проверке формулы содержатся ячейки в текстовом формате с годом, представленным двумя цифрами;
•Числа, отформатированные как текст или с предшествующим апострофом, – данный пункт позволит отмечать те ячейки, в которых при проверке числа записаны текстом или начи-наются с апострофа;
•Формулы, не согласованные с остальными формулами в области, – данный пункт позволит отмечать те ячейки, в которых при проверке диапазона ячеек содержится формула, отличимая от остальных формул данного диапазона;
•Формулы, не охватывающие смежные ячейки, – данный пункт позволит отмечать те ячейки, в которых при проверке диапазона ячеек будут пропущены формулы;
•Незаблокированные ячейки, содержащие формулы, – данный пункт позволит отмечать незаб-локированные ячейки, которые содержат формулы;
•Формулы, которые ссылаются на пустые ячейки, – данный пункт позволит отмечать те ячейки, в которых при проверке находятся формулы, ссылающиеся на пустые ячейки. Этот пункт не всегда рекомендуется включать;
•В таблицу введены недопустимые данные– данный пункт позволит отмечать те ячейки с данными, в которых при проверке обнаружатся данные, несовместимые с типом данных этого столбца для подключенных к данным таблиц в списке SharePoint.
- Закройте окно нажатием кнопкиОК.
Посмотреть источник ошибки в формуле можно двумя способами.
Первый способ:
В окне открытого листа выделите ячейку с сообщением об ошибке.
Раскройте меню кнопкиОшибкарядом с ячейкой, содержащей формулу. В меню отобра-зится причина ошибки.
Второй способ:
В окне открытого листа выделите ячейку с сообщением об ошибке и перейдите к вкладкеФормулы.
В группеЗависимости формулраскройте меню кнопкиПроверить наличие ошибоки выбе-рите пунктИсточник ошибки.
4.2 Обозначения ошибок
Если формула содержит ошибку, то на рабочем листе появится специальное сообщение об ошибке. Каждая ошибка вызывается разными причинами и, соответственно, требует различных способов разрешения (таблица 7).
Таблица 7. Ошибки и способы их разрешения
| Ошибка | Пояснение |
|---|---|
| ##### | Ширина столбца мала для отображения данных или дата и время являются отри-цательными числами |
| #ЗНАЧ! | В формуле использован недопустимый тип аргумента. Возможно, что введен текст вместо логического значения или числа, либо вместо одного значения введен це-лый диапазон |
| #ДЕЛ/0! | Результат вычисления по формуле не определен, т. к. произведена попытка деле-ния числа на нуль |
| #ИМЯ? | В формуле содержится текст, который Excel не может распознать. Возможно неправильно написано имя функции, или использовано несуществующее имя, или текст не заключен в кавычки, или пропущено двоеточие при ссылке на диапазон |
| #ССЫЛКА! | В формуле дана неправильная ссылка на ячейку. Может быть удалена ячейка, используемая в данной формуле |
| #ЧИСЛО! | В формуле содержится некорректное числовое значение. Вероятно, в формулу некорректно введен аргумент, либо результат является слишком большим или слишком малым для Excel |
5 Отслеживание зависимостей в формулах
В Excel предусмотрены средства для отслеживания зависимостей в формулах. Зависимости используются, чтобы просматривать на табличном поле связи между ячейками с формулами и ячейками со значениями, которые были задействованы в данных формулах. Это помогает выявить ошибки при создании формул. Зависимости могут быть отображены только в пределах одной открытой книги. При создании зависимости используются понятиявлияющие ячейкиизависимые ячейки.
Влияющая ячейка– это ячейка, которая ссылается на формулу в другой ячейке. Например, если в ячейкеА1находится формула=B1+C1, то ячейкиB1иС1являются влияющими на ячейкуА1.
Зависимая ячейка– это ячейка, которая содержит формулу. Например, если в ячейкеА1нахо-дится формула=B1+C1, то ячейкаА1является зависимой от ячеекB1иC1.
Все стрелки зависимостей, отображаемые на рабочем листе, исчезнут в случае изменения формулы, на которую указывают стрелки, а также при вставке или удалении столбцов или строк, при удалении или перемещении ячеек. Для их восстановления после сделанных изменений необходимо снова использовать на этом листе команды отображения соответствующих стрелок.
Для получения возможности отслеживания зависимостей в формулах необходимо проверить настройки. Для этого:
В верхнем левом углу окна программы щелкните на кнопкеOffice.
В меню типичных задач выберите пунктПараметры Excel.
В окнеПараметры Excelна вкладкеДополнительнов группеПоказать параметры для следующей книгираскройте список книг и выберите нужную книгу для настройки.
В графеДля объектов показыватьактивируйте:
•Все– для отображения стрелок зависимостей;
•Скрыть объекты- выбираются для скрытия стрелок зависимостей.
- Закройте окно кнопкойОК.
Для отслеживания ошибки в формуле произведите следующие действия.
Укажите ячейку, содержащую формулу, для которой следует найти влияющие ячейки.
Для отображения стрелки зависимости к каждой ячейке, обеспечивающей данными активную ячейку, на вкладкеФормулыв группеЗависимости формулвыберите пунктВлияющие ячейки
· синие стрелки показывают ячейки, не вызывающие ошибок;
· красные стрелки показывают ячейки, вызывающие ошибки;
· если на выбранную ячейку имеется ссылка из другого рабочего листа или книги, путь от выбранной ячейки к значку рабочего листа будет обозначен черной стрелкой
. Другая книга должна быть открыта до того, как Excel будет отслеживать эти зависимости.
- Чтобы продолжить поиск следующего уровня влияющих ячеек, снова нажмите кнопкуВлияющие ячейки
- Для удаления за раз единичного уровня стрелок зависимости, начиная с влияющей ячейки, наиболее удаленной от активной ячейки, на вкладкеФормулыв группеЗависимости формулщелкните стрелку рядом с командойУбрать стрелки, а затем выберите пунктУбрать стрелки к влияющим ячейкам
. Для удаления другого уровня стрелок зависимости вновь нажмите эту кнопку.