5.3Использование технологии связывания таблиц

Вэтом разделе вы освоите:

sтехнологию связывания таблиц командой Правка/Специальная вставка;

sтехнологию прямого связывания.

& Связь между таблицами организуют в том случае, если:

· данные одной таблицы используются или участвуют в вычислениях, произ-

водимых в другой таблице;

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

Связь можно установить между таблицами, находящимися на разных лис-

тах одной книги, в разных книгах.

Связанные таблицы – таблицы, содержащие формулы, в которых присут-

ствуют ссылки на ячейки других таблиц (внешние ссылки).

Виды внешних ссылок:

·ссылка на ячейку на другом листе (например, =Лист2!D7);

·ссылка на ячейку в другой рабочей книге (например, [Книга2.xls]Лист2! D7).

Ссылка на ячейку неоткрытой рабочей книги в формуле указывается в одинарных кавычках без пробелов с заданием полного пути местонахождения файла, (например, ='D:\Мои документы[Книга2.xls] Лист2'!D7).

Внешние ссылки задают следующими способами:

· вводом их с клавиатуры или мышью при построении формул(прямое связы-

вание);

· командой Правка/Специальная вставка.

При связывании таблиц из разных книг удобно держать их открытыми в одном окне Excel. Это выполняется командой Окно/Расположить.

Рассмотрим работу со связанными таблицами на примерах.

Задание 1. Используя данные книги Амортизация, принять решение о закуп-

ке того оборудования, чья амортизация за год меньше300 тыс. руб. Результат

49

представить на отдельном листе в виде связанной таблицы Закупка структуры:

Р е к о м е н д а ц и и п о в ы п о л н е н и ю

·Откройте книгу Амортизация.

·Переименуйте Лист1 в Исходный, а Лист3 – в Результат.

·На листе Результат введите название таблицы Закупка.

·Перейдите в режим отображения формул.

·Заполните столбец Инвентарный номер на листе Результат данными с листа Исходный. Для этого:

-скопируйте диапазон ячеек А2:А5 на листе Исходный в буфер обмена.

- на листе Результат в ячейке 2 Авыберите команду Прав-

ка/Специальная вставка и в появившемся одноименном окне нажмите

[Вставить связь].

Примечание. Связывание таблиц можно осуществить, если ввести знак «=» и щелкнуть по ячейке другой таблицы, затем нажать [Enter] или продолжить вводить нужную формулу.

·Отрегулируйте ширину столбца Инвентарный номер.

·В ячейку В2 внесите название второго столбца таблицыРешение о закуп-

ке, да/нет.

· Заполните столбец Решение о закупке, да/нет на листе Результат дан-

ными с использование данных столбцаАмортизация оборудования за год

листа Исходный. Для этого в ячейку 3В введите формулу с использованием

функций – ЕСЛИ:

-в окне Аргументы функции введите аргументы:

-Лог_выражение – нажмите ячейку Е3 на листе Исходный и наберите требуемое условие <300000;

-Значение_если_истина – введите да;

-Значение_если_ложь – нет. Нажмите [ОК]. Сформированная функция

ЕСЛИ показана на рис 5.27.

50

Рис. 5.27 Аргументы функции ЕСЛИ

· Используя автозаполнение, скопируйте введенную в В3 формулу в ячейки В4

и В5.

· Выполните обрамление таблицы Закупка, выделив её и нажав кнопку

[Все границы]. Результат выполнения задания в режиме отображения формул представлен на рис. 5.28.

Рис. 5.28 Фрагмент рабочего листа с результатом выполнения задания 1

· Отключите режим отображения формул.

Задание 2. Убедитесь в наличии динамической связи между таблицами

Амортизация оборудовании и Закупка.

Р е к о м е н д а ц и и п о в ы п о л н е н и ю

·Перейдите на листИсходный, внесите произвольные изменения данных столбцов 1 и 5 таблицы Амортизация оборудования.

·Убедитесь в наличии соответствующих изменений на листе Результат.

51

·Отмените изменения данных на листе Исходный кнопку [Отменить].

·Сохраните книгу Амортизация.

Задание 3. Создать таблицы Прайс-лист, Заказ и Калькуляция (рис. 5.29) в

книгах с одноименными названиями. На основании данных таблицПрайс-

лист и Заказ произвести вычисления в столбцеИтого таблицы Калькуля-
ция.
		Прайс-лист
	Наименование	Стоимость единицы, тыс. руб.
	Товар 1	4
	Товар 2	6
	Товар 3	10
		Заказ
	Наименование	Количество единиц
	Товар 1	50
	Товар 2	200
	Товар 3	100
		Калькуляция
	Наименование	Итого, тыс. руб.
	Товар 1	?
	Товар 2	?
	Товар 3	?

	Рис. 5.29 Вид таблиц Прайс-лист, Заказ, Калькуляция
	Р е к о м е н д а ц и и п о в ы п о л н е н и ю
·	Создайте три новых книги, дав им имена соответственно: Прайс-лист, За-
каз, Калькуляция.
·	Расположите окна открытых книг на экране рядом командойОк-

но/Расположить/Рядом.

Примечание. Все три книги должны быть открыты в одном окне Excel.

·Создайте таблицу Прайс-лист (см. рис. 5.28) в книге Прайс-лист.

·В книгах Заказ и Калькуляция перейдите в режим отображения формул.

·Создайте таблицы Заказ и Калькуляция. В книгах Заказ и Калькуляция

заполните первые столбцы данными из таблицыПрайс-лист по технологии

52

связанных таблиц (см. рекомендации к заданию 1).

· Заполните столбец Итого на таблицы Калькуляция данными с использо-

вание данных книг Прайс-лист и Заказ. Для этого в ячейку В3 таблицы Каль-

куляция введите расчетную формулу следующим образом:

-введите знак ( = );

-щелкните мышью по ячейке В3 таблицы Прайс-лист,

-введите знак умножения( * );

-щелкните мышью по ячейке В3 таблицы Заказ;

-нажмите [Enter]. Сформируется формула, в которой по умолчанию ис-

пользуются абсолютные ссылки.

·Для обеспечения возможности автозаполнения преобразуйте в получившейся формуле абсолютные ссылки в относительные, убрав знаки «$» вручную или используя клавишу [F4].

·Используя автозаполнение, скопируйте введенную в В3 формулу в ячейки В4 и В5. Результат выполнения задания в режиме отображения формул пред-

ставлен на рис. 5.30.

Рис. 5.30 Фрагмент копии экрана с результатом выполнения задания 3

·Отключите режим отображения формул в таблицах Заказ и Калькуляция.

·Убедитесь в наличии динамической связи между таблицамиПрайс-лист,

53

Заказ и Калькуляция, изменяя данные исходной таблицы Прайс-лист.

· Сохраните книги Прайс-лист, Заказ и Калькуляция.

Задание 4. Представить преподавателю результаты работы в папкеExcel с

файлами Амортизация (листы Исходный и Результат), Прайс-лист, Заказ

и Калькуляция.

	Задания для самостоятельной работы
Задание 1.				В новой книге, на разных листах Работники и Начисления соз-
дать таблицы структуры, приведенной на рис. 5.31.
						Сведения о работниках
			Фамилия			Стаж, лет	Категория (1 или 2)
						Начисления за январь
	Фамилия			Оклад, тыс.		Премия,	Надбавка,		Итого начислено,
				руб.		тыс. руб.	тыс. руб.		тыс. руб.
					Рис. 5.31 Исходные данные задания
Задание 2.				Заполнить произвольными			данными4 строки		исходной таблицы
Сведения о работниках.

Задание 3. В таблице Начисления за январь столбец Фамилия заполнить данными из таблицы Сведения о работниках, используя технологию связы-

вания.

Задание 4. Столбец Оклад заполнить с учетом: минимальный оклад 400 тыс.

руб.; если сотрудник имеет категорию 1, то оклад его минимальный; если кате-

горию 2 – оклад в полтора раза больше.

Задание 5. Столбец Премия рассчитать в размере 30 % от оклада.

Задание 6. Столбец Надбавка рассчитать в размере20% от оклада тем со-

трудникам, которые имеют категорию 2 и стаж работы более 15 лет.

54

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

1.С какой целью устанавливается связь между таблицами в Excel?

2.Дайте определение внешней ссылке.

3.Как осуществляется связь между таблицами в Excel?

4.Каково назначение команды Правка/Специальная вставка.

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

6.Как осуществляются связывание с другой рабочей книгой?

Индивидуальные задания

1. В книге Выдача на листахКлиенты, Выдача сформировать таблицы,

структуры, представленной на рис. 5.32.

Информация о клиентах

			ФИО клиента		Сумма кредита, млн.		Были ли задолженности по
						руб.		выплате, да/нет
	1					2	3
			…			…		…
						Решение о выдаче кредита
			ФИО клиента			Выдавать ли кредит в последующем, да/нет
		?					?
					Рис. 5.32 Структуры исходных таблиц
2. Заполнить столбец 1						исходной таблицы Информация о клиентах
	произвольными данными, а столбцы 2 и 3 – согласно варианту, заданному
	преподавателем из табл. 5.2.
								Таблица 5.2
						Задания для расчета
			Вариант			Значения		Значения
						столбца 2		столбца 3
			1			6; 8; 4; 2; 9		нет; да; нет; да; да
			2			2; 5; 4; 2; 9		да; да; нет; да; да
			3			8; 8; 4; 2; 10		нет; нет; нет; да; да
			4			10; 8; 4; 2; 9		да; да; нет; да; да
			5			3; 8; 5; 2; 9		нет; да; нет; да; да

55

6	4; 7; 4; 2; 9	нет; да; нет; нет; да
7	3; 8; 10; 2; 9	нет; нет; нет; да; да
8	8; 8; 4; 2; 9	нет; да; нет; да; да
9	6; 8; 4; 2; 9	нет; нет; нет; да; да
10	14; 8;4; 2;9	нет; да; нет; да; да
11	13; 8;4; 2;9	нет; нет; нет; да; да
12	2; 18;4; 2;9	да; да; нет; да; да
13	5; 8;4; 2;9	нет; да; нет; да; да
14	6; 8;10; 2;9	нет; да; нет; да; да
15	15; 8;4; 2;9	да; нет; нет; да; да

3.Таблицы Информация о клиентах и Решение о выдаче кредитасвя-

зать по столбцу ФИО клиента.

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

млн. руб., то ему кредит не выдавать.

5.Построить гистограмму, отражающую суммы кредитов, выданных клиентам.

56