贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201510 (44)

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

延續前一篇文章:

Excel-在月曆型式中顯示排班結果(SUMPRODUCT,OFFSET,DATE,ROW)

如果想要給每一位員工一張個人的值班表,該如何處理?


【公式設計與解析】

作法一:標示[班別]姓名

Excel-在月曆型式中顯示排班結果(個人)(SUMPRODUCT,OFFSET,DATE,ROW)

先將日期、早班、中班、晚班各自的範圍內的儲存格定義名稱為:日期、早班、中班、晚班。(選取排班資料清單的所有儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」。)

儲存格A8:=IFERROR("[早]"&OFFSET($J$2,SUMPRODUCT((日期=DATE
($A$1,$G$1,A7))*(早班=$D$1)*ROW(日期))-2,,,),"")

若該日期沒有排到班,則公式會產生錯誤訊息,所以藉助 IFFERROR 函數來顯示空白。

儲存格A9:=IFERROR("[中]"&OFFSET($K$2,SUMPRODUCT((日期=DATE
($A$1,$G$1,A7))*(中班=$D$1)*ROW(日期))-2,,,),"")

儲存格A10:=IFERROR("[晚]"&OFFSET($L$2,SUMPRODUCT((日期=DATE
($A$1,$G$1,A7))*(晚班=$D$1)*ROW(日期))-2,,,),"")

複製儲存格A8:A10,貼至所有日期的儲存格中。


作法二:標示[班別]

Excel-在月曆型式中顯示排班結果(個人)(SUMPRODUCT,OFFSET,DATE,ROW)

儲存格A8:=IFERROR(IF(SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*(早班=
$D$1)*ROW(日期)),"早班",""),"")

儲存格A9:=IFERROR(IF(SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*(中班=
$D$1)*ROW(日期)),"中班",""),"")

儲存格A10:=IFERROR(IF(SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*(晚班=
$D$1)*ROW(日期)),"晚班",""),"")

複製儲存格A8:A10,貼至所有日期的儲存格中。

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

有網友想要將一個 Excel 的排班清單(下圖右)直接在一個月曆中顯示(下圖右),該如何處理?

Excel-在月曆型式中顯示排班結果(SUMPRODUCT,OFFSET,DATE,ROW)


【公式設計與解析】

先將日期範圍內的儲存格定義名稱為:日期。

以下以 2015/10/4 為例:

儲存格A8:="[早]"&OFFSET($J$2,SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*
ROW(日期))-2,,,)

DATE($A$1,$G$1,A7):取得每個儲存格所代表的日期。

SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*ROW(日期)):取得儲存格A8所代表日期在資料清單中的『列號』。

OFFSET($J$2,SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*ROW(日期))-2,,,):依據上式的『列號』,代入 OFFSET 函數查得J欄中對應的儲存格內容。

同理:

儲存格A9:="[中]"&OFFSET($K$2,SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*
ROW(日期))-2,,,)

儲存格A10:="[晚]"&OFFSET($L$2,SUMPRODUCT((日期=DATE($A$1,$G$1,A7))*
ROW(日期))-2,,,)

複製儲存格A8:A10,貼至所有日期的儲存格中。


【延伸處理】

如果在排班表中不是每個日期都有排班,則可以修改公式:

儲存格A8:=IFERROR(原公式,"")

即:

儲存格A8:=IFFERROR("[早]"&OFFSET($J$2,SUMPRODUCT((日期=
DATE($A$1,$G$1,A7))*ROW(日期))-2,,,),"")

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

當在 Excel 中取得一個運動會的報名表(如下圖),除了各班的所有學生基本資料之後,還有各個比賽項目,其中的值為 TRUE 者代表有報名,FALSE 代表沒有報名。

如何能快速計算各個比賽項目的各班男生/女生的參數人數?

Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)


1. 使用樞紐分析表和交叉分析篩選器

以跳高項目為例,當你插入一個樞紐分析表,設定如下:

將欄設為:『性別』欄位;列設為:『班級編碼』欄位;值設定:『跳高』欄位。

你看到的只是各班男生人數和女生人數,並無法呈現 TRUE(報名) 的數量。

Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)

所以你要選取[分析/篩選]功能區中的「插入交叉分析篩選器」。

接著,勾選「跳高」:

Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)

當你按一下「TRUE」,此時顯示的數值即為各班男生/女生的報名人數。

Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)


2. 設計公式來計算

以比賽項目:跳高為例,先將『班級編碼』、『性別』、『跳高』三個欄位的所有資料,定義名稱:班級編碼、性別、跳高。

Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)

輸入公式:

儲存格O2:=SUMPRODUCT((班級編碼=$N2)*(性別=O$1)*(跳高=TRUE))

(班級編碼=$N2)*(性別=O$1)*(跳高=TRUE):「*」運算可以將三個條件執行邏輯 AND 運算,即三個條件皆符合者,才會取出來計算乘積和。而在「*」運算時,會將傳回值 TRUE/FALSE 結果轉換為 1/0

提醒:『跳高=TRUE』不可寫出『跳高="TRUE"』,TRUE 在此是一個布林代數值,而非文字 TRUE。

然後,複製儲存格O2,貼至儲存格O2:P48。


【進階處理】

如果想要一次就取得所有比賽項目的各班男生/女生的參賽人數,該何處理?(參考下圖)

Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)

設計公式前要注意:運算人數的資料表欄位稱必須要和原始資料的欄位名稱完全相同。

接,除了已經定義的名稱:班級編碼、性別之外,再將本例中的『一百、跳高、跳遠、一仟五、聖火隊、助理裁判』每個欄位的所有資料,定義名稱:一百、跳高、跳遠、一仟五、聖火隊、助理裁判。

設計公式,儲存格P2:
=SUMPRODUCT((班級編碼=$N2)*(性別=$O2)*(INDIRECT(P$1)=TRUE))

INDIRECT(P$1)=TRUE:其中 INDIRECT(P$1),巧妙的將儲存格P1中的欄位名稱轉換為實際儲存格範圍(其中每個欄位中的儲存格範圍都已先定義好名稱了)。

然後,複製儲存格P2,貼至儲存格P2:U4。


【後記】

你有發現嗎?只要一個公式即能求出各班的男生/女生參加各個比賽的人數,其實是有一些巧妙的安排的,例如:

(1) O欄中『男』和『女』是分二列安排。

(2) 運算人數的資料表欄位稱和原始資料的欄位名稱完全相同。

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

在 Excel 中有一個日期是取貨的期限,網友想要在期限前三天內予以警示,但是如果在D欄中已有『提件日期』,則取消警示,該如何處理?

參考下圖,例如今天的日期是2015/10/29,則項目:007~012都要警示,但是009和011因為已有『提件日期』,則取消警示。大部分的人做法都是使用「設定格式化的條件」來處理。

image

 

【參考設計做法】

1. 選取儲存格C1:C18。

2. 新增格式化的設定,設定如下:

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

編輯規則:=(B2-TODAY()<=3)*(B2-TODAY()>=0)*ISBLANK(D2)

格式:設定儲存格底色

image

公式:=(B2-TODAY()<=3)*(B2-TODAY()>=0)*ISBLANK(D2)

(B2-TODAY()<=3)*(B2-TODAY()>=0):判斷日期是否介於今日到前三日。

ISBLANK(D2):判斷儲存格B2是否為空白。

 

換個做法 

如果要呈現如下的標示,省略一個警示欄位,直接將整列顯示不同色彩。

image

1. 選取儲存格A1:C18。

2. 新增格式化的設定,設定如下:

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

編輯規則:=($B2-TODAY()<=3)*($B2-TODAY()>=0)*ISBLANK($C2)

格式:設定儲存格底色

 

思考一下 

比較以上二個公式,做法一致,但是呈現效果不一樣。特別要注意儲存格的位址參照方式。

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

延續上篇文章:Excel-在統計圖中更改時間標籤的間距,參考下圖,如果想要將多個工作表中的資料共用一個圖表(折線圖),該如何處理?

在下圖中,原來的表1~表7是放在多個工作表中,現在把它集合在一起,想要透過儲存格I2中的下拉式選單來選取表的名稱,而折線圖自動會顯示該表的數值內容。

Excel-利用下拉式選單顯示多個統計圖(OFFSET,資料驗證)


【操作與解析】

在儲存格I2中要使用下拉式選單,可以透過「資料驗證」,設定其準則:

儲存格內允許:清單

來源:表1,表2,表3,表4,表5,表6,表7

Excel-利用下拉式選單顯示多個統計圖(OFFSET,資料驗證)

接著,你藉由表1來產生一個折線圖(相關步驟在此省略),然後點選折線圖,觀察資料編輯列中的公式。如下:

=SERIES(圖表!$B$15,圖表!$A$16:$A$27,圖表!$B$16:$B$27,1)

其中『圖表!$B$15』和『圖表!$B$16:$B$27』必須改為動態。

Excel-利用下拉式選單顯示多個統計圖(OFFSET,資料驗證)

因此,要定義二個名稱:

DATA1:=OFFSET(圖表!$H$2,0,MATCH(圖表!$D$17,圖表!$H$2:$N$2,0)-1,1,1)

用以取代:『圖表!$B$15』

DATA2:=OFFSET(圖表!$H$3,0,MATCH(圖表!$D$17,圖表!$H$2:$N$2,0)-1,12,1)

用以取代:『圖表!$B$16:$B$27』

注意:公式中使用的位址要設定為絶對參照,領域要設為「活頁簿」。

Excel-利用下拉式選單顯示多個統計圖(OFFSET,資料驗證)

接著,選取折線圖,將公式:

=SERIES(圖表!$B$15,圖表!$A$16:$A$27,圖表!$B$16:$B$27,1)

改為:

=SERIES(折線圖.xlsx!DATA1,圖表!$A$16:$A$27,折線圖.xlsx!DATA2,1)

如此,便能利用儲存格I5的下拉式選單,來動態顯示想要的資料表的折線圖了。

Excel-利用下拉式選單顯示多個統計圖(OFFSET,資料驗證)

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

網友想要依據進場和出場的停車時間來計算停車費,規則如下(參考下圖):

(1) 停車未滿30分鐘收費0元

(2) 超過30分鐘未滿60分鐘收費30元

(3) 三小時以內每30分鐘收費15元

(4) 第四小時開始每30分鐘收費20元

Excel-依據不同時段收費標準計算停車費(SUMPRODUCT,ROW)

參考範例:

Excel-依據不同時段收費標準計算停車費(SUMPRODUCT,ROW)


【公式設計與解析】

為了方便說明,設計了輔助欄位:C欄和D欄。

1. 計算停車的分鐘數

儲存格C2:=INT((B2-A2)*60*24)

2. 分鐘數轉換為第幾個30分鐘

儲存格D2:=INT(C2/30)+1

3. 計算停車費

儲存格E2:
=(D2>1)*30+SUMPRODUCT((D2>=ROW($3:$6))*15)+(D2>6)*INT(D2-6)*20

該公式由三個部分組成:

(1) 30分鐘至60分鐘之間停車費:(D2>1)*30

(2) 60分鐘至180分鐘之間停車費:SUMPRODUCT((D2>=ROW($3:$6))*15)

(3) 180分鐘以上停車費:(D2>6)*INT(D2-6)*20

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

有網友問到:在 Excel 中根據下圖中的資料表繪出一個統計圖,觀察座標軸上的刻度,是以每5分鐘為單位標記。如何讓座標軸上是以每15分鐘為單位標記?

Excel-在統計圖中更改時間標籤的間距

 

【參考做法】

在類別座標軸上按右鍵,選取[座標軸格式]選項。

Excel-在統計圖中更改時間標籤的間距

觀察標籤區中的「標籤與標籤之間的間距」,目前是設定為:自動。

Excel-在統計圖中更改時間標籤的間距

請選取:指定間隔的刻度間距,並將數值改為:3。

如此,便可以讓刻度以 15 分鐘為單位標記了。

Excel-在統計圖中更改時間標籤的間距

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

雲端應用通常是透過在電腦使用瀏覽器或是在行動裝置使用 App 連網後,對檔案或資料夾進行共用、分享等操作。

而當你在電腦中,進入 Dropbox 雲端資料夾,並且在登入的狀態下,在某個檔案中按右鍵,可以發現有四個 Dropbox 的功能選項,讓你可以很方便的操作:分享 Dropbox 連結、在 Dropbox.com 上檢視、檢視過去版本、檢視留言。也就是在檔案總管即可完成在瀏覽器的部分動作。

Dropbox-編輯文件時就可以同時分享和留言

現在,如果你在 Dropbox 資料夾中開啟一個可以編輯的檔案,例如開啟一個 Word 檔,你會發現新增了一個 Dropbox 圖示(如下圖),在此你可以執行:分享連結、留言、檢視檔案版本紀錄、共享資料夾設定。你可以不用切換到檔案總管,再執行這些功能。

Dropbox-編輯文件時就可以同時分享和留言

【例如】

你可以按一下「分享連結」,即可取得該檔案被分享的網址。你不需連到網站或是開啟檔案總管來設定。

你可以在編輯時也同時輸入你的留言(輸入完成要按 Post)。也就是說,在編輯時也同時可以和其他使用者互動、溝通。

Dropbox-編輯文件時就可以同時分享和留言

另一個共享的使用者,可以同時和你互動留言。互動的過程,可以同步在網頁上看到。只要共享的使用者要編輯這個檔案時,都可以看到先前互動的結果。

Dropbox-編輯文件時就可以同時分享和留言

Dropbox 除了讓你分享、共用檔案之外,也一步一步讓你更方便來操作這些動作,讓你在一個工作狀態中就可以同時完成雲端上的操作。


【這樣也可以】

即使你沒有要和他人共享檔案,你也是可以使用留言功能來提醒自己關於這個檔案要注意的地方。

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

網友問到一個問題:如何在 Excel 檔案含有人員和郵件地址的資料表中查到的結果(如下圖),可以直接點選打開 Outlook 新增郵件?

Excel-點選含有Email郵件地址儲存格時,自動開啟Outlook新增郵件


【公式設計與解析】

通常你在 Excel 的儲存格中輸入一個網址或是Email郵件地址時,當按下 Enter 鍵,即會產生這個超連結。

Excel-點選含有Email郵件地址儲存格時自動開啟Outlook新增郵件

當你在這個超連結中,按一下右鍵,選取[編輯超連結]選項。

Excel-點選含有Email郵件地址儲存格時自動開啟Outlook新增郵件

可以看到你可以指定這個超連結是連結到指定的網址,當然也可以指定某個郵件地址。

Excel-點選含有Email郵件地址儲存格時自動開啟Outlook新增郵件

如果你在儲存格中輸入:test01@ms11.hinet.net,

在超連結中會看到:mailto:test01@ms11.hinet.net

Excel-點選含有Email郵件地址儲存格時自動開啟Outlook新增郵件

所以得知:如果要連結郵件地址,可以使用『mailto:』協定。因此,套用到公式中,

儲存格E2:=HYPERLINK("mailto:" & VLOOKUP(D2,A2:B17,2,FALSE),
VLOOKUP(D2,A2:B17,2,FALSE))

VLOOKUP(D2,A2:B17,2,FALSE):在儲存格A2:B17中查詢儲存格D2的內容(人員),對應第2欄,傳回一個郵件地址。

再利用 HYPERLINK 函數建立超連結。

Excel-點選含有Email郵件地址儲存格時自動開啟Outlook新增郵件

當點選儲存格E2的郵件地址時,即會開啟 Outlook 新增郵件。

Excel-點選含有Email郵件地址儲存格時自動開啟Outlook新增郵件

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

你有試著在 Google Chrome 所開啟的分頁標籤上按右鍵?以 Google Chrome 46 版為例,將會看到:新增分頁、重新載入、複製、固定分頁、關閉分頁音訊、關閉分頁、關閉其他分頁、關閉右邊的分頁、重新開啟已關閉的分頁、將所有分頁加入書籤。

以下特別介紹其中三種:

固定分頁:該分頁會被移至最左側,並且將分頁縮到最小,看不到分頁名稱,並且你無法使用關閉圖示。若要還原,則在固定分頁上按右鍵,選取[取消固定分頁]選項。

image

關閉分頁音訊:以 YouTube 影片分頁為例,當你選取關閉分頁音訊時,該分頁不會發出任何聲音,標籤上的符號可以看出沒有聲音。觀察 YouTube 網站時,影片仍在播放,而聲音圖示也沒有被關到最小。這個功能可以在不切換分頁的狀況下,快速關閉分頁中發出的聲音。如果要重新播放出聲音,則在標籤上按右鍵,再選取「開啟分頁音量」。

 

重新開啟已關分頁:讓你快速開啟先前關掉的分頁,而且你每按一次,會『倒著順序』再次開啟原關閉的檔案,多按幾次來試試吧!(你也可以使用快速鍵:Ctrl+Shift+T)

image

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

有同事遇到一個問題:以 Word 檔案為例,想要抓取螢幕中文件的內容另存成一張圖片,要在其他地方使用,但是該張圖片中的內容因為在畫面中縮小比例,以致於抓出來的圖片不夠清楚,而且放大後也會產生失真,不知該如何處理?

要如何幫他呢?後來想到的是,把他的檔案在一部螢幕解析度比較高的電腦打開,例如:原來螢幕解析度為 1360X780,轉換到螢幕解析度 1920X1080 的電腦,抓圖之後存檔再開啟,檢視其內容,的確是較清楚多了。

較不清楚的圖片(這是1360X780),點選可看完整圖片。

點選後放大

較清楚的圖片(這是1920X1080),點選可看完整圖片。

點選後放大

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

網友想要取用 Excel 中的一個含有樓層、房號、入住時間、退房時間的住宿資料表,依據不同樓層,將有住宿的時間內的日期,在該房號中自動標示出來,該如何處理?

Excel-根據日期區間自動標示色彩(設定格式化的條件)

就像下圖這樣,當住宿的總表填入資料後,該樓層、房號對應的日期會被標示出來。

Excel-根據日期區間自動標示色彩(設定格式化的條件)

參考以下的做法:

1. 定義儲存格名稱

選取總表的儲存格A1:E23,按 Ctrl+Shift+F3 鍵,勾選頂端列,定義名稱:樓層、房號、入住時間、退房時間。

特別注意:每個名稱的領域都要設定為「活頁簿」。

Excel-根據日期區間自動標示色彩(設定格式化的條件)

2. 設定格式化的條件(以4F工作表為例)

(1) 選取儲存格B2:P13。

(2) 選取[常用/樣式]功能中的[設定格式化的條件/新增規則]選項。

(3) 編輯格式化規則

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

編輯規則:=SUMPRODUCT((房號=$A2)*(入住時間<=B$1)*(退房時間>=B$1))

格式:在儲存格填滿中設定想要的色彩

Excel-根據日期區間自動標示色彩(設定格式化的條件)

(4) 複製格式到其他工作表

選取「4F工作」中的選取儲存格B2:P13,按一下「複製格式」按鈕,分別在「2F工作表」和「3F工作表」的儲存格B2上按一下,即完成複製格式工作。

大功已經告成。


【補充說明】

一般住宿時,可能最後一天是要退宿,不算是住宿日,所以可修改公式:

編輯規則:=SUMPRODUCT((房號=$A2)*(入住時間<=B$1)*(退房時間>B$1))

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

在校內教職員研習後的二篇文章繼續發酵著:

Google-使用翻譯和拍照功能幫你輸入文字

在Google文件中使用語音輸入來建立文字內容

有同仁問到如果要用手機唸一唸就可以在電腦中取得這些文字,該如何處理?我想的到的,還是要藉助『Google 文件』。參考以下的步驟:

1. 你先在電腦中,連線至 Google 硬碟,再新增一個 Google 文件。

使用手機當成Google文件的文字輸入工具(用唸的輸入文字)

2. 接著在你的手機中,進入 Google 雲端硬碟 App,點選這個新增的文件。

你可以看到有另一人(在電腦)在檢視這個文件,在文件中點一下,進入編輯狀態。

3. 按一下輸入法中的麥克風,進入語音輸入狀態。

使用手機當成Google文件的文字輸入工具(用唸的輸入文字) 使用手機當成Google文件的文字輸入工具(用唸的輸入文字)

5. 此時,你可以開始唸出想要的文字。

使用手機當成Google文件的文字輸入工具(用唸的輸入文字) 使用手機當成Google文件的文字輸入工具(用唸的輸入文字)

6. 同一時間看著你的螢幕,那些字幾乎是同步的出現在電腦上。(文字旁會標示誰在輸入)

既然電腦上已有這些文字,即可隨你後續的操作了。

使用手機當成Google文件的文字輸入工具(用唸的輸入文字)

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

先前的文章(Google-使用翻譯和拍照功能幫你輸入文字)中提到在教職員研習時,介紹了使用語音和拍照方式來輸入文字,獲得同仁的迴響。也有同事問到如果在 Google 翻譯中輸入語音之後,還要複製/貼上到 Word 檔中,整個過程如果是比較長時,會有些不方便之處,還有更方便的方法嗎?

若想要更直接的方式,我想還是藉助 Google文件 的語音輸入功能是不錯的選擇。

當你使用 Google 翻譯的介面來使用語音輸入時,可以不用輸入帳號即可使用,但是內容無法即時儲存,如果你使用 Google 文件的語音輸入,則可以將內容即時儲存,並且下載成 Word 檔。

使用方法也很簡單:

1. 先在 Google 雲端硬碟中新增一個Google的文件。

2. 點選[工具]功能表,再點選「語音輸入」選項。

在Google文件中使用語音輸入來建立文字內容

3. 在出現的麥克風圖示上按一下,你就可以用念方式的來輸入文字。

在Google文件中使用語音輸入來建立文字內容

4. 等到你暢所欲『唸』之後,文字也依依跑出來了。

在Google文件中使用語音輸入來建立文字內容

5. 輸入完成之後,點選[檔案]功能表的[下載格式]功能,再點選「Microsoft Word」。即可將這些內容下載為一個 Word 檔。

在Google文件中使用語音輸入來建立文字內容

所以,你只要有網路,並且登入 Google 雲端硬碟,在 Google 文件中輸入資料應該是一件很方便的事。而且輸入完成之後直接可以下載成 Word檔,便可以和你現在正在編輯的文件接軌,使用上應該算是便利的。

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

(進階版請參考:http://isvincent.pixnet.net/blog/post/46709338)

在 Excel 中,如下圖的資料表,如果想要根據『人員』和『項目』交叉得到一個數值,只要使用類似 INDEX 函數即可完成,但是觀察這個資料表,其中有的項目具有多個重覆,如果要將某個人員的所有相同項目予以加總,該如何處理?

Excel-查表後多欄位加總(SUMPRODUCT,OFFSET,MATCH)

 

【公式設與解析】

一般使用 INDEX 函數可以很容易求得『人員:卯、項目:甲』的對應結果(儲存格C5):

儲存格M3:=INDEX(B2:J13,MATCH(M1,A2:A13,0),MATCH(M2,B1:J1,0))

但是因為甲有多個項目,如何在一個公式中將多個予以加總?

改用下列公式,儲存格M3:

=SUMPRODUCT(OFFSET(A1,MATCH(M1,A2:A13,0),1,,9)*(B1:J1=M2))

MATCH(M1,A2:A13,0):利用 MATCH 函數,求得儲存格M1(卯)在儲存格A2:A13中位於第幾個。本例傳回:4。

OFFSET(A1,MATCH(M1,A2:A13,0),1,,9):在 OFFSET 函數中,利用上式的傳回值,取得儲存格M1所在的列資料範圍,本例傳回:儲存格B5:J5。

最後,透過 SUMPRODUCT 函數,利用條件 B1:J1=M2,取出儲存格B5:J5中的第 2,4,7 個內容。公式中的「*」乃執行邏輯 AND 運算,運算時會將傳回值 TRUR/FALSE 轉換為 1/0

Excel-查表後多欄位加總(SUMPRODUCT,OFFSET,MATCH)

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

最近上教職員建置教學平台的相關研習課程時,先教了如何快速將文件數位化,其中最根本的是輸入文字。結果反應熱烈,因為耳麥和手機是手邊舉手可得的工具,真的讓你的輸入速度好幾倍增。

特別介紹了先前文章所提到的:使用翻譯和拍照功能幫你輸入文字。你只要在電腦中插上耳麥或是使用手機的語音輸入和照相機功能,便可以讓文字輸入變快。對著手機『用唸的』來輸入文字,現在變成我在手機上輸入大量文字,非常實用的方式了。有大量文字需求時,絶對比用按的或是用寫的來的快。


(1) 藉由 Google 翻譯,唸文字即可完成輸入文字

參考:教師和行政利用Goolge文件的語音輸入功能來加快輸入速度並減少打字的辛苦

Google-使用翻譯和拍照功能幫你輸入文字

Google-使用翻譯和拍照功能幫你輸入文字 Google-使用翻譯和拍照功能幫你輸入文字


(2) 拍張照片上傳 Google 雲端碟,即可在 Google 文件中取出文字

參考:利用將照片上傳Google雲端硬碟就可以取出其中的文字

Google-使用翻譯和拍照功能幫你輸入文字

Google-使用翻譯和拍照功能幫你輸入文字

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

有同仁想要在如下圖的 Excel 資料表中,用勾選的方法來決定某一天是否要列入小計,該如何處理?

要做到勾選動作,可以使用開發人員功能表的「核取方塊表單控制項」,所以你得先在功能表中加入「開發人員」功能表。

Excel-依勾選狀況計算小計(SUMPRODUCT,核取方塊表單控制項)

 

【操作與公式設計】

首先,選取[開發人員/控制項]功能表中選取[插入]選項 ,再選取「核取方塊」控制項。

接著,在欄位名稱:星期一的右側置放一個核取方塊,並且設定核取方塊的文字標籤空白。

Excel-依勾選狀況計算小計(SUMPRODUCT,核取方塊表單控制項)

然後,在這個控制項物件上按右鍵,選取[控制項格式]選項。

Excel-依勾選狀況計算小計(SUMPRODUCT,核取方塊表單控制項)

在[控制項格式]對話框中設定:

值:核取,

儲存格連結:$B$1。

此時,儲存格B1會顯示:TRUE。

Excel-依勾選狀況計算小計(SUMPRODUCT,核取方塊表單控制項)

如此,再次重覆上述步驟四次,共建立 5 個控制項,將儲存格B1:F1均設定為 TRUE

Excel-依勾選狀況計算小計(SUMPRODUCT,核取方塊表單控制項)

再來,設定計算公式:

儲存格G3:=SUMPRODUCT(B3:F3*$B$1:$F$1)

複製儲存格G3,貼至儲存格G3:G25。

Excel-依勾選狀況計算小計(SUMPRODUCT,核取方塊表單控制項)

接著,設定未勾選的日期數字予以淡化處理。

1. 在[常用/樣式]功能表中選取[設定格式化的條件/新增規則]選項。

2. 按一下[新增規則]按鈕。

3. 設定以下規則:

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

編輯規則:=B$1=FALSE

格式:字型色彩設定為「灰色」

Excel-依勾選狀況計算小計(SUMPRODUCT,核取方塊表單控制項)

如此,便可大功告成。

當你取消選取某一天時,該天的數字會被淡化,而且不會被列入小計。

Excel-依勾選狀況計算小計(SUMPRODUCT,核取方塊表單控制項)

 

【補充說明】

要開啟「開發人員」功能表,可以選取[檔案/選項]功能,然後在[自訂功能區]標籤下,勾選「開發人員」。

Excel-依勾選狀況計算小計(SUMPRODUCT,核取方塊表單控制項)

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

有網友想要將在 Excel 中如下圖左的原始資料表(看起來像電話號碼)轉換為如下圖右的資料格式,該如何處理?

觀察下圖,看起來是要將『(』消掉,然後將『)』轉換為『-』,其餘不變。

Excel-轉換資料格式(SUBSTITUTE,VALUE,數值格式設定)

以下分為二個部分來設計公式。

1. 轉換為文字型態

儲存格C2:=SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","-")

SUBSTITUTE(A2,"(",""),"):將『(』置換為空白。

SUBSTITUTE(SUBSTITUTE(A2,"(",""),")","-"):將上式結果中的『)』轉換為『-』。

其結果是一個「文字」型態。複製儲存格C2,貼至儲存格C2:C19。


2. 轉換為數字型態

儲存格C2:
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"-",""))

先利用三個 SUBSTISTUTE 函數,將『(、)、-』全部消除,再將這個文字結果使用 VALUE 函數轉換為數值格式。

接著設定儲存格C2的數值格式,自訂為:???-???-????

其結果是一個「數字」型態。複製儲存格C2,貼至儲存格C2:C19。

Excel-轉換資料格式(SUBSTITUTE,VALUE,數值格式設定)

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

觀察同事在使用 Excel 時,操作速度一直快不起來,我建議要適時的使用快速鍵,例如:當選取了儲存格範圍後,要執行加總時,除了使用[加總]按鈕之外,你也可以按一下『Alt+=』,由 Excel 幫你填人加總公式。

Excel-使用快速鍵和通用選單按鍵

已有提供的快速鍵,多用就會多記,熟了自然就能生巧。例如:

(1) 要將檔案另存新檔時,按 F12 鍵,會比移動滑鼠來點選功能表選單和選項來的快。

(2) 按下 Ctrl+1 鍵,可以快速開啟[儲存格設定]對話框。

不過,不是你想要的動作,都配有快速鍵,有一個通用的方式,倒是可以試試。

例如,我要將一段儲存格範圍設定一個名稱,你可以這樣做:

(1) 按著 Alt 鍵,可以看到每個功能表的 Hot Key。

Excel-使用快速鍵和通用選單按鍵

(2) 按一下『M』鍵,切換到「公式」功能表。

Excel-使用快速鍵和通用選單按鍵

3. 按一下『N』鍵,即可開啟[名稱管理員]對話框。

Excel-使用快速鍵和通用選單按鍵

如果,這是你常要使用到的功能,你只要依序按下按鍵:Alt→M→N,即可開啟[名稱管理員]對話框。相信,在熟能生巧之下,可以秒殺這個動作的。

例如:當選取儲存格A1:A14,依序按下按鍵:Alt→M→U→A,Excel 會幫你填上 AVERAGE 函數,並且連儲存格範圍都已加入。

Excel-使用快速鍵和通用選單按鍵

例如:當選取儲存格A1:A14:

(1) 依序按下按鍵:Alt→H→S→S,Excel 會將儲存格A1:A14的內容由小到大排序。

(2) 依序按下按鍵:Alt→H→S→O,Excel 會將儲存格A1:A14的內容由大到小排序。

(3) 依序按下按鍵:Alt→P→R→S,Excel 會將儲存格A1:A14設定為列印範圍。

例如:在有公式的儲存格中,我常用:

(1) 依序按下按鍵:Alt→M→V,Excel 會開啟[評估值公式]對話框,觀察公式過程。

(2) 依序按下按鍵:Alt→M→F,Excel 會開啟[函數引數]對話框,方便輸入引數。

例如:

(1) 依序按下按鍵:Alt→N→M,Excel 會開啟[選擇SMART圖形]對話框。

(2) 依序按下按鍵:Alt→N→P,Excel 會開啟[插入圖]對話框。

最後,還是提醒:你常用的功能不見得和別人一樣,自己要去熟練自己要用的按鈕,熟了就能生巧。

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

在 Windows 10 中的虛擬桌面是工作時的好幫手,可以參考前一篇文章:Windows 10-善用虛擬桌面的快速鍵來有效處理工作,如果想要一個虛擬桌面中的某個視窗移至另一個虛擬桌面l ,該如何處理呢?

當你按 WinKey+D 鍵,可以新增一個虛擬桌面,在各個桌面中開啟幾個視窗。而在按 WinKey+Tab 鍵時,在螢幕最下方即可預覽不同的虛擬桌面。

當你切換至一個虛擬桌面時,可以將其中的某個視窗拖曳至螢幕下方的另一個預覽桌面。

Windows 10-讓視窗在不同虛擬桌面之間移動

如此,便可將視窗在不同桌面間移動。

Windows 10-讓視窗在不同虛擬桌面之間移動

而你也可以將視窗移至螢幕右下角的『新增桌面』處。

Windows 10-讓視窗在不同虛擬桌面之間移動

被拖曳的視窗會被放在新增的桌面中。

Windows 10-讓視窗在不同虛擬桌面之間移動

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

1 23

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼