Фиксация заголовков столбцов и строк

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

· Перейти на лист «Отчет».

· Установить курсор в ячейку С7.

· На вкладке Вид в группе Окно нажать кнопку Закрепить области. В открывшемся окне выбрать Закрепить области.

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

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

· Самостоятельно выполнить фиксацию только шапки таблицы и первого столбца таблицы, затем отменить фиксацию.

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

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

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

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

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

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

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

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

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

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

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

Упражнение. Для защиты рабочего листа «Отчет» необходимо:

· На вкладке Главнаяв группе Ячейки нажать кнопку Формати в открывшемся окне выбрать команду Защитить лист.

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

Рис.18

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

Примечание. Установить защиту листа можно также с помощью кнопки Защитить лист в группе Изменения на вкладке Рецензирование.

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

Можно установить защиту отдельных областей рабочего листа.

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

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

· В открывшемся окне на вкладке Защита. Отключить параметр Защищаемая ячейка и нажать ОК.

· На вкладке Рецензирование нажать кнопку Защитить лист

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

Рис.19

· Нажать ОК.

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

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

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

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

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

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

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

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

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

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

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

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

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

· Установить курсор в ячейку Н11 (итог по графе «Выручка») и на вкладке Данныев группе Работа с данными нажать кнопку Анализ «что-если».В открывшемся окне выбрать команду Подбор параметра.

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

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

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

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

Рис.20

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

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

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

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

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

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

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

· На листе «Анализ данных»: в ячейку Е14 ввести формулу =СУММ(F3:F10);

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

· Выделить блок ячеек D14:Е17, выполнить последовательность действий Данные|Работа с данными| Анализ «что-если»|Таблица данных.

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

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

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

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

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

· Выделить блок ячеек Е20:Н23, выполнить последовательность действий Данные|Работа с данными| Анализ «что-если»|Таблица данных.

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