贊助廠商

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

搜尋本部落格文章資料

目前分類:講義資料 (2058)

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

在 Google 地圖上可以輕鬆的查詢到各個地點的經度和緯度,但是如果是要查詢地點的高度,該如何處理?

目前看來 Google 地圖好像沒有提供高度的資訊,除了「地形」的圖層中有像等高線這類的資訊,但是這只能查詢到近似的高度。而你可以藉助 Google Earth 來查詢高度!

以網頁版的 Google Earht 為例來求取地點的高度資訊:

網頁:https://earth.google.com/web/

以下圖為例:將滑鼠移至你想要知道高度的地點(下圖游標處),再觀察視窗右下角狀態列的資訊,即會看到所顯示該地圖的高度。

Google Earth-在地圖上查詢地點的高度

上下二圖比較,可以知道石門水庫的大霸二端是那邊高、那邊低了。

Google Earth-在地圖上查詢地點的高度

因此,很容易就可以得知擎天崗大草原(停車場附近)的海拔高度了。顯示:769公尺。

Google Earth-在地圖上查詢地點的高度

對照陽明山國家公園的擎天崗介紹(http://www.ymsnp.gov.tw/index.php?option=com_tourmap&view=tourmap&id=7&gp=0&Itemid=128),其中提到「海拔高度僅約770公尺的擎天崗」,看來是不謀而合的。

Google Earth-在地圖上查詢地點的高度

文章標籤

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

在 Excel 的工作表中,如何在二個不連續的儲存格範圍中使用 VLOOKUP 函數來查詢?

參考下圖,如果想要在儲存格A2:B11和A15:B26中查詢某一個人員的數值。(人員在二個不連續範圍中不會重覆)

image

 

【公式設計與解析】

因為 VLOOKUP 函數查詢時,必須在連續範圍裡,所以在二個儲存格範圍中要使用二個VLOOKUP 函數來查詢。

儲存格E2:=IFERROR(VLOOKUP(D2,A2:B11,2,FALSE),"")&IFERROR(VLOOKUP
(D2,A15:B26,2,FALSE),"")

(1) VLOOKUP(D2,A2:B11,2,FALSE)

利用 VLOOKUP 函數查詢儲存格D2在儲存格範圍A12:B11中的「數值」。

若儲存格D2並沒有在儲存格範圍A12:B11中,則會傳回錯誤訊息。

(2) IFERROR(VLOOKUP(D2,A2:B11,2,FALSE),"")

將第(1)式傳回的錯誤以 IFERROR 函數轉換為空字串「""」(空白)。

(3) VLOOKUP(D2,A15:B26,2,FALSE)

利用 VLOOKUP 函數查詢儲存格D2在儲存格範圍A15:B26中的「數值」。

若儲存格D2並沒有在儲存格範圍A15:B26中,則會傳回錯誤訊息。

(4) IFERROR(VLOOKUP(D2,A15:B26,2,FALSE),"")

將第(3)式傳回的錯誤以 IFERROR 函數轉換為空字串「""」(空白)。

(5) 第(2)式&第(4)式

因為第(2)式和第(4)式中,只有一個式子會傳回數值,另一個式子會傳回空字串(空白)。所以利用「&」予以串接。

文章標籤

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

參考下圖,在 Excel 中有一個資料表,如何將相同地址的姓名集合在一起?

Excel-將資料表中相同地址的姓名集合在一起(OFFSET,SMALL,COLUMN)

 

【公式設計與解析】

首先,選取儲存格A1:B27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、地址。

接著,輸入公式:

儲存格E2:{=IFERROR(OFFSET($A$1,SMALL(IF(地址=$D2,ROW(姓名),""),
COLUMN(A:A))-1,0),"")}

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

複製儲存格E2,貼至儲存格E2:K7。

(1) IF(地址=$D2,ROW(姓名),"")

在陣列公式中,判斷址址陣列中是否和儲存格D2(地址A)相同,若是,則傳回對應姓名儲存格的列號:若否,則傳回空字串("")。

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

利用 SMALL 函數,由小至大取出對應的數值(列號)。當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→....。

(3) OFFSET($A$1,第(2)式-1,0)

將第(2)式的結果帶入 OFFSET 函數,取得對應的儲存格內容(姓名)。

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

若公式傳回錯誤訊息,則以 IFERROR 函數改顯示空字串(空白)。

文章標籤

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

最近 Google 新增了 Google 試算表的功能:核取方塊,要如何計算核取方塊被勾選的數量?

要使用核取方塊,則先選取儲存格,再選取「插入/核取方塊」功能。

計算Google試算表中核取方塊勾選的數量

參考下圖,新增的核取方塊預設為「未選取」,其值為FALSE,若是核取方塊被選取,則值為TRUE。要如何計算下圖中每一欄有幾個核取方塊被選取。

計算Google試算表中核取方塊勾選的數量

例如,在儲存格A18中,輸入公式:

(1)=ArrayFormula(SUM(A2:A17*1))

(2) =SUMPRODUCT(A2:A17*1)

計算Google試算表中核取方塊勾選的數量

將儲存格A18複製到儲存格A18:J18。

 

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

在 Excel 中,你會使用 VLOOKUP 函數來執行查詢的工作,但是如果是要查詢多個條件時,VLOOKUP 函數無法滿足這個需求,該如何處理?

以下圖為例,如果要以雙條件在下圖左的資料表中查詢,如何設計公式?

Excel-多條件查詢(SUMPRODUCT,OFFSET)

 

【公式設計與解析】

選取儲存格A1:F14,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:單號、序號、料號、數量、進貨單號、倉庫。

輸入公式,儲存格H8:

=OFFSET($F$1,SUMPRODUCT((單號=H2)*(料號=H4)*ROW(倉庫))-1,0)

(1) SUMPRODUCT((單號=H2)*(料號=H4)*ROW(倉庫))

在 SUMPRODUCT 函數使用雙條件:

條件一:單號=H2:單號和儲存格H2相同者

條件二:料號=H4:料號和儲存格H4相同者

在 SUMPRODUCT 函數中,(單號=H2)*(料號=H4)*ROW(倉庫)會傳回合於條件者的列號。

(2) OFFSET($F$1,第(1)式-1,0)

將第(1)式傳回的列號代入 OFFSET 函數,取得對應的儲存格內容。

如果你要增加條件,則在 SUMPRODUCT 函數中擴增條件,其餘不用變更。

文章標籤

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

參考下圖,在 Excel 中如何根據到期日距今日的日數:30天以內、60天以內、90天以內,給予不同的儲存格色彩?

很明顯的可以利用設定格式化的條件來處理!

Excel-設定多個條件的格式設定

參考以下步驟:

1. 選取儲存格A2:C23。

2. 在設定格式化的條件規則理員中設定:

規則類型:使用公式來決定要格式化哪些儲存格

編輯規則:=$B2-TODAY()<=90

設定格式:儲存格底色為青色

Excel-設定多個條件的格式設定

重覆相同步驟,設定不同規則

規則:=$B2-TODAY()<=60;格式:儲存格底色為藍色

規則:=$B2-TODAY()<=30;格式:儲存格底色為澄色

設定完成的結果如下:

Excel-設定多個條件的格式設定

如果你做出錯誤結果,有可能是規則的順序弄錯了。(如下錯誤結果)

Excel-設定多個條件的格式設定

你可以調整規則順序(上移/下移):

Excel-設定多個條件的格式設定

文章標籤

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

延續前一篇文章:將WorkFlowy的內容轉換至XMind

註冊 WorkFlowy:https://workflowy.com/

如果要將 WorkFlowy 的內容輸出至Word 文件中並且編上號碼,該如何處理?

將WorkFlowy的內容轉換至Word,並設定清單編號

首先將 WorkFlowy 的內容匯出至 Pain text 格式,會產生一個文字檔。

將WorkFlowy的內容轉換至Word,並設定清單編號

在 Word 中先建好多層次清單和樣式,可參考:(如下圖)

Word-多層次清單設定

Word-建立長文件之多層次清單

將WorkFlowy的內容轉換至Word,並設定清單編號

清單內容設定為:

將WorkFlowy的內容轉換至Word,並設定清單編號。

建立的 9 層樣式:(Word中的階層只有 9 層)

將WorkFlowy的內容轉換至Word,並設定清單編號

將 WorkFlowy 匯出的文字檔內容置入 Word 文件中:

將WorkFlowy的內容轉換至Word,並設定清單編號

假設本範例為 7 層的結構。

接著執行以下設定:(○代表一個空格)

尋找目標:○○○○○○○○○○○○○○-○(14個空格-空格)

取代為:○/樣式:第7層。

將WorkFlowy的內容轉換至Word,並設定清單編號

選取「格式/樣式」,再選取:第 7 層。

將WorkFlowy的內容轉換至Word,並設定清單編號

設定如下:

將WorkFlowy的內容轉換至Word,並設定清單編號

全部取代後的結果如下:

將WorkFlowy的內容轉換至Word,並設定清單編號

接著,仿前面步驟:

尋找目標:○○○○○○○○○○○○-○(12個空格-空格)

取代為:○/樣式:第6層。

尋找目標:○○-○(2個空格-空格)

取代為:○/樣式:第1層。

最後執行:

尋找目標:-○(2個空格-空格)

取代為:空白。

全部取代完的結果如下:

將WorkFlowy的內容轉換至Word,並設定清單編號

也可以再透過大綱模式進一步處理:

將WorkFlowy的內容轉換至Word,並設定清單編號

註冊 WorkFlowy:https://workflowy.com/​​​​​​​ ​​​​​​​

文章標籤

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

根據這篇:

Excel-在多個日期的時間清單中找出每日最早和最晚時間(MIN,MAX,陣列公式)

如果要延伸找到最早的人員和最晚的人員,該如何處理。

Excel-在多個日期的時間清單中找出每日最早和最晚時間所對應的人員(SUMPRODUCT,OFFSET,陣列公式)

 

【公式設計】

1. 求取最早和早晚的時間

參考:Excel-在多個日期的時間清單中找出每日最早和最晚時間(MIN,MAX,陣列公式)

儲存格F2:{=MIN(IF((日期=E2),打卡,""))}

儲存格H2:{=MAX(IF((日期=E2),打卡,""))}

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

 

2. 求取最早和早晚的時間對應的人員

先來求取最早時間對應的人員

儲存格G2:{=OFFSET($B$1,SUMPRODUCT((日期=E2)*(打卡=MIN(IF((日期=E2),
打卡,"")))*ROW(人員))-1,0)}

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

(1) MIN(IF((日期=E2),打卡,""))

在陣列公式中找到對應日期最早的時間。

(2) SUMPRODUCT((日期=E2)*(打卡=第(1)式)*ROW(人員))

SUMPRODUCT 函數中利用二個條件找尋完全相符者的列號:

日期=E2:找尋日期陣列中和儲存格E2相符者。

打卡=第(1)式:找尋打卡時間陣列中和第(1)式傳回值相同者。

SUMPRODUCT 函數中執行:((日期=E2)*(打卡=第(1)式)*ROW(人員),可以傳回符合者列號的和。(本例預設只會傳回一個符合的列號。若有二個打卡時間都是最早的,該公式會產生錯誤。)

(3) OFFSET($B$1,第(2)式-1,0)

利用 OFFSET 函數依第(2)傳回的列號求取符合的儲存格內容。

同理:

儲存格I2:{=OFFSET($B$1,SUMPRODUCT((日期=E2)*(打卡=MAX(IF((日期=E2),
打卡,"")))*ROW(人員))-1,0)}

文章標籤

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

WorkFlowy 和 XMind 都是我常用且好用的工具,要如何將 WorkFlowy 的內容轉換至 XMind 中?

註冊 WorkFlowy:https://workflowy.com/

XMind有多種的格式可以輸出,例如:組織圖、樹狀圖、邏輯圖、時間軸、魚骨圖、表格圖等,可以用來補 WorkFlowy 的單一型式圖形。

將WorkFlowy的內容轉換至XMind

請參考以下步驟來執行轉換工作:

下圖是建好的 WorkFlowy 內容:

將WorkFlowy的內容轉換至XMind

當你選取 Export All,再選取:Plain text,再按 click to download。接著會產生一個 TXT 檔。

將WorkFlowy的內容轉換至XMind

將此文字檔的內容送至 Word 文件中開啟。觀察其文字格式:(每一階層都以兩個空格格開,最後再接「_ 」)

將WorkFlowy的內容轉換至XMind

我們要將每二個空格以「^t」(定位格)取代:

將WorkFlowy的內容轉換至XMind

依序執行以下五個取代步驟:(本例只有五層結構)

將「OOOOOOOOOO-O」置換成「^t^t^t^t^t」

將「OOOOOOOO-O」置換成「^t^t^t^t」

將「OOOOOO-O」置換成「^t^t^t」

將「OOOO-O」置換成「t^t」

將「OO-O」置換成「^t」

結果如下圖:

將WorkFlowy的內容轉換至XMind

選取並複製轉換後的結果(第一列的文字除外),至 XMind 的中心主題上按右鍵,再選取「貼上」。

將WorkFlowy的內容轉換至XMind

大功告成!已經轉換為心智圖了!

將WorkFlowy的內容轉換至XMind

你可以選取想要的格式:

將WorkFlowy的內容轉換至XMind

註冊 WorkFlowy:https://workflowy.com/​​​​​​​

 

文章標籤

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

在學校裡常見的學生名條,通常是直式並依座號由上而下排列(如下圖左)。如果想要排成如下圖的四種不同排列方式,該如何利用Excel來完成。

Excel-將學生名條轉換為座位表(OFFSET,INT,ROW,COLUMN)

本篇將善用 ROW 和 COLUMN 函數,其中:

ROW(1:1)向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

COLUMN(A:A)向下複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→…。

 

1. 座號由左至右→由上而下

Excel-將學生名條轉換為座位表(OFFSET,INT,ROW,COLUMN)

公式設計:

儲存格D2:=OFFSET($A$1,(ROW(1:1)-1)/2*6+COLUMN(A:A),0)

儲存格D3:=OFFSET($B$1,(ROW(1:1)-1)/2*6+COLUMN(A:A),0)

複製儲存格D2:D3,貼至儲存格D2:I15。

 

2. 座號由上至下→由左而右

Excel-將學生名條轉換為座位表(OFFSET,INT,ROW,COLUMN)

公式設計:

儲存格D2:=OFFSET($A$1,INT(((ROW(1:1)-1)/2))+(COLUMN(A:A)-1)*7+1,0)

儲存格D3:=OFFSET($B$1,INT(((ROW(1:1)-1)/2))+(COLUMN(A:A)-1)*7+1,0)

複製儲存格D2:D3,貼至儲存格D2:I15。

 

3. 座號由右至左→由上而下

Excel-將學生名條轉換為座位表(OFFSET,INT,ROW,COLUMN)

公式設計:

儲存格D2:=OFFSET($A$1,(ROW(1:1)-1)/2*6+(6-COLUMN(A:A))+1,0)

儲存格D3:=OFFSET($B$1,(ROW(1:1)-1)/2*6+(6-COLUMN(A:A))+1,0)

複製儲存格D2:D3,貼至儲存格D2:I15。

 

4. 座號由上至下→由右而左

Excel-將學生名條轉換為座位表(OFFSET,INT,ROW,COLUMN)

公式設計:

儲存格D2:=OFFSET($A$1,INT(((ROW(1:1)-1)/2))+(6-COLUMN(A:A))*7+1,0)

儲存格D3:=OFFSET($B$1,INT(((ROW(1:1)-1)/2))+(6-COLUMN(A:A))*7+1,0)

複製儲存格D2:D3,貼至儲存格D2:I15。

文章標籤

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

當你在編輯 Word 文件、Excel設算表,如果想在版面的頁首或頁尾加上檔案路徑,以方便日後找尋檔案,該如何處理?

1. Word文件

請先進入頁首/頁尾的編輯狀態中,再選取「設計/插入」功能表中的「文件資訊/檔案路徑」。下圖中在頁首會插入該文件的檔案路徑,這是一個功能變數,會隨著檔案被儲存於不同位置,而有不同的路徑。

在Word文件和Excel試算表的頁首或頁尾中插入檔案路徑

你也可以選取「插入/快速組件」中的「功能變數」:

在Word文件和Excel試算表的頁首或頁尾中插入檔案路徑

再選取功能變數:FileName,並且勾選「將徑加到檔名」。

在Word文件和Excel試算表的頁首或頁尾中插入檔案路徑

 

2. Excel試算表

在「版面設定」中選取「頁首/頁尾」,再於下拉式清單中選取路徑+檔名。

在Word文件和Excel試算表的頁首或頁尾中插入檔案路徑

或是在「版面設定」中選取「頁首/頁尾」,並選取自訂頁首或是自訂頁尾:

在Word文件和Excel試算表的頁首或頁尾中插入檔案路徑

接著選取「插入檔案路徑」:(自動插入「&[路徑]&[檔案]」,這是一個功能變數,會隨著檔案被儲存於不同位置,而有不同的路徑。)

在Word文件和Excel試算表的頁首或頁尾中插入檔案路徑

文章標籤

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

學習者在學習過程中,對於文字和語言的轉換十分重要,微軟利用人工智慧來做翻譯工作。如果你想在使用 Word、PowerPoint、Outlook 時使用語音來輸入文字,則可以下載微軟提供的擴充程式:

下載網址:https://dictate.ms/

下載適合你的 Office 版本(32位元或是64位元)

在Word和PowerPoint文件中利用語音輸入文字及翻譯整個簡報中的每張投影片

安裝完成後,在 Word 中就會多了一個工具:Dictation

切換至這個工具列,插上你的耳麥,在 From 中選取你要說的語言,在 To 中選取你要翻譯的語言。

按下 Start,開始講話,在 Word 文件中就會即產生你指定(To)的語言文字。

在Word和PowerPoint文件中利用語音輸入文字及翻譯整個簡報中的每張投影片

雖然我選了 Chinese Traditional,但是目前出來的還是簡體字。

在Word和PowerPoint文件中利用語音輸入文字及翻譯整個簡報中的每張投影片

還好,利用 Word 中的「簡轉繁」功能即可轉為繁體字。

在Word和PowerPoint文件中利用語音輸入文字及翻譯整個簡報中的每張投影片

在 PowerPoint 和 Outlook 中的使用方式是相同的。

在Word和PowerPoint文件中利用語音輸入文字及翻譯整個簡報中的每張投影片

在Word和PowerPoint文件中利用語音輸入文字及翻譯整個簡報中的每張投影片

在 Dictation 中也提供了幾個口語命令:

New line: Takes cursor to new line

Delete: Removes the last line you dictated

Stop dictation: Terminates the dictation session

Full stop or period: Types period character (.)

Question mark: Types (?)

Open quote: Types (“)

Close quote: Types (”)

Colon: Types (:)

Comma: Types (,)

 

另外,微軟也提供了可以將 PowerPoint 中的每張投影片直接翻譯的外掛程式。

網頁:https://translator.microsoft.com/help/presentation-translator/

在Word和PowerPoint文件中利用語音輸入文字及翻譯整個簡報中的每張投影片

下載網頁:

https://www.microsoft.com/en-us/download/details.aspx?id=55024

下載並安裝PresentationTranslator.exe

過程中可能你必須安裝部分的配合程式:

在Word和PowerPoint文件中利用語音輸入文字及翻譯整個簡報中的每張投影片

在Word和PowerPoint文件中利用語音輸入文字及翻譯整個簡報中的每張投影片

安裝完成後,在「投影片放映」功能表下,可以選取「翻譯投影片」:

在Word和PowerPoint文件中利用語音輸入文字及翻譯整個簡報中的每張投影片

指定投影片的語言和要翻譯的語言,即可進行翻譯投影片:

在Word和PowerPoint文件中利用語音輸入文字及翻譯整個簡報中的每張投影片

翻譯完成的投影片會產生另一個檔案,並以原檔名+「_en」命名。

在Word和PowerPoint文件中利用語音輸入文字及翻譯整個簡報中的每張投影片

註:翻譯的結果目前應該都是不完美的,但根據AI的發展,將來應該可以愈來愈好。

文章標籤

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

有時你取得的表格是 XML 格式,如何才能看到表格內容?

何謂XML格式,請自行參考:https://zh.wikipedia.org/wiki/XML

假設要以 Excel 來看檔案內容,以下圖為例:(將XML檔由記事本開啟)

Excel-如何開啟XML格式的檔案

參考以下的做法:

首先,要啟用「開發人員」工具列。

選取「檔案/選項」,在「自訂功能區」勾選「開發人員」:

Excel-如何開啟XML格式的檔案

此時,會增加「開發人員」功能表,其中有一個「XML」區,點選其中的「匯入」:

Excel-如何開啟XML格式的檔案

你有可能收到如下的訊息:(按「確定」按鈕)

Excel-如何開啟XML格式的檔案

接著指定匯入的資料要放在那一個儲存格,如果按一下「內容」按鈕,則可以做一些細部設定:

Excel-如何開啟XML格式的檔案

Excel-如何開啟XML格式的檔案

這個 XML 格式的檔案滙入後,內容如下:

Excel-如何開啟XML格式的檔案

文章標籤

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

在 Excel 中的一個工作表(如下圖),如何根據指定的項目名稱來計算合乎的品名之加總領用量。

例如:罐子就有多種的樣式,也就是品名中含有『項目』的內容,合於項目的要挑出來予以加總。

Excel-計算名稱中含有關鍵者的總和(SUMPRODUCT,SUBSTITUTE)

 

【公式設計與解析】

先將儲存格範圍定義名稱。選取儲存格A1:B12,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:品名、領用量。

儲存格E2:=SUMPRODUCT((SUBSTITUTE(品名,D2,"")<>品名)*領用量)

(1) SUBSTITUTE(品名,D2,"")<>品名

SUMPRODUCT 函數中,利用 SUBSTITUTE(品名,D2,"") 將每品名的名稱,以空白取笩儲存格D2。若結果和原來品名不相同,則表示該品名中含有儲存格D2的內容。其結果會傳回 TRUE/FALSE 陣列。

(2) (SUBSTITUTE(品名,D2,"")<>品名)*領用量

將第(1)式傳回的 TRUE/FALSE 陣列乘以『領用量』,於 SUMPRODUCT 函數中將讓式的結果予以加總。

文章標籤

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

在 Excel 中的工作表有許多公式(參考下圖),如果想將公式中的「*30%」全部置換為「*40%」,該如何處理?

因為,Excel 接受萬用字元(*和?),但是使用時要在字元前面加上「~」。

所以,本例可將尋找目標設定為:~*30%,取代成設定為:*40%。

再執行「全部取代」即可。

Excel-取代公式中含有*字元的字串

文章標籤

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

如下圖,在 Excel 中如果想要:求year>10且month>21且code>16之data總和,而data的內容可能含有文字,該如何處理才能避免傳回錯誤訊息並且正確運算?

Excel-計算加總時避開文字發生的錯誤(IFERROR)

 

【公式設計與解析】

選取儲存格A3:D19,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:year、month、code、data。

公式一:{=SUM((year>10)*(month>21)*(code>16)*data)}

當你使用這個陣列公式,當data資料範圍中輸入了一個以上的文字,其結果會傳回錯誤訊息。該如何改善?

公式二:{=SUM((year>10)*(month>21)*(code>16)*IFERROR(data*1,0))}

該公式中使用:IFERROR(data*1,0)

當 data 資料陣列在執行「*1」運算時,若儲存格內容是文字,其會傳回錯誤訊息,利用 IFERROR 函數將其轉換為 0,再由 SUM 函數加總。就不會傳回錯誤訊息!

文章標籤

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

如果你有多個 Word 文件檔,假設每一個有各自的頁首與頁尾,當你把這三個檔案合併成一個時,只會保留第一個文件的頁首和頁尾。該如何在合併檔案時,保留檔案原始的頁首和頁尾?(參考下圖,以三個文件檔為例。)

Word-合併多個文件檔時保留原文件各自的頁首和頁尾

可以參考如下做法:

在第一個文件的最後(即要插入第 2 的文件的位置),選取「版面配置/分隔符號」,再選取「分節符號/下一頁」。(用以在新的一頁開始新的一節)

然後在新的一頁,選取「插入/文字檔」,再選取要插入的檔案。循環該步驟,將要連接的文件依此方法插入。

Word-合併多個文件檔時保留原文件各自的頁首和頁尾

本例中三個檔案的頁首和頁尾都是各自保留。

Word-合併多個文件檔時保留原文件各自的頁首和頁尾

文章標籤

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

好像被問過很多次!在 Excel 中,民國年如何轉換為西洋年?

參考下圖,民國年格式為104/11/24,轉換為西元年格式為2015/11/24。

Excel-民國年轉西元年(SUBSTITUTE,REPT,DATE,LEFT,MID,RIGHT)

 

【公式設計與解析】

為了說明方便,使用B欄做為輔助欄位。

儲存格B2:=SUBSTITUTE(A2,"/",REPT(" ",20))

利用 SUBSTITUTE 函數將「/」以20個空白字元取代,結果請觀察B欄。

Excel-民國年轉西元年(SUBSTITUTE,REPT,DATE,LEFT,MID,RIGHT)

儲存格C2:=DATE(LEFT(B2,3)+1911,MID(B2,20,20),RIGHT(B2,2))

(1) LEFT(B2,3)+1911:利用 LEFT 函數取出『年』的數值。(年的數值不超過3碼)

(2) MID(B2,20,20):利用 MID 函數取出『月』的數值。(取20碼必含有月的數值)

(3) RIGHT(B2,2):利用 RIGHT 函數取出『日』的數值。(日的數值不超過2碼)

複製儲存格B2:C2,貼至儲存格B2:C27。

若將以上二式合成一個公式,如下:

儲存格C2:

=DATE(LEFT(SUBSTITUTE(A2,"/",REPT(" ",20)),3)+1911,MID(SUBSTITUTE(A2,
"/",REPT(" ",20)),20,20),RIGHT(SUBSTITUTE(A2,"/",REPT(" ",20)),2))

文章標籤

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

延續前二篇教學:

Excel-根據時間中的分決定是否進位(FLOOR,CEILING)

Excel-根據時間中的分決定是否進位(CHOOSE,INT,HOUR)

參考下圖,依據數值的「小數」調整進位:

小數=0.5:維持0.5

小數<0.5:捨去為0

小數>0.5:進位為0

Excel-依條件決定是否進位(MROUND,FLOOR,CEILING)

 

【公式設計與解析】

儲存格C2:=IF(A2-INT(A2)=0.5,MROUND(A2,0.5),IF(A2-INT(A2)>0.5,
CEILING(A2,0.5),FLOOR(A2,0.5)))

(1) A2-INT(A2):A2-INT(A2)用以取得小數的數值部分。

(2) MROUND(A2,0.5):四捨五入至最近的0.5的倍數

(3) CEILING(A2,0.5):進位至最近的0.5的倍數

(4) FLOOR(A2,0.5))):捨去至最近的0.5的倍數

文章標籤

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

根據前一篇教學:Excel-根據時間中的分決定是否進位(CHOOSE,INT,HOUR),如果使用 FLOOR函數和 CEILING 函數,該如何處理?

Excel-根據時間中的分決定是否進位(FLOOR,CEILING)

 

【公式設計與解析】

儲存格E2:

=CHOOSE(INT(MINUTE(A2)/15)+1,FLOOR(A2,1/24/2),CEILING(A2,1/24/2),
FLOOR(A2,1/24/2),CEILING(A2,1/24/2))

複製儲存格E2,貼至儲存格E2:E17。

FLOOR(A2,1/24/2):儲存格A2的時間無條件捨去至最接近的1/24/2的倍數,即30分鐘的倍數。

CEILING(A2,1/24/2):儲存格A2的時間進位至1/24/2的倍數,即30分鐘的倍數。

 

【FLOOR 函數說明】

FLOOR 函數:將 number 無條件捨位至趨近於零,到最接近的基數倍數。

語法: FLOOR(number, significance)

Number:這是要捨位的數值。

Significance:這是要捨位的倍數。

 

【CEILING 函數說明】

CEILING 函數:傳回進位後的數字,背離於零,到最接近之指定基數的倍數。

語法: CEILING(number, significance)

Number:這是要捨位的數值。

Significance:這是要捨位的倍數。

文章標籤

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼