Тема: Ввод и копирование простейших формул. Оформление таблицы

ЛАБОРАТОРНАЯ РАБОТА № 2

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

Время выполнения: два академических часа.

Задания:

1. Запустите программу MS Excel.

2. Переименуйте Лист1 в «Расчёт заработной платы».

3. Начиная с ячейки B2, создайте таблицу по заданному образцу (ФИО работника и цифры оклада выбираются произвольно):

 

4. Используя полученные ранее знания, рассчитайте контрольные цифры по первому работнику:

- Премия: D5*0,1

- Подоходный: D5*0,3

- Пенсионный: D5*0,02

5. С помощью маркера автозаполнения скопируйте формулу из ячейки E5 в диапазон E5:E9; из ячейки F5 в диапазон F5:F9; из ячейки G5 в диапазон G5:G9 рассчитав таким образом контрольные цифры для остальных работников предприятия.

6. Для расчета итоговых суммирующих значений по столбцам: Оклад, Премия, Подоходный, Пенсионный, выполните следующие действия:

- Установите табличный курсор на ячейке D10 и выполните следующую последовательность операций: Формулы / Библиотека функций / Автосумма/ Убедитесь, что программа автоматически подставила в формулу функцию СУММ и верно выбрала диапазон ячеек для суммирования / Enter.

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

8. Рассчитайте итоговую выплату для первого работника, для этого установите курсор в ячейку H5 и, просуммировав ячейки Оклад и Премия вычтите сумму налогов Подоходный и Пенсионный.

9. Используя маркер автозаполнения, скопируйте формулу из ячейки H5 в диапазон H5:H9, рассчитав таким образом контрольные цифры для остальных работников предприятия.

10. Используя Автосуммирование, рассчитайте сумму итоговых выплат по предприятию и зафиксируйте ее в ячейке H10 строки итогов.

11. Установите широкую внешнюю рамку таблицы, выполнив следующую последовательность действий: Выделите всю таблицу/ Щёлкните правой кнопкой мыши по выделенной области / Формат ячеек … / Граница / Линия / Тип линии / Выберите широкую линию / Все / Внешние / ОК.

12. Раскрасьте таблицу по своему усмотрению, выполнив следующую последовательность операций: Выделите нужные ячейки/ Щёлкните правой кнопкой мыши по выделенной области / Формат ячеек … / Заливка / Цвет фона / Выберите из палитры цвет / ОК.

13. Сравните полученный результат с образцом, представленным ниже (цветовое оформление, ФИО могут быть произвольными):

 

 

14. Переименуйте Лист2 в «Таблица квадратов». Постройте традиционную таблицу квадратов двузначных чисел, для чего выполните следующие действия:

В ячейку A3 введите число 1, в ячейку A4 – число 2, выделите обе ячейки и протащите маркер выделения вниз, чтобы заполнить столбец числами от 1 до 9;

Аналогично заполните ячейки B2-K2 числами от 0 до 9.

Когда Вы заполнили строчку числами от 0 до 9, если все необходимые Вам для работы ячейки одновременно не видны на экране. Необходимо сузить их, но так, чтобы все столбцы имели одинаковую ширину (чего нельзя добиться, изменяя ширину столбцов мышкой), выполнив следующую последовательность операций: Выделите столбцы от Aдо K /Главная / Ячейки / Формат / Ширина столбца / В поле ввода введите значение 5 / ОК;

В ячейку B3 необходимо поместить формулу, которая возводит в квадрат число, составленное из десятков, указанных в столбце A и единиц, соответствующих значению, размещённому в строке 2. Таким образом, само число, которое должно возводиться в квадрат в ячейке B3 можно задать формулой =A3*10+B2 (число десятков, умноженное на десять плюс число единиц). Остаётся возвести это число в квадрат: Отредактируйте формулу, введённую в ячейку B3 до формулы =(A3*10+B2)^2 или =СТЕПЕНЬ((A3*10+B2);2), используя математическую категорию Мастера функций. В ячейке B3появился результат вычислений;

Распространите эту формулу и на остальные ячейки таблицы: Выделите ячейку B3 и заполните соседние ячейки, протянув маркер выделения вправо;

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

 

Дело в том, что когда Вы распространили формулу вправо, MS Excel автоматически изменил с учётом Вашего смещения адреса ячеек, на которые ссылается формула, и в ячейке C3 возводится в квадрат не число 11, а число, вычисленное по формуле =(A3*10+B2)^2. Это относительная ссылка на ячейки таблицы, однако возникла необходимость зафиксировать определённые ссылки, то есть указать, что число десятков можно брать только из столбца A, а число единиц только из строки 2 (для того, чтобы формулу можно было распространить вниз). В этом случае применяют абсолютные ссылки.

▲ Для фиксирования любой позиции адреса ячейки перед ней ставится знак $. Ознакомьтесь с относительными, абсолютными и смешанными ссылками, обратившись в справочную систему (F1).

Таким образом, верните ширину измененных столбцов в исходное положение и выполните следующую последовательность операций: Выделите ячейку B3 и, установив текстовый курсор в Строку формул, исправьте имеющуюся формулу =(A3*10+B2)^2на правильную =($A3*10+B$2)^2;

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

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

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

Задания на самоподготовку:

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

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

Оформите лист для расчёта площади квадрата по произвольно заданной длине его стороны:

 

Оформите лист для определения длины окружности по произвольному радиусу.

Оформите лист для расчёта площади круга по самостоятельно заданному диаметру.

d) Известна сторона квадрата. Оформите лист для определения длины его диагонали.

e) Оформите лист для расчёта при заданном значении a. Решение оформите в виде:

 

 

f) Немецкий физик Д. Г. Фаренгейт в 1724 году предложил шкалу температур, отличающуюся от шкалы температур Цельсия, которая принята в нашей стране. Перевод температуры по шкале Фаренгейта в температуру по шкале Цельсия осуществляется по формуле:

,

где - температура по шкале Фаренгейта. Подготовьте лист для определения температуры по шкале Цельсия при заданной температуре (T) по шкале Фаренгейта и наоборот.

g) Оформите лист для расчёта среднего арифметического и среднего геометрического двух заданных целых чисел.

i) Известны объём и масса тела. Определите плотность материала этого тела. Решение оформите в виде:

 

 

▲ Для того чтобы исключить появления в ячейке B4 сообщения об ошибке в случае, когда объём тела ещё не задан, в ячейку B2 условно введите 1.

j) Известны количество жителей в государстве и площадь его территории. Определите плотность населения в этом государстве.

k) Оформите лист для нахождения корня линейного уравнения в виде:

 

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

▲ Угловым коэффициентом отрезка называется отношение разности ординат концов отрезка к разности их абсцисс.

m) На листе приведены данные о странах, входящих в объединение Бенилюкс:

 

 

В ячейке E8 указана средняя плотность населения по объединению в целом. Заполните пустые клетки таблицы.