Лабораторная работа № 3. Цель: Научить студентов работать со структурами данных в MS Excel.

РАБОТА СО СТРУКТУРАМИ ДАННЫХ В MS EXCEL

Цель: Научить студентов работать со структурами данных в MS EXCEL.

ЗАДАНИЕ 1. МАССИВЫ ДАННЫХ В MS EXCEL

4.1 Понятие массива в Excel

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

На рисунке 4.1 приведены примеры различных массивов: в диапазоне B2:F2 расположен одномерный массив чисел целого типа, в диапазоне B4:B8 расположен одномерный массив текстовых данных, а в диапазоне D5:F7 Расположен двумерный массив данных типа ДАТА.

Рисунок 4.1. Примеры массивов в таблице Excel

4.2 Правила работы с массивами

Элементы массива можно обработать, если в этом возникает необходимость, с помощью одного выражения, применяемого ко всему массиву, как к одному целому объекту. Для обработки массива нужно:

- выделить место для расположения результатов обработки массива. При этом очень важно правильно спрогнозировать размеры получаемых данных.

- ввести нужное выражение, применяемое ко всем элементам массива;

- одновременно нажать клавиши CTRL+SHIFT+ENTER.

4.3 Пример обработки массива

К массивам данных, приведенным на рисунке 4.1, применим следующие действия:

Элементы числового массива возвести в квадрат и увеличить на 5. Из массива фамилий выделить только инициалы, а для массива дат определить количество дней, прошедших с каждого из указанных дат до сегодняшнего дня. Формулы, используемые для обработки элементов данных массивов приведены на рисунке 4.2.

Рисунок 4.2 Обработка элементов массива

При работе с массивами нужно помнить, что нельзя удалить или изменить часть массива, полученного вследствие нажатия клавиш CTRL+SHIFT+ENTER.

4.4 Пример выполнения самостоятельной работы

Дана система линейных уравнений

Решить систему уравнений, т.е. найти такие значения неизвестных х₁,х₂,х₃,х₄ при подстановке которых в уравнения, каждое из уравнений обращается в тождество.

4.4.1 Метод Гаусса

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

Рисунок 4.3 Математическая модель реализации метода Гаусса

Рисунок 4.4 Результат реализации метода Гаусса

4.4.2 Метод Крамера

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

Рисунок 4.5 Математическая модель реализации метода Крамера

Рисунок 4.6 Результат реализации метода Крамера

4.4.3 Метод обратной матрицы

Для реализации данного метода нужно вычислить обратную матрицу для матрицы коэффициентов системы. Затем умножить обратную матрицу и вектор свободных членов.

Рисунок 4.5 Математическая модель реализации метода обратной матрицы

Рисунок 4.6 Результат реализации метода обратной матрицы

4.5 Варианты индивидуальных заданий

В таблицу Excel ввести коэффициенты системы линейных уравнений и свободные члены системы. Решить данную систему тремя методами: Гаусса, Крамера, с помощью обратной матрицы. Для вычисления обратной матрицы использовать функцию Excel =МОБР(), для вычисления определителя матрицы - =МОПРЕД(), для умножения двух матриц - =МУМНОЖ(). При решении системы использовать приемы работы с массивами.

Вариант1 2,13 x 1+1,3 x 2 -0,11 x 3+0,07 x 4=2,15 0,12 x 1+2,03 x 2+0,09 x 3 -0,08 x 4=0,44 -0,36 x 1-0,84 x 2+0,28 x 3+0,06 x 4=-0,83 0,02 x 1+5,1 x 2+1,04 x 3-0,12 x 4=1,16     Вариант2 3,51 x 1+0,17 x 2 +3,75 x 3-0,28 x 4=0,75 4,52 x 1+2,11 x 2-0,11 x 3 -0,12 x 4=1,11 -2,11 x 1-3,17 x 2+0,12 x 3-0,15 x 4=0,21 3,17 x 1+1,81 x 2-3,17 x 3+0,22 x 4=0,05     Вариант3 0,17 x 1+0,75 x 2 -0,18 x 3+0,21 x 4=0,11 0,75 x 1+0,13 x 2 + 0,11 x 3 –1,00 x 4=2,00 -0,33 x 1+0,11 x 2+3,01 x 3-2,01 x 4=0,11 0,11 x 1+1,12 x 2+1,11 x 3-1,31 x 4=0,13     Вариант4 - x 1+0,13 x 2 –2,00 x 3-0,14 x 4=0,15 0,75 x 1+0,18 x 2 -0,21 x 3 –0,77 x 4=0,11 0,28 x 1-0,17 x 2+0,39 x 3+0,48 x 4=0,12 x 1+3,14 x 2-0,21 x 3- x 4=-0,11 Вариант5 3,01 x 1-0,14 x 2 + x 3-0,15 x 4=1,00 -0,75 x 1+1,11 x 2 +0,13 x 3 –0,75 x 4=0,13 0,17 x 1-2,11 x 2+0,71 x 3+1,71 x 4=1,00 0,21 x 1+0,21 x 2+0,35 x 3+0,33 x 4=0,17

Вариант6 1,15 x 1+0,62 x 2 –0,83 x 3-0,92 x 4=2,15 0,82 x 1-0,54 x 2 +0,43 x 3 –0,25 x 4=0,62 0,24 x 1+1,15 x 2-0,33 x 3+1,42 x 4=-0,62 0,73 x 1-0,81 x 2+1,27 x 3-0,67 x 4=0,88     Вариант7 2,2 x 1-3,17 x 2 +1,24 x 3-0,87 x 4=0,46 1,5 x 1+2,11 x 2 –0,45 x 3 +1,44 x 4=1,50 0,86 x 1-1,44 x 2+0,62 x 3+0,28 x 4=-0,12 0,48 x 1+1,25 x 2-0,63 x 3-0,97 x 4=0,35     Вариант8 0,64 x 1+0,72 x 2 –0,83 x 3+4,2 x 4=2,23 0,58 x 1-0,83 x 2 +1,43 x 3 –0,62 x 4=1,71 0,86 x 1+0,77 x 2-1,83 x 3+0,88 x 4=-0,54 1,32 x 1-0,52 x 2-0,65 x 3+1,22 x 4=0,65     Вариант9 1,42 x 1+0,32 x 2 –0,42 x 3+0,85 x 4=1,32 0,63 x 1-0,43 x 2 +1,27 x 3 –0,58 x 4= -0,44 0,84 x 1-2,23 x 2-0,52 x 3+0,47 x 4=-0,64 0,27 x 1+1,37 x 2-0,64 x 3-1,27 x 4=0,85 Вариант10 0,73 x 1+1,24 x 2 –0,38 x 3-1,43 x 4=0,58 1,07 x 1-0,77 x 2 +1,25 x 3 +0,66 x 4= -0,66 1,56 x 1+0,66 x 2+1,44 x 3-0,87 x 4=1,24 0,75 x 1+1,22 x 2-0,83 x 3-0,37 x 4=0,92

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

1) Что называется массивом в Excel?

2) Какой массив называется одномерным?

3) Какой массив называется двумерным?

4) Как производится обработка элементов массива?

5) Какой особенностью обладают массивы?

ЗАДАНИЕ 2. РАБОТА СО СПИСКОМ ДАННЫХ

5.1 Понятие списка в Excel

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

Рисунок 5.1 Список данных о сотрудниках

Строки списка называются записями, а столбцы полями. Объем списка, представленного на рисунке 5.1 равен 7 записям.

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

5.2 Создание списка

При создании списка данные можно вводить непосредственно в ячейки таблицы, как обычно, либо воспользоваться формой ввода. Для использования формы нужно установить курсов внутри списка в любом месте, затем выполнить команду ДАННЫЕ/ФОРМА. При этом на экране появится окно формы.

При “прокручивании” списка с помощью полосы скроллинга, в полях будут изменяться данные соответственно тому на какой записи установлен указатель формы, а над кнопкой ДОБАВИТЬ будет отображаться информация об объеме списка и номере текущей записи. Для ввода новой записи нужно дойти до конца списка. При этом вид окна формы будет следующим (рисунок 5.2)

Рисунок 5.2 Окно формы ввода данных списка

Рисунок 5.3 Форма ввода новой записи

В поля ввода ввести необходимые данные и щелкнуть на кнопке ДОБАВИТЬ. Введенная запись будет размещена в конце списка.

5.3 Сортировка списка

Сортировкой называется упорядочение записей списка по каким-либо полям (максимум 3 поля). По текстовым полям можно сортировать по алфавиту или против алфавита, а по числовым полям по возрастанию или убыванию.

Для сортировки записей нужно установить курсов внутри списка в любом месте и выполнить команду ДАННЫЕ/СОРТИРОВКА.

В появившемся окне (рисунок 5.4) нужно указать последовательно поля для сортировки, выбрав в раскрывающемся имя нужного поя.

Рисунок 5.4 Окно сортировки списка

Если сортировка производится по одному полю, то нужно ответить только на первый запрос.

При сортировке по нескольким полям результат будет сформирован по следующему принципу:

- вначале сортировка производится по первому полю;

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

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

Рисунок 5.5 иллюстрирует результат сортировки по полям ОБРАЗОВАНИЕ-ВОЗРАСТ-ФАМИЛИЯ

Рисунок 5.5 Результат сортировки списка

В приведенном, в качестве примера, списке, после сортировки, записи расположились следующим образом: 4,5,1,7,2,3,6

5.4 Фильтрация списка

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

Фильтрацию можно производить одним из двух способов: автофильтр, расширенный фильтр.

5.4.1 Автофильтр

Для применения автофильтра нужно установить курсор внутри списка в любом месте и выполнить команду ДАННЫЕ/ФИЛЬТР/АВТОФИЛЬТР. В результате в таблице, рядом с именем каждого поля появятся кнопки автофильтра (рисунок 5.6).

Рисунок 5.6 Кнопки автофильтра

Щелчок на какой-либо кнопке автофильтра вызовет список возможных условий отбора (рисунок 5.7).

Рисунок 5.7 Меню автофильтра

При выборе варианта УСЛОВИЕ… на экране появится окно, позволяющее сформировать критерии отбора (рисунок5.8).

Рисунок 5.8 Окно автофильтра

В данном диалоговом окне с помощью раскрывающегося списка в левом ряду нужно выбрать операцию сравнения, а в раскрывающемся списке в правом ряду ввести значение с которым нужно сравнить значение текущего поля(можно выбрать из списка один из возможных вариантов значения). Условие отбора может быть простым или сложным. Если условие скомбинировано из двух простых условий, то их нужно связать между собой логической функцией.

Например, если из списка нужно выбрать записи, в которых фамилия сотрудника начинается с буквы К или Б, то окно условия автофильтра будет выглядеть следующим образом (рисунок 5.9).

Рисунок 5.9 Пример условия, сформированного в окне автофильтра

Результат фильтрации представлен на рисунке 5.10

Рисунок 5.10 Результат применения автофильтра

5.4.2 Расширенный фильтр

Условие отбора для расширенного фильтра формируется на рабочем листе книги Excel. Условие может располагаться на том же листе, где находится список, а также на другом листе и даже в другой рабочей книге.

При формировании условия для расширенного фильтра нужно помнить, что условия, записанные в ячейках одной строки таблицы Excel, будут связаны логической функцией И, а условия, располагающиеся в ячейках разных строк таблицы, будут связаны логической функцией ИЛИ.

Например условие на рисунке 5.11 позволяет отобрать записи о тех сотрудниках учреждения, фамилии которых начинаются на букву К или Б и стаж которых от 15 по 20 лет.

Рисунок 5.11 - Пример условия для расширенного фильтра

После формирования условия выполняется команда ДАННЫЕ/ФИЛЬТР/ РАСШИРЕННЫЙ ФИЛЬТР. В результате на экране появится диалоговое окно расширенного фильтра (рисунок 5.12).

Рисунок 5.12 - Окно диалога расширенного фильтра

На качестве ответа на запрос ИСХОДНЫЙ ДИАПАЗОН нужно ввести ссылку на диапазон ячеек таблицы, в котором располагается список данных., а на запрос ДИАПАЗОН УСЛОВИЙ ввести ссылку на диапазон ячеек, в котором предварительно было расположено условие отбора.

Рисунок 5.13 иллюстрирует результат применения представленного выше условия к списку.

Рисунок 5.13 Результат применения расширенного фильтра

5.5 Вычисление итогов в списке

MS Excel позволяет в числовых полях списка вычислять итоги с помощью математических функций. Перед подведением итогов, если это необходимо, нужно отсортировать список по тому полю, в котором предполагается вычислить итоги. Затем установить курсор внутри списка в любом месте и выполнить команду ДАННЫЕ/ИТОГИ. В итоге на экране появится диалоговое окно ИТОГИ (рисунок 5.14).

В данном окне на запрос ПРИ КАЖДОМ ИЗМЕНЕНИИ В: нужно из раскрывающегося списка выбрать имя того поля, по которому производится группировка записей для итогов.

Запрос ОПЕРАЦИЯ: предоставляет список функций с помощью которых вычисляются итоги.

Запрос ДОБАВИТЬ ИТОГИ ПО: предоставляет список имен полей списка, в которых предполагается вычислить итоги.

На рисунке 5.14 проиллюстрировано окно ПРОМЕЖУТОЧНЫЕ ИТОГИ, где указаны параметры необходимые для вычисления среднего возраста сотрудников, работающих в одном и том же отделе. Перед вычислением этих итогов список был предварительно отсортирован по полю ОТДЕЛ.

Рисунок 5.14 Окно ПРОМЕЖУТОЧНЫЕ ИТОГИ

На рисунке 5.15 представлен результат вычисления итогов в списке данных о сотрудниках.

Рисунок 5.15 Итоги по полю ВОЗРАСТ

5.6 Варианты индивидуальных заданий

Вариант 1

-Создать список данных о книгах: ФАМИЛИЯ АВТОР, НАЗВАНИЕ, МЕСТО ИЗДАНИЯ, ИЗДАТЕЛЬСТВО, ГОД ИЗДАНИЯ, СТОИМОСТЬ, КОЛИЧЕСТВО ЭКЗЕМПЛЯРОВ КНИГ. Количество записей не менее 10. Часть записей ввести обычным образом, часть с помощью формы;

-Отсортировать список по полям ИЗДАТЕЛЬСТВО-ГОД ИЗДАНИЯ-НАЗВАНИЕ. Результат сортировки скопировать на другой лист и прокомментировать;

-Отфильтровать (применить и автофильтр и расширенный фильтр) список по условию: ФАМИЛИЯ АВТОРА начинается на букву К или А и ГОД ИЗДАНИЯ с 1989 по 2002. Результат фильтрации скопировать на другой лист и прокомментировать;

-Вычислить итоги по полям СТОИМОСТЬ (среднее значение) и КОЛИЧЕСТВО ЭКЗЕМПЛЯРОВ (сумма) для записей, сгруппированных по полю ИЗДАТЕЛЬСТВО. Итоги скопировать на другой лист и прокомментировать;

Вариант 2

-Создать список данных о товарах: НАИМЕНОВАНИЕ ТОВАРА, ФИРМА-

ИЗГОТОВИТЕЛЬ, ЕДИНИЦА ИЗМЕРЕНИЯ, КОЛИЧЕСТВО, СТОИМОСТЬ ОДНОЙ ЕДИНИЦЫ ТОВАРА, ДАТА ИЗГОТОВЛЕНИЯ, СРОК ГОДНОСТИ. Количество записей не менее 10. Часть записей ввести обычным образом, часть с помощью формы;

-Отсортировать список по полям ФИРМА-ИЗГОТОВИТЕЛЬ, ЕДИНИЦА ИЗМЕРЕНИЯ, СТОИМОСТЬ. Результат сортировки скопировать на другой лист и прокомментировать;

-Отфильтровать (применить и автофильтр и расширенный фильтр) список по условию: НАИМЕНОВАНИЕ ТОВАРА начинается на букву Т или С, ЕДИНИЦА ИЗМЕРЕНИЯ КГ. или ЛИТР и СТОИМОСТЬ от 500 тг. До 2700 тг. Результат фильтрации скопировать на другой лист и прокомментировать;

-Вычислить итоги по полям СТОИМОСТЬ (среднее значение) и КОЛИЧЕСТВО (сумма) для записей, сгруппированных по полю ЕДИНИЦА ИЗМЕРЕНИЯ. Итоги скопировать на другой лист и прокомментировать;

Вариант 3

-Создать список данных об автомобилях: МАРКА, НОМЕР, ЦВЕТ, ГОД ВЫПУСКА, ПРОБЕГ, ФАМИЛИЯ ВЛАДЕЛЬЦА. Количество записей не менее 10. Часть записей ввести обычным образом, часть с помощью формы;

-Отсортировать список по полям МАРКА, ГОД ВЫПУСКА, ФАМИЛИЯ ВЛАДЕЛЬЦА. Результат сортировки скопировать на другой лист и прокомментировать;

-Отфильтровать (применить и автофильтр и расширенный фильтр) список по условию: ФАМИЛИЯ ВЛАДЕЛЬЦА начинается на букву Б или О и ПРОБЕГ от 500 000 км. по 750 000 км. Результат фильтрации скопировать на другой лист и прокомментировать;

-Вычислить итоги по полям ГОД ВЫПУСКА (максимальное значение) и ПРОБЕГ (сумма) для записей, сгруппированных по полю МАРКА. Итоги скопировать на другой лист и прокомментировать;

Вариант 4

-Создать список данных о пациентах стационара: ФАМИЛИЯ, ДИАГНОЗ, ФАМИЛИЯ ЛЕЧАЩЕГО ВРАЧА, ОТДЕЛЕНИЕ, ВОЗРАСТ, СРОК ЛЕЧЕНИЯ. Количество записей не менее 10. Часть записей ввести обычным образом, часть с помощью формы;

-Отсортировать список по полям ДИАГНОЗ, ВОЗРАСТ, ФАМИЛИЯ ПАЦИЕНТА. Результат сортировки скопировать на другой лист и прокомментировать;

-Отфильтровать (применить и автофильтр и расширенный фильтр) список по условию: ДИАГНОЗ начинается на букву Б или К и СРОК ЛЕЧЕНИЯ от 30 до 55 лет. Результат фильтрации скопировать на другой лист и прокомментировать;

-Вычислить итоги по полям СРОК ЛЕЧЕНИЯ (среднее значение) и ВОЗРАСТ (максимум) для записей, сгруппированных по полю ОТДЕЛЕНИЕ. Итоги скопировать на другой лист и прокомментировать;

Вариант 5

-Создать список данных о частных предприятиях города: НАЗВАНИЕ,ФАМИЛИЯ ДИРЕКТОРА, ТИП ПРЕДПРИЯТИЯ, ГОД ОСНОВАНИЯ, ОТРАСЛЬ ДЕЯТЕЛЬНОСТИ, ЧИСЛЕННОСТЬ ШТАТА, РЕНТАБЕЛЬНОСТЬ ПРЕДПРИЯТИЯ. Количество записей не менее 10. Часть записей ввести обычным образом, часть с помощью формы;

-Отсортировать список по полям ОТРАСЛЬ, ГОД ОСНОВАНИЯ, НАЗВАНИЕ. Результат сортировки скопировать на другой лист и прокомментировать;

-Отфильтровать (применить и автофильтр и расширенный фильтр) список по условию: НАЗВАНИЕ ПРЕДПРИЯТИЯ содержит фрагмент ‘ор’ или ‘ан’ и ЧИСЛЕННОСТЬ ШТАТ от 15 до 40 человек. Результат фильтрации скопировать на другой лист и прокомментировать;

-Вычислить итоги по полям ГОД ОСНОВАНИЯ (максимальное значение) и РЕНТАБЕЛЬНОСТЬ (среднее значение) для записей, сгруппированных по полю ТИП ПРЕДПРИЯТИЯ. Итоги скопировать на другой лист и прокомментировать;

Вариант 6

-Создать список данных об известных спортсменах страны: ФАМИЛИЯ, ВОЗРАСТ, ВИД СПОРТА, СПОРТИВНЫЙ КЛУБ, СПОРТИВНЫЙ ТИТУЛ,ФАМИЛИЯ ТРЕНЕРА, КОЛИЧЕСТВО ПОБЕД НА МЕЖДУНАРОДНЫХ СОРЕВНОВАНИЯХ. Количество записей не менее 10. Часть записей ввести обычным образом, часть с помощью формы;

-Отсортировать список по полям ВИД СПОРТА, ВОЗРАСТ, ФАМИЛИЯ. Результат сортировки скопировать на другой лист и прокомментировать;

-Отфильтровать (применить и автофильтр и расширенный фильтр) список по условию: ФАМИЛИЯ ТРЕНЕРА начинается на букву К или П и ВОЗРАСТ от 18 до 30 лет. Результат фильтрации скопировать на другой лист и прокомментировать;

-Вычислить итоги по полям ВОЗРАСТ (минимальное и максимальные значения) и КОЛИЧЕСТВО ПОБЕД (сумма) для записей, сгруппированных по полю ВИД СПОРТА. Итоги скопировать на другой лист и прокомментировать;

Вариант 7

-Создать список данных о вузах города: НАЗВАНИЕ, ГОД ОСНОВАНИЯ, НАПРАВЛЕННОСТЬ ПОДГОТОВКИ, КОЛИЧЕСТВО СПЕЦИАЛЬНОСТЕЙ, ФАМИЛИЯ РЕКТОРА,ЧИСЛЕННОСТЬ СТУДЕНТОВ, ЧИСЛЕННОСТЬ ПРЕПОДАВАТЕЛЕЙ. Количество записей не менее 10. Часть записей ввести обычным образом, часть с помощью формы;

-Отсортировать список по полям НАПРАВЛЕННОСТЬ, ГОД ОСНОВАНИЯ, НАЗВАНИЕ ВУЗА. Результат сортировки скопировать на другой лист и прокомментировать;

-Отфильтровать (применить и автофильтр и расширенный фильтр) список по условию: ФАМИЛИЯ РЕКТОРА начинается на букву Е или Л и КОЛИЧЕСТВО СПЕЦИАЛЬНОСТЕЙ от 25 до 40. Результат фильтрации скопировать на другой лист и прокомментировать;

-Вычислить итоги по полям ГОД ОСНОВАНИЯ (минимальное) и КОЛИЧЕСТВО СТУДЕНТОВ (сумма) для записей, сгруппированных по полю НАПРАВЛЕННОСТЬ ПОДГОТОВКИ. Итоги скопировать на другой лист и прокомментировать;

Вариант 8

-Создать список данных о города республики: НАЗВАНИЕ, ГОД ОСНОВАНИЯ, ПЛОЩАДЬ, КОЛИЧЕСТВО НАСЕЛЕНИЯ, РЕГИОН СТРАНЫ, ФАМИЛИЯ МЭРА. Количество записей не менее 10. Часть записей ввести обычным образом, часть с помощью формы;

-Отсортировать список по полям РЕГИОН, ГОД ОСНОВАНИЯ, НАЗВАНИЕ. Результат сортировки скопировать на другой лист и прокомментировать;

-Отфильтровать (применить и автофильтр и расширенный фильтр) список по условию: НАЗВАНИЕ начинается на букву А или Т и КОЛИЧЕСТВО ЖИТЕЛЕЙ от * млн. чел. до * млн. чел. Результат фильтрации скопировать на другой лист и прокомментировать;

-Вычислить итоги по полям ПЛОЩАДЬ (среднее) и КОЛИЧЕСТВО ЖИТЕЛЕЙ (сумма) для записей, сгруппированных по полю РЕГИОН. Итоги скопировать на другой лист и прокомментировать;

Вариант 9

-Создать список данных о результатах сессии студентов первого курса университета: ФАМИЛИЯ, ИНСТИТУТ, ГРУППА, ОСНОВА(грант,кредит, договор), ОЦЕНКИ ПО ЭКЗАМЕНАЦИОННЫМ ПРЕДМЕТАМ(три предмета),СРЕДНИЙ БАЛЛ ЗА СЕССИЮ.

Количество записей не менее 10. Часть записей ввести обычным образом, часть с помощью формы;

-Отсортировать список по полям ИНСТИТУТ, ОСНОВА,СРЕДНИЙ БАЛЛ.. Результат сортировки скопировать на другой лист и прокомментировать;

-Отфильтровать (применить и автофильтр и расширенный фильтр) список по условию: ФАМИЛИЯ начинается на букву П или О и ОЦЕНКА ПО ПЕРВОМУ ПРЕДМЕТУ 4 или 5. Результат фильтрации скопировать на другой лист и прокомментировать;

-Вычислить итоги по полям ОЦЕНКА ПО ПЕРВОМУ ЭКЗАМЕНУ (среднее), ОЦЕНКА ПО ВТОРОМУ ЭКЗАМЕНУ (минимальное значение) для записей, сгруппированных по полю ГРУППА. Итоги скопировать на другой лист и прокомментировать;

Вариант 10

-Создать список данных о печатных периодических изданиях:НАИМЕНОВАНИЕ ИЗДАНИЯ, ГОД ОСНОВАНИЯ, ТИП ИЗДАНИЯ, ЖАНР, ТИРАЖ, СТОИМОСТЬ ОДНОГО ЭКЗЕМПЛЯРА,ФАМИЛИЯ РЕДАКТОРА. Количество записей не менее 10. Часть записей ввести обычным образом, часть с помощью формы;

-Отсортировать список по полям ЖАНР, ТИП ИЗДАНИЯ, ГОД ОСНОВАНИЯ. Результат сортировки скопировать на другой лист и прокомментировать;

-Отфильтровать (применить и автофильтр и расширенный фильтр) список по условию: НАИМЕНОВАНИЕ начинается на букву М или В и ТИРАЖ от 500 000 экз. до 1 500 000 экземпляров. Результат фильтрации скопировать на другой лист и прокомментировать;

-Вычислить итоги по полям ТИРАЖ (сумма), СТОИМОСТЬ ОДНОГО ЭКЗЕМПЛЯРА (среднее значение) для записей, сгруппированных по полю ТИП ИЗДАНИЯ. Итоги скопировать на другой лист и прокомментировать;

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

1) Что называется списком данных?

2) Какова структура списка?

3) Каких рекомендаций нужно придерживаться при создании и ведении списка?

4) Какие способы ввода данных можно использовать при создании списка?

5) Что называется сортировкой списка? Какие виды сортировки различают? В чем суть многоуровневой сортировки?

6) Как отсортировать список по пользовательскому списку?

7) Что называется фильтрацией записей? Какие виды фильтров различают?

8) Как произвести фильтрацию записей с помощью автофильтра?

9) Как произвести фильтрацию записей с помощью расширенного фильтра?

10) Как вычислить итоги в списке?

ЛАБОРАТОРНАЯ РАБОТА № 4

СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ МАКРОСОВ

Цель: Ознакомиться со способами создания и использования макросов.

ЗАДАНИЕ 1. СОЗДАНИЕ МАКРОСА С ПОМОЩЬЮ МАКРОРЕКОРДЕРА

6.1 Понятие макроса

Макросом называется программа, созданная на языке Visual Basic с целью автоматизации различных процедур и процессов при обработке данных и формировании документов в Excel. Одним из способов создания макроса является запись текста программы в автоматическом режиме с помощью записывающего устройства, называемого макрорекордером. Для создания макроса нужно выполнить команду СЕРВИС/МАКРОС/НАЧАТЬ ЗАПИСЬ. При этом система запросит имя макроса. Если пользователь не введет желаемое имя, то по умолчанию создаваемым макросам автоматически будут присваиваться имена Макрос1, Макрос2,… После присвоения макросу имени все действия пользователя, произведенные при включенном макрорекордере будут зафиксированы в программном коде в виде инструкций Visual Basic. После завершения действий нужно остановить работу макрорекордера, выполнив команду ДАННЫЕ/МАКРОС/ОСТАНОВИТЬ ЗАПИСЬ.

6.2 Способы запуска макроса

Макрос можно запустить на выполнение одним из следующих способов:

а) СТАНДАРТНЫЙ СПОСОБ. выполнить команду СЕРВИС/МАКРОС/ МАКРОСЫ или нажать клавиши ALT+F8. При этом на экране появится окно со списком макросов (рисунок 27). Из данного списка нужно выбрать имя желаемого макроса и щелкнуть на кнопке ВЫПОЛНИТЬ. Будут выполнены все действия, которые были запрограммированы в данном макросе.

Рисунок 6.1 Окно со списком доступных макросов

б) С ПОМОЩЬЮ ГРАФИЧЕСКОГО ОБЪЕКТА. Для организации запуска макроса данным способом нужно на рабочем листе создать любой графический объект и назначить ему нужный макрос. Для этого нужно вызвать контекстное меню для данного объекта и выбрать пункт НАЗНАЧИТЬ МАКРОС (рисунок 28).

При этом на экране появится окно со списком макросов, из которых выбрать нужный.

Рисунок 6.2 Назначение макроса графическому объекту

В дальнейшем при наведении курсора мыши на данном объекте, курсор примет вид «указующего перста», и графический объект будет служить кнопкой, щелчок на которой запустит макрос.

в) С ПОМОЩЬЮ КНОПКИ, ПОМЕЩЕННОЙ НА ПАНЕЛИ ИНСТРУМЕНТОВ ИЛИ В СТРОКЕ МЕНЮ. Для реализации данного метода нужно войти в режим настройки панелей инструментов (ВИД/ПАНЕЛИ ИНСТРУМЕНТОВ/НАСТРОЙКА). В появившемся окне настройки перейти на вкладку КОМАНДЫ, выбрать категорию МАКРОСЫ и перетащить НАСТРАИВАЕМУЮ КНОПКУ на панель инструментов или в строку меню.

Рисунок 6.3 Окно настройки панелей инструментов

При необходимости можно придать НАСТРАИВАЕМОЙ КНОПКЕ придать желаемый вид:

а) выполнить команду ИЗМЕНИТЬ ВЫДЕЛЕННЫЙ ОБЪЕКТ/ВЫБРАТЬ ЗНАЧОК ДЛЯ КНОПКИ (рисунок 30)

Рисунок 6.4 Выбор значка для кнопки

б) выполнить команду ИЗМЕНИТЬ ВЫДЕЛЕННЫЙ ОБЪЕКТ/ИЗМЕНИТЬ ЗНАЧОК НА КНОПКЕ(рисунок 31). При этом на экране появится графический миниредактор, позволяющий «перерисовать кнопку», придав ей желаемый оригинальный вид.

Рисунок 6.5 Изменение значка кнопки

После получения желаемой кнопки, ей нужно назначить необходимый макрос. Для этого, при выделенной НАСТРАИВАЕМОЙ КНОПКЕ в окне настройки, щелкнуть на кнопке ИЗМЕНИТЬ ВЫДЕЛЕННЫЙ ОБЪЕКТ и, в появившемся меню, выбрать команду НАЗНАЧИТЬ МАКРОС. В дальнейшем щелчок на данной кнопке позволит запустить макрос.

Рисунок 6.6 Кнопка в строке меню для запуска макроса.

6.2 Задание для самостоятельной работы

Создать макрос, позволяющий что-либо выполнить в автоматическом режиме: формирование документа на рабочем листе, выполнение расчетов, настройка среды и т.д. Организовать все способа запуска созданного макроса.

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

1) Понятие макроса?

2) Что собой представляет макрорекордер и для чего предназначен?

3) Как записать макрос?

4) Какие способы запуска макроса различают?

5) Как реализовать каждый из способов запуска макроса?

ЗАДАНИЕ 2. СОЗДАНИЕ ПОЛЬЗОВАТЕЛЬСКОЙ ФУНКЦИИ В EXCEL

7.1 Понятие пользовательской функции

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

7.2 Создание пользовательской функции

Для создания пользовательской функции нужно владеть, по крайней мере, азами алгоритмизации и программирования, и быть знакомым с алфавитом и синтаксисом Visual Basic. Общий формат пользовательской функции имеет следующий формат:

Function Имя(формальные параметры)

…

…

Имя=результат вычислений

End Function

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

Данный алгоритм нужно записать на модульном листе в редакторе Visual Basic. Для этого нужно, находясь в Excel, выполнить команду СЕРВИС/МАКРОС/РЕДАКТОР VISUAL BASIC (ALT+F11). В редакторе VB выполнить команду ВСТАВКА/МОДУЛЬ и, в появившемся окне модульного листа, ввести текст программы. Затем вновь вернуться в Excel (ALT+F11).

7.3 Обращение к пользовательской функции

Обращение к пользовательской функции производится по всем правилам обращения к стандартным функциям, т.е. в ячейку таблицы ввести выражение =ИМЯ(фактические параметры). Где ИМЯ-имя функции присвоенное пользователем на стадии создания функции. Фактические параметры – значения, которые необходимо присвоить формальным параметрам при обращении к функции. Фактическими параметрами могут быть числовые константы, адреса ячеек и формульные выражения.

7.4 Пример пользовательской функции

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

ЗАРПЛАТА=ОКЛАД+ПРЕМИЯ+ОКЛАД*КТУ

Здесь премия зависит от объема выполненной работы:

-если объем работ меньше 50 ед.,то премия составляет 20% от оклада;

-если объем работы от 50 до 150 ед., то премия составляет 30% от оклада;

-если объем выполненной работы равен 150 ед. или превысил этот уровень, то премия составляет 45% от оклада.

Таким образом для вычисления зарплаты рабочего необходимо ввести следующие исходные данные: ОКЛАД, ОБЪЕМ РАБОТЫ, КТУ.

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

Function зарплата(оклад, работа, кту)

If работа < 50 Then премия = оклад * 0.2

If (работа >= 50) And (работа < 150) Then премия = оклад * 0.3

If работа >= 150 Then премия = оклад * 0.45

зарплата = оклад + премия + оклад * кту

End Function

Обращение к данной функции и результат вычислений приллюстрированы на рисунке 7.1.

Рисунок 7.1 Обращение к пользовательской функции ЗАРПЛАТА

7.5 Задание для самостоятельной работы

Создать пользовательскую, позволяющую вычислить значение какой-либо величины.

Вариант1.

а) Создать пользовательскую функцию, позволяющую вычислить новую стоимость товара после уценки. Новая стоимость вычисляется по следующему правилу:

-если с момента товара прошло 1 год и более, то уценить на 40%;

-если с момента товара прошло от 6 месяцев до 1 года, то уценить на 25%;

-если с момента товара прошло не более 6 месяцев, то стоимость

оставить прежнюю.

б) Создать пользовательскую функцию, позволяющую вычислить значение переменной Z.

Z=

Вариант2.

а) Создать пользовательскую функцию, позволяющую вычислить заработную плату рабочих бригады. Зарплата вычисляется по следующему алгоритму: оклад+оклад*КТУ+премия. Премия зависит от объема выполненной работы следующим образом:

-если объем выполненной работы меньше планового объема, то премии нет;

-если работа выполнена в запланированных объемах, то премия 10%;

-если работа выполнена больше запланированного объема, то премия 30%.

б) Создать пользовательскую функцию, позволяющую вычислить значение переменной D.

D=

Вариант3.

а) Создать пользовательскую функцию, позволяющую вычислить стоимость отделочных работ. Общая стоимость работ вычисляется по формуле S=åm_i*p*s_i+p*sr, где m_i-расход материала i-го вида на 1 кв. м. поверхности, площадь поверхности (кв.м.), s_i-стоимость 1 ед. материала i-го вида, sr-стоимость труда рабочего на отделку 1 кв.м.

б) Создать пользовательскую функцию, позволяющую вычислить значение переменной V.

V=

Вариант4.

а)Создать пользовательскую функцию, позволяющую вычислить стоимость аренды торговой площади по формуле S=st*pl, где pl-арендуемая площадь,st-стоимость аренды 1 кв. м. торговой площади:

-если вид арендуемой площади = “открытый”, то стоимость аренды 1 кв.м. стоит 450 тг.;

-если вид арендуемой площади = “закрытый”, то стоимость аренды 1 кв.м. стоит 750 тг.;

б) Создать пользовательскую функцию, позволяющую вычислить значение переменной W.

W=

Вариант5.

а)Создать пользовательскую функцию, позволяющую вычислить размер штрафа за нарушения. Если нарушение вида=1, то штраф составляет 10% от месячного расчетного показателя, если нарушение вида=2, то 15%, а если вида=3, то 30%.

б) Создать пользовательскую функцию, позволяющую вычислить значение переменной R.

R=

Вариант6.

а)Создать пользовательскую функцию, позволяющую вычислить, необходимый для посева, объем зерновых. При расчете нужно использовать данные: площадь посева, расход семян на 1 кв.м., % естественных потерь семян из расчета на 1 кг.

б) Создать пользовательскую функцию, позволяющую вычислить значение переменной T.

T=

Вариант7.

а)Создать пользовательскую функцию, позволяющую вычислить стоимость проката бытовой техники. St= k*s_i, где k-количество дней проката, s_i-стоимость проката 1 экз. бытовой техники i-го вида.

б) Создать пользовательскую функцию, позволяющую вычислить значение переменной Y.

Y=

Вариант8.

а)Создать пользовательскую функцию, позволяющую вычислить сумму премии, величина которой зависит от стажа. Если стаж более 10 лет, то премия составляет 30% от оклада, если стаж от 5 по 10 лет, то 25% от оклада, если премия стаж мене 5 лет, то премия не положена.

б) Создать пользовательскую функцию, позволяющую вычислить значение переменной K.

K=

Вариант9.

а) Создать пользовательскую функцию, позволяющую определить рекомендации по поводу движения сотрудника по должностной лестнице. Если стаж в данной должности более 7 лет и результат аттестации по специальности 80 и более баллов, то данного сотрудника рекомендовать на повышение в должности с прибавкой к зарплате в размере 15% от оклада. Если сотрудник соответствует только одному из данных требований – его считать кандидатом на будущее повышение и зарплату повысить на долю от оклада в 8%. Если стаж менее 5 лет, то сотрудник пребывает в прежней должности с прежней зарплатой. Еесли стаж более 7 лет, но результат аттестации ниже 50 %, то данного сотрудника рекомендовать на понижение в должности с понижением зарплаты на 5%.

б) Создать пользовательскую функцию, позволяющую вычислить значение переменной R.

R=

Вариант10.

а)Создать пользовательскую функцию, позволяющую вычислить стоимость грузоперевозок. S=km*v*stp+km*rg*stg, где km-километраж (длина пути), v-вес груза (тонны), stp – стоимость перевозки 1 тонны груза, rg-расход горючего на 1 км. пути, stg – стоимость 1 л. горючего.

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

С=

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

1) Понятие пользовательской функции?

2) Общий формат текста программного кода пользовательской функции?

3) В каком случае рекомендуется использовать пользовательскую функцию?

4) Какие параметры называются формальными?

5) Какие параметры называются фактическими?

6) Какое соответствие должно выполняться между формальными и фактическими параметрами?

7) Как создается пользовательская функция?

8) Каковы правила обращения к пользовательской функции?

ЛАБОРАТОРНАЯ РАБОТА №5