|
§ 20. Работа с диапазонами. Относительная адресация
Основные темы параграфа:
♦ что такое диапазон (блок);
♦ функции обработки диапазона;
♦ принцип относительной адресации;
♦ сортировка таблицы.
Что такое диапазон (блок)
Табличные процессоры позволяют выполнять некоторые вычисления с целой группой ячеек, называемой диапазоном.
Диапазон (блок, фрагмент) — любая прямоугольная часть таблицы.
Обычно диапазон обозначается именами верхней левой и нижней
правой ячеек, разделенными двоеточием. Например, в табл. 4.4 диапазон,
состоящий из вычисляемых ячеек, обозначается следующим образом: Е2:F6 (в
табл. 4.4 он выделен темным фоном). Минимальным диапазоном является
одна ячейка таблицы.
Функции обработки диапазона
В каждом табличном процессоре имеется целый набор функций,
применяемых к диапазонам. Это суммирование чисел (СУММ), входящих в
диапазон, вычисление среднего значения (СРЗНАЧ), нахождение
максимального (МАКС) и минимального (МИН) значения и некоторые другие.
Такие функции называются статистическими.
Предположим, что в конце рабочего дня необходимо подсчитать выручку,
полученную за день от продажи молочных продуктов. Для этого в таблице
4.3 нужно просуммировать все числа из диапазона F2:F6. Пусть функция
суммирования обозначается словом СУММ. Тогда нужная нам формула
запишется так: СУММ(F2:F6). Она обозначает следующее:
СУММ(F2:F6)=F2+F3+F4+F5+F6.
Запишем формулу суммирования в ячейку F7, а в ячейку Е7 — текст «ВСЕГО:». Результат — в табл. 4.5.
Таблица 4.5. Таблица с вычислением суммарной выручки
| A
| B
| C
| D
| E
| F
|
| 1
| Продукт
| Цена
| Поставлено
| Продано
| Осталось
| Выручка
|
| 2
| Молоко
| 20
| 100
| 100
| 0
| 2000
|
| 3
| Сметана
| 10,2
| 85
| 70
| 15
| 714
|
| 4
| Творог
| 18,5
| 125
| 110
| 15
| 2035
|
| 5
| Йогурт
| 5,4
| 250
| 225
| 25
| 1215
|
| 6
| Сливки
| 15,2
| 50
| 45
| 5
|
684
|
| 7
|
|
|
|
| ВСЕГО:
| 6648
|
Табличные процессоры позволяют манипулировать с диапазонами электронной таблицы.
К операциям манипулирования относятся: удаление, вставка, копирование,
перенос, сортировка диапазонов таблицы. Эти операции выполняются с
помощью команд табличного процессора. Обычно эти команды пользователь
выбирает из меню команд.
Принцип относительной адресации
Казалось бы, в результате таких манипуляций расчетные формулы
могут стать неверными, поскольку изменятся адреса перемещенных на новое
место ячеек. Чтобы такого не происходило, в электронной таблице
реализован принцип относительной адресации.
Согласно принципу относительной адресации, адреса ячеек,
используемые в формулах, определены не абсолютно, а относительно ячейки,
в которой располагается формула.
Следствием этого принципа является следующее правило:
Всякое изменение места расположения формулы ведет к автоматическому изменению адресов ячеек в этой формуле.
Поясним сказанное на примере. Пусть при подготовке таблицы для
расчета продажи товара на следующий день владелец павильона знает, что в
этот день не будут подвозиться сметана и творог. Поэтому две
соответствующие строки из табл. 4.4 можно удалить. Это делается с
помощью команды
УДАЛИТЬ АЗ:F4
На место удаленных строк сдвигаются строки снизу. В результате таблица преобразуется к виду, показанному в табл. 4.6.
Таблица 4.6. Таблица после удаления двух строк
| A
| B
| C
| D
| E
| F
|
| 1
| Продукт
| Цена
| Поставлено
| Продано
| Осталось
| Выручка
|
| 2
| Молоко
|
|
|
| =С2-D2
| =B2*D2
|
| 3
| Йогурт
|
|
|
| =C3-D3
| =B3*D3
|
| 4
| Сливки
|
|
|
| =C4-D4
| =B4*D4
|
Обратите внимание на две последние строки. В присутствующих в них
формулах изменились адреса ячеек. Здесь был учтен сдвиг на две строки
вверх; сработал принцип относительной адресации.
Сортировка таблицы
Допустим, владелец торгового павильона хочет узнать, какие товары
пользуются наибольшим спросом. Для этого достаточно упорядочить строки
таблицы по убыванию чисел в столбце «Продано». Большинство табличных
процессоров позволяют производить сортировку (упорядочение) таблицы по
какому-либо признаку. Для нашего примера формируется команда такого
типа:
СОРТИРОВАТЬ СТОЛБЕЦ D ПО УБЫВАНИЮ
Применение этой команды к табл. 4.5 в режиме отображения значений даст результат, показанный в табл. 4.7.
Таблица 4.7. Результат сортировки таблицы по столбцу «Продано»
| A
| B
| C
| D
| E
| F
|
| 1
| Продукт
| Цена
| Поставлено
| Продано
| Осталось
| Выручка
|
| 2
| Йогурт
| 5,4
| 250
| 225
| 25
| 1215
|
| 3
| Творог
| 13,5
| 125
| 110
| 15
| 2035
|
| 4
| Молоко
| 20
| 100
| 100
| 0
| 2000
|
| 5
| Сметана
| 10,2
| 85
| 70
| 15
| 714
|
| 6
| Сливки
| 15,2
| 50
| 45
| 5
| 684
|
| 7
|
|
|
|
| ВСЕГО:
| 6648
|
Отсюда видно, что наибольшим спросом пользуется йогурт, а меньше всего покупают сливки.
Эта же таблица в режиме отображения формул — табл. 4.8.
Таблица 4.8. Отсортированная таблица в режиме отображения формул
| A
| B
| C
| D
| E
| F
|
| 1
| Продукт
| Цена
| Поставлено
| Продано
| Осталось
| Выручка
|
| 2
| Йогурт
| 5,4
| 250
| 225
| =С2-D2
| =B2*D2
|
| 3
| Творог
| 18,5
| 125
| 110
| =C3-D3
| =B3*D3
|
| 4
| Молоко
| 20
| 100
| 100
| =C4-D4
| =B4*D4
|
| 5
| Сметана
| 10,2
| 85
| 70
| =C5-D5
| =B5*D5
|
| 6
| Сливки
| 15,2
| 50
| 45
| =C6-D6
| =B6*D6
|
| 7
|
|
|
|
| ВСЕГО:
| =СУММ(F2:F6)
|
Снова сработал принцип относительной адресации. Формулы изменились в соответствии с изменением месторасположения строк.
Коротко о главном
Диапазон (блок, фрагмент) таблицы — это любая ее прямоугольная часть (в том числе часть строки, часть столбца или одна ячейка).
Для выполнения расчетов с числовыми фрагментами используются
статистические функции: суммирование, усреднение, нахождение наибольшего
и наименьшего значений и др.
С фрагментами таблицы можно производить операции манипулирования: удаление, вставку, перенос, сортировку.
Принцип относительной адресации: адреса ячеек в формулах
определены не абсолютно, а относительно местонахождения этой формулы.
Следствие: при перемещении формулы в другую ячейку соответствующим
образом изменяются адреса ячеек, содержащиеся в ней.
Вопросы и задания
1. Что такое диапазон? Как он обозначается?
2. Какие вычисления можно выполнять над целым диапазоном?
3. Что понимается под манипулированием диапазонами ЭТ?
4. Что такое принцип относительной адресации? В каких ситуациях он проявляется?
5. В ячейке D7 записана формула (С3+С5)/D6. Как она изменится при переносе этой формулы в ячейку:
а) D8; б) Е7; в) С6; г) F10 ?
6. В ячейке Е4 находится формула СУММ(А4:D4). Куда она переместится и как изменится при:
а)
удалении строки 2; б) удалении строки 7; в) вставке пустой строки перед
строкой 4; г) удалении столбца 3; д) вставке пустого столбца перед
столбцом 6.
7. К таблице «Оплата электроэнергии», полученной при
выполнении задания 6 из предыдущего параграфа, добавьте расчет всей
выплаченной суммы за год.
8. К таблице из предыдущей задачи добавьте расчет выплаченной суммы денег за каждый квартал (квартал — 3 месяца).
Интерактивный задачник, раздел "Статистические функции в электронных таблицах" Решение задач на отработку темы "Статистические функции в электронных таблицах"
Сортировка данных в таблице MS Excel Интерактивный справочник по ИКТ для самостоятельной работы учащихся
Тренировочный тест к главе 4 "Табличные вычисления на компьютере". Самоконтроль по теме "Табличные вычисления на компьютере". Подготовка к итоговому тестированию.
|
