Электронная таблица: адреса, диапазоны и формулы

Ячейка, строка, столбец, рабочий лист; подсчёт ячеек диапазона; порядок действий и значения ошибок; относительные, абсолютные и смешанные адреса и их изменение при копировании формулы — с задачами DİM типа «D2 → E3».

  • 8 и 9 классы
  • 24 мин
  • 12 вопросов
  • Урок 7 из 59

В этом уроке ты узнаешь

  • Считать столбцы, строки и ячейки диапазона и записывать диапазон из заданного числа ячеек
  • Вычислять значение формулы с учётом порядка действий и узнавать значения ошибок
  • Находить, во что превращается формула с относительными, абсолютными и смешанными адресами при копировании в любом направлении
  • Расставлять знак $ так, чтобы формула работала после копирования

Во всех четырёх вступительных экзаменах DİM 2025–2026 годов было по два задания по электронным таблицам: одно о диаграмме, другое об адресах и формулах. Второе обычно звучит так: «Формулу из ячейки D2 скопировали в ячейку E3. Какая формула получится и каково её значение?» Для ответа нужно знать две вещи: как меняются адреса при копировании формулы и в каком порядке вычисляется формула. В уроке «Текстовые процессоры: редактирование, форматирование и клавиши» ты работал с текстовыми документами; теперь переходим к программе, которая работает с числами, — табличному процессору.

Из чего состоит электронная таблица

Табличный процессор (электронная таблица) — программа, которая выполняет вычисления, хранит данные и строит по ним диаграммы: Microsoft Excel, LibreOffice Calc, Google Sheets. Она устроена так:

  • Рабочая книга — файл Excel (.xlsx); состоит из одного или нескольких рабочих листов.
  • Рабочий лист — большая таблица из строк и столбцов; ярлыки листов внизу окна (Sheet1, Sheet2).
  • Столбец обозначается буквами: A, B, …, Z, AA, AB, …, XFD — всего 16 384 столбца. Строка — номером: 1, 2, …, 1 048 576.
  • Активная ячейка — выделенная сейчас ячейка (с жирной рамкой). Её адрес виден в поле имени (Name Box), содержимое — в строке формул.

Адрес ячейкиОпределение

Пересечение столбца и строки — ячейка, а её имя — адрес: сначала буква столбца, затем номер строки — C5, AB12 (не 5C). В справке Excel говорят «ссылка» (reference); DİM использует слово ünvan — «адрес».

В ячейке могут быть данные трёх видов: число (по умолчанию выравнивается вправо), текст (влево) и формула — она всегда начинается со знака =. В ячейке виден результат, а сама формула — в строке формул. Стоит измениться одной из ячеек, на которые ссылается формула, и она пересчитывается — в этом главная сила электронной таблицы.

Чтобы читать дальше, зарегистрируйся бесплатно

Остальные разделы урока, тест и заметки открываются с аккаунтом Educora. Регистрация занимает минуту, а прогресс сохраняется на всех твоих устройствах.

Диапазон и число его ячеек

ДиапазонОпределение

Прямоугольный блок соседних ячеек. Записывается адресами левой верхней и правой нижней ячеек через двоеточие: B2:D5 — все ячейки от B2 до D5. Часть одного столбца (C3:C12) или одной строки (B6:I6) — тоже диапазон.

N = (S₂ − S₁ + 1) · (R₂ − R₁ + 1)

Где

N
число ячеек в диапазоне
S₁, S₂
номера первого и последнего столбцов (A = 1, B = 2, …)
R₁, R₂
номера первой и последней строк

Число столбцов × число строк. Не забывай «+1»: от B до F — 5 столбцов, а не 4.

Пример 1. Размер диапазона

Задача

1) Сколько ячеек в диапазоне C3:G7?
2) Из скольких столбцов и строк состоит диапазон B2:D9?
3) Сколько ячеек в диапазоне H3:K20?
4) В столбце E выделено 12 ячеек подряд, начиная с E4. Запишите диапазон.
5) В 6-й строке выделено 8 ячеек вправо, начиная с B6. Запишите диапазон.

Показать решениеСкрыть решение

Решение

1) Столбцы C, D, E, F, G — 5; строки 3–7 — 5. N = 5 · 5 = 25.
2) Столбцы B, C, D — 3; строк: 9 − 2 + 1 = 8.
3) H = 8, K = 11: 11 − 8 + 1 = 4 столбца; 20 − 3 + 1 = 18 строк; N = 4 · 18 = 72.
4) Последняя строка: 4 + 12 − 1 = 15 → E4:E15 (в E4:E16 было бы 13 ячеек).
5) B = 2, последний столбец: 2 + 8 − 1 = 9 = I → B6:I6.

Формулы: порядок действий и значения ошибок

Формула начинается со знака = и может содержать числа, адреса, знаки операций и функции: =B2*C2, =(A1+A2)/2, =SUM(B2:B9). Пустая ячейка в вычислениях считается нулём. Действия выполняются как в математике, но по собственному порядку Excel:

  1. скобки ( );
  2. унарный минус (-5) и процент % (=20% → 0,2);
  3. возведение в степень ^ (=2^5 → 32);
  4. умножение * и деление / — слева направо;
  5. сложение + и вычитание - — слева направо;
  6. сцепление текста & и сравнения = <> < > <= >= (результат TRUE или FALSE).

Пример 2. Значение формулы

Задача

A1 = 4, B1 = 2, C1 = 5. Найдите значения формул:
1) =A1+B1*C1
2) =(A1+B1)*C1
3) =A1^B1/2
4) =A1/B1^2
5) =C1-B1^2

Показать решениеСкрыть решение

Решение

1) Сначала умножение: 2 · 5 = 10, затем 4 + 10 = 14.
2) Сначала скобки: 4 + 2 = 6, затем 6 · 5 = 30.
3) Сначала степень: 4² = 16, затем 16 / 2 = 8.
4) Сначала степень: 2² = 4, затем 4 / 4 = 1.
5) Сначала степень: 2² = 4, затем 5 − 4 = 1.
ОшибкаПричинаПример
#DIV/0!деление на ноль или на пустую ячейку=A1/B1, B1 пуста
#NAME?неизвестное имя функции=SUMM(A1:A3)
#VALUE!арифметика с текстом=A1+B1, в B1 записано abc
#REF!ссылка на несуществующую ячейку=A1*2 копируют из B2 в A1
#####число не помещается по ширине столбца (не ошибка)достаточно расширить столбец

Относительные, абсолютные и смешанные адреса

При копировании формулы в другую ячейку (Ctrl+C → Ctrl+V, протягиванием маркера заполнения или сочетаниями Ctrl+D / Ctrl+R) Excel не повторяет её буква в букву. Он запоминает адрес как положение: «на два столбца левее и на одну строку выше ячейки с формулой». Куда бы ни попала формула, это относительное положение сохраняется, поэтому адрес меняется. Благодаря этому одна формула считает целый столбец. Но иногда адрес меняться не должен — например, общая для всех строк цена, курс валюты или коэффициент. Для этого служит знак $.

АдресВидЧто меняется при копировании
B3относительныйи столбец, и строка
$B$3абсолютныйничего
$B3смешанныйтолько строка (столбец закреплён)
B$3смешанныйтолько столбец (строка закреплена)
Столбец′ = Столбец + ΔсСтрока′ = Строка + Δr

Где

Δс
на сколько столбцов формулу сдвинули вправо (+) или влево (−)
Δr
на сколько строк вниз (+) или вверх (−)

Правило применяют к каждой части адреса отдельно. Часть, перед которой стоит $, не меняется. При вырезании и вставке (Ctrl+X → Ctrl+V) адреса в формуле вообще не меняются.

  1. Найди сдвиг

    Сравни исходную и целевую ячейки: для D2 → E3 Δс = +1 (D → E), Δr = +1 (2 → 3). Сдвиг бывает и отрицательным (влево, вверх).

  2. Перепиши формулу адрес за адресом

    Сдвинь в каждом адресе часть без $, часть со $ оставь как есть. Числа, знаки и имена функций не меняются.

  3. Найди новые адреса во фрагменте

    Пустая ячейка или ячейка вне фрагмента считается нулём, ячейка с текстом в арифметике даёт #VALUE!.

  4. Вычисли с учётом порядка действий

    Скобки, степень, умножение и деление, сложение и вычитание. Затем проверь в вариантах и формулу, и значение — в неверных вариантах DİM обычно ошибочно одно из двух.

Пример 3. Копирование формулы в разных направлениях

Задача

1) Формулу =A1+$A$1+$A1+A$1 из C3 скопировали в E7. Что получится?
2) Формулу =$A4+B$2*C3 из C4 скопировали в F7 и в B3. Какие формулы получатся?
3) Формулу =A1*2 из B2 скопировали в A1. Что произойдёт?
4) Формулу из C4 вырезали (Ctrl+X) и вставили в F7. Как она изменится?

Показать решениеСкрыть решение

Решение

1) C3 → E7: Δс = +2 (C → E), Δr = +4 (3 → 7).
A1 → C5; $A$1 не меняется; $A1 → $A5 (только строка); A$1 → C$1 (только столбец).
Ответ: =C5+$A$1+$A5+C$1.
2) C4 → F7: Δс = +3, Δr = +3: $A4 → $A7, B$2 → E$2, C3 → F6 → =$A7+E$2*F6.
C4 → B3: Δс = −1, Δr = −1: =$A3+A$2*B2.
3) B2 → A1: Δс = −1, Δr = −1. Левее столбца A столбцов нет → =#REF!*2, в ячейке видно #REF!.
4) При вырезании и вставке адреса не меняются: в F7 снова =$A4+B$2*C3.
ABCD
1432
2516=A$1*$B2+$C$3-1
3273
Фрагмент электронной таблицы к примеру 4 (в D2 записана формула).

Пример 4. D2 → E3: формула и значение (тип DİM)

Задача

Во фрагменте выше формулу из ячейки D2 скопировали в ячейку E3. Какая формула получится в E3 и каково её значение?
A) =B$1*$B3+$C$3-1; 23
B) =A$1*$B3+$C$3-1; 30
C) =B$2*$C3+$C$3-1; 5
D) =B$1*$B3+$D$4-1; 20
E) =B$1*$B3+$C$3-1; 22

Показать решениеСкрыть решение

Решение

D2 → E3: на один столбец вправо (Δс = +1) и на одну строку вниз (Δr = +1).
A$1: строка закреплена, столбец меняется → B$1.
$B2: столбец закреплён, строка меняется → $B3.
$C$3: не меняется.
E3: =B$1*$B3+$C$3-1 = 3 · 7 + 3 − 1 = 23.
Ответ: A. В B не сдвинут столбец, в C перепутаны строка и столбец, в D изменён абсолютный адрес, а в E формула верна, но значение посчитано неверно.

Интерактив

Загрузка симуляции…
Мини-таблица: при копировании адреса меняются так же, как в Excel.

Пример 5. Формула, которая работает при копировании вправо (тип DİM)

Задача

Нужно вычислить значения функции y = k · x² + b. В B1 записано k = 3, в B2 — b = −2 (в A1 и A2 — буквы k и b), значения x — в диапазоне B4:F4 (1, 2, 3, 4, 5). Какую формулу нужно записать в B5, чтобы при копировании в C5:F5 все значения y получились верными?
A) =B1*B4^2+B2
B) =$B1*B4^2+$B2
C) =B$1*B4^2+B$2
D) =$B1*$B4^2+$B2
E) =$A$1*B4^2+$A$2

Показать решениеСкрыть решение

Решение

При копировании вправо меняются только буквы столбцов, номера строк остаются прежними.
k и b всегда должны браться из столбца B → закрепляем столбец: $B1, $B2.
x в каждом столбце должен браться из своей ячейки → B4 остаётся относительным.
B) =$B1*B4^2+$B2: в C5 получится =$B1*C4^2+$B2 = 3 · 4 − 2 = 10, в F5 — 3 · 25 − 2 = 73.
В A и C адреса k и b уходят в пустые ячейки C1, C2… (результат 0), в D x всегда равен B4 (везде 1), в E в A1 и A2 записан текст (#VALUE!).
Ответ: B. Замечание: $B$1 и $B$2 тоже подошли бы, но такого варианта нет.

Интерактив

Загрузка симуляции…
Смешанные адреса: при копировании вправо закрепляем столбец знаком $.

Пример 6. Верные утверждения (тип DİM)

Задача

Определите верные утверждения об электронной таблице.
1. В диапазоне C2:F4 12 ячеек.
2. Диапазон A1:A10 состоит из 10 столбцов.
3. $B2 — смешанный адрес.
4. В адресе $B$3 при копировании не меняется только столбец.
5. Формула =SUM(B1:B4) складывает значения 4 ячеек.
A) 1, 3, 5 B) 2, 4 C) 1, 4, 5 D) 3, 5 E) 1, 2, 3

Показать решениеСкрыть решение

Решение

1 — верно: 4 столбца (C–F) · 3 строки (2–4) = 12.
2 — неверно: 1 столбец и 10 строк.
3 — верно: столбец закреплён, строка меняется.
4 — неверно: $B$3 — абсолютный адрес, не меняются ни столбец, ни строка.
5 — верно: B1, B2, B3, B4.
Ответ: A) 1, 3, 5.

Профессиональная работа с адресами в Excel (именованные ячейки, ссылки на другие листы) — в уроке курса Excel «Относительные и абсолютные ссылки ($A$1)». Следующий урок — «Функции в электронной таблице»: SUM, AVERAGE, COUNT и другие функции программы экзамена.

Запомни

  • Адрес = буква столбца + номер строки (C5); диапазон записывается B2:D5 и содержит (столбцы) · (строки) ячеек — не забывай «+1» в обоих.
  • Формула начинается с =; порядок: скобки → унарный минус и % → ^ → * / → + −; пустая ячейка считается нулём.
  • При копировании относительные части сдвигаются на Δс столбцов и Δr строк, закреплённые $ части не меняются; при вырезании и вставке адреса не меняются.
  • $B$3 — абсолютный, $B3 — смешанный с закреплённым столбцом, B$3 — с закреплённой строкой; при копировании вниз важна фиксация строки, вправо — столбца.
  • Диагональное копирование (D2 → E3) раздели на два шага — столбец вправо, строка вниз, — затем вычисли значение новой формулы по фрагменту.
Заметил ошибку или неточность в уроке?

Проверь себя

Вопросов: 12. Каждый правильный ответ приносит XP.

Вопрос 1 / 12

Вопрос 1 / 12. Сколько ячеек в диапазоне C4:F8?