Относительная и абсолютная адресация

В Excel применяется относительная и абсолютная адресация ячеек.

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

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

Для указания абсолютной адресации вводится символ $. Различают два типа абсолютной адресации: полная и частичная.

Полный абсолютный адрес указывается, если при копировании формулы адрес ячейки не должен меняться. Для этого символ $ ставится перед наименованием столбца и номером строки, например: $B$5; $D$12.

Частичная абсолютная адресация указывается, если при копировании формулы не меняется номер строки или наименование столбца. При этом символ $ в первом случае ставится перед номером строки, а во втором – перед наименованием столбца: B$5; D$12.

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

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

3. Расчертить таблицу. Расчертить таблицу проще всего командой меню Формат®Автоформат, предварительно выделив всю таблицу. В списке форматов можно выбрать надлежащее оформление и щелкнуть на ОК.

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

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

3. Уточните диапазон данных и где они размещены (в строках или столбцах).

4. Введите общий заголовков диаграммы и подписи осей, если они будут присутствовать в диаграмме.

5. Определите, как разместить диаграмму: на отдельном листе или вместе с таблицей.

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

Готовую диаграмму можно отредактировать. Для этого надо

2. один раз щелкнув по ней (выделенная диаграмма отмечена черными квадратиками по углам).

3. теперь ее можно удалить (Delete), двигать мышью по листу в нужное место листа, уменьшать или растягивать за черные квадратики.

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

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

При работе с базой данных представляют интерес три основные группы задач:

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

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

2. В каждой колонке следует использовать один и тот же тип данных, т.е. не смешивать в одной колонке числовые и текстовые данные.

3. Не рекомендуется отделять строку с именами полей от первой записи в базе данных пустыми строками.

4. В базе данных не должно быть одинаковых имен полей, желательно, чтобы имя поля состояло из одного слова длиной не более 15 символов.

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

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

3. В открывшемся диалоговом окне укажите поле, по которому будет выполняться первичная сортировка, выбрав его из списка, т.е. Отдел, и поле, по которому далее упорядочиваются записи, т.е. Оклад. Укажите порядок сортировки (по возрастанию или по убыванию) и щелкните OK. Таблица будет отсортирована.

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

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

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

Например, если нам надо найти работников проектного отдела, имеющих оклад больше 300, во-первых, в списке Отдел выберем проект, во-вторых, в списке Оклад выберем Условие. и в открывшемся диалоговом окне введем условие отбора:

В результате фильтрации в БД будут выделены строки, удовлетворяющие критериям:

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

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

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

Например, если мы хотим найти сотрудников, родившихся до 1960 года и имеющих оклад меньший или равный 400, надо сформировать следующий блок условий отбора:

Далее исполним команду Данные®Фильтр®Расширенный фильтр. Откроется диалоговое окно, в котором укажем

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

1. для выделения строк БД, содержащих текстовые данные, включающие некоторый фрагмент, требуется в качестве условия указать этот фрагмент и символ «*». Звездочка заменит собой любое число символов. Для замены одного символа служит «?».

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

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

После прочтения теоретической части, выполните следующие задания:

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

2. Выполнить вычисление суммы по всем столбцам (строка Итого).

3. Вставить в таблицу дополнительные столбцы Сдали и Процент сдавших после столбца Сдавало.

4. Для одной клетки таблицы вычислить значение в столбце Сдали по формуле как разность между Сдавало и Сдали на “2”.

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

6. Определить для одной клетки таблицы Процент сдавших как отношение Сдали к Сдавало. Результат перевести в проценты.

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

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

где K5, K4, K3, K2 – количество пятерок, четверок, троек и двоек соответственно (использовать адреса ячеек). Выполнить копирование этой формулы для прочих ячеек этого столбца.

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

9. Выполнить центральное выравнивание числовых данных таблицы.

11. Расчертить таблицу. Выполнить предварительный просмотр.

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

S1 – площадь государства в условных единицах до некоторого правителя,

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

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

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

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

3. Добавить столбец Площадь, добавленная правителем (тыс.км.кв.):

где Sp – площадь, добавленная правителем в тыс. квадратных километрах,

4. Построить диаграмму «Правители – присоединенные земли».

5. Построить график «Изменение площади государства Российского». По горизонтальной оси графика — годы, по вертикальной — площадь в тыс.кв.км.

2. Введите данные первого года (1995): Объем продаж, Цена, Расходы.

3. Введите Прогнозные допущения: Рост объема продаж и Рост цен.

4. В ячейку B5 запишите формулу для вычисления дохода:

5. В ячейку B7 запишите формулу для вычисления прибыли:

Объем продаж(1996) = Объем продаж(1995) * (1+%Роста объема продаж).

При записи адреса ячейки Рост объема продаж использовать абсолютный адрес.

Для ячейки Рост цен также использовать абсолютный адрес.

Для вычисления Доход(1996) содержимое ячейки Доход(1995) копируется.

Пересчет остальных параметров из столбца B в столбец C выполняется аналогичным образом.

7. Столбцы D, E, F заполняются копированием формул, содержащихся в столбце С.

Заполненная таблица должна выглядеть следующим образом:

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

1. Заполните ведомость для расчета заработной платы. Процент надбавок к окладу определяется из расчета: 5%, если стаж работы меньше 3 лет; 15%, если стаж от 3-х лет и больше. Исходная таблица:

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

Заполненная таблица должна выглядеть следующим образом:

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

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

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

В результате исходная таблица преобразуется в следующую таблицу:

Создать базу данных подержанных автомобилей по образцу, всего 10-12 записей.

1. недорогие автомобили, имеющие пробег меньше заданного;

2. все автомобили “Жигули”, выпущенные после заданного года.

1. автомобили, имеющие дату выпуска, попадающую в заданный диапазон;

2. автомобили, имеющие цену меньше заданной или имеющие пробег меньше заданного.

Результаты поиска поместите в отдельной области таблицы.

Раздел: Информатика, программирование
Количество знаков с пробелами: 25910
Количество таблиц: 0
Количество изображений: 21

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

Практическая работа. «Microsoft Excel 2007. Абсолютная и относительная адресация»

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

Практическая работа №3 MS Excel. Типы данных, абсолютная и относительная адресация. Стандартные функции Excel.

Для выделения одного листа необходимо щелкнуть его ярлык. Для выделения нескольких смежных листов необходимо щелкнуть 1-й из них, а затем, прижав клавишу Shift, щелкнуть последний. Для выделения произвольной группы листов необходимо прижать кл. Ctrl и щелкать по необходимым листам.

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

Для вставки нового листа необходимо выделить лист, перед которым надо вставить новый лист и Выбрать Вставка + Лист.

Для удаления листа необходимо его выделить и выбрать Правка + Удалить либо щелкнуть правой кнопкой мыши на ярлыке листа и выбрать команду «Удалить».

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

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

Автозаполнение — это функция, которая помогает быстрее вводить данные. При перетаскивании маркера заполнения ячейки может происходить не только копирование одних и тех же значений. Например, введите в какую-либо ячейку число 1, а в соседнюю справа от нее — число 2. Затем отметьте обе ячейки и протяните маркер заполнения вдоль строки. Появится ряд значений: 1,2,3,4…

Ввод формул всегда начинается со знака равенства =. Формулы представляют собой выражения, по которым выполняются вычисления на рабочем листе. В качестве аргументов формулы обычно используются значения ячеек, например: =A1+B1

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

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

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

Пример. В ячейку A1 внесем число 5, в ячейку B2 – число 7. Вычисление по формуле в ячейке B1 даст число 6, а в ячейке A2 – число 11. При копировании формулы из ячейки B1 в C1 изменяется заголовок столбца и в ячейке C1 будет значение 7. В ячейке A3 будет число 18.

Чтобы ввести формулу с клавиатуры, надо выполнить следующие действия:

1. Щелкнуть ячейку, в которую необходимо ввести формулу

4. Нажать Enter или щелкнуть «галочку» в строке формул. Excel вычислит результат.

Так как одной из наиболее часто употребляющихся функций является СУММ, в Excel предусмотрен быстрый способ ее ввода:

1. Выделить ту ячейку, в которую необходимо вставить сумму. Лучше, если она расположена в конце строки или столбца данных — это поможет Excel «догадаться», какие ячейки необходимо просуммировать.

2. Щелкнуть кнопку «Автосумма» на стандартной панели инструментов. В выделенную ячейку будет введена функция СУММ и адрес диапазона левее или выше ячейки.

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

4. Нажать клавишу Enter либо щелкнуть кнопку Enter в строке формул.

Можно просто дважды щелкнуть кнопку «Автосумма» и функция СУММ будет сразу вставлена в выделенную ячейку.

1. Запустите Microsoft Excel (Пуск/Программы/ Microsoft Excel)

2. Сохраните созданную рабочую книгу в папке с именем Вашей группы под именем «ПР№8_Ваша фамилия»

1.1 Переименуйте первый лист Вашей рабочей книги, присвоив ему имя «Автозаполнение» (для этого щелкните правой кнопкой на ярлычке листа и вместо текста «Лист1» внесите текст «Автозаполнение»)

1.2 В ячейку А1 введите любое число, в ячейку В1 – любое слово, а в ячейку С1 введите 1995. Выделите блок А1:С1 и протащите его за маркер заполнения на 10 строк вниз. Проанализируйте результаты.

1.3 В ячейку К9 введите слово Среда. Выделите ячейку. Потяните ее за маркер заполнения на 6 строк вниз. Снова выделите ячейку К9 и протащите ее за маркер на 6 столбцов вправо. Повторите операцию влево и вверх. Проанализируйте результаты.

1.4 В ячейку Е19 введите число 1. В ячейку Е20 – число 2. В ячейку F19 введите число 3. Выделите блок из двух ячеек Е19:Е20 и протащите его за маркер вниз. Снова выделите блок Е19:Е20 и протащите вверх. Выделите блок E19:F19 и протащите за маркер направо, затем налево. Проанализируйте результаты.

1.5 В ячейку А31 введите число 25. В ячейку В31 – число 50 и протащите блок ячеек А31:В31 за маркер на несколько столбцов вправо.

1.6 В ячейку G38 введите Январь. Выделите ее и протащите на 5 столбцов вправо и влево. Проанализируйте результаты.

Упражнение №2.Ввод формул. Мастер функций. Относительная и абсолютная адресация.

1 Дважды щелкните на ярлычке второго рабочего листа и дайте этому рабочему листу имя «Функции».

2 Сделайте текущей ячейку А1 и введите в неё заголовок «Результаты измерений».

3 Введите числа от 2 до 9 в последовательные ячейки столбца А (используя маркер автозаполнения), начиная с ячейки А2 по А9.

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

14 Убедитесь, что формулы автоматически модифицируются так, чтобы работать со значением ячейки в столбце А текущей строки.

15 Сделайте текущей ячейку В10 и нажмите кнопку «Автосумма» на стандартной панели инструментов Excel, протащите указатель мыши по диапазону А2:А9 а затем нажмите Enter. В строке формул проверьте, какая формула (а точнее, функция) теперь находится в ячейке В10(Должно быть =СУММ(А2:А9)).

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

18 Введите в ячейку Е1 строку Масштабный множитель.

19 Введите в ячейку Е2 число 5. (т.к. масштабный множитель равен 5)

22 Используйте метод автозаполнения, чтобы скопировать эту формулу в ячейки столбца F, соответствующие заполненным ячейкам столбца А.

23 Убедитесь, что результат масштабирования оказался неверным. Это связано с тем, что адрес в формуле задан относительной ссылкой.

24 Щёлкните на ячейке F2,затем в строке формул. Установите текстовый курсор на ссылку Е2 и нажмите клавишу F4. Убедитесь, что формула теперь выглядит как =А2*$Е$2, и нажмите клавишу ENTER.

25 Повторите заполнение столбца F формулой из ячейки F2. Сверьте результаты.

Упражнение №3 (самостоятельное задание). Перейдите на 3 лист Вашей рабочей книги и дайте ему название «Агентство по продаже недвижимости». Создайте нижеприведенную таблицу и выполните вычисления.

1) Стоимость квартиры находится путем произведения стоимости площади квартиры на коэффициент престижности: =B2*C2*D2

2) Налог на приватизацию вычисляется по формуле: =0,01*E2

1) Вычислите самостоятельно плату агентству по продаже недвижимости (столбец G) стоимость перевода и регистрации недвижимости (столбец H)/

2) Найдите общую стоимость квартиры и услуг в у.е. путем сложения соответствующих значений в столбцах E, F, G, H.

2) В столбце J вычислите общую стоимость квартиры и услуг в тенге на основе курса доллара, занесенного в ячейку A8.

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

Как сделать относительную адресацию в excel?

В задачах, когда независимо от добавления или удаления строк и столбцов, в формуле необходимо указывать строго закрепленное поле можно использовать функцию ДВССЫЛ(). Параметром будет выступать строка с именем ячейки, например, ДВССЫЛ(«B2»).