廣告

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

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

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

2016年8月20日 星期六

在excel中利用函數轉換班級簡稱轉全名

同事說要將班級簡稱由[企業管理系五乙]轉換成[企業管理系五年乙班],問我看有沒有辦法一次轉換,因為年級有一到六年級,比較麻煩,最後找到年級前的[系]是在家都有的,就利用它來取出到年級前的字串,最後加上最後一個班別,再補上最後一個[班]。

1.先利用函數find找出【系】的位置

2.方法1用函數left跟right

3.方法2用函數mid

範例檔案下載: http://download.bestdaylong.com/f290.htm

2016年3月26日 星期六

在Excel利用vlookup跟choose做由名次查詢學號

之前單位的一個老師問我一個問題,他說看到一個題目有規定只能用vlookup跟choose做查表,但是大部分的人都知道用vlookup查表,要被查詢的值一定要是第一欄,之前像這種問題,我都會用match跟index來做,但是這次規定只能用vlookup跟choose,所以我發現choose可以用陣列的方式來將要查表的順序改變,這樣用vlookup查詢欄位,就可以不用在第1欄,還有之前用輔助欄位的查詢也都可以改成這樣寫法,只要在陣列中用&將文字相加就可以。

1.因為要複製公式,先將名次用格式化成第X名

2.利用vlookup跟choose由名次查詢出學號
檔案下載: http://download.bestdaylong.com/f226.htm

2016年3月9日 星期三

Excel利用函數counta計算有使用的儲存格數量

這是網友在粉絲團中提出的第二個問題,他要計數使用的字數,但是每個儲存格都是一個字,這時候可以利用函數counta,再複製貼上公式,就可以達到所要的功能。

1.要儲存格E欄的結果

2.利用函數counta,再複製貼上公式
範例檔案: http://download.bestdaylong.com/f217.htm

2015年11月9日 星期一

Excel函數sumifs使用時機

當您有多條件的欄位加總時,就可以使用sumifs(Excel 2007以後才有的指令)來逹成,通常要使用時的前置動作,就是將原資料複製一份,並去除重覆,最後再使用這個指令,底下用網友提問的問題做解說。
問題連結:http://discuz.bestdaylong.com/thread-36799-1-1.html

1.將沒有要加總的欄位選取,點選滑鼠右鍵【複製】

2.到Q欄,點選滑鼠右鍵選【123】

3.再選【資料】/【移除重覆】

4.勾【我的資料有標題】/【確定】

5.出現移除439個重覆值,點選【確定】

6.在儲存格T2輸入=SUMIFS(D:D,A:A,Q2,B:B,R2,C:C,S2)

7.將滑鼠移到儲存格T2,滑鼠變成實心十字時,連續點選滑鼠左二下,就會向下複製

8.這是最後的結果

贊助

彰化一整天粉絲團

About Me

我的相片

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

Google+ Followers