Диапазоны. Функции обработки диапазона. Относительная адресация. Сортировка таблицы
1. Работа с диапазонами. Относительная адресация.
Что такое диапазон
Функции обработки диапазона
Принцип относительной адресации
Сортировка таблицы
2.
Табличные процессоры позволяют
выполнять некоторые вычисления с
целой группой ячеек, называемой
диапазоном
Диапазон (блок, фрагмент) – любая
прямоугольная часть таблицы.
Диапазон обозначается именами
верхней левой и нижней правой
ячеек, разделенными двоеточием.
Например: E2:F6.
3.
Минимальным диапазоном является одна
ячейка таблицы.
4. Функции обработки диапазона
В табличном процессоре имеется
целый набор функций, применяемых
к диапазонам.
Суммирование чисел (СУММ),
вычисление среднего значения
(СРЗНАЧ), нахождение
максимального (МАКС),
минимального значения (МИН)
5.
Пример =СУММ(F2:F6) нахождение
выручки за день.
6.
Табличные процессоры позволяют
манипулировать с диапазонами
электронной таблицы.
К операциям манипулирования
относятся: удаление, вставка,
копирование, перенос, сортировка
диапазонов таблицы.
Эти операции выполняются с
помощью команд табличного
процессора.
7. Принцип относительной адресации.
Согласно принципу относительной
адресации, адреса ячеек, используемые в
формулах, определены не абсолютно, а
относительно ячейки, в которой
располагается формула.
Всякое изменение места расположения
формулы ведет к автоматическому
изменению адресов ячеек в этой формуле.
8.
Пусть в этот день не будет
подвозиться сметана и творог.
Поэтому две соответствующие строки
можно удалить с помощью команды
УДАЛИТЬ A3:F4.
На место удаленных строк
сдвигаются строки снизу. В формулах
изменились адреса ячеек.
9. Сортировка таблицы.
Табличный процессор позволяет
производить сортировку таблицы по
какому-либо признаку.
Сортировать столбец D по убыванию.
Диапазон ячеек и его выделение
Диапазон ячеек – базовое структурное понятие электронной таблицы, определяющее блок ячеек (от правого верхнего до левого нижнего угла прямоугольного блока) или несколько прямоугольных блоков. Адресацию ячеек с данными в виде диапазона от левой верхней до правой нижней ячейки можно назвать «правилом двух гвоздей» (рис. 6.3). Диапазон применяется во многих командах, выражениях и функциях табличного редактора. Минимальный диапазон – сама ячейка, максимальный – все ячейки на листе.
Диапазон можно указать несколькими способами:
• выделить ячейки указателем мыши или клавишами клавиатуры, при этом ячейка выделяется прямоугольной рамкой, а диапазон – темным цветом и (или) рамкой. Следует обратить внимание, что начальная ячейка диапазона при выделении не затеняется, чтобы видеть цвет заливки диапазона (эта ячейка остается активной – готовой для обычного ввода или редактирования данных);
Рис. 6.3. Диапазон (В2:Е5), заданный адресами угловых ячеек В2 и Е5 («два гвоздя»)
• набрать адреса угловых ячеек с клавиатуры и поставить между адресами разделитель (оператор ссылки на диапазон) – двоеточие или точку, например В2:Е5 или В2.Е5. Непосредственно в момент выделения поле Имя строки формул указывает размер диапазона количеством строк и столбцов: 3RX4C означает 3 строки, 4 столбца. Выделенному диапазону можно присвоить имя командой Вставка, Имя, Присвоить, чтобы в дальнейшем в функциях указывать не адреса ячеек, а имя.
Приемы выделения диапазона ячеек могут быть следующими.
Выделение диапазона ячеек в виде одного прямоугольного блока. Держать нажатой левую кнопку мыши и переносить указатель от угловой ячейки по диагонали к противоположному углу диапазона или щелкнуть левой кнопкой мыши любой угол диапазона, потом, нажимая и удерживая клавишу Shift, щелкнуть левой кнопкой мыши противоположный по диагонали угол диапазона. Можно, удерживая клавишу Shift, перемещать табличный курсор клавишами со стрелками или другими клавишами перемещения курсора, расширяя выделение.
Выделение диапазона ячеек из нескольких прямоугольных блоков. Держать нажатой клавишу Ctrl и выделять указателем мыши с нажатой кнопкой первый диапазон, затем второй и т.д., чтобы выделить несколько несмежных блоков или многоугольный диапазон.
Выделение строки или столбца. Щелкнуть координатную рамку листа книги на номере строки или букве столбца.
Выделение нескольких столбцов или строк. Нажать левую кнопку мыши и переносить курсор по координатной рамке окна книги по буквам столбцов или номерам строк.
Можно поставить табличный курсор в какой-то угол будущего блока для выделения и нажать клавишу F8. Ячейка стала «исходным углом» диапазона будущего выделения. Перейти указателем мыши или стрелками в конечный угол блока и нажать Enter.
Диапазон ячеек можно выделить с помощью строки (поля) Имя, если ввести туда имена угловых ячеек, разделенные точкой или точкой с запятой, и нажать Enter.
Выделить весь рабочий лист позволяет сочетание клавиш Ctrl + А.
Несколько несмежных диапазонов задают адресами ячеек или диапазонов, между которыми ставится разделитель – точка с запятой. Например, диапазон (C2:D3;F2:G9;E;8) состоит из четырех: два прямоугольных блока C2:D3 и F2:G9; столбец Е; строка 8.
Набор адреса ячеек диапазона с клавиатуры применяют в функциях, чтобы определить столбец, строку, частичный столбец, частичную строку, диапазон столбцов, строк, блок ячеек.
Адресация ячеек. Формула или функция таблицы может содержать ссылки на адреса ячеек, откуда требуется взять данные для вычислений. Структура таблицы чаще всего однородна по столбцам (иногда по строкам). Однородность означает, что действие, записанное в формуле первой строки таблицы, как правило, повторится для ячеек в других строках той же колонки, но со смещением адресов ячеек, на которые формула ссылается.
При копировании формул табличный редактор учитывает это важное свойство таблиц. Копирование формулы, содержащей относительные ссылки, в новую ячейку автоматически перестраивает ссылки, указывая измененные адреса ячеек. Обычная адресация ссылок в формулах и функциях, которая перестраивает адреса относительно нового положения копии ячейки с формулой, называется относительной адресацией. Если в какой-то ячейке записана формула с адресами сомножителей =В2*С2, то ее копирование в ячейку того же столбца на строку ниже изменит записанные в формуле ссылки на адреса ячеек, увеличив номер строки на +1. Формула перестроится как =В3*С3 (относительно нового места).
Чтобы ссылки на адреса не изменялись при копировании формулы или функции в другую ячейку, используют абсолютную адресацию ячеек (абсолютные ссылки). Например, адрес ячейки с курсом валют на товарном счете будет использован ячейками строк разных товаров, цена которых дана в валюте. Абсолютная адресация, которая при копировании не перестраивается, устанавливается символом $, например $D$7. Возможна смешанная адресация. Например, ссылка на адрес Н$5 разрешает при копировании изменять имя столбца Н, а номер строки 5 остается тем же. Символ $ с клавиатуры набирать не обязательно, следует поставить курсор на адрес в формуле и нажать клавишу F4. Ссылка на адрес D7 превратится в $D$7, а после еще одного нажатия – в [)$7 и т.д.
Другой вариант абсолютного адреса – дать имя ячейке или диапазону и сделать в формулах ссылку не на адреса, а на это имя командой Вставка, Имя, Вставить. Если, например, ячейке, где выполняется автосуммирование данных, присвоить имя Итого, то можно написать формулу =Н7/Итого. Имя ячейки Итого как абсолютный адрес будет использоваться для расчета долевой части каждой позиции в строках таблицы.
Копирование и перемещение данных из ячеек. Копирование и перемещение содержимого ячеек можно выполнять разными способами.
Копирование и перемещение командами меню. Для копирования содержимого одной ячейки в другую ячейку (блок ячеек) следует в исходной ячейке дать команду Копировать, а в ячейке назначения – Правка, Вставить.
Для копирования содержимого блока ячеек следует выделить исходный блок ячеек, дать команду Правка, Копировать – рамка выделенного блока превратится в бегущую пунктирную линию. Поставить табличный курсор в ту ячейку, где будет левый верхний угол нового положения блока, и нажать Enter или дать команду Правка, Вставить. Вставку можно повторить несколько раз.
Для перемещения блока ячеек необходимо их выделить и дать команду Правка, Вырезать, затем, поставив курсор на требуемое место, – Правка, Вставить. Перемещаемый блок ячеек заменяет данные в блоке-приемнике, если они там были.
Копирование и перенос с помощью мыши производится выделением диапазона ячеек и подводом указателя мыши к рамке выделенного диапазона. Когда около рамки указатель мыши сменит вид + «крестик» на наклонную стрелку (или четырехнаправленную стрелку), зацепить рамку нажатой левой кнопкой мыши и перетащить на новое место. При одновременном нажатии клавиши Ctrl и перетаскивании рамки происходит копирование блока ячеек.
Копирование и перенос с помощью контекстно-зависимого меню. Выделить блок ячеек, щелкнуть его правой кнопкой мыши и в списке доступных команд выбрать Вырезать или Копировать. Щелкнуть правой кнопкой мыши левую ячейку места назначения и в списке доступных команд выбрать команду Вставить.
Копирование заполнением в смежные ячейки. Выделить диапазон ячеек. Рамка выделения имеет в правом нижнем углу прямоугольную точку – маркер заполнения. Указатель мыши, который обычно в Excel имеет вид крестика +, превращается около точки маркера в знак плюс. Установив курсор мыши на маркер, следует протащить маркер через заполняемые соседние ячейки горизонтально или вертикально. Если перетаскивание маркера заполнения приводит к нежелательному приращению значений чисел или дат в пределах выделенного диапазона, следует нажать временно всплывшую кнопку Параметры автозаполнения и выбрать вариант: копировать ячейки, заполнить только форматы или только значения.
Копирование формата ячейки. Применяется для повторения формата данной ячейки в других ячейках. Выделить ячейку-прототип, нажать на вкладке Главная кнопку Формат по образцу. После этого выделить мышью диапазон-получатель, который отформатируется по образцу ячейки-прототипа.
Автозаполнение. Автоматическое заполнение – процесс заполнения ячеек Excel данными по заготовленному ряду- образцу смежных ячеек или командой с параметрами заполнения.
Ряды автоматического заполнения могут идти по столбцам или по строкам. Например, два начальных члена ряда необходимо ввести с клавиатуры, как на рис. 6.4, а. Затем выделить две смежные ячейки и командой Формат, Ячейки, Дата изменить формат, как на рис. 6.4, б.
Зацеп и перетаскивание маркера заполнения левой кнопкой мыши распределяет рамку выделения на следующие ячейки и выполняет заполнение ряда с интервалом дат, равным шагу от первого ко второму элементу ряда. Для предложенного примера получится интервал дат три месяца (рис. 6.4, в).
Рис. 6.4. Заполнение ряда дат:
а – начальная последовательность; б – выделение и изменение формата; в – результат автозаполнения
Если перетащить маркер указателем с нажатой правой кнопкой мыши, то появится контекстное меню команд с вариантами копирования или заполнения. Для рассмотренного примера из всплывающего контекстного меню следовало бы выбрать команду Заполнить по месяцам.
В Excel хранятся предопределенные последовательности автозаполнения (дни недели, названия месяцев и др.). Соответствие начальных значений времени и дат и получающихся рядов показано в табл. 6.1. Можно образовать ряд, в котором есть постоянный текст и меняющаяся нумерация (см. строку Квартал 1 в табл. 6.1).
Заполнение по команде Прогрессия – способ заполнить прогрессию по одному начальному значению (образец второй ячейки не требуется). Необходимо выделить начальную ячейку и следующие пустые ячейки диапазона, в которых должен образоваться будущий ряд. Выделить, но не зацеплять, не протягивать, не автозаполнять – просто провести указателем мыши по ячейкам.
Примеры последовательностей заполнения ячеек
Лекция: Диапазон ячеек
Компактная группа ячеек таблицы, имеющая прямоугольную форму.
Диск
Круглая металлическая или пластмассовая пластина, покрытая магнитным материалом, на которую информация наносится в виде концентрических дорожек, разделённых на секторы.
Дисковод
Устройство, управляющее вращением магнитного диска, чтением и записью данных на нём.
Дисплей
Устройство визуального отображения информации (в виде текста, таблицы, рисунка, чертежа и др.) на экране электронно-лучевого прибора.
Драйверы
Программы, расширяющие возможности операционной системы по управлению устройствами ввода-вывода, оперативной памятью и т.д.; с помощью драйверов возможно подключение к компьютеру новых устройств или нестандартное использование имеющихся устройств.
Документ (Document)
Объект обработки прикладной программы.
Дюйм (Inch)
Единица измерения длины. (1 дюйм равен 2, 54 см.)
Закладка (Bookmark)
Это имя, присвоенное некоторому месту в документе; она позволяет вам быстро перепрыгивать к этому месту или ссылаться на текст в этом месте с помощью перекрестных ссылок. Закладка может отмечать как курсор вставки, так и область выделения любого размера.
Замена (Replacement)
Замена состоит в выделении ненужного места и вводе вместо него нового.
Ответы по параграфу 22 Работа с диапазонами. Относительная адресация
Учебник по Информатике 8 класс Семакин
Задание 1. Что такое диапазон? Как он обозначается?
Диапазон в таблице – это любая прямоугольная её часть. Он обозначается именами верхней левой и нижней правой ячеек, а между ними стоит двоеточие.
Задание 2. Какие вычисления можно выполнять над целым диапазоном?
С диапазоном можно выполнять целый набор статистических функций, такие как суммирование чисел из диапазона (СУММ), вычисление среднего значения (СРЗНАЧ), нахождение максимального (МАКС) и минимального (МИН) значений, функция (МОДА) возвращает наиболее часто встречающееся значение в диапазоне и другие.
Задание 3. Что понимается под манипулированием диапазонами ЭТ?
Под манипулированием диапазонами ЭТ понимается удаление, вставка, копирование, перенос, сортировка диапазонов таблицы.
Задание 4. Что такое принцип относительной адресации? В каких ситуациях он проявляется?
Согласно принципу относительной адресации, адреса ячеек, применяемые в формулах, определены не абсолютно, а относительной ячейки, в которой находится формула. Он проявляется при изменении места расположения формулы, что ведет к автоматическому изменению адресов ячеек в этой формуле.
Задание 5. В ячейке D7 записана формула (C3+C5)/D6. Как она изменится при переносе этой формулы в ячейку:а) D8; б) E7; в) C6; г) F10?
а) =(C4+C6)/D7
б) =(D3+D5)/E6
в) =(B2+B4)/C5
г) =(E6+E8)/F9
Задание 6. В ячейке E4 находится формула СУММ(A4:D4). Куда она переместится и как изменится при: а) удалении строки 2; б) удалении строки 7; в) вставке пустой строки перед строкой 4; г) удалении столбца C; д) вставке пустого столбца перед столбцом F?
а) перемещается в ячейку E3 =СУММ(A3:D3)
б) ничего не изменится
в) перемещается в ячейку E5 =СУММ(A5:D5)
г) перемещается в ячейку D4 =СУММ(A4:C4)
д) ничего не изменится
Задание 7. К таблице «Оплата электроэнергии», полученной при выполнении задания 6 из предыдущего параграфа, добавьте расчет всей выплаченной за год суммы денег и сумм, выплаченных за каждый квартал (квартал — 3 месяца).
Пример электронной таблицы можете скачать по ссылке ниже, можете поменять цену за 1 киловатт-час в вашем городе и показания счетчика.
Скачать электронную таблицу