1 2 3 4 5 6

廣告

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

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

2019年5月8日 星期三

為什麼Excel無法執行巨集

從網路上下載的excel VBA,第一次開啟都會出現【受保護的檢視 小心,來自於網路的檔案可能有病毒,除非您需要編輯,否則停留在[受保護的檢視]。請點選 【啟用編輯】 ,儲存後下次就不會再出現。第二個訊息是【安全性警告 已經停用巨集。】請點選【啟用內容】,若不小心按到右邊的X,請重新開啟檔案,又會再次出現,若沒有啟用,會出現【無法執行巨集xxxx。該巨集可能無法在此活頁簿中使用,或者己停用所有巨集。】


1.要按【啟用編輯】,只有第一下載執行會出現

2.點選【啟用內容】

3.沒有【啟用內容】就會出現這個訊息

4.若不小心按到最右邊的X,要再重新開啟才會出現【啟用內容】

2019年2月8日 星期五

【網友Excel問題解答】Excel如何排序複合條件的資料

這是網友詢問的問題,因為在excel排序中,若是文字數字會1個字元1個字元比對,會產生9比10大的現象出現,所以要用輔助欄位來解決,將複合欄位拆成兩欄數字,有的資料有~,有的沒有,函數會用到find、left、right、if、iserr、value、len,這時候可以用資料剖析來解決。



網友問題: https://www.facebook.com/f272586/posts/10157136436774461

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


2018年6月25日 星期一

【Excel教學】mround函數介紹取最接近的基數倍數

一般我們用round是10進位的四捨五入,若是不是十進位的,則可以使用mround函數來解決,若您的基數是用15,則7會顯示0,8則會顯示15。


1.mround的執行結果
範例檔案下載: http://download.bestdaylong.com/f596.htm

2018年5月31日 星期四

【Excel教學】如何用函數subtotal顯示篩選後的自動流水號

Excel教學,如何用函數subtotal顯示篩選後的自動流水號
1.用函數counta是不管有沒有顯示都計數
2.用函數subtotal是只有顯示的儲存格才會計數
3.利用下載上櫃股票每日收盤行情,計算有篩選的自動流水號


2018年5月8日 星期二

【教學】如何利用vlookup查詢重覆值

之前教學有寫過類似的文章,昨天網友有問同樣的問題,一樣是用範圍陣列公式加上countif的作法,底下是操作過程。
相關文章:
http://wordpress.bestdaylong.com/blog/archives/18898
【Excel教學】在Excel如何不增加輔助欄位做到vlookup可以查詢重覆值

1.網友想要的結果是可以依序出現重覆的內容

2.選取要顯示的範圍輸入
=VLOOKUP($E13&COLUMN($1:$4),CHOOSE({1,2},A$2:A$15&COUNTIF(INDIRECT("A$2:A"&ROW(A$2:A$15)),A$2:A$15),B$2:B$15),2,0)),"",VLOOKUP($E13&COLUMN($1:$4),CHOOSE({1,2},A$2:A$15&COUNTIF(INDIRECT("A$2:A"&ROW(A$2:A$15)),A$2:A$15),B$2:B$15),2,0)
接著按下[ctrl]+[Shift]+Enter陣列公式輸入方式

3.將公式往下複製即完成

4.若要解決#N/A問題,可以將公式改成
=IF(ISNA(VLOOKUP($E13&COLUMN($1:$4),CHOOSE({1,2},A$2:A$15&COUNTIF(INDIRECT("A$2:A"&ROW(A$2:A$15)),A$2:A$15),B$2:B$15),2,0)),"",VLOOKUP($E13&COLUMN($1:$4),CHOOSE({1,2},A$2:A$15&COUNTIF(INDIRECT("A$2:A"&ROW(A$2:A$15)),A$2:A$15),B$2:B$15),2,0))
按下[ctrl]+[Shift]+Enter陣列公式輸入方式
範例檔案下載: http://download.bestdaylong.com/f574.htm

2018年5月7日 星期一

【Excel教學】將Excel中各列資料重組為一欄資料

這是網友提問的問題,可以利用Offset、Row、Mod、int與絶對位置的應用,就可以輕鬆的做到。
  
1.先確定要轉換的欄數,這個例子是12
  
2.在另一個工作表A1輸入=OFFSET(工作表1!$A$1,INT(ROW(A1)-1)/12,MOD(ROW(A1)-1,12))
往下複製就會出現我們要的結果
範例檔案下載: http://download.bestdaylong.com/f573.htm


2018年5月2日 星期三

【Excel教學】如何不使用輔助欄位做到自動化的應收帳款

這個問題在5年多前有人問過,那時候的做法要先排序資料,使用輔助欄位,最近學了二個技巧,一個是vlookup不使用輔助欄位可以同時查詢2個以上的條件,另一個是利用範圍陣列公式,讓相同資料變成惟一,今天就用這個技巧,讓每次有新廠商時,只要複製工作表,修改廠商名稱就可以輕鬆的完成。

1.名細工作表不用輔助欄位及排序

2.每次複製新的客戶,只要在B4修改客戶名稱,其他資料會自動顯示

3.要先選取範圍再輸入鍵陣列公式,黃色是關鍵

4.若有新增欄位,只要修改黃色的部分,其它公式都相同
範例檔案下載:http://download.bestdaylong.com/f568.htm

2018年4月17日 星期二

【Excel教學】截取最後一個數字以後的字串

這是在麻辣家族討論版中看到的問題,其中有二個種解法,一個是使用負負得正再用isnumber來判斷,最後再用除零錯誤來排除不符合的條件,另一個是在前面加上負號,若是文字前面加上負號會變成#VALUE!錯誤,再利用某些公式會忽略錯誤的特性把要的值找出來。

1.利用=MID(A2,IFERROR(LOOKUP(1,0/ISNUMBER(--MID(A2,ROW($1:$255),1)),ROW($2:$256)),1),255)來解決,其中的255表示文字最多只能255個

2.改良後=MID(B2,IFERROR(LOOKUP(1,0/ISNUMBER(--MID(B2,ROW(INDIRECT("$1:$" & LEN(A2))),1)),ROW(INDIRECT("$2:$" & LEN(A2)+1))),1),這樣的用法沒有文字大小的限制

3.使用陣列公式=MID(A2,MATCH(1,-MID(0&A2,ROW($1:$255),1)),255),前面加0的就是最後一個數字的下一個開始取,您也可以用其它字元表示結果是一樣的,其中255代表文字最多只能255字

4.改良後使用陣列公式=MID(A2,MATCH(1,-MID(0&A2,ROW(INDIRECT("$1:$" & LEN(A2))),1)),LEN(A2))
這樣的用法文字不會長度的限制
範例檔案下載: http://download.bestdaylong.com/f564.htm

2018年4月11日 星期三

【Excel教學】如何讓excel輸入數字超過11位數時不顯示科學符號

在Excel中若是數字輸入字超過11位數時,儲存格就會自動變成1.23457E+11的表示方法,網友問說要如何讓它顯示原本的數字格式,只要將儲存格的顯示方式改成數字,再將原本顯示的2位小數,改成不顯示小數就可以。

1.若是前面加單引號可以顯示,但是變成文字,若有用函數sum做運算就會變0

2.在格式選【數值】

3.再按二次減少小數顯示

4.正常顯示出原本的數字

2018年3月31日 星期六

Excel教學】無排序相同資料只顯示第一筆或最後一筆

在整理相同資料時,有時候會只要顯示第一筆其它刪除,或是顯示最後一筆刪除,若是不用Excel VBA,可以用countif加相對及絕對位置來解決,最後只要將結果複製貼上值,排序後再將空白資料刪除就可以。
範例檔案下載: http://download.bestdaylong.com/f560.htm

1.在儲存格B2輸入
=IF(COUNTIF(A$2:A2,A2)=COUNTIF(A:A,A2),A2,"")
並將公式往下複製

2.在儲存格B2輸入
=IF(COUNTIF(A$2:A2,A2)=1,A2,"")
並將公式往下複製


2018年3月27日 星期二

【Excel教學】可複製公式加總範圍變動_使用 offset跟column

之前是使用函數Char來解決,但是若欄位超過26欄(A-Z)就會出錯,所以想了想用OFFSET來解決好像比較好,因為函數column回傳的是數字,offset的移動也是數字,這樣使用彈性就比較大。

1.儲存格H3輸入=SUM(OFFSET(H3,0,2-COLUMN(),1,COLUMN()-2))
範例檔案下載: http://download.bestdaylong.com/f551.htm

2018年3月26日 星期一

【ExcelVBA觀念】什麼時候程式碼要放在模組中

這是網友在論壇問的,他要開啟excel檔案後,每1分鐘自動存檔,他程式碼是放在ThisWorkBook,因為他用到Application.OnTime這個多久時間就自動執行某個副程式,所以每次一執行時,就會出現
【無法執行巨集xxx。該巨集可能無法在此活頁簿中使用,或者已停用所有巨集。】,這時候只要新增一個模組,再把程式碼從ThisWorkBook移到模組,程式就可以正常執行。
網友問題連結:http://discuz.bestdaylong.com/thread-38138-1-1.html
範例檔案下載: http://download.bestdaylong.com/f556.htm

1.原本程式放在ThisWorkBook底下

2.執行會出現錯誤

3.將部分程式移到Module1下

4.workbook_open還是放在ThisWorkbook下,這樣執行就不會出錯。

2018年3月22日 星期四

【Excel教學】用公式將相同資料合併成一筆

這是網友在論壇問的問題,要將條列式的資料變成表格的方式呈現,看了他的問題,會有多筆的問題,但是在Excel相同資料只會找到第一筆,所以就要先將相同的資料自動合併成一筆,後面再用vlookup來解決就可以。

1.網友想要將左邊的條列資料變成右邊的表格式

2.先依日期地點排序

3.利用if來將相同的資料合併

4.利用if產生相同資料的流水號

5.利用if將最後一筆設為1

6.再利用篩選找出相同資料最後一筆

7.因為有二個欄位要查詢,利用輔助欄位來做字串相加

8.最後使用vlookup查詢出表格內的資料
檔案下載: http://download.bestdaylong.com/f552.htm

2018年3月21日 星期三

【Excel教學】如何讓加總函數可以任意改變大小範圍複製

我們在處理員工薪水時,會在總表算出全部人的金額,若是要將每個人變成一個工作表,就要複製個人名細,再複製合計,可是在複製合計公式貼上時變成=SUM(#REF!),這時因為它找不到對應的相對位置,就會變成沒有參照#REF,這時候可以透過函數indirect將字串轉換成參照位置,再利用函數char產生加總到前一個欄位,函數column取得目前的欄數,因為是前一欄,所以就用63+欄位數來產生對應的欄位字母。

1.原本用sum加總的函數

2.複製到其它工作表時會出現=SUM(#REF!)

3.將公式取成=SUM(INDIRECT("B3:" & CHAR(63+COLUMN()) & "3"))

4.先複製個人明細

5.再複製公式就不會出現錯誤
範例檔案下載: http://download.bestdaylong.com/f550.htm
youtube教學

2018年3月8日 星期四

【Excel教學】如何設定萬元的金額顯示

Excel可以設定格式,例如千元加上一個逗號,若您要顯示萬元,可以設定顯示一個小數點,或是4個小數點,要顯示其他字元要前後加上雙引號,若只是一個位元,可以在前面加一個驚嘆號也可以,若是中文它會自動幫您加,若是逗點,就要手動加上。

1.選取要設定的範圍/【通用格式】/【其他數字格式】

2.選【自訂】/輸入【0!.0,萬】

3.若不用驚嘆號就要前後加上雙引號,例如【0"."0,"萬"】

4.另一個是顯示小數4位的用法【0!.0000"萬""元"】
範例檔案下載: http://download.bestdaylong.com/f537.htm

2018年3月5日 星期一

【Excel教學】使用sumproduct計算年累計YTD(Year To Date)

之前說到用sumproduct可以使用函數year及month,用等於會傳回true或false,經過四捨運算會將true變成1,false則會變0,0乘任何數還是0,利用這個特性,將不符合的結果變成0,因為運算元有優先順序,就用左右括號來改變要判斷的先後順序。

1.函數=SUMPRODUCT((B2:B18)*(MONTH(A2:A18)<=MONTH(D1))*(YEAR(A2:A18)=(YEAR(D1)-1)))

2.函數=SUMPRODUCT((B2:B18)*(MONTH(A2:A18)<=MONTH(D1))*(YEAR(A2:A18)=(YEAR(D1))))
檔案下載: http://download.bestdaylong.com/f532.htm

贊助

彰化一整天粉絲團