分類彙整:Excel

【EXCEL放題】數據分析必學!5類超實用公式大公開(下)

學會了上回的EXCEL攻略【EXCEL放題】數據分析必學!5類超實用公式大公開(上),開心職場要接著跟大家介紹更多實用的Excel公式囉!

◆最大最小值:MAX,MIN
◆找到指定字元:FIND
◆取出指定字元:LEFT,RIGHT

最大最小值

1. 最大值

=MAX(開始範圍1:結束範圍1,開始範圍2:結束範圍2,…)

若希望知道知道選取範圍內的最大值,就可以使用MAX公式,例如下範例=MAX(A3:A6,C3:C6),就是找到A3到A6和C3到C6數值之間最大值。

若包含多個不連續的範圍區間,記得用「,」將不同區間隔開。

2. 最小值

=MIN(開始範圍1:結束範圍1,開始範圍2:結束範圍2,…)

若希望知道知道選取範圍內的最小值,就可以使用MIN公式,例如下範例=MIN(A3:A6,C3:C6),就是找到A3到A6和C3到C6數值之間最小值。

若包含多個不連續的範圍區間,記得用「,」將不同區間隔開。

找到指定字元

=FIND(指定的字元,尋找的儲存格)

找出指定的字元在選定的儲存格內的位置,如果顯示2,代表指定的字元在儲存格內左側數起第2個位置。

下圖範例中FIND(“City",A6)代表找出「City」在A6這個儲存格內左側數來的第幾個字元。

至於「找到指定字元」在職場中該怎麼運用呢? 一般會和「取出指定字元」合用。

取出指定字元

1. 由左側取出字元

=LEFT(指定的儲存格,取出的字元數)

取出指定儲存格內,由左側開始數起的N個字元。

下圖範例中,LEFT(A6,B6-1)就是從A6這格中取出由左開始數起的7個字元。

2. 由右側取出字元

=RIGHT(指定的儲存格,取出的字元數)

如同LEFT,只是改成由右側開始算取出字元。

找到指定字元」和「取出指定字元」一起合用,就可以幫助你快速整理出來指定的資訊囉!例如你有多家客戶地址的一串資料,例如新北市OO區、台北市XX區…等。使用FIND(“市”)搭配LEFT,就可以取出顧客居住在哪一個縣市。

以上就是幾個EXCEL進階公式,希望對於Excel苦手們可以有幫助囉!學起來,一起晉升excel達人囉!


因為自己也曾經痛過,所以創建這個網站,把自己學到的技巧交給其他其他和我們一樣想脫離社畜生活的人們。親愛的酒客們,點杯最適合今天心情的調酒,然後聽HOPPY OFFICE的酒保們給你最終肯的建議吧!

【EXCEL放題】數據分析必學!5類超實用公式大公開(上)

今天我們要跟大家介紹更多實用的Excel公式囉!

◆邏輯公式:IF,AND,OR
◆數字運算:ROUND,ROUNDUP,ROUNDDOWN

邏輯公式

1. 如果IF

=IF(指定的條件,符合條件的顯示內容,不符條件的顯示內容)

指定的條件設定可以是以下幾種:

(1)數值的關聯:大於、小於、等於某個特定的數字。如:K3>300,K3儲存格的數值必須大於300。

(2)文字的相關:等於、包含那些文字。如: K3=”開心職場” ,則K3儲存格內容必須符合這個文字才行。

(3)格式的相關:儲存格內的文字及數字的排列模式符合條件。如:LEFT(K3,2)=A”,則K3儲存格從左數來第二個字元必須是「A」才是符合條件。

符合條件及不符條件要顯示的內容,可以是文字,也可以是公式。記得如果是文字時,文字前後都必須用「”」標記,EXCEL才會知道是文字唷!

下方範例中,若希望自動確認商店每天的銷售額是否達標,則設定公式如圖。這樣如果實際營業額>目標營業額時,達標欄位就會自動顯示「O」,否則顯示「X」。

2. 交集AND

=AND(條件1,條件2,…)

白話文就是「並且」,這個公式通常會與其他公式搭配使用,所有的條件都必須符合才算是「符合條件」。每個條件中間要使用「,」隔開。

範例中,若希望追蹤「每天是否達標」且「銷售超過5筆」,則設定公式如下。這樣當這兩個要件都符合時,才會顯示自動顯示「O」,否則顯示「X」。

3. 聯集OR

=OR(條件1,條件2,…)

白話文是「或者」,這個公式同樣得搭配其他公式使用,只要符合設定條件的其中一項,就代表「符合條件」。

依照下方範例中設定公式,只要符合「達標」或「交易超過5筆」,就會顯示「O」,都不成立才會顯示「X」。

數字運算

1. 四捨五入 ROUND

=ROUND(要算的數字,算到哪一位)

「要取到哪一位」在輸入的時候要注意EXCEL規範的格式。如果是「正數」表示小數點以下幾位「負數」表示小數點以上幾位

下面範例中,我們將B2儲存格內數字分別以四捨五入取道百位、個位及小數點下2位。

2. 無條件進位 ROUNDUP

=ROUNDUP(要算的數字,算到哪一位)

下面範例中,將B4的數值用無條件進位,取到百位、個位、小數點下2位。

3. 無條件捨去ROUNDDOWN

=ROUNDDOWN(要算的數字,算到哪一位)

範例中,我們把B5以無條件捨去法取到百位、個位和小數點下2位。

下回,開心職場還要教你三種超實用的EXCEL公式,千萬別錯過!


因為自己也曾經痛過,所以創建這個網站,把自己學到的技巧交給其他其他和我們一樣想脫離社畜生活的人們。親愛的酒客們,點杯最適合今天心情的調酒,然後聽HOPPY OFFICE的酒保們給你最終肯的建議吧!

【EXCEL放題】數據圖像化!教你製作5種超Fancy區域分布圖

當需要呈現不同地區的現況時,如果單純使用文字表格呈現,當數據量大的情況,很容易看到昏頭,而且也較難看出數據與地緣之間的關係。這時要是有地圖可以一目瞭然的呈現,該有多好?

其實想要有效率地做出明確的區域分佈圖,不需要什麼高超的技巧和專業的程式,用EXCEL就能做到囉!

今天開心職場就要教大家用Excel製作以下5種『區域分布圖』

◆分級地圖
◆各區數量圖
◆直條圖(3D)
◆泡泡圖(3D)
◆分布地圖

各種圖表適合使用的時機

1. 分級地圖

想要將各地區做不同的分級時可以使用,例如要開發新業務時,可以將各地區依顏色來分級,明顯的讓主管知道哪些區域比較適合優先開發。

2. 各區數量圖

將各地區已數量多寡來分級,用顏色深淺來突顯各地的營業額、交易筆數、平均客單價…等資訊。用地圖的方式表現,更能夠看出哪些區域的表現較理想,幫助擬定未來的業務拓展計劃。

3. 3D直條地圖

這種圖表的視覺表現其實沒有其他地圖式圖表理想,但卻能明顯的表達出各地在不同時間區間的差異。所以常用於1–5年的每年業績、交易筆數…等的數據表現。

4. 3D泡泡圖

作用類似於第2種圖表,通常用以表現各區的商店數量及各地業績總額時使用。延續上一個功能,如果要顯示的是各地單年度的營業額或是商店數量的多寡,我們推薦大家使用「3D泡泡圖」

5. 分布地圖

針對顧客住址或商店位置分析可以使用這種圖表,能夠明確地看到所有的落點。


圖表怎麼做?

分級地圖

將地區名稱、分類都輸入好後,全部圈選起來,然後從「插入」中點選「地圖」的「區域分布圖」。就可以看到EXCEL自動依照你的分類將國家上顏色囉!


各區數量圖

將資料都輸入好之後全選,然後點選「插入」裡面的「地圖」。
點選「區域分布圖」之後就會出現圖表啦!


3D直條地圖

如果我們要做的是比較各地多個年度間的營業額的圖表或是比較兩組不同的數據,非常適合使用長條圖的方式來表現。

先把資料都輸入完成後,全部框起來然後點選「插入」裡面的「3D地圖」中的「開啟3D地圖」。

出現這個地圖後,從右側的「圖層窗格」裡面的「高度」選擇你想要的第一個數據(這邊我們設定是人口)。
接著在同樣的位置,點選「新增欄位」,接著點選第二個數據(這邊我們設定的是女性人口)。

預設是累加的結果,我們可以從右側的「圖層窗格」裡面變更為「群組直條圖」。
選擇完畢後,就可看到立體的直條圖顯示在你的地圖上啦!


3D泡泡圖

延續上一個功能,如果要顯示的是各地單年度的營業額或是商店數量的多寡,推薦大家使用「3D泡泡圖」

同3D直條圖作法,只要在右側圖層窗格的部分,改選「泡泡圖」就完成了。


如果想要對圖的顏色做修改,從「圖層窗格」裡面的「圖層選項」中可以自由的選擇想要的樣式喔!

點選「擷取場景」就會幫你把做好的這張圖擷取下來,到你想要使用的地方按「貼上」就會出現了。


分布地圖

這個功能比較特別,要使用到實際的地址,才有辦法製作出來喔!

輸入好地址資料後全選,然後點選「插入」中的「3D地圖」裡的「開啟3D地圖」。

在「位置」的部分新增「地址」,其他部分都不需要另外修改。這時就可以在地圖上看到明顯的藍點,那就是資料的位置。

以上提供給大家5種不同的圖表類型建議。在製作時記得要選擇最適合的圖表,才能讓你一邊耍帥、一邊有效提升報告的成效喔!


因為自己也曾經痛過,所以創建這個網站,把自己學到的技巧交給其他其他和我們一樣想脫離社畜生活的人們。親愛的酒客們,點杯最適合今天心情的調酒,然後聽HOPPY OFFICE的酒保們給你最終肯的建議吧!

【EXCEL放題】報表常用3大公式!學起來工作效率秒提升!

前幾篇的【EXCEL放題】PM、企劃必學!兩方法快速做出甘特圖【EXCEL放題】數據圖像化!教你製作5種超Fancy區域分布圖是不是很好用呢?除了這些功能欄的用法之外,其實Excel最好用的還是公式啦! 這次就匯總三大項常用的公式:加總、計數、平均。

在三個基本公式下,其實可以細分出「有條件的篩選」、「有多項條件的篩選」,只要學起來,未來在製作月報、週報等需要處理大量數據的你來說,都能夠有效提升工作效率哦!

◆加總系列:SUM, SUMIF, SUMIFS
◆計數系列:COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS
◆平均系列:AVERAGE, AVERAGEIF, AVERAGEIFS

加總系列

1. 直接加總

=SUM(開始範圍:結束範圍,開始範圍:結束範圍)

意即:自動將( )內的數值加總。如果要表示某個範圍區,可以用「:」來表示起始和結束的儲存格;若要增加其他範圍,只要再用「,」就可以隔開不同的範圍區間了。

快速鍵:按著「Ctrl」鍵用滑鼠直接點選範圍,就會自動出現你所選的範圍囉!

計算全部的銷售總額

2. 單一條件加總

=SUMIF(篩選開始範圍:篩選結束範圍,篩選條件,加總開始範圍:加總結束範圍)

意即:符合條件的數據才進行加總。前面先選取要篩選的儲存格,並將篩選條件以「,」做區隔,後面再選取需要加總的範圍。

篩選條件不只可以是儲存格,也可以輸入文字,但文字前後請用「 " 」標示,如圖示內公式的:「F3」可換為「 " A " 」則表示篩選條件為文字A。

計算B店的銷售總額

3. 多條件加總

=SUMIFS(加總開始範圍:加總結束範圍,篩選開始範圍1:篩選結束範圍1,篩選條件1,篩選開始範圍2:篩選結束範圍2,篩選條件2)

意即:符合所有條件的項目的數據才進行加總。

與單一條件不同的是,要將「加總範圍」放在公式最前面,後面再輸入想要篩選的條件。可以使用無數個條件,但要記得每個條件的範圍和篩選規則後都務必記得用「,」隔開。

計算B店假日的銷售總額

計數系列

1. 直接計數

=COUNT(開始範圍:結束範圍,開始範圍:結束範圍)

意即:選取的範圍中,有數字的儲存格數量有多少。

計算有消費金額的儲存格數量

2. 計算有效的數量

=COUNTA(開始範圍:結束範圍,開始範圍:結束範圍)

意即:計算範圍內「非空白」儲存個的數量,白話說就是只要有輸入內容都會被計算。同樣可以輸入多個範圍,只要用「,」隔開範圍即可。

計算未有消費金額的儲存格數量

3. 計算無效的數量

=COUNTBLANK(開始範圍:結束範圍,開始範圍:結束範圍)

意即:計算範圍內「空白」儲存格的數量。

和上一個方式相同,不過計算規則改成僅計無內容儲存格的數量。同樣可以輸入多個範圍,中間用「,」隔開。

計算有消費金額的儲存格數量

4. 單一條件計數

=COUNTIF(計數開始範圍:計數結束範圍,計數條件)

意即:符合條件的項目的數據才進行計數。

計數條件可以直接選擇儲存格,或輸入文字,不過文字前後需使用「 " 」標示,如:「 " 女 " 」則表示篩選條件為文字「女」。

計算女性有多少人

5. 多條件計數

=COUNTIFS(計數開始範圍1:計數結束範圍1,計數條件1,計數開始範圍2:計數結束範圍2,計數條件2)

意即:符合所有條件的項目的數據才進行加總。

可以使用無數個條件,每個條件的範圍和篩選規則後都務必記得用「,」隔開。

計算女性消費金額高於或等於1500的有幾人

平均系列

1. 直接平均

=AVERAGE(開始範圍:結束範圍,開始範圍:結束範圍)

意即:將選取範圍中的數字加總後,除以選取的儲存格數量,算出平均數字。

計算每日每店平均銷售額

2. 單一條件平均數

=AVERAGEIF(篩選開始範圍:篩選結束範圍,篩選條件,計算開始範圍:計算結束範圍)

意即:符合條件的項目的數據才進行計算。篩選條件可以直接選擇儲存格,也可以輸入文字,但文字前後使用「 " 」標示,如:「 " A " 」則表示篩選條件為文字A。

計算B店的每日平均銷售額

3. 多條件平均

=AVERAGEIFS(計算開始範圍:計算結束範圍,篩選開始範圍1:篩選結束範圍1,篩選條件1,篩選開始範圍2:篩選結束範圍2,篩選條件2)

意即:符合所有條件的項目的數據才進行計算。可以使用無數個條件,每個條件的範圍和篩選規則後都務必記得用「,」隔開。

計算A店假日的平均每日銷售額

今天給大家最常使用也最方便的基礎公式,試著將這些公式運用到日常的分析工作上吧!


因為自己也曾經痛過,所以創建這個網站,把自己學到的技巧交給其他其他和我們一樣想脫離社畜生活的人們。親愛的酒客們,點杯最適合今天心情的調酒,然後聽HOPPY OFFICE的酒保們給你最終肯的建議吧!

【EXCEL放題】PM、企劃必學!兩方法快速做出甘特圖

今天我們要教大家使用EXCEL來做出一個甘特圖。

「什麼是甘特圖?」

甘特圖簡單的說就是顯示執行工作的時間軸圖表。

這次會提供大家兩個方法製作圖表:

◆條件式格式設定
◆插入圖表


條件格式設定

首先,打開Excel之後,把「項目」、「開始時間」和「結束時間」都填好,接著在最上方一列填入日期。接著點選第一個項目的結束日期右側的空白儲存格,然後從「常用」中的「條件式格式設定」裡點選「新增規則」。再來選擇「使用公式來決定要格式化哪些儲存格」,然後輸入公式「=AND(D$1>=$B2,D$1<=$C2)」,再來點選「格式」。

從跳出的視窗中選擇「填滿」然後選取自己想要的顏色之後按下「確定」。可以從預覽中看到符合條件的結果會是如何,如果覺得沒問題了,在按下「確定」。

同樣在這一格不要移動,接著點選「常用」裡的「複製格式」。在滑鼠游標出現油漆刷的樣子時,一次圈選在日期和項目範圍內的所有儲存格,將格式貼上。

這樣就會出現你想要的甘特圖囉!如果覺得不夠美觀,可以再依照自己的喜好來美化它。一次把所有的行圈選起來同步變窄。從「檢視」裡可以把「格線」取消,畫上自己想要的框線,這樣美美的干特圖就完成囉!


插入圖表

同樣先把「項目」、「開始日期」、「結束日期」都輸入好,但這次要多加一個「持續期間」。持續期間下方的儲存格內要輸入公式「=C14-B14+1」,這樣就會算出來需要的天數。

然後將開始日期和結束日期下方的項目都圈選起來之後,點選「插入」裡面的「平面橫條圖」。這時請注意要選疊加的那個唷!

接著點圖表中的橘色部分,案右鍵選擇「選取資料」,選擇「結束時間」之後按「編輯」。跳出來的視窗中,把數列值清空,改選「持續時間」下方的數字,完成之後記得按「確定」。

最後,點選圖表中的藍色橫條,從「格式」中的「圖案填滿」選取「無填滿」。

再來可以在座標軸的部分按右鍵,點選「座標軸格式」。在右側出現的座標軸選項中,可以自行設定最小、最大值以及間隔單位的大小。

你的甘特圖就完成啦!最後,依照自己的喜好調整圖表設計,上方的「圖表標題」也能自己更改名稱喔!

以上就是我們今天的使用Excel製作甘特圖分享,希望能幫助到大家。那我們下回見啦!


因為自己也曾經痛過,所以創建這個網站,把自己學到的技巧交給其他其他和我們一樣想脫離社畜生活的人們。親愛的酒客們,點杯最適合今天心情的調酒,然後聽HOPPY OFFICE的酒保們給你最終肯的建議吧!

【EXCEL放題】建立問卷必學技能:保護你的工作表!

你們是否也常遇到自己辛苦建立好的表格格式、美麗的版型、甚至是複雜的公式,在一瞬間被EXCEL白痴同事摧毀呢?或是有人總是不按照約定好的內容輸入,導致你要整理訊息的時候變得困難無比,還得自己一筆一筆修改?

今天開心職場要教大家兩個小技巧,讓你的EXCEL工作表不再隨便被人亂動啦!

◆建立選單
 -資料驗證
 -自動提示
◆保護工作表

建立選單

首先先在空白的地方輸入好你要限定的文字或數字內容。接著把要設定的欄位全部圈選起來,然後從「資料」裡面選取「資料驗證」再來設定的部分要選擇「清單」。最後,在「來源」的部分,把預設好的選項圈起來,再來按下確定。

這樣就完成了!當點選到指定的儲存格時,右方會出現向下的箭頭,點選之後就會跑出自訂好的選項囉!

你們可能會想問「性別」當然很好設定,那「電話」有辦法設定嗎?
這邊我們誠實的告訴大家,是可以的,只是超級無敵麻煩!
所以這邊教大家另一種比較省力的提醒方式:當使用者點選到電話的欄位時,可以自動跳出提示,提醒使用者應該以什麼格式輸入。

首先一樣把想要設定的欄位圈選起來,然後點選「資料驗證」。這次我們要從「輸入訊息」裡面設定,「標題」和「輸入訊息」的位置請輸入你想提示的內容,接著按下確認。

再來你就會看到,只要點選到設定的欄位,就會自動出現提示訊息啦!
加上此提示文字,就比較能降低大家不按照格式填寫的問題囉!

保護工作表

首先把基本資料和公式都全部輸入好。接著把「可以讓人編輯」的部分圈選起來,然後從「校閱」裡面點選「允許編輯範圍」。再來按下「新範圍」,出現的視窗裡,可以自己設定標題名稱,以利自己確認使用,最後按下「確定」。

**注意**如果設定「範圍密碼」的話,想要修改儲存內容的人就必須要輸入你設定的密碼才能進行修改;如果要讓所有人都可以在圈選的儲存格範圍內輸入文字,「範圍密碼」就留白不要輸入任何訊息。

再來要按一下「保護工作表」,然後設定自己的密碼,如果之後想要編輯剛才沒有選取到的欄位,都得輸入密碼才能編輯。下面的選單是你想要保護的內容,如果任何格式都不想被破壞,那就只留預設的最上面兩個勾勾,其他都不要打勾。接著一樣記得按下「確定」。

EXCEL這時候會要求你再輸入一次相同的密碼,輸入完請按「確定」。
完成之後,只有指定的部分可以編輯,所有人都能自由輸入文字或數字。
而點選到沒有開放編輯的儲存格,並且想要修改時,就會自動跳出系統提示無法修改。

如果想要修改其他內容時,再到「校閱」裡面按下「取消保護工作表」,然後輸入當初的密碼就行囉!

希望今天教給大家的小技巧,能夠有效的幫助大家阻擋職場上的EXCEL白癡。
那我們就下次見啦!


因為自己也曾經痛過,所以創建這個網站,把自己學到的技巧交給其他其他和我們一樣想脫離社畜生活的人們。親愛的酒客們,點杯最適合今天心情的調酒,然後聽HOPPY OFFICE的酒保們給你最終肯的建議吧!

【EXCEL放題】週報月報救星! 一鍵自動更新圖表

每週做報表都要畫圖表畫到手軟?其實設定成自動更新就好了啦~

前面學過了 【EXCEL放題】 輕鬆用excel做CRM-(上)查詢系統VLOOKUP的設定和 【EXCEL放題】 輕鬆用excel做CRM–把不需要的數字通通藏起IF,今天我們要教大家兩種自動更新數據圖表的方法,會用到的兩種應用分別是:

1.樞紐分析圖
2.公式自動帶入

樞紐分析圖

平常雖然每週都會記錄數據,但要怎樣快速分析每週不同的數據呢?這時就能用「樞紐分析圖」的方式,快速看到各週的數據分析哦!

步驟一:全選現有資料,選取「插入」的「樞紐分析圖」,接著再點一次「樞紐分析圖」選擇好自己要放置圖表的位置後,點選「確定」。

步驟二:再來看右方的「樞紐分析表欄位」,將要化為分析數據圖的部分放入「值」(以此例來說是各地分店),並把時間的部分放入「篩選」(也就是週次的部分)。

接著就能直接從圖表中選取想要看的週次啦!

其他的小撇步:如果想更換圖表類型,可以從上方選單的「設計」中,選取「變更圖表類型」

不過要注意的是,和樞紐分析表一樣,只要資料有變動,都務必要在上方選單中的「樞紐分析圖分析」裡面點選「重新整理」,這樣圖表的數據才會更新喔!


公式自動代入

接下來用之前學過的查詢公式:VLOOKUP 或 HLOOKUP來做自動圖表更新:

資料填好之後,在下方空白處拉個表格,記得保留一格手動填寫的空位。

像圖中B10儲存格,可以保留成「可手動修改」的欄位,這樣不管是要生成哪一週的數據,或是任意直欄的數據,只要在紅色那格修改文字就行了~

接著在B10儲存格下方,使用VLOOKUP公式=VLOOKUP($B$10,$A$1:$D$7,2,FALSE)來查詢固定週次的業績。

這麼一來的好處是,只要在B10這格輸入3/16–3/22, 底下的數據就會自動顯示第二週(也就是第3列)的數據,再來就只需要將這個小表格生成圖表即可~

⚠️這邊要特別留意的是⚠️有些儲存格的條件要先手動設定完成,例如B12的格數,要抓的數據第三欄,因此公式的第三個條件內容要改成3:=VLOOKUP($B$10,$A$1:$D$7,3,FALSE)

想要有幾格「固定數據項目」直欄,就以此類推即可。

到這邊準備工作就完成啦!接下來的步驟很簡單,把你作好的表格圈選起來,接著從上方選單選取「插入」,並從圖表中選擇自己想要的圖表類型。

這樣就完成囉!接著隨時看自己想要查詢哪個時間點的數據,只要在指定的儲存格內(這邊我們設定的是B10)輸入想要的時間,圖表就會自己變動啦!

今天我們的教學就到這邊,大家如果有想要學習什麼樣的EXCEL小技巧,歡迎以各種方式告訴開心職場唷!我們下次再見!掰掰!


因為自己也曾經痛過,所以創建這個網站,把自己學到的技巧交給其他其他和我們一樣想脫離社畜生活的人們。親愛的酒客們,點杯最適合今天心情的調酒,然後聽HOPPY OFFICE的酒保們給你最終肯的建議吧!

【EXCEL放題】秒懂Excel公式 必學常用符號全攻略

開心職場接獲讀者反應說搞不懂那些公式裡面的符號代表什麼意思,好的,那今天就來告訴大家這些符號到底在幹嘛吧!


冒號『:』

區域引用:冒號表示「選取兩個儲存格間的所有範圍」,通常運用在SUM、VLOOKUP等公式中。如下圖中假日累計數量公式=SUM(C4:D8),就是加總C4到D8間所有儲存格的數字。


逗號『,』

逗號使用有兩個含意,第一是用來「聯集」,第二是用作「區隔兩個條件」

作為聯集:同時選取多個範圍時,可以用逗號將每個範圍隔開,如下圖中的平日累計數量公式=SUM(B4:B8,E4:E8),代表加總 B4 到 B8 還有 E4 到 E8 間的所有數值。

作為區隔條件:則是在有多個條件設定的公式時使用,如VLOOKUP、SUMIFS等。

如下圖中計算有多少時段超過5筆交易,使用的公式=COUNTIF(B4:E8,">5″),其中逗號被用來區隔「計數範圍」以及「計數條件」這兩個條件。


雙引號『” ”』

表示運算子:雙引號的第一個功能是表示「運算方式」,如下圖中公式=COUNTIF(B4:E8,”>5″)就是計算範圍內數值大於5的數量。

◆表示文字:第二個功能則是在公式中表示文字,如下圖中公式=SUM(B4:B8)&”筆",表示在B4到B8加總結果後方顯示「筆」這個文字。


連結符號『&』

通常在想合併呈現多個儲存格文字或數值時使用,如下圖公式=SUM(B4:B8)&”筆”,就是呈現加總數值和「筆」這個字。


錢的符號『 $ 』

Excel中設定好公式後若直接複製到其他儲存格,公式內的儲存格會依照移動的欄、列差異自行調整(相對位置的概念)。所以如果其中有個項目不想要移動,就要用「$」用來鎖定欄位,使用的位置是在欄、列的字元前方。如下圖佔比公式=B2/$B$7,將公式下拉時,分母使用的B7儲存格就會被鎖定不會移動。


驚嘆號『 ! 』分頁名稱

當資料內需要連結到不同的工作表時,直接輸入工作表名稱然後加上「!」。如下圖中公式=開心職場!A1,就會顯示「開心職場」這個工作表內的A1數值。


中括號『 [ ] 』檔案名稱

中括號的使用方始與「!」類似,只是中括號用來表示連結到另一個檔案。如下圖中公式=[開心職場.xlsx]工作表1!A1,就會顯示「開心職場」這個Excel檔案裡的「工作表1」頁面中的A1儲存格數值。


星號『 * 』

表示任意的文字或數字,通常在模糊查找時使用。例如下圖計算包含「五」的儲存格,公式=COUNTIF(A1:E14,”*”&”五”),就會從A1到E14儲存格中查找符合條件的儲存格數量,而這個條件為儲存格內數值不論開頭為何,結尾要是「五」。所以被採計的有「週五」(B3)和「有多少時段超過五」(A14),共2格。


問號『 ? 』

使用的方式和星號相同,只是問號代表的是單一未知字元。如下圖計算包含「五」的儲存格數量=COUNTIF(A1:E14,”?”&”五”),如果範圍內儲存格的值在「五」的前面有超過一個字元,就不會被採計。依此公式,只採計「週五」(B3)這一格。

以上就是我們今天的Excel教學啦!希望能夠幫助大家更了解公式到底在寫什麼,讓你不再霧霎霎。


因為自己也曾經痛過,所以創建這個網站,把自己學到的技巧交給其他其他和我們一樣想脫離社畜生活的人們。親愛的酒客們,點杯最適合今天心情的調酒,然後聽HOPPY OFFICE的酒保們給你最終肯的建議吧!

【EXCEL放題】 輕鬆用excel盤點 – 把不需要的數字通通藏起來 IF

上一篇我們教了基本的查詢功能【EXCEL放題】 輕鬆用excel做CRM-查詢系統VLOOKUP的設定,這次我們以倉儲管理做範例,來看看如何讓不需要的資料直接隱藏起來!

值=0 就不顯示

在我們進行盤點的時候,要看出差異非常簡單,直接用減法公式就可以了(如下圖)。但這樣每行都會顯示數字,反而不容易看清楚哪些項目有差異。

如果想要輕易地看出差異,有三種作法:

1. 使用格式化條件設定

圈選『差異』的欄位,點選『設定格式化的條件』,接著點選『新增規則』。

選取『只格式化包含下列的儲存格』,並於下方設定『儲存格值』『等於』『0』,接著點選『格式』。於『字型』的部分,將色彩調成和底色相同的顏色後按確定。

接下來就會看到0都自動消失啦!

2. 使用篩選工具

圈選最上一列之後點選『排序與篩選』中的『篩選』

然後點選『差異』右方的小箭頭,將下方「0」取消勾選,再按確定。

就只會顯示有差異的部分囉!

3. 利用公式讓0差異的不顯示

我們可以利用IF公式,讓差異為0的值自動不顯示

IF公式說明:
=IF(條件,符合條件顯示的值,不符合條件顯示的值)

如下圖,在D2欄位輸入=IF(C2-B2=0,””,C2-B2),意思就是如果C2-B2=0,則顯示””中間的值(也就是沒有值=不顯示),如果C2-B2不為0,則顯示C2-B2

這樣一來,沒有差異的部分就不會再出現囉!

這樣子做下來,看表格是否就更容易了呢?有機會的話快點試試看吧!

延伸閱讀: 【Excel 放題】超實用4大隱藏版功能,輕鬆戰勝試算表
延伸閱讀: 【EXCEL放題】 輕鬆用excel做CRM-查詢系統VLOOKUP的設定


因為自己也曾經痛過,所以創建這個網站,把自己學到的技巧交給其他其他和我們一樣想脫離社畜生活的人們。親愛的酒客們,點杯最適合今天心情的調酒,然後聽HOPPY OFFICE的酒保們給你最終肯的建議吧!

【EXCEL放題】 輕鬆用excel做CRM-查詢系統VLOOKUP的設定

經過上次【Excel 放題】超實用4大隱藏版功能,輕鬆戰勝試算表的簡單教學,是不是終於覺得Excel有點好用了呢?其實除了那些基本的功能之外,只要用得妥當甚至能把Excel當CRM來用哦!快來看看怎麼做吧

查詢系統

查詢系統範例:於左側顧客姓名欄位(A3)輸入顧客姓名,即可自動找出顧客性別、顧客等級以及連絡電話。

要製作這樣的表格,我們需要的是查詢公式VLOOKUP(垂直查詢)或HLOOKUP(水平查詢)。

這兩個公式,可以查詢所輸入的值在表單中右側或下方相對應的文字及數字內容。

下圖中,左側橘色的部分為要建立的表格,而右側綠色區塊為我們使用的查詢清單。建立完成後,只需要輸入顧客姓名,系統會自動顯示顧客性別、等級和電話。

我們要做的事情其實很簡單,在B3到D3欄位分別輸入VLOOKUP公式就可以囉!

VLOOKUP公式說明:
=VLOOKUP(查詢的內容,查詢的範圍,顯示查詢範圍內由左至右的哪一行,是否需要完全符合)

以上面的範例來說明,B3儲存格內輸入VLOOKUP(A3,F:I,2,FALSE),意思就是從F到I的範圍內,查詢完全符合A3這格的內容,顯示F到I範圍中相對應且由左側開始數來第2行的字元。(FALSE=需要完全相符查詢內容)。

在使用這個公式時,要注意的是查詢的值必須要在查詢範圍的最左側,如果是在查詢範圍的第2行是查不到的唷!

跟著一步步做下來之後,是不是瞬間懂了什麼?對沒錯,其實Excel就是這麼方便好用,只是以前老師都沒教過~
那麼今天的Excel教學小教室就到這邊,我們下次再繼續教你怎麼只篩出符合條件的資料!


因為自己也曾經痛過,所以創建這個網站,把自己學到的技巧交給其他其他和我們一樣想脫離社畜生活的人們。親愛的酒客們,點杯最適合今天心情的調酒,然後聽HOPPY OFFICE的酒保們給你最終肯的建議吧!