инструкция к задачам

Задача №1

1. Запускаем редактор электронных таблиц Microsoft Excel и создаем новую электронную книгу.

Для оформления шапки таблицы выделяем ячейки диапазоном A1:J3 и создаем стиль для оформления. Для этого выполните команду Формат/Стиль и в открывшемся окне Стиль наберите имя стиля «Шапка таблиц» и нажмите кнопку Изменить. В открывшемся окне на вкладке Выравнивание выберите горизонтальное и вертикальное выравнивание — по центру, на вкладке Число укажите формат — Текстовый. После этого нажмите кнопки ОК/Добавить/ОК.

3. На первой строке вводим название таблицы — «Новый путь», на второй строке вводим заголовки «Автомобиль», «Кол-во, шт.», «Цена при покупке», «Цена при продаже», «сумма затрат на покупку», «Сумма от продаж, руб.», на третьей строке «$», «руб.», далее заполняем таблицу исходными данными согласно заданию.

4. Рассчитываем сумму затрат на покупку каждой марки автомобиля, для этого пользуемся формулами: «C4*B4», «D4*B4».

Для того чтобы придать денежный формат: Формат/Ячейки/Число/Обозначение.

5. Рассчитываем наибольшую сумму затрат, пользуясь мастером функций (кнопка fx). Функция «Максимальное значение» (МАКС) находится в разделе «Статистические». Для расчета функции МАКС выручки установливаем курсор в соответствующей ячейке для расчета максимальное значение (Е9), запускаем мастер функций (Вставка/Функция/Категория  Статистические/МАКС. В качестве диапазона выделяем группу ячеек с данными для расчета максимального значения  H4:H6.

6. Рассчитываем наименьшую сумму затрат , пользуясь мастером функций (кнопка fx). Функция «Минимальное значение» (МИН) находится в разделе «Статистические». Для расчета функции МИН выручки установливаем курсор в соответствующей ячейке для расчета минимальное значение (Е8), запускаем мастер функций (Вставка/Функция/Категория  Статистические/МИН. В качестве диапазона выделяем группу ячеек с данными для расчета минимального значения  H4:H6.

7. Рассчитываем общую сумму затрат на покупку всех автомобилей пользуясь мастером функций (кнопка fx). Функция «Сумма» (СУММ) находится в разделе «Математические». Для расчета функции СУММ общей суммы установливаем курсор в соответствующей ячейке для расчета суммы (Е10), запускаем мастер функций (Вставка/Функция/Категория  Математические/СУММ) = СУММ(H4:H6).

Конечный вид таблицы приведен на рис. 1.

Новый путь
Автомобиль	Кол-во, шт.	Цена при покупке		Цена при продаже		сумма затрат на покупку		Сумма от продаж, руб.
		$	руб.	$	руб.	$	руб.
ВАЗ-21093	35	$2 000	68 680 р.	$1 310	45 000 р.	$70 000	2 403 800 р.	1 575 000 р.
ГАЗ-31029	10	$2 100	72 114 р.	$1 398	48 000 р.	$21 000	721 140 р.	480 000 р.
ВАЗ-21006	21	$1 600	54 944 р.	$1 136	39 000 р.	$33 600	1 153 824 р.	819 000 р.
Курс $	34 р.
Наименьшая сумма затрат на покупку				721 140 р.
Наибольшая сумма затрат на покупку				2 403 800 р.
Общая сумма затрат на покупку				4 278 764 р.

Рис.1

12. Построим диаграмму (гистограмма), отображающую объем продаж всех марок с использованием мастера диаграмм.

Для этого выделите интервал ячеек с данными суммы от продаж Вставка/Диаграмма. На первом шаге работы с мастером диаграмм выбераем тип диаграммы  гистограмма; на втором шаге на вкладке Диапазон данных в окошке Диапазон указываем интервал ячеек I4:I6.

Далее вводим название диаграммы и подписи осей; дальнейшие шаги построения диаграммы осуществляются автоматически по подсказкам мастера. Конечный вид диаграммы приведен на рис. 2.

Рис.2, Диаграмма 1

Задача №2

1. Создаем новый лист  Задача 2;

2. Вводим тексты заголовка и шапки таблицы: «Ведомость, учитывающая скидки», «Покупатель», «Кол-во наименований купл.товаров, шт.», «Стоимость покупки, руб.», «Стоимость покупки со скидкой, руб», «Кол-во наименований товаров необходимых для скидки», «Стоимость для скидки К, руб.»;

3. Заполняем таблицу;

4. Введём формулу вычисления стоимости покупки со скидкой в ячейку D3. Для этого:

устанавливаем курсор в ячейке D3;

щелкаем по кнопке <Мастер функций> на панели Стандартная и выберите в диалоговом окне параметры:

Категория: логические

Имя: ЕСЛИ

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

=ЕСЛИ(B3>$B$13;C3-C3*0,1;ЕСЛИ(C3>$B$14;C3-C3*0,1;C3)), далее производим автокопирование формулы диапазоном D3:D12.

5. В ячейке В15 найдем кол-во покупателей потративших больше 50 руб. =СЧЁТЕСЛИ(C3:C12;">50")

6. Для ячеек «стоимость покупки» назначаем формат:

Установливаем курсор в ячейке С3.

В строке меню открываем команду Формат/Условное форматирование и в открывшемся окне Условное форматирование в строке Условие 1 выбираем значение больше или равно В14, формат/вид/заливка – красный, начертание шрифта полужирное, в строке Условие 2 выбираем значение меньше В14, формат/вид/заливка – зеленый. После этого нажмаем кнопку ОК.

Конечный вид таблицы приведен на рис. 3.

Ведомость, учитывающая скидки
Покупатель	Кол-во наименований купл.товаров, шт.	Стоимость покупки, руб.	Стоимость покупки со скидкой, руб
Покупатель №1	3	60	54
Покупатель №2	1	12	12
Покупатель №3	6	65	58,5
Покупатель №4	5	40	40
Покупатель №5	2	100	90
Покупатель №6	4	35	35
Покупатель №7	3	26	26
Покупатель №8	1	22	22
Покупатель №9	4	55	49,5
Покупатель №10	8	120	108
Кол-во наименований товаров необходимых для скидки	5
Стоимость для скидки К, руб.	50
Кол-во покупателей потративших больше 50 руб.	5

Рис.3

Задача №3

1. Создаем новый лист  Задача 3;

2. Заполняем и произвольно форматируем таблицу согласно заданию;

3. Добавляем и заполняем столбцы: «Цена 1 ед. товара», «Кол-во проданного товара, шт», «Продано на сумму»;

4. Произведем расчеты для столбца «Продано на сумму» по следующей формуле:

5. Продано на сумму = количество проданного товара * цену 1 ед. товара, для этого в ячейке J3 набираем формулу: = H3*I3, далее производим автокопирование формулы диапазоном J3:J12

6. Осуществляем сортировку по полям, для этого выделяем в столбце «Модель HDD» диапазон: В3:В12, на панели инструментов выбираем сортировка по убыванию. Аналогично осуществляем сортировку в столбце «Время доступа» - сортировка по возрастанию.

Конечный вид таблицы приведен на рис. 4

Параметры винчестеров
№ п/п	Модель HDD	Шина	Ёмкость, Мб	Скорость вращения, об/мин.	Скорость передачи	Время доступа, мс	Цена 1ед. Товара	Кол-во проданного товара, шт.	Продано на сумму
1	WD AC12110	AT	136	3600	1250	5,18	1509	123	185607
2	SEAGATE ST251	SCSI	2040	7200	5760	9,78	1470	158	232260
3	SEAGATE ST251	AT	202	3314	2734	10,35	1670	190	317300
4	SEAGATE CP30100	AT	105	3456	1500	13	1034	124	128216
5	SEAGATE CP30100	MFM	345	3980	2234	13,22	1098	135	148230
6	QUANTUM PRO700S	MFM	116	3460	2001	14,25	2001	134	268134
7	NEC D 3755	AT	234	3551	2131	19	1765	164	289460
8	MAXTOR 7245A	SCSI	42,8	3600	325	25	1299	179	232521
9	MAXTOR 7245A	SCSI	125	3811	2386	25	1831	184	336904
10	FUJITSU M226E	SCSI	668	4500	3450	28	2147	162	347814

Рис.4

7. Копируем содержимое листа «Задача 3» на новый лист {Правка/Переместить/ Скопировать лист). Ставим галочку в окошке Создавать копию. Осуществляем поиск винчестеров:

Для установления режима фильтра установливаем курсор внутри таблицы и воспользуемся командой Данные/Фильтр/Автофильтр. В заголовках полей появятся стрелки выпадающих списков

В открывшемся окне Пользовательский автофильтр задаем условие для столбца «шина» - «SCSI». В столбце «время доступа» задаем условие «больше 10». Произойдет отбор данных по заданным условиям.

Конечный вид таблицы приведен на рис. 5

	Параметры винчестеров
	№ п/п		Модель HDD		Шина		Ёмкость, Мб		Скорость вращения, об/мин.		Скорость передачи		Время доступа, мс		Цена 1ед. Товара		Кол-во проданного товара, шт.		Продано на сумму
	8		MAXTOR 7245A		SCSI		42,8		3600		325		25		1299		179		232521
	9		MAXTOR 7245A		SCSI		125		3811		2386		25		1831		184		336904
10		FUJITSU M226E		SCSI		668		4500		3450		28		2147		162		347814

Рис.5

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

Для этого выделяем всю таблицу и выполните команду Данные/Итоги. Задаём параметры подсчета промежуточных итогов:

при каждом изменении в  столбец В ;

операция  Сумма;

добавить итоги по: столбец J.

Ставим галочку операциям «Заменить текущие итоги» и «Итоги под данными».

Примерный вид итоговой таблицы представлен на рис. 6

Параметры винчестеров
№ п/п	Модель HDD	Шина	Ёмкость, Мб	Скорость вращения, об/мин.	Скорость передачи	Время доступа, мс	Цена 1ед. Товара	Кол-во проданного товара, шт.	Продано на сумму
	Модель HDD Итог								0
1	WD AC12110	AT	136	3600	1250	5,18	1509	123	185607
	WD AC12110 Итог								185607
2	SEAGATE ST251	SCSI	2040	7200	5760	9,78	1470	158	232260
3	SEAGATE ST251	AT	202	3314	2734	10,35	1670	190	317300
	SEAGATE ST251 Итог								549560
4	SEAGATE CP30100	AT	105	3456	1500	13	1034	124	128216
5	SEAGATE CP30100	MFM	345	3980	2234	13,22	1098	135	148230
	SEAGATE CP30100 Итог								276446
6	QUANTUM PRO700S	MFM	116	3460	2001	14,25	2001	134	268134
	QUANTUM PRO700S Итог								268134
7	NEC D 3755	AT	234	3551	2131	19	1765	164	289460
	NEC D 3755 Итог								289460
8	MAXTOR 7245A	SCSI	42,8	3600	325	25	1299	179	232521
9	MAXTOR 7245A	SCSI	125	3811	2386	25	1831	184	336904
	MAXTOR 7245A Итог								569425
10	FUJITSU M226E	SCSI	668	4500	3450	28	2147	162	347814
	FUJITSU M226E Итог								347814
	Общий итог								2486446

Рис.6

9. Для создания сводной таблицы, выполняем команду Данные/Сводная таблица. На экране появится диалоговое окно Мастер сводных таблиц и диаграмм – шаг 1 из 3.

10. Нажмаем кнопку Далее. На экране появится диалоговое окно, требующее ввести диапазон, содержащий исходные данные (А2:J13). Нажмите кнопку Далее. Перетаcкиваем мышью кнопку Модель HDD на поле Строка, Шина – на поле Столбец, Кол-во проданного товара – на поле Данные.