Занятие 6, 7 (4 ч., лабораторное)

Введение

 

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

    Идея создания электронной таблицы возникла у студента Гарвардского университете (США) Дэна Бриклина в 1979 г. Выполняя скучные вычисления экономического характера, он и его друг Боб Франкстон, который разбирался в программировании, разработали первую программу электронной таблицы, названную ими VisiCalc.

    В настоящее время среди табличных процессоров наибольшее распространение получили Lotus 1-2-3, QuatroPro, SuperCalc и Microsoft Excel, версия которого Excel 2003 рассматривается в настоящем методическом пособии.

    Первый вариант табличного процессора Excel фирмы Microsoft появился в 1987 г. В настоящее время широко применяются его последние версии: Excel 2002 и Excel 2003 для операционных систем семейства Windows.

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

· выполнять самые различные вычисления – простые арифметические и сложные, задаваемые произвольными формулами и встроенными функциями;

· сортировать и преобразовывать данные;

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

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

    Настоящее пособие предназначено для студентов 1-го курса по специальности «Менеджмент организации», изучающих дисциплину «Информатика», и призвано сформировать у них базовые навыки работы с табличным процессором Microsoft Excel 2003.

 

Занятие 6, 7 (4 ч., лабораторное)

    Тема: Операции с матрицами. Решение систем линейных уравнений

 

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

    Даны две матрицы A и B:

 

    ;

 

       .

 

    Для исходных матриц вычислите:

· определители;

· матрицы, обратные к исходным матрицам;

· произведение двух матриц.

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

 

Ход работы

1. Доведите общее число листов в новой рабочей книге до двух. В случае, если листов больше, для их удаления используйте команду П равка è Удалить л ист. В случае, если листов меньше, для их вставки используйте команду Вст а вка è Л ист.

2. Присвойте Листу1 текущей рабочей книги имя Матрицы: Фор м ат è Л ист è Пере и меновать.

3. На листе Матрицы введите элементы матриц A и B.

    В ячейке B 1 введите заголовок Матрица A, а сами элементы матрицы введите в ячейки A 2: D 5. Каждое число (элемент) матрицы вводится в отдельную ячейку.

    В ячейке G 1 введите заголовок Матрица B, а сами элементы матрицы введите в ячейки F 2: I 5.

    В ячейке A 7 введите заголовок Определитель А =, а в ячейке F7 – Определитель В =.

    В ячейке B 9 введите заголовок Обратная к A матрица A ^-1.

    В ячейке B15 введите заголовок Произведение матриц A и B.

    В ячейке G 15 введите заголовок Произведение матриц A и A ^-1.

4. Для вычисления определителей используется функция МОПРЕД, которая в качестве аргумента использует блок ячеек с элементами матрицы.

    Для расчета определителя матрицы A в ячейку B 7 введите формулу:

 

    = МОПРЕД(A2:D5).

 

    Для расчета определителя матрицы B в ячейку G 7 введите формулу:

 

    = МОПРЕД(F2:I5).

 

    Для ввода формул можно использовать клавиатуру или Мастер функций.

5. Для вычисления матрицы A^-1, обратной к матрице A, используется функция МОБР, аргументом которой является массив данных исходной матрицы A. Обратная матрица будет существовать, если определитель матрицы A не равен 0.

    Для расчета обратной к A матрицы A^-1 поместите курсор на ячейку A 10 и выделите блок ячеек A 10: D 13. С помощью команды Вст а вка è Ф ункция… запустите Мастер функций и выберите функцию МОБР. В окне функции МОБР введите ее аргумент – А2:D5 и закройте окно, нажав клавиши Ctrl+Shift+Enter. В результате ячейки A 10: D 13 будут содержать элементы обратной матрицы.

6. Для умножения матриц используется функция МУМНОЖ, аргументами которой являются массивы данных умножаемых матриц.

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

    Т.к. матрицы А и В имеют размер 4 х 4, то в результате будет получена матрица размера 4 х 4.

    Для умножения матриц A и B поместите курсор в ячейку А16 и мышью выделите блок ячеек A16:D19. С помощью команды Вст а вка è Ф ункция… запустите Мастер функций и выберите функцию МУМНОЖ. В окне функции МУМНОЖ введите ее аргументы – блоки ячеек A 2: D 5 и F 2: I 5 и закройте окно, нажав клавиши Ctrl+Shift+Enter. В результате ячейки A 16: D 19 будут содержать элементы искомой матрицы.

7. Проверьте правильность вычисления обратной матрицы A^-1 умножением на нее исходной матрицы A, в результате должна получиться единичная матрица.

    Расчет единичной матрицы выполните в ячейках F16:I19.

8. Сохраните текущую рабочую книгу под именем lesson 4. xls: Ф айл è Сохранить к ак…

 

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

    Решите следующую систему линейных уравнений:

 

    .

 

    Система линейных уравнений в матричной форме имеет вид:

 

    AX = B,

 

где A – матрица, составленная из коэффициентов при неизвестных в уравнении;

X – вектор из неизвестных;

B – вектор, составленный из правых частей уравнения.

    Решение системы линейных уравнений в матричной форме имеет вид:

 

    X = A^-1B.

 

    Т.е. для нахождения решения системы X необходимо:

а) найти матрицу, обратную к исходной матрице A;

б) умножить получившуюся обратную матрицу на вектор свободных членов уравнения В.

 

Ход работы

1. Присвойте Листу2 текущей рабочей книги имя Система уравнений: Фор м ат è Л ист è Пере и меновать.

2. На листе Система уравнений введите элементы матриц A и B.

    В ячейке B 1 введите заголовок Матрица A, а сами элементы матрицы введите в ячейки A 2: D 5. Каждое число (элемент) матрицы вводится в отдельную ячейку.

    В ячейке F 1 введите заголовок Матрица B, а сами элементы матрицы введите в ячейки F 2: F 5.

    В ячейке B 7 введите заголовок Обратная к A матрица A ^-1.

    В ячейке F 7 введите заголовок Решение X = A ^-1 B.

3. Для расчета обратной к A матрицы A^-1 поместите курсор на ячейку A 8 и выделите блок ячеек A 8: D 11. С помощью команды Вст а вка è Ф ункция… запустите Мастер функций и выберите функцию МОБР. В окне функции МОБР введите ее аргумент – А2:D5 и закройте окно, нажав клавиши Ctrl+Shift+Enter. В результате ячейки A 8: D 11 будут содержать элементы обратной матрицы.

4. Для умножения матриц A^-1 и B поместите курсор в ячейку F 8 и мышью выделите блок ячеек F 8: F 11. С помощью команды Вст а вка è Ф ункция… запустите Мастер функций и выберите функцию МУМНОЖ. В окне функции МУМНОЖ введите ее аргументы – блоки ячеек A 8: D 11 и F 2: F 5 и закройте окно, нажав клавиши Ctrl+Shift+Enter. В результате ячейки F 8: F 11 будут содержать элементы искомой матрицы, т.е. значения x₁, x₂, x₃ и x₄, которые являются решением исходной системы линейных уравнений.

5. Сохраните текущую рабочую книгу: Ф айл è С охранить.

 

Варианты заданий

    Решите с помощью Excel следующие системы линейных уравнений:

 

1 0,64x1 + 0,72x2 - 0,83x3 + 4,20x4 = 2,23   0,58x1 - 0,83x2 + 1,43x3 - 0,62x4 = 1,71   0,86x1 + 0,77x2 - 1,83x3 - 0,88x4 =- 0,54   1,32x1 - 0,52x2 - 0,65x3 + 1,22x4 = 0,65   2 0,63x1 + 1,00x2 + 0,71x3 + 0,34x4 = 2,08   1,17x1 + 0,18x2 - 0,65x3 + 0,71x4 = 0,17   2,71x1 + 0,75x2 + 1,17x3 - 2,35x4 = 1,28   3,58x1 + 0,21x2 - 3,45x3 - 1,18x4 = 0,05   3 2,00x1 + 0,05x2 - 3,01x3 - 0,11x4 = 0,21   1,00x1 - 2,00x2 + 3,02x3 + 0,05x4 = 0,18   0,17x1 + 0,99x2 - 2,00x3 - 0,17x4 = 0,17   0,33x1 - 0,07x2 + 0,33x3 + 2,00x4 = 0,17   4 0,17x1 - 0,13x2 - 0,11x3 - 0,12x4 = 0,22   1,00x1 - 1,00x2 - 0,13x3 + 0,13x4 = 0,11   0,17x1 + 0,99x2 - 2,00x3 - 0,17x4 = 0,12   0,33x1 - 0,07x2 + 0,33x3 + 2,00x4 = 1,00   5 2, 5x1 - 5,72x2 - 0,38x3 +         = 0,93 1,08x1 + 0,64x2 - 0,95x3 + 1,54x4 = 9,64 0,98x1 - 0,72x2 + 1,36x3 - 0,68x4 =- 0,85   0,64x1 + 1,48x2 + 0,67x3 - 1,58x4 =-1,35

6 0,13x1 + 0,22x2 - 0,14x3 + 0,15x4 = 1,00    0,22x1 - 0,31x2 + 0,42x3 - 5,10x4 = 8,01    0,62x1 - 0,74x2 + 0,85x3 - 0,96x4 = 0,11    0,22x1 +          + 0,14x3 + 0,45x4 = 0,16   7 0,77x1 + 0,19x2 + 0,20x3 - 0,21x4 = 6    0,51x1 - 0,50x2 + 0,49x3 - 0,48x4 = 4    0,61x1 + 0,62x2 - 0,63x3 + 1,3x4 = 3    0,11x1 - 0,5x2 + 0,22x3 - 0,38x4 = 1   8 2,00x1 - 2,01x2 + 2,04x3 + 0,17x4 = 20 1,33x1 + 0,69x2 + 0,44x3 + 0,81x4 = 0,8   3,31x1 + 0,71x2 - 0,21x3 + 0,54x4 = 1,36    0,17x1 + 1,00x2 - 0,3x3 + 0,21x4 = 3,491   9 0,02x1 - 0,01x2 + 0,04x3 + 0,17x4 = 0,38    0,33x1 - 0,77x2 + 0,49x3 +        = 0,57    0,31x1 + 0,17x2 - 0,41x3 + 0,54x4 = 1,21    0,17x1 + 1,20x2 - 0,13x3 + 0,21x4 = 0,31   10 2,02x1 + 0,05x2 - 3,01x3 - 0,11x4 = 0,8   1,11x1 - 2,00x2 + 0,02x3 + 0,05x4 = 1,18   0,33x1 + 0,99x2 - 2,00x3 - 0,17x4 = 5,17   0,33x1 - 0,47x2 + 0,33x3 + 2,00x4 = 0,7

 

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

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

1) 5-7 санитарок;

2) 8-10 медсестер;

3) 10-12 врачей;

4) 1 заведующий аптекой;

5) 3 заведующих отделениями;

6) 1 главный врач;

7) 1 завхоз;

8) 1 заведующий больницей.

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

 

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

 

    A×C+B,

 

где C – оклад санитарки;

A и B – коэффициенты, которые для каждой должности определяются советом трудового коллектива.

    Допустим, совет трудового коллектива решил, что:

медсестра должна получать в 1,5 раза больше санитарки A = 1,5 B = 0;

врач – в 3 раза больше санитарки                                      A = 3 B = 0;

заведующий отделением – на $30 больше, чем врач       A = 3 B = 30;

заведующий аптекой – в 2 раза больше санитарки          A = 2 B = 0;

завхоз – на $40 больше медсестры                                      A = 1,5 B = 40;

главный врач – в 4 раза больше санитарки                       A = 4 B = 0;

заведующий больницей – на $20 больше главного врача A = 4 B = 20.

    Задав количество человек на каждой должности, можно получить следующую модель:

 

    N1×(A1×C+B1) + N2×(A2×C+B2) + … + N8×(A8×C+B8) = 10000,

 

где N1 – количество санитарок;

N2 – количество медсестер и т. д.;

A1…A8 и B1…B8 – коэффициенты для каждой должности.

    В этом уравнении неизвестными являются C и N1…N8. Решить это уравнение можно путем подбора. В Excel это сделать достаточно просто.

 

Ход работы

1. Заполните следующую таблицу на Листе1 новой рабочей книги Excel:

 

	A	B	C	D	E	F	G	H
1	Коэф.	Коэф.	Должность	Зарплата	Кол-во	Суммарн.		Зарплата
2	A	B		сотруд.	сотруд.	зарплата		Санитарки
3	1	0	Санитарка		7
4	1,5	0	Медсестра		10
5	3	0	Врач		12
6	3	30	Зав. отделением		3
7	2	0	Зав. аптекой		1
8	1,5	40	Завхоз		1
9	4	0	Глав. Врач		1
10	4	20	Зав. больницей		1
11
12		Суммарный месячный фонд зарплаты

 

2. В ячейку H3 занесите в качестве первого приближения значение заработной платы санитарки 150 и установите для нее формат 0,00 – два знака после запятой: Фор м ат è Я чейки… è вкладка Число.

3. Ячейке H3 присвойте имя ОкладСанитарки:

3.1. выделите ячейку H3;

3.2. перейдите в поле имени, которое располагается слева в строке формул, и щелкните левой кнопкой мыши (или Вст а вка è И мя è П рисвоить);

3.3. введите имя и нажмите клавишу Enter.

4. В столбце D вычислите зарплату для каждой должности:

4.1. в ячейку D3 занесите формулу = A3*ОкладСанитарки+B3 (использование имени ячейки равнозначно использованию абсолютной ссылки на нее);

4.2. скопируйте формулу из ячейки D3 в ячейки D4:D10;

4.3. установите для данных столбца D формат 0,00 – два знака после запятой.

5. В столбце F вычислите зарплату всех сотрудников определенных должностей:

5.1. в ячейку F3 занесите формулу = D3*E3;

5.2. скопируйте формулу из ячейки F3 в ячейки F4:F10;

5.3. установите для данных столбца F формат 0,00 – два знака после запятой.

6. В ячейку F12, для вычисления суммарного месячного фонда заработной платы, занесите формулу = СУММ(F3:F10).

7. Штатное расписание можно составить вручную, изменяя либо зарплату санитарки, либо количество сотрудников в ячейках E3:E5 до тех пор, пока в ячейке F12 не получим»10000.

8. Штатное расписание можно составить с помощью функции Подбор параметра:

8.1. задайте точное число сотрудников в ячейках E3:E5;

8.2. выберите команду С е рвис è П одбор параметра…;

8.3. укажите в поле Установить в ячейке: адрес целевой ячейки $F$12;

8.4. укажите в поле Значение: 10000;

8.5. укажите в поле Изменяя значение ячейки: адрес ячейки с зарплатой санитарки $H$3 и нажмите на кнопку OK.

9. Составьте несколько вариантов штатного расписания с использованием функции Подбор параметра:

9.1. измените количество сотрудников на должностях санитарки, медсестры и врача;

9.2. подберите зарплату санитарки в новых условиях;

9.3 составьте таблицу нескольких (3-4) вариантов штатного расписания на Листе2 текущей рабочей книги Excel:

 

Варианты штатного расписания

 

№ варианта	Должность	Кол-во сотрудников	Зарплата санитарки
…	Санитарка Медсестра Врач	… … …	…

 

10. Сохраните текущую рабочую книгу под именем lesson 5. xls: Ф айл è Сохранить к ак…

11. Проанализируйте полученные варианты штатного расписания, выберите и оформите в виде таблицы в Word один из них и сохраните документ под именем lesson 5. doc:

 

Штатное расписание хозрасчетного пансионата

 

Должность	Кол-во сотрудников	Зарплата сотрудника	Суммарная зарплата
…	…	…	…
Суммарный месячный фонд зарплаты			…

 

12. В файле lesson 5. xls на Листе1 постройте Круговую диаграмму, показывающую распределение месячного фонда заработной платы по должностям для выбранного в пункте 11 варианта штатного расписания: Вст а вка è Диа г рамма…

13. Скопируйте полученную в пункте 12 диаграмму из файла lesson 5. xls и вставьте ее в файл lesson 5. doc с помощью операций копирования и вставки.

    Для программ, работающих под управлением ОС Windows, доступны операции копирования (copy) и вставки (paste), в которых применяется буфер обмена (clipboard) Windows.

    Самый простой метод переноса информации (объекта) – копирование ее из одной программы и вставка в другую. При этом объекты не являются связанными. Объекты могут быть встроенными, а могут и не быть встроенными.

    При переносе информации всегда есть исходная программа и программа-адресат.

    Когда файл программы-адресата воспринимает и хранит копию объекта, говорят, что объект встроен в этот файл.

    Если дважды щелкнуть на встроенном объекте, хранящемся в файле-адресате, запускается программа, в которой создан этот объект, однако при этом отображается копия встроенного объекта, а не сам оригинал. Любые изменения встроенного объекта происходят только в той копии объекта, которая хранится в файле-адресате. Например, объект, представляющий собой диаграмму Excel, можно встроить в документ Word.

    Если объект хранится в файле-адресате в виде рисунка, он не является встроенным. Например, если диаграмму Excel вставить в документ Word в виде рисунка, она перестает быть объектом Excel, и двойной щелчок на ней не приведет к запуску программы Excel.

    Если объект продолжает храниться в файле исходной программы, а файл программы-адресата располагает лишь сведениями о его месторасположении, говорят, что объект связан с этим файлом.

    Если дважды щелкнуть на связанном объекте в файле-адресате, открывается исходный файл, в котором создан этот объект, а также используемая программа. Любые изменения в связанном объекте отражаются и в исходном файле, и в файле адресате. Точно также, любые изменения в исходном файле, приводят к изменению объекта в том файле, с которым он связан. Например, если объект, представляющий диаграмму Excel, связан с документом Word, любые изменения в файле данных Excel приведут к обновлению диаграммы как в файле Excel, так и в файле Word.

    При использовании операций копирования (copy) и вставки (paste) мы встраиваем диаграмму Excel в файл Word.

14. С помощью команды Word П равка è Сп е циальная вставка…:

14.1. вставьте полученную в пункте 12 диаграмму из файла lesson 5. xls в файл lesson 5. doc в виде рисунка;

14.2. свяжите полученную в пункте 12 диаграмму из файла lesson 5. xls с файлом lesson 5. doc.

 

Варианты заданий

1. Составьте штатное расписание кафе.

2. Составьте штатное расписание туристического агенства.

 

Ход работы

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

 

	A	B	C	D
1	Шифр группы	ФИО студента	Экзаменационная оценка	Дисциплина
2	01АСУ	Гаранина О.И.	4	Математика
3	01АСУ	Косиненко Н.С.	4	Математика
4	01ИСЭ	Козлов М.В.	5	Информатика
5	01ИСЭ	Матросова В.К.	5	Математика
6	00ФиК	Нугуманов Н.Ю.	4	Информатика
7	00ФиК	Одинцов Л.И.	3	Информатика
8	01ИСЭ	Воронин С.Л.	3	Информатика
9	01ИСЭ	Губарева М.В.	5	Математика
10	01АСУ	Степанов М.Ф.	5	Математика
11	01АСУ	Федорова А.Л.	5	Информатика
12	01ИСЭ	Ершов К.Б.	3	Информатика
13	01ИСЭ	Волчек Г.Н.	4	Математика
14	00ФиК	Романов Н.П.	4	Математика
15	00ФиК	Пугачев А.В.	4	Информатика
16	01ИСЭ	Волк М.Д.	3	Математика
17	01АСУ	Борисова В.П.	4	Информатика
18	01ИСЭ	Воронин С.Л.	5	Информатика
19	01АСУ	Носков Р.А.	5	Информатика
20	01АСУ	Орлов В.Д.	5	Математика
21	00ФиК	Сомов М.Ю.	4	Математика
22	00ФиК	Точилин Н.А.	5	Информатика
23	00ФиК	Ушакова А.Д.	3	Математика
24	01ИСЭ	Золотухин Н.Б.	3	Математика
25	01АСУ	Федотов М.К.	5	Математика
26	01ИСЭ	Воробей Е.Н.	2	Информатика
27	01ИСЭ	Иванов И.Л.	4	Математика
28	01ИСЭ	Балык Б.Д.	4	Информатика
29	01ИСЭ	Губарева М.В.	5	Информатика
30	01ИСЭ	Леонидова Л.Г.	4	Информатика
31	00ФиК	Родионов К.И.	4	Математика
32	01АСУ	Петрова В.Э.	3	Математика
33	01ИСЭ	Кочубей И.Г.	5	Математика
34	00ФиК	Галкин С.Л.	5	Математика
35	00ФиК	Громов К.Н.	3	Информатика
36	01АСУ	Мазуров А.З.	5	Информатика
37	01ИСЭ	Соловьев В.В.	4	Информатика
38	01АСУ	Мамонтов Д.В.	4	Математика
39	01АСУ	Клишин Е.Д.	5	Математика
40	00ФиК	Аринцев М.Ю.	4	Информатика
41	01ИСЭ	Пустовенский К.С.	4	Информатика
42	01ИСЭ	Корнева А.А.	3	Информатика
43	01АСУ	Тупикова В.Р.	3	Математика
44	00ФиК	Третьяк С.С.	4	Математика
45	01АСУ	Минский К.С.	5	Информатика
46	00ФиК	Бердников П.Д.	5	Математика
47	00ФиК	Соколов К.Н.	4	Информатика
48	01АСУ	Юрский Н.Е.	5	Математика
49	01ИСЭ	Шолохов К.Н.	3	Математика
50	01ИСЭ	Мишенин Д.В.	4	Информатика
51	00ФиК	Сергеев Н.Т.	3	Информатика

 

2. Присвойте Листу1 текущей рабочей книги имя Список: Фор м ат è Л ист è Пере и меновать.

3. Удалите все другие листы из текущей рабочей книги.

    Для этого:

· перейдите на удаляемый лист;

· для удаления листа используйте команду П равка è Удалить л ист.

4. Сохраните текущую рабочую книгу под именем lesson 6. xls: Ф айл è Сохранить к ак…

5. С использованием команды П равка è Пере м естить/скопировать лист… скопируйте лист Список в конец текущей рабочей книги.

    Для этого:

· перейдите на лист Список;

· выберите команду П равка è Пере м естить/скопировать лист… Отобразится диалоговое окно Переместить или скопировать;

· в диалоговом окне Переместить или скопировать в области П еред листом: выберите (переместить в конец); включите опцию Создавать к опию и нажмите на кнопке OK.

6. Присвойте созданному листу Список (2) текущей рабочей книги имя Сортировка: Фор м ат è Л ист è Пере и меновать.

7. На листе Сортировка осуществите сортировку списка.

    Для этого:

· установите курсор в любую ячейку списка;

· выберите команду Д анные è С о ртировка… Отобразится диалоговое окно Сортировка диапазона;

· в диалоговом окне Сортировка диапазона в области Сортировать по выберите Шифр группы и включите опцию по возраста н ию; в области Затем по выберите Дисциплина и включите опцию по возраста н ию; в области В последнюю очередь, по выберите ФИО студента и включите опцию по возраста н ию; в области Идентифицировать поля по включите опцию п одписям (первая строка диапазона) и нажмите на кнопке OK.

8. Сохраните текущую рабочую книгу: Ф айл è С охранить.

9. С использованием команды П равка è Пере м естить/скопировать лист… скопируйте лист Сортировка в конец текущей рабочей книги.

    Для этого:

· перейдите на лист Сортировка;

· выберите команду П равка è Пере м естить/скопировать лист… Отобразится диалоговое окно Переместить или скопировать;

· в диалоговом окне Переместить или скопировать в области П еред листом: выберите (переместить в конец); включите опцию Создавать к опию и нажмите на кнопке OK.

10. Присвойте созданному листу Сортировка (2) текущей рабочей книги имя Итоги: Фор м ат è Л ист è Пере и меновать.

11. На листе Итоги с использованием команды Д анные è И тоги… рассчитайте средние экзаменационные оценки по каждой группе и среднюю экзаменационную оценку по всем группам.

    Для этого:

· установите курсор в любую ячейку списка;

· выберите команду Д анные è И тоги… Отобразится диалоговое окно Промежуточные итоги;

· в диалоговом окне Промежуточные итоги в раскрывающемся списке П ри каждом изменении в: выберите Шифр группы; в раскрывающемся списке О перация: выберите Среднее; в списке До б авить итоги по: выберите Экзаменационная оценка; выключите опции З аменить текущие итоги и К онец страницы между группами; включите опцию Ито г и под данными и нажмите на кнопке OK.

    Слева от таблицы на служебном поле появятся символы структуры:

· показа деталей (кнопка «плюс»);

· скрытия деталей (кнопка «минус»);

· уровней структуры (кнопки «1», «2», «3» и т. д.

12. Дополнительно рассчитайте средние экзаменационные оценки по каждому предмету по каждой группе.

    Для этого:

· установите курсор в любую ячейку списка;

· выберите команду Д анные è И тоги… Отобразится диалоговое окно Промежуточные итоги;

· в диалоговом окне Промежуточные итоги в раскрывающемся списке П ри каждом изменении в: выберите Дисциплина; в раскрывающемся списке О перация: выберите Среднее; в списке До б авить итоги по: выберите Экзаменационная оценка; выключите опции З аменить текущие итоги и К онец страницы между группами и нажмите на кнопке OK.

13. Сохраните текущую рабочую книгу: Ф айл è С охранить.

14. С использованием команды П равка è Пере м естить/скопировать лист… скопируйте лист Сортировка в конец текущей рабочей книги.

    Для этого:

· перейдите на лист Сортировка;

· выберите команду П равка è Пере м естить/скопировать лист… Отобразится диалоговое окно Переместить или скопировать;

· в диалоговом окне Переместить или скопировать в области П еред листом: выберите (переместить в конец); включите опцию Создавать к опию и нажмите на кнопке OK.

15. Присвойте созданному листу Сортировка (2) текущей рабочей книги имя Консолидация: Фор м ат è Л ист è Пере и меновать.

16. На листе Консолидация с использованием команды Д анные è К онсолидация… рассчитайте количество записей по каждой группе и средние экзаменационные оценки по каждой группе.

    Для расчета количества записей по каждой группе:

· установите курсор в ячейку A53;

· выберите команду Д анные è К онсолидация… Отобразится диалоговое окно Консолидация;

· в диалоговом окне Консолидация в раскрывающемся списке Ф ункция: выберите Кол-во значений; в поле Сс ы лка: введите ссылку на диапазон ячеек A2:D51 листа Консолидация; в рамке Использовать в качестве имен: включите опцию значения л евого столбца и нажмите на кнопке OK.

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

· установите курсор в ячейку A57;

· выберите команду Д анные è К онсолидация… Отобразится диалоговое окно Консолидация;

· в диалоговом окне Консолидация в раскрывающемся списке Ф ункция: выберите Среднее; в списке С писок диапазонов: выберите ссылку на диапазон ячеек A2:D51 листа Консолидация в результате чего в поле Сс ы лка: появится ссылка на диапазон ячеек A2:D51 листа Консолидация; в рамке Использовать в качестве имен: включите опцию значения л евого столбца и нажмите на кнопке OK.

17. Сохраните текущую рабочую книгу: Ф айл è С охранить.

18. С использованием команды П равка è Пере м естить/скопировать лист… скопируйте лист Сортировка в конец текущей рабочей книги.

    Для этого:

· перейдите на лист Сортировка;

· выберите команду П равка è Пере м естить/скопировать лист… Отобразится диалоговое окно Переместить или скопировать;

· в диалоговом окне Переместить или скопировать в области П еред листом: выберите (переместить в конец); включите опцию Создавать к опию и нажмите на кнопке OK.

19. Присвойте созданному листу Сортировка (2) текущей рабочей книги имя Структура: Фор м ат è Л ист è Пере и меновать.

20. На листе Структура с использованием команды Вст а вка è Стро к и вставьте разделяющие строки для учебных групп.

21. Создайте структурные части таблицы для учебных групп.

    Для этого:

· выделите ячейки, относящиеся к первой группе (A2:D16);

· выберите команду Д анные è Г руппа и структура è Г руппировать… Отобразится диалоговое окно Группирование;

· в диалоговом окне Группирование включите опцию с т роки и нажмите на кнопке OK.

    Аналогичным образом создайте структурные части для ячеек таблицы, относящихся ко второй (A18:D32) и третьей (A34:D53) группе.

22. Сохраните текущую рабочую книгу: Ф айл è С охранить.

 

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

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

1) фамилии начинаются с букв А или Б или В;

2) имеют стаж работы от 5 до 15 лет включительно;

3) являются членами профсоюза.

 

Ход работы

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

 

	A	B	C	D	E
1	№ п/п	Фамилия И.О.	Должность	Стаж	Член профсоюза
2	1	Гаранина О.И.	Директор	15	Да
3	2	Косиненко Н.С.	Зам. директора	11	Да
4	3	Козлов М.В.	Зам. директора	7	Да
5	4	Матросова В.К.	Гл. инженер	16	Да
6	5	Нугуманов Н.Ю.	Иженер-конструктор	4	Нет
7	6	Одинцов Л.И.	Иженер-конструктор	7	Нет
8	7	Воронин С.Л.	Иженер-конструктор	10	Да
9	8	Губарева М.В.	Иженер-конструктор	8	Нет
10	9	Степанов М.Ф.	Гл. бухгалтер	10	Нет
11	10	Федорова А.Л.	Бухгалтер	3	Да
12	11	Ершов К.Б.	Бухгалтер	7	Да
13	12	Волчек Г.Н.	Бухгалтер	9	Да
14	13	Романов Н.П.	Экономист	2	Нет
15	14	Пугачев А.В.	Экономист	12	Нет
16	15	Волк М.Д.	Сметчик	6	Да
17	16	Борисова В.П.	Делопроизводитель	3	Нет
18	17	Воронин С.Л.	Делопроизводитель	7	Да
19	18	Носков Р.А.	Референт	9	Да
20	19	Орлов В.Д.	Секретарь	1	Нет
21	20	Сомов М.Ю.	Секретарь	5	Нет
22	21	Точилин Н.А.	Секретарь	6	Да
23	22	Ушакова А.Д.	Гл. энергетик	8	Да
24	23	Золотухин Н.Б.	Энергетик	12345Следующая ⇒ Поделиться с друзьями: Папиллярные узоры пальцев рук - маркер спортивных способностей: дерматоглифические признаки формируются на 3-5 месяце беременности, не изменяются в течение жизни... Состав сооружений: решетки и песколовки: Решетки – это первое устройство в схеме очистных сооружений. Они представляют... Организация стока поверхностных вод: Наибольшее количество влаги на земном шаре испаряется с поверхности морей и океанов (88‰)... Историки об Елизавете Петровне: Елизавета попала между двумя встречными культурными течениями, воспитывалась среди новых европейских веяний и преданий...  © cyberpedia.su 2017-2024 - Не является автором материалов. Исключительное право сохранено за автором текста. Если вы не хотите, чтобы данный материал был у нас на сайте, перейдите по ссылке: Нарушение авторских прав. Мы поможем в написании вашей работы! 0.15 с.