2014年6月27日 星期五

Excel-計算最近幾天的平均(OFFSET)

有網友想要知道在下圖中的 Excel 資料清單,如何計算前幾天的平均分別為多少?

本例使用 OFFSET 函數來取得相對某一儲存格的儲存格範圍,例如:

儲存格C5:=AVERAGE(OFFSET($B$3,0,0,1,ROW(1:1)))

複製儲存格C5,貼至儲存格C5:C14。

OFFSET($B$3,0,0,1,ROW(1:1)):表示儲存格B3:B3

向下複製/貼上時產生:

OFFSET($B$3,0,0,1,ROW(2:2)):表示儲存格B3:C3

OFFSET($B$3,0,0,1,ROW(3:3)):表示儲存格B3:D3

OFFSET($B$3,0,0,1,ROW(3:3)):表示儲存格B3:K3

再置入 AVERAGE 函數求得平均。

2014年6月25日 星期三

Excel-計算平均時不包含空白儲存格(SUMPRODUCT,ISBLANK)

有網友問到如下圖左的 Excel 資料清單中,如果要將編號為奇數/偶數的項目,分別取出計算其平均,該如何處理?其中有部分儲存格的內容為空白。

計算平均時,如果儲存格的內容為空白,理應不併入計算。而 AVERAGE 函數,也是會將儲存格為空白儲存格者不列入平均。

先選取儲存格A1:B20,按一下 Ctrl+Shfit+F3 鍵,定義名稱:編號、數值。

【錯誤結果】

儲存格E2:=SUMPRODUCT((MOD(編號,2)=1)*數值)/SUMPRODUCT(--(MOD(編號,2)=1))

MOD(編號,2)=1:判斷編號除以 2 的餘數是否為 1 (該數為奇數),得到一個 TRUE/FALSE 的陣列。

SUMPRODUCT((MOD(編號,2)=1)*數值):計算編號為奇數者的數值總和。

SUMPRODUCT(--(MOD(編號,2)=1)):藉由「--」運算,將計算結果為 TRUE/FALSE 的陣列轉換為 1/0 的陣列。

儲存格E3:=SUMPRODUCT((MOD(編號,2)=0)*數值)/SUMPRODUCT(--(MOD(編號,2)=0))

MOD(編號,2)=0:判斷編號除以 2 的餘數是否為 0 (該數為偶數),得到一個 TRUE/FALSE 的陣列。

但是以上的公式,會將儲存格B4和儲存格B13這二個空白儲存格的內容視為 0,在計算平均值時會產生錯誤。

 

【正確結果】

為了將空白儲存格不在計算平均時併入計算,將公式稍做修改:

儲存格E4:=SUMPRODUCT((MOD(編號,2)=1)*數值)/SUMPRODUCT((NOT(ISBLANK(數值)))*(MOD(編號,2)=1))

NOT(ISBLANK(數值)):透過 ISBLANK 函數來判斷數值陣列中是否為空白儲存格,得到一個 TRUE/FALSE 陣列,再藉由 NOT 函數將結果轉換為 FALSE/TRUE 陣列。

儲存格E5:=SUMPRODUCT((MOD(編號,2)=0)*數值)/SUMPRODUCT((NOT(ISBLANK(數值)))*(MOD(編號,2)=0))

2014年6月24日 星期二

Excel-關於DATEDIF函數

有網友根據這篇:Excel-計算實際年齡(年月日)-DATEDIF文章,提出了一個問題:

如果在 DATEDIF 函數中,使用參數:md(忽略年月,計算天數),當起始日為 2014/7/28,結束日分別是 2014/10/9 與 2014/11/9,結果分別是11和12,為何會有這樣的差異呢?

關於 DATEDIF 函數的語法說明如下:

DATEDIF

語法:DATEDIF(start_date,end_date,unit)

Unit說明

"Y":週期中的整年數

"M":週期中的整月數

"D":週期中的天數

"MD"start_dateend_date間的天數差。(忽略日期中的月和年)

"YM"start_dateend_date間的月數差。(忽略日期中的日和年)

"YD"start_dateend_date間的天數差。(忽略日期中的年)

 

依照我的推估(參考下圖),其天數差的計算可能是如下的運作:

以 1 月的 28 日和每個月的 9 日來計算天數差為例,則是先找出「迄日前一個月的28日距最後一天的天數,再加上迄日當月的天數(9)。

所以不同月份的迄日,將會在使用「md」參數時,會得到不同的天數差。

2014年6月23日 星期一

Excel-依勾選項目予以加總(SUMPRODUCT)

有網友問到:如下圖的資料表,如何將各欄位中有「V」勾選的項目,將第一欄予以加總小計?例如:現貨的小計為 20000+5000+600+1000 = 26600。

這是一個很典型的 SUMPRODUCT 函數的應用,函數的基本語法:

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

 

【輸入公式】

儲存格B7:=SUMPRODUCT($A$2:$A$6,(B2:B6="V")*1)

B2:B6="V":判斷儲存格範圍中的內容是否為「V」,得列一個 TRUE/FALSE 的陣列。

image

(B2:B6="V")*1:利用「*1」運算,將 TRUE/FALSE 陣列轉換成 1/0 陣列。

image

再透過 SUMPRODUCT 函數運算:

以「現貨」欄位為例:20000X1 + 5000X1 + 600X1 + 500X0 + 1000X1 = 26600。

2014年6月19日 星期四

OneNote-輸出筆記成為 Word、PDF、XPS檔

有同仁想要將在 OneNote 中新增的記事,變成一個可攜式的檔案,該如何處理呢?

雖然 OneNote 是可以同步到雲端,但是有時不方便使用雲端資源,或是想要機器未安裝 OneNote 軟體無法開啟筆記,或是要將筆記移轉給他人使用時,都會遇到必須將記事轉成一個可攜式檔案。

OneNote 可以使用匯出功能來產生檔案,當我們完成一個筆記之後:

選取[檔案/匯出]選項,就可選取匯出的範圍:頁面、節、筆記本,本例選取「頁面」。

接著要選取格式,可以輸出為 OneNote 格式、Word 格式、PDF 格式、XPS格式或是網頁檔(mht),本例選取 PDF 格式。

按一下[匯出]按鈕。

匯出檔案後,即可以移轉至其他位置讀取。

2014年6月17日 星期二

Excel-間隔固定列數複製資料(OFFSET,INT)

有網友問到:如何將一個資料來源清單,複製到另一個資料清單中,而且每個資料要間隔二列。(參考下圖)

(1) 假設下圖C欄中的資料,每二列有一個「空白」儲存格。

參考以下的做法:

1. 選取C欄中的資料的儲存格。

2. 按一下 Ctrl+G 鍵,開啟[到]對話框。

3. 按一下[特殊]按鈕。

4. 在[特殊目標]對話框中選取「空格」選項,按一下[確定]按鈕。

目前的狀態是每間隔二列的C欄中的儲存格已被選取:

5. 輸入公式:=OFFSET($A$2,INT((ROW(1:1)-1)/3),,,),按一下 Ctrl+Enter 鍵。

INT((ROW(1:1)-1)/3):當向下複製公式時,會產生 0,0,0,1,1,1,2,2, …。

透過 OFFSET 函數,可以取得 甲,甲,甲,乙,乙,乙,丙,丙,丙, …。因為每間隔二列只有一個儲存格被選取,所以結果如下,空格已被填入A欄的內容。

(2) 假設下圖C欄中的資料,每個儲存格已有資料。

如果儲存格中已有資料,則只能使用一個輔助欄位來重組資料了。

儲存格E2:

=IF(INT((ROW(1:1)-1)/3)=(ROW(1:1)-1)/3,OFFSET($A$2,INT((ROW(1:1)-1)/3),,,),C2)

INT((ROW(1:1)-1)/3)=(ROW(1:1)-1)/3:當向下複製時會產生 TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, …。

2014年6月16日 星期一

Excel-只計算日期清單中本週的金額(SUMPRODUCT,WEEKDAY)

有網友問到:如何在一個 Excel 的日期清單中,只計算本週的金額小計?

【準備工作】

選取A欄至C欄中要輸入資料的儲存格,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、星期、金額。

【輸入公式】

(1) 計算本週的第一天(星期日)

公式:=TODAY()-WEEKDAY(TODAY(),2)

(2) 計算本週的第六天(星期五)

公式=TODAY()+5-WEEKDAY(TODAY(),2)

(3) 計算本週內星期日至星期五的金額小計

儲存格E5:=SUMPRODUCT((日期>=TODAY()-WEEKDAY(TODAY(),2))*(日期<=TODAY()+5-WEEKDAY(TODAY(),2))*金額)

日期>=TODAY()-WEEKDAY(TODAY(),2):判斷日期是否大於本週第一天,傳回 TRUE/FALSE 的陣列。

日期<=TODAY()+5-WEEKDAY(TODAY(),2):判斷日期是否小於本週第六天,傳回 TRUE/FALSE 的陣列。

公式中的「*」運算子,相當於執行邏輯 AND 運算。

2014年6月15日 星期日

Word-隱藏檔案開啟時的連結要求/找出文件連結的檔案

有同仁不知道自己在 Word 文件中執行了什麼動作,導致開啟這個文件時,出現了如下圖的提示訊息,主要是說明此文件可能參照到其他檔案的連結,詢問是否要使用連結檔案的資料來更新此文件。你可以選取「是」或「否」來決定要不要連結更新。

通常你可能不知道這個訊息的形成原因,或是這個文件不是你製作的,所以也無從得知連結至那些檔案。

依據 Word 的說明文件,其描述可能發生的原因:

●使用 [選擇性貼上] 命令 (位於 [常用] 索引標籤的 [剪貼簿] 群組,按一下 [貼上] 下方的箭頭,再按一下 [選擇性貼上]),然後按一下 [貼上連結] 選項。

●使用 [物件] 命令 (在 [插入] 索引標籤的 [文字] 群組,按一下 [物件],再按一下 [物件]),然後核取 [檔案來源] 索引標籤中的 [連結至檔案] 選項。

●使用 [插入圖片] 對話方塊中的 [連結至檔案] 或 [插入與連結] 命令 (位於 [插入] 索引標籤),在 [圖例群組],按一下 圖片,然後按一下 [插入)] 旁邊的箭號。

如果想要控制 Word 在檔案開啟時自動更新連結,可以選取[檔案/選項]功能中的「進階」標籤。在 [一般] 區域下,取消選取「開啟舊檔時自動更新連結」核取方塊,如此再次開啟這個文件時,就不會再出現提示訊息。(原來的檔案的連結功能依然存在)

如果你真的想要找出檔案連結的位置,則可以試試顯示「功能變數」:

1. 若要顯示或隱藏特定功能變數的功能變數,則在功能變數結果上按 Shfit+F9 鍵。

2. 若要顯示或隱藏文件中所有欄位的功能變數,則按 Alt+F9 鍵。

如此,便可以試著找到檔案的連結位置,再進行進一步的處理。

2014年6月14日 星期六

Excel-取出儲存格中的數字加總(SUMPRODUCT)

有網友問到:參考下圖的金額清單中,因為前三碼是幣別,如果要取出金額的數字部分,並且給予小計加總,該如何處理?

【輸入公式】

儲存格D2:=SUMPRODUCT((LEFT($A$2:$A$25,3)=C2)*(VALUE(RIGHT($A$2:$A$25,LEN($A$2:$A$25)-3))))

複製儲存格D2,貼至儲存格D2:D7。

LEFT($A$2:$A$25,3)=C2:使用 LEFT 函數取得在金額清單中儲存格前三碼,並且判斷是否和儲存格C2中的幣別相同,得到一個 TRUE/FALSE 的陣列。

RIGHT($A$2:$A$25,LEN($A$2:$A$25)-3):使用 RIGHT 函數取得金額清單中儲存格前三碼之後的數字部分。

再使用 VALUE 函數將上式的數字部分(字串),轉換型態為數字。

最後透過 SUMPRODUCT 函數將以上二式相乘加總,即為所求。其中 TRUE/FALSE 陣列在運算時會轉換為 1/0 陣列。

Gmail-進階搜尋郵件

當你在 Gmail 中的郵件數量愈來愈多時,如果你又懶得整理,有時找一封特定的郵件還真是不容易。不過,利用 Gmail 所提供的進階搜尋運算子,可以節省一些時間。常用的運算子也會自然的記下來了,使用時也會更快速。

從 Goolge 網站取得了 Gmail 的運算子說明,稍微整理一下(如下表)。

請參考網站:https://support.google.com/mail/answer/7190?hl=zh-Hant

運算子

定義

範例

from:

用於指定寄件者

範例:from:小美
意義:搜尋小美所寄的郵件

to:

用於指定收件者

範例:to:大為
意義:搜尋所有寄給大為的郵件 (寄件者是您或其他人
)

subject:

搜尋主旨行中的文字

範例:subject:晚餐
意義:搜尋主旨內含「晚餐」的郵件

OR

搜尋符合字詞 A 或字詞 B 的郵件*
*OR
必須大寫

範例:from:小美 OR from:大為
意義:搜尋小美或大為所寄的郵件

-
(
連接號
)

用於排除搜尋某些郵件

範例:晚餐 -電影
意義:搜尋內含「晚餐」但不含「電影」的郵件

label:

按照標籤搜尋郵件

範例:from:小美 label:朋友
意義:搜尋小美所寄且已加上「好友」標籤的郵件

範例:from:大為 label:我的家人
意義:搜尋大為所寄且已加上「我的家人」標籤的郵件

has:attachment

搜尋有附件的郵件

範例:from:大為 has:attachment
意義:搜尋大為所寄,且有附件的郵件

list:

搜尋郵寄清單中的郵件

範例:list:info@example.com
意義:搜尋標頭有 info@example.com
字樣,且來自或傳送至這份清單的郵件

filename:

依照名稱或類型搜尋附件

範例:filename:物理作業.txt
意義:搜尋附件檔名為「物理作業.txt
」的郵件

範例:label:工作 filename:pdf
意義:搜尋已加上「工作」標籤,且有 PDF
附件檔案的郵件

" "
(
引號)

用於搜尋完全相符的字詞*
*
不區分大小寫

範例:「好手氣」
意義:搜尋內含「好手氣」這個詞組的郵件

範例: subject:「晚餐和電影」
意義:搜尋主旨內含「晚餐和電影」這個詞組的郵件

( )

用於組合字詞
用於指定不要排除的字詞

範例:from:小美 (晚餐 OR 電影)
意義:搜尋小美所寄且內含「晚餐」或「電影」的郵件

範例:subject: (晚餐 電影)
意義:搜尋主旨同時包含「晚餐」和「電影」的郵件

in:anywhere

搜尋 Gmail 中的所有郵件*
*
根據預設,系統在搜尋時會排除 [垃圾郵件] [垃圾桶]
內的郵件

範例:in:anywhere 電影
意義:搜尋 [所有郵件][垃圾郵件] [垃圾桶]
中內含「電影」的郵件

in:inbox
in:trash
in:spam

搜尋 [收件匣][垃圾桶] [垃圾郵件] 中的郵件

範例:in:trash from:小美
意義:搜尋 [垃圾桶]
中由小美所寄的郵件

is:important
label:important

搜尋由 [優先收件匣] 標示為重要的郵件。

範例:is:important from:小珍
意義:搜尋由小珍所寄且 [優先收件匣]
標示為重要的郵件

is:starred
is:unread
is:read

搜尋有星號標記、未讀取或已讀取的郵件

範例:is:read is:starred from:大為
意義:搜尋大為所寄,且已讀取並加上星號的郵件

has:yellow-star
has:red-star
has:orange-star
has:green-star
has:blue-star
has:purple-star
has:red-bang
has:orange-guillemet
has:yellow-bang
has:green-check
has:blue-info
has:purple-question

搜尋加上特定星號的郵件

範例:has:purple-star from:大為
意義:搜尋大為所寄,且標上紫色星號的郵件

cc:
bcc:

用於指定 [副本] [密件副本] 欄位的收件者*
*
搜尋密件副本:無法搜尋將您加為密件副本收件者的郵件

範例:cc:大為
意義:搜尋副本收件者包括大為的郵件

after:
before:
older:
newer:

搜尋特定期間收發的郵件
(
使用日期格式為西元年//
)

範例:after:2004/04/16 before:2004/04/18
意義:搜尋 2004 4 16 日至 2004 4 18 日之間傳送的郵件。*
*
更精確的時間:2004 4 16 日上午 12 ( 00:00) 之後、2004 4 18 日上午 12
點之前傳送的郵件。

older_than
newer_than

 older  newer 類似,但能透過 dm  y 查詢的相對日期

範例:newer_than:2d
意義:尋找在過去兩天內發送的郵件。

is:chat

搜尋即時通訊訊息

範例:is:chat 猴子
意義:搜尋所有包含「猴子」的即時通訊訊息。

deliveredto:

搜尋郵件標頭內收件地址行中包含特定電子郵件地址的郵件

範例:deliveredto:<使用者名稱>@gmail.com
意義:搜尋郵件標頭內 [收件地址] 欄位中包含 <使用者名稱>@gmail.com 的郵件 (如此可協助您尋找由另一個帳戶轉寄或傳送至某個別名的郵件)

circle:

搜尋來自您特定 Google+ 社交圈好友發送的郵件

範例:circle:friends
意義:搜尋來自「朋友」社交圈的郵件。

範例:circle:"足球隊好友 (藍隊)" circle:"我的 \"四人幫\""
備註:使用 circle
運算子時,如果您的社交圈名稱中有空格、圓括號、大括弧或垂直線,請將社交圈名稱加上雙引號。如果您的社交圈名稱使用雙引號,請在雙引號前面加上反斜線。

has:circle

搜尋來自您所有 Google+ 社交圈的郵件

範例:has:circle
意義:搜尋來自您所有社交圈內的郵件。

category:

搜尋特定類別內的郵件

範例: category:更新
意義:「更新」類別中的所有郵件。

範例:category:社交 小敏
意義:在「社交」類別中,所有包含「小敏」的郵件。

size:

搜尋大於特定大小的郵件

範例:size:1000000
意義:所有大於 1 MB (1,000,000 位元組)
的郵件

larger:
smaller:

 size: 相似,但允許數字縮寫

範例:larger:10M
意義:搜尋所有至少 10M (10,000,000 位元組)
大小的郵件

+
(
加號
)

完全符合搜尋字串

範例:+unicorn
意義:搜尋包含「unicorn」的郵件,排除「unicorns」或「unciorn

rfc822msgid:

透過訊息識別碼搜尋郵件

範例:rfc822msgid:200503292@example.com
意義:搜尋特定 SMTP
訊息識別碼的確切郵件。進一步瞭解標頭

has:userlabels
has:nouserlabels

搜尋已套用/未套用您自定標籤的郵件
注意:Gmail
可針對個別郵件套用標籤,但不適用於對話串

範例:has:nouserlabels
意義:搜尋所有未套用您自定標籤的郵件 (不包括收件匣,垃圾郵件、垃圾桶等自動標籤)。由於 Gmail
可針對個別郵件套用標籤,您可能會看到搜尋結果套有標籤;這代表在同一對話串的另一則郵件已經套用標籤

好康東東