Microsoft Excel. От основ к задачам обработки и представления данных (стр. 22 )

·  произвести подсчет количества каждого вида оценок и определить процент к общему количеству оценок (с помощью данной операции – подсчета процента части к общему можно узнать преобладающее значение в массе), построить диаграмму, отражающую количество оценок каждого вида;

·  рассчитать средний балл каждого студента (с округлением до двух знаков после запятой) и определить категорию, к которой он относится (двоечник, троечник, хорошист или отличник), а также процент каждой категории к общему количеству студентов;

·  выделить категорию двоечников условным форматированием;

·  определить стипендиальной фонд в зависимости от размера стипендии для каждой категории студентов (троечников, хорошистов, отличников);

·  рассчитать средний балл по каждому предмету и найти предмет с максимальным средним баллом.

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

8.2. Решение задачи

Для простоты будем обозначать студентов С1, С2, С3 и т. д. Кроме того, введем краткие обозначения предметов П1, П2, П3 и т. д. Посмотрите, как записаны результаты в таблице Excel. Обратите внимание, что у ячеек с условными названиями предметов есть примечание – пояснительный текст к ячейке (ячейки с примечанием имеют красный треугольник в правом верхнем углу) (рис. 82).

При наведении курсора мыши на ячейку с примечанием Excel показывает текст примечания. Используйте примечания, когда хотите сообщить дополнительную информацию пользователю или пометить важные данные. Мы будем использовать примечания для расшифровки названий предметов и сокращения «Ср. балл».

8.2.1. Работа с примечаниями

Для добавления примечания к ячейке необходимо выполнить следующие шаги:

1.  Выбрать ячейку, к которой следует добавить примечание.

2.  Выбрать команду «Примечание» в меню «Вставка».

3.  Ввести текст примечания в соответствующее поле.

4.  После окончания ввода текста нажать кнопку мыши вне области примечания.

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

Замечания.

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

2.  Вставить примечание можно мышью. Для этого вызовите нажатием правой кнопки мыши на необходимой ячейке контекстное меню и выберите пункт «Добавить примечание».

Для изменения примечания необходимо выполнить следующие шаги:

1.  Выбрать ячейку, примечание к которой нужно изменить.

2.  Выбрать команду «Изменить примечание» в меню «Вставка».

Либо с помощью контекстно-зависимого меню вызываемого правой кнопкой мыши по команде «Изменить примечание».

Удалить примечание также можно с помощью контекстно-зависимого меню, либо с помощью команд «Очистить», «Примечание» из меню «Правка».

Решение нашей задачи располагается правее исходной таблицы, фон области решения выделен цветом. Будет естественным располагать данные, относящиеся к каждому студенту правее начальных данных. Из условия необходимо найти средний балл каждого студента и определить категорию, к которой относится студент. Кроме того, нам понадобится подсчитать общее количество оценок каждого вида. В этих двух условиях Вы должны "уловить" общее – они разрешаются одним приемом – подсчетом оценок каждого вида у каждого студента. Тогда нам будет легко определить общее количество двоек (суммированием элементов одного столбца), троек, четверок, пятерок и сделать вывод о категории, к которой относится студент.

8.2.2. Определение среднего балла

Средний балл можно найти арифметическим сложением всех оценок и делением полученной суммы на количество предметов. Однако при количестве значений, для которых требуется определить среднее более пяти, данный способ не является эффективным. В Excel есть функция СРЗНАЧ, с помощью которой можно найти среднее значение для диапазона ячеек. Округление можно вести двумя способами:

·  Установкой числового формата ячеек с двумя знаками после запятой (с помощью команды «Формат ячеек …» контекстно-зависимого меню вызываемого правой кнопкой мыши или с помощью команды «Ячейки» меню «Формат» или с помощью клавиш Ctrl+1 или с помощью кнопок «Увеличить разрядность» , «Уменьшить разрядность» на панели инструментов «Форматирование»);

·  Использованием функции Excel ОКРУГЛ.

Для большого количества ячеек эффективнее использовать функциональное округление, так как позволяет сократить количество выделений больших диапазонов ячеек (что отнимает определенное время).

Таким образом, для определения среднего балла необходимо ввести следующую формулу в колонку со средним баллом:

=ОКРУГЛ(СРЗНАЧ(AB17:AM17);2)

Единственным параметром функции СРЗНАЧ является диапазон ячеек, для которых определяется среднее значение. В нашем случае этот диапазон составляют ячейки с оценками по предметам для одного студента. Для функции ОКРУГЛ параметром является округляемое значение и количество знаков после запятой, которое необходимо оставить, указываемое через точку с запятой.

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

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

Диапазон (интервал) ячеек – совокупность ячеек, представляющая собой прямоугольную область, не имеет исключенные и дополнительные ячейки вне прямоугольной области.

8.2.3. Определение количества оценок каждого вида

Для выполнения этой задачи в Excel существует интересная функция СЧЁТЕСЛИ. Эта функция подсчитывает количество непустых ячеек внутри интервала, удовлетворяющих заданному критерию. Параметрами функции являются интервал (диапазон) ячеек и критерий.

Критерий – это критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки".

Например.

1.  Пусть ячейки с A3 по A6 содержат слова "яблоки", "апельсины", "персики", "яблоки" соответственно: =СЧЁТЕСЛИ(A3:A6;"яблоки") равняется 2.

2.  Пусть ячейки с B3 по B6 содержат 32, 54, 75, 86 соответственно: =СЧЁТЕСЛИ(B3:B6;">55") равняется 2.

Данную функцию можно использовать для подсчета количества оценок каждого вида:

·  Для двоек: =СЧЁТЕСЛИ(AB17:AM17;2);

·  Для троек: =СЧЁТЕСЛИ(AB17:AM17;3);

·  Для четверок: =СЧЁТЕСЛИ(AB17:AM17;4);

·  Для пятерок: =СЧЁТЕСЛИ(AB17:AM17;5).

Переключитесь в Excel и посмотрите формулы расчета среднего значения и подсчета количества оценок каждого вида у одного студента.

Подсчитать же общее количество можно, просуммировав столбы с оценками каждого вида, или применяя функцию СЧЁТЕСЛИ, но для всего диапазона ячеек с оценками. Последний вариант и реализован в таблице.

Обратите внимание на подсчет общего количества оценок (ячейка AW16), в данном случае подсчет происходит умножением количества студентов на количество предметов. Это не совсем верно, так как не учитывает возможность отсутствия оценки у студента. Корректнее было бы суммировать либо диапазон с количеством оценок каждого вида у каждого студента, либо суммировать итоговые значения оценок каждого вида.

Подсчет основывается на двух функциях ЧИСЛСТОЛБ и ЧСТРОК, имеющих только один параметр – диапазон ячеек. Первая определяет число столбцов в диапазоне, а вторая число строк. Конечно, когда диапазон маленький, то подсчитать соответствующие значения не составляет труда, однако если диапазон большой или явно не указан (вычисляется с помощью других формул), то вычисление числа строк и столбцов удобнее производить с помощью указанных функций.

8.2.4. Определение категории студента

Для определения категории студента нам необходимо знать количество оценок каждого вида. Как человек определяет категорию студента сравнением количества оценок каждого вида, так же будет сравнивать Excel. Для простоты мы разберем классификацию студентов на две категории: отличник и двоечник. Полный же анализ произведем далее – программно.

Для сравнения в Excel есть функция ЕСЛИ. Данная функция возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА (условие выполняется), и другое значение, если ЛОЖЬ (условие не выполняется). Функция ЕСЛИ используется для условной проверки значений и формул.