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

Корректировка ссылок при копировании ячеек выполняется по умолчанию. Такие ссылки называются относительными. При изменении позиции формулы изменяется и ссылка.

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

На рисунке 5.3 показан пример вычисления налога по формуле:

Налог = Стоимость * НДС

Ссылка на ячейку А2 должна оставаться неизменной при копировании формулы, то есть быть абсолютной – $A$2.

	A	B	C	D
	НДС
	5%
	Цена	Количество	Стоимость	Налог
	12,25		281,75 р.	=$A$2* С4
	38,56		616,96 р.	=$A$2* С5
	4,6		115,00 р.	=$A$2* С6
	4,76		166,60 р.	=$A$2* С7

Рис. 5.3

Чтобы определить ссылку как абсолютную, нужно после щелчка на ячейке (в данном примере это ячейка – А2) нажать клавишу < F4 >. Адрес ячейки в формуле автоматически дополнится символами «$» перед именем столбца и номером строки.

Смешанные ссылки

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

A$2 – фиксированная строка.

$A2 – фиксированный столбец;

Тип адресации (относительная, абсолютная, смешанные) меняется повторными нажатиями клавиши < F4 >при вводе адреса ячейки в формулу или при редактировании формулы.

Имена ячеек для абсолютной адресации

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

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

Присвоение имени текущей ячейке (диапазону):

Первый способ:

1. щелкнуть в поле адреса строки формул, ввести имя;
2. нажать клавишу <Enter>.

Второй способ:

1. выполнить команду Вставка Имя Присвоить;
2. в диалоговом окне ввести имя.

Это же диалоговое окно можно использовать для удаления имени, однако следует иметь в виду, что если имя уже использовалось в формулах, то его удаление вызовет ошибку (сообщение – «# имя?»).

Просмотр зависимостей

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

Команда меню Сервис Зависимости формул позволяет увидеть на экране связь между ячейками.

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

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

Все зависимости в таблице изображаются стрелками. Для удаления стрелок служит команда Сервис Зависимости формул Убрать все стрелки.

При необходимости просмотра многих зависимостей удобно отобразить панель инструментов Зависимости командой Сервис Зависимости формул Панель зависимостей.

Редактирование формул

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

Изменение ссылки в формуле:

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

Изменение типа адресации:

• выделить адрес ячейки двойным щелчком;
• нажать клавишу < F2 >.

Для подтверждения внесенных изменений использовать клавишу <Enter> или кнопку Ввод в строке формул; для отмены изменений – клавишу < Esc > или кнопку Отмена в строке формул.

 

 

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

Вызывается функция, как правило, с помощью Мастера функций (вкладка «Вставка») или кнопка fx в центре строки формул.

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

Например:

 

Существуют различные типы аргументов: число, текст, логическое значение (ИСТИНА и ЛОЖЬ), массивы, значение ошибки (например #Н/Д), или ссылку на ячейку (блок ячеек). В качестве аргументов используются константы формулы или функции. В каждом конкретном случае необходимо использовать соответствующий тип аргумента.

Диалоговое окно Мастер функций облегчает ввод функций при создании формул, содержащих функции (рис.4).

 

Рис.4. Окно мастера функций

 

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

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

 

 

Приведенная функция обеспечивает проведение расчета при условии:

«если среднее значение блока С5:С9 больше 40, ТО суммируются данные блока F2:F5; ИНАЧЕ 0»

 

Работа с диаграммами.

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

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

Гисстограмма, Линейчатая, График, Круговая, Точечная, С областями, Кольцевая, Лепестковая, Поверхность, Пузырьковая, Биржевая, Коническая, Цилиндрическая, Пирамидальная.

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

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

· Выделите ячейки, содержащие данные, которые должны быть отражены на диаграмме.

· Если необходимо, чтобы в диаграмме были отражены и названия строк или столбцов, выделите также содержащие их ячейки.

· Нажмите кнопку Мастер диаграмм.

· Следуйте инструкциям Мастера.

Работа с Мастером диаграмм состоит из 4-х шагов. (окон). На первом шаге выбирается тип и вид диаграмм. На втором шаге указывается источник исходных данных., причем вкладка Ряд включает в себя две кнопки: Удалить и Добавить, с помощью которых можно добавить или удалить ряды данных. На третьем шаге задаются параметры диаграммы: Легенда (имена рядов данных ), Заголовки, Подписи данных, Таблицы данных, Оси, Линии сетки. На четвертом шаге задается место размещения диаграммы: на имеющемся или на отдельном листе.

. Создание диаграмм из несмежных диапазонов.

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

· Удерживая клавишу Ctrl, выделите необходимые дополнительные группы ячеек.

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

· Нажмите кнопку Мастер диаграмм.

· Следуйте инструкциям Мастера.

 

20.3. Создание стандартной диаграммы за один шаг.

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

· Отобразите панель инструментов, на которую требуется добавить кнопку.

· Выберите команду Настройка в меню Се рвис, а затем – вкладку Команды.

· Выберите нужную категорию кнопки из списка Категории.

· Перетащите нужную команду или макрос из списка Команды на панель инструментов.

 

Характеристики диаграмм.

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

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

График отражает тенденции изменения данных за равные промежутки времени.

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

Точечная диаграмма отображает взаимосвязь между числовыми значениями в нескольких рядах и представляет две группы чисел в виде одного ряда точек в координатах XY. Эта диаграмма отображает нечетные интервалы (или кластеры) данных и часто используется для представления данных научного характера. При подготовке данных следует расположить в одной строке или столбце все значения переменной X, а соответствующие значения переменной Y – в смежных строках или столбцах.

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

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

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

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

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

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

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

 

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

ЛАБОРАТОРНЫЕ РАБОТЫ