Задачи этого листка посвящены электронным таблицам.
Для их решения требуется либо Microsoft Excel, либо Libre office Calc.
Если будете использовать одну из последних версий MS Excel, то после старта выбирайте «Пустая книга».
Все задачи в этом листке необходимо сдавать в тестирующую систему в формате xls (MS Excel 2003) или xlsx (MS Excel старше).
Рекомендуется использовать xlsx.
В процессе решения задачи разрешается любым образом использовать любые ячейки ниже или правее каждой ячейки, участвующей в условии.
Во всех решениях должны присутствовать используемые формулы, сдача только ответов не допускается.
Микро-ликбез по интерфейсу
Теория и упражнения
A: Квадраты
Для того, чтобы записать в ячейку число, необходимо кликнуть на ячейку и ввести необходимое число.
Это очень просто.
В электронных таблицах очень легко получить набор ячеек, заполненных в соответствии с арифметической прогрессией.
Для этого заполните две соседние ячейки, выделите их,
нажмите на маленький чёрный квадратик в правом нижнем углу рамки выделения и тяните в нужную сторону.
Если в ячейке A1 стоит число 1, а в ячейке B1 стоит число 2,
то таким образом можно получить последовательных набор натуральных чисел.
Кроме чисел в ячейку можно записать формулу.
В простейшем случае это просто арифметическая формула.
Например, для того, чтобы записать в ячейку B1 квадрат значения ячейки A1,
то нужно так и написать: =A1*A1.
Если после ввода символа = кликнуть в любую ячейку, то в формулу будет вставлена ссылка на данную ячейку.
Если ячейку с формулой скопировать в буфер обмена, а потом вставить в другую ячейку,
то по умолчанию будет скопировано не значение ячейки, а формула.
При этом хотя на экране отображается явная координата ячейки (то есть A1),
в реальности хранится сдвиг от текущей ячейки (то есть RC[-1][0], левый сосед).
Поэтому при копировании формулы =A1*A1 из ячейки B1 в ячейку B2,
будет получена формула =A2*A2
Подобно продолжению арифметической прогрессии возможно «растягивание» формул:
если выделить ячейку A1,
нажать на маленький чёрный квадратик в правом нижнем углу рамки выделения и потянуть вниз,
то эти ячейки будут заполнены формулами в соответствии с правилами выше.
Кроме того, если дважды кликнуть на маленький чёрный квадратик в правом нижнем углу рамки выделения,
то формула будет проставлена во все ячейки текущего столбца,
для которых ячейки слева заполнены (вернее все ячейки до первой дырки).
Создайте таблицу, в которой в ячейках A1-A100 стоят числа от 1 до 100,
а в ячейках B1-B100 стоят их квадраты.
A
B
1
1
2
4
3
9
...
...
B: Квадраты, кубы, ..., десятые степени
Кроме ссылки на ячейку вида A1, можно использовать ссылки $A1, A$1 и $A$1.
Добавление знака $ перед координатой означает, что ссылка не относительная, а абсолютная.
Координата с долларом при копировании или «растягивании» не меняется.
Если в формуле курсор стоит на ссылке, то нажатие клавиши F4 будет циклически переключать описанные выше ссылки.
Создайте таблицу, в которой в ячейках A1-A50 стоят числа от 1 до 50,
в ячейках B1-B50 стоят их квадраты,
в ячейках C1-C50 — кубы,
и так далее до десятых степеней.
Если содержимое ячейки не может быть показано на экране, так как ширина столбца слишком мала,
то будет показано значение ###..##.
Для того, чтобы увеличить ширину столбца, необходимо зажать левой кнопкой мыши и подвинуть правую границу клетки в
шапке таблицы (там, где стоят имена столбцов, A, B, C, ...).
Двойной клик по правой границе приведёт к автоматическому выбору ширины столбца в соответствии с содержанием таблицы.
По умолчанию большие числа записываются в экспоненциальной записи: 1,18588E+11.
Для того, чтобы показать все цифры числа, необходимо выделить нужные ячейки,
нажать правую кнопку мыши и зайти в настройки формата ячеек.
Там нужно выбрать числовой формат и указать число десятичных знаков: 0.
При этом у числа хранится только порядка 15 значащих цифр, поэтому 3810 будет записано как
6278211847988220, а не как 6278211847988224.
Поэтому реальная таблица будет не идеально точной.
Для Excel максимальное число, которое может быть сохранено — 1,79769313486232E+308,
а минимальное положительное число, которое может быть сохранено — 2,2250738585072E-308.
A
B
C
...
1
1
1
...
2
4
8
...
3
9
27
...
...
...
...
...
C: Квадратные уравнения
Кроме банальной арифметики в электронных таблицах можно пользоваться множеством функций.
Если нажать на характерный значок fx над шапкой таблицы (там, где стоят имена столбцов, A, B, C, ...),
то будет открыто меню выбора функций.
Также можно получить справку по каждой функции с примерами использования.
В ячейки A1-C10 скопируйте коэффициенты a, b, c квадратных уравнений ax2 + bx + c из таблицы ниже.
В ячейках E1-E10 запишите меньший корень каждого квадратного уравнения, а в ячейки F1-F10 — больший.
Гарантируется, что у каждого квадратного уравнения есть два различных корня.
A
B
C
D
E
F
20
-15
0
0
0,75
20
181
9
...
...
1
6
8
...
...
5
49
-10
...
...
20
-78
-8
...
...
2
-13
20
...
...
1
-11
18
...
...
40
-51
-7
...
...
4
-16
-48
...
...
4
10
-24
...
...
D: Квадратные уравнения — 2
Кроме математический функций, есть также и логические: ЕСЛИ, И, ИЛИ (IF, AND, OR) и т.п.
Примеры их использования смотрите в справке.
В ячейки A1-C30 скопируйте коэффициенты a, b, c квадратных уравнений ax2 + bx + c из таблицы ниже.
В ячейках E1-E30 запишите меньший корень каждого квадратного уравнения, а в ячейки F1-F30 — больший.
Если корни совпадают, то ячейку F оставьте пустой.
Если корней нет, то в ячейку E также оставьте пустой.
A
B
C
D
E
F
1
-10
21
3
7
1
6
9
-3
-5
-3
-5
1
-1
1
...
...
1
-8
16
...
...
1
4
4
...
...
25
-30
9
...
...
5
-2
5
...
...
4
-4
1
...
...
100
60
-40
...
...
64
96
36
...
...
64
-48
9
...
...
2
4
6
...
...
2
7
8
...
...
1
-14
49
...
...
80
-48
-9
...
...
1
6
9
...
...
-9
8
-3
...
...
20
56
-49
...
...
6
0
5
...
...
-3
-5
-8
...
...
8
-73
72
...
...
10
-66
-28
...
...
40
-76
24
...
...
64
-48
9
...
...
1
-1
-72
...
...
8
-2
10
...
...
20
11
-9
...
...
10
52
10
...
...
5
5
9
...
...
E: Треугольные числа
Некоторые функции могут принимать на вход не конкретные ячейки, а целые диапазоны ячеек.
Диапазоны записываются как A1:C10 (левый верхний угол : правый нижний угол).
Если при вводе формулы выделить часть ячеек, то в формулу будет вставлен соответствующий диапазон.
Как и с обычными ссылками в диапазонах можно использовать символ $,
означающий, что соответствующая координата абсолютная, а не относительная.
В ячейки A1-A100 запишите числа от 1 до 100.
В ячейки B1-B100 запишите соответствующие треугольные числа, вычисленные при помощи функции СУММ (SUM).
В ячейки C1-C100 запишите суммы треугольных чисел, а в ячейки D1-D100 и их суммы.
A
B
C
D
1
1
1
1
2
3
4
5
3
6
10
15
4
10
20
35
…
…
…
…
F: Выписка
Ниже в спойлере приведёна выписка из банка.
Скопируйте её в таблицу и вычислите суммарные траты, суммарные поступления и изменение баланса счёта.
Запишите эти числа в ячейки E1, E2, E3 соответственно.
Для этого вам пригодится функция СУММЕСЛИ (SUMIF).
В ячейки D1, D2, D3 вставьте слова «Траты:», «Поступления:», «Баланс:».
A
B
C
D
E
F
19.06.2014
-6386,65
PAYPAL *FIVESTARSTO/4029357733/22-24
Траты:
-11997,65
19.06.2014
-3106
WWW.RZD.RU/MOSCOW/37A 1, NOVAYA BASMANNAYA.
Поступления:
4301,10
20.06.2014
-260
PSCB*HOSTLAND/ST.-PETERSBUR/TSVETOCHNAYA,25.
Баланс:
-7696,55
03.07.2014
2272,7
Перевод на свой счет. НДС не облагается
03.07.2014
2028,4
Перевод на свой счет. НДС не облагается
04.07.2014
-2245
PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
Выписка
A
B
C
03.01.2014
-8000
MOS.GOSUSLUGI.RU/MOSCOW/MOS.GOSUSLUGI.RU.
06.01.2014
-245
FISSMAN/MOSCOW/8 ANDROPOVA PR-T.
10.01.2014
2028,4
Причисление процентов по договору 1211425 от 25.06.2012.
11.01.2014
-1000
PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
14.01.2014
-2797,71
Комиссия за годовое обслуживание согласно Тарифам Банка
В строчках с последней транзакцией за каждый месяц в ячейки D, E, F
запишите суммарные траты, суммарные поступления и изменение баланса счёта за данный месяц.
Придумайте, как это делать не вручную для каждого месяца по отдельности, а автоматически.
Здесь пригодится функция ЕСЛИ (IF) с третьим параметром — пустой строкой "",
функция МЕСЯЦ (MONTH), знак неравенства <> (вместо питоновского !=),
функция СУММЕСЛИ (SUMIF), самая обычная сумма, а также игра с долларами :)
нужно скачать по ссылке и сохранить в какое-нибудь удобное место.
H: Фильтрация и среднее арифметическое
Отберите из таблицы выше данные на вашу фамилию и скопируйте их в новую таблицу.
Во втором листе этой таблицы в ячейке A1 запишите средний размер всех сданных программ,
а в ячейку A2 — только с вердиктом OK. Пригодятся функции СРЗНАЧ (AVERAGE), СРЗНАЧЕСЛИ (AVERAGEIF) и СРЗНАЧЕСЛИМН (AVERAGEIFS).
Как отбирать данные
I: Маразм крепчал или «Как в ОГЭ»
Отберите из таблицы выше данные на вашу фамилию и скопируйте их в новую таблицу.
Во втором листе этой таблицы в ячейках A1 запишите количество сдач с вердиктом OK программ размера более 200 байт,
ID сдачи которых не превосходит 800.
В ячейке A2 запишите средний размер в байтах описанных выше программ.
Для решения пригодятся СЧЁТЕСЛИМН (COUNTIFS), СУММЕСЛИМН (SUMMIFS) и СРЗНАЧЕСЛИМН (AVERAGEIFS).
J: По часам
Отберите из таблицы выше данные на вашу фамилию и скопируйте их в новую таблицу.
Во втором листе этой таблицы в ячейках A1-A24 запишите суммарное количество программ, сданных в соответствующий час,
в ячейках B1-B24 — количество программ с вердиктом OK в соответствующий час,
а в ячейках С1-С24 — долю успешных сдач от всех сдач в этот час.
Выделите строчку с минимальным «КПД» красным цветом, а строчку с максимальным — зелёным.
(В этой задаче пригодится функция СУММЕСЛИ (SUMIF), в условии которой к символу "=" подклеивается номер часа при помощи &)
K: Перед deadline'ом
Отберите из таблицы выше записи по всем ученикам с вердиктом OK или DQ за даты начиная с 01.01.2018.
Во втором листе этой таблицы в ячейках A1-A93 запишите даты от 01.01.2018 до 03.04.2018.
В ячейках B1-B93 запишите количество задач, сданных в эту дату.
В ячейках С1-С93 запишите среднее количество задач за три дня
(C[i] = (B[i-1]+B[i]+B[i+1])/3, в ячейках C1 и C93 используйте значения B1 и B93 с весом 2 (C[1] = (B[1] + B[1] + B[2])/3)).
Постройке график количества сданных задач: выделите диапазон A1-C93, нажмите ВСТАВИТЬ и ГРАФИК.
Каким событиям соответствуют характерные пики на графике?
Как вставить график (на примере Libre office Calc)
