ML089

B5:B6 區域陣列公式，數量由大至小排列，(小至大 讓你練習)
=INDEX($A:$AE,{1;2},MOD(LARGE(TEXT($B2:$AE2,"0;;0;!0")*10^4+COLUMN($B:$AE),COLUMN(A1)),10^4))

1 先複製公式
2 選擇 B5:B6 儲存格
3 編輯列貼上公式
4 鼠標位於編輯列，用三鍵(CTRL+SHIFT+ENTER)輸入公式

johnny1680

回復 11# ML089
第1次見到"區域陣列"公式
感謝指導。

現在我想將LARGE改為SMALL
圈選B5:B6，在編輯列將LARGE改為SMALL後，同時按3鍵，卻跑出"你不能只改變1個陣列的一部分"的警語；
我將編輯列公式移除或將檔案關閉都不行，不知道怎麼辦?
等發完這篇文稿，我強制關機看看^^"

還是請您指導B6的單獨公式，且第2列=""的儲存格，第6列也=""(不顯示0)
答案如B8:AE8
因為第2列=""的儲存格=第1列"貨品項"已停售，所以BOSS不想顯示0(她說 : 顯示0就=要緊急補貨了)。
謝謝您 !

johnny1680

回復 11# ML089
希望B6的答案能如B8:AE8一樣(有縮排)
謝謝您 !

ML089

回復 13# johnny1680

B5 陣列公式 (三鍵輸入)
=INDEX($A:$AE,ROW(A1),MOD(SMALL(TEXT($B$2:$AE$2,"0;;\9\E\5;\8\E\5")*10^4+COLUMN($B:$AE),COLUMN(A1)),10^4))&""
右拉下拉，文字格式


排列順序：數量 由小至大，補貨中，空格項目

johnny1680

回復 14# ML089
版大您也太強了!!!
一式解決2個需求。
謝謝你的幫忙!

ML089

回復 12# johnny1680

現在我想將LARGE改為SMALL
圈選B5:B6，在編輯列將LARGE改為SMALL後，同時按3鍵，卻跑出"你不能只改變1個陣列的一部分"的警語；
我將編輯列公式移除或將檔案關閉都不行，不知道怎麼辦?
等發完這篇文稿，我強制關機看看^^"
   

上次忘記回覆這問題
區域陣列公式修正確實很麻煩，原先是單格陣列公式，不能直接套上去修改，需先刪除舊公式，才能重新上區域陣列公式。

區域陣列公式以大量節省計算資源，大量數據時才體會。
例如 10萬筆資料取最小前10個，
  SMALL(10萬筆資料，row(a10)) 右拉10格，表示這10個都需要處理 10萬筆資料排序做10次
  SMALL(10萬筆資料，row(1:10)) 直接套在10格，表示這10個只要處理 10萬筆資料排序做1次，一次直接取出前10組資料，豈不是快很多。

johnny1680

回復 16# ML089
謝謝您的詳細說明，
受益良多b^_^d
感恩~~~~~

Andy2483

回復 1# johnny1680


    謝謝前輩發表此主題與範例,謝謝各位前輩指導
今天後學藉此帖學習到很多知識,練習到很多方法,以下學習心得請各位前輩指教

執行前:

下載 (59.15 KB)

2023-3-17 11:48

執行結果:

下載 (75.94 KB)

2023-3-17 11:48

Option Explicit
Sub TEST_1()
Dim Brr, C%, j%, i&, xR As Range
'↑宣告變數:Brr是通用型變數,(C,j)是短整數變數,
'i是長整數變數,xR是儲存格變數
C = Cells(1, Columns.Count).End(xlToLeft).Column
'↑令C這短整數變數是 第1列最右側有內容儲存格欄號
If C <= 2 Then Exit Sub
'↑如果C變數<=2!就結束程式執行
Set xR = Range([A2], Cells(1, C))
'↑令xR這儲存格變數是 [A2]到(第1列第C變數欄儲存格),
'令xR這儲存格變數是這範圍儲存格(物件變數)
Brr = xR
'↑令Brr這通用型變數是 二維陣列,以xR變數(儲存格值)帶入
For i = 1 To UBound(Brr)
'↑設順迴圈!i從1到Brr陣列縱向最大索引號列
   For j = 1 To UBound(Brr, 2)
   '↑設順迴圈!j從1到Brr陣列橫向最大索引號欄
      Brr(i, j) = Format(Brr(i, j), "000|")
      '↑令i迴圈列j迴圈欄Brr陣列值是指定文字格式的字串
      '指定文字格式的字串:若原是數字!就補0成3碼連接"|"
   Next
Next
With xR.Offset(4, 0)
'↑以下是關於xR變數向下偏移4列範圍儲存格的程序
   .Value = Brr
   '↑令該範圍儲存格值是Brr陣列值
   .Offset(0, 1).Sort KEY1:=.Item(2, 1), _
   Order1:=1, Key2:=.Item(1), Order2:=1, _
   Orientation:=xlLeftToRight
   '↑令該範圍儲存格向右偏移1欄範圍儲存格,
   '做兩層次橫向順排序:
   '第1層次以該範圍內下方2列第1欄儲存格為排序基準,
   '第2層次以該範圍內第1格儲存格為排序基準
   .Replace "|*", "", Lookat:=xlPart
   '↑令該範圍儲存格值做文字置換:
   '將"|"符號(含)右側的所有文字 置換為空字元
End With
End Sub