1 2 3 4 5

廣告

網友您好,若是您覺得我寫的工具或文章對您有幫助,
而您或者您的朋友有在博客來購物,可以透過底下連結

如何在博客來購物贊助網站或是點選計數器下方的博客來圖示來讓網站可以永續經營。
顯示具有 excel2013 標籤的文章。 顯示所有文章
顯示具有 excel2013 標籤的文章。 顯示所有文章

2018年4月3日 星期二

【Excel教學】如何計算產品名稱不重覆的數量

之前寫到可以用countif及sumproduct來計算不重覆的次數,但是這樣的解法中間不能有空白產品編號,若有會造成除零錯誤,整個結果會變成#DIV/0!,這時候就要使用 if跟sum來解決,if將有空白的資料排除,sum再將所有的結果加總,應該是多筆資料加總,就要使用陣列公式,再輸入完畢時,按下【Ctrl】+【Shift】+【Enter】送出公式,就可以顯示出正確答案。
範例檔案下載: http://download.bestdaylong.com/f561.htm

1.若產品中間沒有空白,可以用=1/COUNTIF(A$2:A$7,A2)

2.計算不重覆數量=SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))

3.若產品有空白,則會出現#DIV/0!錯誤

4.利用=IF(COUNTIF(H$2:H$7,H2)>0,1/COUNTIF(H$2:H$7,H2),0)
來將除零的錯誤排除

5.輸入=SUM(IF(COUNTIF(H2:H7,H2:H7)>0,1/COUNTIF(H2:H7,H2:H7),0))
送出時,不是按Enter,而是按【Ctrl】+【Shift】+【Enter】
公式前後的大括號是自動產生

影音教學:

2018年1月9日 星期二

【Excel教學】利用函數if及countif將重覆資料做勾選記號

這是粉絲團的網友問的問題,因為他要處理中文字的讀音,資料有9千多筆,需要將相同的字找出來,並將有重覆的字加上流水號,這時候只要在配合函數if就可以快速的解決問題。

1.網友提問的二個問題

2.利用countif將>1的次數顯示V,若沒有則顯示空白

3.再用IF判斷若有重覆再排編號,否則空白顯示
範例檔案下載: http://download.bestdaylong.com/f508.htm

2018年1月8日 星期一

【Excel教學】如何利用函數countif將重覆資料加上流水號

在前一篇利用countif找出重覆值,若我們想把它變成惟一值,就是加上它出現的第幾次,例如第1次出現是1,第2次出現是2以此類推,一樣可以用countif來達成,只是在範圍鎖定時,只會鎖定第1個範圍,最後再用字串合併成英文單字加出現次數。

1.將篩選的資料全選/【複製】到新的工作表貼上

2.利用countif的相對與絶對位置結果,並往下複製

3.檢查第2個公式,發現第2個B2變成B3了

4.再利用&將字串合併
範例檔案: http://download.bestdaylong.com/f507.htm

2017年11月4日 星期六

為什麼在Excel的保護工作表設定可以插入列及刪除列會變成反白沒作用

這是網友來信問我的問題,我跟他說在保護時勾選勾選【插入列】及【刪除列】就可以在保護的工作表下插入及刪除列,但是他設定後還是不行,我再看他跟我的設定差在那裡,發現預設會勾選【選取鎖定的儲存格】及【選取未鎖定的儲存格】,他把【選取鎖定的儲存格】的勾拿掉,所以【插入列】及【刪除列】就因為沒辦法選取到鎖定的儲存格,造成變成灰色表示沒有作用。

1.沒有勾選【選取鎖定的儲存格】

2.選取可看見的儲存格,點選滑鼠右鍵發現【插入】【刪除】是灰色的

3.有勾選【選取鎖定的儲存格】

4.選取整列時,會連後面鎖定的一起選,按滑鼠右鍵,【插入】及【刪除】就可以使用

2017年11月1日 星期三

如何利用vlookup查詢勞健保勞退負擔金額

同事問我,可以用vlookup查詢勞健保勞退負擔金額嗎?我說可以,但是資料要先處理過就可以,我們知道vlookup的最後一個屬性,若沒有設定或是設定True,就會找出最接要查詢的值小的資料,但是資料要先由小到大排序,但是勞健保勞退負擔的規則是要找到最值的資料,只要將要查詢的級數往後加一層,再將第1列的值加1就可以達到目的。

1.原本要查詢的表格

2.中間級距加入一個0並往後貼上,輔助欄位1則是原本的級距

3.輔助欄位2從第2列開始將B欄的級距值加1

4.這時候用vlookup去查表,就會是我們要的結果

5.說明中有說是找出最接近的值,但是用例子去實作,
會發現1.4的最接近值是1.5,但是回傳會是1.1
範例檔案下載: http://download.bestdaylong.com/f482.htm

2017年10月27日 星期五

如何開啟excel的csv檔案時,數字前面的零不會不見

當您把excel檔案儲存成csv的檔案格式時,若是資料中的數字前面有0時,下次用excel開啟csv時,前面的0會消失,這時候您可以將副檔名從.csv改成.txt,再用excel開啟,因為excel看不懂txt的檔案,會自動啟用文字剖析,這時候就可以將有0的欄位,把它設成文字,在excel就會看到有0的數字。

1.csv檔案的電話有0,用excel開啟後0會自動消失

2.先把副檔名從csv改成txt,用excel開啟時,檔案類型選【所有檔案】才會看到.txt的檔案

3.出現匯入精靈/點選【下一步】

4.勾選【逗號】/【下一步】

5.先點選手機,再選【文字】/【完成】

6.手機的0不會消失了

2017年7月8日 星期六

在excel中如何使用陣列公式顯示不重覆資料


在excel中要顯示不重覆資料,以前都是選取,再按excel的去除重覆功能,若是用公式,要如何做到類似的功能,可以使用陣列公式,再利用擴展公式,從原本的資料中找出還沒在清單中的資料,所以會用MATCH(0,COUNTIF(C$1:C1,$A$2A$8),找出還沒有顯示的資料,第一個陣列公式輸入完,只要往下複製就會自動,出現#N/A,就表示都顯示出來。

1.輸入公式黃色的部分有用到擴展公式,
輸入完不能直接按enter
要按下Ctrl+Shift+Enter,就會自動補上前後的大括號

2.將公式往下複製剩下的姓名就會出現
參考資料: https://exceljet.net/formula/extract-unique-items-from-a-list
檔案下載: http://download.bestdaylong.com/f444.htm

2017年7月4日 星期二

在excel如何使用sumifs做OR條件的加總

這是網友問的問題,他使用sumifs做多條件的加總,因為sumifs的條件是用and,所以要使用or條件,就必須一個sumifs加上另一個sumifs,若懂陣列公式,可以使用sum+sumifs來做到這件事,因為sumifs是excel2007(含)以後才提供的函數,所以舊版本的就可以用sumproduct來解決,用加號來表示OR條件,乘1是要把邏輯值(真變成1,假變成0))。

1.原本的公式

2.使用列陣公式的結果

3.用sumproduct也是可以使到一樣的效果

2017年5月2日 星期二

Excel如何利用排序輔助欄位篩選找出報價最低廠商

論壇有網友問如何找出報價最低廠商,看了他的excel檔案後,想到最簡單的方式是依品名及價格遞增排序,再利用輔助欄位找出相同品名價格的排序位罝,最後篩選找出是1,就是所有品項最低價格。

1.先將資料依品名及價格遞增排序

2.利用輔助欄位將相同資料依序填入流水號

3.選【資料】/【篩選】

4.選取1【確定】

5.全選按滑鼠右鍵複製

6.到儲存格G1貼上

7.因為還在篩選,只會看到第前幾筆,將篩選取消

8.就可以看到完整內容

9.相同報價可以利用countifs找出有幾筆
發問連結: http://discuz.bestdaylong.com/thread-37708-1-1.html

2017年1月24日 星期二

Excel如何利用公式做全校及班級排名

網友來信問到【彰化一整天先生請問您:我每次考試都必須將每個班級的學生,依照總分、國文、數學、英文、歷史、生物(這些成績由成績系統會產出來)排序出學生的班排名,然後再排出校排名,因為我們學校有30個班,我每次都需校重複這動作30次,請問您是否可以教我EXCEL哪一個公式或動作,是可以幫忙我解決這煩人的手續的嗎?謝謝您喔!】,校排名可以使用rank函數就可以解決,班排名可以使用sumproduct的陣列用法,先找到符合班級的資料,乘於1是將邏輯值true,false變成1,0,利用0乘於任何數都是0,將大於等於自己分數的1加總就自己在班上的排名。

1.校排名用rank來解決

2.班級排名用sumproduct
範例檔案下載: http://download.bestdaylong.com/f357.htm
youtube解說版: https://www.youtube.com/watch?v=LisXvTudCyA&t=81s

2016年8月25日 星期四

在Excel利用小計與篩選功能來達到不用寫vba插入空白列

我有寫一個http://download.bestdaylong.com/f218.htm(依欄位分類自動補空白列),若是不用VBA來解決,是否有其它解決,所以就想到利用小計時,會在小計的項目下加入一列【合計】,利用篩選來選取沒有小計的欄位就是空白,再清除選取內容,把篩選及小計取消就達到目的。

1.選擇【小計】

2.依您要插入空白列的條件設定小計

3.再選【篩選】

4.選取【空格】

5.將結果選取,點選滑鼠右鍵/【清除內容】

6.取消【篩選】

7.將結果複製到您要儲存格
範例檔案下載: http://download.bestdaylong.com/f294.htm

贊助

彰化一整天粉絲團

About Me

我的相片

很多人問我為什麼叫"一整天",其實這是有原故的.有一天看一本書.就是說如何介紹自己.裡面說到如何讓別人對你的名字有印象.就要讓別人跟你的名字或是身體的一些特徵有關.比如很胖.就會被別人取小胖或是胖子.或是跟名字有關的諧音,那天看布袋戲中有一個叫”一頁書”的角色.所以我就想我的名字.可以想出什麼字出來.結果我把我的名字"明和",拆成日月和,日月和在一起,就代表白天晚上都在一起.就取名為"一整天",而這個"一整天"跟我的名字有關也方便別人記憶,後來發現"一整天"這個名詞太常被用,所以就在"一整天"前面加上"彰化"變成"彰化一整天",為什麼要加彰化.因為我老家在彰化,所以以後別人要找我的部落格的話.我都會跟他們說,請到yahoo或是google打"彰化一整天"第一筆出現的就是我的Blog. PS:現在只要輸入"一整天"可以找到.

Google+ Followers