СУБД / Информатика.Ч1_2Ed_fixed

в окне Изменение ряда введите имя следующего ряда – «Северное

отделение». Нажмите на кнопку и выделите соответствующий диапазон данных. Аналогичным образом добавьте третий ряд в диаграмму, измените название диаграммы и оси ординат. Результат представлен на рис. 2.10.

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

4.Скопируйте уже созданную гистограмму на текущий лист через буфер обмена. Щелкните правой кнопкой мыши на скопированной гистограмме и выберите команду Изменить тип диаграммы…

контекстного меню. Выберите тип

диаграммы Объемная цилиндрическая и измените макет диаграммы, соответствующий представленной на рис. 2.11.

Примечание. Ненужные элементы макета диаграммы можно просто удалить. Для этого элемент нужно выделить и нажать клавишу Delete.

Измените поворот объемной фигуры – установите по оси Х и Y поворот в 300. Значение перспективы отображения гистограммы остается без изменений. Если потребуется, измените размеры гистограммы и надписей.

5.Перейдите на лист Excel «Банковские вклады». Выделите диапазон ячеек, который занимает таблица (включая названия полей). Перейдите на вкладку ленты Вставка и в группе Таблицы нажмите на кнопку Вставить сводную таблицу . В появившемся диалоговом окне нажмите кнопку ОК. В рабочей книге Excel появится новый рабочий лист с областью для вызова дока создания сводной таблицы.

111

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

Отформатируйте сводную таблицу, как показано на рис. 2.12 и переименуйте рабочий лист, задайте имя «Сводная таблица».

ПРАКТИЧЕСКАЯ РАБОТА 2. СОЗДАНИЕ И ФОРМАТИРОВАНИЕ ДИАГРАММ. ИСПОЛЬЗОВАНИЕ ФИЛЬТРОВ

ЗАДАНИЕ 1

1.Создайте таблицы Excel с исходными данными для расчета 30летней ипотечной ссуды со ставкой 12% годовых при начальном взносе 20% и ежемесячной (ежегодной) выплате на новом листе Фи-

нансовые вычисления.

112

Рис. 2.13.

2.Добавьте необходимые формулы в таблицу. Используйте функцию ПЛТ(ставка; кпер; пс; бс; тип) для расчета постоянной периодической выплаты при фиксированной процентной ставке.

Рис.2.14.

3.Настройте формулы для автоматического изменения текстовых надписей количества месяцев и лет. Для этого в ячейки C8 и С9 введите следующие формулы:

=ЕСЛИ(ЗНАЧЕН(ПРАВСИМВ(B8))=1; "месяц"; ЕСЛИ(И( ЗНАЧЕН(ПРАВСИМВ(B8))>=2; ЗНАЧЕН(ПРАВСИМВ(B8))<=4); "месяца"; "месяцев"))

=ЕСЛИ(ИЛИ(B9=1; B9=21); "год"; ЕСЛИ(ИЛИ(И(B9>=2; B9<=4);

113

И(B9>=22; B9<=24)); "года"; "лет"))

МЕТОДИКА ВЫПОЛНЕНИЯ

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

2.Для ввода простых формул можно воспользоваться мышью, указав нужные ячейки в порядке выполнения операций над их значениями. Чтобы добавить более сложные формулы в ячейки листа MS Office Excel, выделите нужную ячейку и нажмите на кнопку Вставить функцию в строке формул. Появиться окно, в котором нужно выбрать категорию, к которой относиться формула и саму формулу. Того же самого результата можно добиться с помощью команд группы Библиотека функций вкладки ленты Формулы.

Примечание. Во многих формулах используется знак доллара $. Он указывает на абсолютный адрес ячейки (диапазона ячеек). Это означает, что при копировании формулы данный адрес не изменяется. Самый быстрый способ превратить относительный адрес в абсолютный – выделить адрес ячейки в строке формул и нажать клавишу F4.

3.Так как исходные данные в таблице могут быть изменены пользователем, то хорошо автоматизировать те изменения, которые важны для создания печатного варианта таблицы. Например, текстовые надписи сроков выплаты ссуды. Для этого выделите ячейку С8 и выберите функцию ЕСЛИ из категории Логические. Далее можно воспользоваться мастером задания параметров функции ЕСЛИ или ввести формулу непосредственно в строку формул. Функции ЗНАЧЕН и ПРАВСИМВ относятся к категории Текстовые. При вводе длинных формул можно увеличить пространство ввода строки фор-

мул щелчком на кнопке Развернуть строку формул .

ЗАДАНИЕ 2

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

114

Рис. 2.15.

2.Задайте формулу для вычисления чистого текущего объема вклада (инвестиции) с помощью формулы ЧПС(ставка; значение1; значе-

ние2; ...).

Рис. 2.16.

3.С помощью надстройки Подбор параметра вычислите, при какой годовой учетной ставке чистый текущий объем вклада равен размеру ссуды.

МЕТОДИКА ВЫПОЛНЕНИЯ

1.На том же самом листе Финансовые вычисления, где Вы выполняли первое задание, создайте вторую таблицу. Скопируйте форматы ячеек с таблицы первого задания.

2.Введите формулу для вычисления чистого текущего объема вклада. Также скопируйте формулу на основе логической функции ЕСЛИ в ячейку С24.

3.Установите в ячейке В25 значение 0%. Перейдите на вкладку ленты

Данные и в группе Работа с данными нажмите на кнопку Анализ

«что-если» . В выпадающем списке выберите команду Подбор параметра. В появившемся диалоговом окне установите необходимые ссылки на ячейки и требуемое конечное значение приближения.

115

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

Нажмите кнопку ОК и текущее значение в ячейке В25 зафиксируется.

ЗАДАНИЕ 3

1.Создайте таблицы Excel с исходными данными автоматического выбора варианта сделки: выдачи ссуды или размещения этой же суммы в банке под проценты.

Рис. 2.17.

2.Задайте формулу для вычисления объема вклада на основе постоянных периодических платежей с помощью формулы ПС(ставка; кпер; плт; бс; тип).

116

Рис. 2.18.

В ячейки С32 и В36 введите следующие формулы

=ЕСЛИ(ИЛИ(B32=1; B32=21); "год"; ЕСЛИ(ИЛИ(И(B32>=2; B32<=4); И(B32>=22; B32<=24)); "года"; "лет"))

=ЕСЛИ(B31<B35; "Выгодно дать деньги в долг"; ЕСЛИ(B35=B31; "Варианты равносильны"; "Выгоднее деньги положить под проценты"))

3. Настроить сценарии выполнения вычислений.

Рис. 2.19.

МЕТОДИКА ВЫПОЛНЕНИЯ

1.На листе Финансовые вычисления создайте таблицу и внесите данные представленные на рис. 2.17. Скопируйте форматы ячеек с таблицы из первого или второго задания.

2.Введите формулу для вычисления объема вклада на основании постоянных платежей в ячейку В35 отформатированной таблицы. Так-

117

же скопируйте формулу в ячейку С32 для изменения текстовой надписи срока возвращения ссуды.

=ЕСЛИ(ИЛИ(B32=1; B32=21); "год"; ЕСЛИ(ИЛИ(И(B32>=2; B32<=4); И(B32>=22; B32<=24)); "года"; "лет"))

В ячейку В36 добавьте формулу отображающую решение, которое следует из результата анализа доходности сделки.

=ЕСЛИ(B31<B35; "Выгодно дать деньги в долг"; ЕСЛИ(B35=B31; "Варианты равносильны"; "Выгоднее деньги положить под проценты"))

3.Часто бывает удобно проанализировать ситуацию для нескольких возможных вариантов параметров. В MS Office Excel имеется возможность задания сценариев расчетов с одновременным автоматическим составлением отчета. Для создания сценария расчета перейдите на вкладку ленты Данные и в группе Работа с данными выберите

из списка Анализ «что-если» команду Диспетчер сценари-

ев… . Нажмите на кнопку Добавить и в открывшемся окне задайте следующие параметры сценария:

Название сценария - ПС1

Изменяемые ячейки - B32:B33 В следующем окне введите

Значение изменяемой ячейки $B$32 - 5

Значение изменяемой ячейки $B$33 - 2500

Таким образом, один из трех сценариев расчета будет создан. Не закрывая окна диспетчера сценариев аналогичным образом добавьте еще два сценария (рис. 2.19).

После создания всех трех сценариев нажмите на кнопку Отчет в окне диспетчера сценариев и установите в поле Ячейки результата адрес В35. Нажмите кнопку ОК. Отчет должен появиться на новом листе (не забудьте изменить авторскую надпись).

Сохраните изменения рабочей книги MS Office Excel, в которой выполнялась данная практическая работа.

118

КОНТРОЛЬНЫЕ ЗАДАНИЯ ПО ТЕМЕ 3

1.Оформить на листе общие сведения о планете Земля

Указания по выполнению:

вячейках С2 и С3 пробелы вручную не ставить;

вячейке С4 выравнивание по правому краю вручную не производить.

2.Оформить на листе данные о распределении суши и воды на земном шаре

Указания по выполнению:

вячейках С4, , С6, E4, E5, E6, G4, G5 и G6 символ «%» вручную не вводить;

вячейке С4, С5, E4, E5, G4, G5 и F6 цифру 0 после запятой вручную не вводить.

3.Оформить на листе данные об океанах на земном шаре

Указания по выполнению:

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

119

4.Оформить фрагмент листа, который при предварительном просмотре будет иметь вид, представленный ниже

5.В школе проводиться шахматный турнир, в котором участвуют 15 учащихся. Соревнования проводятся по круговой системе – каждый играет с каждым по одному разу. Результаты заносятся в обычную турнирную таблицу с диагональю, заполненной «крестиками»

За победу участнику дается 1 очко, за ничью – 0.5, за проигрыш

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

6.На предприятии персонал работает по графику: 12-часовая дневная смена, на следующий день – 12-часовая ночная смена, затем двое суток отдыха. Составить график для четырех человек на 8 дней

120