贊助廠商

///超過3500篇文章列表///

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

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

如果要將一個零散輸入單位和姓名的表格,摘要出各單位出現的次數和包含的人數(不重覆),該如何做呢?

計算各單位出現的次數,F2儲存格的公式:

{=SUM(IF($A$2:$A$28=E2,1,0))}

上式利用陣列判斷合於單位的設定1,然後再將這些1加總,即可得出現的數量。

計算包含的人數,G3儲存格的公式:

{=SUM(IF(FREQUENCY(IF($A$2:$A$28=E2,$B$2:$B$28,""),IF($A$2:$A$28=E2,$B$2:$B$28,""))>0,1))}

接著,利用FREQUENCY函數來計算不重覆出現的人數。

語法:FREQUENCY(data_array,bins_array)

Data_array:是一個要計算頻率的數值陣列或數值參照位址。如果data_array不含資料,則FREQUENCY傳回一個零的陣列。

Bins_array:是一個陣列或一個區間的儲存格範圍參照位址,用來存放data_array裏的數值分組之結果。如果bins_array沒有數值,則FREQUENCY傳回data_array中元素的個數。

再將F2和G2儲存格往下複製。

image1

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

如下圖有兩組資料(欄A、欄B),現在要使用這些資料建立一個摘要表,計算兩組資料交互對應的個數有多少。可以利用SUMPRODUCT函數,例如儲存格E2:

=SUMPRODUCT(($A$1:$A$18=E$1)*($B$1:$B$18=$D2))

再將儲存格E2複製到其他各個儲存格。

SUMPRODUCT 函數:傳回各陣列中所有對應元素乘積的總和。

語法 :SUMPRODUCT(array1,array2,array3, ...)

Array1, array2, array3, ...   是 2 到 255 個欲求其對應元素乘積之和的陣列。

注意:

各陣列必須有相同的維度 (相同的列數,相同的欄數)。否則 SUMPRODUCT 函數會傳回錯誤值 #VALUE!。
SUMPRODUCT 函數會將所有非數值資料的陣列元素當成 0 來處理。

本例中的($A$1:$A$18=E$1)*($B$1:$B$18=$D2)使用了「*」運算,是將兩個陣列拿來相乘。而在運算中,True視為1,False視為0。所以兩組資料的相乘積不為0者,代表兩個條件都滿足,所以使用SUMPRODUCT函數,正好可以算出有幾個1,可以視為幾個數字。

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

如果你要將記錄完整的請假記錄表,轉換成摘要資訊,而不想使用樞紐分析工具,則使用陣列公式應該是不錯的選擇。重點說明相關做法:

在記錄表中若要對齊日期,而不要出現例如:2008/1/4、2008/12/4、2008/10/10等字數不一樣多的對齊問題,則在日期儲存格設定格式為自訂:yyyy/mm/dd。

在星期幾的欄位,其公式為B3儲存格為「=A3」,再設定其格式為「星期X」。

請假假別和時數則自行輸入。

整個記錄表應依日期順序,由小到大記錄。

在F1儲存格中若輸入學年度,則會計算由該年度的8/1到次年的7/31之間的各種假別時數。

在F3儲存格中的公式為:

{=SUM(IF(LEFT($C$3:$C$60,2)=E3,IF($A$3:$A$60>=DATE($F$1+1911,8,1),IF($A$3:$A$60<=DATE($F$1+1912,7,31),VALUE(MID($C$3:$C$60,3,1)),),),0))}

再複製到F4:F6。

在G3儲存格中要將時數換算成天數,則將8小時換算為一天,

在G3儲存格中的公式為:

=INT(F3/8) & "天" & MOD(F3,8) & "小時"

再複製到G4:G6。

接著要建立一個Table,要能自動列出該學年度各種假別的日期和時數。

在I3儲存格中的公式為:

{=IF(ISERROR(SMALL(IF(LEFT($C$3:$C$60,2)=I$2,IF($A$3:$A$60>=DATE($J$1+1911,8,1),IF($A$3:$A$60<=DATE($J$1+1912,7,31),$A$3:$A$60,""))),ROW(1:1))),"",SMALL(IF(LEFT($C$3:$C$60,2)=I$2,IF($A$3:$A$60>=DATE($J$1+1911,8,1),IF($A$3:$A$60<=DATE($J$1+1912,7,31),$A$3:$A$60,""))),ROW(1:1)))}

其中ISERROR用於判斷有公式的儲存格,但卻沒有日期值的時候,避免顯示#Num!。

由於要挑出某個區間的日期,所以借用DATE函數,先將學年轉換成西洋年(+1911),再設定日期。

使用陣列時,則透過IF(IF(IF…))方式達到將三個條件執行AND的功能。

使用SMALL函數和ROW(1:1)(複製後會變成ROW(2:2), ROW(3:3)…),將挑選出來的日期陣列,第一個儲存格顯示最小值(日期),下一個儲存格顯示第2最小值(日期),餘類推。

將儲存格往下複製。

接著利用所顯示的日期,利用查表法將時數顯示出來。

在J3儲存格中的公式為:

=IF(ISERROR(MID(VLOOKUP(I3,$A$3:$C$60,3),3,1)),"",MID(VLOOKUP(I3,$A$3:$C$60,3),3,1)&"小時")

將儲存格往下複製。

將I3複製到K3, M3, O3。

將J3複製到L3, N3, P3。

利用陣列公式,可以自動產生摘要表,並且可以查詢各學年,還可以列出所有請假的日期和時間。

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

如下圖的基本資料,假設要依星期幾來計算各天的數量小計。

image1

其中:

儲存格B17的公式為:{=SUM(IF(WEEKDAY($B$2:$B$15,2)=ROW(1:1),D$2:D$15))}

儲存格B17的公式為:{=SUM(IF(WEEKDAY($B$2:$B$15,2)=ROW(1:1),E$2:E$15))}

結果如下:

image2

WEEKDAY 函數:傳回符合日期的星期。給定的日預設為介於 1(星期日)到7(星期六)之間的整數。

語法:WEEKDAY(serial_number,return_type)

Serial_number   係指想尋找該天日期的序列值。

Return_type   是決定傳回值類型的數字。

Return_type指定傳回的數字:

1 或省略:1 (週日) 到 7 (週末)。與 Microsoft Excel 舊版的性質相同。

2:數字 1 (星期一) 到 7 (星期六)。

3:數字 0 (星期一) 到 6 (星期六)。

本例使用參數2,再配合ROW(1:1),將公式往下的儲存格複製時,會自動變為ROW(2:2)、ROW(3:3)…

,恰可對照星期一~星期日,對照1~7。

使用陣列找到合乎各個星期幾的儲存格,再將鄰欄的數字納入計算整和。

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

聯合新聞網每週一提供的「新聞中的公民與社會」對於準備這個學科的考試,幫助還蠻大的,特摘要連結如下:(摘至2009/8/31)

http://mag.udn.com/mag/campus/itempage.jsp?f_MAIN_ID=13&f_SUB_ID=3748

新聞中的公民與社會/從八八水災看永續發展

從八八水災看永續發展/98年指考試題

政黨政治/98年學測考題

政黨政治/新聞中的公民與社會

政府體制/98年指考及學測試題

新聞中的公民與社會/政府體制

新聞中的公民與社會/兵役制度

政黨政治/98年指考試題

新聞中的公民與社會/經濟發展六要素

新聞中的公民與社會/兩岸ECFA

兩岸ECFA/歷屆學測考題

社會犯罪/歷屆學測及研究用試題

新聞中的公民與社會/社會犯罪

新聞中的公民與社會/立法院議題

立法院議題/歷屆學測及參考試題

新聞中的公民與社會/生態環境問題

新聞中的公民與社會/外交政策

外交政策/歷屆學測考題

國際時事/歷屆學測考題

新聞中的公民與社會/國際時事

政府施政》歷屆學測考題

新聞中的公民與社會》政府施政

新聞中的公民與社會》經濟發展

憲政體制》98年學測考題

98年學測考題

新聞中的公民與社會》憲政體制

新聞中的公民與社會》外交政策

外交政策》指考參考試題

家庭暴力》歷屆學測考題

新聞中的公民與社會》家庭暴力

新聞中的公民與社會》市場經濟

地方制度法》歷屆學測考題

新聞中的公民與社會》地方制度法

公民投票》歷屆學測與參考題

公民投票》核廢料處置場何去何從

公務員懲戒》歷屆學測與參考題

公務員懲戒》「就是范蘭欽」郭冠英免職

通貨緊縮》學測相關試題

通貨緊縮》消費者物價指數看經濟表現

指考搶分新聞中的公民與社會開始預購

廉能政府》歷屆學測相關試題

廉能政府》陽光法案的制定

經濟表現》學測相關試題

經濟表現》失業衍生問題

貿易政策》學測考古題

貿易政策》模擬題組:保護主義

生態環境》模擬題組

生態環境》歷年學測研究試題

宗教文化議題》歷年學測參考試題

宗教文化議題》模擬題組

市場經濟》模擬題組

司法制度》歷年學測研究試題

司法制度》模擬題組:洗錢案之司法爭議

人權政策》學測相關考古題

人權政策》模擬題組:集會遊行法制訂

國家構成要素》模擬題組:國籍身分界定

國家構成要素》學測相關考古題

金融風暴》模擬題組:振興經濟消費券商機

選舉制度》模擬題組:選舉制度的影響

選舉制度》大考中心97年指考研究用試題

遺產分配》模擬題組:民法─遺產分配

媒體素養》大考中心參考試題

媒體素養》模擬題組:媒體識讀能力培養

噪音汙染》模擬題組:噪音管制修正案三讀

兩岸關係》模擬題組:兩岸關係發展

兩岸關係》學測相關考古題

政治體制》學測相關考古題

政治體制》模擬題組:美國總統大選

多元文化》模擬題組:多元文化

多元文化》大考研究用試題

金融風暴》救經濟降稅、補貼、擴大支出

金融風暴》模擬題組:金融風暴

多元文化》從海角七號看多元文化

多元文化》模擬題組:多元文化

多元文化》學測相關考古題

社會安全制度》國民年金制4大保障須知

社會安全制度》模擬題組:國民年金上路

社會安全制度》學測相關考古題

性別平等》學測相關考古題

性別平等》模擬題組:性別差異與平等

性別平等》從同志遊行看性別差異與平等

貨幣經濟》學測相關考古題

貨幣經濟》從雷曼兄弟風暴看貨幣政策

貨幣經濟》模擬題組:國家貨幣政策

經濟學》毒奶粉隱藏「資訊不對稱」

經濟學》模擬題組:大陸毒奶粉事件

政府體制》從泰總理下台看君主立憲

政府體制》學測相關考古題

政府體制》模擬題組:泰國政治動態

法律政治學》官員清廉度入題機率高

法律政治學》告訴.告發.公訴.自訴搞清楚

法律政治學》模擬題組:洗錢案

法律政治學》學測相關考古題

公民拿高分熟讀課本多看報紙

經濟學》蘇麗文奮戰暗藏經濟學

大考加考公民高中讀報熱

公民與社會台大法律採計台大政治觀望

經濟學》模擬題組:奧運篇

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

如果想要在一串學生姓名欄位中,挑出任意幾個名字,做為抽籤之用,該如何處理呢?

將名字列在A欄中,然後在B欄中輸入公式「=RAND()」,即產生任意亂數值。

接著在D4儲存格中輸入公式:

=INDEX($A$1:$A$19,MATCH(LARGE($B$1:$B$19,ROW(1:1)),$B$1:$B$19,))

再將公式複製到D5:D8。其中ROW(1:1)會變為ROW(2:2) … ROW(5:5)。

 image1

 

其中,ROW(1:1)=1,ROW(2:2)=2,…,ROW(5:5)=5

LARGE($B$1:$B$19,ROW(1:1)),即在挑出B1:B19中第1大值。

LARGE($B$1:$B$19,ROW(5:5)),即在挑出B1:B19中第5大值。

MATCH(LARGE($B$1:$B$19,ROW(1:1)),$B$1:$B$19,),在找出第1大值,位在B1:B19的第幾個。

而INDEX($A$1:$A$19,MATCH(LARGE($B$1:$B$19,ROW(1:1)),$B$1:$B$19,))

乃是藉由找出的第幾個,對應到A1:A19中的某一個名字。

此公式的意思,即為找出亂數值最大的前5名所對應的名字。

提示:如果在空白的儲存格上按 F9 鍵,則會重新產生新的亂數,所產生的名字也會不斷的更改。

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

如果想要抓取某一欄位(例如A欄)中的最後一個數值,可以使用以下的公式:

=LOOKUP(9.99999999999999E+307,A:A)

=LOOKUP(9.9E+307,A:A)

公式的意思是要在A欄中找尋Excel可容許的最大正數(9.99999999999999E+307)。

因為LOOKUP函數是以二分搜尋法方式來找尋資料,例如:

=LOOKUP(10,{1,2,3,4,5,6,7,8,9})

先找到中間值5,判斷後繼續在{6,7,8,9}找尋,

先找到中間值8,判斷後繼續在{9}中找尋,

最後找到最接近的值為9。(注意該陣列已經過排序)

而LOOKUP(9.9E+307,A:A)則是要在A欄中找尋一個找不到的值,不管其數列是否已經過排序,除非真的有一個9.99999999999999E+307的數,否則應傳回一個最接近的數,即是該欄的最後一個數。

而改成LOOKUP(MAX(A:A)+1,A:A),應該也是可以得到相同結果。

---------------------------------------------------------------------------------------

LOOKUP函數有兩種語法形式:向量形式與陣列形式。

  • 向量形式:在單列或單欄範圍 (亦稱為向量) 中搜尋值,並從第二個單列或單欄範圍內的相同位置中傳回值,當有大量值的清單要查詢或當值在不同時間會產生變更時,使用向量形式。
  • 陣列形式:在陣列的第一列或第一欄中搜尋指定的值,然後從陣列最後一列或最後一欄的相同位置中傳回值,當有少量值的清單而且在不同時間值都保持不變時,使用陣列形式。

其中:陣列形式的 LOOKUP 會在陣列的第一列或第一欄中搜尋指定的值,然後從陣列最後一列或最後一欄的相同位置中傳回值。若要比對的值位於陣列第一列或第一欄,請使用這種形式的 LOOKUP 函數。若要指定欄或列的位置時,請使用其他形式的 LOOKUP。

語法
LOOKUP(lookup_value, array)LOOKUP 函數的陣列形式語法具有下列引數

  • lookup_value:LOOKUP 函數在陣列中搜尋的值。
    lookup_value 引數可為數字、文字、邏輯值,或是指向某值的名稱或參照位址。
    如果 LOOKUP 函數找不到 lookup_value,就會使用陣列中小於或等於 lookup_value 的最大值。
    如果 lookup_value 小於第一列或第一欄 (視陣列的尺寸而定) 中的最小值,則 LOOKUP 函數會傳回 #N/A 錯誤值。
  • 陣列:此引數包含文字、數字,或要與 lookup_value 比較的邏輯值之儲存格範圍。 

陣列中的值必須以遞增順序排列: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE;否則,LOOKUP 函數可能不會傳回正確的值。

---------------------------------------------------------------------------------------

關於Excel的規格,請參考資料來源:(摘要如下)

http://office.microsoft.com/zh-tw/excel/HP100738491028.aspx

數字精確度:15 位數

最小的可容許負數:-2.2251E-308

最小的可容許正數:2.2251E-308

最大的可容許正數:9.99999999999999E+307

最大的可容許負數:-9.99999999999999E+307

經由公式取得的最大可容許正數:1.7976931348623158e+308

經由公式取得的最大可容許負數:-1.7976931348623158e+308

公式內容的長度:8,192 個字元

公式的內部長度:16,384 個位元組

反複運算:32,767

工作表陣列:受限於可用的記憶體

選取範圍:2,048

在函數中的引數:255

函數的巢狀階層:64

使用者定義的函數類別:255

工作表函數的可用數目:341

運算元堆疊的大小:1,024

跨工作表的相依性:可以有 64,000 張工作表參照其他工作表

跨工作表的陣列公式相依性:受限於可用的記憶體

區域相依性:受限於可用的記憶體

每一張工作表的區域相依性:受限於可用的記憶體

與單一儲存格的相依性:可以有 40 億個公式相依於單一儲存格

從關閉之活頁簿連結的儲存格內容長度:32,767

計算可容許的最早日期:January 1, 1900 (如果使用 1904 的日期系統的話,則為 January 1, 1904)

計算可容許的最晚日期:December 31, 9999

可以輸入的最大時間值:9999:59:59

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

如果你想要計算一群欄位中,奇數欄位的和或是偶數欄位的和,可以使用以下的公式:

 image1

COLUMN:傳回參照位址中的欄名。

語法:COLUMN(reference)

Reference是指某一單一儲存格或儲存格範圍。(如果省略 reference,則COLUMN函數會引用本身的儲存格位址。)

如果reference為一個儲存格範圍,而且COLUMN函數以水平陣列 的格式輸入,則COLUMN函數會以水平陣列的方式傳回各欄的欄號。

注意:Reference 不能指向多個區域。

MOD函數用以計算是不是2的倍數。

其中,MOD(COLUMN($A1:$H1),2)=1和MOD(COLUMN($A1:$H1),2)=2

會傳回True和False組成的陣列。

SUMPRODUCT函數:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1,array2,array3, ...)

Array1, array2, array3, ...    是 2 到 30 個要求其對應元素乘積之和的陣列。其中,SUMPRODUCT函數會將所有非數值資料的陣列元素當成 0 來處理。

計算奇數欄位的和之運算過程:

=True*A1+False*B1+True*C1+False*D1+True*E1+False*F1+True*G1+False*H1

其中True*A1=A1, False*B1=0,餘類推 …

其結果相當於A1+C1+E1+G1

而計算偶數欄位的和,過程則觀念一樣。

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

當在一個儲存格中要使用多個條件來計算個數或是總和,可以透過陣列,藉由「*」符號,將多個條件「AND」在一起。例如:

有沒有覺得比countif和sumif好用呢?

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

如果想要在十五次成績中挑選出較高的十個成績,然後計算其平均,可以利用陣列來處理。

例如在Q2中輸入公式={=AVERAGE(IF(RANK(B2:P2,B2:P2)<=10,B2:P2))}

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

image2

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

image1

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

image1

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

image

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

image

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

image1

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

image1

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

image1

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

image1

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