Учебно-методическое пособие для студентов вузов Павлодар (стр. 35 из 76)
Вычисления в Excel. Формулой называется выражение, в соответствии с которым вычисляется значение ячейки. Формулы в Excel подчиняются определенному синтаксису, в который входит знак равенства (=), вычисляемые элементы (операнды) и операторы. Операндами могут быть: константы, ссылки или диапазоны ссылок, заголовки, имена или функции. Для обозначения операций используются операторы.
Каждая формула должна начинаться со знака равенства. В адресах ячеек и диапазонов используются латинские символы!
Ошибки при составлении формул
Если в ячейке после ввода формулы появляется сообщение об ошибке то может быть, что:
- вы выполнили деление на ноль;
- вы использовали в качестве делителя пустую ячейку;
- вы сослались на пустую ячейку;
- вы удалили ту ячейку, на которую сослались;
- данные в какой-либо ячейке не числового формата;
- вы сослались на ту ячейку, в которой должен появиться результат.
В ячейке Excel отображается результат вычисления. Введенная в ячейке формула отображается в строке формул. Для копирования формулы в соседние ячейки рабочего листа можно воспользоваться функцией Автозаполнения.
Задание
1 Создайте новую рабочую книгу, назовите ее «Оргтехника»
2 На листе «Факсы» внесите следующие данные (рисунок 25).
Рисунок 25 – Вид листа Факсы
3 Столбец Цена рассчитывается по формуле: Цена=Стоимость *Коэффициент наценки. Коэффициент наценки задайте сами (1,0 – 2,0) и внесите его в ячейку C5.
4 Столбец Сумма рассчитывается по формуле Сумма = Цена*Количество
5 На втором листе (назовите его Ксероксы) создайте следующую таблицу данных (рисунок 26).
Рисунок 26 –Вид листа Ксероксы
6 Столбцы Цена и Сумма рассчитываются по тем же формулам (см. п.3, п.4). При расчете Цены, используйте коэффициент наценки из ячейки C5 листа Факсы.
7 Сохраните рабочую книгу.
Контрольные вопросы
1 Что представляет собой электронная таблица?
2 Как формируется адрес ячейки?
3 Что называется диапазоном ячеек? Как он формируется?
4 Какие типы данных встречаются в электронных таблицах?
5 Сформулируйте правило использования формул в Excel.
6 Перечислите способы редактирования содержимого ячейки?
7 Что отображается в строке формул? Что отображается в вычисляемой ячейке?
8 Как производятся основные операции с рабочими листами Excel?
9 Что такое автозаполнение?
Лабораторная работа №16
Виды адресации в MS Excel. Использование функций
Цель: привить навыки использования встроенных функций Excel для вычислений таблиц числовых данных, а также развить логику применения различных видов адресации ячеек в целях автоматизации расчетов.
Краткие теоретические сведения
Задаваемый обычным образом адрес ячейки называется относительным адресом ячейки. При операциях копирования электронная таблица автоматически меняет адреса ячеек в соответствии с направлением копирования. Иногда возникает необходимость не менять адрес ячейки. В таких случаях используется абсолютный адрес ячейки.
Абсолютная ссылка - это ссылка на ячейку, не изменяющаяся при копировании в другое место. Для обозначения абсолютных ссылок используется знак доллара $ .
Примеры:
- пусть в ячейку C1 введена формула =A1+B1. Скопируем содержимое ячейки C1 в ячейку C2. Мы увидим, что в ячейке C2 теперь содержится формула =A2+B2;
- пусть в ячейку D3 введена формула =D1+D2. Скопируем содержимое ячейки D3 в ячейку E3. Мы увидим, что в ячейке E3 теперь содержится формула =E1+E2;
- пусть в ячейку C1 введена формула =$A$1+B1. Скопируем содержимое ячейки C1 в ячейку C2. Мы увидим, что в ячейке C2 теперь содержится формула =$A$1+B2;
- пусть в ячейку D3 введена формула =D1+$D$2. Скопируем содержимое ячейки D3 в ячейку E3. Мы увидим, что в ячейке E3 теперь содержится формула =E1+$D$2.
В Excel Возможно использование так называемых смешанных ссылок, в которых только часть адреса ячейки «защищена» знаком доллара. Внесем, например, в ячейку A1 формулу =$C1+D$1. Скопируем содержимое ячейки A1 в ячейку B2 (т.е. на 1 ячейку вправо и на 1 ячейку вниз). Мы увидим, что в ячейке B2 теперь содержится формула =$C2+E$1.
Функции. Мастер Функций. Функции - это стандартные формулы, которые обеспечивают выполнение определенного набора операций над какими-либо данными. Например, для определения суммы величин в ячейках от A1 до H1 можно задать функцию =СУММ(A1:H1) вместо формулы =A1+B1+C1+…
Каждая функция состоит из следующих 3-х элементов:
- знака равенства;
- названия функции;
- аргумента или нескольких аргументов, заключенных в круглые скобки. Аргументами могут быть адреса ячеек или диапазонов, какие-либо величины, либо результаты вычисления других формул или функций. Аргументы отделяются друг от друга при точки с запятой.
Функции можно вводить при помощи Мастера функций, кнопка
Вставка функции расположена на Стандартной панели инструментов. Также Мастера функций можно вызвать с помощью команды Вставка – Функции. Далее в появившемся окне мастера функций следует отметить нужную функцию и нажать кнопку ОК. Заметим, что функции разделены по категориям, список которых расположен в левой части окна. При выборе функции ее краткое описание появляется в нижней части окна.На экране появится окно палитры формул. В этом окне размещены поля для ввода аргументов выбранной функции. Для перемещения по полям аргументов можно использовать клавишу {Tab} либо щелкать мышью по соответствующему полю. Если значение аргумента находится в ячейках рабочего листа, то можно просто провести мышью по необходимому диапазону ячеек и адреса ячеек появятся автоматически в соответствующем поле.
Автосуммирование и автовычисления. Так как одной из наиболее часто употребляющихся функций является СУММ, в Excel предусмотрен быстрый способ ее ввода – пиктограмма
Автосуммирование.Для быстрых вычислений удобно использовать строку состояния. Необходимо просто выделить диапазон ячеек, и в строке состояния в области автовычисления мы сразу увидим результат - обычно это сумма чисел в выделенном диапазоне. Щелкнув правой кнопкой мыши по области автовычисления, можно выбрать вместо суммы другую функцию, в том числе среднее значение, максимум, минимум, количество значений или количество чисел.
Задание
1 Создайте в табличном процессоре Excel новую рабочую книгу, и сохраните ее под именем «Продажа товаров»
2 В созданной книге на листе 1 (назовите его Январь) создайте следующую таблицу с данными (рисунок 27)
Рисунок 27 – Вид листа Январь
3 Столбец Цена рассчитайте по формуле Стоимость*Коэффициент (используйте абсолютные ссылки на ячейки)
4 Столбец Сумма в тенге рассчитайте по формуле: Цена*Кол-во
5 Переведите Сумму в тенге в Сумму в доллары в соответствии с курсом доллара.
6 Проведите статистический анализ продаж в январе, с помощью функций Excel, результаты занесите в оставленные для этих целей пустые ячейки столбца A.
7 Сохраните рабочую книгу.
Контрольные вопросы
1 Чем отличаются абсолютные и относительные ссылки на ячейки?
2 Что означает частичная и полная относительная ссылка?
3 Приведите пример адреса ячейки, в котором не будет меняться номер строки при копировании формулы в другую ячейку.
4 Какая клавиша позволяет сменить тип адресации в Excel?
5 Какие категории встроенных функций Excel вы знаете?
6 Опишите синтаксис ввода функции в ячейку.
Практическая работа №9
Форматирование данных в электронных таблицах. Построение диаграмм
Цель: научиться использовать возможности электронных таблиц для оформления данных в наглядном и удобном для анализа виде. Развить воображение оформления результатов вычислений в Excel
Ход работы
Изучение принципов форматирования табличных данных
1 Загрузите программу Microsoft Excel.
2 Откройте рабочую книгу, созданную на практической работе №8 и сохраните ее копию с тем же именем в папке ПР9.
3 Перейдите на лист Бюджет 2004.
4 Установите для диапазона D7:D8 процентный формат: выделите диапазон D7:D8 и нажмите пиктограмму Процентный формат 
на панели инструментов Форматирование. Для того, чтобы добавить один знак после запятой, нажмите на пиктограмму Увеличить разрядность 
.
5 Для чисел диапазона C11:I20 следует указать, что суммы показаны в тенге, для этого выберите команду Формат – Ячейки. На вкладке Число перечислены различные форматы, требуемый нам формат можно установить, выбрав пункт все форматы и установив формат ###0" тенге". Если в ячейках появятся значки ####, то измените ширину столбцов.
6 Выделите ячейку E3 и задайте для ее значения формат Денежный ($ Английский(США)) с помощью команды Формат – Ячейки, вкладка Число. Формат, заданный для ячейки E3 можно скопировать на диапазон ячеек J11:J20, для этого:
- щелкните в ячейке E3 и нажмите пиктограмму Формат по образцу 
;
- проведите мышью по требуемому диапазону J11:J20 для копирования формата $.