Archive for the ‘Excel’ Category

Merge adjacent rows of same data with VBA code

March 23, 2015

From: http://ift.tt/1LQ3FuB

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following macro in the Modulewindow.
————————————————————
Sub MergeSameCell()
'Updateby20131127
Dim Rng As Range, xCell As Range
Dim xRows As Integer
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
xRows = WorkRng.Rows.Count
For Each Rng In WorkRng.Columns
For i = 1 To xRows - 1
For j = i + 1 To xRows
If Rng.Cells(i, 1).Value <> Rng.Cells(j, 1).Value Then
Exit For
End If
Next
WorkRng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).Merge
i = j - 1
Next
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
————————————————————

  3. Then press the F5 key to run this code, a dialog is displayed on the screen for selecting a range to  work with.

http://ift.tt/1xXyiCN

excel countif函数中,条件设置为大于某个单元格数字怎么设置

February 19, 2015

转: http://ift.tt/1AVscrv

问:

如大于D4,">D4",但实际统计时是计算的D4,而不是D4单元格中的数值,怎么办?我要的是大于那个单元格中的数值,谢谢
我是有一列数,求这列数中大于本列数之和的0.9倍值和小于本列数值之和的1.1倍,怎么设置
 
答:
求数值用if函数

求个数用countif函数 =countif(A:A,">"&D4)

实在不行就用sum(if())镶嵌的数组函数,不过应该不会不行

countif函数的参数不能引用
 
尼玛这个折腾死了……  

http://ift.tt/1AVse2F

EXCEL 返回非空最后一行的行号

February 12, 2015

转:http://ift.tt/1B0gJYP

A1-A9:数字/字母/汉字
 A10-A12:空格
A13:汉字  
如何返回A13的行数13  
就是说一列数,中间不定单元格有空格 要返回最后个有数据的行数    
 =LOOKUP(1,0/(A1:A65535<>””),ROW(1:65535))  
或者数组公式:  =MAX((A1:A65535<>””)*ROW(A1:A65535))
输入后要按Ctrl+Shift+Enter 
 
 

LOOKUP()函数采用“二分法”进行查数,因此,要求源数据必须进行升序排序。但是,世事无绝对,总有一些牛人能想出各种办法,在不排序的情况下用LOOKUP()!!
公式:=LOOKUP(1,0/($A$2:A$696=F2),$C$2:C$696)
公式释义:$A$2:A$696=F2得到的是False、True的序列,在Excel中,涉及到数学运算时,False=0,True=1,因此用0除后,得到的是#DIV/0!、0序列。注意:在Excel中,所有错误值不参与排序,不参与字符串的比较,或者说:错误值比所有的数值、字符串都要“大”。而公式的作用是在生成的#DIV/0!、0序列中找到小于或等于1的最大值,因为错误值不参与排序,因此只能找到0(也就是$A$2:$A$696=F2成立的地方!!!!并且只有一个!!!),然后返回相对应的$C$2:$C$696中的数值。

另 外,因为“二分法”的缘故,如果源数据中有好几个符合条件的值(这些值肯定是相等的!!),那么LOOKUP()总会返回排在最后面的一个(而 VLOOKUP()总会返回第一次!!)。因此,假设A列有许多数据,并且里面还有空格,想找到最后一个单元格的位置,怎么办?如果没有空格,可以用公 式:
=COUNTA(A1:A65535)
现在有空单元格,可以用公式:
=LOOKUP(1,0/(A1:A65535<>””),ROW(A1:A65535))
与上面的公式原理相同。

http://ift.tt/16VwWAZ

Excel 引用单元格的数值 作为行号

February 12, 2015

转:http://ift.tt/1CXnb3g

比如这个公式=COUNTIFS(E$2:E$288,">="&W3)/(COUNTA(D:D)-1)*100,这里的E$288, 288从A1单元格数值,随A1单元格 数值改自动改变。
 
=COUNTIF(E$2:INDIRECT(ADDRESS(A1,COLUMN(E1))),">="&W3)/(COUNTA(D:D)-1)*100
 
引用规则:
 
A1相对引用
$A1绝对引用列
A$1绝对引用行
$A$1绝对引用行和列
$在谁的前面就绝对引用谁
F4是在四种引用间相互转换的快捷键(在编辑栏输入公式时按下F4功能键可进行切换)

相对引用、绝对引用和混合引用是指在公式中使用单元格或单元格区域的地址时,当将公式向旁边复制时,地址是如何变化的。
具体情况举例说明:
1、相对引用,复制公式时地址跟着发生变化,如C1单元格有公式:=A1+B1
当将公式复制到C2单元格时变为:=A2+B2
当将公式复制到D1单元格时变为:=B1+C1
2、绝对引用,复制公式时地址不会跟着发生变化,如C1单元格有公式:=$A$1+$B$1
当将公式复制到C2单元格时仍为:=$A$1+$B$1
当将公式复制到D1单元格时仍为:=$A$1+$B$1
3、混合引用,复制公式时地址的部分内容跟着发生变化,如C1单元格有公式:=$A1+B$1
当将公式复制到C2单元格时变为:=$A2+B$1
当将公式复制到D1单元格时变为:=$A1+C$1
规律:加上了绝对地址符“$”的列标和行号为绝对地址,在公式向旁边复制时不会发生变化,没有加上绝对地址符号的列标和行号为相对地址,在公式向旁边复制时会跟着发生变化。混合引用时部分地址发生变化。
 

http://ift.tt/16VjJrH

Excel 批处理 填充空白行

October 14, 2014
选中A1到A10,F5,定位条件--空值,确定
编辑栏输入:
=A1
ctrl+enter

Batch rename /w excel

July 15, 2014
  1. CMD –> DIR
  2. dir /b
  3. copy to excel
  4. A: old name, B: new name C: =CONCATENATE(“ren “,A1, ” “, B1)
  5. copy col C to txt, rename .bat
  6. run

EXCEL,将A列中相同代码所对应B列的数值进行自动求平均

March 31, 2014
C1输入=IF(COUNTIF(A$1:A1,A1)=1,AVERAGE(IF(A$1:A$100=A1,B$1:B$100)),””)数组公式,输入后先不要回车,按Ctrl+Shift+Enter结束计算,再向下填充。