贊助廠商

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

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

開學之初而已,就有同仁問到在 Excel 中,關於名次計算的問題。參考下圖,一般在計算名次時,可能會有這樣的狀況:

1. 名次重覆跳過:例如有 2 個第 4 名,所以沒有第 5 名。

2. 名次重覆不跳過:例如有 2 個第 4 名,原來的第 6 名成為第 5 名。

3. 名次不重覆:例如有 2 個第 4 名,會隨機產生一個第 4 名,另一個為第 5 名。

Excel-6種計算名次的方法(RANK,SUMPRODUCT,COUNTIF)

 

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

網友問到 Excel 的問題:如何在一個日期區間中計算工作天數、放假天數?

參考下圖,因為每個人遇到的狀況不同,當要計算工作天數時,你可能要考量到星期六、星期日和放假日,也要考量到補班日,該如何處理?

Excel-計算工作天數和放假天數(NETWORKDAYS,SUMPRODUCT)

 

【公式設計與解析】

1. 不含六日的工作天數

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

網友想要將 Excel 工作表中的資料表(如下圖左)取出資料重組成下圖右的樣子,該如何處理?即原本甲、乙、丙分散在多欄,現在要將甲、乙、丙分別組合成一欄,該如何設計公式?

image

 

【公式設計與解析】

分析上圖,甲、乙、丙有 3 個類別,每個類別有 5 個項目。

儲存格M2:=OFFSET($A$1,MOD(ROW(1:1)-1,5)+1,INT((ROW(1:1)-1)/5)*3)

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

有網友問到:在 Excel 中有一個資料清單,如下圖,如果想要在每種相同商品中找出最高/最低報價及其廠商,該如何處理?

在下圖中,有甲、乙、丙、丁、戊等不同廠商,分別有A、B、C、D、E等不同商品,現在要來找出各種商品的最高/最低報價和所屬廠商。

Excel-在資料清單查詢相同項目的最大值和最小值(OFFSET,MAX,MIN,陣列公式)

 

【公式設計與解析】

選取儲存格A1:C26,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:廠商、商品、報價。

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

網友問到:在下圖中的 Excel 資料表中,如果要計算三個條件都成立的個數有幾個,該如何處理,為何下圖中的公式會『錯誤』。其中的重點是要滿足日期中符合所要月份的條件。

選取儲存格A1:C27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:人員、日期、項目。

Excel-如何正確取出日期中的月份(MONTH,SUMPRODUCT,N)

 

【公式說明】

儲存格F4:=SUMPRODUCT((人員=F1)*(MONTH(日期)=F2)*(項目=F3))

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

本篇文章純粹是要練習幾個 Excel 函數的應用,是否有實用性沒有考量到。參考下圖,有兩個儲存格想要比較其中內容,每一個位元的字元是否相同。

一、儲存格中的內容全為數字

Excel-判斷兩個儲存格中的每個字元是否相同(SUMPRODUCT,MID,COLUMN)

 

(1) 計算各位元相同者

儲存格C5:

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

有網友問到:在工作表如果儲存裡填入運算式,如何得到這個運算式的運算結果?

一般我們在儲存格中顯示的是運算結果,而公式編輯列中顯示運算公式。參考下圖,如果直接將運算式置入儲存格中顯示出來,再取用這個運算公式來計算結果,該如何處理?

Excel-利用儲存格中的運算式計算運算結果(EVALUATE)

首先,要選取儲存格B2,然後定義名稱:運算;參照到:EVALUATE(工作表1!A2)。

Excel-利用儲存格中的運算式計算運算結果(EVALUATE)

然後,輸入公式:

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

我們常在 Excel 的資料清單中會遇到計算『人次』和『人數』的問題,該如何處理?

參考下圖,在不同管道中有些人重覆出現在 A 和 B 管道中,因次計算人次和人數時,人次會比人數多。

Excel-統計人次和人數(不重覆者)(SUMPRODUCT,COUNTIF

如果你不想使用公式,可以使用樞紐分析表工具:在「列」欄位中指定『班級』,在「值」欄位中指定『學號』。

Excel-統計人次和人數(不重覆者)(SUMPRODUCT,COUNTIF

並且設定欄位中設定摘要的計算類為:項目個數。

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

網友問到一個 Excel 的問題:在以下的資料清單中,如果想要執行分組亂數重排,該如何設計公式?

以下圖為例,若每 20 個一組,整個資料清單有若干列(幾列都有可能),如何能在每一分組中,各自亂數重排。每按一次 F9 鍵,即可得到一個新的重排結果。

Excel-分組亂數排序(INT,MATCH,OFFSET)

 

【公式設計與說明】

在工作表中我們需要一個輔助欄位,其中置入亂數,網友是提供了:=INT(RAND()*100)+ROW()/1000,其實如果需要亂數,只要使用=RAND()即可。

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

有網友問到:在 Excel 的工作表中,如何求取一個儲存格範圍中的不重覆數值個數?參考下圖,網友使用了以下的公式:(錯誤結果)

(X) 儲存格G3:{=SUM(COUNTIF(B2:E11,B2:E11))},這是陣列公式。

Excel-計算儲存格範圍中不重覆的數值個數(SUMPRODUCT,COUNTIF)

網友的公式有誤,這是因為在陣列公式中,每個儲存格都會計算和自己相同數值的個數,但是每一個重覆的數值,也都執行了相同的動作,因此結果會傳回重覆計算的結果。

Excel-計算儲存格範圍中不重覆的數值個數(SUMPRODUCT,COUNTIF)

我們來修改網友的公式:

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

有網友問到:在 Excel 中,如果在公式中想要將數值以某種數值格式顯示,該如何處理?參考下圖,如果在公式中使用數值2761,想要調整成$1,761.0,要如何設定公式?

Excel-在公式中設定數值顯示的格式(TEXT)

你只要將數值格式置入 TEXT 函數中,再應用於公式中即可。例如:

儲存格C2:=IF(A2<>"","要買"&B2&"元的貨","")

調整成

儲存格C3:=IF(A3<>"","要買"&TEXT(B3,"$#,???.0")&"元的貨","")

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

快要過農曆新年了,有網友想要利用一些公司員工的清單來製作摸彩券,該如何處理?其中員工清單包含「職稱、編號、姓名」,配合新年快樂圖樣製作二聯式的摸彩券。

(1)型式A

Excel-依名單設計摸彩券(OFFSET,INT,MOD)

儲存格B1:=OFFSET($F$1,INT((ROW(B1)-1)/3),MOD(ROW(B1)-1,3))

複製儲存格B1:貼至儲存格B1:B3。複製儲存格B1:B3,貼至儲存格D1:D3。

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

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

網友根據前一篇文章:Excel-計算各類茶飲的去冰或少冰數量(SUMPRODUCT),想要了解如果不使用 SUMPRODUCT 函數,而使用 COUNTIFS 函數,也可以得到相同結果?

Excel-計算各類茶飲的去冰或少冰數量(COUNTIFS)

因為要使用雙條件,所以不能只使用 COUNTIF 函數,參考以下公式:

儲存格F2:=COUNTIFS($B$2:$B$27,$E3,$C$2:$C$27,"")

儲存格G2:=COUNTIFS($B$2:$B$27,$E2,$C$2:$C$27,"少冰")

儲存格H2:=COUNTIFS($B$2:$B$27,$E2,$C$2:$C$27,"去冰")

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

網友問到:想要在 Excel 的工作表中匯入某個網頁的內容,該如何處理?

網頁中有些資料是動態的資料表,每間隔一段時間會有所變動,如何在 Excel 的工作表中建立和這些資料的關聯,並且隨著網頁的變動,而跟著變動工作表中的內容?

範例網址:https://tw.finance.yahoo.com/q/q?s=0050

Excel-在工作表中匯入網頁中的動態資料

1. 在新增的 Excel 工作表中,點選[資料/取得外部資料]功能表中的「從Web」選項。

Excel-在工作表中匯入網頁中的動態資料

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

有網友問到:在 Excel 中如何計算各類茶飲的去冰或少冰數量?如下圖中,有多種飲品(紅茶、綠茶、奶茶、清茶),特調為少冰或去冰,如何計算各種項目特調後的數量?

Excel-計算各類茶飲的去冰或沙冰數量(SUMPRODUCT)

 

【公式設計與解析】

儲存格F2:=SUMPRODUCT(($B$2:$B$27=E2)*($C$2:$C$27=""))

有的讀者對於使用 SUMPRODUCT 函數並不熟悉,平常都是使用 SUM 函數或是 SUMIF 函數。公式中的「*」,在運算時相當於執行邏輯 AND 運算,兩個關係運算會傳回 TRUE/FALSE 陣列。在執行「*」運算時,會將 TRUE/FALSE 陣列轉換為 1/0 陣列,在 SUMPRODUCT 函數中執行『乘積和』的動作。

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

有網友問到:如何在 Excel 中,當要設計二層下拉式清單時,可以跨工作表使用?例如:在下圖中是一些類別:『季別、月份、星期、天干、地支』的不同項目:

Excel-設計跨工作表的二層下拉式選單(INDIRECT,資料驗證)

當我們在另一個工作表中要設計一個下拉式選單來根據類別,再選取項目,該如何處理?

Excel-設計跨工作表的二層下拉式選單(INDIRECT,資料驗證)

先前的另一篇文章:Excel-製作二層的下拉式清單來輸入資料(INDIRECT,資料驗證),做法是將二層選單內容和選單放在同一個工作表中,讀者可以自行參考。

Excel-設計跨工作表的二層下拉式選單(INDIRECT,資料驗證)

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

有網友問到:如何在 Excel 中,利用一個多類別的折扣對照表來查詢各種數量的折扣。例如下圖中,分別有陸運、海運、空運三類的不同數量/折扣的對照表,要如何設計公式,只要挑選運別,輸入數量後,自動產生折扣值?

先前的一篇文章:Excel-在兩個資料表中查詢(VLOOKUP),已有一些說明。本文用不一樣的方式來操作。在下圖中,以陸運為例:數量超過200,折扣為95%;數量超過300,折扣為92%。

Excel-在多個類別中查詢(VLOOKUP,INDIRECT)

 

【公式設計與解析】

1. 為各種類別命名儲存格範圍。

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

有網友問到一個 Excel 的問題:如果要將一個數值分配給指定的項目數,如何設計公式能自動分配,並使其總和與原數值相同?

參考下圖中的例子,數值24833要分配給7個項目,其中有幾個會比其他數值多 1。分配後的總和與原數值相同。

Excel-將一個數值分配給多個項目,且總和與原數值相同(INT,MOD,ROW)

(1)

儲存格A4:=ROW(1:1)

儲存格B4:=INT($B$1/$D$1)+(ROW(1:1)<=MOD($B$1,$D$1))

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

網友問到:如何在 Excel 的工作表中設計間隔列使用不同色彩?如下圖,分別呈現間隔1~8列的不同色彩。一般都是使用『設定格式化的條件』來設定即可完成。

Excel-間隔列套用不同色彩(MOD,ROW)

在以下的示意圖中,假設原始儲存格底色為『淺綠色』,我們只要讓顯示 TRUE 的儲存格顯示不同色彩(例如『淺藍色』)即可。

Excel-間隔列套用不同色彩(MOD,ROW)

如果要間隔1列顯示不同儲存格底色,則參考以下步驟:

1. 選取儲存格B2:B24。

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

最近孩子問到:在 Word 文件中如何將一篇文章排版成放在稿紙中的樣子?這是非常容易的,因為 Word 已內建有稿紙功能。你只要點選[版面配置/稿紙]選單中的「稿紙設定」功能。

Excel-模擬Word文件將文字置入稿紙中(MID,ROW,COLUMN)

在[稿紙設定]對話框中挑選格線的格式、列數x欄數、格線色彩等,再選取頁面方向和設定頁首/頁尾等。

Excel-模擬Word文件將文字置入稿紙中(MID,ROW,COLUMN)

Word 會自動將文字置入你設定的稿紙格式中。(本例有勾選「允許標點符號益出邊界」)

Excel-模擬Word文件將文字置入稿紙中(MID,ROW,COLUMN)

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

Close

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

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

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

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

reload

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼