Если в диалоговом окне Защита книги установлен флажок Структуру, то запрещается:

• просмотр скрытых листов;

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

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

• перемещение или копирование листов в другую книгу;

• создание для сценариев краткого отчета с помощью Диспетчера сценариев;

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

• запись новых макросов и др.;

Если установлен флажок Окна, то запрещается:

• изменение размеров и положения окон открытой книги;

• перемещение, изменение размеров и закрытие окон и др.

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

Команда СЕРВИС – Защита – Защитить книгу и дать общий доступ используется для защиты элементов рабочей книги при работе с ней нескольких пользователей.

Команда ОКНО – Скрыть позволяет скрыть от пользователя всю книгу, но оставить доступ к её содержанию. Кроме того, в Excel имеется возможность заблокировать отдельные или все связи или ссылки в рабочей книге.

Защита рабочих листов. Защиту отдельных рабочих листов можно установить с помощью команды СЕРВИС – Защита – Защитить лист (рис. 17). При этом файл рабочей книги можно свободно открывать, а в незащищенные листы вносить изменения.

рис.17

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

• содержимого ячеек;

• графических объектов – для запрета их перемещения и редактирования;

• сценариев.

Если в диалоговом окне Защитить лист установлен флажок Содержимое, то запрещаются:

• изменение защищаемых ячеек;

• просмотр скрытых перед защитой листа строк и столбцов;

• просмотр скрытых перед защитой листа строк и столбцов;

• просмотр скрытых перед защитой листа формул;

• изменение элементов на листах диаграмм.

Если установлен флажок Сценарии, то запрещаются:

• просмотр скрытых сценариев;

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

Для того чтобы скрыть рабочий лист или его отдельные части (строки или столбцы), используют команды ФОРМАТ – Лист (Строка, Столбец) – Скрыть. Для их отображения – команды ФОРМАТ – Лист (Строка, Столбец) – Отобразить.

Защита ячеек. Для защиты отдельных ячеек используется команда меню ФОРМАТ – Ячейки. (рис.18)

рис. 18

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

Средства автоматического обобщения и анализа данных электронной таблицы

Excel предоставляет разнообразные способы для автоматического обобщения и анализа данных:

1. Автоматические вычисления;

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

3. Средства автоматического подведения общих и промежуточных итогов;

4. Возможность создания и использования структуры таблицы;

5. Средства для обобщения данных;

6. Возможность создания и модификации сводных таблиц, отчетов и диаграмм;

7. Средства условного анализа (анализа “что, если”), к которым относятся:

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

   • Надстройка Поиск решения;

   • Сценарии;

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

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

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

• каждая строка списка рассматривается как запись базы данных;

• столбцы списков считаются полями базы данных;

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

• все строки таблицы должны содержать однородную информацию – во всех строках в одинаковых столбцах должны находиться однотипные данные;

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

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

Сортировка данных. Сортировка осуществляется непосредствен в электронной таблице. Строки, столбцы и ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Списки можно сортировать в возрастающем порядке (от 1 до 9, от А до Я) или в убывающем порядке (от 9 до 1, от Я до А). Сортировать можно как текстовые, так и числовые данные. Если в сортируемом столбце содержатся и текстовые и числовые данные, то после сортировки числовые значения будут расположены перед текстом. Сортировка данных в электронных таблицах производятся с помощью команд меню ДАННЫЕ – Сортировка или с помощью кнопок [А^Я↑], [Я^А↓] на Стандартной панели инструментов.

В диалоговом окне Сортировка диапазона указывают требуемые параметры – тип и порядок сортировки (рис.19)

рис. 19

☺ Перейдем к работе со списками. Полями нашей базы данных будут:

• Категория – вид продаваемой продукции;

• Товар – название товара;

• Модель – Модель товара;

• Цена (нал) – стоимость товара при наличном расчете;

• Цена (б\н)стоимость товара при безналичном расчете.

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

Автозавершение.

1. На листе Список товаров выделите диапазон С9:G9.

2. Последовательно в каждую ячейку этого диапазона вводите данные:

Компьютер, Персональный, Gamer’s Dream, 1200, =F9+F9*$H$5.

1. Выделите диапазон С10:F10.

2. Введите K. Excel предложит закончить слово «Компьютер». Позвольте это программе, нажав клавишу Enter.

3. Введите П. Excel предложит закончить ввод словом «Персональный». Нажмите клавишу Enter.

4. В оставшиеся ячейки введите Master Class, 1450.

5. Выделите диапазон С11:F11.

6. Последовательно в каждую ячейку этого диапазона вводите данные: Периферия, Принтер, Epson LQ, 123

7. Выделите диапазон С12:F12.

8. Введите П. Excel предложит закончить ввод словом «Периферия». Нажмите клавишу Enter.

9. Аналогично введите «Принтер».

10. Закончите ввод записи, заполнив оставшиеся ячейки значениями Lexmark 5700, 279.

Попробуем, что же такое выбор из списка.

1. Выделим диапазон С13:F13.

2. Нажмите сочетание клавиш Alt + Стрелка вниз. В раскрывшемся списке выделите пункт Периферия и нажмите клавишу Enter. Нажмите еще раз клавишу Enter, чтобы перейти к нижней ячейке.

3. Щелкните правой кнопкой мыши на ячейке D13. В появившемся окне меню выберите команду Выбрать из списка и в появившемся списке выберите пункт Принтер. Нажмите клавишу Enter, чтобы перейти к следующей ячейке.

4. Нажмите комбинацию клавиш Alt + Стрелка вниз. В раскрывшемся списке щелкните на пункте Epson LQ100.

5. Щелкните в строке формул и замените LQ100 на LX300. Нажмите Enter и перейдите к следующей ячейке.

6. Введите 137.

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

1. Введите еще 10 записей, используя возможности Автозавершения и выбора из списка:

Компьютер, Сервер, WorkMem, 2556;

Компьютер, Сервер, BigBlue, 6521;

Периферия, Факс-Модем, USR 33600 int, 58;

Периферия, Факс-Модем, USR 33600 ext, 68;

Периферия, Клавиатура, Genius, 9;

Периферия, Клавиатура, ВТС 5739, 25;

Периферия, Сканер, Acer 610 PT, 195;

Комплектующие, Винчестер, 3.2Гб IDE Seagate, 128;

Комплектующие, Винчестер, 6.4Гб IDE Quantum FB_EX, 161;

Комплектующие, Винчестер, 9.1Гб UW SCSI_IBM, 499.

Cкопируйте формулу из ячейки G9,заполнив поле Цена (б\н) для всех записей. Теперь рабочий лист готов (см. рис. 20).

рис.20

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

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

1. установить курсор внутри таблицы;

2. ввести команду меню ДАННЫЕ – Фильтр – Автофильтр;

3. щелчком мыши по кнопке со стрелкой [▼] раскрыть список столбца, по которому будет производится выборка;

4. указать требуемые значения или выбрать строку ”условие” и задать критерии выборки в диалоговом окне Пользовательский автофильтр.

рис. 21 использование автофильтра для отбора записей по признаку «М» -(мужчина)

рис. 22 рабочий лист после фильтрации списка по признаку «мужчина»

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

• критериев сравнения;

• вычисляемых критериев.

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

Для восстановления всех строк исходной таблицы и отмены режима фильтрации следует ввести команду меню ДАННЫЕ – Фильтр – Отобразить всё.

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

В Excel можно создавать до 8 уровней структуры. Для каждого рабочего листа можно создать только одну структуру.

Структура таблицы создается автоматически после выполнения таких команд меню ДАННЫЕ, как Итоги, Консолидация, Сводная таблица.

Структурировать таблицу можно и самостоятельно с помощью команд меню ДАННЫЕ – Группа и структура.

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

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

В Excel имеется несколько способов консолидации данных

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

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

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

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

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

Анализ и обобщение данных с помощью сводных таблиц и сводных диаграмм.

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