макет MS Excel. Лабораторная работа 1 Редактирование рабочей книги. Построение диаграмм
 Скачать 0.97 Mb.
|
Лабораторная работа № 6 Работа с функциями ссылки и массиваЦель работы: изучение возможностей связывания данных и автоматизации их обработки. Задание 1 Определить стоимость объектов недвижимости (табл. 5.10) на основе их первоначальной стоимости и таблицы скидок за продолжительность эксплуатации (табл. 5.11), т.е. начиная с 1 года – 5%-ая скидка, с 5 лет – 10%-ая скидка и т.д. Таблица 5.10
Таблица 5.11
Методика выполнения работы Создать представленные таблицы на одном рабочем листе Excel. Присвоить диапазонам ячеек имена: выделить блок ячеек, содержащий значения начальной стоимости и выполнить команду Формулы Присвоить имя. Задать имя Первоначальная_стоимость (обратите внимание, что в именах не должно быть пробелов); выделите блок ячеек, содержащих значения года создания и выполните команду Формулы Присвоить имя. Задать имя Год_создания; таким же образом присвоить имя диапазону Год эксплуатации Выделите блок ячеек, содержащий таблицу 5.11, и присвойте ему имя Справочная_таблица. Далее воспользуемся функцией ГПР. Функция ГПР берет значение из первого диапазона, сравнивает со значениями первой строки второго диапазона, находит равное ему или ближайшее наибольшее, а затем в качестве результата берет значение из указанной строки второго диапазона, в нашем примере - строка 2. Подобным образом работает функция ВПР, но она работает с диапазонами ячеек, расположенными вертикально. В первую ячейку, находящуюся на пересечении строки с годом 2000 и столбца – год 2005 введите формулу =Первоначальная_стоимость*(1-ГПР(Год_эксплуатации-Год_создания;Справочная_таблица;2)). скопируйте формулу с помощью автозаполнения в другие вычисляемые ячейки Задание 2 Необходимо создать две связанные таблицы для учета отдыхающих в Доме отдыха. В зависимости от категории комнаты и количества дней проживания рассчитать сумму оплаты. Выполнить поиск, и подстановку значений в таблицу используя функцию ПРОСМОТР. При этом количество дней проживания определяется как разница между днем отъезда и днем заезда. Методика выполнения работы Создайте новую рабочую книгу под именем «Дом отдыха». Переименуйте первый лист в «Сведения» и заполните лист (рис.5.71). Присвойте имена диапазонам командой Формулы Присвоить имя. a) А2:А31 - комнаты б) В2:В31 - категория в) D2:D7 – цена_категория г) Е2:Е7 – цена д) G2 – курс е) G5 – сегодня  Рисунок 5.71 – Таблица для заполнения Перейдите на лист 2, переименуйте его в «Заезды». Подготовить таблицу следующего вида (рис.5.72):  Рисунок 5.72 – Таблица для заполнения Заполните столбец D (номер комнаты) данными по своему усмотрению, на основании данных листа «Сведения». В ячейку Е2 введите формулу =ПРОСМОТР(D2;комнаты;категория) и скопировать ее вниз до 14 строки. В ячейку F2 для определения этажа введите формулу =ЛЕВСИМВ(D2;1) и скопируйте ее вниз. В ячейку G2 для определения количества дней проживания вводим функцию, =ЕСЛИ(C2=0;сегодня-B2;C2-B2) В ячейку H2 для определения суммы оплаты в $ введем формулу =ПРОСМОТР(E2;цен_категория;цена) В ячейку I2 для определения суммы оплаты в рублях введем формулу =H2*Курс Создать сводную таблицу, позволяющую определить общую сумму оплаты по месяцам заезда. Для этого установите курсор внутри таблицы «Заезды» и выполните команду Вставка Таблица Сводная таблица. Откроется макет для размещения полей. На ось строк перетащите поле Дата прибытия, а в область данных поля: Сумма в рублях и Сумма в $ (рис. 5.73).  Рисунок 5.73 – Макет сводной таблицы Установите активную ячейку в поле даты на начальное значение и выполните команду Данные Структура Группировать. Заполните диалоговое окно (рис. 5.74) так, чтобы выполнялась группировка по месяцам и нажать ОК.  Рисунок 5.74 Сохраните файл. Самостоятельное задание 1. В таблице приведена выработка рабочих за 3 дня. Создать на рабочем листе таблицу. На новом листе определить выработку каждого работника по дням с помощью функции СУММЕСЛИ и оформив в виде таблицы.
2. Найти в Excel решение уравнения АХ=В, если все его аргументы – массивы.
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||