贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201708 (7)

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

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

Excel-按月編號(COUNTIF)


【公式設計與解析】

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

儲存格B2:=MONTH(A2)

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

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

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

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

文章標籤

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

我們常在查詢 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 留言(2) 人氣()

網友提問:在 Excel 中,如何透過欄(列)標題和內容來反求列(欄)標題?

【經由列標題和內容求得欄標題】

本例:透過列標題『丁』和內容『R』,要求得欄標題『寅』。

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

1. 定義儲存格名稱

選取儲存格A2:F6,按 Ctrl+Shift+F3 鍵,勾選「最左欄」,定義名稱:甲、乙、丙、丁、戊。

2. 選取儲存格I2,設定「資料驗證」。設定準則:

儲存格內允許:清單。來源:=INDIRECT(H2)。

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

結果如下:(當儲存格H2改變時,儲存格I2可以選取的清單也隨之變動。)

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

3. 設定公式。

儲存格H3:=OFFSET(A1,0,SUMPRODUCT((B2:F6=I2)*COLUMN(B2:F6))-1)

(1) SUMPRODUCT((B2:F6=I2)*COLUMN(B2:F6))

利用 SUMPRODUCT 函數求得儲存格I2的欄號。

(2) OFFSET(A1,0,第(1)式-1)

OFFSET 函數中根據第(1)式傳回的列號來求得對應的儲存格內容。


【經由欄標題和內容求得列標題】

本例:透過欄標題『卯』和內容『S』,要求得列標題『丁』。

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

1. 定義儲存格名稱

選取儲存格B1:F6,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:子、丑、寅、卯、辰。

2. 選取儲存格I2,設定「資料驗證」。設定準則:

儲存格內允許:清單。來源:=INDIRECT(H2)。

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

結果如下:(當儲存格H2改變時,儲存格I2可以選取的清單也隨之變動。)

Excel-由欄(列)標題和內容反求列(欄)標題(OFFSET,INDIRECT,SUMPRODUCT)

3. 設定公式。

儲存格H3:=OFFSET(A1,SUMPRODUCT((B2:F6=I2)*ROW(A2:A6))-1,0)

(1) SUMPRODUCT((B2:F6=I2)*ROW(A2:A6)).

利用 SUMPRODUCT 函數求得儲存格I2的欄號。

(2) OFFSET(A1,第(1)式-1,0)

OFFSET 函數中根據第(1)式傳回的列號來求得對應的儲存格內容。

文章標籤

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

新同學進入新的校園,註冊組長又面臨「分班」的問題了!如果你已經由某一個規則將學生予以排序完成,如何能以 S 型分班的概念來分班呢?Excel 如何幫助你?以下分別以『手動』和『自動』二種方式來說明。

參考下圖,左側是有數百名學生已排序後清單,右側是自動以 S 型來分班的結果。(本例沒有考慮男女生等因素)

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)


【手動處理】

本例假設要分7班。

(1) 在分班欄位依序輸入「1,2,3,4,5,6,7,6,5,4,3,2,1」。

(2) 複製上述的儲存格範圍,往下各列貼上。

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

(3) 進入自動篩選,篩選「1」項。

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN) Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

(4) 複製篩選「1」的儲存格,貼至101班。

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

(5) 重覆步驟(3)和(4),分別篩選「1,2,3,4,5,6,7」對應貼至「101,102,103,104,105,106,107」。

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)


【公式自動處理】

如果你想試試以公式來自動分班,則可以輸入以下公式。

1. 產生分班欄位

儲存格C2:=IF(MOD(INT((ROW(1:1)-1)/$D$1),2),$D$1-MOD(ROW(1:1)-1,
$D$1),MOD(ROW(1:1)-1,$D$1)+1)

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

(1) MOD(INT((ROW(1:1)-1)/$D$1),2)

(2) $D$1-MOD(ROW(1:1)-1,$D$1)

(3) MOD(ROW(1:1)-1,$D$1)+1

第(1)式、第(2)式、第(3)式產生的結果如下圖:

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

(4) IF(第(1)式,第(2),第(3))

當第(1)式傳回『1』時,相當於邏輯 TRUE,會顯示第(2)式的結果。

當第(1)式傳回『0』時,相當於邏輯 FALSE,會顯示第(3)式的結果。


2. 產生分班結果

先選取C欄中有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:分班。

儲存格F2:{=OFFSET($B$1,SMALL(IF(分班=COLUMN(A:A),ROW(分班),),
ROW(1:1)+COUNTIF(分班,"<>"&COLUMN(A:A)))-1,0)}

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

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

(1) IF(分班=COLUMN(A:A),ROW(分班),)

在陣列公式中,判斷分班的儲存格陣列中和 COLUMN(A:A) 相同者,傳回其列號。COLUMN(A:A)=1,向右複製後,得到:COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...。

(2) SMALL(第(1)式,ROW(1:1)+COUNTIF(分班,"<>"&COLUMN(A:A))

利用 SMALL 函數,由小到大依序取出不為 0 的列號。

COUNTIF(分班,"<>"&COLUMN(A:A):計算在分班陣列中,不為「1」的個數。

(3) OFFSET($B$1,第(2)式-1,0)

最後,透過 OFFSET 函數,代入第(2)式的傳回值,得到對應的儲存格內容。

3.使用公式的好處是:不管要分幾班都可以適用。

Excel-執行S型分班(INT,MOD,OFFSET,COLUMN)

文章標籤

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

在 Excel 中有一個數值清單(如下圖B欄),如何計算累計至指定第幾個?

在下圖中,C欄為計算每一個累計的結果,如何在沒有C欄輔助時計算指定個數的累計?

Excel-指定數值清單中累計至第幾個(SUBTOTAL,OFFSET,INDEX)

【公式設計與解析】

方法一:儲存格C2:=SUM($B$2:B2)

方法二:儲存格C2:=SUM(OFFSET($B$2,0,0,ROW(1:1),1))

方法三:儲存格C2:=SUBTOTAL(9,OFFSET($B$2,0,0,ROW(1:1),1))

複製儲存格C2,貼至儲存格C21。

以上三種方式都可以得到每個數值的累計結果。

現在,根據儲存格E2的指定個數,要計算累計結果。

儲存格E4:=INDEX(SUBTOTAL(9,OFFSET($B$2,0,0,ROW(1:20),1)),E2,1)

(1) OFFSET($B$2,0,0,ROW(1:20),1))

利用 OFFSET 函數取得B欄中要計算累計的儲存格區間。

(2) SUBTOTAL(9,OFFSET($B$2,0,0,ROW(1:20),1))

透過 SUBTOTAL 函數指定參數 9,用以指定執行 SUM 功能。

Excel-指定數值清單中累計至第幾個(SUBTOTAL,OFFSET,INDEX)

使用 SUBTOTAL 函數的用意,是可以利用 ROW(1:20) 產生 1~20  的陣列。

image

在執行 SUM 運算時得到累計的結果。

image

(3) INDEX(SUBTOTAL(9,OFFSET($B$2,0,0,ROW(1:20),1)),E2,1)

最後,再利用 INDEX  函數取出指定(儲存格E2)的累計結果。

文章標籤

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

找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼