贊助廠商

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

搜尋本部落格文章資料

(網友提問)參考下圖,如何在一個資料表中,指定欄、列數值,在區間範圍中查詢?

例如:欄數值55是在46-65之間,列數值7在6-8之間,欄列交叉對應得到11。

Excel-在資料表中指定欄、列數值在區間範圍中查詢(MATCH,INDEX,常數陣列)


【公式設計與解析】

儲存格H3:=INDEX(B2:E5,MATCH(H2,{1,3,6,9},1),MATCH(H1,{0,21,46,66},1))

(1) MATCH(H2,{1,3,6,9},1)

因為列的內容是以區間表示,所以我們在 MATCH 函數中用常數陣列,取每個範圍的第一個數值當為陣列項目,再以參數『1』取「小於」(或是參數『-1』取「大於」)。比對後得到一個位置,傳回一個數值。

image

(2) MATCH(H1,{0,21,46,66},1)

同第(1)式,在 MATCH 函數中用陣列常數,取每個範圍的第一個數值當為陣列項目,再以參數『1』取「小於」(或是參數『-1』取「大於」)。比對後得到一個位置,傳回一個數值。

(3) INDEX(B2:E5,第(1)式,第(2)式)

INDEX 函數中以第(1)式和第(2)式的內容代入,以欄列交叉的位置傳回對應的數值。


文章標籤

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

(網友提問)在 Excel 中如果要使用公式以起始字串來篩選清單中的項目,該如何處理?

參考下圖,要以起始字串(本例:A135790)在一個號碼的清單中篩選。

Excel-使用公式以起始字串來篩選(OFFSET,ROW,LEN)

 

【手動篩選】

如果你使用篩選工具,就可以使用「文字篩選」下的「開始於」功能來篩選。

Excel-使用公式以起始字串來篩選(OFFSET,ROW,LEN)

輸入開始的字串:(本例為A135790)

Excel-使用公式以起始字串來篩選(OFFSET,ROW,LEN)

隨之得到篩選結果:

Excel-使用公式以起始字串來篩選(OFFSET,ROW,LEN)

 

【公式設計與解析】

如何以公式來完成 ?

先選取A1:A35,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:號碼。

儲存格D2:{=IFERROR(OFFSET($A$1,SMALL(IF(LEFT(號碼,LEN($C$2))=$C$2,
ROW(號碼),""),ROW(1:1))-1,0),"")}

複製儲存格D2,貼至儲存格D2:D14。

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

Excel-使用公式以起始字串來篩選(OFFSET,ROW,LEN)

(1) IF(LEFT(號碼,LEN($C$2))=$C$2,ROW(號碼),"")

先以LEN 函數計算儲存格C2的文字長度,然後在陣列公式中利用 LEFT 函數判斷所有號碼清單由左邊取和儲存格C2相同的的文字長度,如果和儲存格C2相同者傳回其列號(利用 ROW 函數),否則傳回空字串。

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

利用 SMALL 函數於第(1)式的傳回值由小至大取出列號。公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

(3) OFFSET($A$1,第(2)式-1,0)

將第(2)式傳回的列號代入 OFFSET 函數取得對應的儲存格內容。

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

公式可能傳回錯誤訊息,藉由 IFERROR 函數將錯誤訊息置換為空字串。

文章標籤

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

過農曆年就是要返鄉和家日團聚!為了怕塞車之苦,所以每年都提早請休假,一邊玩一邊返鄉。今年的重要行程如下:(部分景點被省略)

https://www.google.com.tw/maps/dir/星巴克嘉義民雄門市/萬巒彩繪吊橋/車城+福安宮/星砂灣/墾丁凱撒大飯店/船帆石/砂島/臺灣最南點碑/龍磐公園/StepUp客製化夾腳拖/美濃民俗村/漁光島/大降目園區

過年前到過年期間,一連幾天風和日麗,也表現在隨手拍攝的照片上。

【嘉義民雄星巴克】路過(刻意)!喝了再上!

過年-南台灣旅遊

【萬巒彩繪吊橋】附近吃萬巒豬腳。

過年-南台灣旅遊 過年-南台灣旅遊

【車城福安宮】還有吃綠豆蒜!

過年-南台灣旅遊

【星沙灣】

過年-南台灣旅遊

過年-南台灣旅遊

【墾丁凱撒飯店沙灘早上】

過年-南台灣旅遊

過年-南台灣旅遊

過年-南台灣旅遊

【船帆石】

過年-南台灣旅遊

過年-南台灣旅遊

【砂島】砂灘現在只能遠觀,無法就近了!

過年-南台灣旅遊

過年-南台灣旅遊

過年-南台灣旅遊 過年-南台灣旅遊(2018)

【臺灣最南點碑】這個繳費機肯定是全臺灣最的南的繳費機了!(參觀的人太多,所以沒有拍到無人的最南點碑)

過年-南台灣旅遊

【龍磐公園】看出去的景很美!但是風超大!

過年-南台灣旅遊

【港口吊橋】收費20元。

過年-南台灣旅遊

【Step Up 客製化夾腳拖】

不只可以客製(轉動輪盤即可配對鞋底和鞋帶),還可動手完成自己的設計,不滿意也可以重來。鞋帶斷了也能免費更換。

過年-南台灣旅遊

過年-南台灣旅遊

過年-南台灣旅遊 過年-南台灣旅遊

【有點霾害+高雄的落日】

過年-南台灣旅遊

【美濃民俗村】附近吃粄條!

過年-南台灣旅遊 過年-南台灣旅遊

過年-南台灣旅遊 過年-南台灣旅遊

【漁光島】

過年-南台灣旅遊

過年-南台灣旅遊 過年-南台灣旅遊

【大降目園區】有武德殿和新化老街等。

過年-南台灣旅遊

過年-南台灣旅遊 過年-南台灣旅遊

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼