СОВРЕМЕННЫЕ ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ

51

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

Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейки, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании формулы через Буфер обмена или при помощи Маркера заполнения вдоль строк и вдоль столбцов ссылка автоматически корректируется. Например, если в ячейке B2 записана формула =A1, то при копировании содержимого данной ячейки в ячейку B4, в последней будет записана формула =A3. Если формула из B2 копируется в ячейку D2, тогда формула примет вид =C2. Если же содержимое B2 копируется в D4, тогда в D4 будет формула =C3.

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

Абсолютная ссылка в формуле, например, $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы через Буфер обмена или при помощи Маркера заполнения вдоль строк и вдоль столбцов абсолютная ссылка не корректируется.

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

Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов имеет вид $A1, $B1 и т.д. Абсолютная ссылка строки имеет вид A$1, B$1 и т.д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная нет.

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

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

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

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

52

	Для копируе-					Изменяется
	мой формулы:			Если ссылка:		на:
				$A$1 (абсолютный столбец		$A$1
				и абсолютная строка)
				A$1 (относительный стол-		C$1
				бец и абсолютная строка)
				$A1 (абсолютный столбец и		$A3
				относительная строка)
				A1 (относительный столбец		C3
				и относительная строка)

Как было сказано выше, формулы могут содержать Внешние ссылки. Такая ссылка содержит, в отличие от описанные ранее ссылок, имя листа, которое отделяется от адреса ячейки восклицательным знаком, например: =Лист3!С12. В ссылке на другую открытую книгу имя книги заключается в прямоугольные скобки:

=[Книга1]Лист3!С12.

Вотличие от алгебраических формул, формулы в MS Excel 2007 записываются

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

Контрольные вопросы

1.Каково основное назначение программы MS Excel 2007?

2.Перечислить основные элементы Книги в MS Excel 2007.

3.Что представляет собой адрес ячейки?

4.Какие виды данных можно вводить в ячейку?

5.Если в ячейку было введено число, а в ячейке отображаются знаки #####, что это означает?

6.В каком виде следует вводить десятичную дробь?

7.В каком виде можно вводить дату?

8.В каком виде следует вводить время?

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

10.Что такое формула в MS Excel 2007?

11.Какие виды ссылок могут содержать формулы и в чем их различие?

вернуться к оглавлению

перейти к практическому занятию

53

Лекция 9. Электронные таблицы Microsoft Excel 2007. Таблицы – списки

9.1 Функции

Функции это готовые, зачастую очень сложные формулы, выполняющие заранее определенные операции. Функции всегда возвращают результат в ячейку, в которую они введены. MS Excel 2007 имеет более 300 встроенных функций, выполняющих широчайший спектр различных вычислений. Любая функция состоит из двух частей – имени функции и одного или нескольких аргументов, например: =СУММ(D6:D9). Имя функции СУММ описывает операцию, которую эта функция выполняет. В круглых скобках после имени функции перечисляются аргументы, которые задают значения или ячейки, которые эта функция использует. Открывающая круглая скобка ставится сразу после имени функции, пробел не допустим. Если функция использует несколько аргументов, то они разделяются точкой с запятой. Некоторые функции, например СЕГОДНЯ(), не имеют аргументов.

В функциях используются аргументы следующих видов:

а) числовые – числа, числовые формулы, адреса или имена ячеек, содержащие числа или числовые формулы;

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

в) массив – множество значений, заключенных в фигурные скобки. Строки в записи массива разделяются точкой с запятой. Например, {3,4,5;9,8,4;9,0,5};

г) ссылка – адрес, имя или любая формула, возвращающая адрес или имя; д) логические значения Истина или Ложь.

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

Во избежание ошибок при записи функции следует пользоваться Мастером функций. Вызвать Мастер функций можно несколькими способами: Главная → Ре-

дактирование → Сумма → Другие функции, Формулы → Библиотека функций → Вставить функцию, Формулы → Библиотека функций → список функций определенного типа.

54

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

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

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

Как было сказано выше, все функции разделены на следующие категории:

Подкатегории "Справочник по функциям"

Функции надстроек и программирова- Куб ния объектов

	Управление базами данных и списками	Функции даты и времени
	Инженерные функции	Финансовые функции
	Информационные функции	Логические функции
	Функции ссылки и автоподстановки	Математические и тригонометрические
		функции
	Статистические функции	Текст и данные

Наиболее часто используются функции:

						55
	Функции даты и времени:
	Функция					Описание
	ДАТА					Возвращает числовую форму отдельной дате
	ДАТАЗНАЧ					Преобразует дату из текстовой формы в числовую.
	ДЕНЬ					Преобразует числовую форму в день месяца.
	ДНЕЙ360					Вычисляет количество дней между двумя датами на основе 360-
						дневного года.
	ЧАС					Преобразует числовую форму в часы.
	МИНУТЫ					Преобразует числовую форму в минуты.
	МЕСЯЦ					Преобразует числовую форму в месяц.
	ТДАТА					Возвращает числовую форму текущим дате и времени.
	СЕКУНДЫ					Преобразует числовую форму в секунды.
	ВРЕМЯ					Возвращает числовую форму отдельному времени.
	ВРЕМЗНАЧ					Преобразует время из текстового формата в числовую форму.
	СЕГОДНЯ					Возвращает числовую форму текущей дате.
	ДЕНЬНЕД					Преобразует числовую форму в день недели.
	РАБДЕНЬ					Возвращает числовую форму дате, отстоящей вперед или назад на
						заданное количество рабочих дней.
	ГОД					Преобразует числовую форму в год.
	ДОЛЯГОДА					Возвращает долю года, которую составляет количество дней между
						начальной и конечной датами.
Логические функции:
	Функция					Описание
	И			Возвращает «ИСТИНА», если все аргументы являются «ИСТИ-
				НА»
	ЛОЖЬ			Возвращает логическое значение «ЛОЖЬ»
	ЕСЛИ			Предписывает выполнить логическую проверку
	НЕ			Меняет логическое значение своего аргумента на противополож-
				ное
	ИЛИ			Возвращает «ИСТИНА», если любой аргумент является «ИСТИ-
				НА»
	ИСТИНА			Возвращает логическое значение «ИСТИНА»

56

Математические и тригонометрические функции:

	Функция			Описание
	ABS		Возвращает абсолютное значение числа
	ОКРВВЕРХ		Округляет число до ближайшего целого или до ближайшего крат-
			ного значению
	ОКРВНИЗ		Округляет число в меньшую к нулю сторону
	ЦЕЛОЕ		Округляет число до ближайшего меньшего целого
	ПРОИЗВЕД		Перемножает аргументы
	ЧАСТНОЕ		Возвращает целую часть результата деления
	СЛЧИС		Возвращает случайное число от 0 до 1
	СЛУЧМЕЖДУ		Возвращает случайное число в интервале между заданными чис-
			лами
	РИМСКОЕ		Преобразует арабскую цифру в римскую как текст
	ОКРУГЛ		Округляет число до указанного числа десятичных разрядов
	ОКРУГЛВНИЗ		Округляет число в меньшую к нулю сторону
	ОКРУГЛВВЕРХ		Округляет число в большую от нуля сторону
	КОРЕНЬ		Возвращает положительное значение квадратного корня
	СУММ		Суммирует аргументы
	СУММЕСЛИ		Суммирует ячейки, удовлетворяющие заданному условию
	СУММЕСЛИМН		Суммирует диапазон ячеек, удовлетворяющих нескольким усло-
			виям.
	СУММПРОИЗВ		Возвращает сумму произведений соответствующих элементов
			массива
	СУММКВ		Возвращает сумму квадратов аргументов
	ОТБР		Отбрасывает дробную часть числа
Статистические функции:
	Функция			Описание
	СРЗНАЧ			Возвращает среднее арифметическое аргументов
	СРЗНАЧЕСЛИ			Возвращает среднее значение (среднее арифметическое) всех
				ячеек в диапазоне, которые удовлетворяют данному условию.
	СЧЁТ			Подсчитывает количество чисел в списке аргументов

			57
	СЧЕТЗ				Подсчитывает количество значений в списке аргументов
	СЧИТАТЬПУСТОТЫ				Подсчитывает количество пустых ячеек в диапазоне
	СЧЁТЕСЛИ				Подсчитывает количество непустых ячеек в диапазоне, удов-
					летворяющих заданному условию
	СРГЕОМ				Возвращает среднее геометрическое
	НАИБОЛЬШИЙ				Возвращает k-ое наибольшее значение множества данных
	МАКС				Возвращает максимальное значение из списка аргументов
	МЕДИАНА				Возвращает медиану заданных чисел
	МИН				Возвращает минимальное значение из списка аргументов
	МОДА				Возвращает значение моды множества данных
	РАНГ				Возвращает ранг числа в списке чисел
	НАИМЕНЬШИЙ				Возвращает k-ое наименьшее значение множества данных
	Текстовые функции
	Функция				Описание
	ASC			Преобразует полноширинные (двухбайтные) английские буквы
				или знаки катакана в текстовой строке в полуширинные (одно-
				байтные)
	СИМВОЛ			Возвращает знак, определенный кодовым номером
	ПЕЧСИМВ			Удаляет все непечатаемые знаки из текста
	КОДСИМВ			Возвращает числовой код первого знака в текстовой строке
	СЦЕПИТЬ			Объединяет несколько текстовых элементов в один
	СОВПАД			Проверяет идентичность двух текстовых значений
	НАЙТИ, НАЙТИБ			Ищет одно текстовое значение внутри другого (с учетом реги-
				стра)
	ИСПРАВЛЕНО			Форматирует число как текст с заданным числом десятичных
				знаков
	JIS			Преобразует полуширинные (однобайтные) английские буквы
				или знаки катакана в текстовой строке в полноширинные
				(двухбайтные)
	ЛЕВ, ЛЕВБ			Возвращает самые левые знаки текстового значения
	ДЛСТР, ДЛИНБ			Возвращает количество знаков в текстовой строке
	СТРОЧН			Преобразует буквы текста в строчные

			58
	ПСТР, ПСТРБ		Возвращает заданное число знаков из строки текста, начиная с
			указанной позиции
	PHONETIC		Извлекает фонетические знаки (фуригана) из текстовой строки
	ПРОПНАЧ		Делает прописной первую букву каждого слова текстового
			значения
	ЗАМЕНИТЬ, ЗА-		Заменяет знаки в тексте
	МЕНИТЬБ
	ПОВТОР		Повторяет текст заданное число раз
	ПРАВ, ПРАВБ		Возвращает самые правые знаки текстовой строки
	ПОИСК, ПОИСКБ		Ищет одно текстовое значение внутри другого (без учета реги-
			стра)
	ПОДСТАВИТЬ		Заменяет в текстовой строке старый текст новым
	Т		Преобразует аргументы в текст
	ТЕКСТ		Форматирует число и преобразует его в текст
	СЖПРОБЕЛЫ		Удаляет из текста пробелы
	ПРОПИСН		Преобразует буквы текста в прописные
	ЗНАЧЕН		Преобразует текстовый аргумент в число

MS Excel 2007 позволяет использовать функции, как один из аргументов другой функции. Такие функции называются вложенными. Можно вложить до 64 уровней функций. В качестве примера вложенной функции можно рассмотреть функцию, которая суммирует набор чисел (G2:G5), только если среднее значение другого набора чисел (F2:F5) больше 50. В противном случае она возвращает значение 0.

Здесь функции СРЗНАЧ и СУММ вкладываются в функцию ЕСЛИ.

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

 Активизировать ячейку, в которую нужно ввести формулу.

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

Выбрать функцию, которую необходимо использовать. Можно задать поиск в окне Поиск функции (например, запрос «сложить» возвращает функцию СУММ) или осуществить поиск по категориям с помощью поля Категория.

Ввести аргументы. Тип аргумента, используемого функцией, зависит от конкретной функции. Обычно аргументы, используемые функциями, являются числами, текстом, ссылками на ячейки и именами.

59

 Чтобы ввести ссылки на ячейки в качестве аргументов, нажать кнопку Свернуть диалоговое окно , находящуюся рядом с тем аргументом, который необходимо ввести (диалоговое окно будет временно скрыто), выбрать ячейки на листе, а затем нажать кнопку Развернуть диалоговое окно . Можно также просто переместить окно Аргументы функции за строку-заголовок так, чтобы оно не закрывало нужные ячейки.

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

ки формул.

Чтобы продолжить ввод аргументов в предыдущую функцию, необходимо открыть ее окно. Это можно сделать, выполнив щелчок левой кнопкой мыши на имени нужной функции в правой части Строки формул.

Части формулы, отображенные в диалоговом окне Аргументы функции, отображают функцию, выбранную на предыдущем шаге.

9.2Таблица-список

Кроме описанных выше способов обработки информации, MS Excel 2007 позволяет выполнять и другие, такие как фильтрация, сортировка, консолидация, подведение итогов и составление сводных таблиц. Данные виды работ могут быть выполнены только в таблицах определенной структуры. Все строки в таких таблицах, за исключением первой строки, должны быть одинакового вида. Первая строка должна содержать названия столбцов. Все столбцы таблицы должны содержать однотипные данные. Пустые строки или пустые столбцы в этой таблице недопустимы. Такие таблицы называют Таблицы-списки. За другими элементами таблицы также закреплены определенные названия, а именно, первая строка – Строка-заголовок, строка таблицы

– запись, ячейка – поле.

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

Для проведения операции фильтрации необходимо активизировать любую ячейку внутри таблицы, а затем выбрать одноименную команду, а именно, Глав-

ная→Редактирование→Сортировка и фильтр→Фильтр

60

либо команду Фильтр в группе Сортировка и фильтр на вкладке Данные.

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

такие как: равно, не равно, начинается с …, заканчивается на …, содержит …, не со-

держит …, настраиваемый фильтр. В списке, содержащем числовые значения,

можно задавать Числовые фильтры, такие как: равно, не равно, больше, …, первые 10, …, настраиваемый фильтр.

	Используйте		Чтобы найти
	? (знак вопроса)		Любой знак (один)
			Пример: условию «бар?н» соответствуют результа-
			ты «барин» и «барон»
	* (звездочка)		Любое количество знаков
			Пример: условию «*-восток» соответствуют резуль-
			таты «северо-восток» и «юго-восток»
	~ (тильда), за кото-		Вопросительный знак, звездочку или тильду
рой следует ?, * или ~			Пример: условию «ан91~?» соответствует результат
			«ан91?»

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

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

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