 |
|
ПО ВЫПОЛНЕНИЮ ЛАБОРАТОРНЫХ РАБОТ
Лабораторные работы по курсу «Информационные системы в экономике» состоят из двух частей и содержат восемь лабораторных работ (четыре в первой части, четыре во второй).
В качестве программной среды используются средства MS Excel. Техническим обеспечением являются персональные компьютеры современной конфигурации.
Этапы выполнения лабораторных работ:
1. Изучение теоретических положений.
2. Выполнение работы.
На основе усвоенных теоретических положений студент самостоятельно на компьютере выполняет готовый пример лабораторной работы. Выполненная работа проверяется преподавателем, после чего студент получает индивидуальное задание и приступает к его выполнению. Полученный результат проверяется преподавателем.
3. Сохранение результатов выполненной работы.
Результат выполненной работы необходимо сохранить на жестком диске в папке «Мои документы/ИС в экономике» под именем «ФИО _ Работа № _ Вариант № _» либо на съемном носителе информации.
4. Составление отчета о проделанной работе.
Отчет о проделанной работе оформляется на отдельных листах формата А4 с обязательным указанием номера группы, ФИО исполнителя, номера и названия лабораторной работы. Отчет должен содержать: цель работы, основной теоретический материал, условия задачи, ход ее решения. В отчет необходимо включить все полученные таблицы и графики.
5. Представление результатов выполнения работы (отчета и файла на съемном носителе) для проверки.
6. Защита выполненной работы.
Лабораторная работа № 1
РАБОТА С ФОРМУЛАМИ И ФУНКЦИЯМИ
Работа с формулами
Формула – это некое выражение, которое выполняет вычисление между операндами с помощью операторов.
Операнд – это элемент вычисления (константы, функции и ссылки).
Константа – постоянное (не вычисляемое ) значение.
Функция – заранее созданная формула, выполняющая сложные вычисления по введенным значениям (аргументам) в строго определенном порядке. Функции бывают математическими, финансовыми, статистическими и т. д.
Операторы – знак или символ, определяющий тип вычисления в формуле над операндами (математические, текстовые, операторы сравнения и операторы ссылок).
Ввод формулы всегда начинается со знака равенства (=), затем следуют операнды и операторы. Например:
= 25*3, либо = А1 + В2 * (С10 - D8) / СУММ(L12 / Н4),
где 25, 3 – константы; СУММ – функция;
А1, В2, С10, D8, L12 и Н4 – ссылки; +, -, /, * - операторы.
Формулу можно вводить непосредственно в ячейку либо в окно ввода на строке формул. После вычисления в ячейке отображается полученный результат, а на строке формул в окне ввода – созданная формула.
Существуют следующие типы операторов:
Математические операторы – это операторы простых действий, а именно сложения, вычитания, умножения и т. д. Вводятся с помощью клавиатуры:
+ (плюс) – сложение;
– (минус) – вычитание или отрицание;
* (звездочка) – умножение;
/ (косая черта) – деление;
% (знак процента) – процент;
^ (знак крышки) – возведение в степень.
Операторы сравнения – это операторы сравнения разных значений, результатом которого является логическое выражение «ИСТИНА» или «ЛОЖЬ»:
= (знак равенства) – равно;
> (знак «больше») – больше;
< (знак «меньше») – меньше;
>= (знак «больше или равно») – больше или равно;
<= (знак «меньше или равно») – меньше или равно;
<> (знак «не равно») – не равно.
Текстовый оператор амперсанд (&) – это оператор объединения нескольких текстовых отрывков в одну строку. & (амперсанд) – объединение двух последовательностей знаков в одну последовательность.
Оператор ссылок – это оператор ссылки на диапазон ячеек.
- двоеточие (:) – используется между ссылками на первую и последнюю ячейки диапазона. Такое сочетание представляет собой ссылку на диапазон, например, A10:C20;
- точка с запятой (;) – оператор объединения нескольких ссылок в одну, например, СУММ(С10:В15; Е45:I30);
- знак пробела – оператор пересечения множеств, который используется для ссылки на общие ячейки двух диапазонов. Например, В10:D20 C15:C25.
Ссылки в формулах
При создании связей между ячейками используются три типа ссылок – относительные, абсолютные и смешанные.
Относительная ссылка – это ссылка в формуле, основанная на относительном расположении ячейки, в которой находится формула, и ячейки, на которую указывает ссылка. При этом при изменении позиции ячейки с формулой соответственно изменяется и ссылка на связанную ячейку. Так, при копировании формулы вдоль столбцов или строк ссылка автоматически корректируется с учетом перемещения ячейки с формулой.
Абсолютная ссылка – это неизменная ссылка в формуле на ячейку, расположенную в определенном месте. При перемещении ячейки с формулой адрес ячейки с абсолютной ссылкой не корректируется. Абсолютная ссылка указывается символом $. Например, абсолютная ссылка на ячейку $A$1 указывает на неизменность адреса ячейки А1 при копировании формулы вдоль столбца или строки.
Смешанная ссылка – это ссылка с использованием либо абсолютной ссылки на столбец и относительной – на строку ($A1), либо абсолютной ссылки на строку и относительной – на столбец (A$1).
При этом при изменении позиции ячейки с формулой относительная ссылка строки или столбца изменяется, а абсолютная часть ссылки остается прежней.
Трехмерные ссылки – это ссылки на одну и ту же ячейку или диапазон ячеек, расположенные на нескольких листах одной книги. При этом трехмерная ссылка включает в себя имя листа. Например, трехмерная ссылка Лист1:Лист5!А1 указывает на все ячейки А1, расположенные с Листа1 по Лист5. При добавлении или удалении листов, попадающих в диапазон листов трехмерной ссылки, автоматически происходит учет всех изменений. То есть новые данные, расположенные на ячейках вставляемых или удаляемых листов, прибавляются или вычитаются.
Формулы
Формулу можно создать с использованием констант, ссылок, функций или имен.
Под простой формулой здесь понимается формула, содержащая числовые константы и операторы.
1. В окне открытого листа выделите ячейку, где будет находиться формула;
2. Введите символ «=» c клавиатуры;
3. Наберите нужное число, а затем знак действия ( -, /, +, *);
4. Затем введите следующее число. Например: =198*6;
5. Нажмите клавишу Enter для перехода вниз по столбцу.
Рис. 1.1. Ввод простой формулы.
При создании формулы действуют стандартные правила математических вычислений.
Следующий вид формул основан на вычислениях с использованием ссылок на ячейки.
1. В окне открытого листа выделите ячейку, где будет находиться формула;
2. Введите символ «=» c клавиатуры;
3. Затем введите адрес ячейки, содержащий нужные значения;
4. Вставьте в формулу оператор и введите адрес следующей ячейки;
Примечание. Повторяйте ввод операндов и операторов столько раз, сколько требуется согласно создаваемой формуле.
6. Завершите создание формулы так же, как и в предыдущей инструкции.
Рис. 1.2. Ввод формулы с использованием ссылок.
Абсолютная ссылка – это неизменная ссылка в формуле на ячейку, расположенную в определенном месте.
1. В окне открытого листа выделите ячейку, где будет находиться формула;
2. Введите символ «=»;
3. Создайте нужную формулу с использованием ссылок на ячейки;
4. Не закрепляя созданную формулу, щелкните курсором ввода текста в адресном окошке перед адресом той ячейки, которую необходимо сделать абсолютной ссылкой;
5. Нажмите на клавиатуре клавишу F4;
Примечание. В адрес ячейки перед именем столбца и номером строки добавится символ $.
6. Нажмите клавишу Enter.
Рис.1.3. Ввод формулы с абсолютной ссылкой.
Копирование формулы:
При простом копировании формулы в другую ячейку все ссылки, используемые в формуле, автоматически заменяются в соответствии с новым размещением формулы.
1 способ:
1. В окне открытого листа выделите ячейку с нужной формулой;
2. Используйте любой известный способ копирования (кнопка «Копировать» на вкладке «Главная», Ctrl+C и т. д.);
3. Выделите ячейку, куда необходимо вставить формулу;
4. Используйте любой известный способ вставки (кнопка «Вставить» на вкладке «Главная», Ctrl+V и т. д.);
5. Завершите копирование нажатием клавиши Enter.
2 способ:
1. В окне открытого листа выделите ячейку с нужной формулой;
2. Используйте любой известный способ копирования (кнопка «Копировать» на вкладке «Главная», Ctrl+C и т. д.);
3. Выделите ячейку, куда необходимо вставить формулу;
4. Перейдите к вкладке «Главная» и в группе «Буфер обмена» раскройте меню кнопки «Вставить»;
5. В списке команд выберите пункт «Вставить значения»;
6. Закрепите результат нажатием клавиши Esc.
Массив
Массив – это несколько наборов значений, объединенных общими вычислениями.
Формула массива – это формула, в которой производится несколько вычислений над одним или несколькими наборами значений (аргументами массива), а затем отображающая один или несколько результатов. Формула массива создается по тем же правилам, что и обычная формула, только помещается в фигурные скобки { } и закрепляется сочетанием клавиш Ctrl+Shift+Enter. При этом если ввести данные скобки вручную, то формула будет преобразована в текстовую строку и перестанет работать.
Одна формула массива может, как правило, заменить собой несколько формул, расположенных на листе, помогая выполнять сложные расчеты.
Одномерный горизонтальный массив – когда значения находятся в отдельной строке.
Одномерный вертикальный массив – когда значения находятся в отдельном столбце.
Двумерный массив – когда значения находятся в нескольких столбцах или строках.
Рассмотрим создание формулы массива с несколькими ячейками на следующем примере: имеются следующие диапазоны данных – А2:А5 и В2:В5. Необходимо умножить значения диапазона А2:А5 на значения В2:В5.
1. В окне открытого листа выделите диапазон ячеек, где будет располагаться формула массива и введите знак (=);
2. В нашем примере пусть это будут ячейки С2:С5;
3. Введите первый нужный диапазон ячеек А2:А5;
4. Введите оператор умножения (*), а затем следующий диапазон ячеек В2:В5;
5. Завершите операцию сочетанием клавиш Ctrl+Shift+Enter;
6. Созданная формула примет вид {=А2:А5*В2:В5}.
Рассмотрим создание формулы массива с одной ячейкой.
Имеются следующие диапазоны данных – А2:А6, В2:В6, D2:D6 и Е2:Е6. Необходимо умножить значения диапазона А2:А6 на значения В2:В6, затем значения диапазона D2:D6 на значения Е2:Е6.
Рис. 1.4. Формула массива с несколькими ячейками.
Полученные результаты следует сложить. Для этого необходимо:
1. В окне открытого листа выделите ячейку, где будет располагаться формула массива и введите знак (=);
2. Пусть в нашем примере это будет ячейка F2;
3. Введите название функции автосуммы СУММ;
4. Откройте круглые скобки и введите первый нужный диапазон ячеек А2:А6;
5. Введите оператор умножения (*), а затем следующий диапазон ячеек В2:В6;
6. Введите оператор перечисления (;), а затем следующий диапазон ячеек D2:D6;
7. Введите оператор умножения (*), а затем последний диапазон ячеек Е2:Е6;
8. Закройте круглые скобки и нажмите сочетание клавиш Ctrl+Shift+Enter;
9. Созданная формула преобразится и примет вид:
{=СУММ(А2:А6*В3:В6;D2:D6*E2:E6)}.
Рис. 1.5. Формула массива с одной ячейкой.
Способы изменения формулы массива:
1 способ (изменение формулы с одной ячейкой):
1. В окне открытого листа выделите ячейку с формулой массив;
2. Щелкните курсором в окошке строки формул и внесите нужные изменения;
3. Завершите операцию сочетанием клавиш Ctrl+Shift+Enter.
2 способ (изменение формулы с несколькими ячейками):
1. В окне открытого листа выделите весь диапазон ячеек с формулой массива;
2. Щелкните курсором в окошке строки формул и внесите нужные изменения;
3. Завершите операцию сочетанием клавиш Ctrl+Shift+Enter.
В формуле массива с несколькими ячейками можно увеличивать диапазон используемых ячеек:
1. В окне открытого листа выделите диапазон ячеек с формулой массива и дополнительные ячейки, в которые необходимо продолжить формулу массива;
2. Щелкните курсором в окошке строки формул и внесите нужные изменения в ссылках на ячейки;
3. Завершите операцию сочетанием клавиш Ctrl+Shift+Enter.
Константы массива
Константы массива – это компоненты формул массива, которые создаются путем ввода списка элементов внутри фигурных скобок ({ }). Скобки при этом вводятся вручную, например:
={1;2;3;4;5}.
Константы массива могут содержать числа, текст, логические значения (ИСТИНА и ЛОЖЬ), а также значения ошибок. При этом числа можно использовать в целочисленном, десятичном или экспоненциальном формате. А текст необходимо заключать в двойные кавычки (« »).
При горизонтальном массиве (строка) элементы списка разделены точкой с запятой.
При вертикальном массиве (столбец) элементы списка разделены двоеточием.
При двумерном массиве элементы строк разделены точкой с запятой запятыми, а столбцы – двоеточием.
Между собой элементы отделяются пробелом.
Горизонтальная константа
1. В окне открытого листа выделите горизонтальный ряд ячеек с числами. Например, ячейки А1_С1 со значениями 1,2,3;
2. В окошке строки формул введите знак (=) и откройте фигурную скобку;
3. Введите числа, содержащиеся в выделенном ряде ячеек, разделяя их точкой с запятой: ={1;2;3};
4. Закройте фигурные скобки и нажмите сочетание клавиш Ctrl+Shift+Enter.
Формула примет следующий вид: {={1;2;3}}.
Рис. 1.6. Горизонтальная константа.
Вертикальная константа
1. В окне открытого листа выделите вертикальный ряд ячеек с числами. Например, ячейки А2_А4 со значениями 4,5,6;
2. В окошке строки формул введите знак (=) и откройте фигурную скобку;
3. Введите числа, содержащиеся в выделенном ряде ячеек, разделяя их двоеточиями: ={4:5:6};
4. Закройте фигурные скобки и нажмите сочетание клавиш Ctrl+Shift+Enter.
Формула примет следующий вид: {={4:5:6}}.
Рис. 1.7. Вертикальная константа.
Двумерная константа
1. В окне открытого листа выделите прямоугольный диапазон ячеек с числами. Например, ячейки А1_С3 со значениями 1,2,3,4,5,6,7,8,9;
2. В окошке строки формул введите знак (=) и откройте фигурную скобку;
3. Введите числа, содержащиеся в выделенном диапазоне ячеек, разделяя горизонтальные константы точками с запятыми, а вертикальные – двоеточиями. Между собой горизонтальные и вертикальные константы отделяются пробелом:
={1;2;3: 4;5;6: 7,8,9};
4. Закройте фигурные скобки и нажмите сочетание клавиш Ctrl+Shift+Enter.
{={1;2;3: 4;5;6: 7,8,9}}.
Рис. 1.8. Двумерная константа.
Использование константы массива
Предположим, что необходимо умножить значения диапазона ячеек на ряд чисел, а затем полученные результаты сложить.
1. В окне открытого листа выделите ячейку, в которой будет находиться формула, например, В1;
2. Введите нужную формулу. В нашем случае это: =СУММА(A1:А3*{1;2;3}),
где:
- СУММА – функция автосуммирования;
- A1:А3 – диапазон ячеек с исходными значениями 10,20,30;
- * – оператор умножения;
- {1;2;3} – константа массива.
3. Используйте сочетание клавиш Ctrl+Shift+Enter.
Формула примет следующий вид: {=СУММА(A1:А3*{1;2;3})}.
В ячейке В1 отобразится значение 360.
Данная формула соответствует следующей формуле: =СУММА(A1*1,А2*2,А3*3).
Рис. 1.9. Использование константы массива.
Работа с функциями
Функции – это заранее созданные формулы, которые выполняют вычисления по введенным величинам и в указанном порядке. Функция включает следующие элементы:
- знак равенства (=);
- собственное имя, например СУММ;
- открывающая скобка, внутри которой находится список аргументов, разделенных точкой с запятой;
- аргументы, которые непосредственно зависят от выбранной функции и могут состоять из чисел, текста, логических величин, массивов, ссылок, формул и т. д.;
- закрывающая скобка.
Исполнение функции начинается с ввода имени функции, затем открывается круглая скобка, указываются аргумент или несколько аргументов, разделяющиеся точками с запятыми, а затем – закрывающая круглая скобка.
В Excel существуют математические, финансовые, статистические и другие функции. Имя функции можно вводить вручную с клавиатуры, а можно выбирать в окне «Мастер функций».
При работе с вложенными функциями появляется всплывающая подсказка аргумента, щелкнув на которую можно открыть окно справки, помогающей разобраться с синтаксисом и аргументами.
Функцию можно вставить как в начале вычисления, так и по ходу создания собственной формулы.
1 способ:
1. В окне открытого листа выделите ячейку, где будет располагаться функция;
2. Перейдите к вкладке «Формулы» и в группе «Библиотека функций» щелкните по кнопке «Вставить функцию»;
3. В окне «Мастер функций – шаг 1 из 2» в графе «Категории» раскройте список существующих типов функций и выберите нужную категорию: например, «Математические»;
4. В группе «Выберите функцию» просмотрите список функций заданной категории и выберите нужную, например, «ОКРУГЛВВЕРХ»;
5. Перейдите к следующему шагу щелчком по кнопке «ОК»;
6. В окне «Аргументы функции» введите в поля аргументов адреса ячеек для вычисления;
Примечание. Для каждой функции окно «Аргументы функции» имеет собственный вид. Например, в окне «Аргументы функции ОКРУГЛВВЕРХ» необходимо в графе «Число» ввести адрес ячейки с числом, которое необходимо округлить, а в графе «Число разрядов» задать количество разрядов после запятой, до которых необходимо округлить число.
7. Для получения развернутой информации по используемой функции нажмите кнопку «Справка по этой функции»;
8. Закройте окно кнопкой «ОК»;
9. В заданной ячейке, если аргументы введены были правильно, отобразится результат вычисления функции.
Рис. 1.10. Вставка функции.
2 способ:
1. В окне открытого листа выделите ячейку, где будет располагаться функция;
2. Используйте сочетание клавиш Shift+F3;
3. Далее действуйте, как в первом способе данной инструкции.
3 способ:
1. В окне открытого листа выделите ячейку, где будет располагаться функция;
2. На строке формул щелкните по кнопке «Вставить функцию»;
3. Далее действуйте, как в первом способе данной инструкции.