Имя_Листа ! Адрес_Ячейки.
Синтаксис ссылки на ячейку из рабочего листа другой рабочей книги (двумерная внешняя ссылка) имеет вид:
[Имя_Рабочей_книги] Имя_Листа ! Адрес_Ячейки
Сведение на рабочем листе данных из ячеек нескольких листов одной книги выполняется с помощью трехмерных ссылок. Они могут быть использованы только как аргументы определенных функций, например: СУММ, СРЗНАЧ, СЧЁТ, СЧЁТЗ, МАКС, МИН и др. (подробнее см. встроенное справочное руководство).
Синтаксис формулы с трехмерной ссылкой имеет вид:
= Функция ( Диапазон листов ! Адрес_Ячейки ).
Синтаксис формулы с внешней трехмерной ссылкой имеет вид:
= Функция ( [Имя_Рабочей_книги] Диапазон листов ! Адрес_Ячейки ).
Диапазон листов содержит объединенные с помощью оператора диапазона (:) имена начального и конечного листов. Например, формула
= СУММ(Лист2 : Лист15 ! B5)
суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от Лист2 до Лист15 включительно.
Связывать данные можно различными способами: вручную (набирая информацию о связях), автоматически (указывая путь для связывания) или используя команду Специальная вставка. Результаты связывания во всех случаях будут одинаковыми.
Для получения наглядного представления о больших наборах чисел и о том, как эти наборы связаны между собой, целесообразно использовать диаграммы. Их построение помогает в нахождении тенденций и анализе закономерностей, которые нельзя обнаружить при обычном изучении массивов данных.
Диаграмма связана с данными, на основе которых она создана, и обновляется автоматически при их изменении.
MS Excel предлагает 11 типов стандартных диаграмм. Однако не существует четкого правила для определения, какая диаграмма лучше всего подходит для анализируемого набора данных. Обычно используется интуитивный подход – применяется тот тип диаграммы, который в данном случае является наиболее простым и наглядным.
Чтобы создать диаграмму, необходимо выделить интерпретируемые с ее помощью данные, затем воспользоваться Мастером диаграмм (запускается с помощью одноименной кнопки на панели инструментов Стандартная).
Мастер диаграмм выполняет построение диаграммы за 4 шага:
Шаг 1. Выбор типа и вида диаграммы.
Шаг 2. Задание ориентации данных.
Шаг 3. Установление (изменение) параметров диаграммы.
Шаг 4. Выбор расположения диаграммы.
Для получения более подробных сведений о диаграммах рекомендуем обратиться к разделу «Работа с диаграммами» встроенной справочной системы MS Excel.
Задачи Задача 3.1. Потребительский кредит
Предметная область: анализ вариантов кредитования. Условиями договора задаются: сумма кредита (S=1840 грн), размер годовой процентной ставки (i), срок кредита в месяцах (M), взимаемый однократно комиссионный сбор за расчетно-кассовое обслуживание (К):
Вариант |
M |
i |
К |
1 |
12 |
28% |
4,2% |
2 |
9 |
27% |
4,1% |
3 |
6 |
26% |
3,8% |
4 |
3 |
25% |
3,1% |
Отношения:
погашение долга по кредиту производится в конце каждого месяца равными взносами:
взнос = S / М;
начисление процентов осуществляется на фактические суммы долга на начало месяца:
проценты = остаток долга на начало месяца * i / М;
ежемесячные расходы по обслуживанию долга равны:
= взнос + проценты;
комиссионный сбор за расчетно-кассовое обслуживание:
= К * S
Требования:
разработать табличную модель обслуживания долга, реализовав на отдельных листах рабочей книги соответствующие вариантам кредитования таблицы (в качестве имен листов использовать обозначения сроков займа – 12, 9, 6, 3);
выполнить числовое и стилевое форматирование данных;
на пятом листе с именем Анализ свести итоговые суммы обслуживания кредита по каждому варианту и на отдельном листе выполнить графическую интерпретацию этих данных.
Методические указания к решению
При разработке табличной модели данной задачи целесообразно обратиться к решению задачи 1.3 (рис. 9).
На первом рабочем листе после заголовка ввести условия первого варианта кредитования и рассчитать оплату за расчетно-кассовое обслуживание.
Выполнить расчет ежемесячных расходов должника по обслуживанию долга (для обозначения месяцев использовать числа 1, 2, 3 …).
Вычислить расходы по займу как сумму значений столбца Обслуживание долга и оплаты за расчетно-кассовое обслуживание.
Выполнить числовое и стилевое форматирование данных.
Назначить активному листу новое имя «12».
Инструкция по переименованию листа
выбрать двойным щелчком мыши нужный ярлычок листа;
ввести новое имя поверх старого.
Сделать 3 копии листа с именем «12».
Инструкция по копированию листа (графический способ)
установить курсор на ярлыке копируемого листа;
при нажатой клавише Ctrl левой кнопкой мыши выполнить графическое перетаскивание ярлыка листа в нужное положение;
отпустить кнопку мыши, и только после этого – клавишу Ctrl.
Переименовать созданные копии в «9», «6», «3».
Изменить исходные данные на скопированных листах.
Удалить лишние строки, оставив количество месяцев в соответствии с вариантом расчета.
Лист, следующий за листами с вариантами расчета, назвать «Анализ».
На листе «Анализ» создать таблицу вида:
|
A |
B |
1 |
Сравнение вариантов возврата долга |
|
2 |
Вариант |
Сумма расходов |
3 |
Вариант 1 (12 мес.) |
|
4 |
Вариант 2 (9 мес.) |
|
5 |
Вариант 3 (6 мес.) |
|
6 |
Вариант 4 (3 мес.) |
|
Рис. 20. Сравнение вариантов кредитования
«Вытащить» суммы расходов по займу, соответствующие разным вариантам кредитования, создав формулы с двумерными ссылками на ячейки листов «12», «9», «6», «3».
Инструкция по созданию двумерной ссылки
выбрать зависимую ячейку;
ввести знак равенства;
щелкнуть по ярлычку листа, в котором находится влияющая ячейка-источник, и выделить ее;
завершить ввод формулы нажатием клавиши Enter.
Выполнить графическую интерпретацию данных листа «Анализ» на основе гистограммы.