贊助廠商

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

搜尋本部落格文章資料

網友想要在一個清單中,如下圖是多個 IC 接腳及名稱的清單,如何在挑選一個接腳後,列出含有這個接腳的所有 IC?這類問題已多次有網友詢問過,顯見很多人需要這類問題的解答,所以很樂意再說明一次。

Excel-在資料清單中查詢符合條件者(陣列公式,SMALL,OFFSET,ROW) Excel-在資料清單中查詢符合條件者(陣列公式,SMALL,OFFSET,ROW)

 

(1) 使用『篩選』工具

在資料清單中啟動『篩選』工具,只要勾選想要的接腳名稱,即可篩選出IC的名稱。而篩選結果通常要在其他地方使用,因此,只要複製篩選結果,例如複製儲存格B2:B8,然後在另一個位置貼上,其實只有3個項目會被複製,其中被隱藏的儲存格不會被複製。

這個方法在操作上很簡單,但是如果清單內容改變了,你都要重新篩選一次。

Excel-在資料清單中查詢符合條件者(陣列公式,SMALL,OFFSET,ROW)

 

(2) 使用『樞紐分析』工具

你也可以插入一個樞紐表,一次列出所有接腳相關的 IC 名稱,設定如下:

Excel-在資料清單中查詢符合條件者(陣列公式,SMALL,OFFSET,ROW)

接著,在接腳欄位中按一下「作用中欄/欄位設定」。

Excel-在資料清單中查詢符合條件者(陣列公式,SMALL,OFFSET,ROW)

在[小計與篩選]標籤下,設定小計為:無。

Excel-在資料清單中查詢符合條件者(陣列公式,SMALL,OFFSET,ROW)

在[版面配置與列印]標籤下,選取「以列表方式顯示項目標籤」和勾選「重覆項目標籤」選項。

Excel-在資料清單中查詢符合條件者(陣列公式,SMALL,OFFSET,ROW)

這個方法在操作上也很簡單,但是如果清單內容改變了,你仍然要重新整理才能得到正確結果。

 

(3) 設計公式

Excel-在資料清單中查詢符合條件者(陣列公式,SMALL,OFFSET,ROW)

儲存格B2:{=IFERROR(OFFSET($B$1,SMALL(IF($A$2:$A$9=$D$2,
ROW($A$2:$A$9),FALSE),ROW(1:1)),0),"")}

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

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

(1) IF($A$2:$A$9=$D$2,ROW($A$2:$A$9),FALSE)

在陣列公式中,判斷儲存格A2:A9中和儲存格D2是否相符。若成立,則傳回一個列號(例如儲存格A4會傳回4);若不成立,則會傳回 FALSE

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

找出第(1)式中所傳回的所有列號中最小的列號。當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...,可以找出第2小、第 3 小、... 的列號。

(3) OFFSET($B$1,第(2)式,0)

將第(2)式傳回的列號代入 OFFSET 函數中,取得對應的B欄內容。

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

因為公式由儲存格B2向下複製,所以在第(2)式的 SMALL 函數可能傳回錯誤訊息。例如:在儲存格D7中要找尋第4小的列號,因為GND只有三個,所以會傳回錯誤。因此,使用 IFERROR 函數將錯誤訊息顯示為空字串("")。

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

有網友問到,如何根據 Excel 的時間清單,來計算這些時間的平均值?如下圖,時間清單可能由數字構成時分秒,也可能由文字構成時分秒,如何來計算其平均時間?

Excel-計算數個時間的平均值(AVERAGE,LEFT,MID,RIGHT,陣列公式)

 

【公式設計與解析】

1. 時間為數值格式

儲存格B2:=AVERAGE(A2:A18)

如果時間清單中的時間是數值格式,則可以直接以 AVERAGE 函數求得其平均時間,結果也是一個數值。

 

2. 時間為文字格式

儲存格E2:{=(SUM(VALUE(LEFT(D2:D18,2)))*3600+SUM(VALUE(MID(D2:D18,
4,2)))*60+SUM(VALUE(RIGHT(D2:D18,2))))/(24*60*60)/COUNTA(D2:D18)}

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

(1) 時換算秒數:SUM(VALUE(LEFT(D2:D18,2)))*3600

在陣列公式中,使用 LEFT 函數取出每個時間的第1,2碼,計算總和後再乘以3600(一小時有3600秒)。(VALUE 函數用以將文字轉成)

(2) 分換算秒數:SUM(VALUE(MID(D2:D18,4,2)))*60

在陣列公式中,使用 MID 函數取出每個時間的第4,5碼,計算總和後再乘以60(一分鐘有60秒)。

(3) 計算秒數:SUM(VALUE(RIGHT(D2:D18,2)))

在陣列公式中,使用 RIGHT 函數取出每個時間的第7,8碼,計算總和。

(4) 計算平均,公式=(1)+(2)+(3)/(24*60*60)/COUNTA(D2:D18)

(1)+(2)+(3)/(24*60*60):將時間總和換算為秒數。

然後,再除以COUNTA(D2:D18)(計算個數),求得平均值。

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼