Excel 技巧搜集区 “实用篇”

woodstock_05

如果你在Excel上有自认的信心，欢迎你分享你的独门秘笈

1.请把每一个“技巧”或“分享”以一帖式发表（不要一帖，多个技巧）

2.所有无关，或感谢的回复将以“回复管理”删除

3.如有任何疑问，请另行开帖。

4.请注明技巧的使用版本（有的为Excel 2003）




1.整合两个cells (1)
2.整合两个cells (2)
3.Array 篇
4. 简单的批量修改Data
5.统计篇：=SUM(numbers,cells,arrays)
6.统计篇：=SUM(numbers,cells,arrays)（续）
7. VLOOKUP(lookup value, table array, column index no, range lookup)
8.LEFT 的用法：=LEFT(text, num_chars)
9.RIGHT 的用法：=RIGHT(text, num_chars)
10.MID 的用法：=MID(text, start_num, num_chars)
11.IF 的用法 （logical test）
12."掩盖"cell 的价值或方程式

woodstock_05

=Concatenate(A1,"/",B1)

如何把两排的东西合在一起


           之前                             之后
         A          B                          C
1     1234     ABCD                  1234/ABCD
2     4567     EFGH                  4567/EFGH
3      789       IJK                      789/IJK


Excel 2003

kiam06

=Concatenate(A1,"/",B1)

如何把两排的东西合在一起


           之前                             ...
woodstock_05 发表于 23-6-2010 08:48 PM

我来挑战你先。。。

要整合两个cells的东西，无需复杂formula，你可以直接用 & 就可解决！
如，=A1&B1
或者 =A1&"/"&B1
即可！~

kiam06

到我出题，来个高等级的！！！
Array 篇！！

题：如何把一列的资料轻易转成一竖？
如：把A1~E1 变成 A3~A7，一个formula完成！！

	A	B	C	D	E
1	I	You	We	They	He
2
3	I
4	You
5	We
6	They
7	He

select A3~A7，打入" =TRANSPOSE(A1:E1) "然后同时按下 Ctrl + Shift + Enter 即可！！

woodstock_05

我来挑战你先。。。
kiam06 发表于 23-6-2010 11:40 PM

小弟我差多了，不敢挑战。

简单的批量修改Data

开启 Ctrl F 的第二 Tab， Replace

在 Find What  ；填入想修改的data (123)
Replace with ：填入替换的data (A)


123456                    A456
123ABC           >      AABC         
123EFG                    AEFG

kiam06

谢谢版主加分。。。
统计篇：=SUM(numbers,cells,arrays)

例：

	A	B	C	D	E
1	10	20	30	40	=SUM(A1:E1)
2	15	30	45	60	=SUM(A2:E2)
3	5	10	15	20	=SUM(A3:E3)
4	=SUM(A1:A4)	=SUM(B1:B4)	=SUM(C1:C4)	=SUM(D1:C4)	=SUM(A1:D3)

结果：

	A	B	C	D	E
1	10	20	30	40	100
2	15	30	45	60	150
3	5	10	15	20	50
4	30	60	90	120	300

kiam06

继#6的 =sum()

Formula:

	A	B	C
1	100	2	=A1*B1
2	50	3	=A2*B2
3	75	2	=A3*B3
4		Total:	=SUM(C1:C3)

Answer:

	A	B	C
1	100	2	200
2	50	3	150
3	75	2	150
4		Total:	500

更快速拿到C4答案的formula：=SUM(A1:A3*B1:B3)  <<务必同时按下Ctrl+Shift+Enter

kiam06

查找和引用数据：

=VLOOKUP(lookup value, table array, column index no, range lookup)

lookup value = 指定的数据
table array = 目标范围
column index no = 目标数据的列数
range lookup = 精准/模糊寻找

例：

	A	B	C	D	E
1	Company B	=VLOOKUP(A1,D:E,2,0)		Company A	C1001
2	Company A	=VLOOKUP(A2,D:E,2,0)		Company B	C1002
3	Company D	=VLOOKUP(A3,D:E,2,0)		Company C	C1003
4	Company B	=VLOOKUP(A4,D:E,2,0)		Company D	C1004
5	Company C	=VLOOKUP(A5,D:E,2,0)
6	Company D	=VLOOKUP(A6,D:E,2,0)
7	Company C	=VLOOKUP(A7,D:E,2,0)
8	Company A	=VLOOKUP(A8,D:E,2,0)

结果：

	A	B	C	D	E
1	Company B	C1002		Company A	C1001
2	Company A	C1001		Company B	C1002
3	Company D	C1004		Company C	C1003
4	Company B	C1002		Company D	C1004
5	Company C	C1003
6	Company D	C1004
7	Company C	C1003
8	Company A	C1001

注：
B1的 = VLOOKUP(A1 , D:E , 2 , 0)
A1 想要寻找数据
D:E 目标范围，可以把范围缩小至 D1:E4 或 D$1:E$1 （$表示当你向下拉你的FORMULA时，带有$的数据不会改变！）
2 从目标范围的第一个列向右算起直至目标的列数 （要是目标数据在列F，formula应改为 VLOOKUP(A1 , D:F , 3 , 0) ）
0 模糊寻找 1=true, 0=false，不建议使用，所以我放0

超级飞毛腿

LEFT 的用法：=LEFT(text, num_chars)

num_chars: 字的数目

有两种用法：
1）架设我想要从 “Speaker” 里抽出 “Speak" 的字眼 ，方程式 =left("Speaker",5)
    答案就会是 "Speak" 了。 5 的意识是左边算起的五个字。

                                                           或
2）你也可以将方程式写成 =left(A1,5) <--- (架设 cell A1 是 Speaker)

超级飞毛腿

RIGHT 的用法：=RIGHT(text, num_chars)

num_chars: 字的数目

有两种用法：
1）架设我想要从 “Speaker” 里抽出 “peaker" 的字眼 ，方程式 =Right("Speaker",6)
    答案就会是 "peaker" 了。 6 的意识是从右边算起的六个字。

                                                           或

2）你也可以将方程式写成 =right(A1,6) <--- (架设 cell A1 是 Speaker)

超级飞毛腿

MID 的用法：=MID(text, start_num, num_chars)

start_num:  第几个开始的数目
num_chars: 字的数目

有两种用法：
1）架设我想要从 “Speaker” 里抽出 “peak" 的字眼 ，方程式 =mid("Speaker",2,4)
    答案就会是 "peak" 了。 2的意思是第二个 character 开始， 4 的意识是几个 character 的需要， 如：peak 需要4 个character。

                                                           或
2）你也可以将方程式写成 =mid(A1,2,4) <--- (架设 cell A1 是 Speaker)

超级飞毛腿

IF 的用法：

=IF(logical_test , value_if_true , value_if_false)

=IF(条件,真,假) <-- 中文解释

当条件为真是, 执行 “真” 部份语句,否则执行 “假” 部份语句.

Example:

架设 cell A1 的 value 是 10 ，我们在 cell B1 用上 IF 的方程式如：

=IF(A1>11 , "INCORRECT", "CORRECT")

那 CELL B1 的答案将会是 “INCORRECT”。。。(执行 “假” 部份语句)

如果 CELL A1 的价值是 8 ，那 CELL B1 的答案将改换成 "CORRECT" (执行 “真” 部份语句)

超级飞毛腿

掩盖的方法：

要如何"掩盖"cell 的价值或方程式呢？
通常大家都会选用白色的 font 来掩盖着不想让人看到在 cell 上的价值或方程式。这里有个更好的方式。。。

那就是你只需要在你想掩盖的 cell 上 ，right-hand click 选择 Format Cell 或 CTRL 1。

选择了之后 ，在 Category 格子里选择 Custom , 然后在  Type 那儿打上 ; 符号 then ok 就行了。

好处 ：可以掩盖你不想让人看到的资料或方程式。

坏处 ：如果你的 graph 是 link 着你想掩盖的价值 / 资料 / 方程式 ，那 graph 的资料也会被掩盖了。

Cyclone

如何把0到5设为小
6-9 设为大
0，2，4，6，8 设为双
1，3，5，7，9 为单

excel

超级飞毛腿

如何把0到5设为小
6-9 设为大
0，2，4，6，8 设为双
1，3，5，7，9 为单

excel
Cyclone 发表于 28-6-2010 11:03 AM

假设 cell A1 是你要 test value 的 cell:
1)  如何把0到5设为小, 6-9 设为大
     =IF(A1<=5, "SMALL", "BIG")


2)  0，2，4，6，8 设为双 (Even) ; 1，3，5，7，9 为单 (Odd)
     如果是单单这几个数目字 ,最快的方法是:
     =IF(OR(A1=0, A1=2, A1=4, A1=6, A1=8), "EVEN", "ODD")

Assumed that the number tested is only 0 - 9.

羁

请教技巧

如何让我输入1时就变成Mcd，输入2时就变成KCF，输入3时就变成A&W,输入4时就变成PizzaHut,..... ...
kennethwch 发表于 29-7-2010 02:10 PM

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As String
i = Worksheets("Sheet1").Range("B2")
Select Case i

Case 1
    Worksheets("Sheet1").Range("B2") = "MCD"
Case 2
    Worksheets("Sheet1").Range("B2") = "KFC"
Case 3
    Worksheets("Sheet1").Range("B2") = "A&W"
Case 4
    Worksheets("Sheet1").Range("B2") = "Pizza Hut"
End Select

End Sub

kiam06

计算字串长度：=len(text)

例：

	A	B	C	D	E
1	aaaa	=len(A1)
2	11111	=len(A2)
3	a1a1a1	=len(A3)
4	1a1a1a1	=len(A4)

结果：

	A	B	C	D	E
1	aaaa	4
2	11111	5
3	a1a1a1	6
4	1a1a1a1	7

kiam06

继#18的：=len(text) 的应用篇~提取号码

例：

	A	B	C	D	E
1	RM8	=Right(A1,len(A1)-2)		9°C	=Left(A1,len(A1)-2)
2	RM90	=Right(A2,len(A2)-2)		32°C	=Left(A2,len(A2)-2)
3	RM100	=Right(A3,len(A3)-2)		100°C	=Left(A3,len(A3)-2)
4	RM99.50	=Right(A4,len(A4)-2)		2200°C	=Left(A4,len(A4)-2)

结果：

	A	B	C	D	E
1	RM8	8		9°C	9
2	RM90	90		32°C	32
3	RM100	100		100°C	100
4	RM99.50	99.50		2200°C	2200

vrz

以下是Visual Basic，自动从300.20 转去英文 ：Three Hundred Dollars and Twenty Cents

请问如何把它改成 ：Ringgit Malaysia : Three Hundred And Cents Twenty Only.
(加了红色字体，删除Dollars字体）

Function : =SpellNumber(A2)

Option Explicit 'Main Function Function SpellNumber(ByVal MyNumber)     Dim Dollars, Cents, Temp     Dim DecimalPlace, Count     ReDim Place(9) As String     Place(2) = " Thousand "     Place(3) = " Million "     Place(4) = " Billion "     Place(5) = " Trillion "     ' String representation of amount.     MyNumber = Trim(Str(MyNumber))     ' Position of decimal place 0 if none.     DecimalPlace = InStr(MyNumber, ".")     ' Convert cents and set MyNumber to dollar amount.     If DecimalPlace > 0 Then         Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _                   "00", 2))         MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))     End If     Count = 1     Do While MyNumber <> ""         Temp = GetHundreds(Right(MyNumber, 3))         If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars         If Len(MyNumber) > 3 Then             MyNumber = Left(MyNumber, Len(MyNumber) - 3)         Else             MyNumber = ""         End If         Count = Count + 1     Loop     Select Case Dollars         Case ""             Dollars = "No Dollars"         Case "One"             Dollars = "One Dollar"          Case Else             Dollars = Dollars & " Dollars"     End Select     Select Case Cents         Case ""             Cents = " and Cents No"         Case "One"             Cents = " and Cent One"               Case Else             Cents = " and " & Cents & " Cents"     End Select     SpellNumber = Dollars & Cents End Function        ' Converts a number from 100-999 into text Function GetHundreds(ByVal MyNumber)     Dim Result As String     If Val(MyNumber) = 0 Then Exit Function     MyNumber = Right("000" & MyNumber, 3)     ' Convert the hundreds place.     If Mid(MyNumber, 1, 1) <> "0" Then         Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "     End If     ' Convert the tens and ones place.     If Mid(MyNumber, 2, 1) <> "0" Then         Result = Result & GetTens(Mid(MyNumber, 2))     Else         Result = Result & GetDigit(Mid(MyNumber, 3))     End If     GetHundreds = Result End Function        ' Converts a number from 10 to 99 into text. Function GetTens(TensText)     Dim Result As String     Result = ""           ' Null out the temporary function value.     If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19...         Select Case Val(TensText)             Case 10: Result = "Ten"             Case 11: Result = "Eleven"             Case 12: Result = "Twelve"             Case 13: Result = "Thirteen"             Case 14: Result = "Fourteen"             Case 15: Result = "Fifteen"             Case 16: Result = "Sixteen"             Case 17: Result = "Seventeen"             Case 18: Result = "Eighteen"             Case 19: Result = "Nineteen"             Case Else         End Select     Else                                 ' If value between 20-99...         Select Case Val(Left(TensText, 1))             Case 2: Result = "Twenty "             Case 3: Result = "Thirty "             Case 4: Result = "Forty "             Case 5: Result = "Fifty "             Case 6: Result = "Sixty "             Case 7: Result = "Seventy "             Case 8: Result = "Eighty "             Case 9: Result = "Ninety "             Case Else         End Select         Result = Result & GetDigit _             (Right(TensText, 1))  ' Retrieve ones place.     End If     GetTens = Result End Function       ' Converts a number from 1 to 9 into text. Function GetDigit(Digit)     Select Case Val(Digit)         Case 1: GetDigit = "One"         Case 2: GetDigit = "Two"         Case 3: GetDigit = "Three"         Case 4: GetDigit = "Four"         Case 5: GetDigit = "Five"         Case 6: GetDigit = "Six"         Case 7: GetDigit = "Seven"         Case 8: GetDigit = "Eight"         Case 9: GetDigit = "Nine"         Case Else: GetDigit = ""     End Select End Function

请高手指教，谢谢！

Felix2212

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As String
i = Worksheets("Sheet1".R ...
羁 发表于 29-7-2010 10:42 PM

    请问。。。这应该怎样运用？需要"="吗？