Предположим, у меня есть таблица, в которой один столбец - "Адрес магазина", а другой - "Продажи". Я хочу иметь таблицу, которая показывает:
Как бы я это сделал? Звучит просто, но я не могу придумать, как это сделать.
Вот подход. Предположим, у вас есть список из 25 магазинов в ячейках B2:B26 и их продажи в ячейках C2:C26 .
Начните с сортировки объединенного диапазона B2:C26 по продажам (столбец B) в порядке убывания.
Добавьте третий столбец для совокупного процента продаж для отсортированного списка магазинов. Формула для первой ячейки, D2 , будет иметь вид:
=SUM($C$2:C2)/SUM($C$2:$C$26)
Перетащите формулу вниз через ячейку D26 , которая должна равняться 100 процентам. Результирующая таблица может выглядеть примерно так:
Таблица результатов также будет иметь два столбца, один для указанных вами точек останова - 5%, 10%, 15% и 20% - и один для формул, которые будут подсчитывать количество магазинов.
Обратите внимание, что если у вас есть небольшое количество магазинов, например, 25 в этом примере, вы можете просто просмотреть список, чтобы получить количество и вызвать его, чтобы выйти.T Преимущество наличия формул для подсчета состоит в том, что они облегчат получение подсчетов, если у вас много магазинов или много точек останова.
Попытка определить точную формулу для подсчета магазинов поднимает интересный вопрос. Как вы хотите относиться к ситуации, когда совокупные продажи не попадают аккуратно в указанные вами корзины? Если на один магазин приходится 4,9% продаж, кажется разумным сказать, что на 1 магазин пришлось 5% продаж. Но что, если на один магазин приходится только 3,6% продаж, а на второй магазин приходится 3,2% продаж. Так, это был один магазин или два магазина, ответственные за 5% продаж?
Для подсчета 1 магазина в этой ситуации вы должны использовать следующую формулу (при условии, что список процентных точек останова в таблице результатов начался в ячейке F2):
=COUNTIF($D$2:$D$21,"<="&F2)
При копировании этой формулы получается следующая таблица результатов для данных примера:
Если вы хотите получить (гипотетический) счетчик из двух магазинов, формула для таблицы результатов будет такой:
=COUNTIF($D$2:$D$21,"<="&I2)+IF(ISERROR(VLOOKUP(I2,$D$2:$D$21,1,0)),1,0)
К этому добавлен термин, который подсчитывает дополнительный магазин, необходимый для доведения совокупных продаж до точного процента останова.
Вот пользовательская функция VBA, которая достигнет того же самого. Это делает следующее:
1) Берет вертикальный диапазон чисел и копирует их в массив. В зависимости от аргумента "знак" он либо отбрасывает негативы, либо отбрасывает позитивы, либо принимает позитивы и негативы.
2) Получает другую функцию для сортировки массива от наименьшего к наибольшему (не предусмотрено).
3) Итерирует по всему массиву от самого большого до самого маленького, суммируя каждый элемент, чтобы найти, сколько нужно, чтобы составить X%.
Внимание: из-за округления, которое я использую, я подозреваю, что оно не сработает при работе с очень маленькими числами.
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
Не знаю точно, о чем вы просите, но вы можете определить процент, который каждый магазин вносит в общую продажу, взяв продажи каждого магазина и разделив их на общие продажи всех магазинов. Пример формулы может выглядеть так:
=B2/SUM($B$2:$B10)
Это предполагает, что ваши продажи находятся в столбце B и доходят до строки 10. $ Позволит вам перетащить формулу вниз без изменения итоговых сумм. Либо отформатируйте это число в процентах, либо умножьте на 100, чтобы получить эквивалент в процентах. Отсюда вы можете выбрать, какие из них соответствуют вашим 5%, 10% и т.д. Критериям.