Использование таблицы подстановки данных с одной переменной

· Определить на листе поле для создания таблицы подстановки.

· Ниже левой верхней ячейки этого поля ввести столбиком варианты параметров для подстановки в исследуемую формулу.

· В ячейку на строку выше и на столбец правее верхней ячейки этого столбца (поля с параметрами) ввести формулу для определения искомого параметра.

· Выделить созданную таблицу подстановки.

· Выполнить: Данные - Таблица подстановки.

· В окне Таблица подстановки в поле " Подставлять значения по строкам", ввести адрес ячейки, в которую будут вводиться варианты параметров из таблицы подстановки. Ок

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

· Выбрать на листе место для таблицы

· Задать входные параметры в таблице: верхние ячейки столбцов - для первого параметра, а левые ячейки строк - для второго.

· На пересечении строки и столбца с входными параметрами таблицы ввести формулу, включающую в себя эти параметры.

· Выделить все поле таблицы и выполнить: Данные - Таблица подстановки.

· Заполнить поля окна Таблица подстановки, при этом в поле "Подставлять значения по столбцам" ввести адрес ячейки, в которую будут из таблицы подстановки вводиться параметры из верхних ячеек столбцов. В поле "Подставлять значения по строкам" ввести адрес ячейки, в которую будут вводиться значения с первых ячеек каждой из строк таблицы подстановки.

· Добавить в таблицу заголовки столбцов и строк.

Сценарий.

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

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

· Сервис - Сценарий - Добавить.

· Добавить.

· Ввести имя сценария и изменяемые ячейки. Ок

Ввести конкретные значения изменяемых ячеек по данному сценарию. Ок.

Повторить процедуру создания других сценариев аналогично.

Просмотр сценариев

· Сервис - Сценарий.

· Выбрать вариант сценария.

· Вывести.

Отмена сценария

Для возврата к исходному состоянию таблицы сразу после просмотра выполнить: Правка - Отменить вывод.

Защита в Excel

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

Пароль это секретное слово в виде комбинации букв, цифр и других символов (до 15 символов), без пробелов и с учетом регистра. Если забыли пароль, то нельзя обойти защиту.. Способов узнать забытый пароль нет! Его надо помнить и хранить.

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

Защита книг осуществляется через Файл - Сохранить как… – Сервис – Общие параметры..

Назначение пароля: Открыть программой защищаемую книгу и выполнить команду: Файл - Сохранить как… – Сервис – Общие параметры.

В окне Параметры сохранения ввести Пароль на открытие и (или)) Пароль для изменения.

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

Теперь после закрытия книги и открытия вновь, будет востребован пароль.

Отмена пароля. (Файл - Сохранить как… – Сервис – Общие параметры) и удалить звездочки из полей Пароль на открытие и (или) Пароль для изменения. Сохранить файл на диске без пароля под прежним именем.

Защита книги. Сервис – Защита – Защитить книгу: Структуру; Окна.

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

Можно защитить окна от изменения размеров, перемещения и закрытия.

Снять защиту книги: Сервис - Защита - Снять защиту книги.

Одновременный доступ к книге из локальной сети нескольких пользователей: Сервис - Доступ к книге… - Правка - Разрешить совместный доступ, Ок..

Защита листа и отдельных ячеек на нем.

По умолчанию все ячейки листа заблокированы.

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

1-й этап: Разблокироват ь те ячейки, в которых данные после защиты необходимо будет изменять. Для этого такие ячейки выделяются и выполняется команда: Формат - Ячейки - Защита иудаляется флажок "Защищаемая ячейка".

2-й этап: Сервис - Защита - Защитить лист.

Снять защиту листа

· Сервис - Защита - Снять защиту листа.

· Выделить ранее разблокированные ячейки и выполнить: Формат - Ячейки – Защита. Установить флажок "Защищаемая ячейка".

Внимание: В защищенных ячейках формулы не показываются.

Excel

Задание 3.1. Создание таблицы

Часть 1. Работа с книгой

1. Создайте книгу для последующего выполнения на ее листах всех практических работ. Присвойте этой книге имя Учебная_Фамилия И.О., где вместо Фамилия И.О. укажите свою фамилию. Сохраните эту книгу в своей папке.

2. Присвойте Листу 1 имя Ячейки. Выделите ячейку А1. С помощью полос прокрутки найдите ячейку Р37 и сделайте ее активной. Активизируйте ячейку А1, (Ctrl+Home).

3. Выделение областей листа:

· Выделить мышью область А1:D9.

· Выделить столбец листа В (кликом по заголовку столбца).

· Выделить любые пять столбцов листа подряд.

· Выделить на листе пять четных строк.

· Выделите область В6:D8, (использовать клавиши Shift и Стрелки).

4. Вставьте в книгу новый лист и присвойте ему имя Форматы.

5. Настроите переход активной ячейки при вводе данных вправо.

6. Изменение ширины столбца:

· Введите в ячейку В2 листа Форматы слово Мир, а в ячейку С2 слово Организация.

· 1-й способ. Подведите указатель мыши к линии разделения заголовков столбцов В и С и сделайте двойной щелчок.

· 2-й способ. Подведите указатель мыши к правой границе заголовка столбца (например, С) и буксируйте эту границу вправо или влево, до придания нужного размера столбцу.

· 3-й способ.

ØВыделите одновременно пять столбцов листа подряд, начиная со столбца D.

ØЩелкните правой по выделенной области и в КМ выполните команду Ширина столбца. Задайте ширину столбца 2,5 символов.

· Высота строк задаётся аналогично: «Формат – Строка – Высота …», либо буксировкой линий разделения строк.

7. Сохраните изменения в книге.

 

Часть 2. Создание таблицы

8. Заполнение данными таблицы:

· Вставьте в книгу новый лист и назовите его Таблица 3.1.

· Выделите область A1:D9 этого листа и задайте ей тип границ Все границы.

· В ячейки А1:D1 введите заголовки столбцов таблицы-образца.

 

Дата	Наименование товара	Индекс клиента	Объем продаж
27.01.07	Крем-брюле	4739АА	1 456
28.01.07	Сливочное
29.01.07	Сахарная	1488АА	2 068
30.01.07	Шоколадное	6398АА	1 399
31.01.07	Фруктовое	7945АА	2 643
1.02.07	Пломбир		10 570
2.02.07	Крем-брюле
3.02.07	Эскимо	5409АА	1 168

 

· Введенные заголовки разместите в центре своих ячеек: (Формат - Ячейки – Выравнивание – По центру, по горизонтали – По центру, по вертикали).

· В ячейку D1 вставьте Примечание «Распоряжение гендиректора», (Вставка – Примечание).

· Введите в ячейку А2 дату 27.01.2007, точку после даты не ставить!!!

· Задайте введенному числу формат даты. (Формат - Ячейки – Число). В поле Числовые форматы выбрать Дата - в поле Тип выбрать образец даты: ДД.ММ.ГГ, Ок. Попробуйте и другие форматы дат, но оставить ДД.ММ.ГГ.

· Сделать ячейку А2 активной и, используя маркер автозаполнения, буксировкой ввести данные в первый столбец таблицы.

· Заполняя столбец «Наименование товара», обратите внимание на возможность автозаполнения. С ним можно согласиться или нет.

· Заполните столбец «Индекс клиента». Обратите внимание на то, что числа в ячейке прижимаются к правому краю ячейки, а текст к левому. Задайте введенным данным текстовый формат.

· Заполните столбец «Объем продаж». Задайте введенным данным числовой формат.

· Задайте столбцу В ширину 20 символов, а столбцам С и D по 15 символов (см. п. 6).

· Измените содержимое ячейки D7 на 10057.

9. Копирование ячеек, таблиц

· Выделите область А1: D9 и скопируйте ее в БО (кн. Копировать).

· Сделайте ячейку G1 активной и нажмите кнопку Вставить. Скопированная область помещается в область G1:J9. Заметим, что ширина ячеек не копируется.

· Вставьте в книгу новый лист и присвойте ему имя Мороженное. Поместите скопированную таблицу и на этот лист. Для этого на листе Мороженное сделайте активной ячейку А1 и щелкните по кнопке. Вставить.

· Вернитесь на лист Таблица 3.1, выделите копию основной таблицы и удалите ее.

· Очистка ячеек: Активизируйте лист Мороженное, выделите на нем ячейки А1:А10. Выполните «Правка – Очистить – Все». Восстановите А1:А10 отменой предыдущей команды (кнопка Отменить).