拿Excel里的资料作比较

johe07

我不知道该放在哪里。。。就选择这里了。。。

用户给我一个excel file,里面有2个sheet,他要我在这两个sheet里面做比较。

在excel里我是不会。。。
我就尝试放进SQL里，用script来跑。。。
但还是不成功。。。

他要2个worksheet里，同样material不同qty的要显示出来。。。
A sheet 没有,B sheet有的要显示出来。。。
B sheet 没有,B sheet有的也要显示出来。。。


请问有什么更好的方法？
不一定要在SQL里，如果可以在excel更好。。。
总之就是要拿到资料出来。。。

（不写program可以吗？因为只是一时要用到而已。。。）

[ 本帖最后由 johe07 于 2-3-2006 06:19 PM 编辑 ]

goatstudio

据我所知... Excel 的 pivot table 功能可以做到... 但我不会...
也许你把资料放进资料库后, 再放上你的 table 让大家看看.

johe07

嗯。。。我查过资料。。。pivot table好像只是拿来做worksheet中的grouping和Total up的功能。。。可能我不会用吧。。。


* * * * * * * * * * * * * * * *

我刚刚找到一个script file.
请问我在MS SQL server里，要怎样跑script??

[ 本帖最后由 johe07 于 1-3-2006 03:50 PM 编辑 ]

johe07

以下是我找到的SCRIPT:

create procedure sp_Compare (
                                @TableA varchar(50),
                                @TableB varchar(50),
                                @ExcludeFields varchar(255),
                                @JoinFields varchar(255),
                                @CompType int = 0,
                                @debug bit = 0
                                )
as
begin
        --Author:         Subhan Munshi
        --Date:                02/04/2003
        --Comments:        This sp can compare two tables with the same schema
        --                and determine which records are different
        --Usage:        The following example assumes you have created this sp in Pubs and
have created a Titles2 table similar to Titles and changed some data
        --                exec sp_Compare 'Titles','Titles2','Notes,PubDate','Title_Id'

        --CompType        1 = Records that exist in A but not in B
        --                2 = Records that exist in B but not in A
        --                3 = Records that exist in both A and B but one or more columns compared
have different value
        --                4 = Records that exist in both A and B but all columns compared have
same value
        --                0 = Do all 4 comparisons

        set nocount on
        declare @SQL         varchar(8000),
                @select1 varchar(4000),
                @from1        varchar(4000),
                @where1        varchar(4000),
                @select2 varchar(4000),
                @from2        varchar(4000),
                @where2        varchar(4000),
                @select3 varchar(4000),
                @from3        varchar(4000),
                @where3        varchar(4000),
                @select4 varchar(4000),
                @from4        varchar(4000),
                @where4        varchar(4000),
                @ColName varchar(50),
                @field        varchar(50),
                @len        int,
                @start        int


        --Parse Exclude Fields and populate a temp table
        create        table #ExcludeFields(field varchar(50))
        select         @start = 1
        select         @len = Charindex(',',@ExcludeFields,@start)-1
        while        @len<>        -1
                begin
                select @field = substring(@ExcludeFields,@start,@len)
                insert into #ExcludeFields(field) values ('['+@field+']')
                select @ExcludeFields = substring(@ExcludeFields,@len +
2,len(@ExcludeFields))
                select         @len = Charindex(',',@ExcludeFields,@start)-1
                end
        insert         into #ExcludeFields(field) values ('['+@Excludefields+']')

        --Parse join fields and populate a temp table
        create        table #JoinFields(field varchar(50))
        select         @start = 1
        select         @len = Charindex(',',@JoinFields,@start)-1
        while        @len<>        -1
                begin
                select @field = substring(@JoinFields,@start,@len)
                insert into #JoinFields(field) values ('['+@field+']')
                select @JoinFields = substring(@JoinFields,@len + 2,len(@JoinFields))
                select         @len = Charindex(',',@JoinFields,@start)-1
                end
        insert         into #JoinFields(field) values ('['+@JoinFields+']')

        --Get a list of columns name in a temp table
        select        ColName = '[' + rtrim(sc.Name) + ']'
        into        #Columns
        from        syscolumns sc inner join sysobjects so on sc.id = so.id
        where        so.Name = @TableA
        order by colid

        --Initialize select from and where clause
        set        @select1 = 'select '
        set        @select2 = 'select '
        set        @select3 = 'select '
        set        @select4 = 'select '

        set        @from1 = 'from ' + @TableA + ' A left outer join ' + @TableB + ' B on '
        set        @from2 = 'from ' + @TableA + ' A right outer join ' + @TableB + ' B on
'
        set        @from3 = 'from ' + @TableA + ' A inner join ' + @TableB + ' B on '
        set        @from4 = 'from ' + @TableA + ' A inner join ' + @TableB + ' B on '

        set        @where1 = 'where '
        set        @where2 = 'where '
        set        @where3 = 'where '
        set        @where4 = 'where '

        --Build select from and where clauses
        declare cBuildSQL cursor for
                select        ColName
                from        #Columns

        open        cBuildSQL
        fetch         next from cBuildSQL into @ColName
        while         @@fetch_status = 0
                begin
                --select clauses
                set         @ColName = left(@ColName,len(@ColName)-1)
                --select 1
                select        @select1 = @select1 + @ColName + '_A] = A.' + @ColName + '], '

                --select 2
                select        @select2 = @select2 + @ColName + '_B] = B.' + @ColName + '], '

                --select 3
                select        @select3 = @select3 + @ColName + '_A] = A.' + @ColName + '], '
                select        @select3 = @select3 + @ColName + '_B] = B.' + @ColName + '], '

                --select 4
                select        @select4 = @select4 + @ColName + '_A] = A.' + @ColName + '], '
                select        @select4 = @select4 + @ColName + '_B] = B.' + @ColName + '], '

                set        @ColName = @ColName + ']'
                --from clause - Only include the fields in the Join list
                if         @ColName in(select field from #JoinFields)
                        begin
                        select        @from1 = @from1 + 'A.' + @ColName + ' = B.' + @ColName + ' and '
                        select        @from2 = @from2 + 'A.' + @ColName + ' = B.' + @ColName + ' and '
                        select        @from3 = @from3 + 'A.' + @ColName + ' = B.' + @ColName + ' and '
                        select        @from4 = @from4 + 'A.' + @ColName + ' = B.' + @ColName + ' and '
                        end

                --where clause - Do not include the fields in the exclusion list
                if         @ColName not in(select field from #ExcludeFields)
                        begin
                        if         @ColName in(select field from #JoinFields)
                                begin
                                select        @where1 = @where1 + 'B.' + @ColName + ' is null and '
                                select        @where2 = @where2 + 'A.' + @ColName + ' is null and '
                                end
                        else
                                begin
                                select        @where3        = @where3 + 'A.' + @ColName + ' <> ' + 'B.' + @ColName +
' or '
                                select        @where4        = @where4 + 'A.' + @ColName + ' = ' + 'B.' + @ColName + '
and '
                                end
                        end

                fetch         next from cBuildSQL into @ColName

                end
        deallocate cBuildSQL

        --get rid of trailing ',' from select clause, 'and' from from clause and
'or' from where clause
        set        @select1 = left(@select1,len(@select1)-1)
        set        @select2 = left(@select2,len(@select2)-1)
        set        @select3 = left(@select3,len(@select3)-1)
        set        @select4 = left(@select4,len(@select4)-1)

        set        @from1 = left(@from1,len(@from1)-4)
        set        @from2 = left(@from2,len(@from2)-4)
        set        @from3 = left(@from3,len(@from3)-4)
        set        @from4 = left(@from4,len(@from4)-4)

        set        @where1 = left(@where1,len(@where1) - 4)
        set        @where2 = left(@where2,len(@where2) - 4)
        set        @where3 = left(@where3,len(@where3) - 3)
        set        @where4 = left(@where4,len(@where4) - 4)

        if         @debug = 1
                begin
                select         field from #ExcludeFields
                select  field from #JoinFields

                select        select1 = @select1
                select        select2 = @select2
                select        select3 = @select3
                select        select4 = @select4

                select         from1 = @from1
                select        from2 = @from2
                select        from3 = @from3
                select        from4 = @from4

                select         where1 = @where1
                select         where2 = @where2
                select         where3 = @where3
                select         where4 = @where4
                end

        if         @CompType = 0
                begin
                select        Message = 'The following are the records that exist in table A but
not in table B'
                exec        (@select1 + ' ' + @from1 + ' ' + @where1)

                select        Message = 'The following are the records that exist in table B but
not in table A'
                exec        (@select2 + ' ' + @from2 + ' ' + @where2)

                select        Message = 'The following are the records that exist in both table A
and table B but one or more columns compared are different'
                exec        (@select3 + ' ' + @from3 + ' ' + @where3)

                select        Message = 'The following are the records that exist in both table A
and table B and all the columns compared are same'
                exec        (@select4 + ' ' + @from4 + ' ' + @where4)
                end

        if         @CompType = 1
                begin
                select        Message = 'The following are the records that exist in table A but
not in table B'
                exec        (@select1 + ' ' + @from1 + ' ' + @where1)
                end

        if         @CompType = 2
                begin
                select        Message = 'The following are the records that exist in table B but
not in table A'
                exec        (@select2 + ' ' + @from2 + ' ' + @where2)
                end


        if         @CompType = 3
                begin
                select        Message = 'The following are the records that exist in both table A
and table B but one or more columns compared are different'
                exec        (@select3 + ' ' + @from3 + ' ' + @where3)
                end


        if         @CompType = 4
                begin
                select        Message = 'The following are the records that exist in both table A
and table B and all the columns compared are same'
                exec        (@select4 + ' ' + @from4 + ' ' + @where4)
                end

        drop table #columns,#ExcludeFields,#JoinFields
end

执行它的指令是

exec sp_Compare "Titles","Titles2","Notes,PubDate","Title_Id", 0

它是在pubs database下执行。。。

我在它的执行指令修改我要比较的table,却有错误讯息

erver: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.

请问有谁明白吗？

flashang

原帖由 johe07 于 1-3-2006 11:13 AM 发表
我不知道该放在哪里。。。就选择这里了。。。

用户给我一个excel file,里面有2个sheet,他要我在这两个sheet里面做比较。

在excel里我是不会。。。
我就尝试放进SQL里，用script来跑。。。
但还是不成功。 ...

用 excel 內建的 function 就可以了, 只是要比較多步驟...

shinelynn

不好意思，其实不是很明白你要做什么，我姑且猜猜，可是我也不懂得怎么解释，所以print screen给你看。希望可以帮到你。

meemee

他要2个worksheet里，同样material不同qty的要显示出来。。。
A sheet 没有,B sheet有的要显示出来。。。
B sheet 没有,B sheet有的也要显示出来。。。

是A吧？

原帖由 flashang 于 1-3-2006 11:36 PM 发表


用 excel 內建的 function 就可以了, 只是要比較多步驟...

我同意。

我的概念是用match来做比较，找出没有的资料，然后放在新的sheet。

johe07

我对excel不熟，最不会就是你们所说的什么Hlookup, Vlookup....
我没有办法之下才想到用SQL...

我是要直接显示成绩出来，不是Y/N
如果照shinelynn 的方法，就必须用多点的时间了

我会再看看。。。现在在赶报告呢。。。

flashang

原帖由 meemee 于 2-3-2006 09:20 AM 发表


是A吧？


我同意。

我的概念是用match来做比较，找出没有的资料，然后放在新的sheet。

hmm... 我會考慮把 sheet a + sheet b 變成 sheet c, 然後

a + b = c
c - a = b
c - b = a

sheet c 減掉 sheet a 就是 b 有 a 沒有的
sheet c 減掉 sheet b 就是 a 有 b 沒有的

[ 本帖最后由 flashang 于 3-3-2006 12:32 AM 编辑 ]

johe07

哈哈。。。是啊meemee,我打错了。。。

flashang,谢谢你给的概念。。。。
如果A,B都有，其中一个qty不一样呢？

嗯。。。
昨天我用上面的script在SQL里面跑。。。
那个error我已经解决，造成错误的原因是----
       我table的名字
我不可以用数目字来做名字。。。后来我改了，就可以了。。。
（为什么呢？？？）

跑了出来，有了成绩，但是不知道对不对。。
现在还要一个个来比对。。。

flashang

原帖由 johe07 于 3-3-2006 08:43 AM 发表
哈哈。。。是啊meemee,我打错了。。。

flashang,谢谢你给的概念。。。。
如果A,B都有，其中一个qty不一样呢？

嗯。。。
昨天我用上面的script在SQL里面跑。。。
那个error我已经解决，造成错误的原因是- ...

Comparre string = material & qty

那麼同樣 material 不同 qty 就不同了.

johe07

刚才看了一看。。。
发现，我这样比对是不对的。。。

给你们一些例子

Sheet 1                         
Part#    MAterial    Qty
A 0001     M-1         2.000
A 0001     M-2         5.000
A 0001     M-3         1.345
B 0003     M-2         1.000
B 0003     M-8         2.550
B 0003     M-7         8.000
C 0007     M-10       15.000
C 0007     M-20        7.633

Sheet 2                         
Part#    MAterial    Qty
A 0002     M-1         2.000
A 0002     M-2         4.000
A 0002     M-3         1.345
A 0002     M-6         1.000
B 0004     M-2         2.000
B 0004     M-8         2.670
C 0008     M-10       15.000
C 0008     M-20        7.633

以上例子，以红色的资料都是我在做比较后要显示出来了。。。
Part#的资料名字有变化，但其实是同一样东西。。。
只是要检查他的用料不同之处

两个之中没有的都要显示出来
两个中都有的但数量不一样的也要显示出来
唯有原料一样，数量一样的不需要显示出来

meemee

发现，我这样比对是不对的。。。

呵呵，这句话的","应该放在哪里好呢？？

是这个：
发现，我这样比对，是不对的。。。

还是这个：
发现，我这样比，对是不对的。。。

我不会SQL的方法。。。

回家之后再帮你试试用excel的方法。

johe07

原帖由 meemee 于 3-3-2006 16:05 发表
发现，我这样比对是不对的。。。

呵呵，这句话的","应该放在哪里好呢？？

是这个：
发现，我这样比对，是不对的。。。

还 ...

看来我是做到疯了。。。。

我之前是拿material no 来比。。。
在我跑script时，他出来的资料是不对的。。
因为这样没有考虑到part#..

meemee

呵呵，现在我对你的问题越来越乱了。

Part#的资料名字有变化，但其实是同一样东西。。。只是要检查他的用料不同之处

他出来的资料是不对的。。因为这样没有考虑到part#..
这两句话很矛盾，你要考虑part#，但是同样东西的part#也变化，那么？？？

不过，我想你的意思应该是，同样东西的part#会变，但是它的第一个字符(character)是不会变。
这样的话，你需要考虑到part#的第一个字符。是不是这样？？？

我放上一个macro，你试试看。
红色那行是有考虑part#，绿色那行是没考虑part#。

Sub ShowDiff()

Dim Sheet1Data() As String
Dim Sheet2Data() As String

Dim PartChar As String * 1
Dim Material As String
Dim Qty As String
Dim i As Integer, j As Integer
Dim Data1Count As Integer, Data2Count As Integer
Dim CellString As String
Dim CountRow As Integer  'used to count row for new data
Dim FoundData As Boolean

'i = 0
'Combine the data for sheet1
Do
   PartChar = Trim$(Sheet1.Cells(i + 2, 1))
   Material = Trim$(Sheet1.Cells(i + 2, 2))
   Qty = Trim$(Sheet1.Cells(i + 2, 3))
   
   CellString = Material & " " & Qty
   CellString = PartChar & " " & Material & " " & Qty
   If Trim$(CellString) = "" Then
      Exit Do
   Else
      ReDim Preserve Sheet1Data(i)
      Sheet1Data(i) = CellString
   End If

   i = i + 1
Loop While True

i = 0
'Combine the data for sheet2
Do
   PartChar = Trim$(Sheet2.Cells(i + 2, 1))
   Material = Trim$(Sheet2.Cells(i + 2, 2))
   Qty = Trim$(Sheet2.Cells(i + 2, 3))
   
   CellString = Material & " " & Qty
   CellString = PartChar & " " & Material & " " & Qty
   If Trim$(CellString) = "" Then
      Exit Do
   Else
      ReDim Preserve Sheet2Data(i)
      Sheet2Data(i) = CellString
   End If

   i = i + 1
Loop While True

Data1Count = UBound(Sheet1Data)
Data2Count = UBound(Sheet2Data)

'Read from sheet2 and check in sheet1
'if not exist display it in sheet1
CountRow = Data1Count + 4
For i = 0 To Data2Count  'loop for sheet2
    For j = 0 To Data1Count  'loop for sheet1
        FoundData = Sheet1Data(j) = Sheet2Data(i)
        If FoundData Then Exit For
    Next
    If Not FoundData Then
       Sheet1.Cells(CountRow, 1) = Sheet2.Cells(i + 2, 1)
       Sheet1.Cells(CountRow, 2) = Sheet2.Cells(i + 2, 2)
       Sheet1.Cells(CountRow, 3) = Sheet2.Cells(i + 2, 3)
       CountRow = CountRow + 1
    End If
Next

'Read from sheet1 and check in sheet2
'if not exist display it in sheet2
CountRow = Data2Count + 4
For i = 0 To Data1Count  'loop for sheet1
    For j = 0 To Data2Count  'loop for sheet2
        FoundData = Sheet2Data(j) = Sheet1Data(i)
        If FoundData Then Exit For
    Next
    If Not FoundData Then
       Sheet2.Cells(CountRow, 1) = Sheet1.Cells(i + 2, 1)
       Sheet2.Cells(CountRow, 2) = Sheet1.Cells(i + 2, 2)
       Sheet2.Cells(CountRow, 3) = Sheet1.Cells(i + 2, 3)
       CountRow = CountRow + 1
    End If
Next

End Sub

johe07

meemee,
很感谢你的帮忙。。。花了你不少时间吧？
也很抱歉。。。弄得你头昏脑胀。。。

其实我也是快疯了。。。。

因为情况是这样的。。。
我们的product,只要customer要求变动原料。。
我们part no就会换一个level...
所以part#不一样，其实他们是同一个东西。。。

他们就是要找出。。。当这个level换取另一个level时，
有什么东西换了。。。

我还有一个疑问。。。。
那些code要放在哪里啊？
macro 要怎么玩得？

我没有玩过。。。

[ 本帖最后由 johe07 于 6-3-2006 02:50 PM 编辑 ]

meemee

是啊，不过比较长的时间是用来理解你的问题。

macro？不会太难。使用macro的坏处是会出现macro virus的警告，你用了就会知道。

按Alt+F11或者
Tools > Macro > Visual Basic Editor

进入Visual Basic Editor之后，再
insert > module然后就可以把我写的代码放进去。使用的方法跟VB6很像。

在run macro时遇到infinite loop，按ctrl+break就可以中断。

[ 本帖最后由 meemee 于 6-3-2006 07:30 PM 编辑 ]