Лабораторная работа № 1. “СОЗДАНИЕ, ЗАПОЛНЕНИЕ

ОГЛАВЛЕНИЕ

 

ВВЕДЕНИЕ…………………………………………………………………………….……..3

Лабораторная работа № 1. “СОЗДАНИЕ, ЗАПОЛНЕНИЕ

И РЕДАКТИРОВАНИЕ ТАБЛИЦ. АВТОЗАПОЛНЕНИЕ”………………….………..4

Лабораторная работа № 2. “ФОРМАТИРОВАНИЕ ТАБЛИЦ.

ПРОСТЕЙШИЕ ВЫЧИСЛЕНИЯ В ТАБЛИЦАХ. АВТОСУММИРОВАНИЕ”……6

Лабораторная работа №3. “Функции и сложные

вычисления в Excel”……………………………………………………………….12

Лабораторная работа №4. “Функции и

сложные вычисления в Excel” (часть II)……………………………………17

Лабораторная работа №5. “Построение и редактирование

диаграмм, графиков и поверхностей”…………………………………….19

Лабораторная работа №6. “Применение массивов

в решении экономических задач”…………………………………………..27

Лабораторная работа №7. “Финансовый анализ

и построение отчетных ведомостей”…………………………………….38

Лабораторная работа №8. “Анализ и распределение данных.

Подбор параметра”…………………………………………………………………..42

Лабораторная работа №9. “Шаблоны”……………………………………………….46

Лабораторная работа №10. “Консолидация данных.

Фильтрация данных и подведение итогов”…………………………….51

Лабораторная работа №11. “Макросы”………………………………………………62

ЛИТЕРАТУРА……………………………………………………………………………....63

ВВЕДЕНИЕ

 

Настоящее пособие предназначено для студентов и ориентировано на поддержку дисциплин “Информатика” и “Информационные системы и технологии”.

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

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

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

 

Лабораторная работа № 1. “СОЗДАНИЕ, ЗАПОЛНЕНИЕ

И РЕДАКТИРОВАНИЕ ТАБЛИЦ. АВТОЗАПОЛНЕНИЕ”

Задания

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

 

Дать листу имя Автозаполнение 1.

2. Выполнить следующие задания на втором листе книги, используя средство Автозаполнение с заданным шагом:

 

Дать листу имя Автозаполнение 2.

3. Создать на третьем листе следующие списки:

Ø список городов-поставщиков фирмы;

Ø список фирм-потребителей;

Ø список отделов завода.

Дать листу имя Списки.

4. Используя средство Автозаполнение, на Листе 4 построить таблицу вычисления n-ого члена и суммы членов арифметической прогрессии (четвертый столбец в ней не заполнять).

 

Вычисление n-ого члена и суммы членов арифметической прогрессии
d	n	An	Sn

 

Дать листу имя Прогрессия.

5. Используя средство Excel Автозаполнение и Списки, на Листе 5 построить таблицу вида:

 

ОТЧЕТ
		Март	Апрель	Май	Июнь	Июль	Август	Всего
	Приход
	Затраты на товары
	Полная выручка
	Статьи расходов
	Реклама
	Аренда помещений
	Налоги и льготы
	Проценты по кредитам
	Расходы Всего
	Прибыль

 

Дать листу имя Отчет.

6. Сохранить результат работы в своей папке под именем Лабораторная работа №1.

Лабораторная работа № 2. “ФОРМАТИРОВАНИЕ ТАБЛИЦ.

ПРОСТЕЙШИЕ ВЫЧИСЛЕНИЯ В ТАБЛИЦАХ.

АВТОСУММИРОВАНИЕ”

Задания

1. Открыть файл Лабораторная работа №1, открыть лист Прогрессия и скопировать таблицу с этого листа в новую книгу на Лист 1. Присвоить листу имя Прогрессия 1. Заполнить 4-ый столбец и отформатировать таблицу, воспользовавшись командой Автоформат.

Указание. Формула для вычисления суммы членов арифметической прогрессии: S=(a₁ + a_n)*n/2

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

2. Составить на Листе 2 таблицу, аналогичную по структуре таблице из предыдущего задания (можно скопировать таблицу с листа Прогрессия 1), выписав в столбце 3 десять членов числовой последовательности по одному из следующих правил:

- четные натуральные числа, не делящиеся на 4;

- нечетные натуральные числа, делящиеся на 3;

- натуральные числа, которые при делении на 10 дают остаток 9;

- натуральные числа, делящиеся на 3 и 4;

- натуральные числа, которые при делении на 7 дают остаток 3;

- натуральные числа, которые при делении на 5 дают остаток 2;

- четные натуральные числа, не делящиеся на 6;

- нечетные натуральные числа, делящиеся на 9;

- натуральные числа, делящиеся на 15;

- натуральные числа, делящиеся на 45.

Присвоить листу имя Прогрессия 2.

При этом определить формулу для вычисления n-ого члена последовательности и использовать ее для вычисления значений в столбце 3.

Указание. Формула для вычисления n-ого члена арифметической прогрессии: a_n=a₁+d(n-1).

Сохранить результат работы в своей папке под именем Лабораторная работа №2.

3. Открыть файл Лабораторная работа №1, открыть лист Отчет и скопировать таблицу с этого листа в новую книгу на Лист 3. Присвоить листу имя Отчет 1. Заполнить недостающие строки таблицы: " Полная выручка", " Расходы", " Всего", " Прибыль" и заполнить столбец " Всего".

Отформатировать таблицу, задав формат числовых данных в ячейках как в виде ###0 " тыс. грн.". Результат сохранить в своей папке в файле с именем Лабораторная работа №2.

4. Учетное ведомство фирмы " Запад" готовит десятилетний финансовый отчет. Необходимо оформить сведения за последние 10 лет в виде соответствующей таблицы и определить долю торговли от производства.

Для этого:

a) Ввести следующие данные в таблицу (на новом листе):

Фирма " Запад". Торговый оборот за 10 лет

Дата

Годовые показатели

 

Год	Торговля	Производство	Доля торговли

 

b) Присвоить листу имя Торговля и производство.

c) Просуммировать данные по показателям " Торговля" и " Производство".

d) Вычислить " Долю торговли".

Указание. При вычислении " Доли торговли" содержимое столбца " Торговля" делится на содержимое столбца Производство.

d) Отформатировать таблицу, применив к столбцам " Торговля" и " Производство" денежный формат, а к столбцу " Доля торговли" – процентный.

Таблица должна иметь следующий вид:

Год	Торговля	Производство	Доля торговли
	66 174тыс.	1 230 000тыс.	5, 38%
Всего	1 828 990тыс.	14 480 000тыс.	12, 63%

 

Сохранить таблицу в своей папке в файле с именем Лабораторная работа №2.

5.

На новом листе книги с именем Север составить таблицу сведений о прибыли от продаж видеофильмов фирмы " Север" в следующем виде:

Замечание. При вычислении Налога использовать абсолютный адрес для ссылки на ячейку, содержащую %-ую ставку налога.

Результат сохранить в своей папке в файле с именем Лабораторная работа №2.

6. На листе с именем Автомобили создать таблицу и отформатировать ее:

 

Марка	4 – ый квартал 2008	4 – ый квартал 2009	Среднее значение
Объем	Доля	Объем	Доля
БМВ
Форд
Мерседес
Пежо
Рено
Ауди
Опель
Феррари
Всего:

Для этого:

- ввести формулы для вычисления сумм по столбцам 2 и 4;

- вычислить " Долю" как отношение соответствующего объема продаж к значению " Всего";

- вычислить " Среднее значение" как среднее между объемами продаж.

Результат сохранить в своей папке в файле с именем Лабораторная работа №2.

7. На листе Книги заполнить и отформатировать следующую таблицу:

Математическая модель.

Для поиска зависимости между исходными и конечными данными построим цепочку равенств:

Простой вклад	Сложный вклад
А0=S0	В0=S0
А1=А0+А0*Р1/100	B1=B0(1+P2/100)
А2=А1+А0*Р1/100	B3=B1(1+P2/100)
…	…
АN=AN-1+A0*P1/100	BN=BN-1(1+P2/100)

 

Соотношение простого и сложного вкладов через N месяцев определяется знаком разности А_N - B_N.

Математическая постановка задачи: Определить значение N, при котором изменяется знак разности А_N - B_N.

 

9. Экологическая задача.

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

В результате сброса промышленных стоков возрос уровень загрязнения реки. Каким он будет через сутки, двое, трое и т.д. и через сколько суток уровень загрязнения воды станет допустимым, если известно, что за сутки он уменьшается в К раз, начальная концентрация вредных примесей С₀, предельно допустимая концентрация примесей – С_доп.

Провести исследование экологической модели при следующих значениях параметров:

Вещество	C0(мг/л)	Cдоп(мг/л)	K
Свинец		0, 03	1, 12
Мышьяк	1, 5	0, 05	1, 05
Фтор	0, 2	0, 05	1, 01

10. Задача " Преступность и судимость".

На листе Преступность и судимость заполнить таблицу " Соотношение уровней зарегистрированных преступлений, выявленных правонарушителей и осужденных в России в 1985– 1997 годах":

Годы	Зарегистр. преступления	Выявленные правонарушители	Осужденные
Абс. число	% к престу- плениям	Абс. число	% к престу- плениям	% к право- наруши- телям

Отформатировать таблицу. Результат сохранить

Результат сохранить в своей папке в файле с именем Лабораторная работа №2.

Лабораторная работа №3. “Функции и сложные вычисления в Excel”

Задания

I. На листе “Функции” выполнить следующие ниже задания.

1. Вычислить в ячейках строки 1:

Sin 37^o, Cos 25^o, Tg 132^o

2. Вычислить в ячейках строки 2:

Ln 5, 25; Log₂372, 3; e^{7, 05}

3. Составить таблицу значений чисел

7, 2293; -18, 992971; 15, 32143,

округленных обычным способом, с недостатком и с избытком. Сохранить при этом 3 знака после запятой.

4. Вывести в свободную ячейку сегодняшнюю дату.

5. Определить, каким днем недели было:

2 февраля 1907 года;

14 декабря 1938 года;

5 января 1992 года;

Апреля 1996 года.

Задания

 

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

 

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

 

Исходными данными при этом являются:

- № п/п;

- ФИО;

- должность;

- оклад;

- количество отработанных дней.

Эта исходная информация извлекается из файла, подготовленного в Word, и " расщепляется" по столбцам в Excel с помощью Мастера текстов.

Дальнейшие вычисления проводятся в соответствии со следующими требованиями:

1. Начисления:

a) За отработанные дни.

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

b) Премия.

Значение премии вычислить по следующему алгоритму:

премия равна 10% от " Начислено за отработанные дни", если отработал полный месяц, и 0 – в противном случае.

c) Другие начисления.

Значения в этом столбце подсчитать по следующему алгоритму:

другие начисления равны 5 000, если отработал полный месяц и оклад меньше среднего оклада по списку, и 0 – в противном случае. Тогда вычислить в некоторой ячейке (например, Р1) средний оклад по списку (статистическая функция СРЗНАЧ), присвоить имя СРЕДНИЙ_ОКЛАД ячейке Р1 и использовать его в формуле.

d) Всего.

Вычисляется как сумма всех начислений.

2. Удержания:

a) Подоходный налог.

Ставки подоходного налога:

до 52 800 грн.	9%
от 52 801 до 132 000 грн.	4 752+15% с суммы, превышающей 52 800
от 132 001 до 184 800 грн.	16 632+20% с суммы, превышающей 132 000
от 184 801 до 237 600 грн.	27 192+25% с суммы, превышающей 184 800
от 237 601 грн. и выше	40 392+30% с суммы, превышающей 237 600

 

b) Профсоюзный и пенсионный фонды.

Значения в столбцах вычисляются как 1% от " Всего начислено".

c) Всего.

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

d) К выдаче.

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

 

Лабораторная работа №5. “Построение и редактирование диаграмм, графиков и поверхностей”

Задания

1. На листе Графики построить графики функций, приведенных ниже, используя диаграмму типа График и Точечную диаграмму. Результаты сравнить.

a) y=x^3+2x^2+2

b) y=sin x

c) y= -6x^2+3x

d) y=1/x

e) y=ln(x-1)+5

f) y=|cos x|

g) y=|x|+3

h) 3+2/(x-4)

i) y=1/(x^2+2x+1)

Результат сохранить в файле с именем Лабораторная работа №5.

2. Для таблицы, сформированной на листе " Преступность и судимость" в файле Лабораторная работа №2, по данным ее первого, второго, третьего и пятого столбцов построить графики, отражающие динамику основных криминологических показателей в России за 1985 – 1997 годы. Изменить на каждом графике маркеры значений данных и установить для каждого графика свою толщину линии.

3. Для таблицы, сформированной на листе " Торговля_и_Производство" в файле Лабораторная работа №2, выполнить следующие задания:

a) используя данные Год, Торговля, Производство за последние 10 лет, построить объемную гистограмму на отдельном листе с именем Развитие торговли;

b) отредактировать диаграмму:

- изменить подтип гистограммы, чтобы ряды данных размещались один перед другим;

- вставить основную сетку по всем трем осям диаграммы;

- сместить легенду под область построения диаграммы;

- ввести заголовок " Развитие торговли по отношению к производству за последние 10 лет";

- изменить данные в некоторой строке и посмотреть, что произойдет с диаграммой;

- вставить в таблицу новую строку и посмотреть, что произойдет с диаграммой;

c) по данным " Год" и " Доля" торговли построить круговую диаграмму на отдельном листе с именем " Доля_Торговли";

d) отредактировать диаграмму:

- нанести на диаграмму показатели значений к каждому участку диаграммы;

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

4. Для таблицы " Продажа видеофильмов", сохраненной в файле Лабораторная работа №2, выполнить следующие задания:

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

- построить круговую диаграмму " Продажи за 6 января 1998 года", отображающую итоги продажи видеофильмов за 6 января 1998 года;

Результат сохранить в файле с именем Лабораторная работа №5.

Следующие задания выполняются по вариантам.

Номер варианта выполняемого задания совпадает с номером по списку группы (если номер по списку > 10, то отнять от номера число 10, если номер по списку > 20, то отнять от номера число 20).

Вариант 1

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

 

2. Построить в одной системе координат при графики функций:

· Y=2sin(x)cos(x);

· .

3. Построить поверхность при .

 

Вариант 2

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

 

2. Построить в одной системе координат при графики функций:

4.

5. .

3. Построить поверхность при .

 

Вариант 3

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

.

2. Построить в одной системе координат при графики функций:

·

· .

3. Построить поверхность при xÎ [-1; 1].

 

Вариант 4

1. Построить в разных системах координат при x [-1, 5; 1, 5] графики функций:

.

2. Построить в одной системе координат при x [-2; 2] графики функций:

· Y = 3sin(2 π x) cos(π x) – cos² (3π x);

· Z = 2cos²(2π x) – 3sin(3π x).

3. Построить поверхность при x, y [-1; 1]

.

 

Вариант 5

1. Построить в разных системах координат при x [-1, 8; 1, 8] графики функций:

2. Построить в одной системе координат при x [0; 3] графики функций:

· Y = 2sin(π x)cos(π x);

· Z = cos (π x)sin(3π x).

3. Построить поверхность при x, y [-1; 1].

 

Вариант 6

1. Построить в разных системах координат при x [-2; 1, 8] графики функций:

.

2. Построить в одной системе координат при x [-3; 0] графики функций:

· Y = 3sin(3π x)cos(2π x);

· Z = cos³(4π x)sin(π x).

3. Построить поверхность при x, y [-1; 1].

 

Вариант 7

1. Построить в разных системах координат при x [-1, 7; 1, 5] графики функций:

.

2. Построить в одной системе координат при x [-3; 0] графики функций:

· Y = 2sin(2π x)cos(4π x);

· Z = cos²(3π x) – cos(π x)sin(π x).

3. Построить поверхность при x, y [-1; 1].

 

Вариант 8

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

.

2. Построить в одной системе координат при графики функции:

·

·

3. Построить поверхность при

Вариант 9

1. Построить в разных системах координат при xÎ [1, 4; 1, 9] графики функций:

2. Построить в одной системе координат при хÎ [0; 2] графики функций:

·

· .

3. Построить поверхность при хÎ [-1; 1]

.

Вариант 10

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

2. Построить в одной системе координат при графики функций:

·

·

3. Построить поверхность при .

Задания

I. Выполнить следующие задания с помощью средств Microsoft Excel в соответствии с номером варианта (номер варианта выбирается по номеру студента в списке).

Вариант 1

1. Решить системы линейных уравнений AX=B, A³X=B и вычислить значение квадратичной формы Z=Y^TA^TA²Y, где

, ,

2. Вычислить:

,

где x, y – векторы из n компонентов, b – матрица размера mxm, причем n=4, m=2 и

.

Вариант 2

1. Решить системы линейных уравнений АХ=B, A²A^TX=B вычислить значение квадратной формы Z=Y^ТA³Y, где

 

, ,

 

2. Вычислить

где a – вектор из m – компонентов, с – матрица размера nxn, причем n=3, m=4

Вариант 3

1. Решить системы линейных уравнений AX=B, AА^ТАХ=B и вычислить значение квадратичной формы Z=Y^TA^TA³Y, где

, , .

2. Вычислить

,

где x, y – векторы из n компонентов, b – матрица размерности mxm, причем n= 4, m = 2 и

Вариант 4

1. Решить системы линейных уравнений AX=B, A²А^ТАХ=B и вычислить значение квадратичной формы Z=Y^TA^TAА^TY, где

, , .

2. Вычислить

,

где a – векторы из m компонентов, c – матрица размера nxn, причем n= 3, m=4 и

.

Вариант 5

1. Решить системы линейных уравнений AX=B, AА^ТА²Х=B и вычислить значение квадратичной формы Z=Y^TA³A^TY, где

, , .

2. Вычислить

,

где x, y – векторы из n компонентов, b – матрица размера mxm, причем n = 4, m = 2 и

.

Вариант 6

1.Решить системы линейных уравнений AX=B, A³A^TX=B и вычислить значение квадратной формы Z=Y^TА²A^TAY, где

, ,

 

2. Вычислить

 

где a – вектор из m – компонентов, с – матрица размера nxn, причем n=3, m=4

Вариант 7

1. Решить системы линейных уравнений AX=B, А^ТА³Х=B и вычислить значение квадратичной формы Z=Y^TAA^TA²Y, где

, , .

2. Вычислить

где x, y – векторы из n компонентов, причем n = 4 и

.

 

Вариант 8

1. Решить системы линейных уравнений AX=B, AА^ТА²Х=B и вычислить значение квадратичной формы Z=Y^TA²A^TAY, где

, , .

2. Вычислить

,

где a – вектор из mкомпонентов, c – матрица размера nxn причем n= 2, m= 4 и

.

Вариант 9

1. Решить системы линейных уравнений AX=B, A^TAA^TX=B и вычислить значение квадратной формы Z=Y^TAA^TAA^TY, где

 

, ,

 

2. Вычислить

 

где x, y – векторы их n компонентов, причем n=4 и

, .

Вариант 10

1. Решить системы линейных уравнений AX=B, А²А^TAX=B и вычислить значение квадратичной формы Z=Y^TAA^TAA^TY, где

, , .

2. Вычислить

,

где a – вектор из mкомпонентов, c – матрица размера nxn причем n= 3, m= 4 и

.

II. Выполнить следующие задания экономического содержания 1-7, используя операции с матрицами в Microsoft Excel.

1. Частный предприниматель приобрел 250 единиц товара I вида и 600 единиц товара II вида; другой частный пред­приниматель — 200 единиц товара I вида и 700 единиц товара II вида. После удачно проведенной рекламной кампа­нии товара I вида первый предприниматель сделал следую­щие закупки: I вида — 350 единиц, II вида — 550 единиц; второй предприниматель соответственно 350 и 600 единиц. Запишите матрицы: а) А₁и А₂всех закупок первым и вто­рым предпринимателем соответственно; б) общих закупок двумя предпринимателями сначала до, а затем после рек­ламной кампании.

2. Ниже приведены данные о продажах фирмы, владеющей несколькими магазинами. В строках матриц указаны суммы, вырученные на протяжении различных сезонов (весна, лето, осень, зима), а в столбцах — доходы от прода­жи различных видов товаров (телевизоры, музыкальные центры, видеокамеры):

(магазин 1) (магазин 2) (магазин 3)

 

Покажите, что в каждый сезон магазины 1 и 3 вместе взя­тые продали больше каждого вида товаров, чем магазин 2. Найдите матрицу общей продажи всех трех магазинов.

3. Данные о доходах (тыс. ден. ед.) холдинговой компании по трем регионам трех компаний за 2001 и 2003 гг. представ­лены в матрицах Аи В.

По строкам группируются данные о доходах трех компаний, по столбцам — по регионам продаж. Рассчитайте матрицу приростов доходов за период с 2001 по 2003 г. и матрицу, характеризующую средние размеры приростов доходов компании холдинга за год.

4. Тарифы (ден. ед.) перевозки единицы некоторого товара с трех фабрик четырем базам определяются матрицей

Себестоимость единицы товара на первой фабрике — 40 ден. ед., на второй — 38 ден. ед. и на третьей — 41 ден. ед. Запишите матрицу Риздержек производства размером 3x4, элементы которой группируются по строкам и столб­цам так же, как и в S. Определите матрицу Ксовокупных издержек на производство и транспортировку товара.

5. Предприятие производит продукцию двух видов и исполь­зует сырье двух типов. Нормы затрат сырья на единицу продукции каждого вида заданы матрицей:

,

у которой по строкам указано количество (у. е.) сырья, рас­ходуемого на производство единицы продукции I и II вида. Стоимость (ден. ед.) единицы сырья каждого типа задана матрицей В = [70 30]. Каковы общие затраты предприятия на производство 100 у.е. продукции I вида и 150 у.е. II вида?

1234567Следующая ⇒

Поделиться:

Популярное:

1. II. Работа с раздаточным материалом
2. III. Работа по теме урока Представление журналов
3. IV. Работа над пройденным материалом.
4. MS Excel. Расчеты с условиями. Работа со списками
5. MS Word. Работа с математическими формулами
6. V. Работа над пройденным материалом.
7. VI. Рефлексия Работа по учебнику
8. XXIV. Охрана труда при работах в зоне влияния
9. XXXIV. Охрана труда при работах на электрофильтрах
10. А. Программирование работы гирлянды, работающей в режиме бегущей волны
11. Автоклавы для обеспечения безопасной работы снабжаются, также как и сосуды, работающие под давление, предохранительной и запорной арматурой, контрольно-измерительными приборами.
12. Автоматическое заполнение данных