查看: 47288|回复: 283
|
Excel 技巧搜集区 “实用篇”
[复制链接]
|
|
本帖最后由 woodstock_05 于 26-6-2010 11:03 PM 编辑
如果你在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 的价值或方程式 |
|
|
|
|
|
|
|
楼主 |
发表于 23-6-2010 08:48 PM
|
显示全部楼层
本帖最后由 woodstock_05 于 23-6-2010 09:17 PM 编辑
=Concatenate(A1,"/",B1)
如何把两排的东西合在一起
之前 之后
A B C
1 1234 ABCD 1234/ABCD
2 4567 EFGH 4567/EFGH
3 789 IJK 789/IJK
Excel 2003 |
|
|
|
|
|
|
|
发表于 23-6-2010 11:40 PM
|
显示全部楼层
=Concatenate(A1,"/",B1)
如何把两排的东西合在一起
之前 ...
woodstock_05 发表于 23-6-2010 08:48 PM
我来挑战你先。。。
要整合两个cells的东西,无需复杂formula,你可以直接用 & 就可解决!
如,=A1&B1
或者 =A1&"/"&B1
即可!~ |
|
|
|
|
|
|
|
发表于 23-6-2010 11:48 PM
|
显示全部楼层
本帖最后由 kiam06 于 25-6-2010 09:40 AM 编辑
到我出题,来个高等级的!!!
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 即可!! |
评分
-
查看全部评分
|
|
|
|
|
|
|
楼主 |
发表于 24-6-2010 08:27 PM
|
显示全部楼层
我来挑战你先。。。
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 |
|
|
|
|
|
|
|
发表于 24-6-2010 08:52 PM
|
显示全部楼层
本帖最后由 kiam06 于 24-6-2010 08:56 PM 编辑
谢谢版主加分。。。
统计篇:=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 |
|
|
|
|
|
|
|
|
发表于 24-6-2010 09:08 PM
|
显示全部楼层
继#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 |
|
|
|
|
|
|
|
发表于 25-6-2010 09:36 AM
|
显示全部楼层
本帖最后由 kiam06 于 25-6-2010 09:39 AM 编辑
查找和引用数据:
=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 |
评分
-
查看全部评分
|
|
|
|
|
|
|
发表于 25-6-2010 02:17 PM
|
显示全部楼层
LEFT 的用法:=LEFT(text, num_chars)
num_chars: 字的数目
有两种用法:
1)架设我想要从 “Speaker” 里抽出 “Speak" 的字眼 ,方程式 =left("Speaker",5)
答案就会是 "Speak" 了。 5 的意识是左边算起的五个字。
或
2)你也可以将方程式写成 =left(A1,5) <--- (架设 cell A1 是 Speaker) |
|
|
|
|
|
|
|
发表于 25-6-2010 02:20 PM
|
显示全部楼层
RIGHT 的用法:=RIGHT(text, num_chars)
num_chars: 字的数目
有两种用法:
1)架设我想要从 “Speaker” 里抽出 “peaker" 的字眼 ,方程式 =Right("Speaker",6)
答案就会是 "peaker" 了。 6 的意识是从右边算起的六个字。
或
2)你也可以将方程式写成 =right(A1,6) <--- (架设 cell A1 是 Speaker) |
|
|
|
|
|
|
|
发表于 25-6-2010 02:23 PM
|
显示全部楼层
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) |
评分
-
查看全部评分
|
|
|
|
|
|
|
发表于 26-6-2010 03:32 PM
|
显示全部楼层
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" (执行 “真” 部份语句) |
|
|
|
|
|
|
|
发表于 26-6-2010 10:54 PM
|
显示全部楼层
掩盖的方法:
要如何"掩盖"cell 的价值或方程式呢?
通常大家都会选用白色的 font 来掩盖着不想让人看到在 cell 上的价值或方程式。这里有个更好的方式。。。
那就是你只需要在你想掩盖的 cell 上 ,right-hand click 选择 Format Cell 或 CTRL 1。
选择了之后 ,在 Category 格子里选择 Custom , 然后在 Type 那儿打上 ; 符号 then ok 就行了。
好处 :可以掩盖你不想让人看到的资料或方程式。
坏处 :如果你的 graph 是 link 着你想掩盖的价值 / 资料 / 方程式 ,那 graph 的资料也会被掩盖了。 |
|
|
|
|
|
|
|
发表于 28-6-2010 11:03 AM
|
显示全部楼层
如何把0到5设为小
6-9 设为大
0,2,4,6,8 设为双
1,3,5,7,9 为单
excel |
|
|
|
|
|
|
|
发表于 30-6-2010 01:20 PM
|
显示全部楼层
如何把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. |
|
|
|
|
|
|
|
发表于 29-7-2010 10:42 PM
|
显示全部楼层
请教技巧
如何让我输入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 |
|
|
|
|
|
|
|
发表于 3-8-2010 04:37 PM
|
显示全部楼层
计算字串长度:=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 | | | |
|
|
|
|
|
|
|
|
发表于 3-8-2010 05:05 PM
|
显示全部楼层
继#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 |
|
|
|
|
|
|
|
|
发表于 15-9-2010 07:46 AM
|
显示全部楼层
以下是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
|
请高手指教,谢谢! |
|
|
|
|
|
|
|
发表于 15-9-2010 02:00 PM
|
显示全部楼层
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As String
i = Worksheets("Sheet1".R ...
羁 发表于 29-7-2010 10:42 PM
请问。。。这应该怎样运用?需要"="吗? |
|
|
|
|
|
|
| |
本周最热论坛帖子
|