Методические указания 

к выполнению лабораторной работы

"Связывание электронных таблиц Excel"

для студентов всех специальностей

1 Цель

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

2 Задачи

Освоить приемы связывания электронных таблиц в Excel.

3 Содержание

3.1 Загрузить оболочку Windows, запустить приложение Excel, загрузить файл с таблицей, созданной в лабораторной работе №14.

3.2 Открыть второе окно для просмотра следующего листа,  упорядочить расположение окон на экране.

3.3 На втором листе сформировать таблицу, указанную преподавателем  (см. Приложения), объединенную по смыслу с таблицей, созданной ранее. Связать данные таблиц с помощью команды СПЕЦИАЛЬНАЯ ВСТАВКА и прямым связыванием.

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

3.5 Сохранить свой  файл  на диске  и  оформить  отчет.

4 Требования к отчету

   Отчет должен содержать:

   -  название работы, задачи и последовательность ее выполнения ;

   -  ответы на контрольные вопросы по п.7 указаний;

      К отчету прилагается дискета с результатом работы.

5 Общие положения

5.1 Листы рабочей книги

Начиная с версии 5.0, в Excel можно работать одновременно с несколькими таблицами, расположенными на РАБОЧИХ  ЛИСТАХ (СТРАНИЦАХ), которые объединяются в РАБОЧИЕ КНИГИ. Использование рабочих листов, расположенных в одной книге, т.е. в одном файле, облегчает работу с несколькими таблицами или диаграммами, связанными по смыслу или общими данными, и помогает проводить последующий анализ данных.

Рабочие листы могут иметь собственные имена. В новой рабочей книге листы нумеруются от Лист 1 до Лист 16. При запуске Excel на экране появляется рабочая книга с именем Book1(Книга1), в которой открыт первый рабочий лист.

В нижней части экрана Excel расположены ЯРЛЫЧКИ с именами рабочих листов и КНОПКИ ПРОКРУТКИ ярлычков.

Для перехода в другой рабочий лист книги нужно щелкнуть левой кнопкой мыши на его ярлычке и лист откроется. Если ярлычок листа не виден на экране, можно правой  кнопкой  мыши щелкнуть на кнопках прокрутки ярлычков и в открывшемся списке листов выбрать нужный.

 Щелчок правой кнопкой мыши на ярлычке листа открывает его контекстно-зависимое меню, состоящее из пяти команд: ВСТАВКА..., УДАЛИТЬ, ПЕРЕИМЕНОВАТЬ..., ПЕРЕМЕСТИТЬ/СКОПИРОВАТЬ, ВЫДЕЛИТЬ ВСЕ ЛИСТЫ, с помощью которых можно управлять рабочими листами книги.

5.2 Вставка, удаление и переименование рабочего листа

После выбора команды ВСТАВКА из контекстно-зависимого меню листов на экране появляется диалог, в котором можно выбрать тип вставляемого листа: рабочая таблица, диаграмма и др.

Ненужный или пустой лист можно удалить командой УДАЛИТЬ из контекстно-зависимого меню листов.

Можно удалить сразу несколько рабочих листов, предварительно выделив их.  Выделение производится мышью при нажатой клавише Ctrl. После выбора команды Excel спросит вас, действительно ли вы желаете удалить рабочие листы.

Для ежедневной работы переименование рабочих листов очень важно, так как удобнее обращаться к листам, имеющим смысловые названия. Переименование выполняется командой ПЕРЕИМЕНОВАТЬ из контекстно-зависимого меню листов, в одноименном диалоге которой нужно ввести новое имя для рабочего листа.

5.3 Перемещение и копирование рабочих листов

Можно легко перемещать рабочие листы внутри рабочей книги, изменяя порядок их следования. Также возможно копирование рабочих листов, причем перемещать и копировать рабочие листы можно и в другие книги. Для этого используется команда ПЕРЕМЕСТИТЬ-СКОПИРОВАТЬ из контекстно-зависимого меню листов. В появившемся на экране диалоге с помощью кнопки-переключателя СОЗ-ДАВАТЬ КОПИЮ  можно выбрать между перемещением и копированием рабочего листа. В поле В КНИГУ данного диалога можно указать книгу, в которую необходимо переместить или скопировать рабочий лист. По умолчанию в этом поле находится название текущей книги. В поле ПЕРЕД ЛИСТОМ указывается позиция,  куда нужно переместить или скопировать лист.

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

5.4 Связывание рабочих листов

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

Изменение содержимого клетки на одном листе (листе-источнике) рабочей книги приводит к изменению связанных с ней клеток в листах-приемниках. Этот принцип отличает связывание листов от простого копирования содержимого клеток из одного листа в другой. В зависимости от техники исполнения связывание  бывает “прямым“ и через команду СПЕЦИАЛЬНАЯ ВСТАВКА.

5.5.1 Прямое связывание листов используется при вводе формулы в клетку одного листа, в которой в качестве одного из операндов используется ссылка на клетку другого листа. Если в клетке таблицы (например, в рабочем Листе2) содержится формула, в которой используется ссылка на клетку другого рабочего листа (например, на клетку А1 рабочего Листа1) и оба листа загружены, то такое связывание указанных листов называется “прямым”. Термин “прямое” связывание обозначает, что пользователь сам непосредственно при вводе формулы указывает имя листа и абсолютный адрес клетки из него, разделенные знаком "!".

Примеры формул: = C5*Лист1! A1

= Лист3! В2*100

= Лист1! A1- Лист2! A1

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

Например = 'C:\ EXCEL\[ КНИГА1.XLS] Лист1'!A1*С5

5.5.2 Связывание через команду СПЕЦИАЛЬНАЯ ВСТАВКА производится, если какая либо клетка таблицы на одном рабочем листе должна содержать значение клетки из другого рабочего листа.

Чтобы внести в Лист2 значение клетки A1 из исходного Листа1, нужно выделить эту клетку и выбрать команду ПРАВКА-КОПИРОВАТЬ. На втором листе поставить курсор на ту клетку, куда нужно копировать, и выполнить команду ПРАВКА-СПЕЦИАЛЬНАЯ ВСТАВКА - ВСТАВИТЬ СВЯЗЬ. На втором листе появится указание на клетку исходного первого листа, например:

 = Лист1!$A$1 .

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

5.6 Работа с несколькими окнами

Через команды пункта меню ОКНО Excel обеспечивает все многообразие работы с окнами в среде Windows.

5.6.1 Командой РАЗБИТЬ экран Exсel можно “разбить” на четыре окна (два горизонтальных и два вертикальных). Тогда в четырех окнах отображаются разные части одного рабочего листа, причем точка пересечения окон пройдет рядом с активной в момент разделения клеткой таблицы. Переходя из одного окна в другое и перемещаясь в рабочем пространстве, можно установить удобное для работы расположение таблицы. Установив указатель мыши на пересечении вертикальной и горизонтальной линий, разделяющих лист (курсор превращается в крестик) и передвигая его, можно манипулировать размерами и взаиморасположением окон на экране.

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

5.6.2 Командой НОВОЕ ОКНО на экране можно “открыть” несколько дополнительных окон. В этом случае в них могут отражаться разные части одного листа, разные листы одной или разных книг.

 Для закрытия дополнительного окна в его системном меню нужно вызвать команду ЗАКРЫТЬ или воспользоваться комбинацией клавиш CTRL+F4.

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

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

5.6.3 Командой УПОРЯДОЧИТЬ организуют большое количество окон на экране. В диалоге  УПОРЯДОЧИТЬ ОКНА имеется четыре варианта группирования окон: МОЗАИКА, ПО ГОРИЗОНТАЛИ, ПО ВЕРТИКАЛИ, ОКНА АКТИВНОЙ РАБОЧЕЙ КНИГИ.. Первая опция равномерно распределит открытые окна на рабочей странице EXCEL. Вторая расположит все окна одно под другим и в полном размере. Третья- распределит окна одно рядом с другим. Четвертая перенесет все окна рабочей страницы на передний план. Окна других документов EXCEL разместит на заднем  плане.

5.7 Смена, сокрытие и свертывание окон

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

Если в настоящее время работа с открытым окном не ведется, то  его можно "спрятать". Это делается с помощью команды меню ОКНО-СКРЫТЬ. Рабочий лист, таким образом, не удаляется, а делается невидимым.

Окно также можно уменьшить до размера значка одним из трех  способов:

-  с помощью кнопки минимизации окна, находящейся в правом верхнем

углу окна;

-  командой СВЕРНУТЬ из системного меню окна;

-  комбинацией клавиш CTRL+F9;

С помощью команды меню ОКНО-УПОРЯДОЧИТЬ ЗНАЧКИ, которая появляется, когда вы уменьшили окна до значков, можно оптимально разместить  значки внутри окна EXCEL.

5.8 Увеличение и уменьшение размеров изображения в окнах

Для каждого окна можно определить необходимый для удобной работы с ним размер изображения. Для этого в программе предусмотрена команда ВИД- МАСШТАБ открывающая одноименный диалог, где можно увеличить или уменьшить изображение в границах от 10 до 400%. Этот диалог содержит две опции: ПО ВЫДЕЛЕНИЮ и ПОЛЬЗОВАТЕЛЬСКИЙ.

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

5.9 Изменение относительных ссылок в абсолютные и наоборот

Иногда возникает необходимость изменения абсолютных ссылок в смешанные  или относительные. Такие преобразования можно выполнить автоматически с помощью клавиши F4. Для этого выделяют нужную клетку, в панели формул  курсором указывают на изменяемую ссылку и несколько раз нажимают клавишу F4.

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

6 Методические рекомендации

6.1 Загрузка файла с таблицей

Чтобы продолжить работу с книгой, содержащей созданную и сохраненную ранее таблицу, выполним команду главного меню: ФАЙЛ-ОТКРЫТЬ. В появившемся окне в поле Имя файла  зададим имя нужного файла (например, Vak.xls) и нажать ОК, либо выберем его с помощью двойного щелчка мыши из предложенного списка.

6.2 Открытие и упорядочивание окон

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

1) выполним команды главного меню ОКНО-НОВОЕ ОКНО и затем ОКНО-УПОРЯДОЧИТЬ-МОЗАИКА;

2) во втором окне щелкнем на ярлычке второго листа.

6.3 Формирование связанной таблицы

6.3.1 Перейдем на Лист2 во втором окне и сформируем там таблицу, представленную на рис.2. Заголовок таблицы введем обычным способом.

Заголовки двух первых столбцов можно полностью скопировать из таблицы на листе 1:

1) выделим клетки А3:В3 в таблице на первом листе (см.рис.1) ;

2) выполним команду главного меню ПРАВКА-КОПИРОВАТЬ;

3) выделим клетку A3 на втором листе ;

4) выполним команду ПРАВКА-ВСТАВИТЬ.

Заголовки столбцов при копировании сохранили формат оформления, поэтому в них используется установленный ранее перенос слов. Заголовки третьего и четвертого столбцов введем с клавиатуры,  предварительно задав для клеток  С3 и D3 разрешение переносить текст по словам. Используем изменение ширины столбца и высоты строки для равномерного расположения текста во всех столбцах.

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

6.3.3 Столбец 2 заполним соответствующими значениями из таблицы на первом листе. Используем для этого прием связывания через команду СПЕЦИАЛЬНАЯ ВСТАВКА:

1)     выделим клетки В4:В13 на первом листе;

2)     выполним команду ПРАВКА – КОПИРОВАТЬ;

3)     выделим клетку В4 на втором листе;

4)     выполним команду ПРАВКА–СПЕЦИАЛЬНАЯ ВСТАВКА- ВСТАВИТЬ СВЯЗЬ;

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

Рис.1 Фрагмент окна Excel, содержащий исходную таблицу на Листе1

Рис.2  Формируемая таблица на Листе 2

Рис. 3  Предупреждающее окно  при изменении зависимых данных

6.3.4 Количество животных в группе в третий столбец таблицы введем с клавиатуры.

6.3.5 Для нахождения Расхода вакцины на группу нужно Расход вакцины на одно животное из таблицы 1 умножить на количество животных в группе  из второй таблицы, т.е. использовать формулу  = С4*Лист1!C4.  При заполнении четвертого столбца применим одновременно прямое связывание таблиц и прием дублирования формулы. Для этого:

1) выделим диапазон клеток D4:D13 во второй таблице;

2) введем с клавиатуры формулу для определения расхода вакцины на группу животных, и нажмем < Сtrl + Enter>.

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

6.4 Переименование  и удаление  рабочих листов

1) щелкнем правой кнопкой мыши на ярлычке первого листа;

2) выполним команду ПЕРЕИМЕНОВАТЬ в открывшемся контекстном  меню листа;

3) введем новое имя, например Вакцина на одно животное. 

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

Все неиспользуемые листы рабочей книги удалим командой УДАЛИТЬ контекстного-меню листа.

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

1) Объясните назначение команды ОКНА-РАЗБИТЬ.

2) Как  открыть новое окно?

3) Для чего используется команда ОКНО-УПОРЯДОЧИТЬ?

4) Для чего используется комбинация клавиш Ctrl+F4?

5) Объясните отличие команд ПРАВКА-ВЫРЕЗАТЬ и ПРАВКА-КОПИРОВАТЬ.

6) Какими способами можно связать два рабочих листа?

7) Что происходит при изменении связанных данных на листе источнике, на листе-приемнике?

8) Как обозначаются абсолютные  ссылки на клетки?

9) Как вызвать контекстное меню листа?

10) Для чего переименовывают рабочие листы?

8 Рекомендуемая литература

5.1 Конспект лекций по курсу "Информатика".

5.2 Джек Наймершайм. Exсel 5.0 for Windows .-М. : Международные отношения, 1995 - 240с.

5.3 Грег Харвей. Exсel 5.0 для "чайников". - Киев : Диалектика,1995.- 288 с.

5.4 Наташа Николь. Электронные таблицы Excel 5.0.-М.: ЭКОМ., 1995. С.237-253.

9 Приложения

Таблица 1

Определение затрат труда при силосовании

Таблица 2

Себестоимость сельскохозяйственных культур

 Таблица 3

Расчет потребности  комбикормов

Таблица 4

Выполнение плана  заготовки  древесины

Таблица 5

Расчет премии мелиораторов

При перевыполнении нормы выработки до 10% (включительно) премия начисляется в размере 5% от з/п, если >10% , то 15%.