Задача 9 (Excel) в 2023 г.

Хотите готовиться со мной к ЕГЭ?
Пишите:  ydkras@mail.ru
Немного обо мне.

Задача 9 в демонстрационном варианте ЕГЭ по информатике на сайте ФИПИ - неожиданно сложная и мало похожая на задачи прошлых лет.

Вот её текст:

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

Рассмотрим два способа решения этой задачи: непостредственно в Excel и с помощью программы на Питоне.

Решение в Excel

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

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

 

	A	B	C	D	E	F
1	37	83	24	19	37	41
2	59	77	43	43	118	38
3	6	65	40	22	6	130
4	63	77	76	8	63	51
5	74	47	97	26	222	23
6	48	24	17	7	24	36
7	27	39	77	35	27	13
8	77	67	74	3	115	134
9	19	41	45	45	19	41
10	74	66	64	33	74	33

 

Разумеется, это лишь незначительная часть исходной таблицы (в ней 6400 строк), но для объяснения решения этих данных достаточно.

Сначала запишем в ячейку H1 следующую формулу:

=ЕСЛИ(СЧЁТЕСЛИ($A1:$F1;A1)>1;A1;"")

Функция СЧЁТЕСЛИ подсчитывает, сколько раз в ячейках A1:F1 встречается число из ячейки A1. Если более одного раза - то в ячейку H1 будет записано число из A1, в противном случае ячейка H1 будет пустой. 

Теперь скопируем эту формулу в ячейки I1:M1, а потом группу ячеек H1:M1 скопируем в строки ниже первой до конца таблицы. Получим следующую таблицу:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	37	83	24	19	37	41		37				37
2	59	77	43	43	118	38				43	43
3	6	65	40	22	6	130		6				6
4	63	77	76	8	63	51		63				63
5	74	47	97	26	222	23
6	48	24	17	7	24	36			24			24
7	27	39	77	35	27	13		27				27
8	77	67	74	3	115	134
9	19	41	45	45	19	41		19	41	45	45	19	41
10	74	66	64	33	74	33		74			33	74	33

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

Теперь запишем в ячейку O1 такую формулу:

=ЕСЛИ(H1="";A1;"")

Если ячейка H1 пустая, то в ячейку O1 будет скопировано число из ячейки A1, в противном случай ячейка O1 будет пустой.

Копируем эту формулу в ячейки P1:T1, а потом группу ячеек O1:T1 копируем в строки ниже.

Теперь наша таблица выглядит так:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	37	83	24	19	37	41		37				37				83	24	19		41
2	59	77	43	43	118	38				43	43				59	77			118	38
3	6	65	40	22	6	130		6				6				65	40	22		130
4	63	77	76	8	63	51		63				63				77	76	8		51
5	74	47	97	26	222	23									74	47	97	26	222	23
6	48	24	17	7	24	36			24			24			48		17	7		36
7	27	39	77	35	27	13		27				27				39	77	35		13
8	77	67	74	3	115	134									77	67	74	3	115	134
9	19	41	45	45	19	41		19	41	45	45	19	41
10	74	66	64	33	74	33		74			33	74	33			66	64

Строка удовлетворяет условию задачи, если 1) пустых клеток в ячейках H1:M1 ровно 4 и 2) среднее значение в ячейках O1:T1 меньше или равно сумме ячеек H1:M1.

Поместим в ячейку V1 следующую формулу:

=ЕСЛИ(И(СЧЁТЕСЛИ(H1:M1;"")=4;СРЗНАЧ(O1:T1)<=СУММ(H1:M1));1;"")

Эта формула должна записывать в ячейку V1 единицу, если строка отвечает условию, а в противном случае оставлять её пустой. 

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

Вот что у нас получилось:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	37	83	24	19	37	41		37				37				83	24	19		41		1
2	59	77	43	43	118	38				43	43				59	77			118	38		1
3	6	65	40	22	6	130		6				6				65	40	22		130
4	63	77	76	8	63	51		63				63				77	76	8		51		1
5	74	47	97	26	222	23									74	47	97	26	222	23
6	48	24	17	7	24	36			24			24			48		17	7		36		1
7	27	39	77	35	27	13		27				27				39	77	35		13		1
8	77	67	74	3	115	134									77	67	74	3	115	134
9	19	41	45	45	19	41		19	41	45	45	19	41									#ДЕЛ/0!
10	74	66	64	33	74	33		74			33	74	33			66	64

К сожалению, в строке 9 мы видим сообщение "#ДЕЛ/0!". Это случилось потому, что в данной строке нет уникальных значений, из-за чего все ячейки от O до T в данной строке пустые и при попытке вычислить среднее значение этих ячеек действительно происходит деление на 0.

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

Вот новая формула, которую надо записать в ячейку V1, а потом скопировать во все строки ниже:

=ЕСЛИ(СЧЁТЕСЛИ(H2:M2;"")=4;ЕСЛИ(СРЗНАЧ(O2:T2)<=СУММ(H2:M2);1;"");"")

В этой формуле среднее значение вычисляется только тогда, когда не все ячейки от O до T пустые, поэтому деления на ноль не возникает.

Возможен и другой вариант избавления от деления на ноль - использование функции ЕСЛИОШИБКА. Первым параметром в ней указываем наше выражение, а вторым - значение, которое надо выводить, если в первом возникают ошибки при вычислении:

=ЕСЛИОШИБКА(ЕСЛИ(И(СЧЁТЕСЛИ(H2:M2;"")=4;СРЗНАЧ(O2:T2)<=СУММ(H2:M2));1;"");"")

Заменяем формулу в ячейках столбца V, записываем в ячейку X1 формулу, суммирующую числа в столбце V, и в ней появляется ответ:

 

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	37	83	24	19	37	41		37				37				83	24	19		41		1		5
2	59	77	43	43	118	38				43	43				59	77			118	38		1
3	6	65	40	22	6	130		6				6				65	40	22		130
4	63	77	76	8	63	51		63				63				77	76	8		51		1
5	74	47	97	26	222	23									74	47	97	26	222	23
6	48	24	17	7	24	36			24			24			48		17	7		36		1
7	27	39	77	35	27	13		27				27				39	77	35		13		1
8	77	67	74	3	115	134									77	67	74	3	115	134
9	19	41	45	45	19	41		19	41	45	45	19	41
10	74	66	64	33	74	33		74			33	74	33			66	64

 

 

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

 

=СЧЁТЕСЛИ(V:V;1)

 

Решаем задачу на Питоне 

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

(Замечу, что подобный подход вполне годится и для многих других задач обработки числовой информации в Excel, предлагавшихся на ЕГЭ.)

Прежде всего необходимо сохранить nаблицу Excel в текстовом файле (формат csv). В этом формате данные из ячеек таблицы разделяются символом-разделителем. По умолчанию это точка с запятой, но при сохранении можно указать и другой символ (например, пробел). Для наших целей вполне подойдёт и точка с запятой. 

Сохраненный в формате csv файл выглядит так:

 

37;83;24;19;37;41
59;77;43;43;118;38
6;65;40;22;6;130
63;77;76;8;63;51
74;47;97;26;222;23
48;24;17;7;24;36
27;39;77;35;27;13
77;67;74;3;115;134
19;41;45;45;19;41
74;66;64;33;74;33

Когда файл сохранен на диске, можно писать программу для его обработки.

Алгоритм достаточно прост. Открываем сохраненный файл. Переменной k присваиваем значение 0: это счетчик строк, удовлетворяющих условию. Затем в цикле читаем строки из файла и каждую строку преобразуем в массив a из шести целых чисел. Создаем дла пустых массива povt и unik, затем в цикле просматриваем массив a и те элементы, которые встречаются в нём один раз, добавляем в массив unik, а те, которые встречаются более одного - в массив povt. Если массив povt содержит ровно два элемента и среднее арифметическое чисел в массиве unik (т.е. сумма чисел, деленная на их количество) не больше суммы чисел в массиве povt, увеличиваем счетчик k на единицу. Когда цикл закончится, печатаем переменную k.

Вот полный текст программы:

f=open('9.csv')
k=0
for s in f:
    a=list(map(int,s.split(';')))
    povt=[]
    unik=[]
    for n in a:
        (unik if a.count(n)==1 else povt).append(n)
    if len(povt)==2 and sum(unik)/len(unik) <= sum(povt):
        k += 1
print(k)

 

В строке

(unik if a.count(n)==1 else povt).append(n)

использовано условное выражение. Эта строка эквивалентна следующему фрагменту:

if a.count(n)==1:
    unik.append(n)
else:
    povt.append(n)

 

Внимательный читатель может спросить: а почему в этой программе не возникает деления на ноль, если в массиве a нет уникальных чисел, т.е. если массив unik пустой и len(unik)=0? Дело в том, что в Питоне (как и в C) логические выражения вычисляются слева направо и если на каком-то этапе значение логического выражения уже определено, то дальнейшие вычисления не производятся. В данном случае если len(povt) не равно двум, то выражение заведомо ложно и второе условие, т.е. sum(unik)/len(unik) <= sum(povt), не проверяется.

Немного о сортировке и медиане

После публикации новой задачи 9 на сайте ФИПИ в интернете появилось множество задач подобного типа. Вот, например, одна из задач с сайта Полякова:

(№ 5525) (А. Рогов) В файле электронной таблицы 9-170.xls в каждой строке содержатся шесть натуральных чисел. Определите количество строк таблицы, для которых выполнены оба условия:
– в строке нет чисел, которые повторяются;
– среднее арифметическое чисел строки не меньше медианы чисел строки.
Примечание. Медиана — это число, которое находится в середине отсортированного набора чисел. Для четного количества чисел за медиану принимают полусумму двух стоящих в центре чисел. Так, для набора 1, 4, 6, 9 медиана равна 5. 

Вычисление медианы предполагает, что мы отсортировали наши шесть чисел и взяли полусумму третьего и четвертого (после сортировки) чисел. Естественно, возникает вопрос: а как отсортировать числа по возрастанию - независимо в каждой строке? 

Оказывается, это достаточно просто сделать с помощью функции НАИМЕНЬШИЙ. У этой функции два параметра. Первый - это диапазон ячеек, из которого берутся числовые значения. Второй - натуральное число, если это 1 - функция возвращает наименьшее значение, 2 - второе по малости и т.д.

Имеется также функция НАИБОЛЬШИЙ, которая аналогична функции НАИМЕНЬШИЙ, но возвращает значения не по возрастанию, а по убыванию.

С помощью этих функция задача сортировки чисел в строке решается очень просто. 

Допустим, у нас есть следующая таблица:

	A	B	C	D	E	F
1	37	83	24	19	37	41
2	59	77	43	43	118	38
3	6	65	40	22	6	130
4	63	77	76	8	63	51
5	74	47	97	26	222	23
6	48	24	17	7	24	36
7	27	39	77	35	27	13
8	77	67	74	3	115	134
9	19	41	45	45	19	41
10	74	66	64	33	74	33

 

Вписываем в ячейку H1 формулу =НАИМЕНЬШИЙ(A1:F1;1), в ячейку I1 - формулу =НАИМЕНЬШИЙ(A1:F1;2), ..., в ячейку M1 - формулу =НАИМЕНЬШИЙ(A1:F1;6). Теперь в ячейках H1:M1 находятся числа из ячеек A1:F1, отсортированные по возрастанию.

Копируем формулы из ячеек H1:M1 в расположенные ниже строки и получаем желаемый результат:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	37	83	24	19	37	41		19	24	37	37	41	83
2	59	77	43	43	118	38		38	43	43	59	77	118
3	6	65	40	22	6	130		6	6	22	40	65	130
4	63	77	76	8	63	51		8	51	63	63	76	77
5	74	47	97	26	222	23		23	26	47	74	97	222
6	48	24	17	7	24	36		7	17	24	24	36	48
7	27	39	77	35	27	13		13	27	27	35	39	77
8	77	67	74	3	115	134		3	67	74	77	115	134
9	19	41	45	45	19	41		19	19	41	41	45	45
10	74	66	64	33	74	33		33	33	64	66	74	74

 

Если чисел в строке много, то выписывать формулу с функцией НАИМЕНЬШИЙ много раз затруднительно. Можно избежать этого следующим образом. Вставим сверху таблицы пустую строку  (поместим курсор на заголовок строки 1, нажмем правую кнопку и выберем "Вставить строки выше"). В ячейки H1:M1 впишем последовательные числа 1, 2, ..., 6. Теперь впишем в ячейку H2 формулу =НАИМЕНЬШИЙ($A2:$F2;H$1) и скопируем её сначала в ячейки I2:M2, а потом в строки ниже.

	A	B	C	D	E	F	G	H	I	J	K	L	M
1								1	2	3	4	5	6
2	37	83	24	19	37	41		19	24	37	37	41	83
3	59	77	43	43	118	38		38	43	43	59	77	118
4	6	65	40	22	6	130		6	6	22	40	65	130
5	63	77	76	8	63	51		8	51	63	63	76	77
6	74	47	97	26	222	23		23	26	47	74	97	222
7	48	24	17	7	24	36		7	17	24	24	36	48
8	27	39	77	35	27	13		13	27	27	35	39	77
9	77	67	74	3	115	134		3	67	74	77	115	134
10	19	41	45	45	19	41		19	19	41	41	45	45
11	74	66	64	33	74	33		33	33	64	66	74	74

 

Как видим, сортировка чисел в строке excel - вполне решаемая задача.

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

Чтобы убедиться, что все числа в строке различны, выведем в ячейках H1:M1 числа, показывающие, сколько раз встречается в строке соответствующее число. Впишем в ячейку H1 формулу =СЧЁТЕСЛИ($A1:$F1;A1) и скопируем её в ячейки I1:M1, а затем - в строки ниже.  

Теперь можно легко проверять, что все числа в строке различны: при этом максимум в ячейках H1:M1 будет равен 1 или же сумма чисел в ячейках H1:M1 будет равна 6.

Для вычисления медианы чисел из ячеек A1:F1 воспользуемся выражением (НАИМЕНЬШИЙ(A1:F1;3)+НАИМЕНЬШИЙ(A1:F1;3))/2. (Очевидно, для вычисления медианы нам нет необходимости сортировать массив целиком, достаточно взять лишь два числа из середины отсортированного массива.)

Впишем в ячейку O1 формулу
=ЕСЛИ(И(МАКС(H1:M1)=1;СУММ(A1:F1)/6>=(НАИМЕНЬШИЙ(A1:F1;3)+НАИМЕНЬШИЙ(A1:F1;4))/2);1;"")
и размножим её в ячейки ниже. В строках, удовлетворяющих условию, будут единицы, в остальных - пустые ячейки.

В ячейку Q1 запишем формулу, суммирующую числа в столбце O (=СУММ(O:O)) либо подсчитывающую количество единиц в этом столбце (=СЧЁТЕСЛИ(O:O;1)) и получаем ответ.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	37	83	24	19	37	41		2	1	1	1	2	1				2
2	59	77	43	43	118	38		1	1	2	2	1	1
3	6	65	40	22	6	130		2	1	1	1	2	1
4	63	77	76	8	63	51		2	1	1	1	2	1
5	74	47	97	26	222	23		1	1	1	1	1	1		1
6	48	24	17	7	24	36		1	2	1	1	2	1
7	27	39	77	35	27	13		2	1	1	1	2	1
8	77	67	74	3	115	134		1	1	1	1	1	1		1
9	19	41	45	45	19	41		2	2	2	2	2	2
10	74	66	64	33	74	33		2	1	1	2	2	2

 

Приведем также решение на Питоне. Как уже говорилось, предварительно необходимо сохранить таблицу excel в текстовом файле (формат CSV).

f=open('9.csv')

k=0

for s in f:

a=list(map(int,s.split(';')))

a.sort()

if len(a)==len(set(a)) and sum(a)/6 >= (a[2]+a[3])/2:

k += 1

print(k)

Функция set создает из массива a множество: тип данных, представляющих неупорядоченную совокупность значений, в которую каждое значение не может входить более одного раза. Если количество элементов множества равно количеству элементов в исходном массиве, то это означает, что среди элементов массива нет повторяющихся. (Разумеется, проверку всех чисел в массиве на уникальность можно выполнить многими способами, но данный представляется мне наиболее простым.)

(c) Ю.Д.Красильников, 2022 г.