1. 首頁 > 生活 > >

Excel提取數字公式 excel中提取數字的公式

小知識



文章插圖
Excel提取數字公式 excel中提取數字的公式

文章插圖
Excel工作表可以理解為一個由行、列組成的二維數組 。Excel公式支持數組和數組公式 。數組可以是常量數組，也可以是單元格區域的引用 。使用數組和數組公式時，可以逐數組元素或逐單元格處理后返回結果 。
下面直接從一個實例入手，再去討論相關知識點 。
1 實例（從文本里提取數字）
相關數據及需要達到的效果：
序號記錄金額1吃飯15元152朋友過生日送禮金300元3003買米95954火車票200塊回家2005房租900元9006水費200塊錢2007電費250元250
關鍵是要獲得第一個數字出現的位置 。
試著使用下面的公式
=FIND(1,B2,1)
可以返回3，表示字符1在字符串“吃飯15元”中的位置 。
如果要查找0,1,2,3,4,5,6,7,8,9十個字符呢？這時，數組及數組公式就可以派上用場了 。使用數組{0,1,2,3,4,5,6,7,8,9}作為參數，可以逐步處理每一個數字在字符串中出現的位置，返回的也是一個數組（10個值） 。由此，我們選擇一個有10個單元格的連續區域D2:M2，輸入=FIND({0,1,2,3,4,5,6,7,8,9},B2,1)，同時按下CTRL+SHIFT+ENTER，然后復制數組公式，得到如下效果：
錯誤值#VALUE!表示某數字在字符串中不存在，所以也就不能返回位置值 。
為了避免出現錯誤值，可以在字符串后串一個包含10個數字字符的數字字符串：
上表中每行最小的數字就是字符串中每一個數字出現的位置，為此我們可以使用MIN()函數 。=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&”0123456789″,1))
下一步要獲取數字長度 。
函數LEN()可以返回字符數，函數LENB()可以返回字符串的字節數，一個數字一個字節，一個漢字兩個字節，組合使用可以獲取數字的長度：=LEN(B2)-(LENB(B2)-LEN(B2))
使用mid函數即可以截取數字，在公式前使用兩個負號，可將文本轉換為數字：
=–MID(B2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&”0123456789″,1)),LEN(B2)-(LENB(B2)-LEN(B2)))
以下公式可以做一個改良：
1 {0,1,2,3,4,5,6,7,8,9}可以用ROW($A$1:$A$10)-1代替；
2 “0123456789”可以用5/19代替 。
5/19 = 0.26315789474
得到的下面公式具有同樣效果，更通用和簡潔：
=–MID(B5,MIN(FIND(ROW($A$1:$A$10)-1,B5&5/19,1)),LEN(B5)-(LENB(B5)-LEN(B5)))
2 數組及數組公式相關知識
2.1 數組公式
數組就是一組數據，數組公式可以進行多重運算（對每一個數組元素或區域的每一個單元格都應用操作），減少了多次寫于單元格的過程，可以實現常用公式較煩鎖的操作，一步到位 。
2.2 刪除數組公式
當我們對一個區域應用數組公式時，如D2:D6，使用此區域，輸入{=B2:B6*C2:C6}，按CTRL+SHIFT+ENTER，會在D2:D6的每個單元格輸入{=B2:B6*C2:C6}，都是一樣，此時數組公式是一個整體，不能更改數組的一部分，也就是不能單獨刪除其中的一個單元格的數組公式，我們要刪除數組公式要全部選中，當我們有時修改一個單元格的數組公式，不能退出時，大家記得按ESC鍵 。
2.3 一個單元格顯示數組的情況
由于一個單元格內只能儲存一個數值，所以當結果是一組數據時，單元格只返回第一個值 。
2.4 數組分類
可分為橫向數組、縱向數組、區域數組（多行多列），也可以按維來分，橫向數組和縱向數組都屬于一維數組，區域數組屬于二維數，像這種｛1,2,3,1}就是常量數組
2.5 怎樣查看數組的結果（也就是選擇公式的一部分求結果），當然是獨孤九劍F9或使用“公式求值”來按順序求值復合公式的每一部分 。
2.6 數組成員中間有時有分號，有時用逗號是怎么回事？橫向數組用逗號分開，縱向數組用分號分開 。
2.7 常量數據在函數里的應用
a) 23 =INDEX({23,24,25,22},1,1)
b) 24=INDEX({23,24,25,22},1,2)
c) 25=INDEX({23,24,25,22},1,3)
d) 22=INDEX({23,24,25,22},1,4)
2.8 要么用某些函數來取其共性,如SUM Max/Min,Small/Large等
a) 94=Sum({23,24,25,22})
b) 25=Max({23,24,25,22})
c) 22=Min({23,24,25,23})
d) 25=Large({23,24,25,22},ROW(A1))
e) 22 =SMALL({23,24,25,22},ROW(A1))
f) 23=SMALL({23,24,25,22},ROW(A2))
g) 24=SMALL({23,24,25,22},ROW(A3))
h) 25=SMALL({23,24,25,22},ROW(A4))
2.9 參數
a) 數組公式最大的特征就是所引用的參數是數組參數，包括區域數組和常量數組 。
【Excel提取數字公式 excel中提取數字的公式】b) 區域數組，是一個矩形的單元格區域,如 $A$1:$D$5
c) 常量數組，是一組給定的常量,如{1,2,3}或{1;2;3}或{1,2,3;1,2,3}
d) 數組公式中的參數必須為”矩形”，如{1,2,3;1,2}就無法引用了
2.10 輸入
同時按下CTRL+SHIFT+ENTER，數組公式的外面會自動加上大括號{}予以區分 。
3 更多實例
3.1 求表一區域F42:G44大于10數據和
公式：{=SUM((F42:G44>10)*(F42:G44))}
公式解釋：F42:G44>10會返回{TRUE,FALSE;FALSE,TRUE;TRUE,FALSE}，在運算時True=1 False=0 。
然后數組{TRUE,FALSE;FALSE,TRUE;TRUE,FALSE}和區域數組(F42:G44))相對應的數據進行相乘，相乘之后得到一個新的數組{20,0;0,50;30,0}，然后用求和函數Sum進行求和，最后記得三鍵一齊下CTRL+SHIFT+ENTER
3.2 求表二產品AA的總價
公式：=SUM((C50:C54=”AA”)*(D50:D54)*(E50:E54))
c) 公式解釋：=SUM((C50:C54=”AA”)*(D50:D54)*(E50:E54))
先用判斷區域C50:C54是否有等于AA的，結果返回 {TRUE;FALSE;FALSE;TRUE;FALSE}，因為在運算過程中True=1，False=0這樣就得到{=SUM({TRUE;FALSE;FALSE;TRUE;FALSE}*(D50:D54)*(E50:E54))}，然后因為任何數字和0相乘都等于0，也就是符合為AA的就不為0，不符合的就為0上面三個數組相乘最后得到一個新的數組{100;0;0;250;0}，最后用Sum求和，三鍵一齊下得到結果為350
4 Row()函數在數組公式中的運用
a) 談到數組公式，我們不得不說一下ROW()這個函數，它在數組公式中起到了很大的作用，許多公式中都需要用到它來作為參數 。
b) 我們先來做一個題目：求正整數列1,2,3,4……100這100個數字之和(首先假設你不知道等差數列求和公式，呵呵)，=SUM(ROW($A$1:$A$100)) 。
c) 我們利用Row(A1:A100)來產生1到100自然數，然后用Sum求和 。我們都知道，ROW()是用于返回單元格行號的函數，通常它只能引用一個參數 。但是在數組公式中，該函數就能引用多個單元格作為參數，對于整個引用區域進行分別運算，從而就能返回一組數據 。
ROW(A1)=1
ROW(A2)=2
……
ROW(A100)=100
ROW($A$1:$A$100)={1;2;3……100}
知道了這一點以后,我們就能在數組公式中利用這一個功能來得到一組連續的正整數 。
當然COLUMN()的作用和ROW()是相同的，上面的計算也能用以下公式：
{=SUM(COLUMN($A$1:$CV$1))}
但是相對于ROW()的引用方式來說，A1:A100要比A1:CV1更直觀地體現出所引用的是100行還是100列，所以ROW()一般來說使用得更普遍些，當然也不排除有時候需要用到COLUMN(),這就要看具體情況了 。