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

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

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

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

Рисунок 2.1 − Маркер автозаполнения

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

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

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

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

Рисунок 2.2 − Пример относительной адресации ячеек

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

Рисунок 2.3 − Пример абсолютной адресации ячеек

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

Рисунок 2.4 − Пример смешанной адресации ячеек

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

1. Щелкнуть ячейку, в которую необходимо ввести формулу
2. Набрать знак равенства (=)
3. Набрать формулу. Она появится в строке формул.
4. Нажать Enter или щелкнуть «галочку» в строке формул. Excel вычислить результат.

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

1. Выделить ту ячейку, в которую необходимо вставить сумму. Лучше, если она расположена в конце строки или столбца данных - это поможет Excel «догадаться», какие ячейки необходимо просуммировать.
2. Щелкнуть кнопку «Автосумма» на стандартной панели инструментов. В выделенную ячейку будет введена функция СУММ и адрес диапазона левее или выше ячейки.
3. Если выбранный Excel диапазон будет неверным, то необходимо исправить формулу «вручную» в строке формул, либо перетащить курсор мыши через необходимый диапазон.
4. Нажать клавишу Enter либо щелкнуть кнопку Enter в строке формул. Можно просто дважды щелкнуть кнопку «Автосумма» и функция СУММ будет сразу вставлена в выделенную ячейку.

Задание

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

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

2. Переименуйте первый лист Вашей рабочей книги, присвоив ему имя «Автозаполнение» (для этого щелкните правой кнопкой на ярлычке листа и вместо текста «Лист1» внесите текст «Автозаполнение»)
3. В ячейку А1 введите любое число, в ячейку В1 – любое слово, а в ячейку С1 введите 1995. Выделите блок А1:С1 и протащите его за маркер заполнения на 10 строк вниз. Проанализируйте результаты.

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

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

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

4. Дважды щелкните на ярлычке второго рабочего листа и дайте этому рабочему листу имя «Функции».
Сделайте текущей ячейку А1 и введите в неё заголовок «Результаты измерений».
Введите числа от 2 до 9 в последовательные ячейки столбца А (используя маркер автозаполнения), начиная с ячейки А2 по А9.
Введите в ячейку В1строчку «Удвоенное значение».
Введите в ячейку С1 строчку «Квадрат значения».
Введите в ячейку D1 строчку «Корень из числа».
Введите в ячейку А10 строчку «Сумма».
Введите в ячейку А11 строчку «Среднее значение».
Введите в ячейку В2 формулу =2*А2.
Введите в ячейку С2 формулу =А2*А2.

Сделайте активной ячейку D2 войдите во вкладку Формулы и нажмите кнопку «Вставка функции». В появившемся диалоговом окне слева выберите категорию «Полный алфавитный перечень», а затем справа найдите и отметьте функцию КОРЕНЬ и затем нажмите Ok. Далее в поле «Число» необходимо указать ячейку А2. Это можно сделать 3-мя способами: во-первых, можно просто впечатать необходимое в это поле, во-вторых, можно отодвинуть окно мастера функций на свободное место и щелкнуть указателем мыши по необходимой ячейке и, в-третьих, можно нажать кнопку свертывания окна (), щелкнуть указателем мыши по необходимой ячейке и затем нажать кнопку развертывания (). Затем следует нажать Ok. Проверьте теперь, что в ячейке D2 у Вас действительно находится формула =КОРЕНЬ(A2).

5. Выделите протягиванием ячейки В2, С2 и D2.

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

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

Сделайте активной ячейку В11 и нажмите кнопку «Вставка функции» . В появившемся диалоговом окне слева выберите категорию «Полный алфавитный перечень», а затем справа найдите и отметьте функцию СРЗНАЧ и затем нажмите Ok. Далее в поле «Число 1» необходимо указать диапазон А2:А9. Это можно сделать 3-мя способами: во-первых, можно просто впечатать необходимое в это поле, во-вторых, можно отодвинуть окно мастера функций на свободное место и протащить указатель мыши по необходимому диапазону и, в-третьих, можно нажать кнопку свертывания окна (), протащить мышь по диапазону и затем нажать кнопку развертывания (). Затем следует нажать Ok. Проверьте теперь, что в ячейке D2 у Вас действительно находится формула =СРЗНАЧ(A2:A9).

7. Изменить одно из значений в столбце А и убедитесь, что соответствующие значения в столбцах В, С и D, в этой же строке были автоматически пересчитаны.
Введите в ячейку Е1 строку Масштабный множитель.
Введите в ячейку Е2 число 5.
(т.к. масштабный множитель равен 5)
Введите в ячейку F1 строку Масштабирование.
Введите в ячейку F2 формулу =А2*Е2.

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

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

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

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

Рисунок 2.5 - Результаты вычислений

Контрольные вопросы:

1. Как запустить программу Microsoft Excel?
2. Какие существуют способы заполнения и редактирования ячеек?
3. Как выделить смежные и несмежные диапазоны?
4. Как производится автосуммирование строк и столбцов?
5. Как перейти между листами в одной рабочей книге?
6. Как в формулах обозначаются абсолютные и относительные ссылки?