Excel上的文本和数字格式 [英] Text and Number Format on Excel
问题描述
我有问题,当我想比较a1 = 1.3 b1 = 3和a2 = 1.3 b2 = 2 on excel
i have question that when i want to compare between a1= 1.3 b1= 3 and a2= 1.3 b2= 2 on excel
例如= IF(a1> a2,是,IF(a1 = a2,IF(b1> b2,是,否),否))
for example =IF(a1>a2,"Yes",IF(a1=a2,IF(b1>b2,"Yes","No"),"no"))
第一次没关系,但是我改变了从b1 = 1的值改为否,然后我改变了回到b1 = 3它仍然是否
first time it's okay but then i changed value from b1 =1 it changed to "no" then i changed it back to b1= 3 it's still "no"
我认为它关注格式在单元格
i think it concern on format in the cell
另一个关心的是如果我使用1.3.1比较1.3.1.2它没有任何问题
another concern is if i use like 1.3.1 compare to 1.3.1.2 it doesn't have any problem
但是当我只使用1.3(一个小数点)它总是有问题
but when i use only 1.3 (one decimal point) it alway have problem
很难解释你应该尝试比较xx和xxx
it's hard to explain you should try to compare between x.x and x.x.x
如果你有一个很好的另一个VBA代码来比较版本号
also if you have a good another VBA code to compare version number
谢谢你,请告诉我我想学习一下吧
thank you please tell me i want to study about it
1小数点excel看到数量但是很多分数al point excel see as text
it like 1 decimal point excel see as number but many decimal point excel see as text
我如何解决这个问题(我已经尝试改变文本格式,但是在您更改单元格中的值后,它会工作1次) 。)
how do i fix this (i have try change to text format but it work 1 time after you change value in cell it's go back to error.)
由于我的英文很差。使它更清楚我如何使xx看作为文本,以便我可以与xxx比较:)
Due to my english is poor. to make it clearer "How do i make x.x see as text so that i can compare with x.x.x :) "
推荐答案
这是一篇你可能想查看的文章/文章。我对您的问题的第一印象是您可能正在尝试进行版本号比较。您的文本中的点分隔符的长度和数量可能非常重要。现在请检查一下,
Here is an article/ post you may want to check. The first impression I had on your question is that you may be trying to do a version number comparison. Length and number of dot delimiters in your text could matter greatly.for now please check this,
http://www.dbforums.com/microsoft-excel/1670840-compare-version-numbers-return-最高价值
否则您可以尝试日志
:
= A1 * 10 ^(4-INT(LOG(A1)))
=A1*10^(4-INT(LOG(A1)))
或者在尾部。
点,当然第二个文本变成小数:
Or do a replace on trailing .
dots and surely the second text becomes a decimal:
例如1.3.4将为1.34和1.3.4.1.3将为1.3413
E.g. 1.3.4 will be 1.34 and 1.3.4.1.3 will be 1.3413
1.2.5.6将为125.6和1.2.4.6.1将为124.61
1.2.5.6 will be 125.6 and 1.2.4.6.1 will be 124.61
PS:不在机器前面。将为您提供另外一个基于分割点分隔符和代码的代码。
PS: not front of a machine. Will provide you with another code I have based on split by dot delimiter and compare.
使用函数进行编辑:这将比较两个版本号与任意数量的点数,将其视为字符串/文本。但是在1.3.1和1.21.1的情况下,这是最高的1.21.1。
Edit with a function: this will compare two version numbers with any number of dot points, treating it as a string/text. However in the case of 1.3.1 and 1.21.1 this takes 1.21.1 as the highest number.
Option Explicit
Function versionNumberComparison(ByRef rng1 As Range, ByRef rng2 As Range) As String
Dim i As Integer
Dim arrVersion1 As Variant, arrVersion2 As Variant
Dim strVer1 As String, strVer2 As String
Dim bool2 As Boolean, bool1 As Boolean
Dim x As Long, y As Long
Application.EnableEvents = False
If Not IsEmpty(rng1.Value) Then
strVer1 = rng1.Value
arrVersion1 = Split(rng1.Value, ".")
Else
versionNumberComparison = "Version number empty"
GoTo Zoo
End If
If Not IsEmpty(rng2.Value) Then
strVer2 = rng2.Value
arrVersion2 = Split(rng2.Value, ".")
Else
versionNumberComparison = "Version number empty"
GoTo Zoo
End If
If UBound(arrVersion1) > UBound(arrVersion2) Then
x = UBound(arrVersion1)
y = UBound(arrVersion2)
ElseIf UBound(arrVersion1) < UBound(arrVersion2) Then
x = UBound(arrVersion2)
y = UBound(arrVersion1)
Else
x = UBound(arrVersion1)
y = x
End If
i = 0
While i <= y
If IsNumeric(arrVersion1(i)) And IsNumeric(arrVersion2(i)) Then
If CInt(Trim(arrVersion1(i))) = CInt(Trim(arrVersion2(i))) Then
If i = y Then
If x <> y Then
If Len(strVer1) > Len(strVer2) Then
bool1 = True
bool2 = False
GoTo PrintOut
Else
bool2 = True
bool1 = False
GoTo PrintOut
End If
End If
End If
bool1 = False
bool2 = False
ElseIf CInt(Trim(arrVersion1(i))) > CInt(Trim(arrVersion2(i))) Then
bool1 = True
bool2 = False
GoTo PrintOut
Else
bool2 = True
bool1 = False
GoTo PrintOut
End If
Else
versionNumberComparison = "Enter Valid version numbers"
GoTo Zoo
End If
i = i + 1
Wend
PrintOut:
If bool1 Then
versionNumberComparison = strVer1
ElseIf bool2 Then
versionNumberComparison = strVer2
Else
versionNumberComparison = "Both the same"
End If
Zoo:
Application.EnableEvents = True
End Function
输出:
这篇关于Excel上的文本和数字格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!