許多人都在為從microsoftexcel的複雜單元格中提取信息而掙扎。我寫的關於如何用這個函數從Excel中提取數字或文本的文章中的許多評論和問題證明了這一點。顯然,如何從Excel工作表中分離出所需的數據並不總是很清楚。
我們已經從那篇文章中選擇了幾個問題在這裡進行討論,所以您可以看到解決方案是如何工作的。快速學習Excel並不容易,但使用這樣的現實問題會有很大幫助。
讀者阿德里問了以下問題:
I would like to extract the first set of numbers from a list, i.e. (122,90,84,118.4,128.9)Any ideas on what formula I can use?COIL112X2.5COIL90X2.5COIL84X2.0COIL118.4X1.8COIL128.9X2.0
事實上,要提取的數字長度不同,這比僅僅使用MID函數要複雜一些,但是通過組合幾個不同的函數,我們可以去掉左邊的字母以及“X”和右邊的數字,在新的單元格中只留下所需的數字。
下面是我們用來解決這個問題的公式:
=VALUE(LEFT((RIGHT(A1,(LEN(A1)-4))),FIND("X",A1)-5))讓我們從中間開始,找出解決辦法,看看它是如何工作的。首先,我們從FIND函數開始。在本例中,我們使用FIND(“X”,A1)。這個函數在A1單元格中查找字母X。當它找到一個X時,它返回它所在的位置。例如,對於第一個條目COIL112X2.5,它返回8。對於第二個條目,它返回7。
接下來,讓我們看看LEN函數。這只是返回單元格中字符串的長度減去4。結合正確的函數,我們從單元格中得到字符串減去前四個字符,這將從每個單元格的開頭刪除“COIL”。這部分的公式如下:RIGHT(A1,(LEN(A1)-4))。
下一級是LEFT函數。現在我們已經用FIND函數確定了X的位置,用RIGHT函數去掉了“COIL”,LEFT函數返回剩下的。讓我們把這個問題進一步細分。當我們簡化這兩個論點時,會發生以下情況:
=LEFT("112X2.5", (8-5))LEFT函數返回字符串中最左邊的三個字符(參數末尾的“-5”確保通過計算字符串中的前四個字符來消除正確的字符數)。
最後,VALUE函數確保返回的數字被格式化為數字,而不是文本。這個例子並不像在Excel中構建一個俄羅斯方塊工作遊戲那樣有趣,但它確實是一個很好的例子,說明了如何組合一些函數來解決問題。
讀者Yadhu Nandan也提出了類似的問題:
I want to know how to separate the numerical and alphabets.Example is – 4552dfsdg6652sdfsdfd5654I want 4552 6652 5654 in different cells
另一位讀者Tim K SW為這個特殊問題提供了完美的解決方案:
Assuming that 4552dfsdg6652sdfsdfd5654 is in A1 and you want these numbers extracted into 3 different cells. 4452 in say cell B1 and 6652 in C1 and 5654 in D1 you'd use these.B1:=MID(A1,1,4)C1:=MID(A1,10,4)D1:=MID(A1,21,4)
這些公式相當簡單,但是如果您不熟悉MID函數,您可能不瞭解它是如何工作的。MID有三個參數:單元格、結果開始的字符數和應提取的字符數。例如,MID(A1,10,4)告訴Excel從字符串的第十個字符開始取四個字符。
在三個單元格中使用三個不同的中間函數,可以將數字從這長串數字和字母中提取出來。顯然,只有在每個單元格中始終有相同數量的字符(字母和數字)時,此方法才有效。如果每個公式的數量不同,你需要一個更復雜的公式。
文章中最困難的一個請求是讀者Daryl:
Hi, I just want to ask how to separate very unformatted entry like:Rp. 487.500 (Nett 50% OFF)Rp 256.500 Nett 40% OFFRp99.000Rp 51.000/orang Nett (50% Off)
我花了一些時間來研究這個問題,自己也沒能想出解決辦法,於是我就去了Reddit。用戶UnrepiredyMnast提供了這個公式,它很長而且非常複雜:
=SUMPRODUCT(MID(0&LEFT(A1,IFERROR(SEARCH("%",A1)-4,LEN(A1))),LARGE(INDEX(ISNUMBER(--MID(LEFT(A1,IFERROR(SEARCH("%",A1)-4,LEN(A1))),ROW($1:$99),1))*ROW($1:$99),0),ROW($1:$99))+1,1)*10^ROW($1:$99)/10)如您所見,解碼此公式需要相當長的時間,並且需要非常紮實的Excel函數知識。為了幫助您弄清楚這裡到底發生了什麼,我們需要了解一些您可能不熟悉的函數。第一個是IFERROR,它捕獲一個錯誤(通常用磅符號表示,如#N/a或#DIV/0)並用其他內容替換它。在上面,您將看到IFERROR(SEARCH(“%,A1)-4,LEN(A1))。讓我們把它分解一下。
IFERROR查看第一個參數,即SEARCH(“%”,A1)-4。因此,如果A1單元格中出現“%”,則返回其位置,並從中減去四個位置。如果字符串中沒有“%”,Excel將創建錯誤,而返回A1的長度。
您可能不熟悉的其他函數要簡單一些;例如,SUMPRODUCT將數組的元素相乘然後添加。大返回一個範圍內的最大數。ROW與美元符號組合,返回對行的絕對引用。
瞭解所有這些函數是如何協同工作的並不容易,但是如果你從公式的中間開始,向外工作,你就會開始瞭解它在做什麼。這需要一段時間,但如果你對它的工作原理感興趣,我建議你把它放到一個Excel表格中,然後玩玩它。這是瞭解你工作的最好方法。
(另外,避免此類問題的最佳方法是更整潔地導入數據——這並不總是一個選項,但它應該是您的首選。)
正如您所見,解決任何Excel問題的最佳方法是一步一個腳印:從您所知道的開始,看看它給您帶來了什麼,然後從那裡開始。有時你會得到一個優雅的解決方案,有時你會得到一個非常長,混亂,複雜的東西。但只要成功,你就成功了!
別忘了尋求幫助。有一些非常有才華的Excel用戶在那裡,他們的幫助可以是非常寶貴的解決一個棘手的問題。
你有什麼解決棘手的提取問題的建議嗎?你知道我們解決上述問題的其他解決方案嗎?分享他們和你在下面的評論中的任何想法!
食物鏈和食物網的關鍵區別在於,食物鏈解釋了生態系統中能量流動的單一途徑,而食物鏈則解釋了生態系統內相互聯絡的許多能量流動途徑。 所有的動植物(包括人類)都需要食物來生存和工作。太陽是所有生物的主要能...
...》的一個新工具旨在透過一個簡單但具有開創性的工具來解決這個問題:Sideways Dictionary。 ...
...齡組的個人理財建議。它使用一個流行的Reddit縮寫ELI5來解釋它是為誰而設計的。ELI5是一個流行的網際網路縮略語,你應該知道,這意味著“解釋我五歲(歲)”。基本上,它告訴人們,對於初學者來說,要把一個複雜的主題啞...
... 這就解釋了為什麼很多人在你可能認識的人身上看到了一些建議,他們沒有和他們共享任何聯絡人,當然也沒有交換過電話號碼。一位參加**青少年聚會的家長說,會上有人建...
...準備好的時候會是這樣的: 按“確定”來解決您的目標。當它看起來不錯的時候,就按OK。當Goal Seek找到解決方案時,Excel會通知您。 再次單擊“確定”,您將看到透過更改單元格在選定單元格中求...
...應用程式的報價,讓您可以看到日期以及約會的名稱,以解決更大的複雜問題。再深入一點,你會發現,有更多的東西提供了幻想2。 ...
...檔案修訂歷史,以及在特定時間段內發生的更改。這對於解決bug很有用。 透過顏色編碼、語法高亮顯示、行號和補丁,視覺化差異並解決並行或並行開發所導致的衝突。 ...
... 本課程以上一課程中介紹的主題為基礎。它解釋了高階的圖表和繪圖,如何使用格式化工具和查詢函式。 ...