Board logo

標題: 如何去找離自己最近的值? [打印本頁]
作者: 藍天麗池    時間: 2024-2-4 21:22     標題: 如何去找離自己最近的值?

[attach]37402[/attach]
版上大大，請問如同附件中的檔案標示，如何寫函數去尋找離自己最近的值去做相減呢?
作者: Andy2483    時間: 2024-2-5 08:34

回復 1# 藍天麗池


    謝謝前輩發表此主題與範例
以下鏈結帖或許適合前輩的需求,但是好像要用VBA
https://forum.twbts.com/viewthre ... &from=favorites
作者: hcm19522    時間: 2024-2-5 11:15

(搜尋編號12384) google網址:https://hcm19522.blogspot.com/
作者: 藍天麗池    時間: 2024-2-5 11:23

回復 3# hcm19522

H大，感謝你的回覆，但我有點看不太懂，能大致說明一下此函數嗎?
作者: 准提部林    時間: 2024-2-5 12:17

本帖最後由 准提部林 於 2024-2-5 12:19 編輯

K2/陣列公式/右拉下拉
=IF(OR($C$1:$F1=C2),G2-MOD(MAX(($C$1:$F1=C2)*(ROW($A$1:$A1)*10^5+$G$1:$J1)),10^5),"")

以"列號"*10^5 + 數字...求出最大值, 再MOD餘數...即是上一個最後出現的數字
作者: hcm19522    時間: 2024-2-5 13:33

回復 4# 藍天麗池


    有另解 較簡單
作者: 藍天麗池    時間: 2024-2-5 14:06

本帖最後由 藍天麗池 於 2024-2-5 14:10 編輯

回復 5# 准提部林


    准大，函數內容有需要更改嗎?我複製貼上後出現錯誤值
作者: Andy2483    時間: 2024-2-5 14:10

謝謝論壇,謝謝各位前輩
後學藉此帖練習字典,請各位前輩指教
執行前:
[attach]37403[/attach]

執行結果:
[attach]37404[/attach]

Option Explicit
Sub test()
Dim i&, Z, xA As Range, T$
Set Z = CreateObject("Scripting.Dictionary")
Set xA = [C2:H5]
For i = 1 To xA.Count
   T = xA(i): xA(i)(1, 9) = IIf(Z(T) = "", "", xA(i)(1, 5) - Z(T)): Z(T) = xA(i)(1, 5)
Next
End Sub
作者: 准提部林    時間: 2024-2-5 15:24

回復 7# 藍天麗池


陣列公式
Ctrl + Alt, Enter 三鍵完成
作者: 藍天麗池    時間: 2024-2-5 15:47

回復 9# 准提部林


    ??，准大，我不太懂你的意思
作者: 藍天麗池    時間: 2024-2-5 16:08

回復 9# 准提部林


    准大，能麻煩你幫我打在範例上面我再研究看看嗎?
作者: 准提部林    時間: 2024-2-6 12:10

回復 10# 藍天麗池

GOOGLE一下//陣列公式的用法吧!!!
[attach]37406[/attach]
作者: 藍天麗池    時間: 2024-2-6 15:10

回復 9# 准提部林

准大，我說不懂是因為查到的跟你說的不太一樣，不知道是打錯，還是有其他用法https://support.microsoft.com/zh-tw/office/%E5%BD%B1%E7%89%87-%E9%99%A3%E5%88%97%E5%85%AC%E5%BC%8F-8ff8257a-b28e-4e81-b4f8-30f793412dfa
作者: 准提部林    時間: 2024-2-6 15:41

回復 13# 藍天麗池

1) 單格的陣列公式, 在一個儲存格編輯公式, 再用三鍵輸入, 然後複製到其它儲存格(通常是:右拉/下拉)
2) 區域的陣列公式, 則要選取一個區域再按三鍵

通常習慣使用單格陣列公式, 適合擴張範圍~~

google應可找到更多解釋, 卻只去看一個...???
作者: 藍天麗池    時間: 2024-2-6 20:59

本帖最後由 藍天麗池 於 2024-2-6 21:10 編輯

回復 14# 准提部林


   准大，我一開始在K2用Ctrl + Alt+ Enter，結果沒反應，後來看到另一篇是用Ctrl + Shift + Enter，結果他就自己呈現右拉跟下拉的功能，因為兩個有點像，所以才會誤解是打錯，或是有其他用法，抱歉
作者: 准提部林    時間: 2024-2-6 21:45

回復 15# 藍天麗池


是我打錯了, 應該是Shift + Ctrl + Enter
作者: 藍天麗池    時間: 2024-2-6 21:45

回復 14# 准提部林


    另外，准大我有些地方不太了解，我有用GPT先查了一下還是不太懂，在跟您請教一下

      1.MOD(MAX(($C$1:$F2=C3)*(ROW($A$1:$A2)*10^5+$G$1:$J2)),10^5) 這邊這串我不太了解它的原理MAX、ROW、MOD、10^5，這些分開我都看得懂，但合在一起我就不懂了，可以麻煩准大幫我說明一下嗎?
      
      2.這個陣列公式輸入完沒問題後，我再去點K2讓她呈現編輯狀態，然後什麼都不改，在按ENTER，之後她會出現#VALUE，為啥{}會消失不見?又為啥自己加{}卻沒辦法有陣列的效果了呢?

      3.也是同上面的問題，假如我有以下的程式碼，在這裡的部分要填入Range("V" & Target.Row).Formula = "{=IF(OR($C$1:$F1=C2),G2-MOD(MAX(($C$1:$F1=C2)*(ROW($A$1:$A1)*10^5+$G$1:$J1)),10^5),"")"}，但又因為{}自己加會沒效果，那我程式碼又應該如何更改呢?

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, [B5:B210000]) Is Nothing Then
        Range("V" & Target.Row).Formula = "這裡"
      
     ElseIf Not Intersect(Target, [C5:C210000]) Is Nothing Then
        Range("W" & Target.Row).Formula = "這裡"
   End If
   Application.EnableEvents = True
        

End Sub

准大抱歉，問題比較多，想說這個論壇有很多厲害的前輩在，想多多學習，還請准大不吝賜教
作者: 准提部林    時間: 2024-2-7 10:06

本帖最後由 准提部林 於 2024-2-7 10:09 編輯

回復 17# 藍天麗池

觀察陣列公式, 得善用"F9", 以下公式逐一輸入至 K5, 在"編輯狀態"下, 按F9看看//
(1) =($C$1:$F4=C5)  按F9,
    可看到//{FALSE,FALSE,FALSE,FALSE;FALSE,TRUE,FALSE,FALSE;TRUE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE}
(2) =($C$1:$F4=C5)*(ROW($A$1:$A4)*10^5+$G$1:$J4)
    //{0,0,0,0;0,200005,0,0;300012,0,0,0;0,0,0,0}

(3) =MAX(($C$1:$F4=C5)*(ROW($A$1:$A4)*10^5+$G$1:$J4)) ... 這時只會出現一個結果//300012...mod尾數12就是結果

在完整公式下,
=IF(OR($C$1:$F4=C5),G5-MOD(MAX(($C$1:$F4=C5)*(ROW($A$1:$A4)*10^5+$G$1:$J4)),10^5),"")
選取紅色字段, 再按F9, 即為(2)的結果
作者: 准提部林    時間: 2024-2-7 10:15

本帖最後由 准提部林 於 2024-2-7 10:24 編輯

回復 17# 藍天麗池


2.這個陣列公式輸入完沒問題後，我再去點K2讓她呈現編輯狀態，
然後什麼都不改，在按ENTER，之後她會出現#VALUE，為啥{}會消失不見?又為啥自己加{}卻沒辦法有陣列的效果了呢?

只要點進儲存格, 不管裡面是空格/文字或公式, 它就達到 CHANGE 效果

問題(3)//點"錄製巨集" >> 點進公式編輯. 三鍵完成 > 停止錄製
公式對儲存格有"相對參照/絕對參照"之分, 在不同位置產生不同參照, 若不熟悉其運作, 使用錄製出來的程式碼較不會有問題~~

剛入EXCEL之門, 還是要自行多多GOOGLE, 或再多花時間看看論壇帖子吧!  
因為回答起來太花時間~~~回答了問題又會問更多///
作者: 藍天麗池    時間: 2024-2-7 11:01

回復 19# 准提部林


    感謝准大的回覆，我再研究看看
作者: 藍天麗池    時間: 2024-2-15 09:07

回復 19# 准提部林


        准大跟你請教一下，上次我根據你教我的第3點去錄製，之後執行程式，出現"無法設定種類range的formulaArray屬性"的錯誤訊息，以下是我的程式碼，是有哪邊出錯嗎?(出現錯誤的地方在Range("V" & Target.Row).FormulaArray這邊)

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, [B5:B210000]) Is Nothing Then
        Range("V" & Target.Row).FormulaArray = "=IF(AND(RC[-20]=R[-1]C[-20],RC[-19]=R[-1]C[-19],RC[-18]=R[-1]C[-18],RC[-17]=R[-1]C[-17],RC[-16]=R[-1]C[-16]),RC[-10]-R[-1]C[-10]+RC[-9]-R[-1]C[-9]+RC[-8]-R[-1]C[-8]+RC[-7]-R[-1]C[-7]+RC[-6]-R[-1]C[-6],IF(OR(R4C2:R[-1]C6=RC[-20]),RC[-10]-MOD(MAX((R4C2:R[-1]C6=RC[-20])*(ROW(R1C1:R[-4]C1)*10^5+R4C12:R[-1]C16)),10^5),0)+IF(OR(R4C2:R[-1]C6=RC[-19]),RC[-9]-MOD(MAX((R4C2:R[-1]C6=RC[-19])*(ROW(R1C1:R[-4]C1)*10^5+R4C12:R[-1]C16)),10^5),0)+IF(OR(R4C2:R[-1]C6=RC[-18]),RC[-8]-MOD(MAX((R4C2:R[-1]C6=RC[-18])*(ROW(R1C1:R[-4]C1)*10^5+R4C12:R[-1]C16)),10^5),0)+IF(OR(R4C2:R[-1]C6=RC[-17]),RC[-7]-MOD(MAX((R4C2:R[-1]C6=RC[-17])*(ROW(R1C1:R[-4]C1)*10^5+R4C12:R[-1]C16)),10^5),0)+IF(OR(R4C2:R[-1]C6=RC[-16]),RC[-6]-MOD(MAX((R4C2:R[-1]C6=RC[-16])*(ROW(R1C1:R[-4]C1)*10^5+R4C12:R[-1]C16)),10^5),0))"
      
     ElseIf Not Intersect(Target, [C5:C210000]) Is Nothing Then
        Range("W" & Target.Row).FormulaArray = "=IF(AND(RC[-16]=R[-1]C[-16],RC[-15]=R[-1]C[-15],RC[-14]=R[-1]C[-14],RC[-13]=R[-1]C[-13],RC[-12]=R[-1]C[-12]),RC[-6]-R[-1]C[-6]+RC[-5]-R[-1]C[-5]+RC[-4]-R[-1]C[-4]+RC[-3]-R[-1]C[-3]+RC[-2]-R[-1]C[-2],IF(OR(R4C7:R[-1]C11=RC[-16]),RC[-6]-MOD(MAX((R4C7:R[-1]C11=RC[-16])*(ROW(R1C1:R[-4]C1)*10^5+R4C17:R[-1]C21)),10^5),0)+IF(OR(R4C7:R[-1]C11=RC[-15]),RC[-5]-MOD(MAX((R4C7:R[-1]C11=RC[-15])*(ROW(R1C1:R[-4]C1)*10^5+R4C17:R[-1]C21)),10^5),0)+IF(OR(R4C7:R[-1]C11=RC[-14]),RC[-4]-MOD(MAX((R4C7:R[-1]C11=RC[-14])*(ROW(R1C1:R[-4]C1)*10^5+R4C17:R[-1]C21)),10^5),0)+IF(OR(R4C7:R[-1]C11=RC[-13]),RC[-3]-MOD(MAX((R4C7:R[-1]C11=RC[-13])*(ROW(R1C1:R[-4]C1)*10^5+R4C17:R[-1]C21)),10^5),0)+IF(OR(R4C7:R[-1]C11=RC[-12]),RC[-2]-MOD(MAX((R4C7:R[-1]C11=RC[-12])*(ROW(R1C1:R[-4]C1)*10^5+R4C17:R[-1]C21)),10^5),0))"
   End If
   Application.EnableEvents = True
        

End Sub
作者: 准提部林    時間: 2024-2-15 10:01

回復 21# 藍天麗池


上傳檔案看看~~
作者: 藍天麗池    時間: 2024-2-15 21:31

本帖最後由 藍天麗池 於 2024-2-15 21:40 編輯

回復 22# 准提部林

[attach]37414[/attach]這個
我自己猜想是裡面多個陣列相加減的原因，是不是要將陣列分開來相加減才不會出現錯誤，我另一個檔案把她拆開就沒事，但是不用VBA的方式直接打在儲存格上卻可以，我就覺得有點奇怪了
作者: 准提部林    時間: 2024-2-17 11:46

本帖最後由 准提部林 於 2024-2-17 11:47 編輯

回復 23# 藍天麗池


檔案測不了//
可用另一方式:利用一儲存格(例如:V4)輸入陣列公式, 執行時用V4去COPY
    If Not Intersect(Target, [B5:B210000]) Is Nothing Then
       Range("V4").Copy Range("V" & Target.Row)
作者: 藍天麗池    時間: 2024-2-17 21:09

回復 24# 准提部林


   我再試試看



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