将数字缩减为一组静态数字 [英] Reduce a number down to a static set of numbers

问题描述

好的，所以我有一个 Google 表格，我需要将一个数字减少到一组特定的 4 个数字.然后计算这些数字出现的次数.

Okay, so I have a Google Sheet that I need to reduce a number down to a specific set of 4 numbers. Then count how many times those numbers occur.

例如:

它分解为的数字:1, 2, 3, 4

无视他们的个人价值观，而不是为了被分解.

Disregard their individual values other than for the purpose of being broken down.

现在取数字17.

Count+Add 1, 2, 3, 4, 1, 2, 3 = 7 r1

取号12

Count+Add 1, 2, 3, 4, 1 =  r1

余数向下舍入

因此，基本上，它将单元格 A1 分解为达到 A1 中数字总数所需的序列数，四舍五入.

So, basically, it breaks down Cell A1 into a count of how many sequences it takes to reach the total of the number in A1, rounddown.

推荐答案

尝试:

=ARRAYFORMULA(COUNTA(IFERROR(QUERY(MMULT(TRANSPOSE((
 ROW(INDIRECT("A1:A"&A2*COUNTA(SPLIT(B2, ","))))<=TRANSPOSE(
 ROW(INDIRECT("A1:A"&A2*COUNTA(SPLIT(B2, ","))))))*TRANSPOSE(
 SPLIT(QUERY(B2&IF(ROW(INDIRECT("A1:A"&A2))<>"", ",")
 ,,9^9), ","))), SIGN(TRANSPOSE(
 SPLIT(QUERY(B2&IF(ROW(INDIRECT("A1:A"&A2))<>"", ",")
 ,,9^9), ",")))), "where Col1 <="&A2, 0))))

这篇关于将数字缩减为一组静态数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！