廣告

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

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

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

2018年3月3日 星期六

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

粉絲團有網友問到這個問題,可以使用sumif跟sumproduct來解決,sumif會比較簡單但是不能用year及 month函數,只可以用比較符號,所以可以選取某一年,再設定要比那一年的那一個日期小於等於,就可以做出YTD,但是sumproduct可以使用month與year,函數的使用會更靈活。

1.選取2017年資料再用sumif去計算

2.選取2018年資料再用sumif去計算
範例檔案下載: http://download.bestdaylong.com/f531.htm

2018年3月2日 星期五

【Excel教學】如何利用excel連結MySQL資料庫

想說都寫了MSSQL,就連mysql的連線也一起寫好了,因為要連mysql,由於不是微軟的產品,所以要另外安裝MySQL的ODBC Connect,才可以連線,若您的作業系統是windows xp,請安裝MySQL ODBC V5.2.7版,若安裝V5.3會出現錯誤訊息。

1.執行畫面

下載V5.2.7安裝
範例檔案下載: http://download.bestdaylong.com/f529.htm

2018年2月22日 星期四

【Excel教學】用Excel製作薪資條(不使用VBA)

薪資條要每個人都要有標題,除了一筆一筆複製,另外也可以用我之前寫過的VBA工具,看到其他人不使用VBA的做法,覺得不錯,所以把它做成教學,方便其他的網友使用。

1.處理前資料

2.先複製同筆數的標題列

3.將員工資料放在標題底下

4.在標題填入流水號並複製到底下員工薪水

5.依儲存格D欄排序

6.將輔助欄位刪除就完成
範例檔案下載:http://download.bestdaylong.com/f523.htm

贊助

彰化一整天粉絲團

About Me

我的相片

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

Google+ Followers