Задание № 1. Относительные и абсолютные ссылки.

Подготовьте таблицу по образцу.

1.
В ячейку В3 введите «Наименование товара». В ячейку С3 «Количество».

2. Примените к ячейкам нужные способы выравнивания.

3. Примените к ячейкам, содержащим цены, денежный формат числа с разделением на разряды и двумя десятичными знаками.

4. Введите наименование товара и цену за единицу, например:

5.
Для того, чтобы рассчитать стоимость товара за две единицы, в ячейку D 5 введите формулу =С5* D 4 (цену за единицу товара умножить на количество).

6. С помощью маркера заполнения распространите формулу вправо, чтобы получить стоимость товара за 3 и 4 единицы. Сравните свой результат с приведенным ниже.

Можно заметить, что вычисленная по формуле стоимость товара за три единицы неверна. Если выделить эту ячейку (Е5), в Строке формул появится формула (D 5*Е4). Однако в этой ячейке должна быть формула С5*Е4.

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

В таких случаях, составляя формулу, применяют абсолютные ссылки. При перемещении или копировании формулы абсолютные ссылки не изменяются (ячейка фиксируется), в то время как относительные ссылки (с которыми мы работали до сих пор) автоматически обновляются в зависимости от нового положения. Абсолютные ссылки имеют вид: $ F $9; $ C $45. Для фиксации координат применяется знак $.

7. Следовательно, для того, чтобы получить верные результаты в нашем примере, в ячейке D 5 вместо формулы C 5* D 4 должна быть формула $ C $5* D 4.

8. Измените эту формулу и скопируйте её вправо. Сравните результат.

Задание № 2. Имена ячеек.

Ниже в интервале В8 – F 8 оформите таблицу из первого задания:

1. ячейке С10 присвойте имя «цена»:

o сделайте ячейку активной;

o перейти в контекстном меню (появляется при нажатии правой клавиши мыши) Имя диапазона….

o В открывшемся окне компьютер предлагает вам ввести имя ячейки. По умолчанию - текст из соседней ячейки (Сист_плата).

o Удалим это имя и впишем Цена. ОК.

o В адресном поле (поле имени) появилось Цена.

o В ячейку D10 введем формулу =Цена* D 9

o Скопируйте формулу вправо.

o Получили результат.

Задание № 3. Смешанные ссылки.

Составьте таблицу сложения чисел первого десятка.

1. В ячейку А2 введите заголовок: Таблица сложения.

2. Начиная, с ячейки А3 с помощью автозаполнения введите числа вправо и вниз.

3. В ячейку В4 введите формулу: $А4+В$3.

4. Копируйте формулу вправо и вниз. Что получилось?

5. Сохраните документ под именем Lab2.xls.

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

1. Что такое относительная ссылка?

2. Что такое абсолютная ссылка?

3. Что такое смешанная ссылка?

4. Что значит «присвоить ячейке имя»? Как используется имя ячейки?

Лабораторная работа № 3
Тема: Функции в формулах

Цель: научиться использовать мастер функций.

Теоретические сведения

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

Допускается использование ссылок на диапазоны из других листов и книг в качестве аргументов: =СУММ(С7:С9;Лист2!B3:B15; [Книга4]Лист1!$A$4:$A$6).

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

Для работы с функциями можно использовать Мастер функций. Для этого необходимо выбрать ленту Формулы, команду Вставить функцию или нажатием кнопки Вставка функции стандартной панели инструментов (f _x). Все функции сгруппированы по категориям, имена которых отображаются в списке Категория. В поле Функция приводится перечень функций выбранной категории.

Некоторые функции Excel

Функция	Действие
Математические
КОРЕНЬ	квадратный корень
ПРОИЗВЕД	произведение аргументов
СТЕПЕНЬ	возведение в степень
СУММ	сумма аргументов
ОКРУГЛ	округление до указанной точности
ЦЕЛОЕ	ближайшее целое значение
Статистические
СРЗНАЧ	Среднее значение аргументов
МИН	Наименьший среди аргументов
МАКС	Наибольший среди аргументов
Дата и время
ГОД	Номер года из даты
МЕСЯЦ, ДЕНЬ	Аналогично ГОД
Логические
ЕСЛИ (логическое_выражение; Выражение1; выражение2)	Результатом будет значение выражения 1, если логическое_выражение истинно, и выражение 2 в противном случае.

Порядок выполнения работы.

Задание № 1.В таблицу собраны данные о крупнейших озерах мира:

Найти глубину самого мелкого озера, площадь самого обширного озера и среднюю высоту озер над уровнем моря.

Для решения задачи воспользуемся статистическим функциями МИН(), МАКС() и СРЗНАЧ().

1. В клетку с адресом В8 поместим формулу: МИН(С2:С7) — поиск минимального значения по диапазону клеток С2:С7, содержащему значения глубин каждого озера.

2. В клетку с адресом В9 поместим формулу: МАКС(В2:В7) — поиск максимального значения по диапазону клеток В2:В7.

3. В клетку с адресом В10 поместим формулу: CP3HAЧ(D2:D7), с помощью которой вычисляется средняя высота озер над уровнем моря.

4. В клетки A8, A9 и А10 поместим соответствующие пояснения.

В результате получим таблицу:

Задание № 2.

Продолжить таблицу: ввести еще 5 фамилий с оценками. Используя функцию Счетесли рассчитать итоги успеваемости студентов в столбце "Количество" (сколько двоек, троек, четверок, пятерок).

ФИО	Успеваемость (оценка)	Вид оценки	Количество
Алексеев	5	2	?
Баранов	3	3	?
Березкина	4	4	?
Быстров	3	5	?
Воронин	5
Воробьев	3
?	?
?	?
?	?

Функция СЧЕТЕСЛИ находится в Мастере функций в категории Статистические. Функция СЧЕТЕСЛИ имеет два аргумента:

· ссылка на весь диапазон, в котором находятся значения для счета (т.е. нужно выделить тот диапазон ячеек, в котором находятся значения оценок «Успеваемость»);

· критерий, определяющий, что конкретно надо подсчитать (например, сначала подсчитывается количество оценок «5» т.е. в качестве критерия выделяется ячейка, где стоит оценка 5., но только не в диапазоне поиска).

Задание № 3.

Округлить число 347,659 с помощью функции ОКРУГЛ(), получить следующие результаты: 347,66; 347,7; 348,0; 350; 300. Оформить в виде таблицы, использовать операцию копирования формулы.

В функции ОКРУГЛ имеется два аргумента:

· первый значение для округления (т.е. нужно задать ссылку на ячейку с указанным числом);

· цифра указывает количество десятичных разрядов; если аргумент отрицательный, то округление смещается влево от запятой на заданное количество цифр.

Задание № 4.

Рассчитать суммы продаж по регионам для следующих данных, приведенных в таблице, используя функцию СУММЕСЛИ.

Сначала введите исходные данные для расчета. Затем выберите функцию СУММЕСЛИ в категории Математические (или Статистические в зависимости от версии Excel) и введите в поля Мастера требуемые диапазоны ячеек. Эта функция имеет три аргумента:

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

· критерий отбора (название региона);

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

Дата	Регион	Продажи ( в тыс.руб)	Суммы продаж по регионам
12/01/2000	Север	2062,6	Север	?
18/01/2000	Юг	8257,4	Юг	?
23/01/2000	Запад	6004,7	Запад	?
24/01/2000	Восток	828,5	Восток	?
02/02/2000	Север	5136,6
06/02/2000	Юг	10769,2
10/02/2000	Запад	4514,6
14/02/2000	Восток	8135,7
02/03/2000	Север	1881,9
10/03/2000	Юг	8093,5
17/03/2000	Восток	6612,3
12/03/2000	Запад	1638,8

Отформатируйте созданные таблицы. Итоговые значения выделите другим цветом или заливкой. Для числовых данных задайте, где нужно денежный формат.

Результат покажите преподавателю.

Самостоятельное задание

Дана таблица со сведениями о выполнении тестов группой испытуемых (всего 20 фамилий). Вычислить суммарное количество баллов для каждого испытуемого и проставить ему оценку в соответствии со шкалой.

Рис. Таблица результатов тестирования испытуемых

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

1. Какими способами в ячейку можно вставить формулу?

2. С чего начинается ввод формул?

3. С какими категориями функций работает Excell?

4. Для чего в формулах используются функции?

Лабораторная работа № 4
Работа с листами, связи между таблицами, построение диаграмм

Цель: научиться работать с листами рабочей книги: добавлять, удалять, переименовывать. Научиться организовывать связи между таблицами. Овладеть навыками построения диаграмм.

Теоретические сведения

По умолчанию в открываемой книге имеется 3 листа.

Максимальное количество листов – 255, однако, можно и больше, все определяется памятью и системными ресурсами.

Для вставки дополнительного листа используется ярлык листа,расположенный после пречня ярлыков открытых листов в нижней части экрана.

Для удаления листа необходимо щелкнуть мышью по ярлыку листа, нажать правую клавишу мыши и выбрать команду Удалить.

Чтобы использовать ссылку на ячейку из другого листа придерживайтесь следующего формата =Имя_Листа!Адрес_ячейки.

Чтобы сослаться на ячейку из другой рабочей книги придерживайтесь следующего формата =[Имя_Рабочей книги]Имя_листа!Адрес_ячейки.

Порядок выполнения работы:

Дата добавления: 2019-01-14; просмотров: 968; Мы поможем в написании вашей работы!

Поделиться с друзьями:

⇐ Предыдущая 123 4 5 6 7 Следующая ⇒

Мы поможем в написании ваших работ!