Установка связи между листами.

 

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

 

Пример:

1. Книга состоит из четырех Листов.

2. На первых трех Листах данные содержатся в диапазонах А1: А3.

3. На Листе 4 выделить ячейку А1.

4. Щелкнуть по кнопке Сумма из Группы Инструментов Ячейки Вкладки Главная.

5. Щелкнуть по ЯрлычкуЛист1, выделить диапазон А1: А3 и нажать на Enter.

6. На Листе 4 выделить ячейку В1.

7. Щелкнуть по кнопке Сумма из Группы Инструментов Ячейки Вкладки Главная.

8. Щелкнуть по ярлычку Лист, выделить диапазон А1: А3 и нажать на Enter.

9. На Листе 4 выделить ячейку С1.

10. Щелкнуть по кнопке Сумма из Группы Инструментов Ячейки Вкладки Главная.

11. Щелкнуть по ярлыку Лист 3, выделить диапазон А1: А3 и нажать на Enter.

 

В результате - на Листе 4:

 

· в ячейке А1 – сумма диапазона А1: А3 Листа 1.

· в ячейке В1 – сумма диапазона А1: А3 Листа 2.

· в ячейке С1 – сумма диапазона А1: А3 Листа 3.

Изменение любых исходных данных на Листах 1-3 автоматически ведет к изменению сумм на Листе 4

3. Использование дополнительных Книг.

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

 

 

Переход между Книгами.

Открытие дополнительной Книги - стандартное: кнопка Office - команда Создать.

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

Для одновременногопросмотра Книг используют находящиеся там же кнопки Рядом , Синхронная прокрутка и Восстановить расположение окна (См. Рис. 5).

Рис. 5. Диалоговое окно Расположение окон.

 

Для упорядочивания окон служит диалоговое окно Расположение окон, вызываемое кнопкой Упорядочить все, располагающейся в той же Группе инструментов Окно. Это диалоговое окно (См. Рис. 5) содержит переключатель на 4 опции:

 

• Рядом,
• Сверху внизу,
• Слева направо (См. Рис. 6),
• Каскадом.

 

Активная книга определяется по цвету строки заголовка окна (как и все активные окна в среде Windows ). Для перехода от книги к книге используются комбинации клавиш Ctrl+F6 и Ctrl + Shift + F6. Кроме того, переход осуществляется щелчком мыши по строке заголовка нужного окна.

Установка связи между книгами.

Установка связи между Книгами аналогична установке связи между Листами.

Пример (См. Рис. 6): Открыть: Книга1 с данными в диапазоне Лист1! А1: А3

Книга 2 с данными в диапазоне Лист1! А1: А3

 

Получить суммы в Книге 3:

 

· в А1 = SUM([Книга1]Лист1! $А$1: $А$3) и

· в В1 = SUM([Книга2]Лист1! $А$1: $А$3)

Внимание! Используются абсолютные полные адреса.

SUM([Книга2]Лист1! $А$1: $А$3)

SUM([Книга1]Лист1! $А$1: $А$3)

 

Рис.6. Установка связи между Книгами

Консолидация данных.

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

Для установки связи между несколькими Листами с одинаковой структурой используется:

 

Вкладка Данные - Группа Инструментов Работа с данными - кнопка Консолидация.

Пример : Лист Октябрь - данные в диапазоне D5: F10.

Лист Ноябрь - данные в диапазоне D5: F10.

Лист Декабрь - данные в диапазоне D5: F10.

 

Получить результат на Листе Квартал 4 в том же диапазоне.

 

 

Рис.7. Диалоговое окно Консолидация

 

Рис.8. Результат выполнения Консолидации

 

 

Диалоговое окно Консолидация (См. Рис. 7) содержит:

 

· раскрывающийся список Функция (по умолчанию – наиболее часто используется СУММ ),

· текстовое поле Ссылка – для ручного ввода диапазонов или ввода с помощью мыши,

· список используемых диапазонов,

· группа обслуживающих флажков,

· кнопки управления.

 

Алгоритм выполнения:

 

1. Выделить диапазон D5: F10 на Листе Квартал 4.

2. Вызвать диалоговое окно Консолидация.

3. Выделить диапазон D5: F10 на Листе Октябрь .

4. Щелчок по кнопке Добавить.

5. Диапазон D5: F10 с Листа Октябрь зафиксируется в окне . Возможно появление подсказки по выбору следующего диапазона.

6. Выполнить п.п. 3-5 для Листа Ноябрь.

7. Выполнить п.п. 3-5 для Листа Декабрь.

8. Щелчок по кнопке ОК.

 

Диапазон D5: F10 на Листе Квартал 4 будет содержать сквозные суммы с Листов Октябрь, Ноябрь, Декабрь – т.е.сформирована Квартальная ведомость (См. Рис. 8).

 

При консолидации обычно, кроме функции СУММ используются функции СЧЕТ, СРЗНАЧ, МАКС, МИН и СТАНДОТКЛ.

 

 

Защита листов и книг.

 

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

 

 

4.1. Защита Листов.

 

Выбрать Лист, подлежащий защите, затем выполнить:

 

Вкладка Рецензирование - Группа Инструментов Изменения

- кнопка Защитить лист – диалоговое окно (См. Рис. 9), содержащее:

 

· текстовое поле Пароль:

· многочисленные флажки, определяющие критерии защиты:

Внимание!

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

2. При установке пароля Excel отличает прописные буквы от строчных букв.

3. При забывании пароля защиту снять нельзя.

4. После ввода пароля появляется диалоговое окно Подтверждение пароля с напоминанием о равилах пользования.

 

Естественно, что кнопка Защитить лист трансформируется в кнопку Снять защиту листа, если выделить тот Лист, где защита уже установлена.

 

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

 

Рис. 9. Диалоговые окна Защита Листа и Подтверждение пароля.

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

 

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

2. Вызвать диалоговое окно ФорматЯчеек (например, из контекстного меню) - вкладка Защита – снять флажок Защищаемая ячейка.

3. Затем выполнить защиту Листа.

 

 

4.2. Защита Структуры Книги.

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

 

Вкладка Рецензирование - Группа Инструментов Изменения - кнопка Защитить Книгу – раскрывающийся список (См. Рис. 10, содержащий ряд команд.

 

Команда Защита структуры и окон вызывает соответствующее диалоговое окно (См. Рис. 10), содержащее:

 

· Текстовое поле Пароль. Здесь использование пароля аналогично п. 4.1

· Флажок Структуру – запрет другим пользователям на изменение следующего типа:

 

o вставка,

o удаление,

o скрытие,

o переименование,

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

 

· Флажок Окна – ставит запрет другим пользователям на изменение размеров окон при работе с Книгами. Снятие защиты аналогично п. 4.1.

Рис. 10. Инструмент Защитить книгу и диалоговое окно Защита структуры и окон.

 

 

⇐ Предыдущая1234Следующая ⇒

Поделиться: