Тема: «Таблицы MS Excel 2007»

Практическая работа № 1.

Тема: «Таблицы MS Excel 2007»

Цель: Знакомство с возможностями таблиц - списковMS Excel

1. Заполните диапазон А1: F10 данными по образцу, приведенному на рис.2.2.а, или воспользуйтесь результатами предыдущего занятия и сохраните созданный файл.

1.1. Озаглавьте столбцы.

1.2. Заполните диапазон A2: D10.

1.3. Формулы в диапазон E2: F10 вводить не надо.

1.4. Одну из строк диапазона сделайте дублирующей любую другую строку диапазона.

Рис.2.2.а

Рис.2.2.б

2. Преобразуйте диапазон в таблицу.

2.1. Установите курсор внутрь диапазона.

2.2. Выполните команду Вставка – Таблицы – Таблица и в диалоговом окне Создание таблицы проверьте расположение данных таблицы и нажмите ОК.

После преобразования в таблицу диапазон представлен на рис.2.2.б.

3. Познакомьтесь с контекстной вкладкой Работа с таблицами – Конструктор, которая доступна при переходе к любой ячейке таблицы.

3.1. Убедитесь в возможности прокрутки строк таблицы при сохранении на экране заголовков столбцов таблицы.

3.2. Воспользуйтесь командой Сервис – Удалить дубликаты и проследите за результатом.

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

3.4. Воспользуйтесь командой Стили таблиц – Экспресс-стили и примените один из них.

3.5. Удалите из таблицы одну из строк.

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

Познакомьтесь с особенностями ввода формул в таблицу.

4.1. Добавьте в таблицу еще один столбец справа от столбца Стоимость и озаглавьте его Стоимость 1.

4.2. В произвольную ячейку столбца Стоимость введите вручную формулу, обеспечивающую умножение количества продукции на ее цену, например, в ячейку Е6 может быть введена формула =C6*D6. Обратите внимание на то, что формула распространилась на все остальные ячейки столбца таблицы.

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

Убедитесь в том, что в результате во всех ячейках столбца Стоимость 1 будет записана одинаковая формула =[Количество]*[Цена].

Обратите внимание на Автозаполнение формул – средство, позволяющее выбрать функцию, имя диапазона, константы, заголовки столбцов.

4.4. Дайте имя ячейке А15, в которой находится коэффициент, влияющий на комиссионный сбор, например, komiss. Для этого выберите команду Формулы – Определенные имена – Присвоить имя, предварительно активизируйте ячейку А15. Заполните формулами столбец Комисс. сбор, используя Автозаполнение формул.

Познакомьтесь с управлением именами с помощью Диспетчера имен. Активизируйте его командой Формулы – Определенные имена – Диспетчер имен.

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

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

6.1. Отсортируйте таблицу по наименованию продукции (в алфавитном порядке).

6.2. Отсортируйте таблицу в порядке убывания цены на продукцию.

6.3. С помощью фильтрации найдите данные таблицы для бетона и дверей.

6.4. Рассмотрите возможности Текстовых, Числовых фильтров и Фильтров по дате ( добавьте в конец таблицы столбец с датами поступления товаров на склад).

7. Предъявите результаты работы преподавателю.

Практическая работа № 2.

Практическая работа № 3.

Тема «Организация таблиц»

Цель: Знакомство с организацией вычислений в таблицах.

1. Пользуясь методом группового заполнения листов, создайте на трех листах нового документа таблицу, приведенную на рис.5.1, введя данные в диапазон В4: F8. Дайте листам имена " Таб1", " Таб2", " Таб3".

Практическая работа №4.

Тема «Функции»

Цель: Знакомство с использованиемфункций табличного процессора MS Excel.

1. Научитесь пользоваться математическими и статистическими функциями.

1.1.Создайте таблицу, приведенную на рис.6.1.

Рис.6.1

1.2. Введите в столбец B функции, указанные в столбце А (столбец А заполнять не надо) и сравните полученные результаты с данными, приведенными в столбце В на рис.6.1.

1.3. Проанализируйте результаты и сохраните созданную таблицу в книге.

2. Научитесь пользоваться логическими функциями.

2.1. Активизируйте второй лист созданной книги.

2.2. Введите таблицу, приведенную на рис.6.2.

2.3. В клетку С2 введите формулу, по которой будет вычислена скидк а и скопируйте ее в диапазон С3: С6:

· если стоимость товара < 2000 единиц, то скидка составляет 5% от стоимости товара,

· в противном случае - 10%.

2.4. В клетку D2 введите формулу, определяющую налог и скопируйте ее в диапазон D3: D6:

· если разность между стоимостью и скидкой > 5000, то налог составит 5% от этой разности,

· в противном случае - 2%.

Рис.6.2

2.5. Повторите п.2.3 для следующих условий:

· если стоимость товара < 2000, то скидка составляет 5% от стоимости товара,

· если стоимость товара > 5000, то скидка составляет 15% от стоимости товара,

· в противном случае - 10%.

2.6. В клетку А10 может быть занесена одна из текстовых констант: " желтый", " зеленый", " красный". В клетку А11 введите формулу, которая в зависимости от содержимого клетки А10, будет возвращать значения: " ждите", " идите" или " стойте", соответственно.

2.7. Занесите в клетки Е8: E10 три имени: (Лена, Зина, Вера), а в клетки F8: F10 занесите даты их рождений. В клетку E4 введите одно из упомянутых имен.

Пользуясь конструкцией " вложенного" оператора ЕСЛИ, выполните следующие действия:

- проанализировав имя в клетке Е4, запишите в клетку С12 функцию ЕСЛИ, обеспечивающую:

· вывод даты рождения, взятой из соответствующей клетки,

· если же введено неподходящее имя, вывод сообщения: " нет такого имени".

3. Научитесь пользоваться функциями даты и времени, ссылки и массива.

3.1. Активизируйте третий лист книги Имя_6_1.

3.2. Введите в клетку С2 функцию, отображающую сегодняшнюю дату.

3.3. Введите в клетку С3 функцию ДАТА, отображающую произвольно выбранную дату.

3.4. В клетку С5 запишите функцию ВЫБОР, позволяющую вывести название дня недели для даты, введенной в клетку С2 (понедельник, вторник, среда...).

3.5. В клетку С6 запишите аналогичную функцию для даты, введенной в клетку С3.

3.6. Вычислите возраст человека, поместив дату его рождения в клетку С10. Для этого используйте формулу:

= РАЗНДАТ(С10; СЕГОДНЯ(); " y" )

3.7. Представьте текущее время, используя функции ТДАТА() и СЕГОДНЯ().

3.8. Поместите в соседние ячейки текущую дату и время и дату и время, отстоящую от текущей на трое суток. Найдите количество часов и минут между этими датами, пользуясь форматом [ч]: мм: сс и Общим форматом, а также форматом 13: 30. Зафиксируйте результаты и объясните различие.

3.9. Определите номер текущей недели и выведите сообщение:

" Сейчас идет № недели неделя".

3.10. На четвертом листе книги создайте таблицу, приведенную на рис.6.3.

3.10.1. Дайте имена диапазонам клеток, определяющим полученную стипендию за каждый семестр.

3.10.2. В клетку В8 запишите функцию, дающую ответ на вопрос: " Какую стипендию в n -м семестре получил m -й студент? " Значения n -го семестра и фамилия m -го студента должны быть введены в клетки А8 и А9. Для решения поставленной задачи используйте функции ПРОСМОТР и ВЫБОР.

Рис.6.3

4. Научитесь пользоваться статистическими функциями
РАНГ и ПРЕДСКАЗАНИЕ.

4.1. На пятом листе книги создайте таблицу, приведенную на рис.6.4.

4.2. Используя функцию РАНГ, определите ранги цехов в зависимости от объема продаж по каждому году и поместите результаты в соответствующие клетки таблицы. В ячейки J3: J7 запишите формулы для вычисления средних значений рангов цехов.

4.3. Пользуясь информацией об объемах продаж, спрогнозируйте объемы продаж для каждого цеха в 1999 году, пользуясь функцией ПРЕДСКАЗАНИЕ.

Рис.6.4

Практическая работа № 5

Тема «Диаграммы»

Цель: Знакомство с графическим представлением табличных данных в MS Excel.

1. Введите таблицу, представленную на рис.7.1, на первый и второй листы книги.

Рис.7.1

Практическая работа № 6

Практическая работа № 7

Тема «Связанные таблицы. Расчет промежуточных итогов в таблицах MS Excel»

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

Теоретические сведения:

1. Чтобы вставить в формулу адрес или диапазон ячеек с другого листа, следует во время ввода формулы щелкнуть по закладке этого листа и выделить в нем нужные ячейки. Вставляемый адрес будет содержать название этого листа. Например,

= ‘Зарплата декабрь’! F5 + ‘Зарплата ноябрь’! F5 + ‘Зарплата октябрь’! F5

Происходит связывание информации соответствующих ячеек листов электронной книги.

2. Подведение промежуточных итогов: Данные – Итоги. Под структурированием понимается многоуровневая группировка строк и столбцов таблицы и создание элементов управления, с помощью которых можно скрывать и раскрывать эти группы.

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

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

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

1. Открыть созданный ранее файл «Зарплата». Скопировать содержимое листа «Зарплата за ноябрь» на новый лист электронной книги. Присвоить этому листу название «Зарплата за декабрь». Исправить название месяца в ведомости на декабрь.

2. Изменить значение премии на 46%, Доплаты – на 8%. Убедится в том, что был произведен перерасчет формул.

3. По данным таблицы «Зарплата за декабрь» построить гистограмму доходов сотрудников.

4. Перед расчетом итоговых данных за квартал произвести сортировку по фамилиям в алфавитном порядке (по возрастанию) в ведомостях начисления зарплаты за октябрь – декабрь.

5. Скопировать содержимое листа «Зарплата за октябрь» на новый лист электронной книги. Присвоить этому листу название «Итоги за квартал». Исправить название таблицы на «Ведомость начисления зарплаты 4 квартал».

6. Отредактировать лист «Итоги за квартал» согласно образцу на рисунке. Для этого удалить в основной таблице колонки Оклад и Премия, а также строку с численными значениями процентов премии и удержаний и строку Всего. Между названием таблицы и самой таблицей вставить пустую строку. Вставить новый столбец Подразделение.

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

8. Для расчета промежуточных итогов провести сортировку по подразделениям, а внутри подразделений – по фамилиям. Таблица примет следующий вид:

Подведите промежуточные итоги по подразделениям, используя формулу суммирования, как показано на рисунке. Изучить полученную структуру и формулы подведения промежуточных итогов. Научиться сворачивать и разворачивать структуру до разных уровней кнопками «+» и «–».

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

11. Сохранить файл Зарплата с произведенными изменениями.

Практическая работа № 8

Создание исходной таблицы.

Откроем новую Книгу Excel. Создадим таблицу с заголовками: ФИО; Наименование товаров; Выручка; Дата

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

Сортировка данных.

Упорядочим данные в таблице по фамилии сотрудников таким образом, чтобы их продажи были представлены в алфавитном порядке наименования товаров и в порядке возрастания даты продажи.

Выполним команду Данные. – Сортировка

В этом окне указывается иерархия сортировки:

· Сортировка по – ФИО (по возрастанию)

· Затем по – Наименование товара (по возрастанию)

· В последнюю очередь по - Дата (по возрастанию)

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

Фильтрация.

3.1. Она позволяет выделить нужные данные среди имеющихся.Фильтрация выполняется командой Данные – Фильтр, в которой предлагается выбрать:

· Автофильтр – назначает фильтр на каждое поле данных таблицы (если выделены некоторые столбцы, то фильтр будет применяться только к ним).

· Отобразить все – отмена фильтра.

· Расширенный фильтр – устанавливает сложный фильтр.

Применим фильтрацию ко всем столбцам ( Автофильтр ), тогда в заголовке таблицы будут отображены значки выбора.

3.2. Выберем из таблицы только те продажи, выполненные Ивановым. Для этого щелкнем по кнопке рядом с ФИО и выберем Иванов. Тогда таблица выглядит следующим образом:

3.3. Теперь из полученного списка отберем 5 максимальных значений выручки.

Для этого щелкнем по кнопке рядом с заголовком « Выручка » и выберем пункт « Первые 10 ». Тогда в появившемся диалоговом окне « Наложение условия по списку » укажем, что надо выбрать 5 наибольших элементов списка и нажать Ok.

Тогда в таблице останутся записи:

3.4. Из полученного списка отберем только те продажи, которые были совершены в феврале 2002 года. Для этого в строке «Дата» в предложенном фильтре выберем пункт «Условие». В окне «Пользовательский Автофильтр» установим параметры отображения строк:

огда исходная таблица примет вид:

Примечание: После выполнения всех этих операций отмените фильтрацию данных, убрав флажок bв команде Данные – Фильтр – Автофильтр.

Итоги.

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

Примечание: Для формирования итогов необходимо отсортировать данные.

4.1. Найдем суммарную выручку каждого из работников организации.

Для этого выполним команду Данные – Итоги. В диалоговом окне « Промежуточные итоги » в поле « При каждом изменении в » указывается столбец, содержащий элементы, для которых следует вычислить промежуточные итоги.; в поле « Операция » - выбирается функция суммирования, используемая при вычислении промежуточных итогов.; а в поле « Добавить итоги по » - указываются столбцы, содержащие обрабатываемые значения.

В нашем случае устанавливаем в полях:

· « При каждом изменении в» - ФИО

· «Операция» – Сумма

· «Добавить итоги по» – Выручка

После выполнения команды Итоги таблица примет вид (рис.1):

4.2. А теперь найдем итог продаж по видам товаров. Для этого

· в поле « При каждом изменении в » указываем Наименование товара

· в поле « Операция » указать Сумма

· в поле « Добавить итоги по » - Выручка.

В этом случае таблица принимает вид (рис.2)

Практическая работа № 9

Тема «Подбор параметров»

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

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

Задание 1: Оценка рентабельности рекламной кампании фирмы.

1.1 Создать таблицу оценки рентабельности рекламной кампании фирмы по образцу.

1.2 Присвоить ячейке с процентной ставкой имя «Ставка».

1.3 Произвести расчеты по следующим формулам:

A(n) = A(0) * (1 + j / 12) ^(1-ⁿ⁾

При расчете расходов на рекламу нарастающим итогом надо учесть, что первый платеж равен значению текущей стоимости расходов на рекламу, то есть в ячейку D6 надо ввести значение С6. Для ячейки D7 формула примет вид:

D7 = D6 + C7

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

Для расчета текущей стоимости покрытия скопировать формулу из ячейки С6 в ячейку F6:

F6 = E6 * (1 + Ставка / 12) ^ (1 – $А6)

Сумма покрытия нарастающим итогом рассчитывается аналогично расходам на рекламу нарастающим итогом (то есть в ячейку G6 надо ввести значение F6):

G7 = G6 + F7

Колонка Н6 показывает, в каком месяце была пройдена точка окупаемости инвестиций.

H6 = G6 – D6

Ввести условное форматирование результатов расчета колонки Н: отрицательных чисел – синим курсивом, положительных чисел – красным цветом шрифта.

На какой месяц приходится точка окупаемости?

1.4 Рассчитать количество месяцев, в которых сумма покрытия имеется (использовать функцию Счет из раздела Статистические).

1.5 Произвести расчет количества месяцев, в которых сумма покрытия больше 100000 руб. (использовать функцию СчетЕсли ).

1.6 Построить графики по результатам расчетов:

1.7 «Сальдо дисконтированных денежных потоков нарастающим итогом» по результатам расчетов колонки Н;

1.8 «Реклама: расходы и доходы» по данным колонок D и G (выделять диапазоны, удерживая клавишу Ctrl);

1.9 По графикам сделать вывод об окупаемости рекламной кампании.

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

Задание 2: Фирма поместила в коммерческий банк 45000 руб. на 6 лет под 10, 5% годовых. Какая сумма окажется на счете, если проценты начисляются ежегодно? Рассчитать, какую сумму надо поместить в банк на тех же условиях, чтобы через 6 лет накопилось 250000 руб.?

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

2.2 Произвести расчеты A(n) двумя способами:

2.3 С помощью формулы

A(n) = A(0) * (1 + j) ⁿ

2.4 С помощью функции БС

2.5 Используя режим Подбор параметра рассчитать, какую сумму надо поместить в банк

Задание 3: Сравнить доходность размещения средств организации, положенных в банк на один год, если проценты начисляются m раз в год, исходя из процентной ставки j = 9.5%; по результатам расчета построить график изменения доходности от количества раз начисления процентов в году. Выяснить, при каком значении j доходность (при капитализации m = 12) составит 15%.

3.1 Формула для расчета доходности:

Доходность = (1 + j / m)^m – 1

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

Практическая работа № 10