Электронные таблицы
Формула электронной таблицы ссылается на ячейки по адресам, и при копировании относительные ссылки сдвигаются, а ссылки со знаком доллара остаются на месте. В задании 9 для каждой строки пишут формулу с функциями ЕСЛИ, И, ИЛИ, НАИБОЛЬШИЙ и СЧЁТЕСЛИ, копируют её вниз и складывают результаты. Ту же проверку можно сделать короткой программой на Python.
Формулы, ссылки и проверка строк
Формула начинается со знака равенства, аргументы функций разделяет точка с запятой. Формулы одинаково работают в Excel и LibreOffice Calc.
- Относительная ссылка
A1при копировании сдвигается на столько столбцов и строк, на сколько сдвинули формулу. Знак доллара закрепляет столбец ($A1), строку (A$1) или оба ($A$1). - Итоги по диапазону:
СУММ,СРЗНАЧ,МАКС,МИН,СЧЁТЕСЛИ,СУММЕСЛИ. - Задание 9: в первой строке справа от чисел напишите формулу, которая даёт 1, если условие выполнено, и 0, если нет:
ЕСЛИ(И(…);1;0). - Скопируйте формулу вниз на все строки и сложите столбец единиц функцией
СУММ. - Числа строки по порядку дают
НАИБОЛЬШИЙиНАИМЕНЬШИЙ, число повторов значения —СЧЁТЕСЛИ.
Ячейка, адрес и формула
Электронная таблица — таблица, в ячейках которой лежат числа, текст или формулы. Столбцы обозначены буквами, строки — числами. Адрес ячейки — буква столбца и номер строки: B3 — столбец B, строка 3. Диапазон A1:C4 — прямоугольник от левой верхней ячейки A1 до правой нижней C4.
Формула начинается со знака = и считает значение по другим ячейкам: =A1*2+A2. Когда число в A1 меняется, формула пересчитывается сама. В русских версиях программ дробную часть отделяет запятая, а аргументы функции — точка с запятой: =МАКС(A1;B1;2,5).
Пример
В ячейке A1 число 5, в A2 число 3, в B1 формула =A1*2+A2. Какое значение покажет B1?
- 1.Подставляем значения ячеек: .
- 2.Умножение выполняется раньше сложения: .
Ответ: 13
Относительные ссылки при копировании
Относительная ссылка (A1) запоминает не саму ячейку, а её положение относительно формулы. Поэтому при копировании формулы на столбцов вправо и строк вниз каждая относительная ссылка сдвигается на те же столбцов и строк. При копировании влево или вверх ссылки сдвигаются в ту же сторону.
Именно поэтому одну формулу пишут один раз и копируют на все строки: в строке 2 она берёт числа строки 2, в строке 3 — числа строки 3. На этом стоит и заполнение таблицы сумм в задании 18.
Пример
В ячейке C2 записана формула =A1+B2. Её скопировали в ячейку E5. Какой станет формула?
- 1.Сдвиг: от столбца C до E — на 2 столбца вправо, от строки 2 до 5 — на 3 строки вниз.
- 2.
A1сдвигается вC4: A плюс 2 столбца — C, строка . - 3.
B2сдвигается вD5.
Ответ: =C4+D5
Абсолютные и смешанные ссылки
Абсолютная ссылка $A$1 при копировании не меняется: знак доллара перед буквой закрепляет столбец, перед числом — строку. Смешанная ссылка закрепляет только одну часть: в $A1 столбец A не меняется, а строка сдвигается; в A$1 сдвигается только столбец. Тип ссылки переключают клавишей F4, когда курсор стоит на адресе в формуле: A1 → $A$1 → A$1 → $A1.
Абсолютную ссылку ставят на ячейку или диапазон, общий для всех строк: на итог, на коэффициент, на таблицу для функции ВПР.
Пример
В ячейке C2 записана формула =$A1*B$2. Её скопировали в ячейку E5. Какой станет формула?
- 1.Сдвиг: на 2 столбца вправо и на 3 строки вниз.
- 2.
$A1: столбец закреплён и остаётся A, строка сдвигается: . Получаем$A4. - 3.
B$2: строка закреплена и остаётся 2, столбец сдвигается из B в D. ПолучаемD$2.
Ответ: =$A4*D$2
Функции для задания 9
| Функция | Что возвращает |
|---|---|
СУММ(A1:E1) | сумму чисел диапазона |
СРЗНАЧ(A1:E1) | среднее арифметическое |
МАКС(A1:E1), МИН(A1:E1) | наибольшее и наименьшее число |
НАИБОЛЬШИЙ(A1:E1;2), НАИМЕНЬШИЙ(A1:E1;2) | второе по величине число сверху и снизу; 1 — само наибольшее или наименьшее |
СЧЁТ(A1:E1) | количество чисел в диапазоне |
СЧЁТЕСЛИ(A1:E1;7) | количество ячеек, равных 7 |
СУММЕСЛИ(G1:K1;1;A1:E1) | сумму тех ячеек A1:E1, для которых ячейка G1:K1 на том же месте равна 1 |
ЕСЛИ(условие;а;б) | значение а, если условие истинно, иначе значение б |
И(у1;у2), ИЛИ(у1;у2) | ИСТИНА, если верны все условия; ИСТИНА, если верно хотя бы одно |
Сумма, среднее и экстремум диапазона
Функции СУММ, СРЗНАЧ, МАКС и МИН принимают диапазон любой формы: строку, столбец или прямоугольник A1:X91. Так в одной формуле находят наибольшее значение всех измерений и их среднее: =МАКС(A1:X91)-СРЗНАЧ(A1:X91). Пустые и текстовые ячейки эти функции пропускают.
Если в ответе просят целую часть числа, запишите цифры до запятой: у 3,6 целая часть 3. Округлять по правилам не нужно.
Пример
В ячейках A1:E1 лежат измерения температуры: 2,4; 5,1; −1,3; 3,8; 7,0. Найдите целую часть разности наибольшего значения и среднего арифметического.
- 1.
СРЗНАЧ(A1:E1): . - 2.
МАКС(A1:E1)равно 7. - 3.Разность , её целая часть 3.
Ответ: 3
Условие для строки: ЕСЛИ, И, ИЛИ
Сравнение в формуле — A1<B1, A1>=B1, A1<>B1 («не равно») — даёт ИСТИНА или ЛОЖЬ. Функция И объединяет условия, которые должны выполняться все сразу, функция ИЛИ — условия, из которых достаточно одного. ЕСЛИ(условие;1;0) превращает результат в число 1 или 0, которое удобно складывать.
Условие из задания переводите по частям: «оба условия» — И, «хотя бы одно» — ИЛИ, «не больше» — <=, «меньше» — <. Смысл этих связок объясняет тема конъюнкция и дизъюнкция.
Пример
В строке три натуральных числа: 3, 4, 8 (ячейки A1, B1, C1). Могут ли они быть сторонами треугольника? Запишите формулу, которая даёт 1 для «да» и 0 для «нет».
- 1.Неравенство треугольника: каждая сторона меньше суммы двух других. Три условия, все сразу — функция
И. - 2.Формула:
=ЕСЛИ(И(A1<B1+C1;B1<A1+C1;C1<A1+B1);1;0). - 3.Для 3, 4, 8 третье условие ложно: . Формула даёт 0.
Ответ: 0, треугольника нет
Числа строки по порядку: НАИБОЛЬШИЙ и НАИМЕНЬШИЙ
Многие условия говорят о наибольшем числе строки, двух наибольших или «остальных» числах. НАИБОЛЬШИЙ(A1:D1;k) возвращает k-е по величине число сверху, НАИМЕНЬШИЙ(A1:D1;k) — снизу. Сумма «трёх остальных» — это сумма строки минус наибольшее: СУММ(A1:D1)-МАКС(A1:D1).
Четыре числа делятся на две пары с равными суммами только так: . Остальные разбиения дают и , а равенство в них бывает лишь тогда, когда равенство тоже верно.
Пример
В строке A1:D1 числа 30, 17, 35, 42. Выполнены ли оба условия: наибольшее число меньше суммы трёх других; числа можно разбить на две пары с равными суммами?
- 1.По порядку: 17, 30, 35, 42.
МАКС(A1:D1)равно 42,СУММ(A1:D1)-МАКС(A1:D1)равно 82. Первое условие верно: . - 2.Пары: , . Суммы не равны, второе условие ложно.
- 3.Формула для строки:
=ЕСЛИ(И(МАКС(A1:D1)<СУММ(A1:D1)-МАКС(A1:D1);МИН(A1:D1)+МАКС(A1:D1)=НАИБОЛЬШИЙ(A1:D1;2)+НАИБОЛЬШИЙ(A1:D1;3));1;0). Она даёт 0.
Ответ: Нет: первое условие выполнено, второе нет
Повторы в строке: СЧЁТЕСЛИ
Чтобы узнать, сколько раз повторяется каждое число строки, поставьте рядом вспомогательную строку: в G1 формула =СЧЁТЕСЛИ($A1:$F1;A1), затем скопируйте её вправо до L1. Столбцы диапазона закреплены знаком доллара, поэтому при копировании вправо диапазон не сдвигается, а критерий A1 переходит на B1, C1 и так далее. Строка не закреплена, и при копировании вниз формула берёт числа своей строки.
По вспомогательной строке проверяют повторы: СЧЁТЕСЛИ(G1:L1;1) — сколько чисел встречается один раз, СЧЁТЕСЛИ(G1:L1;2) — сколько ячеек занято числами, которые повторяются дважды. Сумму неповторяющихся чисел даёт СУММЕСЛИ(G1:L1;1;A1:F1), повторяющихся — СУММЕСЛИ(G1:L1;2;A1:F1).
Пример
В строке A1:F1 числа 4, 9, 4, 7, 2, 8. Проверьте оба условия: только одно число повторяется ровно два раза, остальные различны; среднее неповторяющихся чисел не больше суммы повторяющихся.
- 1.Вспомогательная строка G1:L1: 2, 1, 2, 1, 1, 1.
- 2.
СЧЁТЕСЛИ(G1:L1;2)равно 2 (одно число дважды),СЧЁТЕСЛИ(G1:L1;1)равно 4 (четыре различных). Первое условие верно. - 3.Среднее неповторяющихся:
СУММЕСЛИ(G1:L1;1;A1:F1)/4. Сумма повторяющихся: . - 4.: второе условие тоже верно.
Ответ: Оба условия выполнены
Подсчёт подходящих строк
Напишите формулу условия в первой строке, справа от чисел. Скопируйте её вниз на все строки: выделите ячейку и дважды щёлкните по квадрату в её правом нижнем углу, тогда формула дойдёт до последней строки с данными. В свободной ячейке сложите столбец единиц: =СУММ(E1:E3100). Сумма — ответ: количество строк, для которых выполнены все условия.
Пример задания
Открытый банк ФИПИ
Откройте файл электронной таблицы, содержащей в каждой строке четыре натуральных числа. Определите количество строк таблицы, содержащих числа, для которых выполнены оба условия:
– наибольшее из четырёх чисел меньше суммы трёх других;
– четыре числа можно разбить на две пары чисел с равными суммами.
В ответе запишите только число.
Та же проверка на Python
К заданию 9 обычно прилагают и файл в текстовом формате CSV. Если его нет, сохраните таблицу сами: «Файл» → «Сохранить как», тип «CSV», разделитель — точка с запятой. Каждая строка файла — строка таблицы, числа в ней разделены точкой с запятой. Программа читает файл построчно, делит строку по ';', превращает части в числа и сортирует их: a[0] — наименьшее, a[-1] — наибольшее. Условие задания записывают одним if. «Все числа различны» — len(set(a)) == len(a), число повторов — a.count(x).
Программа ниже решает задачу из предыдущего блока, если таблица сохранена как 9.csv, и печатает 118. Как программа читает числа из файла, объясняет тема чтение чисел из файла.
count = 0for line in open('9.csv'): a = sorted(int(x) for x in line.split(';')) if a[3] < a[0] + a[1] + a[2] and a[0] + a[3] == a[1] + a[2]: count += 1print(count)
Пример задания
Открытый банк ФИПИ
Задание выполняется с использованием прилагаемых файлов.
Откройте файл электронной таблицы, содержащей в каждой строке пять натуральных чисел. Определите количество строк таблицы, для чисел которых выполнены оба условия:
– в строке все числа различны;
– сумма двух наибольших чисел строки не больше суммы трёх её оставшихся чисел.
В ответе запишите только число.