Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
ВЫПОЛНЕНИЕ ВЫЧИСЛЕНИЙ В СРЕДЕ EXCEL.
Выполнение вычислений является одним из главных назначений электронных таблиц EXCEL. Для этого они предоставляют пользователю хороший инструментарий и имеет широкие функциональные возможности. Целью данной работы является получение навыков работы с электронными таблицами при выполнении различных вычислений. В методическом указании не рассматриваются глубоко вопросы использования встроенных функций для финансовых расчетов и статистического анализа, а основное внимание уделено правилам использования функций вообще и выполнению оригинальных вычислений.
Ссылки на ячейки таблицы
Понятие «ссылка» является одним из основных при работе в среде электронной таблицы. Ссылка однозначно определяет ячейку или группу ячеек рабочего листа. Ссылка MS EXCEL указывает в каких ячейках находятся значения, которые нужно использовать в качестве аргументов формулы. С помощью ссылок можно использовать в формуле данные, находящиеся в разных местах рабочего листа, а также использовать значения одной и той же ячейки в нескольких формулах. Можно также ссылаться на ячейки, находящиеся на других листах рабочей книги, в другой рабочей книге, или даже на данные другого приложения. Ссылки на ячейки других рабочих книг называются внешними. Ссылки на данные в других приложениях называются удаленными. Ссылки на ячейки используют заголовки соответствующих строк и столбцов рабочего листа. Чтобы сослаться на ячейку необходимо указать в формуле ее имя. Наиболее простой способ внесения адреса (имени) ячейки - это в процессе набора формулы выделить ячейку, нужную в данный момент набора маркером мыши, в виде большого креста. И адрес ячейки будет внесен в набираемую формулу. Набор любой формулы всегда начинается со знака “=“ ! При наборе адреса ячейки следует особое внимание уделить тому, какой установлен в момент набора шрифт на вашем компьютере - русский или латинский. Имена столбцов таблицы обозначаются ЛАТИНСКИМ шрифтом. В случае занесения адреса ячейки русским шрифтом компьютер покажет на ошибку в формуле.
ВНИМАНИЕ!
При выполнении данной работы вам часто будет необходимо использовать материал предыдущих заданий . Для того, чтобы не выполнять лишней работы, результата предыдущих заданий должны быть сохранены. Последующее задание следует выполнять на другом листе.
Задание 1. Внесите любые три числа в произвольные ячейки на листе. В четвертой ячейки (тоже произвольной) должен разместиться результат равный (число1+число2)* число3.
а) При наборе формулы в командной строке введите вручную адрес ячейки.
б) Для ввода адреса ячейки при наборе формулы выделите ячейку мыши.
Для выполнения операции суммирования следует воспользоваться кнопкой "Сумма" на панели инструментов. При этом следует всегда иметь ввиду, что суммирование производится или в конце строки, или в конце столбца, или для выделенного диапазона ячеек. Если кнопка «Сумма» отсутствует на панели инструментов, то ее следует вывести, воспользовавшись позициями меню Вид Панель инструментов... Настройка... и далее выбрать категорию Формула и перенести кнопку на панель инструментов.
Задание 2.
Составьте таблицу, которая содержит следующие столбцы:
1) Наименование товара
2) Цена в рублях
3) Количество проданных единиц товара
4) Полученная выручка за каждый товар.
Для большей наглядности при создании «шапки» таблицы воспользуйтесь позициями меню Формат Ячейки Выравнивание Переносить по словам.
Задание 3.
В конце столбца " Выручка за каждый товар» произведите суммирование содержимого ячеек столбца. Для этого воспользуйтесь кнопкой «Суммирование». В строке формул появится формула, которая будет занесена в рабочую ячейку. Проверьте ее внимательно, обращая особое внимание на диапазон ячеек, значения из которых будут суммироваться. Завершите вычисление нажатием ENTER или кнопки с "галочкой" в строке формул, рис.1
Рис.1
Ссылки бывают относительными и абсолютными.
Абсолютная ссылка всегда указывает постоянное местоположение ячейки или интервала ячеек. Относительные ссылки автоматически корректируется при перемещении формул из одной ячейки в другую. Рассмотрим это на примере.
Задание 4.
Начиная с ячейки В4 вниз по столбцу занесите значение 4,6,8, а также начиная с ячейки D5 вниз по столбцу занесите значение 5,2,6. В ячейку F6 занесите формулу =B4+D5, рисунок 2. Далее маркером заполнения - за правый нижний угол ячейки F6. При появлении маленького крестика нажмите на левую клавишу мыши и не отпуская ее протяните вниз, до ячейки F8. Таким образом произойдет заполнение ячеек результатом вычисления по той же формуле, что была введена для ячейки В3, но для своих аргументов. Если после выделения ячейки с формулой воспользоваться позициями меню Сервис Зависимости Влияющие ячейки, то получим следующий результата, рисунок 3.
Рис.2
Рис.3
При копировании маркером заполнения формулы вниз ссылки на влияющие ячейки также переместились плоско - параллельно вниз, не изменяя относительного положения друг к другу. Это легко проверить, если поместить маркер мыши на интересующую ячейку, содержащую формулу. Тогда в строке формул будет отображено содержание ячейки: если она не содержит формулу - то будет выведено только значение (содержимое ячейки), если содержит - то будет показана формула.
Задание 5.
Проверьте адреса ячеек в формулах всего диапазона F6:F8. Запишите свои наблюдения в тетрадь.
Действие абсолютной ссылки существенно отличается от относительной.
Задание 6.
Начиная с ячейки B4 вниз по столбцу занесите следующие числа: 4,6,8. Начиная с ячейки D4 вниз по столбцу занесите следующие числа: 5,2,6. В ячейку E9 занесите следующую формулу =$B$4+D4, рисунок 4.
Эта формула отличается от формул, рассмотренных выше тем, что при их написании мы использовали абсолютную ссылку на ячейку B4. Для закрепления столбцов и строк в формуле используется знак доллара $. Можно по отдельности закреплять столбец или строку. Если закрепить и столбец и строку, то будет закреплена ячейка.
Рис.4
Задание 7.
Маркером заполнения внесем формулы в ячейки E10:E11. Для наглядности воспользуйтесь позициями меню Сервис Зависимости Влияющие ячейки и определите как связаны ячейки в формулах. Какой получился результат ? Он должен быть таким, рисунок 5:
Рис.5
Задание 8.
Просмотрите содержимое ячеек от F9 до F11 в строке формул. Сделайте вывод чем отличается абсолютная ссылка на ячейку B4 от относительных на ячейки D4:D6 и запишите его в тетрадь для отчетов по лабораторным работам.
Задание 9.
Вернитесь к таблице, созданной при выполнении задания 2. Очистите содержимое столбца "Цена в руб.".
Дополните прайс - лист торговой фирмы столбцом " Цена в у.е." после столбца "Наименование" . Для этого:
1) Выделите столбец "Цена в руб."
2) Нажмите правую клавишу мыши
3) Выберите позицию меню "Вставка"
Внесите стоимость товара а у.е.
Напишите ниже таблицы в отдельно стоящей ячейки текст "Курс $" и укажите его значение в соседней правой ячейке. Используя полученные навыки по вводу формул и использованию абсолютной ссылки с учетом цены в у.е. и текущему курсу доллара заполните столбец "Цена в руб." дополненной таблицы.
Задание 10.
Измените значение курса доллара в выделенной для этого ячейки. Посмотрите как изменится вся таблица. Сделайте вывод и запишите его тетрадь для отчетов.
Это мы рассмотрели ссылку в пределах одной таблицы или одного рабочего листа. Но часто ссылки необходимо делать из различных листов одной книги.
Задание 11.
1) Перейдите на другой лист рабочей книги
2) В любую ячейку после знака "=", занесите произвольную формулу, при этом аргументы используйте из предыдущей таблице демонстрации различных разновидностей ссылок (задание 6).
3) Для этого после набора знака «=» перейдите на лист, где выполнялось задание 6, и выделите мышью нужные ячейки
4) Посмотрите на строку формулы. Что произошло? Как был занесен адрес ячейки в строку формулы ? Какие спецсимволы при этом использовались? Запишите ваши наблюдения в тетрадь для отчетов.
5) Не переходя на новый лист допишите в строке формул необходимое выражение.
7) Нажмите ENTER.
8) Перейдите на исходный лист, с которого начинался ввод формулы.
9) Подведите итог ваших наблюдений про ссылку на ячейку на другом листе.
Ссылка на другую книгу.
При работе с несколькими книгами иногда приходится ссылаться на их листы. Чтобы сослаться на листы другой книги необходимо, чтобы она тоже была рабочей. Для этого не выходя из программы EXCEL открыть еще одну книгу (если она новая, достаточно нажать на кнопку "Создать"). Для работы с двумя и более книгами удобно воспользоваться позициями меню "Окно"-"Расположить"-"Рядом"("Окно"-"Упорядочить"-"Мозаикой" для EXCEL 5.0). В данном случае все окна документов будут у вас перед глазами. Напоминаю, что существуют два типа окон: окно программного продукта и окно документа, которое помещается в окно программного продукта. Все открытые документы можно посмотреть в пункте меню «Окно» под выделенной чертой, для перехода к нужному, укажите мышью на выбранный документ. В одно окно программного продукта может быть помещено несколько окон документов. Просмотр окон и работы с ними можно осуществлять также в случае, если они уменьшены в полразмера кнопкой , рисунок 6.
Рис.6
Задание 12.
1) Откройте еще одну книгу и создайте в ней столбец с наименованием "Книга 2".
2) Введите ячейку под названием столбца "=".
3) Перейдите в другую книгу, необходимый лист другой книги (лист, где расположены аргументы в таблице демонстрации абсолютной ,относительной и смешанной ссылок).
4) Укажите на ячейку первого аргумента.
5) Допишите формулу и завершите ее ввод.
6) Маркером заполнения в новой книге заполните вниз несколько ячеек.
7) Перемещаясь маркером по ячейкам с результатом посмотрите с помощью каких спецсимволов была указана ссылка на другую ячейку.
8) Запишите результаты своих наблюдений в тетрадь для отчетов.
Использование стандартных встроенных функций.
Кроме функций, задаваемых пользователем EXCEL содержит десять групп стандартных функций:
Следует иметь ввиду, что представленный список относится для EXCEL 5.0. У EXCEL 7.0 он будет несколько отличным.
Встроенная функция чаще всего содержит список аргументов, которые надо вводить через точку с запятой после набора в строке функций ее имени. Но существует и другой способ ввода функции с помощью мастера функций. Но не зависимо от того как вводить функцию всегда выполняется вначале одна и та же последовательность действий: для использования той или другой функции, как и при вводе оригинальной , необходимо после выбора ячейки вначале набрать “=“. Далее с строке функций нажать на кнопку "встроенные функции", рис.7.
Рис.7
Далее в появившейся мастер функций подскажет назначение данной функции и откроет окна для ввода параметров. Вначале будет открыто минимальное число окон ввода. По мере ввода аргументов число окон будет увеличиваться.
В качестве аргумента может выступать и другая функция. Если ввод функции осуществляется через строку формул (без использования мастера функции), на место аргумента нужно набить название новой функции по смыслу.
В среде мастера функций для ввода в качестве аргумента встроенной функции нужно нажать кнопку, рис.8.
Рис.8
Для перемещения в среде мастера ввода функций следует выбирать очередной “Шаг”.
Рассмотрим некоторые виды встроенных функций и составим примеры их использования.
Финансовая функция расчета амортизационных отчислений .
Амортизация - это перенос стоимости оборудования или имущества или стоимости сооружения на стоимость услуг или товарной продукции за определенный период времени.
Задание 12.
Составить таблицу из четырех строк, рис.9. В первые три строки занесите исходные данные, а в четвертую строку занесите встроенную функцию вычисления амортизационных отчислений. При этом следует иметь ввиду то, что при вводе исходных данных мастеру функций можно указать как истинные значения переменных, так и адреса ячеек где расположены соответствующие значения. Удобнее указывать адреса ячеек с исходными данными, так как при изменении их содержимого, будет оперативно вычислено новое значение функции. Измените значения исходных данных и проследите на результатами. Сделайте вывод по свойствам функции и запишите его в тетрадь для отчетов.
Рис.9
Функции для работы с датами и временем.
Задание 13. Выберите ячейку. Вызовите встроенные функции и выберите функцию “Сегодня”. Она без аргументов. Нажмите ENTER. Напишите в тетрадь полученный результат.
Если в ячейке будет находится эта функция, то каждый раз после загрузки в EXCEL этого документа у вас в данной ячейке будет указана текущая дата. С помощью этих функций можно управлять работой программы по времени.
Но для этого необходимо освоить работу с логическими функциями.
Логические функции.
Очень полезная группа функций, так как большинство встроенных функций также удобно использовать в союзе с логическими функциями.
Очень наглядное использование вложенных функций можно продемонстрировать с помощью логических функций.
Задание 14. Перейдите на новый лист. В ячейке A1 наберите какое-либо число. В любой соседней с ней поставьте следующее условие:
=ЕСЛИ(A1>10000;”хорошо”;”плохо”)
Изменяя значение числа в выбранной проследите, что будет выводиться ячейке, в которой занесена функция “=ЕСЛИ()”.
Результаты наблюдений запишите в тетрадь для отчетов.
Задание 15. Задание на использование логического “И” вложенного в условный оператор “ЕСЛИ”. Перейдите на новый лист.
В ячейке A1 и B1 занесите какие-либо числа. В ячейку C1 занесите условный оператор “ЕСЛИ” следующего вида:
=ЕСЛИ(И(A1>10000;B1<10000);(A1-B1);(A1+B1))
Изменяя значения чисел в ваших двух ячейка проанализируйте получаемый результата и запишите свои выводы в тетрадь для отчета.
Задание 16. Составьте новое условное выражение но уже с использованием логического «ИЛИ». Структура написания выражения будет аналогичной рассмотренной в предыдущем задании, но только вместо логического «И» необходимо поместить логическое «ИЛИ».
Если вам нужно проанализировать содержимое ячейки принять в дальнейшем решение, то лучше всего для этого подойдут функции проверки свойств и значений.
Задание 17. Выберите вашу ячейку. В соседнюю ячейку введите следующую формулу:
=ЕСЛИ(ЕТЕКСТ(адрес вашей ячейки);”написано”;”не написано”)
Функция ЕТЕКСТ анализирует наличие какого-либо текста в ячейке. Если текст имеется, то возвращает “ИСТИНО”, если отсутствует - “ЛОЖНО”. Попробуйте в указанную в формуле ячейку вводить и удалять различную информацию. Проанализируйте результат и выводы запишите в тетрадь.
EXCEL позволяет не только определять содержимое ячеек, но и позволяет ими эффективно оперировать. В группе тестовые функции существует функция “СЦЕПИТЬ”.
Задание 18. Введите в различные ячейки листа любую текстовую информацию. В выбранную ячейку введите следующую формулу:
=СЦЕПИТЬ(ячейка1;" ";ячейка2;" ";ячейка3;” привет ВСЕМ “; “ МЫ все соединили”)
Группа символов " " используется в формуле для внесения разделительных пробелов между словами. Разделительные пробелы можно внести и при составлении самих соединяемых частей строки. В показанном примере это относится к части строки " МЫ соединили", в которой после двойных кавычек и перед МЫ должен стоять пробел.
Изменяя содержимое указанных в формуле ячеек проследите за результатом. Сделайте вывод и запишите его в отчет.
Кроме использования встроенных формул и формул пользователя EXCEL позволяет производит более сложные операция с числами. Наиболее часто используемых возможностей является экстраполяция ряда данных и получения на этой основе краткосрочного прогноза на будущее. Чаще всего используется линейная экстраполяция данных.
Задание 19. Создайте приведенную ниже таблицу, рис.10, содержащую данные об объеме оказываемых услуг абстрактной фирмы по кварталам за 1995-1996 годы.
|
Факт. |
|
|
1кв.95 |
1300 |
|
2кв.95 |
1340 |
|
3кв.95 |
1345 |
|
4кв.95 |
1400 |
|
1кв.96 |
1480 |
|
2кв.96 |
1501 |
|
3кв.96 |
1540 |
|
4кв.96 |
1560 |
Рис.10
Для приведенной таблицы необходимо спрогнозировать динамику роста объема на 1997 год. Для этого выделите четыре последних ячейки и используя маркер заполнения (маленький крестик в нижнем правом углу выделенного фрагмента) заполните ниже ячейки для 1997 года. В результате новые ячейки автоматически заполнятся значениями линейной экстраполяции для данного набора начальных данных.
Следует иметь ввиду, что если внести новые значения в начальные данные, то результат экстраполяции не изменится, так как в последующие ячейки заносятся не формулы, а значения.
Итерационное вычисление
В EXCEL итерационные процедуры нужны для пошаговых вычислений. Итерационное вычисление основано на повторении последовательности операций, при котором на каждом последующем шаге вычисления используется результат предыдущего шага.
Для того чтобы итерационный процесс был возможен, одна или несколько ячеек должны содержать циклические ссылки. В стандартном режиме циклическая ссылка воспринимается программой как ошибка, потому что вычисление с никогда не может быть закончено. Для ограничения числа шагов (циклических ссылок) можно воспользоваться встроенными средствами EXCEL. После этого можно использовать циклические ссылки для вычисления методом последовательного приближения (итерации). Разрешение на итерацию можно ввести с помощью меню Сервис->Параметры->Вычисления и поставить флажок в ячейке Итерация. После того как флажок установлен, включается ограничение на количество циклов вычислений на листе. Во-первых, программа прерывает процесс вычисления, обнаружив его “зависание”, когда относительное изменение величины не превышает некоторого порогового значения . Оно проставляется в окне Максимальное изменение. По умолчанию оно равно 0,001. После того как закончен ввод данных, программа автоматически производит первое вычисление всех значений на листе. Повторить вычисление можно, нажав клавишу F9.
Рассмотрим примеры итерационных процессов.
Задание 20. Разрешите циклическую ссылку. Необходимо решить следующую задачу: вычислить полагающийся торговому агенту бонус, составляющий 12% от чистой прибыли. Сумма общей прибыли составляет 1,5 млн. рублей, чистая прибыль равна величине общей прибыли минус выплачиваемый бонус.
Для этого составьте следующую таблицу, рис.11:
Рис.11
Измените величину общей прибыли и посмотрите, как изменится бонус.
Задание 21. Составьте свой пример на использования итерации. Результата покажите преподавателю и оформите его в тетради.
Подбор параметра.
Программа EXCEL обладает замечательным свойством - подбирать параметр какой-то зависимости, функции для получения заведомо желаемого (заданного) результата. Это может быть использовано, например, для определения процента погашения за ссуду при известном сроке погашении. И много еще других случаев в жизни, когда необходимо "подогнать" какой-либо параметры при остальных фиксированных.
Для использования этого режима необходимо в меню Сервис выбрать позицию Подбор параметра. В появившемся окне диалога необходимо ввести три параметра:
1) Указать адрес ячейки, в которой должно находится целевое значение функции, которое вы хотите достичь.
2) Указать в выбранной ячейке числовое значение целевой функции.
3) Указать адрес ячейки, содержимое которой позволено изменять для получения заданного значения целевой функции.
При указании изменяемой ячейки следует учитывать два нюанса: во-первых, данная ячейка должна содержать числовое значение, а не формулу, во-вторых, итоговая ячейка должна прямо или косвенно ссылаться на изменяемую. В противном случае, несмотря на активные усилия, программа так и не сможет определить нужного исходного значения.
Задание 22. Подобрать радиус круга, площадь которого равна 5000 квадратных метров.
Задание 23. Придумайте свой пример на подбор параметра. Реализуйте его на компьютере и опишите его в тетради.
Поиск решения
В состав EXCEL входит программа Поиск решения, которая использует алгоритм поиска оптимального решения задачи. После выбора этой программы в меню Сервис, появится диалоговое окно, где надо будет ответить на все поставленные вопросы. Однако поскольку программа Поиск решения является дополнительной, то есть при инсталляции пакета MS Office она могла быть не установлена, перед ее активизацией обратитесь к команде Настройки меню Сервис. На экране появится одноименное диалоговое окно, в котором можно проверить, доступна ли эта программа.
ВНИМАНИЕ! Если рядом с именем программы Поиск решения в списке надстроек установлен индикатор, однако соответствующая команда в меню Сервис отсутствует, отключите контрольный индикатор, закройте диалоговое окно Настройки нажатием ОК и, повторно активизировав указанное диалоговое окно на экране, вновь установите опцию Поиск решения.
Задание 24. Найти значения сторон прямоугольника, который имел бы максимальную площадь при заданном периметре равном 400 см.
Задание 25. Составьте и решите задачу на поиск решения на тематику по вашей специальности. Результаты запишите в тетрадь.
Контрольные вопросы к лабораторной работе
Чем отличаются абсолютная ссылка от относительной.
С какого знака начинается ввод формулы в ячейку.
Чем завершается ввод формулы.
Как можно посмотреть влияющие ячейки.
Может ли ячейка иметь свой отдельный формат, не схожий с другими ячейками листа.
Как использовать встроенную функцию EXCEL.
Как выполнить линейную экстраполяцию по группе значений.
Когда используется программа «Подбор параметра». Приведите три реальные задачи, где используется подбор параметра.
Когда используется программа «Поиск решения». Приведите три реальные задачи, в которых реализуется алгоритм поиска решения.
Выполнение вычислений в среде EXCEL
Методические указания
13
PAGE 4