|
Как распределить сумму пропорционально между др. ячейками |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Рассмотрим равномерное непрерывное распределение. Вычислим математическое ожидание и дисперсию. Сгенерируем случайные значения с помощью функции MS EXCEL СЛЧИС() и надстройки Пакет Анализа, произведем оценку среднего значения и стандартного отклонения.
Равномерно распределенная на отрезке [a; b] случайная величина имеет плотность распределения (вероятности) : 
Функция распределения определяется следующим образом:
Равномерное непрерывное распределение (англ. Continuous uniform d istribution или Rectangular distribution ) часто встречается на практике.
Пример1. Например, известно, что гейзер извергается каждые 50 минут. Найти вероятность, того что турист увидит извержение, если будет ждать у гейзера 20 минут. В соответствии с вышеуказанными формулами вероятность увидеть извержение в течение времени наблюдения равна 20/50=0,4, т.е. 40%.
Пример2. Симметричный волчок после раскручивания падает набок. Вертикальная ось волчка после падения указывает на определенный угол от 0 до 360 градусов. Найти вероятность, того что ось волчка укажет на сектор от 90 до 180 градусов. Вероятность равна (180-90)/(360-0)=0,25.
В файле примера приведены графики плотности распределения вероятности и интегральной функции распределения . 
Математическое ожидание и дисперсия
Математическое ожидание для равномерного непрерывного распределения вычисляется по формуле =(a+b)/2.
Генерация случайных чисел
Случайные числа, имеющие равномерное непрерывное распределение на отрезке [0; 1), можно сгенерировать с помощью функции MS EXCEL СЛЧИС() . В функции нельзя задать нижнюю и верхнюю границу интервала, но записав формулу =СЛЧИС()*(b-a)+a можно сгенерировать равномерно распределенные числа на любом интервале [a; b).
Примечание : Чтобы сгенерировать случайные числа, имеющие равномерное дискретное распределение , воспользуйтесь функцией СЛУЧМЕЖДУ() .
Сгенерировать случайные числа, извлеченные из непрерывного равномерного распределения, можно также с помощью надстройки Пакет анализа .
Сгенерируем массив из 50 чисел из диапазона [3,3; 7,5). Для этого в окне Генерация случайных чисел установим следующие параметры (см. файл примера лист Генерация ):
Как видно из рисунка выше, в поле Случайное рассеивание установлен необязательный параметр равный 2. Параметр Случайное рассеивание может принимать значение от 1 до 32767. Если установить этот параметр, то MS EXCEL будет каждый раз генерировать один и тот же массив чисел, соответствующий этот значению. Этот подход удобен для генерации одинаковых массивов, например, на различных компьютерах.
Оценка среднего и стандартного отклонения
Нижнюю и верхнюю границу интервала возьмем [3,3; 7,5) и разместим их в ячейках B4:B5 . Сгенерируем 50 чисел ( выборку ) и поместим их в диапазоне С14:С63 .
Математическое ожидание этого распределения =(B4+B5)/2 и равно 5,4. Стандартное отклонение распределения равно =КОРЕНЬ(((B5-B4)^2)/12)=1,21
Чтобы оценить математическое ожидание воспользуемся значениями выборки =СУММ(C14:C63)/СЧЁТ(C14:C63) .
Оценить стандартное отклонение можно с помощью формулы =СТАНДОТКЛОН.В(C14:C63) в MS EXCEL 2010 или =СТАНДОТКЛОН(C14:C63) для более ранних версий.
Чтобы оценить дисперсию используйте формулу =ДИСП.В(C14:C63) в MS EXCEL 2010 или =ДИСП(C14:C63) для более ранних версий. Также можно использовать формулу =СТАНДОТКЛОН.В(C14:C63)^2 .
СОВЕТ : О других распределениях MS EXCEL можно прочитать в статье Распределения случайной величины в MS EXCEL .
Как посчитать процентное распределение в Excel по формуле
Процентное распределение отображает нам как определенное значение (например, показатель суммарного дохода) разделяется на отдельные составляющие, которые образуют его целостность.
Формула процентного распределения в Excel
Как видно ниже на рисунке ниже формула вычисления процентного распределения в Excel очень проста:
Каждую часть необходимо разделить на сумму всех частей. В данном случаи ячейка B7 содержит значение суммарного дохода всех отделов регионов. Чтобы вычислить процентное распределение суммарного дохода по всем регионам, достаточно лишь поделить значение отдельного показателя по каждому региону на суммарный доход.
Как видно формула не очень сложна. Она использует просто относительные ссылки на доходы регионов, чтобы поделить их на абсолютную ссылку на суммарный доход. Обратите внимание на абсолютную ссылку. Указанные символы доллара позволяют заблокировать ссылку на одну, конкретную ячейку. Благодаря этому адрес абсолютной ссылки не изменяется при копировании формул в другие ячейки.
Процентное распределение по динамической формуле Excel
Отдельное вычисление для хранения суммарного дохода в отдельной ячейке как константу – не обязательно. Если мы добавим в формулу функцию =СУММ(), тогда мы можем динамически выполнять вычисление процентного распределения. Ниже на рисунке показано решение для создания динамической формулы процентного распределения отдельных значений.
Примечание: Для тех, кто не в курсе – функция СУММ суммирует все значения, которые заданы в ее аргументах.
Снова обратите внимание на то, что все адреса ссылок, которые заданы в аргументах функции СУММ должны быть абсолютными (в данном случаи). Благодаря зафиксированный абсолютными ссылками диапазон ячеек в аргументе функции СУММ, не изменяться в процессе копирования формулы в другие ячейки.
Распределение суммы в таблице Excel помесячно
Добрый день, друзья!
Помогите, если не трудно, справиться с задачкой.
Нужно распределить потребность равномерно помесячно по году. Допускается создание дополнительной таблицы. Но единица измерения меньше единицы не допускается.
Буду Вам очень признателен!
Вложения
Книга1.xlsx (11.6 Кб, 13 просмотров)
В ТАБЛИЦЕ EXCEL РАСПРЕДЕЛЕНИЕ КОЛИЧЕСТВА СООТВЕТСТВУЮЩЕМУ НАИМЕНОВАНИЮ
ЗДРАВСТВУЙТЕ. ПЕРВЫЙ РАЗ НА ФОРУМЕ, НЕ СУДИТЕ СТРОГО. ВОПРОС В ТОМ, ЧТО НАДО СДЕЛАТЬ.
распределение суммы
помогите решить следующую задачу есть общая сумма 2750, которую надо разбить на 70 разных.
Распределение суммы переменной
Здравствуйте у меня вопрос. Я хочу вместо нескольких массивов использовать один. Если я буду их.
Распределение суммы по ФИФО
Необходимо высчитать сколько осталось товара с каждой поставки. За основу взять алгоритм ФИФО.
Вложения
Книга1-4.xlsx (12.7 Кб, 23 просмотров)
Сообщение от andrei_g1
Сообщение было отмечено andrei_g1 как решение
Решение
А можете это сделать в моей табличке? А то я совсем «чайник», боюсь что не правильно пропишу формулы.
Спасибо огромное заранее.
Добавлено через 39 минут
Все. Формулы вставил, все получилось.
Ребята, спасибо огромное. Вы мне здорово помогли
Вложения
Книга1-4.xlsx (13.7 Кб, 21 просмотров)
Сообщение было отмечено andrei_g1 как решение
Решение
заинтересовавшись этим вопросом также выяснил,
что для разных отчётов можно ещё использовать
т.н. банковское (бухгалтерское) округление
также известное, как округление до ближайшего чётного
суть его в том, что все числа, у которых «отбрасываемая» цифра не равна 5,
округляются по правилам математического округления,
а если равна, то цифра округляется до ближайшего кратного целого числа
т.е. 6,5 до 6 а 7,5 до 8.
приводят следующий пример:
Сумма исходных чисел: 1.5 + 2.5 + 3.5 +4.5 = 12
Сумма округленных чисел классическим методом: 2 + 3 + 4 + 5 = 14
Сумма округленных чисел бухгалтерским методом: 2 + 2 + 4 + 4 = 12
Распределение суммы акций
Введите фамилии сотрудников (не менее 20), для всех них укажите даты приема на работу (в диапазоне.
Пропорциональное распределение суммы ЗП
Здравствуйте.:) Поставлена задача распределения суммы з/п сотрудника, которая указана в.
Как задать распределение суммы
Родитель дает ребенку X рублей на месяц, в месяце Y дней (каждый месяц разный). Нужно.
Пропорциональное распределение потока с проверкой суммы
ПОжалуйста помогите написать формулу: Спримерно : J6= ЕСЛИ СУММ (F6: I6)<E6, то D6*J5/(d6+d7) .
Распределение по командам в Excel
Уважаемые коллеги! Помогите решить задачу через Excel Дано: Список 14 учеников в первой.
Распределение задач в Excel
Добрый день, форумчане! Задали не простую задачку над которой бьюсь уже второй день. Общая.
griched
Пользователь
Сообщений: 4
Регистрация: 01.01.1970
#1
01.04.2011 01:17:19
Здравствуйте! Поиск не помог(((
Идея такая: есть ряд товаров с определенной ценой, например,
наим. кол-во цена сумма
апельсины — 10 шт. 10 100
бананы 5 — 8 40
————————————
ИТОГО 140
нужно включить стоимость доставки, допустим 5% от суммы (т.е. 7),в стоимость товаров
2 товара => 7/2= 3,5
=> апельсины цена 13,5 бананы 11,5
Когда наименований два, то еще хорошо, а если их 100 и более….
|
0 / 0 / 0 Регистрация: 15.07.2020 Сообщений: 4 |
|
|
1 |
|
|
15.07.2020, 17:16. Показов 8096. Ответов 8
Добрый день, друзья! Буду Вам очень признателен!
0 |
|
866 / 510 / 162 Регистрация: 30.03.2013 Сообщений: 1,048 |
|
|
15.07.2020, 19:53 |
2 |
|
1 |
|
0 / 0 / 0 Регистрация: 15.07.2020 Сообщений: 4 |
|
|
16.07.2020, 07:08 [ТС] |
3 |
|
Спасибо большое за ответ!
0 |
|
5942 / 3154 / 698 Регистрация: 23.11.2010 Сообщений: 10,524 |
|
|
16.07.2020, 08:20 |
4 |
|
меньше единицы не допускается. в случаях где всего 1, 2 и 16 как быть?
1 |
|
65 / 22 / 7 Регистрация: 28.02.2018 Сообщений: 211 |
|
|
16.07.2020, 08:44 |
5 |
|
Решениеandrei_g1, я обычно делаю округление до нуля а в последней ячейке считаю разницу для большей равномерности (и, в данном случае, чтобы избавиться от минусов), Код F6:=ЕСЛИ(ОКРУГЛ($C6/4;0)-СУММ($D6:E6)>0;ОКРУГЛ($C6/4;0)-СУММ($D6:E6);0) i6:=ЕСЛИ(ОКРУГЛ($C6/2;0)-СУММ($D6:H6)>0;ОКРУГЛ($C6/2;0)-СУММ($D6:H6);0) L6:=ЕСЛИ(ОКРУГЛ($C6*3/4;0)-СУММ($D6:K6)>0;ОКРУГЛ($C6*3/4;0)-СУММ($D6:K6);0) O6:=ЕСЛИ(C6-СУММ(D6:N6)>0;C6-СУММ(D6:N6);0) Добавлено через 8 минут
1 |
|
0 / 0 / 0 Регистрация: 15.07.2020 Сообщений: 4 |
|
|
16.07.2020, 10:39 [ТС] |
6 |
|
А можете это сделать в моей табличке? А то я совсем «чайник», боюсь что не правильно пропишу формулы. Спасибо огромное заранее!!! Добавлено через 39 минут
0 |
|
866 / 510 / 162 Регистрация: 30.03.2013 Сообщений: 1,048 |
|
|
16.07.2020, 10:44 |
7 |
|
Тогда так Код =IF(COLUMNS($D:D)<=(12-(ROUNDUP($C6/12;0)*12-$C6));ROUNDUP($C6/12;0);ROUNDDOWN($C6/12;0))
1 |
|
65 / 22 / 7 Регистрация: 28.02.2018 Сообщений: 211 |
|
|
17.07.2020, 10:21 |
8 |
|
Решениеandrei_g1, Не по теме: заинтересовавшись этим вопросом также выяснил, приводят следующий пример: Код =ОКРУГЛТ(A2;2) =ЦЕЛОЕ(A2/2+0,5)*2 в данном случае такое округление не подходит
1 |
|
0 / 0 / 0 Регистрация: 15.07.2020 Сообщений: 4 |
|
|
17.07.2020, 16:32 [ТС] |
9 |
|
Вот умом понимаю, а в формулах прописать тяму не хватает, даже сказал бы, навыков.
0 |
Процентное распределение отображает нам как определенное значение (например, показатель суммарного дохода) разделяется на отдельные составляющие, которые образуют его целостность.
Как видно ниже на рисунке ниже формула вычисления процентного распределения в Excel очень проста:
Каждую часть необходимо разделить на сумму всех частей. В данном случаи ячейка B7 содержит значение суммарного дохода всех отделов регионов. Чтобы вычислить процентное распределение суммарного дохода по всем регионам, достаточно лишь поделить значение отдельного показателя по каждому региону на суммарный доход.
Как видно формула не очень сложна. Она использует просто относительные ссылки на доходы регионов, чтобы поделить их на абсолютную ссылку на суммарный доход. Обратите внимание на абсолютную ссылку. Указанные символы доллара позволяют заблокировать ссылку на одну, конкретную ячейку. Благодаря этому адрес абсолютной ссылки не изменяется при копировании формул в другие ячейки.
Процентное распределение по динамической формуле Excel
Отдельное вычисление для хранения суммарного дохода в отдельной ячейке как константу – не обязательно. Если мы добавим в формулу функцию =СУММ(), тогда мы можем динамически выполнять вычисление процентного распределения. Ниже на рисунке показано решение для создания динамической формулы процентного распределения отдельных значений.
Примечание: Для тех, кто не в курсе – функция СУММ суммирует все значения, которые заданы в ее аргументах.
Снова обратите внимание на то, что все адреса ссылок, которые заданы в аргументах функции СУММ должны быть абсолютными (в данном случаи). Благодаря зафиксированный абсолютными ссылками диапазон ячеек в аргументе функции СУММ, не изменяться в процессе копирования формулы в другие ячейки.
1С:Предприятие 8.2 /
Бухгалтерия для Украины /
Учет НДС
Когда предприятие одновременно ведет хозяйственную деятельность, не облагаемую НДС и облагаемую НДС, тогда часть расходов можно соотнести с конкретным видом деятельности. Но часть расходов организации не может быть соотнесена с одним из видов деятельности. Обычно это расходы, связанные с обеспечением организации в целом — аренды помещений, коммунальные платежи, связь и т. д. Хотя, могут быть и запасы , используемые для обоих видов деятельности, и необоротные активы, работающие в обоих видах деятельности.
Например, организация одновременно продает фармацевтические препараты (освобожденные от НДС) и косметические средства (облагаемые НДС по ставке 20%). Другой пример, организация производит различную печатную продукцию — периодические издания и обычную художественную литературу (освобожденные от НДС), а также учебники и учебные пособия (облагаемые НДС по ставке 20%).
При этом для учета входящего НДС при покупках любых запасов, необоротных активов и услуг действуют такие правила:
-
Если закупаемый актив можно четко отнести к использованию в облагаемой НДС хозяйственной деятельности, то при наличии входящего НДС (ставка 20%) вся сумма НДС включается в налоговый кредит. Налоговая и бухгалтерская стоимость актива определяется по стоимости закупки без НДС. Налоговое назначение НДС при закупке указывается «Обл. НДС»,
-
Если закупаемый актив можно четко отнести к использованию в не облагаемой НДС хозяйственной деятельности, то независимо от наличия входящего НДС (ставка 20% или без НДС) вся стоимость закупки включается в налоговую и бухгалтерскую стоимость актива. Сумма НДС не включается в налоговый кредит. Налоговое назначение закупки указывается «Необл. НДС, любая хоз. «,
-
Если закупаемый актив используется для обоих видов хозяйственной деятельности, то согласно ст. 199 НКУ входящий НДС может быть включен в налоговый кредит только в той части, которая используется в налогооблагаемых операциях. Другая часть НДС включается в бухгалтерскую и налоговую стоимость актива. Налоговое назначение закупки указывается «Пропорц. обл. НДС».
Если приобретаемый актив используется не в хозяйственной деятельности, то его налоговая сумма определяется нулевой, и права на налоговый кредит не возникает. Ни о каком пропорциональном распределении НДС между хозяйственной и не хозяйственной деятельностью речи быть не может. Налоговое назначение указывается «Необл. НДС, нехоз. «. Для операций продажи налоговое назначение «Пропорц. обл. НДС» не используется, так как для реализуемого актива должна быть указана определенная ставка НДС и определенное облагаемое или не облагаемое назначение.
Для пропорционального распределения НДС следует в начале года или первого месяца, когда появились два вида деятельности с точки зрения обложения НДС — зафиксировать коэффициент распределения. Операция отражается документом «Установка коэффициента пропорционального отнесения НДС на кредит».
Рис.1. Установка коэффициентов пропорционального распределения НДС
Коэффициент можно рассчитать автоматически, если в ИБ есть данные прошлого года (или первого месяца в 2011 году, когда появились два вида деятельности НДС). Такой расчет производится по кнопке «Заполнить и рассчитать». При этом выбираются данные в зависимости от установленного переключателя — объемы продаж или прошлого года, или указанного месяца. Если такой информации в ИБ нет, то коэффициент можно рассчитать вручную и ввести в поле «Коэфф. (доля использования)». Если весь объем продаж взять за единицу, то коэффициент показывает — какова доля облагаемых НДС продаж.
В документах приобретения, оплат поставщикам (для договоров с упрощенным налоговым учетом) и в регламентном документе » Регистрация авансов в налоговом учете » (для договоров со сложным налоговым учетом) указываются такие налоговые параметры операций:
-
Ставка НДС,
-
Сумма НДС — полная в соответствие с документами поставщика,
-
Налоговое назначение НДС = «Пропорц. обл. НДС».
Само пропорциональное распределение НДС производится только для операций закупки по ставке 20%, т. к. в противном случае нет суммы НДС и нет предмета распределения. Но для контроля целевого использования и для дальнейшей возможности включения актива в расходы и облагаемой, и не облагаемой НДС деятельности рекомендуется указывать именно это налоговое назначение даже в случае приобретения без НДС. При этом актив с налоговым назначением «Пропорц. обл. НДС» может списываться (амортизироваться) и на облагаемую, и на не облагаемую НДС хозяйственную деятельность без изменения налогового назначения. Однако при списании на не хозяйственную деятельность налоговое назначение следует предварительно изменить.
Указанные выше налоговые параметры должны устанавливаться одинаковыми и в первичных документах, регистрирующих ожидаемый налоговый кредит, и в налоговых документах, регистрирующих подтвержденный налоговый кредит.
При отражении покупки запасов с налоговым назначением НДС «Пропорц. обл. НДС» при проведении документа автоматически рассчитывается сумма налогового кредита в соответствие с установленным коэффициентом (см. Рис. 6.35).
Рис.2. Покупка запасов на распределяемую по НДС деятельность
Остальная часть НДС включается в бухгалтерскую и налоговую стоимость запаса. Аналогичным образом отражаются и другие операции:
-
При покупке необоротного актива часть НДС включается в налоговый кредит, а остальная сумма — в стоимость будущего необоротного актива,
-
При получении услуг часть НДС включается в налоговый кредит, а остальная сумма — в затраты, на которые отнесены полученные услуги,
-
В платежных документах и при регистрации авансов —» рассчитывается только; сумма НДС, включаемая в налоговый кредит — для формирования проводки по НДС; налоговая стоимость актива не формируется по причине отсутствия в операции актива.
Для регистрации подтвержденного налогового кредита вводится документ » Регистрация входящего налогового документа «.
Рис.3. Регистрация входящего налогового документа
При этом сумма операции разбивается по двум строкам документа:
-
База и НДС, включенный в налоговый кредит — с видом «облагаемая НДС деятельность» и соответствующей строкой налоговой декларации,
-
База и НДС, включенные в стоимость запаса — с видом «не облагаемая НДС деятельность» и соответствующей строкой налоговой декларации.
Но в обеих строчках должен быть установлен флаг «Пропорционально». Проводка по подтвержденному НДС формируется только в части НДС, включенной в налоговый кредит.
Такое разбиение на две строки производится:
-
Либо автоматически при создании налогового документа вводом на основании первичного документа с указанными налоговыми параметрами и зафиксированным «событием» по НДС,
-
Либо полуавтоматически — по кнопке «Добавить строки по пропорциональному НДС» — при наличии в документе одной строки с общей суммой операции и флагом «Пропорционально». Второй вариант используется, когда налоговый документ:
-
заполняется вручную,
-
или создается вводом на основании платежного документа для договоров со сложным налоговым учетом (в этом случае налоговые параметры операции не заполняются в платежке и не фиксируются в регистрах), о или создается вводом на основании документа, не являющегося в полной сумме документом «первого события».
-
Такое заполнение документа «Регистрация входящего налогового документа» обеспечивает заполнение отчета «Реестр налоговых документов» в соответствии с п. 11.10 Порядка 1002, а также расшифровку сумм, включаемых и не включаемых в налоговый кредит, в Приложении 5 к Декларации.
Другие материалы по теме:
вводом на основании, сумма ндс, регистрация авансов в налоговом учете, закупки, деятельность, налоговый кредит, регистрация входящего налогового документа, актив., актив, ставка, коэффициент, налоговое назначение, регистрация, назначение, кредит, стоимость, значение, сумма, регистр, организация, операции, документа, документ
Автор:
Robert Simon
Дата создания:
21 Июнь 2021
Дата обновления:
9 Апрель 2023
Содержание
- Шаг 1
- Шаг 2
- Шаг 3
- Шаг 4
- Шаг 5
- Шаг 6
Ячейки электронной таблицы Excel позволяют вводить числа и выполнять математические вычисления с этими числами, что делает его идеальным инструментом для пропорционального распределения чисел. Когда у вас есть стоимость, распределенная в течение определенного периода времени, например, ежемесячная арендная плата или двухнедельный платеж, если вам необходимо знать стоимость за долю этого периода времени, этот процесс называется пропорциональным распределением. Excel позволяет поместить каждый фактор в формуле пропорционального распределения в отдельную ячейку, что упрощает понимание математических расчетов, лежащих в основе процесса.
Шаг 1
Откройте новую электронную таблицу Excel 2010.
Шаг 2
Щелкните ячейку «A1», которая является верхней ячейкой в крайнем левом столбце. Введите общую сумму, которую вы собираетесь уменьшить до пропорциональной суммы. Если, например, вы распределяете ежемесячный счет пропорционально, это будет обычная ежемесячная стоимость счета.
Шаг 3
Щелкните ячейку «B1», которая находится справа от первой ячейки. Введите общее количество подпериодов в общем периоде. Например, если вы распределяете ежемесячный счет пропорционально на несколько дней, укажите количество дней в месяце в этой ячейке.
Шаг 4
Щелкните ячейку «C1». Введите количество подпериодов, которые вы хотите использовать для определения пропорциональной суммы. Продолжая примеры, это количество дней, прошедших в месяце на момент определения пропорциональной суммы.
Шаг 5
Щелкните ячейку «B2», которая находится чуть ниже общего количества подпериодов. Введите «= A1 / B1» без кавычек, чтобы получить стоимость для каждого подпериода.
Щелкните ячейку «C3» и введите «= B2 * C1» без кавычек, чтобы получить желаемую пропорциональную сумму.
Процентное распределение отображает нам как определенное значение (например, показатель суммарного дохода) разделяется на отдельные составляющие, которые образуют его целостность.
Формула процентного распределения в Excel
Как видно ниже на рисунке ниже формула вычисления процентного распределения в Excel очень проста:
Каждую часть необходимо разделить на сумму всех частей. В данном случаи ячейка B7 содержит значение суммарного дохода всех отделов регионов. Чтобы вычислить процентное распределение суммарного дохода по всем регионам, достаточно лишь поделить значение отдельного показателя по каждому региону на суммарный доход.
Как видно формула не очень сложна. Она использует просто относительные ссылки на доходы регионов, чтобы поделить их на абсолютную ссылку на суммарный доход. Обратите внимание на абсолютную ссылку. Указанные символы доллара позволяют заблокировать ссылку на одну, конкретную ячейку. Благодаря этому адрес абсолютной ссылки не изменяется при копировании формул в другие ячейки.
Процентное распределение по динамической формуле Excel
Отдельное вычисление для хранения суммарного дохода в отдельной ячейке как константу – не обязательно. Если мы добавим в формулу функцию =СУММ(), тогда мы можем динамически выполнять вычисление процентного распределения. Ниже на рисунке показано решение для создания динамической формулы процентного распределения отдельных значений.
Примечание: Для тех, кто не в курсе – функция СУММ суммирует все значения, которые заданы в ее аргументах.
Снова обратите внимание на то, что все адреса ссылок, которые заданы в аргументах функции СУММ должны быть абсолютными (в данном случаи). Благодаря зафиксированный абсолютными ссылками диапазон ячеек в аргументе функции СУММ, не изменяться в процессе копирования формулы в другие ячейки.
Можно все это разместить в одной формуле, но получится громоздкая и слабопонятная конструкция.
Итак. Первое, что нужно — таблица с граничными данными. Есть (столбцы B:C).
Чтобы найти скидку цены, нужно узнать стоимость одной операции в диапазоне, в который попало общее количество операций. Соответственно, для определения стоимости необходимо получить количество операций в диапазоне.
Ячейка F1— количество операций, стоимость которых нужно определить.
Определяем границы диапазона — пороги количества и граничные значения стоимости.
Нижний порог (F2):
=ИНДЕКС(B2:B11;ПОИСКПОЗ(F1;B2:B11))
Верхний порог (F3):
=ЕСЛИ(F1>МАКС(B2:B11);МАКС(B2:B11);ИНДЕКС(B2:B11;ПОИСКПОЗ(F1;B2:B11)-СЧЁТЕСЛИ(B2:B11;F1)+1))
Сравнение с МАКС — для количества больше максимального порога, СЧЁТЕСЛИ помогает отследить точное совпадение с пороговым значением.
Стоимость для нижнего и верхнего порогов
(F5, F6) определяется по найденным пороговым значениям:
=ВПР(F2;$B$2:$C$11;2;)
=ВПР(F3;$B$2:$C$11;2;)
Стоимость одной операции (F7) — простым математическим выражением:
=F5-(F5-F6)/(F3-F2+1)*(F1-F2))
Все. Полная стоимость (F8), как обычно — количество*цена:
=F1*F7
Для мазохистов — все в одной ячейке (без оптимизаций и сокращений, почти 400 знаков в формуле)
=F1*(ВПР(ИНДЕКС(B2:B11;ПОИСКПОЗ(F1;B2:B11));$B$2:$C$11;2;)-(ВПР(ИНДЕКС(B2:B11;ПОИСКПОЗ(F1;B2:B11));$B$2:$C$11;2;)-ВПР(ЕСЛИ(F1>МАКС(B2:B11);МАКС(B2:B11);ИНДЕКС(B2:B11;ПОИСКПОЗ(F1;B2:B11)-СЧЁТЕСЛИ(B2:B11;F1)+1));$B$2:$C$11;2;))/(ЕСЛИ(F1>МАКС(B2:B11);МАКС(B2:B11);ИНДЕКС(B2:B11;ПОИСКПОЗ(F1;B2:B11)-СЧЁТЕСЛИ(B2:B11;F1)+1))-ИНДЕКС(B2:B11;ПОИСКПОЗ(F1;B2:B11))+1)*(F1-ИНДЕКС(B2:B11;ПОИСКПОЗ(F1;B2:B11))))