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

·  Если параметр "тип сопоставления" равен 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 неверно принимает подписи по оси за данные, в этом случае необходимо удалить ненужный ряд данных. Так же на этой закладке можно указать подписи данных под осью или на графике (подписи категорий). В поле «Имя» можно задать ячейку таблицы, содержащую название диаграммы. При необходимости вернуться на предыдущий шаг нажмите кнопку «Назад <» (чтобы снова выбрать тип диаграммы).