Почему Excel и калькулятор считают по-разному?

Обновлено: 21.11.2024

Office 365 профессиональный плюс переименовывается в Приложения Microsoft 365 для предприятий. Для получения дополнительной информации об этом изменении прочитайте эту запись в блоге.

Обзор

Эта статья содержит и описывает формулы, которые вычисляют следующее:

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

Дополнительная информация

Формула для подсчета количества вхождений текстовой строки в диапазоне

=СУММ(ДЛСТР(диапазон)-ДЛСТР(ПОДСТАВИТЬ(диапазон,"текст","")))/ДЛСТР("текст")

Где диапазон — это рассматриваемый диапазон ячеек, а «текст» заменяется конкретной текстовой строкой, которую вы хотите подсчитать.

Приведенная выше формула должна быть введена как формула массива. Чтобы ввести формулу в виде массива в Excel для Windows, нажмите клавиши CTRL+SHIFT+ВВОД. Чтобы ввести формулу в виде массива в Excel для Macintosh, нажмите COMMAND+RETURN.

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

Пример 1. Подсчет количества вхождений текстовой строки в диапазоне

Запустите Excel и откройте новую книгу.

Введите на листе 1 следующее:

Значение ячейки A8 равно 4, поскольку текст "яблоко" встречается в диапазоне четыре раза.

Формула для подсчета количества вхождений одного символа в одной ячейке

=LEN(cell_ref)-LEN(ЗАМЕНИТЬ(cell_ref,"a",""))

Где cell_ref — это ссылка на ячейку, а «a» заменяется символом, который вы хотите подсчитать.

Эту формулу не нужно вводить как формулу массива.

Пример 2. Подсчет количества вхождений символа в одной ячейке

Используйте те же данные из предыдущего примера; предполагая, что вы хотите подсчитать количество вхождений символа "p" в A7. Введите следующую формулу в ячейку A9:

Значение ячейки A9 равно 3, поскольку символ "p" встречается в ячейке A7 три раза.

Формула для подсчета количества вхождений одного символа в диапазоне

=СУММ(ДЛСТР(диапазон)-ДЛСТР(ПОДСТАВИТЬ(диапазон,"а","")))

Где диапазон — это рассматриваемый диапазон ячеек, а «а» заменяется символом, который вы хотите подсчитать.

Приведенная выше формула должна быть введена как формула массива. Чтобы ввести формулу в виде формулы массива в Excel, нажмите клавиши CTRL+SHIFT+ВВОД.

Пример 3. Подсчет количества вхождений символа в диапазоне

Используйте те же данные из предыдущего примера; если вы хотите подсчитать количество вхождений или символ "p" в A2:A7. Введите следующую формулу в ячейку A10:

Значение ячейки A10 равно 11, так как символ "p" встречается 11 раз в A2:A7.

Формула для подсчета количества слов, разделенных символом в ячейке

=IF(LEN(TRIM(cell_ref))=0,0,LEN(cell_ref)-LEN(SUBSTITUTE(cell_ref,char,""))+1)

Где cell_ref — ссылка на ячейку, а char — символ, разделяющий слова.

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

Пример 4. Подсчет количества слов, разделенных пробелом в ячейке

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

Запустите Excel и откройте новую книгу.

Введите на листе 1 следующее:

Формула в ячейке A2 возвращает значение 4, чтобы отразить, что строка содержит четыре слова, разделенные пробелами. Если слова разделены несколькими пробелами или слова начинаются или заканчиваются пробелом, это не имеет значения. Функция TRIM удаляет лишние пробелы, а также начальные и конечные пробелы в тексте ячейки.

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

Ссылки

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

89794 Как использовать Visual Basic для приложений для подсчета вхождений символа в выделенном фрагменте в Excel

Обзор

В Microsoft Excel все открытые в данный момент документы используют один и тот же режим вычислений, независимо от режима, в котором они были сохранены.

Дополнительная информация

Чтобы объяснить, как определяется способ расчета, в этой статье используются следующие гипотетические документы:

< /tr>

Имя файла	Вид документа	Режим, используемый для сохранения файла
Auto1.xlsx	Workbook	Automatic
Manual1.xlsx	Рабочая книга	Вручную
Auto2.xlsx	Рабочая книга	Автоматически

Следующие утверждения относятся к режимам расчета в Excel:

Для первого открываемого документа используется режим расчета, в котором он был сохранен в последний раз. Документы, открытые позже, используют тот же режим. Например, если открыть Auto1.xlsx, а затем открыть Manual1.xlsx, в обоих документах будет использоваться автоматический расчет (режим, используемый Auto1.xlsx). Если вы откроете Manual1.xlsx, а затем Auto1.xlsx, в обоих документах будут использоваться расчеты вручную.
При изменении режима расчета одного открытого документа изменяется режим для всех открытых документов. Например, если Auto1.xlsx и Auto2.xlsx открыты, изменение режима расчета Auto2.xlsx на ручной также приводит к изменению режима Auto1.xlsx на ручной.
Все листы, содержащиеся в рабочей книге, используют один и тот же режим расчета. Например, если Auto2.xlsx содержит три листа, изменение режима расчета первого листа на ручной также приведет к изменению режима расчета на ручной на двух других листах.
Если все другие документы закрыты и вы создаете новый документ, в новом документе используется тот же режим расчета, что и в ранее закрытых документах. Однако, если вы используете шаблон, режим расчета — это режим, указанный в шаблоне.
Если режим расчета в рабочей книге изменился и файл сохранен, текущий режим расчета сохраняется. Например, если открыть Auto1.xlsx, открыть Manual1.xlsx и сразу же сохранить Manual1.xlsx, режим расчета сохраняется как автоматический.

Как управлять режимом расчета

Все открытые документы используют один и тот же режим расчета. Для работы с документами, использующими разные режимы расчета, необходимо соблюдать специальные процедуры. Например, если вы работаете с Auto1.xlsx и хотите открыть Manual1.xlsx в ручном режиме расчета, выполните одно из следующих действий:

Перед открытием файла Manual1.xlsx установите Auto1.xlsx в режим ручного расчета.
Закройте Auto1.xlsx (и все другие открытые документы) перед открытием Manual1.xlsx.

В Excel можно выбрать четыре режима расчета. Они следующие:

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

Функция COUNT подсчитывает количество ячеек, содержащих числа, и подсчитывает числа в списке аргументов. Используйте функцию COUNT, чтобы получить количество записей в числовом поле, которое находится в диапазоне или массиве чисел. Например, вы можете ввести следующую формулу для подсчета чисел в диапазоне A1:A20: =COUNT(A1:A20). В этом примере, если пять ячеек в диапазоне содержат числа, результатом будет 5.

Синтаксис

Синтаксис функции COUNT имеет следующие аргументы:

значение1 Обязательно. Первый элемент, ссылка на ячейку или диапазон, в котором вы хотите подсчитать числа.

значение2, . Необязательный. До 255 дополнительных элементов, ссылок на ячейки или диапазонов, в которых вы хотите подсчитывать числа.

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

Примечания

Учитываются аргументы, которые являются числами, датами или текстовым представлением чисел (например, число, заключенное в кавычки, например "1").

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

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

Если аргумент является массивом или ссылкой, учитываются только числа в этом массиве или ссылке. Пустые ячейки, логические значения, текст или значения ошибок в массиве или ссылке не учитываются.

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

Если вы хотите подсчитывать только числа, соответствующие определенным критериям, используйте функцию СЧЁТЕСЛИ или функцию СЧЁТЕСЛИМН.

Пример

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

Мой рабочий лист Excel дает другое решение, использующее ту же формулу. Решение отличается только около 20-го знака после запятой.

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

У меня есть 3 входа и вывод простой дроби

Ставка дивидендов (B1): 0,0668181818181818

Дивиденды: (B2): =B1/6*100

Цена в момент времени 0 (B3): 108,497457761148

Цена на момент 1 (B4): 108,414638813454

Простое решение 1: =(B2-B3+B4)/B3 дает 0,00950084395720738

Простое решение 2: =(-B3+B4+B2)/B3 дает 0,00950084395720741

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

В чем может быть ошибка?

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

Сообщить о нарушении

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

Ответы (9) 

Я попробовал ваши формулы на двух своих компьютерах и получил 0,00950084395720738 ОБОИХ решений 1 и 2.

[1] Windows XP с Excel 2003.

[2] Windows 7 с Excel 2016.

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

Сообщить о нарушении

2 человека сочли этот ответ полезным

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

Большое спасибо, что попробовали это для меня. Кажется, что следование именно тому сценарию, который я дал, действительно дает те же результаты. Ошибку можно воспроизвести, когда дивиденд (ячейка B2) в точности равен другой ячейке, коэффициент дивиденда (ячейка b1), умноженный на 100 и разделенный на 6 (полугодовой), который я для краткости исключил ранее (когда получается быть обязательным.)

Мне очень жаль, но я был бы очень признателен, если бы вы попробовали это для меня.

Ставка дивидендов задается в ячейке B1 как: 0,0668181818181818

ячейка b2 вместо этого: =B1/6*100

Я также обновил это в исходном сообщении.

Сообщить о нарушении

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

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

Я добавил ваше изменение (=B1/6*100), и теперь у меня тоже разные результаты в Решении 1 и 2 (такие же, как и у вас). Я пробовал это как в Excel 2016, так и в Excel 2003 с теми же результатами.

Вы можете попробовать следующее:

В Excel 2016/2013/2010 (более старые версии Excel будут иметь другие инструкции, которые я не привожу в этом посте):

[1] Нажмите на файл

[2] Нажмите «Параметры»

[3] Перейдите на вкладку "Дополнительно"

[4] Прокрутите вниз до раздела «При расчете этой книги»

[5] Установите флажок "Установить отображаемую точность"

[6] Нажмите OK, и появится всплывающее окно «Данные навсегда потеряют точность».

После выполнения этих шагов у вас должно быть 0,00950084395720738 ОБА для решения 1 и решения 2.

Сообщить о нарушении

19 человек сочли этот ответ полезным

Был ли этот ответ полезен?

Извините, это не помогло.

Отлично! Благодарим за отзыв.

Насколько вы удовлетворены этим ответом?

Спасибо за ваш отзыв, он помогает нам улучшить сайт.

Насколько вы удовлетворены этим ответом?

Спасибо за отзыв.

Ставка дивидендов (B1): 0,0668181818181818
Дивиденды: (B2): =B1/6*100

Цена на момент 0 (B3): 108,497457761148

Цена на момент время 1 (B4): 108,414638813454

Простое решение 1: =(B2-B3+B4)/B3 Дает 0,00950084395720738

Простое решение 2: =(-B3+B4+B2) /B3 дает 0,00950084395720741

[. ]

В чем может быть ошибка?

И еще один пример:

=(B2+B4)/B3 – 1 дает 0,00950084395720729

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

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

Чтобы добавить путаницы, если мы реализуем все три формулы в VBA с переменными типа Double (не типа Variant), мы получим тот же результат, а именно: 0,00950084395720741.

Я предполагаю, что это просто любопытство, и разница на самом деле не имеет для вас значения.

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

Более того, вместо констант с 15 значащими цифрами следует, если возможно, ввести формулы, которые их генерируют. Например, я предполагаю, что цены (B3 и B4) получены из NAV, которая обычно имеет точность до 2 или 4 знаков после запятой, разделенную на количество акций фонда, которое обычно имеет точность от 2 до 8 знаков после запятой.

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

Например, то, что выглядит как 108,497457761148 в B3, может быть любым из 71 более точного значения от 108,497457761147,501287268823944032192230224609375 до 108,497457761148,4960457909888840844. К сведению, 108.497457761148 внутренне представлен как 108.497457761147,9986671838560141623020172119140625.

(Я использую точку в качестве десятичной точки и запятую для разграничения первых 15 значащих цифр.)

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

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

Следовательно, большинство нецелых чисел не могут быть точно представлены. Вместо этого они аппроксимируются суммой 53 последовательных степеней 2 ("битов"), умноженных на двоичный экспоненциальный множитель.

Более того, большинство ПК имеют процессоры, совместимые с Intel. Они выполняют арифметические операции, используя 80-битное двоичное представление с плавающей запятой, которое представляет собой сумму 64 последовательных степеней, умноженных на двоичный экспоненциальный множитель.

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

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

В вашем примере B2 составляет около 1,11363636363636, а B3 — около 108,497457761148. Их величины (двоичный показатель степени) отличаются на 6 бит. B2-B3 могут быть представлены в 80-битной форме (VBA) без потери точности. Но мы можем потерять некоторую точность, когда Excel округлит до 64-битной формы.

(В общем случае я говорю "может быть", потому что точность теряется, только если округленные биты отличны от нуля. В этом примере это так.)

Напротив, B4 составляет около 108,414638813454, что соответствует той же величине, что и B3. Таким образом, при вычислении -B3+B4 (B4-B3) точность не теряется, даже если Excel округляет промежуточный результат до 64 бит.

С численной точки зрения лучше (точнее) сгруппировать расчеты с одинаковой величиной, если это возможно. Таким образом, B4-B3+B2 точнее, чем B2-B3+B4.

Наконец, деление может привести к непредсказуемому количеству дополнительных битов точности, если бы не конечные ограничения. Таким образом, (a+b)/c может сильно отличаться от a/c + b/c в компьютерной арифметике, хотя математически они одинаковы.

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

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

Сообщить о нарушении

Читайте также: