Как записать формулу в экселе: как написать формулу, как посчитать сумму, сложение с условием, счет строк и пр.

Содержание

Как написать формулы с помощью макросов в Excel подробное руководство

Итог: ознакомьтесь с 3 советами по написанию и созданию формул в макросах VBA с помощью этой статьи и видео.

Уровень мастерства: Средний

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

Поначалу написание формул в VBA может быть немного сложнее,
поэтому вот три совета, которые помогут сэкономить время и упростить процесс.

Совет № 1: Свойство Formula

Свойство Formula является членом объекта Range в VBA. Мы можем использовать его для установки / создания формулы для отдельной ячейки или диапазона ячеек.

Есть несколько требований к значению формулы, которые мы устанавливаем с помощью свойства Formula:

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

Вот простой пример формулы в макросе.

Sub Formula_Property()
  ' Формула представляет собой строку текста, заключенную в кавычки
  ' Начинается со знака =
  Range("B10").Formula = "=SUM(B4:B9)"
End Sub

Свойство Formula также можно использовать для чтения существующей формулы в ячейке.

Совет № 2: Используйте Macro Recorder

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

Вот шаги по созданию кода свойства формулы с помощью средства записи макросов.

Включите средство записи макросов (вкладка «Разработчик»> «Запись макроса»)
Введите формулу или отредактируйте существующую формулу.
Нажмите Enter, чтобы ввести формулу.
Код создается в макросе.

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

Sub Macro10()
' Используйте средство записи макросов для создания кода для сложных формул с
' специальны символы и относительные ссылки
  ActiveCell.FormulaR1C1 = "=""Total Sales: "" & TEXT(R[-5]C,""$#,###"")"
    
End Sub

Совет № 3: Нотация формулы стиля R1C1

Если вы используете средство записи макросов для формул, вы
заметите, что он создает код со свойством FormulaR1C1.

Нотация стиля R1C1 позволяет нам создавать как относительные (A1), абсолютные ($A$1), так и смешанные ($A1, A$1) ссылки в нашем макрокоде.

R1C1 обозначает строки и столбцы.

Относительные ссылки

Для относительных ссылок мы указываем количество строк и
столбцов, которые мы хотим сместить от ячейки, в которой находится формула.
Количество строк и столбцов указывается в квадратных скобках.

Следующее создаст ссылку на ячейку, которая на 3 строки выше
и на 2 строки справа от ячейки, содержащей формулу.

R[-3]C[2]

Отрицательные числа идут вверх по строкам и столбцам слева.

Положительные числа идут вниз по строкам и столбцам справа.

Абсолютные ссылки

Мы также можем использовать нотацию R1C1 для абсолютных ссылок. Обычно это выглядит как $A$2.

Для абсолютных ссылок мы НЕ используем квадратные скобки. Следующее создаст прямую ссылку на ячейку $A$2, строка 2, столбец 1

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

Проще всего использовать макро-рекордер, чтобы понять это.

Свойство FormulaR1C1 и свойство формулы

Свойство FormulaR1C1 считывает нотацию R1C1 и создает
правильные ссылки в ячейках. Если вы используете обычное свойство Formula с
нотацией R1C1, то VBA попытается вставить эти буквы в формулу, что, вероятно,
приведет к ошибке формулы.

Поэтому используйте свойство Formula, если ваш код содержит
ссылки на ячейки ($ A $ 1), свойство FormulaR1C1, когда вам нужны относительные
ссылки, которые применяются к нескольким ячейкам или зависят от того, где
введена формула.

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

Я надеюсь, что эти советы помогут. Пожалуйста, оставьте
комментарий ниже с вопросами или предложениями.

Как сделать формулу в Excel?

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

Создание формулы в Экселе начинается с написания знака равенства «=» в ячейке, в которой будет создаваться формула. Например, введя в ячейке следующую формулу «=2+3», после нажатия «Enter» мы увидим уже результат вычислений цифру «5», а не саму формулу. Но возможности Экселя не ограничиваются таким простым способом записи формулы. Для проведения вычислений в формуле можно указывать ссылки на другие ячейки, данные в которых должны использоваться для расчетов.

В качестве простого примера можно в разные ячейки записать цифры «2» и «3», и создать формулу в Excel в соседней ячейке, где вместо цифр будут ссылки на ячейки, в которых записаны необходимые нам цифры. В выбранную ячейку записываем знак равенства «=», что сразу даст понять программе о наличии формулы в данной ячейке, и записываем выражение сложения двух чисел, где вместо чисел будут адреса ячеек с числами. Для более удобного и безошибочного ввода адреса ячейки лучшим вариантом будет при наборе формулы в Экселе просто кликнуть указателем мыши на данную ячейку, и ее адрес сразу отобразится в создаваемой формуле. После этого прописываем необходимый арифметический оператор, и указываем адрес следующей ячейки таким же способом. В примере ниже знак равенства в ячейке набран через пробел, поэтому не воспринимается программой, как формула.

В Экселе редко производят единичные расчеты, как правило, формулу необходимо написать для обработки значений в целом столбце таблицы. Чтобы упростить ввод формул для обработки каждого значения, достаточно в Экселе сделать формулу один раз в одной ячейке и размножить ее, перетягивая на все необходимые ячейки. После написания формулы необходимо подвести курсор к правому нижнему углу ячейки, где курсор должен сменить свое отображение на черное перекрестие. После появления черного перекрестия необходимо нажать левую кнопку мыши и перетащить формулу вниз или вверх на необходимое количество ячеек. При этом в каждой последующей ячейке формула будет менять свой вид, заменяя адреса ячеек на адрес ячейки выше или ниже, в зависимости от направления перетаскивания формулы. Для более наглядного примера сделаем формулу в Экселе, с помощью которой у нас получится создать таблицу умножения. В самом первом столбце запишем десять раз в подряд цифру «2». В следующем столбце запишем числа от «1» до «10». В третьем столбце сделаем формулу в Excel, где будут перемножаться значения ячеек, и размножим ее на десять ячеек в столбце.

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

Для примера сначала сделаем формулу в Excel со смешанной ссылкой для нашей таблицы умножения, где столбец с цифрой «2» будет постоянным. Создается смешанная и абсолютная ссылка с помощью значка «$», который устанавливается слева от неменяющегося параметра. В нашем случае его необходимо указать перед адресом столбца. Откорректировав и размножив формулу на необходимые ячейки, скопируем нашу таблицу умножения и вставим немного правее.

Теперь создадим формулу в Экселе с адресом абсолютной ссылки, которой у нас будет самая первая ячейка с цифрой «2». Для этого подставим значок «$» перед адресом столбца и строки, и размножим нашу формулу. Все последующие цифры «2» из нашего столбца можно удалить.

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

Также статьи на сайте chajnikam.ru связанные с программой Excel:
Как добавить столбец в Экселе?
Как создание выпадающего списка в Excel
Создание таблиц в Excel
Объединение ячеек в Экселе

Как создавать формулы в Excel

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

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

Во-первых, любая формула Excel начинается со знака равенства (=). Итак, какую бы формулу вы ни собирались написать, начните с ввода = либо в ячейке назначения, либо в строке формул Excel. А теперь давайте подробнее рассмотрим, как можно создавать разные формулы в Excel.

Создание формул в Excel с использованием констант и операторов
Написание формул в Excel с использованием ссылок на ячейки
Создание формулы Excel с использованием определенных имен
Создание формулы в Excel с помощью функций

Как создать простую формулу Excel с использованием констант и операторов

В формулах Microsoft Excel константы — это числа, даты или текстовые значения, которые вы вводите непосредственно в формулу. Чтобы создать простую формулу Excel с использованием констант, просто сделайте следующее:

Выберите ячейку, в которую вы хотите вывести результат.
Введите символ равенства (=), а затем введите уравнение, которое вы хотите вычислить.
Нажмите клавишу Enter, чтобы завершить формулу. Сделанный!

Вот пример простой формулы вычитания в Excel:
=100-50

Как писать формулы в Excel, используя ссылки на ячейки

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

Например, если вы хотите вычесть значение в ячейке B2 из значения в ячейке A2, введите следующую формулу вычитания: =A2-B2

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

Примечание. По умолчанию Excel добавляет относительные ссылки на ячейки. Чтобы переключиться на другой тип ссылки, нажмите клавишу F4.

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

Как создать формулу Excel, используя определенные имена

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

Самый быстрый способ создать имя в Excel — выбрать ячейку (ячейки) и ввести имя непосредственно в поле 9.0003 Имя ящика . Например, вот как вы создаете имя для ячейки A2:

Профессиональный способ определить имя — через вкладку Формулы > группу Определенные имена или сочетание клавиш Ctrl+F3. Подробные инструкции см. в разделе Создание определенного имени в Excel.

В этом примере я создал следующие 2 имени:

доход для ячейки A2
расходы по ячейке Б2

А теперь, чтобы рассчитать чистый доход, вы можете ввести следующую формулу в любую ячейку на любом листе рабочей книги, в которой были созданы эти имена: =доходы-расходы

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

Например, если вы создадите имя 2015_продажи для ячеек A2:A100, вы можете найти общее количество этих ячеек с помощью следующей формулы СУММ: =СУММ(2015_продажи)

Конечно, вы можете получить того же результата, указав диапазон функции СУММ: =СУММ(A2:A100)

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

Как создавать формулы Excel с помощью функций

Функции Excel — это не что иное, как предопределенные формулы, выполняющие требуемые вычисления за кулисами.

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

Для получения дополнительной информации см. список наиболее популярных функций Excel с примерами формул и снимками экрана.

В электронных таблицах Excel вы можете создать формулу на основе функции двумя способами:

С помощью мастера функций
Написав формулу в ячейку или строку формул

Создайте формулу в Excel с помощью Мастера функций

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

1. Запустите мастер функций.

Чтобы запустить мастер, нажмите кнопку Кнопка Вставить функцию на вкладке Формулы > группа Библиотека функций или выберите функцию из одной из категорий:

Кроме того, вы можете нажать кнопку Вставить функцию слева от строки формул.

Или введите в ячейку знак равенства (=) и выберите функцию в раскрывающемся меню слева от строки формул. По умолчанию в раскрывающемся меню отображаются 10 последних использованных функций, чтобы перейти к полному списку, нажмите Дополнительные функции…

2. Найдите функцию, которую хотите использовать.

Когда появится мастер Вставить функцию , выполните следующие действия:

Если вы знаете имя функции, введите его в поле Поиск функции и нажмите Перейти .
Если вы не уверены, какую именно функцию вам нужно использовать, введите очень краткое описание задачи, которую вы хотите решить, в поле Поиск функции и нажмите Перейти . Например, вы можете ввести что-то вроде этого: « сумма ячеек» или « количество пустых ячеек» .
Если вы знаете, к какой категории относится функция, щелкните маленькую черную стрелку рядом с Выберите категорию и выберите одну из 13 перечисленных категорий. Функции, принадлежащие к выбранной категории, появятся в списке Выберите функцию

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

Найдя нужную функцию, выберите ее и нажмите OK.

3. Укажите аргументы функции.

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

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

Краткое объяснение текущего выбранного аргумента отображается прямо под описанием функции. Для получения более подробной информации нажмите кнопку Справка по этой функции ссылка внизу.

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

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

4. Заполните формулу.

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

Напишите формулу непосредственно в ячейке или строке формул

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

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

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

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

Совет. Если вы не очень хорошо знакомы с синтаксисом функции, щелкните имя функции, и сразу же появится раздел справки Excel .

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

Вас также может заинтересовать

Excel 2010: создание простых формул

Урок 6: создание простых формул

/en/excel2010/saving/content/

Введение

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

Простые формулы

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

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

Чтобы максимально использовать возможности Excel, важно понимать, как создавать простые формулы 9) для показателей.

При написании формул для Excel важно помнить, что все формулы должны начинаться со знака равенства (=). Это связано с тем, что ячейка содержит (или равна) формулу и ее значение.

Чтобы создать простую формулу в Excel:

Выберите ячейку, в которой появится ответ (например, B4, ).
Выбор ячейки B4
Введите знак равенства (=) .
Введите формулу, которую Excel должен вычислить ( 75/250 , например).
Ввод формулы в B4
Нажмите Введите . Формула будет рассчитана, и значение отобразится в ячейке.
Результат в B4

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

Создание формул со ссылками на ячейки

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

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

Выберите ячейку, в которой появится ответ (например, B3, ).
Выбор ячейки B3
Введите знак равенства (=) .
Введите адрес ячейки, которая содержит первое число в уравнении (например, B1, ).
Ввод формулы в B3
Введите нужный оператор для формулы. Например, введите знак добавления (+) .
Введите адрес ячейки, которая содержит второе число в уравнении (например, B2, ).
Ввод формулы в B3
Нажмите Введите . Формула будет рассчитана, и значение отобразится в ячейке.
Результат в B3

Если вы измените значение в B1 или B2, сумма будет автоматически пересчитана.

Результат в B3

Excel не всегда сообщит вам , если ваша формула содержит ошибку, поэтому вы должны проверить все свои формулы. Чтобы узнать, как это сделать, вы можете прочитать урок «Перепроверьте свои формулы» из нашего учебника по формулам Excel.

Чтобы создать формулу методом «укажи и щелкни»:

Выберите ячейку, в которой появится ответ ( B4, например).
Выбор ячейки B4
Введите знак равенства (=) .
Щелкните первую ячейку , которая будет включена в формулу (например, A3, ).
Щелчок по ячейке A3
Введите нужный оператор для формулы. Например, введите знак умножения (*) .
Щелкните следующую ячейку в формуле (например, B3, ).
Щелчок по ячейке B3
Нажмите Введите . Формула будет рассчитана, и значение отобразится в ячейке.
Результат в B4

Чтобы изменить формулу:

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

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

Вызов!

Откройте существующую книгу Excel 2010 . Если хотите, можете использовать этот пример.