Ошибка число сохранено как текст как убрать

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

В этой статье рассматриваются причины появления таких проблем и различные способы их устранения

Причина первая. Число сохранено как текст

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

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

Есть несколько способов решения данной проблемы

  • С помощью маркера ошибки и тега. Если в левом верхнем углу ячеек виден маркер ошибки (зелёный треугольник) и тег, то выделяем ячейки, кликаем мышкой по тегу и выбираем вариант Преобразовать в число
  • С помощью операции Найти/Заменить. Предположим, в таблице есть числа с десятичной запятой, сохраненные как текст. Выделяем диапазон с числами — нажимаем Ctrl+h (либо находим на вкладке Главная или в меню Правка для версий до 2007 команду Заменить) — в поле Найти вводим , (запятую) — в поле Заменить на тоже вводим , (запятую) — Заменить все. Таким образом, делая замену запятой на запятую, мы имитируем редактирование ячейки аналогично F2 — Enter

​

Подобную операцию можно осуществить и с датами с тем лишь отличием, что менять нужно точку на точку.

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

Аналогичную замену можно проделать и формулой (см. ниже), используя функцию ПОДСТАВИТЬ()

  • С помощью Специальной вставки. Этот способ более универсальный, так как работает и с дробными числами, и с целыми, а также с датами. Выделяем любую пустую ячейку — выполняем команду Копировать — выделяем диапазон с проблемными числами — Специальная вставка — Сложить — ОК. Таким образом, мы к числам (или датам) прибавляем 0, что никак не влияет на их значение, зато переводит в числовой формат

Вариантом этого приёма может быть умножение диапазона на 1

  • С помощью инструмента Текст по столбцам. Этот приём удобно использовать если преобразовать нужно один столбец, так как если столбцов несколько, то действия придётся повторять для каждого столбца отдельно. Итак, выделяем столбец с числами или датами, сохраненными как текст, устанавливаем формат ячейки Общий (для чисел можно установить, к примеру, Числовой или Финансовый). Далее выполняем команду Данные — Текст по столбцам — Готово

  • С помощью формул. Если таблица позволяет задействовать дополнительные столбцы, то для преобразования в число можно использовать формулы. Чтобы перевести текстовое значение в число, можно использовать двойной минус, сложение с нулём, умножение на единицу, функции ЗНАЧЕН(), ПОДСТАВИТЬ(). Более подробно можно почитать здесь. После преобразования полученный столбец можно скопировать и вставить как значения на место исходных данных

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

Приведу два примера макросов:

1) умножение на 1

Sub conv()
Dim c As Range
    For Each c In Selection
        If IsNumeric(c.Value) Then
            c.Value = c.Value * 1
            c.NumberFormat = "#,##0.00"
        End If
    Next
End Sub

2) текст по столбцам

Sub conv1()
    Selection.TextToColumns
    Selection.NumberFormat = "#,##0.00"
End Sub

Причина вторая. В записи числа присутствуют посторонние символы.

Чаще всего этими посторонними символами являются пробелы. Они могут располагаться как внутри числа в качестве разделителя разрядов, так и до/после числа. В этом случае, естественно, число становится текстом.

Убрать лишние пробелы также можно с помощью операции Найти/Заменить. В поле Найти вводим пробел, а поле Заменить на оставляем пустым, далее Заменить все. Если в числе были обычные пробелы, то этих действий будет достаточно. Но в числе могут встречаться так называемые неразрывные пробелы (символ с кодом 160). Такой пробел придётся скопировать прямо из ячейки, а затем вставить в поле Найти диалогового окна Найти/Заменить. Либо можно в поле Найти нажать сочетание клавиш Alt+0160 (цифры набираются на цифровой клавиатуре). 

Пробелы можно удалить и формулой. Варианты:

Для обычных пробелов: =—ПОДСТАВИТЬ(B4;» «;»»)

Для неразрывных пробелов: =—ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»)

Сразу для тех и других пробелов: =—ПОДСТАВИТЬ(ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»);» «;»»)

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

Источник

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

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

В этой статье

  • Способ 1. Преобразование чисел в текстовом формате с помощью функции проверки ошибок

  • Способ 2. Преобразование чисел в текстовом формате с помощью функции «Специальная вставка»

  • Способ 3. Применение числового формата к числам в текстовом формате

  • Отключение проверки ошибок

Способ 1. Преобразование чисел в текстовом формате с помощью функции проверки ошибок

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

Ячейки с зеленым индикатором ошибки в левом верхнем углу

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

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

    Выделение ячеек, диапазонов, строк и столбцов

    Чтобы выделить

    Выполните следующие действия

    Отдельную ячейку

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

    Диапазон ячеек

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

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

    Большой диапазон ячеек

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

    Все ячейки листа

    Нажмите кнопку Выделить все.

    Кнопка Выбрать все

    Чтобы выделить весь лист, также можно нажать клавиши CTRL+A.

    Если лист содержит данные, при нажатии клавиш CTRL+A выделяется текущий диапазон. Повторное нажатие клавиш CTRL+A приведет к выделению всего листа.

    Несмежные ячейки или диапазоны ячеек

    Выделите первую ячейку или диапазон ячеек, а затем, удерживая нажатой клавишу CTRL, выделите другие ячейки или диапазоны.

    Вы можете также выбрать первую ячейку или диапазон ячеек, а затем нажать клавиши SHIFT+F8 для включения в выделение других несмежных ячеек или диапазонов. Чтобы остановить включение ячеек и диапазонов, снова нажмите клавиши SHIFT+F8.

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

    Столбец или строку целиком

    Щелкните заголовок сроки или столбца.

    Лист с заголовком строки и заголовком столбца

    1. Заголовок строки

    2. Заголовок столбца

    Вы можете также выделить ячейки в строке или столбце, выделив первую ячейку, а затем нажав сочетание CTRL+SHIFT+клавиша со стрелкой (СТРЕЛКА ВПРАВО или СТРЕЛКА ВЛЕВО — для строк, СТРЕЛКА ВВЕРХ или СТРЕЛКА ВНИЗ — для столбцов).

    Если в строке или столбце содержатся данные, при нажатии сочетания CTRL+SHIFT+клавиша со стрелкой будет выделена строка или столбец до последней заполненной ячейки. Повторное нажатие этого сочетания приведет к выделению строки или столбца полностью.

    Смежные строки или столбцы

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

    Несмежные строки или столбцы

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

    Первую или последнюю ячейку в строке или столбце

    Выделите ячейку в строке или столбце, а затем нажмите клавиши CTRL+СТРЕЛКА (ВПРАВО или ВЛЕВО — для строк, ВВЕРХ или ВНИЗ — для столбцов).

    Первую или последнюю ячейку на листе или в таблице Microsoft Office Excel

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

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

    Ячейки до последней используемой ячейки листа (нижний правый угол)

    Выделите первую ячейку, а затем нажмите клавиши CTRL+SHIFT+END, чтобы расширить выделенную область до последней используемой ячейки листа (нижний правый угол).

    Ячейки до начала листа

    Выделите первую ячейку и нажмите сочетание клавиш CTRL+SHIFT+HOME, чтобы расширить выделение до начала листа.

    Больше или меньше ячеек, чем имеется в активном выделении

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

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

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

    Кнопка ошибки

  3. Выберите в меню пункт Преобразовать в число. (Чтобы просто избавиться от индикатора ошибки без преобразования, выберите команду Пропустить ошибку.)

    Команда "Преобразовать в число"

    Эта команда преобразует числа из текстового формата обратно в числовой.

    Преобразованные числа

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

К началу страницы

Способ 2. Преобразование чисел в текстовом формате с помощью функции «Специальная вставка»

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

  1. Выделите пустую ячейку и убедитесь в том, что она представлена в числовом формате «Общий».

    Проверка числового формата

    • На вкладке Главная в группе Число нажмите стрелку в поле Числовой формат и выберите пункт Общий.

      Изображение ленты Excel

  2. Введите в ячейку число 1 и нажмите клавишу ВВОД.

  3. Выделите ячейку и нажмите сочетание клавиш CTRL+C, чтобы скопировать значение в буфер обмена.

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

    Выделение ячеек, диапазонов, строк и столбцов

    Чтобы выделить

    Выполните следующие действия

    Отдельную ячейку

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

    Диапазон ячеек

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

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

    Большой диапазон ячеек

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

    Все ячейки листа

    Нажмите кнопку Выделить все.

    Кнопка Выбрать все

    Чтобы выделить весь лист, также можно нажать клавиши CTRL+A.

    Если лист содержит данные, при нажатии клавиш CTRL+A выделяется текущий диапазон. Повторное нажатие клавиш CTRL+A приведет к выделению всего листа.

    Несмежные ячейки или диапазоны ячеек

    Выделите первую ячейку или диапазон ячеек, а затем, удерживая нажатой клавишу CTRL, выделите другие ячейки или диапазоны.

    Вы можете также выбрать первую ячейку или диапазон ячеек, а затем нажать клавиши SHIFT+F8 для включения в выделение других несмежных ячеек или диапазонов. Чтобы остановить включение ячеек и диапазонов, снова нажмите клавиши SHIFT+F8.

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

    Столбец или строку целиком

    Щелкните заголовок сроки или столбца.

    Лист с заголовком строки и заголовком столбца

    1. Заголовок строки

    2. Заголовок столбца

    Вы можете также выделить ячейки в строке или столбце, выделив первую ячейку, а затем нажав сочетание CTRL+SHIFT+клавиша со стрелкой (СТРЕЛКА ВПРАВО или СТРЕЛКА ВЛЕВО — для строк, СТРЕЛКА ВВЕРХ или СТРЕЛКА ВНИЗ — для столбцов).

    Если в строке или столбце содержатся данные, при нажатии сочетания CTRL+SHIFT+клавиша со стрелкой будет выделена строка или столбец до последней заполненной ячейки. Повторное нажатие этого сочетания приведет к выделению строки или столбца полностью.

    Смежные строки или столбцы

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

    Несмежные строки или столбцы

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

    Первую или последнюю ячейку в строке или столбце

    Выделите ячейку в строке или столбце, а затем нажмите клавиши CTRL+СТРЕЛКА (ВПРАВО или ВЛЕВО — для строк, ВВЕРХ или ВНИЗ — для столбцов).

    Первую или последнюю ячейку на листе или в таблице Microsoft Office Excel

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

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

    Ячейки до последней используемой ячейки листа (нижний правый угол)

    Выделите первую ячейку, а затем нажмите клавиши CTRL+SHIFT+END, чтобы расширить выделенную область до последней используемой ячейки листа (нижний правый угол).

    Ячейки до начала листа

    Выделите первую ячейку и нажмите сочетание клавиш CTRL+SHIFT+HOME, чтобы расширить выделение до начала листа.

    Больше или меньше ячеек, чем имеется в активном выделении

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

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

  5. На вкладке Главная в группе Буфер обмена щелкните стрелку под кнопкой Вставить и выберите команду Специальная вставка.

  6. В группе Операция выберите вариант умножить и нажмите кнопку ОК.

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

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

Например, если в ячейке A2 содержится значение «156-«, приведенная ниже формула преобразует текст в значение «-156».

Данные

Формула

156-

=ЛЕВСИМВ(A2,ДЛСТР(A2)-1)*-1

К началу страницы

Способ 3. Применение числового формата к числам в текстовом формате

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

  1. Выделите ячейки, которые содержат числа, сохраненные в виде текста.

    Выделение ячеек, диапазонов, строк и столбцов

    Чтобы выделить

    Выполните следующие действия

    Отдельную ячейку

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

    Диапазон ячеек

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

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

    Большой диапазон ячеек

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

    Все ячейки листа

    Нажмите кнопку Выделить все.

    Кнопка Выбрать все

    Чтобы выделить весь лист, также можно нажать клавиши CTRL+A.

    Если лист содержит данные, при нажатии клавиш CTRL+A выделяется текущий диапазон. Повторное нажатие клавиш CTRL+A приведет к выделению всего листа.

    Несмежные ячейки или диапазоны ячеек

    Выделите первую ячейку или диапазон ячеек, а затем, удерживая нажатой клавишу CTRL, выделите другие ячейки или диапазоны.

    Вы можете также выбрать первую ячейку или диапазон ячеек, а затем нажать клавиши SHIFT+F8 для включения в выделение других несмежных ячеек или диапазонов. Чтобы остановить включение ячеек и диапазонов, снова нажмите клавиши SHIFT+F8.

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

    Столбец или строку целиком

    Щелкните заголовок сроки или столбца.

    Лист с заголовком строки и заголовком столбца

    1. Заголовок строки

    2. Заголовок столбца

    Вы можете также выделить ячейки в строке или столбце, выделив первую ячейку, а затем нажав сочетание CTRL+SHIFT+клавиша со стрелкой (СТРЕЛКА ВПРАВО или СТРЕЛКА ВЛЕВО — для строк, СТРЕЛКА ВВЕРХ или СТРЕЛКА ВНИЗ — для столбцов).

    Если в строке или столбце содержатся данные, при нажатии сочетания CTRL+SHIFT+клавиша со стрелкой будет выделена строка или столбец до последней заполненной ячейки. Повторное нажатие этого сочетания приведет к выделению строки или столбца полностью.

    Смежные строки или столбцы

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

    Несмежные строки или столбцы

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

    Первую или последнюю ячейку в строке или столбце

    Выделите ячейку в строке или столбце, а затем нажмите клавиши CTRL+СТРЕЛКА (ВПРАВО или ВЛЕВО — для строк, ВВЕРХ или ВНИЗ — для столбцов).

    Первую или последнюю ячейку на листе или в таблице Microsoft Office Excel

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

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

    Ячейки до последней используемой ячейки листа (нижний правый угол)

    Выделите первую ячейку, а затем нажмите клавиши CTRL+SHIFT+END, чтобы расширить выделенную область до последней используемой ячейки листа (нижний правый угол).

    Ячейки до начала листа

    Выделите первую ячейку и нажмите сочетание клавиш CTRL+SHIFT+HOME, чтобы расширить выделение до начала листа.

    Больше или меньше ячеек, чем имеется в активном выделении

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

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

  2. На вкладке Главная в группе Число нажмите кнопку вызова диалогового окна, расположенную рядом с надписью Число.

    Кнопка вызова диалогового окна в группе "Число"

  3. В поле Категория выберите нужный числовой формат.

    Для успешного выполнения данной процедуры числа, которые хранятся как текст, не должны содержать внутри или вне себя лишние пробелы или непечатаемые знаки. Лишние пробелы и непечатаемые знаки могут появиться при копировании или импорте данных из базы данных или другого внешнего источника данных. Для удаления лишних пробелов из нескольких чисел, которые хранятся в виде текста, можно воспользоваться функцией СЖПРОБЕЛЫ или функцией ПЕЧСИМВ. Функция СЖПРОБЕЛЫ удаляет из текста пробелы за исключением одиночных пробелов между словами. Функция ПЕЧСИМВ удаляет из текста все непечатаемые знаки.

К началу страницы

Отключение проверки ошибок

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

  1. Откройте вкладку Файл.

  2. В группе Справка нажмите кнопку Параметры.

  3. В диалоговом окне Параметры Excel выберите категорию Формулы.

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

  5. Нажмите кнопку ОК.

К началу страницы

Источник
 

peat

Пользователь

Сообщений: 119
Регистрация: 21.04.2013

#1

10.10.2013 23:08:47

Доброго времени суток!)
не закидывайте меня, тема избита, но ответа на свой вопрос я не нашел)

ситуация такая — тысячи ячеек с числами вида xy.z, а мне нужен разделитель запятая. делая замену — все хорошо. записываю замену макрорекодером, выполняю макрос — числа сохраняются, как текст о0
гугл дал вот такую хитрость:

Код 
With Selection
 .NumberFormat = "#,##0.00"
 .Replace ",", "."
 .Formula = .Formula
End With

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

Watcher_1

Пользователь

Сообщений: 155
Регистрация: 03.08.2013

Мои координаты в профиле

У меня нормально конветит даже без .Replace «,», «.»
 

The_Prist

Пользователь

Сообщений: 14429
Регистрация: 15.09.2012

Профессиональная разработка приложений для MS Office

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

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы…

 

peat

Пользователь

Сообщений: 119
Регистрация: 21.04.2013

вот это даа, вот это философский ответ) такого в литературе не прочтешь, наверное)
 

Слэн

Пользователь

Сообщений: 5192
Регистрация: 16.01.2013

философский — это насмешка, судя по смайлу :)

вот так у нас всегда, демократия, тоже ругательное слово, интеллигенция — еще пуще :)

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

peat

Пользователь

Сообщений: 119
Регистрация: 21.04.2013

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

KuklP

Пользователь

Сообщений: 14868
Регистрация: 21.12.2012

E-mail и реквизиты в профиле.

Гы) Все дружно утерлись после peat. …  :D

Я сам — дурнее всякого примера! …

 

peat

Пользователь

Сообщений: 119
Регистрация: 21.04.2013

#8

30.10.2013 08:19:46

снова всем доброго времени суток, возникла необходимость поднять тему)

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

Код 
With Range("M5:N" & LastRow)
    .Replace ".", ","
End With

For Each cell In Range("M5:N" & LastRow)
    cell.Formula = CDbl(cell.Formula)
Next cell

работает очень долго и, почему-то, вместо 23.456 получает 23 456, но вместо 0.0123 получает 0,0123)
Гуру, подскажите, как лучше сделать этот код? или вообще бросить затею и обязать пользователя делать замену вручную?
 

Слэн

Пользователь

Сообщений: 5192
Регистрация: 16.01.2013

пример данных небольшой можно?
 

peat

Пользователь

Сообщений: 119
Регистрация: 21.04.2013

Слэн
пожалуйста)

там около 5к значений, чтобы влезло на сайт. в реальности там по 150к значений)конечно, не все они нужны, нужно сделать выборку из них, с которой в дальнейшем и работать. может быть, есть какой-то способ замены в массиве? например, взять из этих 150к каждое 5ое число,занести в массив, там изменить, и добавить на график?

Прикрепленные файлы

  • 123.xlsx (97.65 КБ)
 

Слэн

Пользователь

Сообщений: 5192
Регистрация: 16.01.2013

#11

30.10.2013 09:09:10

Код 
Sub q()
Dim ar
ar = [a1].CurrentRegion
'Columns("d:e").NumberFormat = "##0.000"
[g1].Resize(UBound(ar), 2) = ar
End Sub

Живи и дай жить..

 

Слэн

Пользователь

Сообщений: 5192
Регистрация: 16.01.2013

на 150 тыс отработало секунды за 3 на моем небыстром
 

Hugo

Пользователь

Сообщений: 23706
Регистрация: 22.12.2012

#13

30.10.2013 10:12:53

Такой стандартный и понятный вариант:

Код 
Sub Macro1()
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
    Columns("B:B").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
End Sub

Почти ничего не менял в записи рекордера — хотя очевидно есть что сократить.
До например такого:

Код 
Sub Macro2()
    Columns("A:A").TextToColumns Destination:=Range("A1")
    Columns("B:B").TextToColumns Destination:=Range("B1")
End Sub

или даже

Код 
Sub Macro2()
    Columns(1).TextToColumns [a1]
    Columns(2).TextToColumns [b1]
End Sub

Изменено: Hugo — 30.10.2013 10:15:12
 

peat

Пользователь

Сообщений: 119
Регистрация: 21.04.2013

Слэн, да, очень быстро работает, спасибо)
Вы не могли бы пояснить синтаксис? в файле-примере работает шикарно, но в моем файле эти данные начинаются не с а1, а с м5 и простая замена результат не приносит — он смещается на пару строк вниз и запятую ставит не там, где надо, а на знак левее (т.е. последнее значение не 30,123, а 3,0123)
Hugo, не знаком с этим методом, к сожалению, с этим методом и последний ваш вариант скопировал данные с точками)
 

peat

Пользователь

Сообщений: 119
Регистрация: 21.04.2013

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

Изменено: peat — 30.10.2013 11:28:00
 

Hugo

Пользователь

Сообщений: 23706
Регистрация: 22.12.2012

«последний ваш вариант скопировал данные с точками» — ну значит там были не «Число сохранено как текст», а текст, похожий на число :)
Т.е. у меня с моими разделителями всё сработало как нужно.
 

peat

Пользователь

Сообщений: 119
Регистрация: 21.04.2013

Hugo, проблема изначально стояла в замене точек на запятые) но буду знать о таком вот методе избавления от ошибки «сохранено как текст», спасибо большое
 

Слэн

Пользователь

Сообщений: 5192
Регистрация: 16.01.2013

#18

30.10.2013 12:43:41

примерно…

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

Живи и дай жить..

Источник

Есть база в ней есть поле код наименования состоящее из цифр. Соответственно эксель понимает это поле как числовое и разрешает применять к нему только числовые фильтры. Но вот при поиске это очень неудобно поскольку обычно знаешь только какую либо часть кода соответственно тут идеальным является поиск как по тексту по критерию содержит. Для этого в формате ячейки выставляю значение Текстовый и применяю соответствующий _автофильтр_. Все работает но есть один момент, а именно когда выберешь формат текстовый эксель в углу каждой ячейки выводит предупреждение о том что число сохранено как текст можно конечно в ручную выбрать весь диапазон и ручками нажать пропустить ошибку но база довольно большая и периодически обновляется а при обновлении в углу ячейки опять высвечивается это предательское предупреждение. Отсюдова вопрос как можно программно заставить эксель пропустить эту ошибку.
То есть обновилась база и потом сработал код который выбрал пропустить эту ошибку для диапазона. Пытался записать макроредактором действие пропустить эту ошибку но он ничего не записал. Вот и пишу теперь сюда. А то с файлом будут работать несколько человек и не хочется слышать панические вопросы типа «а что это у меня тут за ошибка» или «Да тут ошибка оно наверно неправильно работает»

Не пытайтесь спорить с дебилом. Иначе вы опуститесь до его уровня. Где он задавит вас своим опытом.

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

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

Не пытайтесь спорить с дебилом. Иначе вы опуститесь до его уровня. Где он задавит вас своим опытом.

В принципе можно и не менять на текст но тогда вопрос сменяется обратным как в столбце с числами с помощью расширенного фильтра выбрать те что содержат 5 или 025. Мои попытки копать в этом направлении пока бесполезны. Приложу файл может кто знает как должно выглядеть условие

Не пытайтесь спорить с дебилом. Иначе вы опуститесь до его уровня. Где он задавит вас своим опытом.

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

Можете сделать так.

Private Sub Workbook_Open()
Application.ErrorCheckingOptions.NumberAsText = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ErrorCheckingOptions.NumberAsText = True
End Sub
Т.е. при открытии книги убираете галочку, перед закрытием ставите обратно.

Или лучше на Activate, Deactivate книги или листа.

Да это вариант на активацию листа событие засунуть.
В принципе пока наверно лучшее. Но все же мне уже стал интерес и обратный вопрос  как отфильтровать числа как текст.

Не пытайтесь спорить с дебилом. Иначе вы опуститесь до его уровня. Где он задавит вас своим опытом.

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

Не пытайтесь спорить с дебилом. Иначе вы опуститесь до его уровня. Где он задавит вас своим опытом.

Источник

4 Answers

Consider:

Sub Macro1()
    Application.ErrorCheckingOptions.NumberAsText = False
End Sub

This is a single line of a much larger macro to configure Excel when I begin working on a new computer.

answered Aug 19, 2013 at 15:36

Gary's Student's user avatar

Gary’s StudentGary’s Student

95.8k10 gold badges59 silver badges99 bronze badges

3

  • +1 I tried looking for that a while ago. The following wouldn’t work for me: Application.Range(Item.Address).Errors.Item(xlNumberAsText).Ignore = True. Thank you!

    Aug 25, 2017 at 10:55

  • @Brian See my EDIT

    Aug 25, 2017 at 12:13

  • This changes the global Excel settings of the user, which is probably not a good idea to do in a general purpose application program.

    Jul 19 at 3:06

The above examples turn off the «number stored as text» checking for the application.

This code will turn it off for a particular cell:

range("G93").Errors.Item(xlnumberastext).Ignore = True

Gufran Hasan's user avatar

Gufran Hasan

8,9407 gold badges38 silver badges52 bronze badges

answered Jul 22, 2019 at 14:48

James Hammontree's user avatar

You can select the range you want to work and then put for example (now added speechmarks):

Range("A1:Z20").Application.ErrorCheckingOptions.NumberAsText = False

Community's user avatar

answered Jun 15, 2016 at 10:17

user6468957's user avatar

2

  • That seems to turn it off for the entire workbooks, and not limit it only to the particular range.

    Mar 1, 2018 at 13:31

  • I agree with your observation, because the Range object has an Application property which refers to the Application in which that range exists. That line is essentially the same as just typing Application.ErrorCheckingOptions.NumberAsText = False

    Apr 10, 2018 at 18:28

Alternately, instead of formatting the cell as text, you could format the cell as 000000 to get the leading 0’s to show.

answered Aug 19, 2013 at 15:41

tigeravatar's user avatar

tigeravatartigeravatar

26.2k5 gold badges30 silver badges38 bronze badges

1

  • There may be good reason for storing the number as text, though, such as matching using VLOOKUP etc.

    Jan 23, 2018 at 16:25

Источник

Понравилась статья? Поделить с друзьями:
  • Ошибка четности ram ami bios
  • Ошибка числа равна
  • Ошибка четвертого цилиндра
  • Ошибка числа или значения oracle
  • Ошибка чипа картриджа hp m479