Работа с Excel 2007: Формулы
Начало работы
Начните со знака равенства
Для расчёта в Excel используют формулы как обучающий пример рассмотрим следующее:
Два компакт-диска, купленные в феврале, стоили 12,99 и 16,99 грн.. Их суммарная стоимость равна расходам на компакт-диски в этом месяце.
Введите формулу в ячейку C6.
Нажмите клавишу ВВОД, чтобы отобразить результат вычислений по формуле.
Формула отображается в строке формул каждый раз при выборе ячейки C6.
Эти значения можно сложить в Excel, введя в ячейку C6 простую формулу.
Формулы в Microsoft Excel всегда начинаются со знака равенства (=). Чтобы сложить числа 12,99 и 16,99, введите в ячейку C6 формулу
=12,99+16,99
Знак сложения (+) — это математический оператор, обозначающий суммирование значений.
Чтобы позднее узнать, как был получен результат, щелкните в ячейке C6 и посмотрите на формулу в строке формул, расположенной в верхней части листа.
Для составления формул, в которых присутствует не только операция сложения, используются другие математические операторы.
Для вычисления разности используйте знак «минус», для вычисления произведения — знак «звездочка» (*), для вычисления частного — знак «косая черта» (/). Обязательно начинайте формулу со знака равенства.
Примечание. В одной формуле можно использовать несколько операторов. В этом курсе рассматриваются формулы с одним оператором, но следует знать, что при наличии нескольких операторов значение формулы не вычисляется просто слева направо.
Суммирование значений по столбцу
Чтобы просуммировать все расходы за январь, не требуется вводить все значения заново. Используйте встроенную формулу, которая называется функцией.
Расходы за январь можно подсчитать, выбрав ячейку B7 и нажав кнопку Сумма в группе Правка на вкладке Главная. Таким образом будет введена функция «СУММ», складывающая значения по столбцу в интервале ячеек. Использование этой функции упрощает суммирование, если требуется сложить большое число значений, при этом нет необходимости набирать формулу.
По нажатии кнопки ВВОД в ячейке B7 будет отображен результат вычислений (95,94). Формула =CУММ(B3:B6) отображается в строке формул при каждом щелчке в ячейке B7.
Знаки «B3:B6» называются аргументом; аргументы сообщают функции «СУММ», какие значения требуется суммировать. Использование ссылки на ячейку (B3:B6) вместо самих значений в ячейках позволяет автоматически обновлять результат, если значение в ячейках изменится. Двоеточие (:) между B3 и B6 обозначает диапазон ячеек в столбце B, строки с третьей по шестую. Круглые скобки отделяют аргумент от функции.
В следующих двух уроках более подробно рассказывается о ссылках на ячейки и функции.
Совет. Кнопка Сумма также находится на вкладке Формулы. Формулы можно использовать независимо от того, на какой вкладке вы работаете. Перейти на вкладку Формулы может потребоваться при работе с более сложными формулами, использование которых объясняется в других учебных курсах.
Копирование формулы вместо создания новой
Иногда копирование формул существенно проще создания новых.
Для копирования выделите нужную ячейку, в которой расположена формула. Затем поместите указатель мыши в правом нижнем углу ячейки так, чтобы он принял форму черного крестика (+) далее зажмите левую кнопку мыши. Перетащите маркер заполнения на ячейку в которую нужно скопировать формулу. После того как кнопка мыши будет отпущена, в ячейке появится результат вычисленный по формуле. Формула отображается в строке формул в верхней части листа при каждом выделении ячейки.
Примечание. Маркер заполнения может быть использован для копирования формул только в соседние ячейки по горизонтали или вертикали.
Использование ссылок на ячейки
Cсылки на ячейки идентифицируют отдельные ячейки в листе. Они сообщают Excel, где искать значения для использования в формуле.
В программе Excel используется тип ссылок A1, то есть строки указываются буквами, а столбцы — числами. Буквы и числа называются заголовками строк и столбцов. В таблице показано использование ссылок на ячейки с помощью буквы, соответствующей столбцу, и следующего за ней числа, соответствующего строке.
Типы ссылок
Абсолютные. Абсолютные ссылки на ячейки имеют постоянное значение. Абсолютные ссылки не изменяются при копировании формулы из ячейки в ячейку. Абсолютные ссылки обозначаются знаком доллара ($), например $D$9. На рисунке показано, что при копировании со строки на строку формулы =C4*$D$9 абсолютная ссылка на ячейку $D$9 не изменяется.
Смешанные. Смешанная ссылка является абсолютной по столбцу и относительной по строке или относительной по столбцу и абсолютной по строке. Например, ссылка $A1 является абсолютной по столбцу A и относительной по строке 1. При копировании смешанной ссылки из ячейки в ячейку ее относительная ссылка меняется, а абсолютная остается неизменной.
Абсолютные ссылки используются в случаях, когда при копировании формулы требуется сохранить ссылку неизменной. По умолчанию ссылки являются относительными. Чтобы изменить тип ссылки на абсолютный, требуется ввести знак доллара ($), как показано в примере под цифрой 2.
Допустим, получено несколько купонов со скидкой в семь процентов на прокат видеокассет, фильмов и компакт-дисков. Требуется вычислить сумму, которую можно сэкономить за месяц, используя скидки. Для вычисления 7 процентов от февральских расходов можно использовать формулу.
Введите размер скидки 0,07 в свободную ячейку D9, в ячейку D4 введите формулу, начинающуюся с =C4*. Затем введите знак доллара ($) и букву D, чтобы указать абсолютную ссылку на столбец D, и $9, чтобы указать абсолютную ссылку на строку 9. Результатом вычислений по формуле будет произведение значений ячеек C4 и D9.
Затем с помощью маркера заполнения скопируйте формулу из ячейки D4 в ячейку D5. При копировании формулы относительная ссылка изменится с C4 на C5, а абсолютная ссылка на размер скидки в ячейке D9 останется неизменной ($D$9).
Упрощение формул с помощью функций
СУММ — это только одна из многих функций Excel. Эти встроенные формулы упрощают процесс ввода формул для вычислений. Используя функции, можно легко и быстро создавать формулы, написание которых представляет трудность для пользователя.
Нахождение среднего значения
Функцию «СРЗНАЧ» предназначена для вычисления среднего значения.
Excel введет формулу за вас. Щелкните по ячейке в которой вы хотите видеть результат. На вкладке Главная в группе Правка щелкните стрелку на кнопке Сумма и выберите в списке значение Среднее. В строке формул в верхней части листа отобразится формула =СРЗНАЧ(кординаты ячеек). после чего нажимаете Enter.
Формулу также можно ввести непосредственно в ячейку.
Примечание. Кнопка Сумма также находится на вкладке Формулы в группе Библиотека функций.
Непонятные знаки на листе
Иногда программа Excel не может произвести вычисления по формуле, если формула содержит ошибки. В этом случае в ячейке вместо результата будет отображено значение ошибки. Наиболее часто встречаются три значения ошибки.
#####. Столбец недостаточно широк для отображения содержимого ячейки. Увеличьте ширину столбца, уменьшите содержимое ячейки или используйте другой формат числа.
#ССЫЛКА! Ссылка на ячейку неверна. Ячейки могли быть удалены или вставлены поверх.
#ИМЯ? Опечатка в имени функции или использование имени, неизвестного программе Excel.
Использование других функций
В приложение Excel встроено множество полезных функций, например функции для работы с датой, временем и функции для обработки текста.
Чтобы увидеть все другие функции, щелкните стрелку на кнопке Сумма в группе Правка на вкладке Главная и затем щелкните пункт Другие функции в списке. Откроется диалоговое окно Мастер функций - шаг 1 из 2, в котором можно искать функции. Это диалоговое окно является еще одним способом ввода формул в приложении Excel. Можно также отобразить другие функции, щелкнув вкладку Формулы.
Оставив диалоговое окно открытым, можно выбрать категорию и затем выполнить прокрутку списка функций в этой категории. Дополнительные сведения о функции доступны по ссылке Справка по этой функции, расположенной внизу диалогового окна.
- 14266 просмотров