Контрольная работа №2

Решение: Эту задачу также как и предыдущую можно решить с помощью функции ЕСЛИ( ). При этом понадобиться использовать вложенные функции ЕСЛИ ( ). Итак, в качестве проверяемого условия будет выступать Сумма баллов. Тогда итоговая формула будет выглядеть следующим образом:

=ЕСЛИ(Сумма_баллов<=50;‖Плохо‖;ЕСЛИ(Сумма_баллов<65; ‖Удовлетворительно‖; ЕСЛИ(Сумма_баллов<85;‖Хорошо‖;‖Отлично‖)

Пример 8. Дан список названий месяцев. Определить, какой по счету в данном списке месяц «мар»?

Для определения порядкового номера «мар» воспользуемся функцией ПОИСКПОЗ( ).

ПОИСКПОЗ( ) возвращает относительную позицию в массиве элемента, соответствующего указанному значению с учетом указанного порядка.

В нашем случае формула будет имеет вид: =ПОИСКПОЗ("мар";A2:E2;0), где "мар" – искомое_значение (что ищем);

A2:E2 – Просматриваемый_массив (диапазон ячеек для поиска (обязательно либо строка, либо столбец)); 0 – тип_сопоставления, при котором функция ПОИСКПОЗ находит

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

Пример 9. В исходной таблице, найти число, расположенное под цифрой 5 в третьей строке таблицы.

Для решения этой задачи воспользуемся функцией ГПР( ).

Функция ГПР( ) предназначена для поиска исходного значения (в нашем примере цифра 5) в самой верхней строке таблицы (диапазон B7:F9) и изъятие значения из ячейки, находящейся в строке с заданным номером того же столбца, что и исходное значение (в нашем примере строка 3).

21

0 - интервальный просмотр, указывающий что поиск в первой строке должен производится точно

Ответом в данном примере будет число 45

Пример 10. В исходном диапазоне найти символ, стоящий в одной строке с цифрой 3.

Для решения данного примера воспользуемся функцией ВПР( ).

Функция ВПР( ) является транспонированной функцией ГПР( ), т.е. исходное значение ищется в самом первом столбце диапазона, и значение извлекается из столбца с заданным номером той же строки, что и исходное значение.

В результате получим значение «а»

Функции ГПР( ) и ВПР( ) являются аналогами многократного использования в формуле функции ЕСЛИ( )

Пример 11. Найти значение, находящееся на пересечении 2-ой строки и 4-ого столбца.

Для решения данной задачи воспользуемся функцией ИНДЕКС( ).

22

Функция ИНДЕКС( ) осуществляет поиск ячейки в заданном диапазоне на пересечении строки и столбца с заданными номерами.

В результате применения в нашем примере функции ИНДЕКС( ) получим число 40.

Диаграммы

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

В версиях MS Excel 2007, 2010 добавление диаграммы на рабочий лист осуществляется через вкладку Ленты Вставка  Диаграммы. К основным типам диаграмм относятся:

1.Гистограмма – один из наиболее распространенных типов диаграмм. Используется для сравнения значений по категориям.

2.Линейчатая – это гистограмма, повернутая на 900 по часовой стрелке. Преимущество данного типа диаграмм состоит в том, что метки категорий читаются в них проще.

3.График отображает зависимость данных (ось Y) от величины значения X(ось Х) с постоянным шагом. Метки по оси Х должны располагаться по возрастанию или убыванию.

4.Круговая отображает соотношение между частями единого целого и строится только по одному ряду.

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

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

7.Кольцевая– аналог круговой. Отличие состоит в том, что круговая диаграмма может отражать два и более ряда данных.

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

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

23

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

11.Биржевая используется для отображения информации о ценах на бирже.

И др.

На рисунке 6 представлены основные элементы диаграммы.

		Заголовок диаграммы
Область диаграммы

Область

построения

Ряды данных

		Легенда
Ось
значений

Ось категорий		Точка данных

Рисунок 6. Элементы диаграммы

Для создания диаграмм следует подготовить данные для еѐ построения и определить тип. Необходимо учитывать следующее:

1.количество рядов данных (ось Y) должно быть меньше, чем число категорий (ось X).

–Если диаграмма строится для диапазона ячеек, имеющего большее или равное число столбцов, чем строк, то рядами данных считаются строки.

–Если диапазон ячеек имеет больше строк, то рядами данных считаются столбцы

2.названия, связанные с рядами данных, считаются их именами и составляют легенду диаграммы.

24

3.Данные, интерпретируемые как категории, считаются названиями категорий и выводятся вдоль оси X.

4.Если в ячейках, которые Excel будет использовать как название категорий, содержатся числа (не текст и не даты), то Excel разместит эти данные на графике в качестве рядов данных. Изменить данные построения можно во вкладке Работа с диаграммами  Конструктор  Данные  Выбрать данные Подписи горизонтальной оси (категорий) Изменить.

Операции над диаграммами:

•Добавлять или удалять ряды данных. Их можно удалять непосредственно даже на графике

•Редактировать данные в диаграмме и на рабочем листе

•Переставлять ряды данных на диаграмме

•Вставлять текст в любом месте диаграммы

•Редактировать, форматировать и добавлять различные элементы диаграмм с помощью контекстного меню для необходимого элемента диаграммы

•Изменять пространственную ориентацию трѐхмерных диаграмм

•Добавлять различные графические объекты в диаграмму

•Настраивать оси и выбирать шкалы

•Строить составные диаграммы

•Использовать нестандартные типы диаграмм

•Применять диаграммы для анализа данных, то есть, строить различные линии тренда и делать прогнозы

Рекомендуемая методика построения диаграмм:

1.Подготовить диапазоны данных для диаграммы (создать таблицу)

2.Выделить подготовленные данные

3.Открыть вкладку Вставка

4.В разделе Диаграммы выбрать нужный тип диаграммы

5.Отформатировать полученную диаграмму

25

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

1.Какое ПО называется табличным процессором?

2.Перечислите основные функции табличного процессора.

3.Какие элементы составляют структуру окна MS Excel?

4.Перечислите основные элементы документа MS Excel

5.Перечислите основные типы данных, которые можно разместить в ячейках MS Excel

6.Какие приемы быстрого выделения используются в MS Excel

7.Что понимается под понятием «форматирование ячеек»?

8.Какие элементы шаблонов используются для создания пользовательских форматов?

9.Как использовать цвет в пользовательских форматах?

10.Можно ли использовать условие в пользовательских форматах?

11.Что понимается под «Условным форматированием» в MS Excel? 12.Какие виды форматирования позволяет выполнить условное

форматирование?

13.Для чего используется проверка данных в ячейках MS Excel? 14.Что понимается под формулой в MS Excel?

15.Какие операторы используются в формулах?

16.Что представляет собой адрес ячеек?

17.Для чего в формулах используется относительный адрес ячеек? 18.Для чего в формулах используется абсолютный адрес ячеек? 19.Что такое смешанная адресация ячеек?

20.Что понимается под функцией в MS Excel?

21.Перечислите основные категории функций в MS Excel.

22.Как проверить правильность работы формулы?

23.Для чего в MS Excel применяются диаграммы?

24.Перечислите основные типы диаграмм.

25.В каких случаях используются гистограммы?

26.В каких случаях используются линейчатые диаграммы?

27.В каких случаях используются графики?

28.В каких случаях используются круговые диаграммы?

29.В каких случаях используются точечные диаграммы?

30.В каких случаях используются диаграммы с областями?

31.В каких случаях используются кольцевые диаграммы?

32.В каких случаях используются лепестковые диаграммы?

33.В каких случаях используются поверхности?

34.В каких случаях используются пузырьковые диаграммы?

26

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

36.Опишите порядок построения диаграммы

37.Перечислите операции, которые можно выполнять над диаграммами.

27

Варианты контрольной работы

Вариант 1

Фирма построила небольшой 4-х этажный дом за Х$. Требуется установить продажные цены на квартиры, исходя из их площади и потребительских качеств. Устанавливаются скидки на квартиры, находящиеся на 1-ом и последнем (4-ом) этажах, на квартиры, площадью больше 150 кв.м и квартиры, выходящие окнами на улицу (буквой «д» обозначаются квартиры с окнами во двор, «у» - с окнами на улицу). Все скидки представлены в ячейках А2:Е3. В столбце «Эквивалентная площадь» вычисляется некоторая теоретическая площадь, соответствующая цене со скидкой. Суммарная цена квартир должна совпадать со стоимостью дома В1.

	A	B	C	D	E	F	G	H
1	Стоимость	Х $
	дома
2	Вид	Окна на	1	4	Более
	скидки:	улицу	этаж	этаж	150 кв.м
	% скидки:	5%	10%	5%	3%
				ПРОДАЖА КВАРТИР
	Квартира	Площадь	Этаж	Сторо	Оплата со	Эквивал.	Цена	Цена 1
	(№)			на	скидкой (%)	площадь	квартиры	кв.м
	Х	Х кв.м	Х	Х	? %	? кв.м	? $	? $
…	…
	ВСЕГО	? кв.м				? кв.м	? $

Контроль столбец Сторона – сторона={д,у} Форматирование в столбце Квартира №: выделить номера квартир

(разным цветом) с минимальной и максимальной стоимостью 1 кв.м. Диаграмма: № квартиры – Цена квартиры.

Вариант 2

Вычислить <Цену авиабилета> в зависимости от полной протяженности маршрута до всех пунктов посадок (если есть).

Цена билета состоит из трех слагаемых:

1.Стоимости собственно перевозки пассажира, определяемой умножением длины маршрута на <Стоимость 1 км.>полета. Последняя не постоянна. Если длина перелета менее 1000 км., она равна 0,5 руб., если от 1000 до 3000 - меньше на 10%, если свыше 3000 - меньше на 15%. Причем по меньшему тарифу оплачивается только та часть маршрута, которая приходится в соответствующий диапазон.

28

2.Стоимости питания. Пассажиров кормят каждые 1000 км полета на сумму в 100 руб.

3.Стоимости доставки в аэропорт. Она составляет 100 руб. и выполняется только для пассажиров, следующих на расстояние не менее 3000 км.

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

	А	В	С	D	Е	F	G
1	Питание пассажиров:					Стоимость 1 км
						полета пассажира
2	расстояние	стоимость				до 1т. км	0,5р
3	1000км	100р				до 3т. км	10%
…						свыше 3т.	15%
…			СТОИМОСТЬ АВИАПЕРЕВОЗОК
…	Номер	Расстояние до пунктов посадки			Общая	Стоимость
					длина		Цена билета
…		1-й	2-й	3-й
	рейса				маршрута	питания
…	Х	Хкм	Хкм	Хкм	?км	?р	?р
…	• • •		• • •
…	ВСЕГО	?км	?км	?км	?км	?р	?р
…	Средняя длина маршрута				?км
…	Число рейсов:
…	беспосадочных			?
…	с одной посадкой			?
…	с двумя посадками			?

Контроль Расстояние до пунктов посадки(1-й,2-й,3-й):

100км < Расстояние до пункта посадки < 6000км.

Форматирование Номер рейса: если общая длина маршрута > 10000км. Диаграмма: Номер рейса - Цена билета.

Вариант 3

Вычислить стоимость автоперевозок заданного веса груза на заданное расстояние разными типами автомобилей. Путевая скорость всех типов а/м считается равной 50 км/час. Здесь <Число ездок> это <Вес груза>/<Грузоподъемность>, округленное до большего целого; <Пробег> это <Число ездок>*<Расстояние>*2 (удваивается, поскольку автомобиль каждый раз должен возвращаться в исходный пункт). <Стоимость перевозок> состоит из зарплаты и стоимости аренды. <3арплата> водителя определяется <Временем в пути>. Кроме того, если автомобиль находится в пути в оба конца больше 12 часов, водителю производится доплата (командировочные) в размере 50 руб. за каждые 12 часов в пути на каждой поездке. В области Объем заказов подсчитывается число машиночасов (время в пути), необходимых для обслуживания заявок на перевозки разными типами а/м.

29

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

ВПР( ).

	А	В	С	D	Е		F	G	Н	I
1		Тарифы		Сводка						Цена часа
										работы
2	Тип	Цена	Грузо-	Объем
	автомо	часа	подъем-	заказов						водителя
	биля	аренды	ность
3	Зил	80р	4т	?ч						Хр
4	Х	Хр	Хт	?ч
…	• • •	• • •	• • •	• • •
…				АВТОПЕРЕВОЗКИ
…	Фирма	Тип	Вес	Расстояни	Число	Время в		Пробег	Зарплата	Стоимость
		а/м	груза	е	поездо	пути				перевозок
					к
…		Зил	Хт	Хкм	?		?ч	?км	?р	?р
…		• • •			…
…	Всего		?т	?км			?ч	?км	?р	?р

Контроль: столбец Тип а/м в таблице АВТОПЕРЕВОЗКИ ={3ил, Газ, Камаз, …}.

Форматирование столбец Тип а/м: если Пробег> 1000км. Диаграмма: Фирма - Объем заказов.

Вариант 4

Вычислить величину квартплаты. Она определяется количеством квадратных метров <Площади>, умноженных на <Цену 1 квадратного метра>. Кроме того, если в квартире имеется излишек площади относительно социальной нормы, он оплачивается в двойном размере. Излишек определяется как <Площадь> квартиры минус число проживающих в ней <Человек>, умноженное на <Социальную норму>. Если в квартире проживает один человек, ему положена удвоенная социальная норма. Некоторым категориям жильцов предоставляются льготы при оплате коммунальных услуг. Инвалиды платят на 25%, а участники войны на 50% меньше. Эти лица отмечены в колонке <Льготы> буквами "и", "у" или "иу" соответственно. Кроме того, для жильцов первого этажа квартплата снижается на 20% в виду отсутствия необходимости платить за лифт, а жильцам второго — на 10% по тем же причинам. Фактическая квартплата здесь учитывает все возможные скидки. В клетке Е… подсчитывается число квартир с льготной оплатой.

30