贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201602 (30)

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

網友問到:如果根據一個上下班打卡的資料表,如何在 Excel 中計算各個班別的數量?

如下圖,共有三種班別,起迄時間都不相同,如何根據打卡時間的清單,自動判斷各個班別(早班、晚班、全天)的數量?

Excel-依據打卡時間判斷上班的班別(SUMPRODUCT)


【公式設計與解析】

選取B欄和C欄有資料的儲存格,按 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:上班打卡、下班打卡。

由於實際狀況多樣,所以,以下的範例不見得實用。

因為上班打卡不見得會準時,可能提早打卡或是延後打卡,因此假設上班前後一個小時內的打卡都接受。

1. 計算全天班

儲存格H3:=SUMPRODUCT(1*((下班打卡-上班打卡)>=12/24))

由於在 Excel 中一天24小時被定義為『1』,因此一小時以 1/24 計。而 12/24 表示 12 小時。

(下班打卡-上班打卡)>=12/24:當下班打卡和上班打卡時間相減時,必須大於 12 小時。在 SUMPRODUCT 函數,判斷會傳回是否大於 12/24 的邏輯運算結果(TRUE/FALSE)。

而公式中的『1*』,用意在於將 (下班打卡-上班打卡)>=12/24 傳回的 TRUE/FALSE 陣列轉換為 1/0 陣列。

最後透過 SUMPRODUCT 函數計算乘績和,即為所求。


2. 計算晚班

儲存格H4:=SUMPRODUCT(1*(ABS(14/24-上班打卡)<=1/24))

ABS(14/24-上班打卡)<=1/24:其由 14/24 表示一天的 14 時。14/24-上班打卡為計算上班打卡時與 14 時的差距,該式用以判斷該差距是否小於 1/24(1小時)。如果『是』,則為 14 時附近打卡,如果『否』,則不是在 14 時左右來打卡。

ABS 函數用以取數值的絶對值,而公式中的『1*』,用意在於將 ABS(14/24-上班打卡)<=1/24 傳回的 TRUE/FALSE 陣列轉換為 1/0 陣列。

最後透過 SUMPRODUCT 函數計算乘績和,即為所求。


3. 計算早班

儲存格H2:=SUMPRODUCT(1*(ABS(9/24-上班打卡)<=1/24))-(上式計算全天班)

因為早班和全日班的上班時間重覆,所以當計算上班時間是否在 9 時左右時,必須扣掉全天班的數量。

儲存格H2:=SUMPRODUCT(1*(ABS(9/24-上班打卡)<=1/24))-SUMPRODUCT(1*
((下班打卡-上班打卡)>=12/24))


【延伸練習】

儲存格D2顯示的是每個儲存格判斷的各日班別。

儲存格D2:=IF(C2-B2>=12/24,"全天",IF(ABS(9/24-B2)<=1/24,"早班","晚班"))

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

學校開學了,老師們早在寒假期間就陸續備課。很多老師製作了自己的簡報當為上課教材,但是投影片不一定適合直接印給學生當講義,而如果使用 PowerPoint 所提供的講義模式來列印,也無法滿足老師的需求,該如何來調整變化?

在Word中編輯PowerPoint簡報的投影片當作講義

說到要編製講義,使用 Word 或許還是比較方便實用的工具。而在 PowerPoint 中其實就有匯出投影片到 Word 的功能。

當你選取[檔案]功能表,再選取[匯出]選項。在[匯出]功能區中點選「建立講義」,PowerPiont 會執行:

1. 將投影片和備忘稿於 Word 文件

2. 在 Word 中編輯並格式化內容

3. 當簡報變更時,自動更新講義中的投影片

按一下「建立講義」按鈕。

在Word中編輯PowerPoint簡報的投影片當作講義

在[傳送至 Microsoft Word]對話框中,有五種版面配置可以選擇。當你點選一個格式,按下[確定]按鈕後,投影片會陸續被傳送到 Word 文件中。

在Word中編輯PowerPoint簡報的投影片當作講義

本例在將投影片新增至Microsoft Word 文件中,選取「貼上」。一張投影會依你選取的格式轉換至 Word 中,而且一張投影片對應一個 Word 頁面。在 Word 中環境中你可以善用排版功能來編製講義。

在Word中編輯PowerPoint簡報的投影片當作講義

當你在 Word 中的投影片上按右鍵,可以對投影片物件,進行剪裁、編輯、開啟和轉換。

在Word中編輯PowerPoint簡報的投影片當作講義

選取:開啟,Word 會幫你開啟這個簡報,讓你在 PowerPoint 環境下編輯。(本例會產生一個新的簡報檔)

選取:編輯,Word 功能表區顯示的是 PowerPoint 的功能表,可以直接進行各種編輯操作。

你可能會問:在將投影片新增至Microsoft Word 文件時選取「貼上」,如果在 Word 中修改投影片,會不會讓原始的投影片也跟著修改?答案是:不會。反之,如果你在原始簡報中修改了投影片,並不會改變 Word 中的投影片,要特別注意喔!

在Word中編輯PowerPoint簡報的投影片當作講義

但是,如果在將投影片新增至Microsoft Word 文件時選取「貼上連結」,則在 Word 文件中的每一張投影片會和原始簡報檔產生連結。也就是說,在 Word 文件中點選投影片來編輯時,其實都是在編輯原始的簡報檔。因此,當你修改了原始簡報檔的投影片內容,Word 中的投影片也會跟著被修改。

在Word中編輯PowerPoint簡報的投影片當作講義

以上二種方法各有特點,視你的用途,各取所需吧!

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

有同仁問到:一份文件200頁,其中有很多大大小小的圖片,如何才能快速移除文件中的所有圖片,並且保留原來的文字和格式?

參考下圖,文件中的許多地方都有一些各種格式的圖片,可能高達數十、數百個,要移除文件中的所有圖片,並且保留原來的文字和格式,如果一個一個圖片點選再刪除,實在不是太容易的事。如果複製文件後貼至記事本中,只會留下文字,但格式也會全跑掉,該如何處理呢?

Word-如何快速移除文件中的所有圖片(保留文字和格式)

方法其實很簡單:

請先開啟[尋找與取代]對話框,於[尋找目標]文字方塊中輸入「^g」,然後[取代為]文字方塊中要留空。當你按下[全部取代]按鈕,所有圖片瞬間秒殺!

Word-如何快速移除文件中的所有圖片(保留文字和格式)

你可以在[取代]區中按下[指定方式]按鈕,再選取「圖形」,Word 會自動幫你輸入『^g』。

Word-如何快速移除文件中的所有圖片(保留文字和格式)

Word-如何快速移除文件中的所有圖片(保留文字和格式)

不過,也要特別注意,文件中的圖片、美工圖案等都可以使用此方法來刪除。但是,如果你的圖案是浮動的,而非和文字排列在一起時(例如下圖的兩個圖案),無法以這個方法來刪除。

Word-如何快速移除文件中的所有圖片(保留文字和格式)

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

有網友問到一個在 Excel 工作表中資料重組的問題:如下圖工作表中資料清單,甲、乙、丙、丁四欄各有24列資料,如何將其重組為二個甲、乙、丙、丁四欄各有12列資料?

下圖中的公式中的參數 12 和 4 對應到圖示中的 12 和 4。

Excel-一欄分多欄資料重組(OFFSET,INT,MOD)


【公式設計與解析】

儲存格F:=OFFSET($A$2,MOD(ROW(1:1)-1,12)+INT((COLUMN(A:A)-1)/4)*12,
MOD(COLUMN(A:A)-1,4))

複製儲存格F2,貼至儲存格F2:M13。

MOD(ROW(1:1)-1,12):當公式向下複製時,可以產生『0,1,2,3,4,5,6,7,8,9,10,11,
0,1,2,3,…』

INT((COLUMN(A:A)-1)/4)*12:當公式向右複製時,可以產生『0,0,0,0,12,12,12,12,…』

MOD(COLUMN(A:A)-1,4):當公式向右複製時,可以產生『0,1,2,3,0,1,2,3,…』

Excel-一欄分多欄資料重組(OFFSET,INT,MOD)

將以上三式代入 OFFSET 函數,可取得對應的儲存格內容。

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

網友問到:在 Excel 的工作表中(如下圖左),如果想要在每欄取定量來重排,並且去除空白儲存格(如下圖右),該如何處理?

在下圖中,在甲、乙、丙三種不同且重覆的欄位,如果根據取樣中的數量(本例為5),將甲的多欄資料重組在一欄(每欄取5個),並且希望去除空格。

Excel-每欄取定量重排並去除空白(OFFSET,MOD,INT)


【公式設計與解析】

(1) OFFSET(A$2,MOD(ROW(1:1)-1,$H$2),INT((ROW(1:1)-1)/$H$2)*3)

MOD(ROW(1:1)-1,$H$2):依儲存格H2的數值(=5),當公式向下複製時傳回 0, 1, 2, 3, 4, 0, 1, 2, 3, 4, ...。

INT((ROW(1:1)-1)/$H$2)*3:依儲存格H2的數值(=5),當公式向下複製時傳回 0, 0, 0, 0, 0, 3, 3, 3, 3, 3, 3, ...。


(2) 取出定量重排

儲存格I2:=IF(ISBLANK(第(1)式,"",第(1)式)

OFFSET 函數取得的儲存格內容為空白儲存格時,改以空字串顯示。

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


(3) 去除空白儲存格

儲存格M2:{=IFERROR(OFFSET(I$2,SMALL(IF(I$2:I$17<>"",ROW(I$2:I$17),
FALSE),ROW(1:1))-2,,,),"")}

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

IF(I$2:I$17<>"",ROW(I$2:I$17):在陣列公式中判斷儲存格I2:I17裡不是空白儲存格者,傳回其列號(ROW(I2:I17)。

SMALL(IF(I$2:I$17<>"",ROW(I$2:I$17),FALSE):將上式傳回的列號中,由小到大依序取出其最小者。

OFFSET(I$2,SMALL(IF(I$2:I$17<>"",ROW(I$2:I$17),FALSE),ROW(1:1))-2,,,):將

上式代入 OFFSET 函數傳回對應儲存格的內容。

最後利用 IFERROR 函數,將因查詢不到資料而傳回錯誤訊息者,以空白顯示。

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

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

有網友問到:在 Excel 的工作表中有一個日期清單記錄每天的數值,如何每天自動只顯示當天的小計資料(參考下圖),該如何處理?

如下圖,在日期和數量清單中,資料會一直輸入,如何才能只顯示今天的小計而已。

Excel-在日期清單中每日只統計當日數量(TODAY,SUMPRODUCT,OFFSET)


【公式設計與解析】

儲存格E2:=SUMPRODUCT((A2:A1000=TODAY())*B2:B1000)

假設你的資料不會超過 1000 筆,而 TODAY 函數可以取得今天的日期,透過 SUMPRODUCT 函數計算符合 A2:A1000=TODAY() 者和其對應的數量(B2:B1000) 的乘積和。

如果你的清單資料是不斷的增加,你可能會使用這樣的公式:(不建議)

(X) 儲存格E2:=SUMPRODUCT((A:A=TODAY())*B:B)

因為上式中使用A欄整欄來運算乘積和,可能容易產生當機現象。稍微修改一下:

儲存格E2:=SUMPRODUCT((OFFSET(A2,,,COUNT(A:A),)=TODAY())*(OFFSET
(B2,,,COUNT(B:B),)))

先利用 OFFSET(A2,,,COUNT(A:A),) 和 OFFSET(B2,,,COUNT(B:B),) 來找出有資料的儲存格範圍,再讓 SUMPRODUCT 函數計算乘積和。

當每天開啟這個 Excel 檔時,就會以當天的日期來抓取資料計算。

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

在下圖中,是一個以星期幾為主,來顯示各月日期的萬年曆,如何在 Excel 的工作表中建立這個萬年曆?

有了這個萬年曆,只要改變儲存格A1(年份),即可顯示當年的月份和星期幾的對照。因此,例如:可以快速找到各月週六的日期。要如何來設計公式?

image


【公式設計與解析】

儲存格B2:=VALUE(TEXT(DATE($A$1,COLUMN(A:A),1)-WEEKDAY(DATE($A$1,
COLUMN(A:A),1),2)+ROW(1:1),"dd"))

(1) DATE($A$1,COLUMN(A:A),1)

藉由 DATE 函數取得各年各月的第一天日期,Excel 會傳回一個數值。

(2) WEEKDAY(DATE($A$1,COLUMN(A:A),1),2)

先藉由 DATE 函數取得各年各月的第一天日期,再利用 WEEKDAY 函數來找出每個月的第一天的傳回值。本例是選取參數 2,代表星期一到星期日,傳回數字 1 到 7。

image

公式中的 COLUMN(A:A) 乃用於向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→ ... →COLUMN(M:M)=12。

(3) 完整公式

儲存格B2:=VALUE(TEXT(第(1)式-第(2)式+ROW(1:1),"dd"))

第(1)式-第(2)式+ROW(1:1):取得每個月的第一個儲存格(儲存格B2)應該顯示的日期,而 ROW(1:1) 向下複製時,會產生 ROW(1:1)=1→ROW(2:2)=2→ ... →ROW(31:31)=31。

TEXT(第(1)式-第(2)式+ROW(1:1),"dd"):透過 TEXT 函數將上式的日期取出日期中的『日期數值』,並以二碼顯示("dd")。當向下複製公式時,即可產生連續的日期數值。

最後再以 VALUE 函數將上式 TEXT 取得的結果(文字)轉換為數字。

複製儲存格B2,貼至儲存格B2:M43。

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

學校同仁問到:在使用 Word 編輯文件時,由於列印出來的文件還要插入其他資料,所以頁碼無法連續,若在其中插入空白頁,列印時又會浪費紙張,該如何處理?

可以在 Word 中設定不連續頁碼!如下圖,第 4 頁的頁碼要修正為第 6 頁,其後的頁碼要跟著依序順延,應該如何操作?

image

參考以下的步驟:

1. 先在一個 Word 文件中的頁尾插立頁碼。(本練習要將第 4 頁改為第 6 頁)

image

2. 在第 4 頁的最前面設定其後新增『一節』。

例如:可以選取[版面配置]功能表的[分隔設定]選單下的「分節符號/下一頁」選項。

image

例如:也可以在[版面設定]對話框中的[邊界]標籤下,選取『套用至插入點之後』,來新增一節。

image

3. 設定不同的頁碼。

你可以看到目前第 1, 2, 3 頁在第 1 節,而第 4, 5, 6 頁在第 2 節。

image

請你選取第 4 節的頁碼,在其上按右鍵,選取「頁碼格式」選項。

image

在[頁碼格式]對話框中修改為:起始頁碼 6。

image

原來的頁碼『第4頁』已改為『第6頁』:

image

同理:你也可以練習插入第 3 節,並將原第 7 頁的頁碼改為第 12 頁,其後各頁的頁碼依序順延。

image

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

開學之初而已,就有同仁問到在 Excel 中,關於名次計算的問題。參考下圖,一般在計算名次時,可能會有這樣的狀況:

1. 名次重覆跳過:例如有 2 個第 4 名,所以沒有第 5 名。

2. 名次重覆不跳過:例如有 2 個第 4 名,原來的第 6 名成為第 5 名。

3. 名次不重覆:例如有 2 個第 4 名,會隨機產生一個第 4 名,另一個為第 5 名。

Excel-6種計算名次的方法(RANK,SUMPRODUCT,COUNTIF)


【公式設計與解析】

依上圖,選取儲存格A1:A24,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:數值。使用相同方法,將E欄中有資料的儲存格,定義名稱:輔助欄。


1. 名次重覆跳過

儲存格B2:=RANK(A2,數值)

使用 RANK 函數是最通用的計算排名工具。再提供以下三種方式,都能達到相同的排名結果。

(1) 儲存格B2:=COUNTIF(數值,">"&A2)+1

使用 COUNTIF 函數計算在數值陣列中,大於儲存格A2的有幾個,然後再加 1。

(2) 儲存格B2:=SUMPRODUCT(--(數值>A2))+1

SUMPRODUCT 函數中找出數值是否大於儲存格A2的 TRUE/FALSE 陣列,利用『--』運算,將 TRUE/FALSE 陣列轉換為 1/0 陣列,再由 SUMPRODUCT 函數執行『乘積和』運算。最後再加 1,即為所求。

(3) 儲存格B2:{=SUM(IF(數值>A2,1,0))+1}

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

SUM(IF(數值>A2,1,0)) 的計算原理同 SUMPRODUCT(--(數值>A2))。


2. 名次重覆不跳過

儲存格C2:=SUMPRODUCT((數值>A2)*(1/COUNTIF(數值,數值)))+1

1/COUNTIF(數值,數值):藉由 COUNTIF 函數,找出每個名次出現的次數,1/COUNTIF 的用意是將計算後的次數加以倒數,例如:某一名次有 3 人,則 1/COUNTIF 為 0.333333,某一名次有 4 人,則 1/COUNTIF 為 0.25。透過 SUMPRODUCT 函數加總運算後,結果均會為 1。

(數值>A2)*(1/COUNTIF(數值,數值)):找出大於儲存格A2者,再運算 1/COUNTIF 的部分。其中『*』運算,相當於執行邏輯 AND 運算。


3. 名次不重覆

儲存格E2:=A2+RAND()

儲存格D2:=RANK(E2,輔助欄)

藉由輔助欄位將A欄中的每個儲存格加上一個亂數(介於0和1之間的數),然後再置入 RANK 函數中加以排序,如此得到的結果,會是隨機讓同名次者,分出大小。

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

網友問到 Excel 的問題:如何在一個日期區間中計算工作天數、放假天數?

參考下圖,因為每個人遇到的狀況不同,當要計算工作天數時,你可能要考量到星期六、星期日和放假日,也要考量到補班日,該如何處理?

Excel-計算工作天數和放假天數(NETWORKDAYS,SUMPRODUCT)


【公式設計與解析】

1. 不含六日的工作天數

儲存格C2:=NETWORKDAYS(A2,B2)

利用 NETWORKDAYS 函數,只要給予起始和終止的日期,即可計算日期區間中扣除星期六日的天數。

如果你的 Excel 版本沒有 NETWORKDAYS 函數,則可以改用 SUMPRODUCT 函數:

儲存格C2:=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" & N(B2))),2)<6))

"A" & N(A2) & ":A" & N(B2):將二個日期的數值組成一個儲存格範圍,例如:A42370:A42552。其中 N 函數可以將一個日期傳回其代表的數值。

INDIRECT("A" & N(A2) & ":A" & N(B2):利用 INDIRECT 函數將上式轉換為真實的儲存格參照位址。

ROW(INDIRECT("A" & N(A2) & ":A" & N(B2))):將上式置入 ROW 函數,轉換為列號範圍,例如:A42370:A42552→ROW(A42370:A42552),在 SUMPRODUCT 函數中可以表示為 42370, 42371, 42372, ..., 42551, 42552 組成的陣列。

WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" & N(B2))),2)<6:在 WEEKDAY 函數中利用參數『2』,得到傳回值小於 6 者(表示星期一至星期五)的 TRUE/FALSE 陣列。

Excel-計算工作天數和放假天數(NETWORKDAYS,SUMPRODUCT)

1*(WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" & N(B2))),2)<6):利用『*1』,將上式中的 TRUE/FALSE 陣列轉換為 1/0 陣列。

最後,透過 SUMPRODUCT 函數加總,即為所求。


2. 不含六日、不含假日的工作天數

儲存格D2:=NETWORKDAYS(A2,B2,$G$3:$G$16)

NETWORKDAYS 函數置入第 3 個參數,其為放假日的儲存格範圍。

如果你的 Excel 版本沒有 NETWORKDAYS 函數,則可以改用 SUMPRODUCT 函數:

SUMPRODUCT(($G$3:$G$16<=B2)*($G$3:$G$16>=A2)):

求在儲存格G3:G16的放假日中,介於儲存格B2和儲存格A2兩個日期之間的個數。

儲存格D2:=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A"
& N(B2))),2)<6))-SUMPRODUCT(($G$3:$G$16<=B2)*($G$3:$G$16>=A2))


3. 不含六日、不含假日、含補班日的工作天數

SUMPRODUCT(($G$19:$G$21<=B2)*($G$19:$G$21>=A2)):

求在儲存格G19:G21的補班日中,介於儲存格B2和儲存格A2兩個日期之間的個數。

儲存格E2:=NETWORKDAYS(A2,B2,$G$3:$G$16)+SUMPRODUCT
(($G$19:$G$21<=B2)*($G$19:$G$21>=A2))

如果你的 Excel 版本沒有 NETWORKDAYS 函數,則可以改用 SUMPRODUCT 函數:

儲存格E2:=SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT("A" & N(A2) & ":A" &
N(B2))),2)<6))-SUMPRODUCT(($G$3:$G$16<=B2)*($G$3:$G$16>=A2))+
SUMPRODUCT(($G$19:$G$21<=B2)*($G$19:$G$21>=A2))

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

網友想要將 Excel 工作表中的資料表(如下圖左)取出資料重組成下圖右的樣子,該如何處理?即原本甲、乙、丙分散在多欄,現在要將甲、乙、丙分別組合成一欄,該如何設計公式?

image


【公式設計與解析】

分析上圖,甲、乙、丙有 3 個類別,每個類別有 5 個項目。

儲存格M2:=OFFSET($A$1,MOD(ROW(1:1)-1,5)+1,INT((ROW(1:1)-1)/5)*3)

上式中的參數『3』即為 3 個類別,參數『5』即為 5 個項目。

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

ROW(1:1):當公式向下複製時 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

MOD(ROW(1:1)-1,5)+1:當公式向下複製時產生『1,2,3,4,5,1,2,4,5,1,2,3,4,5』。

INT((ROW(1:1)-1)/5)*3:當公式向下複製時產生『0,0,0,0,0,3,3,3,3,3,6,6,6,6,6』。

將以上二式代入 OFFSET 函數,求得對應儲存格的內容,即為所求。

image

同理:

儲存格N2:=OFFSET($B$1,MOD(ROW(1:1)-1,5)+1,INT((ROW(1:1)-1)/5)*3)

儲存格O2:=OFFSET($C$1,MOD(ROW(1:1)-1,5)+1,INT((ROW(1:1)-1)/5)*3)


【補充說明】

INT函數:將函數參數取不大於(大於或小於)的最大整數。

MOD函數:求得兩數相除的餘數。

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

有網友問到:在 Excel 中有一個資料清單,如下圖,如果想要在每種相同商品中找出最高/最低報價及其廠商,該如何處理?

在下圖中,有甲、乙、丙、丁、戊等不同廠商,分別有A、B、C、D、E等不同商品,現在要來找出各種商品的最高/最低報價和所屬廠商。

Excel-在資料清單查詢相同項目的最大值和最小值(OFFSET,MAX,MIN,陣列公式)


【公式設計與解析】

選取儲存格A1:C26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:廠商、商品、報價。

1. 最高報價的報價/儲存格H2:

{=MAX(IF(商品=E2,報價,FALSE))}

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

在陣列公式中,判斷商品陣列和儲存格E2相同者,傳回其報價。再透過 MAX 函數傳回最大值。例如:儲存格E2為『A』,則會傳回 A 中報價的最大值。


2. 最高報價的廠商/儲存格G2:

{=OFFSET($A$1,MAX(IF((商品=E2)*(報價=H2),ROW(廠商),FALSE))-1,)}

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

IF((商品=E2)*(報價=H2),ROW(廠商),FALSE):符合雙條件『商品=E2』和『報價=H2』者,傳回廠商所在列號。

再透過 OFFSET 函數取出列號對應的儲存格內容。


同理:

3. 最低報價的報價/儲存格H3:

{=MIN(IF(商品=E3,報價,FALSE))}

4. 最低報價的廠商/儲存格G3:

{=OFFSET($A$1,MIN(IF((商品=E3)*(報價=H3),ROW(廠商),FALSE))-1,)}


最後,複製儲存格H2:G3,貼至儲存格H2:H11。

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

網友問到:在下圖中的 Excel 資料表中,如果要計算三個條件都成立的個數有幾個,該如何處理,為何下圖中的公式會『錯誤』。其中的重點是要滿足日期中符合所要月份的條件。

選取儲存格A1:C27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、日期、項目。

Excel-如何正確取出日期中的月份(MONTH,SUMPRODUCT,N)


【公式說明】

儲存格F4:=SUMPRODUCT((人員=F1)*(MONTH(日期)=F2)*(項目=F3))

MONTH(日期)=F2:取出日期陣列中的『月份』數值。

(人員=F1)*(MONTH(日期)=F2)*(項目=F3):『*』相當於執行邏輯 AND 運算。三個條件都成立者傳回 TRUE,不成立者傳回 FALSE,在 SUMPRODUCT 函數的乘積和運算時,會將 TRUE/FALSE 轉換為 1/0


為何以下的公式會錯誤?

(X)儲存格F4:=SUMPRODUCT((人員=F1)*(VALUE(LEFT(日期,2))=F2)*(項目=F3))

以圖中的儲存格B25為例,雖然顯示04月23日,但是內部儲存為42483,當你執行:

VALUE(LEFT(日期,2))會傳回『42』(42483的前2碼),並非你所需的月份『04』。

如果你想取得儲存格B25的數值,可以使用公式:=N(B25)。

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

本篇文章純粹是要練習幾個 Excel 函數的應用,是否有實用性沒有考量到。參考下圖,有兩個儲存格想要比較其中內容,每一個位元的字元是否相同。

一、儲存格中的內容全為數字

Excel-判斷兩個儲存格中的每個字元是否相同(SUMPRODUCT,MID,COLUMN)


(1) 計算各位元相同者

儲存格C5:

=SUMPRODUCT(1*(VALUE(MID($A2,COLUMN(A:H),1))=VALUE(MID($B2,
COLUMN(A:H),1))))

COLUMN(A:H):在 SUMPRODUCT 函數中代表數字 1 ~ 8。

MID($A2,COLUMN(A:H),1):在 SUMPRODUCT 函數中以陣列方式取出儲存格A2的第1個字元至第8個字。

VALUE(MID($A2,COLUMN(A:H),1)):將上式的結果(文字型態的數字)轉換為數值。

VALUE(MID($B2,COLUMN(A:H),1)):原理同上式。

SUMPRDUCT 函數中的『1*』,其作用為透過『乘以1』的運算動作,將 TRUE/FALSE 陣列轉換為 1/0 陣列。


(2) 計算各位元不同者

儲存格C6:

=SUMPRODUCT(1*(VALUE(MID($A2,COLUMN(A:H),1))<>VALUE(MID($B2,
COLUMN(A:H),1))))

此公式和是將儲存格C5公式中的『=』改為『<>』,原理相同。


(3) 在對應儲存格中顯示各個字元是否相同,傳回『TRUE/FALSE』

儲存格C2:

=XOR(VALUE(MID($A2,COLUMN(A:A),1)),VALUE(MID($B2,COLUMN(A:A),1)))

複製儲存格C2,貼至儲存格C2:J2。

XOR 函數可以判斷兩個數是否相同,相同傳回 TRUE,不同傳回 FALSE


(4) 在對應儲存格中顯示各個字元是否相同,傳回『相同/不同』

儲存格C3:

=IF(XOR(VALUE(MID($A2,COLUMN(A:A),1)),VALUE(MID($B2,COLUMN(A:A),
1))),"不同","相同")

複製儲存格C3,貼至儲存格C3:J3。


二、儲存格中的內容全為文字

Excel-判斷兩個儲存格中的每個字元是否相同(SUMPRODUCT,MID,COLUMN)

儲存格C5:

=SUMPRODUCT(1*(MID($A2,COLUMN(A:H),1)=MID($B2,COLUMN(A:H),1)))

儲存格C6:

=SUMPRODUCT(1*(MID($A2,COLUMN(A:H),1)<>MID($B2,COLUMN(A:H),1)))

儲存格C2:

=MID($A2,COLUMN(A:A),1)=MID($B2,COLUMN(A:A),1)

儲存格C3:

=IF(MID($A2,COLUMN(A:A),1)=MID($B2,COLUMN(A:A),1),"不同","相同")

Excel在判斷文字是否相同時,會將同一字母大寫和小寫視為相同。而相同公式在儲存格內容全改為數字時仍可使用,也就是可以取代一、中的公式。這個公式比較簡短,也沒有用到XOR函數。其中數字可以被視為文字來處理。

Excel-判斷兩個儲存格中的每個字元是否相同(SUMPRODUCT,MID,COLUMN)

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

在 Windows 7 中如果你按 Delete 鍵來刪除檔案時,系統會顯示確認訊息,等你按下[確定]按鈕後,才會將檔案放至「資源回收筒」中,如果你不想要將檔案放至「資源回收筒」裡,也可以在刪除時按 Shift+Delete 鍵。

這是你熟知的做法,最近有同仁陸續升級到 Windows 10 時,發現以上的做法有所改變了。在 Windows 10 中刪除檔案,已經不再問你,而是直接丟到資源回收筒中了。該如何調整為以前的操作習慣呢?

先看看在 Windows 7 中:在資源回收筒上按右鍵,選取[內容]選項。其中預設值為:

1. 勾選:顯示確認刪除的對話方塊。

2. 沒有選取:不要將檔案移到資源回收筒。在刪除檔案時立即移除。

Windows 10-刪除檔案前先出現確認刪除對話框及其配套做法

以下是 Windows 10中:在資源回收筒上按右鍵,選取[內容]選項。其中預設值為:

1. 沒有勾選:顯示確認刪除的對話方塊。

2. 沒有選取:不要將檔案移到資源回收筒。在刪除檔案時立即移除。

Windows 10-刪除檔案前先出現確認刪除對話框及其配套做法

因此,你只要在此對話框中,即可切換是否要顯示[確認刪除]對話框了。

特別注意:以上的做法是針對每一個磁碟來設定,因此如果有多個磁碟必須要分別操作。

另外,提供你一個配套的做法:

如果你選擇了不顯示確認刪除的對話方塊,又很怕誤刪,則可以在按 Delete 鍵直接送至資源回收筒後,如果想要後悔,則馬上按下 Ctrl+Z 鍵(復原),即可從資源回收筒中還原檔案至原來的位置。這個方法也適用於檔移被搬移或複製到其他位置的還原。

如果是在磁碟中設定『不要將檔案移到資源回收筒。在刪除檔案時立即移除。』,則在刪除檔案後,立即按 Ctrl+Z 鍵是無效的。

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

當教師們在備課時,手邊會有許多自行搜集或是各家書商提供的多種光碟資料,由於多片光碟在管理和使用上並不方便。尤其要帶到課堂上,如果漏帶了某些資料,當天就要開天窗,如果你有習慣把某些資料備份在雲端,可以解決部分的問題。如果你想選擇將整片光碟複製到硬碟上,或許也是可行,但是在管理檔案上顯得較為不方便。

你應該用過壓縮檔(例如:ZIP、WinRAR檔等),如果將多個資料夾和檔案縮壓成一個檔案,透過壓縮/解壓縮軟體,可以直接取用壓縮檔內的檔案,所以在管理和使用上較為方便。

相同概念,如果能把光碟內容儲存成一個 ISO 檔(若有廠商願意提供,那就更棒了!),就可以縮小這些問題了。你可以試著使用燒錄軟體將整片光碟內容或是光碟中的部分檔案/資料夾燒錄成 ISO 檔。

Windows 10-利用ISO檔收納檔案並且以虛擬光碟快速取用

要如何使用這個 ISO 檔呢?在 Winrdows 10 裡使用 ISO 檔非常簡單。打開檔案總管,在這個 ISO 檔上按右鍵,選取『掛接』選項,或是直接在 ISO 檔上按二下。

Windows 10-利用ISO檔收納檔案並且以虛擬光碟快速取用

你的本機磁碟中會多了一個『虛擬光碟』:

Windows 10-利用ISO檔收納檔案並且以虛擬光碟快速取用

點選這個光碟機,即可取用其中的檔案/資料夾。

Windows 10-利用ISO檔收納檔案並且以虛擬光碟快速取用

如果想要卸載這個虛擬光碟,則在光碟機上按右鍵,選取「退出」即可。你還可以同時掛載多個虛擬光碟。

Windows 10-利用ISO檔收納檔案並且以虛擬光碟快速取用

不過,使用上也要先提醒,當光碟內的資料量較大時,要製作成 ISO 檔會花較多的時間。現在流行共同備課,不妨大家互相分擔一點工作,再一起分享使用吧!將 ISO 檔備份在雲端硬碟(私有雲或公有雲)中,即可讓多人下載使用。

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

最近學校放寒假,有比較多的時間到處旅遊,在台灣各地移動。每天都使用手機和電腦在 Google 地圖上把玩、檢視,不免引起同事和親人的好奇,常有人問我為何這麼有興趣在 Google 地圖上,本文就來回答他們的問題。

因為我個人的使用習慣,在旅遊的前、中、後,Google 地圖是非常吃重的助手,除了查詢地點位置、地點的相片、地點的評分和評論之外,通常也用來規劃路線,有時也會用來導航,這些本文就不再贅述了。然而,因為它已整合了『您的時間軸』,而我也加入了 Google 的在地嚮導,所以 Google 地圖上的『您的貢獻』,也是變成一個有趣的地方。

在 Google 地圖中如果你登入 Google 帳號,而且這個帳號也是你在手機上登入的帳號,並且 GPS 定位功能也開啟了。在 Google 地圖的選單中,請你先注意到:『您的時間軸』和『您的貢獻』這兩個項目。

整合Google地圖、你的時間軸、在地嚮導貢獻

在時間軸中,除了記錄你的重要停留點(以紅色點顯示)之外:(先注意到下圖中『時間軸』一旁的鎖頭圖示,表示該記錄只有你看的到)

整合Google地圖、你的時間軸、在地嚮導貢獻

還可以在每一天中,使用時間軸方式標示你所停留點的名稱和時間,如果你使用手機或電腦上傳相片至 Google 相簿中,Google 地圖也會將大約時間時拍的相片顯示在該地點中,很妙吧!

整合Google地圖、你的時間軸、在地嚮導貢獻

如果地點或時間不對,你也可以自行修正:(不想顯示該停留點,也可以刪除喔)

整合Google地圖、你的時間軸、在地嚮導貢獻

整合Google地圖、你的時間軸、在地嚮導貢獻

整合Google地圖、你的時間軸、在地嚮導貢獻

在某些地點你還可以自訂『暱稱』,Google 會幫你放置圖釘。例如,2016年2月6日大地震那天,我正好人在台南老家,歷經了劫後餘生的感受,地震後開車四處繞一下,隨手拍下房子傾斜的照片,日後再回顧這段往事時,相片和時間等元素都會一併出現。

整合Google地圖、你的時間軸、在地嚮導貢獻

當你在手機的 Google 地圖 App 中選取「時間軸」,也可以看到每一天的停留點的資訊:

整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻

你可以編輯每個停留點:

整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻

如果你的時間軸功能有問題,可以檢視選單中的「時間軸設定」,其中『Google 相簿』的狀態是啟動的,而位置回報功能要開啟,定位紀錄也要開啟。

整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻

再來,如果你已加入『Google 在地嚮導』,則你在 Google 地圖中可以檢視「我的貢獻」,你可以看到曾經對某個地點給過的評分和評論,及在該地點上傳的相片:

整合Google地圖、你的時間軸、在地嚮導貢獻

你在每個 Google 地圖上的地點可以新增相片和編輯評論:

整合Google地圖、你的時間軸、在地嚮導貢獻

如果你在手機的 Google 地圖 App,則可以在「我的貢獻」中看到(在地嚮導)自己的評分和評論記綠及上傳的相片。而 Google 也很貼心的將上傳的照片和評論整合在一起,所以別人可以看你的資料,你也可以看到別人的資料。

整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻

其中,評論、評分和相片是可以隨時修改和刪除的。

整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻

如果你對某個地點給予評分或評論,或是你剛好在這個地點而你也在地點上點選該地點時,Google 會問你『熟悉這個地方嗎?協助別人作決定』,只要點一下「確定」,即可開始回答一些問題。問題可能高達好幾十個,Google 一次只會問你幾個問題,如果你主動再點選相同地點,它會不斷的問你各種問題。

整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻

整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻

眾人所回答的問題形成大數據,會顯示在該地點的資訊中,點選後可得詳細資料,而 Google 提問的問題不斷的在增加,眾人的答案傾向也會改變相關顯示的資訊內容。

整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻

整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻

回答完問題,會顯示你的作答記錄,你也可以看到在地嚮導的積分分佈。

整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻

因為大家的資訊都是公開的,所以你看到別人的評論,點選某個人的姓名或頭貼,可以看到這個人的其他評論和投放的照片。

整合Google地圖、你的時間軸、在地嚮導貢獻 整合Google地圖、你的時間軸、在地嚮導貢獻

由於手機隨身攜帶,所以資訊整合的愈多愈方便,除了可以看別人的提供的資訊,也可以將個人的資訊提供給網友,這是個分享的時代,愈多人加入愈能提升品質。

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

苗栗對我而言,是個比較少有機會造訪的地方。今年藉由訪親友的機會順便走春一下,來到了『明德水庫』。要到明德水庫的交通非常方便,經由1號高速公路在頭屋交流道下,沿台13線走,5分鐘車程即可抵達。

明德水庫除了環湖看湖上風光之外,還可以進入『日新島』一遊。進入這個湖中島,必須繳費一人100元,這個門票費用可以抵用島上的消費,只是島上的商家選擇性很少罷了。

2016年春節走春-苗栗明德水庫

走過吊橋(壹號)就是日新島。

2016年春節走春-苗栗明德水庫

島上的設施不多,主要也是散散步看看湖景。

2016年春節走春-苗栗明德水庫

2016年春節走春-苗栗明德水庫

2016年春節走春-苗栗明德水庫

2016年春節走春-苗栗明德水庫

2016年春節走春-苗栗明德水庫

2016年春節走春-苗栗明德水庫

2016年春節走春-苗栗明德水庫

2016年春節走春-苗栗明德水庫

2016年春節走春-苗栗明德水庫

你也可以花300元搭環湖的遊艇來遊湖。

2016年春節走春-苗栗明德水庫

2016年春節走春-苗栗明德水庫

2016年春節走春-苗栗明德水庫

2016年春節走春-苗栗明德水庫

2016年春節走春-苗栗明德水庫

在日新島上再走過一個吊橋(貳號),可以到島的另一邊,散步到「薰衣草森林」。

2016年春節走春-苗栗明德水庫

進入「薰衣草森林」也是要門票,同樣也可以抵消費。不過,當日因先前有下過雨,地上有些泥濘,所以就在門外拍拍照而已。

2016年春節走春-苗栗明德水庫

2016年春節走春-苗栗明德水庫

2016年春節走春-苗栗明德水庫

2016年春節走春-苗栗明德水庫

2016年春節走春-苗栗明德水庫

2016年春節走春-苗栗明德水庫

還好!很早就到達明德水庫,當我要離開時,人潮已增加很多了。大過年的,到處都是人擠人、路塞車的,如何錯開人潮和車潮,也需要一點用心思考。

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

有網友問到:在工作表如果儲存裡填入運算式,如何得到這個運算式的運算結果?

一般我們在儲存格中顯示的是運算結果,而公式編輯列中顯示運算公式。參考下圖,如果直接將運算式置入儲存格中顯示出來,再取用這個運算公式來計算結果,該如何處理?

Excel-利用儲存格中的運算式計算運算結果(EVALUATE)

首先,要選取儲存格B2,然後定義名稱:運算;參照到:EVALUATE(工作表1!A2)。

Excel-利用儲存格中的運算式計算運算結果(EVALUATE)

然後,輸入公式:

儲存格B2:=運算

儲存格C2:=A2&"="&運算

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

最重要的是,當你在儲存這個工作表時,必須儲存為『巨集的活頁簿(*.xlsm)』格式,而開啟該檔案時,也必須啟用巨集,你才能看到運算結果。

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

我們常在 Excel 的資料清單中會遇到計算『人次』和『人數』的問題,該如何處理?

參考下圖,在不同管道中有些人重覆出現在 A 和 B 管道中,因次計算人次和人數時,人次會比人數多。

Excel-統計人次和人數(不重覆者)(SUMPRODUCT,COUNTIF

如果你不想使用公式,可以使用樞紐分析表工具:在「列」欄位中指定『班級』,在「值」欄位中指定『學號』。

Excel-統計人次和人數(不重覆者)(SUMPRODUCT,COUNTIF

並且設定欄位中設定摘要的計算類為:項目個數。

Excel-統計人次和人數(不重覆者)(SUMPRODUCT,COUNTIF

或是使用公式:

儲存格G2:=COUNTIF(班級,F2)

複製儲存格G2,貼至儲存格G2:G11。

以上二個方法只能求出『人次』,而無法求出『人數』。

參考以下使用 SUMPRODUCT 函數的相關做法。

為了讓公式看起來更直覺,先選取儲存格B1:C28,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:班級、學號。

(1) 計算『人次』

儲存格G2:=SUMPRODUCT((班級=F2)*1)

(2) 計算『人數』

儲存格H2:=SUMPRODUCT((班級=F2)*(1/COUNTIF(學號,學號)))

複製儲存格G2:H2,貼至儲存格G2:H11。

為何使用 1/COUNTIF,相關說明請參閱以前的文章:

Excel-計算不重覆的數值個數

Excel-計算合於條件的不重覆個數(SUMPRODUCT,COUNTIF)

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

1 2

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼