Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/доработка формулы или как расчитать сумму - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » доработка формулы или как расчитать сумму (Формулы/Formulas)
доработка формулы или как расчитать сумму
nespica2012 Дата: Воскресенье, 13.02.2022, 19:43 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 40
Репутация: 0 ±
Замечаний: 0% ±

Добрый вечер, уважаемые форумчане! :)

имеем заполненную таблицу на первом листе"сверхурочка" с часами по дням, далее данные переносятся в таблицу на лист "расчет"
необходимо рассчитать сумму: первые два часа дня умножаем на полуторный коэффициент, все последующие – на коэффициент 2

тоесть, в ячейке "СУММА" =сумма первых двух часов одного дня, помноженная*1.5 *на тариф *на коэф (Q*R*2 часа*1.5)+ сумма оставшегося времени в этот же день,умноженная на два на тариф и коэф(Q*R*оставшееся время*2) и потом прибавляем такойже расчет по всем последующим дням

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

Код
=СУММ(ЕСЛИОШИБКА(--ТЕКСТ(МУМНОЖ(ТРАНСП(Таблица18[[1]:[30]]*(Таблица18[Ф. И. О.]=B7)*(Таблица18[р/р]=расчет!D6&"/"&ОТБР(расчет!Q4*100)));Таблица18[год]^0*2)-1;"0;\0;\0");))*Q6*R6


но как ее транслировать на остальные заполненные ячейки я не осознал :'(

помогите пжслт
К сообщению приложен файл: 9222851.xlsm(58.5 Kb)
 
Ответить
СообщениеДобрый вечер, уважаемые форумчане! :)

имеем заполненную таблицу на первом листе"сверхурочка" с часами по дням, далее данные переносятся в таблицу на лист "расчет"
необходимо рассчитать сумму: первые два часа дня умножаем на полуторный коэффициент, все последующие – на коэффициент 2

тоесть, в ячейке "СУММА" =сумма первых двух часов одного дня, помноженная*1.5 *на тариф *на коэф (Q*R*2 часа*1.5)+ сумма оставшегося времени в этот же день,умноженная на два на тариф и коэф(Q*R*оставшееся время*2) и потом прибавляем такойже расчет по всем последующим дням

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

Код
=СУММ(ЕСЛИОШИБКА(--ТЕКСТ(МУМНОЖ(ТРАНСП(Таблица18[[1]:[30]]*(Таблица18[Ф. И. О.]=B7)*(Таблица18[р/р]=расчет!D6&"/"&ОТБР(расчет!Q4*100)));Таблица18[год]^0*2)-1;"0;\0;\0");))*Q6*R6


но как ее транслировать на остальные заполненные ячейки я не осознал :'(

помогите пжслт

Автор - nespica2012
Дата добавления - 13.02.2022 в 19:43
Pelena Дата: Воскресенье, 13.02.2022, 21:19 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 18556
Репутация: 4203 ±
Замечаний: ±

Excel 2016 & Mac Excel
Здравствуйте.
Как поняла
К сообщению приложен файл: 0343012.xlsm(58.0 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Как поняла

Автор - Pelena
Дата добавления - 13.02.2022 в 21:19
nespica2012 Дата: Понедельник, 14.02.2022, 19:23 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 40
Репутация: 0 ±
Замечаний: 0% ±

Pelena,
добрый вечер, не совсем то,

мне надо, например, если заполнены ячейки столбца "G", то необходимо производить расчет в ячейках столбца "Н", и соответственно, если ячейки столбца "K" -то в ячейках столбца "L"; если ячейки столбца "S" -то в ячейках столбца "T"; если ячейки столбца "W" -то в ячейках столбца "X"
по следующим условиям:
например, G7>0, то ищем на странице "DATA_Tech" строку с "работником6", с разрядом работ "р/р"="3/4" и за каждый день производим расчет перебором каждой ячейки от "Н" до"AL" =сумма(Если(Н15>2;((2*1,5*E7*F7)+((H15-2)*2*E7*F7)));Н15*1,5)+ Если(I15>2;((2*1,5*E7*F7)+((I15-2)*2*E7*F7)));I15*1,5)+ ( и так далее до... Если(AL15>2;((2*1,5*E7*F7)+((AL15-2)*2*E7*F7)));AL15*1,5)) - это я создаю расчет. что первые два часа считаются по тарифу в полуторном размере, а остальные в двойном. и если человек отработал меньше двух часов, то просто будет умножаться на полуторный тариф.

вот и думаю, как создать поиск по условию заполнения определенной ячейки (например,G7) и далее прописать в формулу сумму всех переборов ячеек с первого числа до 31 (то есть от 'H' до 'AL' )

замудренно?

могу как-то еще расписать алгоритм, и разбить задачу :)
 
Ответить
СообщениеPelena,
добрый вечер, не совсем то,

мне надо, например, если заполнены ячейки столбца "G", то необходимо производить расчет в ячейках столбца "Н", и соответственно, если ячейки столбца "K" -то в ячейках столбца "L"; если ячейки столбца "S" -то в ячейках столбца "T"; если ячейки столбца "W" -то в ячейках столбца "X"
по следующим условиям:
например, G7>0, то ищем на странице "DATA_Tech" строку с "работником6", с разрядом работ "р/р"="3/4" и за каждый день производим расчет перебором каждой ячейки от "Н" до"AL" =сумма(Если(Н15>2;((2*1,5*E7*F7)+((H15-2)*2*E7*F7)));Н15*1,5)+ Если(I15>2;((2*1,5*E7*F7)+((I15-2)*2*E7*F7)));I15*1,5)+ ( и так далее до... Если(AL15>2;((2*1,5*E7*F7)+((AL15-2)*2*E7*F7)));AL15*1,5)) - это я создаю расчет. что первые два часа считаются по тарифу в полуторном размере, а остальные в двойном. и если человек отработал меньше двух часов, то просто будет умножаться на полуторный тариф.

вот и думаю, как создать поиск по условию заполнения определенной ячейки (например,G7) и далее прописать в формулу сумму всех переборов ячеек с первого числа до 31 (то есть от 'H' до 'AL' )

замудренно?

могу как-то еще расписать алгоритм, и разбить задачу :)

Автор - nespica2012
Дата добавления - 14.02.2022 в 19:23
Pelena Дата: Понедельник, 14.02.2022, 19:29 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 18556
Репутация: 4203 ±
Замечаний: ±

Excel 2016 & Mac Excel
могу как-то еще расписать алгоритм
лучше посчитайте и заполните вручную, что где должно получиться в Вашем примере


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
могу как-то еще расписать алгоритм
лучше посчитайте и заполните вручную, что где должно получиться в Вашем примере

Автор - Pelena
Дата добавления - 14.02.2022 в 19:29
nespica2012 Дата: Понедельник, 14.02.2022, 20:21 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 40
Репутация: 0 ±
Замечаний: 0% ±

Pelena,
в файле, под таблицей расписал то, что необходимо получить на примере работника6
К сообщению приложен файл: 9558810.xlsm(60.4 Kb)
 
Ответить
СообщениеPelena,
в файле, под таблицей расписал то, что необходимо получить на примере работника6

Автор - nespica2012
Дата добавления - 14.02.2022 в 20:21
nespica2012 Дата: Понедельник, 14.02.2022, 20:42 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 40
Репутация: 0 ±
Замечаний: 0% ±

теперь надо подумать, как создать условия для поиска нужной строки

для ячейки "Н7" ищем строку соответствующую критериям работника6, с разрядом работ р/р= 3/4 ( но там могут быть этой группе и разряд работ р/р=4/4, в ячейке "Н8", значит необходимо будет искать строку работника6, с разрядом работ р/р=4/4)

для ячейки "L9" ищем строку соответствующую критериям работника6, с разрядом работ р/р= 4/8

для ячейки "T6" ищем строку соответствующую критериям работника6, с разрядом работ р/р= 3/16 ( но там могут быть этой группе и разряд работ р/р=4/16, в ячейке "T7", да плюс коэффициент (1.1 и 1.5 для ячеек 'T8','T9', )

для ячейки "Х8" ищем строку соответствующую критериям работника6, с разрядом работ р/р= 3/24 с коэффициентом 1.1 ( но там могут быть этой группе и разряд работ р/р=4/24, в ячейке "Х7" с коэффициентом 1, да плюс разряд работ р/р=3/24 с коэффициентом 1 для ячейки 'Х6')


Сообщение отредактировал nespica2012 - Понедельник, 14.02.2022, 20:48
 
Ответить
Сообщениетеперь надо подумать, как создать условия для поиска нужной строки

для ячейки "Н7" ищем строку соответствующую критериям работника6, с разрядом работ р/р= 3/4 ( но там могут быть этой группе и разряд работ р/р=4/4, в ячейке "Н8", значит необходимо будет искать строку работника6, с разрядом работ р/р=4/4)

для ячейки "L9" ищем строку соответствующую критериям работника6, с разрядом работ р/р= 4/8

для ячейки "T6" ищем строку соответствующую критериям работника6, с разрядом работ р/р= 3/16 ( но там могут быть этой группе и разряд работ р/р=4/16, в ячейке "T7", да плюс коэффициент (1.1 и 1.5 для ячеек 'T8','T9', )

для ячейки "Х8" ищем строку соответствующую критериям работника6, с разрядом работ р/р= 3/24 с коэффициентом 1.1 ( но там могут быть этой группе и разряд работ р/р=4/24, в ячейке "Х7" с коэффициентом 1, да плюс разряд работ р/р=3/24 с коэффициентом 1 для ячейки 'Х6')

Автор - nespica2012
Дата добавления - 14.02.2022 в 20:42
nespica2012 Дата: Суббота, 19.02.2022, 21:41 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 40
Репутация: 0 ±
Замечаний: 0% ±

после некоторых раздумий, решил разбить алгоритм и упростить задачу, как мне казалось, но зашел в тупик)) решил прописать расчет сразу на листе ввода, а в таблицу уже собираю данные с первого листа.

во вложении файл,
формула написана для примера на несколько дней (1-4,15), но если ее дописывать на все дни (с 1 по 31 числа), то она становится очень очень громоздкой. можно ли упростить?

Код
=СУММ(ЕСЛИ(И([@[р/р]]="3/24";H4>2);((2*1.5*$AR$2*[@коэф])+(([@1]-2)*2*$AR$2*[@коэф]));
ЕСЛИ(И([@[р/р]]="3/16";H4>2);((2*1.5*$AR$3*[@коэф])+(([@1]-2)*2*$AR$3*[@коэф]));
ЕСЛИ(И([@[р/р]]="4/8";H4>2);((2*1.5*$AR$4*1.5)+(([@1]-2)*2*$AR$4*1.5));
ЕСЛИ(И([@[р/р]]="3/4";H4>2);((2*1.5*$AR$5)+(([@1]-2)*2*$AR$5));([@1]*1.5*$AR$5)))))+
ЕСЛИ(И([@[р/р]]="3/24";I4>2);((2*1.5*$AR$2*[@коэф])+(([@2]-2)*2*$AR$2*[@коэф]));
ЕСЛИ(И([@[р/р]]="3/16";I4>2);((2*1.5*$AR$3*[@коэф])+(([@2]-2)*2*$AR$3*[@коэф]));
ЕСЛИ(И([@[р/р]]="4/8";I4>2);((2*1.5*$AR$4*1.5)+(([@2]-2)*2*$AR$4*1.5));
ЕСЛИ(И([@[р/р]]="3/4";I4>2);((2*1.5*$AR$5)+(([@2]-2)*2*$AR$5));([@2]*1.5*$AR$5)))))+
ЕСЛИ(И([@[р/р]]="3/24";J4>2);((2*1.5*$AR$2*[@коэф])+(([@3]-2)*2*$AR$2*[@коэф]));
ЕСЛИ(И([@[р/р]]="3/16";J4>2);((2*1.5*$AR$3*[@коэф])+(([@3]-2)*2*$AR$3*[@коэф]));
ЕСЛИ(И([@[р/р]]="4/8";J4>2);((2*1.5*$AR$4*1.5)+(([@3]-2)*2*$AR$4*1.5));
ЕСЛИ(И([@[р/р]]="3/4";J4>2);((2*1.5*$AR$5)+(([@3]-2)*2*$AR$5));([@3]*1.5*$AR$5)))))+
ЕСЛИ(И([@[р/р]]="3/24";K4>2);((2*1.5*$AR$2*[@коэф])+(([@4]-2)*2*$AR$2*[@коэф]));
ЕСЛИ(И([@[р/р]]="3/16";K4>2);((2*1.5*$AR$3*[@коэф])+(([@4]-2)*2*$AR$3*[@коэф]));
ЕСЛИ(И([@[р/р]]="4/8";K4>2);((2*1.5*$AR$4*1.5)+(([@4]-2)*2*$AR$4*1.5));
ЕСЛИ(И([@[р/р]]="3/4";K4>2);((2*1.5*$AR$5)+(([@4]-2)*2*$AR$5));([@4]*1.5*$AR$5)))))+
ЕСЛИ(И([@[р/р]]="3/24";V4>2);((2*1.5*$AR$2*[@коэф])+(([@15]-2)*2*$AR$2*[@коэф]));
ЕСЛИ(И([@[р/р]]="3/16";V4>2);((2*1.5*$AR$3*[@коэф])+(([@15]-2)*2*$AR$3*[@коэф]));
ЕСЛИ(И([@[р/р]]="4/8";V4>2);((2*1.5*$AR$4*1.5)+(([@15]-2)*2*$AR$4*1.5));
ЕСЛИ(И([@[р/р]]="3/4";V4>2);((2*1.5*$AR$5)+(([@15]-2)*2*$AR$5));([@15]*1.5*$AR$5))))))


я это я еще в середине пути...потом надо прописать еще условия для некоторой категории работников, да плюс вводить условия если меняется вид работ в течении смены B)
может уже шагать по пути VBA %) ?
К сообщению приложен файл: 0343012___2__.xlsm(72.8 Kb)


Сообщение отредактировал nespica2012 - Воскресенье, 20.02.2022, 09:57
 
Ответить
Сообщениепосле некоторых раздумий, решил разбить алгоритм и упростить задачу, как мне казалось, но зашел в тупик)) решил прописать расчет сразу на листе ввода, а в таблицу уже собираю данные с первого листа.

во вложении файл,
формула написана для примера на несколько дней (1-4,15), но если ее дописывать на все дни (с 1 по 31 числа), то она становится очень очень громоздкой. можно ли упростить?

Код
=СУММ(ЕСЛИ(И([@[р/р]]="3/24";H4>2);((2*1.5*$AR$2*[@коэф])+(([@1]-2)*2*$AR$2*[@коэф]));
ЕСЛИ(И([@[р/р]]="3/16";H4>2);((2*1.5*$AR$3*[@коэф])+(([@1]-2)*2*$AR$3*[@коэф]));
ЕСЛИ(И([@[р/р]]="4/8";H4>2);((2*1.5*$AR$4*1.5)+(([@1]-2)*2*$AR$4*1.5));
ЕСЛИ(И([@[р/р]]="3/4";H4>2);((2*1.5*$AR$5)+(([@1]-2)*2*$AR$5));([@1]*1.5*$AR$5)))))+
ЕСЛИ(И([@[р/р]]="3/24";I4>2);((2*1.5*$AR$2*[@коэф])+(([@2]-2)*2*$AR$2*[@коэф]));
ЕСЛИ(И([@[р/р]]="3/16";I4>2);((2*1.5*$AR$3*[@коэф])+(([@2]-2)*2*$AR$3*[@коэф]));
ЕСЛИ(И([@[р/р]]="4/8";I4>2);((2*1.5*$AR$4*1.5)+(([@2]-2)*2*$AR$4*1.5));
ЕСЛИ(И([@[р/р]]="3/4";I4>2);((2*1.5*$AR$5)+(([@2]-2)*2*$AR$5));([@2]*1.5*$AR$5)))))+
ЕСЛИ(И([@[р/р]]="3/24";J4>2);((2*1.5*$AR$2*[@коэф])+(([@3]-2)*2*$AR$2*[@коэф]));
ЕСЛИ(И([@[р/р]]="3/16";J4>2);((2*1.5*$AR$3*[@коэф])+(([@3]-2)*2*$AR$3*[@коэф]));
ЕСЛИ(И([@[р/р]]="4/8";J4>2);((2*1.5*$AR$4*1.5)+(([@3]-2)*2*$AR$4*1.5));
ЕСЛИ(И([@[р/р]]="3/4";J4>2);((2*1.5*$AR$5)+(([@3]-2)*2*$AR$5));([@3]*1.5*$AR$5)))))+
ЕСЛИ(И([@[р/р]]="3/24";K4>2);((2*1.5*$AR$2*[@коэф])+(([@4]-2)*2*$AR$2*[@коэф]));
ЕСЛИ(И([@[р/р]]="3/16";K4>2);((2*1.5*$AR$3*[@коэф])+(([@4]-2)*2*$AR$3*[@коэф]));
ЕСЛИ(И([@[р/р]]="4/8";K4>2);((2*1.5*$AR$4*1.5)+(([@4]-2)*2*$AR$4*1.5));
ЕСЛИ(И([@[р/р]]="3/4";K4>2);((2*1.5*$AR$5)+(([@4]-2)*2*$AR$5));([@4]*1.5*$AR$5)))))+
ЕСЛИ(И([@[р/р]]="3/24";V4>2);((2*1.5*$AR$2*[@коэф])+(([@15]-2)*2*$AR$2*[@коэф]));
ЕСЛИ(И([@[р/р]]="3/16";V4>2);((2*1.5*$AR$3*[@коэф])+(([@15]-2)*2*$AR$3*[@коэф]));
ЕСЛИ(И([@[р/р]]="4/8";V4>2);((2*1.5*$AR$4*1.5)+(([@15]-2)*2*$AR$4*1.5));
ЕСЛИ(И([@[р/р]]="3/4";V4>2);((2*1.5*$AR$5)+(([@15]-2)*2*$AR$5));([@15]*1.5*$AR$5))))))


я это я еще в середине пути...потом надо прописать еще условия для некоторой категории работников, да плюс вводить условия если меняется вид работ в течении смены B)
может уже шагать по пути VBA %) ?

Автор - nespica2012
Дата добавления - 19.02.2022 в 21:41
Pelena Дата: Воскресенье, 20.02.2022, 19:12 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 18556
Репутация: 4203 ±
Замечаний: ±

Excel 2016 & Mac Excel
Вариант в файле, только Ваши примеры расчётов расходятся с описанием. Вы пишете, что остаток часов надо умножать на 2, а в примерах расчёта не умножаете.
И ещё, если часов не может быть меньше 2, то формула значительно сократится
К сообщению приложен файл: 4524393.xlsm(59.2 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеВариант в файле, только Ваши примеры расчётов расходятся с описанием. Вы пишете, что остаток часов надо умножать на 2, а в примерах расчёта не умножаете.
И ещё, если часов не может быть меньше 2, то формула значительно сократится

Автор - Pelena
Дата добавления - 20.02.2022 в 19:12
nespica2012 Дата: Воскресенье, 20.02.2022, 21:25 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 40
Репутация: 0 ±
Замечаний: 0% ±

Pelena, спасибо большое за отклик hands

проконсультируйте, пжлст, почему при любом "движении"(изменении часов, добавлении строк и т.д) на листе с данными - на листе "расчет" пропадают суммы в ячейках? :'(

ПыСы: с умножением на 2, да, мой косяк, конечно надо)) :)
К сообщению приложен файл: 7309315.jpg(60.9 Kb)


Сообщение отредактировал nespica2012 - Воскресенье, 20.02.2022, 21:26
 
Ответить
СообщениеPelena, спасибо большое за отклик hands

проконсультируйте, пжлст, почему при любом "движении"(изменении часов, добавлении строк и т.д) на листе с данными - на листе "расчет" пропадают суммы в ячейках? :'(

ПыСы: с умножением на 2, да, мой косяк, конечно надо)) :)

Автор - nespica2012
Дата добавления - 20.02.2022 в 21:25
Pelena Дата: Воскресенье, 20.02.2022, 21:41 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 18556
Репутация: 4203 ±
Замечаний: ±

Excel 2016 & Mac Excel
Ничего не поняла, приложите файл с изменением и ошибкой.

Введите формулу как формулу массива Ctrl+Shift+Enter


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеНичего не поняла, приложите файл с изменением и ошибкой.

Введите формулу как формулу массива Ctrl+Shift+Enter

Автор - Pelena
Дата добавления - 20.02.2022 в 21:41
Мир MS Excel » Вопросы и решения » Вопросы по Excel » доработка формулы или как расчитать сумму (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2022 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!