Защита книги с помощью пароля

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

1. Перейдите в группу Файл в строке меню

2. В левом столбце выберите пункт Сведения

3. В появившемся в центральном столбце меню выберите команду Защитить книгу / Зашифровать паролем

4. Введите Ваш пароль. При просьбе подтверждения пароля введите тот же самый пароль, что и в первый раз.

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

1. Перейдите в группу Файл в строке меню

2. Выберите команду Сохранить как …

3. Внизу появившегося окна выберите в меню Сервис пункт Общие параметры

4. В появившемся диалоговом окне введите Ваш пароль в поле пароль для изменения:

5. Подтвердите пароль.

Задания для самостоятельной работы:

1. Создайте шаблон Счета-фактуры. Предусмотрите наличие соответствующих формул.

2. С помощью шаблона создайте счет-фактуру. Защитите ее паролем.

Восьмой урок

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

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

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

 

1. Создайте документ Microsoft Excel.

2. Выделите ячейки A1:C1 и выполните команду Главная/ (Выравнивание) Объединить и поместить в центре. Введите заголовок таблицы «Зарплата ландшафтного дизайнера».

3. В ячейку B3 введите заголовок «Тариф» и выполните команду Рецензирование/ (Примечание) Создать примечание для создания примечания «За озеленение 1 кв. м.»

 

4. В ячейку A4 введите заголовок «Кол-во выполненной работы (кв м)» и в ячейку В6 введите заголовок «Зарплата».

5. В ячейку С3 введите значение «2». Выберите пункт меню Главная,в группе Число откройте выпадающий список Финансовый числовой формат и откройте Другие финансовые форматы.

В открывшемся диалоговом окне выберите числовой формат Денежный и в обозначение выберите $ английский (США). И нажмите OK.

6. В ячейку C4 введите значение «1». Выберите пункт меню Главная,в группе Число откройте выпадающий список Финансовый числовой формат и откройте Другие финансовые форматы.

7. В ячейку А17 введите заголовок «Курс доллара». В ячейку С17 введите курс доллара. Выберите пункт меню Главная в группе Число откройте выпадающий список Финансовый числовой формат и откройте Другие финансовые форматы. В открывшемся диалоговом окне выберите числовой формат Денежный и в обозначение выберите р. И нажмите OK.

8. В ячейку C6 введите формулу =C3*C4*C17.

9. Выделите ячейки C6:C15 и как в пункте 6 установите обозначение р.

10. В ячейки B7:B15 введите значения.

 

Переходим к созданию таблицы подстановки.

Выделите диапазон ячеек B6:C15.

 

1. Выполните команду Данные/ (Работа с данными) Анализ «что-если»/ Таблица данных. На экране откроется диалоговое окно Таблица данных.

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

2. В поле Подставлять значение по строкам в укажите ячейку $C$4.

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

 

После этого таблица заполнится требуемыми значениями.

Анализ «что‐если».

Анализ «что - если» - это процесс изменения ячеек и анализ влияния этих изменений на результат изменения формул на листе. К блоку задач «что-если» относиться подбор параметра.

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

Например, нам надо определить под какой месячный процент нужно положить деньги в банк (подобрать значение ячейки В3), чтобы при установленной предварительной сумме в 100,000 руб (ячейка В1) и сроке - 180 месяцев (ячейка В2) сумма в конце срока составит 1,000,000 руб (ячейка В4). Создадим таблицу:

1. Введем названия строк.

2. Выделим ячейки А1:В4 и определим границы.

1) Для этого установим в меню Главная/(шрифт)Все границы .

2) Другой способ задания границ:

a. Выделите ячейки А1:В4 и нажмите правую кнопку мыши.

b. В раскрывшемся списке выберите пункт Формат ячеек

. Откроется диалоговое окно Формат ячеек.

c. Навкладке Граница, устанавливаем Внутренние и Внешние. Нажмите кнопку ОК.

3. Для столбца А установим ширину. Для этого: выделим столбец А и выберем пункт меню Главная/(Ячейки)Формат/Автоподбор ширины столбца.

4. Установите формат ячейки В3 как процентный. Для этого:

1) Выделите ячейку В3 и выполните команду: в пункте меню Главная в группе Число из раскрывающегося спискавыберите Денежный. 2) Другой способ задания формата ячейки:

a. Выделите ячейку В3 и нажмите правую кнопку мыши.

b. В раскрывшемся списке выберите пункт Формат ячеек

. Откроется диалоговое окно Формат ячеек.

c. Во вкладке Число выберите Процентный. Нажмите кнопку ОК.

5. Самостоятельно определите формат ячеек В1 и В4 как денежный.

6. Введите значения в ячейки В1 и В2.

7. В ячейке В4 введите формулу: =(В1*В3)*В2+В1.

 

Приступим к подбору значения:

1. Выделите ячейку В4.

2. В пункте меню Данные/(Работа с данными) Анализ «что-если» выберем пункт Подбор параметра. Откроется диалоговое

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

3. В строке Установить в ячейке укажем В4.

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

4. В строке Значение – с клавиатуры введем нужное значение. В нашем примере 1,000,000.

5. В строке Изменяя значение в ячейке укажем В3. Нажмем кнопку OK.

6. В окне Результат подбора параметра будет указано на наличие решения.

Нажмем кнопку OK. Найденное значение будет зафиксировано в ячейке.

Работа со сценариями.

Общие сведения

Сценарии являются частью блока задач, который называют инструментами анализа "что-если" (Анализ «что-если». Процесс изменения значений ячеек и анализа влияния этих изменений на результат вычисления формул на листе, например изменение процентной ставки, используемой в таблице амортизации для определения сумм платежей.).

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

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

Теперь рассмотрим следующее задание.

Нам требуется создать бюджет (определить значение в ячейке В3), но доходы и расходы (значения в ячейках В1:В2) точно не известны. Мы можем с помощью Диспетчера сценариев определить различные значения дохода (значение в ячейках В1:В2), а затем переключаться между сценариями для выполнения анализов.

Получим таблицу исходных данных:

1. Введем в ячейки А1:А3 названия исходных данных.

2. Установите формат ячеек В1:В3 как денежный. Для этого:

1) Выделите ячейки В1:В3.

2) В пункте меню Главная в группе Число из раскрывающегося спискавыберите Денежный.

3. Введите в ячейку В3 формулу =В1-В2.

4. Далее следует присвоить имена ячейкам, содержащим исходные данные:

1) Выделите ячейку В1. Выполните команду Формулы/(Определенные имена)Диспетчер имен.

2) В открывшемся окне Диспетчер имен нажмите кнопку Создать из выделенного.

3) Откроется диалоговое окно Создание имен из выделенного диапазона.

Другой способ открыть диалоговое окно Создание имен из выделенного диапазона: выделите ячейку В1. Выполнить команду Форму-

лы/(Определенные имена)Присвоить имя

4) В поле Имя введите Валовая_прибыль.

5) В поле Диапазон введите В1. Для указания ячеек можно поставить курсор в поле , а затем на листе выделить нужные ячейки. Нажмите кнопку ОК.

6) Снова откроется диалоговое окно Диспетчер имен. В списке появиться вновь созданное имя – Валовая_прибыль. Нажмите кнопку Закрыть.

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

5. Самостоятельно присвойте имена ячейкам В2 и В3.

Создание сценария

1. Установить в ячейке B1 значение 50 000р., а в ячейке B2 значение 13 200р. Выделите их.

2. В меню Данные/(Работа с данными)Анализ «что-если» выберете пункт Диспетчер сценариев/

3. Откроется диалоговое окно Диспетчер сценариев. Нажмите в нем кнопку Добавить. Откроется диалоговое окно Добавление сценария/

4. В поле Название сценария введите название создаваемого сценария, например Худший случай.

5. Т.к. изменяемые ячейки В1:В2 были выделены до начала создания сценария, то они автоматически указаны в поле Изменяемые ячейки. При необходимости можно указать другие изменяемые ячейки. Нажмите кнопку ОК.

6. Откроется диалоговое окно Значения ячеек сценария. В полях с именами изменяемых ячеек отображены текущие значения в указанных ячейках В1:В2. При необходимости можно изменить значения выбранных ячеек. Нажмите кнопку ОК.

7. Снова откроется диалоговое окно Диспетчер сценариев, в списке Сценарий появиться имя вновь созданного сценария. Нажмите кнопку Закрыть.

 

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

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

Создание нового сценария

1. Откройте диалоговое окно Диспетчер сценариев, выполнив команду Данные/(Работа с данными)Анализ «что-если»/Диспетчер сценариев.

2. Нажмите в нем кнопку Добавить. Откроется диалоговое окно Добавление сценария.

3. В поле Имя введите Лучший случай. В поле Изменяемые ячейки укажите В1:В2. Нажмите кнопку ОК.

4. Откроется диалоговое окно Значения ячеек сценария. В поле Валовая_прибыль введите 150,000, а в поле Стоимость_товара 26,000. Нажмите кнопку ОК.

5. В открывшемся окне Диспетчер сценариев отобразится список доступных сценариев.

 

Просмотр сценария.

Не закрывая окна Диспетчер сценариев, можно отобразить на листе любой сценарий.

Для просмотра сценария на листе необходимо:

1. Выделить его название (Лучший случай) в списке сценариев; 2. Нажать кнопку Вывести. Выбранный сценарий отобразиться на листе.

Нажмите кнопку Закрыть.

Изменение сценария

Уже созданные сценарии можно изменять. Любой сценарий можно изменить или дополнить. Для этого:

1. Выполните команду Данные/(Работа с данными)Анализ «чтоесли»/Диспетчер сценариев.

2. В окне Диспетчер сценариев выберите нужный сценарий и нажмите кнопку Изменить.

3. Откроется диалоговое окно Изменение сценария, в котором можно изменить имя сценария а также диапазон ячеек. Нажмите кнопку ОК.

4. В полях открывшегося окна Значения ячеек сценария можно ввести другие значения изменяемых ячеек, после чего следует нажать кнопку ОК.

5. Измененный сценарий отобразится в окне Диспетчер сценариев.

Сведение сценариев

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

1. Команду Данные/(Работа с данными)Анализ «что-если»/Диспетчер сценариев.

2. В окне Диспетчер сценариев нажать кнопку Отчет.

3. Откроется диалоговое окно Отчет по сценарию.

4. Можно выбрать тип создаваемого отчета (структура или сводная таблица). Установите флажок около типа отчета Структура.

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

6. Созданный отчет будет автоматически помещен на новый лист текущей книги.

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

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

Для решения таких задач используется надстройка Поиск решения.

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

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

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

1. Нажмите кнопку. В открывшемся меню нажмите кнопку Параметры. Откроется диалоговое окно Параметры Exсel.

2. В открывшемся окне выберите пункт Надстройки.

3. В поле Управление из раскрывающегося списка выберите Надстройки Exсel. Нажмите кнопку Перейти. Откроется диалоговое окно Надстройки.

4. В списке Доступные надстройки установите флажок напротив надстройки Поиск решений. Нажмите кнопку ОК.

 

Теперь рассмотрим следующее задание.

В таблице необходимо подобрать объемы закупки товаров (подобрать значения ячеек В2:В4), чтобы при известных ценах (значения в ячейках С2:С4) общая сумма затрат (результат в ячейке D5) составила 2500 руб. При этом товара «Марс» необходимо купить не менее 120, а товара «Мечта» в два раза больше, чем товара «Люкс». Эта задача решается с помощью надстройки Поиск решения.

Создадим таблицу.

1. Введем названия исходных данных. Укажем значения в ячейках С2:С4 2. В ячейку D2 введем формулу =В2*С2.

3. Аналогично определите формулы для ячеек D3 и D4.

4. В ячейке D5 введем = сумм(D2:D4).

Перейдем к поиску решения

В данном случае целью будет достижение заданного значения в ячейке D5

( общая сумма затрат составляет 2500 руб.), а условиями: В3>=120 ( товара «Марс» необходимо купить не менее 120), В2=2*В4 ( товара «Мечта» необходимо купить в два раза больше, чем товара «Люкс»).

1. Выделите ячейку D5. Выполните команду Данные/(Анализ)Поиск решения . Откроется диалоговое окно Поиск решения.

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

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

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

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

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

В данном примере установите флажок в положении значения и введите число 2500.

4. В поле Изменяя ячейки переменных следует указать, какие ячейки влияют на результат вычислений в целевой ячейке. В нашем примере это ячейки В2:В4.

5. Для указания ячеек можно поставить курсор в поле , а затем на листе выделить нужные ячейки.

Добавление ограничений

1. Нужно нажать кнопку Добавить. Откроется диалоговое окно Добавление ограничения.

2. В поле Ссылка на ячейки указать ячейку В3.

3. Затем в раскрывающемся списке выбрать знак ограничения >=.

4. В поле Ограничение введите 120.

5. Для перехода к установке другого ограничения в окне Добавление ограничения следует нажать кнопку Добавить.

6. Аналогично добавьте следующее ограничение В2=2*В4.

7. Кроме того, значения в ячейках В2:В4 должны быть целыми положительными числами: В2:В4=целое и В2:В4>=0.

8. Для завершения установки ограничений нужно нажать кнопку ОК.

9. В открывшемся окне Поиск решения отобразится список установленных ограничений.

10. В окне Поиск решения нажмите кнопку Найти решение.

11. В окне Результаты поиска решения будет указано на наличие решения.

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

12. Для сохранения найденного решения на листе следует установить переключатель Сохранить найденное решение и нажать кнопку ОК.

Изменение ограничений.

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

1. Выполните команду Данные/(Анализ) Поиск решения. Откроется диалоговое окно Поиск решения.

2. В списке имеющихся ограничений выделите нужное ограничение и нажмите кнопку Изменить. Откроется диалоговое окно Изменение ограничений.

3. Измените ограничения и нажмите кнопку ОК. Снова откроется диалоговое окно Поиск решения.

4. Нажмите кнопку Выполнить.

Удаление ограничений.

Чтобы удалить ограничение:

1. Выполните команду Данные/(Анализ) Поиск решения. Откроется диалоговое окно Поиск решения.

2. В списке имеющихся ограничений выделите нужное ограничение и нажмите кнопку Удалить.

Задания для самостоятельной работы.

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

Здесь значения в ячейках С6:C15 должны зависеть от тарифа, метража и курса доллара (см.уже приведенный в пособии пример).

2. Анализ «что-если». Выполните следующее задание: нам надо определить с какой процентной ставкой взять кредит в банке(подобрать значение ячейки В3), чтобы при занятой сумме в 100 000(ячейка В1) и сроке - 10 месяцев (ячейка В2) сумма к возврату была равна 150 000(ячейка В4). Создадим таблицу.

Значение в ячейке В3 должно зависеть от значений в ячейках В1, В2 и В4.

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

Нам требуется создать бюджет (определить значение в ячейке В5), но доходы и расходы (значения в ячейках В1:В4) точно не известны. Мы можем с помощью Диспетчера сценариев определить различные значения дохода (значение в ячейках В1:В4), а затем переключаться между сценариями для выполнения анализов.

Создайте три сценария: нормальная работа, разорение, процветание. Значения для них в ячейках В1:В4 подберите самостоятельно.

Выполните сведение сценариев на один лист (см. приведенный в данном пособии пример).

4. Поиск решений. Выполните следующее задание.

В таблице необходимо подобрать кол-во товаров, которые нужно купить (подобрать значения ячеек В4:В7), чтобы при известных ценах (значения в ячейках С4:С7) итого было потрачена (результат в ячейке D8) 21 000 руб. При этом духов Chanel необходимо купить 5, товара Шоколадных конфет в два раза больше, чем товара букетов тюльпанов, а подарочных сертификата нужно 3.

Девятый урок

Базы данных в Excel