Решение задач в Excel

Издательство ООО «ЮНИТИ-ДАНА» реализует учебную экономическую литературу в высшие учебные заведения в соответствии с заключенными договорами на год.

Для планирования затрат на приобретение литературы вузах была определена средняя стоимость учебной литературы для студентов, обучающихся по экономическим специальностям. При закупке больших объемов литературы издательство делает соответствующую скидку. Данные для выполнения расчетов представлены на рис. 1.1 и 1.2.

Для решения задачи необходимо следующее.

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

 Средняя стоимость литературы на одного студента, руб.

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

Учебники

Учебные пособия

Практикумы

Общая средняя стоимость литера­туры

ВЗФЭИ

469

156

117

МЭСИ

387

111

94

МГУ

354

125

103

МГТУ

212

165

118

РЭА

398

178

128

МИМО

335

134

106

Процент торговой скидки по вузам

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

Скидка, %

ВЗФЭИ

11,0

МЭСИ

9,5

МГУ

9,4

МГТУ

8,4

РЭА

9,1

МИМО

8,6

Стоимость приобретаемой экономической учебной литературы вузами

№ п/п

Наиме-

нование организа­ции

Количество студен­тов, чел.

Стоимость литера­туры на 1 чел., руб.

Стоимость литера­туры на всех уча­щихся, руб.

Стоимость лите­ратуры с учетом скидки, руб.

1 ВЗФЭИ

1956

2 МЭСИ

1298

3 МГУ

989

4 МГТУ

556

5 РЭА

789

6 МИМО

826

Итого общая стоимость литературы, руб.

Решение задачи средствами MS Excel

1.   Инструкция к решению задачи

 

1. Вызовите Excel (Пуск/Программы/Microsoft Excel)

2. Переименуйте Лист 1 в «Средняя стоимость литературы» (установить курсор на Лист 1, нажать правую кнопку мыши и выбрать переименовать).

3. На первом листе в ячейках таблицы A2:E2 запишите названия столбцов заданной таблицы (рис. 2).

 

Рис. 1. Поля таблицы «Средняя стоимость литературы»

4. Отформатируйте ячейки A3:A8 для ввода текста, а ячейки B3:E8 для ввода чисел

Для этого выделите ячейки, выберите команду «Формат ячеек» в контекстном меню.

Выберите закладку «Число» и в ней формат «Текстовый», а для чисел «Числовой» (рис. 2)

Рис. 2. Изменение типа данных

  1. Заполните таблицу исходными данными (рис. 3).

Рис. 3. Заполнение таблицы

  1. Рассчитайте среднюю стоимость литературы, введя формулу в ячейку E3 и с помощью маркера заполнив ячейки E4:E8 (рис. 4)

 

Рис. 4. Расчет средней стоимости

7. Переименуйте «Лист 2» в «Процент торговой скидки по вузам» (установить курсор на Лист 2, нажать правую кнопку мыши и выбрать переименовать).

8. На втором листе «Лист 2» в ячейках таблицы A2:C2 запишите названия столбцов заданной таблицы.

9. Отформатируйте ячейки A3:A8 для ввода текста, а ячейки B3:B8 для ввода процентов.

10. Заполните таблицу исходными данными (рис. 5).

Рис. 5. Заполнение таблицы

11. Переименуйте Лист 3 в «Стои-сть приобр. экон. лит-ры»

Установите курсор на Лист3, нажмите правую кнопку мыши и выберите «Переименовать».

12. На листе «Стои-сть приобр. экон. лит-ры» в ячейках таблицы A2:Н2 запишите названия столбцов заданной таблицы. Заполните таблицу исходными данными (рис. 6).

Рис. 6. Заполненная таблица «Стоимость приобретаемой экономической учебной литературы вузами»

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

В поле «Категория:» выберите «Ссылки и массивы». В поле «Выберите функцию» нажмите «ПРОСМОТР» (см. рис. 7).

Рис. 7. Выбор функции «ПРОСМОТР»

14. Нажмите кнопку «ОК». Введем искомое значение ‘Средняя стоимость литературы’!A3, Просматриваемый вектор выделим диапазон ячеек ‘Средняя стоимость литературы’!A3:A8, вектор результатов ‘Средняя стоимость литературы’!E3:E8 (рис.8)

Рис. 8. Заполнение аргументов функции «ПРОСМОТР»

15. Маркером автозаполнения заполняем весь столбец «Стоимость литературы на 1 чел., руб.»

16. Для заполнения столбца «Стоимость литературы на всех учащихся, руб.» в ячейке E4 введем формулу =C4*D4 (количество студентов умножим на стоимость литературы на одного студента) и маркером растянем на весь столбец (рис. 9)

Рис. 9. Заполнение столбца «Стоимость литературы на всех учащихся, руб.»

17. Для заполнения столбца «Стоимость литературы с учетом скидки, руб.» в ячейке F4 введем формулу =E4*ПРОСМОТР(‘Процент торговой скидки по вузу’!A3;’Процент торговой скидки по вузу’!A3:A8;’Процент торговой скидки по вузу’!B3:B8) и маркером растянем на весь столбец (рис. 10)

Рис. 10. Заполнение столбца «Стоимость литературы с учетом скидки, руб.»

18. Найдем общую стоимость литературы, руб. Для этого в ячейке F10 выберем команду «Автосумма» из меню «Формулы» (рис. 11)

Рис. 11. Заполнение ячейки «Итого общая стоимость литературы, руб»

19. Добавьте «Лист4» в рабочую книгу и переименуйте его в «Диаграмма»

20. С помощью клавиши Ctrl выделим диапазоны B4:B9,F4:F9. Выберите команду «Гистограмма» в разделе «Диаграммы» меню «Вставка». Выберите необходимый тип гистограммы (рис.12).

Рис. 12. Выбор типа диаграммы

21. Нажимаем ОК и выбираем создать диаграмму стоимости литературы с учетом скидки на отдельном листе, выбрав имя существующего листа «Диаграмма» (рис. 13)

2.   Результаты компьютерного эксперимента и их анализа

Для тестирования правильности решения задачи заполним вход­ные документы, а затем рассчитаем результаты.

Средняя стоимость литературы на одного студента, руб.

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

Учебники

Учебные пособия

Практикумы

Общая средняя стоимость литера­туры

ВЗФЭИ

469

156

117

742

МЭСИ

387

111

94

592

МГУ

354

125

103

582

МГТУ

212

165

118

495

РЭА

398

178

128

704

МИМО

335

134

106

575

 

Процент торговой скидки по вузам

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

Скидка, %

ВЗФЭИ

11.0

МЭСИ

9.5

МГУ

9.4

МГТУ

8.4

РЭА

9.1

МИМО

8.6

 

Стоимость приобретаемой экономической учебной литературы вузами

№ п/п

Наименование организации

Количество студентов, чел.

Стоимость литературы на 1 чел., руб.

Стоимость литературы на всех учащихся, руб.

Стоимость литературы с учетом скидки, руб.

1 ВЗФЭИ

1956

742

1451352

15964872

2 МЭСИ

1298

575

746350

6418610

3 МГУ

989

582

575598

5410621.2

4 МГТУ

556

495

275220

2311848

5 РЭА

789

704

555456

5054649.6

6 МИМО

826

575

474950

4084570

Итого общая стоимость литературы, руб. 39245170.8

 

В результате решения задачи полученные с помощью компьютера ведомости совпадают с тестовыми.

Таким образом, формирование итоговой таблицы «Стоимость приобретаемой экономической учебной литературы вузами» позволяет решить постав­ленную задачу. Создание различных диа­грамм (гистограмм, графиков) на основе данных сводных таблиц средствами MS Excel позволяет не только наглядно представлять результаты обработки информации для проведения анализа с целью принятия решений, но и достаточно быстро осуществлять манипу­ляции в области их построения в пользу наиболее удобного пред­ставления результатов визуализации по задаваемым пользователем (аналитиком) параметрам.

 

Рубрика Информационные технологии. Добавьте постоянную ссылку на эту страницу в закладки.