Все задачи в этом листке необходимо сдавать в тестирующую систему в формате xls (MS Excel 2003) или xlsx (MS Excel старше). Рекомендуется использовать xlsx.

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

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

Теория и упражнения

01: Квадраты

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

В электронных таблицах очень легко получить набор ячеек, заполненных в соответствии с арифметической прогрессией. Для этого заполните две соседние ячейки, выделите их, нажмите на маленький чёрный квадратик в правом нижнем углу рамки выделения и тяните в нужную сторону. Если в ячейке 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 стоят их квадраты.

AB
11
24
39
......

02: Квадраты, кубы, ..., десятые степени

Кроме ссылки на ячейку вида 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 ...
... ... ... ...

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

Кроме банальной арифметики в электронных таблицах можно пользоваться множеством функций. Если нажать на характерный значок 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 ......

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

Кроме математический функций, есть также и логические: ЕСЛИ, И, ИЛИ (IF, AND, OR) и т.п. Примеры их использования смотрите в справке.

В ячейки A1-C20 скопируйте коэффициенты a, b, c квадратных уравнений ax2 + bx + c из таблицы ниже. В ячейках E1-E20 запишите меньший корень каждого квадратного уравнения, а в ячейки F1-F20 — больший. Если корни совпадают, то ячейку 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 ... ...

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

Некоторые функции могут принимать на вход не конкретные ячейки, а целые диапазоны ячеек. Диапазоны записываются как 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

06: Выписка

Ниже в спойлере приведёна выписка из банка. Скопируйте её в таблицу и вычислите суммарные траты, суммарные поступления и изменение баланса счёта. Запишите эти числа в ячейки 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-8000MOS.GOSUSLUGI.RU/MOSCOW/MOS.GOSUSLUGI.RU.
06.01.2014-245FISSMAN/MOSCOW/8 ANDROPOVA PR-T.
10.01.20142028,4Причисление процентов по договору 1211425 от 25.06.2012.
11.01.2014-1000PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
14.01.2014-2797,71Комиссия за годовое обслуживание согласно Тарифам Банка
17.01.2014-508,97GIPERMARKET UYUTERRA/MOSCOW/8 ANDROPOVA PR-T.
17.01.2014-400PAYPAL *FIVESTARSTO/4029357733/22-24 Boulevard Royal,5th.
20.01.2014-515PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
22.01.2014-3886,5PAYPAL *STEAM GAMES/35314369001/Regus City Center Suite.
24.01.2014-1400PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
28.01.2014-8423,33WWW.SMS.RU/MOSCOW/15,1,PLANETNAYA STR..
08.02.2014-2922,05MOSENERGOSBYT EL./MOSKVA/VAVILOVA ST..
12.02.201420000Перевод средств на банковский счет (принят 07.08.2014 через ООО НКО "Рапида")
15.02.2014-400PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
17.02.2014-6990WWW.RZD.RU/MOSCOW/37A 1, NOVAYA BASMANNAYA.
18.02.2014-3544,68PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
18.02.2014-4869,4WWW.RZD.RU/MOSCOW/37A 1, NOVAYA BASMANNAYA.
19.02.201470000WWW.RZD.RU/MOSCOW/37A 1, NOVAYA BASMANNAYA.
24.02.2014-231OAO CPPC/MOSCOW/KOMSOMOLSKAYA PL. D.5.
25.02.2014100000WWW.RZD.RU/MOSCOW/37A 1, NOVAYA BASMANNAYA.
25.02.2014-700MOS.GOSUSLUGI.RU/MOSCOW/MOS.GOSUSLUGI.RU.
05.03.2014-245PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
06.03.2014-5000GOOGLE *Gaijin/GOOGLE.COM/CH/Belgrave House 76 Buckingham P.
08.03.2014-311KARUSEL/MOSCOW/8 ANDROPOVA PR-T.
10.03.2014-295PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
16.03.2014321,28Перевод средств на банковский счет (принят 08.05.2014 через ООО НКО "Рапида")
17.03.2014-270MOSTSVETTORG AVTOZ/MOSCOW/19 LENINSKAYA SLOBODA STR BL.
19.03.2014-859,11KARUSEL/MOSCOW/8 ANDROPOVA PR-T.
19.03.2014-4138,2п GOOGLE *Gaijin/GOOGLE.COM/CH/Belgrave House 76 Buckingham P.
19.03.2014-290PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
01.04.2014-3000SERVISTREID/MOSCOW/8 ANDROPOVA PR..
02.04.2014-5182QBank Mobile/MOSKVA/LENINSKAYA SLOBODA 19.
02.04.2014-2345,67KARUSEL/MOSCOW/8 ANDROPOVA PR-T.
05.04.2014-4076,4OOO DISK-AUT 2000/MOSCOW/205 ANDROPOVA PR-T.
08.04.2014-1295,5QBank/MOSKVA/LENINSKAYA SLOBODA 19.
09.04.2014-3886,5LEROY MERLIN - LEFORTO/MOSKVA/12/2 ENTUZIASTOV SH.
09.04.2014-12702,4RAMBLER.RU/MOSKVA/LENINSKAYA SLOBODA ST..
12.04.2014-50Restaurant Giggijoch/Soelden/HOF 418.
26.04.2014-3600PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
28.04.2014-3000MOS.GOSUSLUGI.RU/MOSCOW/MOS.GOSUSLUGI.RU.
28.04.2014-1129,94PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
08.05.2014-13990AVIS RENT-A-CAR/E823023703/UNKNOWN.
09.05.20149400Перевод на свой счет. НДС не облагается
11.05.2014-270QBank Autoplatezh/MOSKVA/LENINSKAYA SLOBODA 19.
14.05.20142028,4Причисление процентов по договору 1211425 от 25.06.2012.
22.05.2014-272п KARUSEL/MOSCOW/8 ANDROPOVA PR-T.
22.05.2014-6000KARUSEL/MOSCOW/8 ANDROPOVA PR-T.
25.05.201440000WWW.RZD.RU/MOSCOW/37A 1, NOVAYA BASMANNAYA.
28.05.2014-1363,78STRAHOVOY.SVYAZNOY/STRAHOVOY.SVY/STRAHOVOY.SVYAZNOY.RU.
29.05.2014-28110На различные расходы. Без НДС. НДС не облагается
30.05.2014-598,65KARUSEL/MOSCOW/8 ANDROPOVA PR-T.
30.05.2014-8761,6LUKOIL AZS 529/MOSCOW/7-YA KOJUHOVSKAYA.
30.05.2014-110WWW.RZD.RU/MOSCOW/37A 1, NOVAYA BASMANNAYA.
03.06.2014-600QBank Autoplatezh/MOSKVA/LENINSKAYA SLOBODA 19.
10.06.201450000Перевод на свой счет. НДС не облагается
16.06.2014-50VKUSVILL/MOSCOW/TROFIMOVA, 35-20.
19.06.2014-6386,65PAYPAL *FIVESTARSTO/4029357733/22-24 Boulevard Royal,5th.
19.06.2014-3106WWW.RZD.RU/MOSCOW/37A 1, NOVAYA BASMANNAYA.
20.06.2014-260PSCB*HOSTLAND/ST.-PETERSBUR/TSVETOCHNAYA,25.
03.07.20142272,7Перевод на свой счет. НДС не облагается
03.07.20142028,4Перевод на свой счет. НДС не облагается
04.07.2014-2245PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
04.07.2014-2839,53LMEXPRESS 77/MOSKVA/1, 18 STR., SKLADOTCHNAYA.
05.07.2014201,95Перевод средств на банковский счет (принят 09.05.2014 через ООО НКО "Рапида")
25.07.2014-3392QBank/MOSKVA/LENINSKAYA SLOBODA 19.
30.07.2014-1283AZBUKA VKUSA VALOV/MOSCOW/8-18 VALOVAYA STR.
05.08.2014-590GOOGLE *LYSESOFT/GOOGLE.COM/CH/Belgrave House 76 Buckingham P.
06.08.2014-265WWW.SMS.RU/MOSCOW/15,1,PLANETNAYA STR..
09.08.2014-2000MUC 421 TRAVEL VALUE/MUENCHEN FLUG/IT-ABTEILUNG.
09.08.2014-260MOSTSVETTORG AVTOZ/MOSCOW/19 LENINSKAYA SLOBODA STR BL.
15.08.2014-1848,49INGOSSTRAH/MOSCOW/MARKSISTSKAYA STR., 34.
26.08.201420000S7/NOVOSIBIRSK/FRUNZE ST..
27.08.2014-8761,6WWW.RZD.RU/MOSCOW/37A 1, NOVAYA BASMANNAYA.
01.09.2014-200000MOSTSVETTORG AVTOZ/MOSCOW/19 LENINSKAYA SLOBODA STR BL.
11.09.2014-280AUCHAN CITY MOSKVA/MOSCOW/PREVOBEREJNAYA ST. 1 B.
18.09.2014-400QBank Autoplatezh/MOSKVA/LENINSKAYA SLOBODA 19.
23.09.2014-187,9PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
28.09.2014-2948SWAROVSKI KRISTALLWELT/WATTENS/KRISTALLWELTENSTR 1.
28.09.2014-1480PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
06.10.2014-1017PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
06.10.2014-1000WWW.123.RU/MOSCOW/4 BARABANNYY BYSTR BLD 2.
09.10.20142028,4WWW.RZD.RU/MOSCOW/37A 1, NOVAYA BASMANNAYA.
20.10.2014-3400LUKOIL AZS 234/NEMCOVO/M4 69 km.
23.10.2014-884GOOGLE *ustwo/GOOGLE.COM/CH/Belgrave House 76 Buckingham P.
27.10.2014-1400MOS.GOSUSLUGI.RU/MOSCOW/MOS.GOSUSLUGI.RU.
30.10.2014-590Eni ServiceStation 104/Laengenfeld/Gewerbegebiet 352.
14.11.20142272,7Перевод на свой счет. НДС не облагается
14.11.201410776поездка раз. Без НДС
14.11.20142028,4WWW.RZD.RU/MOSCOW/37A 1, NOVAYA BASMANNAYA.
20.11.2014-9738,8PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
26.11.2014-248PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
28.11.2014100000WWW.RZD.RU/MOSCOW/37A 1, NOVAYA BASMANNAYA.
29.11.2014-25684OZON.RU/MOSCOW/NAMETKINA STR 10 BLD 1.
01.12.2014-4900PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5.
15.12.20142028,4Перевод средств на банковский счет (принят 08.05.2014 через ООО НКО "Рапида")
17.12.2014-1295,5PAYPAL *AOKATECH/4029357733/22-24 Boulevard Royal,5th.
17.12.2014-4076,4QBank Autoplatezh/MOSKVA/LENINSKAYA SLOBODA 19.
24.12.2014-1490Перевод на свой счет. НДС не облагается
24.12.2014-1957,97AUCHAN CITY MOSKVA/MOSCOW/PREVOBEREJNAYA ST. 1 B.
25.12.20142028,4WWW.RZD.RU/MOSCOW/37A 1, NOVAYA BASMANNAYA.

07: Выписка — 2

В строчках с последней транзакцией за каждый месяц в ячейки D, E, F запишите суммарные траты, суммарные поступления и изменение баланса счёта за данный месяц. Придумайте, как это делать не вручную для каждого месяца по отдельности, а автоматически. Здесь пригодится функция ЕСЛИ (IF) с третьим параметром — пустой строкой "", функция МЕСЯЦ (MONTH), знак неравенства <> (вместо питоновского !=), функция СУММЕСЛИ (SUMIF), самая обычная сумма, а также игра с долларами :)

A B C D E F
19.06.2014 -6386,65 PAYPAL *FIVESTARSTO/4029357733/22-24
19.06.2014 -3106 WWW.RZD.RU/MOSCOW/37A 1, NOVAYA BASMANNAYA.
20.06.2014 -260 PSCB*HOSTLAND/ST.-PETERSBUR/TSVETOCHNAYA,25. -9752,650-9752,65
03.07.2014 2272,7 Перевод на свой счет. НДС не облагается
03.07.2014 2028,4 Перевод на свой счет. НДС не облагается
04.07.2014 -2245 PRED. PIT-YA NAGATINO/MOSKVA/ANDROPOVA 18-5. -2245,004301,102056,10

08: Фильтрация и среднее арифметическое

Отберите из таблицы выше данные на вашу фамилию и скопируйте их в новую таблицу. Во втором листе этой таблицы в ячейке A1 запишите средний размер всех сданных программ, а в ячейку A2 — только с вердиктом OK. Пригодятся функции СРЗНАЧ (AVERAGE), СРЗНАЧЕСЛИ (AVERAGEIF) и СРЗНАЧЕСЛИМН (AVERAGEIFS).

Как отбирать данные

09: Маразм крепчал или «Как в ОГЭ»

Отберите из таблицы выше данные на вашу фамилию и скопируйте их в новую таблицу. Во втором листе этой таблицы в ячейках A1 запишите количество сдач с вердиктом OK программ размера более 200 байт, ID сдачи которых не превосходит 800. В ячейке A2 запишите средний размер в байтах описанных выше программ. Для решения пригодятся СЧЁТЕСЛИМН (COUNTIFS), СУММЕСЛИМН (SUMMIFS) и СРЗНАЧЕСЛИМН (AVERAGEIFS).

10: По часам

Отберите из таблицы выше данные на вашу фамилию и скопируйте их в новую таблицу. Во втором листе этой таблицы в ячейках A1-A24 запишите суммарное количество программ, сданных в соответствующий час, в ячейках B1-B24 — количество программ с вердиктом OK в соответствующий час, а в ячейках С1-С24 — долю успешных сдач от всех сдач в этот час. Выделите строчку с минимальным «КПД» красным цветом, а строчку с максимальным — зелёным. (В этой задаче пригодится функция СУММЕСЛИ (SUMIF), в условии которой к символу "=" подклеивается номер часа при помощи &)

11: Перед deadline'ом

Отберите из таблицы выше записи с вердиктом OK или DQ за даты начиная с 01.01.2015. Во втором листе этой таблицы в ячейках A1-A93 запишите даты от 01.01.2015 до 03.04.2015. В ячейках 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)