5. Лабораторный практикум Лабораторная работа № 1 «Проектирование и создание таблиц в excel на примере реализации методов многомерного сравнительного анализа»

Цель. Получить навыки:

• работы с книгой, листами, ячейками и диапазонами ячеек EXCEL;

• ввода формул;

• использования простейших функций (=макс(…); =мин(…); =срзнач(…); =сумм(…));

• ввода и интерпретации статистической функции =ранг(…).

1. Открыть файл БД к лаб раб.xls

2. Перейти на лист БД

3. Вставить первую строку. В ячейку А1 вести название таблицы «Социально-экономические показатели регионов РФ»

4. Удалить столбец «Интегральная оценка обеспеченности населения региона товарами длительного пользования»

5. Отформатировать ячейки в диапазоне С2:Q2 (шрифт Arial; размер 8; отображение – «переносить по словам»; заливка – желтая)

6. Задать обрамление ячейкам таблицы. Для этого выделить всю таблицу (диапазон ячеек А2:Q82), назначить режим «Все границы»

7. Заполнить столбец № п.п. (диапазон ячеек А4:А82). Для этого автозаполнением ввести для регионов номера по порядку.

8. В диапазон ячеек С3:Q3 ввести обозначение показателей. Для первого – Y; для остальных автозаполнением Х1, Х2 и т.д.

9. Осуществить операцию «выравнивание по центру» для данных в диапазонах ячеек А4:А82 и С3:Q3

10. Задать ячейкам в диапазоне С4:Q82 числовой формат с точностью «Число десятичных знаков – 1», осуществить операцию «выравнивание по центру».

11. Создать три копии листа БД.

12. Переименовать полученные листы сл. образом: Мн. средней; Паттерн; Суммы мест.

13. Переместить лист Суммы мест, поместить его перед листом Мн. средней.

14. Объединить листы Мн. средней; Паттерн; Суммы мест.

15. Выделить диапазон ячеек С4:Q82, почистить выделенные ячейки. Добавить два столбца: Интегральная оценка (R3) и Рейтинг (S3). Задать обрамление ячейкам последних двух столбцов таблиц. Для этого выделить диапазон ячеек R2:S82, назначить режим «Все границы». Залить краской.

16. Разгруппировать листы Мн. средней; Паттерн; Суммы мест

17. На листе БД в таблицу добавить три строки: Среднее (ячейка В83); Максимум (ячейка В84); Минимум (ячейка В85).

18. В ячейку С83 ввести функцию =СРЗНАЧ(C4:C82), скопировать ее по строке (диапазон ячеек D83:Q83).

19. В ячейку С84 ввести функцию =МАКС(C4:C82), скопировать ее по строке (диапазон ячеек D84:Q84).

В ячейку С85 ввести функцию =МИН(C4:C82), скопировать ее по строке (диапазон ячеек D85:Q85).

21. Задать обрамление ячейкам последних трех строк таблицы. Для этого выделить диапазон ячеек А83:Q85, назначить режим «Все границы». Залить краской.

22. Перейти на лист Мн. Средней, осуществить расчет частных безразмерных оценок для «прямых» и «обратных» показателей. Для этого необходимо установить курсор в ячейку С4 и ввести в нее формулу =БД!C4/БД!C$83. Получим частную оценку для «прямого» показателя. Скопировать введенную формулу по столбцу, используя возможности относительной и абсолютной адресации ячеек. Для «обратных» показателей (Х3, Х4, Х10, Х11) формула должна быть обратной, например для Х10: =БД!M$83/БД!M4.

23. Определить интегральную оценку по каждому экономическому субъекту. Для этого в ячейку R4 вводим функцию =СУММ(C4:Q4) и копируем ее по столбцу. Задать ячейкам в диапазоне С4:R82 числовой формат с точностью «Число десятичных знаков – 3»

24. Для определения рейтинга экономического субъекта среди всей совокупности регионов в ячейку S4 ввести функцию =РАНГ(R4;$R$4:$R$82) и копировать ее по столбцу. Задать рангам формат целых чисел.

25. Перейти на лист Паттерн, осуществить расчет частных безразмерных оценок для «прямых» и «обратных» показателей. Для этого необходимо установить курсор в ячейку С4 и ввести в нее формулу =БД!C4/БД!C$84. Получим частную оценку для «прямого» показателя. Скопировать введенную формулу по столбцу, используя возможности относительной и абсолютной адресации ячеек. Для «обратных» показателей (Х3, Х4, Х10, Х11) формула должна быть обратной, например для Х10: =БД!M$85/БД!M4.

26. Повторить пункты 23-24 для метода Паттерн.

27. Метод суммы мест предполагает использование рангов в качестве частных безразмерных оценок. Перейти на лист Сумма мест, осуществить расчет частных безразмерных оценок для «прямых» и «обратных» показателей. Для этого необходимо установить курсор в ячейку С4 и ввести в нее функцию =РАНГ(БД!C4;БД!C$4:C$82). Получим частную оценку для «прямого» показателя. Скопировать введенную формулу по столбцу, используя возможности относительной и абсолютной адресации ячеек. Для «обратных» показателей (Х3, Х4, Х10, Х11) в функцию =РАНГ(…) вводим третьим параметром цифру 1 (если третий параметр 0, ранжируется «чем больше оценка, тем «первее» субъект, т.е. цифра места меньше; если третий параметр не 0 (ноль), то осуществляется обратное ранжирование, чем больше оценка, тем больше баллов получает субъект). Например, для Х3: =РАНГ(БД!F4;БД!F$4:F$82;1).

28. Повторить пункты 23-24 для метода Суммы мест. Учесть особенность определения окончательного ранга экономических субъектов для метода Суммы мест (=РАНГ(R4;R$4:R$82;1). Сохранить результаты работы в файл.