贊助廠商

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

搜尋本部落格文章資料

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

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

有網友想要在 Excel 中,隨機產生特定一組文字/數字的其中一個,該如何處理呢?

參考下圖,我們來練習在一組數字和一組文字中,隨機產生其中的一個。

數字範例:3、6、7、10、13、15、17、19、20、25

文字範例:甲、乙、丙、丁、戊、己、庚、辛、壬、癸

【產生公式】

(1) 隨機數字

=CHOOSE(INT(RAND()*10+1),3,6,7,10,13,15,17,19,20,25)

(2) 隨機文字

=CHOOSE(INT(RAND()*10+1),"甲","乙","丙","丁","戊","己","庚","辛","壬","癸")

其中 RAND() 函數可以產生介於 0 和 1 之間的亂數值(可能為 0,但不會為 1),INT 函數則可以取一個數之不大於的最大整數。透過 CHOOSE 函數來根據亂數值,對應陣列中的其中一個。

再來試試 INDEX 函數:

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

一直有網友有這種需求:在 Excel 的資料表,想要將一欄中的資料篩選為多欄後依順序列出(參考下圖),該如何處理呢?

Excel 其實提供了多種的工具可以手動來完成這個工作,但是,因為資料會變動,所以還是以公式來處理比較好。

【準備工作】

選取儲存格A1:C26,按下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、假別、時數。

【輸入公式】

(1) 儲存格E2

{=IFERROR(OFFSET($B$1,SMALL(IF(人員="甲",ROW(人員),10^10),ROW(1:1))-1,,,),"")}

這是陣列公式,輸入完成,要按 Ctrl+Shift+Enter 鍵。

IF(人員="甲",ROW(人員),10^10):取得人員為「甲」的「列號陣列」,如果不為「甲」者,給予一個很大的數,本例為 10 的 10 次方。

SMALL(IF(人員="甲",ROW(人員),10^10),ROW(1:1)):利用上式取得的列號陣列,在 SMALL 函數中取得第 1 小值(ROW(1:1)=1)的列號,如果該公式向下複製時,即可取得第 2 小、第 3 小、…的列號。

將上式取得的列號,帶入 OFFSET 函數以取得對照的儲存格內容(即為「假別」)。

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

有網友問到:如下的 Excel 資料表,如何找出各個品項中,日期最大者對應的數量?

【準備工作】

選取儲存格A1:C50,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、品項、數量。

【輸入公式】

儲存格F2:{=OFFSET($C$2,MATCH(MAX(IF((品項=E2),日期,FALSE)),日期)-1,)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

IF((品項=E2),日期,FALSE):核對品項陣列中符合儲存格E2者,找出所對應的日期陣列。

MAX(IF((品項=E2),日期,FALSE)):找出上式日期陣列中的最大值。

MATCH(MAX(IF((品項=E2),日期,FALSE)),日期):找出日期陣列中的最大值為資料中的第幾列。

最後,透過 OFFSET 函數,找出欄C中的對應數量。

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

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

有網友問到:根據一個銷售額清單的 Excel 資料表,如何根據金額區間來計算總額?參考下圖,以每 500 為區間來計算總額。

【準備工作】

選取儲存格B2:B26,按一下 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:銷售額。

【輸入公式】

儲存格E2:

=SUMPRODUCT((銷售額>(ROW(1:1)-1)*500)*(銷售額<=ROW(1:1)*500)*銷售額)

(銷售額>(ROW(1:1)-1)*500):當往下複製儲存格時,ROW(1:1)=1 會變為 ROW(2:2)=2、ROW(3:3)=3、…。計算結果會依序產生 0、500、1000、1500、2000、…。

本例運算結果產生陣列 {True,True,True,True,True,True,True,True,True,True,True,…}。

(銷售額<=ROW(1:1)*500):當往下複製儲存格時,計算結果會依序產生 500、1000、1500、2000、2500、…。

本例運算結果產生陣列 {False,False,False,False,False,True,False,False,False,False,True,…}。

(銷售額>(ROW(1:1)-1)*500)*(銷售額<=ROW(1:1)*500):其中的「*」相當於執行 AND 運算,因此只有第 6、11、… 的結果為 True

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

網友問到:要如何統計儲存格中不同儲存格色彩的個數,其中每二個儲存格已合併,該如何處理?先請參考之前的文章:

Excel-取得儲存格的色彩(http://isvincent.pixnet.net/blog/post/34018213)

作法參考下圖:先取得色彩代表的數值,再測試資料欄位的色彩數值,進一步統計個數。

【參考步驟】

1. 定義二個名稱:

color:=GET.CELL(63,工作表1!$D2)

clolorData:=GET.CELL(63,工作表1!$A2)

image

2. 找出色彩代表的數值

儲存格E2:=color

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

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

有網友問到一個常見的 Excel 問題:如何合併兩個相同格式但不同內容的表格(參考下圖)?

建議作法:

1. 選取儲存格B2:F13。(其中一個表格的資料區)

2. 按一下 Ctrl+G 鍵。(開啟[到]對話框)

3. 在[到]對話框中按一下[特殊]按鈕。

4. 在[特殊目標]對話框中,選取「空格」選項,按一下[確定]按鈕。

目前在第一個表格中的所有空白儲存格都被選取了。

5. 在第一個空白儲存格(本例為儲存格B3),輸入「=I3」(第二個表格的相同位置)。

6. 按一下 Ctrl+Enter 鍵。

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

有網友問到:在 Excel 中,如果要從多個欄位中列出資料重覆的清單(資料交集),該如何處理?(參考下圖)

【準備工作】

選取儲存格A1:A26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:A資料。

選取儲存格A2:E26,定義名稱:全部資料。

【輸入公式】

儲存格G2:=IFERROR(OFFSET($A$1,SMALL(IF(COUNTIF(全部資料,A資料)=5,ROW(A資料),FALSE),ROW(1:1))-1,),"")

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

【公式說明】

參考下圖,對照相關說明。

(1) COUNTIF(全部資料,A資料)

在陣列公式中找出每一個A資料在全部資料中的個數。

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

有網友想要解決一個問題:在一個加班的資料表中,希望能分別計算平日和假日的加班時數。而上班情形又分為二種狀況,一種是正常的星期六日為假日加班日,另一種是輪班之星期二四為加班日。

【準備工作】

選取儲存格A1:C24,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、時數。

一、星期六日為假日加班日

(1) 計算平日加班時數

儲存格D2:=SUM((WEEKDAY(A2,2)<6)*C2)

使用 WEEKDAY 函數來取得日期為星期幾,參數「2」表示傳回值1代表星期一、傳回值 2 代表星期二、…、回值 7 代表星期日。

WEEKDAY(A2,2)<6:表示日期為星期一到星期五(為平日)。

image

(2) 計算假日加班時數

儲存格E2:=SUM(NOT((WEEKDAY(A2,2)<6))*C2)

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

開學三週了,有老師問到如何讓課程中的小老師,協助老師利用網路輸入班上同學的小考成績?我想使用 Google 試算表是一個不錯的選擇!

首先,你和小老師都先要具有 Gmail 的帳號。

1. 連線至 http://google.com/drive,進入 Google 雲端硬碟。

2. 按一下「建立」,再選取「試算表」。

3. 建立如下圖的成績表:

4. 建立二個計算平均的公式:

(1) 計算目前成績的平均

儲存格O2:=average(C2:N2)

(2) 計算目前成績最佳 5 次的平均

儲存格P2:=arrayformula(average(large(C2:N2,{1,2,3,4,5})))

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

網友問到一個常見的問題:參考下圖,想要藉由下拉式清單來篩選不同類別的資料,該如何處理?

處理原則是下拉式清單可以使用「資料驗證」功能,而篩選資料通常會用到陣列公式。

【準備工作】

選取「級數」欄位中有資料的儲存格範圍,按一下 Ctrl+Shift+F3 鍵,定義名稱:級數。

【輸入公式】

儲存格G2:{=IFERROR(OFFSET($B$1,SMALL(IF(級數=$F$1,ROW(級數),FALSE),ROW(1:1))-1,COLUMN(A:A)-1,,),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

複製儲存格G2,貼至儲存格G2:H2。複製儲存格G2:H2,往下各列貼上。

參考下圖,對公式加以分析:

IF(級數=$F$1,ROW(級數),FALSE):在陣列公式中,判斷陣列值是否和儲存格F1的內容相同,如果是則傳回測試列對應的 ROW 函數值(參考欄B和欄C的內容),否則傳回 FALSE

SMALL(IF(級數=$F$1,ROW(級數),FALSE),ROW(1:1)):使用 SMALL 函數找尋上式所對應的列號之第1,2,3,…個(參考欄D的內容)。例如:第一個的列號為1,第二個的列號為7,…。

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

有網友想要在如下的資料表中,統計特定起始字元的資料個數,該如何處理?

儲存格D2:=SUMPRODUCT(--COUNTIF($A$2:$A$26,C2&"*"))

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

這個問題的公式主要是要在 COUNTIF 函數中使用萬用字元「*」。「C2&"*"」表示以儲存格C2為開頭的資料。

使用 SUMPRODUCT 函數即可以一個公式來計算整個資料陣列的和,相當方便好用。

註:所謂起始字元並不限定一個字元,也可以是多個字元,例如:SD、ABD等。


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

在 Excel 的資料表中含有 A 組和 B 組資料,如果想要判斷各組的奇/偶數個數,及二者和之奇/偶數個數,該如何處理。

【準備工作】

選取儲存格A1:B22,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:A組、B組。

【基礎知識】

(1) MOD 函數用以餘數,所以可以使用 MOD 函數來找出一個數除以 2 的餘數,用以判斷該數為奇數或是偶數。例如:

MOD(A2,2) = 1,表示儲存格A2的內容為奇數。

MOD(A2,2) = 0,表示儲存格A2的內容為偶數。

(2) MOD(A2,2)+MOD(B2,2) 會有三種可能:

MOD(A2,2)+MOD(B2,2) = 0 :表示 儲存格A2、儲存格B2 皆為偶數。

MOD(A2,2)+MOD(B2,2) = 2 :表示 儲存格A2、儲存格B2 皆為奇數。

MOD(A2,2)+MOD(B2,2) = 1 :表示 儲存格A2、儲存格B2 一奇一偶。

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

最近上課又教到了 AND 函數和 OR 函數,初學者大多不知如何看待這些函數,以下稍微對這兩個函數做一些分析。

一、AND 函數

儲存格C2:=AND(A2,B2)      【複製公式,往下各列貼上】

如果要將輸入 A、B 以 0、1 來表示,則輸出函數改為:

儲存格G2:=AND(E2,F2)*1  【複製公式,往下各列貼上】

記憶方式:有假(0)必為假(0);兩者為真(1)則為真(1)。

以下是關於 AND 函數的分析:(參考下圖)

(1) 當 A=FALSE,不管 B 為何,結果必為 FALSE。【與 B 無關】

(2) 當 A=TRUE,不管 B 為何,結果必為 B。【與 B 相同】

(3) 當 A 和 B 相反,結果為 FALSE。【與(1)同】

(4) 當 A=B=FALSE,結果為 FALSE;當 A=B=TRUE,結果為 TRUE。【與(2)同】

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

有網友問到:如下圖中的資料,欄為各種長度,列為各種高度,欄列的交集為數量,想要藉由輸入長度和寬度來求得數量,該如何處理?

【準備工作】

選取儲存格B1:H1,在名稱管理中新增一個名稱:長度。

選取儲存格A2:A11,在名稱管理中新增一個名稱:寬度。

 

【輸入公式】

以下列出三種作法:

(1) 儲存格K3:=INDEX(資料,MATCH(K2,寬度,0)+1,MATCH(K1,長度,0)+1)

MATCH(K2,寬度,0):查詢儲存格K2的內容,在寬度儲存格陣列中位於第幾個。

MATCH(K1,長度,0):查詢儲存格K1的內容,在寬度儲存格陣列中位於第幾個。

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

有網友問到一個非常實用的問題:一般在 Excel 的工作表中輸入資料時,可以利用「資料驗證」中的清單來產生一個選單,方便使用者以選取選項的方式來輸入資料。但是,如果選項內容是會變動的,或是內容會出現一些空白選項(參考下圖),將會造成一些困擾,該如何克服這些問題?

image

我們要的是一個會自動增加內容的選單,而且沒有多餘的空白項:

image

做法如下:

假設選取用來作為選項的內容為儲存格D2:D22。

先新增一個名稱:MENU,其內容參照為:=OFFSET(D2,,,COUNTA(D2:D22))

正確的寫法如下:(注意工作表名稱及絶對參照($))

內容參照為:=OFFSET(工作表1!$C$2,,,COUNTA(工作表1!$C$2:$C$22))

其中使用 COUNTA 函數來取得儲存格範圍中有多少個含有資料的儲存格數,再透過 OFFSET 函數取得一個動態儲存格範圍(有內容的儲存格範圍)。

接著,選取儲存格A2:A22,設定資料驗證:

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

有網友問到:如下圖左的資料,希望能在不同的工作表中以「區別」篩選出合乎的資料(分別將北區、西區、南區、東區篩選至不同工作表),參考下圖右,該如何處理?

【準備工作】

選取所有的資料範圍,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、區別、金額。

image image

 

以篩選「北區」為例來說明:

【輸入公式】

儲存格A2:{=IFERROR(INDEX(INDIRECT(A$1),SMALL(IF(區別="北區",ROW(區別),FALSE),ROW(1:1))-1,1),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

IF(區別="北區",ROW(區別),FALSE):找出「區別」中符合「北區」的 ROW() 陣列(例如 ROW(3:3)=3、ROW(4:4)=4),不符合者以 False 表示。

SMALL(IF(區別="北區",ROW(區別),FALSE),ROW(1:1)):在上述的陣列 ROW() 中,取出最小值 ROW(1:1)=1,如果往下複製公式時,可以取出第二小值 ROW(2:2)=2,依此類推得到符合北區的儲存格是第幾列的數值陣列。

本例可得陣列:{false,3,4,false,false7,false,…..}。

INDIRECT(A$1):利用 INDIRECT 函數將儲存格A1內容轉換為真實的位址。(儲存格內容已事先定義為名稱了)

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

有網友問到:如果給一個起始的日期,要產生一個固定星期幾週期的日期,該如何處理?參考下圖,以產生每週星期一和星期四為例。

其中:星期一和星期四差三天,星期四和下個星期一相差四天。

在儲存格A2輸入一個起始日期,本例為 2014/2/17。

儲存格A3:=A2+IF(MOD(ROW(1:1),2),3,4)

ROW(1:1):取得第1列的數值(ROW(1:1)=1),當往下複製儲存格時會產生 ROW(2:2)=2、ROW(3:3)=3、…。

MOD(ROW(1:1),2):計算 ROW(1:1) 除以 2 的餘數(=1),當往下複製儲存格時會產生 1、0、1、0、1、0、…。

IF(MOD(ROW(1:1),2),3,4):如果餘數為 1,則傳回 3,餘數為 0 傳回 4。

將上個儲存格日期加上 3 或 4,即可產生週期性星期幾的日期了。


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

有網友根據之前一篇:Excel-標示週末假日的日期(WEEKDAY),想要知道如果日期資料放在不同欄(同一列)時,該如何處理?(參考下圖)

要標示特定日期,需要藉助 WEEKDAY 函式,並利用設定格式化的條件來處理。

1. 選取儲存格A2:I3。

2. 選取「設定格式化的條件」指令,新增一個規則。

image

3. 選取規則類型:使用公式來決定要格式化哪些儲存格。

4. 編輯規則,輸入公式:=WEEKDAY(A2,2)>5

其中 WEEKDAY 函數的參數使用「2」,表示數字1代表星期一、數字2代表星期二、…、數字6代表星期六、數字7代表星期日。(大於5即為星期六、日)

image

5. 設定格式:儲存格底色:淺黃色,文字色彩:紅色,粗體字。

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

有網友問到:在以下的資料表中,如何根據 Num 欄位的內容來傳回「大/中/小」文字?

規則:輸入1,2,3,傳回「小」;輸入4,5,6,傳回「中」;輸入7,8,9,傳回「大」。

以下提供二種不同作法:

(1) 儲存格B1:=VLOOKUP(A2,{1,"小";4,"中";7,"大"},2,TRUE)

這是利用陣列查表方法找出數字對應的文字。

(2) 儲存格B1:=IF(A2<4,"小",IF(A2<7,"中","大"))

這是利用 IF 的巢狀結構來判斷不同數字範圍傳回對應的文字。

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


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

在 Excel 中有一個資料表,其中有多個分組的資料(參考下圖),如何找出各組最大值和最小值呢?

你可能使用過 SUMIF、COUNTIF、AVERAGEIF 等條件式運算函數,但 Excel 中並沒有 MAXIF 或 MINIF 等函數,不過你可以使用陣列公式來取代。

【準備工作】

選取儲存格A1:B21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:組別、數值。

【建立公式】

(1) 求各組最大值

儲存格C2:{=IF(B2=MAX(IF(組別=A2,數值,FALSE)),"V","")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

{IF(組別=A2,數值,FALSE)}:找出和「組別」陣列中和儲存格A2符合的儲存格陣列(判斷式中若不符合者,則給予 False)。

{MAX(IF(組別=A2,數值,FALSE))}:利用 MAX 函數取出上式中的取大值。

 

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼