贊助廠商

學不完.教不停.用不盡文章列表

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

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

參考下圖,網友想要在 Excel 的工作表中找出同時是會員也是理事的人有幾個。這是常見的找出符合多條件計算個數的例子,只要使用 SUMPRODUCT 函數即可搞定。以下分別使用二種不同的資料呈現方式,再透過 SUMPRODUCT 函數來求解。

選取儲存格B1:C27,按 Ctrl+Shift +F3 鍵,勾選「頂端列」,定義名稱:會員別、職務。

根據上圖計算個數

儲存格E5:=SUMPRODUCT((會員別="會員")*(職務="理事"))

若儲存格內容,改成下圖。

選取儲存格B1:B27,按 Ctrl+Shift +F3 鍵,勾選「頂端列」,定義名稱:會員職務。

儲存格D5:=SUMPRODUCT((會員職務="會員,理事")*1)

試比較以上二個圖中的資料呈現方式,下圖如果不是使用「會員,理事」,而是使用「理事,會員」,則上述公式將會出錯。可以稍加修改公式:

儲存格D5:=SUMPRODUCT((會員職務="會員,理事")*1)+
SUMPRODUCT((會員職務="理事,會員")*1)

 

【思考一下】

(1) 若是條件變為三個、四個,你是否也會處理公式?

(2) 公式中(會員別="會員")*(職務="理事"),其中的「*」意義為何?

(3) 公式中的「*1」意義為何?

(4) 公式:=SUMPRODUCT((會員職務="會員,理事")*1)+SUMPRODUCT((會員職務="理事,會員")*1),改成=SUMPRODUCT((會員職務="會員,理事")*1+(會員職務="理事,會員")*1),結果是否一樣?

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

網友想要將 Excel 中儲存格內所填入的運算式轉換為執行結果,例如下圖中的A欄為運算字串,B欄則顯示其運算結果。這該如何處理呢?

在 Excel 的工作表中,運算公式必須以「=」開頭來輸入,如果輸入「123+456」,則會顯示這個字串,如果輸入「=123+456」,則會顯示運算結果(579)。

image

要解決這個問題,可以參考以下的步驟:

1. 定義一個名稱

假設要運算字串來自儲存格A2,在名稱管理員中新增一個名稱,本例為:caluculate,並將參照位址指定為=EVALUATE(工作表!A2)

2. 設定公式

儲存格B2:=calculate

將要顯示運算結果的儲存格,設定其公式為已經定義的名稱,不要忘記加上「=」,才會執行運算。

3. 儲存檔案

以 Excel 2010/2013 為例,當你第一次存檔時,Excel 會提醒你使用的 EVALUATE 函數有引用巨集功能,所以你得另存為「啟用巨集的活頁簿」。

例如:使用「*.xlsm」格式的 Excel 檔案類型。

但是,如果你不想儲存格為「*.xlsm」格式,則可以選取「Excel 97-2003活頁簿」檔案格式。下次開啟這個檔案時,尚未點選啟用內容前,運算結果通通都會出錯,因為巨集尚未啟用,只要點選啟用內容,運算結果即會恢復正常。

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

有網友問到:在 Excel 中,如何判斷一個儲存格中的內容是否為英文或是中文?我想應該要以判斷幾個全型字或半型字來做區隔,因為中文為全型字,而一般英文字為半型字,可以藉助 LEN 函數和 LENB 函數來判斷。

LEN:傳回文字字串中的字元數。

LENB:傳回用於代表文字字串中字元的位元組數。

參考下圖的整理:

 

【公式處理】

儲存格D2:

=IF(LEN(A2)=LENB(A2),"半型",IF(2*LEN(A2)=LENB(A2),"全型","全半型"))

儲存格E2:

=2*LEN(A2)-LENB(A2)&"個半型"&LENB(A2)-LEN(A2)&"個全半型"

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

有網友問到:在 Excel 中如何將一個月總數轉換為年數+月數?參考下圖,由一個月的總數來轉換為年數和月數。

 

【公式處理】

儲存格C2:=INT(A2/12)

求得月總數除以 12 的商數。

儲存格D2:=MOD(A2,12)

求得月總數除以 12 的餘數。

儲存格F2:=INT(A2/12) & "年" & TEXT(MOD(A2,12),"00") & "月"

其中使用 TEXT 函數來將月數格式化為二個位數。

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

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

有同事在一個 Excel 資料表中,因為執行資料剖析之後,得到如下圖的結果。其實不管是 B欄、C欄、D欄、E欄的儲存格內容,都應該出現在B欄中才是正確結果。但是,現在已變成這樣,該如何解決呢?

建議做法:

1. 選取B欄至E欄中的有資料儲存格(本例為:儲存格B2:E100)

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

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

4. 在[特殊目標]對話框中選取「空格」選項,按下[確定]按鈕,選取所有的空白儲存格。

5. 在選取區上按一下右鍵,選取[右側儲存格左移]選項。

在按下[確定]按鈕之後,在B欄、C欄、D欄、E欄的儲存格內容,都會移至B欄中。

如此,便完成了搬移的工作。

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

在 Excel 中常會遇到要將多個儲存格內容合併在一個儲存格中(分列顯示),或是將一個儲存格中多列顯示的內容分別放在不同儲存格中,該如何處理呢?(參考下圖)

(1)合併儲存格內容

如果要在一個儲存格中分多列顯示,則要藉助 Alt+Enter 鍵。如果你要使用公式來將儲存格內容分列顯示,則可以使用 CHAR(10)

儲存格C2:=A2&CHAR(10)&A3&CHAR(10)&A4

然後設定儲存格C2中對齊方式的文字控制為「自動換列」。

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

 

(2) 分離儲存格內容

儲存格E2:=MID(C2,1,FIND(CHAR(10),C2)-1)

FIND(CHAR(10),C2):找出第一個分列的位置(看不到但也算一個字元)

儲存格E3:=MID(C2,FIND(CHAR(10),C2)+1,FIND(CHAR(10),C2)-1)

由第一個分列的位置之後一個字元開始,找出第二個分列的位置(傳回第幾個字元的位置)

儲存格E4:=RIGHT(C2,LEN(C2)-LEN(E2)-LEN(E3)-2)

其中減 2 是因為在儲存格C2中字元數包含了二個 CHAR(10) 字元。

複製儲存格E2:E4,往下各列貼上。

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

今日在網路上看到一位老師在網路上分享了在 Excel 中複製不連續儲存格範圍時所產生的操作問題:參考下圖,有時在操作時會出現「無法在不相鄰的範圍執行此命令」的錯誤訊息。

以前我和其他同事也有遇過相同的問題。通常,你要選取不連續的儲存格區域,你會藉助 Ctrl 鍵和滑鼠來操作比較方便。

先以下圖來說明,當你選取了儲存格A1:D6後,按著 Ctrl 鍵,再選取儲存格B3:C5。當按下 Ctrl +C 鍵(執行複製動作),此時就會出現:「無法在不相鄰的範圍執行此命令」的錯誤訊息。因為你選取了二個有部分重疊的區域,所以無法在不相鄰的範圍執行此命令。

那麼,為何會出現錯誤訊息呢?試試以下二種操作方式

(1) 會出現錯誤

(作用中儲存格不一定是儲存格A1)按著 Ctrl 鍵,用滑鼠選取儲存格A1:D6,按著 Ctrl 鍵,再用滑鼠選取儲存格A8:D13,按下 Ctrl+C鍵(複製)。

(2) 不會出現錯誤

(作用中儲存格不一定是儲存格A1)用滑鼠選取儲存格A1:D6,按著 Ctrl 鍵,再用滑鼠選取儲存格A8:D13,按下 Ctrl+C鍵(複製)。

你能體會為何出現錯誤訊息嗎?因為作法(1),多選了一個原先作用中的儲存格。

如果以作法(2)選取儲存格A1:D6和儲存格A8:D13和儲存格A15:D20,貼至儲存格F1之後,你有發現嗎?三個不連續的儲存格區域會被接成連續的區域。

如果你用做法(2),然後再貼上前先選取儲存格F1:I6和儲存格F9:I14和儲存格F17:I22,再按 Ctrl +V 鍵(貼上),則一樣也會出現相同的錯誤息。(參考下圖)

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

最近看到同事要將 Excel 中某個工作表的局部內容印出來,竟然是先複製想要的內容到另一個工作表,再執行列印工作,著實嚇了一跳!

如下圖這樣的文件,可能在主要的資料內容之外,還有一些說明內容、美化的圖案等。在某些時候,只想將主要的資料內容列印出來,該如何處理呢?

你可以這樣來操作:

(1)

選取資料部分(儲存格A1:E17),在[列印]對話框中的[設定]區中,選取「列印選取範圍」,檢視預覽列印區中的內容,即會只列出被選取的內容。

特別提醒:如果你使用 Ctrl 鍵,選取了幾個非連續的選取區,則在列印選取範圍時,每不同的選取區將會列印在不同頁面上。

 

(2)

選取資料部分(儲存格A1:E17),在[版面配置]功能表中選取「列印範圍/設定列印範圍」選項。(註:相同的操作也可以解除還原設定的列印範圍)

直接按 Ctrl + P 鍵,即可在[列印]對話框中直接列印被選取的資料部分。

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

在 Excel 中,有網友取得了看似日期時間組成的字串,要如何轉換為標準的日期時間格式?

參考下圖,的確有很多人會接收到這類格式的文字字串,但是在 Excel 中無法直接以日期時間來處理。因為在 Excel 中,日期時間必須是數值格式,所以在下圖中的資料要處理前必須要轉換。

 

【公式設計】

儲存格B2:=DATE(MID(A2,1,4),MID(A2,5,2),MID(A2,7,2))+
TIME(MID(A2,10,2),MID(A2,12,2),0)

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

MID(A2,1,4):取儲存格A2字串的第1~4個字元當為日期中的年。

MID(A2,5,2):取儲存格A2字串的第5~6個字元當為日期中的月。

MID(A2,7,2):取儲存格A2字串的第7~8個字元當為日期中的日。

透過 DATE 函數代入以上三個數值為參數,轉換日期的數值。

MID(A2,10,2):取儲存格A2字串的第10~11個字元當為時間中的時。

MID(A2,12,2):取儲存格A2字串的第12~13個字元當為日期中的分。

透過 TIME 函數代入以上二個數值為參數,轉換時間的數值。

接著,要設定儲存格格式自訂數值為:yyyy/mm/dd hh:mm

學不完.教不停.用不盡文章列表

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

有網友問到:在 Excel 中的一個資料表(如下圖左),如果在 AAA 欄位中篩選出大於 50 者(如下圖右),如何將被篩選出來的 BBB 欄位中的「乙」全置換為「乙乙乙」。

如果你是直接用尋找/取代的功能,則會連未被篩選的儲存格內容也會被置換,該如何才能做到只置換已被篩選的資料呢?

【參考作法】

首先,選取這些已被篩選的儲存格,然後按 Ctrl + G 鍵,在顯示的[到]視窗中,按一下[特殊]按鈕。

接著,點選[可見儲存格]選項,按一下[確定]按鈕。

你會看到,只有被篩選的儲存格被選取。

再來,進行取代的動作:

當你取消篩選時,即可發現原先未被篩選的儲存格資料,並不會被取代動作所改變。

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

在 Excel 中使用公式時,有些儲存格內容看起來像數字其實是文字的格式,或是在公式處理時取出字串中的數字部分,其實還是文字格式,要如何將這些看起來像數字的文字真正的轉換為數字呢?

例如下圖中,在公式中以 RIGHT 函數來取出儲存格A2中的數字部分,這個結果的數字是文字格式(結果預設靠左對齊)。這樣的問題,困擾了很多的 Excel 初學者!

在 Excel 中如果你將文字格式的數字拿來執行數值運算(使用運算子:+、–、*、/ 等),或是透過其他數值公式(例如:SUM、AVERGE)或是統計公式(例如:MAX、SMALL)的運算,都能自動轉換。

透過以下的這些運算,都能在運算過程中將文字轉換為數字。(以下僅是列舉,族繁不及備載!)

=--RIGHT(A2,3)

=0+RIGHT(A2,3)

=1*RIGHT(A2,3)

=RIGHT(A2,3)/1

=VALUE(RIGHT(A2,3))

=INT(RIGHT(A2,3))

=SUM(RIGHT(A2,3))

=AVERAGE(RIGHT(A2,3))

=SMALL(RIGHT(A2,3),1)

=LARGE(RIGHT(A2,3),1)

=MIN(RIGHT(A2,3))

=MAX(RIGHT(A2,3))

。。。。。

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

最近有一些同仁開始在 Excel 的工作表中使用「名稱」,所以提出了一些做法上的問題。名稱的使用的確可以讓選取的操作變的簡單,讓公式的使用變的易讀且能縮短公式。

用以下的資料來練習:

 

【定義名稱】

在功能表中,可以使用的名稱相關工具如下:

要定義名稱,最簡單的做法是:選取儲存格A1:E19,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:座號、人員、國文、英文。

image

當然,你也可以選取[公式/已定義名稱]功能表的「名稱管理員」來設定:

留意到上圖中「參照到」的儲存格範圍,定義儲存格範圍在「工作表1」中的儲存格。

我們試著手動新增小計和資料表兩個名稱,

「小計」名稱參照到:=工作表1!$E$2:$E$19

「資料」名稱參照到:=工作表1!$A$1:$E$19

 

【選取儲存格時使用名稱範圍】

當定義好名稱,如果要選取某個名稱的範圍,則只要在[名稱方塊]下拉式清單中選取想要的名稱即可,例如:選取國文,即會自動選取國文成績範圍。

 

【公式中使用名稱】

而在公式的應用上,你可以使用名稱來取代儲存格或儲存格範圍。例如:

(1) 儲存格E2:=國文+英文

(2) 儲存格C20:=AVERAGE(國文)

當你複製公式時,以名稱表示的儲存格範圍,相當於使用儲存格的「絶對位址」表示。

另一個例子:

如果你要找出英文最大值在第幾列,可以這樣撰寫公式:

=SUMPRODUCT((MAX(英文)=英文)*ROW(英文))

公式簡潔易讀!(結果為12,即在第12列。)

 

【在資料驗證中使用名稱】

如果你想要根據這個資料表的人員清,製作一個人員的下拉式清單:

只要在[資料驗證]對話框中設定:

資料驗證準則的儲存格內允許:清單,來源:=人員。

 

【在監看視窗中使用名稱】

如果你要在監看視窗中新增多個儲存格來監看,則試試使用名稱:

當按下[新增]按鈕時,即可同時新增多個監看的儲存格:

 

【在範圍轉換為表格中使用名稱】

當我們要將一個 Excel 的資料範圍轉換為表格時,也可以使用名稱:

學不完.教不停.用不盡文章列表

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

有網友問到:在 Excel 中的一個資料表(如下圖左),如何篩選出人員為甲及非空白日期的資料,並且統計其數值的和?

如果你不想手動使用篩選工具,則可以藉助公式來篩選,但是必須經過「陣列公式」的處理。

 

【準備工作】

選取儲存格B1:D17,按 Ctrl+Shift+F3 鍵,選取「頂端列」,定義名稱:人員、數值、日期。

 

【公式設計】

1. 篩選:人員為甲

(1) 找出符合的列數

儲存格F3:{=SMALL(IF(人員="甲",ROW(人員),999),ROW(1:1))-1}

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

其中參數「999」是隨意指定一個較大的數值而已。

 

(2) 根據符合的列數找出符合的內容

儲存格G3:{=OFFSET(B$1,SMALL(IF(人員="甲",ROW(人員),999)-1,
ROW(1:1)),,)}

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

複製儲存格G3,貼至儲存格G3:I3。複製儲存格F3:I3,貼至儲存格F3:I8。

 

(3)計算篩選後的總和

儲存格H1:=SUMPRODUCT((人員="甲")*數值)

 

2. 篩選:非空白日期

(1) 找出符合的列數

儲存格F12:{=SMALL(IF(日期<>"",ROW(日期),999),ROW(1:1))-1}

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

 

(2) 根據符合的列數找出符合的內容

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

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

複製儲存格G12,貼至儲存格G12:I12。複製儲存格F12:I12,貼至儲存格F23:I23。

 

(3)計算篩選後的總和

儲存格H10:=SUMPRODUCT((日期<>"")*數值)

 

3. 調整日期格式

讀者如果實做時就會發現在 OFFSET 函數篩選日期的結果如果是空白儲存格,則會以「0」顯示,該如何將 0 顯示為空白呢?

為了兼顧正常日期要顯示為月二碼、日二碼,所以將所有日期的儲存格數值格式設定為:「mm/dd;;」

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

在 Excel 中,有網友想要將一個已知日期減 1 ,並且將原型格式更改為 8 碼的格式(參考下圖),該如何處理?

【輸入公式】

儲存格B2:=TEXT(A2-1,"yyyymmdd")

TEXT 函數的參數 A2-1,表示儲存格中的日期減 1,"yyyymmdd" 表示將格式顯示為西元年 4 碼、月 2 碼、日 2 碼。

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

這是 TEXT 函數好用之處!可以直接在函數中設定數值的顯示格式,當然你也可以改由手動的格式設定來調整顯示的樣子。你可以在儲存格的數值格式中,設定數值的自訂格式為:yyyymmdd。

而 TEXT 函數能設定的格式多元,請參考微軟網站所列的做法:

資料來源:https://support.office.com/zh-hk/article/TEXT-%E5%87%BD%E6%95%B8-29cea14b-bd86-426c-9985-cb2f0b19df58?ui=zh-TW&rs=zh-HK&ad=HK

預留位置

描述

0 ()

如果某數字內含零的數目比格式內設定的零更少,則會顯示無意義的零。例如,若您輸入 8.9,並想要將其顯示為 8.90,請使用格式 #.00

#

遵循與 0 () 相同的規則。不過,當您輸入數字時,若該數字的小數點任一端內含比格式內設定 # 符號的小數位數更少時,Excel 就不會顯示多餘的零。例如,若自訂格式為 #.##,而您在儲存格內輸入 8.9,則會顯示 8.9

?

遵循與 0 () 相同的規則。不過,Excel 會在小數點的任一端為無意義的零加上空白,使小數點在資料行中對齊。例如,自訂格式 0.0? 會在資料行內對齊數字 8.9  88.99

. (句點)

顯示數字內的小數點。

 

顯示天數、月份及年份:若要將數字以日期格式顯示 (例如天數、月份和年份),請在 format_text 引數內使用下列代碼。

引數

描述

m

以沒有前置零的數字顯示月份。

mm

以具有適當之前置零的數字顯示月份。

mmm

以縮寫 (Jan Dec) 顯示月份。

mmmm

以全名 (January December) 顯示月份。

mmmmm

以單一字母 (J D) 顯示月份。

d

以沒有前置零的數字顯示天數。

dd

以具有適當之前置零的數字顯示天數。

ddd

以縮寫 (Sun Sat) 顯示天數。

dddd

以全名 (Sunday Saturday) 顯示天數。

yy

以兩位數的數字顯示年份。

yyyy

以四位數的數字顯示年份。

 

顯示小時、分鐘和秒數:若要顯示時間格式 (例如小時、分數和秒數),請在 format_text 引數內使用下列代碼。

引數

描述

h

以沒有前置零的數字顯示小時。

[h]

以小時為單位顯示已耗用時間。如果您正在使用某個以超過 24 小時的數字傳回時間的公式,請使用類似 [h]:mm:ss 的數字格式。

hh

以具有適當之前置零的數字顯示小時。如果格式內含 AM  PM,則會依照 12 小時制來顯示小時。否則便以 24 小時制來顯示小時。

m

以沒有前置零的數字顯示分鐘。

附註   m  mm 代碼必須立即出現在 h  hh 代碼之後,或立即出現在 ss 代碼之前,否則 Excel 便會顯示月份,而不是分鐘。

[m]

以分鐘為單位顯示已耗用時間。如果您正在使用某個以超過 60 分鐘的數字傳回時間的公式,請使用類似 [mm]:ss 的數字格式。

mm

以具有適當之前置零的數字顯示分鐘。

附註   m  mm 代碼必須立即出現在 h  hh 代碼之後,或立即出現在 ss 代碼之前,否則 Excel 會顯示月份,而不是分鐘。

s

以沒有前置零的數字顯示秒數。

[s]

以秒數為單位顯示已耗用時間。如果您正在使用某個以超過 60 秒的數字傳回時間的公式,請使用類似 [ss] 的數字格式。

ss

以具有適當之前置零的數字顯示秒數。如果您想要顯示秒數的分數文字,請使用類似h:mm:ss.00 的數字格式。

 

註:其他更多的說明請自行參閱微軟網站。

學不完.教不停.用不盡文章列表

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

在之前的文章:Excel-將不同類別資料篩選至另一個工作表(陣列公式)中,如果以抓取「北區」的資料為例,公式:

儲存格A2:{=IFERROR(INDEX(INDIRECT(A$1),SMALL(IF(區別="北區",ROW(區別),
FALSE),ROW(1:1))-1,1),"")}

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

公式說明請參考:Excel-將不同類別資料篩選至另一個工作表(陣列公式)

 

有網友想要問:如果相同的公式,要改求「北區+南區」,要如何修改公式?

儲存格A2:{=IFERROR(INDEX(INDIRECT(A$1),SMALL(IF((區別="北區")+(區別="南區"),
ROW(區別),FALSE),ROW(1:1))-1,1),"")}

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

其公式修改關鍵為將原公式中 IF 函數的條件:區別="北區"

改成雙條件:(區別="北區")+(區別="南區")

其中的「+」運算乃相當於執行 OR 運算,即雙條件中只要符合其中一個即可被取出。

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

在 Excel 的資料表中,如果要符合特定年月日期的總和,通常你會使用 SUMPRODUCT 函數。因為在符合雙條件之下要計算總和,使用 SUMPRODUCT 函數以乘積和的概念來計算,讓公式顯的簡單易懂。

網友問到:日期由於是輸入或由其他來源導入,所以有可能以數字型態呈現,也可能以文字型態呈現。公式應該如何來設計呢?

以下圖為例,日期是數字型態。先選取儲存格A1:B27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:數字日期、數值。

儲存格E3:=SUMPRODUCT((YEAR(數字日期)=2014)*(MONTH(數字日期)=4)*數值)

以下圖為例,日期是文字型態。先選取儲存格A1:B27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:文字日期、數值。

儲存格E3:=SUMPRODUCT((YEAR(文字日期)=2014)*(MONTH(文字日期)=4)*數值)

和數字型態的公式比較,其公式是可以相通的,也就是不管日期是數字或文字,相同公式都可以取得結果。

如果使用另一個公式,也可以得到結果:

儲存格E3:=SUMPRODUCT((LEFT(文字日期,7)="2014/04")*數值)

但是如果在數字日期中使用相同公式,則所得結果皆為 0:

儲存格E3:=SUMPRODUCT((LEFT(數字日期,7)="2014/04")*數值)

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

同事問到的問題:同事已將手機的連絡人已和 Google 通訊錄整合,想要在電腦中備份這些連絡人資料,於是在 Google 通訊錄中匯出了連絡人資料。但是由於 Google 匯出的連絡人資料是一個CSV格式,當由 Excel 開啟時並由明確的將各欄位資料置於各個欄位中:

於是試著使用「資料剖析」工具(使用[資料/資料工具]選單的「資料部析」),參考下圖,卻發現電話欄位中不管是市話或是手機號碼,只要是 0 開頭者,這個 0 都不會顯示,實在很困擾。

該如何避免這種問題發生呢?

這個問題其實不難,只要在執行資料剖析時,在第三個步驟中,挑選電話號碼欄位,再將欄位的資料格式設定為「文字」(預設為「一般」),如果有多個電話號碼欄位,則要一個一個欄位設定:

當完成匯入後,電話號碼欄位即是以「0」開頭之數字組成的文字。

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

學校同仁問到在 Word 文件中要插入頁碼很容易,但要插入文件的頁數卻找到不相關功能位置。如何才能插入文件的頁數呢?

的確,在 Word 2010/2013 都是找不到「插入頁數」的功能,無法直接使用按鈕來插入頁數。

你可以這樣操作:

切換至頁首或頁尾中,顯示[頁首及頁尾工具]功能表中,在[插入/快速組件]選單中,選取「功能變數」。

在[功能變數]對話框中,找到:NumPages,然後在[功能變數內容]區中點選你想要顯示的格式。

如此,便能顯示頁碼和頁數了:

而頁碼也是一種功能變數,其名為:Page。

你只要按 Alt+F9 鍵,即可切換顯示功能變數名稱或是功能變數結果:

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

有網友問到一個在 Excel 中,大家常會遇到的問題:當在一個運算公式中如何排除含有錯誤訊息的儲存格?

例如在下圖中要計算儲存格A1:D8中的所有數值總和,但是很明顯的看到如果使用公式:=SUM(A1:D8),會傳回錯誤訊息,因為運算公式中包含儲存格B4和儲存格C6,這兩個儲存格是運算產生錯誤的儲存格。

但是常常不想要重新設定公式,只想要排除含有錯誤訊息的儲存格,該如何處理呢?

儲存格F6:{=SUM(IF(NOT(ISERR(A1:D8)),A1:D8,))}

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

ISERR(A1:D8):在陣列公式中傳回儲存格A1:D8中含有錯誤訊息的儲存格之 TRUE/FALSE 陣列。

NOT(ISERR(A1:D8)):將上式的 TRUE/FALSE 陣列反轉為 FALSE/TRUE 陣列。

IF(NOT(ISERR(A1:D8)),A1:D8,):若是儲存格A1:D8中不是錯誤訊息者,則傳回其儲存格位址陣列。

再透過 SUM 函數予以加總,即為所求。

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

在 Excel 中可以使用自動填滿或公式方式來產生各種數列和清單,

參考:http://isvincent.pixnet.net/blog/post/32233649

如果像下圖中根據第一個數來產生頭尾都要以二位數流水號來變化,該如何處理呢?

儲存格C1:=TEXT(ROW(1:1),"00")&"XXXX"&TEXT(ROW(1:1),"00")

其中TEXT(ROW(1:1),"00")的用意是:在第一列中ROW(1:1)=1,當往下複製公式時會產生 ROW(2:2)=2 → ROW(3:3)=3 → ROW(4:4)=4 → … 。

TEXT 函數中使用「"00"」格式,可以讓 1 ~ 9 的數字顯示為 00 ~ 09。

複製儲存格C1,往下各列貼上即可。

註:本例公式只能產生二位數的流水號數列清單。

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼