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

 

= Мир MS Excel/Группировка данных - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Группировка данных
Группировка данных
Evgen Дата: Среда, 13.10.2010, 14:35 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Здравствуйте Ув. форумчане. Никак не могу справиться с такой задачей:
Таблица вида:
Дата платежа Зачислено в валюте договора Номер телефона
05.10.2010 1300 9031111111
05.10.2010 1400 9052222222
05.10.2010 1000 9031111111
05.10.2010 3800 9052222222
05.10.2010 3800 9657777777
05.10.2010 3800 9657777777
05.10.2010 3800 9099999999
05.10.2010 3800 9052222222

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

Спасибо.

 
Ответить
СообщениеЗдравствуйте Ув. форумчане. Никак не могу справиться с такой задачей:
Таблица вида:
Дата платежа Зачислено в валюте договора Номер телефона
05.10.2010 1300 9031111111
05.10.2010 1400 9052222222
05.10.2010 1000 9031111111
05.10.2010 3800 9052222222
05.10.2010 3800 9657777777
05.10.2010 3800 9657777777
05.10.2010 3800 9099999999
05.10.2010 3800 9052222222

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

Спасибо.

Автор - Evgen
Дата добавления - 13.10.2010 в 14:35
Serge_007 Дата: Среда, 13.10.2010, 14:43 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Здравствуйте Evgen.
Самое простое решение Вашей задачи - это сводная таблица.
См. вложение.
К сообщению приложен файл: Evgen.xls (20.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеЗдравствуйте Evgen.
Самое простое решение Вашей задачи - это сводная таблица.
См. вложение.
Автор - Serge_007
Дата добавления - 13.10.2010 в 14:43
Serge_007 Дата: Среда, 13.10.2010, 14:55 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Формулами.
1. Номера телефонов:
=ИНДЕКС($C$2:$C$9;НАИБОЛЬШИЙ(ЕСЛИ(ПОИСКПОЗ($C$2:$C$9;$C$2:$C$9;0)=СТРОКА($A$1:$A$8);СТРОКА($A$1:$A$8));СТРОКА()-1))
Формула массива.
2. Деньги:
=СУММЕСЛИ($C$2:$C$9;G2;$B$2:$B$9)

3. Дата:
=ИНДЕКС($A$2:$A$9;ПОИСКПОЗ(G2;$C$2:$C$9;0))
К сообщению приложен файл: Evgen_2.xls (22.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеФормулами.
1. Номера телефонов:
=ИНДЕКС($C$2:$C$9;НАИБОЛЬШИЙ(ЕСЛИ(ПОИСКПОЗ($C$2:$C$9;$C$2:$C$9;0)=СТРОКА($A$1:$A$8);СТРОКА($A$1:$A$8));СТРОКА()-1))
Формула массива.
2. Деньги:
=СУММЕСЛИ($C$2:$C$9;G2;$B$2:$B$9)

3. Дата:
=ИНДЕКС($A$2:$A$9;ПОИСКПОЗ(G2;$C$2:$C$9;0))
Автор - Serge_007
Дата добавления - 13.10.2010 в 14:55
Evgen Дата: Среда, 13.10.2010, 15:06 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

1. В сводной таблице данные у меня почему-то не плюсуются по суммам, а просто откидываются, кроме первого значения.
2. Второй вариант я вообще не понял...
Прошу прощения за свою непонятливость.
 
Ответить
Сообщение1. В сводной таблице данные у меня почему-то не плюсуются по суммам, а просто откидываются, кроме первого значения.
2. Второй вариант я вообще не понял...
Прошу прощения за свою непонятливость.
Автор - Evgen
Дата добавления - 13.10.2010 в 15:06
Serge_007 Дата: Среда, 13.10.2010, 15:10 | Сообщение № 5
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (Evgen писал(а)):
1. В сводной таблице данные у меня почему-то не плюсуются по суммам, а просто откидываются, кроме первого значения.
2. Второй вариант я вообще не понял...

1. Как это "откидываются"?
Посмотрите пример: телефон 9031111111 Зачислено в валюте договора 1300 и 1000 - в сводной 2300. Что не получается?
2. Что не понятно во втором варианте? Файл смотрели?

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (Evgen писал(а)):
1. В сводной таблице данные у меня почему-то не плюсуются по суммам, а просто откидываются, кроме первого значения.
2. Второй вариант я вообще не понял...

1. Как это "откидываются"?
Посмотрите пример: телефон 9031111111 Зачислено в валюте договора 1300 и 1000 - в сводной 2300. Что не получается?
2. Что не понятно во втором варианте? Файл смотрели?
Автор - Serge_007
Дата добавления - 13.10.2010 в 15:10
Evgen Дата: Среда, 13.10.2010, 15:19 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Цитата
Как это "откидываются"?

В Вашем варианте именно 2300 получается, а у меня остается 1300, а вторая сумма просто исчезает.

Цитата
Что не понятно во втором варианте? Файл смотрели?

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

Готов заплатить за создание скрипта. icq 327157637

 
Ответить
Сообщение
Цитата
Как это "откидываются"?

В Вашем варианте именно 2300 получается, а у меня остается 1300, а вторая сумма просто исчезает.

Цитата
Что не понятно во втором варианте? Файл смотрели?

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

Готов заплатить за создание скрипта. icq 327157637

Автор - Evgen
Дата добавления - 13.10.2010 в 15:19
Serge_007 Дата: Среда, 13.10.2010, 15:21 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (Evgen писал(а)):
Таблица, которую я хочу привести в нормальный вид имеет гораздо больше строк, нежели я указал в примере.

Сколько весит файл?

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (Evgen писал(а)):
Таблица, которую я хочу привести в нормальный вид имеет гораздо больше строк, нежели я указал в примере.

Сколько весит файл?
Автор - Serge_007
Дата добавления - 13.10.2010 в 15:21
Evgen Дата: Среда, 13.10.2010, 15:26 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Они разные от 25 кб до нескольких мб
 
Ответить
СообщениеОни разные от 25 кб до нескольких мб
Автор - Evgen
Дата добавления - 13.10.2010 в 15:26
Serge_007 Дата: Среда, 13.10.2010, 15:27 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (Evgen писал(а)):
Они разные от 25 кб до нескольких мб

Высылайте в личку.
Адрес в подписи.

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (Evgen писал(а)):
Они разные от 25 кб до нескольких мб

Высылайте в личку.
Адрес в подписи.
Автор - Serge_007
Дата добавления - 13.10.2010 в 15:27
Evgen Дата: Среда, 13.10.2010, 15:31 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

2 минуты.
 
Ответить
Сообщение2 минуты.
Автор - Evgen
Дата добавления - 13.10.2010 в 15:31
Serge_007 Дата: Суббота, 23.10.2010, 23:40 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Решение предложенное мной в сообщении № 3 полностью отвечало условиям автора.
Тему можно считать закрытой.

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
СообщениеРешение предложенное мной в сообщении № 3 полностью отвечало условиям автора.
Тему можно считать закрытой.
Автор - Serge_007
Дата добавления - 23.10.2010 в 23:40
Hugo Дата: Воскресенье, 24.10.2010, 18:17 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3854
Репутация: 814 ±
Замечаний: 0% ±

365
Если файлы большие и надо сделать процедуру полностью автоматической - можно написать макрос: данные в массив, затем извлекаем уникальные в словарь или коллекцию, а в Item собираем сумму. В итоге выгружаем результат на лист - этот или другой.
Например так:
Option Explicit Sub Otbor()         Dim a(); oDict As Object; i As Long; temp As String                     a = Range("a1:c" & Range("C" & Rows.Count).End(xlUp).Row).Value         Set oDict = CreateObject("Scripting.Dictionary")         For i = 1 To UBound(a)             temp = Тrim(a(i; 3))             If Not oDict.Exists(temp) Then                 oDict.Add temp; CStr(a(i; 2))             Else                 oDict.Item(temp) = CStr(--oDict.Item(temp) + a(i; 2))             End If         Next       With ТhisWorkbook.Worksheets(1)           .Range("D1").Resize(oDict.Count) = Application.Transpose(oDict.keys)           .Range("E1").Resize(oDict.Count) = Application.Transpose(oDict.items)       End With End Sub

На массивах с числом строк 65537 и более Transpose работать не будет. Тогда суммы и значения можно собирать не в словарь, а в другой массив. А словарь использовать только для идентификации уникальных значений, а как Item брать номер позиции во втором массиве.

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD
 
Ответить
СообщениеЕсли файлы большие и надо сделать процедуру полностью автоматической - можно написать макрос: данные в массив, затем извлекаем уникальные в словарь или коллекцию, а в Item собираем сумму. В итоге выгружаем результат на лист - этот или другой.
Например так:
Option Explicit Sub Otbor()         Dim a(); oDict As Object; i As Long; temp As String                     a = Range("a1:c" & Range("C" & Rows.Count).End(xlUp).Row).Value         Set oDict = CreateObject("Scripting.Dictionary")         For i = 1 To UBound(a)             temp = Тrim(a(i; 3))             If Not oDict.Exists(temp) Then                 oDict.Add temp; CStr(a(i; 2))             Else                 oDict.Item(temp) = CStr(--oDict.Item(temp) + a(i; 2))             End If         Next       With ТhisWorkbook.Worksheets(1)           .Range("D1").Resize(oDict.Count) = Application.Transpose(oDict.keys)           .Range("E1").Resize(oDict.Count) = Application.Transpose(oDict.items)       End With End Sub

На массивах с числом строк 65537 и более Transpose работать не будет. Тогда суммы и значения можно собирать не в словарь, а в другой массив. А словарь использовать только для идентификации уникальных значений, а как Item брать номер позиции во втором массиве.
Автор - Hugo
Дата добавления - 24.10.2010 в 18:17
Alex_ST Дата: Понедельник, 25.10.2010, 09:20 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3219
Репутация: 622 ±
Замечаний: 0% ±

2003
Цитата (Hugo писал(а)):
На массивах с числом строк 65537 и более Transpose работать не будет

Игорь, ты не в курсе: это только для 2003-го с его ограничением как раз в 65536 строк (тогда это явно не глюк Application.Transpose, а логичное ограничение) или и на более поздних (2007, 2010) Ёкселях то же самое?


С уважением,
Алексей
MS Excel 2003 - the best!!!

Сообщение отредактировал Alex_ST - Понедельник, 25.10.2010, 09:21
 
Ответить
Сообщение
Цитата (Hugo писал(а)):
На массивах с числом строк 65537 и более Transpose работать не будет

Игорь, ты не в курсе: это только для 2003-го с его ограничением как раз в 65536 строк (тогда это явно не глюк Application.Transpose, а логичное ограничение) или и на более поздних (2007, 2010) Ёкселях то же самое?
Автор - Alex_ST
Дата добавления - 25.10.2010 в 09:20
Alex_ST Дата: Понедельник, 25.10.2010, 09:27 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3219
Репутация: 622 ±
Замечаний: 0% ±

2003
А по поводу твоего кода, т.к.
Цитата (Evgen писал(а)):
Они разные от 25 кб до нескольких мб
, я бы всё-таки на всякий случай добавил в конце Set oDict = Nothing чтобы память очищать.
Или чуть подправил твой код так, как научили старшие товарищи на Планете:
Sub Otbor2()      Dim Arr(); i&; temp$      Arr = Range("A1:C" & Range("C" & Rows.Count).End(xlUp).Row).Value      With Create_Object("Scripting.Dictionary")         For i = 1 To UBound(Arr)            temp = Тrim(CStr(Arr(i; 3)))            If Not .Exists(temp) Then               .Add temp; CStr(Arr(i; 2))            Else               .Item(temp) = CStr(--.Item(temp) + Arr(i; 2))            End If         Next         ТhisWorkbook.Worksheets(1).Range("D1").Resize(.Count) = Application.Transpose(.Keys)         ТhisWorkbook.Worksheets(1).Range("E1").Resize(.Count) = Application.Transpose(.Items)      End With End Sub

P.S. Create_Object, естественно, нужно писать слитно, но шизоидная система безопасности интранета на работе это не пропускает!



С уважением,
Алексей
MS Excel 2003 - the best!!!

Сообщение отредактировал Alex_ST - Понедельник, 25.10.2010, 09:29
 
Ответить
СообщениеА по поводу твоего кода, т.к.
Цитата (Evgen писал(а)):
Они разные от 25 кб до нескольких мб
, я бы всё-таки на всякий случай добавил в конце Set oDict = Nothing чтобы память очищать.
Или чуть подправил твой код так, как научили старшие товарищи на Планете:
Sub Otbor2()      Dim Arr(); i&; temp$      Arr = Range("A1:C" & Range("C" & Rows.Count).End(xlUp).Row).Value      With Create_Object("Scripting.Dictionary")         For i = 1 To UBound(Arr)            temp = Тrim(CStr(Arr(i; 3)))            If Not .Exists(temp) Then               .Add temp; CStr(Arr(i; 2))            Else               .Item(temp) = CStr(--.Item(temp) + Arr(i; 2))            End If         Next         ТhisWorkbook.Worksheets(1).Range("D1").Resize(.Count) = Application.Transpose(.Keys)         ТhisWorkbook.Worksheets(1).Range("E1").Resize(.Count) = Application.Transpose(.Items)      End With End Sub

P.S. Create_Object, естественно, нужно писать слитно, но шизоидная система безопасности интранета на работе это не пропускает!

Автор - Alex_ST
Дата добавления - 25.10.2010 в 09:27
Hugo Дата: Понедельник, 25.10.2010, 10:23 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3854
Репутация: 814 ±
Замечаний: 0% ±

365
Про Transpose говорят, что оно так и осталось.
Но на 2000 ещё хуже - глючит у меня и на меньших объёмах, на каких точно сказать не могу. Но были случаи, когда один и тот же код на 2003 работал, а на 2000 нет. И именно на Transpose выкидывало ошибку.

webmoney: E265281470651 Z422237915069
USDT TRC20: TN8XeEF17o5KPBD9pNwYzNyruycuAc2mVD
 
Ответить
СообщениеПро Transpose говорят, что оно так и осталось.
Но на 2000 ещё хуже - глючит у меня и на меньших объёмах, на каких точно сказать не могу. Но были случаи, когда один и тот же код на 2003 работал, а на 2000 нет. И именно на Transpose выкидывало ошибку.
Автор - Hugo
Дата добавления - 25.10.2010 в 10:23
Alex_ST Дата: Понедельник, 25.10.2010, 12:22 | Сообщение № 16
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3219
Репутация: 622 ±
Замечаний: 0% ±

2003
Интересно, а с использованием API можно как-нибудь сделать транспонирование массива?
Конечно, стандартными VBA-циклами - нет проблем, но м-е-е-д-л-е-н-н-о будет, наверное.
Я вот писАл как-то:
Function ТrArr(Arr)   'возвращает транспонированный 2D-массив       Dim L1&; U1&: L1 = LBound(Arr; 1): U1 = UBound(Arr; 1)       Dim L2&; U2&: L2 = LBound(Arr; 2): U2 = UBound(Arr; 2)       Dim tArr(): ReDim tArr(L2 To U2; L1 To U1)       Dim RR&; CC&       For RR = L1 To U1          For CC = L2 To U2             tArr(CC; RR) = Arr(RR; CC)          Next CC       Next RR       TrArr = tArr End Function

Интересно было бы сравнить по скорости с Application.Transpose...
Что-то не соображу никак, как сравнивать, ведь Application.Transpose возвращает на лист... Или не обязательно - можно и в массив?


С уважением,
Алексей
MS Excel 2003 - the best!!!

Сообщение отредактировал Alex_ST - Понедельник, 25.10.2010, 12:26
 
Ответить
СообщениеИнтересно, а с использованием API можно как-нибудь сделать транспонирование массива?
Конечно, стандартными VBA-циклами - нет проблем, но м-е-е-д-л-е-н-н-о будет, наверное.
Я вот писАл как-то:
Function ТrArr(Arr)   'возвращает транспонированный 2D-массив       Dim L1&; U1&: L1 = LBound(Arr; 1): U1 = UBound(Arr; 1)       Dim L2&; U2&: L2 = LBound(Arr; 2): U2 = UBound(Arr; 2)       Dim tArr(): ReDim tArr(L2 To U2; L1 To U1)       Dim RR&; CC&       For RR = L1 To U1          For CC = L2 To U2             tArr(CC; RR) = Arr(RR; CC)          Next CC       Next RR       TrArr = tArr End Function

Интересно было бы сравнить по скорости с Application.Transpose...
Что-то не соображу никак, как сравнивать, ведь Application.Transpose возвращает на лист... Или не обязательно - можно и в массив?
Автор - Alex_ST
Дата добавления - 25.10.2010 в 12:22
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Группировка данных
  • Страница 1 из 1
  • 1
Поиск:

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