贊助廠商

先前的文章:

用Goole表單來設計線上測驗卷並且評分,還可以獲得測驗結果的統計分析

參考下圖,已經可以由回應結果中看到有一個欄位是『分數』欄位,不用再自行計算分數。但是,如果你「修改了正確的答案,或是修改了各題的得分」,該如何修正分數?

本篇文章要來練習如果要自行判斷得分,該如何處理?

取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)

1. 先下載回應結果為試算表格式。

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

每隔一段時間,就有老師會問到在實務上會遇到的問題:如何運用 Excel 來產生隨機座位表?

例如下圖中,每按一次 F9 鍵,就可以產生一個隨機座位表,該如何處理?

Excel-產生隨機座位表(RAND,OFFSET,MATCH,ROW,COLUMN)

 

【公式設計與解析】

觀察I欄、J欄、K欄,除了座號和姓名之外,在I欄中多了一個亂數欄位,其儲存格內容:『=RAND()』。

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

網友問到的 Excel 的問題:如下圖,有一個日期和服務的清單列表,其中是三種服務的記錄,如何根據這個服務清單(下圖左),轉換為個別三個服務的日期清單(下圖右)

Excel-資料清單轉換(OFFSET,INDIRECT,ROW,陣列公式)

【公式設計與解析】

首先,選取儲存格A1:D27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、A服務、B服務、C服務。

接著輸入公式,儲存格F2:

{=OFFSET($A$1,SMALL(IF(INDIRECT(F$1)="V",ROW(日期),999),ROW(1:1))-1,0)}

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

網友問到 Excel 的問題:如下圖,如何找出每個項目最高價的公司名稱?

觀察下圖,不同項目都有三個公司的標價,其中F欄標示出最高價,要從這二個資訊反推最高價的公司,該如何處理?

Excel-查表反推欄列標題(OFFSET,MATCH)

【公式設計與解析】

這個問題的概念像是要由表格內容反推欄或列的標題。

儲存格G2:=OFFSET($C$1,0,MATCH(F2,C2:E2,0)-1)

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

網友問到:在 Excel 的工作表中,如果已寫好了公式,如何因應可能增加資料而造成公式要跟著調整?

例如:下圖中要計算B欄清單中的數量總和,目前有16筆資料。如果增加一筆時,如何能不用修改公式,即可正確運算?

Excel-增加資料時不用修改公式(INDIRECT)

如果你使用公式:

儲存格E2:=SUM(B2:B17)

當新增一筆資料時,公式仍維持『=SUM(B2:B17)』,當然結果也不會有所調整。

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

網友想問:在 Excel 中如果有兩個固定間隔時間的清單,該如何找出兩者之間所有時間重疊者?

在下圖中:

條件A:在 9:00~13:00 中每間隔 3 分鐘的時間清單。

條件B:在 9:00~13:00 中每間隔 5 分鐘的時間清單。

想要找出條件A和條件B時間重疊者,如下圖右(D欄)。

Excel-找出兩個時間清單中重疊者(OFFSET,SMALL,ROW,陣列公式)

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

最近開始鼓勵學校同事在處理各類報表時能善用 Excel 的樞紐分析工具,因此提供以下的多個練習範例。

基本表:

Excel-樞紐分析報表格式變化練習

 

《練習一》

試練習呈現下列表格格式,該如何設定?

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

網友問到這類的 Excel 應用問題:參考下圖,在加法、減法、乘法前面以『V』代表勾選,如果依勾選結果列出 X 和 Y 的運算式。

下圖中的例子為勾選『乘法』後,運算式為『X*Y=23*4=92』,該如何撰寫公式?

Excel-挑選不同運算子執行不同運算式(CHOOSE,SUMPRODUCT)

 

【公式設計與解析】

1. 使用輔助儲存格M1

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

在 Excel 中如下圖左的資料表共有 1000 筆,其中包含了類別、項目和數量的資料。其中:類別內容為『甲/乙/丙/丁/戊/己』,項目內容為『子/丑/寅/卯/辰/巳』。要如何產出「各個類別中各個項目前三名數量總和的前四名」(如下圖右)?並且希望類別和項目都依由大到小遞減排序該如何處理?

例如:在下圖右中各類別的前四名是:甲→戊→丙→己。而甲的前三名是:卯→丑→子。

Excel-樞紐分析表應用與練習

這時候『樞紐分析表』工具就可以派上用場了!參考以下的步驟來練習:

1. 選取資料清單中的任一個儲存格,點選[插入/表格]功能表中的「樞紐分析表」。

Excel-樞紐分析表應用與練習

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

網友根據前一篇文章:Excel-計算多欄多列交集的小計(SMPRODUCT),如下圖,如果想要列入小計的欄列交集處包含了文字(例如:A),則原公式會發生錯誤,該如何調整?

例如:人員『寅』和項目『乙』資料的交集是『A,A,59,A,79,A,A,78,21,69』,其中包含了 3 個A是非數,該如何計算數字和?

Excel-計算多欄多列交集的小計(SUM,IFERROR,陣列公式)

 

【公式設計與解析】

儲存格M3:{=SUM(IFERROR(B2:J18*(B1:J1=M2)*(A2:A18=M1),0))}

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

Close

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

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

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

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

reload

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼