贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

目前日期文章:201603 (34)

瀏覽方式: 標題列表 簡短摘要

網友問到:如何在 Excel 工作表裡將一個資料清單中為民國年格式的日期轉換為西元年格式?

參考下圖左為民國年格式,轉換為西元年格式,如下圖右。

Excel-民國年格式轉換為西元年格式(RIGHT,MID,LEFT,TEXT,DATE)

【公式設計與解析】

藉著例子來練習:RIGHT、MID、LEFT、DATE、TEXT函數。

儲存格C2:=TEXT(DATE(LEFT(RIGHT("0"&A2,7),3)+1911,MID(
RIGHT("0"&A2,7),4,2),RIGHT(RIGHT("0"&A2,7),2)),"yyyy/mm/dd")

複製儲存格C2,往下各列貼上。

(1) RIGHT("0"&A2,7)

由於民國年在 100 年以前為 2 碼,100 年以後為3 碼,所以儲存格A2可能為 6 碼,也可能為 7 碼。RIGHT("0"&A2,7)可以保證取得 7 碼,若原先為 6 碼者,則第一碼為 0。

(2) LEFT(RIGHT("0"&A2,7),3)+1911

取得左邊 3 碼或 2 碼的民國年份,再加上 1911 轉換為西元年份。

(3) MID(RIGHT("0"&A2,7),4,2)

取得中間 2 碼為月份。

(4) RIGHT(RIGHT("0"&A2,7),2)

取得右邊 2 碼為日份。

(5) DATE(LEFT(第(1)式,第(2)式,第(3)式)

將取得的年、月、日轉換為日期格式(西元年)

(6) TEXT(DATE(LEFT(第(1)式,第(2)式,第(3)式),"yyyy/mm/dd")

將日期格式轉換為 yyyy/mm/dd,西元年 4 碼+月份 2 碼+日數 2 碼。

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

在 Excel 中的公式中可以使用萬用字元『*、?』,可以很方便的查詢到不只一個的數量,例如:=COUNTIF(K1:K25,"李*"),可以在儲存格K1:K25中查詢,姓「李」的人有幾個,不管其名字為一個字、二個字、三個字等,都能併入計算。

但是如果是儲存格內容中已含有『*、?』,則處理上會不一樣。

Excel-公式中如何查詢含~,*,?字元的內容

1. 查詢時使用變數(*、?、~置於儲存格中)

(O) 儲存格E2:=VLOOKUP(D2,A2:B21,2,FALSE)

(O) 儲存格E3:=VLOOKUP(D3,A2:B21,2,FALSE)

(O) 儲存格E4:=VLOOKUP(D4,A2:B21,2,FALSE)

(X) 儲存格E5:=VLOOKUP(D5,A2:B21,2,FALSE)

為何會出錯呢?因為使用 VLOOKUP 函數時,儲存格D5為『~辛』,其中的『~』不能直接查詢,要使用『~~』才能查詢『~』。

公式修正:

(O) 儲存格E6:=VLOOKUP("~"&D5,A2:B21,2,FALSE)

(X) 儲存格E7:=INDEX(A2:B21,MATCH(D7,A2:A21,0),2)

在 INDEX 函數中使用方式和 VLOOKUP 函數一樣,直接查詢『~』開頭的內容會出錯。

修正如下的公式:

(O) 儲存格E8:=INDEX(A2:B21,MATCH("~"&D7,A2:A21,0),2)


2. 查詢時使用定數(*、?、~)

(O) 儲存格E9:=COUNTIF(A2:A21,"~~*")

(O) 儲存格E10:=COUNTIF(A2:A21,"~**")

(O) 儲存格E11:=COUNTIF(A2:A21,"~?*")

若要查詢『*、?、~』這三個字元,都要多加一個『~』。

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

在 Excel 中如果有多個工作表,若要將其摘要在同一個工作表,該如何設計公式?

以二個工作表為例,參考下圖,有工作表:104年和105年。

Excel-在工作表中選取其他工作表並摘要資料表(INDIRECT,SUMPRODUCT) Excel-在工作表中選取其他工作表並摘要資料表(INDIRECT,SUMPRODUCT)

下圖中是在另一個工作表(整理)中以下拉式選單選取一個工作表,並將所對應的工作表摘要成下圖的清單。

Excel-在工作表中選取其他工作表並摘要資料表(INDIRECT,SUMPRODUCT)

 

【公式設計與解析】

1. 製作下拉式清單

利用「資料驗證」功能,將資料驗證準則設定為:

儲存格內允許:清單;來源:「104年,105年」。

注意:其中清單內容必須和工作表名稱一致。

image

 

2. 設定名稱

因為104年、105年兩個工作表中有相同的欄位名稱,所以在定義名稱時,千萬不要使用 Ctrl+Shift+F3 鍵來快速設定名稱,因為這個操作的預設領域為『活頁簿』。所以請你手動設定將每一個工作表中的資料,並以其欄位名稱來定義名稱,並將其名稱指定所屬的工作表名稱。

image

最後共設定了 6 個名稱:

image

 

3. 輸入公式

(1) 使用定數

假設以『105年』工作表為例,公式設計:

儲存格B2:=SUMPRODUCT(('105年'!姓名=整理!$A2)*('105年'!區別=整理!B$1)*
('105年'!金額))

複製儲存格B2,貼至儲存格B2:E42。

SUMPRODUCT 函數中判斷 ('105年'!姓名=整理!$A2)*('105年'!區別=整理!B$1) 二個條件是否成立,其中「*」運算相當於執行邏輯 AND 運算,傳回 TRUE/FALSE 陣列。而 TRUE/FALSE 陣列和('105年'!金額)執行「*」(乘法)運算時,會轉換為 1/0 陣列。最後傳回相乘積的總和,即為所求。

這個公式若要套用在工作表:104年,則必須修改公式。

 

(2) 使用變數

根據使用定數的公式概念,如果想要使用下拉式清單來選取年度時,工作表名稱變成一個變數。公式修改為:

儲存格B2:=SUMPRODUCT((INDIRECT($G$2&"!姓名")=整理!$A2)*(INDIRECT
($G$2&"!區別")=整理!B$1)*(INDIRECT($G$2&"!金額")))

複製儲存格B2,貼至儲存格B2:E42。

因為儲存格G2的內容會變,所以使用:

INDIRECT($G$2&"!姓名") 取代 '105年'!姓名

INDIRECT($G$2&"!區別") 取代 '105年'!區別

INDIRECT($G$2&"!金額") 取代 '105年'!金額

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

有同仁問到:在 PowerPoint 中,對於 SmartArt 中建立的內容,如果在其中的項目建立『超連結』,當在實際執行簡報時,會因為超連結本身和是否點選過這個超連結,而文字顯示的色彩會和原先的設定有差異,而且超連結的文字會自動加上底線,該如何避免呢?

好問題!而且解決之道也很簡單!而且也適用 Word 和 Excel。

通常一般人的做法是:選取文字後再設定超連結,選取一個『連結至』的目的地(參考下圖)。

Word,Excel,PowerPoint-在SmartArt中設定超連結時讓文字不變色不加底線

很明顯,在投影片上即可看出設定超連結的文字預設為藍色並且加上了底線,目前看來已破壞了原先的色彩配置。

Word,Excel,PowerPoint-在SmartArt中設定超連結時讓文字不變色不加底線

當你在簡報時,點選過的超連結又會變換成不同的色彩,並且超連結文字多了底線。

Word,Excel,PowerPoint-在SmartArt中設定超連結時讓文字不變色不加底線

要克服這種現象,只要在一開始設定超連結時,不要選取文字,而是選取文字的『外框』即可。然後,再用相同的步驟設定超連結。(參考下圖)

這是因為在 SmartArt 的每一個項目都是獨立的物件,不管其形狀為何,都會有一個外框。而對於物件設定超連結就沒有加上底色和改變色彩的問題了,因為那只是對『文字』的效果。

Word,Excel,PowerPoint-在SmartArt中設定超連結時讓文字不變色不加底線

結果是,不管是在點選前或點選後,要超連結的文字都不會改變色彩,也不會出現超連結的底線。

在編輯狀態的樣子:

Word,Excel,PowerPoint-在SmartArt中設定超連結時讓文字不變色不加底線

在播放狀態的樣子:

Word,Excel,PowerPoint-在SmartArt中設定超連結時讓文字不變色不加底線

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

今天聽到老婆大人和兒子在吵著全台和外島的鄉鎮市區到底去過幾個了?一時算也算不清楚啊!當下想一想,那就先建個完整的縣市鄉鎮市區表,有空時好好的算一算,算清楚了,沒去過的就努力的安排時間去一下,也算是愛台灣啊!

首先,到維基百科抓到『中華民國自由地區鄉鎮市區列表』:

網址:https://zh.wikipedia.org/wiki/中華民國自由地區鄉鎮市區列表

Excel-根據縣市鄉鎮市區名在Google地圖上顯示區域界線

其中有一個綜合統計資料列表,其中已有所有的行政區名,選取並複製其中的內容:

Excel-根據縣市鄉鎮市區名在Google地圖上顯示區域界線

接著在 Excel 中貼上,並透過「資料剖析」工具,將縣市和行政區分成兩欄。(參考下圖)

可是,單看行政區名怎麼會有概念是否去過呢?還是要再藉助 Google 地圖,在地圖上回憶比較能確定是否有去過。

儲存格D2:

=HYPERLINK("https://www.google.com.tw/maps/place/" & A2& B2,A2&B2)

Excel-根據縣市鄉鎮市區名在Google地圖上顯示區域界線

例如:點選『南投縣鹿谷鄉』,在 Google 地圖上即會出現這個區域的界線圖,你再放大檢視,有助於了解是否曾經拜訪該區域。(溪頭森林遊樂區去過幾次,現在才發現它就在鹿谷!而只是有印象去時經過鹿谷。)

Excel-根據縣市鄉鎮市區名在Google地圖上顯示區域界線

如果在C欄打勾後,想要立刻計算去過的總數,則輸入以下公式:

儲存格C1:="去過"&COUNTIF(C2:C369,"V")&"個"

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

 

當你在製作比較多頁數的文件時,有時候在切換頁面時會比較不容,或許你使用滑鼠滾輪拼命滾動,或許你使用 PageUp/PageDown 鍵來切換,以循序式的方式來瀏覽和尋找所要的頁面。關於切換,因為寬螢幕之下的文件,螢幕高度小於頁面的長,所以我建議可以試試 Ctrl+ PageUp/PageDown 鍵,因為 Word 會以版面的『一頁』為單位來切換。

畢竟使用循序式的切換是速度比較慢、比較不方便的做法。一般還是會建議同仁們要設定動態連結等操作,在後續的作業中會帶來一些便利之處。

Word文件中的動態連結

當你已經設定了段落樣式,選取[檢視/顯示]功能表中的「功能窗格」後,你可以在功能窗格中切換到你想要的位置。其中的標題看起來是有階層架構的。

Word文件中的動態連結

而這個階層架構取決於你在段落中設定的「大綱階層」,由最高的階層1至最低的階層9,如果不設定階層,就是預設的「本文」。

Word文件中的動態連結

在下圖的例子中,章被設定為階層1、節被設定為階層2、圖表被設定為階層3。

Word文件中的動態連結

當你在建立目錄時,也會用到這個階層定義。

Word文件中的動態連結

當你在製作目錄時,可以選取要顯示的階層。本例只選取階層1和階層2。

Word文件中的動態連結

而當你在設定圖和表的目錄時,並沒有要求選取階層,而是以圖表被定義的樣式為準。本(例的圖和表樣式是設定在階層3)

Word文件中的動態連結

除了本文的目錄和圖表的目錄可以動態連結之外,設定「書籤」也可以達到動態連結的效果。當你在文件中挑選想要的位置,在[插入/連結]功能表中選取「書籤」來設定。

Word文件中的動態連結

在使用時,以建立書籤的目錄為例,選取[參資料/標號]功能表中的「交互參照」選項。並且在[參照類型]清單中選取「書籤」,在[參照類型]清單中選取「頁碼」,按下[插入]按鈕,即會在插入點顯示該書籤的頁碼。

Word文件中的動態連結

當果你選取某些文字後,以設定『超連結』方式來製作動態連結,其中也可以在超連結中選取連結至文件中的一個位置。該位置可以是標題樣式和書籤等。

Word文件中的動態連結

不過,這個作法會破壞原有的文字內容,因為會出現超連結樣式。

Word文件中的動態連結

而不論是本文目錄、圖表目錄或是書籤目錄的頁碼都是功能變數產生,你都可以使用 Ctrl+滑鼠左鍵點選跳至該位置。如果是使用超連結形式,也可以相同操作。

如果你把這個 Word 文件另存新檔為 PDF 格式的檔案,其中的動態連結都還是被保留著,可以點選後跳至所屬位置。

Word文件中的動態連結

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

有位網友每天都要在 Excel 工作表中 Keyin 很多的資料(如下圖),而這些資料之後又要依姓名分別列到各個工作表中。我們能做到在主工作表輸入好資料時,其他分頁工作表的內容就自動到位嗎?因為每天耗時 Keyin 二次,真是浪費生命啊!

其實你只要使用「篩選」功能,分幾次複製到各個工作表就可以解決這個問題了。而你如果想要讓公式來代勞,也想省去操作「篩選」的步驟。參考以下的做法。

在下圖中,假設工作表名稱為 DATA。而資料範圍為儲存格A1:C25。

Excel-篩選資料到其他工作表(OFFSET,陣列公式)

在主工作表DATA中,目前有甲、乙、丙、丁、戊五個人,如何在五個工作表中篩選各自的資料呢?以下用「甲」工作表為例來設計公式。

儲存格A1:{=OFFSET(DATA!$A$1,SMALL(IF(DATA!$A$2:$A$25=甲!$A$1,
ROW(DATA!$A$2:$A$25),9999),ROW(1:1))-1,COLUMN(A:A)-1)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

複製儲存格A1,貼至儲存格A1:C17。

(1) IF(DATA!$A$2:$A$25=甲!$A$1,ROW(DATA!$A$2:$A$25),9999)

在陣列公式中判斷DATA工作表的儲存格A2:A25中是否為『甲』,若是傳回儲存格A2:A25對應的列號,否則傳回『9999』。(9999只是隨機的一個很大的數)

(2) SMALL(第(1)式,ROW(1:1))

利用 SMALL 函數根據第(1)式所得的列號,依序傳回由小到大的列號。

(3) OFFSET(DATA!$A$1,第(2)式-1,COLUMN(A:A)-1)

OFFSET 函數中以第(2)式傳回的列號得到對應的儲存格內容。

Excel-篩選資料到其他工作表(OFFSET,陣列公式)

將工作表「A」的公式內容,複製到其他乙、丙、丁、戊工作表,並修改各自的儲存格A1內容為對應的姓名。

Excel-篩選資料到其他工作表(OFFSET,陣列公式)

註:本例未處理查到不資料時的傳回值。

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

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

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

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

儲存格E2:=A2&B2&C2

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

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

改利用 TEXT 函數轉換儲存格A2和儲存格C2的內容,並且以『yyyy/mm/dd』的格式顯示,再以『&』串接字串 。

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

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

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)

複製儲存格L2,貼至儲存格L2:18。

(1) MATCH(J2,$B$1:$G$1,0)

藉由 MATCH 函數查詢儲存格J2在儲存格範圍B1:G1中的位置,即傳回一個數字,代表第幾個。本例的儲存格J2為『22』,傳回『4』。

(2) OFFSET($A$1,1,第(1)式,6,1)

將第(1)式的傳回值代入 OFFSET 函數求得一個儲存格範圍,本例傳回儲存格E2:E7。

(3) MATCH(K2,第(2)式,1)

再次藉由 MATCH 函數,根據第(2)式傳回的儲存格範圍,查詢儲存格K2內容(本例為29)位於儲存格範圍(本例為儲存格E2:E7)中的位置。本例傳回『3』。

(4) INDEX($A$2:$A$7,第(3)式,1)

將第(3)式的傳回值(3)代入 INDEX 函數查詢儲存格A2:A7中的結果,本例傳回『70』。

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

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

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

 

【公式設計與解析】

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

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

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

(1) IF(日期="",ROW(日期),999)

在陣列公式中,找出日期欄位中是空白者,並傳回日期的列號,否則傳回『999』(這只是一個隨機很大的數字)。

(2) SMALL(IF(日期="",ROW(日期),999),ROW(1:1))-1

將第(1)式的傳回值代入 SMALL 函數中,依序得到列號最小到最大的結果。

當公式往下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→ ... 。

(3) OFFSET($A$1,SMALL(IF(日期="",ROW(日期),999),ROW(1:1))-1,0)

將第(2)式傳回的列號代入 OFFSET 函數,可傳回以儲存格A1為起點對應的儲存格內容。

同理:

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

在下圖中,如果在儲存格C5中輸入資料,則預備人選的清單(下圖右)內容,也會隨之對應改變。

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

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

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

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

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

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

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

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

Excel-消除字串中不可列印的字元(CLEAN)

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

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

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

Excel-名稱的定義與使用

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

Excel-名稱的定義與使用

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

(1) 名稱的適用領域可以在活頁簿,也可以在工作表中。

(2) 分別在活頁簿和工作表中的名稱可以重覆。

(3) 在同一活頁簿和同一工作表中的名稱不得重覆。

Excel-名稱的定義與使用

定義名稱參照內容:(參考下圖)

(1) 參照到定數

(2) 參照到變數

(3) 參照到公式

所有的參照都以『=』起始,而設定參照到定數,可以是數值、字串、陣列。參照到公式的名稱中,還可以參照到別的名稱。

Excel-名稱的定義與使用

觀察工作表中的名稱方塊,只有參照到變數(儲存格範圍)者會顯示在清單中:

Excel-名稱的定義與使用

在儲存格中的公式取用名稱時,若有名稱重覆者,要在參照位址加上工作表名稱,未加工作表名稱者視為參照到活頁簿。

Excel-名稱的定義與使用

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

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

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

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

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

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

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

如果你想要轉換為下圖右的格式,又該如何處理?

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

儲存格R2:{=IFERROR(INDEX($B$1:$G$1,1,SMALL(IF($B2:$G2="V",COLUMN
($B2:$G2),999),COLUMN(A:A))-1),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動輸入「{}」。

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

(1) IF($B2:$G2="V",COLUMN($B2:$G2),999)

在陣列公式中,判斷儲存格B2:G2中是否為『V或v』,傳回對應的儲存格B2:G2的欄號(2~7),否則傳回999。在此使用 999,只是一個隨機很大的數值。

(2) SMALL(第(1)式,COLUMN(A:A))

在陣列公式中,當公式往右複製時,會根據第(1)式所得的欄號利用 SMALL 函數,逐欄取出由最小到最大的欄號。

(3) INDEX($B$1:$G$1,1,第(2)式-1)

根據第(2)式傳回由小到大的欄號,藉助 INDEX 函數以查表方式傳回儲存格B1:G1的儲存格內容(甲~己)。

(4) IFERROR(第(3)式,"")

根據第(3)式傳回值,如果是傳回錯誤訊息者,再以 IFERROR 函數將其顯示為空白。

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

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

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

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

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


【公式設計與解析】

儲存格M5:

=OFFSET(A1,MATCH(M2,OFFSET(A2,,MATCH(M1,B1:K1,0),12,1),0),0)

(1) MATCH(M1,B1:K1,0)

傳回儲存格M1的內容在儲存格B1:K1中的位置,即傳回第幾欄,本例為第7欄(庚欄)。

(2) OFFSET(A2,,MATCH(M1,B1:K1,0),12,1)

利用 OFFSET 函數,取得指定欄位的儲存格範圍,本例傳回儲存格H2:H13。

(3) MATCH(M2,OFFSET(A2,,MATCH(M1,B1:K1,0),12,1),0)

傳回儲存格M2的內容(本例為77)在儲存格範圍(本例為H2:H13)的位置,本例傳回:8。

最後透過 OFFSET 函數,查出對應的列標題。


【延伸練習】

如果採用 INDEX 函數,則公式可調整如下。

儲存格M5:

=INDEX(A2:A13,MATCH(M2,OFFSET(A2,,MATCH(M1,B1:K1,0),12,1),0),1)


【參考資料】

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

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

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

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

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

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

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

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

1. 選取儲存格B7:M9。

2. 選取[常用/排序與篩選]功能表中的「自訂排序」。

3. 點選「選項」按鈕。

4. 在[方向]區中選取「循列排序」。

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

在此你也可以發現,如果針對中文字來排序方面,一般中文字的排序都是「依筆劃排序」,這個原因是因為當初中文字的內碼安排即是以筆劃順序配置內碼順序。但是在此,你還可以選取「依注意排序」,讓中文字的排序可以根據注音的順序來排列。

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

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

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

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

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

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

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

既然是協作,同時間一起修改也可以。在你的視窗中你可以看其他人的頭像,並且以不同色彩標示,對應到文件中的文字也會有不同的色彩標示。

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

因為是多人共同編輯,所以想要知道每個參與者編修的細節,可以藉助[檔案]功能表的「查看修訂版本紀錄」選項。

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

在此可以看到有不同的時間點可供選取,並且不同人的編修以不同色彩標示。在簡單模式下,你會看到你自己編修的部分,你也可以點選「詳細顯示修訂項目」,來看更細的時間點和其他人的修訂內容。

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

以下圖為例,雖然有三個使用者共用這份文件,但是在下午7:45這一項被編修時,只有二位使用者在編修。當你點選「還原為此版本」,文件內容即會回覆到這個時間點的結果。如果你不想文件畫面被那麼多色彩干擾,可以取消勾選:顯示變更。

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

雖然不管幾個人共同編輯,在文件中都會同步顯示,你也可以選取「建議操作」,來標示你的修改只是建議。

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

你隨時可以接受建議或是拒絶建議,使用右側的『V』或是『X』。

image

當你選取「檢視」模式,則會顯示最終的修訂結果,而不會標示每個使用者個人編修部分。並且不會列出被你定義為「建議操作」的部分。

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

當大家都輸入完成後,可以選取[檔案/下載格式]功能,再選取:Microsoft Word(.docx),即可下載文件成為 Word 檔,再進行美工處理。

順便一提,Google 最近推出可以儲存為電子格式的檔案,在此選取:EPUB Publication(.epud),下載的檔案可以置入電子書載具中顯示。

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

vincent 發表在 痞客邦 PIXNET 留言(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-
MATCH($A2,$E$1:$F$1,0)))

複製儲存格C2,貼至儲存格C2:C19。

(1) MATCH($A2,$E$1:$F$1,0):傳回儲存格A2(女或男)在儲存格E1:F1的位置(1或2)

(2) OFFSET($E$1,0,第(1)式-1,5,4-第(1)式)

參數『第(1)式-1』:儲存格B2為女生時傳回 0;儲存格B2為女生時傳回 1。

參數『4-第(1)式』:儲存格B2為女生時傳回 3;儲存格B2為女生時傳回 2。

注意其中的參數 5 和 4,其和圖中標示的 5 和 3 有關。

利用 OFFSET 函數,當儲存格B2為女生時傳回儲存格範圍E1:G5;當儲存格B2為男生時傳回儲存格範圍F1:G5。

最後,將以上二式代入 VLOOKUP 函數即可傳回對應的組別。


如果你不想要另增組別的表格,也可以使用定數的方式來處理公式:

儲存格C2:=IF(A2="女",VLOOKUP(B2,{0,"甲";19,"乙";31,"丙";41,"丁"},2,TRUE),
VLOOKUP(B2,{0,"甲";18,"乙";32,"丙";45,"丁"},2,TRUE))

(1) VLOOKUP(B2,{0,"甲";19,"乙";31,"丙";41,"丁"},2,TRUE):女生的對照表。

(2) VLOOKUP(B2,{0,"甲";18,"乙";32,"丙";45,"丁"},2,TRUE)):男生的對照表。

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

有同仁提到:在 Google 雲端硬碟中使用 Google 文件來編輯時,在表格方面要做出漂亮的表格並不方便,時常會造成困擾,該如何解決這樣的問題?

在 Google 文件中新增一個表格,的確是看起來比較單調,要設定出美觀的表格,真是耗時費工的苦差事。

改善Google文件中的表格格式

如果是在 Word 中就不一樣了,好多套色好的表格樣式可供選取:

改善Google文件中的表格格式

何不把這個工具配套在一起呢?你只要在 Word 中複製表格,直接在瀏覽器中的 Google 文件中貼上即可。Word 表格的格式會被帶到 Google 文件中,而你可以繼續在 Google 文件中使用其提供的工具列來編輯表格格式。

改善Google文件中的表格格式

透過在 Google 文件中複製儲存格,也可以建構一些漂亮的格式。

改善Google文件中的表格格式

如果怕手邊沒有 Word 可用也可以利用『網路剪貼簿功能』,將多個表格放在雲端硬碟的暫存空間。

改善Google文件中的表格格式

當你在文件中要使用時,再將其召喚出來。選取「編輯/網路剪貼簿」功能,再選取一個已儲存的表格(可預覽),並選取要以 RTF 或是 HTML 格式貼上。

改善Google文件中的表格格式

叫出表格後再修改。

改善Google文件中的表格格式

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

網友想要在一個含有日期和數值的 Excel 資料表中,根據一個起始日期找出指定天數內的最小值,該如何處/理?

如下圖,例如:根據日期 2014/08/26,要指出連續 5 筆內的最小值。

Excel-查詢指定日期最近幾筆的最小值(OFFSET,MATCH)


【公式設計與解析】

假設日期和數值清單的資料範圍是儲存格 A1:A2000。

儲存格F5:=MIN(OFFSET($B$1,MATCH(E2,$A$2:$A$2000,0),,$D$2,))

MATCH(E2,$A$2:$A$2000,0):傳回儲存格E2在儲存格範圍A2:A2000中的位置(傳回第幾個)。

OFFSET($B$1,MATCH(E2,$A$2:$A$2000,0),,$D$2,):根據上式(指定日期所在的位置),代入 OFFSET 函數,以儲存格B1為起點,查詢對應位置共 5 個的儲存格範圍。

在上式 OFFSET 函數的結果代入 MIN 函數中,即可傳回這個儲存格範圍中的最小值。

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

在 Excel 中你可能用過 SUMIFS 和 AVERAGEIFS 函數,可以運用多個條件計算總和或是平均值。但是沒有 MINIFS 或是 MINIFS 這類的相似函數,如何利用其他的公式來模擬這個結果呢?

例如,在下圖中,想要找出一個日期區間中的最大數值和最小數值。

Excel-找出一個日期區間中的最大值和最小值(陣列公式,模擬MAXIFS和MINIFS)

 

【公式設計與解析】

這類問題可以藉助『陣列公式』來處理。

為了讓公式更易於理解,先選取儲存格A1:B26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、數值。

(1) 求日期區間中的最大數值

儲存格E4:{=MAX(IF((日期>=E1)*(日期<=E2),數值,0))}

公式輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

(日期>=E1)*(日期<=E2):執行『*』運算,相當於執行這兩個條件的邏輯 AND 運算。

IF((日期>=E1)*(日期<=E2),數值,0):在陣列公式中當日期符合這兩個條件者,傳回對應的『數值』陣列,否則傳回『0』。(因為要求最大值,『0』只是給予一個很小的值)

再利用 MAX 函數對上式的傳回值取最大值。

 

(2) 求日期區間中的最小數值

同理:

儲存格E5:{=MIN(IF((日期>=E1)*(日期<=E2),數值,2^99))}

IF((日期>=E1)*(日期<=E2),數值,0):在陣列公式中當日期符合這兩個條件者,傳回對應的『數值』陣列,否則傳回『2^99』。(因為要求最小值,『2^99』只是給予一個很大的值)

再利用 MIN 函數對上式的傳回值取最小值。

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

1 2
找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼