Полезные функции excel для егэ

Excel – Программа для расположения и обработки данных в таблице.

Ячейке можно присвоить формулу, которая будет брать данные из других ячеек и обрабатывать их. (Например: Суммирование, среднее значение и т.д. )

Можно распространять формулу на весь столбец.

Примерные задачи 9 задания ЕГЭ по информатике 2021

Задача (Среднее значение с условием)

Откройте файл электронной таблицы, содержащей результаты тестирования обучающихся по математике и информатике. Каков средний балл по информатике среди участников тестирования, получивших зачётные баллы по этому предмету (т.е. не менее 44 баллов)?
В ответе округлить число до двух знаков после запятой.

Решение:

Для решения этой задачи будем использовать функцию =СРЗНАЧЕСЛИ().

Кликнем в свободную ближайшую ячейку E2. Здесь мы хотим получить ответ на задачу. Нажмём на кнопку «Вставить функцию«, выберем категорию «статистические«, а затем выберем функцию СРЗНАЧЕСЛИ.

В диапазоне нужно указать все ячейки с числами столбца «Информатика» (Столбец D). Значит, напишем: D2:D273 (Чтобы понять сколько строчек в таблице, нужно прокрутить всю таблицу до самого конца вниз.). В условии напишем: >=44 (Т.к. мы должны считать среднее значение для тех ячеек, где число не меньше 44).

Нажимаем «ОК» и получаем в ячейке E2 число 68,60851064. Нам его нужно округлить до 2 знаков после запятой. Воспользуемся инструментом в программе Excel для округления.

Найдём кнопки «Уменьшить разрядность» и «Увеличить разрядность«. Их можно применять к выделенной ячейке.

Уменьшим разрядность до 2 знаков. Получается число 68,61. Это и будет ответ!

Примечание 1: Если мы хотим найти среднее значение без условия, есть функция СРЗНАЧ.

Примечание 2: СРЗНАЧЕСЛИ (СРЗНАЧ)- это и есть среднее арифметическое значение.

Ответ: 68,61

Посмотрим ещё один важный пример 9 задания ЕГЭ по информатике нового формата 2021.

Задача (Количество с условием)

Откройте файл электронной таблицы, содержащей данные о сотрудниках одного из предприятий города Москвы. Сколько человек в таблице имеют возраст меньше 30 лет, и их вес превышает 100 кг?
В ответе напишите одно целое число.

Решение:

Сначала поставим 1 в столбце F в тех строчках, которые удовлетворяют нашему условию: человек имеет возраст меньше 30 лет, и его вес превышает 100 кг. Если строчка не удовлетворяет условию, значит, в ячейке столбца F поставим 0.

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

В поле «Лог_выражение» задаём условие : И(C2>100; E2<30). Т.к. сначала мы задаём формулу для второй строки, то пишем ячейки C2 и E2. Мы написали условие нашей задачи для второй строки. Столбец C — отвечает за Вес, а столбец E — за возраст.

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

В поле «Значение_если_истина» ставим 1. В поле «Значение_если_ложь» ставим 0. Нажимаем «ОК».

Получилось, что в ячейке F2 высветился ноль. Значит, первый сотрудник не удовлетворяет условию задачи.

Теперь распространим формулу на весь столбец F. Подводим мышку к ячейке F2 к правому нижнему углу. Когда появился чёрный крестик, нажимаем левую кнопку мыши и тянем вниз, распространяя формулу на весь столбец.

Где-то будут получатся нули, а где-то единицы. Всё зависит от характеристик сотрудника.

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

Воспользуемся функцией СУММ. Кликнем по ячейке G2. Именно здесь получим ответ. Затем нажмём на кнопку «Вставить функцию» и выберем СУММ из категории «Математические«.

В поле Число1 зададим интервал F2:F301 (Ведь всего у нас 301 строчка в таблице).

Нажимаем «ОК» и получаем в ячейке G2 ответ на нашу задачу. В ответе напишем 14.

Примечание: Для подчёта количества с условием можно использовать функцию СЧЁТЕСЛИ. Попробуйте решить с помощью неё самостоятельно.

Ответ: 14

Продолжаем тренироваться решать 9 задания из ЕГЭ по информатике нового формата 2021.

Задача (Наименьшее значение + условие ИЛИ)

Откройте файл электронной таблицы, содержащей результаты тестирования обучающихся по математике, информатике и физике. Чему равна наименьшая сумма баллов по математике и информатике среди школьников, получивших более 50 баллов по математике или информатике?

В ответе запишете целое число.

Решение:

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

Выпишем сумму двух предметов, для удовлетворяющих условию строчек, в столбец F, с помощью функции ЕСЛИ.

В начале нужно задать формулу для одной ячейки F2. Кликаем по ячейке F2, нажимаем на кнопку «Вставить функцию«, выбираем функцию ЕСЛИ.

В поле «Лог_выражение» пишем: ИЛИ(C2>50; D2>50). В поле «Значение_если_истина«: C2+D2. В поле «Значение_если_ложь«: «» (Пустые двойные кавычки).

Важно: Здесь мы в поле «Значение_если_ложь» должны поставить пустые двойные кавычки, ведь, если поставить ноль, тогда, при поиске минимального значения будем находить всегда этот ноль. Мы ноль ставим в это поле только тогда, когда ищем количество строчек, ведь, тогда мы суммируем единицы, а ноль при суммировании не влияет на результат.

Нажимаем «ОК», и в ячейке F2 получается число 160. Это говорит о том, что первая строчка удовлетворяет условию задачи. И теперь в ячейке F2 лежит сумма баллов по математике и информатике для первого учащегося.

Распространим данную формулу на весь столбец F.

В основном получатся числа, но где-то ячейки останутся пустыми. Это значит, что эти строчки не подошли под условие задачи.

Теперь нужно с помощью функции МИН найти минимальное значение в столбце F.

Кликаем по ячейке G2, нажимаем «Вставить функцию«, выбираем функцию МИН из категории «Статистические«.

В поле Число1 пишем диапазон столбца F: F2:F273.

Получаем ответ 75.

Ответ: 75

Продолжаем оттачивать навыки работы с электронными таблицами в 9 задании из ЕГЭ по информатике нового формата 2021.

Задача (Среднее значение + сложное условие)

Откройте файл электронной таблицы, содержащей результаты тестирования обучающихся по математике, информатике и физике. Каков средний балл по математике обучающихся, набравших не менее 60 баллов по информатике?

В ответе запишите только целую часть получившегося числа.

Решение:

Эту задачу решим двумя способами.

1 Способ

В этом способе решения будем снова использовать функцию СРЗНАЧЕСЛИ.

Кликаем по ячейке F2 и вставляем функцию СРЗНАЧЕСЛИ из категории «Статистические«.

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

В поле «Диапазон» пишем: D2:D273 (Это диапазон, который участвует в условии).

В поле «Условие«: >=60 (Это само условие, которое работает в диапазоне, написанный выше).

В поле «Диапазон_усреднения«: C2:C273 (Это диапазон, для которого будет найдено среднее арифметическое значение. Будут участвовать только те строчки, которые прошли условие).

Если последнее поле остаётся пустым, то среднее значение ищется по первому полю.

Нажимаем «ОК», и получаем число 60,0301205. Округлим это число до целых, получим число 60.

2 Способ

Этот способ подходит и для более запутанных ситуаций.

Для строчек, где выполняется условие задачи, перенесём значения столбца С в столбец F.

Кликаем в ячейку F2, нажимаем кнопку «Вставить функцию«, выбираем функцию Если.

Сначала нужно записать формулу для одной строки.

В поле «Лог_выражение» пишем: D2>=60 (Условие того, что человек набрал не менее 60 баллов по информатике)

В поле «Значение_если_истина» пишем: C2 (Переносим само значение столбца математики. Именно впоследствии мы должны найти среднее значение баллов по математике).

В поле «Значение_если_ложь» пишем: «» (Пустые кавычки. Нельзя писать ноль, иначе мы сильно занизим реальное среднее значение).

Нажимаем «ОК». Получается число 81 в ячейке F2.Оно перенеслось из ячейки C2, ведь значение по информатике больше 60 для этой строчки.

Распространяем формулу на весь столбец.

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

Когда столбец готов, легко найти среднее значение для столбца F.

Кликаем в ячейку G2 и вставляем функцию СРЗНАЧ. В этой функции указываем диапазон F2:F273.

Получается число 60,0301205. Округляем его до целых, получается число 60.

Ответ: 60

Не редкий гость в примерных задачах 9 задания из ЕГЭ по информатике 2021.

Задача (Найти проценты)

Откройте файл электронной таблицы, содержащей данные о количестве белков, жиров и углеводов для различных продуктов. Сколько процентов от общего числа составили продукты, у которых более 10 г белка?

В ответе напишите одно целое число.

Решение:

Задачи на проценты обычно сводятся к нахождению количества.

Найдём сколько продуктов, у которых белка более 10 г.

Кликаем по полю E2. Применим функцию СЧЕТЕСЛИ из категории «Статестические«.

Диапазон указываем: C2:C1001 (Столбец белка).

В поле критерий пишем: >10.

Нажимаем кнопку «ОК». В поле E2 получается число 373.

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

Кликаем по верхнему полю («строка формул»):

Дописываем: =СЧЁТЕСЛИ(C2:C1001;»>10″)/1000 * 100

Всего у нас 1000 наименований продуктов. Строчек 1001, но начинаются продукты со второй строчки. Поэтому делим на 1000 (1001 — 1). Умножаем на 100, чтобы найти проценты.

Нажимаем Enter. Получается число 37,3. Округлим до целого числа, ответом будет 37.

Ответ: 37

В следующей примерной задаче 9 задания ЕГЭ по информатике 2021 будем комбинировать уже изученные приёмы.

Задача (Комбинируем приёмы)

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

Сначала нужно найти среднее значение наценки. Запишем наценку в столбце E.

Запишем формулу сначала для одной ячейки E2. Кликаем по ячейки E2, затем кликаем по верхнему полю («строка формул») и пишем: =C2-B2.

Примечание: Формула начинается со знака «равно»!

Нажимаем Enter. Получается число 9,42.

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

Найдём среднее значение наценки, которая находится в столбце E.

Кликаем по ячейке F2, затем на кнопку «Вставить функцию«, выбираем СРЗНАЧ. Диапазон указываем: E2:E100. Нажимаем «ОК» и получаем число 58,25. Это и есть среднее значение для наценки.

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

Сначала запишем формулу для одной строчки. Кликаем по ячейки G2, нажимаем «Вставить функцию«, выбираем ЕСЛИ.

В поле «Лог_выражение» пишем: E2>58,25. Если наценка выше среднего, то будем считать прибыль.

Т.к. в столбце E все числа имеют не более двух знаков после запятой и число 58,25 тоже имеет два знака после запятой, то мы не получим ошибок из-за округления среднего значения.

В поле «Значение_если_истина» : E2 * D2 (Умножаем наценку на количество проданных товаров. Получаем прибыль).

В поле «Значение_если_ложь«: «» (Пустые кавычки. Ведь мы потом будем искать максимальное значение для прибыли, для товаров, у которых наценка выше среднего значения).

Нажимаем «ОК». Получается в G2 пустое окошко. Значит, для этого товара наценка не выше среднего значения.

Распространим формулу на весь столбец G. Где-то окажутся числа, а где-то пустые ячейки.

Кликнем по ячейке H2 и найдём с помощью функции МАКС (категория «Статистические») максимальную прибыль, в диапазоне указав: G2:G100. Округлив до целых, в ячейке H2 получается ответ 10185.

Примечание: Мы могли при составлении условия E2>58,25 использовать название ячейки F2 вместо числа 58,25. Но тогда нужно её «зацементировать» т.е. E2>$F$2 (Использовать абсолютную адресацию). Если мы всё-таки решили написать число, то нужно следить, чтобы точность была одинаковая у среднего значения, и у тех ячеек, которые мы сравниваем с этим средним значением.

Ответ: 10185

Последнее. Если в задаче говорится о значении по абсолютной величине, т.е. о значении по модулю, то нужно использовать конструкцию ABS(). Например, разница по абсолютной величине двух ячеек A2 и B2 равна ABS(A2-B2). Это можно использовать при составлении формул.

Так же при подготовке к 9 заданию может быть полезна статья, которая была написана под старый формат ЕГЭ по информатике.

На этом всё. Комбинируйте приёмы, которые мы рассмотрели в этом уроке при подготовке к 9 заданию из ЕГЭ по информатике нового формата 2021. Удачи!

---

0 комментария

ГОСУДАРСТВЕННОЕ
БЮДЖЕТНОЕ ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ДОПОЛНИТЕЛЬНОГО ПРОФЕССИОНАЛЬНОГО
ОБРАЗОВАНИЯ РЕСПУБЛИКИ КРЫМ

«КРЫМСКИЙ
РЕСПУБЛИКАНСКИЙ ИНСТИТУТ ПОСТДИПЛОМНОГО ПЕДАГОГИЧЕСКОГО ОБРАЗОВАНИЯ»

Кафедра естественно-математического образования

Сабитова Д.А.,

преподаватель кафедры ЕМО

ТЕМА 2.5. МЕТОДИКА РЕШЕНИЯ ЗАДАЧ ЕГЭ И ОГЭ С
ИСПОЛЬЗОВАНИЕМ ЭЛЕКТРОННЫХ ТАБЛИЦ MS EXCEL

(практическое занятие — 4 часа)

План

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

2. Статистические и логические функции и их применение в электронных таблицах.
Сортировка данных, применение фильтров.

3. Решение задач ЕГЭ и
ОГЭ с использованием
электронных таблиц.

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

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

Табличный процессор содержит набор компьютерных
инструментов для работы с информацией, представленной в табличной форме — в
виде электронной таблицы. Рабочая область электронной таблицы напоминает по
своей структуре шахматную доску. Она состоит из строк и столбцов, имеющих свои
имена.

Основное назначение табличного процессора —
автоматизация расчетов данных, представленных в табличной форме. Результатом
работы табличного процессора является документ в виде таблицы или диаграммы.

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

Таблица представляет собой сложный объект,
состоящий из элементарных объектов: строк, столбцов, ячеек, диапазонов ячеек.
Каждый элементарный объект имеет имя, которое определено разработчиками
электронной таблицы.

ü   
Строка.
Заголовки строк представлены в виде целых чисел, начиная с 1.

ü   
Столбец. Заголовки
столбцов задаются буквами латинского алфавита: сначала от А до Z, затем от АА
до AZ, от ВА до BZ и т. д.

ü   
Ячейка. Адрес
ячейки определяется ее положением в таблице и образуется из заголовков столбца
и строки, на пересечении которых она находится. Сначала записывается заголовок
столбца, а затем номер строки, например: A3, D6, АВ46.

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

Для ввода данных в электронную таблицу
необходимо переместить табличный курсор в ячейку, в которую необходимо ввести
данные. Данные, хранящиеся в ячейках таблицы подразделяются на 3 типа: текст,
число или формула. Ввод формулы начинается со знака равно: «=». Формула может
содержать числа, знаки арифметических операций, скобки, встроенные функции и
адреса ячеек.

Адрес ячейки, изменяющийся при копировании формулы, называется относительным.
Он имеет вид В1, А3. По умолчанию программа Excel рассматривает адреса ячеек
как относительные.
Относительная адресация ячеек – обычное явление при вычислениях по
формулам. При копировании такой формулы вправо или влево будет изменяться
заголовок столбца в имени ячейки, а при копировании вверх или вниз – номер
строки.

Абсолютная адресация используется в том случае, когда нужно использовать
значение, которое не будет меняться в процессе вычислений. Тогда записывают,
например, так: =$А$5. Соответственно, при копировании такой формулы в другие
ячейки текущего рабочего листа, в них всегда будет значение =$А$5. Для того,
чтобы задать ячейке абсолютный адрес, необходимо перед номером строки и номером
столбца указать символ “$” либо нажать клавишу F4.

Смешанная адресация представляет собой комбинацию относительной и абсолютной
адресаций, когда одна из составляющих имени ячейки остается неизменной при
копировании. Примеры такой адресации: $A3, B$1.

Важная причина использования электронных таблиц — это возможность
выполнять основные расчеты. Некоторые из них можно выполнить автоматически,
выбрав диапазон ячеек на рабочем листе:

ü
СРЕДНЕЕ — используется для того, чтобы
проанализировать выбранный диапазон ячеек и рассчитать среднее значение.

ü
КОЛИЧЕСТВО — используется для того, чтобы
подсчитать количество выбранных ячеек, содержащих значения, без учета пустых
ячеек.

ü
СУММА — используется для того, чтобы
сложить все числа в выбранном диапазоне без учета пустых или содержащих текст
ячеек.

Результаты этих расчетов отображаются в правом нижнем углу строки
состояния.

Рассмотрим применение встроенных функций в решении задач ЕГЭ.

Практическое задание

Задача № 7.
Поляков К.Ю. Режим доступа: http://kpolyakov.spb.ru/school/ege.htm

Р-10. Дан
фрагмент электронной таблицы. Из ячейки D2 в одну из ячеек диапазона E1:E4 была
скопирована формула. При копировании адреса ячеек в формуле автоматически изменились,
и значение формулы стало равным 8. В какую ячейку была скопирована формула? В
ответе укажите только одно число – номер строки, в которой расположена ячейка.

Решение:

1) 
в формуле, которая записана в ячейку D2,
две смешанных ссылки, в первой заблокирована строка 3, а во второй – столбец C

2) 
формула перемещается в столбец E (на 1
столбец вправо), поэтому в первой ссылке адрес столбца будет C, так что формула
примет вид:

=C$3 + $C?

где
вместо знака вопроса будет некоторый номер строки – той строки, в которую
скопируют формулу

3) 
значение ячейки C3 равно 5, для того,
чтобы получить в сумме 8, нужно добавить к нему число 3 – в столбце С оно
находится в ячейке C1; поэтому формулу нужно скопировать в первую строку (в
ячейку E1).

4)  ответ:
1.

Р-07. В ячейке E15 электронной таблицы
записана формула. Эту формулу скопировали в ячейки D17 и C18. В соответствии с
формулой, полученной в ячейке D17, значение в этой ячейке равно разности
значений в ячейках D32 и C32; в соответствии с формулой, полученной в ячейке
C18, значение в этой ячейке равно разности значений в ячейках D33 и B32.
Укажите, какая формула могла быть написана в ячейке E15.

1) =E$32-D$30      2)
=$D$32-$B$32      3) =$D$30-$C$32      4)
=$D30-D$32

Решение:

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

        
в E15  → ?

? 
→ в D17  → D32 – C32

? 
→ в C18  → D33 – B32

2)      
видим, что обе целевые ячейки, D17 и C18,
относятся к разным столбцам и разным строкам,  в то же время в обеих формулах в
первой ссылке – столбец D, а во второй – строка 32

3)      
следовательно, эти части ссылок
абсолютные, они заблокированы от изменений знаком $

4)      
номера строк в первой ссылке и имена
столбцов во второй – разные, они относительные

5)      
таким образом, получаем формулу =$D30 –
D$32

6)      
Ответ: 4.

Р-11. Формулу из ячейки B3 скопировали в две
ячейки в соседних строках одного столбца. После этого числовое значение в одной
из этих ячеек оказалось больше числового значения в другой на 2000. Укажите
адрес ячейки, в которой оказалось большее из этих значений.

Решение:

1) 
предположим, что формула из B3 была
скопирована в две ячейки с относительными адресами (dx,
dy)
и (dx, dy+1);
здесь dx означает смещение
верхней из двух ячеек по горизонтали (изменение «номера» столбца), а dy
– смещение по вертикали (изменение номера строки)

2) 
 тогда в верхней ячейке будет записана
формула =(С+dx)(dy+4)+(D+dx)$2,
а в нижней – =(С+dx)(dy+5)+(D+dx)$2;
например, при dx
= 1,  dy 
= –1 эти формулы принимаю вид

= D3 +
E$2 и =
D4
+
E$2

3) 
поскольку в этих формулах второе слагаемое
– это значение одной и той же ячейки, их разность равна разности двух соседних
ячеек столбца (C+dx),
в приведённом примере – разности D4
и D3

4) 
теперь остается найти в таблице две
соседние ячейки одного столбца, разница между значениями которых равна 2000, и
вычислить соответствующие значения dx
и dy

5) 
из приведённой таблицы видим, что этому
условию удовлетворяет единственная пара ячеек: E4 и E5 (выделены жёлтым фоном),
откуда следует, что C + dx = E (значит, dx
= 2) и dy
+ 4 = 4 (значит, dy
= 0)

6) 
при dx
= 2 и dy = 0 формула из B3 будет
скопирована в ячейки D3 и D4
(они выделены зеленым фоном), причём бо́льшее значение имеет ячейка D4.

7)  Ответ:
D4.

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

Для построения диаграммы в Excel
необходимо выполнить стандартную последовательность действий:

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

2)      
на
вкладке Вставка выбрать раздел Диаграмма;

3)      
выбрать
тип диаграммы, например Круговая, и щелкнуть ЛКМ:

Для изменения данных в построенной диаграмме
выполните последовательность действий:

1) 
на
построенной диаграмме щелкните ПКМ и выберите опцию: «Выбрать данные»;

2) 
в
открывшемся диалоге в поле «Диапазон данных для диаграммы» поставьте текстовый
курсор и ЛКМ проведите по диапазону данных, по которым строится диаграмма;

3) 
в
разделе «Подписи горизонтальной оси(категории)» щелкните на кнопку «Изменить» и
проведите по диапазону данных, соответствующему подписям легенды диаграммы (или
оси аргументов ОХ при построении графика);

4) 
при
необходимости можно изменить название диаграммы и добавить подписи (в
контекстном меню на диаграмме выбрать «Добавить подписи данных»:

2. Статистические и логические функции и их применение в
электронных таблицах. Сортировка данных, применение фильтров

Практическое задание

Рассмотрим задачу № 14 нового формата ОГЭ по
информатике.

Задача № 1477. Генератор заданий К.Ю. Полякова: http://kpolyakov.spb.ru/school/oge/generate.htm

В электронную таблицу занесли результаты тестирования учащихся по
различным предметам. На рисунке приведены первые строки получившейся таблицы.
Всего в электронную таблицу были занесены данные по 1000 учащимся.
Порядок записей в таблице произвольный.

Число 0 в таблице означает, что ученик не сдавал
соответствующий экзамен.

На основании данных, содержащихся в этой таблице, выполните задания.

1. Сколько учеников сдали экзамен по
математике на отметку 5 баллов, но получили средний балл по всем
сданным экзаменам ниже, чем 4 балла?

Ответ на этот вопрос запишите в ячейку H2
таблицы. Учтите, что ученики могли сдавать не все экзамены.

2. Каков средний балл учеников 4 класса
по математике?

Учтите, что некоторые ученики не сдавали этот
экзамен. Ответ с точностью до двух знаков после запятой запишите в ячейку H3
таблицы.

3. Постройте круговую диаграмму, отображающую
соотношение числа участников экзамена из 1, 5 и 9 классов. Левый верхний угол
диаграммы разместите вблизи ячейки G6.

Решение.

Поскольку речь в пункте 1 задания идёт о
среднем балле по всем экзаменам, создадим вспомогательный столбец «Средний
балл».
Учтём, что в таблице есть учащиеся, совсем не сдававшие экзаменов, или не
сдававшие 1 или 2 экзамена.

В ячейку G2 вводим формулу с условной функцией
(если учащийся не сдавал ни одного экзамена, не считаем среднее значение, а
сразу пишем 0, далее среднее значение считаем только для ненулевых ячеек:

=ЕСЛИ(И(D2=0;
E2=0;F2=0);0;СРЗНАЧЕСЛИ(D2:F2;»>0″))

Протягивая вниз мышью за маркер в правом нижнем углу, заполняем
формулами все необходимые ячейки данного столбца (до ячейки 1001 — поскольку
учащихся всего 1000, а первый записан в строку 2)

В ячейку H2 вводим формулу, вычисляющую ответ
на вопрос 1 пункта задания: «Сколько учеников сдали экзамен по математике на
отметку 5 баллов, но получили средний балл по всем сданным экзаменам
ниже, чем 4 балла?»

=СЧЁТЕСЛИМН(D2:D1001;5;G2:G1001;»<4″)

Функция СЧЁТЕСЛИМН применяет
критерии к ячейкам в нескольких диапазонах и вычисляет количество соответствий
всем критериям.

Синтаксис функции:

СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)

В ячейку Н3 вводим формулу, вычисляющую ответ
на вопрос 2 «Каков средний балл учеников 4 класса по математике?»:

=СРЗНАЧЕСЛИМН(D2:D1001;D2:D1001;»<>0″;C2:C1001;»=4″)

Выполняем третий пункт задания:

3. Постройте круговую диаграмму, отображающую
соотношение числа участников экзамена из 1, 5 и 9 классов. Левый верхний угол
диаграммы разместите вблизи ячейки G6.

Построим вспомогательную таблицу по заданным
классам.
Здесь удобнее использовать абсолютные ссылки:

=СЧЁТЕСЛИ($C$2:$C$1001; «=1»)

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

Сортировка данных, применение фильтров

Сортировка данных

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

·                    
По возрастанию используется для сортировки данных в порядке
возрастания — от A до Я по алфавиту или от наименьшего значения к наибольшему
для числовых данных.

·                    
По убыванию используется для сортировки данных в порядке
убывания — от Я до A по алфавиту или от наибольшего значения к наименьшему для
числовых данных.

Для сортировки данных:

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

2.                
щелкните по значку Сортировка
по возрастанию , расположенному на вкладке Главная верхней
панели инструментов, для сортировки данных в порядке возрастания, ИЛИ щелкните
по значку Сортировка по убыванию , расположенному на вкладке Главная
верхней панели инструментов, для сортировки данных в порядке убывания.

Фильтрация данных

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

Чтобы включить фильтр:

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

2.                
Щелкните по значку Фильтр
, расположенному на вкладке Главная верхней панели
инструментов.

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

Чтобы применить фильтр:

1.                
Нажмите на кнопку со
стрелкой . Откроется список
команд фильтра:

2.                
Настройте параметры
фильтра. Можно действовать одним из трех следующих способов: выбрать данные,
которые надо отображать, отфильтровать данные по определенным критериям или
отфильтровать данные по цвету.

Выбор данных, которые надо отображать

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

После того как вы выберете все нужные данные,
нажмите кнопку OK в списке команд фильтра, чтобы применить
фильтр.

Фильтрация данных по определенным критериям

В зависимости от данных, содержащихся в
выбранном столбце, в правой части окна фильтра можно выбрать команду Числовой
фильтр или Текстовый фильтр, а затем выбрать одну из опций в
подменю:

Для Числового фильтра доступны
следующие опции: Равно…, Не равно…, Больше…, Больше
или равно…, Меньше…, Меньше или равно…, Между, Первые
10, Выше среднего, Ниже среднего, Пользовательский….

Для Текстового фильтра доступны
следующие опции: Равно…, Не равно…, Начинается с…, Не
начинается с…, Оканчивается на…, Не оканчивается на…, Содержит…,
Не содержит…, Пользовательский….

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

Нажмите кнопку OK, чтобы применить
фильтр.

Сортировка отфильтрованных данных

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

ü  Сортировка
по возрастанию — позволяет сортировать данные в порядке возрастания,
отобразив в верхней части столбца наименьшее значение,

ü  Сортировка
по убыванию
— позволяет сортировать данные в порядке убывания, отобразив в верхней части
столбца наибольшее значение,

ü  Сортировка
по цвету ячеек — позволяет выбрать один из цветов и отобразить
записи с таким же цветом фона ячеек в верхней части столбца,

ü  Сортировка
по цвету шрифта — позволяет выбрать один из цветов и отобразить
записи с таким же цветом шрифта в верхней части столбца.

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

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

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

3. Решение задач
ЕГЭ и ОГЭ с использованием
электронных таблиц.

Практическая
работа

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

1) Решите задачу №1477 ОГЭ (смотрите условие выше) с
помощью фильтрации.

2) Решите задачи № 1473 – 1476, № 1463 — 1466 с сайта
Полякова К.Ю по ссылке: http://kpolyakov.spb.ru/school/oge/gen.php?action=viewAllEgeNo&egeId=215&cat139=on

3)  Решите задачи № 84-89 из методических указаний
«7-2. Анализ диаграмм в электронных таблицах» Полякова К.Ю. Задания скачать по
ссылке:

http://kpolyakov.spb.ru/download/ege7-2.doc

4)     
Решите
задачи № 87-93 из методических указаний «7-1. Адресация в электронных таблицах»
Полякова К.Ю. Задания скачать по ссылке: http://kpolyakov.spb.ru/download/ege7-1.doc

Вопросы для самоконтроля

1.                
В чем отличие абсолютного
и относительного адресов ячейки в MS Excel?

2.                
Какие основные типы данных
присутствуют в таблицах MS Excel?

3.                
Каковы общие правила ввода
формул в таблицах MS Excel?

4.                
Использование логической и
статистической функций для обработки информации.

5.                
Что такое фильтрация и как
ее можно использовать для решения задач ОГЭ?

6.                
Какие действия необходимо
выполнить, чтобы построить диаграмму для ответа на вопрос 3 в задании №14 ОГЭ
по информатике?

Список рекомендуемой литературы

Основная литература

1.       
Информатика. Учебник для 9 класса. И. Г. Семакин, Л. А. Залогова, С. В. Русаков, Л. В. Шестакова.-
М.: БИНОМ. Лаборатория знаний, 2014. – 125 с.

2.       
Информатика. Углубленный уровень: учебник для 11
класса: в 2 ч. Ч.1/ И.Г. Семакин, Т.Ю. Шеина, Л.В. Шестакова.- М.: БИНОМ.
Лаборатория знаний, 2014. – 184 с.

3.       
Информатика. Углубленный уровень: учебник для 11
класса: в 2 ч. Ч.2/ И.Г. Семакин, Т.Ю. Шеина, Л.В. Шестакова. – М.: БИНОМ.
Лаборатория знаний, 2014. – 232 с.

4.       
ЕГЭ 2015. Информатика. Типовые тестовые задания/В.Р.
Лещинер.- М. Издательство «Экзамен», 2015 -223

Дополнительная
литература

1.       
Богомолова О.Б. Проектные работы с
использованием электронных таблиц MS Excel – М.: БИНОМ. Лаборатория знаний,
2012 . – 246 с.

2.       
Богомолова О.Б. Работа в электронных
таблицах OpenOffice.org Calc: практикум – М.: БИНОМ. Лаборатория знаний, 2012 .
– 158 с.

3.       
Полат
Е.С. Новые педагогические и информационные технологии в системе образования. –
М: Академия, 2009 г. – 272 с.

4.       
  Лапчик
М.П.и др. Теория и методика обучения информатике.- М.: Издательский центр
«Академия», 2008.- 624 с.

5.       
Софронова Н.В. Теория и методика
обучения информатике: Учебное пособие, М.: «Высшая школа«, 2004
г. 223 с.

6.       
Лапчик
М.П., Рагулина М.И., Смолина Л.В. Теория и методика обучения информатике.
Лабораторный практикум: Учеб. Пособие для студентов вузов / Под ред. М.П.
Лапчика. Омск: Изд-во ОмГПУ, 2004. – 312 с.

7.       
Преподавание
курса «Информатика и ИКТ» в основной и старшей школе (7-11): Методическое
пособие для учителей. Угринович Н.Д. – М.: БИНОМ. Лаборатория знаний,
2006. 

Интернет-ресурсы

1.       
Дистанционная подготовка
по информатике. – Электронный ресурс. – Режим доступа: http://informatics.mccme.ru

2.       
ГБОУ ДПО РК КРИППО.
– Электронный ресурс. – Режим доступа: www.krippo.ru

3.       
Персональный сайт
учителя информатики К.Ю. Полякова. – Электронный ресурс. – Режим доступа: http://kpolyakov.spb.ru

4.       
Пособие по решению задания
14. А.А. Тузов. – Электронный
ресурс. – Режим доступа: http://kpolyakov.spb.ru/download/tank14.odp

5.       
http://informatics.mccme.ru -дистанционная подготовка по информатике;

6.       
https://cloud.mail.ru/public/9wRJ/UXmNFriTr — ссылка на облако с заготовками к
практическим заданиям

7.       
Алексеев Е.Г., Богатырев С.Д. Информатика.
Мультимедийный электронный учебник. – Электронный ресурс.
– Режим доступа: http://inf.e-alekseev.ru/text/toc.html

8.       
Авторская мастерская И.Г. Семакина.
– Электронный ресурс. –  Режим доступа: http://metodist.lbz.ru/authors/informatika/2/

9.       
Сборник дидактических материалов для
текущего контроля результатов обучения по информатике и ИКТ в основной школе
(авторы Овчинникова Г.Н., Перескокова О.И., Ромашкина Т.В., Семакин И.Г.).
– Электронный ресурс. – Режим
доступа: http://metodist.lbz.ru/authors/informatika/2/files/semakin_did.pdf

10.  
Вики — учебник
по табличному процессору Microsoft Excel.
– Электронный ресурс. – Режим доступа: https://ru.wikibooks.org/wiki/Microsoft_Excel

11.  
Планета Excel. – Электронный ресурс.
– Режим доступа: http://www.planetaexcel.ru

12.  
Информационно-коммуникационные технологии
в образовании. – Электронный ресурс. – Режим
доступа: http:
//ict.edu.ru/

Рассмотрено и одобрено

на заседании кафедры
естественно-математического образования

Протокол № ____ от «_____»  _____________ 20
___ г.

Заведующий кафедрой                                                         А.В. 
Супрычев