Методическая разработка

для студентов

к практическому занятию по теме

«Стандартные программные средства. Microsoft Excel-2007. Создание списка.»

1. Научно-методическое обоснование темы:

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

2. Краткая теория:

1. Понятие списка.

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

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

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

Рис.1

Теперь нужно выделить список значений (без заголовка) и на вкладке «Формулы», нажать кнопку «Присвоить имя» (Рис.2).

Рис.2

В появившемся окне заполняем поле «Имя» и жмем Ok (Рис.3).

Рис.3

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

Рис.4

Теперь на вкладке «Данные» жмем кнопку «Проверка данных» (Рис.5).

Рис.5

В появившемся окне в поле «Тип данных» выбираем значение «Список», в поле «Источник:» вводим знак “=” (равно) и набираем имя списка назначенного нами на Рис.3. Далее жмем Ok (Рис.6).

Рис.6

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

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

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

· Ячейки каждого столбца (т. е. поля) содержат однотипную информацию, имеют одну размерность, поэтому, например, дата и время представлены в одном формате (ДД.ММ.ГГ и ЧЧ.ММ).

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

· Все ячейки в каждой строке образуют одну запись и занимают не более одной строки.

· Абсолютно незаполненные строки не могут считаться данными – создав запись, следует заполнить хотя бы одно поле.

Рис. 7.1. Пример списка данных

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

Для осуществления сортировки необходимо сначала выделить диапазон ячеек, содержащих данные для сортировки, либо ячейку столбца, по которому будет произведена сортировка. После выделения можно применить команды (для сортировки по возрастанию значений) и (для сортировки по убыванию значений). Команду настраиваемой сортировки можно вызвать через Главная – Редактирование – Сортировка и фильтр, либо через Данные – Сортировка и фильтр – Сортировка. В появившемся окне Сортировка (рис. 7.2) нужно указать столбец, порядок и особенности сортировки списка

данных.

Рис. 7.2. Диалоговое окно Сортировка

Для добавления еще одного критерия сортировки нужно использовать кнопку Добавить уровень.

Чтобы данные первой строки списка не участвовали в сортировке, нужно поставить флажок «Мои данные содержат заголовки».

Чтобы задать сортировку не строк, а столбцов диапазона, нужно, нажав кнопку Параметры, указать «сортировать столбцы диапазона».

Чтобы отсортировать данные только в одном столбце списка (не изменяя порядок в других), нужно выделить требуемый столбец, вызвать команду сортировки и в появившемся окне «Обнаружены данные вне указанного диапазона» выбрать пункт «сортировать в пределах указанного выделения».

Замечание. Сортировка может быть произведена по настраиваемым пользовательским спискам, т. е. по определенному пользователем порядку сортировки. Для создания списка нужно внести элементы в ячейки, выделить их и занести в список по команде кнопка Office – Параметры Excel – Основные параметры работы с Excel – Создавать списки для сортировки и заполнения. После того, как список создан, данные могут быть отсортированы по возрастанию/убыванию элементов данного списка. Для этого в окне сортировке в графе Порядок нужно указать, что сортировка будет произведена по настраиваемому списку.

Структурирование данных

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

При ручном структурировании сначала надо определить нужные элементы – выделить диапазоны ячеек, которые должны быть структурированы, затем применить команду Данные – Структура – Группировать – Группировать. После выполнения команды, выделенные строки/столбцы становятся разделом. Нажав на знак +, можно увидеть детали раздела, нажав на знак –, можно скрыть лишние детали.

Замечание. Если при сворачивании раздела определенные строки/столбцы должны быть видимыми, их не нужно выделять для группировки.

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

Удалить структурирование можно, используя команды Данные – Структура – Разгруппировать – Удаление структуры.

Фильтрация

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

весь список. Фильтрация может быть выполнена с помощью автофильтра и расширенного фильтра.

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

автоматически будут пере- Рис.7.3. Выбор условий фильтрации считываться при каждой

фильтрации.

Чтобы применить автофильтр, нужно выделить хотя бы одну ячейку списка данных и применить команду Главная – Редактирование – Сортировка и фильтр – Фильтр или Данные – Сортировка и фильтр – Фильтр. После этого в правом углу ячеек заголовков списка появятся стрелки, нажав на которые можно получить доступ к параметрам фильтрации (рис. 7.3).

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

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

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

- между столбцами можно задать несколько критериев сравнения;

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

- в критерии можно включать формулы.

Чтобы отфильтровать список с помощью расширенного фильтра, необходимо произвести некоторые подготовительные действия:

1) проверить, чтобы столбцы списка имели заголовки;

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

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

Чтобы применить расширенный фильтр, нужно выделить диапазон фильтрации и вызвать команду Данные – Сортировка и фильтр – Дополнительно. В диалоговом окне Расширенный фильтр (рис. 7.4) нужно указать место размещения резуль-

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

Рис. 7.4. Окно Расширенный фильтр

тить панели Сортировка и фильтр.

Чтобы убрать расширенный фильтр, достаточно нажать кнопку Очис-

Разделение данных на несколько столбцов

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

С помощью команды Данные – Работа с данными – Текст по столбцам вызывается мастер, позволяющий указать параметры разбивки. Можно выбрать

разделитель для данных, имеющих одинаковые знаки-разделители, либо указать параметр «фиксированная ширина» (если в столбце данные имеют одинаковую длину) и указать точное местоположение разделителя.

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

Консолидация данных

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

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

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

Работа с данными – Консолидация. В диалоговом окне (рис. 7.5) необходимо выбрать диапазоны ячеек (поля ссылка, список диапазонов) и тип предстоящей консолидации (поле Ф ункция), а также указать использование в качестве имен некоторых значений диапазона данных и установить связь с исходными данными (чтобы при изменении исходных данных производилось обновление консолидированных). После задания параметров итоговые данные будут размещены в указанных ячейках.

Создание промежуточных и общих итогов

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

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

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

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

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

Чтобы добавить итоги для списка данных нужно: 1. Выделить исходный диапазон ячеек.

2. Выбрать команду Данные – Структура – Промежуточные итоги для вызова соответствующего диалогового окна (рис. 7.6).

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

4. В списке Операция выберите функцию, при помощи которой будут подводиться итоги.

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

6. Нажмите кнопку ОК. Excel вставит в рабочий лист строки с промежуточными итогами. Общий итог по всей таблице появится в ее нижней части.

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

должны заменяться новыми (флажок Заме- Рис.7.6. Диалоговое окно нить текущие итоги), также можно разде- Промежуточныеитоги

лить группы итогов на страницы (флажок

Конец страницы между группами) и добавить общие итоги под таблицей данных (флажок Итоги под данными).

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

Использование сводных таблиц для анализа данных

Сводные таблицы обеспечивают очень удобный интерфейс к хранилищам данных различной сложности и разного объема. Сводная таблица – это динамическая таблица специального вида, построенная на базе одной или нескольких исходных таблиц и содержащая сводную информацию по этим таблицам. Базами данных для сводных таблиц могут быть списки, таблицы, расположенные на рабочих листах Excel, либо внешние источники данных (например, базы данных Access).

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

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

3. Цель деятельности студентов на занятии:

Студент должен знать:

1. Что такое список.

2. Уметь сортировать и заполнять ячейки.

3. Уметь использовать расширенный фильтр.

Студент должен уметь:

1.Иметь навыки работы в программе MS EXCEL.

2.Уметь создавать список.

3. Работать со сводной таблицей.

Содержание обучения:

Теоретическая часть:

1.Работа со списком.

2.Назначение списка.

3. Создание промежуточных итогов.

4. Работа со сводной таблицей.

Практическая часть:

1.Создать список, набрать и отформатировать в соответствии с образцом, с использованием средств форматирования.

Вариант №1.

Для примера создайте список городов Московской области. Выделяем список и создаем именованный диапазон. Для этого после щелчка правой кнопки мыши выбираем в контекстном меню «Имя диапазона».

Задаем имя «Город_М_О» и жмем «ОК».

Теперь переходим в ту ячейку, где мы хотим иметь выпадающий список и переходим на закладку «Данные» верхней панели. Здесь нам понадобится кнопка «Проверка данных», расположенная в группе «Работа с данными». Выбираем пункт «Проверка данных».

В появившемся окне выбираем тип данных «Список» и в поле «Источник» вводим «=Город_М_О», то есть заданное нами имя диапазона, который содержит список.

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

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

Вариант 2.

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

Используя технологию формирования промежуточных итогов под данными, а также механизм копирования и вставки получить:

1. Ведомость взаиморасчетов с покупателями с выведение остатка по каждому покупателю.

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

4. Перечень вопросов для проверки исходного уровня знаний: