Инструмент для ввода функций в MS Excel
Обновлено: 20.11.2024
Формулы — это уравнения, которые выполняют вычисления со значениями на вашем листе. Все формулы начинаются со знака равенства (=). Вы можете создать простую формулу, используя константу и оператор вычисления. Например, формула =5+2*3 умножает два числа, а затем добавляет число к результату.
Если вы хотите ссылаться на переменные вместо констант, вы можете использовать значения ячеек, например, =A1+A2. Если вы работаете с длинными столбцами данных или данными, расположенными в разных частях листа или на другом листе, вы можете использовать диапазон, например =СУММ(A1:A100)/СУММ(B1:B100), который представляет собой деление суммы первых сотен чисел в столбце A на сумму этих чисел в столбце B. Когда ваша формула ссылается на другие ячейки, каждый раз, когда вы изменяете данные в любой из ячеек, Excel автоматически пересчитывает результаты. .
Вы также можете создать формулу, используя функцию, предопределенную формулу, которая упрощает ввод вычислений.
Все формулы начинаются со знаков равенства.
Константы, такие как числа или текстовые значения, можно вводить непосредственно в формулу.
Операторы определяют тип вычислений, которые выполняет формула. Например, оператор ^ (вставка) возводит число в степень, а оператор * (звездочка) умножает числа.
Функции – это готовые формулы, которые можно использовать отдельно или как часть более длинной формулы. Каждая функция имеет определенный синтаксис аргумента.
Значения ячеек позволяют ссылаться на ячейку Excel, а не на конкретное значение внутри ячейки, чтобы содержимое ячейки могло изменяться без необходимости изменения функции, которая ссылается на ячейку.
Введите формулу, которая ссылается на значения в других ячейках
На листе, содержащем столбцы чисел, щелкните ячейку, в которой должны появиться результаты формулы.
Введите знак равенства =
Нажмите на первую ячейку, которую вы хотите включить в расчет.
Введите оператор. Оператор — это тип вычисления, который выполняет формула. Например, оператор * (звездочка) умножает числа. В этом примере используйте оператор / (косая черта) для деления. На этом этапе ваша формула должна выглядеть так:
Нажмите следующую ячейку, которую вы хотите включить в расчет. Теперь ваша формула должна выглядеть так:
Результат вычисления появится в ячейке.
Совет. Чтобы быстро применить формулу к следующим ячейкам в столбце, дважды щелкните маркер заполнения в первой ячейке, содержащей формулу.
Введите формулу, содержащую функцию
На листе, содержащем диапазон чисел, щелкните пустую ячейку, в которой должны появиться результаты формулы.
Введите знак равенства и функцию, например =MIN. МИН находит наименьшее число в диапазоне ячеек.
Введите открывающую скобку, выберите диапазон ячеек, которые вы хотите включить в формулу, а затем введите закрывающую скобку.
В нашем примере функция MIN возвращает 11, наименьшее число в ячейках с A1 по C4.
Когда вы вводите формулу в ячейку, она также отображается в строке формул.
Используйте ярлыки в строке формул, чтобы упростить создание формул:
Выберите, чтобы проверить формулу. Если ошибок нет, в ячейке отобразится результат формулы. Если есть ошибки, вы увидите . Наведите указатель мыши на него, чтобы увидеть объяснение проблемы, или выберите раскрывающееся меню, чтобы получить дополнительную помощь по устранению неполадок.
Выберите, чтобы вернуться к предыдущей формуле.
Чтобы выбрать функцию, используйте список функций.
При выборе функции открывается построитель формул с дополнительной информацией о функции.
Microsoft Excel распознает два очень разных типа дополнительных функций: команды и функции.
Команды
В Excel команды имеют следующие характеристики:
Они выполняют действия так же, как и пользователи.
Они могут делать все, что может делать пользователь (с учетом ограничений используемого интерфейса), например изменять настройки Excel, открывать, закрывать и редактировать документы, запускать перерасчеты и т. д.
Их можно настроить так, чтобы они вызывались при возникновении определенных перехваченных событий.
Они могут отображать диалоговые окна и взаимодействовать с пользователем.
Их можно связать с объектами управления, чтобы они вызывались, когда над этим объектом выполняется какое-либо действие, например щелчок левой кнопкой мыши.
Они никогда не вызываются Excel во время пересчета.
Они не могут вызываться функциями во время пересчета.
Функции
Функции в Excel делают следующее:
Обычно они принимают аргументы и всегда возвращают результат.
Их можно ввести в одну или несколько ячеек как часть формулы Excel.
Их можно использовать в определенных определениях имен.
Их можно использовать в выражениях ограничений и порогов условного форматирования.
Их можно вызывать с помощью команд.
Они не могут вызывать команды.
Excel проводит дополнительное различие между определяемыми пользователем функциями рабочего листа и определяемыми пользователем функциями, предназначенными для работы с листами макросов. Excel не ограничивает пользовательские функции листа макросов только для использования на листах макросов: эти функции можно использовать везде, где можно использовать обычные функции листа.
Функции рабочего листа
Для функций листа Excel верно следующее:
Они не могут получить доступ к информационным функциям листа макросов.
Они не могут получить значения невычисленных ячеек.
Они могут быть написаны и зарегистрированы как потокобезопасные, начиная с Excel 2007.
Функции макросов
Следующее верно для функций листа макросов Excel:
Они могут получить доступ к информационным функциям листа макросов.
Они могут получать значения невычисленных ячеек, включая значения вызывающих ячеек.
Начиная с Excel 2007 они не считаются потокобезопасными.
Как Excel обрабатывает определяемую пользователем функцию (UDF), что она разрешает делать и как пересчитывает функцию, все определяется при регистрации функции. Если функция зарегистрирована как функция рабочего листа, но пытается сделать что-то, что может сделать только функция макролиста, операция завершится ошибкой. Начиная с Excel 2007, если функция листа, зарегистрированная как потокобезопасная, пытается вызвать функцию листа макроса, снова происходит сбой операции.
Excel рассматривает определяемые пользователем функции Microsoft Visual Basic для приложений (VBA) как функции, эквивалентные листу макросов, поскольку они могут получать доступ к информации о рабочей области и значениям невычисленных ячеек и не считаются потокобезопасными, начиная с Excel 2007.
Состояния Excel
Excel может находиться в одном из нескольких состояний в любой момент времени в зависимости от действий пользователя, внешнего процесса, перехваченного события, запускающего макрос, или временного служебного события Excel, такого как автосохранение.
Состояния, с которыми сталкивается пользователь, следующие:
Состояние готовности: команды или макросы не выполняются. Диалоговые окна не отображаются. Никакие ячейки не редактируются, и пользователь не выполняет операцию вырезания/копирования и вставки. Ни один встроенный объект не имеет фокуса.
Режим редактирования: пользователь начал вводить допустимые символы ввода в разблокированную или незащищенную ячейку или нажал F2 в одной или нескольких разблокированных или незащищенных ячейках.
Режим вырезания/копирования и вставки: пользователь вырезал или скопировал ячейку или диапазон ячеек и еще не вставил их или вставил их с помощью специального диалогового окна вставки, которое позволяет выполнять несколько операций вставки.
Режим точки: пользователь редактирует формулу и выбирает ячейки, адреса которых добавляются в редактируемую формулу.
Пользователь может отключить режимы редактирования, указания и вырезания/копирования, нажав клавишу ESC, которая вернет Excel в состояние готовности. Другие события могут очищать эти состояния, например следующие:
Пользователь открывает встроенное диалоговое окно.
Пользователь инициирует перерасчет.
Пользователь запускает команду.
Excel выполняет операцию автосохранения.
Событие таймера перехвачено.
Последний пример важен для разработчиков надстроек. Вы должны учитывать влияние обычного удобства использования Excel, когда часто устанавливаются и выполняются ловушки событий таймера.Если это важная часть функциональности вашей надстройки, вы должны предоставить пользователям легкодоступный способ ее приостановки, чтобы они могли нормально вырезать/копировать и вставлять, когда им это нужно.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше
Используя инструменты анализа "что, если" в Excel, вы можете использовать несколько различных наборов значений в одной или нескольких формулах для изучения всех различных результатов.
Например, вы можете выполнить анализ "что, если" и создать два бюджета, каждый из которых предполагает определенный уровень дохода. Или вы можете указать результат, который вы хотите получить с помощью формулы, а затем определить, какие наборы значений будут давать этот результат. Excel предоставляет несколько различных инструментов, которые помогут вам выполнить тип анализа, соответствующий вашим потребностям.
Обратите внимание, что это всего лишь обзор этих инструментов. Для каждого из них есть ссылки на разделы справки.
Анализ «что, если» — это процесс изменения значений в ячейках, чтобы увидеть, как эти изменения повлияют на результаты формул на листе.
В Excel входят три типа инструментов анализа «что, если»: «Сценарии», «Поиск цели» и «Таблицы данных». Таблицы сценариев и данных принимают наборы входных значений и определяют возможные результаты. Таблица данных работает только с одной или двумя переменными, но может принимать множество различных значений для этих переменных. Сценарий может иметь несколько переменных, но не более 32 значений. Поиск цели работает иначе, чем сценарии и таблицы данных, поскольку он принимает результат и определяет возможные входные значения, которые приводят к этому результату.
В дополнение к этим трем инструментам вы можете установить надстройки, помогающие выполнять анализ «что, если», например надстройку «Поиск решения». Надстройка «Поиск решения» похожа на «Поиск цели», но может вмещать больше переменных. Вы также можете создавать прогнозы с помощью маркера заполнения и различных команд, встроенных в Excel.
Для более сложных моделей можно использовать надстройку Analysis ToolPak.
Сценарий — это набор значений, которые Excel сохраняет и может автоматически заменять в ячейках на листе. Вы можете создавать и сохранять разные группы значений на листе, а затем переключаться на любой из этих новых сценариев для просмотра других результатов.
Например, предположим, что у вас есть два сценария бюджета: наихудший и наилучший. Вы можете использовать диспетчер сценариев для создания обоих сценариев на одном рабочем листе, а затем переключаться между ними. Для каждого сценария вы указываете ячейки, которые изменяются, и значения, используемые для этого сценария. Когда вы переключаетесь между сценариями, ячейка результата изменяется, чтобы отразить различные меняющиеся значения ячейки.
<р>1. Изменение ячеек <р>1. Изменение ячеекЕсли у нескольких человек есть определенная информация в отдельных книгах, которую вы хотите использовать в сценариях, вы можете собрать эти книги и объединить их сценарии.
После того как вы создали или собрали все необходимые вам сценарии, вы можете создать сводный отчет по сценариям, который включает информацию из этих сценариев. Отчет о сценарии отображает всю информацию о сценарии в одной таблице на новом листе.
Примечание. Отчеты о сценариях не пересчитываются автоматически. Если вы измените значения сценария, эти изменения не будут отображаться в существующем сводном отчете. Вместо этого вы должны создать новый сводный отчет.
Если вы знаете результат, который хотите получить от формулы, но не знаете, какое входное значение требуется формуле для получения этого результата, вы можете использовать функцию поиска цели. Например, предположим, что вам нужно занять немного денег. Вы знаете, сколько денег вы хотите, как долго вы хотите, чтобы погасить кредит, и сколько вы можете позволить себе платить каждый месяц. Вы можете использовать Goal Seek, чтобы определить, какую процентную ставку вы должны обеспечить, чтобы достичь своей цели по кредиту.
Ячейки B1, B2 и B3 содержат значения суммы кредита, срока и процентной ставки.
Ячейка B4 отображает результат формулы =PMT(B3/12,B2,B1).
Примечание. Поиск цели работает только с одним входным значением переменной. Если вы хотите определить более одного входного значения, например сумму кредита и сумму ежемесячного платежа по кредиту, вместо этого следует использовать надстройку «Поиск решения». Дополнительные сведения о надстройке «Поиск решения» см. в разделе «Подготовка прогнозов и расширенных бизнес-моделей» и перейдите по ссылкам в разделе «См. также».
Если у вас есть формула, в которой используются одна или две переменные, или несколько формул, в которых используется одна общая переменная, вы можете использовать таблицу данных, чтобы просмотреть все результаты в одном месте. Использование таблиц данных позволяет легко изучить ряд возможностей с первого взгляда.Поскольку вы фокусируетесь только на одной или двух переменных, результаты легко читать и делиться ими в табличной форме. Если для книги включен автоматический пересчет, данные в таблицах данных немедленно пересчитываются; в результате у вас всегда будут свежие данные.
Ячейка B3 содержит входное значение.
Ячейки C3, C4 и C5 представляют собой значения, которые Excel заменяет на основе значения, введенного в ячейку B3.
Таблица данных не может содержать более двух переменных. Если вы хотите проанализировать более двух переменных, вы можете использовать сценарии. Хотя таблица данных ограничена только одной или двумя переменными, она может использовать любое количество различных значений переменных. Сценарий может иметь до 32 различных значений, но вы можете создать столько сценариев, сколько захотите.
Если вы хотите подготовить прогнозы, вы можете использовать Excel для автоматического создания будущих значений на основе существующих данных или для автоматического создания экстраполированных значений на основе расчетов линейного тренда или тренда роста.
Вы можете заполнить ряд значений, которые соответствуют простой линейной тенденции или тенденции экспоненциального роста, используя маркер заполнения или команду "Серия". Чтобы расширить сложные и нелинейные данные, вы можете использовать функции рабочего листа или инструмент регрессионного анализа в надстройке Analysis ToolPak.
Несмотря на то, что поиск цели может учитывать только одну переменную, с помощью надстройки "Поиск решения" вы можете проецировать назад больше переменных. Используя Solver, вы можете найти оптимальное значение формулы в одной ячейке, называемой целевой ячейкой, на листе.
Поиск решения работает с группой ячеек, связанных с формулой в целевой ячейке. Решатель корректирует значения в изменяющихся ячейках, которые вы указываете (называемых регулируемыми ячейками), чтобы получить результат, который вы указываете из формулы целевой ячейки. Вы можете применить ограничения, чтобы ограничить значения, которые Solver может использовать в модели, и ограничения могут ссылаться на другие ячейки, которые влияют на формулу целевой ячейки.
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.
Вместо калькулятора используйте Microsoft Excel для расчетов!
Вы можете вводить простые формулы для сложения, деления, умножения и вычитания двух или более числовых значений. Или используйте функцию автосуммы, чтобы быстро суммировать ряд значений, не вводя их вручную в формулу. Создав формулу, вы можете скопировать ее в соседние ячейки — не нужно создавать одну и ту же формулу снова и снова.
Вычитание в Excel
Умножение в Excel
Разделить в Excel
Подробнее о простых формулах
Все записи формул начинаются со знака равенства (=). Для простых формул просто введите знак равенства, за которым следуют числовые значения, которые вы хотите вычислить, и математические операторы, которые вы хотите использовать: знак плюс (+) для добавления, знак минус (-) для вычитания, звездочка ( *) для умножения и косая черта (/) для деления. Затем нажмите клавишу ВВОД, и Excel мгновенно рассчитает и отобразит результат формулы.
Например, если вы введете =12,99+16,99 в ячейку C5 и нажмете клавишу ВВОД, Excel вычислит результат и отобразит 29,98 в этой ячейке.
Формула, которую вы вводите в ячейку, остается видимой в строке формул, и вы можете видеть ее всякий раз, когда эта ячейка выбрана.
Использовать автосумму
Самый простой способ добавить формулу СУММА на лист — использовать автосуммирование. Выберите пустую ячейку непосредственно над или под диапазоном, который вы хотите суммировать, и на вкладках Главная или Формула ленты щелкните Автосумма > Сумма. AutoSum автоматически определит диапазон для суммирования и построит для вас формулу. Это также работает по горизонтали, если вы выбираете ячейку слева или справа от диапазона, который вам нужно суммировать.
Примечание. Автосумма не работает для несмежных диапазонов.
Автосумма по вертикали
На рисунке выше видно, что функция автосуммирования автоматически определяет ячейки B2:B5 как диапазон для суммирования. Все, что вам нужно сделать, это нажать ENTER, чтобы подтвердить это. Если вам нужно добавить/исключить больше ячеек, вы можете удерживать клавишу Shift + клавишу со стрелкой по вашему выбору, пока ваш выбор не будет соответствовать тому, что вы хотите. Затем нажмите Enter, чтобы завершить задачу.
Руководство по функциям Intellisense: плавающий тег SUM(number1,[number2], …) под функцией является руководством по Intellisense. Если щелкнуть SUM или имя функции, оно изменится на синюю гиперссылку на раздел справки для этой функции. Если щелкнуть отдельные функциональные элементы, их репрезентативные части в формуле будут выделены. В этом случае будут выделены только B2:B5, так как в этой формуле есть только одна ссылка на число. Тег Intellisense будет отображаться для любой функции.
Автосумма по горизонтали
Подробнее читайте в статье о функции СУММ.
Не переписывайте одну и ту же формулу
После того как вы создали формулу, вы можете скопировать ее в другие ячейки — нет необходимости переписывать ту же самую формулу. Вы можете либо скопировать формулу, либо использовать маркер заполнения, чтобы скопировать формулу в соседние ячейки.
Например, когда вы копируете формулу из ячейки B6 в ячейку C6, формула в этой ячейке автоматически изменяется, чтобы обновить ссылки на ячейки в столбце C.
При копировании формулы убедитесь, что ссылки на ячейки указаны правильно. Ссылки на ячейки могут измениться, если они имеют относительные ссылки. Дополнительные сведения см. в разделе Копирование и вставка формулы в другую ячейку или лист.
Читайте также: