網友問到:在下圖中的 Excel 工作表,其中要根據年齡和性別來判定傳回值,條件:
1. 年齡<1或年齡>=66,則不分男女傳回 0。
2. 若不在此年齡範圍,則男生傳回 1,女生傳回 2。
該如何設計公式。
網友問到:在下圖中的 Excel 工作表,其中要根據年齡和性別來判定傳回值,條件:
1. 年齡<1或年齡>=66,則不分男女傳回 0。
2. 若不在此年齡範圍,則男生傳回 1,女生傳回 2。
該如何設計公式。
有網友問到:通常我們會在 Excel 中,使用「資料驗證」工具來決定是否要在儲存格中輸入時接受(拒絶)某些資料。但是某些狀況下又想放行不符合條件的狀況,所以如果能有一個切換機制,就不用一直修改資料驗證的設定,該如何處理呢?
以下圖為例,假設當我們在儲存格A2:A10中設定輸入的資料必須是數值,因此當輸入文字(abc)時會出現提示不符合資料驗證準則的訊息。我們要以這個例子,來試試製作一個切換機制。
1. 在儲存格D2中設定資料驗證,使其可以選取「V,X」。
其中資料驗證設定如下:
在 Excel 的工作表中有時候對於顯示 0 的數字,想要讓它不顯示,有時在計算時可能遇到除數為0產生的除法錯誤(#DIV/0),如何要把它視為 0,再來計算,這兩個問題該如何解決呢?
在下圖的C欄中,所以內容為0者全都顯示了空白。在儲存各E9產生了除數為 0 的錯誤訊息,所以在計算平均時,也會得到一個錯誤結果。
【公式設計與解析】
以上圖為例,要將 10 個儲存格的內容計算平均,但是不想將儲存格為0者併入計算。
網友問到:在 Excel 的工作表中有一個數值清單,如果將每個數值依其個位數:
(1) 1 ~ 4 :捨去為 0;(2) 5 :保留 5;(3) 6 ~ 9:進位為 0
該如何處理?
【公式設計與解析】
網友遇到這樣的問題:在 Excel 中有一個去程和回程日期的資料表,如何根據這兩個日期對應查到大人和孩童所對應的票價?
在下圖中,利用兩個條件:出發日期、回程日期,在下圖右側的對照表中查得大人和孩童的票價,並且在對照表中以不同色彩標示查詢結果。
【公式設計與解析】
選取儲存格E1:H10,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:出發日期、回程日期、大人、孩童。
網友問到:如果根據一個上下班打卡的資料表,如何在 Excel 中計算各個班別的數量?
如下圖,共有三種班別,起迄時間都不相同,如何根據打卡時間的清單,自動判斷各個班別(早班、晚班、全天)的數量?
【公式設計與解析】
選取B欄和C欄有資料的儲存格,按 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:上班打卡、下班打卡。
學校開學了,老師們早在寒假期間就陸續備課。很多老師製作了自己的簡報當為上課教材,但是投影片不一定適合直接印給學生當講義,而如果使用 PowerPoint 所提供的講義模式來列印,也無法滿足老師的需求,該如何來調整變化?
說到要編製講義,使用 Word 或許還是比較方便實用的工具。而在 PowerPoint 中其實就有匯出投影片到 Word 的功能。
當你選取[檔案]功能表,再選取[匯出]選項。在[匯出]功能區中點選「建立講義」,PowerPiont 會執行:
1. 將投影片和備忘稿於 Word 文件
2. 在 Word 中編輯並格式化內容
有同仁問到:一份文件200頁,其中有很多大大小小的圖片,如何才能快速移除文件中的所有圖片,並且保留原來的文字和格式?
參考下圖,文件中的許多地方都有一些各種格式的圖片,可能高達數十、數百個,要移除文件中的所有圖片,並且保留原來的文字和格式,如果一個一個圖片點選再刪除,實在不是太容易的事。如果複製文件後貼至記事本中,只會留下文字,但格式也會全跑掉,該如何處理呢?
方法其實很簡單:
請先開啟[尋找與取代]對話框,於[尋找目標]文字方塊中輸入「^g」,然後[取代為]文字方塊中要留空。當你按下[全部取代]按鈕,所有圖片瞬間秒殺!
有網友問到一個在 Excel 工作表中資料重組的問題:如下圖工作表中資料清單,甲、乙、丙、丁四欄各有24列資料,如何將其重組為二個甲、乙、丙、丁四欄各有12列資料?
下圖中的公式中的參數 12 和 4 對應到圖示中的 12 和 4。
【公式設計與解析】
儲存格F:=OFFSET($A$2,MOD(ROW(1:1)-1,12)+INT((COLUMN(A:A)-1)/4)*12,
網友問到:在 Excel 的工作表中(如下圖左),如果想要在每欄取定量來重排,並且去除空白儲存格(如下圖右),該如何處理?
在下圖中,在甲、乙、丙三種不同且重覆的欄位,如果根據取樣中的數量(本例為5),將甲的多欄資料重組在一欄(每欄取5個),並且希望去除空格。
【公式設計與解析】
(1) OFFSET(A$2,MOD(ROW(1:1)-1,$H$2),INT((ROW(1:1)-1)/$H$2)*3)
有網友問到:在 Excel 的工作表中有一個日期清單記錄每天的數值,如何每天自動只顯示當天的小計資料(參考下圖),該如何處理?
如下圖,在日期和數量清單中,資料會一直輸入,如何才能只顯示今天的小計而已。
【公式設計與解析】
儲存格E2:=SUMPRODUCT((A2:A1000=TODAY())*B2:B1000)
在下圖中,是一個以星期幾為主,來顯示各月日期的萬年曆,如何在 Excel 的工作表中建立這個萬年曆?
有了這個萬年曆,只要改變儲存格A1(年份),即可顯示當年的月份和星期幾的對照。因此,例如:可以快速找到各月週六的日期。要如何來設計公式?
【公式設計與解析】
儲存格B2:=VALUE(TEXT(DATE($A$1,COLUMN(A:A),1)-WEEKDAY(DATE($A$1,
學校同仁問到:在使用 Word 編輯文件時,由於列印出來的文件還要插入其他資料,所以頁碼無法連續,若在其中插入空白頁,列印時又會浪費紙張,該如何處理?
可以在 Word 中設定不連續頁碼!如下圖,第 4 頁的頁碼要修正為第 6 頁,其後的頁碼要跟著依序順延,應該如何操作?
參考以下的步驟:
1. 先在一個 Word 文件中的頁尾插立頁碼。(本練習要將第 4 頁改為第 6 頁)
開學之初而已,就有同仁問到在 Excel 中,關於名次計算的問題。參考下圖,一般在計算名次時,可能會有這樣的狀況:
1. 名次重覆跳過:例如有 2 個第 4 名,所以沒有第 5 名。
2. 名次重覆不跳過:例如有 2 個第 4 名,原來的第 6 名成為第 5 名。
3. 名次不重覆:例如有 2 個第 4 名,會隨機產生一個第 4 名,另一個為第 5 名。
網友問到 Excel 的問題:如何在一個日期區間中計算工作天數、放假天數?
參考下圖,因為每個人遇到的狀況不同,當要計算工作天數時,你可能要考量到星期六、星期日和放假日,也要考量到補班日,該如何處理?
【公式設計與解析】
1. 不含六日的工作天數
網友想要將 Excel 工作表中的資料表(如下圖左)取出資料重組成下圖右的樣子,該如何處理?即原本甲、乙、丙分散在多欄,現在要將甲、乙、丙分別組合成一欄,該如何設計公式?
【公式設計與解析】
分析上圖,甲、乙、丙有 3 個類別,每個類別有 5 個項目。
儲存格M2:=OFFSET($A$1,MOD(ROW(1:1)-1,5)+1,INT((ROW(1:1)-1)/5)*3)
有網友問到:在 Excel 中有一個資料清單,如下圖,如果想要在每種相同商品中找出最高/最低報價及其廠商,該如何處理?
在下圖中,有甲、乙、丙、丁、戊等不同廠商,分別有A、B、C、D、E等不同商品,現在要來找出各種商品的最高/最低報價和所屬廠商。
【公式設計與解析】
選取儲存格A1:C26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:廠商、商品、報價。
網友問到:在下圖中的 Excel 資料表中,如果要計算三個條件都成立的個數有幾個,該如何處理,為何下圖中的公式會『錯誤』。其中的重點是要滿足日期中符合所要月份的條件。
選取儲存格A1:C27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、日期、項目。
【公式說明】
儲存格F4:=SUMPRODUCT((人員=F1)*(MONTH(日期)=F2)*(項目=F3))
本篇文章純粹是要練習幾個 Excel 函數的應用,是否有實用性沒有考量到。參考下圖,有兩個儲存格想要比較其中內容,每一個位元的字元是否相同。
一、儲存格中的內容全為數字
(1) 計算各位元相同者
儲存格C5: