От простого сложения до сложных расчетов в Excel

Иван Корнев·21.05.2024·⏱5 мин

Чтобы написать формулу в Excel, начните ввод данных в ячейке со знака = (равно). Без этого символа программа воспримет текст как обычную строку. Сразу после знака равно можно вводить числа, математические операторы (+, -, *, /) или названия функций (например, =СУММ(A1:A5)). Нажмите Enter, чтобы получить результат. Это базовый принцип работы всех вычислений в таблице.

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

Оглавление

Основы ввода и адресация ячеек

Главное правило: любая формула начинается с =. После ввода выражения результат отображается в ячейке, а сама формула видна в строке формул сверху.

Типы ссылок

При копировании формул поведение ссылок меняется. Чтобы управлять этим, используйте знак доллара $:

  • Относительная ссылка (A1): при копировании вниз ссылка изменится на A2, A3. Используется по умолчанию.
  • Абсолютная ссылка ($A$1): ссылка «замораживается». При копировании она всегда будет указывать на ячейку A1.
  • Смешанная ссылка ($A1 или A$1): фиксируется только столбец или только строка.

Лайфхак: Не печатайте $ вручную. Выделите ссылку в формуле и нажмите клавишу F4. Excel сам переключит типы ссылок по кругу.

Математические и статистические функции

Функции — это готовые алгоритмы. В русской версии Excel аргументы разделяются точкой с запятой ;, в английской — запятой ,.

Функция (РУС / ENG)ОписаниеПример использования
СУММ / SUMСкладывает числа в диапазоне.=СУММ(A1:A10)
СРЗНАЧ / AVERAGEВычисляет среднее арифметическое.=СРЗНАЧ(B1:B20)
СЧЁТ / COUNTСчитает количество ячеек с числами.=СЧЁТ(C1:C100)
МАКС / MAXНаходит наибольшее значение.=МАКС(D1:D50)
МИН / MINНаходит наименьшее значение.=МИН(D1:D50)
ОКРУГЛ / ROUNDОкругляет число до заданного знака.=ОКРУГЛ(3,14159; 2) → 3,14

Для ввода функции можно использовать мастер: нажмите Shift + F3, выберите категорию и следуйте подсказкам окна.

Логика и условия: функция ЕСЛИ

Функция ЕСЛИ позволяет автоматизировать принятие решений. Она проверяет условие и возвращает одно значение, если оно истинно, и другое — если ложно.

Синтаксис: =ЕСЛИ(условие; значение_если_истина; значение_если_ложь)

Примеры:

  1. Простая проверка: =ЕСЛИ(A1>100; "Выполнено"; "Не выполнено")
  2. Расчет скидки: =ЕСЛИ(B2>=5000; B2*0,9; B2) (если сумма больше 5000, применяется скидка 10%).
  3. Вложенные условия: Можно вставлять одну функцию ЕСЛИ внутрь другой для проверки нескольких критериев.

Важно: Текстовые значения внутри формул обязательно должны быть заключены в кавычки ("Текст"). Числа пишутся без кавычек.

Для обработки ошибок деления на ноль используйте функцию ЕСЛИОШИБКА: =ЕСЛИОШИБКА(A1/B1; 0) — вернет 0, если в B1 стоит ноль, вместо страшного кода ошибки.

Поиск данных: ВПР и ИНДЕКС

Когда нужно найти значение в большой таблице по ключу (например, цену товара по его артикулу), используют поисковые функции.

ВПР (VLOOKUP)

Ищет значение в первом столбце диапазона и возвращает данные из указанной колонки справа. =ВПР(искомое_значение; таблица; номер_столбца; 0)

  • Последний аргумент 0 (или ЛОЖЬ) обязателен для точного совпадения.

ИНДЕКС + ПОИСКПОЗ (INDEX + MATCH)

Более гибкая связка, которая умеет искать слева направо и наоборот, а также работает быстрее на огромных массивах данных. =ИНДЕКС(столбец_с_результатом; ПОИСКПОЗ(искомое; столбец_для_поиска; 0))

Работа с текстом и датами

Excel умеет манипулировать строками и временными метками так же легко, как числами.

  • Сцепление текста: Используйте амперсанд & или функцию СЦЕПИТЬ.
    • Пример: =A1 & " " & B1 объединит имя и фамилию через пробел.
  • Извлечение частей текста:
    • =ЛЕВСИМВ(A1; 3) — первые 3 символа.
    • =ПРАВСИМВ(A1; 4) — последние 4 символа.
    • =ПСТР(A1; 2; 5) — 5 символов, начиная со 2-го.
  • Даты:
    • =СЕГОДНЯ() — текущая дата (обновляется автоматически).
    • =ДАТА(2024; 12; 31) — создает дату из чисел.
    • =ТЕКСТ(A1; "ДД.ММ.ГГГГ") — преобразует дату в нужный текстовый формат.

Расшифровка кодов ошибок

Если формула не работает, Excel выдает код ошибки. Вот что они означают и как их исправить:

Код ошибкиПричина возникновенияСпособ решения
#ЗНАЧ!Неверный тип аргумента (например, текст вместо числа).Проверьте данные в ячейках, уберите лишние пробелы.
#ИМЯ?Опечатка в названии функции или отсутствие кавычек у текста.Проверьте написание функции (СУММ, а не СУМ).
#ДЕЛ/0!Попытка деления на ноль или пустую ячейку.Используйте ЕСЛИОШИБКА или проверьте знаменатель.
#ССЫЛКА!Удалена ячейка, на которую была ссылка.Восстановите ячейки или исправьте диапазон.
#ЧИСЛО!Проблема с числами (слишком большое число или неверный формат даты).Проверьте диапазоны допустимых значений.

Частые ошибки пользователей

  1. Игнорирование абсолютных ссылок. При протягивании формулы ссылки «уезжают», и расчет становится неверным. Всегда проверяйте, нужны ли знаки $.
  2. Разделители аргументов. Путаница между точкой с запятой ; и запятой ,. Ориентируйтесь на настройки вашей системы (в РФ обычно ;).
  3. Текст вместо чисел. Если число сохранено как текст (часто бывает при выгрузке из 1С или банков), формулы суммирования его проигнорируют. Преобразуйте формат ячейки в «Числовой».
  4. Циклические ссылки. Когда формула ссылается сама на себя. Excel выдаст предупреждение и перестанет считать. Проверьте адреса в формуле.

FAQ

Как посмотреть все формулы на листе сразу? Нажмите сочетание клавиш Ctrl + ~ (тильда, клавиша под Esc). Это переключит режим отображения значений на режим отображения формул. Повторное нажатие вернет обычный вид.

Можно ли писать формулы на английском в русской версии Excel? Да, современные версии Excel понимают названия функций на обоих языках. Однако при сохранении файла имена функций могут автоматически заменяться на язык интерфейса программы. Для совместимости лучше использовать локальные названия.

Почему формула не пересчитывается автоматически? Проверьте режим вычислений: вкладка «Формулы» -> «Параметры вычисления». Должно быть выбрано «Автоматически». Если стоит «Вручную», нажимайте F9 для обновления результатов.