Практическое занятие 7

Название	Практическое занятие 7
Дата конвертации	14.09.2012
Размер	73.07 Kb.
Тип	Практикум

Практикум по Excel. Занятие 7

Практическое занятие 7.

Обработка и условное форматирование ведомости.

Цель работы:

Закрепление навыков вычислений в Excel.
Освоение условного форматирования

1. Необходимые сведения.

1.1. Некоторые стандартные функции Excel:

=СУММ(аргум1; аргум2;…) – возвращает сумму аргументов;

= СУММЕСЛИ(диапазон;критерий;диапазон_суммирования) – суммирует ячейки, заданные критерием, аргументы:

Диапазон    — диапазон вычисляемых ячеек.

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

Диапазон_суммирования    — фактические ячейки для суммирования.

Ячейки из «диапазон_суммирования» суммируются, только если соответствующие им ячейки в аргументе «диапазон» удовлетворяют критерию.
Если «диапазон_суммирования» опущен, то суммируются ячейки в аргументе «диапазон»;

=ЕСЛИ(логическое выражение, значение1 для результата «Истина», значение2 для результата «Ложь») – в зависимости от результата вычисления логического выражения возвращает первое или второе значение. Примеры логических выражений:

$C$12 > $D$5 – сравниваются значения двух ячеек;

$F$15 = “Иванов” – значение ячейки сравнивается с текстом.

=И(логическое_значение1; логическое_значение2; ...) - возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

=ИЛИ(логическое_значение1;логическое_значение2; ...) - возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

В формулах Если, И, Или

Логическое_значение1, логическое_значение2,... — от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

=СРЗНАЧ(число1; число2; ...) - возвращает среднее (арифметическое) своих аргументов.

^ Число1, число2, ... — это от 1 до 30 аргументов, для которых вычисляется среднее.

Аргументы должны быть либо числами, либо именами, массивами или ссылками, содержащими числа.
Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.

=СЧЁТ(значение1; значение2; ...
) — подсчитывает количество чисел в списке аргументов. Функция СЧЁТ используется для получения количества числовых ячеек в интервалах или массивах ячеек.

^ Значение1, значение2, ... — это от 1 до 30 аргументов, которые могут содержать или ссылаться на данные различных типов, но в подсчете участвуют только числа.

Учитываются аргументы, которые являются числами, пустыми значениями, логическими значениями, датами, или текстами, изображающими числа; аргументы, которые являются значениями ошибки или текстами, которые нельзя интерпретировать как числа, игнорируются.
Если аргумент является массивом или ссылкой, то подсчитываются только числа в этом массиве или ссылке. Пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются.

=СЧЁТЗ(значение1; значение2; ...) — подсчитывает количество непустых значений в списке аргументов. Функция СЧЁТЗ используется для подсчета количества ячеек с данными в интервале или массиве

^ Значение1, значение2, ...   - это от 1 до 30 аргументов, количество которых требуется сосчитать. В данном случае значением считается значение любого типа, включая пустую строку (""), но не включая пустые ячейки. Если аргументом является массив или ссылка, то пустые ячейки в массиве или ссылке игнорируются.

=СЧЁТЕСЛИ(диапазон; критерий) — подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.

Диапазон   — это диапазон, в котором нужно подсчитать ячейки.

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

=МИН(число1;число2; ...) — возвращает наименьшее значение в списке аргументов.

^ Число1, число2, ...  — это от 1 до 30 чисел, среди которых ищется минимальное значение.

=МАКС(число1;число2; ...) — возвращает наибольшее значение в списке аргументов.

Число1, число2, ...  — это от 1 до 30 чисел, среди которых ищется минимальное значение.

^ Функции проверки типа - девять функций рабочего листа, которые используются для проверки типа значения или ссылки.

Каждая из этих функций проверяет тип значения и возвращает значение ИСТИНА или ЛОЖЬ в зависимости от типа.

Например, функция ЕПУСТО возвращает логическое значение ИСТИНА, если проверяемое значение является ссылкой на пустую ячейку; в противном случае возвращается логическое значение ЛОЖЬ.

ЕПУСТО(значение); ЕОШ(значение); ЕОШИБКА(значение); ЕЛОГИЧ(значение);
ЕНД(значение); ЕНЕТЕКСТ(значение); ЕЧИСЛО(значение); ЕССЫЛКА(значение);
ЕТЕКСТ(значение)

Значение    — это проверяемое значение. Значение может быть пустой ячейкой, значением ошибки, логическим значением, текстом, числом, ссылкой или именем объекта любого из перечисленных типов.

Функция	Возвращает ИСТИНА, если
ЕЛОГИЧ	Значение ссылается на логическое значение.
ЕНД	Значение ссылается на значение ошибки #Н/Д (значение недоступно).
ЕНЕТЕКСТ	Значение ссылается на любой элемент, который не является текстом. (Заметим, что функция возвращает ИСТИНА, если значение ссылается на пустую ячейку).
ЕОШ	Значение ссылается на любое значение ошибки, кроме #Н/Д.
ЕОШИБКА	Значение ссылается на любое значение ошибки (#Н/Д, #ЗНАЧ! , #ССЫЛ! , #ДЕЛ/0! , #ЧИСЛО! , #ИМЯ? или #ПУСТО!).
ЕПУСТО	Значение ссылается на пустую ячейку.
ЕССЫЛКА	Значение ссылается на ссылку.
ЕТЕКСТ	Значение ссылается на текст.
ЕЧИСЛО	Значение ссылается на число.

1.2. Условное форматирование

1.2.1. Команда Формат - Условное форматирование позволяет применить различные форматы к ячейкам, в зависимости от значений в ячейках. После выделения ячеек и выбора команды будет показан диалог условного форматирования (Рис.7.1)

Рис. 7.1. Диалог условного форматирования

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

Кнопка «А также >>» позволяет задать три подобных условия.

2. Задание.

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

2.1. Выполнить следующее условное форматирование: если экзамены сданы без троек, соответствующая строка таблицы должна подсвечиваться зеленым цветом, если у студента остались задолженности — красным.

2.2. Подсчитать:

средний балл каждого студента;
средний балл группы;
количество и процентную долю каждой отметки

3. Указания к выполнению работы.

3.1. Возможный вид ведомости (после форматирования и подсчета итогов):

Рис. 7.2. Экзаменационная ведомость, пример

3.2. Условия форматирования для данной задачи следует задать формулой.

3.2. Формула для выделения студентов, не имеющих задолженностей и троек, может, например, иметь следующий вид:

=МИН($C4:$G4)>3,
где C4:G4 – диапазон ячеек с оценками одного студента

Замечания:

Формула приведена для первой строки данных примера, показанного на рис. 7.2.
Обратите внимание на способ адресации диапазона данных в формуле.
Это не единственная возможная формула.

3.3. Выделить данные в первой строке ведомости и задать для этой строки условное форматирование для выделения хорошо успевающих студентов.

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

3.5. Вновь выделить первую строку и вызвать диалог условного форматирования.

3.6. С помощью кнопки «А также >>» задать второе условие форматирование – выделение неуспевающих студентов, протестировать форматирование.

3.7. Выделить диапазон с условным форматированием, скопировать его.

3.8. Выделить в ведомости диапазон ячеек с данными и скопировать в него формат первой строки (Правка – Специальная вставка – Форматы – ОК).

3.9. Выполнить указанные в п.2.2. расчеты.

3.10. Сохранить файл и предъявить результаты преподавателю.

^

4. Дополнительное задание.

4.1. Скопировать ведомость на другой рабочий лист.

4.2. Изменить условное форматирование таким образом, чтобы красная подсветка включалась так же и в случае неявки студента на экзамен (вместо отметки в ведомости проставлен символ «н»).

^ 5. Материал к следующему занятию

5.1. Ссылки на данные, расположенные на разных рабочих листах (расширенный формат адреса).

5.2. Стандартные (встроенные) функции: ОКРУГЛ, ЕСЛИ, НЕ, ЕПУСТО

[I – 357-362; 401-409; Справочная система Excel]

^

6. Дополнение. Интерфейс программы Excel – 2007.

6.1. Условное форматирование настраивается пиктограммой Условное форматирование из группы ^ Стили на вкладке Главная. В программе Excel -2007 появились новые возможности условного форматирования:
- можно выделять ячейки с помощью градиента – цвет плавно меняется от ячеек с максимальным значением до минимальных;

- для выделения ячеек с интересующими значениями можно использовать значки, например, светофор – от красной двойки до зеленой пятерки;
- упростился диалог, к котором задаются условия форматирования.

7. Библиографический список

Безручко В. Т. Практикум по курсу «Информатика». Учеб. пособие. – М.: Финансы и статистика, 2003
Безручко В. Т. Компьютерный практикум по курсу «Информатика». Учеб. Пособие. – М.:ИД «Форум» - ИНФРА-М, 2008

www.alural.narod.ru/inform/intro.htm Александр Ю. Алексеев

	Изготовление ватно-марлевой повязки Практическое занятие по обж		Практическое занятие 9 Освоение способов создания, оформления таблиц и выполнения вычислений в таблицах
	Практическое занятие 5 Получение практических навыков по настройке программы и созданию и редактированию текстовых документов		Практическое занятие 8 Освоение способов интеграции объектов: вставка в текстовый документ рисунков, созданных в других приложениях
	Практическое занятие 12 Для создания программ на vba в Excel формы и элементы управления используется так же, как и в Word		Практическое занятие 11 Цель работы: изучение возможностей языка программирования Visual Basic for Application для создания функций пользователя
	Практическое занятие 10 Слияние документов – операция, позволяющая включить в основной документ информацию из другого источника (базы данных, таблицы)		Практическое занятие 11 Создайте новый документ, введите текст "Создание формул" и сохраните документ в своей папке под именем Формула doc
	Практическое занятие 6 Откройте созданный на предыдущем занятии документ Tekst doc и перейдите в начало документа, нажав комбинацию клавиш Ctrl+Home. Выведите...		Практическое занятие «Нестандартные методы решения неравенств. Метод замены функций» Решение некоторых логарифмических неравенств основано на переходе к новому, не зависящему от переменной, основанию

Практическое занятие 7