方案一、在C列、D列与A列第1个数据行交汇的单元格分别输入下面公式：
=IF(COUNTIF($B$1:$B$9,A1)=0,A1,"")
用B列真正的数据区域替换公式中的$B$1:$B$9(必须带$符号!)；用A列第1个数据单元格替换公式中的A1。输入完成后回车结束。
=IF(C1="",1,0)
用C列第1个输入上面公式的单元格替换公式中的C1。输入完成后回车结束。
把刚输入的公式沿C列、D列向下复制到与A列最后1数据行同行止。
如果你不需要C列中间的空单元格，选中C列和D列，执行“数据”菜单下的“排序”——主要关键字“D列”、“升序”，然后“确定”。
最后删除辅助列D列即可。。
方案二、在C列与A列第1个数据行交汇的单元格输入下面公式：
=IF(SMALL(IF(COUNTIF($B$1:$B$9,$A$1:$A$9)=0,ROW($A$1:$A$9),65535),ROW())=65535,"",SMALL(IF(COUNTIF($B$1:$B$9,$A$1:$A$9)=0,ROW($A$1:$A$9),65535),ROW()))
用B列真正的数据区域替换公式中的$B$1:$B$9(必须带$符号!)；
用A列真正的数据区域替换公式中的$A$1:$A$9(必须带$符号!)。
输入完成后同时按下“Shift + Ctrl + 回车”组合键结束。
把刚输入的公式沿C列向下复制到出现空单元格时停止。
require 'win32ole'module Excel
class WorkBook
#xlEdge
#xlEdgeBottom =9
#xlEdgeLeft = 7
#xlEdgeRight = 10
#xlEdgeTop = 8
#xlColor
#xlColorBlank = 1
#xlColorWhite =2
#xlColorRed = 3
#xlColorGreen =10
#xlColorBlue =5
#xlColorYellow =6
#xlColorPurple = 7 # zi se
#xlColorCyan =8 #qing se
#xlBgColorYellow =19
#xlBgColorCyan =20
#xlBgColorPurple =24
#xlDefaultLineStyle = 1
@@worksheets_name =[]
def initialize(encoding="utf-8")
@excel = WIN32OLE.new("excel.application")
@excel.visible = FALSE
@workbook = @excel.Workbooks.Add()
#@style_id = 0
@encoding = encoding
create_style
end
def add_worksheet(name)
while @@worksheets_name.include?(name)
name +="1"
end
@@worksheets_name << name
worksheet = @workbook.Worksheets.Add()
worksheet.Activate
worksheet.name = name
return WorkSheet.new(worksheet)
end
def show
@excel.visible = TRUE
end
def close
@workbook.Close(0)
@excel.Quit()
end
def create_style
[email protected]('NormalStyle')
sty.Font.Size = 12
sty.Borders(7).LineStyle=1
sty.Borders(8).LineStyle=1
sty.Borders(9).LineStyle=1
sty.Borders(10).LineStyle=1
[email protected]('TitleStyle')
sty.Font.Size = 16
sty.Font.Bold =true
sty.Font.ColorIndex =3
#sty.Interior.ColorIndex = 20
end
end
#worksheet
class WorkSheet
IMAGE_ROW_NUM = 56
@@worksheets_name =[]
def initialize(worksheet)
@row_count = 1
@worksheet = worksheet
end
def add_space_line(n=1)
return if n<1
@row_count +=n
end
def add_title(name)
add_space_line
add_row.add_cell(name,false,"TitleStyle")
end
def add_row()
@current_row = Row.new(@worksheet,@row_count)
@row_count +=1
return @current_row
end
def current_row
return @current_row
end
def add_image(image_path)
if not File.exist?(image_path)
return
end
add_space_line 1
add_row
cell_name=current_row.first_cell
@worksheet.Range(cell_name).Select
@worksheet.Pictures.Insert(image_path)
add_space_line IMAGE_ROW_NUM
end
end
#row
class Row
FILL_TYPE = 4
@@cell_map =["A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"]
def initialize(worksheet,row_id)
@row_id =row_id
@cell_count=0
@worksheet = worksheet
end
def curent_cell
return cell_name(@cell_count)
end
def first_cell
return cell_name(0)
end
def add_cell(value,auto_fit = false,style = "NormalStyle")
range = @worksheet.Range(cell_name(@cell_count))
range['Value'] = value.to_s
range['Style']=style
range.Columns.AutoFit if auto_fit
@cell_count +=1
end
def cell_name(index)
second = index % 26
first = (index - second) / 26
if first == 0
return @@cell_map[second]+@row_id.to_s
end
first -=1
return @@cell_map[first]+@@cell_map[second]+@row_id.to_s
end
def set_cell(index,value,auto_fit = false,style = "NormalStyle")
[email protected](cell_name(index))
range['Value'] = value
range['Style']=style
range.Columns.AutoFit if auto_fit
end
end
end
require 'excel'
excel = Excel::WorkBook.new
worksheet = excel.add_worksheet("new sheet")
worksheet.add_title('title')
row = worksheet.add_row
row.add_cell("chenyan")
row.add_cell(0)
row.add_cell("2006-01-01 01:01:01")
worksheet.add_image("C://Sunset.jpg")
row = worksheet.add_row
row.add_cell("shenbin")
row.add_cell(0)
row.add_cell("2006-01-01 01:01:01")
worksheet.add_image("C://Sunset.jpg")
excel.show
用loveruby521 的公式，或者是：=LOOKUP(A1,{0,1,2,3,4,5,6,7,8,9},{3.60,3.70,4.00,5,4.30,6.10,5,4,4,3.50})
然后把这个公式所在单元格格式设置成百分比。
