Excel处理两列相同（重复）或不同的数据单元格问题(2)

  上一篇文章说的有点糊里糊涂，希望这篇您能看明白。
  有些朋友经常碰到两个问题：1）怎么找出两列单元格重复的数据？2）怎么找出两列不同的数据。
方法很简单，
  例如A,B两列既含有重复的数据有含有不同的数据，
  我们首先解决比较简单的问题，即去除单列内的重复项，这个在excel数据的高级筛选可以做到，首先选中单元格——高级筛选——将单元格复制到——选中相邻的单元格作为复制的地址——选中下拉菜单底部的去除重复--确定就完成了。
  经过上一步骤我们得到了两列单列内没有重复项的单元格，如图

  














  我简单介绍一下原理，其实这就是两个集合的交集和补集的问题。重复的部分就是它们的交集，不同的部分就是它们的剔除交集后的并集。
  我们来介绍方法：第一步我们先在A列中查找B列然后将查找结果放在C列，我们在C1单元格中输入，然后下拉填充到B列结束，就行了
  =VLOOKUP(B1,$A$1:$A$16,1,FALSE)
  函数中B1为要查找的对象，$A$1:$A$16查找的范围，表示从A1遍历到A16,$表示绝对地址，避免填充时单元格序号会随填充而变化，1表示返回超找对象列，False表示精确匹配，如果需要最大近似值可以不设置，查找结果如下图

  其中#N/A表示A列中没有和B列相同的元素，B列所特有的部分，不重复项的一部分.如果需要相同的我们把#N/A删除即得重复数据，如果需要不重复的我们把原列中的相同数据删除就的到了不相同的数据。如果数据比较大的话，手动删除好像仍然比较麻烦。我们对结果进行整理
  在D1输入并向下填充以下公式：
  =COUNTIF(B1:C1,B1)
  函数中(B1:C1表示B1和C1匹配，如果相同则返回逻辑值2，如果不同则返回逻辑值1，结果如图

  

















  然后复制B和D列，选择性粘贴（一定要选择性粘贴数值）到sheet2，再对sheet2中的B列排序，选择扩展选定区域，得到结果如下图

  其中2所对应的A列单元格都是原来数据sheet1中A,B列相同的（重复的）数据，1所对应的就是原来的B列所独有的数据。把他们分别复制到新建的excel表格里面（定名为相同列和不同列）。然后删除原列表所有的东西，仅保留数据sheet1中的原始数据A和B列，依照上述方法再对A列进行相同的操作，找出A列中两列共有的数据和A列特有的数据，再把结果整理到刚刚新建的excel表格中相应的部分，我们就分拣出了A列和B列重复的部分和不重复的部分。
不过如果你按步骤操作保证能分拣成功。
上一篇文章说的有点糊里糊涂，希望这篇您能看明白。

Excel处理两列相同（重复）或不同的数据单元格问题(1)

  有些朋友经常碰到两个问题：1）怎么找出两列单元格重复的数据？2）怎么找出两列不同的数据。
方法很简单，
  例如A,B两列既含有重复的数据有含有不同的数据，
  我们首先解决比较简单的问题，即去除单列内的重复项，这个在excel数据的高级筛选可以做到，首先选中单元格——高级筛选——将单元格复制到——选中相邻的单元格作为复制的地址——选中下拉菜单底部的去除重复--确定就完成了。
  经过上一步骤我们得到了两列单列内没有重复项的单元格，如图

  














  我简单介绍一下原理，其实这就是两个集合的交集和补集的问题。重复的部分就是它们的交集，不同的部分就是它们的剔除交集后的并集。
  我们来介绍方法：第一步我们先在A列中查找B列然后将查找结果放在C列，我们在C1单元格中输入，然后下拉填充到B列结束，就行了
  =VLOOKUP(B1,$A$1:$A$16,1,FALSE)
  函数中B1为要查找的对象，$A$1:$A$16查找的范围，表示从A1遍历到A16,1表示返回超找对象列，False表示精确匹配，如果需要最大近似值可以不设置，查找结果如下图

  其中#N/A表示A列中没有和B列相同的元素，B列所特有的部分，不重复项的一部分，下面我们再以相同的方法在B列中查找A列的元素，然后放在D列。函数如下
  =VLOOKUP(A1,$B$1:$B$16,1,FALSE)
  然后下拉填充得到结果如下图

  其中正常显示的就是两列数据重复的单元格，#N/A表示为两列不重复的单元格，如果需要相同的我们把#N/A删除即得重复数据，如果需要不重复的我们把原列中的相同数据删除就的到了不相同的数据。
但手动删除好像仍然比较麻烦。我们对结果进行整理  我们先把B列和C列复制到其他区域如I和J区，记得选选择性粘贴。在L1输入并向下填充以下公式：
  =COUNTIF(I1:J1,J1)
  函数中I1:J1表示I1和J1匹配，如果相同则返回逻辑值2，如果不同则返回逻辑值1，结果如图

  然后复制J和K列，选择性粘贴到其他区域如M和N，在对K列排序，选择扩展到其他区域，得到结果如下图

  其中2所对应的M列单元格都是原来数据A,B列中相同的（重复的）数据，1所对应的就是原来的B列所独有的数据。把他们复制到新建的excel表格里面。然后撤销原列表中的排序步骤，因为上一步的扩展导致整个表格顺序都变了，所以复制之后要撤销排序，依照该方法再对A列和它所查询的结果D列进行相同的操作，再把结果整理到刚刚新建的excel表格中相应的部分，我们就分拣出了A列和B列重复的部分和不重复的部分。
可能表述起来比较麻烦，不过如果你按步骤操作保证能分拣成功。

Ubuntu桌面指南

Ubuntu桌面指南是官方发布的中文文档，主旨是为家庭和办公用户提供全面的帮助和培训，是系统附带的官方文档，现提供简体中文版《ubuntu桌面指南》，想了解的朋友可以关注。

下载链接：https://docs.google.com/open?id=0B1tCPnFlWsLsSDllUDBfZXVXY2s

用R读取上市公司的财务报表

  进行投资分析时难免要用到上市公司的财务报表，但实时获得上市公司的财务报表给分析师带来很多麻烦，又是即使能够得到也是过时的或者是很难用与数据分析的格式或脏数据。

  R 包"quantmod"中的函数getFin函数可以帮助我我们方便的从谷歌财经上读取上市公司的财务报表，而且存储方便，易于后续分析。需要说明的是读取的仅包括谷歌财经上刊登的资产负债表、利润表和现金流量表。下面我们接触一下getFin函数。

1.安装并载入"quantmod"包

> install.packages("quantmod")

> library(quantmod)

2.了解一下getFinancials函数

getFinancials(Symbol, env = .GlobalEnv, src = "google", 
              auto.assign = TRUE,
              ...)
其中Symbol为上市公司代码，env读取后存放路径，src来源等
3.我们来获取中国石化的财务报表，其黄金股票代码为SHA:600028
> getFin("SHA:600028")
4.我们来看查询函数
viewFinancials(x, type=c('BS','IS','CF'), period=c('A','Q'),subset = NULL)

其中x为存储的文件比如上例的文件为SHA.600028.f,type参数包含三个选项，分别对应谷歌财经能够里的BS(Balance Sheets),IS（Income Statement)，CF(Cash Flow Statements)。period参数有两个选项，分别表示年报和季报
> viewFin(SHA.600028.f,"BS","A")
Annual Balance Sheet for SHA:600028
                                                      2011-12-31
Monetary Capital                                           25197
Other Monetary Capital                                        NA
Short-term Investment (Original Value)                        NA
Devaluation Provision for Short-term Investment               NA
Net Short-term Investment                                     NA
Notes Receivable                                           27961
Dividend Receivable                                           NA
Interest Receivable                                           NA
Accounts Receivable (Original Value)                          NA
Original Amount of Other Receivables                          NA
Provision for Bad Debt                                        NA
Net Accounts Receivable                                    58721
Net Other Receivables                                       7360
Prepayment                                                  4096
Allowance Receivable                                          NA
Prepaid Tax                                                   NA
Internal Receivables                                          NA
Internal Organization Receivables                             NA
Affiliated Entity Receivables                                 NA
Margin for Futures                                            NA
Trading Position Fee Receivable                               NA
Inventories                                                   NA
Provision of Inventories Impairment                           NA
Net Inventories                                           203417
Deferred Costs                                                NA
Transferrable Other Operating Costs                           NA
Project Construction                                          NA
Net Loss on Disposable Current Assets                         NA
Long-term Bond Invetment Mature within 1 Year                 NA
Other Current Assets                                         836
Total Current Assets                                      327588
Original Cost of Long-term Equity Investment                  NA
Original Cost of Long-term Bond Investment                    NA
Original Cost of Other Long-term Investment                   NA
Total Original Cost of Long-term Investment                   NA
Devaluation Provision of Long-term Investment                 NA
Net Long-term Equity Investment                            47458
Net Long-term Bond Investment                                 NA
Net Other Long-term Investment                                NA
Total Net Long-term Investment                                NA
Cost-Book Value Differential                                  NA
Equity Investment Differential                                NA
Original Cost of Fixed Assets                                 NA
Accumulated Depreciation                                      NA
Net Fixed Assets                                              NA
Provision for Fixed Assets Impairment                         NA
Net Fixed Assets                                          565936
Project Materials                                             NA
Construction in Process                                   111311
Disposal of Fixed Assets                                      NA
Net Loss on Disposable Fixed Assets                           NA
Total Fixed Assets                                            NA
Intangible Assets                                          34842
Organization Cost                                             NA
Deferred Assets                                               NA
Long-term Deferred Cost                                     9076
Other Long-term Assets                                        NA
Total Intangible and Other Assets                             NA
Deferred Income Tax Asset                                  13398
Total Assets                                             1130053
Short-term Borrowing                                       36985
Notes Payable                                               5933
Accounts Payable                                          177002
Advance Collections                                        66686
Commissioned Sales                                            NA
Salaries Payable                                            1795
Welfare Benefits Payable                                      NA
Dividend Payable                                              NA
Tax Payable                                                39622
To Withold Taxes                                              NA
Accrued Other Payments                                        NA
Internal Payables                                             NA
Internal Organization Payables                                NA
Affiliated Entity Payables                                    NA
Other Payables                                             57662
Commissioned Sales                                            NA
Accrued Cost                                                  NA
Engineering Settlement                                        NA
Domestic Notes Settlement                                     NA
International Notes Settlement                                NA
Estimated Liabilities                                         NA
Long-term Liabilities Mature within 1 Year                    NA
Other Current Liabilities                                     NA
Bonus and Welfare Funds                                       NA
Total Current Liabilities                                 429073
Long-term Borrowing                                        54320
Bond Payable                                              100137
Long-term Payables                                            NA
Specific Item Payable                                         NA
Revolving Housing Fund                                        NA
Other Long-term Liabilities                                   NA
Total Long-term Liabilities                                   NA
Deferred Interest Income                                      NA
Increase of Deferred Income Tax Liabilities                15181
Total Liabilities                                         620528
Minority Interest in Equity                                35126
Paid-up Share Capital                                      86702
Capital Reserve                                            29583
Earned Surplus Reserve                                    178263
Public Welfare Fund                                           NA
Group Welfare Fund                                            NA
Unidentified Investment Loss                                  NA
Undistributed Profit                                      178336
Converted Difference on Foreign Currency Statements        -1600
Total Shareholders' Equity                            509525
Liabilities & Shareholders' Equity                   1130053
Non-current Assets within 1 Year                              NA
Other Items Affecting Current Assets                           0
Held-to-maturity Investment                                   NA
Held-to-maturity Investment                                   NA
Real Estate Investment                                        NA
Long-term Receivables                                         NA
Regenerating Biological Assets                                NA
Oil & Gas Reserves                                            NA
Development Cost                                              NA
Goodwill                                                    8212
Other Non-current Assets                                   12232
Other Items Affecting Non-current Assets                       0
Total Non-current Assets                                  802465
Trading Financial Liabilities                                 NA
Interest Payable                                              NA
Non-current Liabilities Mature within 1 Year               43388
Other Items Affecting Current Liabilities                     NA
Other Non-current Liabilities                               3436
Other Items Affecting Non-current Liabilities                  0
Total Non-current Liabilities                             191455
Less: Treasury Stock                                          NA
Other Items Affecting Equity                                  NA
Trading Financial Assets                                      NA
Inventories - Consumable Biological Assets                    NA
Parent Company Equity                                     474399
Floating Rights of Stock with Different Voting Rights         NA
Deferred Income                                               NA
Less: Investment Returned                                     NA
Special Reserve                                             3115
                                                      2010-12-31
Monetary Capital                                           18140
Other Monetary Capital                                        NA
Short-term Investment (Original Value)                        NA
Devaluation Provision for Short-term Investment               NA
Net Short-term Investment                                     NA
Notes Receivable                                           15950
Dividend Receivable                                           NA
Interest Receivable                                           NA
Accounts Receivable (Original Value)                          NA
Original Amount of Other Receivables                          NA
Provision for Bad Debt                                        NA
Net Accounts Receivable                                    43093
Net Other Receivables                                       9880
Prepayment                                                  5247
Allowance Receivable                                          NA
Prepaid Tax                                                   NA
Internal Receivables                                          NA
Internal Organization Receivables                             NA
Affiliated Entity Receivables                                 NA
Margin for Futures                                            NA
Trading Position Fee Receivable                               NA
Inventories                                                   NA
Provision of Inventories Impairment                           NA
Net Inventories                                           156546
Deferred Costs                                                NA
Transferrable Other Operating Costs                           NA
Project Construction                                          NA
Net Loss on Disposable Current Assets                         NA
Long-term Bond Invetment Mature within 1 Year                 NA
Other Current Assets                                         594
Total Current Assets                                      249450
Original Cost of Long-term Equity Investment                  NA
Original Cost of Long-term Bond Investment                    NA
Original Cost of Other Long-term Investment                   NA
Total Original Cost of Long-term Investment                   NA
Devaluation Provision of Long-term Investment                 NA
Net Long-term Equity Investment                            45037
Net Long-term Bond Investment                                 NA
Net Other Long-term Investment                                NA
Total Net Long-term Investment                                NA
Cost-Book Value Differential                                  NA
Equity Investment Differential                                NA
Original Cost of Fixed Assets                                 NA
Accumulated Depreciation                                      NA
Net Fixed Assets                                              NA
Provision for Fixed Assets Impairment                         NA
Net Fixed Assets                                          540700
Project Materials                                             NA
Construction in Process                                    89599
Disposal of Fixed Assets                                      NA
Net Loss on Disposable Fixed Assets                           NA
Total Fixed Assets                                            NA
Intangible Assets                                          27440
Organization Cost                                             NA
Deferred Assets                                               NA
Long-term Deferred Cost                                     7560
Other Long-term Assets                                        NA
Total Intangible and Other Assets                             NA
Deferred Income Tax Asset                                  15578
Total Assets                                              985389
Short-term Borrowing                                       29298
Notes Payable                                               3818
Accounts Payable                                          132528
Advance Collections                                        57324
Commissioned Sales                                            NA
Salaries Payable                                            7444
Welfare Benefits Payable                                      NA
Dividend Payable                                              NA
Tax Payable                                                33814
To Withold Taxes                                              NA
Accrued Other Payments                                        NA
Internal Payables                                             NA
Internal Organization Payables                                NA
Affiliated Entity Payables                                    NA
Other Payables                                             54871
Commissioned Sales                                          1000
Accrued Cost                                                  NA
Engineering Settlement                                        NA
Domestic Notes Settlement                                     NA
International Notes Settlement                                NA
Estimated Liabilities                                         NA
Long-term Liabilities Mature within 1 Year                    NA
Other Current Liabilities                                     NA
Bonus and Welfare Funds                                       NA
Total Current Liabilities                                 325627
......
5.我们来查看一下黄金企业的利润表，仍然使用viewFin函数
> viewFin(SHA.600028.f,"IS","A")
Annual Income Statement for SHA:600028
                                                             2011-12-31
Sales Revenue                                                2505683.00
Discount and Allowance                                               NA
Net Revenue from Main Operation                                      NA
Less: Cost of Goods Sold                                     2093199.00
Sales Tax & Other Charges                                     189949.00
Profit (Loss) from Principal Operations                              NA
Income from Other Operation                                          NA
Inventory Falling Price Loss                                         NA
Business Expenses                                                    NA
Administrative Expenses                                        63083.00
Financial Expenses                                              6544.00
Period Expense                                                       NA
Purchase Expenses                                                    NA
Exchange Gains or Losses                                             NA
Operating Profit                                              100966.00
Investment Income                                               4186.00
Gains and Loss of Futures                                            NA
Subsidy Income                                                       NA
Non-Operating Revenue                                           3411.00
Income Adjustment for Previous Years                                 NA
Non-operating Expenses                                          1739.00
Net Non-operating Income                                             NA
Profits Distribute to Other Firms                                    NA
Preceding Year Income Tax Adjustment                                 NA
Total Profit                                                  102638.00
Income Tax                                                     25774.00
Minority Interest                                               5167.00
Subsidiary Losses                                                    NA
Special Funds Payable                                                NA
Bonus and Welfare Funds                                              NA
Merger Profit                                                        NA
Unidentified Investment Loss                                         NA
Reimbursement of Income Tax                                          NA
Losses of Subsidiaries shouldn't take by Parent Company          NA
The Revenue to Subsidiaries Offset Excess Deficit                    NA
Net Profit                                                     76864.00
Beginning Undistributed Profit                                       NA
Other Transferred in                                               0.00
Transferred from Surplus Reserve                                     NA
Beginning Undistributed Profit Adjustment                            NA
Reduction of Registered Capital Reduced Undistributed Profit         NA
Translation Reserve                                                  NA
Adjustments on Extraordinary Items                                   NA
Profit Distribution before Established Corporation                   NA
Revolving Housing Fund                                               NA
Profit Available for Dividend                                        NA
Appropriation of Statutory Surplus Reserves                          NA
Appropriation of Statutory Welfare Fund                              NA
Withdrawal of Employee Award and Benefits Fund                       NA
Appropriation of Reserve Fund                                        NA
Appropriation of Enterprise Expansion Fund                           NA
Distribution of Profits Available for Shareholders                   NA
Preferred Stock Dividends Payable                                    NA
Appropriation of Other Surplus Reserve                               NA
Appropriation of Ordinary Shares Dividend                            NA
Conversion of Common Stock Dividend into Stock                       NA
Undistributed Profit                                                 NA
Disposal of Departments or Invested Firms                            NA
Loss Due to Natural Disaster                                         NA
Accounting Policy Change Increase (Decrease) Total Profit            NA
Accounting Estimates Change Increase (Decrease) Total Profit         NA
Loss on Arrangement                                                  NA
.....
6.查看现金流
> viewFin(SHA.600028.f,"CF","A")
Annual Cash Flow Statement for SHA:600028
                                                                            2011-12-31
Cash Received for Goods Sold and Services Rendered                             2889482
Rental Charge                                                                      437
Received and Returned Value Added Tax                                               NA
Tax Refund Received excluding Value Added Tax                                       NA
Tax Refund Received                                                                 NA
Receipts from Other Business Activities                                          12316
Cash Inflows from Operating Activities Subtotal                                2902235
Cash Outflow for Goods & Services Purchased                                    2398623
Cash Payment under Operating Lease                                               12611
Cash Salaries                                                                    41718
Value Added Tax Paid                                                             71311
Income Tax Paid                                                                  29798
Tax Paid excluding Value Added & Income Tax                                         NA
Various Taxes Paid                                                              176474
Other Operating Cash Outflow                                                     20519
Cash Outflow for Operating Activities Subtotal                                 2751054
Net Operating Cash Flow                                                         151181
Divestment Cash Inflow                                                            3039
Cash Received for Dividend or Profit Distribution                                 2961
Cash Received for Bond Interest Income                                              NA
Cash Received for Investment Income                                                 NA
Intangible and Other Long-term Assets                                             1216
Other Investing Cash Inflow                                                      11646
Cash Outflow from Investment Activities Subtotal                                 18862
Intangible and Other Long-term Assets                                           142813
Cash Payment for Equity Investment                                                  NA
Cash Payment for Bond Investment                                                    NA
Investing Cash Outflow                                                            7488
Other Investing Cash Outflow                                                      9569
Cash Outflow from Investment activities Subtotal                                159870
Net Cash from Investment Activities                                            -141008
Cash Raised from Attracting Equity Investment                                       NA
Subsidiaries Financing from Minority Shareholders 
....
有了数据就可以进行下一步的工作了，看到这样现实的数据首先要把它们规范化，那就要根据个人分析的目的操作了

用R做热点图heatmaps

首先我们选择mtcars 作为数据，他主要是70年代的汽车性能表现和机械设计的数据。我们来用这个数据做一个等级h热点图heatmap。
1.首先将数据标准华为均值=1，标准差=0，使用scale()函数，并将结果转化为矩阵。

> mtscaled <- as.matrix(scale(mtcars))

2.创建热点图，去除对列重新排序的默认项。

> heatmap(mtscaled, Colv=F, scale='none')

需要说明是，默认况下，R会同时根据列和行做聚类热点图，而且会依据均值对系统树进行重新排序，Colv=F表示拒绝重新排序。如果我们的数据是时间序列等已经有序的数据，我们可能就不需要对列排序，仅需要对行聚类，这样我们可以把参数设为Colv=NA，从上图我们可以看到hp、cyl、disp的关系较近，而且马力越大，速度越快越耗油。

3.如果我们需要指定聚类数目，比如我要把上图以行聚类的第一个分支的两簇分别做图，那我们就需要hclust和cutree函数,而hclust函数必须根据距离矩阵执行

#首先以行聚类

> hc.rows <- hclust(dist(mtscaled))
#对矩阵转置，并重新以列聚类
> hc.cols <- hclust(dist(t(mtscaled)))
#对第一个分支聚类做热点图
> heatmap(mtscaled[cutree(hc.rows,k=2)==1,], Colv=as.dendrogram(hc.cols), scale='none')

对第二个分支聚类做热点图

> heatmap(mtscaled[cutree(hc.rows,k=2)==2,], Colv=as.dendrogram(hc.cols), scale='none')





















如果你不喜欢这套热点图的颜色，你可以利用一下函数首先修改默认颜色再做图
> palette <- colorRampPalette(c('#f0f3ff','#0033BB'))(256)
> heatmap(mtscaled, Colv=F, scale='none', col=palette)






















或者通过col参数直接设置
> heatmap(mtscaled[cutree(hc.rows,k=2)==2,], Colv=as.dendrogram(hc.cols), scale='none',col=topo.colors(100))
如果你要添加颜色图例的话你可以选择heatmap.2，它在gplot包里。