贊助廠商

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

搜尋本部落格文章資料

網友問到 Excel 的問題:如何依照以下的工時計算標準來計算加班時數?

●星期一到星期五基本工時8小時,超過為加班時數。

●星期六、日列為加班:

*加班時數4小時以內者,以4小時計;

*加班時數逾4小時至8小時以內者,以8小時計;

*加班時數逾8小時至12小時以內者,以12小時計。

Excel-分別計算平時和假日加班時數(WEEKDAY,VLOOKUP)


【公式設計與解析】

儲存格D2:

=IF(WEEKDAY(A2,2)<6,(C2-8)*(C2>8),VLOOKUP(C2,{0.1;4.1;8.1},1)+3.9)

複製儲存格D2,貼至儲存格D2:D23。

(1) WEEKDAY(A2,2)<6

判斷儲存格A2是否是星期一~星期五。

Excel-分別計算平時和假日加班時數(WEEKDAY,VLOOKUP)

(2) (C2-8)*(C2>8)

(星期一~星期五)當儲存格C2數值大於8者,才計算其加班時數。

(3) VLOOKUP(C2,{0.1;4.1;8.1},1)+3.9)

(星期六~星期日)根據以下標準換算加班時數。

*加班時數4小時以內者,以4小時計;

*加班時數逾4小時至8小時以內者,以8小時計;

*加班時數逾8小時至12小時以內者,以12小時計。

文章標籤

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

如下圖,在 Excel 中如何依日期欄位來按月編號?

Excel-按月編號(COUNTIF)

 

【公式設計與解析】

觀察上圖,月份是由小至大依序排列。

儲存格B2:=MONTH(A2)

儲存格C2:="A-"&B2&"-"&COUNTIF($B$2:B2,B2)

複製儲存格B2:C2,貼至儲存各B2:C23。

在此使用輔助欄位B欄,先取出每個日期的月份。

再利用 COUNTIF 計算相同月份由第一個儲存格起算的個數。

文章標籤

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

我們常在查詢 Google 地圖時,Google 對於路線規劃時,常會給幾個不同路線的建議有人問我:如何在地圖上只顯示想要的規劃路線?例如,列印路線時只顯示選取的路線。(下圖在列印時會顯示多條路線)

Google地圖-只留下多個路線規劃的其中一條路線

試著這樣做。

1. 先點選想要的路線上

2. 點選路線上的「詳細資訊」。

Google地圖-只留下多個路線規劃的其中一條路線

3. 按一下「重新整理」。

Google地圖-只留下多個路線規劃的其中一條路線

結果就只會留下你點選的路線而已。

Google地圖-只留下多個路線規劃的其中一條路線

當你分享連結或列印時,就不會有其他路線的干擾了。

文章標籤

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

在 Excel 中,如何將同一欄的資料中找出最大值/最小值所在的儲存格位址,或是將同一列的資料中找出最大值/最小值儲存格位址?

《資料在一欄》

Excel-查詢最大值的位址(ADDRESS,MATCH,INDIRECT)

【公式設計與解析】

選取B1:B21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:數值。

1. 最大值位置

儲存格F2:=ADDRESS(MATCH(MAX(數值),數值,0)+1,1)

(1) MAX(數值)

利用 MAX 函數找出數值陣列的最大值。

(2) MATCH(MAX(數值),數值,0)

利用 MATCH 函數找出最大值的位置(傳回一個數值)。

(3) ADDRESS(MATCH(MAX(數值),數值,0)+1,1)

利用 ADDRESS 函數根據第(2)式傳回的位置找出其儲存格位址。

2. 最大值內容

儲存格E2:=INDIRECT(F2)

利用 INDIRECT 函數將儲存格F2的內容轉換為儲存格位置並取得內容。

3. 最小值位置

儲存格F5:=ADDRESS(MATCH(MIN(數值),數值,0)+1,1)

4. 最小值內容

儲存格E2:=INDIRECT(F5)


《資料在一列》

Excel-查詢最大值的位址(ADDRESS,MATCH,INDIRECT)

【公式設計與解析】

選取B2:N2,按 Ctrl+Shift+F3 鍵,勾選「最左欄」,定義名稱:數值。

儲存格C2:=ADDRESS(1,MATCH(MAX(數值),數值,0)+1)

儲存格B2:=INDIRECT(C5)

儲存格G2:=ADDRESS(1,MATCH(MIN(數值),數值,0)+1)

儲存格F2:=INDIRECT(G5)

原理同《資料在一欄》,注意 ADDRESS 函數中的參數差異

文章標籤

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

網友問到:在下圖中,如何在 Excel 中將左表轉換右表?

下圖中,同一日期中,A組、B組、C組的人不會重覆。

Excel-重組表格資訊(SUMPRODUCT,OFFSET,MATCH)

【公式設計與解析】

選取日期中有資料儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。

選取資料的所有儲存格(本例:儲存格C2:E32),定義名稱:資料。

儲存格H3:=IF(SUMPRODUCT((日期=H$1)*(資料=$G3)),"V","")

因為同一日期中,A組、B組、C組的人不會重覆,所以在 SUMPRODUCT 函數中利用雙條件:(日期=H$1)*(資料=$G3),結果只會傳回 1/0。在 IF 函數中將 1/0 對應顯示V/空白

(完整結果如下圖)

Excel-重組表格資訊(SUMPRODUCT,OFFSET,MATCH)


以下提供第二種不同公式寫法,當作練習公式運用:

Excel-重組表格資訊(SUMPRODUCT,OFFSET,MATCH)

選取日期中有資料儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。

儲存格F3:

=IFERROR(IF(MATCH($G3,OFFSET($A$1,MATCH(H$1,日期,0),2,1,3),0),"V",),"")

複製儲存格F3,貼至儲存格F3:AL14。

(1) MATCH(H$1,日期,0)

MATCH 函數中判斷儲存格H1位於日期陣列的位置,傳回一個數值。

(2) OFFSET($A$1,第(1)式,2,1,3)

將第(1)式的傳回值代入 OFFSET 函數,本例傳回儲存格C2:E2。

(3) MATCH($G3,第(2)式,0)

MATCH 函數中判斷儲存格G3位於第(2)式取得的儲存格範圍中的位置,傳回一個數值。(傳回值:1、2、3和錯誤訊息)

(4) IF(第(3)式,"V",)

判斷第(3)式的傳回值,若是 1,2,3,則顯示「V」。

(5) IFERROR(第(4)式,"")

利用 IFERROR 函數將錯誤訊息顯示為空白。

文章標籤

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

找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼