贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201606 (36)

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

參考下圖,網友想要 Excel 的工作表中指定數值清單出現的位置,如何處理?例如:下圖中的C欄有 3 個數值,在儲存格B1指定一個位置,在A欄中的相對位置顯示C欄的 3 個數值。

Excel-指定數值清單出現的位置(OFFSET)


【公式設計與解析】

儲存格A1:

=IF((ROW(A1)>=$B$1)*(ROW(A1)<$B$1+3),OFFSET($C$1,ROW(A1)-$B$1,0),0)

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

(1) (ROW(A1)>=$B$1)*(ROW(A1)<$B$1+3)

利用 ROW(A1) 的傳回值判斷儲存格所在的列號,當公式向下複製時,ROW(A1)=1→ROW(A2)=2→ROW(A3)=3→...。

(ROW(A1)>=$B$1):第一個條件,判斷儲存格列號是否大於儲存格B1數值。

(ROW(A1)<$B$1+3):第二個條件,判斷儲存格列號是否小於儲存格B1數值+3。

其中『*』相當於執行邏輯 AND 運算。


(2) OFFSET($C$1,ROW(A1)-$B$1,0)

利用 OFFSET 函數,以儲存格C1為起點,ROW(A1)-$B$1為相對位移,即可產生對應的內容。

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

在使用 Word 時你會重覆用到一些圖文的組合嗎?如何才能快速而有效的整組插入文件中?而 Word 中其實已經內建立許多的圖文組合可以選用,你知道嗎?

先來看看 Word 內建提供的建置組塊。在[插入/文字]功能表中有一個[快速組件]功能,選取其中的「建置組塊組合管理」:

Word-使用建置組塊及新增自動圖文集

在[建置組塊組合管理中]對話框中,已在清單中列有多個建置好的組塊,你可以在對話框右側先行檢視預覽的結果,也可以按一下[編輯內容]按鈕,進一步修改設定值。

Word-使用建置組塊及新增自動圖文集

當你插入所選取的組塊後:

Word-使用建置組塊及新增自動圖文集

你可以對這個組塊的內容進行編輯:

Word-使用建置組塊及新增自動圖文集

而組置內建有:文字方塊、目錄、自動圖文集、表格、封面、頁尾、頁首、頁碼、頁碼(頁的底端)、頁碼(頁的頂端)、頁碼(邊界)、書目、浮水印等圖庫。而這些圖庫其實已分散在各個功能中,例如:當你插一個封面時,即用取用封面圖庫中的一個項目。

Word-使用建置組塊及新增自動圖文集

上下兩圖對照一下,即可發現一些封面的組塊已包裝在[插入/封面頁]的清單中:

Word-使用建置組塊及新增自動圖文集

看完這些內建的圖文組塊,你是否也想自訂、新增一些圖文組塊?

先在文件中插入好文字和圖片,然後選取[插入/文字]功能表的「自動圖文集」,接著選取「儲存格選取項目至自動圖文集庫」選項。

Word-使用建置組塊及新增自動圖文集

為這個組塊進行設定:

Word-使用建置組塊及新增自動圖文集

日後,你只要選取這個組塊,即可在文件中插入這個圖文的整合內容。

Word-使用建置組塊及新增自動圖文集

再次檢視[建置組塊組合管理]對話框,你會發現剛剛新增的自動圖文集。

Word-使用建置組塊及新增自動圖文集

自訂的組塊是放在這部電腦 Word 程式中,而非跟著文件走。

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

在 Word 中的長文件編輯時,如果你建立了文件目錄(如下圖),一旦你選擇更新功能變數後,再選取更新整個目錄時。

Word-設定更新目錄時保持已設定的文字格式及超連結不顯示底線

有可能會遇到原先設定好的目錄格式也隨之被更新了,如下圖,在小節部分的格式已跑掉了(和上圖比較)。每更新一次,就得重新設定目錄的格式一次,該如何避免這種狀況呢?

Word-設定更新目錄時保持已設定的文字格式及超連結不顯示底線

參考以下的步驟:

1. 點選[常用/樣式]功能表的右下角圖示,開啟[樣式]功能窗格。

2. 在[樣式]功能窗格中有所有樣式的清單,找到和目錄相關的樣式。例如:目錄 1 和目錄 2 等。

3. 由於小節的格式在目錄 2 中,所以選取「目錄 2」右側的下拉式清單,再選取「修改」選項。

Word-設定更新目錄時保持已設定的文字格式及超連結不顯示底線

4. 在[修改樣式]對話框中修改樣式為你想要的格式。

Word-設定更新目錄時保持已設定的文字格式及超連結不顯示底線

下次再更新整個目錄時,文字格式就不會變來變去了。

同理,如果你想要在 Word 文件中的超連結不要顯示底線該如何處理?

在[樣式]功能窗格中有所有樣式的清單,修改「超連結」樣式:

Word-設定更新目錄時保持已設定的文字格式及超連結不顯示底線

只要取消設定「底線」即可。

Word-設定更新目錄時保持已設定的文字格式及超連結不顯示底線

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

在練習了先前的三個操作:

(1) Word-設定每個章節標題顯示在頁首中

(2) Word-設定每個章節標題顯示在圖號中

(3) Word-建立內文目錄和圖目錄

這篇文章要來練習建立內文目錄和圖目錄。

首先,我們要在這個 6 頁的文件中插入頁碼,選取[插入/頁首及頁尾]功能表中的[頁尾]項目中的一個樣式,本例為「側邊線條」。

Word-建立目錄和內文不同頁碼

接著,在[設計/位置]功能表中「頁面底端至頁尾」為『1 公分』。

Word-建立目錄和內文不同頁碼

再來,於第 2 頁最後,選取[版面配置/版面設定]功能表中的[分隔設定]下拉式清單中挑選「分節符號/下一頁」選項。

Word-建立目錄和內文不同頁碼

如此讓文件共分為二節,第一節是 1, 2 頁,第二節是 3, 4, 5, 6 頁。

Word-建立目錄和內文不同頁碼

移到第 1 頁的頁尾,再選取[設計/頁首及頁尾]功能表的[頁碼]下拉式清單中的「頁碼格式」。

Word-建立目錄和內文不同頁碼

在[頁碼格式]對話框中的[數字格式]下拉式清單中選取一種格式(本例為I, II, III, ...):

Word-建立目錄和內文不同頁碼

此時頁碼顯示如下:

Word-建立目錄和內文不同頁碼

先移到第 3 頁的頁尾,再選取[設計/頁首及頁尾]功能表的[頁碼]下拉式清單中的「頁碼格式」。然後在[頁碼格式]對話框中的[數字格式]下拉式清單中選取另一種格式(本例為1, 2, 3, ...):

Word-建立目錄和內文不同頁碼

此時頁碼顯示如下:

Word-建立目錄和內文不同頁碼

最後,在內文目錄上按一下右鍵,選取「更新功能變數」。(也可以使用快速鍵 F9)

Word-建立目錄和內文不同頁碼

選取:只更新頁碼。

Word-建立目錄和內文不同頁碼

內文頁碼隨即改變:

Word-建立目錄和內文不同頁碼

你可以使用相同方法更新圖的目錄。

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

在練習了先前的二個操作:

(1) Word-設定每個章節標題顯示在頁首中

(2) Word-設定每個章節標題顯示在圖號中

這次要用這個檔案來練習建立內文目錄和圖的目錄。如下圖,目前有四頁內容,其中包含標題、圖片、圖號。並且已新增 2 頁空白頁待用。

特別注意:其中各頁標題已先行設定了樣式(段落設定為「階層1」)。

Word-建立內文目錄和圖目錄

首先來建立內文目錄:

1. 插入點移至第 1 頁的最前端。

2. 選取[參考資料/目錄]功能表中的「目錄」選項,再選取「自訂目錄」。

Word-建立內文目錄和圖目錄

3. 在[目錄]對話框中的[目錄]標籤中,由[格式]下拉式清單選取一個樣式(本例選取「正式的」)。

4. 設定「顯示階層」為『1』。

5. 按下[確定]按鈕。

Word-建立內文目錄和圖目錄

最後結果如下:

Word-建立內文目錄和圖目錄

接著來建立圖目錄:

1. 插入點移至第 2 頁的最前端。

2. 選取[參考資料/標號]功能表中的「插入圖表目錄」。

Word-建立內文目錄和圖目錄

3. 在[圖表目錄]對話框中的[圖表目錄]標籤中,由[格式]下拉式清單選取一個樣式(本例選取「特別的」)。

4. 設定「標題標籤」為『圖』。(參考先前文章,圖號已預設好標籤為『圖』)

5. 按下[確定]按鈕。

Word-建立內文目錄和圖目錄

結果如下:

Word-建立內文目錄和圖目錄

文件最終結果如下:

Word-建立內文目錄和圖目錄

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

根據前一篇文章:Word-設定每個章節標題顯示在頁首中,如何想要將標題文字顯示在圖號中?其實觀念和做法是大同小異的,參考以下的練習。

如下圖,Word 文件中共有四頁,每頁有一個標題和一張圖片。

Word-設定每個章節標題顯示在圖號中

1. 將插入點移至第一張圖的下方。

2. 選取[參考資料/標號]功能表中的「插入標號」選項。

Word-設定每個章節標題顯示在圖號中

3. 你可以在[標籤]下拉式清單中選取一個標籤,按一下[確定]按鈕。

Word-設定每個章節標題顯示在圖號中

或是按一下[新增標籤]按鈕,自行輸入一個新的標籤名稱。

Word-設定每個章節標題顯示在圖號中

4. 結果如下圖,目前只有呈現圖的號碼,沒有圖的名稱。

Word-設定每個章節標題顯示在圖號中

5. 將圖名設定一些格式,並新增樣式(本例名稱設為「圖樣」)。

Word-設定每個章節標題顯示在圖號中

接著,參考前一篇文章:Word-設定每個章節標題顯示在頁首中,為圖的名稱設定功能變數。

6. 將插入點移至「圖 1」之後,選取[插入/文字]功能表中的「快速組件」選項,再選取「功能變數」。

7. 在[功能變數]對話框中的[功能變數名稱]清單中選取「StyleRef」名稱。

8. 在[樣式名稱]清單中選取「標題 1」。(每頁標題已先設定為「標題1」樣示)

Word-設定每個章節標題顯示在圖號中

如下圖,標題(本例為:Word)已以功能變數方式插入「圖 1」文字之後。

Word-設定每個章節標題顯示在圖號中

9. 複製「圖 1 Word」整段文字(必須包含段落標記),貼至每張圖片之下。

目前看到的全部都是相同內容:圖 1 Word。

Word-設定每個章節標題顯示在圖號中

10. 按一下 Ctrl + A 鍵,全選所有內容。

11. 按一下 F9 鍵,更新所有功能變數的內容。(如下圖)

Word-設定每個章節標題顯示在圖號中

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

在編輯 Word 文件時如何讓頁首就顯示該章節的標題名稱呢?

參考下圖,假設所有的章節都已為其文字格式設定了樣式(本例為「標題 1」)。由此開始:

1. 我們將插入點移至第一個標題上。

2. 在[常用/樣式]功能表中的「標題 1」上按一下右鍵,並選取「修改」選項。

Word-設定每個章節標題顯示在頁首中

3. 按一下[修改樣式]對話框中的「格式」按鈕,並選取「段落」選項。

Word-設定每個章節標題顯示在頁首中

4. 在[段落]對話框中的「分行與分頁設定」標籤中,勾選[分頁]區中的「段落前分頁」。

Word-設定每個章節標題顯示在頁首中

結果如下圖,每一個標題都自動位在每一頁的第一列。

Word-設定每個章節標題顯示在頁首中

5. 接著切換至頁首中。

6. 選取[插入/文字]功能表中的「快速組件」選項,再選取「功能變數」。

Word-設定每個章節標題顯示在頁首中

7. 在[功能變數]對話框中的[功能變數名稱]清單中選取「StyleRef」名稱。

8. 在[樣式名稱]清單中選取「標題 1」。

Word-設定每個章節標題顯示在頁首中

頁首會插入本頁的標題名稱:

 Word-設定每個章節標題顯示在頁首中

結果如下,每頁的頁首會顯示所在頁的標題名稱。

Word-設定每個章節標題顯示在頁首中

如果你在這個標題之下輸入的文字超過一頁,你會發現下一頁的頁首也是會跟隨著上一頁的標題名稱。

Word-設定每個章節標題顯示在頁首中

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

有網友問到:如下圖,在 Excel 中若要將 A 表格轉換為 B 表格,該如何處理。

image


【公式設計與解析】

儲存格H13:

=OFFSET($A$1,SUMPRODUCT(($B$3:$E$12=G3)*ROW($A$3:$A$12))-1,0,1,1)

($B$3:$E$12=G3)*ROW($A$3:$A$12):在 SUMPRODUCT 函數中以 $B$3:$E$12=G3 為條件,再乘以 ROW($A$3:$A$12),可以求得符合條件者的『列號』。例如:儲存格G3的內容為『1』,對照A表格,所以傳回列號值『3』。

SUMPRODUCT 函數的結果減1,再置入 OFFSET 函數求得相對位置的內容。

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

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

網友問到一個 Excel 應用的問題:如下圖,一個禮盒包裝三種產品,分別是甲5個、乙4個、丙3個,若現有產品數量甲310個、乙245個、丙185個,試問甲乙丙最多可以包裝幾個禮盒,且各會剩下幾個產品。

Excel-產品包裝數量的計算(INT,MIN)


【公式設計與解析】

最多可生產禮盒A數量:

儲存格D9:=MIN(INT(B6/B3),INT(C6/C3),INT(D6/D3))

利用 INT 函數求得甲、乙、丙三者之(現有數量÷基本數量)的最小值。

剩餘數量:

儲存格B7:=B6-$D$9*B3

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

網友在 Excel 的工作表中要處理欄列交叉的查詢,並且在幣別欄位使用下拉式清單來操作,該如何處理?

Excel-雙條件的交叉查詢(MATCH,VLOOKUP,INDEX,OFFSET)

如果要在『幣別』欄位使用下拉式清單來選取,則可以在「資料驗證」中的準則設定儲存格內允許:清單,來源:=$B$1:$F$1。

Excel-雙條件的交叉查詢(MATCH,VLOOKUP,INDEX,OFFSET)


【公式設計與解析】

(1) 使用 VLOOKUP 函數,儲存格J2:

=VLOOKUP(H2,$A$2:$F$20,MATCH(I2,$B$1:$F$1,0)+1,FALSE)


(2) 使用 INDEX 函數,儲存格J2:

=INDEX($B$2:$F$20,MATCH(H2,$A$2:$A$20,0),MATCH(I2,$B$1:$F$1,0))


(3) 使用 OFFSET 函數,儲存格J2:

=OFFSET($A$1,MATCH(H2,$A$2:$A$20,0),MATCH(I2,$B$1:$F$1,0),1,1)


【補充資料】

關於 MATCH,VLOOKUP,INDEX,OFFSET 函數的相關說明,可參考:

(1) http://isvincent.pixnet.net/blog/post/44853646

(2) http://isvincent.pixnet.net/blog/post/45578017

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

網友問到:如下圖,在 Word 中如何將這些標題的寬度都設為一樣(6個字)?

Word-將不同字數的標題設定成相同的寬度

觀察上圖,其中最多字者為5個字(『緊急聯絡人』),所以先選取『關係』,再選取[常用/段落]功能表中的「最適文字大小」選項。

Word-將不同字數的標題設定成相同的寬度

在[最適文字大小]對話框中顯示,目前文字寬度為2字元,所以要在[新文字寬度]文字方塊中調整為『5字元』。

使用相同方法,操作另外兩個標題。結果即可讓這四個標題的文字寬度設定成相同了。

Word-將不同字數的標題設定成相同的寬度

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

在 Word 文件中,如果想要編輯一份讓他人填寫的文件,並且能控制被填入的內容在所限制的範圍內,並且以動態方式呈現。該如何處理?參考下圖的文件,我們可以使用[開發人員/控制項]功能表的各種「控制項」來協助。

Word-使用控制項來限制輸入的內容

如下圖,本例會使用到四個內容控制項:純文字、核取方塊、下拉式清單、日期選擇器。

Word-使用控制項來限制輸入的內容


1. 姓名:使用「純文字」內容控制項

首先在姓名之後插入一個「純文字」內容控制項,接著點選這個控制項後,按一下「屬性」。

Word-使用控制項來限制輸入的內容

在[內容控制項屬性]對話框中,在標題和標籤文字方塊中輸入姓名。

接著,勾選「利用樣式為您在空白控制項輸入的文字設定格式」選項,然後選取一個樣式(在此之前,已先設定了「填寫」樣式。)。當使用者輸入文字後,會自動套用所選的樣式。

Word-使用控制項來限制輸入的內容


2.性別:使用「核取方塊」內容控制項

在性別之後分別插入二個「核取方塊」內容控制項,接著點選這個控制項後,按一下「屬性」。

Word-使用控制項來限制輸入的內容

在[內容控制項屬性]對話框中的「核取方塊屬性」區,由於預設的『打叉』符號較不適合,想要改成『打勾』符號。按一下已核取符號的[變更]按鈕。

Word-使用控制項來限制輸入的內容

你可設定挑選字型:Arial Unicode MS,並找到核取的符號。如下圖,Unicode代碼為十六進制的2611,名稱為Ballot Box With Check。

Word-使用控制項來限制輸入的內容

Word-使用控制項來限制輸入的內容


3.職稱:使用「下拉式清單」內容控制項

本例想要在擔任職務項目以下拉式清單來挑選『行政、導師、專任』其中之一。

在職稱之後插入一個「下拉式清單」內容控制項,接著點選這個控制項後,按一下「屬性」。

Word-使用控制項來限制輸入的內容

按一下[新增]按鈕,輸入顯示名稱和數值的內容,重覆三次設定:行政、導師、專任。

image


4.生日:使用「日期選擇器」內容控制項

本例的生日要以選擇日期方式,並且顯示中華民國曆表示法,例如:105年01月01日。

在生日之後插入一個「日期選擇器」內容控制項,接著點選這個控制項後,按一下「屬性」。

image

在[內容控制項屬性]對話框中的[月曆類型]下拉式清單中選取『中華民國曆』,挑選範例『105年6月20日』,然後將日期的格式修改為『e年MM月dd日』。(其中的e為顯示民國年,MM可以讓月份顯示2碼,dd可以讓日數顯示2碼。)

Word-使用控制項來限制輸入的內容與格式

如果你想要整份文件中,只有這幾個控制項可以讓使用者操作,其餘位置和內容都要限制使用者編輯,該如何處理?

你可以在[開發人員/保護]功能表中選取「限制編輯」選項,開啟[限制編輯]功能窗格。

勾選「文件中僅允許此類型的編輯方式」,然後選取『填寫表單』。

再按一下「是,開始強制保護」按鈕,輸入二次保護密碼。

Word-使用控制項來限制輸入的內容與格式

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

在 Excel 的文件中,如果要依對表格內容排序,當你指定某個欄位作為主要排序,若遇到相同內容時,會再指定第二、三、四、...個欄位來做次要的排序。如果取用下圖的資料表來排序:

Word-執行三個以上的排序欄位來排序

如果要指定排序順序為總分、國文、英文、數學來排序,則依下圖來設定。

Word-執行三個以上的排序欄位來排序

結果如下:

Word-執行三個以上的排序欄位來排序

如果相同的資料表在 Word 文件中,如何執行排序呢?

你先點選表格中的任意位置,再選取[版面配置/資料]功能表中的「資料/排序」按鈕。

Word-執行三個以上的排序欄位來排序

在開啟的對話框中,你會發現其只允許三個排序條件的設定,但是我要做的是四個條件的排序設定,該如何處理?我們必須要分二階段來處理。

第一階段:

先在[排序]對話框中,在[第一階]中選取「數學」,類型:數字,設定「遞減」排序。

Word-執行三個以上的排序欄位來排序

結果如下:

Word-執行三個以上的排序欄位來排序

第二階段:

以對「數學」排序後的結果,執行以下操作:

在[排序]對話框中,在[第一階]中選取「總分」,類型:數字,設定「遞減」排序。

在[排序]對話框中,在[第二階]中選取「國文」,類型:數字,設定「遞減」排序。

在[排序]對話框中,在[第三階]中選取「英文」,類型:數字,設定「遞減」排序。

Word-執行三個以上的排序欄位來排序

結果如下:

Word-執行三個以上的排序欄位來排序

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

在 Word 文件中,如果因為需求而將某些文字以『文字醒目提示色彩』標示,如何能快速尋找到這些文字,並且執行統一的操作?

例如,在下圖中有四個位置的文字已被設定文字醒目提示,如何一次將其全數取消設定?

Word-如何取消所有的醒目提示設定

你可以在[尋找及取代]對話框中的[尋找]標籤下,點選尋找目標文字方塊,再按一下[格式]按鈕,再選取「醒目提示」選項。

Word-如何取消所有的醒目提示設定

然後點選[尋找]按鈕的下拉式清單,再選取[主文件],Word 會顯示四個位置被找到。此時這四個醒目提示文字是在被選取的狀態下的。(如果你按一下 Delete 鍵,則這四個醒目提示文字會同時被刪除掉。)

Word-如何取消所有的醒目提示設定

如果,你想要同時取消這四個位置的醒目提示設定,則可以切換至[取代]標籤下。在[取代為]文字方塊中,點選[格式]按鈕,則選取[醒目提示]選項。(此時出現『醒目提示』)

Word-如何取消所有的醒目提示設定

再一次點選[格式]按鈕,則選取[醒目提示]選項。(此時出現『非醒目提示』)

Word-如何取消所有的醒目提示設定

當你按下「全部取代」按鈕,所有的醒目提示設定全被消除,此時原先的醒目提示文字仍是都被選取的。

Word-如何取消所有的醒目提示設定

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

當你在編輯文件時,有時候會不小心改變了好不容易設定好的格式,或是不小心刪除了某些資料,所以會希望在文件中限制某些位置的編輯。在

Excel 中,你會先選取某些儲存格,然後在[儲存格格式]對話框中的[保護]標籤下,取消勾選「鎖定」。

Word-如何限制文件中僅某些位置可以輸入資料

然後,啟用保護工作表:

Word-如何限制文件中僅某些位置可以輸入資料

接著,你就可以在未鎖定的儲存格輸入資料,並且在刪除某些被鎖定的儲存格時,會顯示被保護的訊息。

Word-如何限制文件中僅某些位置可以輸入資料

以上是在 Excel 中的操作,但是,如果你在 Word 文件中想要達到相同的文效果,該如何處理?

例如:在下圖中僅想要『考績』欄位的內容可輸入資料,其他位置都要被限制編輯,要如何操作?

Word-如何限制文件中僅某些位置可以輸入資料


【參考步驟】

1. 選取要限制輸入的儲存格。

2. 選取[校閱/保護]功能表中的「保護/限制編輯」選項,顯示[限制編輯]功能窗格。

Word-如何限制文件中僅某些位置可以輸入資料

3. 在[限制編輯]功能窗格中設定:

(1) 勾選「文件中僅允許此類型的編輯方式」,不允許修改(唯讀)。

(2) 勾選「每個人」。

4. 按一下「是,開始強制保護」。

Word-如何限制文件中僅某些位置可以輸入資料

5. 輸入二次相同的保護密碼。

Word-如何限制文件中僅某些位置可以輸入資料

觀察下圖的輸入狀態:

Word-如何限制文件中僅某些位置可以輸入資料

例如你選取一個區塊後,按一下 Delete 鍵,你將看不到任何改變,而且狀態列會出現:無法修改,因為選取範圍已被鎖定。

Word-如何限制文件中僅某些位置可以輸入資料

如果你不想要開放這些被限制的項目,則在[限制編輯]功能窗格中按一下「停止保護」按鈕,再輸入原先的保護密碼,即可解除限制。

Word-如何限制文件中僅某些位置可以輸入資料

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

在學校中製作報表時,基於學生個資保護的觀念,常會需要將學生的姓名中間一個字挖空,在 Excel 和 Word 中應該如何操作?本篇以名字為三個字為例。

1. 在 Excel 中操作

在 Excel 的環境中,透過公式可以轉換姓名將第二個字以『○』取代。例如:

儲存格E2:=LEFT(B2,1)&"○"&RIGHT(B2,1)

複製儲存格E2,貼至儲存格E2:E21。

以 LEFT 函數取出第1個字,以 RIGHT 函數取出第3個字。

(另例)儲存格E2:=MID(B2,1,1)&"○"&MID(B2,3,1)

Excel和Word-如何將學生姓名第二個以○取代


2. 在 Word 中操作

在 Word 中不像 Excel 使用公式來運算,而是直接以「尋找與取代」的方式來操作。

Excel和Word-如何將學生姓名第二個以○取代

1. 在[常用/編輯]功能表中,選取「取代」功能。

2. 在[尋找及取代]對話框的「取代」標籤下,先勾選「使用萬用字元」。

3. 設定如下:(範例)

尋找目標:(?)(?)(?)  『以(?)代表一個字』

取代為:\1○\3   『以\1代表第一個字,以\3代表第三個字。』

Excel和Word-如何將學生姓名第二個以○取代


【補充資料】

你還可以參考微軟提供資料:https://support.office.com/zh-tw/article/尋找及取代文字或其他項目-50b45f26-c4b8-4003-b9e4-315a3547f69c

若要搜尋已定義為萬用字元的字元,請在字元前鍵入反斜線 (\)。例如,鍵入 \? 可尋找問號。

您可以使用括號來分隔萬用字元與文字以及指出評估的順序。例如,鍵入 <(pre)*(ed)> 可尋找 "presorted" 與 "prevented"。

您可以使用 \n 萬用字元來搜尋運算式,然後以重新整理的運算式加以取代。例如,在 [尋找目標] 方塊中鍵入 (Ashton) (Chris),並且在 [取代為] 方塊中鍵入 \2 \1。Word 將會尋找 Ashton Chris,並且取代為 Chris Ashton。

尋找 鍵入 範例
任何單一字元 ? s?t 會找到 sat 和 set。
任何字元字串 * s*d 會找到 sad 和 started。
文字的開頭 < <(inter) 會找到 interesting 和 intercept,但不會找出 splintered。
文字的結尾 > (in)> 會找到 in 和 within,但不會找出 interesting。
其中一個指定字元 [ ] w[io]n 會找到 win 和 won。
此範圍內的任何單一字元 [-] [r-t]ight 會找到 right 和 sight。範圍必須依遞增順序排列。
括號內範圍之字元外的任何單一字元 [!x-z] t[!a-m]ck 會找到 tock 和 tuck,但不會找到 tack 或 tick。
前一個字元或運算式明確出現 n 次 {n} fe{2}d 會找到 feed,但不會找出 fed。
前一個字元或運算式至少出現 n 次 {n,} fe{1,}d 會找到 fed 和 feed。
前一個字元或運算式出現 n 至 m 次 {n,m} 10{1,3} 會找到 10、100 和 1000。
一個或多個前一個字元或運算式出現處 @ lo@t 會找到 lot 和 loot。

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

有網友想要在 Excel 中比對資料,並且自動標示相同者,例如:下圖中的彩券購買號和開獎號碼自動比對,並且標示出雙方有相同者,該如何處理?

Excel-比對資料並自動標示相同者(設定格式化的條件)


【公式設計與解析】

1. 選取儲存格B2:G25。

2. 選取[常用/樣式]功能表中的「設定格式化的條件/新增規則」。

3. 在[編輯格式化規則]對話框中選取規則類型為:使用公式來決定要格式化哪些儲存格。

4. 輸入格式化規則:=COUNTIF($I2:$N2,B2)=1;格式:紅色字。

利用 COUNTIF 函數判斷儲存格B2在儲存格I2:N2中是否有相同者(1個)。

Excel-比對資料並自動標示相同者(設定格式化的條件)

5. 選取儲存格H2:H25。

6. 選取[常用/樣式]功能表中的「設定格式化的條件/新增規則」。

7. 在[編輯格式化規則]對話框中選取規則類型為:使用公式來決定要格式化哪些儲存格。

8. 輸入格式化規則=$H2=$O2;格式:紅色字。

Excel-比對資料並自動標示相同者(設定格式化的條件)

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

又要接近學期末了,又有老師問到在 Excel 建立的學生平時考成績,如果只想採計某幾次來計算平均,該如何處理?

如下圖,在小考成績記錄表中有 8 次小考,如果只想採計其中 6 次(有『V』記號的欄位)來平均,要如何設計公式?

Excel-指定部分的欄位計算平均(SUMPRODUCT,COUNTIF)


【公式設計與解析】

儲存格J2:=SUMPRODUCT(B3:I3*($B$1:$I$1="V"))/COUNTIF($B$1:$I$1,"V")

(1) ($B$1:$I$1="V")

在儲存格陣列(B1:I1)中判斷內容是否為『V』,傳回 TRUE/FALSE 陣列。

(2) B3:I3*($B$1:$I$1="V")

將儲存格B3:I3乘以第(1)式傳回的 TRUE/FALSE 陣列,其中的『*』運算會將 TRUE/FALSE 陣列,轉換為 1/0 陣列。

(3) SUMPRODUCT(B3:I3*($B$1:$I$1="V"))

透過 SUMPRODUCT 函數計算『B3:I3』和『$B$1:$I$1="V"傳回值』的『乘積和』。

(4) COUNTIF($B$1:$I$1,"V")

利用 COUNTIF 函數計算在儲存格B1:I1中內容是『V』的個數。

(5) 將第(3)式除第(4)式,即可求得平均數。

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

網友想要在 Excel 工作表如下圖右中兩個表格的資料清單整併在一起(如下圖左),該如何處理?

Excel-整併兩個表格為一個表格(OFFSET,MATCH)


【公式設計與解析】

儲存格B2:=IFERROR(OFFSET($F$1,MATCH(A2,$E$2:$E$16,0),0),"")

(1) MATCH(A2,$E$2:$E$16,0)

利用 MATCH 函數找尋儲存格A2在儲存格E2:E16的位置(傳回第幾個的數值),其中參數『0』,表示要『完全符合』者才可以。

(2) OFFSET($F$1,MATCH(A2,$E$2:$E$16,0),0)

將第(1)式所傳回的數值代入 OFFSET 函數,得到以儲存格F1為起始的相對位置儲存格內容。

(3) IFERROR(OFFSET($F$1,MATCH(A2,$E$2:$E$16,0),0),"")

最後,利用 IFERROR 函數,將如果 MATCH 函數如果傳回錯誤訊息時,顯示為空字串。

相同原理:

儲存格C2:=IFERROR(OFFSET($H$1,MATCH(A2,$G$2:$G$16,0),0),"")

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

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

網友問到在 Excel 中,如何根據儲存格內容而執行不同的公式運算?例如:(參考下圖)

儲存格內容:11,執行公式:=B2+2。

儲存格內容:22,執行公式:=B2-2。

儲存格內容:33,執行公式:=B2*2。

儲存格內容:44,執行公式:=B2/2。

儲存格內容:55,執行公式:=B2^2。

Excel-根據儲存格內容執行不同公式(CHOOSE,VLOOKUP)


【公式設計與解析】

儲存格C2:=IFERROR(CHOOSE(VLOOKUP(A2,{11,1;22,2;33,3;44,4;55,5},2,
FALSE),B2+2,B2-2,B2*2,B2/2,B2^2),"")

(1) VLOOKUP(A2,{11,1;22,2;33,3;44,4;55,5},2,FALSE)

利用常數陣列:{11,1;22,2;33,3;44,4;55,5},透過 VLOOKUP 函數查詢儲存格A2內容在陣列中的位置,並傳回第2欄對應的內容。其結果可能傳回 1, 2, 3, 4, 5 和錯誤訊息。

(2) CHOOSE(第(1)式,B2+2,B2-2,B2*2,B2/2,B2^2)

CHOOSE 函數中,根據第(1)式傳回的『1, 2, 3, 4, 5』,對照選取公式『B2+2, B2-2, B2*2, B2/2, B2^2』。

(3) IFERROR(第(2)式,"")

因為 VLOOKUP 函數如果傳回錯誤訊息時,利用 IFFERROR 將錯誤訊息轉換為空字串。

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

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼