Все, что тебе нужно знать

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

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

В чем смысл синтаксиса?

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

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

Что такое формула в Excel?

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

Зачем использовать формулу для этого? Ведь вы можете сами вручную подсчитать результат и просто ввести его в нужное поле.

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

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

Построение базовой формулы

Вы вводите формулу в ячейку, начиная со знака равенства, а затем печатая выражение. Например, набрав это, вы получите сумму этих двух чисел:

= 30 + 40

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

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

Вы могли заметить очевидную проблему с этим — алфавиты заканчиваются довольно быстро, так что насчет столбцов после Z? Затем мы просто начинаем комбинировать алфавиты, получая AA, AB, AC и так далее.

Используя эту ссылку, вы можете использовать значение любой ячейки электронной таблицы в формуле. Так:

= А1 + А2

Обратите внимание, что как только вы нажмете Enter, формула появится только в строке формул — сама ячейка содержит результирующее значение. Поэтому вам нужно выбрать ячейку, чтобы определить, получено ли ее значение из формулы или введено в нее вручную.

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

Введение в функции

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

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

Функции вызываются по их имени, за которым следуют любые аргументы, заключенные в квадратные скобки (или круглые скобки, как они строго называются). Например, вы можете использовать функцию СУММ, чтобы легко добавить большое количество ячеек:

Чтобы еще больше сократить формулу, мы можем указать диапазон вместо того, чтобы вводить ссылку на каждую ячейку. Например, вы можете ввести A1:A4, чтобы включить весь диапазон ячеек от A1 до A4. Это также может быть расширено на строку и включать столько ячеек, сколько вы хотите.

SUM — не единственная доступная функция. Полный список можно получить, щелкнув символ ƒ𝑥 рядом с строкой формул, но вот некоторые из наиболее распространенных:

• СРЗНАЧ — еще одна базовая функция, функция СРЗНАЧ возвращает среднее значение диапазона введенных в нее значений. Очень полезно для фискальных расчетов.

• СЦЕПИТЬ — работает с текстом, а не с числами. С помощью CONCATENATE вы можете соединить две строки вместе и вывести окончательное значение. Полезно для генерации текста с условными операторами и объединения их для получения конечного результата.

• LEN — сокращенная форма длины, LEN просто подсчитывает количество символов в заданной строке, включая пробелы. Очень ситуативный в своем использовании (например, выяснение, есть ли пробелы в пустой ячейке), но лучше, чем подсчет символов вручную.

• TRIM — довольно часто случайно вводятся лишние пробелы в некоторых ячейках, отбрасывая результаты некоторых вычислений или операций CONCATENATE. Функцию TRIM можно использовать для удаления всех лишних пробелов, оставляя только один пробел между каждым словом.

Логические функции

До сих пор мы видели множество различных функций, которые выполняют множество полезных задач, таких как SUM, AVERAGE или TRIM. А как же логические сравнения?

Как и любой другой язык программирования, синтаксис Excel включает логические операторы, такие как ЕСЛИ, И и ИЛИ. Наиболее полезной является функция ЕСЛИ, а две другие часто используются в логических выражениях.

Синтаксис IF прост: логическое выражение, которое оценивается либо как ИСТИНА, либо как ЛОЖЬ, за которым следуют значения, которым следует следовать в любом из условий, изложенных в последовательности. Разумеется, все это заключено в скобки и отделяется запятыми. Что-то вроде этого:

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

С другой стороны, первый аргумент может принимать различные формы. Логическое выражение может быть просто сравнением двух значений с использованием таких символов, как «<» или «=», но оно также может быть комбинацией нескольких таких операторов с использованием И или ИЛИ.

Это связано с тем, что и И, и ИЛИ возвращают только ИСТИНА или ЛОЖЬ, а не текстовую строку или числовое значение. Вы используете эти функции, включая два логических выражения в качестве аргументов; И возвращает ИСТИНА, если оба аргумента оцениваются как ИСТИНА, в то время как ИЛИ возвращает ИСТИНА, даже если хотя бы один из них имеет значение ИСТИНА. В противном случае возвращается ЛОЖЬ.

Используя операторы ЕСЛИ, И и ИЛИ, можно создавать очень сложные вложенные условия, которые оценивают набор различных значений, прежде чем, наконец, выдать результат. Хотя такие формулы часто подвержены ошибкам из-за всех задействованных скобок и запятых. Лучше разделить расчеты и не усложнять.

Чтобы еще больше упростить ситуацию, В Excel есть несколько функций, которые объединяют ЕСЛИ с другими полезными операциями, такими как СУММ или СРЗНАЧ..

Копирование формул

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

Нет, вы можете просто скопировать их.

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

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

Что нужно знать о синтаксисе функций Excel?

Если вы новичок, не утруждайте себя попытками запомнить каждую функцию Excel. Большинство из них редко бывают полезными, и вы можете найти их синтаксис, нажав кнопку ƒ𝑥 рядом с панелью формул.

Просто имейте в виду основной синтаксис формулы Excel — знак равенства, за которым следует выражение, которое может включать несколько функций. Каждая функция принимает некоторые аргументы (хотя некоторые функции не принимают) и возвращает значение, которое затем можно использовать в другой функции или вычислении.

Хотя вложенные функции, подобные этой, могут довольно быстро усложниться, и вы потеряете себя в беспорядке отсутствующих скобок. Мы рекомендуем делать каждую формулу простой и понятной, растягивая сложные вычисления на несколько ячеек, чтобы все было управляемо.