Практическое занятие_екон. информ

Практическое занятие № 2

"Microsoft Excel "

1.     Создайте в табличном процессоре Excel таблицу умножения Пифагора, оформите ее как приведено ниже (двойная линия обрамления, узор). Цифры 1-9 вводятся с использованием автозаполнения (1, 2 и перетаскивание черного крестика). Все ячейки с белой заливкой должны быть рассчитаны по одной универсальной для всех ячеек формуле (формула пишется в одной ячейке и копируется в остальные, используется соответствующее копированию фиксирование адреса ячеек - "$").

Таблица 1. Таблица умножения Пифагора

2.     Сохраните книгу под именем Практика по Excel.

3.     На втором листе рассчитайте и оформите таблицу квадратов (см. образец ниже). Используйте следующие функции: СТЕПЕНЬ (для возведения в квадрат), ЗНАЧЕН (преобразовать текстовое значение в число), СЦЕПИТЬ (для соединения десятков с единицами).

Получится: СТЕПЕНЬ(ЗНАЧЕН(СЦЕПИТЬ(десятки;единицы));2)

Таблица 2. Таблица квадратов

Используя логику, попробуйте найти более легкий способ заполнить данную таблицу.

 

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

5.     На третьем листе создайте, рассчитайте и отформатируйте таблицу 3. Требования: - данные столбца А вводятся с использованием автозаполнения; - данные столбца В рассчитываются на основании первых двух ячеек (1 и 140), формулы пишутся в 3 и 4 ячейки и копируются во все остальные, для отображения различным цветом отрицательных и положительных чисел используйте формат ячейки; - столбец С  рассчитывается с использованием функции логической ЕСЛИ, для отображения различным цветом отрицательных и положительных используйте условное форматирование меню формат; столбец D, Е , F и G рассчитываются с использованием соответствующих математических функций для отображения различным цветом отрицательных и положительных чисел используйте дополнительный формат числа в формате ячейки; - столбец H рассчитывается с помощью функции даты и времени ТекущаяДата, используйте дополнительные форматы отображения даты.

Таблица 3. Использование функций

 

6.     На основании таблицы 3 на новом листе рассчитайте следующую таблицу:

Таблица 4. Использование функций

Показатели			Группа функций	Результат
Сумма абсолютных значений отрицательных чисел столбца B			Математические
Количество результатов "да" в столбце С			Статистические
Сумма произведений (A*F)			Математические
Среднее арифметическое значение в столбце F			Статистические
Самая ранняя дата (минимальное значение) в столбце H			Статистические
Дата, соответствующая значению	(Вводится любое  значение из столбца А)	столбца А	Ссылки и массивы (ВПР, СТОЛБЕЦ)
Адрес ячейки, где находится найденная самая ранняя дата			Ссылки и массивы (АДРЕС, ПОИСКПОЗ, СТОЛБЕЦ)

7.     Рассчитанную таблицу 4 скопируйте в отчет по практике.

"Microsoft Excel : расчет амортизации"

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

Создайте книгу электронных таблиц  «амортизация» в программе Excel и сохраните в папку Практика по информатике.

На первом листе сделайте и оформите таблицу.

Вынесите на панель инструментов кнопки содержащие следующие команды: Отмена объединения ячеек, Вставить значения, вставить знак умножения, знак деления, знак «плюс» и знак «минус»..

Таблица 1. Расчет годовой суммы амортизации компьютера

По данным таблицы на первом листе, используя стандартные функции Excel, необходимо рассчитать годовую сумму амортизации компьютера тремя способами: метод суммы лет (функция АМГД), линейная (АМР), уменьшающегося остатка (ДДОБ).

Расчет по каждому способу должен быть оформлен на отдельном листе. Для этого сделайте еще 2 копии первого листа двумя способами: 1) выделить все на первом листе, скопировать в буфер обмена и вставить на втором листе значения и форматы (специальная вставка); 2) используя контекстное меню к ярлыку листа, сделать копию первого листа. Листы должны иметь название соответствующее способу начисления амортизации (например: Линейный). Исходные данные (балансовая и остаточная стоимость, полезный срок службы и текущий период) вводятся только на первом листе, ячейки с исходной информацией на втором и третьем листах должны быть завязаны формулами с первым листом.

Таблицы должны быть оформлены, как приведено.

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

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

Остаток (недоамортизированная сумма) = (Балансовую стоимость – Остаточную стоимость) – Итого. Формат этих данных должен быть денежный, с двумя десятичными знаками.

Процент износа = сумма амортизации / (Балансовую стоимость – Остаточную стоимость). Формат данных должен быть процентный.

Накопительный процент износа = сумме Накопительного процента износа за предыдущий период + Процент износа за текущий год.

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

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

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

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

Сохраните книгу в папку Практика по информатике.

Практическое занятие № 3

1.     Откройте Word и документ отчет по информатике.doc

2.     Сделайте перед списком литературы заголовок 1 – Расчет амортизации.

3.     Откройте в Excel файл амортизация.xls.

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

5.     Скопировав таблицу с расчетом методом уменьшающегося остатка, вставьте ее через Специальная вставка...  Вставить  Лист Microsoft Excel (объект).

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

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

8.     Вставьте, используя средства Word, к таблицам и графикам названия (Таблица и Рис.), отражающие их содержание.

9.     Перейдите в MS Excel и измените балансовую стоимость в первой таблице, введя 200 000. Перейдите в MS Word и определите изменения и в каких таблицах и графиках они произошли.

10. Перед списком литературы сделайте заголовок 1 «Работа с графическим редактором Paint».

11. Примените к созданному разделу (только к текущему разделу, который отделите разрывами со следующей странице) альбомную ориентацию.

12. Вставьте рисунки из файлов, которые были созданы на практическом занятии по графическому редактору Paint из папки Практика по информатике.

13. Вставьте, используя средства Word, к рисункам названия (Рис.), отражающие их содержание.

14. Установите в формате одного из рисунков обтекание сверху и снизу. Растяните рисунок на всю ширину и высоту страницы (до полей).

15. После списка литературы. Используя стиль заголовок 1, создайте заголовки: «Список иллюстраций» и «Список таблиц».

16. После заголовков вставьте указатели таблиц и рисунков, пользуясь средствами Word.

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

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

19. Сохраните документ.

Откройте MS Excel.

1.     Построить Таблицу 1. В столбцы Регион, Месяц и Наименование продукции данные вводить, используя возможность выбора из списка. Сохраните книгу, в которой вы работаете под именем Таблицы.xls на диске H:.

2.     Отсортируйте по алфавиту столбец Регион; столбец Месяц в порядке следования в 1-ом квартале месяцев без потери соответствия данных! (Для этого выделите всю таблицу, выберите Пункт меню Данные - Сортировка - Сортировать по: Регион - по возрастанию: Затем по: Месяц - по убыванию).

3.     Используя функцию Автосумма рассчитайте Итого.

4.     Оформите наглядно Таблицу 1: задайте обрамление, заливку цветом, размер и тип шрифта (по своему усмотрению).

5.     Присвоить ячейке, в которой содержится общая сумма Выручки имя Итого (Вставка - Имя - Присвоить или используя поле имя ячейки).

6.     Закрепите шапку Таблицы 1.

7.     Установите на Таблицу 1 автофильтр.

8.     Используя средства автофильтра, отобразите, выручку за проданную продукцию по Южному региону за Месяц Март по молочным продуктам. Выделите полученные строки красным цветом.

9.     Установите режим - отобразить все.

10. Переименуйте лист 1 в Исходные данные.

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

12. Постройте Таблицу 2.

13. Используя функцию СуммЕсли, рассчитайте Объем выручки по молочным продуктам по всем регионам. Для ячейки, отображающей рассчитанную сумму, задайте дробный формат (простые дроби).

14. Используя возможности автофильтра Таблицы 1 (условие или пользовательский автофильтр), и написав формулу автосуммирования в Таблице 2, рассчитайте Объем выручки по всем продуктам  западного региона в общей сумме выручки. Для ячейки, отображающей рассчитанную сумму, задайте процентный формат.

15. Используя возможности автофильтра (условие или пользовательский автофильтр) Таблицы 1, и написав формулу автосуммирования в Таблице 2, рассчитайте Объем выручки по тем продуктам, выручка за которые превышает 150 тыс. руб.

16. Добавьте ниже Объем выручки по продуктам, сумма которых превышает 150 тыс. руб строку, и напишите показатель Курс доллара, руб/долл и в столбце значение введите курс доллара на дату расчета.

Таблица 1. Динамика продаж по регионам

Таблица 2. Сводные показатели продаж

17. Рассчитайте на основании полученной суммы, выручку в долларах США. Для ячейки, отображающей рассчитанную сумму, задайте денежный формат ($).

18. Определите по всем регионам: во сколько раз выручка продавца Казанцева А. оказалась больше, чем у продавца Ворфоломеева А. Для ячейки, отображающей рассчитанную сумму, задайте числовой формат (число знаков после запятой равно 2).

19. Скройте все формулы для расчетных ячеек Таблицы 2.

20. Защитите лист Таблица 2 от внесение изменений, установив пароль 111. Проверьте работоспособность защиты, попробовав внести изменения.

21. Снимите защиту.

22. Скройте лист Таблица 2.

23. Вынесите на панель инструментов значок мастера сводных.

24. На основе данных Таблицы 1 построить на отдельном листе сводную таблицу 3: Вынести в Поле страницы Регион и Месяц продаж; в Поле столбца - Наименование товара; Поле данных - Выручка за проданную продукцию; Поля строки - Фамилии продавцов.

25. Установите Масштаб отображения листа 75%.

26. Отключите в параметрах сводной таблицы автоформат и включите сохранять форматирование.

27. Придайте сводной Таблице 3 наглядный вид:

      используя Формат ячеек выровняйте содержимое ячеек по Горизонтали: по значению, по Вертикали: по центру; установите переносить текст по словам

      установите автоподбор ширины столбцов.

      установите на свой вкус обрамление, заливку цветом, размер и цвет шрифта;

      все денежные значения должны быть отображены без десятых.

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

29. Отобразите скрытый лист таблица 2.

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

31. Вырежьте в соседнюю ячейку из Таблицы 1 всю выручку по Западному региону.

32. Произведите обновление сводной таблицы (проверьте правильность изменений в Таблице 2).

33. Верните Таблицу 1 в исходный вид.

34. Постройте Сводную таблицу 4 на отдельном листе: Вынести в Поле страницы Регион и Наименование товара, в Поле столбца - Месяц, Поле данных - Выручка за проданную продукцию. В качестве Поля строки принять Фамилии продавцов.

35. Примените к полученной таблице автоформат Классический.

36. Установите имя продавца и месяц, который имеет наименьший объем продаж по Западному региону по всем видам продуктов.

37. Скопируйте полученное значение в ячейку наименьшая выручка Таблицы 2. (Правильный ответ Восковой С., Февраль 30 тыс.)

38. Верните Сводную Таблицу 2 к первоначальному варианту (установите отобразить Все и параметры отображения - автоматически -показывать 10 наибольших). Для числовых данных Сводной Таблицы 2 установите формат ячейки - числовой (число знаков после запятой -2).

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

40. Постройте на отдельном листе Сводную таблицу 3: Вынести в Поле страницы Регион и Наименование товара. Поле данных - Выручка за проданную продукцию. В качестве Поля строки принять Фамилии продавцов и Месяц продаж

41. Переименуйте все листы, на которых находятся сводные таблицы, дав им имена соответствующих таблиц (например, Сводная таблица 1).

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

43. Отобразите Сводную таблицу: Регион - Западный, Товар- мясопродукты.

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

45. Измените вид диаграммы:

      Установите формат заголовка диаграммы - Вид - Заливка - обычная;

      Добавьте легенду,

      Установите подписи значений - Доля.

46. Откройте документ отчет.doc.

47. Создайте гиперссылку c текстом «Лабораторная работа по сводным таблицам» на файл Таблицы.xls.

48. Создайте гиперссылку c текстом «Сайт кафедры ИОМАС» на сайт кафедры ИОМАС (www.iomas.vsau.ru).

49. Проверьте работоспособность гиперссылок.

50. Сохраните изменения в файлах программ Word и Excel.

51. Покажите преподавателю результаты выполнения лабораторных работ по Word и Excel.

52. Закройте файл Таблицы.xls.

53. Закройте документ отчет.doc.