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

При наведении курсора мыши на ячейку с примечанием 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