Упражнения

В каждом задании на проверку необходимо сдать документ, подготовленный в среде электронных таблиц: Microsoft Excel или LibreOffice Calc. При этом файл необходимо сдавать в формате Microsoft Excel 2003 или более новый (расширение .xlsx), даже если вы работаете в Libre Office Calc необходимо при сохранении указать формат Microsoft Excel.

A: Квадраты

Заполните ячейки A1:A100 числами от 1 до 100, а ячейки B1:B100 — их квадратами. Образец.

B: Таблица умножения

Создайте таблицу умножения всех чисел от 1 до 25. Перемножаемые числа записаны в столбце 1 и строке A. Для заполнения таблицы используйте одну формулу с абсолютными и относительными ссылками, раскопировав ее по всему блоку B2:Z26. Образец.

C: График кубического многочлена

Постройте график функции \(y=ax^3+bx^2+cx+d\) с возможностью изменения коэффициентов \(a\), \(b\), \(c\), \(d\). Образец.

Используйте файл с шаблоном таблицы.

Значения коэффициентов записаны в ячейках B1:B4. В ячейках A7:A67 запишите значения \(x\) от -3 до +3 с шагом 0,1. В ячейках B7:B67 запишите значения функции \(f(x)=ax^3+bx^2+cx+d\), используя значения коэффициентов из ячеек B1:B4.

Затем выделите блок A6:B67 и постройте по этому блоку диаграмму (меню Вставка — Диаграмма).

Выберите тип диаграммы “Линии — Только линии”. На диаграмме должны отображаться только значения столбца B, а значения из столбца A должны быть подписями по оси OX. Для этого на шаге “2. Диапазон данных” нужно выбрать галочку “ подписи в первом столбце”.

Для того, чтобы в подписях по оси OX отображалась ровно 1 цифра после запятой, нужно выделить блок чисел и в меню “Формат — Ячейки” установить один символ отображения дробной части.

Сдайте на проверку файл с построенным графиком для значений \(a=1\), \(b=-3\), \(c=-2\), \(d=5\). При изменении значений параметров должен изменяться вид графика.

D: Графики степенной функции

На одной диаграмме постройте графики функции \(y=x^n\) для \(n=0.5, 1, 1.5, 2, 3\). Образец.

Используйте файл с шаблоном таблицы.

Блок A2:A32 заполните значениями \(x\) от 0 до 3 с шагом \(0.1\). В блоке B2:F32 запишите значение функции \(x^n\), где значение \(x\) берётся из столбца A, а значение \(n\) из строки 1. Значения в блоке B2:F32 должны быть получены единой формулой, скопированной по всем ячейкам блока.

Для вычисления значения степени \(a^b\) используется функция POWER(a; b) (русское название — СТЕПЕНЬ) или бинарный оператор “^”.

Сдайте на проверку файл с построенным графиком для значений \(n=0.5, 1, 1.5, 2, 3\). На одной диаграмме должны быть все пять графиков. При изменении значения \(n\) должен изменяться вид графика.

E: Квадратные уравнения

Скачайте файл с шаблоном таблицы.

В каждой строке этого файла в столбцах A, B, C записаны коэффициенты квадратного уравнения \(ax^2+bx+c=0\). Запишите в столбце E значение меньшего корня этого уравнения, в столбце F  значение большего корня. У каждого уравнения есть два корня.

F: Квадратные уравнения — 2

Скачайте файл с шаблоном таблицы.

Теперь у уравнения может быть 0, 1 или 2 корня. Запишите в столбец E значение меньшего корня, в столбеw F — большего корня. Если корни совпали, то в столбце F должно быть пустое значение. Если корней нет, то в столбце E также должно быть пустое значение.

Вы должны использовать одну формулу для столбца E и одну формулу для столбца F, скопированные по всему столбцу. Какие-либо ручные правки значений в столбцах E и F не допускаются.

G: Треугольные числа

Заполните блок A1:A100 числами от 1 до 100. В блок B1:B100 запишите треугольные числа, используя функцию SUM(СУММ), которая считает сумму чисел в блоке (в нескольких блоках, если ей передать несколько аргументов). \(n\)-е треугольное число есть сумма \(1+2+...+n\). В блок C1:C100 запишите тетраэдрические числа — число точек в тетраэдре со стороной \(n\). \(n\)-е тетраэдрическое число равно сумме первых \(n\) треугольных чисел. Наконец, в блоке D1:D100 запишите четырёхмерные тетраэдрические числа (сумма первых \(n\) тетраэдрических чисел).

Образец.

Все значения в блоке B1:D100 должны быть получены при помощи единой формулы.

H: Магазин

Функция SUMPRODUCT(СУММПРОИЗВ) перемножает элементы двух или трёх блоков, а потом суммирует результат. А именно, если X и Y — два прямоугольных блока одинакового размера, то функция возвращает сумму \(\sum x_iy_i\), где \(x_i\) и \(y_i\) — одинаково расположенные элементы блоков X и Y. Если вызвать функцию от трёх блоков SUMPRODUCT(X; Y; Z), то фукнкция вернёт \(\sum x_iy_iz_i\), то есть суммму произведениё трёх чисел, которые берутся из блоков X, Y, Z и т.д.

Скачайте файл с таблицей, содержащей сведения о поступлении товаров в магазин (закупочная цена товара, объём закупки товаром магазина) и продаже товаров (цена продажи товаров магазином, объём продаж).

Вычислите следующие значения:

H2 — выручка магазина (сумма, полученная от продажи товаров).

H3 — доход магазина (выручка за вычетом средст, потраченных на закупку товара. Учитывается только реализованный товар).

H4 — стоимость склада (цена товара, не реализованного магазином, учтённая по закупочной цене).

I: Суммы последовательностей

Посчитайте следующие суммы, и проверьте, что они равны... \[1^2+2^2+...+n^2=\frac{1}{6}n(n+1)(2n+1)\] \[1^3+2^3+...+n^3=(1+2+...+n)^2\] \[1\cdot 2 + 2\cdot 3 + ... + (n-1)n = \frac{1}{3}(n-1)n(n + 1)\] Образец выполнения задания.

Блок A2:A101 заполните числами от 1 до 100. Блок B2:B101 заполните суммами квадратов. В блоке C2:C101 запишите формулы, проверяющую, что эта сумма равна \(\frac{1}{6}n(n+1)(2n+1)\). Блок D2:D101 заполните суммами кубов, блок E2:E101 заполните проверяющими формулами. Наконец, третью сумму запишите в блок F3:F101, и в блок G3:G101 запишите проверяющие формулы.

Для вычисления значений в столбцах B, D, F используйте функцию SUMPRODUCT

J: Числа Фибоначчи

Последовательность Фибоначчи определяется следующим образом: \(\varphi_0=0, \varphi_1=1, \varphi_{n}=\varphi_{n-1}+\varphi_{n-2}\).

Выполните задания:

  1. Вычислите все элементы последовательности Фибоначчи до \(\varphi_{60}\).
  2. Для каждого из \(n\) от 0 до 60 вычислите сумму начала ряда Фибоначчи \(\varphi_{0}+\varphi_{1}+...+\varphi_{n}\). Проверьте, что \(\varphi_{0}+\varphi_{1}+...+\varphi_{n}=\varphi_{n+2}-1\).
  3. Вычислите сумму квадратов начала ряда Фибоначчи: \(\varphi_{0}^2+\varphi_{1}^2+...+\varphi_{n}^2\). Проверьте, что \(\varphi_{0}^2+\varphi_{1}^2+...+\varphi_{n}^2=\varphi_{n}\times\varphi_{n+1}\).
  4. Проверьте, что \(\varphi_{n-1}\times\varphi_{n+1}-\varphi_{n}^2=(-1)^n\).
  5. Проверьте, что \(\varphi_{2n}=\varphi_{n+1}^2-\varphi_{n-1}^2\).

Проверка гипотез из пунктов 2-5 означает, что нужно составить некоторую логическую формулу и убедиться в том, что ее значение истинно для всех вычисленных чисел Фибоначчи. Формула должна быть выписана в отдельном столбце. Столбец должен быть подписан.

Какие-либо иные требования к размещению данных в таблице отсутствуют.

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

K: Задача на остатки

Найдите пять последних цифр числа \(179^{2020}\).

Для вычисления остатка от деления A на B вам поможет функция MOD (ОСТАТ). MOD(A; B) возвращает остаток от деления A на B. Для нахождения целочисленного частного можно использовать функцию QUOTIENT (ЧАСТНОЕ).

Задание оформите в произвольном виде так, чтобы было понятен ход решения. Ответ должен быть записан в ячейке A1.

L: Первообразные корни

Рассмотрим мультипликативную группу кольца вычетов по модулю \(n\). Пусть \(a\) — какое-то число от 1 до \(n-1\). Рассмотрим значения \(a^1\), \(a^2\), \(a^3\) (всё по модулю \(n\)) и т.д. Если возводя число \(a\) в различные степени можно получить все взаимно простые с \(n\) числа, то число \(a\) называется первообразным (или примитивным) корнем.

Первообразные корни существуют для всех \(n=p^k\) или \(n=2p^k\), где \(p\) — простое. Давайте рассмотрим \(n=179\), тогда \(n\) — простое. Рассмотрим все числа \(a=1, 2, ..., 178\).

По малой теореме Ферма \(a^{n-1}\equiv 1\). Между тем если \(a\) — первообразный корень, то \(a^i\not\equiv 1\) для всех \(i\lt n-1\).

Для каждого числа \(a\) постройте его степени \(a^i\) для \(i\lt n - 1\). Проверьте, что среди них нет числа 1. Для этого используйте функцию COUNTIF(A; val) (СЧЕТЕСЛИ), которая возвращает количество значений, равных val, в блоке A. Для каждого числа определите, является ли оно первообразным корнем. Также посчитайте общее число первообразных корней в отдельной ячейке.

Возможный образец части файла с выполненным заданием.

M: Погода — 1

Скачайте файл с таблицей, содержащей сведения о погоде в 1995 году. Используя функции IF запишите в столбцы следующие данные:

  1. В столбец F запишите слово "Тепло", если среднесуточная температура в этот день выше нуля, иначе запишите слово "Холодно".
  2. В столбец G запишите слово "Осадки", если уровень осадков в этот день выше нуля, иначе запишите слово "Сухо".
  3. В столбец H запишите слово "Потепление", если среднесуточная температура в этот день больше, чем в предыдущий, иначе запишите слово "Похолодание".
  4. В столбец I запишите слово, характеризующее колебание температуры в этот день: слово "Большое", если температура в течение этого дня изменялась более, чем на 3 градуса, иначе напишите слово "Маленькое".
  5. В столбец J запишите слово "Сухо" для дней без осадков, слово "Дождь" для дней с осадками и средней температурой выше нуля, слово "Снег" в остальных случаях.
  6. В столбец K запишите слово "Сухо" для дней без осадков. Для дней с осадками нужно написать слово "Дождь", если температура в течение всего дня была выше нуля, слово "Снег", если в течение всего дня температура была ниже нуля, строку "Снег с дождем" во всех остальных случаях.

Возможный образец части файла с выполненным заданием.

N: Погода — 2

Скачайте файл с таблицей.

Используя функции MIN, MAX, COUNT, SUM, AVERAGE, COUNTIF, SUMIF, AVERAGEIF, COUNTIFS, SUMIFS, AVERAGEIFS, MINIFS, MAXIFS а также при необходимости проводя вычисления в дополнительных столбцах запишите следующие значения:

  1. В ячейку E369 — наименьшую температуру в году.
  2. В ячейку E370 — наибольшую температуру в году.
  3. В ячейку E371 — среднегодовую температуру.
  4. В ячейку E372 — общее количество осадков за год.
  5. В ячейку E373 — среднесуточное количество осадков.
  6. В ячейку E374 — максимальное количество осадков за сутки.
  7. В ячейку E375 — максимальное изменение температуры в течение одних суток.
  8. В ячейку E376 — количество дней со среднесуточной температурой выше нуля.
  9. В ячейку E377 — максимальное число осадков, выпавших в течение трех дней, идущих подряд.
  10. В ячейку E378 — количество дней с осадками.
  11. В ячейку E379 — максимальное число подряд идущих дней с осадками.
  12. В ячейку E380 — количество дней с потеплением (то есть количество дней, среднесуточная температура для которых была выше чем среднесуточная температура в предыдущий день).
  13. В ячейку E381 — максимальное число подряд идущих дней с потеплением.

O: Оценки — 1

Скачайте файл с таблицей.

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

Посчитайте количество учащихся, которые:

  1. Учатся в 9 классе. Ответ запишите в ячейку C1311.
  2. Имеют оценку 4 или 5 по информатике. Ответ запишите в ячейку C1312.
  3. Имеют оценку 5 по математике или информатике. Ответ запишите в ячейку C1313.
  4. Имеют оценки 5 по всем экзаменам. Ответ запишите в ячейку C1314.
  5. Имеют хотя бы одну отличную оценку. Ответ запишите в ячейку C1315.
  6. Имеют все оценки, не ниже 4. Ответ запишите в ячейку C1316.
  7. Имеют хотя бы одну оценку, не ниже 4. Ответ запишите в ячейку C1317.
  8. Имеют средний балл за три экзамена не меньше 4. Ответ запишите в ячейку C1318.
  9. Не имеют двоек. Ответ запишите в ячейку C1319.
  10. Имеют не более одной двойки. Ответ запишите в ячейку C1320.
  11. Имеют все оценки не ниже 4 и хотя бы одну 5. Ответ запишите в ячейку C1321.
  12. Имеют не меньше двух пятерок. Ответ запишите в ячейку C1322.
  13. Имеют не меньше двух четверок или пятерок и третью оценку - не ниже тройки. Ответ запишите в ячейку C1323.

Возможный образец части файла с выполненным заданием.

P: Оценки — 2

Скачайте файл с таблицей.

  1. Подсчитайте средний балл по русскому языку для всех учащихся. Ответ запишите в ячейку C1311.
  2. Посчитайте средний балл по математике для тех учащихся, которые имеют удовлетворительную оценку по математике. Ответ запишите в ячейку С1312.
  3. Посчитайте средний балл по информатике для учащихся 9 класса. Ответ запишите в ячейку С1313.
  4. Подсчитайте средний балл по информатике для тех учащихся, которые имеют оценку по математике выше, чем по русскому языку. Ответ запишите в ячейку С1314.
  5. Подсчитайте средний балл по русскому языку для всех школьников, не имеющих двоек по всем предметам. Ответ запишите в ячейку С1315.
  6. Подсчитайте средний балл по математике для учащихся 10 класса, которые не имеют двоек по всем предметам. Ответ запишите в ячейку С1316.

Q: Количество участников регионального этапа

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

Скачайте файл с таблицей.

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

Ответ запишите в ячейку A1 первого листа (answer). Этот лист должен остаться первым. В этой ячейке должна быть записана какая-либо формула, возможно, просто ссылка на ячейку другого листа, но просто ввести руками число в эту ячейку нельзя.

Подумайте, алгоритм какой сложности у вас получился: \(O(n^2)\) или \(O(n\log n)\).

R: Список школ

Скачайте файл с таблицей.

Составьте список всех школ Москвы (вернее, логинов тех школ, из которых был хотя бы один участник регионального этапа). Список должен быть упорядочен по логину школы. Список запишите на лист answer в столбец A начиная со строки 2. В примере уже написаны первые пять строк правильного ответа.

S: Количество победителей и призёров по школам

Скачайте файл с таблицей.

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

Упорядочите список по убыванию сумма победителей и призёров, при равенстве — по убыванию числа победителей, при равенстве — по возрастанию логина школы.

В примере уже написаны первые пять строк правильного ответа.

T: Количество победителей и призёров по школам и предметам

Скачайте файл с таблицей.

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

В ячейки A2:A173 необходимо скопировать список логинов школ из предыдущего задания, сохранив порядок, как в предыдущем задании. Ограничимся школами, в которых не менее 10 победителей и призёров регионального этапа, таких школ 172. Сохраните порядок сортировки школ, как в предыдущем задании.

В ячейки B1:Z1 скопируйте названия предметов, взятых с листа data, в лексикографическом порядке. Возможно, вам в этом поможет специальная вставка, в которой есть параметр “Транспонировать”.

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

В файле с примером уже заполнены значениями первые строки и столбцы.

U: Учащиеся 179 школы

Скачайте файл с таблицей.

Создайте список учащихся 179 школы, упомянутых на листе data (логин школы sch778179), скопируйте его на первый лист таблицы. Для каждого учащегося укажите следующую информацию: логин учащегося (столбец A), фамилия (столбец B), класс обучения (C), количество побед на региональном этапе (D), количество призёрств на региональном этапе (E).

Упорядочите список по убыванию числа побед, при равных значениях — по убыванию числа призёрств, при равных значениях — по логину.

V: Результаты учащихся 179 школы на олимпиаде по информатике

Скачайте файл с таблицей.

Возьмите список учащихся 179 школы (столбцы A, B, C) из предыдущего задания и упорядочите его по логину учащегося. В столбце E напишите результат учащегося на олимпиаде по информатике (одно из слов участник, победитель, призёр).

Для поиска результата используйте функцию VLOOKUP. Если учащийся не участвовал в олимпиаде, то в ячейке будет значение #N/A, означающее ошибку. Оставьте эти значения как есть.

W: Результаты учащихся 179 школы по нескольким предметам

Скачайте файл с таблицей.

Для каждого учащегося (данные необходимо взять из предыдущего задания, сохранив порядок сортировки) определите его результат по предметам Астрономия, Биология, Информатика, Математика, Русский язык, Физика, Экономика.

Все значения должны вычисляться копией одной формулы. Указание: на листе data вычислите конкатенацию логина учащегося и предмета, используйте функцию VLOOKUP для того, чтобы найти запись, соответствующую конкатенации логина учащегося и предмета.

Вместо значений #N/A необходимо оставить пустые строки. Для этого есть функции, позволяющие проверять наличие ошибок и обрабатывать их. Пример такой функции: IFNA(выражение1; выражение2). Эта функция вычисляет выражение1 и возвращает его значение, а если результат вычисления будет ошибкой типа #N/A, то функция возвращает выражение2.