Тема: операции над рабочими листами.

 

Цель: изучить операции над рабочими листами, уметь связывать их при выполнении различных задач.

Теоретические сведения

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

Каждая создаваемая новая рабочая книга состоит из опреде­ленного числа рабочих листов (по умолчанию 16). Их количество изменяется в зависимости от установки по команде Сервис > Па­раметры, вкладка Общие.

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

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

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

Прямое связывание

Для ссылки из таблицы, расположенной на Листе 1, на данные таблицы, расположенной на Листе 2, надо в ячейке таблицы Лис­та 1 указать абсолютный адрес ячеек с данными Листа 2, в виде: Лист2!$А$1.

Если нужно сослаться на данные, расположенные в незагружен­ном файле (другой рабочей книге), то нужно задать полный путь местонахождения файла: =D:\ EXCEL\[PRIMER]ЛИCT 2!$N$20, где ЛИСТ2! — имя рабочего листа в одинарных кавычках, [PRIMER] — имя рабочей книги.

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

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

В Microsoft Excel существуют следующие методы консолида­ции данных:

• по расположению, если данные во всех сводимых областях
располагаются на разных листах, но в таблицах с одинако­вой структурой;

• по категориям (именам), если сводимые области отличаются
по структуре и расположению, но имеют одинаковые кате­гории.

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

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

• отсортировать таблицу по столбцу, содержащему группы, по
которым надо подвести итоги;

• установить курсор в любую ячейку этого столбца;

• задать команду Данные > Итоги;

• в поле При каждом изменении в указать столбец с группами,
по которым надо подводить итоги;

• в поле Использовать функцию указать СУММА;

• в перечне Добавить итоги по указать столбцы, значения в ко­торых должны быть просуммированы;

• нажать кнопку ОК.

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

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

Для анализа данных используются различные приемы.

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

Математическая суть задачи состоит в решении уравнения f(x) = а, где функция f(x) описывается заданной формулой, х — искомый параметр, а — требуемый результат формулы.

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

Анализ выполняется при помощи таблицы подстановки данных, поиска решений, диспетчера сценариев и др.

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

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

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

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

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

Целевая ячейка — это ячейка, для которой нужно найти мак­симальное, минимальное или заданное значения.

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

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

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

Каждый отчет создается на отдельном листе текущей рабочей книги.

Для создания отчета надо в диалоговом окне Результаты поис­ка решения выбрать нужный тип отчета в поле Тип отчета. Можно выбрать сразу несколько типов (при выделении нескольких строк используется клавиша <Ctrl>).

Типы отчетов:

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

• Устойчивость — отчет содержит сведения о степени зависи­мости модели от изменений величин, входящих в формулы, применяемые в задаче (формулы модели и формулы ограни­чений).

• Пределы — выводится целевая ячейка и ее значение, а также
список изменяемых ячеек, их значений, нижних и верхних пределов и целевых результатов.

 

Задание

1. Изучите прием связывания листов.

2. Выполните консолидацию данных.

3. Подведите промежуточные итоги в таблице.

4. Изучите способы анализа данных.

5. Выполните предложенное упражнение.

Технология работы

1. Связывание рабочих листов таблицы

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

1.1. На листе 1 в ячейке В6 введите число 3.

1.2. Перейдите на рабочий Лист 2 (щелк­нув левой кнопкой по ярлыку) и в ячейке А1 введите формулу: =Лист 1!$В$6/5*100. В ячейке А1 Листа 2 получим 60.

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

Консолидация по расположению

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

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

2.2. Задайте команду Данные –Консолидация.

2.3. В окне Консолидация в поле Функция укажите нужную функ­цию (например, СУММА).

2.4. Установите курсор в поле Ссылка, в каждой из сводимых таблиц поочередно с помощью мыши выделяйте диапазон подлежа­щих консолидации данных (без имен-категорий!) и в окне Консо­лидация нажмите кнопку Добавить.

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

Консолидация по категориям (именам)

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

2.5. Установите курсор в верхнюю левую ячейку области, где будут
располагаться значения объединенных данных, включая категории.

2.6. Задайте команду Данные – Консолидация.

2.7. В окне Консолидация в поле Функция укажите нужную функ­цию (например, СУММА).

2.8. В наборе флажков Использовать в качестве имен следует установить флажки, соответствующие расположению в исход­ной области заголовков: подписи верхней строки, значения левого столбца или подписи верхней строки и значения лево­го столбца одновременно.

2.9. Установите курсор в поле Ссылка, поочередно на каждом листе с помощью мыши определяйте подлежащие консоли­дации данные (вместе с именами-категориями!) и в окне Консолидация нажмите кнопку Добавить.

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

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

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

3. Подведение промежуточных итогов в таблице

3.1. Создайте рабочую книгу из трех листов. Присвойте первому листу имя «Январь», второму — «Февраль», третьему — «Итого».

3.2. Выделите все листы и введите шапку таблицы.

3.3. Снимите выделение листов и внесите необходимые изменения в
названия трех таблиц.

3.4. Введите заголовки и заполните данными таблицу 1 (рис. 1), на листе «Январь», а таблицу 2 (рис. 2) — на листе «Февраль».

№ выпуска ГКО	Эмиссия (млрд. руб.)	Выручка (млрд. руб.)	Погашено (млрд. руб.)	Доход бюджета (млрд. руб.)	Средняя взвешенная цена
21000RMFS	979,69	662,95	433,90
21000RMFS	1998,00	1276,40	1250,70
22000RMFS	2440,89	1409,89	1296,50
23000RMFS	278,53	197,45
22000RMFS	162,50	118,23	54,64
ИТОГО:

Рис.1. Справка по объемам эмиссии ГКО и доходах бюджета за январь

№ выпуска ГКО	Эмиссия (млрд. руб.)	Выручка (млрд. руб.)	Погашено (млрд. руб.)	Доход бюджета (млрд. руб.)	Средняя взвешенная цена
21000RMFS	1385,83	911,78	903,10
21000RMFS	50,00	36,64
22000RMFS	320,00	224,61	98,75
23000RMFS	143,07	56,71
22000RMFS	38,76	33,14	18,43
23000RMFS	66,65	52,17	32,17
21000RMFS	150,00	60,82
32000RMFS	54,53	47,18
21000RMFS	45,18	35,72	14,22
ИТОГО:

 

Рис. 2. Справка по объемам эмиссии ГКО и доходах бюджета за февраль

3.5. Выполните все необходимые расчеты следующим образом:

• графа «Доходы бюджета» = графа «Выручка» — графа «Пога­шено»

• графа «Средняя взвешенная цена» = графа «Выручка» / гра­фа «Эмиссия» * 100.

3.6. Рассчитайте суммы итогов за январь и февраль методом авто­суммирования.

3.7. Перейдите на лист «Итого», введите заголовок «Итоговая таблица».

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

3.8. Рассчитайте общие суммы по каждой графе для листа «Итого».

3.9. На листе «Февраль» рассчитать промежуточные итоги по каждому выпуску ГКО.

4. Анализ данных

Подбор параметра

Дано уравнение

Х^2 + 3*Х - 2=А, где А — требуемый результат формулы; X — искомый параметр.

Определить такое значение параметра X, при котором А будет равно 20.

4.1. Введите в ячейку А4 указанную формулу. В формуле сделайте ссылку на ячейку, в которой условно находится параметр X.

4.2. Задайте команду Сервис –Подбор параметра.

4.3. В поле Установить в ячейке указажите А4 (по умолчанию в это поле вводится адрес текущей ячейки).

4.4. В поле Значение введите — 20.

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

Подбор параметра можно выполнять графически, перетаски­вая точки данных на диаграмме.

Таблицы подстановки данных

Анализ формулы начинается с подготовки таблицы подстановки:

1. Левую верхнюю ячейку блока, отведенного под таблицу, ос­тавить пустой.

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

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

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

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

5. В меню Данные выбрать команду Таблица подстановки.

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

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

7. Щелкнуть по кнопке ОК. Таблица будет заполнена значе­ниями.

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

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

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

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

Выделить таблицу подстановки.

5. В меню Данные выбрать команду Таблица подстановки.

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

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

8. Щелкнуть по кнопке ОК. Таблица будет заполнена значе­ниями.

Диспетчер сценариев

Чтобы создать сценарий, следует:

1. В меню Сервис выбрать команду Сценарии.

2. Щелкнуть по кнопке Добавить. Откроется окно Добавление сценария.

3. В поле Название сценария ввести имя сценария.

4. В поле Изменяемые ячейки ввести ссылки на изменяемые ячейки. Несколько ссылок отделяются друг от друга точками с за­пятыми. Ссылки можно ввести с клавиатуры или выделить их на рабочем листе. Несмежные ячейки добавляются при нажатой
клавише <Ctrl>.

5. Щелкнуть по кнопке ОК.

6. В открывшемся диалоговом окне Значения ячеек сценария ввести значения каждой изменяемой ячейки.

7. Для создания других сценариев щелкнуть по кнопке Доба­вить (откроется диалоговое окно Добавление сценария) и повто­рить пункты 3—6.

Для завершения работы с Диспетчером сценариев щелкнуть по кнопке ОК, а затем — по кнопке Закрыть.

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

Для просмотра сценария нужно:

1. В меню Сервис выбрать команду Сценарии.

2. В поле Сценарии выделить имя сценария, который необхо­димо просмотреть.

3. Щелкнуть по кнопке Вывести.

Вместо пунктов 2 и 3 можно дважды щелкнуть по имени нуж­ного сценария.

Чтобы отредактировать сценарий, надо:

1. В меню Сервис выбрать команду Сценарии.

2. В поле Сценарии выделить имя сценария, который необхо­димо отредактировать.

3. Щелкнуть по кнопке Изменить.

4. Внести необходимые изменения: можно изменить имя сце­нария, изменяемые ячейки, значения изменяемых ячеек.

5. Для завершения работы с Диспетчером сценариев щелкнуть
по кнопке ОК, а затем — по кнопке Закрыть.

Для создания итогового отчета по сценариям следует:

1. В меню Сервис выбрать команду Сценарии.

2. Щелкнуть по кнопке Отчет!

3. Выбрать тип отчета: Структура или Сводная таблица.

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

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

4. В поле Ячейки результата ввести ссылки на ячейки, значе­ния которых надо представить в отчете. В качестве разделителя ссылок используется запятая. Ссылки можно ввести с клавиатуры или выделить их на рабочем листе. Несмежные ячейки добавля­ются при нажатой клавише <Ctrl>. Итоговые отчеты создаются на отдельных листах.

Поиск решения

Чтобы запустить процедуру поиска решения, надо:

1. В меню Сервис выбрать команду Поиск решения. Откроется диалоговое окно Поиск решения.

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

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

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

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

• в поле Ссылка на ячейку ввести ссылку на ячейку, содержа­щую формулу, которая определяет ограничение; формула должна прямо или косвенно зависеть от одной или несколь­ких изменяемых ячеек;

• во втором поле выбрать оператор ограничения (>,<, = и т.д.);

• в поле Ограничение ввести значение ограничения.

6. Для задания следующего ограничения щелкнуть по кнопке Добавить и повторить операции пункта 5.

7. Когда все ограничения будут заданы, щелкнуть по кнопке ОК, чтобы вернуться в диалоговое окно Поиск решения.

5. Изменять и удалять ограничения можно с помощью кнопок Изменить и Удалить.

9. С помощью кнопки Параметры можно задать: максималь­ное время решения; предельное число итераций; относительную погрешность; допустимое отклонение; сходимость; метод поиска.

Если известно, что решаемая задача линейная (т.е. зависимо­сти между переменными линейны), то следует включить режим Линейная модель: процесс решения значительно ускорится.

Для возврата в диалоговое окно Поиск решения щелкнуть по кнопке ОК.

10. Для инициализации процедуры поиска решения щелкнуть по кнопке Выполнить. Полученные результаты будут выведены на рабочий лист.

После завершения процедуры решения в диалоговом окне Ре­зультаты поиска решения можно выполнить один из следующих вариантов:

• сохранить найденное решение или восстановить исходные значения на рабочем листе;

• сохранить параметры поиска решения в виде модели;

• сохранить решение в виде сценария;

• просмотреть любой из встроенных отчетов.

Текущие установочные параметры для поиска решения можно сохранить в виде модели.

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

При сохранении модели запоминаются целевая ячейка, изме­няемые ячейки, ограничения и параметры поиска решения.

Чтобы впоследствии загрузить модель, надо щелкнуть по кнопке Загрузить модель в диалоговом окне Параметры поиска решения. (Диалоговое окно Параметры поиска решения открыва­ется при щелчке по кнопке Параметры в диалоговом окне коман­ды Сервис > Поиск решения).

Найденные решения (значения изменяемых ячеек) можно со­хранить в качестве сценария. Для этого нужно:

1. В диалоговом окне Результаты поиска решения выбрать Со­хранить сценарий.

2. В поле Название сценария ввести имя сценария. Просмотреть сценарии можно с помощью команды Сервис – Сценарии.

Упражнение

1. Создайте таблицу, отображающую результаты хозяйственной деятельности предприятия.

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

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

Формулы и константы для расчетов:

Объем сбыта = 35 х Сезонный фактор х V Затраты на рекламу + 3000.

Доход от оборота = Объем сбыта х Цена.

Себестоимость реализованной продукции = Объем сбыта х Себестоимость.

Валовая прибыль = Доход от оборота — Себестоимость реали­зованной продукции.

Накладные расходы =15% дохода от оборота.

Валовые издержки = Затраты на зарплату + Затраты на рекла­му + Накладные расходы.

Прибыль = Валовая прибыль — Валовые издержки.

Коэффициент прибыльности = Прибыль / Доход от оборота.

Сезонный фактор: для I квартала — 0,9; для II — 1,1; для III — 0,8; для IV квартала — 1,2.

Затраты на зарплату: для I квартала — 8000 р.; для II — 8000 р.; для III — 9000 р.; для IV квартала — 9000 р.

Затраты на рекламу для каждого квартала — по 10000 р.

Цена — 40 р.; себестоимость — 25 р.

2. Отформатируйте таблицу: ячейкам, содержащим денежные величины, назначьте денежный формат; ячейкам строки Коэф­фициент прибыльности назначьте процентный формат; расчер­тите таблицу линиями.

3. С помощью программы Поиск решения определите величи­ну затрат на рекламу, обеспечивающую максимальную прибыль в I квартале. Сохраните установки задачи в виде модели. После просмотра результата восстановите исходные значения.

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

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

6. Измените ограничение, установив верхний предел расходов на рекламу за год 50000 р. Сохраните установки задачи в виде мо­дели. После просмотра результата восстановите исходные значе­ния.

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

8. Загрузите каждую модель и сохраните результаты в качестве сценариев. Просмотрите все созданные сценарии.

9. Загрузите каждую модель и создайте отчеты по результатам поиска решения.

10. Восстановите первоначальные значения с помощью перво­го сценария.

 

 

Работа №11

Тема: Работа со списками.

 

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

Теоретические сведения

 

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

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

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

В списке не должно быть пустых строк и столбцов. Это упро­щает идентификацию и выделение списка.

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

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

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

При использовании пункта Условие можно задавать до двух
критериев фильтрации одного столбца, выбирая из списка операторов сравнения и списка значений данного поля те зна­чения, которые необходимы для задаваемого критерия. В слу­чае неточного совпадения значений можно пользоваться под­становочными символами. Завершив установки, нажмите ОК. Список можно сжать еще больше, щелкая на стрелках в других столбцах и выбирая другие элементы.

Библиотека Excel содержит тринадцать встроенных функций списков (баз данных), позволяющих получить информацию из списка или произвести в нем необходимые вычисления. При этом некоторые из этих функций соответствуют уже известным Excel-функциям таблицы (например, БДСУММ(ШСписок; Поле; Кри­терий поиска), БСЧЕТ (Список; Поле; Критерий поиска), ДМАКС(Список; Поле; Критерий поиска) и др.), но являются более гибкими, так как в них можно указывать определенные критерии.

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

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

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

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

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

 

Задание

1. Потренируйтесь выполнять предложенные операции над списками.

2. Создайте сводные таблицы.

3. Выполните упражнение.

Технология работы

1. Создание списка (базы данных)

Для создания списка с помощью формы (маски ввода):

1.1. Сформируйте заглавную строку списка. В каждом столбце
этой строки введите название соответствующего поля записи.

1.2. Щелкните на любой из ячеек заглавной строки и выберите
команду Данные – Форма.

1.3. В открывшемся диалоговом окне, содержащем поля, назва­ние и количество которых соответствует созданным заголовкам столбцов введите данные в каждое поле. Ширина полей соответ­ствует самому широкому столбцу заголовка. Для перехода между полями можно пользоваться указателем мыши, либо клавишами <Таb> — для перехода вниз и <Shift><Tab> — для перехода вверх.

1.4. Нажмите кнопку Добавить для помещения значений данной записи в список и введите следующую запись. Для завершения процесса ввода данных нажмите кнопку За­крыть.

2. Поиск записей в списке

Для того чтобы в большой таблице найти записи, удовлетво­ряющие некоторому условию:

2.1. Установите курсор в любую ячейку списка и выберите ко­манду Данные > Форма. Нажмите кнопку Критерии.

2.2. Введите критерии поиска в одно или несколько полей запи­си. Так, для поиска всех фамилий, начинающихся на букву А, достаточно в поле фамилии набрать А. Для поиска запи­сей с величиной оклада, большей 500000, в поле оклада сле­дует ввести >500000.

2.3. Нажимайте кнопки Назад и Далее, либо кнопки полосы прокрутки для поиска записей, отвечающих установленно­му критерию.

2.4. Для задания нового критерия поиска нажмите кнопку Очи­стить.

3. Редактирование записей с помощью формы

3.1. Для исправления значений (но не формул) в любом поле записи:

3.2. Установите курсор в любую ячейку списка и выберите ко­манду Данные – Форма.

3.3. Найдите требуемую запись с помощью кнопок Назад и Далее. Отредактируйте запись. Нажмите кнопку Закрыть.

4. Удаление записей с помощью формы

Для удаления записи:

4.1. Установите курсор в любую ячейку списка и выберите ко­манду Данные–Форма.

4.2. Найдите требуемую запись и нажмите кнопку Удалить.

4.3. Подтвердите удаление нажав ОК. Нажмите кнопку Закрыть.

5. Добавление записей

При использовании диалогового окна команды Форма вновь создаваемые записи заносятся в конец списка. Для добавления записи внутрь списка:

5.1. Установите курсор в строку, перед которой будет вставлена
новая строка.

5.2. Выберите команду Вставка – Строка и введите в нее соот­ветствующие значения.

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

Выбор элементов списка с помощью автофильтра

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

6.1. Установите курсор в любую ячейку списка, задайте команду
Данные–Фильтр, а затем выберите пункт Автофильтр.

6.2. Нажмите на кнопку со стрелкой в том столбце, по которому
надо фильтровать данные.

6.3. Выберите любой элемент из списка.

6.4. Для восстановления всех записей списка задайте команду Данные – Фильтр –Показать все или же в раскрываю­щемся списке автофильтра выберите пункт Все. Для отмены фильт­рации необходимо повторно выбрать команду Данные – Фильтр – Автофильтр.

Фильтрация списка с использованием сложных критериев

В данном случае должна быть выполнена следующая процедура:

6.5 Создайте область критериев таким образом, чтобы она не ме­шала дополнению и расширению списка. Область критериев представляет собой минимум 2 строки, в первой из которых со­держатся названия полей из заглавной строки списка, а в осталь­ных строках указываются критерии поиска.

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

Если на экран надо вывести записи, удовлетворяющие одно­му из критериев (логическое ИЛИ), то ввод условий произво­дится в разные строки одного столбца.

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

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

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