Формулы Excel. Абсолютная и относительная ссылка на ячейку

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

	A	B	C
	Товар	Цена в евро	Цена в рублях
	Духи
	Шампунь
	Курс евро	34,5

Для расчета «цены в рублях» «духов» необходимо умножить значение из ячейки В2 на значение из ячейки В4 (как ранее упоминалось, каждая ячейка таблицы именуется по двум индексам наименованию образующего столбца и строки). В Excel эта задача реализуется посредством использования формулы. В ячейку С2 необходимо ввести =В2*B4. Причем необязательно производить ввод с клавиатуры имен ячеек, достаточно просто выполнить щелчок мышью по ячейке В2 и В4. В данном случае знак = означает, что в ячейке С2 содержится формула. В Excel существует прекрасная возможность автозаполнения ячеек формулой, т.е необходимо воспользоваться маркером автозаполнения в ячейке С2 и произвести заполнение формулой на ячейку С3. Если это осуществить, то результат в ячейке С3 будет ноль! Такой результат легко объяснить, если в строке формул посмотреть на получившуюся формулу =В3*В5 (в строке формул отображается именно сама формула, а не результат). Дело в том, что в формуле в ячейке С2 ссылки на ячейки В2 и В4 были относительные (относительно ячейки, где располагается формула), т.е. ссылка на ячейку слева от формулы (В2) и на ячейку располагающуюся на 2 ячейки вниз и на одну ячейку влево от формулы (В4). Теперь, когда формула содержится в ячейке С3 в этой формуле указывается ссылка на ячейку слева (В3) и на ячейку располагающуюся, на 2 ячейки вниз и на одну ячейку влево от формулы (В5), но в В5 содержится ноль и соответственно результат будет ноль. Понятно, что в данном примере при заполнении формулой ячейки С3 результат должен быть =В3*В4, т. е при заполнении формулой ссылка на ячейку с В2 должна замениться на В3, но ссылка на ячейку В4 должна оставаться неизменной при копировании формулы этого можно достичь, если использовать абсолютную ссылку на ячейку В4. Для этого необходимо указать знак $ перед наименованием столбца и строки, т. е. правильно формула в ячейке С2 должна выглядеть, так =В2*$B$4. Таким образом, если вы производите последовательное умножение (деление, вычитание, прибавление) содержимого ряда ячеек на некоторое значение из определенной ячейке, то на эту ячейку обязательно необходимо задать абсолютную ссылку.

Примечание1: В формулах могут использоваться арифметические операции (+,-,*,/), логические (<,<=,>,=>,=, не =), а также функции. Понятие функции будет рассмотрено позже.

Примечание2:Кроме абсолютной и относительной ссылки существуют их комбинации, когда фиксируется ссылка только на столбец (например $В4) или строку (например В$4). Так, например, в данном примере в формуле =В2*$B$4 на ячейку В4 можно задать смешанную ссылку В$4, так как столбец при заполнении формулой и так будет неизменным он располагается слева от формулы.

Внешние ссылки в формулах

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

Для поддержания правильности использования внешних ссылок используется команда меню Правка ► Связи, которая открывает диалоговое окно Связи (рис. 5.28). Обновление может осуществляться автоматически при открытии текущей кни­ги или по запросу. Для автоматического обновления внешних связей следует выполнить команду меню Сервис ► Параметры, и на вкладке общие выбрать флажок «автоматически обновлять связи при открытии».

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

Диаграммы

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

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

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

После нажатия на кнопку мастера диаграмм требуется определиться с выбором типа и вида диаграммы. В MS Excel 2000 включено 14 стандартных и 21 нестандартных типов диаграмм, поэтому при выборе нужного вам типа диаграммы просмотрите их. Назначение типа диаграммы поясняется в правой нижней части окна мастера диаграмм.

Ниже перечислены основные типы диаграмм и указано их назначение:

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

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

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

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

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

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

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

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

Точечная диаграмма. Диаграммы этого класса еще называют диаграммами разброса. Они могут наглядно показать зависимо­сти или связи между переменными. Хотя этот тип диаграмм во многом напоминает тип «График», его стоит использовать в том случае, если нет необходимой зависимости между двумя рядами данных, которую нужно отобразить на координатной плоскости.

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

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

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

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

С помощью одного из дополнений MS Excel ˗ Диспетчера карт (Micro­soft Map) — можно создавать внедренные географические карты, на которых можно, например, показывать объемы продаж по странам, отображать стати­стику населения или другие данные, характеризующие распределенные гео­графические области.

Примечание 1:Нажав на кнопку «Просмотр результата» можно увидеть как будет выглядеть диаграмма. На лю­бом шаге вы можете нажать кнопку «Готово», в результате чего построение диаграммы завершится.

Примечание 2: В таких типах диаграмм как Линейчатая, Гистограмма, С областями, График, Круговая, Поверхность существуют объемные разновидности диаграмм, которые позволяют представить данные в перспективе. Причем в объемной диаграмме имеется 3 оси, что позволяет, например, в диаграмме типа Гистограмма отобразить наглядно сразу несколько рядов данных по осям X и Y.

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

По умолчанию MS Excel считает, что ряды данных расположены по стро­кам. При необходимости можно изменить эту установку и считать, что ряды данных расположены по столбцам. Для этого установите переклю­чатель «Ряды в столбцах». Для формирования рядов данных используется втораястраница рассматриваемого диалогового окна. На этой странице можно задать название ряда в поле «Имя»,непосредственно введя его с клавиатуры или выделив на листе, временно свернув диалоговое окно. В поле «Значения» находятся численные данные на основе которых строится диаграмма. Если необходимо для ввода этих данных также удобнее всего воспользоваться кнопкой свертывания окна, после чего можно выделить диапазон данных непосредственно на рабо­чем листе. В поле Подписи оси X вводятся единицы измерения оси X на диаграмме. После этого можно нажать на кнопку «Далее»

Третий шаг работы мастера состоит в выборе оформления диаграммы. На вкладках окна мастера задаются:

• название диаграммы, подписи осей (вкладка «Заголовки»);

• отображение и маркировка осей координат (вкладка «Оси»);

• отображение сетки линий, параллельных осям координат (вкладка «Линии сетки»);

• описание построенных графиков (вкладка «Легенда»);

• отображение надписей, соответствующих отдельным элементам данных на графике (вкладка «Подписи данных»);

• представление данных, использованных при построении графика, в виде таблицы (вкладка «Таблица данных»).

В зависимости от типа диаграммы некоторые из перечисленных вкладок могут отсут­ствовать. Когда вы закон­чите формировать параметры диаграммы, нажмите кнопку «Далее».

Задание:На практическом занятии просмотреть все вкладки на 3-ем шаге построения диаграммы и определить их назначение. Например, можете удалить легенду, сбросив флажок «Добавить легенду» на вкладке «Легенда». На вкладке «Заголовки» можете добавить название диаграммы. Открыв вкладку «Подписи данных», добавьте подпи­си к данным.

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

Итак на четвертом шаге в переключателе «Поместить диаграмму на листе:» предлагается два варианта:

□ Установить переключатель «имеющемся»и выбрать лист из списка.

□ Установить переключатель «отдельном» и указать имя, если стандартное имя «Диаграмма 1» не подходит.

Необходимо сделать выбор и нажать кнопку «Готово», тогда появится полностью за­вершенная диаграмма.

После того как диаграмма построена, можно также изменять размеры диаграммы, перемещать текст, редактировать любые ее элементы, а также изменять сам тип диаграммы для этого выполнив щелчок правой кнопкой в области построения диаграммы (область построения диаграммы представляет собой прямо­угольник, где непосредственно отображается диаграмма) и в контекстном меню выбрать пункт «Тип диаграммы…».

Перемещение объектов диаграммы осуществляется следующим образом:

■ выделить объект, который требуется переместить. При этом вокруг объекта появляется окаймление из черных квадратов:

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

Для изменения размеров поля, на котором находится какой либо из объектов диаграммы, необходимо выполнить следующие действия:

■ выделить требуемый объект;

■ переместить указатель мыши на один из черных квадратов на границах объекта, при этом белая стрелка указатели превратится в черную двунаправленную стрелку;

■ нажать левую кнопку мыши и перетащить границу объекта в нужное место.

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

Примечание:При создании объемных диаграмм пользователь может изменять угол обзора диаграммы. Это необходимо, например, если в объемной диаграмме с областями первый ряд данных закрывает обзор представленных на заднем плане других рядов данных. С помощью изменения угла обзора можно добиться представления на экране и других рядов данных. Изменение угла обзора достигается за счет поворотов диаграммы вокруг отдельных осей. Это можно сделать непосредственно в диаграм­ме с помощью мыши или в диалоговом окне «Формат трехмерной проекции». Для того чтобы иметь возможность изменять обзор с помощью мыши, следует выполнить щелчок мышью на стенке объемной диаграммы. После этого и диаграмме появляются черные квадраты, обозначающие некоторые из углов параллелепипеда, опи­санного вокруг диаграммы. Поместите курсор мыши на одном из квадратов и перемещайте его при нажатой левой кнопке мыши. На экране будет представлен контур параллелепипеда без вписанной в него диаграммы. Изменением позиции одного из углов этого геомет­рического тела (соответственно изменяется форма и положение его в пространстве) можно добиться наилучшего обзора. Отпустите левую кнопку мыши. Найденный угол обзора будет зафиксирован, и диаграмма будет снова представлена на экране уже в новом виде. Можно задать изменение параметров обзора и в диалоговом окне «Формат трехмерной проекции». Для представления на экране этого диалогового окна следует выбрать команду «Объемный вид» из контекстного меню. Здесь пользователь может непосредственно ввести отдельные значения или задать изменение обзора путем манипулиро­вания кнопками.

Для редактирования (форматирования) любого элемента диаграммы можно:

1) выполнить по нему двойной щелчок левой кнопкой мыши.

2) вызов контекстного меню для диаграммы с помощью правой кнопки мыши. Тогда в появившемся списке команд следует вы­брать команду форматирования текущего объекта.

3) вы­зов соответствующего диалогового окна из панели инструментов Диаграммы (список «элементы диаграммы» и кнопка «формат …»

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

Так, например, для редактирования области построения диаграммы вызвать для нее кон­текстное меню и выбрать в нем команду «Формат области по­строения». После этого в открывшемся диалоговом окне «Формат области по­строения» можно задать вид рамки диаграммы, цвет заливки области построения диаграммы, шрифт которым будут отображаться надписи на диаграмме и ряд свойств объектов диаграммы.

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

Например (см. рисунок).

В диалоговом окне «Формат оси» вкладка«Шкала» «минимальное значение»задает минимальное значение на шкале оси Y, а «максимальное значение» — максимальное. Если вы хотите представить в диаграмме только определенный интервал из области значений, введите значения нижней и верхней границ интервала. Шкала значений оси Y начинается с 0 и заканчивается значением, которое вычисляется следующим образом:максимальное значение из области данных округляется до ближайшегобольшего числа, кратного цене деления. С помощью задания значений «цена основных делений» и «цена промежуточных делений» устанавливаются интервалы между засечками на шкале. В случае, если разность между наибольшим и наименьшим значе­ниями очень большая, целесообразно использовать логарифмическую шкалу. Если, например, данные только одного ряда существенно отличаются от остальных значений, то с помощью использования логарифмической шкалы можно улучшить общий вид диаграммы. Чтобы задать применение логарифмической шкалы в диаграмме, следует установить в этом диалоговом окне опцию «Логарифмическая шкала». Здесь же в закладке «Шкала»можно задать начало координат. Для этого следует ввести нужное значение в поле «ось X пересекается в значении».По умолчанию координаты точки пересечения осей – (0,0). Путем задания положительного или отрицательного значения можно «передвинуть» ось X по отношению к оси Y. При выделенной опции «Обратный порядок значений»диаграмма будет представлена в зеркальном отображении относительно оси X: положительные значения на оси Y будут располагаться ниже оси X, а отрицатель­ные – выше.

Параметры масштаба для оси X отличаются, в зависимости от выбранного типа диаграммы, от параметров масштаба для оси Y. Например, при построении гистограммы на оси X будут отображены не числовые значения, а категории. Соответственно, закладка «Шкала» при форматировании оси X приобретает несколько другой вид. В этой панели пользователь может установить параметры разме­щения осей и категорий. Например, можно «передвинуть» ось Y относительно оси X с помощью «Пересечение с осью Y в категории номер», перед которой будет представлена ось Y.

Параметры засечек можно установить в закладке «Вид». В закладке «Вид» пользователь может задать вид засечек. Здесь также можно установить параметры стиля, цвета и толщины оси.

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

Примечание 2: При обработке результатов измерений достаточно часто воз­никает вопрос о тенденции развития или изменения. В Excel этот вопрос решается при помощи определения тренда. Линии тренда графически иллюстрируют тенденцию изменения рядов данных. Они обычно используются в тех случаях, когда нужно построить диаграммы для задач прогнозирования и экстраполяции (так на­зываемых задач регрессионного анализа). С помощью регресси­онного анализа можно продолжить линию тренда вперед или на­зад, экстраполировать ее за пределы, в которых данные уже из­вестны, и показать тенденцию их изменения. Для построения ли­нии тренда следует активизировать ряд данных, после чего в спи­ске команд контекстного меню выбрать «Добавить линию трен­да…», а затем на вкладке «Тип» соответствующего диалогового ок­на - рисунок с требуемым трендом, например, линейным (пункт «Линейная»). Линии тренда можно проводить на гистограммах, графиках, линейчатых и XY-точках диаграммах. Нельзя строить линии тренда для рядов данных на объемных, круговых и кольцевых диаграммах. Если будет изменен тип представления группы ря­дов на один из вышеперечисленных, то соответствующие этим рядам данных линии тренда будут потеряны. В Excel можно выбрать один из пяти типов экстраполяции; линейный, полиномиальный, логарифмический, экспоненциаль­ный или степенной. Тип выбранной экстраполяции задает способ вычислений линии тренда. В зависимости от используемых дан­ных некоторые типы экстраполяции могут оказаться надежнее других с точки зрения оценки результатов сделанных прогнозов. Таким образом, может оказаться полезным проделать несколько различных экстраполяции, чтобы посмотреть, какой из типов лучше всего подходит в данной ситуации.

Функции

Microsoft Excel содержит множество типовых или встроенных формул известных, как функции. Функции могут быть использованы для выполнения простых или сложных вы­числений или выполнения определенного набора операций над заданным диапазоном величин в рабочих книгах. Все встроенные функции разделены на катего­рии: Дата и время, Логические, Математические, Проверка свойств и значений, Работа с базой данных, Ссылки и массивы, Статистические, Текстовые, Финансо­вые функции определенного раздела предназначены для выполнения определенных вы­числений или набора операций.

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

В функциях могут использоваться ссылки на ячейки или диапазоны (например, В1:ВЗ), имена ячеек или диапазонов (например, СБЫТ) и/или численные величины.

Каждая функция состоит из следующих трех элементов:

1. Знак равенства (=) показывает, что далее следует функция (формула).

2. Название функции, например СУММ, определяет операции, которые будут выполняться над аргументами.

3. Аргумент, например (Е1:Н1), показывает адреса ячеек тех величин, с которыми будет оперировать функция. Часто аргументом бывает диапазон ячеек, но также в качестве аргументов можно использовать числа, текст, логические зна­чения, массивы, значения ошибок или ссылки. Аргументы могут быть как константами, так и формулами. В свою очередь, эти формулы могут содер­жать другие функции. Функции, являющиеся аргументом другой функции, называются вложенными. В формулах MS Excel можно использовать до семи уровней вложенности функций.

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

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

Наиболее часто используемой функцией является функция суммирования [СУММ;, которая используется для сложения чисел в диапазоне ячеек. Для нее на панели инструментов есть отдельная кнопка.

Microsoft Access