1.4. Содержание отчета 

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

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

 

1. Какие средства имеются в электронных таблицах Excel для построения графиков и диаграмм?
2. Какие этапы необходимо выполнить для построения графика с помощью мастера диаграмм?
3. Как изменить формат отображения ряда данных на диаграмме?
4. Какие изменения можно внести при редактировании формата оси на диаграмме?
5. Как изменить область данных при работе с диаграммой?
6. Какие типы диаграмм вы знаете. Как изменить вид диаграммы?

2. ЗАДАНИЕ №2

ОБРАБОТКА СПИСКОВ

 

           Цель – изучить  принципы  выполнения  операций  сортировки    данных   (строк / столбцов)  и    поиска  записей  с  использованием  фильтров.  

      2.1. Краткие теоретические  сведения

      2.1.1. Создание списков

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

      Список можно создавать вручную, вводя в ячейки таблицы необходимые данные. Ввод списка можно осуществлять с помощью команды Данные – Форма. В окне команды размещаются поля таблицы. Форма дает возможность просматривать, редактировать, добавлять записи, а также осуществлять поиск по заданным признакам. Операции редактирования записей выполняются с помощью кнопок «Добавить», «Удалить». Движение по списку осуществляется с помощью кнопок «Назад», «Далее». Кнопка «Критерий» позволяет получить доступ к окну поиска. Окно содержит перечень столбцов таблицы, причем все соответствующие поля пусты. Достаточно ввести в одно или несколько полей нужное значение или условие и записи, удовлетворяющие этому условию будут представлены в окне команды.

      2.1.2. Сортировка данных

 

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

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

    Сортировка  столбцов. В окне  команды Сортировка нажать кнопку Параметры. Установить переключатель Сортировать в положение Столбцы диапазона и нажать кнопку OK. Выбор строк, по которым требуется отсортировать столбцы производится в полях Сортировать по и Затем по.

    Сортировка  в задаваемом пользователем порядке. Требуемый порядок сортировки задается в окне  Сортировка - Параметры.  Для этого необходимо вызвать раскрывающееся  меню в поле Сортировка по первому ключу. Пользовательский список порядка сортировки необходимо создать заранее с помощью команды Сервис-Параметры-Списки либо из существующих на листе элементов (их необходимо выделить и нажать кнопку Импорт), либо с помощью непосредственного ввода элементов списка.  

    2.1.3.  Применение фильтров  

    С помощью фильтров можно выводить и просматривать только те данные, которые удовлетворяют определенным условиям.

      Для этих целей в меню Данные – Фильтр есть две команды Автофильтр и Расширенный фильтр. Для применения фильтров необходимо выделить список или установить курсор на любую ячейку списка.

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

    С помощью автофильтра можно устанавливать  сложные пользовательские критерии. Для этого в раскрывающемся списке необходимо выбрать строку «Условие», в результате чего появится диалоговое окно.

      Левые поля диалогового окна задают операцию отношения, которая выбирается из меню (больше, меньше, равно, … для чисел, «содержит», «не содержит»… для текстов). Правые поля должны содержать сравниваемые значения. Можно использовать символы «?» и «*» вместо любого символа или группы символов соответственно.  Критерии типа “И”, “ИЛИ” могут быть заданы для отдельного поля. Если задать условия для нескольких полей, то будут выбраны записи, для которых одновременно выполняются заданные условия  (критерий  “И”).  Пример использования автофильтра приведен на рисунке 2.1. 

 

Рисунок 2.1 – Выбор строк, для которых значения в поле «Доход» находятся в диапазоне 2000…3000. 

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

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

 

Рисунок 2.2 – Выбор строк, для которых значения в поле «Доход» превышают 2000. 

      При использовании расширенного фильтра  необходимо выделить область, содержащую данные (A2:B4). Активизировать фильтр путем выбора пункта Данные - Фильтр - Расширенный Фильтр. Подтвердить область исходной таблицы и указать диапазон критерия D2:E3.На выходе фильтра появятся  записи, соответствующие заданному критерию.

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

      Аналогично  в ячейку под соответствующей  меткой  столбца можно вставить другой критерий. При этом критерию “И” соответствует несколько условий, задаваемых в одной строке (для нескольких полей). Несколько условий в различных строках интерпретируются как критерий “ИЛИ”. 

       2.2. Порядок выполнения  задания 

1.  Выбрать  вариант  i=N mod 14, где N – две последние цифры зачетки.

2. Создать  таблицу для заданной предметной  области в соответствии с вариантом.  Таблица должна содержать 5-6 полей ( 2-3 числовых поля, остальные - символьные, даты и др.), 10-15 записей. Использовать построенную таблицу при выполнении последующих заданий.

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

4. Выполнить выборку данных из таблицы с использованием фильтров.

    4.1. Автофильтр. Использовать в качестве условий : 1) точную форму (константу) выбора; 2) текст выбора (шаблон); 3) формулу выбора (условие).

      Задать  критерий типа "И" для поиска данных по значениям двух полей. Задать критерий типа "ИЛИ" для поиска данных в  отдельном столбце.

    4.2. Расширенный фильтр. Создать на рабочем листе таблицу критериев. Для числового поля задать простое условие, сложное условие для попадания значений числового поля в диапазон (критерий И). Для значений символьного поля реализовать условие ИЛИ. 
 

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

Таблица 2.1 – Варианты заданий

 

    2.4. Содержание отчета 

    Цель  работы, постановка задачи, основные шаги выполнения сортировки, компьютерные распечатки  исходной таблицы, результаты сортировки с         выбранными полями (строками) и порядком   сортировки по каждому полю (строке); описание основных шагов выполнения поиска, критерии  “И”,  “ИЛИ” для поиска числовых и символьных полей, выводы.

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

 

1. Что такое "База данных" с точки зрения Excel?
2. Какие средства существуют в Excel для ввода данных в базу?
3. Как осуществлять поиск записи по критерию в режиме "Форма"?
4. Какие возможности предоставляет команда "Автофильтр"?
5. Как выполняется поиск с использованием расширенного фильтра?
6. Пояснить критерий типа «ИЛИ».
7. Пояснить критерий типа  «И».

3. ЗАДАНИЕ №3.

ИСПОЛЬЗОВАНИЕ MICROSOFT EXCEL В ЭКОНОМИЧЕСКИХ РАСЧЕТАХ 

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

1. . Краткие теоретические  сведения

      Для извлечения информации из блоков (диапазонов) используют функции  категории «ссылки и массивы». Массив - это объект Excel, используемый для получения нескольких значений в результате вычисления одной формулы (диапазон массива) или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам (диапазон констант).

       Для ссылки на ячейку, группу ячеек или  формулу удобно использовать имена. Для присвоения имени используется команда Вставка – Имя - Присвоить. В поле Имя вводится имя диапазона, в поле Формула указывается ячейка или диапазон (вводится непосредственно либо выделяется с помощью мыши). После нажатия кнопки Добавить имя отображается в соответствующем поле. По умолчанию имена являются абсолютными ссылками. Использование имен может упростить понимание формулы. Например, формула «=Активы-Пассивы» понятнее формулы «=F6-D6». Имена можно использовать в любом листе книге. Например, если имя «Контракты» ссылается на группу ячеек "A20:A30" в первом листе рабочей книге, то это имя можно применить в любом другом листе той же рабочей книги.

      3.1.1. Основные встроенные функции для работы с массивами

      Функция ПРОСМОТР. Векторная форма функции ПРОСМОТР просматривает диапазон, в который входят значения только одной строки или одного столбца (вектор) в поисках определенного значения и возвращает значение из другого столбца или строки.