ВУЗ: Пермский государственный технический университет
Категория: Учебное пособие
Дисциплина: Информатика
Добавлен: 20.10.2018
Просмотров: 6706
Скачиваний: 7
56
Рисунок 43. Вставка функции ПЛТ
В поле Ставка укажите месячную ставку, т.е. В2/В4;
В поле Кпер – необходимо указать общее количество периодов, т.е. В5;
В поле Пс (Приведенная стоимость) – необходимо указать запрашиваемую сумму кредита;
Бс – баланс наличности, который нужно достичь после последней выплаты, в нашем случае это 0;
Тип – 0 – выплаты производятся в конце периода, 1 – выплаты производятся в начале периода.
Итог, который Вы получите, должен быть со знаком минус, т.к. это выплаты, а не поступления де-
нежных средств.
1.3.4.
Абсолютная и относительная адресация
Задание 63. Применение абсолютной адресации
Перейдите в книге на Лист 3 и назовите его Потребление воды. Введите данные показаний счетчика,
построив таблицу:
Рисунок 44. Пример таблицы
Внимание! Значения стоимости м³ холодной и горячей воды необходимо разместить в отдельных от
текста ячейках, т.к. они будут использоваться в формулах по расчету Суммы к оплате.
Потребление воды рассчитывается как разность между показаниями счетчика текущего месяца и
предыдущего:
Активизируйте D9, введите формулу: =В9-В8 и нажмите Enter.
57
С помощью точки автозаполнения рассчитайте потребление холодной воды за оставшиеся месяцы.
Таким же образом рассчитайте Потребление горячей воды.
Сумма к оплате за ХВ за январь рассчитывается как произведение количества потребленной воды на
стоимость м3. Формула для нашего примера:
Активизируйте ячейку F9;
Введите формулу: =D9*E4 и нажмите Enter;
Попробуем протянуть формулу с помощью точки автозаполнения:
Вернитесь в ячейку F9, наведите мышь на правый нижний угол ячейки, поймайте указатель мыши в
виде черного креста, удерживая левую клавишу мыши, протяните формулу до конца таблицы.
Рисунок 45. Необходимость применения абсолютной адресации
Убедитесь, что автозаполнение не сработало, разберемся почему:
Активизируйте ячейку F9, в строке формул должна отобразиться формула: =D9*E4;
Активизируйте ячейку F10. Excel, применяя автозаполнение, изменил формулу и сейчас значение
ячейки F10 рассчитывается как =D10*E5. То есть, все ячейки, которые используются в формуле при копиро-
вании формулы автозаполнением вниз, также сдвигаются вниз. В нашем случае движение вниз по столбцу D
– нужно, т.к. за каждый месяц разное значение потребления воды, но движение по столбцу E – не нужно т.к.
умножать нужно всегда на Е4.
Решение:
Вернитесь в ячейку F9, посмотрите в строку формул: =D9*E4.
В формуле необходимо найти ячейку, которая при копировании не должна меняться. Это Е4.
В строке формул установите курсор в формуле между Е и 4, а затем нажмите на клавиатуре кла-
вишу F4, формула примет вид =D9*$E$4. Знаки $ для Excel означают закрепление этой ячейки или абсо-
лютная адресация, при копировании данной формулы точкой автозаполнения, эта ячейка меняться не будет.
Нажмите Enter, вернитесь в ячейку F9 и протяните вниз точку автозаполнения. Выделите ячейку
F10 – обратите внимание на формулу.
Задание 64. Рассчитайте сумму к оплате за горячую воду
Заполните столбец Сумма к оплате за ГВ с применение абсолютной адресации и столбец Итого к
оплате – как сумму по столбцам F и G.
Сохраните файл в Вашей папке под именем «Функции».
1.4. Диаграммы и графики в MS Excel
Огромным достоинством Excel является возможность построения графиков и диаграмм на базе
имеющихся табличных данных. Диаграммы могут быть самые различные: круговые, столбчатые, графики,
трехмерные и т.д.
58
До построения диаграммы необходимо представить, как будут отображаться данные. На основе этой
таблицы попробуем построить следующие диаграммы:
Гистограмма – будет столбиками отображать за каждый месяц расход холодной и горячей воды. По
этой диаграмме можно проследить динамику расхода холодной и горячей воды.
Круговая диаграмма – может быть построена только по одному типу данных, например, только по
холодной воде. Круговые диаграммы строят, чтобы увидеть долю каждого элемента в общей сумме.
График – линии будут отображать динамику расхода холодной и горячей воды.
1.4.1. Построение Гистограммы
Гистограммы используются для демонстрации изменения данных за определенный период времени
или для иллюстрирования сравнения объектов.
Задание 65. Постройте гистограмму для потребления воды
Откроем файл «Функции» на листе Потребление воды. Отразим на диаграмме расход холодной и
горячей воды за полгода. Для этого необходимо выделить диапазон ячеек, по которому будет строиться диа-
грамма:
Выделите ячейки А9:А14, А8 – выделять не надо, т.к. нет значений расхода воды.
Вместе с этим диапазоном необходимо выделить диапазон D9:Е14. Т.к. между этими диапазонами
находятся ячейки, которые выделять не нужно, то выделите А9:А14, нажмите и держите клавишу Ctrl и
выделите D9:Е14.
Откройте вкладку Вставка…Гистограмма…Гистограмма с группировкой.
Гистограмма построена, необходимо ее оформить:
Название:
Выделите диаграмму, откройте вкладку Макет группу Подписи…Название диаграммы…Над диа-
граммой. В появившейся над диаграммой рамке введите название «Потребление воды».
Легенда:
Столбцы с данными закрашены разным цветом, однако в легенде вместо названий подписано: Ряд 1 и
Ряд 2.
Выделите диаграмму, откройте вкладку Конструктор…группа Данные…команда Выбрать данные.
В диалоговом окне слева выберите Ряд 1 и нажмите кнопку Изменить. Когда курсор мигает в поле Имя
ряда щелкните по ячейке D7 – она содержит нужное название, для Ряда 2 примените название из ячейки Е7.
Рисунок 46. Гистограмма потребления воды
Для оформления диаграммы используйте:
Вкладка Конструктор – содержит возможные макеты оформления диаграмм и стили.
Вкладка Макет – позволяет ввести название диаграммы, подписи осей, редактировать линии сетки и
область построения.
60
54
87
65
44
98
27
33
22
52
41
28
0
20
40
60
80
100
120
Потребление воды
Потребление ХВ м3
Потребление ГВ м3
59
Вкладка Формат - позволяет менять заливку, границы объектов диаграммы, задавать размер области
диаграммы и др.
Работа с диаграммами подробно описана в части, посвященной MS Word.
1.4.2.
Построение круговой диаграммы
Круговая диаграммы отображает долю элемента в общей их сумме. По данной таблице круговая диа-
грамма может показывать: в каком месяце за полгода расход воды был наибольший или наименьший.
Задание 66. Постройте круговую диаграмму для расхода холодной воды
Построим круговую диаграмму, которая характеризует расход холодной воды за полгода.
Выделите диапазон А9:А14, нажмите и держите Ctrl и выделите диапазон D9:D14.
На вкладке Вставка выберите круговую диаграмму.
Приведите диаграмму в соответствие с образцом.
Рисунок 47. Круговая диаграмма
Задание 67. Работа с графиками
Постройте график, который отображает динамику расхода горячей и холодной воды за полгода.
1.5. Фильтр, сортировка, промежуточные итоги
Откройте новый файл, на листе 1 создайте таблицу:
Рисунок 48. Список сотрудников
В столбце Заработная плата обозначение р. – выполнено через денежный формат!
Задание 68. Подготовка данных
60
Добавьте в книгу Лист 4. Выделите весь лист, нажав на кнопку, обозначенную на рис. 49 цифрой 1.
Скопируйте выделение в буфер обмена, перейдите на лист 2 и вставьте содержимое буфера, начиная с ячей-
ки А1, перейдите на Лист 3 и снова вставьте содержимое буфера, начиная с ячейки А1, с листом 4 сделайте
тоже самое.
Начнем работу с листа 1.
1.5.1.
Фильтрация
Переименуйте Лист 1 в «Фильтр». Фильтр – инструмент, который позволяет из массива данных вы-
брать элементы, соответствующие заданному критерию.
Задание 69. Фильтрация данных
Для участия в новом проекте необходимо выбрать сотрудников, которые соответствуют требованиям:
Высшее профессиональное образование, опыт работы не менее 3-х лет.
Выделите строку с заголовками таблицы – строка 2;
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Фильтр. Около каждого за-
головка таблицы появится стрелка фильтра;
Выполним первую выборку – оставим только сотрудников с высшим образованием. Разверните
свиток фильтра у заголовка Образование, оставьте галочку только у группы ВПО и нажмите ОК.
Результат – данные, которые не соответствуют требованию – скрыты.
Выполните вторую фильтрацию по опыту работы, здесь необходимо в фильтре выбрать команду
Числовые фильтры…Больше или равно и в окне задать больше или равно 3.
Выбрано 4 сотрудника, которые соответствуют требованиям.
Задание 70. Работа с фильтрованными данными
Рассчитаем их суммарную заработную плату.
Для этого необходимо скопировать отфильтрованную таблицу ниже текущей: выделим диапазон
A2:F12, скопируем в буфер обмена и вставим, начиная с ячейки А16. Сейчас можно к таблице применить
функцию суммирования.
Рисунок 49. Копирование отфильтрованной таблицы
Чтобы вернуть таблице первоначальный вид необходимо развернуть фильтры, по которым произво-
дилась выборка и выбрать команду Снять фильтр с…
Задание 71. Использование Фильтра и Среднего значения
Рассчитайте среднее значение руководящего состава предприятия, используя фильтр и функцию
среднего значения. Таблицу с результатами фильтрации вставьте, начиная с ячейки А23.
1.5.2.
Сортировка
Перейдите на лист 2, назовите его Сортировка.
Если фильтр скрывает ненужные данные, то сортировка – выстраивает их в нужном порядке: по ал-
фавиту, возрастанию или убыванию.