學不完．教不停．用不盡

根據先前的這一篇文章：

Excel-找出同一欄最後一個指定數字對應的日期(SUMPRODUCT,LARGE)

參考下圖，如果將數值範圍擴大為多欄，則原來公式要如何變更？

【公式設計與解析】

在 Excel 的工作表中，常見要把左右兩個儲存格的內容集合在一個儲存格並且上下分列，或是把一個儲存格並且上下分列的內容改成左右分欄置放，該如何處理？

【公式設計與解析】

1. 合併2筆資料上下分列

儲存格F2：=B2&CHAR(10)&C2

（網友提問）在 Excel 的工作表中有一個日期清單，並且每個日期對應一個數值。如何找出同一欄最後一個指定數字對應的日期？

參考下圖，例如：最後一個一個『8』所對應的日期是2017/10/19。

【公式設計與解析】

選取儲存格B1:B25，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：數值。

網友根據前一篇文章：Excel-無條件進位至5的倍數(INT,MOD)，提供了其他做的做法。

前一篇文章比較像是利用運算思維的角度，以 INT 和 MOD 函數來解決問題。如果你要使用現有的函數來處理也是可以。

【公式設計與解析】

原公式：

（網友提問）在 Excel 的工作表中有個數值清單（參考下圖左），如何根據數值清單計算累計前幾個的總和，及排名前幾個的總和？

【公式設計與解析】

選取儲存格A1:D24，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：序號、數值、累計、排名。

1. 累計前n個總和

（網友提問）在 Excel 的工作表中有一個數值清單，如何將數值取無條件進位至5的倍數？（參考下圖）

【公式設計與解析】

儲存格B2：

=INT(A2/10)*10+(MOD(A2,10)>5)*10+(MOD(A2,10)>0)*(MOD(A2,10)<=5)*5

（網友提問）在 Excel 中有一個每日上班和下班的打卡記錄，以每30分採計（未達30分不採計），如何根據這些數據計算薪資總額？

參考下圖，上班時刻10:11、下班時刻10:57，以每1分計是46分，若以每30分計是1。

【公式設計與解析】

儲存格I2：=SUMPRODUCT(INT((C2:C32-B2:B32)*24*60/30))*I1

（網友提問）在 Excel 的工作表中有一個原始日期清單，其格式為105.07.03，如何轉換為1050703，並且能計算二個日期相差的日數？

參考下圖，利用轉換後的日期，計算上下兩個儲存格相差的日數。

【公式設計與解析】

1. 轉換日期格式

（網友提問）在 Excel 的工作表有下圖(上)的資料表，如何轉換為下圖(下)的資料表。

參考下圖，在每個姓名列中，要將有數字的儲存格，對照日期名稱後依序列在同一列中。

【公式設計解析】

儲存格B13：{=IFERROR(OFFSET($B$1,0,SMALL(IF($B2:$K2<>"",

（讀者提問）在 Excel 中如果想要由一個工作表超連結到另一個工作表的指定儲存格，該如何處理？

如下圖，在「引用」工作表中要直接超連結到「來源」工作表中的對應儲存格，公式如何處理？

【公式設計與解析】

儲存格A1：=HYPERLINK("#來源!"&ADDRESS(ROW(1:1),COLUMN(A:A)),

（網友提問）在 Excel 的工作表中，假如要依分組人數給予分組編號，該如何處理？

參考下圖，第一列是分組數，要自動產生分組號碼。

【公式設計與解析】

儲存格C2：=INT((ROW(1:1)-1)/C$1)+1

（原文有誤，已更新）

在 Excel 中有一個日期的清單，當要將日期加２天並且要跳過星期六、日，該如何處理？例如：

星期四加２天，應是星期六，則要調整為星期一。

星期五加２天，應是星期日，則要調整為星期二。

星期六加２天，應是星期一，則要調整為星期二。

（網友提問）如何在 Excel 的工作中，利用輸入年和月後，自動產生該月的月曆（標示星期幾），並且能將星期六、日加以標示，該如何處理？

參考下圖，當年輸入2019、月輸入10後，自動產生該月各日是星期幾，並且將所有的星期六日儲存格用不同色彩標示。

【公式設計與解析】

（讀者提問）在 Excel 的工作表中有個材質和價格的清單，其中的價格依最小值和最大值之間的範圍而定。如何依數值位於的範圍內對應的材質以求得價格？

以下圖為例，材質「ZD」的數值17是位於 6.1～25.0 之間，所以對應的價格為 260。

【公式設計與解析】

選取儲存格A1:D13，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：

（網友提問）在 Excel 的工作表中有一個產品編號和特賣價的對照表，如果選取一個「抽籤折扣（共有三種：95折、90折、85折）」，再依購買數量來計算結帳價，該如何處理？

【公式設計與解析】

抽籤折扣欄位的下拉式清單是利用資料驗證功能來製作：

在 Excel 的工作表中有一個數值清單，如何求正數最小值和負數最大值？

【公式設計與解析】

選取儲存格A1:A21，按 Ctrl+Shift+F3 鍵，定義名稱：數值。

1. 正數最小值

（網友提問）參考下圖的 Excel 工作表，如何依開始天數、天數（下圖左），將數量平均分配至各天之中（下圖右）？

【公式設計與解析】

儲存格F2：=IF(($B2<=F$1)*($B2+$C2>F$1),$D2/$C2,"")

複製儲存格F2，貼至儲存格N11。

利用雙條件：($B2<=F$1)*($B2+$C2>F$1)，判斷儲存格F1的日期，是否介於甲的開始日期和天數之內的日期。

本例判斷：(5/1日<=5/3日)*(5/1日+2日>5/3日)

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

（網友提問）當你在 Excel 的工作表中對一個成績清冊的某個欄位排序時，如果想要只對某些符合條件的資料來排序，該如何處理？

如下圖，若本例設定只顯示英文超過 88 者的排名，並且標示前三名。

【公式設計與解析】

先選取儲存格B1:E26，按Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：國文、英文、數學、總分。

Excel 的圖表工具是實用的好幫手！在 Excel 2016 中又新增了六種實用的圖表工具。最近有試著來練習看看。

1. 矩形式樹狀結構圖

矩形會依數值大小而定，同一類別給予相同色彩，總和大者在左邊，單項大者在上方。

當你建立了一個 Word 文件，如果想要兼顧可以輸入內容，又不想某些文字內容被修改，要如何處理？

參考下圖，本文件只允許黃色區域可以輸入／修改資料，其他區域不能被編輯。

要解決這個問題，必須要藉助文件「限制編輯」功能！

首先，你得先開啟「開發人員」功能表。

接著，在文件中選取你允許使用者輸入資料的位置。