Лабораторные работы по excel для магистров. Лабораторная работа по информатике на тему: excel. специальности “Эксплуатация железных дорог “

Лабораторная работа № 1

Создание списка клиентов

Введите список 15 фирм. Фирмы распределите по 5 городам. Набрав первую запись нажмите на кнопку Добавить.

Форматирование таблицы . Для ячеек

Процентный формат

Форматирование

Сортировка данных. Необходимовыбрать в меню Данные  Сортировка. В диалоговом окне выбрать первый критерий сортировки Код и второй критерий Город и ОК.

Фильтрация данных. Выбрать в меню Данные  Фильтр/Атофильтр. После щелчка на имени этой команды в первой строке рядом с заголовком каждого столбца появиться кнопка со стрелкой. С ее помощью можно открыть список, содержащий все значения полей в столбце. Выберите название одного из городов в Город. Кроме значений полей, каждый список содержит еще три элемента: (Все), (Первые 10…) и (Условие…). Элемент (Все) предназначен для восстановления отображения на экране всех записей после применения фильтра. Элемент (Первые 10…) обеспечивает автоматическое представление на экране десяти первых записей списка. Если вы занимаетесь составлением всевозможных рейтингов, главная задача которых состоит в определении лучшей десятки, воспользуйтесь этой функцией. Последний элемент - используется для формирования более сложного критерия отбора, в котором можно применить условные операторы И и ИЛИ .

Установите курсор в любую заполненную ячейку и выполните следующие действия: в меню Формат  Автоформат  Список 2 .

Создание списка товаров

Второй список будет содержать данные о предлагаемых нами товарах.

Лабораторная работа № 2

Лист Заказы

Переименуйте рабочий лист ЛистЗ на имя Заказы .

Введите в первую строку следующие данные, которые будут в дальнейшем именами полей:
А1  Месяц заказа , В1  Дата заказа , С 1  Номер заказа , D 1  Номер товара , Е1  Наименование товара , F 1  Количество , G 1  Цена за ед ., H 1  Код фирмы заказчика ., I 1  Название фирмы заказчика , J 1  Сумма заказа , К1  Скидка(%) , L 1  Оплачено всего .

Для первой строки выполните выравнивание данных по центру Формат  Ячейки  Выравнивание  переносить по словам .

Выделите по очереди столбцы B, C, D, E, F, G, H, I, J, K, L и введите в поле имени имена Дата, Заказ, Номер2, Товар2, Количество, Цена2, Код2, Фирма2, Сумма, Скидка2 и Оплата .

Выделите столбец В и выполните команду меню Формат  Ячейки . Во вкладке Число выберите
Числовой формат Дата , а в поле Тип выберите формат вида ЧЧ.ММ.ГГ. В завершении диалога
щелкните кнопку ОК.

Выделите столбцы G , J , L и выполните команду меню Формат  Ячейки . Во вкладке Число
выберите Числовой формат Денежный , укажите Число десятичных знаков равное 0, а в поле
Обозначение выберите $ Английский (США). В завершении диалога щелкните кнопку ОК .

Выделите столбец К и выполните команду меню Формат  Ячейки . Во вкладке Число выберите
Числовой формат Процентный , укажите Число десятичных знаков равное 0. В завершении
диалога щелкните кнопку ОК .

В ячейке А2 нужно набрать следующую формулу:

=ЕСЛИ(ЕПУСТО($В2);« »;ВЫБОР(МЕСЯЦ($В2);«Январь»;«Февраль»; «Март»; «Апрель»; «Май»;«Июнь»;«Июль»;«Август»;«Сентябрь»;«Октябрь»;«Ноябрь»;«Декабрь»)) (3.1)

И залить ячейку в желтый цвет.

Формула (3.1) работает следующим образом, вначале проверяется условие на пустоту ячейки А2. Если ячейка пусто, то ставится пробел, в противном случае с помощью функции ВЫБОР выбираем нужный месяц из списка, номер которого определяется функцией МЕСЯЦ.

Для того, чтобы набрать формулу (3.1) выполните следующие действия:

сделайте активной ячейку А2 и вызовите функцию ЕСЛИ ;

в окне функции ЕСЛИ в поле Логическое_выражеиие напечатайте вручную $ B2= «», в

поле значепие_если_истина наберите « », в поле значение_еслн_ложь вызовите функцию ВЫБОР;

в окне функции ВЫБОР в поле значение1 напечатайте «Январь», в поле значение2 напечатайте

в поле номер_индекса и вызовите функцию МЕСЯЦ ;

в окне функции МЕСЯЦ в поле Дата_как_число наберите адрес $ B 2 ;

Щелкните кнопку ОК .

В ячейку Е2 набираем следующую формулу:

=ЕСЛИ($ D2=« »; “ ”;ПРОСМОТР($D2;Номер товара; Наименование товара) (3.2)

Правило набора формулы:
Щелкните в ячейку Е2. Установите курсор на значок Стандартной панели. Откроется окно Мастер функции …, выберите функцию ЕСЛИ. Выполните действия, которые видите на рисунке
Т.е. в позиции Лог_выражение щелкните на ячейку D2 и три раза нажмите на клавишу F4 - получите $D2, наберите =« », клавишей Tab или мышью перейдите в позицию Значение_если_истина и наберите. « », перейдите в позицию Значение_если_ложь – щелкните на кнопку рядом с названием функции и выберите команду Другие функции.. → Категории → Ссылки и массивы, в окне Функции → ПРОСМОТР → ОК→ ОК.

Откроется окно функции ПРОСМОТР . В позиции Искомое_значение щелкните на ячейку D2 и три раза нажмите на клавишу F4 - получите $D2, клавишей Tab или мышью перейдите в позицию Просматриваемый_вектор и щелкните на ярлык листа «Товары », выделите диапазон ячеек А2:А12 , нажмите на клавишу F4, перейдите в позицию Вектор_результатов – еще раз щелкните на ярлык листа «Товары », выделите диапазон ячеек В2:В12 , нажмите на клавишу F4, и ОК. Если выполнили все верно – появится в ячейке # HD .

делайте заливку ячейки желтым цветом.

10. В ячейку G 2 набираем следующую формулу:

=ЕСЛИ($ D 2=« »;« »;ПРОСМОТР($ D 2;Номер товара; Цена)) (3.3)

Сделайте заливку ячейки желтым цветом.

11. В ячейку I 2 набираем следующую формулу:
=ЕСЛИ($Н2=« »;« »;ПРОСМОТР($ H 2;Код; Фирма)) (3.4)
Сделайте заливку ячейки желтым цветом.

12. В ячейку J 2 набираем следующую формулу:
=ЕСЛИ(F 2=« »;« »; F 2* G 2) (3.5)
Сделайте заливку ячейки желтым цветом..

13. В ячейку K 2 набираем следующую формулу:
=ЕСЛИ($Н2=« »;« »;ПРОСМОТР($ H 2;Код; Скидка)) (3.6)
Сделайте заливку ячейки желтым цветом.

14. В ячейку L 2 набираем следующую формулу:
=ЕСЛИ(J 2=« »;« »; J 2- J 2* K 2) (3.7)
Сделайте заливку ячейки желтым цветом.

15. Ячейки В2 , D2 и Н2 – в которых нет формул, залить голубым цветом. Выделите диапазон А2 – L 2 и маркером заполнения (черный крестик в правом нижнем углу блока ) протянуть заливку и формулы до 31 строки включительно..

16. Сделайте активной ячейку В2 и протяните вниз маркером заполнения до ячейки ВЗ1 включительно.

17. В ячейку С2 напечатайте число 2008-01, которое будет начальным номером заказа и протяните вниз маркером заполнения до ячейки C З1 включительно.

18. Теперь необходимо заполнить с клавиатуры столбцы В2:В31 , D 2: D 31 и Н2:Н31 . С В2 по В11 набираем январские даты (например, 2.01.08, 12.01.08). С В12 по В21 набираем февральские даты (например, 12.02.08, 21.02.08) и с В22 по В31 набираем мартовские даты (например, 5.03.08, 6.03.08). В D 2: D 31 набираем номера товаров т.е. 101, 102, 103, 104, 201, 202, 203, 204, 301, 302 и 303. Номера могут повторяться и идти в любом порядке, аналогично в Н2:Н31 вводим Коды ваших фирм, которые у вас набраны на листе Клиенты. В столбец F вводим двузначные числа.

19.

(СРСП) Лабораторная работа № 3

Бланк Заказа

В ячейку Н5 введите запись Код , а в ячейку

=ЕСЛИ($

$3=“ ”; “ ”;ПРОСМОТР($

$3;Заказ; Код2))

В ячейку С7 введите запись Наименование товара.

=ЕСЛИ($

$3=“ ”; “ ”;ПРОСМОТР($

$3;Заказ; Товар2)),

В ячейку Н7 введите символ № , а в ячейку

=ЕСЛИ($

$3=“ ”; “ ”;ПРОСМОТР($

$3;Заказ; Номер2))

В ячейку С9 введите запись Заказываемое количество.

В ячейку Е9 –формулу
=ЕСЛИ($ E

$3=“ ”; “ ”;ПРОСМОТР($

$3;Заказ; Количество))

ед. по цене

Ячейка Н9 должна содержать формулу
=ЕСЛИ($ E

$3=“ ”; “ ”;ПРОСМОТР($

$3;Заказ; Цена2)),

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

за ед.

Введите в С11 текст Общая стоимость заказа , а в Е11 поместите формулу
=ЕСЛИ($ E

$3=“ ”; “ ”;ПРОСМОТР($

$3;Заказ; Сумма)),

Скидка(%)

11,

11, Н11

Объединить и поместить в центре

=ЕСЛИ($

$3=“ ”; “ ”;ПРОСМОТР($

$3;Заказ; Скидка2)),

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

В ячейку С13 –текст К оплате. А в ячейке

=ЕСЛИ($

$3=“ ”; “ ”;ПРОСМОТР($

$3;Заказ; Оплата)),

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

В ячейку Е13 введите запись Оформил(а): , выделитеЕ13 ,

13, Н13,

В завершение установите ширину столбцов

Теперь в Е3 укажите Номер заказа , и перед печатью бланка свою фамилию .

Вы с успехом выполнили работу, сдайте ее преподавателю!.

Сводная таблица

Создан список заказов для практического применения и его данные подлежать анализу. Поможет нам выполнить анализ Мастер сводных таблиц.

Сводные таблицы создаются на основе списка или базы данных.

8. Вы с успехом выполнили работу, сдайте ее преподавателю!.

(СРСП) Лаб. № 4. Филиалы

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

Подготовительный этап. Скопируйте в буфер обмена с листа Товары книги Заказы данные о товарах, их номерах и ценах, т.е. скопируйте диапазон ячеек А1-С12 листа Товары.

Перейдите к первому листу книги Филиалы и в ячейку А3 вставьте скопированный фрагмент таблицы. В 3 строе в ячейки D 3, E 3, F 3 введите соответственно записи Количество заказов, Проданное количество и Объем продаж . Задайте центрирование текста в ячейках и разрешите перенос текста по словам.

В ячейку F 4 поместите формулу: =С4*Е4 и скопируйте ее в ячейки F 5- F 14 .

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

Таких листов должно быть столько, сколько у вас было городов в листе Клиенты . Мы должны скопировать этот лист 4 раза.

Для этого установите курсор мыши на его ярлычке и нажмите правую кнопку манипулятора. В контекстном меню выберите команду Переместить/скопировать , в появившемся диалоговом окне укажите лист, перед которым должна быть вставлена копия, активизируйте опцию Создать копию и нажмите ОК . Намного проще копировать с помощью мыши: установите указатель мыши на ярлычке листа и переместите его в позицию вставки копии, удерживая при этом нажатой клавишу [ Ctrl ] .

Имена рабочих листов соответствуют названиям городов с листа Клиенты , например, Алматы, Астана, Шымкент, Актау, Караганда или другие названия. Введите название филиала, соответствующего названию листа и в ячейку А1 данного листа.

Дополните лист Заказы еще одним столбцом. В ячейку М1 введите слово Город. В ячейку М2 введите формулу =ЕСЛИ(ЕПУСТО($ H 2);“ ”;ПРОСМОТР($ H 2;Код; Город)) , протяните эту формулу до строки 31 этого столбца.

Выбрать в меню Данные  Фильтр/Атофильтр. Выберите в столбце Город первый филиал. Данные столбца Количество листа Заказы будут внесены вами в столбец Проданное количество листа книги Филиалы, в строки соответствующие номерам товаров. Если проданы товары с одним номером в разные месяцы, то берется их суммарное количество. И так заполняются листы всех городов.

Консолидация данных. Скопируйте с первого листа книги Филиалы диапазон А3-В14 , перейдите в 6 рабочий лист и вставьте в ячейку А3 .

Приступаем к консолидации. Установите указатель ячейки в С3 и выберите в меню Данные  Консолидация.

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

Установите курсор ввода в поле Ссылка , выполните щелчок на ярлычке первого города, например –Алматы , выделить диапазон ячеек D 3- F 14 и нажать кнопку Добавить окна Консолидация . В результате указанный диапазон будет переставлен в поле Список диапазонов.

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

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

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

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

Нажмите кнопку ОК.

В ячейку А1 введите название новой таблицы Итоговые данные.

Введите в ячейку В70 значение Всего: , а в Е70 - и нажмите на клавишу [ Enter ]

Теперь приступаем к определению доли от общей прибыли суммы, вырученной от продажи каждого товара. Введите в F 9 формулу = Е9/$ E $70 и скопируйте ее в остальные ячейки столбца F (до ячейки F 70) .

Отформатируйте содержимое столбца F в процентном стиле. Полученные результаты позволяют сделать выводы о популярности того или иного товара.

При консолидации данных программа записывает в итоговой таблице каждый элемент и автоматически создает структуру документа, что позволяет добиться представления на экране только необходимой информации и скрыть ненужные детали. Слева от таблицы отображаются символы структуры. Цифрами обозначаются уровни структуры (в нашем примере – 1 и 2). Кнопка со знаком плюс позволяет расшифровать данные высшего уровня. Нажмите, например, кнопку для ячейки А9 , чтобы получить информацию об отдельных заказах.

Скопируйте формулу из F 9 в ячейки F 4- F 8.

Цифры в превращаются в Диаграммы

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

Откройте ранее созданную книгу Заказы. Создайте новую книгу и присвойте ее первому листу имя Таблица . Этот лист будет содержать числовой материал для диаграммы.

Поместите указатель в ячейку В3 и выберите меню Данные  Сводная таблица.

Выберите первый способ расположения данных – В списке или базе данных

Microsoft

Excel

Далее.

На втором шаге поместив курсор ввода в поле Диапазон следует с помощью меню Окно перейти в рабочую книгу Заказы и в рабочем листе Заказы и выделить диапазон

Далее

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

Выделите диапазон

Щелкните на кнопке Мастер диаграмм в панели инструментов Стандартная.

На первом шаге укажите тип диаграммы, нажмите на кнопку Далее.

На втором шаге подтвердите диапазон =Таблица!$

$4:$

$15.

На третьем шаге указываете параметры диаграммы (Заголовки, Оси, Легенды и т.д.). Название диаграммы введите Объем продаж по месяцам, Категории (Х)- Наименование товара иЗначение(

)

Объем продаж(

USD

)

Внесенные изменения

Образец,

Далее.

Нажмите на кнопку Готово.

МИНИСТЕРСТВО СЕЛЬСКОГО ХОЗЯЙСТВА РОССИЙСКОЙ ФЕДЕРАЦИИ

ФГБОУ ВО «ВЯТСКАЯ ГОСУДАРСТВЕННАЯ

СЕЛЬСКОХОЗЯЙСТВЕННАЯ АКАДЕМИЯ»

Кафедра информационных технологий и статистики

Ливанов Р.В.

Практикум по работе в электронной таблице Microsoft Office Excel 2007

для студентов экономического факультета

КИРОВ Вятская ГСХА

Практическая часть

Лабораторная работа №1.

Общее знакомство с Microsoft Excel

Запустите программу Microsoft Excel: «Пуск» «Все программы» «Microsoft Office» «Microsoft Office Excel 2007» или при помощи соответствующего ярлыка на рабочем столе. В результате откроется новая рабочая книга, содержащая несколько рабочих листов.

В открывшемся окне найдите следующие элементы:

Кнопки управления

Лента инструментов

Полосы прокрутки

Строка заголовка

Адрес активной ячейки

Рабочая область листа

Панель быстрого

Активная ячейка

13. Зона заголовков

столбцов

Кнопка Office

Зона заголовков строк

Строка формул

Вкладки на ленте

10. Ярлыки рабочих

Кнопка вставки

Задание 1. Основы работы с электронными таблицами.

1. Переименуйте название рабочего листа.

Щелкните ПКМ по ярлыку «Лист1» в нижней части рабочего листа и в контекстном меню выберите команду «Переименовать» .

Удалите старое название рабочего листа, введите с клавиатуры новое название «Принтеры» и нажмите клавишу Enter .

2. Подготовьте ячейки таблицы к вводу исходных данных.

Выделите диапазон ячеек A1:D1 и задайте команду контекстного меню

«Формат ячеек».

«переносить по словам» и выберите тип горизонтального и вертикального выравнивания – по центру .

На вкладке «Шрифт» диалогового окна выберите тип начертания шрифта –

полужирный курсив и нажмите кнопку «ОК» .

3. Заполните таблицу данными по предложенному ниже образцу.


		Наименования	Количество,



		Принтер лазерный, ч/б

		Принтер лазерный, цв.

		Принтер струйный, ч/б

		Принтер струйный, цв.

		Принтер матричный, ч/б

4. Рассчитайте объем продаж как произведение количества и цены.

Выделите ячейку D2 и введите с клавиатуры знак = .

Щелкните ЛКМ по ячейке В2 , с клавиатуры введите знак * и щелкните ЛКМ по ячейке С2 . Если все сделано правильно, то в строке формул появится формула следующего вида: =В2*С2 .

Нажмите клавишу Enter – в ячейке появится результат расчета по формуле:

450000.

5. Откопируйте формулу в остальные ячейки столбца.

Выделите ячейку D2 , в которой находится результат вычислений.

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

Нажмите ЛКМ и, удерживая ее, протяните курсор до 6-й строки включительно. Если все сделано правильно, то все ячейки столбца «Объем продаж» будут заполнены рассчитанными значениями.

6. Установите для чисел в столбцах «Цена» и «Объем продаж» денежный формат.

Выделите диапазон ячеек С2:D6 и задайте команду контекстного меню

«Формат ячеек».

денежный , число десятичных знаков – 0 , обозначение – р. и нажмите кнопку «ОК» .

7. Вставьте в таблицу новый столбец.

Выделите щелчком ЛКМ любую ячейку первого столбца (например А2

или А3 ).

«Вставить столбцы на лист» – в результате слева от таблицы появится новый столбец.

В ячейку А1 введите заголовок нового столбца № п/п и установите для данной ячейки горизонтальное и вертикальное выравнивание – по центру ,

тип начертания шрифта – полужирный курсив .

8. Заполните столбец «№ п/п» с использованием автозаполнения.

В ячейку А2 введите цифру 1 , в ячейку А3 – цифру 2 .

Выделите диапазон ячеек А2:А3 .

Наведите курсор мыши на маркер заполнения в правом нижнем углу выделенных ячеек, нажмите ЛКМ и, удерживая ее, протяните курсор до 6-й

строки включительно. В результате в столбце появятся числа от 1 до 5 .

9. Вставьте в таблицу новую строку для оформления заголовка таблицы.

Выделите щелчком ЛКМ любую ячейку первой строки (например В1

или С1 ).

На вкладке «Главная» нажмите кнопку «Вставить» и в раскрывающемся списке выберите команду «Вставить строки на лист» – в результате сверху от таблицы появится новая строка.

Выделите диапазон ячеек A1:Е1 и задайте команду контекстного меню

«Формат ячеек».

В появившемся диалоговом окне на вкладке «Выравнивание» установите флажок «объединение ячеек» , выберите тип горизонтального выравнивания

– по центру .

На вкладке «Шрифт» выберите тип начертания шрифта – полужирный ,

цвет шрифта – красный и нажмите «ОК» .

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

10. Установите обрамление ячеек таблицы.

Выделите все ячейки таблицы за исключением ее заголовка (диапазон

А2:Е7 ) и задайте команду контекстного меню «Формат ячеек» .

В появившемся диалоговом окне на вкладке «Граница» выберите тип

11. Установите заливку ячеек таблицы.

Выделите шапку таблицы (диапазон А2:Е2 ) и задайте команду контекстного меню «Формат ячеек» .

В появившемся диалоговом окне на вкладке «Заливка» выберите какой-либо цвет заливки ячеек и нажмите «ОК» .

Аналогично выполните заливку оставшейся части таблицы с использованием различных цветов.

12. Постройте диаграмму по столбцам «Наименования товаров» и

«Количество».

Выделите диапазон ячеек В2:С7 , задайте команду «Вставка» «Гистограмма» и в раскрывающемся списке выберите вид гистограммы –

гистограмма с группировкой (первый шаблон в первой строке) – в

результате диаграмма построится.

На вкладке «Конструктор» нажмите кнопку «Строка/столбец» –

в результате на гистограмме изменится вид отображения рядов данных.

На вкладке «Макет» нажмите кнопку «Название диаграммы» , в

раскрывающемся списке выберите размещение названия «Над диаграммой»

и введите название диаграммы Принтеры .

Используя кнопку «Подписи данных» на вкладке «Макет» , установите

в диаграмме числовые подписи рядов данных с размещением «В центре» .

На вкладке «Конструктор» нажмите кнопку «Переместить диаграмму»,

в появившемся диалоговом окне выберите размещение диаграммы на отдельном листе и нажмите кнопку «ОК» – в результате в рабочей книге появится новый рабочий лист с названием «Диаграмма1» , на котором будет размещена диаграмма.

13. Рассчитайте строку «Итого» по столбцу «Объем продаж» .

Перейдите на рабочий лист «Принтеры» , содержащий таблицу с данными.

В ячейку В8 введите Итого , а в ячейках С8 и D8 поставьте прочерки.

Установите курсор в ячейку Е8 и щелкните по кнопке автосумма на вкладке «Главная» – в результате в ячейке появится формула

СУММ(Е3:Е7).

Нажмите клавишу Enter – в результате содержимое ячеек, охваченных пунктирной рамкой, будет просуммировано.

Установите для строки «Итого» обрамление и свой цвет заливки.

14. Измените данные в таблице по столбцу «Количество» .

Выделите ячейку С3 и введите в нее значение 30 – после нажатия клавиши

Enter произойдет автоматический пересчет значений в столбце «Объем продаж» .

Выделите ячейку С7 , введите в нее значение 7 и нажмите клавишу Enter .

Убедитесь, что в связи с изменением данных в таблице диаграмма перестроилась c учетом новых значений.

В результате всех вышеперечисленных действий отформатированная

таблица должна выглядеть следующим образом:



		Объем продаж принтеров
		Наименования	Количество,


		Принтер лазерный, ч/б
		Принтер лазерный, цв.
		Принтер струйный, ч/б
		Принтер струйный, цв.
		Принтер матричный, ч/б

Задание 2. Использование условного форматирования при расчетах.

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

«Финансы».

2. Выделите и объедините диапазон ячеек А1:Е1 , установите горизонтальное выравнивание – по центру и введите заголовок таблицы:

Движение денежных средств.

3. Выделите диапазон ячеек А2:Е9 и установите для выделенных ячеек внешние и внутренние границы, используя на вкладке «Главная» кнопку и шаблон «Все границы» .

4. Оформите шапку таблицы.

В ячейку А2 введите Месяц .

В ячейку В2 введите На начало периода.

В ячейку С2 введите Доходы .

В ячейку D2 введите Расходы .

В ячейку Е2 введите На конец периода.

Выделите диапазон ячеек А2:Е2 и установите для них отображение –

переносить по словам , горизонтальное и вертикальное выравнивание –

по центру , начертание шрифта – курсив .

5. Заполните данными столбец «Месяц» с использованием автозаполнения.

В ячейку А3 введите название месяца Январь .

Наведите курсор мыши на маркер заполнения ячейки А3 и, удерживая ЛКМ,

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

В ячейку А9 введите Итого за полугодие и установите для этой ячейки перенос по словам.

6. Заполните ячейки таблицы исходными числовыми данными.

В ячейку В3 введите значение 1000 .

Заполните данными столбцы «Доходы» и «Расходы» «На конец периода» и «На начало периода» .

Выделите ячейку Е3 и введите в нее формулу следующего вида: =B3+C3–D3

Нажмите клавишу Enter – при этом в ячейке появится результат расчета по формуле: 980 .

Выделите ячейку В4 и введите в нее формулу: =Е3

Откопируйте формулу в остальные ячейки этого столбца.

8. Установите для ячеек с числами в таблице денежный формат.

Выделите диапазон ячеек В3:Е8 и задайте команду контекстного меню

«Формат ячеек».

В диалоговом окне на вкладке «Число» выберите числовой формат –

денежный , число десятичных знаков – 0 , обозначение – $ и нажмите кнопку «ОК» .

9. Рассчитайте суммарные доходы и расходы за полугодие.

Выделите ячейку С9 , щелкните по кнопке автосумма на вкладке

«Главная» и нажмите клавишу Enter .

Откопируйте полученную функцию вправо по строке в ячейку D9 .

10. Рассчитайте финансовый результат деятельности.

Выделите диапазон ячеек А12:D12 , объедините их и установите горизонтальное выравнивание – по правому краю .

Введите в получившуюся ячейку: Финансовый результат: прибыль (+),

убыток (–).

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

Лабораторная работа № 3 (квартплата)

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

Создайте электронную таблицу учета платы за квартиру согласно образцу.

		Квартплата
Тариф за I кв. м:		5 р.
Срок оплаты:
Пени за I день:		1,5 р.		■■"■-■:":";"■"■ "■" ."

. № квартиры.	Фамилия квартиросъемщика	Площадь кв. м	Сумма	Дата оплаты	Просрочка	Штраф	Итого

Все заголовки столбцов должны быть выровнены по центру как по горизонтали, так и по вертикали, при определении формата ячейки примените опцию переноса слов.
Столбец «№ квартиры»: 10, 11, 12, ... 50. Центральное выравнивание.
Столбец «Фамилия квартиросъемщика»: Иванов, Петров, Сидоров, Краснов, Белов, все остальные Куропаткин 1, Куропаткин 2,
Столбец «Площадь»: 70; 69,5; 69 и т. д. (каждая следующая на 0,5 кв. м меньше предыдущей), выравнивание центральное.
Столбец «Сумма»: для каждой квартиры умножается значение из графы «Площадь» на значение из графы «Тариф» (которое может быть изменено учителем во время работы); формат рублевый без копеек.
Столбец «Дата оплаты»: с 10 февраля, каждая следующая квартира произвела оплату на день позже предыдущей. Формат Дата, полная форма.
Столбец «Просрочка»: если оплата была произведена до срока оплаты включительно, то автоматически ставится 0 (ноль); если позже срока, то должно подсчитываться количество дней просрочки («Дата оплаты» - «Срок оплаты»). Срок оплаты может меняться учителем во время работы с вашей таблицей. Данные выровняйте по центру.
Столбец «Штраф»: для каждой квартиры умножается значение из графы «Пени за 1 день» на значение из графы «Просрочка». Формат денежный без копеек.
Столбец «Итого»: суммируются значения из граф «Сумма» и «Штраф», формат денежный без копеек.

10. В конце ведомости должна автоматически подсчитываться следующая статистика по всем квартирам: общая сумма графы «Итого» (формат рублевый без копеек), Средняя площадь, Максимальная просрочка.

Министерство образования и науки

Российской Федерации

Федеральное государственное автономное образовательное учреждение

высшего профессионального образования

Национальный исследовательский ядерный университет «МИФИ»

Волгодонский инженерно-технический институт – филиал НИЯУ МИФИ

Создание таблиц

МЕТОДИЧЕСКИЕ УКАЗАНИЯ к лабораторной работе

по информатике в программе microsoft excel

Волгодонск 2010

УДК 519.683(076.5)

Рецензент канд. техн. наук З.О. Кавришвили

Составитель В.А. Булава

Создание таблиц . Методические указания к лабораторной работе в программе Microsort Excel. 2010. 13 с.

Методические указания содержат пояснения и рекомендации по выполнению лабораторной работы по курсу информатика в программе Microsort Excel.

_____________________________________________________________________________

ã Волгодонский институт НИЯУ МИФИ, 2010

ã Булава В.А, 2010

Лабораторная работа Создание таблиц в программе Excel с помощью автоматизации ввода данных.

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

Постановка задачи .

Вычислить значение функции y = f (x )/ g (x ) для всех х на интервале [ a , b ] с шагом к . Значение функций f (x ) , g (x ) , значение концов интервала a и b и значение шага к задается из таблицы 1 в Приложении согласно варианту для конкретной специальности.

Решение должно быть получено в виде таблиц «Основной» и «Вспомогательной».

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

Запуск программы Excel осуществляется с помощью команд Пуск → Программы → Microsort Excel .

При создании таблицы в первой строке объединить ячейки А1:Н1 и в центре расположить текст «Таблицы».

Во второй строке объединить ячейки А2:Е2 и в центре расположить текст «Основная». Объединить ячейки G2:H2 и в центре расположить текст «Вспомогательная»

В ячейку А3 ввести текст «№ п/п». В ячейках В3:F3 разместить соответственно названия столбцов: х ; f (x )=…(согласно своему варианту) ; g (x )=…(согласно своему варианту) ; y = f (x )/ g (x ).

В ячейках G3:H3 разместить соответственно названия столбцов: a ; к .

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

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

Цвет шрифта у названий таблиц должен быть синий.

Внешние границы таблиц окрасить в синий цвет, внутренние границы – в зеленый, заливку ячеек – в желтый.

Форма отчетности .

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

Печатный вариант отчета должен содержать:

а) титульный лист;

б) цель работы;

в) постановку задачи;

г) результат выполнения задания.

2. Результат выполнения лабораторной работы в электронном виде предоставить на дискете 3,5 дюйма в виде файла с именем «Таблицы».

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

Что такое абсолютная, относительная, смешанная адресации?

Каким образом происходит автозаполнение ячеек числами, формулами?

Какие существуют способы выравнивания содержимого ячейки?

Каким образом можно изменить цвет и толщину линий внешних и внутренних границ таблицы?

Каким образом можно изменить цвет фона ячеек таблицы?

Типовой пример.

Вычислить значение функции у = х∙sin(x)/(x+1) на отрезке с шагом 0,1. Решение предоставить в виде таблицы. Вычисленные значения функции у скопировать в столбец К без формул.

Решение.

В данном случае f (x ) = x ∙ sin (x ) , g (x ) = x +1 , a =0 , b = 2 , k = 0.1

1. В первой строке таблицы выделим ячейки А1:Н1. Выполним команду Формат → Ячейки , в открывшемся окне раскроем вкладку выравнивание и выберем пункт объединение ячеек . В центре объединенных ячеек введем текст «Таблицы».

2. Аналогичным образом во второй строке объединим ячейки А2:Е2 и в центре расположим текст «Основная» и объединим ячейки G2:H2, и в центре расположим текст «Вспомогательная».

3. В третьей строке в ячейке А3 введем текст № п/п (название первогостолбца таблицы) , в ячейке В3 – х (название второгостолбца таблицы), вячейке С3 – f (x )= x ∙ sin (x ) , в ячейке D3 – g (x )= x +1 , в ячейке Е3 – у= f (x )/ g (x ) , в ячейке G3 – a , в ячейке H3 – k .

4. В ячейку А4 введем 1 и заполним ячейки А5:А24 числами от 2 до 21. Для этого выделим ячейку А4 (сделаем ее текущей), она выделится в черную рамку. Наведем курсор мыши на маркер заполнения (черный крестик в правом нижнем углу ячейки) и нажав правую кнопку мыши протянем маркер заполнения вдоль столбца А таким образом, чтобы черная рамка охватила ячейки А5:А24. Отпустив правую кнопку мыши, в открывшемся меню выберем пункт заполнить . Ячейки А5: А24 заполнятся числами 2;3;4…

5. В ячейку G4 занесем значение 0 (значение левого конца интервала).

6. В ячейку Н4 занесем значение 0,1 (величина шага).

7. Заполним столбец В значениями х :

В ячейку В4 занесем формулу =$ G $4 (начальное значение х), знак $ указывает на абсолютную адресацию. В ячейку В5 занесем формулу =В4+$ H $4. Это означает, что начальное значение х будет увеличено на величину шага;

с помощью метода автозаполнения заполним этой формулой ячейки В5:В24. Выделим ячейку В5. Наведем указатель мыши на маркер заполнения и, нажав левую кнопку мыши, протянем маркер заполнения таким образом, чтобы черная рамка охватила ячейки В5:В24. Столбец В заполнится числами 0; 0,1; 0,2;…, а в строке формул будут соответствующие формулы.

8. Заполним столбец С значениями функции f(x)=x∙sin(x). В ячейку С4 занесем формулу =В4∙sin(B4). Заполним этой формулой ячейки С5:С24 с помощью метода автозаполнения.

9. Заполним столбец D значениями функции g(x)=x+1. В ячейку D4 занесем формулу =В4+1. Заполним этой формулой ячейки D5:D24 с помощью метода автозаполнения.

10. Заполним столбец E значениями функции y=f(x)/g(x). В ячейку E4 занесем формулу =C4/D4, заполним этой формулой ячейки E5:E24 с помощью метода автозаполнения.

11. Выполним обрамление таблиц:

12. Изменим цвет фона ячеек основной и вспомогательной таблиц:

выделим основную таблицу;

введем команды меню Формат → Ячейки → Вид. В открывшемся окне выберем цвет желтый. Щелкнем по кнопке ОК.

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

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

выделим ячейки Е4:Е24;

наведем указатель мыши на контур черной рамки таким образом, чтобы он принял вид стрелки;

нажав правую кнопку мыши и не отпуская ее переместим указатель мыши в ячейку К4;

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

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


Основная						Вспомогательная

путей сообщения»

Т.Г. ШАХУНЯНЦ

Методические указания

К лабораторным работам

По дисциплине

«Информатика»

Москва – 2014

Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования

«Московский государственный университет

путей сообщения»

Кафедра «Вычислительные системы и сети»

Т.Г. ШАХУНЯНЦ

Обработка данных средствами Microsoft Excel 2013

Университета в качестве методических указаний

Для студентов I курса

специальности “Эксплуатация железных дорог “

Москва – 2014

УДК 681.3

Шахунянц Т.Г. Обработка данных средствами Microsoft Excel 2013: Методические указания. – М.: МГУПС

(МИИТ), 2014. – 36 с.

Данные методические указания предназначены для выполнения лабораторных работ по изучению и освоению некоторых возможностей обработки данных в среде Microsoft Excel 2013. Для выполнения заданий к каждой из лабораторных работ приводятся соответствующие примеры.

Введение………………………………………………………...…4

1.Лабораторная работа №1………….…………………...............5

2.Лабораторная работа №2 ……………………....…………....…9

3.Лабораторная работа №3 ……………………..…………….…12

4.Лабораторная работа №4 ……………..…………………….…15

5.Лабораторная работа №5 ……………….……………….….…19

6.Лабораторная работа №6………………………………………21

7.Лабораторная работа №7……...…………………….………...25

8.Лабораторная работа №8……………...……..……………..…29

9.Литература…………….…………………………………........35

Введение

Microsoft Excel относится к программам, позволяющим обрабатывать данные, представленные в форме таблиц («программам «электронные таблицы»). Электронные таблицы широко применяются в экономических и научно-технических задачах для проведения однотипных расчётов над большими наборами данных, построения диаграмм и графиков по имеющимся данным, решения уравнений, поиска значений параметров в задачах оптимизации и т.п.

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

В описываемых лабораторных работах используется версия Microsoft Excel 2013, имеющая ряд усовершенствований, по сравнению с предыдущими.

Целью лабораторных работ является изучение и освоение некоторых возможностей обработки данных в среде Microsoft Excel 2013.

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

Лабораторная работа №1

Основы работы в среде Microsoft Excel 2013.

1.1. Цель работы

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

1.2. Задания к выполнению лабораторной работы

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

1.3. Подготовка к работе

Для выполнения работы следует ознакомиться с примером действий для выполнения заданий, рассмотренным в разделе 1.4

1.4. Пример действий по выполнению заданий к лабораторной работе

1. Запустите программу Excel (Пуск -> Все программы -> Microsoft Office->Microsoft Excel 2013).

2. Создайте новую книгу (Файл-> Создать).

3. Дважды щелкните на ярлычке текущего рабочего листа и дайте этому рабочему листу имя Данные.

4. Cохраните книгу под именем examples (Файл-> Сохранить как->Компьютер->Обзор->(Выбрать тип файла: Книга Excel).

5. Сделайте ячейку А1 активной и введите в нее заголовок “Результаты измерений”.

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

6. Введите 9 произвольных чисел в последовательные ячейки столбца А, начиная с ячейки А2, заканчивая ячейкой А10.

7. Введите в ячейку В1 строку “Утроенное значение”.

8. Введите в ячейку С1 строку “Куб числа”.

9. Введите в ячейку D1 строку “Квадрат следующего числа”.

10. Введите в ячейку В2 формулу = 3*А2.

11. Введите в ячейку С2 формулу =А2*А2*A2.

12. Введите в ячейку D2 формулу =A3*A3.

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

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

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

16. Введите в ячейку Е1 строку “Масштаб”.

17. Введите в ячейку Е2 число 5.

18. Введите в ячейку F1 строку “Масштабирование”.

19. Введите в ячейку F2 формулу =А2*Е2.

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

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

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

23. Повторите заполнение столбца F формулой из ячейки F2.

24. Убедитесь, что благодаря использованию абсолютной адресации значения ячеек столбца F теперь вычисляются правильно. Сохраните книгу examples.

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

1.В чем отличия ввода данных от записи формул?

2.Как осуществляется копирование формул методом автозаполнения?

3. Чем отличаются относительные и абсолютные ссылки по форме и по результатам их обработки?

Лабораторная работа №2

Использование стандартных и итоговых функций в среде Microsoft Excel.

2.1. Цель работы

Целью работы является изучение способов использования стандартных и итоговых функций.

2.2. Задания к выполнению лабораторной работы

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

2.3. Подготовка к работе

Для выполнения работы следует ознакомиться с примером действий для выполнения заданий, рассмотренным в разделе 2.4

2.4. Пример действий по использованию стандартных и итоговых функций.

1.Запустите программу Excel (Пуск -> Все программы -> Microsoft Office->Microsoft Excel2013) и откройте рабочую книгу examples, созданную ранее (Файл -> Открыть)

2. Выберите рабочий лист “Данные”(щёлкнув по вкладке на нижней панели) .

3. Сделайте активной ячейку А11.

4. Щелкните на кнопку “Автосумма” на вкладке «Формулы» или на стандартной панели значок «∑»

5. Убедитесь, что программа автоматически подставила в формулу функцию СУММ и правильно выбрала диапазон ячеек для суммирования. Нажмите клавишу ENTER.

6. Сделайте активной следующую свободную ячейку в столбце А.

7. Щелкните на кнопке “Вставить функцию” (Значок f X) на вкладке «Формулы».

9. В списке Функция выберите функцию СРЗНАЧ и щелкните на кнопке ОК.

10. Методом протягивания выберете ячейки от А2 до А10

11. Используя порядок действий, описанный в пп. 6-10, вычислите минимальное число в заданном наборе (функция МИН), максимальное число (МАКС), количество элементов в наборе

12. Сохраните книгу examples.

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

1. Способы использования стандартных функций.

2. Способы использования итоговых функций.

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

Лабораторная работа №3

Создание, форматирование и подготовка к печати документов.

3.1. Цель работы

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

3.2. Задания к выполнению лабораторной работы

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

3.3. Подготовка к работе

Для выполнения работы следует ознакомиться с примером действий для выполнения заданий, рассмотренным в разделе 3.4

3.4. Пример действий для выполнения лабораторной работы по созданию, форматированию и подготовке к печати документов.

1. Запустите программу Excel (Пуск> Все программы > Microsoft Office>Microsoft Excel 2013) и откройте книгу examples.

2. Выберите щелчком на ярлычке неиспользуемый рабочий лист или создайте новый (Горячая клавиша SHIFT + F11). Дважды щелкните на ярлычке нового листа и переименуйте его как Прейскурант.

3. В ячейку А1 введите текст “Прейскурант” и нажмите клавишу ENTER.

4. В ячейку А2 введите текст “Курс пересчета” : и нажмите клавишу ENTER. В ячейку В2 введите текст “1 у.е.=” нажмите клавишу ENTER. В ячейку С2 введите “Текущий курс пересчета” и нажмите клавишу ENTER.

5. В ячейку A3 введите текст «Наименование товара» и нажмите клавишу ENTER. В ячейку ВЗ введите текст «цена (у.е.)» и нажмите клавишу ENTER. В ячейку СЗ введите текст «Цена (руб.)» и нажмите клавишу ENTER.

6. В последующие ячейки столбца А введите названия товаров, включенных в прейскурант.

7. В соответствующие ячейки столбца В введите цены товаров в условных единицах.

8. В ячейку С4 введите формулу: =В4*$С$2, которая используется для пересчета цены из условных единиц в рубли.

9. Методом автозаполнения скопируйте формулы во все ячейки столбца С, которым соответствуют заполненные ячейки столбцов А и В.

10. Измените курс пересчета в ячейке С2. Обратите внимание, что все цены в рублях при этом обновляются автоматически.

11. Выделите методом протягивания диапазон А1:С1 и дайте команду в контекстном меню Формат ячеек. На вкладке “Выравнивание” задайте выравнивание: “По левому краю” и щелкните “Объединить по строкам”.

12. На вкладке Шрифт задайте размер шрифта в 14 и в списке “Начертание” выберите вариант “Полужирный”.

13. Щелкните правой кнопкой мыши на ячейке В2 и выберите в контекстном меню команду Формат ячеек. Задайте выравнивание по горизонтали: “По правому краю” и щелкните на кнопке ОК.

14. Щелкните правой кнопкой мыши на ячейке С2 и выберите в контекстном меню команду Формат ячеек. Задайте выравнивание по горизонтали: “По левому краю” и щелкните на кнопке ОК.

15. Выделите методом протягивания диапазон В2:С2. и выберите в контекстном меню команду Формат ячеек. На вкладке “Границы” задайте широкую внешнюю рамку.

16. Дважды щёлкните по границе между заголовками столбцов A и B, B и C, C и D. Обратите внимание, как при этом изменяется ширина столбцов A, B, C.

17. Посмотрите, устраивает ли Вас полученный формат таблицы. Щёлкните на кнопке «Предварительный просмотр», нажав Файл-> Печать, чтоб увидеть, как будет выглядеть при печати.

18. Щёлкните по кнопке «Печать» (Файл -> Печать -> Печать) и напечатайте документ.

Сохраните рабочую книгу examples.