доработка формулы или как расчитать сумму

Дата: Воскресенье, 13.02.2022, 19:43 | Сообщение № 1

Группа: Пользователи

Ранг: Участник

Сообщений: 53

Репутация: 0 ±

Замечаний: 20% ±

2017

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

имеем заполненную таблицу на первом листе"сверхурочка" с часами по дням, далее данные переносятся в таблицу на лист "расчет"
необходимо рассчитать сумму: первые два часа дня умножаем на полуторный коэффициент, все последующие – на коэффициент 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)

Дата: Воскресенье, 13.02.2022, 21:19 | Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 19184

Репутация: 4420 ±

Замечаний: ±

Excel 365 & Mac Excel

Здравствуйте.
Как поняла

К сообщению приложен файл: 0343012.xlsm (58.0 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Дата: Понедельник, 14.02.2022, 19:23 | Сообщение № 3

Группа: Пользователи

Ранг: Участник

Сообщений: 53

Репутация: 0 ±

Замечаний: 20% ±

2017

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' )

замудренно?

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

Дата: Понедельник, 14.02.2022, 19:29 | Сообщение № 4

Группа: Админы

Ранг: Местный житель

Сообщений: 19184

Репутация: 4420 ±

Замечаний: ±

Excel 365 & Mac Excel

Цитата nespica2012, 14.02.2022 в 19:23, в сообщении № 3 (

)

могу как-то еще расписать алгоритм

лучше посчитайте и заполните вручную, что где должно получиться в Вашем примере

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Дата: Понедельник, 14.02.2022, 20:21 | Сообщение № 5

Группа: Пользователи

Ранг: Участник

Сообщений: 53

Репутация: 0 ±

Замечаний: 20% ±

2017

Pelena,
в файле, под таблицей расписал то, что необходимо получить на примере работника6

К сообщению приложен файл: 9558810.xlsm (60.4 Kb)

Дата: Понедельник, 14.02.2022, 20:42 | Сообщение № 6

Группа: Пользователи

Ранг: Участник

Сообщений: 53

Репутация: 0 ±

Замечаний: 20% ±

2017

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

для ячейки "Н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

Дата: Суббота, 19.02.2022, 21:41 | Сообщение № 7

Группа: Пользователи

Ранг: Участник

Сообщений: 53

Репутация: 0 ±

Замечаний: 20% ±

2017

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

во вложении файл,
формула написана для примера на несколько дней (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))))))

я это я еще в середине пути...потом надо прописать еще условия для некоторой категории работников, да плюс вводить условия если меняется вид работ в течении смены

может уже шагать по пути VBA

К сообщению приложен файл: 0343012___2__.xlsm (72.8 Kb)

Сообщение отредактировал nespica2012 - Воскресенье, 20.02.2022, 09:57

Дата: Воскресенье, 20.02.2022, 19:12 | Сообщение № 8

Группа: Админы

Ранг: Местный житель

Сообщений: 19184

Репутация: 4420 ±

Замечаний: ±

Excel 365 & Mac Excel

Вариант в файле, только Ваши примеры расчётов расходятся с описанием. Вы пишете, что остаток часов надо умножать на 2, а в примерах расчёта не умножаете.
И ещё, если часов не может быть меньше 2, то формула значительно сократится

К сообщению приложен файл: 4524393.xlsm (59.2 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Дата: Воскресенье, 20.02.2022, 21:25 | Сообщение № 9

Группа: Пользователи

Ранг: Участник

Сообщений: 53

Репутация: 0 ±

Замечаний: 20% ±

2017

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

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

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

К сообщению приложен файл: 7309315.jpg (60.9 Kb)

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

Дата: Воскресенье, 20.02.2022, 21:41 | Сообщение № 10

Группа: Админы

Ранг: Местный житель

Сообщений: 19184

Репутация: 4420 ±

Замечаний: ±

Excel 365 & Mac Excel

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

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

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816