網友又問到最多人提問的問題,我想也是使用需求最高的問題。在 Excel 中如何根據某一欄的內容來查詢對應的另一欄中的內容?
如下圖,希望根據A欄的日期來查詢B欄的數值,該如何處理?
假設日期已排序,日期範圍由儲存格A2(2015/1/1)至儲存格A366(2015/12/31)。
查表的概念是在一個表格(A2:B366)中,由第一欄(A欄)來查詢某個日期的所在位置,再找出第二欄(B欄)中的『對應』數值,通常在第一欄(A欄)來查詢某個日期的所在位置時會傳回一數值(位於第幾個;在本例中這個數值會和列號相差 1,因為第一個日期由第 2 列開始)。
vincent 發表在 痞客邦 留言(2) 人氣()
有同仁問到:在 Excel 的工作表中,常會由其他來源帶入資料,但是內容是文字或數字,常會一時間看不出來,卻又造成運算結果的錯誤,該如何處理?
討論之餘,或許可以試試以下方式:利用設定格式化的條件來輔助檢查。
參考下圖:分別檢查儲存格為『數字、邏輯值、文字、錯誤訊息』。
在本例中,選取儲存格A1:D17。利用以下四個條件新增四個規則:
條件1格式:淺黃色;公式:=ISNUMBER(A1)
vincent 發表在 痞客邦 留言(1) 人氣()
網友詢問:在 Excel 中如果有一個數值欄位,如果想要在其右邊/左邊補足固定位數填0,該如何處理?
以下圖為例,在A欄中有一數值欄位,在B欄中要在右邊填0補滿8位,在C欄中要在左邊填0填滿8位。
【公式設計與解析】
儲存格B2:=RIGHT(REPT("0",7)&A2,8)
vincent 發表在 痞客邦 留言(2) 人氣()
有同仁問到在 Excel 中,關於日期的顯示格式設定問題,以下稍做整理。
這是先前的文章可參考:http://isvincent.pixnet.net/blog/post/38519565
關於日期的基本格式:
格式 |
功能 |
m |
將月份顯示為數字,前面不補零。 |
mm |
將月份顯示為數字,適當時前面補零。 |
mmm |
將月份顯示為縮寫 (Jan 至 Dec)。 |
mmmm |
顯示完整月份名稱 (January 至 December)。 |
mmmmm |
將月份顯示為單一字母 (J 至 D)。 |
d |
將日期顯示為數字,前面不補零。 |
dd |
將日期顯示為數字,適當時前面補零。 |
ddd |
將星期顯示為縮寫 (Sun 至 Sat)。 |
dddd |
顯示完整星期名稱 (Sunday 至 Saturday)。 |
yy |
將年份顯示為兩位數字。 |
yyyy |
將年份顯示為四位數字。 |
gg |
顯示民國 |
ggg |
顯示中華民國 |
e |
顯示民國年 |
aaa |
以週日~週六表示 |
aaaa |
以星期日~星期六表示 |
範例:
vincent 發表在 痞客邦 留言(2) 人氣()
有網友問到在 Excel 的工作表中,若是在同一欄中,如何避免輸入重覆的內容?這是在輸入資料時常見的問題,一般可以使用二種不同的方式。
1. 使用設定格式化的條件
參考下圖,若在同一欄中輸入了重覆的內容時,就讓重覆內容的儲存格顯示不同的格式。這個方法僅是提示作用,無法強制不輸入重覆的內容。假設一欄中有資料的儲存格範圍是A2:A200。
選取儲存格A2:A200,設定其格式化的條件:
規則類到:使用公式來決定要格式化哪些儲存格
vincent 發表在 痞客邦 留言(2) 人氣()
在 Google Keep App 中,我常拿來做一些瑣事的記事,主要的原因是因為其可以方便的依時間或地點來設定提醒。並且要輸入這些瑣碎的事,大部分我都是用『唸』的方式來輸入,即使文字辨識錯誤,我也不太在意,因為只是處理『瑣碎的事』而已。
以 Android 的 Google Keep App 為例:(在此下載)
最近在使用 Google Keep App 來以語音輸入時,發現它變得更聰明了,當你唸了一個詞語時,再點選這個顯示的文字,它會列出音相似的詞語供選取。
以『學不完.教不停.用不盡』為例,若分成三個字組來唸,你可以再次點選顯示的字組就可以改選其他字組。若你是連續的唸出這三個字組,則其會自動辨識為正確的文字,不用(也不能)再挑字。
vincent 發表在 痞客邦 留言(0) 人氣()
網友問到:如何在 Excel 工作表裡將一個資料清單中為民國年格式的日期轉換為西元年格式?
參考下圖左為民國年格式,轉換為西元年格式,如下圖右。
【公式設計與解析】
藉著例子來練習:RIGHT、MID、LEFT、DATE、TEXT函數。
儲存格C2:=TEXT(DATE(LEFT(RIGHT("0"&A2,7),3)+1911,MID(
vincent 發表在 痞客邦 留言(4) 人氣()
在 Excel 中的公式中可以使用萬用字元『*、?』,可以很方便的查詢到不只一個的數量,例如:=COUNTIF(K1:K25,"李*"),可以在儲存格K1:K25中查詢,姓「李」的人有幾個,不管其名字為一個字、二個字、三個字等,都能併入計算。
但是如果是儲存格內容中已含有『*、?』,則處理上會不一樣。
1. 查詢時使用變數(*、?、~置於儲存格中)
(O) 儲存格E2:=VLOOKUP(D2,A2:B21,2,FALSE)
(O) 儲存格E3:=VLOOKUP(D3,A2:B21,2,FALSE)
vincent 發表在 痞客邦 留言(1) 人氣()
在 Excel 中如果有多個工作表,若要將其摘要在同一個工作表,該如何設計公式?
以二個工作表為例,參考下圖,有工作表:104年和105年。
下圖中是在另一個工作表(整理)中以下拉式選單選取一個工作表,並將所對應的工作表摘要成下圖的清單。
vincent 發表在 痞客邦 留言(2) 人氣()
有同仁問到:在 PowerPoint 中,對於 SmartArt 中建立的內容,如果在其中的項目建立『超連結』,當在實際執行簡報時,會因為超連結本身和是否點選過這個超連結,而文字顯示的色彩會和原先的設定有差異,而且超連結的文字會自動加上底線,該如何避免呢?
好問題!而且解決之道也很簡單!而且也適用 Word 和 Excel。
通常一般人的做法是:選取文字後再設定超連結,選取一個『連結至』的目的地(參考下圖)。
很明顯,在投影片上即可看出設定超連結的文字預設為藍色並且加上了底線,目前看來已破壞了原先的色彩配置。
vincent 發表在 痞客邦 留言(0) 人氣()