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

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

Рабочий лист по умолчанию не защищен, поэтому к его ячейкам и объектам можно легко получить доступ для внесения изменений.

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

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

1. Изменить содержимое или формат защищенных ячеек.

2. Переместить защищенную ячейку или объект.

3. Удалить или вставить строки или столбцы.

4. Изменить ширину столбца или высоту строки.

5. Определить имя для диапазона.

6. Создать на рабочем листе диаграмму или графический объект.

Для включения защиты рабочего листа «Отчет» необходимо:

· Выбрать команду Сервис|Защита|Защитить лист.

· В открывшемся диалоговом окне по желанию введите пароль, а все параметры защиты приведите в соответствие с рисунком (рис.27):

Рис.27

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

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

· Выделить блок ячеек шапки таблицы, которые не будут защищены, и выполнить команду Формат|Ячейки|Защита.

· Отключить параметр Защищаемая ячейка и нажать ОК.

· Выбрать команду Сервис|Защита|Защитить лист.

· В открывшемся диалоговом окне по желанию ввести пароль, а все параметры защиты привести в соответствие с рисунком (рис.28).

Рис.28

· Проверить, что в ячейки шапки таблицы можно вносить изменения, а в остальные ячейки рабочего листа – нет.

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

Контрольные вопросы и упражнения к ЛР №7

1. Что такое сводная таблица?

2. Что является источником данных для сводных таблиц?

3. Как вносить изменения исходных данных в сводную таблицу?

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

5. Что такое “Групповые операции” в сводной таблице?

6. Какая команда используется для закрепления верхней и левой частей электронной таблицы?

7. Как скрыть столбец или строку и как отменить их скрытие?

8. Как защитить рабочий лист и как снять защиту?

9. Какие операции невозможно выполнить, защитив рабочий лист?

10. Выполнить следующее упражнение.

Создать в Excel две сводных таблицы, источником данных для которых является таблица (1), находящаяся на рисунке в начале настоящей лабораторной работы:

– первая сводная таблица – такая же, как на рисунке (сумма продаж товаров по кварталам);

– вторая сводная таблица – средняя величина продаж товаров по кварталам.

Защитить листы со сводными таблицами.

Лабораторная работа №8

Содержание работы

Целью настоящей лабораторной работы является освоение информационных технологий Excel, использующих инструменты анализа “Что-если”. Такой анализ представляет собой процесс изменения значений ячеек и анализа влияния этих изменений на результат вычисления формул на листе. Примерами таких задач являются:

· Что произойдет, если увеличить разницу между себестоимостью и продажной ценой или увеличить зарплату сотрудникам на 1%?

· Какой рост необходим, если к 2008 году нужно добиться увеличения объема продаж на 5 млн. рублей?

· Какой получится прирост, если изменится разница между себестоимостью и продажной ценой?

Используя метод “Что-если” и другие прогнозирующие средства Excel, можно ответить на эти и подобные им вопросы.

Средства для анализа данных

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

Ø Создание таблицы подстановки с одной изменяющейся переменной.

Ø Создание таблицы подстановки с двумя изменяющимися переменными.

Ø Проверка результатов с помощью сценариев.

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

Подбор параметра является частью блока задач, который иногда называют инструментами анализа "Что-если". Когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата, можно воспользоваться средством «Подбор параметра», выбрав команду Подбор параметра в меню Сервис. При подборе параметра Microsoft Excel изменяет значение в одной конкретной ячейке до тех пор, пока формула, зависимая от этой ячейки, не возвращает нужный результат.

Например, можно определить

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

или

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

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

В этом окне имеются три необходимых компонента:

· Установить в ячейке

· Значение

· Изменяя значение в ячейке.

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

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

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

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

1. Выберите команду Сервис|параметры… .В появившемся диалоговом окне Параметры щелкните по вкладке Вычисления.

2. Увеличьте число итераций или уменьшите относительную погрешность.

3. Снова запустите команду Подбор параметра.

Пример работы команды Подбор параметра

Необходимо определить, сколько нужно продавать систем караоке, для того, чтобы выручка магазина «Техношок» увеличилась до 300000 у.е., для этого:

· Открыть книгу «Продажи.xls».

· Скопировать содержимое таблицы листа «Техношок» (А1:Н11) на новый лист в ячейку А1 и назвать его «Анализ данных».

· Установить курсор в ячейку Н11 (итог по графе «Выручка») и выбрать пункт меню Сервис|Подбор параметра.

· В диалоговом окне Подбор параметра в опции Значение ввести с клавиатуры 300000.

· В опции Изменяя значение ячейки щелкнуть левой кнопкой мыши в ячейке Е7 (количество продаж систем караоке). Нажать ОК.

Рис.29

· Просмотреть результат решения в текущей таблице и окне Результат подбора параметра (Рис.30). В результате выполнения команды Подбор параметра количество проданных систем караоке стало равным 215.

· Нажать кнопку Отмена; значения данных в ячейках E7 и H11 станут равными исходным значениям (8 и 150960).

Рис.30

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

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

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

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

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

Таблица подстановки с одной изменяющейся переменной

Необходимо создать таблицу подстановки, показывающую изменение величин в строке “Итого” при изменении количества продаж телевизоров. В этой задаче итоги подсчитываются по трем формулам: =СУММ(F3:F10), =F11*20% и =F11+G11. Задача решается следующим образом.

· На листе «Анализ данных»:

в ячейку Е14 ввести формулу =СУММ(F3:F10);

в ячейку F14 ввести формулу =E14*20%;

в ячейку G14 ввести формулу =E14+F14.

· В ячейку D14 ввести с клавиатуры «Телевизор», в ячейку D15 ввести 40, в ячейку D16 – 80, в ячейку D17 – 100 (обратить внимание, что введенные значения количества продаж телевизора располагаются на столбец левее и на строку ниже формулы, по которой определяется выручка магазина).

· Выделить блок ячеек D14:G17, выбрать команду меню Данные|Таблица подстановки.

·

В открывшемся диалоговом окне Таблица подстановки, установить курсор в опции Подставлять значения по строкам, щелкнуть левой кнопкой мыши в ячейке Е10 и нажать ОК(рис.31).

Рис.31

· Оформить полученную таблицу подстановки по приведенному ниже образцу.

Величины, приведенные в строке “ Телевизор”, соответствуют данным в строке “Итого” исходной таблицы, т.е. итоговым суммам при количестве проданных телевизоров, равном 20., а в остальных строках – 40, 80 и 100 проданных телевизоров соответственно.

Меняя значение адресаE10на E3 – E9, можно подсчитать данные в таблице подстановки для остальных товаров.

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

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

· Установить курсор в ячейку Е20 и ввести формулу =СУММ(Н3:Н10).

· В ячейку Е21 ввести с клавиатуры 60, в ячейку Е22 ввести 80, в ячейку Е23 – 100, в ячейку F20 – 50, в G20 – 70, в Н20 - 90 (обратить внимание, что введенные значения количества продаж видеомагнитофона располагаются в ячейках столбца Е непосредственно под формулой, по которой определяется выручка магазина, а значения количества продаж видеокамеры – в ячейках строки справа от формулы).

· Выделить блок ячеек Е20:Н23, выбрать команду меню Данные|Таблица подстановки.

· В открывшемся диалоговом окне Таблица подстановки, установив курсор в опции Подставлять значения по столбцам, щелкнуть в ячейке Е9, а в опции Подставлять значения по строкам щелкнуть левой кнопкой мыши в ячейке Е3 и нажать ОК(рис.32).

Рис.32

· Оформить полученную таблицу подстановки по приведенному ниже образцу.

Таблица подстановки с двумя переменными

		Количество видеокамер
Количество видеомагнитофонов

Приведенная в таблице подстановки величина 150960 есть итоговая сумма выручки от продажи всех товаров при 30 проданных видеомагнитофонах и 40 видеокамерах, а 176940 – при 60 видеомагнитофонах и 50 видеокамерах.