贊助廠商

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

搜尋本部落格文章資料

目前分類:講義資料 (1957)

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

當你要列印一個長文件時,如果要節省紙張,你可能選擇「雙面列印」。在雙面列印一個A4版面的文件後,直接由側邊裝訂即可使用。但是,如果你是使用A5版面,一頁A4要印兩頁A5,而且一張紙要印 4 頁(雙面列印),印後再切割裝訂,該如何處理?

Word-如何在A4版面雙面列印4個頁面後切割裝訂成冊?

假設在 Word 文件中,你有 8 頁A5版面的內容:

Word-如何在A4版面雙面列印4個頁面後切割裝訂成冊?

其設定為紙張:A4,方向:橫向,頁數:單面雙頁。

Word-如何在A4版面雙面列印4個頁面後切割裝訂成冊?

印表機設定:雙面列印、短邊翻頁。

Word-如何在A4版面雙面列印4個頁面後切割裝訂成冊?

雙面列印 4 頁的順序如下:

Word-如何在A4版面雙面列印4個頁面後切割裝訂成冊?

你只要在列印時自訂列印範圍:1,3,4,2,5,7,8,6:

(第 1 面:1/3頁、第 2 面:4/2頁、第 3 面:5/7頁、第 4 面:8/6頁)

Word-如何在A4版面雙面列印4個頁面後切割裝訂成冊?

列印後每張從中間切割,就可以1/2、3/4、5/6、7/8各為一頁了。

如果你的文件有很多頁,該如何輸入列印順序?可以借助 Excel 來產生列印順序:

Word-如何在A4版面雙面列印4個頁面後切割裝訂成冊?

複製儲存格A12,再貼至 Word 列印對話框中,即可列印。

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

網友提問:在 Excel 的工作表中,如何在不斷新增的儲存格範圍中找出最大值,並且當最後一個數就是最大值時,於最大值上標示紅色前景色彩,而且當不斷插入新的資料時有效。

如下圖,儲存格B2:B15裡的最大值是50(儲存格B15),所以儲存格B16標示為紅色。如何在新增資料時仍然適用原來公式?

Excel-新增一列資料時自動調整公式

由於 Excel 在你新增列或欄時,有自動調整公式的特性,如何克服這個問題?

在本例中,如果你使用:儲存格A16:=MAX(A2:A15),當你在15列和16列之間新增一列時,公式:=MAX(A2:A15)仍會維持不變。但這不是你要的。

改用以下公式:

儲存格A16=MAX(OFFSET(A2,0,0,ROW(A16)-2,1))

複製儲存格A16,貼至儲存格A16:G16。

OFFSET(A2,0,0,ROW(A16)-2,1)用以找出目前16列以上的儲存格範圍,再以 MAX 函數取儲存格範圍內的最大值。

公式自動變更如下:

Excel-新增一列資料時自動調整公式

儲存格A17:=MAX(OFFSET(A2,0,0,ROW(A17)-2,1))

使用此公式,便形成了一個動態的儲存格範圍。

接著,來設定當最後一個數就是最大值時,於最大值上標示紅色前景色彩。

選取儲存格A17:G17。在[編輯格式化的規則]對話框中設定:

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

規則:=A17=OFFSET(A17,-ROW(1:1),0)

格式:紅色前景色彩

在新增一列時,OFFSET(A17,-ROW(1:1),0)永遠對應到儲存格的上一個儲存格。

Excel-新增一列資料時自動調整公式

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

網友問到:如何在 Excel 中,對於一維和二維的數列,計算不重覆的個數。(如下圖)

Excel-計算一維和二維的數列中不重覆的個數(SUMPRODUCT,COUNTIF)

【公式設計與解析】

1. 一維儲存格

儲存格A2:1/COUNTIF(C1:C24,C1:C24)

複製儲存格A2,貼至儲存格A1:A24。

儲存格B2:=SUMPRODUCT(1/COUNTIF(C1:C24,C1:C24))

透過 SUMPRODUCT 函數讓公式執行陣列的運算,本公式的結果相當於:

SUM(A1:A24)

即三個相同時可得三個『1/3』,其和的結果為『1』。

 

2. 二維儲存格

儲存格J2:=SUMPRODUCT(1/COUNTIF(F1:J6,F1:J6))

二維儲存格的運算和一維儲存格相同。

文章標籤

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

本篇文章是前二篇文章的延伸:

Excel-查詢指定字串所在的儲存格位址(MATCH,ADDRESS,IFERROR)

Excel-查詢指定字串所在的儲存格位址(MATCH,ADDRESS,FIND,OFFSET)

如下圖,如何在 Excel 的工作表中,找出分區各自的最大值所在的儲存格位址?

在下圖中,可以指定每一個分區有幾列,並且於I欄中標示每列的最大值(每列有7個儲存格數值)。當找出每一區的最大值(儲存格L3:L15)後,再找出其儲存格位址(儲存格M4:O15)。

Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

【公式設計與解析】

1. 產生序號

儲存格K4:=((ROW(1:1)-1)*$L$1+1)&"-"&ROW(1:1)*$L$1

複製儲存格K4,貼至儲存格K4:K15。

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

本例中,當儲存格L6為 6 時,會依序產生1-6、7-12、13-18、…。

Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

 

2. 找出每區最大值

儲存格L4:=MAX(OFFSET($B$2,(ROW(1:1)-1)*$L$1,0,$L$1,7))

(1) (ROW(1:1)-1)*$L$1

當儲存格L3為3時,公式向下複製時會依序產生 0, 3, 6, 9, …。(作為 OFFSET 函數參數中的相對起始列)

(2) OFFSET($B$2,(ROW(1:1)-1)*$L$1,0,$L$1,7)

根據儲存格L1的數值,透過 OFFSET 函數產生每一區的儲存格範圍。

(3) 再由 MAX 函數取得該範圍中的最大值。(題目有預設每一區中的數值不重覆)

 

3. 找出最大值的列號

儲存格M4:=MATCH(L4,OFFSET($I$2,(ROW(1:1)-1)*$L$1,0,$L$1,1),0)+$L$1*
(ROW(1:1)-1)+1

(1) OFFSET($I$2,(ROW(1:1)-1)*$L$1,0,$L$1,1)

利用 OFFSET 函數找到每一區的儲存格範圍。(例如:序號1-3區為儲存格B2:H4、序號4-6區為儲存格B5:H7)

(2) MATCH(L4,第(1)式,0)

利用 MATCH 函數找到最大值位於第(1)式傳回的儲存格範圍中的第幾個。

(3) 第(2)式+$L$1*(ROW(1:1)-1)+1

找出各區最大值在I欄中的列號。(本例在第 1 區中,最大值是在第 2 列。)

Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

 

4. 找出最大值的欄號

儲存格N4:=MATCH(L4,OFFSET($B$1,M4-1,0,1,7),0)+1

(1) OFFSET($B$1,M4-1,0,1,7)

根據儲存格M4所提供的最大值在I欄中的列號,透過 OFFSET 函數取得該列的儲存格範圍。(在下圖的範例中第 1 區傳回儲存格B4:H4)

(2) MATCH(L4,OFFSET($B$1,M4-1,0,1,7),0)+1

利用 MATCH 函數取得最大值的位置是 4,所以欄號是4+1=5。(因為資料由B欄開始)

Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

 

5. 產生各區最大值儲存格位址

儲存格O4:=ADDRESS(M4,N4,4)

使用 ADDRESS 函數將第(3)式和第(4)式取得的列和欄參數代入,即可得各區最大值的位址。

 

只要改變佔儲存格L1的數值,即可改變分區的列數。

Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

 

【延伸學習】

如果你想要像本例中可以自動標示每一區中的最大值,可以在設定格化的條件中做以下的設定。

1. 選取儲存格B2:H298,設定格式化的條件。

Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

2. 設定格式化的條件

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

條件公式:=B2=MAX(OFFSET($B$2,(INT((ROW(1:1)-1)/$L$1))*$L$1,0,$L$1,7))

格式:紅色前景色彩

Excel-分區查詢最大值所在的儲存格位址(ROW,MATCH,OFFSET,ADDRESS)

文章標籤

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

當在 Excel 中使用設定格式化的條件時,有許多的預設樣式可供選取使用。本例要使用資料横條來練習。

如下圖,列有六種不同的呈現方式,對於正負數和圖形起始位置略有不同之處。

Excel-設定格式化的條件使用横條圖

(本例中,儲存格B2=儲存格A2、儲存格B3=儲存格A3、儲存格B4=儲存格A4、...)

(A)這是 Excel 的預設值,設定:

Excel-設定格式化的條件使用横條圖

最小值:類型(自動)/值(自動)

最大值:類型(自動)/值(自動)

(本例全部都勾選:僅顯示資料横條,所以不會顯示數值。)

Excel-設定格式化的條件使用横條圖

(B)以百分比概念,最小值為百分比=0,所以最小值沒有顯示圖形。設定:

Excel-設定格式化的條件使用横條圖

最小值:類型(百分比)/值(0)

最大值:類型(百分比)/值(100)

Excel-設定格式化的條件使用横條圖

(C) 最小值設為50,設定:

Excel-設定格式化的條件使用横條圖

最小值:類型(數值)/值(=MIN($G$2:$G$7)-50)

最大值:類型(自動)/值(自動)

Excel-設定格式化的條件使用横條圖

(D) 此例數列有工數和負數,設定:

Excel-設定格式化的條件使用横條圖

最小值:類型(數值)/值(=MIN($B$11:$B$16)-200)

最大值:類型(自動)/值(自動)

Excel-設定格式化的條件使用横條圖

負值和座標軸的設定:將相同填滿色彩套用正值橫條。

Excel-設定格式化的條件使用横條圖

(E) 設定負值在中央(0)的左邊(紅色),設定正值在中央(0)的右邊。

Excel-設定格式化的條件使用横條圖

最小值:類型(自動)/值(自動)

最大值:類型(自動)/值(自動)

負值和座標軸的設定:(預設值)

Excel-設定格式化的條件使用横條圖

(F) 正負值不分左右邊,負值以紅色顯示。

Excel-設定格式化的條件使用横條圖

最小值:類型(自動)/值(=MIN($G$11:$G$16)-50)

最大值:類型(自動)/值(自動)

Excel-設定格式化的條件使用横條圖

負值(預設值)和座標軸的設定(無:將負值橫條顯示為正值横條同方向)

Excel-設定格式化的條件使用横條圖

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

網友問到:如何在 Excel 中能由指定的日期開始,依指定週數循環不同格式,該如何處理?

如下圖,本例在儲存格A2中,指定一個日期為起始日期,第一列會自動標示星期幾,再利用儲儲存格I2的循環週數,自動產生間隔週數的儲存格背景和前景色彩。

Excel-依起始日期和循環週數標示日期(MOD,INT)

 

【公式設計與解析】

1. 產生日期

儲存格B2:=A2+1

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

儲存格B3:=A2+7

複製儲存格B3,貼至儲存格B3:G17。

 

2. 產生星期幾

儲存格A1:=A2

複製儲存格A1,貼至儲存格A1:G1。

自定儲存格數值格式:星期三格式

 

3. 設定日期儲存格的格式化的條件

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

選取儲存格A2:G17,設定格式化的條件。

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

規則:=MOD(INT((ROW(A2)-2)/$I$2),2)=0

格式:粉紅色背景和紅色前景

Excel-依起始日期和循環週數標示日期(MOD,INT)

公式:=MOD(INT((ROW(A2)-2)/$I$2),2)=0

INT((ROW(A2)-2)/$I$2):將列號除以循環週數取其商,得到其為第幾個循環週期。

MOD(INT((ROW(A2)-2)/$I$2),2)=0:判斷前式的商是否為 2 的倍數(偶數週期數),若是,則顯示不同格式(粉紅色背景和紅色前景)。

Excel-依起始日期和循環週數標示日期(MOD,INT)

另外,

(參考下圖)如果你想要指定一個日期(儲存格I2)為起始,再依循環週數(儲存格I5)顯示不同的格式,該如何處理?

Excel-依起始日期和循環週數標示日期(MOD,INT)

1. 產生日期

儲存格A2:=TODAY()-WEEKDAY(TODAY(),1)+COLUMN(A:A)

關於公式說明,請參考 :Excel-產生最近四週的日期並標示顏色

2. 設定格式

步驟如下:

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

選取儲存格A2:G17,設定格式化的條件。

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

規則:=(A2>=$I$2)*(MOD(INT((A2-$I$2)/($I$5*7)),2)=0)

格式:紅色前景

Excel-依起始日期和循環週數標示日期(MOD,INT)

公式:=(A2>=$I$2)*(MOD(INT((A2-$I$2)/($I$5*7)),2)=0)

利用二個條件來判斷是否標示為紅色前景:

(A2>=$I$2):儲存格日期要大於或等於儲存格I2。

MOD(INT((A2-$I$2)/($I$5*7)),2)=0:利用INT((A2-$I$2)/($I$5*7))計算每個儲存格日期是第幾個日期,並且位於第幾個循環週期。再利用 MOD 函數判斷是否是偶數週期,若是給予紅色前景。

Excel-依起始日期和循環週數標示日期(MOD,INT)

文章標籤

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

有老師想要在 Excel 的工作表中將總分轉換為等第,該如何處理?

參考下圖,分別以『A...J』 和『甲…癸』來當為等第。

Excel-將分數轉換為文字等第(CHAR,MID,INT)

 

【公式設計與解析】

本例隨意列舉二種不同的函數來完成。

1. 使用 CHAR 函數

儲存格F2:=CHAR(64+INT((E2-180)/10))

INT((E2-180)/10):可以產生以 180 為準,每 10 分為一級距。INT((E2-180)/10)會產生 1, 2, 3, …。

因為『A...J』是由英文字母構成,所以可以使用 CHAR 函數加入參數(ASCII碼)來產生對應的英文字,例如:『A是65』、『B是66』、『C是67』、…。

 

2. 使用 MID 函數

儲存格G2:=MID("甲乙丙丁戊己庚辛壬癸",INT((E2-180)/10),1)

第二種等第是以中文字來表示,無法使用 CHAR 函數,所以改用 MID 函數來取出對應位置的等第。

INT((E2-180)/10):可以產生以 180 為準,每 10 分為一級距。INT((E2-180)/10)會產生 1, 2, 3, …。

文章標籤

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

網路看到的教學文值得推一下!如何在統計圖上只顯示最大值和最小值的數值標籤?

如下圖,在一個數列中只有最大值和最小值的數被標示,該如何處理?

Excel-如何在圖表中只顯示數列最大值和最小值標籤?

 

【設計與解析】

先新增二個輔助欄位:

儲存格C2:=IF(B2=MAX($B$2:$B$17),B2,NA())

儲存格D2:=IF(B2=MIN($B$2:$B$17),B2,NA())

複製儲存格C2:D2,貼至儲存格C2:D17。

接著,選取儲存格B2:D17,新增折線圖並顯示標籤。

Excel-如何在圖表中只顯示數列最大值和最小值標籤?

選取折線圖上的數列標籤:

Excel-如何在圖表中只顯示數列最大值和最小值標籤?

按一下 Delete 鍵,刪除這些標籤。(只會刪除三組數列中的一組)

Excel-如何在圖表中只顯示數列最大值和最小值標籤?

稍加整理圖表,即可得如下樣式。

Excel-如何在圖表中只顯示數列最大值和最小值標籤?

在使用實務上,不要將輔助欄位隱藏,因為最大值和最小值標籤也會被隱藏。若有需要則可以將輔助欄位移出畫面外即可。

Excel-如何在圖表中只顯示數列最大值和最小值標籤?

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

有老師在評量成績時,想要以符號或等第來代替分數,以模糊對分數的斤斤計較。因此,想要以符號來表示學習狀況(如下圖),在 Excel 中可以如何來處理 ?

假設老師要以手動方式(由下拉式清單中選取)來輸入學生的學習狀況(如下圖):

Excel-以符號代替數字來呈現學生的學習表現(格式化的條件設定)

可以在儲存格G2:J21中設定格式化的條件:

(1) 規則類型:根據其值格式化所有儲存格

(2) 格式樣式:圖示集

(3) 圖示樣式:如下圖,本例挑選四個圖示

(4) 勾選「只顯示圖示」

(5) 圖示設定分別是:數值>3、數值>2、數值>1、數值<=1

(數值>3對應4、數值>2對應3、數值>1對應2、數值<=1對應1)

Excel-以符號代替數字來呈現學生的學習表現(格式化的條件設定)

只要在儲存格中輸入1~4,即可顯示對應的符號。

Excel-以符號代替數字來呈現學生的學習表現(格式化的條件設定)

如果你要直接以學生的分數來顯示符號(如下圖):

Excel-以符號代替數字來呈現學生的學習表現(格式化的條件設定)

設定如下:

Excel-以符號代替數字來呈現學生的學習表現(格式化的條件設定)

如果要以學生的分數位於全部的百分比來顯示符號(如下圖):

Excel-以符號代替數字來呈現學生的學習表現(格式化的條件設定)

設定如下:(類型已改為百分比)

Excel-以符號代替數字來呈現學生的學習表現(格式化的條件設定)

在 Excel 中還有其他符號可以選,但其圖示集,同一類型最大只有五種變化。

Excel-以符號代替數字來呈現學生的學習表現(格式化的條件設定)

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

有老師想要在 Excel 中產生多個 5 個字元的亂數字串(參考下圖),該如何處理?

亂數字串的內容是由以下字元構成:

ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789

這也是我們一般所指的文數字組合。

Excel-依指定的字元內容產生固定長度的亂數字串(MID,INT,RAND)

 

【公式設計與解析】

公式設計是由原始字串中取得任一字元開始:

MID($A$2,INT(RAND()*LEN($A$2))+1,1)

(1) LEN($A$2):取得儲存格A2內容的文字長度。

(2) INT(RAND()*LEN($A$2))+1:取得 1~儲存格A2內容的文字長度的亂數值。

(3) MID($A$2,第(2)式,1):取得原始字串中的任意一個字元。

若要產生 5 個任意字元,則公式如下:

儲存格A5:=MID($A$2,INT(RAND()*LEN($A$2))+1,1)&
MID($A$2,INT(RAND()*LEN($A$2))+1,1)&
MID($A$2,INT(RAND()*LEN($A$2))+1,1)&
MID($A$2,INT(RAND()*LEN($A$2))+1,1)&
MID($A$2,INT(RAND()*LEN($A$2))+1,1)

公式是利用『&』串接 5 個原始字串中的任意一個字元而組成。

如上圖,將儲存格A5,複製到儲存格A5:F13。每按一次 F9 鍵,即可產生多組的 5 個字元的亂數字串。

你也可以試著改變原始字串中的文字內容(例如:中文或符號等),隨機顯示的 5 個字元也會隨之改變。

文章標籤

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

根據前一篇文章:

Excel-查詢指定字串所在的儲存格位址(MATCH,ADDRESS,FIND,OFFSET)

延伸的公式設計(使用FIND,SEARCH,SUSTITUTE),其公式執行概念大同小異。(參考下圖)

Excel-查詢指定字串所在的儲存格位置(FIND,SEARCH,SUSTITUTE)

 

【公式設計與解析】

選取儲存格B1:B21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:字串。

 

《FIND函數》

儲存格C2:{=IFERROR(SMALL(IFERROR((FIND($F$2,字串)>0)*ROW(字串),""),
ROW(1:1))-1,"")}

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

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

 

《SEARCH函數》

儲存格D2:{=IFERROR(SMALL(IFERROR((SEARCH($F$2,字串)>0)*ROW(字串),""),
ROW(1:1))-1,"")}

使用 SEARCH 函數的關念和 FIND 函數完全相同。

 

《SUBSTITUTE函數》

儲存格E2:{=IFERROR(SMALL(IF(SUBSTITUTE(字串,$F$2,"")<>字串,
ROW(字串),""),ROW(1:1))-1,"")}

使用 SUBSTITUTE 函數並不是以尋找的關念來操作,而是將儲存格中的內容置換掉所指定字元,若結果不相同者,則表示該字串有包含指定字元。

 

相同公式,適用於不管指定幾個字元,但不適用於空白。若是沒有符合的字串,則會顯示空白。

Excel-查詢指定字串所在的儲存格位置(FIND,SEARCH,SUSTITUTE)

文章標籤

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

網友問到:(參考下圖)在 Excel 中使用 SUMIFS 函式,看起來公式是正確的,但結果卻是錯誤的。大家來除錯吧!

儲存格H2:=SUMIFS(工作表2!N:N,工作表2!L:L,工作表1!A2,工作表2!K:K,TODAY(),工作表2!M:M,"出")

複製儲存格H2,貼至儲存格H2:H5。

Excel-公式中使用範圍過大的儲存格範圍導致公式運算結果出錯,該如何解決?

根據我的測試,因為公式中使用了多個類似「工作表2!N:N」這類的儲存格範圍,所以導致計算結果不正確,解決方法是:

按一下F9鍵(重新運算)

Excel-公式中使用範圍過大的儲存格範圍導致公式運算結果出錯,該如何解決?

不過,在此提醒,盡量不要使用範圍過大的儲存格範圍來運算,雖然你可以按 F9 鍵來解決,但是你只要忘記了,結果就出錯了。所以,只要稍加修改公式即可解決了。

例如:(儲存格範圍自訂)

儲存格H2:=SUMIFS(工作表2!$N$2:$N$7,工作表2!$L$2:$L$7,工作表1!A2,
工作表2!$K$2:$K$7,TODAY(),工作表2!$M$2:$M$7,"出")

或是

儲存格H2:=SUMPRODUCT((工作表2!$K$2:$K$7=TODAY())*(工作表2!$L$2:$L$7
=工作表1!A2)*(工作表2!$M$2:$M$7="出")*工作表2!$N$2:$N$7)

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

根據上一篇文章:

Excel-查詢指定字串所在的儲存格位址(MATCH,ADDRESS,IFERROR)

如果指定的文字位於多個儲存格時,如何列出標示位址和其內容的清單?

image

【公式設計與解析】

儲存格E2:

{=IFERROR(ADDRESS(SMALL(IF(IFERROR(FIND($D$2,$A$2:$A$23),0)>0,
ROW($A$2:$A$23),""),ROW(1:1)),1,4),"")}

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

(1) FIND($D$2,$A$2:$A$23)

在陣列公式中,利用 FIND 函數找出在儲存格A2:A23範圍中的每個儲存格是否含有儲存格D2的內容。若有,會傳回一個數值(位置);若否,傳回錯誤訊息。

(2) IFERROR(FIND($D$2,$A$2:$A$23),0)

若第(1)式傳回錯誤訊息,則利用 IFERROR 函數將錯誤訊息轉換為『0』。

(3) IF(第(2)式>0,ROW($A$2:$A$23),"")

在陣列公式中,若第(2)式傳回數值並且大於 0,表示該儲存格含有儲存格D2的內容,則該式會傳回在ROW($A$2:$A$23)所對應的列號,否則傳回空字串。

(4) SMALL(第(3)式,ROW(1:1))

利用 SMALL 函數將第(3)式傳回的列號,由小至大依序取出。ROW(1:1)向下複製公式時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→……。

(5) ADDRESS(第(4)式,1,4)

將第(4)式代入 ADDRESS 函數中,即可傳回對應的儲存格位址。(參數 4 乃指定傳回相對位址)

(3) IFERROR(ADDRESS(第(4)式,1,4),"")

若儲存格A2:A23的資料內容不存在儲存格D2的內容,則以 IFERROR 函數使其傳回空字串。

同理,儲存格F2:

{=IFERROR(OFFSET($A$1,SMALL(IF(IFERROR(FIND($D$2,$A$2:$A$23),0)>0,
ROW($A$2:$A$23),""),ROW(1:1))-1,0),"")}

在此特別改用 OFFSET 函數來取得儲存格內容,你可以自行練習看看。

請參閱上一篇文章:

Excel-查詢指定字串所在的儲存格位址(MATCH,ADDRESS,IFERROR)

Excel-查詢指定字串所在的儲存格位址(MATCH,ADDRESS,FIND,OFFSET)

文章標籤

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

網友想要在 Excel 中的資料清單中查詢指定字串所在的儲存格位址,該如何處理?

參考下圖,要搜尋指定字串的內容在A欄中的儲存格位址。

Excel-查詢指定字串所在的儲存格位址(MATCH,ADDRESS,IFERROR)

 

【公式設計與解析】

儲存格B2:=IFERROR(ADDRESS(MATCH(D2,A2:A23,0)+1,1,4),"字串不存在")

(1) MATCH(D2,A2:A23,0)

利用 MATCH 函數找出儲存格D2在儲存格A2:A23範圍中的位置,傳回一個數值。

(2) ADDRESS(MATCH(D2,A2:A23,0)+1,1,4)

將第(1)式代入 ADDRESS 函數中,即可傳回對應的儲存格位址。(參數 4 乃指定傳回相對位址)

(3) IFERROR(ADDRESS(MATCH(D2,A2:A23,0)+1,1,4),"字串不存在")

若儲存格A2:A23的資料內容不存在儲存格D2的內容,則以 IFERROR 函數使其傳回:字串不存在。

Excel-查詢指定字串所在的儲存格位址(MATCH,ADDRESS,IFERROR)

 

【延伸閱讀】

Excel-查詢指定字串所在的儲存格位址(MATCH,ADDRESS,FIND,OFFSET)

文章標籤

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

網友問到一個實用的問題:如何在 Excel 的工作表中根據指定的文字以標示不同色彩?

方法一:(參考下圖)使用 FIND 函數。

Excel-判斷儲存格中含有特定字元則標示不同色彩(FIND,SUBSTITUTE)

選取儲存格B2:B27,設定格式化的規則,輸入公式:

=(FIND($D$2,B2)>0)*($D$2<>"")

(1) FIND($D$2,B2)>0

條件一:利用 FIND 函數,尋找在儲存格B2中是否含有儲存格D2的字元,會傳回一個大於 0 的數字(位置),或是傳回一個錯誤訊息(找不到)。

(2) $D$2<>""

條件二:判斷儲存格D2是否不是空白內容。

(3) (FIND($D$2,B2)>0)*($D$2<>"")

其中的運算字元『*』相當於執行邏輯 AND 運算,兩個條件皆成立者傳回 TRUE。

 

方法二:(參考下圖)使用 SUBSTITUTE 函數。

Excel-判斷儲存格中含有特定字元則標示不同色彩(FIND,SUBSTITUTE)

選取儲存格B2:B27,設定格式化的規則,輸入公式:

=(SUBSTITUTE(B2,$D$2,"")<>B2)*($D$2<>"")

(1) SUBSTITUTE(B2,$D$2,"")<>B2

條件一:利用 SUBSTITUTE 函數將儲存格B2中的儲存格D2的字元置換為空字串,若結果和儲存格B2不相同,表示儲存格B2中含有儲存格D2的內容,傳回 TRUE;否則,傳回 FALSE

(2) $D$2<>""

條件二:判斷儲存格D2是否不是空白內容。

(3) (SUBSTITUTE(B2,$D$2,"")<>B2)*($D$2<>"")

其中的運算字元『*』相當於執行邏輯 AND 運算,兩個條件皆成立者傳回 TRUE。

文章標籤

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

孩子三更半夜還在寫報告,突然問到在 Excel 中,如何把下圖左轉換成下圖右的圖表?

(座標軸顯示0, 1, 2, 3, 4, 5,其中 0 顯示在垂直座標軸上。)

Excel-製作統計圖的座標軸顯示0,1,2,…,並且0位於垂直座標軸上

你的做法會是如何?

(X)(1) 選取儲存格A1:B7,產生折線圖。參考下圖上。

結果 Excel 將項目欄位也視為數列(因為項目中的內容都是數值)了。

(X)(2) 選取儲存格B1:B7,產生折線圖。參考下圖下。

在座標軸上無法顯示『項目』的內容(0、1、2、3、4、5)。

Excel-製作統計圖的座標軸顯示0,1,2,…,並且0位於垂直座標軸上

該如何解決這個問題?

將項目中的『0』改為『'0』(將數字 0 改為文字 0),其餘『1、2、3、4、5』,皆仿相同做法。

選取儲存格A18:B23,產生一個折線圖,如下圖。

Excel-製作統計圖的座標軸顯示0,1,2,…,並且0位於垂直座標軸上

設定座標軸格式,在[座標軸位置]區中,目前的選項是:刻度與刻度之間相距。

Excel-製作統計圖的座標軸顯示0,1,2,…,並且0位於垂直座標軸上

將選項修改為:刻度上,即可將座標顯示如題意要求了。

Excel-製作統計圖的座標軸顯示0,1,2,…,並且0位於垂直座標軸上

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

最近的教學又提到了長文件編輯,現在愈來愈強調學生應有實做的能力,會編輯 100 個 1 頁的文件和會編輯 1 個 100 頁的文件,是不一樣的!本篇教材會提到:頁首、頁尾、分節的概念,來加以練習長文件編輯。

在 Word 文件中準備了:「首頁、目錄、本文」三個區塊,如何能在這三個區塊中有各自的頁首和頁尾,並能不同的頁碼格式?要求如下:

1. 首頁區:不顯示頁首文字、不在頁尾顯示放頁碼。(頁首、頁尾皆留空白)

2. 目錄區:頁首顯示「Word練習」,頁尾顯示頁碼,頁碼格式為「Ⅰ、Ⅱ、Ⅲ、...」。

3. 本文區:頁首顯示「Word練習」,頁尾顯示頁碼,頁碼格式為「1、2、3、...」。

以下是三頁分別代表上述的三個區域(已經先以「分頁符號」區隔此三個部分):

image

依據題意要求,因為此三個區域有三種不同的設定,所以要將此三個區域改分成三節。此時,你可以使用[版面配置/分節符號/下一頁]選項,來取代分頁符號。(首頁為第 1 節、目錄為第 2 節、本文為第 3 節)

image

此時,原先的分隔符號「下一頁」已被改「分節符號(下一頁)」。

image

當你在頁首或頁尾的編輯模式之下,即可看見頁首或頁尾分別有:節 1、節 2、節 3。

並且觀察到有文字「同前」字樣,這表示頁首或頁尾的設定會和前一節關聯。(預設值就設為「同前」)

image

先將插入點移至「頁首-節 2」,按一下「連結到前一節」以切換為不連結,並輸入「Word練習」。

觀察下圖:因為設定目錄區的頁首沒有連結到前一節,所以目錄區的首頁不會顯示文字「Word練習」,而本文區頁首還留有「同前」,所以本文區的頁首也會顯示「Word練習」。

image

接著,移至頁尾來編輯。

分別將插入點移至「頁尾-節 2」和「頁尾-節 3」,按一下「連結到前一節」以切換為不連結。在目錄區和本文區的頁尾都不會顯示「同前」。

image

在「頁尾-節 2」中插入頁碼,並於頁碼格式設定中選取數字格式:Ⅰ、Ⅱ、Ⅲ、...,在起始頁碼中選取:Ⅰ。

image

在「頁尾-節 3」中插入頁碼,並在頁碼格式設定中選取數字格式:1, 2, 3, ...,在起始頁碼中選取:1。

image

目前結果如下:

image

當你編好多頁的文件之後,首頁會和其他頁有不同的頁首和頁尾,目錄和本文會有相同的頁首內容,但會有不同的頁尾內容(有不同的頁碼格式)。

image

 

【延伸練習】

如果你想要讓本文區中的頁首裡,讓奇數頁和偶數頁有不同內容,則可以移到「頁首-節 3」中設定版面配置。勾選:奇偶數不同,套用至:此一節。

Word-在長文件中設定首頁、目錄、本文有不同的頁首和頁尾

觀察本文區中的頁首,你會發現出現了:奇數頁頁首-節 3和偶數頁頁首-節 3等。而且偶數頁的「Word練習」文字已經消失,而且頁尾中的頁碼也已消失。

Word-在長文件中設定首頁、目錄、本文有不同的頁首和頁尾

請移至「偶數頁頁首-節 3」中,重新輸入「Word練習」文字(靠右對齊),及移至「偶數頁頁尾-節 3」中設定頁碼。

Word-在長文件中設定首頁、目錄、本文有不同的頁首和頁尾

文章標籤

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

最近幾年在學校裡的工作範疇,有包含了學校網頁的建置(Windwos平台)。但是有些網頁內容應該由其他同仁自已處理,而非再轉一手要由我代勞。

今天想到要回顧這個問題,並且描述我的解決方案!問題:

同仁:不會也不想要建置網頁,但又想要取得檔案的超連結或是提供下載網頁。

自己:不想要隨時待命替同仁轉發檔案和製作網頁,即無時效也責任不清。

(如下圖,提供下載網頁並且可以取得檔案的超連結)

幫助不會網頁製作的同仁產生檔案下載網頁(ASP網頁+FTP+檔案同步軟體)

為了讓大家的做法都是最少且最簡單,我設計的做法是:

1. 寫一個 ASP 網頁程式提供給同仁,可以自動將資料夾中的檔案呈現如上圖的網頁。

2. 在伺服器上開啟 FTP 功能來傳輸網頁和檔案。

3. 直接調整(新增/刪除/重新命名)資料夾中的檔案,檔案命名時要能有辨識性。

4. 使用同步軟體(例如:Allway Sync)進行 FTP 的同步。

5. 開啟網頁檢查是否完成同步更新。

例如:

首先,我自己先建立一個可以自動將資料夾中的檔案呈現為網頁的 ASP 網頁程式(由一個資料夾組成),並提供給每一位同仁。

幫助不會網頁製作的同仁產生檔案下載網頁(ASP網頁+FTP+檔案同步軟體)

同仁們先開啟檔案總管,在我提供的網頁程式資料夾中的指定位置新增一個檔案。

幫助不會網頁製作的同仁產生檔案下載網頁(ASP網頁+FTP+檔案同步軟體)

接著,啟動 Allway Sync 檔案同步軟體進行同步工作:(發現新增 1 個檔案)

(Allway Sync:https://allwaysync.com/,下圖中,同步的兩方,一邊是電腦內的一個資料夾,另一邊是伺服器上的網站。設定為:電腦同步到網站)

幫助不會網頁製作的同仁產生檔案下載網頁(ASP網頁+FTP+檔案同步軟體)

回到網頁中檢視,網頁中的確新了一個檔案。

幫助不會網頁製作的同仁產生檔案下載網頁(ASP網頁+FTP+檔案同步軟體)

當然,如果你進行了刪除的動作,同步之後也可以將網頁中的檔案刪除。

幫助不會網頁製作的同仁產生檔案下載網頁(ASP網頁+FTP+檔案同步軟體)

利用這個方法,也讓多個同仁在原有的學校網頁架構下,只需在檔案總管裡操作檔案,再進行同步,就能自行建立檔案下載網頁,並且可以取得任何檔案的超連結。

雖然現雲端硬碟發達,無論是公有雲和私有雲都是一應俱全,沒多久就不會有人再使用這些自創的工作流程,但是只要能解決問題的方法,都是有價值的!會不會有人繼續使用,是要取決於用的人是否覺得有價值囉!至少也曾經解決了不少學校同仁的問題。

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

繼前面二篇文章 :

(1) Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET)

(2) Excel-由原始清單中挑出符合指定內容的清單(ROW,COLUMN,INT,MOD,OFFSET)

網友又問到:(如下圖)如何分年/分月篩選資料?

1. 篩選指定年份

Excel-Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET,YEAR,MONTH)

【公式設計與解析】

儲存格F2:{=IFERROR(OFFSET(A$1,SMALL(IF(YEAR(日期)=$E$2,ROW(日期),
""),ROW(1:1))-1,0),"")}

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

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

公式原理同:

(1) Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET)

(2) Excel-由原始清單中挑出符合指定內容的清單(ROW,COLUMN,INT,MOD,OFFSET)

其中差異之處,在於條件的設定:YEAR(日期)=$E$2,利用 YEAR 函數篩選『年』。

 

2. 篩選指定年/指定月

Excel-Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET,YEAR,MONTH)

【公式設計與解析】

儲存格F2:{=IFERROR(OFFSET(A$1,SMALL(IF((YEAR(日期)=$E$2)*(MONTH(
日期)=$E$4),ROW(日期),""),ROW(1:1))-1,0),"")}

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

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

公式原理同:

(1) Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET)

(2) Excel-由原始清單中挑出符合指定內容的清單(ROW,COLUMN,INT,MOD,OFFSET)

其中差異之處,在於條件的設定:(YEAR(日期)=$E$2)*(MONTH(日期)=$E$4),利用 YEAR 函數篩選『年』和 MONTH 函數篩選『月』。其中的『*』運算子,相當於執行邏輯 AND 運算。

文章標籤

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

今天同時有兩個網友問了類似的問題,另一篇請參考:

Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET)

本篇和上一篇的差異在於資料清單的安排不同。

Excel-由原始清單中挑出符合指定內容的清單(ROW,COLUMN,INT,MOD,OFFSET)

 

【公式設計與解析】

上一篇文章的做法可以使用篩選工具,本篇則必須要以撰寫公式來完成這個工作。

儲存格B15:

{=IFERROR(OFFSET($B$1,SMALL(IF($A15=$A$2:$A$11,ROW($A$2:$A$11),
""),INT((COLUMN(A:A)+1)/2))-1,MOD(COLUMN(A:A)-1,2)),"")}

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

複製儲存格F15,貼至儲存格F15:I17。

公式解釋請先參考:

Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET)

以下來解釋和上一篇不同之處:

(1) INT((COLUMN(A:A)+1)/2)

公式向右複製時,INT((COLUMN(A:A)+1)/2)會產生:1, 1, 2, 2, 3, 3, …。

(2) MOD(COLUMN(A:A)-1,2)

公式向右複製時,MOD(COLUMN(A:A)-1,2)會產生:0, 1, 0, 1, 0, 1, ….。

公式細節請參考:

Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET)

Excel-由原始清單中挑出符合指定內容的清單(ROW,COLUMN,INT,MOD,OFFSET)

文章標籤

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

找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼