Связывание таблиц, листов.

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

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

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

 

 

Рис.1.

 

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

a) заменить названия листов Лист1 – I полугодие, Лист2 – II полугодие, Лист3 – За год (для этого встать мышкой на слово Лист1 и дважды щелкнуть левой кнопкой мыши);

b) на листе I полугодие создать таблицу с данными по месяцам – январь - июнь; на листе II полугодие - с данными по месяцам июль - декабрь, на листе За год таблицу с колонками – I полугодие, II полугодие, итого;

c) заполнить первую и вторую таблицы;

d) когда исходные таблицы сформированы, приступаем к созданию связей между таблицами, чтобы получить данные для третьей таблицы За год, для этого:

e) активизировать ячейку в третьей таблице в колонке I полугодие и набрать знак “=“;

f) выделить ячейку Итого в таблице I полугодие (Лист I полугодие), после чего координаты этой ячейки появятся в строке формул, если выражение сформировано верно, нажмите Enter;

g) аналогичные операции выполнить и для ячейки II полугодие для таблицы За год.

 

3) связывание групп ячеек с подсчетом результатов по определенным функциям – консолидация. При консолидации данных объединяются значения из нескольких диапазонов данных. Например, если имеется лист расходов для каждого регионального представительства, консолидацию можно использовать для преобразования этих данных в лист корпоративных расходов.

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

Консолидация с помощью формул проводится в следующем порядке.

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

Например, чтобы объединить данные в ячейке B3 листов с Лист2 по Лист7, введите =СУММ(Лист2:Лист7!B3). Если данные, которые требуется объединить, находятся в разных ячейках на разных листах, введите формулу в формате =СУММ(Лист3!B4; Лист4!A7; Лист5!C5). Чтобы ввести ссылку (например Лист3!B4), не используя клавиши на клавиатуре, введите формулу до того места, где требуется вставить ссылку, а затем укажите на листе нужную ячейку.

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

1. Настройте данные для консолидации.

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

§ Разместите каждый диапазон на отдельном листе. Не помещайте диапазоны на лист, на котором должна выполняться консолидация.

§ Если консолидация выполняется по положению, убедитесь, что макеты всех диапазонов совпадают.

Если консолидация выполняется по категории, убедитесь, что подписи столбцов или строк, которые требуется объединить, совпадают с учетом регистра букв.

§ Назначьте имя каждому диапазону: выделите диапазон, укажите в меню Вставка на пункт Имя, выберите команду Присвоить и введите имя для данного диапазона.

2. Щелкните левый верхний угол области, в которой требуется разместить консолидированные данные.

3. В меню Данные выберите команду Консолидация.

4. Выберите из раскрывающегося списка Функция выберите тип функции, которую требуется использовать для консолидации данных.

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

6. Если таблицу консолидации требуется обновлять автоматически при каждом изменении данных в каком-либо исходном диапазоне, и позже точно не потребуется изменять или добавлять диапазоны исходных данных для консолидации, установите флажок Создавать связи с исходными данными.

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

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

Подготовка к печати.

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

Все действия по форматированию можно выполнить, пользуясь командами Шрифт и Число меню Формат. Кроме того, чаще всего использующиеся операции форматирования вынесены в качестве кнопок инструментов Стандартной панели инструментов и панели инструментов Форматирование.

Обрамление таблицы

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

Для оформления таблиц также можно использовать автоформатирование (режим Автоформат), которое позволяет выбрать стандартный формат для вашей таблицы.

Для подготовки таблиц к печати необходимо выполнить следующие действия:

Определить параметры страницы (задать формат бумаги: А4; размеры полей: верхнее, нижнее - 3 см., слева, справа - 2 см.; расположение таблицы на листе: выравнивание по горизонтали).

Задать верхние и нижние колонтитулы (верхний колонтитул: “Лабораторная работа № 1. Задание №….Выполнил (ФИО)”, нижний колонтитул: “лист #”).

Сформировать общий заголовок таблицы и выровнять его по центру листа.

Просмотр готового листа

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

Контрольные вопросы.

1. Перечислите категории вводимых в ячейку данных.

2. Как производится фиксация введенных данных в ячейке?

3. Что называется адресом ячейки? Виды адресов.

4. Как производится копирование и вставка данных в ячейки?

5. Как в EXСEL производится проверка орфографии?

6. Каким образом произвести форматирование чисел в ячейках?

7. Что входит в понятие форматирование ячейки?

8. Как сделать разграфление и обрамление таблицы?

9. Как провести графический анализ данных?

10. Какие виды диаграмм можно использовать в EXСEL?

11. Иногда после создания диаграммы числовые значения требуется изменить. Как обновить такую диаграмму?

12. Назовите способы связывания и консолидации рабочих листов

13. Для чего предназначено связывание рабочих листов?

14. Как подготовить таблицы к печати?

ЗАДАНИЯ.

Часть 1

1. В личной папке создайте папку «Лабораторная работа 3».

2. Откройте рабочую книгу EXСEL. Составить на листе 1 таблицу статистической информации за первое полугодие. На листе 2 – таблицу статистической информации за второе полугодие.

3. В каждой таблице подсчитать по строчкам и столбцам сумму, а также определить с помощью Мастера функций максимальное, минимальное, среднее значение (по строкам, столбцам и за полугодие). По данным каждого месяца посчитать отклонение максимального и минимального значений от среднего значения.

Таблица № 1. Сводка за первое полугодие.

Месяц Наименование	Январь	…	Июнь	Итого	Среднее Значение по позиции	Максимальное значение	Минимальное значение
Итого за месяц
Среднее значение за месяц
Ввести значения используя функцию Если (Макс.– Ср.)>0 то напечатать «Превышение среднего», иначе печатать «Ниже среднего»

 

4. Для каждой таблицы по месячным данным на соответствующем листе построить гистограммы типа №3 и типа №4.

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

Таблица №3. Итоговые значения за год.

Полугодие Наименование	1-е полугодие	2-е полугодие	Итого за год

 

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

Часть 2.

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