Трюк №73. Преобразование чисел со знаком минус справа


Трюк №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. До и после перемещения знака минус

Рис. 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, нужно будет просто выделить их и использовать сочетание клавиш.

18.11.20124592

»Трюки с формулами и функциями » Трюк №73. Преобразование чисел со знаком минус справа




Читайте ранее:
Трюк №72. Как в Excel 2010 добавить расширение данных

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

Закрыть