|
Практикум по Excel. Занятие 7 Практическое занятие 7.Обработка и условное форматирование ведомости. Цель работы:
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 до 30 аргументов, для которых вычисляется среднее.
=СЧЁТ(значение1; значение2; ...) — подсчитывает количество чисел в списке аргументов. Функция СЧЁТ используется для получения количества числовых ячеек в интервалах или массивах ячеек. ^ — это от 1 до 30 аргументов, которые могут содержать или ссылаться на данные различных типов, но в подсчете участвуют только числа.
=СЧЁТЗ(значение1; значение2; ...) — подсчитывает количество непустых значений в списке аргументов. Функция СЧЁТЗ используется для подсчета количества ячеек с данными в интервале или массиве ^ - это от 1 до 30 аргументов, количество которых требуется сосчитать. В данном случае значением считается значение любого типа, включая пустую строку (""), но не включая пустые ячейки. Если аргументом является массив или ссылка, то пустые ячейки в массиве или ссылке игнорируются. =СЧЁТЕСЛИ(диапазон; критерий) — подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию. Диапазон — это диапазон, в котором нужно подсчитать ячейки. Критерий — это критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки". =МИН(число1;число2; ...) — возвращает наименьшее значение в списке аргументов. ^ — это от 1 до 30 чисел, среди которых ищется минимальное значение. =МАКС(число1;число2; ...) — возвращает наибольшее значение в списке аргументов. Число1, число2, ... — это от 1 до 30 чисел, среди которых ищется минимальное значение. ^ - девять функций рабочего листа, которые используются для проверки типа значения или ссылки. Каждая из этих функций проверяет тип значения и возвращает значение ИСТИНА или ЛОЖЬ в зависимости от типа. Например, функция ЕПУСТО возвращает логическое значение ИСТИНА, если проверяемое значение является ссылкой на пустую ячейку; в противном случае возвращается логическое значение ЛОЖЬ. ЕПУСТО(значение); ЕОШ(значение); ЕОШИБКА(значение); ЕЛОГИЧ(значение); ЕНД(значение); ЕНЕТЕКСТ(значение); ЕЧИСЛО(значение); ЕССЫЛКА(значение); ЕТЕКСТ(значение) Значение — это проверяемое значение. Значение может быть пустой ячейкой, значением ошибки, логическим значением, текстом, числом, ссылкой или именем объекта любого из перечисленных типов.
1.2. Условное форматирование 1.2.1. Команда Формат - Условное форматирование позволяет применить различные форматы к ячейкам, в зависимости от значений в ячейках. После выделения ячеек и выбора команды будет показан диалог условного форматирования (Рис.7.1) ![]() Рис. 7.1. Диалог условного форматирования В этом диалоге нужно установить значение или формулу, определяющие условие применения формата, и выбрать формат, который будет действовать при заданных значениях данных в ячейке или при выполнении условий, заданных формулой. Кнопка «А также >>» позволяет задать три подобных условия. 2. Задание. Рабочий лист содержит сводную ведомость студенческих оценок по итогам сессии (не менее шести студентов, не менее пяти экзаменов). Данные задать произвольно. 2.1. Выполнить следующее условное форматирование: если экзамены сданы без троек, соответствующая строка таблицы должна подсвечиваться зеленым цветом, если у студента остались задолженности — красным. 2.2. Подсчитать:
^ 3.1. Возможный вид ведомости (после форматирования и подсчета итогов): ![]() Рис. 7.2. Экзаменационная ведомость, пример 3.2. Условия форматирования для данной задачи следует задать формулой. 3.2. Формула для выделения студентов, не имеющих задолженностей и троек, может, например, иметь следующий вид: =МИН($C4:$G4)>3, где C4:G4 – диапазон ячеек с оценками одного студента Замечания:
3.3. Выделить данные в первой строке ведомости и задать для этой строки условное форматирование для выделения хорошо успевающих студентов. 3.4. Изменяя данные в первой строке, протестировать форматирование, убедиться, что при появлении в строке отметок 3 или 2 выделение исчезает. 3.5. Вновь выделить первую строку и вызвать диалог условного форматирования. 3.6. С помощью кнопки «А также >>» задать второе условие форматирование – выделение неуспевающих студентов, протестировать форматирование. 3.7. Выделить диапазон с условным форматированием, скопировать его. 3.8. Выделить в ведомости диапазон ячеек с данными и скопировать в него формат первой строки (Правка – Специальная вставка – Форматы – ОК). 3.9. Выполнить указанные в п.2.2. расчеты. 3.10. Сохранить файл и предъявить результаты преподавателю. ^ 4.1. Скопировать ведомость на другой рабочий лист. 4.2. Изменить условное форматирование таким образом, чтобы красная подсветка включалась так же и в случае неявки студента на экзамен (вместо отметки в ведомости проставлен символ «н»). ^ 5.1. Ссылки на данные, расположенные на разных рабочих листах (расширенный формат адреса). 5.2. Стандартные (встроенные) функции: ОКРУГЛ, ЕСЛИ, НЕ, ЕПУСТО [I – 357-362; 401-409; Справочная система Excel] ^ 6.1. Условное форматирование настраивается пиктограммой Условное форматирование из группы ^ на вкладке Главная. В программе Excel -2007 появились новые возможности условного форматирования: - можно выделять ячейки с помощью градиента – цвет плавно меняется от ячеек с максимальным значением до минимальных; - для выделения ячеек с интересующими значениями можно использовать значки, например, светофор – от красной двойки до зеленой пятерки; - упростился диалог, к котором задаются условия форматирования. 7. Библиографический список
|
![]() | Изготовление ватно-марлевой повязки Практическое занятие по обж | ![]() | Практическое занятие 9 Освоение способов создания, оформления таблиц и выполнения вычислений в таблицах |
![]() | Практическое занятие 5 Получение практических навыков по настройке программы и созданию и редактированию текстовых документов | ![]() | Практическое занятие 8 Освоение способов интеграции объектов: вставка в текстовый документ рисунков, созданных в других приложениях |
![]() | Практическое занятие 12 Для создания программ на vba в Excel формы и элементы управления используется так же, как и в Word | ![]() | Практическое занятие 11 Цель работы: изучение возможностей языка программирования Visual Basic for Application для создания функций пользователя |
![]() | Практическое занятие 10 Слияние документов – операция, позволяющая включить в основной документ информацию из другого источника (базы данных, таблицы) | ![]() | Практическое занятие 11 Создайте новый документ, введите текст "Создание формул" и сохраните документ в своей папке под именем Формула doc |
![]() | Практическое занятие 6 Откройте созданный на предыдущем занятии документ Tekst doc и перейдите в начало документа, нажав комбинацию клавиш Ctrl+Home. Выведите... | ![]() | Практическое занятие «Нестандартные методы решения неравенств. Метод замены функций» Решение некоторых логарифмических неравенств основано на переходе к новому, не зависящему от переменной, основанию |