贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201702 (26)

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

在 Word、Excel 和 PowerPoint 中常會使用圖片和文字,以下列舉數個可能會被忽略的小地方。

(1) 圖片右側的「錨點」符號

當你選取圖片後,如果看到圖片左側有「錨點」符號,表示該圖片已經設定了「文繞圖」功能或是設定了圖片固定於頁面上位置等設定。

Word-關於圖片的5個小技巧


(2) 利用滑鼠右鍵拖曳來旋轉圖片

通常,你會使用滑鼠左鍵拖曳的方式來讓圖片旋轉,但是旋轉過程中無法與原圖比較。如果你想和原圖加以比較,可以使用「滑鼠右鍵」拖曳來旋轉圖片。你就可以看到兩個重疊的前後圖片比較。

Word-關於圖片的5個小技巧


(3) 平面圖片也可以3D旋轉

當你將圖片效果套用「立體旋轉」,可以將圖片設定平行、透視、傾斜等特效。如果你要微調其中的效果,可以開啟圖片格式設定窗格,在「立體旋轉」區中設定。

Word-關於圖片的5個小技巧

你可以調整:X軸旋轉、Y軸旋轉、Z軸旋轉、透視圖深度、與地面的距離等項目。如果你使用微調按鈕,其會以 5, 10 , 15, ... 等間距來微調,若要更細微的調整,可以直接在文字方塊中輸入想要的數值或是使用向上/向上方向鍵,即可以細微的微調。

Word-關於圖片的5個小技巧


(4) 文字效果

對於文部分,我看到絶大多數的同事都是直接套用 Word 提供的文字效果。少數還不錯,還會進一步套用:大綱、陰影、反射、光暈等特效。

Word-關於圖片的5個小技巧

不過,如果你能開啟[文字效果格式]窗格,自己再進一步微調,就可以創造出不同於他人的效果。就我了解,有的人在操作時,就是因為效果格式的項目太多,而不想使用它。所以才會只套用系統預設的樣式而已。

Word-關於圖片的5個小技巧


(5) 文字藝術師

將文字套用「文字藝術師」,除了前項那些的文字效果之外,還可以設定文字變形效果。

image

套用文字藝術師時,會將文字置入一個圖文框中,所以可以將此圖文框執行所有的「圖案效果」,如果選取文字,可以執行所有的「文字效果」。但是,請注意,其套用結果有所不同。

例如,將圖文框(下圖左)和文字(下圖右)設定圖案效果:陰影/外陰影/下方位移。

(設定文字的效果時,會將所前的文字效果清除。)

image image

文章標籤

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

今天一時興起,趁著假期取得本網站(http://isvincent.pixnet.net)的網頁流量來分析一下,想要看看最近五年(超過1800筆數據)中,各星期幾和各月份的相關數據。

Excel-根據網站瀏覽計算每週各天和各月的平均值並繪統計圖(SUMPRODUCT)

一、分析各週每天和各月份的平均值

Excel-根據網站瀏覽計算每週各天和各月的平均值並繪統計圖(SUMPRODUCT)

【公式設計與解析】

先選取所有基本資料的儲存格範圍,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、星期、瀏覽量。

1. 求各週每天的平均數

第(1)式:=SUMPRODUCT((WEEKDAY(日期,2)=ROW(1:1))*瀏覽量)

第(2)式:=SUMPRODUCT((WEEKDAY(日期,2)=ROW(1:1))*1)

儲存格F2:=第(1)式/第(2)式

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

2. 求各月的平均數

第(1)式:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*瀏覽量)

第(2)式:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*1)

儲存格F11:=第(1)式/第(2)式

複製儲存格F11,貼至儲存格F11:F22。


二、製作每週各天和各月份平均值的統計圖

將計算所得數據產生二個直條圖,發現有趣的現象:

(1) 星期二~星期五每天的流量都差不多;星期六、日較少(約平常的一半)。

(2) 星期一也是上班(課)日,但為何流量會較少?

(3) 1月至12月的曲線呈現笑口的弧形,有特別的意義嗎?

(4) 一年中間月份的流量明顯較少?是因為學生放暑假嗎?

Excel-根據網站瀏覽計算每週各天和各月的平均值並繪統計圖(SUMPRODUCT)

文章標籤

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

網友根據這篇:Excel-檢驗多個儲存格是否每一個都是空白(COUNTIF,LEN,ISBLANK)

想要進一步設定只判斷指定儲存格是否否都是空白,該如何處理?

如下圖,若只想取用儲存格B1、儲存格C1、儲存格E1、儲存格G1來判斷是否其中任一個儲存格有內容。

Excel-檢驗指定儲存格是否每一個都是空白(COUNTIF,LEN,陣列公式)


【公式設計與解析】

儲存格A1:G1中,是否要列入判斷,以陣列{0,1,1,0,1,0,1} 來表示,其中 0 表示不列入,1 表示要列入。

儲存格D4:{=IF(SUM(LEN(A1:G1)*{0,1,1,0,1,0,1}),"Y","N")}

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

LEN(A1:G1):利用 LEN 函數,在陣列公式中計算每個儲存格內容的字元數。

LEN(A1:G1)*{0,1,1,0,1,0,1}:將每個儲存格內容的字元數乘以{0,1,1,0,1,0,1},在此的『*』運算,相當於執行邏輯 AND 運算。邏輯 AND 運算真值表如下:

Excel-檢驗指定儲存格是否每一個都是空白(COUNTIF,LEN,陣列公式)

LEN(A1:G1)*{0,1,1,0,1,0,1}:最後只會留下儲存格B1、儲存格C1、儲存格E1、儲存格G1的運算數值

SUM(LEN(A1:G1)*{0,1,1,0,1,0,1}):在陣列公式中,將儲存格B1、儲存格C1、儲存格E1、儲存格G1中內容的字元數予以加總。

在 IF 函數中,若 SUM(LEN(A1:G1)*{0,1,1,0,1,0,1}) 傳回的數大於 0,則表示至少有一個儲存格含有字元。若傳回的數等於 0,則表示沒有任一個儲存格含有字元。

文章標籤

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

根據前一篇文章:Excel-儲存格內容合併與分離(CHAR(10),FIND,MID)

如果儲存格內容是每個儲存格不相同,如何來設計公式?(參考下圖)

Excel-儲存格內容合併與分離(CHAR(10),FIND,MID)2


(1) 分離第一列字串

儲存格E2:=LEFT(C2,FIND(CHAR(10),C2)-1)

FIND(CHAR(10):找出第一個 CHAR(10) 的位置。

利用 LEFT 函數取出第一個 CHAR(10) 的位置左邊的字串。


(2) 分離第三列字串

儲存格E4:=RIGHT(C2,LEN(C2)-FIND(CHAR(10),C2,LEN(E2)+2))

FIND(CHAR(10),C2,LEN(E2)+2):找出第二個 CHAR(10) 的位置

利用 RIGHT 函數取出第二個 CHAR(10) 的位置右邊的字串。


(3) 分離第二列字串

儲存格E3:=MID(C2,FIND(CHAR(10),C2)+1,FIND(CHAR(10),C2,LEN(E2)+2)-
FIND(CHAR(10),C2))

藉由第(1)式和第(2)式中取得第一個和第二個CHAR(10)的位置,再由 MID 函數取出其中的字串。

文章標籤

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

網友問到:在 Excel 中如何Excel-檢驗多個儲存格是否每一個都是空白?(參考下圖)

通常使用 COUNTIF 函數即可得到結果:

儲存格D4:=IF(COUNTIF(A1:G1,"")=7,"N","Y")

本例中要練習陣列公式,以下提供二種公式來練習。

Excel-檢驗多個儲存格是否每一個都是空白(COUNTIF,LEN,ISBLANK)


【公式設計與解析】

(1) A1~G1是否含有任一個字元?

儲存格D4:{=IF(SUM(LEN(A1:G1)),"Y","N")}

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

LEN(A1:G1):利用 LEN 函數,在陣列公式中計算每個儲存格內容的字元數。

SUM(LEN(A1:G1)):在陣列公式中,將儲存格A1:G1中內容的字元數予以加總。

在 IF 函數中,若 SUM(LEN(A1:G1)) 傳回的數大於 0,則表示至少有一個儲存格含有字元。若傳回的數等於 0,則表示沒有任一個儲存格含有字元。


(2) A9~G9是否含有任一個字元? 

{=IF(SUM(1*ISBLANK(A9:G9))<>7,"Y","N")}

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

ISBLANK(A9:G9):利用 ISBLANK 函數,在陣列公式中判斷儲存格內容是否為空白。傳回 TRUE/FALSE 陣列。

1*ISBLANK(A9:G9)):利用『1*』運算,將 TRUE/FALSE 陣列轉換為 1/0 陣列。

SUM(1*ISBLANK(A9:G9))<>7:在 IF 函數中,判斷傳回值是否不等於 7(儲存格A9:G9共有7個),若結果為 TRUE,表示至少有一個儲存格含有字元,若結果為 FALSE,表示 7 個儲存格中沒有任何一個儲存格含有字元。

文章標籤

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

同事問到:如何在Word、Excel、PowerPoint中將一個矩形的照片,轉變成正圓形(非橢圓)?例如下圖左的矩形照片轉換成下圖右的圓形照片。

的確,這種效果是在文件或簡報中常會被使用到。但是下圖右上的正圓,其中的照片沒有變形;但是,下圖右下的正圓,其中的照片已經變形。原因是因為將橢圓形硬拖曳成正圓形,所導致的照片變形。

Word-將矩形照片改成正圓形,照片內容不變形。

如何做一個照片不會變形的正圓?參考以下的步驟來練習。

1. 選取矩形照片。

2. 選取[格式/裁剪]中的「裁剪成圓形」選項,再選取「橢圓」。(因為沒有「正圓」)

Word-將矩形照片改成正圓形,照片內容不變形。 

3. 選取[格式/裁剪]中的「長寬比」選項,再選取「方形 1:1」。

image

對橢圓而言,長寬1:1即為正圓形。

image

仿此,如果你將照片套用圖片樣式(例如:金屬橢圓形樣式):

image

也可以使用相同方法,將結果顯示為正圓形:

image

文章標籤

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

(回答學校同事提問)開學了!一學期又重頭開始了。當然一學期要做的事也陸續展開了。.

學校訂定的行事曆和全校師生息息相關,如果能將學校行事曆轉換至 Google 日曆和自己的日曆加以整合,就不會遺漏學校活動。如何將學校行事曆轉換至 Google 日曆?

通常學校的行事曆沒有依照什麼標準來呈現,但至少會有日期、處室組別、工作事項等,並且以表格方式呈現。

Excel-將學校行事曆轉換至Google日曆

而 Google 日曆則有提供可以使用 Excel 檔匯入的方式,相當方便而且實用。你可以先請參考 Google 的說明網頁:https://support.google.com/calendar/answer/37118?hl=zh-Hant

Google 日曆接受 Excel 的 CSV 格式,其檔案內容可以使用的欄位名稱如下所示:

(1)Subject:活動名稱。範例:期末考

(2)Start Date:活動的第一天。範例:05/30/2020

(3)Start Time:活動開始時間。範例:10:00 AM

(4)End Date:活動的最後一天。範例:05/30/2020

(4)End Time:活動結束時間。範例:1:00 PM

(5)All Day Event:活動是否為全天。若是,輸入 True;若否輸入 False。範例:False

(6)Description:活動說明或附註。範例:50 題選擇題和 2 題申論題

(7)Location:活動地點。範例:"台灣大學,共同教室 201"

(8)Private:活動是否為私人活動。若是,輸入 True;若否輸入 False。範例:True

(註:如果任何活動詳細資料中含有逗號,必須在文字的左右兩端加上引號。)

特別注意:標頭必須是英文,以上(1)(2)項標頭是必要項目,其餘項目為選項。例如:

Subject
Start DateStart Time
End Date
End Time
All Day Event
Description
Location
Private
         
                 
                 
                 
                 
                 
                 
                 
                 


參考以下的步驟來操作:

1. 首先你要取得學校的行事曆表格,依照上述的欄位名稱加以配置。

(本例使用三個欄位,其中有二個必要欄位。)

2. 將檔案另存新檔為CSV格式。(參考下圖)

Excel-將學校行事曆轉換至Google日曆

3. 進入你的 Google 日曆。

4. 在我的日曆處選取「建立新日曆」。

Excel-將學校行事曆轉換至Google日曆

5. 為新日曆命名,並且建立日曆。

Excel-將學校行事曆轉換至Google日曆

6. 在我的日曆處點選「設定」。

Excel-將學校行事曆轉換至Google日曆

7. 選取「滙入日曆」。

Excel-將學校行事曆轉換至Google日曆

8. 選取先前建立的CSV檔。

9. 選取要將檔案中的行事曆匯入那個日曆中。按一下「匯入」按鈕。

Excel-將學校行事曆轉換至Google日曆

大功告成,所有行事曆都已匯入。

Excel-將學校行事曆轉換至Google日曆

註:本例中的活動因為沒有設定時間,所以皆設為整日活動。

文章標籤

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

網友問到:在 Excel 中如何快速產生如下圖的字元排列組合?

在下圖中,是由「忠孝仁愛信義和平」字組所排列而成,每一欄/每一列都會移位一個儲存格。

Excel-依字串產生固定順序排列的字元(INT,MID)

【公式設計與解析】

儲存格A2:

=MID("忠孝仁愛信義和平",MOD(MOD(ROW(1:1)-1,8)+COLUMN(A:A)-1,8)+1,1)

複製儲存格A2,貼至儲存格A2:H25。

其中的參數 8,是因為字串「忠孝仁愛信義和平」共有 8 個字。

(1) MOD(ROW(1:1)-1,8)

該公式在各個儲存格中的結果如下圖:

Excel-依字串產生固定順序排列的字元(INT,MID)

(2) MOD(ROW(1:1)-1,8)+COLUMN(A:A)-1

該公式在各個儲存格中的結果如下圖:

Excel-依字串產生固定順序排列的字元(INT,MID)

(3) MOD(MOD(ROW(1:1)-1,8)+COLUMN(A:A)-1,8)+1

該公式在各個儲存格中的結果如下圖:

Excel-依字串產生固定順序排列的字元(INT,MID)

(4) MID("忠孝仁愛信義和平",第(3)式,1)

根據第(3)式的傳回值,在字串「忠孝仁愛信義和平」中取出對應的 1 個字元。

文章標籤

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

網友提問:下圖是 Excel 中的資料表,如何將下圖(上)的資料轉換為下圖(下)的呈現方式?

該資料表是由 3 組數值合成一列,每組數值有 6 個,共有 3 列的資料:

Excel-多欄轉少欄且少列轉多列(INT,MOD,OFFSET)


【公式設計與解析】

儲存格A7:=OFFSET($A$1,INT((ROW(1:1)-1)/3),MOD(INT(ROW(1:1)-1)*6+
COLUMN(A:A)-1,18))

複製儲存格A7,貼至儲存格F15。

(1) INT((ROW(1:1)-1)/3)

產生三組 0,0,0,0,0,0→產生三組 1,1,1,1,1→產生三組 2,2,2,2,2 。

(2) MOD(INT(ROW(1:1)-1)*6+COLUMN(A:A)-1,18)

產生三組 0,1,2,3, ..., 17。

第(1)式和第(2)式,如果向右和向下複製公式,將可產生以下的數列。每個儲存格由「第(1)式,第(2)式」組成。

Excel-多欄轉少欄且少列轉多列(INT,MOD,OFFSET)

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

利用第(1)式和第(2)式的傳回結果代入 OFFSET 函數,可以取得對應的儲存格內容。

文章標籤

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

網友問到 Excel 的問題:如何在含有日期清單的資料中,列出指定日期區間內的資料?

如下圖左,有一個「日期、項目、數值」組成的資料清單,想要找出指定的兩個日期之間的資料(參考下圖右)。以下提供三種方式來練習。

Excel-列出日期區間內的資料(OFFSET,SMALL,ROW,陣列公式)


1. 手動:使用篩選工具

(1) 啟用自動篩選,篩選「日期」欄位。

(2) 選取[日期篩選/自訂篩選]選項。

Excel-列出日期區間內的資料(OFFSET,SMALL,ROW,陣列公式)

(3) 設定「之後」的日期為起始日期-1;設定「之前」的日期為終止日期+1。

image

最後結果即為該日期區間的資料。

image


2. 自動:設計公式

先選『日期』欄位中有資料的所有儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。

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

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

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

(1) IF((日期>=$E$2)*(日期<=$E$4),ROW(日期),"")

(日期>=$E$2)*(日期<=$E$4):在 IF 函數中使用雙條件「日期>=$E$2 和 日期<=$E$4」,其中『*』運算子相當於執行邏輯 AND 運算。若雙條件均成立時,傳回對應的日期列號陣列。

(2) SMALL(第(1)式,ROW(1:1))

利用 SMALL 函數找出符合條件的日期所對應由小到大的列號。其中公式向下複製時,會產生 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

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

利用第(2)式傳回的列號,代入 OFFSET 函數找出對應的儲存格。

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

若第(3)式傳錯誤訊息時,利用 IFERROR 函數改以空白顯示。

Excel-列出日期區間內的資料(OFFSET,SMALL,ROW,陣列公式)

換個方式寫公式:

儲存格F2:{=IFERROR(OFFSET(A$1,LARGE((日期>=$E$2)*(日期<=$E$4)*
ROW(日期),ROW(1:1))-1,0),"")}

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

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

其結果會和上式的結果順序相反。

Excel-列出日期區間內的資料(OFFSET,SMALL,ROW,陣列公式)

文章標籤

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

網友問到:每個箱號有10個序號,如何自動產生一組編號?

參考下圖,在 Excel 中可以輕鬆的藉由公式產生相同邏輯的連續號碼。

Excel-產生連續2碼的號碼(INT,MOD,TEXT)


【公式設計與解析】

(1) 箱號

儲存格A2:=TEXT(INT((ROW(1:1)-1)/10)+1,"00")

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

當公式向下複製時,ROW(1:1)會產生ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

INT((ROW(1:1)-1)/10)+1:會產生1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,10,1,…。

再透過 TEXT 函數給予參數『00』,可以讓數字以二位數呈現,1~9會在左側補 0。

(2) 序號

儲存格B2:=TEXT(MOD(ROW(1:1)-1,10)+1,"00")

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

MOD(ROW(1:1)-1,10)+1:會產生1,1,1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,2,2,3,…。

(3) 編號

儲存格C2:

=TEXT(INT((ROW(1:1)-1)/10)+1,"00")&TEXT(MOD(ROW(1:1)-1,10)+1,"00")

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

文章標籤

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

網友問到在 Excel 中的問題:如何將每個 25 日後的日期算為下個月?參考下圖,3/30要傳回 4 月,而 12/27 要傳回 1 月。

Excel-每月25日後算為下個月(MONTH,DAY)


【公式設計與解析】

儲存格C2:

=MONTH(A2)+(DAY(A2)>25)*1-((MONTH(A2)+(DAY(A2)>25)*1)>12)*12

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

(1) (DAY(A2)>25)*1

利用 DAY 函數取出儲存格A2中日期的日數,利用條件 DAY(A2)>25 判斷日數是否大於25,傳回 TRUE/FALSE 。經由『*1』運算,則 TRUE/FALSE 會轉換為 1/0

(2) MONTH(A2)+(DAY(A2)>25)*1

利用 MONTH 函數取出儲存格A2中日期的月數,再加上判斷日數是否超過 25,若是,則+1;若否,則+0。

(3) ((MONTH(A2)+(DAY(A2)>25)*1)>12)*12

由於 12 月 25 日後應顯示為 1 月,所以判斷 MONTH(A2)+(DAY(A2)>25)*1 是否大於 12,再乘以 12 後,會傳回 12 或是 0。公式中的「-((MONTH(A2)+(DAY(A2)>25)*1)>12)*12」乃將第(2)式的傳回值 13 再減 12。

原公式也可以改寫為,儲存格C2:

(1) =MONTH(A2)+(DAY(A2)>25)*1-(MONTH(A2)=12)*(DAY(A2)>25)*12

(2) =MONTH(A2)+(DAY(A2)>25)*(1-(MONTH(A2)=12)*12)

文章標籤

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

在 Excel 中有一個數值清單,其中的數值有重覆的清況(如下圖),如何在其中找出指定第N大的數值?

在下圖中,最大值是 15,有 3 個,接下來是 14,有 1 個,接下來跳至 12,有 2 個。通常我們會使用 LARGE 或是 SMALL 來找第 N大或第 N 小的數值,但是這兩個函數均不會忽略重覆的數。該如何解決?

Excel-在含有重覆的數字清單中找尋第N大的數(LARGE,FREQUENCY,陣列公式)


【公式設計與解析】

儲存格C2:=IFERROR(LARGE(IF(FREQUENCY($A$2:$A$25,$A$2:$A$25),
$A$2:$A$25),ROW(1:1)),0)

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

(1) FREQUENCY($A$2:$A$25,$A$2:$A$25)

FREQUENCY  函數用以計算一個值範圍內的某些數值出現的次數,並傳回一個數值陣列。

語法:FREQUENCY(data_array, bins_array)

  • Data_array:要計算頻率的一組數值的陣列或參照。

  • Bins_array:是區間的陣列或參照,用以將 data_array 中的值分組。

  • (2) IF(FREQUENCY($A$2:$A$25,$A$2:$A$25),$A$2:$A$25)

這個公式只是要讓出現次數對應原數值陣列而已。

(3) LARGE(第(2)式,ROW(1:1))

利用 LARGE 取出第 N 個,ROW(1:1)向下複製公式時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

(4) IFERROR(第(3)式,0)

將第(3)式傳回值若有錯誤訊息時,改顯示為 0。


【特別說明】

若儲存格C2改為陣列公式:{=IFERROR(LARGE(IF(FREQUENCY($A$2:$A$25,
$A$2:$A$25),$A$2:$A$25),ROW(1:1)),0)}

其結果也是相同。FREQUENCY 函數本身即以陣列形式運算。

文章標籤

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

在 Excel 中有一個資料表(如下圖左),想要根據『座號』來取得姓名、成績和名次的資料,該如何處理?(如下圖右)

通常你會使用 VLOOKUP 函數或 HLOOKUP 函數來執行查詢工作,在本例中先觀察這個資料表,是由姓名、座號、成績、名次等四個欄位所組成,若要使用 VLOOKUP 函數來查詢,當取用資料在A欄~D欄時,因為A欄是姓名而非座號,所以無法使用。若取資料B欄~D欄,可以順利用座號查詢,但是無法查詢姓名,因為姓名欄位不在資料範圍。而且查詢結果的顯示順序和原資料的排列順序並不相同。

如何以一個公式便能完成多個儲存格的查詢?還有其他方式可以達到查詢結果,來練習看看。

取代VLOOKUP函數查詢(INDIRECT,INDEX,MATCH)


【公式設計與解析】

(1) 定義名稱

若要讓公式精簡易讀,可以藉助定義儲存格範圍的名稱。選取A欄~D欄中有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、座號、成績、名次。


(2) 使用 INDEX 函數+MATCH 函數

儲存格F4:=INDEX(INDIRECT(F3),MATCH($G$1,座號,0))

INDIRECT(F3):利用 INDIRECT 函數將儲存格F3的內容(例如:姓名),轉換為儲存格範圍(已定義名稱:姓名)。

MATCH($G$1,座號,0):利用 MATCH 函數取得儲存格G1的內容位於座號陣列中的那一個位置(傳回列號)。

最後,透過 INDEX 函數以查表方式取得座號對應的其他欄位值。

複製儲存格F4,貼至儲存格F4:H4。

文章標籤

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

有時你會在網頁上看到資料表,當你要將網頁上的資料放至 Excel 活頁簿中,除了複製/貼上之外,你還有別的選擇。

你也可以直接將網頁儲存為 HTML 格式(*.htm 或 *.html),再由 Excel 中開啟。例如:以下的網頁中有一個表格,先將該網頁存檔。

Excel-將網頁資料在Excel中開啟和將工作表儲存為網頁格式

存檔後,你會得到一個檔案(*.html)和一個同名資料夾(*_files):

Excel-將網頁資料在Excel中開啟和將工作表儲存為網頁格式

點入該資料夾,可以看到檔案內容:

Excel-將網頁資料在Excel中開啟和將工作表儲存為網頁格式

在 Excel 中如何使用這個你想要的資料?在 Excel 中進入[開啟舊檔]對話框,先切換檔案格式為「所有檔案(*.*)」,再選取你所儲存格的 *.html 檔。

Excel-將網頁資料在Excel中開啟和將工作表儲存為網頁格式

本例中先手動將少數干擾的物件移除,即可使用這個網頁上的資料了。

Excel-將網頁資料在Excel中開啟和將工作表儲存為網頁格式

反之,如果你將已做好的 Excel 表格要儲存成網頁檔,也是可以的。

將你做好的 Excel 檔另存新檔時,指定檔案格式為「網頁(*.htm,*.html)」。

Excel-將網頁資料在Excel中開啟和將工作表儲存為網頁格式

Excel 中的資料即可在網頁瀏覽器中顯示:

Excel-將網頁資料在Excel中開啟和將工作表儲存為網頁格式

有一點倒是要特別提醒的:

如果你在 Excel 中開啟一個 HTML 檔,當你持續編輯後,若沒有另存為 .xlsx 格式,則當你儲存時,只會以 htm 格式儲存。所以,可能有部分的 Excel 內容,會因此而消失或是格式改變。

Excel-將網頁資料在Excel中開啟和將工作表儲存為網頁格式

文章標籤

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

網友想要了解如何在 Excel 中快速找出最靠近某日的星期幾日期,例如:在下圖中要找出最靠近 2016/11/19 的星期日、星期五、星期三的日期,該如何處理?

Excel-依指定日期找出最靠近的星期幾日期(WEEKDAY)


(1) 最近的星期日

儲存格C2:=A2+7-WEEKDAY(A2,2)

本例公式 WEEKDAY 函數使用參數『2』,表示要以數字 1~7 代表傳回星期一~星期日。參考下圖可以發現,指定不同參數可以得到不同的傳回值。

Excel-依指定日期找出最靠近的星期幾日期(WEEKDAY)

若 WEEKDAY(A2,2) 傳回 1,表示儲存格A2日期是星期一,7-WEEKDAY(A2,2)=6,表示還要 6 天才是下個星期日。

若 WEEKDAY(A2,2) 傳回 4,表示儲存格A2日期是星期四,7-WEEKDAY(A2,2)=3,表示還要 3 天才是下個星期日。

依此類推。

(2) 最近的星期五

儲存格E2:=A2+7-WEEKDAY(A2,16)

(3) 最近的星期三

儲存格G2:=A2+7-WEEKDAY(A2,14)

你可以試著找出最近的星期二、星期六?


【延伸學習】

Excel-找出指定日期後的特定日數之下個星期一(WEEKDAY)

文章標籤

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

同仁問到一個實用的問題:在 Excel 活頁簿中的每個工作表內容只有一頁大小,當在列印多個工作表時,希望這些工作表的頁碼能連續顯示,該如何處理?

這個操作,其實很多人常會用到。以下圖為例,這個活頁簿中有四個工作表,列印時每個工作表只佔一頁,現在要四個工作表都要列印,並且頁碼也要連續列印。

Excel-多工作表列印連續頁碼

參考作法:

1. 點選第一個工作表名稱(工作表1),按著 Shift 鍵,再點選最後一個工作表名稱(工作表4)。如此,可以選取這四個工作表。

2. 開啟[版面設定]對話框,進入[頁首/頁尾]標籤。

3. 點選「自訂頁尾」按鈕。

Excel-多工作表列印連續頁碼

4. 在頁尾上插入頁碼。

Excel-多工作表列印連續頁碼

當你預覽列印時,就可以發現這四個工作表的頁碼已是連續顯示了。

Excel-多工作表列印連續頁碼

Excel-多工作表列印連續頁碼

文章標籤

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

有同仁問到:在 Excel 中常會用到一些運算,通常在公式中使用『+、-’*、/』,但是如果是想要在表達乘法或除法時使用『×、÷』符號,該如何輸入比較快速?

Excel-如何快速輸入乘法和除法『×、÷』符號

其實,不管是不是在 Excel 中,都可能會用到乘法和除法『×、÷』符號。在此,先提供了二種輸入方式:

1. 在英數輸入法之下

在英數輸入法之下,可以按著 Alt 鍵不放,再依序按下Keypad上的 2, 4, 7 鍵。

同理,

若要輸入『×』,按著 Alt 鍵不放,再依序按下Keypad上的 2, 1, 5 鍵。

2. 在注音輸入法之下

在注音輸入法之下,先按下『‵』鍵(在按鍵1的左側)。

Excel-如何快速輸入乘法和除法『×、÷』符號

再按『/』鍵,接著按向下鍵,按2或是點選÷:

Excel-如何快速輸入乘法和除法『×、÷』符號

文章標籤

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

網友問到:在 Excel 中如何將三欄的資料轉換為表格形式呈現?如下圖,圖左中每一列的資料都沒有重覆,要將其轉換為左上和左下兩種表格呈現,該如何處理?

網友常問到 SUMPRODUCT 函數和陣列的關係,藉這個例子來比較一下。

Excel-表格轉換(比較SUMPRODUCT和SUM+陣列公式)


【公式設計與解析】

(1) 定義名稱

選取儲存格A1:C21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、季別、業績。


(2) 使用 SUMPRODUCT 函數

儲存格F2:=SUMPRODUCT((人員=$E2)*(季別=F$1)*業績)

SUMPRODUCT 函數中使用二個條件:

人員=$E2:判斷人員陣列中的儲存格是否和儲存格E2相同,傳回 TRUE/FALSE 陣列。

季別=F$1:判斷季別陣列中的儲存格是否和儲存格F1相同,傳回 TRUE/FALSE 陣列。

(人員=$E2)*(季別=F$1)中的『*』運算子,相當於執行邏輯 AND 運算。運算時,會將 TRUE/FALSE 陣列轉換為 1/0 陣列。

將上式的 1/0 陣列和業績陣列相乘,只會留下 1 對應的業績(0 和業績相乘會得到 0),也就是二個條件皆成立所對應的業績。最後由 SUMPRODUCT 函數予以加總(本例結果為:66)。

Excel-表格轉換(比較SUMPRODUCT和SUM+陣列公式)

同理:

儲存格F13:=SUMPRODUCT((人員=F$12)*(季別=$E13)*業績)


(3) 使用陣列公式

Excel-表格轉換(比較SUMPRODUCT和SUM+陣列公式)

儲存格F2:{=SUM((人員=$E2)*(季別=F$1)*業績)}

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

其分析原理和 SUMPRODUCT 函數完全相同。

由於 SUMPRODUCT 函數的參數即是以陣列,所以不需要再使用 Ctrl+Shift+Enter 鍵來讓 Excel 使用陣列運算。而 SUM 函數並非預設以陣列運算,所以要多一個操作來告訴 Excel 要使用陣列運算。當 Excel 會自動產生「{}」時,該公式即會將運算內容以陣列來運算。

同理:

儲存格F13:{=SUM((人員=F$12)*(季別=$E13)*業績)}

文章標籤

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

大年初一,一定要帶家人出外踏青!春節天氣又是大好,今年去的是「原台南水道-淨水池區」,位在台南市山上區。相關介紹請參閱台南市政府文化局網站。

資料來源:http://culture.tainan.gov.tw/historic/aqueduc_visit/index.php?m2=241

踏青好去處:原台南水道-淨水池區

地點:

設有停車場,交通還算便利。建築很有特色,環境也整理的很好。

提供幾張照片供網友參考:

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

踏青好去處:原台南水道-淨水池區

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

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼