Основные технологические операции по сортировке данных

МИНИСТЕРСТВО ОБРАЗОВАНИЯ И НАУКИ РФ

ГОУВПО

«Воронежская государственная технологическая академия»

Кафедра ИНФОРМАЦИОННЫХ ТЕХНОЛОГИЙ

МОДЕЛИРОВАНИЯ И УПРАВЛЕНИЯ

ОБРАБОТКА ДАННЫХ В EXCEL

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

лабораторным занятиям по курсу “Информатика”

Для студентов всех направлений,

Дневной формы обучения

ВОРОНЕЖ

УДК 681.3.06

Обработка данных в Excel [Текст] : метод. указания и задания к практическим и лабораторным занятиям по курсу “Информатика” / Воронеж. гос. технол. акад.; сост. С. В. Глущенко, Н. Д. Писаренко, С. В. Чикунов, Б. Е. Никитин, И. И. Каширская. – Воронеж: ВГТА, 2011. - 31 с.

Методические указания разработаны в соответствии с требованиями ГОС и ФГОС ВПО подготовки выпускников по всем направлениям. Они предназначены для закрепления теоретических знаний дисциплин цикла ЕН и Б2 и содержат сведения о способах и методах работы с данными в Excel, с созданием консолидированных групп данных.

Библиогр.: 5 назв.

Составители: доценты С. В. ГЛУЩЕНКО, Н. Д. ПИСАРЕНКО,

С. В. ЧИКУНОВ, Б. Е. НИКИТИН,

ассистент И. И. КАШИРСКАЯ.

Научный редактор профессор Г. В. АБРАМОВ

Рецензент профессор Центрального филиала Российской

академии правосудия В. К. ГОЛИКОВ

Печатается по решению

редакционно-издательского совета

Воронежской государственной технологической академии

Ó Глущенко С. В., Писаренко Н. Д,

Чикунов С. В., Никитин Б. Е.,

Каширская И. И., 2011

Ó ГОУВПО «Воронеж. гос.

технол. акад.», 2011

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

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

Список. Сортировка Данных

Понятие о списке (базе данных Excel)

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

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

Списки (рис. 1) используют терминологию:

· строка списка - запись базы данных;

· столбец списка - поле базы данных.

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

Список (база данных Excel) - электронная таблица, в которой строки (записи) имеют фиксированную структуру, а имена столбцов (полей) занимают одну строку.

Для размещения имени поля списка в одной ячейке

(рис. 1) необходимо:

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

· ввести командуФормат, ячеек и выбрать вкладку Выравнивание;

· на вкладке установить следующие параметры:

По горизонтали: по значению;

По вертикали: по верхнему краю или по центру;

Отображение: установить флажок переносить по словам.

Рис. 1.Список в Excel

Основные технологические операции по сортировке данных

ЗАДАНИЕ 1

1. Проделайте подготовительную работу: создайте книгу и сохраните ее под именем Spisok, переименуйте Лист1 на Список, а Лист2 - на Сортировка.

2. В новой рабочей книге на листе Список создайте таблицу, приведенную на рис. 3.

3. Произведите копирование списка (базы данных) с листа Список на лист Сортировка.

4. Сделайте сортировку на трех уровнях по возрастанию: по преподавателям, по номеру группы, по коду предмета.

5. Выполните сортировку по другим полям.

Выполнение работы

1. Проведите подготовительную работу:

· создайте новую рабочую книгу командой Файл, Создать. Укажите шаблон Книга;

· сохраните созданную рабочую книгу под именем Spisok командой Файл; Сохранить как;

· переименуйте Лист 1 на Список;

· переименуйте Лист1 на Сортировка.

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

· выделите первую строку;

· вызовите контекстное меню и выберите команду Формат ячеек;

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

По горизонтали: по значению;

По вертикали: по верхнему краю;

Переносить по словам: установить флажок;

· введите названия столбцов (имен полей) в соответствии с отображенной на рис. 3 таблицей;

· заполните таблицу данными.

3. Выделите список, начиная от имен полей и вниз до конца записей таблицы, и скопируйте их на лист Сортировка.

4. Выполните сортировку по столбцу Таб. № препод. Для этого:

· установите курсор в поле списка и введите командуДанные ® Сортировка. При этом должна выделиться вся область списка. Если этого не произошло, то предварительно выделите весь список, а затем введите указанную команду;

· в диалоговом окне <Сортировка диапазона> установите:

5. Сортировать по: поле <Таб. № препод.>, по возрастанию. Затем по: поле <Номер группы>, по возрастанию.

В последнюю очередь по: поле <Код предмета>, по возрастанию;

· установите флажок Идентифицировать поля по подписям.

6. Выполните сортировку по другим полям.

Рис. 3. Пример списка (базы данных)

Фильтрация ()выборка) данных

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

Автофильтрация

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

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

· все — выбираются все записи без ограничений;

· первые 10 — данный пункт позволяет во вновь появляющемся диалоговом окне «Наложение условия по списку» (рис. 5) выбрать определенное количество наибольших или наименьших элементов списка, которые необходимо отобразить;

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

· условие — выбираются записи по формируемому пользователем условию в диалоговом окне «Пользовательский фильтр» (рис. 6).

Каждая часть условия включает:

· оператор отношения: = (равно), <> (не равно), > (больше), >= (больше или равно), < (меньше), <= (меньше или равно), начинается с, содержит и т.п.;

· значение, которое может выбираться из списка или содержать шаблонные символы *,?.

Рис. 4. Список с автофильтром

Рис. 5. Диалоговое окно «Наложение условия по списку»

Рис. 6. Диалоговое окно «Пользовательский фильтр»

Пример. Для Кода предмета можно сформировать условия:

>=п* — отобрать все записи, которые содержат код предмета, начинающийся с буквып;

>= п1 И <=п2 — отобрать все записи, которые содержат коды предметов п1 и п2;

<>п1 — отобрать все записи, которые не содержат кода предметап1.

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

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

КомандаДанные, Фильтр, Расширенный фильтр обеспечивает использование двух типов критериев для фильтрации записей списка:

· критерий сравнения;

· вычисляемый критерий.

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

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

Технология использования расширенного фильтра состоит из двух этапов:

этап 1 — формирование области критериев поиска;

этап 2 — фильтрация записей списка.

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

Критерий сравнения формируется при соблюдении следующих требований:

· состав столбцов области критериев определяется столбцами, по которым задаются условия фильтрации записей;

· имена столбцов области критериев должны точно совпадать с именами столбцов исходного списка;

· ниже имен столбцов располагаются критерии сравнения типа:

· точного значения;

· значения, формируемого с помощью операторов отношения;

· шаблона значения, включающего символы *и (или)?.

Правила формирования множественного критерия:

1. Если критерии (условия) указываются в каждом столбце на одной строке, то они считаются связанными условием И .

2. Если условия записаны в нескольких строках, то они считаются связанными условием ИЛИ.

Пример. Условие выбора записей о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5 можно записать несколькими способами:

1-й способ. Множественный критерий сравнения — все условия находятся в одной строке, связка И. Номер группы, код предмета заданы как точные значения, оценка— оператор сравнения со значением константы.

2-й способ. Множественный критерий сравнения — все условия (точи значения полей) находятся в одной строке, столбец Оценка используется дважды, связка И.

3-й способ. Множественный критерий сравнения — условия (точные значения полей) записаны в двух строках, связкаИЛИ.

Вычисляемый критерий представляет собой формулу, записанную в строке области» условий, которая возвращает логическое значение ИСТИНА или ЛОЖЬ. Формула строится с использованием: адресов ячеек, встроенных функций, констант различных типов (число, текст, дата, логическая константа), операторов отношения.

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

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

В области критериев присутствуют столбцы с заголовками: Номер группы. Оценка1.

Последовательность действий по созданию данного вычисляемого критерия:

· присвоить отличное от имен полей списка новое имя столбцу, куда будет введен вычисляемый критерий;

· установить курсор в ячейку ввода;

· вызвать Мастер функций — команда Вставка, Функция, выбор категории —Логические и выбор функции — ИЛИ;

· ввод параметров функции ИЛИ:

Логическое 1: G2<=CP3HAЧ($G$2:$G$17)

(при вводе формулы использовать курсорный указатель на ячейки таблицы, вызов встроенной функцииСРЗНАЧ, указание на абсолютные ссылки с помощью клавиши <F4>).

Логическое 2: G2=5

После завершения ввода вычисляемого критерия в ячейке должна появиться логическая константа ИСТИНА или ЛОЖЬ — результат применения сформированного вычисляемого критерия по отношению к первой записи списка; формулу критерия можно просмотреть лишь в строке формул. Этот же критерий можно было записать по-другому:

либо в комбинированном виде:

Этап 2 .Фильтрация записей расширенным фильтром. После подготовки области критерия курсор устанавливается в список и выполняется командаДанные, Фильтр, Расширенный фильтр(рис. 7).

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

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

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

Для снятия действия условий фильтрации выполняется команда Данные, Фильтр, Отобразить все.

Фильтрация с помощью формы данных

Excel позволяет работать с отдельными записями списка с помощью экранной формы (рис. 8). Основные операции обработки записей списка: последовательный просмотр записей, поиск или фильтрация записей по критериям сравнения, создание новых и удаление существующих записей списка.

При установке курсора в область списка и выполнении команды Данные, Форма на экран выводится форма, в составе которой имена полей — названия столбцов списка.

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

Рис. 7. Диалоговое окно «Расширенный фильтр»

Для создания новой записи нажимаете) кнопка <Добавить>, выполняется заполнение пустых полей экранной формы; для переходи между полями формы используются курсор мыши либо клавиша <Таb>. При повторном нажатии кнопки <Добавить> сформированная запись добавляется в конец списка. Для удаления текущей записи нажимается кнопка <Удалить>, Удаленные записи не могут быть восстановлены, при их удалении происходит сдвиг всех остальных записей списка.

С помощью экранной формы задаются критерии сравнения. Для этого нажимаете» кнопка <Критерии>, форма очищается для т да условий поиска в полях формы с помои кнопки <0чистить>, а название кнопки <Kритерии> заменяется на название <Правка>. По ввода критериев сравнения нажимаются кнопки <Назад> или <Далее> для просмотра отфильтрованных записей в нужном направлении. 1И просмотре можно удалять и корректировать отфильтрованные записи списка. Для возврата к форме нажимается кнопка <Правка>, для выхода из формы — кнопка <3акрыть>.

Рис. 8. Экранная форма для работы со списком записей

ЗАДАНИЕ 2

Выберите данные из списка по критерию отбора, используя Автофильтр.

· Проведите подготовительную работу — переименуйте новый лист на Автофильтр и скопируйте на него исходную базу данных (рис. 3);

· Выберите из списка данные, используя критерий:

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

2. Вид занятий — л:

· Отмените результат автофильтрации.

· Выберите из списка данные, используя критерий: для группы 133 получить сведения о сдаче экзамена по предмету п1 на оценки 3 и 4;

· Отмените результат автофильтрации;

· Выполните несколько самостоятельных заданий, задавая произвольные критерии отбора записей.

Выполнение работы

1. Выберите из списка данные, используя критерий — для преподавателя — а1 выбрать сведения о сдаче экзамена на положительную оценку, вид занятий — л. Для этого:

· установите курсор в область списка и выполните команду Данные, Фильтр, Автофильтр; в каждом столбце появятся кнопки списка;

2. Сформируйте условия отбора записей:

· в столбце Таб. № препод. нажмите кнопку , из списка условий отбора выберите а1;

· в столбце Оценка нажмите кнопку , из списка условий отбора выберите Условие и в диалоговом окне сформируйте условие отбора >2;

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

3. Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные, Фильтр, Автофильтр.

4. Выберите из списка данные, используя критерий — для группы 133 получить сведения о сдаче экзамена по предмету п1 на оценки 3 и 4. Для этого воспользуйтесь аналогичной п. 3 технологией фильтрации.

5. Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные» Фильтр, Автофильтр.

6. Выполните несколько самостоятельных заданий, задавая произвольные критерии отбора записей.

ЗАДАНИЕ 3

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

Для этого:

1. Проведите подготовительную работу — переименуйте новый лист на Расширенный фильтр и скопируйте на него исходную базу данных (см. рис. 3).

2. Скопируйте имена полей списка в другую область на том же листе.

3. Сформируйте в области условий отбора Критерий сравнения — о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5.

4. Произведите фильтрацию записей на том же листе.

5. Придумайте собственные критерии отбора по типу Критерий сравнения и проведите фильтрацию на том же листе.

6. Сформируйте в области условий отбора Вычисляемый критерий — для каждого преподавателя выбрать сведения о сдаче студентами экзамена на оценку выше средней, вид занятий — л; результат отбора поместите на новый рабочий лист.

7. Произведите фильтрацию записей на новом листе.

8. Придумайте собственные критерии отбора по типу Вычисляемый критерий и поместите результаты фильтрации на выбранном ранее листе.

Выполнение работы

1. Проведите подготовительную работу:

· переименуйте Лист4 — Расширенный фильтр;

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

Этап 1. Формирование диапазона условий по типу Критерий сравнения

2. Скопируйте все имена полей списка в другую область на том же листе например установив курсор в ячейку J1. Это область, где будут формироваться условия отбора записей. Например, блок ячеек J1:O1 — имена полей области критерия, J2:О5 — область значений критерия.

3. Сформируйте в области условий отбора Критерий сравнения — о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5. Для этого в первую строку после имен полей введите:

· в столбец Номер группы — точное значение — 133;

· в столбец Код предмета — точное значения — п1;

· в столбец Оценка—условие —>3.

Этап 2. Фильтрация записей расширенным фильтром.

4. Произведите фильтрацию записей на том же листе:

· установите курсор в область списка (базы данных);

· выполните командуДанные, Фильтр, Расширенный фильтр;

· в диалоговом окне «Расширенный фильтр» с помощью мыши задайте параметры, например, скопировать результат в другое место: установите флажок

Исходный диапазон: A1:G17;

Диапазон условия: J1:O5;

Поместить результат в диапазон: J6;

· нажмите кнопку <ОК>.

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

Этап 1. Формирование диапазона условий по типу Вычисляемый критерий.

6. Сформируйте в области условий отбора Вычисляемый критерий — для каждого преподавателя выберите сведения о сдаче студентами экзамена на оценку выше средней, вид занятий — л; результат отбора поместите на новый рабочий лист. Для этого:

· в столбец Вид занятия введите точное значения — буквул;

· переименуйте в области критерия столбец Оценка, например, на имя Оценка 2;

· в столбецОценка 1 введите вычисляемый критерий, например, вида

· =G2>CP3HAЧ($G$2:$G$17),

где G2 — адрес первой клетки с оценкой в исходном списке,

$G$2 : $G$I7 — блок ячеек с оценками,

СРЗНАЧ — функция вычисления среднего значения.

Этап 2. Фильтрация записей расширенным фильтром.

7. Произведите фильтрацию записей на новом листе:

· установите курсор в область списка (базы данных);

· выполните команду Данные, Фильтр, Расширенный фильтр;

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

Исходный диапазон: A1:G17.

Диапазон условия: Л:05.

Поместить результат в диапазон: перейдите на новый лист и щелкните мышью в любой ячейке;

· нажмите кнопку <ОК>.

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

 

ЗАДАНИЕ 4

 

Используя Форму, выберите данные из списка.

1. Проведите подготовительную работу — переименуйте новый лист на Форма и скопируйте на него исходную базу данных (рис. 3).

2. Просмотрите записи списка с помощью формы данных, добавьте новые.

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

4. Просмотрите отобранные записи.

5. Сформируйте собственные условия отбора записей и просмотрите их.

 

Выполнение работы

 

1. Проведите подготовительную работу:

· переименуйте Лист5 — Форма;

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

· установите курсор в область списка и выполните команду Данные, Форма,

2. Просмотрите записи списка и внесите необходимые изменения с помощью кнопки <Назад> и <Далее>. С помощью кнопки <Добавить> добавьте новые записи.

3. Сформируйте условие отбора — для преподавателя — а1 выбрать сведения о сдаче студентами экзамена на положительную оценку, вид занятий — л. Для этого:

· нажмите кнопку <Критерии>, название которой

поменяется на <Правка>;

· в пустых строках имен полей введите критерии;

· в строку Таб № препод. введите а1;

· в строку Вид занятия введите л;

· в строку Оценка введите условие > 2.

4. Просмотрите отобранные записи, нажимая на кнопку <Назад> или <Далее>.

5. Аналогично сформируйте собственные условия отбора записей и просмотрите их.

 

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

Результат консолидации (агрегирования или объединения) данных, представленных в исходных областях - источниках находится в области-назначении. Таблица консолидации создается путем применения функции обработки к исходным значениям. Области-источники могут находиться на различных листах или рабочих книгах. В консолидации может участвовать до 255 областей-источников.

Существуют следующие варианты консолидации данных:

· с помощью формул, где используются ссылки;

· по расположению данных для одинаково организованных областей-источников (фиксированное расположение);

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

· с помощью сводной таблицы;

· консолидация внешних данных.

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

ласти-назначения (рис. 9):

· все области на одном листе - в ссылках указывается адрес блока ячеек, например D1.C8;

· области на разных листах - в ссылках указывается название листа, диапазон, например лист1!D1 :лист2!С8;

· области в разных книгах, на разных листах - в ссылках указывается название книги, название листа, диапазон, например [книга1] лист1!D1: [книга2] лист2!С8.

Рис. 9. Представление о консолидации данных

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

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

 

 

Рис. 10. Пример областей-источников при консолидации данных по расположению

 

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

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

Условия консолидации задаются в диалоговом окне <Консолидация> (рис. 11). В окне <Функция> выбирается функция консолидации данных. Для каждой области-источника строится ссылка, для чего курсор устанавливается в поле ссылки, затем переходят в область-источника для выделения блока ячеек и нажимается кнопка <Добавить>.

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

значения левого столбца (рис. 12). Excel автоматически переносит эти имена в область назначения.

Рис. 11. Пример областей-источников при консолидации

данных по категориям.

 

Ссылка может иметь любую из следующих форм:

ссылки на ячейки     ссылки на лист и ячейки     ссылки на книгу, лист и ячейки полный путь и все ссылки     имя поименованной области

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

 

Рис. 12. Диалоговое окно <Консолидация> для задания условий

консолидации

 

При консолидации внешних данных в диалоговом окне «Консолидация» следует нажать кнопку <0бзор>, в диалоговом окне «Обзор» выбрать файл, содержащий области-источники для добавления к списку, а затем добавить ссылку на ячейку или указать имя блока ячеек.

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

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

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

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

 

ЗАДАНИЕ 5

 

Консолидация данных по расположению и по категориям.

 

---

Не нашли, что искали? Воспользуйтесь поиском по сайту:

©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.