И. В. Степанченко Microsoft Excel От основ к задача

Вид материала

Задача

Содержание 8.2. Решение задачи Рис. 82. Ячейки с примечаниями. 8.2.1. Работа с примечаниями Вставить примечание можно мышью. Для этого вызовите нажатием правой кнопки мыши на необходимой ячейке контекстное меню и выберит 8.2.2. Определение среднего балла 8.2.3. Определение количества оценок каждого вида 8.2.4. Определение категории студента ЕСЛИ. Данная функция возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА Замечание. До семи функций Рис. 83. Блок-схема условия. Задание № 30. 8.2.5. Определение стипендии студента Задание № 31. 8.2.6. Определение стипендиального фонда Суммируемый интервал 8.2.7. Вычисление процентных отношений 8.2.8. Вычисление среднего балла по предметам и определение максимального из них Макс: =макс Поискпоз ( Искомое значение ... Полное содержание

Подобный материал:

• Применение Microsoft Excel для обработки табличных данных. Выполнение расчетов в таблицах, 14.68kb.
• Пособие для начинающих Воробьев В. В. Microsoft Excel, 439.68kb.
• Курс лекций по дисциплине " основы компьютерных технологий" Часть II. Microsoft Excel, 457.54kb.
• Методика оформление учебно-методической документации учителем географии в текстовом, 644.79kb.
• Лабораторная работа №4 Тема: Панели Microsoft Excel, 44.05kb.
• Пособие для начинающих Воробьев В. В. Microsoft Excel 2000, 451.03kb.
• Назначение программы Microsoft Excel (или просто Excel ) и создание и обработка электронных, 184.32kb.
• Программы Microsoft Excel. После загрузки программы на экране монитора можно увидеть, 77.36kb.
• Основы работы с электронными таблицами в Microsoft Excel, 40.42kb.
• Лабораторна робота №9: "Табличний процесор Microsoft Excel", 130.45kb.

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

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

П
Рис. 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 есть функция ЕСЛИ. Данная функция возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА (условие выполняется), и другое значение, если ЛОЖЬ (условие не выполняется). Функция ЕСЛИ используется для условной проверки значений и формул.

Примеры.

1. В следующем примере, если значение ячейки A10 равно 100, то условие выполняется, т. е. имеет значение ИСТИНА и вычисляется сумма для ячеек B5:B15. В противном случае условие не выполняется, т. е. имеет значение ЛОЖЬ и возвращается пустой текст (""), очищающий ячейку, которая содержит функцию ЕСЛИ.
   

=ЕСЛИ(A10=100;СУММ(B5:B15);"")

2. Предположим, что рабочий лист по расходам содержит в ячейках B2:B4 фактические расходы за январь, февраль, март: 1500, 500 и 500 соответственно. Ячейки C2:C4 содержат данные по предполагаемым расходам за те же периоды: 900, 900 и 925. Можно написать формулу для проверки соответствия бюджету расходов определенного месяца, генерируя тексты сообщений с помощью следующих формул:
   

=ЕСЛИ(B2>C2;"Превышение бюджета";"")

условие выполняется, значение в ячейке с условием равняется "Превышение бюджета";

=ЕСЛИ(B3>C3;"Превышение бюджета";"")

условие не выполняется, значение в ячейке с условием равняется "".

Замечание. До семи функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов.

В нашем случае для диагностирования двух категорий студентов необходимо проверить количество двоек (если оно не равно нулю, то студент двоечник) и количество пятерок (если оно равно количеству предметов, т. е. 12, то студент отличник). В ячейке AS17 записана соответствующая формула:

=ЕСЛИ(AO17<>0;"двоечник";ЕСЛИ(AR17=12;"отличник";""))

Обратим внимание на то, что второе условие ЕСЛИ проверяется в том случае, когда первое условие не выполняется, т. е. имеет значение ЛОЖЬ. Если продолжить написание условий, то, поставив условие в качестве третьего аргумента вложенной функции ЕСЛИ, можно определить и троечников и хорошистов:

=ЕСЛИ(AO17<>0;"двоечник";

ЕСЛИ(AR17=12;"отличник";

ЕСЛИ(AP17<>0;"троечник","хорошист"))).

Замечание.

1. Необходимо строго следить за числом скобок в формулах: число открытых скобок должно равняться числу закрытых.
   
2. Рекомендуется рисовать блок-схему условий на бумаге для более эффективной записи условий и самоконтроля.
   

Для нашего последнего условия блок-схема имеет вид (рис. 83).



Рис. 83. Блок-схема условия.

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

Задание № 30. Замените в таблице формулу в столбце AS, так чтобы она определяла все категории студентов (двоечник, троечник, хорошист и отличник). Формула, приведенная в таблице, определяет только две категории: двоечник и отличник.

8.2.5. Определение стипендии студента

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

Особенность, которая характерная для многих задач заключается в следующем: если Вы в качестве аргумента функции ЕСЛИ укажете число (реальный размер стипендии), то при изменении этого числа Вам придется переписать формулу и снова ее скопировать во все ячейки, где использовалось это число. Для того чтобы этого не происходило, указывайте не числа, а ссылки на ячейки, где эти числа хранятся, тогда при изменении значения этих ячеек изменятся и значения формул. Конечно, ссылки должны быть абсолютными.

Напоминание. Абсолютной ссылкой на ячейку называют ссылку на данную ячейку независимо от положения формулы,

например, $A$10, A$5, $B12.

В столбце AT записана следующая формула напротив каждого студента:

=ЕСЛИ(AS17="двоечник";"";ЕСЛИ(AS17="отличник";$AK$10;$AT$10))

В ячейках AK10 и AT10 хранятся суммы стипендий для отличников и для троечников-хорошистов соответственно. Изменив значения этих ячеек, Вы увидите, как изменятся значения стипендий в ячейках столбца AT.

Задание № 31. Замените в таблице формулу в столбце AT, так чтобы она определяла стипендии для всех категорий студентов (двоечников – без стипендии, троечников – минимальная, хорошистов – повышенная и отличников – именная). Формула, приведенная в таблице, определяет только две стипендии – для двоечников и отличников.

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

8.2.6. Определение стипендиального фонда

Для определения общего стипендиального фонда необходимо сложить все значения стипендий в диапазоне AT17:AT316. Для определения стипендиального фонда отличников необходимо сложить стипендии отличников. Это можно осуществить с помощью функции условного суммирования СУММЕСЛИ. У этой функции три параметра: интервал; критерий; суммируемый интервал.

Интервал – это интервал вычисляемых ячеек (для них проверяется критерий).

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

Суммируемый интервал – диапазон, в котором производится суммирование. Если не задан, то суммируются ячейки определенные параметром "интервал". Т. е. суммироваться одни ячейки, а проверяться на удовлетворение параметра "критерий" другие.

Формула вида: =СУММЕСЛИ($AT$17:$AT$316;$AT$10) суммирует ячейки, находящиеся в столбце AT, при условии, что они равны (по значению) ячейке AT10, т. е. в нашем случае размеру стипендии троечника-хорошиста. Абсолютные ссылки не позволяют измениться формуле при копировании.

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

8.2.7. Вычисление процентных отношений

Вычисление процентного отношения Вам знакомо еще со школы и каких-либо трудностей не представляет. Формулы, вычисляющие процент, просты и Вы сможете изучить их реализацию в Excel самостоятельно. Единственной особенностью является то, что умножение на 100% в Excel не нужно. Достаточно указать процентный формат ячеек. Это можно выполнить через меню «Формат» пункт «Ячейки», вкладку «Число». Либо вызвав контекстное меню правой кнопкой мыши и командой «Формат ячеек», либо через панель инструментов «Форматирование» нажав на кнопку «Процентный формат» .

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

8.2.8. Вычисление среднего балла

по предметам и определение максимального из них

Средний балл по предметам находится суммированием всех оценок в соответствующем столбце и делением их на число студентов. Формула, определяющая средний балл по математике (первому предмету), имеет вид: =СУММ(AB$16:AB$317)/ЧСТРОК($AB$17:$AB$316).

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

Для определения максимального балла из средних по предметам можно использовать функцию МАКС: =МАКС(AW37:AW48).

Диапазон ячеек с AW37 по AW48 содержит средние баллы по предметам. Определение же названия предмета немного сложнее.

="П"&ТЕКСТ(ПОИСКПОЗ(МАКС(AW37:AW48);AW37:AW48;0);"0")

Функция ПОИСКПОЗ возвращает относительное положение элемента массива, который соответствует заданному значению указанным образом. С помощью данной функции можно найти положение максимального среднего балла в диапазоне предметов. Функция записывается следующим образом: ПОИСКПОЗ (искомое значение; просматриваемый массив; тип сопоставления).

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

Просматриваемый массив – это непрерывный интервал ячеек, возможно, содержащих искомые значения. Параметр "просматриваемый массив" может быть массивом или ссылкой на массив.

Тип сопоставления – это число –1, 0 или 1. Тип сопоставления указывает, как Microsoft Excel сопоставляет параметр "искомое значение" со значениями в аргументе "просматриваемый массив".

• Если параметр "тип сопоставления" равен 1, то функция ПОИСКПОЗ находит наибольшее значение, которое равно или меньше, чем параметр искомое значение. Просматриваемый массив должен быть упорядочен по возрастанию: ..., –2, –1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА.
  
• Если параметр "тип сопоставления" равен 0, то функция ПОИСКПОЗ находит первое значение, которое в точности равно аргументу искомое значение. Просматриваемый массив может быть в любом порядке.
  
• Если параметр "тип сопоставления" равен –1, то функция ПОИСКПОЗ находит наименьшее значение, которое равно и больше чем искомое значение. Просматриваемый массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z-A, ..., 2, 1, 0, –1, –2, ..., и так далее.
  
• Если параметр "тип сопоставления" опущен, то предполагается, что он равен 1.
  

Замечания.

1. ПОИСКПОЗ возвращает позицию соответствующего значения в аргументе "просматриваемый массив", а не само значение. Например: ПОИСКПОЗ("б";{"а";"б";"в"};0) возвращает 2 – относительную позицию буквы "б" в массиве {"а";"б";"в"}.
   
2. ПОИСКПОЗ не различает регистры при сопоставлении текстов.
   
3. Если функция ПОИСКПОЗ не находит соответствующего значения, то возвращается значение ошибки #Н/Д.
   
4. Если параметр "тип сопоставления" равен 0 и параметр "искомое значение" является текстом, то параметр "искомое значение" может содержать символы шаблона, звездочки (*) и знака вопроса (?). Звездочка соответствует любой последовательности символов, знак вопроса соответствует любому одиночному символу.
   

Итак, в выражении: ="П"&ТЕКСТ(ПОИСКПОЗ(МАКС(AW37:AW48);

AW37:AW48;0);"0") функция ПОИСКПОЗ ищет положение максимального элемента (находит первый элемент равный максимальному значению – последний параметр у функции равен нулю) среди ячеек, содержащих средний балл по предметам.

Замечание. В данном случае в Excel задается строгое условие, поэтому он будет находить первый совпадающий элемент. Чтобы найти несколько предметов имеющих максимальный и одновременно одинаковый средний балл можно воспользоваться средствами Visual Basic.

Функция ТЕКСТ переводит число в текстовую строку. Это необходимо сделать, так как чтобы получить название (условное) предмета мы соединяем букву "П" с номером предмета, имеющим наивысший средний балл. У функции ТЕКСТ два параметра, первый – число, переводимое в текст, второй – формат числа для перевода. В нашем случае второй параметр "0" – это означает, что число будет целым при переводе (без дробных знаков).

8.2.9. Выделение категории студентов

с помощью условного форматирования

Для выделения результатов вычисления или других значений можно использовать условные форматы (формат (например, узор ячейки или цвет шрифта), который Microsoft Excel автоматически применяет к ячейке, если выполняется указанное условие). Например, пусть ячейка содержит формулу, вычисляющую разницу между прогнозируемыми и реальными продажами. Если реальные продажи превышают прогнозируемые, Microsoft Excel может выделить ячейку зеленым цветом, а в обратном случае – красным. Если значение ячейки изменилось и больше не соответствует необходимым свойствам, Microsoft Excel временно скрывает форматы, выделяющие это свойство. Условные форматы остаются до тех пор, пока их не удалят, даже если ни одно из свойств не соответствует данным, и определенные форматы ячеек не выводятся на экран.

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


Шаг 1. Выделение ячеек, формат которых необходимо изменить.

Выделите всю таблицу с фамилиями, оценками, средним баллом и стипендией. Вы можете это выполнить по-разному, либо мышью, либо клавишами Shift, стрелками и PageDown.

Напоминание. Быстрое выделение осуществляется так: встаньте на ячейку с фамилией "С1". Нажмите клавиши Shift и Ctrl. Удерживая их, нажмите клавиши «стрелка вправо», «стрелка вниз» (в любом порядке). Диапазон выделен. Клавиша Shift производит выделение (со стрелками или PageUp, PageDown), а клавиша Ctrl быстрое перемещение к следующему пустому пространству (с теми же клавишами).

Шаг 2. Выбор в меню «Формат» команды «Условное форматирование».

Шаг 3. Чтобы в качестве условия форматирования использовать значения выделенных ячеек, выберите параметр "значение", выберите операцию сравнения, а затем в необходимое поле введите значение. Ввести можно заданное значение или формулу, но перед формулой необходимо поставить знак равенства (=).

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

Формулы в качестве критерия форматирования

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

Чтобы при выбранном параметре "значение" сравнить значения выделенных ячеек с результатом формулы, формула должна начинаться со знака равенства. Вычисления по данной формуле могут производиться только на активном листе. Чтобы оценить данные на другом листе или в другой книге, на активном листе необходимо определить имя для данных на другом листе или в другой книге либо ввести ссылку на данные в ячейке активного листа, затем сослаться на указанную ячейку или имя в формуле. Ссылки на ячейки можно ввести в формулу, выделив ячейки непосредственно на листе. Выделение ячеек на листе вводит абсолютные ссылки. Если необходимо, чтобы были определены ссылки для каждой ячейки в выделенном диапазоне, используйте относительные ссылки. В нашем случае необходимо выбрать параметр "формула" (первый выпадающий список), затем ввести следующую формулу:

=И($AS17="двоечник").

Логическая формула И указывает на то, что последующая формула (или выражение) должна выполняться с формулами, записанными в ячейках в выделенном диапазоне. Формула заключается в простом сравнении значений ячеек, стоящих в столбце AS со значением "двоечник". Абсолютная адресация по столбцу необходима для того, чтобы Excel применял эту формулу только для ячеек содержащих категорию студентов, а не для всех выделенных ячеек. Относительная адресация по строкам позволяет для каждой строки применять свою формулу. Такая формула позволит выделить строки в таблице, которые удовлетворяют этой формуле. Можно форматировать по значению, но тогда форматирование будет действовать только на столбец, где находятся категории студентов, а не на всю строку, содержащую данную категорию.

Шаг 4. Нажмите на кнопку «Формат» в появившемся окне.

Шаг 5. Выберите шрифт, его цвет, подчеркивание, рамку и затенение ячеек или шаблоны. Выбранные форматы будут применены, только если значение ячейки отвечает поставленному условию или если формула принимает значение ИСТИНА.

Шаг 6. Для добавления условия выберите команду «Добавить», а затем повторите шаги 3–5.

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

Задание № 32. Выделите в таблице все строчки с двоечниками красным жирным шрифтом.

8.2.10. Построение диаграммы

Разберем еще раз построение диаграмм и графиков. При отображении данных на диаграммах Microsoft Excel выводит значения ячеек в качестве столбцов, линий, сегментов и других элементов диаграмм. При создании диаграммы значения из листа автоматически представляются в диаграммах. Например, в гистограммах мастер диаграмм для каждого значения из листа создает столбец определенной высоты. Диаграммы связаны с данными листа, из которого они были созданы. При изменении данных на листе изменяются также и диаграммы.

Для начала выделите диапазон с AV17 по AW20. Затем, либо через меню «Вставка» выберите команду «Диаграмма…», либо нажмите на панели инструментов «Стандартная» кнопку «Мастер диаграмм» . Вызванный мастер диаграмм предложит Вам проделать четыре шага.

Шаг 1. Выбор типа диаграммы.

Выберите в появившемся окне тип диаграммы. Диаграммы бывают двух классов: стандартные и нестандартные. Смена одного класса другим производится выбором необходимой закладки. Чтобы посмотреть предварительный вид стандартной диаграммы выберите необходимый тип и нажмите кнопку «Просмотр результата». Для решения нашей задачи подойдет любая круговая диаграмма (хотя, может быть, Вы найдете лучший тип для представления наших данных). В примере был выбран нестандартный тип: вырезанные сектора. Для перехода к следующему шагу нажмите кнопку «Далее >». Для отмены вставки диаграммы кнопку «Отмена». Для построения диаграммы "как есть" кнопку «Готово».

Шаг 2. Источник данных диаграммы.

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

Шаг 3. Параметры диаграммы.

В качестве параметров диаграммы можно указать названия осей (если они имеются), название графика – на вкладке «Заголовки». На вкладке «Легенда» можно указать расположение легенды (условные обозначения) значений. На вкладке «Подписи данных» можно вывести на график значения возле точек графика, подписи значений.

Шаг 4. Размещение диаграммы.

Указывается положение диаграммы в книге Excel. Либо на отдельном листе, либо на листе с данными.

8.2.11. Создание программного кода

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

Последовательность операций для создания программы

Вызывается панель инструментов «Visual Basic» (из контекстного меню, вызываемого правой кнопкой мыши на области панелей инструментов или через меню «Вид» и подменю «Панели инструментов»).

На панели инструментов следует нажать кнопку « Элементы управления» . Выбрать элемент управления «Кнопка» и нарисовать его на свободном месте. Далее вызвать правой кнопкой мыши контекстное меню на элементе управления. В контекстном меню выбрать команду «Свойства». После этого в появившемся окне свойств изменить необходимые свойства. Например, свойство Caption – меняет название кнопки. Название кнопки и ее размеры можно изменить и другой командой контекстного меню «Объект кнопка – Изменить». При необходимости можете поменять цвет, шрифт кнопки, добавить тень (свойство Shadow в окне свойств) и т. д. Далее двойным нажатием левой кнопки мыши на объекте или через контекстное меню командой «Исходный текст» можно войти в редактор Visual Basic. В редакторе Вы увидите следующие операторы:

Private Sub CommandButton1_Click();

End Sub.

Операторы Sub и End Sub на начало и конец процедуры или подпрограммы.

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

Существуют процедуры типа Function, Property и Sub. Имя процедуры всегда определяется на уровне модуля. В процедурах должны содержаться все исполняемые программы. Вложенность процедур в другие процедуры не допускается.

Инструкция – синтаксически завершенная конструкция, представляющая отдельное действие, описание или определение. В качестве инструкций используются операторы, функции, служебные слова.

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

Пример.

= – инструкция (оператор присваивания);

Sin – инструкция (функция синуса);

Const – инструкция (служебное слово).

Константа – именованный элемент, сохраняющий постоянное значение в течение выполнения программ.

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

Пример.

10 – константа (любое число является константой);

"ad" – константа (любое выражение в кавычках является константой).

Процедура Sub представляет собой последовательность инструкций языка Visual Basic, ограниченных инструкциями Sub и End Sub, которая выполняет действия, но не возвращает значение. Процедура Sub может получать аргументы, как, например, константы, переменные, или выражения, передаваемые ей вызывающей процедурой. Если процедура Sub не имеет аргументов, инструкция Sub должна содержать пустые скобки.

У нас имеется следующий текст:

Private Sub CommandButton1_Click();

End Sub.

В нашем случае перед оператором Sub стоит инструкция Private. Так же как и для переменных, эта инструкция означает, что данная процедура будет доступная в пределах одного модуля – в нашем случае это "Лист 1 (Сессия)". После оператора Sub идет имя процедуры. Данное имя Excel дает автоматически, так как основной процедурой на объект класса «Кнопка» является обработка события "нажатие мышью на кнопку" («Click»). Имя процедуры состоит из имени объекта – CommandButton1 и названия события – «Click». Процедура не получает параметров, поэтому после имени процедуры идут пустые скобки – (). Между этими двумя строчками необходимо написать текст программы, которая вызовет на экран окно со статистическими данными. Этот код заключается в одной строчке: UserForm1.Show

Инструкция Show является методом, который показывает на экран объект класса UserForm. Объекта пока не существует, поэтому создадим его.

Для создания объекта класса UserForm нажмите на панели инструментов «Стандарт» соответствующую кнопку, либо через меню « Вставка» командой «UserForm». Необходимо изменить заголовок этой формы (свойство Caption) на фразу "Статистика по студентам". Размеры самого окна подправляются в процессе создания других объектов. Свойство StartUpPosition должно быть установлено в 1 – CenterOwner, чтобы окно выводилось посредине листа. Окно свойств вызывается или клавишей F4 или через контекстное меню на объекте командой «Свойства» или через меню «Вид» командой «Окно свойств».

Далее создается объект класса «Рамка». Объект данного класса может содержать другие объекты. Необходимо изменить название рамки на "Количество оценок", это делается с помощью свойства Caption. Если шрифт не является русским, то измените его на русский шрифт с помощью свойства Font. Стандартным системным экранным шрифтом (которым пишется меню) является MS Sans Serif. В созданном объекте класса «Рамка» располагают объекты класса «Надпись», с текстом "число оценок всего", "число двоек", "число троек", "число четверок", "число пятерок". Изменение текста производится с помощью свойства Caption, затем текст выравнивают по правому краю свойством TextAlign, устанавливая его значение в 3 – fmTextAlignRight. Цвет надписей меняется свойством ForeColor. После создается еще один объект класса «Надпись» и изменяют ему свойство SpecialEffect на значение 2 – fmSpecialEffectSunken. Данный объект можно скопировать (так же как Вы копируете текст в Word). Нажимаете один раз на объекте левой кнопкой мыши (если он не выделен), затем клавиши Ctrl+Insert, а после Shift+Insert. Этот объект будет содержать значение, получаемое при расчете с помощью программы. При необходимости меняют цвет шрифта (свойство ForeColor) и фона (свойство BackColor).

Аналогично создаются заготовки в виде объектов класса «Рамка» для информации об успеваемости студентов и стипендиальном фонде. Для информации об успевающих студентах создается только рамка. В объекте класса «Рамка» с заголовком "Стипендия" присутствует три объекта класса «Счетчик». Значения свойств Max, Min, Value первого объекта установлены в 120, 60, 80 соответственно. Второго объекта в 180, 120, 120, а третьего в 320, 180, 250. Первое значение указывает потолок стипендии: минимальной – для первого объекта, повышенной – для второго и именной – для третьего объекта. Второе значение указывает минимум тех же стипендий. А третье значение указывает на текущий размер этих стипендий (должно лежать в пределах первого и второго). С помощью данных объектов пользователь сможет изменять размер стипендий и получать суммы стипендиального фонда. Кроме того, эти объекты имеют особенные свойства ControlTipText – пояснительный текст к объекту, который выводится на желтом фоне, когда указатель мыши находится некоторое время на объекте. Данное свойство изменено так же у объектов класса «Надпись» стоящих рядом с объектами класса «Счетчик». Внимательно посмотрите на эти свойства.

И, наконец, последний объект класса «Рамка» содержит объект класса «Список». Данный объект также имеет особенное свойство ControlTipText, которое поясняет назначение объекта.

После создания всех этих объектов приступают к написанию программы. Переход в режим написания программы осуществляется либо двойным нажатием левой кнопки мыши на объекте класса UserForm (но не другом объекте, который содержит UserForm), либо нажатием кнопки программа в окне проекта (вызывается либо через меню « Вид» командой «Окно проекта», либо клавишами Ctrl+R). Вызвать режим написания программы можно через контекстное меню (вызывается правой кнопкой мыши) командой «Программа» на объекте класса UserForm.

Появится окно модуля, вверху которого есть два выпадающих списка. Левый список содержит названия всех доступных интерфейсных объектов, а правый все доступные события для каждого из объектов.

Для написания программы, которая будет выполняться при появлении окна на экране (а нам нужна именно такая программа, когда мы нажимаем на кнопку, расположенную на листе, должно появляться окно с рассчитанными значениями) необходимо в левом списке выбрать объект «UserForm1», а в правом событие «Initialize», которое возникает при появлении окна на экране. Появится "заготовка" для процедуры:

Private Sub UserForm_Initialize()

End Sub

Далее нужно написать программу. Текст программы с комментариями приведен ниже (записаны после знака «'»). Все, что заключено в рамки в реальной программе отсутствует – это дополнительные объяснения.


Dim Obj As Object ' Вспомогательная переменная


Здесь объявляется новый для Вас тип переменной – Object. Переменная данного типа может содержать любой объект, который имеется в книге. В программе данная переменная будет содержать ячейку таблицы.


Dim i As Integer ' Вспомогательная переменная


Данная переменная будет использоваться в качестве цикловой переменной


Dim Count_Of_Note As Integer ' Число оценок

Dim Count2 As Integer ' Число двоек

Dim Count3 As Integer ' Число троек

Dim Count4 As Integer ' Число четверок

Dim Count5 As Integer ' Число пятерок


Объявляются переменные, которые будут содержать общее число оценок, число двоек, троек, четверок, пятерок.


' Выводим начальный размер минимальной стипендии

Label40.Caption = SpinButton1.Value

' Выводим начальный размер повышенной стипендии

Label41.Caption = SpinButton2.Value

' Выводим начальный размер именной стипендии

Label42.Caption = SpinButton3.Value

Выводим значения объектов класса «Счетчик» на экран. Данные значения будут содержать объекты класса «Надпись», расположенные рядом. Обратите внимание на то, как осуществляется доступ к свойствам объекта. Он производится через точку. Несмотря на то, что свойство Value является числом, а Caption текстом, преобразование типов данных не нужно. Откуда взять название объектов класса «Надпись» – из окна свойств, там необходимо посмотреть главное свойство любого объекта – Name.


' Обнуляем количество оценок

Count2 = 0: Count3 = 0: Count4 = 0: Count5 = 0

Count_Of_Note = 0

' Обнуляем количество студентов

Student2 = 0: Student3 = 0: Student4 = 0: Student5 = 0

' Просмотрим каждую ячейку в диапазоне двоек

For Each Obj In Worksheets("Сессия").Range("ao17:ao316")

Count2 = Count2 + Obj.Value ' Суммируем число двоек


' Считаем двоечников

If Obj.Value > 0 Then

Student2 = Student2 + 1

End If

Next Obj

Код участка программы можно выразить следующей блок-схемой (рис. 84).



Рис. 84. Блок-схема участка программы.

Здесь перебираются все ячейки в диапазоне от AO17 до AO316 на листе с названием "Сессия", что является диапазоном количества двоек у каждого студента. За перебор отвечает цикл For Each .. In … Next. Переменная Count2, содержащая количество двоек наращивается на величину Obj.Value, которая содержит значение ячейки (числовое или текстовое). Если попадется текст в этом диапазоне, то возникнет ошибка несоответствия типа (пытаемся числовой переменной прибавить текст). В этом же цикле удобно подсчитать количество двоечников. Если у ячейки значение Value не равно нулю, значит студент двоечник. Конечно, если в таблице окажется случайно число 123 (а предметов всего 12), то программа это не учтет. Для этого надо добавить в условие сравнение Obj.Value < 13. Если значение ячейки не равно нулю, то необходимо увеличить переменную Student2 (количество двоечников) на единицу. Кроме того, данный код зависит от формул на листе, так как не считает число двоек у каждого студента, а использует уже подсчитанные значения и суммирует их.

' Просмотрим каждую ячейку в диапазоне троек

For Each Obj In Worksheets("Сессия").Range("ap17:ap316")

Count3 = Count3 + Obj.Value ' Суммируем число троек

Next Obj

' Просмотрим каждую ячейку в диапазоне четверок

For Each Obj In Worksheets("Сессия").Range("aq17:aq316")

Count4 = Count4 + Obj.Value ' Суммируем число четверок

Next Obj

' Просмотрим каждую ячейку в диапазоне пятерок

For Each Obj In Worksheets("Сессия").Range("ar17:ar316")

Count5 = Count5 + Obj.Value ' Суммируем число пятерок

' Считаем отличников

If Obj.Value = 12 Then

Student5 = Student5 + 1

End If

Next Obj

Число отличников можно найти как сумму студентов имеющих число пятерок равное количеству предметов (в нашем случае 12). Число троечников определяется сложнее, так как у "потенциального" троечника (число троек не равно нулю) могут быть двойки. Число хорошистов тоже определяется сложнее по той же причине.

' Число оценок равно сумме 2,3,4,5

Count_Of_Note = Count2 + Count3 + Count4 + Count5

Label13.Caption = Count_Of_Note ' Выводим на экран число оценок

Label14.Caption = Count2 ' Выводим количество двоек

Label15.Caption = Count3 ' Выводим количество троек

Label16.Caption = Count4 ' Выводим количество четверок

Label17.Caption = Count5 ' Выводим количество пятерок

' Вычисление процентов

' Процент двоек

Label18.Caption = Format(Count2 / Count_Of_Note, "0.00%")

Функция Format осуществляет преобразование числа в определенный формат. В данном случае функция Format преобразует число к процентному формату (умножает на 100) с двумя знаками после запятой – "0.00%").

' Процент троек

Label19.Caption = Format(Count3 / Count_Of_Note, "0.00%")

' Процент четверок

Label20.Caption = Format(Count4 / Count_Of_Note, "0.00%")

' Процент пятерок

Label21.Caption = Format(Count5 / Count_Of_Note, "0.00%")

' Просмотрим каждую ячейку в диапазоне оценок

For i = 17 To 316

' Считаем троечников

If Worksheets("Сессия").Range("ao" + Trim(Str(i))) = 0 And _

Worksheets("Сессия").Range("ap" + Trim(Str(i))) > 0 Then

Student3 = Student3 + 1

End If

' Считаем хорошистов

If Worksheets("Сессия").Range("ao" + Trim(Str(i))) = 0 And _

Worksheets("Сессия").Range("ap" + Trim(Str(i))) = 0 And _

Worksheets("Сессия").Range("aq" + Trim(Str(i))) > 0 Then

Student4 = Student4 + 1

End If

' Заносим успевающих студентов в список

If Worksheets("Сессия").Range("ao" + Trim(Str(i))) = 0 Then

ListBox1.AddItem (Worksheets("Сессия"). Range("aa" +

+ Trim(Str(i))). Value & " – " & _

Worksheets("Сессия"). Range("an" + Trim(Str(i))).Value)

End If

Next i


Знак подчеркивания ( _ ) означает, что строка программы продолжается на следующей физической строке. Подсчет троечников осуществляется так: если у студента количество двоек равно 0 и количество троек больше нуля, то он – троечник. Аналогично делается вывод по хорошистам. Интересным является способ создания списка успевающих студентов. Объект класса «Список» имеет метод AddItem. Данный метод добавляет одну строку в список. Строка должна представлять собой текст. В нашем случае текст составляется из фамилии (берется значение ячейки из столбца AA, и добавляется к нему через знак минуса (с помощью знаков &, которые соединяют текстовые строки) значение среднего балла (столбец AN)). Обратите внимание на формирование адреса ячейки: "aa" + Trim(Str(i)). Функция Str переводит числовое значение в строку текста (например, число 234 переводит в "234"), а функция Trim обрезает пробелы в начале и в конце строковой переменной (например, строку " 34е " преобразует в "34е"). Итак, поскольку адрес ячейки представляет собой текст, состоящий из названия столбца и номера строки, то для его формирования мы к названию столбца – AA (мы его знаем заранее) нужно добавить в виде текста номер строки (номера меняются в теле цикла). Поскольку у нас имеется номер строки в виде числа (значение переменной i), то нужно преобразовать его в текст. Функция Str переводит число в текст, однако, она добавляет в начале пробел (если число положительное) или минус (если число отрицательное). У нас число положительное, но пробел в имени ячейки недопустим, поэтому используется функция Trim.

' Всего студентов – вычисляется как сумма студентов всех категорий

Students = Student2 + Student3 + Student4 + Student5

Label22.Caption = Students ' Выводим на экран число студентов

Label26.Caption = Student2 ' Выводим количество двоечников

Label25.Caption = Student3 ' Выводим количество троечников

Label24.Caption = Student4 ' Выводим количество хорошистов

Label23.Caption = Student5 ' Выводим количество отличников

' Выводим на экран процент

Label30.Caption = Format(Student2 / Students, "0.00%")

Label29.Caption = Format(Student3 / Students, "0.00%")

Label28.Caption = Format(Student4 / Students, "0.00%")

Label27.Caption = Format(Student5 / Students, "0.00%")

' Подсчитываем суммы стипендий

Label37.Caption = Student3 * SpinButton1.Value ' Минимальной

Label38.Caption = Student4 * SpinButton2.Value ' Повышенной

Label39.Caption = Student5 * SpinButton3.Value ' Именной

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

' Стипендиальный фонд

Label32.Caption = Student5 * SpinButton3.Value + Student4 * SpinButton2.Value + Student3 * SpinButton1.Value

Выход обратно из модуля к виду объекта класса UserForm можно выполнить, нажав мышью кнопку «Объект» в окне проекта, либо дважды нажав мышью на самом объекте UserForm.

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

Private Sub SpinButton1_Change()

End Sub

Данная процедура будет обрабатывать событие «Change», которое возникает при изменении значения счетчика пользователем (во время работы программы). Необходимо написать следующий код в "заготовке":

' Выводим измененный размер минимальной стипендии

Label40.Caption = SpinButton1.Value

' Сумма стипендий

Label37.Caption = Student3 * SpinButton1.Value ' Минимальной

' Стипендиальный фонд

Label32.Caption = Student5 * SpinButton3.

Value + Student4 * SpinButton2.

Value + Student3 * SpinButton1.Value

Данный код позволит обновить значения (текст) трех объектов, которые содержат: размер минимальной стипендии; стипендиальный фонд для троечников; стипендиальный фонд всех стипендий.

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

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

Осталось самое малое – запустить программу. Нажмите кнопку F5 или кнопку «Запуск программы/UserForm» на панели инструментов. Через небольшое время Вы увидите результат. Можно закрыть редактор Visual Basic (клавиши Alt+Q или через меню «Файл» командой «Закрыть и вернуться в Microsoft Excel»). Теперь, при каждом нажатии на созданную нами кнопку будет выполняться наша программа, и появляться наше окно.

Если необходимо изменить свойства кнопки, то нужно войти в режим конструктора, нажав кнопку « Режим конструктора» на панели инструментов «Visual Basic”. Выход осуществляется также.

Задание № 33. Внимательно изучите весь программный код. Попробуйте сами себе объяснить назначение каждой строчки программы. Изучите свойства объекта. Мысленно повторите процедуру создания программы. Если программа в Вашей заготовке отсутствует, то создайте ее.