Сохранить документ под именем lab1. Xls.

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

Электронная таблица (ЭТ) – самая распространенная и мощная информационная технология для профессиональной работы с данными. Для управления ЭТ созданы специальные программные продукты – табличные процессоры.

Главное достоинство ЭТ – ее динамизм, т. е. возможность мгновенно пересчитывать все данные таблицы, связанные формульными зависимостями, при изменении значений любого операнда.

Базовым элементом электронной таблицы является ячейка, которая образуется на пересечение строк и столбцов таблицы. Каждая ячейка имеет адрес, который задается заголовком столбца (обозначаются буквами A, B, C…) и номера строки (задаются цифрами). Адрес выделенной ячейки показан в поле имени.

Совокупность ячеек составляет рабочий лист. Рабочий лист максимально может иметь 256 столбцов и 65536 строк.

Совокупность рабочих листов образует Рабочую книгу. Каждая рабочая книга может иметь максимум 256 листов.

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

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

Ввод данных в ячейку заканчивается одним из следующих способов:

· нажатием клавиши ENTER;

· щелчком по кнопке Ввод;

· переходом в другую ячейку по щелчку мыши, по клавише TAB или по клавишам управления курсором.

Автозаполнение – автоматическое заполнение ряда ячеек последовательными логически связанными значениями.

Например, ввести в одну из ячеек январь и потянуть мышью вправо за маркер заполнения. Названия месяцев появятся во всей строке (рис. 1).

 

Рис. 1. Пример автозаполнения

Ячейки рабочего листа имеют заданный формат, который устанавливается командой Формат/Ячейки или командой контекстного меню Формат ячеек. Эти команды имеют не сколько вкладок: Число, Выравнивание, Шрифт, Граница, Вид, Защита.

1. Вкладка Число – задает форматы представления данных в ячейке:

· Общий – обеспечивает отображение числовых и текстовых данных произвольной типа;

· Числовой – включает цифры и символы-разделители: десятичная точка, процент знак мантиссы, знак числа, круглые скобки, денежное обозначение (р. или $);

· Денежный или Финансовый – для отображения денежных величин;

· Дата/время – для отображения даты и времени в выбранном формате;

· Процентный – для вывода чисел, предварительно умноженных на 100, с символом процента;

· Дробный – для вывода дробных чисел;

· Экспоненциальный – для вывода чисел в экспоненциальном формате, например 1,65Е+044;

· Текстовый – последовательность букв, цифр, специальных символов;

· Дополнительный – нестандартные дополнительные форматы, например номер телефона, почтовый индекс и пр.

· Все форматы – показывает все имеющиеся в Excel форматы.

2. Вкладка Выравнивание определяет:

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

· Отображение – определяет, можно ли переносить в ячейке текст по словам, разрешает или запрещает объединение ячеек, задает автоподбор ширины ячейки.

3. Вкладка Шрифт – изменяет шрифт, начертание, размер, цвет, подчеркивание и эффекты текста в выделенных ячейках;

4. Вкладка Граница – создает рамки (обрамление) вокруг выделенного блока ячеек;

5. Вкладка Вид – позволяет задать закраску ячейки (цвет и узор);

6. Вкладка Защита – управляет скрытием формул и блокировкой ячеек (запрет редактирования данных ячеек). Устанавливать защиту можно в любой момент, но действовать она будет только после того, когда введена защита листа или книги с помощью команды Сервис/Защитить лист.

В операциях обработки часто используется не отдельная ячейка, а блок ячеек.

Блок – прямоугольная область смежных или несмежных ячеек, расположенных в разных местах.

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

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

1. Запустите табличный процессор Microsoft Excel.

2. Создайте приведённую ниже таблицу. Оформите её с помощью рамок и затенения ячеек.

 

Рис.2 Образец таблицы

Заполнение таблицы.

1.1. В ячейку D1 ввести текст «Сенокосов» (без кавычек!). В ячейку E2 - текст «в том числе» (без кавычек!). В ячейки F3, K3, O3 ввести одинаковый текст «из них» (без кавычек!).

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

A4	Наименование землепользователя
B4	Общая площадь
C4	Пашня
D4	Всего сенокосов
E4	Заливных
F4	Улучшенных
G4	Чистых
H4	Заросших
I4	Покрытых кочками
J4	Суходольных
K4	Улучшенных
L4	Чистых
M4	Заросших
N4	Заболоченных
O4	Чистых
P4	Заросших
Q4	Покрытых кочками

1.3. Установить для ячеек 4-й строки выравнивание текста по вертикали. Для этого:

¨ выделить ячейки с A4 по Q4;

¨ выбрать в контекстном меню (появляется при нажатии правой клавиши мыши) Формат Ячеек, закладка Выравнивание;

¨ установить Ориентацию текста 90 градусов;

¨ нажать Enter;

¨ не снимая выделения, выбрать на ленте Главная, в разделе Ячейки,команду Формат/Автоподбор ширины для уменьшения ширины выделенных ячеек.

1.4. Установить текст «Сенокосов» по центру соответствующих столбцов. Для этого:

¨ выделить ячейки c D1 по Q1;

¨ выбрать в контекстном меню (появляется при нажатии правой клавиши мыши) Формат Ячеек, закладка Выравнивание;

¨ установить Выравнивание по центру

¨ нажать Enter.

1.5. По аналогии с предыдущим пунктом установить текст «в том числе» по центру ячеек с E2 по Q2.

1.6. Установить текст «из них» по центру ячеек с F3 по I3. Для этого:

¨ выбрать в контекстном меню (появляется при нажатии правой клавиши мыши) Формат Ячеек, закладка Выравнивание;

¨ установить Выравнивание по горизонтали по центру выделения;

¨ нажать Enter.

1.7. Установить текст «из них» по центру ячеек с K3 по M3. Для этого:

¨ выбрать в контекстном меню (появляется при нажатии правой клавиши мыши) Формат Ячеек, закладка Выравнивание;

¨ установить Выравнивание по горизонтали по центру выделения;

¨ нажать Enter.

1.8. Установить текст «из них» по центру ячеек с O3 по Q3. Для этого:

¨ выбрать в контекстном меню (появляется при нажатии правой клавиши мыши) Формат Ячеек, закладка Выравнивание;

¨ установить Выравнивание по горизонтали по центру выделения;

¨ нажать Enter.

1.9. Заполнить строки «Фермер 1», «Фермер 2», «Фермер 3» согласно вышеприведённой таблице-образцу (включая числовые данные).

1.10. Настроить ширину каждого столбца для оптимального расположения содержимого, перетаскивая мышью границы столбцов на горизонтальной линейке, содержащей имена столбцов (A, B, C,...) или двойным нажатием левой клавиши мыши на границе столбцов.

1.11. В перечисленные ниже ячейки ввести следующие формулы (начиная от знака =):

¨ N5: =СУММ(O5:Q5), для ввода формулы необходимо выделить ячейки O5:Q5,затем нажать кнопку с символом ∑, расположенную на ленте Главная, затем размножить формулу по столбцу;

¨ J5: =СУММ(K5:M5), размножить формулу по столбцу;

¨ E5: =СУММ(F5:I5), размножить формулу по столбцу;

¨ D5: = E5+J5+N5, для ввода формулы необходимо установить курсор в ячейку D5, ввести знак «=»,затем кликнуть мышью по ячейке E5 и ввести символ «+»,затем кликнуть мышью по ячейке J5 и ввести символ «+», затем кликнуть мышью по ячейке N5 и нажать Enter. Размножить формулу по столбцу;

¨ B5: =СУММ(C5:D5), размножить формулу по столбцу;

· В случае необходимости настроить ширину столбцов.

1.12. Выровнять вертикальные надписи. Для этого:

¨ выделить диапазон ячеек с А1 по А4;

¨ выбрать в контекстном меню (появляется при нажатии правой клавиши мыши) Формат Ячеек, закладка Выравнивание;

¨ поставить маркеры напротив позиций Объединение ячеек и Переносить по словам;

¨ нажать Enter.

¨ повторить ту же операцию для следующих диапазонов: B1:B4, C1:C4, D2:D4; E3:E4, J3:J4; N3:N4.

Расчертить таблицу

1.13. Для этого:

¨ выделить диапазон ячеек A1:Q7;

¨ выбрать в контекстном меню (появляется при нажатии правой клавиши мыши) Формат Ячеек, закладка Граница;

¨ нажать мышью кнопку Внутренние;

¨ выбрать в поле Тип линии двойную тонкую линию;

¨ нажать мышью кнопку Внешние;

¨ нажать Enter.

1.14. Выполнить заливку заголовков столбцов. Для этого:

¨ выделить диапазон ячеек A1:Q4;

¨ выбрать в контекстном меню (появляется при нажатии правой клавиши мыши) Формат Ячеек, закладка Вид;

¨ выбрать цвет из предложенной палитры;

¨ нажать Enter;

¨ снять выделение с ячеек.

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

Вычисления в электронных таблицах производятся по формуле.

Формула – это выражение, определяющее вычислительные действия.

Как правило, формула включает переменные, константы, функции, объединенные знаками арифметических операций и скобками. В Excel переменные – это адреса ячеек.

Формулы в ЭТ Excel начинаются со знака равенства!!!

Для создания формулы необходимо:

1. Выделить ячейку, в которую нужно ввести формулу.

2. Нажать клавишу =.

3. Ввести аргумент (набрать с клавиатуры или щелкнуть мышью по ячейке).

4. Ввести знак арифметического действия.

5. Ввести следующий аргумент. И т.д.

6. Закончить ввод формулы, нажав клавишу ввода.

В строке формул отображается формула, а в ячейке – результат расчета по этой формуле.

Формулу можно скопировать через буфер обмена:

1. Поставить курсор в клетку, ОТКУДА будем копировать.

2. Нажать кнопку «копировать» на панели инструментов.

3. Поставить курсор в клетку, КУДА будем копировать.

4. Нажать кнопку «вставить» на панели инструментов.

При копировании (размножении) формул предусмотрена их АВТОМАТИЧЕСКАЯ НАСТРОЙКА. Это означает, что один раз введенную формулу, например, в верхнюю ячейку столбца, можно скопировать в другие клетки, расположенные ниже, причем обозначения клеток, используемые в формуле (ссылки), автоматически меняются: при копировании по вертикали изменяются номера строк, а при копировании по горизонтали изменяются номера столбцов.

Этот сервис - одна из важнейших черт всех систем электронных таблиц.

Такие ссылки на адрес ячейки называются ОТНОСИТЕЛЬНЫМИ.

Кроме копирования через буфер обмена, в Excel есть возможность АВТОЗАПОЛНЕНИЯ ЯЧЕЕК. Если выделить некоторую ячейку, то в правом нижнем углу рамки есть маленький черный квадратик (Маркер заполнения). Курсор, попав на него, принимает форму маленького черного крестика. Схватившись мышкой за этот крестик и протягивая мышь вниз, мы копируем формулы, а в ячейках таблицы появляются результаты расчетов.

2. Абсолютные ссылки. Имя ячейки

Если при копировании формулы не должен изменяться номер строки и (или) номер столбца, то перед соответствующим номером в адресе ячейки нужно поставит значок «$». Такие ссылки называются АБСОЛЮТНЫМИ.

Часто применяют не чисто относительные или абсолютные ссылки, а смешанные, например, B$1 или $B1. Номер, перед которым не стоит знак «$», будет изменяться при копировании, а тот номер, перед которым знак «$» стоит, останется без изменения.

Чтобы в строке формулы оказалась ссылка $B$1, или $B1, или B$1 достаточно щелкнуть мышью по ячейке В1 и нажимать клавишу F4.

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

Чтобы присвоить ячейке имя, нужно ее выделить, перейти в контекстном меню (появляется при нажатии правой клавиши мыши) выбрать Имя диапазона. В открывшемся окне компьютер предлагает вам ввести имя ячейки. По умолчанию - текст из соседней ячейки. Вы можете согласиться или изменить имя. Имя отображается в адресном поле (поле имени).

Имя не должно содержать пробелов и знаков пунктуации, а также начинаться с цифры. Например, имя «курс доллара» недопустимо. Можно использовать имя «курс» или «курс_доллара».

Использование имени ячейки соответствует действию абсолютной ссылки.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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» т.е. в качестве критерия выделяется ячейка, где стоит оценка 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, однако, можно и больше, все определяется памятью и системными ресурсами.

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

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

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

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

 

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

Задание № 1. Работа с листами, связи между листами.

Задание 2 Построение графика функции

Составьте таблицу значений линейной функции y=3,5x-9,5. Используйте возможности автозаполнения и копирования при создании таблицы. Постройте график этой функции.

 

1. Откройте новую книгу и создайте следующую таблицу:

2. Выполните необходимые вычисления в ячейках В5:L5, в соответствии с формулой линейной функции y=3,5x-9,5

3. Постройте график, для этого:

- выделите диапазон А4:L5;

- вставка/график/график;

-отредактируйте график, для этого выполните команду конструктор/данные/выбрать данные

- в диалоговом окне «Выбор источника данных»(рис) удалите в поле «Элементы легенды» удалите ряд Х

Рис. Диалоговое окно Выбор источника данных

 

- в диалоговом окне «Выбор источника данных» в поле «Подписи горизонтальной оси» нажмите кнопку «Изменить», в открывшемся диалоговом окне введите ячейки В4:L4;

- выполните дальнейшее редактирование графика в соответствии с рисунком:

Рис. График функции

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

Задание 1

Самостоятельно построить график квадратичной функции y = ax²+b, где коэффициенты функции выбрать в соответствии с последнее и предпоследней цифрами студенческого билета. Координаты Х выбрать таким образом, чтобы была видна вершина параболы и симметрично ее ветви.

Задание 2

Вычислите таблицу значений функции f(x,y)=x²-y², где х меняется от –2 до 3 с шагом 0.25, а y от 0 до 2 с шагом 0.1. Результаты отобразите с тремя знаками после точки. Постройте график поверхности.

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

1. Как добавить, переименовать рабочий лист?2

2. Какие виды диаграмм вы знаете?

3. Порядок построения диаграмм.

4. Как выполнить ссылку на ячейку с другого рабочего листа?

 

Лабораторная работа № 5
Работа со списками. Структурирование рабочих таблиц. Подведение итогов для данных, расположенных в смежных диапазонах

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

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

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

· Фильтрация данных в списке – это выбор данных по заданному критерию (условию). Имеются две разновидности этой команды, задаваемые параметрами: Автофильтр и Расширенный фильтр. При выборе команды Данные/сортировка и фильтр/Фильтр автоматически устанавливается автофильтр.. Для обращения к расширенному фильтру следует выбрать команду Данные/сортировка и фильтр/дополнительно

· Сортировка списка. Сортировка – это расположение данных в определенном порядке по возрастанию или убыванию. Сортировка выполняется командой Данные/ сортировка и фильтр/ Сортировка.

· Вставка формул для подведения промежуточных итогов.

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

· Создание с помощью сводной таблицы итоговой таблицы данных списка.

Подведение промежуточных итогов в смежных диапазонах осуществляется через команду меню Данные/Структура/промежуточные итоги.

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

1. Ввести список «Периферия», представленный ниже.

 

Товар	Тип	Наименование	Цена	Кол- во	Сумма
Сканер	Листовой	Paragon Page Easy
Сканер	Планшетный	Paragon 1200 SP
Сканер	Планшетный	ScanExpress A3 P
Принтер	Струйный	Epson Stylus Photo 700
Принтер	Лазерный	HP LaserJet 4000
Принтер	Лазерный	HP LaserJet Color 8500
Сканер	Листовой	Paragon Page 630
Сканер	Планшетный	Paragon 800IIEP
Принтер	Матричный	Epson LX-1050+
Принтер	Лазерный	HP LaserJet 5000
Сканер	Планшетный	ScanExpress 6000 SP
Принтер	Струйный	Epson Stylus-1500
Принтер	Матричный	Epson LQ-2170
Принтер	Матричный	Epson LQ-100
Принтер	Струйный	Epson Stylus-1520

 

1. Подсчитайте сумму для каждого товара по формуле: Цена*Кол-во.

2. Отсортируйте записи по Товару в порядке возрастания, по полю Тип в порядке возрастания командой Данные/Сортировка.

3. Используя автофильтр Данные/сортировка и фильтр/Фильтр, отберите записи:

· По полю Тип: Лазерные принтеры. Для этого перейдите на поле Тип. Раскройте список фильтрации и выберите Лазерные. На экране остались только данные по лазерным принтерам. Отмените фильтр (Данные / сортировка и фильтр /Очистить).

· По полю Товар: Сканеры. Выполнить самостоятельно! Отмените использование фильтра.

· По полю Кол-во: больше 3. Раскройте список фильтрации и выберите Числовые фильтры. Выберите из списка «больше». В соседнем поле задайте значение 3. ОК.

 

Отмените использование фильтра.

4. используя Расширенный фильтр Данные /Сортировка и фильтр/дополнительно найдите сканеры, которых больше 1. Для этого под таблицей запишем условия отбора Товар – Сканер, Кол – во > 1. Выполним команду Данные /Сортировка и фильтр/дополнительно. Выполним обработку, как показано на рисунке ниже.

 

Результат:

 

 

Самостоятельно с помощью расширенного фильтра выберите Лазерные принтеры с ценой больше 1500 руб.

5. Создайте промежуточные итоги командой Данные/структура/промежуточные итоги.

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

1) Отсортируйте таблицу по товару.

2) В меню Данные / структура выберите промежуточные итоги. Откроется диалоговое окно Промежуточные итоги.

3) Для того, чтобы подвести итоги по каждому товару (отдельно принтеры и отдельно сканеры), в списке При каждом изменении в выберите Товар

4) Убедитесь, что в окне Операция выбрана Сумма

5) Для того, чтобы просуммировать показатели количества товара и сумм, затраченных на покупку, установите флажки в поле Добавить итоги по напротив строк Кол-во и Сумма.

6) Проверьте, что напротив строк Заменить текущие итоги и Итоги под данными установлены флажки и нажмите ОК.

7) Таким образом, вы получите итоговые значения количества и суммы для каждого типа товара.

8) Для удаления промежуточных итогов выберите команду Данные/структура/промежуточные итоги и щелкните кнопку Убрать все. Таблица вернется в исходное состояние.

5.2. Найдите среднее значение цены отдельно для всех принтеров и всех сканеров, а также общее среднее значение цены. Для этого:

1) Отсортируйте таблицу по товару.

2) Данные è Промежуточные итоги:

3) При каждом изменении è Товар

4) Операция èСреднее

5) Добавить итоги по èЦена

6) В конце не забудьте убрать промежуточные итоги.

5.3. Вычислите число разновидностей принтеров и сканеров. Для этого

1) Отсортируйте таблицу по товару.

2) Данные è Промежуточные итоги:

3) При каждом изменении è Товар

4) Операция èКол-во значений

5) Добавить итоги по èНаименование

6) В конце не забудьте убрать промежуточные итоги.

5.4. Самостоятельно вычислите среднее значение сумм, потраченных на покупку всех принтеров и всех сканеров.

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

В конце не забудьте убрать промежуточные итоги.

5.5. Вычислите суммы, потраченные на покупку каждого типа товара (каждой разновидности принтеров и сканеров).

1) Отсортируйте таблицу по типу принтеров.

2) Данные è Промежуточные итоги:

3) При каждом изменении è Тип

4) Операция èСумма

5) Добавить итоги по èСумма

6. Часто при составлении итогового отчета нужно видеть только итоги, а остальные записи скрыть. Для этого в колонках с кнопками 1 2 3 можно поменять знак - на +. Такое можно провести для каждого уровня.

 

Воспользуйтесь результатами предыдущего задания.

1) Скройте результаты второго уровня: Щелкните по кнопке 2.

2) Для возврата таблицы в исходный вид щелкните по кнопке того уровня, который вы хотите показать (кнопка 3)

3) Попробуйте по очереди скрывать отдельные записи второго уровня (нажимайте на кнопку -)

4) Для возврата - на кнопку +.

5) Попробуйте поработать с кнопкой 1.

6) Верните таблицу в исходное состояние.

7) Уберите промежуточные итоги.

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

1. Как работать с формой?