贊助廠商

有同事為了取得一段日期區間,在 Excel 中的工作表中如下圖的A,B,C三欄,現在如果想要把這三欄合併成一欄,該如何處理?

Excel-在儲存格中合併顯示多個日期(TEXT)

同仁原本使用的方式是用字串串接的方式,所以使用公式:

儲存格E2:=A2&B2&C2

結果顯然是錯的,所有日期都被轉回原型(一個數字),該如何調整公式呢?

儲存格G2:=TEXT(A2,"yyyy/mm/dd")&B2&TEXT(C2,"yyyy/mm/dd")

vincent 發表在 痞客邦 留言(2) 人氣()

網友問到:下圖左是一個年齡、仰臥起坐次數及得分的對照表,如何依據測試人員的年齡和仰臥起坐次數求得對應的積分(下圖右)?

Excel-根據兩個參數(條件)在矩陣中查表(INDEX,MATCH,OFFSET)

 

【公式設計與解析】

儲存格L2:=INDEX($A$2:$A$7,MATCH(K2,OFFSET($A$1,1,MATCH(J2,
$B$1:$G$1,0),6,1),1),1)

vincent 發表在 痞客邦 留言(0) 人氣()

如下圖,網友想要在 Excel 的資料清單中依據日期欄位中的內容,以第一個空白者(未填日期)為第一個預備人選,由上而下把空白者列為優先名單,該如何處理?

Excel-依據欄位中空白的儲存格列出資料清單(OFFSET,SMALL,ROW)

 

【公式設計與解析】

選取儲存格C1:C17,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。

儲存格F2:{=OFFSET($A$1,SMALL(IF(日期="",ROW(日期),999),ROW(1:1))-1,0)}

vincent 發表在 痞客邦 留言(3) 人氣()

最近有二個網友不約而同問到:在 Excel 的工作表中常會有一些不可見的『亂碼』隱藏其中,造成格式無法對齊、搜尋無法準確等現象,如何快速消掉這些造成亂碼的字元呢?

我其實也沒有特別好的方法!但是在 Excel 2010/2013/2016 等版本中有一個 CLEAN 函數,可以消除字串中無法列印的元字,可以拿拿試試是否可用。

例如:在下圖中的儲存格C2,其公式:=A2&CHAR(10)&B2。

這個公式可以將兩個字串放在同一個儲存格中,並且分成二列呈現。

其中 CHAR(10) 即為不可見、無法列印的字元。

儲存格D2:=CLEAN(C2),利用 CLEAN 函數即會消除這些不可見的字元。

vincent 發表在 痞客邦 留言(0) 人氣()

在 Excel 的公式中使用定義『名稱』是非常實用的方式,用名稱來代表儲存格,就好像你較能接受使用 Domain Name(例如:www.edu.tw),而較無法直接使用 IP(例如:140.111.14.180)。

例如,在下圖中有二個工作表(DATA1和DATA2),其中有兩個類似的表格。

Excel-名稱的定義與使用

我們試著在名稱管理員中建立以下這些名稱:

Excel-名稱的定義與使用

觀察這些定義的名稱,你可以發現:

vincent 發表在 痞客邦 留言(1) 人氣()

同仁遇到這樣的問題:在下圖左,是一個 Excel 的資料表,如果要將這個資料表轉換成下圖右的樣式,該如何處理?

Excel-資料表轉換(INDEX,COLUMN,SMALL)

面對這樣的問題,只要判斷儲存格內是否為『V』,如果成立,則印出欄標題,如果不成立,則印出空白。

儲存格J2:=IF(B2="V",B$1,"")

複製儲存格J2,貼至其他每個儲存格。

特別提醒:在 Excel 的判斷式中,雖然是使用『B2="V"』,但是儲存格B2不管輸入『V』或是『v』,結果都是成立的。

vincent 發表在 痞客邦 留言(2) 人氣()

有網友根據這篇 Excel 的教學文章:

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

想要變換一下,在下圖中想要根據某一欄的一個數值,來反推『列標題』,該如何處理?

Excel-以欄列交叉對照查表(OFFSET,MATCH)

 

【公式設計與解析】

vincent 發表在 痞客邦 留言(1) 人氣()

一般使用者習慣的 Excel 表格應該如下的格式,其中共有三個欄位,如果你要排序,只要指定某一個欄位,再透過排序的工具即可輕易完成。

Excel-在表格中取列來做為排序依據(循列排序)

但是有人的表格如下圖(上),在不想調整表格的前提下(轉置90度),該如何排序成下圖(下)?本例是以第9列的成績資料來排序。

Excel-在表格中取列來做為排序依據(循列排序)

雖然大家都習慣取用『欄』的資料來排序,但是 Excel 也是有提供取『列』來排序的功能。

1. 選取儲存格B7:M9。

vincent 發表在 痞客邦 留言(0) 人氣()

最近在公務上有些文件需要跨處室、多單位的同事一起來填寫,大家不知道如何操作可以最快速且最方便?有人建議使用 Word 中的「追蹤修訂」來處理,但是最後又得將 Word 文件彙整到某一人的電腦中來處理,整個過程也不是很方便。

最後是建議使用 Google 雲端硬碟的 Google 文件來共筆輸入資料,過程不需要使用 Word,也不用進行彙整工作,並且任何地點、任何時間都可以處理,最後再匯出成 Word 檔,再利用 Word 的排版功能加以美工處理。

(因為還有些同事不太熟悉 Google 文件的共用功能,以下稍微簡介說明。)

如何來啟用文件和他人共筆呢?在 Google 文件中點選「共用」,你就可以藉由輸入其他人的 Google 帳號(Email)來邀請加入來協作。被邀請的使用者,只要登入 Google 帳號,即可找到這個被分享的文件。記得:如果要讓使用者可以輸入資料,得挑選「可以編輯」。

有人建議多人使用同一個 Google 帳號來登入,這個作法並不建議,因為無法區隔出每一個做了那些修訂。

使用Google文件共筆協作省時又省事

vincent 發表在 痞客邦 留言(0) 人氣()

在下圖中有一個 Excel 的資料表,其中要依女和男的年齡分別找出對應的組別,該如何處理?

本例是儲存格E11:G15的內容為例子來設計,並練習使用 VLOOKUP、OFFSET、MATCH 等函數。

Excel-使用多變數、多條件查詢(LOOKUP,OFFSET,MATCH)

 

【公式設計與解析】

儲存格C2:=LOOKUP($B2,OFFSET($E$1,0,MATCH($A2,$E$1:$F$1,0)-1,5,4-

vincent 發表在 痞客邦 留言(2) 人氣()

Close

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

reload

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼