Промежуточные итоги. Условное форматирование

1.    Что такое сортировка данных в электронной таблице? Какими способами ее можно выполнить?

2.    Что такое фильтрация данных в электронной таблице? Какими способами ее можно выполнить?

3.    Что такое расширенная фильтрация? Приведите пример ее использования.

ПРОМЕЖУТОЧНЫЕ ИТОГИ

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

На рисунке 7.31 представлена таблица с данными о продаже дисков с фильмами в нескольких магазинах.

Как мы видим, в трех столбцах этой таблицы (Магазин, Жанр и Название) есть данные, которые повторяются. Анализируя данные этой таблицы, можно выяснить, например, сколько экземпляров фильмов каждого жанра было продано, какой магазин продал больше дисков и на какую сумму, сколько экземпляров каждого фильма и на какую сумму было продано и т. п. Такой анализ позволяет планировать, какой из жанров имеет

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

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

Одним из способов выполнить такие операции - является нахождение промежуточных итогов.

Чтобы найти промежуточные итоги, следует:

1.    Выделить ячейки, для значений которых будут находиться промежуточные итоги. Например, на рисунке 7.32 это диапазон А:К

2.    Выполнить сортировку выделенного диапазона в том столбце, по которому необходимо сгруппировать данные. Например, столбец В -Жанр.

3.    Выполнить Данные ^ Структура ^ Промежуточные итоги.

Выбрать в окне Промежуточные итоги (рис. 7.32) в списке поля Операция ту операцию, которая будет обрабатывать данные (Сумма, Количество, Среднее, Максимум и т. д.). Например, на рисунке 7.32 это операция Сумма.

4.    Установить в окне Промежуточные итоги в списке поля Добавить итоги по отметки флажков у тех столбцов, для которых будут вычислены промежуточные итоги. Например, на рисунке 7.32 это столбцы Количество и Продано.

5.    Выбрать кнопку ОК.

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

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

ров Боевик, Фантастика. Если выбрать одну из этих кнопок, то соответствующая группа строк свернется и будет отображаться только строка Итог, а кнопка слева от группы примет вид + . Например, для жанров на рисунке 7.33 Драма, Комедия. В любой момент можно выбрать эту кнопку и снова развернуть соответствующую группу. В самом конце таблицы подведен общий итог по количеству и сумме продаж.

Над кнопками сворачивания/разворачивания групп находятся три кнопки Ш’ Е и |з! быстрого сворачивания/разворачивания уровней сгруппированной таблицы: первый уровень - это вся таблица, второй - группы, третий - члены групп.

УСЛОВНОЕ ФОРМАТИРОВАНИЕ

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

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

Например, в электронном журнале класса удобно обозначать разными цветами данные об учениках, которые имеют оценки низкого, среднего, достаточного и высокого уровней. Например, ячейки, значения которых находятся в диапазоне от 1 до 3, выделить красным цветом, оценки от 4 до 6 - зеленым, от 7 до 9 - желтым, от 10 до 12 - синим цветом.

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

В Excel 2007 для условного форматирования существует пять способов выделения ячеек, список которых открывается выбором кнопки Условное форматирование в группе Стили на вкладке Главная (рис. 7.34):

1.    Правила выделения ячеек.

2.    Правила отбора первых и последних значений.

3.    Гистограммы.

4.    Цветовые шкалы.

5.    Наборы значков.

Для установки первых двух видов условного форматирования, а именно установление правил форматирования, следует:

1.    Выделить необходимый диапазон ячеек.

2.    Выполнить Главная ^ Стили ^ Условное форматирование.

3.    Выбрать в списках кнопки Условное форматирование нужный способ (рис. 7.34).

4.    Указать в списке выбранного способа нужное правило форматирования.

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

6.    Выбрать кнопку ОК.

На рисунке 7.35 представлены в качестве примера окно между, в котором установлено условие форматирования между 7 и 9, со списком стандартных форматов, а также предварительный просмотр выбранного условного форматирования ячеек таблицы.

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

Установка условного форматирования типа Цветовые шкалы приводит к заливке в выделенном диапазоне ячеек с одинаковыми значениями одним и тем же цветом (рис. 7.37).

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

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

Обращаем ваше внимание: к одному диапазону может быть применено несколько правил условного форматирования, выполненных последовательно.

Для удаления условного форматирования следует выполнить Условное форматирование ^ Удалить правила ^ Удалить правила из выделенных ячеек (или Удалить правила со всего листа).

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

Кнопка Промежуточные итоги, которая устанавливает данный режим вычислений, находится на вкладке Данные в группе Структура.

Условное форматирование автоматически изменяет формат ячейки на заданный, если выполняется некоторое условие. Существует пять способов выделения ячеек: Правила выделения ячеек, Правила отбора первых и последних значений, Гистограммы, Цветовые шкалы, Наборы значков. Их список открывается выбором кнопки Условное форматирование в группе Стили на вкладке Главная.

1°. Что такое промежуточные итоги? Когда их целесообразно создавать? 2*. Как вычислить промежуточные итоги в Excel 2007?

3*. Какие функции можно вычислять в промежуточных итогах?

4° Что такое условное форматирование? Для чего оно выполняется? 5°. Какие есть способы условного форматирования?

6*. Как выполнить условное форматирование?

7°. Как очистить условное форматирование на всем листе?

1°. Откройте указанный учителем файл (например, Глава 7\Пункт 7.5\образец 7.5.1.х1вх). На листе Листі представлена таблица со значениями свойств винчестеров. Вычислите промежуточные итоги по значениям в столбце Фирма. Сохраните книгу в вашей папке в файле с именем задание 7.5.1.х1вх.

2*. Откройте указанный учителем файл (например, Глава 7\Пункт 7.5\образец 7.5.2.х1ях). На листе Листі представлена таблица со значениями свойств мониторов. Вычислите промежуточные итоги по значениям в столбце Диагональ. Сохраните книгу в вашей папке в файле с именем задание 7.5.2.х1ях.

3*. Откройте указанный учителем файл (например, Глава 7\Пункт 7.5\образец 7.5.3.х1вх). На листе Листі представлена таблица с дан-

ными о ТБТ-мониторах. Закрасьте в розовый цвет все ячейки, в которых диагональ монитора меньше 21". Сохраните книгу в вашей папке в файле с именем задание 7.5.3.х1вх.

4*. Откройте указанный учителем файл (например, Глава 7\Пункт 7.5\образец 7.5.4.х1ях). На листе Лист1 представлена таблица с данными о жестких магнитных дисках. Установите синий цвет ячеек, в которых емкость кэш-памяти больше 8 Мбайт. Сохраните книгу в вашей папке в файле с именем задание 7.5.4.х1ях.

5*. Откройте указанный учителем файл (например, Глава 7\Пункт 7.5\образец 7.5.5.х1вх). На листе Лист1 представлена таблица с данными о температуре за последний месяц в г. Киеве. Установите условное форматирование Гистограммы (синяя гистограмма) для всех числовых данных таблицы. Сохраните книгу в вашей папке в файле с именем задание 7.5.5.х1вх.

6*. Найдите в Интернете данные о количестве населения в вашем областном центре за последние 5 лет. На листе Лист1 создайте электронную таблицу по этим данным. Установите условное форматирование с использованием Наборов значков (4 сигнала светофора) для данных таблицы. Сохраните книгу в вашей папке в файле с именем задание 7.5.6.х1вх.

ПРАКТИЧЕСКАЯ РАБОТА № 16

«Сортировка данных в таблицах. Автоматические и расширенные фильтры. Условное форматирование»

Внимание! Работая с компьютером, соблюдайте правила безопасности и санитарно-гигиенические нормы.

1.    Откройте указанный учителем файл (например, Глава 7\Практиче-ская 16\образец пр16.х1вх).

2.    На листе Лист1 выполните сортировку таблицы по значениям в столбце Фамилия по убыванию.

3.    На листе Лист2 выполните сортировку таблицы по значениям в нескольких столбцах в указанном порядке: Фамилия (по возрастанию), Имя (по возрастанию), Год рождения (по убыванию).

4.    На листе ЛистЗ отобразите данные об учениках, рост которых выше 1 м 80 см.

5.    На листе Лист4 отобразите данные об учениках, которые посещают кружки рисования или пения.

6.    На листе Лист5 вычислите промежуточные итоги по значениям в столбце Вес учеников, которые определяют средний и максимальный вес.

7.    На листе Лист6 установите условное форматирование, чтобы ячейки, в которых вес учащихся больше 80 кг, закрашивались красным цветом, а ячейки, в которых вес учащихся меньше 75 кг, - синим.

8.    Сохраните книгу в вашей папке в файле с именем практическая 16.х1зх.

Это материал учебника Информатика 8 класс Ривкинд