Como descobrir quantas lojas representam 5% das vendas

Question

Suponha que eu tenha uma tabela em que uma coluna seja "Shop Address" e outra coluna seja "Sales". Eu quero ter uma tabela que mostre:

• 5% das vendas totais são geradas pelas principais lojas X1
• 10% das vendas totais são geradas pelas principais lojas X2
• 15% do total de vendas são gerados pelas principais lojas X3
• 20% das vendas totais são geradas pelas principais lojas X4 ... etc

Como eu faria isso? Parece simples, mas não consigo pensar em como fazer isso.

    

Answer 1

Aqui está uma abordagem. Vamos supor que você tenha uma lista de 25 lojas nas células B2:B26 e suas vendas nas células C2:C26 .

Comece classificando o intervalo combinado B2:C26 por vendas (coluna B) em ordem decrescente.

Adicione uma terceira coluna para o percentual acumulado de vendas para a lista classificada de lojas. A fórmula da primeira célula, D2 , será:

  =SUM($C$2:C2)/SUM($C$2:$C$26)

Arraste a fórmula para baixo até a célula D26 , que deve ser igual a 100%. A tabela resultante pode ser algo assim:

Atabeladeresultadostambémteráduascolunas,umaparaospontosdequebraquevocêespecificou-5%,10%,15%e20%-eumaparaasfórmulasquecontarãoonúmerodelojas.

Notequesevocêtiverumpequenonúmerodelojas,comoas25nesteexemplo,vocêpoderiaapenasolharalistaparaobterascontagenseencerrar.Obenefíciodeterfórmulasfazacontageméqueelasfarãoéfácilobterascontagenssevocêtivermuitaslojasoumuitospontosdeinterrupção.

Tentardefinirumafórmulaexataparacontaraslojaslevantaumaquestãointeressante.Comovocêdesejatratarasituaçãoemqueasvendasacumuladasnãoseencaixamnascaixasespecificadas?Seumalojarespondepor4,9%dasvendas,parecerazoáveldizerqueumalojaproduziu5%dasvendas.Maseseaúnicalojarespondesseporapenas3,6%dasvendas,comumasegundalojarespondendopor3,2%dasvendas?Então,eraumalojaouduaslojasresponsáveispor5%dasvendas?

Paraacontagemde1lojanessasituação,vocêusariaaseguintefórmula(supondoquealistadepontosdeinterrupçãodeporcentagemdatabeladeresultadostenhacomeçadonacélulaF2):

=COUNTIF($D$2:$D$21,"<="&F2)

Copiar esta fórmula produz a seguinte tabela de resultados para os dados de exemplo:

Sevocêquisesseobteracontagemdedoisarmazenamentos(hipotética),afórmuladatabeladeresultadosseria:

=COUNTIF($D$2:$D$21,"<="&I2)+IF(ISERROR(VLOOKUP(I2,$D$2:$D$21,1,0)),1,0)

Isso tem um prazo adicional que conta a loja adicional necessária para levar as vendas acumuladas a exatamente o ponto percentual de ponto de interrupção.

    

Answer 2

Não sei exatamente o que você está pedindo, mas você pode determinar a porcentagem que cada loja contribui para a venda total, tomando as vendas de cada loja e dividindo-a pelo total de vendas de todas as lojas. Uma fórmula de amostra pode ter esta aparência:

=B2/SUM($B$2:$B10)

Isso pressupõe que suas vendas estão na coluna B e vão até a linha 10. O $ permitirá que você arraste a fórmula para baixo sem alterar a soma total. Formate esse número como uma porcentagem ou multiplique por 100 para obter o equivalente percentual. A partir daqui, você pode escolher quais satisfazem seus critérios de 5%, 10%, etc.

    

Answer 3

Aqui está uma função definida pelo usuário do VBA que alcançará a mesma coisa. Ele faz o seguinte:

1) Obtém um intervalo vertical de números e os copia em um array. Dependendo do argumento "assinar", ele descartará pontos negativos, descartará pontos positivos ou obterá pontos positivos e negativos.

2) Obtém outra função para classificar o array do menor para o maior (não fornecido).

3) Itera a matriz da maior para a menor enquanto soma cada elemento para descobrir quantos são necessários para fazer X%.

Cuidado: Por causa do arredondamento que uso, suspeito que não funcionaria quando lidamos com números muito pequenos.

Function ProjectsInPercent(FromRange As Range, TopPercent As Double, sign As Integer) As Double
    Dim values()  As Variant 'array to hold values
    Dim row As Integer
    Dim sum As Double
    Dim percentOfSum As Double
    Dim tmpVal As Double
    sum = 0

ReDim values(1 To FromRange.rows.Count)

If (FromRange.rows.Count > UBound(values, 1)) Then
    ProjectsInPercent = "Too many rows in range"
ElseIf (TopPercent < 0 Or TopPercent > 1) Then
    ProjectsInPercent = "Percentile should be between 0 and 1"
End If

For row = 1 To UBound(values, 1)
    tmpVal = FromRange.Cells(row, 1).value
    If IsNumeric(tmpVal) Then
        If sign > 0 Then
            tmpVal = IIf(tmpVal > 0, tmpVal, 0)
        ElseIf sign < 0 Then
            tmpVal = IIf(tmpVal < 0, -tmpVal, 0)
        End If
        sum = sum + tmpVal
        values(row) = tmpVal
    Else
        values(row) = 0
    End If
Next row

Call QuickSort(values, 1, UBound(values, 1))

percentOfSum = sum * TopPercent
sum = 0
For row = 1 To UBound(values, 1)
    tmpVal = values(UBound(values, 1) - row + 1)
    If Round(sum + tmpVal, 1) >= Round(percentOfSum, 1) Then
        ProjectsInPercent = row + (percentOfSum - sum - tmpVal) / tmpVal
        Exit For
    End If
    sum = sum + tmpVal
Next row

End Function