我有两张Excel表格。
以下是这两张表中的一个例子。
sheet1
a 1
b 2
d 1
e 3
g 1
sheet2
a
b
c
d
e
f
我想在第2个工作表的B1单元格中放置一个公式，并将其向下拖动，以便生成结果的第2个工作表。
sheet2
a 1
b 2
c 0
d 1
e 3
f 0
explanation : - a = 1 because same value in book1
b = 2 because same value in book1
c = 0 because c does not exist in book1
d = 1 because same value in book1
e = 3 because same value in book1
f = 0 because f does not exist in book1
我可以在表2的B列中使用什么公式？
一个 if()、iserror() 和 vlookup() 的组合将是你此处最好的选择。
假设您从第一张工作表中获取的数据在名为refdata的范围内。
=IF(ISERROR(VLOOKUP(A1,refdata,2,FALSE)),0,VLOOKUP(A1,refdata,2,FALSE))
应该做你需要的事（其中A1是包含您想要匹配的数据的单元格）。
在Excel 2007中它看起来像这样：
=IFERROR(VLOOKUP(A1,sheet1data,2,0),)
For an Excel formula I need the first cell out of a list of cells which contains a numeric value: A | B | C | ... | Z | ----------------------------- | 0.1 | 0.4 | ... | =0.1 | | ...
I have a stored procedure that imports differently formatted workbooks into a database table, does work on them then drops the table. Here is the populating query. SELECT IDENTITY(INT,1,1) AS ID ...
The following code works. the connection opens fine but recordset.recordCount always returns -1 when there is data in the table. ANd If I try to call any methods/properties on recordset it crashes ...
I m using Application run to call several macros in order like this. Sub Run_All_Macros() Application.Run ("Macro_1") Application.Run ("Macro_1") End Sub When I start Run_All_Macros, all the ...
Does anyone know how to convert an Excel date to a correct Unix timestamp?
I am trying to import an excel file into a data table using GemBox and I keep getting this error: Invalid data value when extracting to DataTable at SourceRowIndex: 1, and SourceColumnIndex: 1. As ...
I am looking for any tips or resources on importing from excel into a SQL database, but specifically when the information is NOT in column and row format. I am currently doing some pre-development ...
I have created an Add-In in C# that implements user defined functions for Excel. These UDF s return immediately, but they control background asynchronous procedures. These procedures have status ...