Список книг, купленных в январе

ОГЛАВЛЕНИЕ

_Toc416167028ВВЕДЕНИЕ.. 4

Лабораторная работа № 1. «СОЗДАНИЕ, ЗАПОЛНЕНИЕ И РЕДАКТИРОВАНИЕ ТАБЛИЦ. АВТОЗАПОЛНЕНИЕ». 5

Лабораторная работа № 2. «ФОРМАТИРОВАНИЕ ТАБЛИЦ. ПРОСТЕЙШИЕ ВЫЧИСЛЕНИЯ В ТАБЛИЦАХ. АВТОСУММИРОВАНИЕ». 7

Лабораторная работа №3. «ФУНКЦИИ И СЛОЖНЫЕ ВЫЧИСЛЕНИЯ В EXCEL» (ЧАСТЬ 1) 13

Лабораторная работа №4. «ФУНКЦИИ И СЛОЖНЫЕ ВЫЧИСЛЕНИЯ В EXCEL» (ЧАСТЬ II) 17

Лабораторная работа №5. «ПОСТРОЕНИЕ И РЕДАКТИРОВАНИЕ ДИАГРАММ, ГРАФИКОВ И ПОВЕРХНОСТЕЙ». 19

Лабораторная работа №6. «ПРИМЕНЕНИЕ МАССИВОВ В РЕШЕНИИ ЭКОНОМИЧЕСКИХ ЗАДАЧ». 27

Лабораторная работа №7. «ФИНАНСОВЫЙ АНАЛИЗ И ПОСТРОЕНИЕ ОТЧЕТНЫХ ВЕДОМОСТЕЙ». 37

Лабораторная работа №8. «АНАЛИЗ И РАСПРЕДЕЛЕНИЕ ДАННЫХ. ПОДБОР ПАРАМЕТРА» 41

Лабораторная работа №9. «Шаблоны». 43

Лабораторная работа №10. «КОНСОЛИДАЦИЯ ДАННЫХ. ФИЛЬТРАЦИЯ ДАННЫХ И ПОДВЕДЕНИЕ ИТОГОВ». 51

Лабораторная работа №11. «МАКРОСЫ». 60

ЛИТЕРАТУРА.. 61

ВВЕДЕНИЕ

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

Часть 2 данного пособия – лабораторный практикум – состоит из одиннадцати лабораторных работ, предусматривающих освоение основных возможностей приложения 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» ЧАСТЬ 1

Задания

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. Построить поверхность при .

Лабораторная работа №6. «ПРИМЕНЕНИЕ МАССИВОВ
В РЕШЕНИИ ЭКОНОМИЧЕСКИХ ЗАДАЧ»

Задания

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 – матрица размерности m x m, причем n= 4, m = 2 и

Вариант 4

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

, , .

2. Вычислить

,

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

.

Вариант 5

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

, , .

2. Вычислить

,

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

.

Вариант 6

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

, ,

2. Вычислить

где a – вектор из m – компонентов, с – матрица размера n x n, причем 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 – матрица размера n x n причем 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 – матрица размера n x n причем 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 вида?

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

Стоимость (ден. ед.) единицы сырья каждого типа задана матрицей

В= [10 15]. Каковы общие затраты предприятия на производство 100 у.е. продукции I вида, 200 и 150 у.е. продукции II и III видов соответственно?

7. Предприятие выпускает 3 вида изделий, используя при этом сырье 3 типов. Нормы расхода сырья по видам изделий указаны в таблице.

Тип сырья	Норма расхода сырья на 1 изделие по видам
I
II
III

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