Примеры операций с функциями

ОРГАНИЗАЦИЯ И МЕТОДИЧЕСКИЕ УКАЗАНИЯ

ПО ВЫПОЛНЕНИЮ ЛАБОРАТОРНЫХ РАБОТ

Лабораторные работы по курсу «Информационные системы в экономике» состоят из двух частей и содержат восемь лабораторных работ (четыре в первой части, четыре во второй).

В качестве программной среды используются средства 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}.

Рис. 1.4 Формула массива с несколькими ячейками

Рассмотрим создание формулы массива с одной ячейкой:

Имеется следующие диапазоны данных – А2:А6, В2:В6, D2:D6 и Е2:Е6. Необходимо умножить значения диапазона А2:А6 на значения В2:В6, затем значения диапазона D2:D6 на значения Е2:Е6. Полученные результаты следует сложить. Для этого необходимо:

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}

Работа с функциями

Функции – это заранее созданные формулы, которые выполняют вычисления по введенным величинам и в указанном порядке. Функция включает следующие элементы:

- знак равенства (=);

- собственное имя, например СУММ;

- открывающая скобка, внутри которой находится список аргументов, разделенных точкой с запятой;

- аргументы, которые непосредственно зависят от выбранной функции и могут состоять из чисел, текста, логических величин, массивов, ссылок, формул и т. д.

- закрывающая скобка.

Исполнение функции начинается с ввода имени функции, затем открывается круглая скобка, указываются аргумент или несколько аргументов, разделяющиеся точками с запятыми, а затем – закрывающая круглая скобка.

В 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. Далее действуйте, как в первом способе данной инструкции.

Примеры операций с функциями

Таблица 1.1

Необходимо определить:

1. Фонд оплаты труда по предприятию; максимальный и минимальный оклад;

2. Среднюю заработную плату;

3. Число сотрудников, имеющих оклад менее определенной суммы;

4. ФОТ сотрудников, имеющих оклад менее определенной суммы;

5. Доля работников имеющих оклад менее определенной суммы, в общем ФОТ.

Введем исходные данные в Excel.

Рис. 1.11 Исходные данные

Далее введем наименование показателей, которые необходимо рассчитать в отдельные ячейки Excel, затем введем соответствующие формулы.

На рисунке 1.12 включен режим отображения формул в ячейках. Нам необходимо его выключить. Получим результат выполнения расчета необходимых показателей (см. рис. 1.13)

Рис. 1.12 Результат выполнения расчета (режим отображения формул)

Рис. 1.13Результат выполнения расчета

Задания

Вариант 1

Таблица 1.2.

Исходные данные

Необходимо с использованием соответствующих формул рассчитать следующие показатели:

- ФОТ по предприятию;

- Максимальный оклад;

- Минимальный оклад;

- Средняя заработная плата

- Число сотрудников, имеющих оклад менее 4 000 руб.;

- ФОТ сотрудников, имеющих оклад менее 4 000 руб.;

- Доля работников, имеющих оклад менее 4 000 руб.

Вариант 2

Таблица 1.3.

Исходные данные

Определить с использованием соответствующих формул следующие показатели:

- Средний балл (СБ) по каждому студенту за семестр, год;

- Лучшую по успеваемости группу;

- Определить студента с самой высокой успеваемостью;

- Определить итоговый средний балл по всем группам за каждый рейтинг, семестр, год;

- Определить число студентов имеющих средний балл ниже 20;

- Определить количество студентов, имеющих средний балл за год выше 20.

Вариант 3

Таблица 1.4.

Исходные данные

Рассчитать с использованием встроенных функций следующие показатели:

- Суммарный объем продаж;

- Товары с максимальной и минимальной стоимостью объема продаж;

- Суммарный объем продаж в количественном и денежном выражении;

- Определить товар с максимальным и минимальным объемом продаж.

Контрольные вопросы

1. Дайте определение формулы, функции, константы, оператора?

2. Какие типы операторов вы знаете?

3. Какие виды ссылок вы знаете?

4. Дайте определение массива, формулы массива?

5. Какие виды массивов вы знаете?

6. Дайте определение константы массива?

7. Какие константы вы знаете?

Лабораторная работа № 2

РАБОТА СО СВОДНЫМИ ТАБЛИЦАМИ

Отчет сводной таблицы – это интерактивный перекрестный отчет, содержащий итоговые данные из разных источников, При этом выполняется анализ данных, суммирование, исследования и представления сводных данных, для упрощения процедуры сравнений, поиска закономерностей и тенденций. Отчеты сводной таблицы содержат поля строк, столбцов, страниц и данных.

С помощью отчета сводной таблицы можно:

- организовать запрос к большим массивам данных;

- подвести промежуточный итог в таблице;

- применить статистические функции к числовым данным, просуммировать данные по категориям и подкатегориям, а также создать дополнительные вычисления и формулы;

- фильтровать, сортировать, группировать и форматировать подмножества данных.

Основные понятия отчета сводной таблицы:

Поле отчета свободной таблицы – это категория итоговых данных, получаемых из полей или столбцов исходных данных.

Поле строки – поле, которому соответствуют строка. Элементы, связанные с полем строки, обозначаются как подписи строк.

Поле столбца – поле, которому соответствуют столбцы. Элементы, связанные с полем столбца, обозначаются как подписи столбцов.

Кнопка поля – это кнопка, задающая поле в отчете сводной таблицы. Ее можно перетаскивать для изменения макета отчета, а также использовать стрелки рядом с ними для раскрытия данных.

Итоговая функция – вычисление при объединении данных в отчете сводной таблицы, в таблице консолидации или же при вычислении итогов в списках или базах данных. Например, СУММ, СЧЕТ и СРЕДНЕЕ.

Исходные данные – это диапазон данных или таблица, которые используются для создания отчета сводной таблицы. Кроме этого они могут быть взяты из внешней базы данных (например, Microsoft Access).

Вычисляемое поле – поле в отчете сводной таблицы, в котором используется формула. Данные поля могут использовать также и содержимое других полей отчета сводной таблицы.

Вычисляемый элемент – элемент в поле сводной таблицы, который использует формулу. Он может выполнять вычисления, используя содержимое других элементов этого же поля отчета сводной таблицы.

Элемент – категория поля в отчетах сводной таблицы и сводной диаграммы. Например, поле «Месяц» будет включать такие элементы, как «Январь», «Февраль» и т. п.

Запрос – средство поиска записей, отвечающих на определенный вопрос о данных, хранящихся в источнике данных.

Список – набор строк, содержащий взаимосвязанные данные.

Обновление – повторное построение отчета сводной таблицы после изменения исходных данных. При этом если в отчете использованы внешние данные, то при обновлении будет открыт запрос на получение новых или измененных данных.

12
• 3
• 4
• 5
• Далее ⇒