EXCEL中如何实现一个整数(如47)按5的倍数去分,但余数不得小于2.5的函数公式得到n*5+？（

Question

余数可以为0，但如：47=8*5+2*3.5；48=9*5+3；49=9*5+4

Answer 1

小于2.5就拿出一个5，再分成2份？

=IF(MOD(A1,5)>=2.5,INT(A1/5)&"*5+"&MOD(A1,5),INT(A1/5)-1&"*5+2*"&(5+MOD(A1,5))/2)

追问

就是这个意思，但余数为0时可取整如：50=10*5该怎么编写

追答

=IF(MOD(A1,5)=0,INT(A1/5)&"*5",IF(MOD(A1,5)>=2.5,INT(A1/5)&"*5+"&MOD(A1,5),INT(A1/5)-1&"*5+2*"&(5+MOD(A1,5))/2))

Answer 2

你这个很奇怪哦 余数不得小于2.5
那50÷5=10.......0 余数是0<2.5 那不是要50÷5=9.......5？？？？

Answer 3

演示效果和代码如下。文件已上传，下载地址：/s/1o6yoYXO Sub tj()Dim d, kySet d = CreateObject("Scripting.Dictionary")Dim i, j, k, r, n, c, fst As Longr = Range("A" & Rows.Count).End(xlUp).RowDim AKArr, MNArrAKArr = Range("A1:K" & r)For i = r - 3 To 1 Step -1 For j = 2 To 11 AKArr(r, j) = --Right(AKArr(i + 1, 1) + AKArr(i + 2, 1) + j - 2, 1) Next j n = 0: c = 0 For k = r - 1 To r - i Step -1 fst = --Right(AKArr(k - (r - i - 1), 1) + AKArr(k - (r - i - 1) + 1, 1), 1) j = --Right(--AKArr(k, 1) + 10 - fst, 1) + 2 If k = r - 1 Then c = j: n = n + 1 Else If j = c Then n = n + 1 Else Exit For End If Next k If d.exists(AKArr(r, c) & "_" & n) Then d(AKArr(r, c) & "_" & n) = d(AKArr(r, c) & "_" & n) + 1 Else d(AKArr(r, c) & "_" & n) = 1 End IfNext iMNArr = Range("M1:N" & d.Count + 1)MNArr(1, 1) = "数字（连续次数）"MNArr(1, 2) = "出现总个数"n = 2For Each ky In d.keys If Split(ky, "_")(1) > 1 Then MNArr(n, 1) = Replace(ky, "_", "连续") & "次" Else MNArr(n, 1) = Left(ky, InStr(ky, "_") - 1) End If MNArr(n, 2) = d(ky) n = n + 1NextColumns("M:M").NumberFormatLocal = "@"Range("M:N").ClearContentsRange("M1:N" & d.Count) = MNArrWith ActiveSheet.Sort .SortFields.Add Key:=Range("M1") .SetRange Range("M1:N" & d.Count) .Header = xlYes .ApplyEnd WithColumns("M:M").TextToColumnsColumns("M:N").AutoFitColumns("M:N").HorizontalAlignment = xlCenterSet d = NothingEnd Sub