Трюк №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 |
Чтобы применить этот макрос, выберите команду Разработчик → Редактор Visual Basic (Developer → Visual Basic Editor) (сочетание клавиш Alt/Option+FH). Теперь выберите команду Insert → Module и вставьте предыдущий код. Закройте окно, чтобы вернуться в Excel, и выберите команду Разработчик → Макросы (Developer → Macros), а затем выберите Convert Mirror Negatives. Щелкните кнопку Параметры (Options) и назначьте макросу клавиатурное сочетание. Теперь, когда нужно будет преобразовать импортированные отрицательные числа в настоящие отрицательные числа, которые сможет распознать Excel, нужно будет просто выделить их и использовать сочетание клавиш.