Ссылки - адреса ячеек в формулах

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

Относительная ссылка (относительный адрес) в формулах воспринимается, как место расположения ячейки относительно формулы.

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

Рассмотрим пример, пусть в ячейку С5 введена формула =А1+В3

На следующем рисунке показан результат копирования формулы из ячейки С5 в ячейку С7.

На следующем рисунке показан результат копирования формулы из ячейки С5 в ячейку D6.

Абсолютная ссылка (абсолютный адрес) при копировании не меняется, в абсолютной ссылке необходимо перед именем столбца и перед номером строки ввести символ $. Например: $B$4.

Всмешанной ссылке (смешанном адресе) символ $ ставится либо перед именем столбца (при копировании не меняется имя столбца), либо перед именем строки (при копировании не меняется номер строки).

Например: B$2 (не будет меняться номер строки), или $В5 (не будет меняться имя столбца). На рисунках показаны примеры:

Задание 13.1.Перейдитена лист Работа 1и очистите блоки ячеек А14:С16 и F10:H12.

Задание 13.2.Во всех формулах блока А10:С12 замените относительные ссылки на абсолютные, то есть формула =СУММ(G4:G6) в ячейке В10 будет заменена на формулу =СУММ($G$4:$G$6) и т.д.

Задание 13.3.Вновь выполните копирование блока А10:С12 в произвольную область.

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

Защита ячеек

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

Задание 14.1.Защитите от изменений информацию в ячейках А1:G6 на листе Работа1.

Для этого:

1) выделите весь лист и выполните команду Формат – Ячейки,выберите вкладку–Защита;

2) снимите флажок с параметра Защищаемая ячейкаи нажмитеОК;

3) выделите блок А1:G6;

4) вновь выполните команду Формат – Ячейки,выберите вкладку–Защита;

5) поставьте флажок на параметр Защищаемая ячейка,нажмитеОК;

6) выполнить команду Сервис – Защита – Защитить лист

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

Задание 14.2.Сохраните файл под прежним именем.

Задание 14.3. Выйдите из программы.

Деловая графика в Еxcel

Построение диаграмм

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

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

Задание 15.1. Запустите Excel. Откройте свой файл. Откройте лист Работа 1.

Задание 15.2.Снимите защиту с листа. Для этого выполните командуСервис – Защита - Снять защиту листа.

Задание 15.3.Постройте диаграмму, которая даст графическое представление данных по дневному обучению. Для этого:

1) выделите блок А3:F6;

2) выполните команду Вставка – Диаграмма или нажмите кнопку на панели инструментов . Откроется окно «Мастер диаграмм», который позволяет по шагам построить нужную диаграмму.

3) выберите тип диаграммы и нажмите кнопку Далее.

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

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

Вкладка «Ряд» – позволяет убирать ряды данных или добавлять новые ряды даже из других листов и книг.

5) нажмите кнопку Далее;

6) откроется окно «Шаг 3 из 4», в котором имеются:

Ø вкладка «Заголовки» – здесь вводятся название диаграммы, оси Х и оси У.

Ø вкладка «Оси» – вывод или скрытие главных осей диаграммы.

Ø вкладка «Сетка»- отображение линий сетки на основе выбранных параметров.

Ø вкладка «Таблица данных» - отображение данных непосредственно на диаграмме (не для всех типов диаграмм)

Ø вкладка «Легенда» – добавление легенды в область диаграммы.

7) введите названия заголовков диаграммы;

8) нажмите кнопку Далее;

9) откроется окно Шаг 4 из 4, в котором надо указать местоположение диаграммы (выберите «на имеющемся»);

10) нажмите Готово.

На Рабочем листе появится диаграмма:

Задание 15.4. Постройте диаграмму, по которой можно сравнить число студентов дневного обучения на каждом факультете на 01.10.2000г. Для этого выделите блок А4:А6 (надписи для оси Х) и блок G4:G6.

Ряды данных укажите в столбцах, уберите легенду.

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

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

Задание 16.1. Измените тип последней диаграммы на круговую (в контекстном меню команда Тип диаграммы).

Задание 16.2. Сохраните файл.

Задание 16.3. Создайте в своем файле четыре новых листа Задание1, Задание2, Задание3 и Задание4 для выполнения практических заданий.