【Excel教學】加總特定範圍內未隱藏的儲存格且只選擇正數加總

我們知道subtotal可以加總篩選的加總(sum)，代碼是用9，或您是手動隱藏的欄位，就要將代碼變加100變成109，有網友問到subtotal沒有sumif，可是我們可以用offset加上陣列公式，取出每個值，再用sumproduct將>0的條件加入,最後就可以做到sumif的功能。

1.在儲存格D1輸入

=SUMPRODUCT(INT(A2:A6>0),SUBTOTAL(109,OFFSET(A2,ROW(A2:A6)-ROW(A2),0)))

2.我選取其中兩列將它隱藏

3.加總只會顯示看得見且大於0的加總

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

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

有網友來信，說明他工作都是用MSSQL，問說要如何在Excel中從MSSQL資料庫中將資料查詢到Excel，所以就將之前Excel連線到Access的範例改成MSSQL，若有相同需求的網友可以下載範例學習。

1.執行畫面

2.若密碼沒輸入會彈出輸入密碼畫面

3.主要是這行不同

檔案下載: http://download.bestdaylong.com/f528.htm

【Excel教學】如何輸入資料後自動出現今天日期

有人問說如何讓excel的函數可以顯示輸入日期，一般是用函數today來顯示今天日期，但是隔天再輸入時，昨天的日期又會變成今天，有沒有辦法解決，這時候就可以使用循環參照來解決，只要設定1次就可以，若修改也要改變，就要先將儲存格清空，再輸入就會重新顯示，直接修改資料日期是不會改變。

1.利用二個if做判斷

2.點選【檔案】

3.【選項】

4.【公式】/勾【啟用反覆運算】

5.若沒有勾就會出現這個畫面

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

【Excel教學】用快速鍵讓Excel自動加總

若您有一段數字要做加總，而您對函數又不太熟時，可以將要加總的範圍選取，再按下【Alt】+【=】，就會自動在最後一列做加總。

  

1.先選取要加總的範圍，按下【Alt】+【=】

  

2.最後一列自動顯示加總的結果

【Excel教學】利用SUBSTITUTE來做字串取代

網友問到如何取代字串中的某一個符號，換成某一個儲存格的內容，這時候我們會用SUBSTITUTE函數來解決，在函數中輸入字串，要記得在前後加上雙引號，要取代的儲存格位置則不能加，加入會沒作用。

1.網友的問題

2.公式的用法

輸入=SUBSTITUTE(D2,"～",C2)，往下複製

範例程式下載: http://download.bestdaylong.com/f510.htm

【Excel教學】如何讓文字中取出數字可以做加總

這是一個很重要的觀念，在Excel中若要使用函數，只有數字才會有作用，若是文字型別的數字，則回傳的值都會是零，那什麼時候會出現文字型數字,只要用跟文字有關的函數，取出來的數字就是文字數字，文字數字可以透過四則運算時，會強制轉型成數字，所以您才看到有人會在文字數字前面加二個減號，那二個的意思是負負得正，就會變成數字，其實是有函數value可以將文字轉換成數字，用這個函數也方便其他人可以知道您要作的是將文字轉數字，這次的例字是從一串英文名字加上星號再加上數字，請把每個人後面的數字加總起來，因為要尋找的是星號(萬用字元)，所以只能用find函數來解決。

1.利用find找*號的位置

2.再利用mid從*號後面取出全部字元，因為不知道有多少，用len取出全部字元

3.發現用sum來加總結果是0

4.再利用Value函數，則加總就會成功

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

【Excel教學】利用函數sumproduct計算每個人團購的金額

今天有人問我要如何快速的將每個人的團購金額快速加總，就想到要用sumproduct來解決sum是加總product是乘的意恩，合起來就是將所有的加乘的總合加總，使用這個函數有一點要注意，就是所有相乘的數量要相同，否則計算會出現#VALUE!
的錯誤。

1.因為要往右複製公式，所以要加價格前加上$號

檔案下載: http://download.bestdaylong.com/f503.htm

【Excel教學】Excel如何快速將多個工作表做平均(有影音教學)

假設您有二個工作表【期中成績】【期末成績】裡面只有成績不同，其他的標題及學生順序都相同，您想要將這兩個工作表的成績做平均來當作總成績，這時候就可以使用Excel的合併彙算的功能來達成。

1.總成績是【期中成績】及【期末成績】的平均

2.在儲存格C2點選【資料】/【合併彙算】

3.選取函數【平均值】，點選要計算的工作表範圍，按下【確定】

4.就會自動將出平均成績算出

檔案下載: http://download.bestdaylong.com/f498.htm

在excel利用SUBSTITUTE取代有規則的數字字串

這個題目如果是我自己做，會用數學公式找出1到20,21到40的規則，再用文字相加，但是上課的經驗告訴我，只要跟數學有關的，大部分的使用者就會聽不太懂，所以這次用另一個寫法，就是先利用excel產生有規則的數字，再把要取代的文字換成a跟b，再用取代來將a.b取代就完成。

1.黃色是有規則的部分

2.因為有2個變數，所以用a,b來取代

3.利用excel自動產生相關的數字

4.利用SUBSTITUTE取代二次完成

範例檔案:http://download.bestdaylong.com/f484.htm

excel如何篩選特定關鍵字的資料

同事問我要如何知道住址在新竹市的人數有多少人，我跟他說用篩選的文字篩選就可以做到，因為要寫範例給他看，就到網路上將全聯的全省623筆的門市電話地址用VBA下載下來做練習，底下是說明篩選的用法。

1.確認作用儲存格在資料範圍內，點選【資料】/【篩選】

2.點選右下角的向下符號/【文字篩選】/【自訂篩選】

3.若要找開頭是台北市的地址，可以輸入【台北市*】

*代表任何連續字串

?代表單一字元

4.若要換不同條件，可以點選【清除】

5.若是要找出在中山路的店家有那些

就可以輸入【*中山路*】

6.左下角會出現總共有幾筆，找到幾筆符合的記錄

練習檔案下載: http://download.bestdaylong.com/f479.htm
全聯全省門市電話地址資料查詢.xls

excel如何利用lookup與mid找出儲存格中的某一字元的最後出現的位置

之前遇過類似的問題都是用輔助欄位一個一個找出最後一次出現的位置，這次在網路上看到其他網友使用mid與row函數來解決，真的又學了一招，這是陣列公式的用法，但是公式不使用陣列公式輸入，與sumproduct的用法有點像，其中的row(1:100)，是表示把字串的第1個字到第100字用mid取出，再去判斷是否等於某字串，再利用除零會產生錯誤，將所有的不符合的資料過濾掉，最後會回傳最後一個1出的位置。

1.利用lookup找出最後減號出現的位置
=LOOKUP(1,0/(MID(A2,ROW($1:$100),1)="-"),ROW($1:$100))

2.用函數left取出前面除了減號的字元

3.也可以將left跟lookup變成一個公式

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

excel的水平及垂直捲軸不見怎麼該它出現

我的excel2016不知道什麼時候變成開啟舊檔時，竟然找不到水平及垂直捲軸，如何要透過它快速瀏覽資料，就要到設定中將水平及垂直捲軸勾選，再儲存就可以看到。

1.新檔案看不到捲軸

2.點選【檔案】

3.【選項】

4.【進階】將水平及垂直捲軸前面的勾勾選，再按【確定】

5.水平及垂直捲軸出現了

怎麼解決excel工作表索引標籤不見了

在excel2016開啟舊檔時看得到工作表索引標籤，但是只要新增一個excel檔案工作表索引標籤就不見，這時候可以到設定/進階/查看【顯示工作表索引標籤】是否有打勾，若沒有將它打勾就可以看到了。

1.下方看不到工作表

2.到設定/進階/【顯示工作表索引標籤】將它打勾/確定

3.工作表出現了

excel如何計算工時(包含休息時間)

在製造業常會有某個製程要花費多少時間完成，要如何準確的計算出最後完成時間，還要將每段不同的休息時間也算進去，如果用if會寫很多判斷，也不容易偵錯，這時候就可以利用sumproduct函數來將工時內的休息時間也算進去，中間有用到分鐘，excel的一天用1代表，所以1分鐘就是1/24/60，24是24小時，1小時是60分鐘。

1.若是只加上花費時間會少計了休息時間

2.用sumproduct可以將中間的休息時間正常的算時，

原本的20:00還要加上休息的時間90分，

顯示21:30才是正確的完成時間

3.若中間只有一個休息時間，也可以正確的算出

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

excel如何讓數字的最後一碼等於某數時數字加一

這是我之前的同事問我的問題，我跟他說用right跟if就可以做到，但是他說他做了可是不會出來，我將我作的解答給他看，他發現他的數字沒有加上雙引號，所以才會出不來，這裡有一個大部分的人都會做錯的地方，就是什麼時候數字要加雙引號，什麼時候不用，只要是用文字函數產生的結果，不管您處理前是數字還是文字，都會變成文字，若是用像取餘數的方式，那個數字就不能加雙引號，若加上雙引號，就不會產生正常的結果。

1.他想要的結果

2.用函數right因為數字9沒有加雙引號，結果是數字不會加一

3.數字9前後加上雙引號就正常

4.因為是數字，也可以用函數MOD(取餘數來解決)，這時候數字就不用加雙引號

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