Excel文本格式,主键可以有字符串或数字格式,如何vlookup(Excel text format, primary key can have both string or numeric format, how to vlookup)
我有一个带有数字身份证号的excel表,每个客户:
这张照片上的第三个数字是在数字前加上一个引号:
'4346000273
。 它使excel将其读为文本编号。我使用这些数字作为
vba
代码中的Application.Vlookup
函数的第一个参数。我将数字存储为
String
类型的Client
类属性:Private identityNumber As String Public Property Let setIdentityNumber(value As String) identityNumber = value End Property Public Property Get getIdentityNumber() getIdentityNumber = identityNumber End Property 'inside another sub, which creates a list of clients: clientCopy.setIdentityNumber = .Cells(i, column_identity).value 'how I use the numbers in VLookup Function getInfo(clientIdentity As String, infoWorkbook As Workbook) resultValue = Application.VLookup(clientIdentity, _ .Range(rangeString), 4, 0)
我想知道处理这种情况的正确方法是什么。 我担心可能会发生这种情况,因为这种格式的差异,
Vlookup
无法找到数字。I have an excel sheet with numeric identity number for each client:
The third number on this picture was made with adding a quote before the number:
'4346000273
. It makes excel read it as text number.I use those numbers as the first argument for
Application.Vlookup
function invba
code.I store the numbers as a
Client
class property, of typeString
:Private identityNumber As String Public Property Let setIdentityNumber(value As String) identityNumber = value End Property Public Property Get getIdentityNumber() getIdentityNumber = identityNumber End Property 'inside another sub, which creates a list of clients: clientCopy.setIdentityNumber = .Cells(i, column_identity).value 'how I use the numbers in VLookup Function getInfo(clientIdentity As String, infoWorkbook As Workbook) resultValue = Application.VLookup(clientIdentity, _ .Range(rangeString), 4, 0)
I wonder what is that a correct way to handle such situation. I worry there might happen a time when because of this format difference
Vlookup
wouldn't be able to find the numbers.
原文:https://stackoverflow.com/questions/47391760
最满意答案
这是一个艰难的。 这有效:
scales = "sliced"
myLevelPlot = levelplot(myMatrix, at=myLevelsArbitrary, col.regions=myColors, column.values=dataForColumn.values, row.values=dataForRow.values, main="Title", xlab="Distance from center (cm)", ylab="Distance from center (cm)", scales = "sliced", colorkey=list(width=3) ) plot(myLevelPlot)
我不喜欢这两个警告,但有时警告可以忽略......
That's a tough one. This works:
scales = "sliced"
myLevelPlot = levelplot(myMatrix, at=myLevelsArbitrary, col.regions=myColors, column.values=dataForColumn.values, row.values=dataForRow.values, main="Title", xlab="Distance from center (cm)", ylab="Distance from center (cm)", scales = "sliced", colorkey=list(width=3) ) plot(myLevelPlot)
I don't like the two warnings, but sometimes warnings can be ignored...
相关问答
更多-
网格R中的水平图:如何获取单元格中显示的正确的小数位数(levelplot in lattice R: How to get right number of decimals displayed in cells)[2022-02-11]
myPanel <- function(x,y,z, ...){ panel.levelplot(x,y,z,...) panel.text(x,y, sprintf("%.2f", m)) } levelplot(m, colorkey=NULL, xlab=NULL, ylab=NULL, scales = list(x = list(draw = FALSE), y = list(draw = FALSE)), panel= myPanel) 您可 ... -
您必须使用ratify将raster数据声明为分类变量(请参阅此示例 ): library(rasterVis) nr <- 21 nc <- 11 m <- matrix(sample(c(0, 1), nr * nc, replace=TRUE), nr, nc) r <- raster(m) r <- ratify(r) rat <- levels(r)[[1]] rat$code <- c(0, 1) levels(r) <- rat levelplot(r) You have to us ...
-
你的范围不是simetrical。 另一种是这样的: max_abs <- max(abs(pv.df$fold.change)) brk <- do.breaks(c(-max_abs, max_abs), 11) levelplot(fold.change ~ comparison*compound, #p.value instead of p.adjust depending on map.signif pv.df, panel = myPanel, ...
-
您可以使用cex更改标题字体大小,例如: levelplot(z~x*y, colorkey=list(labels=list(cex=1, font=2, col="brown"), height=1, width=1.4, title=expression(m^3/m^3), row=3, column=1, vjust=2), ma ...
-
4字符短语JOHN未被识别为NON_QUOTED_VALUE标记的原因是JOHN = WASHBURN被识别为DOUBLE_QUOTED_VALUE。 使用以下跟踪检测语法将显示此信息(抱歉,Java代码,但我相信您可以翻译)。 NON_QUOTED_VALUE : IDCHAR_COMMON+ {System.out.println("#A:"+getText());}; DOUBLE_QUOTED_VALUE : NON_QUOTED_VALUE | (IDCHAR_COMMON ...
-
对于(i),您可以简单地reclassify栅格图层并将所有小于-20(或大于20)的值设置为-20.5(或20.5)。 我想这很有意义,因为无论如何你对较小或较大的值都不感兴趣。 至于(ii),我建议使用spplot并修改colorkey中详细描述的colorkey(来自包晶格 )。 请注意,可以使用Unicode字符(例如, 大于或等于 )轻松插入所需的标签。 ## reclassify data m_rcl <- matrix(c(-100, -20, -20.5, ...
-
您的问题有几个部分。 让我们一个一个地解决它们: 1:更改标签 。 这可以通过改变panel.text()的第三个参数来panel.text() : myPanel <- function(x, y, z, ...) { panel.levelplot(x, y, z, ...) panel.text(x, y, round(pv.df$p.value, 2)) } 2:更改颜色比例,白色位于0 。 计算色标的每个片段应该有多长,然后分别定义每个片段: color.ramp.length <- 2 ...
-
这是一个艰难的。 这有效: scales = "sliced" myLevelPlot = levelplot(myMatrix, at=myLevelsArbitrary, col.regions=myColors, column.values=dataForColumn.values, row.values=dataForRow.values, main="Title", xlab="Distance from center (cm)", ylab ...
-
levelplot使用背景颜色显示NA值。 因此,您必须使用panel.background修改此颜色。 library(rasterVis) myTheme <- BTCTheme() myTheme$panel.background$col = 'gray' f <- system.file("external/test.grd", package="raster") r <- raster(f) levelplot(r, par.settings = myTheme) levelplot us ...
-
我认为你需要传递一个RasterLayer对象到levelplot: library(rgdal) library(rasterVis) library(RColorBrewer) library(ncdf4) library(maptools) library(raster) r <- raster("/Users/christopher/Downloads/cases5.nc") colr <- colorRampPalette(brewer.pal(11, 'RdYlBu')) p <- leve ...