41863

Редактирование рабочей книги. Построение диаграмм

Лабораторная работа

Информатика, кибернетика и программирование

Изучение способов работы с данными в ячейке. Изучение возможностей автозаполнения. Построение диаграмм. Создание и сохранение таблицы (рабочей книги). Форматирование содержимого ячеек, выбор диапазона ячеек и работа с ними, редактирование содержимого ячеек.

Русский

2013-10-26

976.65 KB

38 чел.

Лабораторная работа №1 Редактирование рабочей книги. Построение диаграмм

Цель работы: Изучение способов работы с данными в ячейке. Изучение возможностей автозаполнения. Построение диаграмм.

Задание 1. Создание и сохранение таблицы (рабочей книги). Форматирование содержимого ячеек, выбор диапазона ячеек и работа с ними, редактирование содержимого ячеек.

Методика выполнения работы

  1.  На рабочем столе найти ярлык Microsoft Excel и открыть окно двойным щелчком левой клавиши мыши или ПускПрограммыMicrosoft Office Microsoft Excel.
  2.  Переименуйте текущий лист Главная Формат  Переименовать лист, введите название листа «Ведомость».
  3.  Добавьте еще один лист в рабочую книгу (щелкните правой кнопкой мыши на ярлыке листа и в контекстном меню выберите команду Вставить).
  4.  Сохраните созданный вами файл под именем book.xls в своем каталоге (Кнопка Office  Сохранить).
  5.  Создайте таблицу по предложенному образцу (табл. 5.1). Для этого нужно выполнить следующие действия:

Таблица 5.1

Экзаменационная ведомость

№п/п

Фамилия, имя, отчество

№ зачетной книжки

Оценка

Фамилия экзаменатора

Смоляков А.П.

010102

4

Беденко А.Ф.

Ливанов А.В.

012102

4

Белоусова Е.В.

011402

3

Ревтова И.С.

014102

5

Кравцов А.Ю.

011302

4

Немов Л.М.

010502

3

  1.  В ячейку А1 ввести заголовок «Экзаменационная ведомость», выделить мышкой ячейки А1:E1 протащив по ним указатель, щелкните на ленте Главная на кнопке Объединить и поместить в центре ;

в ячейку A3 ввести «№ п/п»;

в ячейку ВЗ ввести «Фамилия, имя, отчество»;

в ячейку СЗ ввести «№ зачетной книжки»;

в ячейку D3 ввести «Оценка»;

в ячейку ЕЗ ввести «Фамилия экзаменатора».

Рисунок 5.44 – Группа инструментов выравнивания

  1.  Отформатируйте ячейки шапки таблицы:
  2.  выделите блок ячеек (АЗ:ЕЗ);
  3.  лента Главная  перейдите к группе Выравнивание;
  4.  на панели Выравнивание (рис. 5.44) щелкните на кнопках Выровнять по середине, По центру и Перенос текста, а на вкладке Шрифт измените начертание букв и размер шрифта.
  5.  Измените ширину столбцов, в которые не поместились введенные данные. Для этого можно перетащить границы между строками и столбцами или навести указатель мыши на границу между заголовками столбцов, дважды щелкнуть основной кнопкой мыши. Для более точной настройки надо выполнить команду Формат  Высота строки (Ширина столбец) из группы Ячейки.
  6.  Выполните обрамление таблицы: выделите таблицу, выполните команду Шрифт Список Границы и с помощью соответствующих кнопок установите границы.
  7.  Присвойте каждому студенту свой порядковый номер, используя маркер заполнения. Для этого:
  8.  сделайте текущей первую ячейку столбца «№ п\п» и введите в нее цифру 1;
  9.  затем заполните цифрой 2 следующую ячейку этого столбца;
  10.  выделите блок, состоящий из двух заполненных ячеек;
  11.  установите указатель мыши на правый нижний угол выделенного блока. Указатель мыши станет черным крестиком – это маркер заполнения. Перетащите маркер заполнения при нажатой правой кнопке мыши вниз;
  12.  или выберите команду Главная  Редактировать Заполнить.
    1.  Заполните столбец «Фамилия экзаменатора». Воспользуйтесь методом автозавершения, который состоит в том, что Excel «угадывает» слово, которое собирается вводить пользователь, или заполните ячейки с помощью маркера заполнения.
    2.  Скопируйте таблицу на другой рабочий лист при помощи буфера обмена. Для этого следует:
  13.  выделить таблицу или диапазон ячеек;
  14.  правой клавишей мыши вызвать контекстное меню;
  15.  выполнить команду Копировать;
  16.  затем перейти на другой лист;
  17.  установить курсор в первую ячейку предполагаемой таблицы;
  18.  выполнить команду Вставить из контекстного меню.
    1.  Добавьте в новую таблицу одну строку и один столбец. Для этого нужно:
  19.  выделить столбец;
  20.  щелкнуть правой кнопкой мыши на выделенном диапазоне и в открывшемся контекстном меню выбрать команду Вставить; то же самое повторить для строки.
    1.  Внесите в таблицу ряд изменений:
  21.  озаглавьте последнюю строку – «Средний балл»;
  22.  озаглавьте последнюю колонку - «Подпись экзаменатора».
  23.  Вычислите под столбцом «Оценка» средний балл, набрав там формулу =.

Задание 2. На основе данных, приведенных в табл. 5.2, постройте несколько типов диаграмм, наглядно показывающих итоги сессии.

Таблица 5.2

Средний балл по группе

Группа

Информатика

Математический анализ

История

Экономика

Э101

4,2

3,8

4,5

4,3

Э102

4,0

4,4

4,4

4,2

Э103

3,9

4,0

4,0

3,9

Б101

4,3

4,4

4,4

4,1

Б102

3,8

4,0

4,0

3,6

Б103

3,3

3,9

3,9

3,6

Б104

4,5

4,8

4,8

3,9

Методика выполнения работы

  1.  На листе 3 создайте таблицу «Сведения о результатах сдачи сессии на факультете», внесите в нее данные.
  2.  Постройте диаграмму типа Гистограмма или График для всех групп и всех предметов на отдельном листе. Для этого следует:
  3.  выделить всю таблицу;
  4.  выполнить команду меню Вставка Диаграмма и выбрать вид диаграммы, после этого откроется полотно для построения диаграммы и три ленты (конструктор, макет и формат) с инструментами для редактирования диаграммы (рис. 5.45).

а)

б)

в)

Рисунок 5.45 – Ленты с инструментами: а – Конструктор диаграмм, б– Макет диаграммы, в – Формат диаграммы

  1.  На шаге 2 можно изменить диапазон ячеек, по которым строится диаграмма, задать другие имена для рядов данных, добавить или удалить ряды данных, изменить диапазон подписей по оси Х или Y (рис. 5.45). Команда Данные Выбрать данные, выделите таблицу полностью (рис. 5.46).

Рисунок 5.46 – Диалоговое окно определения рядов данных

  1.  На третьем шаге построения диаграммы внесите название диаграммы, обозначения осей, добавьте легенду (рис. 5.47). Команда Макет Подписи.

Рисунок 5.47

  1.  Постройте диаграммы других типов и сравните результаты сдачи по предметам: информатика, математический анализ и экономика.

Для этого выделите столбцы «Группа», «Информатика», «Математический анализ» и, удерживая клавишу Ctrl, выделите столбец «Экономика»; выберите тип диаграммы График.

  1.  Измените, результаты сдачи сессии и проверьте, как это отразилось на построенных диаграммах.
  2.  На листе 4 постройте таблицу следующего вида (рис. 5.48):

Рисунок 5.48 – Макет таблицы

  1.  Команда Формулы  Библиотека функций. В ячейку В3 введите формулу =МАКС(Лист3!B3:B9); в ячейку В4 формулу =МАКС(Лист3!C3:C9) и т.д. по всем предметам.
  2.  В ячейку С3 введите формулу =МИН(Лист3!B3:B9) и т.д. по всем предметам. Постройте отдельно графики по максимальным средним баллам и по минимальным.
  3.  Отчет о работе представьте в виде диаграмм на отдельных листах рабочей книги.
  4.  Распечатайте созданный документ.

Самостоятельное задание

1. Дан список сотрудников. Известны фамилии, должность, оклад и коэффициент трудового участия каждого. Начислить всем сотрудникам премию в размере 20% от оклада и вычислить итоговые суммы. Провести графическую интерпретацию данных (построить график и диаграмму).

№ п/п

Исполнители

Должность

Оклад

Коэффициент трудового участия

Премия

1

Алехина

Ген.директор

6000

1,0

2

Коннова

Зам.ген.директора

5500

0,9

3

Борисова

Гл.бухгалтер

5500

0,8

4

Овчинникова

Экономист

5000

0,9

5

Медведев

Менеджер

4500

0,7

6

Алдобаев

Вед.специалист

4000

0,6

7

Петраков

Инженер

3200

0,5

8

Цуканов

Гл.специалист

3500

0,4

9

Сорокин

Инженер

3200

0,6

10

Кузьменко

Инженер

3200

0,75

2. Построить и рассчитать в Excel таблицу следующего вида.

Структура доходов коммерческого банка

Статьи доходов

тыс.руб.

% к итогу

Начисленные и полученные проценты

100254

Плата за кредитные ресурсы

18157

Комиссионные за услуги и корреспондентские отношения

37649

Доходы по операциям с ценными бумагами и на валютном рынке

3427

Доходы от лизинговых операций

512

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

1973

Плата за юридические услуги

2836

Итого:

164808

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

Лабораторная работа №2 Создание формул

Цель работы: Создание и использование простых формул в MS Excel.

Задание №1. Торговая фирма имеет в своем ассортименте следующую сельскохозяйственную продукцию: пшеница продовольственная и фуражная, ячмень фуражный, овес, рожь, кукуруза фуражная. Дается цена за 1 тонну продукции и количество проданного. Используя возможности Excel, найти сумму выручки от продаж в рублях и долларах.

Методика выполнения работы.

  1.  Откройте новую рабочую книгу и сохраните ее в своей папке.
  2.  Создайте таблицу, внесите в нее исходные данные задачи (рис. 5.49).

Рисунок 5.49 – Макет таблицы

  1.  Для подсчета выручки от продажи в долларах в ячейки столбца внесите соответствующие формулы. В формулах использована относительная адресация ячеек. Формула вводится лишь в одну ячейку, а остальные формулы в столбце получены при помощи автозаполнения.
  2.  Подсчитайте выручку от продажи в рублях. В формулах использована смешанная и абсолютная адресация ячеек. Для введения абсолютного и смешанного адреса необходимо после введения ссылки нажать клавишу F4 и выбрать из предлагаемых вариантов нужный.
  3.  Подсчитайте сумму выручки от продажи всех видов товаров. Выделить столбец и нажать кнопку Автосумма на ленте Формулы..

Задание №2.

  1.  Изучите создание и использование простых формул, используя тематику финансового и банковского менеджмента.
  2.  Сопоставьте доходность акции по уровню дивидендов за 2010 г. по отдельным эмитентам. Исходные данные задачи представлены в таблице 5.3.

Методика выполнения работы.

  1.  В соответствующие столбцы введите формулы для расчета выходных показателей, заменяя символьные названия показателей адресами ячеек:

Дивиденды, руб. = Номинал акции, руб. * Дивиденды, %

Доходность к номиналу, % = Дивиденды, %

Доходность фактическая, руб. = Цена продажи, руб. - Номинал акции, руб. + Дивиденды, руб.

Таблица 5.3

Эмитент

Номинал акции, руб.

Цена продажи, руб.

Дивиденды, объявленные в расчете на год

Доходность по дивидендам

%

руб.

К номиналу, %

Фактическая, руб.

Юниаструмбанк

10000

17780

400%

Инкомбанк

10000

22900

400%

Россельхозбанк

3000

5600

320%

Импэксбанк

5000

2015

653%

Сбербанк России

10000

2482

736%

МДМ банк

1000

1000

325%

Промстройбанк

1000

1200

153%

  1.  Визуально проанализируйте полученные результаты.
  2.  Добавьте внизу таблицы строку «Среднее значение» и введите формулы по всем столбцам.
  3.  На основании исходного документа «Доходность акций по отдельным дивидендам» рассчитайте следующие значения:
  4.  средняя цена продажи акций по всем эмитентам (выделить столбец «Цена продажи» без заголовка, воспользоваться функцией СРЗНАЧ);
  5.  максимальная цена продажи акций по всем эмитентам (выделить столбец «Цена продажи» без заголовка, воспользоваться функцией МАКС);
  6.  минимальная цена продажи акций (выделить столбец «Цена продажи» без заголовка, воспользоваться функцией МИН);
  7.  максимальная фактическая доходность акций по уровню дивидендов (выделить столбец «Фактическая доходность» без заголовка, воспользоваться функцией МАКС);
  8.  минимальная фактическая доходность акций по уровню дивидендов (выделить столбец «Фактическая доходность» без заголовка, воспользоваться функцией  МАКС).
  9.  Результаты расчетов оформите в виде табл. 5.4.

Таблица 5.4

Расчетная величина

Значение

Средняя цена продажи акций

Максимальная цена продажи акций

Минимальная цена продажи акций

Максимальная фактическая доходность акций

Минимальная фактическая доходность акций

  1.  В исходной таблице отсортируйте записи в порядке возрастания фактической доходности по дивидендам (выделить таблицу без заголовков и строки «Среднее значение», выполните команду Данные Сортировка).
  2.  Выполните фильтрацию таблицы, выбрав из нее только тех эмитентов, фактическая доходность которых больше средней по таблице. Алгоритм фильтрации следующий:
  3.  выделить данные таблицы с прилегающей одной строкой заголовка;
  4.  выполнить команду Данные Фильтр;
  5.  в заголовке столбца «Фактическая доходность» нажать кнопку раскрывающегося списка и выбрать Числовые фильтры;
  6.  в раскрывающемся списке выбрать условие «выше среднего значения».
  7.  Вернитесь к исходному виду с помощью команды Данные Очистить.
  8.  Постройте на отдельном рабочем листе Excel круговую диаграмму, отражающую фактическую доходность по дивидендам каждого эмитента в виде соответствующего сектора (выделить столбцы «Эмитент» и «Фактическая доходность», выполнить команду Вставка Диаграмма). На графике показать значения доходности, вывести легенду и название графика «Анализ фактической доходности акций по уровню дивидендов».
  9.  Постройте на новом рабочем листе Excel смешанную диаграмму, в которой представьте в виде гистограмм значения номиналов и цены продажи акций каждого эмитента, а их фактическую доходность покажите в виде линейного графика на той же диаграмме. Выведите легенду и название графика «Анализ доходности акций различных эмитентов». Алгоритм построения смешанного графика следующий:
  10.  выделить столбцы «Эмитент», «Номинал акции» и «Цена продажи»;
  11.  выполнить команду меню Вставка  Диаграмма - тип диаграммы Гистограмма - введите название диаграммы и по осям - Готово;
  12.  для добавления линейного графика «Фактическая доходность по дивидендам правой клавишей мыши на пустом поле диаграммы Выбрать данные  Добавить, в поле Имя ввести название ряда «Доходность», в поле Значения ввести числовой интервал, соответствующий фактической доходности по дивидендам;
  13.  на полученной диаграмме, курсор мыши установить на столбец, соответствующий значению «Доходность», правой клавишей мыши активизировать контекстное меню, выбрать команду Изменить тип диаграммы для ряда, где выбрать тип диаграммы  – График;
  14.  Подготовьте результаты расчетов и диаграммы к выводу на печать.

Самостоятельное задание

На предприятии работники имеют следующие оклады: начальник отдела  – 1000 руб., инженер 1кат.  – 860 руб., инженер  – 687 руб., техник  – 315 руб., лаборант  – 224 руб. Предприятие имеет два филиала: в средней полосе и в условиях крайнего севера. Все работники получают надбавку 10% от оклада за вредный характер работы, 25% от оклада ежемесячной премии. Со всех работников удерживают 20% подоходный налог, 3% профсоюзный взнос и 1% в пенсионный фонд. Работники филиала, расположенного в средней полосе, получают 15% районного коэффициента, работники филиала, расположенного в районе крайнего севера, имеют 70% районный коэффициент и 50% северной надбавки от начислений.

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


Лабораторная работа №3 Создание базы данных

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

Методика выполнения работы

  1.  Открыть новую рабочую книгу. Листу 1 присвоить имя «Продажа служащими». Заполнить этот лист, как показано ниже на рисунке 5.50.

Рисунок 5.50 – Макет таблицы

  1.  Для того, чтобы отсортировать данные выполните команду Данные Сортировка. Откроется окно «Сортировка» (рис. 5.51). В поле «Сортировать по» установите имя столбца «Имя», щелкнуть Добавить уровень, в поле «Затем по» - имя столбца «Дата». Нажать ОК.

Рисунок 5.51

  1.  Промежуточные итоги. Выполнить команду ДанныеСтруктура Промежуточные итоги. Откроется диалоговое окно (рис. 5.52). Установить в поле При каждом изменении в столбец «Имя», в поле Операция - сумма, в поле Добавить итоги по - столбец «Цена», нажать ОК. Повторить команду ДанныеСтруктура Промежуточные итоги. В поле Операция выбрать «среднее», отменить флажок на опции «Заменить текущие итоги» и нажать ОК.

Рисунок 5.52 – Окно параметров промежуточных итогов

  1.  Сделать самостоятельно сортировку и подсчитать промежуточные итоги по Категории и Магазину.
  2.  Щелкнув левой клавишей мыши на имени столбца, выделить столбец «Магазин», захватить его мышкой и перетащить на столбец А.
  3.  Добавим столбец, содержащий количество проданных фильмов: щелкнуть правой клавишей мыши на имени столбца «Категория», выбрать команду Вставить. Ввести название нового столбца – «Кол-во». Заполнить этот столбец числами в пределах 20. В столбце Н ввести название «Продажа». Этот столбец заполнить формулами, выполняющими действие Кол-во*Цену. Примерный вид новой таблицы показан на рисунке 5.53.

Рисунок 5.53 – Данные для добавления

  1.  Ввод данных и просмотр с помощью формы. Достать на панель быстрого доступа кнопку Форма (Кнопка Office  Параметры  Настройка  Все команды). Отсортировать таблицу по категории «Магазин». Открыть с помощью соответствующей кнопки окно работы с формой (рис.5.54).

Рисунок 5.54 - Окно Форма

Щелкая на кнопках «Назад» и «Далее» можно просматривать записи из нашей таблицы. Щелчок на кнопке «Добавить» откроет, пустую форму для ввода. Введите в таблицу дополнительные записи (записи приводятся далее):

Перемещать курсор между полями мышкой или клавишей Tab. После ввода закрыть окно формы и просмотреть, добавились ли ваши записи в таблицу.

  1.  Выборка по критериям. Выполнить команду Данные  Фильтр. Обратите внимание, что справа от названий столбцов вашей таблицы появилась кнопка для раскрытия списка. Откройте список в столбце «Имя» и установите выборку по «Ольге», выбрав ее из списка. Выборка должна иметь следующий вид (рис. 5.55):

Рисунок 5.55 – Таблица с автофильтром

Выведите выборку на печать. Отменить выборку, можно командой ДанныеОчистить.

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

Выборка по условию: из списка критериев столбца «Кол-во» выберите «Числовые фильтры» и введите условие >5. Выведите на печать. Из списка критериев столбца «Дата» выберите критерий «Фильтры по дате» и введите условие >=10.янв.06 и <=12.янв.06. Выведите на печать. Очистите выборку.

Самостоятельное задание

В рассматриваемом примере выполнить сортировку и вывести на экран только строки относящиеся к научной фантастике. Определить для этой категории книг общую сумму продаж и среднюю цену. Вернуться к исходному виду таблицы.


Лабораторная работа №4 Сортировка данных в списке. Фильтрация записей

Цель работы: выполнение сортировки данн1ых, ознакомление со способом фильтрации записей списка, автофильтрацией, работой с формой данных.

Задание 1.

Выполнить несколько раз сортировку данных таблицы 5.5 в соответствии со следующими признаками - в алфавитном порядке фамилий покупателей, по убыванию суммы сделки, по возрастанию даты сделки, по совокупности признаков (фамилия, дата, сумма).

Методика выполнения работы

1. Откройте новую рабочую книгу и сохраните ее под именем «Сортировка» в своей рабочей папке.

2. Создайте таблицу представленную на рисунке 5.56.

Рисунок 5.56 – Начальная таблица с данными

3. Задайте параметры форматирования для таблицы.

Шрифт Times New Roman, размер шрифта 12 пт., для заголовков начертание полужирное и выравнивание по центру, перенос по словам, заливка серым цветом; для основной части. Напоминаем, что команды форматирования доступны на ленте Главная  Ячейки.

  1.  Для выполнения сортировки по полю фамилия покупателя поставьте курсор в любое место этого столбца и выполните команду Данные  Сортировка (рис. 5.51).

В открывшемся диалоговом окне в поле Сортировать по  выберите «Фамилия покупателя». По возрастанию.

  1.  Повторите все шаги пункта 4 и задайте сортировку по «Сумме сделки», по убыванию.
  2.  Выполните повторно сортировку по полю «Дата сделки», по возрастанию.
  3.  Скопируйте таблицу на новый лист и на нем выполните сортировку по совокупности признаков. Для этого вызовите команду Данные  Сортировка. Установите Сортировать по фамилии в порядке возрастания, Затем по дате в порядке возрастания, В последнюю очередь, по сумме в порядке убывания.
  4.  С помощью команды Переименовать присвойте имена этим двум листам.

Задание 2. Осуществите выборку информации из списка на основе команды Автофильтр.

Методика выполнения работы.

  1.  На листе 4 создайте таблицу и заполните ее сведениями из таблицы 5.5.
  2.  Переименуйте Лист4, присвоив ему имя «Автофильтр №1».
  3.  Чтобы применить Автофильтрацию, установите курсор в область списка и выполните команду Данные Фильтр. Рядом с названиями граф таблицы появятся стрелки направленные вниз, которые раскрывают список возможных значений. В графе «Пол» выберите «М» Скопируйте таблицу на лист 5 и переименуйте его в «Автофильтр №2».
  4.  На листе «Автофильтр №1» в графе «Пол» откройте список фильтрования и выберите «Все». Затем в графе «Дата рождения» выберите в списке фильтрования «Условие» и задайте условие (рис.5.57):

Таблица 5.5

Фамилия

Имя

Дата приема на работу

Дата рождения

Пол

Оклад

Возраст

Пашков

Игорь

16.05.74

15.03.49

М

3200

56

Андреева

Анна

16.01.93

19.10.66

Ж

5300

39

Ерохин

Владимир

23.10.81

24.04.51

М

4400

54

Попов

Алексей

02.05.84

07.10.56

М

4200

49

Тюньков

Владимир

03.11.88

19.07.41

М

3290

64

Ноткин

Евгений

27.08.85

17.08.60

М

5950

45

Кубрина

Марина

20.04.93

26.06.61

Ж

3700

44

Гудков

Никита

18.03.98

05.04.58

М

3700

47

Горбатов

Михаил

09.08.99

15.09.52

М

5500

53

Быстров

Алексей

06.12.00

08.10.47

М

3600

58

Крылова

Татьяна

28.12.93

22.03.68

Ж

4900

37

Бершева

Ольга

14.12.01

22.12.74

Ж

3150

31

Русанова

Надежда

24.05.87

22.01.54

Ж

4260

51

Рисунок 5.57 – Задание условий фильтрации

  1.  Скопируйте отфильтрованную таблицу на лист 6 и переименуйте его в «Автофильтр №3. На листе «Автофильтр №1» отмените выборку.
  2.  В столбце «Фамилия» выберите в списке фильтрации «Условие» и задайте условие на отбор всех сотрудников, чья фамилия начинается на «Б» (рис.5.58).

Рисунок 5.58 – Пользовательский фильтр

  1.  Скопируйте отфильтрованный список на лист 7 переименуйте его в «Автофильтр №4».
  2.  На листе «Автофильтр №1» для графы «Фамилия» задайте «Все», а в графе «Оклад» задайте «Первые 10…» где в диалоговом окне введите «Показать 5 наибольших элементов списка».
  3.  Сохраните файл.

Задание 3. Выполните отбор записей, из списка используя команду Расширенный фильтр.

Методика выполнения работы.

  1.  Перейдите на лист 8 и переименуйте его в «Расширенный фильтр».
  2.  Скопируйте на этот лист таблицу из предыдущего задания (табл. 5.5), вставьте ее, начиная со строки 7. Первые 6 строк отводятся для задания условий.
  3.  Создадим диапазон условий. Предположим, нам требуется отобрать фамилии сотрудников, которые получают больше 5000 руб. Или чей возраст превышает 50 лет. Заполните условия, как показано на рисунке 5.59.

Рисунок 5.59 – Условия для расширенного фильтра

  1.   Выполните команду Данные  Дополнительно. Заполните диалоговое окно следующим образом (рис. 5.60):

Рисунок 5.60 – Окно параметров расширенного фильтра

Просмотрите результаты отбора. При записи условий в одной строке реализуется логическое И. При записи условий на разных строках они считаются связанными логическим ИЛИ. Мы рассмотрели первый вариант, теперь рассмотрим второй.

  1.  Предположим нам требуется вывести только тех сотрудников, фамилии которых начинаются с букв А, Г или Н. Заполните диапазон условий (рисунок 5.61).

Рисунок 5.61 – Условия для расширенного фильтра

  1.  Выполните команду ДанныеДополнительно и заполните диалоговое окно (рисунок 5.62).

Рисунок 5.62 – Окно параметров расширенного фильтра

Просмотрите результаты отбора записей.

  1.  Выведите список всех сотрудников, заработная плата которых больше среднего уровня. Перед созданием этого фильтра введите в ячейку H2 формулу =СРЗНАЧ(F8:F20) для вычисления среднего оклада.
  2.  Затем в ячейку А2 вводим вычисляемое условие =F8>$H$2, ссылающееся на ячейку H2 (рисунки 5.63 и 5.64).

Рисунок 5.63 – Условия для расширенного фильтра

Рисунок 5.64 – Параметры расширенного фильтра

Просмотрите результаты.

Самостоятельное задание

Выполнить самостоятельно следующие задания:

  1.  вывести всех мужчин, чей возраст не превышает 50 лет; а оклад менее 5000 руб.
  2.  вывести список сотрудников, в который включить всех женщин старше 50 лет и всех мужчин старше 60 лет;
  3.  вывести список сотрудников, которые проработали не более 10 лет.

Лабораторная работа №5 Использование логических и статистических функций

Цель работы: знакомство с различными встроенными функциями Microsoft Excel и проведение анализа данных.

Задание 1

  1.  Для студентов планируется поездка во Францию, но поедут только студенты, у которых средний балл более или равен «4», нет неудовлетворительных оценок и по иностранному языку «5». Создать функцию автоматического определения претендентов на поездку.
  2.  Подсчитать в каждой группе количество «5», «4», «3» и «2».
  3.  Определить «Худшую группу» по максимальному количеству «2» и «Лучшую группу» по максимальному количеству «5». При выполнении вычислений применять операцию «Присвоение имени блоку ячеек».

Методика выполнения работы

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

Таблица 5.6

  1.  В столбец «Ср. балл» введите формулу, для этого воспользуйтесь кнопкой Вставить функцию в строке ввода и редактирования формул или командой Формулы  Библиотека функций.

Выберите категорию функций Статистическая и функцию СРЗНАЧ.

В открывшемся диалоговом окне введите диапазон ячеек С5:Е5. Щелкните ОК и скопируйте формулу вниз до ячейки F24.

  1.  С помощью функций из категории Логические создадим в ячейке G5 логическое выражение

=ЕСЛИ(И(F5>=4;E5=5;C5<>2;D5<>2;E5<>2);"Едет";"Не подходит")

Скопируйте формулу до ячейки G24.

  1.  Для подсчета количества человек, которые едут во Францию, а также количества различных оценок создайте на листе таблицу в соответствии с таблицей 5.7.

Функция СЧЕТЕСЛИ находится в категории Статистические.

  1.  В ячейку В27 введите формулу =СЧЁТЕСЛИ(G6:G25;"Не подходит")

Количество полученных оценок определенного вида будем подсчитывать в ячейках С30:F33, используя уже знакомую нам функцию СЧЕТЕСЛИ. Введем в С30 формулу =СЧЁТЕСЛИ($C$5:$E$8;B30). Абсолютные ссылки (знак $) здесь применяются для удобного последующего копирования. Блок ячеек охватывает все оценки группы Б124, а ячейка В30 указывает на то, что подсчитываются оценки «отлично».

Таблица 5.7

  1.  Введите формулу подсчета количества студентов подходящих для поездки в ячейку В26

=СЧЁТЕСЛИ(G5:G24;"Едет")

  1.  Аналогично введите формулы подсчета «5» для других групп в ячейках D30:F30. Затем скопируйте ячейки С30:F30 вниз до 33 строки. Формула должна автоматически настроиться под другие диапазоны ячеек.
  2.  Присвойте имена блокам ячеек С33:F33 и С30:F30, содержащим количество двоек и пятерок по группам соответственно, как «Двойки» и «Пятерки». Для этого блок ячеек предварительно выделяется затем выполняется команда Формулы  Определенные имена  Присвоить имя.
  3.  В ячейку D35 введите формулу

=ПРОСМОТР(МАКС(Двойки);Двойки;C29:F29)

Функция МАКС находится в категории Статистические, а функция ПРОСМОТР в категории Ссылки и массивы.

С помощью справки изучите принцип работы функции

ПРОСМОТР! Ответь на вопрос: для чего в функции используется блок ячеек С29:F29?

  1.  В ячейку D36 аналогично пункту 9 введите формулу для определения лучшей группы по количеству пятерок.

Задание 2

Определить, в какой из заданных интервалов попадает премия отдельных сотрудников риэлтерской фирмы.

Методика выполнения задания

  1.  Для определения интервальных границ по премии каждого сотрудника будем использовать знакомую нам методику из задания 1 данной работы по работе с функциями ЕСЛИ, И.
  2.  На новом листе создайте таблицу начиная с ячейки А1 (табл. 5.8).
  3.  В шапке таблицы для надписей установите выравнивание по горизонтали и вертикали «по центру», а для 5-8 столбцов измените направление текста. Все эти действия можно сделать с помощью команды Главная Выравнивание.
  4.  Создайте таблицу интервалов начиная с ячейки В12 (табл. 5.9). Обратите внимание, что ячейки В12 и С12 объединенные.
  5.  В ячейку D2 введем формулу, которая будет устанавливать в ней 1, если премия попадает в Интервал 1, в противном случае 0. Выберите категорию функций Логические, функция ЕСЛИ.

Таблица 5.8

№ п/п

ФИО

Премия

Интервал 1

Интервал 2

Интервал 3

Интервал 4

Интервал 5

1

Пашков

1200

1

0

0

0

0

2

Андреев

5300

0

0

0

0

1

3

Ерохин

4400

0

0

0

1

0

4

Попов

4200

0

0

0

1

0

5

Тюньков

2290

0

1

0

0

0

6

Ноткин

5950

0

0

0

0

1

7

Кубрина

3700

0

0

1

0

0

8

Гудков

2700

0

1

0

0

0

ИТОГО:

1

2

1

2

2

Таблица 5.9

Интервалы премии

1ин

1000

2000

2ин

2000

3000

3ин

3000

4000

4ин

4000

5000

5ин

5000

6000

  1.  Не выходя из диалогового окна функции, щелкните на меню Имя в строки редактирования и ввода формул.

  1.  В открывшемся списке выберите Другие функции, а затем Логические, функция И (рис. 5.65).

Рисунок 5.65 – Создание вложенных функций

Рисунок 5.66 – Создание вложенной логической функции И

  1.  В диалоговом окне в поле Логическое_значение1 сделайте проверку нижней границы интервала 1. Обратите внимание на использование абсолютных ссылкой для более удобного последующего копирования, для ввода знака $ можно воспользоваться клавишей F4. Наберите С2>=$B$13. В поле Логическое_значение2 проверьте верхнюю границу С2<$C$13. Не выходя из диалогового окна функции, установите курсор в строку ввода и редактирования формул (рис. 5.66) между двумя крайними правыми закрывающими скобками для продолжения функции ЕСЛИ.

Рисунок 5.67 – Итоговая таблица

  1.  Введите с клавиатуры продолжение формулы и нажмите клавишу Enter.
  2.  Формулу из ячейки D2 операцией автозаполнения скопировать по столбцу D.
  3.  Аналогичным образом введите формулы в столбцы Е, F, G, H для других интервалов.
  4.  В итоговой строке таблицы с помощью Автосуммы подсчитайте число попаданий в каждый интервал. В результате вы должны получить таблицу представленную на рисунке 5.67.

Задание 2

Определить количество сотрудников, оклад которых попадает в заданные интервалы с использованием функции ЧАСТОТА и построить по этим данным (табл. 5.8) гистограмму. Вычислить процент заработной платы каждого сотрудника от максимальной.

Методика выполнения работы

  1.  Скопируйте таблицу 5.8 на новый лист и переименуйте его.
  2.  Создайте диапазон интервалов, как показано на рисунке 5.68.
  3.  В ячейку Е13 ввести статистическую функцию =ЧАСТОТА(С2:С9;С13:С18)
  4.  Выделить блок ячеек Е13:Е18, поставить курсор в строку редактирования и ввода формул и нажать клавиши CTRL + SHIFT + ENTER (ввод формулы массива). Ячейки Е13:Е18 заполнятся значениями.

Рисунок 5.68

Рисунок 5.69

  1.  Построить гистограмму распределения заработной платы по интервалам. Установить курсор в ячейку G12. Выполнить команду Кнопка Office  Параметры Надстройки  Перейти. В диалоговом окне отметьте надстройку Пакет анализа и нажмите ОК. Выполните команду Данные Анализ данных  Гистограмма.
  2.  Заполните диалоговое окно, как показано на рисунке 5.69. Результат представлен на рисунке 5.70.
  3.  В столбце I вычислить процент заработной платы сотрудника от максимальной. Установить курсор в ячейку I2 и ввести статистическую функцию =ПРОЦЕНТРАНГ($C$2:$C$9;C2), скопировать ее в ячейки I3:I9 (рис. 5.70).

Рисунок 5.70

Самостоятельное задание

1. Рабочие изготавливают различные изделия. Рассчитать для каждого рабочего зарплату, учитывая, что стоимость изделия зависит от его кода. Кроме того, рабочие получают надбавку за качество работы: за качество с кодом 001 устанавливается надбавка в 50%, а с кодом 002 - надбавка в 25%. Исходные данные взять из таблиц ниже.

Исходные данные

табельный номер

рабочего

код

изделия

количество

изделий

код

качества

777

005

10

001

101

005

5

003

777

004

5

001

111

003

15

002

101

003

10

003

код

изделия

стоимость

изделия

001

7,5

003

10

004

15

005

10

  1.  Построить в Excel таблицу и рассчитать, используя логические функции, годовую амортизацию только для тех ОС, год эксплуатации которых не больше 10 лет.

Основное средство

Балансовая стоимость

Год эксплуатации

Норма амортизации

Годовая амортизация

Здание фермы

100000

10

5%

Трактор МТЗ-80

60000

5

7%

Плодовый сад

45000

12

2%

Склад

23000

22

3%


Лабораторная работа № 6 Работа с функциями ссылки и массива

Цель работы: изучение возможностей связывания данных и автоматизации их обработки.

Задание 1

Определить стоимость объектов недвижимости (табл. 5.10) на основе их первоначальной стоимости и таблицы скидок за продолжительность эксплуатации (табл. 5.11), т.е. начиная с 1 года – 5%-ая скидка, с 5 лет – 10%-ая скидка и т.д.

Таблица 5.10

Наименование

Первоначальная стоимость

Год создания

Год эксплуатации

2000

2005

2010

2015

Зернохранилище

5000000

2005

Складские помещения

1000000

2000

Комбайн

750000

2004

Трактор

350000

2002

Грузовой автомобиль

280000

2001

Таблица 5.11

Количество лет эксплуатации

1

5

10

15

20

Скидки

5%

10%

30%

40%

45%

Методика выполнения работы

  1.  Создать представленные таблицы на одном рабочем листе Excel.
  2.  Присвоить диапазонам ячеек имена:
  3.  выделить блок ячеек, содержащий значения начальной стоимости и выполнить команду Формулы  Присвоить имя. Задать имя Первоначальная_стоимость (обратите внимание, что в именах не должно быть пробелов);
  4.  выделите блок ячеек, содержащих значения года создания и выполните команду Формулы  Присвоить имя. Задать имя Год_создания;
  5.  таким же образом присвоить имя диапазону Год эксплуатации
  6.  Выделите блок ячеек, содержащий таблицу 5.11, и присвойте ему имя Справочная_таблица.

Далее воспользуемся функцией ГПР. Функция ГПР берет значение из первого диапазона, сравнивает со значениями первой строки второго диапазона, находит равное ему или ближайшее наибольшее, а затем в качестве результата берет значение из указанной строки второго диапазона, в нашем примере  - строка 2. Подобным образом работает функция ВПР, но она работает с диапазонами ячеек, расположенными вертикально.

  1.  В первую ячейку, находящуюся на пересечении строки с годом 2000 и столбца – год 2005 введите формулу =Первоначальная_стоимость*(1-ГПР(Год_эксплуатации-Год_создания;Справочная_таблица;2)). скопируйте формулу с помощью автозаполнения в другие вычисляемые ячейки

Задание 2

Необходимо создать две связанные таблицы для учета отдыхающих в Доме отдыха. В зависимости от категории комнаты и количества дней проживания рассчитать сумму оплаты. Выполнить поиск, и подстановку значений в таблицу используя функцию ПРОСМОТР. При этом количество дней проживания определяется как разница между днем отъезда и днем заезда.

Методика выполнения работы

  1.  Создайте новую рабочую книгу под именем «Дом отдыха».
  2.  Переименуйте первый лист в «Сведения» и заполните лист (рис.5.71).
  3.  Присвойте имена диапазонам командой Формулы  Присвоить имя.

a) А2:А31 - комнаты

б) В2:В31  - категория

в) D2:D7 – цена_категория

г) Е2:Е7 – цена

д) G2 – курс

е) G5 – сегодня

Рисунок 5.71 – Таблица для заполнения

  1.  Перейдите на лист 2, переименуйте его в «Заезды».
  2.  Подготовить таблицу следующего вида (рис.5.72):

Рисунок 5.72 – Таблица для заполнения

  1.  Заполните столбец D (номер комнаты) данными по своему усмотрению, на основании данных листа «Сведения».
  2.  В ячейку Е2 введите формулу =ПРОСМОТР(D2;комнаты;категория) и скопировать ее вниз до 14 строки.
  3.  В ячейку F2 для определения этажа введите формулу =ЛЕВСИМВ(D2;1) и скопируйте ее вниз.
  4.  В ячейку G2 для определения количества дней проживания вводим функцию, =ЕСЛИ(C2=0;сегодня-B2;C2-B2)
  5.  В ячейку H2 для определения суммы оплаты в $ введем формулу =ПРОСМОТР(E2;цен_категория;цена)
  6.  В ячейку I2 для определения суммы оплаты в рублях введем формулу =H2*Курс
  7.  Создать сводную таблицу, позволяющую определить общую сумму оплаты по месяцам заезда. Для этого установите курсор внутри таблицы «Заезды» и выполните команду Вставка  Таблица  Сводная таблица. Откроется макет для размещения полей. На ось строк перетащите поле Дата прибытия, а в область данных поля: Сумма в рублях и Сумма в $ (рис. 5.73).

Рисунок 5.73 – Макет сводной таблицы

  1.  Установите активную ячейку в поле даты на начальное значение и выполните команду Данные  Структура Группировать. Заполните диалоговое окно (рис. 5.74) так, чтобы выполнялась группировка по месяцам и нажать ОК.

Рисунок 5.74

Сохраните файл.

Самостоятельное задание

  1.  В таблице приведена выработка рабочих за 3 дня. Создать на рабочем листе таблицу. На новом листе определить выработку каждого работника по дням с помощью функции СУММЕСЛИ и оформив в виде таблицы.

Ф.И.О.

№ уч-ка

23.05.2009

24.05.2009

25.05.2009

Иванов

1

5

7

4

Петров

2

6

8

5

Сидоров

1

7

9

6

Петров

2

8

10

7

Петров

2

2

4

1

Иванов

3

9

11

8

Иванов

2

8

11

5

Петров

3

5

9

7

Иванов

1

4

6

3

Петров

2

3

5

2

Сидоров

3

5

7

1

Иванов

1

7

9

6

Петров

2

4

6

3

Иванов

3

6

8

5

Сидоров

3

8

10

7

Сидоров

2

9

11

8

Иванов

2

4

8

7

Сидоров

2

1

3

0

Сидоров

1

2

4

1

Иванов

2

7

5

2

Сидоров

1

5

7

4

Петров

3

8

4

1

Иванов

2

4

5

3

Петров

2

7

6

3

Петров

1

3

4

5

Сидоров

3

4

6

3

Сидоров

1

6

8

5

2. Найти в Excel решение уравнения АХ=В, если все его аргументы – массивы.

Массив А

Массив В

3

6

5

2

3

4

6

3

5

0

2

3

2

6

4

2

4

3

6

3


Лабораторная работа № 7 Работа со сводными таблицами

Цель работы: освоить навыки создания, редактирования и анализа данных на основе сводных таблиц.

Задание.

Построить сводную таблицу для расчета месячной заработной платы рабочих при повременной форме оплаты труда, начисления премии и учета удержаний. Премия дифференцирована по разрядам: 2 разряд 25%, 3 разряд 32%, 4 разряд 50% к тарифу, 5 разряд – 50%. Удержания берутся со всех видов начислений (зарплата, премия) и составляют 13% от суммы начислений.

Методика выполнения работы

  1.  Открыть новую книгу.
  2.  Переименовать лист в Картотека.
  3.  Подготовить исходные данные (табл. 5.12).

Таблица 5.12

ФИО

Табельный №

Профессия

Разряд работающего

Тариф

Льготы

Иванов А.П.

01234

Кладовщик

3

5,76р.

1

Колесов В.И.

02345

Грузчик

3

5,76р.

1

Крылов А.Р.

00127

Грузчик

4

6,79р.

2

Михайлов П.Р.

12980

Грузчик

3

5,76р.

2

Смирнов И.А.

13980

Кладовщик

4

6,79р.

3

Соколов Р.В.

21097

Уборщик

2

4,95р.

1

Котов А.А.

12350

Весовщик

5

7,78р.

2

Павлов Ф.Ф.

12360

Весовщик

4

6,79р.

2

Соловьев А.П.

12370

Упаковщик

3

5,76р.

1

Крылов А.Н.

12380

Грузчик

5

7,78р.

1

Ильин А.Е.

12390

Упаковщик

5

7,78р.

1

  1.  Установить курсор в список, выполнить команду меню ВставкаСводная таблица для вызова Мастера сводных таблиц и диаграмм.
  2.  Указать тип источника – Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel. Выбрать вид создаваемого отчета – Сводная таблица.
  3.  Выполнить проверку диапазон выделенных ячеек списка. Диапазон включает имена столбцов и все заполненные строки таблицы.
  4.  Разместить поля в макете сводной таблицы:

Фильтр отчета – Профессия, Названия строк – ФИО, Названия столбцов – Разряд работающего, Значения – Тариф, Операция – Сумма. 

Макет сводной таблицы представлен на рисунке 5.75.

Рисунок 5.75 – Макет сводной таблицы

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

В сводной таблице (рис. 5.76) представлен список всех работающих. Для каждого работающего указан только один тариф, соответствующий его разряду.

Для преобразования сводной таблицы следует:

  1.  Установить курсор в область сводной таблицы.
  2.  Выполнить команду Параметры  Формулы  Вычисляемое поле для создания вычисляемого поля.

Рисунок 5.76 – Сводная таблица

  1.  На рис. 5.77 представлено диалоговое окно для формирования вычисляемого поля. Имя поля – Зарплата, Формула вычисления: =Тариф*168. (Коэффициент 168 зависит от количества рабочих часов в текущем учетном периоде.). Для добавления поля в формулу можно воспользоваться кнопкой Добавить.

Рисунок 5.77 – Создание вычисляемого поля

  1.  Установить курсор в область сводной таблицы.
  2.  С помощью кнопки Список полей на ленте Параметры откройте макет сводной таблицы для корректировки.
  3.  Удалить поле Сумма по полю Тариф (простым перетаскиванием мышкой за поле окна).
  4.  Установить курсор в области сводной таблицы на поле Сумма по полю Зарплата.
  5.  На ленте Параметры выполнить команду Активное поле  Параметры поля (рис. 5.78):
  6.  Изменить имя поля в сводной таблице – Месячная зарплата. Нажать кнопку Числовой формат и указать формат поля – Денежный.
  7.  Нажать кнопку ОК.

Рисунок 5.78 – Задание параметров вычисляемого поля

  1.  Установить курсор в область сводной таблицы на поле Разряд работающего.
  2.  Создать вычисляемый объект Премия. Премия выплачивается как процент к начисленной заработной плате, дифференцируется по разрядам: 2 разряд – 25%, 3 разряд – 32%, 4 разряд – 50%, 5 разряд – 55%.
  3.  На ленте Параметры выполнить команду ФормулыВычисляемый объект (рис. 5.79). Указать имя объекта – Премия.
  4.  Для построения формулы в окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы
  5.  Формула: = ‘2’*0,25+’3’*0,32+’4’*0,5+’5’*0,55
  6.  Нажать кнопку Добавить.
  7.  Закрыть окно – кнопка ОК.

Рисунок 5.79 – Создание вычисляемого объекта

  1.  Установить курсор в область сводной таблицы на поле Разряд работающего. Создать вычисляемый объект Вычеты, сумма вычетов это 13% от суммы заработка и премии.
  2.  Выполнить команду Формулы  Вычисляемый объект. Указать имя объекта – Вычеты (рис. 5.80).

Рисунок 5.80 – Создание вычисляемого объекта

  1.  В окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы для построения формулы вида:
    = –0,13*(’2’+’3’+’4’+’5’+Премия)
  2.  Нажать кнопку Добавить.
  3.  Закрыть окно – кнопка ОК.
  4.  Выполнить команду ПараметрыФормулыВывести формулы для просмотра выражений вычисляемых полей и объектов (рис. 5.81).

Если потребуется изменить нормативы (количество отработанных часов, % премии, % вычетов), следует отредактировать вычисляемые поля и объекты – команда меню ФормулыВывести формулы, вызывать поле/объект, внести изменения

Вычисляемое поле

Порядок решения

Поле

Формула

1

Зарплата

=Тариф *168

Вычисляемый объект

Порядок решения

Объект

Формула

1

Премия

='2' *0,25+'3'*0,32+'4'*0,4+'5'*0,55

2

Вычеты

= -0,13*('2'+'3'+'4'+'5'+Премия)

Рисунок 5.81 – Вывод формул

  1.  Переименовать лист, содержащий сводную таблицу, присвоив имя, Сводная таблица 1.
  2.  Поставить курсор внутрь сводной таблицы и на ленте Конструктор выполнить команду Макет отчета. Выбрать тип отчета.
  3.  Поставить курсор внутрь сводной таблицы и щелкнуть на ленте Параметры кнопку Сводная диаграмма.
  4.  В готовой диаграмме перетащить Разряд работающего в область Поле ряда. Выбирая вид профессии просмотреть данные по различным профессиям.
  5.  Сохранить рабочую книгу.

Самостоятельное задание

  1.  На основании данных из лабораторной работы №3 (рис. 5.50) построить сводную таблицу следующего вида:

Сумма по полю Продажа

Категория

Магазин

Всего

Боевик

Восток

146,5

Запад

64,5

Север

479,5

Юг

140

Боевик Всего

830,5

Драма

Восток

16

Запад

344

Север

177

Юг

75

Драма Всего

612

2. В построенной сводной таблице добавить среднее значение суммы продаж по магазинам для каждой категории фильма. Для этого воспользуйтесь созданием вычисляемого поля.


Лабораторная работа №8. Анализ «Что-Если»

Цель работы: освоить начальные навыки экономического анализа данных с помощью специальных инструментов Excel.

Задание

  1.  Рассчитать ежемесячную выплату при изменяющейся ставке и сумме кредита.
  2.  Применить к ячейкам с отрицательными значениями Условное форматирование.
  3.  С помощью подбора параметра определить, какой кредит надо взять, если известно, сколько в месяц вы сможете выплачивать в течение определенного срока.

Методика выполнения работы

  1.  Расчет ежемесячной выплаты.

На рисунке 5.82 показана таблица, которую необходимо создать для проведения расчетов. В ячейках, где числа отображаются с двумя знаками после запятой, для установления этого формата воспользуйтесь кнопкой на панели инструментов Увеличить разрядность. Для выбора вида рамки таблицы применяются команды меню Главная Границы.

Рисунок 5.82 – Вид начальной таблицы

Таблица содержит информацию, позволяющую подсчитывать выплату за покупку 10 видеомагнитофонов в кредит на 1 год по 12% годовых. Выплаты производятся в середине и конце каждого месяца, т.о. количество периодов выплаты равно 24. Задается цена за единицу товара, количество купленного товара, ежемесячный процент выплаты и срок кредита.

В ячейку F6 - Итого занесем формулу  =F4*F5. В ячейку С4 - Стоимость занесем значение, полученное в F6. В ячейку С8 - Выплата заносится формула =ПЛТ(С5/12;С6;С4). Формулу в ячейку С8 можно внести с клавиатуры или воспользоваться мастером функций, финансовые функции. Функция ПЛТ используется для расчета величины выплаты за один период годового кредита.

Ответим на вопрос: Что произойдет, если мы закупим не 10, а 8 видеомагнитофонов? Для этого заменим Количество на 8.

  1.  Прогнозирование в таблице подстановки. Одна входная величина и одна формула.

При покупке видеомагнитофонов возник вопрос: Заказать их в кредит по телефону с 12% ставкой и не иметь проблем с доставкой или ехать самому, чтобы получить кредит с 10% ставкой? Что выгоднее?

Чтобы ответить на этот вопрос дополним нашу таблицу. Занесем в ячейки В11-В17 величину процента от 11% до 14% с шагом 0,5%. Отформатировать эти ячейки для отображения 1 знака после запятой. В ячейку В10 введите формулу =В5. Т.о. ячейке В10 будет присвоено имя Процент. В ячейку С10 ввести формулу =С8.

Выделите всю таблицу данных, включая формулу и заголовок (В10:С17). Переключитесь на ленту Данные. Щелкните кнопку Анализ «что-если». Выберите команду Таблица данных. Откроется диалоговое окно (рис. 5.83). В строке Подставлять значения по строкам в внести адрес $C$5. Щелкнуть ОК. В ячейках С11:С17 показаны новые выплаты (рис.5.84).

Рисунок 5.83 – Окно таблицы данных и кнопка Анализ «что-если».

Рисунок 5.84

  1.  Использование нескольких формул для одной входной переменной.

В предыдущем примере мы использовали для одной входной переменной Процент одну формулу Выплата. Дополним вычисления расчетом суммарного значения выплаты по процентам по истечению срока кредита. Для этого внесем в ячейку D10 новую формулу =(С10*С6)-С4. Т.е. выплату за период умножаем на срок и вычитаем исходную сумму покупки.

Выделим ячейки В10:D17. Выбрать команду ДанныеАнализ «что-если»  Таблица данных. В диалоговом окне Таблица данных, в поле Подставлять значения по строкам набрать $С$5. Результат показан на рисунке 5.85

.

Рисунок 5.85 – Результат подстановки

  1.  Две входные переменные.

Построим таблицу, которая будет отображать валовую прибыль после изменения объема продаж или процента прибыли. На новом рабочем листе заполним таблицу представленную на рисунке 5.86

.

Рисунок 5.86 – Начальные данные

В ячейку В5 ввести формулу =В3*В4. В ячейку В13 ввести формулу =СУММ(В7:В12). В ячейку В15 - формулу =В5-В13. В ячейку D2 поместим формулу вычислений, для этого туда надо занести ссылку =В5. Ячейки Е2:I2 заполнить значениями валового объема продаж варьируемого от 80000 до 160000 с шагом 20000. Ячейки D3:D8 заполнить значениями процента прибыли от 15% до 40% с шагом 5%.

Чтобы создать таблицу анализа данных выделите ее (D2:I8). Выберите команду ДанныеАнализ «что-если»  Таблица данных. В открывшемся диалоговом окне в поле Подставлять значения по столбцам набрать $B$3, а в поле Подставлять значения по строкам - $B$4. Результат вычислений показан на рисунке 5.87.

Рисунок 5.87 – Результат подстановки

Проведем анализ чистой прибыли. Для этого в ячейки Е11:I11 скопируем содержимое ячеек Е2:I2, а в ячейки D12:D17 скопируем содержимое ячеек D3:D8.

Для ячеек Е12:I17 установить условное форматирование. Выделить этот блок ячеек и выполнить команду Главная  Условное форматирование  Правила выделения ячеек, если значения в ячейках будут меньше нуля, формат их отображения должен стать полужирным красного цвета (рис. 5.88).

Рисунок 5.88 – Окно условного форматирования

В ячейке Е12 разместим формулу =Е3-$B$13. Скопировать эту формулу в ячейки Е12:I17. Результат показан на рисунке 5.89.

Рисунок 5.89 – Результат подстановки

  1.  Поиск решения с использованием средства «Подбор параметра».

Вернемся к первому нашему примеру «Покупка видеомагнитофонов в кредит» (рис. 5.82) расположенному на листе 1. В ячейке С8 содержится формула для расчета выплаты по кредиту. Если вы знаете, какую максимальную выплату можете сделать, то Excel сможет вычислить максимальную сумму кредита на покупку, которую вы можете себе позволить при заданном уровне процентной ставки и сроке погашения кредита. Поместив значение, максимально возможной выплаты в ячейку С8, вы удаляете формулу и вычисляете результат с использованием средства Подбор параметра.

Например, вы можете сделать выплату равную 200 руб. и хотите узнать, какой кредит вы можете взять.

В ячейку С4 внесем значение 3500. Сделаем активной ячейку С8 (содержащую формулу). Выполним команду ДанныеАнализ «что-если»  Подбор параметра. Откроется диалоговое окно, показанное на рисунке 5.90.

Рисунок 5.90 – Окно подбора параметра

В этом окне в поле Значение набрать 200, в поле Изменяя значение ячейки набрать $C$4. Щелкнуть ОК. В появившемся окне, «Результат подбора параметра», вы можете принять новое значение, щелкнув ОК, или вернуться к исходным данным, нажав, Отмена.

  1.  Подбор параметра для графиков. (Для версий Microsoft Office кроме 2007).

На новом листе построить таблицу, показанную на рисунке 5.91. Построим для нее диаграмму, представленную на рисунке 5.92. Причем в ячейки, содержащие прибыль, внесены формулы, в которых величины из строки 2 умножаются на 25%. Т.е. уровень прибыли поддерживается одинаковый – 25%.

Рисунок 5.91 – Начальные данные

Зададим вопрос: каким должен быть объем продаж в 2000 году, чтобы поднять уровень прибыли до 48000?

Для этого дважды щелкните на диаграмме, чтобы сделать ее активной. Щелкните на любом из столбцов в наборе данных Прибыль, чтобы выбрать этот набор. Маленький квадратик в каждом прямоугольнике указывает на то, что набор выбран. Щелкните на элементе данных Прибыль в 2005 году, чтобы выбрать его. По периметру элемента появятся маркеры. Потяните вверх центральный маркер, для того, чтобы установить высоту столбца равной прибыли 48000 руб. Открылось диалоговое окно Подбор параметра. Заполните в нем поля Установить в ячейке - $F$3; Значение - 48000; Изменяя значения ячейки - $F$2. Нажать ОК. Появится диалоговое окно Результат подбора параметра. Величина в ячейке F2 равна 192000 руб. Щелкните ОК. Чтобы выйти из режима редактирования диаграммы щелкните мышкой за пределами диаграммы (рис. 5.92).

Рисунок 5.92 – Диаграмма подбора параметра

Самостоятельное задание

1. Для уравнения y=23+x+x2 в Excel выполнить подбор параметра, который дает значение y равное 112.

2. Выполнить в Excel таблицу подстановки для расчета выплаты по кредиту банка на сумму 100000 руб., взятого на 10 лет с изменяющимися процентами от 10 до 20% с шагом 1%.

3. Построить в Excel таблицу:

Год

2001

2002

2003

2004

2005

Объем продаж

500

740

640

800

710

Цена единицы

12

14

12,50

15

13

Выручка

Подобрать на графике такое значение объема продаж в 2005 году, которое позволит получить выручку при той же цене 12000.

Лабораторная работа №9. Консолидация данных

Цель работы: изучить способы группировки данных и подсчета итоговых значений.

Задание 1. Консолидация по расположению

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

На рис. 5.93 представлен простой пример книги, содержащей итоговый лист Усредненный, который устроен так же, как четыре исходных листа. Эти листы – 2002 год, 2003 год, 2004 год и 2005 год – могут быть консолидированы по расположению, так как все они имеют идентичную структуру данных, размещенных в пяти столбцах и пяти строках.

Методика выполнения работы

  1.  Дайте имена рабочим листам: Усредненный, 2002 год, 2003 год, 2004 год, 2005 год. Отформатируйте и внесите данные, как показано на рисунке 5.93.
  2.  Мы воспользуемся командой Консолидация из меню Данные для консолидации данных из листов 2002 год, 2003 год, 2004 год и 2005 год в листе Усредненный.
  3.  Активизируйте итоговый лист и выделите конечную область, то есть блок ячеек, в который будут помещены консолидированные данные. На рис. 2 конечная область – это диапазон B3:Е6 в листе Усреднение.

Рисунок 5.93 – Заготовка для консолидации

  1.  На ленте Данные щелкните кнопку Консолидация.
  2.  Для усреднения значений из всех исходных листов выберите Среднее в поле с раскрывающимся списком Функция в окне диалога Консолидация. Оставьте флажки в секции Использовать в качестве имен не установленными. Поскольку мы не собираемся создавать связи с исходными листами, флажок Создавать связи с исходными данными также оставьте не установленным (рис. 5.94).
  3.  Введите ссылку для каждого исходного диапазона в поле Ссылка или выделите эти диапазоны с помощью мыши.

Рисунок 5.94 – Заполнение окна консолидации

Конечно, использование мыши является наиболее простым способом ввода ссылок, но если необходимо сослаться на закрытые в данный момент исходные листы, придется ввести эти ссылки с клавиатуры. (Можно использовать кнопку Обзор, чтобы определить местонахождение файла, а затем вручную ввести ссылку на ячейку.)

Вводимая ссылка должна иметь следующую форму:

[ИмяФайла]ИмяЛиста!Ссылка

Если диапазон находится в той же самой книге, имя файла вводить необязательно. Если исходному диапазону было назначено имя, можно использовать это имя вместо ссылки.

  1.  Нажмите кнопку Добавить в окне диалога Консолидация. Excel перенесет ссылку из поля Ссылка в поле Список диапазонов.

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

Обратите внимание, что мы выделили ячейки B3:Е6 в каждом исходном листе. Поскольку мы выполняем консолидацию по расположению и итоговый лист имеет соответствующие заголовки столбцов и строк, то исходные ссылки должны содержать только фактические значения, которые мы хотим консолидировать.

  1.  Нажмите кнопку ОК. Excel усреднит исходные значения и поместит их в итоговый лист, как показано на рис. 5.95.

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

  1.  В столбец F добавьте формулы для подсчета среднего по магазинам с использованием функции СРЗНАЧ, как показано на рис.5.95.

Рисунок 5.95 – Итоговая таблица консолидации

Диапазон B3:Е6 в листе Усредненный теперь содержит средние значения для соответствующих ячеек в четырех исходных листах.

Задание 2. Консолидация по категории.

Теперь рассмотрим более сложный пример. На этот раз исходные листы будут содержать различное количество строк, но одинаковое количество столбцов (рис. 5.96).

Итоговый лист имеет заголовки столбцов от Экзамен 1 до Экзамен 4 – в этом отношении все листы одинаковые. Однако в итоговом листе отсутствуют заголовки строк. Мы вынуждены опустить их, потому что в исходных листах они расположены неодинаково. Как вы увидите, команда Консолидация сама вводит заголовки строк.

В качестве основы для консолидации мы используем категории (Студент) в левом столбце каждого исходного листа.

Методика выполнения работы

  1.  Переименуйте рабочие листы следующим образом: Средний балл, Первый семестр, Второй семестр, Третий семестр, Четвертый семестр.
  2.  Заполните информацией и отформатируйте (можно с помощью стилей) рабочие листы. На каждом листе должны присутствовать графы Студент, Экзамен 1, Экзамен 2, Экзамен 3, Экзамен 4, Средний. Баллы по каждому студенту и экзамену заполните самостоятельно из расчета, что балл должен находиться в пределах от 50 до 100. В графе Средний вычислите среднее значение по строке с помощью встроенной функции.
  3.  Перейдите на лист Средний балл.
  4.  Выделите конечную область.

На этот раз конечная область должна включать столбец А, чтобы Excel мог ввести заголовки для консолидированных строк. Но сколько строк должна содержать конечная область? Чтобы ответить на этот вопрос, мы можем просмотреть каждый исходный лист и определить, сколько имеется различных (уникальных) элементов строк (в данном случае фамилий студентов). Еще проще в качестве конечной области выделить ячейку А3. При задании одной ячейки в качестве конечной области команда Консолидация заполняет необходимую область ниже и справа от этой ячейки. В нашем примере мы выделили более чем достаточное число строк для размещения данных с тем, чтобы сохранить форматирование (диапазон А3:F14). В противном случае вам придется сначала консолидировать данные, а затем использовать команду Стили, чтобы быстро переформатировать итоговые данные

Рисунок 5.96 – Исходные листы для консолидации

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

Рисунок 5.97 – Заполнение окна консолидации

Рисунок 5.98 – Итог консолидации

  1.  Итоговый лист уже имеет заголовки столбцов, и поэтому мы можем исключить их из ссылок на исходные листы. Но наши исходные ссылки должны включать все заголовки строк и столбцы от А до F. Поэтому в поле Ссылка введите или укажите с помощью мыши следующие исходные ссылки (рис. 5.97).
  2.  Нажмите кнопку OK, и итоговый лист будет заполнен, как показано на рисунке 5.98.
  3.  Выделите диапазон числовых ячеек и задайте число десятичных знаков 1.

Задание №3. Создание связей с исходными листами.

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

Команду Консолидация можно также использовать для установления постоянной связи между итоговым и исходными листами. Чтобы создать такую связь, установите в окне диалога Консолидация флажок Создавать связи с исходными данными (рис. 5.99) и выполните консолидацию так же, как и без связи.

Рисунок 5.99 – Установление связи с исходными данными

Когда вы консолидируете данные при этом установленном флажке, Excel создает структуру в итоговом листе, как показано на рис. 5.100. Каждый исходный элемент связан с итоговым листом, и Excel создает соответствующие итоговые элементы. Возможно, вам придется отформатировать некоторые ячейки после выполнения консолидации с установлением связей, потому что в этом случае создаются дополнительные строки и столбцы.

Рисунок 5.100 – Итоговая таблицы консолидации с поддержкой

связей

Команда Консолидация создала в итоговом листе отдельную строку для каждого студента, упомянутого в исходных листах

  1.  Внесите изменения в значения по баллам на исходных листах и посмотрите, что происходит с итоговым листом.
  2.  На основании итогового листа постройте три различных диаграммы.

Самостоятельное задание

В магазине имеется 5 видов товаров (папки, скрепки, кнопки, скоросшиватели, степлеры). Продажа этих товаров осуществлялась в течение двух кварталов (1 квартал – январь, февраль, март; 2 квартал – апрель, май, июнь). Для оценки суммы выручки поквартально, необходимо создать на трех листах таблицы с данными: 1 лист – 1квартал; 2лист -2квартал; 3лист - итого). Воспользовавшись командой Консолидация сделать эти таблицы связанными и в итоговой таблице подсчитать сумму выручки по каждому товару за каждый квартал.


 

А также другие работы, которые могут Вас заинтересовать

64661. АНАЛИЗ МЕТОДОВ КОЛЬКУЛИРОВАНИЯ СЕБЕСТОИМОСТИ ПРОДУКЦИИ 256.15 KB
  Цель курсовой работы - проанализировать методы учета себестоимости продукции. Данная цель реализуется в работе на основе решения следующих задач: раскрыть понятие и сущность себестоимости продукции и описать основные методы учета затрат и калькулирования себестоимости.
64662. Технико-экономическое обоснование предпринимательского решения по созданию нового производства 95.99 KB
  Расчет текущих издержек на производство и реализацию продукции Определение выручки от реализации продукции финансовых результатов реализации проекта и критического объема производства Исходя из поставленной цели курсовой работы необходимо решить следующие задачи: обосновать состав и содержание...
64663. Состояние письма учащихся младших классов школы для детей с ТНР 5.23 MB
  Цель исследования: выявить особенности письма учащихся младших классов школы с ТНР. Гипотеза исследования. Мы предполагаем, что у детей 2 класса с ТНР будут наблюдаться трудности в формировании письма, проявляющиеся в ошибках языкового анализа и синтеза...
64664. КОММЕРЧЕСКИЕ БАНКИ И ИХ ФУНКЦИИ В РЫНОЧНОЙ ЭКОНОМИКЕ 376.41 KB
  Банки составляют неотъемлемую часть современного денежного хозяйства, их деятельность тесно связана с потребностями воспроизводства. Они находятся в центре экономической жизни, обслуживают интересы производителей...
64666. Проектирование водоема-копани в Красноармейском районе Волгограда 1.94 MB
  Природными водоемами являются естественные озера и пруды. Чтобы наглядно показать трудоемкость их создания используют такой показатель как отношение объема накопленной воды к объему земляных работ выполняемых для создания водоема.