Board logo

標題: [發問] 統計項含有文字 [打印本頁]

作者: johnny1680    時間: 2021-10-7 03:26     標題: 統計項含有文字

[attach]34168[/attach]
想將B6:AE6的答案顯示出"補貨中";如同B8:AE8一樣。
請問:B6的Excel 2003公式要如何修改?
謝謝各位 !

作者: samwang    時間: 2021-10-7 10:20

回復 1# johnny1680

請問空白 等 0 嗎?
作者: johnny1680    時間: 2021-10-7 10:56

回復 2# samwang
空白就是空白,不顯示0。
謝謝!
作者: samwang    時間: 2021-10-7 10:59

回復 1# johnny1680

只要列1、2的資料新增/變更,列11、12品項/數量監控就會即時更新,請測試看看,謝謝
作者: johnny1680    時間: 2021-10-7 11:26

回復 4# samwang
我無權下載檔案。
謝謝!
作者: samwang    時間: 2021-10-7 11:35

回復 5# johnny1680

一般模組:
Sub 更新()
Dim Arr, xD, a, a2, n%
Arr = Range([b2], Cells(1, Columns.Count).End(1))
For j = 1 To UBound(Arr, 2)
    If Arr(2, j) = "" Then Arr(2, j) = "資料無"
Next
For j = 1 To UBound(Arr, 2)
    For j2 = j + 1 To UBound(Arr, 2)
        If Arr(2, j) > Arr(2, j2) Then
            n = n + 1: a = Arr(1, j): a2 = Arr(2, j)
            Arr(1, j) = Arr(1, j2): Arr(1, j2) = a
            Arr(2, j) = Arr(2, j2): Arr(2, j2) = a2
        End If
    Next
Next
Range([b12], Cells(11, Columns.Count).End(1)).ClearContents
Range("b11").Resize(2, UBound(Arr, 2)) = Arr
End Sub

工作表1
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
    If .Row = 1 Or .Row = 2 Then Call 更新
End With
End Sub
作者: johnny1680    時間: 2021-10-7 11:48

回復 6# samwang
不會程式。
但還是謝謝您^^

能指導公式嗎?
謝謝!
作者: hcm19522    時間: 2021-10-7 11:55

本帖最後由 hcm19522 於 2021-10-7 14:48 編輯

B5 右拉 下拉 {=IFERROR(INDEX(1:1,RIGHT(SMALL(IF($B$2:$AE$2<>"",IF($B$2:$AE$2="補貨中",99,$B$2:$AE$2)/1%+COLUMN($B:$AE)),COLUMN(A1)),2)),"")

https://blog.xuite.net/hcm19522/twblog/590063989
作者: johnny1680    時間: 2021-10-7 13:03

回復 8# hcm19522
格式不對
IFERROR公式我不會改^^"
作者: johnny1680    時間: 2021-10-7 17:48

本帖最後由 johnny1680 於 2021-10-7 17:50 編輯

B6
=IF(ISERROR(SMALL(IF($B2:$AE2>0,$B2:$AE2),COLUMN(A1))),"",IF(ISERROR(SMALL(IF($B2:$AE2="",$B2:$AE2),COLUMN(A1))),"補貨中",SMALL(IF($B2:$AE2>0,$B2:$AE2),COLUMN(A1))))
只能顯示4個數字   1,2,3,5  和  4個"補貨中"

請問 : B6的公式要如何修正?
才顯示出正確答案(如B8:AE8)。
謝謝 !

作者: ML089    時間: 2021-10-7 18:21

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    時間: 2021-10-7 19:10

本帖最後由 johnny1680 於 2021-10-7 19:12 編輯

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

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

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

本帖最後由 johnny1680 於 2021-10-7 19:32 編輯

回復 11# ML089
希望B6的答案能如B8:AE8一樣(有縮排)
謝謝您 !
作者: ML089    時間: 2021-10-7 20:04

本帖最後由 ML089 於 2021-10-7 20:07 編輯

回復 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    時間: 2021-10-7 20:23

回復 14# ML089
版大您也太強了!!!
一式解決2個需求。
謝謝你的幫忙!
作者: ML089    時間: 2021-10-8 10:19

回復 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    時間: 2021-10-8 12:28

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




歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)