Как рассчитать рабочие дни и компенсацию за определенный период исходя из месячной заработной платы сотрудника?

У меня всего три редактируемых поля:

  1. зарплата = 15000
  2. дата начала = 18 июл 2014
  3. дата окончания = 12 октября 2014 г.

С помощью этих полей мне нужно рассчитать общую зарплату, которую мне нужно выплатить. Оплата производится ежемесячно:

(ИЮЛЬ = 15000/31 * 14) +
(AUG = 15000/31 * 31) +
(SEPT = 15000/30 * 30 ) +
(ОКТ = 15 000/30 * 12).

Я могу получить общее количество дней на основе обеих дат (например, 87 дней, =DAYS($enddate,$startdate)+1)), но мне нужно разделить дни по месяцам.

Какая формула мне нужна автоматически, чтобы получить сумму, ведь у каждого будет разная зарплата и разные даты?


excel excel-formula worksheet worksheet-function
person Faizal Nor    schedule 17.12.2014    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Вы можете использовать настраиваемую функцию Excel VBA для решения вашей проблемы:

1). Во-первых, вы должны заполнить лист Excel структурой данных, отражающей имя сотрудника, месячную зарплату, дату начала и дату окончания, как в следующем примере:

Employee    M.Salary    Start Date  EndDate
John     $15,000.00     7/18/2014   10/12/2014
Ann      $20,000.00     7/19/2014   10/13/2014
Peter    $16,000.00     7/20/2014   10/14/2014
Jeff     $25,000.00     7/21/2014   10/15/2014

2). ДНИ в диапазоне дат можно просто найти путем вычитания (EndDate-StartDate), потому что базовый тип данных в целочисленном

3). Для общего решения проблемы (рассчитать компенсацию за любой произвольный период и ежемесячную зарплату) вам нужно будет создать собственную формулу VBA и использовать ее в отдельном столбце для каждого сотрудника. См. Пояснения в этой статье: Создайте настраиваемую функцию рабочего листа в Excel VBA < / а>

4). Применимое к вашему конкретному случаю с фиксированной датой, упрощенное решение, основанное на формулах Excel Worksheet (без VBA), описано ниже:

Employee MoSalary       Start       End         Days FullMo     FirstMo        LastMo        Total
John     $15,000.00     7/18/2014   10/12/2014  87  30000.00     $6,774.19   $6,000.00   $42,774.19 
Ann      $20,000.00     7/18/2014   10/12/2014  87  40000.00     $9,032.26   $8,000.00   $57,032.26 
Peter    $16,000.00     7/18/2014   10/12/2014  87  32000.00     $7,225.81   $6,400.00   $45,625.81 
Jeff     $25,000.00     7/18/2014   10/12/2014  87  50000.00     $11,290.32      $10,000.00      $71,290.32

4а). В столбце E, начиная со строки 2, добавьте формулу для ДНЕЙ: =(D2-C2)+1 и распространите ее на весь диапазон.

4б). В столбце F, начиная со строки 2, добавьте формулу для целых месяцев: =2*B2 и распространите ее на весь диапазон.

4в). В столбце G, начиная со строки 2, добавьте формулу для первого месяца: =14*B2/31 и распространите ее на весь диапазон.

4г). В столбце H, начиная со строки 2, добавьте формулу за последний месяц: =12*B2/30 и распространите ее на весь диапазон.

4д). В столбце I, начиная со строки 2, добавить формулу для общей компенсации: =SUM(F2:H2) и распространить ее на весь диапазон.

Надеюсь, это поможет. С наилучшими пожеланиями,

person Alexander Bell    schedule 17.12.2014
comment
Я согласен с вами, @pnuts, но это то, что OP поместил в свой образец: (OCT = 15000/30 * 12). Давайте подождем его ответа, а не будем гадать. С наилучшими пожеланиями, - person Alexander Bell; 17.12.2014
comment
привет, спасибо за ответ, но я считаю, что это не решает автоматически. Я имею в виду, что формула должна определять, какой месяц входит в продолжительность обеих дат, и только тогда можно сформулировать, сколько дней насчитывается в каждом задействованном месяце. - person Faizal Nor; 07.01.2015

arrow_upward
0
arrow_downward

Я разделил это, потому что я не совсем уверен, что вам нужно, и вы, возможно, сможете собрать части таким образом, который вам больше подходит:

Предполагая, что ваши данные находятся в формате A1: A3, поместите 1/7/14 в C1, 1/8/14 в D1, 1/9/14 в E1 и 1/10/14 в F1.

Чтобы подсчитать количество применимых дней по месяцам, в C2 введите:

=IF(MONTH($A2)=MONTH(C1),EOMONTH(C1,0)-$A2+1,IF(MONTH($A3)=MONTH(C1),DAY($A3),EOMONTH(C1,0)-EOMONTH(C1,-1)))

Чтобы вычислить зарплату за месяц, вычислив дневную ставку за соответствующий месяц и умножив полученную сумму на количество дней, указанное выше, в C3 введите:

=$A1*C2/(DAYS(EOMONTH(C1,0),C1)+1)

Отформатируйте C2: C3 для соответствия и скопируйте в F2: F3.

person pnuts    schedule 17.12.2014
comment
привет, спасибо за ответ, но я не могу полностью понять ваше решение. было бы неплохо, если бы вы могли объяснить дальше. заранее спасибо! - person Faizal Nor; 07.01.2015