4. Связь таблиц
Для того, чтобы была возможность использовать в расчетах данные из нескольких таблиц в MSExcelможно осуществлять связи между таблицами путем использования внешних ссылок (адресов ячеек), содержащих помимо имени столбца и номера строки имя файла, данные из которого используются.
Задание № 6. Связывание таблиц
1. Сформируйте в MS Excel таблицу для расчета сумм стипендий студентам факультета ФЭУТ в соответствии с формой, представленной на рис. 4.1.
При расчетах необходимо учесть следующие условия:
стипендия не начисляется студентам, обучающимся на платной основе (т.е. тем, у которых в столбе Вид оплаты таблицы Список стоит значение 2;
студенты получают минимальную стипендию, если средний экзаменационный балл больше 3;
студенты, имеющие средний балл более 4,5, получают 50% надбавку к стипендии.
Рис. 4.1. Форма итоговой таблицы
Технология работы
Из папки Методические материалы\Информатика\03 Модуль 3\Файлы ПЗ-09 загрузите файл ФЭУТ.xls и сохраните его в папке Мои документы\Отчет ПЗ-09 под именем Связь_таблиц.xls.
Перейдите на чистый лист и назовите его Начисления.
Объедините ячейки А1:Е1 и введите название таблицы Ведомость начисления стипендии студентам ФЭУТ 1-го курса.
Объедините ячейки В2:С2 и введите текст Сумма минимальной стипендии:
В ячейку D2 введите значение минимальной стипендии 600 и определите формат этой ячейки как Денежный с двумя десятичными знаками (это устанавливается на вкладке Число диалогового окна Формат ячеек).
Оформить шапку таблицы. Для этого в ячейки А3:Е3 введите заголовки столбцов таблицы (см. рис. 4.1).
Столбец Номер зачетки заполнить данными. Чтобы не набирать повторно номера зачеток (они есть в ячейках В5:В16 на листе Список) выполните следующее:
на лист Начисления активизируйте ячейку А4 и введите знак = для определения формулы;
перейдите на лист Список (ярлык листа Начисления выделится подсветкой, а в строке формул появится ссылка на лист-источник в следующем виде Список!;
щелкните левой кнопокй мыши на ячейке В5 и нажмите клавишу ENTER;
перейдите на лист Начисления – в ячейке А4 появится значение 991490, а строке формул отобразится формула =Список!B5;
путем протягивания мышью ячейки А4 скопируйте формулу в остальные ячейки столбца А5:А15.
Аналогично заполните столбец Ф.И.О. данными. Для этого в ячейках В4:В15 листа Начисления установить ссылки на ячейки С5:С16 из листа Список.
Введите формулы для расчета стипендии:
на листе Начисления активизируйте ячейку С4 и вызовите Мастера функций (командами меню Вставка Функция или щелчком на кнопке
);в окне Мастер функций – шаг 1 из 2 в списке Категория выберите Логические, а в списке Функция – функцию ЕСЛИ, после чего щелкните на кнопке ОК;
во втором окне Мастера функций (рис. 4.2) задайте аргументы функции:
в поле Лог_выражение введите И(Список!D5=1;Успеваемость!F5>=3);
в поле Значение_если_истина введите $D$2;
в поле Значение_если_ложь введите 0.
При вводе адресов ячеек в операндах можно перейти на нужный лист и щелкнуть на нужной ячейке. Ее полный адрес в поле операнда появится автоматически;
нажмите кнопку ОК.
В результате в ячейке С4 появится значение, рассчитанное по формуле: =ЕСЛИ(И(Список!D5=1;Успеваемость!F5>=3);$D$2;0).
Здесь для задания логического выражения используется логическая функция И, которая возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, и возвращает ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.
Чтобы ссылка на ячейку D2 оставалась неизменной при копировании формулы, необходимо сделать ее абсолютной путем указания знака $ перед именем столбца и номером строки - $D$2.
Рис. 4.2. Диалоговое окно ввода аргументов функции
Путем протягивания мышью ячейки С4 скопируйте формулу в остальные ячейки столбца С5:С15.
Введите формулы для расчета надбавки к стипендии студентам:
на листе Начисления активизируйте ячейку D4 и вызовите Мастера функций;
в окне Мастер функций – шаг 1 из 2 в списке Категория выберите Логические, а в списке Функция – функцию ЕСЛИ, после чего щелкните на кнопке ОК;
во втором окне Мастера функций (рис. 4.2) задайте аргументы функции:
в поле Лог_выражение введите И(Список!D5=1;Успеваемость!F5>=4,5);
в поле Значение_если_истина введите $D$2*0,5;
в поле Значение_если_ложь введите 0;
нажмите кнопку ОК; в ячейке D4 появится значение, рассчитанное по формуле: =ЕСЛИ(И(Список!D5=1;Успеваемость!F5>=4,5);$D$2*0,5;0).
Путем протягивания мышью ячейки D4 скопируйте формулу в остальные ячейки столбца D5:D15.
Введите формулы для расчета общей суммы начислений каждому студенту:
в ячейку Е4 введите формулу: =C4+D4;
путем протягивания мышью скопируйте ее в диапазон ячеек Е5:Е15.
Таблица с полученными результатами показана на рис. 4.3.
Сохраните рабочую книгу в папке Мои документы\Отчет ПЗ-09 под именем Связь_таблиц.xls.
Рис. 4.3. Таблица с результатами связывания данных
ВЫВОДЫ
Табличный процессор MS Excel является мощным средством для моделирования и анализа данных.
MS Excel позволяет проследить изменение результата вычислений по формуле при изменении ее входных значений с помощью таблиц подстановки.
С помощью средства Подбор параметра есть возможность провести обратные вычисления: от заданного результата к набору входных значений, необходимому для его получения.
Для моделирования реальных процессов, когда приходится оценивать эффект, вызываемый одновременным изменением большого количества параметров, предназначено средство Диспетчер сценариев.
MS Excel позволяет связывать таблицы, расположенные на различных листах, и проводить в них различные вычисления.