Задание«Построение расчётной таблицы и визуализация данных средствами MSExcel»: инструкция по выполнению

Задание:

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

Требования:

1. Таблица с результатами проверочной работы состоит из граф №, ФИО, Оценка, упорядочена по алфавиту фамилий. Не менее 15 учеников, какие и с какими оценками – на усмотрение автора.

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

3. В таблице Количество оценок в ячейках правого столбца стоят формулы (функция СчётЕсли). Формула должна допускать автозаполнение (т.е. при копировании формулы из первой строки в последующие должны получаться правильные результаты).

4. В таблице Обобщающие показатели при расчёте среднего балла применить функцию СрЗнач (формат результата – числовой, 2 знака после запятой).

5. Проценты хороших оценок и двоек в таблице Обобщающие показатели вычисляются прямым вводом формул, формат результата – процентный, 2 знака после запятой.

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

Выполнение задания:

1. Запустите табличный процессор MSExcel, выбрав в Главном меню Всепрограммы – MS Office – MS Excel.

2. В ячейки, начиная, например, с В2, введите названия столбцов таблицы с оценками.

3. Чтобы изменить ширину столбцов, установите указатель мыши на границу между обозначениями столбцов (например, между Cи D) и перетащите её в нужном направлении.

4. Введите фамилии учеников (по возможности не те, что в примере) и их оценки.

5. Отсортируем таблицу: выделим столбцы с именами и оценками (без их названий), на вкладке Главная в группе Редактирование раскроем список Сортировка и фильтр, выберем Настраиваемая сортировка и попросим отсортировать по алфавиту ФИО. Внимание: выделены должны быть оба столбца, иначе оценки «оторвутся» от своих хозяев!

6. Поставим номера в первом столбце с помощью автозаполнения. Для этого введём в первые ячейки столбца числа 1 и 2, выделим обе этих ячейки. В правом нижнем углу выделяющей рамки появится маленький чёрный прямоугольничек – маркер автозаполнения. При наведении на него мыши указатель меняется. Если «протащить» выделение за этот маркер вниз по столбцу – столбец заполнится последовательными натуральными числами.

7. Разграфим таблицу – выделим её, на вкладке Главная в группе Шрифт раскроем список Границы, и сначала выберем Все границы, а затем Толстая внешняя.

8. Оформим шапку таблицы: выделим ячейки, на вкладке Главная в группе Шрифт зададим полужирный, в группе Выравнивание зададим выравнивание по центру. Через список Цвет заливки в группе Шрифт зададим желаемый цвет.

9. Подобным же образом можно отформатировать другие ячейки таблицы. Отступ ФИО от левого края можно сделать, если после выделения всех ячеек столбца нажать кнопку Увеличить отступ в группе Выравнивание.

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

11. Приступим к вводу формул. Выделим ячейку, в которой хотим посчитать количество двоек. Откроем вкладку Формулы. В списке Другие функции (Рис.1) выберем Статистические – СЧЁТЕСЛИ (список придётся прокрутить).

12. Открывается окно ввода параметров функции (рис.2). Убедимся, что курсор стоит в поле Диапазон, выделим мышью те ячейки, в которых стоят оценки – в поле появится буквенно-числовое обозначение этого диапазона, а справа от него – перечень значений - затем кликнем в поле Критерий (курсор переместится туда) и по соседней ячейке, где стоит цифра 2. Под заданными параметрами выведется значение функции – количество двоек. Завершим ввод формулы, нажав ОК.

13. Для того, чтобы формулу можно было размножить, адреса диапазона, в котором считаются значения, нужно сделать абсолютными (не изменяемыми при тиражировании). Для этого выделить ячейку с формулой, в строке формул (рис.3) установить курсор на адрес начала диапазона, нажать F4 (перед элементами адреса появятся символы “$”. То же проделать с адресом конца диапазона. Окончательный вид формулы должен быть такой, как на рисунке.

Рисунок 2. Окно параметров функции

Рисунок 3. Формула с абсолютными адресами

 

14. С помощью маркера автозаполнения «растянуть» формулу вниз ещё на 3 ячейки, чтобы сосчитать количество остальных оценок.

15. Для подсчёта среднего балла в таблице Обобщающие показатели воспользуемся функцией СРЗНАЧ, указав в качестве диапазона ячейки с оценками.

16. Формулы процента четвёрок-пятёрок и процента двоек введём без помощи функций. Выделить ячейку для подсчёта процента хороших оценок. Ввести знак «=» (включается режим ввода формул). Вводим с клавиатуры скобку, щёлкаем мышью по ячейке с количеством четвёрок (её адрес включается в формулу), вводим «+», щёлкаем по количеству пятёрок, закрываем скобку, далее знак деления «/» и цифрами – количество учеников. Последняя формула вводится аналогично.

17. Не забывайте по ходу выполнения работы сохранять файл (соответствующие команды – на вкладке Файл).