Эксель формула содержит неопознанный текст как исправить
с помощью импорта внешних данных с сайта получаю числовые данные. выходят они в таком формате 2,840.00 что означает 2840. Из-за последних трёх символов .00 невозможно вставить в формулу для автоматического расчёта. Как сделать либо автоматическое убирание этих символов, либо возможность расчёта данных чисел в формулах?
Какой же русский не любит быстрой езды - бессмысленной и беспощадной!
Нет такой чистой и светлой мысли, которую бы русский человек не смог бы выразить в грязной матерной форме.
Еще никто так, как русские, не глушил рыбу! (в Тихом океане - да космической станцией!)
думаешь, поможет? он же выдирает это со странички в браузере. соответственно, как страничка сделана, так он и выдерет. или ты не это имел в виду?
Настройка - Панель управления - Языки и стандарты - Числа - Разделитель целой и дробной части числа
Да, можно так. Но это тогда коснется всех программ.
Думаю наилучшее написать скрипт преобразования на VBA ввиде функции (т.е. подсьавляется в форулу). А сам скрипт прост: берет значение как текст, перебирает по каждому символу, запятые просто выкидывает, а точку меняет на запятую. Все, вопрос решен.
Вот, не поленился, написал.
Открой Ексел, запиши какой угодно макрос (главное чтоб он был). Теперь по открываешь окно VBA. Там найдешь раздел "Modules". В нем подраздел, по умолчанию "Module1". Открываешь этот подраздел. Там на VBA будет записан твой макрос. Нафиг стираешь его, а вместо него пишешь вот такой тест:
Сохраняешься. Теперь в итоговую ячеку прописываешь формулу, например =Convert_My(A5).
В ячейке А5 находится значение например 2,685.34, и функция будет возвращать в итоговую ячейку нормальной число 2685,34.
З.Ы. Подправил, ошибка была
2 v_user
По-моему загон, и по-любому должныа быть возможность в эксель это настроить.
Здесь, например: Меню "Сервис"-"Параметры", вкладка "Международные". Убрать галочку "Использовать системные разделители" и поставить нужный "Разделитель дробной и целой части". По крайней мере данные можно импортировать, потом изменить формат на нужный и сохранить. А можно прямо так и работать.
Прикрепленные файлы
2 v_user
По-моему загон, и по-любому должныа быть возможность в эксель это настроить.
Здесь, например: Меню "Сервис"-"Параметры", вкладка "Международные". Убрать галочку "Использовать системные разделители" и поставить нужный "Разделитель дробной и целой части". По крайней мере данные можно импортировать, потом изменить формат на нужный и сохранить. А можно прямо так и работать.
Так в том-то и дело, что это коснется всего Эксель. Да сколько уже раз сталкивался что люди изменив запятую на точку уже получали невнятный гемор. Одну свою задачу решали (а это решение через кривые руки программера который даже экспорт/импорт организовать не может по человечески), но другие наживали.
Что ж, если такой подход удовлетворяет, то ради бога. Только вот разбираться в числах, где запятая - разделитель разрядов, а точка - есть "десятичная точка" (вместо соответсвенно пробела и запятой) - думаю "продвинотому" бухгалтеру/менеджеру будет не весело.
Я просто думаю, что автор - не программер, это раз.
А следовательно, ему проще запомнить где галочка, чем весь твой скрипт. (без обид)
Переформатировал бы ты его, что ли. тут классный тэг есть - [code=auto:0]
читать удобнее, правда хайлайтинг и всякие загоны со шрифтами убивает.
Я просто думаю, что автор - не программер, это раз.
А следовательно, ему проще запомнить где галочка, чем весь твой скрипт. (без обид)
Переформатировал бы ты его, что ли. тут классный тэг есть - [code=auto:0]
читать удобнее, правда хайлайтинг и всякие загоны со шрифтами убивает.
Да не, какие обиды.
Просто меня иногда нервируют программисты, которые делают что-то через одно место и/или не до конца. Очень часто это у них в превычке (они так начинали). Вот я и акцентировал на том, что если делать, то так чтоб работало, а не чтоб сперва настроить а уж потом работало.
Кстати, переделал, спс за подсказку про тэг. И кстати изложить посторался как программировать как для новиска.
Ещё рекомендация.
Я когда информатикам задачи делал ради прикола ,
обнаружил, что если человек не знает инглиш, то знает транслит.
Можно изменить имена и ему все будет понятно (Кстати, если не ошибаюсь, по русски в офисе тоже можно программить).
И ещё: у тебя z_ish не используется, как я понял.
И зачем копировать str в ish?
Вот так например (не факт что не сломал, не тестил, по тексту должно работать):
У меня сейчас диск с офисом грохнулся, нет документации по бейсику и влом. Если хош, могу переделать в более человеческий вид на досуге.
Все верно. Так и надо рисовать. Я писал на скоро рука в данном случае, но вообще название переменных стараюсь всегда продумывать. Но грешок ест - люблю короткие имена внутри отдельновзятой функции (все равно дальше функции они не уходят).
По поводу z_ish. В Экселе я на ВБА не пишу, я периодически работаю в Access. Понятно, что ВБА что для экселя, что для аксэса одинаково. Так вот. Столкнулся вот конкретно с каким глюком. Когды ты работаешь внутри функции с "входящими" переменными, то можешь нарваться на глюк необоснованной неправильной работы.
Public Function StrokuVChislo(stroka As String) As Double
Если внутри этой функции ты много раз вызовешь переменную "stroka", то этот глюк может вылезти. Но стоит в начале обявить внутренюю переменную, ей присвоить значение "stroka", то эту переменную хоть миллион раз используй - все будет работать как положено. Вот такой, блин, глюк. С тех пор, как я на него потратил целый день (пока въехал в чем дело), у меня уже в автомате никогда не использовать входящие переменные, а сразу передавать их значение внутренним переменным.
Я немного подправил твое. Я считаю (ИМХО) что после точки возврата по ошибке ничего быть не должно (или или должно, но только ссылка на другую точку возврата). Ведь ты же не знаешь что будет содержаться в otvet в момент ошибки.
2v_user
в otvet в момент ошибки должен содержаться 0. Но только, если ошибка не произойдет во время присваиавания. (Тогда, по логике вещей, тоже должен быть ноль, но не уверен, признаюсь, момент упустил). Документации по VBA сейчас нет и я под него не пишу - просто по аналогии синтаксисов накатал. Так что думаю, что твой вариант живучей.
2v_user
в otvet в момент ошибки должен содержаться 0. Но только, если ошибка не произойдет во время присваиавания. (Тогда, по логике вещей, тоже должен быть ноль, но не уверен, признаюсь, момент упустил). Документации по VBA сейчас нет и я под него не пишу - просто по аналогии синтаксисов накатал. Так что думаю, что твой вариант живучей.
Все правильно. По идее должен быть ноль. Но:
- кто сказал что клюков не бывает у Микрософта?
- Это в данном случае так. Но часто бывает что приходится вносить изменения в код (особенно если он дастаточно большой), и в итоге ситуация меняется, а об действии после возврата по ошибке как-то забываешь. Сколько раз сам же на свои грабли наступал, теперь вот всегда этот момент контролирую (даже если и не надо, как в данном случае).
Кстати, еще. Нашел еще ошибочку у тебя. При объявлении переменных ты перешел на вторую строку. Просто так это нельзя делать, надо в конце первой строки после пробела добавить знак подчеркивания. (Да точки с запятой парачку в конце строки убрал).
Еще добавил вывод ошибки. Предпоследняя строка (здесь отремирована). Чтоб выводило - надо убрать апостроф.
Короче вот этот проверил, работает.
Кстати, еще. Нашел еще ошибочку у тебя. При объявлении переменных ты перешел на вторую строку. Просто так это нельзя делать, надо в конце первой строки после пробела добавить знак подчеркивания. (Да точки с запятой парачку в конце строки убрал).
Это все уже автоматом сделано (рефлекс) - у меня основной язык C++. Соответственно, по тому и точки с запятой в конце строк, и подчерка нет.
Что-то автора темы не слышно, видать решил уже задачку сам как-то.
Ну тут думаю надо делать Эксел в два листа.
Первый - промежуточный. Туда все импортируется. Все импортируется как текст! Второй лист итоговый - то что видно в Итоге. Там просто настрены ссылки на данные первого листа.
Когда экспортируешь как текст - все данные будут в том виде, как на сайте. А потом через ссылки (используя предложенну здесь функцию) числа сайта переводишь в числа понятные Эксель.
Ну тут думаю надо делать Эксел в два листа.
Первый - промежуточный. Туда все импортируется. Все импортируется как текст! Второй лист итоговый - то что видно в Итоге. Там просто настрены ссылки на данные первого листа.
Когда экспортируешь как текст - все данные будут в том виде, как на сайте. А потом через ссылки (используя предложенну здесь функцию) числа сайта переводишь в числа понятные Эксель.
Неполучается у меня, проверьте порядок действий
Порядок действий
Записываю любой макрос
Сервис-макрос-начать запись-ок
Alt f11, modules-module 1-кликая 2раза, в окно вставляю последнюю программу написанную v user
Сохраняюсь
На другом листе в ячейку пишу
=Convert_My(имя ячейки)
Пишет ошибку, формула содержит нераспознанный текст
И ещё, курс евро импортируется с точкой 1.2755 , что тоже не понятно экселю, для этого случая другой макрос?
Неполучается у меня, проверьте порядок действий
Порядок действий
Записываю любой макрос
Сервис-макрос-начать запись-ок
Alt f11, modules-module 1-кликая 2раза, в окно вставляю последнюю программу написанную v user
Сохраняюсь
На другом листе в ячейку пишу
=Convert_My(имя ячейки)
Пишет ошибку, формула содержит нераспознанный текст
И ещё, курс евро импортируется с точкой 1.2755 , что тоже не понятно экселю, для этого случая другой макрос?
Если ты взял последнюю программу - то тогда не Convert_My, а StrokuVChislo (ErV ее переименовал, когда переписывал скрипт более понятно).
Это функция присутствует в списке. Т.е. когда вызываешь список стандартных функций, то среди там ОКРУЛ, СУММ, ЕСЛИ и т.д. будет еще и функция StrokuVChislo.
Вполне вероятно, вы уже хорошо знакомы с этими мелкими ошибками. Одно случайное удаление, один неверный щелчок могут вывести электронную таблицу из строя. И приходится заново собирать/вычислять данные, расставлять их по местам, что само по себе может быть сложным занятием, а зачастую, невозможным, не говоря уже о том, что это отнимает много времени.
И здесь вы не одиноки: даже самые продвинутые пользователи Эксель время от времени сталкиваются с этими ошибками. По этой причине мы собрали несколько советов, которые помогут вам сэкономить несколько минут (часов) при решении проблем с ошибками Excel.
В зависимости от сложности электронной таблицы, наличия в ней формул и других параметров, быть может не все удастся изменить, на какие-то мелкие несоответствия, если это уместно, можно закрыть глаза. При этом уменьшить количество таких ошибок вполне под силу даже начинающим пользователям.
Несколько полезных приемов в Excel
Прежде чем перейти к ошибкам и советам, мы хотели бы поделиться несколькими полезными приемами, которые помогут избежать ошибок в дальнейшем, сэкономить массу времени и сделать вашу жизнь намного проще:
- Начинайте каждую формулу со знака «=» равенства.
- Используйте символ * для умножения чисел, а не X.
- Сопоставьте все открывающие и закрывающие скобки «()», чтобы они были в парах.
- Используйте кавычки вокруг текста в формулах.
Всем знакома маленькая зеленая стрелочка в верхнем левом углу ячейки. Вы знаете, этот противный флажок, который Excel использует, чтобы указать, что что-то пошло не так со значениями в ячейке.
Во многих случаях, нажав на эту стрелку, вы получите достаточно информации, чтобы решить проблему на месте. Вот так это выглядит:
Эксель требует, чтобы формулы содержали только цифры, и не будет отвечать на формулы, связанные с текстом, поэтому он покажет вам ошибку.
Простое решение этой ошибки — дважды проверить формулу, чтобы убедиться, что вы использовали только цифры. Если вы все еще видите ошибку, проверьте наличие пустых ячеек, пропущенных формул, связанных с ячейками, или каких-либо специальных символов, которые могли использовать.
В приведенном выше примере текст «Февраль» в ячейке G14 относится к текстовому формату. Программа не может вычислить сумму числа из ячейки A15 с текстом Февраль, поэтому дает нам ошибку.
Более сложная ошибка. Вот краткое изложение того, почему это может появиться в ячейке, в которой вы работаете.
Допустим, вы правильно написали формулу, но недостаточно информации, введенной в отдельные ее записи. Запись в массиве таблиц неполная. Требуется фактическое имя таблицы, чтобы узнать, где искать желаемое значение.
- Выделите ячейку, в которой вы хотите запустить формулу,
- Перейдите на вкладку «Формулы» в верхней части навигации.
- Выберите «Вставить функцию«. Если вы используете Microsoft Excel 2007, этот параметр будет находиться слева от панели навигации «Формулы».
После этого, в правой части вашей электронной таблицы появится Мастер функций, где вы сможете выбрать нужную формулу. Затем Excel проведет вас через каждый шаг формулы в отдельных полях, чтобы избежать ошибок и программа могла правильно прочитать вашу ячейку.
Как в Excel убрать решетки из ячейки?
Нажмите на правую границу заголовка столбца и увеличьте ширину столбца.
Вы можете дважды щелкнуть по правой границе заголовка, чтобы автоматически разместить самую широкую ячейку в этом столбце.
Лечится довольно просто. Измените значение на значение, не равное 0, или добавьте значение, если ваша ячейка была пустой.
Теперь, что на самом деле означает эта ошибка? Вы могли случайно удалить или вставить данные поверх ячейки, используемой формулой. Например, ячейка B16 содержит формулу =A14/F16/F17.
Если удалить строку 17, как это часто случается у пользователей (не именно 17-ю строку, но… вы меня понимаете!) мы увидим эту ошибку.
Здесь важно отметить, что не данные из ячейки удаляются, но сама строка или столбец.
Прежде чем вставлять набор ячеек, убедитесь, что нет формул, которые ссылаются на удаляемые ячейки. Кроме того, при удалении строк, столбцов, важно дважды проверить, какие формулы в них используются.
Если вы случайно удалили несколько ячеек, вы можете восстановить их нажатием кнопки «Отменить» на панели быстрого доступа. Сочетание клавиш CTRL + Z для Windows и Command + Z для Mac, также позволяет отменить последние внесенные изменения.
Чтобы дать вам некоторый дополнительный контекст, вот как работают справочные операторы Excel:
- Оператор диапазона (точка с запятой): определяет ссылки на диапазон ячеек.
- Оператор объединения (запятая): объединяет две ссылки в одну ссылку.
- Оператор пересечения (пробел): возвращает ссылку на пересечение двух диапазонов.
Прежде всего, убедитесь, что вы используете правильный синтаксис в формуле. Используйте двоеточие, чтобы отделить первую ячейку от последней, когда вы ссылаетесь на непрерывный диапазон ячеек в формуле. С другой стороны, использование запятой поможет правильно вывести формулу, если вы ссылаетесь на две ячейки, которые не пересекаются.
Как устранить эту ошибку
3 раза проверьте все свои формулы и внимательно посмотрите, какие листы или строки могут быть удалены или неправильно указаны. Если у вас есть несколько формул, связанных вместе, убедитесь, что в каждой формуле присутствуют значения.
И еще, при вводе формулы, исключите такие значения, как $ 1000, в формате валюты. Вместо этого введите 1000, а затем отформатируйте ячейку с валютой и запятыми после вычисления формулы. Просто число, без знака $ (доллар).
Как устранить эту ошибку
Проверьте, не ввели ли вы какую-либо отформатированную валюту, дату или спецсимвол. Обязательно удалите их из формулы, сохранив только цифры.
Заключение
Напишите в комментариях, а что вы думаете по этому поводу. Хотите узнать больше советов по Excel? Обязательно поделитесь этой статьей с друзьями.
Возможности Эксель позволяют выполнять вычисления практически любой сложности благодаря формулам и функциям. Однако иногда пользователи могут столкнуться с тем, что формула отказывается работать или вместо желаемого результата выдает ошибку. В данной статье мы рассмотрим, почему так получается, и какие действия предпринять для решения возникшей проблемы.
Решение 1: меняем формат ячеек
Очень часто Excel отказывается выполнять расчеты из-за того, что неправильно выбран формат ячеек.
Например, если задан текстовый формат, то вместо результата мы будем видеть просто саму формулу в виде обычного текста.
В некоторых ситуациях, когда выбран не тот формат, результат может быть посчитан, но отображаться он будет совсем не так, как мы хотели бы.
Очевидно, что формат ячеек нужно изменить, и делается это следующим образом:
Формат ячеек можно сменить с помощью другого инструмента, который позволяет задать более расширенные настройки.
- Выбрав ячейку (или выделив диапазон ячеек) щелкаем по ней правой кнопкой мыши и в открывшемся списке жмем по команде “Формат ячеек”. Или вместо этого, после выделения жмем сочетание Ctrl+1.
- В открывшемся окне мы окажемся во вкладке “Число”. Здесь в перечне слева представлены все доступные форматы, которые мы можем выбрать. С левой стороны отображаются настройки выбранного варианта, которые мы можем изменить на свое усмотрение. По готовности жмем OK.
- Чтобы изменения отразились в таблице, по очереди активируем режим редактирования для всех ячеек, в которых формула не работала. Выбрав нужный элемент перейти к редактированию можно нажатием клавиши F2, двойным кликом по нему или щелчком внутри строки формул. После этого, ничего не меняя, жмем Enter.
Примечание: Если данных слишком много, на ручное выполнение последнего шага потребуется немало времени. В данном случае можно поступить иначе – воспользуемся Маркером заполнения. Но этот работает только в том случае, когда во всех ячейках используются одинаковая формула.
Решение 2: отключаем режим “Показать формулы”
Когда мы вместо результатов видим сами формулы, это может быть связано с тем, что активирован режим показа формул, и его нужно отключить.
Решение 3: активируем автоматический пересчет формул
Иногда может возникать ситуация, когда формула посчитала какой-то результат, однако, если мы решим изменить значение в одной из ячеек, на которую формула ссылается, пересчет выполнен не будет. Это исправляется в параметрах программы.
Решение 4: исправляем ошибки в формуле
Если в формуле допустить ошибки, программа может воспринимать ее как простое текстовое значение, следовательно, расчеты по ней выполнятся не будут. Например, одной из самых популярных ошибок является пробел, установленный перед знаком “равно”. При этом помним, что знак “=” обязательно должен стоять перед любой формулой.
Также, довольно часто ошибки допускаются в синтаксисах функций, так как заполнить их не всегда просто, особенно, когда используется несколько аргументов. Поэтому, рекомендуем использовать Мастер функций для вставки функции в ячейку.
Иногда проще удалить формулу и написать ее заново, чем пытаться искать ошибку в уже написанной. То же самое касается функций и их аргументов.
Распространенные ошибки
В некоторых случаях, когда пользователь допустил ошибку при вводе формулы, в ячейке могут отображаться такие значения:
В случаях, когда приходится иметь со сложными функциями, которые ссылаются на много ячеек, можно воспользоваться инструментов проверки.
Также можно воспользоваться полезным инструментом “Проверка ошибок”, который расположен в том же блоке.
Откроется окно, в котором будет описана причина ошибки, а также предложен ряд действий касательно нее, в т.ч. исправление в строке формул.
Заключение
При ошибочных вычислениях, формулы отображают несколько типов ошибок вместо значений. Рассмотрим их на практических примерах в процессе работы формул, которые дали ошибочные результаты вычислений.
Ошибки в формуле Excel отображаемые в ячейках
В других арифметических вычислениях (умножение, суммирование, вычитание) пустая ячейка также является нулевым значением.
Несколько практических примеров:
В ячейке А1 – слишком большое число (10^1000). Excel не может работать с такими большими числами.
В ячейке А3 – квадратный корень не может быть с отрицательного числа, а программа отобразила данный результат этой же ошибкой.
Как убрать НД в Excel
В данном случаи пересечением диапазонов является ячейка C3 и функция отображает ее значение.
В данном примере ошибка возникал при неправильном копировании формулы. У нас есть 3 диапазона ячеек: A1:A3, B1:B4, C1:C2.
Под первым диапазоном в ячейку A4 вводим суммирующую формулу: =СУММ(A1:A3). А дальше копируем эту же формулу под второй диапазон, в ячейку B5. Формула, как и прежде, суммирует только 3 ячейки B2:B4, минуя значение первой B1.
Примечание. В данном случае наиболее удобнее под каждым диапазоном перед началом ввода нажать комбинацию горячих клавиш ALT+=. Тогда вставиться функция суммирования и автоматически определит количество суммирующих ячеек.
Решетки в ячейке Excel
Формула ЕСЛИОШИБКА обработки ошибок функции ВПР в Excel
Формула, изображенная на следующем рисунке уже изменена. Она использует функцию ЕСЛИОШИБКА и возвращает пустую строку в том случае если искомое значение не найдено в исходной таблице:
Пользователи часто называют эту функцию «скрывающая ошибки». Так как она позволяет определить и укрыть любые ошибки, которые можно после этого воспринимать по-другому. А не сметить этими некрасивыми кодами в отчетах для презентации.
Первый аргумент функции ЕСЛИОШИБКА – это выражение или формула, а во втором аргументе следует указать альтернативное значение, которое должно отображаться при возникновении ошибки. Если в первом аргументе выражение или формула вернет ошибку, тогда функция вместо его значения возвратит второй аргумент. В противные случаи будет возвращено значение первого аргумента.
В данном примере альтернативным значением является пустая строка (двойные кавычки без каких-либо символов между ними). Благодаря этому отчет более читабельный и имеет презентабельный вид. Данная функция может возвращать любое значение, например, «Нет данных» или число 0.
Функции для работы с кодами ошибок в Excel
Чтобы скрывать только определенную группу ошибок Excel предлагает еще 3 других функций:
Три выше описанные функции для обработки ошибок в Excel возвращают логические значения ИСТИНА или ЛОЖЬ наиболее часто используются вместе с функцией ЕСЛИ.
Формула ЕСЛИ и ЕДН для ошибок ВПР без функции ЕСЛИОШИБКА в Excel
Бескомпромиссная функция обработки ошибок ЕСЛИОШИБКА появилась в программе Excel начиная с 2010-й версии. Для проверки ошибок в старших версиях Excel наиболее часто использовалась функция ЕНД:
Главным недостатком такой формулы является необходимость дублировать функцию ВПР:
- первый разу внутри функции ЕНД;
- второй раз в третьем аргументе ЕСЛИ.
Это значит, что Excel должен два раза выполнять функцию ВПР для одной и той же ячейки. Если на листе содержится множество таких формул, тогда их пересчет требует много времени и системных ресурсов. Очень неудобно будет работать с такими файлами. Возникнет необходимость отключения автоматического пересчета формул: «ФОРМУЛЫ»-«Вычисления»-«Параметры вычислений»-«Вручную».
Читайте также: