Введение

Microsoft Excel — одна из самых загадочных и интересных программ в пакете MS Office. Интересна она многочисленными средствами автоматизации работы, оформления документов и богатыми вычислительными возможностями. Загадочность ее состоит в том, что большинство пользователей применяют лишь малую толику того, что может дать им Excel. Это тем более удивительно, что спектр возможностей программы практически безграничен: от создания простых таблиц, построения диаграмм и графиков до решения сложных вычислительных задач и моделирования различных процессов.

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

В основные возможности электронных таблиц входит:

-       автоматизация всех итоговых вычислений;

-      над большими наборами данных можно производить однотипные расчёты;

-       можно разрешать задачи с помощью подбора значений с различными параметрами;

-       можно обрабатывать результаты экспериментов;

-       производить табулирование функций и формул;

-       подготавливать табличные документы;

-       проводить поиск наиболее оптимальных значений для выбранных параметров;

-       строить графики и диаграммы по уже введённым данным.

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

Можно использовать Excel:

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

• длясоставленияотчётов;

• для планирования и расчётов;

• в качестве тестовой оболочки для проверки знаний.

Выбранная тема на данный момент очень актуальна. Мы все знаем, что ЕГЭ по информатике кардинально изменилось.Задания представлены в электронном виде. Если на экзамене было запрещено пользоваться вычислительными средствами, то компьютерная форма сдачи ГИА дает возможность пользоваться такими программами, как электронная таблица, калькулятор, среды программирования.40 процентов заданий можно решить с помощью электронных таблиц – Microsoft Excel, OpenOffise.org Calc или др..

Цель моей работы: рассмотреть возможности табличного процессора на примерах решения задач ГИА.

Моя работа содержит материалы с сайта Полякова К.Ю. http://kpolyakov.spb.ru

I.Встроенныефункции

При обработке данных в электронных таблицах применяются встроенные функции–заранее определенные формулы. При выполнении табличных расчетов в заданиях ГИА достаточно часто используются функции:

СУММ(число1;число2;…)	Суммирование аргументов
МИН(число1;число2;…)–	Определение наименьшего значения из списка аргументов
МАКС(число1;число2;…)	Определение наименьшего значения из списка аргументов
СРЗНАЧ(число1;число2;…)–	Определение среднего (арифметического) своих аргументов
И(логическое_значение1;логическое_значение2;…)	Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотябы один аргумент имеет значение ЛОЖЬ. Аргументы функции - логические выражения, принимающие значения либо истина, либо ложь.
ИЛИ(логическое_значение1;логическое_значение2;…)	Возвращает значение ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ. Аргументы функции- логические выражения, принимающие значения либо истина, либо ложь.
НЕ(логическое_значение)	Функция НЕ используется в тех случаях, когда необходимо быть уверенным в том, что значение неравно некоторой конкретной величине. Аргумент функции- логическое выражение, принимающие значения либо истина, либо ложь.
ЕСЛИ (логическое_выражение; значение1;значение2	Используется прип роверке условий для значений и формул. Здесь логическое_выражение – любое выражение, построенное с помощью операций отношения и логических операций, принимающее значения ИСТИНА или ЛОЖЬ. Если логическое_выражение истинно, то ячейка, в которую записана условная функция, принимает значение1, если ложно - значение2.
СЧЁТЕСЛИ (диапазон; критерий)	Подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.
СУММЕСЛИ (диапазон; критерий; диапазон_суммирования)	Суммирует ячейки, заданные критерием.

II. ПримерзаданияОГЭ.

Обработка данных в электронных таблицах (с диаграммой)

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

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

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

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

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

Решение.

1) Для вычисления воспользуемся дополнительным столбцом G. В ячейку G2 вводим формулу и при этом мы должны учесть, что ученики могли сдавать не все экзамены («<>0»): =ЕСЛИ(И(D2=0;E2=0;F2=0);"";СУММ(D2:F2)/СЧЁТЕСЛИ(D2:F2;"<>0")), копируем ее (таким образом, мы рассчитываем средний балл для каждого ученика по всем сданным предметам). Затем в ячейку H2 вводим формулу для определения количества учащихся, получивших отметку 5 по математике и имеющих средний балл по всем сданным экзаменам ниже, чем 4 балла: =СЧЁТЕСЛИМН(D:D;"=5";G:G;"<4")

2) В ячейку H3 вводим формулу, для нахождения среднего балла учеников 4 класса по математике (при этом, также мы должны учесть, что некоторые ученики не сдавали этот экзамен: =СРЗНАЧЕСЛИМН(D:D;C:C;"=4";D:D;"<>0")

3) Для построения диаграммы необходимо определить количество участников экзамена из 1, 5 и 9 классов:

III. Функции двух аргументов. Таблицы значений

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

Пример. Создать традиционную таблицу квадратов двузначных чисел.

В ячейке В4 введена формула =($A4*10+B3)^2 и скопирована в остальные ячейки. Формула содержит смешанную адресацию.

Используя тот же принцип смешанной адресации, мы можем решить следующие задачи:

З адание 1.С помощью редактора электронных таблиц создайте таблицу вещественных значений выражения F(x,y)=2x³/(y+1) для следующих вещественных значений x и y: x = 5,5; 6,0; …; 8,5; y = 10,0; 10,3; …; 13,0. Вычислите сумму получившихся значений и запишите её целую часть в ответе.

Решение с помощью MSExcel:

Для вычислений функций F(x,y)=2x³/(y+1) вводим формулу в ячейку С2 =2*C$1^2/($B2+1) и копируем во все остальные ячейки. Затем в любой пустой ячейке вычислим сумму получившихся значений, используя автосумму ∑.

Задание 2. Создайте таблицу вещественных значений выражения для следующих вещественных значений x и y: x = 1,5; 1,8; …; 7,8; y = 5,4; 5,8; …; 9,8. Вычислите количество отрицательных чисел в этой таблице.

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

from math import sin,cos

f=0

x=1.5

kotr=0

while x<7.8:

y=5.4

while y<9.9:

f=(5*x*x+2*y*y)/(3*sin(x)+cos(y))

if f<0:

kotr+=1

y=y+0.4

x=x+0.3

print(kotr)

IV. Примеры заданий ЕГЭ

Егэ 9.Тема: Встроенные функции в электронных таблицах

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

Решение.

Функция МАКС возвращает наибольшее значение из набора значений.

Функция СРЗНАЧ возвращает среднее значение (среднее арифметическое) аргументов. Например, если диапазон (Диапазон. Две или более ячеек листа. Ячейки диапазона могут быть как смежными, так и несмежными.)A1:A20содержит числа, формула =СРЗНАЧ(A1:A20) возвращает среднее значение этих чисел.

• Среднее значение — это среднее арифметическое, которое вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.

При решении данной задачи вычисления нужно производить в пустой ячейке. Формула для вычисления предельна проста:

=МАКС(B63:Y92)-СРЗНАЧ(B63:Y92)

Если необходимо округлить значение до целой части, можно применить округление вниз с помощью встроенной функции ОКРУГЛВНИЗ:

=ОКРУГЛВНИЗ(МАКС(B63:Y92)-СРЗНАЧ(B63:Y92);0)

Второй аргумент 0 при вызове функции означает«округление до 0 знаков в дробной части»–то есть до ближайшего целого числа «вниз», к нулю (это равносильно отбрасыванию дробной части числа)

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

Решение.

Заметим, что треугольник является прямоугольным, если квадрат длины гипотенузы треугольника будет равен сумме квадратов длин катетов этого треугольника. Тогда в ячейке D1 запишем формулу =ЕСЛИ(ИЛИ(A2^2=B2^2+C2^2;B2^2=A2^2+C2^2;C2^2=A2^2+B2^2);1;0) и скопируем её во все ячейки диапазона D2:D5000. ... Таким образом, проверим по теореме Пифагора квадрат длины гипотенузы и сумму квадратов катетов для каждой тройки чисел. Теперь, воспользовавшись формулой =СУММ(D1:D5000), получим ответ — 56. 

Пример 3.В файле электронной таблицы 9-162.xls в каждой строке содержатся четыре натуральных числа. Определите количество строк таблицы, содержащих числа, для которых выполнены оба условия:

– разность максимального и минимального чисел не менее 50;

– произведение двух других чисел не более 1000.

Решение.

Для начало необходимо отсортировать числа в каждой строке, например по возрастанию, применив функцию НАИМЕНЬШИЙ () . Для этого в ячейки E1:H1 введем последовательно формулы: =НАИМЕНЬШИЙ(A1:D1;1); =НАИМЕНЬШИЙ(A1:D1,2); =НАИМЕНЬШИЙ(A1:D1;3); =НАИМЕНЬШИЙ(A1:D1;4). Далее мы копируем её во все ячейки диапазона E1:H3000. В ячейку I1 введем формулу =ЕСЛИ(И(H1-E1>=50;F1*G1<=1000);1;0) и посчитаем сумму всех единиц (=СУММ(I1:I3000)). Ответ: 423

Программа на языке Python:

Для начала мы создадим файл txtcданными. Открыть файл, скопировать значения ячеек таблицы A:D и вставить в текстовый документ Блокнот. Заменить знак Табуляции на Пробел и сохранить.

f=open('9.txt').readlines()

k=0

for x in f:

m=[int(a) for a in x.split()]

m.sort()

if m[3]-m[0]>=50 and m[1]*m[2]<=1000:

k+=1

print(k)

Егэ 18. Тема: Динамическое программирование

В задачах, которые предлагаются в этом задании КИМ, нужно найти оптимальный путь для Робота, который перемещается на клетчатом поле. Робот может на каждом шаге выбирать одно из двух направлений движения (например, только вправо и вниз).

Пример. Исходные данные записаны в файле 18-0.xls в виде электронной таблицы прямоугольной формы. Определите максимальную и минимальную денежную сумму, которую может собрать Робот, пройдя из левой НИЖНЕЙ клетки в правую ВЕРХНЮЮ. В ответе укажите два числа – сначала максимальную сумму, затем минимальную.

Решение.

Откроем файл с электронной таблицей размером N на N:

Робот начинает движение из левого нижнего угла (из ячейки А10) и перемещается в правый верхний (то есть в J1)

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

• скопировать исходную таблицу вниз

• обвести её рамкой (и/или выделить фоном), чтобы запомнить исходный размер;

• стереть все данные в копии.

вот что должно получиться:

2. дальше мы будем работать только с областью, выделенной жёлтым фоном

3. предположим, что робот уже находится в левом нижнем углу; в этом случае он может получить только сумму в этой ячейке, то есть величину А10.

4. При смещении вправо робот забирает монеты в очередной ячейке и складывает со значением, которую он взял ранее. Для этого мы вводим в ячейку В22 вводим формулу =A22+B10 и копируем ее во все ячейки диапазона C22:J22. При смещении вверх в ячейку А21 вводим формулу =A22+А9.

5. Что касается центральных ячеек, то робот выбирает путь, который ему принесет максимальную сумму, для этого мы введем формулу в ячейку В21: =макс(А21;В22)+В9 и протягиваем на все оставшиеся ячейки. Полученное значение в ячейке J13 является максимальной суммой, которую может получить робот.

6. Для получения минимальной суммы, необходимо провести замену функции МАКС на МИН.

7. ЕГЭ 23.Динамическое программирование

Исполнитель Июнь17 преобразует число на экране. У исполнителя есть три команды, которым присвоены номера:

1. Прибавить 1

2. Умножить на 2

3. Умножить на 3

Сколько существует программ, для которых при исходном числе 2 результатом является число 30 и при этом траектория вычислений содержит число 15?

Решение. Т.к. траектория вычислений содержит число 15, то наше решение будет состоять из двух таблиц. Первая таблица – находим количество программ из числа 2  15, а другая из 15  30

В первой строке пишутся числа от 2 до 15 (до того числа, которое нужно получить).

Во второй строке пишутся числа, которые в сумме с 1 (единицей) дают числа, написанные в первой строке.

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

В четвёртой строке пишутся числа, которые при умножении на 3 дают числа, написанные в первой строке.
В пятой строке для двойки ставим 1. Для остальных ячеек: смотрим, какие числа участвуют во второй, третьей и четвертой строке для конкретной ячейки. Затем, эти числа ищем в первой строке и пишем сумму количеств программ для этих чисел (Т.е. пишем сумму уже известных значений из пятой строки для этих чисел).

Аналогично, мы пропишем все действия во второй таблице для получения из числа 15 число 30, при этом в пятой строке для 15 ставим количество 21, найденное в первой строке

Таким образом, основная идея 23 задания из ЕГЭ по информатике заключается в том, что результат каждого шага опирается на результаты предыдущих шагов!

ЕГЭ 22. Выполнение параллельных процессов

22. задание подобного типа появилось только в этом 2022 году.

Пример. В файле 22.xls содержится информация о совокупности N вычислительных процессов, которые могут выполняться параллельно или последовательно. Будем говорить, что процесс B зависит от процесса A, если для выполнения процесса B требуется, чтобы был выполнен хотя бы один из процессов А. Информация о процессах представлена в файле в виде таблицы. В первой строке таблицы указан идентификатор процесса (ID), во второй строке таблицы – время его выполнения в миллисекундах, в третьей строке перечислены с разделителем «;» ID процессов, от которых зависит данный процесс. Если процесс является независимым, то в таблице указано значение 0. Определите минимальное время, через которое завершится выполнение всей совокупности процессов, при условии, что все независимые друг от друга процессы могут выполняться параллельно.

Типовой пример организации данных в файле:

В данном случае независимые процессы 1 и 2 могут выполняться параллельно, при этом процесс 1 завершится через 4 мс, а процесс 2 – через 3 мс с момента старта. Процесс 3 может начаться только после завершения процесса 1 или 2, то есть, через 3 мс после старта. Он длится 1 мс и закончится через 3 + 1 = 4 мс после старта. Выполнение процесса 4 может начаться только после завершения процесса 3, то есть, через 4 мс. Он длится 7 мс, так что минимальное время завершения всех процессов равно 4 + 7 = 11 мс.

Решение.

1 способ (ручной перебор) . У нас есть различные процессы. Для каждого процесса известно время его выполнения (столбец B), а так же от каких процессов он зависит (столбец С). Общее время выполнения процесса вычисляется следующим образом: процесс должен дождаться самый долгий процесс от которого он зависит, а потом выполнится сам.

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

Т.к. процессы 2, 3, 10, 11 параллельные, т.е. не зависят от других процессов, то

D2= B2

D3=B3

D10=B10

D11=B11

Остальные процессы вычисляются по формулам:

- если выполнение процесса зависит от времени завершения нескольких процесса

- если от одного

Таким образом, мы получаем:

 

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

Ответ: 19

2 способ (с помощью встроенных функций)

 

Формулу копируем во все ячейки I2:I13

Формулу копируем во все ячейки F2:H13

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

Важно помнить, что когда мы берём время процесса от которого зависит какой-то процесс, то мы должны взять это время из столбца D, т.е. уже окончательное время выполнения процесса.

ЕГЭ 17. Перебор целых чисел на заданном отрезке. Проверка делимости

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

Пример. (демо-2021). Рассматривается множество целых чисел, принадлежащих числовому отрезку [1016; 7937], которые делятся на 3 и не делятся на 7, 17, 19, 27. Найдите количество таких чисел и максимальное из них. В ответе запишите два целых числа: сначала количество, затем максимальное число. Для выполнения этого задания можно написать программу или воспользоваться редактором электронных таблиц.

Программа на языке Python:

kol = 0

max = 0

for x in range(1016, 7937+1):

if (x%3 == 0):

if(x % 7 != 0) and (x % 17 != 0) and (x % 19 != 0)\

and (x % 27 != 0):

max = x

kol += 1

print(kol, max)

ЕГЭ 16.Рекурсивные алгоритмы

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

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

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

Пример.

1. Алгоритм вычисления функции F(n) задан следующими соотношениями:

F(n) = 1 при n = 1

F(n) = 2·F(n–1) + n + 3, если n> 1

Чему равно значение функции F(19)? .

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

Егэ 14.Позиционные системы счисления

Значение арифметического выражения: 9⁷ – 3¹² + 3¹⁰– 19 записали в системе счисления с основанием 3. Сколько цифр «2» содержится в этой записи?

Решение с помощью ЭТ:

Выполним перевод в 3-ую СС:

• в ячейке B2 найдем остаток от деления числа (ячейка А2) на 3, а в А3 – частное.

• скопируем формулы из В2 и А3 вниз до того момента, когда частное станет равно 0 (это означает окончание процесса перевода)

• в ячейке С2 подсчитаем в столбце В число остатков, равных 2

Ответ: 3

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

Обычно электронные таблицы при этом переходят к экспоненциальному (научному) формату. Если число больше, чем 10¹⁵, то оно хранится как вещественное число (неточно). Это ограничивает использование электронных таблиц. В этой задаче заданное число меньше, чем 10¹⁵, поэтому использовать электронные таблицы можно.

Решение с помощью Python:

a=9**7-3**12+3**10-19

k=0

while a > 0:

if a%3==2:

k+=1

a = a//3

print(k)

V. Заключение

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

MS Excel предоставляет большие возможности, вплоть до решения самых сложных заданий ЕГЭ (2 часть, задания 26,27)

VI. Список литературы

1. А. Левин. «Excel - это очень просто!», Издательство: Питер, 2014 г.

2. Сайтhttps://kpolyakov.spb.ru

3. Сайт https://smartiqa.ru

4. Г.Г.Арунянц, К.Х.Пагиева. «Информационные технологии в экономике», ОЛИМП, Владикавказ, 2001

5. Д. М.Златопольский, «Основы программирования на языке Python», - М.: ДМК Пресс, 2017. - 277 c