Трюк №73. Преобразование чисел со знаком минус справа
Вы когда-нибудь встречались с импортированными отрицательными числами, знак минус у которых стоял справа? SAP — это одна из программ, которые проделывают такое с отрицательными числами: например, 200- вместо -200. Изменение формата вручную — это весьма скучная работа, но не обязательно ее делать вручную.
Предположим, есть длинный список чисел, только что импортированных из другой программы, и некоторые из них являются так называемыми отрицательными числами. Ваша задача — преобразовать их в настоящие отрицательные числа, которые сможет распознать Excel. В этом упражнении мы будем использовать диапазон А1:А100. В ячейке В1 введите следующую формулу:
=SUBSTITUTE(IF(RIGHT(TRIM(A1))=»-«;RIGHT(TRIM(A1))&A1;A1);»-«;»»;2)+0 , в русской версии Excel =ПОДСТАВИТЬ(ЕСЛИ(ПРАВСИМВ(СЖПРОБЕЛЫ(А1))=»-«;ПРАВСИМВ(СЖПРОБЕЛЫ(А1))&А1;А1);»-«,»»;2)+0 .
Введите ее в такое количество ячеек этого столбца, сколько данных в столбце А1, а затем скопируйте введенные формулы и выделите столбец А1. Выберите команду Специальная вставка → Значения (Paste Special → Values), чтобы удалить формулу и оставить только значения. На рис. 6.16 показано, как список выглядел до применения формулы (диапазон А1:А7) и после.
Рис. 6.16. До и после перемещения знака минус
Чтобы понять, как работает эта формула, введите следующую формулу в любую ячейку. Ячейка А1 должна содержать значение 200-: =RIGHT(TRIM(A1);1)&A1 , в русской версии Excel =ПРАВСИМВ(СЖПРОБЕЛЫ(А1);1)&А1 . Вы получите результат -200-.
Функция СЖПРОБЕЛЫ (TRIM) просто гарантирует, что в ячейке нет символов пробела. Получив значение -200-, необходимо удалить второе вхождение знака минус. Это делает функция ПОДСТАВИТЬ (SUBSTITUTE). Вы приказали ей заменить второе вхождение символа — на «» (пустой текст). В результате возвращается текстовое значение, поэтому вы просто прибавляете к нему 0, и Excel преобразует это значение в число.
Если вам необходимо часто выполнять эту работу, нужно записать специальный макрос, упрощающий ее. Вот как это сделать. Макрос был оптимизирован, чтобы обеспечить максимальную скорость выполнения.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
// Листинг 6.2 Sub ConvertMirrorNegatives() Dim rCell As Range Dim rRange As Range Dim lCount As Long Dim lLoop As Long //Удостовериться, что данные выделены; если это не так, вывести _ //сообщение и закончить работу макроса. If Selection.Cells.Count = 1 Then MsgBox "Выберите диапазон для преобразования", vbInformation Exit Sub End If //Определить переменную ТОЛЬКО для текстовых значений, например 200- On Error Resume Next Set rRange = Selection.SpecialCells(xlCellTypeConstants, xlTextValues) //Если переменная возвращает Nothing, неправильных отрицательных чисел нет: //вывести сообщение и закончить работу макроса If rRange Is Nothing Then MsgBox "Зеркальных отрицательных чисел нет", vblnformation On Error GoTo 0 Exit Sub End If //Подсчитать, сколько ячеек содержат значения вида 200- и передать это число_ //в переменную для управления количеством проходов цикла. lCount = WorksheetFunction.CountIfCSelection. "*-") //Установить переменную на первую ячейку выделенного диапазона Set rCell = Selection.Cells(1. 1) //Выполнить цикл столько раз, сколько неправильных отрицательных чисел For lLoop = 1 То ICount //На каждом проходе цикла установить переменную на ячейку, содержащую *- //Звездочка - это символ шаблона Set rCell = rRange.Find(What:="*-". After:=rCell. _ LookIn:=xlValues, LookAt:=xlPart. _ SearchOrder:=xlByRows. SearchDirection:= _ xlNext. MatchCase :=False) //Использовать стандартное средство Replace для замены знака "минус" //пустым текстом. Другими словами, удалить его rCell.Replace What:="-". Replacement:="" //Умножить ячейку на -1, чтобы преобразовать в отрицательное число rCell = rCell * -1 Next lLoop On Error GoTo 0 End Sub
// Листинг 6.2 Sub ConvertMirrorNegatives() Dim rCell As Range Dim rRange As Range Dim lCount As Long Dim lLoop As Long //Удостовериться, что данные выделены; если это не так, вывести _ //сообщение и закончить работу макроса. If Selection.Cells.Count = 1 Then MsgBox «Выберите диапазон для преобразования», vbInformation Exit Sub End If //Определить переменную ТОЛЬКО для текстовых значений, например 200- On Error Resume Next Set rRange = Selection.SpecialCells(xlCellTypeConstants, xlTextValues) //Если переменная возвращает Nothing, неправильных отрицательных чисел нет: //вывести сообщение и закончить работу макроса If rRange Is Nothing Then MsgBox «Зеркальных отрицательных чисел нет», vblnformation On Error GoTo 0 Exit Sub End If //Подсчитать, сколько ячеек содержат значения вида 200- и передать это число_ //в переменную для управления количеством проходов цикла. lCount = WorksheetFunction.CountIfCSelection. «*-«) //Установить переменную на первую ячейку выделенного диапазона Set rCell = Selection.Cells(1. 1) //Выполнить цикл столько раз, сколько неправильных отрицательных чисел For lLoop = 1 То ICount //На каждом проходе цикла установить переменную на ячейку, содержащую *- //Звездочка — это символ шаблона Set rCell = rRange.Find(What:=»*-«. After:=rCell. _ LookIn:=xlValues, LookAt:=xlPart. _ SearchOrder:=xlByRows. SearchDirection:= _ xlNext. MatchCase :=False) //Использовать стандартное средство Replace для замены знака «минус» //пустым текстом. Другими словами, удалить его rCell.Replace What:=»-«. Replacement:=»» //Умножить ячейку на -1, чтобы преобразовать в отрицательное число rCell = rCell * -1 Next lLoop On Error GoTo 0 End Sub
Чтобы применить этот макрос, выберите команду Разработчик → Редактор Visual Basic (Developer → Visual Basic Editor) (сочетание клавиш Alt/Option+FH). Теперь выберите команду Insert → Module и вставьте предыдущий код. Закройте окно, чтобы вернуться в Excel, и выберите команду Разработчик → Макросы (Developer → Macros), а затем выберите Convert Mirror Negatives. Щелкните кнопку Параметры (Options) и назначьте макросу клавиатурное сочетание. Теперь, когда нужно будет преобразовать импортированные отрицательные числа в настоящие отрицательные числа, которые сможет распознать Excel, нужно будет просто выделить их и использовать сочетание клавиш.
Как убрать минус в excel перед числом
alihan95, если убрать минус, то это перестанет быть формулой.
У Вас нет желания показать в файле примере что есть — что нужно?
По вопросам из тем форума, личку не читаю.
Пользователь
Сообщений: 3007 Регистрация: 18.07.2016
19.12.2018 23:38:22
alihan95, и Вам добрый вечер!
Ну. например, если предположить, что «-052585» находится в ячейке «А1» .
Тогда можно будет предположить (исключительно как вариант), что если в какой-то другой ячейке разместить формулу
=ABS(A1)
то вполне возможно, что будет достигнут результат по убиранию минуса перед цифрами.
А если предположить, что «-052585» находится внутри какой-то формулы — то фиг его знает, формулу бы увидеть.
Тема: как убрать минус перед цифрами
покупка
Предположим, у вас есть диапазон данных со знаком плюса в разных позициях каждой ячейки, как показано на скриншоте ниже, и теперь вы хотите удалить знак плюса. Конечно, вы можете удалить знак плюса одну за другой, но если для удаления знака плюса требуются сотни ячеек, ручной метод будет тратить много времени. Здесь я расскажу о некоторых хитростях, позволяющих быстро удалить знак плюса из ячеек в Excel.
Удалите знак плюс или минус каждой ячейки с помощью Kutools for Excel
Удалите знак плюс и минус каждой ячейки с помощью Kutools for Excel
Удалите знак плюс или минус каждой ячейки с формулой
В Excel вот формулы, которые помогут вам удалить знак плюса или минуса из ячеек.
Выберите пустую ячейку рядом с вашими данными, например B1, и введите эту формулу = ПОДСТАВИТЬ (A1; «+»; «») + 0 , Затем нажмите Enter и перетащите дескриптор автозаполнения вниз к ячейкам, в которых нужна эта формула. Смотрите скриншоты:
Наконечник:
(1) Если в ваших данных есть знак минус, вы можете использовать эту формулу = ПОДСТАВИТЬ (A1; «-«; «») + 0 чтобы убрать знак минус с каждой ячейки.
(2) Вышеуказанные формулы могут работать только тогда, когда есть только числовые символы и знак плюс или минус..
Удалите знак плюс или минус каждой ячейки с помощью Kutools for Excel
Если ваши данные смешаны с числовыми и буквенными символами и другими специальными символами, как показано на скриншоте ниже, приведенные выше формулы не могут работать для вас. Не волнуйтесь, Kutools for ExcelАвтора Удалить символы Утилита может быстро удалить любой символ из строк по мере необходимости.
После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:
1. Выберите ячейки, которые вы хотите удалить, знак плюса, щелкните Кутулс > Текст > Удалить символы. Смотрите скриншот:
2. в Удалить символы диалог и проверьте На заказ вариант и тип + в текстовое поле ниже, вы можете просмотреть результаты в предварительный просмотр панель. Смотрите скриншот:
3. Нажмите Ok. Все знаки плюса удалены.
Наконечник: Если вы хотите убрать знак минус, просто нужно проверить На заказ вариант и тип – в текстовое поле в Удалить символы Диалог.
Удалите знак плюс и минус каждой ячейки с помощью Kutools for Excel
Если у вас есть диапазон ячеек со знаками плюс и минус, и вы хотите удалить все знаки плюс и минус, вы также можете использовать Kutools for ExcelАвтора Удалить символы утилита.
После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:
1. Выберите ячейки, в которых нужно удалить знаки, и нажмите Кутулс > Текст > Удалить символы.
2. Затем в Удалить символы диалог, проверьте На заказ вариант и тип +- в текстовое поле. Смотрите скриншот:
3. Нажмите Ok. И оба знака плюс и минус удаляются из выбранных ячеек.
Работы С Нами Kutools for ExcelАвтора Удалить символы, вы можете удалить только числовые символы, буквенные символы, нечисловые символы или непечатаемые символы. Щелкните здесь, чтобы узнать больше об удалении символов.
покупка
Как переместить знак минус справа налево / назад вперед в Excel?
В некоторых случаях вы можете встретить отрицательные числа со знаком минус сзади, как показано на скриншоте ниже. И перемещение этих минусовых знаков с конца на передний план значений должно быть пустой тратой времени, если нужно исправить сотни значений. Однако здесь я расскажу о некоторых приемах, которые помогут вам быстро исправить этот завершающий знак минус в Excel.
Перемещение знака минус назад вперед с текстом в столбцы
Перемещение знака минус назад вперед с помощью VBA
Переместите знак минус сзади вперед с помощью Kutools for Excel
Перемещение знака минус назад вперед с текстом в столбцы
Есть скрытая утилита в Текст в столбцы это может помочь вам переместить знак минус назад вперед.
1. Выберите числа, для которых нужно исправить знак минус в конце, и нажмите Данные > Текст в столбцы. Смотрите скриншот:
2. в Текст в столбцы мастер, щелкните прямо Следующая > Следующая , чтобы перейти к Шаг 3 из 3 диалоговое окно и щелкните Фильтр. Смотрите скриншот:
3. в Расширенные настройки импорта текста диалог, просто проверьте Завершающий минус для отрицательных чисел и нажмите OK закрыть его.
4. Затем нажмите Завершить закрывать Текст в столбцы диалоговое окно, а знак минус отрицательных чисел перемещен снизу вверх.
Перемещение знака минус назад вперед с помощью VBA
Если вы знакомы с VBA, вот один из них, который может вам помочь.
1. Нажмите Alt + F11 ключи одновременно, чтобы открыть Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модулии вставьте ниже код VBA в новый Модули окно.
VBA: переместить знак минус назад вперед (справа налево)
Sub FixTrailingNumbers() 'UpdatebyKutoolsforExcel20160409 Dim xRg As Range Dim xCol As Range Dim xTxt As String On Error Resume Next xTxt = ActiveWindow.RangeSelection.Address Set xRg = Application.InputBox("Select a range", "Kutools for Excel", xTxt, , , , , 8) If xRg Is Nothing Then Exit Sub For Each xCol In xRg.Columns xCol.TextToColumns Destination:=xCol, TrailingMinusNumbers:=True Next End Sub
3. Нажмите Run или F5 ключ для выполнения этого кода и Kutools for Excel Появится диалоговое окно, напоминающее о выборе диапазона для работы. Смотрите скриншот:
4. Нажмите OK, и теперь знаки минус отрицательных чисел исправлены правильно.
Переместите знак минус сзади вперед с помощью Kutools for Excel
Если описанные выше методы недостаточно просты, вы можете попробовать применить Изменить знак ценностей полезности Kutools for Excel — удобный инструмент Excel, который поможет вам быстро переместить знак минус с обратной стороны на переднюю.
После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:
1. Выберите числа, для которых нужно исправить знаки минус в конце, и нажмите Кутулс > Содержание > Изменить знак ценностей. Смотрите скриншот:
2. в Изменить знак ценностей диалог, проверьте Исправить конечные отрицательные знаки вариант. Смотрите скриншот:
3. Нажмите Ok or Применить, теперь знаки «минус» у отрицательных чисел исправлены снизу вверх.
Работы С Нами Изменить знак ценностей, вы можете преобразовать все положительные значения в отрицательные, исправить завершающие отрицательные знаки и изменить все отрицательные значения на ноль или изменить знак значений. Нажмите здесь, чтобы узнать больше.
Относительные статьи