Трюки со встроенными возможностями Excel
08.09.2012 19350

Трюк №27. Как в Excel преобразовать текстовые числа в настоящие числа

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

Помните, что числа в Excel по умолчанию выравниваются по правому краю, а текст — по левому. Простой способ идентифицировать проблемные текстовые числа в столбце, который вы считаете полностью числовым, — выделить столбец, выбрать команду Формат → Ячейки → Выравнивание (Format → Cells → Alignment), убедиться, что выбрано выравнивание по умолчанию По значению (General), и щелкнуть кнопку ОК. Расширьте столбец, и вы увидите, что все настоящие числа будут выровнены по правому краю, а проблемные текстовые числа — по левому. Даты также будут выровнены по правому краю, так как в основе даты лежит не что иное, как число1.

Теперь, когда вы знаете, что у вас есть числа, отображаемые как текст, мы предлагаем быстрый и простой способ преобразования их в настоящие числа, чтобы Excel смог использовать эти числа в вычислениях. Скопируйте любую пустую ячейку и затем выделите ваш список чисел. Выберите команду Правка → Специальная вставка (Edit → Paste Special) и в группе Вставить (Paste) выберите переключатель Значения (Values). В группе Операция (Operation) выберите переключатель Сложить (Add) и щелкните на кнопке ОК.

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

Эту же логику можно применить к некоторым стандартным функциям Excel, в частности, к текстовым функциям, например, ТЕКСТ (TEXT). Обычно, когда вы применяете любую текстовую функцию Excel и в качестве результата возвращается число, Excel возвращает это число как текстовое, а не числовое значение.

Предположим, у вас есть диапазон ячеек, начиная с $А$1. Каждая ячейка содержит знак доллара и какое-то число, за которым через пробел указано имя человека. Используя следующую формулу, в которой комбинируются две текстовые функции — ЛЕВСИМВ (LEFT) и НАЙТИ (FIND), можно получить это число: =LEFT(A1;FIND("";A1)-1), в русской версии Excel =ЛЕВСИМВ(А1;НАЙТИ("";А1)-1).

Если ячейка А1 содержит данные $22.70 Fred, то в результате формула вернет значение $22.70. Однако оно будет возвращено как текст, а не как настоящее числовое значение; таким образом, по умолчанию в ячейке оно будет выровнено по левому краю.

Можно модифицировать формулу, чтобы результат возвращался не как текст, а как настоящее числовое значение, добавив к нему 0: =LEFT(A1;FIND("";A1)-1)+0, в русской версии Excel: =ЛЕВСИМВ(А1;НАЙТИ("";А1)-1)+0. Это заставит Excel вернуть значение как число, таким образом, по умолчанию оно будет выровнено по правому краю. Все, что нужно после этого сделать, — соответствующим образом отформатировать ячейку.

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

Чтобы создать пользовательскую функцию, нажмите сочетание клавиш Alt/Option+Fll, выберите команду Insert → Module и введите код из листинга 2.1.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

// Листинг 2.1
Function ExtractNumber(rCelI As Range)
Dim l Count As Long, 1 As Long
Dim sText As String
Dim lNum As String
 
	sText = rCell
 
		For lCount = Len(sText) To 1 Step -1
			If IsNumeric(Mid(sText, lCount. l) Then
				L - l + 1
				lNum = Mid(sText. lCount, 1) & lNum
			End If
 
			If l = 1 Then lNum = Clnt(Mid(lNum. 1, D)
		Next ICount
 
ExtractNumber = Clng(lNurn)
End Function


Нажмите сочетание клавиш Alt/Apple+Q, чтобы сохранить функцию. В диалоговом окне Мастер функций (Paste Function) она появится в области Определенные пользователем (User Defined). столбец А содержит текст вместе с числами, столбец В содержит результат выполнения функции ExtractNumber, а столбец С демонстрирует, как формула выглядит в столбце В.

1  В современных версиях для этой цели служит смарт-тег ошибки ввода. Добиться повторения результата автора нам не удалось. Операция сложения (с ненулевым значением!) выполняется, но выравнивание соответствует текстовому формату чисел в ячейке.

•
Top