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

 

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

Например, для вычисления процентного соотношения В2/В5 в ячейку Е2 (таблица 2) введите формулу =В2/В5, затем выделите ячейку Е2 и нажмите кнопку % на панели инструментов Форматирование. Теперь скопируйте формулу из ячейки Е2 в ячейки Е3 и Е4. Вы получите ошибочный результат, т.к. при копировании адрес ячейки В5 изменится на В6 и на В7. Чтобы избежать этого, необходимо заменить адрес ячейки с относительного В5 на абсолютный $B$5, т.е. не подлежащий изменению при копировании формул. Эту операцию можно выполнить автоматически, если дважды щелкнуть в строке формул на адресе ячейки В5 и нажать клавишу F4.

При заполнении ячеек формулой как относительная рассматривается только та часть адреса, перед которой нет символа «$». Если записать например, $А4, то при переносе формул будет изменяться только адрес строки ячеек, а при обозначении А$4 – только адрес столбца.

Таблица 2

Другой способ абсолютной адресации ячеек заключается в назначении имен ячейкам и использовании их в формулах. Например, назначив имя Всего ячейке В5 (таблица 2), можно в ячейке Е2 ввести формулу =В2/Всего.

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

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

 

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

 

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

Вы можете выбрать нужную функцию с помощью Мастера функций. Чтобы открыть диалоговое окно Мастер функций выберите из списка стандартных функций пункт Другие функции. Данное окно можно открыть также с помощью команды меню Вставка – Функция. В списке Категория выберите ту категорию, к которой относится нужная функция, а в списке Функция – конкретную функцию. Если вы помните название нужной вам функции, то введите его в поле Имя и затем нажмите кнопку Изменить функцию.

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

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

 

Матрица

 

В качестве примера рассмотрим умножение матрицы, состоящей из пяти элементов, на цифру 5 (таблица 3). Для этого выделите в рабочем листе область такого же размера, как перемножаемая матрица (В1:В5).

В строку формул введите формулу =А1:А5*5 и нажмите комбинацию клавиш Ctrl+Enter. Такого же результата можно добиться, если в ячейку В1 ввести формулу =А1*5, нажать клавишу Enter, а затем данную формулу скопировать в ячейки с В2 по В5 любым известным вам способом.

Таблица 3

	А	В	С	D

 

Если вам необходимо умножить матрицу из данных столбца А на данное из ячейки С1, можно выполнить следующие действия:

- введите в ячейку D1 формулу =А1*$С$1 и нажмите клавишу Enter

- скопируйте введенную формулу в ячейки D2:D5.

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

 

Создание диаграмм

 

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

· Ряды данных – это наборы значений, которые требуется изобразить на диаграмме (значения функции на оси Y).

· Категории (аргументы функции на оси Х) служат для упорядочения значений в рядах данных.

· Легенда – это условные обозначения значений различных рядов данных на диаграмме.

Создать диаграмму проще с помощью Мастера диаграмм. Вызвать диалоговое окно Мастер диаграмм можно несколькими способами:

1) щелкнув на кнопке Мастер диаграмм на панели инструментов Стандартная.

2) используя контекстно-зависимое меню для ярлычков рабочих листов и, вызвав команду Добавить, на вкладке Общие выберите Диаграмма из предложенных вариантов объектов.

3) выбрав команду меню Вставка – Диаграмма.

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

Таблица 4

	A	B	C	D
	Наименование
	Книжный мир
	Дом книги
	Техническая книга

 

Для примера, построим гистограмму (рисунок 2), используя таблицу 4.

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

Выбрав диапазон данных A1:D4, щелкните на кнопке Мастер диаграмм на панели инструментов Стандартная. Далее следуйте пошаговым указаниям и выполняйте следующие действия: 1 шаг – выберите тип диаграммы Гистограмма на вкладке Стандартная; 2 шаг – установите флажок в строках в опции Ряды на вкладке Диапазон данных; 3 шаг – на вкладке Легенда активизируйте переключатель Добавить легенду и установите флажок справа в опции Размещение; 4 шаг – установите опцию Поместить диаграмму на листе имеющемся. Подтвердите ваши установки нажатием на кнопку Готово. В результате вы увидите следующую диаграмму

 

Рисунок 2. Гистограмма

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

Редактирование диаграмм

 

Созданные диаграммы можно редактировать. Вы можете, например, вставить название к созданной выше диаграмме. При создании диаграмм используется два вида текстов: связанный и свободный. Связанный текст – это заголовки элементов диаграммы, а свободный вы можете вводить по своему желанию. Чтобы вывести связанный текст, выберите команду Параметры диаграммы из контекстного меню для области диаграммы и на вкладке Заголовки в окно Название диаграммы введите текст «книги», в окно ОсьХ(категорий) – текст «года», а в окно Ось У(значений) – текст «количество».

Свободный текст вы можете просто набрать, используя кнопку Надпись на панели Рисование, но при этом на диаграмме не должен быть выделенным ни один объект. Введите как свободный текст надпись «данные за 3 года». Вы можете с помощью мыши изменять размеры рамки вокруг текстового поля по своему усмотрению, а также форматировать текст (шрифт, начертание, размер), используя команду меню Формат – Выделенный объект.

Можно обрабатывать отдельные компоненты диаграмм, предварительно выделив их (например, ось Х в нашем случае). Выделить можно диаграмму целиком или только область построения диаграммы, т.е. координатные оси и данные. Можно отдельно выделять данные диаграммы (в нашем случае построенные столбцы) и легенду. В зависимости от того, какие компоненты диаграммы вы выделите, вы сможете вызвать разные диалоговые окна.

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

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

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

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

 

Рисунок 3. Надписи к гистограмме

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

Вы можете более наглядно оформить свою диаграмму, проведя линии сетки (рисунок 4). Для этого выделите область диаграммы, выберите команду контекстного меню Параметры диаграммы и в одноименном диалоговом окне на вкладке Линии сетки установите соответствующие опции. Например, установите флажки Основные линии в опциях Ось Х(категорий) и Ось У(значений). С помощью вкладки Оси этого окна вы можете включать или выключать изображение осей диаграммы. Отключите, например, изображение оси Х. Но, чтобы при этом не потерять значения категорий, установите на вкладке Подписи данных флажок в опции Включить в подписи - имена категорий.

В результате вы можете получить следующий вид диаграммы:

 

Рисунок 4. Отформатированная диаграмма

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

Для редактирования легенды вызовите диалоговое окно Формат легенды двойным щелчком или выделите легенду и вызовите команду меню Формат – Выделенная легенда. На вкладке Вид этого окна рассмотрите опции Рамка и Заливка. Для форматирования каждого текстового элемента легенды отдельно выделите легенду, а затем дважды щелкните на нужном элементе внутри легенды.