Страницы: 1
RSS
Найти сколько раз встречается текст в столбце по условию
 
Суть задачи заключается в подсчёте количества выполненных заданий. Если работник выполнял задание один, то считаем 1, если в составе двух сотрудников - делим пополам, если задание выполняли трое, то сотрудник выполнил 0,33 от всего задания.
То есть, нужно найти сколько раз встречается текст в столбце по условию. Условие заключается в следующем: если фамилия встречается один раз в ячейке - умножаем на 1, если среди двух фамилий - умножаем на 0,5, если среди трёх - умножаем на 0,33.

Количество сотрудников в ячейке можно определить по количеству запятых. применил формулу:
Код
=ДЛСТР(B2)-ДЛСТР(ПОДСТАВИТЬ(B2;",";""))

Сколько раз встречается сотрудник определил по формуле:
Код
=СЧЁТЕСЛИ(B2;"*Иванов*")

Далее можно при кол-ве запятых = 0 умножить на 1, при наличии запятой умножить на 0,5 и т.д. Но как это применить для Фамилии во всём диапазоне не могу сообразить. Фамилии могут иметь любой порядок чередования, разделены запятыми без пробела, присутствуют пустые ячейки, которые нельзя удалить. Excel 2007-2010
 
формула массива:
Код
=СУММ((ДЛСТР($B$2:$B$9)>ДЛСТР(ПОДСТАВИТЬ($B$2:$B$9;D11;"")))/(1+ДЛСТР($B$2:$B$9)-ДЛСТР(ПОДСТАВИТЬ($B$2:$B$9;",";""))))
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Код
Sub mrshkei()
    Dim arr, arr2, arr3, arr4, i As Long, j As Long, n As Long, lr As Long
    Dim col As New Collection
    
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    arr = Range("A2:B" & lr)
    
    For i = LBound(arr) To UBound(arr)
    arr2 = Split(arr(i, 2), ",")
        For j = LBound(arr2) To UBound(arr2)
            On Error Resume Next
            col.Add Trim(arr2(j)), CStr(Trim(arr2(j)))
        Next j
    Next i
    ReDim arr3(1 To col.Count, 1 To 3)
    For i = 1 To col.Count
    arr3(i, 1) = col(i)
        For j = LBound(arr) To UBound(arr)
            If InStr(arr(j, 2), col(i)) > 0 Then
                x = Round(1 / (UBound(Split(arr(j, 2), ",")) + 1), 2)
                If arr3(i, 2) = Empty Then arr3(i, 2) = x Else arr3(i, 2) = arr3(i, 2) & "+" & x
                arr3(i, 3) = arr3(i, 3) + x
            End If
        Next j
    Next i
    Range("D11").Resize(UBound(arr3), 3) = arr3
End Sub
Не бойтесь совершенства. Вам его не достичь.
 
Считать количество выполненных заданий - это лишнее.
=СУММПРОИЗВ(ЕЧИСЛО(ПОИСК(D11;$B$2:$B$9))/(ДЛСТР($B$2:$B$9)-ДЛСТР(ПОДСТАВИТЬ($B$2:$B$9;",";))+1))
Можно СУММПРОИЗВ заменить на СУММ, но тогда формулу нужно вводить как формулу массива.
 
не массивная
=SUMPRODUCT((FIND(D11;$B$2:$B$9&D11)<LEN($B$2:$B$9))/(1+LEN($B$2:$B$9)-LEN(SUBSTITUTE($B$2:$B$9;",";""))))
По вопросам из тем форума, личку не читаю.
 
vikttur, заработало сразу!
Два других варианта пока разбираюсь как устроен синтаксис. На VBA ни одной строчки не понял, надо осваивать сам язык ))

Огромное всем спасибо, с Новым Годом !
 
Цитата
написал:
Два других варианта пока разбираюсь как устроен синтаксис
разберитесь пока что значит "формула массива" и как ее ввести  
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Почитал, ввёл через Ctrl+Shift+Enter и всё заработало как надо. Об этом даже и не слышал раньше )))
 
Возник ещё вопрос. Так как я применяю формулы, которые вы подсказали не к диапазону $B$2, а ко всему столбцу B (количество строк неизвестно) - изменил формулу на:

=СУММПРОИЗВ(ЕЧИСЛО(ПОИСК(D12;$B:$B))/(ДЛСТР($B:$B)-ДЛСТР(ПОДСТАВИТЬ($B:$B;",";))+1))

и после этого Excel существенно призадумался. Я понимаю, что формула считает вниз до бесконечности и отнимает время. Это нормально ?
 
Цитата
Денис написал:
формула считает вниз до бесконечности
Число строк в Excel вполне конкретное. Оно не может быть бесконечным )
 
Цитата
Денис написал:
формула считает вниз до бесконечности
то она б и считала бесконечно долго. Скорее всего у вас предсказуемый объем данных. Ограничьте диапазон  с запасом, даже если это 100000 то это в десять раз меньше чем то как вы сделали.
По вопросам из тем форума, личку не читаю.
 
Цитата
Денис написал: Я понимаю, что формула считает вниз до бесконечности и отнимает время.
Включите секундомер. Пробежите 10 метров. Выключите секундомер. Запишите полученное время1.
Включите секундомер. Пробежите 10 километров. Если еще не умерли :) , выключите секундомер. Если полученное время2 меньше 26.11,00 - поздравляю, Вы установили новый мировой рекорд :):)
В любом случае сравните время1 и время2 и сделайте выводы

Задавайте ограниченный диапазон, например $B2:$B99
 
Цитата
vikttur написал:
Пробежите 10 километров
2-гл января?
 
дешевле (по времени расчетов) написать чуть длиннее формулу, которая посчитает размер исходного диапазона и будет проводить расчеты с ним, а не со всем столбиком В:В
Код
=СУММПРОИЗВ(ЕЧИСЛО(ПОИСК(D11;$B$2:ИНДЕКС(B:B;МАКС(A:A)+1)))/(1+ДЛСТР($B$2:ИНДЕКС(B:B;МАКС(A:A)+1))-ДЛСТР(ПОДСТАВИТЬ($B$2:ИНДЕКС(B:B;МАКС(A:A)+1);",";""))))
а МАКС и ИНДЕКС, которые по прежнему обращаются вроде ко всему столбцу В:В и А:А - это быстрые функции, разница в скорости работы между явно заданным диапазоном и вычисленным этими функция окажется заметной только при наличии точных измерений, а по ощущениям ее не будет (не заметите)
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
ну в целом Игорь прав, но это хорошо когда есть к чему прицепится, как в данном случае столбец A, или данные не имеет разрывов и тогда можно использовать подсчет значений в столбце .... но и в том и другом случае лучше вынести расчет количества использованных строк, в отдельную ячейку и считать один раз.
По вопросам из тем форума, личку не читаю.
Страницы: 1
Наверх