Формула всд в excel выдает ошибку число: Функция ВСД — Служба поддержки Office

Мне нужно перенести функцию IRR, найденную в Excel и VB, в ActionScript. Excel IRR формула Есть идеи, как найти source для таких функций? есть ли у кого-нибудь такая реализация на каком-нибудь…

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

Я пытаюсь вычислить IRR в приложении .NET. Приложение основано на листе Excel, который также вычисляет IRR. приложение .NET вызывает функцию IRR в пространстве имен Microsoft.VisualBasic, в то время…

Я хотел бы знать, известен ли численный метод, используемый для вычисления функции IRR в Excel.

Внутренняя норма прибыли (IRR) или экономическая норма прибыли (ERR) — это норма прибыли, используемая в капитальном бюджете для измерения и сравнения прибыльности инвестиций. Я написал некоторый…

Чтобы обеспечить допустимые входные данные для функций NPV и IRR, я пытаюсь создать диапазон значений годовой четной доходности за заданное количество лет. Хотя вопрос также справедлив для любой…

У меня есть субподрядчик, который динамически выбирает диапазон ячеек, а затем передает этот диапазон во внутреннюю формулу нормы прибыли. Мне нужно добавить одну ячейку в начало диапазона, чтобы…

Я только что понял, что вы можете ссылаться на несмежные диапазоны в IRR excel formula, заключив коллекцию ссылок в круглые скобки (т. е. =IRR( (C18:C62,$B$1) ). это полезная функция, но из…

У меня есть следующие годовые денежные потоки: w=np.array([ -56501, -14918073, -1745198, -20887403, -9960686, -31076934, 0, 0, 11367846, 26736802, -2341940, 20853917, 22166416, 19214094, 23056582,…

Я пытаюсь вставить диапазон ячеек в одну ячейку в Excel. Sub MergeSelectedRowData2() Dim MyCell As Range Dim strMerge As String For Each MyCell In Sheets(Sheet1).Range(A1:B2) If strMerge = Then…

Какие существуют обозначения ошибок и способы их исправления? — MS Excel

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

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

• проверить формулу, если вычисляется число дней между двумя датами;
• если формула не содержит ошибок, то необходимо изменить формат ячейки и перейти, например, с формата «Дата и время» на «Общий» или «Числовой» формат.

Эти сообщение об использовании текста вместо числа или логического значения (ИСТИНА или ЛОЖЬ). То есть Excel такой плейбой и не может преобразовать данный текст в ячейке в правильный тип данных.
Необходимо убедиться, что формула или функция ссылается на те ячейки, которые содержат действительные значения.
Например, если в ячейке A2 содержится число, а в ячейке A3 содержится текст, то в ячейке А1 с формулой =A2+A3 будет отображаться #ЗНАЧ!.

Эти сообщение, что в ячейке происходит деление числа на 0 (ноль) или же используются ссылки на пустую ячейку.

• В окне открытого листа выделите ячейку с данной ошибкой и нажмите клавишу F2.
• Когда в ячейке отобразится сама формула или функция, а также станут выделенными все ячейки, связанные ссылками с данной формулой или ячейкой, внимательно проверьте значения в выделенных ячейках и при необходимости внесите коррективы в формулу или измените ссылки на пустые ячейки.
• Нажмите Enter или кнопку «Ввод» на строке формул.

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

Эти символы сообщают, что в формуле используется несуществующее имя или неправильный оператор.

1 вариант

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

• В окне открытого листа перейдите к вкладке «Формулы» и в группе «Определенные имена» щелкните по кнопке «Диспетчер имен».
• В окне «Диспетчер имен» просмотрите, присутствует ли данное имя в списке.

Если данное имя отсутствует, то необходимо добавить его согласно инструкции «Как присвоить имя ячейке или диапазону ячеек?».

2 вариант

Если существует ошибка в написании имени, то необходимо проверить его орфографию.

• В окне открытого листа нажмите клавишу F3.
• В окошке «Вставка имени» выберите в списке имя нужное имя и нажмите кнопку «ОК».
• Внесите исправления (при необходимости) в формуле, которая отобразится в соответствующей ячейке.
• Для закрепления нажмите клавишу Enter.

3 вариант

Если в формуле используется функция с ошибкой в написании.
Например, СУМ(A1:А10) вместо СУММ(A1:А10).

• В окне открытого листа выделите ячейку с ошибкой в написании функции.
• Раскройте меню кнопки «Источник ошибки» рядом с данной ячейкой.
• В списке команд выберите пункт «Изменить в строке формул».
• На строке формул в окошке имени отобразится правильно написание нужной формул, согласно которому и измените ошибочное написание.
• Закрепите результат щелчком по клавише Enter.

4 вариант

Если в формулу введен текст, который не заключен в двойные кавычки, то необходимо проверить все текстовые записи в формуле и заключить их в двойные кавычки. Иначе Excel будет пытаться распознать данный текст как имя диапазона ячеек, хотя это и не предполагалось.
Например, СУММ(A1 А10) вместо СУММ(A1:А10).

5 вариант

Если в ссылке на диапазон ячеек пропущено двоеточие, то для исправления необходимо в формуле во всех подобных ссылках проверить знак двоеточия и исправить по мере необходимости.
Например, СУММ(A1 А10) вместо СУММ(A1:А10).

6 вариант

Если в формулу включена ссылка на значения ячеек других листов или книг, при этом имя не заключено в ординарные кавычки, то необходимо это имя заключить в апострофы ( “ ).

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

1 вариант

Если в формулу были введены недостающие данные, а также #Н/Д или НД(), то #Н/Д необходимо заменить новыми данными.

Обозначение #Н/Д вводится в ячейки, данные из которых еще не доступны.

2 вариант

Если в функциях ГПР, ПРОСМОТР, ПОИСКПОЗ или ВПР указывается неверное значение аргумента «искомое_значение» (например, ссылка на диапазон ячеек, что недопустимо), то необходимо соответственно указать ссылку только на нужную ячейку.

3 вариант

Если не заданы необходимые аргументы стандартной функции листа, то нужно ввести все необходимые соответствующие аргументы функции.

4 вариант

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

5 вариант

Если для просмотра значений в несортированной таблице используются функции ВПР, ГПР или ПОИСКПОЗ, для которых по умолчанию сведения для просмотра таблиц должны располагаться в возрастающем порядке.
В функциях ВПР и ГПР содержится аргумент «интервальный _просмотр», позволяющий искать определенное значение и в несортированной таблице. Но при этом, чтобы отыскать определенное значение, аргумент «интервальный_просмотр» должен иметь значение ЛОЖЬ.
В функции ПОИСКПОЗ содержится аргумент «тип_сопоставления», позволяющий сортировать данные для поиска. Если же соответствующее значение отыскать невозможно, то рекомендуется задать аргумент «тип_сопоставления» равный 0.

6 вариант

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

7 вариант

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

Эти символы сообщают о неверной ссылке на ячейку.

1 вариант

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

2 вариант

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

Интерфейс OLE (Object Linking and Embedding — связывание и внедрение объектов) поддерживается множеством различных программ и используется для помещения документа, созданного в одной программе, в другую программу. Например, можно вставить документ Word в книгу Excel и наоборот.

3 вариант

Если используется ссылка на недоступный объект DDE (Dynamic Data Exchange – динамический обмен данными), например «system», то необходимо проверить, что используется правильный раздел DDE.

4 вариант

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

Это сообщение об использовании неправильных числовых значений в формуле или функции.

1 вариант

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

2 вариант

Если в функции с итерацией (подбором параметров), например «ВСД» или «СТАВКА», невозможно найти результат, то необходимо попробовать иное начальное приближение или изменить число итераций.

3 вариант

Если в результате вычисления формулы получается число, которое слишком велико или, наоборот, слишком мало, чтобы оно могло быть отображено в Excel, то необходимо изменить формулу и добиться, чтобы результат находился в диапазоне от 1*10307 до 1*10307.

Эти сообщение об отсутствии общих ячеек, когда задано пере
сечение двух областей.

1 вариант

Если используется ошибочный оператор диапазона, то необходимо внести исправления, а именно:

• для обозначения ссылки на непрерывный диапазон ячеек используется двоеточие (:) в качестве разделителя между начальной и конечной ячейкой диапазона. Например, СУММ(С1:С20).
• для обозначения ссылки на два непересекающихся диапазона используется оператор объединения – точкой с запятой (;). Например, СУММ(С1:С20;D1:D20).

2 вариант

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

Как исправить ошибку # ЧИСЛО! ошибка

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

Это часто происходит, когда вы ввели числовое значение, используя тип данных или числовой формат, который не поддерживается в разделе аргументов формулы. Например, вы не можете ввести значение 1 000 долларов в денежном формате, поскольку знаки доллара используются в качестве индикаторов абсолютной ссылки, а запятые — в качестве разделителей аргументов в формулах.Чтобы избежать #NUM! ошибка, вместо этого введите значения в виде неформатированных чисел, например 1000 .

Excel также может отображать # ЧИСЛО! ошибка при:

• В формуле используется функция, которая выполняет итерацию, например IRR или RATE, и она не может найти результат.

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

  1. Выберите Файл > Опции .Если вы используете Excel 2007, выберите Microsoft Office Button . > Параметры Excel .

  2. На вкладке Формулы в разделе Параметры расчета установите флажок Включить итеративный расчет .

  3. В поле Максимальное количество итераций введите количество раз, которое Excel должен пересчитать.Чем больше количество итераций, тем больше времени нужно Excel для расчета рабочего листа.

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

• Результатом формулы является слишком большое или слишком маленькое число для отображения в Excel.

  Чтобы исправить это, измените формулу так, чтобы ее результат находился в диапазоне от -1 * 10307 до 1 * 10307.

  Совет: Если в Excel включена проверка ошибок, вы можете нажать рядом с ячейкой, в которой отображается ошибка. Щелкните Показать шаги расчета , если он доступен, и выберите разрешение, подходящее для ваших данных.

У вас есть конкретный вопрос по функциям?

Задайте вопрос на форуме сообщества Excel

Помогите нам улучшить Excel

У вас есть предложения о том, как мы можем улучшить следующую версию Excel? Если да, ознакомьтесь с темами на сайте Excel User Voice

См. Также

Обзор формул в Excel

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

Обнаруживать ошибки в формулах

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Есть ошибка формулы Excel? Вот как это исправить

Итак, в этой статье давайте рассмотрим код , чтобы узнать, как исправить ошибку формулы Excel . Эти раздражающие #SOMETHING! S, которые вы видите, когда в ваших формулах Excel что-то не так.

Контрольный список ошибок формул Excel

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

# Н / Д Ошибка формулы

Это одна из наиболее частых ошибок формул Excel, которые вы видите при использовании формулы vlookup.Ошибка N / A отображается, когда некоторые данные отсутствуют или в функции поиска (vlookup, hlookup и т. Д.) Передаются неподходящие аргументы, если список не отсортирован, а вы пытаетесь выполнить поиск с помощью параметра сортировки. Вы также можете сгенерировать ошибку # N / A, написав = NA () в ячейке.

Как исправить ошибку # N / A?

Убедитесь, что вы добавили в функции поиска какой-либо механизм обработки ошибок. Например, если вы не уверены, что искомое значение доступно, вы можете написать что-то вроде = ЕСЛИОШИБКА (ВПР (…), «Значение не найдено»).Это будет печатать «значение не найдено» всякий раз, когда vlookup возвращает любую ошибку (включая # N / A)

Связано: Узнайте больше о формуле IFERROR

# DIV / 0! Ошибка формулы

Это самый простой из всех. Когда вы делите что-то на 0, вы видите эту ошибку. Например, при этой ошибке вернется ячейка с формулой = 23/0.

Как исправить ошибку # DIV / 0?

Просто, используйте формулу ЕСЛИ для безопасного деления, например:

= ЕСЛИ (A2 = 0, «», A1 / A2)

# ИМЯ? Ошибка формулы

Наиболее частая причина, по которой вы видите эту ошибку, заключается в неправильном написании формулы, таблицы или именованного диапазона.Например, если вы напишете в ячейке = summa (a1: a10), будет возвращено #NAME? ошибка. Есть несколько других причин, почему это может произойти. Если вы забыли закрыть текст в двойных кавычках или опустите оператор диапазона:. Все эти примеры должны возвращать #NAME? ошибка. = сумма (диапазон1; UNDEFIED_RANGE_NAME), = sum (a1a10)

Как исправить # ИМЯ? Ошибка?

• Убедитесь, что вы указали правильное имя формулы. Используйте автозаполнение при вводе формул. Таким образом, при вводе формул или использовании имен / структурных ссылок вы не сделаете никаких ошибок.
• Убедитесь, что вы определили все таблицы и именованные диапазоны, которые вы используете в формуле.
• Убедитесь, что все используемые вами пользовательские функции правильно установлены.
• Дважды проверьте диапазоны и строковые параметры в формулах.

###### Ошибка

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

Как исправить ошибку ######?

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

# ЗНАЧЕНИЕ! Ошибка формулы Excel

Ошибка значения отображается, когда вы используете текстовые параметры для функции, которая принимает числа. Например, формула = СУММ («ab», «cd») возвращает # ЗНАЧ! ошибка.

Как исправить ошибку #VALUE! ошибка?

Убедитесь, что параметры вашей формулы имеют правильные типы данных. Если вы используете функции, которые работают с числами (например, сумма, сумма и т. Д.), то параметры должны быть числами.

# ССЫЛКА! Ошибка формулы

Это одно из наиболее распространенных сообщений об ошибках, которые вы видите, когда возитесь с листом, полным формул. Вы получите # ССЫЛКУ! Ошибка формулы Excel, когда один из параметров формулы указывает на недопустимый диапазон. Это может произойти из-за того, что вы удалили ячейки. Например, попробуйте написать сумму форумла, например = СУММ (A1: A10, B1: B10, C1: C10), а затем удалите столбец C. Сразу же формула суммы вернет #REF! ошибка.

Как исправить ошибку #REF! ошибка?

Сначала нажмите ctrl + Z и отмените выполненные действия.7E + 37 возвращает # ЧИСЛО! ошибка.

Как исправить # ЧИСЛО! ошибка?

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

# ПУСТО! Ошибка формулы

Это редкая ошибка. Когда вы часто используете неправильные операторы диапазона, вы получаете эту ошибку. Например, формула = СУММ (D30: D32 C31: C33) возвращает #NULL! ошибка, потому что нет перекрытия между диапазоном 1 и диапазоном 2.

Как исправить ошибку #NULL! ошибка?

Убедитесь, что вы правильно указали диапазоны.

Формула не работает — отображается как текст?

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

Дополнительная литература по отладке формул Excel

Отладка формул с использованием клавиши F9

Научитесь работать с Циркулярными ссылками

Понять разницу между абсолютными и относительными ссылками

Как работать с таблицами и структурными ссылками

Обнаружение ошибок в формулах [Office.com]

Как использовать новую формулу ERROR.TYPE для работы с ошибками

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

Как использовать ЕСЛИОШИБКА в Excel с примерами формул

В руководстве показано, как использовать ЕСЛИОШИБКА в Excel для обнаружения ошибок и их замены пустой ячейкой, другим значением или настраиваемым сообщением.Вы узнаете, как использовать функцию ЕСЛИОШИБКА с Vlookup и Index Match и как она сравнивается с IF ISERROR и IFNA.

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

Функция ЕСЛИОШИБКА в Excel — синтаксис и основные способы использования

Функция ЕСЛИОШИБКА в Excel предназначена для обнаружения ошибок в формулах и расчетах и ​​управления ими.Более конкретно, ЕСЛИОШИБКА проверяет формулу и, если она дает ошибку, возвращает другое указанное вами значение; в противном случае возвращает результат формулы.

Синтаксис функции Excel ЕСЛИОШИБКА следующий:

ЕСЛИОШИБКА (значение; значение_если_ошибка)

Где:

• Значение (обязательно) — что проверять на наличие ошибок. Это может быть формула, выражение, значение или ссылка на ячейку.
• Value_if_error (обязательно) — что возвращать, если обнаружена ошибка.Это может быть пустая строка (пустая ячейка), текстовое сообщение, числовое значение, другая формула или вычисление.

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

Чтобы этого не произошло, используйте функцию ЕСЛИОШИБКА, чтобы отлавливать и обрабатывать ошибки так, как вы хотите.

Если ошибка, то пусто

Передайте пустую строку («) в аргумент value_if_error , чтобы вернуть пустую ячейку, если обнаружена ошибка:

= ЕСЛИ ОШИБКА (A2 / B2; "")

Если ошибка, то показать сообщение

Вы также можете отобразить собственное сообщение вместо стандартной записи ошибок Excel:

= ЕСЛИ ОШИБКА (A2 / B2, «Ошибка в вычислении»)

5 фактов о функции ЕСЛИОШИБКА в Excel

1. Функция ЕСЛИОШИБКА в Excel обрабатывает все типы ошибок, включая # DIV / 0 !, # N / A, #NAME ?, #NULL !, #NUM !, #REF !, и #VALUE !.
2. В зависимости от содержимого аргумента value_if_error IFERROR может заменить ошибки вашим пользовательским текстовым сообщением, числом, датой или логическим значением, результатом другой формулы или пустой строкой (пустой ячейкой).
3. Если значение аргумент является пустой ячейкой, он обрабатывается как пустая строка (» ‘), но не как ошибка.
4. IFERROR был представлен в Excel 2007 и доступен во всех последующих версиях Excel 2010, Excel 2013 и Excel 2016.
5. Чтобы перехватить ошибки в Excel 2003 и более ранних версиях, используйте функцию ЕСТЬ ОШИБКА в сочетании с ЕСЛИ, как показано в этом примере.

Примеры формул ЕСЛИОШИБКА

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

Excel ЕСЛИ ОШИБКА с Vlookup

Одним из наиболее распространенных способов использования функции ЕСЛИОШИБКА является сообщение пользователям о том, что значение, которое они ищут, не существует в наборе данных.Для этого нужно заключить формулу ВПР в IFERROR следующим образом:

ЕСЛИ ОШИБКА (ВПР (… ), «Не найдено»)

Если значение поиска отсутствует в таблице, которую вы просматриваете, обычная формула Vlookup вернет ошибку # N / A:

Для удобства пользователей оберните VLOOKUP в IFERROR и отобразите более информативное и удобное для пользователя сообщение:

= ЕСЛИОШИБКА (ВПР (A2; 'Таблица поиска'! $ A $ 2: $ B $ 4; 2; ЛОЖЬ); «Не найдено»)

На скриншоте ниже показана эта формула Iferror в Excel:

Если вы хотите перехватить только ошибки # Н / Д, но не все ошибки, используйте функцию IFNA вместо ЕСЛИОШИБКА.

Дополнительные примеры формул Excel IFERROR VLOOKUP можно найти в следующих руководствах:

Вложенные функции ЕСЛИОШИБКА для последовательного Vlookup в Excel

В ситуациях, когда вам нужно выполнить несколько Vlookup в зависимости от того, успешно или нет предыдущий Vlookup, вы можете вложить две или более функций ЕСЛИОШИБКА одна в другую.

Предположим, у вас есть несколько отчетов о продажах из региональных филиалов вашей компании, и вы хотите получить сумму для определенного идентификатора заказа.С A2 в качестве значения поиска в текущем листе и A2: B5 в качестве диапазона поиска в 3 листах поиска (Отчет 1, Отчет 2 и Отчет 3) формула выглядит следующим образом:

= ЕСЛИОШИБКА (ВПР (A2, 'Отчет 1'! A2: B5,2,0), ЕСЛИОШИБКА (ВПР (A2, 'Отчет 2'! A2: B5,2,0), ЕСЛИОШИБКА (ВПР (A2, ' Report 3 '! A2: B5,2,0), «не найдено»)))

Результат будет примерно таким:

Подробное описание логики формулы см. В разделе Как выполнять последовательные поисковые запросы в Excel.

ЕСЛИОШИБКА в формулах массива

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

Допустим, у вас есть Итого в столбце B и Цена в столбце C, и вы хотите вычислить Общее количество .Это можно сделать с помощью следующей формулы массива : , которая делит каждую ячейку в диапазоне B2: B4 на соответствующую ячейку диапазона C2: C4, а затем складывает результаты:

= СУММ ($ B $ 2: $ B $ 4 / $ C $ 2: $ C $ 4)

Формула работает нормально, пока в диапазоне делителя нет нулей или пустых ячеек. Если есть хотя бы одно значение 0 или пустая ячейка, # DIV / 0! возвращается ошибка:

Чтобы исправить эту ошибку, просто выполните разделение в функции ЕСЛИОШИБКА:

= СУММ (ЕСЛИОШИБКА ($ B $ 2: $ B $ 4 / $ C $ 2: $ C $ 4,0))

Формула делит значение в столбце B на значение в столбце C в каждой строке (100/2, 200/5 и 0/0) и возвращает массив результатов {50; 40; # DIV / 0!}.Функция ЕСЛИОШИБКА перехватывает все # DIV / 0! ошибок и заменяет их нулями. Затем функция СУММ складывает значения в результирующий массив {50; 40; 0} и выводит окончательный результат (50 + 40 = 90).

Примечание. Помните, что формулы массива должны быть заполнены нажатием сочетания клавиш Ctrl + Shift + Enter.

ЕСЛИ ОШИБКА и ЕСЛИ ОШИБКА

Теперь, когда вы знаете, насколько просто использовать функцию ЕСЛИОШИБКА в Excel, вы можете задаться вопросом, почему некоторые люди все еще склоняются к использованию комбинации ЕСЛИ ОШИБКА.Есть ли у него преимущества по сравнению с ЕСЛИ ОШИБКА? Никто. В старые плохие времена Excel 2003 и ниже, когда ЕСЛИ ОШИБКА не существовало, ЕСЛИ ОШИБКА была единственным возможным способом отловить ошибки. В Excel 2007 и более поздних версиях это просто более сложный способ добиться того же результата.

Например, чтобы отловить ошибки Vlookup, вы можете использовать любую из следующих формул.

в Excel 2007 — Excel 2016:

ЕСЛИ ОШИБКА (ВПР (… ), «Не найдено»)

Во всех версиях Excel:

ЕСЛИ (ОШИБКА (ВПР (…)), «Не найдено», ВПР (…))

Обратите внимание, что в формуле IF ISERROR VLOOKUP необходимо дважды выполнить Vlookup.На простом английском языке формулу можно прочитать следующим образом: Если Vlookup приводит к ошибке, вернуть «Not found», в противном случае вывести результат Vlookup.

А вот реальный пример формулы Excel If Iserror Vlookup:

= ЕСЛИ (ЕСТЬ ОШИБКА (ВПР (D2, A2: B5,2, ЛОЖЬ)), «Не найдено», ВПР (D2, A2: B5,2, ЛОЖЬ))

ЕСЛИОШИБКА по сравнению с IFNA

IFNA, представленная в Excel 2013, является еще одной функцией для проверки формулы на наличие ошибок. Его синтаксис подобен синтаксису IFERROR:

IFNA (значение; значение_if_na)

Чем IFNA отличается от IFERROR? Функция IFNA ловит только # Н / Д ошибки , тогда как ЕСЛИОШИБКА обрабатывает все типы ошибок.

В каких ситуациях вы можете использовать IFNA? Когда неразумно скрывать все ошибки. Например, при работе с важными или конфиденциальными данными вы можете получать уведомления о возможных сбоях в наборе данных, а стандартные сообщения об ошибках Excel с символом «#» могут быть яркими визуальными индикаторами.

Давайте посмотрим, как можно создать формулу, которая отображает сообщение «Не найден» вместо ошибки «Н / Д», которая появляется, когда значение подстановки отсутствует в наборе данных, но привлекает ваше внимание к другим ошибкам Excel.

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

Чтобы поймать # N / A, но отобразить ошибку # DIV / 0, используйте функцию IFNA в Excel 2013 и Excel 2016:

= IFNA (ВПР (F3; $ A $ 3: $ D $ 6,4; FALSE); «Не найдено»)

Или комбинация IF ISNA в Excel 2010 и более ранних версиях:

= ЕСЛИ (ISNA (ВПР (F3, $ A $ 3: $ D $ 6,4, FALSE)), «Не найдено», VLOOKUP (F3, $ A $ 3: $ D $ 6,4, FALSE))

Синтаксис формул IFNA VLOOKUP и IF ISNA VLOOKUP аналогичен синтаксису формул IFERROR VLOOKUP и IF ISERROR VLOOKUP, описанных ранее.

Как показано на снимке экрана ниже, формула Ifna Vlookup возвращает «Not found» только для элемента, которого нет в таблице поиска ( Peaches ). Для Lemons отображается # DIV / 0! означает, что наша таблица поиска содержит ошибку деления на ноль:

Рекомендации по использованию ЕСЛИОШИБКА в Excel

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

1. Не перехватывайте ошибки без причины.
2. Оберните минимально возможную часть формулы в ЕСЛИОШИБКА.
3. Чтобы обрабатывать только определенные ошибки, используйте функцию обработки ошибок с меньшим объемом:
   • IFNA или IF ISNA для обнаружения только ошибок # N / A.
   • ISERR для перехвата всех ошибок, кроме # N / A.

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

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

ошибок формул Excel | Exceljet

Чем больше формул напишете, тем с большим количеством ошибок столкнетесь 🙂

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

Если вы столкнулись с ошибкой в ​​формуле, не паникуйте. Сохраняйте спокойствие и методично исследуйте, пока не найдете причину. Спросите себя: «О чем мне говорит эта ошибка?» Экспериментируйте методом проб и ошибок. По мере накопления опыта вы сможете избежать многих ошибок и быстрее исправлять возникающие ошибки.

Исправление ошибок

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

1. Найдите ошибки. Вы можете использовать Go to Special> Formula, как описано ниже.

2. Найдите источник ошибки. Если это сложно, попробуйте функцию отслеживания ошибок.

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

4. Исправьте ошибку в источнике.

Видео: примеры ошибок формул Excel

Видео: используйте F9 для отладки формулы

Поиск всех ошибок

С помощью Go To Special вы можете найти сразу все ошибки. Используйте сочетание клавиш Control + G, затем нажмите кнопку «Специальная». Excel отобразит диалоговое окно со многими параметрами, показанными ниже. Чтобы выбрать только ошибки, выберите «Формулы + ошибки», затем нажмите «ОК»:

ошибок захвата

Захват ошибок — это способ «отлова» ошибок, чтобы предотвратить их появление.Это имеет смысл, если вы знаете, что определенные ошибки вероятны, и хотите, чтобы сообщения об ошибках не появлялись. Есть два основных подхода:

2. Перехватите ошибку с помощью IFERROR или ISERROR. При таком подходе вы отслеживаете ошибку и предоставляете альтернативу при ее обнаружении. На этой странице показан пример ВПР.

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

Коды ошибок Excel

Есть 9 кодов ошибок, с которыми вы, вероятно, столкнетесь в какой-то момент при работе с формулами Excel. В этом разделе показаны примеры каждой ошибки формулы с информацией и ссылками на то, как исправить ошибку.

# DIV / 0! ошибка

Как следует из названия, # DIV / 0! ошибка появляется, когда формула пытается разделить на ноль или на значение, эквивалентное нулю. Вы можете увидеть # DIV / 0! ошибка, когда данные еще не полные.Например, ячейка на листе пуста, потому что данные не были введены или еще не доступны. Вы также можете увидеть ошибку деления на ноль с функциями СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН, если критерий не соответствует ни одной ячейке в диапазоне.

Например, на листе ниже ошибка DIV отображается в ячейке D4, потому что C4 пуст. Пустые ячейки оцениваются Excel как ноль, а B4 нельзя разделить на ноль:

Во многих случаях пустые ячейки или пропущенные значения неизбежны.Вы можете использовать функцию ЕСЛИОШИБКА, чтобы перехватить # DIV / 0! и, если хотите, отобразите более дружелюбное сообщение.

Подробнее: Как исправить ошибку # DIV / 0! ошибка

# ИМЯ? ошибка

# ИМЯ? ошибка означает, что Excel что-то не распознает. Это может быть неправильное имя функции, несуществующий именованный диапазон или неправильно введенная ссылка на ячейку. Например, на приведенном ниже экране функция VLOOKUP в F3 написана с ошибкой «VLOKUP». VLOKUP не является допустимым именем, поэтому формула возвращает # ИМЯ ?.

Чтобы исправить # ИМЯ? ошибка, вы должны найти проблему, а затем исправить орфографию или синтаксис. Для получения дополнительных сведений и примеров см. Эту страницу.

# Н / Д ошибка

Ошибка # N / A появляется, когда что-то не может быть найдено. Он сообщает вам, что что-то отсутствует или написано с ошибкой. Это может быть код продукта, который еще не доступен, имя сотрудника с ошибкой, несуществующий цвет и т. Д. Часто ошибки # N / A вызваны лишними пробелами, орфографическими ошибками или неполной таблицей поиска.Функции, на которые чаще всего влияет ошибка # Н / Д, — это классические функции поиска, включая ВПР, ГПР, ПРОСМОТР и ПОИСКПОЗ.

Например, на приведенном ниже экране формула в F3 возвращает # N / A, потому что «Bacon» отсутствует в таблице поиска:

Если значение в E3 изменить на «кофе», «яйца» и т. Д., Функция ВПР будет работать нормально и извлекать стоимость товара.

Лучший способ предотвратить ошибки # Н / Д — убедиться, что значения поиска и таблицы поиска верны и полны.При необходимости вы можете перехватить ошибку # Н / Д с помощью ЕСЛИОШИБКА и отобразить более понятное сообщение или вообще ничего не отображать. ‘

Дополнительная информация: Как исправить ошибку # N / A.

# ЧИСЛО! ошибка

# ЧИСЛО! ошибка возникает, когда число слишком велико или мало, или когда вычисление невозможно. Например, если вы попытаетесь вычислить квадратный корень из отрицательного числа, вы увидите ошибку # ЧИСЛО:

На экране выше функция КОРЕНЬ используется для вычисления чисел квадратного корня в столбце B.Формула в C5 возвращает # ЧИСЛО! ошибка, потому что значение в B5 отрицательное, и невозможно вычислить квадратный корень из отрицательного числа.

Вы также можете столкнуться с ошибкой # ЧИСЛО, если поменяете местами даты начала и окончания внутри функции РАЗНДАТ.

В общем, исправление ошибки # ЧИСЛО! Ошибка заключается в корректировке входных данных по мере необходимости, чтобы сделать расчет снова возможным.

Дополнительная информация: Как исправить ошибку #NUM! ошибка.

# ЗНАЧЕНИЕ! ошибка

# ЗНАЧЕНИЕ! ошибка появляется, когда значение не является ожидаемым или допустимым типом (т.е. дата, время, число, текст и т. д.). Это может произойти, когда ячейка остается пустой, когда текстовое значение передается функции, которая ожидает числовое значение, или когда даты вычисляются Excel как текст.

Например, на приведенном ниже экране ячейка C3 содержит текст «NA», а формула в F2 возвращает #VALUE! ошибка.

Ниже функция МЕСЯЦ не может извлечь значение месяца из «яблоко», поскольку «яблоко» не является датой:

Примечание: вы также можете увидеть #VALUE! ошибка, если вы создаете формулу массива и забываете ввести формулу с помощью Control + Shift + Enter.

Чтобы исправить ошибку #VALUE! ошибка, вам нужно отследить проблемное значение и указать правильный тип значения. Для получения дополнительных сведений и примеров см. Эту страницу.

# ССЫЛКА! ошибка

# ССЫЛКА! error — одна из самых распространенных ошибок, которые вы увидите в формулах Excel. Это происходит, когда ссылка становится недействительной. Во многих случаях это связано с тем, что листы, строки или столбцы были удалены, или потому, что формула с относительными ссылками была скопирована в новое место, где ссылки недействительны.

Например, на приведенном ниже экране формула из C8 была скопирована в E4. В этом новом местоположении, поскольку диапазон C3: C7 является относительным, он становится недействительным, и формула возвращает #REF !:

#REF! Исправить ошибки бывает довольно сложно, потому что исходная ссылка на ячейку исчезла навсегда. Если вы удалите строку или столбец и увидите # ССЫЛКА! ошибки, вам следует немедленно отменить действие и сначала настроить формулы.

Подробнее о #REF! ошибки.

# ПУСТО! ошибка

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

Технически это потому, что пробел является оператором «пересечения диапазона», а #NULL! ошибка сообщает, что два диапазона (C3 и C7) не пересекаются. В большинстве случаев вы можете исправить ошибку NULL, заменив пробел запятой или двоеточием по мере необходимости.

Подробнее: Как исправить ошибку #NULL! ошибка

#### ошибка

Хотя технически это не ошибка, вы также можете увидеть формулу, которая отображает строку хеш-символов (###) вместо обычного результата.Например, на приведенном ниже экране формула в C3 добавляет 5 дней к дате в столбце B:

В этом случае символы решетки или решетки (###) появляются, потому что даты в столбце C отформатированы в длинном формате и не помещаются в столбец. Чтобы исправить эту ошибку, просто сделайте столбец шире.

Примечание. Excel не отображает отрицательные даты. Если формула возвращает отрицательное значение даты, Excel отобразит #####.

Подробнее: Как исправить ##### ошибки

# РАЗЛИВ! ошибка

Ошибка #SPILL возникает, когда формула выводит диапазон разлива, который попадает в ячейку, которая уже содержит данные.Например, на приведенном ниже экране функция УНИКАЛЬНАЯ настроена для извлечения списка уникальных имен в диапазон разлива, начиная с D3. Поскольку D5 содержит «яблоко», операция останавливается, и формула возвращает #SPILL !.

Когда «яблоко» удаляется из D5, формула будет работать нормально и вернет «Джо», «Сэм» и «Мэри».

Подробности: как исправить ошибки #SPILL

Видео: Разлив и полигон

#CALC! ошибка

Ошибка #CALC возникает, когда в формуле возникает ошибка вычисления с массивом.Например, на приведенном ниже экране функция ФИЛЬТР настроена для фильтрации исходных данных в B5: D11. Однако формула запрашивает все данные в группе «яблоко», которой не существует:

Если формула настроена для фильтрации по группе «A», формула будет работать нормально:

Базовые формулы Excel — Список важных формул для начинающих

Руководство по основным формулам Excel

Освоение основных формул Excel имеет решающее значение для новичков, чтобы стать высокопрофессиональными в финансовом анализе Описание работы финансового аналитика Описание работы финансового аналитика ниже дает типичный пример всех навыки, образование и опыт, необходимые для работы аналитиком в банке, учреждении или корпорации.Выполняйте финансовое прогнозирование, отчетность и отслеживание операционных показателей, анализируйте финансовые данные, создавайте финансовые модели. Microsoft Excel Ресурсы ExcelИзучайте Excel онлайн с помощью 100 бесплатных руководств, ресурсов, руководств и шпаргалок по Excel! Ресурсы CFI — лучший способ изучить Excel на своих условиях. считается отраслевым стандартом программного обеспечения для анализа данных. Программа электронных таблиц Microsoft также является одним из наиболее предпочтительных программ для инвестиционных банкиров. Описание вакансии в инвестиционном банке. В этом описании вакансии в инвестиционном банке излагаются основные навыки, образование и опыт работы, необходимые для того, чтобы стать аналитиком или партнером IB и финансовыми аналитиками в области обработки данных и финансового моделирования. Финансовое моделирование Финансовое моделирование выполняется в Excel для прогнозирования финансовых показателей компании.Обзор того, что такое финансовое моделирование, как и зачем создавать модель, и презентация. Это руководство предоставит обзор и список основных функций Excel.

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

Основные термины в Excel

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

1. Формулы

В Excel формула — это выражение, которое работает со значениями в диапазоне ячеек или ячейке. Например, = A1 + A2 + A3, которое находит сумму диапазона значений от ячейки A1 до ячейки A3.

2. Функции

Функции — это предварительно определенные формулы в Excel.Они исключают трудоемкий ручной ввод формул, давая им понятные для человека имена. Например: = СУММ (A1: A3). Функция суммирует все значения от A1 до A3.

Пять экономящих время способов вставки данных в Excel

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

1. Простая вставка: ввод формулы внутри ячейки

Ввод формулы в ячейку или строку формул — самый простой способ вставки основных формул Excel. Обычно процесс начинается с ввода знака равенства, за которым следует имя функции Excel.

Excel достаточно умен тем, что когда вы начинаете вводить имя функции, появляется всплывающая подсказка функции. Из этого списка вы выберете свои предпочтения. Однако не нажимайте клавишу Enter.Вместо этого нажмите клавишу TAB, чтобы продолжить вставку других параметров. В противном случае вы можете столкнуться с ошибкой неверного имени, часто как «#NAME?». Чтобы исправить это, просто повторно выберите ячейку и перейдите к строке формул, чтобы выполнить свою функцию.

Изображение: бесплатный ускоренный курс CFI по Excel.

2. Использование опции «Вставить функцию» на вкладке «Формулы»

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

3. Выбор формулы из одной из групп на вкладке «Формула»

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

Изображение: CFI’s Excel Courses.

4. Использование опции автосуммирования

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

5. Быстрая вставка: используйте недавно использованные вкладки

Если вы обнаружите, что повторный ввод последней формулы является монотонной задачей, воспользуйтесь меню «Недавно использованные». Он находится на вкладке «Формулы», третьем пункте меню рядом с функцией Автосумма.

Бесплатное руководство по формулам Excel на YouTube

Просмотрите БЕСПЛАТНОЕ видео на YouTube от CFI, чтобы быстро изучить наиболее важные формулы Excel. Посмотрев демонстрационный видеоролик, вы быстро узнаете самые важные формулы и функции.

Семь основных формул Excel для вашего рабочего процесса

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

1. СУММ

Функция СУММ Функция СУММ Функция СУММ относится к математическим функциям и функциям тригонометрии. Функция суммирует ячейки, которые представлены как несколько аргументов. Это самая популярная и широко используемая функция в Excel. SUM помогает пользователям выполнять быстрое суммирование указанных ячеек в MS Excel. Например, нам дана стоимость 100 — это первая формула, которую необходимо знать в Excel. Обычно он объединяет значения из набора столбцов или строк из выбранного диапазона.

= СУММ ( число1 , [число2],…)

Пример:

= СУММ (B2: G2) — простой выбор, который суммирует значения строки.

= СУММ (A2: A8) — простой выбор, который суммирует значения столбца.

= СУММ (A2: A7, A9, A12: A15) — сложная коллекция, которая суммирует значения от диапазона A2 до A7, пропускает A8, добавляет A9, перескакивает с A10 и A11, а затем, наконец, добавляет от A12 к A15.

= СУММ (A2: A8) / 20 — показывает, что вы также можете превратить свою функцию в формулу.

Изображение: бесплатный ускоренный курс CFI по Excel.

2. СРЕДНЕЕ

Функция СРЕДНЕЕ Функция СРЕДНЕЕ Расчет среднего значения в Excel. Функция СРЕДНИЙ относится к Статистическим функциям. Он вернет среднее значение аргументов. Он используется для вычисления среднего арифметического заданного набора аргументов. Финансовому аналитику эта функция полезна для определения среднего числа. должен напоминать вам о простых средних данных, таких как среднее количество акционеров в данном пуле акционеров.

= СРЕДНЕЕ ( число1 , [число2],…)

Пример:

= СРЕДНЕЕ (B2: B11) — показывает простое среднее, также похоже на (СУММ (B2: B11) / 10)

3. COUNT

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

= COUNT ( значение1, [значение2],… )

Пример:

COUNT (A : A) — подсчитывает все числовые значения в столбце A. Однако для подсчета строк необходимо настроить диапазон внутри формулы.

COUNT (A1: C1) — Теперь он может подсчитывать строки.

Изображение: CFI’s Excel Courses.

4. COUNTA

Как и функция COUNT, функция COUNTACOUNTA Функция COUNTA вычисляет количество непустых ячеек в заданном наборе значений. Функцию = counta () также часто называют формулой Excel Countif Not Blank. Финансовому аналитику полезна функция подсчета ячеек, которые не являются пустыми или пустыми в заданном диапазоне. считает все клетки в данной ярости.Однако он считает все ячейки независимо от типа. То есть, в отличие от COUNT, которое считает только числа, он также считает даты, время, строки, логические значения, ошибки, пустую строку или текст.

= COUNTA ( значение1, [значение2],… )

Пример:

COUNTA (C2: C13) — считает строки со 2 по 13 в столбце C независимо от типа. Однако, как и COUNT, вы не можете использовать ту же формулу для подсчета строк. Вы должны скорректировать выбор внутри скобок — например, COUNTA (C2: h3) будет считать столбцы от C до H

5.IF

Функция IF Функция IF Функция Excel IF Statement проверяет заданное условие и возвращает одно значение для ИСТИННОГО результата, а другое — для ЛОЖНОГО результата. Например, если общий объем продаж превышает 5000 долларов, тогда в качестве бонуса верните «Да», иначе верните «Нет». Мы также можем создавать вложенные операторы IF, которые часто используются, когда вы хотите отсортировать данные в соответствии с заданной логикой. Лучшая часть формулы ЕСЛИ заключается в том, что вы можете встраивать в нее формулы и работать с ними.

= IF ( logic_test, [value_if_true], [value_if_false] )

Пример:

= IF (C2 — проверяет, соответствует ли значение C3 меньше значения в D3.Если логика верна, пусть значение ячейки будет ИСТИНА, иначе ЛОЖЬ

= ЕСЛИ (СУММ (C1: C10)> СУММ (D1: D10), СУММ (C1: C10), СУММ (D1: D10) )) — Пример сложной логики ЕСЛИ. Сначала он суммирует C1 до C10 и D1 до D10 , затем сравнивает сумму. Если сумма от C1 до C10 больше суммы от D1 до D10 , тогда значение ячейки становится равным сумме от C1 до C10 . В противном случае это будет СУММА от C1 до C10 .

6. ОБРЕЗАТЬ

Функция ОБРЕЗАТЬ Функция ОБРЕЗАТЬ Функция ОБРЕЗАТЬ относится к категории текстовых функций Excel. TRIM помогает удалить лишние пробелы в данных и, таким образом, очистить ячейки на листе. В финансовом анализе функция TRIM может быть полезна для удаления нерегулярных, чтобы убедиться, что ваши функции не возвращают ошибки из-за неуправляемых пробелов. Это гарантирует, что все пустые места удалены. В отличие от других функций, которые могут работать с диапазоном ячеек, TRIM работает только с одной ячейкой.Следовательно, это имеет обратную сторону — добавление дублированных данных в вашу электронную таблицу.

= ОБРЕЗАТЬ ( текст )

Пример:

ОБРЕЗАТЬ (A2) — Удаляет пустые пробелы в значении в ячейке A2.

Изображение: бесплатный ускоренный курс CFI по Excel.

7. МАКСИМУМ и МИНИМУМ

Функция МАКСМАКС Функция МАКСА относится к категории статистических функций Excel. MAX вернет наибольшее значение в заданном списке аргументов.Из заданного набора числовых значений он вернет наибольшее значение. В отличие от функции MAXA, функция MAX будет подсчитывать числа, но игнорировать пустые ячейки и функцию MINMIN Функция MIN относится к категории статистических функций Excel. MIN вернет минимальное значение в заданном списке аргументов. Из заданного набора числовых значений он вернет наименьшее значение. В отличие от функции MINA, функции помогают найти максимальное и минимальное количество в диапазоне значений.

= МИН ( число1 , [число2],…)

Пример:

= МИН (B2: C11) — Находит минимальное число между столбцом B от B2 и столбцом C от C2 до строка 11 в обоих столбцах B и C.

= МАКС ( число1 , [число2],…)

Пример:

= МАКС (B2: C11) — аналогично, он находит максимальное число между столбцом B из B2 и столбец C от C2 до строки 11 в обоих столбцах B и C.

Дополнительные ресурсы

Спасибо за то, что прочитали руководство CFI по основным формулам Excel. Чтобы продолжить свое развитие в качестве финансового аналитика мирового уровня, сертификация FMVA® Присоединяйтесь к более чем 850 000 студентов, которые работают в таких компаниях, как Amazon, J.П. Морган и Феррари, эти дополнительные ресурсы CFI будут полезны:

• Расширенные формулы Excel Расширенные формулы Excel, которые необходимо знать Эти расширенные формулы Excel очень важно знать и выведут ваши навыки финансового анализа на новый уровень. Расширенные функции Excel
• Преимущества сочетаний клавиш Excel Обзор сочетаний клавиш Excel Сочетания клавиш Excel — это забытый метод повышения производительности и скорости работы в Excel. Ярлыки Excel предлагают финансовому аналитику мощный инструмент.Эти ярлыки могут выполнять множество функций. так же просто, как навигация по электронной таблице для заполнения формул или группировки данных.
• Список функций Excel Функции Список наиболее важных функций Excel для финансовых аналитиков. Эта шпаргалка охватывает 100 функций, которые критически важно знать аналитику Excel
• Моделирование оценки Excel Моделирование оценки в Excel Моделирование оценки в Excel может относиться к нескольким различным типам анализа, включая анализ дисконтированного денежного потока (DCF), сопоставимые торговые мультипликаторы

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

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

Как Excel сохраняет даты

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

Excel хранит даты как порядковый номер, который представляет количество дней, прошедших с начала 1900 года. Это означает, что 1 января 1900 года на самом деле просто 1 . 2 января 1900 г. — это 2 . К тому времени, как мы добрались до нынешнего десятилетия, цифры стали довольно большими… 10 сентября 2013 г. хранится как 41527 .

Важно отметить, что любая дата до 1 января 1900 года не распознается как дата в Excel. В числовой строке нет «отрицательных» серийных номеров даты.

Это кажется запутанным, но с ним намного проще складывать, вычитать и считать дни. Неделя с 10 сентября 2013 г. ( 17 сентября 2013 г. ) составляет всего 41527 + 7 дней, или 41534 .

Как Excel сохраняет времена

Excel хранит время, используя тот же формат порядковой нумерации, что и для дат.Дни начинаются в полночь (12:00 или 0:00 часов). Поскольку каждый час составляет 1/24 дня, он представлен как десятичное значение: 0,041666…

Это означает, что 9:00 утра ( 09:00 часов ) 10 сентября 2013 г. будет сохранен как 41527,375 .

Если время указано без даты, Excel сохраняет его так, как если бы оно произошло 0 января 1900 г. . Другими словами, 3:00 pm ( 15:00 часов ) сохраняется как 0.625 . Это может затруднить выполнение вычислений для значений только времени (без даты), поскольку вычитание 6 часов ( 6:00 ) из 3:00 утра ( 03:00 часов ) станет отрицательным и будет считаться ошибка: 0,125 — 0,25 = -0,125 , что отображается как ######### .

Минуты и секунды в Excel работают так же, как часы…

Минута — это 1/60 часа, что составляет 1/24 дня, или всего 1/1440 дня, что составляет 0.00069444…

Секунда составляет 1/60 минуты, что составляет 1/60 часа, что составляет 1/24 дня, или всего 1/86400 дня, что составляет 0,00001157407…

Работа с датой и временем

ДАТА () и ВРЕМЯ ()

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

 =  ДАТА  ( год ,  месяц ,  день ) 

 =  ВРЕМЯ  ( часов ,  минут ,  секунд ) 

Для обеих функций укажите год, месяц и день или часы, минуты и секунды в виде чисел. Например, 10 сентября 2013 г. можно ввести как:

 =  ДАТА  (2013,9,10) 

Он будет храниться как 41527 , что означает, что технически он хранит 12:00 10 сентября 2013 г. .

Для времени 18:00 ( 18:00 ) можно ввести как:

 =  ВРЕМЯ  (18,0,0) 

Он будет храниться как 0,75 , что означает, что технически он хранит 18:00 0 января 1900 г. .

Если мы хотим представить конкретное время и дату, мы можем сложить две функции вместе. Например, 18:00 ( 18:00 ) 10 сентября 2013 г. можно ввести как:

 =  ДАТА  (2013,9,10) +  ВРЕМЯ  (18,0,0) 

Будет вычислено как 41527.75 , что означает, что Excel сохраняет именно ту дату, которую мы хотим…

Дополнительные функции установки даты и времени

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

СЕГОДНЯ ()

Функция СЕГОДНЯ всегда возвращает порядковый номер текущей даты. Функция СЕГОДНЯ просто вводится как:

 =  СЕГОДНЯ  () 

Эта статья была написана в 18:30, ( 18:30 часов, ), 24 сентября 2013 г., , а функция СЕГОДНЯ вычислена как 41541 .Это означает, что технически он хранит 12:00 24 сентября 2013 г. .

СЕЙЧАС ()

Аналогичная функция под названием NOW всегда возвращает порядковый номер текущей даты и времени. Функция NOW просто вводится как:

 =  СЕЙЧАС  () 

Опять же, в 18:30 ( 18:30 часов ) 24 сентября 2013 г. функция, вычисленная как 41541.77081333… NOW , сохраняет точное время и дату с точностью до секунды.

EDATE () и EOMONTH ()

Функция EDATE выдает дату через указанное количество месяцев от даты ввода. Функция EOMONTH выдает дату последнего дня месяца. Это может быть текущий месяц или несколько месяцев в будущем или прошлом. Синтаксис для каждого следующий:

 =  ДАТА  ( начальная_дата ,  месяцев ) 

 =  EOMONTH  ( start_date ,  месяцев ) 

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

Поле месяцев может быть любым числом, но будет использоваться только целочисленное значение (например, оно рассматривает 2,8 как 2).

Функции EDATE и EOMONTH удаляют значение времени из даты. Например, Например, если в ячейке A1 хранится 10 сентября 2013 г. , мы можем получить значение на 2 месяца вперед следующим образом:

 =  ДАТА  ( A1 , 2) 

Возвращает 12:00 am ( 0:00 часов ) 10 ноября 2013 г. или 41588 .Эта функция работает, даже если в месяцах разное количество дней (в сентябре и ноябре их 30, а в октябре 31).

 =  EOMONTH  ( A1 , 2) 

Возвращает 12:00 am ( 0:00 часов ) 30 ноября 2013 г. или 41588 . Опять же, эта функция работает, даже если в месяцах разное количество дней.

РАБДЕНЬ ()

Иногда бывает полезно вести отсчет вперед по рабочим дням (понедельник-пятница), а не по всем 7 дням недели… Для этого в Excel предусмотрено РАБДЕНЬ .Синтаксис РАБДЕНЬ выглядит следующим образом:

 =  РАБДЕНЬ  ( начальная_дата ,  дней ,  [праздничные дни] ) 

start_date такая же, как указано выше.

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

Ввод [праздники] не является обязательным, но позволяет вам дисквалифицировать определенные дни (например, День Благодарения или Рождество), которые в противном случае могли бы выпасть на рабочую неделю.Это серийные номера дат, представленные в массиве, заключенном в квадратные скобки: {}. Чтобы указать несколько праздников, даты должны храниться в ячейках — невозможно поместить несколько функций DATE в массив.

Например, давайте найдем дату за 6 рабочих дней до 18:00 ( 18:00 ) 10 сентября 2013 г. (хранится в ячейке A1). Понедельник, 2 сентября, — День труда, поэтому давайте включим его как выходной:

 =  РАБОЧИЙ ДЕНЬ  ( A1 , -6,  ДАТА  (2013,9,2)) 

Возвращает 12:00 am ( 0:00 часов ) 30 августа 2013 г. или 41516 .(Обратите внимание, что функция удаляет временную часть даты.)

WORKDAY.INTL () (Excel 2010 и новее)

Для более новых версий Excel (2010 и более поздних версий) существует другая версия WORKDAY , которая называется WORKDAY.INTL . WORKDAY.INTL работает так же, как WORKDAY , но добавляет возможность настраивать определение «выходных». Синтаксис РАБДЕНЬ.INTL выглядит следующим образом:

 =  WORKDAY.INTL  ( начальная_дата ,  дней ,  [выходные] ,  [праздничные дни] ) 

Входные данные start_date , days и [праздничные дни] работают так же, как и обычная функция WORKDAY .

Вход [выходные] имеет следующие параметры:

Получение дат в Excel

ДЕНЬ (), МЕСЯЦ () и ГОД ()

Теперь мы знаем, как определять даты, но нам все еще нужно уметь с ними работать. Серийные номера не позволяют легко извлечь месяцы, годы и дни, не говоря уже о часах, минутах и ​​секундах. Вот почему в Excel есть специальные функции для извлечения каждого из этих значений. Для работы с календарем есть ДЕНЬ, МЕСЯЦ и ГОД.Синтаксис прост:

 =  ДЕНЬ  ( серийный_номер ) 

 =  МЕСЯЦ  ( серийный_номер ) 

 =  ГОД  ( серийный_номер ) 

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

 =  ДЕНЬ  ( A1 ) 

Возвращает 10 в виде числового значения.

 =  МЕСЯЦ  ( A1 ) 

Возвращает 9 в виде числового значения.

 =  ГОД  ( A1 ) 

Возвращает 2013 в виде числового значения.

Мы также могли указать прямой серийный номер для 10 сентября 2013 г. :

 =  ДЕНЬ  ( 41527 ) 

Возвращает 10 в виде числового значения.

Время получения в Excel

ЧАС (), МИНУТА () и ВТОРАЯ ()

Иногда процесс очень похож.В Excel есть функция для извлечения часов, минут и секунд из метки времени, которая имеет удобные названия ЧАС , МИНУТА и СЕКУНДА . Синтаксис идентичен:

 =  ЧАС  ( серийный_номер ) 

 =  МИНУТА  ( серийный_номер ) 

 =  СЕКУНДА  ( серийный_номер ) 

serial_number в каждом может быть любой ссылкой на ячейку в формате времени / даты. Например, если A1 хранит 18:15:30 pm ( 18:15 часов, 30 секунд ) на 10 сентября 2013 г. , мы можем использовать каждую из формул по очереди:

 =  ЧАС  ( A1 ) 

Возвращает 18 в виде числового значения.

 =  МИНУТА  ( A1 ) 

Возвращает 15 в виде числового значения.

 =  СЕКУНДА  ( A1 ) 

Возвращает 30 в виде числового значения.

Мы также могли бы указать прямой серийный номер для 6:15:30 pm ( 18:15 часов, 30 секунд ) на 10 сентября 2013 г. :

 =  СЕКУНДА  ( 41527.7607638889 ) 

Возвращает 30 в виде числового значения.

Дополнительные функции получения даты

WEEKDAY () и WEEKNUM ()

Даты содержат не только информацию о месяце и году. Они также кодируют косвенную информацию… 10 сентября 2013 г. также бывает вторник . В Excel есть несколько функций для работы с недельным аспектом дат: WEEKDAY и WEEKNUM . Синтаксис следующий:

 =  WEEKDAY  ( серийный_номер ,  [return_type] ) 

 =  НОМЕР НЕДЕЛИ  ( серийный_номер ,  [return_type] ) 

serial_number в каждом может быть любой ссылкой на ячейку в формате даты.Поскольку [return_type] является необязательным, каждая функция предполагает, что каждая неделя начинается с воскресенья . Если ячейка A1 хранит 10 сентября 2013 г. ( вторник ), мы можем использовать каждую из формул по очереди:

 =  ДЕНЬ НЕДЕЛИ  ( A1 ) 

Возвращает 3 , так как вторник — это 3-й день недели, который начинается в воскресенье .

 =  НЕДЕЛЬНЫЙ НОМЕР  ( A1 ) 

Возвращает 37 , так как 10 сентября 2013 г. находится на 37-й неделе 2013 , когда вы начинаете отсчет недель с воскресенья .

[return_type] позволяет указать другое расположение недель по умолчанию. Вы можете позволить неделе начинаться с понедельника и продолжаться до воскресенья или субботы до пятницы . Однако Excel раздражает и делает разные записи для WEEKDAY и WEEKNUM . Полный список опций для WEEKDAY выглядит следующим образом:

Варианты 2 и 11 функционально одинаковы — первый предназначен только для обратной совместимости с более ранними версиями Excel.

Полный список опций для WEEKNUM выглядит следующим образом:

Подсчет и отслеживание дат

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

ЧИСТЫХ ДНЕЙ ()

Выше мы узнали о РАБДЕНЬ , который позволяет перемещаться вперед и назад на заданное количество рабочих дней, игнорируя выходные и праздничные дни.Но что, если вам нужно измерить количество рабочих дней между двумя датами? Для этого Excel предоставляет ЧИСТЫХ ДНЕЙ . Синтаксис формулы следующий:

 =  NETWORKDAYS  ( start_date ,  end_date ,  [праздничные дни] ) 

start_date и end_date может быть любой ссылкой на ячейку в формате даты.

Ввод [праздники] не является обязательным, но позволяет вам дисквалифицировать определенные дни (например, День Благодарения или Рождество), которые в противном случае могли бы выпасть на рабочую неделю.Это серийные номера дат, представленные в массиве, заключенном в квадратные скобки: {}. Чтобы указать несколько праздников, даты должны храниться в ячейках — невозможно поместить несколько функций DATE в массив.

Например, если A1 содержит 18:00 ( 18:00 ) 10 сентября 2013 г. и B1 содержит 9:00 am ( 9:00 часов ) декабря 2, 2013 , мы можем использовать NETWORKDAYS , чтобы найти количество рабочих дней между двумя датами.

Исключим из праздничных дней День Колумба (14 октября 2013 г.), День ветеранов (11 ноября 2013 г.) и День Благодарения (28 ноября 2013 г.). Для этого мы должны хранить эти даты в других ячейках. Давайте поместим их в C1 , C2 и C3 :

 =  ДАТА  (2013,10,14) 

 =  ДАТА  (2013,11,11) 

 =  ДАТА  (2013,11,28) 

Теперь мы можем объединить их в функцию:

 =  ЧИСТЫХ ДНЕЙ  ( A1 ,  B1 ,  C1: C3 ) 

Функция возвращает 57 как числовое значение.

ЧИСТРАБДНИ.INTL () (Excel 2010 и новее)

Для более новых версий Excel (2010 и более поздних версий) существует другая версия ЧИСТРАБДНИ под названием ЧИСТРАБДНИ.INTL . NETWORKDAYS.INTL работает так же, как NETWORKDAYS , но добавляет возможность настраивать определение «выходных». Синтаксис NETWORKDAYS.INTL следующий:

 =  NETWORKDAYS.INTL  ( начальная_дата ,  конечная_дата ,  [выходные] ,  [праздничные дни] ) 

Входные данные start_date , end_date и [праздничные дни] работают так же, как и обычная функция WORKDAY .

Вход [выходные] имеет следующие параметры:

ГОД ()

Иногда полезно измерить, сколько времени прошло в годах, но вычитание функции ГОД только округляет в меньшую сторону до ближайшего полного года. YEARFRAC берет две даты и предоставляет долю года между ними. Синтаксис следующий:

 =  YEARFRAC  ( начальная_дата ,  конечная_дата ,  [основа] ) 

start_date и end_date может быть любой ссылкой на ячейку в формате даты.

Ввод [базис] не является обязательным, но позволяет указать «правила» для измерения разницы. В большинстве случаев вы захотите использовать вариант 1, но вот полный список:

Например, если A1 содержит 10 сентября 2013 г. и B1 содержит 2 декабря 2013 г. , мы можем использовать YEARFRAC , чтобы найти десятичную часть года между двумя датами:

 =  ГОД RAC  ( A1 ,  B1 , 1) 

Возвращает 0.227397260273973 .

DATEDIF () (недокументированная функция)

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

 =  РАЗНДАТ  ( начальная_дата ,  конечная_дата ,  единица ) 

start_date и end_date может быть любой ссылкой на ячейку в формате даты.

Вход блока просит вас указать строку, которая представляет тип вывода, который вы хотите. Это немного громоздко, так как вы должны заключить ввод в кавычки («»).

Например, если A1 содержит 10 сентября 2012 г. и B1 содержит 2 декабря 2013 г. , мы можем использовать РАЗНДАТ , чтобы найти количество полных лет между двумя датами:

 =  РАЗНДАТ  ( A1 ,  B1 , "Y") 

Возвращает 1 в виде числового значения.

Используя одни и те же входы start_date и end_date , вот возможности вывода для DATEDIF с использованием разных параметров блока :

Каждый раз блок нужно заключать в кавычки (например, «Y» или «MD»).

Преобразование даты и времени из текста

ДАТАЗНАЧ () и ВРЕМЯЗНАЧ ()

Все вышеперечисленные функции отлично работают с серийными номерами в формате даты в Excel. К сожалению, даты и время часто импортируются на листы в виде текста.Большинство различных функций, таких как МЕСЯЦ и ЧАС , достаточно умны для преобразования на лету. Иногда бывает полезно построить значение даты путем конкатенации. Для этой цели Excel предоставляет две функции: DATEVALUE и TIMEVALUE . Синтаксис для каждого следующий:

 =  ЗНАЧЕНИЕ ДАТЫ  ( текст_даты ) 

 =  ВРЕМЕННОЕ ЗНАЧЕНИЕ  ( time_text ) 

date_text и time_text принимают любую текстовую строку, которая выглядит как дата или время.

Вот как DATEVALUE реагирует на различные входы date_text :

Вот как TIMEVALUE реагирует на различные входы time_text :

Преобразование даты и времени в текст

ТЕКСТ ()

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

 =  ТЕКСТ  ( значение ,  формат_текст ) 

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

Входной формат format_text имеет большое количество опций, кратко изложенных здесь:

Выводы можно комбинировать с простыми символами форматирования для создания стандартных форматов даты.Используя дату 5:07:03 am ( 05:07 часов, 3 секунды ) на 10 сентября 2013 г. , вот примеры возможных выходов:

Распространенная проблема

Одна из проблем, с которой часто сталкиваются люди, заключается в том, что Excel иногда неверно интерпретирует текстовые поля как даты. Пример здесь:

Будьте осторожны при вводе дат, особенно если вы импортируете данные из других источников, чтобы убедиться, что ваше «Jan-13» сохранено как 1 января 2013 года, а не 13 января 2013 года!

Эндрю Робертс уже более десяти лет решает бизнес-задачи с помощью Microsoft Excel.Программа Excel Tactics призвана помочь вам в этом. Вы можете прочитать больше о его работах в его личном блоге на NapkinMath.io.

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

Другие публикации в этой серии …

Определение выхода значения за допустимые пределы (Microsoft Excel)

Обратите внимание: Эта статья написана для пользователей следующих версий Microsoft Excel: 97, 2000, 2002 и 2003.Если вы используете более позднюю версию (Excel 2007 или более позднюю), , этот совет может вам не подойти . Чтобы ознакомиться с версией этого совета, написанного специально для более поздних версий Excel, щелкните здесь: Определение того, выходит ли значение за допустимые пределы.

У Дженнифер есть два числа, которые ей нужно сравнить в формуле.Если второе число находится в пределах 5% (плюс или минус) от первого числа, оно считается допустимым. Если второе число находится за пределами этого диапазона, то ей нужна формула, чтобы вернуть что-то вроде «вне пределов».

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

= ЕСЛИ ((A1-B1)> (A1 * 0.05), «вне пределов»,
ЕСЛИ ((B1-A1)> (A1 * 0,05), «вне пределов»,
"в пределах"))
 

Это работает нормально, но формула немного длинная. Вы можете добавить в формулу функцию ИЛИ, чтобы сделать ее немного короче:

= ЕСЛИ (ИЛИ (B1  A1 * 1,05); «вне пределов»; «в пределах»)
 

Вы можете сделать формулу еще короче, пропустив функцию ИЛИ и просто сравнив абсолютную разницу между значениями:

= ЕСЛИ (ABS ((B1-A1) / A1) <= 0.05, «в пределах», «вне пределов»)
 

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

= ЕСЛИ (A1 = 0, «неизвестно», ЕСЛИ (ABS ((B1-A1) / A1) <= 0,05,
"в пределах", "вне пределов"))
 

Если требуется, чтобы значения находились «в пределах 5% друг от друга», расчет будет несколько более сложным:

= ЕСЛИ (ABS (B1-A1) / MAX (ABS (B1), ABS (A1))> 0.05,
"вне пределов", "в пределах")
 

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

ExcelTips - ваш источник экономичного обучения Microsoft Excel. Этот совет (3803) применим к Microsoft Excel 97, 2000, 2002 и 2003.Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь: Определение выхода значения за пределы .

Автор Bio

Аллен Вятт

Аллен Вятт - всемирно признанный автор, автор более чем 50 научно-популярных книг и многочисленных журнальных статей. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги.Узнать больше о Allen ...

Исключение некоторых данных из диаграммы

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

Ускорение слияния почты

Инструмент слияния писем в Word - отличный способ создавать новые настраиваемые документы.