贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201509 (43)

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

有網友問到:如下圖的 Excel 資料表(右圖),如何將「報名」欄為「Yes」者取出摘要成左圖的結果,其中每個學員的 Email 和電話分成二列呈現。

為了說明方便,選取F欄中所有「報名」欄的內容,定義名稱:報名。

Excel-每筆分二列摘要資料(陣列公式,OFFSET,SMALL,ROW,INT)


【公式設計與解析】

1. 列出學員姓名

儲存格A2:{=IFERROR(OFFSET($E$1,SMALL(IF(報名="Yes",ROW(報名),FALSE),
INT(ROW(2:2)/2))-1,0,1,1),"")}

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

(1) SMALL(IF(報名="Yes",ROW(報名),FALSE)

在陣列公式中,當「報名」欄位為「Yes」時傳回「報名」儲存格陣列的列號,否則傳回「FALSE」。

(2) INT(ROW(2:2)/2))

當公式向下複製時會產生 0, 0, 1, 1, 2, 2, 3, 3, 4, 4, 5, 5, ...。

(3) OFFSET($E$1, 第(1)式, 第(2)式, 0,1,1)

將第(1)式和第(2)式代入 OFFSET 函式,取得對應的儲存格內容。

(4) IFERROR(OFFSET($E$1, 第(1)式, 第(2)式 , 0,1,1),"")

將第 (3) 式傳回值為錯誤訊息者,以空白顯示。

2. 列出學員Email

儲存格B2:{=IFERROR(OFFSET($G$1,SMALL(IF(報名="Yes",ROW(報名),FALSE),
INT(ROW(2:2)/2))-1,0,1,1),"")}

和儲存格A2公式的說明相仿。

3. 列出學員電話

儲存格B3:{=IFERROR(OFFSET($H$1,SMALL(IF(報名="Yes",ROW(報名),FALSE),
INT(ROW(2:2)/2))-1,0,1,1),"")}

和儲存格A2公式的說明相仿。

複製儲存格A2:B3,往下各列貼上。

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

以前的一位老同事最近問到一個常見的問題:如何在 Word 中插入圖片時,能自動調整圖片大小以符合表格的欄位寬度?

以 Word 2010 為例,下圖是一個 2X2 的表格,想要在這個表格中插入四張圖,可是四張圖片的大小並不相同,希望能插入圖片時,不要影響表格的欄位寬度,該如何處理呢?

Word-在表格中插入圖片時自動調整大小以符合欄寬

在預設狀態下插入圖片時,會改變儲存格的大小。

Word-在表格中插入圖片時自動調整大小以符合欄寬

要解決這樣的問題,可以先調整好你要的表格、欄位的大小,然後選取表格,再選取[表格工具/版面配置]功能表中的[儲存格大小/自動調整]選項中的「固定欄寬」。

Word-在表格中插入圖片時自動調整大小以符合欄寬

當插入大小大一或是寬度、高度不相同的各式圖片時,都會自動的縮小圖片以符合儲存格的大小。經過這樣的設定後,工作就省時、省事多了。

Word-在表格中插入圖片時自動調整大小以符合欄寬

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

有位老師問到:

如果在 Excel 中每一個月有不同內容的多個工作表,其內容是某些課程的時間、地點和攜帶物品的明細表。如何能在輸入某個月份時,即能列出該月份的各個課程之時間、地點和攜帶物品,以方便提醒每位小朋友呢?

Excel-跨工作表查詢資料(VLOOKUP,INDIRECT,COLUMN)


【公式設計與解析】

關於這個問題,因為老師想要將各個月的課程內容放在不同的工作表,所以依老師的想法設計公式。

1. 安排工作表

以 10月、11月、12月為例:

(1) 將每不同月份都以相同格式安排,只是其中的內容不同而已。

(2) 依序將工作表名稱命名為:10月、11月、12月

Excel-跨工作表查詢資料(VLOOKUP,INDIRECT,COLUMN)

Excel-跨工作表查詢資料(VLOOKUP,INDIRECT,COLUMN)

Excel-跨工作表查詢資料(VLOOKUP,INDIRECT,COLUMN)


2. 設計查詢公式

(1) 在儲存格B1輸入月份

因為儲存格B1輸入的內容要與工作表名稱一致,建議使用「資料驗證」方式來建立下拉式清單以方便選取。

Excel-跨工作表查詢資料(VLOOKUP,INDIRECT,COLUMN)

(2) 輸入公式

儲存格C1:

=VLOOKUP($B3,INDIRECT($B$1&"!$A$2:$D$4"),COLUMN(B:B),FALSE)

複製儲存格C1,貼至儲存格C1:E10。

INDIRECT($B$1&"!$A$2:$D$4"):透過 INDIRECT 函數將儲存格B1的內容轉換為工作表名稱,而參照不同工作表的格式為:「'工作表名稱'!儲存格範圍」。

COLUMN(B:B):當公式向右複製時,COLUMN(B:B)=2→COLUMN(C:C)=3→COLUMN(D:D)=4→...。用以在 VLOOKUP 函數中顯示儲存格A2:D4的第 2, 3, 4 欄內容(時間、地點和攜帶物品)。

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

網友問到在 Excel 中有一個如下圖(上)的一個基本資料表,如何藉由輸入發票號碼,來篩選出同一發票的所有客戶資料?(參考下圖(下))

Excel-查表應用(OFFSET,MATCH,COUNTIF,ROW)


【公式設計與解析】

1. 查詢客戶編號

儲存格C19:=OFFSET(B1,MATCH(A20,A2:A17,0),,,)

MATCH(A20,A2:A17,0):求儲存格A20的發票號碼在儲存格A2:A17範圍中的第幾列。

OFFSET(B1,MATCH(A20,A2:A17,0),,,):將上式傳回的第幾列,將值代入 OFFSET 函數取得儲存格的內容。

2. 查詢客戶名稱

儲存格E19:=OFFSET(C1,MATCH(A20,A2:A17,0),,,)

做法同「1. 查詢客戶編號」公式。

3. 查詢:產品編號、產品名稱、單價、數量

(1) MATCH($A$20,$A$2:$A$17,0)

做法同「1. 查詢客戶編號」公式。

(2) OFFSET(D$1,MATCH($A$20,$A$2:$A$17,0)+ROW(1:1)-1,,,)

MATCH($A$20,$A$2:$A$17,0)+ROW(1:1):用於向下複製公式時,每增加一個列號,其傳回值也會相對加 1。

因為相同發票號已排列在一起,所以將上式代入 OFFSET 函數取得對應的儲存格內容,當向下複製公式時,即可依序取得相同的發票的不同記錄之內容。

儲存格B21:=IF(ROW(1:1)<=COUNTIF($A$2:$A$17,$A$20), 第(2)式 ,"")

COUNTIF($A$2:$A$17,$A$20):計算同一個發票號碼共有幾筆記錄。

透過 IF 函數,將超過相同發票的記錄數者,予以顯示空白。

最後,複製儲存格B21,貼至儲存格B21:E27。


【補充說明】

完整公式/儲存格B21:=IF(ROW(1:1)<=COUNTIF($A$2:$A$17,$A$20),
OFFSET(D$1,MATCH($A$20,$A$2:$A$17,0)+ROW(1:1)-1,,,),"")

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

網友在 Excel 中有一個資料表如下圖中的A欄和B欄,想要根據項次和數值的內容,將合計不超過某一數值者(本例為:300)分成一組,並且依項次由小至大分組。其次,再將各組的項次列出。以上兩個問題,該如何處理?

觀察下圖,項次 A01~A06 的合計為 285,若再加上 A07,會超過 300,所以將A01~A06 分在第1組。而 A07 為第 2 組的第 1 個,A07~A11 合計為 222,若再加上A12 的 96,會超過 300,所以將 A07~A11 分為第 2 組,依此類推。

Excel-以合計不超過某一數值為一組來分組(OFFSET,MATCH,COLUMN)


【公式設計與解析】

本例以累不超過 300 者為一組。

1. 計算分組累計

儲存格C2:=B1

儲存格C3:=IF((C2+B3)>300,B3,C2+B3)

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

2. 找出組別

儲存格D2:=1

儲存格D3:=IF((C2+B3)>300,D2+1,D2)

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

3. 列出每組分項內容

先將D欄中有資料的範圍,定義名稱:組別。

儲存格G2:=IF(COLUMN(A:A)<=COUNTIF(組別,$F2),OFFSET($A$1,MATCH($F2,組別,0)+COLUMN(A:A)-1,,,),"")

(1) MATCH($F2,組別,0)

找出各組的第一個項目所在的列號。

(2) OFFSET($A$1,MATCH($F2,組別,0)+COLUMN(A:A)-1,,,)

根據各組第一個列號,依序列出各組的項目。其中,COLUMN(A:A)=1 在向右複製時,會產生 COLUMN(A:A)=1→COLUMN(B:BA)=2→COLUMN(C:CA)=3→...。

透過 OFFSET 函數將直式(由上而下)的資料轉換為橫式(由左而右)顯示。

(3) IF(COLUMN(A:A)<=COUNTIF(組別,$F2), 第(2)式 ,"")

如果項目所在欄號(A欄=1、B欄=2、C欄=3、...)大於該組的數量,則以空白顯示。

最後,複製儲存格G2,貼至儲存格G2:L16。

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

在網路上看到一篇 Excel 的文章,覺得可以分享給有需要的人。

觀察下圖,這是一個由每個月最後一天所集合而成的報表所繪製出來的折線圖。然而,9/30、10/31、11/30、12/31 因為是推測值(時間是未來時間),所以想要用不同色彩來呈現,以凸顯這是推測的區域,該如何處理呢?

Excel-在折線圖中分二區顯示不同色彩

參考步驟:

下圖是一個依資料表做成的折線圖,其中數列構成的折線圖是單一色彩,無法顯示二種不同色彩。

Excel-在折線圖中分二區顯示不同色彩

因此,可以在想要顯示不同色彩的區域新增一個矩形,蓋住這個區域的折線圖。

Excel-在折線圖中分二區顯示不同色彩

這個矩形有一個預設的色彩,先修改這個色彩為:其他填滿色彩。

Excel-在折線圖中分二區顯示不同色彩

在[色彩]對話框中設定:紅色、綠色、藍色的數值均為:255,透明設定數值為:30%。外框設定為:無外框。

Excel-在折線圖中分二區顯示不同色彩

也就是 9/30~12/31 之間顯示的是一個 30% 透明的白色,視覺上淡化了這個區域的色彩。讓使用者注意到這是個不一樣的折線圖區域。

Excel-在折線圖中分二區顯示不同色彩

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

你知道在 Excel 中要輸入函數有那些方法嗎?對於初學者,可能要由瀏覽函數開始慢慢找到想要的函數,漸漸的可以把函數名稱背下來直接輸入。有時候會面對少用而陌生的函數,該如何取得提示和說明呢?


(1) 瀏覽函數來輸入

使用[插入函數]按鈕,當你選取一個儲存格後,按一下 fx,可以開啟[插入函數]對話框,透過瀏覽函數的方式來找到並且輸入函數。你也可以按下 Shift+F3 鍵,來開啟[插入函數]對話框。

Excel-在輸入公式時使用函數的各種方式


2. 輸入函數名稱

若你記得函數名稱,直接在儲存格中輸入函數名稱,Excel 會列出與該函數文字相關的函數。

Excel-在輸入公式時使用函數的各種方式

在你要的函數名稱上按二下,即可帶入該函數,並且會顯函數參數的提示。

Excel-在輸入公式時使用函數的各種方式

點選函數名稱,可以取得該函數的說明:

Excel-在輸入公式時使用函數的各種方式

若此時,按一下 Ctrl+Shift+A 鍵,則函數的參數提示會顯示在資料編輯列中。

Excel-在輸入公式時使用函數的各種方式

若在已輸入函數名稱時,按一下 Ctrl+A 鍵或是 Shift+F3 鍵。

Excel-在輸入公式時使用函數的各種方式

此時,會開啟對話框讓你輸入函數的參數,並且可以看到預覽結果和輔助說明。

Excel-在輸入公式時使用函數的各種方式

如果你已定義一些名稱,該名稱也會出現在輸入公式的選項中。

Excel-在輸入公式時使用函數的各種方式

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

有學校同仁問到:如何在下圖中 Excel 的儲存格輸入資料,能由左而右、由上而下的順序輸入資料?

在 Excel 中的儲存格輸入資料,按下 Enter 鍵,預設跳到「下一列同一欄」的儲存格,而按下 Tab 鍵,預設跳到「同一列下一欄」的儲存格。由於在工作中,常常是一張紙填寫一筆資料(Record),所以輸入時會同一列的內容輸完才會換到下一列,因此依向左而右、由上而下的順序在儲存格中輸入資料是有其必要性。

Excel-依向左而右、由上而下的順序在儲存格中輸入資料

如果你選取[檔案/選項]功能表,在「進階」標籤下的[編輯選項]區中,可以設定按 Enter 鍵後,移動選取範圍的方向,預設值為:下。

Excel-依向左而右、由上而下的順序在儲存格中輸入資料

如果你在一個儲存格中輸入資料後習慣按 Enter 鍵,則你可以在此更改為方向:右,並且在輸入前先選取要輸入資料的儲存格範圍。如此,便可以依你的習慣來輸入資料。

即輸入儲存格A2後,按 Enter 鍵,會自動選取右邊的儲存格B2,...,當儲存格F2輸入完成後,按 Enter 鍵,會自動跳到下一列的儲存格A3,如此,便能依向左而右、由上而下的順序在儲存格中輸入資料。

Excel-依向左而右、由上而下的順序在儲存格中輸入資料

不過特別要注意,當你更改了方向設定,將會影響其他工作表輸入資料時,也會由左至右輸入。

如果你在一個儲存格中輸入資料後習慣按 Tab 鍵,則不須執行上述更改方向的動作。只要在輸入前先選取要輸入資料的儲存格範圍,當輸入儲存格A2後,按 Tab 鍵,會自動選取右邊的儲存格B2,...,當儲存格F2輸入完成後,按 Tab 鍵,會自動跳到下一列的儲存格A3。如此,也能依向左而右、由上而下的順序在儲存格中輸入資料。

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

這是回應學校老師的一個練習範例。參考下圖,這是一個常見的成績表,如果想要計算每個區間中的各科平均該如何處理?如果計算各科平均時,想要依某個加權來計算總平均,又該如何處理?

Excel-使用小計功能產生不同時期的各科平均及各科加權平均(SUMPRODUCT)

先觀察上圖,每個區間的次數並不相同,在此要自動產生各個科目在這個區間的「平均」,並且計算各科的「加權平均」。(如下圖,還能以群組概念檢視資料)

Excel-使用小計功能產生不同時期的各科平均及各科加權平均(SUMPRODUCT)

參考以下的做法:

1. 選取儲存格A1:G20。

2. 選取[資料/大網]功能表中的「小計」。

Excel-使用小計功能產生不同時期的各科平均及各科加權平均(SUMPRODUCT)

3. 在[小計]對話框中設定:

(1) 分組小計欄位:區間(指第一次期中考、第二次期中考、期末考)

(2) 使用函數:平均值(相當於使用 AVERAGE 函數)

(3) 新增小計位置:國文、英文、數學、社會、自然(指這個個科目都要計算平均)

Excel-使用小計功能產生不同時期的各科平均及各科加權平均(SUMPRODUCT)

(4) 按下[確定]按鈕後,即可看到自動建立群組且依區間計算每個科目的平均值:

Excel-使用小計功能產生不同時期的各科平均及各科加權平均(SUMPRODUCT)

(5) 按一下第2層的「-」按鈕(此為摺疊,按下後會轉為「+」,此為展開按鈕)。

(6) 按著 Ctrl 鍵,分別選取各個平均值的各科平均分數。

(7) 設定各科平均的小數點位數為1位,並設定一個色彩。

Excel-使用小計功能產生不同時期的各科平均及各科加權平均(SUMPRODUCT)

(8) 計算加權平均

假設國文、英文、數學、社會、自然的加權分別為4、4、4、3、3。

儲存格H2:=SUMPRODUCT(C2:G2,{4,4,4,3,3})/SUM({4,4,4,3,3})

複製儲存格H2,貼至儲存格H2:H24。

Excel-使用小計功能產生不同時期的各科平均及各科加權平均(SUMPRODUCT)

最後,稍微調整一下格式設定,美化之後即可使用群組功能來檢視資料了。

Excel-使用小計功能產生不同時期的各科平均及各科加權平均(SUMPRODUCT)


【延伸學習】

(1)

公式儲存格H2:=SUMPRODUCT(C2:G2,{4,4,4,3,3})/SUM({4,4,4,3,3}),其中 {4,4,4,3,3} 是一種陣列的表示法。

(2)

注意喔!自動產生的小計(平均值),並不是使用你熟悉的 AVERAGE 函數,而是使用 SUBTOTAL 函數。值得你進一步研究。

Excel-使用小計功能產生不同時期的各科平均及各科加權平均(SUMPRODUCT)

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

和同事聊到手機上裝了很多的 App,卻不知道這些 App 會取用那些自己的隱私?我開玩笑說,即使知道了,你能採取那些有效的應變措施?會堅決放棄不用有過度侵犯隱私疑慮的 App 嗎?

不過,至少應該會查到這個 App 宣告要使用個人隱私存取的權限有那些。以 Android 手機為例,如果你使用電腦,在 Google Play 網站先找到你要的 App,畫面往下拉,在「其他資訊」區域中,點選「權限」。(本文以 Evernote App 為例)

找到每個手機App的隱私存取權限(以Android為例)

在此,即會顯示該 App 需要被授權存取那些個人隱私項目:

找到每個手機App的隱私存取權限(以Android為例)

如果是在手機中操作,進入 Google Play App 中,找到 Evernote App,點選「權限詳細資訊」,就會出現這個 App 的版本資訊和要求授權的存取項目。(通常這些存取項目會根據你使用的 App 版本而有所不同)

 

或是你已經安裝了這個 App,也可以到「設定/應用程程式」中,找到並點選這個 App,在應用程式資訊中也會顯示這個 App 的版本資訊,將畫面往上滑動,即可看到不太一樣的 App 存取權限資訊。

 

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

在 Excel 中,如果想要依據一個包含 4 個區域、5 種費率的對照表(如下圖左),依不同重量來自動計算金額(如下圖右),該如何處理?

本例想要依區域和重量,自動對應出費率,才能試算金額。

Excel-在VLOOKUP函數中使用雙條件來查詢(INDIRECT)

【準備工作】

定義以下四個名稱:

北:=工作表1!$B$2:$C$6

中:=工作表1!$B$7:$C$11

南:=工作表1!$B$12:$C$16

東:=工作表1!$B$17:$C$21


【公式設計與解析】

儲存格G2:=VLOOKUP(F2,INDIRECT(E2),2)

INDIRECT(E2):將儲存格E2的內容(北、中、南、東)轉換為真實的儲存格位址(已先定義名稱)

透過 VLOOKUP 函數利用查表的概念找到對應的費率。

本例中使用的雙條件為:儲存格F2(重量)、INDIRECT(E2)(區域)。利用定義好的名稱,可以讓使用很短的公式,即可完成查詢的工作。

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

有同仁問到常用的 Google 日曆,可否輸出成一個檔案以方便書面上使用?

Google日曆在電腦和手機上檢視,操作上已是十分方便,也有提供列印的功能。

Google日曆輸出為PDF檔以方便書面使用

如果你想要將日曆內容轉成檔案,Google 日曆已預設可以輸出 PDF 檔。使用時可以開啟 Google 日曆後,按 Ctrl+P 鍵,會進入日曆列印預覽。最下方有一個「另存新檔」的功能。

Google日曆輸出為PDF檔以方便書面使用

這個存檔功能即會儲存日曆為 PDF 檔:

Google日曆輸出為PDF檔以方便書面使用

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

前幾天的二篇 Excel 文章,都是使用「陣列公式」來處理:

Excel-運用雙條件從資料表摘要資料(OFFSET,INDIRECT,SUMPRODCUT)

Excel-藉由定義名稱轉換表格來摘要資料(陣列公式,OFFSET,INDIRECT)

這次不要使用陣列公式,而是使用 SUMPRODUCT 函數來運算。在下圖右的上下二個摘要表中,上半部是依據一個條件來篩選資料,下半部是依據二個條件來篩選資料。請自行對照以上二篇文章比較其差異。

Excel-根據雙條件來摘要資料(SUMPRODUCT,OFFSET,LARGE)


【公式設計與解析】

為了方便說明,先定義名稱。選取儲存格A1:E25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、客編、品名、規格、數量。

使用 SUMPRODUCT 函數可以不用使用「陣列公式」,有些人對於陣列公式望而卻步或是一之半解無法活用。


(1) 依據一個條件來篩選資料

為方便說明使用一個輔助欄位(K欄)。

儲存格K3:=SUMPRODUCT(LARGE((客編=$H$1)*ROW(日期),ROW(1:1)))-1

客編=$H$1:在 SUMPRODUCT 函數中的「客編」陣列中,判斷是和儲存格H1相同,傳回 TRUE/FALSE 陣列。

(客編=$H$1)*ROW(日期):傳回符合「客編」陣列中是和儲存格H1相同者的列號(以日期陣列來取得列號)所組成的陣列。

LARGE((客編=$H$1)*ROW(日期),ROW(1:1)):利用ROW(1:1)來依序「由大到小」取得上式中列號陣列的數值。ROW(1:1)=1向下複製時會產生ROW(2:2)=2→ROW(3:3)=3→...。

儲存格G3:=IFERROR(OFFSET($A$1,K3,,,),"")

儲存格H3:=IFERROR(OFFSET($C$1,K3,,,),"")

儲存格I3:=IFERROR(OFFSET($D$1,K3,,,),"")

儲存格J3:=IFERROR(OFFSET($E$1,K3,,,),"")

複製儲存格G3:K3,貼至儲存格G3:K13。


(2) 依據二個條件來篩選資料

儲存格K17:
=SUMPRODUCT(LARGE((客編=$H$15)*(品名=$J$15)*ROW(日期),ROW(1:1)))-1

觀察上式,使用 (客編=$H$15)*(品名=$J$15) 來篩選雙條件的結果。

儲存格G17:=IFERROR(OFFSET($A$1,K17,,,),"")

儲存格H17:=IFERROR(OFFSET($D$1,K17,,,),"")

儲存格I17:=IFERROR(OFFSET($E$1,K17,,,),"")

複製儲存格G17:K17,貼至儲存格G17:K25。


【思考一下】

如何依據三個條件、四個條件來篩選資料?

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

大家在使用地圖的經驗,大多是用地址查地圖,最近剛好有人問到如何在地圖上查地址(門牌號碼)?如果以現有的 Google 地圖(https://maps.google.com.tw/)來看,在地圖上任一地點按一下右鍵,在功能表中選取「這是哪裡?」。

在地圖下方會顯示該地點的地址(假設剛好有門牌號碼存在)及座標:

點選這個地址,會顯示更細節的內容:

如果使用 Here 地圖(https://www.here.com),只要在地圖的任意位置上按右鍵,會在該處顯示該地點的地址(假設剛好有門牌號碼存在)。

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

唸國中的兒子上數學課時,數學老師提到費氏數列(Fibonacci number),回家和我討論到這件事。我想用 Excel 建構這個數列給他看。

參考:https://en.wikipedia.org/wiki/Fibonacci_number(以下部分圖片取自該網頁)

 

數列:0, 1, 1, 2, 3, 5, 8, 13, 21, 34, 55, 89, 144, ...

Excel-顯示較多位數的費氏數列(Fibonacci number)(RIGHT,LEFT,INT)

根據公式,這個數列其實就是由前二個的數值的和所構成,因此公式很簡單:

儲存格B4:=B2+B3

但是,在 Excel 中受限有效位數15位,在第15位以上時右側數值都只會以0顯示0(參考下圖左)。所以,在 F74 開始,就會顯示不精確的數值了。如何能顯示精確的數值呢?至少可以補足一個國中生的學習欲望!以下就來試試顯示 28 位數正確的數值(參考下圖右)。

Excel-顯示較多位數的費氏數列(Fibonacci number)(RIGHT,LEFT,INT)


【公式設計與解析】

主要做法是將超過 14 位數的數值將其分成二半,一半是 1 ~ 14位數,另一半是 15 ~ 28 位數。分開來加總,但是要注意 1 ~ 14位數的加總有可能進位到 15 ~ 28 位數部分再加總。

Excel-顯示較多位數的費氏數列(Fibonacci number)(RIGHT,LEFT,INT)

以下用 F77(儲存格C79)為例,:

(1) 計算F75和F76之 1 ~ 14 位數部分加總不含進位至15位數的和

=RIGHT(RIGHT(C77,14)+RIGHT(C78,14),14)

(2) 計算F75和F76之 1 ~ 14 位數部分加總之進位

=INT((RIGHT(C77,14)+RIGHT(C78,14))/10^14)

(3) 計算F75和F76之 15 ~ 28 位數部分的和

=LEFT(C77,LEN(C77)-14)+LEFT(C78,LEN(C78)-14)

最後,組合公式:

儲存格C79:=IF(AND(LEN(C77)>=15,LEN(C78)>=15), ((3)+(2))&(1),C77+C78)

複製儲存格C79,貼至儲存格C4:C134。


【補充說明】

完整公式,儲存格A79:=IF(AND(LEN(C77)>=15,LEN(C78)>=15),LEFT(C77,
LEN(C77)-14)+LEFT(C78,LEN(C78)-14)+INT((RIGHT(C77,14)+RIGHT(C78,14))/
10^14)&RIGHT(RIGHT(C77,14)+RIGHT(C78,14),14),C77+C78)

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

在 Excel 中,樞紐分析表/樞紐分析是非常重要的功能,特別為學生整理出網站中數篇和樞紐分析表/樞紐分析圖有關的文章。

樞紐分析

Excel-建立樞紐分析表的各種方法

Excel-列印樞紐分析表時依某個欄位項目分頁列印

Excel-使用合併彙算和樞紐分析表來彙總多個工作表

Excel-篩選資料:在樞紐分析表中使用交叉分析篩選器(免公式)

Excel-在樞紐分析表中自訂日期格式

Excel-藉助唯一值計算加總(VLOOKUP,SUMPRODUCT)

Excel-使用交叉分析篩選器進化樞紐分析

Excel-自訂樞分析表列標籤的順序

Excel-區隔人區隔天計算金額小計(SUMPRODUCT)

Excel-以公式計算樞紐分析表結果(SUMPRODUCT)

Excel-樞紐分析(動態儲存格範圍)

Excel-使用樞紐分析表工具建立摘要表

Excel-應用樞紐分析表

Excel-樞紐分析時對於空格的處理

Excel-樞紐分析表和SUMPRODUCT

Excel-資料分析表(SUMPRODUCT)

Excel-計算每月固定日期間的小計(SUMPRODUCT)

Excel-用公式取代樞紐分析(一)

Excel-用公式取代樞紐分析(二)

Excel-用公式取代樞紐分析(三)

Excel-用公式取代樞紐分析(四)

Excel-取得儲存格的色彩

Excel-資料驗證中使用跨工作表的來源資料

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

以下有四篇先前寫過和萬年曆有關的文章,不同的日期產生方式和呈現格式,要運用公式,也要使用設定格式化的條件功能,大家再自行比較一下。


(1) Excel-建立連續日期的月曆表

萬年曆


(2) Excel-萬年月曆(WEEKDAY,DAY,DATE)

萬年曆


(3) Excel-製作萬年月曆

萬年曆


(4) Excel-建立萬年曆

萬年曆

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

最近在整理資料時,發現幾篇很久以前的文章,拿出來玩味一下,發現以前還蠻熱血的!會去想這些可能自己用不到,但是可以訓練提升自己的程度。以下列舉幾篇,有興趣的人自行由標題點閱了!


(1) Excel-畫出特殊統計圖形

Excel-趣味應用 Excel-趣味應用

Excel-趣味應用 Excel-趣味應用

Excel-趣味應用 Excel-趣味應用

Excel-趣味應用 Excel-趣味應用


(2) Excel-製作較多位數的乘法

Excel-趣味應用


(3) Excel-計算10位元數以上的乘法

Excel-趣味應用


(4) Excel-製作tan()函數的圖形

Excel-趣味應用


(5) Excel-將複數各個次方分解實數和虛數並繪成圖

Excel-趣味應用


(6) Excel-基本邏輯運算練習

Excel-趣味應用

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

有網友問到:給予一個預定的日期,其前10天為警示日期,如何在警示日期到達的那天,自動顯示紅色予以警示?

當在儲存格C2中輸入日期時,儲存格B2要顯示其前10天的日期,所以:

儲存格B2:=C2-10

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

Excel-日期到達時顯示預設的文字色彩

接下來要處理警示日期到來時要顯示紅色:

1. 選取儲存格B2:B22。

2. 選取[常用/樣式]功能表的「設定格式化的條件/新增規則」。

3. 設定以下規則:

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

編輯規則:=B2<=TODAY()

設定文字色彩:紅色。

如果在資料表中你的日期會不斷的增加,可以複製儲存格,往下繼續貼上即可。

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

有網友問到:在 Excel 中如何輸入年和月的數值後後,自動產生該月的所有日的數值和對應的星期幾?

Excel-根據年和月來自動產生該月的日期和星期幾(DATE,TEXT,ROW)


【公式設計與解析】

(1) 儲存格B2:=IF(ROW(1:1)<=DAY(DATE($A$2,$A$4+1,0)),ROW(1:1),"")

DAY(DATE($A$2,$A$4+1,0)):根據儲存格A2(年)和儲存格A4(月),求出該月的日數。

ROW(1:1)=1,往下複製公式時會產生 ROW(2:2)=2→ROW(3:3)=3→...。如果 ROW 函數傳回值小於或等於該月的日數,則顯示 ROW 函數傳回的數值(日),否則顯示空白。

(2) 儲存格C2:=IF(B2="","",TEXT(DATE($A$2,$A$4,B2),"[$-404]aaaa;@"))

DATE($A$2,$A$4,B2):組合年、月、日為一個日期。

TEXT(DATE($A$2,$A$4,B2),"[$-404]aaaa;@"):在 TEXT 函數中使用參數「"[$-404]aaaa;@"」,可以將上式的日期設定格式為星期X。

假如B欄儲存格為空白,則C欄也跟著顯示空白。

(3) 複製儲存格B2:C2,貼至儲存格B2:C32。

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

1 23

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼