Табличный процессор Excel 2007

ТАБЛИЧНЫЙ ПРОЦЕССОР EXCEL 2007

Тема 1. ЭЛЕКТРОННАЯ ТАБЛИЦА И ЕЕ КОМПОНЕНТЫ.

ОСНОВНЫЕ ФУНКЦИОНАЛЬНЫЕ ВОЗМОЖНОСТИ EXCEL 2007

К основным возможностям Excel относятся:

· создание документов, содержащих ЭТ, их редактирование и печать;

· выполнение математических расчетов с использованием  арифметических и логических операций, а также встроенных функций;

· связывание ЭТ при помощи ссылок;

· консолидация ЭТ и их частей;

· построение графиков и диаграмм;

· работа со списками (базами данных) ЭТ;

· создание сводных таблиц;

· решение оптимизационных задач;

· имитационное моделирование и анализ сценариев;

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

· создание макросов для автоматизации часто повторяющихся операций;

· разработка приложений пользователя на языке программирования Visual Basic.

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

 

ВЫЗОВ И ЗАВЕРШЕНИЕ РАБОТЫ EXCEL

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

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

Для завершения работы Excel следует дать команду   кнопка "Office" - Закрыть или закрыть его окно любым другим способом. Если измененная электронная таблица не была предварительно записана в файл, на экране появится диалоговое окно с сообщением «Сохранить изменения в файле?» и кнопками Да, Нет, Отмена. Первая из них используется для записи изменений в файл и завершения работы Excel. Вторая - для завершения работы Excel без записи изменений. Третья - для отказа от выхода из табличного процесссора.

 

ОКНО EXCEL

Окно Excel состоит из следующих компонентов:

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

· меню, содержащее команды Excel;

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

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

· рабочее поле - фрагмент текущего рабочего листа с ячейками ЭТ, ограниченный слева и сверху рамками с нанесенными на них номерами строк и именами столбцов;

· ярлыки рабочих листов ЭТ;

· строка состояния для отображения информации о текущем состоянии (режиме работы) табличного процессора;

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

 

ТЕХНОЛОГИЯ РАБОТЫ В EXCEL

Работа пользователя с ЭТ осуществляется в соответствии с поставленной задачей и состоит из следующих основных этапов:

• создание ЭТ;

• графическое представление данных;

• оформление и печать выходных данных.

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

 

ОСНОВНЫЕ РЕЖИМЫ РАБОТЫ EXCEL

Основными режимами работы Excel являются: режим готовности, режим ввода данных, командный режим, режим редактирования.

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

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

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

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

 

КОНТРОЛЬНЫЕ ВОПРОСЫ

1.Что такое электронная таблица (ЭТ)?

2.Раскройте понятия: строка, столбец, ячейка, блок ячеек.

3.Что такое адрес ячейки?

4.Какая ячейка называется текущей (активной)?

5.Чем абсолютная адресация отличается от относительной?

6.Что может быть содержимым ячейки ЭТ?

7. Каковы правила записи формул?

8.С какими основными объектами работает Excel?

9.Назовите функциональные возможности Excel.

10. Какова структура окна Excel?

 

ЗАДАНИЯ

1.Вызвать Excel.

2.Изучить структуру окна программы Excel.

3.Ознакомиться со справочной системой Excel.

4.Завершить работу Excel.

 

Тема 2. СОЗДАНИЕ, РЕДАКТИРОВАНИЕ, ОФОРМЛЕНИЕ И ПЕЧАТЬ ТАБЛИЦ

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

ПОСЛЕДОВАТЕЛЬНОСТЬ СОЗДАНИЯ ЭТ

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

· ввод названия ЭТ;

· ввод заголовков столбцов (шапки) и заголовков строк (боковика) ЭТ;

· ввод исходных данных;

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

· форматирование данных в ячейках ЭТ;

· обрамление ЭТ;

· запись ЭТ в файл.

 

ОПЕРАЦИИ С ФАЙЛАМИ

Для записи ЭТ в файл используются команды  - Сохра нить или  - Сохранить как. При этом имени файла автоматически присваивается расширение xlsx.

Для загрузки файла предназначена команда  - Открыть.

 

КОПИРОВАНИЕ ФОРМУЛ

Для копирования формулы следует:

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

• скопировать содержимое этой ячейки в буфер обмена с помощью кнопки  на панели инструментов Главная или [Ctrl]+[Ins];

• выделить интервал ячеек, в который должна быть помещена формула;

• извлечь данные из буфера обмена посредством кнопки Вставить на панели инструментов Главная или [Shift]+[Ins].

 

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Какова последовательность создания ЭТ?

2. Как очистить текущую ячейку ЭТ?

3. Как осуществить ввод формулы в ячейку ЭТ?

4. Каким образом выполняется копирование формулы?

5. Как исправить содержимое ячейки?

6. Как осуществить форматирование числовых данных?

7. Как удалить строки (столбцы) ЭТ?

8. Как выполнить выравнивание данных в ячейках ЭТ?

9. Как выполнить центрирование текста относительно нескольких столбцов ЭТ?

10. Как выполнить обрамление ЭТ?

11. Как вывести ЭТ на печать?

12. Как записать ЭТ в файл?

 

ЗАДАНИЯ

 

1. Вызвать Excel.

2. Ввести приведенную ниже таблицу (табл. 1) в следующем порядке:

· ввести название таблицы;

· предусмотреть достаточное количество строк для ввода «шапки» таблицы (Главная - Формат - Формат ячеек - вкладка Выравнивание - флажок Объединение ячеек - Переносить по словам). Названия столбцов таблицы отцентрировать;

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

· ввести исходные данные в таблицу. Данные столбца «Шифр продукции» выровнять по центру.

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

4.Данные столбца «в % к итогу» отформатировать с точностью до 0,1.

5.Выполнить обрамление таблицы.

6.Записать ЭТ в файл с именем пользователя в папку Лабораторные.

7.Выйти из Excel.

Производство продукции

 

Шифр продукции	Цена 1 т, руб	Произведено продукции
		1 кв., т	2 кв., т	3 кв., т	4 кв., т	Всего за год
						т	В % к итогу
101	650	250	310	280	0
102	510	390	480	500	280
103	320	860	780	480	640
201	185	450	480	600	500
202	600	120	180	240	310
203	550	350	430	480	220
301	300	910	820	580	700
302	500	210	150	240	320
303	380	180	120	110	140
Итого:							100,0

 

Тема 3. РАБОТА С НЕСКОЛЬКИМИ ВЗАИМОСВЯЗАННЫМИ ТАБЛИЦАМИ

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

 

СОЗДАНИЕ СВЯЗАННЫХ ТАБЛИЦ

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

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

 

КОНСОЛИДАЦИЯ ДАННЫХ

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

Для выполнения консолидации следует:

• выделить область назначения;

• задать Данные - Консолидация ;

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

• в списке Функция: выбрать нужную функцию;

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

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

КОНТРОЛЬНЫЕ ВОПРОСЫ

1.Что такое рабочий лист?

2.Как перейти с одного листа на другой?

3.Как создать новый лист?

4.Какие функции могут использоваться при консолидации данных?

5.Какие ограничения рекомендуется соблюдать при назначении имен листов?

6.Каковы правила адресации данных на другом листе?

7.Как адресуется интервал, охватывающий данные нескольких листов?

8.Как обеспечить абсолютную адресацию ячейки на другом листе?

ЗАДАНИЯ

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

2.Скопировать введенную таблицу на листы Лист2, Лист3.

3.На листе Лист2 заменить данные по выпуску продукции первым цехом на данные по выпуску продукции вторым цехом, на листе Лист3 - на данные по выпуску продукции третьим цехом (исходные данные приведены ниже, табл. 2). Отредактировать названия таблиц.

4. Переименовать листы, присвоив листу Лист1 -  имя Цех1, листу Лист2 - имя Цех2, листу Лист3 - имя Цех3.

 

 

Исходные данные

 

Шифр продукции	Получено продукции, т
	1 кв.		2 кв.		3 кв.		4 кв.
	Цех 2	Цех 3	Цех 2	Цех 3	Цех 2	Цех 3	Цех 2	Цех 3
0101	450	0	810	0	290	0	500	0
0102	870	50	1260	70	590	20	780	80
0103	2050	400	1600	860	2200	760	2900	990
020,	400	780	460	330	550	320	790	890
0202	840	0	920	0	790	60	680	60
0203	620	90	580	110	120	80	590	80
0301	430	120	360	95	120	160	450	160
0302	150	145	120	130	140	260	130	185
0303	125	140	80	170	145	120	130	160

5.Создать новый лист Лист4. Скопировать исходную таблицу на лист Лист4. Рассчитать количество полученной продукции на предприятии в целом, используя Консолидацию данных (Данные - Консолидация ); для этого в списке диапазонов консолидации  использовать ссылки на данные листов: Цех1, Цех2, Цех3. Название таблицы должно быть: «Производство продукции (в целом по предприятию)».

6.Переименовать лист Лист4, присвоив ему имя Предприятие.

7.Создать новый лист Лист5. Скопировать на Лист5 таблицу «Производство продукции (в целом по предприятию)».

8.Преобразовать таблицу, рассчитав стоимость продукции, полученной на предприятии по кварталам и всего за год. В формулах использовать ссылки на данные листа Предприятие. Ввести новое название таблицы: «Стоимость продукции (в целом по предприятию)». Изменить названия столбцов таблицы.

9.Переименовать лист Лист5, присвоив ему имя Выручка.

10.Создать новый лист Лист6. На листе Лист6 создать следующую таблицу (табл. 3.).

 

 

Тема 4. ПОСТРОЕНИЕ ДИАГРАММ

Цель работы: научиться использовать возможности Excel 2007 по графическому представлению числовых данных.

 

ТИПЫ ДИАГРАММ. ПОДГОТОВКА ДАННЫХ

 

Excel 2007 обеспечивает широкие возможности графического представления числовых данных. Основными типами диаграмм являются:

· Гистограммы;

· Линейчатые диаграммы;

· Графики;

· Круговые диаграммы;

· Точечные диаграммы;

· Диаграммы с областями;

· Кольцевые диаграммы.

Каждому типу диаграмм соответствует несколько видов (форматов) диаграмм: плоскостные, объемные, составные и т. д.

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

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

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

 

КОРРЕКТИРОВКА ДИАГРАММ

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

• изменение размеров диаграммы (для этого нужно оттранспортировать один из восьми маркеров изменения размера, расположенный на рамке диаграммы, в требуемом направлении);

• изменение размеров элемента диаграммы (выделить элемент диаграммы посредством щелчка левой кнопкой мыши, далее действовать так же, как при изменении размера диаграммы);

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

• удаление элемента диаграммы (выделить элемент диаграммы посредством щелчка левой кнопкой мыши, затем нажать [Del]);

• добавление новых элементов (Диаграмма—Параметры диаг раммы);

•  изменение типа диаграммы (Диаграмма—Тип диаграммы);

• перемещение диаграммы по листу (оттранспортировать диаграмму при нажатой левой кнопки мыши);

• удаление диаграммы ([Del]).

Примечание. Если на диаграмме выделен некоторый элемент, то перейти к другому элементу можно посредством клавиш управления курсором.

Для корректировки диаграммы, построенной на отдельном листе, нужно перейти на этот лист. Чтобы удалить диаграмму, расположенную на отдельном листе, удаляют сам лист.

 

КОНТРОЛЬНЫЕ ВОПРОСЫ

1.Какие основные типы диаграмм используются в Excel?

2.Как задать исходные данные для построения диаграммы?

3.Как разместить диаграмму на отдельном листе?

4.Как указать тип и вид (формат) диаграммы?

5.Что представляет собой легенда на диаграмме?

6.Как разместить легенду на диаграмме?

7.Каким образом вводится название диаграммы?

8.Каковы возможности форматирования элементов диаграммы?

9.Как удалить диаграмму?

10.  Как изменить место размещения диаграммы?

 

ЗАДАНИЯ

1.Вызвать Excel.

2.Открыть файл с ЭТ, созданной при выполнении предыдущих практических заданий.

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

Для построения диаграммы выполнить следующую последовательность действий:

• перейти на лист Предприятие;

• выделить в таблице «Производство продукции (в целом по предприятию)» два интервала ячеек (чтобы выделить несколько несмежных интервалов ячеек, надо при выделении интервалов удерживать нажатой [Ctrl]). Первый интервал ячеек должен включать ячейки столбца «Всего за год, т»; второй - ячейки столбца «Шифр продукции»;

• нажать кнопку  на панели инструментов Вставка;

• выбрать тип диаграммы — Гистограмма (№1)и ее вид - обычный; нажать кнопку Далее;

• установить переключатель Ряды в на вкладке Диапазон данных: в положение - столбцах; нажать кнопку Далее;

• выбрать вкладку Ряд. В поле Подписи оси Х указать интервал ячеек с шифрами продукции (заголовок не выделять);

• В окне Ряд удалить наименование «Шифр продукции» (для этого необходимо нажать кнопку Удалить); нажать кнопку Далее;

• ввести заголовки диаграммы: Название диаграммы - Производство всех видов продукции за год и подписи осей: Ось X (категорий):- шифры продукции, Ось Y (значений): - тонны;

• выбрать вкладку Легенда;

• выключить переключатель Добавить легенду; нажать кнопку Далее;

• установить переключатель Поместить диаграмму на листе: в положение — имеющемся; нажать кнопку Готово (Рис.1).

 

Рис. 1 Производство всех видов продукции за год

4. Построить столбиковую диаграмму (гистограмму с накоплением) № 2, характеризующую производство трех первых видов продукции (шифры 0101-0103) в целом по предприятию по кварталам. Предусмотреть все необходимые надписи на диаграмме. Диаграмму разместить на отдельном листе. Для этого:

• выделить в таблице «Производство продукции (в целом по предприятию)» четыре интервала ячеек (по три ячейки в столбцах: «1 кв., т», «2 кв., т,», «3 кв., т», «4 кв., т», при этом сами заголовки не выделять).

• нажать кнопку  на панели инструментов Стандартная;

• выбрать тип диаграммы — Гистограмма и ее вид (формат) –с накоплением; нажать кнопку Далее;

• установить переключатель Ряды в: в положение - столбцах;

• выбрать вкладку Ряд;

• ввести в поле Имя: имена рядов данных: имя ряда 1 – Квартал1, ряда 2 — Квартал2, ряда 3 — Квартал3, ряда 4 — Квартал4.(использовать кнопку Добавить. Удалить лишние ряды с помощью кнопки Удалить);

• в поле Подписи оси X: указать интервал ячеек с шифрами продукции; нажать кнопку Далее;

• ввести заголовки диаграммы (вкладка Заголовки): Название диаграммы - Производство трех первых видов продукции по кварталам и подписи осей: Ось X(категорий): - шифры продукции, Ось Y(значений): -тонны;

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

• установить переключатель Поместить диаграмму на листе:

в положение - отдельном и нажать Готово. (Рис.2).

5. Построить (самостоятельно) объемную столбиковую диаграмму (гистограмму) № 4, характеризующую поквартальное производство трех последних видов продукции в целом по предприятию. Диаграмму разместить на отдельном листе.

Рис. 3 Производство последних трех видов продукции

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

 

• щелкнуть левой кнопкой мыши в области построения диаграммы;

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

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

• достигнув нужного расположения параллелепипеда, отпустить левую кнопку мыши.

6. Построить (самостоятельно) объемную круговую диаграмму № 2, характеризующую структуру стоимости продукции, произведенной в целом на предприятии за год (столбцы Шифр продукции и Цена 1 т. Руб.). Предусмотреть все необходимые надписи на диаграмме, в том числе подписи данных. На втором

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

7. Построить (самостоятельно) кольцевую диаграмму, характеризующую структуру производства продукции в цехе 1 по видам продукции за 1, 2, 3 и 4 кварталы. Диаграмму разместить на листе, содержащем данные по цеху 1.

8. Построить самостоятельно график зависимости цены и произведенной продукции по предприятию в целом (Рис. 5). Для построения использовать столбцы Цена и Всего т. За год в целом по предприятию (вкладка Нестандартные – График 2 оси).

9. Выйти из Excel, предусмотрев запись ЭТ в файл.

 

СОРТИРОВКА ДАННЫХ

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

Чтобы отсортировать список, следует:

· выделить область списка. Для этого достаточно указать одну ячейку списка. После подачи команды сортировки Excel автоматически выделит весь список. Однако в этом случае итоговая строка списка (если она не отделена от списка пустой строкой) также будет включена в сортировку;

· задать Данные - Сортировка;

· в диалоговом окне Сортировка в группе Сортиро вать по определить ключ сортировки (Ключом сортировки является столбец, по которому Excel должен отсортировать данные) и указать направление сортировки: по возрастанию или по убыванию;

· Установить флажок Мои данные содержат заголовки;

·

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

Для выполнения быстрой сортировки (по одному ключу) используются кнопки на панели инструментов Стандартная: -по возрастанию и  - по убыванию. Ключом сортировки в этом случае является столбец с текущей ячейкой.

Excel дает возможность проводить сортировку по нескольким ключам. Многоуровневая сортировка выполняется в том случае, если в списке содержатся данные с одинаковым значением первого ключа.

Для выполнения многоуровневой сортировки следует в диалоговом окне Сортировка диапазона в группе Затем по указать второй, а в группе В последнюю очередь, по - третий ключ сортировки.

 

 

ОТБОР ЗАПИСЕЙ ИЗ СПИСКА

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

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

Чтобы установить автофильтр, следует:

• выделить область БД;

• задать Данные – Фильтр.

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

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

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

Можно задать соединение нескольких критериев фильтрации, объединив их по И (запись появится в выходном списке тогда, когда будут выполнены все заданные условия) или по ИЛИ (в выходном списке запись появится, если будет выполнено хотя бы одно условие). В критериях можно задавать шаблоны, содержащие символы * и?.

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

Предпосылкой использования расширенного фильтра является наличие на рабочем листе области БД и области критериев.

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

Число строк в области критериев определяется количеством критериев. Если условия поиска заданы в области критериев в одной строке, то эти условия связаны оператором И. Если же условия поиска заданы в разных строках, то они связаны оператором ИЛИ. Включение пустых строк в область критериев недопустимо, так как в этом случае будут отобраны все записи БД.

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

• выделить область БД;

• задать Данные – Сортировка и фильтр - Дополнительно;

• в диалоговом окне Расширенный фильтр в поле Исходныйди апазон: указать интервал ячеек, содержащий область БД;

• поле Диапазон условий: указать интервал ячеек, содержащий область критериев;

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

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

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

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

 

КОНТРОЛЬНЫЕ ВОПРОСЫ

1.Что представляет собой БД в Excel?

2.Какими средствами обработки списков обладает Excel?

3.Как преобразовать таблицу Excel в список?

4.Для каких целей используются формы данных?

5.Как выполнить многоуровневую сортировку?

6.Какие существуют способы фильтрации данных?

7.Чем автофильтр отличается от расширенного фильтра?

8.Как выполнить фильтрацию данных с помощью автофильтра?

9.Какова последовательность работы с расширенным фильтром?

10.В каких случаях и каким образом подсчитываются промежуточные итоги?

 

ЗАДАНИЯ

1. Вызвать Excel для работы с ЭТ, созданной при выполнении предыдущих практических заданий.

2. Создать новый лист Лист 7. Присвоить ему имя База данных.

3. На листе База данных создать БД, аналогичную приведенной ниже (табл. 4).

 

База данных

Номер цеха	Шифр продукции	Цена 1 т, руб	1 кв.т	2 кв.т	3 кв.т	4 кв.т	Всего за год, т
1
…
2
…
3
…

 

При заполнении полей: «Шифр продукции», «Цена 1 т, руб.», «1 кв., т», «2 кв., т», «3 кв., т», «4 кв., т)», «Всего за год, т» использовать ссылки на данные листов: Цех1, Цех2, ЦехЗ.

4. Скопировать исходную Базу данных на новый лист. Назвать этот лист Сортировка по цене. Выполнить сортировку БД по полю «Цена 1 т, руб.» в порядке убывания(Выделить одну ячейку в столбце «Цена 1 т, руб.», Главная – Редактирование – Сортировка и фильтр – задать тип сортировки).

5.Скопировать исходную Базу данных на новый лист. Назвать этот лист Многоуровневая сортировка. Выполнить многоуровневую сортировку, где в качестве первого ключа указать номер цеха, второго - цену 1 т продукции. Для этого выделите всю таблицу с заголовком - Главная – Редактирование – Сортировка и фильтр – Настраиваемая сортировка – в поле Сортировать по выбрать Номер цеха – Добавить уровень - в поле Затем по выбрать Цена 1 т., руб

6. Скопировать исходную Базу данных на новый лист. Используя автофильтр, отобрать записи по цеху 2 (Данные – Фильтр).

7.  Скопируйте исходную базу данных на новый лист. Назовите его Пользовательский автофильтр. С помощью автофильтра задать пользовательские критерии для отбора всех продуктов по цеху 1, цена которых больше 300 или меньше 600 руб. т. Для этого: