Материал: ЭКСЕЛЬ2 2013 Методичка Расширенные возможности

81

Microsoft Excel 2013. Уровень 2. Расширенные возможности

По данным таблицы на листе КОМПЛЕКТЫ, построить отчет для анализа продаж Группы компаний: Нирвана, Перспектива, Рапсодия, Фаворит по годам. Отчет разместить на новом листе, который переименовать в АНАЛИЗПРОДАЖ.

Сводные диаграммы

На листе АНАЛИЗБРАКА переместить поле Производитель в область фильтр отчета.

Построить по данным отчета диаграмму – Гистограмма с группировкой.

Отобразить только производителя Tefalka и Braunok.

Изменить вид диаграммы на Гистограмма с накоплением.

Очистить фильтры.

Построение сводных таблиц c использованием модели данных

Исходный диапазон данных на листе БЫТОВАЯ ТЕХНИКА преобразовать в таблицу. Изменить имя таблицы на БытоваяТехника.

Построить на новом листе сводную таблицу с добавлением данных в модель данных: рассчитать по каждому наименованию количество и стоимость партии. Переименовать лист в

ОТЧЕТПРОДАЖИ.

Просмотреть для Кофеварки детализацию значений по Поставщикам.

Добавить в Значения еще раз поле Количество, шт. Подсчитать сколько уникальных поставок Кофеварок у каждого Поставщика по количеству в партии.

 Сохранить изменения в файле и закрыть его.

САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ

Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 16-1, 16-2, 16-3 и 16-4.

82

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Модуль 4. ОСОБЕННОСТИ СОВМЕСТНОЙ РАБОТЫ

Защита ячеек, листов и рабочих книг Excel

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

Защита ячеек листа

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

Защита проводится в два действия: сперва происходит настройка атрибутов ячеек, а затем устанавливается защита ячеек листа.

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

1.Настройка атрибутов ячеек.

Выделить ячейки.

Щелкнуть правой кнопкой мыши по выделению и выбрать Формат ячеек [Format cells]

или нажать клавиши Ctrl + 1 .

Открыть вкладку Защита [Protection].

Снять атрибут Защищаемая ячейка [Locked] с ячеек, которые после установки защиты листа должны изменяться.

Установить атрибут Скрыть формулы [Hidden] для ячеек, где нужно скрыть истинное содержимое ячеек, которое отображается в строке формул. Атрибут Защищаемая ячейка [Locked] должен быть установлен.

83

Microsoft Excel 2013. Уровень 2. Расширенные возможности

2.Установка защиты ячеек листа.

На вкладке Рецензирование [Review], в группе Изменения [Changes], выбрать

Защитить лист [Protect Sheet]

Флажок Защитить лист и содержимое защищаемых ячеек [Protect worksheet and contents of locked cells] должен быть установлен.

В списке Разрешить всем пользователям этого листа [Allow all users of this worksheet to] отметить разрешения на действия с защищенными ячейками.

Установить пароль в поле Пароль для отключения защиты листа [Password to unprotect sheet], иначе защита легко будет снята другим пользователем.

Пароль может содержать только буквы a-z, цифры 0-9 и специальные знаки, например

!, @, #, $, %, ^, &, *, (, ), +, =

ОК.

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

Для снятия защиты достаточно на вкладке Рецензирование [Review], в группе

Изменения [Changes], выбрать Снять защиту листа [Unprotect Sheet].

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

ПРАКТИКУМ:

Открыть файл 04_1 Защита.

На листе ЗАДАНИЕ1 выполнить соответствующие задания.

Сохранить сделанные изменения.

Выборочная защита диапазонов для разных пользователей

Если с файлом работают несколько пользователем, при этом каждый может менять только свою область листа, то можно сделать выборочную защиту диапазонов с уникальным паролем, действующим на разные диапазоны ячеек. Такой тип защиты подразумевает, что у всех защищаемых ячеек установлен атрибут Защищаемая ячейка [Locked].

Для установки такой защиты надо:

1. На вкладке Рецензирование [Review], в группе Изменения [Changes], выбрать Разрешить изменение диапазонов [Allow Users to Edit Ranges].

2. В появившемся окне нажать кнопку Создать [New].

85

Microsoft Excel 2013. Уровень 2. Расширенные возможности

4. ОК.

Для отмены защиты на вкладке Рецензирование [Review], в группе Изменения

[Changes], отжать кнопку Защитить книгу [Protect Workbook].

ПРАКТИКУМ:

В открытом файле 04_1 Защита, на листе ЗАЩИТА3 выполнить соответствующие задания.

Сохранить изменения в файле.

Защита файла от открытия

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

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

1.Выбрать Файл [File], Сохранить как [Save As], Текущая папка [Current Folder] или нажать клавишу F12 .

2.В окне сохранения нажать кнопку Сервис [Tools],

затем Общие параметры [General Options].

3.Ввести Пароль для открытия файла [Password to open], OK.

4.Повторить пароль, OK.

5.Нажать кнопку Сохранить [Save] и подтвердить замену существующего файла.

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

вместо ввода пароля происходит его удаление из соответствующего поля.

ПРАКТИКУМ:

В открытом файле 04_1 Защита, на листе ЗАЩИТА4 выполнить соответствующие задания.

Сохранить изменения в файле и закрыть.

САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ

Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 17.