Как преобразовать завершающие знаки «минус» в настоящие отрицательные числа в Excel?
При работе с импортированными или унаследованными данными в Excel часто встречаются числа, у которых знак «минус» отображается в конце, например 90-, вместо того чтобы стоять в начале — -90. Такой формат с завершающим знаком «минус» обычно возникает из-за особенностей бухгалтерских систем или экспорта данных. Если его не исправить, такие значения будут мешать корректным числовым вычислениям, сортировке и анализу в Excel. Чтобы обеспечить точность и удобство работы с данными, их необходимо преобразовать так, чтобы знак «минус» стоял перед числом, превращая его в настоящее отрицательное число, распознаваемое Excel.
В этом руководстве описаны несколько эффективных способов преобразования завершающих знаков «минус» в корректные числовые значения в Excel. Каждый метод учитывает разные сценарии использования и уровни подготовки пользователей, помогая вам выбрать оптимальный подход для очистки данных.
Исправить все отрицательные числа в конце в Формула
Исправить все отрицательные числа в конце в ячейках с помощью функции «Текст по столбцам»
Исправить все отрицательные числа в конце в ячейках с помощью кода VBA
Быстро Исправить все отрицательные числа в конце в ячейках с помощью Kutools для Excel
Исправить все отрицательные числа в конце в Формула
Предположим, у вас есть диапазон ячеек, например A1:A11, содержащий числа с завершающими знаками «минус» вместо начальных. Поскольку Excel распознаёт только числа со знаком «минус» в начале для вычислений, формула предлагает гибкий и простой способ переформатировать такие значения. Этот метод особенно полезен, если вы хотите сохранить исходные данные без изменений и при этом получить динамическое решение, которое легко обновлять.
Чтобы преобразовать числа, используйте соседнюю пустую ячейку для ввода формулы. Например, щёлкните ячейку B1, расположенную рядом с вашими данными, и введите одну из следующих формул. После ввода формулы нажмите клавишу Enter — и значение в ячейке автоматически преобразуется в правильно отформатированное отрицательное число, если оно содержало завершающий знак «минус». Затем просто перетащите маркер заполнения вниз, чтобы применить формулу ко всем остальным ячейкам столбца и мгновенно исправить все значения. Этот метод сохраняет исходные данные в неизменном виде, выводя скорректированные результаты в отдельном столбце.
=IF(RIGHT(A1,1)="-",SUBSTITUTE(A1,"-","")*-1,A1)=VALUE(IF(RIGHT(A1,1)="-",RIGHT(A1,1)&,LEFT(A1,LEN(A1)-1),A1))
Пояснение к формуле и дополнительные советы:
- Предоставленные формулы обычно проверяют, является ли последний символ значения знаком минус («-»). Если это так, знак «минус» перемещается в начало; в противном случае число возвращается без изменений.
- Если ваши числа могут содержать пробелы или специальные символы, используйте функции вроде ПСТР или ПОДСТАВИТЬ, чтобы устранить несоответствия.
- После применения формул, если нужно заменить исходные данные, скопируйте результаты и воспользуйтесь командой Вставить специально > Значения, чтобы перезаписать исходный столбец значениями исправлений.
- Если возникают ошибки из-за текстового формата, убедитесь, что ячейка отформатирована как Общий или Числовой после применения формулы.
Преимущества: Не изменяет исходные данные — их легко проверить или отменить.
Ограничения: Могут потребоваться дополнительные шаги, если вы хотите заменить исходные значения; некоторые нестандартные форматы данных могут потребовать дополнительной настройки.
Исправить все отрицательные числа в конце в ячейках с помощью функции «Текст по столбцам»
Функция Excel «Текст по столбцам» предлагает альтернативный способ исправления чисел с завершающим знаком «минус». Эта встроенная функция особенно полезна при работе с большим объёмом данных: она распознаёт специфический бухгалтерский формат отрицательных чисел и автоматически преобразует значения вроде 90- в -90. Такое решение особенно эффективно, когда все затронутые числа находятся в одном столбце и имеют единообразный формат.
Выполните следующие шаги, чтобы использовать функцию «Текст по столбцам» для исправления завершающих знаков «минус»:
1. Выделите столбец или диапазон ячеек, содержащих числа с завершающими знаками «минус».
2. Перейдите на вкладку Лента и нажмите Данные > Текст по столбцам. Откроется мастер преобразования текста в столбцы. См. снимок экрана:
3. В мастере просто нажимайте кнопку Далее на шагах 1 и 2. Изменение разделителей не требуется, если вы не разделяете данные.
4. На шаге 3 нажмите кнопку Дополнительно…, чтобы открыть диалоговое окно «Дополнительные параметры импорта текста». Установите флажок «Завершающий минус для отрицательных чисел», затем нажмите ОК. См. снимок экрана:
5. Нажмите кнопку Готово, чтобы применить изменения. Теперь все числа с завершающим знаком «минус» будут преобразованы в стандартные отрицательные целые числа, которые распознаёт Excel.
Советы и устранение неполадок:
- Этот метод работает только с числовыми значениями, не содержащими другого текста.
- Если в ячейке присутствуют нечисловые символы, функция может не распознать и не преобразовать завершающий знак «минус».
- Всегда создавайте резервную копию данных перед запуском функции «Текст по столбцам», так как операция изменяет существующие значения на месте.
- Если изменения не отображаются мгновенно, дважды проверьте, выбрана ли правильная опция в окне Расширенные настройки, и убедитесь, что ваши данные действительно содержат завершающий знак «минус».
Преимущества: Работает быстро и не требует знаний формул или программирования; обрабатывает большой диапазон за один раз.
Ограничения: Перезаписывает существующие данные и не подходит для смешанных или нестандартных форматов.
Исправить все отрицательные числа в конце в ячейках с помощью кода VBA
Для пользователей, знакомых с макросами или желающих автоматизировать процесс для одного или нескольких диапазонов, простой скрипт VBA (Visual Basic for Applications) может быстро преобразовать все числа с завершающими знаками «минус» в стандартные отрицательные числа. Такой подход особенно подходит для многократного использования, больших наборов данных или случаев, когда требуется настраивать логику преобразования. Всегда сохраняйте свою работу перед запуском скриптов VBA, так как они вносят прямые изменения в таблицу.
Чтобы использовать решение на основе VBA, выполните следующие действия:
1. Выделите диапазон, содержащий числа, которые нужно исправить, затем перейдите на вкладку Разработчик > Visual Basic. В открывшемся окне Microsoft Visual Basic for Applications выберите команду Вставка > Модуль и вставьте следующий код VBA в модуль:
VBA: исправить все завершающие знаки «минус» в Выберите диапазон.
Sub FixNegative()
'Updateby20251113
Dim rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeConstants, xlTextValues)
For Each rng In WorkRng
xValue = rng.Value
If VBA.Right(xValue, 1) = "-" Then
rng.Value = "-" & VBA.Left(xValue, VBA.Len(xValue) - 1)
End If
Next
End Sub
2. После ввода кода нажмите кнопку 
«Выполнить» (или клавишу)F5), чтобы запустить макрос. Вам будет предложено выбрать диапазон, к которому нужно применить исправление. См. снимок экрана:
3. Нажмите ОК. Макрос просканирует выбранный диапазон и преобразует все значения с отрицательными знаками в конце в стандартные отрицательные числа. Ваши данные будут готовы для числовых вычислений и дальнейшего анализа.
Практические замечания:
- Если появится запрос системы безопасности, включите макросы, чтобы продолжить.
- Если код выполняется не так, как ожидалось, проверьте выделенный диапазон и убедитесь, что данные имеют единообразную структуру (например, только один дефис в конце каждой затронутой ячейки).
- Для больших наборов данных рекомендуется сначала протестировать код на выборке данных, чтобы подтвердить точность преобразования.
- Решения на основе VBA лучше всего применять к копиям данных, поскольку выполненные изменения невозможно отменить с помощью команды «Отменить».
Преимущества: Полностью автоматизировано, легко адаптируется под сложные задачи и может быть сохранено для многократного использования.
Ограничения: Требуется доступ к вкладке «Разработчик» и базовые знания VBA; отменить изменения с помощью команды «Отменить» после выполнения невозможно.
Быстро Исправить все отрицательные числа в конце в ячейках с Kutools для Excel
Если вы часто работаете с данными, импортированными из внешних систем, специализированный инструмент может упростить и ускорить этот процесс. Kutools для Excel предлагает удобную функцию под названием «Изменить знак числа», которая позволяет вам Исправить все отрицательные числа в конце всего за несколько щелчков. Это особенно полезно для пользователей без технических навыков или при работе с несколькими столбцами и запутанными данными, обработка которых в противном случае потребовала бы сложных формул или скриптов.
Kutools для Excel: более 300 полезных надстроек для Excel — бесплатная пробная версия без ограничений на 30 дней!Скачать сейчас
Если вы установили «Kutools для Excel», выполните следующие действия, чтобы Исправить все отрицательные числа в конце:
Шаг 1. Выделите диапазон, содержащий числа с отрицательными знаками в конце, которые нужно исправить. Затем перейдите в раздел Kutools > Содержимое > Изменить знак числа.
Шаг 2. В появившемся диалоговом окне установите флажок напротив опции Исправить все отрицательные числа в конце, затем нажмите кнопку ОК или Применить. Все числа с отрицательными знаками в конце в выбранном диапазоне будут немедленно преобразованы в стандартные отрицательные числа.
Функция «Изменить знак числа» в Kutools для Excel позволяет не только пакетно преобразовывать все положительные числа в отрицательные и наоборот, но и предлагает целый набор дополнительных инструментов для быстрого изменения знаков при работе с данными. Подробнее об этой функции — в описании Изменить знак числа.
Примечания и устранение неполадок:
- Обязательно проверьте выделение перед применением изменений — операция выполняется на месте и изменяет исходные данные.
- Если результаты оказываются неожиданными, проверьте, содержат ли данные числа, смешанные со специальными символами или текстовыми строками, которые могут потребовать дополнительной очистки.
- Функции Kutools предлагают удобные интерфейсы и диалоговые подсказки, снижая риск случайных ошибок.
Преимущества: Быстро, интуитивно понятно и доступно пользователям любого уровня — идеально подходит для пакетной обработки и сложных наборов данных.
Ограничения: Требуется установка Kutools для Excel; полный функционал доступен только при наличии действующей лицензии после окончания пробного периода.
- Преобразовать отрицательные числа в положительные
- Преобразовать положительные числа в отрицательные
- Изменить знаки значений в ячейках
- Заменить отрицательные числа на ноль
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек