贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201703 (38)

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

在 Excel 中使用 OFFSET 函數時,應用的函數結果其實是一個儲存格範圍。如何讓學生能將函數和儲存格位址及工作表上的儲存格區域加以連結呢?

例如:OFFSET(F14,-5,-3,3,4) 其結果為 C9:F11 和下圖的儲存格範圍如果能加上連結,將有助於了解這個函數的應用。

本篇文章,試著來製作這個教具,希望對學習者能精進對 OFFSET 函數的了解。

Excel-圖形化OFFSET解說以連結函數與儲存格範圍

因為 OFFSET 函數的參數有:reference、rows、cols、height、width,並且要注意:rows 和 cols 可以為 0、可以為正負、也可以為負數。而 height 和 width 則必須大於 1。

在此想要使用微調按鈕的表單控制項來操作這四個函數,並且列出一組判斷用的儲存格範圍數值供製作圖形之用。而儲存格中的填滿色彩,則以設定格式化的條件來處理。

Excel-圖形化OFFSET解說以連結函數與儲存格範圍

本例要在儲存格B6:J22的範圍內顯示 OFFSET 函數的結果,並以儲存格F14為中心點。以中心點為準,列有上下 8 列,欄有左右 4 欄。

1. 處理微調按鈕

Excel-圖形化OFFSET解說以連結函數與儲存格範圍

新增四個微調按鈕的表單控制項,並且做以下的設定:

1. 最小值:0/最大值16/儲存格連結:P8。

2. 最小值:0/最大值8/儲存格連結:P9。

3. 最小值:1/最大值9/儲存格連結:P10。

4. 最小值:1/最大值9/儲存格連結:P11。

Excel-圖形化OFFSET解說以連結函數與儲存格範圍

(rows)儲存格M8:=8-P8,使用微調按鈕產生數值:-8~8。

(cols)儲存格M9:=4-P9,使用微調按鈕產生數值:-4~4。

(height)儲存格M10:=P10,使用微調按鈕產生數值:1~9。

(width)儲存格M11:=P11,使用微調按鈕產生數值:1~9。

繼續來產生判斷用數值:

(左上角列號)儲存格M14:=14+M8

(左上角欄數)儲存格M15:=6+M9

(右下角列號)儲存格M16:=M14+M10-1

(右下角欄數)儲存格M17:=M15+M11-1


2. 顯示 OFFSET 函數內容及結果

產生參數數值,儲存格B4:

="reference="&M7&", rows="&M8&", cols="&M9&", height="&M10&",
width="&M11

結果,例如:reference=F14, rows=-8, cols=2, height=3, width=8

產生OFFSET函數的內容,儲存格B5:

="OFFSET(F14,"&M8&","&M9&","&M10&","&M11&")="&ADDRESS
(M14,M15,4)&":"&ADDRESS(M16,M17,4)

結果,例如:OFFSET(F14,-8,2,3,8)=H6:O8


3. 將 OFFSET 函數所代表的儲存格區域填滿底色

(1) 選取儲存格B6:J22。

(2) 設定格式化的規則。

選取「使用公式來決定要格式化哪些儲存格」,

輸入規則:=(ROW(B6)>=$M$14)*(ROW(B6)<=$M$16)*(COLUMN(B6)>=
$M$15)*(COLUMN(B6)<=$M$17)

格式:填滿藍色儲存格底色

公式中的『*』相當於執行邏輯 AND 運算。

Excel-圖形化OFFSET解說以連結函數與儲存格範圍

如此,便將 OFFSET 函數和產生的儲存格範圍及圖形化的結果加以連結了。

Excel-圖形化OFFSET解說以連結函數與儲存格範圍

,

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

網友問到:在 Excel 中有如下圖左的資料清單,如何能轉換為下圖右的排列方式?(共有二種)

轉換一:

Excel-手動資料轉置(OFFSET,ROW,COLUMN)

【公式設計與解析】

分析原始資料,資料分佈於間隔 2 列(2,4,6,8)、連續 1 欄(1,2,3,4,5,6)。

儲存格I1:=OFFSET($A$1,INT((ROW(1:1)-1)/6)*2+1,MOD((ROW(1:1)-1),6))

複製儲存格I1,貼至儲存格I1:I24。

(1) INT((ROW(1:1)-1)/6)*2+1

當公式向下複製時,依序產生 1,1,1,1,1,1,3,3,3,3,3,3,5,,5,5,5,5,5,7,7,7,7,7,7。

公式中的「/6」乃因為連續 6 欄;公式中的「*2」乃因為間隔 2 列。

(2) MOD((ROW(1:1)-1),6)

當公式向下複製時,依序產生 0,1,2,3,4,5,0,1,2,3,4,5, 0,1,2,3,4,5,0,1,2,3,4,5。

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

將第(1)式和第(2)式代入 OFFSET 函數中,依序取出對應的儲存格內容。


轉換二:

Excel-手動資料轉置(OFFSET,ROW,COLUMN)

【公式設計與解析】

分析原始資料,資料分佈於連續 1 列(2,3,4,5)、連續 1 欄(1,2,3,4,5,6)。

儲存格I1:=OFFSET($A$1,INT((ROW(1:1)-1)/6)+1,MOD((ROW(1:1)-1),6))

複製儲存格I1,貼至儲存格I1:I24。

(1) INT((ROW(1:1)-1)/6)+1

當公式向下複製時,依序產生 1,1,1,1,1,1,2,2,2,2,2,2,3,3,3,3,3,3,4,4,4,4,4,4。

(2) MOD((ROW(1:1)-1),6)

當公式向下複製時,依序產生 0,1,2,3,4,5,0,1,2,3,4,5, 0,1,2,3,4,5,0,1,2,3,4,5。

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

將第(1)式和第(2)式代入 OFFSET 函數中,依序取出對應的儲存格內容。

, , ,

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

網友想要在 Excel 的工作表中將含有中英文的字串,分別取出中文字和英文字,該如何處理?

在下圖中可見,可能一個儲存格的中文字和英文字是分列左右兩側,也可能是中文字插於英文字之間或是英文字插於中文字之間。以下分別來討論。

Excel-將儲存格中的字串分別取出中文字和英文字(MID,ROW,陣列公式)


1. 儲存格中串字是中文字和英文字分列左右兩側

Excel-將儲存格中的字串分別取出中文字和英文字(MID,ROW,陣列公式)

【公式設計與解析】

(1) 取出左側中文字

儲存格B2:=LEFT(A2,LENB(A2)-LEN(A2))

LEN 函數會傳回文字字串中的字元數(中英文都算1個字元)。

LENB 函數會傳回文字字串中字元的位元組數(中文字=2位元組/英文字=1位元組)。

(2) 取出右側英文字

儲存格C2:=RIGHT(A2,2*LEN(A2)-LENB(A2))

(3) 取出右側中文字

儲存格B4:=RIGHT(A4,LENB(A4)-LEN(A4))

(4) 取出左側英文字

儲存格C4:=LEFT(A4,2*LEN(A4)-LENB(A4))


2. 儲存格中字串是中文字插於英文字之間或是英文字插於中文字之間

Excel-將儲存格中的字串分別取出中文字和英文字(MID,ROW,陣列公式)

【公式設計與解析】

為了解說方便,使用輔助欄位。(E欄和F欄)

先來處理取出英文字。

(1) 找出儲存格A2字串第1個英文字的位置(儲存格E2)

公式:{=MIN(IF(BIG5(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<>MID(A2,
ROW(INDIRECT("1:"&LEN(A2))),1),ROW(INDIRECT("1:"&LEN(A2))),""))}

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

(1.1) ROW(INDIRECT("1:"&LEN(A2)))

因為儲存格A2內容為「一二三ABCD四五六」,

所以 LEN(A2)=10,因此 INDIRECT("1:"&LEN(A2))=INDIRECT("1:10")

在此利用 INDIRECT 函數將字串轉換為位址。

ROW(INDIRECT("1:"&LEN(A2)))=ROW(1:10)={1,2,3,4,5,6,7,8,9,10}

ROW 函數會傳回儲存格的列號。

(1.2) MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)

在陣列公式中利用 MID 函數將儲存格A2中的每一個字元逐一找出來,結果:

={"一", "二", "三", "A", "B", "C", "D", "四", "五", "六"}

(1.3) BIG5(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))

利用 BIG5 函數將其中每個字元都轉成全型字,結果:

={"一", "二", "三", "A", "B", "C", "D", "四", "五", "六"}

(1.4) IF((1.3)式<>(1.2)式,(1.1)式,"")

判斷若全型字元和原來字元不相等者,代表是英文字,則傳回該字元的位置(數字),否則,傳回空字串。本例傳回:{"";"";"";4;5;6;7;"";"";""}

(1.5) MIN((1.4)式)

公式=MIN({"";"";"";4;5;6;7;"";"";""})=4。


(2) 找出儲存格A2字串最後1個英文字的位置(儲存格F2)

公式:{=MAX(IF(BIG5(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<>MID(A2,
ROW(INDIRECT("1:"&LEN(A2))),1),ROW(INDIRECT("1:"&LEN(A2))),""))}

第(2)式和第(1)式的差別有於將 MIN 函數改成 MAX 函數,因此

公式=MAX({"";"";"";4;5;6;7;"";"";""})=7。


(3) 找出儲存格A2字串中的英文字(儲存格C2)

公式:=MID(A2,E2,F2-E2+1)=MID("一二三ABCD四五六",4,7-4+1)="ABCD"


接著來處理取出中文字。

(4) 找出儲存格A4字串第1個中文字的位置(儲存格E4)

公式:{=MIN(IF(BIG5(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1))=MID(A4,
ROW(INDIRECT("1:"&LEN(A4))),1),ROW(INDIRECT("1:"&LEN(A4))),""))}

原理同第(1)式,但是將「<>」改成「=」。

公式=MIN({"";"";"";"";5;6;7;8;"";"";"";"";""})=5


(5) 找出儲存格A4字串最後1個中文字的位置(儲存格F4)

公式:{=MAX(IF(BIG5(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1))=MID(A4,
ROW(INDIRECT("1:"&LEN(A4))),1),ROW(INDIRECT("1:"&LEN(A4))),""))}

原理同第(2)式,但是將「<>」改成「=」。

公式=MAX({"";"";"";"";5;6;7;8;"";"";"";"";""})=8


(6) 找出儲存格A4字串中的中文字(儲存格B4)

公式:=MID(A4,E4,F4-E4+1)

原理同第(3)式。

, ,

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

你是否遇到過在網路上由系統產生的 CSV 格式之檔案,在經由 Excel 開啟後,出現亂碼的問題(如下圖),該如何解決?

例如:由 Google 的雲端平台上產生的 CSV 檔,直接由 Excel 開啟時,就常會遇到亂碼的問題。這是因為文字編碼的問題(RTF-8和ANSI不一樣)所造成。

Excel-解決網路下載CSV檔而產生的亂碼問題

參考以下的步驟,可以來克服這樣的問題:

1. 在要開啟的 CSV 檔案上按右鍵,選取「開啟檔案/記事本」。

Excel-解決網路下載CSV檔而產生的亂碼問題

2. 將文字檔(目前文字沒有亂碼)予以儲存檔案(仍是CSV格式)。

Excel-解決網路下載CSV檔而產生的亂碼問題

3. 將已重新儲存的 CSV 檔再以 Excel 開啟,亂碼已經不再出現了。

Excel-解決網路下載CSV檔而產生的亂碼問題

,

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

網友問到:在 Excel 中如下圖的資料清單,如何從中取出 ERN 之後的代碼?

在下圖中,想要取出每個儲存格中,ERN 之後的藍色代碼,該如何處理?

Excel-找出儲存格字串中的部分文字(FIND,MID)


【公式設計與解析】

如果要以公式來取出儲存格中的字串,一定得要先找出字串中文字排列格式。觀察這些儲存格可以發現:代碼置於 ERN 之後,或是在 # 之前。而要找的代碼固定 7 碼。

(1) 儲存格B2:=MID(A2,FIND("ERN",A2)+4,7)

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

FIND("ERN",A2):利用 FIND 函數先找到 ERN 字串的位置。

再利用 MID 函數依 FIND 函數傳回的位置,再加 4 碼(ERN 3 碼、空格 1 碼),找出之後的 7 碼。


(2) 儲存格B2:=MID(A2,FIND("#",A2)-8,7)

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

FIND("#",A2):利用 FIND 函數先找到 # 字元的位置。

再利用 MID 函數依 FIND 函數傳回的位置,再減 8 碼(代碼 7 碼、空格 1 碼),找出之後的 7 碼。

, ,

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

網友問到:在 Excel 的工作表中有一個資料清單(如下圖左),如何找出各項之最早日期(如下圖右)?

在下圖左的資料清單裡,有「列隊、來源、日期」三個欄位,假設日期都不會重覆。要來找出符合條件者的最小日期。

Excel-找出符合條件的最小日期(陣列公式)


【公式設計與解析】

選取儲存格A1:C26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:列隊、來源、日期。

儲存格G2:{=MIN(IF((列隊=E2)*(來源=F2),日期,""))}

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

公式:IF((列隊=E2)*(來源=F2),日期,"")

假如符合二條件:(列隊=E2)和(來源=F2)者,傳回其對應的日期陣列,否則傳回空字串("")。公式中的「*」,相當於執行邏輯 AND 運算。

再利用 MIN 函數,在傳回符合條件的日期中找到最小值,即為所求。


【特別注意】

IF((列隊=E2)*(來源=F2),日期,""),不可改為:

(X)IF((列隊=E2)*(來源=F2),日期,)

(X)IF((列隊=E2)*(來源=F2),日期,0)

否則透過 MIN 函數取得的結果是錯誤值。

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

在網路上看到有網友在推論一些數字與圖形的關係,例如在下圖中,分別要在顯示1~9數字的位置顯示儲存格色彩,如何利用 Excel 來做到這樣的結果?

在下圖中,要練習如何產生數列(儲存格B2:J10),而且除了分別顯示1~9的儲存格色彩之外(共九個),也利用捲軸的表單控制項來做成動態的顯示結果。就用這個實例來練習公式運算和設定格式的條件。

image

1. 建立基本數字表

這個基本表是如何建立的呢?

原則是例如:儲存格I9,是儲存格I2(=8)和儲存格B9(=8)相乘的結果(=64),將十位數(=6)和個位數(=4) 相加(=10),再將這個結果(=10)的十位數(=1)和個位數(=0) 相加,其結果顯示為 1。

image

公式設計:

儲存格C3:=INT((INT($B3*C$2/10)+MOD($B3*C$2,10))/10)+MOD(INT($B3*C$2/10)+MOD($B3*C$2,10),10)

複製儲存格C2,貼至儲存格C3:J10。

(1) INT($B3*C$2/10:取得兩數相乘結果的十位數。

(2) MOD($B3*C$2,10))/10:取得兩數相乘結果的個位數。

(3) INT((第(1)式+第(2)式)/10):取得第(1)式和第(2)式相加結果的十位數。

(4) MOD(第(1)式+第(2)式,10):取得第(1)式和第(2)式相加結果的個位數。


2. 建立捲軸

image

在[開發人員/控制項]選單中插入一個「捲軸」表單控制項:

image

其內容設定為:最小值:1;最大值:9;儲存格連結:$B$11。

當使用捲軸時,儲存格B11會顯示1~9其中一個數。

image


3. 設定儲存格條件

接著,來設定對應數字的儲存格填滿格式。

先選取儲存格B2:J10,設定格式化的條件:

選取「使用公式來決定要格式化哪些儲存格」,輸入規則:=B2=$B$11。

image

所有和儲存格B11相同的數字儲存格都會填上藍色底色。

image


4. 製作1~9的數字對應圖

如何得到以下的結果?

先複製上圖的公式運算結果,貼至九個區域中(選擇性貼上「值」)。在每個區域的左下角依序填上數字1~9。

image

選取公式運算結果的數字,自訂數值格式為「;;;」(三個分號),儲存格上的數字不會顯示出來。

image

再以上述的格式化條件加以設定即可完成。

, ,

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

前一篇文章:Windows-在字元對應表中查詢中文字,有提到字元對應表的使用。本篇特別再提一個和資訊安全有關的操作。

在字元對應表中,先找到202E這個字元(可在[到Unicode]的文字方塊中輸入「202E」)。這是一個不可見的字元,觀察對話框左下角,標示的是:Right-To-Left Override。加入這個Unicode的文字會由右至左顯示。

Windows-由右向左顯示字元(關於Unicode的202E字元)

觀察一下這個概念:

1. 先在字元對應表中複製這個Unicode 202E字元。

(找到這個字元後,按一下[選取]按鈕,再按一下[複製]按鈕。)

2. 在記事本中開啟一個新的記事,然後輸入「abcdefghijk」。(字由左向右顯示)

3. 在文字下方,貼上先前複製的Unicode 202E字元,然後輸入「abcdefghijk」。

你所「看」到的卻是「kjihgfedcba」(字是由右向左顯示)。

Windows-由右向左顯示字元(關於Unicode的202E字元)

在檔案總管中實作:

1. 先在字元對應表中複製這個Unicode 202E字元。

2. 在檔案總管中建立一個記事本,按F2鍵,更新檔名為:exe.txt。(字由左向右顯示)

3. 在檔案總管中建立一個記事本,按F2鍵,貼上先前複製的Unicode 202E字元,然後輸入「t→x→t→.→e→x→e」。

你所「看」到的卻是「exe.txt」(字是由右向左顯示)。

Windows-由右向左顯示字元(關於Unicode的202E字元)

如此,可能讓有心人士將一個應用程式讓人誤以為是文字檔,並且換了檔案圖示,而讓你執行了這個程式。說不定執行時也會打開記事本,但是同時也植入了木馬程式在你的電腦中了。

如果你能將檔案總管設定為顯示副檔名,則執行前可以看到這是「應用程式」。

以下的例子中,當我把Word中的內容,複製後貼到Excel的儲存格中,發現兩者並不相同。你大概猜到是什麼原因了。

在Word中輸入按abcd後,插入Unicode 202E字元,再輸入1234。當貼至Excel的儲存格時,這個不可見字元被過濾掉了,所以顯示了正常的內容。

Windows-由右向左顯示字元(關於Unicode的202E字元)

在Word中可以搜尋/取代Unicode 202E字元。

Windows-由右向左顯示字元(關於Unicode的202E字元)

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

在 Excel 的工作表中,常會見到一些日期的內容,但這個日期其實可能為數值,也可能為文字。要如何將其全數轉換為數值格式?

常看到同仁在處理文件時,因為日期可能是文字,也可能是數值,而引發各種困擾。由於數值才能拿來運算,所以將文字轉換為數字,是重要的一件事。以下是要以函數運算來將文字轉換為數值。

Excel-將文字格式日期轉換為數值格式日期(NUMBERVALUE,VALUE)

首先,來判斷A欄中的日期,是數值或是文字。

儲存格B2:=ISNUMBER(A2)

若傳回值是TRUE,則該儲存格為數值;若傳回值是FALSE,則該儲存格為文字。

接著,將儲存格C2的日期轉換為數值(格式為:n)

儲存格C2:=VALUE(TEXT(A2,"@"))

TEXT(A2,"@"):用以將日期格式顯示為一個數字(傳回的數字是文字格式),再透過VALUE函數,轉換為數值。

或是,

儲存格C2:=NUMBERVALUE(A2)

NUMBERVALUE函數用以將文字轉換為數值。

也可以將儲存格C2的日期轉換為數值(格式為:yyyy/m/d)

儲存格D2:=A2*1

『*1』運算,即會自動的將結果轉換為數值。

複製儲存格B2:D2,貼至儲存格B2:D14。

, , , ,

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

延伸前一篇文章:利用Excel製作能快速查詢多個字詞之注音的系統,如果要查詢列出同音字,該如何處理?

先來探討一個 Excel 的問題:原始資料約有17萬筆,若使用陣列公式({}),速度變慢且容易當機。若不使用陣列公式,則必須增設輔助欄位,以二階段方式來撰寫公式。雖然高達17萬筆的資料,可以明顯的感覺加快了查詢速度。

在下圖中,想要在儲存格F1中輸入一個字或詞後,想要列出所有同音的字或詞。在H欄中增設了輔助欄位,用以加速查詢的速度。

Excel-替代陣列公式以在大筆資料中加快速度(MATCH,OFFSET)


【公式設計與解析】

先來處理輔助欄位:

1. 找出第一筆符合的儲存格列號

儲存格H4:=IFERROR(MATCH($F$1,$B$2:$B$170000,0),"")

(1) MATCH($F$1,$B$2:$B$170000,0)

利用MATCH函數找出儲存格F1在全部資料中符合的儲存格是第幾筆。

利用IFERROR函數將傳回的錯誤訊息轉換為空字串。

2. 找出第二筆以後符合的儲存格列號

儲存格H5:=IFERROR(MATCH($F$1,OFFSET($B$2,H4,0,170000,1),0)+H4,"")

(1) OFFSET($B$2,H4,0,170000,1),0)

找出第一筆符合的儲存格之後的儲存格範圍。

(2) MATCH($F$1,OFFSET($B$2,H4,0,170000,1),0)

利用第(1)式的傳回值,再藉由MATCH函數找出儲存格F1在全部資料中符合的儲存格是第幾筆。

(3) MATCH($F$1,OFFSET($B$2,H4,0,170000,1),0)+H4

將第(2)式的傳回值再加上前一筆傳回值,才是第二筆在整個儲存格的實際所在位置。

最後,利用IFERROR函數將傳回的錯誤訊息轉換為空字串。

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

接著處理查詢欄位:

儲存格E4:=IFERROR(OFFSET($B$1,H4,0),"")

儲存格F4:=IFERROR(OFFSET($C$1,H4,0),"")

複製儲存格E4:F4,貼至儲存格E4:F12。

, ,

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

網友問到一個在 Excel 中常見的問題:常你使用 VLOOKUP 函數來查詢資料時,會因為原始資料中的內容是空白,傳回的是『0』,而非空白。該如何解決這個問題?

參考下圖,當使用 VLOOKUP 函數來查詢座號18的資料時,儲存格I3卻顯示0,如何將其顯示為空白?

Excel-解決VLOOKUP查詢時因原始資料是空白而傳回0的問題

【問題解決與分析】

原始設計,儲存格I3:=VLOOKUP(F3,$A3:$D27,4)

更改設計,儲存格I12:=VLOOKUP(F3,$A3:$D27,4)&""

只要將原始公式再串接一個空字串(""),即可解決。

,

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

最近學校同仁需要查詢多個國語字詞的注音,因而發現了操作時的一些問題。讓我來描述一下狀況。

通常,大多數人都是利用「教育部重編國語辭典修訂本」來查詢注音:

(網址:http://dict.revised.moe.edu.tw/cbdic/

利用Excel製作能快速查詢多個字詞之注音的系統

經由輸入一個字或詞,再從中點選想要字或詞,得到最終網頁結果:

利用Excel製作能快速查詢多個字詞之注音的系統

網頁也會提供一個網址給你利用,例如:http://dict.revised.moe.edu.tw/cgi-bin/cbdic/gsweb.cgi?o=dcbdic&searchid=W00000011170,讓你可以直接連結。

【問題】

教師每查一個字或詞,都要經過多個步驟才能得到查詢結果,如果有很多字詞要尋找,將會花掉太多的時間。如果教育部能直接提供網址的參數,可以讓使用者將要查詢的字詞加上,即可取得網頁內容,即可加快查詢速度。但是,好像沒有發現這樣的功能。

【解決方案】

為了幫助老師解決類似的問題,以下是參考做法:

先連結到:http://resources.publicense.moe.edu.tw/

這是教育部國語辭典公眾授權網,點選《重編國語辭典修訂本》的「資料下載」:

利用Excel製作能快速查詢多個字詞之注音的系統

再下載文字資料庫:

利用Excel製作能快速查詢多個字詞之注音的系統

這是一個壓縮檔,解開後有三個 Excel 檔:

利用Excel製作能快速查詢多個字詞之注音的系統

把三個 Excel 檔彙整成一個檔,並且刪掉不想要的欄位:

利用Excel製作能快速查詢多個字詞之注音的系統

在另一個工作表中撰寫公式:

儲存格B2:=IFERROR(VLOOKUP($A2,DATA,COLUMN(B:B),FALSE),"")&""

複製儲存格B2,貼至儲存格B2:D20。(其中DATA是你的資料範圍)

公式中的「&""」,乃是要將VLOOKUP查詢到空白的儲存格所傳回的 0 轉換為空白。(例如查詢二個字以上的詞時,部首字欄位會傳回 0

當你在A欄的儲存格中輸入一個字或詞時,B欄至D欄的內容即會呈現查詢結果。

其中如果注音欄位出現(一)或(二)等字,表示該字多種讀音。

image

如果想要知道其不同的讀音,則可以回到資料表,利用篩選功能來手動找出不同讀音。

利用Excel製作能快速查詢多個字詞之注音的系統

例如:「高中」有二種不同讀音。

利用Excel製作能快速查詢多個字詞之注音的系統

也可以換個方式呈現:

利用Excel製作能快速查詢多個字詞之注音的系統

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

根據上一篇文章:Excel-垂直資料轉換為矩陣形式(SUMPRODUCT,OFFSET,COLUMN)

如果想要調整為列出每日住院的病歷,該如何處理?

Excel-垂直資料轉換為矩陣形式(SUMPRODUCT,OFFSET,COLUMN)2

【公式設計與解析】

選取儲存格A1:D15,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:病歷號、入院日、出院日。

1. 計算每日人數

儲存格B18:=SUMPRODUCT((入院日<=$A18)*(出院日>=$A18))

複製儲存格B18,貼至儲存格B18:B33。

在陣列公式中判斷入院日儲存格陣列是否小於儲存格A18並且出院日大於儲存格A18,若是,則傳回TRUE,若否,則傳回FALSE。公式中的『*』運算,可以將TRUE/FASLE陣列轉換為1/0陣列。


2. 填入每日病歷號碼

{=IFERROR(OFFSET($A$1,SMALL(IF((入院日<=$A18)*(出院日>=$A18),ROW(病歷號),""),COLUMN(A:A))-1,0),"")}

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

複製儲存格B18,貼至儲存格B18:M33。

詳細說明參閱:Excel-垂直資料轉換為矩陣形式(SUMPRODUCT,OFFSET,COLUMN)

, , ,

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

如果在 Windows 中要查詢字元的 Unicode,通常可以由字元對應表中來查詢(如下圖)。但是在茫茫字海中,如何能快速的搜尋到想要的「中文字」呢?

你一定不想要逐頁瀏覽來搜尋!以下的方法也不見得是最快,請參考:

先選取「進階檢視」:

Windows-在字元對應表中查詢中文字

在[分組方式]下拉式清單中選取「中文繁體注音符號」:

Windows-在字元對應表中查詢中文字

藉由注音的分組方式來查詢想要的字元:

Windows-在字元對應表中查詢中文字

本例查詢:楊:

Windows-在字元對應表中查詢中文字

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

網友問到:在 Excel 工作表有一個如下圖的上方資料表,如何轉換為下方的資料表?

Excel-垂直資料轉換為矩陣形式(SUMPRODUCT,OFFSET,COLUMN)


【公式設計與解析】

選取儲存格C1:C15,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:入院日。


1. 計算每日人數

儲存格B18:=SUMPRODUCT((入院日=A18)*1)

複製儲存格B18,貼至儲存格B18:B24。

SUMPRODUCT 函數中的『*1』,用以將判斷式傳回的 TRUE/FALSE 陣列,轉換為 1/0 陣列。


2. 填入每日病歷號碼

儲存格C18:{=IFERROR(OFFSET($A$1,SMALL(IF(入院日=$A18,ROW(入院日),""),
COLUMN(A:A))-1,0),"")}

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

複製儲存格B18,貼至儲存格B18:H24。

(1) IF(入院日=$A18,ROW(入院日),"")

在陣列公式中判斷入院日儲存格陣列是否和儲存格A18相同,若是,則傳回對應的列號,若否,則會空字串。

(2) SMALL(第(1)式,COLUMN(A:A))

根據第(1)傳回的列號,由小到大依序取出最小者。若COLUMN(A:A)往右方向複製時,則COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...。

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

根據第(2)式傳回的列號,代入 OFFSET 函數取出對應的儲存格內容。

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

因為第(2)式可能傳回錯誤訊,所以令傳回值為錯誤訊息者,改顯示為空字串。

, , ,

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

在 Google 的試算表中,如果你想要製作下圖這樣的組織圖,該如何處理?

Google試算表-製作組織圖

你必須要先熟悉其資料項目的排列方式。

(如下圖)資料項目分為上層和下層,仿照圖中的排列邏輯(對照紅色指引)來建構資料。

Google試算表-製作組織圖

資料建構完成後,選取這些資料,再選取[插入]功能表中的「圖表」選項。

Google試算表-製作組織圖

在對話框中選取[圖表類型]標籤下的「組織圖」(取消勾選「匯總第A欄」)。

即可按下「插入」,大功告成。

Google試算表-製作組織圖

,

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

網友根據前一篇文章:Excel-找出一群數列之最大值/最小值的儲存格位址(SUMPRODUCT),延伸問到如何找出一群數列之第1,23,大值的儲存格位址?

參考下圖,假設其中的數字均沒有重覆,使用三個不同色彩分別標示第 1,2,3 大的值。

Excel-找出一群數列之第1,23,大值的儲存格位址(SUMPRODUCT,LARGE)


【公式設計與解析】

1. 找出第 1 大值的儲存格位置

儲存格H2:=ADDRESS(SUMPRODUCT((A1:F17=LARGE(A1:F17,1))*ROW
(A1:F17)),SUMPRODUCT((A1:F17=LARGE(A1:F17,1))*COLUMN(A1:F17)))

(1) SUMPRODUCT((A1:F17=LARGE(A1:F17,1))*ROW(A1:F17))

找出最大值的列號。ROW 函數會傳回儲存格的列號。

(2) SUMPRODUCT((A1:F17=LARGE(A1:F17,1))*COLUMN(A1:F17))

找出最大值的欄數。COLUMN 函數會傳回儲存格的欄數。

(3) ADDRESS(第(1)式,第(2)式))

將第(1)式和第(2)式代入 ADDRESS 函數即會傳回位址。


2. 找出第 2 大值的儲存格位置

儲存格H5:=ADDRESS(SUMPRODUCT((A1:F17=LARGE(A1:F17,2))*ROW(A1:F17)),SUMPRODUCT((A1:F17=LARGE(A1:F17,2))*COLUMN(A1:F17)))

LARGE 函數的參數改成 2。


3. 找出第 3 大值的儲存格位置

儲存格H8:=ADDRESS(SUMPRODUCT((A1:F17=LARGE(A1:F17,3))*ROW(A1:F17)),SUMPRODUCT((A1:F17=LARGE(A1:F17,3))*COLUMN(A1:F17)))

LARGE 函數的參數改成 3。

, , ,

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

有人想要找出在 Excel 工作表中一群數列之最大值和最小值的儲存格位址,該如何處理?

在以下的圖表中,假設其中的數字均沒有重覆,要來出最大值和最小值的儲存格位址。

Excel-找出一群數列之最大值/最小值的儲存格位址(SUMPRODUCT)


【公式設計與解析】

1. 找出最大值的儲存格位置

儲存格H2:=ADDRESS(SUMPRODUCT((A1:F17=MAX(A1:F17))*ROW(A1:F17)),
SUMPRODUCT((A1:F17=MAX(A1:F17))*COLUMN(A1:F17)))

(1) SUMPRODUCT((A1:F17=MAX(A1:F17))*ROW(A1:F17))

找出最大值的列號。ROW 函數會傳回儲存格的列號。

(2) SUMPRODUCT((A1:F17=MAX(A1:F17))*COLUMN(A1:F17))

找出最大值的欄數。COLUMN 函數會傳回儲存格的欄數。

(3) ADDRESS(第(1)式,第(2)式))

將第(1)式和第(2)式代入 ADDRESS 函數即會傳回位址。


2. 找出最小值的儲存格位置

儲存格H5:=ADDRESS(SUMPRODUCT((A4:F20=MIN(A4:F20))*ROW(A4:F20)),
SUMPRODUCT((A4:F20=MIN(A4:F20))*COLUMN(A4:F20)))

原理同1.,公式的差異在於最大值使用 MAX 函數,最小值使用 MIN 函數。

,

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

在 Excel 中,如果要對一群三個 0/1 一組的清單來計算共有幾個是每組有 2 個 1 的個數,該如何處理?

參考下圖,每一列中有 3 個數,由 0/1 組成,試著來計算每組有 2 個 1 的總數有幾個。

Excel-計算清單中具有3個數中含2個1的總數(SUMPRODUCT)


【公式設計與解析】

(1) 使用 SUMPRODUCT 函數

儲存格F3:=SUMPRODUCT((C3:C19+D3:D19+E3:E19=2)*1)

複製儲存格F3,貼至儲存格F3:F19。

SUMPRODUCT 函數中是以陣列形式來執行 C3:C19+D3:D19+E3:E19,運作過程如下所示:

Excel-計算清單中具有3個數中含2個1的總數(SUMPRODUCT)

C3:C19+D3:D19+E3:E19=2 運算會傳回 TRUE/FALSE 陣列。(參考上圖)

公式中的『*1』運算乃將 TRUE/FALSE 陣列轉換為 1/0 陣列。


(2) 使用陣列公式

儲存格F3:{=SUM((C3:C19+D3:D19+E3:E19=2)*1)}

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

複製儲存格F3,貼至儲存格F3:F19。

該公式的運算原理如(1),只是寫法不同而已。

,

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

延續前一篇文章:Excel-將整年的資料清單分配到12個月的資料表中(OFFSET,MATCH)

如果你的原始資料清單中不是一個月中的每一天都有資料,而且呈現的時候只想要呈現有日期的資料,該如何處理?

以下是三個月的原始資料,有些日期沒有資料。

Excel-將整年的資料清單分配到12個月的資料表中(OFFSET,MATCH)


【公式設計與解析】

先選取[全年]工作表中的所有日期欄位儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。

1. 列出一月份清單

Excel-將整年的資料清單分配到12個月的資料表中(OFFSET,MATCH)

儲存格C2:{=IFERROR(OFFSET(全年!C$1,SMALL(IF(MONTH(日期)=1,ROW(日期),
""),ROW(1:1))-1,0),"")}

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

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

(1) IF(MONTH(日期)=1,ROW(日期),"")

在日期陣列公式中判斷是否月份為1(1月份),若是,則傳回列號,若否,則傳空字串。其中 MONTH 函數用以傳回日期的月份,ROW 函數傳回儲存格列號。

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

利用 SMALL 函數依序由小到大取出列號的最小值。當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

(3) OFFSET(全年!C$1,第(2)式-1,0)

將第(2)式代入 OFFSET 函數,根據第(2)式傳回的列號取得對應儲存格的內容。

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

在執行第(2)式時可能傳回錯誤訊息,藉 IFERROR 函數將錯誤訊息顯示為空字串(空白)。


2. 列出二月份清單

Excel-將整年的資料清單分配到12個月的資料表中(OFFSET,MATCH)

儲存格C2:{=IFERROR(OFFSET(全年!C$1,SMALL(IF(MONTH(日期)=2,ROW(日期),
""),ROW(1:1))-1,0),"")}

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

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


3. 列出三月份清單

Excel-將整年的資料清單分配到12個月的資料表中(OFFSET,MATCH)

儲存格C2:{=IFERROR(OFFSET(全年!C$1,SMALL(IF(MONTH(日期)=3,ROW(日期),
""),ROW(1:1))-1,0),"")}

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

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

, ,

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

1 2

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼