Рис. 4.3. Ввод исходных данных: аргументов функции (а) и ее значений (б)
Для нахождения количества отрицательных значений функции организуем справа дополнительный столбец признаков. Каждая ячейка столбца признаков будет содержать 1, если значение функции в этой строке больше нуля и 0 в противном случае. Просуммировав в дальнейшем эти ячейки, мы получим количество отрицательных значений функции.
Рис. 4.4. Вычисление количества отрицательных (а) и суммы положительных (б) значений функции
Установить требуемые значения в ячейках столбца можно с помощью функции ЕСЛИ. Эта функция возвращает одно из двух значений, в зависимости от истинности логического выражения. Установим указатель в ячейку C2 и вызовем Мастер функций. В категории Логические выберем функцию ЕСЛИ. На следующем шаге в поле Лог_выражение введем B2<0, а в поля Значение_если_истина и Значение_если_ложь значения 1 и 0 соответственно. Заполним созданной формулой диапазон C3:C7. Далее нажмем кнопку с символом “∑” (автосумма) и в ячейке C8 получим искомое значение (рис. 4.4, а).
Для вычисления суммы положительных значений будем использовать функцию СУММЕСЛИ, которая суммирует содержимое тех ячеек заданного диапазона, которые удовлетворяют некоторому критерию. Сделаем активной ячейку C9 и вызовем Мастер функций. В категории Математические выберем функцию СУММЕСЛИ. Далее в поле Диапазон введем B2:B7, а в поле Критерий выражение >0. После ввода аргументов нажмем ОК и получим требуемое значение
(рис. 4.4, б).
Построение диаграмм
Диаграммы позволяют в наглядной форме представить данные, расположенные на рабочих листах и визуально оценить соотношение между их значениями. Наиболее просто можно построить диаграмму выделив на листе диапазон с данными и выполнив команду Вставка | Диаграмма. Запускается Мастер диаграмм, который предлагает за четыре шага настроить вид создаваемой диаграммы. На первом шаге необходимо выбрать тип диаграммы. Для перехода к следующему шагу нажимается кнопка Далее. Нажатие кнопки Готово прервет работу Мастера диаграмм и приведет к немедленной вставке диаграммы на текущий лист. При этом те параметры, которые настраиваются на последующих шагах, будут иметь значения, установленные по умолчанию.
На втором шаге при необходимости уточняется диапазон с данными и способ размещения рядов данных (если их несколько) внутри этого диапазона: в строках или столбцах. Кроме того, каждому из рядов данных на вкладке Ряд можно присвоить свое имя. Третий шаг позволяет указать заголовки диаграммы и осей, включить или отключить линии сетки, добавить легенду, а также произвести другие настройки. На четвертом шаге выбирается место размещения диаграммы: на отдельном листе (специальном листе диаграмм) или текущем листе с данными.
Если диаграмма расположена на листе данных, то после построения ее размеры можно изменять, как и у любого графического объекта. Также можно изменять любые параметры диаграммы, установленные при ее создании: тип, диапазон исходных данных, параметры и место размещения. Для этого необходимо вызвать контекстное меню диаграммы и выбрать соответствующую команду.
Работа со списками данных
Большинство таблиц, создаваемых в Excel, являются какими-либо списками. Для большей эффективности при работе со списком, его структура должна соответствовать следующим правилам:
- верхняя строка таблицы должна содержать уникальные заголовки столбцов, содержимое которых располагается ниже;
- каждый столбец должен содержать однородные данные;
- каждая строка имеет одинаковую структуру;
- в таблице не должно быть пустых строк и столбцов.
Если на листе имеется несколько списков, то они должны отделяться друг от друга как минимум одной пустой строкой или столбцом.
Основными операциями, производимыми со списками, являются сортировка и фильтрация. Для выполнения сортировки необходимо установить указатель в любую ячейку списка и выполнить команду Данные | Сортировка. В диалоговом окне команды выбирается нужный столбец списка и направление сортировки: по возрастанию или по убыванию. Причем если список соответствует изложенным выше требованиям, то программа может идентифицировать столбцы по их заголовкам. Однократно сортировку списка можно произвести по содержимому одного, двух или трех столбцов. В случае множественной сортировки, строки, у которых совпадают значения в первом столбце, между собой сортируются по содержимому второго столбца и т.д. Для сортировки по четырем и более столбцам, описанная выше процедура повторяется многократно.
Фильтрация позволяет временно скрыть те строки списка, в которых значения в одном или нескольких столбцах не удовлетворяют заданным критериям. При этом с видимой после наложения фильтра частью списка можно производить те же действия, что и с любым другим фрагментом рабочего листа.
В Excel существует два варианта наложения фильтра: автофильтрация и расширенный фильтр. Первый обычно используется для установки простых условий отбора, второй – для фильтрации с применением более сложных критериев. Наиболее просто в Excel производится автофильтрация. Для ее применения необходимо сделать активной любую ячейку списка и выполнить команду Данные | Фильтр| Автофильтр. В заголовке каждого столбца появится значок раскрывающегося списка (рис. 4.5).
Рис. 4.5. Вид списка после включения режима автофильтрации
Щелкнув на значке в том столбце, по содержимому которого будет производиться фильтрация, можно выбрать один из нескольких вариантов отбора. Для того чтобы оставить видимыми строки с одним конкретным значением, его необходимо выбрать из списка вариантов фильтрации. Выбрав команду Первые 10 можно указать сколько наибольших (наименьших) значений останется после наложения фильтра. Более сложные критерии отбора можно установить, выбрав команду Условие. В диалоговом окне можно установить два условия, объединив их логической связкой «И» или «ИЛИ». Для примера произведем отбор тех записей, у которых должностной оклад больше 2000 р. и меньше 6000 р. (рис. 4.6, а). Введем каждое из условий в соответствующие строки диалогового окна (рис. 4.6, а) и нажмем ОК. Результат фильтрации показан на рис. 4.6, б.
|  |
Рис. 4.6. Диалоговое окно ввода условий фильтрации (а) и ее результаты (б)
При необходимости, после фильтрации списка по содержимому одного столбца, можно повторить операцию с другим столбцом и отфильтровать уже оставшуюся часть таблицы. Для отключения режима автофильтрации и показа всех скрытых записей, необходимо повторно выполнить команду Данные | Фильтр| Автофильтр.
СОДЕРЖАНИЕ РАБОТЫ
1. Запустите приложение Microsoft Excel и создайте с его помощью файл с именем Lab.xls.
2. Переименуйте листы книги MS Excel: первый в «Задание», второй лист в «Данные», третий лист – «График».
3. Выберите из таблицы вариант задания соответственно номеру в журнале группы.
Варианты задания
4. На листе «Задание» объедините несколько ячеек. Используя шрифт Times New Roman, размер шрифта 12, горизонтальное и вертикальное выравнивание в ячейке по центру, наберите следующую информацию:
