Тема: Адресация ячеек. Относительные, абсолютные и смешанные ссылки. Использование логических выражений
Часто о одной и той же формуле нужно рассчитать не одно, а целый ряд значений при изменении одного или нескольких параметров этой формулы. В этих случаях достаточно составить формулу в одной ячейке и скопировать её в другие ячейки рабочего листа. Естественно, при этом необходимо продумать и составить формулу так, чтобы задействованные ссылки изменялись (или, наоборот, оставались неизменными) при копировании формулы (если оно будет необходимо).
Различают абсолютные, относительные и смешанные ссылки.
В обозначении относительных ссылок указывается просто буквенное обозначение столбца и номер строки, например А1.
Для обозначения абсолютных ссылок добавляется знак $, например, $A$1. Такая ссылка указывает на ячейку, местоположение которой неизменно.
Смешанная ссылка содержит комбинацию относительной и абсолютной ссылок. Закрепляется либо строка, либо столбец. Например, $A1, A$1.
При копировании ячейки с формулой содержащиеся в ней относительные ссылки корректируются в соответствии с новым местоположением формулы, а абсолютные ссылки остаются неизменными. Например, при копировании формулы =ЕХР(В1)+ $A$1из ячейки С1 в ячейки С2 и С3, формула будет преобразована в =ЕХР(В2)+ $A$1 и =ЕХР(В3)+ $A$1 соответственно.
Для быстрого изменения типа ссылки используется клавиша F4: первое нажатие – знак $ подставляется и перед номером строки и перед буквой столбца; второе нажатие – знак остаётся только перед номером строки; третье нажатие – перед буквой столбца; четвёртое нажатие убирает знак $. Введите в ячейку А1 любое число. В ячейку А2 введите формулу =А1. Затем нажимайте клавишу F4. После каждого нажатия клавиши тип ссылки будет меняться.
Задание 1. Создайте электронные таблицы учета платы за квартиру согласно образцу, представленному на рисунке 1.
Квартплата
| А | В | С | D | E | F | G | I | |
| Тариф за 1 кв.м | 25 р. | |||||||
| Срок оплаты | 10 янв. | |||||||
| Пени за 1 день | 1,5 р. | |||||||
| № квартиры | Фамилия квартиросъёмщика | площадь кв.м | сумма | дата оплаты | просрочка | штраф | итого | |
| Иванов | формула | 10 янв. | формула | формула | формула |
Рис. 1. Таблицы учёты квартплаты
1.1. Все заголовки столбцов должны быть выровнены по центру, как по горизонтали, так и по вертикали, при определении формата ячейки примените опцию переноса слов.
1.2. Столбец № квартиры: 10, 11, 12, ... 20 заполняется с помощью функции автозаполнения. Центральное выравнивание.
1.3. Столбец Фамилия квартиросъемщика: Иванов, Петров, и т.д. всего 10 фамилий.
1.4. Столбец Площадь: 70; 69,5; 69 и т. д. (каждая следующая на 0,5 м меньше предыдущей), выравнивание центральное. Используйте функцию автозаполнения.
1.5. Столбец Сумма: для каждой квартиры умножается значение из графы Площадь на значение из графы Тариф; формат рублёвый без копеек. Стоимость кв.м. для всех квартиросъёмщиков одинакова, поэтому для того, чтобы в формуле во всех ячейках графы Сумма было обращение к ячейке, содержащей тариф за 1 кв.м., записывается формула: =С6*$С$1. Знак $, добавленный к имени столбца и к номеру строки, указывает на абсолютную адресацию.
1.6. Столбец Дата оплаты: с 10 января, каждая следующая квартира произвела оплату на день позже предыдущей. Формат: Дата, полная форма. Используйте автозаполнение.
1.7. Столбец Просрочка заполняется формулой: Дата оплаты—Срок оплаты. Ссылка на ячейку, содержащую срок оплаты, должна иметь абсолютную адресацию.
1.8. Столбец Штраф заполняется формулой: Пени за 1 день*Просрочка. Формат денежный без копеек.
1.9. В столбце Итого суммируются значения из граф Сумма и Штраф, формат денежный без копеек.
1.10. Измените значение графы Тариф за 1 кв.м. с 25 руб. на 36 руб. Проверьте, изменились ли данные в столбцах Сумма, Просрочка, Итого.
Задание 2. Использование функций базы данных. Любую таблицу Excel можно преобразовать в базу данных и осуществлять те операции, которые применимы для баз данных: сортировка, поиск по запросу, создание фильтров.
2.1. Формы. Базу данных можно просматривать в режиме таблицы и в режиме формы. Нажмите левой кнопкой мыши по главной кнопке программы (верхний левый угол) и выберите Настройка панели быстрого доступа. В левой части окна выберите Настройка, а в правой части в окошке Выбрать команды из: выберите Все команды. В нижнем окне найдите кнопку Форма… и нажмите кнопку Добавить, после чего кнопка будет добавлена на панель быстрого доступа (верхний левый угол окна). Нажмите ОК.
2.1.1. Выделите основную таблицу и нажмите кнопку Форма на панели быстрого доступа. В результате получится форма данных, содержащая статический текст (имена полей базы данных), и окна редактирования, в которые можно вводить и редактировать текст.
2.1.2. Добавьте в базу данные ещё на двух квартиросъёмщиков, используя форму. После заполнения новой записи нажмите клавишу Enter, и вы автоматически перейдете к новой записи. Как только заполнена новая запись, вся внесенная информация воспроизведется в исходной таблице.
 |
Вычисляемые поля Сумма, Просрочка, Штраф, Итого (в которых размещены формулы) выводятся на экран без окон редактирования (рис.2).
Рис. 2. Форма
Теперь вы имеете таблицу как бы в форме отдельных карточек-записей (каждая из которых представляет строку таблицы). Перемещаться между записями можно либо при помощи кнопок Назад, Далее, либо клавишами управления курсором (вверх, вниз), либо перемещая бегунок на полосе прокрутки формы данных. Перемещаться между окнами редактирования (в которые вносятся данные) удобно клавишей Tab.
2.2. Сортировка. Вернитесь в режим таблицы. Для сортировки записей выделите все строки таблицы (кроме заголовков) и на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка. В первом окошке выберите столбец, по которому нужно отсортировать данные, во втором окошке выберите Значения, а в третьем определите порядок сортировки. Если требуется сортировка одновременно по нескольким полям, то надо добавить уровень сортировки, нажав на кнопку Уровень сортировки. И повторить все действия для второго поля.
2.3. Фильтры. Как и в любой базе данных, информацию можно фильтровать (т. е. выводить на экран только те данные, которые интересуют пользователя). Для этого есть удобное средство автофильтр: выделите таблицу с заголовками столбцов и на вкладке Данные в группе Сортировка и фильтр нажмите на кнопку Фильтр. После этого в правом нижнем углу названия столбика появится стрелка, при нажатии на которую мышкой раскрываются критерии поиска по данном полю.
Задайте условие отбора для поля Сумма «больше 1000» и «меньше 1500». Нажмите Числовые фильтры и выберите Настраиваемый фильтр. В открывшемся окне свяжите нужные запросы.
Если по данному полю был установлен фильтр, то рисунок на кнопке изменяется, что позволяет быстро найти и вернуть данные на экран, сняв соответствующий запрос с помощью команды Снять фильтр с.
2.4. В конце ведомости должна автоматически подсчитываться следующая статистика по всем квартирам: общая сумма графы Итого (формат рублевый без копеек), Средняя площадь, Максимальная просрочка.
Задание 3. Связывание данных, расположенных на разных листах. Подсчитайте сумму, внесённую каждым квартиросъёмщиком за три месяца.
3.1. Скопируйте таблицу «Квартплата» на 2, 3 и 4 листы Книги. На 2 листе измените срок и дату оплаты на февраль, на третьем листе - на март.
3.2. Присвойте листам имена «январь», «февраль», «март» и четвёртому листу - «к оплате», для чего устанавливается курсор на ярлык листа и из контекстного меню выбирается команда Переименовать.
3.3. На листе «к оплате» удалите все столбцы кроме № квартиры, фамилии и итогового столбца на оплату.
3.4. Для того, чтобы связать данные, расположенные на нескольких листах, на листе «К оплате» в ячейку «итого» вставьте формулу, суммирующую квартплату за январь, март и февраль: =«итого» за январь+«итого» за февраль+«итого» за март, выбирая нужные ячейки из таблиц с листов «январь», «февраль», «март» щелчком левой кнопки мыши по нужной ячейке.
Внимание! При связывании данных, расположенных на разных листах, к адресу ячейки автоматически добавляется название листа, например: =$B$3*январь!Н6. в данном примере информация берётся с листа, который называется «январь», из ячейки Н6.
Лабораторная работа №4