Формула эксель и: Использование ЕСЛИ с функциями И, ИЛИ и НЕ

Содержание

Как вычесть в Excel с формулой

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

Вычтите путем включения значений в формулу

Вам не нужно вводить какие-либо значения в ячейки электронной таблицы, чтобы вычитать числа. Вместо этого вы можете включить значения для вычитания в самой формуле. Сначала выберите ячейку, к которой нужно добавить формулу. Затем щелкните в функциональной панели Excel и введите «=», а затем значения, которые необходимо вычесть. Например, введите ‘= 25-5’ в функциональной панели и нажмите Enter. Ячейка формулы вернет значение 20.

Вычесть ссылки на ячейки электронной таблицы

Однако большинству пользователей Excel нужно будет вычитать числа, введенные в столбцы и строки таблицы. Чтобы вычесть значения ячеек, вам нужно вместо этого включить в формулу ссылки на строки и столбцы. Например, введите значения «345» и «145» в ячейки B3 и B4 в пустой электронной таблице Excel, как показано на снимке ниже.

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

Выберите ячейку B5 и нажмите на панели валют, чтобы ввести формулу. Введите формулу ‘= B3-B4’ и нажмите клавишу возврата. B5 теперь вернет значение 200, как показано ниже.

Вычесть одно число из каждого значения в диапазоне ячеек

Если вам нужно вычесть одно значение из каждого числа в диапазоне ячеек, вы можете скопировать формулу в другие ячейки. Например, введите значения «25», «35» и «55» в ячейки C3: C5. Выберите ячейку D3, введите формулу ‘= C3-5’ в функциональной панели и нажмите клавишу возврата. D3 теперь вычтет 5 из 25 в ячейке C3.

Скопируйте формулу в D3 в другие ячейки под ней, выбрав D3, щелкнув по нижнему правому углу ячейки и перетащив ее над ячейками ниже, как показано ниже. Теперь ячейки непосредственно под D3 также вычитают 5 из чисел в C4 и C5.

Вычесть общее значение диапазона ячеек из одного значения

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

Например, введите значения «45», «55» и «75» в ячейки с B7 по B9. Затем введите «200» в ячейку B11. Выделите ячейку B12, нажмите на панели валют и введите «= B11-SUM (B7: B9)» на панели формул. Ячейка B12 вернет значение 25, как показано ниже. Это эффективно вычитает общее количество клеток B7: B9 из 200 в ячейке B11.

Вычтите два или более общих значений диапазона ячеек

Вы также можете вычесть итоговые значения диапазона ячеек, не вводя сначала функции SUM в электронную таблицу. Вместо этого добавьте ссылки на диапазон ячеек в формулу и вычтите их. Например, введите числа «25», «15» и «35» в ячейки C7: C9. Теперь вы можете вычесть сумму диапазона ячеек C7: C9 из ранее введенного диапазона B7: B9.

Выберите C11 в качестве ячейки, к которой нужно добавить формулу, а затем введите ‘= SUM (B7: B9) -SUM (C7: C9)’ в функциональной панели. Нажмите Enter, чтобы добавить функцию в электронную таблицу, которая вернет значение 100 в C11. Таким образом, это вычитает диапазон ячеек C7: C9 из общего диапазона ячеек B7: B9, который в противном случае составляет 175 — 75.

Вычитание процентных значений из цифр

Чтобы вычесть процентное значение, например 50%, из числа, вам нужно будет ввести значение в ячейку с процентным форматом. Затем вы можете добавить формулу, которая вычитает процент от числа в другой ячейке. Например, введите значение «150» в ячейку E3. Вы должны преобразовать ячейку F3 в процентный формат, щелкнув правой кнопкой мыши по ячейке и выбрав Формат ячеек > Номер> процент > Хорошо, Введите «50» в F3, который будет в процентном формате, как показано ниже.

Теперь вы можете добавить формулу, которая вычитает 50% из 150. Нажмите на ячейку G3 и введите формулу E = E3- (F3 * E3) ’в функциональной панели. Ячейка G3 вернет значение 75, что составляет 50% от 150. Таким образом, формула вычла 75 из 150.

Вычитание значений из нескольких листов

Excel не ограничивает вас вычитанием значений в отдельных листах. Таким образом, вы можете вычесть номера ячеек в одном листе из тех, что в другом. Например, введите «55» в ячейку B14 на листе 1 электронной таблицы. Затем перейдите на вкладку Sheet2 в нижней части окна приложения, чтобы открыть пустую электронную таблицу. Введите «5» в ячейку B3 листа 2. Таким образом, эта формула вычтет B3 на листе 2 из B14 на листе 1.

Теперь нажмите B4 на Листе 2, чтобы добавить формулу в эту ячейку. Введите формулу ‘= Лист1! B14-Лист2! B3’ в функциональной панели Excel и нажмите Return. Ячейка B4 теперь вычитает 5 в B3 Листа 2 из значения B14 в Листе 1. Ячейка возвращает значение, как вы, наверное, догадались, 50.

Так вот некоторые из формул вычитания, которые вычитают значения в таблицах Excel. Вы можете скопировать приведенные выше формулы в электронные таблицы с помощью горячих клавиш Ctrl + C и Ctrl + V и при необходимости отредактировать ссылки на их ячейки. Посмотрите это руководство Tech Junkie, если вам нужно вычесть даты в Excel.

Как в Excel копировать и перемещать формулы — Трюки и приемы в Microsoft Excel

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

Далее необходимо рассчитать расходы за следующие месяцы (февраль и март). Вы можете просто создать новые формулы, но есть способ выполнить эту задачу гораздо проще и быстрее, скопировав формулу из ячейки С9. Рис. 1.3 демонстрирует результаты копирования содержимого ячейки С9 в D9.

Рис. 1.2. Суммирование расходов за январь

Как вы можете видеть, Excel автоматически корректирует диапазон вычисления, и теперь формула в D9 автоматически приняла вид =СУММ(D5:D8). Как же Excel узнал о необходимости корректировки диапазона? Для ответа на этот вопрос вам необходимо узнать об относительных ссылках в Excel.

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

Относительный формат ссылок (относительные адреса ячеек)

Когда вы используете указание на ячейку в формуле, Excel выбирает ячейку относительно текущей ячейки (в которой находится формула). Например, представьте, что у вас есть формула =А1*2 в ячейке A3. Для Excel такая формула звучит как: «Умножить содержимое ячейки, находящейся на две строчки выше, на число два». Это называется относительным форматом ссылки, и он является стандартным для Excel. Это означает, что если вы скопируете эту формулу в ячейку А4, относительный формат ссылки по-прежнему будет говорить: «Умножить содержимое ячейки, находящейся на две строчки выше, на число два», но формула, разумеется, при этом меняется на =А2*2, потому что ячейка А2 на две строки выше текущей ячейки А4.

Как вы уже видели из рис. 1.2 и 1.3, при таком копировании все идет достаточно хорошо. Но давайте рассмотрим другой пример. Например, вы хотите также запланировать дополнительные расходы фирмы и указываете, что они равны 3% (см. рис. 1.4). В ячейку С11 мы помещаем число 1.03 для указания дополнительных расходов. Теперь наша формула расходов за месяц становится =СУММ(С5:С8)*С11. Попробуем скопировать данную формулу для вычисления расходов за февраль. Теперь ячейка D9 показывает нам значение 0! (см. рис. 1.4).

Почему произошла данная ошибка? Изучение формулы в ячейке D9 показывает нам, что Excel автоматически сделал ее равной =СУММ(D5:D8)*D11. Разумеется, у нас нет никакого значения в ячейке D11. Поэтому и результат становится равным 0. Вся проблема здесь в относительной адресации ссылок. Когда происходит копирование формулы, Excel также меняет адрес С11 на D11. Для того чтобы устранить данную проблему, необходимо обратиться к абсолютной адресации ссылок.

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

Рис. 1.4. Введение дополнительного поля и появляющаяся ошибка

Абсолютный формат ссылок (абсолютные адреса ячеек)

Когда вы используете абсолютную адресацию ссылок, Excel использует ее для вычисления физического адреса ячейки. Для указания программе, что вы хотите использовать абсолютный формат ссылки на ячейку, используется знак доллара ($). или столбца. Например, формула =$A$1*2 будет интерпретирована Ехeel как «умножить содержимое ячейки А1 на два». Неважно, будете ли вы копировать или перемещать данную формулу, ничего не изменится. Адрес ячейки будет оставаться тем же самым.

Итак, для исправления ошибки на рис. 1.4 вы должны зафиксировать значение в ячейке C11. Для этого вам необходимо переписать самую первую формулу для января следующим образом: =СУММ(С5:С8)*$C$11. После данных изменений ВЫ можете спокойно копировать формулу в другие ячейки. Для февраля скопированная формула будет иметь вид =СУММ(D5:D8)*$С$11, что дает верный результат вычисления. Также вы должны знать о том, что вы можете вводить адреса ссылок, используя смешанный формат. В данном случае вы сможете зафиксировать или строку, или столбец.

Для фиксации строки используйте знак доллара перед числом, например В$6. Для фиксации столбца введите знак доллара перед буквой столбца, например $В6.

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

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

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

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

Синтаксис формулы Excel: объяснение языка функций и формул

По советам и рекомендациям по Excel от Pryor. com

Категории: Excel®
Теги: Синтаксис формулы Excel

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

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

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

Операторы, используемые в формулах и функциях

Математические операторы

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

Примеры:

Операторы сравнения

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

Пример:

Оператор конкатенации текста

Используйте амперсанд (&), чтобы соединить или объединить одну или несколько текстовых строк для создания единого фрагмента текста.

Пример:

Порядок приоритета, в котором Excel выполняет операции

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

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

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

2+3×4-5/6=?

Если бы эту задачу решали только слева направо, ответ был бы 2,5. Однако ваши намерения могли быть совсем другими. Добавление круглых скобок, чтобы показать, какие элементы должны быть вычислены в первую очередь, помогает.

(2+(3×4)-5)/6

Тот же самый набор чисел с добавленными скобками вычисляет совершенно другую сумму. Сначала вычисляется 3×4, всего 12. Затем прибавляется 2, чтобы получить 14, из которых вычитается 5, чтобы получить 9.. Наконец, 9 делится на 6, в сумме получается 1,5.

Теперь, когда у вас есть основы синтаксиса формул Excel — язык и грамматика — поэкспериментируйте с синтаксисом или ознакомьтесь с несколькими другими статьями, чтобы попрактиковаться в построении формул!

PRYOR+ 7 ДНЕЙ БЕСПЛАТНОГО ОБУЧЕНИЯ

Курсы по обслуживанию клиентов, Excel, управлению персоналом, лидерству,
ОСАГО и многое другое. Нет кредитной карты. Без комментариев. Индивиды и команды.

НАЧАТЬ ПРОБНЫЙ ПЕРИОД

Что такое функция Excel // Глоссарий Excel // PerfectXL

Наиболее часто используемые функции в Excel

Список всех функций Excel

Но Excel предлагает сотни других предустановленных формул:

ABS 900
ACCRINT
ACCRINTM
ACOS
ACOSH
ACOT
ACOTH
АДРЕС
AGGREGATE
AMORDEGRC
AMORLINC
AND
ARABIC
AREAS
ASC
ASIN
ASINH
ATAN
ATAN2
ATANH
AVEDEV
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
BAHTTEXT
BASE
BESSELI
BESSELJ
BESSELK
BESSELY
BETA. DIST
БЕТА.ОБР
БЕТАДРАСП
БЕТАИНВ
БИН2ДЕК
БИН2ШЕСТ.
БИН2ОКТ
БИНОМ.РАСП
БИНОМ.РАСП.ДИАПАЗОН0017 Bitor
Bitrshift
Bitxor
Call
Потолочный канал
Потолок. MATH
CELILE.PRECISE
Cell
Char
Chidist
Chiinv
Chisq.dist
CHISQ.DIST.RT
CHISQV.INSQU.DIST
CHISQ.DIST.RT
CHISQV.INSQR.DIST
.DIST.RT
CHISQR.DIST
.
CHITEST
CHOOSE
CLEAN
CODE
COLUMN
COLUMNS
COMBIN
COMBINA
COMPLEX
CONCATENATE
CONFIDENCE
CONFIDENCE.NORM
CONFIDENCE.T
CONVERT
CORREL
COS
COSH
COT
COTH
COUNT
COUNTA
COUNTBLANK
COUNTIF
COUNTIFS
COUPDAYBS
COUPDAYS
COUPDAYSNC
COUPNCD
COUPNUM
COUPPCD
COVAR
COVARIANCE.P
COVARIANCE.S
CRITBINOM
CSC
CSCH
CUBEKPIMEMBER
CUBEMEMBER
CUBEMEMBERPROPERTY
CUBERANKEDMEMBER
CUBESET
CUBESETCOUNT
КУБЗНАЧЕНИЕ
CUMIPMT
CUMPRINC
DATE
DATEVALUE
СРЕДНЕЕ
DAY
DAYS
DAYS360
DB
DBCS
DCOUNT
DCOUNTA
DDB
DEC2BIN
DEC2HEX
DEC2OCT
DECIMAL
DEGREES
DELTA
DEVSQ
DGET
DISC
DMAX
DMIN
DOLLAR
DOLLARDE
DOLLARFR
DPRODUCT
DSTDEV
DSTDEVP
DSUM
DURATION
DVAR
DVARP
EDATE
EFFECT
ENCODEURL
EOMONTH
ERF
ERF. PRECISE
ERFC
ERFC.PRECISE
ERROR.TYPE
EUROCONVERT
EVEN
EXACT
EXP 900DIST0017 EXPONDIST
F.DIST
F.DIST.RT
F.INV
F.INV.RT
F.TEST
FACT
FACTDOUBLE
FALSE
FDIST
FILTERXML
FIND, FINDB
FINV
FISHER
FISHERINV
FIXED
FLOOR
Пол. Мат
Пол. PRECISE
Прогноз
Formulatext
Частота
FTEST
FV
FVSCHEDULE
GAMMA
GAMMA.DIST
GAMMA.INV
GAMMADIST
GAMMAINVVVIVVVINV
GAMMA.INV

GAMMAINVVVIVVVIV
GAMMA.INV

GARMAINVVVVIVVVVIVVVIVVVVIVVVIVVVVIVVVVIVVVIVVVIVVVIVVVIVVVIV
.0017 GESTEP
GETPIVOTDATA
GROWTH
HARMEAN
HEX2BIN
HEX2DEC
HEX2OCT
HLOOKUP
HOUR
HYPERLINK
HYPGEOM.DIST
HYPGEOMDIST
IF
IFERROR
IFNA
IMABS
IMAGINARY
IMARGUMENT
IMCONJUGATE
IMCOS
IMCOSH
IMCOT
IMCSC
IMCSCH
IMDIV
IMEXP
IMLN
IMLOG10
IMLOG2
IMPOWER
IMPRODUCT
IMREAL
IMSEC
IMSECH
IMSIN
IMSINH
IMSQRT0017 IMSUB
IMSUM
IMTAN
INDEX
INDIRECT
INFO
INT
INTERCEPT
INTRATE
IPMT
IRR
ISBLANK
ISERR
ISERROR
ISEVEN
ISFORMULA
ISLOGICAL
ISNA
ISNONTEXT
ISNUMBER
ISO. CEILING
ISODD
ISOWEEKNUM
ISPMT
ISREF
ISTEXT
KURT
LARGE
LCM
LEFT, LEFTB
LEN, LENB
LINEST
LN
LOG
LOG10
LOGEST

7 LOGDIST.17RMNOIST.0017 LOGNORM.INV
LOGNORMDIST
LOOKUP
LOWER
MATCH
MAX
MAXA
MDETERM
MDURATION
MEDIAN
MID, MIDB
MIN
MINA
MINUTE
MINVERSE
MIRR
MMULT
MOD
MODE
MODE.MULT
MODE.SNGL
Месяц
MROUND
Многономиальный
MUNIT
N
NA
NEGBINOM.DIST
NEGBINOMMDIST
NETWERNDAYS
NETWERNDAYS.INTL
NOMINAL
NORM.DIST
NORM.INV
Норма.0017 NORMDIST
NORMINV
NORMSDIST
NORMSINV
NOT
NOW
NPER
NPV
NUMBERVALUE
OCT2BIN
OCT2DEC
OCT2HEX
ODD
ODDFPRICE
ODDFYIELD
ODDLPRICE
ODDLYIELD
OFFSET
OR
PDURATION
PEARSON
PERCENTILE
PERCENTILE. EXC
PERCENTILE .INC
PERCENTRANK
PERCENTRANK.EXC
PERCENTRANK.INC
PERMUT
PERMUTATIONA
PHI
PHONETIC
PI
PMT
POISSON
POISSON.DIST
Power
ppmt
Цена
Quediceisc
PRICEMAT
PROP
Продукт
Правильный
PV
Квартил
Квартиль.
Register.ID
Заменить, заменить
Rept
Справа, правая норм
Roman
Round
Rounddown
Roundup
ROW
ROWS
RRI
RSQ
RTD
SEARC0017 SECOND
SERIESSUM
SHEET
SHEETS
SIGN
SIN
SINH
SKEW
SKEW.P
SLN
SLOPE
SMALL
SQL.REQUEST
SQRT
SQRTPI
STANDARDIZE
STDEV
STDEV.P
STDEV.S
STDEVA
STDEVP
STDEVPA
STEYX
SUBSTITUTE
SUBTOTAL
SUM
SUMIF
SUMIFS
SUMPRODUCT
SUMSQ
SUMX2MY2
SUMX2PY2
SUMXMY2
SYD
T
T.