将数字减少为一组静态数字 [英] 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屋！