贊助廠商

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

搜尋本部落格文章資料

目前日期文章: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 發表在 痞客邦 PIXNET 留言(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 發表在 痞客邦 PIXNET 留言(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 發表在 痞客邦 PIXNET 留言(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 發表在 痞客邦 PIXNET 留言(5) 人氣()

延續上篇文章: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 發表在 痞客邦 PIXNET 留言(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 發表在 痞客邦 PIXNET 留言(0) 人氣()

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

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


【參考做法】

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

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

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

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

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

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

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

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

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

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

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

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

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

【例如】

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

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

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

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

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

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


【這樣也可以】

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

vincent 發表在 痞客邦 PIXNET 留言(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 發表在 痞客邦 PIXNET 留言(0) 人氣()

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

以下特別介紹其中三種:

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

image

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


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

image

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

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

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

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

點選後放大

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

點選後放大

vincent 發表在 痞客邦 PIXNET 留言(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 發表在 痞客邦 PIXNET 留言(2) 人氣()

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

vincent 發表在 痞客邦 PIXNET 留言(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 發表在 痞客邦 PIXNET 留言(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 發表在 痞客邦 PIXNET 留言(2) 人氣()

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

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


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

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

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

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


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

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

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

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

vincent 發表在 痞客邦 PIXNET 留言(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 發表在 痞客邦 PIXNET 留言(0) 人氣()

有網友想要將在 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 發表在 痞客邦 PIXNET 留言(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 發表在 痞客邦 PIXNET 留言(1) 人氣()

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

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

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

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

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

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

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

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

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

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

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

1 23
找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼