Informatica_2006

71

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

Удаление столбцов и строк

Для удаления столбца следует активизировать одну из ячеек столбца и в меню Правка щелкнуть на команде Удалить. Раскроется диалоговое окно Удаление ячеек, в котором нужно выбрать опцию Столбец. При удалении строки следует выполнить те же действия, но выбрать опцию Строку.

Отображение и скрытие столбцов и строк

Для того чтобы спрятать столбцы, сначала необходимо их выделить, а затем в меню Формат выбрать команду Столбец. В раскрывшемся после этого подменю следует щелкнуть на команде Скрыть. Для того чтобы скрытые столбцы отобразить на экране, следует выделить столбцы слева и справа от скрытого и в меню Формат в подменю Столбец щелкнуть на команде Отобразить. Процедуры скрытия и отображения строк аналогичны тем, которые описаны для столбцов, с той разницей, что требуется предварительное выделение соответствующей строки, а в меню Формат - вызов подменю для строк.

5.9. Сортировка

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

Чтобы отсортировать список, следует:

-активизировать ячейку в списке;

-вызвать команду Дан-

ные/Сортировка;

-в списке Сортировать по указать столбец, по которому производится сортировка;

-определить порядок сортировки (по возрастанию или по убыванию);

-нажать ОК.

Помимо описанного простого
варианта сортировки, Excel позво-
ляет выполнять двухуровневую и
трехуровневую сортировку.
Двухуровневая сортировка ис-
пользуется в том случае, если в
столбце, по которому сортировал-	Рис. 5.7

72

ся список, имеются одинаковые значения. Записи с одинаковыми значениями в указанном поле можно дополнительно упорядочить по какому-либо другому полю. Это второе поле и будет определять второй уровень сортировки. Указывается второе поле для сортировки в списке Затем по в окне Сорти-

ровка диапазона (рис. 5.7).

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

Если список необходимо сортировать только по одному полю, можно использовать кнопки на панели Стандартная:

-выделить ячейку в столбце, по которому надо сортировать;

-щелкнуть на кнопке Сортировать по возрастанию или Сортировать

по убыванию.

5.10. Фильтрация

Использование Автофильтра

В результате применения Автофильтра на экране остаются только записи, удовлетворяющие заданным критериям. Остальные записи скрываются.

По сравнению с формой в Автофильтре могут задаваться более сложные критерии.

Чтобы воспользоваться Автофильтром, нужно:

-активизировать ячейку в списке;

-вызвать команду Данные/Фильтр/Автофильтр.

После выполнения данной команды ячейки с названиями полей превращаются в раскрывающиеся списки. Критерии для отбора записей задаются с помощью команд из этих списков.

Возможны следующие варианты:

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

2)команда Условие позволяет задавать одно или два условия для отбора записей; в окне данной команды следует выбрать операцию сравнения (равно, больше, не равно …) и ввести значение для сравнения; если необходимо, можно выбрать одну из функций И или ИЛИ и задать второе условие.

3)команда Первые 10 позволяет отбирать ряд записей из начала или конца списка; выбрав эту команду, можно задать число отбираемых записей, выбор из наибольших или наименьших элементов, единицы отсчета (количество или процент);

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

Чтобы завершить работу с Автофильтром, следует повторно выполнить

команду Данные/Фильтр/Автофильтр.

73

Использование Расширенного фильтра

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

В работе Расширенного фильтра используются три области:

1)Исходный диапазон – список, из которого будут отбираться записи;

2)Диапазон условий – ячейки на рабочем листе, в которых записаны условия отбора;

3)Диапазон извлечения – область на рабочем листе, в которую будут помещены отобранные записи.

Создание диапазона условий:

-в ту область рабочего листа, где предполагается создать условия отбора, скопировать заголовки полей списка, по которым будут отбираться записи;

-в ячейки под заголовками тех полей ввести условия отбора; условия могут занимать несколько строк; при этом действует правило: условия, записанные в одной строке, соединяются операцией И; разные строки условий соединяются операцией ИЛИ.

Создание диапазона извлечения:

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

Порядок использования Расширенного фильтра:

1)активизировать любую ячейку в списке;

2)вызвать команду Данные/Фильтр/Расширенный фильтр;

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

4)в группе Обработка выбрать вариант Скопировать результат в дру-

гое место; 5) щелкнуть в строке Поместить результат в диапазон; выделить в облас-

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

Вычисляемые и составные критерии

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

Вычисляемый критерий представляет собой формулу, результатом которой является логическая величина ИСТИНА или ЛОЖЬ. Расширенный фильтр отбирает записи, для которых проверяемое условие истинно. В формуле критерия можно использовать ссылки как на записи списка, так и на

74

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

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

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

Во всех случаях при указании диапазона условий для отбора данных обязательно включать строку с именами полей (либо без них, если имена не введены).

5.11. Ввод формул и исходных данных

Написание формул. Ввод в формулы адресов ячеек с данными

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

В программе Excel используется два способа ввода адресов ячеек с исходными данными:

-ввод адреса с клавиатуры;

-ввод щелчком мыши на адресуемой ячейке.

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

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

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

Использование в формулах имен ячеек

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

75

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

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

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

Заполнение ячеек данными

Для автоматизации процесса заполнения таблиц в программе Excel преду-

						смотрена	специаль-
						ная операция — За-
	Примеры заполнения ячеек
						полнение. Достаточ-
Содержимое
		Содержимое последующих ячеек				но указать	в первой
первой ячейки
						ячейке (или в первых
Пн		Вт	Ср	Чт	…
						двух ячейках) обра-
Среда		Четверг	Пятница	Суббота	…
						зец заполнения, а за-
Апрель		Май	Июнь	Июль	…
						тем поместить курсор
а		а	а	а	…
						мыши на	маленький

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

Например, запишем в двух последующих ячейках столбца числа: 2 и 4; выделим эти ячейки и, ухватив мышкой угол нижней ячейки, растянем

							выделение	на	после-
			Примеры заполнения ячеек
							дующие ячейки столбца.
							Как только кнопка мыши
	Содержимое двух			Содержимое последующих ячеек
							будет отпущена, в сто-
	первых ячеек
	1990		1991	1992	1993	…	блце появится		последо-
	1		3	5	7	…	вательность	четных чи-
	10		5	0	-5	…	сел. При этом в процессе

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

Дополнительные примеры приведены в таблицах.

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

76

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

Заполнение ячеек формулами

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

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

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

Относительные и абсолютные ссылки на ячейки

При заполнении ячеек формулами в каждом конкретном случае следует различать используемый вид адресации: относительный или абсолютный.

Относительная адресация используется тогда, когда адреса, содержащиеся в порожденных заполнением формулах, изменяются от ячейки к ячейке. В этом случае Excel при порождении формул сохраняет не адрес ячейки, а ее положение относительно ячейки с результатом (относительно формулы). Пример: если в ячейке В5 есть формула, использующая данные ячейки с адресом А5, то Excel отмечает у себя не адрес А5, а то, что нужное значение находится в соседней ячейке слева. Если заполнить той же формулой следующую ниже ячейку, т.е. ячейку В6, то здесь в вычислениях будет использовано значение из ячейки А6.

Если адрес в формуле является абсолютным, то при заполнении формулой других ячеек данный адрес не изменяется, и во всех формулах, порожденных Excel в рамках процедуры заполнения, будет использоваться один и тот же адрес. Для того чтобы программа Excel могла различать относительные и абсолютные адреса, в абсолютных адресах ставится символ $ (знак доллара) перед буквой столбца и номером ячейки. Например, адрес ячейки с абсолютной адресацией может выглядеть так: $А$3.

Для того чтобы изменить тип адресации достаточно добавить или, наоборот, удалить символы $ из формулы в строке формул. Другой способ заключается в позиционировании курсора в строке формул на соответствующий адрес и нажатии клавиши [F4] несколько раз, пока не установится требуемый тип адресации.

77

5.12. Использование библиотеки встроенных функций

Мастер функций

Программа Excel включает большую библиотеку встроенных функций, доступ к которой открывается после нажатия кнопки Вставка функций. Появляется окно Мастер функций (рис. 5.8). Встроенные функции собраны в разделах: финансовые, математические, статистические и др. Например, раздел математических функций включает прямые и обратные тригонометрические функции, логарифмические и показательные функции и т.д.

Рис. 5.8

После выбора нужной функции и нажатия на кнопку <Enter> открывается второе окно мастера функций, в котором следует указать адреса ячеек, содержащих значения аргументов выбранной функции. Окно содержит поля для ввода аргументов, а также краткое описание функции. Адреса ячеек можно либо непосредственно вписать в поля ввода, либо ввести их щелчком мыши в нужных ячейках. Чтобы открылся доступ ко всем ячейкам таблицы окно Мастера функций можно уменьшить либо передвинуть с помощью мыши. Для уменьшения окна нужно щелкнуть на кнопке, расположенной справа в поле ввода аргумента, - окно уменьшится до размеров поля ввода. Теперь можно выбрать нужные ячейки. Перечень выделенных ячеек отображается в поле ввода первого аргумента и строке формул. После выбора ячеек диалоговое окно можно снова увеличить, используя кнопку, расположенную справа в поле аргумента уменьшенного диалогового окна. Для завершения ввода функции следует щелкнуть на кнопке ОК.

В случае функции нескольких аргументов процедура ввода адресов ячеек повторяется для каждого аргумента.

78

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

5.13. Диаграммы

Этапы создания диаграммы

Рассмотрим этапы создания диаграммы.

При создании диаграммы имеет смысл вначале выделить ту область таблицы, которая содержит данные для построения диаграммы, включая заголовки столбцов и строк. Эти заголовки программа Excel использует в качестве элементов описания. При этом совсем не обязательно выделять только смежные области ячеек. Область данных можно также задать позже в диалоговых окнах Мастера диаграмм. Однако без выделения области данных невозможен предварительный просмотр диаграммы, поэтому рекомендуется сначала выделить данные.

Создание диаграммы осуществляется за четыре шага.

Первый шаг. Первое окно Мастера диаграмм, называемое Тип диаграммы, вызывается щелчком на кнопке панели инструментов. Окно состоит из двух вкладок.

На вкладке Стандартные в области Тип программа Excel выводит стандартные типы диаграмм. Для каждого типа диаграммы в поле Вид предлагаются разновидности выбранного типа диаграммы. С помощью командной кнопки Просмотр результата можно увидеть диаграмму, построенную на основе выделенных данных. Дополнительные виды диаграмм приведены на вкладке Нестандартные.

Программа Excel позволяет подключать созданные ранее пользователем типы диаграмм. Для этого на вкладке Нестандартные в области Вывести следует выбрать опцию Дополнительные.

После выбора типа диаграммы нужно щелкнуть на кнопке Далее.

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

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

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

79

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

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

Третий шаг. В следующем диалоговом окне - Параметры диаграммы - устанавливаются детали оформления диаграммы. На различных вкладках, в частности, определяется, появится ли на диаграмме легенда, таблица значений и заголовок диаграммы.

На вкладке Заголовок можно ввести названия диаграммы и осей координат.

На вкладке Оси можно указать, изображается ось на диаграмме или нет, а также определить характер подписей меток оси X.

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

Вкладка Легенда используется, когда нужно пояснить диаграмму. В области Размещение можно выбрать позицию легенды. Легенду можно переместить также с помощью мыши.

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

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

Четвертый шаг - размещение диаграммы. В четвертом окне Мастера диаграмм можно выбрать: поместить ли диаграмму на текущем листе таблицы или на отдельном листе.

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

После завершения всех необходимых установок следует щелкнуть на кнопке Готово.

80

Изменение диаграммы

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

Форматирование элементов диаграммы

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

Для форматирования нужного элемента нужно его выделить, а затем открыть меню Формат и вызвать диалоговое окно. Диалоговое окно для форматирования выделенного элемента можно вызвать также одновременным нажатием клавиш <Ctrl>+<1>. Команды форматирования позволяют менять толщину и цвет линий, цвет заливки, шрифт и размер букв, ориентацию надписей и т.д.

Изменение размеров диаграммы

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

5.14. Дополнительные возможности Excel

Присвоение собственных имен ячейкам

Хотя такие обозначения ячеек, как А1 или С32, являются уникальными, при работе с большой таблицей не всегда легко вспомнить, в какой ячейке были проведены вычисления и сохранены данные. В подобной ситуации приходится долго «бродить» по таблице, прежде чем нужная ячейка будет обнаружена. Проще присвоить ячейке выразительное имя, которое будет напоминать о ее содержимом, например, Полный_оборот_средств.

Имя можно также присвоить целой области ячеек.

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

Назначение имен ячейкам

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

ОК.