Обработка таблицы макросом

[Dorina]

Здравствуйте!
Очень нужна помощь! Есть незамысловатая таблица (во вложении Обработка таблицы Лист 1), количество строк может быть очень большим. Нужно ее преобразовывать в вид Лист 2, т.е. просуммировать все опоздания по месяцу. Значения ФИО много разных, но всегда будут совпадать в написании буква в букву. Знаю, что делается сводной таблицой, но хочется именно макросом, т.к. человеку, который будет с этим работать, нужно максимально быстро + таблиц будет много + боюсь не разберется. У меня макрос получается очень кривой, и обязательно надо сначала отдельно задать все ФИО. Плюс не могу задать выбор периода. Подскажите пожалуйста!

[nilem]

Может, так как-то. В С1 выбираем месяц и зеленая стрелка.

[Dorina]

Спасибо большое, пойду разбираться!

[doober]

Я ,как всегда,решаю запросом.Маленькие коменты внутри

[Dorina]

Спасибо огромное!

[Hugo121]

Вариант полуавтоматический:
1. Слева (левее сумм) добавляем столбец, куда соединяем фамилию и месяц простой формулой =B2 & "|" & MONTH(D2)
Разделитель необязательно, но так лучше, да и потом разделить на фамилию и месяц не сложно.
2. Выделяем диапазоно от нового столбца до столбца с суммами прогулов (все нужные данные), запускаем макрос UniqSummUniversal из открытого в фоне файла по ссылке
http://www.planetaexcel.ru/forum.php?thread_id=26105

Sub UniqSummUniversal()
'Выделить диапазон, где в первом столбце - уникальные, в последнем - суммы
Dim a(), oDict As Object, i As Long, temp As String
Dim ind As Long
a = Selection.Value
ind = UBound(a, 2)
Set oDict = CreateObject("Scripting.Dictionary")
oDict.CompareMode = 1
For i = 1 To UBound(a)
If IsNumeric(a(i, ind)) Then
If Not IsEmpty(a(i, ind)) Then
temp = Trim(a(i, 1))
If Not oDict.Exists(temp) Then
oDict.Add temp, CStr(a(i, ind))
Else
oDict.Item(temp) = CStr(--oDict.Item(temp) + a(i, ind))
End If
End If
End If
Next

On Error Resume Next
With Workbooks.Add.Worksheets(1)
.Range("A1").Resize(oDict.Count) = Application.Transpose(oDict.keys)
.Range("B1").Resize(oDict.Count) = Application.Transpose(oDict.items)
End With
On Error GoTo 0

End Sub

В новом файле получаем данные вида

Иванов Иван|3 0.173611111
Иванова Мария|3 9.03E-02
Петров Петр|4 0.208333333
Иванов Иван|4 0.170138889

Применив "формат по образцу" из исходного файла, получаем
Иванов Иван|3 4:10
Иванова Мария|3 2:10
Петров Петр|4 5:00
Иванов Иван|4 4:05

Далее можно отсортировать, разбить первую колонку на две.
Не совсем то, что в образце, но уже нагляднее.
Кстати, в образце Иванов Иван в марте посчитан как 3:10:00
Но есть "НО" - на работе на английской 2000 версии работает безупречно, дома на русской 2007 неправильно обрабатываются значения времени в экспоненциальной форме. Как победить - пока не нашёл...
P.S. Общее время без разбивки по месяцам можно получить сразу из исходной таблицы, ничего не переделывая, только в итоге применив "формат по образцу" к времени.
Иванов Иван 8:35
Иванова Мария 6:20
Петров Петр 5:00
Петрова Татьяна 2:20
Сидоров Сидор 1:10

[Dorina]

Сообщение от doober

Я ,как всегда,решаю запросом.Маленькие коменты внутри

Спасибо! Очень извиняюсь, но у меня еще вопрос - как сделать так, чтоб обрабатывались не все данные, а только по условию - там, где в последней колонке "Причина" написано ИСТИНА. Файл приложила, на кнопку на первом листе не обращайте внимание.

[doober]

Выделеное красным добавил

  
sSql = "TRANSFORM Sum(F8) AS [время отсутствия] " & _
"SELECT F1 AS [ФИО] , F2 AS [Департамент]  ,  Sum(F8) AS [Итого]" & _
" FROM [Лист1$A2:I65500] T  WHERE  F9 Like 'ИСТИНА' " & _
"GROUP BY F1, F2 ORDER BY  F1" & _
" PIVOT Format([F3],'mmm') In ('янв','фев','мар','апр','май','июн','июл','авг','сен','окт','ноя','дек');"

Кнопка классная

[Dorina]

СПАСИБО!!!! Все работает!

[R Dmitry]

Сообщение от Dorina

СПАСИБО!!!! Все работает!

Здесь можно и без макросов реализовать.

1.Подключаемся к исходному файлу
2.выгружаем данные в промежуточную таблицу
3.добавляем вычисляемый месяц
4.подключаем сводную и наслаждаемся

2 и 3 шаг можно и в Qwery реализовать и тогда промежуточной таблицы вообще не надо

Макросов нет и все работает

Хотя мне макросом больше нравиться