Задания для самостоятельной работы. Физико-математический факультет

Им. Т.Г. Шевченко

Физико-математический факультет

Кафедра информатики и вычислительной техники

 

 

Л А Б О Р А Т О Р Н А Я Р А Б О Т А № 1

 

по дисциплине:

Информационные технологии

в психолого-педагогических исследованиях»

 

Тема:

Использование библиотеки встроенных функций MS Excel

для статистической обработки и анализа данных»

 

РАЗРАБОТАЛА:

преподаватель кафедры ИиВТ

Калинкова Е.В.

 

 

г. Тирасполь

Цель работы:

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

 

Мастер функций

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

После выбора нужной функции и нажатия на кнопку Enter открывается второе окно мастера функций, в котором следует указать адреса ячеек, содержащих значения аргументов выбранной функции. Окно содержит поля для ввода аргументов, а также краткое описание функции. Адреса ячеек можно либо непосредственно вписать в поля ввода, либо ввести их щелчком мыши в нужных ячейках. Чтобы открылся доступ ко всем ячейкам таблицы окно Мастера функций можно уменьшить либо передвинуть с помощью мыши. Для уменьшения окна нужно щелкнуть на кнопке, расположенной справа в поле ввода аргумента, - окно уменьшится до размеров поля ввода. Теперь можно выбрать нужные ячейки. Перечень выделенных ячеек отображается в поле ввода первого аргумента и строке формул. После выбора ячеек диалоговое окно можно снова увеличить, используя кнопку, расположенную справа в поле аргумента уменьшенного диалогового окна. Для завершения ввода функции следует щелкнуть на кнопке ОК.

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

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

 

Логические функции

 

Функция ЕСЛИ используется при проверке условий для значений и формул. Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Синтаксис:

ЕСЛИ(лог_выражение; значение_если_истина; значение_если_ложь)

 

Функция И возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, возвращает ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Синтаксис:

И(логическое1; логическое2; …)

 

Функция ИЛИ возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА.

Синтаксис:

ИЛИ(логическое1; логическое2; …)

 

Математические функции

 

Функция СУММ суммирует аргументы.

Синтаксис:

СУММ(число1; число2; …)

 

Функция СУММЕСЛИ суммирует ячейки в «диапазоне суммирования», которые соответствуют ячейкам в «диапазоне», удовлетворяющим «условию». Если аргумент «диапазон суммирования» не указывается, суммируются ячейки в «диапазоне».

Синтаксис:

СУММЕСЛИ(диапазон; условие; диапазон суммирования)

Пример 1.

Формула	Описание
СУММЕСЛИ(А1:D1;”>50”)	суммируются числа больше 50 из диапазона А1:D1
СУММЕСЛИ(А1:А20;”инженер”;В1:В20)	суммируются ячейки из диапазона В1:В20, соответствующие ячейкам диапазона А1:А20, содержащим текст “инженер”
СУММЕСЛИ(С5:С17;0;D5:D17)	суммируются ячейки из диапазона D5:D17, соответствующие ячейкам диапазона С5:С17, содержащим 0

 

Статистические функции

 

Функции МИН, МАКС, СРЗНАЧ определяют минимальное, максимальное, среднее значение среди указанных аргументов. Могут иметь от 1 до 30 аргументов. В качестве аргументов можно указывать ячейки и диапазоны ячеек.

 

Функция СЧЕТ подсчитывает количество чисел в списке аргументов.

Синтаксис:

СЧЕТ(значение1; значение2; …)

 

Функция СЧЕТЕСЛИ подсчитывает количество ячеек в «диапазоне», удовлетворяющих «условию». «Условие» указывается так же, как в функции СУММЕСЛИ.

Синтаксис:

СЧЕТЕСЛИ(диапазон; условие)

Пример 2.

Формула	Описание
СЧЕТЕСЛИ(А1:А10;”экономист”)	подсчитывает количество ячеек в диапазоне А1:А10, содержащих текст “экономист”
СЧЕТЕСЛИ(В5:В20;”<60”)	подсчитывает количество ячеек в диапазоне В5:В20, содержащих числа, меньшие 60

 

Функции ПОИСКПОЗ и ИНДЕКС

 

Функция ПОИСКПОЗ возвращает относительное положение элемента массива, который соответствует указанному значению в указанном порядке. Функция ПОИСКПОЗ используется вместо одной из функций ПРОСМОТР, когда требуется позиция элемента в диапазоне, а не сам элемент.

Синтаксис:

ПОИСКПОЗ (искомое_значение; просматриваемый_массив; тип_сопоставления)

Искомое_значение – значение, используемое при поиске значения в таблице.

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

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

Тип_сопоставления – число -1, 0 или 1. Тип_сопоставления указывает, как Microsoft Excel сопоставляет искомое_значение со значениями в аргументе просматриваемый_массив.

· Если тип_сопоставления равен 1, то функция ПОИСКПОЗ находит наибольшее значение, которое меньше либо равно, чем искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию:..., -2, -1, 0, 1, 2,..., A-Z, ЛОЖЬ, ИСТИНА.

· Если тип_сопоставления равен 0, то функция ПОИСКПОЗ находит первое значение, которое в точности равно аргументу искомое_значение. Просматриваемый_массив может быть не упорядочен.

· Если тип_сопоставления равен -1, то функция ПОИСКПОЗ находит наименьшее значение, которое больше либо равно чем искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z-A,..., 2, 1, 0, -1, -2,..., и так далее.

· Если тип_сопоставления опущен, то предполагается, что он равен 1.

Пример 3.

	А	В
	Название	Количество
	Бананы
	Апельсины
	Яблоки
	Груши

Формула	Описание (результат)
=ПОИСКПОЗ(39;B2:B5;1)	Так как точного соответствия нет, возвращается позиция ближайшего меньшего элемента (38) диапазона B2:B5. (2)
=ПОИСКПОЗ(41;B2:B5;0)	Позиция числа 41 в диапазоне B2:B5. (4)
=ПОИСКПОЗ(40;B2:B5;-1)	Возвращает сообщение об ошибке, т. к. диапазон B2:B5 упорядочен по возрастанию (а не по убыванию). (#Н/Д)

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

Синтаксис 1:

ИНДЕКС (массив; номер_строки; номер_столбца) возвращает значение указанной ячейки или массив значений в аргументе «массив».

Массив – это диапазон ячеек или массив констант.

· Если массив содержит только одну строку или один столбец, то соответствующий аргумент номер_строки или номер_столбца не является обязательным.

· Если массив занимает больше, чем одну строку и больше, чем один столбец, а задан только один аргумент номер_строки или номер_столбца, то функция ИНДЕКС возвращает массив из целой строки или целого столбца аргумента массив.

Номер_строки – это номер строки в массиве, из которой нужно возвращать значение. Если номер_строки опущен, то аргумент номер_столбца нужно задавать обязательно.

Номер_столбца – это номер столбца в массиве, из которого нужно возвращать значение. Если номер_столбца опущен, то аргумент номер_строки нужно задавать обязательно.

Пример 4.

	А	В
	Данные	Данные
	Яблоки	Лимоны
	Бананы	Груши

 

Формула	Описание (результат)
=ИНДЕКС(A2:B3;2;2)	Значение пересечения второй строки и второго столбца в диапазоне (Груши)
=ИНДЕКС(A2:B3;2;1)	Значение пересечения второй строки и первого столбца в диапазоне (Бананы)

 

Синтаксис 2:

ИНДЕКС (ссылка; номер_строки; номер_столбца; номер_области) возвращает ссылку на указанные ячейки или ячейки в аргументе «ссылка».

Ссылка – это ссылка на один или несколько интервалов ячеек.

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

· Если каждая область в ссылке содержит только одну строку или один столбец, то аргумент номер_строки или номер_столбца, соответственно, является необязательным. Например, для одиночной строки следует использовать форму ИНДЕКС(ссылка;;номер_столбца).

Номер_строки – это номер строки в аргументе ссылка, на которую возвращается ссылка.

Номер_столбца – это номер столбца в аргументе ссылка, на который возвращается ссылка.

Номер_области – интервал ссылки, из которого требуется возвращать пересечение номер_строки и номер_столбца. Первая введенная или выделенная область имеет номер 1, вторая 2 и так далее. Если номер_области опущен, то функция ИНДЕКС использует область номер 1.

· Например, если аргумент ссылка описывает ячейки (A1:B4;D1:E4;G1:H4), то номер_области 1 соответствует интервалу A1:B4, номер_области 2 соответствует интервалу D1:E4 и номер_области 3 соответствует интервалу G1:H4.

Пример 5.

	А	В	С
	Фрукт	Цена	Количество
	Яблоки	0,69
	Бананы	0,34
	Лимоны	0,55
	Апельсины	0,25
	Груши	0,59
	Миндаль	2,80
	Фисташки	3,55
	Арахис	1,25
	Грецкие орехи	1,75

 

Формула	Описание (результат)
=ИНДЕКС(A2:C6;2;3)	Пересечение второй строки и третьего столбца в диапазоне A2:C6, которым является содержимое ячейки C3. (38)
=ИНДЕКС((A1:C6;A8:C11);2;2;2)	Пересечение второй строки и второго столбца во второй области диапазона A8:C11, которым является содержимое ячейки B9. (1,25)

Пример 6.

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

 

месяц год	I	II	III	IV	V	VI	VII	VIII	IX	X	XI	XII
	37,2	11,4	16,5	19,5	11,7	129,1	57,1	43,8	85,7	86,0	12,5	21,2
	34,5	51,3	20,5	26,9	45,5	71,5	152,9	96,6	74,8	14,5	21,0	22,3
	8,0	1,2	3,8	11,9	66,3	60,0	50,6	145,2	79,9	74,9	56,6	9,4

 

Определить:

1) самый засушливый месяц в году;

2) самый влажный месяц в году;

3) общее количество осадков, выпавших за год;

4) количество осадков, которое выпадает в среднем за один месяц в году;

5) количество засушливых месяцев в году, когда количество осадков меньше 10 мм;

6) наиболее влажный сезон (весна, лето или осень) в году.

7) самый влажный месяц за три года.

 

Решение задачи средствами Excel.

1. Оформлена таблица 1, которая построена на основе наблюдений метеостанции (рис. 1).

2. На этом же листе создана и оформлена табл. 2 (рис. 2).

Рис. 1

Рис. 2

 

3. Ячейка H5 используется для хранения номера года. Для удобства записи формул в табл. 2 ячейке H5 присвоено имя год и назначена автоматическая проверка на корректность вводимых значений командой меню Данные, Проверка (рис. 3).

4. Для определения самого засушливого месяца в году в ячейку H7 введена формула

=ЕСЛИ(год=1992;МИН(C6:C17);ЕСЛИ(год=1993;МИН(D6:D17);МИН(E6:E17)))

в ячейку I7 введена формула

=ЕСЛИ(год=1992;ПОИСКПОЗ(H7;C6:C17;0);ЕСЛИ(год=1993;

ПОИСКПОЗ(H7;D6:D17;0);ПОИСКПОЗ(H7;E6:E17;0)))

в ячейку J7 введена формула

=ИНДЕКС(B6:B17;I7)

 

Рис. 3

 

5. Аналогично для определения самого влажного месяца в году в ячейку H8 введена формула

=ЕСЛИ(год=1992;МАКС(C6:C17);ЕСЛИ(год=1993;МАКС(D6:D17);МАКС(E6:E17)))

в ячейку I8 введена формула

=ЕСЛИ(год=1992;ПОИСКПОЗ(H8;C6:C17;0);ЕСЛИ(год=1993;

ПОИСКПОЗ(H8;D6:D17;0);ПОИСКПОЗ(H8;E6:E17;0)))

в ячейку J8 введена формула

=ИНДЕКС(B6:B17;I8)

6. Для определения общего количества осадков за год в ячейку H9 введена формула

=ЕСЛИ(год=1992;СУММ(C6:C17);ЕСЛИ(год=1993;СУММ(D6:D17);СУММ(E6:E17)))

7. Для определения среднемесячного количества осадков за год в ячейку H10 введена аналогичная формула СРЗНАЧ, а в ячейку H11 – аналогичная формула с функцией СЧЕТЕСЛИ для определения засушливых месяцев в году.

8. Для определения самого влажного сезона в году введена вспомогательная таблица (рис. 4). В ячейку С20 введена формула =СУММ(С8:С10), которая затем скопирована в ячейки D20:E20. Аналогично вычисляется количество осадков для других сезонов.

 

Рис. 4

 

9. В ячейку С24 введена формула

=ПОИСКПОЗ(МАКС(C20:C23);C20:C23;0), которая затем скопирована в ячейки D24:E24.

В ячейку C25 введена формула

=ЕСЛИ(C24=1;$B$20;ЕСЛИ(C24=2;$B$21;ЕСЛИ(C24=3;$B$22;$B$23))),

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

10. Для выбора самого влажного сезона в табл. 1 в ячейку H12 введена формула

=ЕСЛИ(год=1992;C25;ЕСЛИ(год=1993;D25;E25))

11. Для определения самого влажного месяца за три года в табл. 1 в ячейку H13 введена формула

=МАКС(C6:E17).

Задания для самостоятельной работы

1. Известны оценки (по пятибалльной шкале), полученные абитуриентами на каждом из трех вступительных экзаменов. Определить:

1) для каждого абитуриента сумму набранных им баллов;

2) для каждого экзамена среднюю оценку;

3) сколько абитуриентов набрали на экзаменах 15 баллов, сколько 14 баллов, …, сколько 9 баллов. Условно принять, что среди оценок нет ни одной двойки.

 

2. Известны оценки (по пятибалльной шкале), полученные абитуриентами на каждом из трех вступительных экзаменов. Абитуриенты, получившие оценку «2», к следующему экзамену и к конкурсу на поступление не допускаются. Значение проходного балла (минимально необходимой суммы баллов) для поступления указывается в отдельной ячейке. Определить:

1) для каждого абитуриента, допущенного к конкурсу, сумму набранных баллов;

2) количество абитуриентов, успешно сдавших вступительные экзамены;

3) для каждого абитуриента, поступил ли он в учебное заведение;

4) количество абитуриентов, принятых в учебное заведение.

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

 

3. На вступительных экзаменах в колледж в конкурсе на поступление участвовали 130 человек. Распределение абитуриентов по сумме набранных баллов приведено в таблице:

Сумма баллов	Кол-во абитуриентов

 

В колледж могут быть приняты не более 80 человек. Определить:

1) сколько абитуриентов набрали на экзаменах 15 баллов, сколько не менее 14 баллов, сколько не менее 13 баллов, …, сколько не менее 9 баллов;

2) проходной балл, т.е. минимальную сумму баллов, достаточную для поступления.

4. Создайте экзаменационные ведомости для группы по трем видам дисциплин: математика, информатика и физика.

 

 

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

• если средний балл не менее 4.5, выплачивается 50%-ная надбавка к минимальной стипендии;

• если средний балл от 4 до 4.5 (включительно), выплачивается минимальная стипендия;

• если средний балл меньше 4, стипендия не выплачивается.

 

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

1. Как просмотреть библиотеку встроенных функций Excel? Какие разделы она включает?

2. Опишите алгоритм вставки функции с помощью Мастера функций.

3. Назовите основные логические, статистические, математические и др. функции для обработки и анализа статистических данных.

4. Приведите примеры использования этих функций.