Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Выделить диапазон условий до определенных значений/символов в функции СУММЕСЛИМН
 
#1
17.10.2024 09:54:42
Добрый день, уважаемые знатоки EXCEL)

Подскажите, пожалуйста, как можно в указанном файле выделить диапазон условия до определенных значений/символов в функции СУММЕСЛИМН?
Как видно по формуле, мне надо отдельно вычеслить часы по оплате по часовому тарифу, и отдельно праздничные часов по месяцам.
Учитывая, что в разных месяцах начисления были в разном количестве, надо вручную менять выделение диапазона условии.

Можно ли это решить какой-нибудь функцией?

Спасибо.
 
 
 
#2
17.10.2024 10:10:44
omip,
Если использовать другую формулу (да хоть массивно СУММ()), то можно учитывать строки, в пределах которых нужно суммировать.
Я так делал с СУММПРОИЗВ().
Т.е. указываем в формуле весь диапазон, а аргументами берём в расчёт только нужные строки.
Например вот так их можно найти:
=ПОИСКПОЗ(D7& "*";$A$5:$A$72;0) & " - " & ПОИСКПОЗ(D8& "*";$A$5:$A$72;0)-1
Внизу добавил "итого" чтоб найти диапазон для января.
 
 
 
#3
17.10.2024 10:14:24
Цитата
написал:
Внизу добавил "итого" чтоб найти диапазон для января.
Вложите, пож-та, тот файл с формулами, который вы сделали) спасибо.
 
 
 
#4
17.10.2024 10:18:32
Hugo,

вложите, пож-та, тот файл с формулами, который вы сделали) спасибо.
 
 
 
#5
17.10.2024 10:35:26
Hugo,
а как дальше =ПОИСКПОЗ применять для вычисления самих часов? не совсем понял дальнейшие действия...(
 
 
 
#6
17.10.2024 10:37:06
omip, не так всё быстро, ту одну формулу я написал в теме, остальное вот доделал в файле.
Вот для других если интересно чтоб файл не качать формула:
Код
=СУММПРОИЗВ($B$5:$B$72
*ЕСЛИОШИБКА(ПОИСК("часов";$A$5:$A$72)>0;0)
*ЕСЛИОШИБКА(ПОИСК("сохр";$A$5:$A$72)-ПОИСК("сохр";$A$5:$A$72);1)
*((СТРОКА($B$5:$B$72)-4)>=ПОИСКПОЗ(D7&"*";$A$5:$A$72;0))
*((СТРОКА($B$5:$B$72)-4)<ПОИСКПОЗ(D8&"*";$A$5:$A$72;0)))
Изменено: Hugo - 17.10.2024 11:00:41
 
 
 
#7
17.10.2024 10:44:55
Hugo,

супер!

Спасибо!!!
 
 
 
#8
17.10.2024 10:46:05
del
Изменено: Hugo - 17.10.2024 10:55:31
 
 
 
#9
17.10.2024 10:48:16
Hugo,

где и как можно пройти обучение, чтобы уметь также совмещать несколько функции в Excel?

На этой платформе видел несколько обучений, только не знаю, какое выбрать, чтобы закрыть вышеуказанную потребность?)
 
 
 
#10
17.10.2024 10:51:05
omip, не подскажу.
Но в файле косячок с сохр... был
Изменено: Hugo - 17.10.2024 10:55:20
 
 
 
#11
17.10.2024 10:55:07
omip, заменил файл выше, поменяйте у себя.
 
 
 
#12
17.10.2024 10:55:32
Hugo,
Цитата
написал:
Но в файле косячок с сохр...
Да, с сохр не правильно считает(
столько вариантов пробовал, не получается подправить... видимо из-за отсутствия понимания функции(

можете подкорректировать?
 
 
 
#13
17.10.2024 10:56:29
Hugo,
Цитата
написал:
omip , заменил файл выше, поменяйте у себя.
Огромное спасибо!!!
 
 
 
#14
17.10.2024 10:58:42
Цитата
omip написал:
видимо из-за отсутствия понимания функции
там смысл если найдено "сохр" то умножить на 0, если не найдено то на 1.
Там я ещё раз файл заменил - удалил лишне скобки
Изменено: Hugo - 17.10.2024 11:01:26
 
 
 
#15
17.10.2024 13:44:52
Hugo,

вашу формулу вставил на указанный файл. Но там нужно посчитать в разрезе ФИО или таб. номера.
Было бы удобно, когда выгружаешь отчет по одному подразделению по всем работникам и нужно посчитать часы по каждому работнику отдельно.

Можно подправить функцию под такой запрос?) если да, прошу помочь...

Спасибо!!!
 
 
 
#16
17.10.2024 13:54:15
Не, проще выгрузить нормальный отчёт в табличном виде.
Из такого можно макросом сделать сводную, но это тоже работа уже, интереса никакого нет.
 
 
 
#17
17.10.2024 13:55:12
Hugo, понятно, спасибо)
 
 
 
#18
17.10.2024 14:15:04
omip, Макросом (например в UDF) можно так:
Сперва составляем список ФИО, и список месяцев - шахматку наверное. Это будут критерии для UDF.
В ней кодом:
1. циклом ищем месяц
2. далее ниже ищем ФИО
3. далее ниже уже анализируем все строки, суммируем по критериям как было в формулах "*часов*" и "<>*сохр*"  пока IndentLevel строки не будет 6
Всё.
 
 
 
#19
17.10.2024 15:06:23
omip, есть вопрос - фио один раз в месяце, или могут быть дубли - работа в разных лабораториях в одном месяце?
Потому что UDF уже работает, но этот вопрос пока не учтён - второй раз ФИО в месяце не учитывает, но таких вроде как и нет.
Учёл, на всякий случай - теперь считает и таких, если будут.
Изменено: Hugo - 17.10.2024 16:07:44
 
 
 
#20
18.10.2024 08:10:51
Hugo, Вы СУПЕР! Спасибо!!!!!!

Цитата
написал:
Учёл, на всякий случай - теперь считает и таких, если будут.
 
 
 
#21
02.11.2024 09:18:50
Hugo, добрый день)

В файле с макросами, из-за того, что добавились сотрудники и месяц (октябрь) изменился выгруженный отчет (TDSheet (2)).
Я поменял лист и диапазон в формуле, но почему то уже неправильно считается часы.

Надо что-то менять в макросах? Подскажите, пожалуйста!

Спасибо)
 
 
 
#22
02.11.2024 09:43:01
Код
Function casov(diap As Range, FIO, mesjac, krit$)
Dim i&, FIO_ As Boolean, mesjac_ As Boolean, s$, sum_ As Double, lvl&, mesjac_lvl&
krit = LCase(krit)

For i = 1 To diap.Rows.Count
lvl = diap.Cells(i, 1).IndentLevel
If mesjac_ And lvl <= mesjac_lvl Then Exit For 'если новый месяц
If diap.Cells(i, 1) = mesjac Then
    mesjac_ = True 'нашли месяц
    mesjac_lvl = lvl
End If
If mesjac_ Then
    If FIO_ And lvl = 6 Then FIO_ = False 'если новый сотрудник
    If diap.Cells(i, 1) = FIO Then FIO_ = True 'нашли сотрудника
End If 'mesjac_
If FIO_ Then
    s = LCase(diap.Cells(i, 1).Value)
    If InStr(1, s, krit$) Then
    If InStr(1, s, "сохр") = 0 Then
    sum_ = sum_ + diap.Cells(i, 2).Value
    End If
    End If
End If ' FIO_
Next

casov = sum_
End Function
Так?
 
 
 
#23
02.11.2024 09:56:41
МатросНаЗебре, уже работает) СПАСИБО ОГРОМНОЕ!!

а в чем была проблема?) я к тому, что каждый раз, когда выгружаю новый отчет (по другим работникам и/или подразделениям) придется менять макросы?((
 
 
 
#24
02.11.2024 10:00:24
Цитата
написал:
а в чем была проблема?)
Выход из цикла осуществлялся по конкретному уровню отступа. Сейчас уровень определяется автоматически в зависимости от уровня отступа в ячейке месяца.
Цитата
написал:
я к тому, что каждый раз, когда выгружаю новый отчет (по другим работникам и/или подразделениям) придется менять макросы?((
Если изменится только отступ, то макрос менять не придётся.
 
 
 
#25
02.11.2024 10:50:31
МатросНаЗебре,

формула ищет только, как я понял, в первом Октябрь 2024, который он находит в начале таблицы.
В зависимости от изм. наименования подразделении, месяцы могут встречаться несколько раз. Можно решить данную задачу? помогите плиз!

Спасибо...
 
 
 
#26
02.11.2024 11:03:25
Код
Function casov(diap As Range, FIO, mesjac, krit$)
Dim i&, FIO_ As Boolean, mesjac_ As Boolean, s$, sum_ As Double, lvl&, mesjac_lvl&
krit = LCase(krit)

For i = 1 To diap.Rows.Count
lvl = diap.Cells(i, 1).IndentLevel
If mesjac_ And lvl <= mesjac_lvl Then mesjac_ = False 'если новый месяц
If diap.Cells(i, 1) = mesjac Then
    mesjac_ = True 'нашли месяц
    mesjac_lvl = lvl
End If
If mesjac_ Then
    If FIO_ And lvl = 6 Then FIO_ = False 'если новый сотрудник
    If diap.Cells(i, 1) = FIO Then FIO_ = True 'нашли сотрудника
End If 'mesjac_
If FIO_ Then
    s = LCase(diap.Cells(i, 1).Value)
    If InStr(1, s, krit$) Then
    If InStr(1, s, "сохр") = 0 Then
    sum_ = sum_ + diap.Cells(i, 2).Value
    End If
    End If
End If ' FIO_
Next

casov = sum_
End Function
 
 
 
Страницы: 1
Наверх