發了那麼多做賬方法, 是時候發點技能提升的文了
今天就講E X C E L !
主要有以下內容:
◆Excel的統計不重複的方法
◆如何清理報表中多餘字元
◆出現#VALUE! 錯誤該怎麼處理
◆sumif函數的使用方法
【Excel的統計不重複的方法】
來看看我的手工台賬:
小本買賣, 上面都是便利店的王牌銷售產品哦。
隨著種類的不斷豐富, 我想知道我進貨的種類一共有多少, 怎麼做?
思路一
文字描述:
找到每一個品種在整個列表中有幾個, 如果有N個, 則自己的數字變為1/N, N個1/N相加等於1。
公式:
{=SUM(1/COUNTIF(A2:A8,A2:A8))}
再次囉嗦一下:公式的{ }不是手動輸入的, 而是輸入公式後, 同時按住Ctrl+Shift+Enter自動生成的, { }這個符號表示的陣列運算, 如果對陣列運算不太熟悉的, 請看一下本公眾號的其它文章哦。
我們將公式做一下拆解:
COUNTIF(A2:A8,A2:A8) 這個函數是怎麼用的呢?
COUNTIF(要判斷的區域,判斷條件)
平時最最常用的使用方式其實是下面這種:
=COUNTIF(A2:A8,A2)
就是查找A2(速食麵)在整個區域出現了幾次。
重新粘貼一下原表格:
第一步:=COUNTIF(A2:A8,A2:A8) 實現了什麼結果呢?
就是得到從A2至A8, 統計每一個儲存格的內容在整個區域出現的次數, 返回結果
{1,2,1,1,2,2,2}
第二步:=1/COUNTIF(A2:A8,A2:A8)實現了什麼呢?
1除以次數, 就是把每個物品的個數作為分母, 返回值
{1,1/2,1,1,1/2,1/2,1/2,1/2}
第三步:=SUM(1/COUNTIF(A2:A8,A2:A8))實現Sum函數將全部陣列結果相加,
最終結果等於5
思路2
直接放解決方案:
公式:
{=SUM(--(MATCH(A2:A8,A2:A8,0)=(ROW(A2:A8)-1)))}
從簡至難:
第一步:ROW(A2:A8)
返回A2至A8的行數, 得到陣列{2;3;4;5;6;7;8}
第二步:ROW(A2:A8)-1
得到陣列{1;2;3;4;5;6;7}
第三步:先解釋一下MATCH函數, MATCH函數是返回某個文本在某個陣列的第幾位元, 常用寫法是:
=MATCH(內容,一個資料區域,0)
我們工作中基本上用到的都是精確匹配, 所以記住第三個參數是0就行。
假如編輯的函數是=MATCH(A8,A2:A8,0), 則是查找加多寶在整個陣列中的第幾位元。
雖然整個區域中有兩個加多寶, 但是Excel找到第一個加多寶的時候, 就默認已經找到, 不會繼續查找了, 所以=MATCH(A8,A2:A8,0)的返回值是2 而不是7。
因此MATCH(A2:A8,A2:A8,0), 得到陣列{1;2;3;4;5;5;2}
重新粘貼一下原表:
用MATCH(A2:A8,A2:A8,0)
函數得到陣列{1;2;3;4;5;5;2}
用ROW(A2:A8)-1
函數得到陣列{1;2;3;4;5;6;7}
有沒有注意到:
如果這個物品是第一次出現的時候兩個陣列的值是一樣的, 如果這個物品是第二次出現的, 兩個陣列對應的數位就是不一樣的。
第四步:MATCH(A2:A8,A2:A8,0)=(ROW(A2:A8)-1)
這樣一判斷, 返回值就是:
{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE}
第五步:這一步也很重要哦
--(MATCH(A2:A8,A2:A8,0)=(ROW(A2:A8)-1))
這個相當於做了數字運算, 將TRUE轉化為1, 將FALSE轉化為0。
所以上面的返回值就是{1;1;1;1;1;0;0}
第六步:最後一步就比較簡單了
=SUM(--(MATCH(A2:A8,A2:A8,0)=(ROW(A2:A8)-1)))
就是=SUM{1;1;1;1;1;0;0}
結果等於5
記住!輸入公式後同時按住Ctrl+Shift+Enter哦
文字描述:用MATCH獲取某個物品在整個列表中處於第幾個, 如果是多個相同的物品, MATCH函數返回的是第一個的位置。
比如加多寶, 用MATCH返回的就是2。 然後在用ROW函數獲取每個儲存格所在的行數, 行數減去1就是這個儲存格在陣列中的位置。
如果兩個數字相等, 就是第一次出現, 如果不等就不是第一次出現。
然後將相等的轉化為1,
相加就是不重複的物品數量。
【如何清理報表中多餘字元】
現在, 日常工作中我們接觸到各種辦公、財稅、管理軟體越來越多, 資料的來源也越來越複雜, 有的資料是從開票系統導入的、有的資料是從HR系統導入的、還有的資料可能是從網頁或者ERP軟體中導入的。
對於這些不同來源的資料, 有的可能直接在報表中就可以使用,但是有的卻需要清除一部分多餘的資料才能夠正常使用。
例如我們在整理資料時,有時需要保留從每個儲存格裡固定的字元前的資料,清除其他多餘的資料。如下表所示
我們要清除網站名稱後面所有多餘的字元和空格,這時候我們應該怎麼辦呢?
逐個儲存格挨個去清除?太耗時間了,傷不起啊……
其實很簡單,因為所有網站名稱後面都是“|”符號,我們只需要在報表中插入新的一列,然後在B2儲存格輸入“=LEFT(A2,FIND("=|",A2))”,就可以將“|”後面的多餘資料都清除了。
其中FIND函數是返回“|”符號在A2儲存格中字串的位置;LEFT函數是返回指定位元數前的字元。
在本例中,FIND函數找到“|”符號位於儲存格字串的第7位元,而LEFT函數則會提取出字串中前7位元字元:“財稅辦公論壇|”
但是,用這公式後尷尬的是“|”沒有刪掉……
怎麼辦呢?
不就一個字元嗎?呵呵,減掉它就可以啦!
我們在B2儲存格中輸入公式:“=LEFT(A3,FIND("|",A3)-1)”,看看,是不是就搞定啦^_^
關鍵點:FIND函數 LEFT函數你會了嗎?
【出現#VALUE! 錯誤該怎麼處理】
當使用錯誤的參數或運算物件類型時,或者當公式自動校正功能不能更正公式時,將產生錯誤值#VALUE!。
原因一:在需要數位或邏輯值時輸入了文本,Excel不能將文本轉換為正確的資料類型。
解決方法:確認公式或函數所需的運算子或參數正確,並且公式引用的儲存格中包含有效的數值。例如:如果儲存格A1包含一個數字,儲存格A2包含文本"學籍",則公式"=A1+A2"將返回錯誤值#VALUE!。可以用SUM工作表函數將這兩個值相加(SUM函數忽略文本):=SUM(A1:A2)。
原因二:將儲存格引用、公式或函數作為陣列常量輸入。
解決方法:確認陣列常量不是儲存格引用、公式或函數。
原因三:賦予需要單一數值的運算子或函數一個數值區域。
解決方法:將數值區域改為單一數值。修改數值區域,使其包含公式所在的資料行或列。
【sumif函數的使用方法】
常用函數是sumif函數,很多朋友都比較瞭解它:條件求和。
Sumif函數一般是根據某一列資料是否滿足指定的條件,來計算與之對應的另一列資料之和。
sumif函數語法是:SUMIF(range,criteria,sum_range)
sumif函數的參數如下:
第一個參數:Range為條件區域,用於條件判斷的儲存格區域。
第二個參數:Criteria是求和條件,由數位、邏輯運算式等組成的判定條件。
第三個參數:Sum_range 為實際求和區域,需要求和的儲存格、區域或引用。
例如:在轎車銷售報表中,我們根據轎車品牌統計其銷售合計。
=SUMIF(C3:C17,"大眾",D3:D17) 根據C列的所屬品牌統計大眾汽車的銷售合計。
但是,現實中銷售報表往往不是那麼理想,例如,銷售報表是這樣的,報表中沒有“大眾”的品牌,還能求大眾轎車的銷售合計了嗎?
通過細緻的觀察,我們可以發現,現在我們要求的是車型中包含“大眾”二字的相關銷售記錄的合計資料。包含“大眾”可能是在前,也可能在後,這時候大家應該想到萬用字元“*”、“?”,前者表示多個任意字元,而後者表示一個任意字元。毫無疑問,包含“大眾”二字的車型,應該是“*大眾*”,剩下的就so easy了。
很多朋友使用sumif函數,始終局限於列的條件求和,卻不知道,對於資料行,sumif函數一樣可以靈活進行判斷求和。
例如在銷售報表中,如果條件按行記錄,這時候,我們只需要把sumif中的條件區域和資料區域都選中為對應行就可以了。
深圳曼陀羅財稅培訓 www.szmandala.com
深圳國興美廣告 www.szgxm.com
有的可能直接在報表中就可以使用,但是有的卻需要清除一部分多餘的資料才能夠正常使用。例如我們在整理資料時,有時需要保留從每個儲存格裡固定的字元前的資料,清除其他多餘的資料。如下表所示
我們要清除網站名稱後面所有多餘的字元和空格,這時候我們應該怎麼辦呢?
逐個儲存格挨個去清除?太耗時間了,傷不起啊……
其實很簡單,因為所有網站名稱後面都是“|”符號,我們只需要在報表中插入新的一列,然後在B2儲存格輸入“=LEFT(A2,FIND("=|",A2))”,就可以將“|”後面的多餘資料都清除了。
其中FIND函數是返回“|”符號在A2儲存格中字串的位置;LEFT函數是返回指定位元數前的字元。
在本例中,FIND函數找到“|”符號位於儲存格字串的第7位元,而LEFT函數則會提取出字串中前7位元字元:“財稅辦公論壇|”
但是,用這公式後尷尬的是“|”沒有刪掉……
怎麼辦呢?
不就一個字元嗎?呵呵,減掉它就可以啦!
我們在B2儲存格中輸入公式:“=LEFT(A3,FIND("|",A3)-1)”,看看,是不是就搞定啦^_^
關鍵點:FIND函數 LEFT函數你會了嗎?
【出現#VALUE! 錯誤該怎麼處理】
當使用錯誤的參數或運算物件類型時,或者當公式自動校正功能不能更正公式時,將產生錯誤值#VALUE!。
原因一:在需要數位或邏輯值時輸入了文本,Excel不能將文本轉換為正確的資料類型。
解決方法:確認公式或函數所需的運算子或參數正確,並且公式引用的儲存格中包含有效的數值。例如:如果儲存格A1包含一個數字,儲存格A2包含文本"學籍",則公式"=A1+A2"將返回錯誤值#VALUE!。可以用SUM工作表函數將這兩個值相加(SUM函數忽略文本):=SUM(A1:A2)。
原因二:將儲存格引用、公式或函數作為陣列常量輸入。
解決方法:確認陣列常量不是儲存格引用、公式或函數。
原因三:賦予需要單一數值的運算子或函數一個數值區域。
解決方法:將數值區域改為單一數值。修改數值區域,使其包含公式所在的資料行或列。
【sumif函數的使用方法】
常用函數是sumif函數,很多朋友都比較瞭解它:條件求和。
Sumif函數一般是根據某一列資料是否滿足指定的條件,來計算與之對應的另一列資料之和。
sumif函數語法是:SUMIF(range,criteria,sum_range)
sumif函數的參數如下:
第一個參數:Range為條件區域,用於條件判斷的儲存格區域。
第二個參數:Criteria是求和條件,由數位、邏輯運算式等組成的判定條件。
第三個參數:Sum_range 為實際求和區域,需要求和的儲存格、區域或引用。
例如:在轎車銷售報表中,我們根據轎車品牌統計其銷售合計。
=SUMIF(C3:C17,"大眾",D3:D17) 根據C列的所屬品牌統計大眾汽車的銷售合計。
但是,現實中銷售報表往往不是那麼理想,例如,銷售報表是這樣的,報表中沒有“大眾”的品牌,還能求大眾轎車的銷售合計了嗎?
通過細緻的觀察,我們可以發現,現在我們要求的是車型中包含“大眾”二字的相關銷售記錄的合計資料。包含“大眾”可能是在前,也可能在後,這時候大家應該想到萬用字元“*”、“?”,前者表示多個任意字元,而後者表示一個任意字元。毫無疑問,包含“大眾”二字的車型,應該是“*大眾*”,剩下的就so easy了。
很多朋友使用sumif函數,始終局限於列的條件求和,卻不知道,對於資料行,sumif函數一樣可以靈活進行判斷求和。
例如在銷售報表中,如果條件按行記錄,這時候,我們只需要把sumif中的條件區域和資料區域都選中為對應行就可以了。
深圳曼陀羅財稅培訓 www.szmandala.com
深圳國興美廣告 www.szgxm.com