Содержание:
У многих начинающих пользователей Excel часто возникает вопрос: что такое формула Excel и как ее ввести в ячейку. Многие даже задумываются, зачем это нужно. Для них Excel — это электронная таблица. Но по сути это большой многофункциональный калькулятор и в какой-то степени среда программирования.
Понятие формулы и функции
А вся работа в Excel строится на формулах, которых огромное количество. В основе любой формулы лежит функция. Это базовый вычислительный инструмент, который возвращает значение в зависимости от переданных данных после их предварительной обработки.
Формула — это набор логических операторов, арифметических операций и функций. Он не всегда содержит все эти элементы. Расчет может включать, например, только математические операции.
В повседневной речи пользователи Excel часто путают эти понятия. На самом деле грань между ними довольно условна, и оба термина используются часто. Однако для лучшего понимания работы с Excel необходимо знать правильные значения.
На самом деле терминологический аппарат гораздо шире и включает в себя множество других понятий, которые необходимо рассмотреть более подробно.
- Постоянный. Это значение, которое остается неизменным и не может быть изменено. Это может быть, например, число Пи.
- Операторы. Это модуль, необходимый для выполнения определенных операций. Excel предоставляет три типа операторов:
- Арифметика. Требуется для сложения, вычитания, деления и умножения нескольких чисел.
- Оператор сравнения. Необходимо проверить, соответствуют ли данные определенному условию. Он может возвращать одно значение: true или false.
- Текстовый оператор. Он всего один и необходим для объединения данных – &.
- Связь. Это адрес ячейки, из которой будут взяты данные, внутри формулы. Существует два типа ссылок: абсолютные и относительные. Первые не меняются, если формулу переместить в другое место. Относительные соответственно меняют ячейку на соседнюю или соответствующую. Например, если в какой-то ячейке указать ссылку на ячейку B2, а затем скопировать эту формулу в соседнюю справа, адрес автоматически изменится на C2. Ссылка может быть внутренней или внешней. В первом случае Excel обращается к ячейке, расположенной в той же книге. Во втором – в другом. То есть Excel может использовать в формулах данные, находящиеся в другом документе.
Как ввести данные в ячейку
Один из самых простых способов вставить формулу, содержащую функцию, — использовать Мастер функций. Для ее вызова необходимо нажать на значок fx немного левее строки формул (он расположен над таблицей, и в нем дублируется содержимое ячейки, если в ней нет формулы или формула отображается, если это так. Появится такое диалоговое окно.
Там вы можете выбрать категорию функции и непосредственно ту из списка, которую вы хотите использовать в конкретной ячейке. Там вы можете увидеть не только список, но и то, что делает каждая из функций.
Второй способ ввода формул — использование соответствующей вкладки на ленте Excel.
Здесь интерфейс другой, но механика та же. Все функции разделены на категории, и пользователь может выбрать ту, которая ему больше всего подходит. Чтобы увидеть, что делает каждая из функций, нужно навести на нее курсор мыши и подождать 2 секунды.
Вы также можете ввести функцию непосредственно в ячейку. Для этого нужно начать писать в ней символ ввода формулы (= =) и ввести имя функции вручную. Этот способ подойдет более опытным пользователям, знающим его наизусть. Позволяет сэкономить много времени.
После ввода первых букв отобразится список, в котором также можно выбрать нужную функцию и вставить ее. Если нет возможности использовать мышь, то перемещаться по этому списку можно с помощью клавиши TAB. Если да, то достаточно просто дважды щелкнуть по соответствующей формуле. После выбора функции появится подсказка, позволяющая ввести данные в правильной последовательности. Эти данные называются аргументами функции.
Если вы все еще используете версию Excel 2003, то в ней не предусмотрен раскрывающийся список, поэтому вам необходимо запоминать точное название функции и вводить данные по памяти. То же самое касается всех аргументов функции. К счастью, для опытного пользователя это не проблема.
Важно всегда начинать формулу со знака равенства, иначе Excel подумает, что ячейка содержит текст.
В этом случае формулой также будут считаться данные, начинающиеся со знака плюс или минус. Если после этого в ячейке будет текст, то Excel выдаст ошибку #ИМЯ?. Если заданы цифры или числа, то Excel попытается выполнить соответствующие математические действия (сложение, вычитание, умножение, деление). В любом случае, ввод формулы рекомендуется начинать со знака =, как это принято.
Аналогично можно начать писать функцию со знака @, который будет автоматически изменен. Этот способ ввода считается устаревшим и необходим для того, чтобы старые версии документов не потеряли часть функциональности.
Понятие аргументов функции
Почти все функции содержат аргументы, которыми могут быть ссылка на ячейку, текст, число и даже другая функция. Итак, если вы используете функцию ЭНЕЧЕТ, вам нужно будет указать цифры, которые будут проверяться. Будет возвращено логическое значение. Если это нечетное число, будет возвращено TRUE. Соответственно, если четное, то «ЛОЖЬ». Аргументы, как видно на скриншотах выше, вводятся в скобки и разделяются точкой с запятой. При этом, если используется английская версия программы, то разделителем служит обычная запятая.
Входной аргумент называется параметром. Некоторые функции вообще их не содержат. Например, чтобы получить текущее время и дату в ячейке, нужно написать формулу =ТАТА (). Как видите, если функция не требует ввода аргументов, скобки все равно нужно указывать.
Некоторые особенности формул и функций
Если данные в ячейке, на которую ссылается формула, редактируются, данные автоматически пересчитываются соответствующим образом. Предположим, у нас есть ячейка A1, в которую записана простая формула, содержащая обычную ссылку на ячейку. = Д1. Если вы измените в ней информацию, то в ячейке А1 отобразится то же значение. Аналогично и для более сложных формул, которые берут данные из определенных ячеек.
Важно понимать, что стандартные методы Excel не могут заставить ячейку вернуть свое значение в другую ячейку. В то же время эту задачу можно решить, используя макросы – подпрограммы, выполняющие определенные действия в документе Excel. Но это совсем другая тема, которая явно не для новичков, так как требует навыков программирования.
Понятие формулы массива
Это один из вариантов формулы, который вводится немного по-другому. Но многие не знают, что это такое. Итак, давайте сначала разберемся в значении этого термина. Гораздо проще это понять на примере.
Предположим, у нас есть формула СУММА, который возвращает сумму значений в определенном диапазоне.
Давайте создадим такой простой диапазон, прописав в ячейки A1:A5 числа от одного до пяти. Затем мы указываем функцию = СУММ (A1: A5) в ячейке В1. В результате там появится цифра 15.
Это уже формула массива? Нет, хотя он работает с набором данных и его можно назвать таковым. Давайте внесем некоторые изменения. Предположим, нам нужно добавить по одному к каждому аргументу. Для этого вам нужно сделать такую функцию:
=СУММ(A1:A5+1). Получается, что мы хотим добавить единицу к диапазону значений, прежде чем вычислять их сумму. Но даже в таком виде Excel не захочет этого делать. Ему нужно это показать с помощью формулы Ctrl+Shift+Enter. Формула массива отличается внешним видом и выглядит следующим образом:
{=СУММ(A1:A5+1)}
После этого в нашем случае будет введен результат 20.
Вводить фигурные скобки вручную нет смысла. Это ничего не даст. Наоборот, Excel даже не подумает, что это функция и просто текст вместо формулы.
Тем временем внутри этой функции были выполнены следующие действия. Сначала программа разлагает этот диапазон на составляющие. В нашем случае это 1,2,3,4,5. Затем Excel автоматически увеличивает каждое из них на единицу. Затем полученные числа суммируются.
Есть еще один случай, когда формула массива может делать то, чего не может стандартная формула. Например, у нас есть набор данных, указанный в диапазоне A1:A10. В стандартном случае будет возвращен ноль. Но предположим, что у нас такая ситуация, что ноль учитывать нельзя.
Давайте введем формулу, которая проверяет диапазон на предмет того, не равен ли он этому значению.
=МИН(ЕСЛИ(A1:A10<>0;A1:A10))
Здесь возникает ложное ощущение, что желаемый результат будет достигнут. Но это не так, потому что здесь нужно использовать формулу массива. В приведенной формуле будет проверяться только первый элемент, что нас, конечно, не устраивает.
Но если превратить это в формулу массива, выравнивание может быстро измениться. Теперь наименьшее значение будет 1.
Преимущество формулы массива состоит в том, что она может возвращать несколько значений. Например, вы можете транспонировать таблицу.
Таким образом, существует множество различных типов формул. Некоторые из них требуют более простого ввода, другие — более сложного. Формулы массивов могут быть особенно сложны для понимания новичками, но они очень полезны.