Тема 3. Организация информации в книгах.

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

Работа с Книгой может требовать участия данных одного Листа при создании формулы на другом Листе. Установка такой связи между Листами также называется Ссылкой. Ссылки дают преимущества в том, что при изменении данных в исходном листе 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. Инструмент Защитить книгу и диалоговое окно Защита структуры и окон.

Регулировка вида.

Основные регулировки вида Листа осуществляются в Группе Инструментов Вид.

5.2. Группа Инструментов Масштаб .

Группа Инструментов Масштаб из Вкладки Вид расширяет возможности инструмента Масштаб из правой нижней части экранного интерфейса , позволяя изменить масштаб Листа или его отдельнойвыделенной части и содержит следующие инструменты:

o кнопка Масштаб – вызывает соответствующее диалоговое окно (См. Рис.12) с переключателемна 7опций:

· первые пять – фиксированные значения,

· По выделению - оптимальное увеличение выделенных ячеек,

· Произвольный - в интервале от 10% до 100% - путем ввода

конкретного значения.

o кнопка 100% - возвращает стандартное значение Масштаба.

o кнопка Масштабировать выделенный фрагмент выполняет объявленное действие.

Внимание! Если необходимо изменить масштабнескольких Листов Книги, необходимо удерживать клавишу Shift при щелчках левой кнопкой мыши по Ярлычкам масштабируемых Листов.

Рис. 12 Диалоговое окно Масштаб из Вкладки Вид.

5.3. Команда Представления.

Если книга состоит из нескольких Листов разных видов, для удобного перехода между ними следует организовать, используя оригинальные имена Листов, список имен в диалоговом окне Представления . То же можно выполнить для отдельных фрагментов одного Листа.

Вкладка Вид - Группа Инструментов Режимы просмотра Книги - кнопка Представления - соответствующее диалоговое окно - кнопка Добавить - диалоговое окно Добавление представления (См. Рис. 13) – затем - ввод имени Представления.

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

Рис.13. Диалоговые окна Представления и Добавление представления.

Удаление Представления на одном или разных Листах - выделение его имени в списке, далее - кнопка Удалить .

6. Наcтройка Панели быстрого доступа.

Приложение Excel дает возможность организовать эту Панель так, как это угодно пользователю.

кнопка Office – кнопка Параметры Excel - соответствующее диалоговое окно - категория Настройка – раздел Настройка Панели быстрого доступа (См. Рис. 14)

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

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

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

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

Находящиеся правее две кнопки управления со стрелками определяют последовательность кнопок на Панели быстрого доступа

Рис. 14. Диалоговое окно параметры Excel - категория Настройка - раздел Настройка Панели быстрого доступа

Кнопка Сброс восстанавливает предыдущее состояние Панели быстрого доступа.

Вкладка Страница.

Вкладка Страница (См. Рис. 18) содержит:

· разделы Ориентация и Масштаб;

· раскрывающиеся списки Размер бумаги и Качество печати;

· кнопка Просмотр, переводящая интерфейс Excel в окно Предварительного просмотра перед печатью.

· кнопки управления принтером Печать и Свойства (если принтер установлен).

Рис. 18. Диалоговое окно Параметры страницы – вкладка Страница.

Вкладка Поля.

Вкладка Поля (См. Рис. 19), кромеокна Образца для визуального контроля, содержит:

· четыре счетчика Полей;

· два счетчика расстояний до верхнего и нижнего Колонтитулов;

· раздел Центрировать на странице, содержащий флажки соответствующей центровки: Горизонтально и Вертикально;

· те же кнопки Просмотр, Печать и Свойства.

Рис. 21. Диалоговое окно Параметры страницы – вкладка Поля.

Вкладка Колонтитулы..

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

Данная вкладка содержит два флажка, установленных по умолчанию, для настройки правильности отображения Листа:

· Изменять масштаб вместе с документом;

· Выровнять относительно полей страницы.

Здесь же находятся два важных флажка, устанавливаемых в особых случаях:

· Разные колонтитулы для четных и нечетных страниц;

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

Вкладка Колонтитулы также содержит кнопки Просмотр, Печать и Свойства.

Рис. 20. Диалоговое окно Параметры страницы – вкладка Колонтитулы - диалоговое окно Верхний Колонтитул.

Вкладка Лист.

Вкладка Лист (См. Рис. 21) содержит следующие инструменты:

· Раздел Печатать на каждой странице:

o Текстовое поле Выводить на печать диапазон - распечатывает указанный в нем диапазон листа.

o Два текстовых поля Сквозные строки и Сквозные столбцы служат для повторения заголовков столбцов и строк на многостраничных распечатках.

· Раздел Последовательность вывода станиц с переключателями направления вывода.

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

Вкладка Лист, как и остальные, содержит кнопки принтера Печать и Свойства, также кнопку Просмотр.

· Меню Сервис - команда Параметры - диалоговое окно - вкладка Вид – снятый флажок Сетка организует снятие сетки с экрана.

7.5. Группа Инструментов Параметры Листа

Группа Инструментов Параметры Листа с Вкладки Разметка страницы (См. Рис. 21) содержит флажки для отображения и/или печати Сетки и Заголовков.

Флажки для отображения и/или печати Сетки и Заголовков

Рис. 21. Диалоговое окно Параметры страницы – вкладка Лист.

Флажки для отображения и/или печати Сетки и Заголовков

8. Настройка внешнего вида Листа.

8.1. Прочие инструменты с Ленты Инструментов

Инструменты настройки внешнего вида Листа весьма разнообразны, и многие уже рассматривались ранее. Кроме них, можно использовать:

Вкладка Разметка Страницы - Группа Инструментов Тем, содержащая кнопки, Панели которых содержат варианты форматирования, улучшающие внешний вид таблиц:

· Темы (См. Рис. 22);

· Цвета;

· Шрифты;

· Эффекты.

ВкладкаВид - Группа Инструментов Показать или скрыть содержит ряд флажков, позволяющих ставить или снимать те или иные элементы графического интерфейса приложения Excel (См. Рис. 23):

· Сетка,

· Линейка,

· Строка формул,

· Заголовки,

· Панель сообщений.

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

Рис. 22. Вариант оформления таблицы – Тема Метро

ВкладкаВид - Группа Инструментов Показать или скрыть - флажки Сетка, Линейка Строка формул, Заголовки, Панель сообщений

Рис. 23. Флажки Группы Инструментов Показать или скрыть с ВкладкиВид

8.2. Основные настройки диалогового окна Параметры Excel

Тема 3. Организация информации в книгах.

1. Работа с Листами

1.1. Переход между ЛистамиКниги

1.2. Переименование Листов

1.3. Удаление Листов

1.4. Вставка Листов

1.5. Контекстное меню ЯрлычкаЛистов

1.6. Перемещение и копирование Листов

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

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

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

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

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

4. Защита Листов и Книг

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

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

4.3. Установка пароля документа к файлу

5. Настройка Excel

5.1. Регулировка вида

5.2. Группа Инструментов Масштаб

5.3.Команда Представления

6. Настройка Панели быстрого доступа

7. Настройка параметров печати

7.1. Управление ориентацией страницы

7.2. Настройка размера страницы и полей

7.3. Прочие инструменты Группы Инструментов Параметры страницы

7.4. Диалоговое окно Параметры страницы

7.4.1. Вкладка Страница.

7.4.2. Вкладка Поля.

7.4.3. Вкладка Колонтитулы..

7.4.4. Вкладка Лист.

8.Настройка внешнего вида Листа

8.1. Прочие инструменты с Ленты Инструментов

8.2. Основные настройки диалогового окна Параметры Excel

1. Работа с Листами.

Приложение Excel 2007 обеспечивает возможность работы с Книгами, включающими до 255 Листов. Все Листы Книги хранятся в одном файле.

1.1. Переход между страницами Книги.

По умолчанию состоит из трех Листов. Количество Листов для создаваемых новых Книг можно установить произвольно в диапазоне 1-255:

кнопка Office – кнопка Параметры Excel - соответствующее диалоговое окно – категория Основные – раздел При создании новых книг – счетчик Число Листов

Ярлычки Листови их Кнопки прокрутки

Рис. 1.Диалоговое окно Параметры Excel – категория Основные.

Переход между Листами осуществляется щелчком мыши по одному из трех т.н. Ярлычков Листов Книги в левом нижнем углу (См. Рис. 1).

Кроме того, там же имеются кнопки прокрутки Ярлычков, активные, когда Ярлычки занимают все отведенное для них место:

· Внешние кнопки – переходы к первому и последнему Листу

· Внутренние кнопки – переходы по одному Листу влево и вправо.

1.2. Контекстное меню Ярлычка Листа.

Щелчок правой кнопки мыши по ярлычку листа вызывает его контекстное меню, содержащее инструменты (См. Рис. 2):

1. Команда Вставить - вызывает соответствующее диалоговое окно .

2. Команда Удалить.

3. Команда Переименовать.

4. Команда Переместить / Скопировать – вызывают соответствующие диалоговые окна.

5. Команда Исходный текст – для перехода в Visual Basic.

6. Команда Защитить Лист - вызывает соответствующее диалоговое окно.

7. Подменю Цвет Ярлычка – вызывает Панель цветов.

8. Команда Скрыть.

9. Команда Отобразить - вызывает соответствующее диалоговое окно .

10. Команда Выделить все листы.

1.3. Переименование Листов.

Стандартные имена: Лист1, Лист2, и т.д. – являются временными. Лист можно переименовать в любой момент одним из следующих вариантов:

1. Двойной щелчок по Ярлычку Листа. Его название представляется желтым шрифтом в черном прямоугольнике. Затем ввод нового имени вручную (отмена переименовывания – клавиша Esc )

2. Контекстное меню Ярлычка – команда Переименовать (См. Рис. 2). Далее аналогично предыдущему варианту.

Имя Листа – до 31 знака, включая пробелы. Однако длинные имена уменьшают место для других Ярлычков.

Рис.2. Контекстное меню Ярлычка.

1.4. Удаление Листов.

Один пустой Лист занимает некоторое пространство на жестком диске. Если лишние Листы не нужны, для экономии их можно удалить (естественно, всегда имеется возможность вставки новых Листов ) одним из следующих вариантов:

Контекстное меню Ярлычка – команда Удалить

Вкладка Главная - Группа Инструментов Ячейки – двойная кнопка Удалить – команда Удалить лист (См. Рис. 3).

Внимание! Удаленный лист – необратимая операция, даже если он содержит данные.

Если необходимо удалить несколько листов, используются вспомогательные клавиши Ctrl – для выборочного выделения под удаление или Shift – для удаления листов по типу выделения « от » и « до ».

Рис.3. Группа Инструментов Ячейки – двойная кнопка Удалить – команда Удалить лист.

1.5. Вставка Листов.

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

1. Вкладка Главная - Группа Инструментов Ячейки – двойная кнопка Вставить – команда Вставить лист – выполняется вставка Листа. Новый вставленный Лист помещается перед текущим Листом, получая следующий порядковый номер.

Контекстное меню Ярлычка – команда Вставить - диалоговое окно Вставка с двумя вкладками - Общие ( для вставки Листов, листов Диаграмм и макросов ) и Решения ( для вставки готовых шаблонов Книг) (См. Рис. 4).

Рис. 4.Вкладки Общие и Решения диалогового окна

1.6 Перемещение и копирование Листов.

В Excel имеется возможность изменить порядок Листов в Книге с помощью мыши стандартным способом:

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

Копирование (отличается от перемещения): те же действия, но необходимо нажимать клавишу Ctrl. Указатель в виде листочка содержит символ « плюс », а имя Листа будет иметь в скобках суффикс ( 2 ) и т.д.

12 3 4 Следующая ⇒
Поделиться:

Последнее изменение этой страницы: 2017-03-17; Просмотров: 309; Нарушение авторского права страницы