Алгоритм решения задачи в MS Excel

МОУ «Детчинская средняя общеобразовательная школа»

Проект решения задачи

по спецкурсу «Решение текстовых задач»

Подготовили учащиеся

8 класса

Росколотенко А.

Проверила

Герасимова О. Н.

Детчино 2011

Задача 18

1. Построить таблицы по приведённым ниже данным (рис. 1, рис. 2).

2. Определить средний балл экзаменационной сессии по курсам и по факультету.

3. Результаты округлить до одного десятичного знака после запятой, используя функцию ОКРУГЛ().

4. Определить рейтинг (место по уровню успеваемости) каждого курса, используя функцию РАНГ().

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

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

Рис. 1. Сведения о результатах экзаменационной сессии

Рис. 2. Средний балл по курсам и по факультету

Алгоритм решения задачи:

1. Запустить табличный процессор MS Excel.

2. Создать книгу с именем «Экзаменационная сессия».

3. Лист 1 переименовать в лист «Экзаменационная сессия».

4. На рабочем листе «Экзаменационная сессия» MS Excel создать таблицу «Сведения о результатах экзаменационной сессии».

5. Заполнить таблицу «Результаты экзаменационной сессии» данными (рис. 1).

Рис. 3. Расположение таблицы «Сведения о результатах экзаменационной сессии» на рабочем листе «Экзаменационная сессия» MS Excel

6. Разработать структуру шаблона таблицы «Средний балл по курсам и факультету» (рис. 4.)

Рис. 4. Структура шаблона таблицы «Средний балл по курсам и факультету»

7. Лист 2 переименовать в лист с названием «Средний балл».

8. На листе «Средний балл» создать таблицу «Средний балл по курсам и факультету» (данные с рис. 2.)

Рис. 5. Расположение таблицы «Средний балл по курсам и по факультету» на рабочем листе MS Excel

9. В колонке В5 написать формулу для вычисления среднего балла и округлить полученный результат до одного знака после запятой с помощью функции ОКРУГЛ (данные для вычислений возьмем с первого листа т. е. с листа «Экзаменационная сессия»): =ОКРУГЛ(('Экзаменационная сессия'!A5*'Экзаменационная сессия'!B5+'Экзаменационная сессия'!A6*'Экзаменационная сессия'!B6+'Экзаменационная сессия'!A7*'Экзаменационная сессия'!B7+'Экзаменационная сессия'!A8*'Экзаменационная сессия'!B8)/('Экзаменационная сессия'!B5+'Экзаменационная сессия'!B6+'Экзаменационная сессия'!B7+'Экзаменационная сессия'!B8);1), нажмём ENTER. В ячейки В5 средний балл вычислен.

Рис.6. Формулы для вычисления среднего балла на рабочем листе MS Excel

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

11. В ячейки В10 напишем формулу для вычисления среднего балла по факультету: =(B5+B6+B7+B8+B9)/5.

Рис.7. Вычисление среднего балла в на рабочем листе MS Excel

12. В ячейки С5 напишем формулу для вычисления рейтинга по курсам: =РАНГ(B5;$B$5:$B$9).

13. С помощью автозаполнения растянем формулу до ячейки С5.

Рис.8. Формулы для вычисления среднего балла и рейтинга на рабочем листе MS Excel

Рис.9. Вычисление среднего балла и рейтинга в на рабочем листе MS Excel

14. Результаты вычислений среднего балла и рейтинга представим в графическом виде. Выделим столбцы «Курс» и «Средний балл» и с помощью Мастера диаграмм построим график функции «Средний балл по курсам и факультету». Выберем тип диаграммы – гистограмма.

Рис.10. Диаграмма «Средний балл по курсам и факультету»

15. Выделим столбцы «Курс» и «Рейтинг» и с помощью Мастера диаграмм построим график функции «Рейтинг по курсам». Выберем тип диаграммы – круговая.

Рис.11. Диаграмма «Рейтинг по курсам»

16. Листу 3 дадим название «Итоговая ведомость».

17. На листе «Итоговая ведомость» построим таблицу «Ведомость итогов экзаменационной сессии по факультету»

18. С помощью формул свяжем таблицы «Сведения о результатах экзаменационной сессии» и «Средний балл по курсам и факультету». Данные отобразим в таблице «Ведомость итогов экзаменационной сессии по факультету».

19. В ячейки В6 запишем формулу для переноса количества «2» для первого курса: ='Экзаменационная сессия'!B8. Аналогичными формулами заполним диапазон ячеек В6:Е10. С помощью этих формул количество «2», «3», «4», «5» перенесётся из таблицы «Сведения о результатах экзаменационной сессии» в таблицу «Ведомость итогов экзаменационной сессии по факультету».

20. В ячейки F6 пропишем формулу для переноса среднего балла для каждого курса из таблицы «Средний балл по курсам и факультету» в таблицу «Ведомость итогов экзаменационной сессии по факультету»: ='Средний балл'!$B$5. Аналогичными формулами заполним ячейки F7:F10.

21. Для вычисления рейтинга по курсам в ячейки G6 запишем формулу которая свяжет таблицы «Средний балл по курсам и факультету» и «Ведомость итогов экзаменационной сессии по факультету»: ='Средний балл'!$C$5. Аналогичными формулами заполним диапазон ячеек F7:F10.

22. В ячейках B11, C11, D11, E11 с помощью автосуммы подсчитаем количество «2», «3», «4», «5» по факультету.

23. В ячейки F11 запишем формулу для среднего балла по факультету (данные возьмем из таблицы «Средний балл по курсам и факультету»: ='Средний балл'!$B$10.

Рис.12. Формулы для составления «Ведомости итогов экзаменационной сессии по факультету» на рабочем листе MS Excel

Рис.13. «Ведомость итогов экзаменационной сессии по факультету»